image.png
普通检索
SELECT prod_name FROM products; -- 单列
SELECT prod_name, prod_id, prod_price FROM products; -- 多列
SELECT * FROM products; -- 所有列
SELECT DISTINCT vend_id FROM products; -- 只检索出不同值
SELECT prod_name FROM products LIMIT 5, 3; -- 1)限制结果(从第三行起检索5行), MySQL是从第5行开始检索3行
SELECT prod_name FROM products LIMIT 5 OFFSET 3; -- 2)限制结果(OFFSET可不写，默认为0)
排序检索
SELECT prod_name FROM products ORDER BY prod_name; --(ORDER BY必须是SELECT语句中的最后一条子句)
SELECT prod_name, prod_id, prod_price FROM products ORDER BY prod_price, prod_name; -- 按多个列排序(先按prod_price排序，prod_price相同时再按prod_name排序)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3; -- 按列位置排序(2, 3代表prod_id, prod_price, prod_name中的prod_price, prod_name)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC; -- 指定排序方向(在多列进行降序排序时，必须对每一列指定DESC)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name; -- 先按prod_price进行降序，prod_price相同时再按prod_name排序(默认升序)
过滤数据
SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price = 3.49;
SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price BETWEEN 5 AND 10; --范围检索(包括5和10)
SELECT cust_name FROM customers WHERE cust_email IS NULL; --空值检索
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 'DLL01' AND prod_price <= 4; --AND操作符
SELECT prod_id, prod_name, prod_price, vend_id FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'; --OR操作符
SELECT prod_id, prod_name, prod_price, vend_id FROM products --AND优先级高于OR
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10; --等价于(WHERE vend_id = 'DLL01' OR (vend_id = 'BRS01' AND prod_price >= 10);)
--修正
SELECT prod_id, prod_name, prod_price, vend_id FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
--等价于
SELECT prod_id, prod_name, prod_price, vend_id FROM products
WHERE vend_id IN ('DLL01', 'BRS01') AND prod_price >= 10; --IN操作符(与OR相当)
SELECT prod_id, prod_name, prod_price, vend_id FROM products
WHERE NOT vend_id IN ('DLL01', 'BRS01'); --NOT操作符
通配符过滤（只能用于文本字段(字符串)）
--%通配符(%表示任何字符任意次数)
--%不会匹配值为NULL的行
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'Fish%'; --（匹配任意以Fish开头的值）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%bean bag%'; --（匹配任何位置上包含bean bag的值）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'F%y'; -- （匹配以F开头、y结尾的值）
--_通配符(只匹配任意单个字符)
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '__ inch teddy bear';
计算字段
SELECT CONCAT(vend_name,' (',vend_country, ')') AS vend_title FROM vendors; --CONCAT拼接字段、AS别名
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20008; -- 创建字段
函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors; --文本处理函数
SELECT order_num FROM orders WHERE YEAR(order_date) = 2012; --某一年
SELECT cust_id, order_num FROM orders WHERE DATE(order_date) = '2005-09-01'; -- 某一天
SELECT cust_id, order_num FROM orders WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9; -- 某个月
--数值处理函数 ABS()
汇总数据(不需要检索实际数据，只需要汇总数据)
-- 聚集函数
SELECT AVG(prod_price) AS avg_price FROM products; --AVG()返回某列的平均值
SELECT COUNT(*) AS num_cust FROM customers; --COUNT(*)统计表中行的总数(包含NULL)
SELECT COUNT(cust_email) AS num_cust FROM customers; --COUNT(cust_email)统计cust_email中行的总数(不包含NULL)
SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS min_price FROM products;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005; --返回订单中所有物品的数量
-- 聚集不同值(DISTINCT)
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 'DLL01';
-- 组合聚集函数
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(DISTINCT prod_price) AS price_avg
FROM products;
数据分组(GROUP BY)
-- GROUP BY可以包含任意数目的列，因此可以对分组进行嵌套，更细致地进行数据分组
-- 如果在GROUP BY中嵌套了分组，数据将在最后指定的分组上进行汇总
-- GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。不能使用别名
-- 大多数SQL实现不允许带有可变的数据类型(如文本或备注型字段)
-- 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
-- 如果分组列中包含NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，他们将分为一组
-- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id; --汇总各个供应商的产品数
-- WHERE过滤行，HAVING过滤分组
-- WHERE在数据分组前过滤，HAVING在数据分组后过滤
SELECT cust_id, COUNT(*) AS order_num FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; --过滤分组(只需要两个以上订单量的分组)
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2; --列出具有两个以上产品且其价格大于等于4的供应商
SELECT order_num, COUNT(*) AS items FROM orderitems
GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num; --1.GROUP BY分组，2.HAVING过滤分组，3.ORDER BY排序
-- SELECT子句顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
《SQL必知必会》系列索引:
