4
JackLin的博客
首页
分类
标签
生活
时间轴
关于我
友链
搜索
管理员
《数据库原理及应用》课后实验2_数据查询
数据库
MySQL
发布日期:
2020-05-18 13:30:02
阅读量:
183
所属分类:
数据库设计
前言:这个系列的的文章主要是关于 **《数据库原理及应用》(MySQL版)-清华大学出版社** 的课后实验报告的总结,相当于作为**数据库系统**课程的期末复习吧! ## 实验2_数据查询 > 实验主要用到的语句及知识点 - SELECT 语句实现基本查询,条件查询 - SELECT 语句的 GROUP BY,ORDER BY 以及 UNION 子句的作用和使用方法 - 嵌套查询 - 连接查询 > 具体的 SQL 语句 关键的重要的语句都用 ##### 标注了,解释以及题目内容都写在了注释里面。 ##### 1~2. 一些基本查询 ``` -- 1.(1) SELECT s_number, s_name, s_birthday from student_info; -- 1. (2) select s_name, s_address from student_info where s_number = 0002; -- 1. (3) select s_name, s_address from student_info where s_birthday > 1995-01-01 and s_gender = '女'; -- 2. (1) select s_number, c_number, g_score from grade where g_score > 70 and g_score < 80; -- 2.2 在grade表中查询课程编号为0002的学生的平均成绩。 select AVG(g_score) from grade where s_number = 0002; SELECT AVG(g_score) FROM grade WHERE s_number = 0002 GROUP BY s_number; -- 2.3 ###### 在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数 select COUNT(s_number) 总人数, COUNT(g_score) 有成绩人数 FROM grade WHERE s_number = '0003'; -- 2.4查询student_info的姓名和出生日期,查询结果按出生日期从大到小排序 select s_name, s_birthday from student_info ORDER BY s_birthday DESC; -- 2.5 查询所有姓名“张”的学生的学号和姓名。 select s_number, s_name from student_info where s_name LIKE '张%'; ``` ##### 3. &&&&& 对student_info表,查询学生的学号、姓名、性别、出生日期及家庭住址,查询结果先按照性别的由小到大排序,性别相同的再按学号由大到小排序。 ``` SELECT s_number, s_name, s_gender, s_birthday, s_address FROM student_info ORDER BY s_gender, s_number DESC; ``` ##### 4. &&&&& 使用GROUP BY子句查询grade表中各个学生的平均成绩。 ``` select AVG(g_score) from grade GROUP BY s_number; ``` ##### 5. 使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。 `注意:使用 UNION 会删除重复行,使用 UNION ALL 不会删除重复行` ``` select s_number, s_name FROM student_info WHERE s_name like '刘%' union select s_number, s_name from student_info where s_name like '张%'; ``` ##### 6. 嵌套查询 ``` -- 6. (1)在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。 SELECT s_name, s_birthday FROM student_info WHERE s_gender = ( SELECT s_gender FROM student_info WHERE s_name = '刘东阳' ); -- 6. 2 (2)使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。 SELECT s_number, s_name, s_gender FROM student_info WHERE s_number IN ( SELECT s_number FROM grade WHERE s_number IN ('0002', '0005') ); -- 6 (3)使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。 SELECT c_number, g_score FROM grade WHERE s_number = '0001' AND g_score > ANY ( SELECT g_score FROM grade WHERE s_number = '0002' ); -- 6 (4)使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。 SELECT c_number, g_score FROM grade WHERE s_number = '0001' AND g_score > ALL ( SELECT g_score FROM grade WHERE s_number = '0002' ); ``` ##### 7. 连接查询 ``` -- 7. (1)查询分数在80-90范围内的学生的学号、姓名、分数。 SELECT grade.s_number, s_name, g_score FROM grade, student_info WHERE grade.s_number = student_info.s_number AND g_score BETWEEN 80 AND 90; -- 7. (2)######## 使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。 SELECT student_info.s_number, s_name, g_score FROM student_info INNER JOIN grade ON student_info.s_number = grade.s_number INNER JOIN curriculum ON grade.c_number = curriculum.c_number AND c_name = '数据库原理及应用'; -- 用连接查询也可以达到相同的效果 SELECT student_info.s_number, s_name, g_score FROM student_info, grade, curriculum WHERE student_info.s_number = grade.s_number AND grade.c_number = curriculum.c_number AND curriculum.c_name = '数据库原理及应用'; -- 7. (3) &&&&& 查询每个学生所选课程的最高成绩,要求列出学号、姓名、最高成绩。 SELECT student_info.s_number, s_name, MAX(g_score) AS 最高成绩 FROM student_info, grade WHERE student_info.s_number = grade.s_number GROUP BY grade.s_number; -- 7. (4) &&&&& 使用左外连接查询每个学生的总成绩,要求列出学号、姓名、总成绩,没有选修课程的学生的总成绩为空。 -- ---> 注意:这里需要按照学号分组 SELECT s.s_number, s_name, SUM(g_score) AS 总成绩 FROM student_info s LEFT JOIN grade g ON s.s_number = g.s_number GROUP BY s_number; -- 7. (5)为grade表添加数据行:学号为0004、课程编号为0006、分数为76。 insert into curriculum (c_number, c_name, c_score) values ('0006', 'test', 1); insert into grade (s_number, c_number, g_score) values ('0004', '0006', 76); -- 7. (6) &&&&& 使用右外连接查询所有课程的选修情况,要求列出课程编号、课程名称、选修人数,curriculum表中没有的课程列值为空 SELECT curriculum.c_number, c_name, COUNT(grade.c_number) AS 课程人数 FROM curriculum RIGHT JOIN grade ON curriculum.c_number = grade.c_number GROUP BY curriculum.c_number; ```