MySQL SELECT API Reference

`DDL`

4 methods

定义或修改数据库模式的语句。

SELECT cols FROM tbl [WHERE condition]

用给定列和表级约束创建新表。

Name	Type	Description
cols	column-list	Comma-separated columns or *.
tbl	table	Source table (or joined subquery).
condition	boolean	Predicate evaluated per row.

ResultSet

mysql

SELECT id, name FROM users WHERE active = 1;

SELECT ... FROM a JOIN b ON a.id = b.a_id

在现有表上添加、删除或修改列和约束。

Name	Type	Description
JOIN	keyword	INNER, LEFT [OUTER], RIGHT [OUTER], CROSS.
ON	predicate	Join condition between the two tables.

ResultSet

mysql

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

SELECT cols, agg(...) FROM tbl GROUP BY cols [HAVING condition]

在一个或多个列上创建二级索引以加速查找；UNIQUE 强制唯一性。

Name	Type	Description
agg	function	Aggregate such as COUNT, SUM, AVG, MAX, MIN.
cols	column-list	Grouping columns.
condition	boolean	Predicate applied to each group (HAVING).

ResultSet

mysql

SELECT dept, COUNT(*) AS n, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) > 50000;

SELECT ... ORDER BY col [ASC|DESC] [LIMIT n [OFFSET m]]

对一个或多个列强制完整性规则的表级约束子句。

Name	Type	Description
col	column	Sort key column or expression.
LIMIT	number	Maximum rows to return.
OFFSET	number	Rows to skip before starting to return.

ResultSet

mysql

SELECT * FROM products
ORDER BY price DESC, name ASC
LIMIT 20 OFFSET 40;