SQL--架构、视图和索引

架构、视图和索引

架构

架构是指用于存储数据库对象的一个命名空间。用于集中管理数据库对象子集，这样极大地方便并简化了管理数据库对象。

创建架构

CREATE SCHEMA schema_name_clause [<schema_element> [...n]]

<schema_name_clause>::=
{
    schema_name
    | AUTHORIZATION owner_name
    | schema_name    AUTHORIZATION owner_name
}

<schema_element>::=
{
    table_definition | view_definition
    grant_statement | revoke_statement | deny_statement
}

schema_name指架构的名称，架构命名规则：

• 架构的名称最长可达128个字符
• 架构的名称必须以英文字母开头
• 架构的名称在同一个数据库中必须唯一

AUTHORIZATION owner 用于指定架构的所有者

table_definition | view_definition 用于指定在架构中创建CREATE TABLE或CREATE VIEW语句

grant_statement | revoke_statement | deny_statement 用于指定对除新架构外的任何安全对象的授予权限GRANT, 撤销权限REVOKE和拒绝权限DENY语句。

创建的架构包含一个【学生信息】表对象，并且架构的所有者是dbo。

移动对象到新的架构

架构是存储对象的容器，在实际应用中，有时候需要将对象从一个架构移动到另一个架构中，需要注意的是移动对象到新的架构必须是同一个数据库中移动。

当移动对象到新的架构时，会更改与对象相关联的命名空间，也会更改对象查询和访问方式，并且影响设置在对象上的权限。

要在架构之间移动对象，必须拥有对对象的CONTROL权限以及对对象目标架构的ALTER权限。

ALTER SCHEMA schema_name TRANSFER securable_name

将securable_name架构中的对象转移到shema_name架构中。

删除架构

如果不再需要某个架构，可以通过DROP SCHEMA语句将其删除。

DROP SCHEMA schema_name

当删除架构的时候，必须确保正在使用正确的数据库，并且没有使用master数据库。

视图

视图是用于查询表中数据的另一种方式，但与表不同的是，视图是一个虚表，是从一个或几个基本表中导出的表。数据库中只存在视图的定义，而不存在视图中相对应的数据，数据仍然存放在原来的基本表中。所以当基本表中的数据发生变化时，从视图中查询出的数据也随之改变。从某种角度讲，视图就像一个移动的窗口，通过这个窗口用户可以看到自己需要的数据。

视图的作用在于方便查询，将经常使用的复杂查询语句写成视图。后期可以使用查询视图的方法来代替复杂的查询语句。

创建视图

CREATE VIEW [schema_name.] view_name [(column [,...n])]
[WITH <view_attribute> [,...n]]
AS select_statement
[WITH CHECK OPTION]

<view_attribute>::=
{
    [ENCRYPTION]
    [SCHEMABINDING]
    [VIEW_METADATA]
}

CHECK OPTION强制针对视图执行的所有数据修改语句都必须符合在select_statement中设置的条件。

SCHEMABINGDING将视图绑定到基础表的架构

VIEW_METADATA指定为引用视图的查询请求浏览模式的元数据时，SQL Server实例将向DB-Library，ODBC和OLE DB API返回有关视图的元数据信息，而不返回基表的元数据信息。

查询表达式可以是任意的复杂的SELECT查询语句，但通常不允许含有ORDER BY子句和DISTINCT短语。

当指定视图的属性列名时，或者全部指定或者全部省略，没有第三种选择。

select_statement可以是任何的查询语句。

更新视图

• 插入数据

INSERT INTO view_name
VALUES
(value[,...n]) [,...n]

不能在使用了DISTINCT、GROUP BY或者HAVING语句的视图中插入数据。另外，对由多个基表连接而成的视图来说，一个插入操作只能作用于一个基表上。

• 修改数据

UPDATE view_name
SET column = value [AND column = value ...n]
[WHERE condition]

当视图是基于多个表创建时，那么修改数据只能修改一个表中的数据。

• 删除数据

通过使用DELETE语句可以将视图中的数据删除，在视图中删除的数据同时在表中也被删除。但是如果一个视图连接了两个以上的基表时，对数据的删除操作则不允许。

DELETE FROM view_name
[WHERE condition]

如果视图中的列是常数或几个字符串列值的和，那么尽管在插入和更新操作时不允许，但却可以在删除操作中进行。

查看视图

当创建视图完成后，如果需要查看有关视图的定义文本，可以使用存储过程sp_helptext语句进行查看。

EXEC sp_helptext view_name

修改视图

如果基表发生变化，或者要通过视图查询更多的信息，则可以根据需要使用ALTER VIEW语句修改视图的定义。

ALTER VIEW [schema_name.] view_name [(column [,...n])]
[WITH <view_attribute> [,...n]]
AS select_statement
[WITH CHECK OPTION]

<view_attribute>::=
{
    [ENCRYPTION]
    [SCHEMABINDING]
    [VIEW_METADATA]
}

删除视图

当某个视图不再需要时，可以使用DROP VIEW语句将视图删除。删除一个视图，就是删除其定义和赋予它的全部权限。删除一个表并不能自动删除引用该表的视图。

DROP VIEW view_name [,...n]

索引

索引是加快查询速度的有效手段，它主要用于提高性能。用户可以根据实际应用需要，在基本表上建立一个或多个索引，以提高多种存取路径，快速定位数据的存储位置。

索引的概述

索引是一个单独的、物理的数据库结构，是某个表中一列或者若干列的集合和相应指向表中物理标识这些值的数据页的逻辑指针清单。索引的建立依赖于表，它提供了数据库编排表中数据的内部方法。表的存储由两部分组成，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，然后通过指针从数据页面中读取数据。

索引一旦创建，将由数据库自动管理和维护。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供一种快速访问指定记录的方法。

将表和索引分别存储在不同的文件组，会大大提高操作数据的速度。

索引类型

• 聚集索引

索引按B-Tree树结构进行组织。索引B-Tree树中的每一页称为一个索引节点。由于真正的数据页链只能按一种方式进行排序，因此，一个表只允许包含一个聚焦索引。

默认情况下，表中的数据在创建索引时排序。但是，如果聚集索引已经存在，且正在使用同一名称和列重新创建，而数据已经排序，则会重建索引，而不是从头创建该索引。

聚集索引主要按下列方式实现：

PRIMARY KEY和UNIQUE约束

独立于约束的索引

索引视图

• 非聚集索引

非聚集索引的数据存储在一个位置，索引存储在另一个位置，索引带有指针指向数据的存储位置。索引中的项目按索引值的顺序存储，而表中的信息按另一种顺序存储。

非聚集索引和聚集索引具有相同的B-Tree结构，存在两个不同：

数据行不按非聚集索引键的顺序排序和存储

非聚集索引的叶层不包含数据页，相反，叶节点包含索引行。

确定索引列

增加索引也有许多不利的因素。

创建和维护索引要耗费时间。

索引需要占物理空间

当对表中的数据进行更新操作时，索引也要动态地维护，这样就降低了数据的维护速度。

选择表和列创建索引的原则

适合创建索引的表或者列	不适合创建索引的表或列
有许多行数据的表	几乎没有数据的表
经常用于查询的列	很少用于查询的列
有宽度范围的值并且在一个典型的查询中，行极有可能被选择的列	有宽度范围的值并且在一个典型的查询中，行不太可能被选择的列
用于聚合函数的列	列的字节数大
用于GROUP BY查询的列	有许多修改，但很少实际查询的表
用于ORDER BY查询的列
用于表级联的列

 

使用聚集和非聚集索引的原则

可以使用聚集索引的列	可以使用非聚集索引的列
被大范围搜索的主键，如账户	顺序的标识符的主键，如标识列
返回大结果集的查询	返回小结果集的查询
用于许多查询的列	用于聚合函数的列
强选择性的列	外键
用于GROUP BY 或者ORDER BY查询的列
用于表级联的列

创建索引

CREATE [UNIQUE] [CLUSTERED] [NONCLUSTERED] INDEX index_name
ON table_or_view_name (column [ASC | DESC] [,...n])
[INCLUDE (column_name [,...n])]
[WITH
PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STATISTICS_NORECOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = max_degree_of_parallelism[,...n]]
ON {partition_schema_name(column_name) | filegroup_name |default}

修改索引

• 重新生成索引

ALTER INDEX index_name ON table_or_view_name REBUILD

• 重新组织索引

ALTER INDEX index_name ON table_or_view_name REORGANIZE

• 禁止索引

ALTER INDEX index_name ON table_or_view_name DISABLE

删除索引

DROP INDEX <table or view name>.<index name>

DROP INDEX <index name> ON <table or view name>