mysql day3

– 插入
– default：系统自动生成唯一值，第一列为主键不需要增加，系统自动递增，直接从第二列开始
– insert into customers
– values (
– default,
– ‘John’,
– ‘Smith’,
– ‘1990-01-01’, – null,这行可空
– null, – default 与 null结果相同
– ‘address’,
– ‘city’,
– ‘CA’,
– default)
– 插入一行
– insert into customers (
– first_name,
– last_name,
– birth_date,
– address,
– city,
– state)
– values (
– ‘John’,
– ‘Smith’,
– ‘1990-01-01’, – null,这行可空
– ‘address’,
– ‘city’,
– ‘CA’)

– 插入多行
– insert into shippers (name)
– values (‘shipper1’),
– (‘shipper2’),
– (‘shipper3’)

– 习题
– insert into products (name,quantity_in_stock,unit_price)
– values (‘Product1’,10,1.95),
– (‘Product2’,11,1.35),
– (‘Product3’,12,1.45)

– 往多表插入数据
– orders 和 order_items 一个订单里买了不同的东西
– insert into orders (customer_id, order_date, status)
– values (1,‘2019-01-02’,1); – 比需插入有效数据，即表中已有的数据
– 往子表插入
– insert into order_items
– values (last_insert_id(),1,1,2.95),
– (last_insert_id(),2,1,3.95)
– select last_insert_id() – 返回/显示的是最后一次插入操作的第一个id

– 创建表复制
– create table order_archived as
– select * from orders – 得到的新表与orders有相同的数据，但是没有主键pk和al标识

– 只复制一部分
– insert into order_archived
– select * from orders
– where order_date < ‘2019-01-01’
– 新表里只有2019年之前的订单

– use sql_invoicing;
– create table invoices_archive as – 新表存储的为select得到的结果
– select
– i.invoice_id,
– i.number,
– c.name as client,
– i.invoice_total,
– i.payment_total,
– i.invoice_date,
– i.payment_date,
– i.due_date
– -- 因为后三列只在发票表中，可不加前缀名i.
– from invoices i
– join clients c
– using (client_id)
– where payment_date is not null

– 更新单行
– update invoices
– set payment_total = 10, payment_date = ‘2019-03-01’
– set payment_total = default, payment_date = NULL 恢复
– where invoice_id = 1
– set payment_total = invoice_total * 0.5,
– payment_date = due_date
– where invoice_id = 3

– 更新多行
update invoices
set payment_total = invoice_total * 0.5,
payment_date = due_date
where client_id = 3

– 习题
– use sql_store;
– update customers
– set points = points + 50
– where birth_date <‘1990-01-01’

– 在updates中用子查询
– update invoices
– set payment_total = invoice_total * 0.5,
– payment_date = due_date
– where payment_date is null

– select *
– from invoices
– where payment_date is null

– where client_id in
– (select client_id
– from clients
– where state in(‘CA’,‘NY’))
– – where name = ‘Myworks’) – where client_id = 先执行这段查询，返回客户id --多选+tab键空格移动

– 练习 超过3000积分的顾客更新订单注释
– update orders
– set comments = ‘Gold customer’
– where customer_id in – 有多个顾客
– (select customer_id
– from customers
– where points >= ‘3000’)

– 删除行
– delete from invoices
– where client_id = (
– select client_id
– from clients
– where name = 'Myworks’)