SQL讲座-北大.ppt

1、SQL语言,高军北京大学计算机科学技术系数据库与信息系统研究室Telmail:gaojun,主要内容,数据库基本知识SQL基本概念和内容SQL分析扩展,数据库技术的基本术语,数据库（Database，简记为DB）DB是长期存储在计算机内、有组织的、可共享的、统一管理的相关数据的集合。DB的特点：数据按一定的数据模型组织、描述和储存支持多用户共享冗余度较小数据独立性较高易扩展,数据库技术的基本术语,数据库管理系统（DatabaseManagementSystem，简记为DBMS）DBMS是位于用户与操作系统（OS）之间的一层数据管理软件，它为用户或应用程序提供访问DB

2、的方法，包括DB的建立、查询、更新及各种数据控制。用途:能够科学地组织和存储数据、高效地获取和维护数据分类：DBMS总是基于某种数据模型，可以分为层次模型、网状模型、关系模型、面向对象模型,DBMS,OS,DB,用户,数据库模式,关系模型发展,E.F.Codd于70年代初提出关系数据理论，他因此获得1981年的ACM图灵奖特点：关系理论是建立在集合代数理论基础上的，有着坚实的数学基础简单，表的概念直观、单一，用户易理解非过程化的数据请求，用户只需提出“做什么”，无须说明“怎么做”数据独立性，坚实的理论基础早期代表系统SystemR：由IBM研制INGRES：由加州Berkeley分校研制,关系

3、模型概述,关系模型的三要素：关系数据结构（关系）：实体及实体集间的联系均用关系（二维表格，逻辑结构）表示；关系操作：一个完备的操作集合，运算输入关系，运算输出关系；关系完整性约束：实体完整性；参照完整性；用户定义的完整性,关系基本概念-关系,域（Domain）一组值的集合，这组值具有相同的数据类型如整数的集合、字符串的集合、全体学生的集合笛卡尔积（CartesianProduct）一组域D1,D2,Dn的笛卡尔积为:D1D2Dn=(d1,d2,dn)|diDi,i=1,n笛卡尔积的每个元素(d1,d2,dn)称作一个n-元组（n-tuple）元组的每一个值di叫做一个分量（component）

4、若Di的基数为mi，则笛卡尔积的基数为,关系基本概念-关系,关系笛卡尔积D1D2Dn的子集叫做在域D1,D2,Dn上的关系，用R(D1,D2,Dn)表示R是关系的名字，n是关系的度或目关系是笛卡尔积中有意义的子集关系也可以表示为二维表关系supply-customer(S,C,P),元组,属性,关系模型-操作,关系操作关系操作是集合操作，操作的对象及结果都是集合，是一次一集合（Set-at-a-time）的方式而非关系型的数据操作方式是一次一记录（Record-at-a-time）关系操作可以用关系代数和关系演算两种方式来表示，它们是相互等价的如用关系代数来表示关系的操作，可以有选择、投影、连

5、接、除、交、差、并等,关系模型-关系代数操作,过程性语言6个基本操作符选择Select投影project并union差setdifference笛卡儿乘积Cartesianproduct重命名rename运算符的输入是两个或多个关系，输出是一个新的关系，满足运算的封闭性,关系模型-完整性,实体完整性关系的主码中的属性值不能为空值空值：不知道或无意义意义：关系对应到现实世界中的实体集，元组对应到实体，实体是相互可区分的，通过主码来唯一标识，若主码为空，则出现不可标识的实体，这是不容许的用户定义的完整性用户针对具体应用环境定义的完整性约束条件如Sid要求是8位整数，Sex要求取值为“男”或“女”,

6、关系模型-完整性,参照完整性如果关系R2的外部码Fk与关系R1的主码Pk相对应，则R2中的每一个元组的Fk值或者等于R1中某个元组的Pk值，或者为空值意义：如果关系R2的某个元组t2参照了关系R1的某个元组t1，则t1必须存在例如关系SC在Sid上的取值有两种可能空值，表示该产品尚未决定从哪个供应商中采购若非空值，则必须是Supply关系中某个元组的值，表示该产品不能从某个不存在的供应商采购,主要内容,数据库基本知识SQL基本概念和内容SQL分析扩展,SQL的历史,1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理

7、系统SYSTEMR，配制的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQUEryLanguage)语言后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。,SQL标准的发展,SQL-86“数据库语言SQL”SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支

8、持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-99增加对面向对象模型和数据分析的支持,SQL特点,一体化集DDL，DML，DCL于一体单一的结构-关系，带来了数据操作符的统一面向集合的操作方式一次一集合非过程化用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径两种使用方式，统一的语法结构SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）语言简洁，易学易用,SQL语言功能分类,SQL查询和关系模型,QueryOptimizationandExecution,Relationa

9、lOperators,FilesandAccessMethods,BufferManagement,DiskSpaceManagement,DB,SQLParser,语法验证、数据字典、视图重写,基于代价、启发式、物化执行和流水线执行,基本关系运算符号的实现方式,堆文件、顺序文件、Hash文件,考虑数据库特性的缓存管理,定长纪录文件、非定长纪录文件,SQL基本概念和内容,基本结构集合运算聚集函数空值嵌套查询派生关系视图数据库修改数据定义语言,基本Select语句的句法,基本Select语句的句法如下：Select目标表的列名或列表达式序列From基本表名和（或）视图序列Where行条件表达式G

10、roupby列名序列Having组条件表达式Orderby列名ASC|DESC，Where子句称为行条件子句Group子句称为分组子句Having子句称为组条件子句Order子句称为排序子句,Select语句基本执行流程,读取From子句中基本表、视图的数据，执行笛卡尔积操作。选取满足Where子句中给出的条件表达式的元组。按Group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组。按Select子句中给出的列名或列表达式求值输出。Order子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。,集合操作,当两个子查询结果的结构完全一致时，可以让

11、这两个子查询执行并、交、差操作。并、交、差的运算符为Union、Intersect和Except。示例：求购买了001或（且）002号厂商产品的客户号。SelectCidFromSCWhereSid=001Union(Intersect)SelectCidFromSCWhereSid=002,聚集函数,SQL中聚集函数将一列中所有的值聚集为单个值SQL中基本的聚集函数包括平均值：avg最小值：min最大值：max总和：sum记数：count,singleton,GroupBy子句,Groupby将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值分组命令格式Grou

12、pby列名Having条件表达式Having对分组进行选择，只将聚集函数作用到满足条件的分组上,空值,SQL中允许列值为空，空值用保留字NULL表示。空值表示值未知或者值不存在空值测试格式columnisnotnull示例:找出地址为空的客户名称SelectCNameFromCustomerWhereLocationisnull不可写为WhereLocation=null,集合成员资格,In子查询判断表达式的值是否在子查询的结果中表达式格式notin（子查询）示例列出供应商A和供应商B的所有信息Select*FromSupplyWhereSnamein（“供应商A”，“供应商B”）购买了001

13、号供应商的客户名称SelectCnameFromCustomerWhereCidin(SelectCidFromSCWhereSid=001）,集合之间的比较,some/all子查询表达式比较运算符some（子查询）表达式的值至少与子查询结果中的一个值相比满足比较运算符表达式比较运算符all（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符,集合之间的比较示例,示例找出购买产品A数量最多的客户号(不支持Max(Sum()SelectCidFromSCGroupbyCidWherePart=“产品A”HavingSum(Quantity)=all(SelectSum(Quantit

14、y)FromSCWherePart=“产品A”GroupbyCid),集合基数的测试,判断子查询的结果集合中是否有任何元组存在格式：notexists（子查询）和In子句的区别in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询大多与外层查询有关，需要执行多次，称之为相关子查询CorrelatedSubquery示例列出购买01号供应商产品的客户号及名称SelectCid，CNameFromCustomerWhereexists(Select*FromSCWhereSid=01andSC.Cid=Customer.Cid),派生关系,SQL-92中，允许在From子句中使

15、用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用格式(子查询)as关系名(列名，列名，)示例：找出购买产品A总数超过10的客户先求出每个客户购买产品A的总数，再从中找出产品数大于10的客户SelectCName，Sum_QuantityFrom(SelectCName，Sum(Quantity)FromSC，CustomerWhereSC.Cid=Customer.CidandSC.part=“产品A”GroupbyCName)AscustomerSum(CNAME，Sum_Quantity)WhereSum_Quantity=10,SQL的数据修改功能：插入单个元组,Inse

16、rt语句完成数据表新增数据的处理，一次操作插入单个元组或元组集合命令格式Insertinto表名（列名，列名values（值，值）插入单个元组示例InsertintoCustomervalues(123,“北京移动A”,“北京海淀”)必须是按照表定义的顺序插入全部的列数；InsertintoCustomer(Cid,CName,Location)values(123,“北京移动A”,“北京海淀”),SQL的数据修改功能：插入元组集合,Insert语句完成数据表新增数据的处理，支持插入元组集合Insertinto表名（列名，列名（子查询）插入子查询结果中的若干条元组示例将购买产品总数大于90的客

17、户加入到ValuedCustomer中InsertintoValuedCustomer(Cid,Quantity)SelectCid,Sum(Quantity)FromSCGroupby(Cid)HavingSum(Quantity)90,SQL的数据修改功能：删除操作,Delete语句完成数据表删除数据的处理，一次操作删除满足条件的元组集合命令格式DeleteFrom表名Where条件表达式从表中删除符合条件的元组，如果没有Where语句，则删除所有元组示例清除所有客户元组DeleteFromCustomer删除供应商A所有的供货记录DeleteFromSCWhereSidin(Select

18、SidFromSupplyWherePNAME=“供应商A”),SQL的数据修改功能：更新操作,Update命令指定对哪些列进行更新，以及更新后的值是什么命令格式：Update表名set列名=表达式|子查询列名=，表达式|子查询Where条件表达式示例采购产品的数量上调5%UpdateSCsetQuantity=Quantity*1.05,数据定义语言(DDL),关系的模式.关联每个属性的值域完整性约束每个关系中的索引.每个关系在磁盘上的物理存储信息.,域定义,域类型（SQL-92）char（n）：固定长度的字符串varchar（n）：可变长字符串int：整数smallint：小整数类型num

19、eric（p，d）：定点数，共p位（包括小数点），右边d位real：浮点数doubleprecision：双精度浮点数date：日期（年、月、日）time：时间（小时、分、秒）Timestamp:日期和时间interval：两个date或time类型数据之间的差,基本表的定义,基本表的定义（Create）格式Createtable表名（列名数据类型default缺省值notnull，列名数据类型default缺省值notnull，primarykey（列名，列名），foreignkey（列名，列名）references表名（列名，列名），check（条件）,基本表的定义修改,修改基本表定义（A

20、lter）更改、添加、除去列和约束格式：Altertable表名add子句增加新列和约束Drop子句删除列和约束modify子句修改列定义示例AltertableSupplyaddbankNamechar(100),基本表的定义撤销,撤消基本表定义（Drop）格式Droptable表名作用是在数据字典中(metadata)中删除表的定义删除表定义及该表的所有数据、索引、触发器、约束和权限规范。基于该表建立的View不可用,视图定义,视图是用户一级的数据观点，视图提供了一定程度的逻辑数据独立性简化了用户观点。数据库的全部结构是复杂的,并有多种联系数据的安全保护功能。定义视图命令格式Createv

21、iewview_name(列名，列名)as（查询表达式）视图的属性名缺省为子查询结果中的属性名，也可以显式指明；撤消视图命令格式Dropviewview_name,视图定义示例,定义用户和购买产品的视图CreateViewCustomer_partasSelectCName,Part,QuantityFromSC,CustomerWhereSC.cid=Customer.Cid定义客户基本信息视图CreateViewCustomer_BasicasSelectCid,CNameFromCustomer,对视图操作,对视图的查询操作和基本表一样。如：SelectCName，PartFromCus

22、tomer_partWhereCNAMElike北京%；更新操作有所不同,一般只有对“行列子集视图”才能更新。如果视图是从单个基本表只使用选择、投影操作导出的，并且包含了基本表的主键，那么这样的视图称为“行列子集视图”，且可以被执行更新操作。Customer_Basic是行列子集视图,索引的定义,索引的定义定义命令格式Createuniqueclusterindex索引名on表名(列名asc/desc,列名asc/desc)unique：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。cluster：聚集索引，表中元组按索引项的值排序并物理地

23、聚集在一起。一个基本表上只能建一个聚集索引删除命令格式DropIndex索引名DropIndex语句不适用于通过定义PrimaryKey或Unique约束创建的索引，它们必须通过除去约束来撤销,索引的有关说明,索引的作用提高查询速度定位元组表连接实现数据约束主码PrimaryKey候选码CandidateKey可以动态地定义索引，即可以随时建立和删除索引一般不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性应该在使用频率高的、经常用于连接的列上建索引一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率,SQL中的权限,

24、数据访问的权限Readauthorization允许读取，但是不允许修改数据.Insertauthorization插入权限,不允许修改.Updateauthorization允许修改，不允许删除Deleteauthorization允许删除数据模式的权限Indexauthorization创建和删除索引.Resourcesauthorization创建关系表，创建的用户自动拥有数据操作的所有权限Alterationauthorization增加和删除关系中的属性.Dropauthorization删除关系表.,SQL中授权相关命令,授权命令Grant表级权限on表名|视图名to用户withg

25、rantoption表级权限包括：Select,Update,Insert,Delete,index,alter,Drop,resource以及它们的总和all，其中对Select,Update可指定列名可以是:用户名public,代表所有合法用户或者用户名，角色名withgrantoption表示获得权限的用户可以把权限再授予其它用户示例GrantSelect,InsertonSCtoLimingwithgrantoption,SQL中授权相关命令,Revoke命令收回某个用户的操作权限命令格式Revoke表级权限on表名|视图名From用户，用户|public注意收回权限时，若该用户已将权

26、限授予其它用户，则也一并收回。示例RevokeInsertonSupplyFromLiming,主要内容,数据库基本知识SQL基本概念和内容SQL分析扩展,SQL分析扩展,扩展GroupBy子句支持在一个GroupBy字句中按照不同的标准进行聚集分析扩展分析功能支持排序支持滑动窗口查询支持扩展的聚集函数,扩展GroupBy子句,Rollup可生成在指定属性列表的每个前缀上分组聚集的并集Rollup命令格式SelectFromGroupbyRollup(column_list);Cube可生成在指定属性列表中所有属性列组合之上聚集的并集Cube命令格式SelectFromGroupbyCube(

27、column_list);,扩展GroupBy子句-Rollup,Rollup可生成在指定属性列表的每个前缀上分组聚集的并集示例：给定Staff(Division,Dept,ID,Salary),按照分区、部门、个人统计职工工资SelectDIVISION,DEPT,ID,SUM(SALARY)ASSUM,COUNT(*)ASCOUNTFromSTAFFGroupbyROLLUP(DIVISION,DEPT,ID),扩展GroupBy子句-Cube,Cube操作对指定属性集合的每个子集计算Groupby的并集例如:给定销售sales(item_name,color,size,number),考

28、虑查询Selectitem-name,color,size,sum(number)FromsalesGroupbycube(item-name,color,size)这将计算出sales关系的八个不同分组聚集的并集:(item-name,color,size),(item-name,color),(item-name,size),(color,size),(item-name),(color),(size),()其中()表示空的Groupby列表.对每个分组,未出现在分组中的属性在结果中为null.,基本分析句法,分析句法()OVER(PARTITIONBY,OrderbyASC|DESCNULLSFIRST|NULLSLAST)PartitionBy的作用是将数据分组，分组之后的数据执行分析函数OrderBy指定组内的数据排序NullsFirst(last)指定如何处理空值Function包括