connect by 用于存在父子,祖孙,上下级等层级关系的数据表进行层级查询。
成都创新互联公司专注于新田网站建设服务及定制,我们拥有丰富的企业做网站经验。 热诚为您提供新田营销型网站建设,新田网站制作、新田网页设计、新田网站官网定制、重庆小程序开发服务,打造新田网络公司原创品牌,更为您提供新田网站排名全网营销落地服务。
语法格式 :
{CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}
特殊词讲解 :
start with: 指定起始节点的条件
connect by: 指定父子行的条件关系
prior: 查询父行的限定符,格式: prior column1 = column2 or column1 = prior column2 and ... ,
nocycle: 若数据表中存在循环行,那么不添加此关键字会报错,添加关键字后,便不会报错,但循环的两行只会显示其中的第一条
循环行: 该行只有一个子行,而且子行又是该行的祖先行
connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字,用于表示是否是循环行,0表示否,1 表示是
connect_by_isleaf: 是否是叶子节点,0表示否,1 表示是
level: level伪列,表示层级,值越小层级越高,level=1为层级最高节点
自定义数据:
数据列表展示:
数据树形展示:
(1) 查询以emp_id为0开始的节点的所有直属节点
(2) 以emp_id为6的所有祖先节点
(3) 查询一个节点的叔叔伯父节点
(4) 查询族兄
(5) level伪列的使用,格式化层级
level数值越低级别越高
(6) connect_by_root 查找根节点
(7) 标注循环行
(8) connect_by_isleaf 是否是叶子节点
原文链接: oracle进阶之connect by笔记
我觉得吧,oracle的体系太庞大了,对于初学者来说,难免会有些无从下手的感觉,什么都想学,结果什么都学不好,所以把我的经验分享下,希望让刚刚入门的人对oracle有一个总体的认识,少走一些弯路。
acle分两大块,一块是开发,一块是管理。开发主要是写写存储过程、触发器什么的,还有就是用Oracle的Develop工具做form。有点类似于程序员,需要有较强的逻辑思维和创造能力,个人觉得会比较辛苦,是青春饭;管理则需要对oracle数据库的原理有深刻的认识,有全局操纵的能力和紧密的思维,责任较大,因为一个小的失误就会down掉整个数据库,相对前者来说,后者更看重经验。 我的方法就是看书、思考、写笔记、做实验、再思考、再写笔记 。看完理论的东西,自己静下心来想想,多问自己几个为什么,然后把所学和所想的知识点做个笔记;在想不通或有疑问的时候,就做做实验,想想怎么会这样,同样的,把实验的结果记下来。思考和做实验是为了深入的了解这个知识点。而做笔记的过程,也是理清自己思路的过程。发现不懂得可以在相关论坛里面求助,看看别人是怎么分析问题的。你还可以在:里面看看,不会让你失望的哦。希望可以帮到你呢。
where子句
案例:显示工资低于5000的员工信息
select * from briup_emp where sal5000;
案例:查询入职时间在2014-01-01之后的员工信息
select * from briup_emp where startdate'2014-01-01'
案例:查询工资在3000到5000之间的用工信息
select * from briup_emp where sal3000 and sal5000;
like子句(模糊查询)
通配符:%:0到N个字符 _:一个字符
案例:查询员工表中所有张姓的同事信息。
select * from briup_emp where ename like '张%' ;
查询名字第三个字母是a的员工的姓名和工资。
select ename,sal from briup_emp where ename like '__a%';
查询名字中倒数第三个字母是a的员工的姓名和工资。
select ename,sal from briup_emp where ename like '%a__';
in子句
案例:查询1号部门和二号部门的所有员工信息。
select * from briup_emp where deptno in(1,2) ;
select ename,deptno from briup_emp where deptno=1 or deptno=2;
is null子句
案例:查询奖金为空的员工信息。
select * from briup_emp where comm is null ;
查询奖金不为空的员工信息。
select * from briup_emp where comm is not null ;
and or子句
案例:查询工资大于5000或奖金大于500且名字里有A这个字符的员工信息。
select ename,sal,comm from briup_emp where (sal5000 or comm500) and ename like '%a%';
order by子句
案例:查询所有员工的工资并且从低到高排序。
select ename,sal from briup_emp order by sal;
查询所有员工的工资并且从高到低排序。
select ename,sal from briup_emp order by sal;
查询所有员工的信息,按照部门编号的升序,工资的降序。
select ename,sal,deptno from briup_emp order by deptno,sal desc;
将所有员工的年工资做降序排列。
select ename,sal*12 年工资 from briup_emp order by 年工资 desc;
数据的分组函数(组函数)
max min avg sum count
问题:求员工表中最大工资和最小工资;
select max(sal),min(sal) from briup_emp;
问题:求员工表中最大工资的员工信息;
select * from briup_empwhere sal=max(sal); error;
where子句后不能使用分组函数
select * from briup_emp where sal=(select max(sal) from briup_emp);
问题:查询员工的平均工资
select avg(sal) from briup_emp;
问题:显示一共有多少个员工
select count(empno) from briup_emp;
问题:查询工资低于平均工资的员工的信息
select ename,sal from briup_emp where sal(select avg(sal) from briup_emp);
distinct关键字
select count(distinct deptno) from briup_emp;
group by子句: 对查询结果进行分组统计,如果在select后面使用group by子句,就可以将行划分为较小的组,一旦使用分组后select操作的对象就变成了分组后的数据,
问题:查询每个部门的平均工资
select deptno,avg(sal)from briup_emp group by deptno; 抽象成----
select a,c,函数(b) from tablename group by a,c;
注意:在select列表所有未包含在组函数中的列都应该包含在group by子句中。
问题:查询每个部门编号的最高工资和最低工资;
select deptno,max(sal),min(sal) from briup_emp group by deptno;
问题:查询每个工种的最高工资和最低工资;
select job,max(sal),min(sal) from briup_emp group by job;
having子句:对分组查询的结果做限制
问题:查询平均工资大于3000的部门编号
select deptno,avg(sal) from briup_emp group by deptno having avg(sal)3000 order by deptno;
总结:
1、在select列表所有未包含在组函数中的列都应该包含在group
by子句中。
2、组函数只能在select、having、order by中使用。
3、group by —
having—order by
where和having区别:(都是去设置限定条件)
where后面不能跟分组函数 ,having
一号部门的平均工资(有些情况下可以通用)
where:select avg(sal) from briup_emp where deptno=1;
having:select avg(sal) from briup_emp group by deptno having deptno=1;
多表查询(从多张表中查数据,根据需求分析出要得到的数据来自于多张表)
问题:每个员工的姓名和工资和部门名称。
select t1.ename,t1.sal,t2.dname from briup_emp t1,briup_dept t2 where t1.deptno=t2.deptno;
笛卡尔集
如果在进行多表查询的时候不指定连接条件,就会导致将一个表中单的所有行都连接在另一个表的所有行上,成为笛卡尔积,如果一个表中有5000条数据,另一个表中有10条数据,将返回50000行。
为了避免笛卡尔积,需要在where中加入有效的连接条件。
等值连接(连接条件中为等号)
问题:每个员工的姓名和工资和部门名称。
select t1.ename,t1.sal,t2.dname from briup_emp t1,briup_dept t2 where t1.deptno=t2.deptno;
不等值连接(连接条件中不为等号)
问题:查看每个员工的姓名和工资和工资等级。
select t1.ename,t1.sal,t2.grade from briup_emp t1,briup_salgrade t2 wheret1.sal=t2.losal and t1.sal=t2.hisal;
等价于
select t1.ename,t1.sal,t2.grade from briup_emp t1,briup_salgrade t2 wheret1.sal between t2.losal and t2.hisal ;必须是大值在前小值在后
外连接(通过外连接,吧对于连接条件不成立的记录,依然包括在最后的结果中)
问题:统计每个部门的员工数(要求你显示部门号,部门名字,人数)
select t2.deptno部门号,t2.dname 部门名字,count(t1.empno) 部门人数 from briup_emp
t1,briup_dept t2 where t1.deptno=t2.deptno group by t2.deptno,t2.dname;
左外连接:当条件不成立是,等号左边的数据依然要被包含。
右外连接:当条件不成立是,等号右边的数据依然要被包含。
select t2.deptno部门号,t2.dname 部门名字,count(t1.empno) 部门人数 from briup_emp
t1,briup_dept t2 where t1.deptno(+)=t2.deptno group by t2.deptno,t2.dname;
自连接(将一张表通过别名视为多张表)
问题:查询员工的姓名及其领导的姓名。
select worker.ename,boss.ename from briup_emp worker,briup_emp boss where worker.mgr=boss.empno;
显示上级领导是Larry的员工姓名(先查询所有员工及其领导的姓名,再查出领导是Larry的员工)。
select worker.ename,boss.ename from briup_emp worker,briup_emp boss where worker.mgr=boss.empno and boss.ename='Larry';
如果员工表中有一万条数据,自连接就会产生一亿条数据,所以自连接不适合操作大表。
层次查询:可以替代自连接,本质上是一个单表查询。
select level,ename,mgr from briup_emp connect by prior empno=mgr start with mgr is null;
(level:伪列,代表层次深度)
子查询(嵌套查询):即一个select中嵌套了另外一个或者多个select
问题:查询和Smith在同一个部门的所有员工的姓名和部门号。
select ename,deptno from briup_emp where deptno=(select deptno from briup_emp where ename='Smith');
应用场景:
1、当一条语句需要依赖另一台查询语句的结果的时候,
单行子查询:只返回一行数据的子查询。
多行子查询:返回多行数据的子查询。
问题:查询和部门1的工作相同的员工的姓名、工作和工资。
select ename,job,sal from briup_emp where job in(select distinct job from briup_emp where deptno=1);
查询比部门1的员工的工资低的员工的姓名和工资。
select ename,sal from briup_emp where salall(select sal from briup_emp where deptno=1;
查询比部门1的任意员工的工资高的员工的姓名和工资。
select ename,sal from briup_emp where salany(select sal from briup_emp where deptno=1;
查询和Smith相同部门相同工作的员工的信息。
select ename,job,deptno from briup_emp where deptno=(select deptno from briup_emp where ename='Smith') and job=(select job from briup_emp where ename='Smith');
select ename,job,deptno from briup_emp where (job,deptno)=(select job,deptno from briup_emp where ename='Smith');
查询和Smith相同部门、工作、领导、工资的员工的信息。
select ename,job,deptno from briup_emp where (job,deptno)=(select job,deptno from briup_emp where ename='Smith');
Oracle是目前最流行的数据库之一。功能强大,性能卓越。
oracle完全可以代表数据库技术的最高水平,因此学习oracle需要你具备一定基础:
1.学习过一门编程语言(java,c,c++均可)。
2.最好学习过一门别的数据库(sql server,mysql,access)。
严禁没有任何基础的同志学习oracle,那样你会浪费时间,同时还打击学习oracle的信心,我个人认为学习流程为:
第一阶段:计算机基础(了解它的基本原理)。
第二阶段:学习一门编程语言(java或者c,c++)。
第三阶段:学习oracle数据库或是别的数据库。
成为一个oracle高手的秘诀就是:
理解小的知识点-做小练习-把小的知识点连成线(知识线)-做oracle项目-把知识线形成知识面-深刻理解
基础部分:
Oracle基本使用
Oracle用户管理
Oracle表管理
高级部分:
Oracle表的查询
Oracle的函数
Oracle数据库管理
Oracle的权限、角色
Pl/sql编程
索引,约束和事务
安装以后,运行里面的sqlplus进去就可以使用sql语言了,登陆用system加你安装数据库时用的初始密码