多表查询以及外键约束

一、外键约束

（1）外键：外键指的是在主表中与从表的主键对应的的那个字段。

（2）外键约束：使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。

 （3）创建外键约束

新建表时添加外键约束：constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段); 

CREATE TABLE emp(
    eid INT PRIMARY KEY auto_increment,
    ename VARCHAR(15),
    age INT,
    did int,
    CONSTRAINT emp_dept_fk FOREIGN KEY(did) REFERENCES dept(did)
);

为已创建好的表添加外键约束：alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名);

ALTER TABLE emp

add CONSTRAINT emp_dept_fk

FOREIGN KEY(did)

REFERENCES dept(did);

 （3）删除外键约束：alter table 从表名 drop foreign key 外键约束的名称;

alter table emp drop foreign key emp_dept_fk;

 （4）外键约束注意事项：

1. 从表的外键类型必须和主表的主键类型保持一致。

2. 添加从表数据时, 从表中添加的外键值, 必须在主表的主键中存在。

3. 删除和变更数据主表数据时, 先删除从表中的数据或将外键设置为null, 再删除主表中的数据。

 （5）设置外键约束的变更和删除的级联操作

选项	作用
RESTRICT(默认)	如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
No action	同RESTRICT, 都是立即检查外键约束
CASCADE	在主表上update/delete记录时，同步update/delete掉从表匹配的记录
SET NULL	在主表上update/delete记录时，将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null

ALTER TABLE emp add CONSTRAINT emp_dept_fk FOREIGN KEY(did) REFERENCES dept(did) ON UPDATE CASCADE ON DELETE CASCADE;

二、多表关系设计

（1）一对多关系

• 一对多关系(1 : n) 例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品

• 一对多建表原则 在从表(多方)创建一个字段, 该字段作为外键指向主表的主键

（2）多对多关系

• 多对多关系(m : n) 例如: 老师和学生, 学生和课程, 用户和角色。

• 多对多关系建表原则 多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多。

（3）一对一关系

• 在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表。

 三、多表查询

（1）内连接：

• 通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上就不显示

• 如: 按照从表的外键 = 主表的主键进行匹配 （ inner join on）, inner可以省略

隐式内连接 ： select 字段名 from 表1, 表2 where 连接条件;

SELECT *
FROM category,products
WHERE category.cid = products.cid;

显示内连接：select 字段名 from 表1 [inner] join 表2 on 条件;

SELECT *
FROM category c JOIN products p ON c.cid=p.cid;

自连接：自连接就是某张表自己和自己关联。

SELECT e2.ename "领导",e1.ename "员工"
FROM emp e1,emp e2
WHERE e1.mgr = e2.empno;

非等值连接：非等值连接就是两张表进行关联的时候不是使用关联的id相等进行连接的。

SELECT *
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;

（2）外连接：通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示。

左外连接：select 字段名 from 表1 left join 表2 on 条件;

特点：

1. 以左表为主, 左表中的数据全部显示

2. 右表匹配到数据就显示匹配到的数据

3. 右表没有匹配的数据显示为null

SELECT *
FROM products p LEFT JOIN category c on p.cid = c.cid;

右外连接：select 字段名 from 表1 right join 表2 on 条件;

特点：

1. 以右表为主, 右表中的数据全部显示

2. 左表匹配到数据就显示匹配到的数据

3. 左表没有匹配的数据显示为null

SELECT *
FROM products p RIGHT JOIN category c on p.cid = c.cid;

全外连接：

(SELECT  列名 FROM 表名 LEFT OUTER JOIN  表名 ON  连接条件) 
UNION 
(SELECT  列名 FROM 表名 RIGHT OUTER JOIN  表名 ON  连接条件)

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT（去重）。

• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

SELECT * FROM products p LEFT JOIN category c on p.cid = c.cid

UNION 

SELECT * FROM products p RIGHT JOIN category c on p.cid = c.cid;

四、子查询

1. 子查询概念

   一条select查询语句的结果作为另一条select语句的一部分

2. 子查询的特点

   子查询一般作为查询条件使用

   使用子查询， 必须将子查询放在小括号中使用

   一条SQL语句含有多个select，一般是先执行子查询，再执行外查询

子查询常用谓词：

谓词	ANY	ALL	EXISTS | IN
意义	某一个	所有	存在

（1）单行子查询：select 字段 from 表 where 字段 判断符号 (子查询); 

特点：查询出的结果为一列一行(一个数据) 如: 最高, 最低, 平均等, 可以使用判断符号 如: > | < | = | != 等。

# 查询价格最高的商品信息

-- 1.查询出商品最高的价格
select max(price) from products;  -- 5000
select * from products where price = 5000;

-- 2.将查询出的最高价格作为条件查询, 获取商品信息 
select * from products where price = (select max(price) from products);

（2）多行子查询：查询出的结果为一列多行(多个数据) 如: 化妆品类别下的商品cid, , 可以使用判断符号 如: in all any。

# 查询化妆品类别中的商品价格和鞋服类别中的商品价格一样的商品信息

-- 1.查询化妆品类别下所有商品的价格
select price from products where cid = 'c003'; -- 800 200

-- 2.查询鞋服类别下所有商品的价格
select price from products where cid = 'c002'; -- 800 200 300 2000

-- 3.化妆品中商品的价格和鞋服中商品的价格进行条件判断
select * from products 
where cid = 'c003' and price in (select price from products where cid = 'c002');