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