| 这个作业属于哪个课程 | https://edu.cnblogs.com/campus/uzz/cs3 |
|---|---|
| 这个作业要求在哪里 | https://edu.cnblogs.com/campus/uzz/cs3/homework/13061 |
| 这个作业的目标 | 第4次作业-SQL语句的基本使用4-分组查询、连接查询 |
1.对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序。
select 学号,姓名,性别,出生日期,家庭住址 from student_info order by 性别 asc,学号 desc;
+------+--------+------+------------+---------------------+
| 学号 | 姓名 | 性别 | 出生日期 | 家庭住址 |
+------+--------+------+------------+---------------------+
| 0008 | 张玲珑 | 女 | 1997-12-24 | 滨江市新建路97号 |
| 0007 | 肖月玲 | 女 | 1996-12-07 | 东方市南京路11号 |
| 0003 | 马晓夏 | 女 | 1995-05-12 | 长岭市五一路763号 |
| 0006 | 郭小斌 | 男 | 1997-11-10 | 南山市红旗路113号 |
| 0005 | 孙海洋 | 男 | 1995-04-03 | 长岛市解放路27号 |
| 0004 | 钱忠理 | 男 | 1994-09-23 | 滨海市洞庭大道279号 |
| 0002 | 刘东阳 | 男 | 1998-12-09 | 东阳市八一北路33号 |
| 0001 | 张青平 | 男 | 2000-10-01 | 衡阳市东风路77号 |
+------+--------+------+------------+---------------------+
2.使用GROUP BY子句查询grade表中各个学生的平均成绩。
mysql> select 学号,avg(分数) as 平均成绩 from grade group by 学号;
+------+-----------+
| 学号 | 平均成绩 |
+------+-----------+
| 0001 | 84.200000 |
| 0002 | 74.600000 |
| 0003 | 82.800000 |
+------+-----------+
3 rows in set (0.12 sec)
3.查询分数在80-90范围内的学生的学号、姓名、分数。
select s.学号,s.姓名,g.分数 from student_info s,grade g where s.学号 = g.学号 and g.分数 between 80 and 90;
+------+--------+-------+
| 学号 | 姓名 | 分数 |
+------+--------+-------+
| 0001 | 张青平 | 80.00 |
| 0001 | 张青平 | 88.00 |
| 0001 | 张青平 | 85.00 |
| 0002 | 刘东阳 | 80.00 |
| 0003 | 马晓夏 | 84.00 |
| 0003 | 马晓夏 | 81.00 |
| 0003 | 马晓夏 | 82.00 |
+------+--------+-------+
7 rows in set (0.18 sec)
4.使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。
select s.学号,姓名,分数 from student_info s inner join grade g
-> on s.学号=g.学号
-> inner join curriculum c
-> on g.课程编号=c.课程编号
-> where 课程名称='数据库原理及应用';
+------+--------+-------+
| 学号 | 姓名 | 分数 |
+------+--------+-------+
| 0001 | 张青平 | 88.00 |
| 0002 | 刘东阳 | 80.00 |
| 0003 | 马晓夏 | 81.00 |
+------+--------+-------+
3 rows in set (0.10 sec)
5.查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。
select s.学号,姓名,max(分数) as 最高成绩 from student_info s,grade g
where s.学号=g.学号 group by s.学号;
+------+--------+----------+
| 学号 | 姓名 | 最高成绩 |
+------+--------+----------+
| 0001 | 张青平 | 91.00 |
| 0002 | 刘东阳 | 80.00 |
| 0003 | 马晓夏 | 92.00 |
+------+--------+----------+
3 rows in set (0.15 sec)
6.使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
select s.学号,姓名,sum(分数) as 总成绩 from student_info s left outer join grade g on s.学号=g.学号
group by s.学号;
+------+--------+--------+
| 学号 | 姓名 | 总成绩 |
+------+--------+--------+
| 0001 | 张青平 | 421.00 |
| 0002 | 刘东阳 | 373.00 |
| 0003 | 马晓夏 | 414.00 |
| 0004 | 钱忠理 | NULL |
| 0005 | 孙海洋 | NULL |
| 0006 | 郭小斌 | NULL |
| 0007 | 肖月玲 | NULL |
| 0008 | 张玲珑 | NULL |
+------+--------+--------+
8 rows in set (0.19 sec)
7.为grade表添加数据行：学号为0004、课程编号为0006、分数为76。
insert into grade(学号,课程编号,分数) values ('0004','0006',76);
Query OK, 1 row affected (0.00 sec)
8.使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。
elect curriculum.课程编号,curriculum.课程名称,count(*) from grade right join curriculum on grade.课程编号=curriculum.课程编号
group by curriculum.课程编号,curriculum.课程名称;
+----------+------------------+----------+
| 课程编号 | 课程名称 | count(*) |
+----------+------------------+----------+
| 0001 | 计算机应用基础 | 3 |
| 0002 | c语言程序设计 | 3 |
| 0003 | 数据库原理及应用 | 3 |
| 0004 | 英语 | 3 |
| 0005 | 数学 | 3 |
+----------+------------------+----------+
9.查询所有没有选修课程的学生的学号、姓名。
select 学号,姓名 from student_info where 学号 not in (select distinct 学号 from grade);
+------+--------+
| 学号 | 姓名 |
+------+--------+
| 0005 | 孙海洋 |
| 0006 | 郭小斌 |
| 0007 | 肖月玲 |
| 0008 | 张玲珑 |
+------+--------+
4 rows in set (0.17 sec)
10.查询选修课程的人数。
select count(distinct 学号) as '人数' from grade;
+------+
| 人数 |
+------+
| 4 |
+------+
11.查询选课人数大于等于3人的课程编号、课程名称、人数。
select c.课程名称,g.课程编号,count(学号) as '人数' from grade g,curriculum c
where g.课程编号 = c.课程编号
group by 课程编号
having count(学号)>=3;
+------------------+----------+------+
| 课程名称 | 课程编号 | 人数 |
+------------------+----------+------+
| 计算机应用基础 | 0001 | 3 |
| c语言程序设计 | 0002 | 3 |
| 数据库原理及应用 | 0003 | 3 |
| 英语 | 0004 | 3 |
| 数学 | 0005 | 3 |
+------------------+----------+------+
5 rows in set (0.12 sec)
12.在查询的FROM子句中实现表与表之间的连接有哪几种方式？对应的关键字分别是什么？
相等连接:from 表名1innerjoin表名2
自身连接:from 表名.a，表名.b where a.字段名=b.字段名
不等连接:between
左外连接:left join
右外连接:right join
