mysql笔记(4)

where 语句可以配合 AND、OR使用，在需要明确顺序的情况使用括号。

IN取合法值的由逗号分隔的清单，全都括在圆括号中。IN功能和OR一样，但是比OR更快且简洁

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id IN (1002,1003)
ORDER BY prod_name ;

可以使用NOT配合IN BETWEEN EXISTS进行取反操作。

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name ;

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id NOT BETWEEN 1001 AND 1002
ORDER BY prod_name ;

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id =1001 AND EXISTS 
(SELECT * 
FROM products p2
WHERE vend_id = 1001);

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id =1001 AND EXISTS 
(SELECT * 
FROM products p2
WHERE vend_id = 1009);

使用NOT对exists 取反。

SELECT vend_id ,prod_id ,prod_price ,prod_name 
FROM products p 
WHERE vend_id =1001 AND NOT EXISTS 
(SELECT * 
FROM products p2
WHERE vend_id = 1009);