1、第五章 SQL語言及其操作學(xué)習(xí)目的和要求 定義子語言操縱子語言SQL語言編程SQL（Structured Query Language） 結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言SQL是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫語言SQL概述 SQL標(biāo)準(zhǔn)的進(jìn)展過程 標(biāo)準(zhǔn) 大致頁數(shù) 發(fā)布日期SQL/89(FIPS 127-1) 120頁 1989年SQL/92 622頁 1992年SQL99 1700頁 1999年SQL2003 2003年T-SQL 語句的種類數(shù)據(jù)定義語言（DDL）語句 數(shù)據(jù)控制語言（DCL）語句數(shù)據(jù)操作語言（DML）語句定義子語言DDL回答如下問題: 1.定義子語言的三個(gè)命令關(guān)鍵字？ 3

2、.表定義？ 5.索引定義？ 4.視圖定義？ 2.數(shù)據(jù)庫定義？一.定義子語言的三個(gè)命令關(guān)鍵字說明：Create、Alter、Drop 各個(gè)命令關(guān)鍵字后應(yīng)緊跟所要定義的對象關(guān)鍵字，如數(shù)據(jù)庫為DATABASE、表為TABLE、視圖為VIEW、索引為INDEX。 例如：CREATE TABLE、DROP TABLE、ALTER TABLE等等。 SQL SERVER中可用此三命令的數(shù)據(jù)庫對象有：DATABASE、TABLE、VIEW、INDEX、TRIGGER、PROCEDURE、RULE、DEFAULT、FUNCTION。 SQL SERVER中，有幾個(gè)數(shù)據(jù)庫對象沒有ALTER命令，如：INDEX、

3、RULE、DEFAULT沒有ALTER。二.定義表1.創(chuàng)建表（CREATE TABLE） CREATE TABLE 數(shù)據(jù)庫名. 擁有者 . |擁有者. 表名 ( | 列名 AS 列計(jì)算表達(dá)式 | | PRIMARY KEY | UNIQUE ,. ) := NULL|NOT NULL DEFAULT 常數(shù)表達(dá)式 | IDENTITY ( 初值 , 步長 ) NOT FOR REPLICATION ROWGUIDCOL . 定義表示例例1 建立“學(xué)生”表Student，學(xué)號(hào)是主碼，姓名取值唯一。學(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept) CREATE TABLE

4、 Student ( Sno CHAR(9) PRIMARY KEY， /* 列級(jí)完整性約束條件*/ Sname CHAR(20) UNIQUE， /* Sname取唯一值*/ Ssex CHAR(2)， Sage SMALLINT， Sdept CHAR(20) )； 主碼數(shù)據(jù)類型SQL中域的概念用數(shù)據(jù)類型來實(shí)現(xiàn)定義表的屬性時(shí) 需要指明其數(shù)據(jù)類型及長度 選用哪種數(shù)據(jù)類型 取值范圍 要做哪些運(yùn)算 系統(tǒng)提供的數(shù)據(jù)類型數(shù)字?jǐn)?shù)據(jù)整型數(shù)據(jù)：存儲(chǔ)整數(shù)bigint占8個(gè)字節(jié)，值的范圍為 -263263-1int占4個(gè)字節(jié)，值的范圍為 -231231-1smallint占2個(gè)字節(jié)，值的范圍為 -327683

5、2 767tinyint占1個(gè)字節(jié)，值的范圍為 0255decimal (p,s)p為精度，最大38；s為小數(shù)位數(shù)，0spnumeric (p,s)在SQL Server中，等價(jià)于decimal小數(shù)數(shù)據(jù)：包含存儲(chǔ)在最小有效數(shù)上的數(shù)據(jù)系統(tǒng)提供的數(shù)據(jù)類型（續(xù)）數(shù)字?jǐn)?shù)據(jù)（續(xù)）近似數(shù)字?jǐn)?shù)據(jù)：表示浮點(diǎn)數(shù)據(jù)的近似數(shù)字float (n)從 -1.79E+308 到 1.79E+308 之間的浮點(diǎn)數(shù)字?jǐn)?shù)據(jù)；n 為用于存儲(chǔ)科學(xué)記數(shù)法尾數(shù)的位數(shù)，同時(shí)指示其精度和存儲(chǔ)大小，1n53real從 3.40E+38 到 3.40E+38 之間的浮點(diǎn)數(shù)字?jǐn)?shù)據(jù)，存儲(chǔ)大小為 4 字節(jié)；SQL Server 中，real 的同義

6、詞為 float(24) money占8個(gè)字節(jié)，相當(dāng)于decimal,小數(shù)點(diǎn)前15位，小數(shù)點(diǎn)后4位。smallmoney占4個(gè)字節(jié)，小數(shù)點(diǎn)前6位，小數(shù)點(diǎn)后4位。貨幣數(shù)據(jù)：表示正的或負(fù)的貨幣值系統(tǒng)提供的數(shù)據(jù)類型（續(xù)）日期和時(shí)間數(shù)據(jù)datetime占8個(gè)字節(jié)，表示從1753年1月1日到 9999年12月31日的日期smalldatetime占4個(gè)字節(jié)，表示從1900年1月1日至2079年6月6日的日期char (n)存儲(chǔ)字符個(gè)數(shù)為 08 000varchar (n)存儲(chǔ)字符個(gè)數(shù)為 08 000text存儲(chǔ)字符個(gè)數(shù)為 02GBnchar (n)存儲(chǔ)字符個(gè)數(shù)為 04 000nvarchar(n)存儲(chǔ)字

7、符個(gè)數(shù)為 04 000ntext存儲(chǔ)字符個(gè)數(shù)為 01GB字符數(shù)據(jù)和 Unicode 字符數(shù)據(jù)系統(tǒng)提供的數(shù)據(jù)類型（續(xù)）二進(jìn)制數(shù)據(jù)binary(n) 存儲(chǔ)字節(jié)個(gè)數(shù) 08 000varbinary(n) 存儲(chǔ)字節(jié)個(gè)數(shù) 08 000image存儲(chǔ)字節(jié)個(gè)數(shù) 02 Gbit存儲(chǔ)位數(shù)據(jù)cursor存儲(chǔ)對游標(biāo)的引用rowversion (timestamp)時(shí)間戳table存儲(chǔ)函數(shù)返回結(jié)果uniqueidentifier存儲(chǔ) GUID 以及 UUID其他數(shù)據(jù)完整性（5-1）數(shù)據(jù)完整性指的是數(shù)據(jù)庫中存儲(chǔ)的數(shù)據(jù)的正確性、有效性和一致性數(shù)據(jù)安全性與完整性的區(qū)別安全性：防止用戶非法使用數(shù)據(jù)庫，包括惡意破壞數(shù)據(jù)和越權(quán)存

8、取數(shù)據(jù)。完整性：防止合法用戶使用數(shù)據(jù)庫時(shí)向數(shù)據(jù)庫中加入不合語義的數(shù)據(jù)。數(shù)據(jù)完整性（5-2）域完整性(列)實(shí)體完整性 （行）參照完整性(表之間)數(shù)據(jù)完整性（5-3）在SQL中通過PRIMARY KEY、UNIQUE或IDENTITY實(shí)現(xiàn)域完整性是指一個(gè)字段的輸入有效性，是否允許為空值。 例：性別 （男，女）實(shí)體完整性是指保證表中所有的記錄的唯一。SC tablegradecnosno9570888063121239500195001950109501095010外碼Student table主碼在SQL中通過FOREIGN KEY實(shí)現(xiàn)李敏勇 劉晨 王敏 張立950019500295003 950

9、10Sname sno 數(shù)據(jù)完整性（5-4）參照完整性（引用完整性）在插入或刪除數(shù)據(jù)時(shí)，維護(hù)表間數(shù)據(jù)一致性的手段。一般建立在主碼（主鍵）與外部碼（外鍵）之間的關(guān)系。在SQL中通過check、default、觸發(fā)器(trigger)等實(shí)現(xiàn)數(shù)據(jù)完整性（5-5）用戶定義的完整性體現(xiàn)實(shí)際運(yùn)用的業(yè)務(wù)規(guī)則。例如：學(xué)生成績 0100分 職工年齡 1860歲 保險(xiǎn)金+補(bǔ)貼基本工資實(shí)施數(shù)據(jù)完整性的途徑約束（constraint）規(guī)則（rule）默認(rèn)值（default）標(biāo)識(shí)列（identity）觸發(fā)器（trigger）約束約束的用途是限制輸入到表中的值的范圍。約束是實(shí)施數(shù)據(jù)完整性的首選方法約束的分類列級(jí)約束：只對

10、一列起作用的約束。表級(jí)約束：對表中的多列起作用的約束。問題的提出約束的類型PRIMARY KEY 約束UNIQUE 約束CHECK約束FOREIGN KEY 約束DEFAULT 約束 PRIMARY KEY 約束PRIMARY KEY約束利用表中的一列或多列數(shù)據(jù)唯一地標(biāo)識(shí)某一行數(shù)據(jù)每個(gè)表只有一個(gè)PRIMARY KEY約束PRIMARY KEY約束的值必須是唯一的不允許有空值SQL Server中最多可定義 16 列作為主鍵直接在列名后增加關(guān)鍵字 PRIMARY KEYCREATE TABLE Student( sno char(5) PRIMARY KEY, sname varchar(20)

11、 not null, ); 創(chuàng)建PRIMARY KEY約束 創(chuàng)建sc表，其主碼為（sno,cno）? ? ? CREATE TABLE sc (sno char(5) PRIMARY KEY， cno char(1) PRIMARY KEY， grade decimal(4,1) ); 創(chuàng)建PRIMARY KEY約束在CREATE TABLE語句各列定義的最后加: PRIMARY KEY()CREATE TABLE sc (sno char(5) ， cno char(1) ， grade decimal(4,1), PRIMARY KEY(sno,cno);刪除表上已定義的主鍵 ALTER

12、TABLE SC1 DROP CONSTRAINT PK_SC;在沒有定義主鍵的表上，加上一個(gè)主鍵 ALTER TABLE SC ADD CONSTRAINT PK_SC primary key (sno,cno); 刪除和添加主鍵 UNIQUE 約束UNIQUE 約束主要被用來確保不受主鍵約束的列上的數(shù)據(jù)唯一性。UNIQUE約束的創(chuàng)建和使用指導(dǎo)：可以向表中的多列應(yīng)用 UNIQUE 約束允許該列存在NULL 值向現(xiàn)有表應(yīng)用 UNIQUE 約束時(shí)，會(huì)驗(yàn)證現(xiàn)有數(shù)據(jù)在創(chuàng)建表時(shí)定義UNIQUE約束CREATE TABLE test1 ( . , tname varchar(20) unique, tm

13、ount int );CREATE TABLE test1 (. , tname varchar(8) constraint UNIQ_ TNAME unique, tmount int );創(chuàng)建UNIQUE 約束列級(jí)UNIQUE約束在創(chuàng)建表時(shí)定義UNIQUE約束CREATE TABLE test2 ( even_id int primary key, even_name char(20), even_type char(20), even_time datetime, ) 創(chuàng)建UNIQUE 約束CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time)要

14、求：在even_type和even_time上共同建立惟一約束表級(jí)UNIQUE約束向 test1表中的 tname 列添加 UNIQUE 約束 ALTER TABLE test1 ADD CONSTRAINT UNIQ_ TNAME UNIQUE (tname) 刪除表上已定義的UNIQUE約束ALTER TABLE test1 DROP CONSTRAINT UNIQ_ TNAME 添加和刪除UNIQUE 約束 UNIQUE 約束UNIQUE 與主鍵約束的區(qū)別UNIQUE 約束主要用在非主鍵的一列或多列上限制數(shù)據(jù)惟一的情況，而主鍵是用于惟一標(biāo)識(shí)一行數(shù)據(jù)UNIQUE 約束允許該列上存在NULL

15、值，而主鍵決不允許出現(xiàn)這種情況可以在一個(gè)表上設(shè)置多個(gè)UNIQUE 約束，而一個(gè)表上只能設(shè)置一個(gè)主鍵 CHECK 約束CHECK約束通過檢查輸入表列的值來維護(hù)值域的完整性。CHECK約束通過對一個(gè)邏輯表達(dá)式的結(jié)果進(jìn)行判斷來對數(shù)據(jù)進(jìn)行檢查。age int CHECK(age=18 and age=0 and grade=100),Create Table Student ( sno char(5) not null, birthdate datetime not null schooldate datetime not null , primary key (sno), ); 創(chuàng)建CHECK 約束

16、表級(jí)CHECK約束check(birthdate getdate(),check(birthdate =0 and gmark=100) 添加和刪除CHECK 約束CHECK 約束應(yīng)用CHECK 約束的注意事項(xiàng)一個(gè)表可以定義多個(gè)CHECK約束，一個(gè)列上只允許創(chuàng)建一個(gè)列級(jí)CHECK約束。列級(jí) CHECK 約束只能引用被約束的列，表級(jí) CHECK 約束只能引用同一表中的列。DEFAULT 約束如果一個(gè)列的值在 插入時(shí)沒有指定，DEFAULT 約束將自動(dòng)輸入一個(gè)值，可以是預(yù)先指定的常量、NULL 或者一個(gè)系統(tǒng)函數(shù)運(yùn)行時(shí)的值語法： CONSTRAINT 約束名 DEFAULT 約束表達(dá)式 DEFAUL

17、T 約束在創(chuàng)建表時(shí)使用default屬性CREATE TABLE userInfo( . country varchar(50) not null DEFAULT China , )為已經(jīng)創(chuàng)建好的表添加default屬性USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName 應(yīng)用 DEFAULT 約束的幾種情況 FOREIGN KEY 約束應(yīng)用FOREIGN KEY約束主要用來維護(hù)兩個(gè)表之間的一致性關(guān)系。當(dāng)一行新的數(shù)據(jù)加入到表格中，或?qū)Ρ砀裰幸呀?jīng)存在的外

18、鍵上的數(shù)據(jù)進(jìn)行修改時(shí)新的數(shù)據(jù)取值必須：存在于另一張表的主鍵上為NULL當(dāng)主鍵所在表的數(shù)據(jù)被另一張表的外鍵引用時(shí)，用戶無法對主鍵的數(shù)據(jù)進(jìn)行修改或刪除。創(chuàng)建一個(gè)新的 employee 表，并向該表中的 dno列添加 FOREIGN KEY 約束CREATE TABLE employee(eno char(4) not null PRIMARY KEY, ename char(8) not null, sex char(2) not null, age integer, marry char(1), title char(10), dno char(2) REFERENCES department(

19、dno);FOREIGN KEY 約束FOREIGN KEY約束表employee和department的參照關(guān)系父表子表FOREIGN KEY 約束刪除表上已定義的外鍵 ALTER TABLE EMPLOYEE DROP CONSTRAINT FRNKEY_DNO;在沒有定義外鍵的表上添加外鍵 ALTER TABLE EMPLOYEE ADD CONSTRAINT FRNKEY_DNO FOREIGN KEY (dno) REFERENCES department(dno);FOREIGN KEY 約束FOREIGN KEY 約束FOREIGN KEY約束的默認(rèn)實(shí)現(xiàn)策略向外鍵所在表（employee）插入一行新元組，如輸入外鍵的值不滿足參照完整性約束規(guī)則，則系統(tǒng)拒絕。修改外鍵所在表（employee）的元組，如所修改外鍵的值不滿足參照完整性約束規(guī)則，則系統(tǒng)拒絕。修改或刪除父表（department）的主鍵，僅當(dāng)子表(employee)中沒有任何元組外鍵值與其相同時(shí)，系統(tǒng)才執(zhí)行刪除操作，否則拒絕。向外鍵所在表（employee）插入一行新元組FOREIGN KEY 約束修改外鍵所在表（employee）的元組FOREIGN KEY 約束刪除父表（department）的主鍵dnoFOREIGN K