11-MySQL--外键（增加外键+修改/删除外键+外键的作用+外键的条件+外键约束）+联合查询+子查询（按位置分类：from/where/exists子查询+按结果分类：标量/列/行/表子查询）

最新推荐文章于 2024-08-06 15:46:06 发布

原创最新推荐文章于 2024-08-06 15:46:06 发布 · 619 阅读

2 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了MySQL中的外键概念、创建与删除、作用及约束条件，探讨了联合查询的基本语法和应用场景，并对子查询的分类、用法进行了深入解析。

一、外键

1、外键：foreign key。如果一张表中有一个字段（非主键）指向另外一张表的主键，将该字段称为外键

注：外键必须指向另外一张表，即和另外一张表产生联系

2、关系型数据库有两种约束，一种是约束实体内部字段与字段之间的关系，一种是约束实体与实体之间的关系（用外键来控制）

3、一张表可以有多个外键

4、增加外键

（1）外键可以在创建表的时候或者创建表之后增加

（2）在创建表的时候增加外键：在所有的表字段之后，使用"foreign key(外键字段) references 外部表(主键字段)"

注：创建表时增加外键只有一种方法，就是在所有字段之后增加外键。不能直接在外键字段后面加"foreign key references 外部表(主键字段)"

    -- 在创建表的时候增加外键
    create table my_foreign1(
        id int primary key auto_increment,
        name varchar(20) not null comment '学生姓名',
        c_id int comment '班级id',    -- 普通字段
        -- 增加外键
        foreign key(c_id) references my_class(id)
    )charset utf8;

    -- 查看表结构
    desc my_foreign1;

    -- 查看表创建语句
    show create table my_foreign1;

注：外键要求字段本身必须先是一个索引（普通索引）。如果字段本身没有索引，外键会先创建一个索引，然后才会创建外键本身

（3）在创建表之后增加外键：修改表结构

注：

a). 外键字段本身要先在表中存在，否则增加外键时会报错

b). 外键名字必须未被使用过，其他表也没有使用过才可以。所以，使用默认的外键名比较靠谱???

    -- 在创建表之后增加外键：修改表结构
    -- 外键字段本身要先存在
    -- 外键名字必须未被使用过，其他表也没有使用过才可以
    alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 外部表(主键字段);

    -- 创建表
    create table my_foreign2(
        id int primary key auto_increment,
        name varchar(20) not null comment '学生姓名',
        c_id int comment '班级id'    -- 普通字段
    )charset utf8;

    -- 查看表结构
    desc my_foreign2;
    
    -- 增加外键（在创建表之后增加外键：修改表结构）
    alter table my_foreign2 add constraint student_class_1 foreign key(c_id) references my_class(id);

    -- 查看表结构
    desc my_foreign2;

    -- 查看表创建语句
    -- 外键名字是"[constraint 外键名字]"中定义的"student_class_1"
    show create table my_foreign2;

5、修改外键&删除外键

（1）外键不可修改，只能先删除后新增

（2）删除外键

注：删除外键时，必须指定要删除的外键的名字。因此，创建外键时，最好自己指定一个外键名字。但不指定也有一个好处，就是外键名永远不会重复

    -- 删除外键
    -- 一张表中可以有多个外键，但是名字不能相同
    alter table 表名 drop foreign key 外键名;

    -- 查看表结构，查看外键字段
    desc my_foreign1;

    -- 删除外键
    alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;

    -- 查看表结构
    desc my_foreign1;

    -- 查看表创建语句
    -- 外键删除不能通过查看表结构体现，应该通过查看表创建语句查看
    show create table my_foreign1;

6、外键的作用

（1）外键默认的作用有两点：一个对子表（外键字段所在的表），一个对父表

（2）对子表的约束：子表数据进行写操作（增和改）时，如果对应的外键字段在父表找不到对应的主键匹配（即子表中的数据在父表中不存在），那么操作会失败（约束子表数据操作）

（3）对父表的约束：父表数据进行写操作（删和改：都必须涉及到主键本身）时，如果对应的主键在子表中已经被数据所引用，那么就不允许操作。如果父表的主键没有被子表引用，可以随意操作

7、外键的条件

（1）外键要存在，首先必须保证表的存储引擎是innodb（默认的存储引擎）。如果不是innodb存储引擎，外键可以创建成功，但是没有约束效果（不会有子表、父表的数据操作影响）

（2）外键字段的字段类型（列类型）必须与父表的主键类型完全一致。建议尽量严格一致（eg：都是unsigned等）

（3）一张表中的外键名字不能重复（在其他表中也不能重复）

（4）增加外键的字段（数据已经存在），必须保证子表的外键与父表的主键相对应

注：任何外键的创建失败，都不会指明具体是哪个位置失败了，只提示错误，不能创建这个外键。此时，要考虑是否满足上面2、3、4的条件要求

8、外键约束

（1）外键约束就是指外键的作用。前面讲的外键作用，是默认的作用。还可以通过对外键的需求，进行定制操作

（2）外键约束有三种约束模式（都是针对父表的约束）

a). district：严格模式（默认）。父表不能删除或者更新一个已经被子表数据引用的记录（父表的主键被子表的外键所引用）

b). cascade：级联模式。父表的主键更新/删除，子表对应的数据（外键）也跟着被更新/删除

注：此种情况，子表对应的外键字段不能设置为not null

c). set null：置空模式。父表的操作之后，子表对应的数据（外键）被置空

注：

a). 对父表来说，理论上只有两种操作：更新和删除。因为父表的新增不会影响子表

b). 子表的约束已经确定了，就是子表不能插入一个父表不存在的数据

（3）通常的一个合理的做法（约束模式）：删除时子表置空，更新时子表级联操作。即删除父表的主键所在的记录时，子表对应的外键置为空；更新父表的主键时，子表对应的外键一同更新

注：删除置空的前提条件：外键字段允许为空（如果不满足条件，外键无法创建）

    -- 删除时子表置空，更新时子表级联操作
    -- 删除置空的前提条件：外键字段允许为空
    foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;

    -- 创建外键，指定模式：删除时子表置空，更新时子表级联操作
    -- 删除置空的前提条件：外键字段允许为空
    create table my_foreign3(
        id int primary key auto_increment,
        name varchar(20) not null,
        c_id int,
        -- 增加外键
        foreign key(c_id) references my_class(id)
        -- 指定删除模式
        on delete set null
        -- 指定更新模式
        on update cascade
    )charset utf8;

    -- 查看表创建语句
    show create table my_foreign3;

    -- 更新操作：级联操作
    select * from my_foreign3;
    select * from my_class;

    -- 更新父表主键
    update my_class set id = 4 where id = 1;

    select * from my_class;
    select * from my_foreign3;


    -- 删除操作：置空
    select * from my_foreign3;
    select * from my_class;

    -- 删除父表主键
    delete from my_class where id = 3;

    select * from my_class;
    select * from my_foreign3;

（4）因为外键约束的存在，导致数据在维护的过程中，很有可能出现不可控的状态

eg：要删除一个主键，一张表的外键是级联或者置空模式（可以删除主键），但另一张表的外键是严格模式（不可以删除主键），会导致主键删除失败

二、联合查询

1、联合查询：将多次查询（多条select语句）在记录上进行拼接。字段不会增加，但记录数会增加

2、基本语法

    -- 联合查询
    select 语句1
    union [union选项]
    select 语句2
    ......

（1）联合查询由多条select语句构成，每一条select语句获取的字段数必须严格一致，但与字段类型无关

（2）union选项：与select选项一样有两个

a). distinct：去重（默认）。所有字段值都相同，该条记录才算重复

b). all：保留所有，不管重复

    -- 联合查询
    -- 默认去重
    select * from my_class
    -- 默认去重
    union 
    select * from my_class;

    -- 不去重
    select * from my_class
    -- 不去重
    union all
    select * from my_class;

3、联合查询只要求字段数一样，与数据类型无关

    -- 联合查询只要求字段数一样，与数据类型无关
    -- 保留第一张表的字段，后面的字段不管（只要字段数相同，不管类型）
    select id, name, room from my_class
    union all    -- 不去重
    select name, number, id from my_student;

4、联合查询的意义

（1）查询同一张表，但是需求不同（eg：查询学生信息，男生身高升序，女生身高降序）

注：分组不合要求，因为分组后每组只能保留一条记录

（2）多表查询。多张表的结构是完全一样的，保存的数据（结构）也是一样的

注：

a). 数据太多就要分表（垂直分表和水平分表），多张表综合统计（做报表等）就要用到联合查询

b). 垂直分表：按数据常用不常用分表。eg：将学生信息的常用字段放一张表，不常用字段放一张表

c). 水平分表：数据太多了分表。eg：十条记录分成两张表存储，一张表放五条记录

d). 垂直分表和水平分表都是为了提高效率

5、联合查询中order by的使用

    select * from my_student;

    -- 需求：男生年龄升序，女生年龄降序
    select * from my_student where sex = '男' order by age asc
    union 
    select * from my_student where sex = '女' order by age desc;

    -- 报错：Incorrect usage of UNION and ORDER BY

（1）在联合查询中，order by不能直接使用，需要对查询语句使用括号

    select * from my_student;

    -- 需求：男生年龄升序，女生年龄降序
    (select * from my_student where sex = '男' order by age asc)
    union 
    (select * from my_student where sex = '女' order by age desc);

    -- 结果有误：男生年龄没有升序

（2）在联合查询中，若要order by生效，必须搭配limit使用。limit使用限定的最大数即可

    select * from my_student;

    -- 需求：男生年龄升序，女生年龄降序
    (select * from my_student where sex = '男' order by age asc limit 9999999)
    union 
    (select * from my_student where sex = '女' order by age desc limit 9999999);

三、子查询

1、子查询：sub query。子查询是在某个查询结果之上进行的，从已经查出来的结果中去查结果（一条select语句内部包含了另外一条select语句）

2、子查询的分类

（1）子查询有两种分类方式：按位置分类和按结果分类

（2）按位置分类：子查询（select语句）在外部查询（select语句）中出现的位置

a). from子查询：子查询跟在from之后

b). where子查询：子查询出现在where条件中

c). exists子查询：子查询出现在exists里面

3、按结果分类：根据子查询得到的数据进行分类（理论上讲，任何一个查询得到的结果都可以理解为二维表）

a). 标量子查询：子查询得到的结果是一行一列

b). 列子查询：子查询得到的结果是一列多行

c). 行子查询：子查询得到的结果是多列一行（多行多列）

d). 表子查询：子查询得到的结果是多行多列

注：标量子查询、列子查询、行子查询出现的位置都是在where之后，表子查询出现的位置是在from之后

4、标量子查询

（1）标量子查询：子查询返回的结果是一行一列，当做一个字符串来使用（某个值）

5、列子查询

（1）列子查询：子查询返回的结果是一列多行，当做一个集合来使用（某个范围之内）

（2）条件

a). in

b). =any <--> in （any前加等号，等于in；any前加不等号，等于not in）

c). any <--> some （any跟some是一样的）

d). =some <--> =any <--> in

e). =all （全部）

6、行子查询

（1）行子查询：子查询返回的结果是多行多列（一行多列）

（2）

（3）行元素：行元素由多个字段构成

7、表子查询

（1）表子查询：子查询返回的结果是多行多列，当做二维表来使用

（2）表子查询：from 表子查询，得到的结果作为from的数据源（数据源是最先执行的）

8、exists子查询

（1）exists：是否存在的意思。exists子查询就是用来判断某些条件是否满足（跨表）。exists接在where之后，其返回的结果只有0和1

    -- exists子查询：用来判断某些条件是否满足（跨表）
    -- exists接在where之后，其返回的结果只有0和1
    select exists(select * from my_student);    -- 结果是：1
    select exists(select * from my_student where id = -1);    -- 结果是：0

    -- exists子查询
    -- exists接在where之后
    select * from my_student where exists(select * from my_class where id = 1);    -- where 1 = 1;
    select * from my_student where exists(select * from my_student where id = -1);    -- where 1 = 0;