SQL练习

本文最后更新于：2022年7月11日 上午

概览：SQL练习

SQL练习

网址：http://xuesql.cn/

https://sqlbolt.com/

http://sqlfiddle.com/

SQL Lesson 1: SELECT 查询 101

【简单查询】找到所有电影的名称title

1
2
SELECT title
FROM Movies;

【简单查询】找到所有电影的导演

1
2
SELECT Director
FROM Movies;

【简单查询】找到所有电影的名称和导演

1
2
SELECT Title,Director
FROM Movies;

【简单查询】找到所有电影的名称和上映年份

1
2
SELECT Title,Year
FROM Movies;

【简单查询】找到所有电影的所有信息

1
2
SELECT *
FROM Movies;

【简单查询】找到所有电影的名称,Id和播放时长

1
2
SELECT Title,Id,Length_minutes
FROM Movies;

SQL Lesson 2: 条件查询 (constraints) (Pt. 1)

【简单条件】找到id为6的电影

1
2
3
SELECT *
FROM Movies
WHERE Id = 6;

【简单条件】找到在2000-2010年间year上映的电影

1
2
3
SELECT *
FROM Movies
WHERE Year BETWEEN 2000 AND 2010;
  • BETWEEN AND是包括边界值的，而NOT BETWEEN AND是不包括边界值的！

【简单条件】找到不是在2000-2010年间year上映的电影。

1
2
3
SELECT *
FROM Movies
WHERE Year NOT BETWEEN 2000 AND 2010;

【简单条件】找到头5部电影

1
2
3
SELECT *
FROM Movies
LIMIT 5;

【简单条件】找到2010（含）年之后的电影里片长小于两个小时的片子

1
2
3
4
SELECT *
FROM Movies
WHERE Length_minutes < 120
	AND Year >= 2010;

SQL Lesson 3: 条件查询(constraints) (Pt. 2)

【复杂条件】找到所有Toy Story系列电影

1
2
3
SELECT *
FROM Movies
WHERE Title LIKE 'Toy Story%';

【复杂条件】找到所有John Lasseter导演的电影

1
2
3
SELECT *
FROM Movies
WHERE Director = 'John Lasseter';

【复杂条件】找到所有不是John Lasseter导演的电影

1
2
3
SELECT *
FROM Movies
WHERE Director != 'John Lasseter';

【复杂条件】找到所有电影名为"WALL-"开头的电影

1
2
3
SELECT *
FROM Movies
WHERE Title LIKE 'WALL%';

【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来

1
2
3
4
SELECT *
FROM Movies
WHERE Year = 1998
	AND Title = "A Bug's Life";

SQL Lesson 4: 查询结果Filtering过滤 和 sorting排序

  • DISTINCT会直接删除重复的行。、
  • GROUP BY 分组
  • ORDER BY column ASC/DESC，按照column这一列的值进行升序或者降序。
  • LIMIT 选取部分结果
  • OFFSET用来指定从哪一行来进行返回。
  • LIMIT和OFFSET一般都在其他SQL执行完成之后再执行。

【结果排序】按导演名排重列出所有电影(只显示导演)，并按导演名正序排列

1
2
SELECT DISTINCT Director
FROM Movies;

【结果排序】列出按上映年份最新上线的4部电影

1
2
3
4
SELECT *
FROM Movies
ORDER BY Year DESC
LIMIT 4;

【结果排序】按电影名字母序升序排列，列出前5部电影

1
2
3
4
SELECT *
FROM Movies
ORDER BY Title ASC
LIMIT 5;

【结果排序】按电影名字母序升序排列，列出上一题之后的5部电影

1
2
3
4
SELECT *
FROM Movies
ORDER BY Title ASC
LIMIT 5 OFFSET 5;

👍【结果排序】如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可

1
2
3
4
5
SELECT Title
FROM Movies
WHERE Director='John Lasseter'
ORDER BY Length_minutes ASC
LIMIT 1 OFFSET 2;

【复习】列出所有在Chicago西部的城市，从西到东排序(包括所有字段

1
2
3
SELECT * FROM north_american_cities 
where Longitude < -87.629798
ORDER by Longitude ASC;

【复习】用人口数population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)

1
2
3
4
5
SELECT *
FROM north_american_cities
WHERE Country='Mexico'
ORDER BY Population DESC
LIMIT 2;

【复习】列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)

1
2
3
4
5
SELECT *
FROM north_american_cities
WHERE Country='United States'
ORDER BY Population DESC
LIMIT 2,2;

Inner Join

1
2
3
4
5
6
7
SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。

  • 交集：只保留两者都有的数据。

【联表】找到所有电影的国内Domestic_sales和国际销售额

1
2
3
4
SELECT * 
FROM movies
INNER JOIN  Boxoffice
ON movies.Id = Boxoffice.Movie_id;

【联表】找到所有国际销售额比国内销售大的电影

1
2
3
4
5
SELECT * 
FROM movies
INNER JOIN  Boxoffice
ON movies.Id = Boxoffice.Movie_id
WHERE Boxoffice.International_sales > Boxoffice.Domestic_sales;

【联表】找出所有电影按市场占有率rating倒序排列

1
2
3
4
5
SELECT * 
FROM movies
INNER JOIN  Boxoffice
ON movies.Id = Boxoffice.Movie_id
ORDER BY Boxoffice.rating;

【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少

1
2
3
4
5
6
SELECT movies.Director,Boxoffice.International_sales
FROM movies
INNER JOIN  Boxoffice
ON movies.Id = Boxoffice.Movie_id
ORDER BY Boxoffice.International_sales DESC
LIMIT 1;

7 Outer Join

  • 外连接不同于内连接，外连接会保留不能匹配的行。
  • A 外连接 B：
    • LEFT JOIN：保留所有A的行
    • RIGHT JOIN：保留所有B的行
    • FULL JOIN：保留所有的行
1
2
3
4
5
6
7
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

【复习】找到所有有雇员的办公室(buildings)名字

1
2
3
SELECT DISTINCT Building
FROM employees
WHERE Building NOT NULL;

【复习】找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

1

【难题】找到所有有雇员的办公室(buildings)和对应的容量

1

Mysql 子查询

查询一个字段的各种长度和长度对应的个数，并按长度的降序排序。

1
2
SELECT  t1.len,count(*) AS '数量' FROM (SELECT LENGTH(cust_city) AS len FROM customers) t1 
GROUP BY t1.len ORDER BY t1.len DESC;
  • t1 是子查询的结果集的名字

牛客网SQL大厂面试真题

https://www.nowcoder.com/exam/oj?page=1&tab=SQL篇&topicId=268

1.各视频的平均完播率

两张表的关联查询

思路：完播率 = count{（结束 - 开始）>=视频总时长} / 总次数

先查询平均播放时长

1
2
3
select video_id,sum(if(end_time - start_time >= b.duration ,1 ,0))/count(start_time)
from tb_user_video_log 
group by video_id

关联表

1
2
3
4
5
select video_id,sum(if(a.end_time - a.start_time >= b.duration ,1 ,0))/count(a.start_time)
from tb_user_video_log
left join tb_video_info b
on a.video_id = b.video_id
group by a.video_id;

然后细节处理

  • 保留三位
  • 2021年数据
  • 排序、列重命名
1
2
3
4
5
6
7
select video_id,round(sum(if(a.end_time - a.start_time >= b.duration ,1 ,0))/count(a.start_time), 3) as avg_comp_play_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where year(a.start_time) = 2021
group by a.video_id
order by avg_comp_play_rate desc;

知识点

1
2
3
4
if(bool,1,0) -- 判断
sum() -- 取总和
round(num,3) -- 取三位
year(a.start_time) - 取年份

2.平均播放进度大于60%的视频类别

播放进度：

如果播放时间超过视频时间，都认为100%

1
IF(time > d,1,time / d) *100

完播率60%

使用having子句

  • having子句用于分组之后的查询，不分组也可以用
  • 但是要求对应的列必须是在结果集中的
  • having子句可以使用聚合函数，但是where子句不可以
1
2
group by tag
having t > 60

百分比与两位小数

  • 百分比可以附加%号来完成
  • round(str,num)控制几位小数

最终答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select tag,CONCAT(avg_play_bai,'%') as avg_play_progress
from
( select tag,
ROUND(
AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time) >= b.duration,1,
   TIMESTAMPDIFF(SECOND,start_time,end_time)/b.duration) *100)
    ,2) as avg_play_bai
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
group by b.tag
HAVING avg_play_bai > 60
ORDER by avg_play_bai desc
) as t

3.每类视频近一个月的转发量/率

无最近一个月的条件

1
2
3
4
5
6
7
8
9
# 转发率：转发次数/观看次数

select tag,sum(a.if_retweet) as retweet_cut,
round(sum(a.if_retweet)/count(*),3) as retweet_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
group by b.tag
order by retweet_rate desc

最近一个月的表示：题目是这一类视频，而非每个视频

1
datediff((select max(start_time) from tb_user_video_log),date(start_time)) < 30

结果

1
2
3
4
5
6
7
8
select tag,sum(a.if_retweet) as retweet_cut,
round(sum(a.if_retweet)/count(*),3) as retweet_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where datediff((select max(start_time) from tb_user_video_log),date(start_time)) < 30
group by b.tag
order by retweet_rate desc

4.每个创作者每月的涨粉率及截止当前的总粉丝量

case的用法：

1
2
3
4
5
CASE 
    WHEN sex = ‘1’ THEN ‘男’ 
    WHEN sex = ‘2’ THEN ‘女’ 
    ELSE ‘其他’ 
END

则粉丝数计算

1
2
3
4
5
6
sum(
case when if_follow=1 then 1
    when if_follow=2 then -1
    else 0
    end
)

每个月？

1
group by month

开窗函数的使用:

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

通俗易懂的学会：SQL窗口函数 - 知乎 (zhihu.com)

语法

1
2
<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

例如：如下的sql求得了按照班级顺序排名，班级内按照成绩排名的效果

  • rank()是一个聚合函数，这里可以替换成其他的函数
1
2
3
4
select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

这里的题目是按照作者排名，作者的每月粉丝量按照月份排序的效果

1
2
3
4
5
6
7
8
sum(sum(case when if_follow=1 then 1
    when if_follow=2 then -1
    else 0
    end))
over (
partition by author
order by month asc
)

最终结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select author,
    date_format(start_time,'%Y-%m') as month,
    round(sum(case when if_follow=1 then 1
       when if_follow=2 then -1
       else 0 end) / count(author),3) as fans_growth_rate,
     sum(sum(case when if_follow=1 then 1
       when if_follow=2 then -1
       else 0 end)) over (partition by author order by date_format(start_time,'%Y-%m') ) as total_fans
     
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where year(start_time)=2021
group by author,month
order by author,total_fans

5.月总刷题数和日均刷题数

按照月进行汇总与排序。

1
2
3
4
select data_format(submit_time,"%Y%m") as submit_month
from t
group by submit_month
order by submit_month

求月中的日均刷题数

要具体到每个月的天数，使用last_day(date)函数可以获取参数日期的最后一天，day(last_day(date))就能获取到一个月的天数。

with rollup的使用

需求：group by 求得了学生的平均分数

现在要求总的平均分数。使用with rollup即可，会在 group by 之后，对聚合函数进行分组求和

  • order by不能在这个中使用。

这样会产生新的一行，但是这样的话，submit_month列值会为空，这是使用coalesce（exp1,exo2,…,expn），当前面的都为空时，才会返回expn。

所以最后结果时

1
2
3
4
5
6
 select COALESCE(date_format(submit_time,'%Y%m'),"2021汇总") as submit_month,
    count(*) as month_q_cnt ,
    round(count(*) / day(last_day(submit_time)),3) as avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
group by date_format(submit_time,'%Y%m') WITH ROLLUP

6.每类试卷得分前3名

按类型划分，且一个学生可能会有多个考试记录

1
group by tag,uid

求得前三名排序: 使用开窗函数 row_number() over()

1
row_number() over(partition by tag order by max(score) desc,min(score) desc,uid desc)
  • row_number 可以标出对应的序列
  • partition by 将结果按照tag进行分组
  • order by 将结果进行排序,先最高分排序，最高分相同按照最低分排序，否则按照uid排序

最终每个取前3名

1
2
3
4
5
6
7
8
9
10
select tid,uid,ranking
from (
select a.tag as tid,uid,
row_number() over(partition by a.tag order by max(b.score) desc,min(b.score) desc,b.uid desc) as ranking
from examination_info a
left join exam_record b
on a.exam_id = b.exam_id
group by a.tag,b.uid
) t
where ranking <= 3;

常见的窗口函数排序

  • rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
  • row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
  • dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

date_format日期格式化

Mysql 中使用DATE_FORMAT函数按月、周统计数据_wcp88888888的博客-CSDN博客

求一个交易表里的每周的交易成功的次数以及平均值

1
2
3
4
5
select date_formate(trans_time,"%Y%v") as week-n,
		count(*) as succ_count
from order_trade
where order_status = 'SUCCESS' and transtime < '2022-07-10'
group by channel_id,date_formate(trans_time,"%Y%v");
SQL
SQL

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