SQL语句：连接查询

连接查询：

• 也叫多表查询。常用于查询字段来自于多张表
• 如果直接查询两张表，将会得到笛卡尔积

mysql> select name, dept_name from employees, departments;

• 通过添加有效的条件可以进行查询结果的限定

 mysql> select name, dept_name from employees, departments where employees.dept_id=departments.dept_id;

连接分类

按功能分类

• 内连接(重要)
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接(重要)
  • 右外连接(重要)
  • 全外连接(mysql不支持，可以使用UNION实现相同的效果)
• 交叉连接

按年代分类

• SQL92标准：仅支持内连接
• SQL99标准：支持所功能的连接

SQL99标准多表查询

• 语法格式

SELECT 字段... 
FROM 表1 [AS] 别名 [连接类型]
JOIN 表2 [AS] 别名
ON 连接条件
WHERE 分组前筛选条件
GROUP BY 分组
HAVING 分组后筛选条件
ORDER BY 排序字段

内连接

• 语法格式

select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
inner join 表3 别名 on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组后筛选]
[order by 排序列表]

等值连接

• 查询每个员工所在的部门名

mysql> select name, dept_name
    -> from employees
    -> inner join departments
    -> on employees.dept_id=departments.dept_id;

• 查询每个员工所在的部门名，使用别名

mysql> select name, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;

• 查询每个员工所在的部门名，使用别名。两个表中的同名字段，必须指定表名

mysql> select name, d.dept_id, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;

• 查询11号员工的名字及2018年每个月工资

mysql> select name, date, basic+bonus as total
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018 and e.employee_id=11;

• 查询2018年每个员工的总工资

mysql> select name, sum(basic+bonus) from employees
    -> inner join salary
    -> on employees.employee_id=salary.employee_id
    -> where year(s.date)=2018
    -> group by name;

• 查询2018年每个员工的总工资，按工资升序排列

mysql> select name, sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> order by total;

• 查询2018年总工资大于30万的员工，按工资降序排列

 mysql> select name, sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> having total>300000
    -> order by total desc;

非等值连接

附：创建工资级别表

创建表语法：

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

创建工资级别表：

id：主键。仅作为表的行号
grade：工资级别，共ABCDE五类
low：该级别最低工资
high：该级别最高工资

mysql> use nsd2021;
mysql> create table wage_grade
    -> (
    -> id int,
    -> grade char(1),
    -> low int,
    -> high int,
    -> primary key (id));

向表中插入数据：

• 语法：

INSERT INTO 表名称 VALUES (值1, 值2,....);

向wage_grade表中插入五行数据：

mysql> insert into wage_grade values
    -> (1, 'A', 5000, 8000),
    -> (2, 'B', 8001, 10000),
    -> (3, 'C', 10001, 15000),
    -> (4, 'D', 15001, 20000),
    -> (5, 'E', 20001, 1000000);

• 查询2018年12月员工基本工资级别

mysql> select employee_id, date, basic, grade
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12;

• 查询2018年12月员工各基本工资级别的人数

mysql> select grade, count(*)
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12
    -> group by grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| A     |       13 |
| B     |       12 |
| C     |       30 |
| D     |       32 |
| E     |       33 |
+-------+----------+
5 rows in set (0.00 sec)

• 查询2018年12月员工基本工资级别，员工需要显示姓名

mysql> select name, date, basic, grade
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12;

自连接

• 要点：

  • 将一张表作为两张使用
  • 每张表起一个别名

• 查看哪些员的生日月份与入职月份相同

mysql> select e.name, e.hire_date, em.birth_date
    -> from employees as e
    -> inner join employees as em
    -> on month(e.hire_date)=month(em.birth_date)
    -> and e.employee_id=em.employee_id;
+-----------+------------+------------+
| name      | hire_date  | birth_date |
+-----------+------------+------------+
| 李玉英    | 2012-01-19 | 1974-01-25 |
| 郑静      | 2018-02-03 | 1997-02-14 |
| 林刚      | 2007-09-19 | 1990-09-23 |
| 刘桂兰    | 2003-10-14 | 1982-10-11 |
| 张亮      | 2015-08-10 | 1996-08-25 |
| 许欣      | 2011-09-09 | 1982-09-25 |
| 王荣      | 2019-11-14 | 1999-11-22 |
+-----------+------------+------------+
7 rows in set (0.00 sec)

外连接

• 常用于查询一个表中有，另一个表中没有的记录

• 如果从表中有和它匹配的，则显示匹配的值

• 如j要从表中没有和它匹配的，则显示NULL

• 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录

• 左外连接中，left join左边的是主表

• 右外连接中，right join右边的是主表

• 左外连接和右外连接可互换，实现相同的目标

左外连接

• 语法

SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
LEFT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段

• 查询所有部门的人员以及没有员工的部门

mysql> select d.*, e.name
    -> from departments as d
    -> left outer join employees as e
    -> on d.dept_id=e.dept_id;

右外连接

• 语法

SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
RIGHT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段

• 查询所有部门的人员以及没有员工的部门

mysql> select d.*, e.name
    -> from employees as e
    -> right outer join departments as d
    -> on d.dept_id=e.dept_id;

交叉连接

• 返回笛卡尔积
• 语法：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

• 查询员工表和部门表的笛卡尔积

mysql> select name, dept_name
    -> from employees
    -> cross join departments;