SQL - 多表查询

SELECT o.product_id,name,quantity,o.unit_price
from order_items o	-- 别名 o 
inner join products p 	-- inner可省略 
on o.product_id=p.product_id	-- 条件

• 如何将分散在多个数据库的表中的列合并起来

• inner join sql_inventory.products p -- 用它所在的数据库充当前缀，即可使用

• SELECT o.product_id,name,quantity,o.unit_price
  from order_items o	-- 别名 o 
  inner join products p 	-- inner可省略 
  on o.product_id=p.product_id	-- 条件
  

• 可以将一张表与它自己连接，例如在公司中，一个人即使员工又是管理者

• 自连接跟连接其他的表大致不同，唯一区别就是我们要使用不同的别名。还要给每个列名取个别名，加以区分。

• use sql_hr;
  select e.employee_id,e.first_name,e.last_name,
  m.employee_id as manager_id
  from employees e	-- 使用别名后，再引用表名只能使用别名
  join employees m on e.reports_to=m.employee_id
  

• 连接超过两张表，继续使用 join 关键字添加表即可

• select c.client_id,invoice_id,date,amount,p.payment_method
  from payments p
  join payment_methods pm on p.payment_id=pm.payment_method_id
  join clients c on p.client_id=c.client_id;
  

• 当一个表中不能用唯一列识别每一行时，比如用两列值才能识别每一行，怎样将这个表同其他表相连接呢？要将这个表中这两个列都要连接作为连接条件，才能连接这个表

• select *
  from order_items oi
  join order_items_notes oin
  on oi.order_id=oin.order_Id and oi.product_id=oin.product_id
  

• 如果两个表中有列名称时完全一样的，我们就可以用一个更简洁的 using 子句替换 on 子句

• select o.customer_id,o.order_id,c.first_name,s.name as shipper
  from orders o
  join customers c 
  -- on o.customer_id=c.customer_id 代替
  using (customer_id)
  left join shippers s
  -- on o.shipper_id=s.shipper_id 代替
  using (shipper_id)
  

• 尽量不要使用，尽量使用显式连接

• -- 显式
  select *
  from orders o
  join customers c on o.customer_id=c.customer_id;
  -- 隐式
  select *
  from orders o,customers c
  where o.customer_id=c.customer_id;
  

• 显式连接
  • from orders o join customers c on o.customer_id=c.customer_id;

• 隐式连接
  • from orders o,customers c where o.customer_id=c.customer_id;

• 左连接：所有左表的记录，不管连接是否正确，都会返回
  • from products p left join order_items oi on p.product_id=oi.product_id;

• 右连接：所有右表的记录，不管连接是否正确，都会返回
  • from products p right join order_items oi on p.product_id=oi.product_id;

  • select p.product_id,name,quantity
    from products p
    -- left join order_items oi on p.product_id=oi.product_id;
    right join order_items oi on p.product_id=oi.product_id;
    

• 最好一直使用左连接，使得连表的情况不那么复杂

• select order_date,order_id,first_name,s.shipper_id as shipper,os.name as status
  from orders o
  left join customers c on c.customer_id=o.customer_id
  left join shippers s on o.shipper_id=s.shipper_id
  left join order_statuses os on os.order_status_id=o.status
  

• 跟自连接类似，只是还要返回没有匹配的空值

• select 
      s.employee_id,
      s.first_name,
      m.employee_id as manager
  from employees s
  left join employees m on s.reports_to=m.employee_id
  

• 一个更简便的方法连接两个表，通过使用自然连接，我们就不用具体写出连接条件了，数据库引擎会自己会基于共同的列连接，但是我们自己无法控制它，可以生成意外的结果

• select *
  from orders o
  natural join customers c
  

• 第一个表的每条记录和第二个表的每条记录都会结合

• from customers c cross join products p (显式连接)

• from customers c,products p (隐式连接)

• 通过 union关键，我们可以合并多段查询的记录

• 注意：
  • 这些查询可以基于同一张表格，也可以是不同表格
  • 所有的select语句中的列数和顺序必须相同，列的数据类型必须相同或兼容

  • -- 合并同表的几段查询，根据分数不同，划分金、银、铜
    select customer_id,
    	first_name,
        points,
        'Gold' as type
    from customers c
    where points>=3000
    union
    select customer_id,
    	first_name,
        points,
        'Silver' as type
    from customers c
    where points >=2000 and points <3000
    union
    select customer_id,
    	first_name,
        points,
        'Bronze' as type
    from customers c
    where points<2000
    order by points desc