【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