oracle資料庫操作

標籤：

　　　oracle資料庫操作

 

一：解鎖Scott/tiger 使用者(locked)
alter user scott account lock; --鎖定
alter user scott account unlock; --解鎖
alter user scott identified by tiger;  --改密碼
grant dba to scott; --賦許可權

二：查詢

2.1：查詢表中所有資料

select * from emp;
select * from dept;

2.2：查詢部分欄位
select empno,ename,job from emp;

2.3：帶條件查詢
select * from EMP where ename = ‘SMITH‘;--字串內容中區分大小寫

--去掉重複內容
select distinct job from emp;
select count(distinct job) from emp;

--給欄位取別名 (無單引號，別名包含空格則需要雙引號)
select  ename as 職員姓名,sal "工  資" from emp;

--算數操作（與空值操作結果為空白  NULL）
select sal+nvl(comm,0) from emp;

--字串拼接( ||  可以拼接任何類型)
select ‘職員姓名:‘||ename||‘ 工資:‘||sal from emp;

--排序
select * from emp order by sal desc,empno asc;
select * from emp order by comm desc nulls last;  -- nulls first;

--模糊查詢：萬用字元( % _ )，空值查詢，in  between and
select * from emp where comm is  not  null;

% 任意長度，任意內容
_ 一個長度，任意內容

--查詢 名字以 J 開頭的人
select * from emp where ename like ‘J%‘;
select * from emp where ename like ‘%A%‘;
select * from emp where ename like ‘J____‘;


--偽列
--Rowid ：是表中行的儲存地址
--Rownum ：是查詢返回的結果集中行的序號
select rowid,rownum,emp.* from emp
        where rownum <=2 ;
-- rownum  作為條件時  只能使用小於或小於等於
--實現SQLServer中的top

--彙總分組 count  sum  avg  max min   group by  having
    --統計每個職位的最高工資
    select job,max(sal) from emp group by job;
    --統計每個部門的平均工資,低於2000的不顯示，結果按降序排列
    select deptno,avg(sal)  
    from emp
    group by deptno
    having avg(sal)>=2000
    order by avg(sal) desc;

--串連查詢(內串連)  --公用欄位
  --查詢所有職員以及所在部門的詳細資料
  select * from emp a  inner join dept b on a.deptno = b.deptno;
  select * from emp a ,dept b where a.deptno = b.deptno;
  --外串連  left  right full
  select * from emp a full join dept b on a.deptno = b.deptno;
  --交叉串連 cross
  select * from emp a cross join dept b ;
 
create table team
(
 teamname varchar2(10)
);
insert into team values (‘A‘);         
insert into team values (‘B‘);
insert into team values (‘C‘);

select * from team a cross join team b
where  a.teamname>b.teamname;

--自串連 ： 查詢KING的下屬
select * from emp where mgr
       = (select empno from emp where ename=‘KING‘);
       
select * from emp a join emp b
         on a.mgr = b.empno
         where b.ename=‘KING‘;

-- 子查詢  = < >  <=  >=     in     not in
   --查詢所有ACCOUNTING部門的職員
   select * from emp where deptno =
          (select deptno from dept where dname =‘ACCOUNTING‘);

   --查詢所有位置在NEW YORK與 DALLAS 的部門職員
   select * from emp where deptno in
          (select deptno from dept where loc in (‘NEW YORK‘,‘DALLAS‘));

    --查詢所有與SMITH同部門同職位的職員（in，= 可以包含多個欄位）
  select * from emp where (deptno , job)
           in (select deptno,job from emp where ename=‘SMITH‘);

-- 內聯視圖子查詢
select * from
  (select a.*,b.dname,b.loc from emp a join dept b on a.deptno=b.deptno) Y ;

   --使用串連查詢作為子查詢 要去掉重複欄位

   --查詢工資高於每部門平均工資的人
select *from emp a join    
   (select deptno,avg(sal) as avgsal from emp group by deptno) Y
   on a.deptno = y.deptno
   where a.sal>y.avgsal;
   
   --統計每個部門的詳細資料與部門平均工資,低於2000的不顯示，結果按降序排列
select *from dept a join    
   (select deptno,avg(sal) as avgsal from emp group by deptno) Y
   on a.deptno = y.deptno
   where avgsal>=2000;

   --查詢工資最低5人(使用偽列rownum與排序)
select rownum,Y.* from
(select * from emp order by sal ) Y
where rownum<=5;

   -- 分頁查詢 將偽列轉為暫存資料表的一個普通列
   select * from
 ( select rownum as rn,emp.* from emp where rownum<=16) Y
  where y.rn  >=11;  



--函數
--日期函數

--日期轉換（一般採用字串轉換的形式描述日期） to_date(日期文字，日期格式)
select to_date(‘2012-7-28‘,‘yyyy-mm-dd‘) from dual;


--1.查詢系統時間 sysdate
select sysdate from dual;

--2.在一段時間上添加月份 add_months(日期，數字)
select add_months(sysdate,-10) from dual;

--3.求某日期所在月的最後一天 last_day（日期）
select last_day(sysdate) from dual;

--4.求兩個日期之間的月份差（返回小數） months_between(日期1，日期2)
select months_between(to_date(‘2012-12-12‘,‘yyyy-mm-dd‘),sysdate)*30 from dual;

 --** 相差的天數：減法
select to_date(‘2012-12-12‘,‘yyyy-mm-dd‘)-sysdate  from dual;
--5.求指定日期之後第一個星期幾  next_day(日期，星期幾)
select next_day(sysdate,‘星期六‘) from dual;
select next_day(sysdate,7) from dual;
       --(1-7  星期日-星期六)  

--6.四捨五入 round(日期，日期代碼)
select round(sysdate,‘yyyy‘) from dual;
--7.提取年月日 extract(year/month/day from 日期)
select extract(year from sysdate) from dual;   --返回數字
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
--8.to_char 提取日期部分 to_char(日期，格式字串)
select to_char(hiredate,‘yyyy"年"mm"月"dd"日" hh24:mi:ss ‘)
 from emp;
 
select to_char(hiredate,‘yyyy‘) from emp; --字串
select to_char(hiredate,‘mm‘) from emp;
select to_char(sysdate,‘day‘) from dual;  --星期

-- 查詢所有周末入職的職員
select * from emp where to_char(hiredate,‘d‘) in (‘1‘,‘7‘);

--字串
--字串長度 length(字串) vsize(字串)
select length(‘hello world!!!!‘)  from dual;
select vsize(‘你好‘) from dual;
--拼接字串 concat(字串1，字串2)  ||
select concat(‘hello‘,‘world‘) from dual;
--截取字串 substr(字串，開始，長度)
select substr(‘hello world !!!!‘,1,5) from dual;
 --0 1 都表示第一個

--去掉首尾的指定內容 trim(字元 from 字串)
select trim(‘x‘ from ‘xxxxxxxxxxxxxhexxlloxxxxxxxxx‘) from dual;
--去空格 trim()
select trim(‘ he  llo  ‘) from dual;
--內容替換 replace(字串，替換內容，替換字元)
select replace(‘hello‘,‘l‘,‘x‘) from dual;
select replace(‘ he  llo  ‘,‘ ‘,‘‘) from dual;

--多重替換translate(字串，替換內容，替換字元)
select translate(‘hello world!!!!‘,‘lr‘,‘xx‘) from dual;

--尋找字元 instr(字串，尋找內容)
select instr(‘hello world!!!!‘,‘l‘) from dual;
         -- instr(字串，尋找內容,開始位置)
select instr(‘hello world!!!!‘,‘l‘,6) from dual;
         --instr(字串，尋找內容,開始位置，第幾個)
select instr(‘hello world world !!!!‘,‘l‘,6,2) from dual;

--ascii碼  ascii(字元)  chr(數字)
select ascii(‘a‘) from dual;
select chr(97) from dual;

--轉為大小寫  upper  lower
select upper(‘hello‘) ,lower(‘HELLO‘) from dual;

--數學函數
select mod(3,10) from dual;   --模數
select ceil(99.0000000001) from dual; --向上取整
select floor(99.999999) from dual;  --向下取整
select round(99.9) from dual;  --四捨五入
select round(99.93,1) from dual;  --四捨五入，精確位元
select dbms_random.value(55,100) from dual;  --隨機數
select dbms_random.value() from dual;

--轉換函式  to_char(內容，格式)  to_number(數字字元) to_date(日期字元，日期格式)
select to_char(sysdate,‘yyyy-mm-dd‘) from dual;
select to_char(12345.126,‘L999999.99‘) from dual;  --L  local

--空值處理
select * from emp;
select sal+comm from emp;

--空值轉為替代值  nvl(欄位，替換值)
select sal+nvl(comm,0) from emp;

--不為空白 轉為值1 為空白轉為值2  nvl2(欄位，值1，值2)
select nvl2(comm,comm,0) from emp;

--參數1 等於參數2 則變為空白  否則為參數1  nullif(參數1，參數2)
select nullif(comm,300) from emp;

select * from emp;
--wm_concat    --行列轉換函式 10g
select deptno,  wm_concat(ename)  from emp group by deptno

--分析函數（進階排序功能）
select  rownum,Y.* from (
select  emp.* from emp order by sal) Y


select ename,sal,row_number() over(order by sal  )  ,
       rank() over(order by sal ),
       dense_rank() over(order by sal )
 from scott.emp  ;
 
 

oracle資料庫操作