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

// 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数SELECT stu.s_id, stu.s_name, s1.s_score FROM student stu, score s1, score s2WHERE stu.s_id = s1.s_idAND stu.s_id = s2.s_idAND s1.c_id = '01'AND s2.c_id = '02'AND s1.s_score > s2.s_score

2、查询每门功成绩最好的前两名 。

// 方法一SELECT c1.c_name, ( SELECT s1.s_id FROM score s1 WHERE s1.c_id = c1.c_idORDER BY s1.s_score DESC LIMIT 0,1) 第一名,( SELECT s1.s_id FROM score s1 WHERE s1.c_id = c1.c_idORDER BY s1.s_score DESC LIMIT 1,1) 第二名FROM course c1// 方法二SELECT s1.c_id, s1.s_id, s1.s_scoreFROM score s1 LEFT JOIN score s2 ON s1.c_id = s2.c_idAND s1.s_score < s2.s_scoreGROUP BY s1.c_id, s1.s_idHAVING COUNT(s1.c_id) < 2ORDER BY s1.c_id ASC, s1.s_score DESC// 方法三SELECT sc.c_id, sc.s_score FROM score sc WHERE (SELECT COUNT(*) FROM score WHERE sc.c_id = score.c_id AND sc.s_score < score.s_score) < 2 ORDER BY sc.c_id ASC,sc.s_score DESC;

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

SELECT stu.s_id, stu.s_name, SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) AS '平均分' FROM student stu, score scWHERE stu.s_id = sc.s_idGROUP BY sc.s_idHAVING SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) >= 60 -- AVG计算平均分，缺考会被忽略--SELECT stu.s_id, stu.s_name, AVG(sc.s_score) AS '平均分' FROM student stu, score scWHERE stu.s_id = sc.s_idGROUP BY sc.s_idHAVING AVG(sc.s_score) >= 60 -- 查询某一个学生平均分。SELECT s_id, c_id, SUM(s_score) AS '总分', SUM(s_score) /5 AS '平均分' FROM score WHERE s_id = 3

4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩。(包括有成绩的和无成绩的)

SELECT stu.s_id, stu.s_name, SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) AS '平均分' FROM student stu, score scWHERE stu.s_id = sc.s_idGROUP BY sc.s_idHAVING SUM(sc.s_score) / ( SELECT COUNT(*) FROM course) < 60 UNIONSELECT stu.s_id, stu.s_name, avg(sc.s_score)FROM student stuLEFT JOIN score sc ON stu.s_id = sc.s_idGROUP BY stu.s_idHAVING avg(sc.s_score) IS NULL;

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。

SELECT stu.s_id AS'学号', stu.s_name AS'姓名', COUNT(sc.c_id)AS'选课总数', SUM(sc.s_score) AS '总成绩' FROM student stu, score scWHERE stu.s_id = sc.s_idGROUP BY stu.s_id

6.查询"李"姓老师的数量。

SELECT COUNT(t_id)AS '总数' FROM teacher WHERE t_name LIKE '李%' 

7.查询学过"张三"老师授课的同学的信息。

SELECT s_id, s_name FROM studentWHERE s_id IN (SELECT sc.s_id FROM score sc, course c, teacher tWHERE sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张三')

8.查询没学过"张三"老师授课的同学的信息。

SELECT s_id, s_name FROM studentWHERE s_id NOT IN (SELECT sc.s_id FROM score sc, course c, teacher tWHERE sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张三')

9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。

                 SELECT stu.s_id, stu.s_name FROM student stu, score scWHERE stu.s_id = sc.s_idAND sc.c_id = '01'AND EXISTS (SELECT * FROM score sc WHERE sc.s_id = stu.s_id AND sc.c_id = '02')

#10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。

SELECT stu.s_id, stu.s_name FROM student stu, score scWHERE stu.s_id = sc.s_idAND sc.c_id = '01'AND stu.s_id NOT IN (SELECT s_id FROM score WHERE c_id = '02')

11.查询没有学全所有课程的同学的信息 。

SELECT s_id, s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM score  GROUP BY s_idHAVING COUNT(c_id) = (SELECT COUNT(c_id) FROM course))

12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息。

SELECT DISTINCT stu.s_id, stu.s_name FROM student stu, score scWHERE stu.s_id = sc.s_idAND sc.c_id IN (SELECT c_id FROM score WHERE s_id = '1')

13.查询和"01"号的同学学习的课程完全相同的其他同学的信息。

SELECT * FROM student WHERE s_id IN (    SELECT s_id FROM score     GROUP BY s_id    HAVING group_concat(c_id) = (        SELECT group_concat(c_id) FROM score        WHERE s_id = 1) AND s_id != 1);select group_concat(stu.s_name)AS '姓名' from student stu -- 上面原查询语句 --select * from student where s_id in (    select s_id from score     group by s_id    having group_concat(c_id ORDER BY c_id) = (        select group_concat(c_id ORDER BY c_id) as str2 from score        where s_id = 1) and s_id != 1);select * from student where s_id in (select s_id from score where s_id not in (select s_id from score where c_id not in (select c_id from score where s_id='01'))group by s_id having count(*)=(select count(*) from score where s_id='01') and s_id != '01');

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

SELECT s_id,s_name FROM student WHERE s_id NOT IN (SELECT sc.s_id FROM score sc, course c, teacher tWHERE sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张三')

15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。

// 方法一SELECT stu.s_id, stu.s_name, AVG(sc.s_score) FROM score sc JOIN student stu ON stu.s_id = sc.s_idGROUP BY stu.s_idHAVING count(sc.s_score<60 or null) >= 2// 方法二SELECT stu.s_id, stu.s_name, AVG(sc.s_score) FROM score sc, student stuWHERE stu.s_id = sc.s_idGROUP BY stu.s_idHAVING count(sc.s_score<60 or null) > 1

16.检索"01"课程分数小于60，按分数降序排列的学生信息。

SELECT stu.s_id, stu.s_name, sc.s_score FROM student stu LEFT JOIN score sc ON stu.s_id = sc.s_idWHERE sc.c_id = '01'AND sc.s_score < 60ORDER BY sc.s_score DESC

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

SELECT stu.s_id, stu.s_name,GROUP_CONCAT(c.c_name) '课程',GROUP_CONCAT(sc.s_score) '分数',AVG(sc.s_score) '平均分'FROM student stuLEFT JOIN score sc ON stu.s_id = sc.s_idJOIN course c ON sc.c_id = c.c_idGROUP BY sc.s_id ORDER BY AVG(sc.s_score) DESC

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分。

SELECT c.c_id AS'课程id',c.c_name AS'课程名称',MAX(sc.s_score) AS'最高分',MIN(sc.s_score) AS'最低分',AVG(sc.s_score) AS'平均分'FROM course c, score scWHERE c.c_id = sc.c_idGROUP BY sc.c_id-- 验证 --SELECT s_score FROM score WHERE c_id = '04' ORDER BY s_score

19.查询出各科成绩总分，并按总分降序排序：以如下形式显示：课程ID，课程name，总分

SELECT c.c_id AS'课程ID', c.c_name AS'课程名称',SUM(sc.s_score) AS'总分'FROM score sc, course cWHERE sc.c_id = c.c_idGROUP BY sc.c_idORDER BY SUM(sc.s_score) DESC

20.查询学生的总成绩及学生信息。

SELECT stu.s_id, stu.s_name,SUM(sc.s_score)AS'总成绩' FROM student stu, score scWHERE sc.s_id = stu.s_idGROUP BY stu.s_id

21.查询不同老师所教不同课程平均分从高到低显示。

SELECT t.t_id, t.t_name AS'教师名称',c.c_name AS'课程名称',AVG(sc.s_score) AS'平均分'FROM teacher t, course c, score scWHERE t.t_id = c.t_idAND c.c_id = sc.c_idGROUP BY t.t_idORDER BY AVG(sc.s_score) DESC

22.查询每门课程被选修的学生数。

SELECT c.c_id, c.c_name AS'课程名称', COUNT(c.c_id)AS'选修人数' FROM course c, score scWHERE c.c_id = sc.c_idGROUP BY c.c_id

23.查询出只有两门课程的全部学生的学号和姓名。

SELECT stu.s_id, stu.s_name FROM student stu, score scWHERE stu.s_id = sc.s_idGROUP BY sc.s_idHAVING COUNT(sc.c_id) = 2

24.查询男生、女生人数

SELECT s_sex AS'性别',COUNT(1) AS '人数'FROM student GROUP BY s_sex

25.查询名字中含有"风"字的学生信息。

SELECT s_id, s_name FROM studentWHERE s_name LIKE '%风%'

26.查询1990年出生的学生名单。

SELECT * FROM student WHERE s_birth LIKE '1990%'-- 方法二 --SELECT * FROM student WHERE s_birth BETWEEN '1990-1-1' AND '1990-12-31';

27.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。

SELECT c.c_name, avg(s_score) FROM score sc, course c WHERE sc.c_id = c.c_id GROUP BY c.c_id ORDER BY AVG(sc.s_score) DESC,sc.c_id

28.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。

SELECT stu.s_id, stu.s_name, AVG(sc.s_score) FROM student stu, score scWHERE stu.s_id = sc.s_idGROUP BY sc.s_id HAVING AVG(sc.s_score) > 85

29.查询课程名称为"数学"，且分数低于60的学生姓名和分数。

SELECT stu.s_id, stu.s_name, sc.s_score FROM student stu, score sc, course cWHERE stu.s_id = sc.s_idAND c.c_id = sc.c_idAND c.c_name = '数学'AND sc.s_score < 60

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

SELECT stu.s_name, c.c_name, sc.s_score FROM student stu, course c, score scWHERE stu.s_id = sc.s_idAND sc.c_id = c.c_idGROUP BY sc.s_score HAVING sc.s_score >70

31.查询不及格的课程。

SELECT stu.s_name, c.c_name, sc.s_score FROM student stu, course c, score scWHERE stu.s_id = sc.s_idAND sc.c_id = c.c_idGROUP BY sc.s_score HAVING sc.s_score < 60

32.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名。

SELECT stu.s_id,stu.s_name, c.c_name, sc.s_score FROM student stu, course c, score scWHERE stu.s_id = sc.s_idAND sc.c_id = c.c_idAND c.c_id = '01'AND  sc.s_score > 80

33.求每门课程的学生人数。

SELECT c.c_name, COUNT(c.c_id) FROM score sc, course cWHERE sc.c_id = c.c_idGROUP BY c.c_id

34.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。

SELECT stu.s_id, stu.s_name, MAX(sc.s_score) FROM student stu, course c, score sc, teacher tWHERE stu.s_id = sc.s_idAND sc.c_id = c.c_idAND c.t_id = t.t_idAND t.t_name = '张无忌'GROUP BY c.c_id -- 方法二 --SELECT s.*,MAX(sc.s_score)FROM student s,score scWHERE s.s_id=sc.s_idAND sc.c_id in(SELECT c_idFROM teacher t,course cWHERE t.t_id=c.t_idAND t.t_name='张三');

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

-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 --SELECT c_id, COUNT(s_id) AS num FROM score GROUP BY c_id HAVING num > 5 ORDER BY num DESC, c_id ASC

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

SELECT stu.s_id, stu.s_name FROM student stu, score sc WHERE stu.s_id = sc.s_id GROUP BY s_id HAVING COUNT(c_id) > 2

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

SELECT stu.s_id, stu.s_name FROM score sc, student stuWHERE sc.s_id = stu.s_idGROUP BY s_id HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM course)

38.查询各学生的年龄。

-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1 --SELECT stu.s_id, stu.s_name, TIMESTAMPDIFF(YEAR,stu.s_birth,CURDATE()) FROM student stuSELECT s_id, s_name, ROUND(DATEDIFF(CURDATE(), s_birth)/365.2422) FROM student/select s_id, s_name, year(current_date())-year(s_birth) as agefrom student;

39.查询本周过生日的学生。

SELECT * FROM student WHERE WEEKOFYEAR(s_birth) = WEEKOFYEAR(CURDATE())SELECT WEEKOFYEAR(CURDATE())

40.查询下周过生日的学生。

SELECT * FROM studentWHERE WEEKOFYEAR(s_birth) = WEEKOFYEAR(CURDATE())+1;

41.查询本月过生日的学生。

SELECT * FROM student t1WHERE MONTH(t1.s_birth) = MONTH(CURDATE());

42.查询下月过生日的学生。

SELECT * FROM student t1WHERE MONTH(t1.s_birth) = MONTH(CURDATE())+1;

43.笛卡尔积乘积

-- 方式一 --SELECT * FROM student s,teacher t-- 方式二 --SELECT * from student CROSS JOIN teacher

=== 建表数据 ===

-- 学生表 --create table student (s_id INT(10) NOT NULL,s_name varchar(20) not null,s_birth varchar(20),s_sex varchar(2),primary key (s_id))insert into `student` values ('1', '张三','1990-10-10','男');insert into `student` values ('2', '李四','1996-10-20','女');insert into `student` values ('3', '王五','2008-10-21','男');insert into `student` values ('4', '赵六','2010-08-10','女');insert into `student` values ('5', '小七','2000-10-22','女');insert into `student` values ('6', '风子','1994-11-10','男');insert into `student` values ('7', '李蛋','1994-12-10','男');insert into `student` values ('8', '重风','1994-09-10','男');-- 课程表 --create table course(c_id varchar(10) not null comment'课程编号',c_name varchar(20) not null comment'课程名称',t_id int(20) not null comment'教师编号')insert into `course` values ('01','语文','1');insert into `course` values ('02','数学','2');insert into `course` values ('03','英语','3');insert into `course` values ('04','体育','4');insert into `course` values ('05','化学','5');insert into `course` values ('06','物理','6');insert into `course` values ('07','生物','7');-- 成绩表 --create table score(s_id int(20) not null comment'学生编号',c_id varchar(10) not null comment'课程编号',s_score int(3) not null comment'分数')insert into `score` values ('1','01','56');insert into `score` values ('1','02','58');insert into `score` values ('1', '03','67');insert into `score` values ('2', '01','54');insert into `score` values ('2', '02','78');insert into `score` values ('2', '03','54');insert into `score` values ('2', '04','66');insert into `score` values ('2', '05','79');insert into `score` values ('2', '06','82');insert into `score` values ('2', '07','87');insert into `score` values ('3', '01','84');insert into `score` values ('3', '02','89');insert into `score` values ('3', '03','68');insert into `score` values ('3', '04','71');insert into `score` values ('3', '05','37');insert into `score` values ('3', '06','66');insert into `score` values ('3', '07','77');insert into `score` values ('4', '01','80');insert into `score` values ('4', '03','74');insert into `score` values ('4', '04','57');insert into `score` values ('4', '05','65');insert into `score` values ('5', '01','100');insert into `score` values ('5', '04','99');insert into `score` values ('7', '01','96');insert into `score` values ('7', '02','50');insert into `score` values ('7', '03','83');insert into `score` values ('8', '05','90');insert into `score` values ('8', '06','88');-- 教师表 --create table `teacher`(`t_id` int(20) not null comment'教师编号',`t_name` varchar(20) not null comment'教师姓名',primary key(`t_id`))insert into `teacher` values ('1', '张无忌');insert into `teacher` values ('2', '张三丰');insert into `teacher` values ('3', '老过');insert into `teacher` values ('4', '郭靖');insert into `teacher` values ('5', '唐僧');insert into `teacher` values ('6', '张六');

总结

========================努力干吧！菜鸟。

来源地址：https://blog.csdn.net/weixin_43883708/article/details/127350253