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

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

--============================================ ---ylb：Oracle --15:29 2011-12-30 --1,函数练习，涉及的用户有scott --============================================    --1,例:提取 7654 号雇员姓名的 2－4位 SQL>select substr(ename,2,3)from empwhere 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 JOBFROM EMP WHERE COMMIS 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 ENAMEFROM EMP WHERE LENGTH(ENAME)=5; --11.显示不带有"T"的员工的姓名. SELECT ENAMEFROM EMP WHERE ENAMENOT 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 成绩>=60group by 性别having avg(成绩)>80           a. 统计每个部门的最高工资 SQL>select deptno,max(sal)from empgroup by deptno;            b.  统计每个部门的最高工资 ,显示其中最高工资大于2900的部门 SQL>select max(sal) ,deptno from empgroup by deptnohaving max(sal)>2900; ---- -- ----         c. 统计工资在2900元以上的所有员工所在部门的最高工资 , 显示其中最高工资大于4000的部门 SQL>select deptno,max(sal)from empwhere sal>2900  group by deptno having max(sal)>4000;         <3>max  min avg        a.显示所有员工中的最高工资 SQL>select max(sal),enamefrom emp;          b.显示不同工种的最高工资 SQL>select max(sal),jobfrom empgroup by job;          c.显示不同部门的平均工资，并保留两位小数 SQL>select deptno,round(avg(sal),2)from empgroup by deptno;      <4>distinct 消除重复记录 SQL>select distinct jobfrom emp;