SQL语句练习题2 单表查询

learn_think

已于 2024-10-29 09:04:28 修改

阅读量1.9k

点赞数 17

CC 4.0 BY-SA版权

分类专栏：数据库实验 211院校sql语句必练文章标签： sql 数据库

于 2024-10-12 23:13:27 首次发布

本文链接：https://blog.youkuaiyun.com/learn_think/article/details/142890824

数据库实验 211院校sql语句必练专栏收录该内容

7 篇文章

订阅专栏

【一句话概括本文】

本文旨在通过mysql实验的方式完成11道单表查询语言题。

【本文特色】

1.配图。干巴巴的文字没有说服力。一图胜千言。

2.sql型配色。颜色已经蕴含了信息。

3.实验验证。实践是检验真理唯一的标准，而非作者一拍脑门写出的结果。

4.目录。利用目录快速到达你有疑问的题目。

5.难点标识。你觉得难并不奇怪，作者已经帮你试过了，真的有难度。

【本文环境】

注意：本文大多数内容不受环境影响，只要与mysql有关即可。

1.服务器：mysql server 8.0

2.客户端：heidisql

【题目】

有以下四个表

1）department 表（院系编号、院系名称），deptNo是主键。

2）student表（学号、姓名、年龄、性别、院系），sno是主键，deptNo是外键。

3）course表（课程号、课程名、先修课、学分、院系编号），cno是主键。

4）SC表（学号、课程号、成绩）选课表，学号和课程号联合做主键。

如何建表可回顾练习题1：

SQL语句练习—创建表和索引-优快云博客

任务：完成11个单表查询

检索计算机系的学生。
检索年龄大于20岁的女学生的学号和姓名。
检索姓名以王打头的所有学生的姓名和年龄。
查询姓名为‘X国X’同学。
在SC中检索成绩为空值的学生学号和课程号。
输出c01课程的成绩单，要求成绩按从高到低排序。
求选修C04课程的学生的平均分。
每个学生选修的课程门数。
检索至少选修两门课程的学生学号。
求每个学生平均成绩。
统计每门课程的学生选修人数(超过3人的课程才统计)。查询结果按人数降序排列，若人数相同，按课程号升序排列。

【重要题干补充（必看）】

在做题之前，首先要导入我们自拟的数据。

推荐方法二，节约读者时间！

节约宝贵人生，做你爱做的事情！

方法一：使用excel表自己设计，然后转为.csv用客户端导入数据库

方法二：直接运行insert语句插入（复制粘贴一下就行了）

对你没搞错，下面的全部粘贴到查询页里面，运行1次即可，超级方便。

INSERT INTO department (deptNo, deptName) VALUES (1, '计算机系');

INSERT INTO department (deptNo, deptName) VALUES (2, '电子工程系');

INSERT INTO department (deptNo, deptName) VALUES (3, '管理系');



INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210001', '张三', 20, '男', 1);

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210002', '李四', 21, '女', 1);

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210003', '王五', 22, '男', 2);

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210004', '赵六', 20, '女', 3);

-- 满足查询姓名以王打头的所有学生的姓名和年龄

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210005', '王朝', 23, '男', 1);

-- 满足查询姓名为‘X国X’同学

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210006', '秦国强', 24, '男', 3);

-- 插入王丽同学的数据

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210010', '王丽', 19, '女', 1);

-- 插入其他女学生数据

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210011', '李红', 21, '女', 1);

-- ... 可以继续添加学生数据，确保满足查询条件



INSERT INTO course (Cno, Cname, Pno, Credit, Deptno) VALUES ('C01', '数据库原理', NULL, 4, 1);

INSERT INTO course (Cno, Cname, Pno, Credit, Deptno) VALUES ('C02', '操作系统', NULL, 3, 1);

INSERT INTO course (Cno, Cname, Pno, Credit, Deptno) VALUES ('C03', '电子电路', NULL, 4, 2);

INSERT INTO course (Cno, Cname, Pno, Credit, Deptno) VALUES ('C04', '管理学', NULL, 2, 3);



-- 插入C语言课程

INSERT INTO course (Cno, Cname, Pno, Credit, Deptno) VALUES ('C05', 'C语言', NULL, 3, 1);



-- ... 可以继续添加课程数据，确保满足查询条件



INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210001', 'C01', 90);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210002', 'C01', 85);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210003', 'C02', 78);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210004', 'C03', 88);

-- 满足在SC中检索成绩为空值的学生学号和课程号

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210007', 'C04', NULL);

-- 满足求选修C04课程的学生的平均分

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210008', 'C04', 75);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210009', 'C04', 80);

-- 插入王丽同学的选课数据

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210010', 'C01', 85);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210010', 'C02', 70);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210010', 'C03', 60);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210010', 'C04', 75);



-- 插入选修‘c01’课程的女学生的选课数据

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210011', 'C01', 90);





-- 插入选修‘数据库’和‘操作系统’课程的学生数据

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210001', 'C02', 85); -- 假设C02是操作系统



-- ... 可以继续添加选课数据，确保满足查询条件，包括成绩为NULL的情况



SELECT* FROM course;

SELECT* FROM department;

SELECT* FROM sc;

SELECT* FROM student;

#读者运行一下直接用就可以了。

下面是插入后的效果

看着这些表，有没有感觉简直是太方便了！

【解析】

1.检索计算机系的学生。

由于是单表查询题。我们假设计算机系的deptno已知。

SELECT * FROM student WHERE deptno=1

或

SELECT * FROM student WHERE deptno='1'

运行结果：

2.检索年龄大于20岁的女学生的学号和姓名。

SELECT sno,sname FROM student WHERE sage>20 and ssex='女'

运行结果：

3.检索姓名以王打头的所有学生的姓名和年龄。

SELECT sname,sage FROM student WHERE sname LIKE '王%'

运行结果：

在SQL中，LIKE 操作符用于在 WHERE 子句中搜索列中的特定模式。

【知识补充】'王_'与'王%'

①sname LIKE '王_':

下划线 _ 是一个通配符，它代表任何单个字符。

这个模式将匹配所有 sname 列中姓“王”后面紧跟一个任意字符的记录。

例如，它会匹配“王6”，但不会匹配“王武武”~~、“王五”（因为五是汉字，不是英文字符，所以本质是汉字编码，那么对应的不止一个字符）~~或“王”。

上面删除的话是作者问个ai后未注意到的错误。根据实验验证结果，该说法并不正确。

SELECT * FROM student WHERE sname LIKE '王_';

INSERT INTO student VALUES('20210033','王',18,'男',1);
SELECT * FROM student

SELECT * FROM student WHERE sname LIKE '秦_';

②sname LIKE '王%':

百分号 % 是一个通配符，它代表任意数量的字符（包括零个字符）。

这个模式将匹配所有 sname 列中以“王”开头的记录，无论后面跟随多少个字符。

例如，它会匹配“王五”、“王武武”、“王”等。

SELECT * FROM student WHERE sname LIKE '秦%';

4.查询姓名为‘X国X’同学。

SELECT * FROM student WHERE sname LIKE '_国_';

运行结果：

5.在SC中检索成绩为空值的学生学号和课程号。

Select sno,cno from sc where grade is null;

运行结果：

6.输出c01课程的成绩单，要求成绩按从高到低排序。

SELECT * FROM sc WHERE cno='c01' ORDER BY grade desc

运行结果：

7.求选修C04课程的学生的平均分。

SELECT AVG(grade) FROM sc WHERE cno='c04'

补充：

手动计算结果为：（80+75+75）/3≈76.7

8.每个学生选修的课程门数。（难题！难点：考察了聚合函数的使用，group by的使用）

SELECT sNo, COUNT(cNo) FROM SC GROUP BY sNo;

或

SELECT sNo, COUNT(cNo) AS courseCount FROM SC GROUP BY sNo;

运行结果：

9.检索至少选修两门课程的学生学号。

（若没有having子句，则无法将聚合函数作为判断条件。聚合函数无法放在where子句中作判定条件使用。）

SELECT sno FROM sc GROUP BY sno HAVING count(sno)>2;

SELECT sno FROM sc GROUP BY sno HAVING count(cno)>2;

10.求每个学生平均成绩。

SELECT sno,AVG(grade) AS avgrade FROM sc GROUP BY sno

运行结果：

补充：没有成绩的学生不统计。

方法一：

SELECT sno,AVG(grade) AS avgrade FROM sc GROUP BY sno HAVING AVG(GRADE) is NOT NULL;

方法二：

SELECT sno,AVG(grade) AS avgrade FROM sc WHERE grade IS NOT null GROUP BY sno

11.统计每门课程的学生选修人数(超过3人的课程才统计)。查询结果按人数降序排列，若人数相同，按课程号升序排列。

#注意，where'1'='1'用于说明where子句的位置。Where子句，group by子句，having子句，order by子句，limit 子句，offset子句顺序不能颠倒，否则语法报错。

SELECT cno,COUNT(sno) as csno FROM sc WHERE '1'='1' GROUP BY cno HAVING csno>3 ORDER BY COUNT(sno) DESC, cno asc