MySQL外键约束和多表查询，特点2:从表外键类型

外键约束和多表查询

一、外键是什么

图解

知识点

外键: 多个表之间的关联字段

特点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 [inner] 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 [outer] join 表2 on 条件
右外连接关键字:表1 right [outer] 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 left  join 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='邯郸市';