Jellyland

SQL Syntax(Ⅰ)

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

检索数据

SELECT column0, column1,... --'\*' for all
FROM table_name;
SELECT TOP [num] column0, column1,... 
FROM table_name;
SELECT column0, column1,... 
FROM table_name
FETCH FIRST [num] ROWS ONLY;
SELECT column0, column1,... 
FROM table_name
WHERE ROWNUM <= [num];
SELECT column0, column1,... 
FROM table_name
LIMIT [num] {OFFSET [num]};
SELECT DISTINCT column_name
FROM table_name;
# 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
SELECT column0, column1,... 
FROM table_name
WHERE [column_name] IN ([value1],[value2],...);

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

用通配符进行过滤

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

#SQL