多表查询过滤重复数据_数据分析之数据库的多表查询

最新推荐文章于 2024-08-28 03:56:16 发布

weixin_39670246

最新推荐文章于 2024-08-28 03:56:16 发布

阅读量609

点赞数 1

文章标签：多表查询过滤重复数据

表的加法——UNION

什么是表的加法？

将两个表的所有数据合并在一起，重复项仅保留一项

2. 文氏图

3. 示例

SELECT `课程号`,`课程名称` 
FROM course
UNION
SELECT `课程号`,`课程名称` 
FROM course1;

【运行结果】

UNION

4. 使用UNION对表进行合并，但需要保留重复行时，使用UNION ALL

示例：

SELECT `课程号`,`课程名称` 
FROM course
UNION ALL
SELECT `课程号`,`课程名称` 
FROM course1;

【运行结果】

UNION ALL

表的联结

要对个表进行联结，需要先理清各表之前的关系，以下为数据库中四个表的连接关系图

联结关系图

交叉联结CORSS JOIN（又称：笛卡尔积）

1）定义：

将表中的每一行都和另一个表中的每一行联结在一起

2）交叉联结示例：

交叉联结定义

3）使用场景：

交叉联结的使用场景少，因为需要处理的数据量大会占用比较大的内存，但是交叉联结是其他联结的基础，其他联结是在交叉联结上过滤所得到的效果。

2. 内联结INNER JOIN

1）文氏图

2）运行逻辑：找出对应条件中的字段相同的数据，将两个表中该字段对应的每行数据提取出来，然后将两个表进行交叉联结。

3）示例：

SELECT a.`学号`,a.`姓名`,b.`课程号`
FROM student AS a INNER JOIN score AS b
ON a.`学号`=b.`学号`;

等价于：

SELECT a.`学号`,a.`姓名`,b.`课程号`
FROM student AS a, score AS b
where a.`学号`=b.`学号`;

【运行结果】

3. 左联结 LEFT JOIN

1）文氏图

2）运行逻辑：将左侧的表作为主表，将所有数据取出来，右边的表取出符合条件的行，并将取出的数据进行交叉联结，右表取出来的数据，如果左表没有对应的行，则设为空值

3）示例：

SELECT a.`学号`,a.`姓名`,b.`课程号`
FROM student AS a LEFT JOIN score AS b
ON a.`学号`=b.`学号`;

【运行结果】

4）左联结的基础上去掉公共部分

a.文氏图

b.示例：

SELECT a.`学号`,a.`姓名`,b.`课程号`
FROM student AS a LEFT JOIN score AS b
ON a.`学号`=b.`学号`
WHERE b.`学号`is NULL;

【运行结果】

4. 右联结RIGHT JOIN

1）文氏图

2）运行逻辑

将右侧的表作为主表，将所有数据取出来，左边的表取出符合条件的行，并将取出的数据进行交叉联结，左表取出来的数据，如果右表没有对应的行，则设为空值。

3）示例：

SELECT a.`学号`,a.`姓名`,b.`课程号`
FROM student AS a RIGHT JOIN score AS b
ON a.`学号`=b.`学号`;

【运行结果】

4）右联结的基础上去掉公共部分

a.文氏图

b.示例：

SELECT a.`学号`,a.`姓名`,b.`课程号`
FROM student AS a RIGHT JOIN score AS b
ON a.`学号`=b.`学号`
WHERE a.`学号` is NULL;

【运行结果】

5. 全联结 FULL JOIN

1）文氏图

2）运行逻辑

返回左表和右表的所有行，无对应值时用空值填充

3）注意

mysql中不支持全联结 FULL JOIN

5. 联结总结图

6. 加入联结后的运行顺序

7. 练习

1）查询所有学生的学号、姓名、选课数、总成绩

【解题】

学号、姓名-学生表中

选课数-课程表中按照学号分类，并对课程号进行计数

总成绩-成绩表中按照学号分类，对成绩求和

【分析】

SELECT 查询结果[学号、姓名、选课数、总成绩]

FROM 从哪张表中查数据[[学号、姓名-学生表，选课数、总成绩-成绩表，左联结]

WHERE [没有]

GROUP BY [学号]

HAVING [没有]

ORDER BY [没有]

LIMIT [没有]

【答案】

SELECT a.`学号`,a.`姓名`,COUNT(b.`课程号`) AS 课程数,SUM(b.`成绩`) AS 总成绩
FROM student AS a LEFT JOIN score AS b
ON a.`学号`=b.`学号`
GROUP BY a.`学号`;

【运行结果】

2）查询平均成绩大于85的所有学生的学号、姓名和平均成绩

【解题】

学号、姓名-学生表中

平均成绩数-课程表中按照学号分类，求学生的平均成绩

【分析】

SELECT 查询结果[学号、姓名、平均成绩]

FROM 从哪张表中查数据[[学号、姓名-学生表，平均成绩-成绩表，左联结]

WHERE [没有]

GROUP BY [学号]

HAVING [成绩>15]

ORDER BY [没有]

LIMIT [没有]

【答题】

SELECT a.`学号`,a.`姓名`,AVG(b.`成绩`) AS 平均成绩
FROM student AS a LEFT JOIN score AS b
ON a.`学号`=b.`学号`
GROUP BY a.`学号`
HAVING AVG(b.`成绩`)>85;

【运行结果】

3）查询学生的选课情况：学号，姓名，课程号，课程名称

【解题】

学号、姓名-学生表

课程号-成绩表

课程名称-课程表

学生表与成绩表用学号进行左联结，获得学号、姓名、课程号

再用上面的结果与课程表进行左联结，拿到课程名称

【分析】

SELECT 查询结果[学号，姓名，课程号，课程名称]

FROM 从哪张表中查数据[（学号、姓名-学生表，课程号-成绩表）内联结，课程名称-课程表，内联结]

WHERE [没有]

GROUP BY [学号]

HAVING [没有]

ORDER BY [没有]

LIMIT [没有]

【答案】

SELECT a.`学号`,a.`姓名`,b.`课程号`,c.`课程名称`
FROM student AS a INNER JOIN score AS b ON a.`学号`=b.`学号` 
INNER JOIN course AS c ON b.`课程号`=c.`课程号`;

【运行结果】

CASE表达式

定义

条件判断函数，判断每一行是否满足格式

case when <判断表达式> when <表达式>

<判断表达式> when <表达式>
<判断表达式> when <表达式>

...

else <表达式>

end

2. 运行逻辑

将表中的每一行的数据拿出来，从上往下与判断表达式进行比较，符合条件时执行对应的表达式，直接完成后执行end结束，再取出表中的下一行数据，一直到数据读取完毕。

3. 示例：查询出每门课程的及格人数和不及格人数

SELECT `课程号`,
SUM(CASE WHEN `成绩` >=60 THEN 1
	ELSE 0
	END) AS 及格人数,
SUM(CASE WHEN `成绩` <60 THEN 1
	ELSE 0
	END) AS 不及格人数
FROM score
GROUP BY `课程号`

【运行逻辑】

【运行结果】

4. 注意事项

1）else可以省略，省略时默认为空值

2）end可以省略

3）case语句可以放在任意子句中

5. 练习

1）使用分段[100-85],[85-70],[70-60],[<60]

来统计各科成绩，分别统计：

各分数段人数，课程号和课程名称

【分析】

各分数段人数，课程号在成绩表，课程名称在课程表

先将左联结课程表，找出课程号对应的课程名称

再按照课程号、课程名称分组，并将成绩类成不同分数段，使用case统计人数

【答案】

SELECT a.`课程号`,b.`课程名称`,
SUM(CASE WHEN a.`成绩` BETWEEN 85 AND 100 THEN 1
	ELSE 0
END) AS '[100-85]',
SUM(CASE WHEN a.`成绩` >=70 THEN 1
	ELSE 0
END) AS '[85-70]',
SUM(CASE WHEN a.`成绩` >=60 THEN 1
	ELSE 0
END) AS '[70-60]',
SUM(CASE WHEN a.`成绩` <60 THEN 1
	ELSE 0
END) AS '[<60]'
FROM score AS a RIGHT JOIN course AS b
ON a.`课程号`=b.`课程号`
GROUP BY a.`课程号`,b.`课程名称`

【运行结果】