4h搞定数据库编程常用语法以及牛客题目

select device_id,gender,age,university,gpa

from user_profile

where university in ('北京大学','复旦大学','山东大学')

注意： is ≠in

什么时候用 is,表达bool类型的时候

2.过滤空值

select device_id,gender,age,university

    from user_profile

        where age is not NULL

注意：不能写成age！=NULL

3.结果去重

select distinct university from user_profile

4. 列重命名，只取前两行

 select device_id

from user_profile

as user_infos_example

limit 2

5.题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

select device_id,gpa,age

from user_profile

order by gpa asc,age asc

注意：降序 desc

6. 现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

select device_id,gender,age,university,gpa

from user_profile

where (gpa>3.5 and university='山东大学') or (gpa>3.8 and university='复旦大学')

注意：gpa>3.5 and university is'山东大学' 错误

7. 模糊查询like

8. 查看学校名称中含北京的用户

select device_id,age,university

from user_profile

where university like '%北京%'

9. 查找gpa最高

select max(gpa)

from user_profile

where university in('复旦大学')

11.分组group by

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

select gender,university,

    count(gender) as user_num,

    avg(active_days_within_30) as avg_active_day,

    avg(question_cnt) as avg_question_cnt

from user_profile

group by university,gender

13. 聚合之后筛选having

现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

select university,

    avg(question_cnt) as avg_question_cnt,

    avg(answer_cnt) as avg_answer_cnt

from user_profile

group by university

having avg_question_cnt<5 or avg_answer_cnt<20

注意：聚合函数结果作为筛选条件时，不能用where，而是用having语法配合重命名即可；

14. 聚合之后排序

现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据

select university,

    avg(question_cnt) as avg_question_cnt

from user_profile

group by university

order by avg_question_cnt asc

15.多表查询-子查询

运营想查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

select device_id,question_id,result

from question_practice_detail

where device_id in(

    select device_id

    from user_profile

    where university='浙江大学'

)

group by……

16. 多表查询-自然连接 JOIN

select university,

    round(count(qpd.question_id)/count(distinct qpd.device_id),4)

    as avg_answer_cnt

from user_profile as uf join question_practice_detail as qpd    ///   table1 join table2

    on uf.device_id=qpd.device_id

group by university

注意：第二行 qpd.device_id是谁的device_id，如果不加，数据库不知道是哪个表的

17. 运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

select university,difficult_level,

    round(count(qpd.question_id)/count(distinct qpd.device_id),4) //必须要加distinct

    as avg_answer_cnt

from user_profile as up

join question_practice_detail as qpd

on up.device_id=qpd.device_id

join question_detail as qd

on qd.question_id=qpd.question_id

group by university,difficult_level

18. 运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

select university,difficult_level,

    round(count(qpd.question_id)/count(distinct qpd.device_id),4)

    as avg_answer_cnt

from user_profile as up

join question_practice_detail as qpd

on up.device_id=qpd.device_id

join question_detail as qd

on qpd.question_id=qd.question_id

where university='山东大学'

group by difficult_level

19. 现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

select device_id,gender,age,gpa

from user_profile

where university='山东大学'

union all   //all不能少

select device_id,gender,age,gpa

from user_profile

where gender='male'

注意：不用where or:

第一or去重

第二实际应尽量避免在where子句中使用or来连接条件。使用or可能会使索引失效，从而全表扫描。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效

20.union 和union all

（1）union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct;

union all: 对两个结果集进行并集操作,, 不管是不是重复;

（2）union: 会对获取的结果进行排序操作

union all: 不会对获取的结果进行排序操作

21.时间函数

现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

day(),year(),month()

select

day(date) as day,

count(question_id) question_cnt  //这里没加distinct，因为练习题id可能重复

from question_practice_detail

where year(date)=2021 and month(date)=8

group by day

22．Case

…………

CASE 测试表达式

WHEN 简单表达式1 THEN 结果表达式1

WHEN 简单表达式2 THEN 结果表达式2 …

WHEN 简单表达式n THEN 结果表达式n

[ ELSE 结果表达式n+1 ]

END

…………….

Eg:

23．现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

select

case

when age<25 or age is NULL then '25岁以下'

when age>=25 then '25岁及以上'

end age_cut,

count(*) as number

from user_profile

group by age_cut

24. 现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

select device_id,gender,

case

when age<20 then '20岁以下'

when age>=20 and age<=24 then '20-24岁'

when age>=25 then '25岁及以上'

else '其他'

end as age_cut   //as可以省略

from user_profile

25.字符串截取

转自https://www.cnblogs.com/mqxs/p/7380933.html

26.

现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

方法一：like

select if(profile like '%female','female','male') as gender,

count(*) as number

from user_submit

group by gender

方法二：SUBSTRING_INDEX的写法

select substring_index(profile,',',-1) as gender,

count(*) as number

from user_submit

group by gender

27. 对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

select device_id,

    substring_index(blog_url,'/',-1) as user_name

from user_submit

28．现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

select

    substring_index(substring_index(profile,',',-2),',',1) as age,

    count(device_id) as number

from user_submit

group by age

29.

现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

注意：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的；

方法一：相关子查询

select

device_id,university,gpa

from user_profile as up

where gpa=(

    select min(gpa)

    from user_profile

    where university=up.university

)

order by university

利用相关子查询，把每个学校的最低gpa当作查询条件，去找出每个学校的gpa最低的同学。因为每个学校只有一个gpa最低的同学，所以最后出来的结果不需要再用group by，用order by排序就好。看代码！！

在子查询中，我们利用到了主查询的表，WHERE university = u.university 这个条件使得mysql去主表得每一行进行查询，比如第一行是2138的北京大学的同学，那么子查询会找出所有北京大学的同学，并且找出其中最低得gpa，如果他是最低的那个就留下，不是就下一个。以此类推找出所有大学的最低gpa的同学，最后排序得最终结果。

方法二：窗口函数

https://zhuanlan.zhihu.com/p/92654574

select *,

   rank() over (partition by 班级

                 order by 成绩 desc) as ranking

select

device_id,university,gpa

from(select *,

    rank() over (partition by university

                order by gpa asc) as rk

     from user_profile

    ) as a

where a.rk=1

外层嵌套要用到内层，内层表必须需要给出声明 as a，不能省略

30. 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

子查询：

select

count(distinct device_id) as did_cnt,

count(question_id) as question_cnt

from(select *

     from  question_practice_detail

     where year(date)=2021 and month(date)=8) as a

--------------------------------------补充

插删改操作:http://t.csdn.cn/EAYO2

1.insert 插入

(1)在“Customers”表中插入一条新记录：

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

(2)仅在指定列中插入数据

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');