【数据库】MySQL习题及答案（教务管理系统_MySQL_5.7）

✨声明

• 不保证习题答案完全正确，仅供参考
• mysql：5.7
• 推荐软件：navicat premium

学习sql语句需要勤于练习！

✨各表字段说明

course

cid	课程序号，主键
pcid	先修课
did	开课系编号，外键
cname	课程名称
ccredit	学分
chour	学时
cattr	课程类型
cnum	选课人数

dept

did	部门编号，主键
dname	部门名称
daddr	地址
dtele	电话
demai	邮箱

grade

gid	班级编号，主键
did	系编号，外键
tid	教师工号，外键
gname	班级名称
gyear	入学年份

information

iid	序号，主键
cid	课程序号，外键
tid	教师编号，外键
gid	班级编号，外键
iroom	上课教室
iweek	周几上课
itimeseg	第几节上课
iterm	开课学年/学期

sc

scid	选课序号，主键
cid	课程编号，外键
sid	学号，外键
scterm	开课学年/学期
scscore1	平时成绩
scscore2	期中成绩
scscore3	期末成绩
scscore	总评成绩

student

sid	学号，主键
gid	班级id，外键
sname	姓名
ssexy	性别
sbdatedate	出生日期
stele	联系电话

teacher

tid	教师工号，主键
did	系编号，外键
tname	姓名
tsexy	性别
tbdate	出生日期
tfield	研究领域
tprof	职称
ttele	联系电话
tqq	qq号码
temail	邮箱
tmsn	msn

✨经验分享

• 多表查询注意

  首先观察题目涉及哪几张表，需要查询那些数据项

  （假设涉及到三张表，需要把三张表中所有有相同字段的全部自然连接起来，然后再进行查询）

• 注意group by后面的字段不要填写错误

• having需要写在group by后面

• 一张表涉及两个及以上操作需要建立临时表

✨习题

eid	econtext	elevel
1	显示院系信息表中的所有信息	1
2	显示院系信息表中的部门编码、部门名称信息	1
3	显示所有女教师的工号、姓名、性别信息	1
4	显示cs系的老师所有个人信息	1
5	显示cs系与it系所有老师的全部个人信息	1
6	显示女教授的姓名、性别、职称与部门编号信息	1
7	显示研究领域为数据库的老师的姓名、研究领域、联系电话、所在部门	1
8	显示40岁以下老师的姓名、出生日期	1
9	显示5月份出生的姓名、性别、出生日期、联系电话	1
10	显示姓李的老师的所有信息	1
11	显示选修了1号课程的同学的学号	1
12	显示同时选修了1号与2号课程的同学的学号	2
13	显示012005002号同学选修的课程号	1
14	显示012005002号同学的全部选修信息	1
15	显示期末成绩不及格的同学的学号、课程号与期末成绩	1
16	显示选修了1号课程的人数	1
17	显示1号课程期末考试成绩的平均分	1
18	显示012005002同学的期末成绩的平均分	1
19	显示2008年每门课程期末成绩的平均分，显示课程号与平均分，并按降序排列	2
20	显示周4有课的教室	1
21	显示周4有课的老师的工号	1
22	显示nb222教室排课的情况	1
23	显示1号课程上课的教室、老师工号、上课时间	1
24	显示02004号老师上课的教室与时间	1
25	显示第4节有课的教室、课程号、教师工号	1
26	统计每个教师每周上课的次数及总时长(每节课45分钟)	2
27	统计每个班每周上课的课时数，显示班级编号和课时数，并按降序排列	2
28	显示无前导课的课程的全部信息	1
29	显示cs系所开课程的课程号与课程名	1
30	显示学分大于3的所有课程的课程名与课时	1
31	按开课院系统计每个院系开课的学分数，显示院系编号和总学分，并按降序排列	2
32	显示学生中所有男生的全部信息	1
33	显示01班所有学生的信息	1
34	显示03班所有女生的信息	1
35	显示刘山同学的电话号码	1
36	显示所有女生的学号、姓名与班级编号	1
37	统计每个班级的人数，显示班级编号，人数，并按降序排列	1
38	显示人数5人以上班级的班级编号和人数，并按升序排列	2
39	显示年龄在18岁以下的学生的全部信息	1
40	统计18岁以下学生的总人数	1
41	统计每个班20岁以下的人数，显示班级编号、人数	2
42	统计每个年龄段的人数，按照年龄升序排列	2
43	显示姓名中有"白云"的同学的所有信息	1
44	显示白姓同学的姓名、性别、班级名称、联系电话	2
45	显示cs系的班级名称及入学年份	1
46	显示没有班导的班级的所有信息	1
47	显示2008年入学班级的所有信息	1
48	显示所有学生的详细信息，包括学号、姓名、性别、年龄、班级名称，入学年份	2
49	显示信息科学与技术系同学的名单，包括学号、姓名、性别、年龄、班级名称、入学年份	2
50	显示选修了"数据库"课程的所有同学的学号、姓名	2
51	显示白云同学的班主任老师的姓名、联系电话	2
52	显示白云同学所在院系的名称、办公地点与联系电话	2
53	统计计算机科学与技术系每个同学已经修完的学分，显示学号、姓名、学分总数	2
54	显示张飞同学已修课程的课程号，课程名及期末成绩	2
55	显示计算机科学与技术系、信息科学与技术系的班级名称、入学年份、班导名称与联系电话	2
56	显示2008年入学的同学的学号、姓名、班级名称	2
57	显示已修数据库的同学的学号、姓名及期末成绩	2
58	显示期末平均成绩75分以上的课程名称与期末平均成绩	2
59	显示期末平均成绩80分以上同学的学号、姓名与期末平均成绩	2
60	显示一周课时数为5节及以上的教师的姓名与研究领域	2
61	按照班级统计期末平均成绩，显示班级名称与平均成绩	2
62	按照学期统计计算机科学与技术系学生的期末平均成绩，显示学期名称与平均成绩	2
63	统计每个院系一周的课时数，显示院系名称与课时数	2
64	显示没有选修任何课程的学生学号、姓名、班级名称	2
65	显示上过李飞老师的课的学生的学号、姓名与联系电话	2
66	显示一周6节课及以上的课程名称、学分	2
67	显示一周6节课及以上班级名称	2
68	查询周四上午第3节有课的同学的学号、姓名与班级名称	3
69	显示期末成绩没有不及格课程的班级的名称	3
70	显示已修数据库的同学信息，包括学号、姓名、班级名称	3
71	显示期中成绩不及格1门以上的同学学号、姓名、门数	3
72	统计每个班级期末成绩的最高分，显示班级名称、期末最高成绩	2
73	显示一周8节课及以上的学生的名单，显示学号、姓名、班级名称	3
74	显示计算机科学与技术1班一周上课的时间、地点，课程名称(周几，哪几节课，哪个教室)	3
75	统计教授每周上课的课时数，显示姓名、课时数	3
76	显示没有班导师的班级名称、院系名称	2
77	显示指导过两个班级以上的班导的姓名、所指导的班级名称	2
78	为洪玉飞老师(教师编号：03012)安排软件工程1班(班级编号：04)的数据库课程(课程编号：1)，上课教师为nb201	1
79	计算机科学与技术3班所有学生都选修了2009-2010-1的操作系统(课程编号为4)，请记录相关信息	1
80	理学院新开一门课程“数学建模”，课程编号20, 学分4，学时72，选修课程，最多选课人数为50	1
81	将李飞同学的联系方式改为660101	1
82	计算所有学生的总评成绩，公式为：总评=平时*20%+实验*20%+期末*60%	1
83	将电子信息1班(班级编号：08)的班主任改为洪玉飞老师(无重名)	1
84	将课程"数据库"的上课教室改为nb111，授课教师改为李飞(教师编号：02001)	1
85	将学号为012005001的学生班级改为计算机科学与技术3班	1
86	删除所有期末成绩小于60分的选课记录	1
87	删除学号为012005001的所有选课记录	1
88	删除所有选修了"数据库"课程的选课记录	1
89	删除李飞老师(教师编号：02001)2008学年的排课记录	1
90	删除所有在nb1楼上课的排课记录	1
91	删除nb111教室在周四的排课记录	1
92	删除选修人数小于5的选课记录	1
93	删除未担任班导师并且未安排课程的教师记录	2
94	创建一个新的用户，用户名为[alogin]，允许该用户在本机访问教务数据库	0
95	授予新建数据库用户[alogin]对表student和sc的查询权限	0
96	授予新建数据库用户[alogin]对表student表sname列的更新权限	0
97	创建一个角色，授予对表course的查询和更新权限，并将该角色授权给新建数据库用户[alogin]	0
98	为student表增加约束条件，性别字段可以的取值为‘男’，‘女’	0
99	为student表增加约束条件：性别默认为‘男’	0
100	为student表增加约束条件：联系方式至少长度为6	0
101	为student表增加约束条件：出生日期小于当前时间	0
102	为dept表增加约束条件：院系名称必须唯一	0
103	为grade表增加约束条件：入学年份不能大于当前年份	0
104	为dept表增加约束条件：联系电话必须为8位数字	0
105	为information表增加约束条件：学期格式为“xxxx-xxxx-x”，其中xxxx为4位数字表示学年，x为1或者2，表示上下学期。	0
106	为sc表增加约束条件：各项成绩都在0-100之间	0
107	删除一个已经存在的约束条件，如果没有先建立约束	0
108	写一个触发器，使得course中的记录更新时，课程性质只能为“选修”或者“必修”。否则提醒更新失败	2
109	写一个触发器，使得sc表的新增记录满足下述条件： 公式为：总评=平时*20%+实验*20%+期末*60%	2

✨习题及答案

显示院系信息表中的所有信息

select *
from dept

显示院系信息表中的部门编码、部门名称信息

select did, dname
from dept

显示所有女教师的工号、姓名、性别信息

select tid, tname, tsexy
from teacher
where tsexy = '女'

显示cs系的老师所有个人信息

select *
from teacher
where did = 'cs'

显示cs系与it系所有老师的全部个人信息

select *
from teacher
where did = 'cs'
   or did = 'it'

显示女教授的姓名、性别、职称与部门编号信息

select tname, tsexy, tprof, did
from teacher
where tsexy = '女'
  and tprof = '教授' 

显示研究领域为数据库的老师的姓名、研究领域、联系电话、所在部门

select tname, tfield, ttele, dname
from dept,
     teacher
where tfield = '数据库'

显示40岁以下老师的姓名、出生日期

select tname, year (tbdate) tbdate
from teacher
where year (now())- year (tbdate) < 40

显示5月份出生的姓名、性别、出生日期、联系电话

select tname, tsexy, tbdate, ttele
from teacher
where month (tbdate)=5

显示姓李的老师的所有信息

select *
from teacher
where left (tname, 1)='李'

显示选修了1号课程的同学的学号

select sid
from sc
where cid = 1

显示同时选修了1号与2号课程的同学的学号

select sid
from sc
where cid = 1
  and sid in (select sid from sc where cid = 2)

显示012005002号同学选修的课程号

select cid
from sc
where sid = 012005002

显示012005002号同学的全部选修信息

select *
from sc
where sid = 012005002

显示期末成绩不及格的同学的学号、课程号与期末成绩

select sid, cid, scscore3
from sc
where scscore3 < 60

显示选修了1号课程的人数

select count(sid)
from sc
group by cid
having cid = 1

显示1号课程期末考试成绩的平均分

select cid, avg(scscore3)
from sc
group by cid
having cid = 1

显示012005002同学的期末成绩的平均分

select distinct sid, avg(scscore3)
from sc
group by sid
having sid = 012005002

显示2008年每门课程期末成绩的平均分，显示课程号与平均分，并按降序排列

select sid, scscore3, scterm
from sc
where scterm in ("2007-2008-2", "2008-2009-1")
order by scscore3 desc

显示周4有课的教室

select iroom
from information
where iweek = 4

显示周4有课的老师的工号

select tid
from information
where iweek = 4

显示nb222教室排课的情况

select distinct iweek, itimeseg
from information
where iroom = 'nb222'

显示1号课程上课的教室、老师工号、上课时间

select iroom, tid, iweek, itimeseg
from information
where cid = 1

显示02004号老师上课的教室与时间

select iroom, iweek, itimeseg
from information
where tid = '02004'

显示第4节有课的教室、课程号、教师工号

select iroom, cid, tid
from information
where itimeseg = 34
   or itimeseg = 345

统计每个教师每周上课的次数及总时长(每节课45分钟)

select tid,
       count(*)                   as cnt,
       sum(length(itimeseg) * 45) as itime
from information
group by tid

统计每个班每周上课的课时数，显示班级编号和课时数，并按降序排列

select gid, length(itimeseg) as cnt
from information
group by gid
order by cnt desc

显示无前导课的课程的全部信息

select *
from course
where pcid is null

显示cs系所开课程的课程号与课程名

select cid, cname
from course
where did = 'cs'

显示学分大于3的所有课程的课程名与课时

select cname, cnum
from course
where ccredit > 3

按开课院系统计每个院系开课的学分数，显示院系编号和总学分，并按降序排列

select cid, sum(ccredit)
from course
group by did
order by ccredit desc

显示学生中所有男生的全部信息

select *
from student
where ssexy = '男'

显示01班所有学生的信息

select *
from student
where gid = '01'

显示03班所有女生的信息

select *
from student
where gid = '03'
  and ssexy = '女'

显示刘山同学的电话号码

select stele
from student
where sname = '刘山'

显示所有女生的学号、姓名与班级编号

select sid, sname, gid
from student
where ssexy = '女'

统计每个班级的人数，显示班级编号，人数，并按降序排列

select gid, count(sname) as cnt
from student
group by gid
order by cnt desc

显示人数5人以上班级的班级编号和人数，并按升序排列

select gid, count(*) as cnt
from student
group by gid
having cnt > 5
order by cnt

显示年龄在18岁以下的学生的全部信息

select *from studentwhere year (now())- year (sbdate)<=18

统计18岁以下学生的总人数

select count(*)from studentwhere year (now())- year (sbdate)<=18

统计每个班20岁以下的人数，显示班级编号、人数

select gid, count(gid)
from student
where timestampdiff(year,sbdate, now()) < 20
group by gid

统计每个年龄段的人数，按照年龄升序排列

select timestampdiff(year,sbdate, now()) as age, count(*)
from student
group by age
order by age
-- select year(now())-year(sbdate) as age,count(*) from student group by age order by age

显示姓名中有白云的同学的所有信息

select *
from student
where sname like '%白云%'

显示白姓同学的姓名、性别、班级名称、联系电话

select student.sname, student.ssexy, grade.gname, student.stele
from student,
     grade
where sname like '白%'
  and student.gid = grade.gid

显示cs系的班级名称及入学年份

select did, gname, gyear
from grade
where did = 'cs'

显示没有班导的班级的所有信息

select *
from grade
where tid is null

显示2008年入学班级的所有信息

select *
from grade
where gyear = 2008

显示所有学生的详细信息，包括学号、姓名、性别、年龄、班级名称，入学年份

select student.sid, student.sname, student.ssexy, timestampdiff(year,sbdate, now()) as age, grade.gname, gyearfrom student,     gradewhere student.gid = grade.gid

显示信息科学与技术系同学的名单，包括学号、姓名、性别、年龄、班级名称、入学年份

select student.sid, student.sname, student.ssexy, timestampdiff(year,sbdate, now()) as age, grade.gname, gyearfrom student,     gradewhere student.gid = grade.gid  and grade.did = 'it'

显示选修了数据库课程的所有同学的学号、姓名

select student.sid, student.snamefrom student,     scwhere student.sid = sc.sid  and cid = 1

显示白云同学的班主任老师的姓名、联系电话

select teacher.tname, ttele
from student,
     grade,
     teacher
where student.sname = '白云'
  and student.gid = grade.gid
  and grade.tid = teacher.tid

显示白云同学所在院系的名称、办公地点与联系电话

select dept.dname, dept.daddr, dept.dtele
from student,
     grade,
     dept
where student.sname = '白云'
  and student.gid = grade.gid
  and grade.did = dept.did

统计计算机科学与技术系每个同学已经修完的学分，显示学号、姓名、学分总数

select student.sid, sname, sum(ccredit)
from student,
     grade,
     dept,
     course,
     sc
where student.gid = grade.gid
  and grade.did = dept.did
  and dept.dname = '计算机科学与技术系'
  and sc.sid = student.sid
  and scscore >= 60
  and sc.cid = course.cid
group by sid

显示张飞同学已修课程的课程号，课程名及期末成绩

select course.cid, course.cname, sc.scscorefrom student,     sc,     coursewhere student.sname = '张飞'  and student.sid = sc.sid  and sc.cid = course.cid

显示计算机科学与技术系、信息科学与技术系的班级名称、入学年份、班导名称与联系电话

select gname, gyear, tname, ttele
from grade,
     dept,
     teacher
where (dept.dname = '计算机科学与技术系' or dept.dname = '信息科学与技术系')
  and dept.did = grade.did
  and grade.tid = teacher.tid

显示2008年入学的同学的学号、姓名、班级名称

select student.sid, sname, grade.gname
from student,
     grade
where grade.gyear = 2008
  and student.gid = grade.gid

显示已修数据库的同学的学号、姓名及期末成绩

select distinct student.sid, sname, scscore
from student,
     sc,
     course
where course.cname = '数据库'

显示期末平均成绩75分以上的课程名称与期末平均成绩

select course.cname, avg(sc.scscore)
from course,
     sc
where course.cid = sc.cid
group by sc.cid
having avg(sc.scscore) > 75

显示期末平均成绩80分以上同学的学号、姓名与期末平均成绩

select student.sid, student.sname, sc.scscore
from student,
     sc
where student.sid = sc.sid
  and sc.scscore > 80

显示一周课时数为5节及以上的教师的姓名与研究领域

select tname, tfield
from information,
     teacher
where information.tid = teacher.tid
group by teacher.tid
having sum(length(itimeseg)) >= 5

按照班级统计期末平均成绩，显示班级名称与平均成绩

select gname, avg(sc.scscore)
from sc,
     grade,
     student
where student.sid = sc.sid
  and student.gid = grade.gid
group by grade.gid

按照学期统计计算机科学与技术系学生的期末平均成绩，显示学期名称与平均成绩

select iterm, avg(sc.scscore3)
from dept,
     grade,
     information,
     sc,
     student
where dept.dname = '计算机科学与技术系'
  and dept.did = grade.did
  and sc.sid = student.sid
  and student.gid = grade.gid
  and information.gid = grade.gid
group by iterm

统计每个院系一周的课时数，显示院系名称与课时数

select dept.dname, sum(length(itimeseg))
from dept,
     information,
     grade
where dept.did = grade.did
  and information.gid = grade.gid
group by dept.did

显示没有选修任何课程的学生学号、姓名、班级名称

select sid, sname, gname
from grade,
     student
where grade.gid = student.gid
  and sid not in (select distinct sid from sc)

显示上过李飞老师的课的学生的学号、姓名与联系电话

select distinct student.sid, student.sname, student.stele
from student,
     sc,
     information,
     teacher
where student.sid = sc.sid
  and information.tid = teacher.tid
  and sc.cid = information.cid
  and teacher.tid in
      (select tid
       from teacher
       where tname = '李飞')

select distinct student.sid, student.sname, student.stele
from grade,
     student,
     information,
     teacher,
     sc
where teacher.tname = '李飞'
  and teacher.tid = information.tid
  and information.cid = sc.cid
  and sc.sid = student.sid

显示一周6节课及以上的课程名称、学分

select course.cname, course.ccredit
from course,
     information
where course.cid = information.cid
group by information.cid
having sum(length(information.itimeseg)) > 6

显示一周6节课及以上班级名称

select grade.gname
from information,
     grade
where information.gid = grade.gid
group by information.gid
having sum(length(information.itimeseg)) > 6

查询周四上午第3节有课的同学的学号、姓名与班级名称

select distinct student.sid, student.sname, grade.gname
from student,
     grade,
     information
where student.gid = grade.gid
  and information.gid = grade.gid
  and information.gid = student.gid
  and information.iweek = 4
  and information.itimeseg like '%3%'
--   and (information.itimeseg = 123 or information.itimeseg = 345 or information.itimeseg = 34)

显示期末成绩没有不及格课程的班级的名称

select distinct grade.gname
from grade,
     sc,
     student
where sc.scscore >= 60
  and sc.sid = student.sid
  and student.gid = grade.gid

显示已修数据库的同学信息，包括学号、姓名、班级名称

select distinct student.sid, student.sname, grade.gname
from student,
     grade,
     sc,
     course
where student.gid = grade.gid
  and sc.cid = course.cid
  and student.sid = sc.sid
  and course.cname = '数据库'

显示期中成绩不及格1门以上的同学学号、姓名、门数

select student.sid, student.sname, count(*)
from student,
     sc
where sc.scscore2 < 60
  and student.sid = sc.sid
group by student.sid
having count(*) > 1

统计每个班级期末成绩的最高分，显示班级名称、期末最高成绩

select grade.gname, max(sc.scscore)
from grade,
     sc,
     student
where student.sid = sc.sid
  and grade.gid = student.gid
group by grade.gid

显示一周8节课及以上的学生的名单，显示学号、姓名、班级名称

select student.sid, student.sname, grade.gname
from student,
     grade,
     sc,
     information
where student.sid = sc.sid
  and student.gid = grade.gid
  and sc.cid = information.cid
group by student.sid
having sum(length(information.itimeseg)) >= 8

显示计算机科学与技术1班一周上课的时间、地点，课程名称(周几，哪几节课，哪个教室)

select information.iweek, information.itimeseg, information.iroom, course.cname
from information,
     course,
     grade
where grade.gname = '计算机科学与技术1班'
  and course.cid = information.cid
  and grade.gid = information.gid

统计教授每周上课的课时数，显示姓名、课时数

select teacher.tname, sum(length(information.itimeseg)) as coursecnt
from teacher,
     information
where teacher.tprof = '教授'
  and teacher.tid = information.tid
group by teacher.tid

显示没有班导师的班级名称、院系名称

select grade.gname, dept.dname
from grade,
     dept
where grade.did = dept.did
  and grade.tid is null

显示指导过两个班级以上的班导的姓名、所指导的班级名称

select tname, gname
from grade,
     teacher
where grade.tid = teacher.tid
  and grade.tid in (
    select grade.tid
    from grade
    where tid is not null
    group by grade.tid
    having count(gid) > 2)

为洪玉飞老师(教师编号：03012)安排软件工程1班(班级编号：04)的数据库课程(课程编号：1)，上课教师为nb201

insert
into information (iid, tid, gid, cid, iroom)
select max(iid) + '1', '03012', '04', '1', 'nb201'
from information

计算机科学与技术3班所有学生都选修了2009-2010-1的操作系统(课程编号为4)，请记录相关信息

insert into sc
    (scid, sid, cid, scterm)
select (@row_number:=@row_number+1) as scid, student.sid, '4' as cid, '2009-2010-1' as scterm
from student,
     grade,
     (select @row_number:=max(scid) from sc) as a
where student.gid = grade.gid
  and gname = '计算机科学与技术3班'

理学院新开一门课程“数学建模”，课程编号20, 学分4，学时72，选修课程，最多选课人数为50

insertinto course (cid, cname, ccredit, chour, cattr, cnum)values ('20', '数学建模', '4', '72', '选修', '50')

将李飞同学的联系方式改为660101

update studentset stele = '660101'where sname = '李飞'

计算所有学生的总评成绩，公式为：总评=平时20%+实验20%+期末*60%

update sc
set scscore = scscore1 * 0.2 + scscore2 * 0.2 + scscore3 * 0.6

将电子信息1班(班级编号：08)的班主任改为洪玉飞老师(无重名)

update grade
set tid = (select tid
           from teacher
           where tname = '洪玉飞')
where gid = 08

将课程数据库的上课教室改为nb111，授课教师改为李飞(教师编号：02001)

update information
set iroom = 'nb111',
    tid   = '02001'
where cid in
      (select cid
       from course
       where cname = '数据库')

将学号为012005001的学生班级改为计算机科学与技术3班

update student
set gid =
        (select gid
         from grade
         where gname = '计算机科学与技术3班')
where sid = '012005001'

删除所有期末成绩小于60分的选课记录

deletefrom scwhere scscore3 < 60

删除学号为012005001的所有选课记录

deletefrom scwhere sid = '012005001'

删除所有选修了数据库课程的选课记录

delete
from sc
where cid in
      (select cid
       from course
       where cname = '数据库')

删除李飞老师(教师编号：02001)2008学年的排课记录

delete
from information
where tid = '02001'
  and iterm like '%2008%'

删除所有在nb1楼上课的排课记录

delete
from information
where iroom like 'nb1%'

删除nb111教室在周四的排课记录

delete
from information
where iroom = 'nb111'
  and iweek = 4

删除选修人数小于5的选课记录

delete
from sc
where cid in
      (select *
       from ((select cid
              from sc
              group by cid
              having count(*) < 5) as tmp))

删除未担任班导师并且未安排课程的教师记录

delete
from teacher
where tid not in
      (select tid
       from grade
       where tid is not null)
  and tid not in
      (select tid
       from information
       where tid is not null)

创建一个新的用户，用户名为[alogin]，允许该用户在本机访问教务数据库

授予新建数据库用户[alogin]对表student和sc的查询权限

授予新建数据库用户[alogin]对表student表sname列的更新权限

创建一个角色，授予对表course的查询和更新权限，并将该角色授权给新建数据库用户[alogin]

为student表增加约束条件，性别字段可以的取值为‘男’，‘女’

为student表增加约束条件：性别默认为‘男’

为student表增加约束条件：联系方式至少长度为6

为student表增加约束条件：出生日期小于当前时间

为dept表增加约束条件：院系名称必须唯一

为grade表增加约束条件：入学年份不能大于当前年份

为dept表增加约束条件：联系电话必须为8位数字

为information表增加约束条件：学期格式为“xxxx-xxxx-x”，其中xxxx为4位数字表示学年，x为1或者2，表示上下学期。

为sc表增加约束条件：各项成绩都在0-100之间

删除一个已经存在的约束条件，如果没有先建立约束

写一个触发器，使得course中的记录更新时，课程性质只能为“选修”或者“必修”。否则提醒更新失败

写一个触发器，使得sc表的新增记录满足下述条件： 公式为：总评=平时20%+实验20%+期末*60%

✨建库sql

/*
 navicat premium data transfer

 source server         : dbms
 source server type    : mysql
 source server version : 50732
 source host           : localhost:3306
 source schema         : edu_schema

 target server type    : mysql
 target server version : 50732
 file encoding         : 65001

 date: 05/07/2021 14:19:35
*/

set names utf8mb4;
set foreign_key_checks = 0;

-- ----------------------------
-- table structure for course
-- ----------------------------
drop table if exists `course`;
create table `course`  (
  `cid` char(4) character set utf8 collate utf8_general_ci not null comment '课程序号，主键',
  `pcid` char(4) character set utf8 collate utf8_general_ci null default null comment '先修课',
  `did` char(2) character set utf8 collate utf8_general_ci null default null comment '开课系编号，外键',
  `cname` char(20) character set utf8 collate utf8_general_ci not null comment '课程名称',
  `ccredit` int(11) not null comment '学分',
  `chour` int(11) not null comment '学时',
  `cattr` char(6) character set utf8 collate utf8_general_ci not null comment '课程类型',
  `cnum` int(11) not null comment '选课人数',
  primary key (`cid`) using btree,
  index `fk_course_course`(`pcid`) using btree,
  index `fk_course_dept`(`did`) using btree,
  constraint `fk_course_course` foreign key (`pcid`) references `course` (`cid`) on delete set null on update set null,
  constraint `fk_course_dept` foreign key (`did`) references `dept` (`did`) on delete set null on update set null
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '课程表' row_format = dynamic;

-- ----------------------------
-- records of course
-- ----------------------------
insert into `course` values ('1', '5', 'cs', '数据库', 4, 72, '必修', 50);
insert into `course` values ('10', '9', 'em', '统计与审计', 5, 90, '必修', 90);
insert into `course` values ('11', null, 'el', '刺绣', 1, 18, '选修', 50);
insert into `course` values ('12', null, 'el', '家庭保健', 1, 18, '选修', 30);
insert into `course` values ('2', null, 'sd', '数学', 3, 54, '必修', 90);
insert into `course` values ('3', '1', 'cs', '信息系统与数据库', 3, 54, '必修', 50);
insert into `course` values ('4', '6', 'cs', '操作系统', 4, 72, '必修', 50);
insert into `course` values ('5', '7', 'cs', '数据结构', 5, 90, '必修', 50);
insert into `course` values ('6', null, 'cs', '计算机基础', 3, 54, '必修', 70);
insert into `course` values ('7', '6', 'cs', 'c语言', 2, 36, '必修', 70);
insert into `course` values ('8', null, 'cs', '计算机组成原理', 3, 54, '选修', 120);
insert into `course` values ('9', '2', 'em', '会计学原理', 5, 90, '必修', 90);

-- ----------------------------
-- table structure for dept
-- ----------------------------
drop table if exists `dept`;
create table `dept`  (
  `did` char(2) character set utf8 collate utf8_general_ci not null comment '部门编号，主键',
  `dname` varchar(20) character set utf8 collate utf8_general_ci not null comment '部门名称',
  `daddr` varchar(20) character set utf8 collate utf8_general_ci null default null comment '地址',
  `dtele` varchar(16) character set utf8 collate utf8_general_ci null default null comment '电话',
  `demail` varchar(50) character set utf8 collate utf8_general_ci null default null comment '邮箱',
  primary key (`did`) using btree
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '院系表' row_format = dynamic;

-- ----------------------------
-- records of dept
-- ----------------------------
insert into `dept` values ('cs', '计算机科学与技术系', 'sl604', '87678976', '[e-mail]');
insert into `dept` values ('el', '外校', null, '62765678', null);
insert into `dept` values ('em', '经济管理系', 'sc302', '87464789', '[e-mail]');
insert into `dept` values ('fd', '外语分院', 'sa401', '65656798', '[e-mail]');
insert into `dept` values ('it', '信息科学与技术系', 'sl704', '88767864', '[e-mail]');
insert into `dept` values ('sd', '理学院', 'nb309', '67536387', '[e-mail]');

-- ----------------------------
-- table structure for exercise
-- ----------------------------
drop table if exists `exercise`;
create table `exercise`  (
  `eid` int(11) not null,
  `econtext` varchar(255) character set utf8 collate utf8_general_ci null default null,
  `elevel` int(11) null default null,
  `eanswer` varchar(255) character set utf8 collate utf8_general_ci null default null,
  primary key (`eid`) using btree
) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic;

-- ----------------------------
-- records of exercise
-- ----------------------------
insert into `exercise` values (1, '显示院系信息表中的所有信息', 1, '');
insert into `exercise` values (2, '显示院系信息表中的部门编码、部门名称信息', 1, '');
insert into `exercise` values (3, '显示所有女教师的工号、姓名、性别信息', 1, '');
insert into `exercise` values (4, '显示cs系的老师所有个人信息', 1, '');
insert into `exercise` values (5, '显示cs系与it系所有老师的全部个人信息', 1, '');
insert into `exercise` values (6, '显示女教授的姓名、性别、职称与部门编号信息', 1, '');
insert into `exercise` values (7, '显示研究领域为数据库的老师的姓名、研究领域、联系电话、所在部门', 1, '');
insert into `exercise` values (8, '显示40岁以下老师的姓名、出生日期', 1, '');
insert into `exercise` values (9, '显示5月份出生的姓名、性别、出生日期、联系电话', 1, '');
insert into `exercise` values (10, '显示姓李的老师的所有信息', 1, '');
insert into `exercise` values (11, '显示选修了1号课程的同学的学号', 1, '');
insert into `exercise` values (12, '显示同时选修了1号与2号课程的同学的学号', 2, '');
insert into `exercise` values (13, '显示012005002号同学选修的课程号', 1, '');
insert into `exercise` values (14, '显示012005002号同学的全部选修信息', 1, '');
insert into `exercise` values (15, '显示期末成绩不及格的同学的学号、课程号与期末成绩', 1, '');
insert into `exercise` values (16, '显示选修了1号课程的人数', 1, '');
insert into `exercise` values (17, '显示1号课程期末考试成绩的平均分', 1, '');
insert into `exercise` values (18, '显示012005002同学的期末成绩的平均分', 1, '');
insert into `exercise` values (19, '显示2008年每门课程期末成绩的平均分，显示课程号与平均分，并按降序排列', 2, '');
insert into `exercise` values (20, '显示周4有课的教室', 1, '');
insert into `exercise` values (21, '显示周4有课的老师的工号', 1, '');
insert into `exercise` values (22, '显示nb222教室排课的情况', 1, '');
insert into `exercise` values (23, '显示1号课程上课的教室、老师工号、上课时间', 1, '');
insert into `exercise` values (24, '显示02004号老师上课的教室与时间', 1, '');
insert into `exercise` values (25, '显示第4节有课的教室、课程号、教师工号', 1, '');
insert into `exercise` values (26, '统计每个教师每周上课的次数及总时长(每节课45分钟)', 2, '');
insert into `exercise` values (27, '统计每个班每周上课的课时数，显示班级编号和课时数，并按降序排列', 2, '');
insert into `exercise` values (28, '显示无前导课的课程的全部信息', 1, '');
insert into `exercise` values (29, '显示cs系所开课程的课程号与课程名', 1, '');
insert into `exercise` values (30, '显示学分大于3的所有课程的课程名与课时', 1, '');
insert into `exercise` values (31, '按开课院系统计每个院系开课的学分数，显示院系编号和总学分，并按降序排列', 2, '');
insert into `exercise` values (32, '显示学生中所有男生的全部信息', 1, '');
insert into `exercise` values (33, '显示01班所有学生的信息', 1, '');
insert into `exercise` values (34, '显示03班所有女生的信息', 1, '');
insert into `exercise` values (35, '显示刘山同学的电话号码', 1, '');
insert into `exercise` values (36, '显示所有女生的学号、姓名与班级编号', 1, '');
insert into `exercise` values (37, '统计每个班级的人数，显示班级编号，人数，并按降序排列', 1, '');
insert into `exercise` values (38, '显示人数5人以上班级的班级编号和人数，并按升序排列', 2, '');
insert into `exercise` values (39, '显示年龄在18岁以下的学生的全部信息', 1, '');
insert into `exercise` values (40, '统计18岁以下学生的总人数', 1, '');
insert into `exercise` values (41, '统计每个班20岁以下的人数，显示班级编号、人数', 2, '');
insert into `exercise` values (42, '统计每个年龄段的人数，按照年龄升序排列', 2, '');
insert into `exercise` values (43, '显示姓名中有\"白云\"的同学的所有信息', 1, '');
insert into `exercise` values (44, '显示白姓同学的姓名、性别、班级名称、联系电话', 2, '');
insert into `exercise` values (45, '显示cs系的班级名称及入学年份', 1, '');
insert into `exercise` values (46, '显示没有班导的班级的所有信息', 1, '');
insert into `exercise` values (47, '显示2008年入学班级的所有信息', 1, '');
insert into `exercise` values (48, '显示所有学生的详细信息，包括学号、姓名、性别、年龄、班级名称，入学年份', 2, '');
insert into `exercise` values (49, '显示信息科学与技术系同学的名单，包括学号、姓名、性别、年龄、班级名称、入学年份', 2, '');
insert into `exercise` values (50, '显示选修了\"数据库\"课程的所有同学的学号、姓名', 2, '');
insert into `exercise` values (51, '显示白云同学的班主任老师的姓名、联系电话', 2, '');
insert into `exercise` values (52, '显示白云同学所在院系的名称、办公地点与联系电话', 2, '');
insert into `exercise` values (53, '统计计算机科学与技术系每个同学已经修完的学分，显示学号、姓名、学分总数', 2, '');
insert into `exercise` values (54, '显示张飞同学已修课程的课程号，课程名及期末成绩', 2, '');
insert into `exercise` values (55, '显示计算机科学与技术系、信息科学与技术系的班级名称、入学年份、班导名称与联系电话', 2, '');
insert into `exercise` values (56, '显示2008年入学的同学的学号、姓名、班级名称', 2, '');
insert into `exercise` values (57, '显示已修数据库的同学的学号、姓名及期末成绩', 2, '');
insert into `exercise` values (58, '显示期末平均成绩75分以上的课程名称与期末平均成绩', 2, '');
insert into `exercise` values (59, '显示期末平均成绩80分以上同学的学号、姓名与期末平均成绩', 2, '');
insert into `exercise` values (60, '显示一周课时数为5节及以上的教师的姓名与研究领域', 2, '');
insert into `exercise` values (61, '按照班级统计期末平均成绩，显示班级名称与平均成绩', 2, '');
insert into `exercise` values (62, '按照学期统计计算机科学与技术系学生的期末平均成绩，显示学期名称与平均成绩', 2, '');
insert into `exercise` values (63, '统计每个院系一周的课时数，显示院系名称与课时数', 2, '');
insert into `exercise` values (64, '显示没有选修任何课程的学生学号、姓名、班级名称', 2, '');
insert into `exercise` values (65, '显示上过李飞老师的课的学生的学号、姓名与联系电话', 2, '');
insert into `exercise` values (66, '显示一周6节课及以上的课程名称、学分', 2, '');
insert into `exercise` values (67, '显示一周6节课及以上班级名称', 2, '');
insert into `exercise` values (68, '查询周四上午第3节有课的同学的学号、姓名与班级名称', 3, '');
insert into `exercise` values (69, '显示期末成绩没有不及格课程的班级的名称', 3, '');
insert into `exercise` values (70, '显示已修数据库的同学信息，包括学号、姓名、班级名称', 3, '');
insert into `exercise` values (71, '显示期中成绩不及格1门以上的同学学号、姓名、门数', 3, '');
insert into `exercise` values (72, '统计每个班级期末成绩的最高分，显示班级名称、期末最高成绩', 2, '');
insert into `exercise` values (73, '显示一周8节课及以上的学生的名单，显示学号、姓名、班级名称', 3, '');
insert into `exercise` values (74, '显示计算机科学与技术1班一周上课的时间、地点，课程名称(周几，哪几节课，哪个教室)', 3, '');
insert into `exercise` values (75, '统计教授每周上课的课时数，显示姓名、课时数', 3, '');
insert into `exercise` values (76, '显示没有班导师的班级名称、院系名称', 2, '');
insert into `exercise` values (77, '显示指导过两个班级以上的班导的姓名、所指导的班级名称', 2, '');
insert into `exercise` values (78, '为洪玉飞老师(教师编号：03012)安排软件工程1班(班级编号：04)的数据库课程(课程编号：1)，上课教师为nb201', 1, '');
insert into `exercise` values (79, '计算机科学与技术3班所有学生都选修了2009-2010-1的操作系统(课程编号为4)，请记录相关信息', 1, '');
insert into `exercise` values (80, '理学院新开一门课程“数学建模”，课程编号20, 学分4，学时72，选修课程，最多选课人数为50', 1, '');
insert into `exercise` values (81, '将李飞同学的联系方式改为660101', 1, '');
insert into `exercise` values (82, '计算所有学生的总评成绩，公式为：总评=平时*20%+实验*20%+期末*60%', 1, '');
insert into `exercise` values (83, '将电子信息1班(班级编号：08)的班主任改为洪玉飞老师(无重名)', 1, '');
insert into `exercise` values (84, '将课程\"数据库\"的上课教室改为nb111，授课教师改为李飞(教师编号：02001)', 1, '');
insert into `exercise` values (85, '将学号为012005001的学生班级改为计算机科学与技术3班', 1, '');
insert into `exercise` values (86, '删除所有期末成绩小于60分的选课记录', 1, '');
insert into `exercise` values (87, '删除学号为012005001的所有选课记录', 1, '');
insert into `exercise` values (88, '删除所有选修了\"数据库\"课程的选课记录', 1, '');
insert into `exercise` values (89, '删除李飞老师(教师编号：02001)2008学年的排课记录', 1, '');
insert into `exercise` values (90, '删除所有在nb1楼上课的排课记录', 1, '');
insert into `exercise` values (91, '删除nb111教室在周四的排课记录', 1, '');
insert into `exercise` values (92, '删除选修人数小于5的选课记录', 1, '');
insert into `exercise` values (93, '删除未担任班导师并且未安排课程的教师记录', 2, '');
insert into `exercise` values (94, '创建一个新的用户，用户名为[alogin]，允许该用户在本机访问教务数据库', 0, '');
insert into `exercise` values (95, '授予新建数据库用户[alogin]对表student和sc的查询权限', 0, '');
insert into `exercise` values (96, '授予新建数据库用户[alogin]对表student表sname列的更新权限', 0, '');
insert into `exercise` values (97, '创建一个角色，授予对表course的查询和更新权限，并将该角色授权给新建数据库用户[alogin]', 0, '');
insert into `exercise` values (98, '为student表增加约束条件，性别字段可以的取值为‘男’，‘女’', 0, '');
insert into `exercise` values (99, '为student表增加约束条件：性别默认为‘男’', 0, '');
insert into `exercise` values (100, '为student表增加约束条件：联系方式至少长度为6', 0, '');
insert into `exercise` values (101, '为student表增加约束条件：出生日期小于当前时间', 0, '');
insert into `exercise` values (102, '为dept表增加约束条件：院系名称必须唯一', 0, '');
insert into `exercise` values (103, '为grade表增加约束条件：入学年份不能大于当前年份', 0, '');
insert into `exercise` values (104, '为dept表增加约束条件：联系电话必须为8位数字', 0, '');
insert into `exercise` values (105, '为information表增加约束条件：学期格式为“xxxx-xxxx-x”，其中xxxx为4位数字表示学年，x为1或者2，表示上下学期。', 0, '');
insert into `exercise` values (106, '为sc表增加约束条件：各项成绩都在0-100之间', 0, '');
insert into `exercise` values (107, '删除一个已经存在的约束条件，如果没有先建立约束', 0, '');
insert into `exercise` values (108, '写一个触发器，使得course中的记录更新时，课程性质只能为“选修”或者“必修”。否则提醒更新失败', 2, '');
insert into `exercise` values (109, '写一个触发器，使得sc表的新增记录满足下述条件： 公式为：总评=平时*20%+实验*20%+期末*60%', 2, '');

-- ----------------------------
-- table structure for grade
-- ----------------------------
drop table if exists `grade`;
create table `grade`  (
  `gid` char(2) character set utf8 collate utf8_general_ci not null comment '班级编号，主键',
  `did` char(2) character set utf8 collate utf8_general_ci null default null comment '系编号，外键',
  `tid` char(5) character set utf8 collate utf8_general_ci null default null comment '教师工号，外键',
  `gname` char(20) character set utf8 collate utf8_general_ci not null comment '班级名称',
  `gyear` int(11) null default null comment '入学年份',
  primary key (`gid`) using btree,
  index `fk_grade_teacher`(`tid`) using btree,
  index `fk_gradet_dep`(`did`) using btree,
  constraint `fk_fk_grade_teacher` foreign key (`tid`) references `teacher` (`tid`) on delete set null on update set null,
  constraint `fk_fk_gradet_dep` foreign key (`did`) references `dept` (`did`) on delete set null on update set null
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '班级表' row_format = dynamic;

-- ----------------------------
-- records of grade
-- ----------------------------
insert into `grade` values ('01', 'cs', '02008', '计算机科学与技术1班', 2006);
insert into `grade` values ('02', 'cs', '02008', '计算机科学与技术2班', 2006);
insert into `grade` values ('03', 'cs', '02008', '计算机科学与技术3班', 2006);
insert into `grade` values ('04', 'cs', '02009', '软件工程1班', 2007);
insert into `grade` values ('05', 'cs', null, '软件工程2班', 2007);
insert into `grade` values ('06', 'it', '03014', '自动化1班', 2008);
insert into `grade` values ('07', 'it', '03014', '自动化2班', 2008);
insert into `grade` values ('08', 'it', '03012', '电子信息1班', 2008);
insert into `grade` values ('09', 'it', null, '电子信息2班', 2008);
insert into `grade` values ('10', 'it', null, '电子信息3班', 2008);
insert into `grade` values ('11', 'em', '03010', '财务管理', 2006);
insert into `grade` values ('12', 'em', '03011', '旅游管理', 2006);
insert into `grade` values ('13', 'em', null, '营销管理', 2006);
insert into `grade` values ('14', 'em', null, '信息管理', 2006);
insert into `grade` values ('15', 'fd', '03012', '日语', 2005);
insert into `grade` values ('16', 'fd', '03013', '德语1班', 2005);
insert into `grade` values ('17', 'fd', '03013', '德语2班', 2005);
insert into `grade` values ('18', 'sd', null, '应用数学1班', 2008);
insert into `grade` values ('20', 'sd', null, '选修混合', null);

-- ----------------------------
-- table structure for information
-- ----------------------------
drop table if exists `information`;
create table `information`  (
  `iid` int(11) not null comment '序号，主键',
  `cid` char(4) character set utf8 collate utf8_general_ci not null comment '课程序号，外键',
  `tid` char(5) character set utf8 collate utf8_general_ci not null comment '教师编号，外键',
  `gid` char(2) character set utf8 collate utf8_general_ci not null comment '班级编号，外键',
  `iroom` char(10) character set utf8 collate utf8_general_ci null default null comment '上课教室',
  `iweek` int(11) null default null comment '周几上课',
  `itimeseg` char(8) character set utf8 collate utf8_general_ci null default null comment '第几节上课',
  `iterm` char(12) character set utf8 collate utf8_general_ci null default null comment '开课学年/学期',
  primary key (`iid`) using btree,
  index `fk_information_course`(`cid`) using btree,
  index `fk_information_grade`(`gid`) using btree,
  index `fk_information_teacher`(`tid`) using btree,
  constraint `fk_information_course` foreign key (`cid`) references `course` (`cid`) on delete restrict on update restrict,
  constraint `fk_information_grade` foreign key (`gid`) references `grade` (`gid`) on delete restrict on update restrict,
  constraint `fk_information_teacher` foreign key (`tid`) references `teacher` (`tid`) on delete restrict on update restrict
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '课程信息表' row_format = dynamic;

-- ----------------------------
-- records of information
-- ----------------------------
insert into `information` values (1, '1', '02001', '01', 'nb111', 4, '123', '2008-2009-1');
insert into `information` values (2, '1', '02001', '01', 'nb111', 2, '345', '2008-2009-1');
insert into `information` values (3, '2', '02003', '03', 'nb222', 1, '34', '2008-2009-1');
insert into `information` values (4, '3', '02004', '01', 'nb223', 5, '678', '2008-2009-1');
insert into `information` values (5, '5', '02005', '05', 'nb224', 3, '34', '2008-2009-1');
insert into `information` values (6, '6', '02006', '05', 'nb225', 1, '67', '2008-2009-1');
insert into `information` values (7, '7', '02007', '03', 'nb226', 4, '89', '2008-2009-1');
insert into `information` values (8, '9', '02008', '08', 'nb227', 4, '678', '2008-2009-1');
insert into `information` values (9, '11', '02009', '09', 'nb228', 2, 'ab', '2008-2009-1');
insert into `information` values (10, '2', '03010', '02', 'nb229', 1, '123', '2008-2009-1');
insert into `information` values (11, '4', '02001', '02', 'nb230', 5, '345', '2008-2009-1');
insert into `information` values (12, '5', '02002', '03', 'nb231', 3, '123', '2008-2009-1');
insert into `information` values (13, '6', '02003', '09', 'nb222', 1, '89', '2008-2009-1');
insert into `information` values (14, '7', '02004', '13', 'nb223', 4, '67', '2008-2009-1');
insert into `information` values (15, '9', '02005', '15', 'nb224', 4, '345', '2008-2009-1');
insert into `information` values (16, '3', '02006', '13', 'nb225', 2, '89', '2008-2009-1');
insert into `information` values (17, '4', '02007', '15', 'nb226', 2, '12', '2008-2009-1');
insert into `information` values (18, '10', '02008', '03', 'nb227', 5, '123', '2008-2009-1');
insert into `information` values (19, '10', '02009', '20', 'nb228', 3, 'ab', '2008-2009-1');
insert into `information` values (20, '12', '03010', '20', 'nb229', 1, 'ab', '2008-2009-1');
insert into `information` values (21, '4', '02001', '12', 'nb230', 2, '34', '2008-2009-1');
insert into `information` values (22, '3', '02002', '09', 'nb231', 4, '345', '2008-2009-1');
insert into `information` values (24, '1', '02001', '09', 'nb111', 5, '12', '2008-2009-1');

-- ----------------------------
-- table structure for sc
-- ----------------------------
drop table if exists `sc`;
create table `sc`  (
  `scid` int(11) not null comment '选课序号，主键',
  `cid` char(4) character set utf8 collate utf8_general_ci not null comment '课程编号，外键',
  `sid` char(9) character set utf8 collate utf8_general_ci not null comment '学号，外键',
  `scterm` char(12) character set utf8 collate utf8_general_ci null default null comment '开课学年/学期',
  `scscore1` float null default null comment '平时成绩',
  `scscore2` float null default null comment '期中成绩',
  `scscore3` float null default null comment '期末成绩',
  `scscore` float null default null comment '总评成绩',
  primary key (`scid`) using btree,
  index `fk_sc_course`(`cid`) using btree,
  index `fk_sc_student`(`sid`) using btree,
  constraint `fk_sc_course` foreign key (`cid`) references `course` (`cid`) on delete restrict on update restrict,
  constraint `fk_sc_student` foreign key (`sid`) references `student` (`sid`) on delete restrict on update restrict
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '选课信息表' row_format = dynamic;

-- ----------------------------
-- records of sc
-- ----------------------------
insert into `sc` values (2, '1', '012005002', '2008-2009-1', 99, 60, 70, 76.3333);
insert into `sc` values (3, '1', '012005003', '2008-2009-1', 99, 44, 88, 77);
insert into `sc` values (4, '1', '012005004', '2008-2009-1', 99, 78, 67, 81.3333);
insert into `sc` values (5, '1', '032005005', '2008-2009-1', 99, 65, 87, 83.6667);
insert into `sc` values (6, '1', '032005006', '2008-2009-1', 99, 76, 98, 91);
insert into `sc` values (7, '1', '032005007', '2008-2009-1', 99, 56, 78, 77.6667);
insert into `sc` values (20, '5', '082005009', '2008-2009-2', 99, 78, 83, 86.6667);
insert into `sc` values (21, '5', '092005010', '2008-2009-2', 99, 77, 63, 79.6667);
insert into `sc` values (22, '5', '112005011', '2008-2009-2', 99, 90, 74, 87.6667);
insert into `sc` values (23, '5', '112005012', '2008-2009-2', 99, 45, 83, 75.6667);
insert into `sc` values (24, '5', '112005013', '2008-2009-2', 99, 89, 73, 87);
insert into `sc` values (25, '10', '012006001', '2008-2009-2', 99, 67, 73, 79.6667);
insert into `sc` values (26, '10', '012006002', '2008-2009-2', 99, 36, 82, 72.3333);
insert into `sc` values (27, '10', '012006003', '2008-2009-2', 99, 87, 83, 89.6667);
insert into `sc` values (28, '10', '012006004', '2008-2009-2', 99, 54, 73, 75.3333);
insert into `sc` values (29, '10', '032006005', '2008-2009-2', 99, 45, 73, 72.3333);
insert into `sc` values (30, '10', '032006006', '2008-2009-2', 99, 78, 83, 86.6667);
insert into `sc` values (31, '10', '032006007', '2008-2009-2', 99, 72, 84, 85);
insert into `sc` values (32, '10', '082006008', '2007-2008-2', 99, 60, 78, 79);
insert into `sc` values (33, '12', '082006009', '2007-2008-2', 99, 60, 73, 77.3333);
insert into `sc` values (34, '12', '092006010', '2007-2008-2', 99, 90, 83, 90.6667);
insert into `sc` values (35, '12', '112006011', '2007-2008-2', 99, 89, 62, 83.3333);
insert into `sc` values (36, '12', '112006012', '2007-2008-2', 99, 89, 65, 84.3333);
insert into `sc` values (37, '12', '112006013', '2007-2008-2', 99, 78, 65, 80.6667);
insert into `sc` values (38, '11', '012007001', '2007-2008-2', 99, 65, 67, 77);
insert into `sc` values (39, '11', '012007002', '2007-2008-2', 99, 76, 78, 84.3333);
insert into `sc` values (40, '11', '012007003', '2007-2008-2', 99, 56, 79, 78);
insert into `sc` values (41, '11', '012007004', '2007-2008-2', 99, 50, 76, 75);
insert into `sc` values (42, '11', '032007005', '2007-2008-2', 99, 78, 71, 82.6667);
insert into `sc` values (43, '11', '032007006', '2007-2008-2', 99, 98, 81, 92.6667);
insert into `sc` values (44, '11', '032007007', '2007-2008-2', 99, 79, 82, 86.6667);
insert into `sc` values (45, '9', '082007008', '2007-2008-2', 99, 93, 62, 84.6667);
insert into `sc` values (46, '9', '082007009', '2007-2008-2', 99, 72, 63, 78);
insert into `sc` values (47, '9', '092007010', '2008-2009-1', 99, 89, 69, 85.6667);
insert into `sc` values (48, '9', '112007011', '2008-2009-1', 99, 86, 68, 84.3333);
insert into `sc` values (49, '9', '112007012', '2008-2009-1', 99, 80, 76, 85);
insert into `sc` values (50, '9', '112007013', '2008-2009-1', 99, 88, 83, 90);
insert into `sc` values (52, '9', '012005002', '2008-2009-1', 99, 54, 76, 76.3333);
insert into `sc` values (53, '9', '012005003', '2008-2009-1', 99, 78, 76, 84.3333);
insert into `sc` values (54, '9', '012005004', '2008-2009-1', 99, 77, 56, 77.3333);
insert into `sc` values (55, '2', '032005005', '2008-2009-1', 99, 90, 65, 84.6667);
insert into `sc` values (56, '2', '032005006', '2008-2009-1', 99, 45, 65, 69.6667);
insert into `sc` values (57, '2', '032005007', '2008-2009-1', 99, 89, 76, 88);
insert into `sc` values (58, '2', '082005008', '2008-2009-1', 99, 67, 45, 70.3333);
insert into `sc` values (59, '2', '082005009', '2008-2009-1', 99, 36, 84, 73);
insert into `sc` values (60, '2', '092005010', '2008-2009-1', 99, 87, 90, 92);
insert into `sc` values (61, '2', '112005011', '2008-2009-1', 99, 54, 93, 82);
insert into `sc` values (62, '2', '112005012', '2008-2009-1', 99, 45, 67, 70.3333);
insert into `sc` values (63, '2', '112005013', '2008-2009-1', 99, 78, 64, 80.3333);
insert into `sc` values (64, '2', '012006001', '2008-2009-1', 99, 72, 63, 78);
insert into `sc` values (65, '6', '012006002', '2008-2009-1', 99, 60, 48, 69);
insert into `sc` values (66, '6', '012006003', '2008-2009-1', 99, 60, 76, 78.3333);
insert into `sc` values (67, '6', '012006004', '2008-2009-1', 99, 90, 65, 84.6667);
insert into `sc` values (68, '6', '032006005', '2008-2009-1', 99, 89, 76, 88);
insert into `sc` values (69, '6', '032006006', '2008-2009-1', 99, 89, 46, 78);
insert into `sc` values (70, '6', '032006007', '2008-2009-1', 99, 78, 85, 87.3333);
insert into `sc` values (71, '6', '082006008', '2008-2009-1', 99, 65, 65, 76.3333);
insert into `sc` values (72, '6', '082006009', '2008-2009-1', 99, 76, 54, 76.3333);
insert into `sc` values (73, '6', '092006010', '2007-2008-1', 99, 56, 65, 73.3333);
insert into `sc` values (74, '6', '112006011', '2007-2008-1', 99, 50, 65, 71.3333);
insert into `sc` values (75, '6', '112006012', '2007-2008-1', 99, 78, 74, 83.6667);
insert into `sc` values (76, '6', '112006013', '2007-2008-1', 99, 98, 74, 90.3333);
insert into `sc` values (77, '7', '012007001', '2007-2008-1', 99, 79, 83, 87);
insert into `sc` values (78, '7', '012007002', '2007-2008-1', 99, 93, 94, 95.3333);
insert into `sc` values (79, '7', '012007003', '2007-2008-1', 99, 72, 73, 81.3333);
insert into `sc` values (80, '7', '012007004', '2007-2008-1', 99, 89, 83, 90.3333);
insert into `sc` values (81, '7', '032007005', '2007-2008-1', 99, 86, 63, 82.6667);
insert into `sc` values (82, '7', '032007006', '2007-2008-1', 99, 80, 67, 82);
insert into `sc` values (83, '7', '032007007', '2007-2008-1', 99, 88, 84, 90.3333);
insert into `sc` values (84, '7', '082007008', '2007-2008-1', 99, 67, 96, 87.3333);
insert into `sc` values (85, '7', '082007009', '2007-2008-1', 99, 65, 90, 84.6667);
insert into `sc` values (86, '7', '092007010', '2007-2008-1', 99, 78, 92, 89.6667);
insert into `sc` values (87, '7', '112007011', '2007-2008-1', 99, 77, 86, 87.3333);
insert into `sc` values (88, '7', '112007012', '2007-2008-1', 99, 90, 80, 89.6667);
insert into `sc` values (89, '7', '112007013', '2007-2008-1', 99, 45, 80, 74.6667);

-- ----------------------------
-- table structure for student
-- ----------------------------
drop table if exists `student`;
create table `student`  (
  `sid` char(9) character set utf8 collate utf8_general_ci not null comment '学号，主键',
  `gid` char(2) character set utf8 collate utf8_general_ci not null comment '班级id，外键',
  `sname` char(8) character set utf8 collate utf8_general_ci not null comment '姓名',
  `ssexy` char(2) character set utf8 collate utf8_general_ci not null default '男' comment '性别',
  `sbdate` date not null comment '出生日期',
  `stele` char(11) character set utf8 collate utf8_general_ci null default null comment '联系电话',
  primary key (`sid`) using btree,
  index `fk_student_grade`(`gid`) using btree,
  constraint `fk_student_grade` foreign key (`gid`) references `grade` (`gid`) on delete restrict on update restrict
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '学生信息表' row_format = dynamic;

-- ----------------------------
-- records of student
-- ----------------------------
insert into `student` values ('012005001', '03', '李山', '男', '1998-10-11', '660780');
insert into `student` values ('012005002', '01', '张飞', '男', '1997-10-11', '660781');
insert into `student` values ('012005003', '01', '李玉和', '女', '1999-10-11', '660782');
insert into `student` values ('012005004', '01', '王一飞', '女', '2000-10-11', '660783');
insert into `student` values ('012006001', '01', '韦宝', '男', '1998-10-11', '660780');
insert into `student` values ('012006002', '01', '李飞', '男', '1997-10-11', '660101');
insert into `student` values ('012006003', '01', '冯玉', '女', '1999-10-11', '660782');
insert into `student` values ('012006004', '01', '马观', '女', '2000-10-11', '660783');
insert into `student` values ('012007001', '01', '李知', '男', '1998-10-11', '660780');
insert into `student` values ('012007002', '01', '吴飞', '男', '1997-10-11', '660781');
insert into `student` values ('012007003', '01', '李凡', '女', '1999-10-11', '660782');
insert into `student` values ('012007004', '01', '王二飞', '女', '2000-10-11', '660783');
insert into `student` values ('032005005', '03', '徐红', '女', '1998-01-11', '660784');
insert into `student` values ('032005006', '03', '刘和', '男', '1998-12-11', '660785');
insert into `student` values ('032005007', '03', '刘山', '男', '1999-10-01', '660786');
insert into `student` values ('032006005', '03', '徐一红', '女', '1998-01-11', '660784');
insert into `student` values ('032006006', '03', '刘一和', '男', '1998-12-11', '660785');
insert into `student` values ('032006007', '03', '马西', '男', '1999-10-01', '660786');
insert into `student` values ('032007005', '03', '王红', '女', '1998-01-11', '660784');
insert into `student` values ('032007006', '03', '王一红', '男', '1998-12-11', '660785');
insert into `student` values ('032007007', '03', '丁西', '男', '1999-10-01', '660786');
insert into `student` values ('082005008', '08', '刘去山', '女', '2000-01-11', '660787');
insert into `student` values ('082005009', '08', '白云飞', '女', '2002-10-11', '660788');
insert into `student` values ('082006008', '08', '刘问计', '女', '2000-01-11', '660787');
insert into `student` values ('082006009', '08', '白问礼', '女', '2002-10-11', '660788');
insert into `student` values ('082007008', '08', '刘红丽', '女', '2000-01-11', '660787');
insert into `student` values ('082007009', '08', '沈学云', '女', '2002-10-11', '660788');
insert into `student` values ('092005010', '08', '白云', '女', '1998-11-11', '660789');
insert into `student` values ('092006010', '09', '黑土', '女', '1998-12-11', '660788');
insert into `student` values ('092007010', '09', '李风', '女', '1998-11-11', '660789');
insert into `student` values ('112005011', '11', '李红', '女', '1997-12-11', '660790');
insert into `student` values ('112005012', '11', '周磊', '男', '1996-06-11', '660791');
insert into `student` values ('112005013', '11', '冯圭', '女', '1997-08-11', '660792');
insert into `student` values ('112006011', '11', '李玉红', '女', '1997-12-11', '660790');
insert into `student` values ('112006012', '11', '冯磊', '男', '2006-06-11', '660791');
insert into `student` values ('112006013', '11', '冯由', '女', '2007-08-11', '660792');
insert into `student` values ('112007011', '11', '刘好', '女', '1997-12-11', '660790');
insert into `student` values ('112007012', '11', '周成', '男', '1996-06-11', '660791');
insert into `student` values ('112007013', '11', '文成', '女', '1997-08-11', '660792');
insert into `student` values ('112007014', '11', '霍去病', '男', '1999-05-09', '660793');

-- ----------------------------
-- table structure for teacher
-- ----------------------------
drop table if exists `teacher`;
create table `teacher`  (
  `tid` char(5) character set utf8 collate utf8_general_ci not null comment '教师工号，主键',
  `did` char(2) character set utf8 collate utf8_general_ci null default null comment '系编号，外键',
  `tname` char(8) character set utf8 collate utf8_general_ci not null comment '姓名',
  `tsexy` char(2) character set utf8 collate utf8_general_ci not null comment '性别',
  `tbdate` date not null comment '出生日期',
  `tfield` char(50) character set utf8 collate utf8_general_ci not null comment '研究领域',
  `tprof` char(10) character set utf8 collate utf8_general_ci not null comment '职称',
  `ttele` char(16) character set utf8 collate utf8_general_ci null default null comment '联系电话',
  `tqq` char(12) character set utf8 collate utf8_general_ci null default null comment 'qq号码',
  `temail` char(30) character set utf8 collate utf8_general_ci null default null comment '邮箱',
  `tmsn` char(30) character set utf8 collate utf8_general_ci null default null comment 'msn',
  primary key (`tid`) using btree,
  index `fk_teacher_dept`(`did`) using btree,
  constraint `fk_teacher_dept` foreign key (`did`) references `dept` (`did`) on delete restrict on update restrict
) engine = innodb character set = utf8 collate = utf8_general_ci comment = '教师信息表' row_format = dynamic;

-- ----------------------------
-- records of teacher
-- ----------------------------
insert into `teacher` values ('02001', 'cs', '李飞', '男', '1986-05-05', '数据库', '讲师', '660001', null, null, null);
insert into `teacher` values ('02002', 'cs', '郭山', '男', '1980-06-09', '数据库', '副教授', '660002', null, null, null);
insert into `teacher` values ('02003', 'cs', '马骊', '女', '1983-03-08', '网络技术与数据库', '教授', '660003', null, null, null);
insert into `teacher` values ('02004', 'cs', '徐守', '女', '1980-06-09', '面向对象编程', '助教', '660004', null, null, null);
insert into `teacher` values ('02005', 'cs', '金贵', '女', '1980-06-09', '财务管理', '助教', '660005', null, null, null);
insert into `teacher` values ('02006', 'it', '成山云', '男', '1984-11-02', '金融学', '教授', '660006', null, null, null);
insert into `teacher` values ('02007', 'it', '张田下', '男', '1977-01-07', '新能源技术', '研究员', '660007', null, null, null);
insert into `teacher` values ('02008', 'it', '王一钱', '女', '1965-03-03', '信息管理与数据库', '讲师', '660008', null, null, null);
insert into `teacher` values ('02009', 'it', '李远', '女', '1977-01-07', '电工学', '实验师', '660009', null, null, null);
insert into `teacher` values ('03010', 'it', '吴天贵', '女', '1984-04-26', '中医学', '主治医师', '660010', null, null, null);
insert into `teacher` values ('03011', 'em', '刘了了', '女', '1972-06-12', '近代史', '研究员', '660220', null, null, null);
insert into `teacher` values ('03012', 'em', '洪玉飞', '男', '1967-09-29', '哲学', '教授', '660222', null, null, null);
insert into `teacher` values ('03013', 'em', '划计成', '女', '1962-09-01', '应用数学', '教授', '660223', null, null, null);
insert into `teacher` values ('03014', 'em', '李丽青', '男', '1968-05-09', '应用物理', '讲师', '660233', null, null, null);
insert into `teacher` values ('03015', 'fd', '李员', '男', '1971-09-15', '统计学', '研究员', '660234', null, null, null);
insert into `teacher` values ('03016', 'fd', '国威', '女', '1965-04-30', '政治学', '研究员', '660123', null, null, null);
insert into `teacher` values ('03017', 'fd', '国华', '女', '1989-05-29', '证券投资', '研究员', '660987', null, null, null);
insert into `teacher` values ('05022', 'sd', '后羿', '女', '1983-06-16', '护理与营养', '主任护理师', '660909', null, null, null);
insert into `teacher` values ('05023', 'sd', '王飞红', '男', '1975-11-20', '多媒体技术', '讲师', '660938', null, null, null);
insert into `teacher` values ('05024', 'sd', '李丽青', '男', '1969-03-30', '理论力学', '副教授', '660323', null, null, null);
insert into `teacher` values ('05025', 'sd', '王红', '女', '1970-06-15', '建筑学', '副教授', '660099', null, null, null);
insert into `teacher` values ('05026', 'sd', '李飞', '女', '1963-12-22', '流体力学', '讲师', '660987', null, null, null);

-- ----------------------------
-- view structure for v_sd
-- ----------------------------
drop view if exists `v_sd`;
create algorithm = undefined sql security definer view `v_sd` as (select `student`.`sid` as `sid`,`student`.`sname` as `sname`,`course`.`cname` as `cname`,`sc`.`scscore1` as `scscore1` from ((`student` join `sc`) join `course`) where ((`student`.`sid` = `sc`.`sid`) and (`sc`.`cid` = `course`.`cid`) and (`course`.`cname` = '数据库')));

-- ----------------------------
-- view structure for v_sg
-- ----------------------------
drop view if exists `v_sg`;
create algorithm = undefined sql security definer view `v_sg` as select `student`.`sid` as `sid`,`student`.`sname` as `sname`,`grade`.`gname` as `gname`,`grade`.`gyear` as `gyear` from (`student` join `grade` on((`student`.`gid` = `grade`.`gid`)));

-- ----------------------------
-- procedure structure for pro_five_grade
-- ----------------------------
drop procedure if exists `pro_five_grade`;
delimiter ;;
create procedure `pro_five_grade`()
begin
-- 需要定义接收游标数据的变量 
  declare a int default 0;
  declare b int default 0;
  declare c int default 0;
  declare d int default 0;
  declare e int default 0;
  declare score float;
  -- 遍历数据结束标志
  declare done int default false;
  -- 游标
  declare cur cursor for select scscore from sc;
  -- 将结束标志绑定到游标
  declare continue handler for not found set done = true;
  -- 打开游标
  open cur;
 
  -- 开始循环
  read_loop: loop
    -- 提取游标里的数据，这里只有一个，多个的话也一样；
    fetch cur into score;
    -- 声明结束的时候
    if done then
      leave read_loop;
    end if;

    if score>90 then
      set a=a+1 ;
    elseif score>80 then
      set b=b+1;
    elseif score>70 then
      set c=c+1;
    elseif score>60 then
      set d=d+1; 
    else
      set e=e+1; 
    end if;

  end loop;
  -- 关闭游标
  close cur;

  -- 输出结果  
  select a; 
  select b; 
  select c; 
  select d; 
  select e; 
end
;;
delimiter ;

-- ----------------------------
-- procedure structure for pro_sum
-- ----------------------------
drop procedure if exists `pro_sum`;
delimiter ;;
create procedure `pro_sum`(m int, n int, out result int)
begin
	set result = m + n;
end
;;
delimiter ;

-- ----------------------------
-- triggers structure for table sc
-- ----------------------------
drop trigger if exists `tri_update_sc`;
delimiter ;;
create trigger `tri_update_sc` before update on `sc` for each row begin
	set new.scscore=(new.scscore1+new.scscore2+new.scscore3)/3;
end
;;
delimiter ;

-- ----------------------------
-- triggers structure for table student
-- ----------------------------
drop trigger if exists `tri_ins_num`;
delimiter ;;
create trigger `tri_ins_num` after insert on `student` for each row begin
#select count(*) into @num from student; 
insert into tb_count values(new.sid);
end
;;
delimiter ;

set foreign_key_checks = 1;

⭐转载请注明出处

本文作者：双份浓缩馥芮白

原文链接：https://www.cnblogs.com/flat-white/p/14972243.html

版权所有，如需转载请注明出处。