mysql基础（六）子查询与多表连接查询

建表：

CREATE TABLE no6(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),age TINYINT,money INT,tel CHAR(11));

插入数据：

INSERT INTO no6 VALUES(NULL,\'张三\',30,2000,13565234121),(NULL,\'李四\',23,1000,13814526352),(NULL,\'王五\',40,8000,13913656352),(NULL,\'赵六\',33,8000,13913121234)

使用子查询为条件进行查询：

SELECT NAME FROM no6 WHERE age IN (SELECT age FROM no6 WHERE money >=2000) AND tel LIKE \'%34\'

随便写的哈，虽然这种子查询没有意义

更新、删除操作也可以使用子查询，但是子查询不能，更删操作表和子查询表不能相同：
操作与子查询是同一张表：
更新语句

UPDATE no6 SET money=(SELECT MAX(money) FROM no6)/2 WHERE id=(SELECT id FROM no6 ORDER BY money LIMIT 1)

报错：

删除语句

DELETE FROM no6 WHERE id=(SELECT id FROM no6 ORDER BY money LIMIT 1)

也是报错

更删no6表，子查询使用no5表
更新

UPDATE no6 SET money=(SELECT MAX(money) FROM no5)/2 WHERE id=(SELECT id FROM no5 ORDER BY money LIMIT 1)

删除

DELETE FROM no6 WHERE id=(SELECT id FROM no5 ORDER BY money LIMIT 1)

any、all、exists用法：
先插入几条数据

INSERT INTO no6 VALUES(NULL,\'二蛋\',35,1500,13565234121),(NULL,\'大柱\',28,3400,13814526352),(NULL,\'铁头\',42,9100,13913656352),(NULL,\'小吴\',37,4500,13913121234)

测试语句

SELECT money FROM no6 WHERE money>2000 AND money<6000

查询结果

就以这条语句为子查询（结果是3400-4500）为取值范围：

SELECT * FROM no6 WHERE money >ANY(SELECT money FROM no6 WHERE money>2000 AND money<6000)

结果中有4500，没有3000。

any是任意的意思，>any（）的取值范围是大于括号里面的任意一个数，返回结果就是大于最小的数。

改成<any

SELECT * FROM no6 WHERE money <ANY(SELECT money FROM no6 WHERE money>2000 AND money<6000)

结果里面有3400，

<any返回就是小于取值范围中的最大值。

再改成=any（any与some完全相同）

SELECT * FROM no6 WHERE money =ANY(SELECT money FROM no6 WHERE money>2000 AND money<6000)

=any也就是返回取值范围内的任意值,=any相当于in，只是in的取值范围里可以直接放数字

把any改成all

SELECT * FROM no6 WHERE money =ALL (SELECT money FROM no6 WHERE money>2000 AND money<6000)

结果为空，

=all只有在取值范围只有一条的情况下才生效。

改成>all

SELECT * FROM no6 WHERE money >ALL (SELECT money FROM no6 WHERE money>2000 AND money<6000)

结果大于取值范围内的所有值。
改成<all

SELECT * FROM no6 WHERE money <ALL (SELECT money FROM no6 WHERE money>2000 AND money<6000)

结果小于取值范围内的所有值

。

改成<>all

SELECT * FROM no6 WHERE money <>ALL (SELECT money FROM no6 WHERE money>2000 AND money<6000)

not in相当于<>all，但是not in的取值范围可以直接以数字表示

SELECT * FROM no6 WHERE money NOT IN(3400,4500)

exists()，查看是否存在：

SELECT * FROM no6 WHERE EXISTS(SELECT * FROM no6 WHERE NAME=\'王八\')

因为子查询结果不存在，查询不生效

SELECT * FROM no6 WHERE EXISTS(SELECT * FROM no6 WHERE NAME=\'大柱\')

当子查询结果存在，查询生效。

多表连接：
先建表并插入数据

CREATE TABLE no7(id INT PRIMARY KEY AUTO_INCREMENT,sex CHAR(5) DEFAULT\'男\',NAME VARCHAR(10),address VARCHAR(20));INSERT INTO no7 VALUES(NULL,\'女\',\'张三\',\'八一路\'),(NULL,\'男\',\'赵六\',\'人民路\'),(NULL,\'男\',\'小强\',\'解放路\'),(NULL,\'女\',NULL,\'黄泉路\');

内连接示例：

SELECT * FROM no6 t1 JOIN no7 t2 ON t1.id=t2.id

返回结果是两表的交集。join前的inner可以省略，t1和t2是两张表的别名，用如下语句同样可以实现如上功能：

SELECT * FROM no6,no7 WHERE no6.id=no7.id

指定字段进行查询的时候要注意两表是否有同名字段，如同名要说明是哪张表的：

SELECT t1.name,age,sex,t2.name FROM no6 t1 JOIN no7 t2 ON t1.id=t2.id

外连接分为左连接和右连接
左连接示例：

SELECT * FROM no6 a LEFT OUTER JOIN no7 b ON a.id=b.id

左外连接返回结果是右表的一部分（交集）和左表的全部,join前的outer可以省略。
当如上查询语句加上右表关联字段为空的条件：

SELECT * FROM no6 a LEFT OUTER JOIN no7 b ON a.id=b.id WHERE b.id IS NULL

返回结果是左表减去交集后的剩余部分，也就是左表与右表的差集。
右外连接演示：

SELECT * FROM no6 a RIGHT JOIN no7 b ON a.id=b.id

返回结果为左表一部分（交集），右表全部。
语句后面加上条件左表关联字段为空：

SELECT * FROM no6 a RIGHT JOIN no7 b ON a.id=b.id WHERE a.id IS NULL

返回结果只有右表减去交集后剩余部分（右表与左表的差集）。
全连接：
mysql中是没有全连接的，但是我们可以用union实现，union用来实现多条select的语句的联合，可以把多条SELECT语句的结果组合到一个结果集合中。
先做个演示：

SELECT NAME FROM no6 WHERE id>1UNIONSELECT NAME FROM no7 WHERE id>1;

这两条语句没有union的时候是不能同时执行的，而现在的结果是怎么呢？

值得注意的是使用union的时候每条语句的查询字段数量要相同。
全连接演示：

SELECT * FROM no6 a LEFT JOIN no7 b ON a.id=b.idUNIONSELECT * FROM no6 a RIGHT JOIN no7 b ON a.id=b.id

语句也很简单，就是左连接和右连接用union联合，结果如下：

返回的是两张表的所有内容，也就是两表并集。
再用两张表的关联字段为空过滤掉两表交集：

SELECT * FROM no6 a LEFT JOIN no7 b ON a.id=b.id  WHERE b.id IS NULLUNIONSELECT * FROM no6 a RIGHT JOIN no7 b ON a.id=b.id  WHERE a.id IS NULL

这两条查询语句之前都已经测试过了，左连接过滤掉交集返回结果是左表与右表差集，右连接过滤掉交集返回结果是右表与左表的差集。那么联合语句返回结果就是除了两表除了交集之外的所有内容，也就是两表的对称差集：

自连接：
自连接是一种特殊的连接，把一张表当成两张表使用，也就是自己连自己。
重建no7表，并插入数据：

CREATE TABLE no7(id INT PRIMARY KEY AUTO_INCREMENT,sex CHAR(5) DEFAULT\'男\',NAME VARCHAR(10),address VARCHAR(20),cid INT);INSERT INTO no7 VALUES(NULL,\'女\',\'张三\',\'八一路\',4),(NULL,\'男\',\'赵六\',\'人民路\',3),(NULL,\'男\',\'小强\',\'解放路\',2),(NULL,\'女\',NULL,\'黄泉路\',1);

进行自连接查询：

SELECT a.id,a.name AS \'正序名字\',b.name AS \'倒序名字\' FROM no7 a INNER JOIN no7 b ON a.id=b.cid  ORDER BY a.id

以表自身两个字段进行关联，然后以对同一个字段name进行了两次查询。
有一张图对各种表连接进行了说明，不是mysql的，所以仅供参考：