常用的 SQL 語法

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 '不及格'
   end
from 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 Score
declare @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 @i=@i+1
   end
select @i
update 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、分頁

--分頁1
select 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..)獲取行號

--分頁2
select * from
  (select *, row_number() over(order by [sid] desc ) as num from    student)as t
where num between (Y-1)*T+1 and Y*T
order 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 s
left 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_Demo
as
select ......

10、區域性變數

---------------------------------區域性變數--------------------------
--宣告變數:使用declare關鍵字，並且變數名已@開頭，@直接連線變數名,中間沒有空格。必須指明變數的型別，同時還可以宣告多個不同型別的變數。
declare @name nvarchar(30) ,@age int

--變數賦值：
--1、使用set 給變數賦值,只能給一個變數賦值
set @age=18
set @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 int
select @sage=sage from student where sName=@sname
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 tran
update bank set balance=balance-1000 where cId='0001'
set @sumError=@sumError+@@error 
update bank set balance=balance+1000 where cId='0002'
set @sumError=@sumError+@@error

if (@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_GroupMainlist1
go
create 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 int
exec usp_GroupMainlist1 1,100,@page output
select @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 列