翼度科技»论坛 编程开发 mysql 查看内容

MySQL外键约束和多表查询

9

主题

9

帖子

27

积分

新手上路

Rank: 1

积分
27
外键约束和多表查询

一、外键是什么

图解


知识点
  1. 外键: 多个表之间的关联字段
  2. 特点1: 从表外键的值是对主表主键的引用。
  3. 特点2: 从表外键类型,必须与主表主键类型一致。
  4. 主从表:  外键字段所在的表是从表,依赖字段对应的表是主表
  5. 多表关系:  一对一    一对多    多对多
  6. 一对多关系:  主表是一方  从表是多方
复制代码
外键约束
  1. 外键约束: FOREIGN KEY  
  2. 外键约束作用:
  3. 保证了数据的准确性: 限制了从表在插入数据的时候,不能插入主表不存在的数据
  4. 保证了数据的完整性: 限制了主表在删除数据的时候,不能删除从表已经引用的数据
  5. 如果添加外键约束:
  6. 在建从表时候添加(建议): constraint [外键名称] foreign key(外键字段名) references 主表(主键)
复制代码
  1. # 拓展存储引擎
  2. # 查看所有存储引擎
  3. show ENGINES;
  4. # 查看默认存储引擎
  5. show variables like '%default_storage_engine%';
  6. # 注意: innodb支持外键而myisam不支持外键
  7. # 如果要使用外键: 你的mysql存储引擎是myisam需要修改成innodb
  8. #数据准备
  9. # 分类表
  10. CREATE TABLE category(
  11.     cid   VARCHAR(32) PRIMARY KEY, # 分类id
  12.     cname VARCHAR(100) #分类名称
  13. );
  14. # 商品表
  15. CREATE TABLE products
  16. (
  17.   pid VARCHAR(100) PRIMARY KEY , # 商品id
  18.   pname VARCHAR(40) ,# 商品名称
  19.   price DOUBLE ,# 商品价格
  20.   category_id VARCHAR(32),# 分类id
  21.   CONSTRAINT FOREIGN KEY(category_id) REFERENCES category(cid) # 添加外键约束
  22. );
  23. # 查看表建表语句
  24. show create table category;
  25. show create table products;
  26. # 插入测试数据
  27. #1 向分类表中添加数据
  28. INSERT INTO category (cid ,cname) VALUES('c001','服装');
  29. #2 向商品表添加普通数据,没有外键数据,默认为null
  30. INSERT INTO products (pid,pname) VALUES('p001','商品名称');
  31. #3 向商品表添加普通数据,含有外键信息(category表中存在这条数据)
  32. INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');
  33. # 演示外键约束的限制作用
  34. # 限制从表插入数据的时候不能插入主表不存在的数据,否则报错
  35. INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999'); # 报错
  36. # 限制主表不能删删除从表已经引用的数据,否则报错
  37. 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. );
  7. # 创建kongfu表
  8. CREATE TABLE kongfu
  9. (
  10.     kid   INT PRIMARY KEY, # 功夫id
  11.     kname VARCHAR(255) # 功夫名
  12. );
  13. # 插入hero数据
  14. INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
  15. # 插入kongfu数据
  16. INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');
复制代码
交叉连接
  1. 交叉连接关键字: cross join
  2. 注意: 交叉连接会产生笛卡尔积(离散数学里面学过)
  3. 格式: 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 条件
  3. 注意:outer可以省略
  4. 左外连接格式: select 字段名 from 左表 left outer join 右表 on 左右表关联条件 ;
  5. 右外连接格式: 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;
  6. # 右外连接格式: select 字段名 from 左表 right outer join 右表 on 左右表关联条件 ;
  7. select hname,kname from kongfu right outer join hero on hero.kongfu_id=kongfu.kid;
  8. 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语句的部分(表或者条件)
  2. 子查询作为表:  select 字段名 from (子查询语句) as 别名;
  3. 子查询作为条件: select 字段名 from 表名 where ... (子查询语句);
复制代码
示例
  1. # 一个语句作为另外一个语句的一部分(表或者条件)
  2. # 演示子查询作为条件
  3. #  1.查询哪些分类的商品已经上架,要求展示分类名称
  4. SELECT DISTINCT
  5.     category_id
  6. FROM
  7.     products
  8. WHERE
  9.     flag = '1'; #先找已经上架的商品的id
  10. SELECT
  11.     cname
  12. FROM
  13.     category
  14. WHERE
  15.     cid IN (SELECT DISTINCT category_id FROM products WHERE flag = '1');
  16. #将上条查询作为条件,进行子查询
  17. # 2.查询“化妆品”分类上架商品详情
  18. SELECT
  19.     cid
  20. FROM
  21.     category
  22. WHERE
  23.     cname = '化妆品'; #先查询化妆品的商品id是什么
  24. SELECT *
  25. FROM
  26.     products
  27. WHERE
  28.       flag = '1'
  29.   AND category_id = (SELECT cid FROM category WHERE cname = '化妆品');
  30. #将上条查询作为条件,进行子查询
  31. # 3.查询“化妆品”和“家电”两个分类上架商品详情
  32. SELECT
  33.     cid
  34. FROM
  35.     category
  36. WHERE
  37.     cname IN ('化妆品', '家电');#先查询化妆品和家电的商品id是什么
  38. SELECT *
  39. FROM
  40.     products
  41. WHERE
  42.       flag = '1'
  43.   AND category_id IN (SELECT cid FROM category WHERE cname IN ('化妆品', '家电'));
  44. #将上条查询作为条件,进行子查询
  45. # 演示子查询作为表
  46. # 1.查询“化妆品”分类上架商品详情,要求包含分类名称
  47. # 显式内连接
  48. SELECT *
  49. FROM
  50.     category
  51. WHERE
  52.     cname = '化妆品'; #查询化妆品分类下的商品信息,作为表
  53. SELECT *
  54. FROM
  55.     products p
  56.         JOIN (SELECT * FROM category WHERE cname = '化妆品') t1 ON p.category_id = t1.cid
  57. WHERE
  58.     flag = '1'; #将上表与商品表连接起来,之后进行查询
复制代码
自连接

知识点
  1. 自连接作为一种特例,可以将一个表与它自身进行连接,称为自连接。
  2. 语法: 自连接语法和内外连接的语法一样,只不过换成了只在同一张表上面操作
  3. 特点: 特殊的地方就是左表和右表是同一张表,只是起了不同的别名
复制代码
示例
  1. # 假设现在有一个区域表areas,里面是我国区域阶级,如下图所示北京市下属有几个区,每个区的pid是其上级区域
  2. # 分析: 省市区三级都在一个表中,那么就可以使用自连接
  3. # 需求1: 查询河北省所有的城市
  4. # 自连接方式  思路: 通过起别名把一个表(区域表)变成两个表(城市表,省级表)使用
  5. #自连接,将表复制为两个表,一个取名city,一个起名province,进行关联,查找
  6. SELECT *
  7. FROM
  8.     areas city
  9.         JOIN areas province ON city.pid = province.id
  10. WHERE
  11.     province.title = '河北省';
  12. #查邯郸市下的区县
  13. SELECT *
  14. FROM
  15.     areas district
  16.         JOIN areas city on district.pid = city.id
  17. where city.title='邯郸市';
复制代码

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


来源:https://www.cnblogs.com/lionet-kk/p/mysql_03.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具