实验 3 _ SQL 语言 (1) 实验报告 03CS 31311060 周燕红 一 实验目的 1. 熟悉标准 SQL 中的简单的 Select 语句和 DDL 语言 2. DML 语言和关系表的约束规范 3. 了解查询执行计划 加深理解关系代数理论 4. 了解数据库系统的数据字典 5. 熟悉 SQL Server 的相关工具的使用 二 实验素材 考虑一个简单的数据库, 在这个数据库中, 只有供应商 零件和项目这三个实体, 下面给出 这个数据库的概念模型 其中主码用下划线标出 实体有 : 供应商 (S) 属性有 S# SNAME STATUS CITY 项目 (J) 属性有 J# JNAME CITY 零件 (P) 属性有 P# PNAME COLOR WEIGHT CITY 这三个实体之间的联系为 : 供应商给项目提供多少零件, 可以用 SPJ 来表示, 它的属性有 : S# P# J# QTY 三 实验活动及其要求 ( 一 ) 熟悉交互式 SQL 的环境, 了解常见的系统表 了解下列系统表的主要作用和主要属性列, 要求 :a 知道这些系统表的作用, 普通用户常用哪些属性列 ;b 画出数据字典的结构图 也就是下列关系表的引用 依赖关系 (1) 该部分实验均在 SQL 查询分析其中进行, 对于各表的相关内容 ( 如表中各个属性列的数据结构等 ) 可以通过察看对象浏览器获得, 在此不作具体说明 关于系统表的详细说明, 及它们的各个属性列的含义在 SQL Server 联机丛书中均能找到, 在此就不详细叙述了, 而仅仅通过简单的 select 语句, 看直观的看看这些表的相关信息 Sysdatabases 表该表在 master 数据库下, 存放系统的现有的数据库信息 其中普通用户常用的属性列有 :name( 即 dbname),dbid,crdate status 打开 SQL 查询分析器, 在 master 数据库下, 输入查询语句 : select* from sysdatabases 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 仅给出表的部分列 )
Sysobjects 表该表存放某个数据库中现有的数据库对象, 包括用户表 系统表 索引 视图 函数 触发器等 同时该表中也给出了, 那些表中带有完整性约束的属性列的相关信息 其中, 普通用户常用的属性列有 :name id xtype uid type 等如在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from sysobjects 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 仅给出表的部分列 ) Syscolumns 表该表存放某个数据库中所有的属性列 注意所有的关系表 ( 包括系统表 ) 的属性列都放在此表中 其中, 普通用户常用的属性列有 :name id xsertype length 等如在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from syscolumns 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 仅给出表的部分列 ) Sysconstraints 表该表存放所在数据库的规范和约束 包含约束映射, 映射到拥有该约束的对象 其中, 普通用户常用的属性列有 :constid id colid status actions 等如在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from sysconstraints 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :??? 实验中的疑惑 : 在对象浏览器下, 展开 master 数据库的系统表, 并没有找到该系统表 sysconstraints, 但这个查询却能执行成功, 并返回相应的结果
Sysfilegroups 表 该表存放所在数据库中的系统文件分组情况的相关信息 其中, 常用的属性列有 groupid groupname status 如在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from sysfilegroups 分析查询语句后执行, 则可得到该表的内容, 如下表所示 : Sysfiles 表该表存放所在数据库的系统文件的相关信息 其中, 常用的属性列有 fileid groupid name 等 如在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from sysfiles 分析查询语句后执行, 则可得到该表的内容, 如下表所示 : Sysforeignkeys 该表存放所在数据库中各表中所使用的外码的相关信息, 包含关于表定义中的 FOREIGN KEY 约束的信息 其中, 常用的属性列有 constid fkeyid rkeyid fkey 等 在 msdb 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from sysforeignkeys 分析查询语句后执行, 则可得到该表的内容, 如下表所示 : Sysindexes
该表存放所在数据库中建立的各索引的相关信息 其中, 常用的属性列有 id status indid minlen dpages 等 在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from Sysindexes 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 仅给出部分属性列 ) Sysindexkeys 表该表存放所在数据库中建立的各索引关键字的相关信息 其中, 常用的属性列有 id indid colid keyno 在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from Sysindexkeys 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 部分 ) Systypes 表该表存放所在数据库中数据类型的相关信息 对于每种系统提供数据类型和用户定义数据类型, 均包含一行信息 其中, 常用的属性列有 name xtype xusertype length 等 在 master 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from systypes 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 部分 )
Sysreferences 该表存储在每个数据库中, 包括 FOREIGN KEY 约束定义到所引用列的映射 其中, 常用的属性列有 constid fkeyid rkeyid 等 在 msdb 数据库下 ( 也可以选择其它数据库 ), 输入查询语句 : select* from Sysreferences 分析查询语句后执行, 则可得到该表的内容, 如下表所示 :( 部分 ) (2) 数据字典结构图如下 : Sysdatabases name dbid crdate Sysobjects name id xtype Syscolumns name Id length systypes name xtype xusertype length Sysindexes Id Status Indid sysreferences constid fkeyid rkeyid Sysconstraints constid id colid status sysindexkeys id Indid colid keyed Sysforeignkeys constid fkeyid rkeyid fkey Sysfiles fileid groupid name Sysfilegroups groupid groupname status
( 二 ) 设计有关 DDL 语句 根据实验素材中的概念模型, 设计相应的 DDL 语句, 各个表的属性的数据类型根据需要自己定义 要求 : A 正确的描述了基本关系表的所有信息, 尤其是关系表间的完整性约束 B 能够在 create table 中, 指定特定的数据文件 ( 组 ) 关系表属主 适当的约束 (CONSTRAINT), 例如 PRIMARY KEY NOT NULL UNIQUE 等 能够保证参照完整性 FOREIGN KEY 指定某一个属性列的默认值(Default) 为满足 A B 两个条件, 设计以下 create table 语句 : D1 供应商(S) 属性有 S# SNAME STATUS CITY CREATE TABLE S ( S# CHAR(5) PRIMARY KEY CLUSTERED, ------- 保证实体完整性约束同时建立聚簇索引 SNAME CHAR(20) CONSTRAINT CON_SNAME UNIQUE, STATUS NUMERIC(5) DEFAULT 0, ---- 指定该属性列的默认值 CITY CHAR(15) ) ON [PRIMARY]; ------------------------------- 指定存储表的文件组 D2 项目(J) 属性有 J# JNAME CITY CREATE TABLE J ( J# CHAR(6), JNAME CHAR(20) CONSTRAINT CON_JNAME UNIQUE, CITY CHAR(15), PRIMARY KEY (J#) ) ON [PRIMARY]; D3 零件(P) 属性有 P# PNAME COLOR WEIGHT CITY CREATE TABLE P ( P# CHAR(5), PNAME CHAR(20), COLOR CHAR(6), WEIGHT NUMERIC(5,1) DEFAULT 0 CONSTRAINT CON_W CHECK(WEIGHT>=0), PRIMARY KEY (P#) ) ON [PRIMARY]; D4 这三个实体之间的联系为 : 供应商给项目提供多少零件, 可以用 SPJ 来表示, 它的属性有 :S# P# J# QTY CREATE TABLE SPJ ( S# CHAR(5), P# CHAR(5), J# CHAR(6), QTY NUMERIC(5) DEFAULT 0, ------------------ 指定该属性列的默认值
PRIMARY KEY(S#,P#,J#), ---------------------------- 保证实体完整性约束 FOREIGN KEY (S#) REFERENCES S, ------------- 参照完整性 FOREIGN KEY (P#) REFERENCES P, ------------- 参照完整性 FOREIGN KEY (J#) REFERENCES J ) ------------ 参照完整性 ON [PRIMARY]; ------------------------------------ 指定存储表的文件组 C 能够在 alter table 中, 指定增加新列和新的完整性约束条件, 删除指定的完整性约束条件, 修改原有列的定义, 包括修改列名和数据类型 思考 : 系统对于 Alter table 有特殊的限制吗? 为什么要有这样的限制? 为满足 C 条件, 设计以下 alter table 语句 : D5 ALTER TABLE S ADD PHONE CHAR(8) CONSTRAINT CON_P UNIQUE; --- 给表 S 添加属性列 PHONE, 并指定它的完整性约束 D6 ALTER TABLE S DROP CON_SNAME; --- 删除表 S 上列 SNAME 的 UNIQUE 约束其中 CON_NAME 为约束名 D7 ALTER TABLE S DROP COLUMN CITY; -- 删除一列, 经过实验证明,SQL 提供了删除属性列的语句, 这与书本讲的有点出入 实验中的问题 : 没有能够实现修改列名以及列的数据流类型这一操作 实验中, 我发现系统对 Alter Table 语句有如下限制 : 1 例如当输入语句 :ALTER TABLE S ADD PHONE CHAR(8) NOT NULL; 该语句系统是无法执行的, 原因如下 : ALTER TABLE 只允许添加可包含空值或指定了 DEFAULT 定义的列 因为列 'PHONE' 不能包含空值且未指定 DEFAULT 定义, 所以无法将该列添加到表 'S' 中 2 在删除列操作 drop column 时, 不能删除定义了约束条件的列, 如表 S 的 NAME 列 该列上定义了 UNIQUE 约束, 如果想删除这一列, 必须先删除该约束, 再删除这一列 D 能够在 create ( 或 Alter)index 中, 指定建立 ( 更改 ) 索引的类型,unique,cluster 等 如果建立多于一个的 cluster index, 会出现什么情况 设计以下 DDL 语句 : D8 CREATE UNIQUE INDEX IXsname ON S (SNAME ASC); --- 为表 S 按学生姓名 SNAME 建立唯一索引 D9 DROP INDEX S.IXsname; -- 删除索引经过实验得出 : 1 SQL 中除提供 create index 和 drop index 操作外, 并不提供修改索引 (ALTER INDEX) 操作, 用户如果想修改索引, 则只能先将它们删除掉, 然后重新建 2 创建一个表格时, 系统会自动在主码上建立以各 cluster 索引, 也可以在表的创建语句中显示的给出, 如表 S 的创建语句 D1, 就指定了主码 S# 为 clustered 3 根据聚簇索引的定义 : 指索引项的顺序与表中记录的物理顺序一致的索引组织 则可以得出结论, 一个基本表上最多只能建立一个聚簇索引 否则, 如果在表中的
两个属性列上分别建立 cluster index, 则会导致表中的纪录不知按照哪种物理顺序存放 ( 三 ) 执行 DDL 语句, 并观察相关系统表的变化 要求 : 首先是能够正确地执行 然后观察该 DDL 语句的执行, 对系统表的影响 要仔细 全面, 尽量去发掘 DDL 操作对于系统表的影响 具体的讲, 例如建立表时, 要观察该表所在的数据库中的 Sysobjects Syscolumns Sysconstraints Sysforeignkeys sysreferences 等系统表的变化, 增加了哪些内容, 这些内容之间的有什么关系 当然对于不同的建表语句, 所影响到的系统表有所不一样 类似的, 对于其他的 DDL 语句, 也要求能够指出它的执行, 影响到哪些系统表 实验步骤 : 1 打开 SQL Server 企业管理器, 新建一个名为 mysimple 数据库 打开 SQL 查询分析器, 在 mysimple 数据库下, 执行上述 DDL 语句 2 执行上述设计的 create table 语句 D1 D2 D3 D4 来创建 S P J SPJ 表 执行上述语句 D1 D2 D3 后, 均会导致 Sysobjects Syscolumns Sysconstraints 等系统表的变化, 在此仅以 S 表的创建为例, 来说明 create table 语句对系统表 Sysobjects Syscolumns Sysconstraints 引起的变化 ; 而执行语句 D4, 即 SPJ 表的创建语句, 则会导致 Sysobjects Syscolumns Sysconstraints Sysforeignkeys sysreferences 等系统表的变化, 在此以 SPJ 表的创建为例来说明 create table 语句对于系统表 Sysforeignkeys sysreferences 引起的变化 : 1 在执行 D1 语句后, 即 S 表建立, 使用查询语句 :select* from sysobjects, 可以看出该表中增加了关于表 S 以及带有完整性约束的属性列的一些记录, 如下表中的 20 23: 2 在执行语句 D1 后, 即 S 表建立, 使用查询语句 :select* from syscolumns, 可以看出该表中增加的表 S 的属性列的信息, 如下表中的 247 250: 3 在执行语句 D1 后, 即 S 表建立, 使用查询语句 :select* from sysconstraints, 可以看出, 该表从原来的空表变成一下状态, 而增加的内容就是有关表 S 的一些约束信息 :
4 在执行语句 D4 后, 即 SPJ 表被建立, 使用查询语句 select* from sysforeignkeys, 可以看出, 该表从原来的空表变成以下状态, 增加了关于表 SPJ 中外码的一些信息 : 5 在执行语句 D4 后, 即 SPJ 表被建立, 使用查询语句 select* from Sysreference, 可以看出, 该表从原来的空表变成以下状态, 增加了关于表 SPJ 中定义的参照完整性的相关信息 : 3 执行上述设计的 alter table 语句 D5 来给表 S 增加列 PHONE, 由于该属性列上定义 UNIQUE 约束, 因此执行该语句后, 会引起表 Sysobjects Syscolumns Sysconstraints 的变化 : 1 在执行 D5 语句后, 使用查询语句 :select* from sysobjects, 可以看出该表中增加了关于表 S 中属性列 PHONE 的一些记录, 如下表中的 31 行 : 2 使用查询语句 :select* from syscolumns, 可以看出该表中增加的表 S 的属性列 PHONE 的信息, 如下表中的 259 行 : 3 使用查询语句 :select* from sysconstraints, 可以看出, 增加了一个约束项, 即表 S 上属性 PHONE 的 UNIQUE 约束 4 执行上述设计的 alter table 语句 D6(ALTER TABLE S DROP CON_NAME), 即在表 S 上删除列 NAME 上定义的 UNIQUE 约束, 执行该语句后, 会引起表 Sysobjects Sysconstraints 的变化 : 1 使用查询语句 :select* from sysobjects, 可以原来存在的关于属性列 NAME 上的约束 CON_NAME 的一些记录没有了 :
2 使用查询语句 :select* from sysconstraints, 可以看出, 少了一个约束项, 即表 S 上属性 NAME 的 UNIQUE 约束 5 执行上述设计的 alter table 语句 D7(ALTER TABLE S DROP COLUMN CITY) 删除表 S 的列 CITY, 则会引起 Syscolumns 表的变化 : 在该表中会失去关于该列的记录 6 执行上述设计的 create index 语句 D8(CREATE UNIQUE INDEX IXsname ON S (SNAME ASC)), 以及 drop index 语句 D9 均会引起系统表 Sysobjects Sysindexes Sysindexkeys 的变化 ( 四 ) 数据操纵语言 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 } 要求 : 对每一种情况都要进行一些合法和非法的操作 ; 对于错误的情况, 记录下来, 并说明原因 实验中用到的 DML 语句如下 : 1A INSERT INTO S(S#,SNAME,STATUS,CITY) VALUES('S1',' 精益 ',20,' 天津 '); 该语句执行成功, 返回结果 : 所影响的行数为 1 1B INSERT INTO S(S#,SNAME,STATUS,CITY) VALUES('S1',' 求精 ',30,' 天津 '); 该语句执行不成功, 原因 : 违反了 PRIMARY KEY 约束 'PK S 2C3393D0' 不能在对象 'S' 中插入重复键 2 当外码不为主属性时可以为空, 如当输入语句 INSERT INTO SPJ(S#,P#,J#,QTY) VALUES( null,'p1','j1','200'); 时, 系统不能执行 删除 更新被参照码时, 如果这个码值被参照了, 则不允许删除 / 更新, 除非先删除那个参照值, 再才能删除 / 更新这个被参照的码值 3A INSERT INTO S(S#,SNAME,STATUS,CITY) VALUES('S2',' 盛锡 ',10,' 北京 '); 该语句执行成功, 返回结果 : 所影响的行数为 1 3B INSERT INTO S(S#,SNAME,STATUS,CITY) VALUES('S3',' 盛锡 ',60,' 北京 '); 该语句执行不成功, 原因 : 违反了 UNIQUE KEY 约束 'CON_SNAME' 不能在对象 'S' 中插入重复键 4A INSERT INTO P VALUES('P1',' 螺母 ',' 红 ',12); 语句执行成功, 返回结果 : 所影响的行数为 1 4B INSERT INTO P VALUES('P2',' 螺母 ',' 红 ',-1); 该语句执行不成功,INSERT 语句与 COLUMN CHECK 约束 'CON_W' 冲突, 因为要求 WEIGHT>=0. 5 INSERT INTO J VALUES('J1',' 三建 ',' 北京 ');
INSERT INTO J VALUES('J2',' 一汽 ',' 长春 '); INSERT INTO J values('j3',' 弹簧厂 ',' 天津 '); INSERT INTO J values('j4',' 造船厂 ',' 天津 '); INSERT INTO P values('p2',' 螺栓 ',' 绿 ',17); 执行成功 6 INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S1','P1','J1','200'); INSERT INTO SPJ VALUES( 'S1','P1','J3','100'); INSERT INTO SPJ VALUES( 'S1','P1','J4','700'); 执行成功 7 UPDATE SPJ SET S#='S11' WHERE P#='P1'; 该语句执行不成功, 因为 UPDATE 语句与 COLUMN FOREIGN KEY 约束 'FK SPJ S# 398D8EEE' 冲突 该冲突发生于数据库 'mysimple', 表 'S', column 'S#' 因此此时,S 表中 S# 并不具有值为 S11 的记录 8 UPDATE S SET S#='S101' WHERE SNAME=' 精益 '; 该语句执行不成功, 因为 UPDATE 语句与 COLUMN REFERENCE 约束 'FK SPJ S# 398D8EEE' 冲突 该冲突发生于数据库 'mysimple', 表 'SPJ', column 'S#' 即 SPJ 表中有元组参照了 S 表的这个属性值 9 DELETE FROM S WHERE S#='S1'; 该语句执行不成功, 因为 DELETE 语句与 COLUMN REFERENCE 约束 'FK SPJ S# 398D8EEE' 冲突 该冲突发生于数据库 'mysimple', 表 'SPJ', column 'S#' 即 SPJ 表中有元组参照了 S 表的这个属性值 ( 五 ) 熟悉简单的 Select 语句的使用要求 : 能够熟练使用简单的 Select 语句 例如, 查看关系表的元组数 从单表中查看某一个条件的元组 查看单表中的某些属性段等 这里仅给出两个简单的选择语句, 而更多选择语句将会在 SQL 实验 2 中进行测试 实验中用到的 SELECT 语句如下 : 1 SELECT COUNT(*) FROM SPJ; 查看 SPJ 表的元组数的, 查询结果如下 : 2 SELECT S#,P# FROM SPJ WHERE QTY=100; 查看 SPJ 表中满足条件 QTY=100 的元组的属性段 S#,P# 查询结果如下: ( 六 ) 实验心得与体会在这次的实验中, 总体来看对 SQL 中的数据字典有些更深入的了解, 这也为我们学习与理解 SQL 的各种语言提供了很好的帮助 除此之外, 对于 SQL 的 DDL DML 语句也有了更为深刻的了解 但好像 SQL2000 中的一些语言结构同书本上给出的还是有些出入, 因此在实验中也遇到了一些困难, 有几种功能我还是没有能够实现, 比如修改表中列的属性等