MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。表1-1数据库的表结构表（一）Student (学生表)字段名...
·
设有一数据库，包括四个表：学生表（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
| Sno | Cno | Degree | |
| 103 | 3-245 | 86 | |
| 105 | 3-245 | 75 | |
| 109 | 3-245 | 68 | |
| 103 | 3-105 | 92 | |
| 105 | 3-105 | 88 | |
| 109 | 3-105 | 76 | |
| 101 | 3-105 | 64 | |
| 107 | 3-105 | 91 | |
| 108 | 3-105 | 78 | |
| 101 | 6-166 | 85 | |
| 107 | 6-166 | 79 | |
| 108 | 6-166 | 81 |
表（四）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;
无需安装部署，在线快速体验 Byzer
更多推荐
54
0- 0
已为社区贡献1条内容
所有评论(0)