mysql子查询

三个举例表

表一名：t_book 表二名:t_bookType 表三名:t_bookType

查找列出 t_booktype中id对应t_book中的id 的数值。

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

查找列出 t_booktype中id不对应t_book中的id 的数值。

SELECT * FROM t_book WHERE booktypeId NOTIN (SELECT id FROM t_booktype);

 

查询t_book数据表price字段>= SELECT price FROM t_pricelevel WHEREpriceLevel=1截取数值；

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

 

EXISTS判断！SELECT* FROM t_booktype是否有结果，如果有则执行前面的语句SELECT * FROM t_book。

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

EXISTS判断！SELECT* FROM t_booktype是否有结果，如果有则不执行前面的语句SELECT * FROM t_book。

SELECT * FROM t_book WHERE NOT EXISTS(SELECT * FROM t_booktype);

 

any 满足任何一条件 查询满足SELECT price FROMt_pricelevel中的任何一个数值条件并列出

SELECT * FROM t_book WHERE price>= ANY(SELECT price FROM t_pricelevel);

 

all满足所有条件 查询满足SELECT price FROMt_pricelevel中的所有数值条件并列出

 

SELECT * FROM t_book WHERE price>= ALL(SELECT price FROM t_pricelevel);