Oracle數(shù)據(jù)庫入門

一、Oracle

1、oracle服務(wù)

1)OracleDBConsoleorcl

Oracle數(shù)據(jù)庫控制臺服務(wù)，orcl是Oracle的實(shí)例標(biāo)識，默認(rèn)的實(shí)例為orcl。

2)OracleOraDb11g_home1TNSListener

監(jiān)聽器服務(wù)，服務(wù)只有在數(shù)據(jù)庫需要遠(yuǎn)程訪問的時候才需要。

3)OracleServiceORCL

數(shù)據(jù)庫服務(wù)(數(shù)據(jù)庫實(shí)例)，是Oracle核心服務(wù)，該服務(wù)是數(shù)據(jù)庫啟動的基礎(chǔ)，只有該服務(wù)啟動，Oracle數(shù)據(jù)庫才能正常啟動。

啟動順序：OracleOracle_homeTNSListener,OracleServiceORCL,OracleDBConsoleORCL

2、sql*plus

1)常用命令

help[?|topic]

？模糊查詢/topic 具體命令

desc[ribe] 查詢指定對象的數(shù)據(jù)結(jié)構(gòu)

如：desc scott.emp

show option 顯示SQL*Plus系統(tǒng)變量的值或環(huán)境變量的值

如：show user

用戶連接命令：

a、conn[ect] conn 用戶名/密碼@網(wǎng)絡(luò)服務(wù)名 [as sysdba/sysoper]

b、disc[onnect] 斷開與當(dāng)前數(shù)據(jù)庫的連接

c、passw[ord] 修改用戶的密碼

d、exit 斷開與數(shù)據(jù)庫的連接，同時會退出sql*plus

查看系統(tǒng)所有用戶：

select username,account_status from dba_users;

查詢用戶下所有的表：

select table_name from tabs;

select table_name from user_tables;

select tname from tab;

select?*?from?dba_tables?where?owner=‘SCOTT';

2)用戶管理指令

忘記管理員密碼：

sqlplus/nolog

connect/as sysdba

alter user sys identified by sys

創(chuàng)建新用戶：具有DBA權(quán)限的用戶執(zhí)行

create user test  identified by 123456

default  tablespace users

temporary tablespace temp

quota 3m on users;

//用戶角色

connect Role(連接角色)：臨時用戶，特別是那些不需要建表的用戶

resource Role(資源角色)：

提供給用戶另外的權(quán)限以創(chuàng)建他們自己的表、序列、過程、觸發(fā)器、索引和簇。只可以創(chuàng)建實(shí)體，不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)

dba Role(數(shù)據(jù)庫管理員角色)：擁有所有的系統(tǒng)權(quán)限，只有DBA才可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)

用戶授權(quán)：

grant sys_privi | role to user | role | public [with admin option]

sys_privi：表示Oracle系統(tǒng)權(quán)限，系統(tǒng)權(quán)限是一組約定的保留字。

role：角色。

user：具體的用戶名，或者是一些列的用戶名。

public：保留字，代表Oracle系統(tǒng)的所有用戶。

with admin option：表示可再將權(quán)限授予另外的用戶。

如：grant  connet,resource to test;為用戶test授予連接和開發(fā)系統(tǒng)權(quán)限

收回權(quán)限：

revoke sys_privi | role from user | role | public；

如：revoke resource from test;

對象權(quán)限：

grant obj_privi | all column on schema.object to user | role | public [with grant option] | [with hierarchy option]

obj_privi：表示對象的權(quán)限，可以是alter、execute、select、update和insert等

role：角色名

user：被授權(quán)的用戶名

with admin option：表示被授權(quán)者可再將系統(tǒng)權(quán)限授予其他的用戶

with hierarchy option：在對象的子對象（在視圖上再建立視圖）上授權(quán)給用戶

如：grant select,insert,delete,update on scott.emp to test; 給用戶test授予select、insert表soctt.emp的權(quán)限

收回對象權(quán)限

revoke obj_privi | all on schema.object from user | role | public cascade constraints

cascade constraints：表示有關(guān)聯(lián)關(guān)系權(quán)限也被撤銷

如：revoke delete,update on scott.emp from test; 從test用戶撤銷scott.emp表的update和delete

修改用戶：就是創(chuàng)建新用戶把create關(guān)鍵字替換成alter

解鎖被鎖住的用戶：

alter user test account unlock;

刪除用戶：

drop user 用戶名 [cascade]

cascade：級聯(lián)刪除選項(xiàng)，連同該用戶所擁有的對象一起刪除

3)常用數(shù)據(jù)類型

a、字符類型

char(n byte/char) 默認(rèn)1字節(jié)n最大2000 末尾填充空格以達(dá)到指定長度

varchar2(n byte/char) 最大長度必須指定最大4000 超過指定長度則報錯

b、數(shù)字類型

number(p[,s]) 1-22字節(jié)

binary_float 32位單精度浮點(diǎn)型

binary_double 64位雙精度浮點(diǎn)型

c、時間類型

date 沒有分秒和時間區(qū)

timestamp 沒有時間區(qū)

timestamp with local time zone 使用數(shù)據(jù)庫時區(qū)

d、大對象類型

blob 二進(jìn)制文件，支持事務(wù)處理

clob 字節(jié)字符數(shù)據(jù)，支持事務(wù)處理

nclob unicode數(shù)據(jù)，支持事務(wù)處理

bfile lob地址指向文件系統(tǒng)上的二進(jìn)制文件，維護(hù)目錄和文件名，不參與事務(wù)，支持只讀

4)sequence序列————主要用于提供主鍵值

a、創(chuàng)建序列需要的權(quán)限：

create sequence 或 create any sequence

b、語法：

CREATE SEQUENCE sequence  //創(chuàng)建序列名稱

[INCREMENT BY n]   //遞增的序列值是n 如果n是正數(shù)就遞增,如果是負(fù)數(shù)就遞減 默認(rèn)是1

[START WITH n]     //開始的值,遞增默認(rèn)是minvalue 遞減是maxvalue

[{MAXVALUE n | NOMAXVALUE}] //最大值

[{MINVALUE n | NOMINVALUE}] //最小值

[{CYCLE | NOCYCLE}] //循環(huán)/不循環(huán)

[{CACHE n | NOCACHE}]; //分配并存入到內(nèi)存中

如：

create sequence seq_test

increment by 1    --每次加1個

start with 1      --從1開始計(jì)數(shù)

nomaxvalue        --不設(shè)置最大值

minvalue 1        --最小值

nocycle           --一直累加，不循環(huán)

nocache;          --不建緩沖區(qū)

c、序列使用

currval: 返回sequence的當(dāng)前值

nextval: 返回sequence的下一個值

d、注意事項(xiàng)

currval總是返回當(dāng)前sequence的值，只有在第一次nextval初始化后，才能使用currval，否則會出錯

第一次nextval返回的值是初始值：隨后的nextval會自動增加定義的increment by值，然后返回增加后的值

使用cache或許會跳號，比如數(shù)據(jù)庫突然不正常down掉，cache中的sequence就會丟失，可以在定義sequence的時候，使用nocache防止這種情況

e、使用觸發(fā)器實(shí)現(xiàn)自增長

create or replace

trigger trg_customers before insert on test for each row 

begin 

select seq_test.nextval into :new.id from dual; 

end; 

5)分頁

a、偽列

rowid行標(biāo)示符和rownum行號 看不到這兩個列，只在數(shù)據(jù)庫內(nèi)部使用

b、三段式分頁

select * from (

select rownum rn, t.* from (

select * from t_users

) t where rownum<=頁碼值*每頁行數(shù)

) where rn>(頁碼-1)*每頁行數(shù)

6)常用函數(shù)

concat(X,Y)：連接字符串X和Y

length(X)：返回X的長度

substr(X,start[,length])返回X的子字串，從start處開始，截取length個字符，缺省length，默認(rèn)到結(jié)尾

ltrim(X[,TRIM_STR])：把X的左邊截去trim_str字符串，缺省截去空格

rtim(X[,TRIM_STR]) 把X的右邊截去trim_str字符串

abs(X) X的絕對值

ceil(X) 大于或等于X的最小整數(shù)值

floor(X) 小于或等于X的最大整數(shù)值

round(X[,Y]) X在第Y位四舍五入

power(X,Y) X的Y次冪

add_months(d,n)，在某一個日期 d 上，加上指定的月數(shù) n，返回計(jì)算后的新日期。d 表示日期，n 表示加的月數(shù)。

last_day(d)，返回指定日期當(dāng)月的最后一天。

extract(fmt FROM d)，提取日期中的特定部分。fmt 為：YEAR、DAY等。其中 YEAR、MONTH、DAY可以為 DATE 類型匹配，也可以與 TIMESTAMP 類型匹配；但是 HOUR、MINUTE、SECOND 必須與 TIMESTAMP 類型匹配。

to_char(d|n[,fmt])：把日期和數(shù)字轉(zhuǎn)換為制定格式的字符串。Fmt是格式串

to_date(X,[,fmt]) 把一個字符串以fmt格式轉(zhuǎn)換成一個日期類型

to_number(X,[,fmt])把一個字符串以fmt格式轉(zhuǎn)換為一個數(shù)字

nvl(X,VALUE)：如果X為空，返回value，否則返回X  

nvl2(x,value1,value2)如果x非空，返回value1，否則返回value2

3、jdbc連接數(shù)據(jù)庫————同mysql

驅(qū)動類：ojdbc6.jar

類名：oracle.jdbc.driver.OracleDriver

url：jdbc:oracle:thin:@192.168.0.1:1521:orcl

user：scott

password：a1s2d3f4