SQL入门篇

SQL入门篇

这篇是我在牛客网上做SQL题时记录的，到真正做题时才发现对数据库的知识已经忘了好多了🤣🤣

温故而知新，记录自己做不出来的题，方便常常回顾。

SQL3 查询结果去重

主要是两种思路：

1
2
select university from user_profile group by university;
select distinct university from user_profile;

SQL4 查询结果限制返回行数

主要使用LIMIT关键字，它可以接两个参数，也可以接一个：

1
2
LIMIT m,n	返回从第m行开始的n条记录
LIMIT　n		返回前n条记录
1
select device_id from user_profile order by id limit 2;

SQL8 查询某个年龄段的用户信息

可以只用between and关键字但是不同的数据库其含义有些许不同，在我测试的MySQL中是左右都是闭区间。

当然可以使用and来连接两个条件。

SQL10 用where过滤空值

1
2
3
4
5
SELECT device_id,gender,age,university 
from user_profile 
where age!=''
# where age <> ''
# where age is not null

SQL13 where in 和 not in

使用in或者not in关键字，可以实现判断：某个元素是否在一个集合中。

1
2
3
select device_id ,gender, age, university, gpa
from user_profile
where university IN ("北京大学","复旦大学","山东大学");

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

通过like关键字，进行字符串模式匹配；

复习下字符串匹配：

1
2
3
4
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^ ]：不匹配[ ]中的任意一个字符。
1
2
3
select device_id, age, university
from user_profile
where university like '%北京%';

SQL19 分组过滤

当聚合函数作为筛选条件时，不能使用where，而是having✅

换句话说，where后面不能接聚合函数

聚合函数：

  1. count()
  2. avg()
  3. sum()
  4. max()
  5. min()

聚合函数使用：通常和分组函数group by结合使用

  1. select子句的选择列表
  2. having子句
  3. compute或compute by子句
1
2
3
4
5
6
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;

SQL23 统计每个学校各难度的用户平均刷题数

因为需要按照不同难度和学校统计：order by u.university, q.difficult_level

1
2
3
4
SELECT u.university, q.difficult_level, COUNT(p.id) / COUNT(DISTINCT(p.device_id))
FROM user_profile as u, question_practice_detail as p, question_detail as q
where u.device_id = p.device_id and q.question_id = p.question_id
GROUP BY u.university, q.difficult_level

SQL25 查找山东大学男生的GPA

组合查询

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

注意题目要求是：不去重

如果是直接使用or关键字是会去掉重复的结果的;

这里就需要使用我们一个新的关键字了：UNION ALL

1
2
3
4
5
6
7
8
9
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE university='山东大学'
UNION ALL
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE gender='male'

# union 会去重， union all 不会去重

SQL26 计算25岁以上和以下的用户

CASE函数

类似编程语言中的if…else或者是case

有两种语法形式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#普通case函数
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END 

#搜索case函数
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END

用途：

  1. 结合分组进行统计

    例如需要统计25岁以上的人数以及25岁以下的人数，也就是这道题目的问题：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT 
        (
        CASE
            WHEN age >= 25 THEN '25岁及以上'
            ELSE '25岁以下'
        END
        ) age_cnt, 
        COUNT(id) number
    FROM user_profile
    GROUP BY 
    (
        CASE
            WHEN age >= 25 THEN '25岁及以上'
            ELSE '25岁以下'
        END)
    1
    2
    3
    4
    # if判断
    SELECT IF(age<25 OR age IS NULL,'25岁以下','25岁以及上') age_cut,COUNT(device_id) Number
    FROM user_profile
    GROUP BY age_cut

  2. 分条件更新字段值

    例如：将工资低于3000（包含3000）的员工涨幅工资20%,工资等于高于3000的员工涨幅8%

    1
    2
    3
    4
    5
    6
    7
    update t_salary
    	set salary = (
        	case
            	when salary > 3000 then salary + salary * 0.08
            	when salary <= 3000 then salary + salary * 0.2
            end
        )

  3. 检查表中字段值

    1
    2
    3
    4
    5
    6
    7
    select name, (
    	case
        	when des in (select des from table_2) then '一致'
        	else '不一致'
        end
    	)
    from table_1

  4. 行列转换

    1
    2
    3
    4
    5
    6
    7
    8
    9
    // 使用搜索case函数
    SELECT NAME,
     max( CASE  WHEN class = '语文' THEN grade ELSE 0 END ) 语文,
     max( CASE  WHEN class = '数学' THEN grade ELSE 0 END ) 数学,
     max( CASE  WHEN class = '英语' THEN grade ELSE 0 END ) 英语 
    FROM
     t_source 
    GROUP BY
    NAME

普通case和搜索case的区别

1、简单case函数判断条件只能是等于,而搜索case函数的条件可以是子查询,In,大于、等于等等。

2、如果只是使用简单的条件分组,可以选择普通case函数,如果需要判断更多的场景,则选择搜索case更好。

SQL28 计算用户8月每天的练题数量

这里主要使用了几个日期的函数：

YEAR()

MONTH()

DAY()

1
2
3
4
SELECT DAY(date) day, COUNT(*)
FROM question_practice_detail
WHERE YEAR(date) = '2021' and MONTH(date) = '08'
GROUP BY day

SQL29 计算用户平均次日留存率

这道题目还是有点难度的，通过查看题解和讨论总结了两种方法。

首先需要指导平均次日留存率的公示：p = （第一天和第二天都访问过的用户数）/（第一天访问过的用户数）

值得注意的是：可能一个用户两天中都访问过好几次，但是这仍然是一次留存，所以需要使用DISTINCT关键字。

方法一：用原始表左连接处理后的表，这个处理后的表是查询出所有用户在原始的访问日期加了一天的情况，最后的连接条件是设备id相同，而且日期相同。

1
2
3
4
5
6
SELECT COUNT(distinct q2.device_id,datee) / COUNT(distinct q.device_id, date)
FROM question_practice_detail q
    left join
    (SELECT DISTINCT device_id, date_add(date, INTERVAL 1 day) as datee
     FROM question_practice_detail) as q2
     on q2.device_id = q.device_id and q2.datee = q.date

方法二：相比前者，这个更好理解一些。核心思想是让两个表自连接，连接条件是device_id相同，而且表1的日期+1天等于表1的日期。

1
2
3
4
5
SELECT COUNT(t2.device_id) / COUNT(t1.device_id) avg_cnt
FROM (SELECT DISTINCT device_id, date FROM question_practice_detail) t1
    left join
    (SELECT DISTINCT device_id, date FROM question_practice_detail) t2
    on t1.device_id = t2.device_id and t1.date+1 = t2.date

文本函数

  1. 长度：length(str)
  2. 连接：concat(str1, str2)
  3. 分割：SUBSTRING_INDEX(str, 分隔符, 位置)
  4. 定位：INSTR(substr, str)
  5. 截取：substring(str, index_begin, len)

窗口函数

SQL33 找出每个学校GPA最低的同学

方法一：使用窗口函数

函数的含义为先分组再排序, row_number() over (partition by col1 order by col2),

表示根据col1分组，在分组内部根据col2排序

1
2
3
4
5
6
SELECT t1.device_id, t1.university, t1.gpa
FROM (
        SELECT device_id, university, gpa,
           row_number() over (partition by university order by gpa) ran
        FROM user_profile) t1
WHERE t1.ran = 1

方法二：使用子查询

1
2
3
4
5
6
7
8
9
SELECT device_id, university, gpa
FROM user_profile
WHERE gpa in (
    SELECT min(gpa)
    FROM user_profile
    GROUP BY university
)
GROUP BY university
ORDER BY university

SQL34 统计复旦用户8月答题情况

首先就有这些条件：

  1. 连接两个表device_id相等：u.device_id = qp.device_id
  2. 只统计复旦大学：u.university = ‘复旦大学’
  3. 时间是8月：YESR(date)=’2021’ AND MONth(date)=’08’

其次我们不仅需要统计做得总题目数还需要统计正确的题目数：

  1. 总题：COUNT(*)
  2. 正确题目数：SUM（if(qp.result=’right’),1,0）

按照device_id列进行聚合：GROUP BY u.device_id

1
2
3
4
5
6
select u.device_id, u.university, COUNT(question_id) as question_cnt,
        sum(if(qp.result='right',1,0)) as right_question_cnt
from user_profile as u left join question_practice_detail as qp
on u.device_id = qp.device_id and YEAR(date) = '2021' and MONTH(date) = '08'
where u.university = '复旦大学'        
GROUP BY u.device_id
笔记 提霸
牛客题霸 SQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！