Oracle學習筆記—(二)

Oracle學習筆記---(二)

二

在SQLPLUS下，實現中-英字元集轉換

alter session set
nls_language='AMERICAN';
alter session set nls_language='SIMPLIFIED
CHINESE';  

主要知識點：
一、有關表的操作

   1)建表
   create table  test as
select * from  dept;     --從已知表複製資料和結構
   create table  test as select *
from  dept  where 1=2;     --從已知表複製結構但不包括資料
   2)插入資料：
   insert
into  test  select * from dept;

二、運算子
  
   算術運算子：+  -  *  /
可以在select 語句中使用
   串連運算子：||     select  deptno|| dname from
dept;                    
   比較子：>  >=   =  !=  <  
<=    like   between   is null    in
   邏輯運算子：not   and   or  
  
集合運算子：      intersect  ，union，  union all，   minus  
    
要求：對應集合的列數和資料類型相同
           查詢中不能包含long 列
          
列的標籤是第一個集合的標籤
           使用order by時，必須使用位置序號，不能使用列名

  
例：集合運算子的使用：
      intersect  ，union，  union all，   minus  
    select *
from emp  intersect select * from emp  where deptno=10 ;
    select * from
emp  minus  select * from emp  where deptno=10;
    select * from emp where
deptno=10  union  select * from  emp where deptno in (10,20);      --不包括重複行

    select * from emp where deptno=10  union  all select * from  emp where
deptno in (10,20);  --包括重複行

三，常用
ORACLE  函數
          sysdate為系統日期    
dual為虛表
一）日期函數[重點掌握前四個日期函數]
1,add_months[返回日期加(減)指定月份後(前)的日期]
select
sysdate  S1,add_months(sysdate,10)  S2,
                    add_months(sysdate,5)  S3  from
dual;

2,last_day [返回該月最後一天的日期]
   select last_day(sysdate) from dual;

3,months_between[返回日期之間的月份數]
   select sysdate S1,
months_between('1-4月-04',sysdate) S2,
                    
months_between('1-4月-04','1-2月-04') S3 from dual

4,next_day(d,day):
返回下個星期的日期,day為1-7或星期日-星期六,1表示星期日    
    select sysdate
S1,next_day(sysdate,1) S2,
                      next_day(sysdate,'星期日') S3
FROM DUAL  
5,round[舍入到最接近的日期](day:舍入到最接近的星期日)

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

6,trunc[截斷到最接近的日期]
   select      sysdate
S1,
        trunc(sysdate)
S2,
        trunc(sysdate,'year')  YEAR,
        trunc(sysdate,'month')
MONTH ,
        trunc(sysdate,'day')   DAY  from
dual

7,返回日期列表中最晚日期
   select
greatest('01-1月-04','04-1月-04','10-2月-04')  from
dual

二）字元函數(可用於字面字元或資料庫列)
   1，字串截取
   select
substr('abcdef',1,3) from dual

   2,尋找子串位置
   select
instr('abcfdgfdhd','fd') from dual

   3,字串串連
   select
'HELLO'||'hello world'  from dual;

   4, 1)去掉字串中的空格
        select
ltrim('   abc')  s1,
               rtrim('zhang   ')  s2,
              
trim('   zhang    ') s3 from dual

      2)去掉前置和尾碼
        select
trim(leading  9 from  9998767999) s1,
               trim(trailing 9
from  9998767999) s2,
               trim(9  from  9998767999) s3  from
dual;
  
   5,返回字串首字母的Ascii值
     select  ascii('a') from
dual

   6,返回ascii值對應的字母
     select  chr(97) from dual

  
7,計算字串長度
     select  length('abcdef') from dual

  
8,initcap（首字母變大寫） ,lower(變小寫),upper(變大寫)
    
select  lower('ABC')  s1,
             upper('def')  s2,
            
initcap('efg') s3  from  dual;
   9,Replace
     select
replace('abc','b','xy') from dual;

   10,translate
     select
translate('abc','b','xx') from dual;      -- x是1位

   11,lpad [左添充] rpad
[右填充](用於控制輸出格式)
     select  lpad('func',15,'=') s1, rpad('func',15,'-') s2
from dual;
     select  lpad(dname,14,'=') from dept;

   12,
decode[實現if ..then 邏輯]
   select
deptno,decode(deptno,10,'1',20,'2',30,'3','其他')  from
dept;

三）數字函數

   1，取整函數（ceil  向上取整,floor 向下取整）
    select
ceil(66.6) N1,floor(66.6) N2  from dual;

   2, 取冪(power) 和
求平方根(sqrt)
    select power(3,2) N1,sqrt(9) N2 from dual;

  
3,求餘
    select  mod(9,5) from dual;

  
4,返回固定小數位元  (round:四捨五入，trunc:直接截斷)
    select round(66.667,2)
N1,trunc(66.667,2)  N2  from dual;  
   5,傳回值的符號(正數返回為1,負數為-1)
    select
sign(-32),sign(293)  from dual;

四）轉換函式
1，to_char()[將日期和數字類型轉換成字元類型]
      1)   select to_char(sysdate)
s1,
                  to_char(sysdate,'yyyy-mm-dd')
s2,
                  to_char(sysdate,'yyyy')  s3,
                  to_char(sysdate,'yyyy-mm-dd  hh12:mi:ss')  s4,
                  to_char(sysdate,
'hh24:mi:ss') s5,
                  to_char(sysdate,'DAY') s6   from
dual;
        
      2)   select sal,to_char(sal,'＄99999')
n1,to_char(sal,'＄99,999') n2 from emp

  2,
to_date()[將字元類型轉換為日期類型]      
      insert into emp(empno,hiredate)
values(8000,to_date('2004-10-10','yyyy-mm-dd'));

  3,
to_number()  轉換為數字類型      
       select
to_number(to_char(sysdate,'hh12'))  from
dual;  //以數字顯示的小時數
      
五）其他函數
      user:
        返回登入的使用者名稱稱  

        select user from dual;

      vsize:

        返回運算式所需的位元組數
        select vsize('HELLO') from
dual;

      nvl(ex1,ex2):     　
        
ex1值為空白則返回ex2,否則返回該值本身ex1（常用）  
           例：如果僱員沒有傭金，將顯示0，否則顯示傭金

           select comm,nvl(comm,0)  from
emp;

      nullif(ex1,ex2):  
        值相等返空，否則返回第一個值
          
例：如果工資和傭金相等，則顯示空，否則顯示工資
           select nullif(sal,comm),sal,comm from
emp;

      coalesce: 　
        返回列表中第一個非空運算式
        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;

六）分組函數
     max  
min   avg  count   sum
    1，整個結果集是一個組

     1) 求部門30
的最高工資，最低工資,平均工資，總人數，有工作的人數，工種數量及工資總和
        select max(ename),max(sal),

               min(ename),min(sal),
              
avg(sal),
               count(*) ,count(job),count(distinct(job))
,
               sum(sal)  from emp   where deptno=30;
    2, 帶group by
和  having
的分組

      1)按部門分組求最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和
        select
deptno, max(ename),max(sal),
                      
min(ename),min(sal),
                      
avg(sal),
                       count(*) ,count(job),count(distinct(job))
,
                       sum(sal) from emp   group by
deptno;

      2)部門30的最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和        
        select
deptno, max(ename),max(sal),
                      
min(ename),min(sal),
                      
avg(sal),
                       count(*) ,count(job),count(distinct(job))
,
                       sum(sal) from emp   group by
deptno  having  deptno=30;

    3, stddev    
返回一組值的標準差
          select  deptno,stddev(sal)  from emp  group by
deptno;
       variance  
返回一組值的方差差
          select  deptno,variance(sal)  from  emp   group by
deptno;

   4, 帶有rollup和cube操作符的Group By
          rollup
按分組的第一個列進行統計和最後的小計
          cube  
按分組的所有列的進行統計和最後的小計
        select  deptno,job ,sum(sal) from emp group
by  deptno,job;
        select  deptno,job ,sum(sal) from emp group
by  rollup(deptno,job);    
              
         cube
產生組內所有列的統計和最後的小計

        select  deptno,job ,sum(sal) from emp group
by  cube(deptno,job);

四、暫存資料表

    只在會話期間或在交易處理期間存在的表.
    暫存資料表在插入資料時，動態分配空間    

    create
global  temporary
table  temp_dept
    (dno  number,
    dname  varchar2(10))
    on
commit delete rows;

    insert into temp_dept
values(10,'ABC');
    commit;
    select * from
temp_dept;  --無資料顯示,資料自動清除

    
on  commit  preserve  rows:在會話期間表一直可以存在（保留資料）
    
on  commit  delete    rows:事務結束清除資料（在事務結束時自動刪除表的資料）