学生选修课程数据库，学生表、课程表、选修表，列出在选修张青老师所教授课程的学生中，成绩最高的学生姓名和成绩

项目背景介绍：本次的数据库设计主要涉及到学生信息、课程信息与成绩信息的相关查询与编辑。任务概述：

具体关系数据库如下：

现有一个学生选修课程的数据库，其中存放以下三个表：

学生（学号，姓名，性别，年龄，系别；

课程（课程号，课程名，任课教师）；

选修（学号，课程号，分数）

用SQL语言实现下列功能：

（1）、建表，在定义中要求声明：

　A、每个表的主外码。

　B、学生的年龄介于16到30。

　C、学生的姓名和课程名不能为空。

　D、选课成绩取0到100的整数。

（2）、插入如下数据：

学生（101，张三，男，16，数学

　102，李四，男，18，计算机

　103，王玲，女，17，中文

　105，李飞，男，19，计算机

　109，赵四，女，18，历史

　110，李平，男，20，化学）

课程（203，操作系统，程羽

　　　279，高等数学，王备

　　　210，现代文学，王林

　　　243，有机化学，沈同

　　　204，数据结构，张青)

选修（101,203,82

　　　105,203,59

　　　102,279,90

　　　101,279,88

　　　105,279,82

　　　110,279,68

　　　109,210,72

　　　103,210,90

　　　110,243,92

　　　101,204,85

　　　105,204,91

　　　102,204,56）

创建生选修课程数据库、创建三张表，向学生选修课程数据库中创建三张表并录入数据：

create table 学生 
(
学号 tinyint,
姓名 char(10) not null,
性别 char(2) default '男' check(性别 in('男','女')),
年龄 smallint default 0,
系别 char(20),
primary key(学号)
);
desc 学生;


insert into 学生 values 
(101,'张三','男',16,'数学'),
(102,'李四','男',18,'计算机'),
(103,'王玲','女',17,'中文'),
(105,'李飞','男',19,'计算机'),
(109,'赵四','女',18,'历史'),
(110,'李平','男',20,'化学');


create table 课程 
(
课程号 smallint,
课程名 char(10) not null default '',
任课教师 char(10) default '',
primary key(课程号)
);
desc 课程;


insert into 课程 values 
(203,'操作系统','程羽'),
(279,'高等数学','王备'),
(210,'现代文学','王林'),
(243,'有机化学','沈同'),
(204,'数据结构','张青');


create table 选修
(
学号 tinyint,
课程号 smallint,
分数 tinyint unsigned default 0,
CONSTRAINT 
`学生_选修` 
FOREIGN KEY (`学号`) REFERENCES `学生` (`学号`),
CONSTRAINT 
`课程_选修` 
FOREIGN KEY (`课程号`) REFERENCES `课程` (`课程号`)
);
desc 选修;


insert into 选修 values 
(101,203,82),
(105,203,59),
(102,279,90),
(101,279,88),
(105,279,82),
(110,279,68),
(109,210,72),
(103,210,90),
(110,243,92),
(101,204,85),
(105,204,91),
(102,204,56);

（3）、列出张三同学选修的所有课程的名称和成绩

select 课程.`课程名`,`选修`.`分数` as 成绩 from 学生,课程,选修
where 
`学生`.学号=`选修`.`学号`
and
`课程`.`课程号`=`选修`.`课程号`
and
姓名='张三';

查询结果：

（4）、列出所有课程都及格了的同学的名字

select 姓名,分数 from 学生,课程,选修
where 
`学生`.学号=`选修`.`学号`
and
`选修`.分数>=60  group by `学生`.学号;

查询结果：

（5）、列出在选修张青老师所教授课程的学生中，成绩最高的学生姓名和成绩

select 姓名,分数 
from 学生,选修 
where `学生`.学号 in (select `选修`.学号 from 课程,选修 where  `课程`.`课程号`=`选修`.`课程号` and 任课教师='张青' )
order by 分数 desc;

特别注意：

这里sql语句如下写成下面这样：

select 姓名,分数 
from 学生,选修 
where `学生`.学号 = (select `选修`.学号 from 课程,选修 where  `课程`.`课程号`=`选修`.`课程号` and 任课教师='张青' )
order by 分数 desc;

运行sql语句，系统会报错。

报错信息:

　　ERROR 1242 (21000): Subquery returns more than 1 row

- 报错信息翻译:

　　子查询返回超过1行

　- 分析与解决方法:

　　1. 在重复写入时会出现这种问题, 可通过去掉重复数据解决

　　　　- 通过在写入时加逻辑判断或者外键防止数据重复写入

　　 2. 利用IN、SOME、ANY、ALL关键字进行限制

　　　　- 报错信息出自子查询, 因此需要对子查询涉及指令进行条件修改

最终解决后的SQL

/*
  其实就是将"="号 改为"IN"。
  原因是"="号的后面取的可以使具体的值也可以是空值，不是判断关键词，
  但是"IN"是会与后面的数据值进行判断的，可以作为判断关键词。
*/
select 姓名,分数 
from 学生,选修 
where `学生`.学号 in (select `选修`.学号 from 课程,选修 where  `课程`.`课程号`=`选修`.`课程号` and 任课教师='张青' )
order by 分数 desc;

子查询：列出在选修张青老师所教授课程的学生中，这里有两个条件（1）任课教师是张青 （2）课程选修选的是张青老师的课

 (select `选修`.学号 from 课程,选修 where  `课程`.`课程号`=`选修`.`课程号` and 任课教师='张青' )

查询结果：

查询出 3个学生在选修张青老师所教授的课程。

接着，在这3个学生中，再找出 成绩最高的学生姓名和成绩

select 姓名,max(分数) as 最高分
from 学生,选修 
where 学生.学号=选修.学号 and 学生.学号 
in (select 学号 from 课程,选修 where 选修.课程号=课程.课程号 and 任课教师='张青')
order by 分数 desc;

（6）、删除所有成绩不及格的选课记录

delete from 选修 where 分数<60;

（7）、将105号同学选修203号课程的成绩改为该门课程的平均成绩