数据库

数据库：DataBase(DB)，是存储和管理数据的仓库

数据库管理系统：DataBase Management System(DBMS)，操纵和管理数据库的大型软件

SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

MySQL

安装

参考即可05-Web后端基础(数据库) - 飞书云文档

MySQL数据模型

关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库
特点：
- 使用表存储数据，格式统一，便于维护
- 使用SQL语言操作，标准统一，使用方便，可用于复杂查询

创建数据库命令：

1	create database xxxx;

SQL语句

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准
分类：

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

建议将SQL语句中的关键字以全大写表示

DDL

库操作

--查询所有数据库(注意复数形式)
show databases;

--查询当前数据库(注意括号)
select database();

--使用/切换数据库
use 数据库名;

--创建数据库(括号内表示可选属性，创建数据库默认字符集就是utf8mb4)
create database [if not exists] 数据库名 [default charset utf8mb4];

--删除数据库
drop database [if exists] 数据库名;

上述语法中的database，也可以替换成schema。如：create schema db01;

MySQL8版本中，默认字符集为utf8mb4

表操作

创建表的语法：
create table tablename(
	字段1 字段类型 [约束] [comment 字段1注释],
	......
	字段2 字段类型 [约束] [comment 字段2注释]
)[comment 表注释];

字段类型

数值类型

数据类型	描述
`TINYINT`	非常小的整数（-128 到 127）
`SMALLINT`	小整数（-32,768 到 32,767）
`MEDIUMINT`	中等大小的整数（-8,388,608 到 8,388,607）
`INT`	普通整数（-2,147,483,648 到 2,147,483,647）
`BIGINT`	大整数（-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807）
`FLOAT`	单精度浮点数
`DOUBLE`	双精度浮点数
`DECIMAL`	固定小数点数

字符串类型

数据类型	描述
`CHAR`	定长字符串
`VARCHAR`	变长字符串
`TINYTEXT`	非常小的文本
`TEXT`	小文本
`MEDIUMTEXT`	中等大小的文本
`LONGTEXT`	大文本
`BINARY`	定长二进制数据
`VARBINARY`	变长二进制数据
`TINYBLOB`	非常小的BLOB数据
`BLOB`	小BLOB数据
`MEDIUMBLOB`	中等大小的BLOB数据
`LONGBLOB`	大BLOB数据
`ENUM`	枚举类型
`SET`	集合类型
`JSON`	JSON类型

Blob类型主要用于存储二进制数据，比如图片、音频、非纯文本文件等。实际开发中，推荐存储文件的本地路径而不是直接存储数据

枚举类型（ENUM）是一种特殊的数据类型，用于限制列的取值范围。枚举值在创建时指定：gender ENUM('MALE', 'FEMALE', 'OTHER')

SET类型允许在一个列中存储多个预定义值的组合，类似于多选枚举。创建方式：hobbies SET('reading', 'music', 'sports', 'travel', 'cooking')

JSON类型允许在数据库中直接存储JSON格式的数据，提供了对JSON数据的存储、查询和操作能力

日期和时间类型

数据类型	描述
`DATE`	日期值（YYYY-MM-DD）
`TIME`	时间值（HH:MM:SS）
`DATETIME`	日期和时间值（YYYY-MM-DD HH:MM:SS）
`TIMESTAMP`	时间戳（YYYY-MM-DD HH:MM:SS），存储从1970年1月1日以来的秒数
`YEAR`	年份值（YYYY）

数据库为了统一时间存储格式，是无法在创建时自定义时间格式的

只有在查询时，才可以根据对应的时间格式化方法将数据进行格式输出

空间数据类型

数据类型	描述
`GEOMETRY`	几何数据的通用类型
`POINT`	一个点
`LINESTRING`	一条线
`POLYGON`	多边形
`MULTIPOINT`	多个点
`MULTILINESTRING`	多条线
`MULTIPOLYGON`	多个多边形
`GEOMETRYCOLLECTION`	几何数据集合

空间数据类型（Spatial Data Types）是用于存储和处理地理空间数据的数据类型，主要用于处理地理位置、地图、几何图形等相关信息。

约束

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

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

一个字段可以添加多个约束，只需要用空格分开即可

综合示例

create table users(
    id int primary key comment "用户ID",
    name varchar(10) not null unique comment "用户名称",
    score double comment "用户积分",
    age int comment "用户年龄",
    gender tinyint comment "用户性别，1为男性，2为女性...",
    description TEXT comment "用户描述",
    birthday DATE comment "用户生日",
    city varchar(20) comment "用户所在城市",
    create_time DATETIME comment "创建时间",
    update_time DATETIME comment "更新时间"
)

建议表的设计中存在基础字段（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 column 字段名;	-- 删除字段
alter table 表名 rename to 新表名;	-- 修改表名

drop table [if exists] 表名;	-- 删除表

show表示查询（desc表示描述）

alter表示修改

drop表示删除（删除表时，数据也会被删除，谨慎操作！）

DML

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

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

INSERT

-- 指定字段添加数据
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

1 2	-- 修改数据 update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , ... [where 条件];

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

DELETE

1 2	-- 删除数据 delete from 表名 [where 条件];

注意：

DELETE 语句的条件可以有，也可以没有，如果没有条件，则删除整张表的所有数据
DELETE 语句不能删除某一个字段的值（如果要操作，可以使用update，将该字段值设为NULL）

DQL

DQL英文全称是Data Query Language（数据查询语言），用来查询数据库表中的记录
关键字：Select

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

基本查询：select ... from ...
条件查询：where
分组查询：group by
排序查询：order by
分页查询：limit

基本查询

-- 查询多个字段
select 字段1,字段2,字段3 from 表名;

-- 查询所有字段(通配符)
select * from 表名;

-- 为查询字段设置别名，as关键字可以省略
select 字段1 [as 别名1], 字段2 [as 别名2] from 表名;
select 字段1 别名1,字段2 别名2 from 表名;

-- 去除重复记录
select distinct 字段列表 from 表名;

示例：

--  =================== DQL: 基本查询 ======================
-- 1. 查询指定字段 name,entry_date 并返回
SELECT name,entry_date FROM emp;

-- 2. 查询返回所有字段
SELECT * FROM emp;


-- 3. 查询所有员工的 name,entry_date, 并起别名(姓名、入职日期)
SELECT name as "姓名",entry_date as "入职日期" from emp;


-- 4. 查询已有的员工关联了哪几种职位(不要重复)
SELECT DISTINCT job from emp;

SELECT * FROM这种使用通配符的方式查询所有字段，并不推荐（效率低）

最好的方式就是将所有的字段罗列出来

条件查询

1 2	-- 条件查询 select 字段列表 from 表名 where 条件列表;

条件列表中可以使用比较运算符和逻辑运算符：

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

示例：

--  =================== DQL: 条件查询 ======================
-- 1. 查询 姓名 为 柴进 的员工
SELECT * FROM emp where name = '柴进';

-- 2. 查询 薪资小于等于5000 的员工信息
SELECT * FROM emp WHERE salary <= 5000;

-- 3. 查询 没有分配职位 的员工信息
SELECT * FROM emp WHERE job IS NULL;


-- 4. 查询 有职位 的员工信息
SELECT * FROM emp WHERE job IS NOT NULL;


-- 5. 查询 密码不等于 '123456' 的员工信息
SELECT * FROM emp WHERE password != '123456';


-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
SELECT * FROM emp WHERE entry_date BETWEEN '2000-01-01' AND '2010-01-01';

-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
SELECT * FROM emp WHERE entry_date BETWEEN '2000-01-01' AND '2010-01-01' AND gender = 2;

-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
SELECT * FROM emp WHERE job IN (2,3,4);


-- 9. 查询 姓名 为两个字的员工信息
SELECT * FROM emp WHERE CHAR_LENGTH(name) = 2;


-- 10. 查询 姓 '李' 的员工信息
SELECT * FROM emp WHERE name LIKE '李%';


-- 11. 查询 姓名中包含 '二' 的员工信息
SELECT * FROM emp WHERE name LIKE '%二%';

注意：第九题中获取字符串长度应该是CHAR_LENGTH()，这个方法返回的结果是字符长度。而LENGTH()该方法返回的是字节长度！

注意：第九题也可以用是SELECT * FROM emp WHERE name = '__';结果是一样的，但是意义比较模糊，不太推荐

分组查询

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

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

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

示例：

--  =================== DQL: 分组查询 ======================
-- 聚合函数
-- 1. 统计该企业员工数量
SELECT COUNT(id) as '员工数量' FROM emp;

-- 2. 统计该企业员工的平均薪资
SELECT AVG(salary) as '平均薪资' FROM emp;

-- 3. 统计该企业员工的最低薪资
SELECT MIN(salary) as '最低薪资' FROM emp;

-- 4. 统计该企业员工的最高薪资
SELECT MAX(salary) as '最高薪资' FROM emp;

-- 5. 统计该企业每月要给员工发放的薪资总额(薪资之和)
SELECT SUM(salary) as '总薪资' FROM emp;




-- 分组
-- 1. 根据性别分组 , 统计男性和女性员工的数量
SELECT gender,COUNT(gender) FROM emp GROUP BY gender;

-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
SELECT job,SUM(job) FROM emp WHERE entry_date >= '2015-01-01' GROUP BY job HAVING SUM(job) >= 2;

WHERE和HAVING的区别：

执行时机不同：WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组；HAVING是分组之后对结果进行过滤

判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以

排序查询

1	select 字段列表 from 表名 [where 条件列表] [group by 分组字段名 having 分组后过滤条件] order by 排序字段排序方式

排序方式：升序（asc），降序（desc）；默认为升序asc，也就是可以不写采用默认方式

示例：

--  =================== 排序查询 ======================
-- 1. 根据入职时间, 对员工进行升序排序
SELECT * FROM emp ORDER BY entry_date ASC;

-- 2. 根据入职时间, 对员工进行降序排序
SELECT * FROM emp ORDER BY entry_date DESC;

-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
SELECT * FROM emp ORDER BY entry_date ASC, update_time DESC;

如果存在多个排序条件，可以通过逗号进行分割。当第一个字段相同时，才会进行后一个字段排序

分页查询

1	select 字段 from 表名 [where 条件] [group by 分组字段 having 过滤条件] [order by 排序字段] limit 起始索引,查询记录数;

示例：

--  =================== 分页查询 ======================
-- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
SELECT * FROM emp LIMIT 0,5;

-- 2. 查询 第1页 员工数据, 每页展示5条记录
SELECT * FROM emp LIMIT 0,5;

-- 3. 查询 第2页 员工数据, 每页展示5条记录
SELECT * FROM emp LIMIT 5,5;

-- 4. 查询 第3页 员工数据, 每页展示5条记录
SELECT * FROM emp LIMIT 10,5;

可以得出规律：起始索引 = 每页展示数量 * （页码 - 1）;

JDBC

JDBC（Java DateBase Connectivty），就是使用Java语言操作关系型数据库的一套API
本质：
- Sun公司官方定义的一套操作所有关系型数据库的规范，即接口
- 各个数据库厂商去实现这套接口，提供数据库驱动jar包
- 我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类

入门程序

需求：基于JDBC，执行UPDATE语句（UPDATE user SET age = 25 WHERE id = 1）
步骤：
- 准备工作：创建Maven项目，引入依赖；准备数据库表user
- 代码实现：编写JDBC程序，操作数据库

<dependency>
	<groupId>com.mysql</groupId>
	<artifactId>mysql-connector-j</artifactId>
	<version>8.0.33</version>
</dependency>

示例：

// 1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");

// 2. 获取连接
String url = "jdbc:mysql://localhost:3306/web01";
String username = "root";
String password = "1234";
Connection connection = DriverManager.getConnection(url,username,password);

// 3. 获取SQL语句执行对象
Statement statement = connection.createStatement();

// 4. 执行SQL
int i = statement.executeUpdate("update user set age = 25 where id = 1");

// 5. 释放资源
statement.close();
connection.close();

基本的流程：注册驱动 => 建立连接 => 获取SQL执行对象 => 执行SQL语句 => 释放资源

查询数据

需求：基于JDBC执行如下select语句，将查询结果封装到User对象中
SQL：SELECT * FROM user WHERE username = ‘daqiao’ AND password = ‘123456’

示例：

@Test
public void testSelect() throws ClassNotFoundException, SQLException {
    // 1. 注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    
    // 2. 获取连接
    String url = "jdbc:mysql://localhost:3306/web01";
    String username = "root";
    String password = "1234";
    Connection connection = DriverManager.getConnection(url,username,password);
    
    // 3. 获取SQL语句执行对象
    String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1,"daqiao");
    statement.setString(2,"123456");
    
    // 4. 执行SQL
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String uname = resultSet.getString("username");
        String passwd = resultSet.getString("password");
        String name = resultSet.getString("name");
        int age = resultSet.getInt("age");
        User user = new User(id,uname,passwd,name,age);
        System.out.println(user);
        // 输出结果：User{id=1, username='daqiao', password='123456', name='大乔', age=25}
    }
    
    // 5. 释放资源
    statement.close();
    connection.close();
}

sql语句中的?表示占位符，可以动态的变更查询条件。此时statement变为prepareStatement，通过setString(第n个占位符，替换内容)替换占位符

ResultSet可以把它看作是一个迭代器，通过迭代器中的方法获取查询返回的数据

预编译SQL

静态SQL：

1 2	Statement statement = connection.createStatement(); int i = statement.executeUpdate("UPDATE user SET age = 25 WHERE id = 1");

预编译SQL：

PreparedStatement statement = connection.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?")
statement.setString(1,"daqiao");
statement.setString(2,"123456");
ResultSet resultSet = statement.executeQuery();

优势一：可以防止SQL注入，更安全
- SQL注入：通过控制输入来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法
优势二：性能更高

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求以及业务模块之间的关系，分析并设计表结构。由于业务之间相互关联，所以各个表结构之间也存在着各种联系
多表关系分为三种：
1. 一对多（多对一）
2. 一对一
3. 多对多

一对多（多对一）

场景：部门与员工的关系（一个部门下有多个员工）

一对多关系的实现：在数据表中多的一方，添加字段，来关联一的一方的主键

外键约束

在一对多的案例中，虽然逻辑上员工表关联了部门表，但是假设部门表中某个部门删除了，员工表中关联的字段（dept_id）是不会被修改的（脏数据）
为了解决这个问题，可以引入外键约束，使得在“物理”方面进行强关联操作，保证数据的一致性、完整性和正确性
外键约束可以在创建表时添加，也可以在创建表后进行添加

-- 创建表时指定
create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);

1 2	-- 建完表后，添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名);

可以通过图形化界面更快捷的添加外键约束（右键表名后点击modify Table）：

物理外键存在许多缺点：

影响增、删、改的效率（需要检查外键关系）
仅用于单节点数据库，不适用于分布式、集群场景
容易引发数据库的死锁问题，消耗性能

真实的项目开发中，尽量避免物理外键的使用，可以用**逻辑外键**替代（相当于将表关系交给开发人处理而不是仅仅通过数据库处理表关系）

一对一

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

多对多

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

tb_student：

id	name	no
1	张三	200100101
2	李四	200100102
3	王五	200100103
4	赵六	200100104

tb_course：

id	name
1	Java
2	PHP
3	MySQL
4	Hadoop

tb_student_course：

id	studentid	courseid
1	1	1
2	1	2
3	1	3
4	2	1
5	2	4

多表查询

多表查询：指从多张表中查询数据

在多表查询前，先准备emp表和dept表：

emp：

id	username	password	name	gender	phone	job	salary	image	entry_date	dept_id	create_time	update_time
1	shinaian	123456	施耐庵	男 (1)	13309090001	4	15000	http://……	2000-01-01	2	2024-10-27 16:35:33	2024-10-27 16:35:35
2	songjiang	123456	宋江	男 (1)	13309090002	2	8600	http://……	2015-01-01	2	2024-10-27 16:35:33	2024-10-27 16:35:37
3	lujunyi	123456	卢俊义	男 (1)	13309090003	2	8900	http://……	2008-05-01	2	2024-10-27 16:35:33	2024-10-27 16:35:39
4	wuyong	123456	吴用	男 (1)	13309090004	2	9200	http://……	2007-01-01	2	2024-10-27 16:35:33	2024-10-27 16:35:41
5	gongsunsheng	123456	公孙胜	男 (1)	13309090005	2	9500	http://……	2012-12-05	2	2024-10-27 16:35:33	2024-10-27 16:35:43
6	huosanniang	123456	扈三娘	女 (2)	13309090006	3	6500	http://……	2013-09-05	1	2024-10-27 16:35:33	2024-10-27 16:35:45

dept：

id	name	create_time	update_time
1	学工部	current_timestamp	current_timestamp
2	教研部	current_timestamp	current_timestamp
3	咨询部	current_timestamp	current_timestamp
4	就业部	current_timestamp	current_timestamp
5	人事部	current_timestamp	current_timestamp

查询语句：

1	SELECT * FROM emp,dept;

结果返回的数据中，每个员工有五条数据，且只有一条是有效的。这是因为每个员工数据都要拼接上所有的部门数据（笛卡尔积现象）

笛卡尔积：数学概念，指定是两个集合的所有组合情况

消除无效数据的方法也很简单，根据返回的结果，可以看出只需要将emp.dept_id匹配上dept.id即可：

1	SELECT * FROM emp,dept WHERE emp.dept_id = dept_id;

连接查询

内连接：相当于查询A、B交集部分数据
外连接：
- 左外连接：查询左表所有数据（包括两张表交集部分数据）
- 右外连接：查询右表所有数据（包括两张表交集部分数据）

内连接

内连接查询的是两张表交集部分的数据。具体语法为：

-- 1. 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHRER 连接条件 ...;

-- 2. 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

示例：

-- ============================= 内连接 ==========================
-- A. 查询所有员工的ID, 姓名 , 及所属的部门名称 (隐式、显式内连接实现)
SELECT * FROM emp,dept WHERE emp.dept_id = dept_id;

SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

-- B. 查询 性别为男, 且工资 高于8000 的员工的ID, 姓名, 及所属的部门名称 (隐式、显式内连接实现)
SELECT emp.id,emp.name,dept.name FROM emp,dept WHERE emp.gender = 1 AND salary > 8000 AND emp.dept_id = dept.id;

SELECT emp.id,emp.name,dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id WHERE emp.gender = 1 AND emp.salary > 8000;

虽然可以将所有条件语句都写在ON后，但是建议ON后的条件只用于多表的连接

将数据筛选的条件写在WHRER后面，这样SQL语句表述更清晰

另外，可以将表名起别名：emp [as] e, dept [as] d，起了别名后，调用表内的字段数据只能用别名调用：e.gender = 1

外连接

外连接分为左外连接和后外连接。具体语法为：

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

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

示例：

-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
SELECT emp.name,dept.name FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id;

-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
SELECT dept.name,emp.name FROM emp RIGHT JOIN dept ON dept.id = emp.dept_id;

-- C. 查询工资 高于8000 的 所有员工的姓名, 和对应的部门名称 (左外连接)
SELECT emp.name,dept.name FROM emp LEFT JOIN dept ON emp.dept_id = dept.id WHERE salary > 8000;

如果员工表中存在dept_id=null的情况，仅仅使用内连接查询是无法获取全部员工信息的

但是外连接可以解决上面的问题，因为外连接不仅查询表之间的交集，还有表的自身所有数据

对于左右连接的选择，根据的是**需要获取哪个表的全部数据**来判断左右的

子查询

介绍：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
形式：SELECT * FROM t1 WHRER column1 = (SELECT column1 FROM t2 ...);
说明：子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任意一个，最常见的是SELECT
分类：
1. 标量子查询：子查询返回的结果为单个值
2. 列子查询：子查询返回的结果为一列
3. 行子查询：子查询返回的结果为一行
4. 表子查询：子查询返回的结果为多行多列
要点：先对需求做拆分，明确具体的步骤，然后再逐步编写SQL语句

示例：

-- ========================= 子查询 ================================
-- 标量子查询
-- A. 查询 最早入职 的员工信息
-- 1. 先获取“最小”的入职时间
SELECT MIN(entry_date) FROM emp;
-- 2. 根据获取到的“最小”入职时间，查找出对应的员工信息
SELECT * FROM emp WHERE entry_date = (SELECT MIN(entry_date) FROM emp);

-- B. 查询在 "阮小五" 入职之后入职的员工信息
-- 1. 先获取“阮小五”的入职时间
SELECT entry_date FROM emp WHERE name = '阮小五';
-- 2. 判断哪些员工入职时间大于获取到的入职时间
SELECT * FROM emp WHERE entry_date > (SELECT entry_date FROM emp WHERE name = '阮小五');

-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
-- 1. 先获取到“教研部”和“咨询部”的id
SELECT id FROM dept WHERE dept.name IN ('教研部','咨询部');
-- 2. 因为emp.dept_id对应的就是dept.id，且有可能返回多个值，所以用"IN"代替"="进行条件筛选
SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE dept.name IN ('教研部','咨询部'));


-- 行子查询
-- A. 查询与 "李忠" 的薪资 及 职位都相同的员工信息 ;
SELECT salary,job FROM emp WHERE name = '李忠';
SELECT * FROM emp WHERE (salary,job) = (SELECT salary,job FROM emp WHERE name = '李忠');

-- 表子查询
-- A. 获取每个部门中薪资最高的员工信息
SELECT dept_id,MAX(salary) FROM emp GROUP BY dept_id;
SELECT * FROM emp WHERE (dept_id,salary) IN (SELECT dept_id,MAX(salary) FROM emp GROUP BY dept_id);

在行子查询和表子查询中，对字段名用括号包裹，这是用于接收到子查询中返回的结果

它的匹配机制是**根据位置来决定的**，而不是根据字段名进行匹配

综合案例

-- 需求:
-- 1. 查询 "教研部" 性别为 男，且在 "2011-05-01" 之后入职的员工信息 。
SELECT * FROM emp LEFT JOIN dept ON emp.dept_id = dept.id WHERE emp.gender = 1 AND entry_date > '2011-05-01' AND dept.name = '教研部';

-- 2. 查询工资 低于公司平均工资的 且 性别为男 的员工信息 。
SELECT * FROM emp WHERE gender = 1 AND salary < (SELECT AVG(salary) FROM emp);


-- 3. 查询部门人数超过 10 人的部门名称 。
SELECT dept.name,COUNT(dept_id) FROM emp LEFT JOIN dept on emp.dept_id = dept.id GROUP BY dept_id HAVING COUNT(dept_id) > 10;

-- 4. 查询在 "2010-05-01" 后入职，且薪资高于 10000 的 "教研部" 员工信息，并根据薪资倒序排序。
SELECT * FROM emp e LEFT JOIN dept d on d.id = e.dept_id WHERE d.name = '教研部' AND e.salary > 10000 ORDER BY salary desc;

-- 5. 查询工资 低于本部门平均工资的员工信息 。
SELECT AVG(salary) avg,dept_id FROM emp Group By dept_id;

SELECT * FROM emp e1,(SELECT AVG(salary) avg,dept_id FROM emp Group By dept_id) e2 WHERE e1.dept_id = e2.dept_id AND e1.salary < avg;