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

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

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

一,SQL Server 2005概述

1,SQL Server 2005 新特性

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

1)Service Broker

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

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

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

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

2,SQL Server 2005 体系结构
SQL Server体系结构

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~zA~Z、汉字或者下画线(_)、符号@#
    • 后续字符可以是字母a~zA~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创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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,查看数据库状态

1
2
3
4
use master;
go

select name,state,state_desc from sys.databases;

2,修改数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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,删除数据库

1
drop database xsxk;

4,分离和附加数据库

1
2
3
4
5
6
7
8
9
10
11
12
-- 分离数据库
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)重命名数据库

1
alter database xsxk modify name = xsxks

4)收缩数据库

1
2
3
4
5
-- 将userDB用户数据库中的文件减小,以使userDB中的文件有10%的可用空间
dbcc sharinkdatabases(userDB, 10)

-- 将userDB用户数据库中名为DataFile的文件大小收缩到7M
DBCC shrinkfile(DataFile, 7)
坚持原创技术分享,您的支持将鼓励我继续创作!