mysql多表查询（二）

子查询

in

SELECT * FROM t_book tb WHERE tb.`bookTypeId` IN (SELECT id FROM t_book_type)

not in

SELECT * FROM t_book tb WHERE tb.`bookTypeId` NOT IN (SELECT id FROM t_book_type)
SELECT * FROM t_book WHERE price >= (SELECT price FROM t_price_level WHERE price_level=1)

exists

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_book_type) 
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_test) 

any

SELECT * FROM t_book WHERE price > ANY (SELECT price FROM t_price_level)

all

SELECT * FROM t_book WHERE price > ALL (SELECT price FROM t_price_level)

链接查询

笛卡尔积现象

SELECT * FROM t_book,t_book_type;

内连接查询

SELECT * FROM t_book tb,t_book_type tby WHERE tb.`bookTypeId`=tby.id;

外连接查询

左连接

SELECT * FROM t_book tb LEFT JOIN t_book_type tby ON tb.`bookTypeId`=tby.id;

右连接

SELECT * FROM t_book tb RIGHT JOIN t_book_type tby ON tb.`bookTypeId`=tby.id;

多条件查询

SELECT * FROM t_book tb,t_book_type tby WHERE tb.`bookTypeId`=tby.id AND tb.price>70;

复合查询

# union 两个表里相同字段的所有数据，相同内容去重处理
SELECT id FROM t_book;
SELECT id FROM t_book_type;
SELECT id FROM t_book UNION SELECT id FROM t_book_type;
# union all 两个表里相同字段的所有数据，相同内容不去重
SELECT id FROM t_book;
SELECT id FROM t_book_type;
SELECT id FROM t_book UNION ALL SELECT id FROM t_book_type;