Hi, guys！yeah，it's me again！
1. 表关系的概述
1. 1 表关系的概念
- 现实生活中， 实体与实体之间肯定是有关系滴， 比如部门和员工、老师和学生等， 我们在设计表的时候， 就应该体现出表与表之间的这种关系
| 表关系 | 案例 |
|---|---|
| 一对多 | 最常用的关系， 部门和员工 |
| 多对多 | 学生选课表 和 学生表， |
| 一对一 | 相对使用比较少， 员工表， 简历表等 |
- 一对多建表原则： 在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键
- 多对多建表原则： 需要创建第三张表， 中间表中至少两个字段， 这两个字段分别作为外键指向各自一方的主键
＞４．一对一： 在实际的开发中应用不多， 因为一对一可以创建成一张表
原则①: 外键唯一： 主表的主键和从表的外键（唯一）， 形成主外键关系， 外键唯一UNIQUE
原则②: 外键是主键： 主表的主键和从表的主键， 形成主外键关系
2. 数据库设计
2.1 数据规范化
- 什么是范式：
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响， 建立科学的， 规范的数据库就要满足一些规则来优化数据的设计和存储， 这些规则就称为范式
- 三大范式
目前数据库有六种范式： 第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第五范式（5NF， 又称为完美范式）； 满足最低要求的范式是第一范式（1NF）， 在第一范式的基础上进一步满足更多规范要求的成为第二范式， 其余范式以此类推， 一般说来， 数据库只需要满足第三范式就行了。
- 1NF： 数据库表的每一列都是不可分割的原子数据项， 不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分成不同的列， 简而言之， 第一范式每一列不可再拆分，称为原子性
- 2NF： 在满足第一范式的前提下，表中的每一个字段都完全依赖于主键，第二范式的特点： ①： 一张表只描述一件事情； ②： 表中的每一列都完全依赖于主键。
- 3NF： 不产生传递依赖， 表中每一列都直接依赖与主键， 而不是通过其他列间接依赖与主键。
3. 数据准备
# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
# 向dept部门表中增加三条数据
INSERT INTO dept(NAME) VALUES('研发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), # 性别
salary DOUBLE, # 工资
join_date DATE, # 入职日期
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES dept(id) # 外键，关联部门表(部门表的主键)
);
# 插入数据
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES
('张三','男','7200','2013-02-24', 1),
('李四','男','3600','2010-12-02', 2),
('王五','男','9000','2008-08-08', 2),
('丽娜','女','5000','2015-10-07', 3),
('萌萌','女','4500','2011-03-14', 1);
3.1 多表查询的分类
- 内连接： 1- 隐式内连接， 2- 显示内连接
- 外连接： 1- 左外连接， 2- 右外连接
3.2 笛卡尔积
# 查询所有的员工和所有的部门
SELECT * FROM emp, dept;
- 你会发现上面的sql语句查询以后， 显示的内容， 例如： 张三每个部门都有一个， 这种情况就是笛卡尔积
- 如何解决呢？
- 我们在创建emp， dept表的时候设置了主键， 这时候就排上用场了， 当员工表.dept.id=部门表.id的数据才是我们想要的，
# 第一步： 设置过滤条件
SELECT *
FROM emp, dept
WHERE emp.dept_id = dept.id;
# 第二步： 查询员工和部门的名字
SELECT emp.name, dept.name
FROM emp, dept
WHERE emp.dept_id = dept.id;
3.3 内链接
用左边表的记录去匹配右边表的记录， 如果符合条件的则显示
- 隐式内连接： 看不到JOIN关键字，条件使用WHERE指定：
格式： SELECT 字段名 FROM 左表，右表 WHERE 条件；- 显示内连接: 使用INNER JOIN...ON语句， 可以省略INNER
格式：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件；
# 查询张三的所有信息，包括所在的部门名称
# 第一步： 先确定要使用的表
SELECT * FROM emp JOIN dept;
# 第二步： 确定表连接条件， emp.dept_id = dept.id的数据才是有效的
SELECT * FROM emp e JOIN dept d ON e.dept_id = d.id;
# 第三步：最终确定查询张三的信息
SELECT * FROM emp e JOIN dept d ON e.dept_id = d.id WHERE e.name='张三';
# 第四步（非必须）：中国文化博大精深
SELECT e.id 员工编号, e.name 员工姓名, e.gender 性别, e.salary 薪水, e.join_date 入职时间, e.dept_id 部门编号, d.name 部门名称
FROM emp e JOIN dept d ON e.dept_id = d.id
WHERE e.name='张三';
总结内连接查询步骤： 1. 确定查询哪些表； 2. 确定表连接的条件； 3. 确定查询的条件； 4. 确定查询的字段。
3.4 外连接
- 左外连接：使用LEFT [OUTER] JOIN...ON, OUTER可以省略
格式： SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件；
用左表的记录去匹配右边表的记录， 如果符合条件的则显示：否则， 显示NULL，
# 测试左外连接： 插入一个新的部门[目前销售部没有员工]
INSERT INTO dept(NAME) VALUES ('销售部');
SELECT * FROM dept;
# 正式测试左外连接(比较下面的代码显示效果有何不同)
SELECT * FROM emp e LEFT JOIN dept d ON d.id = e.dept_id;
SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id ;
２.左外连接：使用RIGHT[OUTER] JOIN...ON, OUTER可以省略
格式： SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件；
用右表的记录去匹配左表的记录， 如果符合条件的则显示：否则， 显示NULL，
# 测试右外链接: 插入一个新的员工, 不给他分配部门
INSERT INTO emp VALUES (NULL, '赵六', '男', 6666, '2013-11-05', NULL);
SELECT * FROM emp;
# 正式测试右外连接(比较下面的代码显示有何不同)
SELECT * FROM emp e RIGHT JOIN dept d ON d.id = e.dept_id;
SELECT * FROM dept d RIGHT JOIN emp e ON e.dept_id = d.id ;
4. 子查询
- 概念: ①一个查询的结果作为另外一个查询的条件;②有查询的嵌套, 内部的查询成为子查询; ③子查询要使用括号
| 子查询的结果 | 操作形式 |
|---|---|
| 单行单列 | 结果肯定在WHERE后面最为条件, 父查询使用: 比较运算符进行查询 |
| 多行单列 | 结果集类似于一个数组, 父查询使用IN运算符 |
| 多行多列 | 结果放在FROM后面作为表 |
# 单行单列测试案例
# 格式: SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
# 查询所有员工中谁的工资最高
# 第一步: 找到最高的工资
SELECT MAX(salary) FROM emp;
# 第二步: 将第一步的结果作为WHERE的条件
SELECT NAME, salary FROM emp
WHERE salary=(SELECT MAX(salary) FROM emp);
# 多行单列测试案例
# 格式: SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
# 查询工资低于7000的员工来自于哪些部门
# 第一步; 找到工资低于7000的员工所在部门编号
SELECT NAME, dept_id
FROM emp
WHERE salary < 7000;
# 第二步: 第一步的结果作为条件进行查询
SELECT NAME
FROM dept
WHERE id IN (SELECT dept_id FROM emp WHERE salary < 7000);
# 多行多列测试案例
# 格式: SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
# 查询2011年以前入职的员工信息, 包括部门名称
# 第一步: 查询2011年以前入职的员工
SELECT * FROM emp WHERE join_date < '2011';
# 第二步: 查询所有的部门信息, 第一步的员工信息中的dept_id与dept中的id相同的输出
SELECT *
FROM dept d, (SELECT * FROM emp WHERE join_date < '2011') e1
WHERE e1.dept_id=d.id;
