MySql_13 MySQL中的子查询

1、什么是子查询？

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以作为外层查询语句提供条件。

2、引发子查询的情况？

使用[NOT]IN的子查询
使用比较运算符的子查询（=、>、 <、>=、 <=、 <>、 !=、 <=>）
使用[NOT]EXISTS的子查询
使用ANY|SOME或者ALL的子查询

运算符	ANY	SOME	ALL
>、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

使用`[NOT] IN`的子查询

首先重置一下employee和department表：

mysql> DROP TABLE employee,department;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS department(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> depName VARCHAR(20) NOT NULL UNIQUE
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT department(depName) VALUES('教学部'),
    -> ('市场部'),
    -> ('运营部'),
    -> ('督导部');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS employee(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> depId TINYINT UNSIGNED
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT employee(username,depId) VALUES('king',1),
    -> ('queen',2),
    -> ('张三',3),
    -> ('李四',4),
    -> ('王五',1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

例如我们想查看部门下面的员工，会分别执行以下命令：

1 2	SELECT * FROM department; SELECT id,username FROM employee WHERE depId IN (1,2,3,4);

通过第一个语句查询到部门的depId然后用IN来查询，而现在我们可以将两条语句整合成一条语句，查询结果是一样的，同样的NOT IN也是一样的：

1	SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);

使用比较运算符的子查询

创建学员表student和奖学金表scholarship表插入几条测试数据：

mysql> # 创建学员表student
mysql> CREATE TABLE IF NOT EXISTS student(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20)  NOT NULL UNIQUE,
    -> score TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT student(username,score) VALUES('king',95),
    -> ('king1',35),
    -> ('king2',45),
    -> ('king3',55),
    -> ('king4',65),
    -> ('king5',75),
    -> ('king6',80),
    -> ('king7',90),
    -> ('king8',25);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> 
mysql> # 创建奖学金scholarship
mysql> CREATE TABLE IF NOT EXISTS scholarship(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> level TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT scholarship(level) VALUES(90),(80),(70);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

假如我们查询获得一等奖学金的同学，之前我们会这样写：

1 2	SELECT level FROM scholarship WHERE id=1; SELECT id,username FROM student WHERE score>=90;

而用了我们的子查询后将两条语句合并在一起：

mysql> SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  8 | king7    |
+----+----------+
2 rows in set (0.01 sec)

将子查询放在了括号内，一定是由内向外执行，将子查询的结果当做条件进行查询。

使用[NOT]EXISTS的子查询

[NOT]EXISTS引发子查询是真假条件，如果子查询是假，那么他的外层条件也不会执行，例如我们只有4个部分，那我们查询第五个部门中的员工的话肯定是没有的：

1	SELECT * FROM department WHERE id=5;

当然，都没有第五个部门就没有必要查询员工，而用EXISTS的查询可以这么写：

1 2	mysql> SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5); Empty set (0.00 sec)

使用ANY|SOME或者ALL的子查询

例如我们查询所有获得奖学金的学员，首先查一下奖学金：

mysql> SELECT * FROM scholarship;
+----+-------+
| id | level |
+----+-------+
|  1 |    90 |
|  2 |    80 |
|  3 |    70 |
+----+-------+
3 rows in set (0.00 sec)

可以看到分数只有大于等于70才能获得奖学金，那么我们利用ANY或者SOME可以查到其中的最小值：

mysql> SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  6 | king5    |    75 |
|  7 | king6    |    80 |
|  8 | king7    |    90 |
+----+----------+-------+
4 rows in set (0.00 sec)

而ALL代表大于里面的最大值，例如查询获得一等奖学金的学员：

mysql> SELECT id,username,score FROM student WHERE score >=ALL(SELECT level FROM scholarship);
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  8 | king7    |    90 |
+----+----------+-------+
2 rows in set (0.00 sec)

而<=是一样的效果，=和!=就相当于IN和NOT IN：

mysql> SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  7 | king6    |    80 |
|  8 | king7    |    90 |
+----+----------+-------+
2 rows in set (0.01 sec)

3、将查询结果写入到数据表

1 2	INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

这个语句是将查询结果插入到另一张表（执行顺序是先查询后插入），我们创建一个测试表将奖学金结果插入：

mysql> CREATE TABLE test1 (
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> num TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT test1(id,num) 
    -> SELECT id,score FROM student;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test1;
+----+------+
| id | num  |
+----+------+
|  1 |   95 |
|  2 |   35 |
|  3 |   45 |
|  4 |   55 |
|  5 |   65 |
|  6 |   75 |
|  7 |   80 |
|  8 |   90 |
|  9 |   25 |
+----+------+
9 rows in set (0.00 sec)

4、创建数据表同时将查询结果写入到数据表

1
2
3

CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

这种方式是在建表的时候直接将查询结果插入到表中：

mysql> CREATE TABLE test2 (
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> score TINYINT UNSIGNED
    -> )SELECT id,score FROM student;  
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+-------+
| id | score |
+----+-------+
|  1 |    95 |
|  2 |    35 |
|  3 |    45 |
|  4 |    55 |
|  5 |    65 |
|  6 |    75 |
|  7 |    80 |
|  8 |    90 |
|  9 |    25 |
+----+-------+
9 rows in set (0.01 sec)

MySql_13 MySQL中的子查询

MySql_13 MySQL中的子查询

1、什么是子查询？

2、引发子查询的情况？

使用[NOT] IN的子查询

使用比较运算符的子查询

使用[NOT]EXISTS的子查询

使用ANY|SOME或者ALL的子查询

3、将查询结果写入到数据表

4、创建数据表同时将查询结果写入到数据表

使用`[NOT] IN`的子查询