SQL Server教程（第6版）(2008版) 课件 第2章 数据库创建

第2章

数据库创建——SQLServer数据库01数据库及其对象数据库架构数据库对象系统数据库数据库快照文件数据库及其对象1．数据库架构简单地说，架构的作用是将数据库中的所有对象分成不同的集合，每一个集合称为一个架构。数据库中的每一个用户都会有自己的默认架构。架构可以在创建数据库用户时由创建者设定，若不设定，则系统默认架构为dbo。数据库用户只能对属于自己架构中的数据库对象执行相应的操作，操作的权限则由数据库角色决定。数据库及其对象2．数据库对象数据库是一个容器，里面包含数据库对象。表：是存放数据及表示关系的主要形式，是最主要的数据库对象。视图：视图是一个或多个基本表生成的引用表（称为虚表）。索引：表中的记录通常按其输入的时间顺序存放，这种顺序称为记录的物理顺序。为了实现对表记录的快速查询，可以对表的记录按某个或某些字段或它们的组合（称为索引表达式）进行排序，这种顺序称为逻辑顺序。约束：约束用于保障数据的一致性与完整性。具有代表性的约束就是主键和外键。主键约束当前表记录的主键字段值唯一性，外键约束当前表记录与其他表的关系。存储过程：存储过程是一组完成特定功能的SQL语句集合，它存储在数据库中。存储过程具有名称，能够接受（输入）参数、输出参数、返回单个或多个值。存储过程独立于表存在。数据库及其对象触发器：触发器基于一个表的操作（插入、修改或删除）创建，编写若干条T-SQL语句，当该操作发生时，这些语句被执行，返回真或者假。返回假，当前表的操作不能被执行。默认值：默认值是在用户插入表新记录前，系统设置的字段的初始值。用户和角色：用户是指对数据库有存取权限的使用者；角色是一个用户组，给角色分配操作权限，该角色对应的组的用户都具有该操作权限。规则：是用来限制表字段的数据范围。类型：用户可以根据需要在指定的系统类型之上定义自己的数据类型。函数：用户可以根据需要将若干个T-SQL语句或者系统函数进行组合实现特定功能，定义成自己的函数。然后，在需要该功能处调用该函数。数据库及其对象3．系统数据库在SQLServer实例安装后，就生成下列系统数据库。（1）master数据库：它记录SQLServer系统的所有系统级信息。包括SQLServer实例初始化信息、登录账户、端点、连接服务器和系统配置等，还记录其他数据库文件的位置。（2）model数据库：保存SQLServer实例上创建的所有数据库的模板。如果修改model数据库，之后创建的所有数据库都将继承这些修改。如果用特定于用户的模板信息修改model数据库，建议您备份model。（3）tempdb数据库：tempdb是所有用户使用的临时数据库。每次启动SQLServer时都会重新创建它，在断开连接时会自动删除其中的临时表和存储过程。不允许对tempdb进行备份和还原操作。

（4）msdb数据库：SQLServer代理使用msdb数据库来计划警报和作业，SQLServerManagementStudio、ServiceBroker和数据库邮件等其他组件也使用该数据库。在进行任何更新msdb的操作（例如备份或还原任何数据库）后，建议备份msdb。数据库及其对象4．数据库快照文件快照是指数据库在某一指定时刻的状态，它提供了源数据库在创建快照时的只读、静态视图。虽然数据库在不断变化，但快照一旦创建就不能改变。多个快照可以位于一个源数据库中，并且可以作为数据库始终驻留在同一服务器实例上。创建快照时，每个快照在事务上都与源数据库一致。在被数据库所有者显式删除之前，快照始终存在。02数据库及其文件数据库名称数据库文件类型文件大小、自动增长和最大容量文件组数据库及其文件1.数据库名称数据库名称是所有SQLServer的集成操作环境（SSMS）和T-SQL语句中引用数据库的名称，必须符合SQLServer标识符命名规则。数据库名称是SQLServer实例层面的名称，是逻辑名，所以在同一个SQLServer实例中，数据库名称必须唯一，例如：xscj。数据库及其文件2.数据库文件类型1）文件类型SQLServer数据库包含下列3类文件：（1）主数据文件主数据文件一般称数据文件，它包含数据库的启动信息，存储部分或全部的数据和数据库对象，并指向数据库中的其他文件。每个数据库仅有一个主数据文件（扩展名mdf），还可以包含若干个辅助数据文件（扩展名ndf），用于存储主数据文件未能存储的剩余数据和一些数据库对象。（2）日志文件对数据库插入、删除、更新等操作的事务都会记录在日志文件（扩展名ldf）中，对数据库查询等读操作则不会记录在日志文件中。每个数据库至少有一个日志文件。（3）FILESTREAM文件FILESTREAM不是一种新的数据类型，而是一种技术，它使SQLServer数据库引擎和NTFS文件系统成为了一个整体，基于SQLServer的应用程序可以将非结构化数据（如文档和图像）存储在文件系统中，通过丰富的流式API对其进行操作。又可对文件数据的操作使用T-SQL语句进行插入、更新、查询、搜索和备份，并保持非结构化数据和对应的结构化数据之间的事务一致性。2）文件存放路径数据库文件默认存放的路径为SQLServer安装目录下的DATA子目录，例如：C:\ProgramFiles\MicrosoftSQLServer\SQLServer内部编号.SQLServer实例名

\MSSQL\DATA数据库及其文件3.文件大小、自动增长和最大容量创建SQLServer数据库需要指定每一个文件初始大小、自动增长方式和最大容量。系统有一个默认初始大小，用户可以根据需要设置。初始大小应以初始存放在数据库中各种数据和对象的累计大小放大一个系数作为参考。增长方式包括“按百分比”（例如10%）增长或者“按MB”（例如5MB）增长。前者每次在原来空间大小的基础上增长10%；后者每次增长5MB，而不管原来的空间大小是多少。当数据库数据超过初始大小按指定的增长方式增长。如果文件组中包含多个文件，则它们在所有文件被填满之前不会自动增长。数据库及其文件4.文件组SQLServer包含下列文件组：（1）PRIMARY文件组。主数据文件（mdf）只能属于系统PRIMARY文件组，所有系统表都属于该文件组。此外，还可以包含一个或者多个辅助数据文件（ndf）。（2）用户定义文件组。在用户定义了文件组后，其他数据文件可以指定属于用户定义的文件组，否则属于默认文件组。（3）默认文件组。新建数据库时，会自动创建一个默认的文件组PRIMARY，可以指定用户定义文件组为默认文件组。默认文件组必须足够大，能够容纳指定为该文件组合未分配文件组的所有新对象。（4）FILESTREAM文件组。是管理FILESTREAM类型文件。需要先添加FILESTREAM文件组，然后才能在数据库中添加FILESTREAM文件类型。（5）内存优化数据文件组。内存优化表把数据存放到内存中的表，内存优化表在创建的同时被编译成本地机器代码装载到内存中，所以对它的访问和操作非常快。第2章

数据库创建——数据库创建和修改01创建数据库文件项文件组项创建数据库实例创建数据库创建数据库前要确保用户具有创建数据库的权限。语法格式如下。CREATEDATABASE数据库名[CONTAINMENT={NONE|PARTIAL}][ON

[PRIMARY][<文件项>,...][,<文件组项>,...]

[LOGON{<文件项>,...}]]...说明：（1）“,...”表示重复前面项。（2）“...”表示还有其他项，为了突出主要内容而省略。（3）在一个语句由多行组成时，为了清晰描述层次之间的关系，第二行开始可以根据所属关系进行阶梯缩进。创建数据库1.文件项<文件项>::={(

NAME=逻辑名称,

FILENAME={'含路径文件名'|'filestream存储路径'}

[,SIZE=文件初始容量]

[,MAXSIZE={文件最大容量|UNLIMITED}]

[,FILEGROWTH=文件增量[容量|%]])}其中：（1）含PRIMARY，对应主数据文件，否则为辅助数据文件。（2）NAME=逻辑名称：SQLServer实例使用的数据库名。（3）FILENAME=含路径文件名：创建数据库后操作系统中包含的数据库文件的路径和文件名。（4）文件初始容量：指定数据文件的初始大小，单位可以是KB、MB、GB、TB。（5）文件最大容量：指定数据文件的最大容量，如果指定为UNLIMITED，表示文件大小不受限制，但实际上受磁盘可用空间的限制。（6）文件增量：有容量值和百分比（%）两种格式。创建数据库2．文件组项<文件组项>::={ FILEGROUP文件组名[DEFAULT]

<文件项>,...}其中：（1）<文件组项>：指定属于该文件组的文件。（2）DEFAULT：指定命名文件组为数据库中的默认文件组。（3）还可以指定为“FILESTREAM”文件组。创建数据库3.创建数据库实例【例2.1】创建一个名为test1的数据库，其初始大小为5MB，最大为50MB，允许数据库自动增长，增长方式是按10%比例。日志文件初始为2MB，最大可增长到5MB，按1

MB增长。语句如下：CREATEDATABASEtest1 ON ( NAME="test1", FILENAME="E:\MyDB\test1.mdf", SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=10% ) LOGON ( NAME="test1_log", FILENAME="E:\MyDB\test1.ldf", SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB )创建数据库【例2.2】创建一个名为test2的数据库，它有两个数据文件，其中主数据文件为20MB，最大不限，按10%增长。1个辅助数据文件为20MB，最大不限，按10%增长；有1个日志文件，为50MB，最大为100MB，按10MB增长。语句如下：CREATEDATABASEtest2 ON PRIMARY ( NAME='test2_data1', FILENAME='E:\MyDB\test2_data1.mdf', SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=10% ), ( NAME='test2_data2', FILENAME='E:\MyDB\test2_data2.ndf', SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=10% ) LOGON ( NAME='test2_log1', FILENAME='E:\MyDB\test2_log1.ldf', SIZE=50MB, MAXSIZE=100MB, FILEGROWTH=10MB )创建数据库【例2.3】创建一个具有2个文件组的数据库test3。主文件组包括文件test3_dat1，文件初始大小为20MB，最大为60MB，按5MB增长。文件组test3Group1包括文件test3_dat2，文件初始大小为10MB，最大为30MB，按10%增长。语句如下：CREATEDATABASEtest3ONPRIMARY( NAME='test3_dat1',FILENAME='E:\MyDB\test3_dat1.mdf',SIZE=20MB,MAXSIZE=60MB,FILEGROWTH=5MB),FILEGROUPtest3Group1( NAME='test3_dat2',FILENAME='E:\MyDB\test3_dat2.ndf',SIZE=10MB,MAXSIZE=30MB,FILEGROWTH=10%)02修改数据库数据库文件选项数据库文件组选项数据库选项修改数据库实例修改数据库已经创建的数据库可以修改它的属性。语法格式如下：ALTERDATABASE数据库名{ ADDFILE<文件选项>...[TOFILEGROUP文件组名] /*在文件组中增加数据文件*/ |ADDLOGFILE<文件选项>... /*增加日志文件*/ |REMOVEFILE逻辑文件名 /*删除数据文件*/ |ADDFILEGROUP文件组名[……] /*增加文件组*/ |REMOVEFILEGROUP文件组名 /*删除文件组*/ |MODIFYFILE<文件选项> /*更改文件属性*/ |MODIFYNAME=新数据库名 /*数据库更名*/ |MODIFYFILEGROUP文件组名 {

<文件组可更新选项> |DEFAULT |NAME=新文件组名 } /*更改文件组属性*/|SET<属性选项>...[WITH<终止>] /*设置数据库属性*/... /*指定数据库排序规则*/}修改数据库1.数据库文件选项（1）ADDFILE子句：向数据库添加数据文件，<文件选项>给出文件的属性，其构成参见CREATEDATABASE语法说明。关键字TOFILEGROUP指出了添加的数据文件所在的文件组名，若默认，则为主文件组。（2）ADDLOGFILE子句：向数据库添加日志文件，<文件选项>给出日志文件的属性。（3）REMOVEFILE子句：从数据库中删除数据文件，被删除的数据文件由其中的参数“逻辑文件名”给出。当删除一个数据文件时，逻辑文件与物理文件全部被删除。（4）MODIFYFILE子句：修改数据文件的属性，被修改文件的逻辑名由<文件选项>的NAME选项给出，可以修改的文件属性包括NEWNAME、FILENAME、SIZE、MAXSIZE和FILEGROWTH。其中NEWNAME为新的文件逻辑名。修改数据库2.数据库文件组选项（1）ADDFILEGROUP子句：向数据库中添加文件组，被添加的文件组名由参数“文件组名”给出。（2）REMOVEFILEGROUP子句：删除文件组，被删除的文件组名由参数“文件组名”给出。（3）MODIFYFILEGROUP子句：用于修改文件组的属性。“文件组名”为要修改的文件组名称。<文件组可更新选项>::={READ_ONLY|READ_WRITE}其中，READ_ONLY将文件组设为只读，READ_WRITE将文件组设为读/写模式。DEFAULT选项：表示将默认数据库文件组改为指定文件组。NAME选项：用于将文件组名称改为新文件组名。3.数据库选项（1）MODIFYNAME子句：更改数据库名，新的数据库名由参数“新数据库名”给出。（2）SET子句：用于设置数据库的属性，<属性选项>中指定了要修改的属性。例如，设为READ_ONLY时，用户可以从数据库读取数据，但不能修改数据库。其他属性请参考有关文档。修改数据库4.修改数据库实例1）修改文件大小和增长方式【例2.4】修改数据库test1的主数据文件逻辑名为test1_data，最大容量改为100MB，增长方式改为按每次5MB增长，文件名作相应调整。语句如下：USEmasterALTERDATABASEtest1 MODIFYFILE ( NAME='test1', NEWNAME='test1_data', FILENAME='E:\MyDB\test1_data.mdf', MAXSIZE=100MB, FILEGROWTH=5MB )修改数据库2）增加和删除数据文件【例2.5】先为数据库test1增加数据文件test1bak，然后删除该数据文件。（1）为数据库test1增加数据文件test1bak。ALTERDATABASEtest1 ADDFILE ( NAME='test1bak', FILENAME='E:\MyDB\test1bak.ndf', SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5% )（2）查看数据库test1参数修改结果。刷新“对象资源管理器”中的数据库，查看“数据库属性”窗口中的“文件”选项卡和数据库文件目录，可以看到数据库test1增加了数据文件test1bak。（3）删除数据文件test1bak。ALTERDATABASEtest1 REMOVEFILEtest1bak修改数据库3）为数据库添加文件组【例2.6】为数据库test1添加文件组fgroup，并为此文件组添加两个大小均为10MB的数据文件，观察结果后将文件组fgroup删除。（1）为数据库test1添加文件组和数据文件。ALTERDATABASEtest1ADDFILEGROUPfgroupGOALTERDATABASEtest1ADDFILE( NAME='test1_data2',FILENAME='E:\MyDB\test1_data2.ndf',SIZE=10MB),( NAME='test1_data3',FILENAME='E:\MyDB\test1_data3.ndf',SIZE=10MB)TOFILEGROUPfgroupGO修改数据库说明：（1）GO必须单独一行，是批处理命令。遇到第一个GO，在此前test1数据库增加了一个文件组fgroup；遇到第二个GO，在test1数据库增加了两个文件。（2）查看数据库test1“文件组”选项卡，可见增加了fgroup文件组；同时，数据库文件目录中增加了2个数据文件。（3）从数据库中删除fgroup文件组。SQL语句如下。ALTERDATABASEtest1 REMOVEFILEtest1_data2ALTERDATABASEtest1 REMOVEFILEtest1_data3GOALTERDATABASEtest1 REMOVEFILEGROUPfgroupGO03删除数据库删除数据库删除数据库使用下列命令。语法格式：DROPDATABASE数据库名,...其中，“数据库名”是要删除的数据库名。例如，要删除数据库test1，使用命令：DROPDATABASEtest1第2章

数据库创建——创建数据库综合01命令方式创建数据库命令方式创建数据库【例2.7】按照下列要求创建学生成绩数据库，数据库名xscj。（1）创建学生成绩数据库xscj，初始文件大小为100MB，按照10%增长，占用最大空间没有限制。日志文件大小为20MB，按照5MB增长，占用最大空间不超过100MB。数据文件和日志文件主文件名为xscj，均存放在“E:\MyDB”目录下。CREATEDATABASExscj ON PRIMARY ( NAME='xscj', FILENAME='E:\MyDB\xscj.ndf', SIZE=100MB, MAXSIZE=UNLIMITED, FILEGROWTH=10% ) LOGON ( NAME='xscj_log', FILENAME='E:\MyDB\xscj.ldf', SIZE=20MB, MAXSIZE=100MB, FILEGROWTH=5MB )GO命令方式创建数据库（2）在数据库xscj中增加文件组fgroup1、fgroup2、fgroup3和fgroup4。ALTERDATABASExscjADDFILEGROUPfgroup1ALTERDATABASExscjADDFILEGROUPfgroup2ALTERDATABASExscjADDFILEGROUPfgroup3ALTERDATABASExscjADDFILEGROUPfgroup4GO（3）给文件组fgroup1、fgroup2、fgroup3和fgroup4增加对应数据文件。（4）在对象资源管理器中查看xscj数据库属性。包括常规、文件组和文件。02界面方式创建数据库创建数据库修改数据库属性增加或删除数据文件删除数据文件增加或删除文件组数据库重命名数据库的删除界面方式创建数据库1.创建数据库【例2.8】创建数据库xscj，数据文件和日志文件的属性按默认值设置。系统对数据文件的默认值为：初始大小3MB；最大容量不限（仅受硬盘空间的限制）；允许数据库自动增长，增量为1MB；日志文件初始大小1MB，最大容量不限（仅受硬盘空间的限制），允许日志文件自动增长，增长方式为按10%比例增长。（1）在SSMS的“对象资源管理器”中，选择“数据库”，右击鼠标，在弹出的快捷菜单中选择“新建数据库”菜单项，打开“新建数据库”窗口。

（2）“新建数据库”窗口的左上方共有3个选项页：“常规”、“选项”和“文件组”，这里只配置“常规”选项页，其他选项页使用系统默认设置。界面方式创建数据库在“新建数据库”窗口的左上方选择“常规”选项页，在“数据库名称”文本框中填写要创建的数据库名称“xscj”，也可以在“所有者”文本框中指定数据库的所有者，如sa。单击“自动增长”和“路径”后面的按钮，可以进入对应对话框指定数据库文件需要的增长方式和存放路径，数据库文件名可以直接输入。这里修改数据库存放路径为“E:\MyDB”，其他属性按默认值设置，如图。界面方式创建数据库单击“确定”，数据库创建完成，在“对象资源管理器”窗口的“数据库”下显示xscj，如图所示。界面方式创建数据库2．修改数据库属性在“数据库”下选择数据库xscj，右击鼠标，在出现的快捷菜单中选择“属性”菜单项。显示“数据库属性-xscj”窗口。从图中的“选项页”列表中可以看出，它包括9个选项页。在这些选项页中，可以查看数据库系统的各种属性和状态，也可以修改原来的属性。可以进行的修改包括以下几项。增加或删除数据文件。改变数据文件的大小和增长方式。改变日志文件的大小和增长方式。增加或删除日志文件。增加或删除文件组。重命名数据库。界面方式创建数据库3．增加或删除数据文件【