mysql学习记录：多表查询、子查询（补充2）

它的多表查询有很多种方式，比如说什么并（UNION）、笛卡尔积（CARTESIAN PRODUCT）、内连接（INNER JOIN）、外连接（OUTER JOIN）、子查询。

并（UNION）：把具有相同字段数目和字段类型的表合在一起，比如说 t_student 表有字段name varchar(20),gender char(5)，另一 t_teacher 表字段及类型一样才能使用UNION将两张表结合起来。

笛卡尔积（CARTESIAN PRODUCT）：将两张看起来关系不大的表连接起来。字段就是两个表的字段相加；记录就是两个表的记录相乘，如表1有4条记录，表2有5条记录，它查询出来的数据就有4*5=20条记录。大概如：

1--------->1,2,3,4,5;

2--------->1,2,3,4,5;

3--------->1,2,3,4,5;

4--------->1,2,3,4,5.

有点像线性代数里面的矩阵。

内连接（INNER JOIN...ON）：

外连接（OUTER JOIN）：

子查询：指在一个查询中嵌套其他若干查询。

因为连接查询性能很差，现在多是使用嵌套查询。这里也主要对嵌套查询测试。

子查询

* WHERE 子句中的子查询：一般返回单行单列，多行单列，单行多列数据记录。

* FROM 子句中的子查询：一般返回多行多列数据记录，可以当做一张临时表。

1.数据准备

t_book增加一个外键字段：ALTER TABLE t_book ADD owner_id INT NOT NULL COMMENT '图书所有者id' AFTER abstract;

创建一个所有者表:

CREATE TABLE t_owner(
owner_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '图书所有者id',
owner_name VARCHAR(20) NOT NULL COMMENT '图书所有者名字'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '图书所有者表';

外键：ALTER TABLE t_book ADD CONSTRAINT fk_book_owner FOREIGN KEY(owner_id) REFERENCES t_owner(owner_id);

2.关联查询

SELECT 

o.`owner_name`,b.`bname`,b.`author`,b.`category`,b.`abstract` 

FROM 

t_owner o,t_book b 

WHERE 

o.`owner_id`=b.`owner_id`;

3.关键字 IN、ANY、ALL、EXISTS

1) 带有关键字IN的子查询

SELECT

bname,author

FROM

t_book b,t_owner o

WHERE 

b.`owner_id` 

IN(

SELECT

o.`owner_id`

FROM

t_owner

WHERE

o.`owner_name` = '王某某');

2)带有关键字ANY的子查询

* =ANY：其功能与关键字IN一样；

* >ANY(>=ANY)：比子查询中返回数据记录中最小的还要大于（大于等于）数据记录；

* <ANY(<=ANY)：比子查询中返回数据记录中最大的还要小于（小于等于）数据记录；

SELECT * FROM t_book WHERE number=ANY(SELECT number FROM t_book WHERE id=1);

SELECT * FROM t_book WHERE number>ANY(SELECT number FROM t_book WHERE id=1);

SELECT * FROM t_book WHERE number<ANY(SELECT number FROM t_book WHERE id=1);

3)带有关键字ALL的子查询

* >ALL(>=ALL)：比子查询中返回数据记录中最大的还要大于（大于等于）；

* <ALL(<=ALL)：比子查询中返回数据记录中最小的还要小于（小于等于）；

SELECT * FROM t_book WHERE number>ALL(SELECT number FROM t_book WHERE id=1);

SELECT * FROM t_book WHERE number<ALL(SELECT number FROM t_book WHERE id=1);

PS:在ANY和ALL的举例中我只使用了一张表，因为一张表更加易于理解，其实可以多表的，只要子查询返回的数据类型是与我们条件数据的要求一致就行。

4)带有关键字EXISTS的子查询

关键字EXISTS是一个布尔类型，当返回结果时为TRUE，不能返回结果时为FALSE，查询时，EXISTS对外表采用遍历方式逐条查询，每次查询都比较EXISTS的条件语句。

SELECT * FROM t_owner o WHERE NOT EXISTS(SELECT * FROM t_book b WHERE b.`owner_id`=o.`owner_id`);

结果：t_owner表 id=3  name=岑天天  （说明：t_book中没有owner_id=3的）

SELECT * FROM t_owner o WHERE EXISTS(SELECT * FROM t_book b WHERE b.`owner_id`=o.`owner_id`);

结果：t_owner表 id=1  name=李雷

t_owner表 id=2  name=王某某

（说明：t_book表中有owner_id=1,2的数据）