mysql查询语句按课程增加列_MySQL查询语句实操练习

题目:新建一个数据库ClassManager，包括四个表：学生表(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)

可

成绩

表(四)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

助教

电子工程系

三.完成以下操作

0.根据上述题目和表格，创建相应数据库，创建相应表结构，并插入给定数据

create databaseClassManager；useClassManager1;create tablestudent (

snovarchar(20) not null comment '学号',

snamevarchar(20) not null comment '学生姓名',

ssexvarchar(20) not null comment '学生性别',

sbirthdaydatetime comment '学生出生年月',

classvarchar(20) comment '学生所在班级');create tablecourse (

cnovarchar(20) not null comment '课程号',

cnamevarchar(20) not null comment '课程名称',

tnovarchar(20) not null comment '教工编号');create tablescore (

snovarchar(20) not null comment '学号',

cnovarchar(20) not null comment '课程号',

degreedecimal(4,1) comment '成绩');create tableteacher (

tnovarchar(20) not null comment '教工编号',

tnamevarchar(20) not null comment '教工姓名',

tsexvarchar(20) not null comment '教工性别',

tbirthdaydatetime comment '教工出生年月',

profvarchar(20) comment '职称',

departvarchar(20) not null comment '教工所在部门');insert into student (sno, sname, ssex, sbirthday, class) values ('108', '曾华','男','1977-09-01','95033');insert into student (sno, sname, ssex, sbirthday, class) values ('105', '匡明','男','1975-10-02','95031');insert into student (sno, sname, ssex, sbirthday, class) values ('107', '王丽','女','1976-01-23','95033');insert into student (sno, sname, ssex, sbirthday, class) values ('101', '李军','男','1976-02-20','95033');insert into student (sno, sname, ssex, sbirthday, class) values ('109', '王芳','女','1975-02-10','95031');insert into student (sno, sname, ssex, sbirthday, class) values ('103', '陆君','男','1974-06-03','95031');insert into course (cno, cname, tno) values ('3-105', '计算机导论', '825');insert into course (cno, cname, tno) values ('3-245', '操作系统', '804');insert into course (cno, cname, tno) values ('6-166', '数字电路', '856');insert into course (cno, cname, tno) values ('9-888', '高等数学', '831');insert into score (sno, cno, degree) values ('103', '3-245', '86');insert into score (sno, cno, degree) values ('105', '3-245', '75');insert into score (sno, cno, degree) values ('109', '3-245', '68');insert into score (sno, cno, degree) values ('103', '3-105', '92');insert into score (sno, cno, degree) values ('105', '3-105', '88');insert into score (sno, cno, degree) values ('109', '3-105', '76');insert into score (sno, cno, degree) values ('101', '3-105', '64');insert into score (sno, cno, degree) values ('107', '3-105', '91');insert into score (sno, cno, degree) values ('108', '3-105', '78');insert into score (sno, cno, degree) values ('101', '6-166', '85');insert into score (sno, cno, degree) values ('107', '6-166', '79');insert into score (sno, cno, degree) values ('108', '6-166', '81');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('804', '李诚','男','1958-12-02','副教授','计算机系');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('856', '张旭','男','1969-03-12','讲师','电子工程系');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('825', '王萍','女','1972-05-05','助教','计算机系');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('831', '刘冰','女','1977-08-14','助教','电子工程系');

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

select sname, ssex, class from student;

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

select distinct depart from teacher;

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

select * from student;

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

select * from score where degree between 60 and 80;

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

select * from score where degree in (85, 86, 88);

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

select * from student where class = '95031' or ssex = '女';

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

select * from student order by class desc;

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

select * from score order by cno asc, degree desc;

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

select count(*) from student where class = '95031';

10.查询Score表中的最高分的学生学号和课程号。

select * from score sc where degree = (select max(degree) as max from score where cno = sc.cno group by cno);

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

select cno, avg(degree) as avg from score group by cno;

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

select cno, avg(degree) as avg from score where cno like '3%' group by cno having count(*) >= 5;

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

select sno from score where degree > 70 and degree < 90;

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

select sname, cno, degree from student s left join score sc on sc.sno = s.sno;

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

select s.sno, c.cname, sc.degree from student s left join score sc on sc.sno = s.sno left join course c on c.cno = sc.cno;

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

select s.sname, c.cname, sc.degree from student s left join score sc on sc.sno = s.sno left join course c on c.cno = sc.cno;

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

select cno , avg(degree) from score sc where sc.sno in (select sno from student where class = '95033') group by cno;

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

select * from score where sno = '109' and cno = '3-105';

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

select * from score where cno = '3-105' and degree > (select degree from score where sno = '109'and cno = '3-105'); --此处理解为只看‘3-105’课程成绩

select * from score where degree > (select degree from score where sno = '109'and cno = '3-105'); --此处为各科成绩都算。

20.查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。(使用year()方法对生日字段求年份)

select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in ('108', '101'));

21.查询“张旭“教师任课的学生成绩。

select sc.*from score sc inner join course c on c.cno = sc.cno inner join teacher t on t.tno = c.tno where t.tname = '张旭'; --合并多个表然后查询

select * from score where cno in(select cno from course where tno in(select tno from teacher where tname ='张旭')); --嵌套查询

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

select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*) > 5));

23.查询95033班和95031班全体学生的记录。

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

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

select distinct cno from score where degree > 85;

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

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

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

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 = '计算机系');

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

select sname as `name`, ssex as `sex`, sbirthday as `birthday` from student union select tname, tsex,tbirthday from teacher;

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

select sname as `name` , ssex as `sex`, sbirthday as `birthday`from student where ssex = '女' union select tname, tsex , tbirthday from teacher where tsex = '女';

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

select * from score sc where degree < (select avg(degree) as avg from score where cno = sc.cno group by cno);

30.查询所有任课教师的Tname和Depart。

select tname, depart from teacher where tno in (select tno from course);

31.查询所有未讲课的教师的Tname和Depart。

select tname, depart from teacher where tno in (select tno from course where cno not in (select cno from score)); --此处理解为没有成绩的课程为未讲课的课程。

32.查询至少有2名男生的班号。

select class from student where ssex = '男' group by class having count(*) >= 2;

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

select * from student where sname not like '王%';

34.查询Student表中每个学生的姓名和年龄(使用year()方法对日期字段)。

select sname, (year(now()) - year(sbirthday)) as nian_ling from student;

35.查询Student表中最大和最小的Sbirthday日期值。

select max(sbirthday) as max,min(sbirthday) as min from student;

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

select * from student order by class desc, sbirthday asc;

37.查询“男”教师及其所上的课程。

select t.*, c.cno, c.cname from teacher t inner join course c on c.tno = t.tno where t.tsex = '男';

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

select sno, cno, degree from score sc where degree = (select max(degree) as max from score where cno = sc.cno group by cno);

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

select sname from student where ssex in (select ssex from student where sname = '李军');

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

select sname from student where ssex in (select ssex from student where sname = '李军') and class in (select class from student where sname = '李军');

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

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