MySQL练习1
2020-04-06 本文已影响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`));
-- Q1:查找最晚入职员工的所有信息
-- 子查询
SELECT
*
FROM
employees
WHERE
hire_date=(SELECT MAX(hire_date) FROM employees);
-- Q2:查找入职员工时间排名倒数第三的员工所有信息
-- 不会
-- 思路：1.按时间排出来（降序排第3） 2.然后找其对应信息。。。不会了
-- 正确答案：
/*补充知识点：LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。 */
-- 1.按时间降序排，并且要distinct去重，
/*假设 5-23（入职最晚日期）入职的有a,b,c 3人；
5-22（入职第二晚日期）入职的有d,e 2人；
5-21(入职倒数第三晚)入职的有f,g,h 3人；
5-21前入职的若干...
若不加distinct去重，那么按照日期倒序，limit 2,1（从倒数第2行开始，取一条数据）的查询结果为 5-23
加了distinct去重，会按入职日期进行分组，多个相同入职日期会分为一组，这样limit 2,1的结果即为 5-21。*/
-- way1：
SELECT
*
FROM
employees
WHERE
hire_date=(SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);
-- way2：
SELECT *
FROM employees
where hire_date=(
select hire_date
from employees
group by hire_date
order by hire_date desc
limit 2,1
);
-- Q3:查询当前薪水详情以及部门编号dep_no
-- 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号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.to_date = '9999-01-01'
and d.to_date = '9999-01-01'
and s.emp_no = d.emp_no ;
-- Q4:题目描述
查找所有已经分配部门的员工的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`));
-- employees表中的dept_no有可能为NULL；但dep_emp表中的dept_no一定不为NULL，
-- 所以使用内部联结，去除dept_no为NULL的employees。
SELECT
e.last_name,e.first_name,d.dept_no
FROM
employees e,
dept_emp d
WHERE
e.emp_no=d.emp_no;
-- Q5:查找所有员工的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
e.emp_no=d.emp_no;
-- Q6:查找所有员工入职时候的薪水情况，给出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`));
SELECT
s.emp_no,s.salary
FROM
salaries s,
employees e
WHERE
s.emp_no=e.emp_no AND e.hire_date=s.from_date
ORDER BY s.emp_no DESC;
-- Q7:查找薪水涨幅超过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)
FROM salaries
GROUP BY emp_no
HAVING COUNT(emp_no) > 15;
-- Q8：找出所有员工当前(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 emp_no,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;
-- Q9:获取所有部门当前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.to_date='9999-01-01'
AND s.to_date='9999-01-01';
-- Q10:获取所有非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`));
-- way1：
SELECT
emp_no
FROM
employees
WHERE
emp_no NOT IN (SELECT emp_no FROM dept_manager);
SELECT
e.emp_no
FROM
employees e LEFT JOIN dept_manager d
ON
e.emp_no = d.emp_no
WHERE
d.emp_no IS NULL;
-- Q11:获取所有员工当前的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`));
SELECT de.emp_no,dm.emp_no AS manager_no
FROM dept_manager AS dm,dept_emp AS de
WHERE de.emp_no <> dm.emp_no
AND de.dept_no = dm.dept_no
AND dm.to_date='9999-01-01';
-- 或者
SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no
WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no
