SQl查询 多表

多表查询

笛卡尔集错误的原因： 省略连接条件 连接条件无效 所有表中的所有行互相连接 为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。 SELECT table1.column … table2.column…

from table1 table2

where table1.column= table2.column

–1.对于两个表都有的列，必须显示指明是哪个表的

–2.可以在select \ where 中使用表的别名

select e.employee_id,e.department_id,d.department_name

from employees e,departments d

where e.department_id = d.department_id;

例如：

等值连接，非等值连接：

内连接，左外连接 和 右外连接 满外连接。

自连接

外连接

多个连接条件与and操作符连接。

使用表名前缀在多个表中区分相同的列。 在不同表中具有相同列名的列可以用表的别名加以区分。
列的别名可以使用在 order by 语句中，不可以使用在where语句中，表的别名没有如上限制。

外连接的小规律：

连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

内连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+).

使用外连接可以查询不满足连接条件的数据。外连接的符号是 (+)。

–总结：如果查询涉及到n个表，至少有 n - 1个连接条件

–内连接：只查询到了满足连接条件的数据

select e.employee_id,e.department_id,d.department_name

from employees e,departments d

where e.department_id = d.department_id;

–外连接：除了查询到满足连接条件的数据之外，还查询出了左表(或右表)中

–不满足条件的数据，那么这个查询就叫做左(或右)外连接

–左外连接

select e.employee_id,e.department_id,d.department_name

from employees e,departments d

where e.department_id = d.department_id(+);

–右外连接

select e.employee_id,e.department_id,d.department_name

from employees e,departments d

where e.department_id(+) = d.department_id;

–sql 99 语法

–规范的内连接 join … on …

select employee_id,e.department_id,department_name

from employees e join departments d

on e.department_id = d.department_id;

select employee_id,e.department_id,department_name,city

from employees e join departments d

on e.department_id = d.department_id

join locations l

on d.location_id = l.location_id;

–如何实现外连接

–左外连接

select employee_id,e.department_id,department_name

from employees e left outer join departments d

on e.department_id = d.department_id;

–右外连接

select employee_id,e.department_id,department_name

from employees e right outer join departments d

on e.department_id = d.department_id;

–满外连接

select employee_id,e.department_id,department_name

from employees e full outer join departments d

on e.department_id = d.department_id;

–自连接

select emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name

from employees emp,employees mgr

where emp.manager_id = mgr.employee_id;

分组函数：

–1.五个组函数:avg sum max min count

– avg / sum:只适用于数值型的变量

select avg(salary),sum(salary)

from employees;

select avg(hire_date),avg(last_name),sum(hire_date),sum(last_name)

from employees;

–max / min :适用于数值型、字符型、日期型的变量

select max(salary),max(hire_date),max(last_name)

from employees;

–count:适用于数值型、字符型、日期型的变量

–空值不计算在内

select count(employee_id),count(salary),count(department_id)

from employees;

–avg = sum / count

select avg(commission_pct),sum(commission_pct)/count(commission_pct),

sum(commission_pct)/107,sum(commission_pct)/count(nvl(commission_pct,0))

from employees;

select count(employee_id),count(1),count(2),count(salary),count(*)

from employees;

–2.group by

–求出EMPLOYEES表中各部门的平均工资

select department_id,avg(salary)

from employees

group by department_id;

select job_id,avg(salary)

from employees

group by job_id;

–结论：在select查询中没有使用组函数的列，都应该出现在group by中

select department_id,job_id,avg(salary)

from employees

group by department_id,job_id;

–3.having 过滤条件:如果过滤条件中出现了组函数，那么必须使用having替换where

–过滤条件没有出现组函数，仍然使用where

–部门最高工资比 10000 高的部门

select department_id,max(salary)

from employees

group by department_id

having max(salary) > 10000;