SQL查询技巧-优快云博客

#学生表及数据

CREATE TABLE `student` (
  `S#` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Sage` datetime DEFAULT NULL,
  `Ssex` varchar(10) DEFAULT NULL
)

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 `teacher` (
  `T#` VARCHAR(10) DEFAULT NULL,
  `Tname` VARCHAR(10) DEFAULT NULL
)

INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

#课程表及数据

CREATE TABLE `course` (
  `C#` VARCHAR(10) DEFAULT NULL,
  `Cname` VARCHAR(10) DEFAULT NULL,
  `T#` VARCHAR(10) DEFAULT NULL
)

INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

#成绩表及数据

CREATE TABLE `sc` (
  `S#` VARCHAR(10) DEFAULT NULL,
  `C#` VARCHAR(10) DEFAULT NULL,
  `score` DECIMAL(18,1) DEFAULT NULL
)


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);

SELECT * FROM Student #学生表
SELECT * FROM Teacher #老师表
SELECT * FROM Course  #课程表
SELECT * FROM SC      #学生成绩表

#注:考试表内有学员指定课程的记录,就说明该学生选修了该课程

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

SELECT sc.`s#`,sname,AVG(score) FROM sc ,student st
WHERE sc.`s#`=st.`s#`
GROUP BY sc.`s#`
HAVING AVG(score)>=60;

#2.查询在sc表存在成绩的学生信息

SELECT * FROM student st
WHERE `s#` IN (
    SELECT `s#` FROM sc
)
GROUP BY `s#`;

#3.查询在sc表至少参加了3门课程考试的学生信息

SELECT * FROM student
WHERE `s#` IN (
    SELECT `S#` FROM SC
    GROUP BY `s#`
    HAVING COUNT(1)>=3
);

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

SELECT st.`s#`,sname,COUNT(sc.`s#`) AS'选课总数',IFNULL(SUM(sc.score),0) AS'总成绩' FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
GROUP BY st.`s#`;

#5.查询所有同学的学生编号、学生姓名、平均成绩，并按平均成绩降序排列

SELECT st.`s#`,sname,IFNULL(AVG(sc.score),0) AS'平均成绩' FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
GROUP BY st.`s#`
ORDER BY IFNULL(AVG(sc.score),0) DESC;

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

SELECT st.* FROM student st,sc
WHERE st.`s#`=sc.`s#`
AND sc.`c#`=(
    SELECT `c#` FROM course
    WHERE `t#`=(
        SELECT `t#` FROM teacher
        WHERE tname='张三'
    )
);

#7.查询学过"01"又学过"02"的课程的同学的信息

SELECT st.* FROM student st
JOIN sc
ON st.`s#`=sc.`s#`
WHERE `c#`='01' OR `c#`='02'
GROUP BY st.`s#`
HAVING COUNT(sc.`c#`)>=2;

#8.查询学过"01"但没学过"02"的课程的同学的信息

SELECT * FROM student
WHERE `s#` IN (
    SELECT `s#` FROM sc a
    WHERE `c#`='01'
)
AND `s#` NOT IN(
    SELECT `s#` FROM sc b
    WHERE `c#`='02'
);

#9.查询没有学全所有课程的同学的信息

SELECT st.* FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
GROUP BY st.`s#`
HAVING COUNT(sc.`s#`)<(SELECT COUNT(1) FROM course);

#10.查询至少有一门课程与"01"同学所学相同的同学的信息

SELECT * FROM student
WHERE `s#` IN (
    SELECT `s#` FROM sc
    WHERE `c#` IN (
        SELECT `c#` FROM sc
        WHERE `s#`='01'
    )
    GROUP BY `s#`
);

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

SELECT st.* FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
GROUP BY st.`s#`
HAVING COUNT(sc.`s#`)<(SELECT COUNT(1) FROM course);

#12.查询没学过"张三"老师课程的学生姓名

SELECT * FROM student
WHERE `s#` NOT IN (
    SELECT `s#` FROM sc
    WHERE `c#`=(
        SELECT `c#` FROM course
        WHERE `t#`=(
            SELECT `t#` FROM teacher
            WHERE tname='张三'    
        )
    )
);

#13.查询两门及以上不及格课程的同学的学号，姓名及其平均成绩
#14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT st.`s#`,sname,IFNULL(AVG(sc.score),0) AS'平均成绩' FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
WHERE st.`s#` IN(
    SELECT `s#` FROM sc
    WHERE score<60
    GROUP BY `s#`
    HAVING COUNT(1)>=2
)OR st.`s#` IN(
    SELECT `s#` FROM student
    WHERE `s#` NOT IN (
        SELECT `s#` FROM sc
    )
)
GROUP BY st.`s#`;

#15.检索"01"课程分数小于60，按分数降序排列的学生编号、姓名、课程、成绩信息

SELECT st.`s#`,sname,IFNULL(`c#`,'01') AS'课程',IFNULL(sc.score,0) AS'成绩' FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
WHERE st.`s#`IN(
	SELECT `s#` FROM student
	WHERE `s#` NOT IN (
		SELECT `s#` FROM sc
	)
)
OR (`c#`='01' 
	AND st.`s#` IN(
		SELECT `s#` FROM sc
		WHERE score<60 AND `c#`='01'
	)

)
GROUP BY st.`s#`
ORDER BY IFNULL(sc.score,0) DESC;

#16.查询比"01"学生的平均成绩要高的学生信息

SELECT st.* FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
GROUP BY st.`s#`
HAVING IFNULL(AVG(sc.score),0)>(
	SELECT AVG(score) FROM sc
	WHERE `s#`='01'
);

#17.查询比"01"学生的平均成绩要底于10分以上的学生信息

SELECT st.* FROM student st
LEFT JOIN sc
ON st.`s#`=sc.`s#`
GROUP BY st.`s#`
HAVING (IFNULL(AVG(sc.score),0)+10)<(
	SELECT AVG(score) FROM sc
	WHERE `s#`='01'
);

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

SELECT st.*,a.score AS'课程01成绩',b.score AS'课程02成绩' FROM student st,sc a,sc b
WHERE a.score>b.score AND a.`s#`=b.`s#` AND a.`s#`=st.`s#` AND a.`c#`='01' AND b.`c#`='02';