数据库技术与应用 （全套完整课件)课件.ppt

连接查询—例子 Relation loan Relation borrower customer-name loan-number Jones Smith Hayes L-170 L-230 L-155 amount 3000 4000 1700 branch-name Downtown Redwood Perryridge loan-number L-170 L-230 L-260 Note: borrower information missing for L-260 and loan information missing for L-155 Joined Relations – Examples loan inner join borrower onloan.loan-number = borrower.loan-number loan left outer join borrower onloan.loan-number = borrower.loan-number branch-name amount Downtown Redwood 3000 4000 customer-name loan-number Jones Smith L-170 L-230 loan-number L-170 L-230 branch-name amount Downtown Redwood Perryridge 3000 4000 1700 customer-name loan-number Jones Smith null L-170 L-230 null loan-number L-170 L-230 L-260 Joined Relations – Examples* loan natural inner join borrower loan natural right outer join borrower branch-name amount Downtown Redwood 3000 4000 customer-name Jones Smith loan-number L-170 L-230 branch-name amount Downtown Redwood null 3000 4000 null customer-name Jones Smith Hayes loan-number L-170 L-230 L-155 Joined Relations – Examples loan full outer join borrower using (loan-number) Find all customers who have either an account or a loan (but not both) at the bank. branch-name amount Downtown Redwood Perryridge null 3000 4000 1700 null customer-name Jones Smith null Hayes loan-number L-170 L-230 L-260 L-155 select customer-name from (depositor natural full outer join borrower) where account-number is null or loan-number is null 概 要 ★概述 ★ SQL的数据定义 ★ SQL的数据查询 SQL的数据更新 ★ 视图的定义和对视图的操作 SQL的数据更新 用SQL语句向数据库中的相应关系 ★删除数据库数据 ★修改数据库数据 ★增加数据库数据 13 删除 ★删除请求可以删除整个元组，但不能只删除某些属性上的值。其格式如下： delete from R where F ★其意义是：从关系R 中删除满足条件F 的元组。如果省略F语句，则 R中所有元组都被删除，但表的定义仍存在。 删除（续） ★三种删除方式 □删除某一个元组的值 □删除多个元组的值 □带子查询的删除语句 例子 ★删除Perryridge 分支机构的所有帐户 delete from account where branch-name =‘Perryidge’ ★删除位于Needlham的每一个分行的所有帐户 delete from account where branch-name in (select branch-name from branch