數(shù)據(jù)庫(kù)課件總結(jié)：Database Chapter Three Outline

1、Database Chapter Three OutlineSQLSQL names are case insensitive 不區(qū)分大小寫Data Definition Language Create Table ConstructAn SQL relation is defined using the create table command:create table r (A1 D1, A2 D2, ., An Dn,(integrity-constraint1),.,(integrity-constraintk)r is the name of the relationeach Ai

2、is an attribute name in the schema of relation rDi is the data type of values in the domain of attribute Ai Integrity Constraints in Create Tablenot nullprimary key (A1, ., An ) Example: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative. create t

3、able branch (branch_namechar(15), branch_citychar(30), assetsinteger, primary key (branch_name), check(assets=0)PRIMARY KEY - FOREIGN KEY - CHECKDrop and Alter Table Constructsalter table r add A D 添加屬性 alter table r drop A 刪除屬性Drop table r 刪除表Basic Query StructureA typical SQL query has the form：se

4、lect A1, A2, ., Anfrom r1, r2, ., rmwhere PAi represents an attributeRi represents a relationP is a predicate.This query is equivalent to the relational algebra expression.The result of an SQL query is a relation.The meaning of select is different fromThe select clause can contain arithmetic express

5、ions involving the operation, +, , *, and /, and operating on constants or attributes of tuples.DISTINCT - FROM - WHERE - GROUB BY - HAVING - ORDER BY. ASC DESCThe Rename Operation old-name as new-nameString OperationsThe operator “l(fā)ike” uses patterns that are described using two special characters:

6、percent (%). The % character matches any substring.underscore (_). The _ character matches any character. concatenation (using “|”) converting from upper to lower case (and vice versa) (upper, lower) finding string length, extracting substrings, etc.Set Operations(select customer_name from depositor

7、)union(select customer_name from borrower) Intersect、except;自動(dòng)去除重復(fù)。若要全部則: UNION ALL 、 INTERSECT ALL 、 EXCEPT ALL;Aggregate Functionsavg: average value min: minimum value max: maximum value sum: sum of valuescount: number of valuesGroup ByHaving ClauseAll aggregate operations except count(*) ignore t

8、uples with null values on the aggregated attributes.Nested SubqueriesA subquery is a select-from-where expression that is nested within another query (appear in where clause or having by clause). Cant use order by in subquery set membership: in and not inSet Comparison SOME大于子查詢結(jié)果中的某個(gè)值 ALL大于子查詢結(jié)果中的所

9、有值 SOME 小于子查詢結(jié)果中的某個(gè)值 = SOME 大于等于子查詢結(jié)果中的某個(gè)值 = ALL大于等于子查詢結(jié)果中的所有值= SOME 小于等于子查詢結(jié)果中的某個(gè)值 = ALL小于等于子查詢結(jié)果中的所有值= SOME 等于子查詢結(jié)果中的某個(gè)值 =ALL等于子查詢結(jié)果中的所有值（通常沒(méi)有實(shí)際意義）!=（或） SOME 不等于子查詢結(jié)果中的某個(gè)值!=（或）ALL不等于子查詢結(jié)果中的任何一個(gè)值 (= some) in However, ( some) not in ( all) not in However, (= all) inexists r r 如果子查詢不為空，則返回true；not ex

10、ists r r = Relevant Nested Subqueries NOT EXIST(B EXCEPT A) 表示關(guān)系A(chǔ)包含關(guān)系B。The unique construct tests whether a subquery has any duplicate tuples in its result. （專門用來(lái)處理“只有一個(gè)”、“有兩個(gè)或者兩個(gè)以上的”）Derived RelationsSQL allows a subquery expression to be used in the from clauseFind the average account balance of t

11、hose branches where the average account balance is greater than $1200.select branch_name, avg_balancefrom (select branch_name, avg (balance) from account group by branch_name ) as branch_avg ( branch_name, avg_balance )where avg_balance 1200Note that we do not need to use the having clause, since we

12、 compute the temporary (view) relation branch_avg in the from clause, and the attributes of branch_avg can be used directly in the where clause.With ClauseThe with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs. View

13、s (只要沒(méi)有更新操作，視圖可以出現(xiàn)在關(guān)系名出現(xiàn)的任何地方)A view provides a mechanism to hide certain data from the view of certain users. create view v as A view definition causes the saving of an expression; the expression is substituted into queries using the view.Views Defined Using Other ViewsA view relation v1 is said to

14、 depend directly on a view relation v2 if v2 is used in the expression defining v1 A view relation v1 is said to depend on view relation v2 if either v1 depends directly to v2 or there is a path of dependencies from v1 to v2 A view relation v is said to be recursive if it depends on itself.View Expa

15、nsionView expansion of an expression repeats the following replacement step:repeatFind any view relation vi in e1Replace the view relation vi by the expression defining vi until no more view relations are present in e1 As long as the view definitions are not recursive, this loop will terminateModification of the Database Deletion Delete from r where PModification of the Database InsertionInsert into r(A1,A2,An) values(v1,v2, ,vn)Modification of the Database UpdatesUPDATE r