PostgreSQL 04 SQL 初级

12月 27 2019

这一次系统的看了数据库, 感觉SQL语言就深深的刻在脑海里再也忘不掉了. 瞬间感觉很复杂的语句也可以写出来了, 不过还不够, 仅仅依靠上边的语句可能要写很复杂的WHERE 子句来判断条件, 所以还要继续看一下SQL初级的其他内容. 主要是集合以及聚合函数, 还有子查询的一些操作.

一个查询, 得到是一个新关系, 同时也是一个关系实例的集合. union, intersect 和 except 对应了集合运算中的并, 交和补运算.

集合运算 – 并运算
集合运算 – 交运算
集合运算 – 差运算
空值
聚集函数 – 基本聚集
聚集函数 – 分组聚集
聚集函数 – 分组聚集中的having
in 与 not in 集合成员资格测试
比较集合
空关系测试
unique 重复性测试
with 临时关系与标量子查询
数据的增删改

集合运算 – 并运算

union的使用条件是, 两个集合的列数要相等, 这样就可以进行并运算了. 并运算即使对不相关的内容也可以进行并运算:

(SELECT dept_name,salary FROM test.instructor) UNION (SELECT name, tot_cred FROM test.student);

前后两个集合都只有两列, 这个出来的结果实际上列名是第一个集合的两列, 然后把剩下的内容全部拼上去了, 所以结果显得不对. 一般并集都是要用在对关系使用并操作可以产生有意义的情况下.

比如寻找2009年秋天开课和2010年春天开课的课程组成的并集:

SELECT course_id from test.section WHERE semester = 'Fall' and year = 2009
UNION
SELECT course_id FROM test.section WHERE semester = 'Spring' and year = 2010
ORDER BY course_id;

可以发现 union 是自动去重的. 如果不去重, 就使用 UNION ALL.

集合运算 – 交运算

交运算将上边的UNION替换成 INTERSECT, 意义就编程在2009年秋天和2010年春天都开课的课程, 结果就只有一个, 就是被刚才并运算去重的CS-101课程.

SELECT course_id from test.section WHERE semester = 'Fall' and year = 2009
INTERSECT
SELECT course_id FROM test.section WHERE semester = 'Spring' and year = 2010 ORDER BY course_id;

同样, INTERSECT ALL就会保留重复, 不过保留的不是所有的重复, 而是多个查询中, 每一个查询的结果中某个元组的重复数量, 等于各个子查询中这个元组重复的最小次数. 举个例子, 比如第一个查询中 CS-101 重复3次, 第二个查询中 CS-101重复4次, 用INTERSECT ALL 连接之后, 结果是3个.

集合运算 – 差运算

差运算比较特殊, 如果说前边两个集合运算对顺序没有什么特殊要求, 差运算就不同的, 差运算表示从第一个集合中去掉所有存在于第二个集合中的元素, 也就是第一个集合减第二个集合的运算.

这个操作会自动去除所有的重复, 比如第一个集合中 CS-101 有两个重复, 第二个集合中 CS-101有三个重复, 差运算的结果不会包含CS-101

如果想保留重复, 就使用 EXCEPT ALL. 此时结果中某个元组的重复数量等于第一个集合中的重复数量减第二个集合的重复数量, 大于0则就是这个数字, 小于等于0则结果中不存在这个元组.

空值

空值是个比较讨厌的东西, SQL规定任何和空值有关的运算都视为unknown, 这可以认为是除了true 和 false 之外的第三个布尔值. 既然是布尔值, 可以进行逻辑运算:

true and unknown = unknown, false and unknown = false, unknown and unknown = unknown
true or unknown = true, false or unknown = unknown, unknown or unknown = unknown
not unknown = unknown

如果 WHERE 后边的表达式的结果是unknown 或者 false, 则结果集中不会包含这个元组. 从这个角度来看, unknown还是比较接近false一些.

可以使用特殊的谓词 is null 来测试是不是空值.

聚集函数 – 基本聚集

SQL标准规定的聚集函数有5个:

avg
min
max
sum
count

聚集函数实际上要分两块看, 一个是基本聚集, 一个是分组聚集.

基本聚集得到的结果都是单个元组, 直接就对一个集合进行聚集操作, 所以得到的结果都是单个元组.

一般仅仅会选择单个要聚集的列, 因为其他列不能聚合上去.

比如计算平均工资:

SELECT avg(salary) AS avg_salary FROM test.instructor;

关于聚集的列一般需要重命名, 已经在AS中说过了. 这里要注意的就是, 很多聚集函数在使用的时候是不是要去重, 影响最后的结果.

此外要明白函数操作的结果, 实际上内部的操作顺序是先选择出不带函数的结果, 然后对这个结果集合应用函数. 例子中实际上会先选出一个包含salary的关系, 然后对这个关系中的集合执行avg函数.

如果要去重, 就要在函数的参数里加上DISTINCT, 然而这个时候就要考虑实际的意义, 比如:

SELECT avg(DISTINCT salary) FROM test.instructor;

如果教师中有两个教师有重复的工资, 这个结果就不会得到正确的平均工资, 因为相同工资的两个教师, 实际上已经被去重去掉了, 这就会导致错误的结果.

而count就经常需要去重. 此外如果要计算一个关系中元组的个数, 可以直接使用SELECT COUNT(*) FROM course. 但是不允许写成 COUNT(distinct *).

max和min可以使用DISTINCT, 但使不使用都一样.

聚集函数 – 分组聚集

分组这个就得好好理解了. 分组的语句是GROUP BY A, A是属性名. 表示先将所有的结果分组, 再进行操作.

比如想要统计每个系的老师的平均工资, 显然不能直接将avg作用在全部老师上, 而是先要分组, 将所有讲师分成一个又一个按照系名分类的集合, 再对每个集合进行avg操作, 将结果再收集成一个集合. 写成SQL就是:

SELECT dept_name, avg(DISTINCT salary) FROM test.instructor GROUP BY dept_name;

这句还是要好好理解的, SELECT之后有两个属性, 说明是两列, 其中有一个是聚合函数, 另外一个是普通列名. 这里一定要注意, SELECT之后跟的属性如果不是聚集函数, 则一定要出现在GROUP BY之后, 或者SELECT之后仅仅跟聚集函数也可以, 但是结果就看不出来分组的对应关系.

为何要做如此规定, 是为了让查询结果能够反映分组的关系, 如果有一个属性不再GROUP BY中出现, 则这个属性就不知道如何处理:

SELECT dept_name, id, avg(DISTINCT salary) FROM test.instructor GROUP BY dept_name;

这一句就会报错: ERROR: column "instructor.id" must appear in the GROUP BY clause or be used in an aggregate function. 这个提示可谓非常清晰了.

聚集函数 – 分组聚集中的having

有的时候对分组限定条件可能比最后的结果限定条件更加方便. 接着上边的例子, 查询平均老师工资大于等于80000元的系和平均工资, 可以写成这样:

SELECT dept_name, avg_salary
FROM (SELECT dept_name, avg(DISTINCT salary) as avg_salary FROM test.instructor GROUP BY dept_name) as foo
WHERE avg_salary > 80000;

查出的avg_salary无法在同一个语句里再使用WHERE avg_salary, 因为WHERE只能控制FROM中的条件, 在形成分组前起作用, 无法在形成分组后起作用. 所有就用了这种子查询.

如果我们直接可以对分组后的结果使用条件限定的话, 就可以更方便的查出来. 这就需要在分组后可以起作用的条件, 这就是having 语句, 上边的例子可以修改如下:

SELECT dept_name, avg(DISTINCT salary) as avg_salary FROM test.instructor GROUP BY dept_name having avg(salary) > 80000;

这里也是要弄清楚实际执行的顺序:

先当做没有任何聚合, 会按照 SELECT detp_name, salary FROM test.instructor 进行查询哦, 将结果叫做结果A
如果有WHERE, 会应用到结果A的每一个元组上, 得到结果B
结果B按照GROUP BY进行分组, 得到多个分组, 这些分组一起组成结果C, 结果C只存在于运算过程中
如果有having 子句, 按照having 子句对结果C中的每个分组进行判断, 去掉所有不满足条件的分组, 剩下的分组一起组成结果D, 结果D也仅仅在过程中; 如果没有having子句, 则就是结果C.
上一步得到的结果D或者原来的结果C, 在其中的每个分组上应用聚合函数, 得到和分组数量一致的元组, 将这些元组拼合成最终的聚合后的关系进行输出.

所以也可以看出, 基本聚集其实就是分组的一个特殊情况, 即所有的元组都被认为在同一个组里.

特别要注意的是,所有的聚集函数除了count(*)之外, 都会忽略空值. 如果忽略空值导致函数参数是一个空集, 则count之外聚合函数都会返回一个空集, 而count一个空集会返回0.

in 与 not in 集合成员资格测试

这个就很像Python动态语言中的in 和 not in 关键字, 用于测试一个关系中的每条元组是否在另一个关系中, 常见就是和子查询搭配使用.

比如找出所有计算机系的学生, 再找出所有学分大于40的学生, 选出前者也存在于后者中的学生(注意这个选择和同时选出计算机系且学分大于40的学生是一样的, 这也说明SQL语言有冗余, 可以以多种方式完成同样的效果):

SELECT *
FROM test.student
WHERE dept_name = 'Comp. Sci.'
  and student.tot_cred in (SELECT student.tot_cred FROM test.student WHERE tot_cred > 40);

可以看到in 用来测试每一个元组中的tot_cred属性的取值, 是否在另外一个也是由tot_cred属性组成的集合中. 如果满足条件, 就选出对应的元组.

按照上边的分析, 这个也可以写成 SELECT * FROM test.student WHERE dept_name = 'Comp. Sci.' and tot_cred > 40;, 这是因为二者的逻辑本质上是一样的.

这里测试了单个属性是否属于某个单属性的关系, 测试多属性是否属于一个多属性关系也是可以的, 只要被测试的属性和集合成员的属性匹配即可:

SELECT count(distinct ID)
FROM test.takes
WHERE (course_id, sec_id) in (Select course_id, sec_id from test.teaches WHERE teaches.ID = '10101');

这个查询的意思是先找到10101老师教的所有课程, 然后从学生中找出所有选了这个老师教的课程的学生, 之后统计学生的数量.

in 和 not in 之后除了跟一个查询之外, 还可以直接给出枚举值来进行判断, 比如:

SELECT  * FROM test.instructor WHERE dept_name in ('Biology', 'Comp. Sci.')

some all 比较集合

这里的比较集合并不是指两个集合以整体进行比较, 而是要表达这样一种关系, 即一个集合中的某一个元组比另外一个集合中的某个元组会怎么样.

常用的就是某一个元组大于(或者其他逻辑)另外一个元组中所有的数据对象, 某一个元组大于(或者其他逻辑)另外一个元组中某一个数据对象这两种情况.

对于这两个情况, 分别使用了 all 和 some 两个关键字, 在其后加上一个关系, 就表示上边的两个逻辑. 比如查找所有比生物系内至少一个老师工资高的所有老师, 在之前使用了as 进行同一个关系的两次查询, 然后本质是笛卡尔积, 然后选出第一个关系的工资大于第二个关系的工资的结果.

现在可以简化一下, 使用大于 some 来表示高于其中至少一个就可以:

SELECT name, salary
FROM test.instructor
WHERE salary > some (Select salary from test.instructor where dept_name = 'Biology');

生物系中只有一个老师, 工资是72000, 所以查到的结果是大于72000的所有老师.

同样, 查询高于金融系中所有老师的工资的老师:

SELECT name, salary
FROM test.instructor
WHERE salary > all (Select salary from test.instructor where dept_name = 'Finance');

金融系中有两个老师, 工资分别是80000和90000, 所以这个结果实际上就是所有工资大于90000的老师.

SQL也允许 < all, <= all, >= all, = all 和 <> all, some也一样. 注意 <> all 等价于 not in. 而 = all 不等价于in.

这个比较也可以比较聚合函数后的结果, 并不是一定要比较原始属性:

SELECT dept_name
FROM test.instructor
GROUP BY dept_name
having avg(salary) >= all (SELECT avg(salary) FROM test.instructor GROUP BY dept_name);

这个是查询平均工资高于其他所有系的平均工资的系.

到这里我发现SQL想要用好, 其实是要自己建立这些关系之间的联系, 然后将这些联系转换成SQL语句.

exists 空关系测试

这个顾名思义, 加在一个关系前边, 返回一个布尔结果, 如果关系中存在元组, 就返回true, 否则返回false. 通常使用在WHERE HAVING之类条件语句中:

SELECT *
FROM test.student
WHERE exists(SELECT * FROM test.student where tot_cred > 20);

这个语句其实就是选出全部的学生, 测试条件是只要有学生的学分大于20就选出全部学生. 如果将20改成3000, 则一个结果都没有了. 因为exits()的返回是false.

这里还一个小概念, 叫做相关子查询, 也就是子查询中使用了上一层查询中AS 定义的别名.

unique 重复性测试

这个是指测试一个关系中是否包含重复的元组, 如果没有重复返回true, 有重复返回false. 使用这个测试的时候最好在要测试的关系中加上标识唯一性的字段.

比如查询2009年最多开设一次的课程. 课程在course中, 实际开设的每次课程的信息在section中, 可以先从section中选出2009年开设的课程, 然后将其course_id与course表进行对比.
选出其中相等的部分, 然后看这个结果是不是唯一的. 如果一门课在2009年内开设多次, 则结果就不唯一(选出的section有两个):

SELECT course_id
FROM test.course as T
WHERE unique (Select course_id FROM test.section as D WHERE t.course_id = d.course_id and year = 2009);

这句还得好好理解一下, 针对每一个 course中的 course_id ,去判断section表中与其对应的section是否只有1个.

在PgSQL上跑这句的时候, 发现 UNIQUE predicate is not yet implemented, 所以通过判断数量了:

SELECT course_id
FROM test.course as T
WHERE  (Select count(*) FROM test.section as D WHERE t.course_id = d.course_id and year = 2009) =1;

其实很上边的本质一样, 通过course的id到section中查2009年内对应的section, 查到结果只有1个的话就说明仅在2009年内开了一次课, 似乎比unique还容易理解一些.

with 临时关系与标量子查询

with子句提供临时的关系, 只在with所在的语句中生效. 对于使用一个简单的标量结果再进一步查询的时候, 用with的语义会清晰很多, 比如找出最大预算的系:

WITH max_budget(value) as (SELECT max(budget) FROM test.department)
SELECT budget
FROM test.department,
     max_budget
WHERE department.budget = max_budget.value;

注意with定义了一个新关系, 关系的名称叫做max_budget, 属性名称是value. SELECT max(budget) FROM test.department 实际查出了只有一列一行的结果, 这个结果通过with被命名为max_budget, 属性是value, value的值就是后边选出来的最大语速的值.

然后通过先取笛卡尔积, 再进行判断的过程, 得到最后结果.

如果不使用with, 考虑可以直接判断每个部门的预算数与最大值是否相等. 这个时候可以用到标量子查询, 也就是如果一个查询的结果只有一行一列, 那么就可以将这个查询当成一个标量来使用:

SELECT budget
FROM test.department
WHERE department.budget = (SELECT max(budget) FROM test.department);

可以看到, (SELECT max(budget) FROM test.department)的结果直接被当成一个标量来和budget数据进行比较. 这就是标量子查询, 有了这个东西就会简化很多操作.

数据的增删改

查询的主要部分基本都学完了. 只要记住这些类似语法糖的各种关键字, 就可以方便的写出来了. 感觉最难的还是要把想查询的结果转换成SQL中如何操作关系的语句.

先是删除 delete, 删除只能作用于一个关系, 删除的最小单位是元组, 不能删除元组上的属性.

delete 也可以加WHERE, 只删除满足条件的元组, 既然可以用WHERE, 就可以写任意复杂的逻辑.

然后是添加 insert into.

这个insert into可以直接插入常量, into后边接关系名称, 然后加上values(), 其中按照属性顺序写对应的值就可以:

INSERT INTO test.department values ('NewDept', 'Watson', 123000);

可以在关系的地方指定属性顺序, 这样就不用按照默认的顺序:

INSERT INTO test.department(building, budget,dept_name) values ( 'Watson', 123000,'NewDept');

不过最牛逼是在SELECT的基础上把结果插入到数据库中, 这要求选出来的元组的属性是能够匹配被插入的关系的.

书上的例子不错, 比如让所有学分超过144的Music系的学生变成Music系的讲师, 初始工资是18000:

INSERT INTO test.instructor(id, name, dept_name, salary)
    SELECT id, name, dept_name, 18000 FROM test.student WHERE dept_name = 'Music' and tot_cred > 144;

这里小技巧, 插常量直接就选常量, 这样那一列全都是常量, 而名称可以用as来指定:

SELECT id, name, dept_name, 18000 as salary
FROM test.student;

有了这个技巧可以任意在选择结果上为每一个元组增加一个属性.

如果插入的时候给出null, 假如符合完整性约束, 是可以插入null的. 也可以只插入部分元组的值, 没插入的会被当做null, 例子如下:

INSERT INTO test.department(building,dept_name) values ( 'Watson','NewDep2t');

最后是改 update, update 搭配set 和 WHERE 使用, 可以在满足条件的情况下更新元组的属性.

这里要关注的是带case结构的更新. 即可以同时判断多个条件. 结构是:

case
    when 条件1 then 结果1
    when 条件2 then 结果2
    when 条件3 then 结果3
    else 结果4
end

这个就是和其他编程语言的swtich case 语句结构很类似, else相当于default. 每个case返回一个具体的值交给set来更新

比如给所有的老师加工资, 工资少于80000的加10%, 工资在80000-100000的加 8%, 超过100000的加 5%, 语句如下:

UPDATE test.instructor set salary = case
    WHEN salary<80000 THEN salary*1.1
    WHEN salary<=100000 and salary>=80000 THEN salary*1.08
    ELSE salary * 1.05
END;

这样逻辑不容易出错.

基础的SQL操作到这里就差不多了, 接下来的所谓中级SQL就是连表操作, 视图, 事务和完整性约束的详细内容了.

发表于PostgeSQL 数据库