数据库-SQL语句练习【已完成26题，还剩35题】
练习题链接：https://www.nowcoder.com/ta/sql?page=0
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 1+1 | 1+1 | 1-1 | 1-1 | 1 | 1 | ||||||||||
| 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 |
| 1 | 1 | 4 | 1 | 0.5 | 1 | ||||||||||
| 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 |
| 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | |||
练习数据库内容：
创建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`));
插入salaries内容
INSERT INTO salaries VALUES(10001,60117,\'1986-06-26\',\'1987-06-26\'); INSERT INTO salaries VALUES(10001,62102,\'1987-06-26\',\'1988-06-25\'); INSERT INTO salaries VALUES(10001,66074,\'1988-06-25\',\'1989-06-25\'); INSERT INTO salaries VALUES(10001,66596,\'1989-06-25\',\'1990-06-25\'); INSERT INTO salaries VALUES(10001,66961,\'1990-06-25\',\'1991-06-25\'); INSERT INTO salaries VALUES(10001,71046,\'1991-06-25\',\'1992-06-24\'); INSERT INTO salaries VALUES(10001,74333,\'1992-06-24\',\'1993-06-24\'); INSERT INTO salaries VALUES(10001,75286,\'1993-06-24\',\'1994-06-24\'); INSERT INTO salaries VALUES(10001,75994,\'1994-06-24\',\'1995-06-24\'); INSERT INTO salaries VALUES(10001,76884,\'1995-06-24\',\'1996-06-23\'); INSERT INTO salaries VALUES(10001,80013,\'1996-06-23\',\'1997-06-23\'); INSERT INTO salaries VALUES(10001,81025,\'1997-06-23\',\'1998-06-23\'); INSERT INTO salaries VALUES(10001,81097,\'1998-06-23\',\'1999-06-23\'); INSERT INTO salaries VALUES(10001,84917,\'1999-06-23\',\'2000-06-22\'); INSERT INTO salaries VALUES(10001,85112,\'2000-06-22\',\'2001-06-22\'); INSERT INTO salaries VALUES(10001,85097,\'2001-06-22\',\'2002-06-22\'); INSERT INTO salaries VALUES(10001,88958,\'2002-06-22\',\'9999-01-01\'); INSERT INTO salaries VALUES(10002,72527,\'1996-08-03\',\'1997-08-03\'); INSERT INTO salaries VALUES(10002,72527,\'1997-08-03\',\'1998-08-03\'); INSERT INTO salaries VALUES(10002,72527,\'1998-08-03\',\'1999-08-03\'); INSERT INTO salaries VALUES(10002,72527,\'1999-08-03\',\'2000-08-02\'); INSERT INTO salaries VALUES(10002,72527,\'2000-08-02\',\'2001-08-02\'); INSERT INTO salaries VALUES(10002,72527,\'2001-08-02\',\'9999-01-01\'); INSERT INTO salaries VALUES(10003,40006,\'1995-12-03\',\'1996-12-02\'); INSERT INTO salaries VALUES(10003,43616,\'1996-12-02\',\'1997-12-02\'); INSERT INTO salaries VALUES(10003,43466,\'1997-12-02\',\'1998-12-02\'); INSERT INTO salaries VALUES(10003,43636,\'1998-12-02\',\'1999-12-02\'); INSERT INTO salaries VALUES(10003,43478,\'1999-12-02\',\'2000-12-01\'); INSERT INTO salaries VALUES(10003,43699,\'2000-12-01\',\'2001-12-01\'); INSERT INTO salaries VALUES(10003,43311,\'2001-12-01\',\'9999-01-01\'); INSERT INTO salaries VALUES(10004,40054,\'1986-12-01\',\'1987-12-01\'); INSERT INTO salaries VALUES(10004,42283,\'1987-12-01\',\'1988-11-30\'); INSERT INTO salaries VALUES(10004,42542,\'1988-11-30\',\'1989-11-30\'); INSERT INTO salaries VALUES(10004,46065,\'1989-11-30\',\'1990-11-30\'); INSERT INTO salaries VALUES(10004,48271,\'1990-11-30\',\'1991-11-30\'); INSERT INTO salaries VALUES(10004,50594,\'1991-11-30\',\'1992-11-29\'); INSERT INTO salaries VALUES(10004,52119,\'1992-11-29\',\'1993-11-29\'); INSERT INTO salaries VALUES(10004,54693,\'1993-11-29\',\'1994-11-29\'); INSERT INTO salaries VALUES(10004,58326,\'1994-11-29\',\'1995-11-29\'); INSERT INTO salaries VALUES(10004,60770,\'1995-11-29\',\'1996-11-28\'); INSERT INTO salaries VALUES(10004,62566,\'1996-11-28\',\'1997-11-28\'); INSERT INTO salaries VALUES(10004,64340,\'1997-11-28\',\'1998-11-28\'); INSERT INTO salaries VALUES(10004,67096,\'1998-11-28\',\'1999-11-28\'); INSERT INTO salaries VALUES(10004,69722,\'1999-11-28\',\'2000-11-27\'); INSERT INTO salaries VALUES(10004,70698,\'2000-11-27\',\'2001-11-27\'); INSERT INTO salaries VALUES(10004,74057,\'2001-11-27\',\'9999-01-01\'); INSERT INTO salaries VALUES(10005,78228,\'1989-09-12\',\'1990-09-12\'); INSERT INTO salaries VALUES(10005,82621,\'1990-09-12\',\'1991-09-12\'); INSERT INTO salaries VALUES(10005,83735,\'1991-09-12\',\'1992-09-11\'); INSERT INTO salaries VALUES(10005,85572,\'1992-09-11\',\'1993-09-11\'); INSERT INTO salaries VALUES(10005,85076,\'1993-09-11\',\'1994-09-11\'); INSERT INTO salaries VALUES(10005,86050,\'1994-09-11\',\'1995-09-11\'); INSERT INTO salaries VALUES(10005,88448,\'1995-09-11\',\'1996-09-10\'); INSERT INTO salaries VALUES(10005,88063,\'1996-09-10\',\'1997-09-10\'); INSERT INTO salaries VALUES(10005,89724,\'1997-09-10\',\'1998-09-10\'); INSERT INTO salaries VALUES(10005,90392,\'1998-09-10\',\'1999-09-10\'); INSERT INTO salaries VALUES(10005,90531,\'1999-09-10\',\'2000-09-09\'); INSERT INTO salaries VALUES(10005,91453,\'2000-09-09\',\'2001-09-09\'); INSERT INTO salaries VALUES(10005,94692,\'2001-09-09\',\'9999-01-01\'); INSERT INTO salaries VALUES(10006,43311,\'1990-08-05\',\'1991-08-05\'); INSERT INTO salaries VALUES(10006,43311,\'1991-08-05\',\'1992-08-04\'); INSERT INTO salaries VALUES(10006,43311,\'1992-08-04\',\'1993-08-04\'); INSERT INTO salaries VALUES(10006,43311,\'1993-08-04\',\'1994-08-04\'); INSERT INTO salaries VALUES(10006,43311,\'1994-08-04\',\'1995-08-04\'); INSERT INTO salaries VALUES(10006,43311,\'1995-08-04\',\'1996-08-03\'); INSERT INTO salaries VALUES(10006,43311,\'1996-08-03\',\'1997-08-03\'); INSERT INTO salaries VALUES(10006,43311,\'1997-08-03\',\'1998-08-03\'); INSERT INTO salaries VALUES(10006,43311,\'1998-08-03\',\'1999-08-03\'); INSERT INTO salaries VALUES(10006,43311,\'1999-08-03\',\'2000-08-02\'); INSERT INTO salaries VALUES(10006,43311,\'2000-08-02\',\'2001-08-02\'); INSERT INTO salaries VALUES(10006,43311,\'2001-08-02\',\'9999-01-01\'); INSERT INTO salaries VALUES(10007,56724,\'1989-02-10\',\'1990-02-10\'); INSERT INTO salaries VALUES(10007,60740,\'1990-02-10\',\'1991-02-10\'); INSERT INTO salaries VALUES(10007,62745,\'1991-02-10\',\'1992-02-10\'); INSERT INTO salaries VALUES(10007,63475,\'1992-02-10\',\'1993-02-09\'); INSERT INTO salaries VALUES(10007,63208,\'1993-02-09\',\'1994-02-09\'); INSERT INTO salaries VALUES(10007,64563,\'1994-02-09\',\'1995-02-09\'); INSERT INTO salaries VALUES(10007,68833,\'1995-02-09\',\'1996-02-09\'); INSERT INTO salaries VALUES(10007,70220,\'1996-02-09\',\'1997-02-08\'); INSERT INTO salaries VALUES(10007,73362,\'1997-02-08\',\'1998-02-08\'); INSERT INTO salaries VALUES(10007,75582,\'1998-02-08\',\'1999-02-08\'); INSERT INTO salaries VALUES(10007,79513,\'1999-02-08\',\'2000-02-08\'); INSERT INTO salaries VALUES(10007,80083,\'2000-02-08\',\'2001-02-07\'); INSERT INTO salaries VALUES(10007,84456,\'2001-02-07\',\'2002-02-07\'); INSERT INTO salaries VALUES(10007,88070,\'2002-02-07\',\'9999-01-01\'); INSERT INTO salaries VALUES(10008,46671,\'1998-03-11\',\'1999-03-11\'); INSERT INTO salaries VALUES(10008,48584,\'1999-03-11\',\'2000-03-10\'); INSERT INTO salaries VALUES(10008,52668,\'2000-03-10\',\'2000-07-31\'); INSERT INTO salaries VALUES(10009,60929,\'1985-02-18\',\'1986-02-18\'); INSERT INTO salaries VALUES(10009,64604,\'1986-02-18\',\'1987-02-18\'); INSERT INTO salaries VALUES(10009,64780,\'1987-02-18\',\'1988-02-18\'); INSERT INTO salaries VALUES(10009,66302,\'1988-02-18\',\'1989-02-17\'); INSERT INTO salaries VALUES(10009,69042,\'1989-02-17\',\'1990-02-17\'); INSERT INTO salaries VALUES(10009,70889,\'1990-02-17\',\'1991-02-17\'); INSERT INTO salaries VALUES(10009,71434,\'1991-02-17\',\'1992-02-17\'); INSERT INTO salaries VALUES(10009,74612,\'1992-02-17\',\'1993-02-16\'); INSERT INTO salaries VALUES(10009,76518,\'1993-02-16\',\'1994-02-16\'); INSERT INTO salaries VALUES(10009,78335,\'1994-02-16\',\'1995-02-16\'); INSERT INTO salaries VALUES(10009,80944,\'1995-02-16\',\'1996-02-16\'); INSERT INTO salaries VALUES(10009,82507,\'1996-02-16\',\'1997-02-15\'); INSERT INTO salaries VALUES(10009,85875,\'1997-02-15\',\'1998-02-15\'); INSERT INTO salaries VALUES(10009,89324,\'1998-02-15\',\'1999-02-15\'); INSERT INTO salaries VALUES(10009,90668,\'1999-02-15\',\'2000-02-15\'); INSERT INTO salaries VALUES(10009,93507,\'2000-02-15\',\'2001-02-14\'); INSERT INTO salaries VALUES(10009,94443,\'2001-02-14\',\'2002-02-14\');
创建dept_emp表格
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`));
插入dept_emp内容
INSERT INTO dept_emp VALUES(10001,\'d001\',\'1986-06-26\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10002,\'d001\',\'1996-08-03\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10003,\'d004\',\'1995-12-03\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10004,\'d004\',\'1986-12-01\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10005,\'d003\',\'1989-09-12\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10006,\'d002\',\'1990-08-05\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10007,\'d005\',\'1989-02-10\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10008,\'d005\',\'1998-03-11\',\'2000-07-31\'); INSERT INTO dept_emp VALUES(10009,\'d006\',\'1985-02-18\',\'9999-01-01\'); INSERT INTO dept_emp VALUES(10010,\'d005\',\'1996-11-24\',\'2000-06-26\'); INSERT INTO dept_emp VALUES(10010,\'d006\',\'2000-06-26\',\'9999-01-01\');
创建titles表格
CREATE TABLE `titles` ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL);
插入titles内容
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\');
创建 departments表格
CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`));
插入departments内容
INSERT INTO departments VALUES(\'d001\',\'Marketing\'); INSERT INTO departments VALUES(\'d002\',\'Finance\'); INSERT INTO departments VALUES(\'d003\',\'Human Resources\'); INSERT INTO departments VALUES(\'d004\',\'Production\'); INSERT INTO departments VALUES(\'d005\',\'Development\'); INSERT INTO departments VALUES(\'d006\',\'Quality Management\');
创建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`));
插入employees内容
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\');
1，查找最晚入职员工的所有信息
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;
2，查找入职员工时间排名倒数第三的员工所有信息
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;
3，查找当前薪水详情以及部门编号dept_no
查找各个部门当前(to_date=\'9999-01-01\')领导当前薪水详情以及其对应部门编号dept_no
错误尝试第二次【原因：①表的先后出错；②to_date两次限定条件】
SELECT d.emp_no,s.salary,s.from_date,s.to_date,d.dept_no FROM dept_manager AS d INNER JOIN salaries AS s ON d.emp_no=s.emp_no WHERE d.to_date=\'9999-01-01\';
正确语句：
SELECT s.*,d.dept_no FROM salaries AS s INNER JOIN dept_manager AS d ON d.emp_no=s.emp_no WHERE d.to_date=\'9999-01-01\' AND s.to_date=\'9999-01-01\';
关于为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？
-----因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前，而dept_manager是因为有领导会离职，to_date时间不一定是9999-01-01，所以要过滤过离职的领导
4，查找所有已经分配部门的员工的last_name和first_name
错误尝试第二次【原因查找到：，的中文标点导致的】
SELECT e.last_name，e.first_name，d.dept_no FROM dept_emp AS d INNER JOIN employees AS e ON e.emp_no=d.emp_no;
正确的语句：
SELECT e.last_name,e.first_name,d.dept_no FROM dept_emp AS d INNER JOIN employees AS e ON e.emp_no=d.emp_no;
5，查找所有员工的last_name和first_name以及对应部门编号dept_no【LEFT ,RIGHT ,INNER JOIN】
SELECT e.last_name,e.first_name,d.dept_no FROM employees AS e LEFT JOIN dept_emp AS d ON e.emp_no=d.emp_no;
6，查找所有员工入职时候的薪水情况
第二次错误尝试【错误原因：内连接条件与WHERE条件混淆】
SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no=s.emp_no WHERE e.hire_date=s.to_date ORDER BY emp_no DESC;
正确语句1：【内连接完成】
SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no=s.emp_no AND e.hire_date=s.from_date ORDER BY e.emp_no DESC;
正确语句2：【，号并列连接完成】
SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date ORDER BY e.emp_no DESC
解析：
内连接是取左右两张表的交集形成一个新表，用FROM并列两张表后仍然还是两张表。
如果还要对新表进行操作则要用内连接。从效率上看应该FROM并列查询比较快，因为不用形成新表。本题从效果上看两个方法没区别。
7，查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
第二次尝试错误【错误原因：把问题想复杂了】
SELECT s.emp_no,COUNT(s.emp) t FROM salaries s INNER JOIN salaries s1 ON s.from_date=s1.to_date GROUP BY emp_no HAVING t>14;
正确语句：
SELECT s.emp_no,COUNT(s.emp_no) t FROM salaries s GROUP BY s.emp_no HAVING t>15;
注意：
group by与order by有什么区别，order by就是排序。而group by就是分组，举个例子好说点，group by 单位名称
这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。
这样可以更好的分下类，更好看一些。
还有就是为什么没有用where而是用的having，记住下面的两句话就好了。
WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。
8，找出所有员工当前薪水salary情况
找出所有员工当前(to_date=\'9999-01-01\')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
SELECT DISTINCT s.salary FROM salaries s WHERE s.to_date=\'9999-01-01\' ORDER BY s.salary DESC;
更优化的语句：
SELECT s.salary FROM salaries s WHERE s.to_date=\'9999-01-01\' GROUP BY s.salary ORDER BY s.salary DESC;
注意：
大部分人都是用distinct，但是大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。
简单一点理解，在单表的时候使用distinct，多表的时候使用group by，虽然一般使用group by ，但还是要知道distinct的用法
9，获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=\'9999-01-01\'
两表联查
SELECT d.dept_no,s.emp_no,s.salary FROM dept_manager d,salaries s WHERE d.emp_no=s.emp_no AND s.to_date=\'9999-01-01\' AND d.to_date=\'9999-01-01\';
内连接
SELECT d.dept_no,s.emp_no,s.salary FROM dept_manager d INNER JOIN salaries s ON d.emp_no=s.emp_no WHERE s.to_date=\'9999-01-01\' AND d.to_date=\'9999-01-01\';
10，获取所有非manager的员工emp_no
NOT IN条件限制
SELECT e.emp_no FROM employees e WHERE e.emp_no NOT IN( SELECT d.emp_no FROM dept_manager d);
左连接尝试错误【①限定条件问题，应该是所有的dept_no；②应该显示连接后新表的所有内容】
SELECT e.emp_no FROM employees e LEFT JOIN dept_manager d ON e.emp_no=d.emp_no WHERE e.dept_no IS NULL;
SELECT emp_no FROM (SELECT e.* FROM employees e LEFT JOIN dept_manager d ON e.emp_no=d.emp_no) WHERE dept_no IS NULL;
左连接正确语句：
SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager ON employees.emp_no=dept_manager.emp_no) WHERE dept_no IS NULL;
SQL是支持集合运算的：
SELECT employees.emp_no FROM employees EXCEPT SELECT dept_manager.emp_no FROM dept_manager;
-- EXPECT 集合差运算【集合A-集合B】-- UNION 集合并运算【集合A，集合B所有元素组成】-- INTERSECT 集合交运算【集合A、集合B共有的部分】
11，获取所有员工当前的manager
获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=\'9999-01-01\'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
两表联查尝试：【不等号<>更加通用；!=在sql2000中用到，则是语法错误，不兼容的】
SELECT de.emp_no,dm.emp_no AS manager_no FROM dept_emp de ,dept_manager dm WHERE de.to_date=\'9999-01-01\' AND dm.to_date=\'9999-01-01\' AND dm.dept_no=de.dept_no AND de.emp_no<>dm.emp_no;
内连接尝试：
SELECT de.emp_no,dm.emp_no AS manager_no FROM dept_emp de INNER JOIN dept_manager dm ON dm.dept_no=de.dept_no WHERE de.to_date=\'9999-01-01\' AND dm.to_date=\'9999-01-01\' AND de.emp_no != dm.emp_no;
12，获取所有部门中当前员工薪水最高的相关信息
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
内连接尝试错误：【GROUP BY 这条语句需要自己好好探究一下】
SELECT d.dept_no,d.emp_no,MAX(s.salary) salary FROM dept_emp d INNER JOIN salaries s ON d.emp_no=s.emp_no WHERE d.to_date=\'9999-01-01\' AND s.to_date=\'9999-01-01\';
正确语法：
SELECT d.dept_no,s.emp_no,MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp AS d ON d.emp_no=s.emp_no WHERE d.to_date=\'9999-01-01\' AND s.to_date=\'9999-01-01\' GROUP BY d.dept_no;
两表联查：
select d.dept_no, d.emp_no, max(s.salary) from salaries s , dept_emp d where d.emp_no = s.emp_no and d.to_date=\'9999-01-01\' and s.to_date=\'9999-01-01\' group by d.dept_no;
我在自己的阿里云主机上的MySQL还遇到了【ONLY_FULL_GROUP_BY问题】
这个主要是自己MySQL服务器设置中的sql_mode中设置了ONLY_FULL_GROUP_BY导致
解决方法见链接：https://www.cnblogs.com/xzjf/p/8466858.html
用连接和用from table1, table2并列查询，有时候都可以解题。
只是前者将几张表连接成一张大表，后者是并列查询若干表，两种方法在表中数据庞大的情况下肯定有效率的差异，至于哪个好自己百度一下吧，有实验环境也可以自己测试一下。
13，从titles表获取按照title进行分组
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
SELECT title,COUNT(title) t FROM titles GROUP BY title HAVING t>1;
为什么不用考虑 to_date=\'9999-01-01\' 呢？【关键字：当前没有出现】
以正常逻辑来说要加，否则没有意义，但题目没要求，而且加了 to_date=\'9999-01-01\'就通不过了，可以从测试用例看出题目算的是当前及以前titles的数目总数
14，从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的title进行忽略。
自己的错误尝试：
SELECT DISTINCT title,COUNT(*) t FROM titles GROUP BY title HAVING t>1;
正确的语句：
SELECT title,COUNT(DISTINCT emp_no) t FROM titles GROUP BY title HAVING t>1;
15，查找employees表
查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
SELECT * FROM employees WHERE emp_no%2=1 AND last_name<>\'Mary\' ORDER BY hire_date DESC;
16，统计出当前各个title类型对应的员工当前薪水对应的平均工资
统计出当前各个title类型对应的员工当前（to_date=\'9999-01-01\'）薪水对应的平均工资。结果给出title以及平均工资avg。
两表联立查询：
SELECT t.title,AVG(s.salary) avg FROM salaries s,titles t WHERE s.emp_no=t.emp_no AND s.to_date=\'9999-01-01\' AND t.to_date=\'9999-01-01\' GROUP BY title;
内连接查询：
SELECT t.title,avg(s.salary) FROM salaries as s INNER JOIN titles as t ON s.emp_no = t.emp_no AND s.to_date = \'9999-01-01\' AND t.to_date = \'9999-01-01\' GROUP BY title
17，获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
直接查询：【但是这样会有一些问题】
SELECT emp_no,salary FROM salaries WHERE to_date=\'9999-01-01\' ORDER BY salary DESC LIMIT 1,1;
避免了2个问题：
(1) 首先这样可以解决多个人工资相同的问题；
(2) 另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资。
SELECT emp_no, salary FROM salaries WHERE to_date = \'9999-01-01\' AND salary = ( SELECT DISTINCT salary from salaries ORDER BY salary DESC LIMIT 1,1 );
大数据量的情况下，避免使用DISTINCT
SELECT emp_no, salary FROM salaries WHERE to_date = \'9999-01-01\' AND salary = ( SELECT salary from salaries GROUP BY salary ORDER BY salary DESC LIMIT 1,1 );
18，获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by
自己尝试的：
SELECT s.emp_no,MAX(s.salary),e.last_name,e.first_name FROM employees AS e ,salaries AS s WHERE s.emp_no=e.emp_no AND s.to_date=\'9999-01-01\' AND e.to_date=\'9999-01-01\' AND s.salary NOT IN(SELECT MAX(salary) FROM salaries WHERE to_date=\'9999-01-01\');
改正代码【两表联立】：
SELECT s.emp_no,MAX(s.salary),e.last_name,e.first_name FROM employees AS e ,salaries AS s WHERE s.emp_no=e.emp_no AND s.to_date=\'9999-01-01\' AND s.salary NOT IN(SELECT MAX(salary) FROM salaries WHERE to_date=\'9999-01-01\');
正确语句【内连接】：
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name FROM employees AS e INNER JOIN salaries AS s ON e.emp_no = s.emp_no WHERE s.to_date = \'9999-01-01\' AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = \'9999-01-01\')
19，查找所有员工的last_name和first_name以及对应的dept_name
查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工【LEFT,RIGHT JOIN眉头一紧发现关键所在】
自己尝试
SELECT t.last_name,t.first_name,dm.dept_name FROM departments dm ,( SELECT e.last_name,e.first_name,de.* FROM employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no) t WHERE t.dept_no=dm.dept_no;
正确的语句：
SELECT e.last_name,e.first_name,dm.dept_name FROM (employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no) LEFT JOIN departments dm ON de.dept_no=dm.dept_no;
20，查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
分别查找两个to_date的数据相减
SELECT ((SELECT salary FROM salaries WHERE emp_no=10001 AND to_date=\'9999-01-01\')- (SELECT salary FROM salaries WHERE emp_no=10001 ORDER BY to_date LIMIT 0,1)) AS growth;
21，查找所有员工自入职以来的薪水涨幅情况【所有员工】【联查与连接如何区分使用？？？】
尝试使用上面的方法进行解决问题【ERROR 1242 (21000): Subquery returns more than 1 row】
SELECT ((SELECT salary FROM salaries WHERE to_date=\'9999-01-01\')- (SELECT salary FROM salaries ORDER BY to_date LIMIT 0,1)) AS growth;
正确语句：
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = \'9999-01-01\') AS sCurrent, (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart WHERE sCurrent.emp_no = sStart.emp_no ORDER BY growth
22，统计各个部门对应员工涨幅的次数总和
统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
自己手撕出来的
SELECT dm.dept_no,dm.dept_name,dc.num FROM (SELECT de.dept_no,SUM(sc.sSum) AS num FROM dept_emp de INNER JOIN (SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no) sc ON de.emp_no=sc.emp_no GROUP BY de.dept_no) dc,departments dm WHERE dc.dept_no=dm.dept_no;
分级进行处理：
①统计各个员工涨薪次数 COUNT
SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no;
②统计各个部门的涨薪次数 SUM
SELECT de.dept_no,SUM(sc.sSum) AS num FROM dept_emp de INNER JOIN (SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no) sc ON de.emp_no=sc.emp_no GROUP BY de.dept_no;
③将departments联查显示部门名称
SELECT dm.dept_no,dm.dept_name,dc.num FROM (SELECT de.dept_no,SUM(sc.sSum) AS num FROM dept_emp de INNER JOIN (SELECT s.emp_no,COUNT(s.emp_no) AS sSum FROM salaries s GROUP BY s.emp_no) sc ON de.emp_no=sc.emp_no GROUP BY de.dept_no) dc,departments dm WHERE dc.dept_no=dm.dept_no;
23，对所有员工的薪水按照salary进行按照1-N的排名【两表联查】
对所有员工的当前(to_date=\'9999-01-01\')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank FROM salaries AS s1, salaries AS s2 WHERE s1.to_date = \'9999-01-01\' AND s2.to_date = \'9999-01-01\' AND s1.salary <= s2.salary GROUP BY s1.emp_no ORDER BY s1.salary DESC, s1.emp_no ASC
24，获取所有非manager员工当前的薪水情况
获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=\'9999-01-01\'
SELECT de.dept_no,de.emp_no,es.salary FROM dept_emp de , (SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date=\'9999-01-01\' AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager)) es WHERE de.emp_no=es.emp_no;
分级来进行处理：
①内连接salaries与employees，找出当前员工的工号与工资
SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date=\'9999-01-01\'；
②在上面的基础上加上一个语句，去除管理岗的工号
SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date=\'9999-01-01\' AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager);
③将新表与dept_emp进行联查处理
SELECT de.dept_no,de.emp_no,es.salary FROM dept_emp de , (SELECT e.emp_no,s.salary FROM salaries s INNER JOIN employees e ON s.emp_no=e.emp_no WHERE s.to_date=\'9999-01-01\' AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager)) es WHERE de.emp_no=es.emp_no;
正确语句2：本质上是一样的
SELECT de.dept_no, s.emp_no, s.salary FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no = e.emp_no AND s.to_date = \'9999-01-01\') INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = \'9999-01-01\')
25，获取员工其当前的薪水比其manager当前薪水还高的相关信息【两表联查】
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=\'9999-01-01\',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de ON s.emp_no = de.emp_no AND s.to_date = \'9999-01-01\' ) AS sem, (SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm ON s.emp_no = dm.emp_no AND s.to_date = \'9999-01-01\' ) AS sdm WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary
分级来进行处理：
①先内连接salaries与dept_emp，获取当前工资、工号、部门号；
SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de ON s.emp_no=de.emp_no AND s.to_date = \'9999-01-01\')
②先内连接salaries与dept_manager，获取当前工资、工号、部门号；
SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm ON s.emp_no=dm.emp_no AND s.to_date = \'9999-01-01\')
③将两个表联查限制条件是：同一个部门，非管理比管理工资高；
SELECT sem.emp_no,sdm.emp_no manager_no,sem.salary emp_salary,sdm.salary emp_salary FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de ON s.emp_no=de.emp_no AND s.to_date = \'9999-01-01\') sem, (SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm ON s.emp_no=dm.emp_no AND s.to_date = \'9999-01-01\') sdm WHERE sem.dept_no=sdm.dept_no AND sem.salary > sdm.salary;
26，汇总各个部门当前员工的title类型的分配数目
汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
错误语句：
SELECT dm.dept_no,dm.dept_name,ec.title,COUNT(ec.title) \'count\' FROM departments dm,(SELECT DISTINCT de.emp_no,de.dept_no,t.title FROM dept_emp de INNER JOIN titles t ON de.emp_no=t.emp_no WHERE de.to_date=\'9999-01-01\' AND t.to_date=\'9999-01-01\') ec WHERE dm.dept_no=ec.dept_no GROUP BY dm.dept_no;
正确语句：
SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count FROM titles AS t INNER JOIN dept_emp AS de ON t.emp_no = de.emp_no AND de.to_date = \'9999-01-01\' AND t.to_date = \'9999-01-01\' INNER JOIN departments AS dp ON de.dept_no = dp.dept_no GROUP BY de.dept_no, t.title
27，给出每个员工每年薪水涨幅超过5000的员工编号emp_no【新知识点】
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime(\'%Y\', to_date)【难度优点大】
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth FROM salaries AS s1, salaries AS s2 WHERE s1.emp_no = s2.emp_no AND salary_growth > 5000 AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 ) ORDER BY salary_growth DESC
总结
常用语句：SELECT,FROM,WHERE【基础】
关键知识点：
疑难知识点：
- INNER,LEFT,RIGHT JOIN
- ORDER BY
- DISTICT 大数据时，该语句效率很低，建议使用GROUP BY 去重
- GROUP BY,HAVING
- COUNT
- MAX,AVG
- EXCEPT,UNION,INTERSECT
- 在sqlite中获取datetime时间对应的年份函数为strftime(\'%Y\', to_date)