学生表

CREATE DATABASE test7
USE test7

CREATE TABLE Student(
  SID VARCHAR(10) PRIMARY KEY COMMENT '学生编号',
  Sname VARCHAR(20) COMMENT '学生姓名',
  Sage DATETIME COMMENT '出生年月',
  Ssex VARCHAR(1) COMMENT '学生性别'
);
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');

课程表

CREATE TABLE Course(            
  CID VARCHAR(10) PRIMARY KEY COMMENT '课程编号',
  Cname VARCHAR(20) COMMENT '课程名称',
  TID VARCHAR(10) COMMENT '教师编号'
);
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

教师表

CREATE TABLE Teacher(
  TID VARCHAR(10) PRIMARY KEY COMMENT '教师编号',
  Tname VARCHAR(20) COMMENT '教师姓名'
);
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

成绩表

CREATE TABLE SC(
  SID VARCHAR(10) COMMENT '学生编号',
  CID VARCHAR(10) COMMENT '课程编号',
  score DOUBLE(4,1) COMMENT '分数',
  CONSTRAINT PRIMARY KEY (sid,cid)
);
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT * FROM student st INNER JOIN (SELECT s1.* FROM sc s1 WHERE s1.C='01' AND s1.score>
(SELECT s2.score FROM sc s2 WHERE s1.S=s2.S AND s2.C='02'))s ON st.S=s.S WHERE s.C!='03'
SELECT * FROM 

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT * FROM student st INNER JOIN (SELECT s1.* FROM sc s1 WHERE s1.C='01' AND s1.score<
(SELECT s2.score FROM sc s2 WHERE s1.S=s2.S AND s2.C='02'))s ON st.S=s.S WHERE s.C!='03'

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT  s.S,s.Sname,AVG(c.score)  FROM student s,sc c WHERE s.S=c.S 
GROUP BY s.S HAVING AVG(c.score)>=60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT  s.S,s.Sname,AVG(c.score)  FROM student s,sc c WHERE s.S=c.S 
GROUP BY s.S HAVING AVG(c.score)<=60

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.S,s.Sname,COUNT(c.C) '选课总数',SUM(c.score) 总成绩 FROM 
student s,sc c WHERE s.S=c.S GROUP BY s.S

6、查询"李"姓老师的数量

SELECT COUNT(*) FROM teacher t WHERE t.Tname LIKE '李%'

7、查询学过"张三"老师授课的同学的信息

SELECT DISTINCT s.*FROM student s,sc c,course cc,teacher t WHERE
 s.S=c.S AND  c.C=cc.C AND cc.T=t.T AND t.Tname='张三'

8、查询没学过"张三"老师授课的同学的信息

SELECT * FROM student WHERE s NOT IN( SELECT DISTINCT s.S  FROM student s,sc 
c,course cc,teacher t WHERE s.S=c.S AND  c.C=cc.C AND cc.T=t.T AND t.Tname='张三' )

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.* FROM student s,sc c1,sc c2 WHERE s.S=c1.S AND  s.S=c2.S AND c1.C='01' AND  c2.C='02'

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT * FROM student s,sc c WHERE s.S=c.S AND c.C='01' AND s.S 
NOT IN ( SELECT s FROM sc WHERE c='02')

11、查询没有学全所有课程的同学的信息

SELECT s.* FROM student s ,sc c WHERE  s.S=c.S GROUP BY s.S 
HAVING COUNT(c.score)<(SELECT COUNT(*) FROM course )

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT DISTINCT s.* FROM student s LEFT JOIN sc c ON s.S=c.S WHERE c.C 
IN (SELECT c FROM sc WHERE s='01') AND s.S<>'01'

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT * FROM (SELECT s.S,COUNT(c.score) aa FROM student s LEFT JOIN sc c ON s.S=c.S WHERE
 c.C IN (SELECT c FROM sc WHERE s='01')GROUP BY s.S HAVING COUNT(c.score)=(SELECT COUNT(*) FROM
 sc WHERE s='01')) a LEFT JOIN (
 SELECT s,COUNT(*) bb FROM sc GROUP BY s) b ON a.s=b.s  WHERE a.aa=b.bb AND a.s<>'01'

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

SELECT * FROM student WHERE s NOT IN( SELECT s.S FROM student s
,sc c,course cc,teacher t WHERE s.S=c.S AND c.C=cc.C AND cc.T=t.T AND t.Tname='张三')

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.S,s.Sname,ROUND(AVG(c.score)) FROM student s,sc c 
WHERE s.S=c.S AND c.score<60

16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT s.* , c.C , c.score FROM student s, sc c
WHERE s.S = C.S AND c.score < 60 AND c.C = '01'
ORDER BY c.score DESC  

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.S,s.Sname ,
MAX(CASE c.c WHEN '01' THEN c.score ELSE 0 END) 语文,
MAX(CASE c.c WHEN '02' THEN c.score ELSE 0 END) 数学,
MAX(CASE WHEN c.c='03' THEN c.score ELSE 0 END) 英语,
MAX(CASE WHEN c.c='04' THEN c.score ELSE 0 END) 物理,
AVG(c.`score`) a FROM student s LEFT JOIN sc c ON s.S=c.S
GROUP BY s.S
ORDER BY a DESC

1.堆和栈的区别.png