学生表
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
