mysql的简单的关联查询

1.新建表

create table websites(id int not null, name varchar(32), url varchar(100) not null ,alexa int not null , country varchar(32))

插入数据

insert into websites values(1,'Google',' https://www.google.cm/',1,'USA' )

insert into websites values(2,'taobao',' https://www.taobao.com/',13,'CN' )

insert into websites values(3,'cainiao',' http://www.runoob.com/',4689,'CN' )

insert into websites values(4,'weibo',' http://weibo.com/',20,'CN' )

insert into websites values(5,'Facebook',' https://www.facebook.com/',3,'USA' )

insert into websites values(7,'stackoverflow',' http://stackoverflow.com/',0,'IND' )

create table access_log(aid int not null, site_id int not null, count int, date varchar(100))

insert into access_log values(1,1,45,'2016-05-10')

insert into access_log values(2,3,100,'2016-05-13')

insert into access_log values(3,1,23,'2016-05-14')

insert into access_log values(4,2,10,'2016-05-14')

insert into access_log values(5,5,205,'2016-05-14')

insert into access_log values(6,4,13,'2016-05-15')

insert into access_log values(7,3,220,'2016-05-15')

insert into access_log values(8,5,545,'2016-05-16')

insert into access_log values(9,3,201,'2016-05-17')

insert into access_log values(10,6,111,'2016-03-19')

select * from mysql.test.websites

| 1 | Google| https://www.google.cm/ | 1 | USA |

| 2 | 淘宝 | https://www.taobao.com/| 13| CN |

| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |

| 4 | 微博 | http://weibo.com/ | 20| CN |

| 5 | Facebook | https://www.facebook.com/ | 3 | USA |

| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |

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

| aid | site_id | count | date |

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

| 1 | 1 |45 | 2016-05-10 |

| 2 | 3 | 100 | 2016-05-13 |

| 3 | 1 | 230 | 2016-05-14 |

| 4 | 2 |10 | 2016-05-14 |

| 5 | 5 | 205 | 2016-05-14 |

| 6 | 4 |13 | 2016-05-15 |

| 7 | 3 | 220 | 2016-05-15 |

| 8 | 5 | 545 | 2016-05-16 |

| 9 | 3 | 201 | 2016-05-17 |

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

2.关联查询

内连接

SELECT *

FROM Websites

INNER JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY Websites.id

左连接

SELECT Websites.name, access_log.count, access_log.date

FROM Websites

LEFT JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY access_log.count DESC

右连接

SELECT Websites.name, access_log.count, access_log.date

FROM Websites

RIGHT JOIN access_log

ON Websites.id=access_log.site_id

ORDER BY access_log.count DESC

全连接

SELECT websites.name, access_log.count, access_log.date

FROM websites

FULL OUTER JOIN access_log

ON access_log.site_id=websites.id

ORDER BY access_log.count DESC

1、用到MySQL的 FIND_IN_SET() 函数。

2、主表：recruitment_demand，子表：recruitment_jobs

需求：需要查询出主表以及关联的子表中的work_name、和user_num字段，work_name拼接起来用逗号隔开，以及统计user_num的总和。

查询语句：

结果图：

select * from 表名 where toId in(select fromId from 表名 where toId = 3) or toId = 3

或

select * from 表名 where toId in((select fromId from 表名 where toId = 3), 3)

---