MySQL中关键字ANY，ALL，SOME，IN，EXISTS，UNION

1.ANY关键字

假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，

where a > any(...);    ->    where a > result1 or a > result2 or a > result3;

 

2.ALL关键字

ALL关键字与any关键字类似，只不过上面的or改成and。即:

where a > all(...);    ->    where a > result1 and a > result2 and a > result3;

 

3.SOME关键字
some关键字和any关键字是一样的功能。所以:

where a > some(...);    ->    where a > result1 or a > result2 or a > result3;

 

4.IN关键字

IN运算符用于WHERE表达式中，以列表项的形式支持多个选择，语法如下：

SELECT ... FROM ... WHERE column IN (value1,value2,...);
SELECT ... FROM ... WHERE column NOT IN (value1,value2,...);

IN 与 "＝ANY" 相等。因此，这两个语句是一样的：

SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

NOT IN 与 "<>ALL" 相等

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);

 

5.EXISTS关键字

EXISTS 和 NOT EXISTS 子查询语法如下：

SELECT ... FROM ... WHERE EXISTS (subquery);
SELECT ... FROM ... WHERE NOT EXISTS (subquery);

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

•EXISTS (subquery) 只返回 TRUE 或 FALSE，因此子查询中的 SELECT * 也可以是 SELECT 1 或其他，官方说法是实际执行时会忽略 SELECT 清单，因此没有区别。

•EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代，何种最优需要具体问题具体分析

 

6.UNION关键字

UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中。语法为：

SELECT column,... FROM table1 
UNION [ALL]
SELECT column,... FROM table2

当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。

 

例子:

有如下表

-- 建表语句
CREATE TABLE score (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR (20),
	subject VARCHAR (20),
	score INT
);

-- 添加数据
INSERT INTO score
VALUES
	(NULL, '张三', '语文', 80),
	(NULL, '张三', '数学', 75),
	(NULL, '李四', '语文', 76),
	(NULL, '李四', '数学', 90),
	(NULL, '王五', '语文', 81),
	(NULL, '王五', '数学', 100),
	(NULL, '王五', '英语', 90);

用一条 SQL 语句查询出每门课都大于 80 分的学生姓名

-- not exists 字段
SELECT DISTINCT name
FROM score a 
WHERE NOT EXISTS (SELECT * FROM score b WHERE a.name = b.name AND b.score <= 80);

-- not in 字段
SELECT DISTINCT name
FROM score 
WHERE name NOT IN (SELECT name FROM score WHERE score <= 80)

-- all()方法
SELECT  DISTINCT name
FROM score a
WHERE 80 < ALL(SELECT score FROM score WHERE a.name = name );

 

转载于:https://my.oschina.net/u/2609161/blog/3060715