Oracle 数据库入门之----------------------,多行函数

2,多行函数

 

SQL> --工资总额

SQL> select sum(sal) from emp;

  SUM(SAL)                                                                                                                                                                                        

----------                                                                                                                                                                                              

     29025    

                                                                                                                                                                                      

SQL> --人数

SQL> select count(*) from emp;

 

  COUNT(*)                                                                                                                                                                                              

----------                                                                                                                                                                                              

        14                                                                                                                                                                                              

 

SQL> --平均工资

SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

 

        一         二                                                                                                                                                                                   

---------- ----------                                                                                                                                                                                   

2073.21429 2073.21429                                                                                                                                                                                   

 

SQL> --平均奖金

SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三

  2  from emp;

 

        一         二         三                                                                                                                                                                        

---------- ---------- ----------                                                                                                                                                                        

157.142857        550        550                                                                                                                                                                        

 

SQL> select count(*), count(comm) from emp;

 

  COUNT(*) COUNT(COMM)                                                                                                                                                                                  

---------- -----------                                                                                                                                                                                  

        14           4                                                                                                                                                                                  

 

SQL> select * from emp;

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              

      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                              

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                                                                                                              

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                              

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                              

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                              

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                              

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                              

      7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                              

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                              

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                              

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              

      7900 JAMES      CLERK           7698 03-12月-81            950                    30                                                                                                              

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                                                                                              

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                              

 

已选择 14 行。

 

SQL> --null值 5. 组函数会自动滤空;

SQL> select count(*), count(nvl(comm,0)) from emp;

 

  COUNT(*) COUNT(NVL(COMM,0))                                                                                                                                                                           

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏黑泽君的专栏

day42_Oracle学习笔记_01

虚拟机上的orcl数据库,所在位置:C:\app\Training\oradata\orcl

10720
来自专栏数据和云

突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 只要增加了DISTINCT关键字,Orac...

42560
来自专栏杨建荣的学习笔记

关于统计信息过期的性能落差(r5笔记第36天)

今天客户反馈某一个应用部署补丁的时候,执行了一个脚本一个多小时还没有执行完。 语句是下面这样的形式。 insert into em1_rater_00068_0...

42960
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

23530
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

31150
来自专栏杨建荣的学习笔记

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

36940
来自专栏乐沙弥的世界

SQL 基础--> 子查询

ORA-01427: single-row subquery returns more than one row

7920
来自专栏Hadoop数据仓库

一个用 Oracle 函数索引进行优化的例子

表中有500万条记录,原来没有索引: set timing on set autotrace traceonly SQL> select count(*), ...

27060
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

17640
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------基本查询

TNAME                          TABTYPE  CLUSTERID                               

14200

扫码关注云+社区

领取腾讯云代金券