第四章插入、更新和删除数据_新插入数据的删除版本号-优快云博客

文章详细介绍了如何在MySQL中进行数据操作，包括使用INSERT插入数据，两种方式指定列名或不指定，插入单行和多行。更新数据使用UPDATE语句，可以配合子查询进行复杂条件更新。删除数据使用DELETE命令，可以结合WHERE子句精确删除。还讨论了创建表备份的方法，以及在UPDATE语句中使用子查询的技巧。

第四章插入、更新和删除数据

p31 列属性√

VARCHAR(variable character)（50）：这列最多可以有50个字符，但如果只有5个字符，那么就存储5个字符，不会因没到50而浪费空间，这也解释了为什么是variable。

如果是CHAR（50），则就固定存储50个字符，不管我们存几个。

p32 插入单行√

INSERT INTO 目标表 （目标列，可选，逗号隔开）
VALUES (目标值，逗号隔开)

实例

在顾客表里插入一个新顾客的信息。

way1：若不指明列名，则插入的值必须按所有字段的顺序完整插入。

USE sql_store;
INSERT INTO customers -- 目标表
VALUES(
	DEFAULT,
    "luka",
    "2",
    "1997-10-09", -- DEFAULT/NULL/'1997-10-09'  （虽然不会报错，但是不要使用 / ，不是标准写法）
    "110",
    "byjz",
    "beijing",
    "China",
    DEFAULT
);

way2：指明列名，可跳过取默认值的列且可更改顺序，一般用这种，更清晰。

USE sql_store;
INSERT INTO customers(
    address,
    city,
    state,
    last_name,
    first_name,
    birth_date  -- 这里不要出现逗号了
)
VALUE(
    '5225 Figueroa Mountain Rd',
    'beijing',
    'CA',
    'luka',
    '077',    
    '1997-08-29' 
);

插入报错时，检查插入的数据是否符合每一列的数据类型。

字符串既可使用单引号也可以使用双引号。

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

p33 插入多行√

实例

插入多条产品信息。

USE sql_store;

INSERT INTO products 
VALUES (DEFAULT, 'product1', 1, 10),
       (DEFAULT, 'product2', 2, 20),
       (DEFAULT, 'product3', 3, 30);

p34 （插入分级行）往多表插入数据√！！

订单表（orders表）里的一条记录对应订单项目表（order_items表）里的多条记录，一对多，是相互关联的父子表。通过添加一条订单记录和对应的多条订单项目记录，学习如何向父子表插入分级（层）/耦合数据（insert hierarchical data）：

关键：在插入子表记录时，需要用内置函数**LAST_INSERT_ID()**获取相关父表记录的自增ID（这个例子中就是 order_id)；
LAST_INSERT_ID()获取的最新的成功的INSERT语句中的自增id(必须是自增的id!!!)，在这个例子中就是父表里新增的order_id。

实例

现在我们要往orders表中添加一条订单记录，同时呢还要向order_items添加一条订单详细记录（订单包括的产品、数量、单价等）。

但order_items中使用的复合主键，因此插入时不能简单的递增（因为同一个订单可能有多个产品，这些订单的order_id是相同的，因此不能使用默认值让其自动递增。）。**所以使用LAST_INSERT_ID()内置函数获得刚添加记录的主键，**再向order_items中添加一条数据。

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

-- 向order_items中插入13号订单中的两个产品，以及他们的数量以及单价
INSERT INTO order_items
VALUES(LAST_INSERT_ID(), 1, 2, 2.95); -- 获得的是上一次，也就是向orders表中插入数据时的自增！！id。
VALUES(LAST_INSERT_ID(), 2, 3, 3);

p35 创建表备份 √

运用 CREAT TABLE 新表名 AS 子查询 快速创建副本表。

实例1

假如我们想要把当前的订单记录存档，也就是把orders表中的每一行都复制到一个新表中。

USE sql_store;
CREATE TABLE orders_archived AS
SELECT * FROM orders; -- 子查询语句 是属于另外一段SQL语句中的选择语句

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

子查询：任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。

但这样操作之后我们发现新表中没有主键，也没有设置自动递增。所以用这个小技巧创建表时，MYSQL会忽略这些属性。

实例2

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

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

USE sql_store;

DROP TABLE orders_archived;  -- 也可右键该表点击 drop
CREATE TABLE orders_archived AS
    SELECT * FROM orders
    WHERE order_date < '2019-01-01';

方法2 TRUCATE 要清空的表名、INSERT INTO 表名子查询

use sql_store;
TRUNCATE TABLE orders_archived; -- 清空表

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

练习

创建一个存档发票表，只包含有过支付记录的发票并将顾客id换成顾客名字

构建的思路顺序：

先创建子查询，确定新表内容：

A. 合并发票表和顾客表

B. 筛选支付记录不为空的行/记录

C. 筛选（并重命名）需要的列
第1步得到的查询内容，可以先运行看一下，确保准确无误后，再作为子查询内容存入新创建的副本订单存档表 CREATE TABLE 新表名 AS 子查询

USE sql_invoicing;

DROP TABLE invoices_archived;  

CREATE TABLE invoices_archived AS
    SELECT i.invoice_id, c.name AS client, i.payment_date  
    -- 为了简化，就选这三列
    FROM invoices i
    JOIN clients c
        USING (client_id)
    WHERE i.payment_date IS NOT NULL
    -- 或者 i.payment_total > 0

p36 更新单行 √

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

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

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

p37 更新多行 √

正常来说不允许多行更新，但是可以通过Edit->SQL-Editor->取消Safe Updates

-- 更新id为3， 4的用户的多条信息
update invoices
set
	payment_total = invoice_total * 0.5,
    payment_date = due_date
where client_id IN (3, 4);

练习

让所有非90后顾客的积分增加50点

USE sql_store;

UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01'

p38 在update中使用子查询 √

非常有用，其实本质上是将子查询用在 WHERE…… 行筛选条件中。

实例

我们要更新客户3的所有发票，但我们没有客户3的id，我们只有客户3的姓名，怎么办？

-- 更新统一用户的多条信息
update invoices
set
	payment_total = invoice_total * 0.5,
    payment_date = due_date
where client_id = (SELECT client_id
					from clients
					where name = 'Myworks');

update invoices
set
	payment_total = invoice_total * 0.5,
    payment_date = due_date
where client_id in (SELECT client_id -- 返回多个id， 要用in
					from clients
					where state in ('CA', 'NY'));

练习

将 orders 表里那些分数>3k 的用户的订单 comments 改为 ‘gold customer’

思考步骤：

WHERE 行筛选出要求的顾客;
SELECT 列筛选他们的id;
将前两步作为子查询用在修改语句中的 WHERE 条件中，执行修改。

USE sql_store;

UPDATE orders
SET comments = 'gold customer'
WHERE customer_id IN
    (SELECT customer_id
    FROM customers
    WHERE points > 3000);

p39 删除行 √

语法结构：

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

案例

删除顾客id为3 / 顾客名字叫’Myworks’的发票记录。

USE sql_invoicing;
SET FOREIGN_KEY_CHECKS = 0; -- Mysql中如果表和表之间建立的外键约束，则无法删除表及修改表结构。所以要先取消外键约束
DELETE FROM invoices
WHERE 
client_id = 3
or 
client_id in 
    (SELECT client_id  
    FROM clients
    WHERE name = 'Myworks');