SQL server高級(jí)應(yīng)用收藏版

更新時(shí)間：2012年08月09日 21:51:29 作者：

SQL server高級(jí)應(yīng)用收藏版,使用sqlserver的朋友可以參考下

一. 建庫,建表,加約束.
1.1建庫

 
use master 
go 
if exists (select * from sysdatabases where name='MyDatabase')—判斷master數(shù)據(jù)庫sysdatagbases表中是否存在將要?jiǎng)?chuàng)建的數(shù)據(jù)庫名 
drop database MyDatabase—如果sysdatabases表中存在該數(shù)據(jù)庫名,則將它刪除 
go 
exec xp_cmdshell ‘md D:/MyDatabases'—利用存儲(chǔ)過程創(chuàng)建一個(gè)文件夾用于存儲(chǔ)數(shù)據(jù)物理文件(數(shù)據(jù)文件，日志文件),DOS命令(mkdir=md) 
go 
create database MyDatabase—?jiǎng)?chuàng)建數(shù)據(jù)庫 
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建表.

復(fù)制代碼代碼如下:

 
If exists (select * from sysobjects where name='Mytable') 
drop table Mytable 
go 
create table Mytable 
( 
ID int not null identity(1,1) primary key,--標(biāo)識(shí)種子1,標(biāo)識(shí)增量1,設(shè)該列為主鍵 
name nchar(10) not null,--不可為空 
degree numeric(18,0)—身份證,numeric(18,0)代表18位數(shù)字,小數(shù)位數(shù)為0 
) 
Go 

1.3加約束.

復(fù)制代碼代碼如下:

 
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—默認(rèn)約束 
alter table stuMarks add constraint ck_stuAge check(stuAge between 15 and 40)—檢查約束 
alter table stuMarks drop constraint ck_stuAge—?jiǎng)h除約束 

1.4帳戶管理.
1.4.1創(chuàng)建登錄帳戶.

復(fù)制代碼代碼如下:

 
exec sp_grantlogin 'jbtraining/s26301' --windows用戶為jbtraining/s26301,jbtraining 表示域 
exec sp_addlogin 'admin','0533131'--SQL登錄帳戶,帳戶: 'admin',密碼:0533131. 

1.4.2創(chuàng)建數(shù)據(jù)庫用戶.

復(fù)制代碼代碼如下:

 
exec sp_grantdbaccess 'jbtraining/s26301','s26301dbuser'--s26301dbuser為數(shù)據(jù)庫用戶名 
exec sp_grantdbaccess 'admin', 's26301dbuser'--s26301dbuser為數(shù)據(jù)庫名 

1.4.3向數(shù)據(jù)庫用戶授權(quán).

復(fù)制代碼代碼如下:

 
/*為s26301dbuser分配對(duì)表mytable的select,insert,updata,delete權(quán)限*/ 
grant select,insert,update,delete on mytable to s26301dbuser 
/*為s26301dbuser用戶分配創(chuàng)建表的權(quán)限 
grant create table to s26301dbuser 

二.T-SQL編程
2.1變量.
局部變量的名稱必須以標(biāo)記@作為前綴：
Declare @name varchar(8)--聲明變量。
Declare @name varchar(8)=value--初始值。
Set @name=value-- 賦值。
Select @name=value--賦值。
全局變量
SQL server中的所有全局變量都使用兩個(gè)@標(biāo)志作為前綴：

變量	含義
@@error	最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào)
@@identity	最后一次插入的標(biāo)識(shí)值
@@language	當(dāng)前使用的語言名稱
@@max_connections	可以創(chuàng)建同時(shí)連接的最大數(shù)目
@@rowcount	受上一個(gè)SQL語句影響的行數(shù)
@@servername	本地服務(wù)器的名稱
@@servicename	該計(jì)算機(jī)上的SQL服務(wù)的名稱
@@timeticks	當(dāng)前計(jì)算機(jī)上每刻度的微秒數(shù)
@@transcount	當(dāng)前連接打開的事務(wù)數(shù)
@@version	SQL server的版本信息

2.2輸出語句.
Print局部變量或字符串,以字符串形式打印數(shù)據(jù) 。
Select 局部變量AS自定義列名，以表格形式打印數(shù)據(jù)。
2.3邏輯控制語句.
2.3.1.If-else
If(表達(dá)式)
Begin
語句1
語句2
End
Else
Begin
語句1
語句2
End
2.3.2.case
Case
When 條件一 then 結(jié)果二
When 條件二 then 結(jié)果二
End

三．高級(jí)查詢

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(子查詢)
語句

四．事務(wù)，索引和視圖.

4.1事務(wù)
² 開始事務(wù):begin transaction
² 提交事務(wù):commit transaction
² 回滾事務(wù):rollback transaction
事務(wù)分類
² 顯式事務(wù):用begin transaction 明確指定事務(wù)的開始。
² 隱性事務(wù):通過設(shè)置set implicit_transactions on語句,將陷性事務(wù)模式設(shè)置為打開。當(dāng)以隱性事務(wù)操作時(shí)，SQL server將在提交或回滾事務(wù)后撲克動(dòng)啟動(dòng)新事務(wù)。無法描述事務(wù)的開始，只需提交或回滾每個(gè)事務(wù).
² 自動(dòng)提交事務(wù):這是SQL server的默認(rèn)模式，它將每條單獨(dú)的T-SQL語句視為一個(gè)事務(wù)。如果成功執(zhí)行，則自動(dòng)提交。如果錯(cuò)誤，則自動(dòng)回滾。
示例:
Begin transaction--開始事務(wù)

復(fù)制代碼代碼如下:

 
Declare @errorsum int 
Set @errorsum=0 
Update ………………………. 
Set @errorsum=@errorsum+@@error 
Update……………………….. 
Set @errorsum=@errorsum+@@error 
If @errorsum<>0 
Begin 
Rollback transaction--回滾事務(wù) 
End 
Else 
Begin 
Commit transaction--提交事務(wù) 
End 
Go 

4.2索引
索引是SQL server編排數(shù)據(jù)的內(nèi)部方法。它為SQL server提供一種方法來編排查詢數(shù)據(jù)的路由
索引頁：
數(shù)據(jù)庫中的存儲(chǔ)索引的數(shù)據(jù)頁。索引頁存放檢索數(shù)據(jù)行的關(guān)鍵字頁以及該數(shù)據(jù)行的地址指針。索引頁類似于漢語字典中按拼音或筆畫排序的目錄頁。
唯一索引：
唯一索引不允許兩行具有相同的索引值.
主鍵索引：
在數(shù)據(jù)庫關(guān)系圖中為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個(gè)值是唯一的。當(dāng)在查詢中使用主鍵索引時(shí)，它還允許快速訪問數(shù)據(jù)。
聚集索引：
在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。表只能包含一個(gè)聚集索引。
創(chuàng)建索引

復(fù)制代碼代碼如下:

 
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之間的值
建立索引的條件
Ø 該列用于頻繁搜索
Ø 該列用于對(duì)數(shù)據(jù)進(jìn)行排序
禁止使用索引的情況
Ø 列中僅包含幾個(gè)不同的值
Ø 表中數(shù)據(jù)僅包含幾行,為小型表創(chuàng)建索引可能不太劃算，因?yàn)镾QL server在索引中搜索數(shù)據(jù)所花的時(shí)間比在表中逐行搜索所花的時(shí)間更長
4.3視圖
視圖的用處
Ø 篩選表中的行
Ø 防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)
Ø 降低數(shù)據(jù)庫的復(fù)雜程度
Ø 將多個(gè)物理數(shù)據(jù)表抽象為一個(gè)邏輯數(shù)據(jù)表
使用視圖的好處
ü 對(duì)最終用戶的好處
l 結(jié)果更容易理解。創(chuàng)建視圖時(shí)，可以將列名改為有意義的名稱，使用戶更容易理解列所代表的內(nèi)容。在視圖中修改列名不會(huì)影響基表的列名
l 獲得數(shù)據(jù)更容易。很多人對(duì)SQL不太了解，因此對(duì)他們來說創(chuàng)建對(duì)多個(gè)表的復(fù)雜查詢很困難。因而可以通過創(chuàng)建視圖來方便用戶訪問多個(gè)表中的數(shù)據(jù)。
ü 對(duì)開發(fā)人員的好處
l 限制數(shù)據(jù)檢索更容易。開發(fā)人員有時(shí)需要隱藏某些行或列中的信息。通過使用視圖，用戶可以靈活地訪問他們需要的數(shù)據(jù)，同時(shí)保證同一個(gè)表或其他表的其他數(shù)據(jù)庫的安全性。要實(shí)現(xiàn)這一目標(biāo)，可以在創(chuàng)建視圖時(shí)將要對(duì)用戶保密碼的列排除在外。
l 維護(hù)應(yīng)用程序更方便。調(diào)試視圖比調(diào)試查詢更容易。跟蹤視圖中過程的各個(gè)步驟中的錯(cuò)誤更為容易，這是因?yàn)樗械牟襟E都是視圖的組成部分。
創(chuàng)建視圖

復(fù)制代碼代碼如下:

 
If exists (select * from sysobjects where name-‘myview') 
Drop view myview 
Go 
Create view myview 
As 
Select 姓名=stuName,學(xué)員=sutInfo from stuInfo left join stuMarks 
on stuInfo.stuNO=stuMarks.stuNo 
Go 

使用視圖
Select * from myview
五．存儲(chǔ)過程
5．1系統(tǒng)存儲(chǔ)過程
常用系統(tǒng)存儲(chǔ)過程
Sp_datadases 列出服務(wù)器上的所有數(shù)據(jù)庫
Sp_helpdb 報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息
Sp_renamedb 更改數(shù)據(jù)庫的名稱
Sp_tables 返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表
Sp_columns 返回某個(gè)表列的信息
Sp_help 查看某個(gè)表的所有信息
Sq_helpconstraint 查看某個(gè)表的約束
Sq_helpindex 查看某個(gè)表的索引
Sq_stored_procedures 列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程
Sp_password 添加或修改登錄帳戶的密碼
Sp_helptext 顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí)際文本
5．2自定義存儲(chǔ)過程
Ø 不帶參存儲(chǔ)過程
Ø 帶輸入?yún)?shù)的存儲(chǔ)過程
Ø 帶輸出參數(shù)的存儲(chǔ)過程

5．2．1不帶參數(shù)的存儲(chǔ)過程

復(fù)制代碼代碼如下:

 
Create proc procedureName 
As 
SQL 語句 
Go 

調(diào)用語法

Exec procedureName

5．2．2帶輸入?yún)?shù)的存儲(chǔ)過程

復(fù)制代碼代碼如下:

 
Create proc procedureName 
@number int =默認(rèn)值, 
@n varchar(20) 
As 
SQL 語句 
Go 
調(diào)用語法: 
Exec procedureName 200,'lyh' 
Exec procedureName @n='lyh' 

5．2．3帶輸出參數(shù)的存儲(chǔ)過程

復(fù)制代碼代碼如下:

 
Create proc procedureName 
@number int output, 
@name char(20) 
As 
SQL 語句 
Set @number=1000 
Go 
調(diào)用語法 
Declare @dd int 
Exec procedureName @dd output,'lyh' 

六.SQL server觸發(fā)器

什么是觸發(fā)器:
觸發(fā)器是在對(duì)表進(jìn)行插入，更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程。
觸發(fā)器的類別
INSERT觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句。
UPDATE觸發(fā)器：當(dāng)更新表中某列、多列時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句。
DELETE觸發(fā)器：當(dāng)刪除表中記錄時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句。
Deleted表：用于存儲(chǔ)DELETE和UPDATE語句所影響的行的副本，即在DELETED表中臨時(shí)保存了被刪除或被更新前的記錄行。在執(zhí)行DELETE或UPDATE語句，行從觸發(fā)器表中刪除，并傳輸?shù)紻ELETED表中。由此我們可以從DELETED表中檢查刪除的數(shù)據(jù)行是否能刪除。如果不能，就可以回滾撤銷此操作，因?yàn)橛|發(fā)器本身就是一個(gè)特殊的事務(wù)單元。
Inserted表：用于存儲(chǔ)INSERT和UPDATE語句所影響的行的副本，即在inserted表中臨時(shí)保存了被插入或被更新后的記錄行。在執(zhí)行INSERT或UPDATE語句時(shí)，新加行被同時(shí)添加到INSERT表和觸發(fā)器表中。由此我們可以從INSERTED檢查插入數(shù)據(jù)是否滿足業(yè)務(wù)需求。如果不滿足，就可以向用戶報(bào)告錯(cuò)誤消息，并回滾撤銷操作。

定義觸發(fā)器

Create trigger trigger_name
On tablae_name
[with encryption]
For(insert,update,delete)
As
SQL 語句
Go

Trigger_name:是觸發(fā)器的名稱。觸發(fā)器名稱必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫中必須唯一，?？梢赃x擇是否指定觸發(fā)器所有者名稱。
Table_name:是在其上執(zhí)行觸發(fā)器的表或視圖
With encryption：加密syscomments表中包含create trigger語句文本的條目。使用with encryption可防止將觸發(fā)器作為SQLserver復(fù)制的部分發(fā)布.
Create trigger：必須是批處理中的第一條語句，并且只能應(yīng)用到一個(gè)表中。
觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建，不過觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫的外部對(duì)象。