检索单个列
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;