多表联合查询
- 内连接
- 左链接
- 右链接
- 子查询
1在多表链接中只有一个主表其他的都是子表
1内链接
- where 链接条件 （这是一个隐性内连接）
- inner join on
准备数据
1准备dog表
create table dog(
id int(11)primary key auto_increment,
dogname varchar(20) not null default "旺财",
dogsex enum("1","0") default 1,
dogmasterid int(11)
);
2 准备dogmaster表
create table dogmaster(
uid int(11) primary key,
mname varchar(20) not null,
salary int(9) unsigned default 1500,
jobnum int(4) unsigned not null,
Depid int(4) unsigned not null
)
3 准备公司表
create table com(
cjobnum int(4) unsigned not null,
cdepid int(4) unsigned not null,
cdepname varchar(16) not null,
bonus int(8) unsigned
) engine = innodb charset = utf8mb4
1.1构建dog表数据
insert into dog(dogname,dogsex,dogmasterid) values("灰灰","1",100167),("小黑","0",100168),("小紫","0",100169),("云安","1",100169),("于山","0",100670),("开饭","0",20081),("米啾","1",20081),("葡萄","0",20081);
+----+---------+--------+-------------+
| id | dogname | dogsex | dogmasterid |
+----+---------+--------+-------------+
| 1 | 灰灰 | 1 | 100167 |
| 2 | 小黑 | 0 | 100168 |
| 3 | 小紫 | 0 | 100169 |
| 4 | 云安 | 1 | 100169 |
| 5 | 于山 | 0 | 100670 |
| 6 | 开饭 | 0 | 20081 |
| 7 | 米啾 | 1 | 20081 |
| 8 | 葡萄 | 0 | 20081 |
+----+---------+--------+-------------+
1.2构建dogmaster表数据
insert into dogmaster VALUEs
(100167,"赵敏",4700,1379,10),
(100168,"河东君",3725,1380,20),
(100169,"徐霞客",3000,1381,30),
(100670,"云雾",3000,1382,20),
(20081,"鱼幼薇",7000,1383,20)
+--------+--------+--------+--------+-------+
| uid | mname | salary | jobnum | Depid |
+--------+--------+--------+--------+-------+
| 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 100167 | 赵敏 | 4700 | 1379 | 10 |
| 100168 | 河东君 | 3725 | 1380 | 20 |
| 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 100670 | 云雾 | 3000 | 1382 | 20 |
+--------+--------+--------+--------+-------+
1.3构建公司表数据
INSERT into com VALUES
(1379,10,"金庸部门",3333),
(1380,20,"秦淮八绝",6578),
(1381,30,"自由摸鱼人",75),
(1382,20,"秦淮八绝",7755),
(1383,20,"秦淮八绝",9735)
+---------+--------+------------+-------+
| cjobnum | cdepid | cdepname | bonus |
+---------+--------+------------+-------+
| 1379 | 10 | 金庸部门 | 3333 |
| 1380 | 20 | 秦淮八绝 | 6578 |
| 1381 | 30 | 自由摸鱼人 | 75 |
| 1382 | 20 | 秦淮八绝 | 7755 |
| 1383 | 20 | 秦淮八绝 | 9735 |
+---------+--------+------------+-------+
可以直接复制1.1where连接
- 语法是 表1，表2。。。。表n where 连接条件
- 那张表在前面取决你的from 的表顺序
1 dog 和dogmaster链接
select * from dog,dogmaster where dog.dogmasterid = dogmaster.uid;
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
| id | dogname | dogsex | dogmasterid | uid | mname | salary | jobnum | Depid |
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
| 1 | 灰灰 | 1 | 100167 | 100167 | 赵敏 | 4700 | 1379 | 10 |
| 2 | 小黑 | 0 | 100168 | 100168 | 河东君 | 3725 | 1380 | 20 |
| 3 | 小紫 | 0 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 4 | 云安 | 1 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 5 | 于山 | 0 | 100670 | 100670 | 云雾 | 3000 | 1382 | 20 |
| 6 | 开饭 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 7 | 米啾 | 1 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 8 | 葡萄 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
2 三张表一起链接
首先我们来理一下想要的效过 我想要
公司表——》狗主人表——》狗表
那么from 后面顺序就应该是和上面的结果一样
select * from com,dogmaster as dm,dog where com.cjobnum = dm.jobnum and dm.uid = dog.dogmasterid;
结果为 如果不喜欢重复字段可以自己加筛选条件过滤结果集
+---------+--------+------------+-------+--------+--------+--------+--------+-------+----+---------+--------+-------------+
| cjobnum | cdepid | cdepname | bonus | uid | mname | salary | jobnum | Depid | id | dogname | dogsex | dogmasterid |
+---------+--------+------------+-------+--------+--------+--------+--------+-------+----+---------+--------+-------------+
| 1379 | 10 | 金庸部门 | 3333 | 100167 | 赵敏 | 4700 | 1379 | 10 | 1 | 灰灰 | 1 | 100167 |
| 1380 | 20 | 秦淮八绝 | 6578 | 100168 | 河东君 | 3725 | 1380 | 20 | 2 | 小黑 | 0 | 100168 |
| 1381 | 30 | 自由摸鱼人 | 75 | 100169 | 徐霞客 | 3000 | 1381 | 30 | 3 | 小紫 | 0 | 100169 |
| 1381 | 30 | 自由摸鱼人 | 75 | 100169 | 徐霞客 | 3000 | 1381 | 30 | 4 | 云安 | 1 | 100169 |
| 1382 | 20 | 秦淮八绝 | 7755 | 100670 | 云雾 | 3000 | 1382 | 20 | 5 | 于山 | 0 | 100670 |
| 1383 | 20 | 秦淮八绝 | 9735 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 | 6 | 开饭 | 0 | 20081 |
| 1383 | 20 | 秦淮八绝 | 9735 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 | 7 | 米啾 | 1 | 20081 |
| 1383 | 20 | 秦淮八绝 | 9735 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 | 8 | 葡萄 | 0 | 20081 |
+---------+--------+------------+-------+--------+--------+--------+--------+-------+----+---------+--------+-------------+
3我不想要这么多字段，我只想要每个员工养狗狗的只数
select cjobnum,mname 员工名,count(dogname) 狗的只数 from com,dogmaster as dm,dog where com.cjobnum = dm.jobnum and dm.uid = dog.dogmasterid group by cjobnum;
+---------+--------+----------+
| cjobnum | 员工名 | 狗的只数 |
+---------+--------+----------+
| 1379 | 赵敏 | 1 |
| 1380 | 河东君 | 1 |
| 1381 | 徐霞客 | 2 |
| 1382 | 云雾 | 1 |
| 1383 | 鱼幼薇 | 3 |
+---------+--------+----------+
还想筛选自己晒辣不会在问我辣。1.2inner join on连接
- 语法是 from 表1 inner join 表2 on 条件[inner join 表3 on 条件]
- 所有这玩意感觉都一样。就是以哪个表为主
1 dog 和dogmaster链接
select * from dog inner join dogmaster as dm on dog.dogmasterid = dm.uid;
结果和上面相同 喜欢用哪个就用哪个
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
| id | dogname | dogsex | dogmasterid | uid | mname | salary | jobnum | Depid |
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
| 1 | 灰灰 | 1 | 100167 | 100167 | 赵敏 | 4700 | 1379 | 10 |
| 2 | 小黑 | 0 | 100168 | 100168 | 河东君 | 3725 | 1380 | 20 |
| 3 | 小紫 | 0 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 4 | 云安 | 1 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 5 | 于山 | 0 | 100670 | 100670 | 云雾 | 3000 | 1382 | 20 |
| 6 | 开饭 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 7 | 米啾 | 1 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 8 | 葡萄 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
2 三张表一起链接
select * from com inner join dogmaster as dm on com.cjobnum = dm.jobnum inner join dog on dog.dogmasterid = dm.uid;
+---------+--------+------------+-------+--------+--------+--------+--------+-------+----+---------+--------+-------------+
| cjobnum | cdepid | cdepname | bonus | uid | mname | salary | jobnum | Depid | id | dogname | dogsex | dogmasterid |
+---------+--------+------------+-------+--------+--------+--------+--------+-------+----+---------+--------+-------------+
| 1379 | 10 | 金庸部门 | 3333 | 100167 | 赵敏 | 4700 | 1379 | 10 | 1 | 灰灰 | 1 | 100167 |
| 1380 | 20 | 秦淮八绝 | 6578 | 100168 | 河东君 | 3725 | 1380 | 20 | 2 | 小黑 | 0 | 100168 |
| 1381 | 30 | 自由摸鱼人 | 75 | 100169 | 徐霞客 | 3000 | 1381 | 30 | 3 | 小紫 | 0 | 100169 |
| 1381 | 30 | 自由摸鱼人 | 75 | 100169 | 徐霞客 | 3000 | 1381 | 30 | 4 | 云安 | 1 | 100169 |
| 1382 | 20 | 秦淮八绝 | 7755 | 100670 | 云雾 | 3000 | 1382 | 20 | 5 | 于山 | 0 | 100670 |
| 1383 | 20 | 秦淮八绝 | 9735 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 | 6 | 开饭 | 0 | 20081 |
| 1383 | 20 | 秦淮八绝 | 9735 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 | 7 | 米啾 | 1 | 20081 |
| 1383 | 20 | 秦淮八绝 | 9735 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 | 8 | 葡萄 | 0 | 20081 |
+---------+--------+------------+-------+--------+--------+--------+--------+-------+----+---------+--------+-------------+
#和上面一样 筛选自己写啊我十几岁我好累2左右连接
- 左连接 主表 left join 子表 on 连接条件
- 右连接 主表 left join 子表 on 连接条件
select * 的时候主表的数据项全部显示 如果子表没有该数据则会自动添加一个万用链接补齐该项（null）
新增一条数据以查看结果
insert into dog(dogname,dogmasterid) value("蓝狗",7563)
1左连接 即以主表为主
select * from dog left join dogmaster on dog.dogmasterid = dogmaster.uid;
因为蓝狗的主人id右表没有所以自动添加一个万用链接
结果如下
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
| id | dogname | dogsex | dogmasterid | uid | mname | salary | jobnum | Depid |
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
| 6 | 开饭 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 7 | 米啾 | 1 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 8 | 葡萄 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 1 | 灰灰 | 1 | 100167 | 100167 | 赵敏 | 4700 | 1379 | 10 |
| 2 | 小黑 | 0 | 100168 | 100168 | 河东君 | 3725 | 1380 | 20 |
| 3 | 小紫 | 0 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 4 | 云安 | 1 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 5 | 于山 | 0 | 100670 | 100670 | 云雾 | 3000 | 1382 | 20 |
| 9 | 蓝狗 | 1 | 7563 | NULL | NULL | NULL | NULL | NULL |
+----+---------+--------+-------------+--------+--------+--------+--------+-------+
2 以右表为主连接
因为右边的主表不包含那一项，所以就算你左边有也不显示
select * from dog right join dogmaster on dog.dogmasterid = dogmaster.uid;
+------+---------+--------+-------------+--------+--------+--------+--------+-------+
| id | dogname | dogsex | dogmasterid | uid | mname | salary | jobnum | Depid |
+------+---------+--------+-------------+--------+--------+--------+--------+-------+
| 1 | 灰灰 | 1 | 100167 | 100167 | 赵敏 | 4700 | 1379 | 10 |
| 2 | 小黑 | 0 | 100168 | 100168 | 河东君 | 3725 | 1380 | 20 |
| 3 | 小紫 | 0 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 4 | 云安 | 1 | 100169 | 100169 | 徐霞客 | 3000 | 1381 | 30 |
| 5 | 于山 | 0 | 100670 | 100670 | 云雾 | 3000 | 1382 | 20 |
| 6 | 开饭 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 7 | 米啾 | 1 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
| 8 | 葡萄 | 0 | 20081 | 20081 | 鱼幼薇 | 7000 | 1383 | 20 |
+------+---------+--------+-------------+--------+--------+--------+--------+-------+3子查询
- any 大于子查询结果中某个结果的值
- all 大于子查询结果中的所有值
- union 并集 可以去重完全啊不一样的
- union all 把表中数据都罗列出来
- union和union all 用在两个select 语句之间
select * from dog where id> all (select id from dog where dogname in ("灰灰","米啾","于山","开饭"));
结果如下
+----+---------+--------+-------------+
| id | dogname | dogsex | dogmasterid |
+----+---------+--------+-------------+
| 8 | 葡萄 | 0 | 20081 |
| 9 | 蓝狗 | 1 | 7563 |
+----+---------+--------+-------------+
SELECT dog.dogmasterid,dog.dogname FROM dog UNION all SELECT mname,uid from dogmaster;
+-------------+---------+
| dogmasterid | dogname |
+-------------+---------+
| 100167 | 灰灰 |
| 100168 | 小黑 |
| 100169 | 小紫 |
| 100169 | 云安 |
| 100670 | 于山 |
| 20081 | 开饭 |
| 20081 | 米啾 |
| 20081 | 葡萄 |
| 7563 | 蓝狗 |
| 鱼幼薇 | 20081 |
| 赵敏 | 100167 |
| 河东君 | 100168 |
| 徐霞客 | 100169 |
| 云雾 | 100670 |
+-------------+---------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
