CH02-使用SSMS创建和管理数据库

第二章

使用SSMS创建和管理数据库预习检查什么是数据库登录名、数据库用户？创建数据库需要指定哪两种文件？使用T-SQL语句创建数据库的语法是什么？写出删除数据库的SQL语句为什么要备份数据库？本章目标掌握SSMS的基本操作。能够使用SSMS图形工具创建、配置、管理和维护SQLServer数据库。能够使用T-SQL语句创建、管理SQLServer数据库。学会使用SQLServer帮助文档新建数据库登录名SQLServer2008数据库SQLServer2008数据库分为系统数据库和用户数据库。系统数据库有数据库管理系统管理，负责管理用户数据库和其他数据库对象。用户数据库用来存储用户数据，主要由数据库管理员管理。SchoolDBPubsNorthWind……mastermodeltempdbmsdbResource系统数据库用户数据库系统数据库和用户数据库系统数据库1.master数据库master数据库记录SQLServer中所有系统级别的信息，包括所有登录账户、系统配置和设置、服务器中其他数据库的相关信息和这些数据库文件的位置，以及SQLServer初始化信息等。2.model数据库model数据库用于创建所有数据库的模板，是tempdb数据库的基础。例如，使用SQL语句创建新数据库时，将使用模板中规定的默认值来创建。3.tempdb数据库tempdb数据库是一个临时性的数据库，保存所有的临时表和临时存储过程，以及临时工作表。关闭SQLServer服务时，tempdb数据库保存的内容将自动消失，重新启动SQLServer服务时，系统将重新创建空的tempdb数据库。

4.msdb数据库msdb数据库提供SQLServer代理服务的调度警报和作业，也会记录数据库备份和还原信息。

5.resource数据库resource数据库是只读数据库，包含SQLServer中所有系统对象，这些系统对象在物理上保存在resource数据库中，但在逻辑上，他们显示在每个数据库的sys架构中。resource数据库是一个“隐藏”了的数据库，我们无法使用SQL命令看到它，但可以查看它的一些信息。

登录名和数据库用户登录名用以登录SQLServer数据库服务器数据库用户用以登录成功后，访问某个具体的用户数据库示例2-1建立登录名第一步：在“对象资源管理器”中单击“安全性”文件夹图标前的“+”图标，右键单击“登录名”图标，选择“新建登录名”选项。第二步：在新建登录名对话框中，填写登录名，选择“SQLServer身份验证”，输入密码和确认密码。第三步：赋予该登录名操作权限。如果已经为某数据库创建了用户，也快成通过用户映射赋予该登录名对该数据库的操作权限。固定服务器角色固定服务器角色权限描述sysadmin能够执行SQLServer上的任何操作serveradmin设置服务器范围的配置选项和关闭服务器setupadmin管理链接服务器和启动过程securityadmin管理登录名、读取错误日志和创建数据库processadmin管理SQLServer中运行的进程public仅可以连接和查看数据库，每个登录名均属于

public服务器角色dbcreator可以创建、更改、还原和删除数据库diskadmin可以管理磁盘文件bulkadmin可以执行

BULKINSERT语句，以用户指定的格式复制一个数据文件至数据库表或视图中上机练习2-1创建登录名

训练要点：1、SSMS的使用。2、创建登录名、熟悉固定系统角色。3、使用创建的登录名登录数据库需求说明：使用SSMS图形工具创建数据库登录名SQLdb，并赋予其dbcreator角色

。

数据库文件相关概念一个数据库至少有一个数据文件和一个事务日志文件。数据文件

数据文件用于存放数据库的数据和各种对象。一个数据库可以有一个或多个数据文件。当有多个数据文件时，数据文件又可以分成主数据文件和次数据文件两种。每一个数据库都有且仅有一个主数据文件。主数据文件名称的默认后缀是.mdf。次数据文件用来存放不在主数据文件中的其他数据和对象，可以没有、也可以有一个或多个，默认后缀是.ndf。数据文件事务日志文件事务日志文件用于存放事务日志，记录对数据库的所有修改操作。每一个数据库都至少有一个日志文件，也可以有多个。日志文件的默认后缀是.ldf。文件组在SQLServer系统内部，每个数据库物理文件都对应一个逻辑文件名，文件组就是文件的逻辑集合，它把一些指定的文件组合在一起，以方便管理和分配数据。事务日志文件和文件组示例2-2使用SSMS创建数据库示例：创建的登录名SQLdb创建“简易银行系统”数据库MyBank第一步：从“开始”菜单中找到“MicrosoftSQLServer2008”菜单项，打开SQLServerManagementStudio窗口，以SQLdb登录名连接到SQL2008数据库实例。

第二步：右键单击“对象资源管理器”窗口中的“数据库”图标，选择“新建数据库”。第三步：在“常规”页中指定数据库属性。1、“数据库名称”文本框填写MyBank，在“所有者”文本框填写数据库所有者名称，或者单击右面的“...”按钮选择，默认值指的是当前登录名SQLdb。2、设置文件属性逻辑名称：指定该文件的文件名。文件类型：可选择行数据或日志，用于指定当前文件是数据文件还是日志文件。文件组：显示当前数据库文件所属的文件组。初始大小：指定该文件的初始容量。自动增长：设置当文件的容量不够用时，文件根据何种增长方式自动增长。路径：指定存放该文件的目录。在默认情况下，SQLServer2008将存放路径设置为SQLServer2008安装目录下的data子目录。第四步：单击选择页中“选项”按钮，

设置数据库选项需要关注的几个选项：兼容级别：表示数据库向以前的版本兼容的级别，例如设置为“SQLServer2005(90)”，那么SQLServer2005也能识别和操作该数据库。数据库为只读：默认为false，若选择true，则无法写入数据。限制访问：指定可访问的用户。有三种选项：Multiple_User，数据库的正常状态，允许多个用户同时访问；Single_User，只允许一个用户访问，用于数据库维护状态；Restricted_User，只允许管理员或特定成员才能访问。需求说明：物理文件路径：D:\StudentInfo；数据文件：初始大小10MB，允许自动增长，数据文件大小不受限制；日志文件：初始大小1MB，最大30MB；其它选项：自动收缩、不创建统计信息、自动关闭。训练要点：1、使用SSMS创建数据库。

2、设置数据库各个参数。上机练习2-2新建StudentInfo学生信息数据库示例：为数据库MyBank新建数据库用户第一步：在“对象资源管理器”窗口中打开MyBank数据库→右键单击“安全性”→新建→用户新建数据库用户第二步：在“新建用户”窗口中输入用户名，选择关联的登录名。例如用户名为cashier，登录名为dbuser（假设此登录名除了public以外没有任何服务器和数据库角色，也就是他只能连接数据库）。第三步：为用户选择数据库架构和角色。固定数据库角色固定数据库角色描述db_owner在数据库中有全部权限db_accessadmin可以为

Windows登录名、Windows组和

SQLServer登录名添加或删除数据库访问权限db_securityadmin能够管理角色和数据库角色成员，并能管理语句和对象的许可权限db_ddladmin可以在数据库中运行任何数据定义语言

(DDL)命令，能够在数据库中添加、修改或删除对象db_backupoperator能够备份数据库db_datareader能够在数据库中所有用户表上执行SELECT语句db_datawriter能够在数据库所有用户表上执行INSERT、UPDATE和DELETE语句db_denydatareader对数据库中的数据库对象(表或视图)没有SELECT权限，不能读取数据库内用户表中的任何数据db_denydatawriter不能新增、修改或删除数据库内任何用户表中的任何数据

定义：单个用户或角色所拥有的一组数据库对象的集合并构成唯一命名空间。

可以将架构看成是对象的容器。数据库角色拥有对应的数据库架构，数据库用户可以通过角色直接拥有架构。数据库用户有默认架构，如果没有定义默认架构，则数据库用户将用dbo作为他的默认架构。数据库架构架构和创建它的用户是分离的，多个用户可以通过角色或组成员关系拥有同一个架构。在后面章节中编写程序操作数据库对象时，可以通过完全限定名来访问，其格式为：服务器.数据库.架构.对象。到此我们创建了MyBank数据库用户并关联到登录名，现在您可以使用关联的登录名登录数据库看看该登录名是否对该数据库有操作权限？需求说明：为StudentInfo数据库创建登录名StuInfoLogin和用户名StuInfoUser，StuInfoLogin没有权限，StuInfoUser有db_owner角色，将登录名和用户名相关联。训练要点：1、建立数据库连接、创建登录名。2、创建数据库用户名、设置角色权限。上机练习2-3为StudentInfo数据库创建登录名和用户名修改数据库在“对象资源管理器”窗口中右键单击MyBank数据库，选择属性，在打开的数据库属性窗口中可以通过“选择页”中的选项，对数据库的属性设置进行修改。删除数据库在“对象资源管理器”窗口中选择要删除的数据库“MyBank”，单击右键，在弹出的窗口中选择“删除”菜单项，打开“删除对象”窗口，单击右下角的“确定”按钮，即可以删除数据库“MyBank”。上机练习2-4修改数据库属性

需求说明：重新设置MyBank数据库的数据文件增长方式为自动增长、增量为1MB、最大值为100MB。训练要点：使用SSMS修改数据库属性。分离数据库

如果要分离的数据库有打开的文件，应首先关闭。附加数据库上机练习2-5分离和附加数据库需求说明：将MyBank数据库文件复制到E盘根目录，然后附加。训练要点：数据库的分离和附加。实现思路：首先分离数据库，将数据库剪切或复制，然后粘贴到E盘根目录E:\后，再附加数据库。备份和还原数据库SQLServer有四种备份方式：完全备份备份数据库的所有内容，包括所有数据库对象和事务日志。事务日志备份只备份自上次备份事务日志后对数据库执行的所有事务记录。差异备份只记录自上次数据库备份后发生更改的数据部分。文件和文件组备份当数据库非常庞大时，可执行数据库文件或文件组备份。这种备份策略使用户只还原已损坏的文件或文件组。完全备份数据库在“备份到”选项中选择“磁盘”，如果想改变系统默认备份地址，可单击“添加”按钮，更改备份路径，然后单击“确定”即可完成备份。还原数据库上机练习2-6备份和还原数据库需求说明：假设数据库StudentInfo已经完全备份，现在数据库受到损坏，需要通过备份文件将其还原到正常状态。训练要点：1、SSMS的使用。 2、备份、分离、还原数据库。T-SQL简介SQL是结构化查询语言(StructuredQueryLanguage)的英文缩写，是一种使用关系模型的数据库应用语言。分为如下几类：1、数据定义语言(DataDefinitionLanguage，DDL)2、数据操纵语言(DataManipulationLanguage，DML)3、数据控制语言(DataControlLanguage，DCL)数据定义语言DDL命令作用CREATE创建数据库或数据库对象

ALTER修改数据库或数据库对象DROP删除数据库或数据库对象

用来建立数据库、数据库对象和定义表的列等。命令作用SELECT从表或视图中检索数据

INSERT将数据插入到表或视图中

UPDATE修改表或视图中的数据

DELETE从表或视图中删除数据

数据操纵语言DML用于更改数据库数据，包括插入、删除和修改等数据操作。数据控制语言DCL命令作用GRANT对象的创建者赋予某用户或某组或所有用户某些特定的权限REVOKE收回权限，与GRANT作用相反，但不影响从其他角色继承权限DENY收回权限，并禁止从其他角色继承许可权限

COMMIT提交当前事务，使事务中执行的变更永久化ROLLBACK恢复数据到修改(update，insert，delete)未commit之前的状态用来控制存取许可、管理权限等。创建数据库每个数据库至少有两个文件（一个主文件和一个日志文件）和一个文件组为便于分配和管理，可以将数据库对象和文件一起分成文件组日志文件不包括在文件组内。日志空间与数据空间分开管理CREATEDATABASE数据库名

ON[PRIMARY](<数据文件参数>[，…n][<文件组参数>])[LOGON](<日志文件参数>[，…n])主文件组，可选参数，默认创建数据库的语法创建数据库示例1使用CREATEDATABASE语句创建数据库MySchool，要求：该数据库具有一个数据文件和一个日志文件文件保存在D盘已有的文件夹Project下数据文件的大小初始为5MB，最大值为100MB，文件增长率是15%日志文件的大小初始为2MB，增长量是1MBCREATEDATABASE

MySchool

ONPRIMARY

--默认就属于PRIMARY主文件组，可省略(

NAME='MySchool_data',--主数据文件的逻辑名

FILENAME='D:\project\MySchool_data.mdf',--主数据文件的物理名

SIZE=5MB,

--主数据文件初始大小

MAXSIZE=100MB,

--主数据文件增长的最大值

FILEGROWTH=15%

--主数据文件的增长率)LOGON(

NAME='MySchool_log',

FILENAME='D:\project\MySchool_log.ldf',

SIZE=2MB,

FILEGROWTH=1MB)GO

数据文件的具体描述日志文件的具体描述创建数据库示例2使用CREATEDATABASE语句创建数据库Employee，要求：文件保存在D:\Project文件下第一个主数据文件以employee1命名，初始大小是10MB，增长量是10%第二个次要数据文件以employee2命名，初始大小是20MB，最大值是100MB，增长量是1MB第一个日志文件以employeelog1命名，初始为10MB，增长量是1MB第二个日志文件以employeelog2命名，初始大小和增长量与第一个日志文件相同，但最大值是50MBCREATEDATABASE

Employee

ON

(

--主数据文件的具体描述

NAME='employee1',

FILENAME='D:\project\employee1.mdf',

SIZE=10,

FILEGROWTH=10%),(

--次要数据文件的具体描述

NAME='employee2',

FILENAME='D:\project\employee2.ndf',

SIZE=20,

MAXSIZE=100,

FILEGROWTH=1)

LOGON

(

--日志文件1的具体描述

NAME='employeelog1',

FILENAME='D:\project\employeelog1_Log.ldf',

SIZE=10,

FILEGROWTH=1),(

--日志文件2的具体描述-

NAME='employeelog2',

FILENAME='D:\project\employeelog2_Log.ldf',

SIZE=10,

MAXSIZE=50,

FILEGROWTH=1

)GO多个数据文件多个日志文件主数据文件次数据文件SQL语句中的“--”用于单行注释，格式为：--注释内容。SQL语句的多行注释为“/**/”。“/*”用于注释文字的开头，“*/”用于注释文字的结尾，格式为：/*注释内容*/。GO不是T-SQL语句，是一个不需任何权限的实用工具命令。它可以由任何用户执行。它向SQLServer实例发送当前批T-SQL语句。当前批语句由上一GO命令后输入的所有语句组成，如果是第一条GO命令，则由脚本开始后输入的所有语句组成。GO命令不能和T-SQL语句在同一行中。SQL注释和GO语句批处理命令GO批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行SQLServer将每批SQL语句编译成一个执行单元，此单元称为执行计划客户端应用程序SQLServer服务器批处理：语句1语句2……GO批处理命令示例为了督促学生学习，学校对多次考试不及格的学生给予处理。凡是一次考试不及格者，给予警告；三次以下（含）不及格者，肄业处理；三次以上不及格者，开除学籍CREATETABLEPunish(--创建表

StuNointNOTNULL,--学号StuCntintNOTNULL,--不及格次数StuMngvarchar(50)--处理意见)GOINSERTINTOPunish--插入数据SELECTStudentNo学号,COUNT(0)不及格次数,''处理意见FROMResultWHEREStudentResult<60GROUPBYStudentNoGOUPDATEPunishSETStuMng

='警告'WHEREStuCnt

=1--更新UPDATEPunishSETStuMng

='肄业'WHEREStuCnt

BETWEEN2AND3--更新UPDATEPunishSETStuMng

='开除'WHEREStuCnt

>3--更新GOSELECT*FROMPunish--查询GO是批处理的标志，表示SQLServer将这些SQL语句编译为一个执行单元，提高执行效率一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定第一步：创建新表Punish，表结构是：处罚记录（学号、不及格次数、处理意见）创建新表时处理意见为空第二步：查询所有不及格的成绩插入Punish表中第三步：根据每个学生不及格次数批量更新处理意见常见错误CREATETABLEStudent(……)GOGO是SQLServer的批处理命令，只有代码编辑器才能识别并处理，编辑其他应用程序就不能使用该命令由于每个批处理之间是独立的，因此，在一个批处理出现错误时，并不会影响其他批处理中SQL代码的运行GO命令和SQL语句不能在同一行上机练习2-7、2-8上机练习2-7使用T-SQL创建数据库StudentInfo上机练习2-8使用T-SQL创建多个数据文件的数据StudentInfo需求说明：创建包含3个数据文件和2个日志文件的数据库，并将两个次数据文件存储在名称为groupfile1的文件组中。练习2-8分析CREATEDATABASEStudentInfoONPRIMARY( --主文件部分省略),FILEGROUPgroupfile1(NAME=StudentInfo_Data1,FILENAME='E:\DB\StudentInfo_Data1.ndf',SIZE=2MB,MAXSIZE=10MB,FILEGROWTH=5%),(NAME=StudentInfo_Data2,FILENAME='E:\DB\StudentInfo_Data2.ndf',--其他同上)/*接下页*/LOGON(NAME=StudentInfo_Log,FILENAME='E:\DB\StudentInfo_Log.ldf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=10%),(NAME=StudentInfo_Log1,FILENAME='E:\DB\StudentInfo_Log1.ldf',--其他同上)使用T-SQL语句修改数据库修改数据库的语法格式：ALTERDATABASE数据库名称{ADDFILE<数据文件参数>[,...n][TOFILEGROUP文件组名称]|REMOVEFILE数据文件名称

|MODIFYFILE<数据文件参数>|ADDLOGFILE<日志文件参数>[,...n]|ADDFILEGROUP文件组名称

|REMOVEFILEGROUP文件组名称

|MODIFYFILEGROUP文件组名称{文件组属性|NAME=修改后文件组名称}|MODIFYNAME=修改后数据库名称

|SET<属性参数>[,...n]}修改数据库名称ALTERDATABASEBookStoreMODIFYNAME=NewBookStore--修改数据库名称示例2-3：修改数据库BookStore名称为NewBookStore向数据库中添加数据文件ALTERDATABASEBookStoreADDFILE