【SQL必知必會】SQL知識查缺補漏

一、使用函數處理資料

1、字元串處理函數-顧客登入名【sql22】

思路1：substring(word,1,n)、upper、concat

SELECT
    cust_id,
    cust_name,
    UPPER(CONCAT(SUBSTRING(cust_name,1,2),SUBSTRING(cust_city,1,3))) AS user_login
FROM Customers      

思路2：LEFT

SELECT
    cust_id,
    cust_name,
    UPPER(CONCAT(LEFT(cust_name,2),LEFT(cust_city,3))) AS user_login
FROM Customers      

二、分組資料

1、傳回總價不低于1000的訂單号和總價

思路：注意分組後的最終結果也要SUM求和

SELECT order_num,
    SUM(item_price*quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price>=1000
ORDER BY order_num ASC      

三、多表連接配接

0、概述

内連接配接、等值連接配接（與内連接配接可以互相轉換）、自然連接配接（natural join很少用）、外連接配接（左右全）

1、傳回顧客名稱和相關訂單号以及每個訂單的總價

切記：每個使用者可能也會有多個訂單

解答：

SELECT 
    cust_name,
    B.order_num,
    SUM(quantity*item_price) AS OrderTotal
FROM Customers A
JOIN Orders B
JOIN OrderItems C
ON 
    A.cust_id=B.cust_id
    AND
    B.order_num=C.order_num
GROUP BY cust_name,B.order_num
ORDER BY cust_name,C.order_num      

2、傳回産品名稱和每一項産品的總訂單數

注意：如果用聚合函數，需要進行分組group by

SELECT
    prod_name,
    COUNT(order_num) AS orders
FROM Products A
LEFT JOIN OrderItems B
ON A.prod_id=B.prod_id
GROUP BY prod_name
ORDER BY prod_name      

四、組合查詢

1、組合兩張表的不同列

Products 表中的産品名稱和 Customers 表中的顧客名稱

SELECT prod_name FROM Products
UNION ALL
SELECT cust_name AS prod_name FROM Customers
ORDER BY prod_name