1 / 91

PART 2

PART 2. RELATIONAL DATABASES. Chapter 3 SQL. Three Parts in Chapter 3. Functions and Usage of SQL data definition: §3. 2, DDL data query/retrieve: §3.3 —— §3.8, Select clause data manipulation: § 3. 9 Modification of the Database. §3.1 Background.

cruz-bridges
Télécharger la présentation

PART 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PART 2 RELATIONAL DATABASES

  2. Chapter 3 SQL

  3. Three Parts in Chapter 3 • Functions and Usage of SQL • data definition: §3.2, DDL • data query/retrieve: §3.3——§3.8, Select clause • data manipulation: § 3.9 Modification of the Database Database System Concepts - Chapter3 SQL -

  4. §3.1 Background • Database query languages • commercial implementation of relational operations in DBS • interactive tools for users to develop , use/access, and maintain DBS • DBS access: create, retrieval, insert, delete, update, etc • “Query” • generalized definition • define, retrieve, modify, control etc. on DB Database System Concepts - Chapter3 SQL -

  5. 3.1 Background (cont.) • SQL • Structured Query Language • on the basis of the relational algebra and the tuplerelationalcalculus • first implemented inSystem Rby IBM in 1970s • standardized by ANSI and ISO, SQL-86, SQL-89, SQL- 92, SQL-99, OO-SQL, the most recent version is SQL-2003 • widely used for commercial DBS Database System Concepts - Chapter3 SQL -

  6. 3.1 Background (cont.) • Parts/functions of SQL • data definition language (DDL) • define, delete, modify on schemas, views and index • interactive data-manipulation language (DML) • query/retrieve, insert , delete, update on tuples in DB • data control • integrity constraints specifying • authorization: security control, specifying access rights • transaction management • embedded and dynamic SQL Database System Concepts - Chapter3 SQL -

  7. 3.1 Background (cont.) • 9basic operators in SQL Database System Concepts - Chapter3 SQL -

  8. 3.1 Background (cont.) • Case study used in this chapter Fig. 3.1 Schema of banking enterprise Database System Concepts - Chapter3 SQL - return

  9. §3.2 Data Definition • DDL in SQL allows the specification of information about each relation, including • the schema for each relation or view -----create table/view • the domain of values associated with each attribute • integrity constraints • the set of indices to be maintained for each relations • security and authorization information for each relation • the physical storage structure of each relation on disk 3.2.1 Basic Domain Types • char(n), varchar(n), int, smallint, numeric(p, d), real, double precision, float(n), date, time, timestamp, interval Database System Concepts - Chapter3 SQL -

  10. 3.2.2 Schema Definition (cont.) • An SQL relation is defined using the create table command create table r(A1D1, A2D2, ..., An Dn,(integrity-constraint1), ..., (integrity-constraintk)) • r is the name of the relation • each Ai is an attribute name in the schema of relation r • Di is the data type of values in the domain of attribute Ai • The most important integrity constraint is the primary-key specification • primary key (Aj1, Aj2, …, Ajm) Database System Concepts - Chapter3 SQL -

  11. 3.2.2 Schema Definition (cont.) • E.g. create table branch (branch-name char(15)branch-city char(30),assets numeric(16,2) primary key (branch-name) • A newly created table is empty initially, we can use insert command to load data into the table • insert intoaccount values (‘A-9372’, ‘Perryridge’, 1200) • The delete command removes tuples from the table • delete from account Database System Concepts - Chapter3 SQL -

  12. §3.2.2 Schema Definition (cont.) • The drop table command deletes all information (tuples and schema) about the dropped relation from the database • drop tabler • compared to delete fromr • The alter table command is used to add or delete/drop attributes to an existing relation • alter table r add A D • where A is the name of the attribute to be added to relation r and D is the domain of A • all tuples in the relation are assignednull as the value for the new attribute • e.g. alter table branch add branch-street char(30) Database System Concepts - Chapter3 SQL -

  13. §3.2.2 Schema Definition (cont.) • alter table r drop A • where A is the name of an attribute of relation r • e.g. alter table branch drop branch-street • dropping of attributes not supported by many databases Database System Concepts - Chapter3 SQL -

  14. §3.3 Basic structure— Select clause for data query query result : • A typical SQL query has the formselect A1, A2, ..., Anfromr1, r2, ..., rmwhere P • Ais represent attributes • ris represent relations • P is a predicate • This query is equivalent to the relational algebra expression  A1, A2, ..., An(P (r1 x r2 x ... x rm)) • the result of an SQL query is a relation • For more details about complete syntax definition for Select clause, refer to Appendix A. Syntax Definition of Select Clause t1 t2 tk Database System Concepts - Chapter3 SQL -

  15. 3.3.1 The select Clause • The select clause list the attributes desired in the result of a query • corresponding to the projection operation of the relational algebra • E.g. Find the names of all branches in the loan relationselect branch-namefrom loan • in the “pure” relational algebra syntax, the query would be  branch-name(loan) Database System Concepts - Chapter3 SQL -

  16. 3.3.1 The select Clause (cont.) • SQL allows duplicates in relations as well as in query results. • To force the elimination of duplicates, insert the keyword distinct after select. • E.g. Find the names of all branches in the loan relations, and remove duplicates select distinct branch-namefrom loan Database System Concepts - Chapter3 SQL -

  17. 3.3.1 The select Clause (cont.) • The keyword all specifies that duplicates not be removed select allbranch-namefrom loan • An asterisk * in the select clause denotes “all attributes” select *from loan • The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples • corresponding to extended projection operation Database System Concepts - Chapter3 SQL -

  18. 3.3.1 The select Clause (cont.) • E.g. The query: selectloan-number, branch-name, amount *100from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100 Database System Concepts - Chapter3 SQL -

  19. 3.3.2 The where Clause • The where clause specifies conditions that the result must satisfy • corresponding to the selection predicate p in the relational algebra operation p • E.g. To find all loan number for loans made at the Perryridgebranch with loan amounts greater than $1200.select loan-numberfrom loanwhere branch-name=‘Perryridge’ and amount > 1200 Database System Concepts - Chapter3 SQL -

  20. 3.3.2 The where Clause (cont.) • In where clause • comparison results can be combined using the logical connectives and, or, and not • comparisons can be applied to results of arithmetic expressions • SQL includes between and not between comparison operator • E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 • select loan-numberfrom loanwhere amountbetween 90,000 and 100,000 Database System Concepts - Chapter3 SQL -

  21. borrower loan 3.3.3 The from Clause • The fromclause lists the relations involved in the query • corresponding tothe Cartesian product operation of the relational algebra • E.g. !!!find the Cartesian productborrower × loanselect *from borrower, loan • As a simple representation of “natural join” • !!!e.g. corresponds to select * from borrower, loanwhere borrower.loan-number = loan.loan-number Database System Concepts - Chapter3 SQL -

  22. 3.3.3 The from Clause (cont.) • E.g. Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch • select customer-name, borrower.loan-number, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’ Database System Concepts - Chapter3 SQL -

  23. 3.3.4 The Rename Operation • The SQL allows renaming relations and attributes using the asclause to rename the attribute name, to avoid the same names for different attributes in the relations as query results old-name as new-name • as clause can appear in selectand fromclauses • E.g. Find the name, loan number, and loan amount of all customers; rename the column name loan-numberas loan-id select customer-name, borrower.loan-numberas loan-id, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number Database System Concepts - Chapter3 SQL -

  24. 3.3.5 Tuple Variables • Tuple variables are defined in the from clause via the use of the as clause • E.g.1 Find the name, loan number and loan amount of all customers having a loan at some branches • E.g.2 Find the names of all branches that have greater assets than some branch located in Brooklyn • /*涉及到对branch关系中属性assets的不同值的比较 select customer-name, T.loan-number, S.amountfrom borrower as T, loan as Swhere T.loan-number = S.loan-number Database System Concepts - Chapter3 SQL -

  25. 3.3.5 Tuple Variables (cont.) • select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets and S.branch-city = ‘Brooklyn’ • /*利用T和S区分不同的branch!!!!!, 实现了对同一属性的不同值的比较 Database System Concepts - Chapter3 SQL -

  26. 3.3.6 String Operations in where Subclause • SQL specifies strings by enclosing them in single quotes, e.g. ‘Perryridge’ • SQL introduces a series of operations on strings, of which the most commonly used operation is pattern-matching operator for comparisons on character strings select customer-namefrom customerwherecustomer-street like ‘%Main%’ • For more details, refer to Appendix B String Operations in SQL Database System Concepts - Chapter3 SQL -

  27. 3.3.7 Ordering the Display of Tuples • The order by clause causes the tuples in the result of query to appear in sorted order, i.e. ascending order or descending order, denoted by keywords asc or desc respectively • ascendingorder is the default • E.g. List in alphabetic descending order the names of all customers having a loan in Perryridgebranch select distinct customer-namefrom borrower, loanwhere borrower loan-number - loan.loan-number and branch-name = ‘Perryridge’order bycustomer-name desc Database System Concepts - Chapter3 SQL -

  28. 3.3.8 Duplicates • Viewing relations with duplicates as multisets, SQL can define not only what tuples will appear in the result of a query, but also how many copies of these tuples appear in the result • The duplicate semantics of select clause is based on multiset versions of the relational algebra operators • For more details, refer to Appendix C Duplicates in Select Clause Database System Concepts - Chapter3 SQL -

  29. §3.4 Set Operations • In SQL, the set operations union, intersect, and except operate on relations and correspond to the relational algebra operators and  • each of the above operations automatically eliminates duplicates • to retain all duplicates use the correspondingmultiset versions union all, intersect all and except all • E.g.1 Find all customers who have a loan, an account, or both • (selectcustomer-name from depositor)union(selectcustomer-name from borrower) Database System Concepts - Chapter3 SQL -

  30. 3.4 Set Operations (cont.) • E.g2. Find all customers who have both a loan and an account • (selectcustomer-name from depositor)intersect(selectcustomer-name from borrower) • E.g3. Find all customers who have an account but no loan • (selectcustomer-name from depositor)except(selectcustomer-name from borrower) Database System Concepts - Chapter3 SQL -

  31. §3.5 Aggregate Functions • The following aggregate functions operate on the set (or multiset) of values of a column of a relation, and return a value • avg: average value, min: minimum value, max: maximum value, sum: sum of values, count: number of values • these functions corresponds to G1, G2, …, GngF1( A1), F2( A2),…, Fn( An) (E) Database System Concepts - Chapter3 SQL -

  32. 3.5 Aggregate Functions (cont.) • How to conduct group by subclause and aggregate function • SelectA1, A2, ., ag_fun(Ai),.., Aj, .., Ak,…, An • From r1, r2, ..., rm • Where P1 • Group by Aj • havingP2 • notes: • P1 is defined on all attributes of r1, r2, ..., rm • Aj must be listed in select subclause • P2 is defined on A1, A2, ., Ai,..,Aj ,… , Ak, …, An, as the constraints on the group formed by Group byAj, so ag_fun (Ak) appear in P2 Database System Concepts - Chapter3 SQL -

  33. 3.5 Aggregate Functions (cont.) • step1. temporal relation T1 is derived from SelectA1, A2,…., Ai,..., Aj, .., Ak,…, An From r1, r2, ..., rm Where P1 • step2. T1 is grouped on Aj, resulting in temporal relation T2 • step3. if having subclause exists, then the group, which is not in accordance with P2, is filtered out of T2 • step4. SelectA1, A2, ., ag_fun(Ai),.., Aj, .., Ak,…, An From T2 Database System Concepts - Chapter3 SQL -

  34. 3.5 Aggregate Functions (cont.) • E.g.1 Find the average account balance at the Perryridge branch ( in accountrelation) • select avg (balance) from accountwhere branch-name = ‘Perryridge’ /*定义在多重集合上的计算 avg Database System Concepts - Chapter3 SQL -

  35. 3.5 Aggregate Functions (cont.) • E.g.2 Find the number of tuples in the customer relation • select count (*)from customer • E.g.3 Find the number of depositors in the bank • select count (distinct customer-name) /*删除重复元组from depositor Database System Concepts - Chapter3 SQL -

  36. 3.5 Aggregate Functions (cont.) • In Select clause, group by subclause is used to grouping tuples in the relations according to some attributes • E.g.4 Find the number of depositors for each branch • select branch-name, count (distinctcustomer-name)from depositor, accountwhere depositor.account-number = account.account-numbergroup by branch-name • note: attributes in select clause outside of aggregate functions must appear in group by list, e.g. branch-name Database System Concepts - Chapter3 SQL -

  37. result relation , bycount on T temporal relation T(branch-name, customer-name) deriving from depositorandaccount

  38. 3.5 Aggregate Functions (cont.) • Having clause is used to state a condition that applies to groups rather than tuples • predicates in the having clause are appliedafter the formation of groups whereas predicates in the where clause are applied before forming groups • E.g.5 Find the names of all branches where the average account balance is more than $1,200 • select branch-name, avg(balance)from accountgroup by branch-namehaving avg (balance) >= 1200 Database System Concepts - Chapter3 SQL -

  39. result relation , byavg on T.balance temporal relation T(branch-name,balance) deriving from account

  40. 3.5 Aggregate Functions (cont.) • E.g.6 Find the names of all branches where the average account balance is more than $1,200 • select branch-name, sum(balance)from accountgroup by branch-namehaving avg (balance) >= 1200 Database System Concepts - Chapter3 SQL -

  41. result relation , byavg on T.balance temporal relation T(branch-name,balance) deriving from account

  42. §3.6 Null Values • It is possible for tuples to have a null value, denoted by null, for some of their attributes • null signifies an unknown value or that a value does not exist. • The predicate is null can be used in Select clause to check for null values • E.g. Find all loan number which appear in the loan relation with null values for amount • select loan-numberfrom loanwhere amount is null • The result of any arithmetic expression involving null is null • e.g. 5 + null returns null Database System Concepts - Chapter3 SQL -

  43. 3.6 Null Values (cont.) • Any comparison with null returns unknown • e.g. 5 < null, null <> null , null = null • In where clause, three-valued logic using the truth value unknown (i.e. null, introduced in SQL 1999) • OR • (unknown or true) = true, (unknown or false) = unknown(unknown or unknown) = unknown • AND • (true and unknown) = unknown, (false and unknown) = false,(unknown and unknown) = unknown Database System Concepts - Chapter3 SQL -

  44. 3.6 Null Values (cont.) • NOT • (not unknown) = unknown • “P is unknown” evaluates to true if predicate P evaluates to unknown • Result of where clause predicate P is treated as false if it evaluates to unknown • Null values and aggregates • all aggregate operations except count(*) ignore tuples with null values on the aggregated attributes Database System Concepts - Chapter3 SQL -

  45. 3.6 Null Values (cont.) • E.g. Total all loanamounts select sum (amount)from loan • if there are null values in amount attributes, above statement ignores null amounts • the result is null if there is no non-nullamount Database System Concepts - Chapter3 SQL -

  46. §3.7 Nested (嵌套) Subqueries • SQL provides a mechanism for the nesting of subqueries, to implement more complex query • A subquery is a select-from-group expression that is nested within another query • the subquery is often nested in the where clause, and the having clause of another query • A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality 3.7.1 Set Membership • Using in or not in connective to test membership or absence membership in where clause Database System Concepts - Chapter3 SQL -

  47. 3.7.1 Set Membership • E.g.1 Find all customers who have both an account and a loan at the bank • select distinct customer-namefrom borrowerwhere customer-name in (select customer-namefromdepositor) • E.g.2 Find all customers who have a loan at the bank but do not have an account at the bank • select distinct customer-namefrom borrowerwhere customer-name not in (select customer-namefrom depositor) Database System Concepts - Chapter3 SQL -

  48. 3.7.1 Set Membership (cont.) • E.g.3 Find the names of all customers who have both an account and a loan at the Perryridge branch • 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) 对应于关系customer Database System Concepts - Chapter3 SQL -

  49. 3.7.2 Set Comparison • E.g.1 Find the names of branches that have assets greater than those of at least onebranch located in Brooklyn.( …至少比位于Brooklyn的某一家支行…） • select branch-namefrom branchwhere assets > some (select assetsfrom branchwhere branch-city = ‘Brooklyn’) • > some : 至少比1个大 Brooklyn各支行的资产值集合 Database System Concepts - Chapter3 SQL -

  50. 3.7.2 Set Comparison (cont.) • compared with the example in §3.3.5 select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets andS.branch-city = ‘Brooklyn’ • SQL allows < some/all, <= some/all, >= some/all, =some/all, and <>some/all comparisons • for more details, refer to Appendix D Some/All Set Comparisons Database System Concepts - Chapter3 SQL -

More Related