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

1、 本章目标本章目标 新建数据库登录名新建数据库登录名 SQL Server 2008数据库 SQL Server 2008数据库分为系统数据库和用户数 据库。 系统数据库有数据库管理系统管理，负责管理用户 数据库和其他数据库对象。 用户数据库用来存储用户数据，主要由数据库管理 员管理。 SchoolDBPubsNorthWind master modeltempdbmsdbResource 系统数据库系统数据库 用户数据库用户数据库 系统数据库和用户数据库 CH02使用SSMS创建和管理数据 库 系统数据库 1.master数据库数据库 master数据库记录SQL Server中 所有系统级

2、别的信息，包括所有登录账户、系统配 置和设置、服务器中其他数据库的相关信息和这些 数据库文件的位置，以及SQL Server初始化信息等。 2. model数据库数据库 model数据库用于创建所有数据库 的模板，是tempdb数据库的基础。例如，使用SQL 语句创建新数据库时，将使用模板中规定的默认值 来创建。 3.tempdb数据库数据库 tempdb数据库是一个临时性的数 据库，保存所有的临时表和临时存储过程，以及临 时工作表。关闭SQL Server服务时，tempdb数据库 保存的内容将自动消失，重新启动SQL Server服务 时，系统将重新创建空的tempdb数据库。 4.msd

3、b数据库数据库 msdb数据库提供SQL Server代 理服务的调度警报和作业，也会记录数据库备 份和还原信息。 5.resource数据库数据库 resource数据库是只读数据 库，包含SQL Server中所有系统对象，这些系 统对象在物理上保存在resource 数据库中，但 在逻辑上，他们显示在每个数据库的 sys 架构 中。resource数据库是一个“隐藏”了的数据 库，我们无法使用SQL命令看到它，但可以查 看它的一些信息。 登录名和数据库用户 登录名登录名 用以登录用以登录SQL ServerSQL Server数据库服务器数据库服务器 数据库用户数据库用户 用以登录成功后

4、，访问某个具体的用户数据库用以登录成功后，访问某个具体的用户数据库 CH02使用SSMS创建和管理数据 库 示例示例2-1 建立登录名建立登录名 第一步：在“对象资源管理器”中单击“安全性”文件 夹图标前的“+”图标，右键单击“登录名”图标，选择 “新建登录名”选项。 CH02使用SSMS创建和管理数据 库 第二步：在新建登录名对话框中，填写登录名，选择“SQL Server身份验证”，输入密码和确认密码。 CH02使用SSMS创建和管理数据 库 第三步：赋予该登录名操作权限。如果已经为某数据库创建 了用户，也快成通过用户映射赋予该登录名对该数据库的 操作权限。 CH02使用SSMS创建和管理

5、数据 库 固定服务器角色 固定服务器角色 权限描述 sysadmin能够执行SQL Server上的任何操作 serveradmin设置服务器范围的配置选项和关闭服务器 setupadmin管理链接服务器和启动过程 securityadmin管理登录名、读取错误日志和创建数据库 processadmin管理SQL Server中运行的进程 public仅可以连接和查看数据库，每个登录名均属于 public 服务 器角色 dbcreator可以创建、更改、还原和删除数据库 diskadmin可以管理磁盘文件 bulkadmin 可以执行 BULK INSERT 语句，以用户指定的格式复制一个数

6、据文件至数据库表或视图中 CH02使用SSMS创建和管理数据 库 上机练习2-1 创建登录名 训练要点训练要点： 1、SSMS的使用 。 2、创建登录名、熟悉固定系统角色。 3、使用创建的登录名登录数据库 需求说明需求说明： 使用SSMS图形工具创建数据库登录名 SQLdb，并赋予其dbcreator角色 。 CH02使用SSMS创建和管理数据 库 数据库文件相关概念 一个数据库至少有一个数据文件和一个事务日志文件。 CH02使用SSMS创建和管理数据 库 数据文件数据文件 数据文件用于存放数据库的数据和各种对象。 一个数据库可以有一个或多个数据文件。 当有多个数据文件时，数据文件又可以分成主

7、数据 文件和次数据文件两种。 每一个数据库都有且仅有一个主数据文件。主数据 文件名称的默认后缀是.mdf。次数据文件用来存放 不在主数据文件中的其他数据和对象，可以没有、 也可以有一个或多个，默认后缀是.ndf。 数据文件 CH02使用SSMS创建和管理数据 库 事务日志文件事务日志文件 事务日志文件用于存放事务日志 ，记录对数据库的所有修改操作。每一个数 据库都至少有一个日志文件，也可以有多个 。日志文件的默认后缀是.ldf。 文件组文件组 在SQL Server系统内部，每个数据库物 理文件都对应一个逻辑文件名，文件组就是 文件的逻辑集合，它把一些指定的文件组合 在一起，以方便管理和分配数

8、据。 事务日志文件和文件组 CH02使用SSMS创建和管理数据 库 示例示例2-2 使用使用SSMS创建数据库创建数据库 示例：创建的登录名SQLdb创建“简易银行系 统”数据库MyBank 第一步：从“开始”菜单中找到“Microsoft SQL Server 2008”菜单项，打开SQL Server Management Studio窗口，以SQLdb登录名连 接到SQL2008数据库实例。 CH02使用SSMS创建和管理数据 库 第二步：右键单击“对象资源管理器”窗口中的 “数据库”图标，选择“新建数据库”。 CH02使用SSMS创建和管理数据 库 第三步：在“常规”页中指定数据库属性

9、。 1、“数据库名称”文本框填写MyBank，在“所有 者”文本框填写数据库所有者名称，或者单击右 面的“.”按钮选择，默认值指的是当前登录名 SQLdb。 CH02使用SSMS创建和管理数据 库 2、设置文件属性 逻辑名称逻辑名称：指定该文件的文件名。 文件类型文件类型：可选择行数据或日志，用于指定当前文件 是数据文件还是日志文件。 文件组文件组：显示当前数据库文件所属的文件组。 初始大小初始大小：指定该文件的初始容量。 自动增长自动增长：设置当文件的容量不够用时，文件根据何 种增长方式自动增长。 路径路径：指定存放该文件的目录。在默认情况下，SQL Server 2008将存放路径设置为S

10、QL Server 2008安 装目录下的data子目录。 CH02使用SSMS创建和管理数据 库 第四步：单击选择页中“选项”按钮， 设置数据库选项 需要关注的几个选项： 兼容级别：表示数据库向以前的版本兼容的级别，例如设 置为“SQL Server 2005 (90)”，那么SQL Server 2005 也能识别和操作该数据库。 数据库为只读：默认为false，若选择true，则无法写入数 据。 限制访问：指定可访问的用户。有三种选项： Multiple_User，数据库的正常状态，允许多个用户同时访 问；Single_User，只允许一个用户访问，用于数据库维 护状态；Restrict

11、ed_User，只允许管理员或特定成员才能 访问。 CH02使用SSMS创建和管理数据 库 需求说明需求说明： 物理文件路径：D:StudentInfo； 数据文件：初始大小10MB，允许自动增长，数据文件 大小不受限制； 日志文件：初始大小1MB，最大30MB；其它选项：自 动收缩、不创建统计信息、自动关闭。 训练要点训练要点： 1、使用SSMS创建数据库。 2、设置数据库各个参数。 上机练习2-2 新建StudentInfo学生信息数据库 CH02使用SSMS创建和管理数据 库 示例：示例：为数据库MyBank新建数据库用户 第一步：在“对象资源管理器”窗口中打开MyBank 数据库右键单

12、击“安全性”新建用户 新建数据库用户 CH02使用SSMS创建和管理数据 库 第二步：在“新建用户”窗口中输入用户名 ，选择关联的登录名。例如用户名为 cashier，登录名为dbuser（假设此登录名 除了public以外没有任何服务器和数据库角 色，也就是他只能连接数据库）。 CH02使用SSMS创建和管理数据 库 第三步：为用户选择数据库架构和角色。 CH02使用SSMS创建和管理数据 库 固定数据库角色 固定数据库角色描述 db_owner在数据库中有全部权限 db_accessadmin可以为 Windows 登录名、Windows 组和 SQL Server 登 录名添加或删除数据

13、库访问权限 db_securityadmin能够管理角色和数据库角色成员，并能管理语句和对象的 许可权限 db_ddladmin可以在数据库中运行任何数据定义语言 (DDL) 命令，能 够在数据库中添加、修改或删除对象 db_backupoperator 能够备份数据库 db_datareader能够在数据库中所有用户表上执行SELECT语句 db_datawriter能够在数据库所有用户表上执行INSERT、UPDATE和DELETE 语句 db_denydatareader 对数据库中的数据库对象(表或视图)没有SELECT权限，不 能读取数据库内用户表中的任何数据 db_denydata

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

15、.架构.对象。 到此我们创建了MyBank数据库用户并 关联到登录名，现在您可以使用关联的 登录名登录数据库看看该登录名是否对 该数据库有操作权限？ CH02使用SSMS创建和管理数据 库 需求说明： 为StudentInfo数据库创建登录名StuInfoLogin 和用户名StuInfoUser，StuInfoLogin没有权 限，StuInfoUser有db_owner角色，将登录 名和用户名相关联。 训练要点： 1、建立数据库连接、创建登录名。 2、创建数据库用户名、设置角色权限。 上机练习2-3 为StudentInfo数据库创建登录名和用户名 CH02使用SSMS创建和管理数据 库

16、修改数据库 在“对象资源管理器”窗口中右键单击 MyBank数据库，选择属性，在打开的数据 库属性窗口中可以通过“选择页”中的选 项，对数据库的属性设置进行修改。 CH02使用SSMS创建和管理数据 库 删除数据库 在“对象资源管理器”窗口中选择要删除的 数据库“MyBank”，单击右键，在弹出的 窗口中选择“删除”菜单项，打开 “删除 对象”窗口，单击右下角的“确定”按钮 ，即可以删除数据库“MyBank”。 CH02使用SSMS创建和管理数据 库 上机练习2-4 修改数据库属性 需求说明： 重新设置MyBank数据库的数据文件增长方式 为自动增长、增量为1MB、最大值为 100MB。 训练

17、要点： 使用SSMS修改数据库属性。 CH02使用SSMS创建和管理数据 库 分离数据库 如果要分离的数据库有打开的文件，应首先关闭。 CH02使用SSMS创建和管理数据 库 附加数据库 CH02使用SSMS创建和管理数据 库 上机练习2-5 分离和附加数据库 需求说明： 将MyBank数据库文件复制到E盘根目录，然 后附加。 训练要点：数据库的分离和附加。 实现思路： 首先分离数据库，将数据库剪切或复制，然 后粘贴到E盘根目录E:后，再附加数据库。 CH02使用SSMS创建和管理数据 库 备份和还原数据库 SQL Server有四种备份方式： 完全备份完全备份 备份数据库的所有内容，包括所有

18、 数据库对象和事务日志。 事务日志备份事务日志备份 只备份自上次备份事务日志后 对数据库执行的所有事务记录。 差异备份差异备份 只记录自上次数据库备份后发生更 改的数据部分。 文件和文件组备份文件和文件组备份 当数据库非常庞大时，可 执行数据库文件或文件组备份。这种备份策 略使用户只还原已损坏的文件或文件组。 CH02使用SSMS创建和管理数据 库 完全备份数据库 CH02使用SSMS创建和管理数据 库 在“备份到”选项中选择“磁盘”，如果想改变系 统默认备份地址，可单击“添加”按钮，更改备份 路径，然后单击“确定”即可完成备份。 CH02使用SSMS创建和管理数据 库 还原数据库 CH02使

19、用SSMS创建和管理数据 库 上机练习2-6 备份和还原数据库 需求说明： 假设数据库StudentInfo已经完全备份，现在 数据库受到损坏，需要通过备份文件将其 还原到正常状态。 训练要点： 1、SSMS的使用。 2、备份、分离、还原数据库。 CH02使用SSMS创建和管理数据 库 T-SQL简介 SQL是结构化查询语言(Structured Query Language)的英文缩写，是一种使用关系模 型的数据库应用语言。 分为如下几类： 1、数据定义语言(Data Definition Language，DDL) 2、数据操纵语言(Data Manipulation Language，

20、DML) 3、数据控制语言(Data Control Language，DCL) CH02使用SSMS创建和管理数据 库 数据定义语言DDL 命令作用 CREATE 创建数据库或数据库对象 ALTER 修改数据库或数据库对象 DROP 删除数据库或数据库对象 用来建立数据库、数据库对象和定义表的列等。 CH02使用SSMS创建和管理数据 库 命令作用 SELECT 从表或视图中检索数据 INSERT 将数据插入到表或视图中 UPDATE 修改表或视图中的数据 DELETE 从表或视图中删除数据 数据操纵语言DML 用于更改数据库数据， 包括插入、删除和修改等数据操作。 CH02使用SSMS创建

21、和管理数据 库 数据控制语言DCL 命令作用 GRANT 对象的创建者赋予某用户或某组或所有 用户某些特定的权限 REVOKE 收回权限，与GRANT作用相反，但不影 响从其他角色继承权限 DENY 收回权限，并禁止从其他角色继承许可 权限 COMMIT 提交当前事务，使事务中执行的变更永 久化 ROLLBA CK 恢复数据到修改(update，insert，delete) 未commit 之前的状态 用来控制存取许可、管理权限等。 CH02使用SSMS创建和管理数据 库 创建数据库 每个数据库至少有两个文件（一个主文件 和一个日志文件）和一个文件组 为便于分配和管理，可以将数据库对象和 文件

22、一起分成文件组 日志文件不包括在文件组内。日志空间与 数据空间分开管理 CREATE DATABASE 数据库名数据库名 ON PRIMARY ( ，n ) LOG ON ( ，n ) 主文件组，可选参数，默认主文件组，可选参数，默认 创建数据库的语法创建数据库的语法 CH02使用SSMS创建和管理数据 库 创建数据库示例 1 使用CREATE DATABASE语句创建数据 库MySchool，要求： 该数据库具有一个数据文件和一个日志文件 文件保存在D盘已有的文件夹Project下 数据文件的大小初始为5MB，最大值为 100MB，文件增长率是15% 日志文件的大小初始为2MB，增长量是1M

23、B CREATE DATABASE MySchool ON PRIMARY -默认就属于默认就属于PRIMARY主文件组，可省略主文件组，可省略 ( NAME= MySchool_data, -主数据文件的逻辑名主数据文件的逻辑名 =D:projectMySchool_data.mdf, -主数据文件的物理名主数据文件的物理名 SIZE=5MB, -主数据文件初始大小主数据文件初始大小 MAXSIZE=100MB, -主数据文件增长的最大值主数据文件增长的最大值 =15% -主数据文件的增长率主数据文件的增长率 ) LOG ON ( NAME=MySchool_log, =D:projectM

24、ySchool_log.ldf, SIZE=2MB, =1MB ) GO 数据文件的具数据文件的具 体描述体描述 日志文件的具日志文件的具 体描述体描述 CH02使用SSMS创建和管理数据 库 创建数据库示例 2 使用CREATE DATABASE语句创建数据库 Employee，要求： 文件保存在D:Project文件下 第一个主数据文件以employee1命名，初始 大小是10MB，增长量是10% 第二个次要数据文件以employee2命名，初 始大小是20MB，最大值是100MB，增长量 是1MB 第一个日志文件以employeelog1命名，初始 为10MB，增长量是1MB 第二个日志

25、文件以employeelog2命名，初始 大小和增长量与第一个日志文件相同，但最 大值是50MB CREATE DATABASE Employee ON ( -主数据文件的具体描述主数据文件的具体描述 NAME = employee1, = D:projectemployee1.mdf , SIZE = 10, = 10% ), ( -次要次要数据文件的具体描述数据文件的具体描述 NAME = employee2, = D:projectemployee2.ndf , SIZE = 20, MAXSIZE = 100, = 1 ) LOG ON ( -日志文件日志文件1的具体描述的具体描述 N

26、AME = employeelog1, = D:projectemployeelog1_Log.ldf , SIZE = 10, = 1 ), ( -日志文件日志文件2的具体描述的具体描述- NAME = employeelog2, = D:projectemployeelog2_Log.ldf , SIZE = 10, MAXSIZE = 50, = 1 ) GO 多个数据文件多个数据文件 多个日志文件多个日志文件 主主数据文件数据文件 次次数据文件数据文件 CH02使用SSMS创建和管理数据 库 SQL语句中的“-”用于单行注释，格式为 ：-注释内容。SQL语句的多行注释为 “/*/”。“

27、/*”用于注释文字的开头， “*/”用于注释文字的结尾，格式为：/*注 释内容*/。 GO 不是T-SQL语句，是一个不需任何权 限的实用工具命令。 它可以由任何用户执 行。它向 SQL Server实例发送当前批T- SQL语句。当前批语句由上一GO命令后输 入的所有语句组成，如果是第一条GO 命令 ，则由脚本开始后输入的所有语句组成。 GO命令不能和T-SQL语句在同一行中。 SQL注释和GO语句 CH02使用SSMS创建和管理数据 库 批处理命令GO 批处理是包含一个或多个批处理是包含一个或多个 SQL 语句的组，从应语句的组，从应 用程序一次性地发送到用程序一次性地发送到SQL Ser

28、ver执行执行 SQL Server 将每批将每批SQL语句编译成一个执行单语句编译成一个执行单 元，此单元称为执行计划元，此单元称为执行计划 客户端应用程序客户端应用程序 SQL Server服务器服务器 批处理：批处理： 语句语句1 语句语句2 GO CH02使用SSMS创建和管理数据 库 批处理命令示例 为了督促学生学习，学校对多次考试不及 格的学生给予处理。凡是一次考试不及格 者，给予警告；三次以下（含）不及格者 ，肄业处理；三次以上不及格者，开除学 籍 CREATE TABLE Punish( -创建表创建表 StuNo int NOT NULL, -学号学号 StuCnt int

29、NOT NULL, -不及格次数不及格次数 StuMng varchar(50) -处理意见处理意见 ) GO INSERT INTO Punish -插入数据插入数据 SELECT StudentNo 学号学号, COUNT(0) 不及格次数不及格次数, 处理意见处理意见 FROM Result WHERE StudentResult3 -更新更新 GO SELECT * FROM Punish -查询查询 GO是批处理的标志，表示是批处理的标志，表示SQL Server将这些将这些SQL语句编译为一个执行单元，语句编译为一个执行单元， 提高执行效率提高执行效率 一般是将一些逻辑相关的业务操

30、作语句，放置在同一批中，这完全由业务需一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需 求和代码编写者决定求和代码编写者决定 第一步：创建新表第一步：创建新表Punish，表结构是：，表结构是： 处罚记录（学号、不及格次数、处理意见）处罚记录（学号、不及格次数、处理意见） 创建新表时处理意见为空创建新表时处理意见为空 第二步：第二步：查询所有不及格的成绩查询所有不及格的成绩插入插入Punish表表中中 第三步：根据每个学生不及格次数批量更新处理意见第三步：根据每个学生不及格次数批量更新处理意见 CH02使用SSMS创建和管理数据 库 常见错误 CREATE TABLE Stu

31、dent ( ) GO GO是是SQLServer的批处理命令，只有代码编辑器才能识别并处理，编辑其的批处理命令，只有代码编辑器才能识别并处理，编辑其 他应用程序就不能使用该命令他应用程序就不能使用该命令 由于每个批处理之间是独立的，因此，在一个批处理出现错误时，并不会影由于每个批处理之间是独立的，因此，在一个批处理出现错误时，并不会影 响其他批处理中响其他批处理中SQL代码的运行代码的运行 GO 命令和命令和 SQL 语语 句不能在同一行句不能在同一行 CH02使用SSMS创建和管理数据 库 上机练习2-7、2-8 上机练习上机练习2-7 使用T-SQL创建数据库StudentInfo 上机

32、练习上机练习2-8 使用T-SQL创建多个数据文件的数据StudentInfo 需求说明： 创建包含3个数据文件和2个日志文件的数据 库，并将两个次数据文件存储在名称为 groupfile1的文件组中。 CH02使用SSMS创建和管理数据 库 练习2-8分析 CREATE DATABASE StudentInfo ON PRIMARY ( -主文件部分省略主文件部分省略 ), groupfile1 ( NAME= StudentInfo_Data1, E:DBStudentInfo_Data1.ndf, SIZE=2MB, MAXSIZE=10MB, ), ( NAME= StudentInf

33、o_Data2, E:DBStudentInfo_Data2.ndf, -其他同上其他同上 ) /*接下页接下页*/ CH02使用SSMS创建和管理数据 库 LOG ON ( NAME= StudentInfo _Log, E: DBStudentInfo _Log.ldf, SIZE=1MB, MAXSIZE=10MB, ), ( NAME= StudentInfo _Log1, E:DBStudentInfo_Log1.ldf, -其他同上其他同上 ) CH02使用SSMS创建和管理数据 库 使用T-SQL语句修改数据库 修改数据库的语法格式：修改数据库的语法格式： ALTER DATABASE 数据库名称 ADD FILE ,.n TO 文件组名称 |REMOVE FILE 数据文件名称 |MODIFY FILE |ADD LOG FILE ,.n |ADD 文件组名称 |REMOVE 文件组名称 |MODIFY 文件组名称 文件组属性|NAME=修改后文件组名称 |MODIFY NAME=修改后数据库名称 |SET ,.n CH02使用SSMS创建和管理数据