TP2 SQL Server數(shù)據(jù)庫表管理

1、第二章第二章SQL Server數(shù)據(jù)庫表管理1回顧q數(shù)據(jù)庫的作用q數(shù)據(jù)庫的發(fā)展過程以及當前流行的數(shù)據(jù)庫q數(shù)據(jù)庫基本概念：數(shù)據(jù)存儲方式、實體和記錄、數(shù)據(jù)庫和數(shù)據(jù)庫表、數(shù)據(jù)冗余和數(shù)據(jù)完整性q使用企業(yè)管理器創(chuàng)建組、注冊、用戶q在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫q收縮、移動和配置數(shù)據(jù)庫的選項2本章目標q使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫表q設(shè)置表的主鍵、外鍵和建立表之間的關(guān)系q為表增加約束3再論數(shù)據(jù)完整性 2-1數(shù)據(jù)完整性數(shù)據(jù)完整性可靠性可靠性準確性準確性4再論數(shù)據(jù)完整性 2-2q 數(shù)據(jù)存放在表中q “數(shù)據(jù)完整性的問題大多是由于設(shè)計引起的”q 創(chuàng)建表的時候，就應(yīng)當保證以后數(shù)據(jù)輸入是正確的錯誤的數(shù)據(jù)、不符合要求的數(shù)據(jù)不允許

2、輸入創(chuàng)建表：保證數(shù)據(jù)的完整性創(chuàng)建表：保證數(shù)據(jù)的完整性 = 實施完整性約束實施完整性約束5完整性包括2-1q 輸入的類型是否正確？年齡必須是數(shù)字q 輸入的格式是否正確？身份證號碼必須是18位q 是否在允許的范圍內(nèi)？性別只能是”男”或者”女”q 是否存在重復(fù)輸入？學員信息輸入了兩次q 是否符合其他特定要求？信譽值大于5的用戶才能夠加入會員列表q 列值要求（約束）整行要求（約束）6完整性包括2-2域完整性實體完整性參照完整性自定義完整性7實體完整性河南新鄉(xiāng)趙可以0010016河南新鄉(xiāng)張麗鵑0010015江西南昌雷銅0010014湖南新田吳蘭0010013山東定陶李山0010012.地址地址姓名姓名學

3、號學號江西南昌雷銅0010014約束方法：唯一約束、主鍵約束、標識列約束方法：唯一約束、主鍵約束、標識列8域完整性河南新鄉(xiāng)趙可以0010016河南新鄉(xiāng)張麗鵑0010015江西南昌雷銅0010014湖南新田吳蘭0010013山東定陶李山0010012.地址地址姓名姓名學號學號湖北江門李亮8700000000約束方法：限制數(shù)據(jù)類型、檢查約束、外鍵約束、約束方法：限制數(shù)據(jù)類型、檢查約束、外鍵約束、默認值、非空約束默認值、非空約束9參照完整性河南新鄉(xiāng)趙可以0010016河南新鄉(xiāng)張麗鵑0010015江西南昌雷銅0010014湖南新田吳蘭0010013山東定陶李山0010012地址地址姓名姓名學號學號98

4、0010021數(shù)學約束方法：外鍵約束約束方法：外鍵約束10自定義完整性AV121322喬峰CV0016AV372133玄痛CV0015AV378291沙悟凈AV0014AV378290豬悟能AV0013AV378289AV378289孫悟空AV0012.會員證會員證用戶姓名用戶姓名用戶編號用戶編號約束方法：規(guī)則、存儲過程、觸發(fā)器約束方法：規(guī)則、存儲過程、觸發(fā)器觸發(fā)器：檢查信用值11表操作在企業(yè)管理器中演示打開表、數(shù)據(jù)輸入等操作12創(chuàng)建數(shù)據(jù)庫表-113SQL Server的數(shù)據(jù)類型14二種：datetime,smalldatetime。說明方法： datetime用 8個字節(jié)存儲日期時間。日期范

5、圍1753/1/19999/12/31。精度到3/100秒。 smalldatetime用4個字節(jié)存儲。范圍1900/1/12079/6/6。精度到分。常量用單引號引起，有多種格式。時間日期數(shù)據(jù)類型：08/10/2004或08-10-2004-分隔符數(shù)字方式08102004 -數(shù)字方式A 10,2004 -英文數(shù)字例：常量2004年8月10日下午5點30分：2004-8-10 17：30：00或者8/10/2004 5:30:00PM15bigint (大整數(shù))：8個字節(jié)。Int或integer(整數(shù))：4個字節(jié)(-231_ 231-1) 。smallint (短整數(shù)):2個字節(jié)(-32768

6、-32767)。tinyint (微短整數(shù)):1個字節(jié)(0-255)。Bit:只能取0、1 或 NULL。bit類型的字段或變量輸入0和 NULL以外的數(shù)值都作為1處理。整數(shù)數(shù)據(jù)類型：16二種：decimal,numeric，等價。存儲實數(shù)值。由數(shù)字09、正負號、小數(shù)點組成。最多38位。由整數(shù)部分和小數(shù)部分構(gòu)成。說明方法：decimal（p,s)或numeric （p,s) 。 P為精度，S為小數(shù)位數(shù)；默認精度18位，默認小數(shù)位0位。精確數(shù)據(jù)類型：例：例：decimal(12,4)表示數(shù)值中表示數(shù)值中共有共有12位，位，4位小數(shù)，位小數(shù)，8位整數(shù)。位整數(shù)。字段的精度為字段的精度為12位。位。1

7、7二種：money,smallmoney。說明方法： money 兩個4個字節(jié)整數(shù)存儲。前面表示貨幣的整數(shù)部分，后面的4個字節(jié)表示小數(shù)部分。小數(shù)點后保留4位數(shù)。 Smallmoney分別用兩個字節(jié)表示貨幣的整數(shù)部分和小數(shù)部分，小數(shù)點后保留4位數(shù)。貨幣常量前加幣值符號。例：$-120.35 貨幣數(shù)據(jù)類型：18思考q 電話號碼一般使用什么數(shù)據(jù)類型存儲?q 性別一般使用什么數(shù)據(jù)類型存儲?q 年齡信息一般使用什么數(shù)據(jù)類型存儲?q 照片信息一般使用什么數(shù)據(jù)類型存儲?q 薪水一般使用什么數(shù)據(jù)類型存儲?19創(chuàng)建數(shù)據(jù)庫表-220思考q 學員姓名允許為空嗎?q 家庭地址允許為空嗎?q 電子郵件信息允許為空嗎?q

8、 考試成績允許為空嗎?21自定義數(shù)據(jù)類型通過存儲過程sp_addtype創(chuàng)建新的類型；用sp_droptype刪除不再使用的自定義類型。sp_addtype type_namesystem_type;“NULL” ”NOT NULL” ”NONULL” 自定義數(shù)據(jù)類型：22自定義數(shù)據(jù)類型例 在BOOKS數(shù)據(jù)庫中定義一個新的數(shù)據(jù)類型，類型名稱為“圖書編碼類型”，字符型，長度為12位，不允許為空。sp_addtype 圖書編碼類型, char(12) , not null創(chuàng)建用戶自定義數(shù)據(jù)類型：23q練習：使用系統(tǒng)存儲過程定義一個新的數(shù)據(jù)類型，類型名稱為“價格類型”，貨幣型，允許為空。USE BO

9、OKSGOEXEC sp_addtype 價格類型, money , null24自定義數(shù)據(jù)類型例 刪除用戶自定義數(shù)據(jù)類型“圖書編碼類型”。USE BOOKSGOEXECUTE sp_droptype 圖書編碼類型刪除用戶自定義數(shù)據(jù)類型：25創(chuàng)建數(shù)據(jù)庫表-326思考q 在主鍵列輸入的數(shù)值，允許為空嗎?q 一個表可以有多個主鍵嗎?q 在一個學校數(shù)據(jù)庫中，如果一個學校內(nèi)允許重名的學員，但是一個班級內(nèi)不允許學員重名，可以組合班級和姓名兩個字段一起來作為主鍵嗎？27選擇主鍵的原則q最少性q盡量選擇單個鍵作為主鍵q穩(wěn)定性q盡量選擇數(shù)值更新少的列作為主鍵28創(chuàng)建數(shù)據(jù)庫表-4q表中沒有合適的列作為主鍵怎么辦

10、？29思考q 標識列允許為字符數(shù)據(jù)類型嗎？q 如果標識列A的初始值為1，增長量為2，則輸入三行數(shù)據(jù)以后，再刪除兩行，下次再輸入數(shù)據(jù)行的時候，標識值從多少開始？30創(chuàng)建數(shù)據(jù)庫表-531創(chuàng)建數(shù)據(jù)庫表-6演示建立主-外鍵關(guān)系32關(guān)系圖演示關(guān)系圖33主表和從表q 1、當主表中沒有對應(yīng)的記錄時，不能將記錄添加到子表成績表中不能出現(xiàn)在學員信息表中不存在的學號；q 2、不能更改主表中的值而導(dǎo)致子表中的記錄孤立把學員信息表中的學號改變了，學員成績表中的學號也應(yīng)當隨之改變；q 3、子表存在與主表對應(yīng)的記錄，不能從主表中刪除該行不能把有成績的學員刪除了q 4、刪除主表前，先刪子表先刪學員成績表、后刪除學員信息表3

11、4創(chuàng)建數(shù)據(jù)庫表-7演示建立檢查約束35創(chuàng)建數(shù)據(jù)庫表完畢！輸入數(shù)據(jù)項，驗證主鍵、主外鍵關(guān)系、檢查約束輸入數(shù)據(jù)項，驗證主鍵、主外鍵關(guān)系、檢查約束36導(dǎo)入-導(dǎo)出數(shù)據(jù)演示導(dǎo)出數(shù)據(jù)為EXCEL文件37回顧表的基礎(chǔ)知識 q 建表的基本步驟：q確定表中有哪些列q確定每列的數(shù)據(jù)類型q給表添加各種約束q創(chuàng)建各表之間的關(guān)系q SQL Server中的數(shù)據(jù)類型 整型數(shù)據(jù)：int smallint 浮點數(shù)據(jù)：float numeric字符數(shù)據(jù)固定長度：char(6)可變長度: varchar(10) unicode編碼：nchar(10) 布爾數(shù)據(jù)（真/假） bit: 1/ 0 貨幣數(shù)據(jù) money日期數(shù)據(jù) date

12、time38創(chuàng)建表q建表的語法CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類型 列的特征， 字段2 數(shù)據(jù)類型 列的特征， .)列的特征：包括該列是是否為空（NULL）、是否是標識列（自動編號）、是否有默認值、是否為主鍵等。 39創(chuàng)建數(shù)據(jù)表qT-SQL創(chuàng)建數(shù)據(jù)表的語法：CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類型 列的特征， 字段2 數(shù)據(jù)類型 列的特征， .) 數(shù)據(jù)類型 ( 長度 , 小數(shù) ) 40創(chuàng)建數(shù)據(jù)表 NOT NULL 取空值約束 DEFAULT 缺省值 IDENTITY (種子值 , 增量) 自動增量標識 CHECK 檢驗表達式 UNIQUE 唯一值約束 PRIMARY K

13、EY 主鍵 FOREIGN KEY 外鍵字段名41建表示例 1USE stuDB -將當前數(shù)據(jù)庫設(shè)置為stuDB GOCREATE TABLE stuInfo /*-創(chuàng)建學員信息表-*/( stuName VARCHAR(20) NOT NULL , -姓名，非空（必填） stuNo CHAR(6) NOT NULL, -學號，非空（必填） stuAge INT NOT NULL, -年齡，INT類型默認為4個字節(jié) stuID NUMERIC(18,0), -身份證號 stuSeat SMALLINT IDENTITY (1,1), -座位號，自動編號 stuAddress TEXT -住址，

14、允許為空，即可選輸入) GONUMERIC (18,0)代表18位數(shù)字，小數(shù)位數(shù)為0演示：創(chuàng)建學員信息表stuInfoIDENTITY（起始值，遞增量） 42建表示例 2CREATE TABLE stuMarks( ExamNo CHAR(7) NOT NULL, -考號 stuNo CHAR(6) NOT NULL, -學號 writtenExam INT NOT NULL, -筆試成績 LabExam INT NOT NULL -上機成績)GO演示：創(chuàng)建學員成績表 stuMarks43刪除表q 如果當前數(shù)據(jù)庫中已存在stuInfo表，再次創(chuàng)建時系統(tǒng)將提示出錯 。如何解決呢？44刪除表q刪除

15、表的語法：DROP TABLE 表名USE stuDB -將當前數(shù)據(jù)庫設(shè)置為stuDB ，以便在stuDB數(shù)據(jù)庫中建表GOIF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo ） DROP TABLE stuInfoCREATE TABLE stuInfo /*-創(chuàng)建學員信息表-*/(.) GO45回顧SQL Server的約束q 約束的目的：確保表中數(shù)據(jù)的完整型q 常用的約束類型：q主鍵約束（Primary Key Constraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空q唯一約束（Unique Constraint）：要求該列唯一，允

16、許為空，但只能出現(xiàn)一個空值。q檢查約束（Check Constraint）：某列取值范圍限制、格式限制等，如有關(guān)年齡的約束q默認約束（Default Constraint）：某列的默認值，如我們的男性學員較多，性別默認為“男”q外鍵約束（Foreign Key Constraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列46添加約束 q 添加約束的語法：ALTER TABLE 表名 ADD CONSTRAINT 約束名 約束類型 具體的約束說明q 約束名的取名規(guī)則推薦采用：約束類型_約束字段q主鍵（Primary Key）約束：如 PK_stuNoq唯一（Unique Key）約束：如

17、UQ_stuIDq默認（Default Key）約束：如 DF_stuAddressq檢查（Check Key）約束：如 CK_stuAgeq外鍵(Foreign Key)約束：如 FK_stuNo 47添加約束示例ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)ALTER TABLE stuInfo ADD CONSTRAINT UQ_stuID UNIQUE (stuID)ALTER TABLE stuInfo ADD CONSTRAINT DF_stuAddress DEFAULT (地址不詳) FOR stu

18、AddressALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO演示：給學員信息表stuInfo添加約束添加主鍵約束（stuNo作為主鍵）添加唯一約束（因為每人的身份證號全國唯一）添加默認約束（如果地址不填，默認為“地址不詳”）添加檢查check約束，要求年齡只能在1540歲之間添加外鍵約束（主表stuInfo和從表stuMarks建立關(guān)系，關(guān)聯(lián)字段為stuNo）48添加約束示例演示：查看約束結(jié)果49刪除約束q如果錯誤地添加了約束，我們還可以刪除約束 q刪除約束的語法ALTER TABLE 表名 DROP CONSTRAINT 約束名 例如：刪除stuInfo表中地址默認約束ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress50總結(jié)q SQL Server創(chuàng)建表的過程是規(guī)定數(shù)據(jù)列的屬性的過程，同時也是實施數(shù)據(jù)完整性（包括實體完整性、引用完整性和域完整性等）保證的過程q 實體完整性數(shù)據(jù)行不能存在重復(fù)，引用完整性要求子表中