DDL语句
DDL（data definition language）数据库定义语言：在创建表的时候用到的一些SQL，比如说：CREATE、ALTER、DROP等。DDL主要是用在操作数据库，定义或改变数据库表的结构，数据类型等初始化工作。
数据库操作
创建数据库
直接创建数据库
1
create database 数据库名;
判断数据库是否已存在，不存在则创建
1
create database if not exists 数据库名;
创建数据库并指定字符集和排序方式
1
2create database 数据库名 character set 字符集;
create database 数据库名 character set utf8mb4 collate utf8mb4_unicode_ci # 最常用的utf-8补充：查看所有字符集
1
show character set;
查看数据库
查看所有数据库
1
show databases;
查看单个数据库创建信息
1
show create database 数据库名;
修改数据库
修改数据库的字符集
1
alter database 数据库名 character set 字符集;
删除数据库
删除某个数据库
1
drop database 数据库名;
使用数据库
查看当前使用的数据库
1
select database();
使用某个数据库
1
use 数据库名;
数据库表操作
创建表
创建表结构
1
2
3
4
5
6create table 表名(
字段名1 字段类型1,
字段名2 字段类型2,
...
字段名3 字段类型3
);快速建表
1
create table 表名 like 已存在的表名; # 创建一个新表，要求表结构与某个已存在的表相同
创建表的注意事项
1
2
3
4
5
6
7
8
9表名称小写，尽量不要使用中文字符作为表名称;
表名称要与业务有关;
表名称不要以数字开头;
表名称不要太长,不建议超过15-20个字符;
不能使用保留字符串或者关键字
必须设置存储引擎和字符集;
针对不同的字段应该设置适合其的数据类型
一张表中必须要有主键
每个列都要注释
查看表
查看所有表
1
show tables;
查看单个表结构
1
desc 表名;
查看单个表的创建信息
1
show create table 表名;
删除表
直接删除表
1
drop table 表名;
判断表是否存在，存在则删除
1
drop table if exists 表名;
修改表
修改表名
1
rename table 旧表名 to 新表名;
修改字符集
1
alter table 表名 character set 字符集;
添加表字段
1
alter table 表名 add 字段名 字段类型;
修改表字段类型
1
alter table 表名 modify 字段名 字段类型;
修改表字段名
1
alter table 表名 change 旧字段名 新字段名 字段类型;
删除表字段
1
alter table 表名 drop 字段名;
存储引擎相关
1 | # 查看数据库默认的存储引擎 |
DML语句
DML（Data Manipulation Language）数据操作语言，用户通过它可以实现对数据库的基本操作。就是我们最经常用到的UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。
表记录操作
插入记录
为所有字段插入记录
1
insert into 表名 values(字段1的值,字段2的值,字段3的值);
按字段插入记录
1
insert into 表名(字段名1,字段名2) values (字段1的值,字段2的值); # 没有插入数据的字段的值为null
更新记录
更新所有记录
1
update 表名 set 字段名1=值1,字段名2=值2,字段名n=值n; # 修改表中的所有记录
更新指定范围的记录
1
update 表名 set 字段名1=值1,字段名2=值2,字段名n=值n where ... # 修改满足where条件表达式的记录
删除记录
删除表中所有记录
1
delete from 表名;
删除指定范围的记录
1
delete from 表名 where ... # 删除满足where表达式的记录
DCL语句
DCL（Data Control Language）数据控制语言， 主要是DBA 用来管理系统中的对象权限时所使用，一般的开发人员很少使用。
DCL中主要包括创建用户、给用户授权、对用户撤销授权、查询用户授权和删除用户等操作。
用户管理
查询用户
1
select user,host,password from user; # 用户信息存放在`mysql`数据库中的`user`表中
新建用户
1
2create user '用户名'@'主机名' identified by '密码'; # 主机名中可以用`%`代表通配符
create user '用户名'@'主机名' identified with mysql_native_password by '456'; # 表示指定加密方式，否则采用默认配置删除用户
1
drop user '用户名'@'主机名';
修改用户密码
1
alter user '用户名'@'主机名' identified by '密码';
情况root用户密码
1
update user set authentication_string='' where user='root';
权限管理
查看用户权限
1
show grants for '用户名'@'主机名';
授予权限
1
2grant 权限 1, 权限 2... on 数据库名.表名 to '用户名'@'主机名'; # 具体的权限包括增删改查等：select，update,select,delete等
grant all on 数据库名.* to '用户名'@'主机名'; # 一次性授予所有权限用all，授予库中的所有表的权限用*撤销权限
1
2revoke 权限 1, 权限 2... on 数据库名.表名 from '用户名'@'主机名'; # 撤销某用户的权限
revoke all on 数据库名.表名 from '用户名'@'主机名'; # 撤销所有权限
DQL语句
DQL（Data QueryLanguage ）数据查询语言，用于从数据库表中查询数据，并不会修改数据，只是一种显示数据的方式。基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。
记录查询
简单查询
显示所有字段
1
select * from 表名; # 查询表中所有记录，显示所有字段
显示表中指定字段
1
select 字段1,字段2,字段3 from 表名; # 查询表中所有记录，显示指定字段
别名查询
表别名
表别名取了一个新的名字，可以代替表名使用。表别名一般用于多表查询。
1
select 字段1,字段2,字段3 from 表名 as 表别名;
字段别名
字段别名查询出记录显示新的名字，并不影响表的结构。
1
select 字段1 as 别名1,字段2 as 别名2,字段3 as 别名3 from 表名 as 表别名;
去除重复记录查询
去除单个字段的重复记录
1
select distinct 字段名 from 表名; # 显示该字段不重复的记录
去除多个字段的重复记录
1
select distinct 字段名1,字段名2 from 表名; # 显示多个字段不同时重复的记录
运算查询
字段值与固定数值运算
1
select 字段名1+固定数值 from 表名; # 把字段1的值加上固定数值显示出来，字段1的值必须也是数值类型
字段值与字段值运算
1
select 字段名1+字段名2 from 表名; # 把字段1的值加上字段2的值显示出来，参与运算的字段必须都是数值类型
条件查询
比较运算符
1
2
3
4
5
6
7> # 大于
< # 小于
= # 等于
>= # 大于等于
<= # 小于等于
<> # 不等于
!= # 不等于，MySQL特有范围运算符
1
2
3between...and... # 在一定范围内
in(值1,值2,值3) # 在特定的值中
not in(值1,值2,值3) # 排除特定值通配符
1
2% # 匹配多个字符
_ # 匹配单个字符非空判断
1
2字段名 is null # 为空，不能写成 字段名=null
字段名 is not null # 不为空，不能写成 字段名!=null逻辑运算符
1
2
3and # 与
or # 或
not # 非
模式匹配
简单模式匹配
like关键字用于在where子句中进行简单的模式匹配
匹配多个字符
1
主查询语句 where 字段名 like '%字符串%'; # % 代表任意0个或多个字符
匹配单个字符
1
主查询语句 where 字段名 like '_字符串_'; # _ 代表任意单个字符
正则模式匹配
regexp关键字用于在where子句中进行正则表达式模式匹配
1 | [abc]： 表示括号内所列字符中的一个（类似正则表达式）。如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e” |
结果处理
结果排序
单列排序
1
2主查询语句 order by 字段名; # 将查询结果按照某字段的值升序排序（默认）：asc
主查询语句 order by 字段名 desc; # 将查询结果按照某字段的值降序排序，desc：降序组合排序
1
2主查询语句 order by 字段名1 [asc/desc],字段名2 [asc/desc],字段名3 [asc/desc];
# 按照多个字段进行排序，先按1字段排序，在按2字段排序，在按3字段排序
结果分组
直接分组显示
通过GROUP BY子句返回每个分组的行记录，分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
1
2主查询语句 group by 字段名; # 按照该字段名的值，将查询结果分组
# 当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的。分组后筛选
1
2
3
4
5主查询语句 group by 字段名 having 筛选条件; # 将分组结果筛选后再展示
/* where与having的区别
where 子句：在分组之前过滤数据，即先过滤再分组。where 后面不可以使用聚合函数。
having 子句：在分组之后过滤数据，即先分组再过滤。having 后面可以使用聚合函数。 */
聚合函数
SQL基本函数，聚合函数对一组值执行计算，并返回单个值，也被称为组函数。
聚合函数语法
1
select 聚合函数(字段名) from 表名; # 聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
常见聚合函数
1
2
3
4
5count(字段名)： 统计总行数
sum(字段名)： 计算列总和
avg(字段名)： 求某一列平均值
min(字段名)： 求某一列的最小值
max(字段名)： 求某一列的最大值除了 COUNT 以外，其他的聚合函数会忽略空值。
聚合函数拓展
1
2
3group_concat(字段名)：作为一个输出字段来使用，表示分组之后，根据分组结果，使用group_concat(字段名)来放置每一组的某字段的值的集合。
with rollup：在最后新增一行，来记录当前列里所有记录的总和，通常写在语句末尾。
分页展示
要返回前几条或者中间某几行数据，mysql提供了limit功能，limit的作用就是限制查询记录的条数。
1 | 主查询语句 limit offset,length; |
多表连接查询
连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。
内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。
内连接分为隐式内连接和显示内连接两种，两者查询效果相同，仅仅使用格式不同。
隐式内连接
1
select */字段列表 from 左表，右表 where 条件表达式; # 使用where关键字
显示内连接
1
select */字段列表 from 左表 [inner] join 右表 on 条件表达式; # 使用inner join…on语句，可以省略inner
外连接
外连接分为左连接和右连接两种。两者格式不同，查询结果也不同。
左连接
左连接表示的是在内连接的基础上保证左表的信息全部显示。用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL。
1
select */字段列表 from 左表 left [outer] join 右表 on 条件表达式; # outer可以省略
右连接
右连接表示的是在内连接的基础上保证右表的信息全部显示。用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL。
1
select */字段列表 from 左表 right [outer] join 右表 on 条件表达式; # outer可以省略
SQL子查询
查询的嵌套，当一个查询是另一个查询的条件时，称之为子查询。一个查询的结果做为另一个查询的条件,其中内部的查询称为子查询，子查询要使用括号。
子查询的结果有三种情况：单行单列、多行单列、多行多列。
单行单列子查询
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=、>=、<= 等。
1 | select */字段列表 from 数据库表名 where 字段名=(子查询); |
多行单列子查询
子查询结果只要是单行多列，结果集类似于一个数组，父查询使用in、not in运算符。
1 | select */字段列表 from 数据库表名 where 字段名 in (子查询); |
多行多列子查询
子查询结果只要是多行多列，肯定在 FROM 后面作为表，子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段。
1 | select */字段列表 from (子查询) [as] 表别名 where 条件表达式; # as 可以省略 |