sql server课件第3章
1第3章 SQL Server 2019 数据库本章主要介绍以下内容:3.1 系统数据库3.2 数据库文件及文件组3.3 创建数据库3.4 设置数据库选项3.5 管理数据库3.6 数据库快照概述数据库是为了满足多个用户的多种应用的需要，按照一定的数据模型在计算机系统中组数据库是为了满足多个用户的多种应用的需要，按照一定的数据模型在计算机系统中组织、存储和使用的互相联系的数据集合织、存储和使用的互相联系的数据集合。如在电子商务网站中，需要存储和管理的客户信息、订单信息、产品信息等数据，在业务处理过程中是一组相互关联的数据，可以保存在一个数据库中；而与学校管理相关的学生信息、成绩信息、课程信息等数据，是另一组相互关联的数据，可以保存在另一个数据库中。在SQL Server中，数据库所包含的内容不仅仅只是数据，还包括与数据管理和操作相关的各种信息，如：数据库关系图、表、视图、同义词、可编程性、Service Broker、存储、安全性等对象，而通常所说的数据则保存在其中的一个数据库对象：数据表中。由此可见，数据库这一概念在SQL Server中已成为一个存储数据库对象的容器。3.1 SQL Server 2019的系统数据库 在SQL Server 2019中数据库分为两大类：系统数据库系统数据库和用户数据库用户数据库。系统数据库用于保存系统运行所需的各种数据，包括用户数据库信息和其他系统性信息。用户数据库是由用户创建的，用于保存某些特定信息的数据库。系统数据库由SQL Server系统预设。在SQL Server 2019安装完成后，就默认创建了五个系统数据库：master、model、msdb、tempdb和Resource。3.1.1 master master数据库是SQL Server系统中最重要的系统数据库，记录了SQL Server系统运行所需的系统信息。这些系统信息包括：所有登录名和用户ID及所属角色所有的系统配置信息（如数据排序规则、安全规则等）服务器中其他系统数据和用户数据等信息，如：数据库的名称、数据库文件的物理位置等SQL Server的初始化信息各种特殊的系统表，如存储缓存使用规则、可用字符集、可用语言列表、系统错误和警告信息等的数据表。3.1.2 model model 数据库是模板数据库。在 SQL Server中创建用户数据库时，都会以model数据库为模板，创建拥有相同对象和结构的数据库。该模板数据库的结构如图所示。如果修改 model 数据库，之后创建的所有数据库都将继承这些修改。因此，如果希望新创建的数据库都有具有相同的特性，如希望在所有新创建的数据库中都建有某个相同的数据表，那么可以预先把这个表建在model数据库中。3.1.3 msdb nmsdb数据库是存储代理服务信息的数据库。nSQL Server代理服务运行所需的作业信息，如作业运行的时间、频率、操作步骤、警报等信息都保存在msdb数据库中。3.1.4 tempdb ntempdb是一个临时数据库。当每次SQL Server服务重新启动时，会创建一个空的tempdb数据库；在SQL Server服务停止或关闭时，tempdb数据库会丢失。ntempdb数据库用于保存SQL Server运行过程中产生的需要临时存储的数据。用户创建的临时表，也会存放在tempdb中。3.1.5 Resource Resource是自SQL Server 2005版起新增的一个系统数据库。在SQL Server 2005版以前，所有可执行的系统对象都存储在master数据库中。这些可执行系统对象是指不存储数据的系统对象，包括：存储过程、视图、函数、触发器等。如在SQL Server 2000版的master数据库中，系统对象就有约1200个。在SQL Server 2005中，这类对象只有约70个，原因是上述很多对象已被存放到了Resource数据库中。3.2 3.2 数据库文件及文件组数据库文件及文件组3.2.1 SQL Server数据存储原理1、数据存取过程SQL Server为提高数据存储的可靠性，采用了优先写日志的方式。即在SQL Server中存储数据的文件除了数据文件外，增加了事务日志文件。数据文件用于保存数据，日志文件用于保存各种操作事务，如修改、新增数据的事务。3.2 数据库文件及文件组3.2.1 SQL Server数据存储原理 2、存储空间分配SQL Server在存储空间分配中使用了较小的数据存储单元，即页页和盘区盘区。页是SQL Server数据文件存储的最小单位，页的大小为8192字节，即8KB。其中96Byte用于保存头部信息，用于记录此页的相关信息，另外在页尾存储用于记录数据行位置的行偏移和其他一些信息。因此，一页实际可保存的数据量为8060Byte。根据页保存数据类型的不同，页可以划分成为：数据页数据页、全局分配图页全局分配图页、索引页索引页、索引分配图页索引分配图页、页面自页面自由空间页由空间页和文本文本/图像页图像页。盘区是连续8个页的集合,盘区根据实际保存数据的不同，可以划分成为两类：单一盘区单一盘区（也称统一盘区）和混合盘区混合盘区（也称混合区）。单一盘区中所存放的数据为一个数据对象所有，如某盘区8个页，存放的都是“数据表1”的数据；混合盘区存放的数据来自多个对象，如有“数据表1”和“数据表2”的数据等。当混合区中的表或索引的大小增长到8页时，系统会将表或索引存放到专门的单一盘区中，以提高访问的效率。10存储空间分配日志文件:.ldf数据文件:.mdf or.ndf区:8 个连续的 8 KB 页页:8 KB2023/11/8103.2.2 SQL Server 数据库文件 SQL Server采用两类文件来保存数据：数据文件数据文件和事务日志文件事务日志文件。数据文件存储数据，事务日志文件记录各种对数据库的操作；数据文件还可往下分为两类：主数据文件主数据文件和辅助数辅助数据文件据文件。1、主数据文件主数据文件（Primary Data File，扩展名为MDF）是SQL Server数据库中最重要的文件，每个SQL Server数据库有且仅有一个主数据文件。在主数据文件中可以保存SQL Server数据库中的所有数据，包括用户对象和系统对象（如系统表）。2、辅助数据文件辅助数据文件（Secondary Data File，扩展名为NDF），也称为次数据文件，在SQL Server中用于保存用户数据，比如用户数据表、用户视图等；但是不能保存系统数据。与主数据文件在SQL Server数据库中有且只能有一个不同，辅助数据文件在一个数据库中可以有多个，一个数据库最多可以有32767个辅助数据文件。3.2.2 SQL Server数据库文件3、事务日志文件事务日志文件（Log File，扩展名为：LDF），是SQL Server数据库中用于记录操作事务的文件。在SQL Server数据库中，事务日志文件也是不可缺少的数据库文件。但与主数据文件在每个数据库中只能有一个不同，事务日志文件可以有多个，最多可达32767个。为提高系统的可靠性和安全性，可以将事务日志文件与主数据文件分别存放在事务日志文件与主数据文件分别存放在不同分区不同分区，如果有多个硬盘，建议存放在不同硬盘中。3.2.3 3.2.3 文件组文件组 在数据文件或事务日志文件数量较多的场合，可以通过文件组对数据文件和事务日志文件进行分组管理。文件组就是文件的逻辑分组。在SQL Server 2019中文件组可以划分为两大类：主文件组主文件组（Primary File Group）、次文件组次文件组（Secondary File Group）。还有一种特殊的文件组是默认文件组默认文件组（Default File Group）。主文件组是每个数据库默认提供的文件组，该文件组不能被删除。主数据文件只能置于主文件组中。次文件组是由用户创建的文件组，在一个数据库中用户可以根据管理需要创建多个次文件组。次文件组也被称为用户定义文件组（User-defined File Group）默认文件组是在新增数据库文件时，如果未明确指定该数据文件所属的文件组，那么该数据文件就会被放置在默认文件组中。系统缺省的默认文件组对应主文件组，但可以修改，如可以将某个用户文件组设置为默认文件组。14AdventureWorks 数据库数据库文件组默认文件组默认文件组OrderHistoryGroupE:C:D:AdventureWorks_Log.IdfAdventureWorks_Data.mdfOrdHist2.ndfOrdHist1.ndfsys.sys.sysusers sysobjects .SalesOrderHeader Customer Product OrdHistYear2 OrdHistYear1 3.3创建数据库 3.3.1 使用SSMS创建数据库 DEMO3.33.3创建数据库创建数据库3.3.2 3.3.2 使用使用TSQLTSQL语句创建数据库语句创建数据库 创建数据库的TSQL语句为“CREATE DATABASECREATE DATABASE”，该语句的基本语法如下：CREATE DATABASE database_name ON PRIMARY ,.n ,.n LOG ON ,.n COLLATE collation_name WITH ;:=(NAME=logical_file_name,FILENAME=os_file_name|filestream_path ,SIZE=size KB|MB|GB|TB ,MAXSIZE=max_size KB|MB|GB|TB|UNLIMITED ,FILEGROWTH=growth_increment KB|MB|GB|TB|%),.n :=FILEGROUP filegroup_name CONTAINS FILESTREAM DEFAULT ,.n 主要关键词含义如下：database_name，指定新创建的数据库的名称，可长达128个字符。PRIMARY，指定主数据文件的名称及路径。LOG ON，指定事务日志文件的名称及路径。NAME，指定数据库文件的逻辑名称，是数据库文件在SQL Server中的标识符，与图3-4数据库文件列表中的“逻辑名称”对应。FILENAME，指定数据库文件在操作系统中的文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。SIZE，指定数据库文件的初始存储空间大小。MAXSIZE，指定数据库文件的最大可用存储空间大小。FILEGROWTH，指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。例如：例如：CREATE DATABASE CateringNONPRIMARY(NAME=CateringN_Data,filename=c:dataCateringN_Data.mdf,Size=3MB,maxsize=20MB,fileGrowth=1MB),(NAME=CateringN_Data_1,filename=C:dataCateringN_Data1.ndf,size=1MB,maxsize=20MB,filegrowth=2MB),(NAME=CateringN_Data_2,filename=C:dataCateringN_Data2.ndf,size=2MB,maxsize=20MB,filegrowth=2MB)Log on(NAME=CateringN_Log,filename=c:dataCateringN_Log.ldf,size=1MB,maxsize=20MB,filegrowth=10%)Go3.4 设置数据库选项 通过对数据库配置选项，可以定制数据库特性。在SQL Server 2019提供了50多个数据库选项，包括：排序规则、恢复模式、兼容级别、页验证、默认游标、ANSI NULL默认值、ANSI_NULLS已启用、数据库
