`
merrymode
  • 浏览: 52981 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

oracle 学习笔记(1)

 
阅读更多

--更改用户口令
alter user test identified by test

--DDL 数据定义语言:操作数据库对象的语言,对数据库对象进行的创建(create),修改(alter),删除(drop)
--DML 数据操纵语言:对表的数据进行增(insert),删(delete),改(update),查(select)
--TCL 事物控制语言:事物的提交、回滚等。(commit,rollback,savepoint)
--DCL 数据控制语言:Grant、revoke

--sysdate  & sysTimestamp
select sysdate from dual;
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyyMMdd') from  dual;
select to_date('20010203','yyyy-MM-dd') from dual;
select systimestamp from dual;

-- rowID &  rowNum
select rowId,sysdate from dual;
select Rownum ,sysdate from dual;
select * from g_jobinfo where rownum <10;

--trancate & delete
--desc
desc tablename
--DML
select * from g_jobinfo;
--使用现有表创建新表 // create table tablename as select
create table gg_jobinfo as select ID,Name,City,DUty from g_jobInfo;
select *  from gg_jobinfo;
create table ggg_jobinfo as select ID,Name ,city,duty from g_jobinfo where 1=2;
select * from ggg_jobinfo;

--ROLLBACK
--rollback to savepoint svt;
select * from gg_jobinfo where rownum<10;
update gg_jobinfo set city = 'WH' where id = 63;
savepoint marker1;
delete from gg_jobinfo where id = 62;
savepoint marker2;
rollback to savepoint marker1;

select * from gg_jobinfo where id = 63;
select * from gg_jobinfo where id = 62;

--union
select count(*) from (select ID,Name,City,DUty  from g_jobinfo union select * from gg_jobinfo);
select count(*) from gg_jobinfo;
select count(*) from g_jobinfo;
 
--union all
select count(*) from (select ID,Name,city,duty from g_jobinfo union all select * from gg_jobinfo);
select b.* from (select ID,name ,city ,duty from g_jobinfo
      union all
      select * from gg_jobinfo) b order by b.id DESC

--intersect
select count(*) from (select id,name ,city ,duty from g_jobinfo
       intersect
       select * from gg_jobinfo) ;
      
--minus
select Id ,name ,city ,duty from g_jobinfo
       minus
       select * from gg_jobinfo;
      
--单行函数:即对表中的没行记录,他们都返回一个值,单行函数可以用在select子句和where子句当中
--          主要包含日期函数、字符串函数、数字函数、转换函数,其他函数
--日期函数
select sysdate ,add_months(sysdate,2) from dual;
select sysdate,add_months(sysdate,-5) from dual;
select sysdate,add_months(sysdate,-9.981) from dual;

select months_between(sysdate,add_months(sysdate,-1)) from dual;

--last_day :返回指定日期当月的最后一天
select last_day(sysdate) from dual;
select last_day(add_months(sysdate,-9)) from dual;

--round
select ROUND(sysdate) from dual; --默认为不加参数,舍到最近的星期日
select round(sysdate ,'day') from dual;
select round(sysdate,'YEAR') from dual;--舍入到最近的年的开始:1月1日
select round(sysdate,'month') from dual;--月的第一日

select round(-1) from dual;

--next_day
select next_day(sysdate,'星期二') from dual;

--trunc
select trunc(sysdate,'YEAR') from dual;
select trunc(sysdate,'Month') from dual;
select trunc(add_months(sysdate,-15),'day') from dual;

--extract
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
--select extract(hour from sysdate) from dual;
--select extract(minute from sysdate)from dual;
--select extract(second from sysdate) from dual;
select to_char(sysdate,'YYYY') from dual;


--字符串函数
select chr(65) from dual;
select ascii('A') from dual;
select initcap('hello') from dual;--首字母大写
select ltrim('abdcsss','ab') from dual;
select Rtrim('abdcsss','sss') from dual;
select trim('abc_abe_abd_ea') from dual;

select translate('我是谁abcdef','我abde','1245') from dual;
select  replace('我是谁abcdef','我','yyy') from dual;
select instr('我是谁abcdef','c') from dual;
select subSTR('我是谁abcdef',3,4) from dual;--从第三个开始,截取4个字符,索引从1开始
select concat('我是谁abcdef','我是谁abcdef') from dual;
select LPAD('function',15,'=') from dual;
select RPAD('function',15,'=') from dual;

select length('skjkasdf') from dual;

--decode
select id ,decode(city,'SH','上海','WH','武汉','QD','青岛')from gg_jobinfo;
select id,
       case city
         when 'SH' then '上海'
         when 'WH' then '武汉'
         when 'QD' then 'qingdao'
       end
         from gg_jobinfo;
        
--数字函数
select sign(34) from dual;
select sign(0) from dual;
select sign(-1333) from dual;

--转换函数
select to_char(sysdate,'yyyy"年"MM"月"dd"日"') from dual;
select to_char(sysdate,'yyyy"华裔"MM"华裔"dd"华裔"') from dual;
select to_char(sysdate,'YYYY"年"fmMM"月"fmDD"日"  HH24:MI:SS') from dual;
select to_char(sysdate,'YYYY"年"fmMM"月"fmDD"日"  HH12:MI:SS') from dual;

select to_char(id,'L99999') from g_jobinfo;
select to_char(id,'C99999') from g_jobinfo;
select to_char(id,'999,999.00') from g_jobinfo;

--to_date
select to_date('2011-02-12','yyyy-mm-dd') from dual;

--NVL
--select nvl(colomn,'') from dual
--NVL2

--count
select count(*) from gg_jobinfo;
select count(duty) from gg_jobinfo;
select count(distinct duty) from gg_jobinfo;
--group by
select distinct city from gg_jobinfo;
select city,count(*) cnt  from gg_jobinfo group by city;

--分析函数 row_number(),rank(),dense_rank();
select * from emp;--以scott模式下的emp表为例

select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from emp;
select ename,job,deptno,sal from emp order by sal Desc;

select ename,job,deptno,sal ,row_number() over(partition by deptno order by sal desc) as sal_rank from emp;
select * from (select ename,job,deptno,sal ,row_number() over(partition by deptno order by sal desc) as sal_rank from emp) where sal_rank=1;
select ename,job,deptno,sal,rank() over(partition by deptno order by sal desc) as sal_rank from emp;
select ename,job,deptno,sal, dense_rank() over(partition by deptno order by sal desc) as sal_rank from emp;

分享到:
评论

相关推荐

    oracle学习笔记1

    尚学堂马世兵oracle课堂笔记一,尚学堂马世兵oracle课堂笔记。

    Oracle学习笔记1.docx

    Oracle学习笔记1.docx

    Oracle学习笔记精华版

    Oracle学习笔记精华版Oracle学习笔记精华版Oracle学习笔记精华版Oracle学习笔记精华版

    Oracle学习笔记

    Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记

    Oracle学习笔记 Oracle学习笔记

    Oracle非常有用的笔记。。。。。。。。。。。Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记

    oracle学习笔记 oracle学习笔记

    oracle学习笔记 oracle学习笔记oracle学习笔记 oracle学习笔记

    oracle 个人学习笔记

    oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记

    Oracle学习笔记 PDF

    本文档主要是网易云李兴华老师进行授课时所作笔记,从Oracle11g数据库的安装到复杂查询做了详细的文档记录。

    Oracle学习笔记.pdf

    oracle学习笔记,包含所有oracle概念,包,函数,oracle的组成,oracle的plsq,oracle的存储过程,oracle的事务等

    Oracle学习笔记.doc

    Oracle学习笔记 以下是我这一周学习oracle整理的笔记,包括课堂的内容和自己看额外看的视频补充的一些内容,基本上囊括了所有oracle的基本知识。主要的形式是例子代码加代码解释加运行结果,我个人认为对于没有学习...

    oracle学习笔记-入门基础

    oracle学习笔记-入门基础-01-张园

    韩顺平oracle学习笔记.pdf

    分享给需要的朋友们韩顺平oracle学习笔记.pdf 大家来下载吧!

    Oracle学习笔记1.doc

    新手用的Oracle参考资料,里面详细的记录了Oracle的基础命令

    Oracle学习笔记-日常应用、深入管理、性能优化

    资源名称:Oracle学习笔记-日常应用、深入管理、性能优化内容简介:Oracle学习笔记-日常应用、深入管理、性能优化Oracle 11g是最具代表性的高端关系型数据库管理系统,它在世界各地的大型商务数据库应用系统中被广泛...

    oracle学习笔记整理

    学习oracle知识笔记整理,包括pl/sql编程,过程、函数、游标开发等。

    oracle学习笔记

    oracle学习笔记

    oracle学习笔记.txt

    超详细Oracle学习笔记,详细记录了oracle的学习过程中遇到的各种问题及基础知识,适合初中级oracle使用人员学习参考。

Global site tag (gtag.js) - Google Analytics