【计算机三级数据库技术】第7章数据库及数据库对象--附思维导图

青旅半醒 2022-11-04 00:52 42阅读 0赞

### 目录 ###

*  1 创建及维护数据库
 *   *  1.1 SQL Server五个数据库
     *  1.2 组成
     *  1.3 数据库文件组
     *  1.4 数据库文件的属性
     *  1.5 T-SQL创建数据库
     *  1.6 修改数据库
     *  1.7 分离和附加数据库
 *  2 架构
 *   *  2.1 概念
     *  2.2 定义架构
     *  2.3 删除架构
 *  3 分区表
 *   *  3.1 概念
     *  3.2 创建分区表
 *  4 索引
 *   *  4.1 创建索引
     *  4.2 删除索引
 *  5 索引视图
 *   *  5.1 基本概念
     *  5.2 适合建立索引视图的场合
     *  5.3 定义索引视图

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTY5Ng_size_16_color_FFFFFF_t_70_pic_center]

# 1 创建及维护数据库 #

## 1.1 SQL Server五个数据库 ##

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

## 1.2 组成 ##

（1）数据文件

*  主要数据文件  
    拓展名.mdf,要求不能小于3M，大于model数据库的大小
 *  次要数据文件  
    拓展名.ndf

（2）日志文件  
拓展名.ldf  
（3）数据库存储空间的分配  
在SQLServer中，数据的存储分配单位是数据页，一页是一块8KB=（8060B+132B，8060是存放数据，132存放系统信息）的连续磁盘空间

## 1.3 数据库文件组 ##

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

## 1.4 数据库文件的属性 ##

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

## 1.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 =
    )

（1）单个数据文件，单个日志文件  
（2）多个数据文件，多个日志文件  
（3）分组下的多个数据文件，多个日志文件

## 1.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

## 1.7 分离和附加数据库 ##

（1）分离数据库

Exec sp_detach_db 'Database_name','True'
    True表示跳过更新统计信息

（2）附加数据库

Create DataBase database_name
    ON(FIlename=''
    For Attach)

# 2 架构 #

## 2.1 概念 ##

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

## 2.2 定义架构 ##

Create Schema schema_name Authorization user_name

## 2.3 删除架构 ##

Drop Schema schema_name Cascade|Restrict

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

# 3 分区表 #

## 3.1 概念 ##

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

## 3.2 创建分区表 ##

（1）条件

*  如果表中有大量的数据都是经常使用的数据，而且她们的操作方式基本一样的，则最好不要使用分区表
 *  如果数据量大，而且数据是分段的，并且对不同段的数据使用的操作不同，则适用于使用分区表  
    （2）步骤
 *  创建分区函数

Create Partition Function partition_function_name(input_type)
    AS Range [ Left |Right] For Values(1,100,1000)

最后的数值表示分区指定边界值，表示四个分区

*  创建分区方案

Create Partition Schema partition_schema_name As Partition partition_function_name [All] To (test1fg,test2fg,test3fg,test4fg)

*  使用分区方案创建表

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);

# 4 索引 #

## 4.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)

（2）创建分区索引，在TransHistory表和ReferOrderID列上为现有分区方案TransactionsPS1创建非聚集分区索引

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

## 4.2 删除索引 ##

Drop INDEX Index_name

# 5 索引视图 #

## 5.1 基本概念 ##

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

## 5.2 适合建立索引视图的场合 ##

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

## 5.3 定义索引视图 ##

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

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTY5Ng_size_16_color_FFFFFF_t_70_pic_center]: /images/20221023/354616a9b2af457db87543c199160b29.png