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)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)