MySQL基础：插入、更新和删除数据

Inserting, Updating and Deleting Data

1. 列属性

• 点击表的扳手按钮：打开设计模式，包含一些表中字段/列的属性。
• 数据类型：INT,CHAR(50)表示占用50个字符的字符串,VARCHAR(5)表示可以占用5个字符以内的字符串
• PK 主键，如果主键没有勾选AI，或者设置默认值不能用default
• NN 非空值
• AI 表示自动递增，通常用于主键
• Default/Expression 默认值

2. 插入单行

INSERT INTO 目标表 （目标列，逗号隔开，也可以省略这一部分不指定列）
VALUES (目标值，逗号隔开)
可采用两种方法来插入列：

1. 不指定列名（可以将括号都省略），但插入的值必须按所有字段的顺序完整插入；

INSERT INTO customers
VALUES(default, 
       'Michael', 
       'Smith', 
       '1991-01-01', 
       default, 
       '5225 Figueroa Mountain Rd',
       'Los Olivos',
       'CA',
       default);     -- 每运行1次会添加一条记录

2. 指定列名，可跳过取默认值的列且可更改顺序。上下对应即可

INSERT INTO customers(
    first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES( 
       'Michael', 
       'Jackson', 
       '1991-01-01', 
       '5225 Figueroa Mountain Rd',
       'Los Olivos',
       'CA');

Note: 多条SQL语句必须以分号（；）分隔。MySQL如同多数DBMS一样，不需要在单条SQL语句后加分号。但特定的DBMS可能必须在单条SQL语句后加上分号。当然，如果愿意可以总是加上分号。事实上，即使不一定需要，但加上分号肯定没有坏处。如果你使用的是mysql命令行，必须加上分号来结束SQL语句。

3. 插入多行

VALUES …… 里一行内数据用括号内逗号隔开，而多行数据用括号间逗号隔开

INSERT INTO shippers(name)
VALUES('shipper1'),
      ('shipper2'),
      ('shipper3') 

Note：对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增

4. 插入分层行

关系型数据库中表之间存在父子关系，一个主表可能对应多个子表，子表用来补充和扩充信息。通过添加一条订单记录和对应的多条订单项目记录，学习如何向父子表插入分级（层）/耦合数据（insert hierarchical data）。

• 内建函数：MySQL里有很多可用的内置函数，也就是可复用的代码块，各有不同的功能，注意函数名的单词之间用下划线连接。
• LAST_INSERT_ID() 函数：获取最新的成功的 INSERT语句 中的自增id，在这个例子中就是父表里新增的order_id.
• 关键：在插入子表记录时，用内建函数 LAST_INSERT_ID() 来获取相关父表记录的自增ID（这个例子中就是orders表中的order_id)

USE sql_store;
INSERT INTO orders(customer_id, order_date, status)
VALUES(1, '2023-08-25', 1);
-- SELECT last_insert_id() 查看是否成功获取到最新的order_id
INSERT INTO order_items  -- 全是必须字段,无NULL值，因此不指定列名
VALUES
    (last_insert_id(), 1, 2, 2.5),
    (last_insert_id(), 2, 5, 1.5)

5. 创建表复制

• 子查询： 任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。（子查询里可以使用WHERE语句进行筛选）
• 创建已有的表或删除不存在的表的话会报错，所以建表和删表都最好加上条件语句

方法：

1. 删除重建：DROP TABLE 要删的表名、CREATE TABLE 新表名 AS 子查询；
2. 清空重填：TRUNCATE 要清空的表名、INSERT INTO 表名 子查询。

-- 创建一个orders表的副本orders_archived，该副本没有主键
CREATE TABLE orders_archived AS
SELECT * 
FROM orders  -- 子查询

-- 删除表格后重建表格，然后用子查询填充已有表格中的数据
DROP TABLE orders_archived;
CREATE TABLE orders_archived AS
SELECT * 
FROM orders
    WHERE order_date > '2019-01-01'
    
-- 删除已有表中的内容，然后再用子查询已有表中的数据进行填充
TRUNCATE orders_archived;
INSERT INTO orders_archived 
    SELECT * 
    FROM orders    -- 子查询语句
    WHERE order_date < '2019-01-01'

操作：

1. MySQL中语句为代码块的形式，代码块之间需要采用分号隔开；
2. 查询、筛选和连接都是用来创建新的数据表格，利用创建表语句可以生成新的表格。（编程思路：先筛选出表格的数据，然后再对表格整体操作）

USE sql_invoicing;
DROP TABLE invoices_archived;
CREATE TABLE invoices_archived AS
SELECT 
    i.invoice_id,
    i.number,
    c.name AS clients,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
FROM invoices i
LEFT JOIN clients c
    ON i.client_id = c.client_id
    -- USING (client_id)
    WHERE i.payment_date IS NOT NULL;
SELECT *
FROM invoices_archived

6. 更新单行

用 UPDATE …… 语句 来修改表中的一条或多条记录，具体语法结构：

UPDATE 表
【SET 要修改的字段 = 具体值/NULL/DEFAULT/列间数学表达式】 （【修改多个字段用逗号分隔】）
WHERE 行筛选

NOTE: 实际执行顺序应该是 UPDATE → WHERE → SET

UPDATE invoices
SET 
    payment_total = invoice_total *0.5, 
    payment_date = due_date
WHERE invoice_id = 1;

7. 更新多行

与更新单行语法一样的，就是让 WHERE…… 的条件包含更多记录，就会同时更改多条记录

NOTE: Workbench默认开启了Safe Updates功能，不允许同时更改多条记录，要先关闭该功能（在 Preference——SQL Editor里）

UPDATE invoices
SET 
	payment_total = invoice_total *0.5, 
	payment_date = due_date
WHERE client_id IN (3, 4);
-- 在WHERE中选择能够影响多行的属性

8. 在Updates中用子查询

• 在MySQL中只选择某段程序可以进行局部运行查看结果，用来程序运行前的验证。
• 将子查询用在WHERE子句中。IN关键字后除了可以接（…, …）还可以接子查询用括号括起来。
• WHERE子句中IN关键字用在多个数据，=后面接一个数据。
• Update 前，最好先验证看一看子查询以及WHERE行筛选条件是不是准确的，筛选出的是不是我们的修改目标，确保不会改错记录，再套入UPDATE SET语句更新。

UPDATE invoices
SET payment_total = invoice_total, payment_date = due_date
WHERE client_id IN 
                    (SELECT client_id
                     FROM clients
                     WHERE name = 'Yadel');

9. 删除行

DELETE FROM 表
WHERE 行筛选条件（当然也可用子查询）

NOTE:若省略WHERE条件语句会删除表中所有记录（和TRUNCATE相同）)

DELETE FROM invoices
WHERE client_id =  -- where可以省略，将删除所有行
                (SELECT client_id
                FROM clients
                WHERE name = 'Myworks');

10. 恢复数据库

重新运行SQL文件从而重新建立数据库。
方法：File->’Open SQL Script’打开脚本程序来重新建立以前的数据库