常用的 SQL 语法

• 汇总
• SELECT 查询 FROM … WHERE … AND/OR … ORDER BY … ASC/DESC 正序逆序
• COALESCE 压缩，返回列表中第一个非null值
• UNION 去重集合 UNION ALL 不去重集合
• CASE 条件查询 WHEN … THEN … ELSE … END
• IF + SELECT 某一列的条件查询
• LIKE 搜索指定的模式 % _
• GROUP BY 分组合并 COUNT MAX MIN SUM AVG … ORDER BY 展示顺序
• UPDATE SET WHERE 更新表里面列的值
• SET + CASE/IF 条件更新列的值
• REPLACE 统一替换列的字符串
• DELETE 删掉表所有信息 DELETE FROM 从表中删掉信息
• UPPER LOWER 字符串大小写转换
• SUBSTR 截取字符串
• CONCAT 拼接字符串
• GROUP_CONCAT 分组合并拼接字符串 GROUP BY
• DISTINCT 返回某列独特的值（注意可以包含NULL，从而引起NOT IN错误）
• RUNTIME ERROR - EVERY DERIVED TABLE MUST HAVE ITS OWN ALIAS 每个衍生表都要有别名
• JOINs - (INNER) JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN, Self Join 合并表格

汇总

SELECT 查询 FROM … WHERE … AND/OR … ORDER BY … ASC/DESC 正序逆序

SELECT 列名们 FROM 表名 WHERE 条件1 AND/OR 条件2 ORDER BY 列名 [ASC | DESC]

COALESCE 压缩，返回列表中第一个非null值

SELECT name, COALESCE(phone, email, 'N/A') as contact_info
FROM user_table;

UNION 去重集合 UNION ALL 不去重集合

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2

SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2

CASE 条件查询 WHEN … THEN … ELSE … END

select employee_id,
case 
when employee_id%2<>0 and name not like "M%" then salary
else 0 
end as bonus 
from Employees 
order by employee_id;

IF + SELECT 某一列的条件查询

SELECT column1, column2, IF(condition, value_if_true, value_if_false) AS new_column
FROM table_name;

SELECT CustomerID, CustomerName, Country,
    IF(Country IN ('USA', 'Canada'), 'Domestic', 'International') AS CustomerType
FROM Customers;

LIKE 搜索指定的模式 % _

• 百分号 (%) 代表零个、一个或多个字符
• 下划线 (_) 代表一个单一字符

select employee_id, salary as bonus from Employees 
where employee_id%2<>0 and name not like "M%" --以M开头的
union
select employee_id, 0 as bonus from Employees 
where employee_id%2=0 or name like "M%"
order by employee_id;

GROUP BY 分组合并 COUNT MAX MIN SUM AVG … ORDER BY 展示顺序

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s) --分组合并
ORDER BY column_name(s); --展示顺序 --先group by再order by

UPDATE SET WHERE 更新表里面列的值

UPDATE table_name --某表
SET column1 = value1, column2 = value2, ... --列名=值
WHERE condition; --条件

SET + CASE/IF 条件更新列的值

UPDATE Orders
SET Amount = CASE
    WHEN Amount >= 300 THEN Amount * 0.9
    ELSE Amount
END;

UPDATE Orders
SET Amount = IF(Amount >= 300, Amount * 0.9, Amount)
END;

REPLACE 统一替换列的字符串

REPLACE(string_column, old_substring, new_substring) --替换x列，把s1换成s2

SELECT ProductID, ProductName, REPLACE(Description, 'latest', 'new') AS NewDescription
FROM Products;

DELETE 删掉表所有信息 DELETE FROM 从表中删掉信息

DELETE table_name;

DELETE FROM table_name
WHERE condition;

UPPER LOWER 字符串大小写转换

SELECT LOWER('SQL Tutorial is FUN!'); --UPPER同理

SUBSTR 截取字符串

SUBSTR(string, start_position, length); --源字符串，开始索引（1开始），截取长度

select user_id, concat(upper(substr(name, 1, 1)), 
lower(substr(name, 2, length(name)-1))) as name
from Users order by user_id;

CONCAT 拼接字符串

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

GROUP_CONCAT 分组合并拼接字符串 GROUP BY

GROUP_CONCAT(column_name ORDER BY order_column SEPARATOR separator) --列名 顺序 分隔符

select sell_date, count(distinct(product)) as num_sold, 
group_concat(distinct(product) order by product) as products 
from Activities group by sell_date order by sell_date;

DISTINCT 返回某列独特的值（注意可以包含NULL，从而引起NOT IN错误）

SELECT DISTINCT column_name1, column_name2, ...
FROM table_name
WHERE condition;

RUNTIME ERROR - EVERY DERIVED TABLE MUST HAVE ITS OWN ALIAS 每个衍生表都要有别名

select t.employee_id from 
(select * from Employees left join Salaries using (employee_id)
union
select * from Salaries left join Employees using (employee_id)) as t
where t.name is null or t.salary is null
order by employee_id;

JOINs - (INNER) JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN, Self Join 合并表格

SELECT column_name(s)
FROM table1
INNER JOIN table2 --LEFT JOIN/ RIGHT JOIN/ FULL JOIN...
ON table1.column_name = table2.column_name
WHERE condition;

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