通过对 DQL 的学习, 可以很轻松的从一张数据表中查询出需要的数据; 在企业应用开发中, 经常需要从多表中查 询数据 (例如: 查询学生信息的时需要同时查询学生的班级信息), 可以通过连接查询从多张数据表提取数据:

在 MySQL 中可以使用 join 实现多表的联合查询——连接查询, join 按照其功能不同分为三个操作:

  • inner join 内连接
  • left join 左连接
  • right join 右连接

数据准备

创建数据表

创建班级信息表和学生信息表

  1. create table classes(
  2.     class_id int primary key auto_increment,
  3.     class_name varchar(40) not null unique,
  4.     class_remark varchar(200)
  5. );
  7. create table students(
  8.     stu_num char(8) primary key,
  9.     stu_name varchar(20) not null,
  10.     stu_gender char(2) not null,
  11.     stu_age int not null,
  12.     cid int,
  13.     constraint FK_STUDENTS_CLASSES foreign key(cid)
  14.     references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE
  15. );

添加数据

添加班级信息

  1. # Java2104 包含三个学生信息
  2. insert into classes(class_name, class_remark) values('Java2104', '...');
  4. # Java2105 包含两个学生信息
  5. insert into classes(class_name, class_remark) values('Java2105', '...');
  7. # 以下两个班级在学生表中没有对应的学生信息
  8. insert into classes(class_name, class_remark) values('Java2106', '...');
  9. insert into classes(class_name, class_remark) values('Python2105', '...');

添加学生信息

  1. # 以下三个学生信息 属于 class_id = 1 的班级 (Java2104)
  2. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  3. values('20210101', '张三', '男', 20, 1);
  4. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  5. values('20210102', '李四', '⼥', 20, 1);
  6. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  7. values('20210103', '王五', '男', 20, 1);
  9. # 以下三个学生信息 属于 class_id = 2 的班级 (Java2105)
  10. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  11. values('20210104', '赵柳', '⼥', 20, 2);
  12. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  13. values('20210105', '孙七', '男', 20, 2);
  15. # 小红和小明没有设置班级信息
  16. insert into students(stu_num, stu_name, stu_gender, stu_age)
  17. values('20210106', '小红', '⼥', 20);
  18. insert into students(stu_num, stu_name, stu_gender, stu_age)
  19. values('20210107', '小明', '男', 20);

内连接 INNER JOIN

语法

  1. select ... from tableName1 inner join tableName2 ON 匹配条件 [where 条件];

笛卡尔积

  • 笛卡尔积 ( A 集合 & B 集合) 使用 A 中的每个记录一次关联 B 中每个记录, 笛卡尔积的总数 = A 总数 * B 总数
  • 如果直接执行 select ... from tableName1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积 (依次使用 tableName1 表中的每一条记录去匹配 tableName2 的每条数据)

内连接条件

两张表时用 inner join 连接查询之后产生的笛卡尔积数据中很多数据都是无意义的, 如何消除无意义的数据?
—— 添加两张进行连接查询时的条件

  • 使用 on 设置两张表连接查询的匹配条件
  1. # 使用 where 设置过滤条件: 先生成笛卡尔积再从笛卡尔积中过滤数据 (效率很低)
  2. select * from students INNER JOIN classes where students.cid = classes.class_id;
  4. # 使用 ON 设置连接查询条件: 先判断连接条件是否成立, 如果成立两张表的数据进行组合生成一条结果记录
  5. select * from students INNER JOIN classes ON students.cid = classes.class_id;
  • 结果: 只获取两种表中匹配条件成立的数据, 任何一张表在另一种表如果没有找到对应匹配则不会出现在查询结果中 (例如: 小红和小明没有对应的班级信息, Java2106 和 Python2106 没有对应的学生)

左连接 LEFT JOIN

需求: 请查询出所有的学生信息, 如果学生有对应的班级信息, 则将对应的班级信息也查询出来

左连接: 显示左表中的所有数据, 如果在有右表中存在与左表记录满足匹配条件的数据, 则进行匹配；如果右表中不存在匹配数据, 则显示为 Null

语法

  1. select ... from tableName1 LEFT JOIN tableName2 ON 匹配条件 [where 条件];

示例

  1. # 左连接: 显示左表中的所有记录
  2. select * from students LEFT JOIN classes ON students.cid = classes.class_id;

右连接 RIGHT JOIN

语法

  1. select ... from tableName1 RIGHT JOIN tableName2 ON 匹配条件 [where 条件];

示例

  1. # 右连接: 显示右表中的所有记录
  2. select * from students RIGHT JOIN classes ON students.cid = classes.class_id;

数据表别名

如果在连接查询的多张表中存在相同名字的字段, 可以使用 表名.字段名 来进行区分, 如果表名太长则不便于 SQL 语句的编写, 可以使用数据表别名

使用示例

  1. select s.*, c.class_name
  2. from students s
  3. INNER JOIN classes c
  4. ON s.cid = c.class_id;

子查询/嵌套查询

子查询 — 先进行一次查询，第一次查询的结果作为第二次查询的源/条件 (第二次查询是基于第一次的查询结果来进行的)

子查询返回单个值 - 单行单列

案例 1: 查询班级名称为 Java2104 班级中的学⽣信息 (只知道班级名称，而不知道班级 ID)

  • 传统的方式：
  1. # a. 查询 Java2104 班的班级编号
  2. select class_id from classes where class_name = 'Java2104';
  4. # b. 查询此班级编号下的学⽣信息
  5. select * from students where cid = 1;
  • 子查询
  1. # 如果⼦查询返回的结果是⼀个值 (单列单⾏), 条件可以直接使⽤关系运算符 (= != ....)
  2. select * from students where cid = (
  3.     select class_id from classes where class_name = 'Java2105');

子查询返回多个值 - 多行单列

案例 2: 查询所有 Java 班级中的学生信息

  • 传统的方式:
  1. # a. 查询所有 Java 班的班级编号
  2. select class_id from classes where class_name LIKE 'Java%';
  4. # b. 查询这些班级编号中的学⽣信息 (union 将多个查询语句的结果整合在⼀起)
  5. select * from students where cid = 1
  6. UNION
  7. select * from students where cid = 2
  8. UNION
  9. select * from students where cid = 3;
  • 子查询
  1. # 如果⼦查询返回的结果是多个值 (单列多⾏), 条件使⽤ IN / NOT IN
  2. select * from students where cid IN (
  3.     select class_id from classes where class_name LIKE 'Java%');

子查询返回多个值 - 多行多列

案例 3: 查询 cid = 1 的班级中性别为男的学生信息

  1. # 多条件查询：
  2. select * from students where cid = 1 and stu_gender = '男';
  4. # ⼦查询: 先查询 cid = 1 班级中的所有学⽣信息, 将这些信息作为⼀个整体虚拟表 (多⾏多列)
  5. # 再基于这个虚拟表查询性别为男的学⽣信息 ('虚拟表' 需要别名)
  6. select * from (select * from students where cid = 1) t where t.stu_gender = '男';