SQL server進階應用程式 收藏版

一. 建庫,建表,加約束.
1.1建庫 複製代碼 代碼如下:use master
go
if exists (select * from sysdatabases where name='MyDatabase')—判斷master資料庫sysdatagbases表中是否存在將要建立的資料庫名
drop database MyDatabase—如果sysdatabases表中存在該資料庫名,則將它刪除
go
exec xp_cmdshell ‘md D:/MyDatabases'—利用預存程序建立一個檔案夾用於儲存資料物理檔案(資料檔案，記錄檔),DOS命令(mkdir=md)
go
create database MyDatabase—建立資料庫
on
(
name='MyDatabase_data',--指定邏輯檔案名稱
filename='D:/MyDatabases/MyDatabase_data.mdf',--指定物理檔案名稱
size=5mb,--初始大小
maxsize=50mb,--指定物理檔案最大容量,可選
filegrowth=20%--增長量
)
log on
(
name='MyDatabase_log',--指定邏輯記錄檔名
filename='D:/MyDatabases/MyDatabase_log.ldf',--指定日誌物理檔案名稱
size=5mb,--初始大小
maxsize=50mb,--指定日誌物理檔案最大容量,可選
filegrowth=20%--增長量
)
go
use MyDatabase
go

1.2建表. 複製代碼 代碼如下:If exists (select * from sysobjects where name='Mytable')
drop table Mytable
go
create table Mytable
(
ID int not null identity(1,1) primary key,--識別值種子1,識別值增量1,設該列為主鍵
name nchar(10) not null,--不可為空白
degree numeric(18,0)—身份證,numeric(18,0)代表18位元字,小數位元為0
)
Go

1.3加約束. 複製代碼 代碼如下:Alter table stuInfo add constraint PK_stuNo primary key(stuNo)—主鍵
alter table stuMarks add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)—外鍵
alter table stuInfo add constraint UQ_stuID unique(stuID)—唯一約束
alter table stuInfo add constraint df_stuAddress default(‘地址不詳') for stuAddres—預設約束
alter table stuMarks add constraint ck_stuAge check(stuAge between 15 and 40)—檢查約束
alter table stuMarks drop constraint ck_stuAge—刪除約束

1.4帳戶管理.
1.4.1建立登入帳戶. 複製代碼 代碼如下:exec sp_grantlogin 'jbtraining/s26301' --windows使用者為jbtraining/s26301,jbtraining 表示域
exec sp_addlogin 'admin','0533131'--SQL登入帳戶,帳戶: 'admin',密碼:0533131.

1.4.2建立資料庫使用者. 複製代碼 代碼如下:exec sp_grantdbaccess 'jbtraining/s26301','s26301dbuser'--s26301dbuser為資料庫使用者名稱
exec sp_grantdbaccess 'admin', 's26301dbuser'--s26301dbuser為資料庫名

1.4.3向資料庫使用者授權. 複製代碼 代碼如下:/*為s26301dbuser分配對錶mytable的select,insert,updata,delete許可權*/
grant select,insert,update,delete on mytable to s26301dbuser
/*為s26301dbuser使用者指派建立表的許可權
grant create table to s26301dbuser

二.T-SQL編程
2.1變數.
局部變數的名稱必須以標記@作為首碼：
Declare @name varchar(8)--聲明變數。
Declare @name varchar(8)=value--初始值。
Set @name=value-- 賦值。
Select @name=value--賦值。
全域變數
SQL server中的所有全域變數都使用兩個@標誌作為首碼：

變數	含義
@@error	最後一個T-SQL錯誤的錯誤號碼
@@identity	最後一次插入的標識值
@@language	當前使用的語言名稱
@@max_connections	可以建立同時串連的最大數目
@@rowcount	受上一個SQL語句影響的行數
@@servername	本機伺服器的名稱
@@servicename	該電腦上的SQL服務的名稱
@@timeticks	當前電腦上每刻度的微秒數
@@transcount	當前串連開啟的事務數
@@version	SQL server的版本資訊

2.2輸出語句.
Print局部變數或字串,以字串形式列印資料 。
Select 局部變數AS自訂欄名，以表格形式列印資料。
2.3邏輯控制語句.
2.3.1.If-else
If(運算式)
Begin
語句1
語句2
End
Else
Begin
語句1
語句2
End
2.3.2.case
Case
When 條件一 then 結果二
When 條件二 then 結果二
End

三．進階查詢

3.1子查詢.
Select … from 表1 where 欄位1>(子查詢)
3.2IN和NOT IN子查詢
Select … from 表1 where 欄位一 NOT IN (子查詢)
Select … from 表2 where 欄位二 IN (子查詢)
3.3Exists和Not Exists子查詢
If exists(子查詢)
語句
If not exists(子查詢)
語句

四．事務，索引和視圖.

4.1事務
² 開始事務:begin transaction
² 提交事務:commit transaction
² 復原事務:rollback transaction
事務分類
² 明確交易:用begin transaction 明確指定事務的開始。
² 隱性事務:通過設定set implicit_transactions on語句,將陷性事務模式設定為開啟。當以隱性事務操作時，SQL server將在提交或復原事務後撲克動啟動新事務。無法描述事務的開始，只需提交或復原每個事務.
² 自動認可事務:這是SQL server的預設模式，它將每條單獨的T-SQL語句視為一個事務。如果成功執行，則自動認可。如果錯誤，則自動復原。
樣本:
Begin transaction--開始事務 複製代碼 代碼如下:Declare @errorsum int
Set @errorsum=0
Update ……………………….
Set @errorsum=@errorsum+@@error
Update………………………..
Set @errorsum=@errorsum+@@error
If @errorsum<>0
Begin
Rollback transaction--復原事務
End
Else
Begin
Commit transaction--提交事務
End
Go

4.2索引
索引是SQL server編排資料的內部方法。它為SQL server提供一種方法來編排查詢資料的路由
索引頁：
資料庫中的儲存索引的資料頁。索引頁存放檢索資料行的關鍵字頁以及該資料行的地址指標。索引頁類似於漢語字典中按拼音或筆畫排序的目錄頁。
唯一索引：
唯一索引不允許兩行具有相同的索引值.
主鍵索引：
在資料庫圖表中為表定義一個主鍵將自動建立主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的。當在查詢中使用主鍵索引時，它還允許快速存取資料。
叢集索引：
在叢集索引中，表中各行的物理順序與索引值的邏輯（索引）順序相同。表只能包含一個叢集索引。
建立索引 複製代碼 代碼如下:If exists (select name from sysindexes where name='myindex')
Drop index 表名.myindex
Create nonclustered index myindex
on
student(id) with fillfactor=30
go

myindex為索引名,with fillfactor=30,指定填滿因數為30%
使用索引
Select * from stumarks (myindex) where writtenExam between 60 and 90
Stumarks為表名,myindex為索引名,writtenexam為列名,between 60 and 90 指定查詢出writtenexam欄位60至90之間的值
建立索引的條件
Ø 該列用於頻繁搜尋
Ø 該列用於對資料進行排序
禁止使用索引的情況
Ø 列中僅包含幾個不同的值
Ø 表中資料僅包含幾行,為小型表建立索引可能不太划算，因為SQL server在索引中搜尋資料所花的時間比在表中逐行搜尋所花的時間更長
4.3視圖
視圖的用處
Ø 篩選表中的行
Ø 防止未經許可的使用者訪問敏感性資料
Ø 降低資料庫的複雜程度
Ø 將多個物理資料表抽象為一個邏輯資料表
使用視圖的好處
ü 對終端使用者的好處
l 結果更容易理解。建立視圖時，可以將列名改為有意義的名稱，使使用者更容易理解列所代表的內容。在視圖中修改列名不會影響基表的列名
l 獲得資料更容易。很多人對SQL不太瞭解，因此對他們來說建立對多個表的複雜查詢很困難。因而可以通過建立視圖來方便使用者訪問多個表中的資料。
ü 對開發人員的好處
l 限制資料檢索更容易。開發人員有時需要隱藏某些行或列中的資訊。通過使用視圖，使用者可以靈活地訪問他們需要的資料，同時保證同一個表或其他表的其他資料庫的安全性。要實現這一目標，可以在建立視圖時將要對使用者保密碼的列排除在外。
l 維護應用程式更方便。調試視圖比調試查詢更容易。跟蹤視圖中過程的各個步驟中的錯誤更為容易，這是因為所有的步驟都是視圖的組成部分。
建立視圖 複製代碼 代碼如下:If exists (select * from sysobjects where name-‘myview')
Drop view myview
Go
Create view myview
As
Select 姓名=stuName,學員=sutInfo from stuInfo left join stuMarks
on stuInfo.stuNO=stuMarks.stuNo
Go

使用視圖
Select * from myview
五．預存程序
5．1系統預存程序
常用系統預存程序
Sp_datadases 列出伺服器上的所有資料庫
Sp_helpdb 報告有關指定資料庫或所有資料庫的資訊
Sp_renamedb 更改資料庫的名稱
Sp_tables 返回當前環境下可查詢的對象的列表
Sp_columns 返回某個表列的資訊
Sp_help 查看某個表的所有資訊
Sq_helpconstraint 查看某個表的約束
Sq_helpindex 查看某個表的索引
Sq_stored_procedures 列出當前環境中的所有預存程序
Sp_password 添加或修改登入帳戶的密碼
Sp_helptext 顯示預設值、未加密的預存程序、使用者定義的預存程序、觸發器或視圖的實際文本
5．2自訂預存程序
Ø 不帶參預存程序
Ø 帶輸入參數的預存程序
Ø 帶輸出參數的預存程序

5．2．1不帶參數的預存程序 複製代碼 代碼如下:Create proc procedureName
As
SQL 陳述式
Go

調用文法

Exec procedureName

5．2．2帶輸入參數的預存程序 複製代碼 代碼如下:Create proc procedureName
@number int =預設值,
@n varchar(20)
As
SQL 陳述式
Go
調用文法:
Exec procedureName 200,'lyh'
Exec procedureName @n='lyh'

5．2．3帶輸出參數的預存程序 複製代碼 代碼如下:Create proc procedureName
@number int output,
@name char(20)
As
SQL 陳述式
Set @number=1000
Go
調用文法
Declare @dd int
Exec procedureName @dd output,'lyh'

六.SQL server觸發器

什麼是觸發器:
觸發器是在對錶進行插入，更新或刪除操作時自動執行的預存程序。
觸發器的類別
INSERT觸發器：當向表中插入資料時觸發，自動執行觸發器所定義的SQL語句。
UPDATE觸發器：當更新表中某列、多列時觸發，自動執行觸發器所定義的SQL語句。
DELETE觸發器：當刪除表中記錄時觸發，自動執行觸發器所定義的SQL語句。
Deleted表：用於儲存DELETE和UPDATE語句所影響的行的副本，即在DELETED表中臨時儲存了被刪除或被更新前的記錄行。在執行DELETE或UPDATE語句 ，行從觸發器表中刪除，並傳輸到DELETED表中。由此我們可以從DELETED表中檢查刪除的資料行是否能刪除。如果不能，就可以復原撤銷此操作，因為觸發器本身就是一個特殊的事務單元。
Inserted表：用於儲存INSERT和UPDATE語句所影響的行的副本，即在inserted表中臨時儲存了被插入或被更新後的記錄行。在執行INSERT或UPDATE語句時，新加行被同時添加到INSERT表和觸發器表中。由此我們可以從INSERTED檢查插入資料是否滿足業務需求。如果不滿足，就可以向使用者報告錯誤訊息，並復原撤銷操作。

定義觸發器

Create trigger trigger_name
On tablae_name
[with encryption]
For(insert,update,delete)
As
SQL 陳述式
Go

Trigger_name:是觸發器的名稱。觸發器名稱必須符合標識符規則，並且在資料庫中必須唯一，。可以選擇是否指定觸發器所有者名稱。
Table_name:是在其上執行觸發器的表或視圖
With encryption：加密syscomments表中包含create trigger語句文本的條目。使用with encryption可防止將觸發器作為SQLserver複製的部分發布.
Create trigger：必須是批處理中的第一條語句，並且只能應用到一個表中。
觸發器只能在當前的資料庫中建立，不過觸發器可以引用當前資料庫的外部對象。