學(xué)習(xí)情境4 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言-SQL

主編樊穎軍副主編張光主審殷鋒社電子商務(wù)數(shù)據(jù)庫(kù)技術(shù)普通高等教育“十二五”規(guī)劃教材電子商務(wù)專(zhuān)業(yè)項(xiàng)目是教學(xué)課題成果中國(guó)水利水電出版社子學(xué)習(xí)情境一認(rèn)識(shí)SQL語(yǔ)言

任務(wù)一SQL語(yǔ)言的產(chǎn)生與發(fā)展任務(wù)二SQL語(yǔ)言的特點(diǎn)任務(wù)三SQL語(yǔ)言的功能子學(xué)習(xí)情境二SQL的數(shù)據(jù)定義功能

任務(wù)一定義、刪除和修改基本表1．定義基本表建立數(shù)據(jù)庫(kù)最重要的一步就是定義基本表。SQL語(yǔ)言使用CREATETABLE語(yǔ)句定義基本表，其一般格式如下：CREATETABLE<表名>(<列名><數(shù)據(jù)類(lèi)型>[列級(jí)完整性約束條件][,<列名><數(shù)據(jù)類(lèi)型>[列級(jí)完整性約束條件]]…[,<表級(jí)完整性約束條件>]);2．修改基本表隨著應(yīng)用環(huán)境和應(yīng)用需求的變化，有時(shí)需要修改已建立好的基本表，SQL語(yǔ)言用ALTERTABLE語(yǔ)句修改基本表，其一般格式為：ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類(lèi)型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類(lèi)型>];其中<表名>是要修改的基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件，MODIFY子句用于修改原有的列定義，包括個(gè)性列名和數(shù)據(jù)類(lèi)型。Access不支持MODIFY子句，要修改列名及數(shù)據(jù)類(lèi)型需要進(jìn)行先刪除后增加的操作。3．刪除基本表當(dāng)某個(gè)基本表不再需要時(shí)，可以使用DROPTABLE語(yǔ)句刪除它。其一般格式為：DROPTABLE<表名>基本表定義一旦刪除，表中的數(shù)據(jù)、此表上建立的索引和視圖都將自動(dòng)被刪除掉。因此，執(zhí)行刪除基本表的操作一定要格外小心。4．補(bǔ)充定義主鍵由于SQL并不要求每個(gè)表都定義主鍵，所以提供了一個(gè)補(bǔ)充定義主鍵的命令，在需要時(shí)定義主鍵。定義主鍵仍然用ALTERTABLE命令，其語(yǔ)法格式為：ALTERTABLE表名ADDPRIMARYKEY(列名);被定義為主鍵的列名必須滿足取值非空且值唯一的條件。5．撤消主鍵定義撤消主鍵定義也用ALTERTABLE命令，Access軟件中撤消主鍵定義的語(yǔ)法格式為：ALTERTABLE表名DROPCONSTRAINTPRIMARYKEY;被定義為主鍵的列名必須滿足取值非空且值唯一的條件。6．補(bǔ)充定義外鍵在需要定義外鍵時(shí)，可以用ALTERTABLE命令，其語(yǔ)法格式為：ALTERTABLE表1ADDFOREIGNKEY[外鍵名]REFERENCES表2[ONDELETE{RESTRICT|CASCADE|SETNULL}];上述命令中，花括號(hào)中的三項(xiàng)需要任選一項(xiàng)，缺省情況下默認(rèn)為RESTRICT。7．撤消外鍵定義在需要撤消外鍵時(shí)，可以用ALTERTABLE命令，其語(yǔ)法格式為：ALTERTABLE表名DROPCONSTRAINT外鍵名;任務(wù)二視圖

1．建立視圖SQL語(yǔ)言用CREATEVIEW命令建立視圖，其一般格式為：CREATEVIEW<視圖名>[<列名>[,<列名>]…]AS<子查詢>[WITHCHECKOPTION];其中子查詢可以是任意復(fù)雜的SELECT語(yǔ)句，但通常不允許含有ORDERBY子句和DISTINCT短語(yǔ)。WITHCHECKOPTION表示對(duì)視圖進(jìn)行UPDATE、INSERT和DELETE操作時(shí)要保證更新、插入或刪除的行滿足視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）。在下列3種情況下必須明確指定組成視圖的所有列名：（1）某個(gè)目標(biāo)列不是單純的屬性名，而是集函數(shù)或列表達(dá)式。（2）多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段。（3）需要在視圖中為某個(gè)列啟用新的更合適的名字。2．刪除視圖刪除視圖語(yǔ)句的格式為：DROPVIEW<視圖名>;視圖刪除后，視圖的定義將從數(shù)據(jù)字典中刪除。但由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，不過(guò)該視圖已失效。用戶使用時(shí)會(huì)出錯(cuò)，要用DROPVIEW語(yǔ)句將它們一一刪除。3．查詢視圖視圖定義后，用戶就可以像對(duì)基本表一樣對(duì)視圖進(jìn)行查詢了。4．更新視圖更新視圖是指通過(guò)視圖來(lái)插入（INSERT）、刪除（DELETE）和修改（UPDATE）數(shù)據(jù)。由于視圖是不實(shí)際存儲(chǔ)數(shù)據(jù)的虛表，因此對(duì)視圖的更新最終要轉(zhuǎn)換為對(duì)基本表的更新。在關(guān)系數(shù)據(jù)庫(kù)中，并不是所有的視圖都是可更新的，因?yàn)橛行┮晥D的更新不能唯一地、有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新。5．視圖的作用視圖最終是定義在基本表之上的，對(duì)視圖的一切操作最終也要轉(zhuǎn)換為對(duì)基本表的操作。（1）視圖能夠簡(jiǎn)化用戶的操作視圖機(jī)制，使用戶可以將注意力集中在所關(guān)心的數(shù)據(jù)上。（2）視圖使用戶能以多種角度看待同一數(shù)據(jù)視圖機(jī)制，能使不同的用戶以不同的方式看待同一數(shù)據(jù)，當(dāng)許多不同種類(lèi)的用戶共享同一個(gè)數(shù)據(jù)庫(kù)時(shí)，這種靈活性是非常重要的。（3）視圖為重構(gòu)數(shù)據(jù)庫(kù)提供了一定程度的邏輯獨(dú)立性。（4）視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)。任務(wù)三索引

1．建立索引在SQL語(yǔ)言中，建立索引使用CREATEINDEX語(yǔ)句，其一般格式為：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);其中，<表名>是要建立索引的基本表的名字。索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔。<列名>后面還可以用<次序>指定索引值的排列次序，可選ASC（升序）或DESC（降序），默認(rèn)值為ASC。UNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄。CLUSTER表示要建立的索引是聚簇索引，即索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織。2．刪除索引索引一經(jīng)建立，就由系統(tǒng)使用和維護(hù)它，不需要用戶干預(yù)。建立索引是為了節(jié)省查詢操作的時(shí)間，但如果數(shù)據(jù)增加刪改頻繁，系統(tǒng)會(huì)花費(fèi)許多時(shí)間來(lái)維護(hù)索引。這時(shí)，可以刪除一些不必要的索引。在SQL語(yǔ)言中，Access刪除索引使用DROPINDEX語(yǔ)句，其一般格式為：DROPINDEX<索引名>ON表名;子學(xué)習(xí)情境三SQL數(shù)據(jù)查詢

數(shù)據(jù)庫(kù)查詢是數(shù)據(jù)庫(kù)的核心操作。SQL語(yǔ)言提供了SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)的查詢，該語(yǔ)句具有靈活的使用方式和豐富的功能。其一般格式為：SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]]ORDERBY<列名2>[ASC|DESC];

（1）SELECT子句。該子句用于指明查詢結(jié)果集的目標(biāo)列。目標(biāo)列可以是直接從數(shù)據(jù)源中投影得到的字段、與字段相關(guān)的表達(dá)式或數(shù)據(jù)統(tǒng)計(jì)的函數(shù)表達(dá)式，目標(biāo)列還可以是常量。如果目標(biāo)列中使用了兩個(gè)基本表（或視圖）中相同的列名，那么就要在列名前加表名限定，即使用“(表名).(列名)”表示。（2）FROM子句。該子句用于指明查詢的數(shù)據(jù)源。查詢操作需要的數(shù)據(jù)源指基本表（或視圖表）組，表間用“,”分隔。如果查詢使用的基本表或視圖不在當(dāng)前數(shù)據(jù)庫(kù)中，則還需要在表或視圖前加上數(shù)據(jù)庫(kù)名加以說(shuō)明，即使用“(數(shù)據(jù)庫(kù)名).(表名)”的形式表示。如果在查詢中需要一表多用，則每種使用都需要一個(gè)表的別名標(biāo)識(shí)，并在各自使用中用不同的表別名表示。定義表別名的格式為“(表名)(別名)”。（3）WHERE子句。該子句通過(guò)條件表達(dá)式描述關(guān)系中元組的選擇條件。DBMS處理語(yǔ)句時(shí)，以元組為單位，逐個(gè)考察每個(gè)元組是否滿足條件，將不滿足條件的元組篩選掉。（4）GROUPBY子句。該子句的作用是按分組列的值對(duì)結(jié)果集分組。分組可以使同組的元組集中在一起，也使數(shù)據(jù)能夠分組統(tǒng)計(jì)。當(dāng)SELECT子句后的目標(biāo)列中有統(tǒng)計(jì)函數(shù)時(shí)，如果查詢語(yǔ)句中有分組子句，則統(tǒng)計(jì)為分組統(tǒng)計(jì)，否則為對(duì)整個(gè)結(jié)果集的統(tǒng)計(jì)。GROUPBY子句后可以帶上HAVING子句表達(dá)組選擇條件，組選擇條件為帶有函數(shù)的條件表達(dá)式，它決定著整個(gè)組記錄的取舍條件。（5）ORDERBY子句。該子句的作用是對(duì)結(jié)果集進(jìn)行排序。查詢結(jié)果集可以按多個(gè)排序列進(jìn)行排序，每個(gè)排序列后都可以跟一個(gè)排序要求：當(dāng)排序要求為ASC時(shí)，元組按排序列值的升序排序；排序要求為DESC時(shí)，結(jié)果集的元組按排序列值的降序排列。任務(wù)一SQL語(yǔ)句的單表查詢操作

1．無(wú)查詢條件的單表查詢例1查詢student、course和score三個(gè)表的全部?jī)?nèi)容。SELECT*FROMstudent;SELECT*FROMcourse;SELECT*FROMscore;例2查詢所有學(xué)生的學(xué)號(hào)、姓名和籍貫。SELECTsno,sname,shomeFROMstudent;例3查詢選修了課程的學(xué)生的學(xué)號(hào)，并去掉重復(fù)的元組。SELECTDISTINCTsnoFROMscore;

2．比較運(yùn)算的單表查詢例4查詢所有女生的姓名和身高。SELECTsname,sheightFROMstudentWHEREssex='女';例5查詢所有有考試成績(jī)不及格的學(xué)生的學(xué)號(hào)。SELECTDISTINCTsnoFROMscoreWHEREgrade<60;3．確定范圍的單表查詢例6查詢考試成績(jī)?cè)?0～70分之間的學(xué)生的學(xué)號(hào)和課程名稱(chēng)。SELECTsno,cnameFROMscoreWHEREgradeBETWEEN60AND70;4．確定集合的單表查詢例7查詢籍貫是西安、咸陽(yáng)、銅川的學(xué)生的學(xué)號(hào)和姓名。SELECTsno,snameFROMstudentWHEREshomeIN('西安','咸陽(yáng)','銅川');5．字符匹配的單表查詢例8查詢所有姓周的學(xué)生的學(xué)號(hào)和姓名。SELECTsno,snameFROMstudentWHEREsnameLIKE'周*';例9查詢所有姓周且名字為三個(gè)字的學(xué)生的學(xué)號(hào)和姓名。SELECTsno,snameFROMstudentWHEREsnameLIKE'周??';6．涉及空值的單表查詢例10查詢所有有考試成績(jī)的學(xué)生的學(xué)號(hào)、課程名稱(chēng)和成績(jī)。SELECTsno,cname,gradeFROMscoreWHEREgradeISNOTNULL;7．多重條件的單表查詢例11查詢所有選修了數(shù)據(jù)庫(kù)技術(shù)這門(mén)課且考試成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)。SELECTsnoFROMscoreWHEREcname='數(shù)據(jù)庫(kù)技術(shù)'ANDgrade>70;8．對(duì)查詢結(jié)果進(jìn)行排序例12查詢選修了電子商務(wù)這門(mén)課的學(xué)生的學(xué)號(hào)、課程名稱(chēng)及成績(jī)，查詢結(jié)果按分?jǐn)?shù)的降序排列。SELECTsno,cname,gradeFROMscoreWHEREcname='電子商務(wù)'ORDERBYgradeDESC;任務(wù)二SQL語(yǔ)句的復(fù)雜查詢操作

1．連接查詢?nèi)粢粋€(gè)查詢同時(shí)涉及兩個(gè)以上的基本表，則稱(chēng)之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢。為了避免屬性名出現(xiàn)混淆，在連接查詢的查詢表達(dá)式中，屬性名前可加上表名前綴。2．嵌套查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECT-FROM-WHERE語(yǔ)句稱(chēng)為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句HAVING短語(yǔ)的條件中的查詢稱(chēng)為嵌套查詢。上層的查詢塊稱(chēng)為外層查詢或父查詢，下層的查詢塊稱(chēng)為內(nèi)層查詢或子查詢。SQL語(yǔ)言允許多層嵌套，但規(guī)定子查詢的SELECT語(yǔ)句中不能使用ORDERBY子句。3．集合查詢SELECT語(yǔ)句的查詢結(jié)果是元組的集合，所以多個(gè)SELECT語(yǔ)句的結(jié)果可進(jìn)行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。

子學(xué)習(xí)情境四SQL數(shù)據(jù)更新

任務(wù)一插入數(shù)據(jù)1．插入單個(gè)元組插入單個(gè)元組的INSERT語(yǔ)句的格式為：INSERTINTO<表名>[<屬性列1>[,<屬性列2>…]]VALUES(<常量1>[,<常量2>…]);其功能是將新元組插入指定表中。其中新記錄屬性列1的值為常量1，屬性列2的值為常量2，……。INTO子句中沒(méi)有出現(xiàn)的屬性列，新記錄在這些列上將取空值。但必須注意的是，在表定義時(shí)說(shuō)明了NOTNULL的屬性列不能取空值，否則會(huì)出錯(cuò)。如果INTO子句中沒(méi)有指明任何列名，則新插入的記錄必須在每個(gè)屬性列上均有值。2．插入子查詢結(jié)果子查詢不僅可以嵌套在SELECT語(yǔ)句中，用以構(gòu)造父查詢的條件，也可以嵌套在INSERT語(yǔ)句中，用以生成要插入的批量數(shù)據(jù)。插入子查詢結(jié)果的INSERT語(yǔ)句的格式為：INSERTINTO<表名>[<屬性列1>[,<屬性列2>…]]任務(wù)二修改數(shù)據(jù)修改數(shù)據(jù)使用UPDATE語(yǔ)句，可以通過(guò)指定修改條件來(lái)修改表中的一行或多行，也可以使用游標(biāo)進(jìn)行定位修改。修改數(shù)據(jù)語(yǔ)句的一般格式為：UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]…WHERE<條件>;其功能是修改指定表中滿足WHERE子句條件的元組。其中SET子句給出<表達(dá)式>的值，用于取代相應(yīng)的屬性列值。如果省略WHERE子句，則表示要修改表中的所有元組。任務(wù)三刪除數(shù)據(jù)刪除數(shù)據(jù)使用DELETE語(yǔ)句，可以通過(guò)指定選取元組的條件刪除表中的一行或多行，也可以使用游標(biāo)進(jìn)行定位刪除。刪除一行后，該行將無(wú)法再恢復(fù)，同時(shí)也無(wú)法再查看。刪除語(yǔ)句的一般格式為：DELETEFROM<表名>WHERE<條件>;DELETE語(yǔ)句的功能是從指定表中刪除滿足WHERE子句條件的所有元組。如果省略WHERE子句，表示刪除表中的全部元組，但表的定義仍在數(shù)據(jù)字典中。也就是說(shuō)，DELETE語(yǔ)句刪除的是表中的數(shù)據(jù)，而不是關(guān)于表的定義。子學(xué)習(xí)情境五SQL數(shù)據(jù)控制

任務(wù)一授權(quán)SQL語(yǔ)言用GRANT語(yǔ)句向用戶授予操作權(quán)限，GRANT語(yǔ)句的一般格式為：GRANT<權(quán)限>[,<權(quán)限>]…[ON<對(duì)象類(lèi)型><對(duì)象名>]TO<用戶>[,<用戶>]…[WITHGRANTOPTION];其語(yǔ)義為：將對(duì)指定操作對(duì)象的指定操作權(quán)限授予指定的用戶。對(duì)屬性列和視圖的操作權(quán)限有：查詢（SELECT）、插入（INSERT）、修改（UPDATE）、刪除（DELETE）以及這4種權(quán)限的總和（ALLPRIVILEGES）。任務(wù)二收回權(quán)限授予的權(quán)限可以由DBA或其他授權(quán)者用REVOKE語(yǔ)句收回，REVOKE語(yǔ)句的一般格式為：REVOKE<權(quán)限>[,<權(quán)限>]…[ON<對(duì)象類(lèi)型><對(duì)象名>]FROM<用戶>[,<用戶>]…;拓展知識(shí)嵌入式SQL

一、嵌入式SQL的特點(diǎn)SQL的功能只包括數(shù)據(jù)定義功能DDL、數(shù)據(jù)操縱功能DML和數(shù)據(jù)控制語(yǔ)言DCL，而缺少程序設(shè)計(jì)必要的程序流程控制和交互式功能，也缺少一些專(zhuān)業(yè)應(yīng)用的功能，例如SQL沒(méi)有分支、循環(huán)、賦值等語(yǔ)句等。因此，在實(shí)際應(yīng)用中SQL經(jīng)常需要和主語(yǔ)言配合使用才能完成各種復(fù)雜的處理操作。二、不用游標(biāo)的SQL語(yǔ)句嵌入式SQL即使是不需要游標(biāo)的語(yǔ)句，其語(yǔ)句格式和功能特點(diǎn)也與獨(dú)立式SQL不同。下面介紹幾種不使用游標(biāo)的SQL語(yǔ)句及其特點(diǎn)和使用方法。1．幾種不需要使用游標(biāo)的SQL語(yǔ)句下面4種SQL語(yǔ)句不需要使用游標(biāo)：（1）用于說(shuō)明主變量的說(shuō)明性語(yǔ)句。SQL的說(shuō)明性語(yǔ)句主要有兩條：EXECSQLBEGINDECLARESECTION;EXECSQLENDDECLARESECTION;這兩條語(yǔ)句必須配對(duì)出現(xiàn)，兩條語(yǔ)句中間是主變量的說(shuō)明。由于說(shuō)明性語(yǔ)句與數(shù)據(jù)記錄無(wú)關(guān)，所以不需要使用游標(biāo)。（2）數(shù)據(jù)定義和數(shù)據(jù)控制語(yǔ)句。數(shù)據(jù)定義和數(shù)據(jù)控制語(yǔ)句在執(zhí)行時(shí)不需要返回結(jié)果，也不需要使用主變量，因而也就不需要使用游標(biāo)。（3）查詢結(jié)果為單記錄的查詢語(yǔ)句。如果在操作前明確知道查詢結(jié)果為單記錄，主語(yǔ)句可一次將查詢結(jié)果讀完，不需要使用游標(biāo)。（4）數(shù)據(jù)的插入語(yǔ)句和某些數(shù)據(jù)刪除、修改語(yǔ)句。對(duì)于數(shù)據(jù)插入語(yǔ)句，即使插入批量數(shù)據(jù)，也只是在數(shù)據(jù)庫(kù)工作區(qū)內(nèi)部進(jìn)行，不需要主語(yǔ)言介入，故不使用游標(biāo)。2．不用游標(biāo)的查詢語(yǔ)句不用游標(biāo)的查詢語(yǔ)句的一般格式為：EXECSQLSELECT[ALL|DISTINCT]<目標(biāo)表達(dá)式