(2) 使用SQL语句创建索引 语法格式如下: CREATE [UNIQUE] [CLUSTER|NONCLUSTERED] INDEX index_name ON table_name(column_name[ASC/DISC] [,column_name[ASC/DISC]]….]) 参数说明: UNIQUE:表示创建的索引是唯一索引,每个索引只对应一个元组值。 CLUSTERED:表示创建的索引是簇集索引。 NONCLUSTERED:表示创建的索引是非簇集索引。 Index_name:创建的索引名。 Table_name:创建索引所在的表名。 Column_name:创建索引使用的列名。由ntext、text或image数据类型组成的列不能指定为索引列。 ASC|DESC:确定具体某个索引列的升序或降序排列。默认设置为ASC。
例1,为TEACHER表建立唯一索引tnamex,升序排列。不管教师是否有重名,每个索引值只对应一个教师名字。 CREATE UNIQUE INDEX tnamex ON TEACHER(tname)
例2,在SC表上按学号sno建立聚集索引scinx。 CREATE CLUSTER INDEX scinx ON SC(sno) 在选课表中建立聚集索引scinx之后,同一个学生的所有选课成绩记录物理地排在一起。所以,查询某个学生的学习成绩或打印某个学生的成绩单速度就比较快。如表4-3。
表4-3
例3:为student表建立索引,NONCLUSTERED为隐含值,可以省略。 CREATE NONCLUSTERED INDEX stu_index ON student(sname)
(3) 在两个或更多属性上建立索引: 假如查询经常涉及到多个列值的检索条件,可在一个关系表上建立多个属性的索引,提高查询效率。否则会降低查询效率。 例如,选课关系sc,主码(sno, cno),经常查询某学号的同学选修某课程的成绩。为两个属性建立索引的语句如下: CREATE INDEX Scidx ON sc(sno, cno);
(4) 使用索引的原则: 要不要建索引以及如何建索引,当属于内模式的概念,这是数据库设计中一个很重要的问题。设计人员要仔细考虑实际应用中修改与查询的频率,权衡建索引的利弊。例如,若一关系的经常性操作是数据的修改,则不宜建索引。但有些修改语句可能包含着查询操作。 一般来说,建索引有几项参考原则: ● 值得建索引:记录有一定规模,而查询只局限于少数记录。 ● 索引用得上:索引列在where子句中应频繁使用。先装数据,后建索引:对于大多数基本表,总是有一批初始数据需要装入。该原则是说,建立关系后,先将这些初始数据装入基表,然后再建索引,这样可加快初始数据的录入。如果建表时就建索引,那么在输入初始数据时,每插入一个记录都要维护一次索引。当然,索引早建晚建都是允许的。 ● 在下列三种情况下,有必要建立簇索引: (1)查询语句中采用该字段作为排序列 例如,我们经常执行下列语句: SELECT * FROM authors ORDER BY au_id 这时候有必要考虑在该表格上建立以au_id为关键字的簇索引。 CRATE CLUSTERED INDEX au_index On authors (au_id)
(2)需要返回局部范围的大量数据 例如,我们要做如下的查询: SELECT * FROM authors WHERE zip BETWEEN 94618 AND 96214 这时就有必要在zip列上建立簇索引。 CRATE CLUSTERED INDEX zip_index On authors (zip)
(3)表格中某字段内容的重复性比较大例如,student表中dno(系号)一列有大量重复数据,当在dno列上建立了簇索引后,下面的连接查询速度会加快。 SELECT * FROM student,dept WHERE student.dno=dept.dno 对以上情况,建立簇索引会提高访问数据库的效率。建立簇索引的语句如下: CRATE CLUSTERED INDEX dept_index On student(dep_id) 注意:当在同一表格中建立簇索引和非簇索引时,先建立簇索引后建非簇索引比较好。因为如先建非簇索引的话,当建立簇索引时,SQL Server会自动将非簇索引删除,然后重新建立非簇索引。每个表仅可以有一个簇索引,最多可以有249个非簇索引。它们均允许以一个或多个字段作为索引关键字(Index Key),但最多只能有16个字段。 SQL Server只对那些能加快数据查询速度的索引才能被选用。如果利用索引检索还不如顺序扫描速度快,SQL Server仍用扫描方法检索数据。建立不能被采用的索引只会增加系统的负担,降低检索速度。因此,可利用性是建立索引的首要条件。
2. 修改索引 用ALTER INDEX可以将一个聚集索引改为一个非聚集索引,或将可以将一个非聚集索引改为一个聚集索引。缺省为非聚集索引。 修改索引语句格式: ALTER INDEX 索引名 TO [NOT]CLUSTER
例1,把tnamex改为一个聚集索引,该表的元组的物理位置要从新排列,原有的非聚集索引需要全部删除,而后重建。 ALTER INDEX tnamex TO CLUSTER
例2,把scinx改为一个非聚集索引 ALTER INDEX scinx TO NOT CLUSTER 这里只介绍用SQL语句修改索引,使用企业管理器修改索引与添加索引大致相同,在此不再赘述。
3.删除索引: 删除索引的语句格式: DROP INDEX table_name. index_name 参数说明: Table_name:要删除索引所在的表。 Index_name:要删除索引的名称。 例如:将teacher表中的索引tnamex删除: DROP INDEX teacher.tnamex 执行 DROP INDEX 后,将重新获得以前由索引占用的空间。此后可将该空间用于任何数据库对象。
● 用子查询插入多个元组值的语句格式: INSERT INTO 表名[(列名[,列名]…)] 子查询 注意:在使用子查询的结果插入元组时,子查询的结果必须匹配待插入表中的列数,并和相应各列数据类型兼容。如果表格中存在某些列定义为NOT NULL,那么子查询的结果在该列上必须有值,否则插入会失败。
例3,建立一个新表,存放每个学生的学号、姓名和平均成绩。把子查询结果插入新表中。 CREATE TABLE savg( sno CHAR(8),sname CHAR(20), avage REAL ) INSERT INTO savg (sno,sname,avage) ( SELECT sno,sname,AVG(grade) FROM SC,STUDENT WHERE SC.sno = STUDENT.sno GROUP BY sno )
例4,在表Studio中插入属性name,这些name是在表Movie中提到,但没有在表Studio中出现的studioName。 INSERT INTO Studio(name) SELECT DISTINCT studioName FROM Movie WHERE studioName NOT IN (SELECT name FROM Studio);
考虑上述例子如果去掉DISTINCT会是什么样子?和你想象的一样吗?
在SQL中,最常用的向表格中添加数据的方法是使用INSERT语句。但是,用INSERT语句每次只能插入一个元组。用带子查询插入语句,一个次可以插入一个或多个元组。 ● 插入一个元组的语句格式: INSERT INTO 表名[(列名[,列名]…)] VALUES(值[,值]…..) 插入单个元组,按顺序在表名后给出表中每个列名,在VALUES后给出对应的每个列值。插入一个完整的新元组时,可省略表的列名。插入部分列值,必须在表名后给出要输入值的列名。
例1,插入单个元组,按顺序给出表中每个列值。 INSERT INTO DEPT VALUES( "CS","计算机","888", "10区" )
例2,插入一个学生的部分列值,必须在表名后给出要输入值的列名。 INSERT INTO STUDENT (sno ,sname) VALUES( "J20045", "刘琉")
● 用子查询插入多个元组值的语句格式: INSERT INTO 表名[(列名[,列名]…)] 子查询 注意:在使用子查询的结果插入元组时,子查询的结果必须匹配待插入表中的列数,并和相应各列数据类型兼容。如果表格中存在某些列定义为NOT NULL,那么子查询的结果在该列上必须有值,否则插入会失败。
例3,建立一个新表,存放每个学生的学号、姓名和平均成绩。把子查询结果插入新表中。 CREATE TABLE savg( sno CHAR(8),sname CHAR(20), avage REAL ) INSERT INTO savg (sno,sname,avage) ( SELECT sno,sname,AVG(grade) FROM SC,STUDENT WHERE SC.sno = STUDENT.sno GROUP BY sno )
例4,在表Studio中插入属性name,这些name是在表Movie中提到,但没有在表Studio中出现的studioName。 INSERT INTO Studio(name) SELECT DISTINCT studioName FROM Movie WHERE studioName NOT IN (SELECT name FROM Studio);
考虑上述例子如果去掉DISTINCT会是什么样子?和你想象的一样吗?
DELETE 语句从表中删除一个或多个满足条件的元组。语句格式: DELETE FROM 表名 [WHERE 条件表达式] WHERE子句中的条件表达式给出被删除元组应满足的条件;若不写WHERE子句,表示删除表中的所有元组,但表的定义仍存在。
例1,只涉及单个元组,从数据库中删除某个学生李楷: DELETE FROM STUDENT WHERE sname = "李楷"
例2,删除操作涉及多个元组,从数据库中删除某门课程的所有元组: DELETE FROM SC WHERE cno = "E001"