MYSQL
查找最晚入职员工的所有信息
题目描述
查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)
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)
考试分数(一)
题目描述
牛客每次考试完,都会有一个成绩表(grade),如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的平均数,并且按照分数降序排序,结果保留小数点后面3位(3位之后四舍五入):
(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)
ROUND(X)
返回参数X, 其值接近于最近似的整数。
ROUND(X,D)
在有两个参数的情况下,返回 X ,其值保留到小数点后D位,
而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位,可将 D 设为负值。
select job, round(avg(score), 3) as `avg`
from grade
group by job
order by `avg` desc
牛客每个人最近的登录日期
题目描述
牛客每天有很多人登录,请你统计一下牛客每个用户最近登录是哪一天。
有一个登录(login)记录表,简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询每个用户最近一天登录的日子,并且按照user_id升序排序,上面的例子查询结果如下:
查询结果表明:
user_id为2的最近的登录日期在2020-10-13
user_id为3的最近的登录日期也是2020-10-13
select user_id, max(date) as d
from login
group by user_id
order by user_id
找到每个人的任务
题目描述
有一个person表,主键是id,如下:
有一个任务(task)表如下,主键也是id,如下:
请你找到每个人的任务情况,并且输出出来,没有任务的也要输出,而且输出结果按照person的id升序排序,输出情况如下:
select p.id, p.name, t.content
from person p left join task t
on p.id = t.person_id
order by p.id
出现三次及以上相同计分的情况
题目描述
在牛客刷题的小伙伴们都有着牛客积分,积分(grade)表简化可以如下:
id为用户主键id,number代表积分情况,让你写一个sql查询,积分表里面出现三次以及三次以上的积分,查询结果如下:
having字句可以让我们筛选分组之后的各种数据,where字句在聚合前先筛选记录
where>聚合函数(sum,min,max,avg,count)>having
若须引入聚合函数来对group by 结果进行过滤 则只能用having。
select number from grade
group by number
having count(number) >= 3
将titles_test表名修改为titles_2017
题目描述
将titles_test表名修改为titles_2017。
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');
/*mysql中修改表名和表中的字段名都用ALTER*/
alter table titles_test rename titles_2017
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
题目描述
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错了。
CREATE TABLE 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');
查找入职员工时间排名倒数第三的员工所有信息
题目描述
查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
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
));
输入描述:
无
输出描述:
emp_no birth_dat first_name last_name gender hire_date
10005 1955-01-21 Kyoichi Maliniak M 1989-09-12
select * from employees
order by hire_date desc
limit 2, 1
/*
limit 2, 1 === limit 1 offset 2
*/
/*
select * from 表名 limit m,n
limit索引从0开始,m表示索引的位置,n表示limit(限制)的条数
*/
查找所有已经分配部门的员工的last_name和first_name以及dept_no
题目描述
查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
CREATE TABLE dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
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 e.last_name, e.first_name, d.dept_no
from dept_emp d left join employees e
on d.emp_no = e.emp_no
/*
或直接内连接取交集
*/
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
题目描述
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
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
));
select emp_no, count(emp_no) t
from salaries
group by emp_no
having t > 15
/*
严格来说涨幅超过15次,则相应的记录应该超过16次
select emp_no, count(distince from_date)-1 t
*/
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况
题目描述
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
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
));
/*
select distinct salary
from salaries
where to_date = '9999-01-01'
order by salary desc
对于distinct与group by的使用: 1、当对系统的性能高并数据量大时使用group by
2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by
*/
select salary
from salaries
where to_date = '9999-01-01'
group by salary
order by salary desc
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
题目描述
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错了。
CREATE TABLE 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’);
/*
REPLACE(str,from_str,to_str)
在字符串 str 中所有出现的字符串 from_str 均被 to_str替换,然后返回这个字符串
如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况,
将会删除原先的数据,然后再进行添加。
replace into table( col1, col2, col3 ) values ( val1, val2, val3 )
*/
update titles_test set emp_no = replace(emp_no, 10001, 10005)
where id = 5
/*
全字段替换
*/
replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01
题目描述
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
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’);
/*
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
*/
update titles_test set to_date = null, from_date = '2001-01-01'
where to_date = '9999-01-01'
删除emp_no重复的记录,只保留最小的id对应的记录
题目描述
删除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’);
delete from titles_test
where id not in(
select * from(
select min(id)
from titles_test
group by emp_no
) as a
)
/*
要给中间查询派生出的表设置一个别名 a
否则会出现错误:
Every derived table must have its own alias
*/
批量插入数据
题目描述
题目已经先执行了如下语句:
drop table if exists actor;
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update DATETIME NOT NULL)
请你对于表actor批量插入如下数据(不能有2条insert语句哦!)
actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33
insert into actor values(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')
将employees表的所有员工的last_name和first_name拼接起来作为Name
题目描述
将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分
(注:sqllite,字符串拼接为 || 符号,不支持concat函数,mysql支持concat函数)
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
));
/*
concat(str1, str2,...)
返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
concat_ws(separator, str1, str2, ...)
说明:第一个参数指定分隔符。
需要注意的是分隔符不能为null,如果为null,则返回结果为null。
*/
select concat_ws(' ', last_name, first_name)
from employees
获取所有非manager的员工emp_no
题目描述
获取所有非manager的员工emp_no
CREATE TABLE dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
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
));
/* not in + 子查询
select emp_no
from employees
where emp_no not in(
select emp_no
from dept_manager
)
*/
/*
left join + is null
*/
select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where dept_no is null
从titles表获取按照title进行分组
题目描述
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS “titles” (
emp_no
int(11) NOT NULL,
title
varchar(50) NOT NULL,
from_date
date NOT NULL,
to_date
date DEFAULT NULL);
select title, count(title) t
from titles
group by title
having t >= 2
/*
执行顺序:Where, Group By, Having, Order by
*/
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅
题目描述
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
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
));
select (MAX(salary) - MIN(salary)) growth
from salaries
where emp_no = 10001