Java基礎——Oracle（五）

標籤：查詢更新   升級   term   round   terminal   字串   schema   登入   comm   

一、Oracle  中的分頁

1) select * from emp;

2)select * ,rownum from emp; //這樣寫不行

3)select ename,job,sal,rownum from emp; //可以

ENAME      JOB             SAL     ROWNUM
---------- --------- --------- ----------
SMITH      CLERK        800.00          1
ALLEN      SALESMAN    1600.00          2
WARD       SALESMAN    1250.00          3
JONES      MANAGER     2975.00          4

4)select a1.*,rownum  from (select * from emp) a1 ;

5)select a1.*,rownum  from (select * from emp) a1 and rownum <=10  ; //只查前10條  用過一次,就不能再用第二次

6)select * from  (select a1.*,rownum 行號 from (select * from emp) a1 where rownum <=10)  where 行號>=5;   查的是5到10條

查詢的變化,如果有改動,只要改變裡面的查詢即可,不管是排序,還是指定列查詢

二、Oracle 中的函數

1.字串函數  

--lower (char) //  select lower(‘AAA‘) from dual; ->aaa

--upper(char)

--length() //select length(‘AAA‘) from dual -> 3

--substr(char,m,n) 取子串

--replace(char1,search_string,replace_string) //顯示所有emp姓名,用 a 替換所有的 A  -> select replace(ename,‘A‘,‘a‘) from emp;

--instr(char1,char2,[,n[,m]] 取子串在字串中的位置

2.數學函數 

--round(n,[m]) 四捨五入,省掉m則四捨五入到整數,如果m為正,則四捨五入到小數點的m位後,如果m是負數,則舍入到小數點的m位前

--trunc (n,[m]) 用於截取數字,省m則截掉小數部分,如果m為正,則四捨五入到小數點的m位後,如果m是負數,則舍入到小數點的m位前

--mod(m,n) 求模

--floor(n) 反回小於或是等於 n的最大整數

--ceil(n) 反回大於或是等於n 的最小整數

3.日期函數

--sysdate 返回系統時間     select sysdate from dual;  mysql 用 now() , sqlserver 用 getDate()

--add_month(d,n)  //尋找已經入職 8 個月多的員工 select * from scott.emp where  sysdate>add_months(hiredate,8);

--last_month(d) 返回指定日期所在月份的最後一天  //這個d 就是指定的日期

4.轉換函式

用於將資料從一種類型轉換成別一種在某些情況下,oracle server 允許值的資料類型和實際的不一樣,這時oracle 會隱含的轉變資料類型。比如 可以將 ‘11‘ 傳給 number類型。

反之也可以,將11 傳給 varchar2日期是否可以顯示時分秒?貨幣可否加 貨幣符號

SQL> select ename,to_char(hiredate,‘yyyy-mm-dd hh24:mi:ss‘) from scott.emp;// 本例沒寫錯,上面的確實是 hh24,並且 那個地方確實是 mi

yyyy 表示 年份

mm 表示 月份

dd 表示 天

hh24 表示 小時

mi 表示 分鐘

ss 表示 秒

如果要寫成  yyyy-MM-dd hh:mm:ss 這樣就不對了,因為 Oracle 的爛格式很特

5.系統函數

SYS_context(‘...‘,‘...‘)     //返回一個指定namespace下的parameter值。該函數可以在SQL和PL/SQL語言中使用。以下的例 子都要寫成 select sys_context(‘userenv‘,‘xxxx‘) from dual 這樣的方式：

1 terminal 當前會話客戶所對應的終端的標識符   select sys_context(‘userenv‘,‘terminal‘) from dual; -> AAA-74B992CC317

2 language 語言 ->SIMPLIFIED CHINESE_CHINA.ZHS16GBK

3 db_name 當前資料庫名稱 ->orcl

4 nls_date_format 當前會話所對應的日期格式 ->DD-MON-RR

5 session_user 當前會話所對應的資料庫使用者名稱 ->SA

6 current_schema 當前會話客戶所對應的預設方案名 SA

7 host 返回資料庫所在的主機名稱   ->WORKGROUP\AAA-74B992CC317

三、關於 dual 的說明

如下查詢  select 10+5 ; 可以發現出錯: 未找到要求的from 關鍵字,但這樣的寫法在mysql或sqlserver都是可以的，在oracle 中 要改寫成 select 10+5 from dual

T-SQL是SQL Server的語言引擎，而Oracle的語言引擎卻是 PLSQL。

這兩種查詢語言都對ANSI SQL-92標準進行了擴充以提供額外的支援力度。

用PLSQL執行資料查詢的時候，FROM子句是必須的，這同SQL Server的要求是不一樣的。

SELECT語句必須選擇針對的資料表。在Oracle資料庫內有一種特殊的表Dual。

Dual表是Oracle中的一個實際存在的表，任何使用者均可讀取，常用在沒有目標表的select中Dual表由Oracle連同資料字典一同建立，所有的使用者都可以用名稱DUAL訪問該表。這個表裡只有一列dummy，該列定義為VARCHAR2(1)類型，有一行值X。從DUAL表選擇資料常被用來通過SELECT語句計算常數運算式，由於DUAL只有一行資料，所以常數只返回一次。

==幾個常見的查詢

//查看當前串連的使用者select user from dual;

//查看當前日期select user from dual;

==幾個常見的查詢操作    

//用查詢結果建立新表create table T(id,name,sal, deptno)  as select empno,ename,sal,deptno from emp;

//使用特定的格式插入日期 (使用 to_date 函數)insert into emp values (2000,‘張三‘,‘XXX‘,9000,to_date(‘1999-12-15‘,‘yyyy-MM-dd‘),3000,500,20); 

//使用子查詢插入資料//例子 : 把emp表中某些合格資料,匯入到 T2表中create table T2(id number(5), stuName varchar2(20),deptNo number(5) ) ;insert into T2 (id,stuName,deptNO) select empno,ename,deptNO from emp where sal <2000

//使用子查詢更新資料//例子 希望工 scott 的 崗位,工資,獎金 和smith 一樣update emp set (job,sal,comm) =(select job,sal,comm from emp where ename=‘SCOTT‘) where ename=‘SMITH‘; 

四、關於 sys 和 system使用者

使用者和方案的關係 一個使用者被建立以後,會建立一個對應的方案,和使用者名稱一樣,方案裡裝的資料對象(表,視圖,序列....)

資料庫管理員

每個oracle都有一個或多個dba ,他的主要工作是什麼

1 安裝和升級oracle資料庫

2 建庫,建資料表空間,建表,視圖,索引

3 定製並實施備份計算和恢複計劃

4 資料庫的許可權管理,調優故障處理

5 進階dba 能參與項目開發,會寫sql語句,預存程序,觸發器,函數,約束等

管理員管理資料庫用的使用者是 sys 和 system

最主要的區別 :儲存的資料的重要性不同

sys: 所有的資料字典和基表和視圖都放在sys使用者中,這些東西對oracle的運行是至關重要的,由資料庫自已維護，任何使用者都不能修改,sys 使用者擁有 dba,sysdab,sysoper 角色或許可權 ,是高許可權使用者

sysdba >sysoper >dba

system 用於存放次一級的資料, 比如orcle 的一些特性或工具的管理資訊,它有dba,sysdba 角色或許可權

sys使用者必須以 as sysdba 或 sysoper 的方式登入 不能normal 方式登入

system 可以以sysdba 的身份登入的,如果以這樣的方式登入,其實登入的就是sys

五、資料庫(表)的邏輯備分與恢複

邏輯備份: 用工具 export 將資料對象的結構和資料以檔案的方式匯出的過程

邏輯恢複: 用工具 import 從備份的檔案把資料對象恢複的過程

物理備份和恢複: 在數庫open 的狀態下或關閉的狀態下均可進行,但邏輯備份和恢複必須在open的狀態的才能進行

== 匯出

分三種 匯出表 ,匯出方案, 匯出資料庫 //使用者和方案的關係:一個使用者建立以後,對應著一個方案,方案名和使用者名稱一樣,方案裡裝的是資料對象

匯出用 exp 命令 ,它有很多常用的選項

-- userid 用於指定執行匯出操作的使用者名稱,口令,連接字串 //scott/[email protected]

-- tables 用於指定要匯出的表

-- owner  執行匯出操作的方案

-- full=y 指定匯出的是整個資料庫

-- inctype 指匯出操作的增量類型

-- rows 指定匯出操作是否要匯出表中的資料 // rows=n 表示不匯出資料,只匯出表結構

-- file 指定檔案名稱

--匯出表

1) 匯出自己的表 比如 scott 匯出自已的 emp 和 dept 表

exp userid=scott/[email protected] tables=(emp,dept) file="c:\scott.dmp"//注意 export 是oracle帶的工具,在 C:\oracle\product\10.2.0\db_2\bin 目錄下 叫 exp.exe

2) 匯出其他方案中的表

要匯出其他方案中的表,要有dba 的許可權或  exp_full_database 許可權

3) 匯出表結構

在上面的操作後面加上 rows=n 即可

4) 使用直接匯出的方式

在上面的語句後面加上 direct=y 即可

速度快,資料量大的時候適用,但要求 資料庫的字元集和用戶端的字元集一致,否則會報錯

== 匯入

匯入有匯入表,匯入方案,匯入資料庫

匯入用的命令是 imp

常用的選項

-- userid 指定匯入用的使用者名稱和密碼串連串

-- tables 執行匯入的表

-- fromuser 指定源使用者

-- touser :指定目標使用者

-- file 指定要匯入的檔案名稱

-- inctype 指匯入操作的增量類型

-- rows 指定匯入操作是否要匯入表中的資料  //rows=n 表示不匯入資料,只匯入表結構

-- ignore 如果表存在,則只匯入資料

匯入表

1) 匯入自己的表

imp userid=scott/[email protected] tables=(emp,dept) file=c:\scott.dmp

2) 把某個或某些表導給其他使用者

imp userid=system/[email protected] tables=(dept,emp)  file=c:\scott.dmp touser=zs//這裡不要加 scott.  還有,要注意約束造成的問題

3 匯入表結構

imp userid=scott/[email protected] tables=(emp,dept) file=c:\scott.dmp rows =n

4) 匯入資料

imp userid=scott/[email protected] tables=(emp,dept) file=c:\scott.dmp ignore=y

匯入方案

1) 匯入自己的方案

imp userid=scott/scott file=d:\xxx.dmp;

2) 匯入其他方案

imp userid system/aaaaaaaa file=d:\xxx.dmp fromuser=system touser=scott;

3) 匯入資料庫

imp userid system/aaaaaaaa  full=y  file=c:\database.dmp; 

Java基礎——Oracle（五）