mrsql查询第二高的成绩_SQL子查询语句

怼烎@ 2023-01-02 05:20 222阅读 0赞

![bc1b785a0521d2bb7f7fb2db8eaef75d.png][]

**本章主要包含以下内容：**

![f10603c72aae81b4ef7f00bcf5f024d0.png][]

表中存放的是实际数据，视图中存放的是SQL查询语句，使用视图时，会运行视图中的SQL查询语句，创建出一张临时表。

**一、视图**

**（1）如何创建视图？**

create view 视图名称(<视图列名1>，<视图列名2>，….)
    as 
    <select 查询语句>;

例如：

create view 按性别汇总(性别,人数)
    as 
    select 性别,count(*)
    from student
    group by 性别;

![962a451135c001a7088dce91c476165d.png][]

![50b1b0b7970a830872f984f7845f1e23.png][]

**(2)如何使用视图？**

（1）中已经示范了创建视图的方法，下面将介绍如何使用视图？当创建好视图后，若要使用视图，这时候只需要将select子句中的from来自于那个表的表名将使用：视图名称代替表名,

**如：**

select 性别,人数
    from 按性别汇总;

![23a0ab3d2ee32133022fa9802b9ddbe2.png][]

**（3）使用视图的好处：**

若SQL语句要重复使用时，可以保存成视图，频繁使用，节约时间，提高效率。

视图的数据随原表的更新而更新，以保持数据的最新。

视图不需要保存数据，可以节省存放数据的空间。

**（4）使用视图的注意事项：**

不能往视图插入数据。

**二、子查询**

子查询就是一张一次性的视图，视图是用来存储select信息的。这就意味着“使用子查询，可以使用在select函数的任何一个地方，也就是说子函数使得一个完整的select语句发挥一个关键字或函数的作用。”或者说子查询就是在一个select语句中又嵌套了另一个select语句。

**sql运行顺序：先运行子查询。**

…in (子查询)

…any (子查询)

…all (子查询)

**(1)找出每个课程里成绩最低的学号：**

**a.首先，找到每门课程的最低分分别是多少？**

select 课程号,min(成绩)
    from score
    group by 课程号;

![495bb0fbb7444f26854268b085361571.png][]

**b.在成绩表中查找这些值对应的学号**

select 学号,成绩
    from score
    where 成绩 in(80,60,80);

![cfe8acb1c9410633237dea6492c0b31b.png][]

**最后，用子查询表示，即为：**

select 学号,成绩
    from score
    where 成绩 in(select min(成绩)
    from score
    group by 课程号);

![7663dd0721ae4160dc513efc1bd07ee4.png][]

1.  **标量子查询(返回单一值，所以可以跟in,any,all,between)。**

*标量子查询就是单一子查询，必须而且只能返回一行一列的结果，可以使用在select语句的任何地方，无论是select子句、group by子句、having子句还是order by子句，几乎所有的地方搜能使用。*

**a.大于平均成绩学生的学号和成绩：**

select 学号,成绩
    from score
    where 成绩>(select avg(成绩)
    from score);

![9a986c496af112caa48e6044a62a7f91.png][]

*标量子查询是查询的一种，普通的子查询可以返回多行值，标量子查询只返回单一的值，所以可以跟in,any,all,between使用，形成复杂查询条件。*

**四、\*关联子查询。**

*关联子查询在每个组里比较，关联子查询需要使用关联条件。*

**a.查找出每个课程中大于对应课程平均成绩的学生。**

**分析思路：**

1)先对课程进行分类，然后求出每门课程的平均成绩，

select 课程号,avg(成绩) as 平均成绩
    from score
    group by 课程号;

2)最后找出比平均成绩分数高的学生。

select 学号,成绩
    from score
    where 成绩>1

**由于1）返回的是多组数据，因此不能用标量子查询，又因为这是所以SQL语句应为：**

select 学号,课程号,成绩
    from score as s1
    where 成绩>(select avg(成绩)
    from score as s2
    where s1.课程号=s2.课程号
    group by 课程号);

![5abd7110997e3c6a3959fe94c76dda9d.png][]

***关联子查询：当需要在每个组里比较时，需要用到关联子查询。***

**五、如何用sql解决业务问题**

翻译成大白话-写出分析思路-写出对应的sql语句

**比如：哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？**

**第一步1：翻译成大白话：找出课程0002的全部成绩**

**第二步1：分析思路：**

select 查询结果[成绩]
    from 从哪张表中查找数据[成绩表]
    where 查询条件[课程号是0002]
    group by分组[没有]
    having 对分组结果指定条件 [没有]
    order by对查询结果进行排序[没有]
    limit从查询结果中取出指定行[没有];

![e34e48fe9a15b3fabaa8a60ec32ff8e1.png][]

运行结果为：3-4-5-1-2-6-7。

**第一步2：若学生的成绩大于第一步的任意一个成绩，则符合条件**

**第二部2：分析思路：**

比任意一个高any(子查询)：

select 学号,成绩
    from score
    where 成绩>any(子查询)

**第三步：写出SQL语句：**

select 学号,成绩
    from score
    where 成绩>any(
    select 成绩
    from score
    where 课程号='0002');

![0abf3ae86f6f203b8418f36a03e7c596.png][]

**六、各种函数：**

**算术函数：**

a. round(数值，保留小数的位数)，主要用于对数据进行四舍五入。

例：：round(1234.5671) 结果为：1234.6

b. abs(数值)：求数值的绝对值。例：abs(-100) 结果：100

c. mod(被除数，除数)，主要用于求余数。例：mod(5,2) 结果：1

**SQLZOO习题：**https://sqlzoo.net/wiki/SELECT\_within\_SELECT\_Tutorial

![2fa373bc6411bdffa8ca2cf5f8f2a107.png][]

![c513404b8417e711f791671ab6923aeb.png][]

![ae07f45212fd66dce3d1dac9b3ec54b6.png][]

**3.在阿根廷和澳大利亚所在的洲份中国家有哪些？查找国家名称和洲名称，并按国家名称排序。**

**知识点：在运算符in中使用子查询**

![5d259ba7c78fbd64c1ed20d896a3efcc.png][]

![7e7fc9c1bac585e5a74a8dd423b6c759.png][]

![4fbe48d93a4b6e34d4fab05a1df423fd.png][]

**5有用到字符串连接函数concat以及浮点数保留几位的函数：round.**

![ede005b225510bdca01dddfa463a84f4.png][]

![225bbb7e878f741db56d941b905484d7.png][]

**8.列出洲份名称和国家名称，其中每个洲只取出一个国家（条件：该国家排序在这个洲的首位）。**

**知识点：all的用法，关联子查询。**

![dc46997cc0fe52642df67285f7326097.png][]

**9.\*找出符合条件的洲和国家名称，条件：该洲的全部国家人口都少于或等于25000000人口。**

![68703fdc34e59d28e19bb471f54208fb.png][]

**10.有些国家的人口是同洲份的所有其他国家的3倍及以上。列出这些国家的名称和洲。(有点难度，需要多理解。)**

![a2e84c1bfe7f30d39c320a04c401069f.png][]

*封面图片来源于网络。*

[bc1b785a0521d2bb7f7fb2db8eaef75d.png]: /images/20221120/df44cb5613ff4ebc99a645cc2d2e0c01.png
[f10603c72aae81b4ef7f00bcf5f024d0.png]: /images/20221120/451fd4f6ece04283b35aee1ad8434540.png
[962a451135c001a7088dce91c476165d.png]: /images/20221120/b0d6989f1ba249ed899b8323d77b80a1.png
[50b1b0b7970a830872f984f7845f1e23.png]: /images/20221120/c8209b5e8e604374973cf307967ce12b.png
[23a0ab3d2ee32133022fa9802b9ddbe2.png]: /images/20221120/eec51a792b5340508629c7db90c908bb.png
[495bb0fbb7444f26854268b085361571.png]: /images/20221120/91f6574b63974951a1099269e1d8f8fd.png
[cfe8acb1c9410633237dea6492c0b31b.png]: /images/20221120/5084dd00417e46a4bfed71e184200ea9.png
[7663dd0721ae4160dc513efc1bd07ee4.png]: /images/20221120/03b0d069829f4bcdb006e36c29712cc5.png
[9a986c496af112caa48e6044a62a7f91.png]: /images/20221120/e31ef42ebb524f339a3ed79328fe761b.png
[5abd7110997e3c6a3959fe94c76dda9d.png]: /images/20221120/b6fc84fee5094d92938bd8be76078ea6.png
[e34e48fe9a15b3fabaa8a60ec32ff8e1.png]: /images/20221120/884744098af74ea890938d464152cf77.png
[0abf3ae86f6f203b8418f36a03e7c596.png]: /images/20221120/0d907c1bdbe34b1f8bf3acfd7353f7d3.png
[2fa373bc6411bdffa8ca2cf5f8f2a107.png]: /images/20221120/9edac1f464f948ba87e86840d40324d5.png
[c513404b8417e711f791671ab6923aeb.png]: /images/20221120/21a5f36ee2484867824b7385fb11f00a.png
[ae07f45212fd66dce3d1dac9b3ec54b6.png]: /images/20221120/8b764b8070fc4ea79a08c2dd33d69208.png
[5d259ba7c78fbd64c1ed20d896a3efcc.png]: /images/20221120/ba0875307076431cb4ec11664d4fafd8.png
[7e7fc9c1bac585e5a74a8dd423b6c759.png]: /images/20221120/637a04422e9f419b9c478629fa081434.png
[4fbe48d93a4b6e34d4fab05a1df423fd.png]: /images/20221120/af490f959f134a6688da3778f8d9d5de.png
[ede005b225510bdca01dddfa463a84f4.png]: /images/20221120/b84d8573da1b42568eb8efedc4d1b79f.png
[225bbb7e878f741db56d941b905484d7.png]: /images/20221120/9af13cee3dfa4f76b2959b86c8a0ef6a.png
[dc46997cc0fe52642df67285f7326097.png]: /images/20221120/33dbbc97cb24495eb5d0b32fcd79c4b7.png
[68703fdc34e59d28e19bb471f54208fb.png]: /images/20221120/70f1c69196a24f76903941d29f5f5a78.png
[a2e84c1bfe7f30d39c320a04c401069f.png]: /images/20221120/6a2b411e6b324ae68fc1f9210ced820a.png