Mosh

Basic

SQL-Cheat-Sheet.pdf

Join

内连接和外连接

外连接，保留Left或Right侧的所有记录。

  • 多的一方(orders): 会有NULL
  • 一的一方(customer): 会有重复

内连接：去除含NULL的记录，也会有重复
image.png

  1. -- Inner Joins
  2. SELECT order_id, o.customer_id, first_name, last_name FROM orders o 
  3.     JOIN customers c ON o.customer_id = c.customer_id ORDER BY order_id ;
  4. SELECT c.customer_id, first_name, last_name, order_id FROM customers c 
  5.     JOIN orders o ON c.customer_id = o.customer_id ORDER BY c.customer_id ;
  6. -- LEFT Outer Joins
  7. SELECT order_id, o.customer_id, first_name, last_name FROM orders o LEFT 
  8.     JOIN customers c ON o.customer_id = c.customer_id ORDER BY order_id ;
  9. SELECT c.customer_id, first_name, last_name, order_id FROM customers c LEFT 
  10.     JOIN orders o ON c.customer_id = o.customer_id ORDER BY c.customer_id ;
  11. -- RIGHT 0uter Joins，注意是c.customer_id
  12. SELECT order_id, c.customer_id, first_name, last_name FROM orders o RIGHT 
  13.     JOIN customers c ON o.customer_id = c.customer_id ORDER BY c.customer_id;
  14. SELECT c.customer_id, first_name, last_name, order_id FROM customers c RIGHT 
  15.     JOIN orders o ON c.customer_id = o.customer_id ORDER BY order_id ;
  17. - SELECT里customer_id加前缀o是因为分不清到底是order中的还是customer中的
  18. （或者两个表中有非关联的同名的键，例如product.unit_price和order_item.unit_price, 
  19. 也要明确是哪个表的）

image.png

多表连接

  1. -- JOIN Multiple Table
  3. SELECT
  4.     o.order_id,
  5.     c.first_name,
  6.     os.name as status
  7. FROM orders o
  8. JOIN customers c
  9.     ON o.customer_id  = c.customer_id 
  10. JOIN order_statuses os
  11.     ON o.status  = os.order_status_id;

复合主键 composite primary key

order_items
image.png
order_item_notes
image.png

  1. SELECT oi.order_id,oi.product_id,oin.note 
  2. FROM order_items oi 
  3. JOIN order_item_notes oin 
  4.     ON oi.order_id = oin.order_Id 
  5.     AND oi.product_id  = oin.product_id;
  7. -- 或者
  8. SELECT oi.order_id, oi.product_id, oin.note
  9. FROM order_item oi
  10. JOIN order_item_notes oin
  11.  USING (order_id, product_id);

Across database join

  1. USE sql_store;
  2. SELECT oi.order_id, oi.unit_price, p.name 
  3. from order_items oi 
  4. -- 只需要给不在当前USE的数据库的表加数据库的前缀
  5. JOIN sql_inventory.products p 
  6.   ON oi.product_id = p.product_id;

自连接 self join

  1. -- Self Joins
  2. SELECT
  3.    e.employee_id, e.first_name, 
  4.    m.first_name AS manager
  5. FROM employee e
  6.    JOIN employee m
  7.    ON e.reports_to = m.employee_id

using

  1. SELECT c.customer_id, first_name, last_name, order_id 
  2. FROM customers c 
  3. JOIN orders o 
  4. USING (customer_id);

Outer Join

尽量只使用左连接

  1. -- Multiple outer join 
  2. SELECT 
  3.     o.order_id ,
  4.     o.order_date ,
  5.     c.first_name as customer,
  6.     sh.name as shipper,
  7.     os.name as status
  8. FROM orders o 
  9. join customers c 
  10.     on o.customer_id = c.customer_id 
  11. left join shippers sh 
  12.     on o.shipper_id = sh.shipper_id 
  13. join order_statuses os 
  14.     on o.status = os.order_status_id ;

Unions和“自定义字段”

  1. -- UNIONALL不去除重复
  2. SELECT customer_id, first_name, points, 'Bronze' AS type
  3.   FROM customers c 
  4.   WHERE points < 2000
  5. UNION
  6. SELECT customer_id, first_name, points, 'Silver' AS type
  7.   FROM customers c 
  8.   WHERE points BETWEEN 2000 AND 3000
  9. UNION
  10. SELECT customer_id, first_name, points, 'GOLD' AS type
  11.   FROM customers c 
  12.   WHERE points > 3000
  13. ORDER BY type;

CUD

INSERT 插入数据

插入分层数据 hierarchical data

  1. INSERT INTO orders (customer_id, order_date, status)
  2. VALUES (1, '2021-04-25', 1);
  4. INSERT INTO order_items 
  5. VALUES (LAST_INSERT_ID(), 1, 1, 2.95),
  6.                                  (LAST_INSERT_ID(), 2, 1, 3.95);

UPDATE 更新数据

更新多行

设置允许更新多行
image.png

  1. UPDATE invoices 
  2. SET 
  3.     payment_total = invoice_total * 0.1,
  4.     payment_date = due_date 
  5. WHERE client_id IN (3,4);

结合子查询（subQuery）和join，子查询一般是和in结合使用，但是不推荐使用

  1. UPDATE invoices 
  2. SET 
  3.     payment_total = invoice_total * 0.3,
  4.     payment_date = due_date 
  5. WHERE client_id IN
  6.                 (SELECT client_id
  7.                 FROM clients
  8.                 WHERE state IN ('CA','TX'));

DELETE删除数据

结合子查询（subQuery）

  1. DELETE FROM invoices
  2. WHERE invoice_id = (
  3.     SELECT client_id FROM clients WHERE name = 'Myworks'
  4. )

复杂查询

WHERE中的子查询

子查询 VS 连接

  1. 性能 2.可读性。优先性能，然后可读性。具体要看执行计划。

子查询可能用于NOT IN（不包含）多一些

  1. -- 子查询
  2. SELECT *
  3. FROM products p 
  4. WHERE product_id NOT IN (
  5.     SELECT DISTINCT product_id 
  6.     FROM order_items oi 
  7. );
  9. -- 连接
  10. SELECT 
  11. p.product_id ,
  12. p.name ,
  13. oi.order_id 
  14. FROM products p
  15. LEFT jOIN order_items oi
  16.     using (product_id )
  17. where oi.order_id IS NULL;

相关子查询

  • 非相关子查询，子查询只执行一次，

  • 相关子查询，主查询的每一行子查询都要执行一次

    1. -- 外面的office_id和里面是同一个
    2. use sql_hr;
    3. SELECT e.employee_id , e.office_id , e.first_name , e.salary 
    4. from employees e 
    5. where e.salary > (
    6.   SELECT AVG(salary)
    7.   from employees 
    8.   where office_id = e.office_id 
    9. );

    IN VS EXISTS

    重要！如果IN运算符后写的子查询生成了很大的结果集，会妨碍性能，使用EXISTS能提高性能。

  • IN: 子查询给外查询返回一个结果集

  • EXISTS: 子查询返回一个指令，说明这个子查询中是否有符合这个搜索条件的行
    1. SELECT * from products p 
    2. WHERE NOT EXISTS (
    3.   select product_id 
    4.   from order_items oi 
    5.   where product_id = p.product_id 
    6. );

    ALL关键字

    all VS max() ```sql SELECT from order_items oi where quantity > ALL ( SELECT quantity from order_items where product_id = 4 ); SELECT from order_items oi where quantity > ( SELECT MAX(quantity) from order_items where product_id = 4 );
  1. <a name="cicfk"></a>
  2. ### SELECT子句中的子查询
  3. ```sql
  4. SELECT invoice_id ,
  5.     invoice_total,
  6.     (SELECT AVG(invoice_total) FROM invoices) as invoice_average,
  7.     invoice_total - (select invoice_average) as difference
  8. from invoices;

FROM中的子查询

相当于临时表/视图，From中的子查询仅限于简单查询

  1. SELECT * FROM (
  2.     SELECT invoice_id ,
  3.         invoice_total,
  4.         (SELECT AVG(invoice_total) FROM invoices) as invoice_average,
  5.         invoice_total - (select invoice_average) as difference
  6.     from invoices i
  7. ) as invoice_summary
  8. WHERE invoice_total IS NOT NULL;

子查询的限制

避免使用子查询，可以把子查询优化为 join 操作。

  • 通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。
  • 删除中的子查询是同一张表，会产生死锁。
    1. -- 删除全部重复的，错误，原因是：更新这个表的同时又查询了这个表，
    2. -- 查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作
    3. DELETE FROM student_contacts
    4. WHERE name IN (
    5.   SELECT name FROM student_contacts GROUP BY name HAVING COUNT(1) > 1
    6. );

    列属性

    VARCHAR和CHAR的区别
    image.png

    函数

    1. SELECT LAST_INSERT_ID();
    ```sql SELECT CONVERT(“2018-11-30”, DATETIME); SELECT CAST(“2018-11-30” AS DATE);

SELECT COALESCE(id, user_id, ‘none’) FROM user; SELECT IFNULL(id, ‘none’) FROM user;

  1. <a name="Dn1vF"></a>
  2. ## AVG平均值
  3. ```sql
  4. SELECT AVG(salary) from employees;

IF条件

  1. SELECT 
  2.     product_id,
  3.     name,
  4.     COUNT(*) as orders,
  5.     IF(COUNT(*) > 1, 'Many times', 'once') as frequence
  6. FROM products
  7. INNER JOIN order_items oi 
  8.     USING(product_id)
  9. GROUP BY product_id, name;

IFNULL

  1. DELIMITER $$ 
  2. CREATE PROCEDURE get_payments
  3. (
  4.     client_id INT(4),
  5.     payment_method_id TINYINT(2)
  6. )
  7. BEGIN 
  8.     SELECT * FROM payments p
  9.         where p.client_id = IFNULL(client_id, p.client_id)
  10.             and p.payment_method = IFNULL(payment_method_id, p.payment_method);
  11. END$$

Case条件

  1. SELECT 
  2.     order_id,
  3.     CASE
  4.         WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active'
  5.         WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'Last YEAR'
  6.         WHEN YEAR(order_date) < YEAR(NOW()) THEN 'Archived'
  7.         ELSE 'Future'
  8.     END AS category
  9. FROM orders;

操作

truncate table

不推荐的

  • implicit join
  • natural joins

点击查看【bilibili】