- 返回一定数目记录
SELECT TOP number ( number percent) column FROM table
SELECT column FROM table LIMIT number /MySQL
SELECT column FROM table WHERE ROWNUM <= number
- 表/列 别名
SELECT t.column FROM table AS t
SELECT column AS c FROM table
- 不同表关联
主键 —— 值的唯一性
SELECT table1.column1, table2.column2
FROM table1, table2
WHERE table1.column-n = table2.column-m
/
SELECT table1.column1, table2.column2
FROM table1 JOIN table2
ON table1.column-n = table2.column-m
/JOIN for example/
| JOIN name | description (n左m右) |
|---|---|
| (INNER) JOIN | n&m交集 |
| FULL JOIN | n&m全集 |
| LEFT JOIN | n全集，m交集 |
| RIGHT JOIN | n交集，m全集 |
- 不同表合并
SELECT column1 FROM table1
UNION (ALL /允许重复值/)
SELECT column2 FROM table2
(column1与column2的列数相同，合并项处于同一顺序上)
- 创建备份表
SELECT column INTO new_table (IN database) FROM old_table
- 自动创建主键值，新增行时无需为主键赋值
AUTO_INCREMENT (= number) /number = 起始值
IDENTITY (n, m) /n起始，m递增CREAT SEQUENCE sequence
MINVALUE n
START WITH n
INCREMENT BY m
CACHE xINSERT INTO table (column) VALUES (sequence.nextval)
- 视图
CREATE VIEW view AS SELECT column FROM table WHERE condition
REPLACE VIEW view AS SELECT column FROM table WHERE condition
DROP VIEW view
- 空值测试
SELECT column FROM table WHERE column IS NULL
SELECT column FROM table WHERE column IS NOT NULL
