mysql常见语句语法

检索单个列

SELECT prod_name FROM Products;

检索多个列

SELECT prod_id, prod_name, prod_price FROM Products;

检索所有列

SELECT * FROM Products;

去重

SELECT DISTINCT vend_id FROM Products;

限制结果个数

SELECT prod_name FROM Products LIMIT 5;

第一个被检索的行是第0行，而不是第1行。因此，LIMIT 1 OFFSET 1会检索第2行，而不是第1行。

排序

SELECT prod_name FROM Products ORDER BY prod_name;

按照多个列排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;

指定排序方向（默认升序）

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;

范围值查找

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;

空值查找

SELECT prod_name FROM Products WHERE prod_price IS NULL;

AND操作符

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;

LIKE操作符 最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

另一个有用的通配符是下划线（_）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';

UPPER()将文本转换为大写

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;

聚集函数

分组是使用SELECT语句的GROUP BY子句建立的。

SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;