Microsoft Word - 实验3_1_.doc

Transcription

1 实验 2 SQL 语言 (1) 实验目的 1. 熟悉标准 SQL 中的简单的 Select 语句和 DDL 语言 2. DML 语言和关系表的约束规范 3. 了解查询执行计划 加深理解关系代数理论 4. 了解数据库系统的数据字典 5. 熟悉 SQL Server 的相关工具的使用 实验素材 在本实验中, 定义了一个大学数据库 University, 其中包括 5 个表 (student, enrollment, course, offering, faculty): student:(stdssn,stdfirstname,stdlastname,stdcity,stdstate,stdmajor,stdclass,stdgpa,stdzip) enrollment:( OfferNo,StdSSN,EnrGrade) course:( CourseNo,CrsDesc,CrsUnits) offering:(offerno,courseno,offterm,offyear,offlocation,offtime,facssn,offdays) faculty:(facssn,facfirstname,faclastname,faccity,facstate,facdept,facrank,facsalary,facs upervisor,fachiredate,faczipcode) 注 : 上述定义中, 标注下划线的属性是表的主码 在表 faculty 中,FacSupervisor 是 faculty 的外码, 属于自引用关系 数据库的定义语句均在附件中 该数据库的 ER- 图为 : Student Offering Faculty StdSSN StdClass StdMajor StdGPA OfferNo OffLocation OffTime Teaches Has FacSSN FacSalary FacRank FacHireDate Accepts Supervises Registers Course Enrollment EnrGrade CourseNo CrsDesc CrsUnits 版权所有 RUC WBL 1

2 实验活动及其要求 1. 熟悉交互式 SQL 的环境, 了解常见的系统表 了解下列系统表的主要作用和主要属性列, Sysdatabases 存放系统的现有的数据库信息, 主要属性列有 :db,dbid,crdate Sysobjects 存放某个数据库中现有的数据库对象, 包括用户表 系统表 索引 视图 函数 触发器等 主要属性列有 :name xtype uid type 等 Syscolumns 存放某个数据库中所有的属性列 注意所有的关系表 ( 包括系统表 ) 的属性列都放在此表中 主要属性列有 :name id length 等 Sysconstraints 存放所在数据库的规范和约束 Sysfilegroups Sysfiles Sysforeignkeys Sysindexes sysindexkeys systypes sysreferences 要求 :a 知道这些系统表的作用, 普通用户常用哪些属性列 ;b 画出数据字典的结构图 也就是上述的这些关系表的引用 依赖关系 可以参考 数据库系统概论 P333 页 2. 设计有关 DDL 语句 根据实验素材中的概念模型, 设计相应的 DDL 语句, 各个表的属性的数据类型根据需要自己定义 要求 : 正确的描述了基本关系表的所有信息, 尤其是关系表间的完整性约束 能够在 create table 中, 指定特定的数据文件 ( 组 ) 关系表属主 适当的约束 (CONSTRAINT), 例如 PRIMARY KEY NOT NULL UNIQUE 等 能够保证参照完整性 FOREIGN KEY 指定某一个属性列的默认值(Default) 能够在 alter table 中, 指定增加新列和新的完整性约束条件, 删除指定的完整性约束条件, 修改原有列的定义, 包括修改列名和数据类型 思考 : 系统对于 Alter table 有特殊的限制吗? 为什么要有这样的限制? 能够在 create ( 或 Alter)index 中, 指定建立 ( 更改 ) 索引的类型,unique,cluster 等 如果建立多于一个的 cluster index, 会出现什么情况 3. 执行 DDL 语句, 并观察相关系统表的变化 要求 : 首先是能够正确地执行 然后观察该 DDL 语句的执行, 对系统表的影响 要仔细 全面, 尽量去发掘 DDL 操作对于系统表的影响 具体的讲, 例如建立表时, 要观察该表所在的数据库中的 Sysobjects Syscolumns 版权所有 RUC WBL 2

3 Sysconstraints Sysforeignkeys sysreferences 等系统表的变化, 增加了哪些内容, 这些 内容之间的有什么关系 当然对于不同的建表语句, 所影响到的系统表有所不一样 类似的, 对于其他的 DDL 语句, 也要求能够指出它的执行, 影响到哪些系统表 4. 数据操纵语言 DML 插入 更改 删除数据时, 应该设计一些非法的数据, 来验证关系表的完整性约束和规则 主要验证的完整性约束和规则有 : primary key 试着插入一个主码为空元组或插入具有相同主码值的元组 foreign key 验证外码什么时候可以为空 删除 更新被参照码时, 对外码有什么影响? Unique 试着插入具有相同主码值的元组 not null check references foreign key on delete { no action cascade set default set null } foreign key on update { no action cascade set default set null } 要求 : 对每一种情况都要进行一些合法和非法的操作 ; 对于错误的情况, 记录下来, 并说明原因 5. 熟悉简单的 Select 语句的使用 要求 : 能够熟练使用简单的 Select 语句 例如, 查看关系表的元组数 从单表中查看某一个 条件的元组 查看单表中的某些属性段等 实验内容 1. 熟悉交互式 SQL 的环境, 了解常见的系统表 Sysdatabases 答 : 该表中包含有 SQL Serve 各个数据库的基本信息, 利用 select 语句查询数据库得到如下图所示数据, 发现在 Sysdatabases 表中每个数据库对应一个元组, 其中存储了各个数据库的 name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version 属性列为 : 版权所有 RUC WBL 3

4 其具体含义为 : name 数据库的名称 dbid 数据库 ID sid 数据库创建者的系统 ID, 其中系统 ID 号为 0x01 mode 用于创建数据库时在内部锁定该数据库 status 状态位, 其中某些状态位可由用户使用 sp_dboption(read only dbo use only single user 等 ) 进行设置 可以同时打开多个位 例如 master 数据库的 status 为 24 = , 具体含义为 8 = trunc. log on chkpt; 16 = torn page detection 所有数字的含义经过查询 SQL Server 联机丛书得到 : 1 = autoclose; 使用 sp_dboption 设置 4 = select into/bulkcopy; 使用 sp_dboption 设置 8 = trunc. log on chkpt; 使用 sp_dboption 设置 16 = torn page detection, 使用 sp_dboption 设置 32 = loading 64 = pre recovery 版权所有 RUC WBL 4

5 128 = recovering 256 = not recovered 512 = offline; 使用 sp_dboption 设置 1024 = read only; 使用 sp_dboption 设置 2048 = dbo use only; 使用 sp_dboption 设置 4096 = single user; 使用 sp_dboption 设置 = emergency mode = autoshrink = cleanly shutdown status2 使用方法基本同上, 在此数据库中, 所有数据库的 status2 均为 = ANSI null default; 使用 sp_dboption 设置 = concat null yields null, 使用 sp_dboption 设置 = recursive triggers, 使用 sp_dboption 设置 = default to local cursor, 使用 sp_dboption 设置 = quoted identifier, 使用 sp_dboption 设置 = cursor close on commit, 使用 sp_dboption 设置 = ANSI nulls, 使用 sp_dboption 设置 = ANSI warnings, 使用 sp_dboption 设置 = full text enabled, 使用 sp_fulltext_database 设置 crdate 创建日期 reserved 留作以后使用 category 在本例中, 所有数据库的该项均被设置为 0, 表示未被设置 包含用于复制的信息位图 : 1 = 已发布 2 = 已订阅 4 = 合并已发布 8 = 合并已订阅 cmptlevel 数据库的兼容级别 本例中均为 80 filename 数据库主文件的操作系统路径和名称 version 创建数据库时使用的 SQL Server 代码内部版本号 仅供 SQL Server 工具在内部用于升级处理 Sysobjects 答 : 利用 select 语句查询数据库得到如下图所示数据, 发现在 Sysobjects 表中在数据库内创建的每个对象 ( 约束 默认值 日志 规则 存储过程等 ) 在表中占一行, 每一行的属性列包括 name, id, xtype, uid, info,status, base_schema_ver, repinfo, parent_obj, crdate, ftcatid, schema_ver, stats_schema_ver, type, userstat, sysstat, indexdel, refdate, version, delstrig, updtrig, seltrig, category, cache 属性列为 : 版权所有 RUC WBL 5

6 查询 SQL Server 联机丛书得到各个属性列的含义如下 : 列名 描述 name 对象名 Id 对象标识号 xtype 对象类型 可以是下列对象类型中的一种 : C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 版权所有 RUC WBL 6

7 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束 ( 类型是 K) RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束 ( 类型是 K) V = 视图 X = 扩展存储过程 uid 所有者对象的用户 ID info 保留 仅限内部使用 status 保留 仅限内部使用 base_schema_ver 保留 仅限内部使用 replinfo 保留 供复制使用 parent_obj 父对象的对象标识号 ( 例如, 对于触发器或约束, 该标识号为表 ID) crdate 对象的创建日期 ftcatid 为全文索引注册的所有用户表的全文目录标识符, 对于没有注册的所有用户表则为 0 schema_ver 版本号, 该版本号在每次表的架构更改时都增加 stats_schema_ver 保留 仅限内部使用 type 对象类型 可以是下列值之一 : C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 FN = 标量函数 IF = 内嵌表函数 K = PRIMARY KEY 或 UNIQUE 约束 L = 日志 P = 存储过程 R = 规则 RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程 userstat 保留 sysstat 内部状态信息 indexdel 保留 refdate 留作以后使用 version 留作以后使用 deltrig 保留 instrig 保留 版权所有 RUC WBL 7

8 updtrig seltrig category cache 保留 保留 用于发布 约束和标识 保留 Syscolumns 答 : 在每个数据库中都包含有一个 Syscolumns 表 例如利用 select 语句查询 master 数据库得到如下图所示数据, 发现在 Syscolumns 表中包含以下属性列 :name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset, bitpos, reserved, colstat, cdefault, domain, number, colorder, autoval, offset, collationid, language, status, type, usertype, printfmt, prec, scale, iscomputed, isoutparam, isnullable, collation, tdscollation 其具体作用为 : 列名 描述 name 列名或过程参数的名称 id 该列所属的表对象 ID, 或与该参数关联的存储过程 ID xtype systypes 中的物理存储类型 typestat 仅限内部使用 xusertype 扩展的用户定义数据类型 ID length systypes 中的最大物理存储长度 xprec 仅限内部使用 xscale 仅限内部使用 colid 列或参数 ID xoffset 仅限内部使用 bitpos 仅限内部使用 reserved 仅限内部使用 版权所有 RUC WBL 8

9 colstat 仅限内部使用 cdefault 该列的默认值 ID domain 该列的规则或 CHECK 约束 ID number 过程分组时 (0 表示非过程项 ) 的子过程号 colorder 仅限内部使用 autoval 仅限内部使用 offset 该列所在行的偏移量 ; 如果为负, 表示可变长度行 status 用于描述列或参数属性的位图 : 0x08 = 列允许空值 0x10 = 当添加 varchar 或 varbinary 列时,ANSI 填充生效 保留 varchar 列的尾随空格, 保留 varbinary 列的尾随零 0x40 = 参数为 OUTPUT 参数 0x80 = 列为标识列 type systypes 中的物理存储类型 usertype systypes 中的用户定义数据类型 ID printfmt 仅限内部使用 prec 该列的精度级别 scale 该列的小数位数 iscomputed 表示是否已计算该列的标志 : 0 = 未计算 1 = 已计算 isoutparam 表示该过程参数是否是输出参数 : 1 = 真 0 = 假 isnullable 表示该列是否允许空值 : 1 = 真 0 = 假 Sysconstraints 答 : 实际上只是一个视图, 每个数据库中对应一个, 例如在本例中,University 所对应的 sysconstraints 表的定义语句即为 : CREATE VIEW sysconstraints AS SELECT constid = convert(int, id), id = convert(int, parent_obj), colid = convert(smallint, info), spare1 = convert(tinyint, 0), status = convert(int, CASE xtype WHEN 'PK' THEN 1 WHEN 'UQ' THEN 2 WHEN 'F' THEN 3 WHEN 'C' THEN 4 WHEN 'D' THEN 5 ELSE 0 END + CASE WHEN info!= 0 -- CNST_COLUMN / CNST_TABLE THEN (16) ELSE (32) END + CASE WHEN (status & 16)!=0 -- CNST_CLINDEX 版权所有 RUC WBL 9

10 THEN (512) ELSE 0 END + CASE WHEN (status & 32)!=0 -- CNST_NCLINDEX THEN (1024) ELSE 0 END + (2048) -- CNST_NOTDEFERRABLE + CASE WHEN (status & 256)!=0 -- CNST_DISABLE THEN (16384) ELSE 0 END + CASE WHEN (status & 512)!=0 -- CNST_ENABLE THEN (32767) ELSE 0 END + CASE WHEN (status & 4)!=0 -- CNST_NONAME THEN (131072) ELSE 0 END + CASE WHEN (status & 1)!=0 -- CNST_NEW THEN ( ) ELSE 0 END + CASE WHEN (status & 1024)!=0 -- CNST_REPL THEN ( ) ELSE 0 END), actions = convert(int, 4096), error = convert(int, 0) FROM sysobjects WHERE xtype in ('C', 'F', 'PK', 'UQ', 'D') AND (status & 64) = 0 对于不同的数据库, 每一行的属性列均相同, 分别为 :name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset 查询 SQL Server 联机丛书得到其作用为 : 列名 constid 约束号 描述 版权所有 RUC WBL 10

11 id 拥有该约束的表 ID colid 在其上定义约束的列 ID, 如果是表约束则为 0 spare1 保留 status 位图指示状态 可能的值包括 : actions error 保留 保留 1 = PRIMARY KEY 约束 2 = UNIQUE KEY 约束 3 = FOREIGN KEY 约束 4 = CHECK 约束 5 = DEFAULT 约束 16 = 列级约束 32 = 表级约束 Sysfilegroups 答 : 每个数据库对应一个, 数据库中的每个文件组在表中占一行 主要包含以下属性列 : name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset 另外还有一组专门表示文件组的属性列 :groupid, allocpolicy, status, groupname 查询 SQL Server 联机丛书得到其主要作用为 : 列名 groupid 每个数据库的唯一组标识号 allocpolicy 保留 描述 版权所有 RUC WBL 11

12 status 0x8 = READ ONLY 0x10 = DEFAULT groupname Sysfiles 文件组的名称 答 : 每个数据库对应一个, 其中记录了 fileid, groupid, size, maxsize, growth, status, pref, name 数据库中的每个文件在表中占一行 该系统表是虚拟表, 不能直接更新或修改 在本例中,University 数据库的 sysfiles 表如下图所示 : 查询 SQL Server 联机文件得到 sysfiles 的作用为 : 列名描述 fileid 每个数据库的唯一文件标识号 groupid 文件组标识号 size 文件大小 ( 以 8 KB 页为单位 ) maxsize 最大文件大小 ( 以 8 KB 页为单位 ) 0 值表示不增长, 1 值表示文件应一直增长到磁盘已满 growth 数据库的增长大小 0 值表示不增长 根据状态的值, 可以是页数或文件大小的百分比 如果 status 包含 0x100000, 则 growth 是文件大小的百分比 ; 否则, 它是页数 status growth 值 ( 以兆字节 (MB) 或千字节 (KB) 为单位 ) 的状态位 0x1 = 默认设备 0x2 = 磁盘文件 0x40 = 日志设备 0x80 = 自上次备份后已写入文件 0x4000 = 由 CREATE DATABASE 语句隐性创建的设备 0x8000 = 在数据库创建过程中创建的设备 版权所有 RUC WBL 12

13 perf name filename 0x = 按百分比而不是按页数增长 保留 文件的逻辑名称 物理设备的名称, 包括文件的完整路径 Sysforeignkeys 答 : 每个数据库包含一个, 包含关于表定义中的 FOREIGN KEY 约束的信息 例如在本例中 University 数据库中的 sysforeignkeys 表如下图所示 : 其中包含了 constid, fkeyid, rkeyid, fkey, rkey, keyno 等属性列, 各个属性列的作用如下表所示 : 列名 描述 constid FOREIGN KEY 约束的 ID fkeyid 具有 FOREIGN KEY 约束的表对象 ID rkeyid 在 FOREIGN KEY 约束中引用的表对象 ID fkey 正在引用的列 ID rkey 已引用的列 ID keyno 该列在引用列列表中的位置 版权所有 RUC WBL 13

14 Sysindexes 答 : 数据库中的每个索引和表在表中各占一行 主要的属性列有 id : 其中记录了表的 ID 号 first : 指向第一页或根页的指针 indid : 索引 ID: 1 = 聚集索引 >1 = 非聚集 255 = 具有 text 或 image 数据的表条目 minlen : 最小行大小 keycnt : 键的数目 其所有属性列的作用如下表所示 : 列名 描述 id 表 ID( 如果 indid = 0 或 255) 否则为索引所属表的 ID status 内部系统状态信息 first 指向第一页或根页的指针 indid 索引 ID: 1 = 聚集索引 >1 = 非聚集 版权所有 RUC WBL 14

15 255 = 具有 text 或 image 数据的表条目 root 如果 indid >= 1 和 < 255,root 是指向根页的指针 如果 indid = 0 或 indid = 255,root 是指向最后一页的指针 minlen 最小行大小 keycnt 键的数目 groupid 在其上创建对象的文件组 ID dpages 如果 indid = 0 或 indid = 1,dpages 是已用数据页的计数 如果 indid = 255, 其设置为 0 否则是已用索引页的计数 reserved 如果 indid = 0 或 indid = 1,reserved 是分配给所有索引和表数据的页计 数 如果 indid = 255,reserved 是分配给 text 或 image 数据的页计数 否则是分配给索引的页计数 used 如果 indid = 0 或 indid = 1,used 是用于所有索引和表数据的总页数 如 果 indid = 255,used 是用于 text 或 image 数据的页计数 否则是用于索 引的页计数 rowcnt 基于 indid = 0 和 indid = 1 的数据级行计数 如果 indid = 255,rowcnt 设 置为 0 rowmodctr 对自上次更新表的统计后插入 删除或更新行的总数进行计数 xmaxlen 最大行大小 maxirow 最大非叶索引行大小 OrigFillFactor 创建索引时使用的起始填充因子值 不保留该值 ; 然而, 如果需要重新创 建索引但记不住当初使用的填充因子, 则该值可能很有帮助 reserved1 保留 reserved2 保留 FirstIAM 保留 impid 保留 索引实现标志 lockflags 用于约束经过考虑的索引锁粒度 例如, 对于本质上是只读的查找表, 可 以将其设置为仅进行表级锁定以使锁定成本减到最小 pgmodctr 保留 keys 组成索引键的列 ID 列表 name 表名 ( 如果 indid = 0 或 255) 否则为索引的名称 statblob 统计 BLOB maxlen 保留 rows 基于 indid = 0 和 indid = 1 的数据级行数, 该值对于 indid >1 重复 如果 indid = 255,rows 设置为 0 提供该列是为了向后兼容 sysindexkeys 答 : 每个数据库对应一个, 主要属性列有 id, indid, colid, keyno, 其主要作用如下表所示 : id indid colid 表 ID 索引 ID 列 ID 版权所有 RUC WBL 15

16 keyno 该列在索引中的位置 在本例中,University 数据库对应的 sysindexkeys 表为 : systypes 答 : 每个数据库对应一个, 对于每种系统提供数据类型和用户定义数据类型, 均包含一行信息 其属性列和本例中 University 数据库的 systypes 表如图所示 : 版权所有 RUC WBL 16

17 其作用如下表 : 列名 描述 name 数据类型名称 xtype 物理存储类型 status 仅限内部使用 xusertype 扩展用户类型 length 数据类型的物理长度 xprec 服务器所使用的内部精度 ( 不能在查询中使用 ) xscale 服务器所使用的内部小数位数 ( 不能在查询中使用 ) tdefault 对此数据类型进行完整性检查的存储过程的 ID domain 对此数据类型进行完整性检查的存储过程的 ID uid 数据类型创建者的用户 ID reserved 仅限内部使用 usertype 用户类型 ID variable 可变长度数据类型为 1; 否则为 0 allownulls 指出此数据类型的默认为空性 如果 CREATE 或 ALTER TABLE 指定 了为空性, 那么该值将替代此数据类型的默认为空性 type 物理存储数据类型 printfmt 保留 prec 此数据类型的精度级别 scale 此数据类型的小数位数 ( 根据精度 ) sysreferences 答 : 每个数据库对应一个, 记录了 FOREIGN KEY 约束定义到所引用列的映射 其属性列和 University 数据库的 sysreferences 表的内容如下所示 : 版权所有 RUC WBL 17

18 查询 SQL Server 联机丛书得到该表的作用如下表所示 : 列名 描述 constid FOREIGN KEY 约束的 ID fkeyid 引用表的 ID rkeyid 被引用表的 ID rkeyindid 涵盖被引用键列的被引用表上唯一索引的索引 ID keycnt 键中的列数 forkeys 仅限内部使用 refkeys 仅限内部使用 fkeydbid 保留 rkeydbid 保留 fkey1 fkey2 fkey3 fkey4 fkey5 fkey6 fkey7 fkey8 fkey9 fkey10 fkey11 fkey12 fkey13 fkey14 fkey15 fkey16 版权所有 RUC WBL 18

19 rkey1 rkey2 rkey3 rkey4 rkey5 rkey6 rkey7 rkey8 rkey9 rkey10 rkey11 rkey12 rkey13 rkey14 rkey15 rkey16 画出数据字典的结构图 也就是上述的这些关系表的引用 依赖关系 版权所有 RUC WBL 19

20 数据字典图 syscolumns name id xtype typestat xusertype length sysdatabases name dbid sid mode status status2 crdate reserved category cmptlevel filename version sysobjects name id xtype uid info status base_schema_ver replinfo crdate refdate sysconstraints sysforeignkeys sysindexes sysindexkeys constid id collid spare1 status actions error constid fkeyid rkeyid fkey rkey keyno id status first indid root minlen groupid rows id indid colid keyno sysreferences sysfilegroups systypes constid fkeyid rkeyid rkeyindid keycnt forkeys refkeys fkeydbid rkeydbid groupid allocpolicy status groupname sysfiles fileid groupid size maxsize growth status pref name filename name xtype status xusertype length xprec 注 :1 图中凡是从表名出发的箭头都是指向 syscolumns, 因为数据库中的每一列都保存在了 syscolumns 中 2 为了简单起见, 未列出表的所有属性 2. 设计有关 DDL 语句 答 : 根据 University 数据库中各表的主码和外码, 以及各表的属性列, 设计其 DDL 语句如下 : Student 表 : CREATE TABLE Student 版权所有 RUC WBL 20

21 ( StdSSN CHAR(11) NOT NULL, StdFirstName VARCHAR(50) NOT NULL, StdLastName VARCHAR(50) NOT NULL, StdCity VARCHAR(50), StdState CHAR(2), StdZip CHAR(10), StdMajor CHAR(6), StdClass CHAR(6), StdGPA DECIMAL(3,2) CONSTRAINT PKStudent PRIMARY KEY(StdSSN) ); Faculty 表 : CREATE TABLE Faculty ( FacSSN CHAR(11) NOT NULL, FacFirstName VARCHAR(50) NOT NULL, FacLastName VARCHAR(50) NOT NULL, FacCity VARCHAR(50) NOT NULL, FacState CHAR(2) NOT NULL, FacZipCode CHAR(10)NOT NULL, FacHireDate DATETIME, FacDept CHAR(6), FacSupervisor CHAR(11), CONSTRAINT PKFaculty PRIMARY KEY (FacSSN), CONSTRAINT FKFacSupervisor FOREIGN KEY (FacSupervisor) REFERENCES Faculty ) ; Enrollment 表 : CREATE TABLE Enrollment ( OfferNo INTEGER, StdSSN CHAR(11), EnrGrade DECIMAL(3,2) CONSTRAINT EnrGradeDefault DEFAULT 5.0, CONSTRAINT PKEnrollment PRIMARY KEY (OfferNo, StdSSN), CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo) REFERENCES Offering, CONSTRAINT FKStdSSN FOREIGN KEY (StdSSN) REFERENCES Student ) Course 表 : CREATE TABLE Course ( CourseNo CHAR(6), CrsDesc VARCHAR(250), CrsUnits SMALLINT, 版权所有 RUC WBL 21

22 CONSTRAINT PKCourse PRIMARY KEY(CourseNo), CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc) ) Offering 表 : CREATE TABLE Offering ( OfferNo INTEGER NOT NULL, CourseNo CHAR(6) NOT NULL, OffLocation VARCHAR(50), OffDays CHAR(6), OffTerm CHAR(6) NOT NULL, OffYear INTEGER NOT NULL, FacSSN CHAR(11), OffTime DATETIME, CONSTRAINT PKOffering PRIMARY KEY (OfferNo), CONSTRAINT FKCourseNo FOREIGN KEY (CourseNo) REFERENCES Course, CONSTRAINT FKFacSSN FOREIGN KEY (FacSSN) REFERENCES Faculty ) 问题 : 1 能够在 alter table 中, 指定增加新列和新的完整性约束条件, 删除指定的完整性约束条件, 修改原有列的定义, 包括修改列名和数据类型 思考 : 系统对于 Alter table 有特殊的限制吗? 为什么要有这样的限制? 答 :(1) 在 University 中 Course 表中指定增加新列, 列名为 CourseName, 其完整性约束条件为 not null, 其 SQL 语句为 alter table Course add CourseDept char(10) unique 执行完该语句后,Course 表中增加了一列 CourseName (2) 在 University 中 Course 表中删除 CourseName 的 not null 的完整性约束条件, 其 SQL 语句为 alter table Course drop unique (CourseName) (3) 修改原有列的定义, 包括修改列名和数据类型 alter table Course modify CourseNo char(8) (4) 试图删除一整列, 设计 SQL 语句如下 alter table Course drop CourseNo 语句分析正确, 但在执行时报处错误如下 : 服务器 : 消息 3728, 级别 16, 状态 1, 行 1 'CourseNo' 不是约束 版权所有 RUC WBL 22

23 服务器 : 消息 3727, 级别 16, 状态 1, 行 1 未能除去约束 请参阅前面的错误信息 删除失败, 但根据报错信息得知有可能是 SQL 语句使用错误, 查询 SQL Server 联机丛书后, 设计出如下语句 : alter table faculty drop column faccity 语句分析正确, 执行正确, 结果成功删除一列 结论 : 在 SQL Server 数据库中可以单独删除一整列 2 能够在 create ( 或 Alter)index 中, 指定建立 ( 更改 ) 索引的类型,unique,cluster 等 如果建立多于一个的 cluster index, 会出现什么情况 答 :(1) 建立一个简单的索引, 设计 SQL 语句如下 : create unique index Courseind on Course(CourseNo) 分析语句正确, 执行语句正确 (2) 建立多个简单索引, 设计 SQL 语句如下 : create unique index CourseNameind on Course(CourseName) create unique index CrsUnitsind on Course(CrsUnits) 分析语句正确, 执行语句正确 (3) 为了在 CourseNo 上建立 cluster index, 先利用 drop index 语句删除索引, 设计语句如下 : drop index Course.Courseind 执行后查询 sysindexes 表, 发现少了一行, 说明删除成功 (3) 建立一个 cluster index, 设计 SQL 语句如下 : create clustered index CourseNoind on Course(CourseNo) 建立 cluster index 成功 (4) 为验证是否可以建立多个 cluster index, 设计另一个 SQL 语句如下 : create clustered index CourseNameNoind on Course(CourseName) 分析语句正确, 但在执行时会报出以下错误 : 服务器 : 消息 1902, 级别 16, 状态 3, 行 1 不能在表 'Course' 上创建多个聚集索引 请在创建新聚集索引前除去现有的聚集索引 'PKCourse' 结论 : 在一个数据库中只能建立一个 cluster index, 因为聚集索引与数据的物理存储顺序保持一致, 而数据的物理存顺序只能有一个, 所以只能建立一个 cluster index 3. 执行 DDL 语句, 并观察相关系统表的变化 要求 : 首先是能够正确地执行 然后观察该 DDL 语句的执行, 对系统表的影响 要仔细 全面, 尽量去发掘 DDL 操作对于系统表的影响 具体的讲, 例如建立表时, 要观察该表所在的数据库中的 Sysobjects Syscolumns Sysconstraints Sysforeignkeys sysreferences 等系统表的变化, 增加了哪些内容, 这些内容之间的有什么关系 当然对于不同的建表语句, 所影响到的系统表有所不一样 类似的, 对于其他的 DDL 语句, 也要求能够指出它的执行, 影响到哪些系统表 答 : 版权所有 RUC WBL 23

24 (1) 因为在前面建立数据库时, 并未仔细观察数据库系统表的变化, 所以在此处先删除数据库, 为此设计 SQL 语句如下 : drop table enrollment drop table offering drop table course drop table faculty drop table student (2) 删除 University 数据库 然后新建 University 数据库 观察系统表, 发现 sysdatabases 表中新加入一行, 阴影部分为新加入行 然后利用 count(*) 语句统计 University 数据库中系统表的行数 设计 SQL 语句如下 : select count(*) from syscolumns 对于其他表类似 得到系统表行数如下 : 表名 行数 syscolumns 256 sysfilegroups 1 sysfiles 2 sysforeignkeys 0 sysindexes 31 sysindexkeys 47 sysobjects 21 sysreferences 0 systypes 26 版权所有 RUC WBL 24

25 syscolumns sysfilegroups sysfiles Sysforeignkeys sysindexes Sysindexkeys 版权所有 RUC WBL 25

26 sysobjects sysreferences systypes 当建立完所有的表后, 继续观察系统表变化, 统计新行数为 : 表名 行数 syscolumns 388 sysfilegroups 1 sysfiles 2 sysforeignkeys 5 sysindexes 37 sysindexkeys 54 sysobjects 37 sysreferences 5 systypes 26 发生变化的系统表为 syscolumns,sysforeignkeys,sysindexes,sysindexkeys,sysobjects, sysreferences, 其余的表未发生变化 各表的变化情况如下 : 4. 数据操纵语言 DML 插入 更改 删除数据时, 应该设计一些非法的数据, 来验证关系表的完整性约束和规则 主要验证的完整性约束和规则有 : 版权所有 RUC WBL 26

27 primary key 试着插入一个主码为空元组或插入具有相同主码值的元组 foreign key 验证外码什么时候可以为空 删除 更新被参照码时, 对外码有什么影响? Unique 试着插入具有相同主码值的元组 not null check references foreign key on delete { no action cascade set default set null } foreign key on update { no action cascade set default set null } 要求 : 对每一种情况都要进行一些合法和非法的操作 ; 对于错误的情况, 记录下来, 并说明原因 答 : 以 University 数据库为例 : (1) 验证 primary key, unique 和 check 条件 a. 首先插入一个合法元组, 设计 SQL 语句如下 : INSERT INTO Student VALUES ( ' ','WILLIAM','PILGRIM','BOTHELL', ,'WA','IS','SO',3.80) 执行成功, 成功插入一行 b. 插入一个主码为 NULL 的行, 设计 SQL 语句为 : INSERT INTO Student VALUES ( null,'william','pilgrim','bothell', ,'wa','is','so',3.80) 语句分析成功, 但执行时报出错误 : 服务器 : 消息 515, 级别 16, 状态 2, 行 1 无法将 NULL 值插入列 'StdSSN', 表 'University.dbo.Student'; 该列不允许空值 INSERT 失败 语句已终止 c. 尝试插入主码重复的行, 设计 SQL 语句为 : INSERT INTO Student VALUES ( ' ','WILLIAM-1','PILGRIM-1','BOTHELL', ,'WA','IS','SO',3.80) 语句分析成功, 但执行时报出错误 : 服务器 : 消息 2627, 级别 14, 状态 1, 行 1 违反了 PRIMARY KEY 约束 'PKStudent' 不能在对象 'Student' 中插入重复键 语句已终止 d. 在 Enrollment 表的设计中, 设置约束条件为 EnrGrade 的默认值为 5.0, 验证如下 : INSERT INTO Enrollment(OfferNo, StdSSN) VALUES ( 1234, ) 版权所有 RUC WBL 27

28 分析正确, 执行正确, 查看数据库, 设计 SQL 语句如下 : SELECT * FROM Enrollment WHERE OfferNo = 1234 and StdSSN = ; 得到如下结果, 在插入语句中并未指定 EnrGrade 属性列的值, 但在约束条件定义中指定该值默认为 5.0, 在结果中该值为 5.0 说明约束条件定义成功 : (2) 验证 foreign key 答 : a. 在 Offering 表中, 引用了 Faculty 表的 FacSSN 属性作为外码, 且 FacSSN 可以为 NULL, 以此为素材, 设计 SQL 语句如下 : INSERT INTO Offering VALUES(6534,'IS320','BLM302','MW','FALL',2002,null,'12/30/ :30:00') 分析执行均正确 为验证结果, 设计查询语句 : SELECT * FROM Offering WHERE OfferNo = 6534; 执行结果为 : 版权所有 RUC WBL 28

29 可以看到 FacSSN 的值为 NULL b. 在 Offering 表中, 引用了 Course 表的 CourseNo 属性作为外码, 且 CourseNo 不可以为 NULL, 以此为素材, 设计 SQL 语句如下 : INSERT INTO Offering VALUES(6777,null,'BLM302','MW','FALL',2002,null,'12/30/ :30:00') 分析正确后执行, 报出错误如下 : 服务器 : 消息 515, 级别 16, 状态 2, 行 1 无法将 NULL 值插入列 'CourseNo', 表 'University.dbo.Offering'; 该列不允许空值 INSERT 失败 语句已终止 c. 在 Course 表中, 主码 CourseNo 被 Offering 表所引用, 为验证参照完整性约束, 删除 Course 表中被引用的列, 为此设计 SQL 语句如下 : DELETE FROM Course WHERE CourseNo = 'IS320'; 但删除未成功, 系统报错如下 : 服务器 : 消息 547, 级别 16, 状态 1, 行 1 DELETE 语句与 COLUMN REFERENCE 约束 'FKCourseNo' 冲突 该冲突发生于数据库 'University', 表 'Offering', column 'CourseNo' 语句已终止 (3) 验证 NOT NULL 在 Offering 表中,OffTerm 属性列规定为 NOT NULL, 为验证是否正确, 设计 SQL 语句如下 : INSERT INTO Offering VALUES(7667,null,'BLM302','MW',null,2002,null,'12/30/ :30:00') 语句分析成功, 但执行时报错 : 服务器 : 消息 515, 级别 16, 状态 2, 行 1 无法将 NULL 值插入列 'OffTerm', 表 'University.dbo.Offering'; 该列不允许空值 INSERT 失败 语句已终止 结果说明 NOT NULL 约束不允许插入空值 版权所有 RUC WBL 29

30 (4)foreign key on delete { no action cascade set default set null } foreign key on update { no action cascade set default set null } 答 :a. 在 Offering 表的主码 OfferNo 被 Enrollment 表所引用, 以此为素材, 删除 Offering 表的一行, 设计 SQL 语句如下 : DELETE FROM Offering WHERE OfferNo = 4321; 语句分析成功, 执行失败, 失败原因如下 : 服务器 : 消息 547, 级别 16, 状态 1, 行 1 DELETE 语句与 COLUMN REFERENCE 约束 'FKOfferNo' 冲突 该冲突发生于数据库 'University', 表 'Enrollment', column 'OfferNo' 语句已终止 说明 SQL Server 默认为 no action 选项 b. 为此, 重新设计 Enrollment, 定义 Enrollment 表的 SQL 语句变为 : CREATE TABLE Enrollment ( OfferNo INTEGER NOT NULL, StdSSN CHAR(11) NOT NULL, EnrGrade DECIMAL(3,2), CONSTRAINT PKEnrollment PRIMARY KEY(OfferNo, StdSSN), CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo) REFERENCES Offering ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FKStdSSN FOREIGN KEY (StdSSN) REFERENCES Student ON DELETE CASCADE ON UPDATE CASCADE ) 然后继续执行删除语句 : DELETE FROM Offering WHERE OfferNo = 4321; 执行后发现 Offering 表中被删除了一行, 而 Enrollment 表中参照了属性 4321 的行全被删除 结果如下 : Offering 表 Enrollment 表 版权所有 RUC WBL 30

31 注 : 在 SQL Server 中不支持 SET NULL 和 SET DEFAULT 选项 实验体会 : 本次实验内容很多, 做起来也很费力, 尤其是各种 SQL 语句, 课本上的语句写到电脑上都能出错, 深刻体会到了微软的 SQL Server 2000 对标准的支持度实在是不高, 不过写完之后就发现其实还是挺简单的 实验中遇到的问题 : 尽管对实验的复杂性早有准备, 但实验中遇到的问题还是很多 首先是 ER 图和数据字典图的画法, 为此我还专门下载了微软的 Office Visio 2003, 发现还不如 Word 简单, 但画出的图好看多了 然后是数据库素材的选择, 我没有选择实验中提供的数据库, 而是选择了一个 University 数据库 数据库来源是一本数据库参考书 其中的实验数据也是从该数据库中导入的 虽说有些取巧, 但这样的数据量更大一些, 而且数据设计更合理一些, 做起实验来也更得心应手 在写 SQL 语句的时候, 遇到了好多麻烦, 有时明明是书上的语句, 但写到电脑上就是不对, 挺烦人的 最大的问题居然是我的电脑, 死机了 N 次, 最惨的一次写了两个多小时的东西全没了, 简直让人达到了崩溃的边缘 版权所有 RUC WBL 31