数据库原理及应用索引.doc

一、创建索引CREATE INDEX 语句用于在表中创建索引。CREATE UNIQUE INDEX index ON tablename (field ASC|DESC, field ASC|DESC, .) WITH PRIMARY | DISALLOW NULL | IGNORE NULL 索引分为聚簇索引和非聚簇索引。1.聚簇索引聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。聚簇索引确定表中数据的物理顺序。聚簇索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用，在汉语字典里，索引项是字母+声调，字典正文也是按照先字母再声调的顺序排列。 聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚簇（物理排序），避免每次查询该列时都进行排序，从而节省成本。 建立聚簇索引的思想1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。 2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、=)或使用group by或orderby的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。 3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。 4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。 5、选择聚簇索引应基于where子句和连接操作的类型。 聚簇索引的侯选列1、主键列,该列在where子句中使用并且插入是随机的。 2、按范围存取的列，如pri_order 100 and pri_order = $1000 AND range $20000B. 列表规则下例创建一个规则，用以将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的值。CREATE RULE list_ruleAS list IN (1389, 0736, 0877)C. 模式规则下例创建一个遵循这种模式的规则：任意两个字符的后面跟一个连字符和任意多个字符（或没有字符），并以 0 到 9 之间的整数结尾。CREATE RULE pattern_rule ASvalue LIKE _ _-%0-9CREATE RULE 定义一个适用于特定表或者视图的新规则。 CREATE OR REPLACE RULE 要么是创建一个新规则， 要么是用一个同表上的同名规则替换现有规则。 NAMECREATE RULE - 定义一个新的重写规则SYNOPSISCREATE OR REPLACE RULE name AS ON event TO table WHERE condition DO INSTEAD NOTHING | command | ( command ; command . ) DESCRIPTION 描述CREATE RULE 定义一个适用于特定表或者视图的新规则。 CREATE OR REPLACE RULE 要么是创建一个新规则， 要么是用一个同表上的同名规则替换现有规则。PostgreSQL规则系统允许我们在从数据库或表中更新， 插入或删除东西时定义一个其它的动作来执行。 简单说，规则就是当我们在指定的表上执行指定的动作的时候，导致一些额外的动作被执行。 另外，规则可以用另外一个命令取代某个特定的命令，或者令命令完全不被执行。 规则还用于实现表视图。我们要明白的是规则实际上只是一个命令转换机制，或者说命令宏。 这种转换发生在命令开始执行之前。如果你实际上想要一个为每个物理行独立发生的操作， 那么你可能还是要用一个触发器，而不是规则。有关规则的更多信息可以在 The Rule System 找到。目前，ON SELECT 规则必须是无条件的 INSTEAD 规则并且必须有一个由一条 SELECT 查询组成的动作。 因此，一条 ON SELECT 规则有效地把对象表转成视图， 它的可见内容是规则的 SELECT 查询返回的记录而不是存储在表中的内容（如果有的话）。 我们认为写一条 CREATE VIEW 命令比创建一个表然后定义一条 ON SELECT 规则在上面的风格要好。你可以创建一个可以更新的视图的幻觉， 方法是在视图上定义 ON INSERT，ON UPDATE，和 ON DELETE 规则（或者满足你需要的任何上述规则的子集），用合适的对其它表的更新替换在视图上更新的动作。如果你想在视图更新上使用条件规则，那么这里就有一个补充： 对你希望在视图上允许的每个动作，你都必须有一个无条件的 INSTEAD 规则。 如果规则是有条件的，或者它不是 INSTEAD， 那么系统仍将拒绝执行更新动作的企图，因为它认为它最终会在某种程度上在虚拟表上执行动作。 如果你想处理条件规则上的所由有用的情况，那也可以；只需要增加一个无条件的 DO INSTEAD NOTHING 规则确保系统明白它将决不会被调用来更新虚拟表就可以了。 然后把条件规则做成非 INSTEAD；在这种情况下，如果它们被触发，那么它们就增加到缺省的 INSTEAD NOTHING 动作中。 PARAMETERS 参数name 创建的规则名。它必须在同一个表上的所有规则的名字中唯一。 同一个表上的同一个事件类型的规则是按照字母顺序运行的。 event 事件是 SELECT， UPDATE，DELETE 或 INSERT 之一。 table 规则施用的表或者视图的名字（可以有模式修饰）。 condition 任意 SQL 条件表达式（返回 boolean）。 条件表达式除了引用 NEW 和 OLD 之外不能引用任何表，并且不能有聚集函数。 command 组成规则动作的命令。有效的命令是 SELECT，INSERT， UPDATE，DELETE，或 NOTIFY 语句之一。 在 condition 和 command 里， 特殊表名字 NEW 和 OLD 可以用于指向被引用表里的数值 new 在 ON INSERT 和 ON UPDATE 规则里可以指向被插入或更新的新行。 OLD 在 ON UPDATE，和 ON DELETE 规则里可以指向现存的被更新，或者删除的行。NOTES 注意为了在表上定义规则，你必须有 RULE 权限。有一件很重要的事情是要避免循环规则。 比如，尽管下面两条规则定义都是 PostgreSQL 可以接受的， 但一条 SELECT 命令会导致 PostgreSQL 报告一条错误信息，因为该查询循环了太多次：CREATE RULE _RETURN AS ON SELECT TO t1 DO INSTEAD SELECT * FROM t2;CREATE RULE _RETURN AS ON SELECT TO t2 DO INSTEAD SELECT * FROM t1;SELECT * FROM t1;目前，如果一个规则包含一个 NOTIFY 查询，那么该 NOTIFY 将被无条件执行 - 也就是说，如果规则不施加到任何行上头， 该 NOTIFY 也会被发出。比如，在CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable;UPDATE mytable SET name = foo WHERE id = 42;里，一个 NOTIFY 事件将在 UPDATE 的时候发出，不管是否有某行的 id = 42。这是一个实现的限制，将来的版本应该修补这个毛病。 三、创建存储过程存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。CREATE PROCEDURE 创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。 可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。 也可以创建在 Microsoft® SQL Server? 启动时自动运行的存储过程。语法CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 参数procedure_name新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。有关更多信息，请参见使用标识符。 要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (#procedure_name)。完整的名称（包括 # 或 #）不能超过 128 个字符。指定过程所有者的名称是可选的。 number是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。 parameter过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。 使用 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。 data_type参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。 说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。 VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。 default参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、 和 ）。 OUTPUT表明参数是返回参数。该选项的值可以返回给 EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。 n表示最多可以指定 2.100 个参数的占位符。 RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。 ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。 FOR REPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。 AS指定过程要执行的操作。 sql_statement过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。 n 是表示此过程可以包含多条 Transact-SQL 语句的占位符。 注释存储过程的最大大小为 128 MB。 用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。在单个批处理中，CREATE PROCEDURE 语句不能与其它 Transact-SQL 语句组合使用。 默认情况下，参数可为空。如果传递 NULL 参数值并且该参数在 CREATE 或 ALTER TABLE 语句中使用，而该语句中引用的列又不允许使用 NULL，则 SQL Server 会产生一条错误信息。为了防止向不允许使用 NULL 的列传递 NULL 参数值，应向过程中添加编程逻辑或为该列使用默认值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 关键字)。 建议在存储过程的任何 CREATE TABLE 或 ALTER TABLE 语句中都为每列显式指定 NULL 或 NOT NULL，例如在创建临时表时。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项控制 SQL Server 为列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 语句中没有指定的话）。如果某个连接执行的存储过程对这些选项的设置与创建该过程的连接的设置不同，则为第二个连接创建的表列可能会有不同的为空性，并且表现出不同的行为方式。如果为每个列显式声明了 NULL 或 NOT NULL，那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。 在创建或更改存储过程时，SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行存储过程时，将使用这些原始设置。因此，所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在存储过程中出现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的功能。 其它 SET 选项（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置，应在过程开头添加一条 SET 语句，以确保设置正确。从存储过程中执行 SET 语句时，该设置只在存储过程完成之前有效。之后，设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项，而不会影响存储过程的逻辑。 说明 SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串，由兼容级别设置控制。如果兼容级别小于或等于 65，SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70，则 SQL Server 将空字符串解释为空字符串。有关更多信息，请参见 sp_dbcmptlevel。 获得有关存储过程的信息 若要显示用来创建过程的文本，请在过程所在的数据库中执行 sp_helptext，并使用过程名作为参数。 说明 使用 ENCRYPTION 选项创建的存储过程不能使用 sp_helptext 查看。 若要显示有关过程引用的对象的报表，请使用 sp_depends。 若要为过程重命名，请使用 sp_rename。 执行存储过程成功执行 CREATE PROCEDURE 语句后，过程名称将存储在 sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中。第一次执行时，将编译该过程以确定检索数据的最佳访问计划。 使用EXECUTE执行存储过程。临时存储过程SQL Server 支持两种临时过程：局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。 临时过程用 # 和 # 命名，可以由任何用户创建。创建过程后，局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。如果创建了全局临时过程，则所有用户均可以访问该过程，权限不能显式废除。只有在 tempdb 数据库中具有显式 CREATE PROCEDURE 权限的用户，才可以在该数据库中显式创建临时过程（不使用编号符命名）。可以授予或废除这些过程中的权限。 示例A. 使用带有复杂 SELECT 语句的简单过程 下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info_all AND type = P) DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO au_info_all 存储过程可以通过以下方法执行： EXECUTE au_info_all - Or EXEC au_info_all 如果该过程是批处理中的第一条语句，则可使用： au_info_all B. 使用带有参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info AND type = P) DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE au_info lastname varchar(40), firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = firstname AND au_lname = lastname GO au_info 存储过程可以通过以下方法执行： EXECUTE au_info Dull, Ann - Or EXECUTE au_info lastname = Dull, firstname = Ann - Or EXECUTE au_info firstname = Ann, lastname = Dull - Or EXEC au_info Dull, Ann - Or EXEC au_info lastname = Dull, firstname = Ann - Or EXEC au_info firstname = Ann, lastname = Dull 如果该过程是批处理中的第一条语句，则可使用： au_info Dull, Ann - Or au_info lastname = Dull, firstname = Ann - Or au_info firstname = Ann, lastname = Dull C. 使用带有通配符参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info2 AND type = P) DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 lastname varchar(30) = D%, firstname varchar(18) = % AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE firstname AND au_lname LIKE lastname GO au_info2 存储过程可以用多种组合执行。下面只列出了部分组合： EXECUTE au_info2 - Or EXECUTE au_info2 Wh% - Or EXECUTE au_info2 firstname = A% - Or EXECUTE au_info2 CKarsOEn - Or EXECUTE au_info2 Hunter, Sheryl - Or EXECUTE au_info2 H%, S% D. 使用 OUTPUT 参数 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输入参数和一个输出参数。 首先，创建过程： USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = titles_sum AND type = P) DROP PROCEDURE titles_sum GO USE pubs GO CREATE PROCEDURE titles_sum TITLE varchar(40) = %, SUM money OUTPUT AS SELECT Title Name = title FROM titles WHERE title LIKE TITLE SELECT SUM = SUM(price) FROM titles WHERE title LIKE TITLE GO 接下来，将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。 参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 SUM = variable 形式）。 DECLARE TOTALCOST money EXECUTE titles_sum The%, TOTALCOST OUTPUT IF TOTALCOST 200 BEGIN PRINT PRINT All of these titles can be purchased for less than $200. END ELSE SELECT The total cost of these titles is $ + RTRIM(CAST(TOTALCOST AS varchar(20) 下面是结果集： Title Name - The Busy Executives Database Guide The Gourmet Microwave The Psychology of Computer Cooking (3 row(s) affected) Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200. E. 使用 OUTPUT 游标参数 OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。 首先，创建以下过程，在 titles 表上声明并打开一个游标： USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = titles_cursor and type = P) DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor titles_cursor CURSOR VARYING OUTPUT AS SET titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titles OPEN titles_cursor GO 接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。 USE pubs GO DECLARE My