mysql 常用语法

查询

select

in

多个 OR 条件的简写；

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
或者
WHERE column_name IN (value1, value2, ...);

LEFT JOIN

返回左表的数据，以及右表中匹配的数据；

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

@setVal := 'valName';

SELECT * FROM 
       ( SELECT id FROM cTable WHERE aName = @setVal) c
	LEFT JOIN bTable b ON b.b_check = c.id
	LEFT JOIN aTable a ON a.a_check = b.id;

绿色区域为返回数据部分

UNION

UNION 运算符用于组合两个或多个 SELECT 语句的结果集。

• 具有相同的列数
• 每列具有相似的数据类型
• 每个列是相同的顺序

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION 会进行去重排序；
UNION ALL 会把结果直接输出，速度会更快一些；

自联结

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

GROUP BY

分组

通常与聚合函数（COUNT()、MAX(), MIN(), SUM(), AVG()) 按一列或多列对结果集进行分组；

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

LIKE

包含

WHERE CustomerName LIKE '%or%'	查找在任何位置有"or"的任何值

AS

在查询期间为列或者表提供一个临时的名称。

SELECT column_name AS alias_name
FROM table_name AS alias_table;

删除

delete

DELETE FROM table_name WHERE condition;

更新

UPDATE

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

复制一个表的数据到另一个表

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;