[SQL]牛客网-SQL快速入门练习题笔记

SQL快速入门

Chamberlain Tao


牛客网-SQL快速入门

  1. 查询所有列
SELECT * FROM user_profile
  1. 查询多列
SELECT device_id,gender,age,university FROM user_profile
  1. 查询结果去重
SELECT DISTINCT university FROM user_profile
  • 用DISTINCT关键字可以去掉结果中的重复行。
  1. 查询结果限制返回行数
SELECT device_id FROM user_profile LIMIT 0, 2

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果只给定一个参数,它表示返回最大的记录行数目。
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1。
初始记录行的偏移量是 0(而不是 1)。

  • 检索记录行 6-10
SELECT * FROM table LIMIT 5,5
  • 检索记录行 11-last
SELECT * FROM table LIMIT 10,-1
  • 检索前 5 个记录行
SELECT * FROM table LIMIT 5
  1. 将查询后的列重新命名
SELECT device_id as user_infos_example FROM user_profile LIMIT 2

二. 条件查询

  1. 查找学校是北大的学生信息
SELECT 	device_id, university 
FROM user_profile 
WHERE university = '北京大学'

[SQL]牛客网-SQL快速入门练习题笔记_第1张图片

  1. 查找年龄大于24岁的用户信息
SELECT device_id, gender, age, university 
FROM user_profile 
WHERE age > 24
  1. 查找某个年龄段的用户信息
SELECT device_id, gender, age 
FROM user_profile
WHERE age >= 20 AND age <= 23
  1. 查找除复旦大学的用户信息
SELECT device_id, gender, age, university
FROM user_profile
WHERE university != '复旦大学'
  1. 用where过滤空值练习
SELECT device_id, gender, age, university
FROM user_profile
WHERE age IS NOT NULL
  1. 高级操作符练习(1)
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE gender = 'male' AND gpa > 3.5
  1. 高级操作符练习(2)
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE university = '北京大学' OR gpa > 3.7
  1. Where in 和Not in
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE university IN ('北京大学', '复旦大学', '山东大学')
  1. 操作符混合运用
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE (gpa > 3.5 AND university = '山东大学') OR (gpa > 3.8 AND university = '复旦大学')
  1. 查看学校名称中含北京的用户
SELECT device_id, age, university
FROM user_profile
WHERE university LIKE "%北京%"

字符匹配

一般形式为:

列名 [NOT ] LIKE

匹配串中可包含如下四种通配符:
_:匹配任意一个字符;
%:匹配0个或多个字符;
[ ]:匹配[ ]中的任意一个字符(若要比较的字符是连续的,则可以用连字符“-”表 达 );
[^ ]:不匹配[ ]中的任意一个字符。

  • 查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况
SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%

三.高级查询

  1. 查找GPA最高值
# 方法一
SELECT MAX(gpa) FROM user_profile WHERE university = '复旦大学'
# 方法二
SELECT gpa FROM user_profile 
WHERE university = '复旦大学' ORDER BY gpa DESC LIMIT 1
  1. 计算男生人数以及平均GPA
SELECT COUNT(gender) as male_num, round(AVG(gpa), 1) as avg_gpa
FROM user_profile
WHERE gender = 'male'

round函数

SELECT ROUND(column_name,decimals) FROM table_name

column_name 要舍入的字段
decimals 要返回的小数位数

count :计数

avg :平均值

  1. 分组计算练习题
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

分组

作用:可以控制计算的级别:对全表还是对一组。
目的:细化计算函数的作用对象。
分组语句的一般形式:
[GROUP BY ]
[HAVING ]

GROUP BY子句中的分组依据列必须是表中存在的列名,不能使用AS子句指派的结果集列的别名。
带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列或统计函数,因为分组后每个组只返回一行结果。

  • 统计每门课程的选课人数,列出课程号和人数。
SELECT 学号, 
COUNT(*) 选课门数,
AVG(成绩) 平均成绩
FROM 成绩表
GROUP BY 学号

19.分组过滤练习题

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

使用HAVING

HAVING子句用于对分组后的结果再进行过滤,
它的功能有点像WHERE子句,但它用于组而不是单个记录。
在HAVING子句中可以使用统计函数,但在WHERE子句中则不能。

HAVING通常与GROUP BY子句一起使用。

  • 查询学生表中人数大于等于3的班号和人数
SELECT 班号, COUNT(*) 人数
FROM 学生表
GROUP BY 班号
HAVING COUNT(*) >= 3
  1. 分组排序练习题
select university, avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt asc
  1. 浙江大学用户题目回答情况
# 1.
select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id = up.device_id
where university = '浙江大学'
# 2.
select device_id, question_id, result
from question_practice_detail
where device_id = (select device_id 
                   from user_profile 
                   where university = '浙江大学')

多表查询-多表连接

  • 查询每个学生及其班级的详细信息。
SELECT * FROM 学生表
INNER JOIN 班级表 ON 学生表.班号=班级表.班号

表别名

可以为表提供别名,其格式如下:
<源表名> [ AS ] <表别名>

SELECT 姓名, 课程号, 成绩
FROM 学生表 S JOIN 成绩表 g 
ON S.学号 = g.学号
WHERE 状态 = ‘重修’

注:如果为表指定了别名,则查询语句中其他所有用到表名的地方都要使用别名

  • 查询软件工程系所有学生的情况,要求列出学生姓名和所在的系。
SELECT 姓名, 系名
FROM 学生表 s JOIN 班级表 bjb
ON s.班号 = bjb.班号
JOIN 系表 xb ON bjb.系号 = xb.系号
WHERE 系名= '软件工程系
  1. 统计每个学校的答过题的用户的平均答题数
select up.university, count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id
group by university
  1. 统计每个学校各难度的用户平均刷题数
select university, qd.difficult_level, round(count(qpd.question_id) / count(distinct up.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
group by university, difficult_level
  1. 统计每个用户的平均刷题数
select university, qd.difficult_level, round(count(qpd.question_id) / count(distinct up.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
  1. 计每个用户的平均刷题数
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 all,去重就用 union。

UNION(并)

使用 UNION可以实现将多个查询结果集合并为一个结果集。

所有查询语句中列的个数和列的顺序必须相同。
所有查询语句中对应列的数据类型必须兼容。
ORDER BY语句要放在最后一个查询语句的后边。

  • 查询系号是1和2的班级的班号、班名、系号,系号是1 的记录在前,2在后。
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 1
UNION
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 2

等价于

SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号 IN (1,2) ORDER BY 系号
  1. 计算25岁以上和以下的用户数量
# case
select case when age  >= 25 then '25岁及以上'
            when age < 25 or age is null then '25岁以下'
            end as age_cut, count(*) as number
from user_profile
group by age_cut
# if
select if (age >= 25, '25岁及以上', '25岁以下') as age_cut, count(*) as number
from user_profile
group by age_cut

case

是一种多分支的函数,可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方,但不能单独作为一个语句执行。
分为:
简单CASE函数
搜索CASE函数

简单CASE函数
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
  • 查询班级表中的学生的班号、班名、系号和班主任号,并对系号作如下处理:
    当系号为1时,显示 “计算机系”;
    当系号为2时,显示 “软件工程系”;
    当系号为3时,显示 “物联网系”。
SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表
搜索CASE函数
CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
  1. 查看不同年龄段的用户明细
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
from user_profile
  1. 计算用户8月每天的练题数量

题意

2021年8每天用户练习题目的数量

select day(date) as day, count(question_id) as question_cnt
from question_practice_detail
where month(date) = 8 and year(date) = 2021
group by date
  • year ,month,day函数
  1. 计算用户的平均次日留存率(hard)
select count(date2) / count(date1) as avg_ret
from (
    select 
        distinct qpd.device_id, 
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id = uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day) = uniq_id_date.date
) as id_last_next_date
  1. 统计每种性别的人数
# if
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
  1. 提取博客URL中的用户名
select device_id, substring_index(blog_url, '/', -1) as user_name
from user_submit
  1. 截取出年龄
select substring_index(substring_index(profile, ',', 3), ',', -1) as age, count(*) as number
from user_submit
group by age

注:两次截取

  1. 找出每个学校GPA最低的同学(hard)

  1. 统计复旦用户8月练题情况
select up.device_id, up.university, 
       count(question_id) as question_cnt, 
       sum(if (qpd.result = 'right', 1, 0)) as right_question_cnt

from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id

where up.university = '复旦大学' and month(qpd.date) = 8
group by up.device_id
  1. 浙大不同难度题目的正确率
select qd.difficult_level, 
       sum(if (qpd.result = 'right', 1, 0)) / count(qpd.question_id) as correct_rate 
from  
    user_profile as up
    inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id
    inner join question_detail as qd
    on qpd.question_id = qd.question_id
where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc
  1. 查找后排序
SELECT device_id, age
from user_profile
order by age asc
  1. 查找后多列排序
select device_id, gpa, age
from user_profile
order by gpa, age
  1. 查找后降序排列
select device_id, gpa, age
from user_profile
order by gpa desc, age desc
  1. 21年8月份练题总数
# 1
select count(distinct device_id) as did_cnt, count(question_id) as question_cnt
from question_practice_detail
where date like "2021-08-%"
# 2
select count(distinct device_id) as did_cnt, count(question_id) as question_cnt
from question_practice_detail
where date between '2021-08-01' and '2021-08-30'
  • List item

你可能感兴趣的