1、第4章 T-SQL與可編程對象4.1 T-SQL4.2 視圖4.3 存儲過程4.4 觸發器4.1 T-SQL4.1.1 T-SQL概述 SQL：結構化查詢語言（Structure Query Language）關系型數據庫管理系統的標準語言：按照ANSI（美國國家標準協會）的規定,最初由IBM公司在七十年代中期開發成功。T-SQL語言是微軟公司在SQL Server中對SQL擴展。1. T-SQL語言的特點 1）非過程化 面向過程的程序設計語言，必須一步一步地將解題的全過程描述出來，不僅要告訴計算機“做什么”，而且還要告訴計算機“怎么做”。非過程化語言，不必描述解決問題的全過程，只需提出“做什

2、么”，至于“如何做”的細節則由語言系統本身去完成并給出操作的結果。2）兩種不同的使用方式 聯機交互方式: SQL語言既可獨立使用嵌入程序設計語言中:例如C#程序設計語言中。不論使用何種方式，T-SQL語言的語法結構基本相同。 3) 高度一體化集數據定義語言（DDL）、數據操縱語言（DML）、數據控制語言（DCL）和T-SQL增加的語言元素于一體，可獨立完成數據庫生命周期的所有活動。 4) 語言簡潔、易學易用2. T-SQL語言的組成元素數據定義語言DDL(Data Definition Language)定義數據庫結構、各對象及屬性的語句例如：Creat DATABASE Student數據庫

3、操縱語言DML(Data Manipulation Language)實現對數據庫基本操作的語句，主要包括查詢、插入、修改、刪除等數據庫控制語言DCL(Data Control Language)保證數據庫一致性和完整性T-SQL增加的語言元素 變量、運算符、函數、流程控制語句和注解等。 3. T-SQL書寫規則在T-SQL語句中，不區分字母大小寫。為閱讀方便，約定: 用大寫字母書寫T-SQL語句中關鍵字；(2) 用小寫字母書寫語句中的標識符、表達式以及各種參數。4. 注釋單行注釋 多行注釋 /*/4.1.2 數據定義語言DDL 數據定義語言用于定義數據庫中的對象，包括數據庫、表、視圖、索引、

4、存儲過程、觸發器等的建立或修改。 操作對象操作方式創建刪除修改使用數據庫CREATE DATABASEDROP DATABASEALTER DATABASEUSE 基本表CREATE TABLEDROP TABLEALTER TABLE視圖 CREATE VIEWDROP VIEWALTER VIEW索引CREATE INDEXDROP INDEX存儲過程CREATE PROCEDUREDROP PROCEDUREALTER PROCEDURE觸發器CREATE TRIGGERDROP TRIGGERALTER TRIGGER表4-1 SQL數據定義語言DDL4.1.2 數據定義語言DDL2.

5、 使用SSMS生成SQL腳本右擊要生成腳本的數據庫對象，在右鍵菜單中選擇“編寫腳本”-“Create到”-“新查詢編輯窗口”在“查詢編輯”窗口中可查看、復制、保存腳本使用“文件”-“打開”可打開腳本文件1)創建一個數據庫SchoolTest ；2)數據庫主數據文件SchoolTest，物理文件為“D:SchoolTest.mdf”, 大小為3MB；3)系統將自動在“D:”中創建大小為1MB、名為“SchoolTest_log.LDF”的日志文件。【例4-1】創建一個只有一個主數據文件 SchoolTest的數據庫（不指定該文件的大小） 。CREATE DATABASE SchoolTestON

6、 (NAME=SchoolTest,FILENAME=D:SchoolTest.MDF) 3常用DDL語句 (1) 創建數據庫語句（CREATE DATABASE） 4.1.2 數據定義語言DDL（2）打開和刪除數據庫語句 USE 數據庫名 /*選擇當前要操作的數據庫 */ USE SchoolTestDROP DATABASE 數據庫名/*刪除數據庫 */ USE School- 當前數據庫必須更改！DROP DATABASE SchoolTest4.1.2 數據定義語言DDL（3）建立表語句（CREATE TABLE） 【例4-2】建立學生表T_StudentTest。CREATE TAB

7、LE T_StudentTest ( StudentCode CHAR(8) NOT NULL UNIQUE, StudentName VARCHAR(16) NOT NULL, Sex CHAR(2) NOT NULL, LiveInDorm BIT DEFAULT 1, Constraint StudentPK Primary Key(StudentCode) )在School數據庫中創建一個新的空表T_StudentTest。在字段的定義中確定StudentCode、StudentName、Sex不可取空值，LiveInDorm的默認值為1，并將StudentCode設定為主鍵約束。4.

8、1.2 數據定義語言DDL（4）刪除表語句（DROP TABLE）【例4-3】刪除T_StudentTest表 DROP TABLE T_StudentTest注意：刪除數據表要十分謹慎，因為當數據表被刪除時，里面的數據也被全部刪除。4.1.2 數據定義語言DDL（5）建立索引語句(CREATE INDEX) CREATE INDEX 索引名 ON 數據表名(字段 ASC|DESC)【例4-4】在Student表的姓名（StudentName）字段上建立升序索引。CREATE INDEX NameIndex on Student(StudentName) （6）刪除索引語句(DROP INDE

9、X) 4.1.3 數據操縱語言DML 數據操縱語言實現對數據的查詢和更新，主要語句及功能如下： 表4-2 SQL數據操縱語言DML命令功能SELECT從一個表或多個表查詢數據INSERT向一個表中添加一條記錄UPDATE修改表中某一個或幾個字段的值DELETE從一個表中刪除記錄查詢數據庫表中的數據并返回符合用戶查詢條件的結果數據。數據查詢語句是SQL語言的核心。Select語句由多個子句構成，其語法形式為：SELECT 字段表FROM 表名WHERE 查詢條件GROUP BY 分組字段 HAVING 分組條件ORDER BY 字段名 ASC/DESC 表示可選項。在書寫SELECT語句時，字母

10、大小寫意義完全相同。語句可以寫在若干行上（如上述語法格式中的描述），也可以不換行。1查詢語句SELECT 4.1.3 數據操縱語言DML(1)字段表 字段表位于關鍵字SELECT后面，這些字段將作為查詢的結果被顯示。使用字段表規則： 1) 字段與字段之間用逗號分隔。 【例4-5】使用SELECT語句查詢Student表中的學生姓名（StudentName）和性別（Sex）。 SELECT StudentName, Sex FROM Student 2) 可以使用通配符“ * ”表示表中的所有字段。 【例4-6】 使用SELECT語句查詢 Student表中所有字段的值。 SELECT * FR

11、OM Student 4.1.3 數據操縱語言DML3) 如果字段名或表名中含有空格，則該字段名或表名必須用方括號括起。 4) 在字段表中，可以使用以下方式將原字段名以新的字段名顯示。 字段名 AS 新字段名 【例4-7】 顯示ClassInfo表中班級代號，并將ClassCode字段改為Class Number。 SELECT ClassCode AS Class Number FROM ClassInfo 注意：字段別名Class Number含有空格，所以用方括號括起來 4.1.3 數據操縱語言DML（1）字段表5) 在字段名前可加上一些范圍限制，以便進一步優化查詢結果。常用的范圍關鍵字

12、有：TOP n ：顯示前n條記錄。TOP m PERCENT ：顯示前m%的記錄。【例4-8】查詢Course表中的前3條記錄。 SELECT TOP 3 * FROM Course 【例4-9】查詢Course表中前30%的記錄。 SELECT TOP 30 PERCENT * FROM Course 4.1.3 數據操縱語言DML（1）字段表DISTINCT：若多條記錄的字段值具有相同數據，只顯示一條記錄。 【例4-10】查詢Course表中開課的學院代碼AcademyCode（與所開課程無關）。 SELECT DISTINCT AcademyCode FROM Course 4.1.3

13、數據操縱語言DML（1）字段表6) 為了增強查詢功能，在SELECT語句中還可以使用內部聚合函數，它們被用來進行一些簡單的統計或計算。常用統計函數如下表所示。表4-13 常用聚合函數函數名函數功能AVG( ALL | DISTINCT expression )計算某一字段的平均值（此字段的值必須是數值型）COUNT( ALL | DISTINCT expression )統計某一字段的個數MAX( ALL | DISTINCT expression )查找某一字段的最大值MIN( ALL | DISTINCT expression )查找某一字段的最小值SUM( ALL | DISTINCT

14、expression )計算某一字段的總和（此字段的值必須是數值型）表4-3 常用聚合函數4.1.3 數據操縱語言DML（1）字段表【例4-11】計算學生的總分、平均分、最高分、最低分及學生選課數。 SELECT Sum (Grade) as 總分, Avg (Grade) as 平均分, Max (Grade) as 最高分, Min (Grade) as 最低分, Count (StudentCode) as 課程人數 FROM Grade 表4-13 常用聚合函數4.1.3 數據操縱語言DML（1）字段表4.1.3 數據操縱語言DML（1）字段表7）使用SELECT語句復制新表。在字段名

15、后加上INTO 表名可將查詢結果復制到一張新表中。 【例】將Student表中所有記錄的學生姓名、出生日期兩個字段復制一張新表NewTable。 SELECT StudentName, BirthDay INTO NewTable FROM Student運行后，產生一張表名為NewTable的新表。4.1.3 數據操縱語言DML（1）字段表(2) FROM子句 FROM 表名1 ，表名2，表名n 1) 在FROM子句中，表的先后次序可以任意排列。2) FROM子句中若包含多個表名，且不同的表中具有相同的字段，那么SELECT子句的字段名必須表示成 “.” 。【例4-12】查詢Student表

16、和Grade表，報告StudentCode、 StudentName、 CourseCode和Grade。SELECT StudentCode, StudentName, CourseCode, Grade FROM Student, Grade 正確語句： SELECT Student.StudentCode, StudentName, CourseCode, Grade FROM Student, Grade 4.1.3 數據操縱語言DML (3) WHERE子句 設置查詢的條件，它是一個可選的子句。在使用時，WHERE子句必須在FROM子句的后面。WHERE子句的語法格式： WHERE

17、查詢條件 查詢條件是一個邏輯表達式。常用的運算符有： 表4-14 T-SQL中常用的關系和邏輯運算查詢條件謂詞比較、=、=、=(等于)、(不等于)確定范圍BETWEEN AND、NOT BETWEEN AND確定集合IN、NOT IN字符匹配LIKE、NOT LIKE空值IS NULL、IS NOT NULL否定NOT邏輯運算AND、OR【例4-14】查詢未住校的女學生的情況。 SELECT StudentCode, StudentName, Sex, LiveInDormFROM StudentWHERE Sex=女 and LiveInDorm=0比較和邏輯運算比較運算用于比較兩個表達式的

18、值；邏輯運算用來連接多個查詢條件。【例4-13】在Student表中查詢所有女同學的姓名。SELECT StudentCode, StudentName, Sex, LiveInDormFROM StudentWHERE Sex=女4.1.3 數據操縱語言DML2)確定范圍BETWEEN ANDBETWEEN 值1 AND 值2:查詢值在值1至值2之間的記錄 NOT BETWEEN 值1 AND 值2:查詢不在指定范圍中的記錄【例4-15】在Grade表中查詢課程代號（CourseCode）為“110001”、成績（Grade）在70分到90分之間的學生代號（StudentCode）及成績。S

19、ELECT StudentCode, CourseCode, Grade FROM GradeWHERE CourseCode=110001 AND Grade BETWEEN 70 AND 90 此例的WHERE子句也可改為：WHERE CourseCode=110001 AND Grade= 70 AND Grade=90【例4-16】查詢Student表中不在1986年到1995年中出生的學生學號和姓名。SELECT StudentCode, StudentName, Birthday FROM StudentWHERE Birthday NOT BETWEEN 1986-01-01 A

20、ND 1995-12-124.1.3 數據操縱語言DML3)確定集合IN運算使用IN運算可查詢某些字段值是否包含在所列出的指定值的記錄.【例4-17】查詢Student表中班號（ClassCode）為“153030301”、“153030302” 的學生。SELECT StudentCode, StudentName, Sex, ClassCodeFROM StudentWHERE Classcode IN (153030301,153030302)此例的WHERE子句也可以改為：WHERE ClassCode=153030301 OR ClassCode=1530303024.1.3 數據操

21、縱語言DML4)字符匹配LIKE 指定字段的值是否包含在給定的字符串中，其結果是滿足字符串匹配的數據記錄。通配符“_”表示任意單個字符；“%”表示包含零個或更多字符的任意字符串。【例4-18】查詢Course表中課程名稱（CourseName）中包含“化學”兩個字的課程代號（CourseCode）及課程名稱。SELECT CourseCode,CourseName FROM CourseWHERE CourseName LIKE %化學%4.1.3 數據操縱語言DML5)聯接查詢 查詢操作中涉及多表的查詢也稱為聯接查詢。可以用兩種方法實現:在WHERE子句中設置查詢條件。 【例4-19】查詢未

22、住校學生的選課及成績情況 SELECT StudentName, CourseCode, Grade, LiveinDormFROM Grade, StudentWHERE LiveInDorm=0 AND Grade.StudentCode=Student.StudentCode4.1.3 數據操縱語言DML5)聯接查詢在FROM子句中使用聯接關鍵字將表與表聯接在一起。 T-SQL查詢嵌套在語言中使用時，多采用該方法 。語法格式： FROM 表1 聯接關鍵字 表2 ON 表1.字段名1 表2.字段名2 其中：表1、表2是被聯接的表名； 字段名是被聯接的字段。必須有相同的數據類型并包含同類數據

23、，但不需要有相同的名稱。 比較運算符：=、=、聯接關鍵字用于確定聯接的方式，常用的有INNER JOIN （內聯接）、LEFT OUTER JOIN（左外聯接）、RIGHT OUTER JOIN（右外聯接）、CROSS JOIN（交叉聯接）；4.1.3 數據操縱語言DMLINNER JOININNER JOIN：查詢結果僅包含兩個表中每對聯結匹配的行。內連接是系統默認的，可以將關鍵字INNER省略。【例4-20】使用內聯接查詢未住校學生的選課及成績情況。SELECT StudentName, CourseCode, Grade, LiveinDormFROM Student INNER JOI

24、N Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0本句的查詢結果與【例4-19】結果完全相同。4.1.3 數據操縱語言DMLLEFT OUTER JOIN左外聯接，查詢結果除了包含兩張表中符合連接條件的記錄外，還包含左表（寫在關鍵字LEFT OUTER JOIN左邊的表）中不符合聯接條件、但符合WHERE條件的全部記錄。 【例4-21】使用左外聯接查詢未住校學生的選課及成績情況。SELECT StudentName, CourseCode, Grade, LiveInDormFROM Student LEF

25、T OUTER JOIN Grade ON Student.StudentCode = Grade.StudentCodeWHERE LiveInDorm=0本句的查詢結果中有些記錄的CourseCode和Grade字段的值為NULL，也就是說這些記錄是不符合聯接條件的（即在Grade中沒有找到有相同StudentCode的記錄），但由于使用了左外聯接的查詢方法，所以左表（Student）中的符合WHERE子句條件的全部記錄顯示在查詢結果中。 4.1.3 數據操縱語言DMLRIGHT OUTER JOIN右外聯接，查詢結果除了包含兩張表中符合聯接條件的記錄，還包含右表（寫在關鍵字RIGHTT

26、OUTER JOIN右邊的表）中不符合聯接條件、但符合WHERE條件的全部記錄。 【例4-22】使用右外聯接查詢未住校學生的選課及成績情況。SELECT StudentName, CourseCode, Grade, LiveInDormFROM Student RIGHT OUTER JOIN Grade ON Student.StudentCode = Grade.StudentCodeWHERE LiveInDorm=0本句的查詢結果與使用內聯接完全相同，但含義不同。結果相同是由于在表設計時，在表Student和Grade之間通過外鍵建立了參照完整性約束，表Grade中的所有Studen

27、tCode必須是Student中出現的值。4.1.3 數據操縱語言DML【例4-23】查詢化工學院開設的課程的選修學生和考試成績。SELECT Course.CourseCode, Course.CourseName, StudentCode, AcademyName, GradeFROM Grade RIGHT JOIN Course ON Grade.CourseCode = Course.CourseCodeJOIN Academy ON Course.AcademyCode=Academy.AcademyCodeWHERE AcademyName=化工學院4.1.3 數據操縱語言DML

28、CROSS JOIN（交叉聯接）：查詢結果是將兩個表進行拼接，即第一個表的每行與第二個表的每一行進行拼接，查詢結果的行數等于兩個表行數之積。【例4-24】使用交叉聯接列出所有學生需選的所有課程情況。 SELECT StudentName, CourseCode FROM Student CROSS JOIN Course多表聯接嵌套：在多表查詢時，所涉及的數據表在3張以上，形成聯接嵌套。嵌套的格式如下： FROM 表1 JOIN 表2 ON 表1.字段1 表2.字段1 JOIN 表3 ON 表2.字段2 表3.字段2 JOIN 表n ON 表n-1.字段n 表n.字段n4.1.3 數據操縱語言

29、DML【例4-25】查詢班號是“051011”的學生姓名、所選課程名稱和成績。使用WHERE子句設置查詢條件：SELECT StudentName, CourseName, Grade FROM Student, Course,Grade WHERE Course.CourseCode = Grade.CourseCode AND Student.StudentCode = Grade.StudentCode AND ClassCode=153030301使用聯接關鍵字的語句： SELECT StudentName, CourseName, GradeFROM Student JOIN Gra

30、de ON Student.StudentCode = Grade.StudentCode JOIN Course ON Grade.CourseCode = Course.CourseCodeWHERE ClassCode=1530303014.1.3 數據操縱語言DML(3) GROUP BY子句 將指定字段中具有相同值的記錄合并成一條記錄。語法格式： GROUP BY 分組字段 HAVING 分組條件 HAVING子句與WHERE子句的作用類似：在使用GROUP BY完成分組后，顯示滿足HAVING子句中分組條件的所有記錄。 【例4-26】按住校與否統計學生的平均成績。SELECT Li

31、veInDorm, STR(AVG(Grade),5,2) AS 平均成績FROM Student JOIN GradeON Student.StudentCode=Grade.StudentCodeGROUP BY LiveInDorm4.1.3 數據操縱語言DML【例4-27】統計課程代號（CourseCode）大于“110006”的各門課程的選課人數。SELECT CourseCode, Count (StudentCode) AS 選課人數FROM GradeGROUP BY CourseCodeHAVING CourseCode 110006(4) ORDER BY子句按指定字段為查

32、詢結果排序。通常是 SQL 語句最后一項。 語法格式為： ORDER BY 字段名 ASC/DESC 字段名是排序的依據，也可以是含有字段名的表達式。 ASC為升序排序(Ascending)，DESC為降序(Descending)缺省排列次序為升序。4.1.3 數據操縱語言DML【例4-28】按成績（Grade）升序顯示Grade表中的所有數據。 SELECT * FROM Grade ORDER BY Grade可以指定多個排序的字段。多字段排序的規則是：首先用指定的第一個字段對記錄排序，然后對此字段中具有相同值的記錄用指定的第二個字段進行排序，依此類推。若無ORDER BY子句，則按原數據

33、表的次序顯示數據。【例4-29】按姓名（StudentName）升序 班名（ClassName）降序查詢學生及其班級信息。SELECT StudentCode, StudentName, ClassName, BirthdayFROM Student JOIN ClassInfoON Student.ClassCode=ClassInfo.ClassCodeORDER BY Studentname ASC, ClassName DESC4.1.3 數據操縱語言DML(5) 子查詢 當一個查詢是另一個查詢的條件時，稱該查詢為子查詢也被稱為嵌套查詢。可以構造出一條含有多個子查詢的SQL語句來完成很

34、復雜目的的查詢。 【例4-29】查詢“張三”同班同學的學號、姓名。SELECT Student.StudentCode, Student.StudentName, Student.ClassCodeFROM Student WHERE Student.ClassCode=( SELECT Student.ClassCode FROM Student WHERE Student.StudentName=張三)4.1.3 數據操縱語言DML如果子查詢結果返回多值則子查詢要與下面介紹的IN、EXIST等結合使用。 1) IN子查詢 IN子查詢用來判斷一個給定值是否在子查詢的結果集中。【例4-30】查

35、詢選修了課程代碼為“110001”的學生的學號、姓名和班級代號。SELECT StudentCode, StudentName, ClassCodeFROM Student WHERE StudentCode IN ( SELECT StudentCode FROM Grade WHERE CourseCode=110001)4.1.3 數據操縱語言DMLSELECT Student.StudentCode, StudentName, ClassCodeFROM Student JOIN Grade ON Student.StudentCode= Grade. StudentCodeWHERE

36、 Grade.CourseCode=1100012) EXISTS子查詢 EXISTS子查詢用于判斷一個子查詢的結果集是否為空，如果為空則返回TRUE，否則返回FALSE。NOT EXISTS的返回值與EXISTS相反。 【例4-31】查詢選修了課程代碼為“110001”的學生的學號、姓名和班級代號。用EXISTS子查詢實現SELECT StudentCode, StudentName, ClassCodeFROM Student WHERE EXISTS ( SELECT * FROM Grade WHERE Student.StudentCode=Grade.StudentCode AND

37、 Grade.CourseCode=110001)4.1.3 數據操縱語言DML.數據插入語句INSERT INTO 功能：在指定的表中添加一個新的記錄，或一次從其他表提取一組記錄插入到目標表中。語法格式1：在指定的表中添加一個新的記錄。INSERT INTO 數據表（字段名1，字段名2)VALUES（表達式1，表達式2）字段可以是某幾個字段。表達式1、表達式2分別對應字段名1、字段名2，它們是所要添加的記錄的值。當插入一條完整的記錄時，可省略字段名，但字段值次序要與表中字段的次序一一對應。 【例4-32】向ClassInfo表插入一條記錄。INSERT INTO ClassInfo VALU

38、ES (143030301,自動化14級301班, 303003, )4.1.3 數據操縱語言DML如果只給出部分字段的值，必須指明相應的字段名，表達式值的次序必須和所列字段名次序一一對應。【例4-33】向ClassInfo表插入一條記錄。INSERT INTO ClassInfo(ClassCode, ClassName, MajorCode) VALUES (143030302,自動化14級302班, 303003)語法格式2：從其他表提取一組記錄插入到目標表中。INSERT INTO 數據表(字段名1，字段名2) SELECT語句 【例4-34】向StudentTest表插入記錄,數據為

39、Student中所有女同學。INSERT INTO StudentTestSELECT StudentCode, StudentName, Sex, LiveInDorm From Student WHERE Sex=女4.1.3 數據操縱語言DML3. 數據更新語句UPDATE 對一條或多條符合條件記錄中進行修改 省略WHERE子句，對數據表中的所有記錄進行更新。 語法格式：UPDATE 數據表 SET 字段名1=表達式1 ,字段名2=表達式2 WHERE 【例4-35】更新Student表中StudentCode為“2015141442001”的記錄信息，將其電話字段（Telephone）

40、的值改為“67792280”。 UPDATE Student SET Telephone=67792280 WHERE StudentCode=20151414420014.1.3 數據操縱語言DML【例4-36】更新Course表中信息，將所有開課學院（AcademyCode）為“305”的課程的實驗課時（LabHour）增加10%, 上課課時(ClassHour)增加10。 UPDATE Course SET LabHour=LabHour*(1+0.1), ClassHour=ClassHour+10 WHERE AcademyCode= 3054.1.3 數據操縱語言DML4. 數據刪

41、除語句DELETE功能：刪除指定表中滿足條件的記錄。語法格式： DELETE FROM 表名 WHERE 如果沒有WHERE子句，即沒有指定刪除條件，則該語句執行后將刪除指定表中的所有記錄。 【例4-37】刪除Student表中StudentCode字段值為“08011106”的記錄。 DELETE FROM Student WHERE StudentCode=080111064.1.3 數據操縱語言DML【例4-38】刪除Student表中選課平均成績低于30的學生信息。DELETE FROM StudentWHERE StudentCode IN ( SELECT StudentCode FROM Grade GROUP BY StudentCode HAVING AVG (Grade)30) 在進行表的插入、更新和刪除時，可能會受到關系完整性的約束，這種約束可以保證數據庫中數據的正確性。4.1.3 數據操縱語言DML1. 標識符、常量和變量 （1