之前我們簡單了解了各種查詢的用法，然而在實(shí)際開發(fā)中還會用到一些比較高級的數(shù)據(jù)處理和查詢，包括索引、視圖、存儲過程和觸發(fā)器。從而能夠更好地實(shí)現(xiàn)對數(shù)據(jù)庫的操作、診斷及優(yōu)化。

成都創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的弓長嶺網(wǎng)站設(shè)計(jì)、移動媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

什么是索引呢，索引是 SQL Server 編排數(shù)據(jù)的內(nèi)部方法，他為 SQL Server 提供了一種方法來編排查詢數(shù)據(jù)的路由，從而達(dá)到通過使用索引來提高數(shù)據(jù)庫的檢索速度、改善數(shù)據(jù)庫性能。

索引也是分為以下六類：
1、唯一索引：不允許兩行具有相同的索引值，創(chuàng)建了唯一約束，將會自動創(chuàng)建唯一索引。
2、主鍵索引：是唯一索引的特殊類型，將表定義一個主鍵時將自動創(chuàng)建主鍵索引，他要求主鍵中的每個值都是唯一的。
3、聚集索引：在聚集索引中，表中各行的物理順序和鍵值的邏輯索引順序相同。（注意：一個表中只能包含一個聚集索引）
4、非聚集索引：建立在索引頁上，在查詢數(shù)據(jù)時可以從索引中找到記錄存放的位置，聚集索引比非聚集索引有更快的數(shù)據(jù)訪問速度。
5、復(fù)合索引：可以將多個列組合為索引。
6、全文索引：是一種特殊類型的基于標(biāo)記的功能性索引，主要用于在大量文本中搜索字符串。
創(chuàng)建唯一索引：（不可有重復(fù)值）

create unique nonclustered index U_cardID 
on TStudent (cardID)

查看表上的索引：

Select * from sys.sysindexes 
where id=(select object_id from sys.all_objects where name='Tstudent')

按照指定的索引進(jìn)行查詢：

SELECT * FROM xueyuan
    WITH (INDEX = IX_name)
    WHERE 學(xué)員姓名 LIKE '孫%'

視圖是一種虛擬表，通常是作為來自一個或多個表的行或列的子集創(chuàng)建的。
視圖的作用就是：
1、篩選表中的數(shù)據(jù)
2、防止未經(jīng)允許的用戶訪問敏感數(shù)據(jù)
3、將多個物理數(shù)據(jù)表抽象為一個邏輯數(shù)據(jù)表
對用戶的好處就是：結(jié)果更容易理解、獲得數(shù)據(jù)更容易
對開發(fā)人員的好處就是：限制數(shù)據(jù)檢索更容易、維護(hù)應(yīng)用程序更方便
注意事項(xiàng)：
1、每個視圖中可以使用多個表
2、與查詢相似，一個視圖可以嵌套另一個視圖，最好不要超過三層
3、試圖定義的 select 語句不能包括以下：

• ORDER BY 子句，除非在 select 語句中的選擇列表中也有一個 TOP 子句
• INTO 關(guān)鍵字
• 引用臨時表或表變量
  創(chuàng)建視圖：

  create view netstudent
  as
  select Sname,sex,Class from dbo.TStudent where Class='網(wǎng)絡(luò)班'

  從視圖中查找數(shù)據(jù)：

  select * from netstudent 
  where sex='男'

  創(chuàng)建視圖、更改列的表頭：

  create view V_Tstudent1
  as
  select StudentID  學(xué)號,Sname 姓名,sex  性別,cardID  ×××號碼,Birthday  生日,Class  班級 from dbo.TStudent
  select * from V_Tstudent1

  什么是存儲過程，存儲過程就是 SQL 語句和控制語句的預(yù)編譯集合，保存在數(shù)據(jù)庫里，可由應(yīng)用程序調(diào)用執(zhí)行。
  那為什么需要存儲過程呢，因?yàn)閺目蛻舳耍╟lient）通過網(wǎng)絡(luò)向服務(wù)器（server）發(fā)送 SQL 代碼并執(zhí)行是不妥當(dāng)?shù)?，?dǎo)致數(shù)據(jù)可能會泄露不安全，印象了應(yīng)用程序的運(yùn)行性能，而且網(wǎng)絡(luò)流量大。
  使用存儲過程的優(yōu)點(diǎn)就是：
  1、模塊化程序設(shè)計(jì)
  2、執(zhí)行速度快、效率高
  3、減少網(wǎng)絡(luò)流量
  4、具有良好的安全性
  存儲過程分為兩類：系統(tǒng)存儲過程和用戶自定義的存儲過程
  系統(tǒng)存儲過程：
  是一組預(yù)編譯的T-SQL語句，提供了管理數(shù)據(jù)庫的更新表的機(jī)制，并充當(dāng)從系統(tǒng)表中檢索信息的快捷方式
  以“sp” 開頭，存放在 Resource數(shù)據(jù)庫中，常用的系統(tǒng)存儲過程有如下：
  
  使用 T-SQL 語句調(diào)用執(zhí)行存儲過程的語法：

  EXEC [UTE] 存儲過程名 [參數(shù)值]
  EXEC為EXECUTE的簡寫

  常用系統(tǒng)存儲過程的用法：

  exec  sp_databases      --列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫
  exec  sp_renamedb  'mybank','bank'   --改變數(shù)據(jù)庫名稱(單用戶訪問)
  use  MySchool
  go 
  exec sp_tables                       --當(dāng)前數(shù)據(jù)庫中可查詢對象的列表
  exec sp_columns student            --查看表student中列的信息
  exec  sp_help student               --查看表student的所有信息
  exec sp_helpconstraint student       --查看表student表的約束
  exec sp_helptext view_student_result   --查看視圖的語句文本
  exec sp_stored_procedures      --返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表

  根據(jù)系統(tǒng)存儲過程的不同作用，系統(tǒng)存儲過程可以分為不同類，擴(kuò)展存儲過程是 SQL Server 提供的各類系統(tǒng)存儲過程中的一類。
  允許使用其他編程語言（如C#）創(chuàng)建外部存儲過程，提供從 SQL Server 實(shí)例到外部程序的接口
  以“xp”開頭，以DLL形式單獨(dú)存在
  一個常用的擴(kuò)展存儲過程為 xp_cmdshell他可完成DOS命令下的一些操作，就以它為例舉
  語法為：
  **EXEC xp_cmdshell DOS命令 [NO_OUTPUT]**
  一般 xp_cmdshell 作為服務(wù)器安全配置的一部分被關(guān)閉，應(yīng)使用如下語句啟用：

  exec sp_configure  'show advanced options', 1   --顯示高級配置選項(xiàng)（單引號中的只能一個空格隔開）
  go 
  reconfigure                                    --重新配置
  go 
  exec sp_configure  'xp_cmdshell',1                 --打開xp_cmdshell選項(xiàng)
  go
  reconfigure                                    --重新配置

  啟用之后執(zhí)行如下語句：

  exec  xp_cmdshell  'mkdir  c:\bank',no_output  --創(chuàng)建文件夾c:\bank
  exec  xp_cmdshell  'dir c:\bank\'               --查看文件 

  用戶自定義的存儲過程：
  一個完整的存儲過程包括

• 輸入?yún)?shù)和輸出參數(shù)
• 在存儲過程中執(zhí)行的T-SQL語句
• 存儲過程的返回值
  用SSMS創(chuàng)建存儲過程
  一個完整的存儲過程包括以下三部分：
  1、輸入和輸出參數(shù)
  2、在存儲過程中執(zhí)行的 T-SQL 語句
  3、存儲過程的返回值
  使用 T-SQL 語句創(chuàng)建存儲過程的語法為：

  CREATE  PROC[EDURE]  存儲過程名 
      [ {@參數(shù)1  數(shù)據(jù)類型 } [= 默認(rèn)值] [OUTPUT],
         ……,
        {@參數(shù)n  數(shù)據(jù)類型 } [= 默認(rèn)值] [OUTPUT]
      ]
  AS
      SQL語句

  刪除存儲過程的語法為：
  DROP PROC[EDURE] 存儲過程名
  舉個例子，實(shí)現(xiàn)查詢該課程最近一次考試的平均分：

  use schoolDB
  go
  if exists  (select  *  from  sysobjects where name='usp_getaverageresult')
  drop  procedure  usp_getaverageresult
  go
  create  procedure  usp_getaverageresult
  as
  declare  @subjectid  nvarchar(4)
  select  @subjectid=subjectid  from  dbo.TSubject where subJectName='網(wǎng)絡(luò)管理'
  declare  @avg decimal (18,2)
  select  @avg=AVG(mark)  from  dbo.TScore where subJectID=@subjectid
  print  '網(wǎng)絡(luò)管理專業(yè)平均分是:'+convert(varchar(5),@avg)
  go

  編寫完畢之后執(zhí)行：exec usp_getaverageresult
  觸發(fā)器：
  是在對表進(jìn)行增、改或刪操作時自動執(zhí)行的存儲過程
  用于強(qiáng)制業(yè)務(wù)規(guī)則，可以定義比用 CHECK 約束更為復(fù)雜的約束
  通過事件觸發(fā)而被執(zhí)行的
  觸發(fā)器分為三類：
  INSERT觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時觸發(fā)
  UPDATE觸發(fā)器：當(dāng)更新表中某列、多列時觸發(fā)
  DELETE觸發(fā)器：當(dāng)刪除表中記錄時觸發(fā)
  inserted表和deleted表
  由系統(tǒng)管理，存儲在內(nèi)存而不是數(shù)據(jù)庫中，因此，不允許用戶直接對其修改
  臨時存放對表中數(shù)據(jù)行的修改信息
  當(dāng)觸發(fā)器工作完成，它們也被刪除
  

觸發(fā)器的作用就是：強(qiáng)化約束、跟蹤變化、級聯(lián)運(yùn)行
創(chuàng)建觸發(fā)器的語法為：

create trigger *triggername(觸發(fā)器名)*
on *tablename(表名)*
[with encryption]
for {[delete，insert，update]}
as SQL 語句

例：創(chuàng)建觸發(fā)器，禁止修改admin表中的數(shù)據(jù)

create  trigger  reminder
on  admin
for  update
as
print  '禁止修改，請聯(lián)系DBA'
rollback  transaction
go

然后執(zhí)行語句查看錯誤信息：

update Admin set  LoginPwd='123'  where  LoginId='benet'
select  *  from  Admin

事務(wù)（一般用在銀行交易這一方面，如轉(zhuǎn)賬）
是一個不可分割的工作邏輯單元
一組命令，要么都執(zhí)行，要么都不執(zhí)行
事務(wù)作為單個邏輯工作單元執(zhí)行的一系列操作，一個邏輯單元必須具備四個屬性：原子性、一致性、隔離性、持久性，這些特性通常簡稱為ACID。
舉個例子，以轉(zhuǎn)賬為準(zhǔn)
首先創(chuàng)建表名為bank：

為 Currentmoney列的Check約束：

插入兩條數(shù)據(jù)：

INSERT INTO bank(customerName,currentMoney) VALUES('張三',1000)
INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)

然后輸入代碼事務(wù)執(zhí)行：

select customername,currentmoney as 轉(zhuǎn)帳事務(wù)前的余額 from bank    --查看轉(zhuǎn)賬事務(wù)前的余額
go
begin transaction     -- 開始事務(wù)（指定事務(wù)從此開始，后續(xù)的T-SQL語句是一個整體）
declare @errorsum int   --定義變量，用于累計(jì)事務(wù)執(zhí)行過程中的錯誤 
set @errorsum=0    --初始化為0，即無錯誤
update bank set currentmoney=currentmoney-1000  --轉(zhuǎn)賬，張三賬戶少1000 李四賬戶多1000 
where customername='張三'
set @errorsum=@errorsum+@@ERROR    --累計(jì)是否有錯誤
update bank set currentmoney=currentmoney+1000
where customername='李四'
set @errorsum=@errorsum+@@ERROR   --累計(jì)是否有錯誤
select customername,currentmoney as 轉(zhuǎn)帳事務(wù)過程中的余額 from bank    --查看那轉(zhuǎn)賬過程中的余額
if @errorsum<>0  --如果有錯誤
begin 
print '交易失敗，回滾事務(wù)'
rollback transaction 
end
else 
begin 
print '交易成功，提交事務(wù)，寫入硬盤，永久的保存'
commit transaction 
end
go
select customername,currentmoney as 轉(zhuǎn)帳事務(wù)后的余額 from bank   --查看轉(zhuǎn)賬后的余額

轉(zhuǎn)賬失敗：

轉(zhuǎn)賬成功：

鎖：
多用戶能夠同時操縱同一個數(shù)據(jù)庫中的數(shù)據(jù)，會發(fā)生數(shù)據(jù)不一致的現(xiàn)象，鎖就是能夠在多用戶環(huán)境下保證數(shù)據(jù)的完整性和一致性
鎖的三種模式：
共享鎖（S鎖）：用于讀取資源所加的鎖。
排他鎖（X鎖）：和其他鎖不兼容，包括其他排他鎖。
更新鎖（U鎖）：U鎖可以看做S鎖和X鎖的結(jié)合，用于更新數(shù)據(jù)。
查看鎖：
使用sys.dm_tran_locks動態(tài)管理視圖
使用Profiler來捕捉鎖信息
死鎖
死鎖的本質(zhì)是一種僵持狀態(tài)，是由多個主體對資源的爭用而導(dǎo)致的。
形成死鎖的條件是：
1、互斥條件：主體對資源是獨(dú)占的
2、請求與等待條件
3、不剝奪條件
4、環(huán)路等待條件
預(yù)防死鎖：
破壞互斥條件
破壞請求與等待條件
破壞不剝奪條件

文章題目：淺談SQLServer查詢優(yōu)化與事務(wù)處理
當(dāng)前地址：http://chinadenli.net/article10/gidjdo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站制作、品牌網(wǎng)站建設(shè)、ChatGPT、靜態(tài)網(wǎng)站、營銷型網(wǎng)站建設(shè)、網(wǎng)站維護(hù)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)