第2章 第5節(jié) 關(guān)系數(shù)據(jù)庫及SQL語言

2.3關(guān)系數(shù)據(jù)庫及SQL語言12.3.1

SQL概述2.3.2

SQL的特點(diǎn)2.3.3

SQL語言的應(yīng)用

2.3.1SQL概述SQL(StructuredQueryLanguage)結(jié)構(gòu)化查詢語言。由Boyce和Chamberlin于1974年提出。國際標(biāo)準(zhǔn)化組織1987年批準(zhǔn)將其作為關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言。SQL通用性強(qiáng)、功能強(qiáng)大，幾乎所有的關(guān)系數(shù)據(jù)庫管理系統(tǒng)軟件都支持SQL。22.3.2SQL的特點(diǎn)SQL語言是一種一體化語言,它包括數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制等方面的功能，它可以完成數(shù)據(jù)庫活動(dòng)中的全部工作。SQL語言是一種高度非過程化的語言，它沒有必要一步步地告訴計(jì)算機(jī)“如何”去做，而只需要描述清楚用戶要“做什么”，SQL語言就可以將要求交給系統(tǒng)，自動(dòng)完成全部工作。32.3.2SQL的特點(diǎn)③SQL語言非常簡(jiǎn)潔。9個(gè)命令動(dòng)詞完成核心功能。SQL功能命令動(dòng)詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE④SQL語言可以直接以命令方式交互使用，也可以嵌入到程序設(shè)計(jì)語言中以程序方式使用。⑤面向集合的操作方式。SQL采用集合操作方式，大大提高了操作效率。SQL命令動(dòng)詞4查詢分析器5查詢分析器功能6(1)用于輸入T-SQL語句的自由格式文本編輯器。(2)在T-SQL語句中使用不同的顏色，以提高復(fù)雜語句的易讀性。(3)對(duì)象瀏覽器和對(duì)象搜索工具，可以輕松查找數(shù)據(jù)庫中的對(duì)象和對(duì)象結(jié)構(gòu)。(4)模板可用于加快創(chuàng)建SQLServer對(duì)象的T-SQL語句的開發(fā)速度。模板是包含創(chuàng)建數(shù)據(jù)庫對(duì)象所需的T-SQL語句基本結(jié)構(gòu)的文件。(5)用于分析存儲(chǔ)過程的交互式調(diào)試工具。(6)以網(wǎng)格或自由格式文本窗口的形式顯示結(jié)果。(7)顯示計(jì)劃信息的圖形關(guān)系圖，用以說明內(nèi)置在T-SQL語句執(zhí)行計(jì)劃中的邏輯步驟。(8)使用索引優(yōu)化向?qū)Х治鯰-SQL語句以及它所引用的表，以了解通過添加其他索引是否可以提高查詢的性能。Sqlserver2005SqlManagementstudio簡(jiǎn)介Sqlserver2005中SqlManagementstudio又稱Sqlserver2005管理平臺(tái)，它包括Sqlserver2000企業(yè)管理器和查詢分析器等方面的功能，此外，該管理平臺(tái)還提供也一種環(huán)境，用于管理Analysisservices,IntegrationservicesReportingservices和Xquery.單擊MicrosoftSqlserver程序組中的Sqlserver2005SqlManagementstudio，啟動(dòng)后就可以打開Sqlserver2005管理平臺(tái)。SQLServer2005組件

----Sqlserver2005查詢分析器界面查詢分析器器窗口或稱【SQLQuery】標(biāo)簽頁SQL查詢結(jié)果【SQL編輯器】工具條【SQL標(biāo)準(zhǔn)】工具條記錄數(shù)據(jù)庫表數(shù)據(jù)庫對(duì)象系統(tǒng)數(shù)據(jù)庫注冊(cè)創(chuàng)建庫演示創(chuàng)建數(shù)據(jù)庫……在Sqlserver服務(wù)器管理平臺(tái)中注冊(cè)服務(wù)器可以存儲(chǔ)服務(wù)器連接信息，以供將來連接使用。服務(wù)器的啟、停演示服務(wù)器啟動(dòng)與停止……SQLServer中的數(shù)據(jù)庫mastermodeltempdbmsdbAdventureWorksAdventureWorksDW系統(tǒng)數(shù)據(jù)庫用戶數(shù)據(jù)庫User1student創(chuàng)建數(shù)據(jù)庫相關(guān)概念1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)

數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)分為邏輯存儲(chǔ)結(jié)構(gòu)和物理存儲(chǔ)結(jié)構(gòu)兩種，數(shù)據(jù)庫的邏輯結(jié)構(gòu)是從用戶的角度理解的，它是數(shù)據(jù)庫中數(shù)據(jù)的組織形式。SQLSERVER的數(shù)據(jù)庫是由諸如表，視圖，索引等不同的數(shù)據(jù)庫對(duì)象所組成的，他們用來存儲(chǔ)特定信息并支持特定功能，構(gòu)成數(shù)據(jù)庫的邏輯存儲(chǔ)結(jié)構(gòu)。數(shù)據(jù)庫的物理存儲(chǔ)結(jié)構(gòu)是指數(shù)據(jù)庫文件時(shí)如何存儲(chǔ)在存儲(chǔ)介質(zhì)上的。數(shù)據(jù)庫以文件形式把數(shù)據(jù)庫保存在存儲(chǔ)介質(zhì)上，他由數(shù)據(jù)庫文件和事務(wù)日志文件組成。一個(gè)數(shù)據(jù)庫至少應(yīng)該包含一個(gè)主數(shù)據(jù)庫文件和一個(gè)事務(wù)日志文件。創(chuàng)建數(shù)據(jù)庫頁：8KB表、索引數(shù)據(jù)數(shù)據(jù)庫數(shù)據(jù)文件

.mdf或.ndf日志文件

.ldf最多=8060字節(jié)數(shù)據(jù)庫文件和事務(wù)日志文件對(duì)于數(shù)據(jù)庫文件來講，它分為主數(shù)據(jù)文件，輔助數(shù)據(jù)庫文件和事務(wù)日志文件。

數(shù)據(jù)庫文件是存放數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對(duì)象的文件，一個(gè)數(shù)據(jù)庫可以有一個(gè)或多個(gè)數(shù)據(jù)庫文件，一個(gè)數(shù)據(jù)庫文件只能屬于一個(gè)數(shù)據(jù)庫。當(dāng)有多個(gè)數(shù)據(jù)庫文件時(shí)，一個(gè)文件被指定為主數(shù)據(jù)庫文件，其擴(kuò)展名為.mdf,它用來存儲(chǔ)數(shù)據(jù)的啟動(dòng)信息和部分或者全部數(shù)據(jù)，一個(gè)數(shù)據(jù)庫只能有一個(gè)主數(shù)據(jù)庫文件。輔助數(shù)據(jù)庫文件用來存儲(chǔ)主數(shù)據(jù)庫文件未能存儲(chǔ)的剩余數(shù)據(jù)和數(shù)據(jù)庫對(duì)象。一個(gè)數(shù)據(jù)庫可以沒有輔助數(shù)據(jù)庫文件，也可以有多個(gè)。擴(kuò)展名為.ndf

事務(wù)日志文件用于記錄所有事務(wù)和由每個(gè)事務(wù)對(duì)數(shù)據(jù)庫的修改，存儲(chǔ)數(shù)據(jù)庫的更新情況等。當(dāng)數(shù)據(jù)庫受到損害時(shí)，管理員使用事務(wù)日志文件恢復(fù)數(shù)據(jù)庫。擴(kuò)展名為.ldf。每個(gè)數(shù)據(jù)庫都有事務(wù)日志文件。

SQLServer中的數(shù)據(jù)庫文件主數(shù)據(jù)文件*.mdf僅有一個(gè)事務(wù)日志文件*.ldf一到多個(gè)

次數(shù)據(jù)文件*.ndf零到多個(gè)次數(shù)據(jù)文件*.ndf零到多個(gè)(僅有一個(gè))主文件組(零到多個(gè))次文件組事務(wù)日志一個(gè)數(shù)據(jù)庫的文件集數(shù)據(jù)庫的文件組成邏輯文件名和物理文件名SQLSERVER的文件擁有兩個(gè)名稱，即邏輯文件名和物理文件名。當(dāng)使用Transact_sql命令語句來訪問某一個(gè)文件時(shí)，必須使用該文件的邏輯文件名，而且數(shù)據(jù)庫中各邏輯文件名必須唯一。物理文件名是文件實(shí)際存儲(chǔ)在磁盤上的文件名，而且可以包含完整的磁盤目錄路徑。例如，系統(tǒng)數(shù)據(jù)庫master,master為邏輯文件名，而其對(duì)應(yīng)的物理文件名為master.mdf,其事務(wù)日志文件名為master.ldfstudent的物理文件結(jié)構(gòu)Student_log.ldfE:\日志文件abcde

abcd

主文件組Student_data.mdfC:\tbl…

tbl…

tblclass

tblstudent

演示創(chuàng)建數(shù)據(jù)庫……SQLServer的系統(tǒng)數(shù)據(jù)庫數(shù)據(jù)庫服務(wù)器上自動(dòng)建立了6個(gè)數(shù)據(jù)庫，其中有4個(gè)系統(tǒng)數(shù)據(jù)庫。系統(tǒng)數(shù)據(jù)庫:master數(shù)據(jù)庫tempdb數(shù)據(jù)庫model數(shù)據(jù)庫msdb數(shù)據(jù)庫Master數(shù)據(jù)庫Sqlserver的主數(shù)據(jù)庫，記錄了sqlserver系統(tǒng)的所有系統(tǒng)信息。Tempdb數(shù)據(jù)庫用來保存所有的臨時(shí)表和臨時(shí)存儲(chǔ)過程。Model數(shù)據(jù)庫用作在系統(tǒng)上創(chuàng)建的所有數(shù)據(jù)庫的模版。Msdb數(shù)據(jù)庫提供sqlserver代理程序調(diào)度警報(bào)和作業(yè)示例數(shù)據(jù)庫WearManager數(shù)據(jù)庫對(duì)象的標(biāo)識(shí)符例如數(shù)據(jù)庫名、表名、視圖名、列名等。SQLServer標(biāo)識(shí)符的命名遵循以下規(guī)則：(1)標(biāo)識(shí)符包含的字符數(shù)必須在1到128之間。(2)標(biāo)識(shí)符的第一個(gè)字符必須是字母、下劃線(_)、at符號(hào)(@)或者數(shù)字符號(hào)(#)。(3)標(biāo)識(shí)符的后續(xù)字符可以為字母、數(shù)字或“@”符號(hào)、“$”符號(hào)、數(shù)字符號(hào)或下劃線。(4)標(biāo)識(shí)符不能是Transact-SQL的保留字，也不能包含空格。25SQLServer數(shù)據(jù)類型SQL的數(shù)據(jù)類型包括：INT，F(xiàn)LOAT，NUMERIC，CHAR,VARCHAR,TEXT,BINARY,VARBINARY,DATETIME等262728定長二進(jìn)制binary(n)：按n個(gè)字節(jié)的固定長度存放二進(jìn)制數(shù)據(jù)，最大長度為8KB，若不足，在數(shù)據(jù)尾部加0補(bǔ)足。變長二進(jìn)制varbinary(n)：按不超過n個(gè)字節(jié)的實(shí)際長度存放二進(jìn)制數(shù)據(jù)，最大長度8KB,若不足，按實(shí)際長度存儲(chǔ)3.圖像二進(jìn)制image：用來存放文本文檔、Excel圖表及圖象數(shù)據(jù)（.gif、.bmp、.jpeg）等。

二進(jìn)制數(shù)據(jù)SQLServer用binary、varbinary和image三種數(shù)據(jù)類型來存儲(chǔ)二進(jìn)制數(shù)據(jù)。二進(jìn)制類型用于存儲(chǔ)聲音、圖像等數(shù)字類型的數(shù)據(jù)。29

注意：

1.二進(jìn)制數(shù)據(jù)常量不允許加引號(hào)，默認(rèn)用十進(jìn)制

書寫，輸出顯示默認(rèn)采用十六進(jìn)制。

2.若實(shí)際數(shù)據(jù)的二進(jìn)制長度超過指定的n個(gè)字節(jié)，用

局部變量存儲(chǔ)時(shí)截取二進(jìn)制的前n個(gè)字節(jié)。

3.數(shù)據(jù)庫中二進(jìn)制字段的數(shù)據(jù)不能在數(shù)據(jù)表中直接輸

入。

4.在用INSERT或UPDATE語句為數(shù)據(jù)表二進(jìn)制字段

輸入、更新數(shù)據(jù)時(shí)，如果超過n字節(jié)，則系統(tǒng)報(bào)錯(cuò)。30

數(shù)值型數(shù)據(jù)SQLServer的數(shù)值型數(shù)據(jù)用8種：整型數(shù)據(jù)4種，實(shí)型數(shù)據(jù)4種。1.字節(jié)型整數(shù)TinyInt2.短整型整數(shù)SmallInt3.基本整型整數(shù)Int或Integer4.長整型整數(shù)Bigint5.近似值實(shí)型浮點(diǎn)數(shù)Real6.可變精度實(shí)型浮點(diǎn)數(shù)Float(n)7.精確小數(shù)型數(shù)據(jù)Numeric(p.s)8.精確小數(shù)型數(shù)據(jù)Decimal(p.s)或Dec(p.s)31字符型數(shù)據(jù)SQLServer提供了Char(n)、Varchar(n)和Text三種ASCII碼字符型數(shù)據(jù)。1.定長字符型Char(n)：按n個(gè)字節(jié)固定存放字符串，若不足則補(bǔ)空格。2.變長字符型Varchar(n)：按不超過n個(gè)字節(jié)的實(shí)際長度存放字符串，長度為8000。2.變長字符型nVarchar(n)：每個(gè)字符占2個(gè)字節(jié)（中英文相同），，長度為4000。3.文本類型Text：當(dāng)存儲(chǔ)字符型數(shù)據(jù)超過8000字節(jié)時(shí)，可選擇Text數(shù)據(jù)類型。32

注意：

1.字符型數(shù)據(jù)一個(gè)英文符號(hào)占1個(gè)字節(jié)，漢字占2個(gè)字

節(jié)。

2.字符型字符串常量必須用單引號(hào)括起來。

3.Char、Varchar數(shù)據(jù)類型若不指定長度n，默認(rèn)為1

個(gè)字節(jié)。

4.若實(shí)際字符串長度超過指定的n個(gè)字節(jié)，用局部變量

存儲(chǔ)時(shí)截取字符串前n個(gè)字節(jié)。

5.可以在企業(yè)管理器中直接輸入到數(shù)據(jù)表中，但輸入

的字符串長度超過字段指定長度時(shí)系統(tǒng)不接受，也

不會(huì)自動(dòng)截取。

6.在用INSERT或UPDATE語句為數(shù)據(jù)表字符型字段輸入，更新字符串時(shí)，如果超過n字節(jié)，則系統(tǒng)報(bào)錯(cuò)。33日期/時(shí)間型數(shù)據(jù)SQLServer提供了Smalldatetime和Datetime兩種日期/時(shí)間的數(shù)據(jù)類型。短日期/時(shí)間型Smalldatetime

占4個(gè)字節(jié)固定長度的存放，存放1900/1/1~2079/6/6的日期時(shí)間，精確到分鐘。2.基本日期/時(shí)間型Datetime

占8個(gè)字節(jié)固定長度的存放，存放1753/1/1~9999/12/31的日期時(shí)間，精確到千分之一秒。

注意：

1.日期/時(shí)間型常量必須用單引號(hào)括起來。

2.用戶在沒有指定小時(shí)以下精確的時(shí)間數(shù)據(jù)時(shí)，自動(dòng)

設(shè)置日期/時(shí)間型數(shù)據(jù)的時(shí)間為00：00：00。

3.數(shù)據(jù)庫中默認(rèn)的日期格式為“年-月-日”，也可以使用“月-日-年”或“日-月-年”。（年/月/日）

4.如果使用“日-月-年”或“日/月/年”，系統(tǒng)不能區(qū)分時(shí)默認(rèn)按“月-日-年”處理。35貨幣型數(shù)據(jù)SQLServer提供了Smallmoney和Money的數(shù)據(jù)類型。短貨幣型Smallmoney

占4個(gè)字節(jié)，前2個(gè)字節(jié)是整數(shù)部分，后2個(gè)字節(jié)為小數(shù)部分。2.基本貨幣型Money

占8個(gè)字節(jié)，前4個(gè)字節(jié)是整數(shù)部分，后4個(gè)字節(jié)為小數(shù)部分。Money或Smallmoney類型是數(shù)值常量，應(yīng)加貨幣符號(hào)$前綴，負(fù)數(shù)時(shí)加后綴$.36位類型數(shù)據(jù)只能存放0、1和NULL，一般用于邏輯判斷2.占1位二進(jìn)制內(nèi)存，如果一個(gè)數(shù)據(jù)表中有8個(gè)以下的位類型字段，系統(tǒng)用一個(gè)字節(jié)存儲(chǔ)所有這些字段，超過8個(gè)不足16個(gè)用2個(gè)字節(jié)存放。3.位類型數(shù)據(jù)輸入任意的非0值時(shí)，都按1處理。37圖像、文本型數(shù)據(jù)的存儲(chǔ)方式

1.存儲(chǔ)文本、圖像等大型數(shù)據(jù)時(shí)，可使用Text、Ntext和Image三種數(shù)據(jù)類型。這三種數(shù)據(jù)類型的數(shù)據(jù)量比較大。2.使用系統(tǒng)存儲(chǔ)過程sp_tableoption指定文本或圖像數(shù)據(jù)是否在表的行中存儲(chǔ)：語法格式：sp_tableoption‘表名’，‘textinrow’,’true|false’38運(yùn)算符與表達(dá)式算術(shù)運(yùn)算符與表達(dá)式算術(shù)運(yùn)算符:+、-、*、/、%

邏輯類運(yùn)算符與邏輯值表達(dá)式SQL的邏輯表達(dá)式取值有三個(gè)邏輯值：TRUE（真）FALSE（假）UNKNOWN（不確定）39

邏輯類運(yùn)算符與邏輯值表達(dá)式1.比較運(yùn)算符：>,>=,=,<,<=,<>或!=,!>,!<

條件表達(dá)式：表達(dá)式1比較運(yùn)算符表達(dá)式22.邏輯運(yùn)算符：not(非)、and(邏輯與)、or(邏輯或)

級(jí)別：TRUEUNKNOWNFALSE（高低）

AND運(yùn)算：結(jié)果取低級(jí)的

OR運(yùn)算：結(jié)果取高級(jí)的

NOT運(yùn)算：結(jié)果取相反的40邏輯類運(yùn)算符與邏輯值表達(dá)式3.范圍運(yùn)算符:[not]between…and

條件表達(dá)式：表達(dá)式[not]between起始值and終止值例：Xbetween5and10（則X>=5且X<=10條件為真，X<5或X>10為假）

Xnotbetween5and10（則X<5或X>10條件為真，X>=5且X<=10為假）注意：between所選取的數(shù)據(jù)范圍包括邊界值，notbetween不包括。41邏輯類運(yùn)算符與邏輯值表達(dá)式4.多值列表運(yùn)算符[not]…in(…)條件表達(dá)式：[not]表達(dá)式in(值1，值2，…，值n)（1）in(…)用于判斷表達(dá)式的值是否等于所給出的值之一，只要與其中一個(gè)值相等條件就為真，全部不等為假。如：xin(1,3,5)相當(dāng)于x=1orx=3orx=5（2）not…in(…)表示判斷表達(dá)式的值是否全部不等于所給出的值，所有的值一個(gè)也不相等條件為真，只要有一個(gè)相等為假。如：notxin(1,3,5)相當(dāng)于x<>1andx<>3andx<>542

4.2.2邏輯類運(yùn)算符與邏輯值表達(dá)式5.用于子查詢的運(yùn)算符（1）列表比較運(yùn)算符ANY|ALL

表達(dá)式格式：表達(dá)式比較運(yùn)算符ANY（子查詢的一列值）表達(dá)式比較運(yùn)算符ALL（子查詢的一列值）說明：只要有一個(gè)比較成立：ANY的結(jié)果為真只有全部比較都成立：ALL的結(jié)果為真

例：3+1>ANY(1,2,3,4)T 3+1>ALL(1,2,3,4,5)F（2）記錄存在邏輯運(yùn)算符[not]exists

用于檢查子查詢返回的結(jié)果集中是否包含有記錄，若包含，則exists為真，否則為假。43邏輯類運(yùn)算符與邏輯值表達(dá)式6.空值運(yùn)算符：[not]isnull

條件表達(dá)式：表達(dá)式[not]isnull說明：（1）isnull表示判斷表達(dá)式的值是否等于空值，為空時(shí)條件為真，否則為假。（1）Notisnull表示判斷表達(dá)式的值是否不等于空值，不為空時(shí)為真，否則為假。

44邏輯類運(yùn)算符與邏輯值表達(dá)式7.字符模糊匹配運(yùn)算符：[not]like‘通配符’通配符種類：%：代表0個(gè)或多個(gè)字符的任意字符串?！?”：代表單個(gè)任意字符“[abcd]”:代表指定字符中的任何一個(gè)單字符.“[^abc]”:代表不在指定字符中的任何一個(gè)單字符。轉(zhuǎn)義字符：在where子句后用escape子句指定一個(gè)轉(zhuǎn)義字符，abc為字段名，查找所有以%開始的字符。例：select1where'ABC%DE'like'ABC[%]DE

WHEREColumnALIKE'%5/%%'ESCAPE'/'

45字符串連接運(yùn)算符連接運(yùn)算符與字符串表達(dá)式字符串表達(dá)式：字符串表達(dá)式1+字符串表達(dá)式2例：‘ABC’+‘DEF’，則結(jié)果是：‘ABCDEF’‘ABC’+‘，’+‘EFG’則結(jié)果是：‘ABC，EFG’46SQL的函數(shù)分為系統(tǒng)函數(shù)(內(nèi)置函數(shù))和用戶自定義函數(shù)。4.3.1數(shù)學(xué)函數(shù)

函數(shù)功能說明函數(shù)功能說明Abs(x)求絕對(duì)值Ceiling(x)求不小于x的最小整數(shù)Asin(x)求反正弦值Floor(x)求不大于x的最大整數(shù)Acos(x)求反余弦值Sin(x)求x（弧度）的正弦值Exp(x)求ex的指數(shù)Cos(x)求x(弧度)的余弦值47字符串函數(shù)

函數(shù)功能說明函數(shù)功能說明ASCII(A)字符串A第一個(gè)字符的ASCII碼Len(A)去掉空格后A的字符個(gè)數(shù)Char(x)ASCII碼為X的字符Lower(A)大寫變小寫Charindex(A,B[,start])返回字符串B在字符串A自start后的其實(shí)位置Ltrim(A[,’B’])將字串A左端字符B刪掉，缺省為刪掉空格Left(A,x)從字符A的左端取X個(gè)字符的子串Space(x)得到有x個(gè)空格的字符串48系統(tǒng)內(nèi)置函數(shù)4.3.3日期時(shí)間函數(shù)Dateadd(yy|mm|dd,x,D):得到按第一個(gè)參數(shù)指定的項(xiàng)目D+x的值dateadd(dd,20,’2008-3-16’)指定日期加20天Datediff(yy|mm|dd,D1,D2):得到按第一個(gè)參數(shù)指定的項(xiàng)目D2-D1的差值.datediff(yy,’1960-3-16’,getdate())可以根據(jù)出生日期得到當(dāng)前的年齡Day(D):得到D的日期數(shù).Getdate():得到系統(tǒng)的日期和時(shí)間.Month(D):得到D的月份數(shù).Year(D):得到D的年份數(shù).49系統(tǒng)內(nèi)置函數(shù)4.3.4類型轉(zhuǎn)換函數(shù)Cast(表達(dá)式as數(shù)據(jù)類型[長度])

將表達(dá)式的值轉(zhuǎn)換成指定的“數(shù)據(jù)類型”。Convert(數(shù)據(jù)類型[長度],表達(dá)式[,style]):

按style格式將表達(dá)式的值轉(zhuǎn)換成指定的“數(shù)據(jù)類型”。例：根據(jù)出生年月，求年齡。Cast(year(‘1990-3-7’)aschar(4))_+’年出生的人是’+cast(datediff(yy,’1990-3-7’),getdate())aschar(2))+’歲’2.3.3T-SQL語言（一）查詢功能（二）操作功能（三）定義功能50T-SQL語言T-SQL語言側(cè)重于處理數(shù)據(jù)庫的數(shù)據(jù)，具體包括：DDL（數(shù)據(jù)定義語言）——用來建立數(shù)據(jù)庫、數(shù)據(jù)庫對(duì)象和定義其列——CREATETABLE、DROPTABLE等DML（數(shù)據(jù)操作語言）——查詢、插入、刪除和修改數(shù)據(jù)庫中的數(shù)據(jù)；——SELECT、INSERT、UPDATE、DELETE等；DCL（數(shù)據(jù)控制語言）——用來控制存取許可、存取權(quán)限等；——GRANT、REVOKE等；變量說明、流程控制、功能函數(shù)——定義變量、判斷、分支、循環(huán)結(jié)構(gòu)等——日期函數(shù)、數(shù)學(xué)函數(shù)、字符函數(shù)、系統(tǒng)函數(shù)等51（一）定義功能標(biāo)準(zhǔn)的SQL的數(shù)據(jù)定義功能：數(shù)據(jù)庫的定義表的定義視圖的定義存儲(chǔ)過程的定義規(guī)則的定義索引的定義52創(chuàng)建庫--DropDatabaseWearManager;CreateDatabaseWearManager;DROPDATABASEWearManager;CREATEDATABASEWearManagerON(NAME=Sales_dat,FILENAME='c:\saledat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=Sales_log,FILENAME='c:\salelog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB);GO531）

基本表的定義1）

基本表的定義SQL使用CREATETABLE命令建立表，相應(yīng)的命令格式是：CREATETABLETableName(column_nameDATATYPE[DEFAULTexpression][NULL|NOTNULL][CONSTRAINconstrain_name][,…])TableName-----表的名稱；Column_name-----列的名稱DATATYPE-----列的數(shù)據(jù)類型DEFAULTexpression——該列的缺省值constrain_name——完整性約束的名稱541）

基本表的定義用CREATETABLE命令建立表可以完成用表設(shè)計(jì)器完成的所有功能.除了建立表的基本功能外,它還包括滿足實(shí)體完整性的主關(guān)鍵字PRIMARYKEY,定義默認(rèn)值的DEFAULT等。5556利用SQL命令來建立相同的數(shù)據(jù)庫,然后可利用數(shù)據(jù)庫設(shè)計(jì)器和表設(shè)計(jì)器來檢驗(yàn)用SQL建立的數(shù)據(jù)庫例33用SQLCREATE命令建立倉庫1表useWearManager;droptabledbo.WearHousecreatetabledbo.WearHouse( WNonvarchar(4)NotNull, Wcitynvarchar(20), Wareaint, primarykey(WNo), Unique(WNo))

572）

表的刪除刪除表的SQL命令是:DROPTABLEtable＿nameDROPTABLE直接從磁盤上刪除tablename所對(duì)應(yīng)的文件.如果tablename是數(shù)據(jù)庫中的表并且相應(yīng)的數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫,則從數(shù)據(jù)庫中刪除了表例34：DROPTABLEdbo.WearHouse583）表結(jié)構(gòu)的修改修改表結(jié)構(gòu)的命令是ALTERTABLE。該命令有三種格式：ALTERTALBE<TalbleName>[ADD<column_Name><Data_Type>[CONSTRAINTconstraint_name][DROP<column_Name>[constraint_name][MODIFY<column_Name><Data_Type>]；ADD添加一個(gè)屬性；DROP刪除一個(gè)屬性；MODIFY更改一個(gè)屬性。593）表結(jié)構(gòu)的修改例為訂購單1表增加一個(gè)貨幣類型的總金額字段。ALTERTABLEdbo.WearHouse1ADDWarea

int例

刪除訂購單1表中的金額字段。ALTERTABLEdbo.WearHouse1DROPCOLUMNWarea604）索引操作建立索引CREATEINDEX,一般格式為：CREATE[UNIQUE]INDEXindex_nameONtable_name(column[,…])例37將訂購單1表的職工號(hào)定義為唯一索引，索引名是emp_sum。CREATEUNIQUEINDEXemp_sumON訂購單(職工號(hào))614）索引操作刪除索引DROPINDEX,一般格式為：DROPINDEXtable_name.index_name例

刪除訂購單1表的索引emp_sup。DROPINDEX訂購單.emp_sum62

5）視圖的定義視圖是從表中派出來的虛表。數(shù)據(jù)庫中僅存放視圖的定義，不存放視圖的數(shù)據(jù)。這些數(shù)據(jù)仍然存在于原來的基本表中。63

5）視圖的定義視圖的建立CREATEVIEWview_name[(column_name[，column_name]…)]AS（select_statement）select_statement可以是任意的select查詢語句。視圖時(shí)根據(jù)表定義或派生出來的。涉及視圖時(shí)常把表稱作基本表。645）視圖的定義例39：某個(gè)用戶對(duì)職工關(guān)系只需要或者只能知道職工號(hào)和所工作的倉庫號(hào)，那么可定義視圖：CREATEVIEWe_wAS；SELECT職工號(hào)，倉庫號(hào)FROM職工其中e_w是視圖的名稱。視圖一經(jīng)定義，就可以和基本表一樣進(jìn)行各種查詢，也可以進(jìn)行一些修改操作。對(duì)于最終用戶來講，有時(shí)并不需要知道操作的是基本表還是視圖。

65

5）視圖的定義為了查詢職工號(hào)和倉庫號(hào)信息，可以有命令：SELECT*FROMe_w或SELECT職工號(hào)，倉庫號(hào)FROMe_w或SELECT職工號(hào)，倉庫號(hào)FROM職工它們可以收到同樣的效果。66

5）視圖的定義視圖由于是從表中派生出來的,所以不存在修改結(jié)構(gòu)的問題,但是視圖可以刪除。刪除視圖的命令格式是:DROPVIEW<視圖名>比如要?jiǎng)h除視圖v_emp,只要鍵入命令:DROPVIEWv_emp；67（一）查詢功能SQL的核心是查詢。SQL的查詢命令也稱作SELECT命令，它的基本形式由SELECT-FROM-WHERE查詢模塊組成，多個(gè)查詢可以嵌套執(zhí)行。

SQLSELECT命令的語法格式如下：

SELECT字段列表

FROM表列表

[WHERE<條件表達(dá)式>][GROUPBY….][HAVING<條件表達(dá)式>][ORDERBY…]68（二）查詢功能SELECT說明要查詢的數(shù)據(jù)FROM說明要查詢的數(shù)據(jù)來自哪個(gè)或哪些表，可以對(duì)單個(gè)表或多個(gè)表進(jìn)行查詢；WHERE說明查詢條件，即選擇元組的條件；GROUPBY短語用于對(duì)查詢結(jié)果進(jìn)行分組，可以利用它進(jìn)行分組匯總；HAVING短語必須跟隨GROUPBY短語使用，它用來限定分組必須滿足的條件；ORDERBY用來對(duì)查詢的結(jié)果進(jìn)行排序。69訂貨管理系統(tǒng)數(shù)據(jù)庫70

1）簡(jiǎn)單查詢（基于單個(gè)表或關(guān)系的查詢）例1

從職工關(guān)系中檢索所有的工資值。SELECTESalary

FROMdbo.EmployeeSELECTDISTINCTESalary

FROMdbo.Employee；去掉查詢結(jié)果中的重復(fù)值。例2

檢索倉庫關(guān)系中的所有元組。SELECT*FROMdbo.WearHouse;等價(jià)于SELECTWNo,Wcity,WareaFROMdbo.WearHouse;71例3

檢索工資多于1230元的職工號(hào)SELECTENoFROMEmployeeWHEREESalary>1230;例4

檢索哪些倉庫有工資多于1210元的職工SELECTDISTINCTWNoFROMEmployee

WHEREESalary>1210;72

1）簡(jiǎn)單查詢（基于單個(gè)表或關(guān)系的查詢）例5

給出在倉庫“WH1”或“WH2”工作,并且工資少于1250元的職工號(hào)SELECTENoFROMEmployee

WHEREESalary<1250AND(WNo='WH1'ORWNo='WH2');例：給出員工工資組別selectesalaryfromemployeegroupbyesalary;統(tǒng)計(jì)每個(gè)倉庫人數(shù)統(tǒng)計(jì)每個(gè)倉庫職工工資總額73

1）簡(jiǎn)單查詢（基于單個(gè)表或關(guān)系的查詢）2)簡(jiǎn)單的聯(lián)接查詢聯(lián)接查詢是一種基于多個(gè)關(guān)系的查詢。例6

找出工資多于1230元的職工號(hào)和他們所在的城市selecteno,wcityfromemployee,wearhousewhere

(esalary>1230)and(employee.wno=wearhouse.wno)倉庫關(guān)系和職工關(guān)系之間存在一個(gè)一對(duì)多的聯(lián)系。74例7

找出工作在面積大于400的倉庫的職工號(hào)以及這些職工所在的城市selecteno,wcity,wareafromemployee,wearhousewhere(warea>400)and(employee.wno=wearhouse.wno)75

3）

嵌套查詢基于多個(gè)關(guān)系的查詢，這類查詢所要求的結(jié)果出自一個(gè)關(guān)系，但相關(guān)的條件卻涉及多個(gè)關(guān)系。當(dāng)檢索關(guān)系X中的元組時(shí),它的條件依賴于相關(guān)的關(guān)系Y中的元組屬性值,這時(shí)使用嵌套查詢。例8

哪些城市至少有一個(gè)倉庫的職工工資為1250元分析:此例要求查詢倉庫表中的城市信息,而查詢條件是職工表中的工資字段值。selectWcityfromWearhousewhereWnoin(selectWnofromemployeewhereEsalary=1250)76

3）

嵌套查詢例9

查詢所有的職工工資都多于1210元的倉庫的信息select*fromWearHousewhereWNoin(selectWnofromemployeewhereEsalary>1210)andWnoin(selectWnofromemployee);“北京”的“WH1”所有職工工資都多于1210元？。77

3）

嵌套查詢例9

查詢所有的職工工資都多于1210元的倉庫的信息分析:此例也可描述為沒有一個(gè)職工的工資少于或等于1210元的倉庫的信息SELECT*FROMWearHouseWHEREWNoNOTIN(SELECTWNoFROMEmployeeWHEREESalary<=1210);“武漢”的“WH4”倉庫還沒有職工，但該倉庫的信息也被檢索出來了。78

3）

嵌套查詢排除那些還沒有職工的倉庫,檢索要求描述為:查詢所有的職工工資都多于1210元的倉庫的信息,并且該倉庫至少要有一名職工。select*fromWearHousewhereWNonotin(selectWnofromemployeewhereEsalary<=1210)andWnoin(selectWnofromemployee);79

3）

嵌套查詢例10

找出和職工E4掙同樣工資的所有職工的職工號(hào)SELECTENoFROMEmployeeWHEREESalary=(SELECTESalaryFROMEmployeeWHEREENo='E4');ANDEno!='E4'80思考結(jié)果？4）幾個(gè)特殊的運(yùn)算符BETWEEN…AND…LIKE!=NOT例11

檢索出工資在1220元到1240元范圍內(nèi)的職工信息。SELECT*FROM職工WHERE工資BETWEEN1220AND1240；等價(jià)于：SELECT*FROM職工WHERE(工資>=1220)AND(工資<=1240)；81例12

檢索出名字中含有“公司”字樣的供應(yīng)商信息匹配字符串查詢，使用LIKE運(yùn)算符SELECT*FROM供應(yīng)商WHERE供應(yīng)商名LIKE‘%公司’匹配符%：表示0個(gè)或多個(gè)字符匹配符:_(下劃線)：表示一個(gè)字符824）幾個(gè)特殊的運(yùn)算符4）幾個(gè)特殊的運(yùn)算符例13

找出不在北京的全部供應(yīng)商信息SELECT*FROM供應(yīng)商WHERE地址!=‘北京’SELECT*FROM供應(yīng)商WHERENOT(地址=‘北京’)

找出工資不在1220元和1240元之間的全部職工信息SELECT*FROM職工WHERE工資NOTBETWEEN1220AND1240；835）

排序使用SQLSELECT可以將查詢結(jié)果排序,使用短句ORDERBYORDERBY,Order_Item[ASC|DESC][,Order_Item[ASC|DESC]…]例14

按職工的工資值升序檢索出全部職工信息SELECT*FROM職工ORDERBY工資；降序：SELECT*FROM職工ORDERBY工資DESC

；845）

排序例15先按倉庫號(hào)排序,再按工資排序并輸出全部職工信息SELECT*FROM職工ORDERBY倉庫號(hào),工資注意：ORDERBY是對(duì)最終的查詢結(jié)果進(jìn)行排序，不可以在子查詢中使用該短語。856）

簡(jiǎn)單的計(jì)算查詢用于計(jì)算檢索的函數(shù)有:(1)

COUNT----計(jì)數(shù)(2)

SUM----求和(3)

AVG----計(jì)算平均值(4)

MAX----求最大值(5)

MIN----求最小值這些函數(shù)可以用在SELECT短語中對(duì)查詢結(jié)果進(jìn)行計(jì)算866）

簡(jiǎn)單的計(jì)算查詢例16

找出供應(yīng)商所在地的數(shù)目SELECTCOUNT(DISTINCT地址)FROM供應(yīng)商注意:除非對(duì)關(guān)系中的元組個(gè)數(shù)進(jìn)行計(jì)數(shù),一般COUNT函數(shù)應(yīng)該使用DISTINCT.例如:SELECTCOUNT(*)FROM供應(yīng)商

例17

求支付的工資總數(shù)SELECTSUM(工資)FROM職工若使用SELECTSUM(DISTINCT工資)FROM職工

876）

簡(jiǎn)單的計(jì)算查詢例18

求北京和上海的倉庫職工的工資總和SELECTSUM(工資)FROM職工WHERE倉庫號(hào)IN(SELECT倉庫號(hào)FROM倉庫WHERE城市=‘北京’OR

城市=‘上?！?;886）

簡(jiǎn)單的計(jì)算查詢例19

求所有職工的工資都多于1210元的倉庫的平均面積SELECTAVG(面積)FROM倉庫WHERE倉庫號(hào)NOTIN(SELECT倉庫號(hào)FROM職工WHERE工資<=1210);注意:

以上結(jié)果包含了尚沒有職工的WH4倉庫,如果要排除沒有職工的倉庫,以上語句應(yīng)改為:SELECTAVG(面積)FROM倉庫WHERE倉庫號(hào)NOTIN(SELECT倉庫號(hào)FFROM職工WHERE工資<=1210)AND倉庫號(hào)IN(SELECT倉庫號(hào)FROM職工）;896）

簡(jiǎn)單的計(jì)算查詢例20

求在WH2倉庫工作的職工的最高工資值SELECTMAX(工資)FROM職工WHERE倉庫號(hào)=‘WH2’求最低工資值:SELECTMIN(工資)FROM職工WHERE倉庫號(hào)=‘WH2’907）

分組與計(jì)算查詢利用GROUPBY進(jìn)行分組計(jì)算查詢,GROUPBY短語的格式如下:GROUPBYGroupColumn[,GroupColumn…][HAVINGFilterCondition]例21

求每個(gè)倉庫的職工的平均工資SELECT倉庫號(hào),AVG(工資)FROM職工

GROUPBY倉庫號(hào)；917）

分組與計(jì)算查詢例22

求至少有兩個(gè)職工的每個(gè)倉庫的平均工資SELECT倉庫號(hào),COUNT(*),AVG(工資)FROM職工；GROUPBY倉庫號(hào)HAVINGCOUNT(*)>=2HAVING子句總是跟在GROUPBY

子句之后,不可以單獨(dú)使用.HAVING子句和WHERE子句不矛盾,在查詢中是先用WHERE子句限定元組,然后進(jìn)行分組,最后再用HAVING子句限定分組.928）

利用空值查詢例23

找出尚未確定供應(yīng)商的訂購單SELECT*FROM訂購單WHERE供應(yīng)商號(hào)ISNULL注意：查詢空值時(shí)要使用ISNULL，而=NULL是無效的，因?yàn)榭罩挡皇且粋€(gè)確定的值，所以不能用“=”這樣的運(yùn)算符進(jìn)行比較。

例24

列出已經(jīng)確定了供應(yīng)商的訂購單信息SELECT*FROM訂購單WHERE供應(yīng)商號(hào)ISNOTNULL939）

別名與自然聯(lián)接查詢?cè)诼?lián)接操作中,經(jīng)常需要使用關(guān)系名作為前綴,有時(shí)這樣做顯得很麻煩.因此,SQL允許在FORM短語中為關(guān)系名定義別名

<關(guān)系名><別名>比如,如下的聯(lián)接語句是一個(gè)基于4個(gè)關(guān)系的聯(lián)接查詢,查詢供貨商所在地與倉庫所在地均在北京的供貨商信息。其中必須使用關(guān)系名做前綴SELECT供應(yīng)商名FROM供應(yīng)商,訂購單,職工,倉庫WHERE地址=”北京”AND城市=”北京”AND供應(yīng)商.供應(yīng)商號(hào)=訂購單.供應(yīng)商號(hào)AND訂購單.職工號(hào)=職工.職工號(hào)AND職工.倉庫號(hào)=倉庫.倉庫號(hào)；949）

別名與自然聯(lián)接查詢使用別名的聯(lián)接嵌套查詢SELECT供應(yīng)商名FROM供應(yīng)商S,訂購單P,職工E,倉庫WWHERE地址=‘北京’AND城市=‘北京’ANDS.供應(yīng)商號(hào)=P.供應(yīng)商號(hào)ANDP.職工號(hào)=E.職工號(hào)AND職工E.倉庫號(hào)=W.倉庫號(hào)以上例子中,別名不是必須的,但在關(guān)系的自然聯(lián)接中,別名是必不可少的。95自然聯(lián)接什么是自然聯(lián)接?SQL不僅可以對(duì)多個(gè)關(guān)系實(shí)行聯(lián)接操作,也可以將同一個(gè)關(guān)系與其自身進(jìn)行聯(lián)接,這種聯(lián)接就稱為自然聯(lián)接。在可以進(jìn)行這種自然聯(lián)接的關(guān)系中,實(shí)際存在著一種特殊的遞歸關(guān)系,即關(guān)系中的一些元組,根據(jù)出自同一值域的兩個(gè)不同的屬性,可以與另外的一些元組有一種對(duì)應(yīng)關(guān)系(一對(duì)多聯(lián)系).96自然聯(lián)接假設(shè)雇員關(guān)系:雇員(雇員號(hào),雇員姓名,經(jīng)理)其中雇員號(hào)和經(jīng)理兩個(gè)屬性出自同一值域,同一元組的這兩個(gè)屬性值是上下級(jí)關(guān)系.雇員號(hào)雇員姓名經(jīng)理E3趙涌

E4錢潮E3E6孫潔E3E8李路E6雇員號(hào)雇員姓名經(jīng)理例25

根據(jù)雇員