MYSQL学习笔记（自用）第五章

第五章

第一节. Introduction| 编写复杂查询

##恢复数据库到处是状态
## FILE-->OPEN SQL Script-->open create-databases

第二节. 子查询| Subqueries

USE sql_store;

SELECT *
FROM products
WHERE unit_price > (
        SELECT unit_price
		FROM products
        WHERE product_id = 3
)

第二节. 作业练习

USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
      SELECT AVG(salary)
      FROM employees
)

第三节. IN运算符| The IN Operator

USE sql_store;

SELECT *
FROM products
WHERE product_id NOT IN (
        SELECT DISTINCT product_id
        FROM order_items
)

第三节. 作业练习

USE sql_invoiceing;
SELECT *
FROM clients
WHERE product_id NOT IN (
        SELECT DISTINCT client_id
        FROM invoices
)

第四节. 子查询 vs 连接| Subqueries vs Joins

SELECT *
FROM clients
WHERE product_id NOT IN (
        SELECT DISTINCT client_id
        FROM invoices
)

SELECT *
FROM  clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

第四节. 作业练习

USE sql_store;
SELECT DISTINCT customer_id, first_name, last_name
FROM customers c 
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3

第五节. ALL关键字| The ALL Keyword

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 MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

第六节. ANY关键字| The ANY Keyword

 SELECT *
 FROM invoices
 WHERE invoice_total > ANY (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

SELECT *
FROM clients
WHERE client_id IN (
	SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

第七节. 相关子查询| Correlated Subqueries

USE sql_hr;

SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)

第七节. 作业练习

USE sql_invoicing;

SELECT *
FROM invoices i
WHERE invoice_total > (
		SELECT AVG(invoice_total)
        FROM invoices
        WHERE client_id = i.client_id
 )

第八节. EXISTS运算符| The EXIXTS Operator

SELECT *
FROM clients
WHERE client_id IN (
		SELECT DISTINCT client_id
        FROM invoices
)

SELECT *
FROM clients c
WHERE EXISTS (
		SELECT client_id
        FROM invoices
        WHERE client_id = c.client_id
)

第八节. 作业练习

USE sql_store;

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

第九节. SELECT子句中的子查询| Subqueries in the SELECT Clause

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

第九节. 作业练习

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

第十节. FROM子句中的子查询| Subqueries in the FROM CLause

SELECT *
FROM (
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
)