Oracle常用sql语句(二)之组函数、多表查询

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle常用sql语句(二)之组函数、多表查询

龙神之怒 2017-10-05 22:05:00 浏览748
展开阅读全文

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;

多表查询

  • 连接类型
  • 等值连接
  • 不等值连接
  • 外连接
  • 自连接

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

写法:

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;

网友评论

登录后评论
0/500
评论
龙神之怒
+ 关注