SQL創(chuàng)建數(shù)據(jù)類型和表

1、數(shù)據(jù)庫程序設(shè)計 SQL Server 2000 數(shù)據(jù)庫程序設(shè)計第1章 SQL Server 概述第2章 創(chuàng)建和管理數(shù)據(jù)庫第3章 創(chuàng)建數(shù)據(jù)類型和表第4章 實現(xiàn)數(shù)據(jù)完整性第5章 Transact-SQL介紹第6章 使用 Transact-SQL第7章 檢索數(shù)據(jù)第8章 數(shù)據(jù)分組與匯總第9章 多表聯(lián)接第10章 子查詢第11章 修改數(shù)據(jù)第12章 全文索引查詢 第13章 規(guī)劃索引第14章 創(chuàng)建和維護索引第15章 實現(xiàn)視圖第16章 實現(xiàn)存儲過程第17章 實現(xiàn)用戶定義函數(shù)第18章 實現(xiàn)觸發(fā)器第19章 多服務(wù)器編程第20章 優(yōu)化查詢性能第21章 分析查詢第22章 管理事務(wù)和鎖第3章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型

2、創(chuàng)建表生成列值生成腳本推薦操作創(chuàng)建數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型選擇數(shù)據(jù)類型的指導(dǎo)原則3.1 創(chuàng)建數(shù)據(jù)類型系統(tǒng)提供的數(shù)據(jù)類型數(shù)字數(shù)據(jù)整型數(shù)據(jù)：存儲整數(shù)小數(shù)數(shù)據(jù)：包含存儲在最小有效數(shù)上的數(shù)據(jù)3.1.1 系統(tǒng)提供的數(shù)據(jù)類型bigint占8個字節(jié)，值的范圍為 -263263-1int占4個字節(jié)，值的范圍為 -231231-1smallint占2個字節(jié)，值的范圍為 -3276832 767tinyint占1個字節(jié)，值的范圍為 0255decimal (p,s)p為精度，最大38；s為小數(shù)位數(shù)，0spnumeric (p,s)在SQL Server中，等價于decimal系統(tǒng)提供

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

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

5、數(shù)為 01GB系統(tǒng)提供的數(shù)據(jù)類型（續(xù)）二進制數(shù)據(jù)其他3.1.1 系統(tǒng)提供的數(shù)據(jù)類型binary(n) 存儲字節(jié)個數(shù) 08 000varbinary(n) 存儲字節(jié)個數(shù) 08 000image存儲字節(jié)個數(shù) 02 Gbit存儲位數(shù)據(jù)cursor存儲對游標(biāo)的引用rowversion (timestamp)時間戳sql_variant可存儲除 text、ntext、image、rowversion 之外的其他類型table存儲函數(shù)返回結(jié)果uniqueidentifier存儲 GUID 以及 UUID創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型3.1.2 創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型用戶定義的數(shù)據(jù)類型當(dāng)多個表的列中要存

6、儲同樣類型的數(shù)據(jù)，且想確保這些列具有完全相同的數(shù)據(jù)類型、長度和為空性時，可使用用戶定義數(shù)據(jù)類型創(chuàng)建用戶定義的數(shù)據(jù)類型企業(yè)管理器系統(tǒng)存儲過程sp_addtype 類型名,系統(tǒng)數(shù)據(jù)類型 ,NULL|NOT NULL ,擁有者刪除用戶定義的數(shù)據(jù)類型sp_droptype 類型名 選擇數(shù)據(jù)類型的指導(dǎo)原則若列值的長度相差很大，那么使用變長數(shù)據(jù)類型例如某列存儲的是人名，地址等謹慎使用 tinyint 數(shù)據(jù)類型雖然節(jié)省空間，但擴展性很小對于小數(shù)數(shù)據(jù)來說，一般使用 decimal 數(shù)據(jù)類型可以精確地控制精度如果行的存儲量 超過8 000字節(jié)，使用 text 或者 image若不大于8 000字節(jié)，可使用cha

7、r、varchar或者binary數(shù)據(jù)類型對于貨幣數(shù)據(jù)，使用 money 數(shù)據(jù)類型不要使用類型為 float 或者 real 的列作為主鍵因為它們不精確，所以不適合用于比較3.1.3 選擇數(shù)據(jù)類型的指導(dǎo)原則第3章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型創(chuàng)建表生成列值生成腳本推薦操作創(chuàng)建表SQL Server 在行中組織數(shù)據(jù)的方式SQL Server 組織 text、ntext 和 image 數(shù)據(jù)的方式創(chuàng)建和刪除表添加和刪除列3.2 創(chuàng)建表SQL Server 在行中組織數(shù)據(jù)的方式行首定長數(shù)據(jù)NBVB變長數(shù)據(jù)空值塊變長值塊4字節(jié)數(shù)據(jù)部分數(shù)據(jù)行由行首和數(shù)據(jù)部分組成行首：四個字節(jié)，包括了數(shù)據(jù)行中每列的信息數(shù)

8、據(jù)部分定長數(shù)據(jù)：存放定長數(shù)據(jù)類型的列的數(shù)據(jù)空值塊：標(biāo)示值為空的列變長值塊：標(biāo)示值為變長數(shù)據(jù)的列的信息以及存放位置變長數(shù)據(jù)：存放變長數(shù)據(jù)類型的列的數(shù)據(jù)3.2.1 SQL Server 在行中組織數(shù)據(jù)的方式SQL Server 組織 text、ntext 和 image 數(shù)據(jù)的方式Text、ntext 和 image 一般是存儲在數(shù)據(jù)行之外的因為它們一般都比較大Text、ntext 和 image 的存儲結(jié)構(gòu)在數(shù)據(jù)行內(nèi)存放一個16字節(jié)的指針，指向一個根結(jié)構(gòu)根結(jié)構(gòu)組成了一棵B樹的根節(jié)點，B樹的葉節(jié)點指向?qū)嶋H存放數(shù)據(jù)的數(shù)據(jù)塊若數(shù)據(jù)大于32 KB，在根節(jié)點和數(shù)據(jù)塊之間添加中間節(jié)點將大對象數(shù)據(jù)類型的數(shù)據(jù)存

9、儲在數(shù)據(jù)行中避免了多次查找數(shù)據(jù)塊位置，可提高性能設(shè)置表的 text in row 選項查看表的 text in row 選項：objectproperty 函數(shù)3.2.2 SQL Server 組織 text、ntext 和 image 數(shù)據(jù)的方式SQL Server 組織 text、ntext 和 image 數(shù)據(jù)的方式（續(xù)）數(shù)據(jù)行text指針根結(jié)構(gòu)中間節(jié)點中間節(jié)點塊 1塊 2塊 1塊 23.2.2 SQL Server 組織 text、ntext 和 image 數(shù)據(jù)的方式創(chuàng)建表命名表和列：數(shù)據(jù)庫名.擁有者.表名 數(shù)據(jù)庫名默認為當(dāng)前的工作數(shù)據(jù)庫 擁有者默認為當(dāng)前用戶或者數(shù)據(jù)庫的擁有者指定

10、NULL 或者 NOT NULL 確定列值是否可為空 默認按照會話或者數(shù)據(jù)庫的默認值設(shè)定列的排序規(guī)則 可為同一表中的各個列指定不同的排序規(guī)則計算列 是一個虛的列，并不物理存放在表中 當(dāng)取列值的時候，SQL Server 根據(jù)其他列的值和一個公 式計算出列值創(chuàng)建和刪除表3.2.3 創(chuàng)建和刪除表分隔標(biāo)識符命名規(guī)則：保留字，如 table、create、select 等，不能作為對象標(biāo)識符不符合標(biāo)識符格式規(guī)則的標(biāo)識符必須使用分隔符兩種分隔標(biāo)識符 括在括號中的標(biāo)識符：用方括號分隔 被引用的標(biāo)識符：用雙引號分隔僅當(dāng) QUOTED_IDENTIFIER 選項設(shè)置為 ON 時，被引用的標(biāo)識符才有效SET Q

11、UOTED_IDENTIFIER ON 刪除表DROP TABLE 表名 ,n 創(chuàng)建和刪除表（續(xù)）3.2.3 創(chuàng)建和刪除表添加和刪除列3.2.4 添加和刪除列添加列語法：ALTER TABLE 表名 ADD 列名 數(shù)據(jù)類型 NULL | NOT NULL添加列時的各個選項和創(chuàng)建表時的列選項相同向表中添加新列時，SQL Server 在列中為表中每個現(xiàn)有的數(shù)據(jù)行插入一個值。因此，在向表中添加列時向列添加 DEFAULT 定義會很有用如果新列沒有 DEFAULT 定義，則必須指定它允許空值如果新列不允許空值，則 SQL Server 向其中插入空值時將返回錯誤添加和刪除列（續(xù)）3.2.4 添加和刪

12、除列刪除列語法：ALTER TABLE 表名 DROP 列名 ,n不能刪除以下列 正在復(fù)制的列 用在索引中的列 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 約束中的列 與 DEFAULT 定義關(guān)聯(lián)或綁定到某一默認對象的列 綁定到規(guī)則的列課堂練習(xí) 添加列添加一個數(shù)值列添加列描述設(shè)置精度和小數(shù)位數(shù)設(shè)置列為標(biāo)識列添加一個 GUID 列添加一個日期列添加一個字符列保存并關(guān)閉表設(shè)計器第3章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型創(chuàng)建表生成列值生成腳本推薦操作生成列值使用 Identity 屬性使用 NEWID 函數(shù)和 uniqueidentifier 數(shù)據(jù)類型3.3 生成列

13、值使用 Identity 屬性使用 Identity 屬性的要求每個表只能有一個標(biāo)識列只用在 integer、numeric 和 numeric(decimal)數(shù)據(jù)類型上。若用于 numeric(decimal)，小數(shù)位數(shù)必須為0標(biāo)識列不能進行更新操作標(biāo)識列不允許空值在查詢中，可以用關(guān)鍵字 IDENTITYCOL 來代表一個表中的標(biāo)識列使得不必指明標(biāo)識列的列名，增加了靈活性3.3.1 使用 Identity 屬性使用 Identity 屬性（續(xù)）檢索 Identity 屬性的信息使用函數(shù) IDENT_SEED 和 IDENT_INCR 獲得定義信息使用全局變量 identity 獲得當(dāng)前會話

14、的所有作用域中的任何表最后生成的標(biāo)識值使用函數(shù) SCOPE_IDENTITY 返回當(dāng)前會話和當(dāng)前作用域中的任何表最后生成的標(biāo)識值 一個作用域就是一個模塊存儲過程、觸發(fā)器、函數(shù)或批處理。因此，如果兩個語句處于同一個存儲過程、函數(shù)或批處理中，則它們位于相同的作用域中使用函數(shù) IDENT_CURRENT 返回任何會話和任何作用域中的特定表最后生成的標(biāo)識值管理 Identity 屬性設(shè)置 IDENTITY_INSERT 為 ON，可在 INSERT 中為標(biāo)識列顯式地插入用戶提供的值使用 DBCC CHECKIDENT 檢查當(dāng)前標(biāo)識值是否越界3.3.1 使用 Identity 屬性使用 NEWID 函數(shù)

15、和 uniqueidentifier 數(shù)據(jù)類型作用：確保 ID 的全局惟一性uniqueidentifier 數(shù)據(jù)類型：存儲 GUIDNEWID 函數(shù)：產(chǎn)生一個 GUID兩者常常和 DEFAULT 約束配合使用 CREATE TABLE Customer (CustID uniqueidentifier NOT NULL DEFAULT NEWID(), CustName char(30) NOT NULL)3.3.2 使用 NEWID 函數(shù)和 uniqueidentifier 數(shù)據(jù)類型第3章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型創(chuàng)建表生成列值生成腳本推薦操作生成腳本使用企業(yè)管理器將模式生成 Transact-SQL 腳本維護備份腳本創(chuàng)建或者更新數(shù)據(jù)庫開發(fā)腳本建立測試或開發(fā)環(huán)境訓(xùn)練新員工可以生成整個數(shù)據(jù)庫的一個創(chuàng)建腳本文件一個或多個表的若干個創(chuàng)建腳本文件表、索引以及存儲過程等各個對象的腳本，可以分別存放在不同腳本文件里3.4 生成腳本第3章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型創(chuàng)建表生成列值生成腳本推薦操作推薦操作在 CREATE TABLE 語句中總是指定列