第二章 数据库的实现
2.1 T-SQL语句回顾(略-详见SQL SERVER应用开发)
2.2 使用SQL语句创建和删除数据库(掌握)
一、创建数据库
(一)格式:
CREATE DATABASE 数据库名
ON [PRIMARY]
(
[NAME = 逻辑文件名,]
FILENAME = 物理文件名,
[SIZE = 文件大小,]
[MAXSIZE = {最大容量 | UNLIMITED},]
[FILEGROWTH = 文件增长量],
)
LOG ON
(
[NAME = 逻辑文件名,]
FILENAME = 物理文件名,
[SIZE = 文件大小,]
[MAXSIZE = {最大容量 | UNLIMITED},]
[FILEGROWTH = 文件增长量],
)
go
(二)参数分析:在上面的结构中,[]表示可选部份,{}表示需要的部份
1、数据库名:数据库的名称,最长为128个字符
2、PRIMARY:将数据文件的所属指定为主文件组
3、LOG ON:日志文件的定义
4、NAME:数据库在SQL SERVER中的逻辑名称
5、FILENAME:指数据库所在文件的操作系统文件名和路径
6、SIZE:指定数据库的初始容量大小
7、MAXSIZE:指定操作系统文件可以增长到的最大尺寸
8、FILEGROWTH:指定文件每次增加容量的大小,当指定数据为0时,表示文件不增长
(三)相关示例
1、一个数据文件和一个日志文件
USE master
GO
CREATE DATABASE Sales
ON
(
NAME = Sales_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
(
NAME = 'Sales_log',
FILENAME = 'c:/program files/microsoft sql server/mssql/data/salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
GO
2、多个数据文件和多个日志文件
USE master
GO
CREATE DATABASE Archive
ON PRIMARY
(
NAME = Arch1,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
),
(
NAME = Arch2,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
),
(
NAME = Arch3,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
)
LOG ON
(
NAME = Archlog1,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
),
(
NAME = Archlog2,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
)
GO
3、使用文件组创建数据库
CREATE DATABASE Sales
ON PRIMARY
(
NAME = SPri1_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15%
),
(
NAME = SPri2_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15%
),
FILEGROUP SalesGroup1 (
NAME = SGrp1Fi1_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
),
(
NAME = SGrp1Fi2_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
),
FILEGROUP SalesGroup2 (
NAME = SGrp2Fi1_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
),
(
NAME = SGrp2Fi2_dat,
FILENAME = 'c:/program files/microsoft sql server/mssql/data/SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
(
NAME = 'Sales_log',
FILENAME = 'c:/program files/microsoft sql server/mssql/data/salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
GO
4、分离数据库及附加数据库:
系统中已经存在一个名为Archive的数据库,可以使用sp_detach_db 存储过程分离该数据库,然后使用带有 FOR ATTACH 子句的CREATE DATABASE 重新附加。"c:/program files/microsoft sql server/mssql/data/archdat1.mdf"是该数据库的物理文件路径,被分离的数据不能处于被使用状态。
sp_detach_db Archive
GO
CREATE DATABASE Archive
ON PRIMARY
(
FILENAME = 'c:/program files/microsoft sql server/mssql/data/archdat1.mdf'
) FOR ATTACH
GO
二、删除数据库
(一)格式: DROP DATABASE 数据库名
(二)为了避免在创建数据库时,在SQLSERVER中已经存在相同名称的数据库,可以使用判断语句进行判断,如果存在先删除再进行创建,SYSDATABASES位于MASTER数据库,表用于存储SQL SERVER中现有的数据库信息
USE MASTER
GO
IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = '数据库名') DROP DATABASE 数据库名
CREATE DATABASE 数据库名
ON ( ... )
LOG ON ( ... )
GO
SQL Server 数据库设计和实现(二)
最新推荐文章于 2021-11-18 17:52:27 发布