数据库课件总结：Database-Chapter-Three-Outline.docx

Database Chapter Three Outline
SQL
SQL names are case insensitive 不区分大小写Data Definition Language
Create Table ConstructAn SQL relation is defined using the create table command: create table r (4 Di, A2 D2,An Dn, (integrity-constrainti),
(integrity-constraintk))r is the name of the relation
each Aj is an attribute name in the schema of relation rDi is the data type of values in the domain of attribute A
Integrity Constraints in Create Tablenot null
primary key (4,・・・，An)Example: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative.
create table branch(branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check(assets>=0))
PRIMARY KEY -> FOR曰GN KEY -> CHECK
Drop and Alter Table Constructs
alter table r add A D 添加属性
alter table r drop 4删除属性
Drop table r 删除表Basic Query Structure
A typical SQL query has the form：
select Ai, A2,Anfrom ri, r2,rm
where P
represents an attribute /?, represents a relation P is a predicate.
This query is equivalent to the relational algebra expression.
口4,&…4储 XGX...Xd)The result of an SQL query is a relation.
The meaning of select is different fromThe select clause can contain arithmetic expressions involving the operation, +,*, and
/, and operating on constants or attributes of tuples.
-I表达式
表达式
SUM
COUNT
子查询块@^
^CURRENT^-
ALL
ANY
(c)比拟条件
DATE
user)
TIME
AVG
^distinct}
TTMESTAMP)- TIMEZCNE)
式MAX
MIN
一表达式
项
列名
DISTINCT
(h)聚集函数
列名」
BETWEEN)-表达式 /anDy
(i) between 条件
表达式
NOT
列名
like^HS^~ 字符串 ~O一~一
(j) like条件
NOT
表达式
常员
(k)6条件
exists)—(£}-子查询块 ~Q)~^
⑴exists条件
DISTINCT -> FROM -> WHERE -> GROUB BY -> HAVING -> ORDER BY.
ASC DESCThe Rename Operation
old-name as new-nameString Operations
The operator "like" uses patterns that are described using two special characters:
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)
union(select customer_name from borrower) lntersect> except;
自动去除重复。假设要全部那么：UNION ALL、INTERSECT ALL、EXCEPT ALL;Aggregate Functions
avg: average value min: minimum value max: maximum value sum: sum of values count: number of valuesGroup By Having Clause
All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes.
Nested Subqueries
A subquery is a select-from-where expression that is nested within another query (appear in where clause or having by clause).
Can't use order by in subqueryset membership: in and not in
Set Comparison >SOME大于子查询结果中的某个值
> ALL大于子查询结果中的所有值<SOME小于子查询结果中的某个值
< ALL小于子查询结果中的所有值=SOME大于等于子查询结果中的某个值
< =ALL大于等于子查询结果中的所有值=SOME小于等于子查询结果中的某个值
< =ALL小于等于子查询结果中的所有值= SOME等于子查询结果中的某个值
=ALL等于子查询结果中的所有值（通常没有实际意义）!=（或<>）SOME不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值(=some) = in However, (w some) = not in
(w all) = not in However, (= all) = in<>or!=

<>or!=
ANY
ALL
NOT IN
<=
<=MAX
<=MIN
>
>MIN
>MAX
>=
>=MIN
>=MAX
exists ru> rw0如果子查询不为空，那么返回true；
not exists r = r-0Relevant Nested Subqueries
NOT EXIST(B EXCEPT A)表示关系A包含关系BoThe unique construct tests whether a subquery has any duplicate tuples in its result.
(专门用来处理“只有一个”、“有两个或者两个以上的”)Derived Relations
SQL allows a subquery expression to be used in the from clause
Find the average account balance of those branches where the average account balance is greater than $1200.
select branch^ame, avg_balance
from (select branch_name, avg (balance)from account
group by branch_name)as branch_avg ( branch_name, avg_balance )
where avg_balance > 1200
Note that we do not need to use the having clause, since we 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 Clause
The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs.
Views (只要没有更新操作，视图可以出现在关系名出现的任何地方)A view provides a mechanism to hide certain data from the view of certain users.
create view vas < query expression >A view definition causes the saving of an expression; the expression is substituted into queries using the view.
Views Defined Using Other Views
A view relation vi is said to depend directly on a view relation 1/2 if 1/2 is used in the expression defining vi
A view relation v± is said to depend on view relation V2 if either vi depends directly to 1/2 or there is a path of dependencies from vi to 也A view relation v is said to be recursive if it depends on itself.
View ExpansionView expansion of an expression repeats the following replacement step:
repeatFind any view relation Vj in ei
Replace the view relation v, by the expression defining v；until no more view relations are present in ei
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 - Insertion
Insert into r(Al,A2,...,An) values(vl,v2, ...,vn)Modification of the Database - Updates
UPDATE r SET A^vl„.^vi WHERE Pupdate account
set balance = casewhen balance <= 10000 then balance *1.05
else balance * 1.06end
Update of a View
Most SQL implementations allow updates only on simple views (without aggregates) defined on a single relation