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

MySQL 50 题。

news 文章来源：https://blog.csdn.net/lyfGeek/article/details/132656869 2025/5/5 10:31:21

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

nlp系列（7）实体识别（Bert）pytorch

模型介绍本项目是使用Bert模型来进行文本的实体识别。 Bert模型介绍可以查看这篇文章：nlp系列（2）文本分类（Bert）pytorch_bert文本分类_牧子川的博客-CSDN博客模型结构 Bert模型的模型结构： 数据介绍 …...

编程日记 2023/9/6 21:57:36

Uniapp学习之从零开始写一个简单的小程序demo（新建页面，通过导航切换页面，发送请求）

先把官网文档摆在这，后面会用到的 [uniapp官网文档]: https://uniapp.dcloud.net.cn/vernacular.html# 一、开发工具准备 1-1 安装HBuilder 按照官方推荐，先装一个HBuilder 下载地址： https://www.dcloud.io/hbuilderx.html1-2 安装微信开…...

编程日记 2023/9/6 21:56:35

uniapp微信小程序隐私保护引导新规

1.components中新建组件PrivacyPop.vue <template><view class"privacy" v-if"showPrivacy"><view class"content"><view class"title">隐私保护指引</view><view class"des">在使用当…...

编程日记 2023/9/6 21:55:34

超图嵌入论文阅读2：超图神经网络

超图嵌入论文阅读2：超图神经网络原文：Hypergraph Neural Networks ——AAAI2019（CCF-A） 源码：https://github.com/iMoonLab/HGNN 500star 概述贡献：用于数据表示学习的超图神经网络 (HGNN) 框架&#xf…...

编程日记 2023/9/6 21:54:33

安全运营中心（SOC）技术框架

2018年曾经画过一个安全运营体系框架，基本思路是在基础单点技术防护体系基础上，围绕着动态防御、深度分析、实时检测，建立安全运营大数据分析平台，可以算作是解决方案产品的思路。依据这个体系框架，当时写了《基于主动…...

编程日记 2023/9/6 21:53:32

并行和并发的区别

从操作系统的角度来看，线程是CPU分配的最小单位。并行就是同一时刻，两个线程都在执行。这就要求有两个CPU去分别执行两个线程。并发就是同一时刻，只有一个执行，但是一个时间段内，两个线程都执行了。并发的实现依赖于…...

编程日记 2023/9/6 21:52:31

GPT转换工具：轻松将MBR转换为GPT磁盘

为什么需要将MBR转换为GPT？ 众所周知，Windows 11已经发布很长时间了。在此期间，许多老用户已经从Windows 10升级到Windows 11。但有些用户仍在运行Windows 10。对于那些想要升级到Win 11的用户来说，他们可能不确定Win 11应该使…...

编程日记 2023/9/6 21:51:29

大模型参数高效微调技术原理综述（二）-BitFit、Prefix Tuning、Prompt Tuning

随着，ChatGPT 迅速爆火，引发了大模型的时代变革。然而对于普通大众来说，进行大模型的预训练或者全量微调遥不可及。由此，催生了各种参数高效微调技术，让科研人员或者普通开发者有机会尝试微调大模型。因此&#xff0c…...

编程日记 2023/9/6 21:50:28

将conda环境打包成docker步骤

1. 第一步，将conda环境的配置导出到environment.yml 要获取一个Conda环境的配置文件 environment.yml，你可以使用以下命令从已存在的环境中导出： conda env export --name your_env_name > environment.yml请将 your_env_name 替换为你要…...

编程日记 2023/9/6 21:49:27

C# 获取Json对象中指定属性的值

在C#中获取JSON对象中指定属性的值，可以使用Newtonsoft.JSON库的JObject类 using Newtonsoft.Json.Linq; using System; public class Program { public static void Main(string[] args) { string json "{ Name: John, age: 30, City: New York }"; …...

编程日记 2023/9/6 21:48:26

MySQL 50 题。

文章目录

数据库。

sql。

相关文章：