Oracle数据库基础4-高级查询

在oracle数据库里，数字类型数据可以被隐形转换为字符型类型，如输入代码select '5' + 3 from dual;打印结果为8，如果需要得到53需要用||连接符；

高级查询

一.分组函数

1.组函数过滤空值问题

例如需要查询表中所有员工平均奖金，可以用AVG（）来计算，但如果有的员工奖金列为空，调用函数时是不把这些空的值计算在内的，我们需要将空的值改为0，可以用组合函数：AVG(NVL(jiangjin,0))来计算；

2.数据表格

select nvl(p.toma,'总计'), sum(p.lastcou) from product p group by rollup(p.toma)；--将数据分组后以表格的形式显示出来；

二.多表连接

Oracle数据库里对于多表连接提供了几种方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。

1.交叉连接

交叉连接也称为笛卡尔积，交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。

2.内连接

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行，分为等值连接和不等值连接。

3.外连接

• 左外连接：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。
• 右外连接：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。
• 全外连接：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

代码示例：表TESTA,TESTB,TESTC,各有A, B两列,以a列为连接条件

a	b
001	10A
002	20A

a	b
001	10B
003	30B

a	b
001	10C
004	40C

 

 

 

 

 

 

1.内连接，最常见的等值连接:inner join

SELECT * FROM texta INNER JOIN testb ON tseta.a=testb.b;
--另一种写法
SELECT * FROM testa,testb WHERE testa.a=testb.a;

结果：

a	b	a	b
001	10A	001	10B

 

 

 

 

2.左外连接：left outer join 或者 left join

SELECT * FROM testa LEFT OUTER JOIN testb ON testa.a=testb.a;
--另一种写法
SELECT * FROM testa,testb WHERE testa.a=testb.a(+);

结果：

a	b	a	b
001	10A	001	10B
002	20A

 

 

 

 

 

 

3个表做左外连接：

SELECT * FROM testa LEFT OUTER JOIN testb ON testa.a=testb.a LEFT OUTER JOIN testc ON testa.a=testc.a;
--另一种写法
SELECT * FROM testa,testb WHERE testa.a=testb.a(+) AND testa.a=testc.a;

结果：

a	b	a	b	a	b
001	10A	001	10B	001	10C
002	20A

 

 

 

 

 

 

3.右外连接：right outer join 或者 right join

SELECT * FROM testa right OUTER JOIN testb ON testa.a=testb.a;
--另一种写法
SELECT * FROM testa,testb WHERE testa.a(+)=testb.a;

结果：

a	b	a	b
001	10A	001	10B
		003	30B

 

 

 

 

 

 

4.全外连接：full outer join 或者 full join

SELECT * FROM testa FULL OUTER JOIN testb ON testa.a=testb.a;
--另一种写法:对同一表先做左连接，然后右连接
SELECT * FROM testa LEFT OUTER JOIN testb ON testa.a=testb.a UNION  SELECT * FROM testa RIGHT OUTER JOIN testb ON testa.a=testb.a;

结果：

a	b	a	b
001	10A	001	10B
002	20A
		003	30B

 

 

 

 

 

 

 

三.层次查询

自连接：即把一个表当做两个表使用，一般用于查看上下级关系，代码示例：

SELECT e1.ename, e2.ename FROM emp e1, emp e2 WHERE e1.empno=e2.manager;--查看上级领导所对应的员工

在一个表中如果存在多层关系时使用以下代码格式：

connect by prior 连接条件 start with 开始条件（prior：优先的）

代码示例：

--通过代号和领导代号查看员工之间的层级关系
SELECT t.*  ,LEVEL FROM T_EMP T CONNECT BY  PRIOR T.DAIHAO =  T.LINGDAODAIHAO START WITH T.DAIHAO=7839;

1.LEVEL:层次查询中每一层的深度

2.ROWID:表示数据表格中某一行的存储地址

3.ROWNUM:行号

• 行号永远按照默认的顺序生成
• 行号只能使用<, <=, 不能使用>, >=
• 可使用rownum来进行分页操作

分页操作代码示例：每页5行，取第三页

SELECT r.*,r.rnum FROM (SELECT t.*,ROWNUM rnum FROM t_emp t WHERE ROWNUM <=15) r WHERE r.rnum>10;

四.子查询

sql语句执行顺序是从右往左执行的；

子查询语句代码示例：

--查询员工表中工资最低的记录，有两种写法
SELECT * FROM emp WHERE sal <= ALL(SELECT sal FROM emp)
SELECT * FROM emp e WHERE e.sal=(SELECT MIN(sal) FROM emp)

子查询的分类：

1.相关子查询:即子查询语句和父查询语句都是在同一个表中执行的；

2.不相关子查询：即子查询语句和父查询语句不在同一个表中执行的；

代码示例：

SELECT e.name,(SELECT d.mingcheng FROM t_dept d WHERE d.bumen=e.bumen) FROM t_emp e;

可以使用子查询的位置：select；from；where；having

3.单行子查询：子查询的返回结果，只有一行。

使用前提：问题不能一步解决，不能再group by后面使用子查询；

单行子查询只能使用单行操作符：=, >=, >, <, <=, <>

4.多行子查询：子查询的返回结果, 可能有多行。

多行子查询只能使用多行操作符: in, all, any

在多行子查询的查询结果中如果包含null，不能使用not in，这里可理解为not in null 不包含空，这是不可能的，相当于1=2，返回值都为false；

五.Exists关键字

exists关键字意思为存在，用来判断返回值真假

例如：if EXISTS(select* from tb where name='abc')

这句话的意思是从tb表中查找某一列，只要这一列的name=‘abc’，那么就返回true，否则返回false，具体查找什么无所谓。

转载于:https://www.cnblogs.com/wyc1991/p/9069966.html