sql常用语句

• 查询数据
  select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

查询所有数据行和列
例：select * from oss_user
说明：查询oss_user表中所有行和列

查询部分行列--条件查询
例：select moduleid,name from oss_user where name='yyk'
说明：查询表oss_user中name='yyk'的所有行，并显示moduleid,name 2列
返回值是一个数组，数组里面是对象[{moduleid : 1, name: "yyk"}]

查找中通过as修改列名
例：select name as studentname from oss_user where age=22
说明查询表oss_user年龄等于22的，并且将列名字改为studentname返回

模糊查询
例：select moduleid from oss_roledetail where roleid like '1101%
说明查询表oss_roledetail以1101开头的数据

查询聚合以后的结果
例如：我要查一段时间内门店营收总额大于1000的，可以先聚合成一个集合，然后再从集合里面进行条件查询
select storeId,storeName 
from 
( 
select 
storeId,
storeName,
sum(totalFee) 
from cashier 
where 
createTime >= startTime 
and createTime <= endTime) temp 
where temp.totalFee > 1000;

date_format(endTime,'%Y-%M-%D') as endTime 输出为2099-December-31st
date_format(endTime,'%Y-%m-%d') as endTime 输出为2099-12-31

  select 
  a.brandId,
  a.brandName,
  a.brandAddress,
  a.brandTel,
  a.brandContact,
  datediff(max(b.endtime), now()) as subDay
  from brand_info a,${allianceDbName}.ab_authtostore b
  where a.brandId = b.brandid and 
  a.bizCode = ${bizCode} 
  group by b.brandid 
  having datediff(max(b.endtime), now()) < 15;

  having可以进行分组的结果进行过滤

select * from ab_authtostore a where a.endtime in (select max(b.endtime) from ab_authtostore as b where a.storeid = b.storeid)

找ab_authtostore的数据里面每个storeid的endtime最大的完整数据

update system_visit_goup a inner join (select brandId,createTime from daily_task a where createTime = (select max(b.createTime) from daily_task as b where a.brandId = b.brandId) and createTime >= 1526918400000) c on a.brandId = c.brandId set a.modifyTime =c.createTime;

更新system_visit_goup的modifyTime为找出来的数据里面相同brandId的createTime