mysql的常用命令总结（持续更新）

 

有道笔记持续更新中（http://note.youdao.com/noteshare?id=aa772307b5052f3fcc51e2ef561cf322&sub=1BD603F126744722A7471D8D1364C178）

 

 

*********************************整个数据库操作****************************

导出（备份）数据库db_test的全部表的结构和数据，命名为zz.sql：

[root@mysqlmaster1 ~]# mysqldump db_test -uusername -ppassword > zz.sql

 

导出（备份）数据库db_test的全部表的结构，命名为zz.sql：

[root@mysqlmaster1 ~]# mysqldump -t db_test -uusername -ppassword > zz.sql

 

导出（备份）数据库db_test的全部表的数据，命名为zz.sql：

[root@mysqlmaster1 ~]# mysqldump -d db_test -uusername -ppassword > zz.sql

 

 

*******************************数据库某张表操作*****************************

导出（备份）数据库db_test的 t1表 和 t2 表 的结构和数据，命名为 t12.sql：

[root@mysqlmaster1 ~]# mysqldump db_test -uusername -ppassword --table t1 t2 > t12.sql

 

导出（备份）数据库db_test的 t1表 和 t2 表 的结构，命名为 t12.sql：

[root@mysqlmaster1 ~]# mysqldump -t db_test -uusername -ppassword --table t1 t2 > t12.sql

 

导出（备份）数据库db_test的 t1表 和 t2 表 的数据，命名为 t12.sql：

[root@mysqlmaster1 ~]# mysqldump -d db_test -uusername -ppassword --table t1 t2 > t12.sql

 

 

*********************导入数据到数据库****************************

1》 直接导入：

[root@mysqlmaster1 ~]# mysql -uusername -ppassword < /root/sqlname.sql

2》间接登录：

[root@mysqlmaster1 ~]# mysql -uusername -ppassword

mysql> source /root/sqlname.sql

 

 

**********************基本信息查询***************************************

1》 数据库本身信息

 

一键看所有信息：

mysql> status;

 

查看数据库的所有用户信息：

mysql> select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;

 

查看某个用户的所有权限：

mysql> show grants for 'root'@'localhost';

 

查看数据库的最大连接数：

mysql> show variables like '%max_connections%';

 

查看数据库的当前连接数和并发数

mysql> show status like 'Threads%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_cached | 17 |

| Threads_connected | 22 |

| Threads_created | 1382 |

| Threads_running | 4 |

+-------------------+-------+

4 rows in set (0.00 sec)

 

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。

 

Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

 

Threads_created :代表从最近一次服务启动，已创建线程的数量。

 

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

 

查看文件的存放路径：

mysql> show variables like '%datadir%';

 

-- 查看mysql的版本

mysql> SELECT VERSION();

 

-- 查看当前时间

mysql> SELECT `CURRENT_DATE` ();

 

查看数据库所用的端口

mysql> show variables like 'port';

 

查看数据库的编码：

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

 

character_set_client 为客户端编码方式；

 

character_set_connection 为建立连接使用的编码；

 

character_set_database 为数据库的编码；

 

character_set_results 为结果集的编码；

 

character_set_server 为数据库服务器的编码；

 

 

查询所有数据库占磁盘的大小：

select

TABLE_SCHEMA,

concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,

concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size

from information_schema.tables

group by TABLE_SCHEMA

ORDER BY data_size desc;

 

查询数据库中每个表占磁盘的大小：

select

TABLE_NAME,

concat(truncate(data_length/1024/1024,2),' MB') as data_size,

concat(truncate(index_length/1024/1024,2),' MB') as index_size

from information_schema.tables

where TABLE_SCHEMA = 'test'

group by TABLE_NAME

order by data_length desc;

 

 

2》 创建数据库的信息

-- 显示当前选择的数据库

-- SHOW `DATABASE`();

 

使用数据库：

mysql> use databases_name;

3》 表的信息

-- 查看当前数据库的所有表

-- SHOW TABLES;

或者

select * from information_schema.tables where table_schema='databasename';

 

-- 显示表的结构

-- DESCRIBE mytable;

 

*************************表的增删改查操作************************************************

SELECT * FROM `user`;

-- 插入值，一次性可以插入多条

INSERT INTO `user` (age, name, address, country, number) 

VALUES

(67, 'n', '河北', '中国', 133), 

(55, 'k', 'cs', '美国', 133),

(55, 'r', '河北', '英国', 133);

-- 插入时间

INSERT INTO `user` (time) VALUES (NOW());

-- 将时间为空的列更新为最新时间

UPDATE `user` time=NOW() WHERE time=NULL;

 

-- 设置时间为特定时间， 用date_sub 和 date_add来将时间向前向后推移 

-- 从当前时间开始计算，并修改

UPDATE `user` c SET  c.time = DATE_SUB(NOW(),INTERVAL 60 SECOND) WHERE id=30;

-- 从原有时间开始计算，并修改

UPDATE `user` c SET  c.time = DATE_SUB(c.time,INTERVAL 10 DAY) WHERE id=30;

-- 只是算结果，不修改原来的时间

SELECT u.time, DATE_ADD(u.time,INTERVAL 10 DAY) AS new_time FROM `user` AS u WHERE u.id = 30;

 

-- NULL的重置与查询 

-- null 和 '' 的不同，back键='' ， delete键=null

UPDATE `user` SET address='' WHERE id=38;

UPDATE `user` SET address=NULL WHERE id=38;

SELECT * FROM `user`  AS u WHERE u.address IS NULL;

-- SELECT * FROM `user`  AS u WHERE u.address IS NOT NULL; -- 可以排除null

-- SELECT * FROM `user` AS u WHERE u.address='Null'; -- 不能查到结果

SELECT * FROM `user` AS u WHERE u.address='';

 

-- 查询表的结构

DESC `user`;

-- 查询id在2， 6， 7这三个数中的值

SELECT * FROM `user` WHERE id IN (2, 6, 7);

-- 查询id小于等于7的值，可用符号（ >,=,<,<>,!=,!>,!<,=>,=< ）

SELECT * FROM `user` WHERE id <= 7;

 

-- 按照country分类统计对应address的个数

SELECT u.country, COUNT(u.address) FROM `user` AS u GROUP BY u.country;

 

-- count=累计， 查询country中不重复的元素，并统计不重复元素的个数

SELECT COUNT(u2.country) FROM (SELECT DISTINCT u1.country FROM `user` as u1) AS u2;

-- sum=累加，去掉age重复元素后所有元素的和

SELECT SUM(u2.age) FROM (SELECT DISTINCT u1.age FROM `user` as u1) AS u2;

-- 不去重age列所有元素的和

SELECT SUM(u.age) FROM `user` AS u;

-- BETWEEN

SELECT * FROM `user` WHERE id BETWEEN 2 AND 6;

 

-- `TRUNCATE`(X,D)删除表中的数据

        TRUNCATE TABLE `user1` ;

        DROP TABLE user1;

 

-- null值转化为0求和（不转换统计将会出错）

        SELECT SUM(ping.number + ping.p) FROM (SELECT ps.number, IFNULL(ps.age,0) AS p FROM `user` AS ps) AS ping;

 

        SELECT IFNULL(ps.age,0) AS p FROM `user` AS ps

 

 

        SELECT SUM(u.age) + SUM(u.number) FROM `user` AS u; -- 推荐写这种

        SELECT SUM( + u.number)  FROM (SELECT  IFNULL(ps.age,0) FROM `user` AS ps)  AS u ;

 

-- 双表左右连接查询

        SELECT * from `user1` u1 LEFT JOIN `user` u ON `u`.id=u1.id;        

        SELECT * from `user1` u1 INNER JOIN `user` u ON `u`.id=u1.id;

 

        SELECT * FROM 

 

-- 联合查询()

        SELECT SUM(u1.age) + SUM(u1.number) from `user1` AS u1 LEFT JOIN `user` AS u ON `u`.id=u1.id;

 

        SELECT *, IFNULL(ps.age,0) FROM `user` AS ps;

        -- number去重， age不去重求和

        SELECT SUM(u2.number) + (SELECT SUM(u.age) FROM `user` AS u)  FROM (SELECT DISTINCT u1.number FROM `user` AS u1  ORDER BY u1.age DESC) AS u2;

 

 

-- 查询非空数据

SELECT * FROM `user` WHERE age <>;

 

-- 数学函数

        -- SIN(X)  COS(X) TAN(X)  ASIN(X) ACOS(X) ATAN(X) LOG2(X) LOG10(X) PI()

        -- 弧度转角度 DEGREES(X)

        -- 角度转弧度 RADIANS(X)

        -- 整除余数   MOD(N,M)

 

-- 字符串函数

 

        -- 合并字符串（注意null的不同）

        SELECT CONCAT(ps.address, ps.country) AS con FROM `user` AS ps WHERE id=6;

        SELECT CONCAT(ps.address, NULL) AS con FROM `user` AS ps WHERE id=6;

        SELECT CONCAT(ps.address, "NULL") AS con FROM `user` AS ps WHERE id=6;

        SELECT CONCAT(NULL, ps.address) AS con FROM `user` AS ps WHERE id=6;

 

        -- 比较字符串的大小

        SELECT STRCMP(ps.address,ps.country) AS con FROM `user` AS ps WHERE id=6;

        SELECT STRCMP(ps.address,NULL) AS con FROM `user` AS ps WHERE id=6;

 

        -- 求字符串的长度

        SELECT LENGTH(ps.address) FROM `user` AS ps WHERE ps.id=6;

        SELECT CHAR_LENGTH(ps.address) FROM `user` AS ps WHERE ps.id=6;

 

        -- 字符串大小写的转换

        SELECT UPPER(ps.address), LOWER(ps.address) FROM `user` AS ps WHERE ps.id=6;

        SELECT UCASE(ps.address), LCASE(ps.address) FROM `user` AS ps WHERE ps.id=6;

        SELECT UPPER(ps.country) FROM `user` AS ps WHERE ps.id=6;

 

        -- 查找字符串的函数

            -- 默认从1开始

        SELECT FIND_IN_SET(ps.address, "kl,rt,er,hm") FROM `user` AS ps WHERE ps.id=4;

        SELECT FIELD(ps.address,"rt","kl","rt","kf") FROM `user` AS ps WHERE ps.id=4;

        SELECT LOCATE(ps.address,"4errtrter") FROM `user` AS ps WHERE ps.id=4;

        SELECT POSITION(ps.address IN "4errtrter") FROM `user` AS ps WHERE ps.id=4;

                -- 注意位置反了

        SELECT INSTR("4errtrter", ps.address) FROM `user` AS ps WHERE ps.id=4;

 

        -- 获取指定位置的字符串

                -- SELECT ELT(2,"er","ff","cc","dd");

        -- 截取字符

                -- 截取左边n个字符

                        -- SELECT `LEFT`("klkl",2);

                -- 截取右边n个字符

                        -- SELECT  `RIGHT`(str,len);

                -- 从str的index 开始截取 len 长度的字符

                        -- SELECT SUBSTRING(str,pos,len)

                        -- SELECT MID(str,pos,len)

                -- 字符串替换(str要替换的字符串， from str 替换位置， to str 替换的字符串， 如果to str 为空，将是删除操作)

                        -- `REPLACE`(str,from_str,to_str) 不懂怎样用

                -- 截取字符串左侧是空格, 截取字符串右面的空格 RTRIM(), 截取两边的空格 TRIM()

                    SELECT ps.address, LTRIM(ps.address) FROM `user` AS ps WHERE id=6;

                    SELECT ps.address, TRIM(ps.address) FROM `user` AS ps WHERE id=6;

 

 

-- 日期函数

 

    -- 获取当前时间

            SELECT NOW();

            SELECT NOW(), SLEEP(5), NOW();

            SELECT curtime();

            SELECT SYSDATE();

            SELECT SYSDATE(), SLEEP(8), SYSDATE();

            SELECT current_timestamp;

            SELECT CURRENT_DATE;

            SELECT CURRENT_TIME;

            SELECT CURRENT_USER;

      SELECT * FROM `user` WHERE time<CURRENT_DATE;

            -- 查找时间并截取日期 

            -- date_format('1997-10-04 22:23:00','%y %M %b %D %W %a %Y-%m-%d %H:%i:%s %r %T')

            -- 结果：97 October Oct 4th Saturday Sat 1997-10-04 22:23:00 10:23:00 PM 22:23:00

            SELECT DATE_FORMAT(ps.time,'%Y-%m-%d') FROM `user` AS ps;

            SELECT DATE_FORMAT(ps.time,'%H:%i:%s') FROM `user` AS ps;

            -- 求日期对应的星期 w 是全写， a是简写

            SELECT DATE_FORMAT(ps.time,'%W') FROM `user` AS ps;

            SELECT DATE_FORMAT(ps.time,'%a') FROM `user` AS ps;

            SELECT DATE_FORMAT(NOW(),'%W');

            -- 计算当前日期是一月中的第几周

            SELECT (DAY(NOW())+WEEKDAY(NOW()-INTERVAL DAY(NOW()) DAY)) DIV 7 + 1;

            -- 计算当前时间是一年中的第几周

            SELECT WEEK(date_add(NOW(),interval 6 day),2);