成绩查询表mysql_Mysql经典四表查询（学生表 教师表 课程表 成绩表）

**建立student表**

属性有:编号：id (主键，自动增长),姓名:sname,出生年月:sage,性别:ssex(枚举)

create table student(sid int primary key auto_increment,

sname varchar(20),

sage date,

ssex enum(‘男’,‘女’));

**建立教师表**

create table teacher(tid int primary key auto_increment,

tname varchar(20));

**建立课程表**

create table course(cid int primary key auto_increment,

cname varchar(20),

tid int,

foreign key(tid) references teacher(tid));

**建立成绩表**

create table sc(sid int,

cid int,

score int);

**插入数据**

1，先给student表插入数据

insert into student values(1,'赵雷','1990-01-01','男'),

(2,'钱电','1990-12-21','男'),

(3,'孙风','1990-05-20','男'),

(4,'李云','1990-08-06','男'),

(5,'周梅','1991-12-01','女'),

(6,'吴兰','1992-03-01','女'),

(7,'郑竹','1989-07-01','女'),

(8,'王菊','1990-01-20','女');

2, 给teacher表插入数据,这里不可以先给course表插入数据,因为course表外链接到teacher的主键

insert into teacher values(1,'张三'),

(2,'李四'),

(3,'王五');

3, 给course表插入数据

insert into course values(1,'语文',2),

(2,'语文',1),

(3,'语文',3);

4, 最后给sc表插入数据(题目图片少了第1个学生成绩，在这加上 1,1,90;  1,2,80;  1,3,90)

insert into sc values(1,1,90),

(1,2,80),

(1,3,90),

(2,1,70),

(2,2,60),

(2,3,80),

(3,1,80),

(3,2,80),

(3,3,80),

(4,1,50),

(4,2,30),

(4,3,20),

(5,1,76),

(5,2,87),

(6,1,31),

(6,3,34),

(7,2,89),

(7,3,98);

**1.查询出只有两门课程的全部学生的学号和姓名**

SELECT * FROM student s

WHERE s.sid in

(SELECT sc.sid FROM sc

GROUP BY sc.sid

HAVING COUNT(*)=2);

**2.查询没有学全所有课程的同学的信息**

SELECT s.sname,cname,score FROM student s,course,sc

WHERE sc.sid=s.sid

AND sc.cid=course.cid;

**3.查询选修了全部课程的学生信息**

SELECT * FROM student s

WHERE s.sid in

(SELECT sc.sid from sc

GROUP BY sc.sid

HAVING COUNT(*)=(SELECT COUNT(*) from course));

------------------------------------

SELECT * FROM student

WHERE NOT EXISTS

(SELECT * FROM course

WHERE NOT EXISTS

(SELECT *

FROM sc

WHERE sc.sid=student.sid AND sc.cid=course.cid));

**4.查询学过"张三"老师授课的同学的信息**

SELECT * FROM student  s,sc,teacher t, course

WHERE sc.sid=s.sid

AND course.cid=sc.cid

AND course.tid=t.tid

AND  t.tname='张三';

**5.查询没学过"张三"老师讲授的任一门课程的学生姓名**

SELECT * FROM student s

WHERE s.sid not in

(SELECT sc.sid FROM sc ,course,teacher

WHERE  sc.cid=course.cid

AND teacher.tid=course.tid AND teacher.tname='张三');

**6.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩**

SELECT s.sid,s.sname,AVG(score) from sc ,student s

WHERE  s.sid=sc.sid

GROUP BY sid

HAVING avg(score)>80;

**7.查询任何一门课程成绩在70分以上的姓名、课程名称和分数**

SELECT s.sid,s.sname,c.cname,score

FROM student s,sc,course c

WHERE sc.sid=s.sid

AND sc.cid=c.cid

AND score>70;

**8.查询不及格的课程**

SELECT s.sname,c.cname,score FROM student s,course c,sc

WHERE sc.sid=s.sid

AND sc.cid=c.cid

AND sc.score<60;

**9.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；**

SELECT s.sid,s.sname FROM student s ,sc

WHERE s.sid=sc.sid

AND cid=1

AND score>80;

**10.求每门课程的查询选修了全部课程的学生信息学生人数**

select cid,count(sid) from sc

group by sc.cid;

**11.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息**

SELECT s.* FROM student s,sc

WHERE sc.sid=s.sid

AND sc.cid=01

AND EXISTS

(SELECT 1 FROM sc sc1

WHERE  sc1.sid=sc.sid

AND sc1.cid=02);

//EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS 指定一个子查询，检测 行 的存在。

//select 1 from table;与select anycol(目的表集合中的任意一行) from table;  表里如果有记录，就显示 1  简单理解就是不查询具体列，只要有值就显示1

**12.查询"01"课程比"02"课程成绩高的学生的信息及课程分数**

SELECT s.*,a.score '课程01的分数',b.score '课程02的分数'

FROM student s,sc a,sc b

WHERE   a.sid=s.sid

AND b.sid=s.sid

AND a.cid=01

AND b.cid=02

AND a.score>b.score;