-數(shù)據(jù)庫原理及應(yīng)用第二版-第4章SQL語言基礎(chǔ)及數(shù)據(jù)定義功能

第4章

SQL語言基礎(chǔ)及數(shù)據(jù)定義功能

4.1基本概念4.2SQL的數(shù)據(jù)類型4.3數(shù)據(jù)定義功能4.4數(shù)據(jù)完整性4.1基本概念4.1.2SQL語言特點(diǎn)

SQL語言集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制功能于一身，其主要特點(diǎn)包括：一體化高度非過程化簡潔以多種方式使用4.1.3SQL語言功能概述

SQL按其功能可分為四大部分：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù)據(jù)查詢功能和數(shù)據(jù)操縱功能。

Sql功能命令動(dòng)詞數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)查詢SELECT數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE4.2SQL的數(shù)據(jù)類型4.2.1數(shù)值型

1.準(zhǔn)確型：指在計(jì)算機(jī)中能夠精確存儲(chǔ)的數(shù)據(jù)。2.近似型：用于表示浮點(diǎn)型數(shù)據(jù)的近似數(shù)據(jù)類型。1)整數(shù)型此種數(shù)據(jù)類型用于存儲(chǔ)整數(shù)，沒有小數(shù)位。它包括bigint、int、smallint和tinyint4種。其中bigint長度為8個(gè)字節(jié)。Int長度為4個(gè)字節(jié)。Smallint長度為2個(gè)字節(jié)Tinying長度為1個(gè)字節(jié)，只能存儲(chǔ)0-255范圍內(nèi)的數(shù)字。2)小數(shù)數(shù)據(jù)類型Decimal，精確數(shù)據(jù)類型，由兩部分構(gòu)成，一個(gè)整數(shù)，二是小數(shù)部分。Decimal(長度，小數(shù)位)3)近似數(shù)值型Float型和real數(shù)據(jù)類型，非精確數(shù)據(jù)。4.2.2字符串型字符串?dāng)?shù)據(jù)由漢字、英文字母、數(shù)字和各種符號(hào)組成。字符串存儲(chǔ)時(shí)采用字符型數(shù)據(jù)類型，由字母、符號(hào)和數(shù)字組成，用引號(hào)括起來。主要的數(shù)據(jù)類型有char、varchar、nvarchar、nchar和text以及ntext類型。Char或text型是固定長度且非unicode的字符。Varchar是一種可變長度且非unicode的字符。Nchar是一種固定的且unicode字符。4.2.3日期時(shí)間類型

專門的日期時(shí)間類型，格式為月/日/年時(shí):分:秒。有兩種：Datetime和smalldatetime兩種。Datetime能表示：占用8個(gè)字節(jié)空間，1753年至9999年。Smalldatetime能表示：占用4個(gè)字節(jié)空間，1900年到2079年。輸入日期時(shí)：Oct252001/*英文數(shù)字格式*/2001-10-15或2001/10/15/*數(shù)字加分隔符格式*/20011020/*純數(shù)字格式*/輸入時(shí)間時(shí)：2001-10-153:22:45PM/*12小時(shí)格式*/2001-10-1515:22:45/*24小時(shí)格式*/4.3數(shù)據(jù)定義功能

4.3.1基本表的定義與刪除

1、定義基本表一般格式為：CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[列級(jí)完整性約束定義]{,<列名><數(shù)據(jù)類型>［列級(jí)完整性約束定義］…}

[,表級(jí)完整性約束定義]）其中：<表名>：所要定義的基本表的名字。<列名>：表中所包含的屬性列的名字。<數(shù)據(jù)類型>指明列的數(shù)據(jù)類型。在定義表的同時(shí)可以定義與表有關(guān)的完整性約束條件。如果完整性約束只涉及到表中的一個(gè)列，則可以在列級(jí)完整性約束定義處定義，也可以在表級(jí)完整性約束定義處定義；如果完整性約束條件涉及表中多個(gè)屬性列，則必須在表級(jí)完整性約束定義處定義。在列級(jí)完整性約束定義處可以定義如下約束：NOTNULL：限制列取值非空DEFAULT：給定列的默認(rèn)值，使用形式為：

DEFAULT常量UNIQUE：限制列取值不重CHECK：限制列的取值范圍，使用形式為：

CHECK（約束表達(dá)式）

PRIMARYKEY：指定本列為主碼FOREIGNKEY：定義本列為引用其他表的外碼。使用形式為:［FOREIGNKEY(<列名>)]REFERENCES<外表名>（<外表列名>）

但有些約束必須在表級(jí)約束處定義：第一，如果CHECK約束是定義多列之間的取值約束；第二，如果表的主碼由多個(gè)列組成；第三，如果在表級(jí)完整性約束處定義外碼，則FOREIGNKEY和<列名>均不能省，且<列名>必須用括號(hào)括起來。即：

PRIMARYKEY(列1{[，列2]…})用SQL語句創(chuàng)建Student表列名說明數(shù)據(jù)類型約束Sno學(xué)號(hào)字符串，長度為7主碼Sname姓名字符串，長度為10非空Ssex性別字符串，長度為2取‘男’或‘女’Sage年齡微整數(shù)取值15～45Sdept所在系字符串，長度為20默認(rèn)為‘計(jì)算機(jī)系’表4-6Student表結(jié)構(gòu)CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),

SagetinyintCHECK(Sage>=15ANDSage<=45),Sdeptchar(20)DEFAULT'計(jì)算機(jī)系')用SQL語句創(chuàng)建Course表列名說明數(shù)據(jù)類型約束Cno課程號(hào)字符串，長度為10主碼Cname課程名字符串，長度為20非空Ccredit學(xué)分微整數(shù)Semster學(xué)期微整數(shù)表4-7course表結(jié)構(gòu)

CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,Ccredittinyint,Semestertinyint,

PRIMARYKEY(Cno))

用SQL語句創(chuàng)建SC表列名說明數(shù)據(jù)類型約束Sno學(xué)號(hào)字符串，長度為7主碼，引用Student的外碼Cno課程名字符串，長度為10主碼，引用Course的外碼Grade成績小整數(shù)XKLB修課類別字符串，長度為4表4-8sc表結(jié)構(gòu)CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradesmallyint,XKLBchar(4),

PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))2、刪除表刪除表的語句格式為:

DROPTABLE<表名>{[,<表名>]…}

例：刪除test表的語句為:

DROPTABLEtest4.3.2修改表結(jié)構(gòu)

Server的ALTERTABLE語句的部分格式：ALTERTABLE<表名>

[ALTERCOLUMN<列名><新數(shù)據(jù)類型>]|[ADD<列名><數(shù)據(jù)類型>[屬性]|[DROPCOLUMN<列名>]|[ADDconstraint約束名約束定義]|[DROPconstraint約束名]例1．為SC表添加“修課類別”列，此列的定義為：XKLBchar(4)

ALTERTABLESCADDXKLBchar(4)NULL例2．將新添加的XKLB的類型改為char(6)。

ALTERTABLESCALTERCOLUMNXKLBchar(6)例3．刪除Course表的Period列ALTERTABLECourseDROPCOLUMNPeriod4.4數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)的正確性和相容性。為了防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，為了維護(hù)數(shù)據(jù)的完整性，數(shù)據(jù)庫管理系統(tǒng)必須提供一種機(jī)制來檢查數(shù)據(jù)庫中的數(shù)據(jù)，看其是否滿足語義規(guī)定的條件。這些加在數(shù)據(jù)庫數(shù)據(jù)之上的語義約束條件就是數(shù)據(jù)完整性約束條件。而DBMS檢查數(shù)據(jù)是否滿足完整性條件的機(jī)制就稱為完整性檢查。4.4.1完整性約束條件的作用對象

完整性約束條件的作用對象可以是表、元組和列。(1)列級(jí)約束對數(shù)據(jù)類型的約束：包括數(shù)據(jù)類型、長度、精度等。對數(shù)據(jù)格式的約束：如規(guī)定學(xué)號(hào)的前兩位表示學(xué)生的入學(xué)年份，第三位表示系的編號(hào)，第四位表示專業(yè)編號(hào)，第五位代表班的編號(hào)等等。對取值范圍或取值集合的約束：如學(xué)生的成績?nèi)≈捣秶鸀?～100。對空值的約束：列是否允許為空。(2)元組約束

元組的約束是元組中各個(gè)字段之間的聯(lián)系的約束，如：借閱日期<應(yīng)還日期。(3)關(guān)系約束

關(guān)系約束是指若干元組之間、關(guān)系之間的聯(lián)系的約束。比如學(xué)號(hào)的取值不能重復(fù)也不能取空值，學(xué)生修課表中的學(xué)號(hào)的取值受學(xué)生表中的學(xué)號(hào)取值的約束等。4.4.2實(shí)現(xiàn)數(shù)據(jù)完整性

實(shí)現(xiàn)完整性一般是在服務(wù)器端完成的。方法有兩種：一種是在定義表時(shí)聲明數(shù)據(jù)完整性；另一種是在服務(wù)器編寫觸發(fā)器來實(shí)現(xiàn)。實(shí)體完整性（PRIMARYKEY）引用完整性（FOREIGNKEY）用戶定義的完整性，包括：默認(rèn)值（DEFAULT）約束列值取值范圍（CHECK）約束惟一值約束（UNIQUE）以雇員表和工作表為例，在這兩張表上添加約束。這兩張表的結(jié)構(gòu)如下：

雇員表

(雇員編號(hào)字符型長度為7非空，雇員名字符型，長度為10，工作編號(hào)字符型，長度為8， 工資整型，電話字符型，長度為8非空）

工作表

(工作編號(hào)字符型，長度為8，非空，最低工資整型，最高工資整型)

(1)主碼約束

a、每個(gè)表只能有一個(gè)；b、用PRIMARYKEY約束的列取值不能有重復(fù)，而且不允許有空值；添加主碼約束的語法格式為：

ALTERTABLE表名 ADD[CONSTRAINT]約束名PRIMARYKEY（<列名>[,…n]）

例1：對雇員表和工作表分別添加主碼約束。

ALTERTABLE雇員表ADDCONSTRAINTPK_EMP

PRIMARYKEY（雇員編號(hào)）ALTERTABLE工作表ADDCONSTRAINTPK_JOB

PRIMARYKEY（工作編號(hào)）(2)UNIQUE約束

用于限制在一個(gè)列中不能有重復(fù)的值。定義UNIQUE約束時(shí)注意如下事項(xiàng)：允許有一個(gè)空值；在一個(gè)表中可以定義多個(gè)UNIQUE約束；可以在一個(gè)列或多個(gè)列上定義UNIQUE約束；

添加UNIQUE約束的語法格式為：

ALTERTABLE表名ADD[CONSTRAINT約束名]UNIQUE（<列名>[,…n]）

例2．為雇員表的“電話”列添加UNIQUE約束。

ALTERTABLE雇員表

ADDCONSTRAINTUK_SIDUNIQUE（電話）(3)外碼約束

外碼約束實(shí)現(xiàn)了引用完整性。添加FOREIGNKEY約束的語法格式為：ALTERTABLE表名ADD[CONSTRAINT約束名]FOREIGNKEY（<列名>）REFERENCES引用表名（<列名>）

例3．為雇員表的工作編號(hào)添加外碼引用約束，此列引用工作表的工作編號(hào)列。ALTERTABLE雇員ADDCONSTRAINTFK_job_id

FOREIGNKEY（工作編號(hào)）

REFERENCES工作表（工作編號(hào)）

(4)DE