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

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

2、Studio-執(zhí)行出錯(cuò)-SQL Server 2012中不再支持存儲(chǔ)過程sp_dboption1 數(shù)據(jù)表簡介2 數(shù)據(jù)類型3 數(shù)據(jù)表創(chuàng)建4 數(shù)據(jù)表修改5 數(shù)據(jù)表刪除6 關(guān)系圖72.1 精確數(shù)字型2.2 近似數(shù)字型2.3 日期時(shí)間型2.4 字符串型2.5 Unicode字符串型2.6 二進(jìn)制字符串型2.7 其他數(shù)據(jù)類型2.8 用戶自定義型8整數(shù)型帶固定精度和小數(shù)位數(shù)的數(shù)據(jù)類型貨幣型9數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲(chǔ)長度存儲(chǔ)長度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個(gè)字節(jié)存儲(chǔ)。如果列為9到16位，則這些列作為2個(gè)字節(jié)存儲(chǔ)，以此類推。10整數(shù)型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲(chǔ)長度存儲(chǔ)長度numeric-1038+1 1038-1存儲(chǔ)長度與精度有關(guān)19位：5字節(jié)1019位：9字節(jié)2028位：13字節(jié)2938位：17字節(jié)decimal-1038+1 1038-1存儲(chǔ)長度與精度有關(guān)19位：5字節(jié)1019位：9字節(jié)2028位：13字節(jié)2

4、938位：17字節(jié)11帶固定精度和小數(shù)位數(shù)的數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲(chǔ)長度存儲(chǔ)長度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ù)類型范圍范圍存儲(chǔ)長度存儲(chǔ)長度float-1.79E+308 -2.23E-308，0以及2.23E-3081.79E+308，最多可以表示15位數(shù)存儲(chǔ)長度與數(shù)值的位數(shù)有關(guān)7位數(shù)時(shí)：4字節(jié)15位數(shù)時(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ù)類型范圍范圍存儲(chǔ)長度存儲(chǔ)長度datetime1753年1月1日到9999年12月31日，可精確到3.33毫秒8字節(jié)smalldatetime1900年1月1日到2079年6月6日，可精確到分鐘4字節(jié)14日期和時(shí)間型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型范圍范圍存儲(chǔ)長度存儲(chǔ)長度char18000個(gè)字符1個(gè)字符占1個(gè)字節(jié)，為固定長度，如果插入的數(shù)據(jù)不夠定義的長度，系統(tǒng)會(huì)自動(dòng)補(bǔ)上空格varcharvarchar(n): 18000個(gè)字符varchar(max): 1231-1個(gè)字符varchar(n): 1個(gè)字符占1個(gè)字節(jié)varchar(max):輸入數(shù)據(jù)的實(shí)際

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

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

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

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

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

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

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

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

14、Y,姓名 nvarchar(50) NOT NULL,性別 bit DEFAULT 1,年紀(jì) tinyint CONSTRAINT CK_年紀(jì) CHECK (年紀(jì)0 AND 年紀(jì)0 AND 年紀(jì)0),合計(jì) as 單價(jià)*數(shù)量)37設(shè)置數(shù)據(jù)表的約束CREATE TABLE 例十三_用戶注冊(cè)表(編號(hào) 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 關(guān)系圖39SQL Server Management Studio修改數(shù)據(jù)表名插入、移動(dòng)和刪除數(shù)據(jù)數(shù)據(jù)表的字段修改字段數(shù)據(jù)類型及屬性 數(shù)據(jù)表有記錄時(shí)不能隨意修改，只能修改成兼容的數(shù)據(jù)類型。 Varchar(5) Varchar(10), Varchar(10)-varchar(5)截?cái)?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 -設(shè)置是否為NULL COLLATE collation_name 設(shè)置排序規(guī)則 | ADD | DROP ROWGUIDCOL | PERSISTED -添加或刪除ROWGUIDCOL屬性 | WITH CHECK | NOCHECK ADD -添加字段 -設(shè)置字段屬性 | -設(shè)置計(jì)算列 |

17、 -設(shè)置表約束 ,.n | DROP -刪除 CONSTRAINT constraint_name -刪除約束 WITH ( ,.n ) -設(shè)置聚集約束選項(xiàng) | 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)添加字段并設(shè)置字段屬性ALTER TABLE 例一表ADD 密碼 varchar(16) NOT Null42刪除字段ALTER TABLE 例一表DROP COLUMN

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

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

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