數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用教程 第4版 教學(xué)課件 作者 劉瑞新第6章-1

教學(xué)目的：

熟練掌握使用SQLServer2008管理平臺(tái)和T-SQL創(chuàng)建數(shù)據(jù)庫、表和約束的方法，學(xué)會(huì)利用管理平臺(tái)查看或修改數(shù)據(jù)庫屬性和表結(jié)構(gòu)。學(xué)會(huì)用T-SQL數(shù)據(jù)查詢操作

。第6章數(shù)據(jù)庫的建立與管理

6.1數(shù)據(jù)庫的定義和維護(hù)

6.2基本表的定義和維護(hù)

6.3數(shù)據(jù)維護(hù)操作

6.4數(shù)據(jù)查詢操作1.使用SQLServer管理平臺(tái)創(chuàng)建數(shù)據(jù)庫

建立學(xué)生課程庫，庫中表結(jié)構(gòu)為：

學(xué)生（學(xué)號(hào)，姓名，年齡，性別，所在系）；

課程（課程號(hào)，課程名，先行課）；

選課（學(xué)號(hào)，課程號(hào)，成績(jī)）.具體任務(wù)要求：

（1）確定數(shù)據(jù)庫文件及參數(shù)，建立數(shù)據(jù)庫。

（2）為每個(gè)屬性選擇數(shù)據(jù)類型和長(zhǎng)度；建表。

（3）確定主碼和約束，并定義。任務(wù)一：1.確定數(shù)據(jù)庫屬性(準(zhǔn)備)數(shù)據(jù)庫名文件名物理文件位置初始大小文件組學(xué)生課程學(xué)生課程_DataD:\SQL2005LX2\學(xué)生課程_Data.MDF3MBPRIMARY學(xué)生課程_LogD:\SQL2005LX2\學(xué)生課程_Log.LDF1MBMyLog6.1數(shù)據(jù)庫的定義和維護(hù)完成任務(wù)1（建庫），可以通過2種方法，確定數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件的邏輯文件名、物理文件、初始大小和文件組等參數(shù)。實(shí)例：2.使用SQLServer管理平臺(tái)創(chuàng)建數(shù)據(jù)庫（建庫方法1）（1）進(jìn)入SQLServer管理平臺(tái)（2）右擊數(shù)據(jù)庫文件夾，從彈出的快捷菜單中選擇新建數(shù)據(jù)庫選項(xiàng)，出現(xiàn)對(duì)話框。（3）改變?cè)鲩L(zhǎng)和路徑屬性（4）修改其他屬性和文件組屬性（5）查看數(shù)據(jù)庫屬性和刪除數(shù)據(jù)庫（1）數(shù)據(jù)庫定義語言CREATEDATABASE〈數(shù)據(jù)庫名〉

[ON[PRIMARY][(NAME=〈邏輯數(shù)據(jù)文件名〉，]

FILENAME='〈操作數(shù)據(jù)文件路徑和文件名〉'

[，SIZE=〈文件長(zhǎng)度〉]

[，MAXSIZE=〈最大長(zhǎng)度〉]

[，F(xiàn)ILEROWTH=〈文件增長(zhǎng)率〉])[，…n]]

[LOGON([NAME=〈邏輯日志文件名〉，]

FILENAME='〈操作日志文件路徑和文件名〉'

[，SIZE=〈文件長(zhǎng)度〉])[，…n]]

[FORRESTORE]句中：PRIMARY指明主文件；SIZE說明文件的大小，默認(rèn)為3MB；FILEROWTH說明文件增長(zhǎng)率，默認(rèn)為10%。FORRESTORE子句說明重建數(shù)據(jù)庫（用于數(shù)據(jù)恢復(fù)）。

3.使用T-SQL語言定義數(shù)據(jù)庫（建庫方法2）實(shí)例：CREATEDATABASE學(xué)生課程ONPRIMARY(NAME=學(xué)生課程_data,FILENAME=‘D:\SQLlx\學(xué)生課程_data.mdf',SIZE=15MB,MAXSIZE=30MB,FILEGROWTH=20%)LOGON(NAME=學(xué)生課程_log,FILENAME=D:\SQLlx\學(xué)生課程_log.ldf',SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=1MB)(2)調(diào)出查詢窗口，輸入T-SQL語句，擊‘執(zhí)行’鍵6.2

基本表的定義和維護(hù)

6.2.1確定數(shù)據(jù)表結(jié)構(gòu)及約束1.系統(tǒng)數(shù)據(jù)類型（數(shù)字型）數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型應(yīng)用說明二進(jìn)制image圖像、視頻、音樂Binary[(n)]標(biāo)記或標(biāo)記組合數(shù)據(jù)數(shù)字精確整數(shù)bigint長(zhǎng)整數(shù)-263～263-1int整數(shù)-231～231-1smallint短整數(shù)-215～215-1精確小數(shù)Decimal[(p[,s])]p:最大數(shù)字位數(shù)s:最大小數(shù)位數(shù)近似數(shù)字float[(n)]－1.79E＋308～1.79E＋308real－3.40E＋38～3.40E＋381.系統(tǒng)數(shù)據(jù)類型（字符和其他類型）數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型應(yīng)用說明字符char[(n)]定長(zhǎng)字符型varchar[(n)]變長(zhǎng)字符型text變長(zhǎng)文本型，存儲(chǔ)字符長(zhǎng)度大于8000字符日期和時(shí)間Datetime1753-1-1～9999-12-31（12:00:00）smalldatetime1900-1-1～2079-6-6貨幣Money-263～263-1（保留小數(shù)點(diǎn)后四位）smallmoney-231～231-1（保留小數(shù)點(diǎn)后四位）特殊bit0/1，判定真或假2.表的完整性約束主鍵約束：即主鍵各列不能為空且主鍵作為行的惟一標(biāo)識(shí)外鍵約束：確定外碼、相關(guān)的主碼表和主碼默認(rèn)值、規(guī)則、值域等3.設(shè)計(jì)表時(shí)需要確定的內(nèi)容表中需要的列以及每一列的類型（必要時(shí)還要有長(zhǎng)度）列是否可以為空是否需要在列上使用約束、默認(rèn)值和規(guī)則需要使用什么樣的索引哪些列作為主鍵表名屬性名數(shù)據(jù)類型長(zhǎng)度列級(jí)約束表級(jí)約束學(xué)生學(xué)號(hào)CHAR5不能空，惟一值學(xué)號(hào)為主碼姓名CHAR8不能空年齡SMALLINT默認(rèn)20性別CHAR2‘男’或‘女’所在系CHAR20課程課程號(hào)CHAR5不能空，惟一值課程號(hào)為主碼課程名CHAR20不能空先行課CHAR5選課學(xué)號(hào)CHAR5不能空學(xué)號(hào)和課程號(hào)為主碼；學(xué)號(hào)為外碼；課程號(hào)為外碼課程號(hào)CHAR5不能空成績(jī)SMALLINT0～100實(shí)例：確定數(shù)據(jù)表結(jié)構(gòu)及約束6.2.2使用SQLServer管理平臺(tái)創(chuàng)建基本表1.選擇新建表，輸入列名2.輸入數(shù)據(jù)類型，改變數(shù)據(jù)長(zhǎng)度3.關(guān)閉后輸入表名4.修改表結(jié)構(gòu)或查看表結(jié)構(gòu)查看表結(jié)構(gòu)6.2.3用SQL定義基本表CREATETABLE[〈庫名〉]〈表名〉(

〈列名〉〈數(shù)據(jù)類型〉|AS〈表達(dá)式〉[〈字段約束〉][，……]

[〈記錄約束〉])[，……]1.定義表(1)記錄約束：

CONSTRAINT<約束名><約束式>

1)[PRIMARYKEY[CLUSTERED|NONCLUSTERED](〈列名組〉)]

2)[FOREIGNKEY(<外碼>)REFERENCES<參照表>(<主碼>)]3)[CHECK(〈條件表達(dá)式〉)]：定義記錄應(yīng)滿足的條件。

4)[UNIQUE(〈列組〉)]：定義不允許重復(fù)值的字段組。(2)字段約束

1)[NOTNULL|NULL]：不允許或允許字段值為空。

2)[PRIMARYKEYCLUSTERED|NONCLUSTERED

3)[REFERENCE<主碼表>(<主碼>)]：定義參照完整條件。

4)[DEFAULT〈缺省值〉]：定義字段的缺省值。

5)[CHECK(〈條件〉)]：定義字段應(yīng)滿足的條件表達(dá)式。

6)[IDENTITY(<初始值>，<步長(zhǎng)>)]：定義字段初始值和步長(zhǎng)值。USE學(xué)生課程GOCREATETABLE學(xué)生(學(xué)號(hào)CHAR(5)NOTNULLUNIQUE，

姓名VARCHAR(8)NOTNULL，年齡SMALLINTDEFAULT20，

性別CHAR(2)CHECK(性別IN(‘男’，‘女’))，所在系VARCHAR(20))GOCREATETABLE課程(課程號(hào)CHAR(5)PRIMARYKEYCLUSTERED，

課程名VARCHAR(20)，先行課CHAR(5))GO【例】建立基本表：學(xué)生(學(xué)號(hào)，姓名，年齡，性別，所在系)；課程(課程號(hào)，課程名，先行課)；選課(學(xué)號(hào)，課程號(hào)，成績(jī)).CREATETABLE選課(學(xué)號(hào)CHAR(5)，課程號(hào)CHAR(5)，

成績(jī)SMALLINT，

CONSTRAINTC1CHECK(成績(jī)BETWEEN0AND100)，

CONSTRAINTC2PRIMARYKEY(學(xué)號(hào)，課程號(hào))，

CONSTRAINTC3FOREIGNKEY(學(xué)號(hào))REFERENCES學(xué)生(學(xué)號(hào))，

CONSTRAINTC4FOREIGNKEY(課程號(hào))REFERENCES課程(課程號(hào)))GO【例】建立基本表：學(xué)生(學(xué)號(hào)，姓名，年齡，性別，所在系)；課程(課程號(hào)，課程名，先行課)；選課(學(xué)號(hào)，課程號(hào)，成績(jī)).2.基本表的維護(hù)(1)修改基本表

1)修改字段的定義。

ALTERTABLE〈表名〉A(chǔ)LTERCOLUMN〈列名〉〈新類型〉[NULL|NOTNULL]〈約束定義〉

2)增加字段和表約束規(guī)則。

ALTERTABLE〈表名〉A(chǔ)DD{〈列定義〉|[〈表約束定義〉]}3)刪除字段或約束規(guī)則。

ALTERTABLE〈表名〉DROP{[CONSTRAINT]〈約束名〉|COLUMN〈列名〉}

4)使約束有效或無效。

ALTERTABLE〈表名〉{CHECK|NOCHECK}CONSTRAINT{ALL|〈約束名組〉}(2)刪除基本表:DROPTABLE〈表名〉3.調(diào)出查詢窗口，輸入T-SQL語句，擊‘執(zhí)行’鍵6.2.4數(shù)據(jù)約束的定義索引：提高數(shù)據(jù)庫的查找速度，保證列的惟一性聚集索引（Clustered）：根據(jù)鍵的值對(duì)行進(jìn)行排序，所以每個(gè)表只能有一個(gè)聚集索引非聚集索引：不根據(jù)鍵值排序，索引數(shù)據(jù)結(jié)構(gòu)與數(shù)據(jù)行是分開的。惟一索引：保證索引中不含有相同的鍵值，聚集索引和非聚集索引都可以是惟一索引。全文（full-text）索引：上Microsoft全文引擎（full-textengine），支持快速的字符串中單詞的查找。XML索引：支持XML數(shù)據(jù)列中的XML二進(jìn)制對(duì)象。創(chuàng)建原則及注意事項(xiàng)適合建立索引：經(jīng)常被查詢搜索的列，如經(jīng)常在where子句中出現(xiàn)的列。在ORDERBY子句使用的列。外鍵或主鍵列。值惟一的列。不適合建立索引。在查詢中很少被引用的列。包含太多重復(fù)值的列。數(shù)據(jù)類型為bit、text、image等的列不能建立索引。(1)打開表數(shù)據(jù)約束定義步驟(2)設(shè)置主鍵和索引(3)建立檢查約束6.3數(shù)據(jù)維護(hù)操作

6.3.1T-SQL數(shù)據(jù)更新語句1數(shù)據(jù)插入語句

(1)使用常量插入單個(gè)元組.格式為：

INSERT

INTO〈表名〉[(〈屬性列1〉[，〈屬性列2〉…)]

VALUES(〈常量1〉[，〈常量2〉]…)【例】插入一條選課記錄(學(xué)號(hào)：'98011'，課程號(hào)：'C10'，成績(jī)不詳)。

INSERT

INTO選課(學(xué)號(hào)，課程號(hào))

VALUES('98011'，'C10')(2)

在表中插入子查詢的結(jié)果集

INSERT

INTO〈表名〉[(〈屬性列1〉[，〈屬性列2〉]…)]

〈子查詢〉【例】求每個(gè)系學(xué)生的平均年齡，把結(jié)果存入數(shù)據(jù)庫中。

CREATETABLE系平均年齡(系名稱VarCHAR(20)，

平均年齡SMALLINT)GOINSERT

INTO系平均年齡

SELECT所在系，AVG(ALL年齡)

FROM學(xué)生

GROUPBY所在系GO2