『SQL Server』第六章 数据库及其对象.md

第六章 数据库及其对象

数据库

1. 分类:系统数据库:master:数据库系统信息　　 　　　 　　
   msdb:数据库作业信息　　 　　　 　　
   model:数据库模板信息　　　 　　 　　
   resource:只读数据库且不可见　　 　　　 　　
   tempdb:临时数据库
   　 　用户数据库:数据文件:主要数据文件:.mdf
   　　　 　　　 　　 　　　次要数据文件:.ndf　 　　　 　　　日志文件:事务日志文件:.ldf
2. 创建:create database db_name　　on　primary　　(<filespec>)　　log on　　(<filespec>)

对<filespec>的解释:name=db_name_data,　　　　　　 　 　filename='C:\db_name_data.mdf',　　　　　 　 　　size=3MB,　　　　　 　 　　maxsize={50MB|unlimited},　　　　　 　 　　filegrowth={1MB|10%}
要插入的表已存在: insert into t2 select * from t1
3. 修改:alter database db_name
　　 add file　　 add log file
　　 modify name=　　 modify file
　　 remove file
4. 分离:exec sp_detach_db 'db_name','true'
5. 附加:
6. 自增标识列:identity

架构(构架)(模式)

1. 创建: create schema sc_name
2. 可以共享

   分区表

3. 创建:例:在分区列colA_name(int)上创建左侧分区函数.　　　(1)创建分区函数:create partition function pf_name(int)
　　　 　　 　　　　　as range left for values(1,10,100）　　　(2)创建分区方案:create partition scheme ps_name　　　　　 　　 　　　as partition pf_name　　　　　　　 　 　　to (test1,test2,test3,test4)　　　(3)使用分区建表:create table tb_name　　　　　　　 　 　　(col_name type… 　　　　　　 　　 　　on ps_name(colA_name)

索引

1. 创建:create [unique | [non]clustered] index idx_name　 　on tb_name(col_name)　 　[with fillfactor=x]
2. 重复小列在前
   主索引为主键索引,辅索引提高查询效率,位图索引用于范围查询，哈希索引用于精确查询

   视图

3. 索引视图来源必须是基本表,不适合建立在大量更新写操作高基数度的数据聚合(group by)

讲一起使用的公共列值的多个表的数据行存储在一起的方法称为聚集

设商品表有下列查询:
SELECT 商品号,商品名,单价
FROM 商品表 WHERE 类别 IN(‘食品’,’家电’)
ORDER BY 商品号
现建立索引:
CREATE INDEX idx1 ON 商品表(商品号) # create unique index idx_name on tb_name(列名)
INCLUDE(商品名,单价) # include(属性)
WHERE 类别=’食品’ OR 类别=’单价’ # where 约束条件

将 t1 表的 c1 列 (int 类型)定义为标识列,初量和增量均为1
CREATE TABLE t1(c1 INT IDENTITY(1,1),…)

直接增加已有文件的大小是扩大数据库空间的一种方法
ALTER DATABASE db1 MODIFY FILE(NAME=db_datafile,SIZE=200MB)

数据库的分类分为系统数据库和用户数据库。
系统数据库分为master，msdb，tempdb，model，resource，其分别保存系统信息，作业信息，临时信息，模板，只读，无法看到
将数据库以文件形式保存。分为数据文件和日志文件。
数据文件分为主要数据文件（mdf只要一个，≥3）和次要数据文件（ndf，n个）
日志文件（ldf，≥1）
数据库存储空间的分配：
文件组：主文件组和用户文件组

一、创建及维护数据库

1. SQL Server五个数据库

master:记录所有系统信息，包括实例范围的元数据、端点、连接服务器和系统配置设置。还记录了所有其他数据库的存在、数据库文件的位置以及SQLServer的初始化信息
model：用作实例上创建的所有数据库的模板。当用户创建一个数据库时，系统将自动将Model数据库中的全部内容复制到新建数据库中
temodb:临时数据库，用于保存临时对象或中间结果集，并未数据的排序等操作提供一个临时工作空间
msdb:供SQL Server 代理服务调度博阿金和作业以及记录操作员时使用，保存关于调度报警、作业、操作员等信息。作业是在SQLserver中定义的自动执行的一系列操作的几何，作业的执行不需要人工干预
Resource：是一个只读数据库，包含了所有系统对象，SQLServer系统对象在物理上保存在Resource数据库中，但在逻辑上却显示在每个数据库的sys架构中

2 组成

（1）数据文件

主要数据文件
拓展名.mdf,要求不能小于3M，大于model数据库的大小
次要数据文件
拓展名.ndf
（2）日志文件
拓展名.ldf
（3）数据库存储空间的分配
在SQLServer中，数据的存储分配单位是数据页，一页是一块8KB=（8060B+132B，8060是存放数据，132存放系统信息）的连续磁盘空间

3 数据库文件组

（1）主文件组
（2）用户定义文件组
通过create DataBase 或 Alter DataBase 语句中使用Filegroup 关键字指定的任何文件组

4 数据库文件的属性

文件名及其位置
    一般情况下，如果有多个数据文件的话，为了获得更好的性能，建议将文件分散存储在多个物理磁盘上
初始大小
    在指定主要数据文件的初始大小时，其大小不能小于model数据库主要文件的大小
增长方式
最大大小
    是指文件增长的最大空间限制

5 T-SQL创建数据库

Create Database database_name
ON Primary%指定主文件组
(Name =主文件名
FileName =操作系统物理文件名
Size = 文件的初始大小
Maxsize = 文件最大值
FileGrowth =指定文件的自动增量
)
LOG %指定日志文件
(Name =
FileName =
Size =
Maxsize =
FileGrowth =
)
FileGroup
(Name =
FileName =
Size =
Maxsize =
FileGrowth =
)

6 修改数据库

（1）扩大数据库空间

添加数据文件
Alter DataBase database_name
ADD File(Name =
FileName =
Size =
Maxsize =
FileGrowth =)
添加日志文件
Alter DataBase database_name
ADD LOG File(Name =
FileName =
Size =
Maxsize =
FileGrowth =)
指定要修改的文件
Alter DataBase database_name
Modify File(Name =
FileName =
Size =
Maxsize =
FileGrowth =)
（2） 收缩数据库空间

收缩整个数据库大小
DBCC ShrinkDatabase
(DataBase_name,20)20表示20%
收缩指定文件大小
DBCC ShrinkFIle
(DataFIle_name,4)4表示4M
（3）添加和删除数据库文件

添加文件(和创建数据库相同)
ALter DataBase
删除文件
Alter DataBase database_name
Remove File datafile_name

7 分离和附加数据库

1）分离数据库

Exec sp_detach_db ‘Database_name’,’True’
True表示跳过更新统计信息
（2）附加数据库
Create DataBase database_name
ON(FIlename=’’
For Attach)

二、架构

1 概念

是数据库下的一个逻辑命名空间，可以存放表、视图等数据库对象，是一个数据库对象的容器

2 定义架构

Create Schema schema_name Authorization user_name

3 删除架构

Drop Schema schema_name Cascade|Restrict
Cascade：删除架构的同时将架构中的所有架构对象一起全部删除
Restrict：如果被删除的架构中包含架构对象，则拒绝删除

三、分区表

1 概念

是将表中的数据按水平方式划分成不同的子集，这些数据子集存储在数据库的一个或多个文件组中。使用分区可以快速且有效地管理和访问数据子集，从而使大型表或索引更易于管理

2 创建分区表

（1）条件

如果表中有大量的数据都是经常使用的数据，而且她们的操作方式基本一样的，则最好不要使用分区表
如果数据量大，而且数据是分段的，并且对不同段的数据使用的操作不同，则适用于使用分区表
（2）步骤
创建分区函数
Create Partition Function partition_function_name(input_type)
AS Range [ Left |Right] For Values(1,100,1000)
1
2
最后的数值表示分区指定边界值，表示四个分区

创建分区方案
Create Partition Schema partition_schema_name As Partition partition_function_name [All] To (test1fg,test2fg,test3fg,test4fg)
1
使用分区方案创建表
Create Partition Function myRangePF1(int)
AS Range Left For Values(1,100,1000)
Go
Create Partition Schema myRangePS1 As Partition myRangePF1 To (test1fg,test2fg,test3fg,test4fg)
GO
Create Table PartitionTable(
coll int,
col2 char(10))
ON myRangePS1(col1);

四、索引

1 创建索引

Create [Unique][Clustered |NonClustered ] INDEX index_name
ON
[Include ]指定要添加到非聚集索引叶级别的非键列
[Where]
[ON {partition_shema_name}指定分区方案{filegroup_name}]为指定文件组创建索引

举例
（1）创建非聚集索引，要求索引键值Cname升序，CardID降序排序

Create Index Comp_index ON Table_customer(Cname ASC,CardID DESC)
1
（2）创建分区索引，在TransHistory表和ReferOrderID列上为现有分区方案TransactionsPS1创建非聚集分区索引

Create NonClustered INDEX index_name
ON TransHistory(RefreOrderID)
ON TransactionsPS1(TransactionDate);

2 删除索引

Drop INDEX Index_name

五、索引视图

1 基本概念

标准视图也成虚拟表，因为这种视图所返回的结果集的格式与基本表基本相同，都是由列和行组成，而且在SQL语句中索引视图的方式也与索引基本表的方式相同。标准视图的结果集并不永久地存储在数据库中，每次通过标准视图查询数据时，数据库管理系统都会在内部将视图的定义替换为对基本表的查询语句，然后再对基本表进行查询

2 适合建立索引视图的场合

如果很少更新基础数据，则索引视图的效果最佳
如果经常更新基础数据，则维护索引视图的成本可能超过使用索引视图带来的性能收益
如果基础数据以批处理的形式定期更新，但是在更新之间主要作为只读数据进行处理，可考虑在更新前删除所有索引视图，然后再重建索引视图，这样做可以提高更新的性能
处理大量行的连接和聚合操作
许多查询经常执行的链接和聚合操作

3 定义索引视图

基本要求
    定义索引视图时，视图不能引用任何其他视图，只能引用索引表
    视图引用的所有基本表必须与视图位于位于同一个数据库中，并且所有者也与视图相同
    必须使用SchemaBinding选项创建视图
    视图中的表达式引用的所有函数必须是确定的
    对视图创建的第一个索引必须是唯一聚集索引，之后再创建其他的非聚集索引
%定义视图

举例
首先创建一个视图，并未该视图创建一个唯一聚聚索引，然后是一个使用该索引视图查询语句

Create VIew Slaes,vOrders
With SchemaBinding
As
….
%定义索引视图
Go
Create Unique Clustered INDEX Idx_V1
ON Sales.vOrders (OrderDate,ProductID);
Go
…查询语句，不用From 索引视图