是否有一种系统的逐步或数学方法可以从给定的人类可读描述中构造SQL查询?
例如,如何确定SQL查询是否需要连接而不是子查询,是否需要分组,是否需要IN子句等等...
例如,曾经学过数字电子学的人都知道像Karnaugh Map或Quin McClausky方法这样的方法。这些都是一些简化数字逻辑的系统方法。
如果有像这样的方法可以手动分析SQL查询以避免每次都进行头脑风暴吗?
[liker] likes [liked]
表格存储每一行,使用行的列值填充(命名的)空白,生成一个真命题。这是一个带有该谓词及其行命题的表格:
liker | liked
--------------
Bob | Dex /* Bob likes Dex */
Bob | Alice /* Bob likes Alice */
Alice | Carol /* Alice likes Carol */
将谓词用表中一行的值填充,得到的每个命题都是真的。而将谓词用不在表中的一行的值填充,得到的每个命题都是假的。下面是表格内容:
/*
Alice likes Carol
AND NOT Alice likes Alice
AND NOT Alice likes Bob
AND NOT Alice likes Dex
AND NOT Alice likes Ed
...
AND Bob likes Alice
AND Bob likes Dex
AND NOT Bob likes Bob
AND NOT Bob likes Carol
AND NOT Bob likes Ed
...
AND NOT Carol likes Alice
...
AND NOT Dex likes Alice
...
AND NOT Ed likes Alice
...
*/
数据库管理员为每个基本表提供谓词。表声明的SQL语法非常类似于给定谓词的自然语言版本的传统逻辑简写。下面是一个声明基本表以保存我们的值的示例:
/* (person, liked) rows where [liker] likes [liked] */
/* (person, liked) rows where Likes(liker, liked) */
CREATE TABLE Likes (
liker ...,
liked ...
);
假设R
的谓词是r
,而S
的谓词是s
。
一个SQL查询(子)表达式将参数表中的值转换为一个新的表值,其中包含使新谓词成立的行。根据(子)表达式的关系/表运算符,可以用参数表谓词来表达新表谓词。查询是一个SQL表达式,其谓词是我们想要的行表的谓词。
当我们给一个表和(可能隐含的)别名A
加入时,该运算符会对一个值和谓词进行操作,类似于表的操作,但列名从C,...
改为A.C,...
。然后
R , S
& R CROSS JOIN S
are rows where r AND s
R WHERE condition
is rows where r AND condition
R INNER JOIN S ON condition
is rows where r AND s AND condition
R LEFT JOIN S ON condition
is rows where (for S
-only columns S1
,...)
r AND s AND condition
OR
r
AND NOT FOR SOME values for S1,... [s AND condition]
AND S1 IS NULL AND ...
SELECT DISTINCT A.C AS D,... FROM R
(maybe with implicit A.
and/or implicit AS D
) is rows where
FOR SOME values for A.*,... [A.C = D AND ... AND r]
(This can be less compact but looks more like the SQL.)r
with A.C,...
replaced by D,...
FOR SOME values for
the dropped columns [
r
with A.C,...
replaced by D,...
]
(X,...) IN (R)
means
r
with columns C,...
replaced by X,...
(X,...) IN R
例子:对于(人,喜欢)行的自然语言,其中[person]是Bob,Bob喜欢某个喜欢[liked]但不喜欢Ed的人:
/* (person, liked) rows where
FOR SOME value for x,
[person] likes [x]
and [x] likes [liked]
and [person] = 'Bob'
and not [x] likes 'Ed'
*/
使用简写谓词进行重写:
/* (person, liked) rows where
FOR SOME value for x,
Likes(person, x)
AND Likes(x, liked)
AND person = 'Bob'
AND NOT Likes(x, 'Ed')
*/
仅使用基本表和别名表的简写谓词进行重写:
/* (person, liked) rows where
FOR SOME values for l1.*, l2.*,
person = l1.liker AND liked = l2.liked
AND Likes(l1.liker, l1.liked)
AND Likes(l2.liker, l2.liked)
AND l1.liked = l2.liker
AND person = 'Bob'
AND NOT (l1.liked, 'Ed') IN Likes
*/
在SQL中重写:
SELECT DISTINCT l1.liker AS person, l2.liked AS liked
/* (l1.liker, l1.liked, l2.liker, l2.liked) rows where
Likes(l1.liker, l1.liked)
AND Likes(l2.liker, l2.liked)
AND l1.liked = l2.liker
AND l1.liker = 'Bob'
AND NOT (l1.liked, 'Ed') IN Likes
*/
FROM Likes l1
INNER JOIN Likes l2
ON l1.liked = l2.liker
WHERE l1.liker = 'Bob'
AND NOT (l1.liked, 'Ed') IN (SELECT * FROM Likes)
R UNION CORRESPONDING S
是行,其中 r OR s
R UNION S
是行,其中 r OR s 的列由 R 的列替换
VALUES (X,...), ...
的列为 C,...
是行,其中 C = X AND ... OR ...
示例:
/* (person) rows where
FOR SOME value for liked, Likes(person, liked)
OR person = 'Bob'
*/
SELECT liker AS person
FROM Likes
UNION
VALUES ('Bob')