SQL server学习02-使用T-SQL创建数据库

目录

一， 使用T-SQL创建数据库

1，数据库的存储结构 

2，创建数据库的语法结构 

1）使用T-SQL创建学生成绩管理数据库

二，使用T-SQL修改数据库

 1，修改数据库的语法结构

1）修改学生成绩管理数据库

①增加文件组及次要数据文件 

​编辑 ②增加日志文件

 ③修改主数据文件最大容量

2）使用T-SQL备份数据库

 3）使用T-SQL删除数据库

①无法删除数据库 "xxx"，因为该数据库当前正在使用

 4）重新加载数据库

---

我一开始接触的是MySQL，之所以要学SQL server，还是因为SQL server有MySQL所没有的优点：

1. 功能和特性上： 
   
   SQL server的管理工具（ SQL Server Management Studio）功能强大，适合企业级应用。支持更多的数据类型，如 XML、JSON、地理空间数据等。
    
2. 数据类型上：
   
   虽然MySQL 更加轻量，适合中小型应用，也支持 JSON，但在某些数据类型的实现上可能不如 SQL Server 灵活。

SQL server还有很多其它的优点，等之后做项目的时候就可以更加直观且清晰的感觉到，因此这里先不赘述。

一， 使用T-SQL创建数据库

 T-SQL全称为transact structured query language，事务化的结构查询语言。是对标准SQL语言功能的扩充。可以声明变量，有流程控制语句，支持对流程的控制等。其中，事务的概念如下：

•  事务transaction是需要一次性完成的操作集合。要么全部执行，要么全部不执行。

1，数据库的存储结构 

 通常SQL server数据库包含以下三类物理文件：

1. 主要数据文件（也称主数据文件）。扩展名为.mdf （main data  file）。
   
   在创建数据库时生成，用来存储数据库的启动信息，部分或全部数据及数据库对象，是所有数据文件的起点，包含指向其它数据文件的指针。每个数据库只能有一个主要数据文件。
    
2. 次要数据文件。扩展名为.ndf（个人ps：next data file）。
   
   辅助主数据文件存储数据及数据库对象，可以在创建数据库时创建，也可以在创建数据库后添加。当数据库存储的数据量巨大，超过了单个Windows文件的最大值或需要将重要数据与次要数据分开存储以提高数据访问速度和安全时，用户可以自行创建一个或多个次要数据文件。一个数据库可以没有次要数据文件，也可以同时拥有多个次要数据文件。
    
3. 日志文件。扩展名为.ldf （log data  file）。
   
   在创建数据库时生成，用于记录系统操作事件的记录文件或文件集合，这些记录可以作为恢复数据库的依据。当数据库损坏时，可以使用事务日志恢复数据库。每个数据库至少拥有一个日志文件，而且允许拥有多个日志文件。

2，创建数据库的语法结构 

一开始脑海里面得有下面这4句👇： 

--1，创数据库
CREATE DATABASE 数据库名 
--2，创数据文件（主文件）
ON PRIMARY(),
FILEGROUP 文件组名()
--3，创日志文件
LOG ON()
--4，批处理分隔符，用来提交T-SQL语句(默认提交次数为1,1可省略)
GO				 	

可以看到，在主数据文件部分包含一句：FILEGROUP 文件组名()，用来创建文件组。而文件组中存放的文件都是次要数据文件，以.ndf为文件名后缀。

批处理分隔符的设置如下👇，没有什么特别的要求，一般默认设置成“GO” 。

之后再往里面填充。这里从上至下，先填充主数据文件的部分。

ON PRIMARY(
NAME=主数据文件名,  -- 逻辑名
FILENAME='盘符\存储路径\主数据文件名.mdf',  -- 物理名
SIZE=主数据文件的初始大小,
MAXSIZE=主数据文件的最大容量,
FILEGROWTH=主数据文件自动增长的方式（可以是百分比也可以是具体的内存大小）
)

如果要求创建文件组的话，就需要在创建好主数据文件的结束括号后面加上逗号，再写上创文件组的语句：创建文件组和创建主数据文件类似，如下👇

,FILEGROUP 文件组名(
NAME=次要数据文件名,
FILENAME='盘符\存储路径\次要数据文件名.ndf',
SIZE=次要数据文件的初始大小,
MAXSIZE=次要数据文件的最大容量,
FILEGROWTH=次要数据文件自动增长的方式（可以是百分比也可以是具体的内存大小）
)

日志文件的创建也一样：

LOG ON(
NAME=日志文件名,
FILENAME='盘符\存储路径\日志文件名.ldf',
SIZE=日志文件的初始大小,
MAXSIZE=日志文件的最大容量,
FILEGROWTH=日志文件自动增长的方式（可以是百分比也可以是具体的内存大小）
)

知道该怎么创建数据库及数据库的3类物理文件之后，上题目：

1）使用T-SQL创建学生成绩管理数据库

1.  数据库名：StuScore
2. 主数据文件：StuScore_data，存储位置'盘符\...\STUSCOREDATA'，初始大小10MB，按照10%自动增长，最大容量200MB
3. 日志文件：StuScore_log，存储位置'盘符\...\STUSCORELOG'，初始大小3MB，自动增长方式为每次增长1MB，最大容量60MB

首先电脑上得事先创建好文件夹 STUSCOREDATA和STUSCORELOG。

之后按照题目要求编写语句： 

--1，创数据库
CREATE DATABASE StuScore 
--2，创数据文件（主文件）
ON PRIMARY(
NAME=StuScore_data,
FILENAME='D:\mySqlserver\STUSCOREDATA\StuScore_data.mdf',
SIZE=10MB,
MAXSIZE=200MB,
FILEGROWTH=5%)
--3，创日志文件
LOG ON(
NAME=StuScore_log,
FILENAME='D:\mySqlserver\STUSCORELOG\StuScore_log.ldf',
SIZE=3MB,
MAXSIZE=60MB,
FILEGROWTH=1MB)
--4，批处理分隔符，用来提交T-SQL语句(默认提交次数为1,1可省略)
GO				 	

创建的主数据文件和日志文件如下：

 

 

二，使用T-SQL修改数据库

 1，修改数据库的语法结构

通过ALTER DATABASE语句，可以进行如下操作：

1. 更改数据库名称，不能更改数据库的存储位置。
2. 添加，删除数据库文件及文件组，
3. 更改文件或文件组的属性，如名称，容量等。

 基本语法如下：

ALTER DATABASE 数据库名
-- 重命名数据库
MODIFY NAME<新的数据库名>
-- 添加/修改数据文件
ADD/MODIFY FILE<filespec>   
-- 删除指定文件
REMOVE FILE<文件逻辑名>
-- 添加/修改/删除文件组
ADD/MODIFY/REMOVE FILEGROUP<文件组名>
-- 添加日志文件
ADD LOG FILE<filespec>

其中，filespec指代如下：

<filespec> ::=
NAME=文件逻辑名,
FILENAME='盘符\存储路径\文件逻辑名.mdf/ndf/ldf',  -- 文件的物理名
SIZE=文件的初始大小,
MAXSIZE=文件的最大容量,
FILEGROWTH=文件自动增长的方式（可以是百分比也可以是具体的内存大小）

1）修改学生成绩管理数据库

①增加文件组及次要数据文件 

 要求：

1. 修改数据库StuScore
2. 增加次要文件StuScore_data2，存储位置'盘符\...\STUSCOREDATA'，初始大小20MB，最大容量2GB，按照10%自动增长，放到StuScoreGroup文件组中。

 之后按照要求编写语句：

ALTER DATABASE StuScore
ADD FILEGROUP StuScoreGroup
ALTER DATABASE StuScore
ADD FILE(
NAME=StuScore_data2,
FILENAME='D:\mySqlserver\STUSCOREDATA\StuScore_data2.ndf',
SIZE=20MB,
MAXSIZE=2GB,
FILEGROWTH=10%
) TO FILEGROUP StuScoreGroup

文件组StuScoreGroup中的次要数据文件StuScore_data2.ndf在磁盘上的存储位置👇： 

 ②增加日志文件

 要求：

1. 日志文件名：StuScore_log2，存储位置'盘符\...\STUSCORELOG'，初始大小10MB，不限制最大容量（UNLIMITED，不限制），按照1MB自动增长。

 按照要求编写语句：

ALTER DATABASE StuScore
ADD LOG FILE(
NAME=StuScore_log2,
FILENAME='D:\mySqlserver\STUSCORELOG\StuScore_log2.ldf',
SIZE=10MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1MB
)

 ③修改主数据文件最大容量

要求：将主数据文件StuScore_data的最大容量设置为500MB。

按照要求编写语句：

ALTER DATABASE StuScore
MODIFY FILE(
NAME=StuScore_data,
MAXSIZE=500MB
)

如下，修改成功 

2）使用T-SQL备份数据库

BACKUP DATABASE StuScore
TO DISK = 'D:\mySqlserver\Backups\StuScore.bak'
WITH FORMAT,MEDIANAME = 'StuScoreBackup',NAME = 'Full Backup of StuScore';

 3）使用T-SQL删除数据库

DROP DATABASE StuScore 

①无法删除数据库 "xxx"，因为该数据库当前正在使用

 如果点击执行之后提示“无法删除数据库 "xxx"，因为该数据库当前正在使用。”，先简单排除：

1. 是否用该数据库来新建查询。
    
2. 建议退出关闭当前的查询，重新开一个查询。

我就是用上面两种方法删除数据库StuScore，如果使用上述两种方法还是无法删除数据库，接下来就需要将数据库设置为单用户模式，断开所有连接，并删除数据库👇

ALTER DATABASE 需要删除的数据库名 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE 要删除的数据库名;

 4）重新加载数据库

RESTORE DATABASE StuScore
FROM DISK = 'D:\mySqlserver\Backups\StuScore.bak'
WITH NORECOVERY;  -- 多个备份文件还原
SELECT NAME FROM sys.databases 

 

如果两三分钟后，数据库依旧显示“正在还原”，可以输入如下语句，完成还原操作：

RESTORE DATABASE StuScore WITH RECOVERY;

 

之后到磁盘下，可以看到相关的数据文件和日志文件👇 

 有问题请在评论区留言或者是私信我，回复时间不超过1天。