DDL

数据库

  1. -- 创建数据库
  2. create database 数据库名 [character set 编码] [collate 校对规则] [if not exists 数据库名];
  4. -- 查看字符集和校对规则
  5. show character set;
  7. -- 查看所有数据库
  8. show databases;
  10. -- 查看数据库的创建信息
  11. show create database 数据库名;
  13. -- 使用数据库
  14. use 数据库名;
  16. -- 查看正在使用的数据库
  17. select database();
  19. -- 修改数据库
  20. alter database 数据库名 default character set 编码方式 collate 校对规则;
  22. -- 删除数据库
  23. drop database 数据库名 [if exists 数据库名];
  • 编码：utf8mb4、utf8 、GBK等
  • 校对规则：utf8_general_ci（不区分大小写）、utf8_general_cs（区分大小写）、utf8_bin（二进制）等

数据表

  1. -- 创建数据库
  2. create table 表名(
  3.   字段名 数据类型 完整性约束条件 [character set 字符集名称] [collate 比较规则],
  4.   字段名 数据类型 完整性约束条件,
  5.   字段名 数据类型 完整性约束条件
  6. )engine=存储引擎 default charset=编码方式;
  8. -- 查看数据表
  9. show create table 表名;
  10. desc 表名;
  12. -- 查看表的所有列信息
  13. show columns from 表名;
  15. 福塔 (Ben Forta). MySQL必知必会 (图灵程序设计丛书 97) (p. 33). 人民邮电出版社. Kindle 版本. 
  17. -- 修改表名
  18. alter table 表名 rename 新表名;
  20. -- 修改字段名
  21. alter table 表名 change 旧字段名 新字段名 新数据类型;
  23. -- 修改字段数据类型
  24. alter table 表名 modify 字段名 数据类型;
  26. -- 添加字段
  27. alter table 表名 add 字段名 数据类型 [约束条件] [first/after已存在字段名];
  29. -- 删除字段
  30. alter table 表名 drop 字段名;
  32. -- 修改字段排列位置
  33. alter table 表名 modify 字段名1 数据类型 first/after 字段名2;
  35. -- 添加外键
  36. alter table 表名 add constraint [外键名] foreign key(主表字段) references 外表表名(字段名);
  38. -- 删除外键
  39. alter table 表名 drop foreign key 外键名;
  41. -- 查看表状态
  42. show table status like '表名';
  43. show table status from 数据库名 like '表名';
  45. -- 清空表
  46. truncate table 表名;
  48. -- 查看表的约束
  49. select * from information_schema.table_constraints where table_name = '表名称';
  • 数据类型
    • 整数型：TINYINT、SMALLINT、MEDIUMINT、**INT**、BIGINT
    • 浮点型：FLOAT、DOUBLE
    • 定点数：**DECIMAL**
    • 逻辑型：BIT
    • 日期型：YEAR、TIME、DATE、DATETIME、**TIMESTAMP**
    • 文本型：CHAR、**VARCHAR**、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
    • 枚举型：ENUM
    • 集合型：SET
    • 二进制字符串：BINARY、VARBINARY、TINYBLOG、BLOG、MEDIUMBLOB、LONGBLOB
    • 其他…
  • 约束
    • primary key：主键约束
    • foreign key：外键约束
    • not null：非空约束
    • unique：唯一性约束
    • default：默认值约束
    • auto_increment：字段值自增
    • unsigned：无符号约束
    • character set：字符集约束

视图

  1. -- 创建/修改
  2. create [or replace]
  3. [algorithm = {undefined | merge | temptable}]
  4. view 视图名称 [(字段列表)]
  5. as 查询语句
  6. [with [cascaded|local] check option];
  8. -- 查看
  9. show tables;
  10. desc|describe 视图名称;
  11. show table status like '视图名称';
  12. show create view 视图名称;
  14. -- 修改
  15. alter view 视图名称 as 查询语句;
  17. -- 删除
  18. drop view if exists 视图名称 [, 视图名称2, ...];

DML

  1. -- 添加数据
  2. insert into 表名 values(值1, 值2, ......);
  3. insert into 表名(字段1,字段2,...)values(值1,值2,...);
  4. insert into 表名 set 字段名1=值1[,字段名2=值2,...];
  5. insert into 表名[(字段名1,字段名2,...)]values(值1,值2,...),(值1,值2,...)...;
  7. -- 更新数据
  8. update 表名 set 字段名1=值1[,字段名2=值2,...] [where 条件表达式];
  10. -- 删除数据
  11. delete from 表名 [where 条件表达式];
  12. truncate [table] 表名;

DQL

  1. -- 简单查询
  2. select * from 表名;
  4. -- 按条件查询
  5. select * from 表名 where 条件表达式 [and 条件表达式];
  6. select * from 表名 where 条件表达式 [or 条件表达式];
  7. select * from 表名 where 字段名 [not] in(元素1,元素2,......);
  8. select * from 表名 where 字段名 [not] between 值1 and 值2;
  9. select * from 表名 where 字段名 is [not] null;
  10. select * from 表名 where 字段名 [not] like '匹配字符串' [escape '特殊字符'];
  11. select * from 表名 where 字段名 regexp '正则表达式';
  13. -- 去重查询
  14. select distinct 字段名 from 表名;
  16. -- 排序查询
  17. select 字段名1,字段名2,... from 表名 order by 字段名1 [asc|desc],字段名2 [asc|desc]...;
  19. -- 分组查询
  20. select 字段名1,字段名2,... from 表名 group by 字段名1, 字段名2, ... [having 条件表达式] [with rollup];
  22. -- 限制查询
  23. select 字段名1,字段名2,... from 表名 limit [位置偏移量, ] 记录数;
  25. -- 取别名
  26. select * from 表名 [as] 别名;
  28. -- 交叉连接查询
  29. select * from 表1, 表2 where 表1.字段 = 表2.字段 [and 条件];
  30. select * from 表1 [[cross] join] 表2 where 表1.字段 = 表2.字段 [and 条件];
  32. -- 内连接查询
  33. select * from 表1 [inner] join 表2 on 表1.关系字段 = 表2.关系字段;
  35. -- 外连接查询
  36. select * from 表1 left | right | [outer] join 表2 on 表1.关系字段 = 表2.关系字段 where 条件;
  38. -- 自然连接
  39. select * from 表1 natural join 表2;
  41. -- USING连接
  42. select * from 表1 join 表2 using(关系字段);
  44. -- 合并查询
  45. select 字段 from 表1 UNION [ALL] select 字段 from 表2;
  47. -- 复合查询
  48. select * from 表1 where x in (select x from 表2);
  49. select * from 表1 where x = (select x from 表2);
  50. select * from 表1 where x > | < any(select x from 表2);
  51. select * from 表1 where x > | < all(select x from 表2);
  52. select * from 表1 where exists(select x from 表2);
  54. -- 总
  55. select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...;

TCL

  1. -- 开启事务
  2. start transaction;
  4. -- 提交事务
  5. commit;
  7. -- 取消事务（回滚，只能针对未提交的事务）
  8. rollback;
  10. -- 设置隔离级别
  11. set session transaction isolation level read uncommitted;
  12. set session transaction isolation level read committed;
  13. set session transaction isolation level repeatable read;
  14. set session transaction isolation level serializable;

DCL

  1. -- 创建用户
  2. create user '用户名'@'主机名' identified by '密码';
  3. insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)values('主机名','用户名',password('密码'),',',',');
  5. -- 删除用户
  6. drop user '用户名'@'主机地址';
  7. delete from mysql.user where user='用户名' and host='主机名';
  9. -- 修改root密码
  10. set password = password('新密码');
  11. update user set password=password('新密码') where user='用户名' and host='主机名';
  13. -- 修改普通用户密码
  14. grant usage on 数据库.表 to '用户名'@'主机名' identified by [password]'new_password';
  15. update mysql.user set password=password('新密码') where user='用户名' and host='主机名';
  16. set password for '用户名'@'主机名'=password('新密码');
  18. -- 普通用户修改密码
  19. set password = password('新密码');
  21. -- 授权
  22. grant 权限 [(columns)][,privileges[(columns)]] on 数据库.表 to '用户名'@'主机' [identified by [密码] [,'用户名'@'主机' [identified by 密码] ... [with grant option ...]
  24. -- 查看权限
  25. show grants for '用户名'@'主机';
  27. -- 回收权限
  28. revoke privileges [columns][,privileges[(columns)]] on 数据库.表 from '用户名'@'主机';
  29. revoke all privileges,grant option from '用户名'@'主机';
  • 权限：all privileges表示所有权限，其余可通过 show privileges 查看
  • columns：表示权限作用于某一列，省略不写则权限作用于整个表
  • grant option：将自己的权限授予其他用户

其他

  1. # 备份单个数据库
  2. mysqldump -uusername -ppassword dbname>filename.sql
  4. # 备份多个数据库
  5. mysqldump -uusername -ppassword --databases dbname1 dbname2...>filename.sql
  7. # 备份所有数据库
  8. mysqldump -uusername -ppassword --all-databases>filename.sql
  10. # 数据库还原（注意，还原的只是数据，库不能被还原）
  11. mysql -uusername -ppassword dbname<filename.sql
  13. # 修改密码
  14. mysqladmin -uusername -ppassword password 新密码