文档章节

Oracle SQL总结

playgame
 playgame
发布于 2016/07/13 11:27
字数 1180
阅读 7
收藏 0

全部调试通过的。

select * from dual;


-- Oracle 日期格式函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_char(sysdate,'yyyy') as nowYear from dual;
select to_char(sysdate,'mm') as nowMonth from dual;
select to_char(sysdate,'dd') as nowDay from dual;
select to_char(sysdate,'hh24') as nowHour from dual;
select to_char(sysdate,'mi') as nowminute from dual;
select to_char(sysdate,'ss') as nowSecibd from dual;

--获得小时数 
 select sysdate ,to_char(sysdate,'hh') from dual;
 select sysdate ,to_char(sysdate,'hh24') from dual;


select to_date('2016-10-10 12:10:10','yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_date('2016-10-10','yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_date('2016-10-10','yyyy-mm-dd') as nowTime from dual;


select to_char(to_date(222,'J'),'Jsp') from dual;

--求某天是星期
select to_char(to_date('2016-7-13','yyyy-mm-dd'),'day') as weekDay from dual;

select to_char(to_date('2016-7-13','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American') as weekDay from dual;

--  两个日期间的天数 
select floor(sysdate-to_date('2016-7-10','yyyy-mm-dd')) as day from dual;
select floor(to_date('2016-10-10','yyyy-mm-dd')-to_date('2016-7-10','yyyy-mm-dd')) from dual;


-- 时间为null的用法  
select 1,to_date(null) from dual;


--变量列表
select * from nls_session_parameters;
select * from V$nls_Parameters where parameter='NLS_LANGUAGE';


-- 查找月份
select months_between(to_date('2016-9-10','yyyy-mm-dd'),to_date('2016-1-10','yyyy-mm-dd')) as allMonth from dual;
select months_between(to_date('2016-5-20','yyyy-mm-dd'),to_date('2016-1-10','yyyy-mm-dd')) as allMonth from dual;

-- 当前时间
select sysdate from dual;
 
--  找出今年的天数

select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;

-- 一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual;
 

-- round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1, round(sysdate) S2 , round(sysdate,'year') YEAR, round(sysdate,'month') MONTH , round(sysdate,'day') DAY from dual


-- trunc[截断到最接近的日期,单位为天] ,返回的是日期类型

select sysdate S1,trunc(sysdate) S2,trunc(sysdate,'year') YEAR,trunc(sysdate,'month') MONTH ,trunc(sysdate,'day') DAY from dual;        

-- 返回日期列表中最晚日期
 select greatest('2001-1-04','2020-9-04','2010-2-04') from dual;
 
-- 更新时间
select sysdate,to_char(sysdate+1*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,add_months(sysdate,3) as newTime from dual ;
select sysdate,to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;


-- 字符串截取
select substr('this is one char for test',1,5) from dual;

-- 查找子串位置
select instr('this is one char for test','char') from dual;


--字符串连接
select 'hello'||'HanMeiMei' as sayHello from dual;

select concat( 'hello','HanMeiMei') as sayHello from dual;

-- 去掉字符串中的空格
select ltrim(' this') s1,rtrim('zhangsan ') s2,trim(' lisi ') s3 from dual;

-- 去掉前导和后缀
select trim(leading 1 from 11111345345345111111) s1,trim(trailing 1 from 11111345345345111111) s2,trim(1 from 11111345345345111111) s3 from dual;

-- 字符串长度 
select length('this is test for oracle') from dual;


-- initcap(首字母变大写) ,lower(变小写),upper(变大写)
select lower('ABC') s1, upper('def') s2, initcap('efg') s3 from dual;

-- Replace
select replace('abc','b','xy') from dual;

-- translate
select translate('abc','b','qqqq') from dual;

-- lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;

-- decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值
select grade_id,decode(grade_id,1,'一年级',2,'二年级',3,'三年级','无分班') from student;

-- case[实现switch ..case 逻辑]
select 
 case when grade_id=1 then '一年级' 
   when grade_id=2 then '二年级' 
    when grade_id=3 then '三年级' 
      else 'unkonwn' end
        from student;

-- 取整函数(ceil 向上取整,floor 向下取整)
 select ceil(66.6) N1,floor(66.6) N2 from dual;

-- 求余数
select mod(9,2) from dual;

-- 返回固定小数位数 (round:四舍五入,trunc:直接截断)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

-- 返回值的符号(正数返回为1,负数为-1)
select sign(-32),sign(293) from dual;

-- to_number() 转换为数字类型
select to_number(to_char(sysdate,'hh12')) from dual;


-- nvl(ex1,ex2):ex1值为空则返回ex2,否则返回该值本身ex1(常用)
select empno,sum(sal+nvl(comm,0)) from emp group by empno;

--返回列表中第一个非空表达式
select comm,sal,coalesce(comm,sal,sal*10) from emp;

-- nvl2(ex1,ex2,ex3) :如果ex1不为空,显示ex2,否则显示ex3
select nvl2(comm,ename,'') as HaveCommName,comm from emp;

-- 分组函数; 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和
select * from emp where deptno=30;
select max(sal) as maxSal,min(sal) as minSal,avg(sal) as avgSal,count(*) as TotalEMP,sum(sal) as totalSal,count(job),count(distinct(job)) from emp where deptno=30;


-- 按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和
select max(sal) as maxSal,min(sal) as minSal,count(*) as TotalEMP,count(job) as hasJob,count(distinct(job)) as jobCount,sum(sal) as totalSal from emp group by deptno;

-- 部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和 
select max(sal) as maxSal,min(sal) as minSal,count(*) as TotalEMP,count(job) as hasJob,count(distinct(job)) as jobCount,sum(sal) as totalSal from emp group by deptno having deptno=30;
 

参考文章:http://www.cnblogs.com/chuncn/archive/2009/04/29/1381282.html

© 著作权归作者所有

共有 人打赏支持
playgame
粉丝 0
博文 26
码字总数 10272
作品 0
洛阳
QA/测试工程师
加载中

评论(1)

playgame
playgame
select * from student where regexp_like(student_name,'^[1-3]');

select * from student where regexp_like(student_name,'[^1-2]');
oracle9i和oracle10g多版本共存,oracle9i不能连接问题总结

oracle9i用pl/slq developer报" ORA-12154: TNS:无法解析指定的连接标识符"错误解决方案总结:机器上先后安装了oracle9i和oracle10g,安装好后,10g能用pl/sql developer正常连接,但9i总是连不上...

Samir
2013/09/12
0
0
sqlplus命令行总结

1.[oracle@zh888 ~]$ sqlplus /nolog //启动oracle数据库实例 SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 11 00:29:00 2013 Copyright (c) 1982, 2005, Oracle. All rights res......

zh888
2013/05/11
0
0
oracle10g总结

1.[oracle@zh888 ~]$ sqlplus /nolog //启动oracle数据库实例 SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 11 00:29:00 2013 Copyright (c) 1982, 2005, Oracle. All rights res......

zh888
2014/05/21
0
0
Oralce OMF 功能详解

OMF,全称是Oracle_Managed Files,即Oracle文件管理,使用OMF可以简化管理员的管理工作,不用指定文件的名字、大小、路径,其名字,大小,路径由oracle 自动分配。在删除不再使用的日志、数据...

yangfei86
2014/07/17
0
0
Oracle Move命令总结

Oracle Move命令总结 2014-03-13 08:48 206人阅读 评论(0) 收藏 举报   Oracle Move命令总结   从8i开始,oracle开始提供Move的命令。我们通常使用这个命令,将一个table segment从一个t...

快速开发师
2017/01/13
106
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

qduoj~前端~二次开发~打包docker镜像并上传到阿里云容器镜像仓库

上一篇文章https://my.oschina.net/finchxu/blog/1930017记录了怎么在本地修改前端,现在我要把我的修改添加到部署到本地的前端的docker容器中,然后打包这个容器成为一个本地镜像,然后把这...

虚拟世界的懒猫
今天
1
0
UML中 的各种符号含义

Class Notation A class notation consists of three parts: Class Name The name of the class appears in the first partition. Class Attributes Attributes are shown in the second par......

hutaishi
今天
1
0
20180818 上课截图

小丑鱼00
今天
1
0
Springsecurity之SecurityContextHolderStrategy

注:下面分析的版本是spring-security-4.2.x,源码的github地址是: https://github.com/spring-projects/spring-security/tree/4.2.x 先上一张图: 图1 SecurityContextHolderStrategy的三个......

汉斯-冯-拉特
今天
1
0
LNMP架构(Nginx负载均衡、ssl原理、生成ssl密钥对、Nginx配置ssl)

Nginx负载均衡 网站的访问量越来越大,服务器的服务模式也得进行相应的升级,比如分离出数据库服务器、分离出图片作为单独服务,这些是简单的数据的负载均衡,将压力分散到不同的机器上。有时...

蛋黄_Yolks
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部