目录
-- 3.查询每个同学的最高成绩和科目名称****（子查询）
-- 5.查询每个课程的最高分的学生信息*****（子查询）
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
-- 21. 查询有且仅有一个课程成绩在80分以上的学生信息
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
一、构建数据库
（1）student表
DROP TABLE IF EXISTS student;/*如果存在student表就删除*/ CREATE TABLE `student` ( `id` INT ( 10 ) PRIMARY KEY,/*主键*/ `name` VARCHAR ( 10 ), `age` INT ( 10 ) NOT NULL, `gender`VARCHAR(2) );
（2）course表
DROP TABLE IF EXISTS course; CREATE TABLE `course` ( `id` INT ( 10 ) PRIMARY KEY,/*主键*/ `name` VARCHAR ( 10 ), `t_id` INT(10) );
（3）teacher表
DROP TABLE IF EXISTS teacher; CREATE TABLE `teacher` ( `id` INT ( 10 ) PRIMARY KEY,/*主键*/ `name` VARCHAR ( 10 ) );
（4）scores表
DROP TABLE IF EXISTS scores; CREATE TABLE `scores` ( `s_id` INT ( 10 ), `score` INT ( 10 ), `c_id`int(10), PRIMARY KEY(s_id,c_id) );
（5）给四张表插入数据
insert into student (id,name,age,gender)VALUES(1,'张小明',19,'男'),(2,'李小红',19,'男'),(3,'小刚',24,'男'),(4,'小龙',11,'男'),(5,'小丽',18,'男'),(6,'张小军',18,'女'),(7,'小航',16,'男'),(8,'小亮',23,'男'),(9,'小杰',22,'女'),(10,'李小虎',21,'男'); insert into course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null); insert into teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose'); insert into scores (s_id,score,c_id)VALUES(1,80,1); insert into scores (s_id,score,c_id)VALUES(1,56,2); insert into scores (s_id,score,c_id)VALUES(1,95,3); insert into scores (s_id,score,c_id)VALUES(1,30,4); insert into scores (s_id,score,c_id)VALUES(1,76,5); insert into scores (s_id,score,c_id)VALUES(2,35,1); insert into scores (s_id,score,c_id)VALUES(2,86,2); insert into scores (s_id,score,c_id)VALUES(2,45,3); insert into scores (s_id,score,c_id)VALUES(2,94,4); insert into scores (s_id,score,c_id)VALUES(2,79,5); insert into scores (s_id,score,c_id)VALUES(3,65,2); insert into scores (s_id,score,c_id)VALUES(3,85,3); insert into scores (s_id,score,c_id)VALUES(3,37,4); insert into scores (s_id,score,c_id)VALUES(3,79,5); insert into scores (s_id,score,c_id)VALUES(4,66,1); insert into scores (s_id,score,c_id)VALUES(4,39,2); insert into scores (s_id,score,c_id)VALUES(4,85,3); insert into scores (s_id,score,c_id)VALUES(5,66,2); insert into scores (s_id,score,c_id)VALUES(5,89,3); insert into scores (s_id,score,c_id)VALUES(5,74,4); insert into scores (s_id,score,c_id)VALUES(6,80,1); insert into scores (s_id,score,c_id)VALUES(6,56,2); insert into scores (s_id,score,c_id)VALUES(6,95,3); insert into scores (s_id,score,c_id)VALUES(6,30,4); insert into scores (s_id,score,c_id)VALUES(6,76,5); insert into scores (s_id,score,c_id)VALUES(7,35,1); insert into scores (s_id,score,c_id)VALUES(7,86,2); insert into scores (s_id,score,c_id)VALUES(7,45,3); insert into scores (s_id,score,c_id)VALUES(7,94,4); insert into scores (s_id,score,c_id)VALUES(7,79,5); insert into scores (s_id,score,c_id)VALUES(8,65,2); insert into scores (s_id,score,c_id)VALUES(8,85,3); insert into scores (s_id,score,c_id)VALUES(8,37,4); insert into scores (s_id,score,c_id)VALUES(8,79,5); insert into scores (s_id,score,c_id)VALUES(9,66,1); insert into scores (s_id,score,c_id)VALUES(9,39,2); insert into scores (s_id,score,c_id)VALUES(9,85,3); insert into scores (s_id,score,c_id)VALUES(9,79,5); insert into scores (s_id,score,c_id)VALUES(10,66,2); insert into scores (s_id,score,c_id)VALUES(10,89,3); insert into scores (s_id,score,c_id)VALUES(10,74,4); insert into scores (s_id,score,c_id)VALUES(10,79,5);
二、需求
-- 1.查询'01'号学生的姓名和各科成绩 **
SELECT s.id sid, s.`name` sname, c.`name` cname, sc.score FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id WHERE s.id = 1;
-- 2.查询各个学科的平均成绩和最高成绩**
SELECT c.id, c.`name`, AVG( sc.score ), max( sc.score ) FROM course c LEFT JOIN scores sc ON c.id = sc.c_id GROUP BY c.id, c.`name`;
-- 3.查询每个同学的最高成绩和科目名称****（子查询）
SELECT t.id 学生编号, t.`NAME` 学生姓名, c.id 课程编号, c.`NAME` 课程名称, r.score 最高成绩 FROM ( SELECT s.id, s.`NAME`,( SELECT MAX( score ) FROM scores r WHERE r.s_id = s.id ) score FROM student s ) t LEFT JOIN scores r ON r.s_id = t.id AND r.score = t.score LEFT JOIN course c ON r.c_id = c.id;
-- 4.查询所有姓张的同学的各科成绩**
SELECT s.id, s.`name`, c.`name` cname, sc.score FROM SELECT s.id, s.`name`, c.`name` cname, sc.score FROM student s LEFT JOIN scores sc ON sc.s_id = s.id LEFT JOIN course c ON c.id = sc.c_id WHERE s.`name` LIKE '张%';
-- 5.查询每个课程的最高分的学生信息*****（子查询）
SELECT * FROM student s WHERE id IN ( SELECT DISTINCT r.s_id FROM ( SELECT c.id, c.NAME, max( score ) score FROM student s LEFT JOIN scores r ON r.s_id = s.id LEFT JOIN course c ON c.id = r.c_id GROUP BY c.id, c.NAME ) t LEFT JOIN scores r ON r.c_id = t.id AND t.score = r.score )
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT s.id AS 学生编号, s.NAME AS 学生姓名, c.NAME 课程名称, sc.score 成绩 FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id WHERE ( s.`name` LIKE '李%' OR s.`name` LIKE '张%' );
-- 7.查询平均成绩及格的同学的信息。（子查询）
/*方法1*/ SELECT a.s_id AS 学生编号, AVG( a.score ) AS 平均成绩 FROM scores AS a GROUP BY a.s_id HAVING avg( a.score )>=60 /*方法2*/ SELECT * FROM student WHERE id IN ( SELECT sc.s_id FROM scores sc GROUP BY sc.s_id HAVING avg( sc.score ) >= 60 )
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT s.id AS 学生编号, s.NAME 学生姓名, sum( sc.score ) 学生总分数 FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id GROUP BY s.NAME ORDER BY score DESC;
--- 9.查询数学成绩的最高分、最低分、平均分。
SELECT c.id AS 科目编号, c.`name` AS 科目名称, max( sc.score ) AS 最高分, min( sc.score ) AS 最低分, AVG( sc.score ) AS 平均分 FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id WHERE c.id = 1;
-- 10.将各科目按照平均分排序。
SELECT c_id AS 科目编号, c.name AS 科目名称, AVG( sc.score ) AS 平均分 FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id GROUP BY c_id ORDER BY sc.score DESC, c_id
-- 11.查询老师的信息和他所带的科目的平均分
SELECT
t.id 老师编号,
t.`name` 老师姓名,
AVG(sc.score) 科目平均成绩,
c.`name` 科目名称
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
t.`name`,
t.id;-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT
t.id 老师编号,
t.`name` 老师名字,
c.`name` 课程名称,
max(sc.score) 课程最高分,
MIN(sc.score) 课程最低分
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
t.id,
t.`name`;
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
t.id,
t.sname,
r.c_id,
c.NAME,
t.score
FROM
(
SELECT
s.id,
s.NAME sname,
max( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
GROUP BY
s.id,
s.NAME
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;-- 14.查询所有学生的课程及分数
SELECT
s.id,
s.NAME,
c.id,
c.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON c.id = r.c_id;-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
/*方法一*/
SELECT
*
FROM
student s
WHERE
s.id IN (
SELECT
r.s_id
FROM
scores r
WHERE
r.c_id = 1
AND r.score > 60)--------------------------------------------------------
/*方法二---外连接*/
SELECT
s.*,
r.*
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
WHERE
r.c_id = 1
AND r.score > 60-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
/*方法一*/
SELECT
s.id 学生学号,
s.`name` 学生姓名,
AVG(sc.score) 平均成绩
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY
s.id
HAVING
AVG(sc.score) >=70----------------------------------------------------
/*方法二*/
SELECT
s.id 学生学号,
s.`name` 学生姓名,
t.score 平均成绩
FROM
student s
LEFT JOIN
( SELECT
r.s_id, AVG( r.score ) score
FROM
scores r
GROUP BY
r.s_id ) t
ON s.id = t.s_id
WHERE
t.score >=70-- 17.查询有不及格课程的学生信息
SELECT
*
FROM
student S
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
min(r.score) <60 );-- 18.查询每门课程有成绩的学生人数
/*方法一*/
SELECT
c.id,
c.NAME,
t.number
FROM
course c
LEFT JOIN ( SELECT r.c_id, count(*) number FROM scores r GROUP BY r.c_id ) t ON c.id = t.c_id;----------------------------------------------------
/*方法二*/
SELECT
c.id,
c.NAME,
count(*)
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME;-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
c.id 课程编号,
c.`name` 课程名称,
AVG(score) 平均成绩
FROM
course c
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
c.id,
c.`name`
ORDER BY
AVG(score) DESC,
c.id ASC;-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.id,
s.NAME sname,
avg( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
s.id,
s.NAME
HAVING
score > 65;-- 21. 查询有且仅有一个课程成绩在80分以上的学生信息
/*方法一*/
SELECT
s.age,
s.gender,
s.id,
s.`name`
FROM
student s
LEFT JOIN scores sc on sc.s_id =s.id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
sc.score >80
GROUP BY
s.id
HAVING COUNT(c.id)=1;-----------------------------------------------------
/*方法二*/
SELECT
*
FROM
student
WHERE
id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*)= 1 );-- 22 .查询出只有三门课程的学生的学号和姓名
/*方法一*/
SELECT
s.id 学生学号,
s.`name` 学生姓名
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY
s.id,
s.`name`
HAVING
COUNT(*)=3;-------------------------------------
/*方法二*/
SELECT
*
FROM
student s
WHERE
id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );-- 23.查询有不及格课程的课程信息
/*方法一*/
SELECT
c.id 课程编号,
c.NAME 课程名称
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
c.id,
c.NAME
HAVING
min( sc.score ) < 60;
/*方法二*/
SELECT
*
FROM
course c
WHERE
id IN (
SELECT
r.c_id
FROM
scores r
GROUP BY
r.c_id
HAVING
min( r.score )< 60
)-- 24 .查询至少选择4门课程的学生信息
/*方法一*/
SELECT
s.id 学生学号,
s.`name` 学生姓名
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY
s.id,
s.`name`
HAVING
COUNT(*)>=4;
/*方法二*/
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) >= 4
)-- 25. 查询没有选全所有课程的同学的信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) != 5
)-- 26.查询选全所有课程的同学的信息
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
number = ( SELECT count(*) FROM course );-- 27.查询各学生都选了多少门课
/*方法一*/
SELECT
s.id 学生学号,
s.`name` 学生姓名,
COUNT(c.id) 选课数量
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON c.id = sc.c_id
GROUP BY
s.id
HAVING
COUNT(c.id)
/*方法二*/
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT
s.id 学生学号,
s.`name` 学生姓名,
sc.score 学生成绩,
c.`name` 课程名称
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
sc.score < 60 AND c.name = 'Java'-- 29.查询学过"Tom"老师授课的同学的信息
SELECT
s.id 学生学号,
s.`name` 学生姓名
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON c.id = sc.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.`name` = 'Tom'-- 30.查询没学过"Tom"老师授课的学生信息
SELECT
*
FROM
student
WHERE
id NOT IN (
SELECT DISTINCT
s.id
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom'
)