第1关定义表时创建索引任务描述本关任务在“学生选课”数据库上按要求执行操作体会聚集索引和非聚集索引的不同。相关知识为了完成本关任务你需要掌握定义表时创建索引。定义表时创建索引基本语法CREATE TABLE 表名(属性列名 1 数据类型 [列级完整性约束条件][,属性列名 2 数据类型 [列级完整性约束条件]…][,表级完整性约束条件]);在基本表定义语句中为主键添加 PRIMARY KEY[CLUSTERED | NONCLUSTERED] 列级完整性约束条件或添加 PRIMARY KEY[CLUSTERED | NONCLUSTERED](属性列名组) 表级完整性约束条件可在定义关系表的主键的同时建立基于该主键的聚集或非聚集索引。其中CLUSTERED 创建聚集索引NONCLUSTERED 创建非聚集索引。系统会自动创建以主键约束名命名的索引与主键约束绑定。不同 DBMS 的默认选项不同。在 SQL Server 中默认在创建表的同时基于主键创建聚集索引用户也可修改系统的默认配置。有的 DBMS 则不支持用户在定义表的同时基于主键显式创建索引而是自动基于主键创建聚集索引。示例创建表并添加主键约束自动创建主键索引CREATE TABLE [dbo].[YourTableName]([ID] [int] IDENTITY(1,1) NOT NULL,[ColumnName1] [nvarchar](50) NOT NULL,[ColumnName2] [nvarchar](50) NULL,CONSTRAINT [PK_YourTableName] PRIMARY KEY CLUSTERED([ID] ASC))在这个例子中[ID] 列被设置为主键并且自动创建了一个聚集索引 PK_YourTableName 。编程要求根据提示在右侧编辑器 Begin-End 内补全代码在“学生选课”数据库上依次执行如下操作体会聚集索引和非聚集索引的不同。这里假设系统默认在关系表 S 上基于主键 Sno 创建了聚集索引。用 CREATE TABLE 语句创建一个新关系表 S1 表结构与关系表 S 相同在创建表的同时基于主键 Sno 创建非聚集索引将关系表 S 的数据插入关系表 S1 中可用带子查询的 INSERT 语句实现并执行 SELECT * FROM S1 查询执行 SELECT * FROM S 查询并与对关系表 S1 的查询显示结果做对比在两个表中分别插入两个相同的新元组先插人的元组的学号要大于后插入的元组的学号再执行前面对两个表的查询操作观察插入的两个新元组在显示结果中所处的位置说明原因用 CREATE TABLE 语句再创建一个新关系表 S2表结构与关系表 S 相同但不定义表的主键并将关系表 S 的数据插人关系表 S2 中分别在关系表 S、S1 和 S2 上查询学号为 S10 的学生信息比较查询的执行算法和估计代价向关系表 S、S1 和 S2 中插入相同的元组重复步骤 ⑦ 中的操作了解索引在查询中的作用。use 学生选课 GO --***********BEGIN********* -- 1. 创建新关系表 S1结构与 S 相同并为 Sno 创建非聚集索引 CREATE TABLE S1 ( Sno Char(5) not null PRIMARY KEY, Sname Varchar(10) not null, Ssex Char(2), Sdateofbirth DATE, SD Varchar(10), ); CREATE NONCLUSTERED INDEX idx_sno ON S1(Sno); -- 2. 将 S 的数据插入 S1 INSERT INTO S1 (Sno, Sname, Ssex, Sdateofbirth, SD) SELECT Sno, Sname, Ssex, Sdateofbirth, SD FROM S; -- 3. 执行 SELECT 查询 SELECT * FROM S1; SELECT * FROM S; --***********END*********** -- 4. 在两个表中分别插入两个相同的新元组注意学号要大于已存在的学号 INSERT INTO S (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S99, 张三, 男, 2003-01-01, 计算机); INSERT INTO S1 (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S99, 张三, 男, 2003-01-01, 计算机); INSERT INTO S (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S98, 李四, 女, 2002-02-02, 数学); INSERT INTO S1 (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S98, 李四, 女, 2002-02-02, 数学); --***********BEGIN********* -- 5. 执行前面对两个表的查询操作 SELECT * FROM S1; SELECT * FROM S; -- 6. 创建新关系表 S2结构与 S 相同但不定义主键 CREATE TABLE S2 ( Sno Char(5) not null PRIMARY KEY, Sname Varchar(10) not null, Ssex Char(2), Sdateofbirth DATE, SD Varchar(10), ); -- 插入 S 的数据到 S2 INSERT INTO S2 (Sno, Sname, Ssex, Sdateofbirth, SD) SELECT Sno, Sname, Ssex, Sdateofbirth, SD FROM S; -- 7. 分别在 S、S1 和 S2 上查询学号为 S10 的学生信息 SELECT * FROM S WHERE Sno S10; SELECT * FROM S1 WHERE Sno S10; SELECT * FROM S2 WHERE Sno S10; --***********END*********** -- 8. 向 S、S1 和 S2 中插入相同的元组重复步骤 7 中的操作 INSERT INTO S (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S99, 王五, 男, 2003-01-01, 计算机); INSERT INTO S1 (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S99, 王五, 男, 2003-01-01, 计算机); INSERT INTO S2 (Sno, Sname, Ssex, Sdateofbirth, SD) VALUES (S99, 王五, 男, 2003-01-01, 计算机); GO