- 附：本书中常用的数据库teaching中的表结构和表记录。-- 创建学生信息表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表insert into student values ('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','pingan@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');-- 创建课程信息表coursecreate 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)); -- 插入数据到课程信息表courseINSERT into course values('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');-- 创建学生分数表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) ); -- 插入数据到分数表scoreINSERT INTO score (studentno, courseno, daily, final)VALUES ('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);-- 创建教师信息表teachercreate 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)); -- 插入数据到教师信息表teacherinsert into teacher values('t05001', '苏超然', '软件工程', '教授', '计算机学院'),('t05002', '常可观', '会计学', '助教', '管理学院'),('t05003', '孙释安', '网络安全', '教授', '计算机学院'),('t05011', '卢敖治', '软件工程', '副教授','计算机学院'),('t05017', '茅佳峰', '软件测试', '讲师', '计算机学院'),('t06011', '夏期年', '机械制造', '教授', '机械学院'),('t06023', '卢释舟', '铸造工艺', '副教授','机械学院'),('t07019', '韩庭宇', '经济管理', '讲师', '管理学院'),('t08017', '白成园', '金融管理', '副教授','管理学院'),('t08058', '孙有存', '数据科学', '副教授','计算机学院');-- 创建纽带表teach_course。create table if not exists teach_course ( teacherno char(6) not null, courseno char(6) not null, primary key (teacherno,courseno) );-- 插入数据到纽带表teach_courseINSERT INTO teach_course (teacherno, courseno)VALUES ('t05001', 'c05103'),('t05002', 'c05109'),('t05003', 'c05127'),('t05011', 'c05138'),('t05017', 'c06108'),('t05017', 'c06172'),('t06011', 'c06127'),('t06023', 'c05127'),('t06023', 'c06172'),('t07019', 'c08106'),('t08017', 'c08123'),('t08058', 'c08171'); -- 创建选课信息表se_coursecreate 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_courseINSERT INTO se_course (sc_no, studentno, courseno, teacherno, score, sc_time)VALUES (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');查询teacher表中每一位教授的教师号、姓名和专业名称。
超级会员免费看
订阅专栏 解锁全文
扫一扫
769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
