MySQL连接查询
今日份浅谈MySQL的连接查询,首先会使用以下数据表:
# 学生表
mysql> select * from student; +----+--------+------+------+------+--------+-------+
| id | number | name | sex | age | height | class |
+----+--------+------+------+------+--------+-------+
| 1 | 22 | asd | boy | 35 | 174 | 3 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 |
| 3 | 11 | ert | boy | 35 | 175 | 3 |
| 4 | 44 | rty | girl | 26 | 179 | 1 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 |
| 6 | 23 | keb | boy | 36 | 188 | 4 |
+----+--------+------+------+------+--------+-------+
6 rows in set (0.00 sec)
# 班级表
mysql> select * from class;
+------+----------+------+
| id | name | room |
+------+----------+------+
| 1 | java01 | a101 |
| 2 | java02 | a102 |
| 3 | python01 | b101 |
| 4 | python02 | b102 |
+------+----------+------+
4 rows in set (0.00 sec)
什么是连接查询
这个连接查询顾名思义就是把多个表连接起来进行记录查询,也就是将多张表进行记录的连接(按照某个指定的条件进行数据拼接)。当查询完成后,系统返回的记录数可能有变化(有可能匹配不到),字段数一定会增加(毕竟多张表合并嘛)。
连接查询的意义
呢连接查询的意义又在哪里呢?当我们查询数据时,需要显示的数据来自于多张表,这个时候就需要连接查询,打个比方说,学校的教务系统可以同时查询到个人信息和考试成绩信息,这个肯定就需要多表连接查询了(也有人可能会说记录就不会在同一个表中嘛,建议了解一下数据库的设计)
连接查询的方式
左表 join 右表;
左表:join关键字左表的表
右表:join关键字右边的表
(简单解释一下,就是字面意思)
连接查询的分类
根据需求我们可以将其分为四类:内连接,外连接,自然连接以及交叉连接。当然了,这四类也不是都有用,怎么说呢,可以将其中的一些作为扩展知识,知道有这么个连接方式就行,(日常使用率过低)
呢就首先讲讲没啥用的交叉连接吧
交叉连接:(cross join)
从一张表中循环取出每一条记录,每条记录都去另一张表中进行匹配,且匹配一定会保留(没有匹配条件,就是一定会匹配成功),而连接本身字段就会增加(也就是作为连接表的字段会保留),打个比方就是a表有3个记录,b表有5个记录,交叉连接返回就是15个记录,这样的匹配结果就是:笛卡尔积。
基本语法:
select * from 左表 cross join 右表;
mysql> select * from student cross join class;
+----+--------+------+------+------+--------+-------+------+----------+------+
| id | number | name | sex | age | height | class | id | name | room |
+----+--------+------+------+------+--------+-------+------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | 1 | java01 | a101 |
| 1 | 22 | asd | boy | 35 | 174 | 3 | 2 | java02 | a102 |
| 1 | 22 | asd | boy | 35 | 174 | 3 | 3 | python01 | b101 |
| 1 | 22 | asd | boy | 35 | 174 | 3 | 4 | python02 | b102 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | 1 | java01 | a101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | 2 | java02 | a102 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | 3 | python01 | b101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | 4 | python02 | b102 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | 1 | java01 | a101 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | 2 | java02 | a102 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | 3 | python01 | b101 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | 4 | python02 | b102 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | 1 | java01 | a101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | 2 | java02 | a102 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | 4 | python02 | b102 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | 1 | java01 | a101 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | 2 | java02 | a102 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | 3 | python01 | b101 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | 4 | python02 | b102 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | 1 | java01 | a101 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | 2 | java02 | a102 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | 3 | python01 | b101 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | 4 | python02 | b102 |
+----+--------+------+------+------+--------+-------+------+----------+------+
24 rows in set (0.00 sec)
(受限于这个代码显示,真真切切的影响观看,建议感兴趣的朋友自己敲敲,眼过千遍不如手过一遍是硬道理)我们可以发现这样的查询结果啥用也没有,它存在的价值可能就是保证了连接结构的完整性吧,可以随便连接,但无实际意义。
内连接:( [inner] join )
从左表中取出每一条记录,每条记录都会去与右表中所有记录进行匹配,但此时的匹配必须是某个条件在左表中与右表中相同(可以理解为两个表同时满足条件),这样的匹配结果最终才会保留下来,否则就不保留
基本语法:
select * from 左表 [inner] join 右表 on 左表.字段 = 右表.字段;
在这里简单对语句作一介绍;
on:表示连接条件;
条件字段:代表有相同的业务含义(比如学生表中的class字段和班级表中id字段,都代表了一个班级)
(如果左右条件字段不同名的话可以省略表名,仅作了解)
先来代码看看结果吧
mysql> select * from student inner join class on student.class = class.id;
+----+--------+------+------+------+--------+-------+------+----------+------+
| id | number | name | sex | age | height | class | id | name | room |
+----+--------+------+------+------+--------+-------+------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | 3 | python01 | b101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | 1 | java01 | a101 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | 1 | java01 | a101 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | 3 | python01 | b101 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | 4 | python02 | b102 |
+----+--------+------+------+------+--------+-------+------+----------+------+
6 rows in set (0.01 sec)
通过示例我们发现保留字段中有同名字段,当然直接查询我们都知道怎么回事,可是若是项目中查询就会出问题了,后面的字段值会覆盖掉前面的字段值,造成数据覆盖导致的数据错误,所以我们就可以用字段别名和表别名来解决这一问题。
字段别名和表别名
在查询数据的时候,不同的表有同名字段,这个时候需要加上表名才能区分,而表名太长通常可以使用别名,都是用关键字 as 来创建
字段名/表名 as 字段别名/表别名
好了,简单介绍字段别名和表别名之后我们重新查询看看结果:
mysql> select s.*,c.name as c_name,c.room from student as s inner join class as c on s.class = c.id;
+----+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+----+--------+------+------+------+--------+-------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | python01 | b101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java01 | a101 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | java01 | a101 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | python01 | b101 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | python02 | b102 |
+----+--------+------+------+------+--------+-------+----------+------+
6 rows in set (0.00 sec)
现在我们发现就没有同名字段来误导了。算是一个小技巧吧,哈哈哈(嘎笑)
值得注意的是:
- 在内连接中,可以没有连接条件,即 关键字 on 之后的内容,但这样的结果就是笛卡尔积;
- 内连接可以使用where关键字来代替,但是涉及到效率问题(在进行匹配时,on直接按照条件匹配符合条件的,没有符合条件的就不匹配;where会与所有记录进行匹配。通俗地讲就是on是看上哪个拿哪个,而where每个都看一遍)
)(示例太多影响观看,感兴趣自行实践)
外连接:(outer join)
以某张表为主,取出里面所有的记录,每条记录与另一张表进行连接,不管能不能匹配上条件,最终都会保留。(匹配的保留正确记录,不匹配的就置空NULL)
外连接的两种分类(有主表)
- left join:左外连接,也称左连接,以左表为主表,最终记录数至少不少于左表已有的记录数。
- right join:右外连接,也称右连接,以右表为主表;最终记录数至少不少于左表已有的记录数。(这里解释一下为什么最终记录数至少不少于左表已有的记录数,因为有的不满足匹配条件的记录也会被保留下来,用NULL代替)
基本语法:
select * from 左表 left/right join 右表 on 左表.字段 = 右表.字段;
需要注意的是,左右连接虽然主表不同,但是左边数据还是在左边,右表数据还是在右边,还有就是这个。
# 左外连接
mysql> select s.*,c.name as c_name,c.room from student as s left outer join class as c on s.class = c.id;
+----+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+----+--------+------+------+------+--------+-------+----------+------+
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java01 | a101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | java01 | a101 |
| 1 | 22 | asd | boy | 35 | 174 | 3 | python01 | b101 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | python01 | b101 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | python02 | b102 |
+----+--------+------+------+------+--------+-------+----------+------+
6 rows in set (0.00 sec)
# 右外连接
mysql> select s.*,c.name as c_name,c.room from student as s right outer join class as c on s.class = c.id;
+------+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+------+--------+------+------+------+--------+-------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | python01 | b101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java01 | a101 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | java01 | a101 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | python01 | b101 |
| 6 | 23 | keb | boy | 36 | 188 | 4 | python02 | b102 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | java02 | a102 |
+------+--------+------+------+------+--------+-------+----------+------+
7 rows in set (0.00 sec)
因为数据问题,我们只可以在右外连接中看见所说的 NULL置空,这就是因为以右表为主,但左表字段有不满足条件的,所以就置空返回了。
自然连接:(natural join)
自然连接就是系统自动匹配连接条件。这里系统以字段名作为匹配条件(即同名字段,有几个就匹配几个),连接之后会自动合并同名字段。
自然连接的分类
既然是自动连接,肯定要遵循系统的连接方式吧,所以自然连接可分为两大类:
- 自然内连接:左表 natural join 右表
- 自然外连接:左表 natural left/right join 右表
基本语法:
# 自然内连接
select * from 左表 natural join 右表;
# 自然外连接:
select * from 左表 natural left/right join 右表;
自然内连接:
mysql> select * from student natural join class;
Empty set (0.00 sec)
什么也没有返回啊。这可不是我们语句问题啊,对比上文查询结果可知,在学生表和班级表中,同名字段只有id和name,呢么问题来了,不是说自动按照同名字段来匹配嘛?说是这么说,当然也是这样执行的,问题在于ID字段我们两个表都是一样的,但是name字段的记录可就都不一样了,所以他什么也没匹配到,我们把字段名改了之后再来看看。
mysql> alter table class change name c_name char(20) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student natural join class;
+----+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+----+--------+------+------+------+--------+-------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | java01 | a101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java02 | a102 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | python02 | b102 |
+----+--------+------+------+------+--------+-------+----------+------+
4 rows in set (0.00 sec)
当我们改变字段名字后,我们就可以看到以同名字段id为条件就可以查询到对应结果,之前无结果就是因为同名字段name中记录不相匹配导致的匹配失败,而内连接不会保留匹配失败的记录。
自然外连接
# 自然左外连接:
mysql> select * from student natural left join class;
+----+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+----+--------+------+------+------+--------+-------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | java01 | a101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java02 | a102 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | python02 | b102 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | NULL | NULL |
| 6 | 23 | keb | boy | 36 | 188 | 4 | NULL | NULL |
+----+--------+------+------+------+--------+-------+----------+------+
6 rows in set (0.00 sec)
# 自然右外连接
mysql> select * from student natural right join class;
+------+----------+------+--------+------+------+------+--------+-------+
| id | c_name | room | number | name | sex | age | height | class |
+------+----------+------+--------+------+------+------+--------+-------+
| 1 | java01 | a101 | 22 | asd | boy | 35 | 174 | 3 |
| 2 | java02 | a102 | 33 | qwe | girl | 33 | 181 | 1 |
| 3 | python01 | b101 | 11 | ert | boy | 35 | 175 | 3 |
| 4 | python02 | b102 | 44 | rty | girl | 26 | 179 | 1 |
+------+----------+------+--------+------+------+------+--------+-------+
4 rows in set (0.00 sec)
对比外连接返回结果我们可以看到自然外连接的返回结果中主表返回结果在前,即左右两边数据跟随主表的变化而变化(主表为左表,则左表数据在前;主表为右表,则右表数据在前)
关于自然连接我们还可以通过使用同名字段作为连接条件来进行匹配,系统自动合并条件(条件字段)
基本语法:
select * from 左表 left/right/inner join 右表 using(字段名);
呢就都演示一次吧,可与上文结果做对比(一模一样不用比了)
# 自然右连接
mysql> select * from student right join class using(id);
+------+----------+------+--------+------+------+------+--------+-------+
| id | c_name | room | number | name | sex | age | height | class |
+------+----------+------+--------+------+------+------+--------+-------+
| 1 | java01 | a101 | 22 | asd | boy | 35 | 174 | 3 |
| 2 | java02 | a102 | 33 | qwe | girl | 33 | 181 | 1 |
| 3 | python01 | b101 | 11 | ert | boy | 35 | 175 | 3 |
| 4 | python02 | b102 | 44 | rty | girl | 26 | 179 | 1 |
+------+----------+------+--------+------+------+------+--------+-------+
4 rows in set (0.00 sec)
# 自然左连接
mysql> select * from student left join class using(id);
+----+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+----+--------+------+------+------+--------+-------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | java01 | a101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java02 | a102 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | python02 | b102 |
| 5 | 55 | ghj | boy | 27 | 174 | 3 | NULL | NULL |
| 6 | 23 | keb | boy | 36 | 188 | 4 | NULL | NULL |
+----+--------+------+------+------+--------+-------+----------+------+
6 rows in set (0.00 sec)
# 自然内连接
mysql> select * from student inner join class using(id);
+----+--------+------+------+------+--------+-------+----------+------+
| id | number | name | sex | age | height | class | c_name | room |
+----+--------+------+------+------+--------+-------+----------+------+
| 1 | 22 | asd | boy | 35 | 174 | 3 | java01 | a101 |
| 2 | 33 | qwe | girl | 33 | 181 | 1 | java02 | a102 |
| 3 | 11 | ert | boy | 35 | 175 | 3 | python01 | b101 |
| 4 | 44 | rty | girl | 26 | 179 | 1 | python02 | b102 |
+----+--------+------+------+------+--------+-------+----------+------+
4 rows in set (0.00 sec)
#########手动分割################
以上就是我对MySQL连接查询的一些简单介绍,望各位前辈多多指教!!!(今天的代码块观感差)