1. 查看存储引擎

show engines;

2. 数据库备份

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

3. 关闭外键 约束

SELECT @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=1; // 开启外键约束SET FOREIGN_KEY_CHECKS=0; // 关闭外键约束

4. 将 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);

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

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';

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

show variables where Variable_name ='datadir';

7. 添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 它 是一种特殊的唯一索引，不允许有空值

8. 添加唯一索引

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

9. 添加普通索引

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

10. 添加全文索引

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

11. 查看表的索引信息

show index from table1

12. 删除索引

DROP INDEX index_name ON table_name

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

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

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

select user,host,password from mysql.user;

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

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

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

SET SQL_SAFE_UPDATES=0;

17. 查询 最后 10 条数据，（id 自增）

select * from wy_user order by id desc limit 10;

18. 查询重复记录

SELECT id,email FROM wy_user2 WHERE id IN ( SELECT id FROM wy_user2 GROUP BY email HAVING count(email) > 1 )

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

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

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

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

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

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

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

select * from tableName ignore index(PRI)

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

select * from tableName ignore index(PRI, indexName)

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

select * from tableName force index(PRI)

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

select * from tableName force index(PRI, indexName)

26. 查看用户权限

show grants for user_name@localhost;

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

show variables like '%log_error%';

28. 创建 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

29. 创建用户

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

30. 关闭自动提交

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

31. 设置自增从 10000 开始

ALTER TABLE tableName AUTO_INCREMENT=10000;