第1章关系数据库语言SQL和Transact-SQL

1第1章关系数据库语言SQL和Transact-SQLSQLServer2000支持ANSISQL(标准SQL，简称SQL)，并将标准SQL扩展成为了更加实用的Transact-SQL。为了便于学习和实验，本章将标准SQL和Transact-SQL的语法对照列出，并给出了一些典型实例。1.1数据定义语言SQL的数据定义功能主要包括对基本表、索引和视图的定义与维护；Transact-SQL的数据定义功能包括数据库和数据库对象的创建与管理功能，数据库对象有基本表、索引、视图、缺省、规则、触发器和存储过程等。1.1.1标准SQL的数据定义语言SQL的基本表定义和维护功能使用基本表的定义、修改和删除三种语句实现。1.定义基本表SQL语言使用CREATETABLE语句定义基本表，定义基本表语句的一般格式为：CREATETABLE[库名](表名)(列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]][，…n][，表级完整性约束条件][，…n])；(1)SQL支持的数据类型不同的数据库系统支持的数据类型不完全相同。IBMDB2SQL支持的数据类型见表1-1，尽管表1-1中列出了许多数据类型，但实际上使用最多的是字符型数据和数值数据。因此，要求必须熟练掌握CHAR、INTEGER、SMALLINT和DECIMAL数据类型。表1-1IBMDB2SQL支持的主要数据类型类型表示类型说明SMALLINT半字长二进制整数。15bits数据INTEGER或INT全字长(四字长)整数。31bits数据数值型数据DECIMAL(p[，q])十进制数，共P位，其中小数点后q位。O≤q≤p，q=0时可省略不写FLOAT双字长浮点数CHARTER(n)或CHAR(n)长度为n的定长字符串字符型数据VARCHAR(n)最大长度为n的变长字符串GRAPHIC(n)长度为n的定长图形字符串特殊数据类型VARGRAPHIC(n)最大长度为n的变长图形字符串DATE日期型，格式为YYYY-MM-DD日期时间型TIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间2(2)列级完整性约束条件列级完整性约束是针对属性值设置的限制条件。SQL的列级完整性条件有以下4种：1)NOTNULL或NULL约束。NOTNULL约束是不允许字段值为空，而NULL约束是允许字段值为空。字段值为空的含义是该属性值“不详”、“含糊”或“无意义”。对于关系的主属性，必须限定是“N01NULL”，以满足实体完整性；而对于一些不重要的属性，例如学生的爱好、特长等，则可以不输入字段值，即允许为NULL值。2)UNIQUE约束。UNIQUE约束是惟一性约束，即不允许该关系的该列中出现有重复的属性值。3)DEFAULT约束。DEFAULT为默认值约束。将列中的使用频率最高的属性值定义为DEFAULT约束中的默认值，可以减少数据输入的工作量。DEFAULT约束的格式为：DEFAULT约束名默认值FOR列名4)CHECK约束。CHECK为检查约束。CHECK约束通过约束条件表达式设置列值应满足的条件。CHECK约束的格式为：CONSTRAINT约束名CHECK(约束条件表达式)列级约束的约束条件表达式中只涉及到一个列的数据。如果约束条件表达式涉及到多列属性，则它就成为表级的约束条件，应当作为表级完整性条件表示。(3)表级完整性约束条件表级完整性约束条件是指涉及到关系中多个列的限制条件。在上述的CHECK约束中，如果约束条件表达式中涉及到多列数据，它便为表级约束。表级约束有以下3种：1)UNIQUE约束。UNIQUE约束是惟一性约束。当要求列组的值不能有重复值时，就需要使用UNIQUE约束定义。2)PRIMARYKEY约束。PRIMARYKEY约束是实体完整性约束。PRIMARYKEY约束用于定义主码，它能保证主码的惟一性和非空性。PRIMARYKEY约束可直接写在主码后，也可按语法单独列出。PRIMARYKEY约束的语法为：CONSTRAINT约束名PRIMARYKEY[CLUSTERED](列组)其中，CLUSTERED短语为建立列组聚簇。3)FOREIGNKEY约束。FOREIGNKEY约束即外码和参照表约束，它用于定义参照完整性。FOREIGNKEY约束语法为：CONSTRAINT约束名FOREIGNKEY(外码)REFERENCES被参照表名(与外码对应的主码名)32．修改基本表SQL语言用ALTERTABLE语句来修改基本表，其一般格式为：ALTERTABLE表名[ADD(新列名数据类型[完整性约束][，…n])][DROP完整性约束名][MODIFY(列名数据类型[，…n])]；可以看出，基本表的修改有3种情况：(1)使用ADD子句增加新列当向表中增加新列和新的完整性约束时，需要使用ADD子句对表结构进行修改操作。【例1-1】向课程表中增加“学时”字段。ALTERTABLE课程ADD学时SMALLINT；(2)使用MODIFY子句修改列的原定义MODIFY子句主要用于加宽原列的宽度。尽管有些系统允许对列名和数据类型进行修改，但一般不允许这样做，以免丢失原表中的数据。(3)使用DROP子句删除指定的完整性约束条件【例1-2】删除学生表中对年龄的默认值的定义。ALTERTABLE学生DROPC1；3．删除基本表删除基本表语句的一般格式为：DROPTABLE表名；4．索引定义和维护在SQL语言中，建立索引使用CREATEINDEX语句，其一般格式为：CREATE[UNIQUE][CLUSTER]INDEX(索引名)ON表名(列名[次序][，列名[次序]]…)；其中：1)表名是要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。2)每个(列名)后面还可以用(次序)指定索引值的排列次序，次序可选ASC(升序)或DESC(降序)，缺省值为ASC。3)UNIQUE表示该索引的每一个索引值只对应惟一的数据记录。4)CLUSTER表示要建立的索引是聚簇索引。聚簇索引使基本表中数据的物理顺序与索引项的排列顺序一致。SQL语言使用DROPINDEX语句删除索引，其一般格式为：DROPINDEX索引名：5．视图的定义和维护SQL语言用CREATEVIEW来定义视图，其一般格式为：4CREATEVIEW视图名[(列名组)]AS子查询[WITHCHECKOPTION]；定义视图的格式中，有两点需要说明：(1)WITHCHECKOPTION选项选择项WITHCHECKOPTION表示在对视图进行UPDATE、INSERT和DELETE操作时，要保证操作的数据满足视图定义中的谓词条件。该谓词条件是视图子查询中的WHERE子句的条件。(2)组成视图的属性列名全部省略或者全部指定若省略了视图的各个属性列名，则该视图的属性为子查询中的SELECT子句的目标列。必须明确指定组成视图的所有列名的3种情况是：某个目标列不是单纯的属性名，而是集函数或列表达式；子查询中使用多个表(或视图)，并且目标列中含有相同的属性名；需要在视图中改用新的、更合适的列名。1.1.2Transact-SQL的数据定义语言1.创建和管理数据库(1)创建数据库创建数据库包括：定义数据库名；确定数据库文件及其大小；确定事务日志文件的位置和大小。创建数据库使用CREATEDATABASE语句，其语法为：CREATEDATABASE数据库名[ON[PRIMARY][(NAME=逻辑数据文件名，]FILENAME=’操作数据文件路径和文件名’[，SIZE=文件长度][，MAXSIZE=最大长度)][，FILEROWTH=文件增长率])[，…n]][LOGON([NAME=逻辑日志文件名，]FILENAME=’操作日志文件路径和文件名’[，SIZE=文件长度])[，…n]][FORRESTORE]数据库定义语句中包括以下4个方面的内容：1)定义数据库名。2)定义数据文件。在ON子句中：PRIMARY短语指明主文件名(．mdf)；NAME短语说明逻辑数据文件名；FILENAME短语指明物理数据文件的存储位置和文件名；SIZE短语说明文件的大小，数据库文件最小为1MB，默认值为3MB；MAXSIZE短语指明文件的最大空间；FILEROWTH短语说明文件的增长率，其默认值为10％。可以定义多个数据文件，默认第一个为主文件。3)定义日志文件。在LOGON子句中：NAME短语说明逻辑日志文件名；FILENAME短语指明日志文件的存储位置和文件名；SIZE短语指明日志文件的长度。可以定义多个日志文件。4)FORRESTORE子句说明能重建一个数据库，该重建的数据库用于数据恢复操作。5(2)选择数据库数据库的选择使用USE命令，其格式为：USE数据库名注意：在Transact-SQL中没有语法结束符号。语句结束后按Enter键，并另起一行输入GO语句，再按Enter键。否则语句不会执行。(3)删除数据库删除数据库的语法为：DROPDATABASE数据库名组2．定义表定义基本表的格式为：CREATETABLE表名(列名类型AS表达式[字段约束][，…][记录约束])上述格式有以下问题需要说明。(1)字段约束字段约束可以使用以下短语定义：1)[NOTNULL|NULL]：定义不允许或允许字段值为空。2)[PRIMARYKEYCLUSTEREDNONCLUSTERED]：定义该字段为主码并建立聚集或非聚集索引。3)[REFERENCE参照表(对应字段)]：定义该字段为外码，并指出被参照表及对应字段。4)[DEFAULT缺省值]：定义字段的缺省值。5)[CHECK(条件)]：定义字段应满足的条件表达式。6)[IDENTITY(初始值，步长)]：定义字段为数值型数据，并指出它的初始值和逐步增加的步长值。(2)记录约束记录约束的格式为：CONSTRAINT约束名约束式约束式主要有以下4种：1)[PRIMARYKEY[CLUSTEREDNONCLUSTERED](列名组)]：定义表的主码并建立主码的聚集或非聚集索引。2)[FOREIGNKEY(外码)REFERENCES参照表(对应列)]：指出表的外码和被参照表。3)[CHECK(条件表达式)]：定义记录应满足的条件。4)[UNIQUE(列组)]：定义不允许重复值的字段组。(3)数据类型SQLServer2000提供许多实用的数据类型，并具有定义用户数据类型的功能。表1-2中列出了SOLServer2000提供的主要数据类型。6表1-2SQLServer2000支持的主要数据类型类型表示类型说明Int全字长(四字节)整数，其中31bits表示数据，1位符号。取值范围为-214783648~2147483647Smallint半字长的整数，取值范围为-32768～32767Tinyint只占一个字节的正数，表示范围为0~255数值型数据Real4字节长的浮点数，最大精度为7位，取值范围为3.4E-38～3.4E+38Float(n)精度为n的浮点数，其精度n的可以为1～15，若忽略n则精度为15。最多占用字节数为8，表示范围为1.7E-308～1.7E+308ecimal(p[,q])十进制，共P位，q位小数，可用2～11个字节存放1-38位精度的数值CHAR(n)长度为n的定长字符串，最多可为255个字符字符型数据VarCHAR(n)最大长度为n的变长字符串型数据，最多可达到255个字符日期、时间型数据Datetime日期时间型数据，可存储1／1／1753~12／31／9999之间的日期时间，缺省表示为MMDDYYYYhhmmAM／PMSmalldatetime日期时间型数据，可表示1／1／1900～6／6／20