實(shí)驗(yàn)03：數(shù)據(jù)庫中表的基本操作講解材料

表的基本操作2024/9/262主要內(nèi)容數(shù)據(jù)庫表SQLServer的數(shù)據(jù)類型創(chuàng)建數(shù)據(jù)庫表定義約束管理數(shù)據(jù)庫表2024/9/263數(shù)據(jù)庫表表的概述表是數(shù)據(jù)庫中最基本的對象。許多數(shù)據(jù)庫對象，如索引、視圖都是以表為基礎(chǔ)的。它與電子表格類似，數(shù)據(jù)在表中是按照行和列的格式來組織的。每一行代表一條唯一的記錄，每一列代表記錄中的一個域。例如，一個包含學(xué)生基本信息的數(shù)據(jù)表，表中的每一行代表一個學(xué)生信息，每一列代表學(xué)生的一項(xiàng)詳細(xì)資料，如學(xué)號、姓名、性別、聯(lián)系方式等。2024/9/264數(shù)據(jù)庫表表的分類在性質(zhì)上，SQLServer將表分為兩種永久表：創(chuàng)建后一直存儲在數(shù)據(jù)庫文件中，除非用戶刪除該表；臨時表：臨時表是系統(tǒng)運(yùn)行過程中由系統(tǒng)創(chuàng)建，當(dāng)用戶退出或系統(tǒng)修復(fù)時，臨時表將被自動被刪除。在類型上，SQLServer將表分為四種標(biāo)準(zhǔn)表。在數(shù)據(jù)庫中，最常用的就是標(biāo)準(zhǔn)表，它用來為數(shù)據(jù)庫提供存儲數(shù)據(jù)的空間。標(biāo)準(zhǔn)表簡稱表，是最重要、最基本的表。系統(tǒng)表。系統(tǒng)表中存儲了有關(guān)SQLServer的配置、數(shù)據(jù)庫設(shè)置、用戶信息、表對象的描述等系統(tǒng)信息。2024/9/266數(shù)據(jù)庫表數(shù)據(jù)完整性存儲在數(shù)據(jù)庫中數(shù)據(jù)的一致性和正確性。為保證數(shù)據(jù)的完整性，SQLServer提供了定義、檢查和控制數(shù)據(jù)的完整性的機(jī)制。數(shù)據(jù)的完整性又進(jìn)一步分為實(shí)體完整性、域完整性、參照完整性和用戶定義完整性四種。實(shí)體完整性也稱行完整性。它將行定義為特定表的唯一實(shí)體。例如在有多個學(xué)生信息的表中，學(xué)號那個列對應(yīng)的值每一行必須不相同，否則，這將造成學(xué)生信息管理的混亂。域完整性也稱列完整性。用它來定義輸入的數(shù)據(jù)值，是否具有正確的數(shù)據(jù)類型、格式以及有效的數(shù)據(jù)范圍。2024/9/267數(shù)據(jù)庫表參照完整性是保證參照與被參照兩個表中數(shù)據(jù)的一致性。例如，在學(xué)生基本信息表中有學(xué)號列，在成績表中也有學(xué)號這個列，而且兩個表的學(xué)號必須一致。用戶定義完整性。為了反映用戶的特殊需求，體現(xiàn)用戶的業(yè)務(wù)規(guī)則，稱為用戶定義完整性。在數(shù)據(jù)庫系統(tǒng)中必須采取一些措施來防止混亂數(shù)據(jù)的產(chǎn)生。在SQLServer中，數(shù)據(jù)的完整性是通過建立和使用約束來保證的。約束是SQLServer強(qiáng)制實(shí)行的應(yīng)用規(guī)則，它通過限制列、行、表中的數(shù)據(jù)，來保證數(shù)據(jù)的完整性。當(dāng)刪除表時，表所帶的約束也隨之被刪除。在SQLServer中，約束包括CHECK約束、PRIMARYKEY約束、FOREIGNKEY約束、UNIQUE約束和DEFAULT約束等。2024/9/268SQLServer的數(shù)據(jù)類型在創(chuàng)建表時，涉及數(shù)據(jù)表的結(jié)構(gòu)問題，也就是涉及數(shù)據(jù)表中各列的數(shù)據(jù)格式：是數(shù)值？是字符？是日期還是貨幣？是圖像等其他格式？因此，在講述數(shù)據(jù)表的操作之前，必須介紹SQLServer的數(shù)據(jù)類型。在SQLServer的數(shù)據(jù)表中，列的數(shù)據(jù)類型既可以是系統(tǒng)提供的數(shù)據(jù)類型，也可以是用戶自定義的數(shù)據(jù)類型。SQLServer系統(tǒng)提供了豐富的數(shù)據(jù)類型，表11-1列出了SQLServer所支持的數(shù)據(jù)類型。2024/9/269數(shù)據(jù)類型數(shù)據(jù)類型說明BIGINT從-263~264-1的整型數(shù)據(jù)INT從-231~232-1的整型數(shù)據(jù)SMALLINT從-215~216-1的整型數(shù)據(jù)TINYINT從0~255的整型數(shù)據(jù)BIT非1即0的整型數(shù)據(jù)DECIMAL從-1038+1~1038-1的固定精度和小數(shù)位數(shù)的數(shù)值數(shù)據(jù)類型NUMERIC相當(dāng)于十進(jìn)制數(shù)MONEY從-263~263-1的貨幣型數(shù)據(jù)，精確到貨幣單位的萬分之一SMALLMONEY從-214748.3648~214748.3647的貨幣型數(shù)據(jù)，精確到貨幣單位的萬分之一FLOAT從-1.79E-308~1.79E+308的浮點(diǎn)精度數(shù)字?jǐn)?shù)據(jù)2024/9/2610數(shù)據(jù)類型數(shù)據(jù)類型說明REAL從-3.40E-38~3.40E+38的浮點(diǎn)精度數(shù)字?jǐn)?shù)據(jù)DATETIME從1753年1月1日到9999年12月31日的日期和時間數(shù)據(jù)，精確到三百分之一秒（11.33毫秒）SMALLDATETIME從1900年1月1日到9999年12月21日的日期和時間數(shù)據(jù)，精確到一分鐘CHAR最大長度為8000個字符的固定長度非Unicode字符數(shù)據(jù)VARCHAR最大長度為8000個字符的可變長度非Unicode字符數(shù)據(jù)Text最大長度為231-1個字符的可變長度非Unicode字符數(shù)據(jù)NCHAR最大長度為4000個字符的固定長度Unicode字符數(shù)據(jù)NVARCHAR最大長度為4000個字符的可變長度Unicode字符數(shù)據(jù)NTEXT最大長度為231-1個字符的可變長度Unicode字符數(shù)據(jù)2024/9/2611數(shù)據(jù)類型數(shù)據(jù)類型說明BINARY最大長度為8000個字符的固定長度二進(jìn)制數(shù)據(jù)VARBINARY最大長度為8000個字符的可變長度二進(jìn)制數(shù)據(jù)IMAGE最大長度為231-1個字符的可變長度二進(jìn)制數(shù)據(jù)CURSOR對光標(biāo)的引用SQL_VARIANT存儲SQLServer支持的數(shù)據(jù)類型（TEXT,NTEXT,TIMESTAMP和SQL_VARIANT除外）值的數(shù)據(jù)類型TABLE用于存儲結(jié)果集合供以后處理的特殊數(shù)據(jù)類型TIMESTAMP整個數(shù)據(jù)庫中都有唯一的一個數(shù)字，隨著行的每次更新而更新UNIQUEIDENTIFIER全局唯一標(biāo)識符（GUID）2024/9/2612數(shù)據(jù)類型空值（NULL）先來了解空值的概念。數(shù)據(jù)列在定義后，還需要確定該列是否允許空值?？罩低ǔＪ俏粗摹⒉荒苡玫幕?qū)⒃谝院筇砑拥臄?shù)據(jù)。允許一個列為空值，則向表中輸入記錄值時，可不為該列給出具體值；不允許為空值時，則在輸入時，必須給出具體的值?？罩蹬c空格字符或數(shù)字0是不同的，空格實(shí)際上是一個有效的字符，0則表示一個有效的數(shù)字。2024/9/2613數(shù)據(jù)類型字符型字符型數(shù)據(jù)類型主要用來存儲由字母、數(shù)字和符號組成的字符串，它又分為定長類型和變長類型。SQLServer提供三種字符型數(shù)據(jù)類型，分別為char、varchar和text。Char列中可以有字母、數(shù)字和符號，甚至是Tab鍵和空格鍵，但不包含其他的非可打印字符。Char字符列是定長的，如果定義的字符列為400個字符大小，那么即使列中的數(shù)據(jù)小于400個字符，這些數(shù)據(jù)也要占用400個字符的磁盤空間。Varchar列存儲變長的字符數(shù)據(jù)。2024/9/2614數(shù)據(jù)類型二進(jìn)制型二進(jìn)制型數(shù)據(jù)是指字符串是由二進(jìn)制值組成，而不是由字符組成。該類型通常用于時間標(biāo)記(timestamp)和image類型。對于二進(jìn)制型數(shù)據(jù)的存儲來說，SQLServer提供三種數(shù)據(jù)類型，分別為binary、varbinary和image。binary用于存儲長度固定的二進(jìn)制字符串，varbinary用于存儲長度可變的二進(jìn)制字符串，image用于存儲大的二進(jìn)制字符串(理論上每行可達(dá)2GB)。2024/9/2615數(shù)據(jù)類型整型和精確數(shù)值型SQLServer2005提供的整型和精確數(shù)值類型有bit、int、smallint、tinyint和decimal、numeric。最常用的是int和numeric類型。int類型是指取值在-2147483648～2147483647之間的整數(shù)，numeric類型則是十進(jìn)制數(shù)。bit數(shù)據(jù)類型可用于存儲邏輯數(shù)據(jù)，可用作狀態(tài)標(biāo)志位，它只存儲1或者0。并且該類型的值不允許為空值、不允許建立索引、幾個bit列可占用同一字節(jié)。2024/9/2616數(shù)據(jù)類型浮點(diǎn)型SQLServer2005提供了float和real類型，來表示浮點(diǎn)數(shù)和實(shí)數(shù)。float整型數(shù)據(jù)取值范圍是-1.79E+308～1.79E+308，real型數(shù)據(jù)的取值范圍是-11.40E+38～11.40E+38。real類型存儲在四個字節(jié)中，可以在real數(shù)據(jù)類型中存儲正的或者負(fù)的十進(jìn)制值。如果不指定float數(shù)據(jù)類型的長度，它會被存儲在8個字節(jié)中。2024/9/2617數(shù)據(jù)類型日期型SQLServer2005可以用datetime和smalldatetime數(shù)據(jù)類型來存儲日期數(shù)據(jù)和時間數(shù)據(jù)。smalldatetime的精度較低，包含的日期范圍也較窄，但占用的空間小。datetime類型數(shù)據(jù)的取值范圍是1753年1月1日到9999年12月31日?？梢允÷詃atetime中的部分值，但如果全部省略，則默認(rèn)的取值為1900，1，1，12:00:00:00AM。如果省略的是時間部分，默認(rèn)值為12:00:00:00。如果省略的是日期部分，則默認(rèn)值為1,l,1900。2024/9/2618數(shù)據(jù)類型字符串?dāng)?shù)據(jù)類型SQLServer2005提供三種Unicode字符串?dāng)?shù)據(jù)類型，分別為nchar、nvarchar和ntext。SQLServerUnicode數(shù)據(jù)類型是基于SQL-92標(biāo)準(zhǔn)中的NationalCharacter數(shù)據(jù)類型的。SQL-92使用前綴字符號n來表示這些數(shù)據(jù)類型和值。在使用Unicode數(shù)據(jù)類型時，一個列可以存儲由Unicode標(biāo)準(zhǔn)所定義的任何字符，該字符包括各種字符集中所定義的所有字符。其中，ntext數(shù)據(jù)類型用來存儲大量的文本，存儲的數(shù)據(jù)通常是直接能夠輸出到顯示設(shè)備上的字符，顯示設(shè)備可以是顯示器、窗口或者打印機(jī)。2024/9/2619數(shù)據(jù)類型用戶自定義數(shù)據(jù)類型用戶自定義數(shù)據(jù)類型，是基于SQLServer系統(tǒng)提供的數(shù)據(jù)類型。當(dāng)多個表的列中要存儲同樣類型的數(shù)據(jù)，并且確保這些列具有完全相同的數(shù)據(jù)類型、長度和是否為空屬性時，可使用用戶自定義數(shù)據(jù)類型。創(chuàng)建用戶自定義數(shù)據(jù)類型必須提供名稱、新數(shù)據(jù)類型所依據(jù)的系統(tǒng)數(shù)據(jù)類型、數(shù)據(jù)類型是否允許空值。2024/9/2620創(chuàng)建與管理表以學(xué)生信息系統(tǒng)為例，介紹如何用SSMS圖形界面來創(chuàng)建表，以及對這些表進(jìn)行管理。學(xué)生信息系統(tǒng)共包括學(xué)生信息表（student）、課程信息表（course）、選課信息表（select）三張表，具體表結(jié)構(gòu)如表11-2、表11-3和表11-4所示。2024/9/2621創(chuàng)建表列名數(shù)據(jù)類型是否允許為空主鍵備注SnoChar（10）否是學(xué)號SnameVarchar(10)否否學(xué)生姓名SsexNchar(2)是否性別BirthdayDatetime是否生日SdeptNchar（20）是否系TelVarchar(13)是否電話student表結(jié)構(gòu)2024/9/2622創(chuàng)建表列名數(shù)據(jù)類型是否允許為空主鍵備注CnoChar（10）否是課程編號CnameVarchar(50)否否課程名稱course表結(jié)構(gòu)select表結(jié)構(gòu)列名數(shù)據(jù)類型是否允許為空主鍵備注SnoChar（10）否是學(xué)號CnoChar(10)否是課程編號ScoreInt是否成績2024/9/2623創(chuàng)建表創(chuàng)建步驟在SSMS窗口中，展開“bnuep”選項(xiàng)，右擊“表”選項(xiàng)，在彈出的快捷菜單中選擇“新建表”命令，如圖11-2所示。2024/9/2624創(chuàng)建表在彈出的“編輯”面板中，分別輸入各列的名稱、數(shù)據(jù)類型、長度、是否允許為空等屬性（可以參考表11-2、表11-3和表11-4所示的表結(jié)構(gòu)），如圖11-3所示。2024/9/2625創(chuàng)建表輸入完各列屬性后，單擊“保存”按鈕，彈出“選擇名稱”對話框，如圖11-4所示。在“選擇名稱”對話框中輸入表的名稱student，點(diǎn)擊“確定”按鈕表創(chuàng)建完成。按照同樣的方法和步驟創(chuàng)建course表和score表。2024/9/2626約束SQLServer中有五種約束類型，分別是CHECK、DEFAULT、PRIMARYKEY、FOREIGNKEY和UNIQUE。CHECK約束CHECK約束用于限制輸入一列或多列的值的范圍，通過邏輯表達(dá)式來判斷數(shù)據(jù)的有效性，也就是一個列的輸入內(nèi)容必須滿足CHECK約束的條件，否則，數(shù)據(jù)無法正常輸入，從而強(qiáng)制數(shù)據(jù)的域完整性。DEFAULT約束若在表中某列定義了DEFAULT約束，用戶在插入新的數(shù)據(jù)行時，如果該列沒有指定數(shù)據(jù)，那么系統(tǒng)將默認(rèn)值賦給該列，當(dāng)然該默認(rèn)值也可以是空值。2024/9/2627約束PRIMARYKEY約束在表中經(jīng)常有一列或多列的組合，其值能唯一標(biāo)識表中的每一行。這樣的一列或多列成為表的主鍵，通過它可以強(qiáng)制表的實(shí)體完整性。一個表有且只有一個主鍵，而且主鍵約束中的列不能為空值。如將student表中學(xué)號Sno設(shè)為該表的主鍵，因?yàn)樗芪ㄒ粯?biāo)識該表，且該列的值不為空。如果主鍵約束定義在不止一列上，則一列中的值可以重復(fù)，但主鍵約束定義中的所有列組合的值，必須唯一，因?yàn)樵摻M合列是表的主鍵。2024/9/2628約束FOREIGNKEY約束外鍵，是用于建立和加強(qiáng)兩個表（主表與從表）的一列或多列數(shù)據(jù)之間的連接的，當(dāng)添加、修改或刪除數(shù)據(jù)時，通過參照完整性來保證它們之間的數(shù)據(jù)的一致性。定義表間的參照完整性的順序是先定義主表的主鍵，再對從表定義外鍵。UNIQUE約束用于確保表中的行在非主鍵中沒有相同的列值。與PRIMARYKEY約束類似，UNIQUE約束也強(qiáng)制唯一性，為表中的一列或多列提供實(shí)體完整性。但UNIQUE約束用于非主鍵的一列或多列組合，且一個表可以定義多個UNIQUE約束，另外UNIQUE約束可以用于定義多列組合。2024/9/2629CHECK在Ssex上創(chuàng)建約束在SSMS窗口中，右擊dbo.student選項(xiàng)，在彈出的快捷菜單中選擇“修改”命令，選中Ssex后右擊數(shù)遍，在彈出的快捷菜單中選擇“CHECK約束”，如圖所示：2024/9/2630CHECK單擊“添加”按鈕，單擊“表達(dá)式”后面的按鈕，進(jìn)入如下圖所示的“CHECK約束表達(dá)式”對話框。如果已經(jīng)在該列上創(chuàng)建了約束，則該操作將查看之前創(chuàng)建的CHECK約束表達(dá)式。在文本框中輸入約束表達(dá)式“Ssex=‘男’ORSsex=‘女’”。2024/9/2631CHECK最后，在“設(shè)計(jì)表”窗口單擊“保存”按鈕，完成了創(chuàng)建和保存CHECK約束的操作。用戶輸入時，若輸入性別不是“男”或“女”，系統(tǒng)將報告輸入無效。刪除約束。2024/9/2632DEFAULT在Ssex上創(chuàng)建DEFAULT約束選擇“Ssex”列，在“列屬性”選項(xiàng)卡中選擇“默認(rèn)值或綁定”，在右側(cè)的文本框中輸入“男”，單擊“保存”按鈕。刪除DEFAULT約束。2024/9/2633PRIMARYKEY將Sno定義為主鍵。選擇“Sno”列，右擊，在彈出的快捷菜單中選擇“設(shè)置主鍵”命令。若主鍵由多列組成，先選中此列，按住Ctrl不放，同時用鼠標(biāo)單擊選擇其他列，然后單擊“設(shè)置主鍵”按鈕。移除主鍵。2024/9/2634FOREGINKEY創(chuàng)建student表與select表之間的外鍵約束。檢查student表中是否將“Sno”設(shè)置為主鍵。選擇“select”表，右擊選擇“修改”，單擊“關(guān)系”按鈕，在彈出的“外鍵關(guān)系”對話框中，單擊“添加”按鈕，如圖所示：2024/9/2635FOREGINKEY選中“表和列規(guī)范”單擊后面的按鈕，進(jìn)入下圖所示的界面。在“主鍵表”下拉列表中選擇student，單擊主鍵表的下拉列表框選擇Sno，在“外鍵表”下拉列表框中選擇Sno。2024/9/2636UNIQUE給Sname添加UNIQUE約束在“修改”定義窗口完成所有列的定以后，單擊“管理索引和鍵”按鈕，打開“索引/鍵”對話框，單擊“添加”按鈕，在“列”下拉列表中選擇“Sname”，在“是唯一的”下拉列表中選擇”是“。2024/9/2637修改表結(jié)構(gòu)修改表名。SQLServer允許修改一個表的名字，當(dāng)表名改變后，與此相關(guān)的某些對象（如視圖，存儲過程等）將無效，因?yàn)樗鼈兌寂c表名有關(guān)。因此，建議一般不要隨便更改一個已有的表名，特別是在其上已經(jīng)定義了視圖等對象。右擊“dbo.student”，在彈出的快捷菜單中，選擇“重命名”命令，在原表上輸入表的新名稱即可。2024/9/2638修改表結(jié)構(gòu)增加列。給student增加”宿舍“屬性。右擊“dbo.student”，在彈出的菜單中選擇“修改”命令。接著在“設(shè)計(jì)表”面板中，單擊“第一個空白行”，輸入列名“domitory(宿舍)”，數(shù)據(jù)類型選擇“varchar(20)”，并選中“允許空”復(fù)選框。然后點(diǎn)擊“保存”按鈕。2024/9/2639修改表結(jié)構(gòu)刪除列。刪除剛才在student表中建立的“domitory”列。打開student表的“修改”面板，右擊“domitory”列，在彈出的快捷菜單中選擇“刪除列”命令。該列即被刪除，最后單擊“保存”按鈕，以保存修改的結(jié)構(gòu)。修改已有列的屬性。和增加、刪除列類似，打開表的“修改”面板，可以對已有列的列名、數(shù)據(jù)類型、長度以及是否允許為空值等屬性直接進(jìn)行修改。修改完畢后，單擊“保存”按鈕以保存修改的結(jié)構(gòu)。但是，在表中已