计算机三级数据库技术备考笔记（七）

第七章 数据库及数据库对象

• 创建及维护数据库
  • SQL Server数据库概述
    • SQL Server 2008 中的数据库由包含数据的表集合以及其他对象(如视图、索引、存储过程等)组成,目的是为执行与数据有关的活动提供支持。
    • 从数据库的应用和管理角度,SQL Server将数据库分为两大类:系统数据库和用户数据库。
    • 系统数据库是SQL Server数据库管理系统自动创建和维护的,这些数据库用于保存维护系统止常运行的信息,例如,一个SQL Server实例上共建有多少个数据库,每个数据库的属性及其所包含的对象,每个数据库的用户以及用户的权限等。一般用户对系统数据库只有查询权。
    • 用户数据库保存的是与用户的业务有关的数据。通常所说的建立数据库指的是创建用户数据库,对数据库的维护也指的是对用户数据库的维护。
    • 安装好SQL Server 2008后,系统会自动安装五个系统数据库,分别是：master、msdb、model、tempdb 和Resource,各数据库的主要用途如下:
      • master：是SQL Server 2008中最重要的数据库,记录SQL Server 实例的所有系统级信息包括实例范围的元数据(例如登录账户)、端点、连接服务器和系统配置设置。此外,master数据库还记录了所有其他数据库的存在、数据库文件的位置以及SQL Server 的初始化信息。
      • msdb：供SQL Server 代理服务调度报警和作业以及记录操作员时使用,保存关于调度报警、作业、操作员等信息,作业是在SQL Server中定义的自动执行的一系列操作的集合,作业的执行不需要任何人工干预。
      • model：用作SQL Server实例上创建的所有数据库的模板。对model数据库进行的修改(如数据库大小、排序规则、恢复模式和其他数据库选项)将应用于以后创建的所有用户数据库。当用户创建一个数据库时,系统自动将model数据库中的全部内容复制到新建数据库中。
      • tempdb：临时数据库,用于保存临时对象或中间结果集,并为数据的排序等操作提供一个临时工作空间。每次启动SQL Server时都会重新创建tempb数据库。
      • Resource：是一个只读数据库,包含了SQL Server中的所有系统对象。SQL Server系统对象在物理上保存在Resource数据库中,但在逻辑上却显示在每个数据库的sys架构中。因此,在SSMS的对象资源管理器中,在“系统数据库"下看不到这个数据库。
  • SQL Server数据库的组成
    • SQL Server 将数据库映射为一组操作系统文件,这些文件被划分为两类：数据文件和日志文件。
    • 数据文件包含数据和对象,日志文件包含恢复数据库中的所有事务需要的信息。
    • 数据文件
      • 主要数据文件。
        • 主要数据文件的推荐扩展名是.mdf,它包含数据库的系统信息,也可存放用户数据。
        • 每个数据库都有且只能有一个主要数据文件。
        • 主要数据文件是为数据库创建的第一个数据文件。
        • SQL Server 2008 要求主要数据文件的大小不能小于3MB
      • 次要数据文件。
        • 次要数据文件的推荐扩展名是.ndf。
        • 一个数据库可以不包含次要数据文件,也可以包含多个次要数据文件,而且这些次要数据文件可以建立在一个磁盘上,也可以分别建立在不同的磁盘上。
      • 让一个数据库包含多个数据文件,并且让这些数据文件分别建立在不同的磁盘上,不仅有利于充分利用多个磁盘上的存储空间,而且可以提高数据的存取效率。
    • 事务日志文件
      • 事务日志文件的推荐扩展名为.ldf,用于存放恢复数据库的所有日志信息。
      • 每个数据库必须至少有一个日志文件,也可以有多个日志文件。
      • SQL Server 2008 不强制使用.mdf、.ndf和.Idf文件扩展名,但建议使用这些扩展名以利于标识文件的用途。
    • 数据库存储空间的分配
      • 在考虑数据库的空间分配时,需了解如下规则:
        • 在创建用户数据库时，model数据库自动被复制到新建用户数据库中,而且是复制到主要数据文件中。
        • 在SQL Server 2008中,数据的存储分配单位是数据页(Page,也简称为页)。一页是一块8KB(8x1024B,其中用8060B存放数据,另外的132B存放系统信息)的连续盘空间。页是存储数据的最小单位,页的大小决定了数据库表中一行数据的最大大小。
        • SQL Server不允许表中的一行数据存储在不同页上[varchar(max)、nvarchar(max)、text、ntext、varbinary(max)和image 数据类型除外],即行不能跨页存储。因此表中一行数据的大小不能超过8060B。
  • 数据库文件组
    • 文件组的概念类似于操作系统中的文件夹,在数据库中,可以定义多个文件组然后将文件放置到不同的文件组中。
    • SQL Server有两种类型的文件组:主文件组和用户定义的文件组。
    • 主文件组
      • 主文件组(PRIMARY)是系统定义好的一个文件组,它包含主要数据文件和任何没有明确分配给其他文件组的其他数据文件。
      • 系统表的所有页均分配在主文件组中。
    • 用户定义文件组
      • 用户定义文件组是通过在 CREATE DATABASE或 ALTER DATABASE 语中使用FILEGROUP关键字指定的任何文件组。
      • 说明：
        • (1)日志文件不包括在文件组内,日志空间与数据空间是分开管理的。
        • (2)一个文件不可以是多个文件组的成员。
        • (3)如果文件组中有多个文件,则它们在所有文件被填满之前不会自动增长,而填满后这些文件会循环增长。
      • 如果在定义数据文件时没有指定其所属的文件组,则新建数据文件将被分配到默认文件组。
      • 每个数据库只能指定一个文件组为默认文件组。
      • 如果用户没有显式地用ALTER DATABASE 语句修改默认文件组,则SQL Server 的默认文件组是PRIMARY。
  • 数据库文件的属性
    • 文件名及其位置
      • 数据库的每个数据文件和目志文件都具有一个逻辑文件名和物理文件名。
      • 逻辑文件名是在所有T-SQL语句中引用物理文件时所使用的名称,该文件名必须符合SQL Server 标识符规则，而且在一个数据库中逻辑文件名必须是唯一的。
      • 物理文件名包括存储文件的路径和物理磁盘文件名,该文件名必须符合操作系统文件命名规则。
    • 初始大小
      • 可以指定每个数据文件和日志文件的初始大小。
      • 在指定主要数据文件的初始大小时,其大小不能小于model数据库主要数据文件的大小,因为系统是将model数据库主要数据文件的内容复制到用户数据库的主要数据文件上。
    • 增长方式
      • 如果需要的话,可以指定文件是否自动增长。该选项的默认配置为自动增长
    • 最大大小
      • 文件的最大大小指的是文件增长的最大空间限制。默认情况是无限制。
  • 用T-SQL语句创建数据库
    • 创建数据库的T-SQL语句为CREATE DATABASE,此语的简化语法格式为:
      CREATE DATABASE database_name
      [ON
      [ PRIMARY ] [ <filespec> [ , …n]
      [，< filegroup > [ ,…n] ]
      [ LOG ON [ <filespec> [ , …n] ] ]
  • 修改数据库
    • 扩大数据库空间
      • 扩大数据库空间有两种方法,一种是扩大数据库中已有文件的大小,另一种是为数据库添加新的文件。
      • 扩大数据库空间的ALTER DATABASE ，其语法格式为:

        

        
        各参数含义如下:
        database_name：要修改的数据库名
        <add_or_modify_files>::=：指定要添加或修改的文件
        ADD FILE: 在数据库中添加新的数据文件。
        TO FILE GROUP { filegroup_name | DEFAULT}: 说明要将指定文件添加到的文件组。如果指定了DEFAULT,则将文件添加到当前的默认文件组中。
        <filespec>: 同CREATE DATABASE语句的<filespec>
        ADD LOG FILE: 在数据库中添加新的目志文件
        MODIFY FILE: 指定要修改的文件。一次只能更改一个<filespec>属性。必须在<filespec>中指定NAME,以标识要修改的文件。如果指定了SIZE,则新大小必须大于文件当前大小。通过MODIFY FILE选项还可以修改数据文件或日志文件的逻辑名称,可以将数据文件或日志文件移动到新的位置。
    • 收缩数据库空间
      • 收缩数据库就是释放数据库中未使用的空间,并将释放的空间交还给操作系统。
      • 文件的收缩都是从末尾开始的。
      • 手工收缩数据库空间分为两种情况,一种是收缩数据库中某个文件的大小,另一种是按比例收缩整个数据库的大小。注意,当收缩整个数据库空间大小时,收缩后各文件的大小不能小于创建这些文件时指定的初始大小,或者是上一次进行收缩文件操作时设置的大小。若是收缩某个文件的大小则无此限制。
      • (1)收缩整个数据库的大小。收缩整个数据库大小的T-SQL语是DBCC ，SHRINK DATABASE,其语法格式为:

        

        
        各参数含义如下:
        'daltabase_name' | database_id | 0：要收缩的数据库的名称或ID。如果指定0,则表示收缩当前正在使用的数据库。
        target_percent：数据库收缩后的文件中所需的剩余可用空间百分比。
        ​NOTRUNCATE:在数据库文件中保留所释放的文件空间。如果未指定该选项,则将所释放的空间释放给操作系统。
        TRUNCATEONLY:将文件中任何未使用的空间均释放给操作系统,并将文件收缩到最后分配的大小,从而无须移动任何数据即可减小文件大小。使用TRUNCATEONLY时,将忽略larget_percent 选项。
      • (2)收缩指定文件的大小。收缩指定文件大小的T-SQL语句是DBCC SHRINKFILE,其语法格式为:

      • 

      • 添加和删除数据库文件
        • 可以通过在数据库中添加文件的方法来扩大数据库空间,也可以通过删除文件的方法来减小数据库空间。
        • (1)添加文件。SQLServer对每个文件组中的所有数据文件都是按比例填充数据的,这使得各文件中存储的数据量与文件中的可用空间成正比,这种方式使得所有数据文件几乎同时被填满。
        • (2)删除文件。只有当文件完全为空时,才可以从数据库中删除文件。
        • 删除数据库文件的T-SQL语句是ALTER DATABASE,其语法格式为:
          ALTER DATABASE database_name
          REMOVE FILE logical _file_name
          • 各参数含义如下:
          • database_name:要删除文件的数据库名
          • logical_fille_name:被删除文件的逻辑文件名。
  • 分离和附加数据库
    • 通过分离和附加数据库的操作可以实现将数据库从一台数据库服务器移动到另一台数据库服务器,而不需要重新创建数据库的目的。
    • 分离数据库
      • 分离数据库是指将数据库从SQL Server实例中删除,但不删除数据库的数据文件和日志文件。这与删除数据库不同,删除数据库会将数据库的所有文件一起删除,而分离数据库会保持数据库的数据文件和日志文件的完整和一致。
      • 分离数据库使用的是sp_detach_db系统存储过程,其简化语法格式为:
        sp_detach_db [ @ dbname= ] 'dbname'
        [ , [@ skipchecks = ]'skipchceks']
    • 附加数据库
      • 附加数据库就是将分离的数据库重新附加到数据库管理系统中,可以附加到本机的另一个SQL Server实例上,也可以附加到另一台数据库服务器上。
      • 在附加数据库之前,应先将要附加的数据库所包含的全部数据文件和日志文件放置到合适的位置。
      • 附加数据库的T-SQL语句是CREATE DATABASE,语法格式如下:
        CREATE DATABASE database_name
        ON <filespec> [ , …n ]
        FOR [ ATTACH | ATTACH_REBUILD_LOG ]
        • 各参数说明如下:
        • database_name: 要附加的数据库名。
        • <filespec> : 同创建数据库语句的 <filespec>,用于指定要附加的数据库的主要数据文件。
        • FOR ATTACH: 指定通过附加一组现有的操作系统文件来创建数据库。必须有一个指定主要数据文件的 <filespec>项。至于其他文件的 <filespec>项,只需指定与第一次创建数据库或上一次附加数据库时路径不同的文件即可
        • FORATTACH_REBUILD_LOG:指定通过附加一组现有的操作系统文件来创建数据库该选项只限于可读/写的数据库。如果缺少目志文件,则将重新生成日志文件
• 架构
  • 架构(Schema,也称为模式)是数据库下的一个逻辑命名空间,可以存放表、视图等数据库对象,它是一个数据库对象的容器。
  • 一个数据库可以包含一个或多个架构,架构由特定的授权用户所拥有。在同一个数据库中,架构名必须唯一。属于一个架构的对象称为架构对象,即它们依赖于该架构。架构对象的类型包括基本表、视图、触发器等
  • 一个架构可以由零个或多个架构对象组成。架构名可以是显式的,也可以是由DBMS提供的默认名。
  • 定义架构
    • 定义架构的SQL语句为CREATE SCHEMA,其语法格式如下:
      CREATE SCHEMA [ <架构名>] AUTHORIZATION <用户名>
    • 如果没有指定<架构名>,则<架构名>隐含为<用户名>。
    • 一个<用户名>可以拥有多个架构执行创建架构语句的用户必须具有数据库管理员的权限,或者是获得了数据库管理员授予的CREATE SCHEMA 的权限。
  • 删除架构
    • 删除架构的T-SQL语句是DROP SCHEMA ,其语法格式如下:
      DROP SCHEMA <架构名> | <CASCADE> | <RESTRICT>
    • 其中:
    • CASCADE: 删除架构的同时将该架构中的所有架构对象一起全部删除。
    • RESTRICT选项: 如果被删除的架构中包含架构对象,则拒绝删除此架构。
• 分区表
  • 基本概念
    • 分区表是将表中的数据按水平方式划分成不同的子集,这些数据子集存储在数据库的一个或多个文件组中。
    • 通常,如果某个大型表同时满足下列条件,则比较适于进行分区:
      • 该表包含(或将包含)以多种不同方式使用的大量数据
      • 数据是分段的,比如数据以年份分隔。
      • 然而,数据量大并不是创建分区表的唯一条件。
    • 分区表是从物理上将一个大表分成几个小表,但从逻辑上来看还是一个大表。
  • 创建分区表
    • 在SQL Server 2008中,创建分区表通过以下几个步骤实现:
      • (1)创建分区函数。创建分区函数的目的是告诉数据库管理系统以什么方式对表进行分区。
        • CREATE PARTITION FUNCTlON partition_function_name ( input_parameter_type )
        • AS RANGE [ LEFT | RIGHT ]
        • FOR VALUES ( [boundary_value [ , .…n ] } )
        • [ ; ]
        • 说明:
          • 当为按左侧分区时，右侧包含等于值，eg：分区范围为(1, 100, 1000),分区情况为：

            

          • (1)分区函数的作用域仅限于创建该分区函数的数据库。
          • (2)分区列为空值的所有行都放在最左侧的分区中,除非将NUL指定为边界值并指定了RIGHT。在这种情况下,最左侧分区为空分区,NULL值被放置在后面的分区中。
      • (2)创建分区方案。分区方案的作用是将分区雨数生成的分区映射到文件组中。分区数的作用是告诉SQL Server如何将数据进行分区,而分区方案的作用则是告诉SQL Server 将已分区的数据放在哪个文件组中。

        

      • (3)使用分区方案创建表。
• 索引
  • 创建索引
    • 创建索引使用的是CREATE INDEX语句。
      CREATE [ UNIQUE ] [CLUSTERED I NONCLUSTERED ] INDEX index_name
      ON <object> (column [ASC | DESC [ , .…n] )
      [INCLUDE (column_name[ , …n] )
      [WHERE <filter_predicate> ]
      [ ON { partition_seheme_name ( column_name )
      | filegroup_name
      | default
      }
      ]
      [FILESTREAM _ON { filestream_filegroup_name l partition_seheme_name | " NULL" } ]
      [ ; ]
      <object> : :=
      {
      [ database_name . [schema_name], | schema_name] table_or_view_name
      ]
  • 删除索引
    • 在SQL语言中,删除索引使用的是DROP INDEX 语句
• 索引视图
  • 基本概念
    • 标准视图也称为虚拟表,因为这种视图所返回的结果集的格式与基本表相同,都是由列和行组成,而且在SQL语句中引用视图的方式也与引用基本表的方式相同
    • 标准视图的结果集并不永久地存储在数据库中
    • 建有唯一聚集索引的视图称为索引视图,也称为物化视图。
  • 适合建立索引视图的场合
    • 如果很少更新基础数据,则索引视图的效果最佳。
    • 如果经常更新基础数据,则维护索引视图的成本可能超过使用索引视图带来的性能收益。
    • 如果基础数据以批处理的形式定期更新,但在更新之间主要作为只读数据进行处理,则可考虑在更新前删除所有索引视图,然后再重建索引视图,这样做可以提高更新的性能
  • 索引视图可以提高下列查询类型的性能:
    • 处理大量行的连接和聚合。
    • 许多查询经常执行的连接和聚合操作。
  • 索引视图通常不会提高下列查询类型的性能:
    • 具有大量写操作的OLTP系统。
    • 具有大量更新操作的数据库。
    • 不涉及聚合或连接的查询。
    • GROUP BY列具有高基数度的数据聚合。高基数度表示列包含许多不同的值。
  • 定义索引视图
    • 在对视图创建聚集索引之前,该视图必须符合下列要求:
      • 定义索引视图时,视图不能引用任何其他视图,只能引用基本表。
      • 视图引用的所有基本表必须与视图位于同一个数据库中,并且所有者也与视图相同。
      • 必须使用SCHEMABINDING选项创建视图。
      • 视图中的表达式引用的所有函数必须是确定的。对视图创建的第一个索引必须是唯一聚集索引,之后再创建其他的非聚集索引。