第2章 第3节.docx

第2章 第3节.docx

《第2章 第3节.docx》由会员分享，可在线阅读，更多相关《第2章 第3节.docx（22页珍藏版）》请在冰点文库上搜索。

第2章第3节

第二章SQLServer数据库管理与开发

第三节SQLServer2005数据库概述

3.1再谈数据库的定义

数据库（database）是对象的容器，以操作系统文件的形式存储在磁盘上。

它不仅可以存储数据，而且能够使数据存储和检索以安全可靠的方式进行。

一般包含关系图、表、视图、存储过程、用户、角色、规则、默认、用户自定义数据类型和用户自定义函数等对象。

3.2SQLServer2005数据库

SQLServer数据库分为：

系统数据库、实例数据库和用户数据库。

3.2.1系统数据库

1什么是系统数据库？

所谓系统数据库是指随安装程序一起安装，用于协助SQLServer2005系统共同完成管理操作的数据库。

（1）Master数据库

记录SQLServer2005实例的所有系统级信息，系统配置设置，不能直接修改。

（2）Tempdb数据库

是连接到SQLServer2005的所有用户都可用的全局资源，它保存所有的临时表和临时存储过程，SQLServer2005关闭后该数据库清空。

（3）Model数据库

用作SQLServer2005实例上创建所有数据库的模板。

对model数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据。

（4）Msdb数据库

被SQLServer2005代理用于进行复制、作业调度、管理警报等活动，是SQLServer中的一个Windows服务。

（5）Resource数据库

一个只读数据库，包含SQLServer2005包括的系统对象。

系统对象在物理上保留在Resource数据库中，但在逻辑上显示在每个数据库的sys架构中。

2.示例数据库

AdventureWorks/AdventureWorksDW是SQLServer2005中的示例数据库（如果在安装过程中选择安装了的话）。

此数据库基于一个生产公司，以简单、易于理解的方式来展示SQLServer2005的新功能。

3.用户数据库

用户根据数据库设计创建的数据库。

如山东大学威海分校教务管理数据库（WHEDUC），图书管理数据库（Library）。

3.3文件和文件组

每个SQLServer2005数据库至少具有两个操作系统文件：

一个数据文件、一个日志文件。

数据文件包含数据和对象。

例如表、索引、存储过程和视图。

日志文件包含恢复数据中所有事务所需的信息。

为了便于管理和分配，可将数据文件集合起来放在文件组中。

3.3.1数据库文件

主要包括三种类型的文件。

1主数据文件（.mdf）

主数据文件包含数据库的启动信息，并指向数据库中的其他文件；

用户数据和对象可存储在此文件中也可存在次要数据文件中；

每个数据库有且仅有一个主数据文件。

2次数据文件（.ndf）

该文件是可选的，由用户定义并存储主数据文件未存储的其他数据和对象；

可用于将数据分散到多个磁盘上。

如果数据库超过了单个Windows文件的最大范围，可以使用次数据文件，这样数据库就能继续增长；

可以没有也可以有多个；该文件可选

名字尽量与主数据文件名相同。

3事务日志文件（.ldf）

保存用于恢复数据库的日志信息；

每个数据库至少有一个日志文件，也可以有多个。

3.3.2数据库文件组

1为什么引入文件组：

为了便于分配和管理，SQLServer2005允许将多个文件（不同的磁盘）归纳为同一组，并赋予此组一个名称；

2分类：

与数据库文件一样，文件组也分为主文件组（PrimaryFileGroup）和次文件组（SecondaryFileGroup）；

3主文件组：

每个数据库有一个主文件组。

其包含系统表和主数据文件和未放入其他文件组的所有次数据文件，是默认的数据文件组。

次文件组：

用于将数据文件集合起来，以便于数据管理、分配、放置。

如，分别在3个磁盘驱动器上创建3个文件data1ndf,data2.ndf,data3.ndf。

将它们分配给文件组Fgroup。

然后可以明确的在文件组Fgroup上建一个表。

对表中的数据的查询将分散到3个磁盘上，从而提高性能。

注意：

Ø一个文件或者文件组只能用于一个数据库，不能用于多个数据库。

Ø一个文件只能是某一个文件组的成员，不能是多个文件组的成员。

Ø数据库的数据信息和日志信息不能放在同一个文件或文件组中。

数据文件和日志文件总是分开的。

Ø日志文件永远也不能是任何文件组的一部分。

也就是文件组只能包含数据文件。

3.4数据库状态和文件状态

SQLServer2005数据库必须处于某个特定的状态中，数据库文件也有状态，并且该文件始终处于一个特定的、独立于数据库的状态。

如：

RESTORING状态表示数据库正处于还原状态，此时数据库暂不能使用。

其他状态同学自己看一下。

3.5SQLServer2005数据库组成部分

前面提到，SQLServer2005数据库一般包含表、视图、存储过程、触发器、用户、角色、规则、默认、用户自定义数据类型和用户自定义函数等对象。

这里就做介绍。

数据表，这里简称“表”，是包含数据库中所有数据的数据库对象。

表定义为一个列集合。

数据在表中的组织方式与在电子表格中相似，都是按行和列的格式组织的。

每一行代表一条唯一的记录，每一列代表记录中的一个字段。

视图是一个虚拟表，是从一个或多个表中导出的表。

同真实的表一样，视图包含一系列带有名称的列和行数据。

视图并不直接存储数据，而是“引用”了数据表中的数据，因此可以使连接多张数据表的虚表。

在数据库中仅对视图的定义进行了存储，没有存储对应的数据。

除非是SQLServer2005数据库中物化的索引视图。

存储过程和触发器是两个特殊的数据库对象。

在SQLServer2005中，存储过程的存在独立于表，而触发器则与表紧密结合。

存储过程，就是一个过程，和其他变成语言中的过程类似。

有以下几点：

✓依靠参数接收输入值，依靠参数返回值。

✓包含用于在数据库中执行操作（包括调用其他过程）的编程语句。

也就是过程可以不返回值，而只执行某个动作、操作。

✓向调用过程或批处理返回状态值，以指明成功或失败。

存储过程和函数不同，函数靠函数名带回返回值，函数可直接在表达式中使用，过程不行。

触发器是一种特殊类型的存储过程。

在执行语言事件执行时自动生效。

是只能存在与表工作框中的对象。

触发器是表中发生某种动作（如插入、更新、删除）时自动执行的一些逻辑代码。

主要用于强制复杂的业务规则或要求。

例如，可以控制是否允许基于顾客的当前帐户状态插入定单。

触发器还有助于强制引用完整性，自动触发的意味，细细体会一下。

关系图是SQLServer7.0增加了绘制数据关系图功能。

描述数据库中部分或全部表、列、键和关系。

就是描述关系之间的联系的图。

用户和角色中用户是对数据库有存取权限的使用者。

角色是指一组数据库用户的集合，当用户加入某一角色时，就具有该角色的所有权限。

规则用来限制表字段的数据范围，如限制分数字段只能是在0～100范围内。

默认值，如果用户在插入行时没有为列指定值，默认值则指定列中使用什么值。

默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。

数据类型，在SQLServer2005中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。

数据类型是一种属性，用于指定对象可保存的数据的类型：

整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。

3.6创建数据库

SQLServer2005创建数据库的方法有两种：

使用SSMS图形界面SQLServer2005managementstudio；使用T-SQL语言

3.6.1使用SSMS创建数据库

（1）打开SQLServer2005managementstudio，并用windows或SQLServer身份验证建立连接，在【对象资源管理器】窗口中，右击“数据库”文件夹，从弹出的快捷菜单中选择“新建数据库”选项，如下图所示。

（2）在窗口中根据提示输入该数据库的相关内容，如数据库名称、所有者、文件初始大小、自动增长值和保存路径等。

例如：

创建教务管理数据库，数据库名称EDUC。

主数据文件保存路径F:

\SQL\山东大学威海分校教务管理系统数据文件，日志文件保存路径F:

\SQL\山东大学威海分校教务管理系统日志文件注意数据和日志文件最好不要保存在同一个驱动器，我这里放在同一个。

主数据文件初始大小为3MB，最大尺寸为10MB，增长速度为10%；日志文件的初始大小为1MB，最大尺寸为2MB，增长速度为10%。

注意：

数据文件应该尽量不保存在系统盘上并与日志文件保存在不同的磁盘区域。

数据库名称：

可以使用字母、数字、下划线或短线。

例如：

EDUC

所有者：

数据库的所有者可以是任何具有创建数据库权限的登录名。

例如：

选择其为<默认值>账户，该账户是当前登录到SQLServer上的账户。

忽略“使用全文索引”复选框：

如果想让数据库具有能搜索特定的词或短语的列，则选中此选项。

例如，搜索引擎可能有一个列，列中包含来自网页的一组短语，可以用全文搜索来找到哪些页面包含正在搜索的词。

文件名（窗口右侧没显示出的部分）：

用于存储数据库中数据的物理文件的名称，默认情况下，SQLServer用数据库名称加上_Data后缀来创建物理文件名。

例如：

EDUC_Data。

2005与SQLServer2000不同，在默认的情况下不再为用户输入的文件名添加下划线和data字样

数据库文件逻辑名称：

引用文件时使用。

指定该文件的文件名，其中数据文件与SQLServer2000不同，在默认的情况下不再为用户输入的文件名添加下划线和data字样，相应的扩展名并未改变。

文件类型：

显示文件是数据文件，还是日志文件，数据文件用来存放数据，而日志文件用来存放对数据所做操作的记录。

文件组：

为数据库中的文件指定文件组，主文件组（PRIMARY）或任一辅助文件组（SECONDARY）。

所有数据库都必须有一个主文件组。

初始大小：

制定该文件的初始容量，2005中数据文件的默认值是3MB，日志文件默认值是1MB。

自动增长：

显示SQLServer是否能在数据库到达其初始大小极限时自动应对。

单击右边带有省略号（…）的命令按钮，如下图所示，设置是否启动自动，文件增长方式，最大文件大小。

默认是“不限制文件增长”，其处是可以不必过分担心数据库的维护，但如果一段“危险”的代码引起了数据的无限循环，硬盘可能会被填满。

因此，当一个数据库系统要应用到生产环境中时，应设置“限制文件增长（MB）”选项以防止出现上述的情形。

可以创建次数据文件来分担主数据文件的增长。

例如：

文件按10%的比例增长，限制最大文件大小为10MB。

路径：

数据库文件存放的物理位置，默认的路径是C:

\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data。

单击右边带有省略号（…）的命令按钮，打开一个资源管理器风格的对话框，可以在该对话框中更改数据库文件的位置。

文件类型选项为“日志”的行与为“数据”的行所包含的信息差不多，只有一两处很小的不同。

这里，“文件名”是通过在数据库名称后面加_log后缀而得到的，并且，不能修改“文件组”列，因为事务日志中实际上没有系统表，所以它只可能填满操作记录。

可以定义多个日志文件位置。

若填满了事务日志，会因为日志满而导致SQLServer停止处理，因此将不能处理更多的信息。

指定多个日志位置则可以避免这种情况。

在大型生产系统中采用故障转移日志文件是可取的。

（3）在选项页框中，如下图所示，可设置数据库的排序规则，恢复模式，兼容级别以及其他一些选项的设置。

（4）在文件组页框中，如下图所示，可设置或添加数据库文件和文件组的属性，如是否只读，是否为默认值等。

（5）单击【确定】按钮，系统开始创建数据库，创建成功后，当回到SSMS中的对象资源管理器时，刷新其中的内容，在【对象资源管理器】的【数据库】节点中就会显示新创建的数据库WHEDUC，如下图所示。

3.6.2使用T-SQL语句创建数据库

1语法格式：

CREATEDATABASEdatabase_name

[ON[PRIMARY][[，…n][，[，…n]]]

[LOGON{[，…n]}]

[FORRESTORE]

:

:

=（[NAME=logical_file_name，]

FILENAME=‘os_file_name’

[，SIZE=size]

[，MAXSIZE={max_size|UNLIMITED}]

[，FILEGROWTH=growth_increment]）[，…n]

:

:

=FILEGROUPfilegroup_name[，…n]

2各参数说明如下：

✓[]中的内容表示可省。

【1,…n】表示同样的选项可以重复1－n次。

✓<>中的内容表示因内容太多需要进行额外说明，如句法中的和,该项的真正语法在:

:

=后定义。

✓大括号{}通常会和|符号连用，表示{}中的选项或参数必须选择其中之一，不可省略。

✓database_name：

数据库的名称，最长为128个字符。

✓PRIMARY：

该选项是一个关键字，指定主文件组中的文件。

✓LOGON：

指明事务日志文件的明确定义。

✓NAME：

指定数据库的逻辑名称，这是在SQLServer系统中使用的名称，是数据库在SQLServer中的标识符。

✓FILENAME：

指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。

✓SIZE：

指定数据库的初始容量大小，至少为模板Model数据库大小。

✓MAXSIZE：

指定操作系统文件可以增长到的最大尺寸。

如果没有指定，则文件可以不断增长直到充满磁盘。

✓FILEGROWTH：

指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。

例：

创建了一个Test数据库，该数据库的主数据文件逻辑名称为Test_data，物理文件名为Test.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Test_log，物理文件名为Test.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。

CREATEDATABASEtest

ONPRIMARY--建立主数据文件

（NAME='test',--逻辑文件名

FILENAME='E:

\SQL\test.mdf',--物理文件路径和名字

SIZE=10240KB,--初始大小

MAXSIZE=UNLIMITED,--最大尺寸为无限大

FILEGROWTH=10%）--增长速度为10%

LOGON

（NAME='test_log',--建立日志文件

FILENAME='F:

\SQL\test_log.ldf',--物理文件路径和名字

SIZE=1024KB,

MAXSIZE=5120KB,

FILEGROWTH=1024KB

）

例：

创建图书管理数据库Library。

CREATEDATABASELibrary

On

（NAME=Library,

FILENAKME='E:

\图书管理数据\Library_data.mdf',

SIZE=3,

MAXSIZE=10,

FILEGROWTH=10%

）

LOGON

（NAME=Library_log,

FILENAME='F:

\图书管理日志\Library_log.ldf',

SIZE=1,

MAXSIZE=2,

FILEGROWTH=10%

）

3创建数据库步骤：

1打开SQLServerManagementStudio窗口，并连接到服务器。

2选择“文件”－>“新建”－>“数据库引擎查询”命令或单击“新建查询”按钮，创建一个查询输入窗口。

3在窗口内输入上例中语句，创建数据库，

4单击“执行”按钮，若执行成功，则在查询窗口内的“查询”窗格中，看到“命令已成功完成”。

之后在“对象资源管理器”窗格中刷新，展开数据库节点就可看到刚创建的数据库。

注意：

使用CREATEDATABASE语句创建数据库最简单的方式如下：

CREATEDATABASEdatabaseName

这种方式只需指定databaseName即可，就是所要创建的数据库名，其他相关设置都系统默认值。

如CREATEDATABASE工资管理系统

3.7操作数据库

3.7.1使用SSMS查看或修改数据库

右击所要修改的数据库->选择“属性”选项->出现如下图所示的数据库属性设置对话框。

可以看到，修改或查看数据库属性时，属性页框比创建数据库时多了两个，即选项和权限页框。

可以分别在常规、文件、文件组、选项和权限对话框里根据要求来查看或修改数据库的相应设置。

3.7.2查看数据库

1目录视图查看数据库

什么是目录视图？

目录视图返回SQLServer数据库引擎使用的信息。

目录视图是系统视图的组成部份，作为访问系统元数据的一般接口。

是MicrosoftSQLServer提供公开元数据的系统视图之一。

系统视图就是MicrosoftSQLServer提供公开元数据的系统视图集合。

目录视图都处于sys架构（schema）下，因此必须引用架构名来访问这些对象。

这其中一些名字很好记，因为它们与SQLServer2000系统表的名称相似。

✓使用目录视图查看数据库

查看数据库基本信息操作有：

●使用sys.databases数据库和文件目录视图查看有关数据库的基本信息。

●使用sys.database_files查看有关数据库文件的信息。

●使用sys.filegroups查看有关数据库组的信息。

●使用sys.master_files查看数据库文件的基本信息和状态信息。

✓查看目录视图的方式：

查看目录视图的信息用如下语句：

SELECT*FROMsys.databases--查询sys.databases视图信息

SELECT*FROMsys.objects--查询sys.objects视图信息

例1

结果

例2

结果

查看sys.objects的定义，执行以下语句：

SELECTobject_definition（object_id（'sys.tables'））

例

结果

作为视图，这些元数据对象都是基于Transact-SQL定义的。

查看这些视图定义最直接的方式就是使用object_definition函数

补充：

有兴趣的同学可以看看，加深对目录视图理解

以下目录视图包含有关单个分区函数的信息。

1、获取有关单个分区函数的信息

sys.partition_functions

每个分区函数都在表中占一行。

列名——数据类型——说明

name——sysname——分区函数的名称。

在该数据库中是唯一的。

function_id——int——分区函数ID。

在数据库内是唯一的。

type——char

（2）——函数类型。

R=范围

type_desc——nvarchar（60）——函数类型RANGE

fanout——int——函数创建的分区数。

boundary_value_on_right——bit——用于区域划分。

1=边界值包括在边界的RIGHT区域内。

0=边界值包括在边界的LEFT区域中。

create_date——datetime——函数的创建日期。

modify_date——datetime——上次使用ALTER语句修改函数的日期。

2使用函数查看数据库

若我们查看指定数据库的指定选项信息，可使用databasepropertyex函数，该函数一次返回一个选项的设置。

例查看EDUC数据库中是否允许更新数据库选项read_only设置信息，语法：

SELECTdatabasepropertyex（'EDUC','read_only'）

3使用存储过程查看数据库

使用存储过程sp_helpdb存储过程查看数据库的基本信息。

例：

sp_helpdbeduc

3.7.3使用T-SQL语句修改数据库

语法格式：

ALTER DATABASE 

ADD FILE

    （[NAME = <'logical file name'>,]

    FILENAME = <'file name'>

    [, SIZE = ]

    [, MAXSIZE = < size in KB, MB, GB or TB >]

    [, FILEGROWTH = ]） 

    [,

n]

    [ TO FILEGROUP filegroup_name]

    [, OFFLINE ]

|ADD LOG FILE

    （[NAME = <'logical file name'>,]

    FILENAME = <'file name'>

    [, SIZE = < size in KB, MB, GB or TB >]

    [, MAXSIZE = < size in KB, MB, GB or TB >]

    [, FILEGROWTH = ]）

|REMOVE FILE  [WITH DELETE]

|ADD FILEGROUP 

|REMOVE FILEGROUP 

|MODIFY FILE 

|MODIFY NAME = 

|MODIFY FILEGROUP  {|NAME = }

|SET  [,

n ][WITH ]

|COLLATE 

例4-3：

将两个数据文件和一个事务日志文件添加到test数据库中。

ALTERDATABASETest

ADDFILE--添加两个次数据文件

（NAME=Test1,

FILENAME='f:

\sql\test1.ndf',SIZE=5MB,

MAXSIZE=100MB,

FILEGROWTH=5MB）,

（NAME=Test2,

FILENAME='f:

\sql\test2.ndf',SIZE=3MB,

MAX