SQL学习 DAY2

2.1 外连接

select * from customers c join orders o on c.customer_id=o.customer_id
order by c.customer_id #会导致customer_id中空的不能返回
#如何解决这个问题？————选用外连接：左连接+右连接
              customers c left join orders o on c.customer_id=o.customer_id
               #左表 customers数据返回 空的数据也会返回

#多表外连接customers+orders+shippers
select * from customers c left join orders o on c.customer_id=o.customer_id#左连接
left join shippers sh on o.shipper_id=sh.shipper_id#shipper_id值中有空的，故用左连接
order by c.customer_id

2.2 using使用

select o.order_id,c.first_name from orders o 
join customers c 
     on o.customer_id=c.customer_id ## 等于 using (customer_id)

select * from order_items oi join order_item_notes oin 
    on oi.order_id=oin.order_id and oi.product_id=oin.product_id
    ##等于 using(order_id,product_id) 

 2.3 联合

select *
from orders where order_date>="2019-01-1" 

#联合 给符合条件的加上标签
select order_id,order_date,"Active"
from orders where order_date>="2019-01-1"

##两个表联合
select first_name from customers
union 
select name_id from shippers

2.4 插入|更新|删除数据

#插入行 根据🔧符号看customer数据特征写入
insert into customers values(
default,"john","smith","1990-01-01",null,"address","city","ca",default)
#括号里写入每列的值;null可换成default

#插入多行
insert into shippers(name)#往shippers的name列添加shipper1 2 3
values ("shipper1"),("shipper2"),("shipper3")

#表中插入分层数据
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) #增加两行在order_items

 2.5 表的创建

#从一张表复制数据到另外一张表
create table orders_archived as #创建表orders_archived
select * from orders #使表数据与orders一致；但是该数据会没有标记主键

insert into orders_archived
select * from orders #复制orders表中的一部分到新表里
where order_date<"2019-01-01"

#例题：
#将invoices和clients列连接,并只选择有付款日期（payment_date）
use sql_invoicing;
create table invoices_archived 
select  *
from invoices i join clients c 
             using(client_id)
where payment_date is not null