乐趣区

关于mysql:MySQL让人又爱又恨的多表查询

jiezi

1. 前言

在 SQL 开发当中，多表联查是相对绕不开的一种技能。同样的查问后果不同的写法其运行效率也是千差万别。

在理论开发当中，我见过（如同还写过~）不少又长又臭的查问 SQL，数据量一上来查个十几分钟那是粗茶淡饭。

因而，深刻了解 SQL 的多表查问机制，少写一些慢查问，应该能够少挨点骂。

2. 等值连贯和非等值连贯

2.1 等值连贯

等值连贯是在多表查问中最根底，也最简略的一种，其值为所有满足条件的笛卡尔积。

在 from 前面，哪个表写在后面后果中哪个表的值就先呈现，如下：

select *
from student,
     family
where student.family_id = family.id;

阿里在最新公布的 Java 开发手册中强制要求，只有波及多个表，必须在列名前加表的别名（或表名）进行限定

2.2 非等值连贯

非等值连贯是通过 a 表中的值在 b 表中的某一个范畴来进行的，可能很好的满足预设定好的分段统计需要。

非等值连贯有两种写法，应用between…and… 或大于号小于号

-- 第一种写法：应用 between...and...
select a.discipline_name, a.score, b.grade_tag
from achievement a,
     achievement_grade b
where a.score between b.lowest_score and b.highest_score;

-- 第二种写法，应用 >= 或 <=
select a.discipline_name, a.score, b.grade_tag
from achievement a,
     achievement_grade b
where a.score >= b.lowest_score
  and a.score <= b.highest_score;

3. 自连贯和非自连贯

3.1 自连贯

自连贯，顾名思义就是同一张表本人跟本人连贯，为了辨别须要给表取不同的别名。如一张成绩表，须要查问所有分数比“语文”高的数据：

若 不应用自连贯，须要先通过查问语文的分数，而后再查问大于这个分数的数据。

具体能够按如下步骤进行查问：

-- 先查问语文的分数
select score from achievement where discipline_name = '语文';

-- 再查问分数比语文分数更高的数据
select * from achievement where score > 76;

而 应用自连贯，则能够在一条 sq 语句里实现查问：

select a.*
from achievement a,
     achievement b
where b.discipline_name = '语文'
  and a.score > b.score;

3.2 非自连贯

除自连贯外，其余的都叫非自连贯~~~

4. 内连贯和外连贯

内连贯和外连贯的辨别实质上是另一种分类办法，如内连贯就是等值连贯。

  • 内连贯：合并具备同一列的两个或两个以上的表的行, 后果集中不蕴含一个表与另一个表不匹配的行

  • 外连贯：两个表在连贯过程中除了返回满足连贯条件的行以外 还返回左（或右）表中不满足条件的

    行，这种连贯称为左（或右）外连贯。没有匹配的行时, 后果表中相应的列为空(NULL)。

    • 左外连贯：连贯条件中右边的表也称为 主表 ，左边的表称为 从表。
    • 右外连贯：连贯条件中左边的表也称为 主表 ，右边的表称为 从表。
    • 全外连贯

4.1 测试数据

测试用学生表 student 和家庭表 family 数据如下：

4.2 左外连贯

-- 查出 student 中的所有数据，不满足的显示为 null
-- 这里 student 在后面
select a.*
from student a
         left join family b on a.family_id = b.id

4.3 右外连贯

-- 查出 student 中的所有数据，不满足的显示为 null
-- 这里 student 在前面
select a.*
from family b
         right join student a on b.id = a.family_id;

4.4 全外连贯

很遗憾，MySQL 不反对全外连贯。

附录：测试数据 SQL 脚本

-- auto-generated definition
create table student
(
    id           int auto_increment
        primary key,
    student_id   int                                null comment '学号',
    student_name varchar(40)                        null comment '姓名',
    family_id    int                                null comment '家庭 ID',
    create_time  datetime default CURRENT_TIMESTAMP null comment '创立工夫'
)
    comment '学生表';

create table family
(
    id             int auto_increment
        primary key,
    family_name    varchar(40)                        null comment '家庭名称',
    family_address varchar(40)                        null comment '家庭地址',
    create_time    datetime default CURRENT_TIMESTAMP null comment '创立工夫'
)
    comment '家庭表';

create table achievement
(
    id              int auto_increment
        primary key,
    score           int         null comment '分数',
    discipline_name varchar(40) null comment '学科名称',
    student_id      int         null comment '学号'
)
    comment '成绩表';

create table achievement_grade
(
    id            int auto_increment
        primary key,
    grade_tag     varchar(10)                        null comment '品位',
    lowest_score  int                                null comment '最低分',
    highest_score int                                null comment '最高分',
    create_time   datetime default CURRENT_TIMESTAMP null comment '创立工夫'
)
    comment '分数品位表';

INSERT INTO achievement_grade (id, grade_tag, lowest_score, highest_score, create_time) VALUES (1, '不及格', 0, 60, '2022-03-02 11:44:01');
INSERT INTO achievement_grade (id, grade_tag, lowest_score, highest_score, create_time) VALUES (2, '良好', 60, 80, '2022-03-02 11:44:01');
INSERT INTO achievement_grade (id, grade_tag, lowest_score, highest_score, create_time) VALUES (3, '优良', 80, 100, '2022-03-02 11:44:01');

INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (1, 1, '张三', 1, '2022-03-02 09:55:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (2, 2, '李四', 2, '2022-03-02 09:55:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (3, 3, '王五', 3, '2022-03-02 09:55:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (4, 4, '高飞', null, '2022-03-02 19:45:14');

INSERT INTO family (id, family_name, family_address, create_time) VALUES (1, '张三家', '北京', '2022-03-02 09:54:13');
INSERT INTO family (id, family_name, family_address, create_time) VALUES (2, '李四家', '上海', '2022-03-02 09:54:13');
INSERT INTO family (id, family_name, family_address, create_time) VALUES (3, '王五家', '西伯利亚', '2022-03-02 09:54:13');

INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (1, 76, '语文', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (2, 80, '数学', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (3, 65, '英语', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (4, 98, '天文', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (5, 77, '历史', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (6, 69, '生物', 1);

微信搜寻：『深海云帆』关注我的众号

或者加我微信：hqzmss，拉你入技术交换群

退出移动版