数据库二 查询 – SELECT语句

【实验名称】：查询 – SELECT语句

【实验目标】：

1. 掌握使用IS NULL, LIKE, BETWEEN… AND等谓词的查询；
2. 掌握时间字段上的查询；
3. 掌握集合函数查询；
4. 掌握使用order by，group by，having等子句的查询。
5. 掌握连接查询；包括左连接、右连接、外连接、自连接；
6. 掌握子查询。

【实验具体要求】：

1. 查询性别为男的所有学生的学号、姓名和班级号，结果按照班级ID倒序排列；（order by）
2. 查询学号以2002开头的学生信息,字段包括学号、姓名、班级号；（like）
3. 查询状态为“未审核”或“审批已通过”，且申请时间在2013年9月4日之后的请假申请单的信息，包含申请时间为9月4日的申请单；（单表多条件查询）
4. 查询时间在2013年8月31日和2013年9月2日之间所提交的申请单ID，请假原因；(between、时间)
5. 查询方燕燕老师所教课程的的选课人数（一人选两门课程，算一人）；（distinct）
6. 查询刘波老师对请假单审核不通过的请假原因与学生姓名；
7. 查询选课人数超过3人的课程号，并按课程号倒序排列；（group by,having,order,desc）
8. 统计每门课的学生的个数（集合函数）。
9. 查询审批通过人数最多的课程名称和教师姓名；（连接、排序）
10. 查询选课人数最多和第二多的课程名称和任课老师姓名；（函数、连接）
11. 查询只选了课程《C++编程》的学生姓名；（子查询）
12. 查询选修了全部课程的学生姓名；（子查询）
13. 查询选修了课程3的学生学号、姓名、身份证号；（多表）
14. 据学生学号将leave_apply表与message表做自然完全外连接查询；（自然连接）
15. 据学号将leave_apply表与message表进行左连接查询,并解释这样做所具有的业务含义；（左连接）
16. 据学号将leave_apply表与message表进行右连接查询,并解释这样做所具有的业务含义；（右连接）
17. 总结自然连接、左连接、右连接查询在产生的结果上面有什么区别；
18. 对lesson表进行查询，字段包括课程号、课程名称、学分、先修课程号、先修课程名称、先修课程学分；（自连接）

【实验步骤】：

（1）  查询性别为男的所有学生的学号、姓名和班级号，结果按照班级ID倒序排列；（order by）

（2）  查询学号以2002开头的学生信息,字段包括学号、姓名、班级号；（like）

（3）  查询状态为“未审核”或“审批已通过”，且申请时间在2013年9月4日之后的请假申请单的信息，包含申请时间为9月4日的申请单；（单表多条件查询）

（4）  查询时间在2013年8月31日和2013年9月2日之间所提交的申请单ID，请假原因；(between、时间)

（5）  查询方燕燕老师所教课程的的选课人数（一人选两门课程，算一人）；（distinct）

（6）  查询刘波老师对请假单审核不通过的请假原因与学生姓名；

（7）  查询选课人数超过3人的课程号，并按课程号倒序排列；（group by,having,order,desc）

（8）  统计每门课的学生的个数（集合函数）。

（9）  查询审批通过人数最多的课程名称和教师姓名；（连接、排序）

（10） 查询选课人数最多和第二多的课程名称和任课老师姓名；（函数、连接）

（11） 查询只选了课程《C++编程》的学生姓名；（子查询）

（12） 查询选修了全部课程的学生姓名；（子查询）

（13） 查询选修了课程3的学生学号、姓名、身份证号；（多表）

（14） 据学生学号将leave_apply表与message表做自然完全外连接查询；（自然连接）

（15） 据学号将leave_apply表与message表进行左连接查询,并解释这样做所具有的业务含义；（左连接）

业务含义：将请假条提交审批后，通过左连接，可以看到包含未审核的请假单的，所有请假条信息和审批的系统状态。

（16） 据学号将leave_apply表与message表进行右连接查询,并解释这样做所具有的业务含义；（右连接）

业务含义：将请假条提交审批后，通过右连接，可以看到所有审批过的申请单和申请单信息。

（17） 总结自然连接、左连接、右连接查询在产生的结果上面有什么区别；

自然连接会比较两个表的相同列，可以得到所有有共同列的请假单信息。

左连接会保留左表的所有信息，和右表中与左表相同列的信息。

右连接会保留右表的所有信息，和左表中与右表相同列的信息。

（18） 对lesson表进行查询，字段包括课程号、课程名称、学分、先修课程号、先修课程名称、先修课程学分；（自连接）

 

【小结】：

知识小结：

（1）ORDER BY

可以对获取的资料进行排序，从小到大ASC,从大到小DESC

ORDER BY “栏目名” [ASC,DESC]

（2）LIKE

多在WHERE中使用，能根据特征模式获取我们要的数据

SELECT “栏目名”

FROM “表格名”

WHERE “栏目名”LIKE{模式}

例：WHERE store_name LIKE ‘%AN%’

（3）BETWEEN

可以使我们通过一个范围获得数据库中的值

SELECT “栏目名”

FROM “表格名”

WHERE “栏目名”BETWEEN ‘值一’AND‘值二’

例：WHERE Date BETWEEN ‘1919-02-02’ AND ‘2020-02-20’

（4）DISTINCT

在表中，有可能会包含重复值，DISTINCT 可以返回唯一不同的值。

SELECT DISTINCT 列 FROM 表

（5）GROUP BY

可以将表按照列属性进行分组，进行分开处理

SELECT “栏位1”,SUM“栏位2”

FROM “表格名”

GROUP BY “栏位1”

（6）HAVING

实现对组的值的筛选，一般跟在GROUP BY的后面，一个含有HAVING字句的SQL并不一定有GROUP BY 子句。

SELECT “栏位1”

FROM “表格名”

GROUP BY “栏位1”

HAVING (函数条件)

（7）区别HAVING 和WHERE

①WHERE 和HAVING 都是用来筛选的

②HAVING 用来筛选组，WHERE 用来筛选记录。where搜索条件在分组操作之前应用，having搜索条件在进行分组操作之后应用

③当一个查询包含了where条件和聚合函数，先执行条件过滤，再进行聚合函数，如：SELECT SUM(score) FROM sc WHERE score > 60 先过滤出score>60的记录，再进行SUM求和

④having在聚合之后进行过滤，having在分组的时候会用，对分组结果进行过滤，通常分组里面包含聚合函数，如：

SELECT sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score) >60;

（8）集合函数

AVG 平均

COUNT 计数

SUM 求和

MAX 最大值

MIN 最小值

（9）连接

自然连接：比较两个表的共同列，并保存共同的列值。

SELECT “栏位1”

FROM TABLE_A NATURAL JOIN TABLE_B

左连接：保留左表的全部列，和右表与左表相同的列值。

SELECT “栏位1”

FROM TABLE_A LEFT JOIN TABLE_B ON [A表值]=[B表值]

右连接：保留右表的全部列，和左表与右表相同的列值。

SELECT “栏位1”

FROM TABLE_A RIGHT JOIN TABLE_B ON [A表值]=[B表值]