一. 建庫(kù),建表,加約束.
1.1建庫(kù)
復(fù)制代碼 代碼如下:
use master
go
if exists (select * from sysdatabases where name='MyDatabase')—判斷master數(shù)據(jù)庫(kù)sysdatagbases表中是否存在將要?jiǎng)?chuàng)建的數(shù)據(jù)庫(kù)名
drop database MyDatabase—如果sysdatabases表中存在該數(shù)據(jù)庫(kù)名,則將它刪除
go
exec xp_cmdshell ‘md D:/MyDatabases'—利用存儲(chǔ)過(guò)程創(chuàng)建一個(gè)文件夾用于存儲(chǔ)數(shù)據(jù)物理文件(數(shù)據(jù)文件，日志文件),DOS命令(mkdir=md)
go
create database MyDatabase—?jiǎng)?chuàng)建數(shù)據(jù)庫(kù)
on
(
name='MyDatabase_data',--指定邏輯文件名
filename='D:/MyDatabases/MyDatabase_data.mdf',--指定物理文件名
size=5mb,--初始大小
maxsize=50mb,--指定物理文件最大容量,可選
filegrowth=20%--增長(zhǎng)量
)
log on
(
name='MyDatabase_log',--指定邏輯日志文件名
filename='D:/MyDatabases/MyDatabase_log.ldf',--指定日志物理文件名
size=5mb,--初始大小
maxsize=50mb,--指定日志物理文件最大容量,可選
filegrowth=20%--增長(zhǎng)量
)
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ù)庫(kù)用戶.
復(fù)制代碼 代碼如下:
exec sp_grantdbaccess 'jbtraining/s26301','s26301dbuser'--s26301dbuser為數(shù)據(jù)庫(kù)用戶名
exec sp_grantdbaccess 'admin', 's26301dbuser'--s26301dbuser為數(shù)據(jù)庫(kù)名
1.4.3向數(shù)據(jù)庫(kù)用戶授權(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)前使用的語(yǔ)言名稱 |
|
@@max_connections |
可以創(chuàng)建同時(shí)連接的最大數(shù)目 |
|
@@rowcount |
受上一個(gè)SQL語(yǔ)句影響的行數(shù) |
|
@@servername |
本地服務(wù)器的名稱 |
|
@@servicename |
該計(jì)算機(jī)上的SQL服務(wù)的名稱 |
|
@@timeticks |
當(dāng)前計(jì)算機(jī)上每刻度的微秒數(shù) |
|
@@transcount |
當(dāng)前連接打開(kāi)的事務(wù)數(shù) |
|
@@version |
SQL server的版本信息 |
2.2輸出語(yǔ)句.
Print局部變量或字符串,以字符串形式打印數(shù)據(jù) 。
Select 局部變量AS自定義列名，以表格形式打印數(shù)據(jù)。
2.3邏輯控制語(yǔ)句.
2.3.1.If-else
If(表達(dá)式)
Begin
語(yǔ)句1
語(yǔ)句2
End
Else
Begin
語(yǔ)句1
語(yǔ)句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(子查詢)
語(yǔ)句
If not exists(子查詢)
語(yǔ)句
四．事務(wù)，索引和視圖.
4.1事務(wù)
² 開(kāi)始事務(wù):begin transaction
² 提交事務(wù):commit transaction
² 回滾事務(wù):rollback transaction
事務(wù)分類
² 顯式事務(wù):用begin transaction 明確指定事務(wù)的開(kāi)始。
² 隱性事務(wù):通過(guò)設(shè)置set implicit_transactions on語(yǔ)句,將陷性事務(wù)模式設(shè)置為打開(kāi)。當(dāng)以隱性事務(wù)操作時(shí)，SQL server將在提交或回滾事務(wù)后撲克動(dòng)啟動(dòng)新事務(wù)。無(wú)法描述事務(wù)的開(kāi)始，只需提交或回滾每個(gè)事務(wù).
² 自動(dòng)提交事務(wù):這是SQL server的默認(rèn)模式，它將每條單獨(dú)的T-SQL語(yǔ)句視為一個(gè)事務(wù)。如果成功執(zhí)行，則自動(dòng)提交。如果錯(cuò)誤，則自動(dòng)回滾。
示例:
Begin transaction--開(kāi)始事務(wù)
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提供一種方法來(lái)編排查詢數(shù)據(jù)的路由
索引頁(yè)：
數(shù)據(jù)庫(kù)中的存儲(chǔ)索引的數(shù)據(jù)頁(yè)。索引頁(yè)存放檢索數(shù)據(jù)行的關(guān)鍵字頁(yè)以及該數(shù)據(jù)行的地址指針。索引頁(yè)類似于漢語(yǔ)字典中按拼音或筆畫排序的目錄頁(yè)。
唯一索引：
唯一索引不允許兩行具有相同的索引值.
主鍵索引：
在數(shù)據(jù)庫(kù)關(guān)系圖中為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個(gè)值是唯一的。當(dāng)在查詢中使用主鍵索引時(shí)，它還允許快速訪問(wèn)數(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í)間更長(zhǎng)
4.3視圖
視圖的用處
Ø 篩選表中的行
Ø 防止未經(jīng)許可的用戶訪問(wèn)敏感數(shù)據(jù)
Ø 降低數(shù)據(jù)庫(kù)的復(fù)雜程度
Ø 將多個(gè)物理數(shù)據(jù)表抽象為一個(gè)邏輯數(shù)據(jù)表
使用視圖的好處
ü 對(duì)最終用戶的好處
l 結(jié)果更容易理解。創(chuàng)建視圖時(shí)，可以將列名改為有意義的名稱，使用戶更容易理解列所代表的內(nèi)容。在視圖中修改列名不會(huì)影響基表的列名
l 獲得數(shù)據(jù)更容易。很多人對(duì)SQL不太了解，因此對(duì)他們來(lái)說(shuō)創(chuàng)建對(duì)多個(gè)表的復(fù)雜查詢很困難。因而可以通過(guò)創(chuàng)建視圖來(lái)方便用戶訪問(wèn)多個(gè)表中的數(shù)據(jù)。
ü 對(duì)開(kāi)發(fā)人員的好處
l 限制數(shù)據(jù)檢索更容易。開(kāi)發(fā)人員有時(shí)需要隱藏某些行或列中的信息。通過(guò)使用視圖，用戶可以靈活地訪問(wèn)他們需要的數(shù)據(jù)，同時(shí)保證同一個(gè)表或其他表的其他數(shù)據(jù)庫(kù)的安全性。要實(shí)現(xiàn)這一目標(biāo)，可以在創(chuàng)建視圖時(shí)將要對(duì)用戶保密碼的列排除在外。
l 維護(hù)應(yīng)用程序更方便。調(diào)試視圖比調(diào)試查詢更容易。跟蹤視圖中過(guò)程的各個(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ǔ)過(guò)程
5．1系統(tǒng)存儲(chǔ)過(guò)程
常用系統(tǒng)存儲(chǔ)過(guò)程
Sp_datadases 列出服務(wù)器上的所有數(shù)據(jù)庫(kù)
Sp_helpdb 報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息
Sp_renamedb 更改數(shù)據(jù)庫(kù)的名稱
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ǔ)過(guò)程
Sp_password 添加或修改登錄帳戶的密碼
Sp_helptext 顯示默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義的存儲(chǔ)過(guò)程、觸發(fā)器或視圖的實(shí)際文本
5．2自定義存儲(chǔ)過(guò)程
Ø 不帶參存儲(chǔ)過(guò)程
Ø 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程
Ø 帶輸出參數(shù)的存儲(chǔ)過(guò)程
5．2．1不帶參數(shù)的存儲(chǔ)過(guò)程
復(fù)制代碼 代碼如下:
Create proc procedureName
As
SQL 語(yǔ)句
Go
調(diào)用語(yǔ)法
Exec procedureName
5．2．2帶輸入?yún)?shù)的存儲(chǔ)過(guò)程
復(fù)制代碼 代碼如下:
Create proc procedureName
@number int =默認(rèn)值,
@n varchar(20)
As
SQL 語(yǔ)句
Go
調(diào)用語(yǔ)法:
Exec procedureName 200,'lyh'
Exec procedureName @n='lyh'
5．2．3帶輸出參數(shù)的存儲(chǔ)過(guò)程
復(fù)制代碼 代碼如下:
Create proc procedureName
@number int output,
@name char(20)
As
SQL 語(yǔ)句
Set @number=1000
Go
調(diào)用語(yǔ)法
Declare @dd int
Exec procedureName @dd output,'lyh'
六.SQL server觸發(fā)器
什么是觸發(fā)器:
觸發(fā)器是在對(duì)表進(jìn)行插入，更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。
觸發(fā)器的類別
INSERT觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。
UPDATE觸發(fā)器：當(dāng)更新表中某列、多列時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。
DELETE觸發(fā)器：當(dāng)刪除表中記錄時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。
Deleted表：用于存儲(chǔ)DELETE和UPDATE語(yǔ)句所影響的行的副本，即在DELETED表中臨時(shí)保存了被刪除或被更新前的記錄行。在執(zhí)行DELETE或UPDATE語(yǔ)句 ，行從觸發(fā)器表中刪除，并傳輸?shù)紻ELETED表中。由此我們可以從DELETED表中檢查刪除的數(shù)據(jù)行是否能刪除。如果不能，就可以回滾撤銷此操作，因?yàn)橛|發(fā)器本身就是一個(gè)特殊的事務(wù)單元。
Inserted表：用于存儲(chǔ)INSERT和UPDATE語(yǔ)句所影響的行的副本，即在inserted表中臨時(shí)保存了被插入或被更新后的記錄行。在執(zhí)行INSERT或UPDATE語(yǔ)句時(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 語(yǔ)句
Go
Trigger_name:是觸發(fā)器的名稱。觸發(fā)器名稱必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫(kù)中必須唯一，?？梢赃x擇是否指定觸發(fā)器所有者名稱。
Table_name:是在其上執(zhí)行觸發(fā)器的表或視圖
With encryption：加密syscomments表中包含create trigger語(yǔ)句文本的條目。使用with encryption可防止將觸發(fā)器作為SQLserver復(fù)制的部分發(fā)布.
Create trigger：必須是批處理中的第一條語(yǔ)句，并且只能應(yīng)用到一個(gè)表中。
觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)建，不過(guò)觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)的外部對(duì)象。
