《数据库系统概念03-SQL.ppt》由会员分享，可在线阅读，更多相关《数据库系统概念03-SQL.ppt（92页珍藏版）》请在三一办公上搜索。
1、2023/11/14,1/105,Book:数据库概念 Ver.5 by A.Silberschatz Chapter 3 SQL第三章 SQL,1,2023/11/14,2/105,第3章 目标与问题,项目驱动目标：如何建立和查询一个关系数据库：一、数据库语言及应具备的处理能力 二、数据定义子语言DDL 三、数据查询子语言DQL1(基本查询)四、数据查询子语言DQL2(复杂查询)五、数据操纵子语言DML主要讨论问题：为什么需要数据库语言什么是SQL语言，有何特点数据库定义子语言提供那些功能如何定义数据结构数据库查询语言提供那些基本操作为什么还需要嵌套子查询为什么还需要视图什么是关系连接,有哪
2、些连接方式数据更新子语言提供哪些处理能力,2023/11/14,3/105,Chapter 3:SQL,Data Definition 数据定义Basic Query Structure 基本查询结构Set Operations 集合运算Aggregate Functions 聚集函数Null Values空值Nested Subqueries嵌套 查询Complex Queries 复杂查询Views 视图Modification of the Database 修改数据库Joined Relations*连接关系,2023/11/14,4/105,为了提高应用开发效率(数据、甚至其处理可能
3、用于众多应用程序中)为了方面与应用程序交互(提共一种简洁、标准的数据操作接口)也为了增强开发的可靠性(若采用临时编程，要保证软件正确性费事)可快捷、方便地满足临时查询需要(马上需要院系班的平均成绩统计表)数据结构定义功能（创建结构）数据操纵功能(更新数据)数据控制功能（安全访问）方便灵活的数据获取功能（查询数据）,为什么需要数据库语言？,问题1答案,数据库语言及应具备的处理能力,一个完备的数据库语言应当具备什么操作能力？,2023/11/14,5/105,前身：IBM Sequel language developed as part of System R project at the IB
4、M San Jose Research Laboratory后来：Renamed Structured Query Language(SQL)再后来：发展成为美国标准化工作组ANSI发布的关系数据库语言标准特点：SQL是一种高级的非过程化描述语言，（犹如4GL）简单易用，操作能力极强（足够用，或称称完备语言，后面将分析指出！）。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，具有不同底层结构的不同DBMS可以使用相同的SQL语言操作数据。组成：SQL包括多种子语言（1数据库原理王能斌p.56）数据定义子语言DDL数据查询语言DQL数据操纵子语言DML数据控制子语言DCL（
5、第4章，下次课介绍）,问题2答案,什么是SQL语言，有何特点？,数据库语言及应具备的处理能力,2023/11/14,6/105,Data Definition Language(DDL)数据定义语言cp48,The schema for each relation.定义模式The domain of values associated with each attribute.值域Integrity constraints 完整性约束The set of indices to be maintained for each relations.索引Security and authorizatio
6、n information for each relation.安全，授权The physical storage structure of each relation on disk.物理存储,数据定义子语言应提供那些功能？,问题3答案,2023/11/14,7/105,Domain Types in SQL 域类型 自学 cp48,char(n).Fixed length character string,with user-specified length n.varchar(n).Variable length character strings,with user-specified
7、 maximum length n.int.Integer(a finite subset of the integers that is machine-dependent).smallint.Small integer(a machine-dependent subset of the integer domain type).numeric(p,d).Fixed point number,with user-specified precision of p digits,with n digits to the right of decimal point.real,double pre
8、cision.Floating point and double-precision floating point numbers,with machine-dependent precision.,2023/11/14,8/105,Domain Types in SQL 域类型 自学 cp48,float(n).Floating point number,with user-specified precision of at least n digits.Null values are allowed in all the domain types.Declaring an attribut
9、e to be not null prohibits null values for that attribute.create domain construct in SQL-92 creates user-defined domain types 自定义类型create domain person-name char(20)not null,2023/11/14,9/105,Date/Time Types in SQL(Cont.)自学 cp48,date.Dates,containing a(4 digit)year,month and dateE.g.date 2001-7-27tim
10、e.Time of day,in hours,minutes and seconds.E.g.time 09:00:30 time 09:00:30.75timestamp:date plus time of dayE.g.timestamp 2001-7-27 09:00:30.75Interval:period of timeE.g.Interval 1 daySubtracting a date/time/timestamp value from another gives an interval valueInterval values can be added to date/tim
11、e/timestamp valuesCan extract values of individual fields from date/time/timestampE.g.extract(year from r.starttime)Can cast string types to date/time/timestamp E.g.cast as date,2023/11/14,10/105,Create Table Construct 建表 DDL cp48,create table r(A1 D1,A2 D2,.,An Dn,(integrity-constraint1),.,(integri
12、ty-constraintk)Example:create table branch(branch-namechar(15)not null,branch-citychar(30),assetsinteger),问题4答案1,如何创建(描述)数据结构？,2023/11/14,11/105,Integrity Constraints in Create Table 建表时 定义完整性约束，作为定义的一部分，用户方便,not nullprimary key(A1,.,An)check(P),where P is a predicate,Example:Declare branch-name as
13、the primary key for branch and ensure that the values of assets are non-negative.create table branch(branch-namechar(15),branch-citychar(30)assetsinteger,primary key(branch-name),check(assets=0),primary key declaration on an attribute automatically ensures not null in SQL-92 onwards,needs to be expl
14、icitly stated in SQL-89,Key 一定不能用空值,主键约束,检查约束,非空约束,2023/11/14,12/105,Drop and Alter Table Constructs ep49删表,和 用默认空值,drop table.alter table r add A D/改为默认值NULL 属性 域（类型）,如何更改和删除已定义的数据结构？,2023/11/14,13/105,SQL查询能力,SQL实现了五种基本操作投影选择笛卡尔集并差SQL查询能力是强大的、足够的完备性：上五种操作可以复合使用还实现了众多扩展功能,数据查询子语言的查询能力有多强？,问题5答案,遗留问
15、题1：SQL如何描述？遗留问题2：SQL如何描述？遗留问题3：SQL如何描述？遗留问题4：SQL如何描述？遗留问题5：SQL如何描述？,遗留问题6：SQL如何描述？,2023/11/14,14/105,Basic Query Structure 查询的基本结构 cp50,SQL is based on set and relational operations with certain modifications and enhancements A typical SQL query has the form:select A1,A2,.,An)from r1,r2,.,rm where P
16、 Ais represent attributesris represent relationsP 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.,数学模型,语言实现（下页还有）,2023/11/14,15/105,SQL is based on set and relational operations with certain modificati
17、ons and enhancements A typical SQL query has the form:典型查询SFW 结构 select A1,A2,.,An 投影，选字段,(不幸的名称)from r1,r2,.,rm 连接 where P 选择Ais represent attributesris represent relationsP 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
18、 query is a relation.,Basic Query Structure 查询的基本结构 cp50,2023/11/14,16/105,The select Clause 选择子句 投影 cp51,select branch-namefrom loanAlgebra Expression:branch-name(loan)An asterisk in the select clause denotes“all attributes”select*from loanNOTE:SQL 对大小写不敏感 case insensitive,2023/11/14,17/105,The sel
19、ect Clause(Cont.)ep51,SQL 允许重复（关系和结果）allows duplicates in relations or results.强调 去重复：To force the elimination of duplicates select distinct branch-namefrom loan保留重复The keyword all specifies that duplicates not be removed.select all branch-namefrom loan,2023/11/14,18/105,The select Clause(Cont.)ep52
20、,SQL 允许重复（关系和结果）allows duplicates in relations or results.强调 去重复：To force the elimination of duplicates select distinct branch-namefrom loan保留重复The keyword all specifies that duplicates not be removed.select all branch-namefrom loan,2023/11/14,19/105,The where Clause where 子句 cp51,The where predicat
21、eThe find all loan number for loans made a the Perryridge branch with loan amounts greater than$1200.select loan-numberfrom loanwhere branch-name=Perryridge and amount 1200Comparison results can be combined using the logical connectives and,or,and not.Comparisons can be applied to results of arithme
22、tic expressions.,True,False,逻辑操作,2023/11/14,20/105,The where Clause(Cont.),between Find the loan number of those loans with loan amounts between$90,000 and$100,000(that is,$90,000 and$100,000)select loan-numberfrom loanwhere amount between 90000 and 100000 闭区间 90000,100,000,SQL中，有若干类似英语自然语言的保留字，人性化，
23、改善用户界面，为自然语言查询作的伏笔.这是SQL在竞争中获胜的原因之一。,2023/11/14,21/105,The from Clause ep51,Cartesian product 笛卡尔积Find the Cartesian product borrower x loanselect from borrower,loanFind the name,loan number and loan amount of all customers having a loan at the Perryridge branch.找出在该支行有借款的账户信息select customer-name,bo
24、rrower.loan-number,amountfrom borrower,loanwhere borrower.loan-number=loan.loan-number and branch-name=Perryridge 保证正确性的 连接条件,遗留问题3答案：SQL实现了笛卡尔积操作！,2023/11/14,22/105,The from Clause ep51,Cartesian product 笛卡尔积Find the Cartesian product borrower x loanselect From borrower,loanFind the name,loan numbe
25、r 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,找出在该支行有借款的账户信息。,保证正确性的连接条件,2023/11/14,23/105,The Rename Operation 重命名 cp52,The SQL allows
26、 renaming relations and attributes using the as clause:old-name as new-nameFind the name,loan number and loan amount of all customers;rename the column name loan-number as loan-id.select customer-name,borrower.loan-number as loan-id,amountfrom borrower,loanwhere borrower.loan-number=loan.loan-number
27、,2023/11/14,24/105,Tuple Variables 元组变量（别名或简称）cp52,Tuple variables are defined in the from clause via the use of the as clause.用 as,引入关系名的 简称，别名，处理someFind the customer names and their loan numbers for all customers having a loan at some branch.select customer-name,T.loan-number,S.amount from borrow
28、er as T,loan as S/引入别名 where T.loan-number=S.loan-numberFind the names of all branches that have greater assets than some branch located in Brooklyn.select distinct T.branch-name from branch as T,branch as S/别名 where T.assets S.assets and S.branch-city=Brooklyn,注意：SFW三块处理次序 F W-S,2023/11/14,25/105,T
29、uple Variables 元组变量（别名或简称）ep52,Tuple variables are defined in the from clause via the use of the as clause.用 as,引入关系名的 简称，别名，处理someFind the customer names and their loan numbers for all customers having a loan at some branch.select customer-name,T.loan-number,S.amount from borrower as T,loan as S wh
30、ere T.loan-number=S.loan-numberFind the names of all branches that have greater assets than some branch located in Brooklyn.select distinct T.branch-name from branch as T,branch as S where T.assets S.assets and S.branch-city=Brooklyn,/引入别名或简称-S,2023/11/14,26/105,String Operations 字符串操作 cp53,%通配符.匹配任
31、意串，%matches any substring._.匹配任意字符找 住地 街道含“Main”.select customer-namefrom customerwhere customer-street like%Main%Match the name“Main%”转义符,使“%”作被处理对象like Main%escape SQL supports a variety of string operations such asconcatenation(using“|”)串的 链接 converting from upper to lower case(大小写 互换)finding str
32、ing length,extracting substrings,etc.,街道名 含子串Main,2023/11/14,27/105,String Operations 字符串操作 cp53,%通配符.匹配任意串，%matches any substring._.匹配任意字符找 住地 街道含“Main”.select customer-namefrom customerwhere customer-street like%Main%Match the name“Main%”转义符,使“%”作被处理对象like Main%escape SQL supports a variety of str
33、ing operations such asconcatenation(using“|”)converting from upper to lower case 大小写转换 finding string length 字符串长度,extracting substrings,查子串 etc.,串的 链接,2023/11/14,28/105,Ordering the Display of Tuples 记录排序 cp54,List in alphabetic order the names of all customers having a loan in Perryridge branchsel
34、ect distinct customer-namefrom borrower,loanwhere borrower loan-number-loan.loan-number and branch-name=Perryridgeorder by customer-nameWe may specify desc for descending order or asc for ascending order,for each attribute;ascending order is the default.E.g.order by customer-name desc,升序,降序,2023/11/
35、14,29/105,Duplicates 处理重复,SQL不主动删除重复，除非用distinct cp54,Duplicates 处理重复,SQL不主动删除重复，除非用distinct,2023/11/14,30/105,Chapter 4:SQL cp55,Set Operations 集合运算 Aggregate Functions 聚集函数Null Values 空值Nested Subqueries 嵌套 查询Basic Structure 基本结构Derived Relations 派生关系Views 视图Modification of the Database 数据库修改Joine
36、d Relations 连接关系Data Definition Language 数据定义语言Embedded SQL,ODBC and JDBC 嵌入 SQL,ODBC,JDBC以后将逐渐减少汉字注释,2023/11/14,31/105,Set Operations 集合操作 cp55,Find all customers who have a loan,an account,or both:(select customer-name from depositor)union 并(select customer-name from borrower)Find all customers wh
37、o have both a loan and an account.(select customer-name from depositor)intersect 交(select customer-name from borrower)Find all customers who have an account but no loan.(select customer-name from depositor)except 差(select customer-name from borrower),遗留问题4&5答案：SQL实现了并、差操作!,2023/11/14,32/105,Set Oper
38、ations 集合操作，自动删除重复 ep56,The set operations union,intersect,and except operate on relations and correspond to the relational algebra operations Each of the above operations automatically eliminates duplicates;to retain all duplicates use the corresponding multiset versions union all,intersect all and
39、 except all.Suppose a tuple occurs m times in r and n times in s,then,it occurs:m+n times in r union all smin(m,n)times in r intersect all smax(0,m n)times in r except all s,不删除重复要专门说明,2023/11/14,33/105,Chapter 4:SQL 本章要点如下,Basic Structure 基本结构Set Operations 集合运算Aggregation Functions 聚集函数Null Values
40、 空值Nested Subqueries 嵌套 查询Derived Relations 派生关系Views 视图Modification of the Database 数据库修改Joined Relations 连接关系Data Definition Language 数据定义语言Embedded SQL,ODBC and JDBC 嵌入 SQL,ODBC,JDBC以后将逐渐减少汉字注释,2023/11/14,34/105,Aggregate Functions 聚集函数 cp56,These functions operate on the multiset of values of a
41、column of a relation,and return a valueavg:average value 平均 min:minimum value 最小max:maximum value 最大sum:sum of values 和 count:number of values 总计,2023/11/14,35/105,Aggregate Functions(Cont.)聚集函数 cp56,Find the average account balance at the Perryridge branch.select avg(balance)from accountwhere branc
42、h-name=PerryridgeFind the number of tuples in the customer relation.select count(*)from customerFind the number of depositors in the bank.select count(distinct customer-name)from depositor,投影和聚集函数同时作,出现在 select后,2023/11/14,36/105,Aggregate Functions Group By 分组 cp57,Find the number of depositors for
43、 each branch.找每个支行的储户数select branch-name,count(distinct customer-name)from depositor,accountwhere depositor.account-number=account.account-numbergroup by branch-nameNote:Attributes in select clause outside of aggregate functions must appear in group by list注意：所有select子句后面的不在聚合函数中的属性必须出现在group by子句后,
44、不重复,/按支行相同的分组,2023/11/14,37/105,Aggregate Functions Having Clause cp57,Find the names of all branches where the average account balance is more than$1,200.找平均余额超过1200$的支行名称select branch-name,avg(balance)from accountgroup by branch-namehaving avg(balance)1200Note:predicates in the having clause are a
45、pplied after the formation of groups whereas predicates in the where clause are applied before forming groups having 子句中的谓词在形成分组后起作用 Where子句在分组前起作用,此 平均 在银行内平均,此平均 对组内平均,2023/11/14,38/105,Chapter 4:SQL 本章要点如下,Basic Structure 基本结构Set Operations 集合运算Aggregate Functions 聚集函数Null Values 空值Nested Subquer
46、ies 嵌套 查询Derived Relations 派生关系Views 视图Modification of the Database 数据库修改Joined Relations 连接关系Data Definition Language 数据定义语言Embedded SQL,ODBC and JDBC 嵌入 SQL,ODBC,JDBC以后将逐渐减少汉字注释,2023/11/14,39/105,Null Values 空值 cp58,null,无值，或 暂时 unknownThe predicate is null can be used to check for null values.E.g
47、.Find all loan number which appear in the loan relation with null values for amount.select loan-number from loanwhere amount is null X+null=nullHowever,aggregate functions simply ignore nullsmore on this shortly聚合函数忽略空值,找出借款为空值 的 贷款,2023/11/14,40/105,Null Values and Three Valued Logic cp58 空值和三值逻辑（T
48、,F,U),法则,按语义理解,Any comparison with null returns unknownE.g.5 null or null=nullThree-valued logic using the truth value unknown:OR:(unknown or true)=true,(unknown or false)=unknown(unknown or unknown)=unknownAND:(true and unknown)=unknown,(false and unknown)=false,(unknown and unknown)=unknownNOT:(no
49、t unknown)=unknown“P is unknown”=T P=unknownResult of where clause predicate is treated as false if it evaluates to unknown,把Null换成True和 False，如结果一致，则为其值，否则结果为NUll,在where子句中，未知当作 False,2023/11/14,41/105,Null Values and Aggregates 空值和聚集cp58,Total all loan amounts select sum(amount)from loanAbove stat
50、ement ignores null amountsresult is null if there is no non-null amount,that is the,聚集一般忽略空值,惟一例外：count(*)计算 元组个数，含空值的元组也计数,2023/11/14,42/105,Nested Subqueries 嵌套的子查询 cp59,SQL provides a mechanism for the nesting of sub-queries.SQL提供嵌套子查询机制A subquery is a select-from-where expression that is nested
