MySQL让人又爱又恨的多表查询( 二 ) _多表查询

文章插图

4.4 全外连接很遗憾， MySQL不支持全外连接。
附录：测试数据SQL脚本-- auto-generated definitioncreate table student(idint auto_incrementprimary key,student_idintnull comment '学号',student_name varchar(40)null comment '姓名',family_idintnull comment '家庭ID',create_timedatetime default CURRENT_TIMESTAMP null comment '创建时间')comment '学生表';create table family(idint auto_incrementprimary key,family_namevarchar(40)null comment '家庭名称',family_address varchar(40)null comment '家庭地址',create_timedatetime default CURRENT_TIMESTAMP null comment '创建时间')comment '家庭表';create table achievement(idint auto_incrementprimary key,scoreintnull comment '分数',discipline_name varchar(40) null comment '学科名称',student_idintnull comment '学号')comment '成绩表';create table achievement_grade(idint auto_incrementprimary key,grade_tagvarchar(10)null comment '档次',lowest_scoreintnull comment '最低分',highest_score intnull comment '最高分',create_timedatetime 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);