【数据库实践】 数据库及其管理

本系列为《数据库系统原理与应用（刘先锋等著）》的读书笔记。

课本第07～08章主要知识点

一，SQL Server 2005概述

1，SQL Server 2005 新特性

SQL Server 是一个全面的，集成的数据库解决方案，为企业中的用户提供了用于企业数据管理和商业智能的一个安全，可靠和高效的平台。

1）Service Broker

• 提供了一个功能强大的，异步编程的新模型。数据库应用程序通常使用异步编程来缩短交互式响应时间，并增加应用程序总吞吐量。
• 帮助数据库开发人员生成可靠且可扩展的应用程序。
• 在SQL Server实例之间提供可靠的消息传递服务。

2）CLR集成
3）专用管理员DAC：SQL Server 2005为管理员提供了一种特殊的诊断连接，以供在无法与服务器建立标准连接时使用。
4）用户与架构分离

• 简化删除数据库用户的操作
• 多个用户可以用户同一个架构

5）分区：分区是大型数据表和索引采取的优化存储性能和数据存储的技术。

2，SQL Server 2005 体系结构

1）数据库引擎组件：为数据存储、处理和安全实现高可伸缩性和高可用性服务。
2）报表组件服务组件：提供了全面的报表解决方案，可创建、管理和发布传统的、可打印的报表和交互性的、基于Web的报表。
3）分析服务组件：提供数据分析服务。利用分析服务开发人员设计、创建和管理多种数据源和多种数据结构，通过数据分析规律，获取数据知识。
4）集成服务组件：用于构建高性能数据集成解决方案（包括为数据仓库提取、转换和加载（ETL）包）的平台。
5）其他组件：复制服务组件，通知服务组件，服务代理组件，全文搜索。

3，SQL Server 2005安装

1）SQL Server 2005版本选择

• SQL Server 2005企业版：支持32位和64位系统，适合超大型企业。包括联机事务处理，高度复杂的数据分析，数据仓库系统和网站所需的基本功能。
• SQL Server 2005标准版：支持32位和64位系统，适合中小型企业。包括电子商务，数据仓库和业务流解决方案所需的基本功能。
• SQL Server 2005工作组版：仅支持32位系统，适合小型企业使用。包括SQL Server产品系列的核心数据库功能，可升级至标准版或者企业版。
• SQL Server 2005开发版：支持32位和64位系统，仅适合于开发和测试系统使用。具备和企业版完全一样的功能，但有许可限制，只能用于开发和测试。
• SQL Server 2005简易版：仅支持32位系统，是一个免费的，使用简单，易于管理的数据库。
• SQL Server 2005精简版：仅支持32位系统，适合在移动智能设备上使用。

二，数据库的类型

1，系统数据库

数据库是表、视图、索引、存储过程等对象的集合，是数据库管理系统的核心，数据库与管理它的数据库管理系统统一组成数据库服务器。

1）master：记录了SQL Server系统的所有服务器的系统信息，包括实例范围的元数据，端点，链接服务器和系统配置设置。

2）model：用于在SQL Server实例上创建的所有数据库的模板。

3）msdb：提供给SQL Server代理服务器使用的数据库，主要用于为警报，作业，任务调度及记录操作员的操作提供相应的支持。

4）tempdb：连接到SQL Server实例的所有用户都可用的全局资源，保存所有临时表和临时存储过程。

5）resource：是一个隐藏的只读数据库，包含了SQL Server中的所有系统对象，但不包含用户数据或者用户元数据。

2，用户数据库

• 数据库命名规则如下：

• 第一个字符必须是字母a～z和A~Z、汉字或者下画线(_)、符号@，#；
• 后续字符可以是字母a～z和A~Z、汉字、数字或者下画线(_)、符号@，$、数字符号；
• 标识符不能是T-SQL的保留字；
• 长度不能超过128。

3，数据库快照

1）数据库快照的定义：数据库快照是用户数据库的只读、静态视图，不包括未提交的事务。

2）数据库快照的特点：

• 反映某个时刻（完成数据库快照创建的时刻）数据库的数据；
• 不允许更新；
• 一个用户数据库可以创建多个数据库快照，并且必须与数据库在同一个服务器实例上。

三，数据库存储文件

SQL Server 2005数据库文件名称包括：

• 逻辑文件名：在所有 T-SQL 语句中引用物理文件时所使用的名称，必须符合SQL Server 标识符规则，而且是唯一的。
• 物理文件名：包括目录路径的物理文件名称，必须符合操作系统文件命名规则。

SQL Server 2005数据库操作系统文件包括：

• 数据文件：包括数据和对象，如表、索引、存储过程和视图。
• 日志文件：包含恢复数据库中的所有事务所需的信息。

1，数据库文件

• 主要数据库文件（.mdf）：数据库的起点，包括数据库的启动信息，并指向数据库中其他文件。
• 次要数据库文件（.ndf）：是可选的，由用户定义并存储用户数据。
• 事务日志文件（.ldf）：用于恢复数据库的日志信息。

2，数据库文件组

• 主文件组：包含主要数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。
• 用户定义文件组：通过在 create database 或者 alter database 语句中使用 filegroup 关键字指定的任何文件组。
• 默认文件组（primary文件组）：如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。

注意：日志文件包括在文件组内。

四，数据库的对象

1，数据库关系图：以图形方式来表示表之间的关系。

2，表：组织和存储数据。

3，索引：提高数据检索速度，但增加了系统存储空间的开销。

4，视图：实现用户对数据对查询，但是视图的结构和数据是建立在对表的查询基础上的。

5，存储过程和触发器：数据库中的对编程对象。

• 存储过程独立于表，存储在服务器上，供客户端调用，提供应用程序的效率。
• 触发器是一种特殊的存储过程，可以大大增强应用程序的健壮性，数据的可恢复性和可管理性。

6，规则和约束：对能够放入表中的内容进行限定。

• 规则：用于在用户定义数据类型上加以限制。
• 约束：本身并非实际的对象，而只是描述特定表的元数据。

7，默认值：

• 默认值有两种类型：

• 默认值默认其本身是一个对象；
• 默认值不是实际的对象，只描述表特定列的元数据。

8，全文目录：全文目录是数据的映射，以加速对启用了全文搜索的列中特定文本块的搜索。

五，创建数据库

• 一个SQL Server实例，最多可以创建32767个数据库。

1，使用SQL Server管理工具创建

2，使用T-SQL创建

create database xsxk 
on primary                   -- 建立主要数据文件
(
    name = 'xsxk_data',         -- 逻辑名称
    filename = 'E:\students\xsxk_data.mdf',   -- 物理文件路径和名称
    size = 1024KB,         -- 初始大小
    maxsize = unlimited,   -- 最大尺寸为无限大
    filegrowth = 10%       -- 增长速度
)
log on
(
    name = 'xsxk_log',         -- 建立日志文件
    filename = 'E:\students\xsxk_log.ldf',   -- 物理文件路径和名称
    size = 1024KB,         -- 初始大小
    maxsize = 5120KB,  
    filegrowth = 1024KB
 )

六，维护数据库

1，查看数据库状态

use master;
go
​
select name,state,state_desc from sys.databases;

2，修改数据库

alter database xsxk 
modify file
(
    name = 'xsxk_data',         -- 逻辑名称
    size = 5MB,         -- 初始大小
    maxsize = 50MB,   
    filegrowth = 2%       -- 增长速度
)
go

alter database xsxk
log on
(
    name = 'xsxk_log',         -- 建立日志文件
    filename = 'E:\students\xsxk_log.ldf',   -- 物理文件路径和名称
    size = 1024KB,         -- 初始大小
    maxsize = 5120KB,  
    filegrowth = 2MB
 )
 go

3，删除数据库

drop database xsxk;

4，分离和附加数据库

-- 分离数据库
sp_detach_db xsxk;
​
-- 附加数据库
create database xsxk on
(
    filename = 'E:\students\xsxk_data.mdf'
),(
    filename = 'E:\students\xsxk_log.ldf'
)
    for attach;
go

5，其他操作

1）脱机用户数据库

2）联机用户数据库

3）重命名数据库

alter database xsxk modify name = xsxks

4）收缩数据库

-- 将userDB用户数据库中的文件减小，以使userDB中的文件有10%的可用空间
dbcc sharinkdatabases(userDB, 10)
​
-- 将userDB用户数据库中名为DataFile的文件大小收缩到7M
DBCC shrinkfile(DataFile, 7)