MSSQL 常用指令

一、管理指令
1、停用mssqlserver服務，並同意停止其他相關服務
net stop mssqlserver /y

2、啟用mssqlserver服務
net start sqlserveragent

3、列出資料庫所有狀態
sp_helpdb

PS：服務名稱不是在 "服務"中顯示名稱，而是"服務名稱"

二、查詢
1、列出所有AC資料庫下所有資料表
USE AC

EXEC sp_tables ;

2、查詢資料庫版本
SELECT @@version

3、僅列出資料庫名稱
use master
SELECT dbid,DB_NAME(dbid) as DB_NAME
FROM sysdatabases order by dbid

4、取得欄位說明
如果要針對某個資料表的某個欄位取得
可以使用下面的語法

select value from ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', N'資料表名稱', 'column', N'欄位名稱')

如果要取得某個資料表中所有欄位說明的話,可以使用

select * from ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', N'資料表名稱', 'column',NULL)

5、Case when用法 來源

Case具有兩種格式。簡單Case函數和Case搜尋函數。

--簡單Case函數

CASE sex

WHEN  '1'  THEN  '男' 

WHEN  '2'  THEN  '女' 

ELSE  '其他'  

END 

--Case搜尋函數

CASE  

WHEN sex = '1'  THEN  '男' 

WHEN sex = '2'  THEN  '女' 

ELSE  '其他'  

END

這兩種方式，可以實現相同的功能。

簡單Case函數的寫法相對比較簡潔，但是和Case搜尋函數相比，功能方面會有些限制，例如寫判斷式。

還有一個要注意的問題，Case函數只會傳回第一個符合條件的值，剩下的Case部分將會自動忽略。

--比如說，下面這段SQL，你永遠無法得到「第二類」這個結果

CASE  

WHEN col_1 IN ( 'a' , 'b' ) THEN  '第一類' 

WHEN col_1 IN ( 'a' )        THEN  '第二類' 

ELSE '其他'  

END

三、修改
1、將自動編號歸零
TRUNCATE TABLE 資料表名稱

2、清空指定資料表內容資料
TRUNCATE  table  資料表名稱

3、變更資料型別
ALTER TABLE 表名 ALERT COLUMN 欄位名稱 資料型別 NOT NULL;
例如 ALTER TABLE abc ALERT COLUMN id Varchar(20)  NOT NULL;

四、刪除
1、刪除 table 資料表中的資料
DELETE FROM 資料表名稱


五、轉換
DATEPART ( datepart ,date ) 返回代表指定日期的指定日期部分的整數。
Select getdate()
, DatePart(yy, getdate()) as '年'
, DatePart(mm, getdate()) as '月'
, DatePart(dd, getdate()) as '日'
, DatePart(dayofyear, getdate()) as '本年一月一號至今的天數'
, DatePart(week , getdate()) as '第N週'
, DatePart(weekday , getdate()) as '星期幾(代號)' --星期日 = 1
             --星期一 = 2
             --星期二 = 3
             --星期三 = 4
             --星期四 = 5
             --星期五 = 6
             --星期六 = 7
, DATENAME(Weekday, GETDATE()) as '星期幾'
, DatePart(hour, getdate()) as '時'
, DatePart(minute, getdate()) as '分'
, DatePart(second, getdate()) as '秒'
, DatePart(millisecond, getdate()) as '毫秒'

六、常用內建函數型態轉換

型態轉換

CAST (運算式 AS 資料型別 [ (資料長度) ]) 

CONVERT ( 資料型別 [ (資料長度) ] , 運算式 [ , 日期格式樣式 ] )

日期

DATEADD(單位,加減值,日期) 

例 SELECT DATEADD(DAY,10, GETDATE()) # 2024-05-18 09:43:01.333

七、控制流語句

use Accounting

select [name], sex from people

--CASE語句

--依sex顯示稱謂

--方式一

select [name], sex, 

(case sex 

when 'M' then 'Gentlemen'

when 'F' then 'Miss'

else 'unknow'

 end) as title

from people

--方式二

select [name], sex, 

(case 

when sex ='M' then 'Gentlemen'

when sex ='F' then 'Miss'

else 'unknow'

end) 

from people

--用WHILE 語句進行循環

--while<Boolean Expression>

-- Begin

-- <sql statement>

-- [ break | continue ]

-- End

use Accounting

drop table test1

if not exists(select name from sys.tables where name ='dbo.test')

Begin

create table test1 (id int   , randNumber int null )

end

truncate table test1

declare @i int = 0

while @i < 1000  --利用循環插入資料

begin

set @i=@i+1

if @i = 500 break 

if @i % 2 = 0 continue  --偶數不執行下列指令

insert into test1 values (@i, cast(rand()*10000 as int ))

end

--WAITFOR 語句

--waitfor delay <'時段'>  

--waitfor time <'時刻'>

waitfor delay '00:00:05' --等5秒

--TRY/CATCH 語句

--begin try

-- <sql statement(s)>

--end try

--begin catch

-- <sql statement(s)>

--end catch

begin try

create table test1 (id int   , randNumber int null )

end try

begin catch

print 'error level: ' + cast(error_severity() as varchar())

print 'message:' + error_message()

end catch

select * from test1

七、存儲過程(預存程序)

• 優點：
• 安全性 
• 儲存在數據庫中

--建立預存，並含參數及輸出結果

CREATE PROC sp_select_id

@TableID int, @OutResult varchar(50) OUTPUT

AS

BEGIN

DELETE FROM AdventureWorks2019.dbo.DynamicSQLExample                                    WHERE TableID  =(@TableID)

DECLARE @MessageRecode int 

SET @MessageRecode = @@ROWCOUNT

SET @OutResult = 'A total recodes affected by ' +                                                         CAST(@MessageRecode as varchar(20)) 

END

--呼叫預存

DECLARE @OutMessage VARCHAR(50), @ReturnResult INT

EXEC @ReturnResult=sp_select_id 52, @OutMessage OUTPUT

SELECT @OutMessage AS 輸出執行訊息, @ReturnResult AS 返回結果

八、錯誤處理

常見錯誤類型：

1. 產生運行時錯誤並終止代碼繼續運行
2. SQL Server 知道的，但不產生使代碼停用運行的
3. 更具邏性但在SQL Server 中不太引起注意的

利用＠＠Error 取得錯誤代碼

例如：

alter proc sp_test1_createTable

as

begin

declare @Error int

begin try

create table test1 (id int)

end try

begin catch

set @Error = @@ERROR

end catch

SELECT message_id AS Error,  --查詢錯誤代碼意思

severity AS Severity,

[Event Logged] = CASE is_event_logged

WHEN 0 THEN 'No' ELSE 'Yes'

END,

[text] AS [Description]

FROM sys.messages

WHERE language_id = 1040 /* replace 1040 with the desired language ID, such as 1033 for US English */

and message_id  = @Error

ORDER BY message_id;

end 

exec sp_test1_createTable

九、自定義函數

-- 建立自定義函數

--呼叫getYear()計算年紀，找出20~25歲人員並且住在台南及台中清單

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION def_getAgeAndCityPeopleList(

@startAge int,

@endAge int,

@cityName1 nvarchar(50),

@cityName2 nvarchar(50))

RETURNS Table

AS

RETURN

(

SELECT [Name],Sex,Birthday,Age,AddressCityName 

FROM Accounting.dbo.people AS people join Accounting.dbo.AddressInfo as AddressInfo 

ON people.AddressInfoID = AddressInfo.AddressInfoID 

WHERE 

dbo.def_getYear(Birthday) between @startAge and @endAge

and AddressCityName in (@cityName1,@cityName2)

)

GO

--呼叫自定義函數

select * from def_getAgeAndCityPeopleList(20,35,'台中','台南')

九、觸發器

觸發器的概念

觸發器的應用在：

1.資料定義語言(DDL)觸發器

2.資料操縱語言(DML)觸發器

觸發器附加的事件：

1. INSERT觸發器

2. DELETE觸發器

3. UPDATE觸發器

4.以上任一類型的混合

例如：

--建立insert觸發器，當在student表新增資料時，檢查Accounting.dbo.AddressInfo是否對應AddressInfoID

--沒有則不能插入，有則可

create trigger trg_students_insert on accounting.dbo.students

for insert

as

begin

declare @AddressInfoID int

select @AddressInfoID = AddressInfoID from inserted

if not exists(select AddressInfoID from Accounting.dbo.AddressInfo where AddressInfoID = @AddressInfoID)

begin 

raiserror('AddressInfoID not found',16,1)

rollback

end

end

insert into Accounting.dbo.students (studnetNO,[name],[AddressInfoID]) values('A0051','陳大女','9')

go

--建立delete觸發器，當刪除Accounting.dbo.AddressInfo記錄，會觸發自動刪除accounting.dbo.students對應的記錄

alter trigger trg_addressInfo_recode_delete on Accounting.dbo.AddressInfo

for delete

as

begin

delete from Accounting.dbo.students 

where students.AddressInfoID in (select AddressInfoID from deleted)

if (select count(*) AddressInfoID from deleted) <= 0

begin

raiserror('AddressInfoID not Found.',16,1)

rollback

end

end

delete from Accounting.dbo.AddressInfo  where AddressInfoID = 9

go

--建立update觸發器，當刪除Accounting.dbo.AddressInfo記錄，會觸發自動更新accounting.dbo.students對應的記錄

alter trigger trg_AddressInfo_Update on Accounting.dbo.AddressInfo

for update

as

begin

declare @AddressInfoID_old int = (select AddressInfoID from deleted) --取得刪除前記錄

declare @AddressInfoID_new int =(select AddressInfoID from inserted) --取得修改後記錄

update  Accounting.dbo.students set AddressInfoID=@AddressInfoID_new where AddressInfoID=@AddressInfoID_old

end

update AddressInfo set AddressInfoID =99 where AddressInfoID =7

go

--啟用或關閉觸發器

alter table accounting.dbo.[students]  disable trigger  [trg_students_insert] 

alter table accounting.dbo.[students]  enable trigger  [trg_students_insert] 

十、定序

--查詢Accounting定序方式

SELECT DATABASEPROPERTYEX ('Accounting' ,'Collation' )

--查詢資料表欄位定序方式

SELECT Table_Name,CoLumn_name,collation_name,*

FROM INFORMATION_SCHEMA.COLUMNS

WHERE collation_name='Chinese_Taiwan_Stroke_CI_AS'

--ORDER BY [Table_Name],[CoLumn_name]

--修改AMS資料庫定序方式

USE [master]

GO

ALTER DATABASE [AMS] COLLATE Chinese_Taiwan_Stroke_CS_AS

GO

十一、從資料表讀取xml文檔

--從資料表取得xml資料

DECLARE @idoc INT, @docXML XML, @doc Varchar(4000);

SET @docXML = (SELECT top 2 [2] from TB_EIP_DUTY_PSN_TT where year=2024 and  MONTH =5  and TB_EIP_DUTY_PSN_TT.USER_GUID ='cd73a295-586e-4da9-a089-54d14cee2e39')

SET @doc = CAST(@docXML as Varchar(4000))

select @doc

--將取得xml載入

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

--讀取xml指定文字節點

/*

1 = 元素節點

2 = 屬性節點

3 = 文字節點

*/

SELECT *

FROM OPENXML(@idoc, '/TT',3) WITH (

        WH VARCHAR(10)    

    );

##