資訊分享: 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)

三、修改
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

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 返回結果

八、錯誤處理

常見錯誤類型：

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

利用＠＠Error 取得錯誤代碼

例如：

alter proc sp_test1_createTable

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

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION def_getAgeAndCityPeopleList(

@startAge int,

@endAge int,

@cityName1 nvarchar(50),

@cityName2 nvarchar(50))

RETURNS Table

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)

)

--呼叫自定義函數

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

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

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

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

alter trigger trg_addressInfo_recode_delete on Accounting.dbo.AddressInfo

for delete

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

delete from Accounting.dbo.AddressInfo where AddressInfoID = 9

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

alter trigger trg_AddressInfo_Update on Accounting.dbo.AddressInfo

for update

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

資訊分享

2014年9月1日星期一

MSSQL 常用指令

沒有留言:

熱門文章

2014年9月1日 星期一

MSSQL 常用指令

沒有留言:

2014年9月1日星期一