SQL Syntax(Ⅰ)

　　这两天刚好在翻《SQL必知必会》，就顺手写几篇Markdown整理一下语法吧。

检索数据

SELECT column0, column1,... --'\*' for all
FROM table_name;
  • In SQL Server and Access,
SELECT TOP [num] column0, column1,... 
FROM table_name;
  • In DB2,
SELECT column0, column1,... 
FROM table_name
FETCH FIRST [num] ROWS ONLY;
  • In Oracle,
SELECT column0, column1,... 
FROM table_name
WHERE ROWNUM <= [num];
  • In MySQL, MariaDB, PostgreSQL and SQLite
SELECT column0, column1,... 
FROM table_name
LIMIT [num] {OFFSET [num]};
  • Select distinct value
SELECT DISTINCT column_name
FROM table_name;
  • Annotation
# Here is a comment
SELECT column0 -- Here is also a comment
FROM table_name;
/*
Also a comment~
*/

排列检索数据

SELECT column0, column1,... 
FROM table_name
ORDER BY column0, column1, ...;
-- Also, you can use order numbers to represent certain column
-- Use 'DESC' or 'ASC' in the end to assigned the order direction

过滤数据

SELECT column0, column1,... 
FROM table_name
WHERE column = [value];

高级数据过滤

SELECT column0, column1,... 
FROM table_name
WHERE [clause] AND/OR [clause];
-- The priority of 'AND' is higher than 'OR' when they are used together
  • Actually, ‘OR’ is same as ‘IN’
SELECT column0, column1,... 
FROM table_name
WHERE [column_name] IN ([value1],[value2],...);
  • ‘NOT’

  SELECT column0, column1,... 
  FROM table_name
  WHERE NOT xxxx;

用通配符进行过滤

通配符 说明
%(* in Access) 任意字符任意次数
_ 任意字符一次
[] 指定一个字符集, 如 [JM] 匹配’J’和’M'（用"^“可以否定）

Contents


本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可。

知识共享许可协议