《数据库原理及应用》课后实验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;

```

JackLin的博客

《数据库原理及应用》课后实验2_数据查询