为什么要有多表

最直观的感受是减少冗余。比如工作地点，通常工作地点也就那几个地方，每个员工都存一个很长的字符串就很不方便了。可以考虑拆成两张表。
多表环境下，有外键的表叫做从表，外键对应的表叫做主表。

外键约束

防止从表随便插入

/*
    添加外键    方式1 建表时给出
    create table xxx (
        字段...
        [constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
    );
*/

CREATE TABLE employee(

    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
        # 添加级联删除
        # ON DELETE CASCADE
);

/*
    增加数据 先增加主表
    删除数据 先删除从表
*/

# 删除外键 
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
# 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES department(id);

# 级联删除
# 可以在删除主表数据时删除与之相关的从表的数据

多表之间的关系

一对一人和身份证
一对多班级和学生多的那一方加外键即可
多对多学生和课程需要中间表，中间表中的是外键

多表查询

/*    会产生笛卡尔积    */
SELECT * FROM products, category;

内连接

注意，连接本身与逻辑无关。你where条件怎么写，和你多表查询要连接是没关系的。

# 隐式内连接    用where指定连接条件
# 1) 查询所有商品信息和对应的分类信息
SELECT * FROM products, category WHERE category_id = cid;
# 2) 查询商品表的商品名称 和 价格,以及商品的分类信息
SELECT 
    p.`pname`, p.`price`, c.`cname`
FROM products p, category c WHERE p.category_id = c.cid;
# 3) 查询 格力空调是属于哪一分类下的商品
SELECT p.`pname`, c.`cname` FROM products p, category c WHERE c.`cid` = p.`category_id`  AND p.`pname` = '格力空调';

# 显式内连接
# 1) 查询所有商品信息和对应的分类信息
SELECT * FROM products p INNER JOIN category c ON p.`category_id` = c.`cid`; 
# 2) 查询鞋服分类下,价格大于500的商品名称和价格
SELECT p.`pname`, p.`price` FROM products p INNER JOIN category c ON p.`category_id` = c.`cid` WHERE p.`price` > 500 
AND c.`cname` = '鞋服';

外连接

左外连接以左表为基准，右外连接以右表为基准

# 外连接查询 左外left join
SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`;
# 查询每个分类下的商品个数
SELECT c.`cname`, COUNT(p.`pid`) FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id` GROUP BY c.`cname`;
# 右外连接 right join
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

子查询

子查询一定要放在小括号里。

# 子查询
# 找出最贵的商品的信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

子查询分类

where型子查询结果作为where条件

# 找出最贵的商品的信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
# 查询化妆品分类下的 商品名称 商品价格
SELECT p.`pname`, p.`price` FROM products p WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');
# 查询小于平均价格的商品信息
SELECT AVG(price) FROM products;
SELECT * FROM products WHERE price < (SELECT AVG(price) FROM products);

from型子查询结果是一张表

SELECT 
p.`pname`, p.`price`, c.`cname` 
FROM products p 
INNER JOIN category c 
WHERE p.`category_id` = c.`cid` AND p.`price` > 500;

SELECT 
p.`pname`, p.`price`, c.`cname` 
FROM products p 
# 注意这个括号里的子查询是个虚拟表，所以必须起个别名
INNER JOIN (SELECT * FROM category) c 
WHERE p.`category_id` = c.`cid` AND p.`price` > 500;

exists型子查询结果是单列多行

# 查询价格小于两千的商品,来自于哪些分类(名称)
SELECT * FROM category WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);
# 查询家电类 与 鞋服类下面的全部商品
SELECT * FROM products WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','鞋服'));

三范式

目的：创建冗余小结构合理的数据库。范式就是设计数据库的要求/规范。
1NF 原子列，列不可以再分。
2NF 确保表中每一列都和主键所在列相关，一张表只描述一件事。
3NF 消除传递依赖。表中的列不应该能被推导出来。

anti-三范式

通过增加冗余来换取性能

冗余字段-> 某个字段属于一张表，但是又在多张表中都有出现。
冗余字段可以减少join的次数。

MYSQL 多表相关