MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher） |
您所在的位置：网站首页 › 课程表的英文怎么读 › MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher） |
MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
|
设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。
表1-1数据库的表结构 表（一）Student (学生表) 字段名 数据类型 可否为空 含 义 Sno Varchar2(3) 否 学号（主键） Sname Varchar2(8) 否 学生姓名 Ssex Varchar2(2) 否 学生性别 Sbirthday Date 可 学生出生年月 SClass Varchar2(5) 可 学生所在班级 表（二）Course（课程表） 属性名 数据类型 可否为空 含 义 Cno Varchar2(5) 否 课程号（主键） Cname Varchar(10) 否 课程名称 Tno Varchar2(3) 否 教工编号（外键） 表（三）Score(成绩表) 属性名 数据类型 可否为空 含 义 Sno Varchar2(3) 否 学号（外键） Cno Varchar2(5) 否 课程号（外键） Degree Number(4,1) 可 成绩 主键：Sno+ Cno 表（四）Teacher(教师表) 属性名 数据类型 可否为空 含 义 Tno Varchar2(3) 否 教工编号（主键） Tname Varchar2(4) 否 教工姓名 Tsex Varchar2(2) 否 教工性别 Tbirthday Date 可 教工出生年月 Prof Varchar2(6) 可 职称 Depart Varchar(10) 否 教工所在部门 表1-2数据库中的数据 表（一）Student
Sno Sname Ssex Sbirthday class
108 曾华 男 1977/09/01 95033
105 匡明 男 1975/10/02 95031
107 王丽 女 1976/01/23 95033
101 李军 男 1976/02/20 95033
109 王芳 女 1975/02/10 95031
103 陆君 男 1974/06/03 95031
表（二）Course
Cno Cname Tno
3-105 计算机导论 825
3-245 操作系统 804
6-166 数字电路 856
9-888 高等数学 831 表（三）Score SnoCnoDegree 1033-24586 1053-24575 1093-24568 1033-10592 1053-10588 1093-10576 1013-10564 1073-10591 1083-10578 1016-16685 1076-16679 1086-16681表（四）Teacher
Tno Tname Tsex Tbirthday Prof Depart
804 李诚 男 1958/12/02 副教授 计算机系
856 张旭 男 1969/03/12 讲师 电子工程系
825 王萍 女 1972/05/05 助教 计算机系
831 刘冰 女 1977/08/14 助教 电子工程系
建表sql: create DATABASE testdb; use testdb; DROP TABLE IF EXISTS `Course`; CREATE TABLE `Course` ( `Cno` varchar(5) NOT NULL, `Cname` varchar(10) NOT NULL, `Tno` varchar(3) NOT NULL, PRIMARY KEY (`Cno`), KEY `Tno` (`Tno`), CONSTRAINT `Course_ibfk_1` FOREIGN KEY (`Tno`) REFERENCES `Teacher` (`Tno`) ) ENGINE = InnoDB CHARSET = utf8; INSERT INTO `Course` (`Cno`, `Cname`, `Tno`) VALUES ('3-105', '计算机导论', '825'), ('3-245', '操作系统', '804'), ('6-166', '数字电路', '856'), ('9-888', '高等数学', '831'); /*Table structure for table `Score` */ DROP TABLE IF EXISTS `Score`; CREATE TABLE `Score` ( `Sno` varchar(3) NOT NULL, `Cno` varchar(5) NOT NULL, `Degree` decimal(4, 1) DEFAULT NULL, PRIMARY KEY (`Sno`, `Cno`), KEY `Cno` (`Cno`), CONSTRAINT `Score_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `Student` (`Sno`), CONSTRAINT `Score_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `Course` (`Cno`) ) ENGINE = InnoDB CHARSET = utf8; INSERT INTO `Score` (`Sno`, `Cno`, `Degree`) VALUES ('101', '3-105', '64.0'), ('101', '6-166', '85.0'), ('103', '3-105', '92.0'), ('103', '3-245', '86.0'), ('105', '3-105', '88.0'), ('105', '3-245', '75.0'), ('107', '3-105', '91.0'), ('107', '6-166', '79.0'), ('108', '3-105', '78.0'), ('108', '6-166', '81.0'), ('109', '3-105', '76.0'), ('109', '3-245', '68.0'); /*Table structure for table `Student` */ DROP TABLE IF EXISTS `Student`; CREATE TABLE `Student` ( `Sno` varchar(3) NOT NULL, `Sname` varchar(8) NOT NULL, `Ssex` varchar(2) NOT NULL, `Sbirthday` date DEFAULT NULL, `Sclass` varchar(5) DEFAULT NULL, PRIMARY KEY (`Sno`) ) ENGINE = InnoDB CHARSET = utf8; INSERT INTO `Student` (`Sno`, `Sname`, `Ssex`, `Sbirthday`, `Sclass`) VALUES ('101', '李军', '男', '1976-02-20', '95033'), ('103', '陆君', '男', '1974-06-03', '95031'), ('105', '匡明', '男', '1975-10-02', '95031'), ('107', '王丽', '女', '1976-01-23', '95033'), ('108', '曾华', '男', '1977-09-01', '95033'), ('109', '王芳', '女', '1975-02-10', '95031'); /*Table structure for table `Teacher` */ DROP TABLE IF EXISTS `Teacher`; CREATE TABLE `Teacher` ( `Tno` varchar(3) NOT NULL, `Tname` varchar(4) NOT NULL, `Tsex` varchar(2) NOT NULL, `Tbirthday` date DEFAULT NULL, `Tprof` varchar(6) DEFAULT NULL, `Depart` varchar(10) NOT NULL, PRIMARY KEY (`Tno`) ) ENGINE = InnoDB CHARSET = utf8; INSERT INTO `Teacher` (`Tno`, `Tname`, `Tsex`, `Tbirthday`, `Tprof` , `Depart`) VALUES ('804', '李诚', '男', '1958-12-02', '副教授', '计算机系'), ('825', '王萍', '女', '1972-05-05', '助教', '计算机系'), ('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系'), ('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系'); select t.* from Student t; select t.* from Course t; select t.* from Teacher t; select t.* from Score t; -- 1、查询教师所有的单位即不重复的Depart列。 select distinct Depart from Teacher; -- 2、查询Score表中成绩在60到80之间的所有记录。 select * from Score where degree > 60 and degree < 80; select * from Score where degree between 60 and 80; -- 3、查询Score表中成绩为85，86或88的记录。 select * from Score where Degree=85 or Degree=86 or degree=88; select * from Score where Degree in (85,86,88); -- 4、查询Student表中“95031”班或性别为“女”的同学记录。 select * from Student where sclass=95031 or ssex='女'; -- 5、以Sclass降序查询Student表的所有记录。 select * from Student order by sclass desc; -- 6、以Cno升序、Degree降序查询Score表的所有记录。 select * from Score order by sno asc,degree desc; -- 7、查询“95031”班的学生人数。 select count(sno) 学生人数,sclass from Student where sclass = '95031'; -- 8、查询Score表中的最高分的学生学号和课程号。（子查询或者排序） select t.sno,t.cno,t.degree from Score t where t.degree in (select max(degree) from Score); select sno,cno,degree from Score order by Degree desc limit 0,1; -- 9、查询每门课的平均成绩。 select cno,avg(degree) from Score group by cno; -- 10、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select cno,avg(degree) from Score where cno like '3%' group by cno having count(cno)>5; -- 11、查询所有学生的Sname、Cno和Degree列。 -- 多表查询 -- 可以用where,内连接，子查询 -- 左连接以左表为准匹配，没有的右侧都为null： select t.sname,s.cno,s.degree from Student t,Score s where t.Sno = s.sno; select t.sname,s.cno,s.degree from Student t inner join Score s on t.sno = s.sno; select t.sname,s.cno,s.degree from Student t left join Score s on t.sno = s.sno; -- 拓展，如果条件写在on上，则把on的所有条件作为匹配条件，不符合的右表都为null。where与on的区别（没看出来啊） select t.sname,s.cno,s.degree from Student t left join Score s on t.sno = s.sno and s.cno = '3-105'; select t.sname,s.cno,s.degree from Student t left join Score s on t.sno = s.sno where s.cno = '3-105'; -- 12、查询所有学生的Sno、Cname和Degree列。 -- 多表查询 -- 可以用where,内连接，子查询 select t.sno,c.cname,t.degree from Score t,Course c where t.cno = c.cno; select t.sno,c.cname,t.degree from Score t left join Course c on t.cno = c.cno; -- 13、查询所有学生的Sname、Cname和Degree列。 -- 多表查询 -- 可以用where,内连接，子查询 select t.sname,c.cname,s.degree from Student t,Score s,Course c where t.sno = s.sno and s.cno = c.cno; select t.sname,c.cname,s.degree from Student t join Score s on t.sno = s.sno join Course c on s.cno = c.cno; select t.sname,c.cname,s.degree from Student t inner join Score s on t.Sno=s.Sno inner join Course c on s.cno = c.cno; -- 14、查询“95033”班学生的平均分。 select s.sclass,avg(t.degree) from Score t,Student s where s.sno = t.sno and s.sclass='95033' ; select s.sclass,avg(t.degree) from Score t inner join Student s on s.sno = t.sno where s.sclass='95033'; select avg(t.degree) from Score t where t.sno in (select s.sno from Student s where s.sclass='95033'); -- 查询平均分 select avg(t.degree) from Score t; -- 15、假设使用如下命令建立了一个Grade表： create table Grade(low int(3),upp int(3),rank char(1)); insert into Grade values(90,100,'A'); insert into Grade values(80,89,'B'); insert into Grade values(70,79,'C'); insert into Grade values(60,69,'D'); insert into Grade values(0,59,'E'); select * from Grade;
|
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |