北京邮电大学软件工程研究生的课程---数据库设计开发-0-3

北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3SQL•自从20世纪80年代以来，SQL就是一个通用的、功能极强的关系数据库语言。现在，SQL语言正从关系形式（ANSISQL-92标准）转向对象-关系形式（ANSISQL-99标准，1999年颁布）。•SQL语言是1974年由Boyce和Chamberlin提出。1986年10月美国国家标准局(ANSl)批准了SQL作为关系数据库语言的美国标准。同年公布了SQL标准文本(简称SQL-86)。1987年6月国际标准化组织(ISO)也采纳了此标准。1989年，美国国家标准局（ANSI）采纳了新的规范SQL-89标准，取代SQL-86，同时SQL-89标准也被国际标准化组织(ISO)采纳。1992年，ANSI/ISO颁布了SQL2版本，标准的名称为SQL-92。SQL-92分称几个顺序级别：从代表SQL-89最小扩展集的“Entry”到“Intermediate”和“Full”。完成于己于1999年的SQL-99具有更加高级的特征（包括对象-关系特性），亦称SQL3。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3SQL•主要的几个数据库生产厂商并不可能完全遵守SQL-99（以及更老的SQL-92）。我们一般更加关注在产品中已经实现的SQL-99特征。1.3.1SQL特点1.3.2数据定义1.3.2.1SQL模式的创建和删除1.3.2.2SQL提供的基本数据类型1.3.2.3定义、删除与修改基本表1.3.2.4建立与删除索引1.3.2.5视图的创建和删除1.3.3数据查询1.3.3.1简单查询1.3.3.2子查询1.3.3.3UNION运算和FORALL条件1.3.3.4高级SQL语法1.3.3.5集合函数1.3.3.6行分组1.3.4数据更新1.3.4.1插入数据1.3.4.2修改数据1.3.4.3删除数据北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.1SQL特点•1)综合统一•2)高度非过程化•3)面向集合的操作方式•4)以同一种语法结构提供两种使用方式•5)语言简捷，易学易用和关系代数相比，就查询能力而言，SQL并没有根本的改进，在关系代数查询方面的经验可以成为用SQL来实现查询的良好借鉴。在构造查询时SQL的select语句比关系代数要灵活.北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2数据定义•SQL的数据定义功能包括对模式(Schema)、表(关系，Table)、视图(View)和索引(Index)的创建、删除和修改操作。如下表所示。操作对象操作方式创建删除修改模式CREATESCHEMADROPSCHEMA表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.1SQL模式的创建和删除•在SQL-99中，模式是表、索引及其他数据库对象的集合。模式名通常是一个用户名。在CoreSQL-99和当前大多数产品中，当用户的数据库帐户建立时，其模式在用户名之后给出，他们不能再建立其他模式。SQL-99的扩展特性允许用户建立附加模式，一个SQL模式由模式名和模式拥有者的用户名或账号来确定。•SQL模式的创建可用CREATE语句实现，其句法如下：CREATESCHEMA模式名AUTHORIZATION用户名目前只有DB2UDB允许用户建立附加模式。其它产品中模式用用户名替代。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.1SQL模式的创建和删除•在SQL中还有一个“目录”概念。目录是SQL环境中所有模式的集合。包含数据库中定义的对象的信息的表，由系统维护。ORACLE叫数据字典，DB2UDB叫目录表，INFORMIX叫系统目录。目录表在建立数据库时建立，用户不能更新，但DBA可以用select获取这些信息。•当一个SQL模式及其所属的基本表、视图等元素都不需要时，可以用DROP语句撤消这个SQL模式。DROP语句的句法如下：DROPSCHEMA模式名[CASCADE|RESTRICT]北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.2SQL提供的基本数据类型SQL-99ORACLEINFORMIXDB2UDB说明Cchar(n)char(n)n=4000char(n)n=32767char(n)n=254定长字符型chararray[n+1]varchar(n)varchar(n)varchar2(n)varchar(n)varchar(n)变长字符型chararray[n+1]numeric(p,d)decimal(p,d)numeric(p,d)decimal(p,d)number(p,d)numeric(p,d)decimal(p,d)numeric(p,d)decimal(p,d)定点数，由p位数字(不包括符号、小数点)小数点后面有d位数字无smallintsmallintsmallintsmallint短整数shortintintegerintegerintegerinteger长整数int,longintrealrealrealreal浮点数floatdoubleprecision,float,float(n)doubleprecision,number,floatfloat(n)doubleprecision,floatdoubleprecision,double,float,float(n)取决于机器精度的双精度浮点数至少为n位精度double北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.3定义、删除与修改基本表•建立数据库最重要的一步就是定义一些基本表。SQL语言使用CREATETABLE语句定义基本表，一般格式如下：CREATETABLE表名(列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件])；•例:CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY,SnameCHAR(8)NOTNULL,SageSMALLINTCHECK(SageBETWEEN17AND22),SsexCHAR(2)CHECK(SsexIN(’男’,’女’)),SdeptCHAR(20)DEFAULT(‘软件学院’));创建学生表:Sno为主键(非空唯一),Sname非空,Sage在17到20之间取值,Ssex只能取‘男’或‘女’,Sdept默认值为‘软件学院’.北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.3定义、删除与修改基本表•实际使用时要有用户ID和密码,进入交互式环境，才能完成数据库操作。•修改基本表ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][MODIFY列名数据类型];其中表名是要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIPY子句用于修改原有的列定义，包括修改列名和数据类型。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.3定义、删除与修改基本表•删除基本表DROPTABLE表名基本表一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定要格外小心。•注意：有的系统，如Oracle，删除基本表后建立在此表上的视图定义仍然保留在数据字典中。但是，当用户引用时就报错。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.4建立与删除索引•索引的功能表现在以下3方面。(1)使用索引可以明显地加快数据查询的速度(2)使用索引可保证数据的唯一性(3)使用索引可以加快连接速度•建立索引的原则(1)索引的建立和维护由DBA和DBMS完成(2)大表应当建索引，小表则不必建索引(3)对于一个基本表，不要建立过多的索引(4)根据查询要求建索引北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.4建立与删除索引•CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[asc|desc[，列名[asc|desc]])；•如果数据增加删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。DROPINDEX索引名；北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.5视图的创建和删除•视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图之上再定义新的视图，但对视图的更新(增加、删除、修改)操作则有一定的限制。•CREATVIEW视图名[(列名[,列名]...)]AS子查询[WITHCHECKOPTION]；其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT短语。WITHCHECKOPTION表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.2.5视图的创建和删除•DROPVIEW视图名；视图删除后视图的定义将从数据字典中删除。但是由该视图导出的其他视图定义仍在数据字典中，不过该视图已失效。用户使用时会出错，要用DROPVIEW语句将它们一一删除。•DBMS执行CREATEVIEW语句的结果只把视图的定义存入数据字典，并不执行其中的SELECT。在关系数据库中，并不是所有的视图都是可更新的,因为有些视图的更新不能的有意义的转换成对基本表的更新。行列子集视图是可更新的。各个DBMS对视图的更新有自己的规定。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.3数据查询•数据查询是数据库的核心操作。SQL语言的数据查询只有一条SELECT语句:SELECT[ALL|DISTINCT]{*|目标列表达式[[as]别名][，目标列表达式[[as]别名]]...}FROM表名或视图名[，表名或视图名]…[WHERE条件表达式][GROUPBY列名[，列名][HAVING条件表达式]]][ORDERBY列名[ASC|DESC][，列名[ASC|DESC]]…];北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.3数据查询•Select语句的执行过程可以理解为：首先，对FROM子句中的所有表做关系乘积接着，删除不满足WHERE子句的行根据GROUPBY子句对剩余的行进行分组然后删除不满足HAVING子句的组求出SELECT子句选择列表的表达式的值若有关键词DISTINCT存在，则删除重复的行•Select中的标识符：一般的SQL标识符是大小写无关的。实际上SQL在解释以前会把它们转化为大写形式。一个标识符必须以一个字母打头，EntrySQL-92和CoreSQL-99将一个标识符字节数限制在18个以内。北京邮电大学软件学院郭文明2003.06《数据库设计与开发》讲义1.3.3数据查询•表达式可以是数值表达式、字符串表达式和日期表达式等。数值表达式由常数、表属性、算术运算符、算术函数所组成。字符串表达式由常数、表属性、字符串运算符、字符串函数所组成。日期表达式由常数、表属性、日期运算符、日期函数所组成•WHERE中使用谓词来表示条件。一般情况谓词运算结果为TRUE或FALSE,但如果遇到空值时,