MySQL(B站CodeWithMosh)——2024.10.6(9)

ZZZZZZ目的
ZZZZZZ代码
ZZZZZZ重点
ZZZZZZ操作（非代码，需要自己手动）

9- 删除行 | Deleting Rows_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=39&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 删除名为Myworks的顾客的发票
   DELETE FROM invoices
   WHERE client_id = (SELECT * FROM client WHERE name = 'Myworks')

10- 恢复数据库 | Restoring the Databases_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=40&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 将数据恢复到原来最开始的样子
   点击MySQL上方的File，Open SQL Scripts，找到存储SQL脚本的目录位置(我的在D盘MySQL文件夹下面)，打开create-database.sql文件，执行脚本，重建所有数据库

【第五章】1- 聚合函数 | Aggregate Functions「汇总数据」_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=41&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. MySQL中有一些内置函数，例如MAX()、MIN()、AVG()、SUM()、COUNT()等，这些函数需要使用阔靠来调用或者执行
2. 输出sql_invoicing中invoices表格中invoice_total列的最大值、最小值、平均值、总和、计数，并分别将其命名为highest、lowest、average、total和number_of_invoices
   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
   FROM invoices
   别忘记函数之间的逗号
3. 还可以将这些函数用到日期上
   输出的支付日期中离我们现在最近的日期
   SELECT MAX(payment_date) AS highest
   FROM invoices
4. 如果列中有空值，那么空值是不被算在函数里面的
   分别计数invoice_total和payment_date
   SELECT COUNT(invoice_total) AS number_of_invoices,
                  COUNT(payment_date) AS count_of_payments
   FROM invoices
   输出分别是17和7，因为并不是所有的订单都支付了的
   得到表格中所有记录条目，不管是不是空值
   SELECT COUNT(*) AS total_records
   FROM invoices
5. 对2019年7月1日之后，顾客id计数(排除重复值)
   SELECT COUNT(DISTINCT client_id) AS total_records
   FROM invoices
   WHERE invoice_date > '2019-07-01'

【练习题】
按照下面的图片输出，what_we_expect这一列是total_sales和total_payments的差值

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'

1. 一共有三行输出，可以用UNION连接
2. 在计算total_payments时，不能用total_sales-total_payments(我也不知道为啥，记住吧)
3. 一定要注意拼写，有的要加s，有的不加s
4. 第三行虽然是total，但也是2019年的total，还是要用到WHERE和BETWEEN
5. 别忘记了FROM invoices

2- GROUP BY子句 | The GROUP BY Clause_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=42&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 在invoices表中，输出每个顾客的在2019年7月1日之后的总消费额，并将总消费额取名为total_sales，按照总消费额降序来排列
   SELECT client_id, SUM(invoice_total) AS total_sales
   FROM invoices
   WHERE invoice_date > '2019-07-01'
   GROUP BY client_id
   ORDER BY total_sales DESC
   注意每个运算符的顺序，应该先是WHERE，然后才是GROUP BY，ORDER BY，可以这样想，先确定下来数据范围，然后再考虑数据的输出方式
2. 根据state和city来计算总消费额，并将总消费额取名为total_sales
   SELECT state, city, SUM(invoice_total) AS total_sales
   FROM invoices i
   JOIN clients c USING (client_id)
   GROUP BY state, city

【练习题】
输出下面这张图片

SELECT i.payment_date AS date, pm.name AS payment_method, SUM(i.payment_total) AS total_payments
FROM payments p
JOIN payment_methods pm ON pm.payment_method_id = p.payment_method
JOIN invoices i USING (invoice_id)
GROUP BY i.payment_date, pm.name
ORDER BY date
注意，要对i.payment_total求和；GROUP BY尽量用原始的列明，不要用自己取的名字，MySQL可能会识别不出来