MySQL 相关笔记

`MySQL` 基础知识

1 数据库相关概念

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

常见的 DBMS：Oracle、MySQL、SQL Server 等。我们以 MySQL 作为课程的讲解的管理系统。

2 `MySQL` 数据库的数据模型

关系型数据库（RDBMS）：建立在关系模型基础上，由多张相关联的二维表 Table 组成的数据库。如

MySQL 数据库的结构为：

$\text{MySQL客户端} \to \text{DBMS} \to \text{DB} \to \text{表 (Table)} \to \text{记录}$

3 SQL 语句

3.1 `SQL` 的通用语法

SQL通用语法

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

单行注释：

1
2
3

-- 注释内容 
或
#注释内容(MySQL特有）

多行注释：
1
/* 注释内容*/

3.2 `SQL` 的分类

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

3.3 `DDL` 数据定义语句

数据库相关操作

SHOW 查询所有数据库

1	SHOW DATABASES;

CREATE 创建数据库

1	CREATE DATABASE [IF NOT EXISTS] '数据库名' [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

例如

1 2	CREATE TABLE IF NOT EXISTS 'test' DEFAULT CHARSET utf8 COLLATE DEC; -- 如果不存在‘test’数据库则创建一个 utf8字符集、降序排列的数据库

USE 切换数据库

1	USE '数据库名';

SELECT DATABASE() 显示当前数据库

1	SELECT DATABASE();

DROP 删除数据库

1	DROP DATABASE [IF EXISTS] '数据库名';

表相关操作

SHOW TABLES 查询当前数据库的所有表

1	SHOW TABLES;

DESC 查询表结构

DESC 表名;

查询指定表的建表语句

1	SHOW CREATE TABLE 表名;

CREATE 创建表

关系名（表名）、列名（属性名或字段名）、属性的数据类型、完整性约束

CREATE TABLE '表名' (
	'列名' 数据类型 列的完整性约束 [COMMENT 字段的注释],
	'列名' 数据类型 列的完整性约束 [COMMENT 字段的注释],
  ... ,
) 表的的完整性约束  [COMMENT 表的注释];

例如

CREATE TABLE IF NOT EXISTS emp(
	id INT PRIMARY KEY COMMENT '职员编号',
	name VARCHAR(20) COMMENT '姓名',
	age INT COMMENT '年龄',
	gender VARCHAR(1) COMMENT '性别',
	dept_id INT COMMENT '部门编号',
	manager_id INT COMMENT '部门领导编号'
) COMMENT '职员表';

CREATE TABLE IF NOT EXISTS dept(
	id INT PRIMARY KEY COMMENT '部门编号',
	name VARCHAR(20) COMMENT '部门名称',
	address VARCHAR(100) DEFAULT NULL COMMENT '办公地点'
) COMMENT '部门表';

ALTER 表中添加、修改属性

ALTER TABLE '表名' (
  -- 添加
	ADD COLUMN '新列名' 数据类型 列的完整性约束 ,
  ADD 完整性约束 ,
  
  -- 修改
  MODIFY '列名' 数据类型 , -- 修改数据类型
  CHANGE '旧列名' '新列名' 数据类型 [COMMENT '注释'] [约束], -- 修改列名和数据类型
  
  -- 删除
  DROP COLUMN '列名' ,
  DROP 完整性约束 ,
  
  -- 修改表名
  RENAME TO '新表名'
);

DROP 删除表

1	DROP TABLE [IF EXISTS] '表名';

TRUNCATE 格式化表

1 2	-- 删除指定表，并重新创建该表 TRUNCATE TABLE '表名'

数据类型

（1）数值类型

类型	内存大小	范围（`SIGNED`）	范围（`UNSIGNED`）	类似 Java 中的类型
`TINYINT`	1 Byte	(-2^7, 2^7)	(0, 2^8-1)	`btye`
`SMALLINT`	2 Bytes	(-2^15, 2^15)	(0, 2^15-1)	`short`
`MEDIUMINT`	3 Bytes	(-2^23, 2^23)	(0, 2^24-1)	-
`INT` 或 `INTEGER`	4 Bytes	(-2^31, 2^31)	(0, 2^32-1)	`int`
`BIGINT`	8 Bytes	(-2^63, 2^64)	(0, 2^64-1)	`long`
`FLOAT`	4 Bytes			`float`
`DOUBLE`	8 Bytes			`double`
`DECIMAL`	-	取决于精度与标度	取决于精度与标度	-

（2）字符串类型

类型	大小	描述
`CHAR`	0～2^8-1 Bytes	定长字符串。例 `CHAR(10)`：10 Bytes 的固定长度，会补位。浪费空间。
`VARCHAR`	0～2^16-1 Bytes	变长字符串。例 `VARCHAR(10)`：最大 10 Bytes 的长度，与实际大小相关，不会补位。浪费性能。
`TINYBLOB`	0～2^8-1 Bytes	不超过 255 个字符串的二进制数据
`TINYTEXT`	0～2^8-1 Bytes	短文本字符串
`BLOB`	0～2^16-1 Bytes	二进制形式的长文本数据
`TEXT`	0～2^16-1 Bytes	长文本数据
`MEDIUMBLOB`	0～2^24-1 Bytes
`MEDIUMTEXT`	0～2^24-1 Bytes
`LONGBLOB`	0～2^64-1 Bytes
`LONGTEXT`	0～2^64-1 Bytes

（3）日期时间类型

类型	大小	格式	格式	描述
`DATE`	3 Bytes	1000-01-01 至 9999-12-31	`YYYY-MM-DD`	日期值
`TIME`	3 Bytes	-838:59:59 至 838:59:59	`HH:MM:SS`	时间值或持续时间
`YEAR`	1 Bytes	1901 至 2155	`YYYY`	年份值
`DATETIME`	8 Bytes	1000-01-01 00:00:00 至 9999-12-31 23:59:59	`YYYY-MM-DD HH:MM:SS`	混合日期和时间
`TIMESTAMP`	4 Bytes	1970-01-01 00:00:01 至 2038-01-19 03:14:07	`YYYY-MM-DD HH:MM:SS`	时间戳：混合日期和时间

3.4 `DML` 数据操作语言

`INSERT` 插入语句

-- 将指定元组加入指定表中
INSERT 
INTO 表名 [(列名1,列名2,...,列名n)]
VALUES val_1,val_2,...,val_n;
-- 或子查询

例如

1	INSERT INTO table_test(id, name, age, gender) VALUES 2, 'Mike', 20, '男';

`UPDATE` 修改语句

1
2
3

UPDATE 表名
SET 列名 = 值
[WHERE _condition] -- 缺省时表示修改属性名下的所有记录

例如

1 2	-- 修改 table_test 表中 ID为2 的name属性为Neil UPDATE table_test SET name = 'Niel' WHERE id = 2;

`DELETE` 删除语句

1
2
3

UPDATE
FROM 表名
[WHERE _condition]

例如

1	DELETE FROM table_test WHERE id = 2;

3.5 `DQL` 数据查询语言（单表）

DQL(Data Query Language) 数据查询语言，用来查询数据库记录的语言。

DQL 语法：

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

常见的聚合函数

常用聚合函数：null 值不参与统计

COUNT (列名)：统计记录的个数（行数）
1
SELECT COUNT(id) FROM table_test;
SUM (列名)：计算列总和
AVG (列名)：计算列平均值
MAX (列名)：计算列最大值
MIN (列名)：计算列最小值

`SELECT` 查询语句

-- 当满足_condition的条件下，从表T中查询返回去重的列A
SELECT DISTINCT A as '列A的别名' , B as '列B的别名'-- DISTINCT 显示查询结果无重复
FROM T
WHERE _condition;

查询多个字段

1 2	SELECT 字段1, 字段2,... FROM 表名; SELECT * FROM 表名;

设置别名

1	SELECT 字段1[as 别名1], 字段2[as 别名2],... FROM 表名;

返回去重的查询结果

1	SELECT DISTINCT * FROM 表名;

`WHERE` 条件查询

WHERE ：条件子句，可以用 AND / OR 连接多个条件

【比较大小】：=, >, <, >=, <=, <>, !<, !>, NOT
1
SELECT * FROM table_test WHERE id > 1

【确定范围 (包含)】：列名 [NOT] BETWEEN ... AND...

1	SELECT DISTINCT * FROM table_test WHERE age BETWEEN 10 AND 40;

【指定集合】：列名 [NOT] IN (Val1, Val2, Val3)，意为在查询结果中筛选列名的值为 Val1, Val2, Val3 的记录
1
SELECT * FROM table_test WHERE age IN (13, 15, 16);
【字符串匹配】：列名 [NOT] LIKE '匹配目标串' [ESCAPE '转义字符']
- % 通配符：代表任意长度的字符串。 a%b 表示以 a 开始，b 结束的任意长度的字符串
  1
  SELECT * FROM table_test WHERE name LIKE 'M%e';
- _ 通配符：代表单个字符。a_b 表示a开始，b结束的长度为3的字符串
  1
  SELECT * FROM table_test WHERE name like 'Ni_l';

【涉及空值】：IS [NOT] NULL

1	SELECT * FROM table_test WHERE name IS NULL;

`GROUP BY` 分组查询

【GROUP BY】：对查询结果分组，

分组方法：按指定的一列或多列值分组，值相等的为一组。

HAVING：功能类似于 WHERE（分组前的数据），但是作用域是分组后的数据（HAVING）

-- 1. 根据性别分组，统计男性与女性的数量
SELECT gender, count(*) FROM table_test GROUP BY gender;

-- 2. 根据性别分组，统计男性与女性的平均年龄
SELECT gender, avg(age) FROM table_test GROUP BY gender;

-- 3. 查询年龄小于45岁的人，并且根据工作地址分组，获取员工数量大于等于3的工作地址
SELECT address, count(*) FROM table_test 
	WHERE age < 45 GROUP BY address HAVING count(*) >= 3;

`ORDER BY` 排序查询

【ORDER BY】：对查询结果进行排序

1	SELECT * FROM table_test ORDER BY 列名1 DESC, 列名2 DESC, ...;

列名1,列名2 ASC：升序（默认，可省略）
列名1,列名2 DESC：降序

-- 1. 根据年龄对所有人进行升序/降序排序
SELECT * FROM table_test ORDER BY age;
SELECT * FROM table_test ORDER BY age DESC;

-- 2. 根据年龄对所有人进行升序排序, 如果年龄相同，再按照id降序排序
SELECT * FROM table_test ORDER BY age ASC, id DESC;

`LIMIT` 分页查询

【关键字】：LIMIT

1	SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

注意 ⚠️：

【起始索引】从 0 开始， $起始索引 = (查询页码 - 1) \times 每页的记录数$

LIMIT 的功能，在不同数据库管理系统中有不同的实现，mySQL 中是 LIMIT

如果查询的是第一页的内容，起始索引可以省略，直接简写为 LIMIT 查询记录数

-- 1. 查询第1页的数据，每页展示10条记录
SELECT * FROM table_test LIMIT 0,10;

-- 2. 查询第2页的数据，每页展示10条记录
SELECT * FROM table_test LIMIT 10,10; -- 10 = (2-1)*10

`DQL` 的执行顺序

SELECT 字段列表          -- 4
FROM 表名               -- 1
WHERE 条件              -- 2
GROUP BY 分组           -- 3
HAVING 分组后的条件      -- 3
ORDER BY 排序           -- 5
LIMIT 分页查询           -- 6

DQL 的执行顺序：（可用 AS 别名 验证）

（1）FROM 表名 $\to$ （2）WHERE 条件 $\to$ （3）GROUP BY 分组 [HAVING 分组后的条件] $\to$ （4）SELECT 字段 $\to$ （5）ORDER BY 排序 $\to$ （6）LIMIT 分页

3.6 `DCL` 数据控制语言

DCL (Data Control Language) 数据控制语言，用来管理数据库用户，及其访问数据库的权限。

管理用户

【查询用户】：在 mySQL 数据库中，用户的信息都存放在 user 表中
1
2
USE mysql;
SELECT * FROM user;

【创建用户】：

1	CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

当主机名为 % 时，表示任意主机的通配符

例如：

-- 1. 创建一个用户gdai，他只能够在当前主机localhost访问，密码123456
CREATE USER 'gdai'@'localhost' IDENTIFIED BY '123456';

-- 2. 创建一个用户gdai，他可以在任意主机访问该数据库，密码123456
CREATE USER 'gdai'@'%' IDENTIFIED BY '123456';

【修改用户密码】

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

例如：

1 2	-- 修改用户gdai的密码为1111 ALTER USER 'gdai'@'%' IDENTIFIED WITH mysql_native_password BY '1111';

【删除用户】

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

例如：

1 2	-- 删除用户gdai DROP USER 'gdai'@'%';

管理权限

mySQL 数据库中定义了许多种权限，但是常用的有以下几种：

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

查询权限

1	SHOW GRANTS FOR '用户名'@'主机名'；

授予权限

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

撤销权限

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

例如：

-- 查询用户gdai的权限
SHOW GRANTS FOR 'gdai'@'%';

-- 授予用户gdai关于table_test数据库表的所有权限
GRANT ALL ON table_test.* ON 'gdai'@'%';

-- 撤销用户gdai关于table_test数据库表的删除权限
REVOKE DELETE ON table_test.* ON 'gdai'@'%';

4 函数

我们在之前 DQL 中提到过 COUNT、AVG、SUM 等聚合函数，本小节中，我们将详细介绍 mySQL 中内置函数相关的内容。

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

4.1 字符串函数

mySQL 数据库中常用的字符串函数有以下几种：

函数名	功能
`CONCAT(s1, s2, s3, ..., sn)`	字符串拼接，将 `s1,...,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` 的 `strat` 处截取长度为 `len` 的子串

4.2 数值函数

mySQL 数据库中常用的数值函数有以下几种：

函数	功能
`CIEL(x)`	`x` 向上取整
`FLOOR(x)`	`x` 向下取整
`MOD(x,y)`	返回 `x mod y`
`RAND`	`0-1` 范围内的随机数
`ROUND(x,y)`	对 `x` 四舍五入，保留小数点后 `y` 位

4.3 日期函数

mySQL 数据库中常用的日期函数有以下几种：

函数	功能
`CURDATE()`	返回当前日期
`CURTIME()`	返回当前时间
`NOW()`	返回当前日期与时间
`YEAR(date)`	返回指定 `date` 的年份
`MONTH(date)`	返回指定 `date` 的月份
`DAY(date)`	返回指定 `date` 的天
`DATE_ADD(date, INTERVAL expr type)`	返回指定 `date` 加上一个时间间隔 `expr` 后的值
`DATEDIFF(date1, date2)`	返回 `date1` 与 `dete2` 之间的天数

4.4 流程控制函数

mySQL 数据库中常用的流程函数有以下几种：

函数	功能
`IF(value, t, f)`	如果 `value` 为真，返回 `t`，否则返回 `f`
`IFNULL(value1, value2)`	如果 `value1` 不为空，返回 `value1`，否则返回 `value2`
`CASE WHEN val1 THEN res1 WHEN val2 THEN res2 ... ELSE default END`	如果 `val1` 为真，返回 `res1`；如果 `val2` 为真，返回 `res2`，…，否则返回 `default`
`CASE expr WHEN val1 THEN res1 WHEN val2 THEN res2 ... ELSE default END`	如果 `expr` 的值为 `val1` ，返回 `res1`；如果为 `val2` ，返回 `res2`，…，否则返回 `default`

5 约束

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

常见的约束有以下几种：

约束	描述	关键字
非空约束	限制该字段的数据非空	`NOT NULL`
唯一约束	保证该字段的所有数据都是唯一的	`UNIQUE`
主键约束	主键是一条记录的唯一标识，要求非空且唯一	`PRIMARY KEY`
外键约束	用来在多张表的数据之间建立连接，保证数据的一致性和完整性	`FOREIGN KEY`
检查约束（`ver 8.0.16` 之后）	保证字段值满足某一个条件	`CHECK(expr)`
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	`DEFAULT`
自增约束（主键）	保证主键字段的数据自增	`AUTO_INCREMENT`

5.1 外键约束

【概念】：在多张表的数据之间建立连接，保证数据的一致性和完整性。

我们将拥有外键的表称为【从表】，外键所关联的表称为【主表】。

添加外键约束

1
2
3

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
	FOREIGN KEY (外键字段名) REFERENCES 主表(字段名)
	ON UPDATE 更新策略 ON DELETE 删除策略;

例如，我们有如下两个表，Client 和 Account。我们想在 Account 表中添加一个名为 client_ID 的外键。

1 2	ALTER TABLE Account ADD CONSTRAINT fk_client_ID_in_Account FOREIGN KEY (client_ID) REFERENCES Client(ID);

删除外键约束

1	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

例如：

1	ALTER TABLE Account DROP FOREIGN KEY fk_client_ID_in_Account;

更新/删除策略

mySQL 数据库提供以下几种更新策略：

行为	说明
`NO ACTION`	默认。当在主表中删除/更新相应记录时，首先检查该记录是否被关联作外键，如果有则不允许删除/更新（同 `RESTRICT`）。
`RESTRICT`	默认。当在主表中删除/更新相应记录时，首先检查该记录是否被关联作外键，如果有则不允许删除/更新（同 `NO ACTION`）。
`CASCADE`	当在主表中删除/更新相应记录时，首先检查该记录是否被关联作外键，如果有则同时删除/更新从表中的记录.
`SET NULL`	当在主表中删除相应记录时，首先检查该记录是否被关联作外键，如果有则在从表中将相关记录的字段值设置为 null
`SET DEFAULT`	当在主表中设置相应记录时，首先检查该记录是否被关联作外键，如果有则在从表中将相关记录的字段值设置为 default 值。（`innoDB` 不支持）

6 多表查询

在之前的 DQL 的学习中，我们涉及的都是单表查询。这一小节我们介绍多表查询。

6.1 多表关系

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

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

一对多（多对一）关系

案例：客户 Client 与账户 Account 的对应关系。

关系：一个客户 Client 可以有多个账户 Account，一个账户 Account 只能对应一个客户 Client。

实现：在“多”的一端（从表）添加一个外键指向“一”的一端（主表）

1 2	ALTER TABLE Account ADD CONSTRAINT fk_client_ID_in_Account FOREIGN KEY (client_ID) REFERENCES Client(ID);

多对多关系

案例：商品 Item 与品类 Category 的对应关系。

关系：一件商品 Item 可以对应很多中品类 Category，一个品类 Category 中也可以包含多种商品 Item。

实现：建立一张中间表 t_Item_Category，中间表中至少包含两个外键，分别关联双方的主键。

-- 创建中间表 t_Item_Category
CREATE TABLE t_Item_Category (
	ID int PRIMARY KEY AUTO_INCREMENT，
	item_ID int NOT NULL,
	category_ID int NOT NULL,
	CONSTRAINT fk_itemID FOREIGN KEY(item_ID) REFERENCES Item(ID),
	CONSTRAINT fk_categoryID FOREIGN KEY(category_ID) REFERENCES Category(ID)
) COMMENT '中间表';

一对一关系

案例：经理 Manager 与部门 Department 之间的关系

关系：一个经理 Manager 只能任职一个部门 Department；一个部门 Department 只能有一个经理 Manager。

实现：在任意一方加入外键（从表），关联另一方（主表）的主键，并且设置外键为 UNIQUE

1 2	ALTER TABLE Department ADD CONSTRAINT fk_ManagerID FOREIGN KEY (Manager_ID) REFERENCES Manager(ID);

6.2 多表查询概述

连接查询

【连接查询】又分为：内连接和外连接。

【内连接】

又叫等值连接。相当于查询A、B两张表交集的部分。

等值连接

1 2	-- 等值连接 SELECT * FROM 表1,表2 WHERE 表1字段=表2字段;

内连接有两种查询语法：

隐式内连接

1	SELECT 字段列表 FROM 表1,表2 WHERE 连接条件;

显式内连接：

1	SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

举例：

-- 1. 隐式内连接：查询每个员工的姓名（emp表），及关联的部门的名称（dept表）
SELECT emp.name, dept.name FROM emp, dept WHERE emp.dept_id=dept.id;

-- 2. 显式内连接：查询每个员工的姓名（emp表），及关联的部门的名称（dept表）
SELECT emp.name, dept.name FROM emp INNER JOIN dept ON emp.dept_id=dept.id;

查询结果如下：

注意 ⚠️：

在上述内查询中，我们可以注意到 emp.id = 2 的员工对应的 dept_id = null，在内连接中无法查询到到该条记录。

【外连接】

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

1	SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件;

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

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

举例：

【左外连接】查询：

1
2
3

-- 1. 左外连接：查询每个员工的姓名（emp表），及关联的部门的名称（dept表）
-- 例子中 emp 为左表，dept表为右表
SELECT emp.name, dept.name FROM emp LEFT OUTER JOIN dept ON emp.dept_id=dept.id;

查询结果如下：

【右外连接】查询：

1
2
3

-- 2. 右外连接：查询每个员工的姓名（emp表），及关联的部门的名称（dept表）
-- 例子中 emp 为左表，dept表为右表
SELECT emp.name, dept.name FROM emp RIGHT OUTER JOIN dept ON emp.dept_id=dept.id;

查询结果如下：

【自连接】

顾名思义，就是自己与自己的连接查询。查询时需要给表起别名。

1	SELECT 字段列表 FROM 表1 AS 表1别名 [LEFT] JOIN 表1 AS 表1别名 ON 连接条件;

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

举例：

1
2
3

-- 自连接：查询每个员工的姓名（emp表），及其管理人员的姓名（emp表）
-- 例子中 emp 为左表，dept表为右表
SELECT e.name, m.name FROM emp AS e LEFT JOIN emp AS m ON e.manager_id=m.id;

查询结果如下：

交叉连接（广义笛卡尔积）：不常用

1
2
3

SELECT col1.*,col2.*
FROM col1,col2
-- 返回col1和col2的广义笛卡尔积

联合查询

对于 union 联合查询，就是把多个查询结果通过 UNION(去重) 和 UNION ALL(不去重) 合并起来，成为一个新的查询结果集。

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

举例：

-- 1. 查询薪资低于 8000 的员工，和年龄大于 24 岁的员工
select * from emp where salery<8000
union
select * from emp where age>24;

查询结果为

注意 ⚠️

在联合查询中，查询的字段列表要保持一致，否则无法查询；

union all 会将所有查询结果合并在一起，union 会对合并之后的结果去重。

嵌套查询

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

SELECT 列名
FROM 表名
WHERE _condition IN[>,<,=,...] (
	SELECT 内层列名
	FROM 内层表名
	WHERE _condition2)

内层查询的结果是一个关于“内层列名”的集合
不能使用 ORDER BY 和 GROUP BY语句

根据嵌套查询返回结果的不同，可分为：

标量嵌套查询（返回的查询结果是单个值）；
列嵌套查询（返回的查询结果是一列）；
行嵌套查询（返回的查询结果是一行）；
表嵌套查询（返回的查询结果是多行多列的表）；

【标量嵌套查询】

标量嵌套查询返回的内层查询结果是单个值（数字、字符串、日期等）。

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

举例：

-- 1 查询"研发部"的所有员工信息：
--      （a）查询"研发部"的部门id
--      （b）根据上一步查询到的部门id，在emp表中查询员工信息
SELECT * FROM emp WHERE dept_id=(
    SELECT id FROM dept WHERE name='研发部'	-- (id=4)
  );

查询结果为

【列嵌套查询】

列嵌套查询返回的内层查询结果是一列（即多行数据），再将一列数据作为输入参与外层查询。

常见的操作符有：

操作符	描述
`IN`	在指定的集合范围之内，多选一
`NOT IN`	不在指定的集合范围之内
`ANY`	在内层查询返回的结果中，有任意一个满足就可以
`SOME`	同 `ANY`
`ALL`	内层查询返回的所有结果全部满足才可以

举例：

-- 1 查询 "人力资源部" 和 "研发部" 的所有员工信息
--      （a）查询 "人力资源部" 和 "研发部" 的部门id
--      （b）查询其对应的员工信息
SELECT * FROM emp WHERE dept_id IN (
    SELECT id FROM dept WHERE name='人力资源部' OR name='研发部'    -- (1,2)
    );

查询结果：

【行嵌套查询】

行嵌套查询返回的内层查询结果是一行（即多列数据），再将一行数据作为输入参与外层查询。

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

举例：

-- 1 查询与Bob的薪资和部门领导相同的与员工信息
--      （a）查询Bob的薪资和部门领导
--      （b）查询与Bob的薪资和部门领导相同的与员工信息
SELECT * FROM emp WHERE (salery,manager_id) = (
    SELECT salery, manager_id FROM emp WHERE name='Bob'     -- (7500,1)
    );

查询结果：

【表嵌套查询】

行嵌套查询返回的内层查询结果是多行多列数据（即表），再将这些数据作为输入参与外层查询。

常用到的操作符：IN

举例：

-- 1 查询与Theo，Tom的年龄和部门相同的员工信息
--      （a）查询Theo，Tom的年龄和部门
--      （b）查询与Theo，Tom的年龄和部门相同的员工信息
SELECT * FROM emp WHERE (age, dept_id) IN (
    SELECT age, dept_id FROM emp WHERE name='Theo' OR name='Tom'
    );

查询结果：

例 2:

-- 2 查询年龄大于24岁的员工信息，及其工作地点
--      （a）查询年龄大于24岁的员工信息
--      （b）查询上述员工的工作地点
SELECT temp_table.*, dept.address FROM (
    SELECT * FROM emp WHERE age>24
    ) AS temp_table LEFT JOIN dept ON temp_table.dept_id = dept.id;

查询结果：

6.3 练习

我们在数据库空定义如下图所示的表：

-- 1. 准备数据
-- 部门表
create table dept (
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
-- 添加数据
insert into dept(name) values ('研发部'), ('市场部'),('财务部'),('销售部'),('总经理办公室');

-- 职员表
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',
    deptID int comment '所属部门ID'
) comment '职员表';
-- 设置外键
alter table emp add constraint
    foreign key fk_deptID (deptID) references dept(id)
    on update cascade on delete set null ;
-- 添加数据
insert into emp(name, age, job, salary, entrydate, managerID, deptID) values
    ('金庸', 66, '总裁', 20000, '2000-01-01', null, 5), -- 1
    ('张无忌', 20, '项目经理', 12500, '2010-12-01', 1, 1), -- 2
    ('杨逍', 33, '开发', 8400, '2012-11-01', 2, 1),
    ('韦一笑', 48, '开发', 11000, '2002-02-01', 2, 1),
    ('常遇春', 43, '开发', 10500, '2004-09-01', 2, 1),
    ('小昭', 19, '会计', 8000, '2008-01-01', 7, 3),
    ('灭绝师太', 60, '财务总监', 7000, '2008-01-01', 1, 3), -- 7
    ('周芷若', 19, '会计', 6500, '2010-01-01', 7, 3),
    ('丁敏君', 23, '出纳', 4500, '2010-01-01', 7, 3),
    ('赵敏', 20, '市场总监', 12500, '2010-07-01', 1, 2), -- 10
    ('鹿杖客', 56, '职员', 6000, '2004-03-01', 10, 2),
    ('鹤笔翁', 67, '职员', 4500, '2008-01-01', 10, 2),
    ('张三丰', 88, '销售总监', 14000, '2000-03-01', 1, 4), -- 13
    ('宋青书', 30, '销售', 6000, '2004-01-01', 13, 4),
    ('陈友谅', 42, null, 2000, '2020-03-01', 1, null);

-- 薪资等级表
create table salaryGrade (
    grade int auto_increment comment '等级数' primary key,
    minSalary int not null comment '最低薪资',
    maxSalary int not null comment '最高薪资'
) comment '薪资等级表';
-- 添加数据
insert into salaryGrade(minSalary, maxSalary) values
    (1,3000),
    (3001,5000),
    (5001,8000),
    (8001,10000),
    (10001,15000),
    (15001,20000),
    (20001,25000),
    (25001,30000);

emp 表：

dept 表：

salaryGrade 表：

需求 1

查询员工的姓名、年龄、职位、部门名称（使用隐式内连接）

-- 1 查询员工的姓名、年龄、职位、部门名称（使用隐式内连接）
select emp.name, emp.age, emp.job, dept.name
    from emp,dept
    where emp.deptID = dept.id;

查询结果：

需求 2

查询年龄小于30岁的员工的姓名、年龄、职位和部门名称（显式内连接）

-- 2 查询年龄小于30岁的员工的姓名、年龄、职位和部门名称（显式内连接）
select emp.name, emp.age, emp.job, dept.name
    from emp inner join dept on emp.deptID = dept.id
    where emp.age<30;

查询结果：

需求 3

查询拥有员工的部门 ID 与部门名称

-- 3 查询拥有员工的部门 ID 与部门名称
-- 方法一
select dept.id, dept.name from dept where dept.id in (
    select distinct emp.deptID from emp
    );
    
-- 方法二
select distinct dept.id, dept.name from dept inner join emp on emp.deptID = dept.id;

查询结果：

需求 4

查询所有年龄大于 40 岁的员工，及其所属的部门名称；如果有员工没有分配部门，也要展示出来。

-- 4 查询所有年龄大于 40 岁的员工，及其所属的部门名称；如果有员工没有分配部门，也要展示出来。
--      分析：应该使用外连接，才能展示null的记录
select emp.*, dept.name
    from emp left join dept on emp.deptID=dept.id
    where emp.age>40;

查询结果：

需求 5

查询所有员工的工资等级

-- 5 查询所有员工的工资等级
select emp.name, salaryGrade.grade
    from emp,salaryGrade
    where emp.salary between salaryGrade.minSalary and salaryGrade.maxSalary;

查询结果：

需求 6

查询“研发部”的所有员工的信息以及薪资等级

-- 6 查询“研发部”的所有员工的信息以及薪资等级
--      分析     连接条件 1：salaryGrade.minSalary <= emp.salary <= salaryGrade.maxSalary
--              连接条件 2：emp.deptID = dept.id
--              查询条件：dept.name='研发部'
select tempTable.*
    from (select emp.*, salaryGrade.grade
            from emp,salaryGrade
            where emp.salary between salaryGrade.minSalary and salaryGrade.maxSalary)
        as tempTable left join dept on tempTable.deptID = dept.id
    where dept.name='研发部';
    
-- 或者
select emp.*, salaryGrade.grade
    from emp, dept, salaryGrade
    where emp.salary between salaryGrade.minSalary and salaryGrade.maxSalary
        and emp.deptID = dept.id
        and dept.name = '研发部';

查询结果：

需求 7

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

-- 7 查询“研发部”的平均工资
select avg(emp.salary) as '平均薪资'
    from emp join dept on emp.deptID=dept.id
    where dept.name='研发部';

查询结果：

需求 8

查询薪资比“常遇春”高的员工信息。

-- 8 查询薪资比“常遇春”高的员工信息。
select emp.* from emp where emp.salary > (
    select emp.salary from emp where emp.name='常遇春'
    );

查询结果：

需求 9

查询比平均工资高的员工信息

-- 9 查询比平均工资高的员工信息
select emp.* from emp where emp.salary > (
    select avg(emp.salary) from emp
    );

查询结果：

需求 10 *

查询低于本部门平均工资的员工信息。

-- 10 查询低于本部门平均工资的员工信息。
--      分析：（1）先查询出一个包含 deptID 和 avg(salary) 的，按 emp.deptID 分组的临时表 temp
--           （2）在用外连接连接查询 temp 和 emp。
--                  连接条件：temp.id = emp.deptID
--                  查询条件：emp.salary < temp.avg
select emp.name, emp.salary, temp.avg, emp.deptID, temp.id
from emp
         join
     (select emp.deptID as id, avg(emp.salary) as avg from emp group by emp.deptID) as temp
     on emp.deptID = temp.id
where emp.salary < temp.avg;

-- 或者：使用自连接查询
select e1.* from emp e1 where e1.salary < (
    select avg(e2.salary) from emp e2 where e2.deptID = e1.deptID
    );

查询结果：

需求 11

查询所有的部门信息，并统计部门的员工数量。

-- 11 查询所有的部门信息，并统计部门的员工数量。(思路同上一题)
--      分析：（1）先查询出一个包含 deptID 和 count(*) 的，按 emp.deptID 分组的临时表 temp
--           （2）在用外连接连接查询 temp 和 dept。
--                  连接条件：temp.id = emp.deptID
select dept.*, temp.num
    from dept
        join (select count(*) as num, emp.deptID as id from emp group by emp.deptID) as temp 
        on dept.id = temp.id;

查询结果：

7 事务

本小节我们会研究一种数据库操作中关于事务的相关概念，包含

本章节单独作为一篇：事务 Transaction，点击跳转。

`MySQL` 进阶知识

在进阶篇章中，我们将分为以下几个部分：

存储引擎
索引
SQL 优化
视图/存储过程/触发器
锁
innoDB 引擎
mySQL 管理

1 存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型。

我们在创建表时，可以指定存储引擎（默认为 innoDB）：

CREATE TABLE 表名(
	字段1 类型1 [COMMENT 注释1],
	字段2 类型2 [COMMENT 注释2],
	...
)ENGINE=INNODB

查看当前数据库支持的存储引擎：

1	SHOW engines;

其中，我们主要讲解 innoDB、myISAM 和 memory 这三个引擎。

1.1 `innoDB` 存储引擎

innoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 mySQL v5.5 版本之后，innoDB 是默认的存储引擎。

特点

DML 操作（增删改）遵循 ACID 模型，支持事务；
支持行级锁，提高并发访问性能；
支持外键约束，保证数据的完整性和正确性
每个表有对应一个 .idb 的表空间文件，用来存储该表的结构、数据和索引。

逻辑存储结构

TableSpace：表空间；
Segment：段；
Extent：区，大小为 1M，一个区包含 64 个页；
Page：页，大小为 16K；
Row：行，对应一条记录。

1.2 `myISAM` 存储引擎

myISAM 是 mySQL 早期默认的存储引擎。

特点

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快
有 3 个文件存储一个表：.sdi 文件存放表的结构信息；.myd 文件存放数据；.myi 文件存放索引

1.3 `memory` 存储引擎

memory 存储引擎的表数据存储在内存中，只能将这种表作为临时表或缓存使用。

特点

存放在内存
hash 索引
.sdi 文件存放表结构

1.4 三种引擎的区别

	`innoDB`	`myISAM`	`memory`
存储限制	`64 TB`	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
`B+ Tree` 索引	支持	支持	支持
`Hash` 索引	-	-	支持
全文索引	支持 (`v5.6` 之后)	支持	-
空间使用	高	低	-
内存使用	高	低	中等
批量插入速度	低	高	高
是否支持外键	支持	-	-
适用场景	对事务的完整性有高要求、高并发条件、除了增查操作外还有大量的改删操作	增查操作为主、不要求高并发性（`MangoDB`）	常用于临时表和缓存、无法保证数据的安全性（`Redis`）

2 索引

索引是帮助 mySQL 高效获取数据的（有序）数据结构。除了数据，DBMS 还需要维护这种指向数据的、满足特定查找算法的数据结构，这样就可以在索引上高效地查询数据。

假设我们将这种数据结构用二叉树实现，使其在 age 字段建立索引，指向数据的地址：（注意 ⚠️，此处只是用一个二叉树举例，并不是真实的索引结构）

1	select * from emp where age=45;

【索引的优缺点】

优点	缺点
提高数据检索的效率，降低数据库的 IO 成本	索引需要额外的存储空间
通过索引对数据进行排序，可以降低数据排序的成本，降低 CPU 的消耗	索引大大提高了查询效率，但是却降低了更新表（`update`、`insert` 和 `delete`）的速度

2.1 索引的结构

mySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

索引结构	描述
`B+ Tree` 索引	最常见的索引结构，大部分引擎都支持
`Hash` 索引	底层通过哈希表实现，不支持范围查询，只支持精确查询
`R-tree`（空间索引）	`myISAM` 的一个特殊索引结构，主要用于地理空间数据类型
`Full-text`（全文索引）	是一种通过建立倒排索引，快速匹配文档的方法

2.2 `B+ Tree` 索引结构

在之前的例子中我们，我们使用了二叉树来充当索引，但是使用二叉树会存在两个问题：

如果我们按照一定顺序插入一组数据，该二叉树会变成一个链表，从而失去了索引的意义；
二叉树的一个节点只能有两个子节点，如果数据量较大，树的层级就会很深，检索依然很慢。

例如我们插入 36,22,48,19,33,45,53,17,20,23,34,40,46,50,70 时，会形成如下的二叉树

如果我们插入 36,34,33,23,22,20,19,17 时，会形成如下的二叉树

我们知道，红黑树是一种自平衡二叉树，可以解决第一个问题，如下图所示。但是依旧不能解决数据量较大的问题。

如果我们不使用二叉树，而采用平衡的 n 叉树来建立索引，就可以解决上述的问题。这个平衡的 n 叉树就称为 B-Tree（多路平衡查找树）。

`B-Tree`（多路平衡查找树）

以一颗最大度树 (Max-Degree) 为 5 的五阶 B-Tree 为例（每个节点最多存储 4 个 key，5 个指针）：

我们可以在 B-Tree Visualization 网站中插入如下数据，可视化模拟在 B-Tree 的插入操作。

1
2
3

B-Tree:
	Max-degree = 5
	insert: 100, 65, 169, 368, 900, 556, 780, 35, 215, 1200, 234, 888, 158, 90, 1000, 88, 120, 268,250

插入数据后的 B-Tree 如下所示：

这里提供一个 B-Tree 的 Java 实现，代码来自 algs4.cs.princeton.edu：

public class BTree<Key extends Comparable<Key>, Value>  {
    // max children per B-tree node = M-1
    // (must be even and greater than 2)
    private static final int M = 4;

    private Node root;       // root of the B-tree
    private int height;      // height of the B-tree
    private int n;           // number of key-value pairs in the B-tree

    // helper B-tree node data type
    private static final class Node {
        private int m;                             // number of children
        private Entry[] children = new Entry[M];   // the array of children

        // create a node with k children
        private Node(int k) {
            m = k;
        }
    }

    // internal nodes: only use key and next
    // external nodes: only use key and value
    private static class Entry {
        private Comparable key;
        private Object val;
        private Node next;     // helper field to iterate over array entries
        public Entry(Comparable key, Object val, Node next) {
            this.key  = key;
            this.val  = val;
            this.next = next;
        }
    }

    /**
     * Initializes an empty B-tree.
     */
    public BTree() {
        root = new Node(0);
    }

    /**
     * Returns true if this symbol table is empty.
     * @return {@code true} if this symbol table is empty; {@code false} otherwise
     */
    public boolean isEmpty() {
        return size() == 0;
    }

    /**
     * Returns the number of key-value pairs in this symbol table.
     * @return the number of key-value pairs in this symbol table
     */
    public int size() {
        return n;
    }

    /**
     * Returns the height of this B-tree (for debugging).
     *
     * @return the height of this B-tree
     */
    public int height() {
        return height;
    }


    /**
     * Returns the value associated with the given key.
     *
     * @param  key the key
     * @return the value associated with the given key if the key is in the symbol table
     *         and {@code null} if the key is not in the symbol table
     * @throws IllegalArgumentException if {@code key} is {@code null}
     */
    public Value get(Key key) {
        if (key == null) throw new IllegalArgumentException("argument to get() is null");
        return search(root, key, height);
    }

    private Value search(Node x, Key key, int ht) {
        Entry[] children = x.children;

        // external node
        if (ht == 0) {
            for (int j = 0; j < x.m; j++) {
                if (eq(key, children[j].key)) return (Value) children[j].val;
            }
        }

        // internal node
        else {
            for (int j = 0; j < x.m; j++) {
                if (j+1 == x.m || less(key, children[j+1].key))
                    return search(children[j].next, key, ht-1);
            }
        }
        return null;
    }


    /**
     * Inserts the key-value pair into the symbol table, overwriting the old value
     * with the new value if the key is already in the symbol table.
     * If the value is {@code null}, this effectively deletes the key from the symbol table.
     *
     * @param  key the key
     * @param  val the value
     * @throws IllegalArgumentException if {@code key} is {@code null}
     */
    public void put(Key key, Value val) {
        if (key == null) throw new IllegalArgumentException("argument key to put() is null");
        Node u = insert(root, key, val, height);
        n++;
        if (u == null) return;

        // need to split root
        Node t = new Node(2);
        t.children[0] = new Entry(root.children[0].key, null, root);
        t.children[1] = new Entry(u.children[0].key, null, u);
        root = t;
        height++;
    }

    private Node insert(Node h, Key key, Value val, int ht) {
        int j;
        Entry t = new Entry(key, val, null);

        // external node
        if (ht == 0) {
            for (j = 0; j < h.m; j++) {
                if (less(key, h.children[j].key)) break;
            }
        }

        // internal node
        else {
            for (j = 0; j < h.m; j++) {
                if ((j+1 == h.m) || less(key, h.children[j+1].key)) {
                    Node u = insert(h.children[j++].next, key, val, ht-1);
                    if (u == null) return null;
                    t.key = u.children[0].key;
                    t.val = null;
                    t.next = u;
                    break;
                }
            }
        }

        for (int i = h.m; i > j; i--)
            h.children[i] = h.children[i-1];
        h.children[j] = t;
        h.m++;
        if (h.m < M) return null;
        else         return split(h);
    }

    // split node in half
    private Node split(Node h) {
        Node t = new Node(M/2);
        h.m = M/2;
        for (int j = 0; j < M/2; j++)
            t.children[j] = h.children[M/2+j];
        return t;
    }

    /**
     * Returns a string representation of this B-tree (for debugging).
     *
     * @return a string representation of this B-tree.
     */
    public String toString() {
        return toString(root, height, "") + "\n";
    }

    private String toString(Node h, int ht, String indent) {
        StringBuilder s = new StringBuilder();
        Entry[] children = h.children;

        if (ht == 0) {
            for (int j = 0; j < h.m; j++) {
                s.append(indent + children[j].key + " " + children[j].val + "\n");
            }
        }
        else {
            for (int j = 0; j < h.m; j++) {
                if (j > 0) s.append(indent + "(" + children[j].key + ")\n");
                s.append(toString(children[j].next, ht-1, indent + "     "));
            }
        }
        return s.toString();
    }


    // comparison functions - make Comparable instead of Key to avoid casts
    private boolean less(Comparable k1, Comparable k2) {
        return k1.compareTo(k2) < 0;
    }

    private boolean eq(Comparable k1, Comparable k2) {
        return k1.compareTo(k2) == 0;
    }
}

/******************************************************************************
 *  Copyright 2002-2022, Robert Sedgewick and Kevin Wayne.
 *
 *  This file is part of algs4.jar, which accompanies the textbook
 *
 *      Algorithms, 4th edition by Robert Sedgewick and Kevin Wayne,
 *      Addison-Wesley Professional, 2011, ISBN 0-321-57351-X.
 *      http://algs4.cs.princeton.edu
 *
 *
 *  algs4.jar is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  algs4.jar is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with algs4.jar.  If not, see http://www.gnu.org/licenses.
 ******************************************************************************/

@Test
public void testBTree() {
       BTree<String, String> st = new BTree<String, String>();

       st.put("www.cs.princeton.edu", "128.112.136.12");
       st.put("www.cs.princeton.edu", "128.112.136.11");
       st.put("www.princeton.edu",    "128.112.128.15");
       st.put("www.yale.edu",         "130.132.143.21");
       st.put("www.simpsons.com",     "209.052.165.60");
       st.put("www.apple.com",        "17.112.152.32");
       st.put("www.amazon.com",       "207.171.182.16");
       st.put("www.ebay.com",         "66.135.192.87");
       st.put("www.cnn.com",          "64.236.16.20");
       st.put("www.google.com",       "216.239.41.99");
       st.put("www.nytimes.com",      "199.239.136.200");
       st.put("www.microsoft.com",    "207.126.99.140");
       st.put("www.dell.com",         "143.166.224.230");
       st.put("www.slashdot.org",     "66.35.250.151");
       st.put("www.espn.com",         "199.181.135.201");
       st.put("www.weather.com",      "63.111.66.11");
       st.put("www.yahoo.com",        "216.109.118.65");


       StdOut.println("cs.princeton.edu:  " + st.get("www.cs.princeton.edu"));
       StdOut.println("hardvardsucks.com: " + st.get("www.harvardsucks.com"));
       StdOut.println("simpsons.com:      " + st.get("www.simpsons.com"));
       StdOut.println("apple.com:         " + st.get("www.apple.com"));
       StdOut.println("ebay.com:          " + st.get("www.ebay.com"));
       StdOut.println("dell.com:          " + st.get("www.dell.com"));
       StdOut.println();

       StdOut.println("size:    " + st.size());
       StdOut.println("height:  " + st.height());
       StdOut.println(st);
       StdOut.println();
   }

经典 `B+ Tree` 结构

前面我们了解了 B-Tree 的索引结构。我们发现，在 B-Tree 中数据和索引是糅合在一起的，即数据部分既在叶子节点又在根节点。所以我们在 B-Tree 的基础上改良得到 B+ Tree。即将所有的元素及其数据都存放在叶子结点上，根节点元素只用作索引，然后将所有的叶子节点使用链表连接。如下图：

同样的，我们可以在 B+ Tree Visualization 网站中插入如下数据，可视化模拟在 B+ Tree 的插入操作。

1
2
3

B-Tree:
	Max-degree = 5
	insert: 100, 65, 169, 368, 900, 556, 780, 35, 215, 1200, 234, 888, 158, 90, 1000, 88, 120, 268,250

`mySQL` 的 `B+ Tree` 索引结构

mySQL 索引结果对经典的 B+ Tree 进行了优化：在原来的基础上，增加了一个执行相邻叶子结点的指针，就形成了带有顺序的 B+ Tree，以提高区间访问及排序能力。如下图

2.3 `Hash` 索引

Hash 索引就是采用一定的 Hash 算法，将需要索引的字段值换算成新的 Hash 值，映射到对应 Hash 值的槽位上，然后存储在 Hash 表中。
如果多个都映射到一个相同的槽位上，他们之间就发生了 Hash 冲突，可以通过链表来解决。

【特点】

Hash 索引只能用于等值比较 (=, in)，不支持范围查询 (between, >, <)
无法利用索引完成排序操作
查询效率高，在没有 Hash 冲突的情况下只需要一次索引

2.4 索引的分类

分类	含义	特点	关键字
主键索引	针对于表中的主键创建的索引	默认自动创建，唯一	`PRIMARY`
唯一索引	避免同一个表中某字段的值重复	可有多个	`UNIQUE`
常规索引	快速定位数据	可有多个
全文索引	查找文本中的关键字，而不是索引中的值	可有多个	`FULLTEXT`

在 innoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚簇索引 (`Clutered Index`)	将数据与索引存放在一起，索引的叶子节点保存了行数据	必须有，且只能有一个
非聚簇索引 (`Secondary Index`)	将数据与索引分开存放，索引结构的叶子结点关联的是对应行的主键	可以存在多个

【聚簇索引的选取规则】

如果有主键，主键索引就是聚簇索引
如果没有主键，将使用第一个非空索引作为聚簇索引
如果以上两者都不满足，innoDB 会自动生成一个 rowid 作为隐藏的聚簇索引。

聚簇索引与非聚簇索引的区别

聚簇索引：在 B+ Tree 的叶子结点上保存的数据是行数据

非聚簇索引，又叫二级索引。顾名思义，二级索引就是需要索引两次才能找到对应的行数据 row。即，在非聚簇索引中， B+ Tree 的叶子结点上保存的数据是主键 / 第一个非空索引 / 自动生成的rowid（会表查询）。

例如：

1	select * from user where name='Amy';

在如上的 SQL 语句中，我们需要

（1）先通过非聚簇索引查找 name = 'Amy'，得到其对应的 id

（2）再通过聚簇索引查找 id = 3，得到相应的行数据。

MySQL 基础知识

1 数据库相关概念

2 MySQL 数据库的数据模型

3 SQL 语句

3.1 SQL 的通用语法

3.2 SQL 的分类

3.3 DDL 数据定义语句

数据库相关操作

表相关操作

数据类型

3.4 DML 数据操作语言

INSERT 插入语句

UPDATE 修改语句

DELETE 删除语句

3.5 DQL 数据查询语言（单表）

常见的聚合函数

SELECT 查询语句

WHERE 条件查询

GROUP BY 分组查询

ORDER BY 排序查询

LIMIT 分页查询

DQL 的执行顺序

3.6 DCL 数据控制语言

管理用户

管理权限

4 函数

4.1 字符串函数

4.2 数值函数

4.3 日期函数

4.4 流程控制函数

5 约束

5.1 外键约束

添加外键约束

删除外键约束

更新/删除策略

6 多表查询

6.1 多表关系

一对多（多对一）关系

多对多关系

一对一关系

6.2 多表查询概述

连接查询

【内连接】

【外连接】

【自连接】

联合查询

嵌套查询

【标量嵌套查询】

【列嵌套查询】

【行嵌套查询】

【表嵌套查询】

6.3 练习

需求 1

需求 2

需求 3

需求 4

需求 5

需求 6

需求 7

需求 8

需求 9

需求 10 *

需求 11

7 事务

MySQL 进阶知识

1 存储引擎

1.1 innoDB 存储引擎

特点

逻辑存储结构

1.2 myISAM 存储引擎

特点

1.3 memory 存储引擎

特点

1.4 三种引擎的区别

2 索引

2.1 索引的结构

2.2 B+ Tree 索引结构

B-Tree（多路平衡查找树）

经典 B+ Tree 结构

mySQL 的 B+ Tree 索引结构

`MySQL` 基础知识

2 `MySQL` 数据库的数据模型

3.1 `SQL` 的通用语法

3.2 `SQL` 的分类

3.3 `DDL` 数据定义语句

3.4 `DML` 数据操作语言

`INSERT` 插入语句

`UPDATE` 修改语句

`DELETE` 删除语句

3.5 `DQL` 数据查询语言（单表）

`SELECT` 查询语句

`WHERE` 条件查询

`GROUP BY` 分组查询

`ORDER BY` 排序查询

`LIMIT` 分页查询

`DQL` 的执行顺序

3.6 `DCL` 数据控制语言

`MySQL` 进阶知识

1.1 `innoDB` 存储引擎

1.2 `myISAM` 存储引擎

1.3 `memory` 存储引擎

2.2 `B+ Tree` 索引结构

`B-Tree`（多路平衡查找树）

经典 `B+ Tree` 结构

`mySQL` 的 `B+ Tree` 索引结构

2.3 `Hash` 索引