MySQL 学习笔记

😀😀😀创作不易，各位看官点赞收藏.

MySQL 学习笔记

MySQL 数据库：按照一定的格式来储存数据的一些文件的组合，就是储存数据的仓库。是专门用来管理数据库中的数据的，可以对数据库中的数据进行增删改查。常见数据库管理系统：MySQL、Oracle、MS等。

MySQL数据库的官网https://www.mysql.com/

SQL 语句分类：

• DQL：数据查询语言，凡是带有select关键字的都是查询语句。
• DML：数据操作语言，凡是对数据库的增、删、改。insert、delete、update。
• DDL：数据定义语言，主要操作表的结构，凡是带有create、drop、alter。
• TCL：事务控制语言，包括事务提交（commit）、事务回滚（rollback）。
• DCL：数据控制语言，包括授权和撤销权限。

基本常用命令行：

# windows启动、停止命令(可能需要管理员权限)
net stop/start MySQL服务名称
# 命令登陆到MySQL
mysql -u[登陆用户名] -p[登陆密码]
# 退出MySQL
exit
# 展示数据库列表(要分号结尾)
show databases;
# 使用某个数据库
use [数据库名称]
# 创建数据库
create database [数据库名]
# 查看数据库下的表列表
show tables;
# 导入某个SQL资源，路径使用绝对路径，路劲不能存在中文
source 资源的路径;
# 查看表结构
desc [表名];
# 查看数据库版本号
select version();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SQL 语句注释：

• # 注释内容。
• -- 注释内容，–`后面有一个空格。
• /* 注释内容*/，多行注释。

1、DQL 查询语句

查询语句：负责数据库数据查询。关键字 select，查询语句只会查询数据，永远不会修改表中的内容。

# 最基本查询语句(查询常数)，并没有指定表，它就会指定伪表DUAL
SELECT 1,2,3
SELECT 1,2,3 FROM DUAL

# 查询表中字段，
# other在表中没有存在，可以给它指定一个默认值，
# as：表示给查询出来的字段起别名
# FROM：指定表名，这个as可以省略直接使用空格隔开
SELECT id,name,sex,password,'test' as 'other' FROM t_user

# * 会自动解析成表中所有字段，这样写会多一个步骤，不推荐
SELECT *,'test' as 'other' FROM t_user
1
2
3
4
5
6
7
8
9
10
11
12

注意：对于字符串、时间表达式、字段别名等值需要使用单引号或者双引号包裹起来。

1.1、基本查询

条件查询：查询符合条件的数据，不符合条件的就不查询出来，通过 where 关键字添加条件。

# 比较运算符

# 1. 等于 = 
select 字段 from 表 where 字段 = 条件值;
# 2.安全等于，特殊等于可以用于比较NULL值
select 字段 from 表 where 字段 <=> 条件值;
# 3.不等于 <>或!=
select 字段 from 表 where 字段 <> 条件值;
select 字段 from 表 where 字段 != 条件值;
# 4.小于 <, 小于等于 <=, 大于 >, 大于等于>=
select 字段 from 表 where 字段 < | <= | > | >= 条件值;

# 关键字运算符

# 1.字段值为 null，不能使用 字段 = null 
select 字段 from 表 where 字段 is null;
# 2.字段值不为 null
select 字段 from 表 where 字段 is not null; 
# 3.在.....之间，between ... and ...，左小又大，相当于大于等于和小于等于一起使用
select 字段 from 表 where 字段 betwee 条件值1 and 条件值2;
# 4.in，包含，相当于使用多个or，数据值是否在in这个集合中，多个匹配数据使用逗号隔开
select 字段 from 表 where 字段 in (值1,值2,值3);
# 5.not in，不包含，查询数据值不在in这个集合中的数据
select 字段 from 表 where 字段 not in (值1,值2,值3);

# 逻辑运算符

# 1.并且 and && (多个条件同时满足)，或者 or || (多个条件有一个满足)，and的优先级比or的优先级高
select 字段 from 表 where 字段1 = 条件值1 and 字段2 = 条件值2;
select 字段 from 表 where 字段1 = 条件值1 or 字段2 = 条件值2;
# 2.非 not !
select 字段 from 表 where not 字段1 = 条件值1;
# 3.异或 XOR，两边条件结果不一致返回 1
select 字段 from 表 where 字段1 = 条件值1 XOR 字段2 = 条件2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

distinct 去重：使用 distinct 关键字去除查询的重复数据。

# 按照指定字段进行去重，后面有多个字段按照多个字段进行去重
select distinct 字段1 from 表;
1
2

注意：distinct 只能使用在 select 的后面，字段的前面。如果 distinct 后面有多个字段，则是多个字段的联合，然后去除重复的数据。

# 不能写成这样
select 字段1, distinct 字段2 from 表名;
1
2

算数运算符：查询字段、过滤条件时可以使用基本数学表达式进行操作。在 SQL 中和 NULL 进行运算结果都是 NULL。

# 最基本的数学四则运算+、-、*、/、%，在 SQL 语句中 + 符号没有连接作用只有加法作用
SELECT (sex + 10) as 'sex', (money * 10) as 'money', (id/2) as 'id' FROM t_user
1
2

排序查询：在查询数据时，可以按照某个字段查询后的数据进行排序，不指定排序默认查询出来就是在数据库添加的顺序。

# 默认是升序排序 asc；降序排序，在后面添加desc，
select 字段 from 表 order by 字段 asc/desc;

# 可以按照多个字段进行排序，当某个字段相等时这时可以按照另一个字段进行排序，前面的字段起主导作用。
select 字段 from 表 order by 字段1 asc, 字段2 desc;

# 选择表中对应位置字段进行排序。
# 按照表中排第二的字段进行排序，但是在实际开发中不建议使用该方法，可能表中的字段位置可能发生变化
select 字段 from 表 order by 2;
1
2
3
4
5
6
7
8
9

模糊查询：使用 like 去匹配模糊查询结果。

• %：匹配多个任意字符，例如：%三，表示以三结尾的数据。
• _：匹配任意一个字符，例如：_三，表示任意一个字符开头，三结尾的数据，数据只有两个字符。

# 左模糊查询
select 字段 from 表 where 字段 like '%张';
# 右模糊查询
select 字段 from 表 where 字段 like '张%';

# 正则表达式条件查询，使用 REGEXP或者RLIKE关键字
select 字段 from 表 where 字段 REGEXP | RLIKE '正则表达式';
1
2
3
4
5
6
7

注意：在查询时，遇到特殊字符，例如 _ 和 % 需要使用转义字符来转义，select 字段名 from 表名 where 字段名 like '%\_%';

limit 分页：用于限制查询出来数据的条数，一般用于分页查询。limit 后面需要指定数据开始的下标和查询的长度，它的下标是从0 开始的。

# 查询下标开始后面指定长度的数据条数
select 字段 from 表名 limit 下标,长度;
1
2

分页查询：

• 实现分页查询，一般是从前端传入一个页码和每一页的数据条数，然后执行相应的sql语句。
• limit 下标公式：下标 = (页码 - 1) * 每页条数，每页条数一般就是作为长度。

1.2、函数查询

MySQL 提供了许多内置函数，涵盖了各种数据类型和操作。这些函数可以用于处理字符串、数字、日期、时间等各种数据。

• 单行函数：一条数据输入，对应一条数据输出。
• 聚合函数：输入一组数据，最终输出一行数据，多组数据有多条结果，相当于数据的统计。

1.2.1、单行函数

数值函数：用于计算的一些函数。

字符串函数：操作字符串的一些内置函数。

获取日期函数：

日期格式化与解析：

MySQL 中日期格式：默认格式：%Y-%m-%d，%Y：表示年；%m：表示月；%d：表示天；%H：表示时；%i：表示分；%s：表示秒

# 字符串转换成date日期，str_to_date
insert into 表名
	(日期字段)
values 
	(str_to_date('01-11-1999','%d-%m-%Y'));
	
# 日期按照指定格式转换成字符串，date_format
select date_format(字段,日期格式) from 表;
1
2
3
4
5
6
7
8

流程控制函数：

加密解密函数：需要匹配加密后的内容，直接将用户输入内容进行加密然后再进行匹配。

MySQL 信息函数：

1.2.2、聚合函数

聚合函数是作用于一组数据，并且对一组数据返回一个值，聚合函数在使用时需要进行分组，然后才能使用，如果没有分组，默认整张表就是一组。

# 常见聚合函数

# 1.求平均值（针对数字），avg
select avg(字段) as 别名 from 表;
# 2.求和（针对数字），sum
select sum(字段) as 别名 from 表;
# 3.最大值/最小值，max/min
select max(字段)/min(字段) as 别名 from 表;

# 4.指定字段在查询结果中的个数，count
select count(字段) as 别名 from 表;
1
2
3
4
5
6
7
8
9
10
11

count() 函数：

• count(字段)：统计这个字段不为 NULL 的数据条数。
• count(*)、count(1)：统计查询结果的所有条数。

注意事项：

1. 聚合函数自动忽略 null 值得数据记录，不需要对 null 的数据处理。
2. 聚合函数不能使用在 where 语句中。因为聚合函数使用前必须先进行分组，但是 where 语句得执行顺序比分组 group by 先执行。
3. 所有得聚合函数不能嵌套使用。

分组查询：将数据按照一定字段进行分组，然后对每组数据进行处理。

1. 分组函数后的 select 只能查询到参与分组的字段、聚合函数所取到的值。如果存在其它字段在其它数据库中会出错。
2. 分组函数可以对多个字段进行分组查询，多个字段使用英文逗号隔开。

WITH ROLLUP：它会将分组后的结果在当成一个分组然后进行操作，相当于是多一条当前分组后的操作数据，但是不能和 ORDER BY 一起使用，因为最后一条数据是分组后数据的操作数据，再进行分组就会矛盾。

HAVING 关键字：
用于对分完组的数据进行筛选，与 group by 联合使用，不能单独使用，也不能代替 where。能用 where 就用where，where 不行的再使用 having。

select 字段 from 表 where 条件 group by 字段 having 条件;
1

1.3、复杂查询

1.3.1、连接查询

连接查询：两张表或多张表联合起来查询数据称为连接查询。

• 内连接：两张表之间没有主次之分，没有匹配就不显示。
  1. 等值连接：表与表连接的条件时某个字段的值相等。
  2. 非等值连接：表与表连接的条件时某个字段的值不相等。
  3. 自连接：一张表自己与自己关联 查询。
• 外连接：
  1. 左外连接：左边表为主表，显示左表所有数据，右表没有匹配的查询字段显示为 NULL。
  2. 右外连接：右边表为主表，显示中的所有数据，没有匹配的也会显示。
• 全连接：两张表都为主表，两张表的所有数据都会展示出来，不匹配的字段显示为 NULL。

笛卡尔积现象：当两张或多张表进行连接查询时，没有任何的条件限制，最终的查询结果条数是两张表条数的乘积，这个现象称为笛卡尔积现象。例如：select 字段 from 表1,表2; —> 查询的数据就是表1数据的条数乘以表2数据的条数。

如何避免笛卡尔积？ 在进行多表联查的时候，对查询的条件进行加限制。

# 虽然避免了笛卡尔积现象，但是表之间的比配次数还是没有减少，就是表1和表2数据记录数的乘积，将每张表的关联关系作为条件
# 不加连接条件默认是 CROSS JOIN 表之间全部匹配，就会出现笛卡尔积现象 
select 字段 from 表1,表2 where 表1.字段 = 表2.字段;
1
2
3

注意：表的连接次数越多，查询的效率越低，尽量避免表之间的连接。

内连接：

# inner 可省略默认是内连接
select 字段 from 表1
inner join 表2
on 连接的表达式 where 条件

# 连接的表达式是一个值相等关系就是等值连接，如果是一个不相等关系就是一个非等值连接。
1
2
3
4
5
6

外连接：

# outer可省略
select 字段 from 表1
left/right/full outer join 表2 
on 条件表达式 where 条件 

# 左外连接和右外连接是可以相互转换的，只是表的位置不同，更具需求来写`sql`语句。
1
2
3
4
5
6

union 合并查询：将两条 SQL 查询出来的结果合并成一个集合，并且去掉重复数据。

select 。。。
union [all] # 加上all就不去掉重复数据
select 。。。

# union 对应的每个sql语句的查询字段要相同
1
2
3
4
5

自然连接：会将两张表中所有 相同字段，进行等值连接。

# 自动将两张表中相同字段进行等值连接，但是灵活性不强
select 。。。
from 。。。
NATURAL JOIN 。。。
1
2
3
4

注意：

• 一条sql语句中，内连接和外联接可以同时存在。

• 外连接查询的数据数，一定比内连接查询的数据数多。

1.3.2、子查询

子查询：指 select 语句中嵌套 select 语句，被嵌套的 select 语句称为子查询，子查询查询结果会被外查询使用的。

where 中子查询：
在 where 的后面出现 select 子查询，一般子查询的结果作为 where 的条件判断依据。

# 只有当子查询出来的结果条数大于3时，主查询才会满足条件
select distinct name 
from user u1
where 
	(select count(*) from user u2 where u1.name=u2.name) > 3
1
2
3
4
5

from 中子查询：
把 from 中的子查询的结果当成一张临时的表提供给主查询进行查询。

SELECT
	字段 
FROM
	( SELECT 字段 FROM 表名 ) AS 临时表的别名 
WHERE
	条件表达式
1
2
3
4
5
6

注意：临时表必须起别名，不然无法在 where 语句中使用表。临时表中的字段也是要取别名的。

case 流程中子查询：使用子查询在流程控制中作为条件。

SELECT
	id,
	`name`,
	( CASE salay_level WHEN SELECT MIN( salay ) FROM...THEN 'A' ELSE 'B' END CASE ) AS salay_level 
FROM
	...
WHERE
	...
1
2
3
4
5
6
7
8

子查询函数：通过 any、all、some 函数前面使用运算符 (>、<等 )去匹配子查询结果。

• any： 匹配子查询的任意结果。
• all：需要匹配子查询的所有结果。
• some：与 any 用法一样，匹配子查询的某一个结果。

SELECT ...
FROM ...
WHERE ... 运算符 any/all/some( 子查询)
1
2
3

相关子查询：子查询执行依赖于外部查询，在子查询中表用到外部表并作为条件进行关联，因此没执行一步外部查询子查询都要执行一次。

SELECT ...
FROM t1
WHERE (
	SELECT ...
	FROM t2
	# 子查询查询条件依赖于外部传入
	WHERE t2... = t1...
)
1
2
3
4
5
6
7
8

EXISTS 和 NOT EXISTS：子查询中的是否存在记录数据。

• EXISTS：如果在子查询中存在了满足的一条记录就返回 true 就不在继续查询，如果不存在继续查询。
• NOT EXISTS：相反如果不存在就返回 true。

SELECT ...
FROM ...
WHERE EXISTS/NOt EXISTS (子查询结果)
1
2
3

1.4、SQL 语句 执行顺序

在 SQL 语句中有自己的执行顺序，理解执行顺序才能写出正确的 SQL 语句。

# 标准 SQL 执行语句
SELECT 
DISTINCT # 去重
.... # 查询字段
FROM .... # 查询表
JOIN .... ON ... # 连接表
WHERE ... # 不含有聚合函数的过滤条件
GROUP BY ... # 分组
HAVING ... # 含有聚合函数的过滤条件
ORDER BY ... # 排序
LIMIT ... # 分页
1
2
3
4
5
6
7
8
9
10
11

执行顺序：

• 首先执行 FROM，先知道从哪个表中获取数据。
• 然后连接查询表，通过 ON 连接条件过滤掉一部分数据。
• 再执行 WHERE，过滤掉表中不符合条件的数据。
• 再执行 GROUP BY，给过滤好的数据进行分组，没有指定分组默认是所有的数据为一组。
• 再执行 HAVING，HAVING 必须和 GROUP BY 同时使用，是将分组的数据进行再次过滤。
• 再执行 SELECT ，选取出满足条件的所有数据。
• DISTINCT，去掉查询到的重复数据。
• 再执行 ORDER BY，将查询的数据按照指定字段进行排序。
• 最后执行分页 LIMIT。

2、DDL 定义语句

DDL：数据定义语言，包含表的创建、修改、删除等等操作。

2.1、MySQL 数据类型

常用数据类型：

• VARCHAR：可变长度的字符串，根据实际的数据来动态分配空间，节省空间，最长255。
• char：定长的字符串，不管实际长度多少，都会分配固定的长度来储存数据，可能会导致空间的浪费，最长255。
• int：数字中的整型，等同于Java中的 int。
• bigint：数字中的长整型，等同于Java中的 long。
• float：单精度的浮点型数据，可以指定数据长度，整数位超过长度报错，小数超过长度进行四舍五入。
• double：双精度的浮点型数据。
• DECIMAL：定点数据类型，精度更高。
• BIT：位类型，用于存储 0、1，不能超过这个值，可以指定二进制存储的长度。
• date：短日期类型，日期默认格式：%Y-%m-%d。
• datetime：长日期类型，格式：%Y-%m-%d %h:%i:%s，占用8个字节。
• TIMESTAMP：表示带有时区的时间，格式：%Y-%m-%d %h:%i:%s，占用4个字节，但是存储范围小一些。
• longtext：大文本存储类型，可以存储 4GB 的文本数据。
• enum：枚举类型，字段只能存储列举出来的一个枚举值。
• SET：集合类型，集合元素最多 64 个，可以使用集合中一个或多个元素可多选，但是不能重复选择。
• longblob：二进制大对象，专门用来储存图片、声音、视频等，往该类型的字段插入数据的时候通常使用IO流来完成，最大存储 4GB。
• json：能够存储 json 字符串，对字符串进行自动验证。

注意：当数据为固定长度的时候使用char，这样的速度较快，不用动态分配空间。当数据为可变长度的时候，使用varchar这样可以避免空间的浪费。

2.2、DDL 常见操作

数据库命令：

# 创建数据库：
# 方式一：默认字符集是 mysql 服务器中database的字符集
CREATE DATABASE 数据库名;
# 方式二：创建数据库指定字符集
CREATE DATABASE 数据库名 CHARACTER SET '字符集';
# 方式三：如果数据库不存在就创建，存在就不会创建，上面两种方式如果数据库存在再创建就会报错
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET '字符集';

# 操作数据库：
# 查看当前数据库有哪些
SHOW DATABASES;
# 切换数据库
USE 数据库名;
# 查看数据库下的表，不指定数据库则是当前数据库下的表
SHOW TABLES [FROM 数据库名]

# 修改数据库：
# 修改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET '字符集'

# 删除数据库
DROP DATABASE IF EXISTS 数据库名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

表操作命令：

# 创建表：
# 方式一：最后一个字段的数据类型的后面不能有逗号，否则报错。
CREATE TABLE 表(
 	字段1 数据类型 [约束] [默认值] [CHARACTER SET 字符集],
    字段2 数据类型,
    字段3 数据类型 
)ENGINE=InnoDB DEFAULT CHARSET=utf8; # 指定字符集和存储引擎
# 方式二：将表中的数据查询出来，数据和表结构都会复制到新表中
# 用于复制表时可能出现表默认值不能赋值
CREATE TABLE 新表 AS SELECT 复制的字段 FROM 旧表

# 修改表
# 重命名表名 
RENAME TABLE 旧表名 TO 新表名;
ALTER TABLE RENAME 旧表名 TO 新表名;

# 修改表字段
# 增加字段，可以指定新字段添加的位置
ALTER TABLE 表名 ADD 字段名 类型 [FIRST|BEFORE|AFTER 字段名] ;
# 修改字段，一般不会修改字段类型
ALTER TABLE 表名 MODIFY 字段 [长度] [默认值];
# 重命名字段
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 [类型|长度|默认值];
# 删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;

# 删除表
DROP TABLE IF EXISTS 表名;
truncate table 表名;
# 清空表
TRUNCATE TABLE 表名;
DELETE FROM 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

• DELETE：删除表中的数据，如果不添加条件限制则是删除表中所有数据，这种删除会把删除日志记录，支持事务回滚操作。
• TRUNCATE：用于清空表中的所有数据，会保留表的结构、属性、索引等，truncate 会重置自增列的值。执行后立即生效，数据无法回滚删除效率高。
• DROP：删除表的数据和结构，将表所占用的空间全释放掉，无法进行事务回滚操作。

3、DML 操作语句

INSERT：用于向 MySQL 数据库中添加数据。

# 新增单挑数据
INSERT INTO 表 (字段1,字段2,字段3) VALUES (值1,值2,值3);

# 批量添加数据
INSERT INTO 表 (字段)
VALUES
	(一条数据),
	(两条数据),
	(三条数据);
	
# 将查询结果作为数据添加到表中，添加数据字段与查询字段需要保持一致，不然会报错 
INSERT INTO 表 SELECT 字段 FROM 查询表;
1
2
3
4
5
6
7
8
9
10
11
12

注意：

• 字段的顺序可以和表中的字段顺序不一样，但是值的顺序要和前面指定的字段顺序对应。
• 如果缺少一些表中得字段，那些缺少得字段则会为默认值，如果没有设置默认值，则值为 NULL。
• 如果所有得字段都不写，默认是插入全部得字段，后面得值要全部填上。

UPDATE：通过 SQL 语句去修改表中指定的数据。

# 注意：在修改时没有条件来限制的的话，它会修改表中的全部数据。
UPDATE 表 SET 字段1=值1,字段2=值2 WHERE 条件表达式;
1
2

DELETE：通过 SQL 语句删除表中的数据。

# 注意：如果没有条件表达式，则会删除表中的所有数据，可以用于清空表中的数据。
DELETE FROM 表 WHERE 条件表达式;

# 连接删除
# 可以删除两个表中条件满足的数据
DELETE m,u
FROM 表1 JOIN 表2 ON 连接条件
WHERE ... 
1
2
3
4
5
6
7
8

注意：

• 只是删除表中的数据，表的结构没有删除，例如索引、约束等，可以重置自增列的值。
• 支持事务回滚，删除后可以后悔，但是效率低下。

MySQL8.0 新特性计算列：在创建表时可以指定字段为计算列，当表中其他列数据发生变化可以重新计算出新值。

# 创建计算列，字段c为计算列，会根据a、b字段的变化而变化
CREATE TABLE IF NOT EXISTS 表名(
	a int,
	b int,
	c int GENERATED ALWAYS AS (a + b) VIRTUAL
);
1
2
3
4
5
6

4、数据约束（constraint）

数据约束：创建表时，可以给字段添加一些约束，在插入数据时必须满足这些约束来保证数据的完整性。

1. 非空约束（NOT NULL）：插入数据字段值不能为空，如果不设置非空约束，默认数据库为空是 NULL。
2. 唯一性约束（UNIQUE）：字段的值不能重复，但是可以为 NULL，多个 NULL可以重复，可以设置多个字段组合唯一性约束。添加唯一性约束会自动创建唯一索引，删除唯一性约束只能通过删除唯一性索引去删除。
3. 主键约束（PRIMARY KEY）：数据不能为空并且所有数据必须唯一，任何一张表都必须要有一个主键，相当于数 非空约束 + 唯一性约束，可以设置多个字段为复位主键约束。
4. 自增约束：当字段是整型时，添加自增约束，插入数据时不设置该字段值，默认会递增方式填充。
   • 一个表只有一个自增长列。
   • 自增长列必须是主键列，唯一并且不能为空。
   • 当插入数据的自增字段值为 0 或者 NULL，实际上会自动添加成最大自增值。
5. CHECK 约束：对数据进行校验约束，MySQL5.7 之前不支持 CHECK 约束，但是 MySQL8.0支持约束。
6. DEFAULT 约束：在插入数据时没有显示赋值，设置了默认值就用默认值填充。

# 创建表时添加对应的约束
CREATE TABLE IF NOT EXISTS 表(
    # 列级约束
	字段1 数据类型 NOT NULL, # 非空约束
    字段2 数据类型 UNIQUE, # 一个字段的唯一性约束
    字段3 数据类型 PRIMARY KEY, # 主键约束
    字段4 数据类型 AUTO_INCREMENT, # 自增约束
    字段5 数据类型 CHECK(校验条件，boolean值), # CHECK 约束
    字段6 数据类型 DEFAULT 默认值 # 默认值约束
    
    # 表级约束 
    UNIQUE(字段1,字段2), # 多个字段的联合性唯一
    PRIMARY KEY(字段1,字段3) # 多个字段的联合主键约束
);

# 对于已存在表修改字段约束
ALTER TABLE 表名 MODIFY 字段 类型 约束;
# 删除表索引
ALTER TABLE 表名 DROP INDEX 索引名
# 删除表主键约束
ALTER TABLE 表名 DROP PRIMARY KEY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

外键约束（FOREIGN KEY）：两个表之间，子表引用父表中指定字段的数据，只能使用这些数据不能使用其它的数据。

create table if not exists t_class(
    c_no int(11) primary key, // 被引用的字段
    c_name varchar(255)
);
1
2
3
4

create table if not exists t_student(
	stu_no int(11) primary key,
    stu_name varchar(255),
    c_no int, // 被设置外键的字段
	foreign key (c_no) references t_class(c_no)  // 这是设置外键的语法
);

# 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名 
1
2
3
4
5
6
7
8
9

​ 两个表，t_class(父表)是班级表，t_student(子表)是学生表，子表中创建一个外键 c_no，引用父表中的 c_no 字段。在子表中这个字段值只能是父表中对应字段的值。

注意：

1. 创建表时，要先创建父表，然后创建子表。
2. 删除表时，要先删除子表，然后删除父表。
3. 删除数据时，要删除子表中的数据，然后才能删除父表中的数据。
4. 插入数据时，要插入父表中的数据，然后才能在子表中插入数据。
5. 外键的值可以为空，但父表中字段值必须是唯一的。

5、视图 View

视图：一个或多个数据表里的数据的逻辑显示，视图并不存储数据。

• 视图是一种虚拟表，不存储数据，占用空间小。
• 视图的创建、删除只会影响本身，不会影响基表 (创建视图的表)，对视图中数据的增加、删除、修改会对基表也做修改，基表的数据发生变化，视图中的数据也会变化。
• 视图可以看成存储起来的 SELECT 语句，对于复杂的 SELECT 语句可以用视图进行代替来减少重复 SQL 的编写。
• 创建视图后，可以将视图看做表进行操作，简便开发中编写重复 SQL。

# 创建视图，SELECT 语句就是查询对应的表数据，创建的视图字段名和 SELECT 语句查询出来的字段名一样
CREATE VIEW 视图名 AS SELECT ...

# 创建视图也可以自定义视图的字段名称，名称与 SELECT 查询的字段需要一一对应 
CREATE VIEW 视图名(字段名...) AS SELECT ...

# 基于视图创建视图，SELECT 查询视图中的数据
CREATE VIEW 视图名称 AS SELECT ...

# 修改视图
CREATE OR REPLACE VIEW 视图名称 AS SELECT ...
ALTER VIEW 视图名称 AS SELECT ...

# 删除视图，删除视图不会对基表有影响，但是删除视图中的数据基表中对应数据也会删除 
DROP VIEW IF EXISTS 视图名称;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

注意：

• 如果视图数据来之一张表，可以对数据进行更新，但是如果数据来之不同表或者 SELECT 语句中出现了 GROUP BY\ HAVING \UNION \UNION ALL、聚合函数等，或者视图来之一张不可更新的视图，那么视图的数据不能进行更新操作。
• 如果视图是根据另一个主视图创建，主视图删除了会导致从视图查询失败。

6、存储过程与函数

存储过程：一组经过预编译的 SQL 语句的封装，存储过程存储在 MySQL 服务器上，需要执行的时候客户端发出调用命令，服务端就将存储过程执行一次。

• 简化操作，提高 SQL 语句的重用性。
• 减少网络的传输，提高性能。

# 存储过程的基本语法
/*
1.括号中无参数
2.IN 类型参数，有输入值
3.OUT 类型参数，有返回值
4.INOUT 类型参数，既是输入值也是返回值
*/
# 若参数不写 IN或者OUT，默认是IN类型参数
# DELIMITER $：表示是结束标识符
DELIMITER $
CREATE PROCEDURE 存储过程名称 (IN|OUT|INOUT 参数名称 参数类型)
BEGIN
	存储过程体;
END $
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 调用存储过程
CALL 存储过程名称(参数);
1
2

# OUT 类型的存储过程
DELIMITER $
CREATE PROCEDURE test1 (OUT param 参数类型)
BEGIN
	# 将查询出来结果写入返回值中
	SELECT 字段 INTO param ...
END $
DELIMITER ;

# 调用存储函数
CALL test1(param);
1
2
3
4
5
6
7
8
9
10
11

存储函数：用户自定义函数，使用函数可以对数据进行各种操作，定义完成后可以像调用 MySQL 内置函数一样调用。

# 存储函数的所有参数都是 IN 类型参数
CREATE FUNCTION 函数名(参数名 参数类型 ...)
RETURN 返回值类型
BEGIN
	# 函数体中一定有return返回值
	 return 函数体....
END

# 调用函数 
SELECT 函数名();
1
2
3
4
5
6
7
8
9
10

查看、修改、删除存储过程和函数：

# 查看
SHOW CREATE [PROCEDURE | FUNCTION] 存储过程或函数名

# 修改
ALTER [PROCEDURE | FUNCTION] 存储过程或函数名 具体存储过程或函数

# 删除
DROP [PROCEDURE | FUNCTION] IF EXISTS 存储过程或函数名
1
2
3
4
5
6
7
8

7、MySQL 变量

变量：可以使用变量来存储查询计算的结果，在 MySQL 中分为系统变量和用户自定义变量。

7.1、系统变量

• 全局系统变量：需要添加 global 关键字，全局变量针对的是所有会话都共有的变量，重启 MySQL 服务会导致全局系统变量失效。
• 会话系统变量：需要添加 session 关键字，会话系统变量针对的是某一次客户端连接会话，会话与会话之间相互不干扰。

# 查看所有系统变量
SHOW [GLOBAL|SESSION] VARIABLES;
SHOW VARIABLES; # 默认查询会话系统变量

# 查看部分系统变量
SHOW [GLOBAL|SESSION] VARIABLES LIKE '模糊匹配变量名';

# 查看指定系统，MySQL 中使用 @@ 表示系统变量，例如：@@global 表示全局系统变量
SELECT @@global.变量名;
SELECT @@session.变量名;
SELECT @@变量名; # 默认先查询会话系统变量再查询全局系统变量
1
2
3
4
5
6
7
8
9
10
11

操作系统变量：系统变量一般是初始化 MySQL 服务时从配置文件读取或者系统初始化加载的，重启 MySQL 服务，对于后期设置的系统变量会失效。

# 设置系统变量值
SET @@[global|session].变量名 = 变量值;

SET GLOBAL 变量名 = 变量值;
SET SESSION 变量名 = 变量值;
1
2
3
4
5

7.2、用户自定义变量

• 会话用户变量：作用于当前会话连接，断开连接变量失效，用户变量以 @ 符号开头。
• 局部变量：只在 BEGIN 和 END 语句中有效，局部变量只能在存储过程和函数中使用。

# 定义会话用户变量
# 方式一：
SET @变量名 = 值;
SET @变量名 := 值;
# 方式二：
SELECT @变量名 := 表达式 FROM ...
SELECT 表达式 INTO @变量名 FROM ...

# 例如：
SET @a = 1;
SET @b = 2;
SET @c = @a + @b;
SELECT @count := COUNT(*) FROM 表
SELECT COUNT(*) INTO @count FROM 表
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 定义局部变量
BEGIN
	# 定义局部变量
	DECLARE 变量名 类型 default 默认值;
	# 赋值，和用户变量赋值类似
	SET 变量名 = 值;
END
1
2
3
4
5
6
7

8、触发器

触发器：由某个时间触发某个操作，例如发生插入、更新、删除等操作时，触发器就会自动触发执行定义的相关操作。

# 创建触发器
DELIMITER $
CREATE TRIGGER 触发器名称
# 设置触发时机以及触发事件，在之前还是之后
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表 # 设置触发器作用的表
FOR EACH ROW 
BEGIN 
	触发执行的语句块;
END $
DELIMITER ;
# 创建好触发器后，发生对应的事件就会自动执行定义的 SQL 语句
1
2
3
4
5
6
7
8
9
10
11

# 触发器在执行时可以获取到触发事件的数据
DELIMITER $
CREATE TRIGGER test
BEFORE INSERT ON table
FOR EACH ROW 
BEGIN 
	SELECT ... FROM .... NEW # 表示添加时对应的数据。
END $
DELIMITER ;
1
2
3
4
5
6
7
8
9

# 查看触发器
SHOW TRIGGERS 触发器名称;
# 删除触发器
DROP TRIGGER 触发器名称;
1
2
3
4

9、MySQL8.0 新特性

9.1、窗口函数

序号函数（ROW_NUMBER）：类似于分组函数，它不会将一组的数据放在一起而是一组的数据都是占用一行并且携带序号，可以根据某个字段进行排序，每一组都有自己组的序号。

# PARTITION BY: 按照字段进行分组
SELECT ROW_NUMBER() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表;
1
2
3
4

RANK 函数：对序号进行并列排序，并且会跳过重复的序号，相同成绩并列，下一个成绩跳一个序号。例如：1、1、3、4

SELECT RANK() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表; 
1
2
3

DENSE_RANK() 函数：对序号进行并列排序，不会跳过重复的序号，例如：1、1、2

SELECT DENSE_RANK() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表; 
1
2
3

前后函数（LAG(expre, n)）：返回当前行的前 n 行的 expre 的值。

# 按照某个字段进行分组，返回组内这条记录前n行的column1的值
SELECT 字段..., LAAG(column1, n) OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS t 
FROM ...
1
2
3

LEAD(expr, n)函数返回当前行的后 n 行对应的字段数据。

FIRST_VALUE(exp)：返回组内中第一个 exp 的值。

# 按照某个字段进行分组，返回组内这条记录前n行的column1的值
SELECT 字段..., FIRST_VALUE(exp) OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS t 
FROM ...
1
2
3

• LAST_VALUE(exp)：返回组内中最后一个 exp 的值。
• NTH_VALUE(exp, n)：返回组内中第 n 个exp 对应的值。

9.2、公共表表达式

CTE：一个可以复用的子查询，查询出来的结果看成一个临时表，分为普通公共表表达式和递归公共表表达式。

# 普通公共表表达式
WITH 表达式名称 AS 子查询

# 使用公共表表达式，把它看成一个临时表
SELECT 字段
FROM ... JOIN 表达式名称 ON 关联条件
1
2
3
4
5
6

# 递归公共表表达式
WITH RECURSIVE 表达式名称 AS 子查询
1
2

SE_RANK() 函数：对序号进行并列排序，不会跳过重复的序号，例如：1、1、2

SELECT DENSE_RANK() OVER(PARTITION BY 字段 ORDER BY 字段 DESC) AS row_num
字段....
FROM 表; 
1
2
3