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;

来源：https://www.runoob.com/sql/sql-join.html
https://www.runoob.com/mysql/mysql-join.html