Mysql之懵逼的一天

1.查询后端用户且性别为1的有哪些有哪些（0：前端用户 1 后端用户）

select * from easybuy_user where type=1 and sex=1

2.查询家住在海淀区创建时间为空的信息

select
	* 
from
	easybuy_user_address 
where
	address like '%海淀区%' 
	and createTime is null

3.每个用户系统允许添加多个住址，请查询出不止一个住址的用户，显示username address

select userId,count(userId) 地址数 from easybuy_user_address  GROUP BY userId having 地址数>1

 SELECT userName, COUNT(address )>1 FROM easybuy_user_address  a,  easybuy_user b WHERE 
 a.id=b.id

4.查询哪些用户没有默认地址

select userId from easybuy_user_address group by userId having not sum(isDefault)>0
 
 select distinct userId from easybuy_user_address where userId!=(select userid from easybuy_user_address where isDefault=1)
 

select
	id,
	userName,
case
		sex 
		when 0 then
		'女' 
		when 1 then
		'男' 
	end as 性别 
from
	easybuy_user 
where
	sex =0

select
	count(*) 
from
	easybuy_user 
where
	loginName = 'admin' 
	and password = 'e10adc3949ba59abbe56e057f20f883e'

select id, name, concat( '最高单价:', price ) `最高单价/最大库存` from easybuy_product where price =( select max( price ) from easybuy_product ) union
select
	id,
	name,
	concat( '最大库存:', stock ) 
from
	easybuy_product 
where
	stock =(
	select
		max( stock ) 
	from
	easybuy_product)

select
	a.id,
	a.loginName,
	a.userName,
	b.name as typeName 
from
	easybuy_user a,
	easybuy_usertype b 
where
	a.type = b.id

select
	a.id,
	a.loginName,
	a.userName,
	b.address 
from
	easybuy_user a
	left join easybuy_user_address b on a.id = b.userId 
	and address is null
--------------------------------------------------------
select
	id,
	loginName,
	userName 
from
	easybuy_user 
where
	Id <> all ( select distinct userId from easybuy_user_address )

显示出销售业绩前1名的销售人员(显示id username)的名单以及销售额显示出来

select
	b.username,
	sum( aa ) 
from
	easybuy_user as b
	inner join (
	select
		easybuy_order.userid,
		sum( easybuy_order_detail.quantity * easybuy_order_detail.cost ) as aa 
	from
		easybuy_order_detail
		inner join easybuy_order on easybuy_order_detail.orderid = easybuy_order.id 
	group by
		easybuy_order_detail.orderid 
	) as a on a.userid = b.id 
group by
	a.userid


-----------------------------------------------------------------------
select
	c.username,
	sum( b.cost * b.quantity ) 销售总额 
from
	easybuy_order a
	inner join easybuy_order_detail b on a.id = b.orderid
	inner join easybuy_user c on a.userid = c.id 
group by
	userid 
order by
	销售总额 desc 
	limit 1