VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > MySQL >
  • MySQL教程之mysql新手进阶02

云想衣裳花想容,春风拂槛露华浓。
若非群玉山头见,会向瑶台月下逢。

现在有一教学管理系统,具体的关系模式如下:
Student (no, name, sex, birthday, class)
Teacher (no, name, sex, birthday, prof, depart)
Course (cno, cname, tno)
Score (no, cno, degree)
其中表中包含如下数据:
Course表:

Score表:

Student表:

Teacher表:

根据上面描述完成下面问题:
(注意:注意保存脚本,尤其是DDL和DML,以便进行数据还原)
DDL
1.写出上述表的建表语句。

命令:
create table Student (no int, name varchar(30), sex char(2), birthday datetime, class int);
create table Teacher (no int, name varchar(30), sex char(2), birthday datetime, prof varchar(20), depart varchar(30));
create table Course (cno varchar(10), cname varchar(30), tno int);
create table Score (no int, cno varchar(10), degree float);
DML
2.给出相应的INSERT语句来完成题中给出数据的插入。

命令:
insert into Student values(5001,'李勇','男','1987-07-22 00:00:00.000',95001);
insert into Student values(5002,'刘晨','女','1987-11-15 00:00:00.000',95002);
insert into Student values(5003,'王敏','女','1987-10-5 00:00:00.000',95001);
insert into Student values(5004,'李好尚','男','1987-9-25 00:00:00.000',95003);
insert into Student values(5005,'李军','男','1987-7-17 00:00:00.000',95004);
insert into Student values(5006,'范新位','女','1987-6-18 00:00:00.000',95005);
insert into Student values(5007,'张霞东','女','1987-8-29 00:00:00.000',95006);
insert into Student values(5008,'赵薇','男','1987-6-15 00:00:00.000',95007);
insert into Student values(5009,'钱民将','女','1987-6-23 00:00:00.000',95008);
insert into Student values(5010,'孙俪','女','1987-9-24 00:00:00.000',95002);
insert into Student values(108,'赵里','男','1987-6-15 00:00:00.000',95007);
insert into Student values(109,'丘处机','男','1987-6-23 00:00:00.000',95008);
insert into Student values(107,'杨康','男','1987-9-24 00:00:00.000',95001);

insert into Teacher values('1','李卫','男','1957-11-5','教授','电子工程');
insert into Teacher values('2','刘备','男','1967-10-9','副教授','math');
insert into Teacher values('3','关羽','男','1977-9-20','讲师','cs');
insert into Teacher values('4','李修','男','1957-6-25','教授','elec');
insert into Teacher values('5','诸葛亮','男','1977-6-15','教授','计算机系');
insert into Teacher values('6','殷素素','女','1967-1-5','副教授','cs');
insert into Teacher values('7','周芷若','女','1947-2-23','教授','cs');
insert into Teacher values('8','赵云','男','1980-6-15','副教授','计算机系');
insert into Teacher values('9','张敏','女','1985-5-5','助教','cs');
insert into Teacher values('10','黄蓉','女','1967-3-22','副教授','cs');
insert into Teacher values('11','张三','男','1967-3-22','副教授','cs');

insert into Course values('3-101','数据库',1);
insert into Course values('5-102','数学',3);
insert into Course values('3-103','信息系统',4);
insert into Course values('3-104','操作系统',6);
insert into Course values('3-105','数据结构',4);
insert into Course values('3-106','数据处理',5);
insert into Course values('4-107','Pascal语言',5);
insert into Course values('4-108','c++',7);
insert into Course values('4-109','Java',8);
insert into Course values('3-245','数据挖掘',10);
insert into Course values('3-111','软件工程',11);

insert into Score values(5001,'3-105',69);
insert into Score values(5001,'5-102',55);
insert into Score values(5003,'4-108',85);
insert into Score values(5004,'3-105',77);
insert into Score values(5005,'3-245',100);
insert into Score values(5006,'3-105',53);
insert into Score values(5003,'4-109',45);
insert into Score values(5008,'3-105',98);
insert into Score values(5004,'4-109',68);
insert into Score values(5010,'3-105',88);
insert into Score values(5003,'3-105',98);
insert into Score values(5005,'4-109',68);
insert into Score values(5002,'3-105',88);
insert into Score values(107,'3-105',98);
insert into Score values(108,'4-109',68);
insert into Score values(109,'3-105',88);
insert into Score values(109,'4-109',80);
insert into Score values(107,'3-111',88);
insert into Score values(5003,'3-111',80);
单表查询
3.以class降序输出student的所有记录(student表全部属性)
命令:
select * from Student order by class desc;

4.列出教师所在的单位depart(不重复)。
命令:
select count(depart) from Teacher;

5.列出student表中所有记录的name、sex和class列
命令:
select name, sex, class from Student;

6.输出student中不姓王的同学的姓名。
命令:
select name from Student where name not like '王%';

7.输出成绩为85或86或88或在60-80之间的记录(no,cno,degree)
命令:
select no, cno, degree from Score where degree in(85, 86, 88) or degree between 60 and 80;

8.输出班级为95001或性别为‘女’ 的同学(student表全部属性)
命令:
select * from Student where class = 95001 or sex = '女';

9.以cno升序、degree降序输出score的所有记录。(score表全部属性)
命令:
select * from Score order by cno asc, degree desc;

10.输出男生人数及这些男生分布在多少个班级中
命令:
select count(*), count(distinct class) from Student where sex = '男';

11.列出存在有85分以上成绩的课程编号。
命令:
select cno from Score where degree > 85 group by cno;

12.输出95001班级的学生人数
命令:
select count(*) from Student where class = 95001;

13.输出‘3-105’号课程的平均分
命令:
select avg(degree) from Score where cno = '3-105';

14.输出student中最大和最小的birthday日期值
命令:
select max(birthday), min(birthday) from Student;

15.显示95001和95004班全体学生的全部个人信息(不包括选课)。(student表全部属性)
命令:
select * from Student where class = 95001 or class = 95004;

聚合查询
16.输出至少有5个同学选修的并以3开头的课程的课程号,课程平均分,课程最高分,课程最低分。
命令:select cno, avg(degree), max(degree), min(degree) from Score where cno like '3%' group by cno having count(*) >= 5;

17.输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名
命令:
select no,name from Student where no in(select no from Score group by no having min(degree) > 70 and max(degree) < 90);

18.显示所教课程选修人数多于5人的教师姓名
命令:
select name from Teacher where no in(select tno from
Course where cno in (select cno from score
group by cno having count(cno) > 5));

19.输出’95001’班级所选课程的课程号和平均分
命令:select cno, avg(degree) from Score where no in (select no from Student where class = 95001) group by cno;

20.输出至少有两名男同学的班级编号。
命令:select class from Student where sex = '男' group by class having count(*) >= 2;

多表查询
21.列出与108号同学同年出生的所有学生的学号、姓名和生日
命令:select a.no, a.name, a.birthday from Student a, Student b where b.no = 108 and year(b.birthday) = year(a.birthday)

22.列出存在有85分以上成绩的课程名称
命令:
select cname from Course where cno in (select cno from Score where degree > 85 group by cno);

select cname from Course, (select cno from Score where Score.degree > 85 group by cno) t where Course.cno = t.cno;

23.列出“计算机系”教师所教课程的成绩表(课程编号,课程名,学生名,成绩)。
命令:
select Score.cno, cname, Student.name, degree from Teacher, Course, Student, Score where Teacher.depart = '计算机系' and Teacher.no = Course.tno and Course.cno = Score.cno and Student.no = Score.no;

24.列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息,要求输出每个老师的姓名(name)和(职称)
命令:select a.name, a.prof, b.name, b.prof from Teacher a, Teacher b where a.depart in ('计算机系', '电子工程') and b.depart in('计算机系', '电子工程') and a.prof != b.prof and a.depart != b.depart;

25.列出所有处于不同班级中,但具有相同生日的学生,要求输出每个学生的学号和姓名。(提示:使用datediff函数,具体用法可以参考:http://hcmfys.javaeye.com/blog/588844)
命令:select a.no, a.name, b.no, b.name from Student a, Student b where a.class != b.class and datediff(day, a.birthday, b.birthday) = 0;

26.显示‘张三’教师任课的学生姓名,课程名,成绩
命令:select Student.name, cname, degree from Teacher, Student, Course, Score where Teacher.name = '张三' and Teacher.no = Course.tno and Course.cno = Score.cno and Student.no = Score.no;

27.列出所讲课已被选修的教师的姓名和系别
命令:select distinct name, depart from Teacher, Course, Score where Teacher.no = Course.tno and Course.cno = Score.cno;

28.输出所有学生的name、no和degree。(degree为空的不输出和为空的输出两种情况)。
命令:select name, Student.no, degree from Student, Score where Student.no = Score.no;
select name, Student.no, degree from Student left join Score on Student.no = Score.no;

29.列出所有任课教师的name和depart。(从课程选修和任课两个角度考虑)
命令:select distinct name, depart from Teacher, Course where Teacher.no = Course.tno;
select distinct name, depart from Teacher, Course, Score where Teacher.no = Course.tno and Score.cno = Course.cno;

30.输出男教师所上课程名称。
命令:
select cname from Teacher, Course where Teacher.sex = '男' and Course.tno = Teacher.no;

31.出与“李军”同性别的所有同学的name。
命令:
select a.name from Student a, Student b where a.sex = b.sex and b.name = '李军';

32.输出选修“数据结构”课程的男同学的成绩。
命令:
select degree from Score, Course, Student where Score.cno = Course.cno and Course.cname = '数据结构' and Student.no = Score.no and Student.sex = '男';

33.列出选修编号为‘3-105’课程且该门课程成绩比课程‘3-111’的最高分要高的cno,no和degree。
命令:select Score.cno, no, degree from Score, (select cno, max(degree) maxdegree from Score group by cno) t where Score.cno = '3-105' and t.cno = '3-111' and Score.degree > t.maxdegree;

子查询
34.输出score中成绩最高的学号和课程号
命令:select no, cno from Score where degree = (select max(degree) from Score);

35.输出选修3-105课程,其成绩高于109号同学在此课程所得成绩的所有同学的学号,姓名
命令:select a.no, Student.name from Score a, Score b, Student where a.cno = '3-105' and b.cno = '3-105'and b.no = 109 and a.degree > b.degree and a.no = Student.no;

36.列出成绩比该课程平均成绩低的同学的学号,成绩和该门课的平均成绩
命令:
select no, degree, avg_degree from Score, (select cno, avg(degree) avg_degree from Score group by cno) t where Score.degree < t.avg_degree and Score.cno = t.cno;

37.列出没有实际授课的教师的姓名和系别
命令:select distinct name, depart from Teacher where no not in (select tno from Course, Score where Course.cno = Score.cno);

38.列出选修了编号为‘3-105’课程且其成绩高于‘4-109’课程最高成绩的同学的 课程编号,学号和成绩
命令:
select cno, no, degree from Score where degree > (select max(degree) from Score where cno = '4-109') and cno = '3-105';

39.**列出符合下述条件的所有可能的同学配对(sno1,sname1,sno2,sname2,difference)。其中要求学号为sno1的sname1同学的所学课程的平均分大于学号为sno2的sname2同学的所学课程平均分,两个同学的课程平均分的差值difference为(sno1同学平均分-sno2同学平均分)
命令:
select a.no, a.name, b.no, b.name, c.avgdegree - d.avgdegree difference from Student a, Student b, (select no, avg(degree) avgdegree from Score group by no) c,(select no, avg(degree) avgdegree from Score group by no) d where a.no = c.no and b.no = d.no and c.avgdegree > d.avgdegree;


相关教程