SQLserver高級應用收藏版

一. 建庫,建表,加約束.
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:是觸發器的名稱。觸發器名稱必須符合標識符規則，并且在數據庫中必須唯一，?？梢赃x擇是否指定觸發器所有者名稱。
Table_name:是在其上執行觸發器的表或視圖
With encryption：加密syscomments表中包含create trigger語句文本的條目。使用with encryption可防止將觸發器作為SQLserver復制的部分發布.
Create trigger：必須是批處理中的第一條語句，并且只能應用到一個表中。
觸發器只能在當前的數據庫中創建，不過觸發器可以引用當前數據庫的外部對象。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com