mosh-10小时SQL

-- 3小时结束，另外7小时

SELECT
　　MAX(invoice_total) highest,
　　MIN(invoice_total) lowest,
　　AVG(invoice_total) average,
　　SUM(invoice_total) total, -- 可以写表达式 SUM(invoice_total) * 1.1)
　　COUNT(invoice_total) number_of_invoices,
　　COUNT(DISTINCT client_id),-- 记录唯一值
　　COUNT(payment_date) number_of_payment, -- 忽略空值
　　COUNT(*) total_records -- 包含空值
FROM invoices
WHERE invoice_date > '2019-07-01';

-- EXERCISE
SELECT
　　'First half of 2019' date_range,
　　SUM(invoice_total) total_sales,
　　SUM(payment_total) total_payment,
　　SUM(invoice_total - payment_total) what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT
　　'Second half of 2019' date_range,
　　SUM(invoice_total) total_sales,
　　SUM(payment_total) total_payment,
　　SUM(invoice_total - payment_total) what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT
　　'Total' date_range,
　　SUM(invoice_total) total_sales,
　　SUM(payment_total) total_payment,
　　SUM(invoice_total - payment_total) what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31';
-- 聚合函数汇总数据

SELECT
　　p.date,
　　pm.name payment_method,
　　SUM(p.amount) total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY date,payment_method
ORDER BY date;
--  GROUP BY数据分组,后需接SELECT后除聚合函数外的所有字段，也可接SELECT后没有的字段；

SELECT
　　c.customer_id,
　　c.first_name,
　　c.last_name,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE c.state = 'VA'
GROUP BY
　　c.customer_id,
　　c.first_name,
　　c.last_name
HAVING total_sales > 100;
-- HAVING 过滤分组结果，放置在GROUP BY后,后接字段须是SELECT或GROUP BY出现过的字段或聚合函数

SELECT
　　pm.name payment_method,
　　SUM(amount) total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP;
-- WITH ROLLUP 汇总聚合函数,用在GROUP BY之后，如果别名是表中已经出现过的，WITH ROLLUP前使用实际字段名，不能使用别名；如果别名是表中没有的字段，可以使用别名。

SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
-- 子查询

SELECT *
FROM clients
WHERE client_id NOT IN (
　　SELECT DISTINCT client_id
　　FROM invoices
);
-- IN运算符；子查询
-- 另一种写法 连接
SELECT *
FROM clients c
LEFT JOIN invoices i USING(client_id)
WHERE invoice_id IS NULL;

-- EXERCISE
-- 写法一：子查询
SELECT
　　customer_id,
　　first_name,
　　last_name
FROM customers
WHERE customer_id IN (
　　SELECT customer_id
　　FROM orders
　　WHERE order_id IN (
　　　　　　SELECT order_id
　　　　　　FROM order_items
　　　　　　WHERE product_id = 3
));

-- 写法二：连接
SELECT
DISTINCT
　　c.customer_id,
　　c.first_name,
　　c.last_name
FROM customers c
JOIN orders o USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE oi.product_id =3;

-- 写法三：连接/子查询混用
SELECT
　　customer_id,
　　first_name,
　　last_name
FROM customers
WHERE customer_id IN (
　　SELECT customer_id
　　FROM order_items oi
　　JOIN orders o USING(order_id)
　　WHERE product_id =3
);

 

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
);
-- ALL 关键字的运用，也可用MAX函数；

SELECT *
FROM clients
WHERE client_id = ANY (
　　SELECT client_id
　　FROM invoices
　　GROUP BY client_id
　　HAVING COUNT(*) >= 2
);
-- ANY关键字的运用，也可将‘= ANY’替换成IN运算符；

SELECT *
FROM employees e
JOIN (
　　SELECT office_id,AVG(salary) avg_salary
　　FROM employees
　　GROUP BY office_id
) a USING(office_id)
HAVING salary > avg_salary;

SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
);
-- 相关子查询
-- EXERCISE
SELECT *
FROM invoices i
WHERE invoice_total > (
　　SELECT AVG(invoice_total)
　　FROM invoices
　　WHERE client_id = i.client_id
);

-- Select clients that have an invoice (IN/JOIN/EXISTS三种方式)
SELECT *
FROM clients
WHERE client_id IN (
　　SELECT client_id
　　FROM invoices
);

SELECT DISTINCT c.*
FROM clients c
JOIN invoices i using(client_id);

SELECT *
FROM clients c
WHERE EXISTS (
　　SELECT client_id
　　FROM invoices
　　WHERE client_id = c.client_id
);
-- EXERCISE
SELECT *
FROM products
WHERE product_id NOT IN (
　　SELECT product_id
　　FROM order_items
);

SELECT p.*
FROM products p
LEFT JOIN order_items oi USING (product_id)
WHERE oi.order_id IS NULL;

SELECT *
FROM products p
WHERE NOT EXISTS (
　　SELECT product_id
　　FROM order_items
　　WHERE product_id = p.product_id

);
-- EXISTS运算符的应用

SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
invoice_total - (SELECT invoice_average) AS difference
FROM invoices;

-- EXERCISE
SELECT
client_id,
name,
(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c;
-- SELECT子句中的子查询