SQL-SELECT（3）多表查询，分组函数，子查询

2021.10.28

多表查询

课件中主要表结构：

select employee_id,department_id,department_name
from employees,departments

由于department_id字段同时存在于两个表中，该查询方式会导致将两表中所有信息组合显示得到length(a)*length(b)大小的数据量，并不符合我们需要查询的信息，该错误称为笛卡尔集错误。

为避免该错误产生，需要加入过滤条件，即where

等值连接

select e.employee_id,e.department_id,d.department_name --第1,3项中的前缀可以省略
from employees e,departments d
where e.department_id = d.department_id

需要多个表可以直接添加连接条件，n个表需要至少n-1个连接条件

非等值连接

直接使用比较运算符或between等进行运算

select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal

外连接

上述方式称为内连接，不包含两表中不匹配的内容，而外连接可以

select e.employee_id,e.department_id,d.department_name --第1,3项中的前缀可以省略
from employees e,departments d
where e.department_id = d.department_id(+)

返回包含左表中的不匹配内容，右表则将加号添加在左侧，不允许两侧均有

cross join & natural join

cross join同样会产生笛卡尔集错误
natural join能不需要限定表名，自动识别表中相等的所有字段的筛选，即存在多条一致列名时，返回各个列均相等的值

select employee_id,department_id,department_name
from employees natural join departments

join using

select employee_id,department_id,department_name
from employees join departments
using(department_id)
--选取指定列进行连接

缺点：列名必须一致，不能实现列名不一致的连接

join on

select employee_id,d.department_id,department_name,city
from employees e join departments d
on e.department_id = d.department_id
join location l
on d.location_id = l.location_id
--选取指定列进行连接，可以避免上述缺点

left/right/full outer join

在join前添加，达到与上述外连接一致的效果，outer可以省略
full outer join实现外连接中不能实现的左右表均显示

自连接

实现在同一个表中进行连接操作，将单个表命名为两个表

select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp,employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'
--查询公司中员工'chen'的manager信息

分组函数

属于多行函数
AVG
COUNT
MAX
MIN
STDDEV：标准差
SUM

select avg(salary),max(salary),min(salary),sum(salary)
from employees

其中AVG和SUM函数不能对日期/字符进行运算
COUNT函数只计算不为空的值的个数，并且参数若填入不是列名，则输出总共多少条数据count(1) = count(2)
AVG中使用sum/count，因此无法严格计算平均值，只能计算包含的平均值
需要严格计算平均值时可以利用

select sum(salary)/count(nvl(salary,0))--nvl:将空值进行转化

利用distinct关键字可以返回非重复列select count(distinct salary) from employees

group by

将需要得到的值按照列进行分组

select department_id,avg(salary)
from employees
group by department_id
--计算相同部门中各自的平均薪水

select department_id,avg(salary)
from employees
where department_id in (40,60,80)
group by department_id
--计算在40,60,80部门中各自的平均薪水

注意，where关键字必须在from后出现

group by还可以实现多个分组：

select department_id,job_id,avg(salary)
from employees
group by department_id,job_id--不能删除，因为AVG输出会只有一个
--计算不同部门中不同工作各自的平均薪水

having子句

where子句中不能使用组函数

where avg(salary>100)

需要使用having，having放在group by前后均可

having avg(salary>100)

组函数可以嵌套：max(avg(salary))

子查询

即嵌套查询

select last_name
from employees
where salary > (select salary
                from employees
                where last_name = 'Tom')
--查询比tom工资更高的员工     

子查询（内查询）优先执行，结果返回到父查询（外查询）中作为条件

单行子查询

select last_name,job_id,salary
from employees
where job_id = (
                select job_id
                from employees
                where employee_id = 141
                )
and salary > (
              select salary
              from employees
              where employees_id = 143
              )
--查询大于143号员工薪水的，和141号员工工作一致的员工                        

若子查询返回空值，则主查询由于无法满足条件，也返回空值

多行子查询

(NOT) IN：（不）等于列表（子查询返回值）中的一个
ANY：和列表中任意一个比较满足即可
ALL：必须与列表中所有值比较

select a
from list
where a < any (select...)