Level 6 临时表单

PreviousOUTER JOIN NextGROUP BY

Last updated 4 years ago

Was this helpful?

Level 6 临时表单

该章节参考

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  | eastern.heretic@gmail.com |  20 | CN      |
|  2 | Northern Beggar  | northern.beggar@qq.com    |  21 | CN      |
|  3 | Western Venom    | western.venom@163.com     |  28 | USA     |
|  4 | Southern Emperor | southern.emperor@qq.com   |  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  | eastern.heretic@gmail.com |  20 | CN      |
|  2 | Northern Beggar  | northern.beggar@qq.com    |  21 | CN      |
|  3 | Western Venom    | western.venom@163.com     |  28 | USA     |
|  4 | Southern Emperor | southern.emperor@qq.com   |  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  | eastern.heretic@gmail.com |  20 | CN      |
|  2 | Northern Beggar  | northern.beggar@qq.com    |  21 | CN      |
|  3 | Western Venom    | western.venom@163.com     |  28 | USA     |
|  4 | Southern Emperor | southern.emperor@qq.com   |  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;

执行输出结果

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;

执行输出结果

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;

执行输出结果

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;

执行输出结果

PreviousOUTER JOIN NextGROUP BY

Last updated 4 years ago

Was this helpful?

该章节参考

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  | eastern.heretic@gmail.com |  20 | CN      |
|  2 | Northern Beggar  | northern.beggar@qq.com    |  21 | CN      |
|  3 | Western Venom    | western.venom@163.com     |  28 | USA     |
|  4 | Southern Emperor | southern.emperor@qq.com   |  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  | eastern.heretic@gmail.com |  20 | CN      |
|  2 | Northern Beggar  | northern.beggar@qq.com    |  21 | CN      |
|  3 | Western Venom    | western.venom@163.com     |  28 | USA     |
|  4 | Southern Emperor | southern.emperor@qq.com   |  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  | eastern.heretic@gmail.com |  20 | CN      |
|  2 | Northern Beggar  | northern.beggar@qq.com    |  21 | CN      |
|  3 | Western Venom    | western.venom@163.com     |  28 | USA     |
|  4 | Southern Emperor | southern.emperor@qq.com   |  21 | CN      |
+----+------------------+---------------------------+-----+---------+
4 rows in set (0.00 sec)

1.4.3 练习

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

1.5 多行子查询

多行子查询即是子查询的返回结果是多行数据。当主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，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 多列子查询

案例

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;

执行输出结果

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;

执行输出结果

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;

执行输出结果

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;

执行输出结果