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是寒冬太冷还是我太菜##你觉得今年秋招难吗#

全部评论

推荐最新楼层

管泽元呢

06-30 23:41

点赞评论收藏

心愿便利贴

07-04 17:43

杭州电子科技大学运营

关于秋招的一些心得

秋招已经开始了，作为过来人今天想分享一些信息差和关于秋招的基础知识一、投递策略一定一定要遵循”海王法则勇敢去试，勇敢去投，勇敢的人先享受世界，记住不要一个公司投很多个岗位，尽量找最想做，简历也最贴合的岗位去投递；也不要一份简历投很多岗位，可以提前梳理自己想要从事的方向，多备几份简历：例如产品、运营、市场、职能等方向，需要有不同的侧重，这一点牛客上有很多简历，大家可以互相交流~二、投递时一定要做好分级管理记录很重要！将自己要投递的公司进行记录！可以先投一些自己意愿度没那么高的公司，主要在于套题库、打磨简历为主，打磨简历后，再进行分级投递三、投递时间一定要以hr上班时间投递，并且在筛选简历的那个时...

点赞评论收藏

被迫内卷的王同学

05-21 12:26

大连理工大学嵌入式软件开发

嵌入式软件实习简历，好难找！

#牛客帮帮团来啦！有问必答#5月初才开始投实习，投了有20多家吧，只有一家小厂给过面试机会，中厂基本都直接挂简历了，大家能不能帮忙看看哪里需要修改，还是说之前干过机械结构设计转行受限呢，ε=(´ο｀*)))唉。

鼠鼠酱：学历和技术基础说实话感觉都很顶了，个人感觉最大的概率是第一学历非科班，目前大家都倾向于要科班一贯出身的，你也许可以考虑面试的时候表现的更像核动力驴一点

牛客帮帮团来啦！有问必答

点赞评论收藏

国产水货

06-28 00:09

门头沟学院运维开发工程师

百度

拿下百度，待遇还行，知足了，上班了 #百度工作体验#

百度工作体验

点赞评论收藏

清隆学长a

07-02 10:57

北京理工大学算法工程师

最新华为OD机试真题-整数的连续自然数之和表示(100分)

🍭 大家好这里是清隆学长 ，一枚热爱算法的程序员 ✨ 本系列打算持续跟新华为OD-D卷的三语言AC题解 👏 感谢大家的订阅➕ 和 喜欢💗 📎在线评测链接 => 整数的连续自然数之和表示(100分) <= 🌍 评测功能需要 =>订阅专栏<= 后联系清隆解锁～ 🍓OJ题目截图 🍰 整数的连续自然数之和表示 问题描述 给定一个正整数 ，请找出所有用连续自然数之和来表示 的方案。例如，整数 可以表示为：，，。你的任务是按照以下要求，输出所有表示 的方案： 优先输出自然数个数最少的表达式。 每个表达式中的自然数按照递增顺序输出。 最后输出表达式的总...

查看1道真题和解析

投递华为等公司10个岗位 > 最新华为OD机试-D卷

点赞评论收藏

3 19 评论

招聘动态

中国民生银行信用卡中心

“民生π”菁英挑战赛

2025届校招Special 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.查询下月过生日的学生

全站热榜