SQLServer 編程總結
case的用途
case語句有四個關鍵字,缺一不可,分別是:case when then end，另外還有一個else。case 后面跟字段名（當在when后面出現字段名時，case里不能寫出來），when后面跟判斷語句，then后面跟顯示結果， end表示case語句結束。
--第一種用法 用于簡單的是非判斷(字段名在case后面) select sname,case ssex when 1 then '男' else '女' end as sex from student --第二種 進行稍微復雜一些的判斷（字段名在when后面） select sname,case when ssex = 1 then '男' when ssex =0 then '女' end as sex from student
for xml path
for xml path是將結果集以XML的格式返回。用的比較多的地方，就是group by之后，拼接字符串返回。
示例：
--創建一張表subjects，有三個人，分別選修了不同的科目 create table subjects(username varchar(20),subjects_name varchar(20)) insert into subjects (username,subjects_name) values ( '譚' ,'高數' ), ( '譚','音樂' ), ( '譚譚','體育' ), ( '譚譚','武術' ), ( '譚譚','化學' ), ( '譚譚譚','生物' )
根據上面的內容，我們就可以查詢每個人學習的科目，并把這些科目放在一起（用逗號分隔）。
--使用stuff去除首位分隔符
select s.username,stuff(( select ','+b.subjects_name from subjects as b where b.username=s.username for xml path('')),1,1,'') as subjects
from subjects as s
group by username
除了上面的用法，也可是 FOR XML PATH可以用來拼接JSON字符串或字符串。例如：
-- STUFF結合FOR XML PATH可以用來拼接JSON字符串
select '['+ stuff((select ',{"name": "' + username + '","subjects": "' + subjects_name + '"}'
from subjects for xml path('')),1,1,'') +']'
while循環與游標
while語句使用示例
declare @i int
set @i = 1
while @i<5
begin
--insert into student (sname,sage,ssex) values ('tanyongjun',12,1)
print @i
set @i = @i +1
end
游標示例
--1.聲明游標 declare test_cursor cursor scroll for select sname,sage from student where ssex = 1 --2.打開游標 open test_cursor --3.聲明游標提取數據所要存放的變量 declare @sname varchar(50), @sage int --4.定位游標到哪一行 fetch first from test_cursor into @sname,@sage --into的變量數量必須與游標查詢結果集的列數相同 while @@fetch_status=0 --提取成功，進行下一條數據的提取操作 begin --進來業務處理 --例如：把 sname為 tanyongjun 的數據中 sage 字段 加+1，進行修改 if @sname='tanyongjun' begin update student set sage = sage+1 where current of test_cursor -- 如果是修改當前行，就可以用上面這中寫法 end -- 當然也可以根據提取的數據作為條件進行處理 fetch next from test_cursor into @sname,@sage --移動游標 end --5.關閉游標 close test_cursor --6.刪除游標，釋放資源 deallocate test_cursor
總結：
1. while循環和游標都可以實現循環的目的，while用法比較簡潔明了；
2. 游標是在while的基礎上實現循環的功能，但是語法較復雜，且效率沒有while高，所以一般不推薦使用；
3. while循環體內支持再嵌套一個循環，但是一定要注意不能出現死循環，否則sql服務就會卡死、崩潰。
exists
exists是用來判斷是否存在的，當exists查詢中的查詢存在結果時則返回真，否則返回假。not exists則相反。
exists做為where 條件時，是先對where 前的主查詢詢進行查詢，然后用主查詢的結果一個一個的代入exists的查詢進行判斷，如果為真則輸出當前這一條主查詢的結果，否則不輸出。
查詢時，一般情況下，子查詢會分成兩種情況：
1.子查詢與外表的字段有關系時
例如： select col1 , col2 from t1 where exists (select col1 , col2 from t2 where t2.col1 = t1.col1)
上面的sql語句相當于一個關聯查詢。它先執行t1的查詢，然后把t1中的每一條記錄放到t2的條件中去查詢，如果存在，則顯示此條記錄。
2.子查詢與外表的字段沒有任何關聯
例如： select col1 , col2 from t1 where exists ( select * from t2 where t2.col1 = '條件')
在這種情況下，只要子查詢的條件成立，就會查詢出t1中的所有記錄，反之，如果子查詢中沒有查詢到記錄，則t1不會查詢出任何的記錄。
當子查詢與主表不存在關聯關系時，簡單認為只要exists為一個條件判斷，如果為true,就輸出所有記錄。如果為false則不輸出任何的記錄。
json操作
主要介紹5個函數：
1. openJson:打開Json字符串
2. IsJson:判斷一個字符串是不是合法的Json格式。是返回1，否返回0，null返回null。
3. Json_Value:從Json字符串中提取值。
4. Json_Query:Json字符串中提取對象或數組。
5. Json_Modify:更新Json字符串中的屬性值，并返回更新的Json字符串。
--IsJson:判斷一個字符串是不是合法的Json格式。是返回1，否返回0，null返回null。
select isjson('{"id":"1","name":"tanyongjun","sex":1}')
select isjson('tan')
--從Json字段中提取值
select json_value('{"id":"1","name":"tanyongjun","sex":1}','$.name')
函數和存儲過程
一、自定義函數
自定義函數分為：標量值函數或表值函數兩種。
標量值函數：如果 RETURNS 子句指定一種標量數據類型，則函數為標量值函數。（返回字段）
表值函數：如果 RETURNS 子句指定 TABLE，則函數為表值函數。（返回table）
表值函數又分為兩種：內嵌表值函數（行內函數）或多語句函數
如果 RETURNS 子句指定的 TABLE 不附帶列的列表，則該函數為內嵌表值函數。
如果 RETURNS 子句指定的 TABLE 類型帶有列及其數據類型，則該函數是多語句表值函數
--標量值函數
create function fun_add_num(@age int)
returns int
as
begin
declare @num int
set @num = 2
return @age + @num
end
go
-- 調用 *注：必須加上dbo.否則會報錯
select dbo.fun_add_num(11)
go
--內嵌表值函數
create function fun_test(@name varchar(50))
returns table
as
return (select sname,sage,ssex from student where sname = @name)
go
--調用 在調用 表值函數 時 dbo. 可以省略
select * from fun_test('tanyongjun')
go
-- 多語句表值函數 ****分割字符串，然后返回
create function fun_split_str
(
@str_val varchar(2000), --要分割的字符串
@split varchar(10) --分隔符號
)
returns @temp table (result varchar(10))
as
begin
declare @result as varchar(10); --定義變量用于接收單個結果
set @str_val = @str_val + @split ;
while (@str_val <> '')
begin
set @result = left(@str_val, charindex(@split, @str_val) -1) ;
insert @temp values(@result) ;
--STUFF()函數用于刪除指定長度的字符，并可以在指定的起點處插入另一組字符。
set @str_val = stuff(@str_val, 1, charindex(@split, @str_val), '');
end
return
end
go
--調用
select * from fun_split_str('tan,tanyong,tanyongjun',',')
注意事項：
標量函數：
1. 所有的入參前都必須加@
2. create后的返回，單詞是returns，而不是return
3. returns后面的跟的不是變量，而是返回值的類型，如：int，char等。
4. 在begin/end語句塊中，是return。
內嵌表值函數：
1. 只能返回table，所以returns后面一定是TABLE
2. AS后沒有begin/end，只有一個return語句來返回特定的記錄。
多語句表值函數：
1. returns后面直接定義返回的表類型，首先是定義表名，表明前面要加@，然后是關鍵字TABLE，最后是表的結構。
2. 在begin/end語句塊中，直接將需要返回的結果insert到returns定義的表中就可以了，在最后return時，會將結果返回。
3. 最后只需要return，return后面不跟任何變量。
二、存儲過程
-- 無參存儲過程 create proc proc_test1 as -- as 不能省略 begin select * from tb_test end go exec proc_test1 go --有參存儲過程 create proc proc_test2 @username varchar(50) as begin select * from tb_test where username = @username end exec proc_test2 'tanyongjun' --有參 有返回值的存儲過程 create proc proc_test3 @username varchar(50), @row_number int output as begin select @row_number = count(*) from tb_test where username = @username end go declare @row_num int exec proc_test3 'tanyongjun11', @row_num output -- 不寫output ，@row_num 不輸出任何信息 print @row_num
存儲過程和函數的使用：
1. 存儲過程和函數都可以將一段SQL語句進行封裝，這樣大大的方便了實際使用時候的調用步驟。
2. 存儲過程里面可以嵌套存儲過程，也經常會調用各種函數。
3. 函數里面可以調用其他函數，但是一般不嵌套存儲過程。
4. 上面例子中的函數，也可以改寫成存儲過程，但是函數使用起來要方便的多，因為函數可以直接用select聯用，但是存儲過程只能用exec執行。
常用函數（字符串、時間）
一、字符串
--計算字符串長度
select len('tanyongjun')
--字符串轉換為大、小寫
select lower('TAN') --將字符串轉換為小寫
select upper('tan') --將字符串轉換為大寫
--去字符串前后空格
select trim( ' tanyongjun ')
--刪除指定長度的字符，并在指定的起點處插入另一組字符
--stuff(character_expression , start , length ,character_expression)
--start 一個整數值，指定刪除和插入的開始位置。
--length 一個整數，指定要刪除的字符數。
select stuff('abcdefg',1,6,'Hello ')
--從左側開始取子字符串
select left('tanyongjun',3)
--從右側開始取子字符串
select right('tanyongjun',7)
--字符串替換
select replace('tanyongjun','yong','YONG')
--字符串拼接
select 'tan'+'yogjun' --用+號實現字符串拼接
select concat('tan','yongjun') --用concat()內置函數實現字符串拼接
二、時間
select convert(varchar(50),getdate(),23) -- 獲取當前日期 select convert(varchar(50),getdate(),108) -- 獲取當前時間
1. DATEADD (datepart , number, date )函數
用于進行日期時間的加法運算。其中datepart參數是表示日期部分的參數（比如是以日期還是以月份相加等），number參數是具體的加數，正數代表向未來日期方向加，負數代表向過去日期方向減，date參數為待計算的日期時間類型數據。
datepart參數可以取的單位類型如下：
例如：當前時間加上 3 天 ： select dateadd(dd,3,getdate())
2. DATEDIFF ( datepart , startdate , enddate )函數
用于計算兩個日期時間之間的差額。其中datepart參數表示日期部分的參數（比如只比較年還是只比較月等），startdate參數為起始日期時間類型數據；enddate參數為結束日期時間類型數據。
示例： select datediff(dd, '2023/1/17 ', '2023/1/19')
3. DATENAME(datepart,date)函數
用來獲取一個日期的特定部分，比方只獲取年份或者是只獲取月份等。其中datepart參數是表示要返回的日期部分的參數(即如果是Year的話，則返回的是具體的年份)，date參數為待計算日期。
示例： select datename(dd,getdate()) -- 獲取當前時間是 “幾號”
三、其他
-- cast 類型轉換 declare @temp1 int = 1 select cast(@temp1 as varchar(50) ) -- isnull 如果表達式為 NULL，則返回指定值，否則返回表達式 declare @temp2 varchar(50) select isnull(@temp2,'沒有內容') set @temp2 = 'tanyongjun' select isnull(@temp2,'沒有內容')
浙公網安備 33010602011771號