彙整 SQL Server 日常開發與維護的核心 T-SQL 指令,涵蓋資料庫/資料表建立、預存程序(含參數、輸出、回傳值、動態 SQL)、視圖、索引類型與權限管理,適合作為速查手冊。
CREATE DATABASE Personnel_Training_DB;
GO
USE Personnel_Training_DB;
GO
CREATE TABLE Dept (
DeptId INT PRIMARY KEY IDENTITY(1,1),
DeptName VARCHAR(20) NOT NULL,
Remark VARCHAR(20) NOT NULL
);
CREATE PROC proc_dept @deptname VARCHAR(20)
AS
SELECT * FROM Dept WHERE DeptName = @deptname;
GO
EXEC proc_dept '教職部';
CREATE PROC proc_dept2 @deptname VARCHAR(20)
AS
EXEC('SELECT * FROM Dept WHERE DeptName = ''' + @deptname + '''');
GO
-- 新增
CREATE PROC proc_add_dept @deptname VARCHAR(20), @remark VARCHAR(20)
AS INSERT INTO Dept VALUES (@deptname, @remark);
GO
-- 刪除(支援多 ID 逗號分隔)
CREATE PROC proc_del_deptid @deptid VARCHAR(20)
AS EXEC('DELETE FROM Dept WHERE DeptId IN (' + @deptid + ')');
GO
EXEC proc_del_deptid '1,2';
-- 多條件動態查詢
CREATE PROC proc_like_query_dept @deptname VARCHAR(20), @remark VARCHAR(20)
AS
BEGIN
IF @deptname <> '' AND @remark <> ''
SELECT * FROM Dept WHERE DeptName = @deptname AND Remark = @remark;
ELSE IF @remark <> '' AND @deptname = ''
SELECT * FROM Dept WHERE Remark = @remark;
ELSE IF @deptname <> '' AND @remark = ''
SELECT * FROM Dept WHERE DeptName = @deptname;
ELSE
SELECT * FROM Dept;
END
GO
CREATE PROC proc_set @deptid INT OUTPUT
AS SELECT @deptid = DeptId FROM Dept WHERE DeptName = '學術部' AND Remark = '這個不能還有點意思';
GO
DECLARE @deptid INT;
EXEC proc_set @deptid OUTPUT;
PRINT '取得的 DeptId:' + CAST(@deptid AS VARCHAR(10));
CREATE PROC proc_return
AS
BEGIN
DECLARE @cnt INT;
SELECT @cnt = COUNT(*) FROM Dept;
RETURN @cnt;
END
GO
DECLARE @cnt INT;
EXEC @cnt = proc_return;
PRINT '總筆數:' + CAST(@cnt AS VARCHAR(10));
CREATE PROC proc_query_like @like VARCHAR(20)
AS
BEGIN
DECLARE @sql NVARCHAR(1024) = N'SELECT * FROM Dept WHERE 1=1';
SET @sql += N' AND (DeptName LIKE ''%'' + @like + ''%'' OR Remark LIKE ''%'' + @like + ''%'')';
EXEC sp_executesql @sql, N'@like VARCHAR(20)', @like;
END
GO
EXEC proc_query_like '了';
CREATE VIEW view_dept AS SELECT * FROM Dept;
GO
SELECT * FROM view_dept;
| 類型 | 關鍵字 | 特性 |
|---|---|---|
| 主鍵索引 | PRIMARY KEY | 唯一、非空、叢集索引預設 |
| 叢集索引 | CLUSTERED | 資料實體排序,每表僅一個 |
| 非叢集索引 | NONCLUSTERED | 索引頁指向資料頁,可多個 |
| 唯一索引 | UNIQUE | 值唯一,允許一個 NULL |