需求:
输出如下形式的结果
employee_id, first_name, department_id, department_name
10001 张无忌 10 人力资源部
10002 杨逍 20 计划部
...
10007 阿二 40 秘书处
--多表连接
--表连接 oracle8i********************************************************************************************
--#################################################################################################
--等值连接
需求:输出如下形式的结果
employee_id, first_name, department_id, department_name
10001 张无忌 10 人力资源
10002 杨逍 10 人力资源
...
10006 赵敏 20 办公室
select employees.employee_id,employees.first_name,
departments.department_id, departments.department_name
from employees,departments
where employees.department_id=departments.department_id
--使用and操作符增加查询条件
输出结果如下
employee_id, first_name, department_id, department_name
10001(不包含) 张无忌 10 人力资源部
10002 杨逍 20 计划部
...
10007 阿二 40 秘书处
select employees.employee_id,employees.first_name,
departments.department_id, departments.department_name
from employees,departments
where employees.department_id=departments.department_id
and employees.employee_id<>10001
--使用表的别名，简化查询
employee_id, first_name, department_id, department_name
10001 张无忌 10 人力资源部
10002 杨逍 20 计划部
...
10007 阿二 40 秘书处
--使用表的别名 列也使用别名(表名 别名)
select e.employee_id,e.first_name,d.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id
--三个表的等值连接
employee_id, first_name, department_id, department_name location_id city
10001 张无忌 10 人力资源部 1000 北京
10002 杨逍 20 计划部 1100 上海
...
10007 阿二 40 秘书处 1200 天津
select e.employee_id, e.first_name, d.department_id, d.department_name,l.location_id,l.city
from employees e ,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
--#################################################################################################
--非等值连接
需求显示结果如下
employee_id, first_name, salary, grade_level lowest_sal highest_sal
10001 张无忌 5000.00 c 4000 5999
..
10006 赵敏 6000 D 6000 7999
10004 韦一笑 6000 C 5000 69999
select e.employee_id, e.first_name,e.salary,j.grade_level,j.lowest_sal,j.highest_sal
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal
--#################################################################################################
外连接:
右外连接语法格式:
SELECT table.column, table.column
FROM table1, table2
WHERE table1.column = table2.column(+);
--右外连接:(+ 在右端)显示左边表的所有记录及其右边表和左边表相对应的记录
输出如下信息(右外连接)
e.employee_id, e.first_name, d.department_id, d.department_name
10001 张无忌 10 人力资源部
10002 杨逍 10 人力资源部
...
10006 赵敏 20 秘书处
100013 周芷若
100014 小昭
--右外连接
select e.employee_id,e.first_name,d.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id(+)
--(左边表)employees全部显示 右边表(departments)和employees相匹配的记录
--#################################################################################################
左外连接语法格式:
SELECT table.column, table.column
FROM table1, table2
WHERE table1.column(+) = table2.column
左外连接:(+在左端 =)显示右边表的所有记录,及其左边表和右边表对应的记录
输出如下信息(左外连接)
e.employee_id, e.first_name, d.department_id, d.department_name
10001 张无忌 10 人力资源部
10002 杨逍 10 人力资源部
...
10006 赵敏 20 秘书处
30 企划部
40 技术支持
50 总裁办公司
--左外连接
select e.employee_id,e.first_name,d.department_id,d.department_name
from employees e,departments d
where e.department_id(+)=d.department_id
--#################################################################################################
--自连接
输出信息如下:
员工id 姓名 管理者id 姓名
10002 杨逍 10001 张无忌
10003 谢逊 10001 张无忌
10004 韦一笑 10001 张无忌
10007 阿大 10006 赵敏
10008 阿二 10006 赵敏
.....
select e.employee_id,e.first_name, e.manager_id,m.first_name
from employees e,employees m
where e.manager_id=m.employee_id
--#################################################################################################
--*************************************************************************************************************
--支持sql-1999的连接标准
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
--#################################################################################################
--交叉连接
SELECT table1.column, table2.column
FROM table1 CROSS JOIN table2
--oracle8i
select * from employees e,dept d
--oralce9i
select * from employees e CROSS JOIN departments d
--#################################################################################################
--自然连接:
* NATURAL JOIN 子句基于两个表中列名完全相同的多个列产生连接
* 从两个表中选出连接列的值相等的所有行
* 如果两个列的名称相同,但是具有不同的数据类型，则查询会返回一个错误.
SELECT table1.column, table2.column
FROM table1 NATURAL JOIN table2
--使用等值连接查询emp和dept表
--采用等值连接
select * from emp e,dept d
where e.deptno=d.deptno
--自然连接
select * from emp e NATURAL JOIN dept d --连个表中都有deptno
--使用自然连接 （两个表中有相同的列(列名相同),同时还要有主外键关系) emp --->deptno dept ---deptno ,
--效果等同于等值连接
--语法结构
SELECT table1.column, table2.column
FROM table1 JOIN table2 USING (column_name)
select * from emp e JOIN dept d using(deptno)
--#################################################################################################
--on子句
* 自然连接的条件是基于表中所有同名列的等值连接
* 为了设置任意的连接条件或者指定连接的列，需要使用ON子句
* 连接条件与其它的查询条件分开书写
* 使用ON 子句使查询语句更容易理解
--输出结果如下
employee_id, first_name, department_id, department_name
10001 张无忌 10 人力资源部
10002 杨逍 20 计划部
...
10007 阿二 40 秘书处
--采用等值连接(oracle8i)
select e.employee_id,e.first_name,d.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id
--采用sql-1999标准的语法
--语法结构:
SELECT table1.column, table2.column
FROM table1 JOIN table2
ON(table1.column_name = table2.column_name)
--内连接 等价于 oracle8i等值连接
select e.employee_id,e.first_name,d.department_id,d.department_name
from employees e inner join departments d
on(e.department_id=d.department_id)
--注:inner可加 也可不加
--三个表的内连接
--输出如下结果
employee_id, first_name, department_id, department_name location_id city
10001 张无忌 10 人力资源部 1000 北京
10002 杨逍 20 计划部 1100 上海
...
10007 阿二 40 秘书处 1200 天津
--采用oracle8i的等值连接
select e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
--采用sql -1999标准的写法
select e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city
from employees e join departments d
on(e.department_id=d.department_id)
join locations l
on(d.location_id=l.location_id)
--#################################################################################################
--左外连接(left outer join)(常用) 等同于oracle8i的右外连接(不常用)
--显示左边表的所有记录,及其右边表和左边表相对应的记录
SELECT table1.column, table2.column
FROM table1
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)
输出如下信息
e.employee_id, e.first_name, d.department_id, d.department_name
10001 张无忌 10 人力资源部
10002 杨逍 10 人力资源部
...
10006 赵敏 20 秘书处
100013 周芷若
100014 小昭
--查询上面的信息 oracle8i采用右外连接
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e,departments d
where e.department_id=d.department_id(+)
--查询上面的信息 sql-1999的左外连接来实现
--语法结构:
SELECT table1.column, table2.column
FROM table1
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e left outer join departments d
on(e.department_id=d.department_id)
--#################################################################################################
--右外连接(常用) --等同于oracle81的左外连接(不常用)
--显示右边表的所有记录,及其左边表和右边表相对应的记录
输出信息如下
e.employee_id, e.first_name, d.department_id, d.department_name
10001 张无忌 10 人力资源部
10002 杨逍 20 计划部
...
10007 阿二 40 秘书处
50 办公室
60 财务部
--查询上面的信息 oracle8i采用左外连接
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e,departments d
where e.department_id(+)=d.department_id
--查询上面的信息 sql-1999的右外连接来实现
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e right outer join departments d
on(e.department_id=d.department_id)
--#################################################################################################
--全外连接(full outer join),显示左外和右边的对应和不对应的记录都显示
--oralce8i没有
输出如下信息
e.employee_id, e.first_name, d.department_id, d.department_name
10001 张无忌 10 人力资源部
10002 杨逍 10 人力资源部
...
10006 赵敏 20 秘书处
100013 周芷若
50 办公室
60 财务部
--查询上面的信息 sql-1999的全外连接来实现
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e full outer join departments d
on(e.department_id=d.department_id)
--#################################################################################################
--******************************************************************************************************
--子查询的语法结构
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
1 子查询在主查询前执行一次
2 主查询使用子查询的结果
--查询员工表中,工资大于平均工资的所有员工
select * from employees where salary>(
select avg(salary) from employees
)
--单列多行
--查询所有的员工信息,员工的job_id在10号部门中存在的
select * from employees
where job_id in( select distinct job_id from employees where department_id=10)
--any元素符
ANY 通常与大小写符号搭配使用，不单独使用。可以是<ANY 和>ANY ：
分别代表着下面的含义
* <ANY 小于子查询数据中的最大值
* >ANY 大于子查询数据中的最小值
--查询工资小于各部门平均工资的最大值的员工信息
select * from employees where salary <any (
select avg(salary) from employees group by department_id
)
--查询工资大于于各部门平均工资的最小值的员工信息
select * from employees where salary >any (
select avg(salary) from employees group by department_id
)
--各部门的平均工资
select department_id,avg(salary) from employees group by department_id
ALL 通常与大小写符号搭配使用，不单独使用。可以是<ALL 和>ALL ：分别代表着下面的含义
* >ALL 指大于子查询数据中的最大值
* <ALL 指小于子查询数据中的最小值
--查询工资大于各部门平均工资的最大值的员工信息
select * from employees where salary >all (
select avg(salary) from employees group by department_id
)
--查询工资小于各部门平均工资的最小值的员工信息
select * from employees where salary <all (
select avg(salary) from employees group by department_id
)