简单题_查询编号为奇数的用户信息语句怎么写-CSDN博客

本文链接：https://blog.csdn.net/weixin_43857827/article/details/112596598

1、查找入职员工时间排名倒数第三的员工所有信息

在这里插入图片描述

/*
升序(asc)或降序(desc)，默认升序排序。
limit 10 索引前10行
limit 1,10 索引从第二行开始，累计10行，显示2。。。。11
limit 5，10 索引从第六行开始，累计10行，显示6.。。。。15
*/
select * from employees
order by hire_date desc
limit 2,1;

2、查找所有已经分配部门的员工信息

在这里插入图片描述

select last_name,first_name,dept_no 
from employees as e
(inner) join dept_emp as d
on e.emp_no = d.emp_no;

3、查找薪水`变动`超过15次的员工

在这里插入图片描述

题解：
1.一条记录就算一次涨幅(严格意义上，从第二条记录算起才算一次涨幅)
2.任何一条记录都算一次涨幅(严格意义上，必须比上一条记录的工资高才算一次涨幅)

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

补充：如果考虑第二条记录算起才算一次涨幅，应该写：

select emp_no, count(distinct from_date)-1 as t
from salaries
group by emp_no
having t>15

补充：如果考虑严格意义上的涨幅，应该写：

select a.emp_no, count() 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

4、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况

在这里插入图片描述

SELECT DISTINCT salary 
FROM salaries 
WHERE to_date = '9999-01-01' 
ORDER BY salary DESC

5、获取所有非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`));

插入：

INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'1990-08-05','9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'1989-09-12','9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'1986-12-01','9999-01-01');
INSERT INTO dept_manager VALUES('d005',10010,'1996-11-24','2000-06-26');
INSERT INTO dept_manager VALUES('d006',10010,'2000-06-26','9999-01-01');

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

在这里插入图片描述
题解：

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

6、查找最晚入职员工的所有信息

在这里插入图片描述

SELECT *
FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees)

7、从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);

如插入：

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10005,'Senior Staff','1996-09-12','9999-01-01');
INSERT INTO titles VALUES(10005,'Staff','1989-09-12','1996-09-12');
INSERT INTO titles VALUES(10006,'Senior Engineer','1990-08-05','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');
INSERT INTO titles VALUES(10007,'Staff','1989-02-10','1996-02-11');
INSERT INTO titles VALUES(10008,'Assistant Engineer','1998-03-11','2000-07-31');
INSERT INTO titles VALUES(10009,'Assistant Engineer','1985-02-18','1990-02-18');
INSERT INTO titles VALUES(10009,'Engineer','1990-02-18','1995-02-18');
INSERT INTO titles VALUES(10009,'Senior Engineer','1995-02-18','9999-01-01');
INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');
INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');

在这里插入图片描述

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

8、查找employees表所有emp_no为奇数

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

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`));

如插入：

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

在这里插入图片描述

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

9、查找员工编号emp_no为10001

查找员工编号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) as growth
from salaries
where emp_no=10001;

10、将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`));

在这里插入图片描述

select concat(last_name,' ',first_name) Name
from employees

11、批量插入数据

题目描述
题目已经先执行了如下语句:

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语句哦!)
在这里插入图片描述

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

12、删除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表为
在这里插入图片描述
注意：

在MySQL中还有一个坑，需要给子查询添加别名，不然会抛出错误
MySQL的UPDATE或DELETE中子查询不能为同一张表，可将查询结果再次SELECT
报错：

DELETE FROM titles_test WHERE id NOT IN 
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)

修改：

DELETE FROM titles_test 
WHERE id NOT IN (
    select *
    from(
        SELECT MIN(id) 
        FROM titles_test 
        GROUP BY emp_no) ID)

13、将所有to_date为9999-01-01的全部更新为NULL

将所有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 titles_test 
SET to_date = NULL, from_date = '2001-01-01' 
WHERE to_date = '9999-01-01';

14、将id=5以及emp_no=10001的行数据替换成id=5

将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(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。

update  titles_test 
set emp_no = replace(emp_no,'10001','10005') 
where id=5

15、将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');

修改表名称：rename table 老表名 to 新表名

rename table titles_test to  titles_2017

16、出现三次以上相同积分的情况

在牛客刷题的小伙伴们都有着牛客积分，积分(grade)表简化可以如下:
在这里插入图片描述
id为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分，查询结果如下:

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

17、找到每个人的任务

有一个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

18、获取薪水第二多的员工的emp_no以及其对应的薪水salary

题目描述
有一个薪水表salaries简况如下:
在这里插入图片描述
请你获取薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary
from salaries
order by salary desc
limit 1,1

简单题

1、查找入职员工时间排名倒数第三的员工所有信息

2、查找所有已经分配部门的员工信息

3、查找薪水变动超过15次的员工

4、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况

5、获取所有非manager的员工emp_no

6、查找最晚入职员工的所有信息

7、从titles表获取按照title进行分组

8、查找employees表所有emp_no为奇数

9、查找员工编号emp_no为10001

10、将employees表的所有员工的last_name和first_name拼接起来作为Name

11、批量插入数据

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

13、将所有to_date为9999-01-01的全部更新为NULL

14、将id=5以及emp_no=10001的行数据替换成id=5

15、将titles_test表名修改为titles_2017。

16、出现三次以上相同积分的情况

17、找到每个人的任务

18、获取薪水第二多的员工的emp_no以及其对应的薪水salary

3、查找薪水`变动`超过15次的员工