創(chuàng)建數(shù)據(jù)類型與表ppt課件

1、第第3章章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型和表n創(chuàng)建數(shù)據(jù)類型創(chuàng)建數(shù)據(jù)類型n創(chuàng)建表創(chuàng)建表n生成列值生成列值n生成腳本生成腳本n推薦操作推薦操作創(chuàng)建數(shù)據(jù)類型創(chuàng)建數(shù)據(jù)類型n系統(tǒng)提供的數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型n創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型n選擇數(shù)據(jù)類型的指導(dǎo)原則選擇數(shù)據(jù)類型的指導(dǎo)原則3.1 創(chuàng)建數(shù)據(jù)類型創(chuàng)建數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型n數(shù)字數(shù)據(jù)數(shù)字數(shù)據(jù)n整型數(shù)據(jù)：存儲整數(shù)整型數(shù)據(jù)：存儲整數(shù)n小數(shù)數(shù)據(jù)：包含存儲在最小有效數(shù)上的數(shù)據(jù)小數(shù)數(shù)據(jù)：包含存儲在最小有效數(shù)上的數(shù)據(jù)3.1.1 系統(tǒng)提供的數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型bigintbigint占8個字節(jié)，值的范圍為

2、 -263263-1intint占4個字節(jié)，值的范圍為 -231231-1smallintsmallint占2個字節(jié)，值的范圍為 -3276832 767tinyinttinyint占1個字節(jié)，值的范圍為 0255decimal (p,s)decimal (p,s)p為精度，最大38；s為小數(shù)位數(shù)，0spnumeric (p,s)numeric (p,s)在SQL Server中，等價于decimal系統(tǒng)提供的數(shù)據(jù)類型續(xù)）系統(tǒng)提供的數(shù)據(jù)類型續(xù)）n數(shù)字數(shù)據(jù)續(xù)）數(shù)字數(shù)據(jù)續(xù)）n近似數(shù)字數(shù)據(jù)：表示浮點數(shù)據(jù)的近似數(shù)字近似數(shù)字數(shù)據(jù)：表示浮點數(shù)據(jù)的近似數(shù)字n貨幣數(shù)據(jù)：表示正的或負的貨幣值貨幣數(shù)據(jù)：表示正的或

3、負的貨幣值3.1.1 系統(tǒng)提供的數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型float (float (n n)從 -1.79E+308 到 1.79E+308 之間的浮點數(shù)字數(shù)據(jù)；n 為用于存儲科學(xué)記數(shù)法尾數(shù)的位數(shù)，同時指示其精度和存儲大小，1n53realreal從 3.40E+38 到 3.40E+38 之間的浮點數(shù)字數(shù)據(jù)，存儲大小為 4 字節(jié)；SQL Server 中，real 的同義詞為 float(24) moneymoney占8個字節(jié)，值的范圍為 -922 337 203 685 477.580 8 +922 337 203 685 477.580 7smallmoneysmallmoney占4個

4、字節(jié)，值的范圍為 -214 748.3648 214 748.3647系統(tǒng)提供的數(shù)據(jù)類型續(xù)）系統(tǒng)提供的數(shù)據(jù)類型續(xù)）n日期和時間數(shù)據(jù)日期和時間數(shù)據(jù)n字符數(shù)據(jù)和字符數(shù)據(jù)和 Unicode 字符數(shù)據(jù)字符數(shù)據(jù)3.1.1 系統(tǒng)提供的數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型datetimedatetime占8個字節(jié)，表示從1753年1月1日到 9999年12月31日的日期smalldatetimesmalldatetime占4個字節(jié)，表示從1900年1月1日至2079年6月6日的日期char (char (n n)存儲字符個數(shù)為 08 000varchar (varchar (n n)存儲字符個數(shù)為 08 000tex

5、ttext存儲字符個數(shù)為 02GBnchar (nchar (n n)存儲字符個數(shù)為 04 000nvarchar(nvarchar(n n)存儲字符個數(shù)為 04 000ntextntext存儲字符個數(shù)為 01GB系統(tǒng)提供的數(shù)據(jù)類型續(xù)）系統(tǒng)提供的數(shù)據(jù)類型續(xù)）n二進制數(shù)據(jù)二進制數(shù)據(jù)n其他其他3.1.1 系統(tǒng)提供的數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型binary(binary(n n) ) 存儲字節(jié)個數(shù) 08 000varbinary(varbinary(n n) ) 存儲字節(jié)個數(shù) 08 000imageimage存儲字節(jié)個數(shù) 02 Gbitbit存儲位數(shù)據(jù)cursorcursor存儲對游標(biāo)的引用rowve

6、rsion (timestamp)rowversion (timestamp)時間戳(特點演示)sql_variantsql_variant可存儲除 text、ntext、image、rowversion 之外的其他類型tabletable存儲函數(shù)返回結(jié)果uniqueidentifieruniqueidentifier存儲 GUID 以及 UUID創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型3.1.2 創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型n用戶定義的數(shù)據(jù)類型用戶定義的數(shù)據(jù)類型n當(dāng)多個表的列中要存儲同樣類型的數(shù)據(jù)，且想確保當(dāng)多個表的列中要存儲同樣類型的數(shù)據(jù)，且想確

7、保這些列具有完全相同的數(shù)據(jù)類型、長度和為空性時這些列具有完全相同的數(shù)據(jù)類型、長度和為空性時，可使用用戶定義數(shù)據(jù)類型，可使用用戶定義數(shù)據(jù)類型n創(chuàng)建用戶定義的數(shù)據(jù)類型創(chuàng)建用戶定義的數(shù)據(jù)類型n企業(yè)管理器企業(yè)管理器n系統(tǒng)存儲過程系統(tǒng)存儲過程nsp_addtype 類型名類型名,系統(tǒng)數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型 ,NULL|NOT NULL ,擁有者擁有者n刪除用戶定義的數(shù)據(jù)類型刪除用戶定義的數(shù)據(jù)類型nsp_droptype 類型名類型名 選擇數(shù)據(jù)類型的指導(dǎo)原則選擇數(shù)據(jù)類型的指導(dǎo)原則n若列值的長度相差很大，字符一般使用變長數(shù)據(jù)類型，數(shù)字和若列值的長度相差很大，字符一般使用變長數(shù)據(jù)類型，數(shù)字和日期類型考慮實際情況

8、日期類型考慮實際情況n例如某列存儲的是人名，地址等例如某列存儲的是人名，地址等n謹慎使用謹慎使用 tinyint 數(shù)據(jù)類型數(shù)據(jù)類型0-255）n雖然節(jié)省空間，但擴展性很小雖然節(jié)省空間，但擴展性很小n對于小數(shù)數(shù)據(jù)來說，一般使用對于小數(shù)數(shù)據(jù)來說，一般使用 decimal 數(shù)據(jù)類型數(shù)據(jù)類型n可以精確地控制精度可以精確地控制精度(1-38)n如果行的存儲量如果行的存儲量 超過超過8 000字節(jié)，運用字節(jié)，運用 text 或者或者 imagen若不大于若不大于8 000字節(jié)，可使用字節(jié)，可使用char、varchar或者或者binary數(shù)據(jù)類型數(shù)據(jù)類型n對于貨幣數(shù)據(jù)，運用對于貨幣數(shù)據(jù)，運用 money

9、數(shù)據(jù)類型數(shù)據(jù)類型n不要使用類型為不要使用類型為 float 或者或者 real 的列作為主鍵的列作為主鍵n因為它們不精確，所以不適合用于比較因為它們不精確，所以不適合用于比較3.1.3 選擇數(shù)據(jù)類型的指導(dǎo)原則選擇數(shù)據(jù)類型的指導(dǎo)原則創(chuàng)建表創(chuàng)建表nSQL Server 在行中組織數(shù)據(jù)的方式在行中組織數(shù)據(jù)的方式nSQL Server 組織組織 text、ntext 和和 image 數(shù)據(jù)的方式數(shù)據(jù)的方式n創(chuàng)建和刪除表創(chuàng)建和刪除表n添加和刪除列添加和刪除列3.2 創(chuàng)建表創(chuàng)建表SQL Server 在行中組織數(shù)據(jù)的方式在行中組織數(shù)據(jù)的方式行首行首定長數(shù)據(jù)定長數(shù)據(jù)NBVB變長數(shù)據(jù)變長數(shù)據(jù)空值塊空值塊變長值

10、塊變長值塊4字節(jié)數(shù)據(jù)部分n數(shù)據(jù)行由行首和數(shù)據(jù)部分組成數(shù)據(jù)行由行首和數(shù)據(jù)部分組成n行首：四個字節(jié)，包括了數(shù)據(jù)行中每列的信息行首：四個字節(jié)，包括了數(shù)據(jù)行中每列的信息n數(shù)據(jù)部分數(shù)據(jù)部分n定長數(shù)據(jù)：存放定長數(shù)據(jù)類型的列的數(shù)據(jù)定長數(shù)據(jù)：存放定長數(shù)據(jù)類型的列的數(shù)據(jù)n空值塊：標(biāo)示值為空的列空值塊：標(biāo)示值為空的列n變長值塊：標(biāo)示值為變長數(shù)據(jù)的列的信息以及存放變長值塊：標(biāo)示值為變長數(shù)據(jù)的列的信息以及存放位置位置n變長數(shù)據(jù)：存放變長數(shù)據(jù)類型的列的數(shù)據(jù)變長數(shù)據(jù)：存放變長數(shù)據(jù)類型的列的數(shù)據(jù)3.2.1 SQL Server 在行中組織數(shù)據(jù)的方式在行中組織數(shù)據(jù)的方式SQL Server 組織組織 text、ntext 和

11、和 image 數(shù)據(jù)的方式數(shù)據(jù)的方式nText、ntext 和和 image 一般是存儲在數(shù)據(jù)行之外的一般是存儲在數(shù)據(jù)行之外的n因為它們一般都比較大因為它們一般都比較大nText、ntext 和和 image 的存儲結(jié)構(gòu)的存儲結(jié)構(gòu)(建表時類型后長度建表時類型后長度是是16)n在數(shù)據(jù)行內(nèi)存放一個在數(shù)據(jù)行內(nèi)存放一個16字節(jié)的指針，指向一個根結(jié)字節(jié)的指針，指向一個根結(jié)構(gòu)構(gòu)n根結(jié)構(gòu)組成了一棵根結(jié)構(gòu)組成了一棵B樹的根節(jié)點，樹的根節(jié)點，B樹的葉節(jié)點指向樹的葉節(jié)點指向?qū)嶋H存放數(shù)據(jù)的數(shù)據(jù)塊實際存放數(shù)據(jù)的數(shù)據(jù)塊n若數(shù)據(jù)大于若數(shù)據(jù)大于32 KB，在根節(jié)點和數(shù)據(jù)塊之間添加中間，在根節(jié)點和數(shù)據(jù)塊之間添加中間節(jié)點節(jié)點n

12、將大對象數(shù)據(jù)類型的數(shù)據(jù)存儲在數(shù)據(jù)行中將大對象數(shù)據(jù)類型的數(shù)據(jù)存儲在數(shù)據(jù)行中n避免了多次查找數(shù)據(jù)塊位置，可提高性能避免了多次查找數(shù)據(jù)塊位置，可提高性能n設(shè)置表的設(shè)置表的 text in row 選項選項n查看表的查看表的 text in row 選項：選項：objectproperty 函數(shù)函數(shù)3.2.2 SQL Server 組織組織 text、ntext 和和 image 數(shù)據(jù)的方式數(shù)據(jù)的方式sp_tableoption TableNamePattern = table , OptionName = option_name , OptionValue = valueSQL Server 組織組

13、織 text、ntext 和和 image 數(shù)據(jù)的方式續(xù)）數(shù)據(jù)的方式續(xù)）數(shù)據(jù)行根結(jié)構(gòu)根結(jié)構(gòu)中間節(jié)點中間節(jié)點中間節(jié)點中間節(jié)點塊塊 1塊塊 2塊塊 1塊塊 23.2.2 SQL Server 組織組織 text、ntext 和和 image 數(shù)據(jù)的方式數(shù)據(jù)的方式n創(chuàng)建表創(chuàng)建表n命名表和列：數(shù)據(jù)庫名命名表和列：數(shù)據(jù)庫名.擁有者擁有者.表名表名n 數(shù)據(jù)庫名默認為當(dāng)前的工作數(shù)據(jù)庫數(shù)據(jù)庫名默認為當(dāng)前的工作數(shù)據(jù)庫n 擁有者默認為當(dāng)前用戶或者數(shù)據(jù)庫的擁有者擁有者默認為當(dāng)前用戶或者數(shù)據(jù)庫的擁有者n指定合適的數(shù)據(jù)類型和長度指定合適的數(shù)據(jù)類型和長度n指定指定 NULL 或者或者 NOT NULLn 確定列值是否可為

14、空確定列值是否可為空n 默認按照會話或者數(shù)據(jù)庫的默認值設(shè)定默認按照會話或者數(shù)據(jù)庫的默認值設(shè)定n列的排序規(guī)則列的排序規(guī)則n 可為同一表中的各個列指定不同的排序規(guī)則可為同一表中的各個列指定不同的排序規(guī)則n計算列計算列n 是一個虛的列，并不物理存放在表中是一個虛的列，并不物理存放在表中n 當(dāng)取列值的時候，當(dāng)取列值的時候，SQL Server 根據(jù)其他列的值和一根據(jù)其他列的值和一個公個公 式計算出列值式計算出列值創(chuàng)建和刪除表創(chuàng)建和刪除表3.2.3 創(chuàng)建和刪除表創(chuàng)建和刪除表create table a(a int,b int,c as a+b )insert into a(a,b) values(1,1

15、)select * from aCreate table 語法語法CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type COLLATE DEFAULT constant

16、_expression | IDENTITY ( seed , increment ) NOT FOR REPLICATION ROWGUIDCOL .n create table Produce(Pro_id int,Name char(10),Price decimal,qty int,cost as Price*qty)n := CONSTRAINT constraint_name NULL | NOT NULL | PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegrou

17、p | DEFAULT | FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_expression ) n := CONSTRAINT constraint_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ASC | DESC ,.n ) WIT

18、H FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column ,.n ) REFERENCES ref_table ( ref_column ,.n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_conditions )n分隔標(biāo)識符分隔標(biāo)識符n命名規(guī)則：保留字，如命名規(guī)則：保留字，如 table、create、select

19、等，等，不能作為對象標(biāo)識符不能作為對象標(biāo)識符n不符合標(biāo)識符格式規(guī)則的標(biāo)識符必須使用分隔符不符合標(biāo)識符格式規(guī)則的標(biāo)識符必須使用分隔符n兩種分隔標(biāo)識符兩種分隔標(biāo)識符n 括在括號中的標(biāo)識符：用方括號分隔括在括號中的標(biāo)識符：用方括號分隔n 被引用的標(biāo)識符：用雙引號分隔被引用的標(biāo)識符：用雙引號分隔n僅當(dāng)僅當(dāng) QUOTED_IDENTIFIER 選項設(shè)置為選項設(shè)置為 ON 時，被引時，被引用的標(biāo)識符才有效用的標(biāo)識符才有效nSET QUOTED_IDENTIFIER ON n刪除表刪除表nDROP TABLE 表名表名 ,n 創(chuàng)建和刪除表續(xù)）創(chuàng)建和刪除表續(xù)）3.2.3 創(chuàng)建和刪除表創(chuàng)建和刪除表修改表語法修改

20、表語法ALTER TABLE table ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD | DROP ROWGUIDCOL | ADD | column_name AS computed_column_expression ,.n | WITH CHECK | WITH NOCHECK ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN column ,.n | CHECK | NOCHECK CONSTRAI

21、NT ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER ALL | trigger_name ,.n 添加和刪除列添加和刪除列3.2.4 添加和刪除列添加和刪除列n添加列添加列n語法：語法：ALTER TABLE 表名表名 ADD 列名列名 數(shù)據(jù)類型數(shù)據(jù)類型 NULL | NOT NULLn添加列時的各個選項和創(chuàng)建表時的列選項相同添加列時的各個選項和創(chuàng)建表時的列選項相同n向表中添加新列時，向表中添加新列時，SQL Server 在列中為表中每個現(xiàn)在列中為表中每個現(xiàn)有的數(shù)據(jù)行插入一個值。因此，在向表中添加列時有的數(shù)據(jù)行插入一個值。因此，在向

22、表中添加列時向列添加向列添加 DEFAULT 定義會很有用定義會很有用n如果新列沒有如果新列沒有 DEFAULT 定義，則必須指定它允許空定義，則必須指定它允許空值值n如果新列不允許空值，那么如果新列不允許空值，那么 SQL Server 向其中插入空向其中插入空值時將返回錯誤值時將返回錯誤添加和刪除列續(xù)）添加和刪除列續(xù)）3.2.4 添加和刪除列添加和刪除列n刪除列刪除列n語法：語法：ALTER TABLE 表名表名 DROP 列名列名 ,nn不能刪除以下列不能刪除以下列n 正在復(fù)制的列正在復(fù)制的列n 用在索引中的列用在索引中的列n 用在用在 CHECK、FOREIGN KEY、UNIQUE

23、或或 PRIMARY KEY 約束中的列約束中的列n 與與 DEFAULT 定義關(guān)聯(lián)或綁定到某一默認對象的列定義關(guān)聯(lián)或綁定到某一默認對象的列n 綁定到規(guī)則的列綁定到規(guī)則的列課堂練習(xí)課堂練習(xí) 添加列添加列n添加一個數(shù)值列添加一個數(shù)值列n添加列描述添加列描述n設(shè)置精度和小數(shù)位數(shù)設(shè)置精度和小數(shù)位數(shù)n設(shè)置列為標(biāo)識列設(shè)置列為標(biāo)識列n添加一個添加一個 GUID 列列n添加一個日期列添加一個日期列n添加一個字符列添加一個字符列n保存并關(guān)閉表設(shè)計器保存并關(guān)閉表設(shè)計器生成列值生成列值n運用運用 Identity 屬性屬性n運用運用 NEWID 函數(shù)和函數(shù)和 uniqueidentifier 數(shù)據(jù)類型數(shù)據(jù)類型3.

24、3 生成列值生成列值運用運用 Identity 屬性屬性n運用運用 Identity 屬性的要求屬性的要求n每個表只能有一個標(biāo)識列每個表只能有一個標(biāo)識列n只用在只用在 integer、numeric 和和 numeric(decimal)數(shù)據(jù)類型數(shù)據(jù)類型上。若用于上。若用于 numeric(decimal)，小數(shù)位數(shù)必須為，小數(shù)位數(shù)必須為0n標(biāo)識列不能進行更新操作標(biāo)識列不能進行更新操作n標(biāo)識列不允許空值標(biāo)識列不允許空值n在查詢中，可以用關(guān)鍵字在查詢中，可以用關(guān)鍵字 IDENTITYCOL 來代表一個來代表一個表中的標(biāo)識列表中的標(biāo)識列n使得不必指明標(biāo)識列的列名，增加了靈活性使得不必指明標(biāo)識列的列

25、名，增加了靈活性3.3.1 運用運用 Identity 屬性屬性運用運用 Identity 屬性續(xù)）屬性續(xù)）n檢索檢索 Identity 屬性的信息屬性的信息n使用函數(shù)使用函數(shù) IDENT_SEED 和和 IDENT_INCR 獲得定義信息獲得定義信息n使用全局變量使用全局變量 identity 獲得當(dāng)前會話的所有作用域中的任何表最獲得當(dāng)前會話的所有作用域中的任何表最后生成的標(biāo)識值后生成的標(biāo)識值n使用函數(shù)使用函數(shù) SCOPE_IDENTITY 返回當(dāng)前會話和當(dāng)前作用域中的任何返回當(dāng)前會話和當(dāng)前作用域中的任何表最后生成的標(biāo)識值表最后生成的標(biāo)識值n 一個作用域就是一個模塊一個作用域就是一個模塊存儲

26、過程、觸發(fā)器、函數(shù)或批處存儲過程、觸發(fā)器、函數(shù)或批處理。因此，如果兩個語句處于同一個存儲過程、函數(shù)或批處理理。因此，如果兩個語句處于同一個存儲過程、函數(shù)或批處理中，則它們位于相同的作用域中中，則它們位于相同的作用域中n使用函數(shù)使用函數(shù) IDENT_CURRENT 返回任何會話和任何作用域中的特定返回任何會話和任何作用域中的特定表最后生成的標(biāo)識值表最后生成的標(biāo)識值n管理管理 Identity 屬性屬性n設(shè)置設(shè)置 IDENTITY_INSERT 為為 ON，可在，可在 INSERT 中為標(biāo)識列顯式地中為標(biāo)識列顯式地插入用戶提供的值插入用戶提供的值n運用運用 DBCC CHECKIDENT 檢查當(dāng)前標(biāo)識值是否越界檢查當(dāng)前標(biāo)識值是否越界3.3.1 運用運用 Identity 屬性屬性運用運用 NEWID 函數(shù)和函數(shù)和 uniqueidentifier 數(shù)據(jù)類型數(shù)據(jù)類型n作用：確保作用：確保 ID 的全局惟一性的全局惟一性nuniqueidentifier 數(shù)據(jù)類型：存儲數(shù)據(jù)類型：存儲 GUIDnNEWID 函數(shù)：產(chǎn)生一個函數(shù)：產(chǎn)生一個 GUIDn兩者常常和兩者常常和 DEFAULT 約束配合使用