mysql入门笔记_MySQL入门笔记（二）

MySQL的数据类型、数据库操作、针对单表的操作以及简单的记录操作可参考：MySQL入门笔记(一)

五、子查询

子查询可简单地理解为查询中的查询，即子查询外部必然还有一层查询，并且这里的查询并非仅仅指SELECT的查询操作，而是包括INSERT、DELETE、SET等操作在内的所有操作。

1. 使用比较运算符的子查询

operand comparison_operator [{ANY | SOME | ALL}] (subquery)

operand为操作数，即参与比较运算的数；在语句的最后为subquery子查询，子查询必须写在小括号内；ANY、SOME与ALL关键字用于子查询返回结果不唯一的情况，下面通过例子说明几个关键字的作用。

例：存在下表test

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

| id | name | age |

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

| 1 | John | 20 |

| 2 | Kity | 24 |

| 3 | Tommy | 26 |

| 4 | Jackson | 31 |

| 5 | Pat | 19 |

| 6 | Darling | 33 |

| 7 | Tamzin | 29 |

| 8 | Hoking | 21 |

| 9 | Obama | 22 |

| 10 | Bush | 25 |

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

现需要查询年龄大于平均值的的人，按照普通的方法，则需要先计算出年龄平均值SELECT AVG(age) FROM test;(结果为25.0000)，再根据输出的结果进行查询SELECT * FROM test WHERE age > 25;；若使用子查询，则可将两步合为一步：

SELECT * FROM test WHERE age > (SELECT AVG(age) FROM test);

这就是子查询，而ANY、SOME以及ALL关键字的作用则是，当子查询返回的结果存在多条记录时，规定其比较方法，例如，现在要查询比id<=3的记录中任意一记录的age小的记录：

SELECT * FROM test WHERE age < ANY (SELECT age FROM test WHERE id <= 3);

SOME的作用与ANY相同，而ALL则是如其含义一般的“全部”，例如在上面的例子中，若将ANY改成ALL，则返回的结果的age必须大于id<=3的全部记录的age。

2. 使用[NOT] IN 的子查询

operand [NOT] IN (subquery)

[NOT] IN的作用为检测某些记录是否存在指定表中，其中IN等效于= ANY；NOT IN等效于!= ALL或者<> ALL。

例：以下两个语句结果完全一样：

SELECT * FROM test WHERE age = ANY (SELECT age FROM test WHERE id <= 3);

SELECT * FROM test WHERE age IN (SELECT age FROM test WHERE id <= 3);

3. 使用[NOT] EXISTS的子查询

若子查询返回任意行，EXISTS返回1；否则为0。

例：以下操作返回结果则为0：

SELECT EXISTS (SELECT age FROM test WHERE id < 0);

六、多表操作

1. 连接

table_reference {[INNER|CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN} table_reference ON conditional_expr

连接，如其字面含义，连接多个数据表，使其产生物理上的关系(即实际存在关系，而非仅仅为逻辑上的关系)。ON关键字用于设定其后的连接条件；{[INNER|CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN}为连接类型，分别是INNER JOIN内连接、LEFT [OUTER] JOIN左外连接和RIGHT [OUTER] JOIN右外连接三种：

内连接为仅返回符合连接条件的记录，如下图所示，内连接返回的结果为两表的交集部分。其中JOIN、INNER JOIN和CROSS JOIN作用相同，均表示内连接。

例：存在以下两表：

users：

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

| id | name | age | location |

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

| 1 | John | 20 | 3 |

| 2 | Kity | 24 | 3 |

| 3 | Tommy | 26 | 4 |

| 4 | Jackson | 31 | 3 |

| 5 | Pat | 19 | 4 |

| 6 | Darling | 33 | 2 |

| 7 | Tamzin | 29 | 2 |

| 8 | Hoking | 21 | 2 |

| 9 | Obama | 22 | 3 |

| 10 | Bush | 25 | 1 |

| 11 | Richard | 22 | 12 |

| 12 | Andy | 22 | 13 |

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

lname：

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

| id | name |

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

| 1 | 北京市 |

| 2 | 上海市 |

| 3 | 广州市 |

| 4 | 深圳市 |

| 5 | 杭州市 |

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

现进行以下操作：

SELECT * FROM users INNER JOIN lname ON users.location=lname.id;

结果为：

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

| id | name | age | location | id | name |

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