数据库的基本操作

一、实验目的：
掌握数据库的基础知识，了解数据库的物理组织与逻辑组成情况，学习创建、修改、查看、缩小、更名、删除等数据库的基本操作方法。
二、实验内容和主要知识点（掌握数据库的创建与维护方法和步骤）
从交互方式和T-SQL两种方式进行总结书写
1、创建数据库
交互式：
在打开对象资源管理器后，点击已经连接的数据库引擎节点，在数据库节点或某用户数据库节点上单击鼠标右键，在弹出的快捷菜单中，选中“新建数据库”菜单项，会弹出如下对话框

在这里可以确定数据库名称、所有者、是否使用全文索引、数据库文件信息等。在这里我们输入数据库名称“jxgl”。同时打开“数据文件”选项卡，增加一个文件jxgl_data，初始大小为2MB。打开“事务日志”选项卡，增加一个日志文件jxgl_log，初始大小为2MB.点击确定后即可创建数据库。
在完成“常规”页信息指定后，可选中“选项”页，可按需要指定排序规则、恢复模式、兼容级别、其它选项等选项值。点击“文件组”页则能对数据库的文件组信息进行指定，能添加新的“文件组”页以备数据库使用。

最后在“脚本”下拉列表框可选择将新建数据库对话框中已指定的创建数据库信息以脚本的形式保存到“新建查询”窗口、文件、剪贴板或作业中。产生的脚本能保存起来，以备以后修改使用。在完成所有设定后，点击“确定”按钮，即可完成新建数据库的创建。
在创建JXGL数据库后，我们可通过在数据库一栏右击查看属性，点击文件，即可查看jxgl_data.mdf、jxgl_log.ldf两数据库文件所处的子目录。如下图所示

删除数据库可直接右击数据库，选择删除，即可删除该数据库，利用其它方法，再建相同要求的该数据库。

T-SQL：
首先新建一个“新建查询”窗口，使用CREATE DATABASE命令创建数据库，如下图所示

此时刷新一下数据库就可以看到创建好的数据库。
又或者我们可以使用SSMS自带的模板，选择CREATE DATABASE模板进行创建数据库

Tongyang 在点击刷新后即可看到创建好的数据库。
接下来在Management Studio中，打开一个查询窗口，按照表2.2所示的要求，创建数据库student2，要求写出相应的CREATE DATABASE命令，并执行创建该数据库。

代码如下

IF DB_ID (N'student2') IS NOT NULL DROP DATABASE student2;
DECLARE @data_path nvarchar(256);       -- @data_path中存放SQL Server数据库路径
SET @data_path=(SELECT SUBSTRING(physical_name,1,CHARINDEX(N'master.mdf', LOWER(physical_name))-1) 
               FROM master.sys.master_files 
               WHERE database_id=1 AND file_id=1);

EXECUTE ('CREATE DATABASE student2 ON PRIMARY 
(NAME=studentg_dat,FILENAME='''+@data_path+'student_dat.ndf'',SIZE=5,MAXSIZE=20,FILEGROWTH=10%)
LOG ON ( NAME=student_log,FILENAME='''+@data_path+'studentlog.ldf'',
SIZE=2MB,MAXSIZE=15MB,FILEGROWTH=2MB)');  -- 以执行字符串命令形式执行

请分别使用交互方式和T-SQL语句创建数据库Student，要创建的数据库的要求如下所示：数据库名称为Student，包含3个20MB的数据库文件，2个10MB的日志文件，创建使用一个自定义文件组，主文件为第一个文件，主文件的后缀名为.mdf，次要文件的后缀名为.ndf；要明确地定义日志文件，日志文件的后缀名为.ldf；自定义文件组包含后两个数据文件，所有的文件都放在目录“C:\DATA”中。
交互式：
在新建数据库对话框选中文件组，新建一个自定义文件组test

根据题目的要求，将3个20MB的数据库文件，2个10MB的日志文件，主文件的后缀名为.mdf，次要文件的后缀名为.ndf，并将两个次要文件放在自定义文件组test中

这样以来，符合该要求的数据库student就建成了。

T-SQL语句：
代码如下

IF DB_ID (N'student') IS NOT NULL DROP DATABASE student;
DECLARE @data_path nvarchar(256);       -- @data_path中存放SQL Server数据库路径
SET @data_path=(SELECT SUBSTRING(physical_name,1,CHARINDEX(N'master.mdf', LOWER(physical_name))-1) 
               FROM master.sys.master_files 
               WHERE database_id=1 AND file_id=1);
EXECUTE (
    'CREATE DATABASE student ON PRIMARY 
    ( 
        NAME=student1_dat,
        FILENAME=''c:\student1_dat.mdf'',
        SIZE=20,MAXSIZE=50,FILEGROWTH=15%
    ),
    FILEGROUP studentGroup1 
    (
        NAME=studentg11_dat,
        FILENAME='''+@data_path+'studentg11_dat.ndf'',
        SIZE=20,MAXSIZE=50,FILEGROWTH=5
    ),
    ( 
        NAME=studentg12_dat,
        FILENAME='''+@data_path+'studentg12_dat.ndf'',
        SIZE=20,MAXSIZE=50,FILEGROWTH=5
    )
    LOG ON ( 
        NAME=student_log1,
        FILENAME='''+@data_path+'studentlog1.ldf'',
        SIZE=10MB,MAXSIZE=25MB,FILEGROWTH=5MB),
    ( 
        NAME=student_log2,
        FILENAME='''+@data_path+'studentlog2.ldf'',
        SIZE=10MB,MAXSIZE=25MB,FILEGROWTH=5MB
    )
');  -- 以执行字符串命令形式执行

打开student数据库的属性查看文件如下，可验证数据库成功按要求创建。

创建实验15中“15.2 企业库存管理及Web网上订购系统(C#/ASP.NET技术)”的数据库KCGL。你可以在Management Studio中交互方式创建，也可以利用CREATE DATABASE命令创建。根据该数据库数据容量的估算，其文件初始大小要求是：①“数据文件”名为KCGL_Data.mdf，初始大小为100MB，以后按5%自动增长，大小不限；②“事务日志”名为KCGL_log.ldf，初始大小为50MB，以后按5%自动增长，最大不超过200MB。
交互式：
新建一个数据库，在新建数据库对话框进行如下选项,设置文件

最后点击完成即可成功创建数据库KCGL。

T-SQL语句：
代码如下

IF DB_ID (N'KCGL') IS NOT NULL DROP DATABASE KCGL;
DECLARE @data_path nvarchar(256);       -- @data_path中存放SQL Server数据库路径
SET @data_path=(SELECT SUBSTRING(physical_name,1,CHARINDEX(N'master.mdf', LOWER(physical_name))-1) 
               FROM master.sys.master_files 
               WHERE database_id=1 AND file_id=1);
EXECUTE (
    'CREATE DATABASE KCGL ON PRIMARY 
    ( 
        NAME=KCGL_dat,
        FILENAME=''c:\KCGL_dat.mdf'',
        SIZE=100,FILEGROWTH=5%
    )
    LOG ON 
    ( 
        NAME=KCGL_log,
        FILENAME='''+@data_path+'KCGL_log.ldf'',
        SIZE=50MB,MAXSIZE=200MB,FILEGROWTH=5%
    )
');  -- 以执行字符串命令形式执行

打开KCGL数据库的属性，对其文件进行验证，发现创建成功。

2、查看和修改数据库信息
(1)右击数据库，从弹出的快捷菜单中选择“属性”命令，打开“student2属性”对话框，打开“选项”选择页，修改“数据库为只读”属性为“TRUE”。这样数据库就变为只读数据库，接着对数据库作改动操作如删除表、更新表等，查看这些操作是否可行？

在“对象资源管理器”中选择某数据库，从弹出的快捷菜单中选择“属性”命令，打开“student2属性”对话框，打开“选项”选择页，修改“数据库为只读”属性为“TRUE”，这样数据库就变为只读数据库。
修改完后，发现在对该数据库进行改动操作，将无法进行，出现如下的对话框

(2)然后在查询窗口中，使用T-SQL语句更改数据库选项。如把只读属性改还来：

ALTER DATABASE AdventureWorks SET READ_WRITE;

再做同样的数据库更新操作，看是否可行？
在SSMS新建一个查询窗口，输入如下代码

ALTER DATABASE AdventureWorks SET READ_WRITE;

在输入代码后，进行操作发现操作可行了，可直接删除表，修改表，如下图示例

(3)收缩数据库：
交互式：
在Management Studio中交互方式收缩某数据库，方法为：右击数据库，从弹出的快捷菜单中选择“任务”→“收缩”→“数据库”菜单命令，在出现的“收缩数据库”对话框中，按“确定”来完成数据库收缩（也可以同时指定文件的最大可用空间）。

然后选中收缩操作，并且可以设置收缩后文件中的最大可用空间，设置后后点击确定即可。

T-SQL语句：
使用T-SQL语句压缩数据库。在打开的某数据库查询窗口中，输入DBCC SHRINKDATABASE(student2，10)，使student2数据库有10%的自由空间。
在新建的查询窗口输入如下语句即可

DBCC SHRINKDATABASE(student2,10)

(4)更改数据库：在打开的某数据库查询窗口中，利用ALTER DATABASE命令实现更改数据库student2，参数如表2.3所示。

代码如下：

DECLARE @data_path nvarchar(256);       -- @data_path中存放SQL Server数据库路径
SET @data_path=(SELECT SUBSTRING(physical_name,1,CHARINDEX(N'master.mdf', LOWER(physical_name))-1) 
               FROM master.sys.master_files 
               WHERE database_id=1 AND file_id=1);
ALTER DATABASE student2 ADD FILEGROUP Studentfg;
EXECUTE ('
ALTER DATABASE student2 
ADD FILE 
(NAME = Student2_dat,
FILENAME ='''+@data_path+'Student2_dat.ndf'', 
SIZE = 2MB,MAXSIZE = 100MB,FILEGROWTH = 5MB), 
(NAME = Student3_dat,
FILENAME = '''+ @data_path + 'Student3_dat.ndf'',
SIZE = 2MB,MAXSIZE = 100MB,FILEGROWTH= 5MB
)
TO FILEGROUP Studentfg

ALTER DATABASE student2 
ADD LOG FILE 
(
NAME=Student2_log,
FILENAME='''+@data_path+'Student2_log.ldf'', 
SIZE = 2MB,MAXSIZE = 30MB,FILEGROWTH = 2MB)
');

打开数据库student2的属性界面可以看到如下

发现修改成功。

3、分离数据库
分离数据库是指将数据库从 SQL Server 实例中删除，但使数据库在其数据文件和事务日志文件中保持不变。之后，就可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。如果存在下列三种情况，则不能分离数据库：
1）已复制并发布数据库。如果进行复制，则数据库必须是未发布的。必须通过运行 sp_replicationdboption 禁用发布后，才能分离数据库。如果无法使用实验 2 数据库的基本操作 13 sp_replicationdboption，可以通过运行 sp_removedbreplication 删除复制。
2）数据库中存在数据库快照。必须首先删除所有数据库快照，然后才能分离数据库。要注意不能分离或附加数据库快照。
3）数据库处于可疑状态。在SQL Server 2014中，无法分离可疑数据库，必须将数据库置入紧急模式，才能对其进行分离。将数据库置入紧急模式命令如：ALTER DATABASE [jxgl] SET emergency --命令将数据库jxgl置入紧急状态；而ALTER DATABASE jxgl SET offline --命令将数据库jxgl置入脱机状态；ALTER DATABASE jxgl SET online --命令将数据库jxgl 置入联机状态。

分离数据库的方法：
1）通过 sp_detach_db 命令
命令语法： sp_detach_db [ @dbname= ] ‘dbname’ [ , [ @skipchecks= ] ‘skipchecks’ ][ , [ @KeepFulltextIndexFile= ] ‘KeepFulltextIndexFile’] 其中 skipchecks、KeepFulltextIndexFile 的值可以是 TRUE、FALSE、NULL 等。
举个例子，我们可以分离上述已创建的数据库jxgl
代码如下

SP_DETACH_DB 'jxgl','true','true'

2）在 Management Studio 中分离数据库
对象资源管理器数据库在 jxgl（为某一要分离的数据库名）上鼠标右键“任务” “分离”出现“分离数据库”对话框在对话框右边指定分离选项：要“删除连接”？要“更新统计信息”？要“保留全文目录”?后按“确定”按钮即可。
具体步骤如下图：

4、附加数据库
您可以附加复制的或分离的 SQL Server 数据库。在 SQL Server 2014 中，数据库包含的全文文件随数据库一起附加。通常，附加数据库时会将数据库重置为它分离或复制时的状态。但是，附加和分离操作都会禁用数据库的跨数据库所有权链接。此外，附加数据库时，TRUSTWORTHY 均设置为 OFF（可通过 ALTER DATABASE 设置为 ON)。附加数据库时，所有数据文件（MDF 文件和 NDF 文件）都必须可用。如果任何数据文件的路径不同于首次创建数据库或上次附加数据库时的路径，则必须指定文件的当前路径。
附加数据库的方法：
1）通过 CREATE DATABASE 命令附加命令语法：CREATE DATABASE database_name ON [,…n] FOR { { ATTACH [ WITH [,…n ]]}|ATTACH_REBUILD_LOG }[;]
举个例子，附加已分离了的数据库jxgl

DECLARE @data_path nvarchar(256);       -- @data_path中存放SQL Server数据库路径
SET @data_path=(SELECT SUBSTRING(physical_name,1,CHARINDEX(N'master.mdf', LOWER(physical_name))-1) 
               FROM master.sys.master_files 
               WHERE database_id=1 AND file_id=1);

EXECUTE ('CREATE DATABASE jxgl ON (NAME=jxgl,FILENAME = '''+ @data_path + 'jxgl.mdf'') FOR
ATTACH');

2）在 Management Studio 中附加数据库对象资源管理器->数据库->“附加”->出现“附加数据库”对话框-> 按右中“添加”按钮-> 在“定位数据库文件”对话框中，选择要附加的数据库的主数据文件-> 按“确定”按钮返回-> 最后，按“确定”完成附加
使用分离和附加操作移动数据库分为以下阶段：①分离数据库；②将数据库文件移到其它服务器磁盘上；③通过指定移动文件的新位置附加数据库。
具体步骤如下：

最后在数据库一栏刷新一下，即可看见附加的数据库。
3）通过 sp_attach_db 命令附加数据库 jxgl 的命令为(注意不鼓励使用)：
EXECUTE (‘EXEC sp_attach_db @dbname = N’‘jxgl’’, @filename1 = ‘’’+ @data_path + ‘jxgl.mdf’’, @filename2 = ‘’’+ @data_path + ‘jxgl_log.ldf’’;’)

5、备份数据库
数据库创建后，所有的对象和数据均已添加且都在使用中，有时需要对其进行维护
交互式：
打开数据库，找打需要备份的数据库，鼠标右键选择任务—备份

接下来选择备份文件的位置。

在选择完存储路径后，点击确定即可完成备份。在介质选项中尽量选择追加，备份完成后先择验证。

T-SQL语句：
代码如下：

use master;
go
---声明变量
declare @dbName nvarchar(max)='student2';
declare @dbFullName nvarchar(max)='D:\SQL_backups\test1.bak';
---备份数据库
DECLARE @kid varchar(100)  
SET @kid=''  
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10))  FROM master..sysprocesses  
WHERE dbid=DB_ID(@dbName)  
PRINT @kid  
EXEC(@kid);
backup database student2 to disk=@dbFullName;

在新建查询窗口执行该代码后即可备份该数据库。

在备份位置也可以看到备份文件了

6、恢复数据库
交互式：
选择要恢复的数据库，右击在任务中选中还原数据库，如下图所示

接下来点击确定

即可恢复数据库成功

T-SQL语句：
代码如下：

use master
go
---声明变量
declare @dbName nvarchar(max)='student2';
declare @dbFullName nvarchar(max)='D:\SQL_backups\test1.bak';
--1.1修改为单用模式
 exec(N'ALTER DATABASE '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
--1.2结束链接进程
    DECLARE @kid varchar(max)  
    SET @kid=''  
    SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10))  FROM master..sysprocesses  
    WHERE dbid=DB_ID(@dbName)  ;
    EXEC(@kid) ;
--2.执行还原语句
   restore database @dbName from  disk=@dbFullName
   with replace  --覆盖现有的数据库
--3.重置数据库为多用户模式
 exec(N'ALTER DATABASE '+@dbName+' SET MULTI_USER WITH ROLLBACK IMMEDIATE');

在新建一个查询窗口执行该代码后即可完成对数据的恢复。

7、修改日志文件
交互式：
在SSMS的对象资源管理器中，选择需要修改的文件对应的数据库，右击该数据库，选择它的属性，在属性页中选择文件页

在这里可以直接操作文件，可删除文件或添加文件。

T-SQL语句：
（1）、添加新文件
代码如下

DECLARE @data_path nvarchar(256);       -- @data_path中存放SQL Server数据库路径
SET @data_path=(SELECT SUBSTRING(physical_name,1,CHARINDEX(N'master.mdf', LOWER(physical_name))-1) 
               FROM master.sys.master_files 
               WHERE database_id=1 AND file_id=1);
EXECUTE ('
ALTER DATABASE AdventureWorks 
ADD FILE 
(
NAME = Test2dat2,
FILENAME = '''+ @data_path + 'test2dat2.ndf'',
SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB
)
')

新建一个查询窗口，执行如下代码即可新建相关文件
（2）、删除文件
代码如下：

ALTER DATABASE AdventureWorks remove FILE test3dat3;

新建一个查询窗口，执行如下代码即可删除相关文件

8、修改数据库名称
在 SQL Server 2014 中，可以更改数据库的名称。在重命名数据库之前，应该确保没有人使用该数据库，而且该数据库设置为单用户模式。数据库名称可以包含任何符合标识符规则的字符。
交互式：
在对象资源管理器中，选择要修改名称的数据库，右键点击它，选择“重命名”菜单项

接下来就可以直接修改或输入新的数据库名称，点击Enter键确定即可。
T-SQL语句：
代码如下：

Alter database jhjj MODIFY NAME=xbd

在新建查询窗口执行改代码后，刷新数据库，即可看到数据库名字发生改变。

9、删除数据库
当不再需要用户定义的数据库，或者已将其移到其它数据库或服务器上时，即可删除该数据库。数据库删除之后，文件及其数据都从服务器上的磁盘中删除。一旦删除数据库，它即被永久删除，并且不能进行检索，除非使用以前的备份。不能删除系统数据库。
删除数据库后，应备份 master 数据库，因为删除数据库将更新 master 数据库中的信息。如果必须还原 master，自上次备份 master 以来删除的任何数据库仍将引用这些不存在的数据库。这可能导致产生错误消息。
必须满足下列条件才能删除数据库：①如果数据库涉及日志传送操作，请在删除数据库之前取消日志传送操作；②若要删除为事务复制发布的数据库，或删除为合并复制发布或订阅的数据库，必须首先从数据库中删除复制。如果数据库已损坏，不能首先删除复制，通常您仍然可以通过首先使用ALTER DATABASE将数据库设置为脱机然后再删除的方法来删除数据库；③必须首先删除数据库上存在的数据库快照。
交互式：
在对象资源管理器中，选择要删除的数据库，鼠标右击，选中“删除”菜单项，出现“删除对象”对话框，按“确定”按钮即可完成删除操作。

T-SQL语句：
代码如下
```javascript
DROP DATABASE xbd

若该数据库有快照文件，则应该先执行DROP语句删除快照，再进行删除数据库的操作。

更多推荐

数据库的基本操作

数据库的基本操作

发布评论取消回复

最近发表

热门文章

标签列表

数据库的基本操作

相关文章

发布评论取消回复

最近发表

热门文章

标签列表