MySQL 练习50题

MySQL 练习50题

概述

学习的精髓 = 理论+实操+总结,所有题都亲手敲一遍才会牢记。

下面是我自己写的答案,有些不会写只能空着,以后再补。(建表的SQL语句在文章最后)

1、表结构

2、案例答案

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 
SELECT 
	stu.*, 
	s1.s_score as 01_score,
	s2.s_score as 02_score
FROM 
	Student as stu
	left JOIN Score s1 on stu.s_id = s1.s_id 
	AND s1.c_id = '01'
	left JOIN Score s2 on stu.s_id = s2.s_id 
	AND s2.c_id = '02' 
	or s2.c_id = NULL
where 
	s1.s_score > s2.s_score
	
-- 或者
SELECT 
	stu.*, 
	s1.s_score as 01_score,
	s2.s_score as 02_score
FROM 
	Score as s1, 
	Score as s2,
	Student as stu 
WHERE 
	stu.s_id = s1.s_id
	AND stu.s_id = s2.s_id
	AND s1.c_id = '01'
	AND s2.c_id = '02'
	AND s1.s_score > s2.s_score
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT 
	stu.*, 
	s1.s_score as 01_score,
	s2.s_score as 02_score
FROM 
	Student as stu
	left JOIN Score s1 on stu.s_id = s1.s_id 
	AND s1.c_id = '01'
	left JOIN Score s2 on stu.s_id = s2.s_id 
	AND s2.c_id = '02' 
	or s2.c_id = NULL
where 
	s1.s_score < s2.s_score
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT 
	s2.s_id, 
	s2.s_name, 
	ROUND(AVG(s1.s_score),2) as avg_score 
FROM 
	Score AS s1 
	left JOIN Student AS s2 on s1.s_id = s2.s_id
GROUP BY 
	s2.s_id  
HAVING AVG(s1.s_score) >=60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
SELECT 
	s1.s_id, 
	s1.s_name, 
	ROUND(AVG(s2.s_score),2) avg_score 
FROM 
	Student AS s1 
	LEFT JOIN Score s2 on s1.s_id = s2.s_id
GROUP BY 
	s1.s_id 
HAVING AVG(s2.s_score) < 60
union
	select 
		a.s_id,
		a.s_name,0 as avg_score 
	from student a 
	where a.s_id not in (
		select distinct s_id from score
    );
-- union用于合并两个或多个结果集,每个结果集必须拥有相同数量的列、列必须拥有相似的数据类型、列的顺序必须相同。
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT 
	s1.s_id, 
	s1.s_name, 
	count(s2.c_id) as '课程数', 
	SUM(s2.s_score) as '总分'
FROM 
	Student s1 
	LEFT JOIN Score s2 on s1.s_id = s2.s_id 
GROUP BY 
	s1.s_id
-- 6、查询"李"姓老师的数量 
SELECT count(t.t_id) FROM Teacher t WHERE t.t_name LIKE '李%'
-- 7、查询学过"张三"老师授课的同学的信息 
SELECT	s1.*
FROM 
	Student s1, 
	Score s2
WHERE 
	s1.s_id = s2.s_id 
	AND s2.c_id in (
		SELECT c.c_id
		FROM 
    		Teacher t, 
    		Course c 
		where t.t_id = c.t_id 
    	AND t.t_name = '张三'
	)
-- 8、查询没学过"张三"老师授课的同学的信息 
SELECT	* FROM Student 
WHERE s_id not in (
		SELECT s1.s_id 
    	from 
    		Student s1 
    		JOIN Score s2 on s1.s_id = s2.s_id 
    	WHERE s2.c_id in (
			select a.c_id 
            from course a 
            JOIN teacher b on a.t_id = b.t_id
            where t_name ='张三'
        )
)
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT	stu.*
from 
	Student stu, 
	Score s1, 
	Score s2
WHERE 
	s1.c_id = 01 
	AND s2.c_id = 02
	AND s1.s_id = stu.s_id
	AND s2.s_id = stu.s_id
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT stu.*
FROM Student stu 
WHERE 
	stu.s_id in (
    	SELECT s_id 
    	FROM Score 
    	where c_id = 01
	)
	AND stu.s_id not in (
    	SELECT s_id 
    	FROM Score 
    	where c_id = 02
	)
-- 11、查询没有学全所有课程的同学的信息
SELECT stu.*
FROM Student stu 
WHERE stu.s_id not in (SELECT s_id 
		FROM Score	
		GROUP BY s_id 
        HAVING count(*) = (
            SELECT count(*) FROM Course
        )
)
-- 或者		
SELECT stu.* 
FROM 
	Student stu
	left join Score sc on stu.s_id = sc.s_id
GROUP BY 
	stu.s_id 
HAVING count(sc.s_id) < (
    SELECT count(*) FROM Course
)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT stu.* 
FROM 
	Student stu, 
	Score sc 
where 
	stu.s_id = sc.s_id 
	AND sc.c_id in (
	SELECT sc.c_id 
    FROM Student stu 
	left join Score sc on stu.s_id = sc.s_id 
	where stu.s_id = '01' 
) 
GROUP BY stu.s_id HAVING stu.s_id not in ('01')
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT stu.* 
FROM Student stu
WHERE stu.s_id in (	-- 找到与‘01’同学课程数完全一样的同学
	SELECT s_id FROM Score 
	GROUP BY s_id 
    HAVING count(s_id) = (
		-- 找到'01'同学学习的课程数
		SELECT COUNT(c_id) FROM Score WHERE s_id = '01'
	)
)
AND stu.s_id not in (	-- 排除学了 '01'同学没学过的课程 的同学
	SELECT s_id 
    FROM Score 
	WHERE c_id in (
		SELECT c_id FROM Score 
        WHERE c_id  not in (
			-- 找出‘01’同学学习的课程
			SELECT c_id FROM Score WHERE s_id = '01'
		)
	)
    GROUP BY s_id
)
AND stu.s_id not in ('01')	-- 排除 '01' 同学自己
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name 
FROM Student 
where s_id not in (	-- 排除学过‘张三’老师的课的学生
	SELECT sc.s_id 
    FROM Score sc  -- 找出学过‘张三’老是的课学生
	where sc.c_id in(
		select co.c_id 
        FROM Course co  -- 找出‘张三’老师讲授的所有课程
		left join Teacher t on t.t_id = co.c_id 
		where t.t_name = '张三'
	)
)
-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT 
	stu.s_id, 
	stu.s_name,
	sum(sc.s_score)/(SELECT count(c_id) FROM Course)
from 
	Student stu 
	left join Score sc on stu.s_id = sc.s_id
where stu.s_id in(
	SELECT s_id 
    FROM Score 
    where s_score < 60 
    GROUP BY 
    	s_id 
    HAVING count(*) >= 2
)
GROUP BY sc.s_id
-- 16、检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT stu.*
FROM 
	Student stu 
	left join Score sc on stu.s_id = sc.s_id	
	and sc.c_id = 01
where stu.s_id in (
	SELECT s_id 
    FROM Score
	WHERE s_score < 60
)
ORDER BY 
	sc.s_score desc
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
	stu.s_id '学号',
	stu.s_name '姓名',
	sum(
			case sc.c_id when '01' then sc.s_score  END
	) '语文',
	sum(
			case sc.c_id when '02' then sc.s_score  END
	) '数学',
	sum(
			case sc.c_id when '03' then sc.s_score  END
	) '英语',
	round(avg(sc.s_score),2) '平均分'
FROM
	Student stu
left join Score sc on stu.s_id = sc.s_id
GROUP BY
	stu.s_id
ORDER BY
	6 DESC	-- 根据第6列进行排序
-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SELECT
	co.c_id,
	co.c_name,
	max(sc.s_score) '最高分',
	min(sc.s_score) '最低分',
	sum(sc.s_score)/count(stu.s_id) '平均分',
	round(100*count(
		case  when sc.s_score >= 60 then sc.s_id END
	)/count(stu.s_id),2) '及格率(%)',
	ROUND(100*count(
		case  when sc.s_score >= 70 AND sc.s_score < 80 then sc.s_id END
	)/count(stu.s_id),2) '中等率(%)',
	ROUND(100*count(
		case  when sc.s_score >= 80 AND sc.s_score < 90 then sc.s_id END
	)/count(stu.s_id),2) '优良率(%)',
	ROUND(100*count(
		case  when sc.s_score >= 90 then sc.s_id END
	)/count(stu.s_id),2) '优秀率(%)'
FROM
	Course co
left join Score sc on sc.c_id = co.c_id
left join Student stu on stu.s_id = sc.s_id
GROUP BY
	sc.c_id
-- 20、查询学生的总成绩并进行排名
SELECT
	stu.*,
	SUM(sc.s_score) as '总分', 
	rank() OVER(ORDER BY SUM(sc.s_score) desc) as '排名'
FROM
	Student stu
left join Score sc on stu.s_id = sc.s_id
GROUP BY
	sc.s_id
ORDER BY SUM(sc.s_score) desc
-- 21、查询不同老师所教不同课程平均分从高到低显示 
SELECT
	co.c_name,
	t.t_name,
	ROUND(sum(sc.s_score)/count(co.c_id),2) '平均分'
FROM
	Course co
	left join Score sc on sc.c_id = co.c_id
	left join Teacher t on t.t_id = co.t_id
GROUP BY
	sc.c_id
ORDER BY 3 desc
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT
	co.c_id,
	co.c_name,
	count(
		case when sc.s_score >= 85 then sc.s_id END
	) as '[100-85]',
	count(
		case when sc.s_score >= 70 AND sc.s_score < 85 then sc.s_id END
	) as '[85-70]',
	count(
		case when sc.s_score >= 60 AND sc.s_score < 70 then sc.s_id END
	) as '[70-60]',
	count(
		case when sc.s_score < 60 then sc.s_id END
	) as '[60-0]'
FROM
	Course co
left join Score sc on co.c_id = sc.c_id
left join Student stu on stu.s_id = sc.s_id
GROUP BY
	sc.c_id
-- 24、查询学生平均成绩及其名次 
SELECT
	avg(s_score) as '平均成绩',
	rank() OVER(ORDER BY AVG(s_score) DESC) as '名次'
FROM
	Score
GROUP BY
	s_id
-- 25、查询各科成绩前三名的记录
SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a
	LEFT JOIN score b ON a.c_id = b.c_id 
AND a.s_score < b.s_score 
GROUP BY
	a.s_id,
	a.c_id,
	a.s_score 
HAVING
	COUNT( b.s_id ) < 3 
ORDER BY
	a.c_id,
	a.s_score DESC
-- 26、查询每门课程被选修的学生数
SELECT
	co.c_id '课程ID',
	co.c_name '课程名',
	count(sc.s_id) '选修人数'
FROM
	Course co 
	LEFT JOIN Score sc on sc.c_id = co.c_id
	LEFT JOIN Student stu on stu.s_id = sc.s_id
GROUP BY
	sc.c_id
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT
	stu.s_id '学号',
	stu.s_name '姓名'
FROM
	Student stu
	LEFT JOIN Score sc on sc.s_id = stu.s_id
GROUP BY
	sc.s_id
HAVING
	count(sc.c_id) = 2

-- 28、查询男生、女生人数
SELECT
	s_sex as '性别',
	count(s_sex) as '人数'
FROM
	Student
GROUP BY
	s_sex
-- 29、查询名字中含有"风"字的学生信息
SELECT * FROM Student WHERE s_name like '%风%';
-- 30、查询同名同性学生名单,并统计同名人数 
SELECT
	a.s_name,
	a.s_sex,
	count(*)
FROM
	Student a
	JOIN Student b on a.s_id <> b.s_id 
	and a.s_name = b.s_name
	and a.s_sex = b.s_sex
GROUP BY
	a.s_name,
	a.s_sex
-- 31、查询1990年出生的学生名单
SELECT * FROM Student where s_birth like '1990%'
-- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 
SELECT
	c_id,
	avg(s_score)
FROM
	Score
GROUP BY
	c_id
ORDER BY avg(s_score) desc, c_id asc

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
	stu.s_id as '学号',
	stu.s_name as '姓名',
	avg(s_score) as '平均成绩'
FROM
	Score sc
	LEFT JOIN Student stu on stu.s_id = sc.s_id
GROUP BY
	sc.s_id
HAVING avg(s_score) >= 85

-- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数 
SELECT
	stu.s_id,
	stu.s_name,
	sc.s_score 
FROM
	Student stu
	LEFT JOIN Score sc ON sc.s_id = stu.s_id
	LEFT JOIN Course co ON sc.c_id = co.c_id 
WHERE
	co.c_name = '数学' 
	AND s_score < 60
-- 35、查询所有学生的课程及分数情况;
SELECT
	stu.s_id,
	stu.s_name,
	stu.s_sex,
	sc.c_id,
	sc.s_score
FROM	
	Student stu
	LEFT JOIN Score sc on sc.s_id = stu.s_id
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT
 stu.s_name,
 co.c_name,
 sc.s_score
FROM 
	Student stu
	LEFT JOIN Score sc on stu.s_id = sc.s_id
	LEFT JOIN Course co on sc.c_id = co.c_id
where 
	sc.s_score > 70
ORDER BY sc.s_id
-- 37、查询不及格的课程
SELECT
 stu.s_id,
 stu.s_name,
 co.c_name,
 sc.s_score
FROM 
	Student stu
	LEFT JOIN Score sc on stu.s_id = sc.s_id
	LEFT JOIN Course co on sc.c_id = co.c_id
where 
	sc.s_score < 60
ORDER BY sc.s_id
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT
	stu.s_id,
	stu.s_name,
	sc.s_score
FROM
	Student stu
	LEFT JOIN Score sc on stu.s_id = sc.s_id
	LEFT JOIN Course co on sc.c_id = co.c_id
where
	co.c_id = '01'
	and sc.s_score >= 80
ORDER BY sc.s_id
-- 39、求每门课程的学生人数
SELECT 
	c_id,
	count(*) 
FROM 
	Score 
GROUP BY c_id
-- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩 
SELECT
	stu.s_name,
	sc.s_score
FROM
	Student stu
	LEFT JOIN Score sc on stu.s_id = sc.s_id
	LEFT JOIN Course co on sc.c_id = co.c_id
	LEFT JOIN Teacher t on t.t_id = co.t_id
WHERE
	t.t_name = '张三'
ORDER BY
	s_score DESC
LIMIT 0,1
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT
	a.s_id,
	a.c_id,
	a.s_score
FROM
	Score a
	JOIN Score b on a.s_id = b.s_id
	and a.s_score = b.s_score
	and a.c_id <> b.c_id
ORDER BY a.s_id
-- 42、查询每门课成绩最好的前两名 
	-- 这写法真牛逼
select 
	a.s_id,
	a.c_id,
	a.s_score 
from score a
where (
	select COUNT(1) 
	from score b 
	where 
		b.c_id=a.c_id 
		and b.s_score>=a.s_score
)<=2 
ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,
--     若人数相同,按课程号升序排列  
SELECT
	c_id,
	count(s_id)
FROM
	Score
GROUP BY
	c_id
ORDER BY
	count(s_id) desc,
	c_id asc
-- 44、检索至少选修两门课程的学生学号 
SELECT
	sc.s_id '学号',
	count(sc.s_id) '课程数'
FROM
	Score sc
GROUP BY
	sc.s_id
HAVING count(sc.s_id) >= 2
-- 45、查询选修了全部课程的学生信息
SELECT
	stu.*
FROM
	Student stu
	LEFT JOIN Score sc on stu.s_id = sc.s_id
GROUP BY
	sc.s_id
HAVING
	count(sc.s_id) = (
		SELECT count(*) FROM Course
	)
-- 46、查询各学生的年龄
SELECT
	s_name,
	2021 - year(s_birth) '年龄'
FROM
	Student
-- 47、查询本周过生日的学生
  -- 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
  -- 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写
-- 48、查询下周过生日的学生
-- 49、查询本月过生日的学生
SELECT * FROM Student where  MONTH(s_birth) = MONTH(NOW())
-- 50、查询下月过生日的学生
 -- 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模
 select 
 	* 
 from 
 	student 
 where 
 	MONTH(NOW())+1 =MONTH(s_birth)
 	OR MONTH(NOW())-11 =MONTH(s_birth)  -- 本月为12月情况

3、SQL表语句

/*
 Navicat Premium Data Transfer

 Date: 23/06/2021 17:07:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course`  (
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('01', '语文', '02');
INSERT INTO `Course` VALUES ('02', '数学', '01');
INSERT INTO `Course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for Score
-- ----------------------------
DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Score
-- ----------------------------
INSERT INTO `Score` VALUES ('01', '01', 80);
INSERT INTO `Score` VALUES ('01', '02', 90);
INSERT INTO `Score` VALUES ('01', '03', 99);
INSERT INTO `Score` VALUES ('02', '01', 70);
INSERT INTO `Score` VALUES ('02', '02', 60);
INSERT INTO `Score` VALUES ('02', '03', 80);
INSERT INTO `Score` VALUES ('03', '01', 80);
INSERT INTO `Score` VALUES ('03', '02', 80);
INSERT INTO `Score` VALUES ('03', '03', 80);
INSERT INTO `Score` VALUES ('04', '01', 50);
INSERT INTO `Score` VALUES ('04', '02', 30);
INSERT INTO `Score` VALUES ('04', '03', 20);
INSERT INTO `Score` VALUES ('05', '01', 76);
INSERT INTO `Score` VALUES ('05', '02', 87);
INSERT INTO `Score` VALUES ('06', '01', 34);
INSERT INTO `Score` VALUES ('06', '03', 34);
INSERT INTO `Score` VALUES ('07', '01', 89);
INSERT INTO `Score` VALUES ('07', '03', 98);

-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Student
-- ----------------------------
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-07-01', '女');
INSERT INTO `Student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `Student` VALUES ('07', '郑竹', '1989-06-04', '女');
INSERT INTO `Student` VALUES ('08', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

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

SET FOREIGN_KEY_CHECKS = 1;

参考资料:https://blog.csdn.net/fashion2014/article/details/78826299/

原文地址:https://www.cnblogs.com/luler/p/14923658.html