软件测试-MySQL语言基础-2多表查询-知归博客

连接查询

1.基本概念

当查询结果来源多张表时，需要将多张表连接成一个大的数据集，再选择合适的结果返回。
连接查询可以通过连接运算符（连接条件）可以实现多个表查询。
内连接：查询的结果只显示两个表中满足连接条件的部分。
左连接：查询的结果为两个表匹配得到的数据，加左表特有的数据，对于右表中不存在的数据使用null填充。
右连接：查询的结果为两个匹配得到的数据，加右表特有的数据，对于左表中不存在的数据使用null填充。

2.内连接 inner join

2.1 标准语法

select * from 表1 inner join 表2 on 表1+匹配字段名 = 表2+匹配字段名;

创建表a和表b，并向其插入数据。

-- 如果表a存在则删除，不存在则不做任何操作
drop table if exists a;

-- 创建表a
create table a (
  id int,
  name varchar(10)
);

-- 向表a中插入数据
insert into a values(1,'张三'),(2,'李四'),(3,'王五');

select * from a;

-- 如果表b存在则删除，不存在则不做任何操作
drop table if exists b;

-- 创建表b
create table b(
 id int,
 score int
);

-- 向表b中插入数据
insert into b values(1,30),(1,40),(2,50),(2,60);

表a和表b数据展示。
表a和表b通过字段id内连接查询后的结果。
- select * from a inner join b on a.id = b.id;
查询结果列的说明：
- 表a和表b的列都会出现在查询结果中，由于表a和表b都有列名叫id 的字段，为了区分，表b 的id列显示为id1.
查询结果行的说明：
- 记录只保留表a 和表 b 中id字段相同的记录。

students表和scores表的数据说明

20250501151854232-image

查询学生信息和学生的成绩，只显示有成绩的学生。

select * from students inner join scores on students.studentNo = scores.studentNo;
查询结果只显示students 和scores两个表中studentNo相同的部分。

2.2隐式链接

select * from 表1,表2 where 表1.字段 = 表2.字段;

查询学生的信息和成绩，只显示有成绩的学生。

select * from students,scores where students.studentNo = scores.studentNo;

2.3选择部分字段

查询学生信息，要求只显示姓名、课程、成绩。

标准语法：
- select name,courseNo,score from students inner join scores on students.studentNo = scores.studentNo;
隐式链接：
- select name,courseNo,score frmo where students.studentNo = scores.studentNo;

2.4表的别名

查询学生信息，要求只显示姓名、课程号、成绩，其中表students别名stu，表scores别名sc。

select name,courseNo,score from students as stu inner join scores as sc on stu.studentNo = sc.studentNo;

— 练习

查询成绩表信息，同时显示成绩对应的课程表。

select * from students inner join scores,courses on students.studentNo = scores,studentNo and scores.courseNo = courese.course.NO;

2.5带有wuere条件的内连接

语法格式
- select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件;

查询王昭君的信息，要求只显示姓名，课程号，成绩。

select name,courseNo,score from students inner join scores on students.studentNo = scores.studentNo where students.name = ‘王昭君’;

2.6带有and逻辑运算符的内连接查询

查询姓名为王昭君并且成绩小于90的信息，要求只显示姓名，成绩。

select name 姓名,score 成绩 from students
inner join scores on students.studentNo = scores.studentNo
where students.name = ‘王昭君’ and scores.score < 90;

2.7多表内连接查询

查询学生信息和成绩以及成绩对应的课程名称。

select * from students
inner join scores on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = courses.courseNo;

— 练习

写sql三步法：

搭建框架
- 基本的select语句框架搭建起来，如果有多表，把相应的多表也联合起来。
看条件
- 决定where后面的具体条件。
显示的字段
- select 后面到底要显示什么字段。

查询所有学生的Linux课程成绩，要求只显示姓名，成绩，课程名称。

select name,score,coursename from students
inner join scores on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = courses.courseNo
where courses.coursename = ‘Linux’;

2.8带有order by 排序的内连接查询

查询成绩最高的男生信息，要求只显示姓名，课程名，成绩。

select name , coursename ,score from students
inner join scores on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = courses.courseNo
where students.sex = ‘男’
order by scores.score desc
limit 1;

3.左连接 left join

语法格式
- select * from 表1
  left join 表2 on 表1.字段 = 表2.字段；

20250501172913591-image

查询所有学生的信息以及成绩，包括没有成绩的学生。

students 表中有些学生在scores表中没有成绩；
左连接查询的结果为students和scores两个表匹配得到的数据，加上students表特有的数据，对于students表中不存在的数据使用null填充。

4.右连接 right join

语法格式
- select * from 表1
  right join 表2 on 表1.字段 = 表1.字段;

查询所有课程的信息，包括没有成绩课程。

courses表中courseNo为7的课程 python 在scores 表中没有对应的成绩；
右连接查询的结果为 scores 和courses 两个表匹配得到的数据，加courses表特有的数据，对于scores表中不存在的数据使用null填充。
- select * from scores right join courses on scores.courseNo = courses.couseNo;