大家可以根据自己的查询需求更改数据,如果你sql很6那么当然最好啦,如果不是特别厉害建议理解这50道Sql题目,完事你会发现自己进步蛮大!!!加油
1.学生表
student(s_id,s_name,s_birth,s_sex)
-- 学生编号,学生姓名, 出生年月,学生性别
2.课程表
course(c_id,c_name,t_id)
-- 课程编号, 课程名称, 教师编号
3.教师表
teacher(t_id,t_name)
-– 教师编号,教师姓名
4.成绩表
score(s_id,c_id,s_score)
--学生编号,课程编号,分数
随便建立一个数据库,导入数据到数据库:
1-- 建立课程表
2DROP TABLE IF EXISTS `course`;
3CREATE TABLE `course` (
4 `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '课程编号',
5 `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '课程名称',
6 `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
7 PRIMARY KEY (`c_id`) USING BTREE
8) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
9-- 课程表数据
10INSERT INTO `course` VALUES ('01', '语文', '02');
11INSERT INTO `course` VALUES ('02', '数学', '01');
12INSERT INTO `course` VALUES ('03', '英语', '03');
13-- 分数表
14DROP TABLE IF EXISTS `score`;
15CREATE TABLE `score` (
16 `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '学生编号',
17 `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '课程编号',
18 `s_score` int(3) NULL DEFAULT NULL COMMENT '分数',
19 PRIMARY KEY (`s_id`, `c_id`) USING BTREE
20) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
21-- 分数数据
22INSERT INTO `score` VALUES ('01', '01', 80);
23INSERT INTO `score` VALUES ('01', '02', 90);
24INSERT INTO `score` VALUES ('01', '03', 99);
25INSERT INTO `score` VALUES ('02', '01', 70);
26INSERT INTO `score` VALUES ('02', '02', 60);
27INSERT INTO `score` VALUES ('02', '03', 80);
28INSERT INTO `score` VALUES ('03', '01', 80);
29INSERT INTO `score` VALUES ('03', '02', 80);
30INSERT INTO `score` VALUES ('03', '03', 80);
31INSERT INTO `score` VALUES ('04', '01', 50);
32INSERT INTO `score` VALUES ('04', '02', 30);
33INSERT INTO `score` VALUES ('04', '03', 20);
34INSERT INTO `score` VALUES ('05', '01', 76);
35INSERT INTO `score` VALUES ('05', '02', 87);
36INSERT INTO `score` VALUES ('05', '03', 95);
37INSERT INTO `score` VALUES ('06', '01', 31);
38INSERT INTO `score` VALUES ('06', '02', 88);
39INSERT INTO `score` VALUES ('06', '03', 34);
40INSERT INTO `score` VALUES ('07', '01', 66);
41INSERT INTO `score` VALUES ('07', '02', 89);
42INSERT INTO `score` VALUES ('07', '03', 98);
43INSERT INTO `score` VALUES ('08', '01', 59);
44INSERT INTO `score` VALUES ('08', '02', 88);
45INSERT INTO `score` VALUES ('09', '02', 67);
46INSERT INTO `score` VALUES ('09', '03', 88);
47INSERT INTO `score` VALUES ('10', '01', 65);
48INSERT INTO `score` VALUES ('10', '02', 78);
49-- 建立学生表
50DROP TABLE IF EXISTS `student`;
51CREATE TABLE `student` (
52 `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '学生编号',
53 `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '学生姓名',
54 `s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '出生年月',
55 `s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '学生性别',
56 PRIMARY KEY (`s_id`) USING BTREE
57) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
58-- 学生数据
59INSERT INTO `student` VALUES ('01', '斯内克', '1990-01-01', '男');
60INSERT INTO `student` VALUES ('02', '张益达', '1990-12-21', '男');
61INSERT INTO `student` VALUES ('03', '张大炮', '1990-05-20', '男');
62INSERT INTO `student` VALUES ('04', '李云龙', '1990-08-06', '男');
63INSERT INTO `student` VALUES ('05', '楚云飞', '1991-12-01', '女');
64INSERT INTO `student` VALUES ('06', '赵日天', '1992-03-01', '女');
65INSERT INTO `student` VALUES ('07', '小甜甜', '1989-07-01', '女');
66INSERT INTO `student` VALUES ('08', '王菊花', '1990-01-20', '女');
67INSERT INTO `student` VALUES ('09', '李慕白', '1994-01-20', '男');
68INSERT INTO `student` VALUES ('10', '东京热', '1980-01-20', '女');
69-- 建立老师表
70DROP TABLE IF EXISTS `teacher`;
71CREATE TABLE `teacher` (
72 `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '教师编号',
73 `t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '教师姓名',
74 PRIMARY KEY (`t_id`) USING BTREE
75) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
76-- 老师表数据
77INSERT INTO `teacher` VALUES ('01', '墨白');
78INSERT INTO `teacher` VALUES ('02', '默狐');
79INSERT INTO `teacher` VALUES ('03', '柠檬');
80SET FOREIGN_KEY_CHECKS = 1;1、查询"语文"课程比"数学"课程成绩高的学生的信息及课程分数
1SELECT
2 st.*,
3 sc.s_score AS '语文',
4 sc2.s_score '数学'
5FROM
6 student st
7 LEFT JOIN score sc ON sc.s_id = st.s_id
8 AND sc.c_id = '01'
9 LEFT JOIN score sc2 ON sc2.s_id = st.s_id
10 AND sc2.c_id = '02'
11WHERE
12 sc.s_score > sc2.s_score
2、查询"语文"课程比"数学"课程成绩低的学生的信息及课程分数
1SELECT
2 st.*,
3 sc.s_score '语文',
4 sc2.s_score '数学'
5FROM
6 student st
7 LEFT JOIN score sc ON sc.s_id = st.s_id
8 AND sc.c_id = '01'
9 LEFT JOIN score sc2 ON sc2.s_id = st.s_id
10 AND sc2.c_id = '02'
11WHERE
12 sc.s_score < sc2.s_score
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
1SELECT
2 st.s_id,
3 st.s_name,
4 ROUND( AVG( sc.s_score ), 2 ) "平均成绩"
5FROM
6 student st
7 LEFT JOIN score sc ON sc.s_id = st.s_id
8GROUP BY
9 st.s_id
10HAVING
11 AVG( sc.s_score )>= 60
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
1SELECT
2 st.s_id,
3 st.s_name,(
4 CASE
5
6 WHEN ROUND( AVG( sc.s_score ), 2 ) IS NULL THEN
7 0 ELSE ROUND( AVG( sc.s_score ), 2 )
8 END
9 ) "平均成绩"
10 FROM
11 student st
12 LEFT JOIN score sc ON sc.s_id = st.s_id
13 GROUP BY
14 st.s_id
15 HAVING
16 AVG( sc.s_score )< 60
17 OR AVG( sc.s_score ) IS NULL
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
1SELECT
2 st.s_id,
3 st.s_name,
4 count( sc.c_id ) "选课总数",
5 sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) "总成绩"
6FROM
7 student st
8 LEFT JOIN score sc ON st.s_id = sc.s_id
9GROUP BY
10 st.s_id
6、查询"墨"姓老师的数量
1SELECT
2 t.t_name,
3 count( t.t_id )
4FROM
5 teacher t
6GROUP BY
7 t.t_id
8HAVING
9 t.t_name LIKE "墨%";
7、查询学过"墨白"老师授课的同学的信息
1SELECT
2 st.*
3FROM
4 student st
5 LEFT JOIN score sc ON sc.s_id = st.s_id
6 LEFT JOIN course c ON c.c_id = sc.c_id
7 LEFT JOIN teacher t ON t.t_id = c.t_id
8WHERE
9 t.t_name = "墨白"8、查询没学过"柠檬"老师授课的同学的信息
1-- 柠檬老师教的课
2SELECT
3 c.*
4FROM
5 course c
6 LEFT JOIN teacher t ON t.t_id = c.t_id
7WHERE
8 t.t_name = "柠檬"
9-- 有柠檬老师课成绩的st.s_id
10SELECT
11 sc.s_id
12FROM
13 score sc
14WHERE
15 sc.c_id IN ( SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = "柠檬" )
16-- 不在上面查到的st.s_id的学生信息,即没学过柠檬老师授课的同学信息
17SELECT
18 st.*
19FROM
20 student st
21WHERE
22 st.s_id NOT IN (
23 SELECT
24 sc.s_id
25 FROM
26 score sc
27 WHERE
28 sc.c_id IN ( SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = "柠檬" )
29 )9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
1SELECT
2 st.*
3FROM
4 student st
5 INNER JOIN score sc ON sc.s_id = st.s_id
6 INNER JOIN course c ON c.c_id = sc.c_id
7 AND c.c_id = "01"
8WHERE
9 st.s_id IN (
10 SELECT
11 st2.s_id
12 FROM
13 student st2
14 INNER JOIN score sc2 ON sc2.s_id = st2.s_id
15 INNER JOIN course c2 ON c2.c_id = sc2.c_id
16 AND c2.c_id = "02"
17 )
18-- 或者第二种
19SELECT
20 a.*
21FROM
22 student a,
23 score b,
24 score c
25WHERE
26 a.s_id = b.s_id
27 AND a.s_id = c.s_id
28 AND b.c_id = '01'
29 AND c.c_id = '02';
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
1SELECT
2 st.*
3FROM
4 student st
5 INNER JOIN score sc ON sc.s_id = st.s_id
6 INNER JOIN course c ON c.c_id = sc.c_id
7 AND c.c_id = "01"
8WHERE
9 st.s_id NOT IN (
10 SELECT
11 st2.s_id
12 FROM
13 student st2
14 INNER JOIN score sc2 ON sc2.s_id = st2.s_id
15 INNER JOIN course c2 ON c2.c_id = sc2.c_id
16 AND c2.c_id = "02"
17 )
11、查询没有学全所有课程的同学的信息
1SELECT
2 *
3FROM
4 student
5WHERE
6 s_id NOT IN (
7 SELECT
8 st.s_id
9 FROM
10 student st
11 INNER JOIN score sc ON sc.s_id = st.s_id
12 AND sc.c_id = "01"
13 WHERE
14 st.s_id IN ( SELECT st1.s_id FROM student st1 INNER JOIN score sc2 ON sc2.s_id = st1.s_id AND sc2.c_id = "02" )
15 AND st.s_id IN ( SELECT st2.s_id FROM student st2 INNER JOIN score sc2 ON sc2.s_id = st2.s_id AND sc2.c_id = "03" ))
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
1SELECT DISTINCT
2 st.*
3FROM
4 student st
5 LEFT JOIN score sc ON sc.s_id = st.s_id
6WHERE
7 sc.c_id IN (
8 SELECT
9 sc2.c_id
10 FROM
11 student st2
12 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
13 WHERE
14 st2.s_id = '01'
15 )
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
1SELECT
2 st.*
3FROM
4 student st
5 LEFT JOIN score sc ON sc.s_id = st.s_id
6GROUP BY
7 st.s_id
8HAVING
9 group_concat( sc.c_id ) = (
10 SELECT
11 group_concat( sc2.c_id )
12 FROM
13 student st2
14 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
15 WHERE
16 st2.s_id = '01'
17 )
14、查询没学过"默狐"老师讲授的任一门课程的学生姓名
1SELECT
2 st.s_name
3FROM
4 student st
5WHERE
6 st.s_id NOT IN (
7 SELECT
8 sc.s_id
9 FROM
10 score sc
11 INNER JOIN course c ON c.c_id = sc.c_id
12 INNER JOIN teacher t ON t.t_id = c.t_id
13 AND t.t_name = "默狐"
14 )
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
1SELECT
2 st.s_id,
3 st.s_name,
4 avg( sc.s_score )
5FROM
6 student st
7 LEFT JOIN score sc ON sc.s_id = st.s_id
8WHERE
9 sc.s_id IN (
10 SELECT
11 sc.s_id
12 FROM
13 score sc
14 WHERE
15 sc.s_score < 60
16 OR sc.s_score IS NULL
17 GROUP BY
18 sc.s_id
19 HAVING
20 COUNT( 1 )>= 2
21 )
22GROUP BY
23 st.s_id
16、检索"01"课程分数小于60，按分数降序排列的学生信息
1SELECT
2 st.*,
3 sc.s_score
4FROM
5 student st
6 INNER JOIN score sc ON sc.s_id = st.s_id
7 AND sc.c_id = "01"
8 AND sc.s_score < 60
9ORDER BY
10 sc.s_score DESC
11
12-- 第二种SQL
13SELECT
14 st.*,
15 sc.s_score
16FROM
17 student st
18 LEFT JOIN score sc ON sc.s_id = st.s_id
19WHERE
20 sc.c_id = "01"
21 AND sc.s_score < 60
22ORDER BY
23 sc.s_score DESC
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
1SELECT
2 st.s_id,
3 st.s_name,
4 avg( sc4.s_score ) "平均分",
5 sc.s_score "语文",
6 sc2.s_score "数学",
7 sc3.s_score "英语"
8FROM
9 student st
10 LEFT JOIN score sc ON sc.s_id = st.s_id
11 AND sc.c_id = "01"
12 LEFT JOIN score sc2 ON sc2.s_id = st.s_id
13 AND sc2.c_id = "02"
14 LEFT JOIN score sc3 ON sc3.s_id = st.s_id
15 AND sc3.c_id = "03"
16 LEFT JOIN score sc4 ON sc4.s_id = st.s_id
17GROUP BY
18 st.s_id
19ORDER BY
20 avg( sc4.s_score ) DESC
21
22-- 第二种
23SELECT
24 st.s_id,
25 st.s_name,
26 ( CASE WHEN avg( sc4.s_score ) IS NULL THEN 0 ELSE avg( sc4.s_score ) END ) "平均分",
27 ( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) "语文",
28 ( CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END ) "数学",
29 ( CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END ) "英语"
30FROM
31 student st
32 LEFT JOIN score sc ON sc.s_id = st.s_id
33 AND sc.c_id = "01"
34 LEFT JOIN score sc2 ON sc2.s_id = st.s_id
35 AND sc2.c_id = "02"
36 LEFT JOIN score sc3 ON sc3.s_id = st.s_id
37 AND sc3.c_id = "03"
38 LEFT JOIN score sc4 ON sc4.s_id = st.s_id
39GROUP BY
40 st.s_id
41ORDER BY
42 avg( sc4.s_score ) DESC
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90)
1SELECT
2 c.c_id,
3 c.c_name,
4 max( sc.s_score ) "最高分",
5 MIN( sc2.s_score ) "最低分",
6 avg( sc3.s_score ) "平均分",((
7 SELECT
8 count( s_id )
9 FROM
10 score
11 WHERE
12 s_score >= 60
13 AND c_id = c.c_id
14 )/(
15 SELECT
16 count( s_id )
17 FROM
18 score
19 WHERE
20 c_id = c.c_id
21 )) "及格率",((
22 SELECT
23 count( s_id )
24 FROM
25 score
26 WHERE
27 s_score >= 70
28 AND s_score < 80
29 AND c_id = c.c_id
30 )/(
31 SELECT
32 count( s_id )
33 FROM
34 score
35 WHERE
36 c_id = c.c_id
37 )) "中等率",((
38 SELECT
39 count( s_id )
40 FROM
41 score
42 WHERE
43 s_score >= 80
44 AND s_score < 90
45 AND c_id = c.c_id
46 )/(
47 SELECT
48 count( s_id )
49 FROM
50 score
51 WHERE
52 c_id = c.c_id
53 )) "优良率",((
54 SELECT
55 count( s_id )
56 FROM
57 score
58 WHERE
59 s_score >= 90
60 AND c_id = c.c_id
61 )/(
62 SELECT
63 count( s_id )
64 FROM
65 score
66 WHERE
67 c_id = c.c_id
68 )) "优秀率"
69FROM
70 course c
71 LEFT JOIN score sc ON sc.c_id = c.c_id
72 LEFT JOIN score sc2 ON sc2.c_id = c.c_id
73 LEFT JOIN score sc3 ON sc3.c_id = c.c_id
74GROUP BY
75 c.c_id
19、按各科成绩进行排序，并显示排名(实现不完全)
1SELECT
2 c.c_id,
3 c.c_name,
4 max( sc.s_score ) "最高分",
5 MIN( sc2.s_score ) "最低分",
6 avg( sc3.s_score ) "平均分",((
7 SELECT
8 count( s_id )
9 FROM
10 score
11 WHERE
12 s_score >= 60
13 AND c_id = c.c_id
14 )/(
15 SELECT
16 count( s_id )
17 FROM
18 score
19 WHERE
20 c_id = c.c_id
21 )) "及格率",((
22 SELECT
23 count( s_id )
24 FROM
25 score
26 WHERE
27 s_score >= 70
28 AND s_score < 80
29 AND c_id = c.c_id
30 )/(
31 SELECT
32 count( s_id )
33 FROM
34 score
35 WHERE
36 c_id = c.c_id
37 )) "中等率",((
38 SELECT
39 count( s_id )
40 FROM
41 score
42 WHERE
43 s_score >= 80
44 AND s_score < 90
45 AND c_id = c.c_id
46 )/(
47 SELECT
48 count( s_id )
49 FROM
50 score
51 WHERE
52 c_id = c.c_id
53 )) "优良率",((
54 SELECT
55 count( s_id )
56 FROM
57 score
58 WHERE
59 s_score >= 90
60 AND c_id = c.c_id
61 )/(
62 SELECT
63 count( s_id )
64 FROM
65 score
66 WHERE
67 c_id = c.c_id
68 )) "优秀率"
69FROM
70 course c
71 LEFT JOIN score sc ON sc.c_id = c.c_id
72 LEFT JOIN score sc2 ON sc2.c_id = c.c_id
73 LEFT JOIN score sc3 ON sc3.c_id = c.c_id
74GROUP BY
75 c.c_id
20、查询学生的总成绩并进行排名
1SELECT
2 st.s_id,
3 st.s_name,(
4 CASE
5
6 WHEN sum( sc.s_score ) IS NULL THEN
7 0 ELSE sum( sc.s_score )
8 END
9 )
10 FROM
11 student st
12 LEFT JOIN score sc ON sc.s_id = st.s_id
13 GROUP BY
14 st.s_id
15ORDER BY
16 sum( sc.s_score ) DESC
21、查询不同老师所教不同课程平均分从高到低显示
1SELECT
2 t.t_id,
3 t.t_name,
4 c.c_name,
5 avg( sc.s_score )
6FROM
7 teacher t
8 LEFT JOIN course c ON c.t_id = t.t_id
9 LEFT JOIN score sc ON sc.c_id = c.c_id
10GROUP BY
11 t.t_id
12ORDER BY
13 avg( sc.s_score ) DESC
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
1SELECT
2 a.*
3FROM
4 (
5 SELECT
6 st.*,
7 c.c_id,
8 c.c_name,
9 sc.s_score
10 FROM
11 student st
12 LEFT JOIN score sc ON sc.s_id = st.s_id
13 INNER JOIN course c ON c.c_id = sc.c_id
14 AND c.c_id = "01"
15 ORDER BY
16 sc.s_score DESC
17 LIMIT 1,
18 2
19 ) a UNION ALL
20SELECT
21 b.*
22FROM
23 (
24 SELECT
25 st.*,
26 c.c_id,
27 c.c_name,
28 sc.s_score
29 FROM
30 student st
31 LEFT JOIN score sc ON sc.s_id = st.s_id
32 INNER JOIN course c ON c.c_id = sc.c_id
33 AND c.c_id = "02"
34 ORDER BY
35 sc.s_score DESC
36 LIMIT 1,
37 2
38 ) b UNION ALL
39SELECT
40 c.*
41FROM
42 (
43 SELECT
44 st.*,
45 c.c_id,
46 c.c_name,
47 sc.s_score
48 FROM
49 student st
50 LEFT JOIN score sc ON sc.s_id = st.s_id
51 INNER JOIN course c ON c.c_id = sc.c_id
52 AND c.c_id = "03"
53 ORDER BY
54 sc.s_score DESC
55 LIMIT 1,
56 2
57 ) c
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
1SELECT
2 c.c_id,
3 c.c_name,((
4 SELECT
5 count( 1 )
6 FROM
7 score sc
8 WHERE
9 sc.c_id = c.c_id
10 AND sc.s_score <= 100 AND sc.s_score > 80
11 )/(
12 SELECT
13 count( 1 )
14 FROM
15 score sc
16 WHERE
17 sc.c_id = c.c_id
18 )) "100-85",((
19 SELECT
20 count( 1 )
21 FROM
22 score sc
23 WHERE
24 sc.c_id = c.c_id
25 AND sc.s_score <= 85 AND sc.s_score > 70
26 )/(
27 SELECT
28 count( 1 )
29 FROM
30 score sc
31 WHERE
32 sc.c_id = c.c_id
33 )) "85-70",((
34 SELECT
35 count( 1 )
36 FROM
37 score sc
38 WHERE
39 sc.c_id = c.c_id
40 AND sc.s_score <= 70 AND sc.s_score > 60
41 )/(
42 SELECT
43 count( 1 )
44 FROM
45 score sc
46 WHERE
47 sc.c_id = c.c_id
48 )) "70-60",((
49 SELECT
50 count( 1 )
51 FROM
52 score sc
53 WHERE
54 sc.c_id = c.c_id
55 AND sc.s_score <= 60 AND sc.s_score >= 0
56 )/(
57 SELECT
58 count( 1 )
59 FROM
60 score sc
61 WHERE
62 sc.c_id = c.c_id
63 )) "60-0"
64FROM
65 course c
66ORDER BY
67 c.c_id
24、查询学生平均成绩及其名次
1SET @i = 0;
2SELECT
3 a.*,
4 @i := @i + 1
5FROM
6 (
7 SELECT
8 st.s_id,
9 st.s_name,
10 round(( CASE WHEN avg( sc.s_score ) IS NULL THEN 0 ELSE avg( sc.s_score ) END ), 2 ) "平均分"
11 FROM
12 student st
13 LEFT JOIN score sc ON sc.s_id = st.s_id
14 GROUP BY
15 st.s_id
16 ORDER BY
17 sc.s_score DESC
18 ) a
25、查询各科成绩前三名的记录
1SELECT
2 a.*
3FROM
4 (
5 SELECT
6 st.s_id,
7 st.s_name,
8 c.c_id,
9 c.c_name,
10 sc.s_score
11 FROM
12 student st
13 LEFT JOIN score sc ON sc.s_id = st.s_id
14 INNER JOIN course c ON c.c_id = sc.c_id
15 AND c.c_id = '01'
16 ORDER BY
17 sc.s_score DESC
18 LIMIT 0,
19 3
20 ) a UNION ALL
21SELECT
22 b.*
23FROM
24 (
25 SELECT
26 st.s_id,
27 st.s_name,
28 c.c_id,
29 c.c_name,
30 sc.s_score
31 FROM
32 student st
33 LEFT JOIN score sc ON sc.s_id = st.s_id
34 INNER JOIN course c ON c.c_id = sc.c_id
35 AND c.c_id = '02'
36 ORDER BY
37 sc.s_score DESC
38 LIMIT 0,
39 3
40 ) b UNION ALL
41SELECT
42 c.*
43FROM
44 (
45 SELECT
46 st.s_id,
47 st.s_name,
48 c.c_id,
49 c.c_name,
50 sc.s_score
51 FROM
52 student st
53 LEFT JOIN score sc ON sc.s_id = st.s_id
54 INNER JOIN course c ON c.c_id = sc.c_id
55 AND c.c_id = '03'
56 ORDER BY
57 sc.s_score DESC
58 LIMIT 0,
59 3
60 ) c
26、查询每门课程被选修的学生数
1SELECT
2 c.c_id,
3 c.c_name,
4 count( 1 )
5FROM
6 course c
7 LEFT JOIN score sc ON sc.c_id = c.c_id
8 INNER JOIN student st ON st.s_id = c.c_id
9GROUP BY
10 st.s_id
27、查询出只有两门课程的全部学生的学号和姓名
1SELECT
2 st.s_id,
3 st.s_name
4FROM
5 student st
6 LEFT JOIN score sc ON sc.s_id = st.s_id
7 INNER JOIN course c ON c.c_id = sc.c_id
8GROUP BY
9 st.s_id
10HAVING
11 count( 1 )=2
28、查询男生、女生人数
1SELECT
2 st.s_sex,
3 count( 1 )
4FROM
5 student st
6GROUP BY
7 st.s_sex
29、查询名字中含有"白"字的学生信息
1SELECT
2 st.*
3FROM
4 student st
5WHERE
6 st.s_name LIKE "%白%";
30、查询同名同性学生名单，并统计同名人数
1SELECT
2 st.*,
3 count( 1 )
4FROM
5 student st
6GROUP BY
7 st.s_name,
8 st.s_sex
9HAVING
10 count( 1 )> 1
31、查询1990年出生的学生名单
1SELECT
2 st.*
3FROM
4 student st
5WHERE
6 st.s_birth LIKE "1990%";
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
1SELECT
2 c.c_id,
3 c.c_name,
4 avg( sc.s_score )
5FROM
6 course c
7 INNER JOIN score sc ON sc.c_id = c.c_id
8GROUP BY
9 c.c_id
10ORDER BY
11 avg( sc.s_score ) DESC,
12 c.c_id ASC
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
1SELECT
2 st.s_id,
3 st.s_name,
4 avg( sc.s_score )
5FROM
6 student st
7 LEFT JOIN score sc ON sc.s_id = st.s_id
8GROUP BY
9 st.s_id
10HAVING
11 avg( sc.s_score )>= 85
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
1SELECT
2 st.s_id,
3 st.s_name,
4 sc.s_score
5FROM
6 student st
7 INNER JOIN score sc ON sc.s_id = st.s_id
8 AND sc.s_score < 60
9 INNER JOIN course c ON c.c_id = sc.c_id
10 AND c.c_name = "数学"35、查询所有学生的课程及分数情况
1SELECT
2 st.s_id,
3 st.s_name,
4 c.c_name,
5 sc.s_score
6FROM
7 student st
8 LEFT JOIN score sc ON sc.s_id = st.s_id
9 LEFT JOIN course c ON c.c_id = sc.c_id
10ORDER BY
11 st.s_id,
12 c.c_name
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
1SELECT
2 st2.s_id,
3 st2.s_name,
4 c2.c_name,
5 sc2.s_score
6FROM
7 student st2
8 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
9 LEFT JOIN course c2 ON c2.c_id = sc2.c_id
10WHERE
11 st2.s_id IN (
12 SELECT
13 st.s_id
14 FROM
15 student st
16 LEFT JOIN score sc ON sc.s_id = st.s_id
17 GROUP BY
18 st.s_id
19 HAVING
20 min( sc.s_score )>= 70
21 )
22ORDER BY
23 s_id
37、查询不及格的课程
1SELECT
2 st.s_id,
3 c.c_name,
4 st.s_name,
5 sc.s_score
6FROM
7 student st
8 INNER JOIN score sc ON sc.s_id = st.s_id
9 AND sc.s_score < 60
10 INNER JOIN course c ON c.c_id = sc.c_id
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
1SELECT
2 st.s_id,
3 st.s_name,
4 sc.s_score
5FROM
6 student st
7 INNER JOIN score sc ON sc.s_id = st.s_id
8 AND sc.c_id = "01"
9 AND sc.s_score >= 80
39、求每门课程的学生人数
1SELECT
2 c.c_id,
3 c.c_name,
4 count( 1 )
5FROM
6 course c
7 INNER JOIN score sc ON sc.c_id = c.c_id
8GROUP BY
9 c.c_id
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
1SELECT
2 st.*,
3 c.c_name,
4 sc.s_score,
5 t.t_name
6FROM
7 student st
8 INNER JOIN score sc ON sc.s_id = st.s_id
9 INNER JOIN course c ON c.c_id = sc.c_id
10 INNER JOIN teacher t ON t.t_id = c.t_id
11 AND t.t_name = "墨白"
12ORDER BY
13 sc.s_score DESC
14 LIMIT 0,1
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
1SELECT
2 st.s_id,
3 st.s_name,
4 sc.c_id,
5 sc.s_score
6FROM
7 student st
8 LEFT JOIN score sc ON sc.s_id = st.s_id
9 LEFT JOIN course c ON c.c_id = sc.c_id
10WHERE
11 (
12 SELECT
13 count( 1 )
14 FROM
15 student st2
16 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
17 LEFT JOIN course c2 ON c2.c_id = sc2.c_id
18 WHERE
19 sc.s_score = sc2.s_score
20 AND c.c_id != c2.c_id
21 )>1
42、查询每门功成绩最好的前两名
1SELECT
2 a.*
3FROM
4 (
5 SELECT
6 st.s_id,
7 st.s_name,
8 c.c_name,
9 sc.s_score
10 FROM
11 student st
12 LEFT JOIN score sc ON sc.s_id = st.s_id
13 INNER JOIN course c ON c.c_id = sc.c_id
14 AND c.c_id = "01"
15 ORDER BY
16 sc.s_score DESC
17 LIMIT 0,
18 2
19 ) a UNION ALL
20SELECT
21 b.*
22FROM
23 (
24 SELECT
25 st.s_id,
26 st.s_name,
27 c.c_name,
28 sc.s_score
29 FROM
30 student st
31 LEFT JOIN score sc ON sc.s_id = st.s_id
32 INNER JOIN course c ON c.c_id = sc.c_id
33 AND c.c_id = "02"
34 ORDER BY
35 sc.s_score DESC
36 LIMIT 0,
37 2
38 ) b UNION ALL
39SELECT
40 c.*
41FROM
42 (
43 SELECT
44 st.s_id,
45 st.s_name,
46 c.c_name,
47 sc.s_score
48 FROM
49 student st
50 LEFT JOIN score sc ON sc.s_id = st.s_id
51 INNER JOIN course c ON c.c_id = sc.c_id
52 AND c.c_id = "03"
53 ORDER BY
54 sc.s_score DESC
55 LIMIT 0,
56 2
57 ) c
58 -- 借鉴(更准确,漂亮):
59SELECT
60 a.s_id,
61 a.c_id,
62 a.s_score
63FROM
64 score a
65WHERE
66 ( SELECT COUNT( 1 ) FROM score b WHERE b.c_id = a.c_id AND b.s_score >= a.s_score )<= 2
67ORDER BY
68 a.c_id
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
1SELECT
2 sc.c_id,
3 count( 1 )
4FROM
5 score sc
6 LEFT JOIN course c ON c.c_id = sc.c_id
7GROUP BY
8 c.c_id
9HAVING
10 count( 1 )> 5
11ORDER BY
12 count( 1 ) DESC,
13 sc.c_id ASC
44、检索至少选修两门课程的学生学号
1SELECT
2 st.s_id
3FROM
4 student st
5 LEFT JOIN score sc ON sc.s_id = st.s_id
6GROUP BY
7 st.s_id
8HAVING
9 count( 1 )>=2
45、查询选修了全部课程的学生信息
1SELECT
2 st.*
3FROM
4 student st
5 LEFT JOIN score sc ON sc.s_id = st.s_id
6GROUP BY
7 st.s_id
8HAVING
9 count( 1 )=(
10 SELECT
11 count( 1 )
12 FROM
13 course
14 )
46、查询各学生的年龄
1SELECT
2 st.*,
3 timestampdiff(
4 YEAR,
5 st.s_birth,
6 now())
7FROM
8 student st
47、查询本周过生日的学生
1SELECT
2 st.*
3FROM
4 student st
5WHERE
6 WEEK (
7 now())= WEEK (
8 date_format( st.s_birth, '%Y%m%d' ))
48、查询下周过生日的学生
1SELECT
2 st.*
3FROM
4 student st
5WHERE
6 WEEK (
7 now())+ 1 = WEEK (
8 date_format( st.s_birth, '%Y%m%d' ))
49、查询本月过生日的学生
1SELECT
2 st.*
3FROM
4 student st
5WHERE
6 MONTH (
7 now())= MONTH (
8 date_format( st.s_birth, '%Y%m%d' ))
50、查询下月过生日的学生
1SELECT
2 st.*
3FROM
4 student st
5WHERE
6 MONTH (
7 timestampadd(
8 MONTH,
9 1,
10 now()))= MONTH (
11 date_format( st.s_birth, '%Y%m%d' ))
12 -- 或
13SELECT
14 st.*
15FROM
16 student st
17WHERE
18 ( MONTH ( now()) + 1 ) MOD 12 = MONTH (
19 date_format( st.s_birth, '%Y%m%d' ))
JAVA资料（4.48G）.PDF领取
推荐阅读
【实战原创】SpringBoot应用docker化并发布到远程服务器
推荐程序员必备微信号
▼
JAVA
微信号：
javabaiwen
推荐理由：
在多学一点知识，就可以少写一行代码！专注于技术资源分享，经验交流，最新技术解读，另有海量免费电子书以及成套学习资源，关注JAVA，做技术得先驱者。▼长按下方↓↓↓二维码识别关注
zaikan?
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
