第3章_SQL语言

第三章SQL语言3.1SQL概述SQL（StructuredQueryLanguage）是结构化查询语言的缩写。虽然叫查询语言，但它的功能已经远远超出了查询，是一种集多种功能为一体的关系数据库标准语言。1974年IBM公司SanJose实验室的Boyce和Chamberlin首先提出了SQL。随后，SQL在IBMSystemR上的关系数据库SQL/DS得到了实现。1986年美国国家标准协会（ANSI）公布了第一个SQL标准，即SQL-86，并于1987年被国际标准化组织（ISO）采纳为国际标准。经过不断修改和完善，1989年ISO公布了SQL-89国际标准，1992年公布了SQL-92国际标准，也称为SQL2。目前正在进行SQL3新的标准化工作。SQL语言是一种高度非过程性的关系数据库语言，采用的是集合的操作方式，操作的对象和结果都是元组的集合，用户只需知道“做什么”，无需知道“怎么做”，因此SQL语言接近英语自然语言、结构简洁、易学易用。同时SQL语言集数据查询、数据定义、数据操纵、数据控制为一体，功能强大，得到了越来越广泛的应用，几乎所有著名的关系数据库系统如DB2、Oracle、MySql、Sybase、SQLServer、FoxPro、Access等都支持SQL语言。SQL已经成为关系数据库的国际性标准语言。SQL语言主要有四大功能：(1)数据定义语言(DataDefinitionLanguage，简称DDL)，DDL用于定义数据库的逻辑结构，是对关系模式一级的定义，包括基本表、视图及索引的定义。(2)数据查询语言(DataQueryLanguage，简称DQL)，DQL用于查询数据。(3)数据操纵语言(DataManipulationLanguage，简称DML)，DML用于对关系模式中的具体数据的增、删、改等操作。(4)数据控制语言(DataControlLanguage，简称DCL)，DCL用于数据访问权限的控制。SQL语言的功能及用于实现功能的9个动词见图3.1。表3.1SQL的四大功能及9个动词SQL功能动词数据定义(DDL)CREATE,DROP,ALTER数据查询(DQL)SELECT数据操纵(DML)INSERT,UPDATE,DELETE数据控制(DCL)GRANT,REVOKE3.2SQL数据定义SQL支持关系数据库的三级模式结构，其中外模式对应视图和部分基本表，模式对应基本表，内模式对应存储文件，如图3.2所示。在关系数据库中，关系就是表，表又分成基本表(BaseTable)和视图(View)两种，它们都是关系。基本表是实际存储在数据库中的表，是独立存在的。视图是从其它表(包括其它视图)中导出的表，它仅是一种逻辑定义保存在数据字典中，本身并不独立存储在数据库中，因此视图是一种虚表。一个基本表对应一个或多个存储文件，一个存储文件可以存放一个或多个基本表，一个基本表可以有若干个索引，索引同样存放在存储文件中。SQL视图v1视图v2基本表b1基本表b2基本表b3基本表b4存储文件f1存储文件f2存储文件f3外模式模式内模式图3.1SQL支持的关系数据库的三级模式结构3.2.1基本表1.定义基本表SQL语言定义基本表的一般格式为：CREATETABLE表名(列名数据类型[列级完整性约束],…[,表级完整性约束])；这里①表名为基本表的名称，列名为表中属性的名称，一般用有含义的英文或拼音。②每个属性都有数据类型，不同的关系数据库系统所支持的数据类型不完全相同，但一般都支持如图3.3的SQL2的一些主要数据类型。表3.2SQL2的一些主要数据类型数据类型说明CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT或INTEGER长整数(全字长)SMALLINT短整数(半字长)NUMBER(x,y)定点数FLOAT(n)浮点数，精度至少为n位DOULEPRECISION双精度浮点数DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SS③列级完整性约束仅作用在该列上，表级完整性约束是作用在整个表的，这些完整性约束条件都保存在系统的数据字典中，并由DBMS自动检查。例3.1定义一个学生表S(Snum,Sname,sex,birthday)，其中Snum是学号，Sname是姓名，sex是性别，birthday是出生年月。Snum是主码，不能为空值且是唯一。解：CREATETABLES(SnumCHAR(5)NOTNULLUNIQUE,SnameCHAR(8),sexCHAR(2),birthdayDATE,PRIMARYKEY(Snum));这里定义的学生表有四个属性，其中NOTNULL和UNIQUE是列级完整性约束，作用在Snum上，NOTNULL要求Snum不能为空值，UNIQUE要求Snum取值唯一，而PRIMARYKEY(Snum)是表级完整性约束，定义该表的主码为Snum，由于主码本身就是唯一且不能为空，因此Snum上的NOTNULL和UNIQUE是冗余的，可以省略。该表中前面三个属性都是定长的字符串，但长度不同，而birthday是日期型，长度为8。列级完整性约束一般都可以用表级完整性约束来替代，但反之则不一定。上例可以改为：CREATETABLES(SnumCHAR(5),SnameCHAR(8),sexCHAR(2),birthdayDATE,PRIMARYKEY(Snum),CONSTRAINTuni_SnumUNIQUE(Snum));该定义与上一个定义在作用上是一样的，这里只是把Snum上的UNIQUE列级完整性约束转移到了表级上，使用了CONSTRAINT子句表示约束，其中uni_Snum是完整性约束名。数据库系统在执行以上的SQL定义语句后，就在数据库中创建了一个新的学生表S。而S表的定义及有关的完整性约束条件存放在数据字典中。例3.2定义一个课程表C(Cnum,Cname,credit)和一个选修表SC(Snum,Cnum,grade)，其中Cnum是课程号，Cname是课程名称，credit是学分，Snum是学号，grade是成绩。Cnum是C表的主码，(Snum,Cnum)是SC表的主码。要求学分credit可以是空值或者是0到10之间的定点数。而SC表中的Cnum和Snum分别是C表和S表的外码。解：CREATETABLEC(CnumCHAR(4),CnameCHAR(20),creditNUMBER(2,1),PRIMARYKEY(Cnum),CHECK((creditISNULL)OR(creditBETWEEN0AND10)));CREATETABLESC(SnumCHAR(5),CnumCHAR(4),gradeSMALLINT,PRIMARYKEY(Snum,Cnum),FOREIGNKEY(Snum)REFERENCESS(Snum),FOREIGNKEY(Cnum)REFERENCESS(Cnum));通常使用CHECK子句来保证属性值满足某些条件，CHECK子句可以很复杂，甚至可以内嵌SELECT语句。而FOREIGNKEY(Snum)REFERENCESS(Snum)说明该表的Snum为S表的外码。例3.3定义一个教师表teacher(Tnum,Tname,sex,birthday,dept,title,address)，其中Tnum是教师号，Tname是姓名，sex是性别，birthday是出生年月，dept是系，title是职称，address是地址。Tnum是主码。解：CREATETABLEteacher(TnumCHAR(5),TnameCHAR(8),sexCHAR(2),birthdayDATE,deptVARCHAR(20),titleCHAR(6),addressVARCHAR(30),PRIMARYKEY(Tnum));2.修改基本表在数据库的实际应用中，随着应用的环境和需求的变化，经常需要修改基本表的结构，包括修改属性列的宽度和数据类型、增加新的属性列、增加新的约束条件、删除原有的约束条件等。SQL语言修改基本表的一般格式为：ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][ALTERCOLUMN列名数据类型]；这里①表名是已存在的且要修改的基本表。②ADD子句用于增加新列。但新属性不能定义为NOTNULL，因为表在新增加一列后，原来的元组在新增的列上的值都取为空值。③DROP子句用于删除指定的完整性约束条件。④MODIFY子句用于修改原有的列名和数据类型。例3.4在已存在的学生表S中增加一个dept（系）的新的属性列。解：ALTERTABLESADDdeptCHAR(20)；要注意的是，新增加的属性列的值都是空值，因为其值是不确定的。例3.5删除学生表S中Snum上的完整性约束。解：ALTERTABLESDROPUNIQUE(Snum)；例3.6修改学生表S中的name的数据类型。解：ALTERTABLESALTERCOLUMNnameVARCHAR(8)；修改表中原有的列的定义可能会破坏已有的数据。要注意的是，在SQL语言中没有删除属性列的语句，不能试图用DROP去删除属性列。用户只能间接实现这个功能，先创建一个新表，该表保留了除要删除的属性列以外的原表的属性列，然后用INSERT和SELECT语句把原表的内容复制到新表中，再用DROP语句删除原表。3.删除基本表SQL语言删除基本表的一般格式为：DROPTABLE表名；例3.7删除临时表temp。解：DROPTABLEtemp；要注意的是，基本表一旦删除，则表中的数据及与此表相关的索引和视图都将自动被删除。3.2.2索引建立索引的目的是为了加快查询的速度。索引是属于物理存取路径的概念，而不属于逻辑数据模式的概念。在早期的SQL86和SQL89中，关系还没有码(Key)的概念，因此只能用索引来解决查询速度的问题。但是定义关系的同时还要定义索引，实际上是把数据库的物理结构和逻辑结构混在一起，这在理论上是有缺陷的。因此SQL2抛弃了索引的概念，直接使用主码的概念。在这里介绍的是早期SQL标准中的索引机制。1.建立索引SQL语言建立索引的一般格式为：CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名[次序]]…)；这里①对一个基本表可以建立若干个索引，以提供多条存取路径。②索引可以建在一个或多个列上，次序可选项为ASC(升序)或DESC(降序)，缺省值为ASC。③UNIQUE可选项，使用户可指明索引是否为唯一性索引，唯一性索引的列不能有相同的值。④CLUSTER可选项，表示该索引为聚簇索引。聚簇索引是指索引的顺序与基本表中的物理顺序一致的索引。一般在最常查询的列上建立聚簇索引，以提高查询效率。一个基本表上最多只能建立一个聚簇索引。但聚簇索引也有明显的缺点，经常要更新的属性列是不适合建立聚簇索引的，因为代价比较大。⑤索引建立以后，使用和维护是由数据库系统自动进行的，不需要用户干预。⑥索引也有副作用，在相应基本表更新数据时会增加开销。例3.8在学生表S的姓名列Sname上建索引解：CREATEINDEXidx_snameONS(Sname)；例3.9在选修表SC的课程号Cnum和成绩grade上建索引，要求课程号为主索引且为升序，课程号相同时成绩为降序。解：CREATEINDEXidx_sc_gONSC(Cnum,gradeDESC);2.删除索引SQL语言删除索引的一般格式为：DROPINDEX索引名；例3.10在学生表S上的索引idx_sname。解：DROPINDEXidx_sname;3.2.3视图视图是由基本表或其它视图导出的虚表。数据库中只存放视图的定义，视图并不存放数据，用户在视图中看到的数据存放在相关的基本表中。视图定义之后，可以和基本表一样被用户查询