【数据库01】MySQL数据库开发基础篇

个人学习记录，参考：黑马程序员

工具

图形化工具

• Sqlyog
• Navicat
• DataGrip

SQL简介

引言：在web开发中，一般将web应用程序分为三层，即：Controller、Service、Dao。由Dao来访问数据库。

• 数据库（DataBase，简称DB）：它是存储和管理数据的仓库。
• 数据库管理系统（DataBase Management System，简称DBMS）：操作和管理数据库的大型软件。
• SQL（Structured Query Language，简称SQL）：结构化查询语言，它是操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。
  程序员给DBMS发送SQL语句，再由DBMS操作DB当中的数据。

数据模型

• 关系型数据库（RDBMS）：概念：建立在关系模型基础上，由多张相互连接的二维表（由行和列组成的表）组成的数据库。
• 基于二维表存储数据的数据库就成为关系型数据库（比如MySQL）；不是基于二维表存储数据的数据库，就是非关系型数据库（比如Redis）
• 二维表的优点：
  • 使用表存储数据，格式统一，便于维护
  • 使用SQL语言操作，标准统一，使用方便，可用于复杂查询

在Mysql数据库服务器当中存储数据，需要：

1. 先去创建数据库（可以创建多个数据库，之间是相互独立的）
2. 在数据库下再去创建数据表（一个数据库下可以创建多张表）
3. 再将数据存放在数据表中（一张表可以存储多行数据）

通用语法

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

分类

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

数据库项目开发流程

1. 数据库设计阶段
   • 参照页面原型以及需求文档设计数据库表结构
2. 数据库操作阶段
   • 根据业务功能的实现，编写SQL语句对数据表中的数据进行增删改查操作
3. 数据库优化阶段
   • 通过数据库的优化来提高数据库的访问性能。优化手段：索引、SQL优化、分库分表等
     

DDL

数据库基本语法

语句	功能
show databases	查询有哪些数据库
select database()	查询当前用的是哪个数据库
create database [ if not exists ] 数据库名	创建数据库
use 数据库名	使用数据库
drop database [ if exists ] 数据库名	删除数据库

tips

• 在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错
• 上述语法中的database，也可以替换成 schema

表操作基本语法

创建语句

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

注意： [ ] 中的内容为可选参数； 最后一个字段后面没有逗号

😊创建一个用户表，包括编号、姓名、性别，使用InnoDB引擎，字符集和排序方式默认

CREATE TABLE 'tb_user'(
'id' int DEFAULT NULL COMMENT '编号',
'name' varchar(50) DEFAULT NULL COMMENT '姓名',
'gender' varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表'

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

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

• 主键自增：auto_increment
  • 每次插入新的行记录时，数据库自动生成id字段(主键)下的值
  • 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
  • id int primary key auto_increment comment 'ID,唯一标识'

设计流程

1. 阅读页面原型及需求文档

2. 基于页面原则和需求文档，确定原型字段(类型、长度限制、约束)

3. 再增加表设计所需要的业务基础字段(id主键、插入时间、修改时间)

设计两条基础字段：

• create_time：记录的是当前这条数据插入的时间。

• update_time：记录当前这条数据最后更新的时间。

增删改查

这些语句面试中可能会问，但工作中一般用图形化界面操作

语句	功能
show tables	查询有哪些表
desc 表名	查看指定表的字段的信息
show create table 表名	查询指定表的建表语句
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];	添加字段
alter table 表名 modify 字段名 新数据类型(长度)	修改数据类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]	修改字段名
alter table 表名 drop 字段名	删除某个字段
drop table [ if exists ] 表名	删除表
rename table 表名 to 新表名	更改表名
alter table 表名 rename to 新表名	更改表名

数据类型

主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT	1byte	(-128，127)	(0，255)	小整数值
SMALLINT	2bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT/INTEGER	4bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8bytes	(-263，263-1)	(0，264-1)	极大整数值
FLOAT	4bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

示例: 
    年龄字段 ---不会出现负数, 而且人的年龄不会太大
	age tinyint unsigned
	
	分数 ---总分100分, 最多出现一位小数 100.1四位
	score double(4,1)

字符串类型

类型	大小	描述
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	极大文本数据

char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。

示例： 
    用户名 username ---长度不定, 最长不会超过50 根据实际长度存储
	username varchar(50)
	
	手机号 phone ---固定长度为11 不足11 也会开辟11的空间
	phone char(11)

日期时间类型

类型	大小	范围	格式	描述
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 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

示例: 
	生日字段  birthday ---生日只需要年月日  
	birthday date
	
	创建时间 createtime --- 需要精确到时分秒
	createtime  datetime

DML

• 添加数据（INSERT）
• 修改数据（UPDATE）
• 删除数据（DELETE）

增

• 向指定字段添加数据

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

• 全部字段添加数据

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

• 批量添加数据（指定字段）

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

• 批量添加数据（全部字段）

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

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

改

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

案例1：将tb_emp表中id为1的员工，姓名name字段更新为’张三’

update tb_emp set name='张三', update_time=now() where id=1;

案例2：将tb_emp表的所有员工入职日期更新为’2010-01-01’

update tb_emp set entrydate='2010-01-01',update_time=now();

注意事项:

1. 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
2. 在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

删

delete from 表名  [where  条件] ;

案例1：删除tb_emp表中id为1的员工

delete from tb_emp where id = 1;

案例2：删除tb_emp表中所有员工

delete from tb_emp;

注意事项:
​ • DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
​ • DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
​ • 当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute即可。

DQL

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基本查询

在基本查询的DQL语句中，不带任何的查询条件，语法如下：

• 查询多个字段

  select 字段1, 字段2, 字段3 from 表名;
  

• 查询所有字段（通配符）

  select * from 表名;
  

• 设置别名

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

• 去除重复记录

  select distinct 字段列表 from  表名;
  

条件查询

语法：

select 字段列表 from 表名 where 条件列表 ; -- 条件列表：意味着可以有多个条件

学习条件查询就是学习条件的构建方式，而在SQL语句当中构造条件的运算符分为两类：

• 比较运算符
• 逻辑运算符

比较运算符

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

易错：查询身份证号不存在的员工信息和查询身份证号存在的员工信息

select * from emp where idCard is null;
select * from emp where idCard is not null;
//而不是 idCard = null 和 idCard != null

逻辑运算符

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

tip：尽量用and和or而不是&&和||

案例

• 查询 职位是 2 (讲师), 4 (教研主管) 的员工信息

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where job in (2,4);

• 查询名字是两个字的员工信息

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like '__';  # 通配符 "_" 代表任意1个字符

• 查询 姓 ‘张’ 的员工信息

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like '张%'; # 通配符 "%" 代表任意个字符（0个 ~ 多个）

聚合函数

之前做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）

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

注意 : 聚合函数会忽略空值，对NULL值不作为统计。

常用聚合函数：

函数	功能
count	统计数量，按照列去统计有多少行数据
max	最大值
min	最小值
avg	平均值
sum	求和，计算指定列的数值和，如果不是数值类型，那么计算结果为0

案例1：统计该企业员工数量

# count(字段)
select count(id) from tb_emp;-- 结果：29
select count(job) from tb_emp;-- 结果：28 （聚合函数对NULL值不做计算）

# count(常量)
select count(0) from tb_emp;
select count('A') from tb_emp;

# count(*)  推荐此写法（MySQL底层进行了优化）
select count(*) from tb_emp;

案例2：统计该企业最早入职的员工

select min(entrydate) from tb_emp;

案例3：统计该企业员工 ID 的平均值

select avg(id) from tb_emp;

案例3：统计湖南地区员工的年龄之和

select sum(age) from tb_emp where wordAddress = '湖南';

分组查询

分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。通常会使用聚合函数进行计算。

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

案例1：根据性别分组 , 统计男性和女性员工的数量

select gender, count(*) 
from tb_emp
group by gender; -- 按照gender字段进行分组（gender字段下相同的数据归为一组）

案例2：查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

select job, count(*)
from tb_emp
where entrydate <= '2015-01-01'   -- 分组前条件
group by job                      -- 按照job字段分组
having count(*) >= 2;             -- 分组后条件

注意事项:

​ • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

​ • 执行顺序：where > 聚合函数 > having

where与having区别

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

排序查询

select 字段列表  
from 表名   
[where 条件列表] 
[group by 分组字段 ] 
order by 字段1  排序方式1 , 字段2  排序方式2 … ;

• 排序方式：
  • ASC ：升序（默认值）
  • DESC：降序

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

案例1：根据入职时间, 对员工进行升序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate ASC; -- 按照entrydate字段下的数据进行升序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate; -- 默认就是ASC（升序）

案例2：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate ASC, update_time DESC;

分页查询

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

注意事项:

1. 起始索引从0开始。 计算公式 ： 起始索引 = （查询页码 - 1）* 每页显示记录数
2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
3. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

案例1：从起始索引0开始查询员工数据, 每页展示5条记录

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 0, 5; -- 从索引0开始，向后取5条记录

案例2：查询 第1页 员工数据, 每页展示5条记录

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 5; -- 如果查询的是第1页数据，起始索引可以省略，直接简写为：limit 条数

案例3：查询 第2页 员工数据, 每页展示5条记录

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 5, 5; -- 从索引5开始，向后取5条记录

案例

1. 在员工管理的列表上方有一些查询条件：员工姓名、员工性别，员工入职时间(开始时间~结束时间)
   👉姓名：张 | 性别：男 | 入职时间：2000-01-01 ~ 2015-12-31
2. 除了查询条件外，在列表的下面还有一个分页条，这就涉及到了分页查询
   👉查询第1页数据（每页显示10条数据）
3. 基于查询的结果，按照修改时间进行降序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like '张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0 , 10;

执行顺序

from 👉 where 👉 group by 👉 select 👉 order by 👉 limit

DCL

CRUD

• 查询用户

USE mysql;
SELECT * FROM user;

• 创建用户

CREATE USER '用户名'@'主机名' identified by '密码';

'主机名'决定了用户权限：比如：
👉localhost：只能在当前主机访问该数据库
👉%：任意主机访问该数据库

• 修改用户

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

• 删除用户

DROP USER '用户名'@'主机名' ;

tip

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用 % 通配。

权限控制

官方文档

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

• 查询权限

SHOW GRANTS FOR '用户名'@'主机名';

• 授予权限

GRANT 权限 ON 数据库名.表名 TO '用户名'@'主机名';

• 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

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

案例：授予 ‘happy’@‘%’ 用户today数据库所有表的所有操作权限

GRANT ALL ON today.* TO 'happy'@'%';

函数

字符串函数

接口名	接口描述
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个长度的字符串

案例：原来的id为01、02、03，现在由于业务变更要改为五位数
思路：使用左填充函数补0

update emp set id = lpad(id, 5, '0')

数值函数

案例：通过数据库的函数，生成一个六位数的随机验证码。
思路： rand()函数，获取0-1之间的随机数，乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

select lpad(round(rand()*1000000, 0), 6, '0');

日期函数

案例：查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

select name, datediff(curdate() - entrydate) as '入职天数' from emp order by '入职天数' desc;

流程函数

接口名	接口描述
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默认值

案例： 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

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

约束

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

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 '性别'
);

可视化添加约束

外键约束

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

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

• 创建表时创建外键约束

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

• 删除外键

alter table 表名 drop foreign key 外键名称

👉 删除/更新行为

• CASCADE

# `CASCADE`
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade;
# `SET NULL`
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update set null on delete set null;

多表查询

加上连接查询的条件，否则查询的是两张表的笛卡尔积

select * from emp , dept where emp.dept_id = dept.id;

关系

一对多

👉案例：药品分类 与 药品
👉关系：一个药品分类 对应多个药品，一个药品对应一个药品分类
👉实现：多的一方建立外键，指向少的一方的主键

多对多

👉案例：药品存储区域 与 药品
👉关系：一个药品存放区域 对应多个药品，一个药品对应多个药品存放区域
👉实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

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

分类（内、外、自）

内连接

查询A、B交集部分数据

1. 隐式内连接

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;

2. 显式内连接

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名, inner 关键字可以省略，简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

一旦为表起了别名，就不能再使用表名来指定对应的字段了，只能够使用别名来指定字段。

外连接

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

案例：查询emp表的所有数据, 和对应的部门信息

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
//outer关键字可以省略

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

案例：查询dept表的所有数据, 和对应的员工信息

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

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。比如上述右外连接语句可以写成：

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

而我们在日常开发使用时，更偏向于左外连接。

自连接

👉自己连接自己，也就是把一张表连接查询多次。
👉在自连接查询中，必须要为表起别名，否则我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

案例：查询员工 及其 所属领导的名字

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

案例：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

select a.name, b.name from emp a left join emp b on a.managerid = b.id;

联合查询union

👉把多次查询的结果合并起来，形成一个新的查询结果集。
👉对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
👉union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

案例：将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来

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

子查询

👉SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
👉子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。

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

标量子查询（子查询结果为单个值）

👉常用的操作符：= <> > >= < <=

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

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

案例：查询在 “方东白” 入职之后的员工信息

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

列子查询(子查询结果为一列)

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

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

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

案例： 查询比 财务部 所有人工资都高的员工信息

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

行子查询(子查询结果为一行)

👉常用的操作符：= 、<> 、IN 、NOT IN

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

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

表子查询(子查询结果为多行多列)

👉常用的操作符：IN

案例：查询与 “鹿晗” , “吴世勋” 的职位和薪资相同的员工信息

select * from emp where (job, salary) in (select job, salary from emp where name = '鹿晗' or name = '吴世勋');

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

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

案例

create table dept(
                     id int auto_increment comment 'ID' primary key,
                     name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,'销售部'), (5, '总经办'), (6, '人事部');

create table emp(
                    id int auto_increment comment 'ID' primary key,
                    name varchar(50) not null comment '姓名',
                    age int comment '年龄',
                    job varchar(20) comment '职位',
                    salary int comment '薪资',
                    entrydate date comment '入职时间',
                    managerid int comment '直属领导ID',
                    dept_id int comment '部门ID'
)comment '员工表';

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

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
    (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
    (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
    (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
    (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
    (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
    (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
    (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
    (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
    (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
    (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
    (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
    (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
    (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
    (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
    (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
    (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
    (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);


create table salgrade( grade int, losal int, hisal int) comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

# 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name, e.a
ge, e.job, d.name from emp e, dept d where e.dept_id = d.id;
# 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name, e.age, e.job, d.name from emp e join dept d on e.dept_id = d.id where e.age < 30;
# 查询拥有员工的部门ID、部门名称
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
# 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
# 查询所有员工的工资等级
select distinct e.*, s.grade from salgrade s, emp e where e.salary >= s.losal and e.salary <= s.hisal;
# 查询 "研发部" 所有员工的信息及工资等级
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 = '研发部';
# 查询工资比 "灭绝" 高的员工信息
select * from emp where salary > (select salary from emp where name = '灭绝') ;
# 查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);
# 查询低于本部门平均工资的员工信息
select * from emp e where e.salary < (select avg(e1.salary) from emp e1 join dept d on e1.dept_id = d.id);
# 查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

事务

👉事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
👉默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

-- 开启事务
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;

ACID

原子性

👉是最小的不可分割的单位，一个事务中的语句要么同时成功要么同时失败

一致性

👉事务完成后，必须所有的数据都保持一致的状态

原子性和一致性是基于undo log保证的，undo log提供回滚和MVCC，记录的是相反的操作，用于事务回滚时提供逆操作。

隔离性

👉事务和事务之间是相互隔离的，不受外界并发影响
并发事务问题

• 脏读：一个事务读到了另外一个事务还没有提交的数据
• 不可重复读：一个事务先后读取同一条数据，但是两次读取的数据不同
• 幻读：一个事务按照条件查询时，没有对应的数据行，但在插入数据时，发现这行数据已经存在

隔离级别

• 读未提交：允许读取尚未提交的数据😶‍🌫️不能解决任何问题
• 读已提交：允许读取并发事务已经提交的数据😶‍🌫️可以解决脏读
• 可重复读【默认】：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改😶‍🌫️可以解决脏读、不可重复读
• 串行化：所有的事务依次逐个执行😶‍🌫️可以解决所有问题

隔离级别的实现

• 基于锁和MVCC机制
  • 排他锁：一个事务获得了一个行的排他锁，那么其他事务就获取不了了
  • MVCC：多版本并发控制，指维护一个数据的多个版本，使读写操作没有冲突

持久性

👉事务一旦提交或者回滚，对数据库的改变是永久的

• redo log
  • 在进行增删改查操作的时候，实际上先操作缓冲池中的数据，并以一定的频率刷新到磁盘的数据页中，但这样会产生一个“脏页”的现象，比如服务器宕机的时候，磁盘中的数据可能无法同步。这时候就需要用到redo log
  • redo log由两部分组成，一个是redo log buffer，它和缓冲池一样都存储在主内存中，另外一个是redo log file，它存储在磁盘中。当缓冲池的数据被操作后，会同步记录到redo log buffer中，redo log buffer再同步到redo log file中，这样当出现脏页现象的时候，磁盘就可以从redo log file中恢复数据了。
    为什么明明是同步，但是redo log的同步要比缓冲池与数据页同步的性能好呢？
    因为redo log的同步是一个顺序磁盘IO，而缓冲池和数据页同步是随机磁盘IO。
• undo log
  • 提供回滚和MVCC
  • 记录的是相反的操作，用于事务回滚时提供逆操作
  • 比如delete时，记录一条对应的insert

redo log 和 undo log的区别
他们都是MySQL的日志文件，但是用途不一样
redo log 记录的是事务提交时数据页的物理修改，用于实现事务的持久性
undo log 记录的是事务被修改前的信息，是逻辑日志，用于保证事务的原子性和一致性

指令

查看事务隔离级别：SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别：SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }