删除行.聚合函数（汇总数据）

/*使用delect from删除表的记录*/
DELETE FROM invoices#删除表里全部的记录
DELETE FROM invoices#删除表里部分记录
WHERE invoice_id = 1

DELETE FROM invoices#删除表里部分记录
WHERE client_id = (
	SELECT client_id
    FROM clients
    WHERE name = 'Myworks')

聚合函数

/*选择invoices表的invoice_total列的最大值*/
SELECT MAX(invoice_total)
FROM invoices

 

 

/*
聚合函数，只运行非空值，有空值不会算在函数里
如count(invoice_total)返回有invoice_total记录数的不管是不是重复
MAX()
MIN()
AVG()
SUM()
COUNT()计数
*/
/*选择invoices表的invoice_total列的最大值*/
SELECT 
	MAX(payment_date) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total * 1.1) AS total,#先每个列乘1.1在求和
    COUNT(invoice_total) AS number_of_invoices,#注意虽然表格19行，但是12和14行没有
    COUNT(payment_date) AS count_of_payments
FROM invoices

/*
聚合函数，只运行非空值，有空值不会算在函数里
MAX()
MIN()
AVG()
SUM()
COUNT()计数
如果要得到表格中所有数据的条目不管是不是空值需要使用COUNT(*)
*/
/*选择invoices表的invoice_total列的最大值*/
SELECT 
	MAX(payment_date) 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'

 /*会取重复条目，如果想要排除重复条目，需要使用distinct*/

/*
会取重复条目，如果想要排除重复条目，需要使用distinct*/
SELECT 
	MAX(payment_date) 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(client_id) AS total_records#返回有client_id记录数的，不管有没有重复
FROM invoices

/*
会取重复条目，如果想要排除重复条目，需要使用distinct*/
SELECT 
	MAX(payment_date) 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(DISTINCT client_id) AS total_records#返回有client_id记录数的，不管有没有重复
FROM invoices

练习

在invoices表中得到下列结果

 

 

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 
	'Toal' 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'