SqlServer基础安装与操作

概述

MS SQL Server是一种关系型数据库管理系统(RDBMS)，由Microsoft开发和维护。它支持数据存储、数据检索和数据分析等功能，并提供了高可用、安全性和可扩展性等特性。SQL Server广泛应用于企业级应用程序、数据仓库和商业智能系统等领域。

数据库就像一个仓库，它按照数据结构来组织、存储和管理数据，是计算机软件的组成部分。不管是网站开发、桌面软件还是手机应用开发，只要涉及数据的存储管理，都离不开它。对于学习程序开发的人员来说，掌握数据库的使用方法是很重要的。

版本

主要版本

• 企业版

  考虑保费发售，企业版的目的是向大规模数据中心和数据仓库解决方案。数据管理和商业智能平台，提供企业级的可扩展性，高可用性和安全运行的关键任务应用。数据中心版的所有功能（这是包含在SQL Server的早期版本，但现已不用）在SQL Server 2014企业版。

• 标准版

    标准版的目的是对那些通常出现在规模较小的组织或部门的部门数据库和有限的商业智能应用。

• 商业智能版

  针对向那些需要企业商业智能和自助服务功能，但不需要完整的在线事务处理（OLTP）性能和可扩展性到企业版。

• 专业版
  • 开发版(也称为精简版)

    免费，嵌入式软件开发人员可以使用ASP.NET构建网站和Windows桌面应用程序的数据库。

  • Web版本

    旨在支持面向Internet的工作负载，使企业能够快速部署网页，应用程序，网站和服务。

  • Express版本

    SQL Server理想的学习和构建桌面和小型服务器应用程序的免费版本，限于每个数据库10GB存储空间。

必备条件

软件条件

硬件条件

SQL Server安装

1. 双击安装文件
2. 单击后会显示是否要允许以下程序在这台电脑进行安装？
3. 如果你想执行一个新的独立安装或从以前版本的 SQL Server 升级可以按照下述操作进行。执行新的独立安装，单击新的 SQL Server 单机安装或添加功能到现有安装：
   
   
4. 如果您同意许可条款，请选择我接受许可条款，然后单击下一步：
   
   
5. 接着进行功能的选择，选择或取消您想要或不要的功能。 您可以通过点击看到每个功能的说明。该说明显示在右上方窗格中。 在此窗格中，您可以看到基于所选功能的前提条件和要求。如果你愿意，你也可以更改列在底部的默认目录。下面的截图显示的默认功能（这是我用来安装SQL Server）。满意所选定的功能后单击下一步：
   
   
   
   
6. 指定实例路径（或使用默认设置），然后进行下一步：
   
   
   
   
7. 在服务器配置屏幕，您可以指定用户帐户和启动类型，或者干脆把它的默认配置，或者满意配置后，进行下一步：
   
   
   
   
8. 在数据库引擎配置界面，该配置将影响到以后使用数据库管理工具时的登录方式。身份验证模式，默认勾选项是“Windows身份验证模式”，即只需通过Windows管理员账户验证即可登录，一般会勾选第二项，“混合模式（SQL Server 身份验证和 Windows 身份验证）”，勾选后需设置系统管理员账户（用户名为sa）的密码；指定 SQL Server 管理员，点击“添加当前用户”，操作完成后点击下一步：
   
9. 安装完成后会提示用户需要重新启动计算机，看到该对话框后点击确定则出现安装完成的界面，此时 SQL Server 2014 就安装完毕了，关闭安装程序后请记得重启计算机。
   
10. 这样 SQL Server 2014就安装好了。

SQL Server配置

SQL Server远程配置

SSMS远程设置

1. 在“对象资源管理器”中，右键单击服务器，再单击“属性” 。
   
2. 在“SQL Server 属性 - <server_name>”对话框中，单击“连接”。允许远程连接到此服务器,并勾选
   

SQL Server 配置管理器设置

1. 在 “开始” 菜单中，依次指向 “所有程序” 、 “Microsoft SQL Server” 和 “配置工具” ，然后单击 “SQL Server 配置管理器” 。
   
2. 打开SQL Server配置管理器后，选择SQL Server网络配置下面的MSSQLSERVER，然后看右边里面的TCP/IP是否为已启用。
   

防火墙设置

1. 在上面两步操作完成后，用户需要做的是对SQL服务器防火墙进行重新配置。在进行这一步操作时，首先找到SQL服务器上那个端口支持TCP/IP协议。用户可以在SQL服务器防火墙已经处于运行状态下，右击“TCP/IP协议”选择“属性”:
   

   从上图中我们可以看出，这台SQL服务器上支持TCP/IP协议的是1433端口。下一步要做的是在防火墙的配置中允许1433端口支持TCP/IP协议即可。

2. 打开控制面板选择 Windows防火墙，然后点击高级选项：
   
3. 选择“高级设置”后，打开后会发现在左边菜单栏中有入站规则（Inboud Rules）选项。将该选项打开，并在右边菜单栏中选择新建规则（New Rule）选项：
   
4. 打开“新建规则”选项后，利用“新内置绑定规则向导”为1433端口配置“内部绑定协议”配置为适用于TCP/IP协议即可。（前提是，需要完成该步骤以前所述的所有步骤），根据下面的几幅图为1433端口配置适用于1433端口即可：

   
   最后点击“完成”即可。测试方法：在局域网内找一台电脑，打开SQLServer数据库，然后输入你的Ip和密码，如果能连接上，就说明已经配置好了，如下图所示：
   

SQL Server 实例设置为自动启动

1. 在 “开始” 菜单中，依次指向 “所有程序” 、 “Microsoft SQL Server” 和 “配置工具” ，然后单击 “SQL Server 配置管理器” 。
2. 在 “SQL Server 配置管理器” 中，展开 “服务” ，再单击 SQL Server。
3. 在详细信息窗格中，右键单击要自动启动的实例的名称，然后单击“属性”。
4. 在“SQL Server <instancename> 属性”对话框中，将“启动模式”设置为“自动”。
5. 单击“确定”，然后关闭 SQL Server 配置管理器。

SQL Server管理套件(SSMS)

SQL Server管理套件是什么？

• SQL Server 的管理套件（SSMS）是 SQL Server 的主管理控制台；
• SSMS 中您可以创建数据库对象（如数据库，表，存储过程，视图等），在数据库中查看该数据，配置用户帐户，执行备份，复制，进行数据库之间的数据传输，等等；
• SQL Server 管理套件的图形用户界面，做的最多的事情是 “点击”；
• SSMS 使您能够运行 SQL 脚本接口，因此也有需要编程/脚本任务。许多任务可以通过 GUI 或 SQL 脚本来执行，所以你自己的选择使用哪一个。例如，您可以创建一个使用 GUI 或通过运行SQL 脚本的数据库。虽然如此，但你仍然需要 GUI 运行脚本。

SSMS是什么样子的？

当你打开 SQL Server 管理套件后会显示一个登录界面，如下图所示，然后就连接到 SQL Server。

您可以不对默认身份验证进行设置或者有需要的话也可以更改它们：

下面是 SQL Server 管理套件的样子，一旦你连接（并打开新的查询）：

SQL Server库操作

创建数据库

SQL Server创建数据库的方法有两种：一种是通过运行 SQL 脚本；另一种是直接使用 SQL Server 管理套件即可创建数据库，在本节中我们使用的是后一种方法。

SQL Sever 系统数据库

安装好SQL Server 的时候，会自动创建下面的四个数据库。

• master

   master 数据库记录了所有的 SQL Server 数据库系统的系统级信息，如用户帐户，配置设置，并存在所有其他数据库信息。

• model

  model 数据库是一个模板数据库。每当创建一个新的数据库（包括系统数据库的TempDB），会创建一个以 Model 数据库为副本数据库，并更改成你创建数据库时所用的名称。

• msdb

  msdb 数据库是 SQL Server 代理的数据库，用于配置警报使用 SQL Server 代理和预定作业等。

• tempdb

  tempddb 数据库是由 SQL Server 用于暂时存储数据的，这其中包含所有临时表，临时存储过程，并通过 SQL Server 生成任何其他临时存储需求。

这些系统数据库有它们特有的用处，系统数据库是我们新建数据库的模板。

开始创建新的数据库

1. 在对象资源管理器中，右键单击数据库文件夹/图标，然后选择 新建数据库...
   
2. 进行数据库命名，此处叫 “测试数据库”，然后点击 “OK”：
   
3. 此时一个新的数据库就已经创建了，新数据库会出现在对象资源管理器中的数据库部分。
   

新的数据库是基于模型数据库，该模型数据库是被用作每当创建一个新的数据库模板的系统数据库。

在左侧窗格中浏览数据库，并展开左侧列表，会看到数据库已经包含了许多对象，例如，它已经包含的系统功能，系统视图，存储系统过程，和（隐藏）系统表。这些都是提供有关数据库的信息系统对象。

创建表

我们依旧选择使用 SQL Server 管理套件(SSMS) 在 SQL Server 2014 数据库中创建一个表。

1. 确保有正确的数据库扩展(在我们的例子中，数据库是“测试数据库”)，右键单击表图标并选择 新建-》表.....从上下文菜单：
   
2. 一个新表将在设计视图中打开。当你打开了这样的界面，请执行以下操作：
   1. 在截图中的值，完成细节的列名列数据类型列，允许空列。
   2. 通过右键单击旁边的 ID 按钮(即其中关键出现在下面的截图同一地区)，并选择设置主键 ID 列主键字段。
   3. 通过将设置标识规范设置为是 (你可以找到在身份规格部分在底部窗格此选项)使 ID 列标识列。需要注意的是在底部窗格中设置的值，需要首先选择在顶部窗格中的列名。我们设置这个专栏是一个自动编号列 - 它会自动生成创建的每个记录一个新数值。

   
3. 保存该表通过选择 文件> 保存表 或者通过表的选项卡，然后从上下文菜单中选择保存 Table_1上单击鼠标右键：
   
4. 当系统提示时，命名表(我们称之为 人员信息表)
   
5. 新建的表会出现在数据库表部分，如果它不能显示出来，请尝试刷新对象资源管理器。
   

此时一个数据表就已经建立好了。

添加数据

数据库添加数据的方法大致分为以下几种：

• 手动输入: 直接将数据输入到数据库即可。
• 使用复制/粘贴功能: 类似于以前的选项，但是这一次是从其他来源的数据复制，然后粘贴到你的数据库中的表。
• 直接导入: 可以使用导入和导出向导从另一个源导入数据。
• 使用SQL脚本: 可以运行一个包含所有数据插入的SQL脚本。
• 应用/网站: 使用者通过下列方式：应用程序或网站更新数据库。

手动输入

在数据库中选择“编辑前200行”选项，然后就可以手动直接输入数据到表行中了。

手动输入的数据是确定的，而且只能一点点输入，遇到大量的数据的时候，操作会变得很繁重，而且它不满足大多数业务的需求，而且不利于更新。

1. 在对象资源管理器中，右键点击你要打开的表，然后选择 “编辑前200行”
   
2. 然后就可以将数据直接输入到表中了。

需要注意的是在使用我们刚刚创建的表，我们并不需要输入数据到 ID 列。 这是因为他们将被自动填充（请记住，我们设置 ID 是唯一标识。

使用复制/黏贴功能

通过从另一数据源复制并粘贴到数据库表的方法也能添加数据库中的数据。

这将需要在源表具有相同的列的目标表。

类似于上面的手动方法中，这适合一个小数量的记录。

1. 从数据源得到所需的所有记录；
2. 在目标数据库（例如：一个要填充数据），目标表上单击鼠标右键，然后选择编辑前200行；
3. 在最左边的列（它更是一个按钮，最左边的列的左侧，可以选择整个行），然后从上下文菜单中选择 “Past（粘贴）”，选择通过右键单击一个空行：
   

直接导入

当然也可以从另一个数据源导入数据。

导入数据与复制/粘贴的方法相似，但是它更加灵活，可以更合适于许多场合。

例如，您可以选择从多个视图和表中的数据，可以写一个查询就可以导入需要的数据。

要导入数据，在数据库上单击鼠标右键，并选择 人员信息表> 任务> 导入数据... 并从那里按照向导执行操作。

在 SQL Server 中导入和导出向导数据，可以将数据复制到并从其中一个托管的 .NET Framework 数据提供程序或本机 OLE DB 提供程序可用的任何数据源。

这些包括：

• SQL Server
• 普通文件
• Microsoft Office Access
• Microsoft Office Excel

使用脚本

很多时候，使用包含需要插入数据的 SQL 脚本会更加有效。

您可以使用 SQL INSERT 语句插入指定数据。

SQL 脚本是静态插入/引用数据，它们可以存储并重新运行需要它的任何时间（例如在另一数据库）。脚本通常不进行数据的不断变化（如客户详细信息）那么好。你可能不会被保留过时的数据在一个副本的SQL脚本。但总有例外。例如，你可以使用这样一个脚本来填充客户表，用于测试/开发环境。

应用/网站

大多数的 SQL Server 数据库后端数据存储为一个前端应用程序。该应用程序的用户负责将数据添加到数据库（以编辑）。 因此，许多表中您的数据库将通过应用程序进行更新。在这种情况下，应用程序被更新是使用 SQL 脚本到数据库。

这些脚本和那些我们上面讨论的区别在于，在应用程序正在使用的脚本将是动态的。他们将接受数据作为传递给脚本的参数。因此，用户可以输入，电子邮件地址到应用程序中，但他并不知情在应用程序运行的 SQL 脚本，需要他的电子邮件地址，验证它，把它添加到脚本，如果通过了所有的业务/验证规则，最后将其插入到数据库中。

这些 SQL 脚本可以直接放置到您的网站的代码（ASP.NET，JAVA,PHP的等），也可以将它们存储在数据库中的存储过程或视图。

SQL语句

SQL 脚本通常由一个或多个“语句”得来的，每个语句告诉 SQL Server 该怎么做。

SQL 脚本可以包含许多语句。例如，SQL 脚本可以包含一个语句创建一个表，另一份声明将数据插入到该表，而另一份语句中，以选择所有刚刚插入到表中的数据。事实上，SQL 有用于确切操作的具体语句：CREATE语句，INSERT语句和SELECT语句。

插入语句

insert into tableName(field1,field2,...) values(值1,值2,...)

更新语句

update tableName set field1=值1, field2=值2 where id=查询值

删除语句

delete from tableName where id=2

查询语句

select * from tableName

SQL 语句示例

SQL Server查询设计器

查询分析器是一个图形化的数据库编程接口，是SQL server 客户端的重要组成部分。在构建复杂的查询，涉及到许多表，视图等的时候，查询分析器特别有用。

1. 在工具栏上单击新建查询(New Query)打开一个新的查询
   
2. 通过选择打开查询设计器 Query > Design Query in Editor... 从顶部菜单：
   
3. 选择要运行的查询(本例中,我们添加两个表),点击添加。单击关闭对话框。
   
4. 选择列，想通过检查旁边的列名的复选框，在查询中显示。在中间窗格中，你可以取消一个输出复选框以隐藏该字段被显示在查询运行（但字段仍然会参与查询）。添加值下的过滤器来缩小结果到只有那些感兴趣的（在这个例子中，它是创建一个 WHERE 子句只选择那些记录了 状态ID“1” - 也就是 “正常”）
   
5. 当满意查询后点击OK。
6. 该查询将出现在查询窗口，在这里可以运行它，运行任何其他查询相同的方式（点击！执行或按F5）当点击OK，会发现工作空间中已经添加了查询。然后，就可以像其他查询一样运行它：
   

SQL Server视图

• 从数据库系统内部来看，一个视图是由 SELECT 语句组成的查询定义的虚拟表（因为一个视图可以拉动多个表，并汇总数据在一起并将其显示，就好像它是一个单一的表）
• 视图是由一张或多张表中的数据组成的，当你运行视图，会看到它的结果，就像打开一个表时一样。
• 从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。

SQL Server视图的优点

视图可以执行以下操作：

• 限制访问特定的表中的行
• 限制访问特定的表中的列
• 从多个表中加入列，并呈现出来，好像他们是一个单一的表的一部分
• 呈现汇总的信息(如COUNT函数的结果)

SQL Server视图语法

通过使用 CREATE VIEW 语句创建一个视图，其次是 SELECT 语句，如下：

create view ViewName as
select ...

SQL Server创建视图

我们以前使用的查询设计器创建两个表中选择数据的查询。

现在让我们将查询保存为一个名为 “人员信息视图” 的视图。

我们需要做的就是把 CREATE VIEW 人员信息视图 的 AS 查询，如下：

CREATE VIEW 人员信息视图 AS
SELECT p.ID,p.姓名,p.性别,s.状态,p.创建时间 
FROM dbo.人员信息表 p INNER JOIN dbo.状态表 s 
ON p.状态ID=s.ID

运行该脚本，在左窗格中刷新浏览文件夹，你会看到视图就在左窗格中：

SQL Server运行视图

创建视图后，就可以简单地查看结果，就像你会选择任何表。

可以简单地键入 select * from 人员信息视图，它会运行完整的查询，而不是输入出大量的 SELECT 语句的 INNER JOIN ：

SQL Server存储过程

SQL Server 中视图通过简单的 SELECT 查询来解决复杂的查询，但是视图不能提供业务逻辑功能，而存储过程可以办到这点。

存储过程 Procedure 是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的 SQL 语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、网络负担。

存储过程的优点

• 存储过程允许标准组件式编程 存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。
• 存储过程能够实现较快的执行速度 如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。
• 存储过程减轻网络流量 对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。
• 存储过程可被作为一种安全机制来充分利用
• 系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

存储过程的创建

CREATE PROCEDURE myStoredProcedure AS
...

OR

CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS
...

用户自定义存储过程

1. 创建不带参数的存储过程
   
   

   -- 创建存储过程
   IF (EXISTS (SELECT * FROM sys.objects WHERE name = '人员信息存储过程'))
       DROP PROC 人员信息存储过程;
   GO
   CREATE PROC 人员信息存储过程
   AS
   SELECT *
   FROM dbo.人员信息表;
   -- 调用、执行存储过程
   EXEC dbo.人员信息存储过程;

2. 创建带参数的存储过程
   

   -- 创建带参数的存储过程
   IF(OBJECT_ID('proc_find_person','P') IS NOT NULL)
   DROP PROC proc_find_person
   GO
   CREATE PROC proc_find_person(
           @id INT
   )
   AS 
   SELECT * FROM dbo.人员信息表 WHERE ID=@id
   GO
   
   -- 调用、执行存储过程
   EXEC dbo.proc_find_person @id=3
   -- EXEC dbo.proc_find_person 2

3. 带通配符参数的存储过程
   

   -- 创建带通配符参数存储过程
   IF(OBJECT_ID('proc_find_person_byName','P') IS NOT NULL)
   DROP PROC proc_find_person_byName
   GO
   CREATE PROC proc_find_person_byName(
           @name NVARCHAR(20)='%j%'
   )
   AS 
   SELECT * FROM dbo.人员信息表 WHERE 姓名 LIKE @name
   GO
   
   -- 调用执行存储过程
   EXEC dbo.proc_find_person_byName @name='%张%'

4. 带输出参数的存储过程
   

   -- 创建带输出参数的存储过程
   IF(OBJECT_ID('proc_out_person','P') IS NOT NULL)
   DROP PROC proc_out_person
   GO
   CREATE PROC proc_out_person(
           @id INT, --默认输入参数
           @name NVARCHAR(20) OUT --输出参数
   )
   AS 
   SELECT @name=姓名 FROM dbo.人员信息表 WHERE id=@id
   GO
   
   -- 调用执行存储过程
   DECLARE @name NVARCHAR(20);
   EXEC proc_out_person 1,@name OUT;
   SELECT 姓名=@name;

SQL Server服务器角色

我们登录了一个 SQL Server 数据库后，必须分配登录的一个或多个服务器角色的选项。

注意：

• 服务器角色(不要与数据库角色混淆)可用来管理服务器上的权限。
• 不是每个人都应该分配给服务器的角色，只有高级用户，如数据库管理员应指定一个服务器角色。

查看服务器角色

要查看在 SQL Server 2014 管理 Studio 服务器角色，在对象资源管理器，展开安全文件夹：

SQL Server 2014 提供了九个固定服务器角色，被授予固定服务器角色的权限，不能更改。

九个固定服务器角色的说明

一些这些角色允许执行非常特定的任务。如果只有一个小团队，很可能只使用其中的一个角色（包括系统管理员）的两个。如果有一个大的团队，那么可以使用它们。

在 SQL Server 2014（和 SQL Server 2012），可以创建用户定义的服务器角色，并添加服务器级别的权限用户定义的服务器角色。

SQL Server日常维护

数据库分离

分离数据库是指将数据库从 SQL Server 实例中移除，但会完整保留数据库及其数据文件和事务日志文件。 然后可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。

1. 在数据库上-》右键-》任务-》分离
   
2. 选择"删除"和"更新"，然后点"确定"
   

数据库附加

可以附加复制的或分离的 SQL Server 数据库。 将包含全文目录文件的 SQL Server 2005 (9.x) 数据库附加到 SQL Server 服务器实例时，与在 SQL Server 2005 (9.x) 中一样，目录文件与其他数据库文件一起从旧位置附加。

附加数据库时，所有数据文件（MDF 文件和 NDF 文件）都必须可用。 如果任何数据文件的路径不同于首次创建数据库或上次附加数据库时的路径，则必须指定文件的当前路径。

1. 在 SQL Server Management Studio 对象资源管理器中，连接到一个 SQL Server 数据库引擎实例，然后在 SSMS 中选择展开该实例视图。
2. 右键单击“数据库”，然后选择“附加”。
   
3. 在“附加数据库”对话框中，若要指定要附加的数据库，请选择“添加”。 在“定位数据库文件”对话框中，选择数据库所在的位置，然后展开目录树来查找数据库的 .mdf 文件并将其选中，
   

数据库备份

1. 启动 SQL Server Management Studio (SSMS) 并连接到 SQL Server 实例。
2. 在对象资源管理器中，展开“数据库”节点。
3. 右键单击数据库，将鼠标悬停在“任务”上，然后选择“备份...”。
   
4. 在“目标”下，确认备份路径正确。 如需更改路径，请选择“删除”以删除现有路径，然后选择“添加”来键入新路径。 可通过省略号导航到特定文件。选择“确定”以备份数据库。
   
5. 数据库备份完成。
   

数据库还原

1. 启动 SQL Server Management Studio (SSMS) 并连接到 SQL Server 实例。
2. 在对象资源管理器中右键单击“数据库”节点，然后选择“还原数据库...”。
   
3. 选择“设备:”，然后选择省略号 (...) 来查找备份文件。
4. 选择“添加”，然后导航到 .bak 文件所在的位置。 选择 .bak 文件，然后选择“确定”。
5. 选择“确定”，关闭“选择备份设备”对话框。
6. 选择“确定”以还原数据库备份。
   

收缩数据库

1. 在 “对象资源管理器” 中，连接到 SQL Server 数据库引擎的实例，然后展开该实例。
2. 展开“数据库”，再右键单击要收缩的数据库。
3. 指向“任务”，指向“收缩”，然后单击“数据库”。