数据库作业

目录

数据库teaching中的表结构和表记录。

问题：

答案：

---

数据库teaching中的表结构和表记录。

　　　（1）学生信息表student
　　　#student表结构
　　　　　create table if not exists student (
　　　　　studentno  char(11) not null comment'学号', 
　　　　　sname char(8) not null comment'姓名', 
　　　　　sex enum('男', '女') default '男' comment'性别', 
　　　　　birthdate date not null comment'出生日期', 
　　　　　entrance int(3)  null comment'入学成绩',         
　　　　　phone varchar(12) not null comment'电话', 
　　　　　Email varchar(20) not null comment'电子信箱',
　　　　　primary key (studentno)
　　　　　);
　　　#student表常用数据
　　　　　studentno    sname sex    birthdate   entrance      phone          Email
　　　　　20112100072    许东方  男    2002/2/4         658      12545678998    su12@163.com 
　　　　　20112111208    韩吟秋  女    2002/2/14    666      15878945612    han@163.com 
　　　　　20120203567    封白玫  女    2003/9/9         898      13245674564    feng@126.com
　　　　　20120210009    崔舟帆  男    2002/11/5    789      13623456778    cui@163.com 
　　　　　20123567897    赵雨思  女    2003/8/4     879      13175689345    pinan@163.com
　　　　　20125121109    梁一苇  女    2002/9/3      777      13145678921    bing@126.com 
　　　　　20126113307    姚扶竹  女    2003/9/7      787      13245678543    zhu@163.com
　　　　　21125111109    敬秉辰  男    2004/3/1         789      15678945623 jing@sina.com 
　　　　　21125221327    何桐影  女    2004/12/4    879      13178978999  he@sina.com 
　　　　　21131133071    崔依歌  男    2002/6/6         787   15556845645  cui@126.com 
　　　　　21135222201    夏文斐  女    2005/10/6    867      15978945645  xia@163.com 
　　　　　21137221508    赵临江  男    2005/2/13    789      12367823453  ping@163.com 

　　　（2）课程信息表course
　　　#course表结构
　　　　　create table if not exists course (
　　　　　courseno  char(6) not null, 
　　　　　cname  char(6) not null, 
　　　　　type char(8) not null,  
　　　　　period int(2) not null, 
　　　　　exp int(2) not null,
　　　　　term int(2) not null,
　　　　　primary key (courseno)
　　　　　); 
　　　#course表常用数据
　　　　　courseno    cname        type   period    exp    term
　　　　　c05103        高等数学        必修        64        16     2
　　　　　c05109        C语言        必修        48        16     2
　　　　　c05127        数据结构        必修        64        16     2
　　　　　c05138        软件工程        选修        48        8     5
　　　　　c06108        机械制图        必修        60        8     2
　　　　　c06127        机械设计        必修        64        8     3
　　　　　c06172        铸造工艺        选修        42        16     6
　　　　　c08106        经济法        必修        48        0     7
　　　　　c08123        金融学        必修        40        0     5
　　　　　c08171        会计软件        选修        32        8     8

　　　（3）成绩信息表score
　　　#score表结构
　　　　　create table if not exists score(
　　　　　studentno  char(11) not null, 
　　　　　courseno  char(6) not null, 
　　　　　daily float(4,1) default 0, 
　　　　　final float(4,1) default 0,
　　　　　primary key (studentno , courseno) 
　　　　　); 
　　　#score表常用数据
　　　　　studentno    courseno     daily    final
　　　　　20112100072    c05103        99         92
　　　　　20120203567    c05103          78        67
　　　　　20120210009    c05103        65        98
　　　　　20125121109    c05103        88        79
　　　　　21125111109    c05103        96        97
　　　　　21137221508    c05103        77        92
　　　　　20112100072    c05109        95        82
　　　　　20120203567    c05109        87        86
　　　　　20125121109    c05109        77        82
　　　　　20126113307    c05109        89        95
　　　　　21125111109    c05109        87        82
　　　　　21125221327    c05109        89        95
　　　　　20120210009    c05138        88        89
　　　　　21137221508    c05138        74        91
　　　　　20112111208    c06108        77        82
　　　　　20120210009    c06108        79        88
　　　　　20123567897    c06108        99        99
　　　　　20126113307    c06108        78        67
　　　　　20112111208    c06127        85        91
　　　　　20120203567    c06127        97        97
　　　　　20112111208    c06172        89        95
　　　　　21125221327    c06172        88        62
　　　　　21131133071    c06172        78        95
　　　　　21125111109    c08106        77        91
　　　　　21135222201    c08106        91        77
　　　　　21137221508    c08106        89        62
　　　　　21131133071    c08123        78        89
　　　　　21135222201    c08123        79        99
　　　　　20112100072    c08171        82        69
　　　　　20125121109    c08171        85        91
　　　　　21131133071    c08171        88        98
　　　　　21135222201    c08171        85        92
　　　　　
　　　（4）教师信息表teacher
　　　#teacher表结构
　　　　　create table if not exists teacher (
　　　　　teacherno  char(6) not null comment '教师编号', 
　　　　　tname  char(8) not null comment'教师姓名', 
　　　　　major  char(10) not null comment '专业', 
　　　　　prof char(10) not null comment '职称',
　　　　　department char(16) not null comment '部门',
　　　　　primary key (teacherno)
　　　　　); 
　　　#teacher表常用数据
　　　　　teacherno    tname        major        prof     department
　　　　　t05001        苏超然        软件工程        教授        计算机学院
　　　　　t05002        常可观        会计学        助教        管理学院
　　　　　t05003        孙释安        网络安全        教授        计算机学院
　　　　　t05011        卢敖治        软件工程        副教授    计算机学院
　　　　　t05017        茅佳峰        软件测试        讲师        计算机学院
　　　　　t06011        夏期年        机械制造        教授        机械学院
　　　　　t06023        卢释舟        铸造工艺        副教授    机械学院
　　　　　t07019        韩庭宇        经济管理        讲师        管理学院
　　　　　t08017        白成园        金融管理        副教授    管理学院
　　　　　t08058        孙有存        数据科学        副教授    计算机学院

　　　（5）纽带表teach_course
　　　#teach_course表结构
　　　　　create table if not exists teach_course (
　　　　　teacherno char(6) not null, 
　　　　　courseno  char(6) not null, 
　　　　　primary key (teacherno,courseno) 
　　　　　);
　　　#teach_course表常用数据
　　　　　teacherno  courseno
　　　　　t05001        c05103
　　　　　t05002        c05109
　　　　　t05003        c05127
　　　　　t05011        c05138
　　　　　t05017        c06108
　　　　　t05017        c06172
　　　　　t06011        c06127
　　　　　t06023        c05127
　　　　　t06023        c06172
　　　　　t07019        c08106
　　　　　t08017        c08123
　　　　　t08058        c08171

　　　（6）选课信息表se_course
　　　#se_course表结构
　　　　　create table se_course
　　　　　(sc_no int(6) not null auto_increment, 
　　　　　studentno  char(11) not null, 
　　　　　courseno  char(6) not null, 
　　　　　teacherno char(6) not null,  
　　　　　score   int(3)    null,
　　　　　sc_time timestamp not null default now(), 
　　　　　primary key (sc_no)
　　　　　);
　　　#se_course表常用数据示例
　　　　　sc_no  studentno    courseno  teacherno  score   sc_time       
　　　　　1       21125111109   c06172    t05017     NULL    2020-12-09 18:33:45
　　　　　2       20120210009    c06108      t06023     NULL    2020-12-24 18:30:15
　　　　　3       20123567897    t01239      t05003     NULL    2020-12-26 18:09:09

 

问题：

（1）在course表的cname列上创建索引IDX_cname。

（2）在student表的studentno和phone列上创建唯一索引uq_stu。并输出student表中的记录，查看输出结果的顺序。

（3）创建一个视图v_teacher，查询所有“计算机学院”教师的信息。

（4）创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排序。

（5）修改v_teacher的视图定义，添加with check option选项。

（6）通过视图v_teacher向基表teacher中分别插入数据（'t05039'，'张馨月'，'计算机应用'， '讲师'， '计算机学院）和（'t06018'， '李书诚'， '机械制造'，'副教授'，'机械学院'），并查看插入数据情况。

（7）通过视图v_teacher将基表teacher中教师编号为t05039的教师职称修改为‘副教授’。

答案：

1.

create index IDX_cname on course (cname);

select * from course;

2.

create unique index uq_stu on student (studentno, phone);

select * from student;

3.

create view v_teacher as select * from teacher where teacher.department='计算机学院';

select * from v_teacher;

4.

create view v_avgstu as select student.studentno,student.sname,avg(score.final) as avg from student join score on student.studentno=score.studentno group by score.studentno order by avg desc;

select * from v_avgstu;

5.

create view v_teacher as select * from teacher where teacher.department='计算机学院' with check option;

select * from v_teacher;

6.（注意：这里需要使用第3题创建的视图，因为这里插入了机械学院的数据）

insert into v_teacher(teacherno,tname,major,prof,department) values('t05039','张馨月','计算机应用','讲师','计算机学院');

insert into v_teacher(teacherno,tname,major,prof,department) values('t06018', '李书诚', '机械制造','副教授','机械学院');

select * from v_teacher;

7.

update v_teacher set prof='副教授' where teacherno='t05039';

select * from v_teacher;