函数，聚合函数，分组，字符串连接

1，Demo ,函数练习，涉及的用户有scott

--============================================ ---ylb：Oracle --15:29 2011-12-30 --1,函数练习，涉及的用户有scott --============================================    --1,例:提取 7654 号雇员姓名的 2－4位 SQL> select  substr(ename,2,3) from  emp where  empno=7654; --2,round函数的使用。 SELECT  ROUND(2.3) FROM  DUAL; SELECT  ROUND(2.6) FROM  DUAL; --3,to_char --以货币形式显示sal列 SELECT  TO_CHAR(SAL, '$99,999,999' ) FROM  EMP; SELECT  TO_CHAR(SAL, 'L99,999,999' ) FROM  EMP; SELECT  TO_CHAR(SAL, 'L00,000,000' ) FROM  EMP; --显示时间 SELECT  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS' ) FROM  DUAL; SELECT  TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"' ) FROM  DUAL; --4,to_date --查找入职时间在1981-4-01后的员工： SELECT  * FROM  EMP WHERE  HIREDATE>TO_DATE( '1981-4-01' , 'YYYY-MM-DD' ); --5,nvl函数 --求员工的年薪 SELECT  SAL*12+NVL(COMM,0) FROM  EMP; --6.找出收取佣金的员工的不同工作. SELECT  DISTINCT  JOB FROM  EMP WHERE  COMM IS  NOT  NULL ; --7.找出各月倒数第3天受雇的所有员工. SELECT  * FROM  EMP WHERE  HIREDATE=LAST_DAY(HIREDATE)-2; --8.找出早于27年前受雇的员工. SELECT  * FROM  EMP WHERE  FLOOR(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE))/12)>27 --9.以首字母大写的方式显示所有员工的姓名. SELECT  INITCAP(ENAME) FROM  EMP; --10.显示正好为5个字符的员工的姓名. SELECT  ENAME FROM  EMP WHERE  LENGTH(ENAME)=5; --11.显示不带有"T"的员工的姓名. SELECT  ENAME FROM  EMP WHERE  ENAME NOT  LIKE  '%T%' ; --12.显示所有员工的姓名,用a替换所有"A" SELECT  REPLACE (ENAME, 'A' , 'a' ) FROM  EMP; --13.显示满10年服务年限的员工的姓名和受雇日期. SELECT  * FROM  EMP WHERE  FLOOR(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE))/12)>10; --14.显示所有员工的姓名、加入公司的年份和月份 --,按受雇日期所在月排序 --,若月份相同则将最早年份的员工排在最前面. SELECT  TO_CHAR(HIREDATE, 'YYYY-MM' ) FROM  EMP; --15.显示在一个月为30天的情况所有员工的日薪金,忽略余数. --16.找出在(任何年份的)2月受聘的所有员工。 --p:17.对于每个员工,显示其加入公司的天数. SELECT  TRUNC(SYSDATE-HIREDATE) FROM  EMP; --18.以年月日的方式显示所有员工的服务年限. (大概) --19 decode()函数: 显示员工编号,姓名,月薪及月薪等级: 要求800=  初级 1600= 中级 3000= 高级 其余月薪显示其他 SELECT  DECODE(SAL,800, '初级' , '其它' ) FROM  EMP; -- SELECT  DECODE(SAL,800, '初级' , DECODE(SAL,1600, '中级' , DECODE(SAL,3000, '高级' , '其它' ))) FROM  EMP; --20sign函数 显示员工编号,姓名,月薪及月薪等级: 要求1 ----999  初级 1000 ----2999 中级 3000 ----3999 高级 4000 ----超级 SELECT  DECODE(SIGN(SAL-1000),-1, '初级' , '其它' ) FROM  EMP; -- SELECT  DECODE(SIGN(SAL-1000),-1, '初级' , DECODE(SIGN(SAL-3000),-1, '中级' , DECODE(SIGN(SAL-4000),-1, '高级' , '超级' ))) FROM  EMP;

 

2，Demo 聚合函数、分组、字符串连接

--======================================================== --ylb：Oracle --17:13 2011-12-30 --1，基于scott用户 --2，聚合函数 --3，分组 --4，聚合函数和分组的结合 --5，字符串连接 --========================================================    1,运行产生表格， set  pagesize 30; set  linesize 120; 2，字段连接。使用||符号 SQL> select  '编号:' ||empno|| ',姓名:' ||ename 情况 from  emp; 3，分组函数: count   group  by   max  min 等    <1> count  : 计数          a.统计表记录数 SQL> select  count (*) from  emp;          b.统计comm字段不为空的记录数 SQL> select  count (*) from  emp; 总结： count （列）不计算 null               <2> group  by  ... having  .... 分组筛选 一旦涉及 group  by ，记住两点：1， select 后面只能跟分组列及聚合函数，2， where 前 group  by 中 having 在后，顺序不能变， where 筛选的是记录， having 筛选的是组。 --例子: 将班级中及格的同学按性别分组，找到平均分在80分以上的组 select  性别， count (*）， sum (成绩), avg (成绩), max (成绩), min (年龄) from  学生表 where  成绩>=60 group  by  性别 having  avg (成绩)>80            a. 统计每个部门的最高工资 SQL> select  deptno, max (sal) from  emp group  by  deptno;             b.  统计每个部门的最高工资 ,显示其中最高工资大于2900的部门 SQL> select  max (sal) ,deptno from  emp group  by  deptno having  max (sal)>2900; ---- -- ----          c. 统计工资在2900元以上的所有员工所在部门的最高工资 , 显示其中最高工资大于4000的部门 SQL> select  deptno, max (sal) from  emp where  sal>2900   group  by  deptno having  max (sal)>4000;          <3> max   min  avg         a.显示所有员工中的最高工资 SQL> select  max (sal),ename from  emp;           b.显示不同工种的最高工资 SQL> select  max (sal),job from  emp group  by  job;           c.显示不同部门的平均工资，并保留两位小数 SQL> select  deptno,round( avg (sal),2) from  emp group  by  deptno;       <4> distinct  消除重复记录 SQL> select  distinct  job from  emp;