- 在现实世界中,通常会需要从多张表格里选取列,也就是当想要获取一些列信息时,这些列在不同的表中,就需要连接,将它们连成一个表,之后再进行操作。连接分为内连接和外连接
- 内连接(join)
- inner join products p -- inner可省略
-
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 子句
- 如果两个表中有列名称时完全一样的,我们就可以用一个更简洁的 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;
- 外连接(outer join)
- 当两个表内连接时,结果集只会返回连接条件正确的记录,其他记录不返回,外连接,会返回表中所有记录,对应字段没有值会是null
- 两种连接方式
- 左连接:所有左表的记录,不管连接是否正确,都会返回
- 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)
- 通过 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
11-02
1万+
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
08-16
348
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)