2017年07月03号课堂笔记

2017年07月03号 星期一  多云 空气质量：轻度污染~中度污染

内容：MySQL第四节课

in和not in；两个表的内连接；exists和not exsits的使用；all,any和some；

使用子查询的注意事项；sql优化（使用exists 代替 in）；group by；两道mysql面试题

一、in和not in

1、in
-- 使用in替换 等于（=）的子查询语句！
-- in后面的子查询可以返回多条记录！

1）例题1
-- 查询年级编号是1或者2 的 所有学生列表

SELECT * FROM student WHERE gradeId IN(1,2)
2）例题2
-- 查询 年级名称是 大一或者大二的所有学生信息

-- 分析：学生表中没有 年级名称 但是有年级编号

-- 01.根据 年级名称 查询出 年级编号
SELECT gradeID FROM grade WHERE gradeName IN('大一','大二');

-- 02.再根据 年级编号 查询学生信息
SELECT * FROM student WHERE
gradeID
IN (SELECT gradeID FROM grade WHERE gradeName IN('大一','大二'))

3）例题3（使用where ... =）
-- 查询参加最近一次 高等数学-1 考试的学生 成绩的最高分和最低分

-- 01. 发现成绩表中 没有 编号 只有科目名称！根据名称取编号

SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1'

-- 02.查询最近一次 高等数学-1 考试的时间

SELECT MAX(ExamDate) FROM result
WHERE
SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1')

-- 02(2).所有最近考试的成绩(老师上课演示用，可以不写)
SELECT * FROM result
WHERE ExamDate='2013-11-11 16:00:00'

-- 03.开始获取最高分和 最低分
SELECT MAX(studentResult) AS 最高分,
MIN(studentResult) AS 最低分
FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1')
AND ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE
SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1'))

4）例题4（使用where ... in）
-- 查询 高等数学-1 考试成绩是 60 分的 学生信息

-- 01.根据 科目名称 获取 科目编号
SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'

-- 02.根据科目编号 和 考试成绩条件 查询所有的学生编号
SELECT studentNo FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60; -- 成绩=60

-- 03.根据符合条件学生编号 查询对应的学生信息
SELECT * FROM student
WHERE studentNo IN
(SELECT studentNo FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60)

2、not in
-- not in :不在某个范围之内

1）例题1
-- 查询未参加 “高等数学-1” 课程最近一次考试的在读学生名单

-- 01.根据 科目名称 获取 科目编号
SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'

-- 02根据科目编号 获取该科目最近一次考试时间
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=
(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')

-- 03.查询没参加该科目 最近一次考试的 学生编号，学生姓名
SELECT studentNo,StudentName FROM student
WHERE studentNo NOT IN
(
SELECT StudentNo FROM result
WHERE SubjectNo=
(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=
(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
)

 

二、高级查询

1、笛卡尔乘积

1）概念：笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，

第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

假设集合A={a, b}，集合B={0, 1, 2}，

则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

A表示所有声母的集合，B表示所有韵母的集合，那么A和B的笛卡尔积就为所有可能的汉字全拼。

2）老师mysql举例： SELECT * FROM `grade`INNER JOIN `student`
笛卡尔积 ：两个表数据的乘积！

 

2、两个表的内连接
SELECT * FROM `grade`INNER JOIN `student`
ON grade.`GradeID`=student.`GradeId`

on 两个表通过那一列建立关联关系

 

3、exists的使用

1）语法和作用

-- 01. 用于检测表，数据库等等 是否存在

-- 02.检查子查询中是否会返回一行数据！其实子查询并不返回任何数据！
只返回 true或者false！

SELECT * FROM Student WHERE EXISTS(SELECT NULL)
SELECT * FROM Student WHERE EXISTS(SELECT 9*9)
SELECT * FROM Student WHERE EXISTS(SELECT StudentName FROM student)

图示如下：

 

2）exists例题1　　exists和in的转换写法

-- 01.exists的写法

SELECT * FROM Student

WHERE EXISTS(SELECT studentName FROM Student WHERE studentName='张三')

-- 02.in的写法

SELECT * FROM Student WHERE studentName IN(SELECT studentName FROM Student)

-- 03.in 效果等同于 =any
SELECT * FROM Student WHERE
studentName =ANY(SELECT studentName FROM Student)

 

3）all,any和some
-- 01.all 大于子查询语句中的 最大值   >(1,2,3) >3
SELECT * FROM student
WHERE studentNo>ALL
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- 02.any 大于子查询语句中的 最小值   >(1,2,3) >1
SELECT * FROM student
WHERE studentNo>ANY
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- 03.some 和any功能一样
SELECT * FROM student
WHERE studentNo>SOME
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

 

4）exists例题2
-- 检查“高等数学-1” 课程最近一次考试成绩
-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

-- ① 不使用exists

-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')

-- 03. 在02的基础上 加条件 成绩大于80
SELECT * FROM result
WHERE ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
AND StudentResult>80

-- 04.显示分数排在前5名的学员学号和分数
SELECT studentNo,StudentResult FROM result
WHERE ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
AND StudentResult>80
ORDER BY StudentResult DESC
LIMIT 0,5

 

同样题目：

-- 检查“高等数学-1” 课程最近一次考试成绩
-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

-- ② 使用exists

-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试时间
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')

-- 03.查询学号和成绩
SELECT StudentNo,StudentResult FROM result
WHERE EXISTS
(
SELECT * FROM result
WHERE subjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
AND StudentResult>80
)
AND subjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
ORDER BY StudentResult DESC
LIMIT 0,5

 

5）exists例题3
-- 如果有 年级名称是大二 的学生，就 查询出 年级名称是大一的 所有学生信息

-- 01.先查询出 对应的年级编号
SELECT GradeId FROM grade WHERE GradeName='大一'
SELECT GradeId FROM grade WHERE GradeName='大二'

-- 02.在学生表中是否存在 年级名称是大二 的学生
SELECT * FROM student WHERE gradeID=(
SELECT GradeId FROM grade WHERE GradeName='大二'
)

-- 03.如果有查询出 年级名称是大一的 所有学生信息
SELECT * FROM student
WHERE EXISTS
(
SELECT * FROM student WHERE gradeID=(
SELECT GradeId FROM grade WHERE GradeName='大二'
)
)
AND GradeId=(
SELECT GradeId FROM grade WHERE GradeName='大一'
)

 

6）not exists
例题如下：
-- 检查“高等数学-1”课程最近一次考试成绩
-- 如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分

-- 01.查询“高等数学-1” 课程 对应的编号
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')

-- 03.查询成绩大于60的 （逆向思维）
SELECT StudentResult FROM result
WHERE StudentResult>60
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)

-- 04. 如果全部未通过考试，考试平均分加5分
SELECT AVG(StudentResult)+5 FROM result
WHERE NOT EXISTS
(
SELECT StudentResult FROM result
WHERE StudentResult>60
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)
)
AND SubjectNo=(
SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
)
AND ExamDate=(
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=(SELECT subjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
)

 

7） 使用子查询的注意事项
-- 01.任何允许使用表达式的地方都可以使用子查询
-- 02.只出现在子查询中但是没有在父查询中出现的列，结果集中的列不能包含！

 

4、sql优化（使用exists 代替 in）

1）使用exists 代替 in
使用not exists 代替not in

exists 只返回true或者false.不返回结果集
in 返回结果集

-->exists性能更高！

2）例题1
-- 查询姓李的学生信息 % 代表0或者多个字符 _代表一个字符

-- 01.使用like
SELECT * FROM student WHERE StudentName LIKE '李%'
SELECT * FROM student WHERE StudentName LIKE '李_'

-- 02.使用in完成上述代码
SELECT * FROM student WHERE StudentName IN(
SELECT studentName FROM student WHERE StudentName LIKE '李%')
-- in(多条数据--》返回结果集)

-- 03.使用exists替换
SELECT * FROM student WHERE EXISTS(
SELECT studentName FROM student)
AND StudentName LIKE '李%'
-- exists（有没有数据）

 

5. GROUP BY 列名 分组 　　having 分组之后的条件
1）例题1
-- 统计每门课程平均分各是多少
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno

2）例题2
-- 查询出课程平均分大于60的课程编号 和 平均分
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno
HAVING AVG(studentresult)>60 -- 分组之后的条件

3）例题3
-- 01.统计每门课程平均分各是多少 降序排列
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno
ORDER BY AVG(studentresult) DESC

-- 02.如果成绩相同 再按照 课程编号 升序排序
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno
ORDER BY AVG(studentresult) DESC,subjectno

4）例题4
-- 分组统计每个年级的 男女人数

SELECT gradeid 年级编号,sex 性别,COUNT(sex) 人数
FROM student
GROUP BY gradeid,sex

 

6、面试题
1）面试题1

老师代码：（数据不完全对应题目，领会解题方法）

-- 创建表
CREATE TABLE IF NOT EXISTS examTest(
id INT(2) NOT NULL,
sex VARCHAR(20)
)

-- 同时新增多条数据
INSERT INTO examTest VALUES(1,'男'),(2,'男'),(3,'女'),(4,NULL);

01.使用where
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
WHERE sex IS NOT NULL
GROUP BY sex
ORDER BY COUNT(sex) DESC

02.使用having
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
GROUP BY sex
HAVING sex IS NOT NULL
ORDER BY COUNT(sex) DESC

03.使用where和in（取巧）
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
WHERE sex IN('男','女')
GROUP BY sex
ORDER BY COUNT(sex) DESC

2）面试题2

作业，待解答

下次课确认答案后补上

老师代码：

 

 

 

三、作业

1、复习之前所讲的mysql课程（老师txt文件里的），预习事务，下节课考试

2、面试题第二题

四、老师辛苦了！