h第3章SQL标准语言.ppt

天津师范大学管理学院06/072,1,数据库原理,张庆一2007.2天津师范大学管理学院,天津师范大学管理学院06/072,2,第3章关系数据库标准语言SQL,3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6授权3.7游标*,天津师范大学管理学院06/072,3,3.1SQL概述,3.1.1SQL的发展3.1.2SQL的特点3.1.3SQL语言的基本概念,天津师范大学管理学院06/072,4,3.1.1SQL的发展,1关于SQLStructuredQueryLanguage称为SEQUEL语言，即结构化查询语言，简称SQL。一种介于关系代数与关系演算之间的语言。集四大功能于一体（定义、查询、操纵、控制）。关系数据库的标准语言(支持SQL的DBMS有：Oracle、Informix、Sybase、INGRES、DB2、SQLServer、VisualFoxPro、Access等。),天津师范大学管理学院06/072,5,3.1.1SQL的发展,2早期的实现70年代后期，Oracle公司发行了第一个采用SQL语言的商业化RDBMS。80年代中期，Sybase公司发行了它的RDBMS-SQLServer。SQL具有跨平台能力和客户/服务器计算功能。,天津师范大学管理学院06/072,6,3.1.1SQL的发展,3SQL语言的标准化SQL86(1)由ANSI（美国国家标准局）于1986年10月公布。(2)ISO（国际标准化组织）于1987年通过。(3)以后不断修改和完善，主要是：数据定义语言、数据操纵语言、模块化语言、嵌入式语法SQL89(1)ISO于1989年公布(2)增加简单的完整性描述语句,天津师范大学管理学院06/072,7,3.1.1SQL的发展,SQL92(1)ISO于1992年公布(2)也称SQL2(3)定义了3级SQL规范：初级、中级和高级(4)新的特点：可滚动的游标、动态SQL等。SQL99/SQL3(1)酝酿中的新标准，逐渐增加标准的文档(2)增加面向对象功能,天津师范大学管理学院06/072,8,3.1.2SQL的特点,1综合统一数据库系统的主要功能是通过数据库支持的数据语言来实现的。SQL语言集下列功能为一体。数据查询（DataQuery）数据控制DC（DateControl）数据定义DD（DateDefinition）数据操纵DM（DateManipulation）,天津师范大学管理学院06/072,9,3.1.2SQL的特点,SQL语言风格统一，可以独立完成数据库生命周期中的全部活动。这主要包括：定义关系模式、建立数据库维护数据库数据库重构数据更新数据查询数据库安全性控制,天津师范大学管理学院06/072,10,3.1.2SQL的特点,2高度非过程化使用SQL语言进行数据操作，只要提出“做什么”，而无须指明“怎么做”。大大减轻用户的负担，有利于提高数据独立性。,天津师范大学管理学院06/072,11,3.1.2SQL的特点,3面向集合的操作方式SQL语言采用集合操作方式，操作对象、查找结果是元组的集合，一次插入、删除、更新操作的对象也是元组的集合。,天津师范大学管理学院06/072,12,3.1.2SQL的特点,4以同一种语法结构提供两种使用方式SQL语言既可以是自含式语言(模块化语言)，又可以是嵌入式语言。在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。,天津师范大学管理学院06/072,13,3.1.2SQL的特点,自含式语言特点：面向所有用户使用，可以独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。嵌入式语言特点：针对应用程序员使用，SQL语句能够嵌如到高级语言（宿主语言）程序中，例如VB、VC语言。程序设计员可以在设计的程序中使用SQL。,天津师范大学管理学院06/072,14,3.1.2SQL的特点,5语言简捷、易学易用SQL语言功能强，语言简捷，完成核心功能的命令动词只有9个。SQL语言采用类英语命令，容易学、容易使用。不同的RDBMS在实现标准SQL的同时还做了扩充，支持的命令也有所不同。,天津师范大学管理学院06/072,15,3.1.3SQL语言的基本概念,SQL语言支持关系数据库三级模式结构。外模式对应于视图（View）和部分基本表（Basetable），模式对应于基本表，内模式对应于存储文件。基本表和视图均为关系。用户可用SQL语言对基本表和视图进行查询和其它操作。,天津师范大学管理学院06/072,16,3.1.3SQL语言的基本概念,基本表是本身独立存在的表，在SQL中一个关系对应一个表。一个（或多个）基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构是任意的，对用户是透明的。视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。,天津师范大学管理学院06/072,17,3.2数据定义,3.2.1数据定义语句3.2.2定义数据库3.2.3定义、删除和修改基本表3.2.4定义和删除索引3.2.5实际RDBMS语言的实现,天津师范大学管理学院06/072,18,3.2.1数据定义语句,关系数据库系统支持三级模式结构。其模式、外模式和内模式中的基本对象有表、视图和索引。因此，SQL的数据定义功能包括定义表、定义视图和定义索引。,天津师范大学管理学院06/072,19,3.2.2定义数据库,1数据库的一般概念在客户机/服务器数据库系统中，程序和数据库结构是两个主要的组成部分。程序是为基于客户机的用户访问数据库提供一个用户界面，而数据库结构是用来管理和存储服务器上的数据。设计数据库结构，就意味着规划、创建和维护许多相关的数据库组件。数据库组件主要包括数据库、表、数据库图表、索引、视图、存储过程和触发器等。,天津师范大学管理学院06/072,20,3.2.2定义数据库,在大型数据库中，数据库是一个容器，它将相应的对象容纳在该容器之中。通常数据库是由表、索引、参照关系、存储程序和触发器等对象组成的。在VFP中，数据库容器实际是一个特殊的表，表中记录了数据库中表的结构信息、索引表达式、参照关系表达式和存储程序等等。,天津师范大学管理学院06/072,21,3.2.2定义数据库,一般地，RDBMS可以同时支持许多数据库，每个数据库即可以存储与另一个数据库相关的数据，也可以存储不相关的数据。设计数据库需要考虑的问题（1）创建数据库规则（2）确认应用程序的类型（3）规范化（4）数据完整性（5）数据安全性（6）数据库性能考虑（7）维护,天津师范大学管理学院06/072,22,3.2.2定义数据库,2定义数据库命令格式：CREATEDATABASE功能：创建数据库。例3-1，建立一个教学管理数据库。CREATEDATABASETMDB说明：各个DBMS的格式变化很大，因此不再进行定义格式的扩充，具体查阅有关资料。实际创建数据库时，要先进行数据库设计；创建数据字典；创建关键字段。,天津师范大学管理学院06/072,23,3.2.3定义、删除和修改基本表,1定义基本表命令格式:CREATETABLE（列级完整性约束条件,列级完整性约束条件,）;,天津师范大学管理学院06/072,24,3.2.3定义、删除和修改基本表,讨论：建表的同时，可以根据需要定义与表有关的完整性约束条件。完整性约束条件存放在系统的数据字典中。用户操作表时，DBMS将自动检查该操作是否违反了规定的完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。,天津师范大学管理学院06/072,25,3.2.3定义、删除和修改基本表,关于标识符的定义说明表名由字母开头，大小写无关，最多30个字符，不能与已有表或视图重名，不能使用保留字。列名的要求同，同表中不允许有相同的列名。,天津师范大学管理学院06/072,26,3.2.3定义、删除和修改基本表,例3-2，在教学管理数据库TMDB中，建立一个学生表：Student(Sno,Sname,Ssxe,Sage,Sdept)其中学号不能为空，值为一；性别的默认值为“男”。CREATETABLEStudent(SnoCHAR(10)NOTNULLUNIQUE,SnameCHAR(10),SsexCHAR(2)DEFAULT男,SageINT,SdeptCHAR(10);（注：关于数据类型参考相关的DBMS规定。）,天津师范大学管理学院06/072,27,3.2.3定义、删除和修改基本表,约束(Constraint)指关系数据库中的对象，用以存放关于插入到一个表的某一列的数据的规则。用来确保库中数据的准确性和一致性的。约束的种类:非空(NOTNULL)约束主键(primarykey)约束唯一(unique)约束外键(foreignkey)约束校验(check)约束,天津师范大学管理学院06/072,28,3.2.3定义、删除和修改基本表,2修改基本表格式ALTERTABLEADD完整性约束DROPMODIFY；说明：ADD：增加新列DROP：删除完整性约束条件MODIFY：修改原有的列定义,天津师范大学管理学院06/072,29,3.2.3定义、删除和修改基本表,例3-3，在Student表中增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE;,天津师范大学管理学院06/072,30,3.2.3定义、删除和修改基本表,3删除基本表格式DROPTABLE例3-4，删除教学管理数据库TMDB中的Student表。DROPTABLEStudent删除时，要格外小心。表中的数据和此表上的索引都将自动被删除掉，而视图仍然保留，但已无法引用。,天津师范大学管理学院06/072,31,3.2.4定义和删除索引,1索引概述在数据库管理中，为加快从庞大的数据库中搜索所需要的数据，常在数据库中对表增加索引，来实现检索数据的速度。数据库中的索引是表中数据和相应存储位置的列表。按照索引的顺序与基本表的物理顺序是否相同，将索引分为：聚簇索引非聚簇索引,天津师范大学管理学院06/072,32,3.2.4定义和删除索引,聚簇索引指基本表中记录的物理顺序和索引顺序是相同的。非聚簇索引指基本表中记录的物理顺序和索引顺序是不相同的。每一个基本表中只能有一个聚簇索引，但是可以有多个非聚簇索引。,天津师范大学管理学院06/072,33,3.2.4定义和删除索引,索引是建立在列的上面，建立索引的原则是：（1）在经常需要搜索的列上建立索引。（2）在主键上建立索引。（3）在经常用在连接的列（外键）上建立索引。（4）在经常需要根据范围进行搜索的列上创建索引（5）在经常需要排序的列上建立索引。（6）在经常使用在WHERE子句中的列上面创建索引。,天津师范大学管理学院06/072,34,3.2.4定义和删除索引,建立索引可以提高系统的性能，其主要优点是：（1）通过创建唯一性索引，可以保证每一行数据的唯一性。（2）可以大大加快数据的检索速度。（3）加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。（4）在使用ORDERBY和GROUPBY子句进行检索时，可以减少查询中分组和排序的时间。（5）使用索引可以在查询过程中自动使用查询优化器，提高系统性能。,天津师范大学管理学院06/072,35,3.2.4定义和删除索引,索引建立过多也会有许多不利之处，其缺点是：（1）创建索引和维护索引耗费时间。（2）索引需要占用物理空间。（3）对表中的数据进行增加、删除和修改的时候，索引也需要动态地维护，这样将降低数据的维护速度。,天津师范大学管理学院06/072,36,2定义索引格式：CREATEUNIQUECLUSTERINDEXON(,)；说明表名：基本表的名字列名：建立索引的列名次序：指定索引值的排列次序，ASC升序、DESC降序，缺省为ASCUNIQUE：每一个索引值只对应唯一的数据记录CLUSTER：建立聚簇索引,3.2.4定义和删除索引,天津师范大学管理学院06/072,37,3.2.4定义和删除索引,例3-5，对教学管理数据库TMDB中的Student表中的学号。按升序建立唯一索引。CREATEUNIQUEINDEXSIONSNOONStudent(Sno);,例3-5，对教学管理数据库TMDB中的Student表中的学号。按升序建立唯一索引。CREATEUNIQUEINDEXSIONSNOONStudent(Sno);,天津师范大学管理学院06/072,38,3.2.4定义和删除索引,3删除索引格式DROPINDEX;例3-6，删除SIONSNO索引。DROPINDEXSIONSNO;讨论：索引建立后将由系统维护它，建立索引的目的是减少查询操作的时间。过多的索引将给系统带来负担，可以根据需要删除掉。,天津师范大学管理学院06/072,39,3.2.5实际RDBMS语言的实现,请查阅有关DBMS资料进行实践ORACLESQLServerVisualFoxProACCESS,天津师范大学管理学院06/072,40,3.3查询,3.3.1SQL查询语句格式3.3.2单表查询3.3.3连接查询3.3.4子查询块嵌套查询3.3.5并交差集合查询,天津师范大学管理学院06/072,41,3.3.1SQL查询语句格式,SQL查询语句的一般格式：SELECTALL|DISTINCTFROM,WHEREGROUPBYHAVINGORDERBYASC|DESC;其中：ALL：访问全部记录DISTINCT：访问无重复的记录,天津师范大学管理学院06/072,42,3.3.1SQL语句格式,以下是教学管理数据库TMDB中的三个表，将作为本章问题讨论的实践对象。学生表Student(Sno,Sname,Ssex,Sage,Sdept)课程表Course(Cno,Cname.Cpro,Ccredit)学生选课表SC(Sno,Cno,Grade),天津师范大学管理学院06/072,43,3.3.2单表查询,1投影查询（选择表中的若干列）例3-7，查询指定列，查询全体学生的学号、姓名和性别。SELECTSno,Sname,SsexFROMStudent;例3-8，查询全部列，查询全体学生的全部信息。SELECT*FROMStudent;,天津师范大学管理学院06/072,44,3.3.2单表查询,例3-8，查询经过计算的值，查询全体学生的学号、出生年份和所在系。SELECTSno,2007-Sage,ISLOWER(Sdept)FROMStudent;例3-9，列标题使用别名显示。SELECTSnoName,2007-SageBirthdayFROMStudent;,天津师范大学管理学院06/072,45,3.3.2单表查询,2选取查询（选择表中的若干元组）第一类情况：消除取值重复的元组关键字：DISTINCT第二类情况：选取满足条件的元组常用查询条件：（1）比较（4）字符匹配（2）确定范围（5）空值（3）确定集合（6）多重条件,天津师范大学管理学院06/072,46,3.3.2单表查询,第一类情况：消除取值重复的行例3-10，访问SC表，查询选修了课程的学生学号。SELECTDISTINCTSnoFROMSC;练习：访问SC表，查询选修了课程号为1或2的学生学号。（注意，不允许重复的学生出现。）,天津师范大学管理学院06/072,47,3.3.2单表查询,第二类情况：选取满足条件的元组(1)比较大小条件表达式：NOT比较运算符运算符：=,=,!=,!,!、=、=、ANY大于子查询结果中的某个值ALL小于子查询结果中的所有值ANY小于子查询结果中的某个值子查询结果返回集合值。,天津师范大学管理学院06/072,89,3.3.4子查询块嵌套查询,例3-42，查询其他系中比信息系某一学生年龄小的学生姓名和年龄。SELECTSname,SageFROMStudentWHERESageIS;本例的其他方法：采用集函数。,子查询结果为一个集合（19，18）,天津师范大学管理学院06/072,90,3.3.5并交差集合查询,SELECT语句的查询结果是元组的集合，多个结果集可以进行集合操作。集合操作主要包括：并（UNION）交（INTERSECT）差（MINUS）标准SQL与SQLServer2000中没有提供集合的交、差操作，只能使用其它方法实现。,天津师范大学管理学院06/072,91,3.3.5并交差集合查询,1并运算使用UNION运算必须做到：参加操作的各个结果表的列数必须相同。对应项的数据类型也必须相同。,天津师范大学管理学院06/072,92,3.3.5并交差集合查询,例3-43，查询计算机科学系的学生及年龄不大于19岁的学生，即查询计算机科学系的学生或年龄不大于19岁的学生。SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19;,天津师范大学管理学院06/072,93,3.3.5并交差集合查询,2交运算例3-44，查询计算机科学系的学生与年龄不大于19岁的学生的交集，即查询计算机科学系中年龄不大于19岁的学生。SELECT*FROMStudentWHERESdept=CSANDSage19;,天津师范大学管理学院06/072,95,3.4数据更新,3.4.1插入数据3.4.2修改数据3.4.3删除数据3.4.4更新与数据库的一致性问题,天津师范大学管理学院06/072,96,3.4.1插入数据,1插入单个元组语句格式INSERTINTO表名(属性列1，属性列2)VALUES(常量1，常量2);,天津师范大学管理学院06/072,97,3.4.1插入数据,例3-46，将一个新生记录插入到Student表中。INSERTINTOStudentVALUES(95020,陈东,男,18,IS);（注：VFP命令格式与此相同。）,天津师范大学管理学院06/072,98,3.4.1插入数据,2插入子查询的结果语句格式INSERTINTO表名(属性列1，属性列2)子查询；,天津师范大学管理学院06/072,99,3.4.1插入数据,例3-47，设数据库中存在Deptage(Sdept,Avgage)表，计算每个系学生的平均年龄，并把结果存入Deptage表中。INSERTINTODeptage(Sdept,Avgage)SELECTSdept,AVG(Sage)FROMStudentGROUPBYSdept;,天津师范大学管理学院06/072,100,3.4.2修改数据,1修改语句的一般格式UPDATE数据库名！表名SET列名=表达式,列名=表达式WHERE条件;功能：选取满足WHERE条件的元组，修改其指定的列。若WHERE省略，则修改所有元组的指定列。说明：子查询可以嵌套在UPDATE语句中。（注：VFP命令格式与此相同。）,天津师范大学管理学院06/072,101,3.4.2修改数据,2修改某一个元组的值例3-48，将学生95001的年龄改为22岁。UPDATEStudentSETSage=22WHERESno=95001;,天津师范大学管理学院06/072,102,3.4.2修改数据,3修改多个元组例3-49，将所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1;,天津师范大学管理学院06/072,103,3.4.2修改数据,4带子查询的修改语句例3-50，将计算机科学系全体学生的成绩置零。UPDATESCSETGrade=0WHERECS=(SELECTSdeptFROMStudent，SCWHEREStudent.Sno=SC.Sno);,天津师范大学管理学院06/072,104,3.4.3删除数据,1删除语句格式DELETEFROM数据库名！表名WHERE条件；功能：在指定的表中，将满足条件的元组删除，省略WHERE子句，则删除全部元组。（注：VFP格式与此相同，但功能是逻辑删除。）,天津师范大学管理学院06/072,105,3.4.3删除数据,2删除某一个元组的值例3-51，删除学号为95019的学生记录。DELETEFROMStudentWHERESno=95019;,天津师范大学管理学院06/072,106,3.4.3删除数据,3删除多个元组的值例3-52，删除所有的学生选课记录。DELETEFROMSC；,天津师范大学管理学院06/072,107,3.4.3删除数据,4带子查询的删除语句例3-53，删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERECS=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);,天津师范大学管理学院06/072,108,3.4.4更新与数据库的一致性问题,增、删、改操作只能对数据库中的一个表进行操作。一个表中的数据如果被删除，很可能涉及其它表中相关数据的删除。,天津师范大学管理学院06/072,109,3.4.4更新与数据库的一致性问题,例如，删除学号为95019的学生记录，就必须删除其选课记录。即两件事情必须同时做（一先一后不可以），否则数据处于不一致性状态，使参照完整性受到破坏。采取策略：系统自动地删除参照表中响应的元组。系统检查参照表中是否存在相应的元组，如果存在，则操作失败。（不同的DBMS提供了不同的实现策略）,天津师范大学管理学院06/072,110,3.5视图,3.5.1概述3.5.2建立视图3.5.3删除视图3.5.4查询视图3.5.5更新视图3.5.6视图的作用,天津师范大学管理学院06/072,111,3.5.1概述,视图是关系的基本类型之一，它是一个虚表。视图是从一个或几个基本表（或视图）导出的表。数据库中仅存放视图的定义，不存储对应的数据，数据仍然存储在原来的基本表中。视图一旦被定义，使用方法和基本表一样。但对视图的更新操作有一定的限制。,天津师范大学管理学院06/072,112,3.5.2建立视图,1建立视图语句格式CREATEVIEW（，）AS子查询WITHCHECKOPTION；说明：子查询可以是任意的SELECT语句，但通常不含有ORDERBY和DISTINCT子句。WITHCHECKOPTION表示对视图进行增、删、改时要保证相应的行满足视图定义中的谓词条件（子查询条件）。,天津师范大学管理学院06/072,113,3.5.2建立视图,视图的属性列名的选取规定一般对视图的属性要全部指定。但组成视图的属性列名也可以全部省略，这时隐含将子查询的结果列作为视图的诸字段。当使用子查询的结果列作为视图的诸字段时，下列情况需要指明组成视图的全部列名。某个目标不是单纯的属性名，而是集函数或列表达式。多表连接时出现了同名列名需要为某个列启用更合适的列名,天津师范大学管理学院06/072,114,3.5.2建立视图,2建立视图，视图的列名由子查询指定。例3-54，建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=IS注：视图的列名由子查询指定，即Sno,Sname,Sage。,天津师范大学管理学院06/072,115,3.5.2建立视图,3建立视图，并对视图的访问加谓词条件限制。例3-55，建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。CREATEVIEWIS_StudentAASSELECTSno,Sname,SageFROMStudentWHERESdept=ISWITHCHECKOPTION;这样，以后对该视图进行插入、修改和删除操作时，DBMS将自动加上Sdept=IS的条件。,天津师范大学管理学院06/072,116,3.5.2建立视图,4在多个基本表的基础上建立视图。例3-56，建立信息系选修了1号课程的学生的视图。CREATEVIEWIS_SI(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=1;,天津师范大学管理学院06/072,117,3.5.2建立视图,5带表达式的视图。例3-57，建立一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno,Sname,Sbrith)ASSELECTSno,Snmae,2007-SageFROMStudent;,天津师范大学管理学院06/072,118,3.5.2建立视图,6用带有集函数和GROUPBY子句的查询建立视图（分组视图）。例3-58，将学生的学号及其平均成绩定义为一个视图。CREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;,计算每个学生选课的平均成绩。,天津师范大学管理学院06/072,119,3.5.3删除视图,删除视图的语句格式DROPVIEW功能：将指定的视图从数据字典中删除。注意，视图删除后，由该视图导出的其它视图定义仍存在数据字典中，但该视图已经不可以访问，否则出错。例3-59，删除视图IS_S1。DROPVIEWIS_S1；,天津师范大学管理学院06/072,120,3.5.4查询视图,例3-60，在信息系学生的视图中找出年