Mysql高级操作（select嵌套，多表JOIN）

如果展示格式有问题，请移步到www.yuque.com/mrhuang-ire…

select嵌套查询

1. 返回一个值

当子查询的返回值只有一个时，可以使用比较运算符可以使用比较运算符如=、<、>、>=、<=、!=等将主查询和子查询连接起来。示例：

SELECT name,age 
FROM person 
WHERE age > (SELECT age FROM person WHERE name = '张三');

2. 返回一组值

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入IN、ANY、SOME、ALL, 或者EXISTS。

• IN，用于where子句中用来判断查询的表达式是否在子查询集合中，返回满足IN列表中的记录。IN的语法是：<表达式> in (子查询）
• ANY，与子查询集每个元素的比较，部分值为True，那么结果就为True。ANY的语法是：<表达式>{ =|<>|!=|>|>=|!>|<|<=|!<}ANY(子查询) , SOME与ANY语法一致。
• ALL，如果与子查询集每个元素的比较都为true，那么结果才能为true。ALL的语法是：<表达式> { =|<>|!=|>|>=|!>|<|<=|!<} all(子查询)
• EXISTS, 如果子查询返回有值，那么主查询数据返回。

SELECT name 
FROM person
WHERE country_id IN (SELECT country_id FROM country where country_name = '中国');

3. **返回新数据表 **

select返回的数据可以当做临时表用，语法是：(<子查询>) <临时表名>, 注意需要加上临时表名。示例：

SELECT name
FROM (SELECT name,country_id FROM country where country_name = '中国') a;

多表JOIN查询

多表查询，又为连接查询，是指在两张表（多张表）中进行查询的操作。当查询的字段来自多个表，先将表与表之间通过主键和外键联系在一起，再执行多表查询操作。

语法结构：

SELECT column_name1[,column_name2,...]
FROM table_name_1
[LEFT OR RIGHT OR INNER] JOIN table_name_2 ON conditions
[ [LEFT OR RIGHT OR INNER] JOIN tables_name_3 ON conditons,...]
[WHERE conditions]

其中有两个关键字： JOIN: 建立表与表进行连接，分为内连接(INNER JOIN)，左外连接(LEFT JOIN)，右外连接(RIGHT JOIN), 全连接(FULL JOIN)。 ON: 表与表之间连接条件。

执行顺序：

• FROM

  以两张表进行连接为例，对左右两张表执行笛卡尔积，产生第一张临时表vt1。行数为n*m（n为左表的行数，m为右表的行数)； 笛卡尔积，又叫交叉连接(CROSS JOIN)， 在数学中如果两个集合 X 和 Y ，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合。以两表连接为例，假设第一张表有n条数据，第二张表有m条数据，根据笛卡尔方法生成一张临时表vt1, 行数为n * m。

• ON

根据ON的条件逐行筛选vt1，将结果聚合，生成新临时表vt2中；

• JOIN

添加外部行，如果指定了LEFT JOIN(LEFT OUTER JOIN)，则先遍历一遍左表的每一行，其中不在vt2的行会被插入到vt2，该行的剩余字段将被填充为NULL，形成新临时表vt3；如果指定了 RIGHT JOIN也是同理。但如果指定的是INNER JOIN，则不会添加外部行，上述插入过程被忽略，vt2=vt3（所以INNER JOIN的过滤条件放在ON或WHERE里 执行结果是没有区别的);

• WHERE

对vt3进行条件过滤，满足条件的行被输出到新临时表vt4；

• SELECT

取出vt4的指定字段到vt5；

1.内连接

特点： 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示，比如通过: 从表的外键 = 主表的主键方式去匹配。

• 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据

• 如果匹配不到, 都不展示；

2.左外连接

会读取左边数据表的全部数据，即使右边表无对应数据。

特点： 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

①以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据

②如果匹配不到, 左表中的数据正常展示, 右边的展示为null.

3.右外连接

读取右边数据表的全部数据，即使左边边表无对应数据。

特点： 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

• 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据

• 如果匹配不到，右表中的数据正常展示, 左边展示为null

示例:

//初始数据
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程  | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)

mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)
 

//内连接示例: 
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a 
INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)
 
//左连接示例
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a 
LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

//右连接示例
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a 
RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

参考： [1].blog.csdn.net/u013158317/… [2].www.runoob.com/mysql/mysql… [3].segmentfault.com/a/119000001… [4].www.codeproject.com/Articles/33…