Mysql-3-连接

2022-05-28 13:54:55

内连接

连接的关键字是JOIN而连接又分成内连接和外连接，内连接的关键字是INNER JOIN

但是INNER可写可不写

关键字后用ON 写两个表联合的数据

SELECT *
FROM orders
JOIN customers
    ON orders.customer_id=customers.customer_id

比如这个就是说连接orders和customers两张表要确保两个表中顾客id列一样

（注意要显示两个链接的表都有的列要指明位置如表名.列名）

这里也是可以简化代码的

SELECT *
FROM orders o
JOIN customers c
    ON o.customer_id=c.customer_id

在表名后面可以自定义代称这样就可以简化下代码

跨数据库连接

其实用法和内连接很相像，只不过这是两个数据库所以在写表的时候要在前面写上数据库的名字（只需要给不在当前数据库的表加前缀）

USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p
 ON oi.product_id=p.product_id

这是在数据库sql_inventory中将库中的products表和sql_store数据库中的order_items相连接

自连接

一张表和他自己连接 ，这种有什么用呢，比如这里有个存放员工管理工作的表也就是上下级关系

Mysql-3-连接

我们可以通过自连接同时显示员工的详细信息以及其对应管理者的详细信息

USE sql_hr;
SELECT *
FROM employees e
JOIN employees m
    ON e.reports_to=m.employee_id

Mysql-3-连接

这里其实理解为复制了一张一样的表只是代号不一样，我们从e表中获取员工信息，从m表中获取对应的管理者信息，将两者合并

所以我们在筛选列的时候一定要注明是那张表

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

Mysql-3-连接

多表连接

其实和内连接差不多只是同一个数据库中一个表连接两个或两个以上的其他表

我们就依次像内连接一样依次连接就好了

比如我要链接如下三张表的信息（他们在同一个数据库中）

Mysql-3-连接

USE sql_store;
​
SELECT *
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_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

Mysql-3-连接

复合连接条件、

我们一般的表都有单一的列能够准确识别表中的一行比如

Mysql-3-连接

customers表中的customer_id这一列每个数字都是不一样的能识别单独一行，但是有些表没有这种列

Mysql-3-连接

这张表就需要用两列的信息唯一识别这一行

Mysql-3-连接

总的来说就是要满足两个列的值一一对应来完成连接我们叫做复合连接，写条件时直接加上AND就ok了

隐式连接语法

我们主要通过一个简单的例子来说明

USE sql_store;
​
SELECT *
FROM orders o
JOIN customers c
    ON o.customer_id=c.customer_id
USE sql_store;
​
SELECT *
FROM orders o,customers c
WHERE o.customer_id=c.customer_id

这两个查询结果是一样的

但是隐式的连接会让人有时候忘了写条件，所以为了严谨我们一般还是写JOIN，只是需要知道这样的代码是啥意思就行了

外连接

OUTER JOIN

USE sql_store;
​
SELECT 
c.customer_id,
c.first_name,
o.order_id
FROM customers c
JOIN orders o
    ON c.customer_id=o.customer_id
    ORDER BY c.customer_id

当我们执行的时候，我们只会看到有订单的人的信息而不是所有人，因为有的人在order里面没有信息满足不了条件

Mysql-3-连接

所以这里有两种外连接

LEFT JOIN

USE sql_store;
​
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

当使用做链接时就是join左边那张表不管是否满足后面的条件都会显示所有Mysql-3-连接

RIGHT JOIN

同理就是右边的表可以不管条件返回列中所有的信息

多表外连接

和多表的内连接类似

现在我们要在上节课的基础上显示出发货人的id其信息在shippers表中

USE sql_store;
​
SELECT 
c.customer_id,
c.first_name,
o.order_id,
s.shipper_id
FROM orders o
LEFT JOIN customers c
    ON c.customer_id=o.customer_id
JOIN shippers s
    ON o.shipper_id=s.shipper_id
    ORDER BY c.customer_id

Mysql-3-连接

发现只有这一点信息这是因为一些商品没有发货人信息，所以不满足条件没有显示，我们要让他全部显示无视条件就要用外连接

USE sql_store;
​
SELECT 
c.customer_id,
c.first_name,
o.order_id,
s.name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id=o.customer_id
LEFT JOIN shippers s
    ON o.shipper_id=s.shipper_id
    ORDER BY c.customer_id

一般使用外连接就使用左连接，你左右连接混合会使人思维混乱

自外连接

回顾一下我们之前学的自连接来获取每个员工的管理者

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

但是这样会有一个问题就是有的员工没有管理者就不会显示出来，我们就可以将连接改成外连接这样就显示了

Mysql-3-连接

USING子句

用来简化连接

当我们要连接两个列的名字一模一样时就可以用USING来取代ON

USE sql_store;
​
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id=c.customer_id

可以改成

USE sql_store;
​
SELECT *
FROM orders o
JOIN customers c
USING (customer_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

这里我们的条件有两个且都是不同表的同名列所以我们用USING改写

USE sql_store;
​
SELECT *
FROM order_items oi
JOIN order_item_notes oin 
USING (order_id,product_id)

是不是简短多了

自然连接

关键字NATURAL JOIN

一般不建议使用，有时候结果会有出入，只是要知道这是干什么的

使用后就不用写条件了，系统自定义同名的列进行连接

Mysql-3-连接

交叉连接

我们用交叉连接结合或者连接第一个表的每条记录和第二个表的每条记录

关键字CROSS JOIN

两个表的每条记录都会互相结合，所以不用写条件

Mysql-3-连接

改进一下就可以将每个顾客与相对应的产品一一对应了

Mysql-3-连接

USE sql_store;
SELECT 
sh.name AS shipper,
p.name AS product
FROM shippers sh
CROSS JOIN products P
ORDER BY sh.name

练习

联合|Unions

关键字UNION

我们学的连接都是链接多张表的列，SQL里也可以连接多张表的行

比如我们筛选出一些有用信息，但是筛选条件不一样又想要一起显示结果时就i可以用联合

SELECT 
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date>='2019-01-01'

筛选出19年1月后的订单，状态显示Active

Mysql-3-连接

SELECT 
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date<'2019-01-01'

筛选出19年1月之前的订单状态显示Archived

Mysql-3-连接

随后将两个结果合并

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'

Mysql-3-连接

这里是同一张表但是我们也可以是不同的表

只是要记住查询返回的列的数量一定要一样，否者会得到错误提示

USE sql_store;
SELECT 
customer_id,
first_name,
points,
'Bronze' AS type
From customers
WHERE points<2000
UNION 
SELECT 
customer_id,
first_name,
points,
'Silver' 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 first_name

练习



上一篇：SQL注入绕过技术


下一篇：2019-腾讯Android面试精选题——谈一谈Binder的原理和实现一次拷贝的流程