MySQL多表查询之子查询

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;

|   1 | Tube  |   31 |

|   2 | Kevin |   34 |

|   3 | Todd  |   32 |

mysql> select * from t2 where age >any (select age from t1);

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/p_w_picpaths/banner.gif     |

|   4 | MySQL    | InnoDB: Unable tolock ./ibdata1, error: 11    |

4 rows in set (0.05 sec)

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');

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/p_w_picpaths/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);

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

Union子查询 （唯一性）

Union用于合并查询结果。可以将多条select语句查询的结果组合成单个结果集。

列数必须相同，使用ALL关键字则拼接两张表。

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;

mysql> insert into People2 values('jingjing',31,123321456,18812344321);

Query OK, 1 row affected (0.01 sec)

| lee |   20 | 123456789 | 13021981234 |

+----------+------+-----------+-------------+

| name    | age  | certnum   | phone      |

| Lucy    |   45 | 678954321 | 13098784321 |

| jingjing |   31 | 123321456 | 18812344321 |

mysql> select * from People union select* from People2;

| lee     |   20 | 123456789 | 13021981234 |

| John    |   32 | 987654321 | 13998763456 |

| Ken     |   33 | 986745321 | 18609073544 |

5 rows in set (0.00 sec)

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

mysql> select * from People union allselect * from People2;

6 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 |

mysql> select * from Blacklist union allselect * from People2;

加不加ALL一样

mysql> select name,price from Blacklistunion all select name,certnum from People2;

+----------+-----------+

| name    | price     |

| John    |      3000 |

| Lucy    |     24000 |

| Lucy    | 678954321 |

| jingjing | 123321456 |

mysql> select name,price from Blacklistunion select name,certnum from People2;

mysql> update People2 set certnum=24000where name='Lucy';

Rows matched: 1  Changed: 1 Warnings: 0

mysql> select name,certnum from People2where name='Lucy';

+------+---------+

| name | certnum |

| Lucy |  24000 |

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