04.第四章數(shù)據(jù)庫原理與SQL-Server

數(shù)據(jù)庫原理與SQLServer第四課檢索數(shù)據(jù)第四課檢索數(shù)據(jù)

4.1Transact-SQL運(yùn)算符和函數(shù)

4.2SELECT語句

4.3高級SELECT語句

4.4關(guān)系運(yùn)算實(shí)訓(xùn)四檢索數(shù)據(jù)4.1Transact-SQL語言數(shù)據(jù)運(yùn)算

4.1.1Transact-SQL運(yùn)算符(1)算術(shù)運(yùn)算符：+、-、*、/、%注：null與任何值運(yùn)算結(jié)果為null;+、-運(yùn)算可用于datetime型數(shù)據(jù)。(2)字符串運(yùn)算符：+(3)比較運(yùn)算符：=、>、<、>=、<=、<>、!=、!>、!<(4)邏輯運(yùn)算符:not、and、or、between(指定范圍)、

like(模糊匹配)、all(所有)、in(包含于)、any(任意一個(gè))、some(部分)、exists(存在)。

邏輯常量：ture、false。(6)賦值運(yùn)算符：=4.1.2Transact-SQL函數(shù)1、數(shù)學(xué)函數(shù)2、字符串函數(shù)3、日期時(shí)間函數(shù)4、類型轉(zhuǎn)換函數(shù)

1.數(shù)學(xué)函數(shù)sin(n)（正弦）

asin(n)（反正弦）pi()（圓周率）abs(n)（絕對值）exp(n)（指數(shù)）log(n)（自然對數(shù)）power(n,m)(nm)round(n,m)（四舍五入）sign(n)（符號）sqrt(n)（平方根）rand([n])（隨機(jī)數(shù)）

2.字符串函數(shù)str(n,n1,n2)（數(shù)值轉(zhuǎn)換為字符串）len(s)（求串長）left(s,n)（左取子串）right(s,n)（右取子串）substring(s,n1,n2)（取子串）lower(s)（轉(zhuǎn)小寫）upper(s)（轉(zhuǎn)大寫）ltrim(s)（刪除左空格）rtrim(s)（刪除右空格）space(n)（產(chǎn)生空空格）reverse(s)（反轉(zhuǎn)字符串）charindex(s1,s2)（字符串s1在字符串s2中的起始位置）

3.日期時(shí)間函數(shù)getdate()、year(d)、month(d)day(d)datepart(datepart,d)（日期的datepart部分，datepart為日期類型，參見表4-4）dateadd(datepart,n,d)(日期加，即日期d的datepart部分加數(shù)值n后的新日期)datediff(datepart,d1,d2)(日期減，即日期d1與d2的datepart部分相差的值)例4-1計(jì)算香港回歸已經(jīng)有多少年、多少天，今天以后15個(gè)月是哪一天。腳本：

SELECTGETDATE(),DATEDIFF(YEAR,'1997-7-1',GETDATE()), DATEDIFF(DAY,'1997-7-1',GETDATE()),DATEADD(MONTH,15,GETDATE())

4.類型轉(zhuǎn)換函數(shù)convert(data_type,expression[,style])cast(expressionASdata_type)

其中，Style(日期樣式)取值參見表4-5。例4-2將當(dāng)前時(shí)間日期轉(zhuǎn)換為美國格式(mm/dd/yyyy及mm-dd-yyyy)、ANSI(yyyy.mm.dd)，并將當(dāng)前時(shí)間的時(shí)間部分轉(zhuǎn)換為字符串。

腳本：selectgetdate(),convert(char(10),getdate(),101),convert(char(10),getdate(),102),convert(char(10),getdate(),110),convert(char(10),getdate(),114)4.2SELECT語句

在SQL語句中，SELECT語句是最頻繁使用的也是最重要的語句。SELECT<表達(dá)式>[AS<別名>][INTO<目標(biāo)表名>]FROM<源表名>[WHERE<條件>][GROUPBY<列>[HAVING<條件>]][ORDERBY<列>[DESC]]4.2.1操縱列1.計(jì)算表達(dá)式值例4-1、例4-22.輸出列例4-3

檢索所有學(xué)生的所有信息。腳本：select*froms3、設(shè)置列標(biāo)題例4-4

檢索所有學(xué)生的所有信息。腳本：selectsnameasname,'is',

year(getdate())-year(birthday)asagefroms4.1.2操縱行1.普通查詢例4-5

檢索所有1985年12月31日以后以及1982年12月31日以前出生的女生的姓名和出生日期。腳本：

selectsnamebirthdayfromswheresex=‘女'and(year(birthday)>=1986oryear(birthday)<=1982)

2.模糊查詢模糊匹配：like

通配符：%(*)、_(?)、[](指定范圍)。

例4-6

檢索所有姓李以及第二個(gè)字為李的住址在西安的學(xué)生的姓名、性別和住址。

selectsname,sex,addressfromswhere(snamelike'李%'orsnamelike'_李%')andaddresslike'%西安%'注：SQL語言中將一個(gè)漢字視為一個(gè)字符而非2個(gè)字符。4.1.2操縱行4.2.3匯總和排序1.聚合函數(shù)

count([distinct]<字段表達(dá)式>|*)max([distinct]<字段表達(dá)式>)min([distinct]<字段表達(dá)式>)sum([distinct]<字段表達(dá)式>)

avg([distinct]<字段表達(dá)式>)其中，distinct為取消重復(fù)記錄。2.分類

groupby<列>注：groupby子句可以使用表達(dá)式，但不能使用text、image、bit類型數(shù)據(jù)。例4-7

查詢每個(gè)學(xué)生所選課程的數(shù)量、總分及最高、最低分。腳本：

selectsno,count(*)asnum,sum(score),max(score),min(score)fromscgroupbysno3.分類后過濾記錄

having<條件>

例4-8

查詢平均成績及格的學(xué)生所選課程的數(shù)量、總分及最高、最低分。腳本：

selectsno,count(*)asnum,sum(score),max(score),min(score)fromscgroupbysnohavingavg(score)>=60having與where功能相同，不同的是having在groupby后執(zhí)行，可以使用聚合函數(shù)。4.2.4排序orderby<列>[desc]例4-9

檢索每個(gè)學(xué)生所選課程的數(shù)量、總分、平均分及最高、最低分，并按平均分排名次。規(guī)定當(dāng)平均分相等時(shí)，最高分高在前。腳本：

selectsno,count(*)asnum,sum(score),avg(score),max(score),min(score)fromscgroupbysno

orderbyavg(score)desc,max(score)desc4.3高級SELECT語句

一、連接查詢二、子查詢?nèi)?、集合運(yùn)算四、生成新表五、嵌入式Transact-SQL4.3.1連接查詢

所謂多表查詢就是從幾個(gè)表中檢索信息，這種操作通?？梢酝ㄟ^表的連接實(shí)現(xiàn)。實(shí)際上，連接操作是區(qū)別關(guān)系數(shù)據(jù)庫管理系統(tǒng)與非關(guān)系數(shù)據(jù)庫管理系統(tǒng)的最重要的標(biāo)志。

1.無限制連接——笛卡爾積無where子句，一般無實(shí)際意義。

例4-10

求表s與表sc的笛卡兒積。

腳：

select*froms,sc

from后有多個(gè)表，where子句為一F表達(dá)式。注：當(dāng)from后有多個(gè)表時(shí)，where應(yīng)包含這多個(gè)表的關(guān)系(一般是字段的相等關(guān)系)，否則即成為了笛卡爾積。

2.內(nèi)連接——F連接

例4-11

檢索選修了數(shù)據(jù)庫應(yīng)用課程及VB程序設(shè)計(jì)課程的學(xué)生的學(xué)號、姓名、課程名、成績。腳本一：腳本二：3.自連接

連接不僅可以在表之間進(jìn)行，也可以使一個(gè)表同其自身進(jìn)行連接，稱為自連接。

例4-12

檢索所有選修了課程編號為c001及c003的學(xué)生的學(xué)號。腳本：

selectsc1.snofromscassc1,scassc2wheresc1.sno=sc2.snoando='c001'ando='c003'4.3.2子查詢

如果一個(gè)SELECT語句嵌套在WHERE子句中，則稱這個(gè)SELECT語句為子查詢或內(nèi)層查詢，而包含子查詢的SELECT語句稱為主查詢或外查詢。為了區(qū)別主、子查詢，子查詢應(yīng)加小括號。

1.不相關(guān)子查詢所謂不相關(guān)子查詢是指子查詢的查詢條件不依賴于主查詢，此類查詢在執(zhí)行時(shí)首先執(zhí)行子查詢，然后執(zhí)行主查詢。

邏輯運(yùn)算符：in(包含于)、any(某個(gè)值)、some(某些值)、all(所有值)、exists(存在結(jié)果)例4-13檢索選修了數(shù)據(jù)庫應(yīng)用課程的學(xué)生的學(xué)號、姓名、成績。腳本：selectsc.sno,sname,scorefroms,scwheresc.sno=s.snoandcno=(selectcnofromcwherecname='數(shù)據(jù)庫應(yīng)用')例4-14檢索選修了數(shù)據(jù)庫應(yīng)用及VB程序設(shè)計(jì)課程的學(xué)生的學(xué)號、姓名、課程名、成績。腳本：selectsc.sno,sname,cname,scorefroms,c,scwheresc.sno=s.snoando=oandoin(selectcnofromcwherecname='數(shù)據(jù)庫應(yīng)用'orcname='VB程序設(shè)計(jì)')2.相關(guān)子查詢

所謂相關(guān)子查詢是指子查詢的查詢條件依賴于主查詢，此類查詢在執(zhí)行時(shí)首先執(zhí)行主查詢得到第一個(gè)元組，再根據(jù)主查詢第一個(gè)元組的值執(zhí)行子查詢，依此類推直至全部查詢執(zhí)行完畢。

例4-15

檢索平均成績及格的學(xué)生的學(xué)號、姓名。腳本：

selectsno,snamefromswhereexists(selectsnofromscwheres.sno=sc.snogroupbysnohavingavg(score)>=60)4.3.3集合運(yùn)算

使用union運(yùn)算符可以將兩個(gè)或兩個(gè)以上的查詢結(jié)果合并為一個(gè)結(jié)果集。

例4-16

查詢表s和表s_bak中的所有學(xué)生的所有信息（假設(shè)表s_bak已存在，且結(jié)構(gòu)與表s相同）。

腳本：

(select*froms)union(select*froms_bak)4.3.4生成新表1.生成臨時(shí)表

INTO#/##<新表>

臨時(shí)表保存在臨時(shí)數(shù)據(jù)庫Tempdb中，并由SQLServer2000負(fù)責(zé)刪除。

例4-17查詢平均成績超過總平均成績的學(xué)生的學(xué)號、姓名、平均成績。

腳本：

selectsno,avg(score)asavginto#tempfromscgroupbysnohavingavg(score)>=(selectavg(score)fromsc)2.生成永久表INTO<新表>

例4-18創(chuàng)建一個(gè)包含信息501班學(xué)生的學(xué)號、姓名、性別以及出生日期的表。腳本：

selectsno,sname,sex,birthdayintotempfromswhereclass='信息501'4.3.5嵌入式Transact-SQL1.概念在宿主語言中使用的T-SQL語言。2.規(guī)則

T-SQL語句加標(biāo)志以示區(qū)別，如PB中SQL語句應(yīng)加“;”。3.共享變量

T-SQL和主語言中均可使用，在主語言中定義，T-SQL中使用時(shí)加“:”。4.接口

SQL通訊區(qū)(SQLCA)，其中

SQLCode=0操作成功

=-1操作失敗

=100操作成功但無返回?cái)?shù)據(jù)

4.4關(guān)系運(yùn)算

4.4.1關(guān)系模型1.關(guān)系的數(shù)學(xué)定義

定義4.1域(Domain)是一組具有相同數(shù)據(jù)類型的值的集合。定義4.2給定一組域D1、D2、…、Dn，D1、D2、…、Dn的笛卡兒積為D1XD2X…XDn＝{(d1,d2,…,dn)|diDi,i=1,…,n}

其中，每一個(gè)元素(d1,d2,…,dn)稱為一個(gè)元組（簡稱元組），元素中每一個(gè)值di稱為一個(gè)分量。例4-19

給定三個(gè)域：姓名＝{張三,李四}、性別＝{男,女}、課程＝{數(shù)據(jù)庫,軟工}則域上的笛卡兒積為：姓名X性別X課程＝{(張三,男,數(shù)據(jù)庫),(張三,男,軟工),(張三,女,數(shù)據(jù)庫),(張三,女,軟工),(李四,男,數(shù)據(jù)庫),(李四,男,軟工),(李四,女,數(shù)據(jù)庫),(李四,女,軟工)}定義4.3域D1、D2、…、Dn上的笛卡兒積的子集稱為在域D1、D2、…、Dn上的關(guān)系，用R(D1,D2,…,Dn)表示。其中，R表示關(guān)系名，n為關(guān)系的度或目或元數(shù)。例4-20給定三個(gè)域：姓名＝{張三,李四}、性別＝{男,女}、課程＝{數(shù)據(jù)庫,軟工}

則域上的教師授課關(guān)系為：教師授課(姓名,性別,課程)={(張三,男,數(shù)據(jù)庫),(李四,女,軟工)}，其中n＝3。

2.關(guān)系的性質(zhì)

(1)屬性取自同一個(gè)域。

(2)屬性是原子的，且屬性名不能相同。

(3)沒有重復(fù)的元組。

(4)沒有行序。

(5)理論上沒有列序，為方便可以有列序。4.4.2關(guān)系代數(shù)

1.傳統(tǒng)的集合運(yùn)算(1)并（Union）:R∪S＝{t|tR∨tS}(2)交（Intersection）:R∩S＝{t|tR∧tS}(3)差（Difference）:R－S＝{t|tR∧tS}(4)笛卡兒積（CartesianProduct）:

RXS＝{t|t=(tm,tn)∧tmR∧tnS}

例4-21設(shè)關(guān)系R和關(guān)系S具有相同的關(guān)系模式，分別求出關(guān)系R和S的并、交和差。例4-22由學(xué)生關(guān)系S和課程關(guān)系C，求出其廣義笛卡兒積。2.專門的關(guān)系運(yùn)算

(1)選擇（Selection）:σF(R)＝{t|tR∧F(t)=true}(2)投影（Projection）:ΠA(R)＝{t[A]|tR}(3)連接（Join）:將兩個(gè)關(guān)系連在一起，形成一個(gè)新的關(guān)系。

包括：θ連接、等值連接、F連接、自然連接。(4)除（Division）

:R(X,Y)÷S(Y,Z)=ΠX(R)—ΠX((ΠX(R)XΠY(S))－R)

或

R(X,Y)÷S(Y,Z)={tr[X]|trR∧∏Y(S)Yx}例4-23由學(xué)生關(guān)系S，查詢所有女生的信息。關(guān)系代數(shù)表達(dá)式：σSEX='女'(S)或σ3='女'(S)例4-24由學(xué)生關(guān)系S，查詢所有學(xué)生的姓名、性別信息。關(guān)系代數(shù)表達(dá)式：ΠSNAME,SEX(S)或Π2,3(S)例4-25由學(xué)生關(guān)系S、課程關(guān)系C和選課關(guān)系SC，求、和。

例4-26由選課關(guān)系SC、課程關(guān)系C，求SC÷C。方法一：按公式R(X,Y)÷S(Y,Z)=ΠX(R)—ΠX(ΠX(R)XΠY(S))－R)計(jì)算SC(X,Y)÷C(Y)，即X=(SNO)，Y=(CNO)，Z=(CNAME,CREDIT)。①計(jì)算T1＝ΠSNO(SC)②計(jì)算T2＝T1XΠCNO(C)③計(jì)算T3＝T2－SC④計(jì)算T4＝ΠSNO(T3)⑤計(jì)算SC÷C＝T1－T4

方法二：按公式R(X,Y)÷S(Y,Z)={tr[X]|trR∧∏Y(S)Yx}。①

求R中X的各分量的象集Yx。

SC中，X＝(SNO)，其值有：{(1001),(1002),(1003),(2001),(2002)}，其象集為：

Y(1001)={(c001),(c003)}Y(1002)={(c001)}Y(1003)={(c001)}Y(2001)={(c001),(c003)}Y(2002)={(c003)}②

求出S在Y上的投影ΠY(S)。

ΠCNO(C)＝{(c001),(c003)}③

選取∏Y(S)Yx的分量x。顯然Y(1001)和Y(2001)包含{(c001),(c003)}所以SC÷C＝{(1001),(2001)}3.關(guān)系代數(shù)表達(dá)式在關(guān)系代數(shù)中介紹了9種關(guān)系代數(shù)運(yùn)算，其中并、差、笛卡兒積、選擇和投影是基本運(yùn)算，交、連接、自然連接、除可以用這5種基本運(yùn)算經(jīng)過有限次復(fù)合來表達(dá)。由關(guān)系運(yùn)算有限次復(fù)合組成的式子稱為關(guān)系代數(shù)表達(dá)式。關(guān)系代數(shù)表達(dá)式的運(yùn)算結(jié)果仍為關(guān)系。實(shí)際上，可以用關(guān)系代數(shù)表達(dá)式表示各種數(shù)據(jù)查詢操作。例4-27

對“學(xué)生選課”關(guān)系模型：

S(SNO,SNAME,SEX)C(CNO,CNAME,CREDIT)SC(SNO,CNO,SCORE)用關(guān)系代數(shù)表達(dá)式表示以下查詢并寫出相應(yīng)T-SQL語句。

(1)選修了課程編號為c001的課程的學(xué)生的學(xué)號。關(guān)系代數(shù)表達(dá)式：

∏SNO(σCNO='c001'(SC))T-SQL語句：

selectsnofromscwherecno='c001‘(2)沒有選修編號c01的課程的學(xué)生的學(xué)號。關(guān)系代數(shù)表達(dá)式：

∏SNO(R)－∏SNO(σCNO='c001'(SC))

注意不能寫為：∏SNO(σCNO<>'c001'(SC))T-SQL語句：