2.6 连接查询

2.6.1 语法

/* 连接查询 */ 
# 1 sql92
select 查询列
from 表1, 表2
where 连接条件
and 筛选条件;
# 2 sql99
select 查询列
from 表1 别名 
【连接类型】 join 表2 别名
on 连接条件
where 筛选条件;

• 连接查询又称多表查询
• 连接查询分类
  • 内连接：inner
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接：left 【outer】，左边是主表
      • 查询结果=内连接查询结果+主表中有而从表中没有的记录
    • 右外连接：right 【outer】，右边是主表
    • 全外连接（MySQL 不支持）：full 【outer】
      • 查询结果=内连接查询结果+主表中有而从表中没有的记录+从表中有而主表中没有的记录
  • 交叉连接：cross
    • 笛卡尔乘积

      2.6.2 使用

      ```sql

      1 【内连接】

      1.1 【等值连接】查询员工名和对应的部门名

      select last_name, department_name from employees as e, departments as d where e.department_id = d.department_id;

select last_name, department_name from employees as e inner join departments as d on e.department_id = d.department_id;

1.2 【非等值连接】查询员工的工资和工资级别

select salary, grade_level from employees e, job_grades g where e.salary between g.lowest_sal and g.highest_sal;

select salary, grade_level from employees e inner join job_gardes g on e.salary between g.lowest_sal and g.highest_sal;

1.3 【自连接】员工名及其上级名

select e.employee_id, e.last_name, m.employee_id, m.last_name from employees e, employees m where e.manager_id = m.employee_id;

select e.employee_id, e.last_name, m.employee_id, m.last_name from employees e inner join employees m on e.manager_id = m.employee_id;

2 【外连接】

2.1 【左外连接】查询没有员工的部门

select d.*, e.employee_id from department d left outer join employees e on d.department_id = e.department_id where e.employee_id is null;

2.2 【右外连接】查询没有员工的部门

select d.*, e.employee_id from employees e right outer join department d on d.department_id = e.department_id where e.employee_id is null;

3 【交叉连接】笛卡尔积

select 查询列 from 表1 别名 cross join 表2 别名;

---

<a name="kUzeI"></a>
## 2.7 子查询
<a name="28FbL"></a>
### 2.7.1 语法
```sql
/* 子查询 */
select 字段 from 表名 where 字段 in (select语句);

• 子查询：其他语句中的 select 语句，也称为内查询
  • 其他语句可以是 insert、 update、 delete、 select
• 主查询：嵌套其他 select 语句，也称为外查询
• 子查询先于主查询运行

• 子查询分类

  • 按出现位置分类
    • select 后（仅支持标量子查询）
    • from 后
    • where / having 后
    • exists 后（相关子查询）

  • 按结果集行列数分类

    • 标量子查询：结果集一行一列
      • 搭配单行操作符使用，包括 >、<、>=、<=、=、<>
    • 列子查询：结果集多行一列
      • 搭配多行操作符使用，包括 in、 any / some、 all
    • 行子查询：结果集一行多列
    • 表子查询：结果集多行多列

      2.7.2 使用

      ```sql

      1 【where/having后】

      1.1 【标量子查询/单行子查询】

      【where】查询工资比Abel高的员工信息

      select * from employees where salary > ( select salary from employees where last_name = ‘Abel’ );

    【having】查询最低工资大于50号部门最低工资的部门id及其最低工资

    select department_id, min(salary) from employees group by department_id having min(salary) > ( select min(salary) from employees where department_id = 50 );

1.2 【列子查询/多行子查询】

# 【where】查询location_id为1400或1700的部门中所有员工姓名

select last_name from employees where department_id in ( select department_id from departments where location_id in (1400,1700) );

# in() 相当于 = any()
 # not in() 相当于 <>all()

1.3 【行子查询】查询员工标号最小且工资最高的员工信息

select * from employees where (employee_id, salary) = ( select min(employee_id), max(salary) from employees );

# 或

select * from employees where employee_id = ( select min(employee_id) from employees ) and salary = ( select max(salary) from employees );

2 【select后】仅支持标量子查询

# 查询每个部门的员工个数

select d., ( select count() from employees e where e.department_id = d.department_id ) 个数 from departments d;

3 【from后】将子查询结果视为一个表，必须起别名

# 查询每个部门的平均工资的工资等级

select avg_dep.*, g.grade_level form ( select avg(salary) avg, department_id from employees group by departmemt_id ) avg_dep inner join job_grades g on avg_dep.avg between g.lowest_sal and g.highest_sal;

4 【exists后】相关子查询

# 查询有员工名的部门名

select department_name from departments d where exists ( select last_name from employees e where e.department_id = d.department_id );

# 或

select department_name from departments d where d.department_id in ( select department_id from employees; );

---

<a name="6pn4M"></a>
## 2.8 分页查询
<a name="UDdwh"></a>
### 2.8.1 语法
```sql
/* 分页查询 */
select 字段 from 表名 limit offset,size;
# offset 起始索引，若从0开始可以省略
# size 索引个数

select 字段 from 表名 limit (page-1)*size, size
# page 页码
# size 每页条目数

2.8.2 使用

# 1 【分页查询】查询前五条员工信息
select * from employees limit 0,5;
select * from employees limit 5;

# 2 【分页查询】查询第2、3条员工信息
select * from employees limit 2 offset 1;
# 从第1条（不包括）数据开始取后2条数据

2.9 联合查询

2.9.1 语法

/* 联合查询 */
select 字段1 from 表名1 where 筛选条件1;
union
select 字段2 from 表名2 where 筛选条件2;
union
...

• 查询的内容源于多个表且表之间无一定的连接关系，且查询字段数目一致
• union 默认去重， union all 不会去重

  2.9.2 使用

  # 1 【联合查询】查询中国和外国用户中男性的用户信息
  select id, cname, csex from t_ca where csex='男'
  union
  select t_id, tname, tsex from t_ua where tsex='male';
  

2.10 小结

select 字段                          7
from 表1                                 1
【连接类型】 join 表2     2
on 连接条件                            3
where 筛选条件                    4
group by 字段                        5
having 筛选条件                    6
order by 字段                        8
limit offset, size;            9