admin 管理员组文章数量: 888394
Mysql多表联查——经典50题
目录
- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数。
- 2、查询每门功成绩最好的前两名 。
- 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
- 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩。(包括有成绩的和无成绩的)
- 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
- 6.查询"李"姓老师的数量。
- 7.查询学过"张三"老师授课的同学的信息。
- 8.查询没学过"张三"老师授课的同学的信息。
- 9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
- 11.查询没有学全所有课程的同学的信息 。
- 12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
- 13.查询和"01"号的同学学习的课程完全相同的其他同学的信息。
- 14.查询没学过"张三"老师讲授的任一门课程的学生姓名。
- 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
- 16.检索"01"课程分数小于60，按分数降序排列的学生信息。
- 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分。
- 19.查询出各科成绩总分，并按总分降序排序：以如下形式显示：课程ID，课程name，总分
- 20.查询学生的总成绩及学生信息。
- 21.查询不同老师所教不同课程平均分从高到低显示。
- 22.查询每门课程被选修的学生数。
- 23.查询出只有两门课程的全部学生的学号和姓名。
- 24.查询男生、女生人数
- 25.查询名字中含有"风"字的学生信息。
- 26.查询1990年出生的学生名单。
- 27.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
- 28.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。
- 29.查询课程名称为"数学"，且分数低于60的学生姓名和分数。
- 30.查询任何一门课程成绩在70分以上的姓名、课程名称和分数。
- 31.查询不及格的课程。
- 32.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名。
- 33.求每门课程的学生人数。
- 34.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
- 35.统计每门课程的学生选修人数（超过5人的课程才统计）。
- 36.检索至少选修两门课程的学生学号。
- 37.查询选修了全部课程的学生信息。
- 38.查询各学生的年龄。
- 39.查询本周过生日的学生。
- 40.查询下周过生日的学生。
- 41.查询本月过生日的学生。
- 42.查询下月过生日的学生。
- 43.笛卡尔积乘积
- === 建表数据 ===
- 总结
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数。
// 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数SELECT stu.s_id, stu.s_name, s1.s_score FROM student stu, score s1, score s2WHERE stu.s_id = s1.s_idAND stu.s_id = s2.s_idAND s1.c_id = '01'AND s2.c_id = '02'AND s1.s_score > s2.s_score
2、查询每门功成绩最好的前两名 。
// 方法一
SELECT c1.c_name, ( SELECT s1.s_id FROM score s1 WHERE s1.c_id = c1.c_idORDER BY s1.s_score DESC LIMIT 0,1) 第一名,( SELECT s1.s_id FROM score s1 WHERE s1.c_id = c1.c_idORDER BY s1.s_score DESC LIMIT 1,1) 第二名FROM course c1
// 方法二 SELECT s1.c_id, s1.s_id, s1.s_scoreFROM score s1 LEFT JOIN score s2 ON s1.c_id = s2.c_idAND s1.s_score < s2.s_scoreGROUP BY s1.c_id, s1.s_idHAVING COUNT(s1.c_id) < 2ORDER BY s1.c_id ASC, s1.s_score DESC
// 方法三
SELECT sc.c_id, sc.s_score FROM score sc
WHERE (SELECT COUNT(*) FROM score WHERE sc.c_id = score.c_id AND sc.s_score < score.s_score
) < 2
ORDER BY sc.c_id ASC,sc.s_score DESC;
3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
SELECT stu.s_id, stu.s_name, SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) AS '平均分' FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) >= 60 -- AVG计算平均分，缺考会被忽略--
SELECT stu.s_id, stu.s_name, AVG(sc.s_score) AS '平均分' FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) >= 60 -- 查询某一个学生平均分。
SELECT s_id, c_id, SUM(s_score) AS '总分', SUM(s_score) /5 AS '平均分' FROM score WHERE s_id = 3
4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩。(包括有成绩的和无成绩的)
SELECT stu.s_id, stu.s_name, SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) AS '平均分' FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) < 60
UNION
SELECT stu.s_id, stu.s_name, avg(sc.s_score)
FROM student stu
LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING avg(sc.s_score) IS NULL;
5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT stu.s_id AS'学号', stu.s_name AS'姓名', COUNT(sc.c_id)AS'选课总数', SUM(sc.s_score) AS '总成绩'
FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id
6.查询"李"姓老师的数量。
SELECT COUNT(t_id)AS '总数' FROM teacher WHERE t_name LIKE '李%'
7.查询学过"张三"老师授课的同学的信息。
SELECT s_id, s_name FROM student
WHERE s_id IN (SELECT sc.s_id FROM score sc, course c, teacher tWHERE sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张三'
)
8.查询没学过"张三"老师授课的同学的信息。
SELECT s_id, s_name FROM student
WHERE s_id NOT IN (SELECT sc.s_id FROM score sc, course c, teacher tWHERE sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张三'
)
9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
/* -- EXISTS 检语法：
select 字段名|表达式 from 表名1 a
where exists (select * from 表名2 b where a. 字段名a=b.字段名b) ;
关键字EXISTS：构造子查询，子查询是否返回结果集，返回则才进行外层查询。如果子查询的结果集不为空，则EXISTS返回的结果为TRUE，此时外层查询语句将进行查询；
如果子查询的结果集为空，则EXISTS返回的结果为FLASE，此时外层查询语句将不进行查询；
*/
SELECT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '01'
AND EXISTS (SELECT * FROM score sc WHERE sc.s_id = stu.s_id AND sc.c_id = '02')
#10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
SELECT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '01'
AND stu.s_id NOT IN (SELECT s_id FROM score WHERE c_id = '02'
)
11.查询没有学全所有课程的同学的信息 。
SELECT s_id, s_name FROM student
WHERE s_id NOT IN (SELECT s_id FROM score GROUP BY s_idHAVING COUNT(c_id) = (SELECT COUNT(c_id) FROM course)
)
12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
/**
* 关键字 DISTINCT 去重
*/
SELECT DISTINCT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id IN (SELECT c_id FROM score WHERE s_id = '1'
)
13.查询和"01"号的同学学习的课程完全相同的其他同学的信息。
SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score GROUP BY s_idHAVING group_concat(c_id) = (SELECT group_concat(c_id) FROM scoreWHERE s_id = 1) AND s_id != 1
);/*group_concat()函数：合并同列变成一行，默认以逗号分隔显示
*/
select group_concat(stu.s_name)AS '姓名' from student stu -- 上面原查询语句 --
select * from student where s_id in (select s_id from score group by s_idhaving group_concat(c_id ORDER BY c_id) = (select group_concat(c_id ORDER BY c_id) as str2 from scorewhere s_id = 1) and s_id != 1);select * from student where s_id in (select s_id from score where s_id not in (select s_id from score where c_id not in (select c_id from score where s_id='01'))
group by s_id
having count(*)=(select count(*) from score where s_id='01') and s_id != '01');
14.查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT s_id,s_name FROM student
WHERE s_id NOT IN (SELECT sc.s_id FROM score sc, course c, teacher tWHERE sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张三'
)
15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
// 方法一
SELECT stu.s_id, stu.s_name, AVG(sc.s_score)
FROM score sc JOIN student stu ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING count(sc.s_score<60 or null) >= 2
// 方法二
SELECT stu.s_id, stu.s_name, AVG(sc.s_score)
FROM score sc, student stu
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING count(sc.s_score<60 or null) > 1
16.检索"01"课程分数小于60，按分数降序排列的学生信息。
SELECT stu.s_id, stu.s_name, sc.s_score FROM student stu
LEFT JOIN score sc ON stu.s_id = sc.s_id
WHERE sc.c_id = '01'
AND sc.s_score < 60
ORDER BY sc.s_score DESC
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
/*
* 函数：group_concat
* 作用：函数返回一个字符串结果，该结果由分组中的值连接组合而成。（一列多行数据，在一行显示。）
* group_concat( [DISTINCT] 连接字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
*/
SELECT stu.s_id, stu.s_name,GROUP_CONCAT(c.c_name) '课程',GROUP_CONCAT(sc.s_score) '分数',AVG(sc.s_score) '平均分'
FROM student stu
LEFT JOIN score sc ON stu.s_id = sc.s_id
JOIN course c ON sc.c_id = c.c_id
GROUP BY sc.s_id
ORDER BY AVG(sc.s_score) DESC
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分。
SELECT c.c_id AS'课程id',c.c_name AS'课程名称',MAX(sc.s_score) AS'最高分',MIN(sc.s_score) AS'最低分',AVG(sc.s_score) AS'平均分'
FROM course c, score sc
WHERE c.c_id = sc.c_id
GROUP BY sc.c_id-- 验证 --
SELECT s_score FROM score WHERE c_id = '04' ORDER BY s_score19.查询出各科成绩总分，并按总分降序排序：以如下形式显示：课程ID，课程name，总分
SELECT c.c_id AS'课程ID', c.c_name AS'课程名称',SUM(sc.s_score) AS'总分'
FROM score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id
ORDER BY SUM(sc.s_score) DESC20.查询学生的总成绩及学生信息。
SELECT stu.s_id, stu.s_name,SUM(sc.s_score)AS'总成绩' FROM student stu, score sc
WHERE sc.s_id = stu.s_id
GROUP BY stu.s_id
21.查询不同老师所教不同课程平均分从高到低显示。
SELECT t.t_id, t.t_name AS'教师名称',c.c_name AS'课程名称',AVG(sc.s_score) AS'平均分'
FROM teacher t, course c, score sc
WHERE t.t_id = c.t_id
AND c.c_id = sc.c_id
GROUP BY t.t_id
ORDER BY AVG(sc.s_score) DESC
22.查询每门课程被选修的学生数。
SELECT c.c_id, c.c_name AS'课程名称', COUNT(c.c_id)AS'选修人数'
FROM course c, score sc
WHERE c.c_id = sc.c_id
GROUP BY c.c_id
23.查询出只有两门课程的全部学生的学号和姓名。
SELECT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING COUNT(sc.c_id) = 2
24.查询男生、女生人数
SELECT s_sex AS'性别',COUNT(1) AS '人数'
FROM student
GROUP BY s_sex
25.查询名字中含有"风"字的学生信息。
SELECT s_id, s_name FROM student
WHERE s_name LIKE '%风%'
26.查询1990年出生的学生名单。
SELECT * FROM student WHERE s_birth LIKE '1990%'
-- 方法二 --
/*
* 函数 BETWEEN ... AND 取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
*/
SELECT * FROM student WHERE s_birth BETWEEN '1990-1-1' AND '1990-12-31';27.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT c.c_name, avg(s_score) FROM score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY c.c_id ORDER BY AVG(sc.s_score) DESC,sc.c_id
28.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。
SELECT stu.s_id, stu.s_name, AVG(sc.s_score) FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id HAVING AVG(sc.s_score) > 85
29.查询课程名称为"数学"，且分数低于60的学生姓名和分数。
SELECT stu.s_id, stu.s_name, sc.s_score FROM student stu, score sc, course c
WHERE stu.s_id = sc.s_id
AND c.c_id = sc.c_id
AND c.c_name = '数学'
AND sc.s_score < 60
30.查询任何一门课程成绩在70分以上的姓名、课程名称和分数。
SELECT stu.s_name, c.c_name, sc.s_score FROM student stu, course c, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id
GROUP BY sc.s_score HAVING sc.s_score >70
31.查询不及格的课程。
SELECT stu.s_name, c.c_name, sc.s_score FROM student stu, course c, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id
GROUP BY sc.s_score HAVING sc.s_score < 60
32.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名。
SELECT stu.s_id,stu.s_name, c.c_name, sc.s_score FROM student stu, course c, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id
AND c.c_id = '01'
AND sc.s_score > 80
33.求每门课程的学生人数。
SELECT c.c_name, COUNT(c.c_id) FROM score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY c.c_id
34.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
SELECT stu.s_id, stu.s_name, MAX(sc.s_score)
FROM student stu, course c, score sc, teacher t
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id
AND c.t_id = t.t_id
AND t.t_name = '张无忌'
GROUP BY c.c_id -- 方法二 --
SELECT s.*,MAX(sc.s_score)
FROM student s,score sc
WHERE s.s_id=sc.s_id
AND sc.c_id in(
SELECT c_id
FROM teacher t,course c
WHERE t.t_id=c.t_id
AND t.t_name='张三');
35.统计每门课程的学生选修人数（超过5人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 --
SELECT c_id, COUNT(s_id) AS num FROM score
GROUP BY c_id HAVING num > 5
ORDER BY num DESC, c_id ASC
36.检索至少选修两门课程的学生学号。
SELECT stu.s_id, stu.s_name
FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY s_id HAVING COUNT(c_id) > 2
37.查询选修了全部课程的学生信息。
SELECT stu.s_id, stu.s_name FROM score sc, student stu
WHERE sc.s_id = stu.s_id
GROUP BY s_id HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM course)
38.查询各学生的年龄。
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1 --
/**
方法一：
TIMESTAMPDIFF() 函数：计算两个日期的时间差
CURDATE() 函数：返回当前的日期语法结构 TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)参数说明:
unit: 日期比较返回的时间差单位，常用可选值如下:
SECOND：秒 MINUTE：分钟 HOUR：小时
DAY：天 WEEK：星期 MONTH：月
QUARTER：季度
YEAR：年
datetime_expr1: 要比较的日期1 datetime_expr2: 要比较的日期2TIMESTAMPDIFF函数返回datetime_expr2 - datetime_expr1的结果，其中datetime_expr1和datetime_expr2可以是DATE或DATETIME类型值
原文链接：
**/
SELECT stu.s_id, stu.s_name, TIMESTAMPDIFF(YEAR,stu.s_birth,CURDATE()) FROM student stu/**
方法二 :
-- round函数用于数据的四舍五入
-- datediff() 函数返回两个日期之间的天数。
-- curdate() 返回当前的日期
**/
SELECT s_id, s_name, ROUND(DATEDIFF(CURDATE(), s_birth)/365.2422) FROM student/**
方法三：
CURRENT_DATE：返回此时区的当前本地日期
**//
select s_id, s_name,
year(current_date())-year(s_birth) as age
from student;
39.查询本周过生日的学生。
/**
* WEEKOFYEAR()函数：用于查找给定日期的星期数
* YEARWEEK 是获取年份和周数的一个函数
**/
SELECT * FROM student WHERE WEEKOFYEAR(s_birth) = WEEKOFYEAR(CURDATE())SELECT WEEKOFYEAR(CURDATE())
40.查询下周过生日的学生。
SELECT * FROM student
WHERE WEEKOFYEAR(s_birth) = WEEKOFYEAR(CURDATE())+1;
41.查询本月过生日的学生。
SELECT * FROM student t1
WHERE MONTH(t1.s_birth) = MONTH(CURDATE());
42.查询下月过生日的学生。
SELECT * FROM student t1
WHERE MONTH(t1.s_birth) = MONTH(CURDATE())+1;
43.笛卡尔积乘积
-- 方式一 --
SELECT * FROM student s,teacher t-- 方式二 --
SELECT * from student CROSS JOIN teacher
=== 建表数据 ===
-- 学生表 --
create table student (s_id INT(10) NOT NULL,s_name varchar(20) not null,s_birth varchar(20),s_sex varchar(2),primary key (s_id))
insert into `student` values ('1', '张三','1990-10-10','男');
insert into `student` values ('2', '李四','1996-10-20','女');
insert into `student` values ('3', '王五','2008-10-21','男');
insert into `student` values ('4', '赵六','2010-08-10','女');
insert into `student` values ('5', '小七','2000-10-22','女');
insert into `student` values ('6', '风子','1994-11-10','男');
insert into `student` values ('7', '李蛋','1994-12-10','男');
insert into `student` values ('8', '重风','1994-09-10','男');-- 课程表 --
create table course(c_id varchar(10) not null comment'课程编号',c_name varchar(20) not null comment'课程名称',t_id int(20) not null comment'教师编号'
)
insert into `course` values ('01','语文','1');
insert into `course` values ('02','数学','2');
insert into `course` values ('03','英语','3');
insert into `course` values ('04','体育','4');
insert into `course` values ('05','化学','5');
insert into `course` values ('06','物理','6');
insert into `course` values ('07','生物','7');-- 成绩表 --
create table score(s_id int(20) not null comment'学生编号',c_id varchar(10) not null comment'课程编号',s_score int(3) not null comment'分数'
)insert into `score` values ('1','01','56');
insert into `score` values ('1','02','58');
insert into `score` values ('1', '03','67');insert into `score` values ('2', '01','54');
insert into `score` values ('2', '02','78');
insert into `score` values ('2', '03','54');
insert into `score` values ('2', '04','66');
insert into `score` values ('2', '05','79');
insert into `score` values ('2', '06','82');
insert into `score` values ('2', '07','87');insert into `score` values ('3', '01','84');
insert into `score` values ('3', '02','89');
insert into `score` values ('3', '03','68');
insert into `score` values ('3', '04','71');
insert into `score` values ('3', '05','37');
insert into `score` values ('3', '06','66');
insert into `score` values ('3', '07','77');insert into `score` values ('4', '01','80');
insert into `score` values ('4', '03','74');
insert into `score` values ('4', '04','57');
insert into `score` values ('4', '05','65');
insert into `score` values ('5', '01','100');
insert into `score` values ('5', '04','99');
insert into `score` values ('7', '01','96');
insert into `score` values ('7', '02','50');
insert into `score` values ('7', '03','83');
insert into `score` values ('8', '05','90');
insert into `score` values ('8', '06','88');-- 教师表 --
create table `teacher`(`t_id` int(20) not null comment'教师编号',`t_name` varchar(20) not null comment'教师姓名',primary key(`t_id`)
)
insert into `teacher` values ('1', '张无忌');
insert into `teacher` values ('2', '张三丰');
insert into `teacher` values ('3', '老过');
insert into `teacher` values ('4', '郭靖');
insert into `teacher` values ('5', '唐僧');
insert into `teacher` values ('6', '张六');
总结
========================努力干吧！菜鸟。
本文标签： Mysql多表联查经典50题
版权声明：本文标题：Mysql多表联查——经典50题 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1688272930h200023.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论