sql_study_02_汇总，复杂查询，内置函数

基础进阶-汇总，复杂查询，内置函数

一、汇总数据

1. Aggregate Funtions

   1. MAX(列名) – 获取这列的最大值
   2. MIN(列名) – 获取这列的最小值
   3. AVG(列名) – 获取这列的平均值
   4. SUM(列名) – 获取这列的总和
   5. COUNT() – 统计出现的次数

   USE sql_invoicing;
   -- Aggregate Functions
   SELECT
       MAX(invoice_total) AS highest,
       MIN(invoice_total) AS lowest,
       AVG(invoice_total) AS average,
       SUM(invoice_total * 1.1) AS total,
       COUNT(DISTINCT client_id) AS total_records
   FROM invoices
   WHERE invoice_date > '2019-07-01';
   

   

   -- Aggregate Functions Exercise
   SELECT
       'First half of 2019' AS data_range,
       SUM(invoice_total) AS total_sale,
       SUM(payment_total) AS total_payments,
       SUM(invoice_total - payment_total) AS what_we_expect
   FROM invoices
   WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
   UNION
   SELECT
       'Second half of 2019' AS data_range,
       SUM(invoice_total) AS total_sale,
       SUM(payment_total) AS total_payments,
       (SUM(invoice_total)-SUM(payment_total)) AS what_we_expect
   FROM invoices
   WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
   UNION
   SELECT
       'total' AS data_range,
       SUM(invoice_total) AS total_sale,
       SUM(payment_total) AS total_payments,
       (SUM(invoice_total)-SUM(payment_total)) AS what_we_expect
   FROM invoices
   WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31';
   

   

2. The Group By Clause

   1. GROUP BY

      1. 在SELECT子句中使用了聚合函数后，如果有除聚合函数以外的列名出现，必须要用GROUP BY做分组
         1. 我是这样理解的，因为state,city他们是一组数据，而SUM（invoice_total）是所有invoice_total列的总和。如果不分组处理的话，数据库不知道如何处理他们之间的对应关系，所以必须加GROUP BY来指明他们的对应关系

      -- The Group By  Clause
      SELECT
          state,
          city,
          SUM(invoice_total) AS total_sales
      FROM invoices
      JOIN clients USING (client_id)
      WHERE invoice_date >= '2019-07-01'
      GROUP BY state,city
      ORDER BY total_sales DESC;
      

      

      -- The Group By Clause Exercise
      SELECT
          date,
          pm.name AS pay_method,
          SUM(amount) AS total_payments
      FROM payments p
      JOIN payment_methods pm
      ON p.payment_method = pm.payment_method_id
      GROUP BY date,payment_method
      ORDER BY date;
      

      

3. The Having Clause

   1. HAVING

      1. 相当于WHERE，但是是用于分组后的条件筛选，且条件列名必须是在SELECT语句中出现的
      2. WHERE中无法使用列别名

      -- The Having Clause
      -- 使用聚合函数（如 SUM 和 COUNT）时，所有非聚合字段（如 client_id）必须出现在 GROUP BY 子句中
      -- 聚合函数的别名只能在分组后使用
      -- Having在分组后查询，条件列名必须在select语句中出现的，WHERE（排序前）则没有这种限制
      USE sql_invoicing;
      SELECT
          client_id,
          SUM(invoice_total) AS total_sales,
          COUNT(*) AS number_of_invoices
      FROM invoices
      GROUP BY client_id
      HAVING total_sales > 500 AND number_of_invoices > 5;
      

      

      -- The Having Clause Exercise
      USE sql_store;
      SELECT
          c.customer_id,
          SUM(oi.quantity * oi.unit_price) AS spent
      FROM customers c
      JOIN orders o USING (customer_id)
      JOIN order_items oi ON o.order_id = oi.order_id
      WHERE state = 'VA'
      GROUP BY c.customer_id
      HAVING spent > 100;
      

      

4. The ROLLUP Operator

   1. ROLLUP统计数据,（GROUP BY的拓展）在分组后，为每一个分组自动生成汇总行

   -- The ROLLUP Operator
   USE sql_invoicing;
   SELECT
       client_id,
       SUM(invoice_total) AS total_sales
   FROM invoices
   GROUP BY client_id WITH ROLLUP ;
   

   

   -- 统计多列 每一层都会统计，同一个州，下面的所有市
   SELECT
       c.state,
       c.city,
       SUM(invoice_total) AS total_sales
   FROM invoices i
   JOIN clients c USING (client_id)
   GROUP BY
            c.state,
            c.city
   WITH ROLLUP ;
   

   

   -- The ROLLUP Exercise
   -- 使用ROLLUP时，不能在GROUP BY子句中使用列别名,不过好像正常情况也不能用别名，因为sql他是先执行GROUP BY子句的，然后才执行SELECT子句
   SELECT
       pm.name AS pay_method,
       SUM(p.amount) AS total
   FROM payments p
   JOIN  payment_methods pm ON p.payment_method = pm.payment_method_id
   GROUP BY pm.name
   WITH ROLLUP ;
   

   

二、编写复杂查询

1. SubQuery

   1. WHERE 子查询

   -- SubQuery
   USE sql_store;
   SELECT *
   FROM products
   WHERE unit_price > (
       SELECT unit_price
       FROM products
       WHERE product_id = 3
   );
   

   

   2. IN

   USE sql_store;
   -- IN Operator
   SELECT *
   FROM products
   WHERE product_id NOT IN (
       SELECT DISTINCT product_id
       FROM order_items
   );
   

   

2. Subqueries VS Joins

   1. Subqueries和Join的不同写法

   -- 获取买了产品3的用户信息
   USE sql_store;
   -- sub query
   SELECT customer_id,
          first_name,
          last_name
   FROM customers
   WHERE customer_id IN (
       SELECT DISTINCT customer_id
       FROM orders
       WHERE order_id IN (
           SELECT order_id
           FROM order_items
           WHERE product_id = 3
       )
   );
   
   -- subqueries and join
   SELECT
       c.customer_id,
       c.first_name,
       c.last_name
   FROM customers c
   WHERE customer_id IN (
       SELECT o.customer_id
       FROM order_items oi
           JOIN orders o
               USING (order_id)
       WHERE product_id = 3
       );
       
   -- join
   SELECT DISTINCT c.customer_id,
          c.first_name,
          c.last_name
   FROM order_items oi
            JOIN orders o
                 USING (order_id)
            JOIN customers c
                 USING (customer_id)
   WHERE product_id = 3;
   

   

3. ALL MAX ANY

   1. MAX 获取选中列的最大值

      USE sql_invoicing;
      -- MAX
      SELECT *
      FROM invoices
      WHERE invoice_total > (
          SELECT MAX(invoice_total)
          FROM invoices
          WHERE client_id = 3
          );
      

   2. ALL 所有值满足条件

      -- ALL
      SELECT *
      FROM invoices
      WHERE invoice_total > ALL (
          SELECT invoice_total
          FROM invoices
          WHERE client_id = 3
          );
      

   3. ANY SOME任意一个值满足条件就可

      SELECT *
      FROM clients
      WHERE client_id = ANY (
          SELECT client_id
          FROM invoices
          GROUP BY client_id
          HAVING COUNT(*) > 2
          );
      SELECT *
      FROM clients
      WHERE client_id = SOME (
          SELECT client_id
          FROM invoices
          GROUP BY client_id
          HAVING COUNT(*) > 2
          );
      

4. correlated subqueries

   1. 相关子查询

      1. 子查询依赖外部查询的一些数据

         -- correlated subQueries
         -- 查询薪资高于所属部门的平均水平的员工
         USE sql_hr;
         SELECT *
         FROM employees e
         WHERE salary > (
             SELECT AVG(salary)
             FROM employees
             WHERE office_id = e.office_id
             );
         

         

         SELECT AVG(invoice_total) FROM invoices GROUP BY client_id;
         

         

         -- correlated subQueries exercise
         -- 查询用户发票中超过该用户所有订单的平均值的发票信息
         USE sql_invoicing;
         SELECT *
         FROM invoices i
         WHERE invoice_total > (
             SELECT AVG(invoice_total)
             FROM invoices
             WHERE client_id = i.client_id
             );
         

5. The EXIST Operator

   1. EXIST

      1. SELECT 没查询一条数据都会进入EXIST 子句查询invoices表中是否存在一条符合条件的一条记录，有则返回TRUE,否则返回FALSE

      -- The EXIST Operator
      -- 查询有发票的客户
      SELECT *
      FROM clients c
      WHERE EXISTS(
          SELECT client_id
          FROM invoices i
          WHERE i.client_id = c.client_id
                );
      

      

6. SubQueries in the SELECT Clause

   1. 在SELECT 子句中使用子查询

      -- Sub queries in the SELECT Clause
      USE sql_invoicing;
      SELECT
          invoice_id,
          invoice_total,
          (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
          invoice_total - (SELECT invoice_average) AS differnce
      FROM invoices;
      

      

      -- Exercise
      -- 相关子查询也同样试用
      SELECT
          client_id,
          name,
          (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
          (SELECT AVG(invoice_total) FROM invoices) AS average,
          (SELECT total_sales - average) AS difference
      FROM clients c;
      

      

7. subQuery in the from

   1. 将子查询作为一张新表，WHERE可以使用该表中的字段作为条件

      -- sub query in the from
      SELECT *
      FROM (
           SELECT
              client_id,
              name,
              (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
              (SELECT AVG(invoice_total) FROM invoices) AS average,
              (SELECT total_sales - average) AS difference
          FROM clients c
      ) AS sales_summary
      WHERE total_sales IS NOT NULL ;
      

      

三、MySQL中的基本函数

8. Numberic Functions

   -- Numberic Functions
   SELECT ROUND(5.752,1); -- 四舍五入(原始数，小数点后要保留的位数) 5.8
   SELECT TRUNCATE(5.752,1); -- 截断 5.7
   SELECT CEILING(5.721,2); -- 天花板函数 5.73
   SELECT FLOOR(5.923,2); -- 地板函数 5.92
   SELECT ABS(-5.2); -- 绝对值函数 5.2
   SELECT RAND(); -- 随机0-1函数
   

9. String Functions

   -- String Functions
   SELECT LENGTH('key');
   SELECT UPPER('key');
   SELECT LOWER('KEY');
   SELECT LOWER('KEY');
   SELECT LTRIM('  KEY');
   SELECT RTRIM('KEY  ');
   SELECT TRIM('    k   ey'); -- 删除字符串开头和结尾的空格
   SELECT RIGHT('kindergarten',2); -- 从右边开始截取固定长度的字符串，同理LEFT
   SELECT SUBSTRING('kindergarten',2,2); -- SUBSTRING('kinder',firstLocation,endLocation)
   SELECT LOCATE('garten','kindergarten'); -- 没有则返回0
   SELECT REPLACE('kindergarten','garten','garden'); -- 没有则返回0
   SELECT CONCAT('kindergarten','garten','garden');
   

10. Date Functions

    -- Date Functions
    SELECT YEAR(NOW()); -- 获取当前年份
    USE sql_store;
    SELECT * FROM orders
    WHERE YEAR(order_date) = YEAR(NOW());
    SELECT CURDATE();
    SELECT NOW();
    

11. TimeFormat And DATE_FORMAT

    -- DATE_Format
    SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日  %H:%i:%s');
    

12. IFNULL、COALESCE、IF、CASE

    -- IFNULL 和 COALESCE
    -- IFNULL 函数用于检查第一个参数是否为 NULL，如果是，则返回第二个参数；如果不是，则返回第一个参数。
    -- COALESCE 函数返回其参数中第一个不为 NULL 的值。它可以接受两个或更多参数。COALESCE 在参数数量上比 IFNULL 更灵活。
    SELECT
        customer_id,
        IFNULL(shipper_id,'No Assignment') AS shipper,
        COALESCE(shipper_id,comments,'No Assignment')
    FROM orders;
    -- Exercise
    SELECT
        CONCAT(first_name,last_name) AS customer,
        IFNULL(phone,'Unknown') AS phone
    FROM customers;
    
    
    -- IF Function
    -- IF(条件表达式，'ture','false')
    SELECT
        order_id,
        order_date,
        IF(YEAR(order_date) = YEAR(NOW()),'active','archived') AS catory
    FROM orders;
    
    SELECT COUNT(product_id) FROM order_items
    GROUP BY product_id;
    SELECT
        DISTINCT product_id,
        p.name,
        COUNT(product_id) AS orders,
        IF(COUNT(product_id) > 1,'Many times','Once') AS frequecy
    FROM order_items oi
    JOIN products p USING (product_id)
    GROUP BY product_id, p.name;
    
    -- 判断多个条件用CASE
    -- CASE
    --     WHEN  条件表达式   THEN  '如果满足条件后的值'
    --     WHEN  条件表达式   THEN  '如果满足条件后的值'
    --     。。。
    --     ELSE '如果都不满足上面的条件，则为这个值，ELSE可省略'
    -- END
    SELECT
        order_id,
        CASE
            WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active'
            WHEN YEAR(order_date) = YEAR(NOW())-1 THEN 'Last Year'
            WHEN YEAR(order_date) < YEAR(NOW())-1 THEN 'Archived'
            ELSE 'Future'
        END AS category
    FROM orders;
    
    -- CASE Exercise
    SELECT
        CONCAT(first_name,last_name) AS customer,
        points,
        CASE
            WHEN points > 3000 THEN 'Gold'
            WHEN points >= 2000 THEN 'Sliver'
            ELSE 'Bronze'
        END AS category
    FROM customers
    ORDER BY points DESC;