SQL----chapter4:inserting, updating and deleting data

 1.列属性

• 点击表的扳手按钮：打开设计模式，介绍了一些表中字段/列的属性。

2. 插入单行

insert into ---目标表（目标列，逗号隔开，也可以省略这一部分不指定列）
values ---（目标值，逗号隔开）

---在顾客表里插入一个新顾客的信息
---法1：不指明列名（注意连括号也省了），但插入的值必须按所有字段的顺序完整插入

use sql_store;

insert into customers
values (
      default, 
      'Michael', 
      'Jackson', 
      '1958-08-29', 
       default, 
      '5225 Figueroa Mountain Rd', 
      'Los Olivos', 
      'CA', 
       default
       )

---法2：指明列名，可跳过取默认值的列且可更改顺序，更清晰

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'
       )          

3.插入多行

• values…… 里一行内数据用括号内逗号隔开，而多行数据用括号间逗号隔开 
• 对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增

use sql_store;

insert into shippers (name)
values ('shipper1'),
       ('shipper2'),
       ('shipper3')


use sql_store;

insert into products
values (default, 'product1', 1, 10),
       (default, 'product2', 2, 20),
       (default, 'product3', 3, 30)
或
insert into products (name, quantity_in_stock, unit_price)
values ('product1', 1, 10),
       ('product2', 2, 20),
       ('product3', 3, 30)

 4.插入分级行

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

相关知识点：

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

---新增一个订单（order），里面包含两个订单项目/两种商品（order_items），请同时更新订单表和订单项目表

use sql_store;

insert into orders (customer_id, order_date, status)
values (1, '2019-01-01', 1);

insert into order_items   ---全是必须字段，就不用指定了
values (last_insert_id(), 1, 1, 2.95),
       (last_insert_id(), 2, 1, 3.95)

5.创建表的副本

小结

• 法1. 删除重建：drop table 要删的表名、create table 新表名 as 子查询
• 法2. 清空重填：trucate '要清空的表名'、insert into 表名 子查询
• 子查询里当然也可以用 where 语句进行筛选

---案例 1
---运用 create table 新表名 as 子查询 快速创建表 orders 的副本表 orders_archived

use sql_store;

create table orders_archived as 
        select *
        from orders  ---子查询

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



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

use sql_store;

drop table orders_archived;
create table orders_archived as
        select *
        from orders
        where order_date < '2019-01-01'

---法2. truncate '要清空的表名'、insert into 表名 子查询
---insert into 表名 子查询 很常用，子查询替代原先插入语句中 values(……,……),(……,……),…… 的部分

truncate 'orders_archived';
---新的 8.0版 MySQL 的语法好像变为了 truncate table orders_archived？那样就与 drop table
orders_archived 一致了
insert into orders_archived  ---不用指明列名，会直接用子查询表里的列名
        select *
        from orders
        where order_date < '2019-01-01'

---创建一个存档发票表，只包含有过支付记录的发票并将顾客id换成顾客名字
---构建的思路顺序：
---1. 先创建子查询，确定新表内容：
---    1. 合并发票表和顾客表
---    2. 筛选支付记录不为空的行/记录
---    3. 筛选（并重命名）需要的列
---2. 第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

6.更新单行

--- 用 UPDATE …… 语句 来修改表中的一条或多条记录，具体语法结构：
update 表 
select 更改字段 = 具体值/null/default/列间数学表达式  ---修改多个字段用逗号分隔
where 行筛选

--- 实际执行顺序为update —— where —— select


use sql_invoicing;

update invoices
select payment_total = 100 / 0 / default / null / 0.5 * invoice_total,
       payment_date = '2019-01-01' / default / null / due_date
where invoice_id = 3

7.更新多行

use sql_invoicing;

update incoices
select payment_total = 233, payment_date = due_date
where client_id = 3   --- 该客户的发票记录不止一条，将同时更改
/where client_id in (3, 4)   

use sql_store;

update customers
select points = points + 50
where birth_date < '1990-01-01'

8.在updates中使用子查询

本质上是将子查询用在 WHERE…… 行筛选条件中

注意:

• 1. 括号的使用
• 2. IN …… 后除了可接 （……, ……） 也可接由子查询得到的多个数据（一列多条数据）

--- 更改发票记录表中名字叫 Yadel 的记录，但该表只有 client_id，故先要从另一个顾客表中查询叫 Yadel 人的 client_id

use sql_invoicing;

update invoices
select 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语句更新 

--- 可以先验证子查询：

select client_id
from clients
where state in ('CA', 'NY')

--- 以及验证WHERE行筛选条件（即先不UPDATE，先SELECT，改之前，先看一看要改的目标选对了没）

select *
from invoices
where client_id in 
            (select client_id
            from clients
            where state in ('CA', 'NY'))

--- 确保WHERE行筛选条件准确准确无误后，再放到修改语句后执行修改：

update invoices
select payment_total = 567, payment_date = due_date
where client_id in 
            (select client_id
            from clients
            where state in ('CA', 'NY'))

--- 有子查询的 Update 主要验证 Where 条件中的 子查询部分正不正确，而没有子查询的 Update 则应该将 update换成 select 先验证一下整个 Where 筛选条件正不正确。

--- 将 orders 表里那些 分数>3k 的用户的订单 comments 改为 ‘gold customer’，
--- 思考步骤：
--- 1. WHERE 行筛选出要求的顾客
--- 2. SELECT 列筛选他们的id
--- 3. 将前两步 作为子查询 用在修改语句中的 WHERE 条件中，执行修改

use sql_store;

update orders
select customers = 'gold customer'
where customer_id in
            (select customer_id
            from customers
            where points > 3000)

9.删除行

---语法结构：
delete from 表
where 行筛选条件（当然也可用子查询）

use sql_invoicing;

delete from invoices
where client_id = 3
/where client_id =
        (select client_id
        from clients
        where name = 'Myworks')

10.恢复数据库