ZZZZZZ目的
ZZZZZZ代码
ZZZZZZ重点
ZZZZZZ操作(非代码,需要自己手动)
4- ROLLUP运算符 | The ROLLUP Operator_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=44&vd_source=eaeec77dfceb13d96cce76cc299fdd08
- 在sql_invoicing中invoices表,按照顾客id分组,计算每组顾客的消费总和,并将其取名为total_sales,并输出所有顾客的消费综合
SELECT client_id, SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
ROLLUP只能应用于聚合值的列,所以client_id那一列ROLLUP之后是空值,而total_sales是所有的值相加 - 根据state和city来计算总消费额,并将总消费额取名为total_sales,并计算每组的总和,所有的总和
USE sql_invoicing;
SELECT state, city, SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP
当进行多列分组,并运用ROLLUP运算符的时候,会看到每个组及整个结果集的汇总值,但是ROLLUP这个运算符只能在MySQL中使用,在SQL server或者oracle都用不了
【练习题】
用payment表来输入下面的图
SELECT pm.name AS payment_method, SUM(amount) AS total
FROM payments p
JOIN payment_methods pm ON pm.payment_method_id = p.payment_method
GROUP BY pm.name WITH ROLLUP
【第六章】1- 介绍 | Introduction「编写复杂查询」_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=45&vd_source=eaeec77dfceb13d96cce76cc299fdd08
- 恢复数据
点击MySQL上方的File,Open SQL Scripts,找到存储SQL脚本的目录位置(我的在D盘MySQL文件夹下面),打开create-database.sql文件,执行脚本,重建所有数据库
2- 子查询 | Subqueries_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=46&vd_source=eaeec77dfceb13d96cce76cc299fdd08
- 在sql_store的products中,找到比生菜(id=3)单价贵的商品
SELECT * FROM sql_store.products
WHERE unit_price >
( SELECT unit_price FROM sql_store.products WHERE product_id = 3)
sql_store.products这种方法就不用USE sql_store了
这种在一层代码里面增加一层的,就叫做子查询
【练习题】
在sql_hr中,输出比平均工资高的员工
USE sql_hr;
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
3- IN运算符 | The IN Operator_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=47&vd_source=eaeec77dfceb13d96cce76cc299fdd08
- 在sql_store中,输出从来没有被购买过的商品
USE sql_store;
SELECT * FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_items)
NOT IN就是不在,DISTINCT就是唯一值,不输入DISTINCT的话,就会很多重复的product_id输出
注意,一开始是FROM products,然后是FROM order_items,并不是两个都是FROM order_items,因为要筛选出没有被购买过的
【练习题】
在sql_invoicing中,输出没有发票的客户
USE sql_invoicing;
SELECT * FROM clients
WHERE client_id NOT IN (SELECT DISTINCT client_id FROM invoices)
4- 子查询 vs 连接 | Subqueries vs Joins_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=48&vd_source=eaeec77dfceb13d96cce76cc299fdd08【练习题】
用子查询和连接两种方法查找订购生菜(id=3)的顾客
子查询:
USE sql_store;
SELECT DISTINCT customer_id
FROM customers c
JOIN orders o USING (customer_id)
WHERE order_id IN (SELECT DISTINCT order_id FROM order_items
WHERE product_id = 3)
连接:
SELECT DISTINCT customer_id
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3
连接中因为已经将customers和orders连接了,所以可以直接在和order_items通过JOIN连接,不是说必须用orders作为FROM的对象(作为桥梁),将customers和order_items连接起来