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;