165 lines
5.7 KiB
Markdown
165 lines
5.7 KiB
Markdown
**一、上机内容**
|
||
|
||
1. 在studentsdb数据库中使用SELECT语句进行基本查询。
|
||
|
||
（1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。
|
||
|
||
```sql
|
||
select 学号,姓名,出生日期 from student_info;
|
||
```
|
||
|
||
（2）查询student_info表学号为 0002的学生的姓名和家庭住址。
|
||
|
||
```sql
|
||
select 姓名,家庭住址 from student_info where 学号=0002;
|
||
```
|
||
|
||
（3）查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。
|
||
|
||
```sql
|
||
select 姓名,出生日期 from student_info where 性别='女' and 出生日期>'1995-12-31';
|
||
```
|
||
|
||
2. 使用select语句进行条件查询。
|
||
|
||
（1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。
|
||
|
||
```sql
|
||
select 学号,课程编号,分数 from grade where 分数>=70 and 分数<=80;
|
||
```
|
||
|
||
（2）在grade表中查询课程编号为0002的学生的平均成绩。
|
||
|
||
```sql
|
||
select avg(分数) from grade where 课程编号=0002;
|
||
```
|
||
|
||
（3）在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。
|
||
|
||
```sql
|
||
select count(分数) as 人数 from grade where 课程编号=0003;
|
||
```
|
||
|
||
（4）查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。
|
||
|
||
```sql
|
||
select 姓名,出生日期 from student_info order by 出生日期 desc;
|
||
```
|
||
|
||
（5）查询所有姓名“张”的学生的学号和姓名。
|
||
|
||
```sql
|
||
select 学号,姓名 from student_info where 姓名 like '张%';
|
||
```
|
||
|
||
3. 对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序。
|
||
|
||
```sql
|
||
select 学号,姓名,性别,出生日期,家庭住址 from student_info order by 性别,学号 desc;
|
||
```
|
||
|
||
4. 使用GROUP BY子句查询grade表中各个学生的平均成绩。
|
||
|
||
```sql
|
||
select 学号,avg(分数) 平均分 from grade group by 学号;
|
||
```
|
||
|
||
5. 使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。
|
||
|
||
```sql
|
||
select 学号,姓名 from student_info where 姓名 like '刘%'
|
||
union
|
||
select 学号,姓名 from student_info where 姓名 like '张%';
|
||
```
|
||
|
||
6. 嵌套查询
|
||
|
||
（1）在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
|
||
|
||
```sql
|
||
select 姓名,出生日期 from student_info where 性别=(select 性别 from student_info where 姓名='刘东阳');
|
||
```
|
||
|
||
（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。
|
||
|
||
```sql
|
||
select 学号,姓名,性别 from student_info where 学号 in (select 学号 from grade where 课程编号 in('0002','0005'));
|
||
```
|
||
|
||
（3）使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。
|
||
|
||
```sql
|
||
select 课程编号,分数 from grade where 学号 = '0001' and 分数 > any (select 分数 from grade where 学号 = '0002');
|
||
```
|
||
|
||
（4）使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
|
||
|
||
```sql
|
||
select 课程编号,分数 from grade where 学号 = '0001' and 分数 > all (select 分数 from grade where 学号 = '0002');
|
||
```
|
||
|
||
7. 连接查询
|
||
|
||
（1）查询分数在80-90范围内的学生的学号、姓名、分数。
|
||
|
||
```sql
|
||
select st.学号,姓名,分数 from student_info st,grade gr where st.学号 = gr.学号 and 分数 between 80 and 90;
|
||
```
|
||
|
||
（2）查询学习“数据库原理及应用”课程的学生学号、姓名、分数。
|
||
|
||
```sql
|
||
select st.学号,姓名,分数 from student_info st inner join grade gr on st.学号 = gr.学号 inner join curriculum cu on gr.课程编号 = cu.课程编号 where 课程名称 = '数据库原理及应用';
|
||
|
||
select st.学号,姓名,分数 from student_info st,grade gr,curriculum cu where 课程名称 = '数据库原理及应用' and gr.课程编号 = cu.课程编号 and st.学号=gr.学号;
|
||
```
|
||
|
||
（3）查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。
|
||
|
||
```sql
|
||
select st.学号,姓名,max(分数) 最高成绩 from student_info st,grade gr where st.学号 = gr.学号 group by st.学号;
|
||
```
|
||
|
||
（4）使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
|
||
|
||
```sql
|
||
select st.学号,姓名,sum(分数) 总成绩 from student_info st left outer join grade gr on st.学号 = gr.学号 group by st.学号;
|
||
```
|
||
|
||
（5）为grade表添加数据行：学号为0004、课程编号为0006、分数为76。使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数,curriculum表中没有的课程列值为空。
|
||
|
||
```sql
|
||
insert into grade values('0004','0006',76);
|
||
|
||
select gr.课程编号,课程名称,count(*) 选修人数 from curriculum cu right outer join grade gr on gr.课程编号 = cu.课程编号 group by gr.课程编号;
|
||
```
|
||
|
||
**二、实验思考**
|
||
|
||
1.查询所有没有选修课程的学生的学号、姓名。
|
||
|
||
```sql
|
||
select st.学号,姓名,课程编号 from student_info st left outer join grade gr on gr.学号=st.学号 where 课程编号 is null;
|
||
```
|
||
|
||
2.查询选修课程的人数。
|
||
|
||
```sql
|
||
select count(distinct 学号) 选修课程人数 from grade;
|
||
```
|
||
|
||
3.查询选课人数大于等于3人的课程编号、课程名称、人数。
|
||
|
||
```sql
|
||
select gr.课程编号,cu.课程名称,count(gr.学号) 人数 from grade gr,curriculum cu where gr.课程编号=cu.课程编号 group by gr.课程编号 having count(gr.学号)>=3;
|
||
```
|
||
|
||
4.在查询的FROM子句中实现表与表之间的连接有哪几种方式？对应的关键字分别是什么？
|
||
|
||
```sql
|
||
内连接 inner join
|
||
左连接 left join
|
||
右连接 right join
|
||
```
|
||
|