情景4數(shù)據(jù)表管理

1、 情景情景4 4 數(shù)據(jù)表管理數(shù)據(jù)表管理SQL Server數(shù)據(jù)庫技術(shù)及應(yīng)用數(shù)據(jù)庫技術(shù)及應(yīng)用 目錄o 任務(wù)任務(wù)1：掌握數(shù)據(jù)類型：掌握數(shù)據(jù)類型o 任務(wù)任務(wù)2：掌握數(shù)據(jù)表的創(chuàng)建方法：掌握數(shù)據(jù)表的創(chuàng)建方法o 任務(wù)任務(wù)3：創(chuàng)建：創(chuàng)建student數(shù)據(jù)庫的數(shù)據(jù)表數(shù)據(jù)庫的數(shù)據(jù)表4.1 數(shù)據(jù)類型的概念及其意義o現(xiàn)實世界的各類數(shù)據(jù)我們都要將其抽象后放入數(shù)據(jù)庫中，然而各類信息以什么格式，多大的存儲空間進(jìn)行組織和存儲呢？這就有賴于我們事先的規(guī)定。比如把2010-08-19規(guī)定為日期格式，就能正常地識別這組字符串的含義，否則就只是一堆無意義的數(shù)據(jù)。這就是進(jìn)行數(shù)據(jù)類型定義的意義。 數(shù)據(jù)庫存儲的對象主要是數(shù)據(jù)。現(xiàn)實中存在

2、著各種不同類型的數(shù)據(jù)，數(shù)據(jù)類型就是以數(shù)據(jù)的表現(xiàn)方式和存儲方式來劃分的數(shù)據(jù)種類。有了數(shù)據(jù)類型就能對各類數(shù)據(jù)進(jìn)行分門別類，并且對其不同類型的數(shù)據(jù)操作進(jìn)行定義，進(jìn)一步賦予該類數(shù)據(jù)的存儲和操作規(guī)則。4.2 SQL Server的數(shù)據(jù)類型o SQL Server 2008的數(shù)據(jù)類型可以分為兩類：基本數(shù)據(jù)類型和用戶自定義數(shù)據(jù)類型。SQL Server 2008支持?jǐn)?shù)字型、字符型、貨幣型、日期時間型、字符型等多種基本數(shù)據(jù)類型。4.2 SQL Server的數(shù)據(jù)類型 1數(shù)字?jǐn)?shù)據(jù)類型 2貨幣數(shù)據(jù)類型 3日期和時間數(shù)據(jù)類型 4字符數(shù)據(jù)類型 5Unicode數(shù)據(jù)類型 6二進(jìn)制數(shù)據(jù)類型 7特殊數(shù)據(jù)類型 8用戶定義的數(shù)

3、據(jù)類型表表4-1 SQL Server 20084-1 SQL Server 2008數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型范范 圍圍存儲大小存儲大小精確數(shù)字精確數(shù)字Bigbit-263263-1的整型數(shù)字8BInt-231231-1的整型數(shù)字4BSmallint-215215-1的整型數(shù)字2BTinyint0255的整型數(shù)字1BBit0或1的整型數(shù)字不定不定Decimal numeric-10381038-1的定精度與有效位數(shù)的數(shù)字517B貨幣貨幣Money-263(-922,337,203,685,477.5808) 263-1(922,337,203,685,477.5807)的貨幣數(shù)據(jù)8BS

4、mallmoney從-214,748.3648到214,748.3647的貨幣數(shù)據(jù)4B近似數(shù)字近似數(shù)字Float從-1.79E+308到1.79E+308可變精度的數(shù)字48BReal從-3.40E+38到3.40E+38可變精度的數(shù)字4B日期和時日期和時間間Datetime從1753年1月1日到9999年12日31的日期和時間數(shù)據(jù)，最小時間單位為百分之三秒或3.33毫秒8BSmalldatetime從1900年1月1日到2079年6月6日的日期和時間數(shù)據(jù)，最小時間單位為分鐘4BDate僅存儲日期，不存儲時間。范圍是從 1000 年 1 月 1 日到 9999 年 12 月 31 日3BTime

5、僅存儲一天中的時間，不存儲日期。范圍從00:00:00.0000000 23:59:59.999999935B表表4-1 SQL 4-1 SQL ServerServer 2008 2008數(shù)據(jù)類型數(shù)據(jù)類型( (續(xù)續(xù)) )字符串字符串Char定長的字符型數(shù)據(jù)，最大長度為8000N個字節(jié)個字節(jié)Varchar變長的字符型數(shù)據(jù)，最大長度為8000輸入數(shù)據(jù)長度加輸入數(shù)據(jù)長度加2BText變長的字符型數(shù)據(jù)，最大長度為231-1(2G)=2147483647BUnicode字符串字符串Nchar定長Unicode的字符型數(shù)據(jù)，最大長度為40002 2n n個字節(jié)個字節(jié)Nvarchar變長Unicode的字

6、符型數(shù)據(jù)，最大長度為4000輸入字符個數(shù)輸入字符個數(shù)2倍倍+2BNtext變長Unicode的字符型數(shù)據(jù)，最大長度為231-1 (2G)輸入字符個數(shù)的兩倍輸入字符個數(shù)的兩倍二進(jìn)制字符串二進(jìn)制字符串Binary定長二進(jìn)制數(shù)據(jù)，最大長度為8000N個字節(jié)個字節(jié)Varbinary變長二進(jìn)制數(shù)據(jù)，最大長度為8000輸入數(shù)據(jù)長度加輸入數(shù)據(jù)長度加2BImage變長二進(jìn)制數(shù)據(jù)，最大長度為231-1(2G)不定不定其它數(shù)據(jù)類型其它數(shù)據(jù)類型Cursor游標(biāo)的引用Table一種特殊的數(shù)據(jù)類型，存儲供以后處理的結(jié)果集timestamp時間戳，一個數(shù)據(jù)庫寬度的唯一數(shù)字Uniqueidentifier全球唯一標(biāo)識符GU

7、IDxml存儲xml數(shù)據(jù)的數(shù)據(jù)類型4.3 表的結(jié)構(gòu)設(shè)計o 在關(guān)系數(shù)據(jù)庫中，每一個關(guān)系都體現(xiàn)為一張二維表，使用表來存儲和操作數(shù)據(jù)的邏輯結(jié)構(gòu)，表是數(shù)據(jù)庫中最重要的數(shù)據(jù)對象。如同Excel電子表格，數(shù)據(jù)在表中是按照行和列的格式進(jìn)行組織的，其中每一行代表一個記錄，每一列代表記錄中的一個域。例如，在包含圖書信息的Bookinfo表中每一行代表一種圖書，每一列表示這種圖書某一方面的屬性，如圖書名稱、出版社、價格、作者等等。4.3 表的結(jié)構(gòu)設(shè)計o 在一個數(shù)據(jù)庫中需要包含各個方面的數(shù)據(jù)，所以在設(shè)計數(shù)據(jù)庫時，首先要確定什么樣的表，各表中都應(yīng)該包含哪些數(shù)據(jù)以及各表之間的關(guān)系和存取權(quán)限等等，這個過程稱之為設(shè)計表。在

8、設(shè)計表時需要確定的項目有：o（1）表的名字，每個表都必須有一個名字。表名必須遵循SQL Server 2008的命名規(guī)則，且最好能夠使表名準(zhǔn)確表達(dá)表格的內(nèi)容。o（2）表中各列的名字和數(shù)據(jù)類型，包括基本數(shù)據(jù)類型及自定義數(shù)據(jù)類型。每列采用能反映其實際意義的字段名。4.3 表的結(jié)構(gòu)設(shè)計（3）表中的列是否允許空值。（4）表中的列是否需要約束、默認(rèn)設(shè)置或規(guī)則。（5）表是否需要約束。（6）表所需要的索引的類型和需要建立索引的列。（7）表間的關(guān)系，即確定哪些列是主鍵，哪些是外鍵。 在為各個字段和關(guān)系進(jìn)行命名時注意以下兩點： （1）采用有意義的字段名，盡可能地把字段描述得清楚些。 （2）采用前綴命名，如果多個

9、表里有許多同一類型的字段，不妨用特定表的前綴，來幫助標(biāo)識字段。4.4 4.4 創(chuàng)建和修改表創(chuàng)建和修改表4.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o 表作為數(shù)據(jù)庫的基本組成部分，實際上是關(guān)系數(shù)據(jù)庫中對關(guān)系的一種抽象化描述。表是數(shù)據(jù)存儲的地方。是數(shù)據(jù)庫中最重要的部分，管理好表也就管理好了數(shù)據(jù)庫。我們假設(shè)要創(chuàng)建的數(shù)據(jù)表結(jié)構(gòu)的設(shè)計如下：表4-2 Student_info(學(xué)生信息表)字段名數(shù)據(jù)類型寬度空值否默認(rèn)值主鍵外鍵備注SidChar8否是學(xué)號SnameVarchar10否姓名SgenderBit否1性別SbirthDatetime是出生日期Sdepar

10、tChar6否G02001Department的Did所在系SaddrNvarchar50是地址StelChar14是電話 4.4.1 使用SQL Server Management Studio 工具創(chuàng)建數(shù)據(jù)表1.創(chuàng)建表創(chuàng)建表o 【例例4-1】根據(jù)數(shù)據(jù)庫的設(shè)計，要將學(xué)生相根據(jù)數(shù)據(jù)庫的設(shè)計，要將學(xué)生相關(guān)信息存放在關(guān)信息存放在Student_info表中，需要表中，需要在學(xué)生管理信息系統(tǒng)中創(chuàng)建學(xué)生信息表在學(xué)生管理信息系統(tǒng)中創(chuàng)建學(xué)生信息表Student_info。4.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o （1）啟動SQL Server Managem

11、ent Studio，在對象資源管理器中依次展開“數(shù)據(jù)庫”節(jié)點和Student節(jié)點，右鍵單擊數(shù)據(jù)庫的“表”節(jié)點，選擇“新建表”命令。 4.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o （2）出現(xiàn)如圖4-2所示的表設(shè)計器，在其右上半部分窗口輸入列的基本屬性，包括列的名稱、長度、數(shù)據(jù)類型、為空性等表的基本信息如圖4-3所示。 圖4-2 圖4-34.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o （3）在表設(shè)計器右下半部分的Column Properties指定列的詳細(xì)屬性，包括輸入表是否自動增長等補(bǔ)充信息。如圖4-4所示

12、。圖4-44.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o （4）定義好表中的所有列后，單擊保存按鈕或“文件”菜單中的“保存表名”命令。o （5）在彈出的“選擇名稱”對話框中，為該表鍵入一個名稱，單擊“確定”按鈕。如圖4-5所示。圖4-54.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o 新表創(chuàng)建后，在“對象資源管理器中，可以依次展開“數(shù)據(jù)庫”節(jié)點和Student節(jié)點，查看剛才所建的表，如圖4-6圖4-64.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表2修改表修改表o （1）修改

13、表的結(jié)構(gòu)。）修改表的結(jié)構(gòu)。o 在學(xué)生信息管理系統(tǒng)中將Student_info表中的列名Sid改為Sno。o 啟動SQL Server Management Studio，在對象資源管理器中依次展開“數(shù)據(jù)庫”節(jié)點和Student節(jié)點。o 在“Student”表上單擊右鍵，在彈出的菜單中選擇“設(shè)計”命令，如圖4-7所示。o 在右上部分窗口中，可以修改列名、類型、長度等信息。如圖4-8所示。o 內(nèi)容修改完后，單擊保存按鈕，完成修改。圖4-7圖4-84.4.1 使用SQL Server Management Studio工具創(chuàng)建數(shù)據(jù)表o （2）重命名表）重命名表o 表在創(chuàng)建以后可以根據(jù)需要對其重新命名

14、，在圖4-7中不選擇“設(shè)計”，而選擇“重命名”即可。如下圖4-9。4.4.2 使用Transact-SQL創(chuàng)建和修改表1創(chuàng)建表o 【例例4-2】為了存儲學(xué)生信息，需要在學(xué)生為了存儲學(xué)生信息，需要在學(xué)生信息管理系統(tǒng)中建立一張信息管理系統(tǒng)中建立一張“Student”的表的表，表的結(jié)構(gòu)如例，表的結(jié)構(gòu)如例4-1，該操作使用，該操作使用T-SQL語句如下：語句如下：4.4.2 使用Transact-SQL創(chuàng)建和修改表CREATE TABLE Student_info(Sid Char(8),Sname Varchar(10) ,Sgender Bit,Sbirth Datetime,Sdepart Ch

15、ar(6),Saddr Nvarchar(50),Stel Char(14)4.4.2 使用Transact-SQL創(chuàng)建和修改表o 通過對例4-2的操作，我們總結(jié)T-SQL語句創(chuàng)建表的基本語句格式如下：CREATE TABLE （ ）4.4.2 使用Transact-SQL創(chuàng)建和修改表o 擴(kuò)展擴(kuò)展：CREATE TABLE是創(chuàng)建表的命令，完整的命令參數(shù)可以如下表達(dá)：CREATE TABLE 表名（字段名數(shù)據(jù)類型列級完整性約束條件 ，字段名 數(shù)據(jù)類型 列級完整性約束條件. ，表級完整性約束條件）4.4.2 使用Transact-SQL創(chuàng)建和修改表o注意：注意： o表名：要建立的表名是符合命名規(guī)則

16、的任意字符。在同一數(shù)據(jù)庫中表名應(yīng)該是唯一的。o字段名：是組成表的各個字段的名稱。在一個表中，列名也應(yīng)該是唯一的，而不同的表中允許相同的列名。o數(shù)據(jù)類型：是對應(yīng)列數(shù)據(jù)所采用的數(shù)據(jù)類型。o列級約束：用來對同一字段中的數(shù)據(jù)進(jìn)行限制。o表級約束：如果完整性約束涉及多個字段，則可以將完整性約束定義在表級上。o列級約束與表級約束等關(guān)于完整性的問題我們將在情景5仔細(xì)論述，結(jié)合情景5的內(nèi)容再去建表將對創(chuàng)建數(shù)據(jù)表有更深的認(rèn)識。4.4.2 使用Transact-SQL創(chuàng)建和修改表2修改表修改表o （1）添加列。 【例例4-3】要了解學(xué)生所在的年級，需要在要了解學(xué)生所在的年級，需要在Student_info表中添加

17、表中添加Sgrade字段，其類字段，其類型為型為char，長度為，長度為4。該操作使用。該操作使用T-SQL語句語句完成如下：完成如下： ALTER TABLE Student_info ADD Sgrade char(4)4.4.2 使用Transact-SQL創(chuàng)建和修改表o (2)修改列。o 【例例4-4】考慮到其學(xué)號的長度有些不適宜，考慮到其學(xué)號的長度有些不適宜，需要將長度更改為需要將長度更改為10，類型仍為，類型仍為char。 ALTER TABLE Student_info ALTER COLUMN Sid char(10)4.4.2 使用Transact-SQL創(chuàng)建和修改表o (3

18、)刪除列。o 例4-5 由于系統(tǒng)需求的變更，將把例4-1添加的Sgrade字段刪除o ALTER TABLE Student_info DROP COLUMN Sgrade 4.4.2 使用Transact-SQL創(chuàng)建和修改表o 通過對以上三個應(yīng)用的操作，我們總結(jié)T-SQL語句修改表的基本語句格式如下：ALTER TABLE ALTER COLUMN ADD 完整性約束DROP COLUMN 注意：表結(jié)構(gòu)的修改語句沒有修改字段名的功能，想要修改列名，只有將原來的列名刪除，再添加新的列。另一種做法是，在SSMS管理器中進(jìn)行修改。4.54.5數(shù)據(jù)表的查看和刪除數(shù)據(jù)表的查看和刪除o 4.5.1 使用

19、SSMS查看和刪除表o 1查看表o【例例4-6】查看剛才在學(xué)生信息管理系統(tǒng)查看剛才在學(xué)生信息管理系統(tǒng)Student中中已經(jīng)建立好的已經(jīng)建立好的Student_info表中的信息。表中的信息。o(1)啟動SQL Server Management Studio，在對象資源管理器中依次展開“數(shù)據(jù)庫”節(jié)點、Student節(jié)點和表節(jié)點。o(2)在“Student_info”表上單擊鼠標(biāo)右鍵，在彈出的菜單中選擇“屬性”命令 o(3)打開“表屬性”對話框，可以查看表的常規(guī)、權(quán)限等詳細(xì)信息。4.5.1 4.5.1 使用使用SSMSSSMS查看和刪除表查看和刪除表o 2刪除表 根據(jù)數(shù)據(jù)管理的需要，有時要刪除數(shù)

20、據(jù)庫中 的某些表以釋放更多的空間，刪除表時特別要注意的是表的結(jié)構(gòu)定義、數(shù)據(jù)、約束和索引等都會被永久刪除。4.5.1 使用SSMS查看和刪除表o 【例例4-7】刪除在刪除在Student數(shù)據(jù)庫中已經(jīng)數(shù)據(jù)庫中已經(jīng)建立的建立的Student_info表。表。o(1)啟動SQL Server Management Studio，在對象資源管理器中依次展開“數(shù)據(jù)庫”節(jié)點、Student節(jié)點和表節(jié)點。o(2)在Student_info表上單擊鼠標(biāo)右鍵，在彈出的菜單中選擇“刪除”命令 o(3)打開“刪除對象”對話框，單擊“確定”按鈕即完成表的刪除 4.5.2 使用T-SQL查看和刪除表o 1查看表 使用存儲

21、過程sp_help可以查看表的相關(guān)信息。儲存過程sp_help的基本語句格式如下： Sp_help 表名參數(shù)含義：表名是指要查看表的名稱。比如，要完成例4-6也可以使用以下語句： Sp_help student_info4.5.2 使用T-SQL查看和刪除表o 2刪除表使用DROP TABLE可以刪除表，其基本的格式如下： DROP TABLE表名參數(shù)含義：表名是指要刪除表的名稱。比如，要完成例4-7，也可以使用以下語句： DROP TABLE Student_info 該語句一旦執(zhí)行，表中的數(shù)據(jù)以及在此表上建立的約束、索引都將被刪除掉，建立在該表上的視圖依然保留，但已經(jīng)無法引用，因此，執(zhí)行刪

22、除操作要十分謹(jǐn)慎。4.6 4.6 數(shù)據(jù)記錄的操作數(shù)據(jù)記錄的操作o 4.6.1 使用使用SSMS進(jìn)行數(shù)據(jù)記錄的操作進(jìn)行數(shù)據(jù)記錄的操作o(1)啟動SQL Server Management Studio，在對象資源管理器中依次展開“數(shù)據(jù)庫”節(jié)點、Student節(jié)點和表節(jié)點。o(2)在“Student_info”表上單擊鼠標(biāo)右鍵，在彈出的菜單中選擇“編輯前200行” 。o(3)在SQL Server Management Studio中可以直接在圖4-16所示的表格中完成添加、修改表的操作。4.6.1 使用SSMS進(jìn)行數(shù)據(jù)記錄的操作圖4-164.6.1 使用SSMS進(jìn)行數(shù)據(jù)記錄的操作 (4)要刪除記

23、錄時，在選定的記錄單擊鼠標(biāo)右鍵，在彈出菜單中選擇“刪除”命令即可，如圖4-17。記錄操作完成后，根據(jù)提示完成保存操作。圖4-174.6.1 使用SSMS進(jìn)行數(shù)據(jù)記錄的操作o 注意：注意：o 添加、修改和刪除記錄并不是總能正確執(zhí)行，數(shù)據(jù)必須添加、修改和刪除記錄并不是總能正確執(zhí)行，數(shù)據(jù)必須遵循一定的規(guī)則，這個規(guī)則就是我們下一情景要講的數(shù)遵循一定的規(guī)則，這個規(guī)則就是我們下一情景要講的數(shù)據(jù)完整性規(guī)則。據(jù)完整性規(guī)則。o 在添加和修改過程中按在添加和修改過程中按“Esc”鍵可取消不符合規(guī)則的鍵可取消不符合規(guī)則的數(shù)據(jù)輸入。數(shù)據(jù)輸入。4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o 1插入記錄插入記錄o 【例例

24、4-7】新生入學(xué)，在新生入學(xué)，在Student_info表中表中插入一條新數(shù)據(jù)。插入一條新數(shù)據(jù)。 INSERT INTO Student_info VALUES（20100201,黃旭明,男, 1992-12-23,G02001 ,NULL4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o 通過以上應(yīng)用總結(jié)出插入記錄的語句格式 :INSERT INTO ,VALUES(,) 4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o 2修改記錄o 【例例4-8】個別學(xué)生信息有時會發(fā)生變更，個別學(xué)生信息有時會發(fā)生變更，比如學(xué)生轉(zhuǎn)專業(yè)，所在系發(fā)生變化等。將比如學(xué)生轉(zhuǎn)專業(yè)，所在系發(fā)生變

25、化等。將“ 2 0 1 0 0 2 0 1 ” 記 錄 的 所 在 系 由記 錄 的 所 在 系 由G02001改為改為G02002。UPDATE Student_infoSET Sdepart = G02002WHERE Sid = 201002014.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o 如果不指定WHERE條件，則會修改所有記錄，這是要十分小心的地方，通過以上應(yīng)用，我們總結(jié)出修改記錄語句格式為：UPDATE SET = ， = FROM WHERE 4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o 3.刪除記錄o 【例例4-9】學(xué)生張自立已經(jīng)退學(xué)，需要在學(xué)學(xué)生張自立已經(jīng)退學(xué)，需要在學(xué)

26、生信息表中刪除該學(xué)生信息。生信息表中刪除該學(xué)生信息。DELETE FROM STUDENTWHERE Sname = 張自立4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o 通過對實例的應(yīng)用，可以總結(jié)出刪除記錄語句的基本格式如下：DELETEFROM WHERE 4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o DELETE語句的功能是從指定表中刪除滿足條件的WHERE字句條件的所有記錄。如果省略了WHERE條件字句，表示刪除表中全部記錄，但表的定義仍存在，也就是說DELETE刪除的是表中的數(shù)據(jù)，而不是關(guān)于表的定義。o 【例例4-10】刪除學(xué)生信息表中所有學(xué)生信息刪除學(xué)生信息表中所有學(xué)生信息。 D

27、ELETE FROM STUDENT4.6.2 使用T-SQL進(jìn)行數(shù)據(jù)記錄的操作o DELETE語句使STUDENT表成為空表，它刪除了STUDENT表中的所有記錄。刪除表中所有記錄也可以使用TRUNCATE TABLE 語句來完成。具體如下： TRUNCATE TABLE STUDENT任務(wù)任務(wù)3 3 創(chuàng)建創(chuàng)建studentstudent數(shù)據(jù)庫的數(shù)據(jù)表數(shù)據(jù)庫的數(shù)據(jù)表表4-3 Department_info（系部信息表）字段名數(shù)據(jù)類型寬度空值否默認(rèn)值主鍵外鍵備注DidChar6否是系編號DnameVarchar18否系名DdeanVarchar10是系主任DtelChar14是聯(lián)系電話DaddrNvarchar50是系所在地址表4-4 Student_info(學(xué)生信息表)字段名數(shù)據(jù)類型寬度空值否默認(rèn)值主鍵外鍵備注SidChar8否是學(xué)號SnameVarchar10否姓名SgenderChar2否性別SbirthDatetime是出生日期SdepartChar6否G02001Department的Di