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

1、本章目标本章目标新建数据库登录名新建数据库登录名 SQL Server 2008数据库SQL Server 2008数据库分为系统数据库和用户数据库。系统数据库有数据库管理系统管理，负责管理用户数据库和其他数据库对象。用户数据库用来存储用户数据，主要由数据库管理员管理。SchoolDBPubsNorthWindmastermodeltempdbmsdbResource系统数据库系统数据库用户数据库用户数据库系统数据库和用户数据库系统数据库1.master数据库数据库 master数据库记录SQL Server中所有系统级别的信息，包括所有登录账户、系统配置和设置、服务器中其他数据库的相关信息和

2、这些数据库文件的位置，以及SQL Server初始化信息等。2. model数据库数据库 model数据库用于创建所有数据库的模板，是tempdb数据库的基础。例如，使用SQL语句创建新数据库时，将使用模板中规定的默认值来创建。3.tempdb数据库数据库 tempdb数据库是一个临时性的数据库，保存所有的临时表和临时存储过程，以及临时工作表。关闭SQL Server服务时，tempdb数据库保存的内容将自动消失，重新启动SQL Server服务时，系统将重新创建空的tempdb数据库。4.msdb数据库数据库 msdb数据库提供SQL Server代理服务的调度警报和作业，也会记录数据库备份

3、和还原信息。5.resource数据库数据库 resource数据库是只读数据库，包含SQL Server中所有系统对象，这些系统对象在物理上保存在resource 数据库中，但在逻辑上，他们显示在每个数据库的 sys 架构中。resource数据库是一个“隐藏”了的数据库，我们无法使用SQL命令看到它，但可以查看它的一些信息。登录名和数据库用户登录名登录名用以登录用以登录SQL ServerSQL Server数据库服务器数据库服务器数据库用户数据库用户用以登录成功后，访问某个具体的用户数据库用以登录成功后，访问某个具体的用户数据库示例示例2-1 建立登录名建立登录名第一步：在“对象资源管理

4、器”中单击“安全性”文件夹图标前的“+”图标，右键单击“登录名”图标，选择“新建登录名”选项。第二步：在新建登录名对话框中，填写登录名，选择“SQL Server身份验证”，输入密码和确认密码。第三步：赋予该登录名操作权限。如果已经为某数据库创建了用户，也快成通过用户映射赋予该登录名对该数据库的操作权限。固定服务器角色固定服务器角色 权限描述sysadmin能够执行SQL Server上的任何操作serveradmin设置服务器范围的配置选项和关闭服务器setupadmin管理链接服务器和启动过程securityadmin管理登录名、读取错误日志和创建数据库processadmin管理SQL

5、Server中运行的进程public仅可以连接和查看数据库，每个登录名均属于 public 服务器角色dbcreator可以创建、更改、还原和删除数据库diskadmin可以管理磁盘文件bulkadmin 可以执行 BULK INSERT 语句，以用户指定的格式复制一个数据文件至数据库表或视图中上机练习2-1 创建登录名训练要点训练要点：1、SSMS的使用 。2、创建登录名、熟悉固定系统角色。3、使用创建的登录名登录数据库需求说明需求说明： 使用SSMS图形工具创建数据库登录名SQLdb，并赋予其dbcreator角色 。 数据库文件相关概念一个数据库至少有一个数据文件和一个事务日志文件。数据

6、文件数据文件 数据文件用于存放数据库的数据和各种对象。一个数据库可以有一个或多个数据文件。当有多个数据文件时，数据文件又可以分成主数据文件和次数据文件两种。每一个数据库都有且仅有一个主数据文件。主数据文件名称的默认后缀是.mdf。次数据文件用来存放不在主数据文件中的其他数据和对象，可以没有、也可以有一个或多个，默认后缀是.ndf。数据文件事务日志文件事务日志文件 事务日志文件用于存放事务日志，记录对数据库的所有修改操作。每一个数据库都至少有一个日志文件，也可以有多个。日志文件的默认后缀是.ldf。文件组文件组 在SQL Server系统内部，每个数据库物理文件都对应一个逻辑文件名，文件组就是文

7、件的逻辑集合，它把一些指定的文件组合在一起，以方便管理和分配数据。事务日志文件和文件组示例示例2-2 使用使用SSMS创建数据库创建数据库示例：创建的登录名SQLdb创建“简易银行系统”数据库MyBank第一步：从“开始”菜单中找到“Microsoft SQL Server 2008”菜单项，打开SQL Server Management Studio窗口，以SQLdb登录名连接到SQL2008数据库实例。第二步：右键单击“对象资源管理器”窗口中的“数据库”图标，选择“新建数据库”。第三步：在“常规”页中指定数据库属性。1、“数据库名称”文本框填写MyBank，在“所有者”文本框填写数据库所有

8、者名称，或者单击右面的“.”按钮选择，默认值指的是当前登录名SQLdb。2、设置文件属性逻辑名称逻辑名称：指定该文件的文件名。文件类型文件类型：可选择行数据或日志，用于指定当前文件是数据文件还是日志文件。文件组文件组：显示当前数据库文件所属的文件组。初始大小初始大小：指定该文件的初始容量。自动增长自动增长：设置当文件的容量不够用时，文件根据何种增长方式自动增长。路径路径：指定存放该文件的目录。在默认情况下，SQL Server 2008将存放路径设置为SQL Server 2008安装目录下的data子目录。第四步：单击选择页中“选项”按钮，设置数据库选项 需要关注的几个选项： 兼容级别：表示

9、数据库向以前的版本兼容的级别，例如设置为“SQL Server 2005 (90)”，那么SQL Server 2005也能识别和操作该数据库。 数据库为只读：默认为false，若选择true，则无法写入数据。 限制访问：指定可访问的用户。有三种选项：Multiple_User，数据库的正常状态，允许多个用户同时访问；Single_User，只允许一个用户访问，用于数据库维护状态；Restricted_User，只允许管理员或特定成员才能访问。需求说明需求说明： 物理文件路径：D:StudentInfo；数据文件：初始大小10MB，允许自动增长，数据文件大小不受限制； 日志文件：初始大小1MB

10、，最大30MB；其它选项：自动收缩、不创建统计信息、自动关闭。训练要点训练要点：1、使用SSMS创建数据库。 2、设置数据库各个参数。上机练习2-2 新建StudentInfo学生信息数据库示例：示例：为数据库MyBank新建数据库用户第一步：在“对象资源管理器”窗口中打开MyBank数据库右键单击“安全性”新建用户新建数据库用户第二步：在“新建用户”窗口中输入用户名，选择关联的登录名。例如用户名为cashier，登录名为dbuser（假设此登录名除了public以外没有任何服务器和数据库角色，也就是他只能连接数据库）。第三步：为用户选择数据库架构和角色。固定数据库角色固定数据库角色描述db_

11、owner在数据库中有全部权限db_accessadmin可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限db_securityadmin能够管理角色和数据库角色成员，并能管理语句和对象的许可权限db_ddladmin可以在数据库中运行任何数据定义语言 (DDL) 命令，能够在数据库中添加、修改或删除对象db_backupoperator 能够备份数据库db_datareader能够在数据库中所有用户表上执行SELECT语句db_datawriter能够在数据库所有用户表上执行INSERT、UPDATE和DELETE语句db_denyd

12、atareader 对数据库中的数据库对象(表或视图)没有SELECT权限，不能读取数据库内用户表中的任何数据db_denydatawriter 不能新增、修改或删除数据库内任何用户表中的任何数据定义：单个用户或角色所拥有的一组数据库对象的集合并构成唯一命名空间。可以将架构看成是对象的容器。数据库角色拥有对应的数据库架构，数据库用户可以通过角色直接拥有架构。数据库用户有默认架构，如果没有定义默认架构，则数据库用户将用 dbo 作为他的默认架构。数据库架构架构和创建它的用户是分离的，多个用户可以通过角色或组成员关系拥有同一个架构。在后面章节中编写程序操作数据库对象时，可以通过完全限定名来访问，其

13、格式为：服务器.数据库.架构.对象。到此我们创建了MyBank数据库用户并关联到登录名，现在您可以使用关联的登录名登录数据库看看该登录名是否对该数据库有操作权限？需求说明：为StudentInfo数据库创建登录名StuInfoLogin和用户名StuInfoUser，StuInfoLogin没有权限，StuInfoUser有db_owner角色，将登录名和用户名相关联。训练要点：1、建立数据库连接、创建登录名。2、创建数据库用户名、设置角色权限。上机练习2-3为StudentInfo数据库创建登录名和用户名修改数据库在“对象资源管理器”窗口中右键单击MyBank数据库，选择属性，在打开的数据库

14、属性窗口中可以通过“选择页”中的选项，对数据库的属性设置进行修改。删除数据库在“对象资源管理器”窗口中选择要删除的数据库“MyBank”，单击右键，在弹出的窗口中选择“删除”菜单项，打开 “删除对象”窗口，单击右下角的“确定”按钮，即可以删除数据库“MyBank”。上机练习2-4 修改数据库属性需求说明：重新设置MyBank数据库的数据文件增长方式为自动增长、增量为1MB、最大值为100MB。训练要点：使用SSMS修改数据库属性。分离数据库如果要分离的数据库有打开的文件，应首先关闭。附加数据库上机练习2-5 分离和附加数据库需求说明：将MyBank数据库文件复制到E盘根目录，然后附加。训练要点

15、：数据库的分离和附加。实现思路：首先分离数据库，将数据库剪切或复制，然后粘贴到E盘根目录E:后，再附加数据库。备份和还原数据库SQL Server有四种备份方式： 完全备份完全备份 备份数据库的所有内容，包括所有数据库对象和事务日志。 事务日志备份事务日志备份 只备份自上次备份事务日志后对数据库执行的所有事务记录。 差异备份差异备份 只记录自上次数据库备份后发生更改的数据部分。 文件和文件组备份文件和文件组备份 当数据库非常庞大时，可执行数据库文件或文件组备份。这种备份策略使用户只还原已损坏的文件或文件组。完全备份数据库在“备份到”选项中选择“磁盘”，如果想改变系统默认备份地址，可单击“添加”

16、按钮，更改备份路径，然后单击“确定”即可完成备份。还原数据库上机练习2-6 备份和还原数据库需求说明：假设数据库StudentInfo已经完全备份，现在数据库受到损坏，需要通过备份文件将其还原到正常状态。 训练要点：1、SSMS的使用。 2、备份、分离、还原数据库。 T-SQL简介 SQL是结构化查询语言(Structured Query Language)的英文缩写，是一种使用关系模型的数据库应用语言。 分为如下几类：1、数据定义语言(Data Definition Language，DDL)2、数据操纵语言(Data Manipulation Language，DML)3、数据控制语言(D

17、ata Control Language，DCL)数据定义语言DDL命令作用CREATE 创建数据库或数据库对象 ALTER 修改数据库或数据库对象DROP 删除数据库或数据库对象 用来建立数据库、数据库对象和定义表的列等。命令作用SELECT 从表或视图中检索数据 INSERT 将数据插入到表或视图中 UPDATE 修改表或视图中的数据 DELETE 从表或视图中删除数据 数据操纵语言DML用于更改数据库数据，包括插入、删除和修改等数据操作。数据控制语言DCL命令作用GRANT 对象的创建者赋予某用户或某组或所有用户某些特定的权限REVOKE 收回权限，与GRANT作用相反，但不影响从其他角

18、色继承权限DENY 收回权限，并禁止从其他角色继承许可权限 COMMIT 提交当前事务，使事务中执行的变更永久化ROLLBACK恢复数据到修改(update，insert，delete)未commit 之前的状态用来控制存取许可、管理权限等。创建数据库 每个数据库至少有两个文件（一个主文件和一个日志文件）和一个文件组 为便于分配和管理，可以将数据库对象和文件一起分成文件组 日志文件不包括在文件组内。日志空间与数据空间分开管理CREATE DATABASE 数据库名数据库名 ON PRIMARY ( ，n ) LOG ON ( ，n )主文件组，可选参数，默认主文件组，可选参数，默认创建数据库的

19、语法创建数据库的语法创建数据库示例 1 使用CREATE DATABASE语句创建数据库MySchool，要求： 该数据库具有一个数据文件和一个日志文件 文件保存在D盘已有的文件夹Project下 数据文件的大小初始为5MB，最大值为100MB，文件增长率是15% 日志文件的大小初始为2MB，增长量是1MBCREATE DATABASE MySchool ON PRIMARY -默认就属于默认就属于PRIMARY主文件组，可省略主文件组，可省略( NAME= MySchool_data, -主数据文件的逻辑名主数据文件的逻辑名 FILENAME=D:projectMySchool_data.m

20、df, -主数据文件的物理名主数据文件的物理名 SIZE=5MB, -主数据文件初始大小主数据文件初始大小 MAXSIZE=100MB, -主数据文件增长的最大值主数据文件增长的最大值 FILEGROWTH=15% -主数据文件的增长率主数据文件的增长率)LOG ON( NAME=MySchool_log, FILENAME=D:projectMySchool_log.ldf, SIZE=2MB, FILEGROWTH=1MB)GO 数据文件的具数据文件的具体描述体描述日志文件的具日志文件的具体描述体描述创建数据库示例 2使用CREATE DATABASE语句创建数据库Employee，要求：

21、文件保存在D:Project文件下 第一个主数据文件以employee1命名，初始大小是10MB，增长量是10% 第二个次要数据文件以employee2命名，初始大小是20MB，最大值是100MB，增长量是1MB 第一个日志文件以employeelog1命名，初始为10MB，增长量是1MB 第二个日志文件以employeelog2命名，初始大小和增长量与第一个日志文件相同，但最大值是50MBCREATE DATABASE Employee ON ( -主数据文件的具体描述主数据文件的具体描述 NAME = employee1, FILENAME = D:projectemployee1.mdf

22、 , SIZE = 10, FILEGROWTH = 10% ), ( -次要次要数据文件的具体描述数据文件的具体描述 NAME = employee2, FILENAME = D:projectemployee2.ndf , SIZE = 20, MAXSIZE = 100, FILEGROWTH = 1 ) LOG ON ( -日志文件日志文件1的具体描述的具体描述 NAME = employeelog1, FILENAME = D:projectemployeelog1_Log.ldf , SIZE = 10, FILEGROWTH = 1 ), ( -日志文件日志文件2的具体描述的具体

23、描述- NAME = employeelog2, FILENAME = D:projectemployeelog2_Log.ldf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 1 )GO 多个数据文件多个数据文件多个日志文件多个日志文件主主数据文件数据文件次次数据文件数据文件 SQL语句中的“-”用于单行注释，格式为：-注释内容。SQL语句的多行注释为“/*/”。“/*”用于注释文字的开头，“*/”用于注释文字的结尾，格式为：/*注释内容*/。 GO 不是T-SQL语句，是一个不需任何权限的实用工具命令。 它可以由任何用户执行。它向 SQL Server实例

24、发送当前批T-SQL语句。当前批语句由上一GO命令后输入的所有语句组成，如果是第一条GO 命令，则由脚本开始后输入的所有语句组成。GO命令不能和T-SQL语句在同一行中。SQL注释和GO语句批处理命令GO批处理是包含一个或多个批处理是包含一个或多个 SQL 语句的组，从应语句的组，从应用程序一次性地发送到用程序一次性地发送到SQL Server执行执行SQL Server 将每批将每批SQL语句编译成一个执行单语句编译成一个执行单元，此单元称为执行计划元，此单元称为执行计划客户端应用程序客户端应用程序SQL Server服务器服务器批处理：批处理：语句语句1语句语句2GO批处理命令示例 为了督

25、促学生学习，学校对多次考试不及格的学生给予处理。凡是一次考试不及格者，给予警告；三次以下（含）不及格者，肄业处理；三次以上不及格者，开除学籍CREATE TABLE Punish( -创建表创建表 StuNo int NOT NULL, -学号学号 StuCnt int NOT NULL, -不及格次数不及格次数 StuMng varchar(50) -处理意见处理意见)GOINSERT INTO Punish -插入数据插入数据 SELECT StudentNo 学号学号, COUNT(0) 不及格次数不及格次数, 处理意见处理意见 FROM Result WHERE StudentResu

26、lt3 -更新更新GOSELECT * FROM Punish -查询查询GO是批处理的标志，表示是批处理的标志，表示SQL Server将这些将这些SQL语句编译为一个执行单元，语句编译为一个执行单元，提高执行效率提高执行效率一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定求和代码编写者决定第一步：创建新表第一步：创建新表Punish，表结构是：，表结构是：处罚记录（学号、不及格次数、处理意见）处罚记录（学号、不及格次数、处理意见）创建新表时处理意见为空创建新表时处理意见为空第二步：查询所有

27、不及格的成绩第二步：查询所有不及格的成绩插入插入Punish表表中中第三步：根据每个学生不及格次数批量更新处理意见第三步：根据每个学生不及格次数批量更新处理意见常见错误CREATE TABLE Student ( ) GOGO是是SQLServer的批处理命令，只有代码编辑器才能识别并处理，编辑其的批处理命令，只有代码编辑器才能识别并处理，编辑其他应用程序就不能使用该命令他应用程序就不能使用该命令由于每个批处理之间是独立的，因此，在一个批处理出现错误时，并不会影由于每个批处理之间是独立的，因此，在一个批处理出现错误时，并不会影响其他批处理中响其他批处理中SQL代码的运行代码的运行GO 命令和命

28、令和 SQL 语语句不能在同一行句不能在同一行上机练习2-7、2-8 上机练习上机练习2-7使用T-SQL创建数据库StudentInfo 上机练习上机练习2-8使用T-SQL创建多个数据文件的数据StudentInfo 需求说明：创建包含3个数据文件和2个日志文件的数据库，并将两个次数据文件存储在名称为groupfile1的文件组中。练习2-8分析CREATE DATABASE StudentInfoON PRIMARY(-主文件部分省略主文件部分省略),FILEGROUP groupfile1(NAME= StudentInfo_Data1,FILENAME=E:DBStudentInfo

29、_Data1.ndf,SIZE=2MB,MAXSIZE=10MB,FILEGROWTH=5%),(NAME= StudentInfo_Data2,FILENAME=E:DBStudentInfo_Data2.ndf,-其他同上其他同上)/*接下页接下页*/LOG ON(NAME= StudentInfo _Log,FILENAME=E: DBStudentInfo _Log.ldf,SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=10%),(NAME= StudentInfo _Log1,FILENAME=E:DBStudentInfo_Log1.ldf,-其他同上其他同上)

30、 使用T-SQL语句修改数据库修改数据库的语法格式：修改数据库的语法格式：ALTER DATABASE 数据库名称 ADD FILE ,.n TO FILEGROUP 文件组名称 |REMOVE FILE 数据文件名称 |MODIFY FILE |ADD LOG FILE ,.n |ADD FILEGROUP 文件组名称 |REMOVE FILEGROUP文件组名称 |MODIFY FILEGROUP文件组名称 文件组属性|NAME=修改后文件组名称 |MODIFY NAME=修改后数据库名称 |SET ,.n修改数据库名称ALTER DATABASE BookStore MODIFY NAME=NewBookStore -修改数据库名称示例示例2-3 ：修改数据库：修改数据库BookStore名称为名称为NewBookStore向数据库中添加数据文件ALTER DATABASE BookStoreADD FILE ( NAME=books, FILENAME=E:DBbooks.ndf, SIZE=1MB, FILEGROWTH=1MB)示例示例2-4 ：向数据库：向数据库BookStore中添加数中添加数据文件据文件books。修改数据库文