SQL Server学习

1.SQL server的集成环境介绍

SQL server 提供强大的GUI界面，用户可以直接界面或者通过T-SQL语句操作数据库。

1.1 企业管理器

企业管理器是图形化管理界面的核心。在开始菜单选择“Microsoft SQL server tools 18 ->Microsoft SQL server managemement studio 18 ”，就可以打开企业管理器。

可以看到有许多选项，这个界面上可以利用图形工具创建数据库，表，视图和存储过程等数据库项目。

2.系统数据库

系统数据库作为 SQL server 默认安装的一部分而安装的，有系统个存储过程组成。系统数据库、表以及存储过程共同为您提供了设计、创建、部署和维护世界级 SQL server 数据库的工具和支持。

系统数据库存储着有关 SQL server 和所有用户数据库的配置和操作信息。SQL server 的系统数据库有 master、msdb、tempdb 和 model 数据库。可以用企业管理器查看这四个数据库及其内容。

在系统数据库中的数据表包含了 SQL Server 的整体信息以及每一个用户数据库的信息。可以像查询 SQL Server 中任何其他表一样查询系统表。

2.1 Master 系统数据库

Master 系统数据库是追踪所有其他数据库和存储配置信息的关键数据库。它控制着用户数据库和 SQL Server 的操作。这通过在 Master 数据库的系统表中存储数据来实现。

由于 Master 数据库所包含的数据对于 SQL Server 发挥其功能上非常重要的，因此不应该允许任何用户删除或直接修改该数据库。

2.2 Model 系统数据库

SQL Server 把 Model 数据库用作创建新数据库的模板，它包含了应该出现在每个用户数据库中系统表。在创建一个数据库的时候，SQL Server 会把 Model 数据库的内容复制到新的数据库。既然整个 Model 数据库的内容都要复制到每一个新数据库，就必须确保新的数据库的大小等于或大于 Model 数据库。

尽管可以修改，不推荐进行改动

2.3 msdb 系统数据库

msdb 数据库是 SQL Server Agent 的主要支持。SQL Server Agent 负责复制、任务调度、警报、系统操作员信息以及备份信息。SQL Server 在 msdb 数据库中自动地维护完整的联机备份和恢复历史记录。不管何种原因，只要历史记录无法写入 msdb 数据库， 就会有一条消息写入 Windows NT/2000 的事件日志和 SQL Server 错误日志。

在每条 Backup 语句执行之后，该备份的描述就会保存到 syshistory 表中，该备份细节会存储到 sysbackdetail 表，作为一个事务的一部分。备份细节包括谁执行了备份、备份的时间以及保存该备份的设备或文件的名称。

在每条 Restore 语句执行之后，该恢复的描述就会保存到 sysrestorehistory 表中， 设备的细节则保存到 sysrestoredetail 表中。

2.4 tempdb 系统数据库

tempdb 数据库为临时表其他临时工作存储需求提供一个过度的存储区域。SQL Server 仅维护单一tempdb 数据库而不管已经有多少个其他数据库。其最大的优势在于SQL Server 不把tempdb 的活动记入日志，从而改进了响应时间。这也带来一个缺点， 失去了日志记录，如果服务器失败，那么 tempdb 中所有信息都会丢失。没有日志记录项，要重建数据是极为困难的。

3 表变量和临时表

3.1 表变量

使用场景：作为中间表使用，可以提高 SQL 处理性能。

优点：表变量直接创建到内存，表变量会随着定义它的对象结束而结束。

创建和插入数据的方法

declare @tb table(
  id int,
  name varchar(50),
  age int)

insert @tb select 1,'nn',14

select * from @tb

3.2 临时表

使用场景：作为中间表使用，可以提高 SQL 处理性能。

优点：临时表会创建到 tempdb 数据库，使用的是磁盘空间，相当于操作系统中虚拟内存概念。

创建和插入数据的方法

create table #t(
  id int,
  name varchar(50),
  years int),
  nums int)

insert #t select 1,'nn',14,15
  union all select 1,'nn',14,15 

select * from #t

删除方法：

if object_id('tempdb..#t)
  is not null
  begin
    drop table #t
  end

4.SQL server数据库文件

SQL server 数据库文件是一些对象的集合，这些对象包括表、视图、存储过程和约束等。数据库存储在两个或多个操作系统文件中，这些文件称为数据文件。这些文件被分成文件组用于分配和管理。实际的数据存储在表中。

在 SQL server中大多数数据库操作都是以以下两种方式进行的：使用SQL 企业管理器图形化进行

使用 T-SQL 语句非图形化进行

对数据库进行的任何修改都会记录到事务日志中。事务日志在数据库恢复和备份操作中发挥着重要的作用。

SQL server 数据库存储在文件中。这些文件是在穿件数据库时自动创建的，有三种类型的数据库文件，而每个数据库至少使用两种类型。

数据库文件分为三种类型：主要数据库文件、次要数据库文件、日志文件

4.1 主要数据库文件

每个数据库必须有且 仅有一个 主要数据文件。主要数据文件的扩展名为 .mdf。

4.2 次要数据库文件

次要数据库文件是可选的，可以包含所有不在主要数据文件中的数据和对象。数据库可以有多个次要数据文件。次要数据文件的扩展名为 .ndf。

4.3 日志文件

日志文件包含所有用于恢复数据库的事务日志信息。每个数据库至少有一个日志文件。日志文件的扩展名为 .ldf。

注意

数据库文件通常指的是三种文件类型中的任何一种。数据文件一词则指的是主要数据文件和次要数据文件。日志文件一词则是作为数据库事务日志的一部分。

5.创建数据库

可以使用 create database 命令来创建新的数据库和相关文件。用的关键字时 create database

5.1 语法格式

基本语法：

create database database_name[on 
  [<filespec>[,...n]]
  [,<filegroup>[,...n]]]
[log on {<filespec>[,...n]}]
[collate collation_name]
[for load | for attach]

其中：
1.文件信息详细描述：

< filespec > ::= [ PRIMARY ]
( [ NAME = logical_file_name , ] FILENAME = 'os_file_name' [ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ] 

2.文件组信息详细描述：

< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]

3. 参数描述和主要事项：

• name 和 filename 指定的文件名可以不一样；
• filegowth 默认值为 10%，最小为 64k，无指定为 1MB；
• size 默认单位为MB；
• for attach 是指指定从现有的一组操作系统文件组附加数据库。必须有指定第一个主文件的 条目，附加的数据库必须使用与 SQL server 相同的代码页和次序创建，不要在创建数据库时候使用，而使用 sq_attach_db 系统存储过程
• 日志文件和物理文件无直接的逻辑对应关系。

6.2 创建数据库

一个数据文件、一个日志文件

USE master 
GO

CREATE DATABASE MySales ON
  ( NAME ='Sales_dat',
  FILENAME = 'd:\培训库\Mysaledat.mdf', SIZE = 10,
  MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON
  ( NAME = 'Sales_log',
  FILENAME = 'd:\培训库\Mysalelog.ldf', SIZE = 5MB,

多个数据文件、多个日志文件

USE master 
GO
CREATE DATABASE Archive ON
PRIMARY
  ( NAME = Arch1,FILENAME = 'd:\培训库\archdat1.mdf', 
  SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),
  ( NAME = Arch2,FILENAME = 'd:\培训库\archdat2.ndf',
  SIZE = 100MB, MAXSIZE = 200,FILEGROWTH = 20),
  ( NAME = Arch3,FILENAME = 'd:\培训库\archdat3.ndf',
  SIZE = 100MB, MAXSIZE = 200,FILEGROWTH = 20) 
LOG ON
  ( NAME = Archlog1,FILENAME = 'd:\培训库\archlog1.ldf', SIZE = 100MB,
  MAXSIZE = 200,FILEGROWTH = 20),
  ( NAME = Archlog2,FILENAME = 'd:\培训库\archlog2.ldf', SIZE = 100MB,
  MAXSIZE = 200,FILEGROWTH = 20) 
GO

5.3 删除数据库

删除数据库的关键词是： drop database。语法是：

drop database test1

注意：不要轻易删除数据库，将导致所有数据的完全丢失，最好使用分离数据库，然后保留一段时间，最终确认是无用库再删除数据库文件就可以了。