Eyes are more eloquent than lips.
“眉目传情胜于甜言蜜语”
整理了一下自己遇到并且经常用到的MySQL命令，虽然官方文档上有很详细的解释，不过自己还是在这里记录一下，方便查看，顺便加深一下印象，当前版本：MySQL 5.6
注：
- 未加 ‘shell > ’的均表示在mysql命令行下的命令；
- MySQL关键字全部使用小写，标识符全部使用中文（感觉相对直观）；
一、系统服务
启动/停止/重启/查看状态：shell > service mysql start | stop | restart | status
二、连接服务器
连接： shell > mysql [-h主机名] -u用户名 -p[密码]
断开： mysql > quit | exit;
三、数据库操作
创建： create database 数据库名;
删除： drop database 数据库名;
查看定义： show create database 数据库名;
查看所有数据库： show databases;
查看当前使用的数据库： select database();
切换： use 数据库名;
四、数据库对象
1、表
创建：
create table 表名
(
列名1 数据类型 [列级约束条件] [默认值],
... ...
[表级约束条件]
);
修改： alter table 表名 [修改动作]
修改动作如下：
- 修改表名： rename 新表名;
- 修改字段名： change 旧字段名 新字段名 数据类型;
- 修改字段类型： modify 字段名 数据类型;
- 添加字段： add 字段名 数据类型 [列级约束条件] [first | after 列名];
- 删除字段： drop 字段名;
- 修改字段的排列位置： modify 字段名 数据类型 [first | after] 列名;
- 修改存储引擎： engine = 新引擎名;
- 删除外键约束： drop foreign key 外键名;
删除： drop table [if exists] 表名列表;
查看表结构：
- 基本结构： desc 表名;
- 详细结构： show create table 表名;
2、索引
创建： 索引定义块的格式为 [unique | fulltext | spatial] index 索引名（字段[列表]） [asc | desc]
- 创建表时将定义块附加在‘表级约束条件’位置;
- 在已经存在的表上创建： alter table 表名 add 定义块;
- 直接创建： create [unique | fulltext | spatial] index 索引名 on 表名（字段[列表]）;
删除：
- 修改表： alter table 表名 drop index 索引名;
- 直接删除： drop index 索引名 on 表名;
查看： show index from 表名;
3、视图
创建：
create [or replace] [algorithm = {unidefined | merge | temptable}]
view 视图名
as select 字段[列表]
from 表或视图[列表]
[with [cascaded | local] check option];
修改：
- 使用创建的命令格式修改;
- 直接修改：将创建命令中的 create [or replace] 替换为 alter ;
通过视图修改表数据：把视图当做表操作，使用表的命令格式
删除： drop view [if exists] 视图列表 [restrict | cascade];
查看信息：
- desc 视图名;
- show table status [like '匹配模式'];
- show create view 视图名;
- select * from information_schema.views [where 子句];
4、存储过程和函数
创建存储过程：
create procedure 过程名([参数列表])
特性
begin
程序体
end;
创建函数：
create function 函数名([参数列表])
returns 返回类型
特性
程序体
return 返回值;
修改特性： alter {procedure | function} name 特性;
删除： drop {procedure | function} [if exists] 名称;
调用存储过程： call 过程名(参数列表);
调用函数： select 函数名(参数列表);
查看状态：
- show {procedure | function} status [like '匹配模式'];
- show create {procedure | function} 名称;
- select * from information_schema.routines [where 子句];
5、触发器
创建：
create trigger 名称
before | after
insert | update | delete
on 表名
for each row
begin
程序体
end;
删除： drop trigger 名称;
查看：
- show triggers;
- select * from information_schema.triggers [where 子句];
五、数据处理
1、插入： insert into 表名[(字段列表)] 数据部分;
数据部分如下：
- 直接插入数据： values(数据1)[, (数据2), ... ];
- 插入查询结果： select 子句;
2、更新： update 表名 set 字段1=值1[, 字段2=值2, ... ] [where 子句];
3、删除： delete from 表名 [where 子句];
六、查询(表)
基本格式：
select [distinct] {* | 字段列表}
from 表列表
[
[where 过滤条件]
[group by 字段列表]
[having 过滤条件]
[order by 字段1[asc | desc] [,字段2[asc | desc], ... ]]
[limit [行偏移量,] 显示行数]
];
过滤条件 格式： 条件1 [and | or 条件2 ... ]
条件 格式：
- 字段 比较运算符 [all | any | exists] 结果[集]
- 字段 in 结果集
- 字段 between 下限 and 上限
- 字段 like '匹配模式'
- 字段 regexp '正则表达式'
子查询：将select 子句的查询结果作为外层查询过滤条件中的结果[集];
连接查询：
- 内连接：
select 字段列表
from 表1 inner join 表2
on 过滤条件;
- 左 | 右 外连接：
select 字段列表
from 表1 left | right outer join 表2
on 过滤条件;
七、数据备份与还原
1、数据库级别的备份与还原
备份数据库对象
基本格式： shell > mysqldump [-h主机名] -u用户名 -p[密码] 备份对象 > 目标文件.sql
备份对象：
- 单个数据库的所有表： 数据库名
- 单个数据库中的部分表： 数据库名 表列表
- 多个数据库： --databases 数据库列表
- 所有数据库： --all-databases
还原数据库对象
- shell > mysql -u用户名 -p [数据库名] < 目标文件.sql
- source 目标文件.sql
2、直接复制数据库目录（仅用于MyISAM表）
- 备份：复制整个数据库目录
- 还原：将备份文件拷贝的数据库目录，并将其用户和组改为mysql
3、表数据的备份与还原
数据导出：
- select 语句： select 子句 into outfile '目标文件' [选项列表];
- mysqldump： shell > mysqldump -T 备份路径 -uroot -p 数据库名 [表列表] [选项列表]
- mysql： mysql -uroot -p [--virtical | --html | --xml] --execute="select 语句" 数据库名 > 目标文件
数据导入：
- load 语句：
load data infile '目标文件' into table 表名 [选项列表] [ignore 行数 lines];
- mysqlimport： shell > mysqlimport -uroot -p 数据库名 目标文件 [选项列表];
八、性能优化
1、查看性能参数： show status like '参数值';
2、分析查询： explain [extended] select语句;
3、优化数据库结构：
- 禁用 | 开启索引： alter table 表名 disable | enable keys;
- 禁用 | 开启唯一性检查： set unique_checks={0 | 1};
- 禁用 | 开启外键检查： set foreign_key_checks={0 | 1};
- 禁用 | 开启自动提交： set autocommit={0 | 1};
- 分析表： analyze [local | no_write_to_binlog] table 表列表;
- 检查表： check table 表列表，option;
- 优化表： optimize [local | no_write_to_binlog] table 表列表;
九、用户管理
1、账户管理
新建普通用户：
- create 语句：
create user '用户名'@'主机名'
[
identified by [password] '密码'
| identified with auth_plugin [as '插件名']
];
- grant 语句：
grant 权限列表 on 数据库.表
to '用户名'@'主机名'
[identified by '密码']
[with grant option];
- 向user表插入数据：
insert into mysql.user(host, user, password [, pri_list])
values('主机名', '用户名', '密码' [, 权限列表]);
删除普通用户：
- 直接删除： drop user 用户列表;
- 删除user表中的行：
delete from mysql.user where host='主机名' and user='用户名';
修改密码
root用户：
- 修改自己的密码：
①shell > mysqladmin [-h主机名] -uroot -p password "新密码"
②update mysql.user set password=password("新密码");
③set password=password("新密码");
- 修改普通用户的密码：
①set password for '用户名'@'主机名'=password("新密码");
②update mysql.user set password=password("新密码") where user='用户名' and host='主机名';
③grant usage on 数据库.表 to '用户名'@'主机名' identified by '新密码';
- 找回自己的密码：
①shell > mysqld_safe --skip-grant-tables user=mysql
②shell > net stop mysql
③shell > mysqld --skip-grant-tables
④打开新终端：shell > mysql -uroot
⑤使用上面的方法设置密码
普通用户：
- 修改自己的密码： set password=password("新密码");
2、权限管理
- 授权：
grant 权限类型1[(字段列表1)] [, 权限类型2[字段列表2]] ...
on 对象类型
to '用户名1'@'主机名1'
[identified by [password] '密码']
['用户2'@'主机名2' ...]
[with grant option];
对象类型：表，函数，存储过程
- 收回权限：
revoke [all | 权限类型1[(字段列表1)] ... ]
on 表列表
from '用户名'@'主机名'列表;
- 查看权限：
①show grants for '用户名'@'主机名';
②select 权限列表 from user where user='用户名' and host='主机名';
(全文完)