MySQL(B站CodeWithMosh)——2024.10.9(12)

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

5- ALL关键字 | The ALL Keyword_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=49&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 在sql_invoincing中，输出比客户3的发票数额大的所有发票
   USE sql_invoicing;
   SELECT *
   FROM invoices
   WHERE invoice_total > (
         SELECT MAX(invoice_total)
         FROM invoices
         WHERE client_id = 3)
   或者
   SELECT *
   FROM invoices
   WHERE invoice_total > ALL (
         SELECT invoice_total
         FROM invoices
         WHERE client_id = 3)
   对于第一种写法，因为客户3的发票有很多，所以我们要先输出客户3发票的最大值，然后才能筛选出比客户3的发票数额大的所有发票
   对于第二种写法，如果不想用最大值的方法，那么可以保证比客户3所有的发票都大就行，所以这里用到了ALL运算符

6- ANY关键字 | The ANY Keyword_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=50&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. ALL、ANY和SOME运算符表达的意思是一样的
   输出发票数大于等于2的客户信息
   FROM clients
   WHERE client_id IN (
   SELECT client_id
   FROM invoices
   GROUP BY client_id
   HAVING COUNT(*) >= 2)
   或者
   SELECT *
   FROM clients
   WHERE client_id = ANY (
   SELECT client_id
   FROM invoices
   GROUP BY client_id
   HAVING COUNT(*) >= 2)

7- 相关子查询 | Correlated Subqueries_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=51&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 在sql_hr中，返回所有工资大于本部门平均值的员工
   USE sql_hr;
   SELECT *
   FROM employees e
   WHERE salary > (
   SELECT AVG(salary)
   FROM employees
   WHERE office_id = e.office_id)
   为什么后面的office_id需要用等号连接呢，因为需要将内部的employees表格的office_id和外部employees表格连接起来，所以要用e.office_id表示外部表格的id

【练习题】
在sql_invoincing中，返回所有发票大于该客户发票平均值的发票
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE invoice_total > (
SELECT AVG(invoice_total)
FROM invoices
WHERE client_id = i.client_id)

8- EXISTS运算符 | The EXISTS Operator_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=52&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 返回有发票的顾客
   USE sql_invoicing;
   SELECT *
   FROM clients c
   WHERE EXISTS(
                 SELECT client_id
                 FROM invoices
                 WHERE client_id = c.client_id)
   EXISTS语句并没有输出所有有发票的顾客id，而是一个一个判断是否存在满足条件的顾客

【练习题】
在sql_store中，用EXISTS语句输出从来没有被订购过的产品
USE sql_store;
SELECT *
FROM products p
WHERE NOT EXISTS (
  SELECT p.product_id
  FROM order_items oi
  WHERE p.product_id = oi.product_id)

9- SELECT子句中的子查询 | Subqueries in the SELECT Clause_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=53&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 输出下面的图片，其中invoice_average是所有发票的平均值，difference是invoice_total和difference的差值
   
   USE sql_invoicing;
   SELECT invoice_id, invoice_total, 
          (SELECT AVG(invoice_total) FROM invoices) AS invoice_average, 
          invoice_total-(SELECT invoice_average) AS difference
   FROM invoices
   GROUP BY invoice_id
   在计算difference的时候，是不能直接用invoice减去invoice_average的，可以重新计算一遍invoice_average，也可以为了方便，写成SELECT invoice_average，因为之前已经定义过invoice_average了

【练习题】
输出下面的图片，total_sales是每位顾客开出发票的总和，average是所有发票总记的平均值，difference是前两列的差值

USE sql_invoicing;
SELECT client_id, name,
       SUM(invoice_total) AS total_sales,
       (SELECT AVG(invoice_total) FROM invoices) AS average,
       SUM(invoice_total)-(SELECT average) AS difference
FROM invoices
RIGHT JOIN clients USING (client_id)
GROUP BY client_id
ORDER BY client_id

10- FROM子句中的子查询 | Subqueries in the FROM Clause_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=54&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 对于上面那个代码输出的表格是一个虚拟表格，我们可以对这个虚拟表格进行操作，把他变成我们可以筛选数据的表格
   SELECT *
   FROM (
       SELECT client_id, name,
              SUM(invoice_total) AS total_sales,
              (SELECT AVG(invoice_total) FROM invoices) AS average,
              SUM(invoice_total)-(SELECT average) AS difference
       FROM invoices
       RIGHT JOIN clients USING (client_id)
       GROUP BY client_id
       ORDER BY client_id) AS sales_summary
   WHERE total_sales IS NOT NULL
   可以看到，我们将他放在FROM中，并把它取名为sales_summary，然后筛选出有销售量的数据信息

【第七章】1- 数值函数 | Numeric Functions「MySQL的基本函数」_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=55&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. ROUND：对数值进行四舍五入
   SELECT ROUND(5.73)
   输出为6
   SELECT ROUND(5.73,1)
   输出为5.7
2. TRUNCATE：对数值进行截断
   SELECT TRUNCATE(5.7345,1)
   输出为5.7
3. CEILING：返回大于或者等于这个数字的最小整数
   SELECT CEILING(5.7345)
   输出为6
4. FLOOR：返回小于或者等于这个数字的最大整数
   SELECT CEILING(5.7345)
   输出为5
5. ABS：计算绝对值
   SELECT ABS(5.7345)
   输出为5.7345
6. RAND：生成0-1区间的随机浮点数
   SELECT RAND()
   输出为'0.5924817711044864
   上面是常见的处理数值的函数，可以在网上搜索mysql numeric functions获取全部的函数