SQL Server 常用進階文法筆記

標籤：

1、case...end （具體的值）
case後面有值，相當於c#中的switch case
注意：case後必須有條件，並且when後面必須是值不能為條件。

-----------------case--end---文法結構---------------------select name , --注意逗號   case level --case後跟條件       when 1 then ‘骨灰‘       when 2 then ‘大蝦‘       when 3 then‘菜鳥‘     end as‘頭銜‘from [user]

 

2、case...end （範圍）
case 後面無值，相當於c#中的if...else if...else....
注意：case後不根條件

------------------case---end--------------------------------select studentId,    case       when english between 80 and 90  then ‘優‘       when english between 60 and 79  then ‘良‘       else ‘差‘    end from Score------------------case---end--------------------------------select studentId,    case       when english >=80  then ‘優‘       when english >=60  then ‘良‘       else ‘差‘    end from Score-----------------------------------------------------select *,   case         when english>=60 and math >=60 then ‘及格‘         else ‘不及格‘   endfrom Score 

 

3、if...eles

IF(條件運算式)  BEGIN --相當於C#裡的{    語句1  ……  END --相當於C#裡的}ELSE BEGIN    語句1    ……  END--計算平均分數並輸出，如果平均分數超過分輸出成績最高的三個學生的成績，否則輸出後三名的學生declare @avg int --定義變數select @avg= AVG(english) from Score  --為變數賦值select ‘平均成績‘+CONVERT(varchar,@avg) --列印變數的值    if @avg<60           begin               select ‘前三名‘              select top 3 * from Score order by english desc          end    else         begin             select ‘後三名‘             select top 3 * from Score order by english           end

 

4、while迴圈

WHILE(條件運算式)  BEGIN --相當於C#裡的{    語句    ……    BREAK  END --相當於C#裡的}--如果不及格的人超過半數(考試題出難了)，則給每個人增加分select * from Scoredeclare @conut int,@failcount int,@i int=0  --定義變數select @conut =COUNT(*) from Score --統計總人數select @failcount =COUNT(*) from Score where english<100 --統計未及格的人數while (@failcount>@conut/2)   begin       update Score set english=english+1       select @failcount=COUNT(*) from Score where english<100       set @[email protected]+1   endselect @iupdate Score set english=100 where english >100

 

5、索引
使用索引能提高查詢效率，但是索引也是佔據空間的，而且添加、更新、刪除資料的時候也需要同步更新索引，因此會降低Insert、Update、Delete的速度。只在經常檢索的欄位上(Where)建立索引。
1）叢集索引：索引目錄中的和目錄中對應的資料都是有順序的。
2）非叢集索引：索引目錄有順序但儲存的資料是沒有順序的。

--建立非叢集索引CREATE NONCLUSTERED INDEX [IX_Student_sNo] ON student(    [sNo] ASC)

 

6、子查詢
將一個查詢語句做為一個結果集供其他SQL語句使用，就像使用普通的表一樣，被當作結果集的查詢語句被稱為子查詢。所有可以使用表的地方几乎都可以使用子查詢來代替。

select * from (select * from student where sAge<30) as t  --被查詢的子表必須有別名where t.sSex =‘男‘ --對子表中的列篩選

 

轉換為兩位小數：CONVERT(numeric(10,2), AVG(english))
只有返回且僅返回一行、一列資料的子查詢才能當成單值子查詢。

select ‘平均成績‘, (select AVG(english) from Score) --可以成功執行select ‘姓名‘, (select sName from student) --錯誤，因為‘姓名’只有一行，而子表中姓名有多行select * from student where sClassId in(select cid from Class where cName IN(‘高一一班‘,‘高二一班‘)) --子查詢有多值時使用in

 

7、分頁

--分頁1select top 3 * from student where [sId] not in (select top (3*(4-1)) [sid] from student)--4表示頁數select *, row_number() over(order by [sage] desc ) from student-- row_number() over (order by..)擷取行號--分頁2select * from  (select *, row_number() over(order by [sid] desc ) as num from    student)as twhere num between (Y-1)*T+1 and Y*Torder by [sid] desc

--分頁3
select * from (select ROW_NUMBER() over( order by [UnitPrice] asc) as num,* from [Books] where [publisherid]=1 )as t where t.num between 1 and 20  --要查詢的開始條數和結束條數

 

8、串連

select sName,sAge,   case      when english <60 then ‘不及格‘      when english IS null then ‘缺考‘       else CONVERT(nvarchar, english)   end as‘英語成績‘from student as sleft join Score as c on s.sid =c.sid內串連  inner join...on...        查詢滿足on後麵條件的資料    外串連            左串連            left join...on...            先查出左表中的所有資料            再使用on後面的條件對資料過濾        右串連            right join...on...            先查出右表中的所有資料            再使用on後面的條件對資料過濾        全串連            full join ...on...    （*）交叉串連         cross join 沒有on            第一個表的每一行和後面表的每一行進行串連            沒有條件。是其它串連的基礎

 

9.視圖
優點：

• 篩選表中的行
• 防止未經許可的使用者訪問敏感性資料
• 降低資料庫的複雜程度

建立視圖

create view v_Demoasselect ......

 

10、局部變數

---------------------------------局部變數----------------------------聲明變數:使用declare關鍵字，並且變數名已@開頭，@直接連接變數名,中間沒有空格。必須指明變數的類型，同時還可以聲明多個不同類型的變數。declare @name nvarchar(30) ,@age int--變數賦值：--1、使用set 給變數賦值,只能給一個變數賦值set @age=18set @name =‘Tianjia‘select @age,@name  --輸出變數的值--2、使用select 可以同時為多個變數賦值select @age=19,@name=‘Laoniu‘--3、在查詢語句中為變數賦值declare @sum int =18 --為變數賦初值select @sum= SUM(english) from Score --查詢語句中賦值select @sum --輸出變數值--4、變數作為條件使用declare @sname nvarchar(10)=‘張三‘ declare @sage intselect @sage=sage from student where [email protected]select @sage--5、使用print輸出變數值，一次只能輸出一個變數的值,輸出為文本形式print @sage 

 

11、全域變數

--------------------------全域變數（系統變數）----------------------------------select * from student0 select  @@error --最後一個T-SQL錯誤的錯誤號碼select @@max_connections--擷取建立的同時串連的最大數目select @@identity --返回最近一次插入的編號

 

12、事務
事務：同生共死
指訪問並可能更新資料庫中各種資料項目的一個程式執行單元(unit)--也就是由多個sql語句組成，必須作為一個整體執行
這些sql語句作為一個整體一起向系統提交，要麼都執行、要麼都不執行

文法步驟：

• 開始事務：BEGIN TRANSACTION
• 事務提交：COMMIT TRANSACTION
• 交易回復：ROLLBACK TRANSACTION

判斷某條語句執行是否出錯：
全域變數@@ERROR；
@@ERROR只能判斷當前一條T-SQL語句執行是否有錯，為了判斷事務中所有T-SQL語句是否有錯，我們需要對錯誤進行累計；

---------------------------類比轉賬----------------------------declare @sumError int=0 --聲明變數begin tranupdate bank set balance=balance-1000 where cId=‘0001‘set @[email protected][email protected]@error update bank set balance=balance+1000 where cId=‘0002‘set @[email protected][email protected]@errorif (@sumError=0)commit tran --提交成功,提交事務else rollback tran --提交失敗,復原事務

 

13、預存程序

預存程序---就像資料庫中運行方法(函數)
和C#裡的方法一樣，由預存程序名/預存程序參數組成/可以有返回結果。
前面學的if else/while/變數/insert/select 等，都可以在預存程序中使用
優點：

• 執行速度更快 - 在資料庫中儲存的預存程序語句都是編譯過的
• 允許模組化程式設計 - 類似方法的複用
• 提高系統安全性 - 防止SQL注入
• 減少網路流通量 - 只要傳輸 預存程序的名稱

系統預存程序
由系統定義，存放在master資料庫中
名稱以“sp_”開頭或”xp_”開頭

建立預存程序：

定義預存程序的文法    CREATE  PROC[EDURE]  預存程序名     @參數1  資料類型 = 預設值 OUTPUT,    @參數n  資料類型 = 預設值 OUTPUT    AS      SQL語句參數說明：參數選擇性參數分為輸入參數、輸出參數 輸入參數允許有預設值EXEC  過程名  [參數]----------------------例--------------------------if exists (select * from sys.objects where name=‘usp_GroupMainlist1‘)drop proc usp_GroupMainlist1gocreate proc usp_GroupMainlist1   @pageIndex int, --頁數   @pageSize int, --條數   @pageCount int output--輸出共多少頁as    declare @count int --共多少條資料   select @count =count(*) from [mainlist] --擷取此表的總條數   set @pageCount=ceiling(@count*1.0/@pageSize)       select * from    (select *,row_number() over(order by [date of booking] desc) as ‘num‘ from [mainlist]) as t   where num between(@pageSize*(@pageIndex-1)+1) and @pageSize*@pageIndex   order by [date of booking] desc---------------------------------------------------------------------------------------------調用   declare @page intexec usp_GroupMainlist1 1,100,@page outputselect @page

14、常用函數

   1）ISNULL(expression,value)     如果expression不為null返回expression運算式的值，否則返回value的值

   2）彙總函式

avg()  -- 平均值 統計時注意null不會被統計，需要加上isnull(列名，0)sum() -- 求和count() -- 求行數 min() -- 求最小值max() -- 求最大值

   3）字串操作函數

  LEN() --計算字串長度  LOWER() --轉小寫  UPPER () --大寫  LTRIM() --字串左側的空格去掉   RTRIM () --字串右側的空格去掉   LTRIM(RTRIM(‘         bb        ‘))  LEFT()、RIGHT() -- 截取取字串  SUBSTRING(string,start_position,length) -- 參數string為主字串，start_position為子字串在主字串中的起始位置（從1開始），length為子字串的最大長度。SELECT  SUBSTRING(‘abcdef111‘,2,3)   REPLACE(string,oldstr,newstr)Convert(decimal(18,2),num)--保留兩位小數

     4）日期相關函數

GETDATE() --取得當前日期時間  DATEADD (datepart , number, date )--計算增加以後的日期。參數date為待計算的日期；參數number為增量；參數datepart為計量單位，可選值見備忘。DATEADD(DAY, 3,date)為計算日期date的3天后的日期，而DATEADD(MONTH ,-8,date)為計算日期date的8個月之前的日期 DATEDIFF ( datepart , startdate , enddate ) --計算兩個日期之間的差額。 datepart 為計量單位，可取值參考DateAdd。  -- 擷取日期的某一部分 ：  DATEPART (datepart,date)--返回一個日期的特定部分 整數  DATENAME(datepart,date)--返回日期中指定部分 字串  YEAR()  MONTH()  DAY()

 

15、sql語句執行順序

  5>…Select 5-1>選擇列,5-2>distinct,5-3>top  1>…From 表  2>…Where 條件  3>…Group by 列  4>…Having 篩選條件  6>…Order by 列

 

 

SQL Server 常用進階文法筆記