之前的B站鏈接失效了，又重新找到了mosh課程鏈接：【中字】SQL進(jìn)階教程 | 史上最易懂SQL教程阱驾！10小時(shí)零基礎(chǔ)成長(zhǎng)SQL大師＞兔铡！_嗶哩嗶哩_bilibili

大神整理的視頻中使用到的數(shù)據(jù)庫(kù)：https://pan.baidu.com/s/1KMrI0NqCbWagKI_oK_Rndw?pwd=g5rp 提取碼: g5rp

數(shù)據(jù)恢復(fù)后操作

知識(shí)點(diǎn)：
1啊易、聚合函數(shù):
sum()吁伺、max()饮睬、min()租谈、avg()、total()捆愁、count()

1割去、聚合函數(shù)

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 

允許空值字段，統(tǒng)計(jì)非空數(shù)量

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 

添加條件昼丑、統(tǒng)計(jì)總記錄數(shù)量

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 只統(tǒng)計(jì)唯一值

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'

練習(xí)

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'