T-SQL之資料庫操作

極限四維發表於2018-03-29
SQL資料庫

對資料的簡介：

        資料庫是資料儲存的倉庫，使用者在利用資料庫管理系統提供的功能時，首先必須將資料儲存到使用者的資料庫中。資料庫中儲存的物件主要有：表，索引和檢視。（SQL Server支援在一個例項中建立多個資料庫，且這些資料庫在物理和邏輯上都是相互獨立的）。

資料庫檔案（資料檔案和日誌檔案）：

    1）資料檔案可以有多個，但是主資料檔案只能有一個;
    2）日誌檔案也可以宣告多個;
    3）資料庫檔案的屬性（檔名及其位置，初始大小，增長方式，最大大小） 。


資料庫的分類：

在SQL Server中資料庫可以分為兩類：系統資料庫和使用者資料庫。（以下只簡單的介紹一下系統資料庫）
- >系統資料庫（master，msdb，model，tempdb）
   master：是SQL Server中最重要的資料庫，用於記錄系統級資訊（該資料庫損壞則SQL Server將無法正常工作）;
   msdb：儲存關於排程報警，作業，操作員等資訊;
   model：使用者資料庫模板，當使用者建立一個資料庫時，會將model資料庫的全部內容複製到新建資料庫中;
   tempdb：是臨時資料庫，用於儲存使用者建立的臨時表（查詢操作的表即儲存其中），使用者宣告的變數以及使用者定義的遊標資料等。

T-SQL之資料庫操作

T-SQL之資料庫建立

建立資料庫主要使用CREATE DATABASE語句

1，建立資料庫的語法：

1)、建立資料庫的語法：
CREATE DATABASE <Db_name>
[ON [PRIMARY]
(
	[NAME = <logical_file_name>,]
	FILENAME = <file_name>
	[, SIZE = <size>]
	[, MAXSIZE = <max_size>]
	[, FILEGROWTH = <growth_size|percentage>]
)]
[LOG ON
(
	[NAME = <logical_file_name>,]
	FILENAME = <file_name>
	[, SIZE = <size>]
	[, MAXSIZE = <max_size>]
	[, FILEGROWTH = <growth_size|percentage>]
)]
[ COLLATE <collation_name> ]
[ FOR ATTACH [WITH <service_broker>]| FOR ATTACH_REBUILD_LOG| WITH DB_CHAINING {ON|OFF } | TRUSTWORTHY { ON|OFF }]
[AS SNAPSHOT OF <source_database_name>]
[;]


--語法引數說明
--1、CREATE DATABASE <Db_name>：為建立資料庫的主要語法，其中Db_name為資料庫的名稱；
--2、PRIMARY：標識該mdf檔案為主檔案(系統預設第一個為主資料檔案)；
--3、NAME：檔案的邏輯名稱（在同一個資料庫中該值必須唯一）；
--4、FILENAME：檔案的具體儲存路徑（必須完整且存在）；
--5、SIZE：設定資料檔案的初始大小（單位：M、G等）；
--6、MAXSIZE：設定資料檔案的最大容量（單位：M、G等）；
--7、FILEGROWTH：設定資料檔案的增長量(可以為具體的儲存量亦可以為一個百分數)；
--8、COLLATE：可應用於資料庫定義或列定義以定義排序規則，或應用於字串表示式以應用排序規則投影；(可以不加預設為系統的排序規則)；
--9、FOR ATTACH：將已存在的資料庫檔案附加到當前伺服器上；
--10、WITH DB_CHAINING {ON|OFF }：如果該選項為ON,則跨資料庫的所有權鏈有效；否則全鏈無效；
--11、TRUSTWORTHY { ON|OFF }：控制訪問的安全性(預設情況下為關閉狀態)；
--12、AS SNAPSHOT OF <source_database_name>：指定要建立的資料庫為source_database_name指定的源資料庫的資料庫快照(快照和源資料庫必須位於同一例項中)。

2，建立資料庫的示例

--建立資料庫(CREATE DATABASE語句)


--1)建立系統預設的資料庫
CREATE DATABASE Demo
GO
--程式碼說明：
--該語句為建立資料庫的最簡單形式，所有的引數都為系統預設(資料庫檔案儲存在預設位置)


--2)建立指定的資料庫(單資料庫、但事務日誌)
CREATE DATABASE Demo
ON PRIMARY                 --PRIMARY指主資料檔案（為系統的預設值，可以省略）
(						--在這裡設定主資料檔案mdf                        
	NAME='Demo',								 --主資料檔案的邏輯名稱
	FILENAME='F:\sql\Demo.mdf',					 --檔案的具體儲存路徑(路徑必須完整存在)
	SIZE=3MB,								     --設定檔案的初始大小
	MAXSIZE=100MB,								 --設定檔案最大容量      UNLIMITED為不限制大小
  --filegrowth=1MB								 --設定檔案的增長量(可以為位元組，也可以為百分數)
	FILEGROWTH=10%
)
LOG ON						--建立資料事務(也可以新增多個事務日誌)
(						--在這裡設定事務日誌檔案                
	NAME='Demo_log',								--日誌的名稱
	FILENAME='F:\sql\Demo_log.ldf',				--日誌的完整路徑(路徑必須完整存在)
	SIZE=1MB,									--日誌檔案的初始大小
	MAXSIZE=UNLIMITED,							--日誌檔案的最大容量,不設最大限制
	FILEGROWTH=1MB								--增長速量為1MB
)
GO


--3)建立指定的資料庫(多資料庫、多事務日誌)
CREATE DATABASE DemoTest
ON PRIMARY                 --PRIMARY指主資料檔案（為系統的預設值，可以省略）
(						--在這裡設定主資料檔案mdf                        
	NAME='DemoTest1',							 --主資料檔案的邏輯名稱
	FILENAME='F:\sql\DemoTest1.mdf',			 --檔案的具體儲存路徑(路徑必須完整存在)
	SIZE=3MB,								     --設定檔案的初始大小
	MAXSIZE=100MB,								 --設定檔案最大容量      UNLIMITED為不限制大小
	FILEGROWTH=10%
),										
(						--在這裡設定次資料檔案mdf                        
	NAME='DemoTest2',							 --資料檔案的邏輯名稱
	FILENAME='F:\sql\DemoTest2.mdf',			 --檔案的具體儲存路徑(路徑必須完整存在)
	SIZE=3MB,								     --設定檔案的初始大小
	MAXSIZE=100MB,								 --設定檔案最大容量      UNLIMITED為不限制大小
    FILEGROWTH=1MB								 --設定檔案的增長量為1MB
)
LOG ON		--建立資料事務
(						--在這裡設定事務日誌檔案                
	NAME='DemoTest1_log',							--日誌的名稱
	FILENAME='F:\sql\DemoTest1_log.ldf',			--日誌的完整路徑(路徑必須完整存在)
	SIZE=1MB,								    	--日誌檔案的初始大小
	MAXSIZE=20MB,						        	--日誌檔案的最大容量,不設最大限制
	FILEGROWTH=1MB								    --增長速量為1MB
),
(						--在這裡設定事務日誌檔案                
	NAME='DemoTest2_log',							--日誌的名稱
	FILENAME='F:\sql\DemoTest2_log.ldf',			--日誌的完整路徑(路徑必須完整存在)
	SIZE=1MB,									    --日誌檔案的初始大小
	MAXSIZE=UNLIMITED,							    --日誌檔案的最大容量,不設最大限制
	FILEGROWTH=10%								    --增長速量為10%
)
GO


T-SQL之資料庫修改

修改資料庫主要使用ALTER DATABASE語句

    對於資料庫的修改操作常使用到的有：修改資料庫中檔案的某些屬性，向資料庫中新增檔案和移除資料庫中的檔案，下面分別從這三個方面進行說明和程式碼展示。

1，修改資料庫中檔案的屬性：

--修改資料庫中已經定義好的檔案屬性
ALTER DATABASE Test5
MODIFY FILE						--修改指定資料庫中的資料檔案
(
	NAME = "Test5_data",			--檔案的邏輯名稱(必須項)
	SIZE = 10MB,					--修改檔案的大小
	MAXSIZE = 50MB,					--修改檔案的最大容量
	FILEGROWTH = 10%				--修改檔案的增長量
)


ALTER DATABASE Test5
MODIFY FILE						--修改資料庫中的日誌檔案
(
	NAME =  "Test5_log",		--日誌檔案的邏輯名稱(必須項)
	SIZE = 5MB,					--修改日誌檔案的初始大小
	MAXSIZE = 10MB,				--修改檔案的最大容量
	FILEGROWTH = 10%			--修改檔案的增長量
)

2，向已建立的資料中新增檔案：

--向已建立的資料中新增檔案
ALTER DATABASE Test5
ADD FILE									--向資料庫中新增資料檔案
(
	NAME = "Test5_data1",					--資料檔案的邏輯名稱
	FILENAME = "F:\sql\Test5_data1.mdf",	--資料檔案的物理位置
	SIZE = 10MB,							--初始容量
	MAXSIZE = 50MB,							--最大容量
	FILEGROWTH = 10%						--增長量
)
ALTER DATABASE Test5
ADD LOG FILE								--向資料庫中新增日誌檔案
(
	NAME = "Test5_log1",					--日誌檔案的邏輯名稱
	FILENAME = "F:\sql\Test5_log1.mdf",		--日誌檔案的物理儲存位置(完整性)
	SIZE = 10MB,							--初始容量
	MAXSIZE = 40MB,							--最大容量
	FILEGROWTH = 10%						--增長量
)

3，移除資料庫中的檔案

ALTER DATABASE Test5
REMOVE FILE Test5_data1			--移除資料庫中的資料檔案
GO


ALTER DATABASE Test5
REMOVE FILE Test5_log1			--移除資料庫中的日誌檔案
GO

T-SQL之資料庫刪除

刪除資料常使用DROP DATABASE語句
示例程式碼：
--刪除資料庫
DROP DATABASE Test5,Test							--刪除資料庫(將資料庫連根拔起)
GO

另：
DELETE FROM Staff							--主要用來刪除資料記錄(一條資料會生成一行日誌)
GO


TRUNCATE TABLE Test5						--清空表中的資料(只產生很少的日誌記錄)
GO


相關文章