2022-12-13 22:02 已编辑门头沟学院 Java 发布于广西

关注

MySQL入门到精通经典50题，看这一篇就够了

pdf获取方式，公众号：每天都要努力coding
回复：mysql经典50题

MySQL安装教程详解

数据预览

sql建表语句

teacher表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : learn

 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001

 Date: 11/12/2022 12:24:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `TId` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_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;

student表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : learn

 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001

 Date: 11/12/2022 12:24:27
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SId` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `Sage` datetime NULL DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙⻛', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '⼥');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '⼥');
INSERT INTO `student` VALUES ('07', '郑⽵', '1989-01-01 00:00:00', '⼥');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '⼥');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '⼥');
INSERT INTO `student` VALUES ('11', '李四', '2012-06-06 00:00:00', '⼥');
INSERT INTO `student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '⼥');
INSERT INTO `student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '⼥');

SET FOREIGN_KEY_CHECKS = 1;

course表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : learn

 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001

 Date: 11/12/2022 12:24:11
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `CId` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `TId` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_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');

SET FOREIGN_KEY_CHECKS = 1;

sc表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : learn

 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001

 Date: 11/12/2022 12:24:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `SId` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `CId` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 60.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);

SET FOREIGN_KEY_CHECKS = 1;

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

SELECT
	Student.*,
	01_score.CId,
	1_score,
	02_score.CId,
	2_score 
FROM
	Student
	JOIN ( SELECT SId, CId, score AS 1_score FROM SC WHERE CId = '01' ) AS 01_score ON Student.SId = 01_score.SId
	JOIN ( SELECT SId, CId, score AS 2_score FROM SC WHERE CId = '02' ) AS 02_score ON 01_score.SId = 02_score.SId 
WHERE
	1_score > 2_score;

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

SELECT
	any_value ( sc.SId ) SId,
	any_value ( student.Sname ) Sname,
	avg( sc.score ) score 
FROM
	sc
	JOIN student ON sc.SId = student.SId 
GROUP BY
	sc.SId 
HAVING
	avg( sc.score ) > 60;

3.(略)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
	any_value(student.Sname),
	any_value(student.SId),
	count( sc.CId ) course_count,
	IFNULL( sum( sc.score ), 0 ) all_score
FROM
	student
  left JOIN sc ON student.SId = sc.SId 
GROUP BY
	student.SId;

4.(略)查询姓“张”的老师个数

SELECT count(*)
from teacher
where Tname like '张%';

5.查询没学过张三老师课的学生的学号和姓名

-- 查询学习过张三老师课的学生
SELECT SId
from sc
	INNER JOIN course on course.CId = sc.CId
	INNER JOIN teacher on teacher.TId = course.TId
where Tname =  "张三";

-- 
SELECT
	student.SId,
	student.Sname 
FROM
	student 
WHERE
	SId NOT IN (
        SELECT
        SId 
        FROM
        sc
        INNER JOIN course ON course.CId = sc.CId
        INNER JOIN teacher ON teacher.TId = course.TId 
        WHERE
        Tname = "张三" 
    );

6.查询学过张三老师所教的所有课程的同学的学号和姓名

SELECT
	student.SId,
	student.Sname,
	course.Cname,
	teacher.Tname 
FROM
	sc
	INNER JOIN course ON sc.CId = course.CId
	INNER JOIN teacher ON teacher.TId = course.TId
	INNER JOIN student ON student.SId = sc.SId 
WHERE
	teacher.Tname = "张三";
ORDER BY
	student.SId;

7.查询学过01课程并且学过02课程的学生

-- 查询同时学习过01和02课程的同学
SELECT
	a.SId 
FROM
	( SELECT sc.SId FROM sc WHERE sc.CId = '01' ) AS a
	INNER JOIN ( SELECT sc.SId FROM sc WHERE sc.CId = '02' ) AS b ON a.SId = b.SId;
-- 综合
SELECT
	* 
FROM
	student 
WHERE
	student.SId in (
        SELECT
        a.SId 
        FROM
        ( SELECT sc.SId FROM sc WHERE sc.CId = '01' ) AS a
        INNER JOIN ( SELECT sc.SId FROM sc WHERE sc.CId = '02' ) AS b ON a.SId = b.SId
    );

8.(略)查询学过02课程的总成绩

SELECT
	sum( score ) 
FROM
	sc 
WHERE
	sc.CId = '02';

9.查询所有课程成绩小于60的学生

-- 查询成绩小于60分的学生以及课程数
SELECT
	sc.sid,
	count( sc.CId ) AS acount 
FROM
	sc 
WHERE
	sc.score < 60 
GROUP BY
	sc.SId;

-- 查询每个学生的课程数
SELECT
	sc.sid,
	count( sc.CId ) AS bcount 
FROM
	sc 
GROUP BY
	sc.sid;

-- 汇总
SELECT
	a.sid,
	student.Sname 
FROM
	( SELECT sc.sid, count( sc.CId ) AS acount FROM sc WHERE sc.score < 60 GROUP BY sc.SId ) a
	INNER JOIN ( SELECT sc.sid, count( sc.CId ) AS bcount FROM sc GROUP BY sc.sid ) b ON a.SId = b.sid
	INNER JOIN student ON student.SId = a.sid 
WHERE
	a.acount = b.bcount;

10.查询没有学全所有课的学生学号、姓名(student为主表)

-- 查询所有的课程数量
SELECT count(*) FROM course;
-- 查询每个学生的课程数
SELECT sc.SId, count(sc.CId) from sc GROUP BY sc.SId;
-- 查询课程数小于课程数量的sid
SELECT
	any_value ( student.sid ),
	any_value ( student.sname ) 
FROM
	student
	LEFT JOIN sc ON sc.sid = student.sid 
GROUP BY
	student.sid 
HAVING
	count( DISTINCT sc.cid ) < ( SELECT count( cid ) FROM course );

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生学号、姓名

-- 查询01号学生的课程号 
SELECT sc.CId from sc WHERE sc.SId = '01';
-- 查询课程号在01号学生中的学生
SELECT DISTINCT sc.SId
from sc
WHERE sc.CId in (SELECT sc.CId from sc WHERE sc.SId = '01')
and sc.SId != '01';
-- 汇总
SELECT
	student.SId,
	student.Sname 
FROM
	student
	INNER JOIN (
        SELECT DISTINCT
        sc.SId 
        FROM
        sc 
        WHERE
        sc.CId IN ( SELECT sc.CId FROM sc WHERE sc.SId = '01' ) 
        AND sc.SId != '01' 
    ) a ON a.SId = student.SId;

12.查询和“01”学号学生所学课程完全相同的学生学号

SELECT
	* 
FROM
	student 
WHERE
	SId IN (SELECT SId FROM (
        SELECT * FROM sc a WHERE CId IN ( SELECT CId FROM sc WHERE SId = 01 )) b 
            GROUP BY SId 
            HAVING count( CId ) =(SELECT count( CId ) FROM sc c WHERE SId = 01 )) 
	AND SId != 01;

13.查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 查询张三老师讲授的课程
SELECT CId 
from 
	course, teacher
WHERE
	course.TId = teacher.TId
and 
	teacher.Tname = "张三";

-- 学过张三老师课程的学生
SELECT sc.SId 
from
	sc
	INNER JOIN course on course.CId = sc.CId
	INNER JOIN teacher on teacher.TId = course.TId
	AND teacher.Tname = "张三";


SELECT
	* 
FROM
	student 
WHERE
	SId NOT IN (
        SELECT
        sc.SId 
        FROM
        sc
        INNER JOIN course ON course.CId = sc.CId
        INNER JOIN teacher ON teacher.TId = course.TId 
        AND teacher.Tname = "张三" 
    );

15.查询两门及其以上不及格的同学的学号、姓名和平均成绩

SELECT
	any_value(sc.SId),
	any_value(student.Sname),
	AVG( sc.score ) 
FROM
	sc
	INNER JOIN student ON sc.SId = student.SId 
WHERE
	sc.score < 60 GROUP BY sc.SId HAVING count( DISTINCT sc.CId ) >= 2;

-- 查询每个学生成绩不及格的门数
SELECT SId,count(*) 
from sc
WHERE sc.score < 60
GROUP BY SId;

16.(略)检索01课程分数小于60 ，按课程分数降序排列学生信息

SELECT student.SId,Sname, sc.score
from 
	student
	INNER JOIN sc on student.SId = sc.SId
WHERE
	sc.score < 60 and sc.CId = 01
ORDER BY sc.score DESC;

17.按平均成绩从高到低显示所有学生的所有课程的成绩和平均成绩

SELECT
	sc.sid,
	max( CASE WHEN sc.cid = '01' THEN sc.score ELSE NULL END ) '语文',
	max( CASE WHEN sc.cid = '02' THEN sc.score ELSE NULL END ) '数学',
	max( CASE WHEN sc.cid = '03' THEN sc.score ELSE NULL END ) '英语',
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.sid 
ORDER BY
	avg( sc.score ) DESC;

查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率

-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT 
	sc.CId,
	course.Cname,
  max(sc.score),
	min(sc.score),
	avg(sc.score),
	sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / count(sc.SId) '及格',
	sum(CASE WHEN sc.score >= 70 and sc.score < 80 THEN 1 ELSE 0 END) / count(sc.SId) '中等',
	sum(CASE WHEN sc.score >= 80 and sc.score < 90 THEN 1 ELSE 0 END) / count(sc.SId) '优良',
	sum(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / count(sc.SId) '优秀'
from sc
	INNER JOIN course on course.CId = sc.CId
GROUP BY sc.CId, course.Cname;

19.按各科成绩进行排序，并显示排名

SELECT
	sc.sid,
	sc.cid,
	course.Cname,
	sc.score,
	ROW_NUMBER() over ( ORDER BY sc.CId, sc.score DESC ) 
FROM
	sc
	INNER JOIN course on course.CId = sc.CId;

20.(略)查询学生的总成绩并进行排名

SELECT
	any_value(sc.SId),
	any_value(student.Sname),
	sum(sc.score) grade
from sc
	INNER JOIN student on student.SId = sc.SId
GROUP BY sc.SId
ORDER BY grade desc;

21.(略)查询不同老师所教不同课程平均分从高到低展示

SELECT 
	any_value(teacher.Tname),
	sc.CId,
	avg(sc.score)
from 
	sc
	INNER JOIN course on course.CId = sc.CId
	INNER JOIN teacher on teacher.TId = course.TId
GROUP BY sc.CId
ORDER BY AVG(sc.score) desc;

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT 
	any_value(student.Sname),
	any_value(sc.SId),
	sum(sc.score) grade
from sc
	INNER JOIN student on student.SId = sc.SId
GROUP BY sc.SId
ORDER BY grade desc
LIMIT 1, 2;

23.使用分段来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT 
	sc.CId,
	any_value(course.Cname),
	sum(case WHEN sc.score < 60 THEN 1 else 0 end) '60以下',
	sum(case WHEN sc.score >= 60 and sc.score < 70 THEN 1 else 0 end) '60-70',
	sum(case WHEN sc.score >= 70 and sc.score < 80 THEN 1 else 0 end) '70-80',
	sum(case WHEN sc.score >= 80 and sc.score < 90 THEN 1 else 0 end) '80-90',
	sum(case WHEN sc.score >= 90 THEN 1 else 0 end) '90以上'
from 
	sc
	INNER JOIN course on course.CId = sc.CId
GROUP BY sc.CId;

24.查询学生平均成绩以及排名

SELECT
	sc.SId,
	any_value(student.Sname),
	avg( sc.score ) grade 
FROM
	sc
	INNER JOIN student on student.SId = sc.SId 
GROUP BY
	sc.SId 
ORDER BY
	grade DESC;

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

-- PARTITION BY作用于分组之下的排序，与group by类似，但是有区别
-- 进行每个科目的排序
SELECT
	*,
	ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m 
FROM
	sc
	INNER JOIN student stu ON stu.sid = sc.sid

-- 汇总结果
-- 窗口函数
SELECT
	*
FROM
	(
        SELECT
        student.SId, sc.CId, sc.score, student.Sname, student.Sage, student.Ssex, 
        ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m 
        FROM
        sc
        INNER JOIN student ON student.sid = sc.sid 
    ) a 
WHERE
	m IN ( 1,2, 3 )

26.(略)查询每门课程被选修的学生数

SELECT 
	sc.CId,
	count(sc.SId)
from sc
GROUP BY sc.CId;
-- 答案
SELECT
	course.cid,
	count( sc.sid ) 
FROM
	sc
	INNER JOIN course ON sc.CId = course.cid 
GROUP BY
	course.cid

27.(略)查询出只有两门课程的全部学生的学号和姓名

SELECT student.SId, any_value(student.Sname)
from student
	INNER JOIN sc on sc.SId = student.SId
GROUP BY student.SId
HAVING count(sc.CId) = 2;


-- 28.(略)查询男生、女生人数
SELECT 
	student.Ssex,
	count(student.Ssex)
from student
GROUP BY student.Ssex;

(略)查询名字中含有"三"字的学生信息

SELECT *
from student
WHERE student.Sname like "%三%";

31.查询1990年出生的学生名单

SELECT *
from student
WHERE year(student.Sage) = 1990;

-- 或者是用like
SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sage LIKE '1990%';

32.(略)查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT student.SId, any_value(student.Sname), avg(sc.score) avg_score
FROM student 
	INNER JOIN sc on sc.SId = student.SId
GROUP BY student.SId
HAVING avg_score >= 85;

33.(略)查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，

-- 按课程号降序排列
SELECT 
	sc.CId, 
	avg(sc.score)
from sc
GROUP BY sc.CId
ORDER BY avg(sc.score) asc, sc.CId DESC;

34.(略)查询课程名称为"数学"，且分数低于60分的学生姓名和分数

SELECT student.Sname, sc.score
from student
	INNER JOIN sc on student.SId = sc.SId
	INNER JOIN course on course.CId = sc.CId and course.Cname = "数学"
WHERE
	sc.score < 60;

35.查询所有学生的课程及分数情况

SELECT
	student.sid,
	any_value(student.sname),
	max( CASE WHEN course.cname = '语文' THEN sc.score ELSE NULL END ) '语文',
	max( CASE WHEN course.cname = '数学' THEN sc.score ELSE NULL END ) '数学',
	max( CASE WHEN course.cname = '英语' THEN sc.score ELSE NULL END ) '英语' 
FROM
	student
	INNER JOIN sc ON sc.sid = student.sid
	INNER JOIN course ON sc.CId = course.CId 
GROUP BY
	student.sid;

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT student.SId, student.Sname, course.Cname, sc.score
from student
	INNER JOIN sc on sc.SId = student.SId
	INNER JOIN course on course.CId = sc.CId
WHERE sc.score > 70;

37.(略)查询不及格的课程并按课程号从大到小排列

SELECT
	student.sid,
	student.sname,
	course.cname,
	sc.cid,
	sc.score 
FROM
	sc
	INNER JOIN course ON course.cid = sc.cid
	INNER JOIN student ON student.sid = sc.SId 
WHERE
	sc.score < 60 
ORDER BY
	sc.cid DESC;

38.(略)查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

SELECT student.SId, student.Sname, sc.score
from student
	INNER JOIN sc on sc.SId  = student.SId and sc.CId = 03
WHERE sc.score > 80;


-- 39.(略)求每门课程的学生人数
SELECT any_value(course.Cname), count(DISTINCT course.CId)
from course
	INNER JOIN sc on sc.CId  = course.CId
GROUP BY sc.CId;

40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT 
	any_value(student.SId), any_value(student.Sname), 
	any_value(sc.CId),any_value(course.Cname),
	max(sc.score)
from student
	INNER JOIN sc on sc.SId = student.SId
	INNER JOIN course on course.CId = sc.CId
	INNER JOIN teacher on teacher.TId = course.TId
WHERE teacher.Tname = "张三";

41.查询某个不同课程但成绩相同的学生编号、课程编号、学生成绩

SELECT
	sid 
FROM
	(SELECT b.sid,b.score 
     FROM sc AS b
     INNER JOIN ( SELECT sid FROM sc GROUP BY sid HAVING count( DISTINCT cid )> 1 ) AS c ON b.sid = c.sid 
     GROUP BY
     b.sid,
     b.score 
    ) AS a 
GROUP BY sid HAVING	count( sid )=1

42.查询每一门课程最好的前两名

SELECT a.CId, a.SId, any_value(a.score)
from sc a
	LEFT JOIN sc b
	on a.CId = b.CId
	and a.score < b.score	
	GROUP BY a.CId, a.SId
HAVING count(a.CId) < 2;

43.统计每门课程的学生选修人数（超过5人的课程才统计）

-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.CId, count(sc.SId) as cnt
from sc
GROUP BY sc.CId
HAVING cnt > 5
ORDER BY cnt DESC, sc.CId ASC;

44.检索至少选修两门课程的学生学号

SELECT sc.SId, any_value(student.Sname), count(sc.CId) cnt
from sc
	INNER JOIN student on student.SId = sc.SId
GROUP BY sc.SId
HAVING cnt >= 2;

45.查询选修了全部课程的学生信息

SELECT sc.SId, any_value(student.Sname), count(sc.CId) cnt
from sc
	INNER JOIN student on student.SId = sc.SId
GROUP BY sc.SId
HAVING cnt = (SELECT count(*) from course);


-- 日期类函数
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT DATE("2022-12-10");

46.(略)查询各学生的年龄

SELECT
	student.Sname,
	TIMESTAMPDIFF(
        YEAR,
        student.Sage,
        CURTIME()) 
FROM
	student;

47.(略)查询本周过生日的学生

SELECT
	* 
FROM
	student 
WHERE 
	WEEK(Sage, 1) = WEEK(NOW(), 1);

-- WEEKOFYEAR() 和 CURDATE()。WEEKOFYEAR() 函数用于获取某个日期所在的周，而 CURDATE() 函数用于获取当前日期。
SELECT * from student
WHERE WEEKOFYEAR(student.Sage) = WEEKOFYEAR(CURDATE());

48.查询下周过生日的学生

-- 如果想要查询的是下一周过生日的学生，可以把 CURDATE() 替换为 CURDATE() + INTERVAL 7 DAY
SELECT * from student WHERE 
WEEKOFYEAR(student.Sage) = WEEKOFYEAR(CURDATE() + INTERVAL 7 day);

49.(略)查询本月过生日的学生

SELECT * from student
WHERE MONTH(student.Sage) = MONTH(CURDATE());

50.查询下月过生日的学生

SELECT * from student
WHERE MONTH(student.Sage) = MONTH(CURDATE() + INTERVAL 1 MONTH);

d GROUP BY sc.SId HAVING cnt = (SELECT count(*) from course);

-- 日期类函数 SELECT NOW(); SELECT CURDATE(); SELECT CURTIME(); SELECT DATE("2022-12-10");




##### 46.(略)查询各学生的年龄

```sql
SELECT
	student.Sname,
	TIMESTAMPDIFF(
        YEAR,
        student.Sage,
        CURTIME()) 
FROM
	student;

47.(略)查询本周过生日的学生

SELECT
	* 
FROM
	student 
WHERE 
	WEEK(Sage, 1) = WEEK(NOW(), 1);

-- WEEKOFYEAR() 和 CURDATE()。WEEKOFYEAR() 函数用于获取某个日期所在的周，而 CURDATE() 函数用于获取当前日期。
SELECT * from student
WHERE WEEKOFYEAR(student.Sage) = WEEKOFYEAR(CURDATE());

48.查询下周过生日的学生

-- 如果想要查询的是下一周过生日的学生，可以把 CURDATE() 替换为 CURDATE() + INTERVAL 7 DAY
SELECT * from student WHERE 
WEEKOFYEAR(student.Sage) = WEEKOFYEAR(CURDATE() + INTERVAL 7 day);

49.(略)查询本月过生日的学生

SELECT * from student
WHERE MONTH(student.Sage) = MONTH(CURDATE());

50.查询下月过生日的学生

SELECT * from student
WHERE MONTH(student.Sage) = MONTH(CURDATE() + INTERVAL 1 MONTH);

#找工作时遇到的神仙HR##如何看待2023届秋招##0offer是寒冬太冷还是我太菜##你觉得今年秋招难吗#

全部评论

推荐最新楼层

11-19 17:15

华南农业大学运营

在楼梯睡觉被领导抓到

周一上班太困了，眼皮都睁不开，我就想躲在公司楼梯里悄悄睡一会，睡得正香呢，遇到老板出来抽烟，他站在楼梯口，我只能战战兢兢地蹲在那里，结果还是被领导看到了。

职场中你干过哪些“蠢”事

点赞评论收藏

今天 17:35

厦门大学嘉庚学院用户运营

入职第一天的正确打开方式

入职第一天，千万别傻傻地坐在工位上发呆。虽然领导可能会说先熟悉环境，但这并不意味着你可以闲着。其实，第一天的关键在于主动出击。要有“工作的样子”，主动探索、学习和交流。最重要的是，养成写日报的习惯，每天总结和反思，并主动提交给领导。日报模板分享工作内容第一天，我通过PPT了解了公司的企业文化和考勤制度，这让我对公司的运作有了初步的认识。接着，我开始了解公司的主要产品线和目前负责项目的进度，重点是弄清楚项目的产品和定位。最后，我明确了接下来一周的主要工作内容。工作计划接下来，我计划继续深入学习公司的组织架构和业务产品信息。同时，我会详细了解公司在公众号、小红书和抖音上发布的内容，并初步确定小红书...

点赞评论收藏

10-05 23:02

东北大学 Java

很难想象仅仅过去了4年找工作的难度就大不一样

我说句实话啊：那时候看三个月培训班视频，随便做个项目背点八股，都能说3 40w是侮辱价

点赞评论收藏

11-15 15:20

华北电力大学 Web前端

发现了新老员工之间的区别

和同龄的刚入职场没几年的同事交流 不管是工作还是日常 没有那种别人在分析我的感受 所以说话没有障碍 和老员工交流 总给我一种他时刻在揣摩我话外之意的感觉 即便我没那个意思 只要他能想到，就会认为我有这个意思 然后让我解释我那句话什么意思 总感觉他在时时刻刻分析我 我想：或许是他在过往工作中被人坑过吧，现在觉得我身边没有人是坏人，但是以后呢，万一呢，我不会一直幸运 我想：或许我以后也会变成这样子，小心谨慎 我想：或许我不会变成这样，可能会选择离开，而不是被同化 我想：一份工作而已

点赞评论收藏

3 20 评论

招聘动态

杉川机器人

2025校园招聘

字节跳动

2025校园招聘

字节跳动Data

2025校园招聘

快手Star

2025届招聘

快手

销售类投递专区

库洛游戏

全站热榜

正在热议

# 选完offer后，你后悔学本专业吗 #

MySQL入门到精通经典50题，看这一篇就够了

MySQL安装教程详解

数据预览

sql建表语句

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

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

3.(略)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

4.(略)查询姓“张”的老师个数

5.查询没学过张三老师课的学生的学号和姓名

6.查询学过张三老师所教的所有课程的同学的学号和姓名

7.查询学过01课程并且学过02课程的学生

8.(略)查询学过02课程的总成绩

9.查询所有课程成绩小于60的学生

10.查询没有学全所有课的学生学号、姓名(student为主表)

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生学号、姓名

12.查询和“01”学号学生所学课程完全相同的学生学号

13.查询没学过"张三"老师讲授的任一门课程的学生姓名

15.查询两门及其以上不及格的同学的学号、姓名和平均成绩

16.(略)检索01课程分数小于60 ，按课程分数降序排列学生信息

17.按平均成绩从高到低显示所有学生的所有课程的成绩和平均成绩

查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率

19.按各科成绩进行排序，并显示排名

20.(略)查询学生的总成绩并进行排名

21.(略)查询不同老师所教不同课程平均分从高到低展示

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

23.使用分段来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

24.查询学生平均成绩以及排名

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

26.(略)查询每门课程被选修的学生数

27.(略)查询出只有两门课程的全部学生的学号和姓名

32.(略)查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

34.(略)查询课程名称为"数学"，且分数低于60分的学生姓名和分数

35.查询所有学生的课程及分数情况

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

37.(略)查询不及格的课程并按课程号从大到小排列

38.(略)查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

41.查询某个不同课程但成绩相同的学生编号、课程编号、学生成绩

42.查询每一门课程最好的前两名

43.统计每门课程的学生选修人数（超过5人的课程才统计）

44.检索至少选修两门课程的学生学号

45.查询选修了全部课程的学生信息

46.(略)查询各学生的年龄

47.(略)查询本周过生日的学生

48.查询下周过生日的学生

49.(略)查询本月过生日的学生

50.查询下月过生日的学生

47.(略)查询本周过生日的学生

48.查询下周过生日的学生

49.(略)查询本月过生日的学生

50.查询下月过生日的学生

全站热榜