關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL語言.ppt

1、第四章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,4.1 SQL概述 4.2 數(shù)據(jù)定義 4.3 數(shù)據(jù)查詢 4.4 數(shù)據(jù)更新 4.5 視圖,第四章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,4.1 SQL 概述 SQL（Structed Query Language），即結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，SQL是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫語言。,4.1.1 SQL的產(chǎn)生與發(fā)展,SQL是在1974年由Boycee和Chamberlin提出的，并在IBM公司研制的關(guān)系數(shù)據(jù)庫管理系統(tǒng)原型System R上實(shí)現(xiàn)。 由于SQL簡(jiǎn)單易學(xué)，功能豐富，深受用戶及計(jì)算機(jī)工業(yè)界歡迎，因此被數(shù)據(jù)庫廠商所采用。經(jīng)各公司的不斷修改、擴(kuò)充和完

2、善，SQL得到業(yè)界的認(rèn)可。 1986年10月美國(guó)國(guó)家標(biāo)準(zhǔn)局的數(shù)據(jù)庫委員會(huì)X3H2批準(zhǔn)了SQL作為關(guān)系數(shù)據(jù)庫語言的美國(guó)標(biāo)準(zhǔn)。同年公布了SQL標(biāo)準(zhǔn)文本。 1987年國(guó)際標(biāo)準(zhǔn)化組織也通過了這一標(biāo)準(zhǔn)。 SQL標(biāo)準(zhǔn)從1986年公布以來隨著數(shù)據(jù)庫技術(shù)的發(fā)展不斷發(fā)展，不斷豐富。,4.1.2 SQL的特點(diǎn),一、綜合統(tǒng)一 SQL集數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)控制語言DCL的功能于一體，語言風(fēng)格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動(dòng)。 二、高度非過程化 SQL進(jìn)行數(shù)據(jù)操作，只要提出“做什么”,而無需指明“怎么做”,因此無需了解存取路徑。存取路徑的選擇以及SQL的操作過程由系統(tǒng)自動(dòng)完成。,4.

3、1.2 SQL的特點(diǎn),三、面向集合的操作方式 SQL采用面向集合的操作方式，不僅操作對(duì)象、查找結(jié)果可以是元組的集合，而且一次插入、刪除、更新操作的對(duì)象也可以是元組的集合。 四、以同一種語法結(jié)構(gòu)提供多種使用方式 作為獨(dú)立語言，它能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式，用戶可以在終端鍵盤上直接鍵入SQL命令對(duì)數(shù)據(jù)庫進(jìn)行操作； 作為嵌入式語言，SQL語句能夠嵌入到高級(jí)語言（例如C，C+，Java）程序中，供程序員設(shè)計(jì)程序時(shí)使用。,4.1.2 SQL的特點(diǎn),五、語言簡(jiǎn)潔易學(xué) SQL功能極強(qiáng)，但由于設(shè)計(jì)巧妙，語言十分簡(jiǎn)潔，完成核心功能只用了9個(gè)動(dòng)詞。SQL接近英語口語，因此容易學(xué)習(xí)，容易使用。,4.2數(shù)據(jù)定義

4、,4.2.1基本表的定義、刪除、與修改 一、定義基本表 CREATE TABLE （ 列級(jí)完整性約束條件 , 列級(jí)完整性約束條件 , ）；,4.2數(shù)據(jù)定義,例:建立一個(gè)“學(xué)生”表Student。 create table Student ( sno char(9) primary key, sname char(20) unique, ssex char(2), sage smallint, sdept char(20) );,4.2數(shù)據(jù)定義,例:建立一個(gè)“課程”表Course。 create table Course ( cno char(4) primary key, cname char

5、(40), cpno char(4), ccredit smallint, foreign key (cpno) references Course(cno) );,4.2數(shù)據(jù)定義,例：建立學(xué)生選課表SC。 create table SC ( sno char(9), cno char(4), grade smallint, foreign key (sno) references student(sno), foreign key (cno) references course(cno) ),4.2數(shù)據(jù)定義,4.2.2修改基本表 ALTER TABLE ADD 完整性約束 DROP ALTE

6、R COLUMN ；,4.2數(shù)據(jù)定義,例：向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。 alter table Student add s_entrance datetime; 例：將年齡的數(shù)據(jù)類型由smallint改為int。 alter table Student alter column sage int;,4.2數(shù)據(jù)定義,例：增加課程名必須取唯一值的約束。 alter table course add unique(cname);,4.2數(shù)據(jù)定義,4.2.3刪除基本表 DROP TABLE RESTRICT|CASCADE RESTRICT:該表的刪除是有限制條件的。欲刪

7、除的基本表不能被其他表的約束所引用(如CHECK,FOREIGN KEY等約束)，不能有視圖，不能有觸發(fā)器，不能有存儲(chǔ)過程或函數(shù)等。如果存在這些依賴該表的對(duì)象，則此表不能被刪除。 CASCADE:該表的刪除沒有限制條件。在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象，例如視圖，都將被一起刪除。 (SQL Server 2000中不支持RESTRICT|CASCADE),4.2數(shù)據(jù)定義,4.2.3索引的建立與刪除 建立索引是加快查詢速度的有效手段。用戶可以根據(jù)應(yīng)用環(huán)境的需要，在基本表上建立一個(gè)或多個(gè)索引，以提供多種存取路徑，加快查找速度。 建立與刪除索引有數(shù)據(jù)庫管理員DBA或表的屬主，負(fù)責(zé)完成。,4.2數(shù)據(jù)

8、定義,4.2.3索引的建立與刪除 一、建立索引 CREATE UNIQUECLUSTER INDEX ON （, ） 索引的排列次序，可選ASC或DESC，缺省值為ASC。 UNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄。 CLUSTER表示要建立的索引是聚簇索引。所謂聚簇索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織。,4.2數(shù)據(jù)定義,二、刪除索引 DROP INDEX 例：索引示例。 create table abc ( a int, b int ); create unique index abcindex on abc(a desc); drop index abc.

9、abcindex; create clustered index abcindex on abc(a desc);,4.3數(shù)據(jù)查詢,SELECT ALL|DISTINCT, FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;,4.3.1單表查詢,單表查詢是指僅涉及一個(gè)表的查詢。 一、選擇表中的若干列 1.查詢指定列 例1.查詢?nèi)w學(xué)生的學(xué)號(hào)和姓名。 select sno,sname from student; 例2.查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。 select sname,sno,sdept from student;,4.3.1單表查詢,2.查

10、詢?nèi)苛?例3.查詢?nèi)w學(xué)生的詳細(xì)記錄。 select * from student; 3.查詢經(jīng)過計(jì)算的值 例4.查詢?nèi)w學(xué)生的姓名及其出生年份。 select sname,2012-sage from student; 例5.查詢?nèi)w學(xué)生的姓名、出生年份和所在的院系，要求用小寫字母表示所有系名。 select sname,year of birth:,2012-sage,lower(sdept) from student;,4.3.1單表查詢,二、選擇表中的若干元組 1.消除取值重復(fù)的行 例6.查詢選修了課程的學(xué)生學(xué)號(hào)。 select sno from sc; select distinc

11、t sno from sc; 2.查詢滿足條件的元組 （1） 比較大小 例7.查詢計(jì)算機(jī)科學(xué)系全體學(xué)生的名單。 select sname from student where sdept=CS;,4.3.1單表查詢,例8.查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。 select sname,sage from student where sage20; 例9.查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)。 select distinct sno from sc where grade60;,4.3.1單表查詢,（2）確定范圍 例10.查詢年齡在20-23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年

12、齡。 select sname,sdept,sage from student where sage between 20 and 23; 例11.查詢年齡不在20-23歲之間的學(xué)生姓名、系別和年齡。 select sname,sdept,sage from student where sage not between 20 and 23;,4.3.1單表查詢,（3）確定集合 例12.查詢計(jì)算機(jī)科學(xué)系（CS）、數(shù)學(xué)系（MA）和信息系（IS）學(xué)生的姓名和性別。 select sname,ssex from student where sdept in(CS,MA,IS); 例13.查詢既不是計(jì)算

13、機(jī)科學(xué)系、數(shù)學(xué)系，也不是信息系的學(xué)生的姓名和性別。 select sname,ssex from student where sdept not in(CS,MA,IS);,4.3.1單表查詢,（4）字符匹配 謂詞LIKE可以用來進(jìn)行字符串的匹配。 NOT LIKE ESCAPE %代表任意長(zhǎng)度的字符串（長(zhǎng)度可以為0）； -（下橫線）代表任意單個(gè)字符。,4.3.1單表查詢,例14.查詢學(xué)號(hào)為200215121的學(xué)生的詳細(xì)情況。 select * from student where sno like 200215121; 例15.查詢所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別。 select * fro

14、m student where sname like 劉%; 例16.查詢姓“歐陽”且全名為3個(gè)漢字的學(xué)生的姓名。 select sname from student where sname like 歐陽_;,4.3.1單表查詢,例17.查詢名字中第2個(gè)字為“陽”字的學(xué)生的姓名和學(xué)號(hào)。 select sname,sno from student where sname like _陽%; 例18.查詢所有不姓劉的學(xué)生姓名。 select sname from student where sname not like 劉%;,4.3.1單表查詢,例19.查詢DB_Design課程的課程號(hào)和學(xué)分

15、。 select cno,ccredit from course where cname like DB_Design escape ; 例20.查詢以“DB_”開頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況。 select * from course where cname like DB_%i_ escape ;,4.3.1單表查詢,（5）涉及空值的查詢 例21.查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。 select sno,cno from sc where grade is null; 例22.查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。 select sno,cno from sc where gr

16、ade is not null;,4.3.1單表查詢,（6）多重條件查詢 例23.查詢計(jì)算機(jī)科學(xué)系年齡在20歲以下的學(xué)生姓名。 select sname from student where sdept=CS and sage20;,4.3.1單表查詢,三、ORDER BY子句 例24.查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)的降序排列。 select sno,grade from sc where cno=3 order by grade desc; 例25.查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，統(tǒng)一系中的學(xué)生按年齡降序排列。 select * from stude

17、nt order by sdept,sage desc;,4.3.1單表查詢,四、聚集函數(shù) count(列名) max(列名) min(列名) sum(列名) avg(列名),4.3.1單表查詢,例26.查詢學(xué)生總?cè)藬?shù)。 select count(*) from student; 例27.查詢選修了課程的學(xué)生人數(shù)。 select count(distinct sno) from sc; 例28.計(jì)算1號(hào)課程的學(xué)生平均成績(jī)。 select avg(grade) from sc where cno=1;,4.3.1單表查詢,五、GROUP BY子句 GROUP BY子句將查詢結(jié)果按某一列或多列的值

18、分組，值相等的為一組。 例31. 求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。 select cno,count(sno) from sc group by cno;,4.3.1單表查詢,例32.查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。 select sno from sc group by sno having count(*)3;,4.3.2 連接查詢,若一個(gè)查詢同時(shí)涉及兩個(gè)以上的表，則稱之為連接查詢。 一、等值與非等值連接查詢 連接查詢的WHERE子句用來連接兩個(gè)表的條件稱為連接條件或連接謂詞。 . 其中比較運(yùn)算符主要有：=、=、)等。 此外連接謂詞還可以使用下面形式： .BETWEEN.AND .,4.3

19、.2 連接查詢,當(dāng)連接運(yùn)算為=時(shí)，稱為等值連接。使用其他運(yùn)算符稱為非等值連接。 連接謂詞中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的，但名字不必相同。 例33.查詢每個(gè)學(xué)生及其選修課程的情況。 select student.*,sc.* from student,sc where student.sno=sc.sno;,4.3.2 連接查詢,例34. 對(duì)例33用自然連接完成。 select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;,4.3.2 連接查

20、詢,二、自身連接 連接操作不僅可以在兩個(gè)表之間進(jìn)行，也可以是一個(gè)表與自己進(jìn)行連接，稱為表的自身連接。 select o,second.cpno from course first,course second where first.cpno=o;,4.3.2 連接查詢,例36.以student為主體列出每個(gè)學(xué)生的基本情況及其選課情況。 select student.sno,sname,ssex,sage,sdept,cno,grade from student left outer join sc on student.sno=sc.sno,4.3.2 連接查詢,四、復(fù)合條件連接 例37.查詢

21、選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生。 select student.sno,sname from student,sc where student.sno=sc.sno and o=2 and sc.grade90;,4.3.2 連接查詢,四、復(fù)合條件連接 例38.查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)。 select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and o=o;,4.3.3嵌套查詢,在SQL語言中，一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊

22、。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。 select sname from student where sno in ( select sno from sc where cno=2 );,4.3.3嵌套查詢,一、帶有IN謂詞的子查詢。 例39.查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。 select sno,sname,sdept from student where sdept in ( select sdept from student where sname=劉晨 );,4.3.3嵌套查詢,例40.查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)

23、號(hào)和姓名。 select sno,sname from student where sno in ( select sno from sc where cno in ( select cno from course where cname=信息系統(tǒng) ) );,4.3.3嵌套查詢,子查詢的查詢條件不依賴于父查詢，這類子查詢叫作不相關(guān)子查詢。 子查詢的查詢條件依賴于父查詢，這類子查詢稱為相關(guān)子查詢。,4.3.3嵌套查詢,二、帶有比較運(yùn)算符的子查詢 例41.找出每個(gè)學(xué)生超過他選修課程平均成績(jī)的課程號(hào)。 select sno,cno from sc x where grade ( select avg

24、(grade) from sc y where y.sno=x.sno ),4.3.3嵌套查詢,三、帶有any或all謂詞的子查詢 子查詢返回單值時(shí)可以用比較運(yùn)算符，但返回多值時(shí)要用ANY或ALL謂詞修飾符。而使用ANY或ALL謂詞時(shí)則必須同時(shí)使用比較運(yùn)算符。 例42.查詢其他系中比計(jì)算機(jī)科學(xué)系某一學(xué)生年齡小的學(xué)生姓名和年齡。 select sname,sage from student where sage CS,4.3.3嵌套查詢,例43.查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。 select sname,sage from student where sage CS,

25、4.3.3嵌套查詢,例45.查詢沒有選修1號(hào)課程的學(xué)生姓名。 select sname from student where not exists ( select * from sc where sno=student.sno and cno=1 ),4.3.3嵌套查詢,例46 查詢選修了全部課程的學(xué)生姓名。 可將題目的意思轉(zhuǎn)換成等價(jià)的用存在量詞的形式：查詢這樣的學(xué)生，沒有一門課程是他不選修的。,4.3.3嵌套查詢,select sname from student where not exists ( select * from course where not exists( selec

26、t * from sc where sc.sno=student.sno and o=o ) ),4.3.3嵌套查詢,例47.查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號(hào)碼。 本查詢可以用邏輯蘊(yùn)含來表達(dá)：查詢學(xué)號(hào)為x的學(xué)生，對(duì)所有的課程y，只要200215122學(xué)生選修了課程y，則x也選修了y。 用p表示謂詞“學(xué)生200215122選修了課程y” 用q表示謂詞“學(xué)生x選修了課程y” 則上述查詢?yōu)?4.3.3嵌套查詢,該查詢可以轉(zhuǎn)換為如下等價(jià)形式,它所表達(dá)的語義為：不存在這樣的課程y,學(xué)生200215122選修了y,而學(xué)生x沒有選。,4.3.3嵌套查詢,select distinc

27、t sno from sc x where not exists ( select * from sc y where y.sno=200215122 and not exists( select * from sc z where z.sno=x.sno and o=o ) ),4.3.4集合查詢,例48.查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 select * from student where sdept=CS union select * from student where sage=19,4.3.4集合查詢,例49.查詢選修了課程1或者選修了課程2的學(xué)生。 select

28、 sno from sc where cno=1 union select sno from sc where cno=2,4.3.4集合查詢,例50.查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集。 select * from student where sdept=CS intersect select * from student where sage=19/sql server2000不支持intersect,4.3.4集合查詢,例51查詢既選修了課程1又選修了課程2的學(xué)生。 select sno from sc where cno=1 intersect select sno

29、from sc where cno=2 /sql server2000不支持intersect,4.3.4集合查詢,例52.查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。 select * from student where sdept=CS except select * from student where sage=19 /sql server2000不支持except,4.4數(shù)據(jù)更新,數(shù)據(jù)更新操作有3種：向表中添加若干行數(shù)據(jù)、修改表中的數(shù)據(jù)和刪除表中的若干行數(shù)據(jù)。 4.4.1插入數(shù)據(jù) SQL的數(shù)據(jù)插入語句INSERT通常有兩種形式。 插入一個(gè)元組 插入子查詢結(jié)果,4.4數(shù)據(jù)更

30、新,4.4.1插入數(shù)據(jù) 一、插入元組 插入元組的INSERT語句的格式為 INSERT INTO(,) VALUES(,);,4.4數(shù)據(jù)更新,例1.將一個(gè)新學(xué)生元組（學(xué)號(hào)：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 insert into student(sno,sname,ssex,sdept,sage) values(200215128,陳冬,男,IS,18),4.4數(shù)據(jù)更新,例2.將學(xué)生張成民的信息插入到Student表中。 insert into student values(200215126,張成民,男,18,CS),4.4數(shù)據(jù)

31、更新,例3.插入一條選課記錄（200215128，1） insert into sc(sno,cno) values(200215128,1) insert into sc values(200215128,1,null),4.4數(shù)據(jù)更新,二、插入子查詢結(jié)果 INSERT INTO (,) 子查詢；,4.4數(shù)據(jù)更新,例4.對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。 首先在數(shù)據(jù)庫中建立一個(gè)新表，其中一列存放系名，另一列 存放相應(yīng)的學(xué)生平均年齡。 create table Dept_age ( sdept char(5), Avg_age smallint );,4.4數(shù)據(jù)更新,然后對(duì)st

32、udent表按系分組求平均年齡，再把系名和平均年齡存入新表中。 insert into Dept_age(sdept,Avg_age) select sdept,avg(sage) from student group by sdept;,4.4數(shù)據(jù)更新,4.4.2修改數(shù)據(jù) 修改操作又稱為更新操作，其語句一般格式為 UPDATE SET =,= WHERE ;,4.4數(shù)據(jù)更新,一、修改某一個(gè)元組的值 例5.將學(xué)生200215121的年齡改為22歲。 update student set sage=22 where sno=200215121,4.4數(shù)據(jù)更新,二、修改多個(gè)元組的值 例6.將所有學(xué)

33、生的年齡增加1歲。 update student set sage=sage+1;,4.4數(shù)據(jù)更新,三、帶子查詢的修改語句 例7.將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。 update sc set grade=0 where CS= ( select sdept from student where student.sno=sc.sno ),4.4數(shù)據(jù)更新,4.4.3刪除數(shù)據(jù) 一般格式 DELETE FROM WHERE ；,4.4數(shù)據(jù)更新,一、刪除一個(gè)元組的值 例8.刪除學(xué)號(hào)為200215128的學(xué)生記錄 delete from student where sno=200215128;,4.4數(shù)

34、據(jù)更新,二、刪除多個(gè)元組的值 delete from sc;,4.4數(shù)據(jù)更新,三、帶有查詢的刪除語句 例10.刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。 delete from sc where CS= ( select sdept from student where student.sno=sc.sno );,4.5視圖,視圖是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表。它與基本表不同，是一個(gè)虛表。數(shù)據(jù)庫中只存放視圖的定義，而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來的基本表中。 視圖一經(jīng)定義，就可以和基本表一樣被查詢、被刪除。也可以在一個(gè)視圖之上再定義新的視圖，但對(duì)視圖的更新（增、刪、改）操作則有一

35、定的限制。,4.5.1定義視圖,一、建立視圖 CREATE VIEW (,) AS WITH CHECK OPTION 子查詢可以是任意復(fù)雜的SELECT語句。但通常不允許含有ORDER BY子句和DISTINCT短語。 WITH CHECK OPTION表示對(duì)視圖進(jìn)行UPDATE,INSERT,DELETE操作時(shí)要保證更新、插入或刪除的行滿足視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）,4.5.1定義視圖,create view is_student as select sno,sname,ssex from student where sdept=IS insert into is_st

36、udent values(200215129,王六,男),4.5.1定義視圖,插入語句可以執(zhí)行。 select * from is_student select * from student,4.5.1定義視圖,create view is_student as select sno,sname,ssex from student where sdept=IS with check option,4.5.1定義視圖,insert into is_student values(200215129,王六,男) 服務(wù)器: 消息 550，級(jí)別 16，狀態(tài) 1，行 1 試圖進(jìn)行的插入或更新已失敗，原因是

37、目標(biāo)視圖或者目標(biāo)視圖所跨越的某一視圖指定了 WITH CHECK OPTION，而該操作的一個(gè)或多個(gè)結(jié)果行又不符合 CHECK OPTION 約束的條件。 語句已終止。,4.5.1定義視圖,子查詢中帶有order by 子句 CREATE VIEW FSTUDENT AS SELECT * FROM STUDENT WHERE SSEX=女 order by sage 服務(wù)器: 消息 1033，級(jí)別 15，狀態(tài) 1，過程 FSTUDENT，行 5 除非同時(shí)指定了 TOP，否則 ORDER BY 子句在視圖、內(nèi)嵌函數(shù)、派生表和子查詢中無效。,4.5.1定義視圖,CREATE VIEW FSTUD

38、ENT AS SELECT top 10 * FROM STUDENT WHERE SSEX=女 order by sage,4.5.1定義視圖,子查詢中含有DISTINCT短語 create view abc as select distinct sno from sc where grade is null,4.5.1定義視圖,組成視圖的屬性列名或者全部省略或者全部指定。如果省略了視圖的各個(gè)屬性列名，則隱含該視圖由子查詢中的SELECT子句目標(biāo)列中的諸字段組成。但在下列三種情況下必須明確指定組成視圖的所有列名。 （1）某個(gè)目標(biāo)列不是單純的屬性名，而是聚集函數(shù)或列表達(dá)式；,4.5.1定義視圖

39、,create view test1 as select sno,count(*) from sc group by sno 服務(wù)器: 消息 4511，級(jí)別 16，狀態(tài) 1，過程 test1，行 3 創(chuàng)建視圖或函數(shù)失敗，因?yàn)闆]有為第 2 列指定列名。,4.5.1定義視圖,create view test2 as select sname,2012-sage from student 服務(wù)器: 消息 4511，級(jí)別 16，狀態(tài) 1，過程 test2，行 3 創(chuàng)建視圖或函數(shù)失敗，因?yàn)闆]有為第 2 列指定列名。,4.5.1定義視圖,（2）多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段 （sql serve

40、r 2000中不符合） create view test3 as select student.sno,cno,grade from student,sc where student.sno=sc.sno,4.5.1定義視圖,（3）需要在視圖中為某個(gè)列啟用新的更適合的名字。 create view test4(姓名,年齡) as select sname,sage from student,4.5.1定義視圖,例1.建立信息系學(xué)生的視圖。 create view is_student as select sno,sname,sage from student where sdept=IS,4.

41、5.1定義視圖,例2.建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有信息系的學(xué)生。,create view IS_Student1 as select sno,sname,sage from student where sdept=IS with check option,4.5.1定義視圖,例3.建立信息系選修了1號(hào)課程的學(xué)生的視圖。 create view IS_S1(sno,sname,grade) as select student.sno,sname,grade from student,sc where sdept=IS and student.sno=sc.s

42、no and o=1,4.5.1定義視圖,例4.建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以以上的學(xué)生的視圖。,create view IS_S2 as select sno,sname,grade from IS_S1 where grade=90,4.5.1定義視圖,例5.定義一個(gè)反映學(xué)生出生年份的視圖。 create view BT_S(sno,sname,sbirth) as select sno,sname,2012-sage from student,4.5.1定義視圖,例6.將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖。 create view S_G(Sno,Gavg) as sele

43、ct sno,avg(grade) from sc group by sno,4.5.1定義視圖,若一個(gè)視圖是從單個(gè)基本表導(dǎo)出的，并且只是去掉了基本表的某些行和某些列，但保留了主碼，我們稱這視圖為行列子集視圖。 在基本表中并不實(shí)際存在的列稱為虛擬列。,帶虛擬列的視圖也稱為帶表達(dá)式的視圖。,4.5.1定義視圖,二、刪除視圖 DROP VIEW CASCADE; 例8. drop view bt_s,4.5.2查詢視圖,例9.在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。,select sno,sage from is_student where sage20,4.5.1查詢視圖,例10.查詢選修

44、了1號(hào)課程的信息系學(xué)生。 select IS_student.sno,sname from is_student,sc where is_student.sno=sc.sno and o=1,4.5.2查詢視圖,例11.在S_G視圖中查詢平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和平均成績(jī)。,select * from s_g where gavg=90,4.5.3更新視圖,例12將信息系學(xué)生視圖IS_Student中學(xué)號(hào)為200215122的學(xué)生姓名改為“劉晨”。,update is_student set sname=劉辰 where sno=200215122,4.5.3更新視圖,例13向信息系學(xué)生

45、視圖is_student中插入一個(gè)新的學(xué)生記錄，其中學(xué)號(hào)為200215129，姓名為趙新，年齡為20歲。 insert into is_student values(200215129,趙鑫,20),4.5.3更新視圖,例14.刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為200215129的記錄。 delete from is_student where sno=200215125,4.5.3更新視圖,在關(guān)系數(shù)據(jù)庫中，并不是所有的視圖都是可更新的，因?yàn)橛行┮晥D的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新。 把視圖S_G中學(xué)號(hào)為200215122的學(xué)生的平均成績(jī)改成90分。 update

46、s_g set gavg=90 where sno=200215121 服務(wù)器: 消息 4403，級(jí)別 16，狀態(tài) 1，行 1 視圖或函數(shù) s_g 不可更新，因?yàn)樗酆稀?4.5.3更新視圖,在視圖中修改數(shù)據(jù)的幾個(gè)問題 對(duì)于多表視圖不能使用DELETE 語句 create view score_view(sname,cno,grade) as select sname,cno,grade from student,sc where student.sno=sc.sno,4.5.3更新視圖,delete from score_view 服務(wù)器: 消息 4405，級(jí)別 16，狀態(tài) 1，行 1 視圖或函數(shù) score_view 不可更新，因?yàn)樾薷臅?huì)影響多個(gè)基表。,4.5.3更新視圖,除非底層表的所有非空列都已經(jīng)在視圖中出現(xiàn)否則不能使用INSERT 語句。有這個(gè)限制的原因是SQL 不知道應(yīng)該將什么數(shù)據(jù)插入到NOT NULL 限制列中。 cr