Level 6 临时表单

该章节参考 https://www.cnblogs.com/lium710/p/9966191.html

1. SELECT 嵌套（子查询）

子查询（Sub Query）或者说内查询（Inner Query），也可以称作嵌套查询（Nested Query），是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询。

子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。

子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。

使用子查询必须遵循以下几个规则：

• 子查询必须括在圆括号中，除子查询在 INSERT 语句中。

• 返回多行数据的子查询只能同多值操作符一起使用，比如 IN，ALL，ANY 操作符。

• SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。

• 子查询不能直接用在聚合函数中。

• BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

1.1 SELECT 语句中的子查询

1.1.1 基本语法

通常情况下子查询都与 SELECT 语句一起使用，其基本语法如下所示：

SELECT column_name(s)
FROM   table_name
WHERE  column_name OPERATOR
      ( SELECT column_name(s)
       FROM table_name )

其中

OPERATOR 表示 =、<、>、>=、<=、IN、BETWEEN 等运算符

1.1.2 实例

查询 'Western Venom' 老师所教的所有课程信息。

SELECT * FROM courses WHERE teacher_id = (
    SELECT id FROM teachers WHERE name = 'Western Venom'
);

执行输出结果

mysql> SELECT * FROM courses WHERE teacher_id = (
    ->  SELECT id FROM teachers WHERE name = 'Western Venom'
    -> );
+----+-------------------------+----------------+-------------+------------+
| id | name                    | student_count | created_at | teacher_id |
+----+-------------------------+----------------+-------------+------------+
|  2 | System Design           |            135 | 2020-07-18  |          3 |
|  3 | Django                  |             78 | 2020-02-29  |          3 |
|  6 | Artificial Intelligence |            166 | 2018-05-13  |          3 |
|  7 | Java P6+                |             78 | 2019-01-19  |          3 |
+----+-------------------------+----------------+-------------+------------+
4 rows in set (0.01 sec)

1.1.3 练习题

查询 'Big Data' 课程对应的老师姓名。

代码输入框：

SELECT name FROM teachers WHERE id = (
    SELECT teacher_id FROM courses WHERE name = 'Big Data'
)

执行输出结果

+-----------------+
| name            |
+-----------------+
| Eastern Heretic |
+-----------------+

1.2 INSERT 语句中的子查询

子查询还可以用在 INSERT 语句中。INSERT 语句可以将子查询返回的数据插入到其他表中。

子查询中选取的数据可以被任何字符、日期或者数值函数所修饰。

1.2.1 基本语法

其基本语法如下所示：

INSERT INTO table_name
           SELECT colnum_name(s)
           FROM table_name
           [ WHERE colnum_name OPERATOR ]

1.2.2 实例

考虑与 teachers 表拥有相同结构的 teachers_bkp 表。现在要将 teachers 表中所有的数据复制到 teachers_bkp 表中，代码如下：

INSERT INTO teachers_bkp 
SELECT * 
FROM teachers;

执行后 teachers 表数据为：

mysql> INSERT INTO teachers_bkp
    -> SELECT *
    -> FROM teachers;
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM teachers_bkp;
+----+------------------+---------------------------+-----+---------+
| id | name             | email                     | age | country |
+----+------------------+---------------------------+-----+---------+
|  1 | Eastern Heretic  | [e-mail] |  20 | CN      |
|  2 | Northern Beggar  | [e-mail]    |  21 | CN      |
|  3 | Western Venom    | [e-mail]     |  28 | USA     |
|  4 | Southern Emperor | [e-mail]   |  21 | CN      |
|  5 | Linghu Chong     | NULL                      |  18 | CN      |
+----+------------------+---------------------------+-----+---------+
5 rows in set (0.05 sec)

1.2.3 练习题

现有一张 courses_bkp 结构与 courses 表相同，现在需要将 courses 表中所有的数据复制到 courses_bkp 表中。

代码输入框：

1.3 UPDATE 语句中的子查询

子查询可以用在 UPDATE 语句中。

当子查询同 UPDATE 一起使用的时候，既可以更新单个列，也可更新多个列。

1.3.1 基本语法

其基本语法如下所示：

UPDATE table_name SET column_name = new_value
WHERE column_name OPERATOR 
   (SELECT column_name
   FROM table_name  [WHERE] )

1.3.2 实例

现在有与 teachers 表拥有相同结构和数据的 teachers_bkp 表。现在要将 teachers 表中所有 age（年龄） 小于 20 岁老师的 age 修改为 21 岁，代码如下：

UPDATE teachers 
SET age = 21 
WHERE id IN ( 
            SELECT id 
            FROM teachers_bkp 
            WHERE age < 20 
        );

执行后 teachers 表数据为：

mysql> UPDATE teachers
    -> SET age = 21
    -> WHERE id IN (
    ->             SELECT id
    ->             FROM teachers_bkp
    ->             WHERE age < 20
    ->         );
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM teachers;
+----+------------------+---------------------------+-----+---------+
| id | name             | email                     | age | country |
+----+------------------+---------------------------+-----+---------+
|  1 | Eastern Heretic  | [e-mail] |  20 | CN      |
|  2 | Northern Beggar  | [e-mail]    |  21 | CN      |
|  3 | Western Venom    | [e-mail]     |  28 | USA     |
|  4 | Southern Emperor | [e-mail]   |  21 | CN      |
|  5 | Linghu Chong     | NULL                      |  21 | CN      |
+----+------------------+---------------------------+-----+---------+
5 rows in set (0.01 sec)

1.3.3 练习

现在有与 courses 表拥有相同结构和数据的 courses_bkp 表。现在要将 courses 表中所有 student_number（学生人数） 大于等于 200 课程的 student_number 修改为 100。

1.4 DELETE 语句中的子查询

如同前面提到的其他语句一样，子查询还可以同 DELETE 语句一起使用。

1.4.1 基本语法

其基本语法如下所示：

DELETE FROM table_name
WHERE column_name OPERATOR 
   (SELECT column_name
   FROM table_name  [WHERE] )

1.4.2 实例

现在有与 teachers 表拥有相同结构和数据的 teachers_bkp 表。

下面的示例将从 teachers 表中删除所有 age（年龄）小于 20 岁的老师：

DELETE FROM teachers 
WHERE id IN ( 
            SELECT id 
            FROM teachers_bkp 
            WHERE age < 20 
        );

执行后 teachers 表数据为：

mysql> DELETE FROM teachers
    -> WHERE id IN (
    ->             SELECT id
    ->             FROM teachers_bkp
    ->             WHERE age < 20
    ->         );
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM teachers;
+----+------------------+---------------------------+-----+---------+
| id | name             | email                     | age | country |
+----+------------------+---------------------------+-----+---------+
|  1 | Eastern Heretic  | [e-mail] |  20 | CN      |
|  2 | Northern Beggar  | [e-mail]    |  21 | CN      |
|  3 | Western Venom    | [e-mail]     |  28 | USA     |
|  4 | Southern Emperor | [e-mail]   |  21 | CN      |
+----+------------------+---------------------------+-----+---------+
4 rows in set (0.00 sec)

1.4.3 练习

现在有与 courses 表拥有相同结构和数据的 courses_bkp 表。现在要将 courses 表中所有 student_number（学生人数） 大于等于 200 的课程删除掉。

1.5 多行子查询

多行子查询即是子查询的返回结果是多行数据。当主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL,ANY）来进行比较。

其中，IN 的含义是匹配子查询结果中的任一个值即可（"IN" 操作符，能够测试某个值是否在一个列表中），ALL 则必须要符合子查询的所有值才可，ANY 要符合子查询结果的任何一个值即可。而且须注意ALL 和ANY 操作符不能单独使用，而只能与单行比较符（=、>、< 、>= 、<= 、<>）结合使用。

1.2.1 多行子查询使用IN操作符号

基本语法

SELECT column_name(s)
FROM table_name
WHERE column_name in
   (SELECT column_name
   FROM table_name)

实例

查询 “Eastern Heretic”（东邪）老师所教的所有课程名称：

SELECT name
FROM   courses
WHERE  teacher_id IN 
       ( SELECT id 
         FROM teachers 
         WHERE name = 'Eastern Heretic');

执行查询结果为：

mysql> SELECT name
    -> FROM   courses
    -> WHERE  teacher_id IN
    ->        ( SELECT id
    ->          FROM teachers
    ->          WHERE name = 'Eastern Heretic');
+---------------------+
| name                |
+---------------------+
| Big Data            |
| Data Analysis       |
| Dynamic Programming |
+---------------------+
3 rows in set (0.00 sec)

练习

查询所有 'country' 等于 'CN' 的老师所教的所有课程的课程名。

1.2.2 多行子查询使用ALL操作符号

查询有一门以上的成绩高于Kaka的最高成绩的学生的名字

SELECT stname 
FROM   student 
WHERE  stid IN(SELECT DISTINCT stid 
               FROM   score 
               WHERE  score > ALL (SELECT score 
                                   FROM   score 
                                   WHERE  stid = (SELECT stid 
                                                  FROM   student 
                                                  WHERE  stname = 'Kaka')));

1.2.3 多行子查询使用ANY操作符号

查询有一门以上的成绩高于Kaka的任何一门成绩的学生的名字:

SELECT stname 
FROM   student 
WHERE  stid IN(SELECT DISTINCT stid 
               FROM   score 
               WHERE  score > ANY (SELECT score 
                                   FROM   score 
                                   WHERE  stid = (SELECT stid 
                                                  FROM   student 
                                                  WHERE  stname = 'Kaka')));

1.6 多列子查询

当是单行多列的子查询时，主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝, >, <, >=, <=, <>）来进行比较；当是多行多列子查询时，主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL,ANY）来进行比较。

案例

SELECT deptno, 
       ename, 
       job, 
       sal 
FROM   emp 
WHERE  (deptno,sal)
IN     ( 
        SELECT   deptno, 
                 Max(sal) 
        FROM     emp 
        GROUP BY deptno
        )；

1.7 内联视图子查询

案例1

ELECT ename,job,sal,rownum 
FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)；

案例2

SELECT ename,job,sal,rownum 
FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal) 
WHERE rownum<=5；

1.8 在HAVING子句中使用子查询

案例

SELECT   deptno, 
         job, 
         Avg(sal) 
FROM     emp 
GROUP BY deptno, 
         job 
HAVING   Avg(sal)> 
         ( 
                SELECT sal 
                FROM   emp 
                WHERE  ename='MARTIN')；

让我们看看具体实例

实例Ⅰ

给出人口多于Russia(俄国)的国家名称

SELECT name FROM bbc
WHERE population>
(SELECT population FROM bbc
WHERE name='Russia')

实例Ⅱ

给出'India'(印度), 'Iran'(伊朗)所在地区的任何国家的任何信息

SELECT * FROM bbc
WHERE region IN
(SELECT region FROM bbc
WHERE name IN ('India','Iran'))

实例Ⅲ

SELECT name FROM bbc
WHERE region='Europe' AND gdp/population >
(SELECT gdp/population FROM bbc
WHERE name='United Kingdom')

2. GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

演示数据库

在本小节中，我们将使用 RUNOOB 样本数据库。

下面是选自 "Websites" 表的数据：

+----+--------------+---------------------------+-------+---------+ 
| id | name         | url                       | alexa | country | 
+----+--------------+---------------------------+-------+---------+ 
| 1  | Google       | https://www.google.cm/    | 1     | USA     | 
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      | 
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      | 
| 4  | 微博          | http://weibo.com/         | 20    | CN      | 
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     | 
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     | 
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 网站访问记录表的数据：

mysql> SELECT * FROM access_log; 
+-----+---------+-------+------------+ 
| aid | site_id | count | date       | 
+-----+---------+-------+------------+ 
|   1 |       1 |    45 | 2016-05-10 | 
|   2 |       3 |   100 | 2016-05-13 | 
|   3 |       1 |   230 | 2016-05-14 | 
|   4 |       2 |    10 | 2016-05-14 | 
|   5 |       5 |   205 | 2016-05-14 | 
|   6 |       4 |    13 | 2016-05-15 | 
|   7 |       3 |   220 | 2016-05-15 | 
|   8 |       5 |   545 | 2016-05-16 | 
|   9 |       3 |   201 | 2016-05-17 | 
+-----+---------+-------+------------+ 
9 rows in set (0.00 sec)

GROUP BY 简单实例

统计 access_log 各个 site_id 的访问量：

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

执行输出结果

img

GROUP BY 多表实例

下面的 SQL 语句统计有记录的网站的记录数量：

SELECT Websites.name,COUNT(access_log.aid) AS nums 
FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

执行输出结果

img

3. HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

语法

SELECT   column_name, 
         aggregate_function(column_name) 
FROM     table_name 
WHERE    column_name operator value 
GROUP BY column_name 
HAVING   aggregate_function(column_name) operator value;

演示数据库

在本小节中，我们将使用 RUNOOB 样本数据库。

下面是选自 "Websites" 表的数据：

+----+--------------+---------------------------+-------+---------+ 
| id | name         | url                       | alexa | country | 
+----+--------------+---------------------------+-------+---------+ 
| 1  | Google       | https://www.google.cm/    | 1     | USA     | 
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      | 
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      | 
| 4  | 微博          | http://weibo.com/         | 20    | CN      | 
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     | 
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     | 
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 网站访问记录表的数据：

mysql> SELECT * FROM access_log; 
+-----+---------+-------+------------+ 
| aid | site_id | count | date       | 
+-----+---------+-------+------------+ 
|   1 |       1 |    45 | 2016-05-10 | 
|   2 |       3 |   100 | 2016-05-13 | 
|   3 |       1 |   230 | 2016-05-14 | 
|   4 |       2 |    10 | 2016-05-14 | 
|   5 |       5 |   205 | 2016-05-14 | 
|   6 |       4 |    13 | 2016-05-15 | 
|   7 |       3 |   220 | 2016-05-15 | 
|   8 |       5 |   545 | 2016-05-16 | 
|   9 |       3 |   201 | 2016-05-17 | 
+-----+---------+-------+------------+ 
9 rows in set (0.00 sec)

HAVING 实例Ⅰ

现在我们想要查找总访问量大于 200 的网站。

我们使用下面的 SQL 语句：

SELECT websites.NAME, 
       websites.url, 
       Sum(access_log.count) AS nums 
FROM   (access_log 
        INNER JOIN websites 
                ON access_log.site_id = websites.id) 
GROUP  BY websites.NAME 
HAVING Sum(access_log.count) > 200;

执行输出结果

img

HAVING 实例Ⅱ

现在我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 200。

我们在 SQL 语句中增加一个普通的 WHERE 子句：

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites 
INNER JOIN access_log 
ON Websites.id=access_log.site_id 
WHERE Websites.alexa < 200  
GROUP BY Websites.name 
HAVING SUM(access_log.count) > 200;

执行输出结果

img