网传的SQL50题

目录

SQL50题 （MySQL版）

参考博文：sql语句练习50题(Mysql版)

推荐丛书：《SQL必知必会》

配置：win10家庭版、MySQL5.6

说明：因为是从Typora笔记手动复制粘贴过来的，所以SQL的排版、图片的大小，并不很合适。

总结：

说明：这是我做完后自己总结的，不适合所有人，且尤其不适合一点都没做就看这个总结，适合都做完，并作了自己的总结后，来这里分享经验。

​ 对于普通的开发人员来说，建表及删改表、数据、用户等操作，无需深度掌握，以个人测试练习为要求即可，但是必须熟练掌握SQL的查询语句。

​ 其中，select后面接所需的查询结果的各个字段；from后面接用来查询的最终数据表（可能需要按照查询需求自行构建）。

​ from部分，即最终表的构建思路，主要是依据所要查询的字段进行构建。在实现查询SQL时，可以先写from部分，也可以select部分与from部分“随机应变”，如果十拿九稳，比如查询原表的原字段，则可以先写select部分，再写from部分。

​ 构建最终表的思路：

  • 如果最终表是数据库原表，或其他简单的形式，则直接莽上去即可。
  • 拆表分组：要对隶属于不同组的同一字段下的数据进行比较，如，第1题，等。
  • group by分组：要对分组后的每组数据用聚集函数进行计算，如，第3、11、15题。

​ 要注意的是，在构建最终表时：

  • 内联结等价于直接联结，无论是联结后的表结构，还是查询效率，二者只是SQL语法上的不同。
  • 没有关联条件的两个表进行联结，或存在关联条件但未说明，则返回结果为笛卡尔积——检索出的行的数目 = 第一个表的行数 × 第二个表的行数——所以，使用内联结，或直接联结时，一定要写关联条件！
  • 外联结所得到的联结表，包含没有联结条件的那些行（如，A左外联结B，则包含A表中与B没有联结条件的那些行）——A左外联结B，等价于B右外联结A，不等价于A右外联结B。
  • 如果过滤条件是null值，则只能使用 is、is not 来表示等于、不等于，不能使用 =、!=，或 <> 等符号。
  • where子句不支持连等操作（如，a = b = c，或a = b = c = d），须使用and操作符来连接多个比较操作（如，a = b and b = c，或a > b and b = c）。

​ 最后，所谓“一回生，二回熟，三回四回熟能生巧”，写SQL其实就是将实际问题转换为SQL，而这种思考回路须要大量练习来形成。

测试数据：

  1. 学生：

    姓名 性别 出生年月日
    赵雷 男 1990-01-01
    钱电 男 1990-12-21
    孙风 男 1990-05-20
    李云 男 1990-08-06
    周梅 女 1991-12-01
    吴兰 女 1992-03-01
    郑竹 女 1989-07-01
    王菊 女 1990-01-20

  2. 课程及授课教师：

    课程名 授课教师
    数学 张三
    语文 李四
    英语 王五

  3. 成绩：

    • 赵雷：数学-90；语文-80；英语-99。
    • 钱电：数学-60；语文-70；英语-80。
    • 孙风：数学-80；语文-80；英语-80。
    • 李云：数学-30；语文-50；英语-20。
    • 周梅：数学-未选课；语文-76；英语-87。
    • 吴兰：数学-未选课；语文-31；英语-34。
    • 郑竹：数学-未选课；语文-98；英语-89。
    • 王菊：数学-未选课；语文-未选课；英语-未选课。

问题要求：

00. 按照下列要求在数据库中创建表，并导入测试数据。

–1.学生表 
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 
–2.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
–3.课程表 
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

  1. 建表：

    #学生表
CREATE TABLE student (
  s_id int(11) NOT NULL AUTO_INCREMENT,
  s_name varchar(20) NOT NULL DEFAULT '',
  s_sex varchar(20) NOT NULL DEFAULT '',
  s_birth varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (s_id));
#教师表
CREATE TABLE teacher (
  t_id int(11) NOT NULL AUTO_INCREMENT,
  t_name varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (t_id));
#课程表
CREATE TABLE course (
  c_id int(11) NOT NULL AUTO_INCREMENT,
  c_name varchar(20) NOT NULL DEFAULT '',
  t_id int(11) NOT NULL,
  PRIMARY KEY (c_id),
  KEY t_id (t_id),
  FOREIGN KEY (t_id) REFERENCES teacher (t_id));
#分数表
CREATE TABLE score (
  s_id int(11) NOT NULL,
  c_id int(11) NOT NULL,
  s_core varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (s_id,c_id))

    复习：

    • 表级约束：PRIMARY KEY、FOREIGN KEY、UNIQU。
    • 列级约束：PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、AUTO_INCREAMENT。
    • 建表推荐写法：列级约束与对应列同一行，表级约束单独一行。

  2. 导入测试数据：SQL，略。

01. 查询“02”课程比“01”课程成绩高的学生的信息及课程分数。

  1. SQL语句：

    SELECT stu.*, sc1.s_core AS '课程1', sc2.s_core AS '课程2'
FROM student stu 
     INNER JOIN score sc1 ON stu.s_id = sc1.s_id AND sc1.c_id = 1
	INNER JOIN score sc2 ON stu.s_id = sc2.s_id AND sc2.c_id = 2
WHERE sc1.s_core < sc2.s_core

  2. SQL解析 ：

    ​ 由score表的表结构可知，所有课程的成绩统一存放在s_core字段下，

    想要查询出“02”课程比“01”课程成绩高的结果集，但是所有学生的所有课程成绩都存储在score表的s_core字段下，所以选择拆表分组来实现比较。

    ​ 拆表分组：将score表中只含有“01”课程的数据拆分出来作为表sc1，将score表中只含有“02”课程的数据拆分出来作为表sc2，将sc1表与sc2表进行内联结，得到联结表中存在sc1.s_core字段与sc2.s_core字段，这样就可以使用过滤条件“02课程比01课程成绩高”。

    ​ 注意，内联结的两个表，无需在意谁联结谁，得到的联结表的表结构一致。外联结的两个表，须在意谁联结谁，得到的表结构可能不一样，这是外联结的特点所决定的——设A表左外联结B表，则联结表中包含A表中与B表无关联条件（如，为null值）的所有行。

    ​ 本解题只选择比较同时选择了“01”和“02”课程的学生，所以联结时选择使用内联结。

    ​ 另一种写法：使用直接联结替换内联结——内联结等价直接联结，无论是联结后的表结构，还是查询效率，二者都相同，只是SQL语法不同。

    SELECT stu.*, sc1.s_core AS '课程1', sc2.s_core AS '课程2'
FROM student stu, score sc1, score sc2 
WHERE stu.s_id = sc1.s_id AND stu.s_id = sc2.s_id 
	AND sc1.c_id = 1 AND sc2.c_id = 2
	AND sc1.s_core < sc2.s_core

    或

    SELECT stu.*, sc1.s_core AS '课程1', sc2.s_core AS '课程2'
FROM student stu 
	JOIN score sc1 ON stu.s_id = sc1.s_id AND sc1.c_id = 1
	JOIN score sc2 ON stu.s_id = sc2.s_id AND sc2.c_id = 2
WHERE sc1.s_core < sc2.s_core

    ​ 注意：直接联结两个表，如果未在where子句中声明关联条件，或是就不存在关联条件，则返回结果为笛卡尔积——构建出的表的行数 = 第一个表的行数 × 第二个表的行数。

02. 查询“02”课程比“01”课程成绩低的学生的信息及课程分数。

  1. SQL语句：

    SELECT stu.*, sc1.s_core AS '课程1', sc2.s_core AS '课程2'
FROM student stu 
	INNER JOIN score sc1 ON stu.s_id = sc1.s_id AND sc1.c_id = 1
	INNER JOIN score sc2 ON stu.s_id = sc2.s_id AND sc2.c_id = 2
WHERE sc1.s_core > sc2.s_core

  2. SQL解析：略，参考第一题。

03. 查询平均成绩大于等于60的学生编号、姓名、平均成绩。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name
		,AVG(sc.s_core) AS '平均成绩'
FROM student stu 
	INNER JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id 
HAVING AVG(sc.s_core) > 60

  2. SQL解析：

    • 解析：

      ​ 由score表的表结构可知，所有学生的所有成绩都存放在s_core字段下，所以，想要求各个学生的平均成绩，必须使用group by按照s_id进行分组——一个s_id对应一个平均成绩，是为一组（不是查询一个s_id对应的各科的s_core，此为一组）。

      ​ 由题干可知，过滤条件为“平均成绩大于60”，即，是在分组完成后进行过滤，所以选择having进行过滤。

    • 辨析：

      ​ where操作符作用位置在from后，group by前；having操作符作用位置在group by后。

      ​ where是行级过滤，在分组前进行过滤；having是组级过滤，在分组后进行过滤。因此，使用where过滤掉的行，会影响到having的过滤操作（因为改变了计算值）。所以，在需要同时使用where和having时，要考虑到where对having的过滤影响。

    • 优化：

      ​ 使用round函数对平均成绩进行四舍五入，使数据更简洁。

      • 语法：ROUND(number,num_digits)

      • number：数值；num_digits：保留的小数位。

      • ROUND(number)：默认保留小数位为0。

      SELECT stu.s_id,stu.s_name
		,ROUND(AVG(sc.s_core),1)AS'平均成绩'
FROM student stu 
	LEFT JOIN score sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id 
HAVING AVG(sc.s_core) > 60

      PS：也可使用cast函数 + decimal数据类型来完成四舍五入

      SELECT stu.s_id, stu.s_name
			,CAST(AVG(sc.s_core) AS DECIMAL(10,2)) AS '平均成绩'
FROM student stu, score sc
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING AVG(sc.s_core) > 60

      其中：

      1. decimal数据类型：

        参考博文：MySQL数据类型DECIMAL用法。

        • 作用：保留准确精准度的列，如会计系统中的货币数据。

        • 语法：column_name DECIMAL(P,D)

          ​ 其中，P表示有效数字数的精度，范围1〜65；D表示小数点后的位数，范围为0~30。P和D都是正整数。MySQL要求D小于等于(<=)P。MySQL允许以下用法：

          1. column_name DECIMAL(P)：默认D = 0。
          2. column_name DECIMAL：默认P = 10且D = 0。

      2. cast函数：

        参考博文：MYSQL中，CAST函数的使用规则，mysql cast()函数。

        • 作用：将指定字段下的记录数据转换为指定的数据类型。

        • 语法：cast(字段名 as 数据类型)

          ​ 其中，常用的数据类型有：

          1. char[(n)]：字符数据类型。
          2. date：日期数据类型。
          3. datetime：日期和时间数据类型。
          4. time：时间数据类型。
          5. signed：int数据类型。
          6. decimal：float数据类型。

      ​ 辨析 - case函数与cast函数：

      • cast函数：将指定字段下的记录数据转换为指定的数据类型。

      • case函数：与 if...eles... 相似，起判断作用（联想记忆：switch分支语句），只要一个判断满足了，后面剩下的case部分将会被自动忽略，不再匹配。

        参考博文：MySQL函数之CASE、MySQL case函数的详细用法。

        1. case函数的语法：

          case [column_name] when [value1] then [result1] ... else [default] end

        2. case函数的两种写法：

          • 简单case函数：照搬语法写。

            CASE hero_name 
WHEN '盖伦' THEN '上单'
WHEN '拉克丝' THEN '中单'
WHEN '金克斯' THEN 'ADC'
WHEN '琴女' THEN '辅助'
WHEN '盲僧' THEN '打野'
ELSE '混子' END

          • case搜索函数：不写case后的[column_name]。

            (CASE 
WHEN age >= 18 THEN '成年'
WHEN age < 18 THEN '未成年'
ELSE '保密' END) AS '是否成年'

04. 查询平均成绩小于60的学生编号、姓名、平均成绩（包括无成绩的学生）。

  1. SQL语句：

    SELECT stu.s_id,stu.s_name
		,ROUND(AVG(sc.s_core),1) AS '平均成绩'
FROM student stu 
	LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id 
HAVING AVG(sc.s_core) < 60

  2. SQL解析：略，参考第3题。

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

  1. SQL语句：

    SELECT stu.s_id, stu.s_name
			, COUNT(sc.s_core) AS '选课总数'
			, SUM(sc.s_core) AS '总成绩'
FROM student stu 
	LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id

  2. SQL解析：

    • 解析：

      ​ 由题干可知，需要对score表按照学生ID进行分组，然后才能使用聚集函数求得各学生的“选课总数”、“总成绩”。

      ​ 注意，本题需考虑到那些未选课的学生，否则结果集未包含全部学生，所以要使用外联结来构建最终表。

      ​ 虽然常用 COUNT(1) 来实现计数功能，但是本题须使用 COUNT(s_core)，因为，根据score表的表结构可知，score表只包含选课的数据，想要得到所有学生各选了那些课程，须student表外联结score表，其中，课程成绩为null值，是该学生未选择该课程的唯一判断依据。

    • 聚集函数count的使用：

      参考博文：count(1)、count(*)与count(列名)的执行区别，count(1)这里面的1是什么意思，详解select count(1)的意思。

      1. count(字段)、count(1)、count(*)的作用辨析：都起计数作用。

        • count(字段)：计算指定字段下的行数/记录数，不包括该字段下为null值的行/记录。

        • count(1)：计算一共有多少符合条件的行数/记录数，包括为null值的行/记录。

          这里的 “1”，可理解为，有这么一个字段，其字段名的为 1。事实上，也可以是count(x)，x表示任意实数，使用上没影响。

        • count(*)：同count(1)，但不包括为null值的行/记录。

      2. count(字段)、count(1)、count(*)的效率比较：

        • 现在的DBMS能够自动做优化选择，是走索引还是全表，所以三者的效率一样。
        • 过去的DBMS不会自动做优化，效率上因字段是否有索引而需程序员自行选择——字段为主键，或有索引时，count(字段)效率最高，其次为count(1)，最后为count(*)，因为count(*)执行时会将星号翻译成字段的具体名字，所以比count(1)的速度稍稍微微地慢一点点；若字段不是主键或没有索引，count(*)的效率最高。

    • 补充——过滤条件中，对null值的判定（mysql中）：

      ​ !=、<>等判断符对null值进行判定，都不好使，须使用 is，或 is not，对null值进行判定。

06. 查询“李”姓老师的数量。

  1. SQL语句：

    SELECT COUNT(1) #count(*)，或count(teacher表中的任一字段)，都可以
FROM teacher 
WHERE t_name LIKE '李%'

  2. SQL解析：

    ​ MySQL使用 like 操作符来实现模糊查询，通配符常为 % 和 _ 。

    • %通配符：最常用，一个 % 在搜索串中表示任意字符出现任意次数，包括空格，不包括null值；

    • _通配符：一个 _ 在搜索串中表示任意字符只出现一次，包括空格，不包括null值。并且，DBMS默认区分模糊查询的搜索串的大小写。

    • like操作符前面可接not操作符：查询不包含模糊条件的结果集。

    • 注意：最好不要在计算字段中使用like操作符进行模糊查询，很影响效率。

07. 查询学过“张三”老师课程的学生信息。

  1. SQL语句：

    SELECT st.*
FROM student st 
	INNER JOIN score sc ON st.s_id = sc.s_id
	INNER JOIN course cs ON sc.c_id = cs.c_id
	INNER JOIN teacher te ON cs.t_id = te.t_id
WHERE te.t_name LIKE '张%'

  2. SQL解析：略。

08. 查询没学过“张三”老师课程的学生信息。

  1. SQL语句：

    SELECT stu.*
FROM student stu 
WHERE stu.s_id NOT IN (
	SELECT st.s_id
	FROM student st 
		INNER JOIN score sc ON st.s_id = sc.s_id
		INNER JOIN course cs ON sc.c_id = cs.c_id
		INNER JOIN teacher te ON cs.t_id = te.t_id
	WHERE te.t_name LIKE '张%')

  2. SQL解析 ：

    ​ 本题易错点是，过滤条件为 WHERE te.t_name NOT LIKE '张%' 。要注意到，选择张姓老师课程的学生，同时也可能选择其他老师的课程，若按照上述过滤条件进行查询，则得到的结果集为未选过张姓老师课程的学生集合 + 选过张姓老师课程的学生选择非张姓老师课程的集合。

    ​ 本题可这样思考：设所有学生为全集U，选过张姓老师课程的学生集合为A，未选过张姓老师课程的学生集合为B，则A与B互为补集。鉴于无法直接过滤掉“选过张姓老师课程的学生选择非张姓老师课程的集合”，故使用B = U - A的关系来过滤。

    ​ 其中，A集合即是第7题的查询结果集，U集合即是整个student表的表数据。所以，使用子查询求得A集合的s_id作为结果集，过滤条件为 where s_id not in A集合的s_id结果集。

    ​ 注意，子查询只能查询一个列，不能查询多个列（否则报语法错误）。

09. 查询同时学过“01”和“02”课程的学生信息。

  1. SQL语句：

    SELECT stu.*
FROM student stu 
	INNER JOIN score sc1 ON stu.s_id = sc1.s_id AND sc1.c_id = 1
	INNER JOIN score sc2 ON stu.s_id = sc2.s_id AND sc2.c_id = 2

    或

    SELECT stu.*
FROM student stu 
	INNER JOIN score sc1 ON stu.s_id = sc1.s_id 
	INNER JOIN score sc2 ON stu.s_id = sc2.s_id 
WHERE sc1.c_id = 1 AND sc2.c_id = 2

    或

    SELECT DISTINCT stu.*
FROM student stu, score sc1, score sc2
WHERE stu.s_id = sc1.s_id 
	AND stu.s_id = sc2.s_id 
	AND sc1.c_id = 1 
	AND sc2.c_id = 2

  2. SQL解析：

    ​ 解题思路是，由score表的表结构可知，某一学生选过的所有课程的成绩统一放在s_core字段下，想要使用过滤条件“同时学过01和02课程”，就必须使最终表里包含两个字段，分别对应“01”课程、“02“课程，所以需要对score表进行拆分。

    ​ 本题不能使用group by + having来实现查询。若使用group by按照s_id对score表进行分组后，一行数据只有一个c_id，过滤条件“同时学过01和02课程”无法使用。

10. 查询学过“02”但没学过“01”课程的学生信息。

  1. SQL语句：

    SELECT stu.*
FROM student stu, score sc
WHERE stu.s_id = sc.s_id AND sc.c_id = 2
	AND stu.s_id NOT IN(
		SELECT st.s_id
		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 = 1 
        	AND sc2.c_id = 2)

  2. SQL解析 ：

    ​ 本题思路是，设都学过02课程的学生集合做全集U，同时学过01和02课程的学生集合为集合A，则满足“学过“02”但没学过“01”课程”的学生集合B = U-A。

    ​ 使用子查询查得A集合，使用 where s_id not in A集合的所有s_id 来得到所求的结果集。

11. 查询没学全部课程的学生信息。

  1. SQL语句：

    SELECT stu.*
FROM student stu 
WHERE stu.s_id not IN(
		SELECT st.s_id
		FROM student st 
		INNER JOIN score sc1 ON st.s_id = sc1.s_id AND sc1.c_id = 1
		INNER JOIN score sc2 ON st.s_id = sc2.s_id AND sc2.c_id = 2
		INNER JOIN score sc3 ON st.s_id = sc3.s_id AND sc3.c_id = 3)

    或

    SELECT stu.*
FROM student stu LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING COUNT(sc.c_id) != 3

  2. SQL解析：

    ​ 常规思路，设全集C为所有学生，补集A为学了全部课程的学生，则所要查询的学生集合B = C- A。据此构建最终表即可。

    ​ 或，使用group by分组：student表左外联结score表，按s_id进行分组，过滤出c_id数量不为3的组（使用聚集函数count计数）。

12. 查询与学号为“01”的学生至少有一门选课相同的学生信息。

  1. SQL语句：

    SELECT DISTINCT stu.*
FROM student stu LEFT JOIN score sc ON stu.s_id = sc.s_id AND stu.s_id != 1
WHERE sc.c_id IN (
	SELECT sc2.c_id
	FROM student st LEFT JOIN score sc2 ON st.s_id = sc2.s_id AND st.s_id = 1)

  2. SQL解析：

    ​ 解题思路，使用子查询得到s_id = 1的学生的所有选课c_id，使用 in 操作符实现过滤条件“与学号为“01”的学生至少有一门选课相同”。

    ​ 注意，由原始表score的表结构可知，一个s_id可能对应多个c_id，所以需要对最终的查询结果要进行去重，distinct。

13. 查询与学号为“01”的学生所有选课相同的学生信息。

  1. SQL语句：

    SELECT stu.*
FROM student stu INNER JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING GROUP_CONCAT(sc.c_id) = (
	SELECT GROUP_CONCAT(sc2.c_id)
	FROM student st INNER JOIN score sc2 ON st.s_id = sc2.s_id AND st.s_id = 1
)

  2. SQL解析：

    ​ 解题思路，得到每个的学生的所有选课c_id作为一个字段下的一条数据，然后过滤出所查询的结果集。

    ​ 本题难点在于，如何将每个的学生的所有选课c_id作为一个字段下的一条数据。

    ​ 在将student表左外联结score表，使用group by按照s_id进行分组后，使用group_concat函数，将每组的c_id字段下的字符串连接起来成一行数据，再进行过滤。

    • 语法：group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])
    • 作用：将每组的同一字段下的不同行的结果字符串连接起来成一行数据。

    参考博文：mysql之group_concat函数详解。

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

  1. SQL语句：

    SELECT stu.s_name
FROM student stu 
WHERE stu.s_id NOT IN (
	SELECT st.s_id
	FROM student st 
		INNER JOIN score sc2 ON st.s_id = sc2.s_id 
		INNER JOIN course cs ON sc2.c_id = cs.c_id
		INNER JOIN teacher te ON cs.t_id  = te.t_id AND te.t_name = '张三')

  2. SQL解析：

    ​ 解题思路是，设所有学生的s_id集合为全集C，选择张姓老师课程的所有学生s_id集合为A集合，则所查询的集合B = C - A。使用not in来实现过滤。

15. 查询两门及以上成绩不合格的学生学号、姓名、平均成绩。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name, AVG(sc.s_core)
FROM student stu 
	INNER JOIN score sc ON stu.s_id = sc.s_id
WHERE stu.s_id IN(
	SELECT sc2.s_id
	FROM score sc2
	WHERE sc2.s_core < 60
	GROUP BY sc2.s_id 
	HAVING COUNT(1) >= 2)

  2. SQL解析：

    ​ 解题思路是，使用group by按照s_id对score表中s_core < 60的数据进行分组，分组后过滤出行数 ≥ 2的组（使用聚集函数count计数）。

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

  1. SQL语句：

    SELECT stu.*
FROM student stu 
	INNER JOIN score sc ON stu.s_id = sc.s_id AND sc.c_id = 2
WHERE sc.s_core < 60
ORDER BY sc.s_core DESC

  2. SQL解析：

    ​ order by 操作符起到排序作用，永远位于一条SQL查询语句的最后（如果没有limit子句），且是一条查询语句中最后执行的子句（如果没有limit子句），语法为 ORDER BY 字段名 ASC/DESC，其中，asc表示升序排序，是自然排序，desc是降序排序。

17. 显示所有学生的各科成绩及平均成绩，按平均成绩降序。

  1. SQL语句：

    SELECT stu.s_name, sc1.s_core AS'数学', sc2.s_core AS'语文', sc3.s_core AS '英语',
			 AVG(sc.s_core) AS '平均成绩'
FROM student stu
	LEFT JOIN score sc1 ON stu.s_id = sc1.s_id AND sc1.c_id = 1
	LEFT JOIN score sc2 ON stu.s_id = sc2.s_id AND sc2.c_id = 2
	LEFT JOIN score sc3 ON stu.s_id = sc3.s_id AND sc3.c_id = 3
	LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
ORDER BY AVG(sc.s_core) DESC

  2. SQL解析：

    ​ 注意，as是接别名关键字，其使用：

    1. as 别名：不建议使用，譬如，可能会与系统关键字冲突，或者，在别名中添加诸如空格等的标点符号时，报错。
    2. as '别名'：单引号表示字符串，常用。
    3. as [别名]：方括号表示避免与系统关键字冲突。

    参考博文：sql 语句中as的用法和作用。

    ​ 可选择使用round函数或cast函数 + decimal数据类型来对平均成绩进行四舍五入，SQL略，参考第3题。

18. 查询各科的课程编号、课程名称、最高分、最低分、平均分、及格率、中等率、优良率、优秀率（及格>=60，中等70-80，优良80-90，优秀>=90）。

  1. SQL语句：

    SELECT cs.c_id, cs.c_name
			 ,MAX(sc.s_core) AS '最高分'
			 ,MIN(sc.s_core) AS '最低分'
			 ,CAST(AVG(sc.s_core) AS DECIMAL(10,2)) AS '平均分'
       ,((SELECT COUNT(s_id) FROM score WHERE s_core >= 60 AND c_id = cs.c_id)
					/(SELECT COUNT(s_id) FROM score where c_id = cs.c_id)) AS '及格率'
       ,((SELECT COUNT(s_id) FROM score WHERE s_core >= 70 AND s_core < 80 AND c_id = cs.c_id)
					/(SELECT COUNT(s_id) FROM score where c_id = cs.c_id)) AS '中等率'
       ,((SELECT COUNT(s_id) FROM score WHERE s_core >= 80 AND s_core < 90 AND c_id = cs.c_id)
					/(SELECT COUNT(s_id) FROM score where c_id = cs.c_id)) AS '优良率'
       ,((SELECT COUNT(s_id) FROM score WHERE s_core >= 90 AND c_id = cs.c_id)
					/(SELECT COUNT(s_id) FROM score where c_id = cs.c_id)) AS '优秀率'
FROM course cs,score sc
WHERE cs.c_id = sc.c_id
GROUP BY cs.c_id

  2. SQL解析 ：

    ​ 解题思路是，使用子查询作为计算字段，求出分组后的及格率、中等率、优良率、优秀率。

    ​ 注意，子查询可以使用主查询里的表。

19. 按各科成绩排序，并显示排名。

  1. SQL语句：

    使用set语句声明用户自定义变量。

    SET @i := 0;
SET @ii := 0;
SET @iii := 0;
SELECT tb1.c_id, tb1.c_name, tb1.s_id, tb1.s_name, tb1.s_core, @i := @i + 1 AS '排名'
FROM (SELECT cs.c_id, cs.c_name, sc1.s_id, stu.s_name, sc1.s_core
			FROM course cs, score sc1, student stu
			WHERE cs.c_id = sc1.c_id AND cs.c_id = 1 AND stu.s_id = sc1.s_id 
			ORDER BY sc1.s_core DESC) AS tb1
UNION ALL
SELECT tb2.c_id, tb2.c_name, tb2.s_id, tb2.s_name, tb2.s_core, @ii := @ii + 1 AS '排名'
FROM  (SELECT cs.c_id, cs.c_name, sc2.s_id, stu.s_name, sc2.s_core
			FROM course cs, score sc2, student stu
			WHERE cs.c_id = sc2.c_id AND cs.c_id = 2 AND stu.s_id = sc2.s_id 
			ORDER BY sc2.s_core DESC) AS tb2
UNION ALL
SELECT tb3.c_id, tb3.c_name, tb3.s_id, tb3.s_name, tb3.s_core, @iii := @iii + 1 AS '排名'
FROM (SELECT cs.c_id, cs.c_name, sc3.s_id, stu.s_name, sc3.s_core
			FROM course cs, score sc3, student stu
			WHERE cs.c_id = sc3.c_id AND cs.c_id = 3 AND stu.s_id = sc3.s_id 
			ORDER BY sc3.s_core DESC) AS tb3

    或，使用 select @变量名 = 初始值 声明自定义变量。

    SELECT tb1.c_id, tb1.c_name, tb1.s_id, tb1.s_name, tb1.s_core, @i := @i + 1 AS '排名'
FROM (SELECT cs.c_id, cs.c_name, sc1.s_id, stu.s_name, sc1.s_core
			FROM course cs, score sc1, student stu
			WHERE cs.c_id = sc1.c_id AND cs.c_id = 1 AND stu.s_id = sc1.s_id 
			ORDER BY sc1.s_core DESC) AS tb1,
			(SELECT @i := 0) AS i1
UNION ALL
SELECT tb2.c_id, tb2.c_name, tb2.s_id, tb2.s_name, tb2.s_core, @ii := @ii + 1 AS '排名'
FROM  (SELECT cs.c_id, cs.c_name, sc2.s_id, stu.s_name, sc2.s_core
			FROM course cs, score sc2, student stu
			WHERE cs.c_id = sc2.c_id AND cs.c_id = 2 AND stu.s_id = sc2.s_id 
			ORDER BY sc2.s_core DESC) AS tb2,
			(SELECT @ii := 0) AS i2
UNION ALL
SELECT tb3.c_id, tb3.c_name, tb3.s_id, tb3.s_name, tb3.s_core, @iii := @iii + 1 AS '排名'
FROM (SELECT cs.c_id, cs.c_name, sc3.s_id, stu.s_name, sc3.s_core
			FROM course cs, score sc3, student stu
			WHERE cs.c_id = sc3.c_id AND cs.c_id = 3 AND stu.s_id = sc3.s_id 
			ORDER BY sc3.s_core DESC) AS tb3,
			(SELECT @iii := 0) AS i3

  2. SQL解析：

    ​ 本题的难点在于实现排名。

    ​ 首先，分别查询各科的成绩排序，各查询之间使用union all操作符来组合成一个结果集。

    ​ 组合查询：

    1. 使用规则：
      • 至少是两个查询相组合，查询之间使用union连接。
      • 相组合的查询，须包含相同的列、表达式或聚合函数。
      • 相组合的查询，列数据的数据类型必须兼容。
    2. union与union all之间的区别：
      • union操作符：组合后的结果集，去重。
      • union all操作符：组合后的结果集，不去重。
    3. 排序：在最后一行使用order by不是对最后一个查询进行排序，而是对整个组合所得的结果集进行排序。
    4. 注意：组合查询中的各个查询语句间不能使用英文分号 ; ，其位置由union或union all替代。

    ​ 其次，因为MySQL不支持rank函数，所以查询排名须要自行实现——使用MySQL的用户自定义变量来实现——定义一个自定义变量作计算字段，初始值为0，变量值依行数自增（前提，查询的最终表的数据按成绩降序排序）。

    ​ MySQL的用户自定义变量——会话变量 @变量名：

    PS：MySQL的用户自定义全局变量、局部变量，略，详见参考博文：深入MySQL用户自定义变量：使用详解及其使用场景案例。

    参考博文：MySQL定义变量，Mysql自定义变量的使用，mysql :=和=的区别，深入MySQL用户自定义变量：使用详解及其使用场景案例。

    1. 声明/定义一个用户自定义变量：set @变量名 = 初始值;

      注意：同时执行两条及以上的SQL语句时，SQL语句之间使用英文分号 ; 分隔，组合查询使用union或union all分隔；当前连接断开时，声明/定义的MySQL用户自定义会话变量会消失。

    2. 查询用户自定义变量：select @变量名 [:= 表达式]

      注意：若不使用set语句声明用户自定义变量，则须联结一个由子查询 select @变量名 := 初始值 所得到的表来构成最终表——不推荐！

    3. 说明 - MySQL中 := 和 =的区别：

      • MySQL中， = 只在set和update语句中表示“赋值”，在其他语句中表示“相等”、“等于”。
      • MySQL中， := 在任何语句中都表示“赋值”。建议使用MySQL用户自定义会话变量时，只使用 := 进行赋值。
      • MySQL中，不允许使用自增自减符号——++、--。

    4. 会话变量 @变量名 作为计算字段时，在SQL语句中的执行次序：

      ​ 会话变量 @变量名 作为计算字段，则该计算字段在where子句后执行，在group by子句前执行。

      PS - 一条SQL查询语句中的子句执行次序：

      • select子句 —> from子句 —> where子句 —> group by子句 —> having子句 —> order by子句 —> limit子句。
      • 如果出现子查询，无论子查询出现在哪里，子查询中都可使用主查询from子句里面出现的表的数据。

    ​ 最后，在实现时遇到需要注意的问题：

    1. 问题：在子查询中为图省事儿，直接查询所有字段，即select *，但是进行组合查询时，报错“[Err] 1060 - Duplicate column name XXX”。经测试，若单独运行构成组合查询的任一条查询语句时，也报这个错误。
    2. 解决：产生问题的原因是，在本题的子查询中，如果查询全部字段，会有两个c_id字段——原始表course的 c_id 和原始表score的c_id（虽然在单独使用该子查询时，DBMS给第二个c_id字段命名为c_id1，但这只是为了方便显示的操作，该子查询实际得到的结果就是两个c_id字段）。问题的解决是，子查询时，不查询全部字段，而是查询所需要的的字段。

20. 查询学生的总成绩并进行排名。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name
	  ,CASE WHEN SUM(sc.s_core) IS NULL THEN 0
			ELSE SUM(sc.s_core) END AS '总成绩'
FROM student stu 
	LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
ORDER BY SUM(sc.s_core) DESC

  2. SQL解析：

    ​ 本题难点在于，需要在SQL语句中实现分支语句——使用case函数。

    ​ case函数：与 if...eles... 相似，起判断作用（联想记忆：switch分支语句），只要一个判断满足了，后面剩下的判断不再匹配。

    参考博文：MySQL函数之CASE、MySQL case函数的详细用法。

    1. case函数的语法：

      case [column_name] when [value1] then [result1] ... else [default] end

    2. case函数的两种写法：

      • 简单case函数：照搬语法写。

        CASE hero_name 
WHEN '盖伦' THEN '上单'
WHEN '拉克丝' THEN '中单'
WHEN '金克斯' THEN 'ADC'
WHEN '琴女' THEN '辅助'
WHEN '盲僧' THEN '打野'
ELSE '混子' END

      • case搜索函数：不写case后的[column_name]。

        (CASE 
WHEN age >= 18 THEN '成年'
WHEN age < 18 THEN '未成年'
ELSE '保密' END) AS '是否成年'

21. 查询不同老师所教授的不同课程，按平均分降序排序。

  1. SQL语句：

    SELECT te.t_id, te.t_name, cs.c_name, CAST(AVG(sc.s_core) AS DECIMAL(10,2)) AS '课程平均分'
FROM teacher te
	LEFT JOIN course cs ON te.t_id = cs.t_id
	LEFT JOIN score sc ON cs.c_id = sc.c_id
GROUP BY te.t_id
ORDER BY AVG(sc.s_core) DESC

  2. SQL解析：略。

22. 查询所有课程中成绩为第二名和第三名的学生信息及他们该课程的成绩。

  1. SQL语句：

    SELECT a.*
FROM (
	SELECT stu.s_id, stu.s_name, cs.c_name ,sc.s_core
	FROM student stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		INNER JOIN course cs ON sc.c_id = sc.c_id AND cs.c_id = 1
	ORDER BY sc.s_core DESC
	LIMIT 1,2
) AS a
UNION ALL
SELECT b.*
FROM (
	SELECT stu.s_id, stu.s_name, cs.c_name ,sc.s_core
	FROM student stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		INNER JOIN course cs ON sc.c_id = sc.c_id AND cs.c_id = 2
	ORDER BY sc.s_core DESC
	LIMIT 1,2
) AS b
UNION ALL
SELECT c.*
FROM (
	SELECT stu.s_id, stu.s_name, cs.c_name ,sc.s_core
	FROM student stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		INNER JOIN course cs ON sc.c_id = sc.c_id AND cs.c_id = 3
	ORDER BY sc.s_core DESC
	LIMIT 1,2
) AS c

  2. SQL解析：

    ​ 解题思路是，分别求取各课程的第2、3名学生信息及成绩，再使用组合查询union all进行组合。其中，求取第2、3名，可先使用order by进行desc排名，然后使用limit节选第2、3名。

    ​ limit子句：

    参考博文：mysql中的limit用法有哪些(推荐)，MYSQL limit用法。

    1. 语法：SELECT * FROM 表名 limit m,n;

    2. 使用：

      • 从m+1条记录行开始检索，取出n条数据（m从0计）。
      • n为-1时，表示从m+1行开始检索，直到取出最后一条数据。
      • 若只给出m，则表示从第1条记录行开始一共取出m条数据。

    3. 常用于分页查询：

      MySQL是中小型网站普遍使用的数据库之一，单表大约在2千万条记录（4G）下能够良好运行，经过数据库的优化后5千万条记录（10G）下运行良好。

      —— 参考博文：MySQL到底能支持多大的数据量？

      • 最基本的分页方式：

        ​ 适用于，查询一次在中小数据量（小数据量：查询一次 的数据量≤ 10万条数据）的情况下使用。

        ​ 使用前提，确保使用了索引。

        ​ 示例，设在articles表的category_id、 id两列上建立了复合索引，则：

        SELECT * 
FROM articles 
WHERE category_id = 123 
ORDER BY id 
LIMIT 50, 10

      • 子查询的分页方式：

        ​ 适用于，提高分页效率——越往后分页（100页之后），LIMIT语句的偏移量就会越大，速度也会明显变慢。

        ​ 使用方式，通过子查询快速定位分页查询的起始位置。

        ​ 示例，设在articles表的category_id、 id两列上建立了复合索引，已知要从articles表的第10001行开始查询10条数据，则：

        SELECT * 
FROM articles 
WHERE  id >=   
 (SELECT id 
  FROM articles  
  WHERE category_id = 123 
  ORDER BY id LIMIT 10000, 1)
LIMIT 10

      • JOIN分页方式：查询效率同子查询的分页查询，略。

23. 统计各科成绩的各分数段的人数及百分比，按如下形式显示：课程编号，课程名称，[100-85)，[85-70)，[70-60)，[0-60]。

  1. SQL语句：

    SELECT cs.c_id, cs.c_name
			 ,((SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id AND sc.s_core > 85 AND sc.s_core <= 100))
				 /(SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id) AS '[100-85)'
			 ,((SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id AND sc.s_core > 70 AND sc.s_core <= 85))
				 /(SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id) AS '[85-70)'
			 ,((SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id AND sc.s_core > 60 AND sc.s_core <= 70))
				 /(SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id) AS '[70-60)'
			 ,((SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id AND sc.s_core >= 60 AND sc.s_core <= 0))
				 /(SELECT COUNT(1)
					FROM score sc 
					WHERE sc.c_id = cs.c_id) AS '[60-0]'
FROM course cs

  2. SQL解析：略。

24. 查询学生的平均成绩及其名次。

  1. SQL语句：

    SET @i := 0;
SELECT a.*, @i := @i + 1
FROM (SELECT stu.s_id, stu.s_name, AVG(sc.s_core) 
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
			GROUP BY stu.s_id
			ORDER BY AVG(sc.s_core) DESC) AS a

  2. SQL解析：略，详见第19题。

25. 查询各科成绩排名前三的学生记录（按“课程编号”、“课程名称”、“学生编号”、“学生姓名”、“学生成绩”显示）。

  1. SQL语句：

    SELECT a.*
FROM (SELECT cs.c_id, cs.c_name, stu.s_id, stu.s_name, sc.s_core
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
				INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_id = 1
			ORDER BY sc.s_core DESC
			LIMIT 0,3) AS a
UNION ALL
SELECT b.*
FROM (SELECT cs.c_id, cs.c_name, stu.s_id, stu.s_name, sc.s_core
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
				INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_id = 2
			ORDER BY sc.s_core DESC
			LIMIT 0,3) AS b
UNION ALL
SELECT c.*
FROM (SELECT cs.c_id, cs.c_name, stu.s_id, stu.s_name, sc.s_core
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
				INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_id = 3
			ORDER BY sc.s_core DESC
			LIMIT 0,3) AS c

  2. SQL解析：略。

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

  1. SQL语句：

    SELECT cs.c_id, cs.c_name, count(1) AS '选课人数'
FROM score sc 
	INNER JOIN course cs ON sc.c_id = cs.c_id
	INNER JOIN student stu ON sc.s_id = stu.s_id
GROUP BY sc.c_id

  2. SQL解析：略。

27. 查询出只选修两门课程的学生编号及姓名。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name
FROM student stu
	LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING COUNT(1) = 2

  2. SQL解析：略。

28. 查询男生和女生的人数。

  1. SQL语句：

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

  2. SQL解析 ：略。

29. 查询名字中含有“风”字的学生信息。

  1. SQL语句：

    SELECT s_id, s_name, s_sex, s_birth
FROM student
WHERE s_name LIKE '%风%'

  2. SQL解析：

    ​ 注意，尽量不要使用 * 来代替所要查询的字段。

30. 查询同名同姓的学生名单，并统计人数。

  1. SQL语句：

    SELECT s_id, s_name, s_sex, s_birth, COUNT(1) AS '人数'
FROM student
GROUP BY s_name
HAVING COUNT(1) > 1

  2. SQL解析：略。

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

  1. SQL语句：

    SELECT s_id, s_name, s_sex, s_birth
FROM student
WHERE s_birth LIKE '1990%'

  2. SQL解析：略。

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

  1. SQL语句：

    SELECT cs.c_id, cs.c_name
			 ,CAST(AVG(sc.s_core) AS DECIMAL(10,2)) AS '平均成绩'
FROM course cs 
	LEFT JOIN score sc ON cs.c_id = sc.c_id
GROUP BY cs.c_id
ORDER BY AVG(sc.s_core) DESC, cs.c_id ASC

  2. SQL解析：

    ​ order by可以同时使用两种排序规则，使用英文逗号 , 连接，逗号前的排序规则优先于逗号后的排序规则。

33. 查询平均成绩大于等于85的学生编号、姓名、平均成绩。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name
				,CAST(AVG(sc.s_core) AS DECIMAL(10,2)) AS '平均成绩'
FROM student stu 
	LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING AVG(sc.s_core) >= 85

  2. SQL解析：略。

34. 查询数学分数低于60的学生姓名及分数。

  1. SQL语句：

    SELECT stu.s_name, sc.s_core
FROM student stu 	
	INNER JOIN score sc ON stu.s_id = sc.s_id
	INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_name = '数学' 
WHERE sc.s_core < 60

  2. SQL解析：略。

35. 查询所有学生的选修课程及分数。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name, cs.c_name, sc.s_core
FROM student stu
	LEFT JOIN score sc ON stu.s_id = sc.s_id
	INNER JOIN course cs ON sc.c_id = cs.c_id
ORDER BY stu.s_id, cs.c_id

  2. SQL解析：略。

36. 查询任何一门课程的成绩都在70分以上的学生姓名、课程名称、分数。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name, cs.c_name, sc.s_core
FROM student stu 
	LEFT JOIN score sc ON stu.s_id = sc.s_id
	INNER JOIN course cs ON sc.c_id = cs.c_id
WHERE stu.s_id IN(
	SELECT st.s_id
	FROM student st
		LEFT JOIN score s ON st.s_id = s.s_id
	GROUP BY st.s_id
	HAVING MIN(s.s_core) > 70)

  2. SQL解析：略。

    ​ 解题思路是，查询“任何一门课程的成绩都在70分以上”的学生信息，须进行分组group by，而使用分组后的最终表查询“课程名称”，根据group by 的特性可知，只能得到一行数据，故，只能是将查询“任何一门课程的成绩都在70分以上”的学生s_id作为子查询，再查询任何一门课程的成绩都在70分以上的学生姓名、课程名称、分数。

37. 查询取得不及格课程的学生信息及其不及格成绩。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name, cs.c_name, sc.s_core
FROM student stu 
	INNER JOIN score sc ON stu.s_id = sc.s_id AND sc.s_core < 60
	INNER JOIN course cs ON sc.c_id = cs.c_id

  2. SQL解析：略。

38. 查询课程编号为“02”，且课程成绩在80分以上的学生编号和姓名。

  1. SQL语句：

    SELECT stu.s_id, stu.s_name
FROM student stu 
	INNER JOIN score sc ON stu.s_id = sc.s_id AND sc.c_id = 2 AND sc.s_core >= 80

  2. SQL解析：略。

39. 查询每门课程的学生人数。

  1. SQL语句：

    SELECT cs.c_id, cs.c_name, COUNT(1) AS '选课人数'
FROM course cs INNER JOIN score sc ON cs.c_id = sc.c_id
GROUP BY cs.c_id

  2. SQL解析：略。

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

  1. SQL语句：

    SELECT stu.s_id, stu.s_name, sc.s_core
FROM student stu
	INNER JOIN score sc ON stu.s_id = sc.s_id
	INNER JOIN course cs ON sc.c_id = cs.c_id
	INNER JOIN teacher te ON cs.t_id = te.t_id AND te.t_name = '张三'
ORDER BY sc.s_core DESC 
LIMIT 0,1

  2. SQL解析：略。

41. 查询课程不同但成绩相同的学生信息、课程编号、成绩。

  1. SQL语句：

    SELECT stu1.s_id, stu1.s_name, cs1.c_name, sc1.s_core
FROM student stu1 
	INNER JOIN score sc1 ON stu1.s_id = sc1.s_id
	INNER JOIN course cs1 ON sc1.c_id = cs1.c_id
WHERE (
	SELECT COUNT(1)
	FROM student stu2
		INNER JOIN score sc2 ON stu2.s_id = sc2.s_id
		INNER JOIN course cs2 ON sc2.c_id = cs2.c_id
	WHERE cs1.c_id != cs2.c_id AND sc1.s_core = sc2.s_core
) >= 1

  2. SQL解析：

    ​ 本题难点在于，如何实现“课程不同但成绩相同”的比较。

    ​ 如果选择在一张表中实现“课程不同但成绩相同”的比较，即直接联结两个score表，分别对应“不同课程”与“成绩相同”这两个过滤条件，即：

    SELECT stu.s_id, stu.s_name, sc.c_id, sc2.c_id, sc.s_core
FROM student stu 
	INNER JOIN score sc ON stu.s_id = sc.s_id
	INNER JOIN score sc2 ON stu.s_id = sc2.s_id
WHERE sc.c_id != sc2.c_id AND sc.s_core = sc2.s_core

    则查询结果为：

    ​ 可以发现，查询结果不是问题所要的结果。因为，它不包含s_id不同的学生间满足“课程不同但成绩相同”的数据（经对照原表，存在），这是由关联条件所决定的，所以结果集错误。

    ​ 所以，想要获得正确的结果集，应该是两张score表间进行“课程不同但成绩相同”的比较，这样才能包含s_id不同的学生间满足“课程不同但成绩相同”的数据。

    ​ 解题思路是，利用在子查询可以使用主查询from子句中的表的数据的特点，在子查询与主查询的from子句中分别构建两张一样的表，在子查询中查询满足过滤条件“课程不同但成绩相同”的数据个数，主查询中使用该个数作为过滤条件，只要数据个数 ≥ 1，就查询出来。

42. 查询每门功课成绩最好的前两名学生记录。

  1. SQL语句：

    SELECT a.*
FROM (SELECT cs.c_id, cs.c_name, stu.s_id, stu.s_name, sc.s_core
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
				INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_id = 1
			ORDER BY sc.s_core DESC
			LIMIT 0,2) AS a
UNION ALL
SELECT b.*
FROM (SELECT cs.c_id, cs.c_name, stu.s_id, stu.s_name, sc.s_core
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
				INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_id = 2
			ORDER BY sc.s_core DESC
			LIMIT 0,2) AS b
UNION ALL
SELECT c.*
FROM (SELECT cs.c_id, cs.c_name, stu.s_id, stu.s_name, sc.s_core
			FROM student stu 
				LEFT JOIN score sc ON stu.s_id = sc.s_id
				INNER JOIN course cs ON sc.c_id = cs.c_id AND cs.c_id = 3
			ORDER BY sc.s_core DESC
			LIMIT 0,2) AS c

  2. SQL解析：略，对比第25题。

43. 统计各个课程的选修人数（说明：选修人数超过5人才统计），要求显示课程编号及选修人数，查询结果按照统计人数降序排列，若人数相同则按照课程编号升序排列。

  1. SQL语句：

    SELECT	c_id, COUNT(1) AS '选课人数'
FROM  score 
GROUP BY c_id
HAVING COUNT(1) > 5
ORDER BY COUNT(1) DESC, c_id ASC

  2. SQL解析：略。

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

  1. SQL语句：

    SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(1) >= 2

  2. SQL解析：略。

45. 查询选修所有课程的学生信息。

  1. SQL语句：

    SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(1) = (SELECT COUNT(1) FROM course)

  2. SQL解析：略。

46. 查询各个学生的年龄。

  1. SQL语句：

    SELECT s_id, s_name
       ,TIMESTAMPDIFF(YEAR,s_birth,NOW()) AS '年龄'
FROM student

  2. SQL解析：

    使用MySQL的timestampdiff()函数，通过生日计算年龄：

    参考博文：Mysql中通过生日计算年龄的多种方法，MySQL timestampdiff()函数。

    扩展：MySQL的时间差函数TIMESTAMPDIFF、DATEDIFF的用法。

    • 语法：TIMESTAMPDIFF(unit,begin,end)

    • unit参数：确定(end-begin)的结果的单位，取值如下。

    1. MySQL常用的日期时间函数：

      参考博文：MySQL常用日期时间函数。

      • 日期时间函数：

        补充 - SUBDATE(date,INTERVAL expr type) 函数：等同于DATE_SUB(date,INTERVAL expr type) 函数，与date_add函数的作用相反，是返回一个日期或时间值减去上一个时间间隔的时间值。若不写type，默认type为day。

        补充 - SUBDATE(expr,days) 函数：是expr - days，其中days为天数，相当于省略了interval和type的subdate函数，其中，type参数的类型为day。

      • 客户端的时区设置会影响一些日期函数返回值的显示，如，now()、curtime()、curdate()，也影响timestamp列值的显示。默认情况下，客户端和服务端的时区相同，为SYSTEM，表示使用系统时区。

    2. MySQL格式化日期：

      参考博文：mysql格式化日期。

      • DATE_FORMAT(date,format) ：以不同的格式显示日期/时间数据。

      • format参数的格式：须使用英文单引号 '' 括起来。

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

  1. SQL语句：

    SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN  DATE_FORMAT(SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') - 1),'%m-%d')
	AND  DATE_FORMAT(SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') -7),'%m-%d')

  2. SQL解析：

    ​ 解题思路，求出本周周一和周日的日期（格式为“年/月/日”，以防本周跨年）—> 查询生日处于本周（格式为“月/日”）的学生信息。

    ​ 本题难点在于，求出本周周一和周日的日期，以及今天是周几，关键函数是subdate和date_format。注意，无需考虑是否为闰年，是否跨年，因为DBMS自己考虑到了。

    • 本周周一的日期（格式为“年/月/日”）：

      SELECT SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') - 1)

    • 本周周日的日期：（格式为“年/月/日”）

      SELECT SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') -7)

    • 求今天是当前周的第几天：date_format(curdate(),'%w')，%w表示周的天数，其中，周一为0，周日为6。

  3. SQL优化：使用用户自定义变量——会话变量 @变量名，来使SQL更易读。

    SET @i := DATE_FORMAT(CURDATE(),'%w');
SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN  DATE_FORMAT(SUBDATE(CURDATE(), @i - 1),'%m-%d')
	AND  DATE_FORMAT(SUBDATE(CURDATE(), @i -7),'%m-%d')

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

  1. SQL语句：

    SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN DATE_FORMAT(SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') - 8),'%m-%d')
	AND DATE_FORMAT(SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') -14),'%m-%d')

  2. SQL解析：

    ​ 解题思路，求出下周周一和周日的日期（格式为“年/月/日”，以防下周跨年）—> 查询生日处于下周（格式为“月/日”）的学生信息。

    ​ 本题难点在于，求出下周周一和周日的日期，关键函数是subdate和date_format。注意，无需考虑是否为闰年，是否跨年，因为DBMS自己考虑到了。

    • 下周周一的日期（格式为“年/月/日”）：

      SELECT SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') - 8)

    • 下周周日的日期（格式为“年/月/日”）：

      SELECT SUBDATE(CURDATE(), DATE_FORMAT(CURDATE(),'%w') -14)

  3. SQL优化：使用用户自定义变量——会话变量 @变量名，来使SQL更易读。

    SET @i := DATE_FORMAT(CURDATE(),'%w');
SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN DATE_FORMAT(SUBDATE(CURDATE(), @i - 8),'%m-%d')
	AND DATE_FORMAT(SUBDATE(CURDATE(), @i -14),'%m-%d')

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

  1. SQL语句：

    SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN DATE_FORMAT(SUBDATE(CURDATE(), DAY(CURDATE()) - 1),'%m-%d')
	AND DATE_FORMAT(LAST_DAY(CURDATE()),'%m-%d')

  2. SQL解析：

    ​ 解题思路，求出本月月初和月末的日期（格式为“年/月/日”，以防以防本月跨年）—> 查询生日处于本月（格式为“月/日”）的学生信息。

    ​ 本题难点在于，求出本月月初和月末的日期，关键函数是last_date、subdate和date_format。注意，无需考虑是否为闰年，是否跨年，因为DBMS自己考虑到了。

    • 本月月初的日期（格式为“年/月/日”）：

      SELECT SUBDATE(CURDATE(), DAY(CURDATE()) - 1)

    • 本月月末的日期（格式为“年/月/日”）：使用 LAST_DAY(date) 函数直接求得。

      SELECT LAST_DAY(CURDATE())

50. 查询下个月过生日的学生。

  1. SQL语句：

    SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN DATE_FORMAT(SUBDATE(SUBDATE(CURDATE(),INTERVAL -1 MONTH),DAY(SUBDATE(CURDATE(),INTERVAL -1 MONTH)) - 1),'%m-%d')
	AND DATE_FORMAT(LAST_DAY(SUBDATE(CURDATE(),INTERVAL -1 MONTH)),'%m-%d')

  2. SQL解析：

    ​ 解题思路，求出下个月月初和月末的日期（格式为“年/月/日”，以防以防以防下个月跨年）—> 查询生日处于下个月（格式为“月/日”）的学生信息。

    ​ 本题难点在于，求出下个月月初和月末的日期，以及当前日期对应的下个月的日期，关键函数是subdate和date_format。注意，无需考虑是否为闰年，是否跨年，因为DBMS自己考虑到了。

    • 下个月月初的日期（格式为“年/月/日”）：

      SELECT SUBDATE(SUBDATE(CURDATE(),INTERVAL -1 MONTH),DAY(SUBDATE(CURDATE(),INTERVAL -1 MONTH)) - 1)

    • 下个月月末的日期（格式为“年/月/日”）：

      SELECT LAST_DAY(SUBDATE(CURDATE(),INTERVAL -1 MONTH))

    • 求出当前日期对应的下个月的日期（格式为“年/月/日”）：

      SUBDATE(CURDATE(),INTERVAL -1 MONTH)

  3. SQL优化：使用用户自定义变量——会话变量 @变量名，来使SQL更易读。

    SET @i := SUBDATE(CURDATE(),INTERVAL -1 MONTH);
SELECT s_id, s_name
FROM student
WHERE DATE_FORMAT(s_birth,'%m-%d') 
	BETWEEN DATE_FORMAT(SUBDATE(@i,DAY(@i) - 1),'%m-%d')
	AND DATE_FORMAT(LAST_DAY(@i),'%m-%d')
posted @ 2021-01-06 03:21  不是长发不恣意  阅读(237)  评论(0)    收藏  举报