mysql 语句牛客网练习1-20-上

最新推荐文章于 2021-08-18 21:36:18 发布

落叶无痕00

最新推荐文章于 2021-08-18 21:36:18 发布

阅读量288

点赞数 1

分类专栏：面试

本文链接：https://blog.csdn.net/qq_37638061/article/details/99894434

版权

面试专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章目录

建表语句

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

1. 查找最晚入职员工的所有信息

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

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

select * from employees where hire_date =
	(select distinct hire_date from employees 
		order by hire_date desc 
		limit 2,1);

3. 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_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 `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 s.*,d.dept_no
	from salaries s, dept_manager d
	where s.emp_no = d.emp_no;

4. 查找所有已经分配部门的员工的last_name和first_name，不包括展示没有分配具体部门的员工

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 last_name,first_name,dept_no
     from employees e,dept_emp d
     where e.emp_no = d.emp_no;

别人的：
select e.last_name,e.first_name,d.dept_no
    from employees e
    inner join dept_emp d
    on e.emp_no = d.emp_no;

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

别人家的：
select e.last_name,e.first_name,d.dept_no
    from employees e
    left join dept_emp d
    on d.emp_no = e.emp_no;

** 内连接左连接右连接的含义，区别

大佬博客：图解MySQL 内连接、外连接、左连接、右连接、全连接……太多了

6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_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`));

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

答案

# 需要使用 employees 中的 emp_no，因为salaries中，因为薪资的变动，而出现一个emp_no出现多次况且 salaries 的主键是一个联合主键
	
	SELECT e.emp_no,s.salary
		FROM employees e,salaries s
		WHERE e.emp_no = s.emp_no AND e.hire = s.from_date
		ORDER BY e.emp_no DESC;
		
别人家的：
	SELECT e.emp_no,s.salary
		FROM employees e,
		INNER JOIN salaries s
		ON e.emp_no = s.emp_no AND e.hire = s.from_date
		ORDER BY e.emp_no DESC;

** 内连接和并列查询

内连接是取左右两张表的交集形成一个新表，
并列查询：用FROM并列两张表后仍是两张表，
如果还要对新表进行操作则要用内连接。从效率上看FROM并列查询比较快，因为不用形成新表。
本题从效果上看，这两方法没啥区别。

7. 查找薪水涨幅超过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`));

答案

自己的：考虑不周全，写的其实薪水变动了15次，不管其增减
SELECT emp_no,count(emp_no) 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;

** GROUP BY，ORDER BY，WHERE，HAVING

ORDER BY：排序
GROUP BY：分组
WHERE 在 GROUP BY 之前，SQL会在分组之间计算WHERE语句。
HAVING 在 GROUP BY 之后，SQL会在分组之后计算HAVING语句。

8. 找出所有员工当前(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;
    
别人的：
SELECT salary 
	FROM salaries
    WHERE to_date = '9999-01-01'
    GROUP BY salary 
    ORDER BY salary DESC;

去重操作的话，小表一般使用 DISTINCT ，大表用 DISTINCT 效率不高，一般使用 GROUP BY 解决问题。

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’

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 `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 d.dept_no,d.emp_no,s.salary
	FROM dept_manager d,salaries s
	WHERE d.emp_no = s.emp_no
	AND d.date = '9999-01-01'
	AND s.date = '9999-01-01';
	
别人的：
SELECT d.dept_no,d.emp_no,s.salary
	FROM dept_manager d
	INNNER JOIN salary s
	ON d.emp_no = s.emp_no
	AND d.date = '9999-01-01'
	AND s.date = '9999-01-01';

还是喜欢用并列查询，不习惯使用连接（好吧，是不会使用连接）

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

答案

自己的：
SELECT emp_no
	FROM employees
	WHERE emp _no
	NOT IN(
		SELECT emp_no
		FROM dept_mangager;
	);
	
别人的：
SELECT d.emp_no
	FROM dept_manager d
	LEFT JOIN employees e
	ON d.emp_no = e.emp_no
	WHERE d.dept_no IS NULL;

别人的：
SELECT emp_no
	FROM employees
EXCEPT
	SELECT emp_no
FROM dept_manager;

EXPECT：集合差运算；UNION：集合并运算；INTERSECT：集合交运算

11. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 `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`));

答案

大佬的，自己的没过（我对这个to_date = '9999-01-01'很反感，不知道为什么）
SELECT e.emp_no,m.emp_no AS manager_no
    FROM dept_emp e
    LEFT JOIN dept_manager m
    ON e.dept_no = m.dept_no
    WHERE m.to_date = '9999-01-01'
    AND e.to_date = '9999-01-01'
    AND e.emp_no <> m.emp_no

12. 从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;

13. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

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
    WHERE emp_no IN 
        ( SELECT DISTINCT emp_no
            FROM titles
        );
      
别人的：
SELECT title,COUNT(DISTINCT emp_no) t
	FROM titles
	GROUP BY title
	HAVING t >= 2;
	
我没看懂题意。。。

14. 查找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`));

答案

SELECT *
    FROM employees
    WHERE emp_no % 2 <> 0
    AND last_name != 'Mary'
    ORDER BY hire_date DESC;

补充：mysql中加减乘除以及求余运算，跟平时的一样，
- 比如：求 test表中num字段 + 5 后的值SELECT num + 5 FROM test;
- 等于或者不等于的符号不太一样，等于的话是一个**=====**，mysql中不等于符号号 <> 和 **!=**都阔以。Sql Server中 != 不识别，为避免混淆统一使用 <> 。

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

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

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 t.title,avg(salary)  avg
    FROM salaries s,titles t
    WHERE t.emp_no = s.emp_no
    AND s.to_date = '9999-01-01'
    AND t.to_date = '9999-01-01';
    
别人的1：
SELECT t.title,avg(salary) avg
    FROM salaries s,titles t
    WHERE t.emp_no = s.emp_no
    AND s.to_date = '9999-01-01'
    AND t.to_date = '9999-01-01'
    GROUP BY t.title;
    
别人的2：
SELECT t.title,avg(salary) avg
    FROM titles t
    INNER JOIN salaries s
    ON t.emp_no = s.emp_no
    AND s.to_date = '9999-01-01'
    AND t.to_date = '9999-01-01'
    GROUP BY t.title;

** WHERE 和 HAVING 的区别

参考文章：
1. 正确理解MySQL中的where和having的区别
2. 为什么不能再where语句中使用聚合函数
HAVING 不是必须配合 GROUP BY 一起使用

where 和 having 都可以使用的场景

select id,name from tb_user where id > 2;

select id,name from tb_user having id > 2;

解释：上面的having可以用的前提是已经是筛选出了goods_price字段，在这种情况下having和where字段是等效的

只可以使用where，不可以使用having

select name from tb_user where id > 2;

//会报错！因为前面条件没有筛选出 id 字段
select name from tb_user having id > 2;

只可以使用 having，不可以使用 where

//会报错！因为 avg 在 tb_user 表中
select id avg(score) avg from tb_user where avg > 60;

select id,avg(score) avg from tb_user having avg > 60;

总结：
- where：是一个约束声明，使用 where 来约束之前数据库的数据，where 是在结果返回之前起作用的，且 where 中不能使用聚合函数
- having：是一个过滤声明，是在查询结果集以后对查询结果进行的过滤操作，在having中可以使用聚合函数
- 聚合函数：（即mysql内置函数）MAX()、MIN()、COUNT()、SUM()、AVG()。（SUM、AVG只能对数字类型数据做计算；MAX、MIN可用于数值、字符串或是日期时间数据类型；COUNT可用于任何数据类型，因为仅仅只是计数）
- 为什么 where 中不能使用聚合函数？
  - sql语句的执行过程是：from --> where --> group by --> having --> irder by --> select;
  - 聚合函数是针对结果集进行的，但是where条件并不是在查询结果集之后运行，所以聚合函数放在where语句中，会出现错误。
  - having不一样，having是针对结果集做筛选的，所以我们一般吧组函数放在having中，用having来代替where，having一般跟在group by后面。

16. 获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水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 emp_no,salary
	FROM salaries
	WHERE to_date = '9999-01-01'
	AND salary = (
		SELECT DISTINCT salary
		FROM salaries
		WHERE to_date = '9999-01-01'
		ORDER BY salary DESC
		LIMIT 1,1
	);

未完待续

落叶无痕00

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 语句牛客网练习1-20-上

文章目录1. 查找最晚入职员工的所有信息2. 查找入职员工时间排名倒数第三的员工所有信息3. 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no4. 查找所有已经分配部门的员工的last_name和first_name，不包括展示没有分配具体部门的员工5. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工** 内...
复制链接

扫一扫