1、主講人：孫承愛總學(xué)時：54學(xué)時（上課36學(xué)時，上機18學(xué)時）授課方式：有的邊講邊練，有的課下練習(xí)。大作業(yè)：開發(fā)一個小系統(tǒng)（50分）平時成績：考勤+作業(yè)+操作（50分）電話:P） 86057852（O）Email: 數(shù)據(jù)庫原理與應(yīng)用4.1 TSQL語言 1、TSQL概述：TSQL是應(yīng)用于數(shù)據(jù)庫的結(jié)構(gòu)化查詢語言，本身不能獨立存在，是一種非過程性的語言。2、TSQL語言按照用途可以分為如下4類： DQL（Data Query Language）數(shù)據(jù)查詢語言 主要進行查詢操作（SELECT語句） DDL（Data Definition Language）數(shù)據(jù)定義語言 主要進行

2、建立對象的操作（建庫、表、視圖、索引等） DML（Data Manipulation Language）數(shù)據(jù)操縱語言 主要進行數(shù)據(jù)處理操作（插入、刪除、更新等） DCL（Data Control Language）數(shù)據(jù)控制語言 主要進行多個SQL語句的操作4 標準SQL語句 啟動查詢分析器的操作步驟：（1）在“開始”菜單的“程序”級聯(lián)菜單中，選擇Microsoft SQL Server程序組中的“查詢分析器”選項，即可啟動SQL查詢分析器。（2）在啟動SQL查詢分析器時，系統(tǒng)首先打開“連接到SQL Server”對話框，如右圖所示。如果是遠程SQL Server服務(wù)器，可以直接在SQL Ser

3、ver文本框中輸入遠程計算機的IP地址。4.2 TSQL基礎(chǔ) SQL Server 提供了多種圖形和命令行工具，用戶可以使用不同的方法來訪問數(shù)據(jù)庫。SQL查詢分析器是一個圖形用戶界面，用以交互地設(shè)計和測試SQL 語句、批處理和腳本。4.2.1 查詢分析器（3）選擇SQL Server服務(wù)器后，單擊“確定”按鈕，然后在“連接到SQL Server”對話框中設(shè)置正確的驗證模式，即可連接到SQL Server服務(wù)器，并打開SQL查詢分析器，如圖所示。對象窗口SQL的輸入窗口語句執(zhí)行查詢的按鈕4.2.2 數(shù)據(jù)庫的操作語句1. 創(chuàng)建數(shù)據(jù)庫CREATE DATABASE database_name（新庫名

4、不可多于128個字符） ON filespec ON子句顯式定義數(shù)據(jù)文件 LOG ON filespec LOG ON子句顯式定義日志文件 如果省略O(shè)N和LOG ON，庫的大小與model庫的大小相等filespec定義為( NAME = logical_file_name , FILENAME = os_file_name（指出操作系統(tǒng)創(chuàng)建filespec定義的物理文件時使用的路徑名和文件名） , SIZE = size （指出filespec中定義的文件大小，不可為分數(shù)） , MAXSIZE = max_size | UNLIMITED （指出filespec中定義的文件可以增長到的最大值

5、，不可為分數(shù)） , FILEGROWTH = growth_increment )（指出filespec中定義的文件的增長增量，不能超過MAXSIZE，不可為分數(shù)）例如：Create database testOn ( name = test1, filename = F:testDB_test1.mdf, size = 10mb, maxsize = 50mb unlimited, filegrowth = 10mb )Log on ( name = test1_log, filename = F:testDB_test1.ldf, size = 10mb, maxsize =50mb un

6、limited, filegrowth = 10mb)執(zhí)行并刷新后可以在企業(yè)管理器中看到新建立的數(shù)據(jù)庫2、修改數(shù)據(jù)庫：ALTER DATABASE database_name （要修改的庫名） ADD FILE filespec （指定要添加文件）| ADD LOG FILE filespec （指定要添加日志文件）| REMOVE FILE logical_file_name（指定從庫中刪除空文件）| MODIFY FILE filespec （指定要更改給定的文件）| MODIFY NAME = new_dbname （用來重命名數(shù)據(jù)庫） filespec定義同前面所述4.2.3 表的操作

7、語句1. 表的創(chuàng)建使用CREATE TABLE語句來建立表，其語法如下：CREATE TABLE table_name（表名）( column_namel （列名）data_type（類型） NULL | NOT NULL （是否允許為空） PRIMARY | UNIQUE （是否為主鍵/唯一性） FOREIGN KEY (column_name)（外鍵）REFERENCES ref_table (ref_column column_name2 ) )（是否與其它表關(guān)聯(lián)）例1：create table sc( sno char(10) foreign key references studen

8、t (sno), cno char(10) foreign key references course(cno), degree numeric(9) )與其他表建立關(guān)聯(lián):表的字段可能參考到其他表的字段，這就需要將兩個表建立關(guān)聯(lián)，語法為：FOREIGN KEY REFERENCE ref_table(ref_column)例2：create table score( sno char(10) not null foreign key references student(sno), cno char(10) not null foreign key references course(cno)

9、, degree numeric(9),primary key (sno,cno) )創(chuàng)建數(shù)據(jù)表時的選項從父表中刪除記錄時,如果有一個子記錄引用這個被刪除的父記錄,則參照完整性就丟失了,此時可以考慮以下幾種情況:1)NO ACTION (不操作)：如果有任何相關(guān)的子記錄,就不從父表中將該記錄刪除。2)CASCADE(級聯(lián))：當(dāng)父記錄被刪除后,自動刪除相關(guān)的子記錄.如果被刪除的子記錄也在其它關(guān)系中充當(dāng)父記錄,那么也應(yīng)該刪除那些子表中的相關(guān)記錄。3)SET NULL(置空)；當(dāng)父記錄被刪除時,相關(guān)子記錄中外鍵的值自動被置為空值。4)SET DEFAULT(置預(yù)定值)：當(dāng)父記錄被刪除時,所有相關(guān)子記

10、錄中的外碼的值自動置為他們的預(yù)置值。5)NO CHECK(不檢查)：當(dāng)父記錄被刪除時，不做任何保證參照完整性的操作。使用SQL語句時應(yīng)注意的事項1、SQL中的字符串中只能用，而不能用“”2、每個SELECT語句將產(chǎn)生一個查詢結(jié)果集，它由一個或多個列以及0行或多行結(jié)果組成。3、(NOT)BETWEEN AND測試包含邊界兩端的值。4、(NOT)IN 用于取值有限，多者取其一。5、(NOT)LIKE 模式匹配查詢，“%”代表0個或多個字符序列“”代表一個字符；若查詢的串中含有“%”或“”，則用轉(zhuǎn)義字符表示。例：LIKE 15#% ESCAPE #和 LIKE 15% ESCAPE 都可查詢含“15

11、%”的字符串。6、IS NULL（IS NOT NULL）用于查詢是否為空。 空值：指值不確定或不能使用的數(shù)據(jù)列。 兩個不確定的值是不能比較是否相等的。7、 *可以代替所有列名，但它只可用于關(guān)系表比較穩(wěn)定的情況下，否則當(dāng)表發(fā)生變化時將會給修改帶來麻煩。8、在算術(shù)表達式中引用的列必須都為數(shù)值類型的。列名一般與檢索的表中相應(yīng)列名相同，但也有的為位置名（如col3）或用AS子句來為列名命名。9、ORDER BY 子句允許檢索得到的行基于任一列或列的組合以升序或降序排列,而不考慮這些列是否出現(xiàn)在結(jié)果中,但是,有的版本堅持ORDER BY 元素需要出現(xiàn)在SELECT列表中,但不管是哪種情況, ORDER

12、 BY 子句必須是SELECT語句的最后一個子句.10、聚合函數(shù)在表中的一個列上操作，并且返回單一值。COUNT、MAX、MIN可以用到數(shù)值和非數(shù)值型字段上，但SUM、AVG只能用到數(shù)值型字段上。除COUNT（*）外，每個函數(shù)都首先忽略NULL值，然后再對剩下的非空值進行操作，而COUNT（*）是COUNT的特殊用法，它統(tǒng)計表中的所有行，不管這些行是否是空值或是否出現(xiàn)重復(fù)值。 去掉重復(fù)值DISTINCT對于MAX和MIN來說沒有作用，但對于SUM和AVG有影響。但要注意在一個查詢中DISTINCT只能指定一次。11、特別要注意的是，聚合函數(shù)只能在SELECT列表和HAVING子句中使用。在任何

13、別的地方使用都是不正確的。如果SELECT列表中包含了聚合函數(shù)，并且沒有使用GROUP BY子句，那么SELECT列表中的項不能包含對任何列的引用，除非該列是聚合函數(shù)的參數(shù) 也就是說SELECT列表中的項要么全為聚合函數(shù)，要么全不是聚合函數(shù)，或含有GROUP BY參數(shù)。SELECT NO，COUNT（STY） FROM SSS （錯誤）SELECT NO，COUNT（STY） FROM SSS GROUP BY NO12、一個包含了GROUP BY子句的查詢被稱為分組查詢，因為它將SELECT表中的數(shù)據(jù)進行分組并為每個分組產(chǎn)生了一個概要行。在GROPR BY子句中的列被稱為分組列。ISO標準要

14、求SELECT子句和GROUP BY子句緊密結(jié)合在一起。當(dāng)使用了GROUP BY子句后，SELECT列表中的每一項在每個分組中必須都是單值的，而且，SELECT子句只能包含：列名、聚合 函數(shù)、常量、一個包含上述各項的表達式。在SELECT列表中的所有列名都必須出現(xiàn)在GROUP BY子句中，除非該名稱只在聚合函數(shù)中使用。反之則不然，在GROUP BY子句中出現(xiàn)的列名則不一定出現(xiàn)在SELECT列表中。當(dāng)WHERE子句和GROUP BY 子句同時使用時，WHERE子句先被應(yīng)用，然后在剩下的滿足條件的行之上進行分組。函數(shù)名功能AVG計算一個數(shù)值型列的平均值COUNT計算指定列中選擇的項數(shù)，COUNT(

15、*)統(tǒng)計查詢輸出的行數(shù)MIN計算指定列中的最小值MAX計算指定列中的最大值SUM計算指定列中的數(shù)值總和STDEV計算統(tǒng)計標準偏差VAR統(tǒng)計方差4. 使用聚合函數(shù)Student(Sno,SName,SSex,Age,Class) Score(Sno,Cno,Degree)例1：計算班級編號為020104的學(xué)生數(shù)select count(*) from student where class = 020104例2：計算最高分和最低分select max(degree),min(degree) from score例3：計算最高分和最低分之差select max(degree)-min(degree

16、) from score常用的聚合函數(shù)列表例4：計算學(xué)號是2002010401的學(xué)生的平均分select avg(degree) from score where sno = 20020104015. 表的連接查詢在一個查詢中，當(dāng)需要對兩個或多個表連接時，可以指定連接列，在WHERE子句中給出連接條件，在FROM子句中指定要連接的表，格式：SELECT 列名1， FROM 表1，WHERE 連接條件Student(Sno,SName,SSex,Age)Score(Sno,Cno,Degree)例1： 查詢課程編號是數(shù)學(xué)課程的平均分select avg(degree) from score, c

17、ourse where o = o and ame = 數(shù)學(xué)例2：查詢比學(xué)生2002010401的6-166號課程成績高的記錄select s2.* from score s1, score s2 where s1.sno = 2002010401 and o = 6-166 and s2.degrees1.degree6. 子查詢子查詢可以使用幾個簡單命令構(gòu)造功能強大的復(fù)合命令。子查詢最常用于SQL命令的WHERE子句中。Student(Sno,SName,SSex,Age)Score(Sno,Cno,Degree)例1：查詢比學(xué)生2002010401的6-166號課程成績高的所有記錄sel

18、ect * from score where degree (select degree from score where sno = 2002010401 and cno = 6-166 )例2：查詢存在有85分以上成績的課程 cnoselect distinct cno from score where degree in (select degree from score where degree85)例3： 查詢“張旭”老師任課的學(xué)生成績，并按成績遞增排列select cno,sno,degree from score where cno=(select o from course x

19、,teacher y where x.tno=y.tno and y.tname=“張旭”) order by degree desc例4：查詢選修某課程的學(xué)生人數(shù)多于5人的教師姓名select tname from teacher where tno in(select x.tno from course x,score y where o=o group by x.tno having count(x.tno)5)說明：SQL命令還提供了UNION子句，它可以將多個SQL命令連接起來生成單個SQL無法做到的結(jié)果集合。例：查詢所有教師和學(xué)生的tname、tsext和tbirthday.Sel

20、ect tname as “姓名”, tsext as “性別”,tbirthday as “出生日期” from teacher UNION Select sname ，ssext,sbirthday from student例5： 查詢選修編號為“3-105”的課程，且成績高于所有編號為“3-245”成績低的學(xué)生的cno,sno,degreeSelect cno,sno,degree from score where cno=“3-105” and degree all(select degree from score where cno = “3-245”)Order by degree

21、 desc注意事項1、使用別名的格式格式1：別名 AS 字段名稱格式2：字段名稱 別名格式3：別名=字段名稱 （其中的別名用單引號括起來）2、ntext、text、image三種類型的數(shù)據(jù)不能用作ORDER BY排序的字段。3、SELECT 列表中可以直接出現(xiàn)字符串，它將直接顯示在結(jié)果中。如： SELECT 學(xué)號，總成績，sum(成績) FROM STUDENT對比: SELECT 學(xué)號,sum(成績) AS 總成績, FROM STUDENT4、GETDATE（）函數(shù)可以獲取當(dāng)前的日期。函數(shù) DATEPART（參數(shù)表）可以取日期時間類型數(shù)據(jù)中的各個組成成份，具體到網(wǎng)上查著用。5、要想顯示單引

22、號，必須用兩個單引號表示如：SET XX=this is a string6、CHAR函數(shù)可以將一個整數(shù)轉(zhuǎn)換成ASCII碼如：在字符串中用CHAR（13）和CHAR（10）產(chǎn)生一個回車并生成一個新行, PRINT 用于打印字符串,將其顯示在屏幕上。PRINT 第一行 + CHAR（13）+ CHAR（10）+ 第二行 執(zhí)行結(jié)果為：第一行第二行7、日期時間類型數(shù)據(jù)為datetime(精確到3秒)和smalldatetime (精確到精確到分鐘)如：SELECT CAST(2010-6-1 12:35:29.988 AS smalldatetime)結(jié)果顯示：2010-6-1 12:35:00 （

23、1）CAST函數(shù)是將某種數(shù)據(jù)類型的表達式轉(zhuǎn)換為另一種數(shù)據(jù)類型（AS前面的類型轉(zhuǎn)為AS后面的類型）。 8、SQL SERVER 支持邏輯數(shù)據(jù)類型bit，它可以存儲整型數(shù)據(jù)1、0、NULL。如果輸入0以外的其它值時， SQL SERVER 均把它著1。4.3 TSQL程序設(shè)計基礎(chǔ) 4.3.1 標識符1. 常規(guī)標識符常規(guī)標識符必須符合標識符的格式規(guī)則。在TSQL語句中使用常規(guī)標識符時不用將其分隔。在SQL Server 2005中，常規(guī)標識符的格式規(guī)則取決于數(shù)據(jù)庫的兼容級別，兼容級別可以用存儲過程sp_dbcmptlevel來設(shè)置。當(dāng)兼容級別為80時，規(guī)則如下： 第一個字符必須是Unicode 2.

24、0標準所定義的字母、下劃線、 、#。 后續(xù)字符可以是Unicode 2.0所定義的字母、來自基本拉丁字母或其他國家地區(qū)腳本的十進制數(shù)字、at符號（）、美元符號（$）、數(shù)字符號（#）或下劃線（_）。 標識符不能是TSQL的保留字。SQL Server保留其保留字的大寫和小寫形式。3. 使用標識符在SQL Server 2005中，一個對象的全稱語法格式為：server.database.owner.object可用的簡寫格式包含下面幾種：server.database.objectserver.owner.objectserverobjectdatabase.ownerobjectdatabas

25、e.objectowner.objectobject在上面的簡寫格式中，沒有指明的部分使用如下的默認設(shè)置值：服務(wù)器（本地服務(wù)器），數(shù)據(jù)庫（當(dāng)前數(shù)據(jù)庫），所有者（在指定的數(shù)據(jù)庫中與當(dāng)前連接會話的登錄標識相對應(yīng)的數(shù)據(jù)庫用戶或數(shù)據(jù)庫所有者）例如：一個用戶名為bookadm的用戶登錄到myserver服務(wù)器上，并使用book數(shù)據(jù)庫，使用下述語句創(chuàng)建了一個mytable表。Creat table mytable( column1 int, column2 char(20) )則:表mytable的全稱就是：myserver.book.bookadm.mytable4.3.2 數(shù)據(jù)類型在SQL Serve

26、r 2005中，以下對象可以具有數(shù)據(jù)類型： 表和視圖中的列。 存儲過程中的參數(shù)。 變量。 返回一個或多個特定數(shù)據(jù)類型數(shù)據(jù)值的TSQL函數(shù)。 具有一個返回代碼的存儲過程 （返回代碼總是具有integer數(shù)據(jù)類型）。指定對象的數(shù)據(jù)類型定義了該對象的4個特性： 對象所含的數(shù)據(jù)類型。 所存儲值的長度或它的大小。 數(shù)字精度（僅用于數(shù)字數(shù)據(jù)類型）。 數(shù)值小數(shù)位數(shù)（僅用于數(shù)字數(shù)據(jù)類型）。分類數(shù)據(jù)類型定義符整數(shù)型Bigint（8），int（4），smallint（2），tinyint（1）邏輯數(shù)值型Bit(1或2字節(jié)，不可建索引)小數(shù)數(shù)據(jù)類型decimal，numeric（長度見P147表9.3）貨幣型Mon

27、ey（8），smallmoney（4）（前面加￥符號）近似數(shù)值型float，real （長度見P148表9.4）字符型char，varchar，text （用“”括起來的字符串）見P148、9Unicode字符型Nchar，nvarchar，ntext二進制數(shù)據(jù)類型Binary（最多8KB），varbinary （最多8KB） ，image日期時間類型datetime，smalldatetime（格式見P152-153）其他數(shù)據(jù)類型cursor，sal_variant，table，timestamp，uniqueidentifier 見P155SQL Server 2005提供的系統(tǒng)數(shù)據(jù)類型

28、4.3.3 運算符SQL Server的運算符有算術(shù)運算符、賦值運算符、按位運算符、比較運算符、邏輯運算符、字符串連接運算符和一元運算符。1. 算術(shù)運算符算術(shù)運算符包括+、-、*、/和 %（取模） %（取模,結(jié)果符號與被除數(shù)據(jù)的符號相同）2. 賦值運算符TSQL有一個賦值運算符，即等號（=）。3. 按位運算符按位運算符包括&（按位與）、|（按位或）和（按位異或）。4. 比較運算符SQL Server提供的比較運算符有下面幾種： 、 、 = 、 = 、 != 、 、 !5. 邏輯運算符SQL Server總共提供了10個邏輯運算符，如表所示。邏輯運算符含義ALL當(dāng)一組比較關(guān)系的值都為TRUE時，

29、才返回TRUEAND當(dāng)要比較的兩個布爾表達式的值都為TRUE，才返回TRUEANY只要一組比較關(guān)系中有一個值為TRUE，就返回TRUEBETWEEN只有操作數(shù)在定義的范圍內(nèi)，才返回TRUE（含兩個端點）EXISTS如果在子查詢中存在，就返回TRUEIN如果操作數(shù)在所給的列表表達式中，則返回TRUELIKE如果操作數(shù)與模式相匹配，則返回TRUE(下頁有例子)（常使用通配符）NOT對所有其他的布爾運算取反OR只要比較的兩個表達式有一個為TRUE，就返回TRUESOME如果一組比較關(guān)系中有一些為TRUE，則返回TRUE說明：SQL Server中通配符及其含義% 包含0個或更多個字符的任意字符串 任

30、何單個字符 指定范圍（a-f或集合（abcdef）中的任何單個字符） 不屬于指定范圍（a-f或集合（abcdef）的任何單個字符）例：利用like運算符查找相匹配的記錄select * from testdb.student where sname like %明%select * from testdb.student where sname like 王%select * from testdb.student where sname like _明 select * from testdb.student where sname like 王,李% select * from testd

31、b.student where sname like 王,李% 6. 字符串連接運算符字符串連接運算符為加號（+）。7. 一元運算符一元操作符包含+（正）、-（負）和（按位取反）。測試字符串連接運算符select 姓名:+sname+,性別+ssex+,年齡+cast(age as char) from testdb.student where sname like _明8. 在SQL Server中，運算符的優(yōu)先級如下： +（正）、-（負）、（ 按位取反） *（乘）、/（除）、%（模） +（加）、+（連接）、-（減） =、=、=、!=、!和! 80begin print 課程：3-105 p

32、rint 考試成績還不錯endelsebegin print 課程：3-105 print 考試成績一般end;3. CASE語句使用CASE語句可以進行多個分支的選擇。CASE具有兩種格式： 簡單CASE格式 搜索CASE格式（1）簡單CASE格式,其語法格式如下：CASE input_expression WHEN when_expression THEN result_expressionn ELSE else_result_expressionEND例如：use schoolselect tname as 姓名, depart as 單位, case prof when 教授 then

33、 高級職稱 when 副教授 then 高級職稱 when 講師 then 中級職稱 /*when 助教 then 初級職稱*/ ELSE 無職稱 end as 職稱from teacher （2）搜索CASE格式,其語法格式如下：CASE WHEN Boolean_expression THEN result_expression n ELSE else_result_expressionEND例如：use testdbselect sno as 姓名 , cno as 單位, case when degree=85 then A when degree=75 then B when deg

34、ree=60 then C /*when degree60 then E */ else E end as 成績from score 4. WHILE語句WHILE語句可以設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的條件。可以使用BREAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語句的執(zhí)行。其語法格式如下：WHILE Boolean_expression sql語句|語句塊BREAKsql語句|語句塊CONTINUE例如：使用while循環(huán)求1+2+.+100declare s int,i intset i = 0set s = 0while i = 100 begin set s = s

35、+ i set i = i + 1 endprint 1+2+.+100= + cast(s as char(25)例如：使用goto語句求1+2+.+100declare s int,i intset i = 0set s = 0my_loop: set s = s + i set i = i + 1if i #1/1/96# AND OrderDate#96-1-1#也可以表示為:WHEREOrderDateDatevalue(1/1/96) 2、使用 NOT 表達式求反。例：查看96年1月1日以后的定單WHERE Not OrderDate=#1/1/96# 3、IN 運算符用來匹配列表

36、中的任何一個值。IN子句可以代替用OR子句連接的一連串的條件。例：要找出住在 London、Paris或Berlin的所有客戶SELECT CustomerID, City FROMCustomers WHERE City In(London, Paris, Berlin) 4、SUBSTRING（字符串，開始位置，長度）例：SELECT SUBSTRING（City,3,4）FROMCustomers 三、功能查詢所謂功能查詢，實際上是一種操作查詢，它可以對數(shù)據(jù)庫進行快速高效的操作。它以選擇查詢?yōu)槟康?，挑選出符合條件的數(shù)據(jù)，再對數(shù)據(jù)進行批處理。功能查詢包括更新查詢，刪除查詢，添加查詢和生成表

37、查詢。功能查詢是一個SELECT查詢（也就是子查詢）它返回單個值且嵌套在SELECT、INSERT、UPDATE、DELETE語句或其它子查詢中。任何允許使用表達式的地方都可以使用子查詢。如果某個表只出現(xiàn)在子查詢中而不出現(xiàn)在外部查詢中，那么該表中的列就無法包含中輸出中。子查詢最多允許嵌套32層。1、更新查詢UPDATE子句可以同時更改一個或多個表中的數(shù)據(jù)。它也可以同時更改多個字段的值。語法：UPDATE 表名 SET新值 WHERE 準則例：英國客戶的定貨量增加5%，貨運量增加3%UPDATEOEDERSSETOrderAmount = OrderAmount *1.1Freight = Fr

38、eight*1.03 WHEREShipCountry = UK2、刪除查詢DELETE子句可以使用戶刪除大量的過時的或冗于的數(shù)據(jù)。刪除查詢的對象是整個記錄。語法：DELETE表名.* FROM來源表 WHERE準則例：要刪除所有94年前的定單DELETE * FROMOrders WHERE OrderData#94-1-1#3、追加查詢INSERT子句可以將一個或一組記錄追加到一個或多個表的尾部。INTO 子句指定接受新記錄的表valueS 關(guān)鍵字指定新記錄所包含的數(shù)據(jù)值。語法：INSETR INTO 目的表或查詢(字段1,字段2,)valueS(數(shù)值1,數(shù)值2,)例：增加一個客戶INSE

39、RT INTO Employees(FirstName,LastName,title)valueS(Harry,Washington,Trainee) 4、生成表查詢可以一次性地把所有滿足條件的記錄拷貝到一張新表中。通常制作記錄的備份或副本或作為報表的基礎(chǔ)。語法：SELECT 字段1,字段2, INTO新表IN外部數(shù)據(jù)庫 FROM來源數(shù)據(jù)庫 WHERE準則例：為定單制作一個存檔備份SELECT * INTOOrdersArchive FROM Orders 四、表達式的值與子查詢1、表達式的值與子查詢返回的單一值做比較語法：表達式 comparisionANY|ALL|SOME(子查詢)說明：

40、ANY和SOME謂詞是同義詞，與比較運算符(=,=)一起使用。返回一個布爾值True或False。ANY的意思是，表達式與子查詢返回的一系列的值逐一比較，只要其中的一次比較產(chǎn)生True結(jié)果，ANY測試的返回 True值(既WHERE子句的結(jié)果)，對應(yīng)于該表達式的當(dāng)前記錄將進入主查詢的結(jié)果中。ALL測試則要求表達式與子查詢返回的一系列的值的比較都產(chǎn)生True結(jié)果，才回返回True值。例：主查詢返回單價比任何一個折扣大于等于25%的產(chǎn)品的單價要高的所有產(chǎn)品。SELECT * FROM Products WHERE UnitPriceANY(SELECT UnitPrice FROMOrder De

41、tails WHERE Discount0.25)2 、檢查表達式的值是否匹配子查詢返回的一組值的某個值語法：NOTIN(子查詢)例：返回庫存價值大于等于1000的產(chǎn)品。SELECT ProductName FROM ProductsWHERE ProductID IN (SELECT PrdoctID FROM Order DEtails WHERE UnitPrice*Quantity= 1000)3、檢測子查詢是否返回任何記錄語法：NOTEXISTS (子查詢)例：用EXISTS檢索英國的客戶SELECT ComPanyName,ContactName FROM OrdersWHERE

42、EXISTS(SELECT * FROMCustomers WHERECountry = UK ANDCustomers.CustomerID= Orders.CustomerID) 說 明1、視圖、存儲過程：都是建在數(shù)據(jù)庫上的。2、觸發(fā)器、索引：都是建在表上。3、批處理、事務(wù)：單獨建立。4、游標：單獨建立、單獨使用，專用于記錄集操作。5、觸發(fā)器、批處理、事務(wù)：都可以看著是存儲過程內(nèi)容的一部分。6、索引：一般只在主鍵上建立聚集索引，記錄越多索引的優(yōu)越性越高。4. 4索引 索引是為了加速檢索的速度而創(chuàng)建的一種存儲結(jié)構(gòu),索引是針對一個表而建立的。通常是在主鍵上或外建上建索引，依據(jù)索引的順序和數(shù)據(jù)庫

43、的物理存儲順序是否相同而將索引分為兩類：聚集索引和非聚集索引。且都是使用的B-樹索引。在帶有索引的表中，數(shù)據(jù)存儲在一個地方，而索引存儲在另一個地方，索引帶有指針指向數(shù)據(jù)的存儲位置，索引中的項目按索引鍵值的順序存儲，而表中的信息按另一種順序存儲（由聚集索引規(guī)定）。如果沒有聚集索引，則無法保證這些行具有特定的順序。1、聚集索引：保證數(shù)據(jù)庫表中記錄的物理順序與索引順序相同，一個表中只能有一個聚集索引。2、非聚集索引：數(shù)據(jù)庫表中記錄的物理順序與索引順序可以不相同，一個表中可以有多個非聚集索引。3、唯一索引：表示表中任何兩條記錄的索引值都不相同，它可以確保索引列不包含重復(fù)值。4、組合索引：是將兩個或多個

44、字段組合起來的索引，而單獨的字段允許不是唯一的值。5、創(chuàng)建索引：在表上單擊右鍵，“索引”-“新建索引”按照需要設(shè)計選項，即可創(chuàng)建索引。說明：一般情況下，聚集索引在起作用?？梢酝ㄟ^向表中插入新記錄來進行驗證。4. 5批處理批處理是包含一個或多個SQL語句的組，從應(yīng)用程序一次性地發(fā)送到SQL SERVER執(zhí)行。一個批處理語句以GO結(jié)束。1、建立批處理時應(yīng)遵循的規(guī)則：1）所有批處理都必須以CREAT 語句開頭。并且CREAT DEFAUTL、 CREAT PROCEDURE、 CREAT RULE、 CREAT TRIGGER、 CREAT VIEW等語句不能在批處理中與其它語句組合使用。2）不能在

45、同一個批處理中更改表，然后引用新列。3）如果EXECUTE語句是批處理中的第一句，則不需要EXECUTE關(guān)鍵字，否則必須EXECUTE作關(guān)鍵字。2、 USE SUN （舉例） GO CREATE VIEW SUN0 AS SELECT * FROM 學(xué)生 GO SELECT * FROM SUN0 GO4. 6事務(wù)處理事務(wù)是SQL SERVER中的單個邏輯單元，一個事務(wù)內(nèi)的所有SQL語句作為一個整體，要么全都執(zhí)行，要么全都不執(zhí)行。一個邏輯單元必須有4個屬性：原子性、一致性、隔離性、持久性。1、事務(wù)的分類：顯式事務(wù)（用戶指定的事務(wù)）、 隱性事務(wù)、自動提交事務(wù)（默認提交方式）。2、啟動事務(wù)：使用B

46、EGIN TRANSACTION語句，該語句將全局變量TRANCOUNT加1。3、結(jié)束事務(wù)：如果沒有遇到錯誤，可使用COMMIT TRANSACTION語句成功地結(jié)束事務(wù)。4、回滾事務(wù)：如果事務(wù)中出現(xiàn)錯誤，或用戶決定取消事務(wù)，可以通過ROLLBACK語句回滾事務(wù)。如果回滾到事務(wù)起始位置，則全局變量TRANCOUNT減1。5、設(shè)置保存點：可以使用SAVE TRANSACTION語句設(shè)置保存點，可使ROLLBACK只回滾到保存點為此。例：USE SUNGOBEGIN TRANSACTION ABC *啟動事務(wù)INSERT INTO STUD VALUES（值1） *插入值1記錄SAVE TRANS

47、ACTION ABC *設(shè)置保存點INSERT INTO STUD VALUES（值2） *插入值2記錄ROLLBACK TRANSACTION ABC *回滾事務(wù)COMMIT TRANSACTION *結(jié)束事務(wù)GOSELECT * FROM STUD（可以發(fā)現(xiàn)只有值1,而沒有值2 ）GO刪除“值2”記錄GO4. 7 游標 游標是映射查詢得到的結(jié)果集并在結(jié)果集內(nèi)的單個行上建立一個位置的實體。利用游標用戶就可以訪問結(jié)果集中的任意一行數(shù)據(jù)了。1、建立游標：SQL語句、API應(yīng)用程序接口（均在服務(wù)器端實現(xiàn)）；客戶端游標（由SQL SERVER ODBC驅(qū)動程序、DB-LIBRARY DLL和實現(xiàn)AD

48、O API的DLL在內(nèi)部實現(xiàn)）。2、 SQL SERVER 支持4種API服務(wù)器游標：靜態(tài)游標、動態(tài)游標、只進游標、鍵集驅(qū)動游標。3、聲明游標：DECLARE 游標名 CURSOR FOR SELECT語句例：USE STUDENT DECLARE ABC CURSOR FOR SELECT * FROM TEACHER WHERE XB=14、打開游標：OPEN 游標名例： USE STUDENT DECLARE ABC CURSOR FOR SELECT * FROM TEACHER WHERE XB=1 OPEN ABC GO5、讀取游標數(shù)據(jù)： FETCH NEXT | FIRST |

49、LAST |N FROM 游標名例：USE STUDENT DECLARE ABC SCROLL CURSOR FOR SELECT * FROM TEACHER WHERE XB=1 OPEN ABC FETCH NEXT FROM ABC GO說明：若要用FETCH LAST讀取最后一條記錄要使用SCROLL5、關(guān)閉游標：CLOSE 游標名6、刪除游標：DEALLOCATE 游標名說明：關(guān)閉游標后不可以創(chuàng)建同名游標；刪除游標后可以創(chuàng)建同名游標。4. 8存儲過程在使用SQL SERVER創(chuàng)建應(yīng)用程序時，SQL編程語言是應(yīng)用程序和SQL SERVER數(shù)據(jù)庫之間的主要編程接口。使用SQL程序時，

50、可用兩種方法存儲和執(zhí)行程序。1）在本地存儲程序，并創(chuàng)建向SQL SERVER發(fā)送命令并處理結(jié)果的應(yīng)用程序。2）在SQL SERVER中將程序存儲為存儲過程，創(chuàng)建執(zhí)行存儲過程及處理結(jié)果的應(yīng)用程序。說明：任何一組SQL語句構(gòu)成的代碼塊都可以作為存儲過程保存起來。1、創(chuàng)建存儲過程：使用命令。命令：CREATE PROCEDURE 過程名 參數(shù)名及類型列表 AS 一組SQL語句例1：USE STUDENT GO CREATE PROCEDURE AB (無參) AS UPDATA TEACHER SET GZ=GZ*1.2 例2：USE STUDENT GO CREATE PROCEDURE AB s

51、s SMALLINT(有參) AS UPDATA TEACHER SET GZ=GZ*(1+ss/100)例3：如果要通過SQL命令定義一個存儲過程查詢XSCJ數(shù)據(jù)庫中每個同學(xué)各門功課的成績，然后調(diào)用該存儲過程步驟如下：定義如下存儲過程CREATE PROCEDURE student_grade AS SELECT XS.學(xué)號,XS.姓名,KC.課程名, XS_KC.成績FROM XS,XS_KC,KCWHERE XS.學(xué)號=XS_KC.學(xué)號 AND XS_KC.課程號=KC.課程號GoEXEC student_grade 調(diào)用該存儲過程GO2、執(zhí)行存儲過程：EXEC 過程名 參數(shù)值例：USE

52、 STUDENT EXEC AB SS=20 GO (1) 設(shè)計簡單的存儲過程例1：從XSCJ數(shù)據(jù)庫的三個表中查詢，返回學(xué)生學(xué)號、姓名、課程名、成績、學(xué)分。該存儲過程不使用任何參數(shù)。USE XSCJ/*檢查是否已存在同名的存儲過程，若有，刪除。*/IF EXISTS (SELECT name FROM sysobjects WHERE name = student_info AND type = P) DROP PROCEDURE student_infoGO/*創(chuàng)建存儲過程*/CREATE PROCEDURE student_infoASSELECT a.學(xué)號, 姓名, 課程名, 成績, 學(xué)

53、分 FROM XS a INNER JOIN XS_KC b ON a.學(xué)號 = b.學(xué)號 INNER JOIN KC t ON b.課程號= t.課程號 GO3、存儲過程應(yīng)用舉例(2) 使用帶參數(shù)的存儲過程例2：從XSCJ數(shù)據(jù)庫的三個表中查詢某人指定課程的成績和學(xué)分。該存儲過程接受與傳遞參數(shù)精確匹配的值。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = student_info1 AND type = P) DROP PROCEDURE student_info1GOCREATE PROCEDURE student_inf

54、o1 name char (8),cname char(16) AS SELECT a.學(xué)號, 姓名, 課程名, 成績, 學(xué)分 FROM XS a INNER JOIN XS_KC b ON a.學(xué)號 = b.學(xué)號 INNER JOIN KC t ON b.課程號= t.課程號 WHERE a.姓名=name and t.課程名=cnameGO(3) 使用帶有通配符參數(shù)的存儲過程例3：從三個表的聯(lián)接中返回指定學(xué)生的學(xué)號、姓名、所選課程名稱及該課程的成績。該存儲過程在參數(shù)中使用了模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認值。USE XSCJIF EXISTS (SELECT name FROM

55、 sysobjects WHERE name = st_info AND type = P) DROP PROCEDURE st_infoGOCREATE PROCEDURE st_infoname varchar(30) = 劉% - - 定義變量AS SELECT a.學(xué)號,a.姓名,c.課程名,b.成績FROM XS a INNER JOIN XS_KC b ON a.學(xué)號 =b.學(xué)號 INNER JOIN KC c ON c.課程號= b.課程號 WHERE 姓名 LIKE nameGO (4) 使用帶OUTPUT參數(shù)的存儲過程例4：用于計算指定學(xué)生的總學(xué)分，存儲過程中使用了一個輸入?yún)?/p>

56、數(shù)和一個輸出參數(shù)。USE XSCJGOIF EXISTS(SELECT name FROM sysobjects WHERE name = totalcredit AND type = P)DROP PROCEDURE totalcreditGOUSE XSCJGOCREATE PROCEDURE totalcredit name varchar(40), total int OUTPUTASSELECT total= SUM(學(xué)分)FROM XS,XS_KC,KCWHERE 姓名=name AND XS.學(xué)號= XS_KC.學(xué)號 GROUP BY XS.學(xué)號GO(5) 使用OUTPUT游標參

57、數(shù)的存儲過程OUTPUT 游標參數(shù)用于返回存儲過程的局部游標。例5：在 XSCJ數(shù)據(jù)庫的XS表上聲明并打開一個游標。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = st_cursor and type = P)DROP PROCEDURE st_cursorGOCREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUTASSET st_cursor = CURSOR FORWARD_ONLY STATIC FORSELECT * FROM XSOPEN st_cur

58、sorGO (6) 使用 WITH ENCRYPTION 選項（了解）WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。例6：創(chuàng)建加密過程，使用 sp_helptext 系統(tǒng)存儲過程獲取關(guān)于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息。IF EXISTS (SELECT name FROM sysobjects WHERE name = encrypt_this AND type = P) DROP PROCEDURE encrypt_thisGOUSE XSCJGOCREATE PROCEDURE encrypt_this WITH ENCRY

59、PTIONASSELECT * FROM XSGO(7) 創(chuàng)建用戶定義的系統(tǒng)存儲過程（了解）例7：創(chuàng)建一個過程，顯示表名以xs開頭的所有表及其對應(yīng)的索引。如果沒有指定參數(shù)，該過程將返回表名以kc開頭的所有表及對應(yīng)的索引。IF EXISTS (SELECT name FROM sysobjectsWHERE name = sp_showtable AND type = P) DROP PROCEDURE sp_showtableGOUSE masterGOCREATE PROCEDURE sp_showtable TABLE varchar(30) = kc%AS SELECT AS TABLE

60、_NAME, AS INDEX_NAME, indid AS INDEX_ID FROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE LIKE TABLEGO USE XSCJEXEC sp_showtable xs%GO4、刪除存儲過程：DROP 過程名 例：DROP AB GO4. 9 觸發(fā)器觸發(fā)器是一種特殊的存儲過程，它在指定的表中的數(shù)據(jù)發(fā)生變化時自動生效。它與普通存儲過程的不同之處在于：普通存儲過程是由命令調(diào)用執(zhí)行的，而觸發(fā)器是由事件觸發(fā)的。1、觸發(fā)器的分類：DML觸發(fā)器和DDL觸發(fā)器（2005有）