SQL server學習（三）T-SQL程式設計、邏輯控制語句和安全模式

T-SQL程式設計

T-SQL程式設計與C語言類似，隻是文法稍有不同而已，總體思想還是沒有變化的。多的就不說了，還是從變量開始。

變量也分為全局變量和局部變量，表示方式稍有不同。

局部變量：

　　局部變量必須以标記@作為字首，如@age

　　局部變量的使用也是先聲明，再指派

全局變量：

　　全局變量必須以标記@ @作為字首，如@@version

　　全局變量由系統定義和維護，我們隻能讀取，不能修改全局變量的值

局部變量

聲明局部變量文法為：declare @變量名 資料類型

例：declare @age int
   declare @name nvarchar(10)      

指派的文法為：

set @變量名 = 值 或 select @變量名 = 值

例：
set @name =＇張三＇
select @name = stuname from stu where stuid=＇101＇      

使用select方法指派需要注意的是：要確定篩選出來的資料隻有一條

來看一個例子：

1 /*--查找一壺清酒的資訊--*/
 2 declare @name varchar(8)  --學員姓名
 3 set @name='一壺清酒'        --使用set指派
 4 select * from stuInfo where stuName = @name
 5 /*--查找一壺清酒的左右同桌--*/
 6 declare @seat int  --座位号
 7 select  @seat=stuSeat from stuInfo  --使用select指派
 8 where stuName=@name
 9 select * from stuInfo 
10    where (stuSeat = @seat+1) or (stuSeat = @seat-1)
11 go      

全局變量

聲明全部變量文法為：declare @@變量名 資料類型

常用的全局變量如下表所示：

變量	含義
@@ERROR	最後一個T-SQL錯誤的錯誤号
@@IDENTITY	最後一次插入的辨別值
@@LANGUAGE	目前使用的語言的名稱
@@MAX_CONNECTIONS	可以建立的同時連接配接的最大數目
@@ROWCOUNT	受上一個SQL語句影響的行數
@@SERVERNAME	本地伺服器的名稱
@@TRANSCOUNT	目前連接配接打開的事務數
@@VERSION	SQL Server的版本資訊

再看一個例子：

1 print  'SQL Server的版本'+@@VERSION 
 2 print  '伺服器的名稱: '+@@SERVERNAME 
 3 insert into stuInfo(stuName,stuNo,stuSex,stuAge)   
 4      values('武松','s25328','男','23')
 5 --如果大于0表示上一條語句執行有錯誤
 6 print '目前錯誤号'+convert(varchar(5),@@ERROR)
 7  print '剛才報名的學員，座位号為:'  
 8               +convert(varchar(5),@@IDENTITY )
 9 update stuinfo set stuAge=85 
10        where stuName='一壺清酒'
11 print '目前錯誤号'+convert(varchar(5),@@ERROR) 
12 go      

error指的是錯誤号，傳回為0表正确，沒有錯誤

邏輯控制語句

在c語言中，邏輯控制語句是if-else，switch，for，while，在sql程式設計中，也大相徑庭，這裡的邏輯控制語句分别是：if-else，while，case-end語句

if-else語句

文法格式為：

if(條件)
    begin
        語句1
        語句2
    end
else
    begin
        語句1
        語句2
    end      

注意：begin…end相當于c語言中的大括号，有多條語句才使用begin…end，相當于begin…end之間的語句就是一個語句塊

看個執行個體更容易了解，例：

declare @myavg float
select @myavg=avg(writexam) from stumake
print ＇本班平均分＇ + convert(varchar(5),@myavg)
if(@myavg>70)
    begin
        print ＇前三的成績為＇
        select top 3 * from stumake order by writexam dese
    end
else
    begin
        print ＇後三名的成績為＇
        select top 3 * from stumake order by writexam asc
    end      

在使用T-SQL程式設計時，可以切換視圖來檢視結果，工具>選項>查詢結果>正常>顯示結果的預設方式>以文本格式顯示結果，檢視效果更好

while循環語句

文法格式為：

while(條件)
    begin
        語句1
        語句2
        break
    end      

注意：break是表示結束循環，與c語言中的輔助控制語句break，continue類似

看個執行個體：

declare @n int
while(1=1) --條件永遠成立
  begin
    select @n=count(*) from stuMarks 
            where writtenExam<60    --統計不及格人數
    if (@n>0)
       update stuMarks     --每人加2分
       set writtenExam=writtenExam+2 
   else
       break    --退出循環
  end
print '加分後的成績如下：'
select * from stuMarks      

case-end多分支語句

文法格式為：

case
    when 條件1 then 結果1
    when 條件2 then 結果2
    else 其他結果
end      

看個成績等級執行個體就很清楚了，例：

print  'ABCDE五級顯示成績如下：'
select stuNo,成績= case
         when writtenExam<60 then 'E'
         when writtenExam between 60 and 69 then 'D'
         when writtenExam between 70 and 79 then 'C'
         when writtenExam between 80 and 89 then 'B'
         else  'A'
   end
from stuMarks      

注意：之前代碼中出現過go，批處理标志是go，批處理是将一些邏輯相關的業務操作語句放置在同一批中，同時操作

再看一個執行個體：

根據如下規則對機試成績進行反複加分，直到平均分超過85分為止。請編寫T-SQL語句實作。

90分以上：   不加分

80－89分：   加1分

70－79分：   加2分

60－69分：   加3分

60分以下：   加5分

1 select * from stuMarks  --原始成績
 2 declare @labAvg int      --聲明變量，使用者臨時存放平均分
 3 while(1=1)                   --條件一直成立
 4       begin
 5            update stuMarks
 6           set labExam=case
 7             when labExam<60 then labExam+5     --設定加分條件
 8             when labExam between 60 and 69 then labExam+3
 9             when labExam between 70 and 79 then labExam+2
10             when labExam between 80 and 89 then labExam+1
11             else labExam
12           end
13      select @labAvg=avg(labExam) from stuMarks   --擷取目前平均分，判斷是否還據需加分
14      if @labAvg>=85
15            break               --如果已經大于或等于85就執行break，跳出循環
16        end
17 select * from stuMarks  --加分後的成績      

安全模式

在SQL server學習（一）的文章中，有提到過安全模式（設定權限），但都是用手動操作，現在講一種代碼實作的方式

登入驗證有兩種方式：

SQL身份驗證：适合于非windows平台的使用者或Internet使用者， 需要提供帳戶和密碼

Windows身份驗證：适合于windows平台使用者，不需要提供密碼，和windows內建驗證

登入帳戶相應有兩種：SQL 帳戶和Windows帳戶

建立登入

添加 Windows登入帳戶

exec sp_grantlogin 'HONG-PJ3108YT43\Administrator' –域名\使用者名，域名為本機計算機名，使用者名為計算機下的使用者      

添加 SQL登入帳戶

exec sp_addlogin  ‘yihuqingjiu', ‘123456’         --使用者名,密碼      

exec表示調用存儲過程，存儲過程類似C語言的函數,在後面文章會講到

建立資料庫使用者

建立資料庫使用者需要調用系統存儲過程sp_grantdbaccess，其用法為：

exec sp_grantdbaccess ‘登入帳戶名’，’資料庫使用者名’      

其中，“資料庫使用者“為可選參數，預設為登入帳戶，即資料庫使用者預設和登入帳戶同名。

在資料庫中添加兩個使用者

exec sp_grantdbaccess 'jbtraining\ww123456', 'ww123DBUser' 
exec sp_grantdbaccess 'yihuqingjiu', 'yiDBUser'      

系統内置的資料庫使用者

dbo使用者和guest使用者

dbo使用者：表示資料庫的所有者（DB Owner），無法删除dbo使用者，此使用者始終會出現在每個資料庫中

guest來賓使用者：适用于沒有資料庫使用者的登入賬号通路，每個資料可有也可删除，來賓使用者需要管理者授權，不然無法操作資料庫

授權的文法

grant 權限 [on  表名 ]  to  資料庫使用者      

例：

/*--為yiDBUser配置設定對表stuInfo的select, insert, update權限--*/
grant select, insert, update on  stuInfo  to  yiDBUser  
/*--為ww123DBUser配置設定建表的權限--*/
grant  create  table  to  ww123DBUser      

這樣操作比之前的方法可是要簡單了很多

本文僅代表作者觀點，系作者@溫一壺清酒發表。歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。文章出處：http://www.cnblogs.com/hong-fithing/      

轉載于:https://www.cnblogs.com/hong-fithing/p/7637948.html