一、 DDL

1. 数据库操作

（1）查询

• 查询所有数据库

  1	show databases;

• 查询当前数据库

  1	select database();

（2）创建

• 创建数据库

  1	create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

（3）删除

• 删除数据库

  1	drop database [if exists] 数据库名

（4）使用

• 使用数据库

  1

  use 数据库名;

2. 表操作

（1）查询 show | desc

• 查询当前数据库所有表

  1	show tables;

• 查询表结构

  1 2 3

  desc 表名; describe 表名; show columns from 表名;

• 查询指定表的建表语句

  1	show create table 表名;

（2）创建 create table | create index

• 建表语句

  1	create table 表名 (列名1 数据类型 [约束] [comment 注释], 列名2 数据类型 [约束] [comment 注释], ...);

• 创建索引

  1	create [unique|fulltext|spatial] index 索引名称 on 表名(字段名称[(长度)] [asc|desc])

（3）修改 alter table

• 添加字段

  1 2	alter table 表名 add [column] 字段名 数据类型 [约束] [comment 注释];

• 添加索引

  1 2	alter table 表名 add [unique|fulltext|spatial] index 索引名称(字段名称[(长度)] [asc|desc]);

• 添加 check 约束（域完整性）

  1 2	alter table 表名 add constraint 约束名称 check (条件);

• 添加唯一约束（实体完整性）

  1 2 3

  # 唯一约束 alter table 表名 add constraint [约束名称（若不设置，则以字段名称作为约束名称）] unique (字段名称);

• 添加主键约束和外键约束（参照完整性）

  1 2 3 4 5 6 7 8 9

  # 主键约束 alter table 表名 add constraint [约束名称（在 MySql 即使设置该参数也会被忽略）] primary key (字段名称); # 外键约束 alter table 表名 add constraint 约束名称 foreign key(字段名称) references 被引用表名 (被引用字段名称); # 注意：必须先为被引用字段创建索引，通常是主键索引

• 修改数据类型

  1 2	alter table modify 字段名 新数据类型 [约束] [comment 注释];

• 修改字段名和数据类型

  1 2	alter table change 旧字段名 新字段名 新数据类型 [约束] [comment 注释];

• 修改表名

  1 2 3

  alter table 旧表名 rename to 新表名; # 或者 rename table 旧表名 to 新表名;

（4）删除 drop table | truncate table | alter table drop | drop index

• 删除表

  1	drop table [if exists] 表名;

• 删除，并重建指定表

  1	truncate table 表名;

• 删除字段

  1 2	alter table 表名 drop 字段名;

• 删除约束

  1 2 3 4 5 6 7 8 9 10 11

  alter table 表名 drop constraint 约束名; # 删除主键约束有三种写法，分别是： alter table 表名 drop primary key; # 或者 (不推荐) alter table 表名 drop constraint `primary`; # 或者 (不推荐) drop index `primary` on 表名;

• 删除索引

  1 2 3 4

  alter table 表名 drop index 索引名; # 或者 drop index 索引名 on 表名;

（5）常用的数据类型

① 数值类型

② 日期类型

③ 字符串类型

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

3. 视图操作

（1）查询

• 查询所有视图

  1 2	show tables; -- 视图和表一起显示 show full tables where table_type = 'VIEW'; -- 只显示视图

• 查询视图结构

  1 2	desc 视图名; show columns from 视图名;

• 查询视图创建语句

  1	show create view 视图名;

（2）创建

• 创建视图

  1 2 3 4

  create [or replace] [algorithm = {undefined | merge | temptable}] view 视图名 [(列名列表)] as select语句 [with [cascaded | local] check option];

• 简化语法

  1	create view 视图名 as select语句;

（3）修改

• 修改视图

  1 2 3

  alter view 视图名 [(列名列表)] as select语句; # 或者 create or replace view 视图名 as select语句;

（4）删除

• 删除视图

  1	drop view [if exists] 视图名1 [, 视图名2, ...];

二、DML

1. 插入数据 insert

（1）给指定字段添加数据

1

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);

（2）给全部字段添加数据

1

insert into 表名 values (值1, 值2, ...);

（3）批量添加数据

1
2
3
4
5
6
7
8
9
10
11

# 指定字段批量插入
insert into 表名 (字段名1, 字段名2, ...) values 
    (值1, 值2, ...),
    (值1, 值2, ...),
    (值1, 值2, ...);

# 全字段批量插入
insert into 表名 values 
    (值1, 值2, ...),
    (值1, 值2, ...),
    (值1, 值2, ...);

（4）插入查询结果

1
2

insert into 表名 (字段名1, 字段名2, ...) 
select 字段名1, 字段名2, ... from 源表名 [where 条件];

2. 修改数据 update

（1）修改数据

1

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

（2）关联修改

1
2
3

update 表1, 表2 
set 表1.字段名 = 值, 表2.字段名 = 值 
where 关联条件 [and 其他条件];

3. 删除数据 delete

（1）删除数据

1

delete from 表名 [where 条件];

（2）关联删除

1

delete 表1, 表2 from 表1, 表2 where 关联条件 [and 其他条件];

（3）删除与清空的区别

三、 DQL

1. 基础查询

（1）查询多个字段

1
2

select 字段1, 字段2, ... from 表名;
select * from 表名;  -- 查询所有字段

（2）设置别名

1

select 字段1 [as 别名1], 字段2 [as 别名2] from 表名;

（3）去除重复记录

1

select distinct 字段列表 from 表名;

2. 条件查询 where

（1）比较运算符

（2）逻辑运算符

（3）条件查询语法

1

select 字段列表 from 表名 where 条件列表;

3. 聚合函数 count | max | min | avg | sum

（1）常见聚合函数

（2）聚合函数语法

1

select 聚合函数(字段列表) from 表名;

注意：null值不参与所有聚合函数运算

4. 分组查询 group by

（1）语法

1

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

（2）where 与 having 区别

5. 排序查询 order by

（1）语法

1

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

（2）排序方式

• asc：升序（默认值）
• desc：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

6. 分页查询 limit

（1）语法

1

select 字段列表 from 表名 limit 起始索引, 查询记录数;

注意：起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数

7. 连接查询

（1）内连接

• 隐式内连接

  1	select 字段列表 from 表1, 表2 where 条件...;

• 显式内连接

  1	select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

（2）外连接

• 左外连接

  1	select 字段列表 from 表1 left [outer] join 表2 on 条件...;

• 右外连接

  1	select 字段列表 from 表1 right [outer] join 表2 on 条件...;

（3）自连接

1

select 字段列表 from 表a 别名a join 表a 别名b on 条件...;

8. 子查询

（1）标量子查询

子查询结果为单个值

1

select * from 表1 where 字段 = (select 字段 from 表2 where 条件);

（2）列子查询

子查询结果为一列

1

select * from 表1 where 字段 in (select 字段 from 表2 where 条件);

（3）行子查询

子查询结果为一行

1

select * from 表1 where (字段1, 字段2) = (select 字段1, 字段2 from 表2 where 条件);

（4）表子查询

子查询结果为多行多列

1

select * from 表1 where (字段1, 字段2) in (select 字段1, 字段2 from 表2 where 条件);

9. 多表查询案例

（1）查询语法顺序

1
2
3
4
5
6
7

select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数;

（2）执行顺序

1. from 表名列表
2. where 条件列表
3. group by 分组字段列表
4. having 分组后条件列表
5. select 字段列表
6. order by 排序字段列表
7. limit 分页参数

四、 DCL

1. 用户管理

（1）查询用户

1
2

use mysql;
select * from user;

（2）创建用户

1

create user '用户名'@'主机名' identified by '密码';

（3）修改用户密码

1

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

（4）删除用户

1

drop user '用户名'@'主机名';

（5）主机名说明

2. 权限控制

（1）查询权限

1

show grants for '用户名'@'主机名';

（2）授予权限

1

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

（3）撤销权限

1

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

（4）常用权限

（5）权限控制案例

1
2
3
4
5
6
7
8
9
10
11
12

# 创建用户并授权
create user 'test'@'localhost' identified by '123456';
grant select, insert, update on test_db.* to 'test'@'localhost';

# 查看权限
show grants for 'test'@'localhost';

# 撤销权限
revoke insert, update on test_db.* from 'test'@'localhost';

# 删除用户
drop user 'test'@'localhost';

3. 常用系统函数

（1）字符串函数

（2）数值函数

（3）日期函数

（4）流程函数