Mysql常用命令和函数

139次阅读

常用命令
1创建用户test,密码为password该用户没有任何权

create user test@localhost identified by ‘password’;
2指定用户拥有创建表的权限远程 %可以换成ip

grant all on database.* to ‘test’@’%’ identified by ‘password’;
grant select,insert,update,delete,create,drop on database.* to test@localhost identified by “test”;
3.刷新数据库

flush privileges;
4.查看用户信息

select host,user from mysql.user;
cmd mysql的bin目录下,使用mysql命令备份，还原能避免一些报错，解决兼容问题

备份MySQL数据库的命令
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
mysqldump -h192.168.1.240 -uroot -ppassword testa>”F:test.sql”
备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=... -C databasename

导入数据

mysql>use abc;
mysql>set names utf8;
mysql>source /home/abc/abc.sql;
不存在就创建

CREATE TABLE IF NOT EXISTS basegraphics (
id int(10) NOT NULL AUTO_INCREMENT,
content text NOT NULL COMMENT ‘文本内容’,
src varchar(100) NOT NULL COMMENT ‘图片地址’,
width varchar(30) NOT NULL COMMENT ‘图片宽度’,
height varchar(30) NOT NULL COMMENT ‘图片高度’,
createtime int(10) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
如果表存在则删除

DROP TABLE IF EXISTS orders
DISTINCT 替换GROUP by

select count(1) from (select order_id FROM sales_flat_order_item GROUP by order_id)a;
SELECT count(DISTINCT order_id) FROM sales_flat_order_item;

即可查看建表sql语句

SHOW CREATE TABLE test;

表的字段数

SHOW COLUMNS FROM test;
上一条

SELECT * FROM A WHERE id < $id ORDER BY id DESC LIMIT 1
下一条

SELECT * FROM A WHERE id > $id ORDER BY id ASC LIMIT 1
如果有字段自增和默认值可写成：

INSERT INTO Persons VALUES (‘Bill’, ‘Xuanwumen 10’);
常用函数
ABS(x) 返回x的绝对值
SELECT ABS(-1);
—- 返回1
CEIL(x)返回大于或等于 x 的最小整数
SELECT CEIL(1.5);
—- 返回2

–判断是否是整数
SELECT 10<>ceil(10.23),10<>ceil(10.0)
FLOOR(x) 返回小于或等于 x 的最大整数
SELECT FLOOR(1.5);
—- 返回1
ROUND(x)返回离 x 最近的数

SELECT ROUND(1.23456, 2);
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入

SELECT TRUNCATE(1.23456,3);
—- 返回1.234
LPAD (str,len,padstr)
返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

mysql> SELECT LPAD(‘hi’,4,’?’);-> ‘??hi’
mysql> SELECT LPAD(‘hi’,1,’?’);-> ‘h’
RPAD(str,len,padstr)
返回字符串str, 其右边被字符串 padstr填补至len 字符长度。假如字符串str 的长度大于 len,则返回值被缩短到与 len 字符相同长度。

FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入

SELECT FORMAT(250500.5634, 2);
—- 返回250,500.56
LTRIM/RTRIM/TRIM 去掉字符串两端的空格

SELECT TRIM(‘ RUNOOB ‘);
—- 返回RUNOOB
INSERT(str,pos,len,newstr)
返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr取代的len 字符。如果pos 超过字符串长度，则返回值为原始字符串。假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

mysql> SELECT INSERT(‘Quadratic’, 3, 4, ‘What’);-> ‘QuWhattic’
mysql> SELECT INSERT(‘Quadratic’, -1, 4, ‘What’);-> ‘Quadratic’
mysql> SELECT INSERT(‘Quadratic’, 3, 100, ‘What’);-> ‘QuWhat’
LENGTH(str)
返回值为字符串str 的长度，单位为字节。一个多字节字符算作多字节。这意味着对于一个包含5个2字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH()的返回值则为5。

mysql> SELECT LENGTH(‘text’);-> 4
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。

mysql> SELECT REPEAT(‘MySQL’, 3);-> ‘MySQLMySQLMySQL’
测试表数据

GREATEST（求最大值）和LEAST（求最小值）
mysql> SELECT c_id, GREATEST( num1, num2 ) AS max, num1, num2 from comment where
num1 != “” and num2 != “”;
+——+——+——+——+
| c_id | max | num1 | num2 |
+——+——+——+——+
| 1 | 21 | 21 | 12 |
| 2 | 219 | 133 | 219 |
| 3 | 67 | 67 | 16 |
+——+——+——+——+
3 rows in set (0.00 sec)

mysql> SELECT c_id, LEAST( num1, num2 ) AS max, num1, num2 from comment where nu
m1 != “” and num2 != “”;
+——+——+——+——+
| c_id | max | num1 | num2 |
+——+——+——+——+
| 1 | 12 | 21 | 12 |
| 2 | 133 | 133 | 219 |
| 3 | 16 | 67 | 16 |
+——+——+——+——+
3 rows in set (0.00 sec)

REGEXP函数

mysql> SELECT * FROM comment WHERE content REGEXP ‘[0-9]+’;
+——+——+——+————+———————+——+——+
| c_id | u_id | name | content | datetime | num1 | num2 |
+——+——+——+————+———————+——+——+
| 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 |
+——+——+——+————+———————+——+——+
1 row in set (0.00 sec)
IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。 IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

mysql > select IFNULL(1,0);
-> 1
mysql> select IFNULL(0,10);
-> 0
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,’yes’);
-> ‘yes’
判断字段值是否为null或者空串”

SELECT * FROM `table_name’ WHERE ifnull(col_name,”)=”;
IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。 IF()返回一个数字或字符串值，取决于它被使用的上下文。

mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,’yes’,’no’); -> ‘yes’
mysql> select IF(strcmp(‘test’,’test1′),’yes’,’no’);
-> ‘no’
expr1作为整数值被计算，它意味着如果你正在测试浮点或字符串值，你应该使用一个比较操作来做。

mysql> select IF(0.1,1,0);
-> 0
mysql> select IF(0.1<>0,1,0);
-> 1
在上面的第一种情况中，IF(0.1)返回0，因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中，比较测试原来的浮点值看它是否是非零，比较的结果被用作一个整数。

select * from stock_moves where abs (qty)=1140
CONCAT
可以连接一个或者多个字符串,MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

mysql> SELECT CONCAT(2,’ test’);
-> ‘2 test’
CONCAT_WS
即有分隔符的字符串连接如连接后以逗号分隔。优先使用

mysql> SELECT CONCAT_WS(‘;’,2,’ test’);
-> ‘2;test’
SELECT CONCAT_WS(‘;’, null,’ test’);
-> ‘test’
COALESCE()
函数表示可以返回参数中的第一个非空表达式，当你有N个参数时选取第一个非空值（从左到右）。

mysql->select coalesce(null,”carrot”,”apple”)
->carrot
mysql-> select coalesce(1,”carrot”,”apple”)
->1
select coalesce(a,b,c);
如果a==null,则选择b；

如果b==null,则选择c；

如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参数的顺序被颠倒。

mysql> SELECT INSTR(‘foobarbar’, ‘bar’);
-> 4
LOCATE(substr,str)
返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.

select substr(‘1分29秒’,1,LOCATE(‘分’,’1分29秒’)-1)*60+substr(‘1分29秒’,LOCATE(‘分’,’1分29秒’)+1,2)
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) ,SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

mysql> SELECT SUBSTRING(‘Quadratically’,5);
-> ‘ratically’
mysql> SELECT SUBSTRING(‘foobarbar’ FROM 4);
-> ‘barbar’
mysql> SELECT SUBSTRING(‘Quadratically’,5,6);
-> ‘ratica’
mysql> SELECT SUBSTRING(‘Sakila’, -3);
-> ‘ila’
SUBSTRING_INDEX(str,delim,count)
在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边的一切内容。

mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);
-> ‘www.mysql’
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
-> ‘mysql.com’
CREATE FUNCTION substr_count(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, ”))) // select substr_count(‘ABAC’,’A’);
MAKE_SET(bits,str1,str2,…)
返回一个设定值 (一个包含被‘,’号分开的字字符串的字符串) ，由在bits 组中具有相应的比特的字符串组成。str1 对应比特 0, str2 对应比特1,以此类推。str1, str2, …中的 NULL值不会被添加到结果中。

mysql> SELECT MAKE_SET(1,’a’,’b’,’c’);
-> ‘a’
mysql> SELECT MAKE_SET(1 | 4,’hello’,’nice’,’world’);
-> ‘hello,world’
mysql> SELECT MAKE_SET(1 | 4,’hello’,’nice’,NULL,’world’);
-> ‘hello’
LEFT(str,len) 返回从字符串str 开始的len 最左字符。
mysql> SELECT LEFT(‘foobarbar’, 5);-> ‘fooba’
RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符
ELT(N,str1,str2,str3,…)
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。

select ELT(1, ‘ej ‘, ‘Heja ‘, ‘hej ‘, ‘foo ‘); //-> ‘ej ‘
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

mysql> SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
-> ‘WwWwWw.mysql.com’
update user set created =replace(replace (replace(created, “年 “, “- “), “月 “, “- “), “日 “, ” “);
GROUP_CONCAT()
是MySQL数据库提供的一个函数，通常跟GROUP BY一起用,
1.例如:

select student_id,group_concat(courses_id order by courses_id desc) as courses_id from student_courses group by student_id;
　　+————+————-+
　　| student_id | courses_id |
　　+————+————-+
　　| 2 | 5,4,3 |
2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：

SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR ‘|||’) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
+————+———+
| student_id | courses |
+————+———+
| 2 | 3|||4|||5 |
+————+———+
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

UNION 指令的目的是将两个 SQL 语句的结果合并起来。从这个角度来看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。 UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外，当我用 UNION 这个指令时，我们只会看到不同的资料值 (类似 SELECT DISTINCT )。

UNION 的语法如下：

[SQL 语句 1]
UNION
[SQL 语句 2]

而我们要找出来所有有营业额 (sales) 的日子。要达到这个目的，我们用以下的 SQL 语句：

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

trim函数可以过滤指定的字符串：

mysql> SELECT TRIM(‘ bar ‘); //默认删除前后空格
-> ‘bar’
mysql> SELECT TRIM(LEADING ‘，’ FROM ‘，，barxxx’); //删除指定首字符如’，‘
-> ‘barxxx’
mysql> SELECT TRIM(BOTH ‘，’ FROM ‘，，bar，，，’); //删除指定首尾字符
-> ‘bar’
mysql> SELECT TRIM(TRAILING ‘，’ FROM ‘barxxyz，，’);
-> ‘barxxyz’

正文完

mysql

发表至： mysql

2023-11-21

外包网址

Mysql常用命令和函数

即可查看建表sql语句

表的字段数

近期文章

近期评论