一、T—SQL 的組成 1、DML(數據操作語言 Data Manipulation Language) 查詢、插入、刪除和修改資料庫中的數據。SELECT、INSERT、UPDATE、DELETE 等； 2、DCL(數據控制語言 Data Control Language) 用來控制存取許可、存取 ...
一、T—SQL 的組成
1、DML(數據操作語言 Data Manipulation Language)
查詢、插入、刪除和修改資料庫中的數據。SELECT、INSERT、UPDATE、DELETE 等；
2、DCL(數據控制語言 Data Control Language)
用來控制存取許可、存取許可權等。GRANT、REVOKE 等。
3、DDL(數據定義語言 Data Definition Language)
用來建立資料庫、資料庫對象和定義其列。CREATE TABLE 、DROP TABLE 等。
4、變數說明、流程式控制制、功能函數
定義變數、判斷、分支、迴圈結構等。日期函數、數學函數、字元函數、系統函數等。
二、庫
1、建立庫
--判斷是否存在該庫,如果有則刪除
USE master --設置當前資料庫為 master,以便訪問 sysdatabases 表
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='stuDB')
DROP DATABASE stuDB
--建立資料庫
CREATE DATABASE stuDB
ON PRIMARY --預設就屬於 PRIMARY 主文件組,可省略
(
NAME='stuDB_data', --主數據文件的邏輯名
FILENAME='D:\project\stuDB_data.mdf', --主數據文件的物理名
SIZE=5mb, --主數據文件初始大小
MAXSIZE=100mb, --主數據文件增長的大值
FILEGROWTH=15% --主數據文件的增長率
)
LOG ON
(
NAME='stuDB_log',
FILENAME='D:\project\stuDB_log.ldf',
SIZE=2mb,
FILEGROWTH=1MB
)
GO
2、刪除庫
USE master --設置當前資料庫為 master,以便訪問 sysdatabases 表
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='stuDB')
DROP DATABASE stuDB
EXISTS()語句：檢測是否存在 stuDB 資料庫,如果存在 stuDB 資料庫,則刪除
sysdatabases 表在 master 資料庫中,保存著當前系統中所有的資料庫
三、表
1、建表
--判斷是否存在該表,有則刪除
USE stuDB --將當前資料庫設置為 stuDB ,以便在 stuDB 資料庫中建表
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='stuInfo' )
DROP TABLE stuInfo
--建表
CREATE TABLE stuInfo /*-創建學員信息表-*/
(
stuName VARCHAR(20) NOT NULL , --姓名,非空(必填)
stuNo CHAR(6) NOT NULL, --學號,非空(必填)
stuAge INT NOT NULL, --年齡,INT 類型預設為 4 個位元組
stuID NUMERIC(18,0), --身份證號
stuSeat SMALLINT IDENTITY (1,1), --座位號,自動編號
stuAddress TEXT --住址,允許為空,即可選輸入
)
GO
2、刪表
USE 庫名 --將當前資料庫設置為 stuDB ,以便在 stuDB 資料庫中建表
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='表名' )
DROP TABLE 表名
四、約束
1、主鍵(primary key)
ALTER TABLE stuInfo
ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)
2、唯一性(uinque)
ALTER TABLE stuInfo
ADD CONSTRAINT UQ_stuID UNIQUE (stuID)
3、預設填寫(default('……' ) for)
ALTER TABLE stuInfo
ADD CONSTRAINT DF_stuAddress
DEFAULT ('地址不詳') FOR stuAddress
4、檢查(check(……))
ALTER TABLE stuInfo
ADD CONSTRAINT CK_stuAge
CHECK(stuAge BETWEEN 15 AND 40)
5、外鍵(foreign key(列名) references 主表名(列名))
ALTER TABLE stuMarks
ADD CONSTRAINT FK_stuNo
FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
6、刪除約束
ALTER TABLE 有約束的表名 DORP 約束名
五、通配符
通配符 解釋 示例
'_' 一個字元 A Like 'C_'
% 任意長度的字元串 B Like 'CO_%'
[ ] 括弧中所指定範圍內的一個字元 C Like '9W0[1-2]'
[^] 不在括弧中所指定範圍內的一個字元 D Like '%[A-D][^1-2]'
六、插入數據
INSERT 表名(列名) VALUES (插入的列值)
insert stuinfo(stuname,stuno,stuage,stuid,stuaddress)
values('張三',001,20,100,'hello')
1、註意事項
A、每次插入一行數據,不可能只插入半行或者幾列數據,因此,插入的數據是否有效將
按照整行的完整性的要求來檢驗。
B、每個數據值的數據類型、精度和小數位數必須與相應的列匹配。
C、不能為標識列指定值,因為它的數字是自動增長的。
D、如果在設計表的時候就指定了某列不允許為空,則必須插入數據。
E、插入的數據項,要求符合檢查約束的要求。
F、具有預設值的列,可以使用 DEFAULT(預設)關鍵字來代替插入的數值。
2、插入多行數據
(1)對象表存在
INSERT INTO <表名>(列名)
SELECT <列名> FROM <源表名>
INSERT INTO stuinfobak (stuname,stuno,stuage)
SELECT stuname,stuno,stuage FROM stuinfo
(Stuinfobak 表必須在資料庫中存在)
(2)對象表不存在
SELECT (列名) INTO <表名> FROM <源表名>
SELECT stuname,stuno,stuage INTO stuinfobak1 FROM stuinfo
(Stuinfobak1 表必須在資料庫中不存在)
(3)插入新的標識列
IDENTITY(數據類型,標識種子,標識增量)
SELECT IDENTITY(數據類型,標識種子,標識增長量) AS 列名
INTO 新表 FROM 原始表
SELECT Students,SName,Students,SAddress,Students,SEmail,
IDENTITY(int,1,1) As StudentID
INTO TongXunLuEX FROM Students
(4)插入多行內容
INSERT INTO <表名>(列名)
SELECT 列內容 UNION
SELECT 列內容 UNION
……
七、更新數據行
UPDATE <表名> SET <列名 = 更新值>
[WHERE <更新條件>]
UPDATE Students
SET SAddress ='某社區 22#樓 4 單元 45 號'
WHERE SAddress = '某社區 22#樓 4 單元 45 號'
UPDATE Scores
SET Scores = Scores + 5
WHERE Scores <= 95
八、刪除數據行
1、刪除指定的行
DELETE FROM <表名> [WHERE <刪除條件>]
註意：DELETE FROM 不會只刪單個欄位,要刪就是整行
2、刪除所有記錄
TRUNCATE TABLE <表名>
註意：不能用於有外鍵約束引用的表。刪除後,表的結構、列、約束、索引不變。
九、查詢
SELECT <列名>
FROM <表名>
[WHERE <查詢條件表達式>]
[ORDER BY <排序的列名>[ASC(升)或 DESC(降)] ] --預設為升序
1、查詢全部的行和列
SELECT * FROM Students
2、查詢部分行
SELECT <列名>
FROM <表名>
WHERE <查詢條件表達式>
3、自定義命名查詢結果中的列名
(1)使用 AS 來命名列
SELECT 原表中列名 AS 查詢結果中顯示的列名
FROM <表名>
WHERE <查詢條件表達式>
SELECT Stuno AS 學員編號,StuName AS 學員姓名,StuAddress AS 學員地址
FROM stuinfo
WHERE SAddress like '地址不詳'
(2)合併兩列數據,以規定格式輸出查詢結果
SELECT 原表中列名+ '.' +原表中列名 AS 查詢結果中顯示的列名
FROM 表名
(3)使用=來命名列
SELECT 查詢結果中顯示的列名 = 原表中列名
FROM <表名>
WHERE <查詢條件表達式>
SELECT 查詢結果中顯示的列名 = 原表中列名+ '.' +原表中列名
FROM 表名
(4)查詢某幾列為空的行
SELECT 列名 A
FROM 表名
WHERE 條件列名 B IS NULL
顯示結果：B 列為空的 A 列內容。
(5)顯示結果中加入常量列
SELECT 列 A = SName,列 B = SAddress,'常量列內容'AS 常量列名稱
FROM Students
SELECT 姓名=SName,地址= SAddress,'72 中' AS 學校名稱
FROM Students
(6)限制查詢結果輸出的行數
SELECT TOP 5 列名
FROM 表名
WHERE 條件
顯示結果：符合條件的前五行。
(7)返回百分之多少行
SELECT TOP 20 PERCENT 列名
FROM 表名
WHERE 條件
顯示結果：符合條件的行數的前 20%行。
(8)升序排列(預設)ASC
SELECT StudentID As 學員編號,(Score*0.9+5) As 綜合成績
FROM Score
WHERE (Score*0.9+5)>60
ORDER BY Score
(9)降序排列 DESC
SELECT Au_Lname +'.' +Au_fName AS EMP
From 表 A Union
SELECT fName +'.'+ LName AS EMP
From 表 B
ORDER BY EMP DESC
顯示結果：混合查找兩張表中的列,並按格式輸出到新列中,並按新列排序輸出。
(10)按多列排序
SELECT StudentID As 學員編號, Score As 成績
FROM 表名
WHERE Score>60
ORDER BY Score,studentID
顯示結果：先按 Score 排序，再按 studentID 排序。
十、模糊查詢
1、LIKE
查詢時,欄位中的內容並不一定與查詢內容完全匹配,只要欄位中含有這些內容。
SELECT StuName AS 姓名
FROM Stuinfo
WHERE stuname LIKE '徐%'
顯示結果：姓為“徐”的人的名字。
2、IS NULL
把某一欄位中內容為空的記錄查詢出來。
SELECT StuName AS 姓名,StuAddress AS 地址。
FROM Stuinfo
WHERE StuAddress IS NULL
顯示結果：把地址欄為空的顯示出來。
3、BETWEEN
把某一欄位中內容在特定範圍內的記錄查詢出來。
SELECT StuNo, Score
FROM Stumarks
WHERE Score BETWEEN 60 AND 80
顯示結果：把分數 80>= Score >=60 的顯示出來。
4、IN
把某一欄位中內容與所列出的查詢內容列表匹配的記錄查詢出來。
SELECT StuName AS 學員姓名,StuAddress As 地址
FROM Stuinfo
WHERE StuAddress IN ('北京','廣州','上海')
顯示結果：把地址在('北京','廣州','上海')里的顯示出來。
十一、聚合函數
1、SUM(求和)
SELECT SUM(Score)
FROM Stumarks
WHERE 條件
顯示結果：把符合條件的 Score 求和,然後顯示結果。
2、AVG(求平均值)
SELECT AVG(SCore) AS 平均成績
From Score
WHERE Score >=60
顯示結果：把 Score >=60 的成績求平均值,然後顯示結果,顯示的列名為“平均成績”
3、MAX、MIN(求大、小值)
SELECT MAX (Score) AS 高分, MIN (Score) AS 低分
From Score
WHERE Score >=60
顯示結果：把 Score>=60 中的高分和低分顯示出來。
4、COUNT(計數)
SELECT COUNT (*) AS 及格人數
From Score
WHERE Score>=60
顯示結果：把 Score 列中,>=60 的個數統計出來,然後顯示統計數目。
十二、分組查詢
1、單列分組查詢
SELECT CourseID, AVG(Score) AS 課程平均成績
FROM Score
GROUP BY CourseID
顯示結果：按 CourseID 組求 Score 的平均值,然後將 CourseID 和平均值顯示出來。
2、多列分組
SELECT StudentID AS 學員編號,CourseID AS 內部測試, AVG(Score) AS 平均成績
FROM Score
GROUP BY StudentID,CourseID
顯示結果：顯示所有學員的：“學員編號”(StudentID),“內部測試”(CourseID),“平均
成績”(AVG(Score))。如果同一 CourseID 組中出現了相同的 StudentID,則顯示出來的是這一
CourseID 組中相同的 StudentID 的平均成績。
3、HAVING(追加條件)
SELECT StudentID AS 學員編號,CourseID AS 內部測試, AVG(Score) AS 平均成績
FROM Score
GROUP BY StudentID,CourseID
HAVING COUNT(Score)>1
顯示結果：顯示補考學員的：“學員編號”(StudentID),“內部測試”(CourseID),“平均
成績”(AVG(Score))。如果同一 CourseID 組中的同一 StudentID 組中記錄 Score 的次數,如果
次數>1,則顯示出來“學員編號”(StudentID),“內部測試”(CourseID),“平均成績”(AVG(Score))。
4、條件比較順序
WHERE 子句從數據源中去掉不符合其搜索條件的數據。
GROUP BY 子句搜集數據行到各個組中,統計函數為各個組計算統計值。
HAVING 子句去掉不符合其組搜索條件的各組數據行。
WHERE———>GROUP BY———>HAVING
十三、多表聯接查詢
1、分類
(1)內聯接(INNER JOIN)
(2)外聯接
A、左外聯結 (LEFT JOIN)
B、右外聯結 (RIGHT JOIN)
C、完整外聯結 (FULL JOIN)
(3)交叉聯接(CROSS JOIN)
2、多表內聯結查詢
(1)建立聯接
SELECT S.SName,C.CourseID,C.Score
From Score AS C
INNER JOIN Students AS S
ON C.StudentID = S.SCode
顯示結果：把 Score 表和 Students 表建立內聯結,查詢 C.StudentID = S.SCode 時,顯示
S.SName,C.CourseID,C.Score 的內容。
(2)未建立聯接
SELECT Students.SName, Score.CourseID, Score.Score
FROM Students,Score
WHERE Students.SCode = Score.StudentID
顯示結果：同上。
(3)多表聯接查詢—三表聯接
SELECT S.SName AS 姓名, CS.CourseName AS 課程, C.Score AS 成績
FROM Students AS S
INNER JOIN Score AS C
ON (S.SCode = C.StudentID)
INNER JOIN Course AS CS
ON (CS.CourseID = C.CourseID)
(4)區別
建立聯接的查詢速度比沒有建立的快得多。
3、多表外聯接查詢
(1)左外聯接
(LEFT JOIN 或 LEFT OUTER JOIN)
SELECT S.SName,C.CourseID,C.Score
From Students AS S
LEFT JOIN Score AS C
ON C.StudentID = S.SCode
顯示結果：Students 為左表(left join)Score 為右表。左表中有的,右表中沒有的顯示空值
(NULL)。
(2)右外聯接
(RIGHT JOIN 或 RIGHT OUTER JOIN)
SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name
FROM titles
RIGHT OUTER JOIN Publishers
ON Titles.Pub_id = Publishers.Pub_id
顯示結果：與左外連接相反。
(3)完整外聯接
(FULL JOIN 或 FULL OUTER JOIN)
顯示結果：左表和右表中的所有行。當某行在另一個表中沒有匹配行時,則另一個表的
選擇列為空值；如果有匹配行,則顯示結果包括左右表中的所有列值。
4、多表交叉聯接查詢
(CROSS JOIN)
交叉聯接返回左表中的所有行,左表中的每一行與右表中的所有行一一組合,相當於兩
個表“相乘”。
十四、資料庫用戶
1、創建登錄帳戶
(1)添加 Windows 登錄帳戶
EXEC sp_grantlogin 'jbtraining\S26301' (功能變數名稱\用戶名)
(2)添加 SQL 登錄帳戶
EXEC sp_addlogin 'zhangsan', '1234'
EXEC 表示調用存儲過程,存儲過程類似 C 語言的函數。
內置的系統管理員 帳戶 sa,密碼預設為空,建議修改密碼
2、創建資料庫用戶
USE 庫名
GO
EXEC sp_grantdbaccess '登錄帳戶名','資料庫用戶名'
其中,“資料庫用戶“為可選參數,預設為登錄帳戶,即資料庫用戶預設和登錄帳戶同名。
3、給用戶分配許可權
GRANT 許可權 [ON 表名 ] TO 資料庫用戶
許可權：select、insert、update、delete、create table ……
4、系統內置的資料庫用戶
(1)dbo 用戶
A、表示資料庫的所有者(DB Owner)
B、無法刪除 dbo 用戶,此用戶始終出現在每個資料庫中
(2)guest 用戶
A、適用於沒有資料庫用戶的登錄帳號訪問
B、每個資料庫可有也可刪除
十五、T—SQL 編程
1、變數
(1)局部變數
A、局部變數必須以標記@作為首碼 ,如@age。
B、局部變數的使用也是先聲明,再賦值。
C、聲明局部變數
DECLARE @變數名 數據類型
D、賦值
SET @變數名 = 值
SELECT @變數名 = 值
(2)全局變數
A、全局變數必須以標記@ @作為首碼,如@@version
B、全局變數由系統定義和維護,我們只能讀取,不能修改全局變數的值
C、全局變數的類型與含義
變 量 含 義
@@ERROR 後一個 T-SQL 錯誤的錯誤號
@@IDENTITY 後一次插入的標識值
@@LANGUAGE 當前使用的語言的名稱
@@MAX_CONNECTIONS 可以創建的同時連接的大數目
@@ROWCOUNT 受上一個 SQL 語句影響的行數
@@SERVERNAME 本地伺服器的名稱
@@TRANSCOUNT 當前連接打開的事務數
@@VERSION SQL Server 的版本信息
2、輸出語句
(1)print 局部變數或字元串
結果在消息視窗以文本方式顯示。
(2)SELECT 局部變數 AS 自定義列名
結果在網格視窗以表格方式顯示。
3、邏輯控制語句
(1)IF-ELSE 條件語句
IF(條件)
BEGIN
語句塊……
END
ELSE
……
(2)WHILE 迴圈語句
WHILE(條件)
BEGIN
語句塊……
(BREAK)-- 跳出迴圈,可選語句。
END
(3)CASE—END 多分支語句
CASE
WHEN 條件 1 THEN 結果 1
WHEN 條件 2 THEN 結果 2
……
(ELSE) -- 其他結果,可選語句。
END
(4)GO 批處理語句
語句 1
語句 2
……
GO -- 批處理語句的標誌
A、 批處理是包含一個或多個 SQL 語句的組,從應用程式一次性地發送到 SQL Server
執行。SQL Server 將批處理語句編譯成一個可執行單元,此單元稱為執行計劃。執行
計劃中的語句每次執行一條。
B、 GO 是批處理的標誌,表示 SQL Server 將這些 T-SQL 語句編譯為一個執行單元,提高
執行效率。一般是將一些邏輯相關的業務操作語句,放置在同一批中,這完全由業務需
求和代碼編寫者決定。
C、 SQLServer 規定：如果是建庫、建表語句、以及我們後面學習的存儲過程和視圖等,
則必須在語句末尾添加 GO 批處理標誌。
十六、高級查詢
1、簡單的子查詢
SELECT * FROM stuInfo
WHERE stuAge>( SELECT stuAge FROM stuInfo where stuName='姓名')
（1）子查詢的一般用法
A、SELECT … FROM 表 1 WHERE 欄位 1 >(子查詢)。
B、外面的查詢稱為父查詢，括弧中嵌入的查詢稱為子查詢。
C、UPDATE、INSERT、DELETE 一起使用，語法類似於 SELECT 語句。
D、將子查詢和比較運算符聯合使用，必須保證子查詢返回的值不能多於一個。
（2）子查詢替換表連接
A、一般來說，表連接都可以用子查詢替換，但有的子查詢卻不能用表連接替換。
B、子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合於操縱一個表的數據。
C、表連接更適合於查看多表的數據。
2、IN（NOT IN）子查詢
SELECT stuName FROM stuInfo
WHERE stuNo IN (SELECT stuNo FROM stuMarks WHERE writtenExam=60)
IN（在子查詢範圍內），NOT IN（不在子查詢範圍內）
IN（NOT IN）後面的子查詢可以返回多條記錄，常用 IN 替換等於（＝）的比較子查詢。
3、EXISTS 子查詢
IF EXISTS （子查詢）
語句……
A、如果子查詢的結果非空，即記錄條數 1 條以上，則 EXISTS （子查詢）將返回真（true），
否則返回假(false) 。
B、EXISTS 也可以作為 WHERE 語句的子查詢，但一般都能用 IN 子查詢替換。
C 、EXISTS 和 IN 一樣，允許添加 NOT 取反，表示不存在。
十七、事務
1、使用 T-SQL 語句來管理事務
開始事務：BEGIN TRANSACTION
提交事務：COMMIT TRANSACTION
回滾（撤銷）事務：ROLLBACK TRANSACTION
2、判斷某條語句執行是否出錯
使用全局變數@@ERROR。@@ERROR 只能判斷當前一條 T-SQL 語句執行是否有錯，
為了判斷事務中所有 T-SQL 語句是否有錯，我們需要對錯誤進行累計；
如： SET @errorSum=@errorSum+@@error
3、事務必須具備 ACID 四個屬性
原子性（Atomicity）：事務是一個完整的操作。事務的各步操作是不可分的（原子的）；
要麼都執行，要麼都不執行
一致性（Consistency）：當事務完成時，數據必須處於一致狀態
隔離性（Isolation）：對數據進行修改的所有併發事務是彼此隔離的，這表明事務必須是
獨立的，它不應以任何方式依賴於或影響其他事務
永久性（Durability）：事務完成後，它對資料庫的修改被永久保持，事務日誌能夠保持
事務的永久性
4、事務的分類
顯示事務：用 BEGIN TRANSACTION 明確指定事務的開始，這是常用的事務類型
隱性事務：通過設置 SET IMPLICIT_TRANSACTIONS ON 語句，將隱性事務模式設置
為打開，下一個語句自動啟動一個新事務。當該事務完成時，再下一個 T-SQL 語句又將啟
動一個新事務
自動提交事務：這是 SQL Server 的預設模式，它將每條單獨的 T-SQL 語句視為一個
事務，如果成功執行，則自動提交；如果錯誤，則自動回滾
5、事務例句
BEGIN TRANSACTION -- 開始事務
DECLARE @errorSum INT --定義變數接收錯誤語句數
SET @errorSum = 0 --初始化變數，無錯誤
SQL 語句 1
SET @errorSum=@errorSum+@@error
SQL 語句 2
SET @errorSum=@errorSum+@@error
……
IF @errorSum <> 0 --產生錯誤，回滾事務
BEGIN
ROLLBACK TRANSACTION
END
ELSE --如果無錯誤，提交事務
BEGIN
COMMIT TRANSACTION
END
GO
十八、索引
1、索引類型
唯一索引：唯一索引不允許兩行具有相同的索引值
主鍵索引：為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。
主鍵索引要求主鍵中的每個值是唯一的，並且不能為空
聚集索引(Clustered)：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個表只
能有一個
非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數據存儲在一個位置，索
引存儲在另一個位置，索引中包含指向數據存儲位置的指針。可以有多個，小於 249 個
2、使用 T-SQL 語句創建索引
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_name (column_name…)
[WITH FILLFACTOR=x]
UNIQUE 表示唯一索引，可選。
CLUSTERED、NONCLUSTERED 表示聚集索引還是非聚集索引，可選。
FILLFACTOR 表示填充因數，指定一個 0 到 100 之間的值，該值指示索引頁填滿的空間所
占的百分比。
3、索引的優缺點
（1）優點
A、加快訪問速度
B、加強行的唯一性
（2）缺點
A、帶索引的表在資料庫中需要更多的存儲空間
B、操縱數據的命令需要更長的處理時間，因為它們需要對索引進行更新
4、創建索引的指導原則
（1）請按照下列標準選擇建立索引的列
A、該列用於頻繁搜索
B、該列用於對數據進行排序
（2）請不要使用下麵的列創建索引
A、列中僅包含幾個不同的值。
B、表中僅包含幾行。為小型表創建索引可能不太划算，因為 SQL Server 在索引中搜索
數據所花的時間比在表中逐行搜索所花的時間更長
5、索引例句
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_writtenExam')
DROP INDEX stuMarks.IX_writtenExam
/*--筆試列創建非聚集索引：填充因數為 30％--*/
CREATE NONCLUSTERED INDEX IX_writtenExam
ON stuMarks(writtenExam)
WITH FILLFACTOR= 30
GO
/*-----指定按索引 IX_writtenExam 查詢----*/
SELECT * FROM stuMarks (INDEX=IX_writtenExam)
WHERE writtenExam BETWEEN 60 AND 90
十九、視圖
1、是麽是視圖
A、視圖是一張虛擬表，它表示一張表的部分數據或多張表的綜合數據，其結構和數據
是建立在對錶的查詢基礎上
B、視圖中並不存放數據，而是存放在視圖所引用的原始表（基表）中
C、同一張原始表，根據不同用戶的不同需求，可以創建不同的視圖
2、視圖的用途
A、篩選表中的行
B、防止未經許可的用戶訪問敏感數據
C、降低資料庫的複雜程度
D、將多個物理資料庫抽象為一個邏輯資料庫
3、使用 T-SQL 語句創建視圖
CREATE VIEW view_name
AS
<select語句……>
GO
4、視圖例句
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'view_stuInfo_stuMarks')
DROP VIEW view_stuInfo_stuMarks
GO
CREATE VIEW view_stuInfo_stuMarks
AS
SELECT 姓名=stuName,學號=stuInfo.stuNo,
筆試成績 =writtenExam, 機試成績=labExam,平均分=(writtenExam+labExam)/2
FROM stuInfo LEFT JOIN stuMarks
ON stuInfo.stuNo=stuMarks.stuNo
GO
SELECT * FROM view_stuInfo_stuMarks
二十、存儲過程
1、什麼是存儲過程（procedure）
一組預編譯的 SQL 語句，它可以包含數據操縱語句、變數、邏輯控制語句等。
2、存儲過程的優點
A、執行速度更快
B、允許模塊化程式設計
C、提高系統安全性
D、減少網路流通量
3、存儲過程的分類
(1)系統存儲過程
由系統定義，存放在 master 資料庫中，類似 C 語言中的系統函數，系統存儲過程的名
稱都以“sp_”開頭或”xp_”開頭。
(2)用戶自定義存儲過程
由用戶在自己的資料庫中創建的存儲過程，類似 C 語言中的用戶自定義函數。
4、常用的系統存儲過程
擴展存儲過程：xp_cmdshell
可以執行 DOS 命令下的一些的操作，以文本行方式返回任何輸出
調用語法：
EXEC xp_cmdshell DOS 命令 [NO_OUTPUT]
例句：
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
EXEC xp_cmdshell 'dir D:\bank\' --查看文件
系統存儲過程 說明
sp_databases 列出伺服器上的所有資料庫。
sp_helpdb 報告有關指定資料庫或所有資料庫的信息
sp_renamedb 更改資料庫的名稱
sp_tables 返回當前環境下可查詢的對象的列表
sp_columns 回某個表列的信息
sp_help 查看某個表的所有信息
sp_helpconstraint 查看某個表的約束
sp_helpindex 查看某個表的索引
sp_stored_procedures 列出當前環境中的所有存儲過程。
sp_password 添加或修改登錄帳戶的密碼。
sp_helptext 顯示預設值、未加密的存儲過程、用戶定義的存儲過
程、觸發器或視圖的實際文本。
5、使用 T-SQL 語句創建和調用存儲過程
創建：
CREATE PROC[EDURE] 存儲過程名
@參數 1 數據類型 = 預設值,
…… ,
@參數 n 數據類型 = 預設值
AS
SQL語句
GO
調用：
EXEC 過程名 [參數]
註意：
A、和 C 語言的函數一樣，參數可選
B、參數分為輸入參數、輸出參數
C、輸入參數允許有預設值
D、存儲過程的大大小為 128 MB
（1）不帶參數的存儲過程
創建：
CREATE PROCEDURE proc_stu
AS
DECLARE @writtenAvg float,@labAvg float
SELECT @writtenAvg=AVG(writtenExam),
@labAvg=AVG(labExam) FROM stuMarks
print '筆試平均分：'+convert(varchar(5),@writtenAvg)
print '機試平均分：'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本班考試成績：優秀'
ELSE
print '本班考試成績：較差'
print '--------------------------------------------------'
print ' 參加本次考試沒有通過的學員：'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam
FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60
GO
調用：
EXEC proc_stu
（2）帶輸入參數的存儲過程
創建：
CREATE PROCEDURE proc_stu
@writtenPass int=60, --輸入參數可帶預設值
@labPass int=60
AS
print '--------------------------------------------------'
print ' 參加本次考試沒有通過的學員：'
SELECT stuName,stuInfo.stuNo,writtenExam,
labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
調用：
EXEC proc_stu --都採用預設值
EXEC proc_stu 64 --第二個參數採用預設值
EXEC proc_stu @labPass=55 --第一個參數採用預設值
EXEC proc_stu 60,55 --都不採用預設值
（3）帶輸出參數的
創建：
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --輸出（返回）參數
@writtenPass int=60, --推薦將預設參數放後面
@labPass int=60
AS
……
SELECT stuName,stuInfo.stuNo,writtenExam,
labExam FROM stuInfo INNER JOIN stuMarks
ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
SELECT @notpassSum=COUNT(stuNo)
FROM stuMarks WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
調用：
/*---調用存儲過程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64
print '--------------------------------------------------'
IF @sum>=3
print '未通過人數：'+convert(varchar(5),@sum)+ '人,
超過 60%,及格分數線還應下調'
ELSE
print '未通過人數：'+convert(varchar(5),@sum)+ '人,
已控制在 60%以下，及格分數線適中'
GO
6、處理存儲過程中的錯誤
（1）PRINT 語句顯示錯誤信息
可以使用 PRINT 語句顯示錯誤信息，但這些信息是臨時的，只能顯示給用戶
（2）RAISERROR 顯示用戶定義的錯誤信息
可指定嚴重級別，設置系統變數@@ERROR，記錄所發生的錯誤等。
（3）RAISERROR 語句的用法
RAISERROR (msg_id | msg_str,severity,state WITH option[,...n]])
msg_id：在 sysmessages 系統表中指定用戶定義錯誤信息。
msg_str：用戶定義的特定信息，長 255 個字元。
severity：定義嚴重性級別。用戶可使用的級別為 0–18 級。
state：表示錯誤的狀態，1 至 127 之間的值。
option：指示是否將錯誤記錄到伺服器錯誤日誌中。
（4）例句
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --輸出參數
@writtenPass int=60, --預設參數放後
@labPass int=60 --預設參數放後
AS
IF (NOT @writtenPass BETWEEN 0 AND 100)
OR (NOT @labPass BETWEEN 0 AND 100)
BEGIN
RAISERROR (‘及格線錯誤，請指定 0－100 之間的分 數，統計中斷退出',16,1)
RETURN ---立即返回，退出存儲過程
END
…..其他語句同上例，略
GO
/*---調用存儲過程，測試 RAISERROR 語句----*/
DECLARE @sum int, @t int
EXEC proc_stu @sum OUTPUT ,604
SET @t=@@ERROR
print '錯誤號：'+convert(varchar(5),@t )
IF @t<>0
RETURN --退出批處理，後續語句不再執行
print '--------------------------------------------------'
IF @sum>=3
print '未通過人數：'+convert(varchar(5),@sum)+ '人,超過 60%,及格分數線還應下調'
ELSE
print '未通過人數：'+convert(varchar(5),@sum)+ '人,已控制在 60%以下，
及格分數線適中'
GO
二十一、觸發器
1、創建觸發器的語法
CREATE TRIGGER trigger_name ON table_name
[WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL 語句……
GO
2、例句
（1）Insert 觸發器
CREATE TRIGGER trig_transInfo ON transInfo
FOR INSERT
AS
DECLARE @type char(4),@outMoney MONEY
DECLARE @myCardID char(10),@balance MONEY
SELECT @type=transType,@outMoney=transMoney，
@myCardID=cardID FROM inserted
IF (@type='支取')
UPDATE bank SET currentMoney=currentMoney-@outMoney
WHERE cardID=@myCardID
ELSE
UPDATE bank SET currentMoney=currentMoney+@outMoney
WHERE cardID=@myCardID
GO
（2）Delete 觸發器
CREATE TRIGGER trig_delete_transInfo ON transInfo
FOR DELETE
AS
print '開始備份數據，請稍後......'
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='backupTable')
SELECT * INTO backupTable FROM deleted
ELSE
INSERT INTO backupTable SELECT * FROM deleted
print '備份數據成功，備份表中的數據為:'
SELECT * FROM backupTable
GO
（3）Update 觸發器
CREATE TRIGGER trig_update_bank ON bank
FOR UPDATE
AS
DECLARE @beforeMoney MONEY,@afterMoney MONEY
SELECT @beforeMoney=currentMoney FROM deleted
SELECT @afterMoney=currentMoney FROM inserted
IF ABS(@afterMoney-@beforeMoney)>20000
BEGIN
print '交易金額:'+convert(varchar(8),
ABS(@afterMoney-@beforeMoney))
RAISERROR ('每筆交易不能超過 2 萬元，交易失敗',16,1)
ROLLBACK TRANSACTION
END
GO
CREATE TRIGGER trig_update_transInfo ON transInfo
FOR UPDATE
AS
IF UPDATE(transDate) --判斷是否被更改
BEGIN
print '交易失敗.....'
RAISERROR (‘安全警告：交易日期不能修改，由系統自動產生',16,1)
ROLLBACK TRANSACTION
END
GO
