MySQL 命令大全

DDL

连接：mysql -uroot -p
显示所有数据库：show databases
创建数据库：create database dbname
删除数据库：drop database dbname
使用数据库：use dbname
显示所有表：show tables
创建表：create table tname(cname1 ctype1 constraints,cname2 ctype2 constraints,...)
查看表定义：desc tname
查看创建表定义： show create table tname
删除表：drop table tname
修改表类型：alter table tname modify [column] column_definition [first|after cname]
增加表字段：alter talbe tname add [column] column_definition [first|after cname]
删除表字段：alter table tname drop [column] cname
字段改名：alter table tname change ocname cname cdefinition [first|after cname]

表改名：alter table otname [to] rename tname

DML

插入记录：insert into tname (cname1,cname2,...) values (cvalue1,cvalue2,...),(cvalue1,...) 不指定字段名时，values 后面的顺序要和字段顺序一样
更新记录：update tname set cname1=cvalue1,cname2=cvalue2,...[where condition]
多表更新：update tname1 a,tname2 b set a.cname=b.cname,b.cname=a.cname [where condition]
删除记录：delete from tname [where condition]
删除多表记录：delete t1,t2,... from t1,t2,...[where condition]
查询记录：select * from tname [where condition]
去重复查询：select distinct cname from tname [where condition]
条件查询：select * from tname where cname=value
排序：select * from tname [where condition] [order by cname1 [desc|asc],cname2 [desc|asc],...]
限制：select * from tname [where condition] [limit offset_start, row_count] 起始偏移量（offset_start）默认为0，row_count 表示显示的行数
聚合：select cname fun_name from tname [where condition] [group by cname1,cname2,...] [with rollup] [having condition]
fun_name 表示要做的聚合操作(sum(),count(),max(),min())
group by 表示要进行聚合的字段
with rollup 对聚合后的结果汇总
having 对聚合后的结果过滤
表连接
内连接：select * from tname1,tname2 [where condition]
外连接：
左连接：select cn1,cn2 from tn1 left join tn2 on tn1.cn2==tn2.cn2
右连接：select cn1,cn2 from tn1 right join tn2 on tn1.cn2==tn2.cn2
子查询：select * from tn1 where cn in (select * from tn2) 关键字包括in,not in,=,!=

记录联合：select * from tn1 union|union all select * from tn2

DCL

创建用户授予权限：grant select,insert on dataname.* to 'username'@'localhost' identified by 'password'

变更权限：revoke select on dataname.* from 'username'@'localhost'

others

数据库支持的存储引擎： show engines
创建表时设置存储引擎：create table tn (cn int)engine=innodb defualt charset=utf8
修改存储引擎：alter table tn engine=myisam dufault charset=gbk
设置 auto_increment 的初始值，默认从1开始：alter table tn auto_increment=10
查询当前线程最后插入记录使用的值：select last_insert_id()
关闭外键检查：set foreign_key_checks=0
查看表的状态：show table status like 'tn'
增加外键：alter table tn1 constraint fkname add foreign key(indexname) references tn2(indexname) on delete no action on update cascade
cascade 跟随父表更新删除
restrict 默认值，和 no action 一样，限制父表更新删除
set null 父表更新删除，子表设置为 null
创建表时添加外键：create table tn (cn int primary key,foreign key(cn) references tn2(cn) on update cascade on delete restrict)
删除外键：alter table tn drop foreign key fkname
查看字符集：show character set，information_schema.character_sets
查看字符集的校对规则：show collation like 'utf8%'，information_schema.collations
服务器级字符集，在 my.cnf 文件中设置： [mysqld] character-set-server=utf8
查看服务器字符集：show variables like 'character_set_server'
显示数据库字符集和校对规则：show variables like 'character_set_database'，show variables like 'collation_database'
修改数据库字符集：alter database db character set utf8
显示表字符集：show create table tn
修改表字符集：alter table tn default character set utf8
设置连接字符集，在 my.cnf 文件中设置：[mysql] default-character-set=utf8，或每次通过命令 set names utf8
导出表：mysqldump -uroot -p db>newdb.sql
-d 不导出插入的数据
-t 不导出表创建表结构
-n 不导出创建数据库的语句
--quick 该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有的行，并在输出前将它缓存到内存中
--extended-insert 使用包括几个 values 列表的多行 insert 语法。这样使转储文件更小，重载文件时可以加速插入
--no-create-info 不导出每个转储表的 create table 语句
--default-character-set=Latin1 按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码
导入数据：mysql -uroot -p db<newdb.sql
创建索引：create [unique|fulltext|spatial]index indexname on tname(cname(length),...)
增加索引：alter table tn add index indexname(length)
删除索引：drop index indexname on tname 或 alter table tname drop index indexname
创建视图： create [or replace] [algorithm = {undefined | merge | temptable}] view viewname as select_statement [with [cascaded | local] check option]
修改视图： alter [algorithm = {undefined | merge | temptable}] view viewname as select_statement [with [cascaded | local] check option]
删除视图：drop view viewname
查看视图：show create view viewname，show table status like 'viewname'
修改定界符： delimiter ;
创建存储过程： create procedure pname([parameter[,...]])[characteristic ...] routine_body
创建函数：create function fname([parameter[,...]])returns type[characteristic ...] routine_body
修改存储过程或函数：alter {procedure|function} name [characteristic...]
调用过程：call name(parameter[,...])
删除存储过程或函数：drop {procedure|function} [if exists] name
查看存储过程或函数的状态：show [procedure|function] status like 'name'
查看存储过程或函数的定义：show create [procedure|function] name
查看 information_schema.routines表了解存储过程和函数的信息
定义变量：declare varname[,...] type [default value]
变量赋值：set varname=value,[varname=value]...
通过查询赋值：select cname into varname from tname
定义条件：declare condition_name condition for sqlstate_value|mysql_error_code
定义条件处理：create continue|exit|undo handler for (sqlstate_value|condition_name|sqlwarning|not fond|sqlexception|mysql_error_code)[,...] sp_statement
创建光标：declare cursor_name cursor for select_statement
open 光标：open cursor_name
fetch 光标：fetch cursor_name into var_name[,var_name]...
close 光标：close curosr_name
变量，条件，处理程序，光标都是通过 declare 定义的，它们之间是有先后顺序要求的。（变量，条件，光标，处理程序）
if 语句：if search_condition then statement_list [elseif search_condition then statement_list]... [else statement_list] endif
case 语句： case case_value when when_value then statement_list [when when_value then statement_list]...[else statement_list] end case 或 case when search_condition then statement_list [when search_condition then statement_list]... [else statement_list] end case
loop 语句: [begin_label:]loop statement_list end loop[end_label]
leave 语句：leava label 退出循环
iterate 语句：iterate label 放弃循环剩下的语句，进入下一个循环
repeat 语句：[begin_label:] repeat statement_list until search_condition end repeat [end_label] 满足条件退出循环，至少执行一次
while 语句：[begin_label:] while search_condition do statement_list end while[end_label] 满足条件执行循环
事件调度器：create event eventname on schedule every 5 second do insert into tname values(1,2,3)
查看事件：show events
查看事件状态：show variables like '%scheduler%'
打开事件调度器：set global event_scheduler = 1
禁用事件：alter event eventname disabled
删除事件：drop event eventname
清空表：truncate table tablename
查看线程：show processlist
创建触发器：create trigger triggername before|after update|delete|insert on tname for each row begin ... end
删除触发器：drop trigger triggername
查看触发器：show triggers 或 information_schema.triggers 表
表锁：lock tables tname {read [local]|[low_priority]write},tname... 或 lock table tname read|write
表解锁：unlock tables
开始事务：start transaction 或 begin
提交事务：commit [and chain|release]
回滚：rollback [to savepoint pointname]
关闭自动提交：set autocommit=0
指定事务回滚的位置：savepoint pointname
删除位置：release savepoint pointname
查看 SQL mode：select @@sql_mode
设置 SQL mode：set (session|global)? sql_mode='modes'
range 分区：create table tname (...)partition by range [columns](cname)(partition p0 values less than(10),partition p1 values less than(20),...partition pn values less than maxvalue)
删除分区(range|list)：alter table tname drop partition p0
增加分区(range|list)：alter table tname add partition (partition p3 values less than (30))
list 分区：create table tname (...) partition by list[columns](cname)(partition p0 in (1,3,5),partition p1 in (2,4,6),...)
hash 分区(整数)：create table tname (...) partition by [linear] hash (cname) partitions n
key 分区：create table tname(...) partition by [linear] key (cname) partitions n
子分区：create table tname(...)partition by range|list (cname) subpartition by hash|key(cname) subpartitions n (partition p0 values less than (10),partition p1 values less than (20))
重组分区(range|list)：alter table tname reorganize partition p0,p1 into (partition p0 values less than (20))
合并分区(key|hash)：alter table tname coalesce partition 2
增加分区(key|hash)：alter table tname add partition partitions 8
显示SQL执行频率：show status like 'com%'
显示SQL执行计划： explain select * from tname 或 explain extended select * from tname 或 explain partitions select * from tname
检查是否支持profile：select @@have_profiling
检查是否开启profiling：select @@profiling
设置profiling：set profiling = 1
查看profiles：show profiles
查看profile：show profile [all|cpu|block io|context switch| page faults] for query queryid
查看索引使用情况：show status like 'handler_read%'
分析表：analyze [local|no_write_to_binlog] table tname[,tname]...
检查表：check table tname[,tname]... [quick|fast|medium|extended|changed]
优化表：optimize [local|no_write_to_binlog] table tname[,tname]...
设置 MyISAM 表非唯一索引的更新： alter table tname enable|disable keys
设置唯一性校验：set unique_checks=0|1
显示表的索引：show index from tname
SQL 提示：select * from tname use index(indexname) 或 select * from tname ignore index (indexname) 或 select * from tname force index(indexname)
随机排序：order by rand()
不排序：order by null
bit_or(cn) 和 bit_and(cname)
优化表的数据类型：select * from tn procudure analyse()
查看表锁争夺情况：show status like 'table%'
降低更新请求的优先级：set low_priority_updates=1
查看行锁争夺情况：show status like 'innodb_row_lock%'
共享读锁：select * from tn where ... lock in share mode
排他写锁：select ... for update
行锁是给索引加锁，不然会给所有行加锁，相同索引的值都会加锁
查看隔离级别：select @@tx_isolation
查看发生死锁的原因：show innodb status
查看所有线程的状态：show engine innodb status
查看缓存区大小：show variables like 'key_buffer_size'
查看查询缓存：show variables like 'query_cache'
显示所有命令 mysql --help
查看当前连接的用户：select current_user()
连接时设置连接字符集：mysql -uroot -p --default-character-set=utf8
在 mysqld 开启 binlog：bin-log=master-bin
在 mysqld 设置 binlogindex：bin-log-index=master-bin.index
查看所有 binlog：show master logs
查看最新 binlog：show master status
binlog 读取：mysqlbinlog binlogname-bin.000001 或 show binlog events in 'binlogname-bin.000001' [from pos] [limit]
刷新 binlog（所有新的记录都记录在新的 binlog 中）：flush logs
清空binlog：reset master
删除 binlog.000006 之前的 binlog：purge master logs to 'binlog.000006'
删除某时间之前的 binlog：purge master logs before '2017-11-8 14:47:00'
从 binlog 恢复：mysqlbinlog --stop-position=1234 binlog-bin.000002 | mysql -uroot -p
在 mysqld 设置日志的过期天数：expire_logs_day=n
设置查询日志：set global general_log=1
导出表：select * from tn into outfile 'filename'
导入表：load data infile 'filename' into table tn [fileds terminated by ',' (optionally)? enclosed by '"' ...]
创建用户(授予权限)：grant all privileges on *.* to name@'127.0.0.1' identified by '123' [with grant option|max_queries_per_hours n|max_updates_per_hours n|max_connections_per_hours n|max_user_connections n]
查看用户权限：show grants for user@host
收回权限：revoke all privileges,grant option on *.* from user@host[,user...]
修改密码：set password for user@host=password('123')
删除用户：drop user username@host
刷新权限：flush privileges|flush user_resources|mysqladmin reload

设置 mysqld 跳过权限表：skip-grant-tables