8.MySQL知识巩固-牛客网练习题

 

目录

 SQL228 批量插入数据

描述

SQL202 找出所有员工当前薪水salary情况

描述

示例1

SQL195 查找最晚入职员工的所有信息描述

示例1

SQL196 查找入职员工时间排名倒数第三的员工所有信息描述

SQL201查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

描述

---

 SQL228 批量插入数据

如果忘记了可以参考我这一篇文章

6.MySQL的增删改查-CSDN博客

批量插入数据_牛客题霸_牛客网 (nowcoder.com)https://www.nowcoder.com/practice/51c12cea6a97468da149c04b7ecf362e?tpId=82&tqId=29802&tPage=2&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking

描述

对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

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

输入：

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)

输出：

1|PENELOPE|GUINESS|2006-02-15 12:34:33
2|NICK|WAHLBERG|2006-02-15 12:34:33

插入语句语法：

insert into 表名 (字段名，，，，) values (要插入的数据字段对齐，，，)

# INSERT INTO actor(actor_id,
#                   first_name,
#                   last_name,
#                   last_update)
# VALUES(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");

SQL202 找出所有员工当前薪水salary情况

如果忘记了可以参考我这篇文章：

6.MySQL的增删改查-CSDN博客

找出所有员工当前薪水salary情况_牛客题霸_牛客网 (nowcoder.com)https://www.nowcoder.com/practice/ae51e6d057c94f6d891735a48d1c2397?tpId=82&tqId=29760&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking

描述

有一个薪水表，salaries简况如下:

emp_no	salary	from_date	to_date
10001	72527	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示，以上例子输出如下:

salary

72527

43311

示例1

输入：

drop table if exists  `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,72527,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

输出：

72527
43311

这是答案哦

Select distinct salary from salaries order by salary desc;

SQL195 查找最晚入职员工的所有信息描述

有一个员工employees表简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

请你查找employees里最晚入职员工的所有信息，以上例子输出如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10004	1954-05-01	Christian	Koblick	M	1986-12-01

示例1

输入：

drop table if exists  `employees` ; 
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');

输出：

10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15

答案在这哦

 方法一：我们可以按照入职时间进行降序，入职最晚的就是时间最大的，

从 0 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

select * from employees order by hire_date desc limit 1;

方法二：

-- 从 s 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n

select * from employees order by hire_date desc limit 1 offset 0;

方法三：

我们可以使用子查询进行操作

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

SQL196 查找入职员工时间排名倒数第三的员工所有信息描述

查找入职员工时间排名倒数第三的员工所有信息_牛客题霸_牛客网 (nowcoder.com)https://www.nowcoder.com/practice/ec1ca44c62c14ceb990c3c40def1ec6c?tpId=82&tqId=29754&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking

有一个员工employees表简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

请你查找employees里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26

注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。

既然不止一个人那我们就选择distinct关键字进行去重

SELECT * 
FROM employees
WHERE hire_date = (
    SELECT DISTINCT hire_date 
    FROM employees
    ORDER BY hire_date DESC      
    LIMIT 1 OFFSET 2              
);    

SQL201查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

描述

有一个薪水表，salaries简况如下:

emp_no	salary	from_date	to_date
10001	60117	1986-06-26	1987-06-26
10001	62102	1987-06-26	1988-06-25
10001	66074	1988-06-25	1989-06-25
10001	66596	1989-06-25	1990-06-25
10001	66961	1990-06-25	1991-06-25
10001	71046	1991-06-25	1992-06-24
10001	74333	1992-06-24	1993-06-24
10001	75286	1993-06-24	1994-06-24
10001	75994	1994-06-24	1995-06-24
10001	76884	1995-06-24	1996-06-23
10001	80013	1996-06-23	1997-06-23
10001	81025	1997-06-23	1998-06-23
10001	81097	1998-06-23	1999-06-23
10001	84917	1999-06-23	2000-06-22
10001	85112	2000-06-22	2001-06-22
10001	85097	2001-06-22	2002-06-22
10002	72527	1996-08-03	1997-08-03

请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t，以上例子输出如下:

emp_no	t
10001	16

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