MySQL多表查询之子查询

jlo86

ANY和SOME子查询
Any和some关键字是同义词，表示满足其中任一条件。
通过创建表达式，对返回的结果进行比较，并输出符合条件的结果。

mysql> select * from t2;
+------+-------+------+
| id  | name  | age  |
+------+-------+------+
|   1 | Mark  |   29 |
|   2 | Frank |   32 |
|   3 | Niko  |   27 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+------+-------+------+
| id  | name  | age  |
+------+-------+------+
|   1 | Tube  |   31 |
|   2 | Kevin |   34 |
|   3 | Todd  |   32 |
+------+-------+------+
3 rows in set (0.00 sec)


mysql> select * from t2 where age >any (select age from t1);
+------+-------+------+
| id  | name  | age  |
+------+-------+------+
|   2 | Frank |   32 |
+------+-------+------+
1 row in set (0.07 sec)

EXITS和NOT EXITS子查询
EXITS和NOT EXITS可以说是一种判断子查询。
系统对子查询进行运算，并判断是否有返回行。如果有则执行外层语句中的查询，如果没有则不进行查询。

mysql> select * from log;
+------+----------+------------------------------------------------+
| id  | category | log                                            |
+------+----------+------------------------------------------------+
|   1 | Nginx    | upstream timed out(110: Connection timed out) |
|   2 | Apache   | File does notexist:/var/www/html/1.html       |
|   3 | Apache   | Could not openfile:/var/images/banner.gif     |
|   4 | MySQL    | InnoDB: Unable tolock ./ibdata1, error: 11    |
+------+----------+------------------------------------------------+
4 rows in set (0.05 sec)

mysql>
mysql>
mysql> select * from state;
+---------+-------+---------+---------+
| apache | Nginx | App     | MySQL   |
+---------+-------+---------+---------+
| Succeed | Fail  | Succeed | Succeed |
+---------+-------+---------+---------+
1 row in set (0.09 sec)

mysql> select * from log where category= 'Nginx' and exists (select * from state where Nginx='Fail');
+------+----------+------------------------------------------------+
| id  | category | log                                            |
+------+----------+------------------------------------------------+
|   1 | Nginx    | upstream timed out(110: Connection timed out) |
+------+----------+------------------------------------------------+
1 row in set (0.00 sec)

如果查询结果为真，显示；为假不显示。

mysql> select * from log where category= 'Apache';
+------+----------+--------------------------------------------+
| id  | category | log                                        |
+------+----------+--------------------------------------------+
|   2 | Apache   | File does notexist:/var/www/html/1.html   |
|   3 | Apache   | Could not openfile:/var/images/banner.gif |
+------+----------+--------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from log where category= 'Apache' and exists (select * from state where apache='Fail');
Empty set (0.00 sec)

IN子查询

IN关键字对子查询结果进行运算，并判断是否返回行。如果有则执行外部查询语句。如果没有则不执行。
外查询返回的结果匹配子查询的结果。
mysql> select * from People;
+------+------+-----------+-------------+
| name | age  | certnum  | phone       |
+------+------+-----------+-------------+
| Lee |   20 | 123456789 | 13021981234 |
| John |  32 | 987654321 | 13998763456 |
| Lucy |  45 | 678954321 | 13098784321 |
| Ken |   33 | 986745321 | 18609073544 |
+------+------+-----------+-------------+
4 rows in set (0.03 sec)

mysql> select * from Blacklist;
+------+------+-------+--------+
| ID  | Name | Price | Level  |
+------+------+-------+--------+
|   1 | John |  3000 | normal |
|   2 | Lucy | 24000 | hard   |
+------+------+-------+--------+
2 rows in set (0.08 sec)

mysql> select * from People where nameIN
   -> (select Name from Blacklist);
+------+------+-----------+-------------+
| name | age  | certnum  | phone       |
+------+------+-----------+-------------+
| John |  32 | 987654321 | 13998763456 |
| Lucy |  45 | 678954321 | 13098784321 |
+------+------+-----------+-------------+
2 rows in set (0.00 sec)

IN子查询适合从大的结果集拾取指定条件的小的结果集。这个小的结果集同时也是另外一个表中的内容。

Union子查询 （唯一性）
Union用于合并查询结果。可以将多条select语句查询的结果组合成单个结果集。
列数必须相同，使用ALL关键字则拼接两张表。

mysql> select * from People;
+------+------+-----------+-------------+
| name | age  | certnum  | phone       |
+------+------+-----------+-------------+
| Lee |   20 | 123456789 | 13021981234 |
| John |  32 | 987654321 | 13998763456 |
| Lucy |  45 | 678954321 | 13098784321 |
| Ken |   33 | 986745321 | 18609073544 |
+------+------+-----------+-------------+
4 rows in set (0.00 sec)

mysql> create table People2 like People;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into People2 select * from People where age=45;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> select * from People2;
+------+------+-----------+-------------+
| name | age  | certnum  | phone       |
+------+------+-----------+-------------+
| Lucy |  45 | 678954321 | 13098784321 |
+------+------+-----------+-------------+
1 row in set (0.00 sec)

mysql> insert into People2 values('jingjing',31,123321456,18812344321);
Query OK, 1 row affected (0.01 sec)

mysql> select * from People;
+------+------+-----------+-------------+
| name | age  | certnum  | phone       |
+------+------+-----------+-------------+
| lee |   20 | 123456789 | 13021981234 |
| John |  32 | 987654321 | 13998763456 |
| Lucy |  45 | 678954321 | 13098784321 |
| Ken |   33 | 986745321 | 18609073544 |
+------+------+-----------+-------------+
4 rows in set (0.00 sec)

mysql> select * from People2;
+----------+------+-----------+-------------+
| name    | age  | certnum   | phone      |
+----------+------+-----------+-------------+
| Lucy    |   45 | 678954321 | 13098784321 |
| jingjing |   31 | 123321456 | 18812344321 |
+----------+------+-----------+-------------+
2 rows in set (0.00 sec)


mysql> select * from People union select* from People2;
+----------+------+-----------+-------------+
| name    | age  | certnum   | phone      |
+----------+------+-----------+-------------+
| lee     |   20 | 123456789 | 13021981234 |
| John    |   32 | 987654321 | 13998763456 |
| Lucy    |   45 | 678954321 | 13098784321 |
| Ken     |   33 | 986745321 | 18609073544 |
| jingjing |   31 | 123321456 | 18812344321 |
+----------+------+-----------+-------------+
5 rows in set (0.00 sec)

重复的一条数据不存在了。


mysql> select * from People union allselect * from People2;
+----------+------+-----------+-------------+
| name    | age  | certnum   | phone      |
+----------+------+-----------+-------------+
| lee     |   20 | 123456789 | 13021981234 |
| John    |   32 | 987654321 | 13998763456 |
| Lucy    |   45 | 678954321 | 13098784321 |
| Ken     |   33 | 986745321 | 18609073544 |
| Lucy    |   45 | 678954321 | 13098784321 |
| jingjing |   31 | 123321456 | 18812344321 |
+----------+------+-----------+-------------+
6 rows in set (0.00 sec)

简单的拼接

mysql> select * from Blacklist;
+------+------+-------+--------+
| ID  | Name | Price | Level  |
+------+------+-------+--------+
|   1 | John |  3000 | normal |
|   2 | Lucy | 24000 | hard   |
+------+------+-------+--------+
2 rows in set (0.00 sec)

mysql> select * from People2;
+----------+------+-----------+-------------+
| name    | age  | certnum   | phone      |
+----------+------+-----------+-------------+
| Lucy    |   45 | 678954321 | 13098784321 |
| jingjing |   31 | 123321456 | 18812344321 |
+----------+------+-----------+-------------+
2 rows in set (0.00 sec)



当列数据类型不一样的时候

mysql> select * from Blacklist unionselect * from People2;
+----------+------+-----------+-------------+
| ID      | Name | Price     | Level       |
+----------+------+-----------+-------------+
| 1       | John |      3000 | normal      |
| 2       | Lucy |     24000 | hard        |
| Lucy    | 45   | 678954321 | 13098784321 |
| jingjing | 31   | 123321456 | 18812344321 |
+----------+------+-----------+-------------+
4 rows in set (0.00 sec)

mysql> select * from Blacklist union allselect * from People2;
+----------+------+-----------+-------------+
| ID      | Name | Price     | Level       |
+----------+------+-----------+-------------+
| 1       | John |      3000 | normal      |
| 2       | Lucy |     24000 | hard        |
| Lucy    | 45   | 678954321 | 13098784321 |
| jingjing | 31   | 123321456 | 18812344321 |
+----------+------+-----------+-------------+
4 rows in set (0.00 sec)

加不加ALL一样



mysql> select name,price from Blacklistunion all select name,certnum from People2;
+----------+-----------+
| name    | price     |
+----------+-----------+
| John    |      3000 |
| Lucy    |     24000 |
| Lucy    | 678954321 |
| jingjing | 123321456 |
+----------+-----------+
4 rows in set (0.00 sec)

mysql> select name,price from Blacklistunion select name,certnum from People2;
+----------+-----------+
| name    | price     |
+----------+-----------+
| John    |      3000 |
| Lucy    |     24000 |
| Lucy    | 678954321 |
| jingjing | 123321456 |
+----------+-----------+
4 rows in set (0.00 sec)


mysql> update People2 set certnum=24000where name='Lucy';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1 Warnings: 0



mysql> select name,certnum from People2where name='Lucy';
+------+---------+
| name | certnum |
+------+---------+
| Lucy |  24000 |
+------+---------+
1 row in set (0.00 sec)


mysql> select name,price from Blacklistunion select name,certnum from People2;
+----------+-----------+
| name    | price     |
+----------+-----------+
| John    |      3000 |
| Lucy    |     24000 |
| jingjing | 123321456 |
+----------+-----------+
3 rows in set (0.00 sec)

mysql> select name,price from Blacklistunion all select name,certnum from People2;
+----------+-----------+
| name    | price     |
+----------+-----------+
| John    |      3000 |
| Lucy    |     24000 |
| Lucy    |     24000 |
| jingjing | 123321456 |
+----------+-----------+
4 rows in set (0.00 sec)


能不能去重：取决于列数据类型一样，数据一样。