牛客网数据库SQL练习3-4

3.查找当前薪水详情以及部门编号dept_no（考察两个表的连接）

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录，任何一边缺失数据就不显示。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

 题目：请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'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');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

答案：

1）隐式内连接

SELECT s.*,d.dept_no 
FROM salaries AS s,dept_manager AS d 
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'
AND s.emp_no=d.emp_no
ORDER by s.emp_no ACS

默认升序，可以不排序

本题不限定日期也可得到相应结果

2）显式外连接

SELECT s.*,d.dept_no 
FROM salaries AS s
JOIN dept_manager AS d 
ON s.emp_no=d.emp_no
AND s.to_date='9999-01-01'
AND d.to_date='9999-01-01'

3）左外连接

SELECT s.*,d.dept_no 
FROM salaries AS s
LEFT JOIN dept_manager AS d 
ON s.emp_no=d.emp_no
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'

left join on后面只能跟一个条件 不能用and 并列多个

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no

答案：

SELECT e.last_name,e.first_name,d.dept_no
FROM employees e 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 e.emp_no=d.emp_no