mysql与配偶同性_mysql 左,右,内连接

最新推荐文章于 2021-03-18 00:28:20 发布

Fl4me

最新推荐文章于 2021-03-18 00:28:20 发布

阅读量111

点赞数

文章标签： mysql与配偶同性

本文链接：https://blog.csdn.net/weixin_42138716/article/details/113942623

版权

左右连接

全相乘方法(效率很低)

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;

+------+-------+--------+-------+

+------+-------+--------+-------+

| 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;

+----------+--------+--------------------+--------+----------+

+----------+--------+--------------------+--------+----------+

| 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 ;

+----------+--------+--------------------+--------+----------+

+----------+--------+--------------------+--------+----------+

| 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;

+----------+--------------------+--------+----------+

+----------+--------------------+--------+----------+

| 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;

+--------+-------+--------+-------+

+--------+-------+--------+-------+

| 屌丝 | 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;

+--------+-------+--------+-------+

+--------+-------+--------+-------+

| 空姐 | 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;

+--------+-------+--------+-------+

+--------+-------+--------+-------+

| 空姐 | 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;

+--------+-------+--------+-------+

+--------+-------+--------+-------+

| 空姐 | 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;

+----------+--------------+---------------+--------------+------------+

+----------+--------------+---------------+--------------+------------+

| 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';

+--------------+-----------+---------------+-----------+--------------+------------+

+--------------+-----------+---------------+-----------+--------------+------------+

| 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 |

+--------------+-----------+---------------+-----------+--------------+------------+

Fl4me

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql与配偶同性_mysql 左,右,内连接

左右连接全相乘方法(效率很低)mysql> select * from test10;+------+-------+| id | sname |+------+-------+| 1 | 云彩 || 2 | 月亮 || 3 | 星星 |+------+-------+3 rows in set (0.00 sec)mysql> sel...
复制链接

扫一扫

mysql与配偶同性_mysql 左,右,内连接

“相关推荐”对你有帮助么？