MySQL如何进行增删改操作

1.2.1. 插入数据（INSERT）

插入单行数据：

指明字段名，有默认值的字段可以跳过，且可更改顺序，一般用这种，更清晰（一个字段名对应一个值）

-- 插入数据
INSERT INTO customers (
    address,
    city,
    state,
    last_name,
    first_name,
    birth_date,
    )
VALUES (
    '5225 Figueroa Mountain Rd',
    'Los Olivos',
    'CA',
    'Jackson',
    'Michael',    
    '1958-08-29',  
    )

插入多行数据：

INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('product1', 1, 10),
       ('product2', 2, 20),
       ('product3', 3, 30)

注意：对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增——目前有10条数据，删除了最后两条数据再进行插入数据后，会在10的基础上递增。

插入分层行：

当插入的数据具有分层结构，就是一个数据项又包含了另一个数据项（一对多），可采用分层插入的方法。

下面案例通过添加一条订单记录和对应的多条订单项目记录，来记住如何向父表插入分级（层）/耦合数据（insert hierarchical data）：

        新增一个订单（order），包含两个订单项目（order_items），同时更新订单表和订单项目表

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

-- 可以先试一下用 SELECT last_insert_id() 看能否成功获取到的最新的 order_id

INSERT INTO order_items  -- 全是必须字段，就不用指定了
VALUES 
    (last_insert_id(), 1, 2, 2.5), -- 获取最新成功insert语句的主键id
    (last_insert_id(), 2, 5, 1.5)

执行流程：向orders表中插入了一条数据，然后在order_items表中插入该记录的其他信息，一对多的关系。

• LAST_INSERT_ID()：内建函数，获取最新的成功的 INSERT 语句 中的自增id，上面案例中获取的就是父表记录的自增ID（ orders的主键id)

创建表复制：

DROP TABLE 要删的表名、CREATE TABLE 新表名 AS 子查询、TRUCATE 要清空的表名、INSERT INTO 表名 子查询

创建副本表案例：

可用于创建表的副本：

CREATE TABLE orders_archived AS
    SELECT * FROM orders  -- 子查询

SELECT * FROM orders 选择了 oders 中所有数据，作为AS的内容，是一个子查询

• 子查询： 任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询。

如果不需要全部的数据，就选用原表中部分数据创建副本表，如，用今年以前的 orders 创建一个副本表 orders_archived，其实就是在子查询里增加了一个WHERE orders<今年时间的 语句进行筛选。注意要先 drop 删掉 或 truncate清空掉之前建的 orders_archived 表保证该表是不存在的且是空的，再重建或重新插入数据。

        DROP TABLE 要删的表名、CREATE TABLE 新表名 AS 子查询

DROP TABLE orders_archived;  -- 也可用可视化工具直接删除
CREATE TABLE orders_archived AS
    SELECT * FROM orders
    WHERE order_date < '2019-01-01'

INSERT INTO 表名 子查询 很常用，子查询替代原先插入语句中 VALUES(……,……),(……,……),…… 的部分

        INSERT INTO 表名 子查询

INSERT INTO orders_archived  
-- 不用指明字段名，会直接用子查询表里的字段名
    SELECT * FROM orders  
    -- 子查询，替代原先插入语句中VALUES(……,……),(……,……),…… 的部分
    WHERE order_date < '2019-01-01'

1.2.2. 更新数据（UPDATE）

使用UPDATE....来修改表中的一条或多条记录，具体语法⬇：

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

更新多行：

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

UPDATE invoices
SET payment_total = 233, payment_date = due_date
WHERE client_id = 3  
-- 该客户的发票记录不止一条，将同时更改
/WHERE client_id IN (3, 4) 
-- 第二章 4~9 讲的那些写 WHERE 条件的方法均可用
-- 甚至可以直接省略 WHERE 语句，会直接更改整个表的全部记录

在UPDATE中使用子查询：

本质上是将子查询用在 WHERE…… 筛选条件中，WHERE中使用子查询需要用括号括起来。IN …… 后除了可接 （……, ……） 也可接由子查询得到的多个数据（一列多条数据）。

        update中使用子查询

UPDATE invoices
SET payment_total = 567, payment_date = due_date
WHERE client_id = 
    (SELECT client_id 
    FROM clients
    WHERE name = 'Yadel');
    -- 放入括号，确保先执行

-- 若子查询返回多个数据（一列多条数据）时就不能用等号而要用 IN 了：
WHERE client_id IN 
    (SELECT client_id 
    FROM clients
    WHERE state IN ('CA', 'NY'))

注意：Update 前，最好先执行一下查询子句，验证一下子查询以及WHERE行筛选条件是不是准确的， 确保不会改错记录，再套入 UPDATE SET 语句更新。

1.2.3. 删除数据（DELETE）

语法结构：

DELETE FROM 表 
WHERE 行筛选条件
（当然也可用子查询）
（若省略 WHERE 条件语句会删除表中所有记录）

删除操作案例：

选出顾客id为3/顾客名字叫'Myworks'的发票记录

DELETE FROM invoices
WHERE client_id = 3
-- WHERE可选，省略就是会删除整个表的所有行/记录
/WHERE client_id = 
    (SELECT client_id  
    FROM clients
    WHERE name = 'Myworks')