SQL Server 基礎操作全攻略：建庫、建表、索引、查詢與進階編程

核心物件管理

資料庫與資料表建立

-- 建立資料庫
CREATE DATABASE Students;

-- 建立基礎資料表
CREATE TABLE student (
    sno CHAR(5), 
    sname CHAR(20), 
    ssex CHAR(2), 
    sage SMALLINT, 
    sdept CHAR(15)
);

CREATE TABLE course (
    cno CHAR(3), 
    cname CHAR(30), 
    cpno CHAR(3), 
    ccredit SMALLINT
);

CREATE TABLE sc (
    sno CHAR(5), 
    cno CHAR(3), 
    grade INT
);

資料表結構修改

-- 新增欄位
ALTER TABLE student ADD scome DATETIME;

-- 修改欄位型別
ALTER TABLE student ALTER COLUMN scome CHAR(50);

-- 刪除欄位
ALTER TABLE student DROP COLUMN scome;

-- 刪除資料表
DROP TABLE student;
DROP TABLE course;
DROP TABLE sc;

完整性約束實作

建表時定義約束

CREATE TABLE student (
    sno CHAR(5) NOT NULL UNIQUE,
    sname CHAR(20),
    sex CHAR(2),
    sage SMALLINT DEFAULT 20,
    sdept CHAR(15),
    CONSTRAINT chk_sex CHECK (sex IN ('男','女'))
);

事後增刪約束

-- 刪除約束
ALTER TABLE student DROP CONSTRAINT chk_sex;

-- 新增檢查約束
ALTER TABLE student ADD CONSTRAINT chk_sex CHECK (sex IN ('男','女'));

-- 新增主鍵
ALTER TABLE student ADD CONSTRAINT PK_SNO PRIMARY KEY (sno);

-- 新增外鍵（參考已存在的主鍵）
ALTER TABLE course ADD CONSTRAINT PK_CNO PRIMARY KEY (cno),
    CONSTRAINT FK_CPNO FOREIGN KEY (cpno) REFERENCES course(cno);

-- SC 表複合主鍵 + 雙外鍵
CREATE TABLE sc (
    sno CHAR(5) FOREIGN KEY REFERENCES student(sno),
    cno CHAR(3) FOREIGN KEY REFERENCES course(cno),
    grade INT,
    CONSTRAINT PK_SC PRIMARY KEY (sno, cno)
);

索引管理

-- 唯一索引（聚集/非聚集）
CREATE UNIQUE INDEX STU_sno ON student(sno);
CREATE UNIQUE INDEX COU_cno ON course(cno);
CREATE UNIQUE INDEX SC_no ON sc(sno ASC, cno DESC);

-- 刪除索引
DROP INDEX SC_no ON sc;

-- 查看碎片資訊
DBCC SHOWCONTIG;

資料操作 (DML)

資料插入

INSERT INTO student VALUES ('10021','張三','男',20,'計科系');
INSERT INTO student VALUES ('10022','王朝','女',18,'軟體');
-- ... 更多範例

查詢技巧大全

基礎查詢與格式化

-- 去重
SELECT DISTINCT sno FROM sc;

-- 別名寫法
SELECT sname AS '姓名', 2013-sage AS '出生年份' FROM student;
SELECT 姓名=sname, 出生年份=2013-sage FROM student;

條件與模糊查詢

SELECT * FROM course WHERE ccredit > 3;
SELECT * FROM course WHERE ccredit BETWEEN 2 AND 5;
SELECT * FROM student WHERE sname LIKE '劉__';
SELECT * FROM student WHERE sname LIKE '%表%';

聚合與分組

SELECT COUNT(*) AS 總人數 FROM student;
SELECT AVG(grade) AS 平均分 FROM sc WHERE sno='10021';
SELECT SUM(grade) AS 總分 FROM sc GROUP BY sno HAVING SUM(grade) > 100;

多表連接

-- 內連接
SELECT DISTINCT student.*, sc.* 
FROM student, sc 
WHERE student.sno = sc.sno;

-- 外連接
SELECT A.*, B.* FROM student A LEFT JOIN sc B ON A.sno = B.sno;
SELECT A.*, B.* FROM student A RIGHT JOIN sc B ON A.sno = B.sno;
SELECT A.*, B.* FROM student A FULL JOIN sc B ON A.sno = B.sno;

-- 三表連接
SELECT DISTINCT A.*, B.*, C.* 
FROM student A, sc B, course C 
WHERE A.sno = B.sno AND B.cno = C.cno AND B.grade > 99;

子查詢與 EXISTS

SELECT * FROM student 
WHERE sage > (SELECT AVG(sage) FROM student);

SELECT * FROM student 
WHERE EXISTS (SELECT * FROM sc WHERE sno = student.sno);

進階編程

變數與流程控制

DECLARE @name CHAR(10);
SET @name = '司馬相如';
PRINT @name;

DECLARE @a INT = 33, @b INT = 34;
IF @a > @b PRINT '最大值：' + CAST(@a AS VARCHAR)
ELSE PRINT '最大值：' + CAST(@b AS VARCHAR);

-- 延遲執行
WAITFOR DELAY '00:00:04'; PRINT '4秒後執行';
WAITFOR TIME '17:45:50'; PRINT '定時執行';

函數與預存程序

-- 表值函數
CREATE FUNCTION GetTime(@date1 DATETIME, @date2 DATETIME)
RETURNS TABLE
AS RETURN (
    SELECT DATEDIFF(dd,@date1,@date2) AS 日差,
           DATEDIFF(mm,@date1,@date2) AS 月差,
           DATEDIFF(yy,@date1,@date2) AS 年差
);

-- 基礎 CRUD 預存程序
CREATE PROC sp_sel @sno CHAR(10)
AS SELECT * FROM student WHERE sno = @sno;

CREATE PROC sp_ins @sno CHAR(10), @sname CHAR(20), @sex CHAR(2), @sage SMALLINT, @sdept CHAR(15)
AS INSERT INTO student VALUES (@sno,@sname,@sex,@sage,@sdept);

EXEC sp_sel @sno='10021';
EXEC sp_ins @sno='10029', @sname='tom', @sex='男', @sage=100, @sdept='sc';

視圖管理

CREATE VIEW VIEW_STUGrade(學號,姓名,課程,成績)
AS SELECT student.sno, student.sname, course.cname, sc.grade
FROM student, course, sc
WHERE student.sno = sc.sno AND course.cno = sc.cno AND student.sdept = '軟體';

-- 可更新視圖需符合：單一基表、無聚合/運算式/嵌套查詢/DISTINCT
ALTER VIEW VIEW_STUGrade(學號,姓名,課程,成績)
AS ... WITH CHECK OPTION;

UPDATE VIEW_STUGrade SET 姓名='王超' WHERE 學號='10022';
DROP VIEW VIEW_STUGrade;

觸發器

CREATE TRIGGER trg_ins ON student AFTER INSERT AS PRINT '新資料插入';
CREATE TRIGGER trg_upd ON student AFTER UPDATE AS PRINT '資料更新';
CREATE TRIGGER trg_del ON student AFTER DELETE AS PRINT '資料刪除';

-- 防止刪除特定資料
ALTER TRIGGER trg_del ON student AFTER DELETE AS
IF '王帥' IN (SELECT sname FROM deleted)
BEGIN
    PRINT '該資料不准刪除！';
    ROLLBACK TRANSACTION;
END

-- 管理指令
sp_helptext trg_del;
sp_helptrigger student;
DROP TRIGGER trg_del;

權限與用戶

EXEC sp_addlogin 'bnc', 'bnc', 'Students';
EXEC sp_adduser 'bnc', 'bnc';

GRANT SELECT ON student TO bnc;
REVOKE SELECT ON student FROM bnc;