SQL Server 教學：核心 SQL 語法完整指南 (查詢、修改、DDL、函數)

SELECT：資料查詢

用途：從指定表取出指定欄位資料。

SELECT column_name(s) FROM table_name

完整子句摘要：

SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

範例資料表 Persons

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger

選取特定欄位

SELECT LastName, FirstName FROM Persons

回傳三筆姓名記錄。

選取所有欄位

SELECT * FROM Persons

WHERE：條件篩選

SELECT column FROM table WHERE column operator value

運算子：=, >, <, >=, <=, <> (或 !=)。

範例

SELECT * FROM Persons WHERE City = 'Sandnes'

回傳居住在 Sandnes 的三筆記錄 (含 Year 欄位)。

AND / OR：多條件組合

• AND：兩條件皆真才回傳真。
• OR：任一條件真即回傳真。

範例

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'
SELECT * FROM Persons WHERE FirstName='Tove' OR LastName='Svendson'
SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson'

BETWEEN ... AND：範圍查詢

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen'

DISTINCT：去除重複

SELECT DISTINCT Company FROM Orders

回傳唯一廠商名稱。

ORDER BY：排序

SELECT Company, OrderNumber FROM Orders ORDER BY Company [ASC|DESC]

預設 ASC 遞增，DESC 遞減。

GROUP BY：分組彙總

SELECT Company, SUM(Amount) FROM Sales GROUP BY Company

依公司分組計算總銷售額。

HAVING：分組後篩選

SELECT Company, SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount) > 10000

僅回傳總額超過 10000 的群組。HAVING 通常搭配 GROUP BY；無 GROUP BY 時類似 WHERE。

JOIN：多表聯結

類型	語法關鍵字	回傳結果
INNER JOIN	INNER JOIN ... ON	兩表符合條件的配對列
LEFT JOIN	LEFT JOIN ... ON	左表所有列 + 右表匹配列 (無匹配則 NULL)
RIGHT JOIN	RIGHT JOIN ... ON	右表所有列 + 左表匹配列

範例：Employees 與 Orders

SELECT Employees.Name, Orders.Product
FROM Employees INNER JOIN Orders ON Employees.ID = Orders.ID

ALIAS：別名

-- 欄位別名
SELECT LastName AS Family, FirstName AS Name FROM Persons
-- 表格別名
SELECT LastName, FirstName FROM Persons AS Employees

INSERT INTO：新增資料

INSERT INTO table_name VALUES (val1, val2, ...)
INSERT INTO table_name (col1, col2) VALUES (val1, val2)

UPDATE：更新資料

UPDATE table_name SET col = new_val WHERE condition
UPDATE table_name SET col1 = v1, col2 = v2 WHERE condition

DELETE：刪除資料

DELETE FROM table_name WHERE condition

CREATE TABLE：建立資料表

CREATE TABLE table_name (
    col1 datatype,
    col2 datatype,
    ...
)

常用資料型別

• INTEGER(size)、DECIMAL(size,d)、CHAR(size)、VARCHAR(size)、DATE

ALTER TABLE：修改結構

ALTER TABLE table_name ADD col_name datatype
ALTER TABLE table_name DROP COLUMN col_name

部分 DBMS 不支援 DROP COLUMN。

DROP TABLE / DATABASE

DROP TABLE table_name
DROP DATABASE database_name

彙總函數

函數	用途	範例
COUNT	列數	SELECT COUNT(Name) FROM Persons → 3
SUM	總和	SELECT SUM(Age) FROM Persons → 98
AVG	平均	SELECT AVG(Age) FROM Persons → 32.67
MAX	最大值	SELECT MAX(Age) FROM Persons → 45
MIN	最小值	SELECT MIN(Age) FROM Persons → 19

算術/三角/指數/對數函數

函數	說明	範例結果
ABS	絕對值	ABS(-1) = 1
CEIL	大於等於的最小整數	CEIL(123.45) = 124
FLOOR	小於等於的最大整數	FLOOR(123.45) = 123
COS/SIN/TAN	三角函數 (弧度)	COS(2) ≈ -0.416
EXP	指數 e^x	EXP(1) ≈ 2.718
LOG	自然對數	LOG(10) ≈ 2.302
POWER	乘冪	POWER(2,6) = 64
SQRT	平方根	SQRT(100) = 10
SIGN	正負號	SIGN(-5) = -1