文章目录
- ** 内连接 左连接 右连接的含义,区别
- ** 内连接和并列查询
- ** GROUP BY,ORDER BY,WHERE,HAVING
- 8. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
- 9. 获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
- 10. 获取所有非manager的员工emp_no
- 11. 获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。
- 12. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
- 13. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略。
- 14. 查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
- 15. 统计出当前各个title类型对应的员工当前(to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。
- ** WHERE 和 HAVING 的区别
- 未完待续
- 建表语句
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;
** 内连接 左连接 右连接的含义,区别
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中 != 不识别,为避免混淆统一使用 <> 。
- 比如:求 test表中num字段 + 5 后的值
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 的区别
-
参考文章:
-
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
);