關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL上實(shí)用教案

1、學(xué)習(xí)學(xué)習(xí)(xux)目標(biāo)目標(biāo) 了解SQL的發(fā)展過程和主要功能； 熟練掌握SQL的CREATE TABLE語句和數(shù)據(jù)完整性約束的定義方法； 熟練掌握SQL的數(shù)據(jù)操作(cozu)語句，深刻理解數(shù)據(jù)完整性約束的作用； 熟練掌握SQL SELECT語句，能夠運(yùn)用該語句完成關(guān)系數(shù)據(jù)庫上的各種查詢； 理解關(guān)系數(shù)據(jù)庫的視圖概念，掌握視圖的定義和使用方法。第1頁/共112頁第一頁，共113頁。重點(diǎn)難點(diǎn)重點(diǎn)難點(diǎn) SQL語言本身就是學(xué)習(xí)數(shù)據(jù)庫的重點(diǎn)，所以要求學(xué)生要熟練掌握，具體就是要熟練掌握SQL CREATE TABLE語句(yj)、SQL INSERT語句(yj)、SQL DELETE語句(yj)、SQL UP

2、DATE語句(yj)和SQL SELECT語句(yj)。 本章學(xué)習(xí)的難點(diǎn)是CREATE TABLE語句(yj)中有關(guān)數(shù)據(jù)完整性約束的定義，以及SQL SELECT中復(fù)雜的連接和嵌套查詢。 第2頁/共112頁第二頁，共113頁。3.1 SQL語言(yyn)概述 SQL標(biāo)準(zhǔn)源于1986年10月的ANSI標(biāo)準(zhǔn) 1987年6月正式成為ISO標(biāo)準(zhǔn) SQL89提出了完整性特征 SQL92標(biāo)準(zhǔn)逐步完善(基本、標(biāo)準(zhǔn)和完全級(jí) ) SQL99增加了面向?qū)ο蟮?最新的標(biāo)準(zhǔn)是2003年發(fā)布的SQL2003 SQL標(biāo)準(zhǔn)只是一個(gè)建議標(biāo)準(zhǔn)，主流產(chǎn)品一般也只達(dá)到(d do)了基本級(jí)的要求第3頁/共112頁第三頁，共113頁。

3、SQL語言(yyn)的主要特點(diǎn) SQL是一種一體化的語言 SQL語言是一種高度非過程化的語言 SQL語言非常簡潔 SQL語言可以直接以命令方式交互使用(shyng)，也可以嵌入到程序設(shè)計(jì)語言中以程序方式使用(shyng) 第4頁/共112頁第四頁，共113頁。樣本(yngbn)數(shù)據(jù)庫 教師 (Student) 教師編號(hào) 院系 姓名 性別 職稱 專業(yè)課程 (Student) 課程編號(hào) 課程名稱 責(zé)任教師 學(xué)時(shí) 課程性質(zhì)選課 (Student) 學(xué)號(hào) 課程編號(hào) 考試成績學(xué)生 (Student) 學(xué)號(hào) 院系 姓名 性別 生源 狀態(tài)院系 (Student) 編號(hào) 名稱 負(fù)責(zé)人 辦公地點(diǎn)圖3-1 樣本數(shù)

4、據(jù)庫第5頁/共112頁第五頁，共113頁。數(shù)據(jù)數(shù)據(jù)(shj)(shj)定義功能定義功能與數(shù)據(jù)與數(shù)據(jù)(shj)(shj)完整性完整性約束的實(shí)現(xiàn)約束的實(shí)現(xiàn)SQL的定義(dngy)功能包括模式、表、視圖、存儲(chǔ)過程、觸發(fā)器、規(guī)則、默認(rèn)值等的定義(dngy)。第6頁/共112頁第六頁，共113頁。SQL中的模式(msh)(Schema) 組織數(shù)據(jù)庫對(duì)象的一種邏輯結(jié)構(gòu) 把一個(gè)數(shù)據(jù)庫中的所有對(duì)象按用途劃分(hu fn)為若干集合第7頁/共112頁第七頁，共113頁。模式模式(msh)組織舉例組織舉例z產(chǎn)品模式z訂購(dnggu)模式z銷售模式第8頁/共112頁第八頁，共113頁。定義(dngy)(dngy)

5、模式 模式通常由DBA創(chuàng)建 也可以由得到CREATE SCHEMA授權(quán)的用戶創(chuàng)建 使用(shyng)短語AUTHORIZATION owner_name可以指定模式的管理者（默認(rèn)是創(chuàng)建者） CREATE SCHEMA schema_name AUTHORIZATION owner_name 第9頁/共112頁第九頁，共113頁。刪除(shnch)(shnch)模式 只有在模式(msh)為空、即模式(msh)中不包含任何對(duì)象時(shí)才可以刪除模式(msh)。 DROP SCHEMA schema_name 第10頁/共112頁第十頁，共113頁。表的定義(dngy) (dngy) 一個(gè)表由若干列構(gòu)成，

6、自SQL Server 2005不僅可以定義實(shí)列(簡稱(jinchng)列，column_definition), 而且還可以定義虛列(即計(jì)算列，computed_column_definition) 理解： CREATE TABLE schema_name.table_name ( | ,.n )第11頁/共112頁第十一頁，共113頁。定義(dngy)(dngy)列() () CREATE TABLE schema_name.table_name ( | ,.n )column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY

7、KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第12頁/共112頁第十二頁，共113頁。定義(dngy)(dngy)列()() 列

8、名（column_name）和數(shù)據(jù)類型（）必須指定，在SQL Server 2005中可以使用(shyng)的數(shù)據(jù)類型如表3-1所示 其他項(xiàng)目是各種約束 column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACT

9、ION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第13頁/共112頁第十三頁，共113頁。是否(sh fu)允許空值約束 空值的概念(ginin)？column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column )

10、ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第14頁/共112頁第十四頁，共113頁。關(guān)于約束(yush)名稱 列級(jí)約束名，名稱(mngchng)可以省略 用于命名主關(guān)鍵字、唯一性或參照完整性約束 省略名稱(mngchng)不便于管理相應(yīng)的完整性約束column_name NULL | NOT NULL

11、CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第15頁/共1

12、12頁第十五頁，共113頁。主關(guān)鍵字和唯一性約束(yush) PRIMARY KEY：主關(guān)鍵字約束，說明該列是主關(guān)鍵字 UNIQUE：惟一性約束，說明該列取值必須(bx)惟一 PRIMARY KEY和UNIQUE約束的區(qū)別？column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET

13、NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第16頁/共112頁第十六頁，共113頁。參照(cnzho)完整性約束 說明外部關(guān)鍵字，F(xiàn)OREIGN KEY可以省略(shngl) 什么是外部關(guān)鍵字？外部關(guān)鍵字的作用？ schema_name . referenced_table_name ( ref_column ) 指出參照的表和列，默認(rèn)模式時(shí)schema_name可以省略(

14、shngl)，列名相同時(shí)( ref_column )可以省略(shngl)column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT |

15、 CHECK ( logical_expression ) DEFAULT constant_expression 第17頁/共112頁第十七頁，共113頁。參照(cnzho)完整性規(guī)則 ON DELETE和ON UPDATE說明在執(zhí)行(zhxng)刪除和更新操作時(shí)如何處理參照完整性 刪除操作檢查的完整性？ 更新操作檢查的完整性？column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_na

16、me ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第18頁/共112頁第十八頁，共113頁。CHECK約束(yush) 用來說明列的取值范圍 本約束用邏輯表達(dá)式 ( logical_expression )進(jìn)行說明 如果列值使表達(dá)式為真則是合法值，否則就是(jish)非法值

17、column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT

18、 constant_expression 第19頁/共112頁第十九頁，共113頁。字段默認(rèn)值 用常量(chngling)表達(dá)式（constant_expression）來說明列的默認(rèn)取值 column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAUL

19、T ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 第20頁/共112頁第二十頁，共113頁。定義(dngy)(dngy)計(jì)算列 column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES referenced_

20、table_name ( ref_column ) ON DELETE NO ACTION | CASCADE ON UPDATE NO ACTION | CHECK ( logical_expression ) CREATE TABLE schema_name.table_name ( | ,.n )第21頁/共112頁第二十一頁，共113頁。定義(dngy)(dngy)計(jì)算列 其中column_name給出計(jì)算(j sun)列的列名，computed_column_expression是對(duì)應(yīng)的計(jì)算(j sun)表達(dá)式，PERSISTED說明物理存儲(chǔ)計(jì)算(j sun)列的值。 其他選項(xiàng)和定義

21、實(shí)列時(shí)類似。column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE ON UPDATE NO ACTION | CHECK ( logical_expression ) 第22頁/共112頁第二十二頁，共113頁。定義(dngy)(dngy)表級(jí)完整性約

22、束 如果某個(gè)完整性約束與多個(gè)列相關(guān)，則這樣的完整性約束不能定義(dngy)在單個(gè)列上，這時(shí)候就需要表級(jí)完整性約束。 單個(gè)列上的完整性約束也可以用表級(jí)完整性約束的形式來定義(dngy)。第23頁/共112頁第二十三頁，共113頁。定義(dngy)(dngy)表級(jí)完整性約束 CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY ( column ,.n ) REFERENCES referenced_table_name ( ref_column ,.n ) ON DELETE NO ACTION | CASCADE | SET

23、 NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) CREATE TABLE schema_name.table_name ( | ,.n )第24頁/共112頁第二十四頁，共113頁。表定義(dngy)(dngy)舉例 例1：定義(dngy)院系表，其中編號(hào)為主關(guān)鍵字，名稱應(yīng)該惟一、并且不允許為空值。 create table student.院系 (編號(hào)tinyint primary key, 名稱char(30) unique n

24、ot null, 負(fù)責(zé)人char(10), 辦公地點(diǎn)char(40) )第25頁/共112頁第二十五頁，共113頁。第26頁/共112頁第二十六頁，共113頁。表定義(dngy)(dngy)舉例 例2：定義學(xué)生表，其中規(guī)定： 學(xué)號(hào)列是主關(guān)鍵字； 院系列為計(jì)算列（取學(xué)號(hào)列的第3和第4個(gè)字符），并且院系值參照院系表的編號(hào)值（院系表是被參照表，主關(guān)鍵字是編號(hào)；參照表是學(xué)生表，外部關(guān)鍵字是院系），此約束說明一名學(xué)生一定屬于某個(gè)院系； 姓名列不允許為空值； 性別必須(bx)取值“男”或“女”； 學(xué)生的學(xué)籍狀態(tài)為正常、留級(jí)、休學(xué)或退學(xué)。 第27頁/共112頁第二十七頁，共113頁。create table

25、 student.學(xué)生(學(xué)號(hào)char(8) primary key, 院系as convert(tinyint,substring(學(xué)號(hào),3,2) persisted not null foreign key references student.院系(編號(hào)), 姓名char(10) not null, 性別char(2) check (性別=男 or 性別=女), 生源char(6), 狀態(tài)char(4) check (狀態(tài)in (正常,留級(jí),休學(xué)(xi xu),退學(xué))第28頁/共112頁第二十八頁，共113頁。第29頁/共112頁第二十九頁，共113頁。表定義(dngy)(dngy)舉例

26、例3：定義教師表，其中規(guī)定： 教師編號(hào)是主關(guān)鍵字； 院系列為計(jì)算列（取教師編號(hào)的第1和第2個(gè)字符），并且院系值參照院系表的編號(hào)值，此約束說明一名教師一定屬于某個(gè)院系； 姓名列不允許為空值； 性別必須取值“男”或“女”； 職稱的取值為教授(jioshu)、副教授(jioshu)、講師或助教。 第30頁/共112頁第三十頁，共113頁。create table student.教師(教師編號(hào)char(6) primary key, 院系as convert(tinyint,substring(教師編號(hào),1,2) persisted not null foreign key references s

27、tudent.院系(編號(hào)), 姓名char(10) not null, 性別char(2) check (性別=男 or 性別=女), 職稱char(6) check (職稱in (教授,副教授,講師,助教(zhjio), 專業(yè)char(16)第31頁/共112頁第三十一頁，共113頁。第32頁/共112頁第三十二頁，共113頁。表定義(dngy)(dngy)舉例 例4：定義課程表，其中規(guī)定： 課程編號(hào)是主關(guān)鍵字； 課程名稱不允許(ynx)為空值； 責(zé)任教師參照教師表的教師編號(hào)值，此約束說明責(zé)任教師一定是已經(jīng)在編的教師； 學(xué)時(shí)不允許(ynx)為空值； 課程性質(zhì)分為公共基礎(chǔ)、專業(yè)基礎(chǔ)、專業(yè)選修和

28、任意選修。 第33頁/共112頁第三十三頁，共113頁。create table student.課程(課程編號(hào)char(6) primary key, 課程名稱char(24) not null, 責(zé)任教師char(6) constraint duty_teacher foreign key references student.教師(教師編號(hào)), 學(xué)時(shí)tinyint not null, 課程性質(zhì)char(8) check (課程性質(zhì)in (公共基礎(chǔ),專業(yè)(zhuny)基礎(chǔ),專業(yè)(zhuny)選修,任意選修) 第34頁/共112頁第三十四頁，共113頁。第35頁/共112頁第三十五頁，共11

29、3頁。表定義(dngy)(dngy)舉例 例5：定義選課表，其中規(guī)定： 學(xué)號(hào)和課程編號(hào)兩列構(gòu)成主關(guān)鍵字（需要表級(jí)完整性約束）； 學(xué)號(hào)參照學(xué)生(xu sheng)表的學(xué)號(hào)（約束選課的學(xué)生(xu sheng)必須存在）； 課程編號(hào)參照課程表的課程編號(hào)（約束被選的課程必須存在）； 考試成績?nèi)≈翟?100之間，并且默認(rèn)為空值。 第36頁/共112頁第三十六頁，共113頁。create table student.選課(學(xué)號(hào)char(8) foreign key references student.學(xué)生, 課程(kchng)編號(hào)char(6) foreign key references studen

30、t.課程(kchng), 考試成績tinyint check (考試成績between 0 and 100) default null primary key (學(xué)號(hào),課程(kchng)編號(hào)) 第37頁/共112頁第三十七頁，共113頁。第38頁/共112頁第三十八頁，共113頁。第39頁/共112頁第三十九頁，共113頁。修改(xigi)(xigi)表結(jié)構(gòu) ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name

31、 | COLUMN column_name ALTER COLUMN修改已有列的定義，但是(dnsh)只能修改為兼容數(shù)據(jù)類型或重新定義是否允許空值； 第40頁/共112頁第四十頁，共113頁。修改(xigi)(xigi)表結(jié)構(gòu) ADD添加(tin ji)新列、或新的計(jì)算列或表級(jí)完整性約束； ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name 第41頁/共112頁第四十

32、一頁，共113頁。修改(xigi)(xigi)表結(jié)構(gòu) DROP刪除指定(zhdng)的完整性約束或指定(zhdng)的列。 ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name 第42頁/共112頁第四十二頁，共113頁。修改(xigi)(xigi)表結(jié)構(gòu)舉例 例6：規(guī)定學(xué)生表的性別(xngbi)字段不允許為空值。 ALTER TABLE 學(xué)生 ALTER COLUMN

33、 性別(xngbi)char(2) NOT NULL 是否會(huì)影響原先定義在該列上的其他約束？第43頁/共112頁第四十三頁，共113頁。第44頁/共112頁第四十四頁，共113頁。修改表結(jié)構(gòu)(jigu)(jigu)舉例 例7：刪除課程表中的責(zé)任(zrn)教師列。 alter table student.課程drop column 責(zé)任(zrn)教師 如果捆綁了其他對(duì)象（如列約束），則需要先刪除捆綁的對(duì)象。 alter table student.課程drop duty_teacher第45頁/共112頁第四十五頁，共113頁。第46頁/共112頁第四十六頁，共113頁。第47頁/共112頁第四

34、十七頁，共113頁。修改表結(jié)構(gòu)(jigu)(jigu)舉例 例8：為課程表增加責(zé)任教師列，列描述如下： 字符類型，長度為6； 允許空值； 外部關(guān)鍵字，參照教師表的教師編號(hào)列，當(dāng)刪除被參照記錄( jl)時(shí)該列置為空值，當(dāng)修改被參照記錄( jl)的主關(guān)鍵字時(shí)級(jí)聯(lián)修改參照記錄( jl)的外部關(guān)鍵字值。第48頁/共112頁第四十八頁，共113頁。alter table student.課程add 責(zé)任教師( jiosh)char(6) nullconstraint ref_teacher foreign key references student.教師( jiosh)(教師( jiosh)編號(hào))on

35、 delete set nullon update cascade第49頁/共112頁第四十九頁，共113頁。第50頁/共112頁第五十頁，共113頁。刪除(shnch)(shnch)表 DROP TABLE schema_name.table_name ,.n 一次可以刪除多個(gè)表。不能使用DROP TABLE刪除被FOREIGN KEY約束參照(cnzho)的表。如果要在同一個(gè) DROP TABLE語句中刪除參照(cnzho)表以及被參照(cnzho)表，則必須先列出參照(cnzho)表。第51頁/共112頁第五十一頁，共113頁。思考題 在SQL中CREATE SCHEMA是一條什么(sh

36、n me)命令？創(chuàng)建的SCHEMA是一種什么(shn me)對(duì)象？它有什么(shn me)作用？ 在表中定義約束時(shí)，PRIMARY KEY和UNIQUE有什么(shn me)區(qū)別？ 在CREATE TABLE命令中哪些內(nèi)容與定義參照完整性有關(guān)？討論它們的具體作用。 約束一般定義在列上，為什么(shn me)還需要表級(jí)約束？ 定義計(jì)算列時(shí)選用關(guān)鍵詞PERSISTED有什么(shn me)作用？ 計(jì)算列是否可以作為參照列（外部關(guān)鍵字）？ 如果在定義約束時(shí)沒有使用CONSTRAINT短語給出約束名，而事后又要?jiǎng)h除該約束應(yīng)該怎么辦？ 對(duì)已有的表可以進(jìn)行哪些方面的表結(jié)構(gòu)修改？第52頁/共112頁第五十二

37、頁，共113頁。數(shù)據(jù)操作數(shù)據(jù)操作(cozu)(cozu)功能功能 及數(shù)據(jù)完整性的作用(zuyng) 第53頁/共112頁第五十三頁，共113頁。SQLSQL的數(shù)據(jù)(shj)(shj)操作 插入(ch r)操作及其完整性約束 刪除操作及其完整性約束 更新操作及其完整性約束第54頁/共112頁第五十四頁，共113頁。插入(ch r)(ch r)操作及其完整性約束 SQL的插入語句是INSERT，常用格式： INSERT INTO schema_name.table_name ( column_list ) VALUES(expression | DEFAULT | NULL ,.n ) 其中 sc

38、hema_name指出(zh ch)模式名 table_name指出(zh ch)表名 column_list給出插入操作所涉及列的列表（默認(rèn)是表的全部列） expression | DEFAULT | NULL ,.n 給出對(duì)應(yīng)于column_list的各個(gè)列的值 第55頁/共112頁第五十五頁，共113頁。例9 插入(ch r)院系表的第1個(gè)元組(記錄)INSERT INTO student.院系(編號(hào)(bin ho),名稱,負(fù)責(zé)人,辦公地點(diǎn)) VALUES(1,信息管理學(xué)院,李嘉,教一樓3層)CREATE TABLE student.院系(編號(hào) tinyint PRIMARY KEY,

39、名稱 char(30) UNIQUE NOT NULL, 負(fù)責(zé)人 char(10), 辦公(bn gng)地點(diǎn) char(40)第56頁/共112頁第五十六頁，共113頁。第57頁/共112頁第五十七頁，共113頁。例10 省略(shngl)列名、插入院系表的第2個(gè)元組INSERT INTO student.院系VALUES(2,經(jīng)濟(jì)( jngj)管理學(xué)院,吳鴻忠,教三樓4層)CREATE TABLE student.院系(編號(hào) tinyint PRIMARY KEY, 名稱 char(30) UNIQUE NOT NULL, 負(fù)責(zé)人 char(10), 辦公(bn gng)地點(diǎn) char(4

40、0)第58頁/共112頁第五十八頁，共113頁。第59頁/共112頁第五十九頁，共113頁。例11INSERT INTO student.院系VALUES(2,計(jì)算機(jī)學(xué)院(xuyun),張秋霞,教二樓3層)CREATE TABLE student.院系(編號(hào)(bin ho) tinyint PRIMARY KEY, 名稱 char(30) UNIQUE NOT NULL, 負(fù)責(zé)人 char(10), 辦公地點(diǎn) char(40)違背(wibi)PRIMARY KEY 約束第60頁/共112頁第六十頁，共113頁。第61頁/共112頁第六十一頁，共113頁。例12INSERT INTO stude

41、nt.院系VALUES(3,經(jīng)濟(jì)管理(gunl)學(xué)院,張秋霞,教二樓3層)CREATE TABLE student.院系(編號(hào) tinyint PRIMARY KEY, 名稱(mngchng) char(30) UNIQUE NOT NULL, 負(fù)責(zé)人 char(10), 辦公地點(diǎn) char(40)違背(wibi)UNIQUE約束第62頁/共112頁第六十二頁，共113頁。第63頁/共112頁第六十三頁，共113頁。例13INSERT INTO student.院系(編號(hào)(bin ho),負(fù)責(zé)人,辦公地點(diǎn)) VALUES(3,張秋霞,教二樓3層)CREATE TABLE student.院系(

42、編號(hào) tinyint PRIMARY KEY, 名稱 char(30) UNIQUE NOT NULL, 負(fù)責(zé)人 char(10), 辦公(bn gng)地點(diǎn) char(40)違背(wibi)NOT NULL約束第64頁/共112頁第六十四頁，共113頁。第65頁/共112頁第六十五頁，共113頁。第66頁/共112頁第六十六頁，共113頁。例14CREATE TABLE student.學(xué)生(xu sheng)(學(xué)號(hào) char(8) PRIMARY KEY, 院系 AS CONVERT(Tinyint,SUBSTRING(學(xué)號(hào),3,2) PERSISTED NOT NULL FOREIGN

43、KEY REFERENCES student.院系(編號(hào)), 姓名 char(10) NOT NULL, 性別 char(2) CHECK (性別=男 OR 性別=女), 生源 char(6), 狀態(tài) char(4) CHECK (狀態(tài)IN(正常,留級(jí),休學(xué),退學(xué))插入(ch r)學(xué)生表的第1個(gè)元組。INSERT INTO student.學(xué)生(xu sheng)(學(xué)號(hào),姓名,性別,生源,狀態(tài)) VALUES(04014101,曹波,男,湖北,正常)第67頁/共112頁第六十七頁，共113頁。第68頁/共112頁第六十八頁，共113頁。例15 插入學(xué)生表的第2條記錄(jl)（指定正確的院系值）

44、注意(zh y)：學(xué)生表的院系列是計(jì)算列，是不能被插入語句操作的。 CREATE TABLE student.學(xué)生(學(xué)號(hào) char(8) PRIMARY KEY, 院系 AS CONVERT(Tinyint,SUBSTRING(學(xué)號(hào),3,2) PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(編號(hào)), 姓名 char(10) NOT NULL, 性別(xngbi) char(2) CHECK (性別(xngbi)=男 OR 性別(xngbi)=女), 生源 char(6), 狀態(tài) char(4) CHECK (狀態(tài)IN(正常,留級(jí),休學(xué),

45、退學(xué))INSERT INTO student.學(xué)生(學(xué)號(hào),院系,姓名,性別,生源,狀態(tài)) VALUES(04014111,1,程鳴,女,湖南,正常)INSERT INTO student.學(xué)生 VALUES(04014111,程鳴,女,湖南,正常)正確語句：第69頁/共112頁第六十九頁，共113頁。第70頁/共112頁第七十頁，共113頁。第71頁/共112頁第七十一頁，共113頁。例17INSERT INTO student.學(xué)生(xu sheng) VALUES(04074122,吳紅霞,女,浙江,正常)CREATE TABLE student.學(xué)生(xu sheng)(學(xué)號(hào) char(

46、8) PRIMARY KEY, 院系 AS CONVERT(Tinyint,SUBSTRING(學(xué)號(hào),3,2) PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(編號(hào)), 姓名 char(10) NOT NULL, 性別 char(2) CHECK (性別=男 OR 性別=女), 生源 char(6), 狀態(tài) char(4) CHECK (狀態(tài)IN(正常,留級(jí),休學(xué),退學(xué))違背(wibi)參照完整性約束第72頁/共112頁第七十二頁，共113頁。第73頁/共112頁第七十三頁，共113頁。例18INSERT INTO student.學(xué)生

47、 VALUES(04026205,張鴻飛,南,北京(bi jn),正常)CREATE TABLE student.學(xué)生(學(xué)號(hào) char(8) PRIMARY KEY, 院系 AS CONVERT(Tinyint,SUBSTRING(學(xué)號(hào),3,2) PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(編號(hào)), 姓名 char(10) NOT NULL, 性別 char(2) CHECK (性別=男 OR 性別=女), 生源 char(6), 狀態(tài)(zhungti) char(4) CHECK (狀態(tài)(zhungti)IN(正常,留級(jí),休學(xué),退

48、學(xué))違背(wibi)域完整性約束第74頁/共112頁第七十四頁，共113頁。第75頁/共112頁第七十五頁，共113頁。CREATE TABLE student.教師(教師編號(hào) char(6) PRIMARY KEY, 院系 AS CONVERT(Tinyint,SUBSTRING(教師編號(hào),1,2) PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(編號(hào)), 姓名 char(10) NOT NULL, 性別 char(2) CHECK (性別=男 OR 性別=女), 職稱 char(6) CHECK (職稱IN (教授,副教授,講師,助

49、教(zhjio), 專業(yè) char(16) 插入教師(jiosh)表記錄，注意教師(jiosh)編號(hào)的PRIMARY KEY約束，院系是計(jì)算列及其REFRENCES約束等。 第76頁/共112頁第七十六頁，共113頁。CREATE TABLE student.課程(課程編號(hào) char(6) PRIMARY KEY, 課程名稱 char(24) NOT NULL, 責(zé)任教師 char(6) CONSTRAINT duty_teacher FOREIGN KEY REFERENCES student.教師(教師編號(hào)), 學(xué)時(shí) tinyint NOT NULL, 課程性質(zhì) char(8) CHECK

50、 (課程性質(zhì)IN (公共基礎(chǔ),專業(yè)(zhuny)基礎(chǔ),專業(yè)(zhuny)選修,任意選修)插入課程表記錄，注意(zh y)相關(guān)約束。第77頁/共112頁第七十七頁，共113頁。例20CREATE TABLE student.選課(學(xué)號(hào) char(8) FOREIGN KEY REFERENCES student.學(xué)生, 課程(kchng)編號(hào) char(6) FOREIGN KEY REFERENCES student.課程(kchng), 考試成績 tinyint CHECK (考試成績 BETWEEN 0 AND 100) DEFAULT NULL PRIMARY KEY (學(xué)號(hào),課程(kc

51、hng)編號(hào)) )INSERT INTO student.選課(學(xué)號(hào),課程(kchng)編號(hào),考試成績) VALUES(04014122,B00016,88)第78頁/共112頁第七十八頁，共113頁。第79頁/共112頁第七十九頁，共113頁。例21CREATE TABLE student.選課(學(xué)號(hào) char(8) FOREIGN KEY REFERENCES student.學(xué)生(xu sheng), 課程編號(hào) char(6) FOREIGN KEY REFERENCES student.課程, 考試成績 tinyint CHECK (考試成績 BETWEEN 0 AND 100) DE

52、FAULT NULL PRIMARY KEY (學(xué)號(hào),課程編號(hào)) )INSERT INTO student.選課 VALUES(04014111,X00201,NULL)第80頁/共112頁第八十頁，共113頁。第81頁/共112頁第八十一頁，共113頁。例22CREATE TABLE student.選課(學(xué)號(hào) char(8) FOREIGN KEY REFERENCES student.學(xué)生, 課程(kchng)編號(hào) char(6) FOREIGN KEY REFERENCES student.課程(kchng), 考試成績 tinyint CHECK (考試成績 BETWEEN 0 AN

53、D 100) DEFAULT NULL PRIMARY KEY (學(xué)號(hào),課程(kchng)編號(hào)) )INSERT INTO student.選課VALUES(04014111,B00016,DEFAULT)第82頁/共112頁第八十二頁，共113頁。第83頁/共112頁第八十三頁，共113頁。例23CREATE TABLE student.選課(學(xué)號(hào) char(8) FOREIGN KEY REFERENCES student.學(xué)生(xu sheng), 課程編號(hào) char(6) FOREIGN KEY REFERENCES student.課程, 考試成績 tinyint CHECK (考試

54、成績 BETWEEN 0 AND 100) DEFAULT NULL PRIMARY KEY (學(xué)號(hào),課程編號(hào)) )INSERT INTO student.選課(學(xué)號(hào),課程(kchng)編號(hào)) VALUES(04014101,B00018)第84頁/共112頁第八十四頁，共113頁。第85頁/共112頁第八十五頁，共113頁。例24違反參照(cnzho)完整性的輸入CREATE TABLE student.選課(學(xué)號(hào) char(8) FOREIGN KEY REFERENCES student.學(xué)生(xu sheng), 課程編號(hào) char(6) FOREIGN KEY REFERENCES

55、student.課程, 考試成績 tinyint CHECK (考試成績 BETWEEN 0 AND 100) DEFAULT NULL PRIMARY KEY (學(xué)號(hào),課程編號(hào)) )INSERT INTO student.選課VALUES(04016116,B00017，52)第86頁/共112頁第八十六頁，共113頁。第87頁/共112頁第八十七頁，共113頁。第88頁/共112頁第八十八頁，共113頁。插入插入(ch r)操作時(shí)檢查完整性？操作時(shí)檢查完整性？ 檢查實(shí)體(sht)完整性規(guī)則 檢查參照完整性規(guī)則 檢查用戶定義完整性規(guī)則 第89頁/共112頁第八十九頁，共113頁。檢查實(shí)體(s

56、ht)(sht)完整性規(guī)則 如果插入元組的主關(guān)鍵字的屬性不為空值、并且相應(yīng)的屬性值在關(guān)系(gun x)中不存在（即保持唯一性），則可以執(zhí)行插入操作，否則不可以執(zhí)行插入操作。 第90頁/共112頁第九十頁，共113頁。檢查(jinch)(jinch)參照完整性規(guī)則 如果是向被參照關(guān)系插入元組，則無須檢查參照完整性；如果是向參照關(guān)系插入元組，則要檢查外部關(guān)鍵字屬性上的值是否(sh fu)在被參照關(guān)系中存在對(duì)應(yīng)的主關(guān)鍵字的值，如果存在則可以執(zhí)行插入操作，否則不允許執(zhí)行插入操作。另外，如果插入元組的外部關(guān)鍵字允許為空值，則當(dāng)外部關(guān)鍵字是空值時(shí)也允許執(zhí)行插入操作。 第91頁/共112頁第九十一頁，共11

57、3頁。檢查用戶定義(dngy)(dngy)完整性規(guī)則 如果(rgu)插入的元組在相應(yīng)的屬性值上遵守了用戶定義完整性規(guī)則(包括CHECK約束、UNIQUE約束、NULL或NOT NULL約束)，則可以執(zhí)行插入操作，否則不可以執(zhí)行插入操作。 第92頁/共112頁第九十二頁，共113頁。刪除操作(cozu)(cozu)及其完整性約束 SQL的刪除語句是DELETE，常用格式： DELETE FROM schema_name.table_name WHERE 其中： schema_name指出(zh ch)模式名； table_name指出(zh ch)從哪個(gè)表刪除記錄； 用來指出(zh ch)刪除記

58、錄的條件，默認(rèn)是刪除全部記錄。第93頁/共112頁第九十三頁，共113頁。例25DELETE student.學(xué)生(xu sheng) WHERE 姓名=吳紅霞 刪除操作失敗(shbi)，違背參照完整性 CREATE TABLE student.選課(學(xué)號(hào) char(8) FOREIGN KEY REFERENCES student.學(xué)生, 課程(kchng)編號(hào) char(6) FOREIGN KEY REFERENCES student.課程(kchng), 考試成績 tinyint CHECK (考試成績 BETWEEN 0 AND 100) DEFAULT NULL PRIMARY K

59、EY (學(xué)號(hào),課程(kchng)編號(hào)) )第94頁/共112頁第九十四頁，共113頁。第95頁/共112頁第九十五頁，共113頁。例26DELETE student.教師(jiosh) WHERE 教師(jiosh)編號(hào)=011122ALTER TABLE student.課程(kchng)ADD 責(zé)任教師char(6) NULLCONSTRAINT ref_teacher FOREIGN KEY REFERENCES student.教師(教師編號(hào))ON DELETE SET NULL ON UPDATE CASCADE 刪除成功。如果有課程記錄的責(zé)任教師(jiosh)參照被刪除記錄，則將該課程記錄的責(zé)任教師(jiosh)字段置為NULL。第96頁/共112頁第九十六頁，共113頁。第97頁/共112頁第九十七頁，共113頁。第98頁/共112頁第九十八頁，共113頁。舉例(j l)27DELETE student.選課 WHERE 課程(kchng)編號(hào)=B00018 AND 考試成績 IS NULL刪