第10讲-数据库实现(数据库语言).ppt

第5部分:数据库实现  数据库语言SQL 教 师：朱征宇 单 位：重庆大学计算机学院 1 SQL历史 ☆ 2 SQL数据定义语言(DDL) ☆ 3 SQL查询语言(QL)的基本结构 ☆ 4 附加的基本运算 ☆ 5 集合运算 ☆ 6 空值 ☆ 7 聚集函数 ☆ 8 嵌套子查询 ☆ 9 数据库的修改 ☆ 10 连接表达式 ☆ 11 视图 ☆ 12 事务 ☆ 13 完整性约束 ☆ 14 其它数据类型及索引 ☆ 15 授权 ☆ Joined Relations(Cont.) Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join. Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated. 是否可以对视图进行数据更新？ 原则上，可以通过视图对数据库数据进行更新。 在DBMS内部，它是通过转换为对基本表的更新操作来实现的。 什么情形才可以对视图进行数据更新？ 只能在少部分特殊视图（通俗地称为“行列视图”）上进行数据更新操作（例子） 但通常，结果是难以预测的（例子） Materialized Views Materializing a view: create a physical table containing all the tuples in the result of the query defining the view If relations used in the query are updated, the materialized view result becomes out of date Need to maintain the view, by updating the view whenever the underlying relations are updated. Transactions Unit of work Atomic transaction either fully executed or rolled back as if it never occurred Isolation from concurrent transactions Transactions begin implicitly Ended by commit work or rollback work But default on most databases: each SQL statement commits automatically Can turn off auto commit for a session (e.g. using API) In SQL:1999, can use: begin atomic …. end Not supported on most databases *Assertion Example Every loan has at least one borrower who maintains an account with a minimum balance greater or equal to $1000.00 (与原PPT不同) 每笔贷款(可能多个贷款人)的贷款人中至少有一人的账户余额不小于1000.00美元 create assertion balance_constraint check (not exists ( select * from loan where not exists (  select * from borrower, depositor, account where loan.loan_number = borrower.loan_number 原组变量 and borrower.customer_name = depositor.customer_name and depositor.account_number = account.account_number