MySQL是一个开放源代码的关系型数据库管理系统。
下载并安装MySQL,本文以下面的MySQL数据库为主
排序
- order by xxx 升序;
- order by xxx desc 降序;
- order by tt,hh tt和hh升序
- 中文的升序、降序没有显著的规律
过滤、筛选
- where companyId=4184,选出companyId为4184的;
- where city="北京",中文的要加上引号;
- where companyId between 50 and 60,选择50到60之间的;
- where companyId in(53,62),找出53和62的;
- limit 10 前10条数据;
- !=和<>是不等于; 或者not in;
- where companyId =62 and firstType="技术",既是62又是技术,可以多个and,可以换成or(但对于in(53,34));
- and or 同时出现且没有括号,先执行and;
- where secondType like "%开发%",包含开发;
- where secondType like "前端%" ,前边是前端。
分组
group by
- select city,count(positionId) from new_schema.dataanalyst group by city,对城市分组,得出不同城市的职位数;
- select city,count(positionId),count(distinct companyId) from new_schema.dataanalyst group by city,不同城市的职位数以及不同的公司数,distinct不同的,count(positionId)和count(1)、count(*) 一样;
- select city,education,count(1) from new_schema.dataanalyst group by city,education,对城市和教育分组,得到不同城市,不同教育所需的个数;
- group by city having count(positionId)>=100,对城市进行过滤,having相当于where;
- group by city having count(if(industryField like '%电子商务%',1,null)) >= 50,嵌套函数;
- select city,count(if(industryField like '%电子商务%',industryField,null)),count(1) from new_schema.dataanalyst group by city;
- count(if(industryField like '%电子商务%',industryField,null))/count(1) 占比。
case when
与Excel中lookup(分组)类似: case when xxx then xxx else xxx .end。
跨表分析
- select * from new_schema.dataanalyst where city in (select city from new_schema.dataanalyst group by city having count(positionId)>=900)。得出的数据明细只是其中一张表的,还不完整;
- join(相当于VLOOKUP)(关联):join xxx on xxx
- select *from new_schema.dataanalyst join new_schema.company on dataanalyst.companyId = company.companyId 等号两边顺序无所谓
- select * from new_schema.dataanalyst as d left join (select * from new_schema.company where companySize = "150-500人" )as c on d.companyId = c.companyId 。
时间
- now(year,mouth,week);
- date日期“年月日”;
- date_format(paidTime,"%Y-%m") 日期改为“年月”Y完整年份2016 y 16 m几月 M英文月份;
- date_add(paidTime, interval 2 day)增加一天(周,年,月都行);
- datediff日期差值,返回天数。
其他知识
- SQL:Excel
- left 函数一致,
- locate函数与find,
- length与len,
- substry与mid,
- ceil向上取整。
练习SQL的网站:力扣https://leetcode-cn.com/