第3章-數(shù)據(jù)庫原理(新)1

第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL林穎賢第三章

關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3數(shù)據(jù)更新3.4查詢3.5視圖3.6存儲過程3.7觸發(fā)器3.8小結(jié)3.1SQL概述SQL：（StructuredQueryLanguage結(jié)構(gòu)化查詢化語言），不僅僅具有查詢功能。它是一個通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫操作語言。SQL的特點(diǎn)面向集合操作、高度非過程化集數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)控制功能于一體統(tǒng)一語法結(jié)構(gòu)的兩種使用方式，簡單易學(xué)

SQL功能主要包括三個方面:數(shù)據(jù)操縱數(shù)據(jù)定義數(shù)據(jù)控制SQL對關(guān)系數(shù)據(jù)庫模式的支持SQL視圖1基本表2存儲文件1存儲文件2基本表1基本表3基本表4視圖2外模式模式內(nèi)模式SQL語言支持關(guān)系數(shù)據(jù)庫三級模式結(jié)構(gòu)圖

SQL語言支持關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)，其中視圖對應(yīng)外模式，基本表對應(yīng)模式，存儲文件對應(yīng)內(nèi)模式。3.2數(shù)據(jù)定義SQL的數(shù)據(jù)定義功能:模式定義、表定義、視圖和索引的定義定義模式格式:CREATESCHEMA<模式名>AUTHORIZATION<用戶名>>[<表定義子句>|<視圖定義子句>|<授權(quán)定義子句>]

如果沒有指定<模式名>，那么<模式名>隱含為<用戶名>定義模式實(shí)際上定義了一個命名空間在這個空間中可以定義該模式包含的數(shù)據(jù)庫對象，例如基本表、視圖、索引等。在CREATESCHEMA中可以接受CREATETABLE，CREATEVIEW和GRANT子句。定義模式[例]CREATESCHEMA

AUTHORIZATIONlyx1CREATETABLElyx1.TAB1(COL1SMALLINT，

COL2INT，

COL3CHAR(20)，

COL4NUMERIC(10，3)，

COL5DECIMAL(5，2))；

為用戶lyx1創(chuàng)建了一個模式lyx，并在其中定義了一個表TAB1。(注意:必須先建立一個用戶lyx二、刪除模式DROPSCHEMA<模式名><CASCADE|RESTRICT>CASCADE(級聯(lián))

刪除模式的同時(shí)把該模式中所有的數(shù)據(jù)庫對象全部撤消RESTRICT(限制)

如果該模式中定義了下屬的數(shù)據(jù)庫對象（如表、視圖等），則拒絕該刪除語句的執(zhí)行。當(dāng)該模式中沒有任何下屬的對象時(shí)才能執(zhí)行。3.2.1定義語句格式定義基本表（CREATE）createtable

表名（列名數(shù)據(jù)類型[default缺省值][notnull][，列名數(shù)據(jù)類型[default缺省值][notnull]]……[，primarykey（列名[，列名]…）][，foreignkey

（列名[，列名]…）

references

表名（列名[，列名]…）] [，check（條件）]）;createtableStudent(

Stu_nochar(8),

Stu_namevarchar(10)notnull,

Stu_age

tinyint,

Stu_sexchar(2),

Stu_deptvarchar(20),

CONSTRAINTpk_snoPRIMARYKEY(Stu_no))createtableCourse(

Cou_nochar(3),

Cou_namevarchar(16)notnull,

Cou_pnochar(3),

Cou_teachervarchar(10),

CONSTRAINTpk_cnoPRIMARYKEY(Cou_no))createtableSC(

Stu_nochar(8),

Cou_nochar(3),Gradedecimal(5,1),CONSTRAINTpk_scPRIMARYKEY(Stu_no,Cou_no),

foreignkey(Stu_no)referencesStudent(Stu_no),foreignkey(Cou_no)referencesCourse(Cou_no),

check((gradeisnull)or(gradebetween0and100)))三、刪除基本表

DROPTABLE<表名>

基本表刪除數(shù)據(jù)、表上的索引都刪除表上的視圖往往仍然保留，但無法引用.刪除基本表時(shí)，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述[例]刪除Student表

DROPTABLE

Student2、修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][ALTERCOLUMN<列名><數(shù)據(jù)類型>]<[例2]向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADD

Stu_dateDATETIME不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。

[例1]將年齡的數(shù)據(jù)類型改為半字長整數(shù)。

ALTERTABLEStudentALTERCOLUMN

stu_ageSMALLINT注：修改原有的列定義有可能會破壞已數(shù)據(jù)

數(shù)據(jù)更新

1插入數(shù)據(jù)2修改數(shù)據(jù)3刪除數(shù)據(jù)[例1]將一個新學(xué)生記錄（）插入到Student表中。[例2]對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入另一張表Dept_age。INSERTintoStudentVALUES(‘20028103’,‘張燕紅',18,'女','信息');第一步：建表

CREATETABLEDept_age(SdeptCHAR(15),

AvgageSMALLINT)第二步：插入數(shù)據(jù)

INSERTINTODept_age(Stu_dept，Avgage)SELECTStu_dept，AVG(Stu_age)FROMStudentGROUPBYStu_dept2.修改數(shù)據(jù)語句表中的數(shù)據(jù)通常需要改變，可以使用UPDATE語句修改表中的數(shù)據(jù)。格式：UPDATEtablenameSET column=value[,column=value][WHERE condition]update語句包括三個子句：update語句指定要更新的表，set子句指定所有要改變數(shù)值的列和指定新值，where子句確定哪些行要被修改。如果省略where子句，則要對表中的每一行進(jìn)行修改。【例3】將student表中學(xué)號為20026101的學(xué)生年齡修改為18。

【例4】將所有選修課程號為a01的成績加2分，所有選修課程號為a03的成績加3分。其余加1分。updatestudentsetstu_age=18wherestu_no='20026101';updatescsetgrade=casecou_nowhen'a01'thengrade+2when'a03'thengrade+3elsegrade+1end;思考題:請用SQL語句完成顯示如下結(jié)果:selectstu_no

學(xué)號,cou_no

課程號,grade成績,等級=casewhengrade>=90then'優(yōu)秀'whengrade>=80then'良好'whengrade>=70then'中等'whengrade>=60then'及格'whengradeisnullthen'缺考'else'不及格'endfromsc

3.刪除數(shù)據(jù)使用delete語句可從表中刪除現(xiàn)有的數(shù)據(jù)。但不會釋放表所占用的空間。如果用戶確定要刪除表的所有數(shù)據(jù)，那么使用”TRUNCATETABLE”語句速度更快。delete語句的操作可以回退，但TRUNCATETABLE語句的操作不能回退。格式：DELETEFROM<表名|VIEW>[WHERE條件];如果不用where子句，則會刪除表中所有的行，使用where子句只刪除滿足條件的行。1.刪除選修課程號為a01學(xué)生的成績記錄

deletefromscwherecou_no='a01';2.刪除成績表中所有的記錄并釋放表所占用的空間

truncatetableSC;begintrandeletefromscwherecou_no='a01';rollback3.2.2建立與刪除索引建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動建立以下列上的索引

PRIMARYKEYUNIQUE維護(hù)索引

DBMS自動完成

使用索引

DBMS自動選擇是否使用索引以及使用哪些索引一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)

用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引

[例6]為學(xué)生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學(xué)號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(stu_no)CREATEUNIQUEINDEXCoucnoONCourse(Cou_no)CREATEUNIQUEINDEXSCnoONSC(stu_noASC，Cou_noDESC)唯一值索引:

對于已含重復(fù)值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時(shí)DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束什么是聚簇?

為了提高某個屬性（或?qū)傩越M）的查詢速度，把這個或這些屬性（稱為聚簇碼）上具有相同值的元組集中存放在連續(xù)的物理塊稱為聚簇.聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中元組的物理順序一致。在一個基本表上最多只能建立一個聚簇索引聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率聚簇索引的適用范圍:

很少對基表進(jìn)行增刪操作很少對其中的變長列進(jìn)行修改操作聚簇的用途:

1.大大提高按聚簇屬性進(jìn)行查詢的效率例：假設(shè)學(xué)生關(guān)系按所在系建有索引，現(xiàn)在要查詢信息系的所有學(xué)生名單。信息系的500名學(xué)生分布在500個不同的物理塊上時(shí)，至少要執(zhí)行500次I/O操作。如果將同一系的學(xué)生元組集中存放，則每讀一個物理塊可得到多個滿足查詢條件的元組，從而顯著地減少了訪問磁盤的次數(shù)。2.節(jié)省存儲空間聚簇以后，聚簇碼相同的元組集中在一起了，因而聚簇碼值不必在每個元組中重復(fù)存儲，只要在一組中存一次就行了注意：一個基表不能建太多的索引；空值不能被索引只有唯一索引才真正提高速度,一般的索引只能提高30%左右。提高查詢速度的方法還有在表上建立主鍵，主鍵與唯一索引的差別在于唯一索引可以空，主鍵為非空對于已含重復(fù)值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時(shí)DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束。排序的字段如果通過索引去訪問那將大大提高排序速度。對一些經(jīng)常處理的業(yè)務(wù)表應(yīng)在查詢允許的情況下盡量減少索引。數(shù)據(jù)重復(fù)且分布平均的表字段盡量減少索引。假如一個表有10萬行記錄，有一個字段A只有T和F兩種值，且每個值的分布概率大約為50%，那么對這種表A字段建索引一般不會提高數(shù)據(jù)庫的查詢速度。1.想知道在Student表上建的索引是個聚集索引還是一個非聚集索引，可用sp_helpstudent二、刪除索引DROPINDEX<索引名>刪除索引時(shí)，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例]刪除Student表的Stusno索引。

DROPINDEXstudent.Stusno3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結(jié)語句格式:

SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]][COMPUTERBY]1.單表查詢查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結(jié)果排序四、使用集函數(shù)五、對查詢結(jié)果分組

SelectingAllColumns

DEPTNODNAMELOC------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONSELECT*FROM dept;

UsingArithmeticOperators

SELECTename,sal,sal+300FROM emp;ENAMESALSAL+300----------------------------KING50005300BLAKE28503150CLARK24502750JONES29753275MARTIN12501550ALLEN16001900...14rowsselected.

OperatorPrecedence

SELECTename,sal,12*sal+100FROMemp;ENAMESAL12*SAL+100-----------------------------KING500060100BLAKE285034300CLARK245029500JONES297535800MARTIN125015100ALLEN160019300...14rowsselected..查詢經(jīng)過計(jì)算的值

SELECT子句的<目標(biāo)列表達(dá)式>為表達(dá)式算術(shù)表達(dá)式字符串常量函數(shù)列別名

[例5]查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTstu_name，getdate()-stu_age，LOWER(stu_dept)FROMStudent；[例6]查詢選修了課程的學(xué)生學(xué)號（消除取值重復(fù)的行）SELECTDISTINCT

stu_noFROMSC;[例7]使用列別名改變查詢結(jié)果的列標(biāo)題SELECT

stu_no

as

學(xué)號，stu_nameas

姓名FROMStudent;[例8]限制結(jié)果集返回行數(shù)SELECTTOP5stu_no

as

學(xué)號，stu_nameas

姓名FROMStudent;錯datepart(yy,getdate())-stu_agea)數(shù)學(xué)函數(shù):ABS(X),SQRT(X),EXP(X),RAND([SEED]),LOG(X)selectsqrt(144)b)字符串函數(shù)：+，ASCII(ch),CHAR(x),SPACE(n),UPPER(ch),LOWER(ch),LEFT(n),LEN(n),REPLACE(exp1,exp2,exp3)-用第3個表達(dá)式替代第一個表達(dá)式中出現(xiàn)的所有第二給定的表達(dá)式。

c)日期和時(shí)間函數(shù):

GETDATE()--可以獲得當(dāng)前的日期和時(shí)間

CONVERT(）-得到不同格式的日期和時(shí)間DATEPART()-得到日期和時(shí)間的一部分DATEDIFF（）-返回兩個日期所差的天數(shù)或月數(shù)或年數(shù)。DATEADD()-在向給定日期加上一段時(shí)間的基礎(chǔ)上，返回新的日期值。d)聚合函數(shù)：AVG(),MAX(),MIN(),SUM(),COUNT()[例9]用“CS”替換學(xué)生表中專業(yè)字段中的“計(jì)算機(jī)”。

UPDATESTUDENTSETstu_dept=replace(stu_dept,,’計(jì)算機(jī)‘,’CS’)函數(shù)GETDATE（）可以用來作為DATEDIME型字段的缺省值。這對插入記錄時(shí)保存當(dāng)時(shí)的時(shí)間是有用的。[例10]，假設(shè)有一個表用來保存站點(diǎn)上的活動日志。每當(dāng)有一個訪問者訪問到的站點(diǎn)時(shí)，就在表中添加一條新記錄，記下訪問者的名字，活動，和進(jìn)行訪問的時(shí)間。要建立一個表，其中的記錄包含有當(dāng)前的日期和時(shí)間，可以添加一個DATETIME型字段，指定其缺省值為函數(shù)GETDATE（）的返回值，CREATETABLEsite_log(usernameVARCHAR(40),

useractivityVARCHAR(100),

entrydate

DATETIMEDEFAULTGETDATE())要得到不同格式的日期和時(shí)間，使用函數(shù)CONVERT（）。例如，當(dāng)下面的這個語句執(zhí)行時(shí)，顯示的時(shí)間將包括毫秒：SELECTCONVERT(VARCHAR(30),GETDATE(),9)注意例子中數(shù)字9的使用。這個數(shù)字指明了在顯示日期和時(shí)間時(shí)使用哪種日期和時(shí)間格式。當(dāng)這個語句執(zhí)行時(shí)，將顯示如下的日期和時(shí)間：101720046:54:24:397PM(1row(s)affected)表1日期和時(shí)間的類型類型值標(biāo)準(zhǔn)輸出

0Defaultmon

dd

yyyy

hh:miAM1USAmm/dd/yy2ANSIyy.mm.dd

9Default+millisecondsmon

dd

yyyy

hh:mi:ss:mmmAM10USAmm-dd-yy11JAPANyy/mm/dd

13Europeyyyyhh:mi:ss:mmm(24h)只想得到日期和時(shí)間的一部分，而不是完整的日期和時(shí)間。例如，假設(shè)想列出的站點(diǎn)目錄中每個站點(diǎn)被查詢的月份?？梢允褂煤瘮?shù)DATEPART（）：

SELECTsite_nameas‘站點(diǎn)名’,DATEPART(mm,site_entrydate)as‘月份’

FROMsite_directory函數(shù)DATEPART（）的參數(shù)是兩個變量。第一個變量指定要抽取日期的哪一部分；第二個變量是實(shí)際的數(shù)據(jù)。在這個例子中，函數(shù)DATEPART（）抽取月份，因?yàn)閙m代表月份。函數(shù)DATEPART（）的返回值是一個整數(shù)?？梢杂眠@個函數(shù)抽取日期的各個不同部分，如表2所示。要以更易讀的格式得到部分的日期和時(shí)間，可以使用函數(shù)DATENAME（），函數(shù)DATENAME（）和函數(shù)DATEPART（）接收同樣的參數(shù)。但是，它的返回值是一個字符串，而不是一個整數(shù)。表2日期的各部分及其簡寫日期部分簡寫值yearyy1753--9999quarterqq1--4monthmm1--12dayofyeardy1--366daydd1--31weekwk1--53weekdaydw1--7(Sunday--Saturday)hourhh0--23minutemi0--59secondss0--59milisecondms0--999條件查詢常用的查詢條件不小于不大于[例11]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。[例12]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。[例13]查詢信息系和計(jì)算機(jī)系學(xué)生的姓名和性別。[例14]查詢既不是信息系，也不是計(jì)算機(jī)系的學(xué)生的姓名和性別。

1.SELECTStu_name，Stu_age

FROMStudentWHEREStu_age<20

或SELECTStu_name，Stu_age

FROMStudentWHERENOTStu_age>=203.SELECTStu_name，Stu_sexFROMStudentWHEREStu_dept

IN(‘計(jì)算機(jī)’，‘信息')4.SELECTStu_name，Stu_sexFROMStudentWHEREStu_dept

NOTIN(‘計(jì)算機(jī)’，‘信息')SELECTStu_name,Stu_dept,Stu_ageFROMStudentWHEREStu_ageBETWEEN20AND23字符串匹配

[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]通配符%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。_(下橫線)代表任意單個字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。[]指定范圍內(nèi)的任意單個字符。例：LIKE‘[ck]%’[^]不在指定范圍內(nèi)的任意單個字符。當(dāng)用戶要查詢的字符串本身就含有通配符時(shí)，要使用ESCAPE'<換碼字符>'短語對通配符進(jìn)行轉(zhuǎn)義。<匹配串>：指定匹配模板,匹配模板：固定字符串或含通配符的字符串當(dāng)匹配模板為固定字符串時(shí)，可以用=運(yùn)算符取代LIKE謂詞,用!=或<>運(yùn)算符取代NOTLIKE謂詞[例15]查詢學(xué)號為20026101的學(xué)生的詳細(xì)情況[例16]查詢所有姓劉學(xué)生的姓名、學(xué)號和性別[例17]查詢姓“王”且全名為二個漢字的學(xué)生的姓名。[例18]查詢名字中第2個字為“明”字的學(xué)生的姓名和學(xué)號[例19]查詢所有不姓劉的學(xué)生姓名。[例20]查詢DB_Design課程的課程號。

SELECT*FROMStudentWHEREStu_no

LIKE‘20026101'SELECTStu_name，Stu_no，Stu_sexFROMStudentWHEREStu_name

LIKE‘劉%’SELECTStu_nameFROMStudentWHEREStu_name

LIKE‘王__'SELECTStu_name，Stu_noFROMStudentWHEREStu_name

LIKE‘_明%'SELECTStu_name，Stu_no，Stu_sexFROMStudentWHEREStu_name

NOTLIKE'劉%'SELECTCou_noFROMCourseWHERECou_name

LIKE'DB\_Design'ESCAPE‘\'

涉及空值的查詢使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。

SELECTstu_no，noFROMSCWHEREgradeISNULL對查詢結(jié)果排序 使用ORDERBY子句:可以按一個或多個屬性列排序.升序：ASC；降序：DESC；缺省值為升序[例22]查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。

SELECT*FROMStudent

ORDERBYstu_dept，stu_ageDESC作業(yè)：1將學(xué)生表按專業(yè)名排序，并匯總各專業(yè)人數(shù)和平均年齡。COMPUTEBY子句使您得以用同一SELECT語句既查看明細(xì)行，又查看匯總行?？梢杂?jì)算子組的匯總值，也可以計(jì)算整個結(jié)果集的匯總值。COMPUTE子句需要下列信息：可選的BY關(guān)鍵字，該關(guān)鍵字可按對一列計(jì)算指定的行聚合。行聚合函數(shù)名稱；例如，SUM、AVG、MIN、MAX或COUNT。

select*fromstudentorderbystu_deptcomputeavg(stu_age),count(stu_dept),作業(yè)1:將學(xué)生表按專業(yè)名排序，并匯總各專業(yè)人數(shù)和平均年齡。select*fromstudentorderbystu_deptcomputeavg(stu_age),count(stu_dept)bystu_dept對查詢結(jié)果分組[例23]查詢各個課程號與相應(yīng)的選課人數(shù)SELECTCno,COUNT(Sno)AS人數(shù)FROMSCGROUPBYCno;

sc

3

5

6

cno

sno------------------1980119902198032981229823298052980629807398213982239834398563986739834

CNO人數(shù)

----------------

132536作業(yè)2:在學(xué)生表上產(chǎn)生一個結(jié)果集，包括每個專業(yè)的男生人數(shù)，女生人數(shù)，總?cè)藬?shù)及學(xué)生人數(shù)。GROUPBY[字段名][WITH{CUBE|ROLLUP}]用ROLLUP匯總數(shù)據(jù)CUBE生成的結(jié)果集顯示了所選列中值的所有組合的聚合。

ROLLUP生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。

selects.stu_deptas專業(yè)名,s.stu_sexas性別,count(*)as人數(shù)

fromstudentsgroupbys.stu_dept,s.stu_sexwithrollup作業(yè)2:在學(xué)生表上產(chǎn)生一個結(jié)果集，包括每個專業(yè)的男生人數(shù)，女生人數(shù)，總?cè)藬?shù)及學(xué)生人數(shù)。在學(xué)生表上產(chǎn)生一個結(jié)果集，包括每個專業(yè)的男生人數(shù)，女生人數(shù)，總?cè)藬?shù)及男生總數(shù)、女生總數(shù)、學(xué)生總數(shù)。

selects.stu_deptas專業(yè)名,s.stu_sexas性別,count(*)as人數(shù)

fromstudentsgroupbys.stu_dept,s.stu_sexwithcube使用HAVING短語篩選最終輸出結(jié)果

[例24]查詢選修了3門以上課程的學(xué)生學(xué)號。

SELECTSnoFROMscGROUPBYSnoHAVINGcount(*)>3;

Sno------------------98019805[例25]

查找計(jì)算機(jī)專業(yè)平均成績在85分以上的學(xué)生的學(xué)號和平均成績。selectstu_no

學(xué)號,avg(grade)平均成績

fromscwherestu_noIN(selectstu_nofromstudentwherestu_dept='計(jì)算機(jī)')groupbystu_nohavingavg(grade)>85;3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢

連接查詢比較連接查詢自身連接復(fù)合條件連接連接查詢中用來連接兩個表的條件稱為連接條件或連接謂詞，其一般格式為：

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

其中比較運(yùn)算符主要有：=、>、<、>=、<=、!=。當(dāng)連接運(yùn)算為=時(shí)，稱為等值連接。使用其他運(yùn)算符稱為非等值連接。

連接謂詞中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的，但不必是相同的。比較連接查詢[例26]

SELECTStudent.*,Sc.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;SC表Student表查詢結(jié)果自身連接[例27]查詢每一門課的間接先修課（即先修課的先修課）課程”表即Course關(guān)系中，只有每門課的直接先修課信息，而沒有先修課的先修課，要得到這個信息，必須先對一門課找到其先修課，再按此先修課的課程號，查找它的先修課程，這相當(dāng)于將Course表與其自身連接后，取第一個副本的課程號與第二個副本的先修課號做為目標(biāo)列中的屬性T1表（Course表）T2表（Course表）結(jié)果為：SELECTT1.Cno,T2.CpnoFROMCourseT1,CourseT2WHERET1.Cpno=T2.Cno復(fù)合條件連接例29：查詢選修a01號課程且成績在90分以上的所有學(xué)生的學(xué)號及姓名。例30：查詢所有學(xué)生的學(xué)號、姓名及所學(xué)的課程名及成績。SELECTS.Stu_no

學(xué)號,S.Stu_name

姓名

FROMStudentS,SCWHERES.Stu_no=SC.Stu_no

ANDSC.Cou_no='a01'ANDSC.Grade>90selects.stu_no,s.stu_name,c.cou_name,sc.gradefromstudents,course

c,scwheres.stu_no=sc.stu_noandc.cou_no=sc.cou_no嵌套查詢在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。例如：SELECTSname(外層查詢/父查詢)FROMStudentWHERESno

IN

（SELECTSno(內(nèi)層查詢/子查詢) FROMSC WHERECno=‘2’）不相關(guān)子查詢:子查詢的查詢條件不依賴于父查詢相關(guān)子查詢:子查詢的查詢條件依賴于父查詢不相關(guān)子查詢是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。[例]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名

SELECTSno，Sname

③最后在Student關(guān)系中

FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno

②然后在SC關(guān)系中找出選

FROMSC修了3號課程的學(xué)生學(xué)號

WHERECnoIN(SELECTCno

①首先在Course關(guān)系中找出“信

FROMCourse息系統(tǒng)”的課程號，結(jié)果為3號

WHERECname=‘信息系統(tǒng)’));帶有ANY或ALL謂詞的子查詢謂詞語義ANY：任意一個值A(chǔ)LL：所有值>ANY 大于子查詢結(jié)果中的某個值>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個值>ALL 小于子查詢結(jié)果中的所有值[例31]查詢其他系中比計(jì)算機(jī)系任意一個(其中某一個)學(xué)生年齡小的學(xué)生姓名和年齡

SELECTStu_name,Stu_ageFROMStudent

WHEREStu_age<ANY(SELECTStu_ageFROMStudentWHEREStu_dept='計(jì)算機(jī)')ANDstu_dept<>'計(jì)算機(jī)'/*注意這是父查詢塊中的條件*/結(jié)果

Sname Sage

王敏18執(zhí)行過程1.DBMS執(zhí)行此查詢時(shí)，首先處理查詢，找出IS系中所有學(xué)生的年齡，構(gòu)成一個集合(19，18).2.處理父查詢，找所有不是IS系且年齡小于19或18的學(xué)生.WHEREStu_age<(SELECTMAX(Stu_age)FROMStudentWHEREStu_dept='計(jì)算機(jī)')作業(yè)：1.檢索平均成績最高的學(xué)生學(xué)號。外連接（OuterJoin）外連接與普通連接的區(qū)別：

普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出。左外連接(LEFTJOIN)右外連接(RIGHTJOIN)完全外連接(FULLJOIN)SELECTStudent.*,SC.*FROMStudentLEFTJOINSCONStudent.stu_no=SC.stu_no[例32]查詢每個學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生.帶有EXISTS謂詞的子查詢1.EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無實(shí)際意義2.NOTEXISTS謂詞[例33]查詢沒有選修a01號課程的學(xué)生姓名。

SELECTStu_nameFROMStudentWHERENOTEXISTS(SELECT*FROMSC

WHERE

Stu_no=Student.Stu_no

AND

Cou_no='a01')相關(guān)子查詢首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個元組；重復(fù)這一過程，直至外層表全部檢查完為止。5.用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn))SQL語言中沒有全稱量詞

（Forall）,可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：(

x)P≡

(

x(

P))[例34]查詢選修了全部課程的學(xué)生姓名。(即不存在這樣的課程，這門課該學(xué)生沒有學(xué)過。(書P111)

7)πname(S|×|(πSNO,CNO(SC)÷πcno(C)))SELECTStu_nameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHEREStu_no=Student.Stu_noANDCou_no=S1.Cou_no))

[例35]查詢至少選修了學(xué)生20028101選修的全部課程的學(xué)生號碼。解題思路：用邏輯蘊(yùn)函表達(dá)：查詢學(xué)號為20028101的學(xué)生，對所有的課程y，只要20028101學(xué)生選修了課程y，則x也選修了y。形式化表示：用P表示謂詞“學(xué)生20028101選修了課程y”用q表示謂詞“學(xué)生x選修了課程y”

則上述查詢?yōu)?(

y)p

q等價(jià)變換：(

x)P≡

(

x(

P))

（y)p

q≡

(

y(

(p

q))≡

(

y(

(

p∨q)≡

y(p∧

q)變換后語義：不存在這樣的課程y，學(xué)生20028101選修了y，而學(xué)生x沒有選。用NOTEXISTS謂詞表示：

SELECTDISTINCTStu_no

學(xué)號

FROMSCXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.Stu_no='20028101'ANDNOTEXISTS(SELECT*FROMSCZWHEREZ.Stu_no=X.Stu_noANDZ.Cou_no=Y.Cou_no))andStu_no!='20028101'視圖視圖作用建立視圖刪除視圖查詢視圖更新視圖視圖的作用：視圖是原始數(shù)據(jù)庫數(shù)據(jù)的一種變換，是查看表中數(shù)據(jù)的另外一種方式。可以將視圖看成是一個移動的窗口，通過它可以看到感興趣的數(shù)據(jù)。視圖是從一個或多個基本表中獲得的，這些表的數(shù)據(jù)存放在數(shù)據(jù)庫中。那些用于產(chǎn)生視圖的表叫做該視圖的基表。一個視圖也可從另一個視圖產(chǎn)生。視圖的定義存在數(shù)據(jù)庫中，與此定義相關(guān)的數(shù)據(jù)并沒有再存一份于數(shù)據(jù)庫中。通過視圖看到的數(shù)據(jù)存放在基表中。視圖看上去非常象數(shù)據(jù)庫的物理表，對它的操作同任何其它的表一樣。當(dāng)通過視圖修改數(shù)據(jù)時(shí)，實(shí)際上是在改變基表中的數(shù)據(jù)；相反，基表數(shù)據(jù)的改變也會自動反映在由基表產(chǎn)生的視圖中。由邏輯上的原因，有些視圖可以修改對應(yīng)的基表，有些則不能（僅查詢）。建立視圖一、視圖的定義

格式：CREATEVIEW<視圖名>[<列名1>[,…]AS<子查詢>[WITHCHECKOPTION]不允許使用含有：SELECTINTO、COMPUTE或COMPUTERBY子句、ORDERBY子句在視圖增刪除時(shí)，檢查視圖定義條件，若不滿足，則拒絕執(zhí)行。1.對用戶經(jīng)常要用到的學(xué)號、姓名、課程名、成績等數(shù)據(jù)建立視圖。createviewstu_viewasselects.stu_no,s.stu_name,c.cou_name,sc.gradefromstudents,sc,coursecwheres.stu_no=sc.stu_noandsc.cou_no=c.cou_no2.用戶經(jīng)常要查詢每個學(xué)生選修課（成績非空）的門數(shù)和平均分，可以定義視圖createviewgrade_view(s