MySQL：外键约束，多表查询，三范式

补一下之前的笔记

---

一、外键

1.1多表和数据准备

生活中有时候一张表解决不了需求，所以我们需要多张表。

CREATE DATABASE db3 CHARACTER SET utf8;

-- 创建emp表 主键自增
CREATE TABLE emp(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT ,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

在添加数据的时候，发现部门信息大量重复，造成了数据的冗余因此我们建立多张表格

-- 创建部门表
-- 一方,主表
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);
-- 创建员工表
-- 多方 ,从表
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT
);
-- 添加2个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1);
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2);
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2);
SELECT * FROM employee;

1.2 外键和主从表

我们发现员工表中的dept_id和部门的主键id相对应，我们把员工表的dept_id成为外键。外键所在的表称为从表，主键所在的表称为主表

1.3外键约束

虽然关系上是那么说的，但是在数据库中我们还没有建立主从表之间的联系。外键可以添加没有的主键，这显然是不被允许的。

1.3.1 添加外键约束

--创建时添加主键约束
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
--创建后的表添加主键约束
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES
主表(主 键字段名);

1.3.2 删除外键约束

alter table 从表 drop foreign key 外键约束名称

可以知道，创建外键约束名称不仅可以方便我们理解约束，还可以方便我们删除。
当创建时缺省外键约束名称，会自动为我们生成，在表的详细信息中可以查看。
或者直接使用show keys from 表名语句。

1.4 级联删除操作

可以删除主表数据的同时，也删除掉从表数据

create table 表名(
	...
	on delete cascade
	);

二、多表查询

2.1笛卡尔积

直接查询多张表会出现笛卡尔积

select 字段名 from 表1，表2;

集合A和集合B的笛卡尔积就是：
$\{(a,b)|a\in A,b\in B\}$

比如表1中存在两个字段，学生id和学生姓名。表2中有学生id和学生成绩。 表1中的数据为id=1,name=1和id=2,name=2
表2中的数据为id=1,score=1和id=2,score=2 那么查询后得到的新表为： id=1,name=1,id=1,score=1
id=1,name=1,id=2,score=2 id=2,name=2,id=1,score=1
id=2,name=2,id=2,score=2

这显然不是我们想要的结果

2.2 内连接查询

通过指定条件例如：从表的外键=主表的主键 方式去匹配

2.2.1 隐式内连接

select 字段名 from 左表 左表别名，右表 右表别名 where 连接条件;

2.2.2 显式内连接

select 字段 from 左表 [inner] join 右表 on 条件;

2.3 外连接查询

外连接的特点是以一张表为基准。

2.3.1 左外连接

select 字段 from 左表 left [outer] join 右表 on 条件;

左外连接以左表为基准

2.3.2 右外连接

select 字段 from 左表 right [outer] join 右表 on 条件;

如果匹配不到左表的数据，那么左表的数据为Null

三、子查询

即将第一次查询的结果当第二次查询的条件
返回的结果为一行一列的时候：可以当值使用

select 字段 from 表 where 字段=(子查询);

返回的结果为多行行一列的时候：可以当数组使用 in (子查询)

select 字段 from 表 where 字段 in (子查询);

返回的结果为多列的时候：只能当作一张表来用，并且需要起别名（因为没名字就无法后续访问

select 字段 from (子查询) [as] 别名 where 条件;

四、数据库设计

数据库设计三范式

4.1 第一范式

列不可再分
最低要求满足第一范式，字段都是单一属性，不可再分，如果可以再分，则不满足第一范式

4.2 第二范式

一个表描述一件事情
满足第一范式的情况下，要求表的非主键字段必须依赖主键字段

4.3 第三范式

消除传递依赖
能够被推导出来的字段不应该单独设计

4.4 反三范式

就是违反了三范式的设计，违反了三范式虽然浪费了存储的空间，但是加快了查询的时间。
当一些字段反复被查询时，经常在每一个表中添加。

都看到这里了，顺手给个赞吧