15 個常用的 SQL Server 高階語法

-----------------case--end---語法結構---------------------

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

------------------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

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

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

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

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

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

--分頁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  --要查詢的開始條數和結束條數

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
            第一個表的每一行和後面表的每一行進行連線
            沒有條件。是其它連線的基礎

create view v_Demo
as
select ......

---------------------------------區域性變數--------------------------
--宣告變數:使用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

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

---------------------------模擬轉賬----------------------------
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 --提交失敗,回滾事務

定義儲存過程的語法
    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

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

  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)--保留兩位小數

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()

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