SQL-学习笔记（第四周）

之前的B站链接失效了，又重新找到了mosh课程链接：【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！_哔哩哔哩_bilibili

大神整理的视频中使用到的数据库：https://pan.baidu.com/s/1KMrI0NqCbWagKI_oK_Rndw?pwd=g5rp 提取码: g5rp

数据恢复后操作

知识点：
1、聚合函数:
sum()、max()、min()、avg()、total()、count()

1、聚合函数

use sql_invoicing;

SELECT max(invoice_total)
FROM invoices 

#注意：max与（）之间不可有空格

---

use sql_invoicing;

SELECT 
    MAX(invoice_total) AS hightest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    COUNT(invoice_total) AS number_of_invoices
FROM invoices 

---

允许空值字段，统计非空数量

use sql_invoicing;

SELECT 
    MAX(invoice_total) AS hightest,
    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 number_of_payments,
    COUNT(*) AS total_recordes
FROM invoices 

---

添加条件、统计总记录数量

use sql_invoicing;

SELECT 
    MAX(invoice_total) AS hightest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total*1.1) AS total,
    
    COUNT(*) AS total_recordes
FROM invoices 
WHERE invoice_date>'2019-07-01'

---

use sql_invoicing;

SELECT 
    MAX(invoice_total) AS hightest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total*1.1) AS total,
    
    COUNT(client_id) AS total_recordes
FROM invoices 
WHERE invoice_date>'2019-07-01'

---

#DISTINCT 只统计唯一值

use sql_invoicing;

SELECT 
    MAX(invoice_total) AS hightest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total*1.1) AS total,
    
    COUNT(DISTINCT client_id) AS total_recordes
FROM invoices 
WHERE invoice_date>'2019-07-01'

练习

---

use sql_invoicing;

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