SQL语言笔记 第三章 表中插入\更新\删除数据

使用的是MySQL数据库

• 如果在插入记录前，删除了原有最后记录行，如最后一行的id为12，新插入的数据id会从13开始。

1. 插入单行 INSERT INTO

• customers表结构
  

INSERT INTO customers (
    -- 列和对应数据可以不按照表结构中列的顺序写，一样可以插入数据
    first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
-- 第一种写法：上面所有的列名都写了，下面每一列数据也写全
-- 第二种写法：上面只写有确切值的列名称，下面的值就可以不用写DEFAULT和NULL值，没有写的列MySQL会自动生成默认值
-- 第一列customer_id设置了自增，不要写确切值，可以写DEFAULT或者不写，MySQL会自动生成这个值
    -- DEFAULT,
-- 第二列first_name不为空
    'John',
-- 第三列last_name不为空
    'Smith',
-- 第四列birth_date默认为空 可以写确切的日期，可以赋NULL关键字省略这个值
    '1990-01-01',
-- 第五列phone默认为空 没有确切号码，可以赋NULL或者DEFAULT关键字，让MySQL给这列空值
    -- NULL,
-- 第六列address不为空
    'address',
-- 第七列city不为空
    'city',
-- 第八列state不为空
    'VA')
-- 最后一列points不为空，默认值为0，可以写确切值，也可以用DEFAULT关键字让MySQL生成0
    -- DEFAULT)

• 结果显示：插入了一条customer_id为11的记录，customer_id自增。
  

2. 插入多行

• shippers表结构
  

-- 插入多行数据，只要在每个数据之间加逗号
INSERT INTO shippers (name)
VALUES ('shipper1'),
       ('shipper2'),
       ('shipper3')

• 结果显示：插入了shipper_id为6、7、8的三条记录，shipper_id自增
  

3. 插入分层行

• 往多张表中插入数据，或者是往两个互为亲子关系的表中插入数据，使用到数据库引擎自带的内容功能 LAST_INSERT_ID() 最近插入的id

-- LAST_INSERT_ID()是数据库引擎自带的内置功能，表示获取最近插入的id 可以用SELECT LAST_INSERT_ID()查询

-- 因为orders表和order_items表存在'亲子关系'，orders表是母表，order_items表是子表；
-- 就是在orders表中生成一个订单时，订单中存在一个或多个商品，因此会同时在order_items表中生成一个或多个订单项目。
-- 可以理解为先插入订单数据，再插入订单项目数据。

-- 在orders表中插入单行记录顾客id,下单时间,订单状态(订单状态有单独的表)
INSERT INTO orders (customer_id,order_date,status)
VALUES (1,'2021-11-26',1);
-- 在执行语句时，会先执行上面的语句，在orders表最后一行插入记录，order_id自增
-- 我们需要这个自增的order_id，以便我们生成订单项目记录，因此使用LAST_INSERT_ID()找到最近插入的id，也就是上面的order_id
-- 下面写order_items表中插入多行数据，order_items表中没有自增的id，四列都不为空，因此可以省略列名称
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,2.95),
       (LAST_INSERT_ID(),2,1,3.95)
-- 此时就成功向order_items表中插入了两行订单项目数据

4. 创建表复制 快速将数据从一张表复制到另一张新表

• 创建表复制的缺点，MySQL会忽略id的主键和自增属性，手动到设计模式中勾上。
• 创建表复制的书写步骤，先写子查询确认查询的内容正确，再写CREATE TABLE

-- 用选择语句作为创建表里的子查询
-- 将orders订单表复制给另一张新表orders_archived订单存档表
-- 创建表orders_archived
CREATE TABLE orders_archived AS
-- 下面的选择语句称为子查询
SELECT * FROM orders;
-- 使用这个技巧创建的表的缺点，**MySQL会忽略id的主键和自增属性

-- 将orders_archived表中的数据截断（TRUNCATE table） 此时得到一个空表

-- 用选择语句作为插入语句里的子查询 可以将满足条件的数据插入到orders_archived表中
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date > '2019-01-01';

5. 更新单行 UPDATE

-- 发票1中付款金额和付款日期都没填，更新发票1后发现更新错了发票
-- 由于发票设计模式中 payment_total默认值为0，payment_date默认为空，可以写成：
UPDATE invoices
SET payment_total = DEFAULT,
    payment_date = NULL
WHERE invoice_id = 1;

-- 更新发表3中付款金额为原先的50%，付款日期为截止日期
UPDATE invoices
SET payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id = 3;

6. 更新多行

• MySQL Workbentch软件执行更新多行记录会报错，其他的应用程序不会；可以通过以下方式解决，记得保存后重新连接MySQL instance才奏效。这原本是防止意外更新或删除了一些记录。
  

-- 所有client_id为3的发票都更新了
UPDATE invoices
SET payment_total = invoice_total * 0.5,
    payment_date = due_date
-- WHERE子句是可选的，不写WHERE子句表示更新所有发票
WHERE client_id = 3;

7. 在UPDATE语句中使用子查询

使用子查询或者更新的条件不是确切值时，一定要先执行选择语句，检查是否是要更新的记录，只有确定是要更新的记录，再执行更新语句！！！

-- 用选择语句作为UPDATE语句中的子查询
-- 如果客户输入用户名，我们需要根据用户名查询客户id，根据客户id更新发票信息
UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = 
          (SELECT client_id
           FROM clients
		WHERE name = 'Myworks');
     
-- 子查询使用IN运算符，得到的client_id不止一个，因此更细语句中的条件可以使用IN运算符
UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN
          (SELECT client_id
           FROM clients
		WHERE state IN ('NY','CA'));

-- 不清楚哪些发票的付款日期为空时，先查询是否是要更新的发票，确认好，再删除选择语句
UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE payment_date IS NULL;

8. 删除行 DELETE

-- 如果不写WHERE子句，这个语句会删除表里的全部内容，比较危险
DELETE FROM invoices;
-- 删除发票id为1的记录
DELETE FROM invoices
WHERE invoice_id = 1;

-- DELETE语句中可以使用子查询
-- 删掉名字为Myworks的客户的所有发票
DELETE FROM invoices
WHERE client_id =
     (SELECT client_id
	   FROM clients
      WHERE name = 'Myworks');

9. 恢复数据库

File----->Open SQL Script----->在存储SQL脚本的目录位置选择要恢复的数据库----->执行脚本