左右连接
全相乘方法（效率很低）
mysql> select * from test10; +------+-------+ | id | sname | +------+-------+ | 1 | 云彩 | | 2 | 月亮 | | 3 | 星星 | +------+-------+
3 rows in set (0.00 sec)
mysql> select * from test11; +--------+-------+ | cat_id | cname | +--------+-------+ | 95 | 猴子 | | 96 | 老虎 | +--------+-------+
2 rows in set (0.00 sec)
数据库中实现两表*的效果
mysql> select * from test10,test11; +------+-------+--------+-------+ | id | sname | cat_id | cname | +------+-------+--------+-------+ | 1 | 云彩 | 95 | 猴子 | | 1 | 云彩 | 96 | 老虎 | | 2 | 月亮 | 95 | 猴子 | | 2 | 月亮 | 96 | 老虎 | | 3 | 星星 | 95 | 猴子 | | 3 | 星星 | 96 | 老虎 | +------+-------+--------+-------+
6 rows in set (0.03 sec)
分析：
test10看成集合有三个元素
test11看成集合有两个元素
test10*test11得到新集合是有六个元素
行 两个表行数相乘
列 两个表列数相加（可以重复）
多表查询时候列名重复的情况时候，需要明确指出取得是那个表
mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name from minigoods,category limit 3; +----------+--------+--------------------+--------+----------+ | goods_id | cat_id | goods_name | cat_id | cat_name | +----------+--------+--------------------+--------+----------+ | 1 | 4 | KD876 | 1 | 手机类型 | | 4 | 8 | htcN85原装充电器 | 1 | 手机类型 | | 3 | 8 | 诺基亚原装5800耳机 | 1 | 手机类型 | +----------+--------+--------------------+--------+----------+
技巧 ：创建一个同样结构的表 create table [新表名] like [旧表名]
create table minigoods like goods;
复制一部分表的内容
mysql> insert into minigoods -> select * from goods limit 3;
取得有意义的两个表的对应 minigoods.cat_id=category.cat_id
mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name from minigoods,category where minigoods.cat_id=category.cat_id ; +----------+--------+--------------------+--------+----------+ | goods_id | cat_id | goods_name | cat_id | cat_name | +----------+--------+--------------------+--------+----------+ | 1 | 4 | KD876 | 4 | 3G手机 | | 4 | 8 | htcN85原装充电器 | 8 | 耳机 | | 3 | 8 | 诺基亚原装5800耳机 | 8 | 耳机 | +----------+--------+--------------------+--------+----------+
3 rows in set (0.00 sec)
左连接语法
a表在左，不动
b表在右，动
a表和b表通过一个关系（自己设定），来筛选a所需要的b的行
a left join b on [条件] ----条件为真，取出b的行
a left join b on [条件] 结果集也可以看成表（假设为c表），可以对其再查询
mysql> select goods_id,goods_name,cat_name -> from -> (minigoods left join category on minigoods.cat_id=category.cat_id); +----------+--------------------+----------+ | goods_id | goods_name | cat_name | +----------+--------------------+----------+ | 1 | KD876 | 3G手机 | | 4 | htcN85原装充电器 | 耳机 | | 3 | 诺基亚原装5800耳机 | 耳机 | +----------+--------------------+----------+
[minigoods left join category on minigoods.cat_id=category.cat_id 看做c表]
验证：就是后面依然可以使用where 等等过滤条件
mysql> select goods_id,goods_name,cat_name from (minigoods left join category on minigoods.cat_id=category.cat_id ) where 1 order by goods_id desc limit 2; +----------+--------------------+----------+ | goods_id | goods_name | cat_name | +----------+--------------------+----------+ | 4 | htcN85原装充电器 | 耳机 | | 3 | 诺基亚原装5800耳机 | 耳机 | +----------+--------------------+----------+
2 rows in set (0.00 sec)
可以多张表左连接，就是把结果看成表
看做一张表
{{a left join b on [条件]} left join c on [条件]}
mysql> select goods.goods_id,goods.goods_name,goods.cat_id,cat_name
-> from
-> minigoods left join category on minigoods.cat_id=category.cat_id
-> left join goods on minigoods.cat_id=4 limit 4;
+----------+--------------------+--------+----------+
| goods_id | goods_name | cat_id | cat_name |
+----------+--------------------+--------+----------+
| 1 | KD876 | 4 | 3G手机 |
| 4 | htcN85原装充电器 | 8 | 3G手机 |
| 3 | 诺基亚原装5800耳机 | 8 | 3G手机 |
| 5 | 索爱原装M2卡读卡器 | 11 | 3G手机 |
+----------+--------------------+--------+----------+左右连接的区别===================================================================================================
a left join b on 意思是查询的时候以a为基准查询
a right join b on 意思是查询的时候以b为基准查询
a left join b on 等同于 b right join a （都是以a为基准查询）
技巧：处于移植兼容性和理解上来说，最好一律使用左连接实现
create table boy( bname varchar(20), other char(1) )engine myisam charset utf8; insert into boy values ('屌丝','A'), ('李四','B'), ('王五','C'), ('高富帅','D'), ('郑七','E');
create table girl( gname varchar(20), other char(1) )engine myisam charset utf8; insert into girl values ('空姐','B'), ('大S','C'), ('阿娇','D'), ('张柏芝','D'), ('林黛玉','E'), ('宝钗','F');
取出所有男生的配偶（左连接）
select boy.*,girl.* from boy left join girl on boy.other=girl.other; +--------+-------+--------+-------+ | bname | other | gname | other | +--------+-------+--------+-------+ | 屌丝 | A | NULL | NULL | | 李四 | B | 空姐 | B | | 王五 | C | 大S | C | | 高富帅 | D | 阿娇 | D | | 高富帅 | D | 张柏芝 | D | | 郑七 | E | 林黛玉 | E | +--------+-------+--------+-------+
取出所有女生的配偶（左连接）
mysql> select girl.*,boy.* from -> girl left join boy on -> boy.other=girl.other; +--------+-------+--------+-------+ | gname | other | bname | other | +--------+-------+--------+-------+ | 空姐 | B | 李四 | B | | 大S | C | 王五 | C | | 阿娇 | D | 高富帅 | D | | 张柏芝 | D | 高富帅 | D | | 林黛玉 | E | 郑七 | E | | 宝钗 | F | NULL | NULL | +--------+-------+--------+-------+
取出所有女生的配偶（右连接，和上面左连接一致）
mysql> select girl.* ,boy.* from -> boy right join girl on -> boy.other=girl.other; +--------+-------+--------+-------+ | gname | other | bname | other | +--------+-------+--------+-------+ | 空姐 | B | 李四 | B | | 大S | C | 王五 | C | | 阿娇 | D | 高富帅 | D | | 张柏芝 | D | 高富帅 | D | | 林黛玉 | E | 郑七 | E | | 宝钗 | F | NULL | NULL | +--------+-------+--------+-------+
内连接===================================================================================================
取出有配偶的
select girl.*,boy.* from boy inner join girl on boy.other=girl.other; +--------+-------+--------+-------+ | gname | other | bname | other | +--------+-------+--------+-------+ | 空姐 | B | 李四 | B | | 大S | C | 王五 | C | | 阿娇 | D | 高富帅 | D | | 张柏芝 | D | 高富帅 | D | | 林黛玉 | E | 郑七 | E | +--------+-------+--------+-------+
内连接是左右连接的交集
（外连接是左右连接的并集，mysql不支持）可以使用用union实现
左连接应用===================================================================================================
create table match_t( match_id int primary key auto_increment, host_team_id int, guest_team_id int, match_result varchar(20), match_time date )engine myisam charset utf8; insert into match_t values (1,1,2,'2:0','2006-05-21'), (2,2,3,'1:2','2006-06-21'), (3,3,1,'2:5','2006-07-21'), (4,1,1,'3:2','2006-08-21'); create table team_t( team_id int primary key auto_increment, team_name varchar(20) )engine myisam charset utf8; insert into team_t values (1,'恒大'), (2,'国安'), (3,'申花'), (4,'大连'); mysql> select * from match_t; +----------+--------------+---------------+--------------+------------+ | match_id | host_team_id | guest_team_id | match_result | match_time | +----------+--------------+---------------+--------------+------------+ | 1 | 1 | 2 | 2:0 | 2006-05-21 | | 2 | 2 | 3 | 1:2 | 2006-06-21 | | 3 | 3 | 1 | 2:5 | 2006-07-21 | | 4 | 4 | 1 | 3:2 | 2006-08-21 | +----------+--------------+---------------+--------------+------------+ mysql> select * from team_t; +---------+-----------+ | team_id | team_name | +---------+-----------+ | 1 | 恒大 | | 2 | 国安 | | 3 | 申花 | | 4 | 大连 | +---------+-----------+
技巧 ：善与给表其别名
2006-06-21日期之后 把表中对战换成队名
select host_t.team_name,guest_t.team_name,match_result,match_time from match_t left join (team_t as host_t) on match_t.host_team_id=host_t.team_id left join (team_t as guest_t) on match_t.guest_team_id=guest_t.team_id where match_time>='2006-06-21'; +--------------+-----------+---------------+-----------+--------------+------------+ | host_team_id | team_name | guest_team_id | team_name | match_result | match_time | +--------------+-----------+---------------+-----------+--------------+------------+ | 1 | 恒大 | 2 | 国安 | 2:0 | 2006-05-21 | | 2 | 国安 | 3 | 申花 | 1:2 | 2006-06-21 | | 3 | 申花 | 1 | 恒大 | 2:5 | 2006-07-21 | | 4 | 大连 | 1 | 恒大 | 3:2 | 2006-08-21 | +--------------+-----------+---------------+-----------+--------------+------------+
以上就是mysql 左,右,内连接的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！
Apakah resolusi 1080p?
Bagaimana untuk menjalankan projek phpstudy
psrpc.dll tidak menemui penyelesaian
Kedudukan terkini sepuluh bursa teratas dalam bulatan mata wang
Sebab mengapa skrin sentuh telefon bimbit gagal
WiFi tidak menunjukkan akses kepada Internet
Pengenalan kepada protokol xmpp
Apakah fungsi
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
