MySQL练习-employees数据库(二)

department	部门表
dept_emp	部门员工任职期表(按部门&时期)
dept_manager	部门经理任职期表(按时期)
employees	员工详情表
salaries	员工薪资表(按时期)
title	员工职称表(按时期)

选择员工id为110022，因为他还当过经理，所有五张表都会有

SELECT * 
FROM dept_emp
WHERE emp_no = 110022;

SELECT * 
FROM dept_manager
WHERE emp_no = 110022;

SELECT * 
FROM employees
WHERE emp_no = 110022;

SELECT * 
FROM titles
WHERE emp_no = 110022;

SELECT * 
FROM salaries
WHERE emp_no = 110022;

dept_emp

dept_manager

employees

title

salaries

 

分组统计&排序 

统计历史上各个部门所拥有的员工数量，并降序排序

SELECT dept_no, COUNT(*) AS emp_sum
FROM dept_emp
GROUP BY dept_no
ORDER BY emp_sum DESC

创建视图

把上面的查询创建成视图（执行完语句后记得点刷新，不然看不到）

CREATE VIEW test AS
SELECT dept_no, COUNT(*) AS emp_sum
FROM dept_emp
GROUP BY dept_no
ORDER BY emp_sum DESC:

 

联结

使用联结，将创建的视图test补上部门名称，使用WHERE联结两个表的dept_on变量，这是一种等值联结/自然联结，因为dept_on在两张表都是唯一并且一一对应的。

注意一点，联结后我们仍希望筛选联结的变量时，需要指定是哪张表的dept_on，不然系统分不清。

以上为联结的入门，可以说最常用最简单的联结方式，接下来我们将联结方式进行尝试，再根据使用场景进行总结。

INNER JOIN

上面的自然联结(使用WHERE)其实就内部联结，如下两句SQL语句效果是一样。

当需要联结的两个联结键都是“唯一的”(内部没有重复值的意思)，便需要使用内部联结，因为这种情况下是用WHERE和INNER JOIN语句是一样的，为了方便使用WHERE吧，如果语句赋值还是使用INNER JOIN

SELECT *
FROM dept_manager, dept_emp
WHERE dept_manager.emp_no = dept_emp.emp_no;

SELECT *
FROM dept_manager INNER JOIN dept_emp
ON dept_manager.emp_no = dept_emp.emp_no;

 

OUTER JOIN

如果两个表进行联结的两个联结键其中一个是有重复值的，一个是没有的，而且“有重复的”要比“唯一的”范围“大”，然后我们用"唯一的"的内容去填补“有重复的”表！

使用LEFT JOIN将departments表中信息将dept_manager补上部门名称

SELECT dept_manager.*, dept_name
FROM dept_manager LEFT JOIN departments
ON dept_manager.dept_no = departments.dept_no

 

使用OUTER JOIN必须加上LEFT或者RIGHT，所以实际并不存在OUTER JOIN，而是存在

LEFT JOIN

RIGHT JOIN

(OUTER可以省略不写)

 

总结使用场景其实简单

内部联结：两个表的两个联结键都是“唯一的”

外部链接：两个联结键一个是“有重复的”，一个是“唯一的”，而且前一个的范围最好比后一个“大”，我们就可以用“唯一的”信息去填补“有重复的”，没有对应就留空。

联结探究

我们来研究下不符合上面使用场景的情况是怎样的。我们以dept_emp和salaries表中员工号为10010的数据为例，因为刚好emp_no有“重复的”，接下来我们就用emp_no作为联结键。

SELECT *
FROM dept_emp 
WHERE emp_no = 10010;

SELECT *
FROM salaries 
WHERE emp_no = 10010;

dept_emp

salaries

使用内部联结，INNER JOIN和WHERE一起上，结构一致，使用“有重复的”的联结键，产生的结果是笛卡尔积：2*6=12

SELECT *
FROM salaries INNER JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
WHERE salaries.emp_no = 10010;

SELECT *
FROM salaries, dept_emp
WHERE salaries.emp_no = dept_emp.emp_no AND salaries.emp_no = 10010;

 

使用外部链接，LEFT和RIGHT都上，结果也和内部联结相似，只是排序些许不同（不影响）。

SELECT *
FROM salaries LEFT JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
WHERE salaries.emp_no = 10010;

SELECT *
FROM salaries RIGHT JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
WHERE salaries.emp_no = 10010;

 

 

联结多个表

SELECT dept_manager.*, departments.dept_name, employees.first_name 
FROM dept_manager, departments, employees
WHERE dept_manager.dept_no = departments.dept_no 
    AND dept_manager.emp_no = employees.emp_no