第 4 章 表與視圖

第4章表與視圖

SQLServer中的數(shù)據(jù)類型4.1創(chuàng)建表4.2修改表的結(jié)構(gòu)4.3添加表中的記錄4.4刪除表4.7刪除表中的記錄4.6視圖的概念4.8修改表中的記錄4.5通過視圖修改基本表中的數(shù)據(jù)4.11管理視圖4.10創(chuàng)建視

圖4.94.1SQLServer中的數(shù)據(jù)類型4.1.1數(shù)據(jù)類型1．字節(jié)（Byte）型字節(jié)型數(shù)據(jù)存儲為單精度型、無符號整型、8位（1個字節(jié)）的數(shù)值形式，范圍為0～255。2．整數(shù)型整數(shù)型數(shù)據(jù)可用于存儲精確的整數(shù)，包括bigint，int，smallint和tinyint4種類型。它們的區(qū)別在于存儲的范圍不同。如表4-1所示。數(shù)據(jù)類型數(shù)據(jù)范圍占用存儲空間bigint?263～263?18個字節(jié)int?231～231?14個字節(jié)smallint?215～215?12個字節(jié)tinyint0～2551個字節(jié)表4-1 整數(shù)型數(shù)據(jù)3．精確數(shù)值型精確數(shù)值型數(shù)據(jù)由整數(shù)部分和小數(shù)部分構(gòu)成，decimal和numeric包括兩種類型。存儲范圍為?1038+1～1038?1。兩者的區(qū)別在于decimal不能用于帶有identity關(guān)鍵字的列。聲明精確數(shù)值型數(shù)據(jù)的格式是numeric(p,[s])或者decimal(p,[s])，其中p為精度，s為小數(shù)位數(shù)，s的默認(rèn)值為0。4．近似數(shù)值型近似數(shù)值型數(shù)據(jù)可以存儲精度不是很高，但數(shù)據(jù)的取值范圍卻又非常大的數(shù)據(jù)。借助科學(xué)計(jì)數(shù)法，即尾數(shù)E階數(shù)的形式來表示。數(shù)據(jù)類型數(shù)據(jù)范圍占用存儲空間real?3.40E+38～3.40E+384個字節(jié)float?1.79E308～1.79E3088個字節(jié)表4-2 近似數(shù)值型數(shù)據(jù)5．貨幣型在SQLServer中用十進(jìn)制數(shù)來表示貨幣值。使用貨幣型數(shù)據(jù)時必須在數(shù)據(jù)前加上貨幣表示符（$），數(shù)據(jù)中間不能有逗號（，）；當(dāng)貨幣值為負(fù)數(shù)時，在數(shù)據(jù)前加上符號（?）。貨幣型包括money和smallmoney兩種類型。兩者的區(qū)別如表4-3所示。數(shù)據(jù)類型數(shù)據(jù)范圍占用存儲空間smallmoney?231～231?14個字節(jié)Money?263～263?18個字節(jié)表4-3 money和smallmoney數(shù)據(jù)6．位（bit）型在SQLServer中位型相當(dāng)于很多語言中的邏輯型，存儲0和1，占用1個字節(jié)存儲空間。7．字符型字符型數(shù)據(jù)指由字母、數(shù)字和其他特殊符號（如$，#，@）構(gòu)成的字符串。在引用字符串時要用單引號括起來。字符型數(shù)據(jù)最多包含的字符數(shù)目是8000。字符型包括char和varchar兩種類型。聲明的格式是char(n)或者varchar(n)。n表示字符串所包含的最大字符數(shù)目。前者是當(dāng)輸入的字符長度不足n時則用空格補(bǔ)足，而后者是輸入的字符的長度就是實(shí)際的長度。所以前者又稱為固定長度字符型，后者稱為可變長度字符型。8．文本型當(dāng)存儲的字符數(shù)目大于8000時使用文本型。文本型包括text和ntext，前者存儲ASCII字符，后者存儲Unicode字符。text類型可以表示最大長度為231?1個字符，其存儲長度為實(shí)際字符數(shù)個字節(jié)。而ntext類型可以表示最大長度為230?1個Unicode字符，其存儲長度為實(shí)際字符個數(shù)的兩倍，因?yàn)閁nicode字符用雙字節(jié)表示的。9．日期時間型在SQLServer中日期時間型的數(shù)據(jù)以字符串的形式表示，即要用單引號括起來。日期時間型有smalldatetime和datetime兩種類型。

smalldatetime可表示從1900年1月1日到2079年6月6日的日期和時間，其存儲長度為4個字節(jié)，前2個字節(jié)用來存儲日期部分距1900年1月1日之后的天數(shù)，后2個字節(jié)用來存儲時間部分距中午12點(diǎn)的分鐘數(shù)。

datetime可表示從1753年1月1日到9999年12月31日的日期和時間，其存儲長度為8個字節(jié)，前4個字節(jié)用來存儲距1900年1月1日的天數(shù)，后4個字節(jié)用來存儲距中午12點(diǎn)的毫秒數(shù)。4.1.2空值的含義創(chuàng)建表時需要確定該列的取值能否為空值（NULL）?？罩狄馕吨鴽]有值，并不是“空格”或數(shù)值0。允許空值表示該列取值是不確定的。4.2創(chuàng)建表（1）打開“企業(yè)管理器”，在“企業(yè)管理器”中展開服務(wù)器，繼續(xù)展開“數(shù)據(jù)庫”，并展開要建立表的數(shù)據(jù)庫，本例是“Student”。在“表”項(xiàng)上右擊鼠標(biāo)，執(zhí)行“新建表”命令，如圖4-1所示。圖4-1建立新表（2）這時出現(xiàn)如圖4-2所示的設(shè)計(jì)表的各個字段的圖形界面，根據(jù)表結(jié)構(gòu)依次輸入即可。圖4-2student_Info表結(jié)構(gòu)（3）輸入完成后在“student_ID”列上單擊，然后在工具欄中單擊按鈕，這時“學(xué)號”前出現(xiàn)圖標(biāo)。這樣就完成了主鍵的設(shè)置。如果是多列構(gòu)成主鍵，那么就要先同時選中這幾列再單擊按鈕。（4）單擊工具欄上的按鈕，在彈出的“選擇名稱”對話框中輸入表名就完成了表的創(chuàng)建工作。本例輸入“student_Info”。4.2.2使用CREATETABLE語句創(chuàng)建表1．語法

CREATETABLEtable_name(column_name

data_type{[NULL|NOTNULL][PRIMARYKEY|UNIQUE]}[,…n])2．參數(shù)說明①CREATETABLE：語法的關(guān)鍵詞用大寫字母來表示。本語法中表明是要創(chuàng)建表。②table_name：用戶自定義的表名。③column_name：字段名。④data_type：字段的數(shù)據(jù)類型。⑤NULL|NOTNULL：允許字段為空或者不為空,默認(rèn)情況下是NULL。⑥PRIMARYKEY|UNIQUE：字段設(shè)置為主鍵或者字段值惟一。⑦[,…n]：表明可以重復(fù)前面的內(nèi)容。在本語法中表明可以定義多個字段。⑧由于建表時還應(yīng)考慮數(shù)據(jù)的完整性等問題，所以上面的語法是不全面的，但已經(jīng)可以創(chuàng)建表了。4.3修改表的結(jié)構(gòu)（1）打開“企業(yè)管理器”并展開服務(wù)器，繼續(xù)展開“數(shù)據(jù)庫”，并展開要修改表的數(shù)據(jù)庫，本例是“Student”。在“表”項(xiàng)上單擊鼠標(biāo)，右邊會出現(xiàn)表對象，右擊要修改的表名，并選擇“設(shè)計(jì)表”命令。如圖4-3所示。圖4-3修改student_Info表結(jié)構(gòu)

（2）這時會出現(xiàn)創(chuàng)建表結(jié)構(gòu)時的窗口，然后對表結(jié)構(gòu)做修改就可以了。最后保存退出。4.3.2使用ALTERTABLE語句修改表結(jié)構(gòu)

1．語法ALTERTABLEtable_name{ALTERCOLUMNcolumn_name

data_type[NULL|NOTNULL]ADDcolumn_name

data_type[NULL|NOTNULL]DROPCOLUMNcolumn_name[,…n]}2．參數(shù)說明①ALTERTABLE：本語法中表明是要修改表。②table_name：用戶要作修改的表名。③ALTERCOLUMNcolumn_name

data_type：表明更改字段。④ADDcolumn_name

data_type[NULL|NOTNULL]：表明添加新的字段。⑤DROPCOLUMNcolumn_name：表明刪除一列。4.4添加表中的記錄4.4.1使用企業(yè)管理器添加記錄使用企業(yè)管理器添加記錄的步驟如下。（1）打開“企業(yè)管理器”并展開服務(wù)器，繼續(xù)展開“數(shù)據(jù)庫”，并展開要添加記錄的數(shù)據(jù)庫，本例是“Student”。在“表”項(xiàng)上單擊鼠標(biāo)，右邊會出現(xiàn)表對象，右擊要添加記錄的表名，并選擇打開表命令，進(jìn)而選擇返回所有行命令。（2）如果是第一次執(zhí)行該命令，出現(xiàn)的窗口的二維表中是沒有任何內(nèi)容的。否則會出現(xiàn)如圖4-4所示的內(nèi)容。（3）在標(biāo)有“*”的一行輸入相應(yīng)數(shù)據(jù)就可以了。完成后保存退出。圖4-4表student_Info中的記錄4.4.2使用INSERT語句添加記錄1．語法INSERT[INTO]table_name[（column_name1[,column_name2…]）]VALUES(column_value1[,column_value2…])2．參數(shù)說明

①table_name：要插入記錄的表名。②[(column_name1[,column_name2…])]：要插入字段值的字段名。該部分可以省略不寫，那么表明是所有的列都要插入數(shù)據(jù)。③column_value1[,column_value2…]：所要插入的字段值。字段值要和上面所列字段一一對應(yīng)。

INSERT語句一次只能插入一條紀(jì)錄。如果要插入n條記錄，那么INSERT語句要書寫n次。第一個INSERT語句由于是每個字段都要插入數(shù)據(jù)，所以就沒有指明字段名，當(dāng)然也可以像第二個INSERT語句指明每個字段，同時第二個INSERT語句加上了INTO關(guān)鍵字。4.5修改表中的記錄4.5.2使用update語句修改記錄1．語法UPDATEtable_name

SETcolumn_name=column_value[,…n][WHEREcondition]2．參數(shù)說明①table_name：要修改數(shù)據(jù)的表名。②SETcolumn_name=column_value：將字段column_name的值修改為column_value。③WHEREcondition：修改的條件。這是用來做篩選的，表明滿足condition條件的記錄才會執(zhí)行SET操作。該子句可以省略，這時表明所有的記錄都做SET操作。4.6刪除表中的記錄4.6.1使用企業(yè)管理器刪除記錄4.6.2使用DELETE語句刪除記錄1．語法DELETE[FROM]table_name[WHEREcondition]2．參數(shù)說明①table_name：要刪除記錄的表名。②省略了WHERE子句的話表明是要刪除表中所有的記錄，這時候就成了空表。4.7刪除表4.7.1使用企業(yè)管理器刪除表（1）打開“企業(yè)管理器”并展開服務(wù)器，繼續(xù)展開“數(shù)據(jù)庫”，并展開要添加記錄的數(shù)據(jù)庫，本例是“Student”。在“表”項(xiàng)上單擊鼠標(biāo)，右邊會出現(xiàn)表對象，右擊要刪除的表名，并選擇刪除表命令。（2）這時會彈出“除去對象”對話框。在得到了用戶的肯定后系統(tǒng)才會確實(shí)刪除掉表。所以用戶確定要刪除掉表，選擇“全部除去”按鈕就可以了。4.7.2使用DROPTABLE語句刪除表1．語法DROPTABLEtable_name2．參數(shù)說明①table_name：要刪除的表名。②DELETE和DROP同為刪除操作，但兩者的刪除的對象是不一樣的。DELETE是刪除表中的記錄，即使記錄全部沒有了，表仍然是存在的。DROP卻是用來刪除表的，同時表里面的記錄也會隨之消失。因此進(jìn)行DROP操作時要非常慎重。4.8視圖的概念視圖是一種常見的數(shù)據(jù)庫對象，它提供了另外一種查看和存放數(shù)據(jù)的方法。視圖類似于表，可以采用二維表的形式顯示出數(shù)據(jù)。不過視圖是虛擬的表，它的數(shù)據(jù)來自于一個或多個表甚至是視圖。①限制用戶檢索的數(shù)據(jù)。②方便數(shù)據(jù)的操作。4.9創(chuàng)建視圖4.9.1使用企業(yè)管理器創(chuàng)建視圖（1）打開“企業(yè)管理器”并展開服務(wù)器，繼續(xù)展開“數(shù)據(jù)庫”，并展開要建立視圖的數(shù)據(jù)庫，本例是“Student”。在“視圖”項(xiàng)上右擊鼠標(biāo)，執(zhí)行“新建視圖”命令，如圖4-8所示。圖4-8新建視圖（2）接著彈出“創(chuàng)建視圖”的窗口，如圖4-9所示。（3）在第一個窗格中單擊鼠標(biāo)右鍵，從彈出的菜單中選擇“添加表”命令。如圖4-9所示。圖4-9在創(chuàng)建視圖時添加表（4）接著出現(xiàn)“添加表”對話框，如圖4-10所示。在“添加表”對話框中選擇要加入的表、視圖或函數(shù)。本例中需用到“student_Info”這張表，所以選中這張表后單擊“添加”按鈕就完成了添加表的工作。如果要加入多張表，借助Ctrl和Shift鍵可以進(jìn)行多選。圖4-10“添加表”對話框

（5）在第二個窗格中選擇創(chuàng)建視圖所需的字段，可以指定列的別名、排序方式和規(guī)則等。（6）單擊“保存”按鈕，在出現(xiàn)的對話框中輸入視圖名。本例輸入“班號為200001的學(xué)生情況”即可。圖4-11創(chuàng)建視圖時指定字段和條件4.9.2使用CREATEVIEW創(chuàng)建視圖1．語法CREATEVIEWview_name[column_name[,…n]][WITHENCRYPTION]ASSELECT語句[WITHCHECKOPTION]2．參數(shù)說明①column_name[,…n]是用于指明視圖結(jié)構(gòu)的。當(dāng)它省略時就表明使用了SELECT語句的字段名。不過遇到下列情形是不能省略的，而且還要做點(diǎn)修改：某個列不是單純的字段名，比如使用了集合函數(shù)或字段表達(dá)式；多個表做了連接操作，那么選同名字段名作為視圖的字段；給某個列使用新的更合適的名字。②由于CREATEVIEW的語句文本是存放在syscommnets系統(tǒng)表中的，使用系統(tǒng)存儲過程sp_helptext是可以查看到視圖的文本的。為了保密可以使用WITHENCYPTION對存放的CREATEVIEW文本加密。但是該選項(xiàng)使用后，創(chuàng)建者也是沒有辦法再看到的。所以創(chuàng)建者應(yīng)在其他地方做個備份。③SELECT語句具體在第5章中講到。在創(chuàng)建視圖時，SELECT語句的使用有下列限制：不能包括ORDERBY、COMPUTE和COMPUTEBY關(guān)鍵字；不能包含INTO關(guān)鍵字；不可以在臨時表上創(chuàng)建視圖。④WITHCHECKOPTION表示對視圖進(jìn)行UPDATE、INSERT和DELETE操作時，要保證更新、插入或刪除的記錄滿足視圖中SELECT語句的條件表達(dá)式。4.10管理視圖4.10.1查看和修改視圖信息1．通過企業(yè)管理器查看（1）打開“企業(yè)管理