当前位置：首页 > news >正文

MySQL 50 题。

news 2025/8/28 10:49:51

MySQL 50 题。

文章目录

- MySQL 50 题。
- - 数据库。
  - sql。

数据库。

CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 ;Operation failed: There was an error while applying the SQL script to the database.
Executing:
CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 ;ERROR 1044: Access denied for user 'lyfgeek'@'%' to database 'new_schema'
SQL Statement:
CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4

CREATE SCHEMA `mysql_fifty` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE `lyfgeek`.`student`
(`id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',`s_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '学生 id。',`s_name`         VARCHAR(45) NOT NULL DEFAULT '' COMMENT '姓名。',`s_birth`        VARCHAR(45) NOT NULL DEFAULT '' COMMENT '出生年月。',`s_sex`          VARCHAR(45) NOT NULL DEFAULT '' COMMENT '性别。',`db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',`db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',`db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',PRIMARY KEY (`id`, `s_id`),UNIQUE INDEX `s_id_UNIQUE` (`s_id` ASC) VISIBLE
)ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COMMENT = '学生。';-- 课程表。
CREATE TABLE `lyfgeek`.`course`
(`id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',`c_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '课程 id。',`c_name`         VARCHAR(45) NOT NULL DEFAULT '' COMMENT '课程名称。',`t_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '教师 id。',`db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',`db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',`db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',PRIMARY KEY (`id`),UNIQUE INDEX `c_id_UNIQUE` (`c_id` ASC) VISIBLE
)ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COMMENT = '成绩。';-- 教师表。
CREATE TABLE `lyfgeek`.`teacher`
(`id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',`t_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '教师 id。',`t_name`         VARCHAR(45) NOT NULL DEFAULT '' COMMENT '姓名。',`db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',`db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',`db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',PRIMARY KEY (`id`, `t_id`),UNIQUE INDEX `t_id_UNIQUE` (`t_id` ASC) VISIBLE
)ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COMMENT = '教师。';-- 成绩表。
CREATE TABLE `lyfgeek`.`score`
(`id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',`s_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '学生 id。',`c_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '课程 id。',`s_score`        INT         NULL     DEFAULT NULL COMMENT '成绩。',`db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',`db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',`db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',PRIMARY KEY (`id`)
)ENGINE = InnoDBDEFAULT CHARACTER SET = utf8mb4COMMENT = '成绩。';# 学生表测试数据。
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('01', '学生 1', '2021-01-01', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('02', '学生 2', '2022-02-02', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('03', '学生 3', '2023-03-03', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('04', '学生 4', '2024-04-04', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('05', '学生 5', '2025-05-05', '女');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('06', '学生 6', '2026-06-06', '女');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('07', '学生 7', '2027-07-07', '女');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('08', '学生 8', '2028-08-08', '女');# 课程表测试数据。
INSERT INTO `lyfgeek`.`course`(`c_id`, `c_name`, `t_id`)
VALUES ('01', '语文', '02');
INSERT INTO `lyfgeek`.`course`(`c_id`, `c_name`, `t_id`)
VALUES ('02', '数学', '01');
INSERT INTO `lyfgeek`.`course`(`c_id`, `c_name`, `t_id`)
VALUES ('03', '英语', '03');# 教师表测试数据。
INSERT INTO `lyfgeek`.`teacher` (`t_id`, `t_name`)
VALUES ('01', '老师 1');
INSERT INTO `lyfgeek`.`teacher` (`t_id`, `t_name`)
VALUES ('02', '老师 2');
INSERT INTO `lyfgeek`.`teacher` (`t_id`, `t_name`)
VALUES ('03', '老师 3');# 成绩表测试数据。
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('01', '01', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('01', '02', '90');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('01', '03', '99');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('02', '01', '70');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('02', '02', '60');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('02', '03', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('03', '01', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('03', '02', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('03', '03', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('04', '01', '50');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('04', '02', '30');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('04', '03', '20');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('05', '01', '76');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('05', '02', '87');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('06', '01', '31');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('06', '03', '34');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('07', '02', '89');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('07', '03', '98');

sql。

-- 查询课程"01"比课程"02"成绩高的学生的信息及课程分数。
-- - in ~ 长型数据变成宽型数据。
SELECT st.*,t.s01,t.s02
FROM (SELECT sc.`s_id`,MAX(CASEWHEN sc.`c_id` = '01' THEN sc.`s_score`END) s01,MAX(CASEWHEN sc.`c_id` = '02' THEN sc.`s_score`END) s02FROM `score` scGROUP BY sc.`s_id`) t,`student` st
WHERE t.s01 > t.s02AND t.`s_id` = st.`s_id`;-- 自连接。
SELECT st.*,sc1.`s_score`,sc2.`s_score`
FROM `student` st,`score` sc1,`score` sc2
WHERE st.`s_id` = sc1.`s_id`AND sc1.`s_id` = sc2.`s_id`AND sc1.`c_id` = '01'AND sc2.`c_id` = '02'AND sc1.`s_score` > sc2.`s_score`;-- 2、查询课程"01"比课程"02"成绩低的学生的信息及课程分数。
SELECT `student`.*,sc1.`s_score`,sc2.`s_score`
FROM `student`INNER JOIN`score` sc1 ON `student`.`s_id` = sc1.`s_id`AND sc1.`c_id` = '01'INNER JOIN`score` sc2 ON sc1.`s_id` = sc2.`s_id`AND sc2.`c_id` = '02'
WHERE sc1.`s_score` < sc2.`s_score`OR sc1.`s_score` IS NULL;-- 3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。
-- 子查询方法。
SELECT sc.`s_id`,(SELECT `s_name`FROM `student` stWHERE st.`s_id` = sc.`s_id`) AS s_name,AVG(sc.`s_score`)                avg_score
FROM `score` sc
GROUP BY sc.`s_id`
HAVING avg_score >= 60;-- 两个表连接方法。
SELECT st.`s_id`,st.`s_name`,AVG(sc.`s_score`)
FROM `student` st,`score` sc
WHERE st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING AVG(sc.`s_score` >= 60);-- 4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩。
-- （包括有成绩的和无成绩的）。
SELECT st.`s_id`,st.`s_name`,AVG(sc.`s_score`) AS avg_score
FROM `student` st,`score` sc
WHERE st.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`) < 60
UNION
SELECT st.`s_id`,st.`s_name`,0 AS avg_score
FROM `student` st
WHERE st.`s_id` NOT IN (SELECT DISTINCT `s_id`FROM `score`);-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT st.`s_id`,st.`s_name`,COUNT(sc.s_id)               AS sum_course,IFNULL(SUM(sc.`s_score`), 0) AS sum_score
FROM `student` stLEFT OUTER JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`;-- 6、查询"李"姓老师的数量。
SELECT COUNT(`t_id`)
FROM `teacher`
WHERE `t_name` LIKE '李%';-- 7、查询学过"张三"老师授课的同学的信息。
SELECT st.*
FROM `student` stJOIN`score` sc ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` IN (SELECT `c_id`FROM `course`WHERE `t_id` = (SELECT `t_id`FROM `teacher`WHERE `t_name` = '老师 1'));SELECT st.*
FROM `student` stINNER JOIN`score` sc ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` IN (SELECT `c_id`FROM `course` cINNER JOIN`teacher` t ON c.`t_id` = t.`t_id`WHERE `t`.`t_name` = '老师 1');-- 8、查询没学过"张三"老师授课的同学的信息。
SELECT *
FROM `student` st
WHERE st.s_id NOT IN (SELECT st.`s_id`FROM `student` stJOIN`score` sc ON st.`s_id` = sc.`s_id`WHERE sc.`c_id` IN (SELECT `c_id`FROM `course`WHERE `t_id` = (SELECT `t_id`FROM `teacher`WHERE `t_name` = '老师 1')));SELECT *
FROM `student`
WHERE `s_id` NOT IN (SELECT sc.`s_id`FROM `teacher` t,`score` sc,`course` cWHERE t.`t_id` = c.`t_id`AND c.`c_id` = sc.`c_id`AND t.`t_name` = '老师 1');SELECT *
FROM `student`
WHERE NOT EXISTS(SELECT 1FROM (SELECT sc.`s_id`FROM `teacher` t,`score` sc,`course` cWHERE t.`t_id` = c.`t_id`AND c.`c_id` = sc.`c_id`AND t.`t_name` = '老师 1') tWHERE t.s_id = student.s_id);-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
SELECT st.*
FROM `student` st,`score` sc1,`score` sc2
WHERE st.`s_id` = sc1.`s_id`AND st.`s_id` = sc2.`s_id`AND sc1.`c_id` = '01'AND sc2.`c_id` = '02';-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
SELECT st.*
FROM `student` st
WHERE st.`s_id` IN (SELECT `s_id`FROM `score`WHERE `c_id` = '01')AND st.`s_id` NOT IN (SELECT `s_id`FROM `score`WHERE `c_id` = '02');SELECT st.*
FROM `student` st,(SELECT `s_id`,MAX(IF(`c_id` = '01', `s_score`, NULL)) s01,MAX(IF(`c_id` = '02', `s_score`, NULL)) s02FROM `score`GROUP BY `s_id`) t
WHERE t.`s_id` = st.`s_id`AND t.s01 IS NOT NULLAND t.s02 IS NULL;-- 11、查询没有学全所有课程的同学的信息。
SELECT st.*,COUNT(sc.`c_id`) count_sc
FROM `student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING COUNT(sc.`c_id`) < (SELECT COUNT(`c_id`)FROM `course`);SELECT st.*
FROM `student` st
WHERE st.`s_id` IN (SELECT `s_id`FROM `score`WHERE `s_id` NOT IN (SELECT sc1.`s_id`FROM `score` sc1JOIN`score` sc2 ON sc1.`s_id` = sc2.`s_id`AND sc2.`c_id` = '02'JOIN`score` sc3 ON sc1.`s_id` = sc3.`s_id`AND sc3.`c_id` = '03'WHERE sc1.`c_id` = '01'));-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
SELECT *
FROM `student`
WHERE `s_id` IN (SELECT DISTINCT `s_id`FROM `score`WHERE `c_id` IN (SELECT `c_id`FROM `score`WHERE `s_id` = '01'))
GROUP BY 1, 2, 3, 4;
-- 第 1 2 3 4 字段。-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息。-- 创建 01 所学临时表。
CREATE TABLE s01_sc_temp AS
SELECT t1.*, sc.`c_id` cid2
FROM (SELECT st.*,t2.`c_id`FROM `student` st,(SELECT `c_id`FROM `score`WHERE `s_id` = '01') t2) t1LEFT JOIN`score` sc ON t1.`s_id` = sc.`s_id`AND t1.`c_id` = sc.`c_id`
UNION
SELECT t.*,sc.`c_id` cid2
FROM (SELECT st.*,b.`c_id`FROM `student` st,(SELECT `c_id`FROM `score`WHERE `s_id` = '01') b) tRIGHT JOIN`score` sc ON t.`s_id` = sc.`s_id`AND t.`c_id` = sc.`c_id`;SELECT *
FROM `student`
WHERE `s_id` NOT IN (SELECT `s_id`FROM s01_s_tempWHERE cid2 IS NULLOR `c_id` IS NULL)AND `s_id` != '01';-- ~ ~ ~SELECT *
FROM `student`
WHERE `s_id` IN (SELECT DISTINCT `s_id`FROM `score`WHERE `s_id` != '01'AND `c_id` IN (SELECT `c_id`FROM `score`WHERE `s_id` = '01')GROUP BY `s_id`HAVING COUNT(1) = (SELECT COUNT(1)FROM `score`WHERE `s_id` = '01'));-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT st.`s_name`
FROM `student` st
WHERE st.`s_id` NOT IN (SELECT `s_id`FROM `score`WHERE `c_id` = (SELECT `c_id`FROM `course`WHERE `t_id` = (SELECT `t_id`FROM `teacher`WHERE `t_name` = '老师 1'))GROUP BY s_id);-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
SELECT st.`s_id`,st.`s_name`,AVG(sc.`s_score`) avg_score
FROM `student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;SELECT st.`s_id`,st.`s_name`,AVG(sc.`s_score`) avg_score
FROM `student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;SELECT st.`s_id`,st.`s_name`,ROUND(AVG(sc.`s_score`))
FROM student stLEFT JOINscore sc ON st.`s_id` = sc.`s_id`
WHERE st.`s_id` IN (SELECT `s_id`FROM `score`WHERE `s_score` < 60GROUP BY `s_id`HAVING COUNT(1) >= 2)
GROUP BY st.`s_id`, st.`s_name`;-- 16、检索课程"01"分数小于 60，按分数降序排列的学生信息。
SELECT st.*,sc.`s_score`
FROM `score` scRIGHT JOIN`student` st ON sc.`s_id` = st.`s_id`
WHERE sc.`c_id` = '01'AND sc.`s_score` < 60
ORDER BY `s_score` DESC;SELECT st.*,sc.`c_id`,sc.`s_score`
FROM `student` st,`score` sc
WHERE st.`s_id` = sc.`s_id`AND sc.`c_id` = '01'AND sc.`s_score` < 60
ORDER BY sc.`s_score` DESC;-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT sc.`s_id`,(SELECT `s_score`FROM `score`WHERE `s_id` = sc.`s_id`AND `c_id` = '01')    AS 语文,(SELECT `s_score`FROM `score`WHERE `s_id` = sc.`s_id`AND `c_id` = '02')    AS 数学,(SELECT `s_score`FROM `score`WHERE `s_id` = sc.`s_id`AND `c_id` = '03')    AS 英语,ROUND(AVG(`s_score`), 2) AS 平均分
FROM `score` sc
GROUP BY sc.`s_id`
ORDER BY 平均分 DESC;-- 18.查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
-- 及格为 >= 60，中等为：70 ~ 80，优良为：80 ~ 90，优秀为：>= 90。
SELECT sc.`c_id`,c.`c_name`,MAX(`s_score`),MIN(`s_score`),ROUND(AVG(`s_score`), 2),ROUND(100 * (SUM(IF(sc.`s_score` >= 60, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 及格率,ROUND(100 * (SUM(IF(sc.`s_score` >= 70 AND sc.`s_score` <= 80, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 中等率,ROUND(100 * (SUM(IF(sc.`s_score` >= 80 AND sc.`s_score` <= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 优良率,ROUND(100 * (SUM(IF(sc.`s_score` >= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 优秀率
FROM `score` scLEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
GROUP BY sc.`c_id`, c.`c_name`;-- 19、按各科成绩进行排序，并显示排名（实现不完全）。
-- mysql 没有 rank(); 函数。
SELECT sc1.`c_id`,sc1.`s_id`,sc1.`s_score`,COUNT(sc2.`s_score`) + 1 AS `rank`
FROM `score` sc1LEFT JOIN`score` sc2 ON sc1.`s_score` < sc2.`s_score`AND sc1.`c_id` = sc2.`c_id`
GROUP BY sc1.`c_id`, sc1.`s_id`, sc1.`s_score`
ORDER BY sc1.`c_id`, `rank`;-- 20、查询学生的总成绩并进行排名。
SELECT t1.`s_id`,@i := @i + 1                              AS i,@k := (IF(@score = t1.sum_score, @k, @i)) AS `rank`,@score := t1.sum_score                    AS score
FROM (SELECT `s_id`,SUM(`s_score`) AS sum_scoreFROM `score`GROUP BY `s_id`ORDER BY sum_score DESC) t1,(SELECT @k := 0, @i := 0, @score := 0) AS kis;-- 21、查询不同老师所教不同课程平均分从高到低显示。
SELECT c.`t_id`,t.`t_name`,c.`c_id`,ROUND(AVG(`s_score`), 2) AS avg_score
FROM `course` cLEFT JOIN`score` sc ON c.`c_id` = sc.`c_id`LEFT JOIN`teacher` t ON c.`t_id` = t.`t_id`
GROUP BY c.`c_id`, c.`t_id`, t.`t_name`
ORDER BY avg_score DESC;-- 22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩。
SELECT d.*,si2.排名,si2.`s_score`,si2.`c_id`
FROM (SELECT sc.`s_id`,sc.`s_score`,sc.`c_id`,@i := @i + 1 AS 排名FROM `score` sc,(SELECT @i := 0) as i2WHERE sc.c_id = '01') as si2LEFT JOINstudent d ON si2.s_id = d.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT st.*,sj2.排名,sj2.s_score,sj2.c_id
FROM (SELECT sc.s_id,sc.s_score,sc.c_id,@j := @j + 1 AS 排名FROM `score` sc,(SELECT @j := 0) as j2WHERE sc.c_id = '02') as sj2LEFT JOIN`student` st ON sj2.s_id = st.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT d.*,s.排名,s.`s_score`,s.`c_id`
FROM (SELECT sc.`s_id`,sc.`s_score`,sc.`c_id`,@k := @k + 1 AS 排名FROM `score` sc,(SELECT @k := 0) as k2WHERE sc.`c_id` = '03') as sLEFT JOIN`student` d ON s.`s_id` = d.`s_id`
WHERE 排名 BETWEEN 2 AND 3;-- 23、统计各科成绩各分数段人数：课程编号, 课程名称, [100-85], [85-70], [70-60], [0-60] 及所占百分比。
SELECT DISTINCT c.`c_name`,sc.`c_id`,t1.`85-100`,t1.百分比,t2.`70-85`,t2.百分比,t3.`60-70`,t3.百分比,t4.`0-60`,t4.百分比
FROM `score` scLEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0))                              AS `85-100`,ROUND(100 * (SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t1 ON sc.`c_id` = t1.`c_id`LEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0))                              AS `70-85`,ROUND(100 * (SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t2 ON sc.`c_id` = t2.`c_id`LEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0))                              AS `60-70`,ROUND(100 * (SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t3 ON sc.`c_id` = t3.`c_id`LEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0))                              AS `0-60`,ROUND(100 * (SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t4 ON sc.`c_id` = t4.`c_id`LEFT JOIN`course` c ON sc.`c_id` = c.`c_id`;-- 24、查询学生平均成绩及其名次。
SELECT t.`s_id`,@i := @i + 1                                 AS '不保留空缺排名',@k := (IF(@avg_score = t.avg_score, @k, @i)) AS '保留空缺排名',@avg_score := avg_score                      AS '平均分'
FROM (SELECT `s_id`,ROUND(AVG(`s_score`), 2) AS avg_scoreFROM scoreGROUP BY `s_id`) t,(SELECT @avg_score := 0, @i := 0, @k := 0) b;-- 25、查询各科成绩前三名的记录。
-- - 选出 sc1 表比 sc2 表成绩大的所有组。
-- - 选出比当前 id 成绩大的小于三个的。
SELECT sc1.`s_id`,sc1.`c_id`,sc1.`s_score`
FROM `score` sc1LEFT JOIN`score` sc2 ON sc1.`c_id` = sc2.`c_id`AND sc1.`s_score` < sc2.`s_score`
GROUP BY sc1.`s_id`, sc1.`c_id`, sc1.`s_score`
HAVING COUNT(sc2.`s_id`) < 3
ORDER BY sc1.`c_id`, sc1.`s_score` DESC;-- 26、查询每门课程被选修的学生数。
SELECT `c_id`,COUNT(`s_id`)
FROM `score`
GROUP BY `c_id`;-- 27、查询出只有两门课程的全部学生的学号和姓名。
SELECT `s_id`,`s_name`
FROM `student`
WHERE `s_id` IN (SELECT `s_id`FROM `score`GROUP BY `s_id`HAVING COUNT(`c_id`) = 2);-- 28、查询男生、女生人数。
SELECT `s_sex`,COUNT(`s_sex`) AS 人数
FROM student
GROUP BY s_sex;-- 29、查询名字中含有"风"字的学生信息。
SELECT *
FROM `student`
WHERE `s_name` LIKE '%风%';-- 30、查询同名同性学生名单，并统计同名人数。
SELECT st1.`s_name`,st1.`s_sex`,COUNT(*)
FROM `student` st1JOIN`student` st2 ON st1.`s_id` != st2.`s_id`AND st1.`s_name` = st2.`s_name`AND st1.`s_sex` = st2.`s_sex`
GROUP BY st1.`s_name`, st1.`s_sex`;-- 31、查询 1990 年出生的学生名单。
SELECT `s_name`
FROM `student`
WHERE `s_birth` LIKE '1990%';-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT `c_id`,ROUND(AVG(`s_score`), 2) AS avg_score
FROM `score`
GROUP BY `c_id`
ORDER BY avg_score DESC, `c_id` ASC;-- 33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩。
SELECT sc.`s_id`,st.`s_name`,ROUND(AVG(sc.`s_score`), 2) AS avg_score
FROM `score` scLEFT JOIN`student` st ON sc.`s_id` = st.`s_id`
GROUP BY `s_id`
HAVING avg_score >= 85;-- 34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数。
SELECT st.`s_name`,sc.`s_score`
FROM `score` scLEFT JOIN`student` st ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` = (SELECT `c_id`FROM `course`WHERE `c_name` = '数学')AND sc.s_score < 60;-- 35、查询所有学生的课程及分数情况。
SELECT st.`s_id`,st.`s_name`,SUM(IF(c.`c_name` = '语文', sc.`s_score`, 0)) AS '语文',SUM(IF(c.`c_name` = '数学', sc.`s_score`, 0)) AS '数学',SUM(IF(c.`c_name` = '英语', sc.`s_score`, 0)) AS '英语',SUM(sc.`s_score`)                             AS '总分'
FROM `student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`LEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
GROUP BY st.`s_id`, st.`s_name`;-- 36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数。
SELECT st.`s_name`,c.`c_name`,sc.`s_score`
FROM `course` cLEFT JOIN`score` sc ON c.`c_id` = sc.`c_id`LEFT JOIN`student` st ON st.`s_id` = sc.`s_id`
WHERE sc.`s_score` >= 70;-- 37、查询不及格的课程。
SELECT sc.`s_id`,sc.`c_id`,c.`c_name`,sc.`s_score`
FROM `score` scLEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
WHERE sc.`s_score` < 60;-- 38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名。
SELECT sc.`s_id`,st.`s_name`
FROM `score` scLEFT JOIN`student` st ON sc.`s_id` = st.`s_id`
WHERE sc.`c_id` = '01'AND sc.`s_score` > 80;-- 39、求每门课程的学生人数。
SELECT COUNT(*)
FROM `score`
GROUP BY `c_id`;-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
-- 查询老师 id。
SELECT `c_id`
FROM `course` c,`teacher` t
WHERE c.`t_id` = t.`t_id`AND t.`t_name` = '老师 1';
-- 查询最高分（可能有相同分数）。
SELECT MAX(`s_score`)
FROM `score`
WHERE `c_id` = '02';
-- 查询信息。
SELECT st.*,sc.`s_score`,sc.`c_id`,c.`c_name`
FROM `student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`LEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
WHERE sc.`c_id` = (SELECT `c_id`FROM `course` c,`teacher` tWHERE c.`t_id` = t.`t_id`AND t.`t_name` = '老师 1')AND sc.s_score IN (SELECT MAX(s_score)FROM scoreWHERE c_id = '02');SELECT c.`c_name`, sc.`s_score`, st.*
FROM `course` c,`score` sc,`teacher` t,`student` st
WHERE t.`t_id` = c.`t_id`AND c.`c_id` = sc.`c_id`AND st.`s_id` = sc.`s_id`AND t.`t_name` = '老师 1'AND sc.`s_score` IN (SELECT MAX(`s_score`)FROM `course`,`score`,`teacher`,`student`WHERE `teacher`.`t_id` = `course`.`t_id`AND `course`.`c_id` = `score`.`c_id`AND `student`.`s_id` = `score`.`s_id`AND `teacher`.`t_name` = '老师 1');-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
SELECT DISTINCT sc2.`s_id`,sc2.`c_id`,sc2.`s_score`
FROM `score` sc1,`score` sc2
WHERE sc1.`c_id` != sc2.`c_id`AND sc1.`s_score` = sc2.`s_score`;-- 42、查询每门功成绩最好的前两名。
-- 牛逼的写法。
SELECT sc1.`s_id`,sc1.`c_id`,sc1.`s_score`
FROM `score` sc1
WHERE (SELECT COUNT(1)FROM `score` sc2WHERE sc2.`c_id` = sc1.`c_id`AND sc2.`s_score` >= sc1.`s_score`) <= 2
ORDER BY sc1.`c_id`;-- 43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT `c_id`,COUNT(*) AS total
FROM `score`
GROUP BY `c_id`
HAVING total > 5
ORDER BY total DESC, `c_id`;-- 44、检索至少选修两门课程的学生学号。
SELECT `s_id`,COUNT(*)
FROM `score`
GROUP BY `s_id`
HAVING COUNT(*) >= 2;-- 45、查询选修了全部课程的学生信息。
SELECT *
FROM `student`
WHERE `s_id` IN (SELECT `s_id`FROM `score`GROUP BY `s_id`HAVING COUNT(*) = (SELECT COUNT(*)FROM `course`));-- 46、查询各学生的年龄。
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一。
SELECT `s_birth`,(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`s_birth`, '%Y') -(IF(DATE_FORMAT(NOW(), '%m%d') > DATE_FORMAT(`s_birth`, '%m%d'),0,1))) AS age
FROM `student`;-- 47、查询本周过生日的学生。
SELECT *
FROM `student`
WHERE WEEK(DATE_FORMAT(NOW(), '%Y%m%d')) = WEEK(`s_birth`);SELECT *
FROM `student`
WHERE YEARWEEK(`s_birth`) = YEARWEEK(DATE_FORMAT(NOW(), '%Y%m%d'));SELECT WEEK(DATE_FORMAT(NOW(), '%Y%m%d'));-- 48、查询下周过生日的学生。
SELECT *
FROM `student`
WHERE WEEK(DATE_FORMAT(NOW(), '%Y%m%d')) + 1 = WEEK(`s_birth`);-- 49、查询本月过生日的学生。
SELECT *
FROM `student`
WHERE MONTH(DATE_FORMAT(NOW(), '%Y%m%d')) = MONTH(`s_birth`);-- 50、查询下月过生日的学生。
SELECT *
FROM `student`
WHERE MONTH(DATE_FORMAT(NOW(), '%Y-%m-%d')) + 1 = MONTH(`s_birth`);

-- 查询课程"01"比课程"02"成绩高的学生的信息及课程分数。
-- - in ~ 长型数据变成宽型数据。
SELECT st.*, t.s01, t.s02
FROM(SELECT sc.`s_id`,MAX(CASEWHEN sc.`c_id` = '01' THEN sc.`s_score`END) s01,MAX(CASEWHEN sc.`c_id` = '02' THEN sc.`s_score`END) s02FROM`score` scGROUP BY sc.`s_id`) t,`student` st
WHEREt.s01 > t.s02 AND t.`s_id` = st.`s_id`;-- 自连接。
SELECT st.*, sc1.`s_score`, sc2.`s_score`
FROM`student` st,`score` sc1,`score` sc2
WHEREst.`s_id` = sc1.`s_id`AND sc1.`s_id` = sc2.`s_id`AND sc1.`c_id` = '01'AND sc2.`c_id` = '02'AND sc1.`s_score` > sc2.`s_score`;-- 2、查询课程"01"比课程"02"成绩低的学生的信息及课程分数。
SELECT `student`.*, sc1.`s_score`, sc2.`s_score`
FROM`student`INNER JOIN`score` sc1 ON `student`.`s_id` = sc1.`s_id`AND sc1.`c_id` = '01'INNER JOIN`score` sc2 ON sc1.`s_id` = sc2.`s_id`AND sc2.`c_id` = '02'
WHEREsc1.`s_score` < sc2.`s_score`OR sc1.`s_score` IS NULL;-- 3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。
-- 子查询方法。
SELECT sc.`s_id`,(SELECT `s_name`FROM`student` stWHEREst.`s_id` = sc.`s_id`) AS s_name,AVG(sc.`s_score`) avg_score
FROM`score` sc
GROUP BY sc.`s_id`
HAVING avg_score >= 60;-- 两个表连接方法。
SELECT st.`s_id`, st.`s_name`, AVG(sc.`s_score`)
FROM`student` st,`score` sc
WHEREst.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING AVG(sc.`s_score` >= 60);-- 4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩。
-- （包括有成绩的和无成绩的）。
SELECT st.`s_id`, st.`s_name`, AVG(sc.`s_score`) AS avg_score
FROM`student` st,`score` sc
WHEREst.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`) < 60 
UNION SELECT st.`s_id`, st.`s_name`, 0 AS avg_score
FROM`student` st
WHEREst.`s_id` NOT IN (SELECT DISTINCT`s_id`FROM`score`);-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT st.`s_id`,st.`s_name`,COUNT(sc.s_id) AS sum_course,IFNULL(SUM(sc.`s_score`), 0) AS sum_score
FROM`student` stLEFT OUTER JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`;-- 6、查询"李"姓老师的数量。
SELECT COUNT(`t_id`)
FROM`teacher`
WHERE`t_name` LIKE '李%';-- 7、查询学过"张三"老师授课的同学的信息。
SELECT st.*
FROM`student` stJOIN`score` sc ON st.`s_id` = sc.`s_id`
WHEREsc.`c_id` IN (SELECT `c_id`FROM`course`WHERE`t_id` = (SELECT `t_id`FROM`teacher`WHERE`t_name` = '老师 1'));SELECT st.*
FROM`student` stINNER JOIN`score` sc ON st.`s_id` = sc.`s_id`
WHEREsc.`c_id` IN (SELECT `c_id`FROM`course` cINNER JOIN`teacher` t ON c.`t_id` = t.`t_id`WHERE`t`.`t_name` = '老师 1');-- 8、查询没学过"张三"老师授课的同学的信息。
SELECT *
FROM`student` st
WHEREst.s_id NOT IN (SELECT st.`s_id`FROM`student` stJOIN`score` sc ON st.`s_id` = sc.`s_id`WHEREsc.`c_id` IN (SELECT `c_id`FROM`course`WHERE`t_id` = (SELECT `t_id`FROM`teacher`WHERE`t_name` = '老师 1')));SELECT *
FROM`student`
WHERE`s_id` NOT IN (SELECT sc.`s_id`FROM`teacher` t,`score` sc,`course` cWHEREt.`t_id` = c.`t_id`AND c.`c_id` = sc.`c_id`AND t.`t_name` = '老师 1');SELECT *
FROM`student`
WHERENOT EXISTS( SELECT 1FROM(SELECT sc.`s_id`FROM`teacher` t, `score` sc, `course` cWHEREt.`t_id` = c.`t_id`AND c.`c_id` = sc.`c_id`AND t.`t_name` = '老师 1') tWHEREt.s_id = student.s_id);-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
SELECT st.*
FROM`student` st,`score` sc1,`score` sc2
WHEREst.`s_id` = sc1.`s_id`AND st.`s_id` = sc2.`s_id`AND sc1.`c_id` = '01'AND sc2.`c_id` = '02';-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
SELECT st.*
FROM`student` st
WHEREst.`s_id` IN (SELECT `s_id`FROM`score`WHERE`c_id` = '01')AND st.`s_id` NOT IN (SELECT `s_id`FROM`score`WHERE`c_id` = '02');SELECT st.*
FROM`student` st,(SELECT `s_id`,MAX(IF(`c_id` = '01', `s_score`, NULL)) s01,MAX(IF(`c_id` = '02', `s_score`, NULL)) s02FROM`score`GROUP BY `s_id`) t
WHEREt.`s_id` = st.`s_id`AND t.s01 IS NOT NULLAND t.s02 IS NULL;-- 11、查询没有学全所有课程的同学的信息。
SELECT st.*, COUNT(sc.`c_id`) count_sc
FROM`student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING COUNT(sc.`c_id`) < (SELECT COUNT(`c_id`)FROM`course`);SELECT st.*
FROM`student` st
WHEREst.`s_id` IN (SELECT `s_id`FROM`score`WHERE`s_id` NOT IN (SELECT sc1.`s_id`FROM`score` sc1JOIN`score` sc2 ON sc1.`s_id` = sc2.`s_id`AND sc2.`c_id` = '02'JOIN`score` sc3 ON sc1.`s_id` = sc3.`s_id`AND sc3.`c_id` = '03'WHEREsc1.`c_id` = '01'));-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
SELECT *
FROM`student`
WHERE`s_id` IN (SELECT DISTINCT`s_id`FROM`score`WHERE`c_id` IN (SELECT `c_id`FROM`score`WHERE`s_id` = '01'))
GROUP BY 1 , 2 , 3 , 4;
-- 第 1 2 3 4 字段。-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息。-- 创建 01 所学临时表。
CREATE TABLE s01_sc_temp AS SELECT t1.*, sc.`c_id` cid2 FROM(SELECT st.*, t2.`c_id`FROM`student` st, (SELECT `c_id`FROM`score`WHERE`s_id` = '01') t2) t1LEFT JOIN`score` sc ON t1.`s_id` = sc.`s_id`AND t1.`c_id` = sc.`c_id` 
UNION SELECT t.*, sc.`c_id` cid2
FROM(SELECT st.*, b.`c_id`FROM`student` st, (SELECT `c_id`FROM`score`WHERE`s_id` = '01') b) tRIGHT JOIN`score` sc ON t.`s_id` = sc.`s_id`AND t.`c_id` = sc.`c_id`;SELECT *
FROM`student`
WHERE`s_id` NOT IN (SELECT `s_id`FROMs01_s_tempWHEREcid2 IS NULL OR `c_id` IS NULL)AND `s_id` != '01';-- ~ ~ ~SELECT *
FROM`student`
WHERE`s_id` IN (SELECT DISTINCT`s_id`FROM`score`WHERE`s_id` != '01'AND `c_id` IN (SELECT `c_id`FROM`score`WHERE`s_id` = '01')GROUP BY `s_id`HAVING COUNT(1) = (SELECT COUNT(1)FROM`score`WHERE`s_id` = '01'));-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT st.`s_name`
FROM`student` st
WHEREst.`s_id` NOT IN (SELECT `s_id`FROM`score`WHERE`c_id` = (SELECT `c_id`FROM`course`WHERE`t_id` = (SELECT `t_id`FROM`teacher`WHERE`t_name` = '老师 1'))GROUP BY s_id);-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
SELECT st.`s_id`, st.`s_name`, AVG(sc.`s_score`) avg_score
FROM`student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;SELECT st.`s_id`, st.`s_name`, AVG(sc.`s_score`) avg_score
FROM`student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;SELECT st.`s_id`, st.`s_name`, ROUND(AVG(sc.`s_score`))
FROMstudent stLEFT JOINscore sc ON st.`s_id` = sc.`s_id`
WHEREst.`s_id` IN (SELECT `s_id`FROM`score`WHERE`s_score` < 60GROUP BY `s_id`HAVING COUNT(1) >= 2)
GROUP BY st.`s_id` , st.`s_name`;-- 16、检索课程"01"分数小于 60，按分数降序排列的学生信息。
SELECT st.*, sc.`s_score`
FROM`score` scRIGHT JOIN`student` st ON sc.`s_id` = st.`s_id`
WHEREsc.`c_id` = '01' AND sc.`s_score` < 60
ORDER BY `s_score` DESC;SELECT st.*, sc.`c_id`, sc.`s_score`
FROM`student` st,`score` sc
WHEREst.`s_id` = sc.`s_id`AND sc.`c_id` = '01'AND sc.`s_score` < 60
ORDER BY sc.`s_score` DESC;-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT sc.`s_id`,(SELECT `s_score`FROM `score`WHERE `s_id` = sc.`s_id`AND `c_id` = '01')    AS 语文,(SELECT `s_score`FROM `score`WHERE `s_id` = sc.`s_id`AND `c_id` = '02')    AS 数学,(SELECT `s_score`FROM `score`WHERE `s_id` = sc.`s_id`AND `c_id` = '03')    AS 英语,ROUND(AVG(`s_score`), 2) AS 平均分
FROM `score` sc
GROUP BY sc.`s_id`
ORDER BY 平均分 DESC;-- 18.查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
-- 及格为 >= 60，中等为：70 ~ 80，优良为：80 ~ 90，优秀为：>= 90。
SELECT sc.`c_id`,c.`c_name`,MAX(`s_score`),MIN(`s_score`),ROUND(AVG(`s_score`), 2),ROUND(100 * (SUM(IF(sc.`s_score` >= 60, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 及格率,ROUND(100 * (SUM(IF(sc.`s_score` >= 70 AND sc.`s_score` <= 80, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 中等率,ROUND(100 * (SUM(IF(sc.`s_score` >= 80 AND sc.`s_score` <= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 优良率,ROUND(100 * (SUM(IF(sc.`s_score` >= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),2) AS 优秀率
FROM `score` scLEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
GROUP BY sc.`c_id`, c.`c_name`;-- 19、按各科成绩进行排序，并显示排名（实现不完全）。
-- mysql 没有 rank(); 函数。
SELECT sc1.`c_id`,sc1.`s_id`,sc1.`s_score`,COUNT(sc2.`s_score`) + 1 AS `rank`
FROM`score` sc1LEFT JOIN`score` sc2 ON sc1.`s_score` < sc2.`s_score`AND sc1.`c_id` = sc2.`c_id`
GROUP BY sc1.`c_id` , sc1.`s_id` , sc1.`s_score`
ORDER BY sc1.`c_id` , `rank`;-- 20、查询学生的总成绩并进行排名。
SELECT t1.`s_id`,@i:=@i + 1 AS i,@k:=(IF(@score = t1.sum_score, @k, @i)) AS `rank`,@score:=t1.sum_score AS score
FROM(SELECT `s_id`, SUM(`s_score`) AS sum_scoreFROM`score`GROUP BY `s_id`ORDER BY sum_score DESC) t1,(SELECT @k:=0, @i:=0, @score:=0) AS kis;-- 21、查询不同老师所教不同课程平均分从高到低显示。
SELECT c.`t_id`,t.`t_name`,c.`c_id`,ROUND(AVG(`s_score`), 2) AS avg_score
FROM`course` cLEFT JOIN`score` sc ON c.`c_id` = sc.`c_id`LEFT JOIN`teacher` t ON c.`t_id` = t.`t_id`
GROUP BY c.`c_id` , c.`t_id` , t.`t_name`
ORDER BY avg_score DESC;-- 22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩。
SELECT d.*,si2.排名,si2.`s_score`,si2.`c_id`
FROM (SELECT sc.`s_id`,sc.`s_score`,sc.`c_id`,@i := @i + 1 AS 排名FROM `score` sc,(SELECT @i := 0) as i2WHERE sc.c_id = '01') as si2LEFT JOINstudent d ON si2.s_id = d.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT st.*,sj2.排名,sj2.s_score,sj2.c_id
FROM (SELECT sc.s_id,sc.s_score,sc.c_id,@j := @j + 1 AS 排名FROM `score` sc,(SELECT @j := 0) as j2WHERE sc.c_id = '02') as sj2LEFT JOIN`student` st ON sj2.s_id = st.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT d.*,s.排名,s.`s_score`,s.`c_id`
FROM (SELECT sc.`s_id`,sc.`s_score`,sc.`c_id`,@k := @k + 1 AS 排名FROM `score` sc,(SELECT @k := 0) as k2WHERE sc.`c_id` = '03') as sLEFT JOIN`student` d ON s.`s_id` = d.`s_id`
WHERE 排名 BETWEEN 2 AND 3;-- 23、统计各科成绩各分数段人数：课程编号, 课程名称, [100-85], [85-70], [70-60], [0-60] 及所占百分比。
SELECT DISTINCT c.`c_name`,sc.`c_id`,t1.`85-100`,t1.百分比,t2.`70-85`,t2.百分比,t3.`60-70`,t3.百分比,t4.`0-60`,t4.百分比
FROM `score` scLEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0))                              AS `85-100`,ROUND(100 * (SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t1 ON sc.`c_id` = t1.`c_id`LEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0))                              AS `70-85`,ROUND(100 * (SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t2 ON sc.`c_id` = t2.`c_id`LEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0))                              AS `60-70`,ROUND(100 * (SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t3 ON sc.`c_id` = t3.`c_id`LEFT JOIN(SELECT `c_id`,SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0))                              AS `0-60`,ROUND(100 * (SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0)) / COUNT(*)), 2) AS 百分比FROM `score`GROUP BY `c_id`) t4 ON sc.`c_id` = t4.`c_id`LEFT JOIN`course` c ON sc.`c_id` = c.`c_id`;-- 24、查询学生平均成绩及其名次。
SELECT t.`s_id`,@i := @i + 1                                 AS '不保留空缺排名',@k := (IF(@avg_score = t.avg_score, @k, @i)) AS '保留空缺排名',@avg_score := avg_score                      AS '平均分'
FROM (SELECT `s_id`,ROUND(AVG(`s_score`), 2) AS avg_scoreFROM scoreGROUP BY `s_id`) t,(SELECT @avg_score := 0, @i := 0, @k := 0) b;-- 25、查询各科成绩前三名的记录。
-- - 选出 sc1 表比 sc2 表成绩大的所有组。
-- - 选出比当前 id 成绩大的小于三个的。
SELECT sc1.`s_id`, sc1.`c_id`, sc1.`s_score`
FROM`score` sc1LEFT JOIN`score` sc2 ON sc1.`c_id` = sc2.`c_id`AND sc1.`s_score` < sc2.`s_score`
GROUP BY sc1.`s_id` , sc1.`c_id` , sc1.`s_score`
HAVING COUNT(sc2.`s_id`) < 3
ORDER BY sc1.`c_id` , sc1.`s_score` DESC;-- 26、查询每门课程被选修的学生数。
SELECT `c_id`, COUNT(`s_id`)
FROM`score`
GROUP BY `c_id`;-- 27、查询出只有两门课程的全部学生的学号和姓名。
SELECT `s_id`, `s_name`
FROM`student`
WHERE`s_id` IN (SELECT `s_id`FROM`score`GROUP BY `s_id`HAVING COUNT(`c_id`) = 2);-- 28、查询男生、女生人数。
SELECT `s_sex`, COUNT(`s_sex`) AS 人数
FROMstudent
GROUP BY s_sex;-- 29、查询名字中含有"风"字的学生信息。
SELECT *
FROM`student`
WHERE`s_name` LIKE '%风%';-- 30、查询同名同性学生名单，并统计同名人数。
SELECT st1.`s_name`, st1.`s_sex`, COUNT(*)
FROM`student` st1JOIN`student` st2 ON st1.`s_id` != st2.`s_id`AND st1.`s_name` = st2.`s_name`AND st1.`s_sex` = st2.`s_sex`
GROUP BY st1.`s_name` , st1.`s_sex`;-- 31、查询 1990 年出生的学生名单。
SELECT `s_name`
FROM`student`
WHERE`s_birth` LIKE '1990%';-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT `c_id`, ROUND(AVG(`s_score`), 2) AS avg_score
FROM`score`
GROUP BY `c_id`
ORDER BY avg_score DESC , `c_id` ASC;-- 33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩。
SELECT sc.`s_id`,st.`s_name`,ROUND(AVG(sc.`s_score`), 2) AS avg_score
FROM`score` scLEFT JOIN`student` st ON sc.`s_id` = st.`s_id`
GROUP BY `s_id`
HAVING avg_score >= 85;-- 34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数。
SELECT st.`s_name`, sc.`s_score`
FROM`score` scLEFT JOIN`student` st ON st.`s_id` = sc.`s_id`
WHEREsc.`c_id` = (SELECT `c_id`FROM`course`WHERE`c_name` = '数学')AND sc.s_score < 60;-- 35、查询所有学生的课程及分数情况。
SELECT st.`s_id`,st.`s_name`,SUM(IF(c.`c_name` = '语文', sc.`s_score`, 0)) AS '语文',SUM(IF(c.`c_name` = '数学', sc.`s_score`, 0)) AS '数学',SUM(IF(c.`c_name` = '英语', sc.`s_score`, 0)) AS '英语',SUM(sc.`s_score`)                             AS '总分'
FROM `student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`LEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
GROUP BY st.`s_id`, st.`s_name`;-- 36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数。
SELECT st.`s_name`, c.`c_name`, sc.`s_score`
FROM`course` cLEFT JOIN`score` sc ON c.`c_id` = sc.`c_id`LEFT JOIN`student` st ON st.`s_id` = sc.`s_id`
WHEREsc.`s_score` >= 70;-- 37、查询不及格的课程。
SELECT sc.`s_id`, sc.`c_id`, c.`c_name`, sc.`s_score`
FROM`score` scLEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
WHEREsc.`s_score` < 60;-- 38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名。
SELECT sc.`s_id`, st.`s_name`
FROM`score` scLEFT JOIN`student` st ON sc.`s_id` = st.`s_id`
WHEREsc.`c_id` = '01' AND sc.`s_score` > 80;-- 39、求每门课程的学生人数。
SELECT COUNT(*)
FROM`score`
GROUP BY `c_id`;-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
-- 查询老师 id。
SELECT `c_id`
FROM`course` c,`teacher` t
WHEREc.`t_id` = t.`t_id`AND t.`t_name` = '老师 1';
-- 查询最高分（可能有相同分数）。
SELECT MAX(`s_score`)
FROM`score`
WHERE`c_id` = '02';
-- 查询信息。
SELECT st.*, sc.`s_score`, sc.`c_id`, c.`c_name`
FROM`student` stLEFT JOIN`score` sc ON st.`s_id` = sc.`s_id`LEFT JOIN`course` c ON sc.`c_id` = c.`c_id`
WHEREsc.`c_id` = (SELECT `c_id`FROM`course` c,`teacher` tWHEREc.`t_id` = t.`t_id`AND t.`t_name` = '老师 1')AND sc.s_score IN (SELECT MAX(s_score)FROMscoreWHEREc_id = '02');SELECT c.`c_name`, sc.`s_score`, st.*
FROM`course` c,`score` sc,`teacher` t,`student` st
WHEREt.`t_id` = c.`t_id`AND c.`c_id` = sc.`c_id`AND st.`s_id` = sc.`s_id`AND t.`t_name` = '老师 1'AND sc.s_score IN (SELECT MAX(s_score)FROMcourse,score,teacher,studentWHEREteacher.t_id = course.t_idAND course.c_id = score.c_idAND student.s_id = score.s_idAND teacher.t_name = '老师 1');-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
SELECT DISTINCTsc2.`s_id`, sc2.`c_id`, sc2.`s_score`
FROM`score` sc1,`score` sc2
WHEREsc1.`c_id` != sc2.`c_id`AND sc1.`s_score` = sc2.`s_score`;-- 42、查询每门功成绩最好的前两名。
-- 牛逼的写法。
SELECT sc1.`s_id`, sc1.`c_id`, sc1.`s_score`
FROM`score` sc1
WHERE(SELECT COUNT(1)FROM`score` sc2WHEREsc2.`c_id` = sc1.`c_id`AND sc2.`s_score` >= sc1.`s_score`) <= 2
ORDER BY sc1.`c_id`;-- 43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT `c_id`, COUNT(*) AS total
FROM`score`
GROUP BY `c_id`
HAVING total > 5
ORDER BY total DESC , `c_id`;-- 44、检索至少选修两门课程的学生学号。
SELECT `s_id`, COUNT(*)
FROM`score`
GROUP BY `s_id`
HAVING COUNT(*) >= 2;-- 45、查询选修了全部课程的学生信息。
SELECT *
FROM`student`
WHERE`s_id` IN (SELECT `s_id`FROM`score`GROUP BY `s_id`HAVING COUNT(*) = (SELECT COUNT(*)FROM`course`));-- 46、查询各学生的年龄。
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一。
SELECT `s_birth`,(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`s_birth`, '%Y') - (IF(DATE_FORMAT(NOW(), '%m%d') > DATE_FORMAT(`s_birth`, '%m%d'),0,1))) AS age
FROM`student`;-- 47、查询本周过生日的学生。
SELECT *
FROM`student`
WHEREWEEK(DATE_FORMAT(NOW(), '%Y%m%d')) = WEEK(`s_birth`);SELECT *
FROM`student`
WHEREYEARWEEK(`s_birth`) = YEARWEEK(DATE_FORMAT(NOW(), '%Y%m%d'));SELECT WEEK(DATE_FORMAT(NOW(), '%Y%m%d'));-- 48、查询下周过生日的学生。
SELECT *
FROM`student`
WHEREWEEK(DATE_FORMAT(NOW(), '%Y%m%d')) + 1 = WEEK(`s_birth`);-- 49、查询本月过生日的学生。
SELECT *
FROM`student`
WHEREMONTH(DATE_FORMAT(NOW(), '%Y%m%d')) = MONTH(`s_birth`);-- 50、查询下月过生日的学生。
SELECT *
FROM`student`
WHEREMONTH(DATE_FORMAT(NOW(), '%Y-%m-%d')) + 1 = MONTH(`s_birth`);

MySQL 50 题。

MySQL 50 题。文章目录 MySQL 50 题。数据库。sql。数据库。 CREATE SCHEMA new_schema DEFAULT CHARACTER SET utf8mb4 ;Operation failed: There was an error while applying the SQL script to the database. Executing: CREATE SCHEMA new_schema DEFAULT CHARACTER SE…...

编程日记 2023/9/6 22:17:55

强化学习算法总结 (1)

强化学习算法总结 (1) 1.综述强化学习是通过与环境进行交互，来实现目标的一种计算方法。 s − a 1 − r − s ′ s - a_1 - r- s s−a1−r−s′ 1.1强化学习优化目标 p o l i c y a r g m a x p o l i c y E ( a , s ) [ r e w a r d ( s , a ) ] policy ar…...

编程日记 2023/9/6 22:16:53

Qt应用开发(基础篇)——向导对话框 QWizard

一、前言 QWizard类继承于QDialog，为有向导界面需求的应用环境提供了一个框架。对话框窗口 QDialog QWizard向导对话框是一个拥有队列界面的特殊对话框，向导的目的是引导用户一步一步的完成预设的流程。向导常用于软件安装界面向导、硬件线路安装向导、…...

编程日记 2023/9/6 22:15:52

Python类的方法

Python类的方法主要分为实例方法、类方法和静态方法三种。 1 实例方法以self作为第一个参数的方法，就是类的实例方法。该方法由类的实例调用，Python会把调用该方法的实例对象传递给self。如下代码定义了一个名为A的类。 class A:def __init__(self…...

编程日记 2023/9/6 22:14:51

变电站自动化监控系统

力安科技变电站自动化监控系统是以箱式变电站为管理对象，加装箱变网关，在完成箱变智能化改造的基础上，依托电易云，构建一体化智慧箱变及运维系统。智能箱式变电站被广泛应用于住宅小区、城市公用变压器、工厂、商场、机场、电站等…...

编程日记 2023/9/6 22:13:50

MySql学习笔记11——DBA命令介绍

DBA命令数据导入要进入Mysql 创建数据库 create database database_name;使用数据库 use database_name;初始化数据库 source .sql文件地址，不能加双引号；数据导出要在windows的dos环境下进行导出数据库 mysqldump database_name > 存放…...

编程日记 2023/9/6 22:12:50

Webpack 复习小结

nodejs学习参考 node常用命令： node xxx.js 执行js文件 npm init -y 初始化package.json npm i 软件包名下载软件包到本地 npm i 软件包名 -g 下载软件包到全局 npm uni 软件包名删除软件包系统优化CDN使用 CDN for free 需求：开发模式使用本地第三…...

编程日记 2023/9/6 22:11:49

Laravel chunk和chunkById的坑

在编写定时任务脚本的时候，经常会用到chunk和chunkById的API。一、前言数据库引擎为innodb。表结构简述，只列出了本文用到的字段。字段类型注释idint(11)IDtypeint(11)类型mark_timeint(10)标注时间（时间戳） 索引&#x…...

编程日记 2023/9/6 22:10:48

从零开始学习 Java：简单易懂的入门指南之泛型及set集合（二十二）

泛型及set集合扩展 1.泛型1.1泛型概述 2.Set集合2.1Set集合概述和特点【应用】2.2Set集合的使用【应用】 3.TreeSet集合3.1TreeSet集合概述和特点【应用】3.2TreeSet集合基本使用【应用】3.3自然排序Comparable的使用【应用】3.4比较器排序Comparator的使用【应用】3.5两种比较…...

编程日记 2023/9/6 22:09:47

JVM----GC（垃圾回收）详解

一、Automatic Garbage Collection（垃圾回收）简介 Automatic Garbage Collection （自动垃圾回收）是JVM的一个特性，JVM会启动相关的线程，该线程会轮训检查heap memeory，并确定哪些是未被引用的(…...

编程日记 2023/9/6 22:08:46

数据库的三个范式

数据库的三个范式是关系数据库设计中的一组规范，用于确保数据的有效性和一致性。这三个范式分别是： 第一范式（1NF）：要求数据库表中的每一列都是不可分割的原子值。换句话说，每个表中的每个字段不能包含多个…...

编程日记 2023/9/6 22:07:45

谷歌浏览器打开白屏后台还有还有很多google chrome进程在运行

环境： Win10 专业版谷歌浏览器版本 116.0.5845.141（正式版本） （64 位） L盾加密终端问题描述： 谷歌浏览器打开白屏后台还有还有很多google chrome进程在运行，要全部结束谷歌浏览器进程&…...

编程日记 2023/9/6 22:06:45

Java EE 突击 15 - Spring Boot 统一功能处理

Spring Boot 统一功能处理一 . 统一功能的处理1.1 初级阶段 : 不断重复1.2 中级阶段 : 集成方法1.3 高级阶段 : Spring AOP1.4 超高级阶段 : Spring 拦截器准备工作实现拦截器自定义拦截器将自定义拦截器加入到系统配置拦截器实现原理扩展 : 统一访问前缀添加二 . 统一异常的…...

编程日记 2023/9/6 22:05:44

JasperReport定义变量后打印PDF变量为null以及整个pdf文件为空白

问题1: JasperReport打印出来的整个pdf文件为空白文件； 问题2：JasperReport定义变量后打印PDF变量为null； 问题1原因是因为缺少数据源JRDataSource JasperFillManager.fillReport(jasperReport, params,new JREmptyDataSource());如果你打印…...

编程日记 2023/9/6 22:04:42

Python 及 Pycharm 的安装 2023.8

Python 及 PyCharm 的安装仅适用于 Windows 系统！ 视频教程：【Python及Pycharm的安装 2023.8】 https://www.bilibili.com/video/BV1A34y1T7Gu 文章目录 Python 及 PyCharm 的安装安装 Python安装 PyCharmHi, PyCharmPyCharm 汉化安装 Python 进入 …...

编程日记 2023/9/6 22:03:42

java中的线程中断

java中的线程中断 1、线程中断即线程的取消/关闭的机制2、线程对中断interrupt()的反应2.1、RUNNABLE：线程在运行或具备运行条件只是在等待操作系统调度2.2、WAITING/TIMED_WAITING：线程在等待某个条件或超时2.3、BLOCKED：线程在等待锁&…...

编程日记 2023/9/6 22:02:41

【跟小嘉学 Rust 编程】二十三、Cargo 使用指南

系列文章目录【跟小嘉学 Rust 编程】一、Rust 编程基础【跟小嘉学 Rust 编程】二、Rust 包管理工具使用【跟小嘉学 Rust 编程】三、Rust 的基本程序概念【跟小嘉学 Rust 编程】四、理解 Rust 的所有权概念【跟小嘉学 Rust 编程】五、使用结构体关联结构化数据【跟小嘉学…...

编程日记 2023/9/6 22:01:40

R Removing package报错(as ‘lib’ is unspecified)

remove.packages(ggpubr) Removing package from ‘/Library/Frameworks/R.framework/Versions/4.0/Resources/library’ (as ‘lib’ is unspecified) 解决办法： > .libPaths() [1] "/Library/Frameworks/R.framework/Versions/4.0/Resources/library&qu…...

编程日记 2023/9/6 22:00:39

金融信创，软件规划需关注自主安全及生态建设

软件信创化，就是信息技术软件应用创新发展的意思（简称为“信创”）。相信在中国，企业对于“信创化”这个概念并不陌生。「国强则民强」，今年来中国经济的快速发展，受到了各大欧美强国的“卡脖子”操作的影…...

编程日记 2023/9/6 21:59:38

无重叠区间【贪心算法】

无重叠区间给定一个区间的集合 intervals ，其中 intervals[i] [starti, endi] 。返回需要移除区间的最小数量，使剩余区间互不重叠。 class Solution {public int eraseOverlapIntervals(int[][] intervals) {//先排序，按照左边界升序,注…...

编程日记 2023/9/6 21:58:37

简易版抽奖活动的设计技术方案

1.前言本技术方案旨在设计一套完整且可靠的抽奖活动逻辑，确保抽奖活动能够公平、公正、公开地进行，同时满足高并发访问、数据安全存储与高效处理等需求，为用户提供流畅的抽奖体验，助力业务顺利开展。本方案将涵盖抽奖活动的整体架构设计、核心流程逻辑、关键功能实现以及…...

编程新知 2025/8/22 3:01:43

什么是库存周转？如何用进销存系统提高库存周转率？

你可能听说过这样一句话： “利润不是赚出来的，是管出来的。” 尤其是在制造业、批发零售、电商这类“货堆成山”的行业，很多企业看着销售不错，账上却没钱、利润也不见了，一翻库存才发现： 一堆卖不动的旧货…...

编程新知 2025/8/22 5:05:48

页面渲染流程与性能优化

页面渲染流程与性能优化详解（完整版） 一、现代浏览器渲染流程（详细说明） 1. 构建DOM树浏览器接收到HTML文档后，会逐步解析并构建DOM（Document Object Model）树。具体过程如下： (…...

编程新知 2025/8/10 22:32:47

Mac下Android Studio扫描根目录卡死问题记录

环境信息操作系统: macOS 15.5 (Apple M2芯片)Android Studio版本: Meerkat Feature Drop | 2024.3.2 Patch 1 (Build #AI-243.26053.27.2432.13536105, 2025年5月22日构建) 问题现象在项目开发过程中，提示一个依赖外部头文件的cpp源文件需要同步，点…...

编程新知 2025/8/22 3:24:31

以光量子为例，详解量子获取方式

光量子技术获取量子比特可在室温下进行。该方式有望通过与名为硅光子学（silicon photonics）的光波导（optical waveguide）芯片制造技术和光纤等光通信技术相结合来实现量子计算机。量子力学中，光既是波又是粒子。光子本…...

编程新知 2025/8/25 13:28:36

使用Spring AI和MCP协议构建图片搜索服务

目录使用Spring AI和MCP协议构建图片搜索服务引言技术栈概览项目架构设计架构图服务端开发 1. 创建Spring Boot项目 2. 实现图片搜索工具 3. 配置传输模式 Stdio模式（本地调用） SSE模式（远程调用） 4. 注册工具提…...

编程新知 2025/8/22 5:17:58

DeepSeek源码深度解析 × 华为仓颉语言编程精粹——从MoE架构到全场景开发生态

前言在人工智能技术飞速发展的今天，深度学习与大模型技术已成为推动行业变革的核心驱动力，而高效、灵活的开发工具与编程语言则为技术创新提供了重要支撑。本书以两大前沿技术领域为核心，系统性地呈现了两部深度技术著作的精华：…...

编程新知 2025/8/22 6:01:32

stm32wle5 lpuart DMA数据不接收

配置波特率9600时，需要使用外部低速晶振...

编程新知 2025/8/26 15:30:23

【Linux】Linux安装并配置RabbitMQ

目录 1. 安装 Erlang 2. 安装 RabbitMQ 2.1.添加 RabbitMQ 仓库 2.2.安装 RabbitMQ 3.配置 3.1.启动和管理服务 4. 访问管理界面 5.安装问题 6.修改密码 7.修改端口 7.1.找到文件 7.2.修改文件 1. 安装 Erlang 由于 RabbitMQ 是用 Erlang 编写的，需要先安…...

编程新知 2025/8/26 0:07:41

sshd代码修改banner

sshd服务连接之后会收到字符串： SSH-2.0-OpenSSH_9.5 容易被hacker识别此服务为sshd服务。是否可以通过修改此banner达到让人无法识别此服务的目的呢？ 不能。因为这是写的SSH的协议中的。也就是协议规定了banner必须这么写。 SSH- 开头&#xff0c…...

编程新知 2025/8/23 11:31:57

MySQL 50 题。

文章目录

数据库。

sql。

相关文章：