mysql day2

– 自链接
– use sql_hr;
– select e.employee_id, e.first_name, m.first_name as manager
– 得出每个员工的编号姓名和对应管理者的姓名
– from employees e
– join employees m
– on e.reports_to = m.employee_id

– 多表链接
– use sql_store;
– select
– o.order_id, – 订单id
– o.order_date,-- 订单日期
– c.first_name,-- 顾客名字
– c.last_name,
– os.name as status – 订单状态
– from orders o
– join customers c
– on o.customer_id = c.customer_id
– join order_statuses os
– on o.status = os.order_status_id

– 习题
– use sql_invoicing;
– select
– p.date,
– p.invoice_id,
– p.amount,
– c.name,
– pm.name
– from payments p
– join payment_methods pm
– on p.payment_method = pm.payment_method_id
– join clients c
– on p.client_id = c.client_id

– 复合链接
– 表中有两个属性才能唯一确定一条记录
– use sql_store;
– select *
– from order_items oi
– join order_item_notes oin
– on oi.order_id = oin.order_Id
– and oi.product_id = oin.product_id

– 隐式连接 用where，后接join on/显示连接中的on后的表达式

– 外链接
– select
– c.customer_id,
– c.first_name,
– o.order_id
– from customers c

– join orders o
– 内连接取交集，只能得到有订单的顾客

– left join orders o
– left join on 外链接取交集+左表，在from的表(左表)的记录都会被返回，可以不满足on条件，可得到没有订单的顾客

– right join orders o
– right join on 外链接取交集+右表，在join的表(右表)的记录都会被返回，与join orders o结果相同
– 左右表可以交换位置

– on c.customer_id = o.customer_id
– order by c.customer_id

– 习题
– select
– p.product_id,
– p.name,
– oi.quantity
– from products p
– left join order_items oi
– on p.product_id = oi.product_id
– 没有被下单的产品也能显示

– 多表外链接
– select
– c.customer_id,
– c.first_name,
– o.order_id,
– sh.name as shipper
– from customers c
– left join orders o
– on c.customer_id = o.customer_id
– -- join shippers sh
– -- 所有的订单中没发货的不显示
– left join shippers sh
– on o.shipper_id = sh.shipper_id
– 所有的订单不管发货或是没发货都显示
– order by c.customer_id

– 尽量避免右链接，最好使用左连接

– 习题
– select
– o.order_date,
– o.order_id,
– c.first_name,
– sh.name as shipper,
– os.name as status
– from orders o
– left join customers c
– on o.customer_id = c.customer_id
– – 可以不用left，每笔订单都有顾客，总是满足on条件
– left join shippers sh
– on sh.shipper_id = o.shipper_id
– left join order_statuses os
– on os.order_status_id = o.status
– order by status

– 自外链接
– use sql_hr;
– select
– e.employee_id,
– e.first_name,
– m.first_name as manager
– from employees e
– -- join employees m
– left join employees m
– on e.reports_to = m.employee_id – 只返回有管理者的员工，manager没有id

– using子句,在不同表中列的名字完全相同时可替换on
– select
– o.order_id,
– c.first_name,
– sh.name as shipper
– from orders o
– join customers c
– – on o.customer_id = c.customer_id
– using (customer_id)
– left join shippers sh
– using (shipper_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)

– 习题
– use sql_invoicing;
– select
– p.date,
– c.name as client,
– p.amount,
– pm.name as payment_method
– from payments p
– join clients c
– using(client_id)
– join payment_methods pm
– on p.payment_method = pm.payment_method_id

– 自然连接 基于共同的列链接,不建议使用
– use sql_store;
– select
– o.order_id,
– c.first_name
– from orders o
– natural join customers c

– 交叉链接 链接第一个表的每条记录与第二个表的每条记录,没有条件on
– 不常用，所有型号和所有颜色组合时才采用交叉连接
– select
– c.first_name as customer,
– p.name as product

– 显示连接：
– from customers c
– cross join products p
– 隐式连接
– from customers c, products p
– order by c.first_name

– 联合 union合并多段查询，可在某段结束尾加；表示已结束

– 基于同一个表
– select
– order_id,
– order_date,
– ‘Active’ as status – 做标记
– from orders
– where order_date >= ‘2019-01-01’
– union
– select
– order_id,
– order_date,
– ‘Archived’ as status – 做标记
– from orders
– where order_date < ‘2019-01-01’

– 基于不同的表
– 要求查询返回的列的数量要一致，如果第一段返回两列，第二段返回一列则报错
– select first_name – as fullname 将列名改名
– from customers
– union
– select name
– from shippers
– 结果得到的表的列名由第一段的列名决定

– 习题
select
c.customer_id,
c.first_name,
c.points,
‘Bronze’ as type
from customers c
where points < ‘2000’
union
select
c.customer_id,
c.first_name,
c.points,
‘Silver’ as type
from customers c
where points between 2000 and 3000
union
select
c.customer_id,
c.first_name,
c.points,
‘Gold’ as type
from customers c
where points > ‘3000’
order by first_name – firstname前不可以加表名