MySQL语句训练（1）

1、查询项目地址address为空的创建人姓名和项目名称；
select
a.name,
b.name
from
user a,
project b
where a.id=b.user_id
and b.address ='';

select a.name,c.name1 from user a INNER JOIN (
select b.user_id,b.name as name1 from project b where b.address='') as c
ON a.id=c.user_id;

2、查询创建项目最多的用户id、姓名和项目数量
select
a.id,
a.name,
COUNT(b.user_id)
from
user a,
project b
where a.id=b.user_id
group by b.user_id
ORDER BY COUNT(b.user_id) desc limit 1;

select a.id,a.name,c.total from user a INNER JOIN (
select b.user_id,COUNT(b.user_id) as total from project b GROUP BY b.user_id ORDER BY COUNT(b.user_id) desc limit 1) as c
ON a.id=c.user_id;

 

3、查询创建时间在2015至2018年之间的项目id、项目名称和创建时间

select id,name,createtime from project where createtime between "2015-01-01" and "2018-12-31";

select id,name,createtime
from project
where substring(createtime,1,4) >= 2015
and substring(createtime,1,4) <= 2018

 

4、查询没有在天津和上海创建项目的用户id和姓名
select
a.id,
a.name
from
user a,
(select DISTINCT(b.user_id) from project b where b.address !='上海' and b.address!='天津')
as e
where
a.id=e.user_id;

select a.id,a.name from user a where a.id in (
select DISTINCT(b.user_id) from project b where b.address !='上海' and b.address!='天津');

5、查询在天津和上海都创建了项目的用户id和用户姓名

select
a.id,
a.name
FROM
user a,
(select DISTINCT c.user_id from
(select b.user_id from project b where b.address='天津' ) as c
INNER JOIN (select p.user_id from project p where p.address='上海' ) as d
on c.user_id =d.user_id
)
as e
where
a.id=e.user_id;

select a.id,a.name from user a where a.id in (
select DISTINCT(b.user_id) from project b where b.user_id in
(select b.user_id from project b where b.address ='上海') and b.address='天津');