数据库mysql

---

---

---

------------------------------------------------------------------------

第1章 绪论

1.1 数据库系统概述

$▶$ 课前问题

1. 数据与数据库的概念？
2. 如何科学的组织和储存数据？
   $\bullet$ 使用数据库管理系统

$▶$ 基本概念

1. 数据：
   $\bullet$ 数据是数据库中储存的基本对象，是描述事物的符号记录。
   $\bullet$ 数据的含义称为数据的语义。
2. 数据库DB
   $\bullet$ 数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合。
   $\bullet$ 数据库中的数据按一定的数据模型组织、描述和储存。
3. 数据库管理系统DBMS
   $\bullet$ 定义：
   位于用户应用与操作系统之间的一层数据管理软件；是计算机基础软件，是一个大型复杂的软件系统。
   $\bullet$ 主要功能：
   $\bullet$$\bullet$ 数据定义功能：定义数据库中的数据对象。
   $\bullet$$\bullet$ 数据的组织储存和管理：分类组织储存和管理各种数据、实现数据之间的联系、提供多种存取方法，提高储存空间利用率和方便存取。
   $\bullet$$\bullet$ 数据的操纵功能：实现对数据库的基本操作（增删改查）
   $\bullet$$\bullet$ 数据库的事务管理和运行管理：保证正确运行。
   $\bullet$$\bullet$ 数据库的建立和维护功能：批量装载、数据库转储等
4. 数据库系统
   $\bullet$ 定义：数据库系统是由数据库、数据库管理系统、应用程序和数据库管理员 组成的 存储、管理、处理和维护数据的系统。
   
5. 数据库系统的特点
   $\bullet$ 数据结构化
   $\bullet$ 数据的共享性高、冗余程度低且易扩充
   减少数据冗余，节约空间，避免数据的不一致性
   $\bullet$ 数据独立性高
   数据库中的数据与应用程序相互独立（物理储存和逻辑结构）。
   $\bullet$ 数据由数据库管理系统统一管理和控制
   保证数据完整性、安全性、并发控制、数据恢复

---

---

1.2 数据模型

$▶$ 数据模型

1. 定义
   $\bullet$ 对现实世界数据特征的抽象，是现实世界的模拟。
   $\bullet$ 数据模型是严格定义的一组概念的集合，精确地描述了系统的静态特性、动态特性和完整性约束条件。
   

$▶$ 数据模型的组成要素

1. 数据结构：
   $\bullet$ 描述数据库的组成对象。（对象的类型、内容、性质）
   $\bullet$ 描述对象之间的联系，
   $\bullet$ 描述系统的静态特性。
2. 数据操作：
   $\bullet$ 对数据库中各种对象的实例允许执行的操作的集合，包括操作以及有关操作规则。
   $\bullet$ 描述数据的动态特性
3. 数据的完整性约束条件：
   $\bullet$ 数据的完整性约束条件是一组完整性规则的集合；
   $\bullet$ 完整性规则：给定数据模型中数据和联系所具有的制约与依存规则。

---

$▶$ 两类数据模型

1. 概念模型
   按用户的观点来对数据和信息建模，用于数据库的设计。
2. 逻辑模型和物理模型
   $\bullet$ 逻辑模型：按计算机系统的观点对数据建模，主要用于数据库管理系统的实现。
   $\bullet$ 物理模型：是系统内部对数据的存储、存取方式。
   

$▶$ 概念模型

1. 基本概念：
   $\bullet$ 实体、属性、码、实体型、实体集、联系
   $\bullet$ 联系：
   实体内部的联系：指个属性之间的联系；
   实体之间的联系：指不同实体集之间的联系，一对一，一对多，多对多。
   

$▶$ 主要的逻辑数据模型

1. 层次模型：
   用树形结构来表示各类实体以及实体间的联系。
2. 网状模型
   允许结点没有双亲结点，允许结点有多个双亲结点；
   层次模型是网状模型的一个特例
3. 关系模型
   $\bullet$ 基本概念：关系(一张表)、元组(行)、属性(列)、码、域(属性的取值范围)、分量
   $\bullet$ 关系模式：对关系的描述
   表示为：关系名(属性1，属性2… 属性n)

---

---

1.3 数据库系统的结构

1.3.1 数据库系统模式的概念

$▶$ 模式

1. 模式的定义：
   $\bullet$ 是对数据库逻辑结构和特征的描述；
   $\bullet$ 仅仅涉及数据模型中型的描述，不涉及具体的值。（型是指对某一类数据的 结构和属性的说明）
   例：学生的记录/实体型：（学号、姓名、性别、年龄）
   记录/实体值：（202011、李明、男、20）
2. 模式的值（实例）
   $\bullet$ 模式的一个具体值称为模式的一个实例，同一个模式可以有多个实例；
   例：同一个“学生选课数据库”模式：20年的实例和21年的实例不同
   $\bullet$ 模式是稳定的，实例是变动的。
3. 模式的组成
   $\bullet$ 由关系模式组成（表）
   例：“学生选课数据库”模式：由学生、课程和学生选课3个关系模式组成

---

1.3.2 数据库系统的三级模式结构与二级映像

$▶$ 三级模式结构

1. 模式：
   $\bullet$ 是对数据库中全体数据的逻辑结构和特征的描述；
   $\bullet$ 仅仅涉及数据模型中型的描述，不涉及具体的值。（型是指对某一类数据的 结构和属性的说明）
2. 外模式
   $\bullet$ 定义：子模式，是局部数据的逻辑结构和特征的描述，外模式是模式的子集。
3. 内模式
   $\bullet$ 定义：存储模式，一个数据库只有一个内模式。是数据库物理结构和储存方法的描述

$▶$ 模式映像

1. 外模式/模式映像
   $\bullet$ 一个模式有任意多个外模式，每一个外模式对应一个映像；
   $\bullet$ 定义了该外模式与模式之间的对应关系；
   $\bullet$ 数据与程序的逻辑独立性：
   模式改变，外模式可保持不变，程序不变。
2. 内模式/模式映像
   $\bullet$ 数据库中只有一个模式，也只有一个内模式；
   $\bullet$ 定义了数据全局逻辑结构与储存结构之间的对应关系；
   $\bullet$ 数据与程序的物理独立性：
   内模式改变，模式可保持不变，程序不变。

$▶$ 各种人员的数据视图

---

---

1.4 MySQL环境搭建

---

---

---

------------------------------------------------------------------------

第 2 章 关系数据库

关系模型、关系代数、关系演算

2.1 关系数据结构

从集合论角度给出关系数据结构的形式化定义

$▶$ 关系

1. 关系定义
   $\bullet$ $D_1\ast D_2\ast \dots D_n$的子集叫作在域$D_1,D_2,\dots D_n$上的关系，表示为$R(D_1,D_2,\dots D_n)$
   $\bullet$ R：关系名
   $\bullet$ n：关系的目或度(Degree)
2. 元组
   $\bullet$ 关系中的每个元素(d1,d2,…,dn)叫作一个n元组(n-tuple)或简称元组，通常用t表示。
3. 属性
   $\bullet$ 对每列的 域 起一个名字，称为属性(Attribute)
   n目关系必有n个属性
4. 码
   $\bullet$ 候选码(Candidate key)
   若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码。候选码至少有一个。
   简单的情况：候选码只包含一个属性(如：学号，ID)
   $\bullet$ 全码(All-key)
   最极端的情况：关系模式的所有属性组是这个关系模式的候选码
   称为全码(All-key)

$▶$ 关系模式

1. 关系模式定义
   关系模式(Relation Schema)是型
   关系是值
   关系模式是对关系的描述
2. 关系模式是对关系的描述
   $\bullet$ 元组集合的结构
   属性构成
   属性来自的域
   属性与域之间的映象关系
   $\bullet$ 完整性约束条件
3. 关系模式形式化地表示为：R (U,D,DOM,F)
   R：关系名
   U：组成该关系的属性名集合
   D：U中属性所来自的域
   DOM：属性向域的映象集合
   F：属性间数据的依赖关系的集合
   $\bullet$ 关系模式通常可以简记为：R(U)或R(A1,A2…,An)
4. 关系与关系模式
   $\bullet$ 关系模式
   对关系的描述
   静态的、稳定的
   $\bullet$ 关系
   关系是关系模式在某一时刻的状态或内容
   动态的、随时间不断变化的

---

2.2 关系操作

---

2.3 关系的完整性

2.3.1 实体完整性和参照完整性

关系模型必须满足的完整性约束条件称为关系的两个不变性，应该由关系系统自动支持。

$▶$ 实体完整性

1. 实体完整性规则
   关系的主属性不能取空值，空值就是“不知道”或“不存在”或“无意义”的值。

$▶$ 参照完整性

1. 外码定义
   $\bullet$ 设F是基本关系R的一个或一组属性，但不是关系R的主码。
   如果F与基本关系S的主码K相对应，则称F是R的外码。
   $\bullet$ 基本关系R称为参照关系(Referencing Relation)
   $\bullet$ 基本关系S称为被参照关系(Referenced Relation)或目标关系(Target Relation)
2. 参照完整性规则
   若属性（或属性组）F是基本关系R的外码它与基本关系S的主码K相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为：
   $\bullet$ 或者取空值(F的每个属性值均为空值)
   $\bullet$ 或者等于S中某个元组的主码值

---

2.3.2 用户定义的完整性

应用领域需要遵循的约束条件，体现了具体领域中的语义约束

$▶$ 用户定义的完整性

1. 针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求
2. 关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不需由应用程序承担这一功能

---

---

2.4 关系代数

2.4.1 集合运算

$▶$ 集合运算

1. 定义：
   $\bullet$ 二元运算，包括并、差、交、笛卡儿积。
   $\bullet$ 集合运算将关系看成元组的集合，其运算是从关系的“水平”方向，即行的角度来进行；
2. 并(union)
   由属于R或属于S的元组组成。
3. 差(except)
   由属于R而不属于S的所有元组组成。
4. 交(intersection)
   由既属于R又属于S的元组组成。
5. 笛卡积(cartesian product)
   $\bullet$ 是广义的笛卡儿积(extended cartesian product)，因为这里笛卡儿积的元素是元组。
   $\bullet$ 两个分别为n目和m目的关系R和S的笛卡儿积是一个(n+m)列的元组的集合。
   $\bullet$ 若R有k1个元组，S有k2个元组,则关系R和关系S的笛卡儿积有k1×k2个元组。记作

---

2.4.2 关系运算

$▶$ 关系运算概述

1. 定义：
   关系运算包括选择、投影、连接、除运算等。
2. 设关系模式为$R(A_1,A_2\dots ,A_n)$
   $\bullet$ $R$：关系模式的一个关系
   $\bullet$ $t\in R$：t是R中的一个元组。
   $\bullet$ $t[A_i]$：表示元组中相应于属性$A_i$的一个分量
   $\bullet$ $A$：A称为属性列或属性组。
3. 连接 $\widehat{t_r{t}_s}$
   $\bullet$ $\widehat{t_r{t}_s}$ 称为元组的连接（R为n目关系，S为m目关系，t∈R,t∈S）
   $\bullet$ $\widehat{t_r{t}_s}$ 是一个n+m列的元组。
4. 象集$Z_x$
   给定一个关系R(X,Z)，X和Z为属性组。
   Z x = { t [ Z ] ∣ t ∈ R , t [ X ] = x } Z_x= \{t[Z] |t∈R,t[X]=x \} Zx​={t[Z]∣t∈R,t[X]=x}
   $\bullet$ 它表示R中属性组X上值为x的元组，在属性组Z上对应的分量z的集合
   例： Z x 1 = { Z 1 , Z 2 , Z 3 } Z_{x1}= \{Z_{1},Z_{2},Z_{3}\} Zx1​={Z1​,Z2​,Z3​}
   

---

$▶$ 四个关系运算

1. 选择 $\sigma$
   $\bullet$ 定义：
   选择又称为限制(Restriction)，在关系R中选择满足给定条件的诸元组。
   $\bullet$ 选择运算符的含义
   σ F ( R ) = { t ∣ t ϵ R ∧ F ( t ) = ′ 真 ′ } \sigma_F(R)=\{ t| t \epsilon R∧F(t)='真' \} σF​(R)={t∣tϵR∧F(t)=′真′}
   $\bullet$ 选择条件F：是一个逻辑表达式，取值为“真”或“假”
   基本形式：$X_1\theta Y_1$
   $\theta :$表示比较运算符，它可以是>，≥，<，≤，=或<>
2. 投影 $\Pi$
   $\bullet$ 定义：
   从R中选择出若干属性列组成新的关系。
   记作： Π A ( R ) = { t [ A ] ∣ t ∈ R } \Pi_A (R)= \{t[A] | t∈R \} ΠA​(R)={t[A]∣t∈R} ，A：R中的属性列。
   $\bullet$ 投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）
   $\bullet$ 例：查询学生关系中有那些系：${\Pi }_{Sdept}(Student)$
3. 连接 $⊳⊲$
   $\bullet$ 定义：
   从两个关系的笛卡尔积中选取属性间满足一定条件的元组。（A和B：分别为R和S上度数相等且可比的属性组）
   R ⊳ ⊲ S = { t r t s ^ ∣ t r ∈ R ∧ t s ∈ S ∧ t r [ A ] θ t s [ B ] } R \rhd\lhd S=\{ \widehat{t_r t_s} | t_r ∈R∧t_s \in S\land t_r[A]\theta t_s[B]\} R⊳⊲S={tr​ts​ ​∣tr​∈R∧ts​∈S∧tr​[A]θts​[B]}
   $\bullet$ 详细定义：
   连接运算从关系R和S的广义笛卡尔积RXS中，选取 R关系在A属性组上的值与 S关系在B属性组上的值满足比较关系$\theta$的元组。
   $\bullet$ 悬浮元组(dangling tuple)
   关系R中某些元组有可能在S中不存在公共属性上值相等的元组，这些被舍弃的元组称为悬浮元组(dangling tuple)。
   $\bullet$ 外连接(outerjoin)
   如果把悬浮元组也保存在结果关系中，而在其他属性上填空值(NULL)。
4. 除
   设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合都在R中。

$▶$ 连接运算分类

1. 连接运算分类
   非等值连接、等值连接、自然连接
2. 等值连接：
   从关系R和S的广义笛卡尔积RXS中选取A、B属性值相等的元组。
3. 自然连接：
   一种特殊的等值连接，要求比较的两个分量必须是同名的属性组，且在结果中把重复的属性列去掉。
   
   

---

$▶$ 输入输出分析法

1. 例题1
   
2. 例题2
   
   

---

---

2.5 关系型数据库设计规则

2.5.1 表、记录、字段

$▶$ 表、记录、字段

1. E-R（entity-relationship，实体-联系）模型中有三个主要概念是：
   实体集 、 属性 、 联系集 。
2. 实体集（class）
   对应于数据库中的一个表（table）
3. 实体（instance）
   对应于数据库表中的一行（row），也称为一条记录（record）。
4. 属性（attribute）
   对应于数据库表中的一列（column），也称为一个字段（field）。
   
5. 对应关系
   数据库中的一个表 $\Rightarrow$ Java或Python中的一个类
   表中的一条数据 $\Rightarrow$ 类中的一个对象（或实体）
   表中的一个列 $\Rightarrow$ 类中的一个字段、属性(field)

---

---

---

------------------------------------------------------------------------

第三章 关系数据库标准语言SQL与Mysql

3.1 数据查询select：连接过滤，分组过滤，选择去重与排序

3.1.1 基础SSELECT语句

$▶$ 基础SSELECT语句

1. 列的别名：as “xxx "
   $\bullet$ as（可省略）
   $\bullet$ 列的别名可以使用一对” "引起来，使用场景：别名中存在空格
2. 去除重复行
   DISTINCT
3. 空值参与运算
   $\bullet$ 所有运算符或列值遇到null值，运算的结果都为null
   $\bullet$ 在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
4. 着重号 ``
   表名与SQL关键字重名
5. 查询常数
   在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
6. 显示表结构
   DESCRIBE：DESC显示了表中字段的详细信息

空值参与运算解决方案：
SELECT employee_id,salary "月工资",salary *(1+IFNULL(commission_pct,0))*12 "年工资",commission_pct
FROM employees;

---

---

3.1.2 单表查询

先查询所有行，返回完整查询结果，再看查某几列，然后对查询结果排序或去重

1.select选择列

$▶$ 选择表中若干列：指定列、全部列、经过计算的值

1. 查询格式
   
   如：
   
2. 关键字解释
   $\bullet$ SELECT子句：指定要查询的列
   $\bullet$ FROM子句：指定查询对象（基本表或视图）
   $\bullet$ WHERE子句：指定查询条件
   $\bullet$ GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
   $\bullet$ HAVING短语：只有满足指定条件的组才予以输出
   $\bullet$ ORDER BY子句：对查询结果表按指定列值的升序或降序排序
   

例子：
#1. 查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT employee_id,last_name,salary*12 "ANNUAL SALARY"
FROM employees;

#2.查询employees表中去除重复的job id以后的数据
SELECT DISTINCT job id
FROM employees;

#3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary >12000;

#4.查询员工号为176的员工的姓名和部门号
SELECT employee_id,last_name,department_id
FROM employees
WHERE employee_id = 176;

#5.显示表departments的结构，并查询其中的全部数据
DESCRIBE departments;
SELECT *FROM departments;

---

2.where选择行

$▶$ 选择表中若干元组：查询满足条件的元组

1. 四种判断运算符
   算术运算符、比较运算符、逻辑运算符、位运算符

$▶$ 算术运算符

1. 加法与减法
   $\bullet$ 在MySQL中+只表示数值相加。
   $\bullet$ 如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）
2. 乘法与除法
   $\bullet$ 一个数乘以整数1和除以整数1后仍得原数；
   $\bullet$ 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
   $\bullet$ 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；并保留到小数点后4位；
   $\bullet$ 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
   
3. 求模（求余）运算符
   %或MOD：

---

$▶$ 比较运算符

1. 比较运算符
   $\bullet$ null参与判断
   只要有nul1参与判断，结果就为null
   $\bullet$ 等号运算符
   如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL
   $\bullet$ 安全等于运算符
   两边的操作数的值都为NULL时，返回的结果为1，其他返回结果与等于运算符相同。
   
2. 空值判断：
   IS NULL / IS NOT NULL
3. 最小值运算符
   语法格式为：LEAST(值1，值2，…，值n)。
4. 最大值运算符
   语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。
5. BETWEEN AND运算符 （包含边界）
   $\bullet$ 使用的格式：
   SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。
   $\bullet$ 否定 NOT BETWEEN AND
6. IN运算符
   $\bullet$ 用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。
   $\bullet$ 如果给定的值为NULL，或者IN列表中只存在NULL，则结果为NULL
   $\bullet$ 否定 NOT IN
7. LIKE运算符
   $\bullet$ LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。
   $\bullet$ 如果给定的值或者匹配条件为NULL，则返回结果为NULL。
   $\bullet$ 通配符
   “%”：匹配0个或多个字符。
   “_”：只能匹配一个字符。
   $\bullet$ 转义字符 “\”
8. REGEXP运算符
   用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。

#1.null参与判断：
# = :只要有nul1参与判断，结果就为null
# <=> :安全等于
SELECT 1 = NULL,NULL = NULL
FROM DUAL

SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct = NULL;#全为NULL

练习1：查询表中commission pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

练习2：查询表中commission pct不为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
或
SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

练习3：查询last_name中以字符'a'开头的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE 'a%'

练习：查询第2个字符是_且第3个字符是'a'的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE '_\_a%'

---

$▶$ 逻辑运算符

1. 逻辑运算符
   AND的优先级高于OR
   
2. 运算符左右两边地位平等
3. 异或的使用场景
   两个条件只满足其中一个

---

3.排序与分页

$▶$ 排序数据：ORDER BY子句

1. 操作
   对查询结果按照属性列的 升序(ASC) 或者 降序(DESC) 排列.
2. 排序规则
   $\bullet$ 使用 ORDER BY 子句排序
   ASC（ascend）: 升序(默认)
   DESC（descend）:降序
   $\bullet$ 升序降序
   以左上为基点往下看，由小到大为升序。
3. 二级排序
   $\bullet$ 对两列按照不同排序规则排序
   $\bullet$ 首先排序的第一列必须有相同的列值，才会对第二列进行排序。 如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
4. 多列排序
   $\bullet$ 可以使用不在SELECT列表中的列排序。
   $\bullet$ 对多列进行排序
   即多级排序
   $\bullet$ 列的别名进行排序
   列的别名只能在ORDER BY中使用，不能在WHERE中使用。
5. ORDER BY 子句在SELECT语句的结尾

二级排序
练习：显示员工信息，按照department_id的降序排列，salaryl的升序排列
SELECT employee id,salary,department id
FROM employees
ORDER BY department id DESC,salary ASC;

$▶$ 分页

1. 定义
   所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
2. MySQL中使用 LIMIT 实现分页
   $\bullet$ $LIMIT[位置偏移量,]行数$
   $\bullet$ 第二个参数“行数”指示返回的记录条数。

• 分页显式公式：
  （当前页数-1）*每页条数，每页条数

---

3.1.3 多表查询 $★$$★$$★$

$▶$ 多表查询概述

1. 多表连接
   $\bullet$ 区分重复的列名：
   多个表中有相同列时，必须在列名之前加上表名前缀。
   $\bullet$ 连接条件：
   连接 n个表，至少需要n-1个连接条件。
   $\bullet$ 表的别名
   使用别名可以简化查询。
   列名前使用表名前缀可以提高查询效率。
2. 多表查询分类
   $\bullet$ 内连接：
   合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
   $\bullet$ 外连接:
   两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
   

-------------------------------------------------------------------------
多表查询分类
1.等值连接与非等值连接
#等值连接
#非等值连接：查询员工工资等级
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

SELECT * FROM employees;

2.自连接与非自连接：即将一张表看作两张表，然后用两个不同的别名进行查询
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id =mgr.employee_id;

3.内连接与外连接：
#A:员工
#B:部门
#A∩B员工和部门都存在

3.1 内连接：关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
#合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
# 即：交集∪，共106条
SELECT employee_id,department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id


3.2 外连接：除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。
#两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接
#sql99实现外连接：join ...on...
SELECT employee_id,department_name,city
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id;

---

1.外连接

1. SQL92实现外连接
   $\bullet$ Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
   $\bullet$ MySQL 则不支持 SQL 的满连接。
   $\bullet$ 在 SQL92 中采用**（+）代表从表所在的位置。即左或右外连接中， (+) 表示哪个是从表
   
2. SQL99实现外连接
   join xx on sb：加入xx，由谁的关系加入的的
3. 实现连接三种方式：
   $\bullet$ WHERE：
   适用于所有关联查询
   $\bullet$ JOIN…ON ：
   只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
   $\bullet$ USING：
   只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字
   段值相等

• UNION关键字：
  合并查询结果
  $\bullet$ UNION 操作符：返回两个查询的结果集的并集，去重。
  $\bullet$ UNION ALL操作符：返回两个查询的结果集的并集。不去重。
  注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

-------------------------------------------------------------------------
SQL99语法实现多表查询
#外连接语法:
SELECT 字段列表
FROM A表 JOIN B表
ON 关联条件
WHERE 等其他子句;

-------------------------------------------------------------------------
实现连接三种方式：
#1.把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

#2.把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;

SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;

SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;

#3.把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

---

-------------------------------------------------------------------------
#sql99实现外连接：满FULL\左LEFT\右RIGHT OUT JOIN ...ON...

MySQL实现外连接：
3.内连接与外连接：
#A:员工107
#B:部门122
#A∩B员工和部门都存在106

3.1 内连接：关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
#合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
# 即：AB交集，共106条
SELECT employee_id,department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id


3.2 外连接：除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。
#两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接
#sql99实现外连接：join ...on...
SELECT employee_id,department_name,city
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id;

#左满：所有员工，即A，107条
SELECT employee_id,last_name,department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;  #无部门的员工 A-A∩B=1条


#右满：所有部门,即：B，122条
SELECT employee_id,last_name,department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;   #无员工的部门 即：B-A∩B=16条

-------------------------------------------------------------------------
#满连接：
#MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替
#UNION 与 UNION ALL操作符

方法一：A∪B去重 
#左满：所有员工，即A
SELECT employee_id,department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
UNION
#右满：所有部门,即：B
SELECT employee_id,department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id

方法二： A∪（B-A∩B）
#左满：所有员工，即A
SELECT employee_id,department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
#右满：所有部门,即：B
SELECT employee_id,department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id
#无员工的部门 即：B-A∩B=16条
WHERE e.department_id IS NULL;

-------------------------------------------------------------------------
练习：
#1.显示所有员工的姓名，部门号，部门名称
#外连接：**所有** 有员工没有部门
SELECT last_name,e.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`

#2.查询90号部门员工的e.job_id和90号部门的d.laction_id
#无外连接：即忽略没有部门城市的人
SELECT e.job_id,d.location_id
FROM employees e 
JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.`department_id`=90;

#3.选择所有有奖金的员工的 e.last_name, d.department_name, d.location_id, l.city
#外连接：所有：有的员工没有部门，没有部门所在城市，即代应该使用外连接
SELECT e.last_name,e.`commission_pct`,d.`department_name`,d.location_id,l.city
FROM employees e 
LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
LEFT JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE e.`commission_pct` IS NOT NULL;

#4.选择 l.city在Toronto工作的员工的e.last_name , e.job_id, d.department_id , d.department_name
#无外连接：即忽略没有部门城市的人
SELECT e.last_name , e.job_id, d.department_id , d.department_name
FROM employees e 
JOIN departments d
ON e.`department_id`= d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE l.city = 'Toronto';
 

#5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为'Executive
#无外连接：即忽略没有部门城市的人
SELECT  d.department_name , l.street_address , e.last_name , e.job_id , e.salary
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
JOIN locations l
ON d.`location_id` = l.location_id
WHERE d.`department_name`='Executive';


#7.查询哪些部门没有员工
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.`department_id`
WHERE e.`department_id` IS NULL;


#8.查询哪城市没有部门
SELECT d.`department_id`, l.city
FROM departments d
RIGHT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id` IS NULL;


#9.查询部门名为Sales或IT的员工信息
SELECT  e.employee_id, e.last_name , e.`department_id`
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN ('Sales','IT');

---

3.1.4 函数

1.单行函数

$▶$ 数值函数

1. 分类：
   绝对值、天花板、地板、随机数rand、四舍五入round、截断truncate、三角函数、指数POW()、对数、进制转换
2. 单行函数可以嵌套

$▶$ 字符串函数

---

• 字符长度：CAHR_LENGTH()
• 连接：concat()
• 替换：INSERT()、REPLACE()
• 大小写：lower()、upper()
• 对齐：LPAD()、RPAD()
  

$▶$ 流程控制函数

1. 流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
   MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()

---

2.多行函数

聚集函数：

$▶$ 聚集函数：作用于一组数据，并对一组数据返回一个值，查询指定字段时自动过滤空值

1. AVG()、SUM()
   只适用于数值类型的字段（或变量）
2. MAX()、MIN()
   适用于数值类型、字符串类型、日期时间类型的字段（或变量）
3. COUNT()：不计算空值，需要特殊处理
   $\bullet$ 作用：计算指定字段在查询结果中出现的个数
   注意：计算指定字段出现的个数时，是不计算NULL值的。
   $\bullet$ $公式：AVG(x)=SUM(x)/COUNT(x)$
   

#如果计算表中有多少条记录，如何实现？
#方式1：COUNT(*)
#方式2：COUNT(1)
#方式3：COUNT(具体字段)：不一定对！

#需求：查询公司中平均奖金率
#错误的！
SELECT AVG(commission_pct)
FROM employees; 
#正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), #count需要把所有的人都算上
# AVG(IFNULL(commission_pct,0))
FROM employees;

#某员工所在部门平均工资
SELECT AVG(salary)							  #相关子查询：每次查询都子查询都要重新计算一次
FROM employees e2                             #子查询中使用主查询中的列
WHERE e2.`department_id` = e1.`department_id` #过滤得到相同部门行，聚合函数作用于过滤得到整体的结果，即求得本部门平均工资

---

GROUP BY

$▶$ 分组：GROUP BY子句

1. 作用
   $\bullet$ 将表中的数据分成若干组
   即：细化 聚集函数的作用对象
   如果未对查询结果分组，聚集函数将作用于整个查询结果
   对查询结果分组后，聚集函数将分别作用于每个组
2. 结论：
   $\bullet$ SELECT中出现的非聚合函数的字段必须声明在GROUP BY中。 反之，GROUP BY中声明的字段可以不出现在SELECT中。
   即：在SELECT列表中所有未包含在组函数中的列 都应该包含在 GROUP BY子句中

• GROUP BY 声明位置：
  在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面
  
• count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

#错误的！
#job_id：SELECT中出现的非聚合函数的字段必须声明在GROUP BY中
SELECT department_id, job_id,AVG(salary)
FROM employees
GROUP BY department_id;

---

HAVING

$▶$ 对分组过滤数据：HAVING

1. 作用
   在分组查询的结果中再次过滤

• 不能在 WHERE 子句中使用聚合函数
  
  

1. 要求：
   $\bullet$ 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
   $\bullet$ 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。
   $\bullet$ HAVING必须声在 GROUP BY后面。

#sq192语法：
#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,

---

$▶$ 聚合函数练习

#7.查询所有部门的(名字，location_id,员工数量和平均工资)，并按平均工资降序
SELECT d.department_name , d.location_id , COUNT(salary) , AVG(salary)
#外连接：存在有员工没有部门,存在有部门没有员工
FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
GROUP BY d.department_name , d.location_id
ORDER BY AVG(salary) DESC

#8.查询每个工种、每个部门的(部门名、工种名和最低工资)
SELECT d.department_name , e.job_id , MIN(salary)
FROM employees e RIGHT JOIN departments d ON e.`department_id`=d.`department_id`
GROUP BY d.department_name , e.job_id

---

---

3.1.4 子查询

$▶$ 子查询

1. 子查询的分类
   $\bullet$ 角度1：从内查询返回的结果的条目数
   单行子查询vs多行子查询
   $\bullet$ 角度2：内查询是否被执行多次
   相关子查询 vs不相关子查询

• 自连接的处理速度要比子查询快得多

---

1.单行子查询 与 多行子查询

$▶$ 子查询书写方式

1. 由内而外
2. 由外而内
   $\bullet$ 例子：定语划分
   题目：查询（（与141号或174号）员工的manager_id和department_id相同的）其他员工的employee_id，manager_id，department_id

一、由内而外：
一、由内而外：
题目：显示员工的employee_id,last_name和location。
其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。
#1.查询location_id为1800 的department_id为20
SELECT department_id FROM departments WHERE location_id = 1800

#2.若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT CASE department_id WHEN 20 THEN 'Canada'
				   ELSE 'USA' END
FROM departments;

#3.显示员工的employee_id,last_name和location
SELECT employee_id,LAST NAME,() "location"
FROM employees

#4.组合
题目：显示员工的employee_id,last_name和location。
SELECT employee_id,last_name,(CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
				   								                       ELSE 'USA' END) "location"
FROM employees

---

$▶$ 多行子查询

1. 多行子查询
   $\bullet$ 也称为集合比较子查询
   $\bullet$ 内查询返回多行，使用多行比较操作符
2. 多行比较操作符
   

#多行查询
#题目：查询（平均工资最低）的部门id
解析：
1.查询最低平均工资
2.分组过滤：求各部门平均工资， 查询等于最低平均工资的 部门id
即：先求最低平均工资，在找哪一个部门才是最低平均工资

#1.查询最低的平均工资
#1.1 方法一：错误，MYSQL中聚合函数不可以嵌套，Oracle可以
SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id
#1.2 方法二：将AVG聚合函数结果视为一张表dep_avg_sal（必需取别名），再使用MIN聚合函数
由内而外
#最低工资
SELECT MIN(avg_sal)
FROM (SELECT AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id) dep_avg
	
-----------------------------------------------------------------
#2.1分组过滤-单行查询：求各部门平均工资， 单行查询等于最低平均工资的 部门id
SELECT *
FROM departments
WHERE department_id =(
	SELECT department_id
	FROM employees
	GROUP BY department_id    #分组过滤：查找部门平均工资等于最低平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MIN(avg_sal)   #部门最低平均工资
		FROM (
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #部门平均工资
	     )
)

-----------------------------------------------------------------
#2.2分组过滤-多行查询
#思路：查询各部门平均工资结果，多行查询使用<ALL从结果中查找最小值
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			);

---

2.相关子查询 与 不相关子查询

$▶$ 相关子查询：通过主查询A表，查找与A表相关的B表的信息，返回查找的信息

1. 定义
   $\bullet$ 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次。
   $\bullet$ 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
   $\bullet$ 子查询中使用主查询中的列

#相关子查询
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
-----------------------------------------------------------------
#1.相关子查询方式
#思路：求员工部门的平均工资后与直接与员工工资比较

#1.1 某员工的 部门的平均工资
SELECT AVG(salary)
FROM employees e2
WHERE e2.`department_id` = e1.`department_id`

#1.2 员工工资与本部门平均工资比较
select last_name,salary,department_id
from employees e1
where e1.`salary` > (
			SELECT AVG(salary)							  #相关子查询：每次查询都子查询都要重新计算一次
			FROM employees e2                             #子查询中使用主查询中的列
			WHERE e2.`department_id` = e1.`department_id` #过滤得到相同部门行，聚合函数作用于过滤得到整体的结果，即求得本部门平均工资
			);

-----------------------------------------------------------------
#2.内连接：在FROM中查询
#思路：连接，将各部门工资查询结果作为一个表，连接employees表后进行工资比较

#2.1各部门工资
SELECT e.`department_id`,AVG(salary)
FROM employees e
group by e.`department_id`

#2.2连接employees表后进行工资比较
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1 join (SELECT e.department_id,AVG(salary) dep_sal FROM employees e GROUP BY e.`department_id`) e2
on e1.`department_id`= e2.department_id
where e1.`salary`>e2.dep_sal;

---

$▶$ 结论：$★$$★$
在SELECT中，除了GROUP BY和LIMIT之外，其他位置都可以声明子查询！

总结：
# 1.SELECT中子查询
#题目：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id,last name,(CASE department id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
				   								 ELSE 'USA' END) "location"
FROM employees

#CASE中的子查询
题目：显示员工的employee_id,last_name和location。
SELECT employee_id,last_name,(CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
				   								                       ELSE 'USA' END) "location"
FROM employees

#2.FROM中子查询
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#2.在FROM中查询
#思路：连接，将各部门工资查询结果作为一个表，连接employees表后进行工资比较
#2.1各部门工资
SELECT e.`department_id`,AVG(salary)
FROM employees e
group by e.`department_id`
#2.2连接employees表后进行工资比较
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1 join (SELECT e.department_id,AVG(salary) dep_sal FROM employees e GROUP BY e.`department_id`) e2
on e1.`department_id`= e2.department_id
where e1.`salary`>e2.dep_sal;

#3.HAVING中子查询
题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
		(SELECT MIN(salary)
		FROM employees
		WHERE department_id = 50);
		
#4.在ORDER BY中子查询
#题目：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
	SELECT department_name
	FROM departments d
	WHERE e.department_id = d.department_id);

$★$$★$ 总结：

子查询：由外而内、由内而外
什么时候由外而内？：查询较简单时，
什么时候由内而外？：查询较复杂时，更清晰

每层：连接过滤，分组过滤，选择去重与排序

#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id

SELECT last_name,salary,department_id
FROM employees e1
WHERE e1.`salary` > (
			SELECT AVG(salary)			      #相关子查询：每次查询都子查询都要重新计算一次
			FROM employees e2                             #子查询中使用主查询中的列
			WHERE e2.`department_id` = e1.`department_id` #过滤得到相同部门行，聚合函数作用于过滤得到整体的结果，即求得本部门平均工资
			);
			
SELECT last_name,salary,department_id
FROM employees e1
WHERE e1.`salary` > (
	SELECT avg_sal
	FROM (
		SELECT department_id,AVG(salary) avg_sal 
		FROM employees 
		GROUP BY department_id) dep_avg_sal
	WHERE e1.department_id = dep_avg_sal.department_id
);

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
			SELECT department_id
			FROM employees
			WHERE last_name='Zlotkey'
) ;


#2.查询工资 比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
		SELECT AVG(salary)
		FROM employees
		);

#3.选择工资 大于 (所有JOB_ID = 'SA_MAN'的员工的工资的)员工的last_name, job_id, salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary>(
		SELECT MAX(salary)
		FROM employees
		WHERE job_id='SA_MAN'
		);

#4.（内：查询姓名中包含字母u的员工） 外:查询在相同部门的员工的 员工号和姓名
#分析：相关子查询，每次查询时子查询的操作不同
SELECT job_id,last_name
FROM employees
WHERE department_id = (
			SELECT department_id 
			FROM employees
			WHERE last_name LIKE '%u%'
			);



#5.查询 部门的location_id为1700的部门(部门id) (这些部门)工作的员工的员工号
SELECT employee_id,location_id
FROM employees e JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE location_id = 1700;

SELECT employee_id
FROM employees
WHERE department_id IN (
			SELECT department_id
			FROM departments
			WHERE location_id = 1700
			)

#6.查询 管理者是King的 员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id =ANY (
			SELECT employee_id 
			FROM employees
			WHERE last_name='King'
			);
			
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = 'King'
)
			

#7.查询工资最低的员工信息: last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);


#8.查询平均工资最低的部门信息
#分组过滤：平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id #3.分组过滤：查找部门平均工资等于最低平均工资的部门id
HAVING AVG(salary) = ( 
	SELECT MIN(avg_sal) #2.各部门中最低平均工资
	FROM (
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) dep_avg_sal #1.各部门平均工资
)

#查询平均工资最低的部门信息
SELECT *
FROM departments
#1.过滤条件：部门id = 平均工资最低的部门id
WHERE department_id =( 
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤：查找部门平均工资等于最低平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MIN(avg_sal) #3.1选择：各部门中最低平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #4.1分组过滤：部门平均工资
	     )
)

连接过滤，分组过滤，选择去重与排序
#9.查询平均工资最低的部门信息 和 该部门的平均工资（相关子查询）
SELECT *        #1.3选择连接表的所有列
FROM departments d 
RIGHT JOIN (    #1.1右外连接：得到所有部门的信息和平均工资
		SELECT department_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id) dep_avg_sal 
ON d.`department_id`= dep_avg_sal.department_id
WHERE d.department_id =(  #1.2过滤条件：部门id = 平均工资最低的部门id
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤：查找部门平均工资等于最低平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MIN(avg_sal) #各部门中最低平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #部门平均工资
	     )
)


#10.查询平均工资最高的 job 信息
SELECT *    #1.2 选择：在jobs表中通过job_id选择所有列    
FROM jobs j
WHERE j.job_id = ( #1.1 过滤:job_id = 平均工资最高的job_id
	SELECT job_id
	FROM employees
	GROUP BY job_id #2.1分组过滤：按job_id分组，查找job_id=平均工资最高的job_id
	HAVING AVG(salary) = ( 
		SELECT MAX(avg_sal) #3.1选择：各工作中最高平均工资
		FROM (
			SELECT job_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY job_id) job_avg_sal #4.1分组过滤：各部门平均工资
	)
)


#11.查询平均工资 高于 公司平均工资的 部门有哪些?
SELECT *
FROM departments
WHERE department_id IN( #1.1过滤：部门id = (平均工资高于公司平均工资的)部门id
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤：部门平均工资>公司平均工资的部门id
	HAVING AVG(salary) > (
		SELECT AVG(salary)
		FROM employees
	     )
)

#查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;


#12.查询出公司中所有 manager 的详细信息
SELECT employee_id
FROM employees 
WHERE employee_id IN (
	SELECT manager_id							  
	FROM employees                           
)


#13.各个部门中最高工资中 最低的那个部门的 最低工资是多少?

#1.2 选择：在departments表中通过department_id选择所有列, 最高工资中的最低工资
SELECT d.*,(SELECT MIN(max_sal)FROM (SELECT department_id,MAX(salary) max_sal FROM employees GROUP BY department_id) dep_max_sal) min_sal 
FROM departments d
#1.1 过滤:department_id = 最高工资中的最低工资的department_id
WHERE d.department_id = ( 
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤：按department_id分组，查找department_id=工资最低的department_id
	HAVING AVG(salary) = ( 
		SELECT MIN(max_sal) #3.1选择：各部门最高工资中的最低工资
		FROM (
			SELECT department_id,MAX(salary) max_sal
			FROM employees
			GROUP BY department_id) dep_max_sal #4.1分组过滤：各部门最高工资
	)
)

SELECT MIN(max_sal)FROM (SELECT department_id,MAX(salary) max_sal FROM employees GROUP BY department_id) dep_max_sal #4.1各部门最高工资

#14.查询 平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id =( #1.1过滤条件：部门id = 平均工资最高的部门id
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤：查找部门平均工资等于最高平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MAX(avg_sal) #3.1选择：各部门中最高平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #4.1分组过滤：部门平均工资
	     )
)

#公司中所有 manager 的详细信息
SELECT employee_id
FROM employees 
WHERE employee_id IN (
	SELECT manager_id							  
	FROM employees                           
)

SELECT *
FROM employees 
#1.1过滤：是平均工资最高的部门 且 是管理者
WHERE employee_id IN (SELECT manager_id FROM employees)
AND department_id =( 
	SELECT department_id
	FROM employees
	GROUP BY department_id #2.1分组过滤：查找部门平均工资等于最高平均工资的部门
	HAVING AVG(salary) = ( 
		SELECT MAX(avg_sal) #3.1选择：各部门中最高平均工资
		FROM (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id) dep_avg_sal #4.1分组过滤：部门平均工资
	     )
)

#15. 查询 部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT DISTINCT department_id 
FROM employees 
WHERE department_id NOT IN (
	SELECT DISTINCT department_id 
	FROM employees 
	WHERE job_id='ST_CLERK')

#16. 选择 所有没有管理者的 员工的last_name
SELECT last_name
FROM employees
WHERE manager_id IS NULL

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT employee_id,last_name,salary
FROM employees
WHERE manager_id = (
	SELECT employee_id
	FROM employees 
	WHERE last_name = 'De Haan');


#18.查询 各部门中工资 比 本部门平均工资高的 员工的员工号, 姓名和工资（相关子查询）
SELECT employee_id,last_name,salary
FROM employees e1
WHERE e1.salary > ( 
	SELECT AVG(salary)
	FROM employees e2
	WHERE e1.department_id = e2.`department_id` #部门平均工资
	)
)


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT department_name
FROM departments d
WHERE d.department_id IN (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING COUNT(employee_id)>5
)

#20.查询 每个国家下的 部门个数 大于2的  国家编号（相关子查询）
#表A: locations(国家编号country_id)
#表B: departments
#表AB的关联条件A.a=B.a：l.location_id = d.location_id，每一次主查询都执行一次子查询

SELECT l.country_id,COUNT(department_id)
FROM departments d 
JOIN locations l
ON d.location_id = l.location_id
GROUP BY country_id
HAVING COUNT(department_id)>5

SELECT country_id 
FROM locations l 
WHERE 2 < ( #1.1过滤：部门个数>2
	SELECT COUNT(*) #2.2选择列或聚合函数：聚合函数计算，每个位置有几个部门
	FROM departments d
	WHERE l.`location_id` = d.`location_id` 
	#2.1相关子查询过滤：每次查询使用一个关联条件过滤B表，A.a=B.a，即：位置id=部门位置id，
	#过滤行：使用关联过滤条件 过滤 B表的各行，得到对应于该条件的结果
);


	

---

---

3.2 DDL、DCL

3.1.1 DDL：创建、更新、删除（数据库，表）

$▶$ SQL(Structured Query Language)概述

1. 定义：
   结构化查询语言，是关系数据库的标准语言
2. SQL语言 分类
   

$▶$ 数据库：创建、更新、删除、使用

#1.创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER CHARACTER SET 'utf-8';

#2.修改数据库
ALTER DATABASE mytest2 CHARACTER SET 'utf8'; #更改数据库字符集

#3.删除
DROP DATABASE IF EXISTS 数据库名;

#4.使用查看
#4.1.使用数据库
USE atguigudb;
SHOW TABLES; #查看当前数据库中保存的数据表
SHOW TABLES FROM 数据库名; #查看指定库下所有的表

#4.2查看当前所有的数据库
SHOW DATABASES;

#4.3查看当前正在使用的数据库
SELECT DATABASE();

#4.4查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;

---

$▶$ 表：1创建、2更新、3删除、4清空

$▶$ 基本表的定义

1. 定义语句
   

1.创建表：直接创建和查询创建
USE atguigudb;
SHOW CREATE DATABASE atguigudb; #默认使用的是utf8
SHOW TABLES;

#方式1：直接创建，"白手起家"的方式
CREATE TABLE IF NOT EXISTS myemp1(   #需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15), #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);
#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1; #如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。
#查看表数据
SELECT * FROM myemp1;

#方式2：查询创建，基于现有的表，同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

DESC myemp2;
DESC employees;

SELECT *
FROM myemp2;

#说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
#说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT *
FROM myemp3;

DESC myemp3;


--------------------------------------------------------------------
2.修改表内容
#1. 修改表字段：ALTER TABLE 表 ADD\MODIFY\CHANGE\DROP 字段
DESC myemp1;

# 1.1 添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10,2); #默认添加到表中的最后一个字段的位置

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

# 1.2 修改一个字段：数据类型、长度、默认值（略）
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

# 1.3 重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

# 1.4 删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_email;

#2. 修改名表
ALTER TABLE myemp2 RENAME TO myemp12;
DESC myemp12;



--------------------------------------------------------------------
3.删除表
#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS 表

---

3.1.2 DCL-COMMIT 和 ROLLBACK：清空和回滚

4. 清空表
#清空表，表示清空表中的所有数据，但是表结构保留。
TRUNCATE TABLE 表;
DELETE FROM 表;

# 4.1 DCL 中 COMMIT 和 ROLLBACK
# COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
# ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

# 4.2 对比 TRUNCATE TABLE 和 DELETE FROM 
# 相同点：都可以实现对表中所有数据的删除，同时保留表结构。
# 不同点：
#	TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
#	DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据是可以实现回滚的。


4.3 DDL 和 DML 的说明
  ① DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL
    操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
  
  ② DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 
    SET autocommit = FALSE，则执行的DML操作就可以实现回滚。


# 演示：DELETE FROM 
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

# 演示：TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

$▶$ 使用规范

1. SQL大小写规范（建议遵守）：
   $\bullet$ 数据库名、表名、表别名、字段名、字段别名等都小写
   $\bullet$ SQL关键字、函数名、绑定变等都大写
2. 注释：
   $\bullet$ 单行注释：# 注释文字(MySQL特有的方式)
   $\bullet$ 单行注释：-- 注释文字(–后面必须包含一个空格。)
   $\bullet$ 多行注释：/* 注释文字 */

$▶$ 数据导入

1. 命令行客户端导入
   在命令行客户端登录mysql，使用source指令导入
   mysql> source d:\mysqldb.sql
2. 使用软件导入

---

---

---

3.3 DML(表的增删改)

DML之增删改

#0. 储备工作
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);

DESC emp1;
SELECT * FROM emp1;

--------------------------------------------------------
#一、添加数据
#1.手动添加：是否指明添加字段、是否多条插入
#1.1没有指明添加的字段
#正确的
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); #注意：一定要按照声明的字段的先后顺序添加

#错误的
INSERT INTO emp1
VALUES (2,3400,'2000-12-21','Jerry');

#1.2指明要添加的字段 （推荐）
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');
# 说明：没有进行赋值的hire_date 的值为 null
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');

#同时插入多条记录 （推荐）
INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);

#2.查询添加，指明添加字段
INSERT INTO emp1(id,NAME,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date  # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (70,60);

SELECT * FROM emp1;
DESC emp1;
DESC employees;

#注意：emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
#如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。

--------------------------------------------------------
#2. 更新数据 （有约束）：把set的位置类比select的位置，先过滤再更新
# UPDATE .... SET .... WHERE ...
# 可以实现批量修改数据的。

UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

SELECT * FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;

#题目：将表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE NAME LIKE '%a%';

#修改数据时，是可能存在不成功的情况的。（可能是由于约束的影响造成的）
UPDATE employees
SET department_id = 10000
WHERE employee_id = 102;

--------------------------------------------------------
#3. 删除数据（有约束）： DELETE FROM .... WHERE....
DELETE FROM emp1
WHERE id = 1;

#在删除数据时，也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id = 50;

#小结：DML操作默认情况下，执行完以后都会自动提交数据。
# 如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE，配合rollback使用

--------------------------------------------------------
#4. MySQL8的新特性：计算列
#计算列：通过别的列计算得来的，自动更新
USE atguigudb;

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL  #字段c即为计算列
);

INSERT INTO test1(a,b)
VALUES(10,20);

DESC test1
SELECT * FROM test1;

UPDATE test1
SET a = 100; 

$▶$ 数据定义

1. SQL的数据定义功能：定义各种数据库的“对象”
   $\bullet$ 模式
   $\bullet$ 基本表
   $\bullet$ 视图
   $\bullet$ 索引
   

---

---

3.2.2 基本表的定义、修改、删除

$▶$ 基本表的定义

1. 定义语句
   
2. 完整性约束条件
   $\bullet$ <列级完整性约束条件>：涉及相应属性列的完整性约束条件
   $\bullet$ <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
   $\bullet$ 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上。
   

---

$▶$ 基本表的修改：增删改

# 增：
# 1.VALUES的方式
INSERT INTO 表名 VALUES( ) 

# 2.查询方式
INSERT INTO 表名(tar_column1 [, tar_column2, …, tar_columnn])
SELECT(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
WHERE ...

# 改：
UPDATE 表名 SET ...WHERE ...

# 删：
DELETE FROM 表名 WHERE ...

---

---

3.5 视图

1. 视图
   $\bullet$ 视图是一种 虚拟表 ，本身是不具有数据的，占用很少的内存空间，视图是从一个或几个基本表（或视图）导出的表。
   $\bullet$ 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。数据库中只存放视图的定义，而不存放视图对应的数据。
   $\bullet$ 视图的优点：
   简化查询；控制数据的访问
2. 定义视图
   $\bullet$ 组成视图的属性列名或者全部省略或者全部指定
3. 查询视图
   视图定义后，用户就可以像对基本表一样对视图进行查询了。
4. 更新视图数据
   $\bullet$ 用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作
   视图的作用
   $\bullet$ 要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。
   $\bullet$ 虽然可以更新视图数据，但总的来说，视图作为 虚拟表 ，主要用于 方便查询 ，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。
5. 修改视图
6. 删除视图

# 1.创建视图
CREATE VIEW 视图名称
AS 查询语句

#2.修改视图
#方式1：使用CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW 视图名称
AS
查询语句

#方式2：ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句

#3.删除视图
#删除视图只是删除视图的定义，并不会删除基表的数据。
DROP VIEW IF EXISTS 视图名称

约束

$▶$ 约束概述

1. 数据完整性（Data Integrity）
   是指数据的精确性（Accuracy）和可靠性(Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
2. 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
   $\bullet$ 实体完整性(Entity Integrity)
   $\bullet$ 域完整性(Domain Integrity)
   $\bullet$ 引用完整性（Referential Integrity）
   $\bullet$ 用户自定义完整性(User-defined Integrity)

第6章 数据库设计规范

6.1 问题的提出

总之，开始设置数据库的时候，我们就需要重视数据表的设计。为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。

---

---

6.2 范式

• 在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。 可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
• 目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯·科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

函数依赖

---

$▶$ 码（键）

1. 超键：
   能唯一标识元组的属性集叫做超键。
2. 候选键：
   如果超键不包括多余的属性，那么这个超键就是候选键。
3. 主键：
   用户可以从候选键中选择一个作为主键。
4. 外键：
   如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。
5. 主属性：
   包含在任一候选键中的属性称为主属性。
6. 非主属性：
   与主属性相对，指的是不包含在任何一个候选键中的属性。

通常，我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。

---

$▶$ 范式

1. 第一范式
   主要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。
2. 第二范式
   $\bullet$ 在满足第一范式的基础上
   $\bullet$ 唯一标识：
   满足数据表里的每一条数据记录，都是可唯一标识的。
   $\bullet$ 完全依赖：
   而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。 如果知道主键的所有属性的值，就可以检索到任
   何元组（行）的任何属性的任何值。（要求中的主键，其实可以拓展替换为候选键）。

3. 3NF
4. BCNF

6.2.7多值依185
6.2.8 4NF.188
6.2.9规范化小189
6.3数据依赖的公理系统190
*6.4模式的分解194
6.4.1模式分解的三个定义194
6.4.2分解的无损连接性和保持
函数依赖性·196
6.4.3模式分解的算法198
6.5 小.201

视图
3.7.1
定义视图
3.7.2
查询视图
3.7.3
更新视图
3.7.4
视图的作用

视图

约束pdf
视图pdf
存储过程与函数.pdf
变量、流程控制与游标.pdf
触发器.pdf
MySQL8其它新特性pdf

---

---

---

------------------------------------------------------------------------

第10章 事务

10.1 事务基础知识

$▶$ 事务的ACID原则

1. 原子性
2. 一致性 consistency
   $\bullet$ 根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。
   $\bullet$ 这种状态是语义上的而不是语法上的，跟具体的业务有关。
   $\bullet$ 合法的数据状态：
   满足预定的约束的状态就叫做合法的状态，通俗一点，这状态是由你自己来定义的(比如满足现实世界中的约束)。如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态。
3. 隔离性 isolation
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性
   $\bullet$ 是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
   $\bullet$ 持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。
   $\bullet$ 当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

10.2 并发控制概述

数据不一致性：由于并发操作破坏了事务的隔离性

$▶$ 并发操作带来的数据不一致性

1. 丢失修改（Lost Update）
   两个事务T和T2读入同一数据并修改，T2的提交结果破坏了T1提交的结果，导致T的修改被丢失。
2. 不可重复读(Non-repeatable Read)
   $\bullet$ 事务1读取数据，事务2修改该数据
   $\bullet$ 事务1读取数据，事务2删除该数据
   $\bullet$ 事务1读取数据，事务2插入数据
3. 读“脏”数据（Dirty Read）
   $\bullet$ 事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致
   $\bullet$ T2读到的数据就为“脏”数据，即不正确的数据

---

$▶$ 并发控制的主要技术

1. 封锁(Locking)
2. 时间戳(Timestamp)
3. 乐观控制法
4. 多版本并发控制(MVCC)

$▶$ 封锁(Locking)

1. 封锁(Locking)
   $\bullet$ 封锁就是事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁，加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。
   $\bullet$ 一个事务对某个数据对象加锁后究竟拥有什么样的控制由封锁的类型决定。
2. 基本封锁类型
   排它锁(Exclusive Locks，简记为X锁)
   共享锁(Share Locks,，记为S锁)
3. 排它锁(Exclusive Locks，简记为X锁)
   $\bullet$ 若事务T对数据对象A加上X锁，则只允许T读取和修改A,其它**$\bullet$ 任何事务都不能再对A加任何类型的锁**，直到T释放A上的锁
   保证其他事务在T释放A上的锁之前不能再读取和修改A
   $\bullet$ 排它锁又称为写锁
4. 共享锁(Share Locks,，记为S锁)
   $\bullet$ 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A,其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁
   $\bullet$ 保证其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改
   $\bullet$ 共享锁又称为读锁

---

$▶$ 封锁协议

1. 在运用X锁和S锁对数据对象加锁时，需要约定一些规则，这些规则为封锁协议（Locking Protocol）。
   $\bullet$ 何时申请X锁或S锁
   $\bullet$ 持锁时间
   $\bullet$ 何时释放

---

$▶$ 三级封锁协议

1. 一级封锁协议
   $\bullet$ 事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。
   正常结束(COMMIT)
   非正常结束(ROLLBACK)
   $\bullet$ 一级封锁协议可防止丢失修改，并保证事务T是可恢复的。
   
2. 二级封锁协议
   $\bullet$ 一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。
   $\bullet$ 二级封锁协议可以防止丢失修改和读“脏”数据。
   
3. 三级封锁协议
   $\bullet$ 一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。
   $\bullet$ 三级封锁协议可防止丢失修改、读脏数据和不可重复读。
   
4. 总结
   

10.2 可串行化调度与冲突可串行化调度

$▶$ 可串行化调度

1. 可串行化(Serializable)调度
   $\bullet$ 多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同
2. 可串行性(Serializability)
   $\bullet$ 是并发事务正确调度的准则
   $\bullet$ 一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度

---

$▶$ 冲突可串行化调度

1. 冲突可串行化调度
   $\bullet$ 一个调度S在保证冲突操作的次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度S’,如果S’是串行的，称调度S’是冲突可串行化的调度
   $\bullet$ 若一个调度是冲突可串行化，则一定是可串行化的调度
2. 不同事务冲突操作：
   $\bullet$ 不同的事务对同一数据的读写操作和写写操作
   涉及同一个数据库元素，并且至少有一个是写操作
3. 不能交换(Swap)的动作：
   $\bullet$ 同一事务的两个操作，是系统安排的顺序
   $\bullet$ 不同事务的冲突操作

---

例

---

---

问题

1.在cmd 中输入了错误mysql命令后，如何退出?

$▶$ ****

1. 第一种方法：
   mysql以“;” 结尾，打一个“；” 试下。
2. 第二种办法是:
   ‘>SA’;按ENTER回车,就OK了.
   mysql> ,不过错了显示,但不用去理它.
   注意:SA是随便输入的,后要 '+;就可以退去了.
3. 第三种方法：
   ctrl+C；

例如:
mysql> select * from tb_name

         '>

         '>

         '>

         '>

---