《數據庫原理與應用教程》第5版 課件 第3章 SQL 語言基礎及數據定義功能

數據庫原理與應用教程(第5版)第3章SQL語言基礎及數據定義功能

3.1基本概念3.2SQLServer的主要數據類型3.3數據定義功能3.4數據完整性3.1基本概念3.1.1SQL語言的發(fā)展3.1.2SQL語言的特點3.1.3SQL語言功能概述

3.1.1SQL語言的發(fā)展1986年10月由美國ANSI公布最早的SQL標準。1989年4月，ISO提出了具備完整性特征的SQL，稱為SQL-89。1992年11月，ISO又公布了新的SQL標準，稱為SQL-92（以上均為關系形式）。

1999年頒布SQL-99，是SQL92的擴展。3.1.2SQL語言的特點1.一體化2.高度非過程化3.簡潔4.使用方式多樣3.1.3SQL語言功能概述SQL功能命令動詞數據查詢SELECT數據定義CREATE、DROP、ALTER數據操縱INSERT、UPDATE、DELETE數據控制GRANT、REVOKE四部分：數據定義功能、數據控制功能、數據查詢功能和數據操縱功能。

3.2SQL的數據類型數值型字符串型日期和時間型數值型準確型

整數Bigint:8字節(jié)，Int：4字節(jié)Smallint：2字節(jié)，Tinyint：1字節(jié)Bit：1位，存儲1或0小數Numeric（p,q）或Decimal（p,q），其中：p為數字位長度，q：小數位長度。近似型

Float：8字節(jié)

Real：4字節(jié)字符串型普通編碼字符串類型統(tǒng)一字符編碼字符串類型二進制字符串類型普通編碼字符串類型Char[(n)]：定長存儲，n范圍：1~8000Varchar[(n|max)]：不定長存儲（按實際長度存儲），n范圍：1~8000，max指示最大存儲大小是2GB。

注：n為字符個數統(tǒng)一字符編碼字符串類型nchar（n）:定長存儲，n<=4000nvarchar（n）：不定長存儲，長度最大不超過n,n<=4000ntext：存儲大于8000字節(jié)的文本特點：每個字符占兩個字節(jié)二進制字符串類型Binary(n)：固定長度，n<=8000。Varbinary(n)：可變長度，n<＝8000。注：n為二進制數據的字節(jié)數image：大容量、可變長二進制字符數據，可用于存儲文件。日期時間型Datetime：8字節(jié)，年月日時分秒毫秒 （例：‘2001/08/0310:30:00.000’）

SmallDateTime：4字節(jié)，年月日時分（例：‘2001/08/0310:30:00’）日期、時間的輸入格式日期時間型（續(xù)）date：3字節(jié)，定義一個日期。格式：YYYY-MM-DD，

范圍：0001-01-01到9999-12-31

Time：5字節(jié)，定義一天中的某個時間。格式：hh:mm:ss[.n*]n*是0到7位數字，范圍：0~99999993.3數據定義功能

3.3.1基本表的定義與刪除3.3.2修改表結構3.3.1基本表的定義與刪除1．定義基本表使用SQL語言中的CREATETABLE語句實現，其一般格式為：CREATETABLE<表名>(<列名><數據類型>[列級完整性約束定義]{,<列名><數據類型>[列級完整性約束定義］…}[,表級完整性約束定義])

在列級完整性約束定義處可以定義的約束NOTNULL：限制列取值非空。DEFAULT：給定列的默認值。UNIQUE：限制列取值不重。CHECK：限制列的取值范圍。PRIMARYKEY：指定本列為主碼。FOREIGNKEY：定義本列為引用其他表的外碼。使用形式為：［FOREIGNKEY(<外碼列名>)］

REFERENCES<外表名>(<外表列名>)幾點說明NOTNULL和DEFAULT只能是列級完整性約束；其他約束均可在表級完整性約束處定義。注意以下幾點：第一，如果CHECK約束是定義多列之間的取值約束，則只能在表級完整性約束處定義；第二，如果表的主碼由多個列組成，則也只能在表級完整性約束處定義，并將主碼列用括號括起來，即：PRIMARYKEY（列1｛[，列2]…｝）；第三，如果在表級完整性約束處定義外碼，則“FOREIGNKEY(<外碼列名>)”部分不能省。約束定義

①列取值非空約束<列名><類型>NOTNULL

例：snamechar(10)NOTNULL約束定義（續(xù)）②表主碼約束在定義列時定義主碼（僅用于單列主碼）列定義PRIMARYKEY例：SNOchar(7)PRIMARYKEY在定義完列時定義主碼（用于單列或多列主碼）PRIMARYKEY

（<列名序列>）

例：PRIMARYKEY(SNO)PRIMARYKEY(SNO，CNO)約束定義（續(xù)）③外碼引用約束指明本表外碼列引用的表及表中的主碼列。

[FOREIGNKEY(<本表列名>)]REFERENCES<外表名>(<外表主碼列名>)例：

FOREIGNKEY(sno)

REFERENCES學生表(sno)創(chuàng)建學生表CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2),Sagetinyint,Sdeptchar(20))創(chuàng)建課程表CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,Ccredittinyint,Semestertinyint,PRIMARYKEY(Cno))

創(chuàng)建SC表CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradetinyint,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))刪除表當確信不再需要某個表時，可以將其刪除刪除表時會將與表有關的所有對象一起刪掉，包括表中的數據。刪除表的語句格式為：DROPTABLE<表名>{[,<表名>]…}例：刪除test表的語句為：DROPTABLEtest3.3.2修改表結構在定義完表之后，如果需求有變化，比如添加列、刪除列或修改列定義，可以使用ALTERTABLE語句實現。ALTERTABLE語句可以對表添加列、刪除列、修改列的定義、定義主碼、外碼，也可以添加和刪除約束。修改表結構語法ALTERTABLE<表名>[ALTERCOLUMN<列名><新數據類型>]|[ADD[COLUMN]<列名><數據類型>|[DROPCOLUMN<列名>]|[ADDPRIMARYKEY（列名[，…n]）]|[ADDFOREIGNKEY（列名）REFERNECES表名（列名）]示例例2．為SC表添加“修課類別”列，此列的定義為：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL

示例例3．將新添加的XKLB的類型改為char(6)。ALTERTABLESCALTERCOLUMNXKLBchar(6)示例例3.刪除Course表的Period列。ALTERTABLECourseDROPCOLUMNPeriod

3.4數據完整性3.4.1完整性約束條件的作用對象3.4.2實現數據完整性

完整性約束條件的作用對象完整性檢查是圍繞完整性約束條件進行的，因此，完整性約束條件是完整性控制機制的核心。完整性約束條件的作用對象可以是表、元組和列。列級約束元組約束關系約束列級約束列級約束主要是對列的類型、取值范圍、精度等的約束，具體包括：對數據類型的約束：包括數據類型、長度、精度等。對數據格式的約束：如規(guī)定學號的前兩位表示學生的入學年份，第三位表示系的編號，第四位表示專業(yè)編號，第五位代表班的編號等等。對取值范圍的約束：如學生的成績取值范圍為0～100。對空值的約束。

元組約束元組的約束是元組中各個字段之間的聯(lián)系的約束，如：開始日期小于結束日期，職工的最低工資不能低于規(guī)定的最低保障金。關系約束指若干元組之間、關系之間的聯(lián)系的約束。比如:學號的取值不能重復也不能取空值，學生修課表中的學號的取值受學生表中的學號取值的約束

實現數據完整性

聲明完整性

在表定義時聲明使用約束、缺省值(DEFAULT)等由SQLServer自動加以保證過程完整性

在客戶端或服務器端用編程語言或工具實現在Server端用觸發(fā)器（trigger）來實現實現約束1.PRIMARYKEY約束2.UNIQUE約束3.FOREIGNKEY約束4.DEFAULT約束5.CHECK約束PRIMARYKEY約束保證實體完整性每個表有且只有一個PRIMARYKEY約束格式：ALTERTABLE表名

ADD[CONSTAINT約束名]

PRIMARYKEY(列名[,…n])示例例：對雇員表和工作表分別添加主碼約束。ALTERTABLE雇員表

ADDCONSTRAINTPK_EMPPRIMARYKEY(雇員編號)ALTERTABLE工作表

ADDCONSTRAINTPK_JOBPRIMARYKEY(工作編號)UNIQUE約束確保在非主鍵列中不輸入重復值。應用在客觀具有唯一性質的列上，如身份證號、社會保險號等。格式：ALTERTABLE表名

ADD[CONSTRAINT約束名]UNIQUE（<列名>[,…n]）示例例．為雇員表的“電話號碼”列添加UNIQUE約束。ALTERTABLE雇員表

ADDCONSTRAINTUK_SIDUNIQUE

(電話號碼)FOREIGNKEY約束用于建立和加強兩個表數據之間的連接的一列或多列格式：ALTERTABLE表名

ADD[CONSTRAINT約束名]FOREIGNKEY

(<列名>)REFERENCES引用表名(<列名>)示例例．為雇員表的工作編號添加外碼引用約束，此列引用工作表的工作編號列。ALTERTABLE雇員

ADDCONSTRAINTFK_job_idFOREIGNKEY(工作編號)REFERENCES工作表(工作編號)

DEFAULT約束當向表中插入數據時，如果沒有為定義了DEFAULT的列提供值，則是隱式要求為此列使用默認值。一個Default只能約束一列。格式：ALTERTABLE表名

ADD[CONSTRAINT約束名]DEFAULT默認值FOR列名示例例．定義雇員表的工資的默認值為3600。ALTERTABLE雇員

ADDCONSTRAINTDF_SALARYDEFAULT3600FOR工資CHECK約束通過限制輸入到列中的值來強制域的完整性。可定義同