牛客 SQL 刷题笔记

21.查找入职以来薪水涨幅：salaries表用两次，减法，on 后面可以接 and

select e.emp_no, (s1.salary-s2.salary) as growth
from employees e inner join salaries s1 on e.emp_no=s1.emp_no and s1.to_date='9999-01-01'
inner join salaries s2 on e.hire_date=s2.from_date
order by growth

23.dense_rank() 函数用法：1,2,2,3； rank() 函数：1,2,2,4

select emp_no, salary, dense_rank()over(order by salary desc)
from salaries

25.员工薪水比其管理员还高：同一张表用两次，构造临时表，同一表的相同字段比较

select t1.emp_no, t2.emp_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary
from (select de.emp_no, de.dept_no, s.salary from dept_emp de inner join salaries s on de.emp_no=s.emp_no) t1
inner join (select dm.emp_no, dm.dept_no, s.salary from dept_manager dm inner join salaries s on dm.emp_no=s.emp_no) t2
on t1.dept_no = t2.dept_no
where t1.salary > t2.salary

26.汇总各个部门当前员工的title类型的分配数目：先对dept_no分组，再对title分组。两表中都有的字段使用时一定要加表名前缀

select d.dept_no,d.dept_name,t.title,count(*)
from departments d inner join dept_emp de on d.dept_no=de.dept_no
inner join titles t on de.emp_no=t.emp_no
group by de.dept_no,t.title
order by de.dept_no

28.查找描述信息中包含robot的电影对应的分类名称以及电影数目

select c.name,count(f.film_id) 
from film_category fc 
inner join category c on c.category_id=fc.category_id
inner join film f on fc.film_id=f.film_id
where f.description like "%robot%" 
and fc.category_id in (
    select category_id from film_category group by category_id having count(category_id)>=5
)

29.左连接后找字段为null：字段 is null

select f.film_id, f.title 
from film f left join film_category fc on f.film_id=fc.film_id
where fc.category_id is null

32.字符串拼接：concat(字段1,"-",字段2)

select concat(last_name," ",first_name) from employees

33.创建 actor 表

create table actor(
    actor_id smallint(5) primary key comment "主键id",
    frist_name varchar(45) not null comment "名字",
    last_name varchar(45) not null comment "姓氏",
    last_update date not null comment "日期"
)

35.批量插入数据，不使用replace

# mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

37.创建索引

alter table actor add unique index uniq_idx_firstname (first_name);
alter table actor add index idx_lastname (last_name);

38.创建视图

create view actor_name_view AS
select first_name first_name_v, last_name last_name_v
from actor;

39.使用强制索引

select *
from salaries
force INDEX (idx_emp_no)
where emp_no = 10005

40.现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

alter table actor add column create_date datetime not null DEFAULT '2020-10-01 00:00:00'

41.构建一个触发器

create trigger audit_log
after insert on employees_test
for each row
begin
    insert into audit values(new.id,new.name);
end

42.只保留组中 id 值最小的记录

# delete 中的子查询语句 ，规定删除的表和查询的表 表名不能相同，所以需要造表
delete from titles_test
where id not in (
    select * from (select min(id) from titles_test group by emp_no) as a
);

43.更新字段值

update titles_test 
set to_date=NULL,from_date='2001-01-01' 
where to_date='9999-01-01' 

46.创建外键：在audit表上创建外键约束，其emp_no对应employees_test表的主键id

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

50.字符串拼接：用 ’ 连接名和姓

select concat(last_name,"'",first_name) as name from employees

51.字符串长度，字符串替换

# 查找字符串'10,A,B' 中逗号','出现的次数cnt
select length("10,A,B")-length(replace("10,A,B",",","")) as cnt

52.截取字符串

select first_name
from employees
order by substr(first_name,length(first_name)-1,2) #substr(字符串，起始位置（索引以1开始），长度）

54.排除最大最小之后的平均工资

select avg(salary) as avg_salary
from salaries
where salary not in(select min(salary) from salaries where to_date='9999-01-01')
  and salary not in(select max(salary) from salaries where to_date='9999-01-01')
  and to_date='9999-01-01'

55.分页查询：查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5,5

59.case用法：奖金类型为1，奖金为工资的0.1倍；类型为2，奖金为工资的0.2倍，其余为0.3倍

select e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(case b.btype
when 1 then s.salary * 0.1
when 2 then s.salary * 0.2
when 3 then s.salary * 0.3 end) as bonus
from employees e inner join emp_bonus b on e.emp_no = b.emp_no
inner join salaries as s on e.emp_no = s.emp_no
where b.btype is not null
and s.to_date = '9999-01

60.窗口函数sum(字段)实时累加

<窗口函数>(查询的字段) over (partition by <用于分组的列名>
                order by <用于排序的列名>)
常用窗口函数有 rank，dense_rank,sum,min,avg 等

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

查询每个销售员销售金额前三名的订单

65.异常的邮件概率：round,case 函数的使用

select date,
ROUND(AVG(CASE WHEN type = 'no_completed' THEN 1 ELSE 0 END),3) AS p
from email
where send_id not in (select id from user where is_blacklist = 1)
and receive_id not in (select id from user where is_blacklist = 1)
group by date
order by date

68.新登录用户次日成功留存率：date_add(date,interval 1 day)

select round(count(distinct user_id)*1.0/(select count(distinct user_id) from login),3)
from login
where (user_id,date) in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id)

69.牛客每个人最近的登陆日期（四）：case，row_number()函数的使用

select temp.date,sum(case t_rank when 1 then 1 else 0 end) new
from (select date,row_number() over (partition by user_id order by date) t_rank from login) as temp
group by temp.date

75.找中位数位置：floor 函数的使用

select job,floor((count(*)+1)/2) as start,floor((count(*)/2+1)) as end
from grade
group by job
order by job

85. 截取字符串函数 left，year(date)

select job,left(date,7) mon,sum(num) cnt
from resume_info
where year(date)='2025'
group by job,mon 
order by mon desc,cnt desc

小结：
1.基本语法&常用函数：

select distinct col //注意 distinct 不是函数，不要加括号
	   count(col),count(*) //前者统计该字段行数，字段值为null不统计；后者统计记录条数   
	   max(col),min(col),avg(col),sum(col) //基础聚合函数  
	   round(count(col1)*1.0/count(col2),3) //四舍五入保留3位小数，同理 floor()向下取整，ceil()向上取整 
	   left(col,7) //截取字符串的前7位，适用于日期如 2020-11-11，截取出年月
	   //case条件语句，与上面的函数联合使用，例：ROUND(AVG(CASE TYPE WHEN 'no_completed' THEN 1 ELSE 0 END),3)
	   func(case col when 条件1 then 结果1 when 条件2 then 结果2 else 结果3 end) 
	   sum(col1)over(partition by col2 order by col3 desc) //逐行累加到当前行
	   //三个排序开窗函数的区别：1,2,3,4； 1,2,2,4； 1,2,2,3
	   row_number(),rank(),dense_rank() over(partition by col1 order by col2) 
	   concat(col1,"-",col2)  // 字符串拼接 
	   //length()计算字符串长度， replace(str,"a","b"):将str中的a用b代替
	   length("10,A,B")-length(replace("10,A,B",",","")) as cnt
from table1 t1 inner/left join table2 t2 on t1.col1=t2.col2 and t1.col2=t2.col1
	 join (select col from table1 join table2 on ....) as t on t1.col=t.col // 子查询作为表
where t1.col =/in/not in/like/is null/ (select col from t) // 子查询作为查询条件
group by col1,col2...
having  count(col1)>=10
order by col desc
limit 10,10 // 从第11条数据开始，查询出10条记录

1. 创建外键:在table1表上创建外键，其col1与table2的col2相关联
alter table tabel_name add foreign key (col1) references table2(col2)
2. 创建触发器
create trigger trig1
after insert/update/delete on table_name
for each row
begin
..sql语句...
end
3.给table_name表新增一列carete_date，类型为date，非空，默认值为'2020-01-01'
alter table table_name add column create_date date not null default '2020-01-01'
4.使用强制索引
select * from table_name force index(col1) where col2=value
5.创建视图
create view view_name
select col1,col2 from table_name
6.创建索引
alter table table_name add index/unique index index_name (col)
create index index_name on table_name(col_name)
7.插入数据
insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，如果数据库已经存在数据，则用新数据替换，如果没有则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
8.创建actor表
create table table_name(
	col_name, type, not null, primary key
	......
)