《SQL必知必会》—— 检索1

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必知必会》系列索引: