CREATE TABLE table_name (
column1 datatype [NOT NULL] [PRIMARY KEY],
column2 datatype [NOT NULL],
...
);
datatype 常用類型:INT、BIGINT、VARCHAR(n)、TEXT、DATE、DATETIME、DECIMAL(p,s)、ENUM、JSON
-- 新增欄位
ALTER TABLE table_name ADD COLUMN column_name datatype;
-- 設定主鍵
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 移除主鍵
ALTER TABLE table_name DROP PRIMARY KEY;
CREATE INDEX index_name ON table_name (column_name);
DROP TABLE table_name;
DROP INDEX index_name ON table_name;
-- 指定欄位
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
-- 依表定義順序(不指定欄位)
INSERT INTO table_name VALUES (val1, val2, ...);
-- 從子查詢插入
INSERT INTO table_name (col1, col2)
SELECT colx, coly FROM another_table;
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name WHERE col1 = 'xxx' AND col2 > 100;
SELECT col1, col2 FROM table_name ORDER BY col2 DESC;
SELECT COUNT(*) FROM table_name WHERE col = 'xxx';
SELECT SUM(col1), AVG(col2), MAX(col3), MIN(col4) FROM table_name;
SELECT col1, AVG(col2) FROM table_name GROUP BY col1 HAVING AVG(col2) > 50;
-- 內連接
SELECT * FROM t1 JOIN t2 ON t1.col = t2.col;
-- 左連接
SELECT * FROM t1 LEFT JOIN t2 ON t1.col = t2.col;
-- 交叉連接
SELECT * FROM t1, t2 WHERE t1.col = t2.col;
-- EXISTS 相關子查詢
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE condition);
-- IN 子查詢
SELECT * FROM t1 WHERE col1 IN (SELECT col1 FROM t2 WHERE condition);
-- BETWEEN 範圍
SELECT * FROM t1 WHERE col1 BETWEEN 10 AND 20;
-- LIKE 模糊比對
SELECT * FROM t1 WHERE col1 LIKE 'prefix%';
UPDATE table_name SET col1 = 'new_val' WHERE condition;
/* 無 WHERE 則更新全表,極度危險! */
DELETE FROM table_name WHERE condition;
/* 無 WHERE 則清空全表,建議改用 TRUNCATE TABLE */
| 資料庫 | 語法範例 |
|---|---|
| Access | WHERE mydate > #2000-01-01# |
| Oracle | WHERE mydate > TO_DATE('2000-01-01','YYYY-MM-DD') |
| MySQL | WHERE mydate > '2000-01-01' 或 CAST('2000-01-01' AS DATE) |
| SQL Server | WHERE mydate > '2000-01-01' 或 CAST('2000-01-01' AS DATE) |
| PostgreSQL | WHERE mydate > DATE '2000-01-01' |
theDate := '2000-01-01';
Query1.SQL.Add('SELECT * FROM abc WHERE mydate > CAST(' + QuotedStr(theDate) + ' AS DATE)');
掌握 DDL/DML 核心語法、索引策略、子查詢模式與跨資料庫日期處理差異,是資料庫開發與維運的基本功。