SQL外键约束&&多表查询&&级联删除&&子查询

一、外键

• 外键指的是在从表中与主表的主键对应的字段
• 使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表的引用的完整性
• 多表关系中的主表和从表
  • 主表：主键id所在的表，约束别人的表
  • 从表：外键所在的表，被约束的表

1.创建外键约束

• 新建表时候添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);

• 已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

2.删除外键约束

• 删除外键约束

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

3.外键约束提示

• 从表外键类型必须与主表外键类型一致，否则创建失败
• 添加数据时，应该先添加主表中的数据，再添加从表中的数据
• 删除数据时，应该先删除从表中的数据，再删除主表中的数据

二、级联删除

• 如果想实现删除主表数据的同时，也删除掉从表数据，可以使用级联删除操作

ON DELETE CASCADE
在创建表的同时添加上述格式代码

三、多表查询

1.笛卡尔积

• 概念：
  
• 举例：

2.内连接查询

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

1.隐式内连接

• from子句后面直接写多个表名，使用where指定连接条件

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;

SELECT
p.pname '商品',
p.price '价格',
c.cname '种类'
FROM products p, category c WHERE c.cid = p.category_id AND p.price = 5000;

2.显示内连接

使用inner join …on 这种方式

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
– inner 可以省略

SELECT * FROM products p INNER JOIN category c ON c.cid = p.category_id AND c.cname = '家电';

SELECT * FROM products p INNER JOIN category c ON c.cid = p.category_id 
WHERE c.cname = '家电';

3.外连接查询

1.左外连接

• 使用 LEFT OUTER JOIN , OUTER 可以省略
• 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
• 如果匹配不到, 左表中的数据正常展示, 右边的展示为null

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

SELECT 
c.cname AS '商品种类',
p.pname AS '商品名称'
FROM category c LEFT JOIN products p ON p.category_id = c.cid;

2.右外连接

• 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
• 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
• 如果匹配不到，右表中的数据正常展示, 左边展示为null

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

SELECT 
c.cname AS '商品种类',
p.pname AS '商品名称'
FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

四、子查询

• 概念：
  • 一条select 查询语句的结果, 作为另一条 select 语句的一部分
• 特点
  • 子查询必须放在小括号中
  • 子查询一般作为父查询的查询条件使用

常见分类

• where型子查询: 将子查询的结果, 作为父查询的比较条件
• from型子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
• exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

1.子查询的结果作为查询条件

SELECT 查询字段 FROM 表 WHERE 字段=(子查询);

SELECT
* 
FROM category c 
WHERE c.cid = (SELECT category_id FROM products WHERE price = 5000); 

2.子查询的结果作为一张表

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

SELECT
*
FROM category c 
INNER JOIN (SELECT * FROM products) p 
ON c.cid = p.category_id; 

3.子查询结果是单行多列

SELECT 查询字段 FROM 表 WHERE 字段 IN(子查询);

SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products);