6.SQL-结构化查询语言（二）

聚合函数

函数	功能
COUNT（[ DISTINCT ] A ）	A 列中（唯一）值的数量
SUM（[ DISTINCT ] A ）	A 列中所有（唯一）值的总和
AVG（[ DISTINCT ] A）	A 列中所有（唯一）值的平均值
MAX（A）	A 列中的最大值
MIN（A）	A 列中的最小值

• 找出 Perryridge 支行的平均 balance：
  • 
• 找出所有账户关系对的数量
  • 
  • * 表示所有属性
  • 等同于
    • 
    • 因为 account-number 是一个键

集合运算

• 集合运算 union、intersect 和 except 表示并集、交集和差集
• 以上每次操作自动消除重复自动去重；要保留所有重复项，我们可以使用 union all、intersect all 和 except all
• 找出所有有贷款的客户或者存款的客户，或者两者都有的客户：
  • 
• 找出所有既有贷款又有存款的客户：
  • 
• 找出有存款但没有贷款的客户：
  • 
• 在 Oracle 中，minus 用来代替 except

嵌套查询

• 每个 SQL 语句都会返回
  • Relations 记录集合，或
  • Null，或
  • 一个数值
• 可在 where 语句中运用子查询
  • 
• 子查询返回的类型须与不等式的另一边相同
• 例如：IN
  • 找出在银行既有贷款又有存款的客户
    • 
• 例如：NOT IN
  • 找出在银行有贷款但没有存款的客户
    • 
• SOME 语句
  • 找出所有资产大于坐落于布鲁克林任意一个银行资产的银行
    • 
  • SOME 的语义
    • 
• ALL 语句
  • 找出所有资产大于布鲁克林所有银行资产的银行
    • 
  • ALL 的语义
    • 
• EXISTS 语句：判断列表是否为空
  • 
  • 找出所有同时拥有贷款和存款的客户
    • 
  • 找出所有有存款但没有贷款的客户
    • 
• 去重 unique
  • 找出在 Perryridge branch 只有一个账户的所有客户
    • 
    • 
• 去独
  • 找出所有在 Perryridge branch 至少有两个账户的客户
    • 

除法

• 找出在所有 Brooklyn 分行都有账号的客户
  • 

  ---

  • 

  ---

  • 

  ---

分组 Group By

• 找出各个分行拥有的账号数目
  • 

  ---

  • 
• SELECT 指定的字段要么就包含在 Group By 语句的后面，作为分组的依据；要么就要被包含在聚合函数中：
  
  • 

  ---

  • 
• 找出每个支行中储蓄用户的人数
  • 
  • 因为同个客户可能有多个账户。先 JOIN 两个 relation，找出顾客姓名、账户和 branch 的结合
  • 这里的 DISTINCT 是必须的，因为有些 customers 可能在同个 branch 有多个储蓄账号
  • 
  • 

HAVING 语句

• 找出平均账户余额超过 700 块的分行
  • 
• Having 后接的语句是应用到每个分组的，而不是全部记录

衍生的 relation

• 找出平均账户余额最大的分行
  • 

空值 NULL value

• 值未知
  • 加入该记录的时候没有对属性进行赋值。譬如：尚未为新员工分配主管
    • 
• 记录不具备该属性，不适用
  • 

  ---

• 
• 传统的逻辑运算
  • 
• 加入空值后的逻辑运算
  • 
• 例子：
  • 

数据定义语言：DDL（进阶概念）

域约束 Domain Constraints

• 规定了 hourly-wage 是一个 5 位数的十进制数，其中小数点后有 2 位，该域有一个约束，确保每小时工资大于 4.00

SQL 中的主键和候选键

• 除了主键外，用 UNIQUE 指明其他的候选键
• 伴随着约束 -- 用 unique 声明的主键组合，在实际存储中也必须是唯一的，即不同记录不能有相同值
  • 
  • 滥用的话，可能会导致现实中的一些记录不能被成功存储。

SQL 中的外键

• Enrolled 表中的记录，在加入表时，其相应的 sid 必须存在于 students 表格中，不然其插入会被拒绝
  • 

引用完整性

如果一个学生元组被删除了，应该怎么办

• ( cascading deletion 级联删除):把其相应在 enrolled 纪录也删除
• 拒绝该操作，不让删除被 Enrolled 引用的学生元组
• 设置默认值，被删除学生在 Enrolled 的相关记录 sid 变成一个默认值
• 变成 null 值
• SQL92（ SQL 的一个国际标准）包括所有 4 个删除和更新选项
  • 
  • 
    • 
    • 加了关键词“ on delete cascade ”以后，删除 customer 里的记录，会导致其相应在 depositor 的记录也被删除
      • 

参与约束

• 全部参与
  • 
  • 图中表明了 departments 对 manages 全部参与，所以实际存储时其外键 EID 是不能为空的
  • 

弱实体

• 
• 如图，弱实体依赖于 owner entity 进行索引
• 根据关系代数，弱实体与其依赖的 relationship 一起映射为一个 relation
  • 当删除 owner entity 时，所有拥有的弱实体也必须被删除
  • 

记录删除

• 删除 Perryridge 银行的所有记录
  • 
• 删除坐落在 Needham 的每个银行的所有客户
  • 

记录插入

• 按默认属性顺序（创建 relation 时）插入
  • 
• 自定义属性顺序插入
  • 
• 在账户中添加一个新的元组，并将余额设置为 null：
  • 
• 例子：
  • 给所有在 Perryridge 的贷款客户创建 200 元的储蓄账户，并且把贷款账号作为该储蓄账户的账号
    • 

记录更新

将所有余额超过 10,000 美元的账户增加 6% ，所有其他账户收到 5%

• 

---

• 

Views

提供的一种机制，从 relation 中创建 view ，可以实现隐藏部分 relation 的属性

• 
• 例子：隐藏 acmount 列：
  • 
  • 授权一个用户可以访问 branch-loan，但不可以访问 loan，相当于不让它知道 amount 列
• 可以使用 Drop view branch-loan 语句来清除views

SQL 的两个组件

Asserting 断言

• 一个 Asserting 是时时刻刻都要遵循的约束
  • 
• 与一般约束的区别
  • constraint 与单个 relation 关联，并在该特定关系有更新时进行检查
  • 一个断言可能与多个关系相连，并且每次在任何地方有更新的时候都会进行检查
  • 即，Asserting 语句可以涉及多个 relations
• 符合 SQL 语法的逻辑语句都可以用作其中的 predicate
• 例子：每个分行的总贷款额必须小于其总存款额
  • 
  • 这里的 assertion 用到了多个 relations，所以是没办法单单写成在 loan or account relation 里的一个约束。

Triggle 触发器

• Triggle：当数据产生特定修改后，触发器的语句就会被执行
• 要设计触发器，我们必须：
  • 指定触发条件
  • 指定触发后执行的语句
• 例子：如果存款账户余额变为负数，那么把该存款账户变成贷款账户
  • 

  ---

  • 

  ---

  •