学生选课数据库SQL语句45道练习题整理及mysql常用函数（20161019）

学生选课数据库SQL语句45道练习题：

一、            设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。

                 表1-1数据库的表结构

表（一）Student (学生表)                        

属性名	数据类型	可否为空	含 义
Sno	varchar (20)	否	学号（主码）
Sname	varchar (20)	否	学生姓名
Ssex	varchar (20)	否	学生性别
Sbirthday	datetime	可	学生出生年月
Class	varchar (20)	可	学生所在班级

表（二）Course（课程表）    主外码（主外键）

属性名	数据类型	可否为空	含 义
Cno	varchar (20)	否	课程号（主码）
Cname	varchar (20)	否	课程名称
Tno	varchar (20)	否	教工编号（外码）

表（三）Score(成绩表)

属性名	数据类型	可否为空	含 义
Sno	varchar (20)	否	学号（外码）
Cno	varchar (20)	否	课程号（外码）
Degree	Decimal(4,1)	可	成绩
主码：Sno+ Cno

表（四）Teacher(教师表)

属性名	数据类型	可否为空	含 义
Tno	varchar (20)	否	教工编号（主码）
Tname	varchar (20)	否	教工姓名
Tsex	varchar (20)	否	教工性别
Tbirthday	datetime	可	教工出生年月
Prof	varchar (20)	可	职称
Depart	varchar (20)	否	教工所在部门

表1-2数据库中的数据

表（一）Student

Sno	Sname	Ssex	Sbirthday	class
108	曾华	男	1977-09-01	95033
105	匡明	男	1975-10-02	95031
107	王丽	女	1976-01-23	95033
101	李军	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陆君	男	1974-06-03	95031

 

表（二）Course

Cno	Cname	Tno
3-105	计算机导论	825
3-245	操作系统	804
6-166	数字电路	856
9-888	高等数学	831

表（三）Score

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

表（四）Teacher

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	李诚	男	1958-12-02	副教授	计算机系
856	张旭	男	1969-03-12	讲师	电子工程系
825	王萍	女	1972-05-05	助教	计算机系
831	刘冰	女	1977-08-14	助教	电子工程系

1、 查询Student表中的所有记录的Sname、Ssex和Class列。

2、 查询教师所有的单位即不重复的Depart列。

3、 查询Student表的所有记录。

4、 查询Score表中成绩在60到80之间的所有记录。

5、 查询Score表中成绩为85，86或88的记录。

6、 查询Student表中“95031”班或性别为“女”的同学记录。

7、 以Class降序查询Student表的所有记录。

8、 以Cno升序、Degree降序查询Score表的所有记录。

9、 查询“95031”班的学生人数。

10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

11、 查询每门课的平均成绩。

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

13、查询分数大于70，小于90的Sno列。

14、查询所有学生的Sname、Cno和Degree列。

15、查询所有学生的Sno、Cname和Degree列。

16、查询所有学生的Sname、Cname和Degree列。

17、 查询“95033”班学生的平均分。

18、 假设使用如下命令建立了一个grade表：

create table grade(low  int(3),upp  int(3),rank  char(1))

insert into grade values(90,100,’A’)

insert into grade values(80,89,’B’)

insert into grade values(70,79,’C’)

insert into grade values(60,69,’D’)

insert into grade values(0,59,’E’)

现查询所有同学的Sno、Cno和rank列。

19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

23、查询“张旭“教师任课的学生成绩。

24、查询选修某课程的同学人数多于5人的教师姓名。

25、查询95033班和95031班全体学生的记录。

26、  查询存在有85分以上成绩的课程Cno.

27、查询出“计算机系“教师所教课程的成绩表。

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

31、 查询所有教师和同学的name、sex和birthday.

32、查询所有“女”教师和“女”同学的name、sex和birthday.

33、 查询成绩比该课程平均成绩低的同学的成绩表。

34、 查询所有任课教师的Tname和Depart.

35 、 查询所有未讲课的教师的Tname和Depart.

36、查询至少有2名男生的班号。

37、查询Student表中不姓“王”的同学记录。

38、查询Student表中每个学生的姓名和年龄。

39、查询Student表中最大和最小的Sbirthday日期值。

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

41、查询“男”教师及其所上的课程。

42、查询最高分同学的Sno、Cno和Degree列。

43、查询和“李军”同性别的所有同学的Sname.

44、查询和“李军”同性别并同班的同学Sname.

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

 

答案：

1.select sname,ssex,class from student
2.select distinct depart from teacher
3.select * from student
4.select * from score where degree btween 60 and 80;
5.select * from score where degree in(85,86,88)
6.select * from student where class='95031' or ssex='女'
7.select * from student order by class desc
8.select * from score order by cno,degree desc
9.select count(*) from student where class='95031'
10.select sno,cno from score where degree=(select max(degree) from score)
select * from score order by degree desc limit 0,1
11.select cno,avg(degree) from score group by cno

12.select avg(degree) from score where cno like'3%' and cno in(select cno from score group by cno having count(*)>4)

select avg(degree) from score group by cno having count(*)>4 and cno like '3%'

13.select sno from score where degree>70 and degree<90

14.select student.sname,score.cno,score.degree from student,score where student.sno = score.sno

15.select score.sno,course.cname,score.degree from score,course where score.cno=course.cno

16.select student.sname,course.cname,degree from score,student,course where student.sno=score.sno and score.cno=course.cno

17.select avg(degree) from score where sno in(select sno from student where class='95033')

18.select sno,cno,rank from score,grade where degree between low and upp

19.(1)select * from score where cno = '3-105' and degree>(select max(degree) from score where sno='109')
(2)select * from score where cno = '3-105' and degree>(select max(degree) from score where sno='109' and cno='3-105')

20.(1)select * from score where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score)

(2)select * from score a where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score b where b.cno = a.cno)

21.select * from score where degree>(select degree from score where sno='109' and cno='3-105')

22.select sno,sname,sbirthday from student where YEAR(sbirthday) = (select YEAR(sbirthday) from student where sno='108')

23.select * from score where cno in(select cno from course where tno =(select tno from teacher where tname='张旭'))

24.select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5))

25.select * from student where class in('95033','95031')

26.select distinct cno from score where degree>85

27.select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'))

28.select tname,prof from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select tname,prof from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系')

select tname,prof from teacher where prof not in( select prof from teacher where depart='计算机系' and prof in(select prof from teacher where depart='电子工程系'))

29.select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245')

30.select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245')

31.select sname,ssex,sbirthday from student
union
select tname,tsex,tbirthday from teacher

32.select sname,ssex,sbirthday from student where ssex='女'
union
select tname,tsex,tbirthday from teacher where tsex='女'

33.select * from score a where degree<(select avg(degree) from score b where b.cno =a.cno )

34.select tname,depart from teacher where tno in(select tno from course where cno in(select cno from score))

35.select tname,depart from teacher where tno in(select tno from course where cno not in(select cno from score))

36.select class from student where ssex='男' group by class having count(*)>1

37.select * from student where sname not like '王%'

38.select sname,year(now())-year(sbirthday) from student

39.select max(sbirthday),min(sbirthday) from student

40.select * from student order by class desc,sbirthday

41.select tname,cname from teacher,course where teacher.tno = course.tno and tsex='男'

42.select * from score where degree=(select max(degree) from score)

43.select sname from student where ssex=(select ssex from student where sname='李军')

44.select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军')

45.select * from score where sno in(select sno from student where ssex='男') and cno in(select cno from course where cname='计算机导论')

总结：

1.联合主键：主码：aa+bb  代码：primary key(aa,bb)

2.若插入数据后出现      [Err] 1050 - Table 'student' already exists

把原来的表删除再运行代码就行了

3.查询不重复数据：select distinct a from b;

4.把一个表中Cno相同行分组并按Degree求平均值

select Cno,avg(Degree) from Score group by Cno having count(Cno)

5.至少高于：至少高于一个，用any

　高于：高于所有，用all

 

 

 mysql常用函数：

 

一、数学函数
　　数学函数主要用于处理数字，包括整型、浮点数等。

ABS(x)
返回x的绝对值　　

SELECT ABS(-1) -- 返回1

CEIL(x),CEILING(x)
返回大于或等于x的最小整数　　

SELECT CEIL(1.5) -- 返回2

FLOOR(x)
返回小于或等于x的最大整数　　

SELECT FLOOR(1.5) -- 返回1

RAND()
返回0->1的随机数　　

SELECT RAND() --0.93099315644334

RAND(x)
返回0->1的随机数，x值相同时返回的随机数相同　　

SELECT RAND(2) --1.5865798029924

PI()
返回圆周率(3.141593）　　

SELECT PI() --3.141593

TRUNCATE(x,y)
返回数值x保留到小数点后y位的值（与ROUND最大的区别是不会进行四舍五入）　　

SELECT TRUNCATE(1.23456,3) -- 1.234

ROUND(x,y)
保留x小数点后y位的值，但截断时要进行四舍五入　　

SELECT ROUND(1.23456,3) -- 1.235

POW(x,y).POWER(x,y)
返回x的y次方　　

SELECT POW(2,3) -- 8

SQRT(x)
返回x的平方根　　

SELECT SQRT(25) -- 5

EXP(x)
返回e的x次方　　

SELECT EXP(3) -- 20.085536923188

MOD(x,y)
返回x除以y以后的余数　　

SELECT MOD(5,2) -- 1

 

二、字符串函数
　　字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。

函数 说明

CHAR_LENGTH(s)
返回字符串s的字符数

SELECT CHAR_LENGTH('你好123') -- 5

CONCAT(s1,s2,...)
将字符串s1,s2等多个字符串合并为一个字符串

SELECT CONCAT('12','34') -- 1234

CONCAT_WS(x,s1,s2,...)
同CONCAT(s1,s2,...)函数，但是每个字符串直接要加上x

SELECT CONCAT_WS('@','12','34') -- 12@34

INSERT(s1,x,len,s2)
将字符串s2替换s1的x位置开始长度为len的字符串

SELECT INSERT('12345',1,3,'abc') -- abc45

UPPER(s),UCAASE(S)
将字符串s的所有字母变成大写字母

SELECT UPPER('abc') -- ABC

LOWER(s),LCASE(s)
将字符串s的所有字母变成小写字母

SELECT LOWER('ABC') -- abc

LEFT(s,n)
返回字符串s的前n个字符

SELECT LEFT('abcde',2) -- ab

RIGHT(s,n)
返回字符串s的后n个字符

SELECT RIGHT('abcde',2) -- de

LTRIM(s) 去掉字符串s开始处的空格
RTRIM(s) 去掉字符串s结尾处的空格
TRIM(s) 去掉字符串s开始和结尾处的空格
SELECT TRIM('@' FROM '@@abc@@') -- abc

REPEAT(s,n)
将字符串s重复n次

SELECT REPEAT('ab',3) -- ababab

SPACE(n) 返回n个空格

REPLACE(s,s1,s2)
将字符串s2替代字符串s中的字符串s1

SELECT REPLACE('abca','a','x') --xbcx

STRCMP(s1,s2) 比较字符串s1和s2

SUBSTRING(s,n,len) 获取从字符串s中的第n个位置开始长度为len的字符串

LOCATE(s1,s),POSITION(s1 IN s)
从字符串s中获取s1的开始位置

SELECT LOCATE('b', 'abc') -- 2

REVERSE(s)
将字符串s的顺序反过来

SELECT REVERSE('abc') -- cba

FIELD(s,s1,s2...)
返回第一个与字符串s匹配的字符串位置

SELECT FIELD('c','a','b','c') -- 3

 

三、日期时间函数
　　MySQL的日期和时间函数主要用于处理日期时间。
函数 说明

CURDATE(),CURRENT_DATE()
返回当前日期

SELECT CURDATE()
->2014-12-17

CURTIME(),CURRENT_TIME
返回当前时间

SELECT CURTIME()
->15:59:02

NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),

SYSDATE(),LOCALTIMESTAMP()

返回当前日期和时间

SELECT NOW()
->2014-12-17 15:59:02

YEAR(d),
MONTH(d)
DAY(d)
返回日期d中的月份值，1->12

SELECT MONTH('2011-11-11 11:11:11')
->11

MONTHNAME(d)
返回日期当中的月份名称，如Janyary

SELECT MONTHNAME('2011-11-11 11:11:11')
->November

DAYNAME(d)
返回日期d是星期几，如Monday,Tuesday

SELECT DAYNAME('2011-11-11 11:11:11')
->Friday

DAYOFWEEK(d)
日期d今天是星期几，1星期日，2星期一

SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6

WEEKDAY(d)
日期d今天是星期几，

0表示星期一，1表示星期二

WEEK(d)，WEEKOFYEAR(d)
计算日期d是本年的第几个星期，范围是0->53

SELECT WEEK('2011-11-11 11:11:11')
->45

DAYOFYEAR(d)
计算日期d是本年的第几天

SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315

DAYOFMONTH(d)
计算日期d是本月的第几天

SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11

QUARTER(d)
返回日期d是第几季节，返回1->4

SELECT QUARTER('2011-11-11 11:11:11')
->4

HOUR(t)
返回t中的小时值

SELECT HOUR('1:2:3')
->1

MINUTE(t)
返回t中的分钟值

SELECT MINUTE('1:2:3')
->2

SECOND(t)
返回t中的秒钟值

SELECT SECOND('1:2:3')
->3

 

四、系统信息函数
　　系统信息函数用来查询MySQL数据库的系统信息。

函数 作用
VERSION()
返回数据库的版本号

SELECT VERSION()
->5.0.67-community-nt

CONNECTION_ID() 返回服务器的连接数
DATABASE()、SCHEMA 返回当前数据库名
USER()、SYSTEM_USER()
返回当前用户

五、加密函数
　　加密函数是MySQL用来对数据进行加密的函数。

　　1、PASSWORD(str)

　　该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密。

SELECT PASSWORD('123')
->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
　　2、MD5

　　MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。

SELECT md5('123')
->202cb962ac59075b964b07152d234b70

　　3、ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)

　　ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对，需要同样的密码才能够解密。

SELECT ENCODE('123','xxoo')
->;vx
SELECT DECODE(';vx','xxoo')
->123

insert into login values('lch','alvin',encode('123','xxoo'),'50')

select Name,decode(Password,'xxoo') from login where username='lch'