MySQL外键约束和多表查询

外键约束和多表查询

一、外键是什么

图解


知识点

1. 外键: 多个表之间的关联字段
3. 特点1: 从表外键的值是对主表主键的引用。
4. 特点2: 从表外键类型，必须与主表主键类型一致。
6. 主从表:  外键字段所在的表是从表,依赖字段对应的表是主表
7. 多表关系:  一对一    一对多    多对多
8. 一对多关系:  主表是一方  从表是多方

复制代码

外键约束

1. 外键约束: FOREIGN KEY  
3. 外键约束作用:
4. 保证了数据的准确性: 限制了从表在插入数据的时候,不能插入主表不存在的数据
5. 保证了数据的完整性: 限制了主表在删除数据的时候,不能删除从表已经引用的数据
7. 如果添加外键约束:
8. 在建从表时候添加(建议): constraint [外键名称] foreign key(外键字段名) references 主表(主键)

复制代码

1. # 拓展存储引擎
2. # 查看所有存储引擎
3. show ENGINES;
4. # 查看默认存储引擎
5. show variables like '%default_storage_engine%';
6. # 注意: innodb支持外键而myisam不支持外键
7. # 如果要使用外键: 你的mysql存储引擎是myisam需要修改成innodb
9. #数据准备
10. # 分类表
11. CREATE TABLE category(
12.     cid   VARCHAR(32) PRIMARY KEY, # 分类id
13.     cname VARCHAR(100) #分类名称
14. );
16. # 商品表
17. CREATE TABLE products
18. (
19.   pid VARCHAR(100) PRIMARY KEY , # 商品id
20.   pname VARCHAR(40) ,# 商品名称
21.   price DOUBLE ,# 商品价格
22.   category_id VARCHAR(32),# 分类id
23.   CONSTRAINT FOREIGN KEY(category_id) REFERENCES category(cid) # 添加外键约束
24. );
25. # 查看表建表语句
26. show create table category;
27. show create table products;
28. # 插入测试数据
29. #1 向分类表中添加数据
30. INSERT INTO category (cid ,cname) VALUES('c001','服装');
31. #2 向商品表添加普通数据,没有外键数据，默认为null
32. INSERT INTO products (pid,pname) VALUES('p001','商品名称');
33. #3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
34. INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');
36. # 演示外键约束的限制作用
37. # 限制从表插入数据的时候不能插入主表不存在的数据,否则报错
38. INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999'); # 报错
39. # 限制主表不能删删除从表已经引用的数据,否则报错
40. DELETE FROM category WHERE cid = 'c001';# 报错

复制代码

多表查询

图解

数据准备

1. # 创建hero表
2. CREATE TABLE hero(
3.     hid       INT PRIMARY KEY,# 英雄id
4.     hname     VARCHAR(255),# 英雄名称
5.     kongfu_id INT # 对应功夫id
6. );
8. # 创建kongfu表
9. CREATE TABLE kongfu
10. (
11.     kid   INT PRIMARY KEY, # 功夫id
12.     kname VARCHAR(255) # 功夫名
13. );
14. # 插入hero数据
15. INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
17. # 插入kongfu数据
18. INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');

复制代码

交叉连接

1. 交叉连接关键字: cross join
3. 注意: 交叉连接会产生笛卡尔积(离散数学里面学过)
5. 格式: select 字段名 from 左表 cross join 右表 ;     注意:以后一般不用

复制代码

内连接

知识点

1. 内连接关键字:表1 [inner] join 表2 on 条件
2. 显式内连接格式:select 字段名 from 左表 inner join 右表 on 左右关联条件;
3. 隐式内连接格式:select 字段名 from 左表,右表 where 左右关联条件;

复制代码

示例

1. # 需求: 查找英雄中有对应功夫的信息
2. # 显式格式: select 字段名 from 左表 inner join 右表 on 左右表关联条件
3. SELECT * FROM hero inner join kongfu on kongfu_id = kid;
4. # 隐式格式: select 字段名 from 左表 , 右表 where 左右表关联条件
5. SELECT * FROM hero,kongfu WHERE kongfu_id = kid;

复制代码

外连接

知识点

1. 左外连接关键字:表1 left [outer] join 表2 on 条件
2. 右外连接关键字:表1 right [outer] join 表2 on 条件
4. 注意:outer可以省略
6. 左外连接格式: select 字段名 from 左表 left outer join 右表 on 左右表关联条件 ;
7. 右外连接格式: select 字段名 from 左表 right outer join 右表 on 左右表关联条件 ;

复制代码

示例

1. # 需求: 查找所有英雄对应功夫信息,即使没有功夫也要展示信息
2. # 左外连接格式: select 字段名 from 左表 left outer join 右表 on 左右表关联条件 ;
3. # 左连接效果: 以左表为主,左表数据都展示,右表只展示和左表关联上的数据,其他内容null补全
4. select hname,kname from hero left outer join kongfu on hero.kongfu_id=kongfu.kid;
5. select hname,kname from hero left  join kongfu on hero.kongfu_id=kongfu.kid;
7. # 右外连接格式: select 字段名 from 左表 right outer join 右表 on 左右表关联条件 ;
8. select hname,kname from kongfu right outer join hero on hero.kongfu_id=kongfu.kid;
9. select hname,kname from kongfu right join hero on hero.kongfu_id=kongfu.kid;

复制代码

内外连接练习

准备数据

1. # 创建分类表
2. CREATE TABLE category (
3.   cid VARCHAR(32) PRIMARY KEY ,
4.   cname VARCHAR(50)
5. );
6. # 创建商品表
7. CREATE TABLE products(
8.   pid VARCHAR(32) PRIMARY KEY ,
9.   pname VARCHAR(50),
10.   price INT,
11.   flag VARCHAR(2),    #是否上架标记为：1表示上架、0表示下架
12.   category_id VARCHAR(32),
13.   CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
14. );
15. # 插入数据
16. # 分类
17. INSERT INTO category(cid,cname) VALUES('c001','家电');
18. INSERT INTO category(cid,cname) VALUES('c002','服饰');
19. INSERT INTO category(cid,cname) VALUES('c003','化妆品');
20. INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
21. # 商品
22. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
23. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
24. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
25. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
26. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
27. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
28. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
29. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
30. INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'0','c003');

复制代码

示例

1. # 1.查询哪些分类的商品已经上架,要求展示分类名称
2. # 注意: 如果表名称较长,可以使用别名,as关键字可以省略
3. select distinct cname from category c join products p on c.cid = p.category_id where flag = '1';
4. # 2.查询所有分类商品的个数,要求展示分类名称
5. # 注意: 可以利用聚合函数(字段名)的忽略null的特点
6. select cname,count(category_id) from category c left join products p ON c.cid = p.category_id GROUP BY cname;

复制代码

子查询

知识点

1. 子查询核心思路:   一个select语句的结果作为另一个select语句的部分(表或者条件)
3. 子查询作为表:  select 字段名 from (子查询语句) as 别名;
5. 子查询作为条件: select 字段名 from 表名 where ... (子查询语句);

复制代码

示例

1. # 一个语句作为另外一个语句的一部分(表或者条件)
4. # 演示子查询作为条件
5. #  1.查询哪些分类的商品已经上架,要求展示分类名称
6. SELECT DISTINCT
7.     category_id
8. FROM
9.     products
10. WHERE
11.     flag = '1'; #先找已经上架的商品的id
12. SELECT
13.     cname
14. FROM
15.     category
16. WHERE
17.     cid IN (SELECT DISTINCT category_id FROM products WHERE flag = '1');
18. #将上条查询作为条件，进行子查询
19. # 2.查询“化妆品”分类上架商品详情
20. SELECT
21.     cid
22. FROM
23.     category
24. WHERE
25.     cname = '化妆品'; #先查询化妆品的商品id是什么
26. SELECT *
27. FROM
28.     products
29. WHERE
30.       flag = '1'
31.   AND category_id = (SELECT cid FROM category WHERE cname = '化妆品');
32. #将上条查询作为条件，进行子查询
34. # 3.查询“化妆品”和“家电”两个分类上架商品详情
35. SELECT
36.     cid
37. FROM
38.     category
39. WHERE
40.     cname IN ('化妆品', '家电');#先查询化妆品和家电的商品id是什么
41. SELECT *
42. FROM
43.     products
44. WHERE
45.       flag = '1'
46.   AND category_id IN (SELECT cid FROM category WHERE cname IN ('化妆品', '家电'));
47. #将上条查询作为条件，进行子查询
49. # 演示子查询作为表
50. # 1.查询“化妆品”分类上架商品详情，要求包含分类名称
51. # 显式内连接
52. SELECT *
53. FROM
54.     category
55. WHERE
56.     cname = '化妆品'; #查询化妆品分类下的商品信息，作为表
57. SELECT *
58. FROM
59.     products p
60.         JOIN (SELECT * FROM category WHERE cname = '化妆品') t1 ON p.category_id = t1.cid
61. WHERE
62.     flag = '1'; #将上表与商品表连接起来，之后进行查询

复制代码

自连接

知识点

1. 自连接作为一种特例，可以将一个表与它自身进行连接，称为自连接。
3. 语法: 自连接语法和内外连接的语法一样，只不过换成了只在同一张表上面操作
5. 特点: 特殊的地方就是左表和右表是同一张表,只是起了不同的别名

复制代码

示例

1. # 假设现在有一个区域表areas，里面是我国区域阶级，如下图所示北京市下属有几个区，每个区的pid是其上级区域
2. # 分析: 省市区三级都在一个表中,那么就可以使用自连接
4. # 需求1: 查询河北省所有的城市
5. # 自连接方式  思路: 通过起别名把一个表(区域表)变成两个表(城市表,省级表)使用
6. #自连接，将表复制为两个表，一个取名city，一个起名province，进行关联，查找
7. SELECT *
8. FROM
9.     areas city
10.         JOIN areas province ON city.pid = province.id
11. WHERE
12.     province.title = '河北省';
14. #查邯郸市下的区县
15. SELECT *
16. FROM
17.     areas district
18.         JOIN areas city on district.pid = city.id
19. where city.title='邯郸市';

复制代码

<img alt="image-20230511203635933" loading="lazy">

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！