SQL Server教程（第6版）(2008版) 課件 第3章 表的創(chuàng)建和操作

第3章

表的創(chuàng)建和操作——表結(jié)構(gòu)和數(shù)據(jù)類(lèi)型01表和表結(jié)構(gòu)表和表結(jié)構(gòu)每個(gè)數(shù)據(jù)庫(kù)包含了若干個(gè)表。表是SQLServer中最主要的數(shù)據(jù)庫(kù)對(duì)象，它是用來(lái)存儲(chǔ)數(shù)據(jù)的一種邏輯結(jié)構(gòu)。表由行和列組成，因此也稱(chēng)為二維表。表是在日常工作和生活中經(jīng)常使用的一種表示數(shù)據(jù)及其關(guān)系的形式，表就是用來(lái)表示學(xué)生情況的一個(gè)“學(xué)生”表。學(xué)

號(hào)姓名性別出生日期專(zhuān)

業(yè)總學(xué)分備

注221101王林男2004-02-10計(jì)算機(jī)15

221102程明男2005-02-01計(jì)算機(jī)15

221103王燕女2003-10-06計(jì)算機(jī)15參加校女子足球隊(duì)201103嚴(yán)紅女2002-08-11計(jì)算機(jī)60

221201劉華男2004-06-10通信工程13輔修計(jì)算機(jī)專(zhuān)業(yè)221202王林男2004-01-29通信工程13

表和表結(jié)構(gòu)下面簡(jiǎn)單介紹與表有關(guān)的幾個(gè)概念。（1）表結(jié)構(gòu)。組成表的各列的名稱(chēng)及數(shù)據(jù)類(lèi)型，統(tǒng)稱(chēng)為表結(jié)構(gòu)。（2）記錄（行）。每個(gè)表包含了若干行數(shù)據(jù)，它們是表的“值”，表中的一行稱(chēng)為一個(gè)記錄。因此，表是記錄的有限集合。（3）列（字段）。每個(gè)記錄由若干個(gè)數(shù)據(jù)項(xiàng)構(gòu)成，將構(gòu)成記錄的每個(gè)數(shù)據(jù)項(xiàng)稱(chēng)為字段，又直接稱(chēng)列。例如，表3.1中的表結(jié)構(gòu)為（學(xué)號(hào)，姓名，性別，出生日期，專(zhuān)業(yè)，總學(xué)分，備注），包含7個(gè)列，由6個(gè)記錄組成。（4）空值?？罩担∟ULL）通常表示未知、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)。若一個(gè)列允許為空值，則向表中輸入記錄值時(shí)可不為該列指定具體值；而一個(gè)列若不允許為空值，則在輸入時(shí)必須給出具體值。（5）關(guān)鍵字。若表中記錄的某一列或列組合能唯一標(biāo)識(shí)記錄，則稱(chēng)該列或列組合為候選關(guān)鍵字（Candidatekey）。若一個(gè)表有多個(gè)候選關(guān)鍵字，則選定其中一個(gè)為主關(guān)鍵字（Primarykey），也稱(chēng)為主鍵。當(dāng)一個(gè)表僅有唯一的一個(gè)候選關(guān)鍵字時(shí)，該候選關(guān)鍵字就是主關(guān)鍵字。02數(shù)據(jù)類(lèi)型整數(shù)型精確數(shù)值型浮點(diǎn)型貨幣型位型字符型Unicode字符型文本型二進(jìn)制型日期時(shí)間類(lèi)型時(shí)間戳型圖像數(shù)據(jù)類(lèi)型xml類(lèi)型地理空間類(lèi)型和幾何數(shù)據(jù)類(lèi)型其他數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型列的數(shù)據(jù)類(lèi)型可以是SQLServer提供的系統(tǒng)數(shù)據(jù)類(lèi)型，也可以是用戶定義的數(shù)據(jù)類(lèi)型。SQLServer提供了豐富的數(shù)據(jù)類(lèi)型，列于表中。數(shù)據(jù)類(lèi)型符號(hào)標(biāo)識(shí)整數(shù)型bigint,int,smallint,tinyint精確數(shù)值型decimal,numeric浮點(diǎn)型float,real貨幣型money,smallmoney位型bit字符型char,varchar、varchar(MAX)Unicode字符型nchar,nvarchar、nvarchar(MAX)文本型text,ntext二進(jìn)制型binary,varbinary、varbinary(MAX)日期時(shí)間類(lèi)型datetime,smalldatetime,date,time,datetime2,datetimeoffset時(shí)間戳型timestamp圖像型image其他sql_variant,uniqueidentifier,xml,hierarchyid,geometry數(shù)據(jù)類(lèi)型在討論數(shù)據(jù)類(lèi)型時(shí)，使用了精度、小數(shù)位數(shù)和長(zhǎng)度3個(gè)概念，前兩個(gè)概念是針對(duì)數(shù)值型數(shù)據(jù)的，它們的含義是：精度：指數(shù)值數(shù)據(jù)中所存儲(chǔ)的十進(jìn)制數(shù)據(jù)的總位數(shù)。小數(shù)位數(shù)：指數(shù)值數(shù)據(jù)中小數(shù)點(diǎn)右邊可以有的數(shù)字位數(shù)的最大值。例如，數(shù)值數(shù)據(jù)3890.587的精度是7，小數(shù)位數(shù)是3。長(zhǎng)度：指存儲(chǔ)數(shù)據(jù)所使用的字節(jié)數(shù)。1．整數(shù)型整數(shù)型包括bigint、int、smallint和tinyint，從標(biāo)識(shí)符的含義就可以看出，它們表示數(shù)的范圍逐漸縮小。（1）bigint：大整數(shù)，范圍為

263～263

1，其精度為19，小數(shù)位數(shù)為0，長(zhǎng)度為8字節(jié)。（2）int：整數(shù)，范圍為

231～231

1，其精度為10，小數(shù)位數(shù)為0，長(zhǎng)度為4字節(jié)。（3）smallint：短整數(shù)，范圍為

215～215

1，其精度為5，小數(shù)位數(shù)為0，長(zhǎng)度為2字節(jié)。（4）tinyint：微短整數(shù)，范圍為0～255，長(zhǎng)度為1字節(jié)，其精度為3，小數(shù)位數(shù)為0，長(zhǎng)度為1字節(jié)。數(shù)據(jù)類(lèi)型2．精確數(shù)值型精確數(shù)值型數(shù)據(jù)由整數(shù)部分和小數(shù)部分構(gòu)成，其所有的數(shù)字都是有效位，能夠以完整的精度存儲(chǔ)十進(jìn)制數(shù)。精確數(shù)值型包括decimal和numeric兩類(lèi)。在SQLServer中，這兩種數(shù)據(jù)類(lèi)型在功能上完全等價(jià)。聲明精確數(shù)值型數(shù)據(jù)的格式是numeric|decimal(p[,s])，其中p為精度，s為小數(shù)位數(shù)，s的默認(rèn)值為0。例如，指定某列為精確數(shù)值型，精度為6，小數(shù)位數(shù)為3，即decimal(6,3)，那么當(dāng)向某記錄的該列賦值56.342689時(shí)，該列實(shí)際存儲(chǔ)的是56.343。decimal和numeric可存儲(chǔ)–1038+1～1038–1的固定精度和小數(shù)位的數(shù)字?jǐn)?shù)據(jù)，它們的存儲(chǔ)長(zhǎng)度隨精度變化而變化，最少為5字節(jié)，最多為17字節(jié)。數(shù)據(jù)類(lèi)型3．浮點(diǎn)型有兩種近似數(shù)值數(shù)據(jù)類(lèi)型：float[(n)]和real，兩者通常都使用科學(xué)記數(shù)法表示數(shù)據(jù)，即形為：尾數(shù)E階數(shù)，如5.6432E20，–2.98E10，1.287659E-9等。（1）real：使用4字節(jié)存儲(chǔ)數(shù)據(jù)，表數(shù)范圍為–3.40E+38～3.40E+38，數(shù)據(jù)精度為7位有效數(shù)字。（2）float：表數(shù)范圍為–1.79E+308～1.79E+308。定義中的n取值范圍是1～53，用于指示其精度和存儲(chǔ)大小。當(dāng)n在1～24之間時(shí)，實(shí)際上將定義一個(gè)real型數(shù)據(jù)，存儲(chǔ)長(zhǎng)度為4字節(jié)，精度為7位有效數(shù)字。4．貨幣型SQLServer提供了兩個(gè)專(zhuān)門(mén)用于處理貨幣的數(shù)據(jù)類(lèi)型：money和smallmoney，它們用十進(jìn)制數(shù)表示貨幣值。（1）money：范圍為

263～263

1，其精度為19，小數(shù)位數(shù)為4，長(zhǎng)度為8字節(jié)。money的數(shù)范圍與bigint相同，不同的只是money型有4位小數(shù)。實(shí)際上，money就是按照整數(shù)進(jìn)行運(yùn)算的，只是將小數(shù)點(diǎn)固定在末尾4位。（2）smallmoney：范圍為–231～231

1，其精度為10，小數(shù)位數(shù)為4，長(zhǎng)度為4字節(jié)。數(shù)據(jù)類(lèi)型5．位型SQLServer中的位（bit）型數(shù)據(jù)相當(dāng)于其他語(yǔ)言中的邏輯型數(shù)據(jù)，它只存儲(chǔ)0和1，長(zhǎng)度為1字節(jié)。但要注意，SQLServer對(duì)表中bit類(lèi)型列的存儲(chǔ)進(jìn)行了優(yōu)化：如果一個(gè)表中有不多于8個(gè)bit列，則這些列將作為1字節(jié)存儲(chǔ)；如果表中有9～16個(gè)bit列，則這些列將作為2字節(jié)存儲(chǔ)，更多列的情況以此類(lèi)推。6．字符型SQLServer字符字符型數(shù)包括兩類(lèi)：定長(zhǎng)（char）和變長(zhǎng)（varchar）。（1）char[(n)]：定長(zhǎng)字符數(shù)據(jù)類(lèi)型，其中n定義字符型數(shù)據(jù)的長(zhǎng)度，在1～8000之間，默認(rèn)為1。當(dāng)表中的列定義為char(n)類(lèi)型時(shí)，若實(shí)際存儲(chǔ)的串長(zhǎng)度不足n時(shí)，則在串的尾部添加空格以達(dá)到長(zhǎng)度n，所以char(n)的長(zhǎng)度為n。（2）varchar[(n)]：變長(zhǎng)字符數(shù)據(jù)類(lèi)型，其中，n的規(guī)定與定長(zhǎng)字符型char中的n完全相同，但這里n表示的是字符串可達(dá)到的最大長(zhǎng)度。數(shù)據(jù)類(lèi)型7．Unicode字符型Unicode字符型包括nchar[(n)]和nvarchar[(n)]兩類(lèi)。nchar是定長(zhǎng)Unicode數(shù)據(jù)的數(shù)據(jù)類(lèi)型，nvarchar是變長(zhǎng)Unicode數(shù)據(jù)的數(shù)據(jù)類(lèi)型，二者均使用UNICODEUCS-2字符集。（1）nchar[(n)]：nchar[(n)]為包含n個(gè)字符的固定長(zhǎng)度Unicode字符型數(shù)據(jù)，n的值在1～4000之間，長(zhǎng)度為2n字節(jié)。若輸入的字符串長(zhǎng)度不足n，將以空白字符補(bǔ)足。（2）nvarchar[(n)]：nvarchar[(n)]為最多包含n個(gè)字符的可變長(zhǎng)度Unicode字符型數(shù)據(jù)，n的值在1～4000之間，默認(rèn)為1。長(zhǎng)度是所輸入字符個(gè)數(shù)的兩倍。8．文本型文本型包括text和ntext兩類(lèi)，分別對(duì)應(yīng)ASCII字符和Unicode字符。（1）text類(lèi)型：可以表示最大長(zhǎng)度為231

1個(gè)字符，其數(shù)據(jù)的存儲(chǔ)長(zhǎng)度為實(shí)際字符個(gè)數(shù)字節(jié)。（2）ntext類(lèi)型：可表示最大長(zhǎng)度為230

1個(gè)Unicode字符，其數(shù)據(jù)的存儲(chǔ)長(zhǎng)度是實(shí)際字符個(gè)數(shù)兩倍的字節(jié)。數(shù)據(jù)類(lèi)型9．二進(jìn)制型二進(jìn)制數(shù)據(jù)類(lèi)型表示的是位數(shù)據(jù)流，包括binary（固定長(zhǎng)度）和varbinary（可變長(zhǎng)度）兩種。（1）binary[(n)]：固定長(zhǎng)度的n個(gè)字節(jié)二進(jìn)制數(shù)據(jù)。n的取值范圍為1～8000，默認(rèn)為1。binary(n)數(shù)據(jù)的存儲(chǔ)長(zhǎng)度為n+4個(gè)字節(jié)。若輸入的數(shù)據(jù)長(zhǎng)度小于n，則不足部分用0填充；若輸入的數(shù)據(jù)長(zhǎng)度大于n，則多余部分被截?cái)唷＃?）varbinary[(n)]：n個(gè)字節(jié)變長(zhǎng)二進(jìn)制數(shù)據(jù)。n取值范圍為1～8000，默認(rèn)為1。varbinary(n)數(shù)據(jù)的存儲(chǔ)長(zhǎng)度為實(shí)際輸入數(shù)據(jù)長(zhǎng)度+4個(gè)字節(jié)。數(shù)據(jù)類(lèi)型10．日期時(shí)間類(lèi)型（1）datetime類(lèi)型：可表示的日期范圍從1753年1月1日～9999年12月31日，精確度為0.03s（3.33ms或0.00333s）。例如，1～3ms的值都表示為0ms，4～6ms的值都表示為4ms。datetime類(lèi)型數(shù)據(jù)長(zhǎng)度為8字節(jié)，日期和時(shí)間分別使用4個(gè)字節(jié)存儲(chǔ)。前4字節(jié)用于存儲(chǔ)datetime類(lèi)型數(shù)據(jù)中距1900年1月1日的天數(shù)。為正數(shù)表示日期在1900年1月1日之后，為負(fù)數(shù)則表示日期在1900年1月1日之前。后4字節(jié)用于存儲(chǔ)datetime類(lèi)型數(shù)據(jù)中距12:00（24小時(shí)制）的毫秒數(shù)。日期部分的表示形式常用的格式如下：年月日2001Jan20、2001January20年日月200120Jan月日[,]年Jan202001、Jan20,2001、Jan20,01月年日J(rèn)an200120日月[,]年20Jan2001、20Jan,2001日年月202001Jan年（4位數(shù)）2001表示2001年1月1日年月日20010120、010120月/日/年01/20/01、1/20/01、01/20/2001、1/20/2001月-日-年01-20-01、1-20-01、01-20-2001、1-20-2001月.日.年01.20.01、1.20.01、01.20.2001、1.20.2001數(shù)據(jù)類(lèi)型時(shí)間部分常用的表示格式如下：時(shí):分10:20、08:05時(shí):分:秒20:15:18、20:15:18.2時(shí):分:秒:毫秒20:15:18:200時(shí):分AM|PM10:10AM、10:10PM（2）smalldatetime：該類(lèi)型數(shù)據(jù)可表示從1900年1月1日～2079年6月6日的日期和時(shí)間，數(shù)據(jù)精確到分鐘。即29.998s或更低的值向下舍入為最接近的分鐘，29.999s或更高的值向上舍入為最接近的分鐘。（3）date：該類(lèi)型數(shù)據(jù)可以表示從公元元年1月1日到9999年12月31日的日期，date類(lèi)型只存儲(chǔ)日期數(shù)據(jù)，不存儲(chǔ)時(shí)間數(shù)據(jù)，存儲(chǔ)長(zhǎng)度為3字節(jié)，表示形式與datetime數(shù)據(jù)類(lèi)型的日期部分相同。數(shù)據(jù)類(lèi)型（4）time：該類(lèi)型數(shù)據(jù)只存儲(chǔ)時(shí)間數(shù)據(jù)，表示格式為“hh:mm:ss[.nnnnnnn]”。hh表示小時(shí)，范圍為0～23。mm表示分鐘，范圍為0～59。ss表示秒數(shù)，范圍為0～59。n是0～7位數(shù)字，范圍為0～9999999，表示秒的小數(shù)部分，即微秒數(shù)。所以time數(shù)據(jù)類(lèi)型的取值范圍為00:00:00.000

0000～23:59:59.9999999。time類(lèi)型的存儲(chǔ)大小為5字節(jié)。另外還可以自定義time類(lèi)型微秒數(shù)的位數(shù)，例如，time(1)表示小數(shù)位數(shù)為1，默認(rèn)為7。（5）datetime2：新的datetime2數(shù)據(jù)類(lèi)型和datetime類(lèi)型一樣，也用于存儲(chǔ)日期和時(shí)間信息。但是datetime2類(lèi)型取值范圍更廣，日期部分取值范圍從公元元年1月1日～9999年12月31日，時(shí)間部分的取值范圍為00:00:00.0000000～23:59:59.999999。另外，用戶還可以自定義datetime2數(shù)據(jù)類(lèi)型中微秒數(shù)的位數(shù)，例如，datetime(2)表示小數(shù)位數(shù)為2。datetime2類(lèi)型的存儲(chǔ)大小隨著微秒數(shù)的位數(shù)（精度）而改變，精度小于3時(shí)為6字節(jié)，精度為4和5時(shí)為7字節(jié)，所有其他精度則需要8字節(jié)。（6）datetimeoffset：該數(shù)據(jù)類(lèi)型也用于存儲(chǔ)日期和時(shí)間信息，取值范圍與datetime2類(lèi)型相同。但datetimeoffset類(lèi)型具有時(shí)區(qū)偏移量，此偏移量指定時(shí)間相對(duì)于協(xié)調(diào)世界時(shí)（UTC）偏移的小時(shí)和分鐘數(shù)。數(shù)據(jù)類(lèi)型11．時(shí)間戳型標(biāo)識(shí)符是timestamp。若創(chuàng)建表時(shí)定義一個(gè)列的數(shù)據(jù)類(lèi)型為時(shí)間戳類(lèi)型，那么每當(dāng)對(duì)該表插入新行或修改已有行時(shí)，都由系統(tǒng)自動(dòng)將一個(gè)計(jì)數(shù)器值加到該列，即將原來(lái)的時(shí)間戳值加上一個(gè)增量。記錄timestamp列的值實(shí)際上反映了系統(tǒng)對(duì)該記錄修改的相對(duì)（相對(duì)于其他記錄）順序。一個(gè)表只能有一個(gè)timestamp列。timestamp類(lèi)型數(shù)據(jù)的值實(shí)際上是二進(jìn)制格式數(shù)據(jù)，其長(zhǎng)度為8字節(jié)。12．圖像數(shù)據(jù)類(lèi)型標(biāo)識(shí)符是image，它用于存儲(chǔ)圖片、照片等。實(shí)際存儲(chǔ)的是可變長(zhǎng)度二進(jìn)制數(shù)據(jù)，介于0與231

1（2147483647）字節(jié)之間。在SQLServer中，該類(lèi)型是為了向下兼容而保留的數(shù)據(jù)類(lèi)型。微軟推薦用戶使用varbinary(MAX)數(shù)據(jù)類(lèi)型來(lái)替代image類(lèi)型。數(shù)據(jù)類(lèi)型13.xml類(lèi)型用來(lái)在數(shù)據(jù)庫(kù)中保存xml文檔和片段的一種類(lèi)型，大小不能超過(guò)2GB。例如描述地址：'{"省":"江蘇","市":"南京","區(qū)縣":"棲霞","街道":"仙林智谷","電話":"1381390100X"}'。14.地理空間類(lèi)型和幾何數(shù)據(jù)類(lèi)型geometry：用于存儲(chǔ)地理空間類(lèi)型。例如地球某點(diǎn)的經(jīng)緯度為：（118.912000，32.096790），把空間描述字符串轉(zhuǎn)換為地理空間類(lèi)型數(shù)據(jù)：geometry::STGeomFromText('POINT(118.91200032.096790)',4326)，其中4326是坐標(biāo)系的參數(shù)，代表GCS-WGS-1984坐標(biāo)系，是系統(tǒng)默認(rèn)的坐標(biāo)系。數(shù)據(jù)類(lèi)型15．其他數(shù)據(jù)類(lèi)型（1）sql_variant：一種存儲(chǔ)SQLServer支持的各種類(lèi)型（除text、ntext、image、timestamp和sql_variant外）值的數(shù)據(jù)類(lèi)型，，最大長(zhǎng)度可達(dá)8016字節(jié)。（2）uniqueidentifier：唯一標(biāo)識(shí)符類(lèi)型。系統(tǒng)將為這種類(lèi)型的數(shù)據(jù)產(chǎn)生唯一標(biāo)識(shí)值，它是一個(gè)16字節(jié)長(zhǎng)的二進(jìn)制數(shù)據(jù)。（3）hierarchyid：SQLServer新增加的一種長(zhǎng)度可變的系統(tǒng)數(shù)據(jù)類(lèi)型，可用來(lái)表示層次結(jié)構(gòu)中的位置。另外，varchar、nvarchar、varbinary這3種數(shù)據(jù)類(lèi)型可以使用MAX關(guān)鍵字，如varchar(MAX)、nvarchar(MAX)、varbinary(MAX)，加了MAX關(guān)鍵字的這幾種數(shù)據(jù)類(lèi)型最多可存放231-1個(gè)字節(jié)的數(shù)據(jù)，分別可以用來(lái)替換text、ntext和image數(shù)據(jù)類(lèi)型。03表結(jié)構(gòu)設(shè)計(jì)表結(jié)構(gòu)設(shè)計(jì)在創(chuàng)建表之前，先要確定表的名字、表的屬性，同時(shí)確定表所包含的列名、列的數(shù)據(jù)類(lèi)型、長(zhǎng)度、是否可為空值、約束條件、默認(rèn)值設(shè)置、規(guī)則以及所需索引、哪些列是主鍵、哪些列是外鍵等，這些屬性構(gòu)成表結(jié)構(gòu)。例如：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)（xscj）包含學(xué)生表（xsb）、課程表（kcb）和成績(jī)表（cjb）。學(xué)生表xsb包含的屬性有學(xué)號(hào)、姓名、性別、出生日期、專(zhuān)業(yè)、總學(xué)分、備注，如表。列

名數(shù)據(jù)類(lèi)型長(zhǎng)

度是否可空默

認(rèn)

值說(shuō)

明學(xué)號(hào)定長(zhǎng)字符型（char）6×無(wú)主鍵,前兩位表示年級(jí),中間兩位為專(zhuān)業(yè),后兩位為序號(hào)姓名定長(zhǎng)字符型（char）8×無(wú)

性別位型（bit）默認(rèn)值√11:男,0:女出生日期日期型（date）默認(rèn)值√無(wú)

專(zhuān)業(yè)不定長(zhǎng)字符型（varchar）12√計(jì)算機(jī)

總學(xué)分整數(shù)型（int）默認(rèn)值√00≤總學(xué)分<160備注不定長(zhǎng)字符型（varchar）500√無(wú)

表結(jié)構(gòu)設(shè)計(jì)參照xsb表結(jié)構(gòu)的設(shè)計(jì)方法，同樣可以設(shè)計(jì)出kcb和cjb其他兩個(gè)表的結(jié)構(gòu)，如表。列

名數(shù)據(jù)類(lèi)型長(zhǎng)

度是否可空默

認(rèn)

值說(shuō)

明課程號(hào)定長(zhǎng)字符型（char）3×無(wú)主鍵課程名定長(zhǎng)字符型（char）16×無(wú)

開(kāi)課學(xué)期整數(shù)型（tinyint）1√1只能為1～8學(xué)時(shí)整數(shù)型（tinyint）1√0

學(xué)分整數(shù)型（tinyint）1×0只能為1～6kcb的表結(jié)構(gòu)列

名數(shù)據(jù)類(lèi)型長(zhǎng)

度是否可空默

認(rèn)

值說(shuō)

明學(xué)號(hào)定長(zhǎng)字符型（char）6×無(wú)主鍵課程號(hào)定長(zhǎng)字符型（char）3×無(wú)主鍵成績(jī)整數(shù)型（int）默認(rèn)值√0范圍為0～100

cjb的表結(jié)構(gòu)第3章

表的創(chuàng)建和操作——表結(jié)構(gòu)創(chuàng)建、修改和刪除01表結(jié)構(gòu)創(chuàng)建表結(jié)構(gòu)創(chuàng)建T-SQL語(yǔ)法格式如下。CREATETABLE表名( <列定義>, …)<列定義>::=列名,數(shù)據(jù)類(lèi)型[屬性][默認(rèn)值][列約束]其中：（1）表名如果創(chuàng)建的表非當(dāng)前數(shù)據(jù)庫(kù)的表，需要采用“數(shù)據(jù)庫(kù)-表名”。一般使用下列命令指定當(dāng)前數(shù)據(jù)庫(kù)：USE數(shù)據(jù)庫(kù)名（2）列名必須符合標(biāo)識(shí)符規(guī)則，長(zhǎng)度不能超過(guò)64個(gè)字符，而且在表中要唯一。（3）數(shù)據(jù)類(lèi)型和長(zhǎng)度通過(guò)關(guān)鍵字聲明具體的類(lèi)型，有的數(shù)據(jù)類(lèi)型需要指明長(zhǎng)度n并用()括起。表結(jié)構(gòu)創(chuàng)建（4）屬性SQLServer支持用多種關(guān)鍵字定義列的屬性，比如，用NOTNULL|NULL指定列是否允許空值，NOTNULL表示列不可取空值，NULL表示列可取空值。本例的“學(xué)號(hào)”和“姓名”列以NOTNULL明確不允許空值。如果不指定，SQLServer視為指定了NULL。（5）默認(rèn)值用“DEFAULT常量表達(dá)式”為所在列指定默認(rèn)值，其中“常量表達(dá)式”必須是一個(gè)常量值、標(biāo)量函數(shù)或NULL值。DEFAULT定義可適用于除定義為timestamp或帶IDENTITY屬性的列以外的任何列。（6）列約束列的完整性約束，指定主鍵、替代鍵、外鍵等。例如，若指定該列為主鍵，則使用PRIMARYKEY關(guān)鍵字，本例在“學(xué)號(hào)”列上創(chuàng)建了主鍵約束，將該列設(shè)為整個(gè)表的主鍵：學(xué)號(hào) char(6) NOTNULLPRIMARYKEY,表結(jié)構(gòu)創(chuàng)建【例3.1】在xscj數(shù)據(jù)庫(kù)中創(chuàng)建學(xué)生表xsb，該表的結(jié)構(gòu)如表3.3所示。語(yǔ)句如下。USExscjCREATETABLExsb(

學(xué)號(hào) char(6) NOTNULLPRIMARYKEY,

姓名 char(8) NOTNULL,

性別 bit NULLDEFAULT1,

出生日期 date NULL,

專(zhuān)業(yè) varchar(12) NULLDEFAULT'計(jì)算機(jī)',

總學(xué)分 int NULLDEFAULT0,

備注 varchar(500) NULL)表結(jié)構(gòu)創(chuàng)建說(shuō)明：（1）USExscj語(yǔ)句將數(shù)據(jù)庫(kù)xscj指定為當(dāng)前數(shù)據(jù)庫(kù)，然后CREATETABLE語(yǔ)句在數(shù)據(jù)庫(kù)xscj中創(chuàng)建表xsb。如果xscj數(shù)據(jù)庫(kù)此前沒(méi)有創(chuàng)建，可以采用默認(rèn)方式創(chuàng)建xscj數(shù)據(jù)庫(kù)，然后打開(kāi)xscj數(shù)據(jù)庫(kù)，再在該數(shù)據(jù)庫(kù)中創(chuàng)建表。CREATEDATABASExscj（2）后面只要在xscj數(shù)據(jù)庫(kù)中操作，就不再需要打開(kāi)該數(shù)據(jù)庫(kù)。（3）xsb表創(chuàng)建后，在SSMS的對(duì)象資源管理器的xscj的“表”下就會(huì)顯示xsb?！纠?.2】創(chuàng)建一個(gè)帶計(jì)算列的表，表中包含課程的課程號(hào)、總成績(jī)和學(xué)習(xí)該課程的人數(shù)，以及課程的平均成績(jī)。T-SQL語(yǔ)句如下。CREATETABLEkcb_pjcj(

課程號(hào) char(3) PRIMARYKEY,

總成績(jī) real NOTNULL,

人數(shù) int NOTNULL,

平均成績(jī) AS總成績(jī)/人數(shù)PERSISTED)02分區(qū)表創(chuàng)建分區(qū)函數(shù)分區(qū)方案使用分區(qū)方案創(chuàng)建分區(qū)表分區(qū)表創(chuàng)建創(chuàng)建分區(qū)表的步驟如下：（1）創(chuàng)建分區(qū)函數(shù)，指定如何分區(qū)。（2）創(chuàng)建分區(qū)方案，定義分區(qū)函數(shù)在文件組上的位置。（3）使用分區(qū)方案。1．分區(qū)函數(shù)1）創(chuàng)建分區(qū)函數(shù)語(yǔ)法格式如下：CREATEPARTITIONFUNCTION函數(shù)名(輸入?yún)?shù)類(lèi)型) ASRANGE[LEFT|RIGHT] FORVALUES([邊界值[,...]])說(shuō)明：函數(shù)名：要?jiǎng)?chuàng)建的分區(qū)函數(shù)的名稱(chēng)。輸入?yún)?shù)類(lèi)型：用于分區(qū)的列的數(shù)據(jù)類(lèi)型。LEFT|RIGHT：指定當(dāng)間隔值由數(shù)據(jù)庫(kù)引擎按升序從左到右排列時(shí)，邊界值屬于每個(gè)邊界間隔的哪一側(cè)（左側(cè)還是右側(cè)）。如果未指定，則默認(rèn)值為L(zhǎng)EFT。邊界值：為該分區(qū)函數(shù)的已分區(qū)表或索引的每個(gè)分區(qū)指定邊界值。分區(qū)表創(chuàng)建【例3.3】如果需要對(duì)學(xué)號(hào)列進(jìn)行分區(qū)，需要對(duì)char類(lèi)型列創(chuàng)建一個(gè)分區(qū)函數(shù)，名為PFunXH，數(shù)據(jù)分成4個(gè)區(qū)。包括小于或等于'210000'的區(qū)、大于'210000'且小于或等于'220000'的區(qū)、大于'220000'且小于或等于'230000'的區(qū)和大于'230000'的區(qū)。T-SQL語(yǔ)句如下。USExscjCREATEPARTITIONFUNCTIONPFunXH(char(6)) ASRANGELEFTFORVALUES('210000','220000','230000')語(yǔ)句執(zhí)行后，在xscj→存儲(chǔ)→分區(qū)函數(shù)下，就會(huì)看到“PFunXH”項(xiàng)。2）分區(qū)函數(shù)刪除DROPPARTITIONFUNCTION分區(qū)函數(shù)名分區(qū)表創(chuàng)建2.分區(qū)方案1）創(chuàng)建分區(qū)方案語(yǔ)法格式如下：CREATEPARTITIONSCHEME方案名 ASPARTITION函數(shù)名 [ALL]TO({文件組名|[PRIMARY]}[,...n])說(shuō)明：方案名：創(chuàng)建的分區(qū)方案的名稱(chēng)，在創(chuàng)建表時(shí)使用該方案即可創(chuàng)建分區(qū)表。函數(shù)名：使用分區(qū)方案的分區(qū)函數(shù)的名稱(chēng)，該函數(shù)必須在數(shù)據(jù)庫(kù)中存在，分區(qū)函數(shù)所創(chuàng)建的分區(qū)將映射到在分區(qū)方案中指定的文件組。ALL：指定所有分區(qū)都映射到在“文件組名”中提供的文件組，或映射到主文件組（如果指定了[PRIMARY]）。文件組名：指定用來(lái)持有由“函數(shù)名”指定的分區(qū)的文件組的名稱(chēng)。分區(qū)分配到文件組的順序是從分區(qū)1開(kāi)始，按文件組在[,...]中列出的順序進(jìn)行分配。分區(qū)表創(chuàng)建【例3.3續(xù)】在數(shù)據(jù)庫(kù)xscj中增加文件組fgroup1、fgroup2、fgroup3和fgroup4，根據(jù)前面定義的分區(qū)函數(shù)創(chuàng)建一個(gè)分區(qū)方案，將分區(qū)函數(shù)中的4個(gè)分區(qū)分別存放在這4個(gè)文件組中。T-SQL語(yǔ)句如下。ALTERDATABASExscjADDFILEGROUPfgroup1ALTERDATABASExscjADDFILEGROUPfgroup2ALTERDATABASExscjADDFILEGROUPfgroup3ALTERDATABASExscjADDFILEGROUPfgroup4CREATEPARTITIONSCHEMEPSchXH ASPARTITIONPFunXH TO(fgroup1,fgroup2,fgroup3,fgroup4)語(yǔ)句執(zhí)行后，在xscj→存儲(chǔ)→分區(qū)方案下，就會(huì)出現(xiàn)“PSchXH”項(xiàng)。2）刪除分區(qū)方案DROPPARTITIONSCHEME分區(qū)方案名分區(qū)表創(chuàng)建3．使用分區(qū)方案創(chuàng)建分區(qū)表分區(qū)函數(shù)和分區(qū)方案創(chuàng)建以后就可以創(chuàng)建分區(qū)表了。創(chuàng)建分區(qū)表使用CREATETABLE語(yǔ)句，只要在ON關(guān)鍵字后指定分區(qū)方案和分區(qū)列即可。【例3.3續(xù)】在數(shù)據(jù)庫(kù)xscj中創(chuàng)建學(xué)生子表，分區(qū)方案是對(duì)該表學(xué)號(hào)列分區(qū)。T-SQL語(yǔ)句如下。USExscjCREATETABLExsb1_p(

學(xué)號(hào) char(6) NOTNULLPRIMARYKEY,

姓名 char(8) NOTNULL,

出生日期 date NULL, )ONPSchXH(學(xué)號(hào))【例3.3續(xù)】刪除學(xué)生子表分區(qū)及其分區(qū)方案和分區(qū)函數(shù)。USExscjDROPTABLExsb1_pDROPPARTITIONSCHEMEPSchXHDROPPARTITIONFUNCTIONPFunXH03表結(jié)構(gòu)修改表結(jié)構(gòu)修改語(yǔ)法格式如下：ALTERTABLE表名 ALTERCOLUMN 列名

列定義… |ADD <列定義> |DROPCOLUMN 列名其中，ALTERCOLUMN子句修改已有列的屬性；ADD子句添加新的列；DROPCOLUMN子句刪除已有的列?！纠?.4】在xsb1_p表中增加1個(gè)新列“獎(jiǎng)學(xué)金等級(jí)”，然后再刪除該列。T-SQL語(yǔ)句如下。（1）增加“獎(jiǎng)學(xué)金等級(jí)”列：USExscjALTERTABLExsb1_p ADD獎(jiǎng)學(xué)金等級(jí)tinyintNULL說(shuō)明：執(zhí)行后，“xscj”中dbo.xsb1_p表就出現(xiàn)了“獎(jiǎng)學(xué)金等級(jí)”列。如果原表中存在和添加列同名的列，則語(yǔ)句運(yùn)行將出錯(cuò)。（2）刪除“獎(jiǎng)學(xué)金等級(jí)”列：ALTERTABLExsb1_p DROPCOLUMN獎(jiǎng)學(xué)金等級(jí)表結(jié)構(gòu)修改【例3.4續(xù)】將xsb1_p表“姓名”的列長(zhǎng)度由原來(lái)的8改為10，“出生日期”的列的數(shù)據(jù)類(lèi)型由原來(lái)的date改為smalldatetime。T-SQL語(yǔ)句如下。ALTERTABLExsb1_p ALTERCOLUMN姓名char(10)ALTERTABLExsb1_p ALTERCOLUMN出生日期smalldatetime說(shuō)明：（1）姓名列長(zhǎng)度由8改為10（變大）是可以的，但如果變小，則需要該列所有數(shù)據(jù)均不超過(guò)修改后的長(zhǎng)度，否則不能執(zhí)行。（2）若表中該列所存數(shù)據(jù)的數(shù)據(jù)類(lèi)型與將要修改的列類(lèi)型沖突，則發(fā)生錯(cuò)誤。04刪

除

表刪除表語(yǔ)法格式：DROPTABLE表名其中，“表名”是要被刪除的表的名稱(chēng)?！纠?.4續(xù)】刪除xsb1_p表。T-SQL語(yǔ)句如下。USExscjDROPTABLExsb1_pGO05界面方式創(chuàng)建表創(chuàng)建表結(jié)構(gòu)修改表結(jié)構(gòu)刪除表界面方式創(chuàng)建表1.創(chuàng)建表結(jié)構(gòu)【例3.5】界面方式創(chuàng)建xsb、kcb和cjb表。（1）在“對(duì)象資源管理器”中展開(kāi)“數(shù)據(jù)庫(kù)”，右擊“xscj”數(shù)據(jù)庫(kù)菜單下的“表”選項(xiàng)，在彈出的快捷菜單中選擇“新建表”菜單項(xiàng)，打開(kāi)“表設(shè)計(jì)器”窗口，如圖。（2）在“表設(shè)計(jì)器”窗口中，根據(jù)已經(jīng)設(shè)計(jì)好的xsb的表結(jié)構(gòu)分別輸入或選擇各列的名稱(chēng)、數(shù)據(jù)類(lèi)型、是否允許為空值等屬性。界面方式創(chuàng)建表（3）在“列屬性”選項(xiàng)卡包含當(dāng)前列默認(rèn)值或綁定、標(biāo)識(shí)規(guī)范、說(shuō)明等屬性。例如，“專(zhuān)業(yè)”列的“默認(rèn)值或綁定”項(xiàng)輸入“'計(jì)算機(jī)'”，對(duì)應(yīng)性別的默認(rèn)值輸入“1”。說(shuō)明項(xiàng)是對(duì)列的注釋。如圖。界面方式創(chuàng)建表（4）在“學(xué)號(hào)”列上右擊鼠標(biāo)，選擇“設(shè)置主鍵”菜單項(xiàng)，選擇“設(shè)為主鍵”選項(xiàng)，學(xué)生表結(jié)構(gòu)設(shè)計(jì)完成后的結(jié)果如圖。（5）在表的各列屬性均編輯完成后，單擊工具欄中的“保存”按鈕，出現(xiàn)“選擇表名”對(duì)話框。在其中輸入表名“xsb”，單擊“確定”按鈕即可創(chuàng)建xsb表。界面方式創(chuàng)建表2.修改表結(jié)構(gòu)1）更改表名SQLServer2008中允許改變一個(gè)表的名字，但當(dāng)表名改變后，與此相關(guān)的某些對(duì)象（如視圖），以及通過(guò)表名與表相關(guān)的存儲(chǔ)過(guò)程將無(wú)效。在“對(duì)象資源管理器”中選擇需要更名的表xsb，右擊鼠標(biāo)，在彈出的快捷菜單上選擇“重命名”菜單項(xiàng)，輸入新的表名“student”，單擊“Enter”鍵即可更改表名。2）增加列和刪除列向表xsb中添加一個(gè)“獎(jiǎng)學(xué)金等級(jí)”列，列的數(shù)據(jù)類(lèi)型為“tinyint”，允許為空值。（1）在“xscj”數(shù)據(jù)庫(kù)中選擇表“dbo.xsb”，右擊鼠標(biāo)，在彈出的快捷菜單上選擇“設(shè)計(jì)”菜單項(xiàng)，打開(kāi)“表設(shè)計(jì)器”窗口。（2）在“表設(shè)計(jì)器”窗口中選擇第一個(gè)空白行，輸入列名“獎(jiǎng)學(xué)金等級(jí)”，選擇數(shù)據(jù)類(lèi)型“tinyint”。（3）當(dāng)向表中添加的列均輸入完畢后，關(guān)閉該窗口，此時(shí)將彈出一個(gè)“保存更改”對(duì)話框，單擊“是”按鈕（或單擊面板中的按鈕），保存修改后的表。這時(shí)，dbo.xsb就增加了“獎(jiǎng)學(xué)金等級(jí)”列。（4）在“表dbo.xsb設(shè)計(jì)器”窗口中選擇需刪除的列（例如，在xsb表中刪除“獎(jiǎng)學(xué)金等級(jí)”列），右擊鼠標(biāo)，在彈出的快捷菜單上選擇“刪除列”菜單項(xiàng)，該列即被刪除。界面方式創(chuàng)建表3）修改列（1）具有以下特性的列不能修改：數(shù)據(jù)類(lèi)型為timestamp的列。計(jì)算列。全局標(biāo)識(shí)符列。用于索引的列（但若用于索引的列為varchar、nvarchar或varbinary數(shù)據(jù)類(lèi)型時(shí)，可以增加列的長(zhǎng)度）。用于由CREATESTATISTICS生成統(tǒng)計(jì)的列，如需修改這樣的列，則必須先用DROPSTATISTICS語(yǔ)句刪除統(tǒng)計(jì)。用于主鍵或外鍵約束的列。用于CHECK或UNIQUE約束的列。關(guān)聯(lián)有默認(rèn)值的列。（2）當(dāng)改變列的數(shù)據(jù)類(lèi)型時(shí)，要求滿足下列條件：

原數(shù)據(jù)類(lèi)型必須能夠轉(zhuǎn)換為新數(shù)據(jù)類(lèi)型。新數(shù)據(jù)類(lèi)型不能為timestamp類(lèi)型。界面方式創(chuàng)建表【例3.6】在xsb表中，將“姓名”列名改為“name”，數(shù)據(jù)長(zhǎng)度由8改為10，允許為空值。將“出生日期”列名改為“birthday”，數(shù)據(jù)類(lèi)型由“date”改為“datetime”。在“對(duì)象資源管理器”中展開(kāi)數(shù)據(jù)庫(kù)→xscj→表，選擇xsb，右擊鼠標(biāo)，選擇“設(shè)計(jì)”菜單項(xiàng)，進(jìn)入表xsb的設(shè)計(jì)窗口，單擊需要修改的列，修改相應(yīng)的屬性。修改完后保存。3.刪除表刪除一個(gè)表時(shí)，表的定義、表中的所有數(shù)據(jù)以及表的索引、觸發(fā)器、約束等均被刪除。例如，選擇xsb，右擊鼠標(biāo)，在彈出的快捷菜單上選擇“刪除”菜單項(xiàng)。系統(tǒng)彈出“刪除對(duì)象”窗口。單擊“確定”按鈕即可刪除xsb表。第3章

表的創(chuàng)建和操作——表記錄的插入、修改和刪除01插入記錄單記錄插入多記錄插入插入查詢記錄插入記錄1.單記錄插入語(yǔ)法格式如下：INSERTINTO表名(列名,...)VALUES(值表)或者INSERTINTO表名 VALUES(值表)其中，“列名,…”就是要向表中插入數(shù)據(jù)的那些列的名稱(chēng)，之間以逗號(hào)（,）分隔；“值表”則指明列表中的每個(gè)列對(duì)應(yīng)要插入的數(shù)據(jù)內(nèi)容?？梢詫?duì)表中所有的列插入數(shù)據(jù)，也可以只對(duì)部分列插入數(shù)據(jù)。插入記錄【例3.7】向xscj數(shù)據(jù)庫(kù)的表xsb中插入下列記錄。221101，王林，男，2004-02-10，計(jì)算機(jī)，15，NULLT-SQL語(yǔ)句如下。USExscjINSERTINTOxsb VALUES('221101','王林',1,'2004-02-10','計(jì)算機(jī)',15,NULL)使用以下語(yǔ)句與上面的語(yǔ)句執(zhí)行效果是相同的。INSERTINTOxsb VALUES('221101','王林',1,'2004-02-10',DEFAULT,15,NULL);或者：USExscjINSERTINTOxsb(學(xué)號(hào),姓名,性別,出生日期,總學(xué)分) VALUES('221101','王林',1,'2004-02-10',15)插入記錄2.多記錄插入一條SQL語(yǔ)句完成多條記錄插入的任務(wù)。語(yǔ)法格式如下：INSERTINTO表名(列,...)VALUES(值表1),(值表2),...,(值表n);【例3.8】一次向xsb表中插入5行記錄。221201，劉華，男，2004-06-10，通信工程，13，輔修計(jì)算機(jī)專(zhuān)業(yè)221202，王林，男，2004-01-29，通信工程，13221204，馬琳琳，女，2003-02-10，通信工程，15 221206，李計(jì)，男，2003-09-20，通信工程，16211201，李紅慶，男，2002-05-01，通信工程，43T-SQL語(yǔ)句如下。USExscjINSERTINTOxsbVALUES ('221201','劉華',1,'2004-06-10','通信工程',13,'輔修計(jì)算機(jī)專(zhuān)業(yè)'), ('221202','王林',1,'2004-01-29','通信工程',13,NULL), ('221204','馬琳琳',0,'2003-02-10','通信工程',15,NULL), ('221206','李計(jì)',1,'2003-09-20','通信工程',16,NULL), ('211201','李紅慶',1,'2002-05-01','通信工程',43,NULL)插入記錄3.插入查詢記錄可以把一個(gè)表的記錄移植到另一個(gè)表或者合并已有多張表的內(nèi)容。語(yǔ)法格式如下：INSERTINTO目標(biāo)表名(列名,...) SELECT列名,...FROM源表名[WHERE條件]其中，第一個(gè)“(列名,...)”是要插入記錄的目標(biāo)表中的列，第二個(gè)“列名,...”是從已存有記錄的源表中查詢出來(lái)的結(jié)果列，如果源表與目標(biāo)表有著完全相同的結(jié)構(gòu)，也可省略這兩個(gè)列表。插入記錄【例3.9】從表xsb中生成通信工程專(zhuān)業(yè)的學(xué)生表xsb2，包括num（學(xué)號(hào)）、name（姓名）、speiality(專(zhuān)業(yè))，要求新表中的數(shù)據(jù)為結(jié)果集中前5行。建立表xsb2：T-SQL語(yǔ)句如下。USExscjCREATETABLExsb2( num char(6) NOTNULLPRIMARYKEY, name char(8) NOTNULL, speiality char(10) NULL)用INSERT語(yǔ)句向xsb2表中插入xsb表通信工程專(zhuān)業(yè)的學(xué)號(hào)、姓名、專(zhuān)業(yè)列。插入記錄T-SQL語(yǔ)句如下。INSERTINTOxsb2 SELECT學(xué)號(hào),姓名,專(zhuān)業(yè) FROMxsb WHERE專(zhuān)業(yè)='通信工程'從xsb2表中查詢結(jié)果：SELECT* FROMxsb2查詢結(jié)果如圖。02修改記錄修改記錄表中的數(shù)據(jù)行記錄可以用命令修改。語(yǔ)法格式如下：UPDATE表名 SET列名=值,... [WHERE條件]

其中，SET子句指示要更新的列以及對(duì)應(yīng)的新值，新值可以是表達(dá)式或關(guān)鍵字DEFAULT（默認(rèn)值）?！纠?.10】將xsb2表中學(xué)號(hào)為211201的學(xué)生學(xué)號(hào)改為計(jì)算機(jī)專(zhuān)業(yè)編號(hào)和專(zhuān)業(yè)改為“計(jì)算機(jī)”。USExscjUPDATExsb2 SETnum='211121',speiality='計(jì)算機(jī)' WHEREnum='211201'SELECT*FROMxsb2執(zhí)行結(jié)果顯示如圖。03同步記錄同步記錄在SQLServer中新增加了一個(gè)MERGE語(yǔ)句，用于根據(jù)與源表連接的結(jié)果，對(duì)目標(biāo)表執(zhí)行插入、更新或刪除操作。例如，根據(jù)一個(gè)表的數(shù)據(jù)在另一個(gè)表中插入、更新或刪除有差異的行，可以對(duì)兩個(gè)表進(jìn)行信息同步。語(yǔ)法格式如下：MERGEINTO目標(biāo)表[AS別名]USING源表ON條件 /*（1）*/ WHENMATCHED /*（2）*/ THENUPDATESET…|DELETE WHENNOTMATCHED /*（3）*/ THENINSERT[(列表)]VALUES(值表) WHENNOTMATCHEDBYSOURCE /*（4）*/ THENUPDATESET…|DELETE;同步記錄說(shuō)明：（1）目標(biāo)表：指定要更新數(shù)據(jù)的表或視圖。也可用AS為表定義別名。USING子句：指定用于更新的源表，ON指定在源表與目標(biāo)表進(jìn)行連接時(shí)所遵循的條件。（2）WHENMATCHED子句：目標(biāo)表存在與源表匹配