高级数据查询语句-多表联查

标签：语句 classid 多表 -- student sc 联查 class SELECT

一、多表联查

多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。

1.内联查询

只有完全满足条件(主外键关系)的数据才能出现的结果

1.1 非等值查询

语法：非等值查询：SELECT * FROM 表1，表2

注意：

非等值联查：笛卡尔积逻辑上有错误，表中标红的一行数据是有错误的，班级编号为1，但是显示数据在2班

1.2 等值查询

语法：SELECT * FROM 表1，表2 WHERE 表1.字段1 = 表2.字段2...

-- 等值联查
-- 查询出学生和班级信息 student class
SELECT * FROM student,class 
WHERE student.classid=class.classid

1.3 五张表全部联查出来

-- 5张表全部联查(等职联查)
SELECT * FROM student,class,sc,teacher,course 
WHERE student.classid=class.classid 
AND student.Sid = sc.Sid 
AND sc.Cid = course.Cid 
AND course.Tid = teacher.Tid

1.4 面试题

-- 查询出学过张三老师课程的学生信息(面试)
SELECT * FROM student,teacher,course,sc
WHERE student.Sid = sc.Sid
AND sc.Cid = course.Cid
AND course.Tid = teacher.Tid
AND teacher.Tname = '张三'

-- 查询每个学生的平均成绩 学生姓名，班级名称，平均成绩
SELECT student.Sname,class.classname,AVG(score) FROM student,sc,class
WHERE student.Sid = sc.Sid
AND student.classid = class.classid
GROUP BY student.Sid

1.5 inner join on

-- 通过第一张表的结果进行on条件匹配
-- 优点：表少，每张表的数据大，内存占用小，IO高（每次拿表，都要做判断）效率高

-- 查找出男生的班级信息
SELECT * FROM student 
INNER JOIN class ON student.classid = class.classid 
WHERE ssex = '男'

---------上下效果相同---------

SELECT * FROM student,class 
WHERE student.classid = class.classid
AND student.ssex = '男'

-- 3表联查
SELECT * FROM student
INNER JOIN class ON student.classid = class.classid
INNER JOIN sc ON student.Sid = sc.Sid

-- 5表联查
SELECT * FROM student
INNER JOIN class ON student.classid = class.classid
INNER JOIN sc ON student.Sid = sc.Sid
INNER JOIN course ON sc.Cid = course.Cid
INNER JOIN teacher ON teacher.Tid = course.Tid;

-- 每门课程的平均成绩 课程名称 代课老师姓名 平均成绩
SELECT course.Cname,teacher.Tname,AVG(score) FROM sc
INNER JOIN course ON course.cid = sc.cid
INNER JOIN teacher ON teacher.Tid = course.Tid
GROUP BY course.Cid

1.6 等值联查与inner join on 的区别

等职查询：

先拿出所有的结果再筛选
适合表的个数多，但是每个表的数据量不大，吃内存但是 IO小

inner join on:

通过第一张表的结果集进行on后面的结果匹配，符合条件的放到结果集里面
适合表少，但是每张表的数据大，内存占用小，IO高

2.外联查询

找到主查表（分清楚主要查什么）
以小表驱动大表

2.1 left join on 左外联主表在join的左边

-- 所有学生的数据和对应的班级信息
-- left jion on 左外联
SELECT * FROM student 
LEFT JOIN  class ON student.classid = class.classid

2.2 right join on 右外联主表在join的右边

-- right join on 右外联
SELECT * FROM class 
RIGHT JOIN student ON student.classid = class.classid

-- 查询出所有的学生学过多少门课程   学生名字 课程数 *****
SELECT student.Sname,COUNT(Cid) FROM student 
LEFT JOIN  sc ON student.Sid = sc.Sid
GROUP BY student.Sid;

-- 没有班级的学生
SELECT * FROM student
LEFT JOIN class ON student.classid = class.classid
WHERE class.classid IS NULL;

-- 没有学生的班级
SELECT * FROM student
RIGHT JOIN class ON student.classid = class.classid
WHERE student.Sid IS NULL;

2.3 union

注意：

union 两个结果集的并集
union 去除重复与distinct 一样
不同类型的字段是可以合并的
不同列数量的结果集不允许合并
起别名给第一个结果集才有用

-- 库中的所有人的名字
SELECT Sname FROM student
UNION
SELECT Tname FROM teacher

-- 获取没有学生的班级和没有班级的学生的数据
SELECT * FROM student
LEFT JOIN class ON student.classid = class.classid
WHERE class.classid IS NULL
UNION
SELECT * FROM student
RIGHT JOIN class ON student.classid = class.classid
WHERE student.Sid IS NULL;

获取没有班级的学生，班级和学生都有，没有学生的班级

-- 全连接
-- 获取没有班级的学生，班级和学生都有，没有学生的班级
SELECT * FROM student
LEFT JOIN class ON student.classid = class.classid
UNION  -- 去重
SELECT * FROM student
RIGHT JOIN class ON student.classid = class.classid

获取没有班级的学生和班级和学生都有的还要获取没有学生的班级(不去重的并集)

-- 不去重的数据
SELECT * FROM student
LEFT JOIN class ON student.classid = class.classid
UNION ALL  -- 不去重
SELECT * FROM student
RIGHT JOIN class ON student.classid = class.classid

3.子查询

子查询，又叫内部查询

3.1 where 型子查询

查询id最大的一个学生(使用排序+分页实现)

-- 去查询id最大的一个学生
SELECT * FROM student ORDER BY sid DESC LIMIT 1

查询id最大的一个学生(子查询)

-- 查询id最大的一个学生(子查询)
SELECT MAX(student.Sid) FROM student

所有的查询必须用小括号括起来
效率极低

查询每个班下id最大的学生(使用where子查询实现)
SELECT * FROM student
LEFT JOIN class ON 
student.classid = class.classid
WHERE sid 
IN(SELECT MAX(Sid) FROM student GROUP BY classid)

查询学过张三老师课程的学生

-- 查询学过张三老师课程的学生

SELECT * FROM teacher WHERE Tname = '张三'   -- ???

SELECT * FROM student WHERE sid IN(
	SELECT sid FROM sc WHERE cid = (
		SELECT cid FROM course WHERE tid =
			(SELECT tid FROM teacher WHERE tname = '张三')
		)
);

查询没有学过张三老师课程的学生(反向过滤*****)

-- 查询没有学过张三老师课程的学生(反向过滤*****) 

SELECT * FROM student WHERE Sid NOT IN(
SELECT Sid FROM student WHERE Sid IN(
	SELECT Sid FROM sc WHERE Cid = (
		SELECT Cid FROM course WHERE Tid =
			(SELECT Tid FROM teacher WHERE Tname = '张三')
		)
))

3.2 from 子查询查询结果将作为一张表使用

查询大于5人的班级名称和人数(不使用子查询)

-- 查询大于5人的班级名称和人数（不使用子查询）
SELECT classname,COUNT(*) FROM class
LEFT JOIN student ON class.classid = student.classid
GROUP BY class.classid HAVING COUNT(*) > 5

查询大于5人的班级名称和人数（子查询）

-- 查询大于5人的班级名称和人数（子查询）-- ******************************************************
SELECT classname, 人数 FROM class LEFT JOIN
(SELECT classid,COUNT(*) 人数 FROM student GROUP BY classid) t1
ON class.classid = t1.classid
WHERE 人数 > 5

3.3 exists 子查询

子句有结果，父句执行，子句没结果，父句不执行

-- exists 子查询 子句有结果，父句执行，子句没结果，父句不执行
SELECT * FROM teacher
WHERE EXISTS (SELECT * FROM student WHERE classid = 1)

3.4 any(推荐新版本的any)\some(旧版本)子查询

题：查询出一班成绩比二班最低成绩高的学生

-- 题：查询出一班成绩比二班最低成绩高的学生
SELECT DISTINCT student.* FROM sc
LEFT JOIN student ON sc.Sid = student.Sid
WHERE student.classid = 1 AND score >ANY(
SELECT score FROM sc
LEFT JOIN student ON sc.Sid = student.Sid
WHERE student.classid = 2
)         -- some和替换any

SELECT DISTINCT student.* FROM sc
LEFT JOIN student ON sc.Sid = student.Sid
WHERE student.classid = 1 AND score >SOME(
SELECT score FROM sc
LEFT JOIN student ON sc.Sid = student.Sid
WHERE student.classid = 2
)

3.5 all子查询

题：查询出一班成绩比二班最高成绩高的学生

-- all  题：查询出一班成绩比二班最低成绩高的学生
SELECT DISTINCT student.* FROM sc
LEFT JOIN student ON sc.Sid = student.Sid
WHERE student.classid = 1 AND score >ALL(   -- all
SELECT score FROM sc
LEFT JOIN student ON sc.Sid = student.Sid
WHERE student.classid = 2
)

4.结果集的控制语句

4.1 IF(expr1,expr2,expr3)

IF(expr1,expr2,expr3)
expr1 条件
expr2 条件成立显示数据
expr3 条件不成立显示数据

-- 1.女
-- 2.男
SELECT Tid,Tname,IF(Tsex=1,'女','男')Tsex,Tbirthday,Taddress FROM teacher

4.2 IFNULL(expr1,expr2)

-- ifnull(expr1,expr2)
-- expr1 字段
-- expr2 当字段为null 默认值

-- ifnull(expr1,expr2) 
-- expr1 字段
-- expr2 当字段为null 默认值
SELECT sid,sname,IFNULL(birthday,'这个学生没有生日，很可怜')bir,ssex FROM student;  -- bir 起别名

4.3 case when then end（必须同时出现）

-- case when then end(要么)
-- 没有在选项内的，直接为null
SELECT Tid,Tname,
CASE Tsex
	WHEN 0 THEN '男'
	WHEN 1 THEN '女'
END Tsex,Tbirthday FROM teacher

-- ---------------------------------------------

SELECT Tid,Tname,
CASE Tsex
	WHEN 0 THEN '男'
	WHEN 1 THEN '女'
	ELSE '保密'
END Tsex,Tbirthday FROM teacher

-- ---------------------------------------

SELECT Tid,Tname,
CASE 
	WHEN Tsex>1 THEN '男'
	WHEN Tsex=1 THEN '女'
	WHEN Tsex<1 THEN '未知'
END Tbirthday FROM teacher

-- ----------------------------------------

-- 查询学生的成绩，
-- 大于80分的用B显示，
-- 大于70分的用C显示，
-- 大于60分的用D显示，
-- 小于60分的显示不及格

SELECT student.Sid,Sname,
CASE 
	WHEN score>=90 THEN 'A'
	WHEN score>=80 THEN 'B'
	WHEN score>=70 THEN 'C'
	WHEN score>=60 THEN 'D'
	WHEN score<=60 THEN '不及格'

END score FROM sc LEFT JOIN  student ON sc.Sid = student.Sid

SELECT score,
CASE 
	WHEN score>=90 THEN 'A'
	WHEN score>=80 THEN 'B'
	WHEN score>=70 THEN 'C'
	WHEN score>=60 THEN 'D'
	WHEN score<=60 THEN '不及格'
	
END score  FROM sc

（面试题）行转列，列转行

统计各个分数段的人数

输出格式1：

SELECT '100-90' 分数段,COUNT(*) FROM sc WHERE sc.score >= 90
UNION
SELECT '90-70',COUNT(*) FROM sc WHERE sc.score >= 70 AND sc.score <90
UNION
SELECT '70-60',COUNT(*) FROM sc WHERE sc.score >=60 AND sc.score <70
UNION
SELECT '不及格',COUNT(*) FROM sc WHERE sc.score <60

输出格式2：

SELECT '人数' 分段式,
COUNT(CASE WHEN score >= 90 THEN score END) '100-90',
COUNT(CASE WHEN score >= 70 AND score < 90 THEN score END) '90-70',
COUNT(CASE WHEN score >= 60 AND score < 70 THEN score END) '70-60',
COUNT(CASE WHEN score < 60 THEN score END) '不及格'
FROM sc;

标签：语句,classid,多表,--,student,sc,联查,class,SELECT
From： https://blog.csdn.net/m0_64481525/article/details/140619875

高级数据查询语句-多表联查

一、多表联查

1.内联查询

1.1 非等值查询

1.2 等值查询

1.3 五张表全部联查出来

1.4 面试题

1.5 inner join on

1.6 等值联查与inner join on 的区别

2.外联查询

2.1 left join on 左外联主表在join的左边

2.2 right join on 右外联主表在join的右边

2.3 union

3.子查询

3.1 where 型子查询

3.2 from 子查询查询结果将作为一张表使用

3.3 exists 子查询

3.4 any(推荐新版本的any)\some(旧版本)子查询

3.5 all子查询

4.结果集的控制语句

4.1 IF(expr1,expr2,expr3)

4.2 IFNULL(expr1,expr2)

4.3 case when then end（必须同时出现）

相关文章

赞助商

阅读排行

高级数据查询语句-多表联查

一、多表联查

1.内联查询

1.1 非等值查询

1.2 等值查询

1.3 五张表全部联查出来

1.4 面试题

1.5 inner join on

1.6 等值联查与inner join on 的区别

2.外联查询

2.1 left join on 左外联 主表在join的左边

2.2 right join on 右外联 主表在join的右边

2.3 union

3.子查询

3.1 where 型子查询

3.2 from 子查询 查询结果将作为一张表使用

3.3 exists 子查询

3.4 any(推荐新版本的any)\some(旧版本)子查询

3.5 all子查询

4.结果集的控制语句

4.1 IF(expr1,expr2,expr3)

4.2 IFNULL(expr1,expr2)

4.3 case when then end（必须同时出现）

相关文章

赞助商

阅读排行

2.1 left join on 左外联主表在join的左边

2.2 right join on 右外联主表在join的右边

3.2 from 子查询查询结果将作为一张表使用