萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 常見操作速查:建庫、建表、預存程序、動態 SQL、視圖、索引與權限管理

彙整 SQL Server 日常開發與維護的核心 T-SQL 指令,涵蓋資料庫/資料表建立、預存程序(含參數、輸出、回傳值、動態 SQL)、視圖、索引類型與權限管理,適合作為速查手冊。

1. 資料庫與資料表建立

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
);

2. 預存程序進階範例

2.1 基本查詢程序

CREATE PROC proc_dept @deptname VARCHAR(20)
AS
    SELECT * FROM Dept WHERE DeptName = @deptname;
GO
EXEC proc_dept '教職部';

2.2 動態 SQL(防注入請改用 sp_executesql)

CREATE PROC proc_dept2 @deptname VARCHAR(20)
AS
    EXEC('SELECT * FROM Dept WHERE DeptName = ''' + @deptname + '''');
GO

2.3 新增/刪除/條件查詢

-- 新增
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

2.4 輸出參數

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));

2.5 回傳值(RETURN)

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));

2.6 LIKE 模糊查詢(參數化動態 SQL)

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 '了';

3. 視圖

CREATE VIEW view_dept AS SELECT * FROM Dept;
GO
SELECT * FROM view_dept;
  • 視圖為虛擬表,儲存的是查詢定義而非資料。
  • 優點:隱藏敏感欄位、簡化複雜 JOIN、客製化不同使用者視角、安全性控管。
  • 限制:單一視圖僅能包含一條 SELECT,不可回傳多結果集。

4. 索引

資料頁儲存於資料頁,索引頁儲存於索引頁;善用覆蓋索引避免書籤查找。

5. 權限與安全性

  • GRANT SELECT, INSERT ON Dept TO [UserA];
  • DENY DELETE ON Dept TO [UserB];
  • REVOKE INSERT ON Dept FROM [UserA];

熟練這些核心指令,能大幅提升 SQL Server 開發與維護效率。

copyright © 萬盛學電腦網 all rights reserved
類型關鍵字特性
主鍵索引PRIMARY KEY唯一、非空、叢集索引預設
叢集索引CLUSTERED資料實體排序,每表僅一個
非叢集索引NONCLUSTERED索引頁指向資料頁,可多個
唯一索引UNIQUE值唯一,允許一個 NULL