声明
- 不保证习题答案完全正确,仅供参考
- 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', 'cs@163.com'); insert into `dept` values ('el', '外校', null, '62765678', null); insert into `dept` values ('em', '经济管理系', 'sc302', '87464789', 'em@163.com'); insert into `dept` values ('fd', '外语分院', 'sa401', '65656798', 'fd@163.com'); insert into `dept` values ('it', '信息科学与技术系', 'sl704', '88767864', 'it@163.com'); insert into `dept` values ('sd', '理学院', 'nb309', '67536387', 'sd@163.com'); -- ---------------------------- -- 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
版权所有,如需转载请注明出处。