Mysql 练习(牛客网Mysql总结)

最新推荐文章于 2024-04-07 11:22:06 发布

jialun0116

最新推荐文章于 2024-04-07 11:22:06 发布

阅读量416

点赞数 1

分类专栏：面试 mysql 数据分析文章标签： mysql 面试

本文链接：https://blog.csdn.net/qq_30031221/article/details/109445823

版权

mysql 同时被 3 个专栏收录

44 篇文章 3 订阅

订阅专栏

面试

34 篇文章 5 订阅

订阅专栏

数据分析

17 篇文章 3 订阅

订阅专栏

Mysql 练习

重点看：2、5、6、7、8、10、12、17、18、21、22、27、28、32、37、38、39、42、45、46、50、55、57、59、61、62、64、66、68、74、75

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

select * from employees 
order by hire_date desc
limit 1;

select * from employees 
where hire_date = (
  select max(hire_date) from employees
);

查找入职员工时间排名倒数第三的员工的所有信息**(可能存在多个)**

-- order by 排序
-- desc 从大到小
-- limit 2,1 从第二条开始读，读一条
-- limit 1 offset 2 偏移2读取1
select * from employees 
where hire_date = (
    select distinct hire_date 
  	from employess
    order by hire_date desc
  	limit 1 offset 2
);

select * from employees order by hire_date desc limit 2,1;

查找各个部门当前(dept_manager.to_date=‘9999-01-01’)，领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no

select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from salaries inner join dept_manager 
on salaries.emp_no = dept_manager.emp_no 
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
order by salaries.emp_no;

查找所有已经分配部门的员工的last_name和first_name以及dept_no

select employees.last_name,employees.first_name,dept_emp.dept_no
from employees inner join dept_emp 
on dept_emp.emp_no = employees.emp_no;

查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

-- left join 左连接
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left join dept_emp 
on dept_emp.emp_no = employees.emp_no;

查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)

-- desc 逆序 从大到小
select employees.emp_no,salaries.salary
from employees left join salaries
where employees.emp_no = salaries.emp_no
and employees.hire_date = salaries.from_date
order by employees.emp_no desc;

查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

-- 按照员工号分类 
select emp_no,count(from_date) as t 
from salaries
group by emp_no 
having t > 15;
-- 下面代码正确 但是牛客网的环境不支持这样写
select a.emp_no, count(a.emp_no) t
from salaries a inner join salaries b
on a.emp_no=b.emp_no and a.to_date=b.from_date
where a.salary < b.salary
group by a.emp_no
having t>15

找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

-- WHERE语句在GROUP BY语句之前，SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后，SQL会在分组之后计算HAVING语句。
-- Sql中过滤条件 on 和 where的区别  https://blog.csdn.net/u013468917/article/details/61933994

select salary from salaries
where to_date = '9999-01-01'
group by salary 
order by salary desc;

获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

select dept_manager.dept_no,dept_manager.emp_no,salaries.salary
from dept_manager 
inner join salaries on dept_manager.emp_no = salaries.emp_no
where salaries.to_date = '9999-01-01'
and dept_manager.to_date = '9999-01-01'

获取所有非manager的员工emp_no

select emp_no from employees 
where emp_no not in 
(select emp_no from dept_manager)

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

获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

select dept_emp.emp_no,dept_manager.emp_no as manager_no
from dept_emp left join dept_manager
on dept_emp.dept_no = dept_manager.dept_no
where dept_manager.to_date='9999-01-01'
and dept_emp.emp_no <> manager_no

获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。

select dept_emp.dept_no,dept_emp.emp_no, max(salaries.salary) as salary 
from dept_emp inner join salaries
on dept_emp.emp_no = salaries.emp_no
where dept_emp.to_date = '9999-01-01'and salaries.to_date = '9999-01-01'
group by dept_emp.dept_no 
order by dept_emp.dept_no 

-- 上面代码由于用了group by，而d.emp_no是非聚合字段，所以不能出现在SELECT。
-- 一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。
-- 如果强行写上述代码，当多人同时拥有最高薪水时也无法查出。
-- GROUP BY语句与HAVING语句的使用：参考资料：https://www.cnblogs.com/geogre123/p/11177204.html
-- 下面是规范代码

select d1.dept_no,d1.emp_no,s1.salary
from dept_emp d1 inner join salaries s1
on d1.emp_no = s1.emp_no
and d1.to_date = '9999-01-01'and s1.to_date = '9999-01-01'
where s1.salary in (
    select max(s2.salary)
    from dept_emp as d2 inner join salaries as s2
    on d2.emp_no = s2.emp_no
    and d2.to_date = '9999-01-01'and s2.to_date = '9999-01-01'
    and d1.dept_no = d2.dept_no
)
order by d1.dept_no



select t.dept_no,t.emp_no,t.salary from
(
    select de.dept_no,de.emp_no,s.salary,row_number() 
    over(partition by de.dept_no order by s.salary desc) as rk
    from(select * from dept_emp where to_date='9999-01-01') de
         inner join
        (select * from salaries where to_date='9999-01-01') s
         on de.emp_no=s.emp_no
)t
where t.rk=1;

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

select title,count(title) as t
from titles
group by title having t >= 2

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

select title,count(distinct emp_no) as t
from titles
group by title 
having t >= 2

查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees 
where last_name <> 'Mary' and emp_no % 2 = 1
order by hire_date desc;

统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

select titles.title,avg(salaries.salary) 
from titles inner join salaries on titles.emp_no = salaries.emp_no 
where titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by titles.title

获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary**(可能有多个)**

select emp_no,salary 
from salaries
where salary in (
    select salary 
    from salaries where to_date = '9999-01-01'
    group by salary
    order by salary desc limit 1,1
)

-- 开窗函数 
下面介绍三种用于进行排序的专用窗口函数：
1、RANK()
    在计算排序时，若存在相同位次，会跳过之后的位次。
    例如，有3条排在第1位时，排序为：1，1，1，4······
2、DENSE_RANK()
    在计算排序时，若存在相同位次，不会跳过之后的位次。
    例如，有3条排在第1位时，排序为：1，1，1，2······
3、ROW_NUMBER()
    这个函数赋予唯一的连续位次。
    例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
                 ORDER BY <排序用列清单> ）
             
select emp_no,salary
from(
    -- 当薪水第二的人有多个时只会输出一个
    -- select emp_no,salary,row_number() over(order by salary desc) t 
  	-- 当薪水第二的人有多个时全部输出
    select emp_no,salary,rank() over(order by salary desc) t
    from salaries
    where to_date='9999-01-01'
)
where t=2

查找当前薪水(to_date=‘9999-01-01’)排名第二多(可处理任意值)的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

select salaries.emp_no,salaries.salary,employees.last_name,employees.first_name
from salaries left join employees 
on salaries.emp_no = employees.emp_no
where to_date='9999-01-01'
and salaries.salary in (
    select s1.salary 
  	from salaries as s1inner join salaries s2
    on s1.salary <= s2.salary
    group by s1.salary 
    having count(distinct s2.salary) = 2
    and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
)

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工(三个表连接)

select employees.last_name,employees.first_name,tmp.dept_name
from employees left join (
    select emp_no,dept_name
    from dept_emp left join departments
    on dept_emp.dept_no = departments.dept_no
)tmp
on employees.emp_no = tmp.emp_no

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

select max(salary) - min(salary)
from salaries
where emp_no = 10001
-- 按照时间排序 最后的-最前的
select(
    select salary from salaries where emp_no = 10001
    order by to_date desc limit 1
)-(
    select salary from salaries where emp_no = 10001
    order by to_date asc limit 1
)as growth

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

-- 思想与20一样 最后的 - 前面的
select salaries1.emp_no,(salaries1.salary - salaries2.salary) as growth
from(
  select emp_no,salary from salaries where to_date = '9999-01-01'
) salaries1
inner join (
    select salaries.emp_no,salaries.salary from salaries inner join employees 
    on salaries.emp_no = employees.emp_no
    where employees.hire_date = salaries.from_date
) salaries2
on salaries1.emp_no = salaries2.emp_no
order by growth

统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum(三表联查)

select d.dept_no,d.dept_name,count(*) as sum
from salaries s inner join dept_emp de on s.emp_no=de.emp_no
inner join departments d on d.dept_no=de.dept_no
group by d.dept_no

-- 上面的有问题
select departments.dept_no,departments.dept_name,tmp.sum
from departments inner join 
(
  	select dept_emp.dept_no,count(salaries.salary) as sum
		from dept_emp inner join salaries
  	on dept_emp.emp_no = salaries.emp_no
		group by dept_emp.dept_no
) tmp
on departments.dept_no = tmp.dept_no

对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

select emp_no,salary,dense_rank() over(order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

select dept_emp.dept_no,employees.emp_no,salaries.salary
from dept_emp 
inner join employees on dept_emp.emp_no = employees.emp_no
inner join salaries on employees.emp_no = salaries.emp_no
where salaries.to_date='9999-01-01'
and employees.emp_no not in (
    select emp_no from dept_manager
)

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

select emp.emp_no,manager.emp_no,emp.salary,manager.salary
from (
        select dept_emp.emp_no,dept_emp.dept_no,salary 
          from salaries inner join dept_emp 
          on salaries.emp_no = dept_emp.emp_no 
          and salaries.to_date = '9999-01-01'
          and dept_emp.to_date = '9999-01-01'
      ) emp
      inner join
      (
          select dept_manager.emp_no,dept_manager.dept_no,salary 
          from salaries inner join dept_manager 
          on salaries.emp_no = dept_manager.emp_no
          and salaries.to_date = '9999-01-01'
          and dept_manager.to_date = '9999-01-01'
      ) manager
on emp.dept_no = manager.dept_no
where emp.salary > manager.salary

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count

(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)

select departments.dept_no,departments.dept_name,titles.title,count(titles.title)
from departments,dept_emp,titles
where departments.dept_no = dept_emp.dept_no
and dept_emp.emp_no = titles.emp_no
and dept_emp.to_date = '9999-01-01'
and titles.to_date ='9999-01-01'
group by departments.dept_no,titles.title

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)

(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

select s1.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1 inner join salaries s2
on s1.emp_no = s2.emp_no
and s1.to_date = s2.from_date
where s2.salary - s1.salary > 5000
order by salary_growth desc

select s1.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1
join salaries s2
on s1.emp_no=s2.emp_no
and (strftime('%Y', s2.to_date)-strftime('%Y', s1.to_date))=1
and (s2.salary-s1.salary)>5000
order by salary_growth desc;

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

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

使用join查询方式找出没有分类的电影id以及名称

-- inner join 中where和on类似
-- 在left join 中on后面的条件只对右表有效 ，right join同理
-- where 对整个表过滤

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

你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

select title,description
from film 
where film_id in(
    select  film_category.film_id
    from film_category inner join category
    on film_category.category_id = category.category_id
    where category.name = 'Action'
)

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)

-- mysql
select concat(last_name," ",first_name) as Namefrom employees
-- sqlite
select (last_name||' '||first_name) as Name from employees

创建一个actor表，包含如下列信息

create table actor(
    actor_id smallint not null primary key,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update date not null default(datetime('now','localtime'))
)

请你对于表actor批量插入如下数据(不能有2条insert语句哦!)

insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
                        (2,'NICK','WAHLBERG','2006-02-15 12:34:33')

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

insert or ignore into actor 
values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

create table if not exists actor_name(
    first_name varchar(45) not null,
    last_name varchar(45) not null
); 

insert into actor_name
select first_name,last_name from actor;

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
```
create unique index uniq_idx_firstname on actor(first_name);

create index idx_lastname on actor(last_name);
```
针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

select * from salaries indexed by idx_emp_no where emp_no = 10005

现在在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.构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

CREATE TABLE employees_test(
	ID INT PRIMARY KEY NOT NULL,
	NAME TEXT NOT NULL,
	AGE INT NOT NULL,
	ADDRESS CHAR(50),
	SALARY REAL
);
CREATE TABLE audit(
	EMP_no INT NOT NULL,
	NAME TEXT NOT NULL
);
/*在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
*/
create trigger audit_log
after insert on employees_test
for each row
begin
    insert into audit values(new.id,new.name);
end

删除emp_no重复的记录，只保留最小的id对应的记录。

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

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

-- update table set value where condition
update titles_test set to_date=NULL,from_date="2001-01-01"
where to_date = '9999-01-01'

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错了。

-- reaplace函数 第一个参数为该字段的名称，第二参数为该字段的需要被修改值，第三个参数为该字段修改后的值
update titles_test set 
emp_no = replace(emp_no,10001,10005)
where id = 5

将titles_test表名修改为titles_2017

/*
alter table 表名 add 列名/索引/主键/外键等；
alter table 表名 drop 列名/索引/主键/外键等；
alter table 表名 alter 仅用来改变某列的默认值；
alter table 表名 rename 列名/索引名 to 新的列名/新索引名；
alter table 表名 rename to/as 新表名;
alter table 表名 modify 列的定义但不改变列名；
alter table 表名 change 列名和定义都可以改变。

*/
alter table titles_test rename to titles_2017

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

-- alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
alter table audit 
add constraint fk_no_id foreign key(emp_no)
references employees_test(id)

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

update salaries set salary=salary*1.1
where to_date='9999-01-01'
and emp_no in (
    select emp_no from emp_bonus
)

将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)

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

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

select length("10,A,B") - length(replace("10,A,B",",",""))

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select first_name
from employees 
order by substr(first_name,-2,2)

select first_name 
from employees
order by right(first_name,2)

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

select dept_no,group_concat(emp_no,",") as employees
from dept_emp
group by dept_no

查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。

select avg(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 salary not in 
(select min(salary) from salaries where to_date='9999-01-01')

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

select * from employees limit 5,5

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received

select e.emp_no, de.dept_no, eb.btype, eb.received
from employees e
inner join dept_emp de on e.emp_no = de.emp_no
left join emp_bonus eb on e.emp_no = eb.emp_n

使用含有关键字exists查找未分配具体部门的员工的所有信息。

select * from employees 
where not exists (select emp_no from dept_emp
                  where dept_emp.emp_no = employees.emp_no);

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。当前薪水表示to_date=‘9999-01-01’

-- case x when condition then else end
select employees.emp_no,employees.first_name,employees.last_name,
emp_bonus.btype ,salaries.salary,
(case emp_bonus.btype when 1 then 0.1*salaries.salary 
 when 2 then 0.2*salaries.salary
 else 0.3*salaries.salary end)as bonus
from employees inner join emp_bonus on employees.emp_no=emp_bonus.emp_no
inner join salaries on employees.emp_no=salaries.emp_no
and salaries.to_date='9999-01-01'

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

按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。具体结果如下Demo展示

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

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

select e1.first_name from employees e1
inner join 
(select first_name,row_number() over(order by first_name) rk from employees) e2
on e1.first_name = e2.first_name
where e2.rk%2 == 1

select e1.first_name from employees e1
where(select count(*) from employees e2 where e1.first_name>=e2.first_name )%2 = 1

d为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分

select number from grade
group by number
having count(number) >= 3

输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下:

select id,number,dense_rank() over(order by number desc) as t_rank
from passing_number
order by t_rank,id

select a.id,a.number,
(select count(distinct b.number) from passing_number b 
	where b.number>=a.number ) as t_rank
from passing_number a order by t_rank,a.id;

请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序

select person.id,person.name,task.content
from person left join task on person.id = task.person_id
order by person.id

每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

select email.date,
round(sum (case email.type when 'completed' then 0 else 1 end)*1.0 / count(email.type),3)
from email
inner join user u1 on email.send_id = u1.id and u1.is_blacklist=0
inner join user u2 on email.receive_id = u2.id and u2.is_blacklist=0
group by email.date

请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序

select user_id,max(date) from login group by user_id

请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

select user.name as u_n,client.name as c_n,login.date as d
from login
inner join user on user.id = login.user_id
inner join client on client.id = login.client_id
where (login.user_id,login.date) in 
(select user_id,max(date) from login group by user_id)
order by user.name

请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下

-- 注意distinst
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);

请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，

select l1.date,sum(case when(l1.user_id,l1.date) in 
     (select distinct l2.user_id,min(l2.date) from login l2 group by l2.user_id)
     then 1 else 0 end)
from login l1
group by l1.date
  
select tmp.date,sum(case tmp.rk when 1 then 1 else 0 end) as new
from (select user_id,date,rank() over(partition by user_id order by date) as rk from login) tmp
group by tmp.date

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序

select t1.date,round(count(t2.user_id)/count(t1.user_id),3) p
from
(select user_id,min(date) date from login group by user_id) t1
left join (select user_id,date from login) t2
on t1.user_id = t2.user_id
and t2.date=date_add(t1.date,interval 1 day)
group by t1.date
union
select date,0.000 p from login
where date not in (select min(date) from login group by user_id)
order by date;

牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

select user.name as u_n, login.date, p1.ps_num
from login 
inner join (select user_id,date,
            sum(number) over (partition by user_id order by date) ps_num
            from passing_number)p1
on login.user_id = p1.user_id and login.date=p1.date
inner join user on login.user_id = user.id
order by login.date,user.name

请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):

select job,round(sum(score)*1.0/count(score),3) as avg
from grade group by job
order by avg desc

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:

select g1.* from grade g1
inner join
(
    select id,job,round(sum(score)*1.0/count(score),3) as avg
    from grade group by job
    order by avg desc
) g2
on g1.job = g2.job
where g1.score > g2.avg
order by g1.id

请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:

select g.id,l.name,g.score
from
(
    select id, score,language_id, dense_rank() over(partition by language_id order by score desc) as rk
    from grade 
)g
inner join language l on g.language_id = l.id
where g.rk <=2
order by l.name,g.score desc,g.id

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:

select job,
    case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2,0) end as 'start',
    case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2+1,0) end as 'end'
from(
    select job,count(*) as nums from grade group by job
)g


select job, 
  case when count(*)%2=0 then round(count(*)/2,0) else round((count(*)+1)/2,0) end as start,
  case when count(*)%2=0 then round(count(*)/2+1,0) else round((count(*)+1)/2,0) end as end 
from grade 
group by job

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

select g1.id,g1.job,g1.score,g1.ranking
from (
    select id,job,score,row_number() over(partition by job order by score desc) as 'ranking'
    from grade
)g1
inner join 
(
    select job,
        case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2,0) end as 'start',
        case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2+1,0) end as 'end'
    from(
        select job,count(*) as nums from grade group by job
    )g2
)g3
on g1.job = g3.job 
where g1.ranking between g3.start and g3.end
order by g1.id

末尾

现在数据库中有一张用户交易表order，其中有userid（用户ID）、orderid（订单ID）、amount（订单金额）、paytime（支付时间），请写出对应的SQL语句，查出每个月的新客数（新客指在严选首次支付的用户），当月有复购的新客数，新客当月复购率（公式=当月有复购的新客数/月总新客数）

  create view t1 as
  select userid,strftime('%Y-%m',min(paytime)) as year_month 
  from order group by userid 
  
  -- 每个月新客数
  select t1.year_month,count(t1.userid) as '每月新客数'
  from t1 group by t1.year_month
  
  -- 当月有复购的新客数
  select t2.year_month,count(t2.nums) as '复购的新客数'
  from 
  (
    select t1.year_month,count(o1.userid) as nums
    from t1 inner join order o1
    on o1.userid = t1.userid and strftime('%Y-%m',o1.paytime) = t1.year_month
    group by o1.userid
    having count(o1.userid) > 1
  ) as t2
  group by t2.year_month
  
  -- 新客当月复购率
  select a.year_month, a.每月新客数, b.复购的新客数, 1.0*b.复购的新客数/a.每月新客数 as '当月复购率'
  from 
  (
    	select t1.year_month,count(t1.userid) as '每月新客数'
  		from t1 group by t1.year_month
  ) as a
  (
    	select t2.year_month,count(t2.nums) as '复购的新客数'
  		from 
   		(
        	select t1.year_month,count(o1.userid) as nums  
        	from t1,order o1
  				where o1.userid = t1.userid and strftime('%Y-%m',o1.paytime) = t1.year_month
  				group by o1.userid  having count(o1.userid) > 1
   		)as t2
   		group by t2.year_month
  ) as b
  where a.year_month=b.year_month
  order by a.year_month

jialun0116

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Mysql 练习(牛客网Mysql总结)

Mysql 练习查找最晚入职员工的所有信息select * from employees where hire_date = (select max(hire_date) from employees);查找入职员工时间排名倒数第三的员工的所有信息-- order by 排序-- desc 从大到小-- limit 2,1 从第二条开始读，读一条select * from employees order by hire_date desc limit 2,1;查找各个部门当前(
复制链接

扫一扫

专栏目录