MySQL外键约束和多表查询
外键约束和多表查询一、外键是什么
图解
知识点
外键: 多个表之间的关联字段
特点1: 从表外键的值是对主表主键的引用。
特点2: 从表外键类型，必须与主表主键类型一致。
主从表:外键字段所在的表是从表,依赖字段对应的表是主表
多表关系:一对一 一对多 多对多
一对多关系:主表是一方从表是多方外键约束
外键约束: FOREIGN KEY
外键约束作用:
保证了数据的准确性: 限制了从表在插入数据的时候,不能插入主表不存在的数据
保证了数据的完整性: 限制了主表在删除数据的时候,不能删除从表已经引用的数据
如果添加外键约束:
在建从表时候添加(建议): constraint [外键名称] foreign key(外键字段名) references 主表(主键)# 拓展存储引擎
# 查看所有存储引擎
show ENGINES;
# 查看默认存储引擎
show variables like '%default_storage_engine%';
# 注意: innodb支持外键而myisam不支持外键
# 如果要使用外键: 你的mysql存储引擎是myisam需要修改成innodb
#数据准备
# 分类表
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY, # 分类id
cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
pid VARCHAR(100) PRIMARY KEY , # 商品id
pname VARCHAR(40) ,# 商品名称
price DOUBLE ,# 商品价格
category_id VARCHAR(32),# 分类id
CONSTRAINT FOREIGN KEY(category_id) REFERENCES category(cid) # 添加外键约束
);
# 查看表建表语句
show create table category;
show create table products;
# 插入测试数据
#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');
#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');
#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');
# 演示外键约束的限制作用
# 限制从表插入数据的时候不能插入主表不存在的数据,否则报错
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999'); # 报错
# 限制主表不能删删除从表已经引用的数据,否则报错
DELETE FROM category WHERE cid = 'c001';# 报错多表查询
图解
数据准备
# 创建hero表
CREATE TABLE hero(
hid INT PRIMARY KEY,# 英雄id
hname VARCHAR(255),# 英雄名称
kongfu_id INT # 对应功夫id
);
# 创建kongfu表
CREATE TABLE kongfu
(
kid INT PRIMARY KEY, # 功夫id
kname VARCHAR(255) # 功夫名
);
# 插入hero数据
INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
# 插入kongfu数据
INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');交叉连接
交叉连接关键字: cross join
注意: 交叉连接会产生笛卡尔积(离散数学里面学过)
格式: select 字段名 from 左表 cross join 右表 ; 注意:以后一般不用内连接
知识点
内连接关键字:表1 join 表2 on 条件
显式内连接格式:select 字段名 from 左表 inner join 右表 on 左右关联条件;
隐式内连接格式:select 字段名 from 左表,右表 where 左右关联条件;示例
# 需求: 查找英雄中有对应功夫的信息
# 显式格式: select 字段名 from 左表 inner join 右表 on 左右表关联条件
SELECT * FROM hero inner join kongfu on kongfu_id = kid;
# 隐式格式: select 字段名 from 左表 , 右表 where 左右表关联条件
SELECT * FROM hero,kongfu WHERE kongfu_id = kid;外连接
知识点
左外连接关键字:表1 left join 表2 on 条件
右外连接关键字:表1 right join 表2 on 条件
注意:outer可以省略
左外连接格式: select 字段名 from 左表 left outer join 右表 on 左右表关联条件 ;
右外连接格式: select 字段名 from 左表 right outer join 右表 on 左右表关联条件 ;示例
# 需求: 查找所有英雄对应功夫信息,即使没有功夫也要展示信息
# 左外连接格式: select 字段名 from 左表 left outer join 右表 on 左右表关联条件 ;
# 左连接效果: 以左表为主,左表数据都展示,右表只展示和左表关联上的数据,其他内容null补全
select hname,kname from hero left outer join kongfu on hero.kongfu_id=kongfu.kid;
select hname,kname from hero leftjoin kongfu on hero.kongfu_id=kongfu.kid;
# 右外连接格式: select 字段名 from 左表 right outer join 右表 on 左右表关联条件 ;
select hname,kname from kongfu right outer join hero on hero.kongfu_id=kongfu.kid;
select hname,kname from kongfu right join hero on hero.kongfu_id=kongfu.kid;内外连接练习
准备数据
# 创建分类表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
# 创建商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
# 插入数据
# 分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
# 商品
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'0','c003');示例
# 1.查询哪些分类的商品已经上架,要求展示分类名称
# 注意: 如果表名称较长,可以使用别名,as关键字可以省略
select distinct cname from category c join products p on c.cid = p.category_id where flag = '1';
# 2.查询所有分类商品的个数,要求展示分类名称
# 注意: 可以利用聚合函数(字段名)的忽略null的特点
select cname,count(category_id) from category c left join products p ON c.cid = p.category_id GROUP BY cname;子查询
知识点
子查询核心思路: 一个select语句的结果作为另一个select语句的部分(表或者条件)
子查询作为表:select 字段名 from (子查询语句) as 别名;
子查询作为条件: select 字段名 from 表名 where ... (子查询语句);示例
# 一个语句作为另外一个语句的一部分(表或者条件)
# 演示子查询作为条件
#1.查询哪些分类的商品已经上架,要求展示分类名称
SELECT DISTINCT
category_id
FROM
products
WHERE
flag = '1'; #先找已经上架的商品的id
SELECT
cname
FROM
category
WHERE
cid IN (SELECT DISTINCT category_id FROM products WHERE flag = '1');
#将上条查询作为条件，进行子查询
# 2.查询“化妆品”分类上架商品详情
SELECT
cid
FROM
category
WHERE
cname = '化妆品'; #先查询化妆品的商品id是什么
SELECT *
FROM
products
WHERE
flag = '1'
AND category_id = (SELECT cid FROM category WHERE cname = '化妆品');
#将上条查询作为条件，进行子查询
# 3.查询“化妆品”和“家电”两个分类上架商品详情
SELECT
cid
FROM
category
WHERE
cname IN ('化妆品', '家电');#先查询化妆品和家电的商品id是什么
SELECT *
FROM
products
WHERE
flag = '1'
AND category_id IN (SELECT cid FROM category WHERE cname IN ('化妆品', '家电'));
#将上条查询作为条件，进行子查询
# 演示子查询作为表
# 1.查询“化妆品”分类上架商品详情，要求包含分类名称
# 显式内连接
SELECT *
FROM
category
WHERE
cname = '化妆品'; #查询化妆品分类下的商品信息，作为表
SELECT *
FROM
products p
JOIN (SELECT * FROM category WHERE cname = '化妆品') t1 ON p.category_id = t1.cid
WHERE
flag = '1'; #将上表与商品表连接起来，之后进行查询自连接
知识点
自连接作为一种特例，可以将一个表与它自身进行连接，称为自连接。
语法: 自连接语法和内外连接的语法一样，只不过换成了只在同一张表上面操作
特点: 特殊的地方就是左表和右表是同一张表,只是起了不同的别名示例
# 假设现在有一个区域表areas，里面是我国区域阶级，如下图所示北京市下属有几个区，每个区的pid是其上级区域
# 分析: 省市区三级都在一个表中,那么就可以使用自连接
# 需求1: 查询河北省所有的城市
# 自连接方式思路: 通过起别名把一个表(区域表)变成两个表(城市表,省级表)使用
#自连接，将表复制为两个表，一个取名city，一个起名province，进行关联，查找
SELECT *
FROM
areas city
JOIN areas province ON city.pid = province.id
WHERE
province.title = '河北省';
#查邯郸市下的区县
SELECT *
FROM
areas district
JOIN areas city on district.pid = city.id
where city.title='邯郸市';
<img alt="image-20230511203635933" loading="lazy">
来源:https://www.cnblogs.com/lionet-kk/p/mysql_03.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]