CREATE TABLE table_name(
column1 DATATYPE [NOT NULL] [PRIMARY KEY],
column2 DATATYPE [NOT NULL],
...
)
DATATYPE 詳見資料類型表。NOT NULL 禁止空值。PRIMARY KEY 定義主鍵。
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 (column_name)
無刪除欄位語法。
CREATE INDEX index_name ON table_name (column_name)
DROP table_name
DROP index_name
smallint - 16 位整數integer - 32 位整數decimal(p,s) - 精確值 p、小數位 s (預設 p=5, s=0)float - 32 位實數double - 64 位實數char(n) - 固定長度字串 (n≤254)varchar(n) - 可變長度字串 (n≤4000)graphic(n) - 雙位元組字串 (n≤127),支援中文等vargraphic(n) - 可變雙位元組字串 (n≤2000)date - 年月日time - 時分秒timestamp - 年月日時分秒毫秒INSERT INTO table_name (col1, col2, ...) 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 > yyy] [OR col3 <> zzz]
SELECT col1, col2 FROM table_name ORDER BY col2 [DESC]
組合查詢 (多表聯接)
SELECT * FROM table1, table2 WHERE table1.col1 = table2.col1
彙總查詢
SELECT COUNT(*) FROM table_name WHERE col = xxx
SELECT SUM(col1) FROM table_name
SELECT col1, AVG(col2) FROM table_name GROUP BY col1 HAVING AVG(col2) > xxx
子查詢與集合操作
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE conditions)
SELECT * FROM t1 WHERE col1 IN (SELECT col1 FROM t2 WHERE conditions)
其他查詢
SELECT * FROM t WHERE col LIKE 'x%'
SELECT * FROM t WHERE col IN ('xxx','yyy')
SELECT * FROM t WHERE col BETWEEN xx AND yy
UPDATE table_name SET col1='xxx' WHERE conditions
DELETE FROM table_name WHERE conditions
WHERE mydate > #2000-01-01#WHERE mydate > CAST('2000-01-01' AS DATE) 或 TO_DATE