MySQL学习笔记，MySQL基础-进阶-运维全流程学习

MySQL基础篇

一、MySQL概述

1. 数据库相关概念

名称	全称	简称
数据库	存储数据的仓库，数据是有组织的进行存储	DataBata（DB）
数据库管理系统	操纵和管理数据库的大型软件	DataBase Management System (DBMS)
SQL	操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准	Structured Query Language(SQL)

2. MySQL数据库

• 关系型数据库（RDBMS)

  概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

  特点:

  1. 使用表存储数据,格式统一，便于维护

  2. 使用SQL语言操作，标准统一，使用方便

     

• 数据模型

  

二、SQL

1. SQL-通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：
   • 单行注释：–注释内容 或 # 注释内容(MySQL特有)
   • 多行注释：/*注释内容*/

2. SQL-分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

3. SQL-数据类型

3.1 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

3.2 日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

3.3 字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

3.4 枚举与集合类型

• ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
• SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

3.5 空间数据类型

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

4. SQL-DDL

4.1 DDL-数据库操作

• 查询

  查询所有数据库

  show database;
  

  查询当前数据库

  select database();
  

• 创建

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

• 删除

  drop database [if exists] 数据库名;
  

• 使用

  use 数据库名;
  

4.2 DDL-表操作-查询

• 查询当前数据库所有表

  show tables;
  

• 查询表结构

  desc 表名;
  

• 查询指定表的建表语句

  show create table 表名;
  

4.3 DDL-表操作-创建

create table 表名(
	字段1 字段1类型 [comment 字段1注释],
	字段2 字段2类型 [comment 字段2注释],
	字段3 字段3类型 [comment 字段3注释],
	……
	字段n 字段n类型 [comment 字段n注释],
)[comment 表注释];

4.4 DDL-表操作-修改

• 添加字段

  alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
  

• 修改数据类型

  alter table 表名 modify 字段名 新数据类型(长度);
  

• 修改字段名和字段类型

  alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
  

• 删除字段

  alter table 表名 drop 字段名;
  

• 修改表名

  alter table 表名 rename to 新表名;
  

4.5 DDL-表操作-删除

• 删除表

  drop table [if exists] 表名；
  

• 删除指定表，并重新创建该表

  truncate table 表名;
  

5. SQL-DML

5.1 DML-介绍

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

• 添加数据（insert）
• 修改数据（update）
• 删除数据（delete）

5.2 DML-添加数据

• 给指定字段添加数据

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

• 给全部字段添加数据

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

• 批量添加数据

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

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

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一 一对应的。
• 字符串和日期类型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

5.3 DML-修改数据

• 修改数据

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

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

5.4 DML-删除数据

• 删除数据

delete from 表名 [where 条件];

注意：

• delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• delete语句不能删除某一个字段的值(可以使用update)。

6. SQL-DQL

6.1 DQL-介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

• 查询数据（select）

6.2 DQL-语法

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

6.3 DQL-基本查询

• 查询多个字段

  select 字段1,字段2,字段3……from 表名;
  select * from 表名；
  

• 设置别名

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

• 去除重复记录

  select distinct 字段列表 from 表名;
  

6.4 DQL-条件查询

• 语法

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

• 条件

  比较运算符	功能
  >	大于
  >=	大于等于
  <	小于
  <=	小于等于
  =	等于
  <> 或 !=	不等于
  between…and…	在某个范围之内(含最小、最大值)
  in(…)	在in之后的列表中的值，多选一
  like 占位符	模糊匹配(_匹配单个字符，%匹配任意个字符)
  is null	是null

  逻辑运算符	功能
  and 或 &&	并且(多个条件同时成立)
  or 或 ||	或者(多个条件任意一个成立)
  not 或 ！	非，不是

6.5 DQL-聚合函数

• 介绍

  将一列数据作为一个整体，进行纵向计算。

• 常见聚合函数

  函数	功能
  count	统计数量
  max	最大值
  min	最小值
  avg	平均值
  sum	求和

• 语法

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

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

6.6 DQL-分组查询

• 语法

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

• where与having区别

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

• 执行顺序：where > 聚合函数 > having。
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

6.7 DQL-排序查询

• 语法

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

• 排序方式

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

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

6.8 DQL-分页查询

• 语法

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

注意

• 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
• limit要放最后。

6.9 DQL-执行顺序

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

7. SQL-DCL

7.1 DCL-介绍

DCL（Data Control Language）：数据控制语言，用来管理数据库用户，控制数据库的访问权限。

7.2 DCL-用户管理

• 查询用户

  use mysql;
  select * from user;
  

• 创建用户

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

• 修改用户密码

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

• 删除用户

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

注意：

• 主机名可以使用%通配。
• 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

7.3 DCL-权限控制

权限	说明
ALL，ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

• 查询权限

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

• 授予权限

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

• 撤销权限

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

注意

• 多个权限之间，使用逗号分隔。
• 授权时，数据库名和表名可以使用*进行通配，代表所有。

三、函数

1. 函数-概念

函数是指一段可以直接被另一段程序调用的程序或代码。

2. 函数-字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

函数	功能
concat(s1,s2,…sn)	字符串拼接，将s1,s2,…sn拼接成一个字符串
lower(str)	将字符串str全部转为小写
upper(str)	将字符串str全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

select 函数(参数);

3. 函数-数值函数

常见的数值函数如下：

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x / y的模
rand()	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

4. 函数-日期函数

常见的日期函数如下：

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date 1,date 2)	返回起始时间date1和结束时间date2之间的天数

5. 函数-流程函数

流程函数是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
if(value,t,f)	如果value为true，则返回t，否则返回f
ifnull(value1,value2)	如果value1不为空，返回value1,否则返回value2
case when [val1] then [res1] …else [default] end	如果val1为true，返回res1,…否则返回default默认值
case [expr] when [val1] then [res1] …else[default] end	如果expr的值等于val1，返回res1,…否则返回default默认值

四、约束

1. 约束-概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	保证字段值满足某一个条件	check
外键约束	用来让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

2. 约束-非空约束

• 概念：指定某列的值不为空，在插入数据的时候必须非空， ‘’ 不等于null，0不等于 null。

• 添加非空约束：

  • 建表时添加

    create table t1(
    	id int not null,
    	name varchar(20) not null
    );
    

  • 通过alter语句添加

    alter table t1 modify id int not null;
    alter table t1 modify change name name varchar(20) not null;
    

• 删除非空约束：

  alter table t1 modify id int;
  alter table t1 change name name varchar(20);
  

3. 约束-唯一约束

• 概念：

  1. 指定列或列组合不能重复，保证数据的唯一性
  2. 不能出现重复的值，但是可以有多个null
  3. 同一张表可以有多个唯一的约束

• 添加唯一约束：

  • 建表时添加

    create table t1(
    	id int unique,
    	name varchar(20) unique
    );
    

    复合约束

    create table t1(
    	id int,
    	name varchar(20) not null,
    	constraint id_unique unique(id,name)
    );
    

  • 通过alter语句添加

    alter table t1 modify id int unique;
    alter table t1 add unique(name);
    alter table t1 add constraint un_id unique(id);
    

• 删除唯一约束：

  drop index on 表名;
  alter table t1 drop index id_unique;
  

  注意：如果删除的唯一约束具有自增长约束，则必须先删除自增长约束，再删除唯一约束

4. 约束-主键约束

• 概念：当前行的数据不为空并且不能重复，相当于：唯一约束+非空约束。

• 添加主键约束：

  • 建表时添加：

    create table t1(
    	id int primary key,
    	name varchar(20)
    );
    

    复合约束

    create table t1(
    	id int,
    	name varchar(20),
    	constraint id primary(id,name)
    )
    

  • 通过alter语句添加

    alter table t1 modify id int primary key;
    alter table t1 add constraint un_primary primary key(id,name);
    

• 删除主键

  alter table t1 drop primary key;
  

  注意：如果删除的主键约束具有自增长约束，则必须先删除自增长约束，再删除主键约束。

5. 约束-自增约束

• 概念：列的数值自动增长，列的类型只能是整数类型，通常给主键添加自增长约束。自增约束是MySQL的方言。

• 添加自增约束：

  • 建表时添加

    create table t1(
    	id int primary key auto_increment,
    	name varchar(20)
    );
    

  • 通过alter语句添加

    alter table t1 change id id int auto_increment;
    

• 删除自增约束：

  alter table t1 modify id int;
  

  注意：一张表只能有一个自增长列，并且该列需要定义约束

6. 约束-默认约束

• 概念：指定某列的默认值，插入数据的时候，如果此列没有值，则用default指定的值来填充。

• 添加默认约束：

  • 建表时添加

    create table t1(
    	id int default 1,
    	name varchar(20) default '张三'
    );
    

  • alter语句添加

    alter table t1 modify id int default 2;
    alter table t1 modify change name name varchar(20) default '李四';
    

• 删除默认约束：

  alter table t1 modify id int;
  alter table t1 change name name varchar(20);
  

7. 约束-检查约束

• 概念：在数据库中定义规则，确保插入或更新的数据满足特定条件

• 添加检查约束：

  • 建表时添加

    creat table t1 (
        id int,
        age int,
        constraint check_age check (age >= 18) -- 确保年龄大于等于18岁
    );
    

  • alter语句添加

    alter table t1 add constraint check_age check (age >= 18);
    

• 删除检查约束：

  alter table t1 drop constraint check_age;
  

8. 约束-外键约束

• 概念：

  • 建立表与表之间的关系，建立参照完整性，一个表可以有多个外键，每个外键必须参照另一个主键。
  • 被外键约束的列，取值必须参照其主表列中的值
  • 通常先创建主表，再创建从表

• 添加外键约束：

  • 建表时添加

    create table emp(
    	empno int primary key auto_increment,
    	ename varchar(20) not null,
    	deptno int.
    	[constraint fk_name] foreign key(deptno) references dept(deptno)
    );
    
    create table dept(
    	deptno int primary key auto_increment,
        dname varchar(20),
        loc varchar(20),
    );
    

  • alter添加

    alter table emp add constraint fk_name foreign key(deptno) references dept(deptno);
    

• 删除外键约束

  alter table emp drop foreign key fk_name;
  

  注意：

  • 在创建表时，不去明确致命外键约束的名称，系统会自动生成一个外键的名称。
  • 使用show create table 表名，查看具体的外键名称。

• 外键的删除/更新行为

  行为	说明
  no action	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 restrict一致）
  restrict	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 no action 一致）
  cascade	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
  set null	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null （这就要求该外键允许取nul）
  set default	父表有变更时，子表将外键列设置成一个默认的值（InnoDB不支持）。

  外键的行为语法：

  alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名) on update cascade on delete cascade;
  

  级联删除

  create table emp(
  	empno int primary key auto_increment,
  	ename varchar(20) not null,
  	deptno int,
  	[constraint fk_name] foreign key(deptno) references dept(deptno) on delete cascade
  );
  

  注意：

  • 插入数据时，先插入主表的数据，再插入从表的数据。
  • 删除数据时，先删除从表的数据，再删除主表的数据。

五、多表查询

1. 多表查询-多表关系

多表关系一般分为三种：一对多（多对一）、多对多、一对一

1.1 多表关系-一对多（多对一）

• 案例：部门与员工的关系
• 关系：一个部门对应多个员工，一个员工对应一个部门
• 实现：在多的一方建立外键，指向一的一方的主键

1.2 多表关系-多对多

• 案例：学生与课程的关系
• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

1.3 多表关系-一对一

• 案例：用户与用户详情的关系
• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

2. 多表查询-概述

• 概述：多表查询就是从多张表中查询数据

• 笛卡尔积：笛卡尔积是指在数学中，两个集合A和B的所有组合情况（多表查询中，需要消除无效的笛卡尔积）

  select * from A,B;
  

• 消除笛卡尔积

  select * from A,B where A.B_id = B.id;
  

• 多表查询的分类

  • 连接查询

    • 内连接：查询A、B交集部分数据

    • 外连接：

      左外连接：查询左表所有数据，以及两张表交集部分数据

      右外连接：查询右表所有数据，以及两张表交集部分数据

    • 自连接：当前表与自身的连接查询，自连接必须使用表别名

  • 子查询
    

3. 多表查询-内连接

• 内连接查询语法

  • 隐式内连接

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

  • 显示内连接

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

  内连接查询的是两张表的交集部分

4. 多表查询-外连接

• 外连接查询语法

  • 左外连接

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

    相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据

  • 右外连接

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

    相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据

5. 多表查询-自连接

• 自连接查询语法

  select 字段列表 from 表A 别名A join 表A 别名B on 条件 …;
  

  自连接查询，可以是内连接查询，也可以是外连接查询。

  自连接查询中必须对表起别名。

6. 多表查询-联合查询

• 概念：联合查询-union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

• 语法：

  select 字段列表 from 表A …
  union [all]
  select 字段列表 表B …;
  

  对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

  union all 会将全部的数据合并在一起，union会对合并后的数据去重。

7. 多表查询-子查询

7.1 子查询-概念

• 概念：SQL语句中嵌套select语句，成为嵌套查询，又称子查询

• 语法：

  select * from t1 where column1 = (select column from t2);
  

  子查询外部的语句可以是insert / update / delete / select 的任何一个。

• 子查询分类：

  根据子查询的结果不同，分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

  根据子查询的位置，分为：where之后，from之后，select之后。

7.2 子查询-标量子查询

• 概念：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

• 常用的操作符：=、<>、>、>=、<、<=

• 常见的用法

  • 子查询作为条件

    select name
    from employees
    where salary > (select avg(salary) from employees);
    

  • 子查询作为计算字段

    select name, (select max(salary) from employees) as max_salary
    from employees;
    

  • 子查询作为连接条件

    select name, department
    from employees
    where department_id = (select id from departments where name = 'Engineering');
    

7.3 子查询-列子查询

• 概念：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

• 常用的操作符：IN、NOT IN、ANY、SOME、ALL

  操作符	描述
  IN	在指定的集合范围内，多选一
  NOT IN	不在指定的集合范围内
  ANY	子查询返回列表中，有任意一个满足即可
  SOME	与ANY等同，使用SOME的地方都可以使用ANY
  ALL	子查询返回列表的所有值都必须满足

• 常见的用法

  • 作为select子句的一部分

    select name, 
           (select avg(salary) from employees where department_id = departments.id) as avg_salary
    from departments;
    

  • 作为from子句的一部分（内联视图）

    select department_name, total_salary
    from (select department_id, sum(salary) as total_salary from employees group by department_id) as dept_salaries
    join departments on departments.id = dept_salaries.department_id;
    

  • 作为where子句的一部分

    select name, salary
    from employees
    where salary > (select avg(salary) from employees);
    

7.4 子查询-行子查询

• 概念：子查询返回的结果是一行（可以是多列），这种子查询成为行子查询。

• 常用操作符：=、<>、in、not in

• 常见的用法

  • 作为where子句的一部分

    select name, department_id
    from employees
    where department_id in (select id from departments where location = 'New York');
    

  • 作为from子句的一部分（内联视图）

    select name, total_sales
    from employees,
         (select employee_id, sum(amount) as total_sales from sales group by employee_id) as employee_sales
    where employees.id = employee_sales.employee_id;
    
    

  • 作为from子句的一部分（表连接）

    select e.name,d.name as department_name
    from (select * from employees where salary > 50000) as e
    join departments as d on e.department_id = d.id;
    

7.5 子查询-表子查询

• 概念：子查询返回的结果是多行多列，这种子查询称为表子查询。

• 常用的操作符：in

• 常见的用法

  • 作为from子句的一部分（内联视图）

    select e.name, s.amount
    from employees as e
    join (select employee_id, sum(amount) as amount from sales group by employee_id) as s
    on e.id = s.employee_id;
    

  • 作为from子句的一部分（表连接）

    select e.name, d.name as department_name
    from (select * from employees where salary > 50000) as e
    join departments as d on e.department_id = d.id;
    

  • 作为from子句的一部分（union或union all运算符）

    select name, 'employee' as type from employees
    union all
    select name, 'customer' as type from customers;
    

六、事务

1. 事务-简介

• 概念：

  • 事务是数据库管理系统（DBMS）中用来管理对数据库的访问和修改的一种机制。
  • 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

• 银行转账案例-经典的事务问题

  

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

2. 事务-操作

控制事务的两种方式：

• 方式一

  • 查看 / 设置事务提交方式

    select @@autocommit;
    set @@autocommit = 0;
    

  • 提交事务

    commit;
    

  • 回滚事务

    rollback;
    

• 方式二

  • 开启事务

    start transaction 或 begin;
    

  • 提交事务

    commit;
    

  • 回滚事务

    rollback;
    

3. 事务-四大特性

3.1 原子性（Atomicity）

• 概念：原子性是指事务是一个不可分割的最小操作单元，要么全部成功，要么全部失败。
• 描述：如果事务中的任何一个操作失败，整个事务将会被回滚（撤销），即数据库状态会被恢复到事务执行前的状态，保证了数据的完整性。
• 实现：原子性可以通过事务的开始（BEGIN）、提交（COMMIT）和回滚（ROLLBACK）来实现。

3.2 一致性（Consistency）

• 概念： 一致性是指事务执行后，数据库从一个一致性状态转换到另一个一致性状态，即数据库的完整性约束没有被破坏。
• 描述：事务的一致性规定必须使所有的数据都保持一致状态。当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。
• 实现：在事务执行过程中，数据库的约束、触发器、外键等保证了数据的有效性和一致性。

3.3 隔离性（Isolation）

• 概念：隔离性是指当多个事务并发执行时，每个事务的操作都应该与其他事务的操作相互隔离。
• 描述：一个事务的操作不应该对其他事务产生影响，每个事务都应该像在独立执行一样。
• 实现：隔离性可以通过事务隔离级别来控制，常见的隔离级别包括读未提交（Read Uncommitted）、读提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

3.4 持久性（Durability）

• 概念：持久性指一旦事务被提交，其所做的修改将会永久保存在数据库中，并且不会被后续的系统故障，数据库崩溃或断电所影响。
• 描述：即使系统崩溃或数据库重新启动，事务提交的结果也应该被保持下来。
• 实现：持久性通常通过数据库的日志系统来实现，以确保事务的持久性。

4. 事务-并发问题

4.1 事务问题-脏读

• 概念：脏读是指一个事务读取了另一个事务尚未提交的数据。
• 描述：假设事务 A 修改了某些数据但尚未提交，此时事务 B 可以读取到事务 A 修改过的数据，这时候如果事务 A 回滚，则事务 B 读取到的数据就是无效的，这就是脏读。

脏读破坏了事务的隔离性，可能会导致不一致的结果。

4.2 事务问题-不可重复读

• 概念：不可重复读是指在同一个事务中，两次读取同一行数据得到的结果不一致。
• 描述：假设事务 A 在读取某一行数据后，事务 B 修改了该行数据并提交，然后事务 A 再次读取该行数据，发现两次读取的结果不一致，这就是不可重复读。

不可重复读破坏了事务的隔离性，使得同一个事务内部看到的数据状态不一致。

4.3 事务问题-幻读

• 概念：幻读是指在同一个事务中，两次执行相同的查询得到的结果集不一致。
• 描述：假设事务 A 在执行一次查询时得到了一组符合条件的数据，然后在执行相同的查询时，发现又多出了一些新的数据行，这就是幻读。

幻读通常发生在事务中插入或删除操作导致的数据集合变化，破坏了事务的隔离性。

5. 事务-隔离级别

隔离级别-并发问题对应表

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	√	√	√
读已提交（Read Committed）	×	√	√
可重复读（Repeatable Read）（MySQL默认）	×	×	√
串行化（Serializable）	×	×	×

5.1 读未提交（Read Uncommitted）

• 概念：在该隔离级别下，一个事务可以读取另一个事务尚未提交的数据。
• 问题：可能会导致脏读、不可重复读和幻读等并发问题。
• 特点：事务的隔离性最低，性能最高，适用于对数据一致性要求较低的场景。

5.2 读已提交（Read Committed）

• 概念：在该隔离级别下，一个事务只能读取另一个事务已经提交的数据。
• 问题：避免了脏读，但可能出现不可重复读和幻读。
• 特点：事务的隔离性比读未提交要高，但仍然存在一定的并发问题。

5.3 可重复读（Repeatable Read）

• 概念：在该隔离级别下，一个事务在执行期间多次读取同一行数据时，得到的结果保持一致。
• 问题：避免了不可重复读，但可能出现幻读。
• 特点：数据库会在事务开始时记录快照，事务期间其他事务对数据的修改不会影响到当前事务的查询结果。

5.4 串行化（Serializable）

• 概念：在该隔离级别下，事务被串行执行，相当于每个事务按顺序一个接一个地执行
• 问题：避免了所有并发问题，包括脏读、不可重复读和幻读。
• 特点：事务的隔离性最高，但并发性最低，性能较差，适用于对数据一致性要求极高的场景。

6.事务-隔离级别操作

• 查看事务的隔离级别

  select @@transaction_isolation;
  

• 设置事务的隔离级别

  set [session | global] transaction isolation level {read uncommited | read commited | repetable read | serializable}