【mysql-基础篇】

一、mysql概述

1、数据库相关概念

• 这一部分中，我们先来讲解三个概念：数据库、数据库管理系统、SQL;

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

• 目前主流的关系型数据库管理系统

  Oracle：大型的收费数据库，Oracle公司产品，价格昂贵
  MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。
  目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本。
  SQL Server：Microsoft 公司推出的收费的中型数据库，C#、.net等语言常用。
  DB2：IBM公司的大型收费数据库产品。
  PostgreSQL：开源免费的中小型数据库。
  SQLLite：嵌入式的微型数据库。Android内置的数据库采用的就是该数据库。
  MariaDB：开源免费的中小型数据库。是MySQL数据库的另外一个分支、另外一个衍生产品，与
  MySQL数据库有很好的兼容性。

而不论我们使用的是上面的哪一个关系型数据库，最终在操作时，都是使用SQL语言来进行统一操作，
因为我们前面讲到SQL语言，是操作关系型数据库的 统一标准 。所以即使我们现在学习的是MySQL，
假如我们以后到了公司，使用的是别的关系型数据库，如：Oracle、DB2、SQLServer，也完全不用
担心，因为操作的方式都是一致的。

2、数据模型

2.1 关系型数据库（RDBMS）

• 概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
• 而所谓二维表，指的是由行和列组成的表，如下图（就类似于Excel表格数据，有表头、有列、有行，
  还可以通过一列关联另外一个表格中的某一列数据）。我们之前提到的MySQL、Oracle、DB2、
  SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。简单说，基于二维表存储
  数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库。
  
• 特点：
  使用表存储数据，格式统一，便于维护。
  使用SQL语言操作，标准统一，使用方便。

2.2数据模型

MySQL是关系型数据库，是基于二维表进行数据存储的，具体的结构图下:

• 我们可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。
• 可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据。
• 一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包
  含多行记录。

---

二、SQL

全称 Structured Query Language，结构化查询语言。操作关系型数据库的编程语言，定义了
一套操作关系型数据库统一标准 。
1、SQL通用语法

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

2、SQL分类
sql语句，根据其功能分类，主要分为四类：DDL、DML、DQL、DCL

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

3、数据类型

• 整数类型：bit 、bool、tinyint、smallint、mediumint、int、bigint

• 浮点数类型：float、double、decimal

• 字符串类型：char、varchar、tinyblob、mediumblob、blob、longblob、tinytext、text、miediumtext、longtext

• 日期类型：Date、DateTime、Time、Year

  3.1、整数类型
  
  无符号的需要在类型后面加上unsigned
  类型（n）的说明：

  • 在开发中我们会碰到有些定义整型的写法是int(11),这种写法感觉在开发中没有什么用途，int(n)我们只要记住这两点：
  • 无论n等于多少，int永远占4个字节
  • n表示的是显示宽度，不足的用0补齐，超过的无视长度而直接显示整个数字，但这要整型设置unsigned zerofill才有效
  • int(5)输出宽度不满5时，前面用0进行填充

  3.2、浮点数类型
  
  float和double都是浮点型，而decimal是定点型。浮点型和定点型可以用类型名称后加（M,D）来表示。M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。float和double在不指定精度时，默认会按照实际的精度来显示，而decimal在不指定精度时，默认整数为10，小数为0。
  3.3、日期类型

3.4、字符串类型
  

4、DDL
Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。
数据库操作：
查询所有的数据库

show databases ;

查询当前数据库

select database() ;

创建数据库

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

删除数据库

drop database [ if exists ] 数据库名 ;

切换数据库

use 数据库名 ;

表操作：
查询当前数据库所有表：

show tables;

查询指定的表结构：

desc 表名;

查询指定表的建表语句：

show create table 表名；

创建表结构

create table tb_user(
 	id int comment '编号',
 	name varchar(50) comment '姓名',
 	age int comment '年龄',
 	) comment '用户表';

添加表字段

alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
alter table emp add nickname varchar(20) comment '昵称';

修改数据类型

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

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];
alter table emp change nickname username varchar(20) comment '昵称';

删除表字段

alter table 表名 drop 字段名;
alter table emp drop username;

修改表名

alter table 表名 rename to 新表名;

删除表

drop table [if exists] 表名;

---

5、DML
DML英文全称是 Data Manipulation Language(数据操作语言)，用来对数据库中表的记录进行增、删、改操作。
5.1添加数据
给指定字段添加数据

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.2修改数据
修改数据的具体语法为

update 表名 set 字段名1 = 值1,字段值2 = 值2,... [where 条件];
注意事项：
	修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

5.3删除数据
删除数据的具体语法为

delete from 表名 [where 条件];
注意事项：
	delete语句的条件可以有或者没有，没有的话则会删除整张表的所有数据。
	delete语句不能删除某一个字段的值（可以使用update，将该字段的值设置为null即可）

---

6、DQL
Data Query Language（数据查询语言）
DQL查询语句，语法结构如下

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

上面的完整语法进行拆分：

• 基本查询（不带任何条件）
• 条件查询（where）
• 聚合函数（count、max、min、avg、sum）
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit）

6.1基础查询
查询多个字段

select 字段1,字段2... from 表名;
select * from  表名;
注意：* 号代表查询所有字段，在实际开发中尽量少用（不直观，影响效率）

字段设置别名

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

去除重复记录

select distinct 字段列表 from 表名;

6.2条件查询
语法

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

常用的比较运算符

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

常用的逻辑运算符

逻辑运算符	功能
and或&&	并且（多个条件同时成立）
or 或双竖线	或者（多个条件任意一个成立）
not或！	非，不是

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

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

语法

select 聚合函数(字段列表) from 表名;
注意：null值是不参与所有聚合函数运算的。

6.4分组查询
语法

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

where与having的区别

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

注意事项：

• 分组之后，查询的字段一般为聚合函数和分组字段，查询字段无任何 意义。
• 执行顺序：where > 聚合函数>having
• 支持多字段分组，具体语法为：group by columnA，columnB

6.5排序查询
排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。
语法

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

排序方式

• ASC :升序
• DESC:降序

注意事项：
如果是升序，可以不指定排序方式ASC;
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；

6.6 分页查询
分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台都需要借助于数据库的分页操作。
语法

select 字段列表 from 表名 limit 起始索引, 查询记录数;
注意事项：
	起始索引从0开始，起始索引 = (查询页码 - 1)* 每页显示记录;
	分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是limit。
	如果查询的是第一页数据，起始索引可以省略，	直接简写为 limit 0;

6.7 执行顺序
在讲解DQL语句的具体语法之前，我们已经讲解了DQL语句的完整语法，及编写顺序，接下来我们来说明的是DQL语句在执行时的执行顺序，也就是先执行哪一部分，后执行哪一部分。

---

7.DCL
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户，控制数据库的访问权限。

查询用户

select * from mysql.user;

其中Host代表当前用户访问的主机，如果为localhost，仅代表只能在当前本机访问，是不可以远程访问的.User代表的是访问改数据库的用户名。在mysql中需要通过Host和User来唯一标识一个用户。
创建用户

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

修改用户密码

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

删除用户

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

注意事项：
1、在mysql中需要通过用户名@主机名的方式，来唯一标识一个用户。
2、主机名可以使用%通配。
3、这类sql开发人员操作的比较少，主要是DBA(数据库管理员)使用。

7.1 权限控制
mysql中定义了很多种权限，但是常用的就以下几种

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

查询权限

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

授予权限

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

撤销权限

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

---

三、函数

函数是指一段可以直接被另一段程序调用的程序或代码。也就意味着，这一段程序或代码在mysql中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。那么函数到底在哪使用呢？
我们先来看两个场景：

1、在企业的oa或其他人力系统中，经常会提供有这样一个功能，每一个员工登录上来之后都能够看到当前员工入职的天数。而在数据库中，存储的都是入职日期，如2000-11-12，那那如何快速计算出天数呢？
2、在做报表这类的业务需求中，我们要展示出学员的分数等级分布。而在数据库中存储的是学生的分数值，如98、74等，如何快速判定分数的等级呢？

其实，上述的这一类的需求呢，我们通过mysql中的函数都可以很方便的实现。
mysql中的函数主要分为以下四类：字符串函数、数值函数、日期函数、流程函数。

3.1 字符串函数

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

函数	功能
concat(S1,S2…Sn)	字符串拼接，将s1，s2…拼接成一个字符串
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个长度的字符串

演示如下：
concat：字符串拼接

select concat('hello','mysql');

lower：全部转为小写

select lower('HeLLo');

upper：全部转为大写

select upper('Hello');

lpad：左填充

select lpad('01'5,'-');

rpad：右填充

select rpad('01',5,'0');

trim：去除空格

select trim(' hello musql ');

substring：截取字符串

select substring('hello mysql ',1,5);

案例：

3.2 数值函数

常见的数值函数如下：

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

演示如下：
ceil：向上取整

select ceil(1.1);

floor：向下取整

select floor(1.9);

mod：取模

select mod(7,4);

rand：获取随机数

select rand();

round：四舍五入

select round(2.332,2);

3.3日期函数

常见的日期函数如下：

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

演示如下：

select curdate(); -- 当前日期  2023-06-26

select curtime(); -- 当前时间  15:38:07

select now();  --当前日期和时间2023-06-26 15:44:24

select year(now()); -- 当前年 
select month(now()); -- 当前月
select day(now()); -- 当前日

select date_add(now(),interval 70 year ); -- 增加指定得时间间隔 2093-06-26 15:47:47

select datediff('2021-10-01','2021-12-01'); -- 获取两个日期相差得天数  -61

案例：
查询所有员工得入职天数，并根据入职天数倒叙排序

select name,datediff(curdate(),entrydate)  as 'entrydays'from emp order by entrydays desc;

3.4 流程函数

流程函数也是很常见的一类函数，可以在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默认值

案例：

select if(true,'ok','erroe'); -- ok

select ifnull('ok','default'); #ok
select ifnull('','defalut'); #空
select ifnull(null,'default');# default

需求：
查询emp表的员工姓名和工作地址（北京上海一线城市，其他二线城市）

select name ,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市'end ) as '工作地址'from emp

案例：

select id,name ,(case when math>=85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学',(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end) as '英语'from score;

四、约束

4.1 概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。

分类：

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

注意：约束作用于表中的字段上的，可以在创建表、修改表的时候添加约束。

4.2约束演示

上面我们介绍了数据库中常见的约束，以及约束设计到的关键字，那这些约束我们如何在创建表、修改表的时候来指定呢。

CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年龄' ,
status char(1) default '1' COMMENT '状态',
gender char(1) COMMENT '性别'
);

4.3 外键约束

4.3.1 介绍

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
我们来看一个例子：

注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

接下来我们做一个测试，删除id为1的部门信息。

结果，我们看到删除成功，部门表不存在id为1的部门，而在emp表中还有很多的员工，关联的id为1的部门，此时就出现了数据不完整性。而想要解决这个问题就得通过数据库得外键约束。

4.3.2 语法

1、添加外键

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;

案例：
为emp表得dept_id字段添加外键约束，关联dept表的主键id。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

添加了外键约束之后，我们再到dept表(父表)删除id为1的记录，然后看一下会发生什么现象。此时将会报错，不能删除或更新父表记录，因为存在外键约束。

2、删除外键

alter table 表名 drop foreign key 外键名称;

4.3.3 删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种：

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

具体的语法为：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

演示如下：
由于NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再演示其他的两种行为：CASCADE、SET NULL。

1. cascade

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade ;

再一般的业务系统中，不会修改一张表的主键值。

五、 多表查询

我们之前在讲解SQL语句的时候，讲解了DQL语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要学习的则是多表查询操作，主要从以下几个方面进行讲解。

5.1 多表关系

项目开发中，再进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

5.1.1 一对多

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

5.1.2 多对多

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

5.1.3 一对一

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

5.2 多表查询概述

5.2.1 概述

多表查询就是指从多张表中查询数据。

原来查询单表数据，执行的SQL形式为：select * from emp;
那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept;
具体的执行结果如下:

此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录
(17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单介绍下笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。

而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。


在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。
select * from emp , dept where emp.dept_id = dept.id;

5.2.2 分类

• 连接查询
  1.内连接：相当于查询A、B交集部分数据
  2.左外连接：查询左表所有数据，以及两张表交集部分数据
  3.右外连接：查询右表所有数据，以及两张表交集部分数据
  4.自连接：当前表与自身的连接查询，自连接必须使用表别名

• 子查询
  

5.3 内连接

1.隐式内连接

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

2.显示内连接

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

案例：
A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN …
ON …
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

5.4 外连接

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：
1). 左外连接

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

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

2). 右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

案例：
A. 查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

B. 查询dept表的所有数据, 和对应的员工信息(右外连接)
由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺
序就可以了。而我们在日常开发使用时，更偏向于左外连接。

5.5 自连接

5.5.1 自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：

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

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

案例：
A. 查询员工 及其 所属领导的名字
表结构：emp

select a.name,b.name from emp a,emp b where a.managerid = b.id;

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b

select a.name '员工',b.name '领导' From emp a left join emp b on a.managerid = b.id;

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底
是哪一张表的字段。

5.5.2 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

案例：
A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们也可以通过union/union all来联合查询.

select * from emp where salary < 5000
union all
select * from emp where age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

union 联合查询，会对查询出来的结果进行去重处理。

5.6 子查询

5.6.1 概述

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); 

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

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

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

根据子查询位置，分为：

• where之后
• from之后
• select之后

5.6.2 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=

案例：
A. 查询 “销售部” 的所有员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');

B. 查询指定入职日期之后入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东
白');

5.6.3 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

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

案例：
A. 查询 “销售部” 和 “市场部” 的所有员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or
name = '市场部');

②. 根据部门ID, 查询员工信息

select * from emp where dept_id in (select id from dept where dept.name = '销售部' or dept.name ='市场部');

B. 查询比 财务部 所有人工资都高的员工信息

select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where dept.name ='财务部'));

5.6.4 行子查询

子查询返回的结果一行（可以是多列），这种子查询称为行子查询。
常用的操作符：=、<>、in、not in

案例：
A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary,managerid from emp where name='张无忌');

5.6.5 表子查询

子查询返回的结果是多行多列，这种子查询称为表字查询。
常用的操作符：in

案例：
A. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

select * from emp where (job,salary) in (select job,salary from emp where name ='鹿杖客' or name='宋远桥');

B. 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left
join dept d on e.dept_id = d.id;

5.7 多表查询案例

1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）

select a.name,a.job,a.age,b.name from emp a,dept b where a.dept_id = b.id;

2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

select e.name,e.age,d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

3). 查询拥有员工的部门ID、部门名称

select distinct d.id,d.name from emp e, dept d where e.dept_id= d.id;

4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;

查询所有员工的工资等级

-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >=
s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary
between s.losal and s.hisal;

6). 查询 “研发部” 所有员工的信息及 工资等级

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (
e.salary between s.losal and s.hisal ) and d.name = '研发部';

查询 “研发部” 员工的平均工资

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发
部';

8). 查询工资比 “灭绝” 高的员工信息。

select * from emp where salary > ( select salary from emp where name = '灭绝' ); 1

9). 查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp );

10). 查询低于本部门平均工资的员工信息

select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where
e1.dept_id = e2.dept_id );

11). 查询所有的部门信息, 并统计部门的员工人数

select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数'
from dept d;

查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

select s.name , s.no , c.name from student s , student_course sc , course c where
s.id = sc.studentid and sc.courseid = c.id ;

六、事务

6.1 事务简介

事物 是一组操作的集合，它是一个不可分割的单元，事物会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加
1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四
增加1000, 转账成功 :

异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张
三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行
完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

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

6.2 事务操作

6.2.1 未控制事务

1). 测试正常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

2). 测试异常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

我们把数据都恢复到2000， 然后再次一次性执行上述的SQL语句(出错了… 这句话不符合SQL语
法,执行就会报错)，检查最终的数据情况, 发现数据在操作前后不一致了。

6.2.2 控制事务一

1). 查看/设置事务提交方式

SELECT @@autocommit ;
SET @@autocommit = 0 ;

2). 提交事务

COMMIT; 

3). 回滚事务

ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提
交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

6.2.3 控制事务二

1). 开启事务

START TRANSACTION 或 BEGIN ;

2). 提交事务

COMMIT;

3). 回滚事务

ROLLBACK;

转账案例：

-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

6.3 事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
• 上述就是事务的四大特性，简称ACID。

6.4 并发事务问题

1). 脏读：一个事务读到另外一个事务还没有提交的数据。

2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

3).幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

6.5 事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

1). 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

2). 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。