SQL语法简明列表

SQL查询的执行顺序

FROM：确定查询的数据源，包括表和视图等，并处理JOIN操作。
WHERE：应用过滤条件，对数据进行筛选。
GROUP BY：对筛选后的数据进行分组。
HAVING：对分组后的数据进行进一步的筛选。
SELECT：选择所需的列；此阶段还涉及计算选中的列（如使用聚合函数）。
DISTINCT：去除重复的行。
ORDER BY：对结果进行排序。
LIMIT / OFFSET：限制返回的行数及其偏移量。

SELECT

SELECT CustomerName, City FROM Customers;

DISTINCT The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT Country FROM Customers;

WHERE

SELECT * FROM Customers WHERE Country=’Mexico’;

The following operators can be used in the WHERE clause:

字符串需要用单引号

=	Equal
SELECT * FROM Customers WHERE City = 'Berlin';
SELECT * FROM Customers whereCustomerID = 32;

>	Greater than
	
<	Less than
	
>=	Greater than or equal
	
<=	Less than or equal
	
<>	Not equal. Note: In some versions of SQL this operator may be written as !=

BETWEEN	Between a certain range
	
LIKE	Search for a pattern
	
IN	To specify multiple possible values for a column

ORDER BY

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

SELECT * FROM Customers ORDER BY City;
SELECT * FROM Products ORDER BY Price;

AND

SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;

SELECT *
FROM Customers
WHERE Country = ‘Spain’ AND CustomerName LIKE ‘G%’;

OR

SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;

SELECT *
FROM Customers
WHERE Country = ‘Germany’ OR Country = ‘Spain’;

NOT

SELECT column1, column2, …
FROM table_name
WHERE NOT condition;

SELECT * FROM Customers
WHERE NOT Country = ‘Spain’;

INSERT INTO

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
(‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’),
(‘Greasy Burger’, ‘Per Olsen’, ‘Gateveien 15’, ‘Sandnes’, ‘4306’, ‘Norway’),
(‘Tasty Tee’, ‘Finn Egan’, ‘Streetroad 19B’, ‘Liverpool’, ‘L1 0AA’, ‘UK’);

NULL Values

Test for NULL Values

IS NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

UPDATE Statement

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;

DELETE Statement

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;

LIMIT OFFSET

SELECT column1, column2, …
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number_of_rows OFFSET offset_value;

LIKE

like 操作符可以使用的通配符 % 任意数量的任意字符 _ 任意一个字符

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%mer%’;

IN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);

SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SQL Aggregate Functions

聚合函数一般和分组组合使用，分组完后，聚合函数会作用与单个分组上。

MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column

Aggregate functions ignore null values (except for COUNT()).

SQL JOIN

对于左连接或者右连接和全连接，按照连接条件没有找到的值为null

INNER JOIN

SELECT column_name(s)
FROM table1
[INNER] JOIN table2
ON table1.column_name = table2.column_name;

INNER 关键字可选，省略默认是INNER JOIN

LEFT JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL JOIN

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

SELF JOIN

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

GROUP BY Statement

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

HAVING Clause

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

CodeMe