Mosh-mysql基础-增删查改

要了解后端，从Mysql开始学起，仅作备份。
参考mosh三小时入门
参考https://zhuanlan.zhihu.com/p/222865842?native.theme=1

MYSQL

mysql workbench中运行快捷键用：Ctrl+Enter

面板里面的话用 "\G"结尾

SELECT * FROM customers \G

类似pycharm，可以对指定的语句点击闪电符号运行

1、关系型数据库和数据模型

2、SQL语法与分类

Mosh基础——增删查改

1、SELRT FROM

 SELECT 
     name,
     unit_price,
     unit_price*1.1 AS 'new price'
 FROM products

2、WHERE用于过滤

使用"WHERE IN"条件来简化多个OR条件的使用，"WHERE IN"用于在查询中指定一个字段与一个值集合进行比较，并返回匹配任何集合中值的行。它可以与子查询结合使用，也可以直接和值列表一起使用。

SELECT *
FROM table_name
WHERE column_name IN (value1, value2, value3);

在上述示例中，“WHERE IN"条件表达式用于在名为"table_name"的表中筛选出"column_name"字段的值与"value1”、"value2"和"value3"中的任何一个相等的数据行。这将返回所有匹配的行。

3、REGEXP是正则表达式匹配运算符，

用于执行模式匹配。它用于在字段中查找满足特定模式的值。

通过使用REGEXP运算符，可以将正则表达式模式与字符串进行比较，从而判断字符串是否满足所需的模式。常见的正则表达式模式包括字符类、重复、分组等。

下面是一个示例：

SELECT * 
FROM table_name
WHERE column_name REGEXP 'pattern';
WHERE last_name REGEXP 'ey$|on$'  --寻找以ey or on结尾的字段 
WHERE last_name REGEXP '^my|se'   --寻找以my开头 or 包含se的字段 
WHERE last_name REGEXP 'br|bu'   --
WHERE last_name REGEXP 'b[ru]'   --寻找以B开头，跟随r/u的字段 
ORDER BY first_name DESC  --名字降序排

这将返回在指定的表（table_name）中，满足指定模式（pattern）的行。

需要注意的是，REGEXP运算符是区分大小写的。如果要进行不区分大小写的模式匹配，可以使用REGEXP的变体函数REGEXP_LIKE()。

4、寻找计算并展示

SELECT *,
    quantity*unit_price AS 'amount'
FROM order_items
WHERE order_id = 2
ORDER BY amount DESC

5、限制返回数量

挑选points最高的前三位

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

6、JOIN

order表和customers表两个有共同列customer_id，现在将customer加入到order里面

SELECT *
-- SELECT 
FROM orders 
JOIN customers 
     ON orders.customer_id = customers.customer_id

PS:在不同表里面找相同列的时候，需要在sql语句中加上对应的表名，用以指定

SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p
     ON oi.product_id = p.product_id

7、其它数据库中的表插入

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
     ON oi.product_id= p.product_id


OR 

USE sql_inventory;

SELECT *
FROM sql_store.order_items oi
JOIN products p
     ON oi.product_id = p.product_id

8、自己的表和自己JOIN

USE sql_hr;

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m
     ON e.reports_to = m.employee_id

9、两个表JOIN

USE sql_store;

SELECT
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
     ON o.customer_id = c.customer_id
JOIN order_statuses os
     ON o.status = os.order_status_id

OR

USE  sql_invoicing;

SELECT 
      -- p.payment_method,
	  p.date,
      p.invoice_id,
      p.amount,
      c.name,
      pm.name AS paymethods
FROM payments p
JOIN clients c
      ON p.client_id = c.client_id
JOIN payment_methods pm
      ON p.payment_method = pm.payment_method_id

10、主键和复合表结构

USE sql_store;

SELECT*

FROM order_items oi
JOIN order_item_notes oin
     ON oi.order_id = oin.order_Id
     AND oi.product_id = oin.product_id

11、mysql内部连接———隐式连接

USE sql_store;

SELECT
	c.customer_id,
    c.first_name,
    o.order_id

FROM orders o
-- LEFT JOIN orders o
RIGHT JOIN customers c
     ON c.customer_id = o.customer_id
ORDER BY c.customer_id

SAME AS
USE sql_store;

SELECT
	c.customer_id,
    c.first_name,
    o.order_id

FROM customers c
-- LEFT JOIN orders o
RIGHT JOIN orders o
     ON c.customer_id = o.customer_id
ORDER BY c.customer_id

USE sql_store;

SELECT
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
 LEFT JOIN orders o
-- RIGHT JOIN customers c
     ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	 ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

PS:左连接和右连接，并不是简单意义上的在SELECT和JOIN上面换个位置，就可以得到同样的效果。如上SQL，左连接和右链接得到的表结果根本不一样

12、外部连接

USE sql_hr;

SELECT 
     o.order_id,
     c.first_name,
     sh.name AS shipper
FROM orders o
JOIN customers c
     USING(customer_id)
LEFT JOIN shippers sh
     USING(shipper_id)

13、USING关键字

14、多表连接和USING/ON使用

USE sql_invoicing;

SELECT
     p.date,
     c.name AS client,
     p.amount,
     pm.name AS payment_method
FROM payments p
JOIN clients c USING(client_id)
JOIN payment_methods pm
     ON p.payment_method = pm.payment_method_id

15、Cross JOIN笛卡尔积组合

通过CROSS JOIN将shippers表和products表进行笛卡尔积组合，返回所有可能的组合结果。最后，使用ORDER BY对SHIPPER进行升序排序。

CROSS JOIN会生成两个表的每个行之间的组合，可能导致结果集非常庞大。请谨慎使用CROSS JOIN。

等同于

16、UNION

UNION操作用于将两个或多个SELECT语句的结果合并成一个结果集。其主要意义如下：

1. 合并查询结果：通过UNION可以将多个查询结果合并为一个结果集，方便对数据进行综合处理和分析。
2. 删除重复记录：当多个查询结果可能存在重复记录时，使用UNION可以自动删除重复的记录，确保结果集中的每条记录都是唯一的。
3. 类似合并表：通过UNION操作可以模拟将不同表的数据合并到一个表中的效果，方便进行数据比较、交集、并集等操作。
4. 简化查询逻辑：使用UNION可以将复杂的查询逻辑拆分为多个简单的查询，并通过合并它们的结果来获取最终的数据。

需要注意的是，使用UNION操作时，要求参与合并的查询结果列数和数据类型必须相同或兼容。同时，UNION默认会去除重复记录，如果需要保留重复记录，可以使用UNION ALL操作。

SELECT 
     order_id,
     order_date,
     'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
     order_id,
     order_date,
     'Actived' AS status
FROM orders
WHERE order_date <= '2019-01-01'

SELECT 
     customer_id,
     first_name,
     points,
     'Bronze' AS type
FROM customers
WHERE points < '2000'
UNION
SELECT 
     customer_id,
     first_name,
     points,
     'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000

UNION
SELECT 
     customer_id,
     first_name,
     points,
     'Gold' AS type
FROM customers
WHERE '3000' <= points  
ORDER BY first_name

17、INSERT INTO

INSERT INTO是MySQL中用于向表中插入新数据的关键字。它的意义如下：

1. 插入数据：使用INSERT INTO可以将新的行插入到指定的表中。
2. 指定列名：可以通过指定列名的方式，只向表中插入指定列的值，而不是全部列。这在某些情况下可以提高插入效率，也可以避免出现列顺序不一致的问题。
3. 指定值：通过VALUES关键字，可以指定要插入的具体值。可以是常量值、变量值、表达式等。
4. 插入多行数据：可以一次性插入多行数据，每行数据通过逗号分隔。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

其中，table_name是要插入数据的表名。column1, column2等是要插入的列名，可以省略。value1, value2等是要插入的值。

**注意：**插入的值的顺序必须和列的顺序相匹配，或者通过列名指定对应的值。同时，插入的值的数据类型必须与目标列的数据类型相匹配，否则可能会导致错误。

一表插入多行

INSERT INTO products (name,quantity_in_stock,unit_price)
VALUES ('DF111',10,1.98),
       ('WFE222',11,1.98),
       ('EFE333',12,1.98)

数据插入多表

第一个语句用于向名为"orders"的表中插入一条新记录。该记录包含三个列：customer_id、order_date和status。值分别为1、'2019-01-02’和1。通过使用last_insert_id()函数来获取刚刚插入的订单记录的自增ID值，以便在后续插入订单项时使用。

第二个语句用于向名为"order_items"的表中插入两条新记录。每条记录包含四个列：order_id（使用last_insert_id()函数获取）、product_id、quantity和price。这两条记录分别表示与刚刚创建的订单关联的两个订单项。

这两个语句的执行顺序很重要，在向"order_items"表中插入订单项之前，必须先插入订单并获取其ID值。然后将此ID值用于在"order_items"表中与订单项关联。

INSERT INTO orders (customer_id,order_date,status)
VALUES (1, '2019-01-02',1);

INSERT INTO order_items
VALUES
     (last_insert_id(),1,1,2.1),
     (last_insert_id(), 2,1,3.1)

18、COPY

INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

19、CREAT+JOIN+WHERE+USING

CREATE TABLE invoice_archived1 AS
SELECT 
     i.invoice_id,
     i.number,
     c.name AS client,
     i.invoice_total,
     i.payment_total,
     i.invoice_date,
     i.payment_date,
     i.due_date
FROM invoices i
JOIN clients c
	USING (client_id)
WHERE payment_date IS NOT NULL

20、UPDATE表内容

UPDATE invoices
SET payment_total = 10, payment_date='2019-03-01'
WHERE invoice_id = 1

--表内数据互用
UPDATE invoices
SET 
     payment_total = 10 * invoice_total, 
     payment_date=due_date
WHERE invoice_id = 1

UPDATE invoices
SET 
     payment_total = 10 * invoice_total, 
     payment_date=due_date
WHERE client_id =   -- 子查询，基于这个条件完成上述操作
		(SELECT client_id
		FROM clients
		WHERE name = 'Yadel')
                

21、联合子查询

UPDATE invoices
SET 
     payment_total = 10 * invoice_total, 
     payment_date=due_date
WHERE client_id IN 
		(SELECT client_id
		FROM clients
		-- WHERE name = 'Yadel')
        WHERE state IN('CN','NY'))

22、DELETE

DELETE FROM invoices
WHERE client_id = (
		SELECT client_id
		FROM clients
		WHERE name = 'Myworks'
)