牛客mysql汇总

谢钰灿

已于 2023-03-15 09:46:06 修改

阅读量65

点赞数

分类专栏： mysql 文章标签： mysql

于 2022-10-13 16:55:28 首次发布

本文链接：https://blog.csdn.net/weixin_48829840/article/details/127304445

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1：开窗函数

本题出题的题意其实主要是考察sum() over (order by ) 开窗函数，sum(a) over (order by b) 的含义是:
例如
a  b
1 2
3 4
5 6
按照b列排序，将a依次相加，得到结果，如下:
a  b  sum(a) over (order by b)：
1 2    1
3 4    1+3
5 6    1+3+5

此题就是将b换成了grade，即使b列乱序也没关系，因为有order by b：
number  grade  sum(number) over(order by grade)：
2    A    2
2    B    2+2
2    C    2+2+2
1    D    2+2+2+1

例子：
select emp_no,salary,(sum(salary) over(order by emp_no)) running_total
from salaries
where to_date = '9999-01-01'
order by emp_no

2：连接函数（group_concat）

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。
答案：
SELECT dept_no,group_concat(emp_no) employees
FROM dept_emp GROUP BY dept_no

3：排序函数

row_number 不存在并列
dense_rank 和rank存在并列，但rank很跳。
口诀的意思是：
row_number：不考虑并列的情况，哪怕分数相同，排名都是一溜下来的自然数。
dense_rank和rank 考虑并列的情况，区别在于rank很跳，并列排名的个数会影响接下来的排名，
表现为数字的中断。而dense_rank 不管有几个并列的第5名，接下来都是从6开始排。

例子：
select *,dense_rank() over (order by number desc) t_rank
from passing_number 
order by 
t_rank,id


order by排序
PARTITION by 分区

在这里插入图片描述

4：日期格式函数（date_format）

select job,date_format(date,'%Y-%m') mon,sum(num) cnt
from resume_info
where date like '2025%'
group by job,mon 
order by mon desc,cnt desc

5：case的使用

select em.emp_no,em.first_name,em.last_name,eb.btype,s.salary,
case when eb.btype =1 then s.salary*0.1
when eb.btype=2 then s.salary*0.2
else s.salary*0.3
end bonus   #可以把case理解为一个字段值得计算
from employees em,emp_bonus eb,salaries s
where em.emp_no=eb.emp_no
and eb.emp_no=s.emp_no
and to_date='9999-01-01'


#例子二：sum中使用case
select `date`,round(sum(case when type='completed' then 0 else 1 end)*1.0/count(type),3) p
from email
join user u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user u2 on (email.receive_id=u2.id and u2.is_blacklist=0)
group by email.date
order by email.date

6：计算小数点

round(字段,保留多少位)

7：求中位数

当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

select grade
from (
    select grade,(select sum(number) from class_grade) total,
    sum(number) over(order by grade) a, #这里是属于查询到分段得累加值  正序和
    sum(number) over(order by grade desc) b #逆序和
    from class_grade
    order by grade
)t
where a>=total/2 and b>=total/2  #正序逆序均大于整个数列数字个数的一半
order by grade

8：limit中offset的用法

以下的两种方式均表示取2,3,4三条条数据。
1.select* from test LIMIT 1,3;
当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。
 
2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

9：对于插入数据时，已经存在的情况下将对应数据进行忽略

insert ignore into actor value('3','ED','CHASE','2006-02-15 12:34:33')

10：将查询到的数据插入到表中

create table if not exists actor_name(
    first_name varchar(45)	not null comment '名字',
    last_name varchar(45)	not null comment '姓氏'
);
insert into actor_name    //将查询到的数据插入到表中
select first_name,last_name from 
actor

11：创建唯一索引和普通索引

create unique index uniq_idx_firstname on actor (first_name);
create index idx_lastname on actor (last_name)

12：mysql使用强制索引查询

SELECT * FROM salaries FORCE INDEX (idx_emp_no) WHERE emp_no = 10005

13：在数据表中添加一个字段

alter table actor add column create_date datetime NOT NULL 
default '2020-10-01 00:00:00'

14：删除问题

题目

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),

(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);

删除后titles_test表为(注：最后会select * from titles_test表来对比结果)
在这里插入图片描述

我一开始的做法

delete from titles_test  where id not in 
(
    select min(id) from titles_test 
    group by emp_no 
)

这样做的话明明是正确的啊，为什么不行了呢？
原因：MySQL中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了）

#给他嵌套多一层查询就行了
DELETE from titles_test where id not in 
(
    select *FROM
    (
        SELECT min(id) from titles_test GROUP BY emp_no
    )t
)

15：更新时涉及主键问题

在这里插入图片描述

问题分析

方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，
故会替换掉表中 id=5的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。
并且要将所有字段的值写出，否则将置为空。
方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，
Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用UPDATE和REPLACE 配合完成，
用REPLACE函数替换后的新值复制给 id=5 的emp_no。REPLACE的参数为整型时也可通过。

答案

方法一：replace into titles_test  values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
方法二：update titles_test set emp_no=REPLACE(emp_no,10001,10005) where id=5

16：为一个表创建外键

alter table audit
add constraint foreign key (emp_no)
references employees_test (id)

17：按照名称最后两位字母进行排序

#方法一：right(str, num) 函数。从右边开始截取str字符串num长度
select first_name from employees order by right(first_name, 2)
方法二：substr(x,y)函数。截取字符串x从y位置开始z长度，y为负数从末尾开始计算
select first_name from employees order by substr(first_name, -2)
    
select first_name from employees order by substr(first_name, length(first_name)-1);

18：date的用法 -》查询明天

在这里插入图片描述

select round(count(distinct l1.user_id)/(select count(distinct user_id) from login),3) p
from login l1,login l2
where l1.user_id=l2.user_id and l2.date=date(l1.date+'1 day')
and l1.date=(select min(date) from login where user_id=l1.user_id)

19：计算日期之差

DATEDIFF(d1,d2) 语句—计算日期 d1->d2 之间相隔的天数。

我的答案

select * from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
order by id

使用DATEDIFF(d1,d2)之后的答案

select *
from order_info
where datediff(date,"2025-10-15")>0
    and status = "completed"
    and product_name in("C++","Java","Python")
order by id

20：订单分析4

在这里插入图片描述

select user_id,date first_buy_date,cnt
from (
    select user_id,date,row_number() over(partition by user_id order by date asc) rn,
    count(*) over(partition by user_id) cnt
    from order_info
    where date>'2025-10-15'
    and status='completed'
    and product_name in ('C++','Java','Python')
) t
where cnt>=2 and rn=1
order by user_id asc