oracle中多表查询语句怎么写,Oracle常用sql语句(二）之组函数、多表查询

DML(数据操纵语言)

INSERT 、UPDATE、 DELETE

插入操作：INSERT:

语法： INSERT INTO 表名(列名1，列名2 …)VALUES(列值1，列值2…);

注意：列名与列值的类型、个数、顺序要一一对应。

可以把列名当做java中的形参，把列值当做实参。

值不要超出列定义的长度。

如果插入空值，请使用null

插入的日期和字符一样，都使用引号括起来。

修改操作 UPDATE:

语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2 …….. WHERE 列名=值

删除操作 DELETE:

语法 ： DELETE FROM 表名 【WHERE 列名=值】

注意：where语句可以不写

DELETE ：删除表中的数据，表结构还在;删除后的数据可以找回。

TRUNCATE ：删除是把表直接DROP掉，然后再创建一个同样的新表。

删除的数据不能找回。执行速度比DELETE快。

DQL(数据查询语言)

查询表中的所有数据

语法： SELECT 列名 FROM表名

［WHERE –> GROUP BY –>HAVING–> ORDER BY］

语法：

SELECT selection_list /*要查询的列名称*/

FROM table_list /*要查询的表名称*/

WHERE condition /*行条件*/

GROUP BY grouping_columns /*对结果分组*/

HAVING condition /*分组后的行条件*/

ORDER BY sorting_columns /*对结果分组*/

LIMIT offset_start, row_count /*结果限定*/

DML是对表中的数据进行增、删、改的操作。不要与DDL(数据定义语言)混淆了。

查询代码的书写顺序和执行顺序

查询语句书写顺序：select – from- where- group by- having- order by-limit

查询语句执行顺序：from －＞　where　 －－＞　group by　 －－＞ having －－＞ select －－＞ order by　－－＞　limit

注：其中WHERE 语句执行顺序是从右到左

组函数(多行函数，分组函数)

注：组函数会自动忽略空值，NVL函数使分组函数无法忽略空值

AVG(expr) 求平均值

COUNT([DISTINCT] expr) 统计行数,当expr=*时，所有的行数，expr=COLUMN时会自动过滤对应列的空值的行数

DISTINCT 去掉重复行

MAX(expr) 求最大值

MIN(expr) 求最小

SUM(expr) 求和

分组函数(GROUP BY)

注：SELECT 中的列不在组函数中就必须在 GROUP BY 中

多个列的分组：先按照第一个列分组，如果相同，再第二个列分组，依次类推

SELECT

deptno,

job,

AVG( sal )

FROM

emp

GROUP BY

deptno,

job;

Oracle的增强GROUP BY：用于做财务报表

break on deptno skip 2

— 部门号只显示一个，不同的部门号跳过2行

break on null 关闭设置

GROUP BY ROLLUP(a,b) 增强分组

例子

SELECT

deptno,

job,

SUM( sal )

FROM

emp

GROUP BY

ROLLUP (

deptno,

job)

上面代码相当于下面几个分组函数的和

SELECT

deptno,

job,

SUM( sal )

FROM

emp

GROUP BY

deptno,

job;

+

SELECT

deptno,

SUM( sal )

FROM

emp

GROUP BY

deptno,

job;

+

SELECT

SUM( sal )

FROM

emp

GROUP BY

deptno,

job

即：

GROUP BY a,b　　+　　GROUP BY a + 没有GROUP BY

过滤分组(HAVING)

注：不能再WHERE子句中使用组函数

可以在HAVING 子句中使用组函数，尽量使用WHERE

SELECT

deptno,

job,

AVG( sal )

FROM

emp

GROUP BY

deptno,

job

HAVING AVG(sal)>300;

重点：HAVING 可以和组函数配合使用，不是必须和GROUP BY配合，例子如下

SELECT COUNT(*)

from dual

HAVING COUNT(*)>0;

多表查询

连接类型

等值连接

不等值连接

外连接

自连接

左外连接：当条件不成立的时候，等号左边的表依然包含在最后的结果中

写法：

WHERE e.deptno=d.deptno(+) 或者FROM emp e right outer join dept d on e.deptno=d.deptno

右外连接：当条件不成立的时候，等号右边的表依然包含在最后的结果中

写法：

WHERE e.deptno(+)=d.deptno 或者FROM emp e left outer join dept d on e.deptno=d.deptno

例子

按部门号查询部门号，部门名称，人数

SELECT d.deptno 部门号,d.dname 名称,COUNT(e.empno) 人数

FROM emp e ,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,d.dname

SELECT d.deptno 部门号,d.dname 名称,COUNT(e.empno) 人数

FROM emp e right outer join dept d

on e.deptno=d.deptno

GROUP BY d.deptno,d.dname

自连接： 通过表的别名，将同一张表视为多张表

注：自连接不适合操作大表

-- 查询员工信息： 员工姓名 老板名

SELECT e1.empno,e1.ename 员工,e2.empno,e2.ename 老板

FROM emp e1 left outer join emp e2

on e1.mgr=e2.empno

SELECT e1.empno,e1.ename 员工,e2.empno,e2.ename 老板

FROM emp e1 , emp e2

WHERE e1.mgr=e2.empno(+)

层次查询

实质是单表查询

在层次查询中Oracle为我们提供了伪列 leval

上面的自连接问题：

查询员工信息： 员工姓名 老板名

SELECT LEVEL ,empno, ename,mgr

FROM emp

CONNECT BY PRIOR empno=mgr

START WITH mgr IS NULL;