第 4 章: SQL

1. 第4章: SQL • 基本结构 • 集合运算 • 合计函数 • 空值 • 嵌套子查询 • 导出关系 • 视图 • 数据库更新 • 连接关系 • DDL • Embedded SQL, ODBC及JDBC

2. 例子中所用模式

3. 基本结构 • SQL基于集合与关系运算并作了某些修改和增强 • 典型的 SQL 查询形如:select A1, A2, ..., Anfromr1, r2, ..., rmwhere P • Ai是属性 • ri是表 • P是谓词 • 等价于关系代数表达式 A1, A2, ..., An(P (r1 x r2 x ... x rm)) • SQL 查询的结果是一个关系.

4. select 子句 • select子句对应于关系代数的投影运算. 用于列出想要的查询结果中的属性. • 从loan关系求所有分行的名称select branch-namefrom loan • 在 “纯” 关系代数语法中, 对应查询是: branch-name(loan) • select 子句中用 * 表示 “所有属性” select *from loan • 注意: SQL 不允许在名字中用 ‘-’ 字符, 因此在实际系统中当用branch_name而非branch-name. 本书用 ‘-’ 是因为它好看一点! • 注意: SQL 是不分大小写的.

5. select 子句 (续) • SQL 允许关系与查询结果中出现重复元组. • 可用关键字distinct 强制删除重复元组.从loan 关系求所有分行名并删除重复元组 select distinct branch-namefrom loan • 关键字all 声明保留重复元组 select allbranch-namefrom loan • 这是缺省情形, 可省略

6. select 子句 (续) • select子句可包含使用+, –, , / 运算以及常量和属性的算术表达式. • 查询 selectloan-number, branch-name, amount  100from loan 将loan 关系的amount 属性乘以100.

7. where 子句 • where 子句对应于关系代数的选择谓词. 其中的谓词涉及from子句中出现的关系的属性. • 求Perryridge分行发出的所有额度超过$1200 的贷款的贷款号.select loan-numberfrom loanwhere branch-name = ‘Perryridge’ and amount > 1200 • 比较结果可用逻辑连接词and, or, not 组合在一起. • 可对算术表达式的结果进行比较.

8. where 子句(续) • SQL的between运算符可以简化介于两个值之间的比较. • 求额度介于$90,000 与 $100,000之间的贷款的贷款号select loan-numberfrom loanwhere amountbetween 90000 and 100000

9. from 子句 • from 子句对应于关系代数的笛卡尔积运算. 它列出了查询中要扫描的关系. • 求笛卡尔积borrower x loanselect from borrower, loan • 求所有在Perryridge分行有贷款的客户的姓名, 贷款号和贷款数量.select customer-name, borrower.loan-number, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number andbranch-name = ‘Perryridge’

10. 重命名操作 • SQL 允许对关系和属性用as子句重命名:old-name as new-name • 求所有客户的姓名, 贷款号和贷款数量; 将loan-number列改名为loan-id.select customer-name, borrower.loan-number as loan-id, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number

11. 元组变量 • 元组变量是在from子句中用as 子句定义的. • 求所有有贷款的客户的姓名和贷款号. select customer-name, T.loan-number, S.amountfrom borrower as T, loan as Swhere T.loan-number = S.loan-number • 求比位于Brooklyn的某分行资产多的分行的名字.select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets and S.branch-city = ‘Brooklyn’

12. 串操作 • SQL 包含串匹配运算用于比较字符串. 模式用两个特殊字符描述: • 百分号 (%). % 字符可与任何字符串匹配. • 下划线 (_). _ 字符与任一字符匹配. • 求所有街道名称包含子串 “Main”的客户的姓名. select customer-namefrom customerwherecustomer-street like ‘%Main%’ • 若要匹配串“Main%”则需用 like‘Main\%’escape ‘\’ • SQL支持许多串操作 • 连接 ( “||”) • 大小写转换 • 求串长度, 取子串, etc.

13. 元组的显示顺序 • 按字母顺序列出在Perryridge分行有贷款的客户的姓名 select distinct customer-namefrom borrower, loanwhere borrower loan-number - loan.loan-number and branch-name = ‘Perryridge’order by customer-name • 可对每个排序属性使用desc指定降序,asc指定升序; 升序是缺省顺序. • E.g. order bycustomer-namedesc

14. 重复元组 • 对于有重复元组的关系, SQL 定义了运算结果中重复元组的拷贝数如何确定. • 一些关系代数运算的多重集合版本 – 给定关系r1和r2: 1. 若在r1 中元组t1 有c1份拷贝, 且t1满足选择条件,, 则在 (r1)中t1 有c1份拷贝. 2. 对r1 中元组t1 的每一拷贝, 在A(r1)中有元组A(t1)的一份拷贝, 这里 A(t1)表示元组t1 的投影. 3. 若在r1 中元组t1 有c1份拷贝, 在r2 中元组t2 有c2份拷贝,则在r1 x r2 中元组t1. t2 有c1 x c2份拷贝

15. 重复元组 (续) • 例如: 假设有多重集关系r1 (A, B) 和r2 (C) 如下: r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)} • 则B(r1) 为 {(a), (a)}, B(r1) x r2为 {(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)} • SQL重复元组的语义: select A1,, A2, ..., Anfrom r1, r2, ..., rmwhere P 等价于下列表达式的多重集版本:  A1,, A2, ..., An(P (r1 x r2 x ... x rm))

16. 集合运算 • 关系的集合操作union, intersect, except 对应于关系代数运算 • 以上操作自动删除重复元组; 为了保留所有重复元组应使用对应的多重集版本union all, intersect all, except all.假如一个元组在r 中发生m 次,在s 中发生n 次, 则: • 在r union all s 中发生 m + n 次 • 在rintersect all s 中发生 min(m,n) 次 • 在rexcept all s 中发生max(0, m – n) 次

17. 集合操作 • 求有贷款或账户的客户 (selectcustomer-name from depositor)union (selectcustomer-name from borrower) • 求既有贷款又有账户的客户 (selectcustomer-name from depositor)intersect (selectcustomer-name from borrower) • 求只有账户没有贷款的客户 (selectcustomer-name from depositor)except (selectcustomer-name from borrower)

18. 合计函数 • 以下函数对关系中某一列值的多重集进行计算并返回单个值 avg: 平均值min: 最小值max: 最大值sum: 总和count: 值的个数

19. 合计函数 (续) • 求Perryridge分行的平均账户余额. select avg (balance)from accountwhere branch-name = ‘Perryridge’ • 求customer 关系中的元组个数 select count (*)from customer • 求银行存款人数. select count (distinct customer-name)from depositor

20. 合计函数 – Group By • 求每个分行的存款人数. select branch-name, count (distinctcustomer-name)from depositor, accountwhere depositor.account-number = account.account-numbergroup by branch-name 注意: select 子句中处于合计函数之外的属性必须在group by属性列表中出现！

21. 合计函数 – Having 子句 • 求平均账户余额超过$1,200的所有分行名及平均余额. select branch-name, avg (balance)from accountgroup by branch-namehaving avg (balance) > 1200 注意: having子句中的谓词是在分组形成之后起作用的, 而where子句中的谓词是在分组形成之前起作用的

22. 空值 • 元组在某些属性上可以有空值, 用null 表示 • null表达未知的值或不存在的值. • 谓词is null可用来检查空值. • E.g. 求loan 关系中amount 为空值的贷款号. select loan-numberfrom loanwhere amount is null • 任何涉及空值的算术表达式的结果也为null • E.g. 5 + null 返回 null • 合计函数简单地忽略空值 • 稍候详述

23. 空值与三值逻辑 • 与null的比较返回unknown • E.g. 5 < null or null <> null or null = null • 三值逻辑用到真值unknown: • OR: (unknownortrue) = true, (unknownorfalse) = unknown (unknown or unknown) = unknown • AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown • NOT: (not unknown) = unknown • 如果谓词P为unknown, 则 “P is unknown” 为真 • 若 where 子句的谓词计算到unknown 则视为false

24. 空值与合计 • 求贷款总额 select sum (amount)from loan • 上述语句忽略空值 • 如果loan 关系中没有非空 amount 则结果为null • 除count(*) 之外的所有合计操作都忽略在合计属性上为空值的元组.

25. 嵌套子查询 • SQL 提供嵌套子查询的机制. • 子查询是嵌在另一个查询内部的select-from-where表达式. • 子查询的通常用法是执行集合成员检测, 集合比较, 以及集合基数.

26. 查询例 • 求既有账户又有贷款的客户. select distinct customer-namefrom borrowerwhere customer-name in (select customer-namefromdepositor) • 求有贷款但没有账户的客户 select distinct customer-namefrom borrowerwhere customer-name not in (select customer-namefrom depositor)

27. 查询例 • 求在Perryridge分行既有账户又有贷款的客户 select distinctcustomer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number andbranch-name = “Perryridge” and(branch-name, customer-name) in (select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) • 注意: 以上查询可以写成更简单的形式. 这里的写法是为了说明 SQL特色. (Schema used in this example)

28. 集合比较 • 求比位于Brooklyn的某个分行资产多的所有分行. select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets andS.branch-city = ‘Brooklyn’ • 使用 > some子句的同一查询 select branch-namefrom branchwhere assets > some (select assetsfrom branchwhere branch-city = ‘Brooklyn’)

29. 0 5 6 Some 子句的定义 • F <comp> some r t r s.t. (F <comp> t)其中 <comp> 可以是:  (5< some ) = true (read: 5 < some tuple in the relation) 0 ) = false (5< some 5 0 ) = true (5 = some 5 0 (5 some ) = true (since 0  5) 5 (= some)  in 然而, ( some) not in

30. 0 5 6 all 子句的定义 • F <comp> all r t r (F <comp> t) (5< all ) = false 6 ) = true (5< all 10 4 ) = false (5 = all 5 4 (5 all ) = true (因为 5  4 and 5  6) 6 (all)  not in 然而, (= all)  in

31. 查询例 • 求比位于Brooklyn的所有分行资产都多的分行名. select branch-namefrom branchwhere assets > all (select assetsfrom branchwhere branch-city = ‘Brooklyn’)

32. 测试空关系 • 如果子查询非空, 则exists谓词返回true. • exists r  r  Ø • not exists r  r = Ø

33. 查询例 • 求在位于Brooklyn的所有分行都开了账户的客户. select distinct S.customer-namefrom depositor as Swhere not exists ( (select branch-namefrom branchwhere branch-city = ‘Brooklyn’)except (select R.branch-namefrom depositor as T, account as Rwhere T.account-number = R.account-number andS.customer-name = T.customer-name)) (Schema used in this example) • 注意:X – Y = Ø  X Y • 注意:这个查询不能用= all及其变种

34. 测试重复元组 • unique谓词测试子查询的结果中是否有重复元组. • 求在Perryridge分行最多只开了一个账户的客户. select T.customer-name from depositor as T where unique ( select R.customer-namefrom account, depositor as Rwhere T.customer-name = R.customer-name andR.account-number = account.account-number andaccount.branch-name = ‘Perryridge’) (Schema used in this example)

35. 查询例 • 求在Perryridge分行至少开了两个账户的客户. select distinct T.customer-name from depositor T where not unique ( select R.customer-name from account, depositor as R where T.customer-name = R.customer-name and R.account-number = account.account-number and account.branch-name = ‘Perryridge’) (Schema used in this example)

36. 视图 • 提供对某些用户隐藏某些数据的机制. 创建视图命令:create view v as<query expression> 其中: • <query expression> 是任何合法查询表达式 • v 是视图名

37. 查询例 • 包含分行及其客户的视图 create view all-customer as(select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) union(select branch-name, customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number) • 求Perryridge 分行的所有客户 select customer-namefrom all-customerwhere branch-name = ‘Perryridge’

38. 导出关系 • 求平均账户余额超过$1200的分行名及其平均账户余额. select branch-name, avg-balancefrom (select branch-name, avg (balance)from accountgroup by branch-name)as result (branch-name, avg-balance)where avg-balance > 1200 注意: 这里我们不必使用having 子句, 因为我们在from子句中计算了临时关系result, result的属性可直接用于where 子句.

39. With 子句 • With 子句是我们可以定义局部于查询的视图, 而非全局视图 • 求具有最大余额的账户 withmax-balance(value) asselect max (balance)fromaccountselectaccount-numberfromaccount, max-balancewhereaccount.balance = max-balance.value

40. 使用With 子句的复杂查询 • 求账户余额总和大于全体分行的平均账户余额总和的分行withbranch-total (branch-name, value) asselectbranch-name, sum (balance)fromaccountgroupbybranch-namewithbranch-total-avg(value) asselectavg (value)frombranch-totalselect branch-namefrombranch-total, branch-total-avgwherebranch-total.value >= branch-total-avg.value

41. 数据库更新 – 删除 • 删除Perryridge分行的所有账户记录 delete from accountwhere branch-name = ‘Perryridge’ • 删除位于Needham的每个分行的所有账户. delete from accountwhere branch-name in (select branch-namefrom branchwhere branch-city = ‘Needham’)delete from depositorwhere account-number in (select account-numberfrom branch, accountwhere branch-city = ‘Needham’and branch.branch-name = account.branch-name) (Schema used in this example)

42. 查询例 • 删除余额低于平均值的账户. delete from accountwhere balance < (select avg (balance)from account) • 问题: 当我们从account 删除元组时,平均余额也发生了变化 • SQL的解决方案: 1. 首先, 计算平均余额并找出所有应删除元组 2. 其次, 将上述所有元组删除 (并不重新计算avg或重测试元组)

43. 数据库更新 – 插入 • 向account 增加一个新元组 insert into accountvalues (‘A-9732’, ‘Perryridge’,1200)或等价地insert into account (branch-name, balance, account-number)values (‘Perryridge’, 1200, ‘A-9732’) • 向account 增加一个新元组, 余额置为null insert into accountvalues (‘A-777’,‘Perryridge’, null)

44. 数据库更新 – 插入 • 向所有Perryridge分行的贷款客户提供一个存有$200的储蓄账户作为礼物. 用贷款号作为新储蓄账户的账号 insert into accountselect loan-number, branch-name, 200from loanwhere branch-name = ‘Perryridge’insert into depositorselect customer-name, loan-numberfrom loan, borrowerwhere branch-name = ‘Perryridge’ and loan.account-number = borrower.account-number • 在其结果插入关系之前, select-from-where 语句应完全求出. 否则类似下面的查询会导致问题insert intotable1 select * fromtable1

45. 数据库更新 – 修改 • 余额超过$10,000的账户增加6%, 其他账户增加5%. • 用两条update 语句: update accountset balance = balance  1.06where balance > 10000 update accountset balance = balance  1.05where balance  10000 • 次序很重要 • 更好的做法是用case 语句

46. Case 语句 • 余额超过$10,000的账户增加6%, 其他账户增加5%. updateaccountsetbalance = casewhenbalance <= 10000 thenbalance *1.05elsebalance * 1.06end

47. 视图的更新 • 创建隐藏了amount 属性的贷款数据的视图 create view branch-loan as select branch-name, loan-numberfrom loan • 插入新元组 insert into branch-loanvalues (‘Perryridge’, ‘L-307’) 这个插入可以表示为向loan 关系插入元组 (‘L-307’, ‘Perryridge’, null) • 对更复杂的视图作更新往往难以翻译, 因而通常不允许. • 多数SQL 实现只允许对定义在单个关系上的简单视图(不含合计)进行更新

48. Transactions • A transaction is a sequence of queries and update statements executed as a single unit • Transactions are started implicitly and terminated by one of • commit work: makes all updates of the transaction permanent in the database • rollback work: undoes all updates performed by the transaction. • Motivating example • Transfer of money from one account to another involves two steps: • deduct from one account and credit to another • If one steps succeeds and the other fails, database is in an inconsistent state • Therefore, either both steps should succeed or neither should • If any step of a transaction fails, all work done by the transaction can be undone by rollback work. • Rollback of incomplete transactions is done automatically, in case of system failures

49. Transactions (Cont.) • In most database systems, each SQL statement that executes successfully is automatically committed. • Each transaction would then consist of only a single statement • Automatic commit can usually be turned off, allowing multi-statement transactions, but how to do so depends on the database system • Another option in SQL:1999: enclose statements withinbegin atomic … end

50. 连接关系 • 附加的连接操作常用于from子句中作为子查询表达式 • 连接条件 – 定义两个关系的元组如何匹配, 以及连接结果中有哪些属性. • 连接类型 – 定义如何处理没有匹配的元组. 连接类型 连接条件 inner join left outer join right outer join full outer join natural on <predicate> using (A1, A2, ..., An)