Oracle: 四、函数,聚合函数,分组,字符串连接(中)

吞吞吐吐的 2017-11-09

函数 Oracle SQL BY demo Group

内容简介

   1,Demo ,函数练习,涉及的用户有scott2,Demo 聚合函数、分组、字符串连接。

 

技术与环境

操作系统:

windows

语言类别:

SQL之PL/SQL

thankyou: sunshine, 谢谢你的默默付出

数据库:

Oracle

学习软件:

Oracle 10g

课程总策划:

yuanbo

English name:

sunshine

个人主页:

http://www.cnblogs.com/ylbtech/

科研团队:

ylbtech

教研团队:

ylbtech

 

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,wheregroup byhaving在后,顺序不能变,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;

 

本文转自ylbtech博客园博客,原文链接:http://www.cnblogs.com/ylbtech/archive/2012/08/09/2630678.html,如需转载请自行联系原作者

登录 后评论
下一篇
我是小助手
29027人浏览
2019-07-31
相关推荐
Oracle操作总结
302人浏览
2011-02-07 16:42:00
Oracle聚合连接字符串
645人浏览
2017-10-30 13:25:00
group by 和聚合函数
420人浏览
2015-09-07 14:12:00
Oracle中的聚合函数
336人浏览
2013-05-26 16:23:40
SQL优化一(SQL使用技巧)
647人浏览
2017-05-14 20:59:00
Oracle数据库
719人浏览
2018-07-28 08:55:39
SQL数据库学习之路(六)
279人浏览
2018-10-27 17:48:57
Oracle-单表查询
425人浏览
2018-12-09 14:47:27
0
0
0
625