MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）_数据库学生表、课程表和成绩表

设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。

 

  表1-1数据库的表结构

表（一）Student (学生表)                        

表（二）Course（课程表）

表（三）Score(成绩表)

表（四）Teacher(教师表)

表1-2数据库中的数据

表（一）Student

 

表（二）Course

表（三）Score

表（四）Teacher

 

 

建表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;