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

1、數(shù)據(jù)庫(kù)程序設(shè)計(jì) SQL Server 2000 數(shù)據(jù)庫(kù)程序設(shè)計(jì)第1章 SQL Server 概述第2章 創(chuàng)建和管理數(shù)據(jù)庫(kù)第3章 創(chuàng)建數(shù)據(jù)類型和表第4章 實(shí)現(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)建和維護(hù)索引第15章 實(shí)現(xiàn)視圖第16章 實(shí)現(xiàn)存儲(chǔ)過(guò)程第17章 實(shí)現(xiàn)用戶定義函數(shù)第18章 實(shí)現(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ù)字?jǐn)?shù)據(jù)整型數(shù)據(jù)：存儲(chǔ)整數(shù)小數(shù)數(shù)據(jù)：包含存儲(chǔ)在最小有效數(shù)上的數(shù)據(jù)3.1.1 系統(tǒng)提供的數(shù)據(jù)類型bigint占8個(gè)字節(jié)，值的范圍為 -263263-1int占4個(gè)字節(jié)，值的范圍為 -231231-1smallint占2個(gè)字節(jié)，值的范圍為 -3276832 767tinyint占1個(gè)字節(jié)，值的范圍為 0255decimal (p,s)p為精度，最大38；s為小數(shù)位數(shù)，0spnumeric (p,s)在SQL Server中，等價(jià)于decimal系統(tǒng)提供

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

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

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

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

7、r、varchar或者binary數(shù)據(jù)類型對(duì)于貨幣數(shù)據(jù)，使用 money 數(shù)據(jù)類型不要使用類型為 float 或者 real 的列作為主鍵因?yàn)樗鼈儾痪_，所以不適合用于比較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ù)的方式行首定長(zhǎng)數(shù)據(jù)NBVB變長(zhǎng)數(shù)據(jù)空值塊變長(zhǎng)值塊4字節(jié)數(shù)據(jù)部分?jǐn)?shù)據(jù)行由行首和數(shù)據(jù)部分組成行首：四個(gè)字節(jié)，包括了數(shù)據(jù)行中每列的信息數(shù)

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

9、儲(chǔ)在數(shù)據(jù)行中避免了多次查找數(shù)據(jù)塊位置，可提高性能設(shè)置表的 text in row 選項(xiàng)查看表的 text in row 選項(xiàng)：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é)點(diǎn)中間節(jié)點(diǎn)塊 1塊 2塊 1塊 23.2.2 SQL Server 組織 text、ntext 和 image 數(shù)據(jù)的方式創(chuàng)建表命名表和列：數(shù)據(jù)庫(kù)名.擁有者.表名 數(shù)據(jù)庫(kù)名默認(rèn)為當(dāng)前的工作數(shù)據(jù)庫(kù) 擁有者默認(rèn)為當(dāng)前用戶或者數(shù)據(jù)庫(kù)的擁有者指定

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

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

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

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

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

15、和 uniqueidentifier 數(shù)據(jù)類型作用：確保 ID 的全局惟一性u(píng)niqueidentifier 數(shù)據(jù)類型：存儲(chǔ) GUIDNEWID 函數(shù)：產(chǎn)生一個(gè) 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 腳本維護(hù)備份腳本創(chuàng)建或者更新數(shù)據(jù)庫(kù)開發(fā)腳本建立測(cè)試或開發(fā)環(huán)境訓(xùn)練新員工可以生成整個(gè)數(shù)據(jù)庫(kù)的一個(gè)創(chuàng)建腳本文件一個(gè)或多個(gè)表的若干個(gè)創(chuàng)建腳本文件表、索引以及存儲(chǔ)過(guò)程等各個(gè)對(duì)象的腳本，可以分別存放在不同腳本文件里3.4 生成腳本第3章 創(chuàng)建數(shù)據(jù)類型和表創(chuàng)建數(shù)據(jù)類型創(chuàng)建表生成列值生成腳本推薦操作推薦操作在 CREATE TABLE 語(yǔ)句中總是指定列