备案控制台
开发者社区 数据库 文章 正文

3. 查询和连接

2024-04-30 19
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 3. 查询和连接

内连接

join默认是自然连接on表示在什么条件下连接

sql

复制代码

-- 有重复的customer_id需要确定是哪一个，不然会因为重复而报错
select order_id,orders.customer_id, first_name, last_name     
from orders
inner join customers    -- inner可以省略
  on orders.customer_id = customers.customer_id

简写表名称

sql

复制代码

select order_id,a.customer_id, first_name, last_name 
from orders a
inner join customers    -- inner可以省略
  on a.customer_id = customers.customer_id

自然连接 oi 和 p 表中 oi.product_id = p.product_id 的部分，并输出 order_id, oi.product_id,  quantity, oi.unit_price

sql

复制代码

select order_id,oi.product_id, quantity,oi.unit_price
from order_items oi
join products p 
  on oi.product_id = p.product_id;

跨数据库连接

同理啊，只需要在 join 的表前面加上是哪一个数据库名字就可以。 查询语句会跟着当前使用的数据库的改变而改变

sql

复制代码

select *
from sql_store.order_items oi
join products p
  on oi.product_id = p.product_id

自连接

sql

复制代码

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

和自己链接，通过每个员工编号和领导编号可以输出如下信息。

employee_id first_name manager
33391 D'arcy Yovonnda
37851 Sayer Yovonnda
40448 Mindy Yovonnda
56274 Keriann Yovonnda
63196 Alaster Yovonnda

多表连接

sql

复制代码

use sql_store;
select 
  o.order_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
8 2018-06-08 Clemmie Betchley Processed
6 2018-11-18 Levy Mynett Processed
4 2017-01-22 Ines Brushfield Processed
3 2017-12-01 Thacher Naseby Processed
1 2019-01-30 Elka Twiddell Processed

sql

复制代码

use sql_invoicing;
select 
  p.date,
    p.invoice_id,
  c.client_id,
  c.name,
    pm.name
from payments p
join clients c
  on c.client_id = p.client_id
join payment_methods pm
  on pm.payment_method_id = p.payment_method;
2019-02-12 2 5 Topiclounge Credit Card
2019-01-03 6 1 Vinte Credit Card
2019-01-11 11 3 Yadel Credit Card
2019-01-26 13 5 Topiclounge Credit Card
2019-01-15 15 3 Yadel Credit Card
2019-01-15 17 3 Yadel Credit Card

复合主键

需要使用多重条件来连接两个主键

sql

复制代码

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;

隐式链接

隐式链接是上面的显式连接的另一种写法。 但是不推荐这样写，因为一旦忘记写条件，就会得到两张表的积

sql

复制代码

SELECT *
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id;
-- Implpicit Join Syntax
SELECT * 
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

外连接

默认只写join是内连接，只会返回满足 on 条件的记录如果还想显示未满足的记录，在join前加上left或者right，就是outer外连接

sql

复制代码

SELECT
  c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id

使用LEFT JOIN，customers 中的所有记录都会被返回，不管满不满足条件。RIGHT JOIN同理。

多表外连接

sql

复制代码

SELECT
  c.customer_id,
    c.first_name,
    o.order_id,
    sh.name
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
ORDER BY c.customer_id

sql

复制代码

SELECT
  o.order_date,
  o.order_id,
    c.first_name,
    sh.name as shipper,
    os.name as status
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id
left Join shippers sh
  on o.shipper_id = sh.shipper_id
Join order_statuses os
  on o.status = os.order_status_id
ORDER BY status
2018-06-08 8 Clemmie Processed
2018-11-18 6 Levy Processed
2017-01-22 4 Ines Processed
2017-12-01 3 Thacher Processed
2019-01-30 1 Elka Processed
2018-04-22 10 Elka Schinner-Predovic Shipped
2017-07-05 9 Levy Hettinger LLC Shipped
2018-09-22 7 Ines Mraz, Renner and Nolan Shipped
2017-08-25 5 Clemmie Satterfield LLC Shipped
2018-08-02 2 Ilene Mraz, Renner and Nolan Shipped

自外连接

就是对自己使用外连接，可以有不满足条件的地方

sql

复制代码

use sql_hr;
SELECT
  e.employee_id ,
    e.first_name,
  m.first_name AS manager
FROM employees e
LEFT JOIN employees m
  ON e. reports_to = m. employee_id

USING子句

当作为合并条件（join condition）的列在两个表中有相同的列名时，可用 USING (……, ……) 取代 ON …… AND …… 予以简化，内/外链接均可如此简化。但是列名不同就必须用 ON

sql

复制代码

use sql_store;
SELECT
  o.order_id ,
    c.first_name ,
  sh.name AS shipper
FROM orders o
JOIN customers c
  USING(customer_id)
LEFT JOIN shippers sh
  USING (shipper_id)

sql

复制代码

select *
from order_items oi
left join order_item_notes oin
--  on oi.order_id = oin.order_id and
--    oi.product_id = oin.product_id
  using (order_id,product_id)

自然连接

就是让MySQL自动检索同名列作为合并条件。 最好别用，因为不确定合并条件是否找对了，有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的

sql

复制代码

USE sql_store;
SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

交叉连接

得到名字和产品的所有组合，因此不需要合并条件。显式连接：

sql

复制代码

select 
  c.first_name,
    p.name
from customers c
cross join products p
order by c.first_name

隐式连接：

sql

复制代码

select 
  sh.name as shipper,
    p.name as product
from shippers sh,products p
order by sh.name

Union联合

结合多个表的行FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表

sql

复制代码

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'

sql

复制代码

SELECT first_name, last_name
FROM customers
UNION
SELECT nameFROM shippers

注意：

  • 合并的查询结果必须列数相等，否则会报错
  • 合并表里的列名由排在 UNION 前面的决定

sql

复制代码

select 
  customer_id,
    first_name,
    points,
    'Bronze' as type
from customers
where points < 2000
union
select 
  customer_id,
    first_name,
    points,
    'Sliver' as type
from customers
where points >= 2000 and points < 3000
union
select 
  customer_id,
    first_name,
    points,
    'Gold' as type
from customers
where points >= 3000
order by points desc


文章标签：
SQL
数据库
关系型数据库
MySQL
数据库连接
俄罗斯刺沙蓬
目录
相关文章
唐唐思
|
1天前
|
数据库
【数据库】简单&连接&嵌套查询
【数据库】简单&连接&嵌套查询
唐唐思
33 2
吃豆子的恐龙
|
10月前
|
SQL
SQL Server—并查询、交查询、差查询
将两个select语句查询到的结果合并
吃豆子的恐龙
71 1
潇湘信安
|
8月前
|
安全 API 网络安全
查询和开启3389端口方式总结
查询和开启3389端口方式总结
潇湘信安
944 0
dahezhiquan
|
SQL 关系型数据库 MySQL
Mysql数据库，去重，连接篇
sql99的优点是：表的连接是独立的，不占用where的位置。使sql语句整体更加清晰😀 内连接 - 非等值连接 案例：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级
dahezhiquan
122 0
q7s2kces74wvy
|
SQL 大数据 开发者
连接操作_连接类型_inner | 学习笔记
快速学习连接操作_连接类型_inner
q7s2kces74wvy
65 0
连接操作_连接类型_inner | 学习笔记
有勇气的牛排
|
SQL Serverless
SQL Sever联接查询
作者主页：https://www.couragesteak.com/
有勇气的牛排
103 0
SQL Sever联接查询
dasein58
|
数据库连接
ACCESS数据库连接查询语句
　　基于视频讲解中的实现语句，包括：LEFT JOIN（左连接）、RIGHT JOIN（右连接）、INNER JOIN（内连接）和笛卡尔积加条件，语句的写法格式如下： 　　LEFT JOIN（左连接） 　　SELECT a.*, b.英语分数 AS 英语期中分数 　　FROM 入学成绩 AS a 　　LEFT JOIN 期中成绩 AS b 　　ON a.学号=b.学号;RIGHT JOIN（右连接）
dasein58
144 0
明明如月
连接、查询出现的问题及解决方案(…
连接、查询出现的问题及解决方案(…
明明如月
121 0
范大脚脚
|
SQL 索引
T-SQL查询高级--理解SQL SERVER中非聚集索引的覆盖，连接，交叉和过滤
范大脚脚
1048 0
科技探索者
|
网络协议
批处理查询主机信息
科技探索者
929 0