检索数据
select
检索不同的值
distinct
限制结果
limit 第0行开始 limit 0,1 返回第一个
注释
-- # /* */
排序数据
order by
select * from orderitems order by order_item, quantity;
先按照order_item排序,再按照quantity排序;
默认升序,降序用 desc
过滤数据
where
范围值检查
BETWEEN 5 AND 10
空值检查
IS NULL
组合WHERE子句
AND OR NOT
IN操作符
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;
通配符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
%表示任何字符出现任意次数
_下划线的用途与%一样,但它只匹配单个字符,而不是多个字符。
计算字段
SELECT Concat(vend_name, ' (', vend_country, ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
聚集函数
数据分组
GROUP BY
过滤分组
HAVING
HAVING支持所有WHERE操作符
SELECT子句顺序
子查询
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Order
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));