SQL SERVER学习笔记（二）数据库管理

SQL SERVER学习笔记(二)数据库管理

第二部分：数据库管理

单词记忆：transact：处理

create：创建

execute：执行、完成

一、 SQL Server的特性

1、 安装简便：为了便于安装、使用和管理，SQL Server2000提供了一组管理和开发工具。

2、 伸缩性和有效性

3、 支持客户/服务器模式（C/S）

4、 多协议兼容性

5、 数据仓库

6、 遵从ANSI/ISO SQL-92标准：Micrsoft 还对其进行了语言扩展并命名为：“Transact-SQL”

7、 支持数据复制

8、 全文检索

9、 联机丛书

二、T-SQL简介

Transact-SQL（或称为T-SQL）是Micosoft的一个程序扩展集合。T-SQL为SQL增加了些功能,包括事务控制、异常错误处理和行处理。

Transact- SQL允许用户在Transact- SQL对象中声明和使用局部变量和常量，并且有数据类型。

三、SQL Server存储体系构成

? 数据文件

后缀为.MDF 文件

–主数据文件，一个数据库只有一个 MDF文件，存放数据库中各种数据实体的定义（数据字典），也可以存放其他数据，但要注意控制其大小。

? 数据文件

后缀为.NDF文件

–扩展数据文件，一个数据库可以有多个NDF文件，存放各种数据

1. 数据规划(组）

2. 数据扩展

3. 文件仿真阵列

? 日志文件

后缀为.LDF文件

–日志文件，一个数据库可以有多个LDF文件，存放联机重做日志数据

? 注意：如果日志文件已经填满，SQL Server将自动停止运行并等待人工干预，所以要经

常监控日志的情况保证系统的稳定运行。

? 文件组

FileGroup

–将MDF和NDF文件在逻辑上进行分组，进行表规划时候，只需要指定表所需存放的组，就可以实现数据分离。

注意：系统默认的组为“PRIMARY”，不指定数据文件所隶属的组，则数据文件自动划分到“PRIMARY”组中；创建的表不指定所在的组，系统也自动分配该表到“PRIMARY”组中。

四、数据库的创建

示例：

Create Database 数据库名

On

(Name=‘逻辑名‘，

FileName=‘物理文件完全路径(*.mdf)’,

Size=n,

MaxSize=n,

FileGrowth=n)

FileGroup 组名字

(??.)

Log On

(Name=‘逻辑名‘，

FileName=‘物理文件完全路径(*.ldf)’,

Size=n,

MaxSize=n,

FileGroup=n)

五、创建过程中需要注意的问题：

? 只能为一个数据库创建一个MDF文件；

? 如果需要进行文件分组，只需要在文件前加上FILEGROUP组名就可以了，但PRIMARY组中至

少必须包含一个文件；

? 如果Size和Max Size不指定单位，系统默认用“M”为单位；

? 如果不指定数据库文件的大小，系统默认为“1M”；

? 如果创建时没有使用Log On字句，系统自动创建一个LDF文件，大小为MDF文件的1/4；

? 文件的增长率FILEGROWTH应该根据实际需要设置大些，以保证系统不会频繁扩容。

六、数据库性能选项配置

? 选项设置

Exec Sp_Dboption ‘数据库名’,‘选项‘，’值’

如：Exec Sp_Dboption ‘Pubs’,’AutoClose’,’False’

? 查询所有可用选项

Exec Sp_Dboption

? 查询数据库已经开启的选项

Exec Sp_Dboption ‘数据库名’

如：Exec Sp_Dboption ‘Pubs’

七、数据库的调整

1、设置数据库只读取

Exec Sp_dboption ‘Pubs’,’Read_Only’,’True’

2、设置数据库离线

Exec Sp_dboption ‘Pubs’,’Offline’,’True’

3、数据库更名（Sp_RenameDb)

Exec Sp_dboption ‘Pubs’,’Single User’,’True’

Exec Sp_RenameDb ‘Pubs’,’Pubs_1’

Exec Sp_dboption ‘Pubs_1’,’Single User’,’False’

八、数据库结构调整

1、调整数据库结构

Alter Database 数据库名

? Add File (文件参数) To FileGroup 文件组

? Add Log File (文件参数）

? Remove File ‘逻辑数据库文件名字’

? Remove FileGroup ‘组名’

? Add FileGroup ‘组名’

? Modify File (文件参数)

注意：

1、ADD FILE时如果不指定“TO FileGroup 文件组”,系统自动将该文件归到“Primary”组中；

2、REMOVE FILEGROUP时，如果组中有数据，将无法删除。

九、数据规划的实现

表的数据规划的实现

Create Table Student

(Stu_Code Varchar(10) Primary Key

On G_Index,

Stu_Name Varchar(10) Not Null,

Stu_Sex Varchar(1) Not Null,

Stu_Pic Image)

On G_Normal

TextImage_On G_Big

规化重点：

1、将大数据（G_Big)和常规数据(G_Normal)分离;

2、将索引数据（G_Index）和表数据分离；

第二部分习题样例

一、创建本班数据库（T607），根据规划原则将四大类数据分组，每组一个数据文件5－100M，按30％增长，日志文件2个，5－100M，按50％增长。 create database T607 on primary (

name = 'T607_mdat',

filename = 'd:\ size = 5,

maxsize = 100, filegrowth = 30% ),

filegroup G_Normal (

name = 'T607_n1dat',

filename = 'd:\ size = 5, maxsize = 100, filegrowth = 30% ),

filegroup G_Change (

name = 'T607_n2dat',

filename = 'd:\ size = 5, maxsize = 100, filegrowth = 30%

),

filegroup G_Index (

name = 'T607_n3dat',

filename = 'd:\ size = 5, maxsize = 100, filegrowth = 30% ),

filegroup G_Big (

name = 'T607_n4dat',

filename = 'd:\ size = 5, maxsize = 100, filegrowth = 30% ) log on

(

name = 'T607_log1',

filename = 'd:\ size = 5, maxsize = 100, filegrowth = 50% ), (

name = 'T607_log2',

filename = 'd:\ size = 5, maxsize = 100, filegrowth = 50% )

二、数据库状态属性修改练习

1、 将创建好的数据库设置成自动“文件自动关闭”和自动“文件自动收缩”方式 文件自动关闭：exec Sp_dboption ' T607',' autoclose ',' true '

文件自动收缩：exec Sp_dboption ' T607',' autoshrink ',' true '

2、 将创建好的数据库设置成“只读方式”

exec Sp_dboption ' T607',' read only ',' true '

3、 将创建好的数据库设置成“离线状态”后恢复 exec sp_dboption ' T607',' offline ',' true '

4、 将创建好的数据库名称改名为“T607-2006”

exec sp_dboption ' T607',' single user ',' true ' exec sp_renameDb ' T607',' T607_2006 '

exec sp_dboption ' T607-2006 ',' single user ',' false ' 注意事项：首先必须把只读属性取消

三、修改数据库文件属性

将数据库中的一个数据文件属性修改为10—无限大，增长比率为40％； alter database [T607_2006]

modify file (

name = T607_n1dat, size = 10,

maxsize = unlimited, filegrowth = 40% )

注意事项：修改时，请勿指定物理名称。

四、收缩数据库文件

将数据库中的一个文件收缩到3M； dbcc shrinkfile(‘ T607_n2dat ‘, 3) 注意事项：必须先use T607_2006才能收缩

五、组的创建和维护

在数据库中创建一个新的组“G_TEST” alter database [T607_2006] add filegroup G_TEST

六、组中增加一个文件

在数据库中增加一个新的文件“T607_TEST.ndf”，并自动加入到“G_TEST”组中； alter database [T607-2006] add file (

name = 'T607_TEST',

filename = 'd:\ )

to filegroup G_ TEST

七、向数据库中增加日志文件

在数据库中增加一个新的日志文件“T607_LOG_TEST”文件； alter database [T607-2006] add log file (

name = 'T607_LOG_TEST',

filename = 'd:\ T607_LOG_TEST.log'

)

八、数据库的规划方式

创建一个学生资料表，包含字段： 学生编码、 学生名称不允许重复 学生性别、 学生年龄 15—30、 学生生日、 学生地址、 学生国籍、 学生备注大量文本、 学生照片 JPG

根据表规划及优化原则，创建该表： create table 学生 (

学生编码 smallint,

学生名称 varchar(10) unique,

学生性别 nvarchar(1),

学生年龄 tinyint check(学生年龄>=15 and 学生年龄<=30), 学生生日 datetime, 学生地址 nvarchar(20), 学生国籍 varchar(10), 学生备注 text, 学生照片 image ) on group1

textimage_on group2

九、删除数据库

删除本练习中创建的数据库 drop database T607-2006

本文来源：https://www.bwwdw.com/article/rzz.html