写在前面
支撑SQL和关系数据库的基础理论：数学领域的集合论和逻辑学标准体系的谓词逻辑
理论篇
什么是谓词？谓词是返回值为真值(true false unknown)的函数
关系数据库里，每一个行数据可以看作是一个命题
实体的阶层
0阶实体(单行) -- 1阶谓词( = between and)
1阶实体(行集合/表) -- 2阶谓词 (exists)
2阶实体(表的集合) -- 3阶谓词 1970被毙掉，目前数据库均以二阶谓词为基准
全称量化与存在量化
- 全称量词：所有的\(x\)都满足条件\(P\)
- 存在量词：存在(至少有一个)满足条件\(P\)的\(x\)
- EXISTS谓词实现了存在量词(因此，可以根据德摩根律实现全称量化)
实践篇
查询表中不存在的数据
/* 查询表中“不”存在的数据 */
CREATE TABLE Meetings
(meeting CHAR(32) NOT NULL,
person CHAR(32) NOT NULL,
PRIMARY KEY (meeting, person));
INSERT INTO Meetings VALUES('第1次', '伊藤');
INSERT INTO Meetings VALUES('第1次', '水岛');
INSERT INTO Meetings VALUES('第1次', '坂东');
INSERT INTO Meetings VALUES('第2次', '伊藤');
INSERT INTO Meetings VALUES('第2次', '宫田');
INSERT INTO Meetings VALUES('第3次', '坂东');
INSERT INTO Meetings VALUES('第3次', '水岛');
INSERT INTO Meetings VALUES('第3次', '宫田');
-- 求所有人参加所有会的笛卡尔积
SELECT DISTINCT m1.meeting,m2.person FROM Meetings AS m1 CROSS JOIN Meetings AS m2;
-- 求出缺席者的SQL语句(1):存在量化的应用
SELECT DISTINCT m1.meeting,m2.person
FROM Meetings AS m1 CROSS JOIN Meetings AS m2
WHERE NOT EXISTS (SELECT * FROM Meetings AS m3 WHERE m1.meeting = m3.meeting AND m2.person = m3.person);
-- 求出缺席者的SQL语句(2):使用差集运算
SELECT m1.meeting,m2.person
FROM Meetings AS m1,Meetings AS m2
EXCEPT
SELECT meeting,person
FROM Meetings;
全称量词(1)：习惯"肯定 \(\Leftrightarrow\) 双重否定"之间的转换
/* 全称量化（1）：习惯“肯定<＝>双重否定”之间的转换 */
CREATE TABLE TestScores
(student_id INTEGER,
subject VARCHAR(32) ,
score INTEGER,
PRIMARY KEY(student_id, subject));
INSERT INTO TestScores VALUES(100, '数学',100);
INSERT INTO TestScores VALUES(100, '语文',80);
INSERT INTO TestScores VALUES(100, '理化',80);
INSERT INTO TestScores VALUES(200, '数学',80);
INSERT INTO TestScores VALUES(200, '语文',95);
INSERT INTO TestScores VALUES(300, '数学',40);
INSERT INTO TestScores VALUES(300, '语文',90);
INSERT INTO TestScores VALUES(300, '社会',55);
INSERT INTO TestScores VALUES(400, '数学',80);
-- 查出所有科目分数都在50分以上的学生
SELECT DISTINCT student_id FROM TestScores AS TS1
WHERE NOT EXISTS (SELECT * FROM TestScores AS TS2 WHERE TS2.student_id
= TS1.student_id AND TS2.score < 50) -- 仅就本题而言，还可以使用min(score)>=50
-- 查找出数学分数>=80,语文分数>=50的学生
SELECT DISTINCT student_id FROM TestScores AS TS1
WHERE subject IN ('数学','语文') AND NOT EXISTS (SELECT * FROM TestScores AS TS2 WHERE TS2.student_id
= TS1.student_id AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
WHEN subject = '语文' AND score < 50 THEN 1 ELSE 0 END)
GROUP BY student_id
HAVING COUNT(*) = 2; -- group by having 子句要求两门课程都要有成绩
全称量化(2)：集合和谓词，哪个更强大？
/* 全称量化（2）：集合VS谓词——哪个更强大？ */
CREATE TABLE Projects
(project_id VARCHAR(32),
step_nbr INTEGER ,
status VARCHAR(32),
PRIMARY KEY(project_id, step_nbr));
INSERT INTO Projects VALUES('AA100', 0, '完成');
INSERT INTO Projects VALUES('AA100', 1, '等待');
INSERT INTO Projects VALUES('AA100', 2, '等待');
INSERT INTO Projects VALUES('B200', 0, '等待');
INSERT INTO Projects VALUES('B200', 1, '等待');
INSERT INTO Projects VALUES('CS300', 0, '完成');
INSERT INTO Projects VALUES('CS300', 1, '完成');
INSERT INTO Projects VALUES('CS300', 2, '等待');
INSERT INTO Projects VALUES('CS300', 3, '等待');
INSERT INTO Projects VALUES('DY400', 0, '完成');
INSERT INTO Projects VALUES('DY400', 1, '完成');
INSERT INTO Projects VALUES('DY400', 2, '完成');
-- 查询完成到了工程1的项目 having子句解法
SELECT product_id FROM Projects GROUP BY project_id HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= AND status = '完成' THEN 1 WHEN step_nbr > 1 AND status = '等待' THEN 1 ELSE 0 END);
-- 查询完成到了工程1的项目 having子句解法 谓词解法
SELECT * FROM Projects P1 WHERE NOT EXISTS (SELECT status from Projects P2 WHERE P1.project_id = P2.project_id AND status <> CASE WHEN step_nbr <= 1 THEN '完成' ELSE '等待' END);
-- 劣势：双重否定，不易理解；优势：性能好，只要有一个行满足条件，查询就会终止；包含的信息更全
对列进行量化：查询全是1的行
/* 对列进行量化：查询全是1的行 */
CREATE TABLE ArrayTbl
(keycol CHAR(1) PRIMARY KEY,
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
col4 INTEGER,
col5 INTEGER,
col6 INTEGER,
col7 INTEGER,
col8 INTEGER,
col9 INTEGER,
col10 INTEGER);
--全为NULL
INSERT INTO ArrayTbl VALUES('A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO ArrayTbl VALUES('B', 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
--全为1
INSERT INTO ArrayTbl VALUES('C', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
--至少有一个9
INSERT INTO ArrayTbl VALUES('D', NULL, NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO ArrayTbl VALUES('E', NULL, 3, NULL, 1, 9, NULL, NULL, 9, NULL, NULL);
-- "列方向"的全称量化：查找全是1的行 不优雅的解答
SELECT * FROM ArrayTbl WHERE col1 = 1 AND col2 = 1 AND col3 = 1 AND col4 = 1 AND col5 = 1
AND col6 = 1 AND col7 = 1 AND col8 = 1 AND col9 = 1 AND col10 = 1;
-- "列方向"的全称量化：查找全是1的行 优雅的解答
SELECT * FROM ArrayTbl WHERE 1 = ALL (values(col1),(col2),(col3),(col4),(col5),(col6),(col7),(col8),(col9),(col10));
-- "列方向"的全称量化：查找某一列是9的行
SELECT * FROM ArrayTbl WHERE 9 = ANY (values(col1),(col2),(col3),(col4),(col5),(col6),(col7),(col8),(col9),(col10));
-- "列方向"的全称量化：查找某一列是9的行
SELECT * FROM ArrayTbl WHERE 9 IN (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10);
-- 查找全是NULL的行
SELECT * FROM ArrayTbl WHERE COALESCE(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10) IS NULL;
小结
- SQL中的谓词指的是返回真值的函数
- EXISTS与其他谓词不同，接受的参数是集合
- 因此EXISTS可以看成一种高阶函数
- SQL没有与全称量词相当的谓词，可以使用NOT EXISTS代替
练习题
/* 练习题1-8-1:数组表——行结构表的情况 */
CREATE TABLE ArrayTbl2
(key CHAR(1) NOT NULL,
i INTEGER NOT NULL,
val INTEGER,
PRIMARY KEY (key, i));
/* A全为NULL、B仅有一个为非NULL、C全为非NULL */
INSERT INTO ArrayTbl2 VALUES('A', 1, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('A',10, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 1, 3);
INSERT INTO ArrayTbl2 VALUES('B', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('B',10, NULL);
INSERT INTO ArrayTbl2 VALUES('C', 1, 1);
INSERT INTO ArrayTbl2 VALUES('C', 2, 1);
INSERT INTO ArrayTbl2 VALUES('C', 3, 1);
INSERT INTO ArrayTbl2 VALUES('C', 4, 1);
INSERT INTO ArrayTbl2 VALUES('C', 5, 1);
INSERT INTO ArrayTbl2 VALUES('C', 6, 1);
INSERT INTO ArrayTbl2 VALUES('C', 7, 1);
INSERT INTO ArrayTbl2 VALUES('C', 8, 1);
INSERT INTO ArrayTbl2 VALUES('C', 9, 1);
INSERT INTO ArrayTbl2 VALUES('C',10, 1);
/* 正确解法 */
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE NOT EXISTS
(SELECT *
FROM ArrayTbl2 A2
WHERE A1.key = A2.key
AND (A2.val <> 1 OR A2.val IS NULL));
/* 其他解法1：使用ALL谓词 */
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE 1 = ALL
(SELECT val
FROM ArrayTbl2 A2
WHERE A1.key = A2.key);
/* 其他解法2：使用HAVING子句 */
SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) = 10;
/* 其他解法3：在HAVING子句中使用极值函数 */
SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING MAX(val) = 1
AND MIN(val) = 1;
/* 练习题1-8-2：使用ALL谓词进行全称量化
查找已经完成到工程1的项目：使用ALL谓词解答 */
SELECT *
FROM Projects P1
WHERE '○' = ALL
(SELECT CASE WHEN step_nbr <= 1 AND status = '完成' THEN '○'
WHEN step_nbr > 1 AND status = '等待' THEN '○'
ELSE '×' END
FROM Projects P2
WHERE P1.project_id = P2. project_id);
/* 练习题1-8-3：求（1-100）中的质数 */
SELECT num AS prime
FROM Numbers Dividend
WHERE num > 1
AND NOT EXISTS
(SELECT *
FROM Numbers Divisor
WHERE Divisor.num <= Dividend.num / 2 /* 除了自身之外的约数必定小于等于自身值的一半 */
AND Divisor.num <> 1 /* 约数中不包含1 */
AND MOD(Dividend.num, Divisor.num) = 0) /*“除不尽”的否定条件是“除尽” */
ORDER BY prime;