0基础跟德姆（dom）一起学AI 基础阶段 MySQL数据库04-多表详解

# -------------- 案例1: 多表建表 一对多关系 --------------
/*
外键约束介绍:
    概述:
        它是用来保证属于的 完整性 和 一致性的.
    添加外键约束的格式:
        场景1: 建表时添加.
            在外表中写: [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
        场景2: 建表后添加.
            alter table 外表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
    删除外键约束:
         alter table 外表名 drop foreign key 外键约束名;
    特点:
        外表的外键列 不能出现 主表的主键列 没有的数据.

MySQL属于 关系型数据库, 表与表之间是有关系的, 例如: 一对多, 多对多, 一对一....

一对多解释:
    例子:
        一个部门可以有多个员工, 1个员工只能属于1个部门.
        用户 和 订单
        ...
    建表原则:
        在 多的一方新建1列, 充当外键列, 去关联1的一方的主键列.
*/
# 1. 建库, 切库, 查表.
create database day03;
use day03;
show tables;

# 2. 新建部门表.
drop table dept;
create table dept(
    id int primary key auto_increment,  # 部门id
    name varchar(20)                    # 部门名称
);
# 3. 新建员工表.
drop table employee;
create table employee(
    id int primary key auto_increment,  # 员工id
    name varchar(10),                   # 员工姓名
    age int,                            # 员工年龄
    did int                             # 员工的部门id
    # ,constraint fk01 foreign key(did) references dept(id)        # 场景1: 建表时, 直接添加外键约束.
);

# 4. 添加外键约束. 场景2: 建表后, 添加约束.
# 格式: alter table 外表名 add [constraint 外键约束名] foreign key(外键列) references 主表名(主键列);
alter table employee add constraint fk_dept_emp foreign key(did) references dept(id);

# 5. 尝试往上述的两个表中添加数据.
# 往 部门表中添加数据
insert into dept values(null, '行政部'), (null, '研发部'), (null, '财务部'), (null, '人事部');

# 往 员工表添加数据.
insert into employee values(null, '乔峰', 31, 4);
insert into employee values(null, '鸠摩智', 50, 10);   # 没有 10号部门.

# 6. 查看表数据.
select * from dept;
select * from employee;

# 7.删除部门.
delete from dept where id = 1;      # 可以删.
delete from dept where id = 4;      # 不能删, 4部门有人, 即: 外表还在用这个值.

# 8. 演示删除外键约束.
# 格式: alter table 外表名 drop foreign key 外键约束名;
alter table employee drop foreign key fk_dept_emp;

# -------------- 案例2(扩展): 多表建表 多对多关系 --------------
/*
例如:
    学生 和 选修课.
    订单 和 商品
    ......
建表原则:
    1. 新建中间表, 该表至少有2列, 分别去关联多的两方的主键列.
    2. 如果中间表也想有自己的主键列, 则可以把它设置为: 伪主键.
    3. 需要设置 中间表的 两个外键列为 联合主键, 防止出现重复值的情况.
*/
# 1. 查看数据表.
show tables;
# 2. 创建 学生表 和 课程表.
create table student(
    sid int primary key auto_increment, # 学生id
    name varchar(10)                    # 学生姓名
);
create table course(
    cid int primary key auto_increment, # 课程id
    name varchar(10)                    # 课程名
);
# 3. 创建中间表.
create table stu_cur(
    id int not null unique auto_increment,  # 自身id, 伪主键(非空, 唯一) + 自增
    sid int,        # 学生id
    cid int        # 课程id
    # ,primary key(sid, cid)       # 设置 学生id, 课程id为: 联合主键.
);
# 4. 添加外键约束.
# 4.1 关联 中间表 和 学生表.
alter table stu_cur add constraint fk_mid_stu foreign key(sid) references student(sid);
# 4.2 关联 中间表 和 课程表.    演示写我们不指定外键约束的名字, 由系统自动生成.
alter table stu_cur add foreign key(cid) references course(cid);

# 5. 设置中间表的2个外键列为: 联合主键.
alter table stu_cur add primary key(sid, cid) ;
# 6. 尝试往上述的三张表中添加数据.
# 学生表
insert into student values(null, '张三'), (null, '李四'), (null, '王二麻子');
# 课程表
insert into course values(null, 'AI人工智能'), (null, 'Py大数据'), (null, '鸿蒙');
# 中间表
insert into stu_cur values(null, 1, 1), (null, 1, 2), (null, 2, 1);
insert into stu_cur values(null, 2, 1);     # 不行, 因为(2,1)已经存在了, 它们是 联合主键
# 7. 查看表数据.
# 学生表
select * from student;
# 课程表
select * from course;
# 中间表
select * from stu_cur;
# 8. 查看哪个学生学了什么课.
select
    s.name, c.name
from
    student s, stu_cur sc, course c
where
    s.sid = sc.sid and sc.cid = c.cid;

# -------------- 案例3(扩展): 多表建表 一对一关系 --------------
# 略.

# -------------- 案例4: 多表查询 准备动作 --------------
# 0. 切库.
use day03;
# 1. 建表.
# 创建hero表
create table hero(
    hid   int primary key auto_increment,   # 英雄id
    hname varchar(255),     # 英雄名字
    kongfu_id int           # 功夫id
);
# 创建kongfu表
create table kongfu(
    kid     int primary key auto_increment, # 功夫id
    kname   varchar(255)        # 功夫名
);

# 2. 添加表数据.
# 插入hero数据
insert into hero values(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
# 插入kongfu数据
insert into kongfu values(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');

# 3. 查看表数据.
select * from hero;
select * from kongfu;

/*
多表查询介绍:
    概述:
        多表查询的精髓是: 把多张表 按照条件 组合成"1张表", 然后进行 单表查询.
    分类:
        交叉查询:
            查询结果是: 两张表的笛卡尔积, 即: 表A的总数 * 表B的总数
        连接查询:
            内连接:   查询结果是: 两张表的交集.
            外连接:
                左外连接: 表A的全集 + 两张表的交集.
                右外连接: 表B的全集 + 两张表的交集.
        子查询:
            1个SQL语句的查询条件, 需要依赖另1个SQL语句的查询结果.
            里边的查询叫: 子查询.
            外边的查询叫: 父查询(主查询)
*/
# -------------- 案例5: 多表查询 交叉查询 --------------
# 格式: select * from A, B;
# 结果: 两张表的笛卡尔积, 即: 表A的总数 * 表B的总数
select * from hero, kongfu;

# -------------- 案例6: 多表查询 连接查询 --------------
# 场景1: 内连接 查询.
# 结果: 两张表的 交集.
# 格式1, 显式内连接.   select * from A inner join B on 关联条件 where ......;
select * from hero h inner join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h join kongfu kf on h.kongfu_id = kf.kid;        # 效果同上, 语法糖, inner可以省略不写.

# 格式2, 隐式内连接.   select * from A, B where 关联条件......;
select * from hero h, kongfu kf where h.kongfu_id = kf.kid;

# 场景2: 外连接 查询.
# 格式: 左外连接.   select * from A left outer join B on 关联条件 where ......;
# 查询结果 = 左表的全集 + 表的交集.
select * from hero h left outer join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h left join kongfu kf on h.kongfu_id = kf.kid;     # outer可以省略不写.

# 格式: 右外连接.   select * from A right outer join B on 关联条件 where ......;      # outer可以省略.
# 查询结果 = 右表的全集 + 表的交集.
select * from hero h right outer join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h right join kongfu kf on h.kongfu_id = kf.kid;     # outer可以省略不写.

# 细节: 左外和右外只需要掌握1个就行了, 因为: 右外连接, 把表顺序换一下, 结果和 左外连接效果是一样的.
select * from  kongfu kf right join hero h on h.kongfu_id = kf.kid;     # outer可以省略不写.

# -------------- 案例7: 多表查询 子查询 --------------
/*
概述:
    一个SQL语句的查询条件 需要依赖 另一个SQL语句的查询结果, 这种写法: 就称之为子查询.
    里边的叫: 子查询.  外边的叫: 父查询(主查询)
格式:
                主查询 | 父查询               子查询
    select * from 表名 where 列名 in (select 列名 from 表名...);
*/
# 需求: 查询学习了 "降龙十八掌" 武功的 英雄信息.
# 方式1: 分解版.
# step1: 先找到 降龙十八掌的 武功id.
select * from kongfu where kname = '降龙十八掌';     # 功夫id: 1

# step2: 去 英雄表中找 功夫id为1的 英雄信息.
select * from hero where kongfu_id = 1;

# 方式2: 合并版, 子查询写法.
select * from hero where kongfu_id = (select kid from kongfu where kname = '降龙十八掌');

# 方式3: 合并版, 连接查询.
select
    *
from
    hero h
join        # 内连接
    kongfu kf on h.kongfu_id = kf.kid
where
    kname = '降龙十八掌';

# -------------- 案例8: 多表查询 自关联(自连接)查询 --------------
# 1. 查看表数据.
select * from areas limit 100;

# 2. 查看河南省的信息.
select * from areas where id = '410000';

# 3. 查看河南省所有的市.
select * from areas where pid = '410000';

# 4. 查看 新乡市 所有的县区.
select * from areas where pid = '410700';

# 5. 自关联查询, 找到所有的 省, 市, 县区及其对应的关系.
select
    province.id, province.title,    # 省的信息
    city.id, city.title,            # 市的信息
    county.id, county.title         # 县区的信息
from
    areas as province
join
    areas as city on city.pid = province.id     # 市的父id = 省的id
join
    areas as county on county.pid = city.id;     # 县区的父id = 市的id

select
    province.id, province.title,    # 省的信息
    city.id, city.title,            # 市的信息
    county.id, county.title         # 县区的信息
from
    areas as province
join
    areas as city on city.pid = province.id     # 市的父id = 省的id
join
    areas as county on county.pid = city.id     # 县区的父id = 市的id
where
    county.id = '430502';

# -------------- 案例8: 扩展 case when --------------
/*
格式1:
    case
        when 条件1 then 值1
        when 条件2 then 值2
        ...
        else 值n
    end [as 别名]

格式2: 语法糖, 如果操作的是同1个列, 且都是等于的判断, 则语法格式可以写成如下的样子.
    case 字段名
        when 结果1 then 值1
        when 结果2 then 值2
        ...
        else 值n
    end [as 别名]

*/
# 1. 查看原表数据.
select * from day02.product;

# 2. 根据category_id, 转换成: category_name.
# 规则: c001 => 电脑, c002 => 衣服,  c003 => 护肤品, c004 => 零食, c005 => 饮品, null => 未知
select
    *,
    case
        when category_id = 'c001' then '电脑'
        when category_id = 'c002' then '衣服'
        when category_id = 'c003' then '护肤品'
        when category_id = 'c004' then '零食'
        when category_id = 'c005' then '饮品'
        else '未知'
    end as category_name
from
    day02.product;

# 语法糖写法.
select
    *,
    case category_id
        when 'c001' then '电脑'
        when 'c002' then '衣服'
        when 'c003' then '护肤品'
        when 'c004' then '零食'
        when 'c005' then '饮品'
        else '未知'
    end as category_name
from
    day02.product;