MySQL数据库04(高级查询)

(注:数据均为自己随意调节添加!!!)

 一、EXISTS子查询

1.用SQL语句检测temp表是否已经创建

语法:

DROP TABLE IF EXISTS temp;

CREATE TABLE temp(
...#省略建表语句

)

2.EXISTS子查询语法:

SELECT ......FROM 表名 WHERE EXISTS(子查询);

子查询有返回结果:EXISTS子查询结果为TRUE

子查询无返回结果:EXISTS子查询结果FALSE,外层查询不执行

例:

检查“Logic Java”课程最近一次考试成绩

如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

 
SELECT studentNo AS '学员学号',studentResult AS '成绩'
FROM result
WHERE EXISTS(SELECT * #用seists语句检查是否有80分以上的成绩
		     FROM result
		     WHERE studentResult>80#条件 成绩大于80
		     AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')#条件 成绩为80以上的必须是Logic Java课程
                     AND examDate=(SELECT MAX(examDate)FROM result WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')
			))#条件 必须是最近一次考试
AND studentNo IN (
	SELECT studentNo 
	FROM result
	WHERE examDate=(SELECT MAX(examDate)
			FROM result
			WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')
			)
	AND studentNo IN (SELECT studentNo
		     FROM result
		     WHERE studentResult>80
		     AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')))
ORDER BY studentResult DESC
LIMIT 5

执行命令得

3.NOT EXISTS子查询

检查“Logic Java”课程最近一次考试成绩

如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分

 
SELECT AVG(studentResult)+5
FROM result
WHERE NOT EXISTS(
	SELECT studentResult#查询所有考试成绩
	FROM result 
	WHERE studentResult >=60#条件1 成绩要 >=60
	AND  subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')# 条件2 科目必须是Logic Java
        AND examDate=(SELECT MAX(examDate)FROM result WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')#条件3 必须是近期考试
			))
AND  subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')					
AND  examDate=(SELECT MAX(examDate)FROM result WHERE `subjectNO`=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))

4.子查询注意事项

<1>任何允许使用表达式的地方都可以使用子查询

<2>潜逃在父查询SEELECT语句的子查询可包括

SELECT子句
FROM子句
WHERE子句
GROUP BY子句

HAVING子句

<3>只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

二、分组查询

1.掌握GROUP BY 字句实现分组查询

语法:

SELECT ...... FROM 表名

WHERE......

GROUP BY......

例:

查询每门课程的平均分，并且按照分数由高到低的顺序排列显示

 
SELECT subjectNo,AVG(studentResult) AS '平均分'
FROM result
GROUP BY subjectNo
ORDER BY 平均分 DESC

执行命令得

注:SELECT里表只中能包含:

    <1>.被分组的列

    <2>.为每分组返回一个值的表达式,如聚合函数

2.多列分组

分组统计每个年级男、女生人数

 
SELECT `gradeID` AS 年级编号,sex AS 性别,COUNT(*) AS 人数
FROM student
GROUP BY gradeID

执行命令得

3.分组筛选

例:获取课程平均分及格的课程编号

 
SELECT subjectNo,AVG(studentResult)AS 平均分
FROM result
GROUP BY subjectNo#分组
HAVING 平均分>=60#筛选
ORDER BY 平均分 DESC#顺序

执行命令得

按出生年份分组统计学生人数，将各组中人数达到2人的年份和人数显示出来

 
SELECT YEAR(bornDate)AS 年份, COUNT(studentNo) AS 人数
FROM student
GROUP BY 年份
HAVING 人数>2

4.HWERE与HAVING对比

WHERE字句:用来筛选FROM字句中指定的操作所产生的行

GROUP BY子句:用来分组 WHERE字句的输出

HAVING子句:用来分组的结果中筛选行

三、多表连接查询

内连接(INNER JOIN)

外连接

    左外连接(LEFT JOIN)

    右外连接(RIGHT JOIN)

1.内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行

语法: 

SELECT....

FROM 表1

INNER JOIN 表2

ON ...

直联语法:

SELECT....

FROM 表1,表2

WHERE ....

例:查询学生(不包括未考的)的考试成绩 显示学生姓名 科目ID  成绩

 
#内联
SELECT studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r 
ON r.studentNo=s.studentNo
#直联
SELECT studentName,subjectNo,studentResult
FROM student AS s ,result AS r
WHERE s.studentNo=r.studentNo

执行命令得

2.三表内连接(多表连接戏法相同)

查询学生(不包括未考的)的考试成绩 显示学生姓名 学生编号    成绩 科目名

(s.studentName 表示获取student表里的列 防止获取不明确报错)

 
#三表内连接
SELECT s.studentName,s.studentNo,r.studentResult,sub.subjectName
FROM student AS s
INNER JOIN result AS r 
ON r.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo
#三表直连接
SELECT s.studentName,sub.subjectNo,r.studentResult
FROM student AS s ,result AS r,`subject` AS sub
WHERE s.studentNo=r.studentNo
AND sub.subjectNo=r.subjectNo

执行命令得

3.左外连接

主表(左表)student中数据逐条匹配表result中的数据

<1>匹配,返回到结果集

<2>无匹配.NULL值返回到结果集

例:查取学生(包括未考试的学生)学生姓名 课程编号 考试成绩

 
SELECT studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r 
ON r.studentNo=s.studentNo

执行命令得

4.右外连接

右外连接的原理与左外连接相同

右表逐条去匹配记录;否则NULL填充

 
SELECT studentName,subjectNo,studentResult
FROM result AS r
LEFT JOIN student AS s 
ON r.studentNo=s.studentNo

执行命令得

练习

需求说明
为每个学生制作在校期间每门课程的成绩单，要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩
成绩单的数据项
学生姓名
课程所属的年级名称
课程名称
考试日期

考试成绩

 
SELECT s.studentName,sub.gradeID,sub.subjectName,r.examDate,r.studentResult
FROM student AS s
INNER JOIN result AS r ON r.studentNo=s.studentNo
INNER JOIN `subject` AS sub ON r.subjectNo=sub.subjectNo
WHERE examDate=(
	SELECT MAX(examDate) FROM result
	WHERE studentNo=s.studentNo#条件 必须同一个学生考同一门课程的最大时间 比较难写
	AND subjectNo=sub.subjectNo)

执行命令得

不加where条件结果为

加where条件结果为