SQL Server T-SQL 核心語法完整指南：從 SELECT 到進階函數

基礎查詢子句

SELECT - 資料投影

SELECT column_name(s) FROM table_name;
SELECT * FROM Persons;  -- 全欄位
SELECT LastName, FirstName FROM Persons;  -- 指定欄位

完整語法架構：

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 ] ]

WHERE - 條件篩選

SELECT * FROM Persons WHERE City = 'Sandnes';
-- 運算子: =, <>, !=, >, <, >=, <=, BETWEEN, LIKE, IN

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 (預設)
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC;   -- 降序

GROUP BY / HAVING - 分組統計

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

多表連接

INNER JOIN - 內連接

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

LEFT JOIN - 左外連接

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders ON Employees.ID = Orders.ID;
-- 返回 Employees 所有行，Orders 無匹配則為 NULL

RIGHT JOIN - 右外連接

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders ON Employees.ID = Orders.ID;
-- 返回 Orders 所有行

別名

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

資料操作語言 (DML)

INSERT INTO

INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes');
INSERT INTO Persons (LastName, Address) VALUES ('Rasmussen', 'Storgt 67');

UPDATE

UPDATE Person SET FirstName = 'Nina' WHERE LastName = 'Rasmussen';
UPDATE Person SET Address = 'StIEn 12', City = 'Stavanger' WHERE LastName = 'Rasmussen';

DELETE

DELETE FROM Person WHERE LastName = 'Rasmussen';

資料定義語言 (DDL)

CREATE TABLE

CREATE TABLE Person (
  LastName VARCHAR(30),
  FirstName VARCHAR(30),
  Address VARCHAR(120),
  Age INT
);

常用資料型別：INT、DECIMAL(size,d)、CHAR(size)、VARCHAR(size)、DATE

ALTER TABLE

ALTER TABLE Person ADD City VARCHAR(30);
ALTER TABLE Person DROP COLUMN Address;  -- 部分資料庫不支援

DROP TABLE / DATABASE

DROP TABLE table_name;
DROP DATABASE database_name;

聚合函數

數學與三角函數

字串與日期函數（常用補充）

• LEN、SUBSTRING、REPLACE、UPPER/LOWER、LTRIM/RTRIM
• GETDATE()、DATEADD、DATEDIFF、CONVERT、FORMAT (SQL 2012+)