mysql练习题目试水50题，附建库sql代码

如果你没试过水的话，那一题一题地每一题都敲一遍吧。不管它们对你看来有多么简单。

建库代码

部分题目答案在末尾，可用ctrl f 搜索题号。

作业练习——学生-选课 表结构

学生表： Student(Sno,Sname,Ssex,Sage,Sdept)

说明：Sno 学号(主键)，Sname 姓名，Ssex 性别，Sage

年龄，Sdept 所在系别

课程表： Course(Cno,Cname,Cpno,Ccredit)

说明：Cno 课程号(主键)，Cname 课程名，Cpno 选修课

号（某课程的选修课），Ccredit 学分

学生选课表： SC(Sno,Cno,Grade)

说明：Sno 学号(主键),Cno 课程号(主键),Grade 成绩

作业练习——学生-选课 题目-1

1.编写建库sql语句

2.编写创建数据库表的语句

3.给数据表加约束

4.往三个表添加测试数据

5.查询全体学生的学号与姓名

6.查询全体学生的详细记录

7.查询全体学生的姓名及其出生年份

8.查询全体学生的姓名，出生年份和所有系，要求用小写字母表示所

有系名

9.查询选修了课程的学生学号 并消除重复行

10.查询计算机系全体学生的名单

11.查询所有年龄在20岁以下的学生姓名及其年龄

12.查询考试成绩有不及格的学生的学号

作业练习——学生-选课 题目-2（续）

13.查询年龄在20～23岁(包括20岁和23岁)之间的学生的姓名，系别

和年龄

14.查询年龄不在20～23岁之间的学生的姓名，系别和年龄

15.查询信息系(IS),数学系(MA)和计算机系(CS)的学生姓名和性别

16.查询即不是信息系(IS),数学系(MA)也不是计算机系(CS)的学生姓

名和性别

17.查询学生号为 2009001 的学生的详细情况

18.查询所有姓刘的学生姓名，学号和性别

19.查询姓“欧阳”且全名为三个汉字的学生的姓名

20.查询名字中第二个字为阳字的学生的姓名和学号

21.查询缺少成绩的学生的学号和相应的课程号

22.查询所有成绩的学生的学号和相应的课程号

23.查询计算机系年龄在20岁以上的学生姓名

作业练习——学生-选课 题目-3（续）

24.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降

序排列

25.查询全体学生情况，查询结果按所在系的系号升序排列，同一系

中的学生按年龄降序排列

26.查询学员总人数

27.查询选修了课程的学生人数

28.计算 1 号课程的学生平均成绩

29.查询选修1号课程的学生最高分数

30.求各个课程号及相应的选课人数

31.查询选修了2门以上课程的学生学号

32.查询每个学生及其选修课程的情况

33.查询每一门课的间接选修课（即选修课的选修课）

34.查询选修2号课程且成绩在60分以上的所有学生

作业练习——学生-选课 题目-4（续）

35.查询每个学生的学号，姓名，选修的课程及成绩

36.查询与 "刘晨"在同一个系学习的学生

37.查询选修了课程名为“信息系统”的学生学号和姓名

38.查询其他系中比信息系某一学员年龄小的学生姓名和年龄

39.查询其他系中比信息系所有学员年龄都小的学生姓名和年龄

40.查询所有选修了 1 号课程的学生姓名

41.查询没有选修 1 号课程的学生姓名

42.查询选修了全部课程的学生姓名

43.查询至少选修了 学生 2009002 选修的全部课程的学生号码

44.对每一个系，求学生的平均年龄，并把结果存入数据库

45.将计算机系的全体学生的成绩置零

作业练习——学生-选课 题目-5（续）

46.删除学号为 2009008 的学员记录

47.删除计算机系所有学生的选课记录

48.查询不选修课程名为’SC’的学员姓名和学号

49.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

50.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”

号课和“2”号课的成绩

建库代码

创建数据库db_school后：

 1 USE `db_school`;
 2 
 3 /*Table structure for table `t_course` */
 4 
 5 DROP TABLE IF EXISTS `t_course`;
 6 
 7 CREATE TABLE `t_course` (
 8   `Cno` int(11) NOT NULL AUTO_INCREMENT,
 9   `Cname` varchar(50) NOT NULL,
10   `Cpno` int(11) NOT NULL,
11   `Ccredit` float NOT NULL,
12   PRIMARY KEY (`Cno`)
13 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
14 
15 /*Data for the table `t_course` */
16 
17 insert  into `t_course`(`Cno`,`Cname`,`Cpno`,`Ccredit`) values (1,\'数据库\',5,4),(2,\'数学\',0,2),(3,\'信息系统\',1,4),(4,\'操作系统\',6,3),(5,\'数据结构\',7,4),(6,\'数据处理\',0,2),(7,\'PASCAL语言\',6,4);
18 
19 /*Table structure for table `t_sc` */
20 
21 DROP TABLE IF EXISTS `t_sc`;
22 
23 CREATE TABLE `t_sc` (
24   `Sno` int(11) NOT NULL,
25   `Cno` int(11) NOT NULL,
26   `Grade` float DEFAULT NULL,
27   PRIMARY KEY (`Sno`,`Cno`)
28 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
29 
30 /*Data for the table `t_sc` */
31 
32 insert  into `t_sc`(`Sno`,`Cno`,`Grade`) values (20090001,1,92),(20090001,2,85),(20090001,3,NULL),(20090002,2,90),(20090002,3,80);
33 
34 /*Table structure for table `t_student` */
35 
36 DROP TABLE IF EXISTS `t_student`;
37 
38 CREATE TABLE `t_student` (
39   `Sno` int(11) NOT NULL AUTO_INCREMENT,
40   `Sname` varchar(20) NOT NULL,
41   `Ssex` varchar(10) NOT NULL,
42   `Sage` int(11) DEFAULT NULL,
43   `Sdept` varchar(50) DEFAULT NULL,
44   PRIMARY KEY (`Sno`)
45 ) ENGINE=InnoDB AUTO_INCREMENT=20160034 DEFAULT CHARSET=utf8;
46 
47 /*Data for the table `t_student` */
48 
49 insert  into `t_student`(`Sno`,`Sname`,`Ssex`,`Sage`,`Sdept`) values (20090001,\'张凯\',\'男\',22,\'CS\'),(20090002,\'李勇\',\'男\',23,\'IS\'),(20090003,\'刘晨\',\'女\',22,\'MA\'),(20090004,\'张力\',\'男\',22,\'MA\'),(20090005,\'张悦\',\'女\',20,\'CS\'),(20090006,\'王敏\',\'女\',18,\'IS\'),(20160001,\'张三\',\'男\',33,\'计算机系\'),(20160002,\'zh\',\'女\',12,\'计算机系\'),(20160003,\'zh\',\'男\',12,\'计算机系\'),(20160004,\'zh\',\'女\',55,\'sdfs\'),(20160005,\'zh\',\'女\',12,\'sdfs\'),(20160006,\'zh\',\'女\',55,\'计算机系\'),(20160007,\'zh\',\'男\',24,\'sdfs\'),(20160008,\'zh\',\'女\',47,\'计算机系\'),(20160009,\'zh\',\'男\',12,\'sdfs\'),(20160010,\'zh\',\'女\',12,\'sdfs\'),(20160011,\'zh\',\'男\',12,\'sdfs\'),(20160012,\'zh\',\'女\',77,\'sdfs\'),(20160013,\'zh\',\'男\',12,\'sdfs\'),(20160014,\'zh\',\'男\',12,\'计算机系\'),(20160015,\'zh\',\'男\',12,\'sdfs\'),(20160016,\'zh\',\'女\',12,\'sdfs\'),(20160017,\'zh\',\'男\',12,\'sdfs\'),(20160018,\'zh\',\'男\',12,\'sdfs\'),(20160019,\'zh\',\'女\',12,\'计算机系\'),(20160020,\'zh\',\'男\',12,\'sdfs\'),(20160021,\'zh\',\'男\',12,\'计算机系\'),(20160022,\'zh\',\'女\',12,\'sdfs\'),(20160023,\'zh\',\'男\',12,\'sdfs\'),(20160024,\'zh\',\'女\',12,\'sdfs\'),(20160025,\'zh\',\'女\',12,\'sdfs\'),(20160026,\'zh\',\'男\',12,\'sdfs\'),(20160027,\'zh\',\'女\',12,\'sdfs\'),(20160028,\'zh\',\'女\',12,\'sdfs\'),(20160029,\'zh\',\'女\',12,\'sdfs\');

个人记录：

多表查询

连接，即：查询的结果表的一个记录依赖于各个子表和连接条件。

SELECT s.stuName,c.courseName,sc.grade FROM t_student s,t_course c,t_studentcourse sc WHERE s.stuId=sc.stuId AND c.courseId=sc.courseId;

等于

SELECT s.stuName,c.courseName,sc.grade FROM t_student s join t_course c join t_studentcourse sc ON s.stuId=sc.stuId AND c.courseId=sc.courseId;

查询的结果都是得到一个完整（记录FULL！）的结果表，然后截取所要的。

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

select 查询了聚集函数只返回一个记录。

group by 配合聚集函数使用，也可以不配合。值相同的为一组。

分组后一般只查询分组对象，其他对象没有意义。

group by 后只接having语句，因为对象为组。

嵌套查询是独立的，如别名。

mysql 查询时对字符串的大小写不敏感。

查询是否为null： WHERE sc.Grade IS NULL;

order by 列，列 //用逗号分隔。

聚集函数在结果表中作用，即后发执行。。比broup by还后。

group by 后每个组只存在一个记录。

查询所有选修了 1 号课程的学生姓名

? 41.查询没有选修 1 号课程的学生姓名

41：

SELECT s.Sname FROM t_student s

WHERE s.Sname

NOT IN(SELECT s.Sname FROM t_student s,t_sc sc

WHERE s.Sno=sc.Sno

AND sc.Cno=1 GROUP BY sc.Sno);

//不用group by也行。

//返回多个记录，适用于in子句。

//

因为group by 只返回一条数据，所以只有：查询结果以group by对象区分时才有意义（最直观的情况是只查询group by的对象）。换个说法：我们要得到的结果中作为分组依据的列的每个值只有一个记录。

group by函数本质是为‘聚合’服务的。

//推断不确定：上面的sql中恰好Cno=1的记录分组中只有一条。

//不过应该是对的。

相似的having语句是筛选组的（组的记录固定为1），所以。。。不分组（即一个组）只返回一个记录。

为null的列参与判断时，所属记录不参与查询。

既然having是搭配group by用到，而where又不能与聚合函数连用，那用嵌套查询：

SELECT *FROM t_sc sc WHERE sc.Grade>=(SELECT AVG(sc.Grade) FROM t_sc sc)

42查询选修了全部课程的学生姓名

以得到各个学生的课程数目表为突破口。

SELECT sc.Sno,COUNT(sc.Cno) AS coursenum FROM t_sc sc

GROUP BY sc.Sno HAVING coursenum=3;

也可以：

SELECT sc.Sno FROM t_sc sc

GROUP BY sc.Sno HAVING COUNT(sc.Cno)=3;

group by是配合查询中的聚集函数用的。

43查询至少选修了 学生 2009002 选修的全部课程的学生号码

SELECT sc.Sno,COUNT(sc.Cno) AS num FROM t_sc sc

WHERE sc.Cno IN(SELECT sc.Cno FROM t_sc sc WHERE sc.Sno=\'20090002\')

GROUP BY sc.Sno HAVING num=(SELECT COUNT(*) FROM t_sc sc WHERE sc.Sno=\'20090002\');

mysql中的整数数据是从1开始的，没有0；

50.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”

号课和“2”号课的成绩

SELECT sc1.Sno,sc1.grade,sc2.grade FROM t_sc sc1,t_sc sc2

WHERE sc1.Sno=sc2.Sno AND sc1.Cno=1 AND sc2.Cno=2 AND sc1.Grade>sc2.Grade;

思考题：如何查询得到一个包含学号和所有所选课程的成绩？

49.

SELECT sc.Sno,COUNT(*) FROM t_sc sc WHERE sc.Grade>80 GROUP BY sc.Sno

HAVING COUNT(*)>=2;

等于：

SELECT sc.Sno FROM t_sc sc WHERE sc.Grade>80 GROUP BY sc.Sno

HAVING COUNT(*)>=2;

只要出现了聚集函数，不管在group by 前或后，group by都能得到正确的使用。

：聚集函数的加入相当于增加了一个列，该列的值的计算以分组为界限。

48.

SELECT s.Sname,s.Sno FROM t_student s WHERE s.Sno

NOT IN((SELECT sc.Sno FROM t_course c,t_sc sc

WHERE c.Cno=sc.Cno AND c.Cname=\'数据库\'));

45.

UPDATE t_sc AS sc SET grade=0 WHERE sc.Sno

IN(

SELECT s.Sno FROM t_student s WHERE s.Sdept=\'cs\'

);