SQL知识点

题目

互联网校招SQL笔试经典50题及答案解析

原文：https://zhuanlan.zhihu.com/p/53302593
原文有几个答案错误，均已改正
更正后的sql题在GitHub上：地址

45道经典SQL练习题附答案以及相关知识点说明

原文：https://blog.youkuaiyun.com/qq_36437446/article/details/81708741
GitHub上：地址

总结

HIVE SQL与SQL的区别

条件查找

case when

select 
 count(distinct user_id) as 'DAU'
,count(distinct case when (page_name='免单大转盘' and element_name='每日访问') then user_id else null end) as '进入转盘页面人数uv'
,sum(case when ad_action='广告请求' then 1 else 0 end) as '广告请求次数'
,sum(case when ad_action='广告请求成功' then 1 else 0 end) as '广告请求成功次数'
from nginx_log.event_log_parquet
where log_day='{yesterday}'  and event='AppClick'  and agency_id='2'  
'''.format(yesterday=yesterday)

with as

针对在不同条件查询数据的集合

 with T1 AS 
    (select adzone_name
    ,round(sum(cast(pub_share_pre_fee as float)*0.9),2) as `订单佣金`
    ,count(distinct trade_id) as `订单数` 
    from taolijin_taobao_order
    where DATE(tk_create_time)='{week_ago}' and tk_status!=13 
    GROUP BY adzone_name),
    
T2 AS (select case 
    when name='福利商城' then '正式服-福利商城-淘新闻' 
    when name='酷划锁屏-福利商城' then '正式服-福利商城-锁屏'
    else name end as name
    ,agency_id
    from ec_user_rds.agency_app
    where app_type=1)
    
SELECT T2.agency_id,T1.`订单佣金`,T1.`订单数` from T1,T2 WHERE T1.adzone_name=T2.name

if

if(判断条件，为真执行，为假执行)

if(cid='01',score,100)

sum if

sum函数中使用if判断条件格式为：sum(if(条件,列值,0))
注解：sum是求和函数，条件为真时，执行列值（字段名）求和也就是累加，条件为假时为0求和（当然还是0）

1.单条件判断格式，sum(if(条件字段名=值,需要计算sum的字段名,0))

2.多条件判断格式，sum(if(条件字段名>值 AND 条件字段名>值 AND 条件字段名=值,1,0))

注解：多条件判断格式整体含义为，计算满足条件的数据总数，如果满足条件，那么数据总数加1，所以1的含义为累加1

3.常见case when格式，sum(case when 条件字段名 in (范围较小值,范围较大值) then [需要计算sum的字段名] else 0 end)

count if

count函数中使用if判断条件格式为：
1.统计总数，count(if(条件字段名=值,true/列值,null))

2.统计总数去重复值，count(DISTINCT 需要计算count的字段名,if(条件字段名=值,true,null))

having count(if(cid='01',score,null))>0 and
count(if(cid='02',score,null))>0

oracle的分析函数over(Partition by…) 及开窗函数

• 分析函数over
  Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

• 开窗函数
  开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

between and

    count(if(score between 85 and 100,sid,null))/count(sid)

time

• 获取某时间段的年：year(sage)
• 获取某时间段的周：weekofyear(sage)
• 获取当前时间的周：weekofyear(curdate())
• 增加一周的时间：weekofyear(sage)=weekofyear(date_add(curdate(),interval 1 week));

存储过程

• 构造求老师课程平均分的存储过程

drop procedure if exists avg_score_by_cid;
delimiter $$
create procedure avg_score_by_cid(out parm_score int,in tname1 varchar(255)) #输入输出
begin
select avg(score) into parm_score from course inner join sc on course.cid=sc.cid inner join teacher on teacher.tid=course.tid where tname=tname1 group by course.cid;
end
;
$$

• 赋值

delimiter ;
call avg_score_by_cid(@parm_score,'张三');
select @parm_score;

• 构造求老师教的课程cid的存储过程

drop procedure if exists cid_by_tname;
delimiter $$
create procedure cid_by_tname(out parm_cid int,in tname1 varchar(255))
begin
select cid into parm_cid from course inner join teacher on course.tid=teacher.tid where tname=tname1 group by course.cid;
end
;
$$

• 赋值

delimiter ;
call cid_by_tname(@parm_cid,'张三');
select @parm_cid;

更新

replace

select * ,REPLACE(content_,'/<br>','') from school2

然后用Navicat 复制为update语句

存储过程更新语句

update sc set score=@parm_score where sc.cid=@parm_cid;
select score from sc where cid=@parm_cid;

更新某列的值

update school2 set pici_2015= '-'  where pici_2015='无数据'

import pymysql
def database():
    coon = pymysql.connect(
        host='localhost', user='root', passwd='root',
        port=3306, db='data', charset='utf8'
    )
    cur = coon.cursor()  # 建立游标
    return cur, coon

def qingxi():
    cur ,coon =database()
    cur.execute("select * from school2")
    col_name_list = [tuple[0] for tuple in cur.description][5:]
    clean_list = ['暂无招生专业', '专业暂无数据', '暂无招生人数', '暂无专业名', '无数据']
    for col_name in col_name_list:
        for value in clean_list:
            try:
                sqll = '''
                update school2 set {col_name}= "-"  where {col_name}="{value}"
                '''.format(col_name=col_name,value=value)
                print(sqll)
                cur.execute(sqll)
                coon.commit()
            except Exception as e:
                print('file',e)
                pass
if __name__ == '__main__':
    qingxi()