MySQL连接:内连接、左外连接、右外连接

内连接(inner join)

MySQL连接:内连接、左外连接、右外连接

语句:
	select [表字段1],[表字段2],... from [表A],[表B] where [表A.相同字段] = [表B.相同字段]
隐式内连接:
select first_name, last_name, order_date, order_amount
from customers, orders 
where customers.customer_id = orders.customer_id
显示内连接:
select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id

左外连接(left outer join,outer可省略)

MySQL连接:内连接、左外连接、右外连接

语句:
	select [表字段1],[表字段2],... from [表A] LEFT JOIN [表B] on [表A.预关联的字段] = [表B.预关联的字段]
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
SELECT
  o.`id`,
  o.`oil_name`,
  o.`address`,
  o.`longitude`,
  o.`latitude`,
  o.`discount`,
  o.`explains`,
  DATE_FORMAT (o.`adddate`, '%Y-%m-%d %H:%i') ADDDATE,
  o.`status`,
  a.id aid,
  a.`attMiniUrl`,
  a.`attType`,
  a.`attUrl`,
  a.`businessType`,
  a.`isdefault`,
  a.`name`,
  DATE_FORMAT (
    a.`uploadTime`,
    '%Y-%m-%d %h:%i'
  ) uploadTime
FROM
  b_oil o
  LEFT JOIN b_attachment a
    ON o.id = a.businessId
    AND a.businessType = 1 
<where>
    o.isdel=0
       <if test="oil_name !=null and oil_name !='' ">
          and o.oil_name like concat('%',#{oil_name},'%')
       </if>
       <if test="address !=null and address !='' ">
          and o.address like concat('%',#{address},'%')
       </if>
       <if test="status!=null and status!=''">
          and o.status=#{status}
       </if>
</where>
    <if test="begin!=null">
       limit #{begin},#{pageSize}
    </if>

右外连接(right outer join,outer可省略

MySQL连接:内连接、左外连接、右外连接

select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id
上一篇:P3项目全球模板狗血设置之一 --- 发货到成本中心需要输入Customer


下一篇:go实现员工管理系统