SQL92的语法规则的缺点:
- 语句过滤条件和表连接的条件都放到了where子句中 。
- 当条件过多时,联结条件多,过滤条件多时,就容易造成混淆
- 语句过滤条件和表连接的条件都放到了where子句中 。
- 当条件过多时,联结条件多,过滤条件多时,就容易造成混淆
表连接的种类
内连接:(inner join)
返回两个表都对应的数据
返回两个表都对应的数据
外连接:
左外连:(left join)以左表为主,右表与其对应
右外连:(right join)以右表为主,左表与其对应
全外连:(full join)以左右表为主,左右表数据
交叉连接:(cross join)
笛卡尔集
自然连接:(Natural join)
自己找关系
Using子句
如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足要求,可以在连接时使用USING子句来设置用于等值连接的列(参照列)名。
using子句引用的列在sql任何地方不能使用表名或者别名做前缀
如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足要求,可以在连接时使用USING子句来设置用于等值连接的列(参照列)名。
using子句引用的列在sql任何地方不能使用表名或者别名做前缀
On子句
- 自然连接的条件是基于表中所有同名列的等值连接
- 为了设置任意的连接条件或者指定连接的列,需要使用ON子句
- 连接条件与其它的查询条件分开书写
- 使用ON 子句使查询语句更容易理解
- 自然连接的条件是基于表中所有同名列的等值连接
- 为了设置任意的连接条件或者指定连接的列,需要使用ON子句
- 连接条件与其它的查询条件分开书写
- 使用ON 子句使查询语句更容易理解
子查询:
一个查询的结果作为另一个查询的条件
一个查询的结果作为另一个查询的条件
单行子查询单行子查询只返回一行记录
对单行子查询可使用单行记录比较运算符< 、 > 、 = 、 >=、 <= 、 <>、!=
多行子查询
- 多行子查询返回多行记录
- 对多行子查询只能使用多行记录比较运算符
- ALL 和子查询返回的所有值比较
- ANY 和子查询返回的任意一个值比较
- IN 等于列表中的任何一个
oracle连表查询 代码
--92连表查询
--查询员工信息和部门信息
select * from emp e, dept d where e.deptno = d.deptno;
--查询员工姓名,部门名称
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
--查询员工所有信息和部门名称
select e.*, d.dname from emp e, dept d where e.deptno = d.deptno
--查询工资大于3000的员工姓名,工资和部门名称
select e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
and e.sal > 3000;
--非等值查询
--查询员工的工资等级
select *
from emp e, salgrade s
where e.sal <= s.hisal
and e.sal > s.losal
--外连接
--左外连
select * from emp e, dept d where e.deptno = d.deptno(+)
--右外连
select * from emp e, dept d where e.deptno(+) = d.deptno
--自连接 自己连自己
--查询员工姓名和员工的经理姓名
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;
--查询员工姓名和员工的经理姓名和其经理的姓名
select e1.ename, e2.ename, e3.ename
from emp e1, emp e2, emp e3
where e1.mgr = e2.empno
and e2.mgr = e3.empno;
--99连表查询
--cross join交叉连接(笛卡尔集)
select * from emp e cross join dept d;
-- natural join 自然连接
select * from emp natural join dept;
--using
select e.ename, deptno from emp e join dept d using (deptno);
--on 自定义连接条件 join 后紧跟 on
select * from emp e join dept d on e.deptno = d.deptno;
select * from emp e1 join emp e2 on e1.mgr = e2.empno;
--查询员工的姓名和经历的姓名和其经理的姓名
select e1.ename, e2.ename, e2.ename
from emp e1
join emp e2
on e1.mgr = e2.empno
join emp e3
on e2.mgr = e3.empno
--inner join 两边都合法的数据(相对比较精准)内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
--left join 以左表为主
select * from emp e left join dept d on e.deptno = d.deptno;
--right join 以右表为主
select * from emp e right join dept d on e.deptno = d.deptno;
--full join 全连 取两个表的所有数据
select * from emp e full join dept d on e.deptno = d.deptno;
--查询员工姓名和部门姓名和其经理的名称和经理的部门名称
select e1.ename, e2.ename, d1.dname, d2.dname
from emp e1
left join dept d1
on e1.deptno = d1.deptno
left join emp e2
on e1.mgr = e2.empno
left join dept d2
on e2.deptno = d2.deptno;
select sal from emp where ename='CLARK';
select *from emp where sal >2450;
--子查询
--查询工资大于CLARK工资的员工信息
select * from emp where sal > (select SAL from emp where ename = 'CLARK');
--思考:查询工资高于平均工资的雇员名字和工资。
select ename, sal from emp where sal > (select avg(sal) from emp);
--思考:查询和SCOTT同一个部门且比他工资低的雇员名称和工资
select ename, sal
from emp
where deptno = (select deptno from emp where ename = 'SCOTT')
and sal < (select sal from emp where ename = 'SCOTT');
--多行子查询查询和'SCOTT','SMITH','CLARK'在同一个部门的员工信息并不包含'SCOTT','SMITH','CLARK';
select *
from emp
where deptno in
(select deptno from emp where ename in ('SCOTT', 'SMITH', 'CLARK'))
and ename not in ('SCOTT', 'SMITH', 'CLARK');
--思考:查询工资最高的雇员名字和工资。
select ename, sal from emp where sal = (select max(sal) from emp);
-- 查询职务和SCOTT相同,比SCOTT 雇佣时间早的员工信息
select *
from emp
where job = (select job from emp where ename = 'SCOTT')
and hiredate < (select hiredate from emp where ename = 'SCOTT')
--查询工资比SCOTT 高或者雇用时间比SCOTT 早的雇员的编号和姓名
select empno,ename from emp where SAL>(select sal from emp where ename ='SCOTT')OR HIREDATE<
(SELECT HIREDATE FROM EMP WHERE ENAME ='SCOTT');
--多行查询
--查询工资低于任何一个"CLERK"的工资的雇员信息
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT sal from emp where JOB = 'CLERK');
--查询工资比所有的“SALESMAN”都高的雇员的编号、名称和工资
select empno, ename, sal
from emp
where sal > all (select sal from emp where job = 'SALESMAN');
--查询部门20中职务同部门10的雇员一样的雇员信息
select * from emp where job in(select job from emp where deptno=10)and deptno=20;
--查询在雇员中有哪些人是经理人
select * from emp where empno in (select distinct mgr from emp where mgr is not null or mgr!='');
--找出部门编号为20的所有员工中收入最高的职员
select * from emp where sal=(select max(sal) from emp where deptno='20');
--查询每个部门平均薪水的等级
select a.deptno, grade
from salgrade, (select deptno, avg(sal) b from emp group by deptno) a
where losal < a.b
and hisal >= a.b