牛客网数据库编程题总结

数据库编程

一、写题须知数据库执行顺序

这是本人在牛客网上做的算法题总结，如有侵权请告知本人，本人将其删除，转载请说明出处；各个大厂招人的觉得我写的不错的也可以联系我：联系方式:19937164020(同微信)

查询:
1、from先确定从那个表中取数据； 存在join多表连接 from tab1，tab2,可以对表加别名，方便后面引用。
2、where 语句条件限定，没有限定就写成 where 1=1,表示总为true,无附加条件。
	如: select from tab_emp where 1=1 group by having order by
3、group by......having

	3.1、当group by 与聚合函数函数配合使用时，功能为分组后计算；
	select max(user_id),grand from user_info group by grand;分组条件grand,查询非聚合条件也是grand不产生冲突;
	
	3.2、当group by 与having配合使用时，功能为分组后过滤;
	select max(user_id),grand from user_info group by grand having grand > 'A';
	
	3.3、当group by 与聚合函数，同时与非聚合函数使用时，非聚合字段的取值是第一个匹配到字段内容，即id小的条目对应的字段内容;
	

4、聚合函数:有max,min,count,sum 聚合函数的执行在having之后，如果where中写聚合函数就会报错;
	重点:聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；

5、select 语句,选择出要查找的字段;

6、order by 根据选择select查找出的字段，才能进行排序。

7、过滤条件: where, group by 中的 having; 聚合函数结果作为筛选条件时使用 having 后跟group by后的字段
8、多表联查的过滤条件: inner join 中的 on 作为多表联查的过滤条件;on后面还可以跟 and

9、聚合函数 sum(if(submit_time is null,1,0)) 代表 如果 sub_time 为空 则累加 1, 否则加 0;
插入:
1、插入可以后面跟 select ，可以插入很多，可用于数据库的备份；
2、replace 后面跟主键或唯一，表中已有数据则更新，没有则插入

更新:
update 也可以使用 where

关于时间的函数:
interval
TIMESTAMPDIFF

二、总结mysql判断为奇偶数的方法

1、按位与
	id & 1 = 1; # 查询字段大于等于一的奇数
	
2、先除以2然后乘以2，如果与原来的相等就是偶数
	id=(id>>1)<<1;  # 查询id大于等于2的偶数
	
3、正则匹配最后一位
	select * from cinema WHERE id regexp '[13579]$';
	select * from cinema WHERE id regexp '[02468]$';

4、取余运算
	select * from cinema WHERE id%2 = 1;
	select * from cinema WHERE id%2 = 0;
	或者:
	select * from cinema WHERE mod(id, 2) = 1;
	select * from cinema WHERE mod(id, 2) = 0;
	
5、-1的奇数次方和偶数次方
	select * from cinema WHERE POWER(-1, id) = -1;
	select * from cinema WHERE POWER(-1, id) = 1; 

三、数据库优化

1、使用is not 代替 !=

1、查询最晚入职员工的所有信息

有一个员工employees表简况如下

要求查询：最晚入职员工的所有信息；

建表语句如下：

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL, 
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

答案:

select * from employees where hire_date = (select max(hire_date) from employees);
select * from employees order by hire_date desc limit 1;
select * from employees order by hire_date desc limit 0,1;

总结:也就是最晚入职也是最大的；在数字上；

2、入职时间排名倒数第三的员工

员工表:

建表语句:

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL, 
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

排名倒数第三的员工信息

学习: limit偏移量初始值为0；

limit接收一个或者两个数字的参数，参数必须是一个整数常量。

1、如果只给定一个参数，他表示返回最大的记录行数目。
select * from table limit 5;  // 代表查询前五行数据

2、如果给定两个参数，第一个参数代表从第几行开始，第二个参数代表从第一个参数往后偏移的个数;
select * from table limit 2,1;  // 从第二个开始，往后查询一个;

3、查找当前薪水详情和部门编号

有一个全部员工的薪水表salaries简况如下

有一个各个部门的领导表dept_manager如下：

建表语句

薪水表:

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

领导表:

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

请你查找各个部门当前领导的薪水详情以及对应部门编号dept_no, 输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no 列是最后一列，例子如下:

答案:

select ts.emp_no, salary, from_date, ts.to_date, dept_no from salaries ts inner join dept_manager td on ts.emp_no=td.emp_no order by ts.emp_no asc;

总结: 
	查询来说，字段和字段之间需要使用逗号隔开；
	两张表的字段有冲突的情况下，需要使用 表名.字段名 把字段加以区分(不会引起字段查询冲突)；

4、薪水记录超过15次的员工号emp_no和对应次数大于15

有一个薪水表，salaries简况如下

建表语句如下:

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

请你查询薪水记录超过15次的员工号emp_no 以及对应的记录次数t,输出如下:

答案:

select emp_no,count(emp_no) as t from salaries group by emp_no having t>15;

总结:
	1、count(emp_no) as t 展示, 如果不是表中的字段，需要使用 group by;
	2、count()聚合函数,不能写在where条件中固写在having语句后;

5、查找出所有当前薪水salary的情况

描述:

有一个薪水表，salaries简况如下:

请你找出所有员工具体薪水salary情况，对于有相同的薪水只显示一次，并按照逆序显示；

答案:

select distinct salary from salaries order by salary desc;

总结:
	distinct必须放在查询字段的开头;
	注意:是逆序排序;

6、查询非领导的员工

描述:有一个员工表employees简况如下:

有一个领导表dept_manager如下:

请你找出所有非部门领导的员工emp_no,以上例子输出；

答案:

select te.emp_no from employees te left join dept_manager td on te.emp_no=td.emp_no where td.dept_no is null ;

总结:
	左表展示不是领导的员工，先左连接查询 emp_no 相等的员工，此时左表完整展示，然后只展示在dept_manager中dept_no为 null的员工，表名该员工不是领导;

7、查找普通员工所对应的领导

有一个员工表dept_emp 简况如下

第一行代表员工编号为10001的所对应的是d001部门；

有一个部门经理表dept_manager简况如下

第一行表示为d001部门经理对应的编号为10002的员工。

获取所有员工和员工对应的经理，如果员工本身不是经理的话则不显示；

答案:

SELECT e.emp_no, m.emp_no as manager
FROM dept_emp AS e INNER JOIN dept_manager AS m 
ON e.dept_no = m.dept_no
WHERE e.emp_no != m.emp_no;

8、查找每个部门当前员工薪水最高的员工

有一员工表dept_emp简况如下

有一薪水表salaries简况如下:

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

答案:

SELECT d.dept_no, s.emp_no, MAX(s.salary) salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
GROUP BY d.dept_no;


解题思路:
1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
2、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
3、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

9、查找employees表，字段为奇数，并且字段名不为mary的员工

员工表employees简况如下:

请你查找employees表所有emp_no为奇数，且last_name不为Mary的与员工信息，并按照hire_date逆序排序；

查询结果如下:

select * from employees where emp_no & 1 =1 and last_name != 'Mary' order by hire_date desc;

10、统计出当前各个title类型对应员工当前薪水对应的平均工资

有一个员工职称表 titles 简况如下：

有一薪水表 salaries 简况如下:

请你统计出各个 title 类型对应的员工薪水对应的平均工资avg ,并且以avg生序排序，以上例子输出如下：

结果:

select t.title, avg(s.salary) from titles t inner join salaries s on 
t.emp_no=s.emp_no group by t.title order by avg(s.salary) asc;


注意: 查询出来的字段要用逗号隔开;

11、Where in 和Not in

题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

输出结果:

答案:

select device_id,gender,age,university,gpa FROM
user_profile WHERE university in("北京大学","复旦大学","山东大学")

12、分组过滤练习题

总结: 限定条件:

select university,avg (question_cnt) as avg_question_cnt,
avg (answer_cnt) avg_answer_cnt FROM
user_profile GROUP by university HAVING
avg(question_cnt)<5 or avg (answer_cnt)<20 

13、从两张表里面取出来自浙江大学的用户题目回答明细情况

题解:

方法1：join两个表，用inner join，条件是`on up.device_id=qpd.device_id and up.university=‘浙江大学’

使用inner join 是为了左表和右表 完整展示；

方法2：先从画像表找到浙江大学的所有学生id列表where university='浙江大学'，再去练习明细表筛选出id在这个列表的记录，用where in

第一种:
select a.device_id, question_id,result
from question_practice_detail a INNER JOIN
user_profile b on a.device_id=b.device_id
and b.university='浙江大学'

第二种:
select device_id, question_id,result
from question_practice_detail 
WHERE device_id in (select device_id from user_profile
         where university='浙江大学')

14、每个学校的答题数量

结果:

分析:

select university, (count(question_id)/count(DISTINCT b.device_id))as avg_answer_cnt 
from user_profile a INNER join question_practice_detail b
on a.device_id=b.device_id GROUP by a.university 

15、计算单个学校每个用户刷题数量

结果:

分析:

第一种:
select university,difficult_level,count(b.question_id)/count(DISTINCT(a.device_id))
as avg_answer_cnt
from user_profile a
INNER join question_practice_detail b on a.device_id=b.device_id 
INNER join question_detail c on b.question_id=c.question_id
group by difficult_level,a.university HAVING a.university='山东大学'

第二种:
select university,
difficult_level,
count(b.question_id)/count(DISTINCT(a.device_id)) as avg_answer_cnt
from user_profile a,question_practice_detail b,question_detail c
where a.device_id=b.device_id and a.university='山东大学'
and b.question_id=c.question_id
group by difficult_level

总结: 凡是inner 多表联查都可以使用 where

16、不去重查询union all

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。
where university=‘山东大学’ or gender="male"的话，也是去重的。

17、得分不小于平均分的最小分数

描述：请从试卷作答记录找到SQL试卷得分不小于该类试卷平均分的用户最低得分；

题解: 首先找出最小的分数，基于平均分；

就找找出score>= avg(score)

select min(score) min_score_over_avg 
from exam_record a, examination_info b
WHERE score is not NULL and a.exam_id=b.exam_id and b.tag='SQL' 
and score>=
(select avg(score) from exam_record c,examination_info d where c.exam_id=d.exam_id
and tag='SQL');

18、平均活跃天数和活跃人数

请计算2021年每个月试卷作答区用户平均月活跃天数avg_active_days和月活跃人数mau;

答案:

select DATE_FORMAT(start_time,'%Y%m') month,
round(count(distinct uid,DATE_FORMAT(start_time,'%Y%m%d'))/count(DISTINCT(uid)),2) avg_active_days,
count(distinct(uid)) mau
from exam_record 
where submit_time is not null and YEAR(submit_time) =2021
GROUP by month

用到知识点:

这个题，说实话我不会
date_format(start_time,'%Y%m%d')
round(除数/被除数,2)

插入

1、插入

第一种写法:
insert into exam_record(uid,exam_id,start_time,submit_time,score)
VALUES
(1001,9001,"2021-09-01 22:11:12","2021-09-01 23:01:12",90),
(1002,9002,"2021-09-04 07:01:02",null,null);
第二种写法:
insert into exam_record
VALUES
(null,1001,9001,"2021-09-01 22:11:12","2021-09-01 22:11:12" +INTERVAL 50 minute,90),
(null,1002,9002,"2021-09-04 07:01:02",null,null);

2、数据库备份(条件过滤)语句

第一种:
INSERT INTO exam_record_before_2021(uid,exam_id,start_time,submit_time,score)
select uid,exam_id,start_time,submit_time,score FROM exam_record where year(start_time) < '2021' and not isnull(score)
第二种:
INSERT INTO exam_record_before_2021
select null,uid,exam_id,start_time,submit_time,score FROM exam_record where year(submit_time) < 2021

3、replace 的使用

总结:

replace into examination_info
values
(null,9003,'SQL','hard',90,'2021-01-01 00:00:00')

更新

1、更新字段下的tag字段内容

update examination_info 
set tag='Python' where tag='PYTHON'

总结: update 也可以使用 where

删除

1、条件删除

题解:

相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');

相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

相差176400秒
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

14个月前
SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -14 MONTH), '%Y/%m/%d')

答案:
DELETE from exam_record
where 
timestampdiff(MINUTE, start_time, submit_time)< 5 
and score <60

2、删除无效的记录

DELETE from exam_record
where timestampdiff(MINUTE, start_time, submit_time)< 5 
or submit_time is null order by start_time limit 3;

总结:

order by 虽然也作为筛选条件，但是语法上前面不加 and

创建表

1、给出字段信息，创建表

备注：后台会通过 SHOW FULL FIELDS FROM user_info_vip 语句，来对比输出结果

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS user_info_vip (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int(11) NOT NULL unique key COMMENT '用户ID',
nick_name varchar(64) COMMENT '昵称',
achievement int(11) default 0 COMMENT '成就值',
level int(11) COMMENT '用户等级',
job varchar(32) COMMENT '职业方向',
register_time datetime default current_timestamp COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

2、修改表

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
 
alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型
 
alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

索引

1、修改索引

-- 普通索引
ALTER TABLE examination_info ADD INDEX idx_duration(duration);
-- 唯一索引
ALTER TABLE examination_info ADD UNIQUE uniq_idx_exam_id(exam_id);
-- 全文索引
ALTER TABLE examination_info ADD FULLTEXT full_idx_tag(tag);

2、删除索引

drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;

3、统计作答次数

where 执行的先后顺序影响；

错误答案:

--这种写法错误，因为先执行 where 的限制条件
select
count(id) total_pv,
count(submit_time) complete_pv,
count(distinct(exam_id)) complete_exam_cnt
FROM exam_record
WHERE submit_time is not NULL or score is not null;

--这种写法没有错误，相当于全局搜索
select
count(id) total_pv,
count(submit_time) complete_pv,
(select count(distinct(exam_id)) from exam_record WHERE submit_time is not NULL or score is not null) complete_exam_cnt
FROM exam_record;

查找

1、月刷题数和日均刷题数

有表:practice_record;

解释:2021年8月共有2次刷题记录，日均刷题数为 2/31=0.065; 汇总的均值 总数/31,最后一点需要升序排序

select DATE_FORMAT(submit_time,"%Y%m") submit_month,
count(score) month_q_cnt,
round(count(score)/day(Last_DAY(submit_time)),3) avg_day_q_cnt 
from practice_record
where DATE_FORMAT(submit_time,"%Y")=2021
GROUP by submit_month
union all
select '2021汇总' submit_month,
count(score) month_q_cnt,
round(count(score)/31,3) avg_day_q_cnt 
from practice_record
where DATE_FORMAT(submit_time,"%Y")=2021
order by submit_month;

2、求某个月有多少天

day(LAST_DAY(submit_time))

函数 last_day（）和 day 的用法；

mysql> select * from practice_record;
+----+------+-------------+---------------------+-------+
| id | uid  | question_id | submit_time         | score |
+----+------+-------------+---------------------+-------+
|  1 | 1001 |        8001 | 2021-08-02 11:41:01 |    60 |
|  2 | 1002 |        8001 | 2021-09-02 19:30:01 |    50 |
|  3 | 1002 |        8001 | 2021-09-02 19:20:01 |    70 |
|  4 | 1002 |        8002 | 2021-09-02 19:38:01 |    70 |
|  5 | 1003 |        8002 | 2021-08-01 19:38:01 |    80 |
+----+------+-------------+---------------------+-------+
5 rows in set (0.00 sec)

mysql> select LAST_DAY(submit_time) day from practice_record;
+------------+
| day        |
+------------+
| 2021-08-31 |
| 2021-09-30 |
| 2021-09-30 |
| 2021-09-30 |
| 2021-08-31 |
+------------+
5 rows in set (0.01 sec)

mysql> select day(LAST_DAY(submit_time)) day from practice_record;
+------+
| day  |
+------+
|   31 |
|   30 |
|   30 |
|   30 |
|   31 |
+------+

3、试卷发布当天作答人数和平均数

有一张用户信息表user_info

试卷信息表: examination_info

试卷作答表: exam_record

要求: 计算每张SQL 类别试卷发布后，当天5级以上的用户作答人数uv 和平均分avg_score, 按人数降序，相同人数按平均分升序

结果:

select exam_id,count(distinct uid) as uv,round(avg(score),1) as avg_score
from exam_record
where exam_id in (select exam_id from examination_info where tag = 'SQL')
      and uid in (select uid from user_info where level >5)
      and date(submit_time) in (select date(release_time) from examination_info)
      and submit_time is not NULL
group by exam_id,date(submit_time)
order by uv desc,avg_score asc;

4、试卷SQL得分大于80用户分布等级

描述:

用户信息表user_info

试卷信息表: examination_info

试卷作答信息表: exam_record:

要求:

统计作答SQL类别的试卷得分大于80的人用户分布等级，按数量降序排序，示例数据结果输出如下

解释: 9001 为SQL类试卷，作答试卷大于80的人有 1002,1003,1005 共3个人，6级2人，5级1人。

select level, 
count(score>80) level_cnt
from user_info a,examination_info b,exam_record c
where b.tag='SQL' and b.exam_id=c.exam_id and a.uid=c.uid and score>80
GROUP by level
ORDER by level_cnt DESC

5、每份试卷和每个题目被作答的人数和次数

描述:

现有试卷作答记录表exam_record;

题目练习表practice_record

请统计每个题目和每份试卷被作答的人数和次数，分别在试卷区和题目区按uv&pv降序显示，示例数据结果如下

解释: 试卷区有3人共练习3次试卷9001,1人作答3次9002；刷题区有3人刷5次8001,2人刷2次8002

select exam_id tid, count(distinct(uid)) uv, count((exam_id)) pv
from exam_record GROUP by exam_id 
union ALL
select question_id tid,count(DISTINCT(uid)) uv,count(question_id) pv
from practice_record GROUP by question_id 
order by LEFT(tid,1) desc,uv desc,pv desc;

总结:

left() 函数的用法

返回字符串str的最左面len个字符。
select count(Tname) 李姓老师的数量 from Teacher where left(Tname,1) = '李';

6、分别满足两个活动的人

现有试卷examination_info

试卷作答记录表 exam_record

实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动2

(select uid,'activity1' as'activity'
from exam_record 
where year(start_time)=2021
group by uid
HAVING min(score)>=85)
union ALL
(select distinct uid, 'activity2' as 'activity'
from exam_record a,examination_info b 
WHERE a.exam_id=b.exam_id and difficulty='hard' 
and score>80
and TIMESTAMPDIFF(SECOND,start_time,submit_time)<=duration*60/2
and year(start_time)=2021)
order by uid;

总结:

select distinct uid, 'activity2' as 'activity'

解释: 将右面 activity2(在表中无字段) 随着 左边 uid 进行匹配 只展示 activity2 

7、统计未完成状态的试卷的未完成数和为完成率

现有试卷作答记录表 exam_record

请统计有未完成状态的试卷的未完成数incomplete_cnt和为完成率 incomplete_rate,数据结构如下

解释：试卷9001有3次被作答的记录，其中两次完成，1次未完成，因此未完成数为1，未完成率为0.333（保留3位小数）

select exam_id,
sum(if(submit_time is null,1,0)) incomplete_cnt,
round(sum(if(submit_time is null,1,0))/count(*),3) incomplete_rate 
from exam_record
group by exam_id HAVING incomplete_cnt >=1;

总结:

sum(if(submit_time is null,1,0)) // 求 submit_time 不为null的总和,意为：为null,就加1,否则加 0
round(被除数/除数,3);

8、0级用户高难度的平均得分

现有用户表 user_info

试卷信息表: examination_info

试卷作答记录表： exam_record

请输出每个0级用户所有高难度试卷考试平均用时和平均得分，未完成的默认试卷时长和0分处理；

解释：0级用户有1001，高难度试卷有9001，1001作答9001的记录有3条，分别用时20分钟、未完成（试卷时长60分钟）、30分钟（未满31分钟），分别得分为80分、未完成（0分处理）、20分。因此他的平均用时为110/3=36.7（保留一位小数），平均得分为33分（取整）

题解:

先搞出来一张表：

根据 0 级用户，查出所有高难度试卷的得分和用时 得到一张表；

然后 在根据新表 算出 平均得分，和平均用时

 select uid,
 round(avg(new_score),0) avg_score,
 round(avg(cost_time),1) avg_time_took
 from(
 select a.uid as uid,
           if(score is not null, score, 0) as new_score,
           if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time
    from exam_record a left join examination_info b
    on a.exam_id = b.exam_id
    left join user_info c
    on a.uid = c.uid
    where level = 0
    and difficulty = 'hard') new_table
    GROUP by uid; 

9、分割字符串SUBSTRING_INDEX

现有试卷examination_info

录题同学有一次手误，将部分记录的试题类别tag 、难度、 时长同时录入 tag 字段，请帮忙找出这些录错的记录，并拆分后按正确的类型输出

select exam_id,
SUBSTRING_INDEX(tag,',',1) tag,
SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1) difficulty,
SUBSTRING_INDEX(tag,',',-1) duration
from examination_info
WHERE tag LIKE '%,%';

总结:

SUBSTRING_INDEX(tag,',',2)  // 根据逗号分割，获取前两个字符
SUBSTRING_INDEX(tag,',',-2) // 根据逗号分割，获取后两个字符

10、对过长的昵称截取处理

描述；

现有用户信息表user_info

要求: 筛选昵称nick_name大于10的用户，如果大于13,截取到10,然后添加到…

select uid
,IF(CHAR_LENGTH(nick_name)>13,CONCAT(SUBSTRING(nick_name,1,10),'...'),nick_name) nick_name
from user_info
where CHAR_LENGTH(nick_name)>10

总结:

知识点:
判断字符大于10: CHAR_LENGTH(nick_name)>10
连接两个字符串: concat(nick_name,'...')
if判断: if(a>b;a,b);  // 如果a大于b,选择a，否则选择b

11、查询所有员工所对应的部门，包括没有分部门的员工

描述:

有一员工表:

有一部门表:

有一个员工关系表:

要求:

请你查出所有员工last_name和 first_name对应的部门，包括没有分配部门的员工；

分析: 没有分配部门的员工，部门为 null, 根据左连接特性，左表完整展示，右表匹配展示，使用做链接即可

select last_name,first_name,dept_name
from employees a 
left join dept_emp c on a.emp_no=c.emp_no
left join departments b on b.dept_no=c.dept_no;

12、统计各个部门的工资记录数

有一个部门表departments:

有一员工关系表dept_emp：

有一个薪水表salaries:

要求:统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，以上例子输出如下:

select a.dept_no,a.dept_name,
count(c.emp_no) as sum
from departments a,dept_emp b,salaries c
where b.dept_no=a.dept_no and b.emp_no=c.emp_no
group by a.dept_no
order by a.dept_no ;

13、各个视频的平均完整率

描述:

用户-视频互动表 tb_user_video_log

短视频信息表: tb_video_info

问题；

计算2021年里有播放记录的每个视频的完播率，按照完播率降序排序；

完播率: 完全的播放次数/ 总播放次数

思路:

使用 sum if 统计出完整播放的次数 / 某个视频播放的总次数；

select a.video_id,
ROUND(sum(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>= duration,1,0))
      /count(a.video_id),3) avg_comp_play_rate
FROM tb_user_video_log a,tb_video_info b
WHERE a.video_id=b.video_id
and year(start_time)=2021 
GROUP by a.video_id 
order by avg_comp_play_rate desc;

14、平均播放进度大于60%的视频类别

用户-视频互动表tb_user_video_log:

短视频信息表tb_video_info：

问题：计算各类视频的平均播放进度，将进度大于60%的类别输出。

SELECT tag,
concat(round(avg(if(TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)>b.duration,1,TIMESTAMPDIFF(SECOND,a.start_time,a.end_time)/b.duration)) * 100,2) ,'%') as avg_play_progress
FROM tb_user_video_log a,tb_video_info b
       where a.video_id=b.video_id
       GROUP by tag having replace(avg_play_progress,'%','')>60
       ORDER by avg_play_progress desc;

15、每类视频近一个月的转发量

用户-视频互动表tb_user_video_log:

短视频信息表tb_video_info：

问题：统计每类视频在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中的转发量和转发率（保留3位小数），

注：转发率＝转发量÷播放量。结果按转发率降序排序。

思路:

就是距离最近日期的一个月内的转发率;

核心就是求出: 最大日期，和 不确定日期，相差一个月

sum的用法:

sum(if_retweet)    // 统计 if_retweet 为 1 的总和；

DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(a.start_time)) <= 29

select tag,
sum(if_retweet) retweet_cnt,
round(sum(if_retweet)/count(a.video_id),3) retweet_rate
FROM tb_user_video_log a,tb_video_info b
where a.video_id=b.video_id 
and DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(a.start_time)) <= 29
group by tag 
order by retweet_rate desc