SQLServer數(shù)據(jù)庫技術-03數(shù)據(jù)表管理

1、濟南大學 信息科學與工程學院 張坤1 數(shù)據(jù)表簡介2 數(shù)據(jù)類型3 數(shù)據(jù)表創(chuàng)建4 數(shù)據(jù)表修改5 數(shù)據(jù)表刪除6 關系圖23系統(tǒng)表服務器的配置信息、數(shù)據(jù)表的定義信息等只讀，由數(shù)據(jù)庫系統(tǒng)維護用戶表已分區(qū)表將數(shù)據(jù)水平劃分為多個單元的表，這些單元可以分布到數(shù)據(jù)庫中的多個文件組中臨時表由于系統(tǒng)或用戶運算的臨時需要而創(chuàng)建的表，使用完畢后即可刪除。本地臨時表、全局臨時表4字段名數(shù)據(jù)類型是否允許為Null是否自動增長IDNETITY屬性5Northwind數(shù)據(jù)庫(SQL Server 2000)SQL Server 2012上安裝Northwind數(shù)據(jù)庫6右擊-打開方式-SQL Server Management

2、Studio-執(zhí)行出錯-SQL Server 2012中不再支持存儲過程sp_dboption1 數(shù)據(jù)表簡介2 數(shù)據(jù)類型3 數(shù)據(jù)表創(chuàng)建4 數(shù)據(jù)表修改5 數(shù)據(jù)表刪除6 關系圖72.1 精確數(shù)字型2.2 近似數(shù)字型2.3 日期時間型2.4 字符串型2.5 Unicode字符串型2.6 二進制字符串型2.7 其他數(shù)據(jù)類型2.8 用戶自定義型8整數(shù)型帶固定精度和小數(shù)位數(shù)的數(shù)據(jù)類型貨幣型9數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度bigint-263(-9 223 372 036 854 775 808) 263-1(9 223 372 036 854 775 807)8字節(jié)int-231(-2 147

3、483 648) 231-1(2 147 483 647) 4字節(jié)smallint-215(-32 768) 215-1(32 767)2字節(jié)tinyint02551字節(jié)bit0、1或Null如果表中的列為8位或更少，則這些列作為1個字節(jié)存儲。如果列為9到16位，則這些列作為2個字節(jié)存儲，以此類推。10整數(shù)型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度numeric-1038+1 1038-1存儲長度與精度有關19位：5字節(jié)1019位：9字節(jié)2028位：13字節(jié)2938位：17字節(jié)decimal-1038+1 1038-1存儲長度與精度有關19位：5字節(jié)1019位：9字節(jié)2028位：13字節(jié)2

4、938位：17字節(jié)11帶固定精度和小數(shù)位數(shù)的數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度money-922 337 203 685 477.5808 922 337 203 685 477.58078字節(jié)smallmoney-214 748.3648 214 748.36474字節(jié)12貨幣型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度float-1.79E+308 -2.23E-308，0以及2.23E-3081.79E+308，最多可以表示15位數(shù)存儲長度與數(shù)值的位數(shù)有關7位數(shù)時：4字節(jié)15位數(shù)時：8字節(jié)real-3.40E+38-1.18E-38，0以及1.18E-383.40E+38

5、，最多可以表示7位數(shù)4字節(jié)13近似數(shù)字型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度datetime1753年1月1日到9999年12月31日，可精確到3.33毫秒8字節(jié)smalldatetime1900年1月1日到2079年6月6日，可精確到分鐘4字節(jié)14日期和時間型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度char18000個字符1個字符占1個字節(jié)，為固定長度，如果插入的數(shù)據(jù)不夠定義的長度，系統(tǒng)會自動補上空格varcharvarchar(n): 18000個字符varchar(max): 1231-1個字符varchar(n): 1個字符占1個字節(jié)varchar(max):輸入數(shù)據(jù)的實際

6、長度加2個字節(jié)Text1231-1個字符1個字符占1個字節(jié)，存儲多少個字符即占多少空間，最大可存儲2GB數(shù)據(jù)15字符串型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度nchar14000個字符1個字符占2個字節(jié)，為固定長度，如果插入的數(shù)據(jù)不夠定義的長度，系統(tǒng)會自動補上空格narcharvarchar(n): 14000個字符varchar(max): 1231-1個字符varchar(n): 1個字符占2個字節(jié)varchar(max):輸入數(shù)據(jù)的實際長度的兩倍再加2個字節(jié)ntext1230-1個字符1個字符占2個字節(jié)，存儲多少個字符即占多少空間，最大可存儲2GB數(shù)據(jù)16Unicode字符串型數(shù)據(jù)

7、數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲長度存儲長度binary18000個字節(jié)為固定長度，如果插入的數(shù)據(jù)不夠定義的長度，系統(tǒng)會自動補上0 x00varbinaryvarbinary(n): 18000個字節(jié)varbinary(max): 1231-1個字節(jié)varbinary (n): 可變長度，輸入數(shù)據(jù)的實際長度varbinary(max):輸入數(shù)據(jù)的實際長度加2個字節(jié)image1231-1個字節(jié)可變長度，輸入數(shù)據(jù)的實際長度17二進制字符串型數(shù)據(jù)cursorsql_varianttimestampuniqueidentifiertableXML18sql_variant可用來存儲除了text、ntext

8、、image、timestamp、sql_variant之外的所有的SQL Server 2008支持的數(shù)據(jù)類型，其主要用于列、參數(shù)、變量和用戶定義函數(shù)的返回值中。當某個字段需要存儲不同類型的數(shù)據(jù)時，可以將其設置為sql_variant。XML可以在列或變量中存儲XML文檔和片段XML片段是缺少單個頂級元素的XML實例存儲空間不能超過2GB19timestamp8字節(jié)的16進制數(shù)據(jù)在數(shù)據(jù)庫范圍內提供唯一值在數(shù)據(jù)庫中更新或插入數(shù)據(jù)行時，此數(shù)據(jù)類型定義的列的值會自動更新，一個計算值被自動添加到列中，而且此值在整個數(shù)據(jù)庫中是唯一的每個數(shù)據(jù)表中只能有一個是timestamp類型的字段uniqueide

9、ntifier與timestamp類似16字節(jié)的16進制數(shù)據(jù)提供的是全球范圍內的唯一值20cursor主要是用于變量或存儲過程OUTPUT參數(shù)的一種數(shù)據(jù)類型，這些參數(shù)包含對游標的引用。Cursor主要用來存儲查詢結構，它是一個數(shù)據(jù)集，其內部的數(shù)據(jù)可以單條取出來進行處理table主要用于臨時存儲一組行，這些行是作為表值函數(shù)的結果集返回的，其用途與臨時表很相似主要用于函數(shù)、存儲過程和批處理中21用戶可以根據(jù)需要自定義數(shù)據(jù)類型，并可以用此數(shù)據(jù)類型來聲明變量或字段如可以將經(jīng)常使用的char(100)數(shù)據(jù)類型自定義為一個數(shù)據(jù)類型，如char100。221 數(shù)據(jù)表簡介2 數(shù)據(jù)類型3 數(shù)據(jù)表創(chuàng)建4 數(shù)據(jù)表修

10、改5 數(shù)據(jù)表刪除6 關系圖23SQL Server Management Studio定義數(shù)據(jù)表的字段和主鍵 主鍵：一個字段或多個字段的組合設置默認值設置標識列 自動增長。標識種子、標識增量建立表與表之間的關系 INSERT和UPDATE規(guī)范：不執(zhí)行任何操作、級聯(lián)、設置Null、設置默認值創(chuàng)建索引創(chuàng)建約束 主鍵約束、外鍵約束、Unique約束、Check約束、默認值、允許空設置數(shù)據(jù)表所在文件組24smalldatetime - getdate()25提示說不允許保存修改工具-選項-設計器-表設計器和數(shù)據(jù)庫設計器-阻止保存要求重新創(chuàng)建表的更改26CREATE TABLE database_nam

11、e . schema_name . | schema_name . table_name -設置表名 ( -設置列屬性 | -設置計算列 ,.n ) -設置表約束 ON partition_scheme_name ( partition_column_name ) | filegroup | default -指定存放表數(shù)據(jù)的分區(qū)架構或文件組 TEXTIMAGE_ON -指定存放Text及Image類型字段數(shù)據(jù)的分區(qū)架構或文件組 filegroup | default 27創(chuàng)建新表CREATE TABLE Northwind.dbo.例一表(編號 int,姓名 nvarchar(50)CREA

12、TE TABLE Northwind.例一表(編號 int,姓名 nvarchar(50)USE NorthwindCREATE TABLE 例一表(編號 int,姓名 nvarchar(50)28創(chuàng)建標識字段CREATE TABLE 例二表(編號 int IDENTITY,姓名 nvarchar(50)創(chuàng)建帶標識種子和標識增量的標識字段CREATE TABLE 例三表(編號 int IDENTITY(1,2) PRIMARY KEY,姓名 nvarchar(50)創(chuàng)建非NULL字段CREATE TABLE 例四表(編號 int IDENTITY(1,1) PRIMARY KEY,姓名 nva

13、rchar(50) NOT NULL)29設置字段的默認值CREATE TABLE 例五表(編號 int IDENTITY(1,1) PRIMARY KEY,姓名 nvarchar(50) NOT NULL,性別 bit DEFAULT 1)30創(chuàng)建新表并將其放在文件組中CREATE TABLE 例六表(編號 int IDENTITY(1,1) PRIMARY KEY,姓名 nvarchar(50) NOT NULL,性別 bit DEFAULT 1)ON 第二文件組31創(chuàng)建新表并使用Check約束CREATE TABLE 例七表(編號 int IDENTITY(1,1) PRIMARY KE

14、Y,姓名 nvarchar(50) NOT NULL,性別 bit DEFAULT 1,年紀 tinyint CONSTRAINT CK_年紀 CHECK (年紀0 AND 年紀0 AND 年紀0),合計 as 單價*數(shù)量)37設置數(shù)據(jù)表的約束CREATE TABLE 例十三_用戶注冊表(編號 int IDENTITY(1,1) PRIMARY KEY,用戶名 nvarchar(50) Not null,密碼 varchar(16) Not null,電話 varchar(50),地址 nvarchar(200),CHECK (電話 is not null or 地址 is not null)

15、381 數(shù)據(jù)表簡介2 數(shù)據(jù)類型3 數(shù)據(jù)表創(chuàng)建4 數(shù)據(jù)表修改5 數(shù)據(jù)表刪除6 關系圖39SQL Server Management Studio修改數(shù)據(jù)表名插入、移動和刪除數(shù)據(jù)數(shù)據(jù)表的字段修改字段數(shù)據(jù)類型及屬性 數(shù)據(jù)表有記錄時不能隨意修改，只能修改成兼容的數(shù)據(jù)類型。 Varchar(5) Varchar(10), Varchar(10)-varchar(5)截斷 Bit-decimal(18,0), decimal(18,0)-bit不一定修改主鍵、外鍵、索引和約束修改數(shù)據(jù)表的屬性40ALTER TABLE database_name . schema_name . | schema_name

16、. table_name ALTER COLUMN column_name -要修改的字段名 type_schema_name. type_name ( precision , scale | max | xml_schema_collection ) -修改后的數(shù)據(jù)類型 NULL | NOT NULL -設置是否為NULL COLLATE collation_name 設置排序規(guī)則 | ADD | DROP ROWGUIDCOL | PERSISTED -添加或刪除ROWGUIDCOL屬性 | WITH CHECK | NOCHECK ADD -添加字段 -設置字段屬性 | -設置計算列 |

17、 -設置表約束 ,.n | DROP -刪除 CONSTRAINT constraint_name -刪除約束 WITH ( ,.n ) -設置聚集約束選項 | COLUMN column_name 刪除字段 ,.n | WITH CHECK | NOCHECK CHECK | NOCHECK CONSTRAINT -啟用或禁用約束 ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER -啟用或禁用觸發(fā)器 ALL | trigger_name ,.n | SWITCH PARTITION source_partition_number_exp

18、ression -切換數(shù)據(jù)塊 TO schema_name. target_table PARTITION target_partition_number_expression 41更改表字段的屬性ALTER TABLE 例一表ALTER COLUMN 姓名 nvarchar(20) COLLATE Chinese_PRC_CI_AS not null添加字段ALTER TABLE 例一表ADD 性別 nvarchar(2)添加字段并設置字段屬性ALTER TABLE 例一表ADD 密碼 varchar(16) NOT Null42刪除字段ALTER TABLE 例一表DROP COLUMN

19、密碼添加約束ALTER TABLE 例一表ADD CONSTRAINT CK_性別 CHECK (性別=男 OR 性別=女)默認情況下，添加Check約束時會檢查數(shù)據(jù)表里的數(shù)據(jù)是否符合Check約束，如果有數(shù)據(jù)不符合Check約束，添加Check約束會失敗。如果希望添加約束時不檢查數(shù)據(jù)庫里的數(shù)據(jù)是否符合Check約束，參考如下：ALTER TABLE 例一表WITH NOCHECK ADD CONSTRAINT CK_性別 CHECK (性別=男 OR 性別=女)43禁用約束ALTER TABLE 例一表NOCHECK CONSTRAINT CK_性別一次禁用多個約束ALTER TABLE 例

20、一表NOCHECK CONSTRAINT CK_1,CK_2,CK_3禁用所有約束ALTER TABLE 例一表NOCHECK CONSTRAINT ALL44啟用約束ALTER TABLE 例一表CHECK CONSTRAINT CK_性別一次啟用多個約束ALTER TABLE 例一表CHECK CONSTRAINT CK_1,CK_2,CK_3啟用所有約束ALTER TABLE 例一表CHECK CONSTRAINT ALL45刪除約束ALTER TABLE 例一表DROP CK_性別設置主鍵ALTER TABLE 例一表ALTER COLUMN 編號 int not nullGOALTE

21、R TABLE 例一表ADD CONSTRAINT PK_主鍵 PRIMARY KEY (編號)GO46修改字段名和數(shù)據(jù)表名Alter Table沒有直接修改數(shù)據(jù)表名和字段名的功能。使用存儲過程sp_rename (SQL Server 2008)exec sp_rename 例一表.姓名,名稱,COLUMNexec sp_rename 例一表,例二十三表sp_rename 原對象名稱 , 新對象名稱 , 對象類型471 數(shù)據(jù)表簡介2 數(shù)據(jù)類型3 數(shù)據(jù)表創(chuàng)建4 數(shù)據(jù)表修改5 數(shù)據(jù)表刪除6 關系圖48SQL Server Management Studio當數(shù)據(jù)表有外鍵依賴時是不能被刪除的 查看表的依賴關系一次刪除多個數(shù)據(jù)表 摘要窗格中選擇多個數(shù)據(jù)表49DROP TABLE database_name . schema_name . | schema_name . table_