时刻记住自己要成为什么样的人。 ——大宁
mysql的几种关联操作
JOIN的含义就如英文单词“join”一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接。这里描述先甩出一张用烂了的图,然后插入测试数据。
union(并)
查询相关
-- 略
说明
#什么是union连接?
就是两个表的查询竖直拼接。
实验
show tables;
--创建一个表1: tab1
create table tab1(
id int,
name VARCHAR(20),
age int,
hight float
);
-- 创建一个表2 和表1的结构一样: tab2
create table tab2 like tab1;
-- 往tab1中插入一些数据
insert into tab1 values
(1, 'a1', 21, 191.3),
(2, 'a2', 22, 192.3),
(3, 'a3', 23, 193.3);
-- 往tab2中插入一些数据
insert into tab2 values
(3, 'b1', 11, 141.3),
(4, 'b2', 12, 132.3),
(5, 'b3', 13, 123.3);
-- 查看数据
select * from tab1;
select * from tab2;
-- 使用union将两次查询拼接起来
select * from tab1
union
select * from tab2;
cartesian product(笛卡尔积)
查询相关
https://www.cnblogs.com/HH0260/p/15090312.html
说明
实验
-- 沿用上面(union)的数据
-- 以下三个查询语句的结果都是笛卡尔积
select * from tab1 inner join tab2;
select * from tab1 cross join tab2;
select * from tab1, tab2;
-- 结果:
1 a1 21 191.3 3 b1 11 141.3
2 a2 22 192.3 3 b1 11 141.3
3 a3 23 193.3 3 b1 11 141.3
1 a1 21 191.3 4 b2 12 132.3
2 a2 22 192.3 4 b2 12 132.3
3 a3 23 193.3 4 b2 12 132.3
1 a1 21 191.3 5 b3 13 123.3
2 a2 22 192.3 5 b3 13 123.3
3 a3 23 193.3 5 b3 13 123.3
Inner join (内连接)
查询相关
yiibai.com/mysql/inner-join.html
https://blog.csdn.net/gezongbo/article/details/120790221
说明
概念
#什么是内连接?
是内连接查询中一种特殊的等值连接,所谓的自连接就是指表与其自己当前表进行连接。自己和自己做连接。
语法
语法1:select f1,f2,f3...
from table1 inner join table2
on 关联匹配条件
语法2: select f1,f2,f3...
from table1,table2,table3
where 关联匹配条件
实验
select * from tab1 t1
INNER JOIN
tab2 t2
-- 结果:既是笛卡尔积
1 a1 21 191.3 3 b1 11 141.3
2 a2 22 192.3 3 b1 11 141.3
3 a3 23 193.3 3 b1 11 141.3
1 a1 21 191.3 4 b2 12 132.3
2 a2 22 192.3 4 b2 12 132.3
3 a3 23 193.3 4 b2 12 132.3
1 a1 21 191.3 5 b3 13 123.3
2 a2 22 192.3 5 b3 13 123.3
3 a3 23 193.3 5 b3 13 123.3
-- 内连接实验
select * from tab1 t1
INNER JOIN
tab2 t2
on t1.id = t2.id;
-- 结果:on是连接筛选条件。相当于在上面笛卡尔积的基础上增加一个条件:t1.id = t2.id
3 a3 23 193.3 3 b1 11 141.3
-- 内连接的on中不仅可以写等于,还可以写不等于,总而言之:相当于在迪卡尔积的基础上再加一个过滤条件
select * from tab1 t1
INNER JOIN
tab2 t2
on t1.id != t2.id;
-- 结果:
1 a1 21 191.3 3 b1 11 141.3
2 a2 22 192.3 3 b1 11 141.3
1 a1 21 191.3 4 b2 12 132.3
2 a2 22 192.3 4 b2 12 132.3
3 a3 23 193.3 4 b2 12 132.3
1 a1 21 191.3 5 b3 13 123.3
2 a2 22 192.3 5 b3 13 123.3
3 a3 23 193.3 5 b3 13 123.3
natural join(自然连接)
查询相关
https://blog.csdn.net/weixin_39793564/article/details/114328932
说明
概念
#什么是自然连接?
使用 NATURAL JOIN 时,MySQL 将表中具有相同名称的字段自动进行记录匹配,而这些同名字段类型可以不同。因此,NATURAL JOIN 不用指定匹配条件。NATURAL JOIN 默认是同名字段完全匹配的 INNER JOIN。
简单的说:NATURAL JOIN 就是左右两表的行交集
实验
-- 补充一条数据
insert into tab2 values
(2, 'a2', 22, 192.3);
select * from tab1 t1
NATURAL JOIN
tab2 t2 ;
-- 结果: 2 a2 22 192.3
outer join(外连接)
查询相关
说明
概念
#什么是外连接
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。
#另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现。 !!!!
# 什么是左外连接,右外连接,全外连接
实验
-- 无: 因为MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现。 !!!!
left join(左连接)
查询相关
说明
概念
#什么是左连接?
就是以左表为基准,将满足条件的右表拼接在后面,没得到拼接的以null填充
实验
select * from tab1
-- 结果:
1 a1 21 191.3
2 a2 22 192.3
3 a3 23 193.3
select * from tab2
-- 结果:
3 b1 11 141.3
4 b2 12 132.3
5 b3 13 123.3
2 a2 22 192.3
select * from tab1 t1
left join
tab2 t2
on t1.id = t2.id;
-- 结果:
3 a3 23 193.3 3 b1 11 141.3
2 a2 22 192.3 2 a2 22 192.3
1 a1 21 191.3 null null null null
right join(右连接)
查询相关
说明
概念
#什么是左连接?
与左连接相反。就是以右表为基准,将满足条件的左表拼接在前面,没得到拼接的以null填充
实验
select * from tab1
-- 结果:
1 a1 21 191.3
2 a2 22 192.3
3 a3 23 193.3
select * from tab2
-- 结果:
3 b1 11 141.3
4 b2 12 132.3
5 b3 13 123.3
2 a2 22 192.3
select * from tab1 t1
right join
tab2 t2
on t1.id = t2.id;
-- 结果:
2 a2 22 192.3 2 a2 22 192.3
3 a3 23 193.3 3 b1 11 141.3
null null null null 4 b2 12 132.3
null null null null 5 b3 13 123.3
汇总总结
https://blog.csdn.net/weixin_42168230/article/details/113909672
JOIN的含义就如英文单词“join”一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接。这里描述先甩出一张用烂了的图,然后插入测试数据。
#1.笛卡尔积:CROSS JOIN
·要理解各种JOIN首先要理解笛卡尔积。
·笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。
·所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。
·t_blog表有10条记录,t_type表有5条记录,所有他们俩的笛卡尔积有50条记录。
#2.内连接:INNER JOIN
·内连接INNER JOIN是最常用的连接操作。
·从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。
有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法。
SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
#左连接:LEFT JOIN
·左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。
·依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录(见最后三条)。
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
+----+-------+--------+------+------+
| id | title | typeId | id | name |
+----+-------+--------+------+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
+----+-------+--------+------+------+
#右连接:RIGHT JOIN
·同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。
·再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录(见最后一条)。
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
+------+-------+--------+----+------------+
| id | title | typeId | id | name |
+------+-------+--------+----+------------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 6 | fff | 3 | 3 | Java |
| 7 | ggg | 2 | 2 | C |
| NULL | NULL | NULL | 5 | Javascript |
+------+-------+--------+----+------------+
#外连接:OUTER JOIN
·外连接就是求两个集合的并集。
·从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。
·另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现。
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
UNION
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
+------+-------+--------+------+------------+
| id | title | typeId | id | name |
+------+-------+--------+------+------------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | Javascript |
+------+-------+--------+------+------------+
USING子句
MySQL中连接SQL语句中,ON子句的语法格式为:table1.column_name = table2.column_name。
当模式设计对联接表的列采用了相同的命名样式时,就可以使用 USING 语法来简化 ON 语法,格式为:USING(column_name)。
所以,USING的功能相当于ON,区别在于USING指定一个属性名用于连接两个表,而ON指定一个条件。
另外,SELECT *时,USING会去除USING指定的列,而ON不会。实例如下。
SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId =t_type.id;
+----+-------+--------+----+------+
| id | title | typeId | id | name |
+----+-------+--------+----+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
+----+-------+--------+----+------+
SELECT * FROM t_blog INNER JOIN t_type USING(typeId);
ERROR 1054 (42S22): Unknown column 'typeId' in 'from clause'
SELECT * FROM t_blog INNER JOIN t_type USING(id); -- 应为t_blog的typeId与t_type的id不同名,无法用Using,这里用id代替下。
+----+-------+--------+------------+
| id | title | typeId | name |
+----+-------+--------+------------+
| 1 | aaa | 1 | C++ |
| 2 | bbb | 2 | C |
| 3 | ccc | 3 | Java |
| 4 | ddd | 4 | C# |
| 5 | eee | 4 | Javascript |
+----+-------+--------+------------+
#自然连接:NATURE JOIN
自然连接就是USING子句的简化版,它找出两个表中相同的列作为连接条件进行连接。
有左自然连接,右自然连接和普通自然连接之分。
在t_blog和t_type示例中,两个表相同的列是id,所以会拿id作为连接条件。
另外千万分清下面三条语句的区别 :
自然连接:SELECT * FROM t_blog NATURAL JOIN t_type;
笛卡尔积:SELECT * FROM t_blog NATURA JOIN t_type;
笛卡尔积:SELECT * FROM t_blog NATURE JOIN t_type;