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 |

能不能去重：取決于列資料類型一樣，資料一樣。