溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
前言：上次主要介紹的是sql server數據庫的搭建以及通過SSMS工具進行連接數據庫，本次內容主要介紹數據庫的表進行管理。在工作中DBA通常對數據庫進行創建和刪除以及修改表的內容，擴展和收縮、分離及附加。
表是存放數據、查找數據以及更新數據的基本數據構建，在對數據的操作都是在表的基礎上進行的，以及如何維護表。 注：實際上表是邏輯的存在，事實上數據存放在硬盤上面。
另外在創建數據庫的時候回形成兩個基本的庫文件主數據庫文件.mdf和事物日志文件.ldf
數據庫在磁盤上是以文件為單位存儲的，由數據文件和事物日志文件組成。一個數據庫至少應該包含一個數據文件和一個事物日志文件。
數據庫創建在物理介質（磁盤）上的一個或多個文件上，它預先分配了被數據和事物日志所要適用的物理存儲空間。存儲數據的文件叫做數據文件，數據文件包含數據和對象，如表和索引。存儲事物日志的文件叫做事物日志文件（又稱日志文件）。
sql server 2008 R2數據庫有一下類型的文件
1. 主數據文件
2. 輔數據文件
3. 事物日志文件
4. 文件流
舉例說明
--創建數據庫
create database schoolDB
go
use schoolDB
go
這樣創建完的數據庫只有一個數據文件和一個事物日志文件，查看方式在創建的schoolDB上右鍵選擇“屬性”--“文件”
可以在界面中單擊“添加”增加新的數據文件schooldb2，以后再schooldb數據庫中創建新的表，表中的數據就會存放在schooldb和schoolDB2數據文件中。
數據文件在數據庫中存放的時候都有標識，我們可以通過sp_helpdb schoolDB
數據文件結構
數據文件1和數據文件3的結構如果所示：
數據文件由64kb大小的區（extend)組成的,每個區由8個8KB的連續的頁組成的。
sqlserver所能識別的最小存儲單元被稱為頁，一個頁的大小是8KB,是sql server 存儲數據的單位。
在數據庫中一頁的大小是8KB，在計算機中1KB就是1024，所以1頁可以存放1024*8=8192個字節
1.1. 如何根據表的記錄數量估算占用的磁盤空間
1.先算一個表中一行有多少個字節，例如學號字段6個字節，姓名字段8個字節，出生日期字段多少個字節
2.算一個頁能夠存多少行
3.數據庫中的表每天增加多少行記錄，就能夠算出每天要增加多少硬盤空間，這樣就可根據數據量估算規劃多大的空間。
在數據庫中創建一張Tstudent表
create TABLE TStudent (
StudentID varchar(10) NOT NULL,
Sname varchar(10) DEFAULT NULL,
sex char(2) DEFAULT NULL,
cardID varchar(20) DEFAULT NULL,
Birthday datetime DEFAULT NULL,
Email varchar(40) DEFAULT NULL,
Class varchar(20) DEFAULT NULL,
enterTime datetime DEFAULT NULL
)
go
10個字節+10個字節+2個字節+20字節+8字節+40字節+20字節+8個字節=118字節
Tstudent表的一個行有118個字節
如果每天增加10000條記錄，10000條記錄占多少頁？
1頁可以存放1024*8=8192個字節，拋去標頭96字節，真正存放的字節數就8192-96=8096字節。
一頁能存8096字節，一頁能夠存8096/118=68條記錄
10000行有多少頁？10000/68=148(頁）
一頁是8KB，148*8==1184KB
在Tstudent表中存放10000條記錄需要預備出來1MB多的磁盤空間。這樣就可以根據每天增加的記錄數，合理規劃好磁盤空間了。
現在Tstudent表中還沒有插入任何記錄，所以占用了0頁的數據
提供查看數據文件頁數的命令
select OBJECT_NAME(i.object_id) as 表名,data_pages as 數據頁數
from sys.indexes as i
join sys.partitions as p ON p.object_id=i.object_id and p.index_id=i.index_id
join sys.allocation_units as a ON a.container_id=p.partition_id
where i.object_id=object_id('dbo.TStudent')
執行教學環境中的存儲過程，添加10000條記錄
這個頁數和我們剛才算出來的有些差距，這是正常的，每一頁可能并沒有插入68條記錄。
事物是一個或多個T-SQL語句的集合，事物有一個特性：要么執行成功，要么執行失敗。每個sql server數據庫都具有事物日志，用于記錄所有事物的SQL語句。當發生數據災難時候，通過事物日志記錄的T-SQL語句可以恢復數據庫。
如果系統出現故障，sql server將使用事物日志重做（前滾）所有已確認的事物，撤銷（回滾）所有未完成的事物。
使用圖形界面創建數據庫，在對象資源管理器中，右擊“數據庫”節點，在彈出的快捷菜單中選擇“新建數據庫”命令，打開“新建數據庫”窗口，設置數據庫名稱為“class”，制定數據庫的所有者，默認創建數據庫的用戶將成為該數據庫的所有者。主數據文件初始值為3mb,自動增長值為1mb，當數據文件或日志文件空間使用完之后，會根據設定的自動增長值增大文件的容量。
在路徑列中可以設置數據文件和日志文件的保存路徑。單擊“確定”之后一個新的數據庫就建立成功了，隨之會生成兩個文件一個是包含數據文件的.mdf，一個是包含日志信息的.ldf文件。
創建數據庫之前，必須先確定數據庫的名稱，所有者（創建數據庫的用戶），大小以及存儲該數據文件和事物日志文件的位置。
如果要對數據庫做數據庫級別的設置
在數據庫屬性中選擇“選項”，在右側可以修改數據庫相關的屬性。
當數據庫中的數據文件和日志文件被充滿時候，需要為數據文件和日志文件分配更多的空間。sql server 可以根據在新建數據庫時定義的增長參數自動擴展數據庫，也可以通過在現有的數據文件上分配更多的文件空間，或者在另一個新的數據文件上分配空間來手動擴展數據庫。
擴展數據庫時，必須使數據庫的容量至少增加1mb，還可以指定允許文件增長到的最大值，這樣可以防止文件無限制的增長，導致用盡整個磁盤空間。
舉例說明：
將class數據庫的class數據文件大小設置為100MB,不自動增長，然后添加一個新的數據文件，文件名為“class1”，初始文件大小設置為“50MB”，自動增長，最大文件大小設置為“500MB”
在對象資源管理器中，右擊class數據庫，在彈出的快捷菜單中選擇“屬性”命令，打開“數據庫屬性-class”窗口，在“數據庫屬性-class”窗口的“選擇頁”中選擇“文件”選項，打開“文件”選擇頁
配置為如下圖所示：最后單擊“確定”
數據庫在使用一段時間后，時常會因為數據刪除而造成數據庫中空閑空間增多的情況，這時就需要減少分配給數據庫文件和事物日志文件的磁盤空間，以免浪費磁盤空間。當數據庫中沒有數據時，可以修改數據庫文件屬性，直接改變其占用空間，但是當數據庫中有數據時，這樣做會破壞數據庫中的數據，因此需要使用收縮的方式來縮減數據庫空間。
數據庫中的每個文件都可以通過刪除未使用的空間的方法來減小，SQL server允許通過縮小數據庫，把不使用的空間釋放出來，數據文件和日志文件都可以收縮。可以采用手動收縮和自動收縮數據庫。
手動收縮數據庫的方法
從圖中可以看出來即可以選擇收縮數據庫，也可以選擇單獨收縮某個數據文件。
收縮數據庫：
在收縮數據庫時，無法將整個數據庫收縮到比初始大小更小，如果數據庫創建時的大小是10mb，后來增長到10mb,則該數據庫最小只能收縮到10mb，即使已經刪除了所有數據也是10mb。但是收縮文件時，可以將數據庫文件收縮得比其初始大小更小
收縮數據文件
選擇--“任務”--“收縮”--“文件”命令，打開收縮文件對話框
在“文件類型”下拉列表框中選擇“數據”選項，也可以在這里選擇收縮日志文件。”當前分配的空間“選項和”可用空間“選項顯示了該文件的占用空間，使用空間和收縮的百分比。
釋放未使用的空間：將釋放文件中所有未使用的空間，并將文件收縮到上次分配的大小，這樣將減小文件大小，但不移動任何數據。
在釋放未使用空間前重新組織頁：將釋放文件中所有未使用的空間，并嘗試重新定位到未分配的空間。這里需要指定”將文件收縮到“選項的值。
通過將數據遷移到同一文件組中的其他文件來清空文件：將指定文件中所有數據遷移至同一文件組中的其他文件中，然后可以刪除空文件。
根據需要確定選項，然后單擊“確定”，執行收縮操作。
自動收縮數據庫
可以通過設置“數據庫屬性--class”窗口中的“選項”選擇頁中的“自動收縮”選項參數來實現自動收縮功能。將“自動收縮”選項設置為“true”后，將自動收縮可用空間的數據庫。
如果要將數據庫更改到同一計算機的不同sql server實例中或要移動數據庫，就可以使用分離和附加數據庫的功能了。可以分離數據庫的數據文件和日志文件，然后將它們從新附加到同一其他SQL Server實例中。
分離數據庫
分離數據庫是指將數據庫從SQL server實例中移除，但是要保證數據庫中的數據文件和日志文件完整無損。這些分離的數據文件和日志文件可以附加到其他的數據庫實例中。
舉例說明：
將class數據庫進行分離
在對象資源管理器中右擊class數據庫，在彈出的快捷菜單中選擇“任務”---“分離”命令，打開“分離數據庫”窗口。
數據庫中有一個或多個活動連接時，“消息”列將顯示活動連接的數量，如“一個活動連接”。必須選中“刪除連接”復選框以斷開所有活動連接。
默認情況下，分離操作將在分離數據庫時保留過期的優化統計信息，若要更新現有的優化統計信息，可以選中“更新統計信息”復選框。
附加數據庫
附加數據庫時，所有的數據文件（主數據文件和次要數據文件）都必須可用。如果任何數據文件的路徑不同于首次創建數據庫或上次附加數據庫時的路徑，則必須指定文件的當前路徑。
單擊“添加”按鈕，選擇“class.mdf”數據文件
單擊“確定”之后，附加文件成功。
如果數據庫的事物日志文件丟失，數據文件保持完好，也可以附加成功，在附加的時候，數據庫會自動新建事物日志文件。
當用戶不在需要自己的數據庫時就可以刪除數據庫了，但不能刪除系統數據庫。刪除數據庫后，文件及其數據都從服務器上的磁盤中刪除，一旦刪除數據庫，就會被永久刪除。
確認要刪除的數據庫
數據完整性：
實體完整性：確保每一行有一個唯一的標識列：
域完整性：規定該列能夠接受的數據取值（域完整性）
引用完整性：表內和表間的列參照
用戶定義的完整性：列級約束和表級約束，存儲過程及觸發器
主鍵：唯一標識表中的記錄，一個主鍵值對應一行數據。主鍵由一個或多個字段組成，主鍵值具有唯一性，而且不允許取空值（null），一個表只能有一個主鍵。
如果主鍵由多個列組成，則其中一個列將允許有重復值，但是主鍵中所有列的值得各種組合必須是唯一的。
定義主鍵可以對在不允許空值的指定列中輸入的值強制其唯一性。如果為數據庫中的某個表定義了主鍵，則可將該表與其他表相關連，從而減少對冗余數據的要求。
sql server 中的每個數據庫最多可以存儲20億個表，每個表可以有1024列，表的行數及大小僅受可用存儲空間的限制，每行最多可以存儲8060B，創建表時必須指定表名，列名即數據類型等。
1. 數字型代表數字 int tinyint smallint bigint 十進制小數 money smallmoney decimal 浮點數和real
2. 日期型 datetime 可以精確到0.333毫秒 small
3. 字符型包括char和nchar 也包含變長字符類型varchar和nvarchar
4. 定長字符 char(20)
5. 變長字符 varchar(20)
6. Char 適合存放英文一個字符占用1個字節
7. Nchar 適合存放中文一個字符占用2個字節
8. 二進制型 Binary和varbinary，bit代表一位的值0或1，rowversion代表數據庫中唯一的8位二進制。
如果插入行時沒有為列指定值，則該列使用默認值，默認值可以是計算結果為常量的任何值，可以是表達式，內置函數或數學表達式。
對于表的每個列，如果沒有分配默認的值，并且保留為空白：則
1.如果設置了允許空值的選項，則將象該列中插入NULL
2.如果沒有設置允許為空值，則該列將保持空白。但在用戶為該列提供值之前，他們將無法保存行。
對于每個表，均可創建一個博暗含系統生成的序號值得標識付列，該序號值以唯一的方式標識表中的每一行。當在表中插入行時，標識符列可自動為應用程序生成唯一的標號。
標識符列具有以下三種特點
1.列的數據類型為不帶小數的數值類型
2.在進行插入操作時，該列的值有系統按一定規律生成，不允許用空值
3.列值不重復，具有標識表中每一行的作用。每個表只能有一個標識列
創建標識列，通常指定三個內容
類型：decimal ,int,numeric,smallint,bigint,tinyint。其中decimal和numeric，小數位數必須為零
種子：指派給表中第一行的值：默認為“1”
地增量：相鄰兩個標識值之間的增量，默認為1.
通過限制列可接收的值，check約束可以強制域的完整性。此類約束類似于外鍵約束，因為可以控制放入列中的值。但是他們在確定有效值的方式上有所不同。外鍵約束從其他表中獲得有效值列表，而check約束通過不基于其他列中的數據的邏輯表達式確定有效值。
可以通過任何基于邏輯運算符返回TRUE或FALSE的邏輯表達式創建check約束。
舉例說明：
可以通過創建check約束將age列中的值得范圍限制在0-200之間的數據，以防止輸入的年齡值超出正常的年齡范圍，邏輯表達式為：
age >=0 and age <=200
在class 數據庫中創建表，表名為“student”，其中包含列為：學生編號（int），姓名（nvarchar(50)），×××號（varchar(18)），所在班級（thinyint），成績（tinyint）,備注（nvarchar(2000)），其中學生編號自動生成，從1開始，每增加1人則編號自動增加1，要求輸入成績的時候，其值必須為0-100，設置×××號列為主鍵：
為學生編號列設置標識列，標識增量和標識種子設置為“1”，如圖所示：
分別新建“姓名”，“×××號”，“所在班級”，“成績”，“備注”如圖所示
由于要求輸入成績時，值必須是0-100,因此可以在成績列設置“check約束”，
在打開的“check約束”對話框中，單擊“添加”按鈕，然后在“常規”文本框中輸入“表達式”為“成績>=0 and 成績<=100”。單擊確定即可
右擊“×××號”，在彈出的快捷菜單中選擇“設置主鍵”，可將該列設置為主鍵，主鍵列的左邊會顯示“×××鑰匙”，圖標
設置完成后，保存該表，
輸入表的名稱“student”
在對象資源管理器中查看剛剛創建的student表
編輯student表，右擊表，在彈出的快捷菜單中選擇“編輯前200行”命令，然后可以在表中插入、更新或刪除數據了
修改表的結構
如果需要修改表結構，在表中新增或刪除字段，則需要重新對表進行設計，在對象資源管理器中，右擊student表，選擇“設計”命令
刪除表：如果需要刪除表，可以右擊表student，在彈出的快捷菜單中選擇“刪除”命令
使用T-SQL語句操作數據表
使用iNSERT語句將數據插入到表中
INSERT INTO <表名> [列名] VALUES <值列表>
INSERT INTO student (姓名，×××號，所在班級，成績) VALUES ('百合'，'152323198201190045',2,90)
更新數據
使用UPDATE語句更新表中的數據
UPDATE <表名> set <列名 = 更新值> [WHERE <更新條件>]
update student set 成績=‘88’ where 姓名=‘玉蘭’
刪除數據
使用delete語句刪除表中的數據
DELETE FROM <表名> [WHERE <刪除條件>]
DELETE FROM student WHERE 姓名=‘玉蘭’
刪除student表中所有記錄
DELETE FROM student
使用truncate table 語句刪除表中的數據
truncate table 語句用來刪除表中的所有行，功能上類似于沒有WHERE子句的DELETE語句。
TRUNCATE TABLE <表名>
要刪除student表中的所有記錄，可以使用如下語句
TRUNCATE TABLE student
TRUNCATE語句與DELETE語句的區別
1. TRUNCATE語句不帶WHERE子句，只能將整個表數據清空，而DELETE語句可以帶WHERE子句，允許按條件刪除某些記錄
2. TRUNCATE語句不記錄事物日志，而DELETE語句無論刪除多少條記錄，都會記錄事物日志，所以使用TRUNCATE語句刪除數據后是無法通過事物日志恢復數據。
3. TRUNCATE語句刪除表中所有行，標識符列會重置為0，而DELETE語句不會重置標識付列。
4. TRUNCATE語句不能用于有外鍵約束引用的表，這種情況下需要使用DELETE語句。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
