连接查询
连接查询可以实现多个表的查询,当查询的字段来自不同表的时候就可以用连接查询。
连接查询又可以分为4种方式:
- 内连接查询
- 左连接查询
- 右连接查询
- 自连接查询
1. 内连接查询
使用“=、>、<、<>”等运算符根据每个表共有记录,它是两个数据的交集。
内连接查询语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明:
- inner join 就是内连接查询关键字
- on 连接查询条件
现在我们以后的数据如下:
mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+
| id | name | age | gender | height | city | addr | personId |
+----+-----------+------+--------+--------+--------+--------------+--------------------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 |
+----+-----------+------+--------+--------+--------+--------------+--------------------+
6 rows in set (0.00 sec)
这里可以增加一个字段,这个字段表示城市所在省份的简称 比如北京 简称为京 四川简称为川,重庆简称为渝。
mysql> alter table personinfo add proabb varchar(4) not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| id | name | age | gender | height | city | addr | personId | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
6 rows in set (0.00 sec)
现在我们将成都 所在省的简称设置为川,重庆简称设置为渝
mysql> update personinfo set proabb='川' where city = '成都';
mysql> update personinfo set proabb='渝' where city = '重庆';
mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| id | name | age | gender | height | city | addr | personId | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | 川 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | 川 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | 川 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | 川 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | 渝 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | 渝 |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
6 rows in set (0.00 sec)
现在再建立一个表,表示每个省份的简称
mysql> create table province(id int unsigned primary key auto_increment not null,name varchar(40) not null, proabb varchar(4) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc province;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(40) | NO | | NULL | |
| proabb | varchar(4) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
现在我们添加这个省的信息
mysql> insert into province(name,proabb) values('北京','京'),('天津','津'),('四川','川'),('重庆','渝');
mysql> select * from province;
+----+--------+--------+
| id | name | proabb |
+----+--------+--------+
| 1 | 北京 | 京 |
| 2 | 天津 | 津 |
| 3 | 四川 | 川 |
| 4 | 重庆 | 渝 |
+----+--------+--------+
4 rows in set (0.00 sec)
现在使用内查询 关联 两个表
mysql> select * from personinfo inner join province on personinfo.proabb = province.proabb;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
| id | name | age | gender | height | city | addr | personId | proabb | id | name | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | 川 | 3 | 四川 | 川 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | 川 | 3 | 四川 | 川 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | 川 | 3 | 四川 | 川 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | 川 | 3 | 四川 | 川 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | 渝 | 4 | 重庆 | 渝 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | 渝 | 4 | 重庆 | 渝 |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
6 rows in set (0.00 sec)
现在只查看省份简写为‘川'的所有人员,只显示名字 ,性别、年龄、身份证号,城市、省份
mysql> select personinfo.name as '名字',personinfo.age as '年龄',personinfo.gender as '性别',personinfo.personId as '身份证号',personinfo.city as '城市',province.proabb as '省份' \
-> from personinfo inner join province on personinfo.proabb = province.proabb where personinfo.proabb = '川';
+-----------+--------+--------+--------------------+--------+--------+
| 名字 | 年龄 | 性别 | 身份证号 | 城市 | 省份 |
+-----------+--------+--------+--------------------+--------+--------+
| xiaozhang | 20 | 男 | 510131198605136412 | 成都 | 川 |
| xiaozhang | 20 | 男 | 510131198605136413 | 成都 | 川 |
| xiaowang | 22 | 男 | 510131198605136414 | 成都 | 川 |
| xiaowang | 30 | 男 | 510131198605136415 | 成都 | 川 |
+-----------+--------+--------+--------------------+--------+--------+
4 rows in set (0.00 sec)
2. 左连接查询
查询的结果集包括SQL语句中左表的所有行,右表中匹配的行。如果左表的某行在右表中没有匹配行,则用null表示。如果左表数据,在右表中有多行匹配,则查询结果左表为多行显示。
左连接查询语法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明:
- left join 就是内连接查询关键字
- on 连接查询条件
现在我们以后的数据如
首先我们在personinfo表中添加2个数据 其省份信息没有在province表中
mysql> insert into personinfo(name,age,gender,height,city,addr,personId,proabb) value('xiaoxu',28,'男',155,'贵阳','贵阳南城',510131199204105870,'贵');
mysql> insert into personinfo(name,age,gender,height,city,addr,personId,proabb) value('xiaozhou',29,'女',160,'桂林','桂林北城',510131199204105815,'云');
mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| id | name | age | gender | height | city | addr | personId | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | 川 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | 川 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | 川 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | 川 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | 渝 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | 渝 |
| 7 | xiaoxu | 28 | 男 | 155.00 | 贵阳 | 贵阳南城 | 510131199204105870 | 贵 |
| 9 | xiaozhou | 29 | 女 | 160.00 | 桂林 | 桂林北城 | 510131199204105815 | 云 |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
8 rows in set (0.00 sec)
使用左连接,可以看到,'贵' 、'云' 在province表中显示的是 null
mysql> select * from personinfo left join province on personinfo.proabb = province.proabb;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+------+--------+--------+
| id | name | age | gender | height | city | addr | personId | proabb | id | name | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+------+--------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | 川 | 3 | 四川 | 川 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | 川 | 3 | 四川 | 川 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | 川 | 3 | 四川 | 川 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | 川 | 3 | 四川 | 川 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | 渝 | 4 | 重庆 | 渝 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | 渝 | 4 | 重庆 | 渝 |
| 7 | xiaoxu | 28 | 男 | 155.00 | 贵阳 | 贵阳南城 | 510131199204105870 | 贵 | NULL | NULL | NULL |
| 9 | xiaozhou | 29 | 女 | 160.00 | 桂林 | 桂林北城 | 510131199204105815 | 云 | NULL | NULL | NULL |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+------+--------+--------+
8 rows in set (0.00 sec)
3. 右连接查询
右连接与左连接正好相反,查询的结果集包括SQL语句中右表的所有行,左表中匹配的行。如果右表的某行在左表中没有匹配行,则用null表示。如果右表数据,在左表中有多行匹配,则查询结果右表为多行显示。
mysql> select * from personinfo right join province on personinfo.proabb = province.proabb;
+------+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
| id | name | age | gender | height | city | addr | personId | proabb | id | name | proabb |
+------+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
| 1 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136412 | 川 | 3 | 四川 | 川 |
| 2 | xiaozhang | 20 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136413 | 川 | 3 | 四川 | 川 |
| 3 | xiaowang | 22 | 男 | 172.00 | 成都 | 成都高新 | 510131198605136414 | 川 | 3 | 四川 | 川 |
| 4 | xiaowang | 30 | 男 | 170.00 | 成都 | 成都高新 | 510131198605136415 | 川 | 3 | 四川 | 川 |
| 5 | xiaozhao | 25 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136416 | 渝 | 4 | 重庆 | 渝 |
| 6 | xiaozhao | 26 | 女 | 165.00 | 重庆 | 重庆高新 | 510131198605136417 | 渝 | 4 | 重庆 | 渝 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 北京 | 京 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2 | 天津 | 津 |
+------+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
8 rows in set (0.00 sec)
4. 自连接
左表和右表是同一个表,根据连接查询条件查询两个表中的数据。
语法格式和内连接相似,只是将内连接 两个表名改成一样的
select 字段 from 表1 as table1 inner join as table2 on table1.字段1 = table2.字段2