前言：
记录一下sql学习，仅供参考基本都对了，不排除有些我做的太快做错了。里面sql不存在任何sql优化操作，只以完成最后输出结果为目的，包含我做题过程和思路最后一行才是结果。
1.过程:
1.1.插入数据
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 8.0.32 : Database - test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `test`;
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` varchar(20) NOT NULL,
`c_name` varchar(20) NOT NULL DEFAULT '',
`t_id` varchar(20) NOT NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `course` */
insert into `course`(`c_id`,`c_name`,`t_id`) values
('01','语文','02'),
('02','数学','01'),
('03','英语','03'),
('04','化学','04'),
('05','物理','05'),
('06','生物','06');
/*Table structure for table `dept` */
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) NOT NULL COMMENT '部门名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门表';
/*Data for the table `dept` */
insert into `dept`(`id`,`name`) values
(1,'研发部'),
(2,'市场部'),
(3,'财务部'),
(4,'销售部'),
(5,'总经办'),
(6,'人事部');
/*Table structure for table `score` */
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`s_id` varchar(20) NOT NULL,
`c_id` varchar(20) NOT NULL DEFAULT '',
`s_score` int DEFAULT NULL,
PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `score` */
insert into `score`(`s_id`,`c_id`,`s_score`) values
('01','01',80),
('01','02',90),
('01','03',99),
('02','01',70),
('02','02',60),
('02','03',80),
('03','01',80),
('03','02',80),
('03','03',80),
('04','01',50),
('04','02',30),
('04','03',20),
('05','01',76),
('05','02',87),
('06','01',31),
('06','03',34),
('07','02',89),
('07','03',98);
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) NOT NULL,
`s_name` varchar(20) NOT NULL DEFAULT '',
`s_brith` varchar(20) NOT NULL DEFAULT '',
`s_sex` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `student` */
insert into `student`(`s_id`,`s_name`,`s_brith`,`s_sex`) values
('01','赵雷','1990-01-01','男'),
('02','钱电','1990-12-21','男'),
('03','孙风','1990-05-20','男'),
('04','李云','1990-08-06','男'),
('05','周梅','1991-12-01','女'),
('06','吴兰','1992-03-01','女'),
('07','郑竹','1989-07-01','女'),
('08','王菊','1990-01-20','女'),
('09','lpq','2002-**-**','女'),
('10','lpq','2002-**-**','女');
/*Table structure for table `teacher` */
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` varchar(20) NOT NULL,
`t_name` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `teacher` */
insert into `teacher`(`t_id`,`t_name`) values
('01','张三'),
('02','李四'),
('03','王五');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
1.2.解题过程
USE test;
//查询01课程比02课程成绩高的学生
SELECT * FROM score s WHERE s.c_id='01';
SELECT * FROM score s WHERE s.c_id='02';
SELECT * FROM student st LEFT JOIN (SELECT * FROM score s WHERE s.c_id='01') AS s1 ON s1.s_id=st.s_id LEFT JOIN
(SELECT * FROM score s WHERE s.c_id='02') AS s2 ON st.s_id=s2.s_id WHERE s1.s_score>s2.s_score;
//查询01课程比02课程成绩低的学生信息及课程分数
SELECT * FROM student st LEFT JOIN (SELECT * FROM score s WHERE s.c_id='01') AS s1 ON s1.s_id=st.s_id LEFT JOIN
(SELECT * FROM score s WHERE s.c_id='02') AS s2 ON st.s_id=s2.s_id WHERE s1.s_score<s2.s_score;
//查询平均分数大于等于60的同学的学生编号和学生姓名和平均成绩
SELECT s.s_id,AVG(s_score) AS _avg FROM score s GROUP BY s.s_id HAVING AVG(s.s_score)>=60;
SELECT st.*,s1._avg FROM student st JOIN (SELECT s.s_id,AVG(s_score) AS _avg FROM score s GROUP BY s.s_id HAVING AVG(s.s_score)>=60) AS s1
ON st.s_id=s1.s_id;
//查询平均成绩小于60的同学学生编号和学生姓名和平均成绩（包括有成绩和无成绩的）难度***
SELECT s.s_id,AVG(s_score) AS _avg FROM score s GROUP BY s.s_id HAVING AVG(s.s_score)<60;
SELECT * FROM student st LEFT JOIN (SELECT s.s_id,AVG(s_score) AS _avg FROM score s GROUP BY s.s_id HAVING AVG(s.s_score)) AS s1 ON
s1.s_id=st.s_id WHERE s1._avg<60 OR s1._avg IS NULL;
//查询所有同学的学生编号、学生姓名、选课总数、所有课程总成绩
SELECT st.*,st2._sum,st2._count FROM student st LEFT JOIN (SELECT SUM(s.s_score) AS _sum,s.s_id,COUNT(*) AS _count FROM score s GROUP BY s.s_id)AS st2 ON st2.s_id=st.s_id; 
//查询李性老师的数量
SELECT COUNT(*) FROM teacher t WHERE t.t_name LIKE '%李%'
//查询学过张三老师课程学生
SELECT st.* FROM score s JOIN course c ON s.c_id=c.c_id JOIN student st ON st.s_id=s.s_id WHERE c.t_id=(SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'); 
//查询没学过张三老师课程学生
//1.先查询学过的
SELECT st.s_id FROM student st JOIN score s ON st.s_id=s.s_id JOIN course c ON c.c_id=s.c_id JOIN teacher t ON t.t_id=c.t_id WHERE c.t_id IN
(SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三');
//取反
SELECT * FROM student st WHERE st.s_id NOT IN(SELECT st.s_id FROM student st JOIN score s ON st.s_id=s.s_id JOIN course c ON c.c_id=s.c_id JOIN teacher t ON t.t_id=c.t_id WHERE c.t_id IN
(SELECT t.t_id FROM teacher AS t WHERE t.t_name='张三'))
剩下的就不一个个贴了。
//查询学过01和02课程学生信息
SELECT * FROM score s WHERE s.c_id='01';
SELECT * FROM score s WHERE s.c_id='02';
SELECT st.* FROM (SELECT * FROM score s WHERE s.c_id='01') st1 JOIN (SELECT * FROM score s WHERE s.c_id='02')
st2 ON st1.s_id=st2.s_id JOIN student st ON st.s_id =st1.s_id;
//查询学过01但是没学过02的 有点难度***
SELECT st.* FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='01' AND s.s_id NOT IN (SELECT s.s_id FROM score s WHERE s.c_id='02')
//查询没有学会全部课程的
SELECT s.s_id,COUNT(*) AS _count FROM score s GROUP BY s.s_id HAVING COUNT(*)!=3;
SELECT st.*,s1._count FROM student AS st LEFT JOIN (SELECT s.s_id,COUNT(*) AS _count FROM score s GROUP BY s.s_id) AS s1 ON
st.s_id=s1.s_id WHERE s1._count IS NULL OR s1._count!=3;
//查询和01号同学学习完全相同的其他同学的信息
SELECT s.c_id FROM score s WHERE s.s_id='01';
SELECT st.*,COUNT(*) FROM score s JOIN student st ON st.s_id=s.s_id
WHERE s.c_id IN (SELECT s.c_id FROM score s WHERE s.s_id='01') AND s.s_id!='01' GROUP BY s.s_id HAVING COUNT(*)=3;
//查询没学过张三老师讲授的任何一门课程的学生
SELECT c.c_id FROM teacher t JOIN course c ON c.t_id=t.t_id WHERE t.t_name='张三'
SELECT s.s_id FROM score s WHERE s.c_id =(SELECT c.c_id FROM teacher t JOIN course c ON c.t_id=t.t_id WHERE t.t_name='张三') GROUP BY
s.s_id;
SELECT * FROM student st WHERE st.s_id NOT IN (SELECT s.s_id FROM score s WHERE s.c_id =(SELECT c.c_id FROM teacher t JOIN course c ON c.t_id=t.t_id WHERE t.t_name='张三') GROUP BY
s.s_id);
//查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.s_id AS _count FROM score s WHERE s.s_score<60 GROUP BY s_id;
SELECT st.*,AVG(s.s_score) FROM student st JOIN score s ON st.s_id=s.s_id WHERE st.s_id IN (SELECT s.s_id AS _count FROM score s WHERE s.s_score<60 GROUP BY s_id)
GROUP BY st.s_id;
//检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT st.* FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='01' AND s.s_score<60 ORDER BY s.s_score DESC;
//按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT st.s_id,s.s_score AS 语文 FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='01';
SELECT st.s_id,s.s_score AS 数学 FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='02';
SELECT st.s_id,s.s_score AS 英语 FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='03';
SELECT st.*,IFNULL(s1.语文,0) AS 语文,IFNULL(s2.数学,0) AS 数学,IFNULL(s3.英语,0) AS 英语, (IFNULL(s1.语文, 0) + IFNULL(s2.数学, 0) + IFNULL(s3.英语, 0)) / 3 AS 平均分 FROM student st LEFT JOIN (SELECT st.s_id,s.s_score AS 语文 FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='01') AS s1
ON st.s_id=s1.s_id LEFT JOIN (SELECT st.s_id,s.s_score AS 数学 FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='02') AS s2
ON st.s_id=s2.s_id LEFT JOIN (SELECT st.s_id,s.s_score AS 英语 FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.c_id='03') AS s3
ON st.s_id=s3.s_id
//查询各科成绩最高分、最低分和平均分 //这题和标准答案不一样我随便写写，这种用代码写更好sql性能太消耗了
SELECT s.c_id,MAX(s.s_score)AS _max,MIN(s.s_score) AS _min,AVG(s.s_score) AS _avg FROM score s GROUP BY s.c_id;
//按各科成绩进行排序，并显示排名
SELECT s.s_id,RANK() OVER(ORDER BY s.s_score DESC) FROM score s WHERE s.c_id='01'
SELECT s.s_id,RANK() OVER(ORDER BY s.s_score DESC) FROM score s WHERE s.c_id='02'
SELECT s.s_id,RANK() OVER(ORDER BY s.s_score DESC) FROM score s WHERE s.c_id='03'
SELECT st.*,s1.语文,s2.数学,s3.英语 FROM student st LEFT JOIN (SELECT s.s_id,RANK() OVER(ORDER BY s.s_score DESC) AS 语文 FROM score s WHERE s.c_id='01') AS s1 ON
s1.s_id=st.s_id LEFT JOIN (SELECT s.s_id,RANK() OVER(ORDER BY s.s_score DESC) AS 数学 FROM score s WHERE s.c_id='02') AS s2 ON
s2.s_id=st.s_id LEFT JOIN (SELECT s.s_id,RANK() OVER(ORDER BY s.s_score DESC) AS 英语 FROM score s WHERE s.c_id='03') AS s3 ON
s3.s_id=st.s_id;
//查询学生的总成绩并进行排名
SELECT SUM(s.s_score) AS _sum FROM score s GROUP BY s.s_id;
SELECT st.*,IFNULL(s2._sum,0) FROM student st LEFT JOIN (SELECT SUM(s.s_score) AS _sum,s.s_id FROM score s GROUP BY s.s_id) s2 ON s2.s_id=st.s_id ORDER BY _sum DESC;
//查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_name,s.c_id,AVG(s.s_score) AS _avg FROM score s JOIN course c ON c.c_id=s.c_id JOIN teacher t ON t.t_id=c.t_id GROUP BY s.c_id ORDER BY
_avg DESC;
//查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
//错误示例 ORDER BY limit只能在语句最后面 但是写成子查询c
SELECT * FROM score s WHERE s.c_id='01' ORDER BY s.s_score DESC LIMIT 1,2 UNION ALL
SELECT * FROM score s WHERE s.c_id='02' ORDER BY s.s_score DESC LIMIT 1,2 UNION ALL
SELECT * FROM score s WHERE s.c_id='03' ORDER BY s.s_score DESC LIMIT 1,2
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='01' ORDER BY s.s_score DESC LIMIT 1,2) AS s1 UNION ALL
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='02' ORDER BY s.s_score DESC LIMIT 1,2) AS s2 UNION ALL
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='03' ORDER BY s.s_score DESC LIMIT 1,2) AS s3
//统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
pass 程序更容易
//查询学生平均成绩及其名次
SELECT st.*,RANK() OVER(ORDER BY AVG(s.s_score) DESC),AVG(s.s_score) FROM score s JOIN student st ON st.s_id=s.s_id GROUP BY s.s_id;
//查询各科成绩前三名的记录
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='01' ORDER BY s.s_score DESC LIMIT 0,3) AS s1 UNION ALL
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='02' ORDER BY s.s_score DESC LIMIT 0,3) AS s2 UNION ALL
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='03' ORDER BY s.s_score DESC LIMIT 0,3) AS s3
//查询每门课程被选修的学生数
SELECT s.c_id,COUNT(*) FROM score s GROUP BY s.c_id;
//查询出只有两门课程的全部学生的学号和姓名
SELECT st.*,COUNT(*) FROM score s JOIN student st ON st.s_id=s.s_id GROUP BY s.s_id HAVING COUNT(*)=2;
//查询男生、女生人数
SELECT st.s_sex,COUNT(*) FROM student st GROUP BY st.s_sex;
//查询名字中含有"风"字的学生信息
SELECT * FROM student st WHERE st.s_name LIKE '%风%';
//查询同名同性学生名单，并统计同名人数 //手动加入了lpq测试数据这里开始不一样了
SELECT * FROM student st1 JOIN student st2 ON st1.s_id!=st2.s_id AND st1.s_name=st2.s_name;
//查询1990年出生的学生名单
SELECT * FROM student st WHERE YEAR(st.s_brith)=1990;
//查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT s.c_id,AVG(s.s_score) AS _avg FROM score s GROUP BY s.c_id ORDER BY _avg DESC,s.c_id DESC;
//查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT st.*,AVG(s.s_score) AS _avg FROM score s JOIN student st ON st.s_id=s.s_id GROUP BY st.s_id HAVING AVG(s.s_score)>=85;
//查询课程名称为"数学"，且分数低于60的学生姓名和分数 偷个懒知道数学是02
SELECT st.*,s.s_score FROM score s RIGHT JOIN student st ON st.s_id = s.s_id WHERE s.s_score<60 AND s.c_id='02';
//查询所有学生的课程及分数情况
SELECT s.s_score AS 语文,s.s_id FROM score s WHERE s.c_id='01'
SELECT s.s_score AS 数学,s.s_id FROM score s WHERE s.c_id='02'
SELECT s.s_score AS 英语,s.s_id FROM score s WHERE s.c_id='03'
SELECT st.*,IFNULL(s1.语文,0),IFNULL(s2.数学,0),IFNULL(s3.英语,0) FROM student st
LEFT JOIN (SELECT s.s_score AS 语文,s.s_id FROM score s WHERE s.c_id='01') AS s1 ON s1.s_id=st.s_id
LEFT JOIN (SELECT s.s_score AS 数学,s.s_id FROM score s WHERE s.c_id='02')AS s2 ON st.s_id=s2.s_id
LEFT JOIN (SELECT s.s_score AS 英语,s.s_id FROM score s WHERE s.c_id='03')AS s3 ON st.s_id=s3.s_id
//查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
SELECT s.s_id FROM score s WHERE s.s_score>70 GROUP BY s.s_id;
SELECT st.* FROM score s RIGHT JOIN student st ON st.s_id=s.s_id WHERE s.s_score>70 GROUP BY st.s_id;
//查询课程不及格的学生
SELECT st.* FROM score s RIGHT JOIN student st ON st.s_id=s.s_id WHERE s.s_score<60 GROUP BY s.s_id;
//查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT * FROM score s WHERE s.s_score>80;
SELECT st.* FROM score s JOIN student st ON s.s_id=st.s_id WHERE s.s_score>80 AND s.c_id='01';
//求每门课程的学生人数
SELECT s.c_id,COUNT(*) FROM score s GROUP BY s.c_id;
//查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT c.c_id FROM teacher t JOIN course c ON c.t_id = t.t_id WHERE t.t_name='张三'
SELECT s.s_id,s.s_score FROM score s WHERE s.c_id= (SELECT c.c_id FROM teacher t JOIN course c ON c.t_id = t.t_id WHERE t.t_name='张三') ORDER BY s.s_score
DESC LIMIT 1
SELECT st.*,s1.s_score FROM student st JOIN (SELECT s.s_id,s.s_score FROM score s WHERE s.c_id= (SELECT c.c_id FROM teacher t JOIN course c ON c.t_id = t.t_id WHERE t.t_name='张三') ORDER BY s.s_score
DESC LIMIT 1) AS s1 ON st.s_id=s1.s_id;
//查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 ***
SELECT s.s_score FROM score s GROUP BY s.s_score HAVING COUNT(*)>1;
SELECT * FROM score WHERE s_score IN (SELECT s.s_score FROM score s GROUP BY s.s_score HAVING COUNT(*)>1)
//查询每门课程成绩最好的前三名
SELECT c_id FROM score GROUP BY c_id;
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='01' ORDER BY s.s_score DESC LIMIT 3) AS s1 UNION ALL
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='02' ORDER BY s.s_score DESC LIMIT 3) AS s2 UNION ALL
SELECT * FROM (SELECT * FROM score s WHERE s.c_id='03' ORDER BY s.s_score DESC LIMIT 3)AS s3
// 统计每门课程的学生选修人数（超过5人的课程才统计）
SELECT s.c_id,COUNT(*) FROM score s GROUP BY s.c_id HAVING COUNT(*)>5;
//检索至少选修两门课程的学生学号
SELECT s.s_id,COUNT(*) FROM score s GROUP BY s.s_id HAVING COUNT(*)>=2;
//查询选修了全部课程的学生信息
SELECT s.s_id, FROM score s GROUP BY s.s_id HAVING COUNT(*)=3;
SELECT * FROM student AS st WHERE st.s_id IN (SELECT s.s_id FROM score s GROUP BY s.s_id HAVING COUNT(*)=3)
//查询各学生的年龄(周岁) 算个大概的
SELECT YEAR(NOW())
SELECT YEAR(NOW())-YEAR(st.s_brith) FROM student st;
// 剩下4题没啥用不用了。
总结
1.前面比较难，中间简单，最后有几个也挺难的。最后几题跟日期有关的我没做，这个用程序写更好。
2.仅供参考答案不一定对。
