数据记录筛选:
sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
多条件:where 字段名1=字段值1 and 字段名2=字段值2
或条件:where 字段名1=字段值1 or 字段名2=字段值2
不等于:!=
sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]"
sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 in ('值1','值2','值3')"
sql="select * from 数据表 where 字段名 between 值1 and 值2"
条件查询:
1.or 与and ,and是优先执行
例:查询城市为上海,学历可以是本科或者工作经历是1-3年
SELECT * FROM data.datanalyst
where city = "上海" and (education ="本科" or workYear = "1-3年")
如果不加括号,那么会先执行上海and本科,在执行or
Group by
SELECT city, count(positionId) FROM data.datan group by city 直接写*也可以,但是部分会报错
SELECT city, count(positionId) ,count(distinct companyId) FROM datan group by city 去重
SELECT city, education,count( education) from datan group by city,education 多条件分组
SELECT city,count(city) FROM datan group by city having count(positionId)>=100 having:针对group by后的筛选
SELECT city,count(city) FROM datan group by city having count(if(industryField like '%电子商务%',1,null))>=50 count与if的组合应用,count只计数1,不计数null
SELECT
city,count(city), count(if(industryField like '%电子商务%',1,null)), count(if(industryField like '%电子商务%',1,null))/count(city)
FROM datan group by city 计算各城市电子商务岗位占比
SELECT
city,count(city), count(if(industryField like '%电子商务%',1,null)), count(if(industryField like '%电子商务%',1,null))/count(city)
FROM datan group by city having count(if(industryField like '%电子商务%',1,null))>=10
order by count(if(industryField like '%电子商务%',1,null))
优化:
SELECT
city,count(city), count(if(industryField like '%电子商务%',1,null)) as emark, count(if(industryField like '%电子商务%',1,null))/count(city)
FROM datan group by city having emark>=10 order by emark 优化的写法不能在SELECT使用