MYSQL学习笔记（自用）第四章

最新推荐文章于 2024-09-15 20:24:46 发布

Bro Ze

最新推荐文章于 2024-09-15 20:24:46 发布

阅读量111

点赞数 1

分类专栏： MYSQL学习笔记文章标签： mysql

本文链接：https://blog.csdn.net/qq_40038682/article/details/124325068

版权

MYSQL学习笔记专栏收录该内容

10 篇文章 1 订阅

订阅专栏

本文深入探讨了SQL中的聚合函数如MAX、MIN、AVG、SUM和COUNT的使用，通过实例展示了如何在sql_invoicing数据库中进行数据汇总。同时，讲解了GROUP BY子句用于按特定字段分组数据，以及HAVING子句在分组后的过滤条件。此外，还介绍了ROLLUP运算符在创建汇总报告中的应用，提供了一组练习题以巩固所学知识。

摘要由CSDN通过智能技术生成

第四章

第一节. 聚合函数|Aggregate Functions [汇总数据]

MAX()
MIN()
AVG()
SUM()
COUNT()

USE sql_invoicing;

SELECT 
       MAX(invoice_total) AS highest,
       MIN(invoice_total) AS lowest,
	   AVG(invoice_total) AS average,
       SUM(invoice_total) AS total,
       COUNT(invoice_total) AS number_of_invoices,
       COUNT(payment_date) AS count_of_payments,
	   COUNT(*) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'

第一节. 作业练习

SELECT
      'First half of 2019' AS date_range,
      SUM(invoice_total) AS total_sales,
      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 date_range,
      SUM(invoice_total) AS total_sales,
      SUM(payment_total) AS total_payments,
      SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date 
	BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT
      'Total' AS date_range,
      SUM(invoice_total) AS total_sales,
      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-12-31'

第二节. GROUP BY子句| The GROUP BY Clause

SELECT
      client_id,
      SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
WHERE invoice_Date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC

SELECT
      state,
      city,
      SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY client_id

第二节. 作业练习

SELECT
	p.date,
    pm.name AS payment_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

第三节. HAVING子句| The HAVING CLAUSE

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

第三节. 作业练习

USE sql_store;

SELECT
     c.first_name,
     c.last_name,
     state,
     SUM(oi.quantity * oi.unit_price) AS total_cost
FROM customers c
JOIN orders o
  USING(customer_id)
JOIN order_items oi
  USING(order_id)
GROUP BY customer_id, state
HAVING total_cost > 100 AND state = 'VA'

第四节. ROLLUP运算符| The ROLLUP Operato

USE sql_invoicing;

SELECT
     client_id,
     SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP

SELECT
     state,
     city,
     SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP

第四节. 作业练习

SELECT
     pm.name,
     SUM(p.amount)
FROM payments p
JOIN payment_methods pm
  ON p.payment_method = pm.payment_method_id
GROUP BY name WITH ROLLUP