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

標籤：values   版本   tab   float   等於   cin   設定   switch   connect   

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    endelse    begin        語句1        語句2    end

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

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

declare @myavg floatselect @myavg=avg(writexam) from stumakeprint ＇本班平均分＇ + convert(varchar(5),@myavg)if(@myavg>70)    begin        print ＇前三的成績為＇        select top 3 * from stumake order by writexam dese    endelse    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 intwhere(1=1) --條件永遠成立  begin    select @n=count(*) from stuMarks             where writtenExam<60    --統計不及格人數    if (@n>0)       update stuMarks     --每人加2分       set writtenExam=writtenExam+2    else       break    --退出迴圈  endprint ‘加分後的成績如下：‘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‘   endfrom 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+210             when labExam between 80 and 89 then labExam+111             else labExam12           end13      select @labAvg=avg(labExam) from stuMarks   --擷取目前平均分，判斷是否還據需加分14      if @labAvg>=8515            break               --如果已經大於或等於85就執行break，跳出迴圈16        end17 select * from stuMarks  --加分後的成績

安全模式

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

登入驗證有兩種方式：

SQL身分識別驗證：適合於非windows平台的使用者或Internet使用者， 需要提供帳戶和密碼

Windows身分識別驗證：適合於windows平台使用者，不需要提供密碼，和windows整合驗證

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

建立登入

添加 Windows登入帳戶

exec sp_grantlogin   ‘jbtraining\ww123456‘ –網域名稱\使用者名稱

添加 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/

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