mysql指令按顺序排列_mysql: 常用命令

将自己常用到的MySQL的一些命令在这里做个记录，便于查询和更新。

1. 登入

mysql -h localhost -u root -p dbName

2. 查看当前数据库

show databases; select database();

3. 切换数据库

use world;

4. 查看表

show tables;

5. 查看有哪些数据库

show databases;

6. 授权(所有权限)某个用户从某台机器访问本台mysql服务器

grant all on *.* to user1@192.168.67.1 identified by "pawd123";

7. 查看MySQL当前的版本

select version();

8. 查看参数值

show variables like "%log%";

9. flush logs;

10. 查看最后一个bin日志

show master status;

11. 清空所有日志

reset master;

12. 删除一张表里所有的数据

truncate table1;

13. 删除一张表;

drop table table1;

14. 查看安装的plugin

show plugins;

15. 查看创建表的sql语句

show create table t1;

16. 排序

SELECT * FROM `tableName` ORDER BY colName desc, colName asc

// 将字符型的 数字(ID_)转成数字来排序 +0 或 *1

SELECT * FROM `tableName` order by ID_+0 asc;

17. 分组

SELECT `colName `,count(*) as total FROM `tableName` GROUP BY colName

18. 查看大小写是否敏感 mysql中控制数据库名和表名的大小写敏感由参数lower_case_table_names控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin 大小写敏感

SHOW VARIABLES LIKE '%case%';

19. 表复制

create table bs_test2 like bs_test1 // 复制表结构

INSERT INTO bs_test1 SELECT * FROM bs_test2; // 复制表中的数据

20. 给表添加注释

ALTER TABLE 表名 COMMENT ‘注释的内容'

// 查看某个表的注释

SELECT table_name,table_comment FROM information_schema.tables where table_name='表名'

21. 查看存储引擎

show engines;

22. 数据库备份

/usr/local/mysql/bin/mysqldump -u root -p lemon > lemon.sql /usr/local/mysql/bin/mysqldump -u root -p dbName tableName --where="..." > table.sql

23. 关闭外键 约束

SELECT @@FOREIGN_KEY_CHECKS;

SET FOREIGN_KEY_CHECKS=1; // 开启外键约束

SET FOREIGN_KEY_CHECKS=0; // 关闭外键约束

24. 将 CSV 文件导入 Mysql 中

LOAD DATA LOCAL INFILE '/Users/fangcailiang/Downloads/dbs/db-friend/aff11.csv' into table user1 FIELDS TERMINATED BY ',' lines terminated by '\n' ignore 1 lines (pwsid,email,country,sex,birthday,state,zip,ip);

25. 查询出 数据库 中所有的 表信息

select table_name from information_schema.tables where table_schema='数据库名' and table_type='base table'; select * from information_schema.tables where table_schema='数据库名' and table_type='base table';

26. 查看MySQL的一些配置，比如查看MySQL的数据库文件存放在那个目录就可以用下面的命令

show variables where Variable_name ='datadir';

27. 添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

它 是一种特殊的唯一索引，不允许有空值

28. 添加唯一索引

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

29. 添加普通索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

30. 添加全文索引

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间 (适用于，大块数据，如文章内容)

31. 查看表的索引信息

show index from table1

32. 删除索引

DROP INDEX index_name ON table_name

33 查看一张表 或 一条sql语句的执行情况

(DESC 或 EXPLAIN) DESC SELECT * FROM bs_member DESC bs_member

34. 查看当前数据库有哪些用户可以访问

select user,host,password from mysql.user;

35. 创建数据库, utf8_bin 区分大小写，utf8_general_ci 不区分大小写

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_bin;

36. 关闭更新时的安全模式

SET SQL_SAFE_UPDATES=0;

37. 查询 最后 10 条数据，(id 自增)

select * from wy_user order by id desc limit 10;

38. 查询重复记录

SELECT

id,email

FROM

wy_user2

WHERE

id IN (

SELECT

id

FROM

wy_user2

GROUP BY

email

HAVING

count(email) > 1

)

39. 删除重复记录，并保留id最小的记录

delete from wy_user2 where id not in (select minid from (select min(id) as minid from wy_user2 group by email) b);

40. 按某一列的值的长度查找 // UTF8 编码中文长度为 3

SELECT * FROM `bs_member` WHERE city like '%北京%' and length(city) > 7

41. 查看 MySQL 加载配置文件的顺序，后面的配置会覆盖前面相同的配置项

mysqld --help --verbose | grep -A 1 'Default options'

42. 查询时禁止使用主键索引

select * from tableName ignore index(PRI)

43. 查询时禁止使用某些索引

select * from tableName ignore index(PRI, indexName)

44. 查询时强制使用主键索引

select * from tableName force index(PRI)

45. 查询时强制使用某些索引

select * from tableName force index(PRI, indexName)

46. 查看用户权限

show grants for user_name@localhost;

47. 查看错误日志的存放位置

show variables like '%log_error%';

48. 创建 json 列，创建虚拟列 user_name，address

CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`info` json NOT NULL,

`user_name` varchar(128) GENERATED ALWAYS AS (json_extract(`info`,'$.name')) VIRTUAL,

`address` varchar(128) GENERATED ALWAYS AS (json_extract(`info`,'$.address')) STORED,

PRIMARY KEY (`id`),

KEY `user_name_index` (`user_name`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

49. 创建用户

CREATE USER 'test@%' IDENTIFIED BY "123456";

50. 关闭自动提交

SET AUTOCOMMIT=0; // 只对当前会话生效

51. 设置自增从 10000 开始

ALTER TABLE tableName AUTO_INCREMENT=10000;

52. 查看数据库占用空间

SELECT

table_schema,

SUM(data_length + index_length) / 1024 / 1024 AS total_mb,

SUM(data_length) / 1024 / 1024 AS data_mb,

SUM(index_length) / 1024 / 1024 AS index_mb,

COUNT(*) AS tables,

CURDATE() AS today

FROM

information_schema.tables

GROUP BY table_schema

ORDER BY 2 DESC;

53. 查看某个数据库表中的情况

SELECT

table_name,

(data_length / 1024 / 1024) AS data_mb,

(index_length / 1024 / 1024) AS index_mb,

((data_length + index_length) / 1024 / 1024) AS all_mb,

table_rows

FROM

information_schema.tables

WHERE

table_schema = 'db_name';

54. 查看某个库的具体情况

show table status from db_name;

56. 查看数据库中表碎片的情况

SELECT TABLE_SCHEMA

,TABLE_NAME

,ENGINE

,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB

,ROUND(DATA_FREE/1024/1024,2) AS FREE_SIZ_MB

FROM information_schema.TABLES

WHERE DATA_FREE >=100*1024*1024

ORDER BY FREE_SIZ_MB DESC;

，，，，