SQL之数据库的创建与管理

SQL之数据库的创建与管理

认识数据库

数据库的基本概念

数据库是按照数据结构来组织、存储和管理数据的仓库，是存储在一起的相关数据的集合。其优点主要体现在一下几个方面：

• 减少数据的冗余度，节省数据的存储空间
• 具有较高的树独立性和易扩充性
• 实现数据资源的充分共享

数据库的相关概念

1. 数据库系统：数据库系统是由数据库、数据库管理系统、数据库管理员、硬件和软件5部分组成。

2. 数据库管理系统：数据库的一个重要组成部分，位于用户与操作之间的一层管理软件，负责数据库中的数据组织、数据操作、数据维护和数据服务等。

3. 关系数据库：支持关系模型的数据库。

4. 数据库组成：本文组要以SQL Server 2012 数据库介绍，主要有文件和文件组组成。

   1. 文件：
      1. 主要数据文件：存放数据和数据库的初始化信息。每个数据库只有一个主要数据文件，默认扩展名为.mdf
      2. 次要文件：存放除主要数据文件以外的所有数据文件。有些数据库可能没有次要文件，也可能有多个次要文件，默认扩展名是.ndf
      3. 事务日志文件：存放用于恢复数据库所有日志信息。每个数据库至少有一个事务日志文件，也可以有多个，默认扩产名是.ldf
   2. 文件组：
      1. 主文件组：包含只要数据文件和任何没有明确指派给其他文件组的文件。系统表的所有页都分配在主要文件组中。
      2. 用户定义文件组：主要是在create database或alter database语句中，使用filegrowth关键字指定的文件
   3. 文件、文件组的设计规则：
      1. 文件只能是一个文件组发成员
      2. 文件或文件组不能由一个以上的数据库使用
      3. 数据和事物日志信息不能属于同一个文件或文件组
      4. 日志文件不能作为文件组的一部分。日志空间与数据空间分开管理

5. 系统数据库：SQL Server 2012的安装程序在安装时默认将建立4个系统数据库（master、tempdb、model、msdb)

   1. master数据库：是SQL Server2012中最重要的数据库，记录SQL Server实例的所有系统级信息，包括实例范围的元数据、端点、连接服务器和系统配置设置
   2. tempdb数据库：是一个临时数据库，用于保存临时对象或中间结果集
   3. model数据库：用作SQL Server实例上创建的所有数据库模板，对model数据库进行修改将应用于以后创建的所有数据库
   4. msdb数据库：用于SQL Server代理计划警报和作业

SQL Server的命名规则

标识符

标识格式

1. 标识符首字符必须是下列字符之一
   1. 统一码
   2. 下划线“_”、符号“@”或则数字符号“
      #”
2. 标识符后续字符可以是一下3种：
   1. 统一码
   2. 来自拉丁字母或其他国家脚本的十进制数字
   3. “@”符号、美元符号“$”、数字符号“#”或下划线“_”
3. 标识符不允许是Transact-SQL的保留字
4. 不允许嵌入空格过其他特殊字符

标识符分类

• 常规标识符：符合标识符的格式规则
• 分割标识符：包含在双引号（“”）或则括号（[]）内的标识符。该标识符可以不符合标识符的格式规则，如[MRGZGLXT]、MR和GZGLXT之间含有空格，但因为使用了方括号，所以视为分隔标识符

对象命名规则

在数据库中创建一个数据库对象后，数据库对象的完整名称应该由服务器名、数据库名、拥有者名和对象名4部分组成，其语法格式如下：

[[[server.][database].][ouner_name].]object_name

服务器、数据库和所有者的名称即所谓的对象名称的限定符。当引用一个对象时，不需要指定服务器、数据库和所有者，可以利用句号标出他们的位置，从而省略限定符。

对象名的有效格式如下：

server.database.owner_name.object_name
server.database..object_name
server..owner_name.object_name
server...object_name
database.owner_name.object_name
database..object_name
owner_name.object_name
object_name

指定了所有4部分的对象名称被称为完全合法名称

数据库的创建与管理

数据库的创建与管理采用SQL Server界面形式相对于简单，所以就不介绍界面方式的创建方法，主要介绍数据库定义语言（DDL）

创建数据库

使用CREATE DATABASE语句创建数据库。语法如下：

CREATE DATABASE database_name
on [primary]
(
name = STC,
filename = 'E:\STC.mdf',
size = 10MB,
maxsize = 5MB,
filegrowth = 100MB
)
log on
(
name = log_STC,
filename = 'E:\log_STC.ldf',
size = 2MB,
maxsize = 2MB,
filegrowth = 50MB    
)

注释如下：

on后面是主文件
log on 后面是事务日志文件
name 是文件名称，filename是文件路径
size 文件的初始化大小
maxsize文件的最大存储空间
filegrowth 文件的空间的增长速度

修改数据库

使用alter database 语句修改数据库

alter database database_name
{
add file<filespec>[to filegroup filegroup_name]
|add log file<filespec>
|reomve file logical_file_name
|add filegroup filegroup_name
|remove filegroup filegroup_name
|modify file<filespec>
|modify name=new_dbname
|modify filegroup filegroup_name
}

注释：

add file:指定要增加的数据库文件
to filegroup:指定要增加的文件添加到哪个文件组
add log file:指定要增加的事物日志文件
remove file:从数据库中删除指定文件的定义，并删除其物理文件。文件只有为空时才能呗删除
add filegroup:指定要增加的文件组
remove filegroup:从数据库中删除指定文件组的定义，并删除其物理问文件组。文件组只有为空时才能被删除
modify file:指定文件的文件名、容量大小、最大容量、文件增容方式属性，但一次只能修改一个文件的属性
modify filegroup:修改文件组的属性，其中属性filefroup_property的取值可以为readonly,表示文件组指定为只读。
set:设置数据库的属性
alter database:修改数据库的大小、缩小数据库、更改数据库名称

向数据库中添加文件

__将一个大小为10MB的数据文件student添加到STC数据库中，改数据文件大小为10MB，增长速度为2MB，最大的文件为100MB，STC数据库在E盘下：

use STC
alter database STC -- 更改数据库
add file --添加文件
(
name = student, -- 文件名称
filename = 'E:\student.ndf', -- 路径
size = 10MB, -- 大小
maxsize = 100MB, -- 最大值
filegrowth = 2MB -- 表示增量
)

更改数据库名称

将数据库名称”STC”更名为db_STC

使用存储过程中的sp_renamedb

exec sp_renamedb 'STC',
'db_STC' --数据库重命名

删除数据库

数据库删除后，相应的数据库文件及其数据都会被删除，并且不可恢复

删除数据库时必须满足一下条件：

• 如果数据库涉及日志传送操作，在删除数据库之前必须取消日志传送操作
• 若要删除为事物复制发布的数据库，或删除为合并复制发布或订阅的数据库，必须首先从数据库中删除备份。如果数据库已损坏，不能删除备份，可以先将数据库设置为脱机状态，然后再删除数据库
• 如果数据库上存在数据库快照，必须首先删除数据库快照

使用drop database语句删除数据库

drop database database_name[,....n] -- 如果有多个要删除的数据库要都好分开，其中database_name是要删除的数据库

__注意：删除正在使用的数据库系统将出现错误__例如：

use STC
drop database STC