1. 數據庫級操作
-- 創建
CREATE DATABASE MyDB;
-- 刪除
DROP DATABASE MyDB;
-- 備份裝置與完整備份
USE master;
EXEC sp_addumpdevice 'disk', 'MyDB_BakDev', 'D:\Backup\MyDB.bak';
BACKUP DATABASE MyDB TO MyDB_BakDev;
2. 表結構管理
-- 創建表(含主鍵、非空、類型)
CREATE TABLE Products (
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Qty INT DEFAULT 0,
InDate DATETIME DEFAULT GETDATE()
);
-- 增加列
ALTER TABLE Products ADD UnitPrice DECIMAL(10,2);
-- 刪除列
ALTER TABLE Products DROP COLUMN UnitPrice;
-- 主鍵
ALTER TABLE Products ADD PRIMARY KEY (ID);
ALTER TABLE Products DROP CONSTRAINT PK_Products; -- 需知約束名
-- 索引
CREATE UNIQUE INDEX IX_Products_Name ON Products(Name);
DROP INDEX IX_Products_Name ON Products;
-- 視圖
CREATE VIEW V_ActiveProducts AS SELECT * FROM Products WHERE Qty > 0;
DROP VIEW V_ActiveProducts;
3. 基礎 DML
-- 插入
INSERT INTO Products (Name, Qty, InDate) VALUES ('礦泉水', 100, '2024-01-02');
-- 刪除
DELETE FROM Products WHERE Qty = 0;
-- 更新
UPDATE Products SET Qty = Qty + 10 WHERE Name = '礦泉水';
-- 查詢
SELECT * FROM Products;
SELECT Name, Qty FROM Products WHERE Name LIKE '%水%';
SELECT * FROM Products ORDER BY InDate DESC;
SELECT COUNT(*) AS Total FROM Products;
SELECT SUM(Qty) AS TotalQty FROM Products;
4. 系統表/信息模式查詢元數據
-- 所有用戶表
SELECT name FROM sysobjects WHERE xtype = 'U';
-- 表列
SELECT name FROM syscolumns WHERE id = OBJECT_ID('Products');
-- 信息模式(標準、跨版本穩定)
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Products';
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
5. 進階查詢技巧
去重插入
INSERT INTO Target (Col1, Col2)
SELECT Col1, Col2 FROM Source s
WHERE NOT EXISTS (SELECT 1 FROM Target t WHERE t.Col1 = s.Col1);
分頁 (2012+)
SELECT * FROM Products ORDER BY ID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
按筆畫排序
SELECT * FROM Customers ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;
6. 事務模板
BEGIN TRAN
BEGIN TRY
UPDATE Accounts SET Balance -= 100 WHERE ID = 1;
UPDATE Accounts SET Balance += 100 WHERE ID = 2;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW; -- 重拋錯誤
END CATCH