SQLServer基本操作

1.1. 本书的实验环境

本书的示例与windows和sql server环境有关，具体的环境如下：

windows环境：

windows 7 旗舰版 32位

主机名：guo-pc

用户：guo，属于administrators组

sql server环境：

sql server 2012 express(sql server 11.0.2100)

1.2. sql server组成

sql server提供包括引擎、analysis services、reporting services、integration services和master data serie服务等，这些服务由表 1 1中的服务器提供。其中，数据库引擎是sql server的数据库服务器，analysis services是olap分析服务器，reporting services是报表服务器，integration services是数据集成服务器。本书的内容只涉及数据库引擎服务器，它可以完成数据库的管理、安全管理(用户与权限)、数据库基本对象(表、索引、视图)和t-sql对象的管理等。

表 1 1 sql server主要服务器组件

服务器组件 说明

sql server 数据库引擎 sql server 数据库引擎包括数据库引擎(用于存储、处理和保护数据的核心服务)、复制、全文搜索、用于管理关系数据和 xml 数据的工具以及 data quality services (dqs) 服务器。

analysis services analysis services 包括用于创建和管理联机分析处理 (olap) 以及数据挖掘应用程序的工具。

reporting services reporting services 包括用于创建、管理和部署表格报表、矩阵报表、图形报表以及*格式报表的服务器和客户端组件。

reporting services 还是一个可用于开发报表应用程序的可扩展平台。

integration services integration services 是一组图形工具和可对象，用于移动、复制和转换数据。

它还包括 integration services 的 data quality services (dqs) 组件。

master data services master data services (mds) 是针对主数据管理的 sql server 解决方案。

可以配置 mds 来管理任何领域(产品、客户、帐户);mds 中可包括层次结构、各种级别的安全性、事务、数据版本控制和业务规则，以及可用于管理数据的 用于 excel 的外接程序。

表 1 2 sql server 主要管理工具

管理工具 说明

sql server management studio sql server management studio 是用于访问、配置、管理和开发 sql server 组件的集成环境。

management studio 使各种技术水平的开发人员和管理员都能使用 sql server。

management studio 的安装需要 internet explorer 6 sp1 或更高版本。

sql server 配置管理器 sql server 配置管理器为 sql server 服务、服务器协议、客户端协议和客户端别名提供基本配置管理。

sql server 事件探查器 sql server 事件探查器 提供了一个图形用户界面，用于监视数据库引擎实例或 analysis services 实例。

数据库引擎优化顾问 数据库引擎优化顾问可以协助创建索引、索引视图和分区的最佳组合。

数据质量客户端 提供了一个非常简单和直观的图形用户界面，用于连接到 dqs 数据库并执行数据清理操作。

它还允许您集中监视在数据清理操作过程中执行的各项活动。

数据质量客户端的安装需要 internet explorer 6 sp1 或更高版本。

sql server 数据工具 sql server 数据工具 (ssdt) 提供 ide 以便为以下商业智能组件生成解决方案：analysis services、reporting services 和 integration services。

(以前称作 business intelligence development studio)。

ssdt 还包含“数据库项目”，为数据库开发人员提供集成环境，以便在 visual studio 内为任何 sql server 平台(无论是内部还是外部)执行其所有工作。

数据库开发人员可以使用 visual studio 中功能增强的服务器资源管理器，轻松创建或编辑数据库对象和数据或执行查询。

sql server 数据工具安装需要 internet explorer 6 sp1 或更高版本。

连接组件 安装用于客户端和服务器之间通信的组件，以及用于 db-library、odbc 和 ole db 的网络库。

1.3. sql server安装

下载sql server express 2012

sql server 2012 包括enterprise、business intelligence、standard三个主要版本，另外，微软还提供web(professional)、developer和express版本。sql server express 2012是微软提供的一个免费版本。其下载的页面上提供了多种下载选项，其中，下载文件sqexprwt_x86_chs.exe包括数据库引擎和工具sql server management studio express。

注解：如果安装的不是express版本，则需要选择安装的组件。本书的内容中需要安装数据库引擎服务器组件和客户端工具组件。因为有些组件需要额外的安装条件，这会明显降低安装的速度，也可能导致安装失败。

sql server express 2012下载页面：

https://www.microsoft.com/zh-cn/download/details.x?id=29062

sql server express 2012 sqlexprwt_x86_chs.exe下载地址：

https://download.microsoft.com/download/3/6/e/36e9ca26-cc2c-4600-8d25-a152f9498fa1/chs/x86/sqlexprwt_x86_chs.exe)

安装示例数据库adventureworks

后面内容中所提供的示例均使用sql server示例数据库adventureworks。sql server 2012的示例数据库adventureworks需要单独安装，其安装文件adventureworks2012_data.mdf是adventureworks2012数据库的数据文件，其下载地址：

https://download-codeplex.sec.s-msft.com/download/release?projectname=msftdbprodsamples&downloadid=165399&filetime=129762331847030000&build=20717

可以使用下面两种方法之一安装示例数据库adventureworks。(若win10提示没有权限，需要以管理员权限运行sql server management studio)

方法一：

(1) 在sql server management studio中从数据库的弹出菜单中选择“附加”数据库。

(2) 在附加数据库对话框中添加数据库，选择文件adventureworks2012_data.mdf，注意要删除自动添加的日志文件，即完成adventureworks2012的安装。

(3) 重命名adventureworks2012为adventureworks

方法二：

(1) 在sql server management studio的查询分析器中执行下面的命令：

exec sp_attach_db @dbname=n’adventureworks2012’,

@filename1=n’d:\sql server 2000 sample databases\adventureworks2012_data.mdf’

go

use master

go

alter database adventureworks2012 modify name=adventureworks

go

1.4. sql server 数据库引擎实例

数据库引擎的实例(简称“实例”或“数据库实例”，你也可以把它称做“sql server实例”)是作为操作服务运行的 sqlservr.exe 进程。每个实例管理多个系统数据库(master、model、msdv和tempdb)以及零个或多个用户数据库(例如sql server示例数据库adventureworks)。

每台计算机可以运行数据库引擎的多个实例。如果是第一次安装sql server，可以安装一个默认实例，一台计算机上只能安装一个默认实例。默认实例没有名称，连接默认认实例只需要提供计算机名，而连接有名实例则需要提供计算机名和实例名，如guo-pc\sqlexpress，其中，guo-pc是运行实例的计算机名，sqlexpress是实例名。

应用程序必须连接到某一个实例，只有通过实例才能访问sql server数据库。

数据库实例与其它的计算机进程没有本质上的区别，都是可执行程序的运行副本，所不同的是访问的数据不同。例如，运行word的可执行程序文件winword.exe便会产生一个该程序的winword.exe进程，它可以称为word实例，重复运行就会产生多个word实例，每个word文档都必须通过一个word实例才能够访问。word文档就相当于word数据库。

注解：严格意义上讲，不通过word实例也可以访问word文档。但从安全性的角度考虑，数据库软件厂商不会公开其数据库的内部结构(除了像aceess个人数据库外)，也不会提供访问数据库的其它方式，只能通过实例访问数据库。

实例处理所有应用程序请求的数据库服务操作。在完成某一连接后，应用程序通过该连接将 transact-sql 语句发送给该实例。该实例将这些 transact-sql 语句解析为针对数据库中的数据和对象的操作并将操作的结果或错误消息返回给应用程序。

注解：与sql server不同，oracle的实例定义为由后台进程和系统全局区sga(用于访问数据库的内存)两部分组成，oracle实例并不负责解析sql语句，也不负责管理连接和传送数据，这些任务由oracle服务器进程完成。

1.5. sql server服务

sql server服务是sql server引擎的windows 进程。在操作数据库前，必须要保证该服务是处于运行状态。可能通过sql server配置管理器(在sql server配置管理菜单项中)来启动、关闭及配置这个服务。(如图 1 1所示)

图 1 1 sql server配置管理器

也可以通过windows的服务管理器来启动和关闭该服务(图 1 2)。

图 1 2 windows服务管理器中的sql server服务

1.6. sql server management studio工具

1.6.1. 连接sql server数据库引擎

运行sql server management studio(简称ssms)。第一个出现的界面是连接sql server服务器的对话框，如图 1 3。

在图 1 3中，在服务器类型中选择“数据库引擎”(注：如果只安装了数据库引擎组件，则服务器类型列表框不能选择，它自动选择“数据库引擎”)。

服务器名称“guo-pc\sqlexpress”中的guo-pc是所连接的服务器的主机名，而sqlexpress则是数据库实例名。如果连接默认实例，则只有主机名。

身份验证列表框列出了“windows身份验证”和“sql server身份验证”两种方式。身份验证在后面有专门的实验进行练习，因此在这里选择“windows 身份验证”。

当成功连接到sql 数据库服务器后，则进入ssms的基本操作界面，如图 1 2所示。

检查sql server服务器的基本信息

对象资源管理器(图 1 4)的根节点是一个连接的数据库引擎节点，显示了sql server数据库引擎的基本信息guo-pc\sqlexpress(sql server 11.0.2100-guo-pc/guo)，包括运行数据库引擎服务器的主机名guo-pc、数据库实例名sqlexpress(如果是默认实例则没有实例名)、sql server数据库引擎的版本11.0.2100、连接数据库引擎的登录名guo-pc\guo。

上面的示例是数据库引擎在本地主机运行，所以，服务器名称和登录名中的主机都是guo-pc。如果使用guo-pc上的用户连接的服务器运行的主机名是server1、实例名为eexpress，则显示的信息应为：server1\sqlexpress(sql server 11.0.2100-guo-pc/guo)。

浏览对象资源管理器工具栏

其中，ssms左边的窗口是对象资源管理器，右边的窗口是摘要窗口。在对象资源管理器窗口的工具栏中有五个按钮，分别是连接、断开连接、停止、刷新和筛选。如果需要使用新的用户建立与数据库服务器的连接，则可以使用工具栏的“连接”按钮并选择“数据库引擎”，这样就会出现与图 1 3完全相同的界面。

使用查询编辑器

可以使用ssms的“新建查询”或者“数据库引擎查询”打开一个查询编辑器(如图 1 4所示)。查询编辑器中可以执行t-sql语句，它还可以将其中的t-sql语句保存到相应的文件中。需要注意的是，查询编辑器标题栏中的连接信息，它决定了执行t-sql语句的环境与权限，图 1 4中的guo-pc\guo表示使用登录guo-pc\guo连接了数据库引擎。

图 1 6 ssms查询编辑器

检查数据库

展开sql server服务器下面的节点“数据库”后会显示该数据库服务器上所有的数据库。其中，有系统数据库和示例数据库“adventureworks”。进一步展开“系统数据库”节点后会显示所有的系统数据库，这些数据库的具体信息会在后续的内容中进一步学习。

可以选择其中的一个数据库节点如master，展开后显示该数据库的更详细的信息，如图 1 5。

检查数据库服务器的安全性

展开对象资源管理器的“安全性”节点，可以查看数据库服务器的安全性，包括登录名、服务器角色和凭据。

注意登录名中的“guo-pc\guo”，它的存在使得以windows的用户guo登录到windows后可以通过windows身份验证模式直接连接到数据库服务器。具体的机制在后面的内容中会作进一步的解释。

1.7. 设置sql server身份验证模式

sql server提供了两种身份验证模式，一是windows身份验证模式，二是sql server和windows身份验证模式(简称混合验证模式)。混合验证模式可以使用windows身份验证模式，也可以使用sql server身份验证模式。windows身份验证模式是假设登录到windows的用户是安全的，在连接到sql server时不需要提供用户密码。sql server身份验证模式在连接数据库服务器时必须提供sql server的登录名和相应的密码。两种身份验证模式都使用登录连接数据库服务器。

两种验证模式中，windows身份验证模式更为常用。特别是通过应用程序访问数据库时，由于不需要在应用程序中硬编码登录密码，所以提高了数据库服务器的安全性。而有些应用，比如用户通过internet直接访问数据库时，由于用户本身无法登录到数据库服务器或其所在的域，因而就必需使用sql server身份验证模式。另外，有一些应用程序本身只支持sql server身份验证，例如erwin data modeler 4.0。所以本实验的目的是通过操作掌握数据库服务器的身份验证模式的设置，以根据不同的应用设置不同的验证模式。

图 1 8 修改sql server的验证模式

修改sql server身份验证的操作很简单，选择ssms中对象资源管理器的sql server服务器节点，通过弹出菜单的“属性”项进入到修改属性对话框。选择对话框左侧列表中的“安全性”，右侧的窗口中出现服务器安全属性窗口。在图 1 8中选择sql server和windows身份验证模式。当前是windows身份验证模式，它是安装时设定的，安装时也可以设定为混合验证模式。

1.8. 登录管理

为了测试sql server登录的管理，首先需要新创建一个windows用户。下面的实验中新创建的windows用户的名称为david。

为了测试如何操作才能使一个windows的用户以windows的身份验证模式登录到sql server，我们在实验的过程中分成多个步骤，这样的步骤被分成创建windows用户、在sql server中创建相应的登录和给sql server的登录分配相应的权限三个步骤。每个步骤完成后进行测试以检查不正确的配置会发生什么样的问题，这样以加深对整个配置过程的理解。

具体的操作步骤如下：

(1) 使用管理员用户guo创建windows用户david。

(2) 使用新的用户david登录windows并连接sql server。如果成功则进行第5步，否则，进行第3步。

(3) 使用用户guo为david用户创建sql server登录guo-pc\david。

(4) 第二次使用新的用户david登录windows并连接sql server。

(5) 使用管理员用户guo连接数据库并设置sql server登录guo-pc\david的默认数据库和权限。

1.8.1. 第一次连接sql server

使用新创建的windows用户重新登录windows，注意检查ssms连接对话框中的连接信息是否是如图 1 9所示内容，特别是用户名是否是本地主机的新用户david。然后使用ssms连接sql server，连接成功。

注解：在有些环境下，可能连接失败。windows用户使用windows身份验证连接数据库服务器必须在数据库服务器中有对应的登录。对应的登录有两种：一是为windows用户创建的登录，例如，windows主机guo-pc上的用户david创建的数据库登录名为guo-pc\david，david用户使用该登录连接数据库服务器;二是为windows用户组创建的登录，例如，在数据库服务器中有一个名为builtin\users的登录，它是一个内置的登录，与所有属于users组的windows用户对应，每个属于windows用户组users的用户都使用该登录连接数据库(在没有对应用户登录的情况下)。如果在数据库服务器中没有对应的登录，则连接失败。

1.8.2. 创建sql server登录

新建登录需要使用具有相应权限的windows用户重新登录windows并连接sql server。在sql server对象资源管理器中选择“安全性”，点击右键并选择“新建登录名…”进入到sql server新建登录对话框(如图 1 10所示)。在“登录名”文本框中输入所要创建的登录名guo-pc\david，其中，guo-pc表示主机名，david表示主机guo-pc上的windows用户名。

注解：可以使用图 1 7中右边窗口中的“脚本”将要执行的操作转换为t-sql语句并显示在一个查询编辑器窗口中，或者保存到一个文件中。

完成新建登录后，可以通过ssms查看新建的登录是否确实存在。展开ssms的“对象资源管理器”的“安全性”节点，检查前面操作所创建的sql server登录guo-pc\david是否存在。

1.9. 修改sa登录的口令及状态

sa登录是sql server的一个特殊登录，使用它连接数据库服务器后可以完成对服务器所有的管理操作，因此，需要对sa登录进行重点保护。

sa登录的口令在安装过程中可以设定，但如果在安装时选择的是windows身份验证模式则无法设定它的密码，且该登录的状态也被设定为“禁用”。

修改sa登录的密码与状态与修改其它sql server登录的密码与状态具有相同的操作过程。从ssms的“安全性”节点选择相应的登录(这里为sa)，进入到登录属性修改对话框。

图 1 11 sa登录属性

输入新的密码，然后选择左边列表中的“状态”，进入到状态属性修改页面，如图 1 10所示。注意，sa初始的状态为“禁用”，把登录状态修改为“启用”并点击确定。

图 1 12 sa登录的状态

1.10. 使用sql server验证模式连接数据库服务器

在完成对数据库服务器的登录验证模式的修改后，可以使用windows身份验证也可以使用sql server身份验证模式登录数据库服务器。在sql server登录sa属性的修改后，现在可以使用sql server登录连接数据库服务器了。

使用ssms断开与服务器的连接，重新连接时使用sql server登录模式并使用sa用户登录连接数据库服务器，如图 1 14所示。连接后注意ssms中显示的信息的变化。

1.11. 小结

通过本实验完成了对ssms的初步认识，ssms其它的功能操作分别在以后的各实验进行完成。如果需要系统的了解ssms的功能，可以参考sql server的联机文档。