谓词时返回值为真值(true、false或unknown)的函数。EXISTS与其他谓词不同,它接受的参数是行的集合。
输入值为一行的谓词叫做“一阶谓词”(例如>、<、= 及 LIKE等);输入值为行的集合的谓词叫做“二阶谓词”(例如EXISTS);输入值为集合的集合的谓词叫做“三阶谓词”。SQL并不会出现三阶以上的情况。
1 实践
1.1 查找表中“不”存在的数据
图 参会信息t_meeting_records 表
需求:查找出每次会议中没有参与的人。
SELECT DISTINCT m1.meeting,m2.person
FROM t_meeting_records m1 CROSS JOIN t_meeting_records m2
WHERE NOT EXISTS
(SELECT *FROM t_meeting_records WHERE meeting = m1.meeting AND person = m2.person
)
1.1.1 SQL 的集合运算
执行SQL的集合运算,需要满足下面的条件:
- 所有查询中的列数和列的顺序必须相同。
- 两个查询结果集中对应的列数据类型可以不同,但必须兼容。
- 两个查询结果集中的列不能包含不可比较的数据类型(例如text、blog等)。
- 返回的结果集的列名与操作符左侧的查询相同,ORDER BY 子句中的列名或别名必须引用左侧查询返回的列名。
- 不能与COMPUTE 和COMPUTE BY子句一起使用。
- 通过比较行来确定非重复值时,两个NULL值被视为相等。
EXCEPT | 获取在左侧的集合存在,但是不存在于右侧集合中的数据。 会去重。 |
INTERSECT | 交集,会去重。 |
UNION | 并集,UNION会去重,UNION ALL 不会去重。 |
表 SQL 的集合运算
SELECT m1.meeting,m2.person
FROM t_meeting_records m1 CROSS JOIN t_meeting_records m2
EXCEPT
SELECT meeting,person
FROM t_meeting_records
1.2 肯定与双重否定转换
图 学生成绩信息t_student_grade 表
需求:查询出科目分数都在50分以上的学生。
SELECT *
FROM t_student_grade g1
WHERE NOT EXISTS
(SELECT *FROM t_student_grade g2 WHERE g2.student_id = g1.student_id AND g2.score < 50
)
需求:查处满足下列条件的学生,1)数学分数在80分以上。2)语文分数在50分以上。
SELECT student_id
FROM t_student_grade g1
WHERE g1.`subject` IN ("数学","语文") AND NOT EXISTS
(SELECT *FROM t_student_grade g2WHERE g2.student_id = g1.student_id AND 1 = CASE WHEN g2.`subject` = '数学' AND g2.score < 80 THEN 1WHEN g2.`subject` = '语文' AND g2.score < 50 THEN 1 ELSE 0 END
)
GROUP BY g1.student_id
HAVING COUNT(*) = 2
1.3 EXISTS 与 HAVING
图 工程进展记录信息t_project_info 表
需求:查询哪些项目已经完成到了工程1。
SELECT *
FROM t_project_info p1
WHERE NOT EXISTS
(SELECT *FROM t_project_info p2 WHERE p2.project_id = p1.project_id AND p2.status != CASE WHEN p2.step_number = 0 THEN '完成'WHEN p2.step_number = 1 THEN '完成'ELSE '等待' END
)
-- HAVING
SELECT project_id
FROM t_project_info
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_number <= 1 AND `status` = '完成' THEN 1WHEN step_number > 1 AND `status` = '等待' THEN 1ELSE 0 END
)
ESISTS 代码看起来不那么容易理解,但是性能好,而且结果包含的信息量更大。
2 练习
2.1 行结构
图 行结构表t_key_val
需求:查出value 全为1的key。
SELECT *
FROM t_key_val k1
WHERE NOT EXISTS(SELECT * FROM t_key_val k2 WHERE k2.`key` = k1.`key`AND (k2.value != 1 OR k2.`value` IS NULL)
);
-- HAVING
SELECT `key`
FROM t_key_val
GROUP BY `key`
HAVING
COUNT(*) = SUM(CASE WHEN `value` = 1 THEN 1 ELSE 0 END
);
-- ALL
SELECT DISTINCT `key`
FROM t_key_val k1
WHERE
1 = ALL (SELECT `value` FROM t_key_val k2 WHERE k2.`key` = k1.`key`
)
2.2 全称量化 ALL
需求:用ALL 完成1.3的需求。
需求:用ALL 完成1.3的需求。
-- ALL
SELECT *
FROM t_project_info p1
WHERE 1= ALL (SELECT CASE WHEN p2.step_number <= 1 AND p2.`status` = '完成' THEN 1WHEN p2.step_number > 1 AND p2.`status` = '等待' THEN 1ELSE 0 ENDFROM t_project_info p2 WHERE p2.project_id = p1.project_id
)
2.3 求质数
图 数字集t_number 表
SELECT *
FROM t_number n1
WHERE num > 1
AND NOT EXISTS (SELECT *FROM t_number n2WHERE n2.num <= (n1.num / 2) AND n2.num > 1 AND MOD(n1.num,n2.num) = 0
)