数据库原理 第4章 SQL课件.ppt

Chapter 4: SQL;Schema Used in Examples示例用的模式;Basic Structure基本结构;The select Clause select子句;;The select Clause (Cont.);The where Clause ;The where Clause (Cont.);The from Clause;The Rename Operation更名运算;;Tuple Variables元组变量;;String Operations字符串操作;;Ordering the Display of Tuples元组显示排序;;Duplicates重复;Duplicates (Cont.);Set Operations集合运算;Find all customers who have a loan, an account, or both: (select customer-name from depositor) union (select customer-name from borrower) 如果一个叫Jones的客户在银行中有几个帐户或贷款（或两者都有），Jones在结果中也只出现一次。 union all：如果Jones在银行中有三个帐户和两笔贷款，那么Jones在结果中出现五次。 Find all customers who have both a loan and an account. (select customer-name from depositor) intersect (select customer-name from borrower) 如果一个叫Jones的客户在银行中有几个帐户和贷款，Jones在结果中也只出现一次。 intersect all：如果Jones在银行中有三个帐户和两笔贷款，那么Jones在结果中出现两次。 Find all customers who have an account but no loan. (select customer-name from depositor) except (select customer-name from borrower) 仅当Jones在银行中有帐户但无贷款时，那么Jones在结果中出现且只出现一次。 except all：如果Jones在银行中有三个帐户和两笔贷款，那么Jones在结果中出现一次。但若Jones在银行中有两个帐户和三笔贷款，那么Jones在结果中不出现。 ;Aggregate Functions;Aggregate Functions – Group By;Aggregate Functions – Having Clause;Find the number of tuples in the customer relation. select count (*) from customer Find the average balance for each customer who lives in Harrison and has at least three accounts. Account-schema=(branch-name, account-number, balance) Depositor-schema=(customer-name, account-number) Customer -schema=(customer-name, customer-street,customer-city) select depositor.customer-name, avg (balance) from depositor,account,customer where depositor.account-number = account.account-number and depositor. customer-name=customer. customer-name and customer-city=‘Harrison’ group by depositor. customer-name having count (distinct depositor.account-number ) =3 ;Null Values;Null Values and Three Valued Logic