无眠：数据分析面试必备——SQL你准备好了吗？
一、最基本
1.选择某列
select a from table_1;
2.表连接：多张表中，想选取多个字段？
select a.id,a.age,b.sex
from (select id,age from table-1)a
join
(select id , sex from table_2) b
on a.id=b.id;
知识点：join
知识点：union和join的区别
二、最常用(多重组合)
1、如果有千万用户数据，想知道有多少去重的用户数？—— 去重 distinct
罗列不同的id
select distinct id from table_1;
统计不同的id个数
select count(distinct id) from table_1;
优化后
select count(*) from
(select distinct id from table_1) tb;
2、想分性别进行统计，看看男女各多少？—— 聚合函数和groupby
#统计不同性别(男、女)中，不同id的个数
select count(distinct id) from table_1
group by sex;
#统计最大最小平均年龄
select max(age),min(age),avg(age) from table_1
group by id;
易错点：直接写select count(*) from table_1 group by sex;没有对相同的id进行去重，非常重要！！！！！！！！！！！！
3、只想查看A公司的男女人数数据？—— 筛选 where/having
#统计A公司的男女人数？
select count(distinct id) from table_1
where company='A'
group by sex;
#统计各公司的男性平均年龄，并且仅保留平均年龄在30岁以上的公司
select company, avg(age) from table_1
where sex='M'
group by company
having avg(age)>30;
4、希望查询结果从高到低/从低到高排序？—— 排序 orderby
#按年龄全局倒序排序取最年迈的签10个人
select id, age from table_1
order by age desc
limit 10;将数值型的变量转化为分类型的变量？ —— case when 条件函数
5、将数值型的变量转化为分类型的变量？ —— casewhen 条件函数
此种情况常见于将某列进行分类或者分组
#收入分组
select id ,
case when cast(salary as float) <50000 then '0-5万',
when cast(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10万',
when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20万'
when CAST(salary as float)>200000 then '20万以上'
else null end
from table_1;
6、字符串的相关常见函数concat(a,b.....)返回的是a和b的连在一起的字符串
select concat('www','ooo') from a;
wwwooosplit(str,regex)用于将string类型按regex提取，分割后转化成array序列
以regex为分隔符分隔str,并返回array序列
substr(str,0,len) 截取字符串从0位开始的长度为len个字符。
select substr('abcde',3,2) from a；
cd
三、基础进阶
1、不想全局排序，需要分组排序？—— row_number()
select *,
row_number()over(order by salary desc) as rn1 --全局按照salary下降排序row_number()over(partition by deptid order by salary desc) as rn2 --分组按照salary下降排序from table1;
2、想要获取top10%的值？—— percentile 百分位函数、ntile(n)函数也可以做到
3、想要对时间字段进行操作？—— 时间函数
to_date('datetime') ;--转化为日期函数date_format('datetime’,'YYYY-mm-dd')---日期格式化datediff('2016-12-30','2016-12-29')-----日期相差天数dateadddatesubfrom_unixtime()时间戳转化为日期unixtime()日期转化为时间戳
四、常见笔试/面试题:
学生表：SID，Sname,Sage,Ssex
课程表：CID，Cname，TID
教师表：TID,Tname
成绩表：SID， CID， score
#1.2查询同时存在01课程和02课程的学生姓名
select Sname from student a
inner join
(select SID from score where CID='01') b
on a.SID= b.SID
inner join
(select SID from score where CID='02') c
on a.SID=c.SID;
select Sname from student a
inner join score b
on a.SID=b.SID AND b.CID='01'
inner join score c
on a.SID=c.SID AND c.CID='02';
#8.查询没有学过张三老师授课的同学的信息
select * from student
where SID
not in
(select SID from score a
inner join course b
on a.CID=b.CID
INNER JOIN teacher c
on b.TID=c.TID
where Tname='张三');
#15查出两门及以上不及格课程的同学学号姓名及其平均成绩
select a.SID,a.Sname,avg(b.score) from student a
inner join score b
on a.SID=b.SID
where a.SID IN
(SELECT SID FROM SCORE where score<60 group by SID having count(*)>=2)
group by a.SID;
