第二單元 資料庫操作

1. 資料庫文件組成

主數據文件

• 主要數據文件的建議文件擴展名是 .mdf。

• 主要數據文件包含資料庫的啟動信息，並指向資料庫中的其他文件，存儲部分或全部的數據。用戶數據和對象可存儲在此文件中，也可以存儲在次要數據文件中。

• 每個資料庫有一個主要數據文件。

• mdf文件並非普通文件，因此不藉助相應軟體是無法打開 mdf文件的。打開mdf文件的常用虛擬光碟機軟體主要有：Daemon Tools 、東方光碟機魔術師等。

次要數據文件 （*.ndf）

• 次要數據文件的建議文件擴展名是 .ndf。

• 次要數據文件是可選的，由用戶定義並存儲用戶數據，用於存儲主數據文件未能存儲的剩餘數據和一些資料庫對象。

• 通過將每個文件放在不同的磁碟驅動器上，次要文件可用於將數據分散到多個磁碟上。

• 如果資料庫超過了單個 Windows 文件的最大大小，可以使用次要數據文件，這樣資料庫就能繼續增長。

事務日誌 （*.ldf）

• 事務日誌的建議文件擴展名是 .ldf。

• 事務日誌文件保存用於恢複數據庫的事務日誌信息。資料庫的插入、刪除、更新等操作都會記錄在日誌文件中，而查詢不會記錄在日誌文件中。整個的資料庫有且僅有一個日誌文件。

• 每個資料庫必須至少有一個日誌文件。

2. 文件組

不同的文件可以存分佈到不同的物理硬碟上，這樣便於分散硬碟IO，提高數據的讀取速度。

數據文件的組合，稱作文件組（File Group），資料庫不能直接設置存儲數據的數據文件，而是通過文件組來指定。

文件和文件組的關係

SQL Server 的數據存儲在文件中，文件是實際存儲數據的物理實體，文件組是邏輯對象，SQL Server 通過文件組來管理文件。

一個資料庫有一個或多個文件組，其中主文件組（Primary File Group）是系統自動創建的，用戶可以根據需要添加文件組。 每一個文件組管理一個或多個文件，其中主文件組中包含主要數據文件（*.mdf），主文件組中也可以包含次要數據文件 。（主要數據文件是系統預設生成的，並且在資料庫中是唯一的；次要數據文件是用戶根據需要添加的。） 除了主文件組之外，其他文件組只能包含輔助文件。 如下示例資料庫，系統已自動創建主文件組 PRIMARY，勾選 Default 表示將主文件組設置為預設文件組，即如果在 create table 和 create index 時沒有指定 FileGroup 選項，那麼 SQL Server 將使用預設的 PRIMARY 文件組來存儲數據。

文件組是一個邏輯實體，實際上，數據存儲在文件中（.mdf和.ndf）中，每一個文件組中都包含文件，如下圖：

由上圖可以看到，資料庫文件的元數據：

Logical Name – 文件的邏輯名稱，用於數據壓縮 DBCC ShrinkFile 等； File Type – 文件類型，有兩種：Rows Data（存儲數據）和 Log（存儲日誌）； Initial Size – 文件初始大小； Autogrowth/Maxsize – Autogrowth 表示文件自動增加的步長，

Maxsize 表示文件大小的最大值限制； Path – 文件存放路徑； File Name – 文件的物理名稱，邏輯名稱和物理名可以不同 。

使用文件組的優勢

在實際開發資料庫的過程中，通常情況下，用戶需要關註文件組，而不用關心文件的物理存儲，即使DBA改變文件的物理存儲，用戶也不會察覺到，也不會影響資料庫去執行查詢。除了邏輯文件和物理文件的分離之外，SQL Server使用文件組還有一個優勢，那就是分散IO負載，其實現的原理是：

對於單分區表，數據只能存到一個文件組中。如果把文件組內的數據文件分佈在不同的物理硬碟上，那麼SQL Server能同時從不同的物理硬碟上讀寫數據，把IO負載分散到不同的硬碟上。 對於多分區表，每個分區使用一個文件組，把不同的數據子集存儲在不同的磁碟上，SQL Server在讀寫某一個分組的數據時，能夠調用不同的硬碟IO。

3. 資料庫操作

1. 使用SSMS方式

SSMS : Microsoft SqlServer Management Studio， 也就是資料庫管理軟體。

1. 在對象資源管理器中，右鍵單擊資料庫文件夾/圖標，然後選擇 New database...

2. 進行資料庫命名，此處叫 “TaskTracker”，然後點擊 “OK”

   

3. 資料庫展示

   

2. T-SQL 方式

1. 創建資料庫

-- 創建資料庫
create database 第二單元測試
-- 指定數據文件存儲的文件組  on:在。。。。這上，primary:主文件組
on primary  
(
    -- 資料庫的邏輯名稱:相當於是某人的外號
    Name = '第二單元測試', -- 邏輯名稱需要是唯一
    filename = 'D:\test\第二單元測試_物理名稱.mdf', -- 物理名稱
    size=5mb, -- 文件初始大小，初始化必須>=5 ,因為創建資料庫的model 模板信息 必須是5mb以上
    filegrowth = 4mb,  -- 每次增長多少
    maxsize =200mb  -- 文件的最大值
);

2. 創建次文件

alter database 第二單元測試
add file
(
    -- 資料庫的邏輯名稱:相當於是某人的外號
    Name = '第二單元測試_次文件',
    filename = 'E:\test\第二單元測試_次文件.ndf', -- 物理名稱
    size=5mb, -- 文件初始大小，初始化必須>=5 ,因為創建資料庫的model 模板信息 必須是5mb以上
    filegrowth = 4mb,  -- 每次增長多少
    maxsize =200mb  -- 文件的最大值
)

3. 簡化創建資料庫（初學者推薦）

-- create database <資料庫名稱>;
create database 任我行教學管理系統;

4. 刪除資料庫

-- 切換資料庫
use master;
-- drop database <資料庫名稱>;
drop database 任我行教學管理系統;

5. 查看資料庫信息

-- exec sp_helpdb '<資料庫名稱>'
exec sp_helpdb 'Soa模擬考試'

6. 修改資料庫名稱

-- exec sp_renamedb '<需要修改的資料庫的名稱>','<新的資料庫名稱>' ;

exec sp_renamedb 
'第二單元測試', -- 需要修改的資料庫的名稱
'第二單元'  -- 新的資料庫名稱

7. 切換資料庫

-- use <資料庫名稱>
use 任我行教學管理系統;

4. 備份與還原

可能有一天，資料庫遭黑客攻擊，資料庫遭破壞，這個時候就需要時常的做文件的備份。也有可能公司來了一個馬大哈，把資料庫給刪除（刪庫跑路），這個時候也需要備份。

備份

-- backup database <資料庫名稱> to disk = '磁碟路徑';
backup database 第二單元測試 to disk ='D:\test\第二單元測試.bak';

還原

• 資料庫不存在的情況下

-- restore database <資料庫名稱>  from disk = '磁碟路徑'
restore database 第二單元測試 from disk = 'D:\test\第二單元測試.bak'

• 資料庫存在的情況下

-- with replace:替換
-- restore database <資料庫名稱>  from disk = '磁碟路徑' with replace;
restore database 第二單元測試 from disk = 'E:\test\第二單元測試.bak' with replace;

5. 附加與分離

假設我有一個比較好的資料庫，大家都想要，我可以發給你們，但是直接發送不了，因為會提示“這個文件在資料庫SqlServer中打開”， 這個時候就需要使用分離，將這個資料庫文件中SqlServer中 T 出去。

現在資料庫已經分離並且資料庫也發給你們了，我自己也想要用這個資料庫，這個時候就要重新的附加到SQLSERVER中來

分離

-- execute:執行
-- sp_detach_db：分離的存儲過程（理解為一個函數）
-- execute sp_detach_db '<資料庫名稱>'
execute sp_detach_db 'Soa模擬考試'

附加

-- 附加
-- sp_attach_db:附加的存儲過程
-- exec sp_attach_db '<資料庫名稱>','<資料庫文件所在路徑>' ;
​
exec sp_attach_db 'Soa模擬考試','C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Soa模擬考試.mdf'

配套視頻鏈接：【階段二】 - SQLServer 基礎(超級詳細，口碑爆盆)_嗶哩嗶哩_bilibili