柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫 Oracle介紹

http://yzkb.51969.com/

1、什么是Oracle Oracle數(shù)據(jù)庫是美國(guó)ORACLE公(甲骨文)提供的以分布式數(shù)據(jù)庫為核心的一組軟件產(chǎn)品，是目前最流行的 客戶/服務(wù)器(CLIENT/SERVER)或B/S體系結(jié)構(gòu)的數(shù)據(jù)庫之一。 2、Oracle的特點(diǎn) (1)支持多用戶、大事務(wù)量的事務(wù)處理 (2)數(shù)據(jù)安全性和完整性控制 (3)支持分布式數(shù)據(jù)處理 (4)可移植性 3、Oracle體系結(jié)構(gòu)介紹【重要】 數(shù)據(jù)庫 Oracle數(shù)據(jù)庫是數(shù)據(jù)的物理存儲(chǔ)。這就包括(數(shù)據(jù)文件 ORA 或者 DBF、控制文件、聯(lián)機(jī)日志、參數(shù) 文件)。其實(shí) Oracle 數(shù)據(jù)庫的概念和其它數(shù)據(jù)庫(SQLSERVER、MYSQL)不一樣，這里的數(shù)據(jù)庫是一 個(gè)操作系統(tǒng)只有一個(gè)庫?？梢钥醋魇荗racle 就只有一個(gè)大數(shù)據(jù)庫。 實(shí)例 一個(gè)oracle實(shí)例有一系列的后臺(tái)進(jìn)程和內(nèi)存結(jié)構(gòu)組成，一個(gè)數(shù)據(jù)庫可以有n個(gè)實(shí)例。實(shí)例之間互不 干擾。 數(shù)據(jù)文件（.DBF） 數(shù)據(jù)文件是數(shù)據(jù)庫的物理存儲(chǔ)單位。數(shù)據(jù)庫的數(shù)據(jù)是存儲(chǔ)在表空間中，真正是在某一個(gè)或多個(gè)數(shù)據(jù) 文件中。而一個(gè)表空間可以由一個(gè)或多個(gè)數(shù)據(jù)文件組成，一個(gè)數(shù)據(jù)文件只能屬于一個(gè)表空間。一旦 數(shù)據(jù)文件被加入到某個(gè)表空間后，就不能刪除這個(gè)文件，如果要?jiǎng)h除某個(gè)數(shù)據(jù)文件，只能刪掉其所 屬于的表空間才行。 表空間 表空間是Oracle對(duì)物理數(shù)據(jù)庫上相關(guān)數(shù)據(jù)文件(ORA 或者 DBF 文件)的邏輯映射。一個(gè)數(shù)據(jù)庫在邏 輯上被劃分成一到若干個(gè)表空間，每個(gè)表空間包含了在邏輯上相關(guān)聯(lián)的一組結(jié)構(gòu)。每個(gè)數(shù)據(jù)庫至少 有一個(gè)表空間(稱之為 system表空間)。 每個(gè)表空間由同磁盤上的一個(gè)或多個(gè)文件組成，這些文件叫數(shù)據(jù)文件(datafile)。一個(gè)數(shù)據(jù)文件只能 屬于一個(gè)表空間。 注：表的數(shù)據(jù)，是由用戶放入某一個(gè)表空間的，而這個(gè)表空間會(huì)隨機(jī)把這些表數(shù)據(jù)放到一個(gè)或者多 個(gè)數(shù)據(jù)文件中。 由于oracle的數(shù)據(jù)庫不是普通的概念，oracle是有用戶和表空間對(duì)數(shù)據(jù)進(jìn)行管理和存放的。但是表 不是有表空間去查詢的，而是由用戶去查的。因?yàn)椴煌脩艨梢栽谕粋€(gè)表空間建立同一? 個(gè)名字的 表!這里區(qū)分就是用戶了! --------簡(jiǎn)單來說Oracle中的表空間類似mysql的database數(shù)據(jù)庫，可以在表空間下創(chuàng)建多個(gè)表

二、Oracle表空間和用戶【重點(diǎn)】 1、oracle表空間 (1)、表空間分類 永久表空間 表空間是數(shù)據(jù)庫的邏輯劃分，一個(gè)表空間只能屬于一個(gè)數(shù)據(jù)庫。所有的數(shù)據(jù)庫對(duì)象都存放在指定的表空 間中。但主要存放的是表，所以稱作表空間 臨時(shí)表空間 oracle臨時(shí)表空間主要用來做查詢和存放一些緩沖區(qū)數(shù)據(jù)。臨時(shí)表空間消耗的主要原因是需要對(duì)查詢的 中間結(jié)果進(jìn)行排序。重啟數(shù)據(jù)庫可以釋放臨時(shí)表空間 (2)、表空間操作 查看所有表空間 創(chuàng)建表空間 select * from sys.dba_tablespaces;

?

創(chuàng)建表空間 select * from sys.dba_tablespaces; create tablespace 永久表空間名稱 datafile ‘永久表空間物理文件位置【數(shù)據(jù)文件】.DBF’ size 100M -- size設(shè)置數(shù)據(jù)文件的大小 autoextend on next 10M -- autoextend 設(shè)置數(shù)據(jù)文件是否自動(dòng)擴(kuò)容，on是開啟【開啟后可以通過 “next xx”設(shè)置擴(kuò)容大小】，off是關(guān)閉 maxsize 200M -- maxsize設(shè)置上限大小和autoextend配合使用，不設(shè)置就是無限 [permanent] -- 設(shè)置永久表空間，設(shè)置了就是永久表空間 [online]; -- 聯(lián)機(jī)狀態(tài) ，以便數(shù)據(jù)庫用戶訪問其中的數(shù)據(jù)。但是數(shù)據(jù)庫管理員可以在對(duì)表空間進(jìn)行維護(hù)， 備份或恢復(fù)操作時(shí)，令表空間脫機(jī)(offline)

?

?

表空間重命名

alter tablespace 原表空間名稱 rename to 新表空間名稱;

增加數(shù)據(jù)文件

ps：一個(gè)表空間可以有多個(gè)數(shù)據(jù)文件的，每個(gè)是數(shù)據(jù)文件屬于一個(gè)表空間，數(shù)據(jù)文件就是在磁盤上存儲(chǔ)數(shù)據(jù)的媒介?

alter tablespace 表空間名稱 add datafile ‘?dāng)?shù)據(jù)文件路徑’ size 大小 autoextend on next 大小 maxsize 大小

修改表空間的數(shù)據(jù)文件 PS1: 想要修改數(shù)據(jù)文件需要先將表空間改為離線狀態(tài)“offline”，否則無法生效，因?yàn)閛nline狀態(tài)下，表空間在使用數(shù)據(jù)文件，有這個(gè)讀寫保護(hù)的 PS2: 修改成的文件需要存在?

alter tablespace 表空間名 rename datafile ‘原來數(shù)據(jù)文件的路徑’ to ‘新的數(shù)據(jù)文件路徑’;

修改數(shù)據(jù)文件大小

alter database datafile ‘?dāng)?shù)據(jù)文件路徑’ resize 大小;

修改數(shù)據(jù)文件為自動(dòng)增長(zhǎng)

alter database datafile '數(shù)據(jù)文件路徑' autoextend on next 大小 maxsize 大小;?

?

?

修改表空間狀態(tài) 刪除表空間 ? 演示案例：移動(dòng)數(shù)據(jù)文件 ps：在實(shí)際的開發(fā)中可能會(huì)遇到磁盤損壞，需要移動(dòng)表空間文件移至安全路徑 alter database datafile ‘?dāng)?shù)據(jù)文件路徑’ resize 大小; alter database datafile '數(shù)據(jù)文件路徑' autoextend on next 大小 maxsize 大小; 1、聯(lián)機(jī) alter tablespace 表空間名 online； 2、脫機(jī) alter tablespace 表空間名 offline； 3、修改為只讀表空間 alter tablespace 表空間名 read only; 4、查看表空間狀態(tài) select * from DBA_TABLESPACES where TABLESPACE_NAME=’表空間名’; drop tablespace 表空間名; 第一步：使用管理員用戶操作，查看數(shù)據(jù)文件所在的表空間 select tablespace_name from dba_data_files where file_name=upper(‘?dāng)?shù)據(jù)文件路徑’); 第二步：使表空間進(jìn)行脫機(jī) alter tablespace 表空間名 offline； 第三步：移動(dòng)數(shù)據(jù)文件至指定的目錄【如果使用linux就用mv指令，window可以用鼠標(biāo)】 第四步：修改數(shù)據(jù)文件的位置 alter tablespace 表空間名 rename datafile ‘原來數(shù)據(jù)文件的路徑’ to ‘新的數(shù)據(jù)文件路徑’; 第五步：使用表空間聯(lián)機(jī) alter tablespace 表空間名稱 online;

-- 查看全部表空間 select * from sys.dba_tablespaces; ? ? ? 2、Oracle用戶--DCL【重點(diǎn)】 oracle用戶和表空間掛鉤的，每一個(gè)表空間下創(chuàng)建多個(gè)表，用戶起到分隔數(shù)據(jù)的作用，是oracle權(quán)限的 重要組成 創(chuàng)建用戶 -- 創(chuàng)建表空間---類似MySQL中創(chuàng)建數(shù)據(jù)庫 create tablespace mytablespace datafile '/usr/local/dba/testdbf.DBF' ?-- LINUX服 務(wù)器沒有磁盤的直接是目錄路徑 size 100M -- size設(shè)置數(shù)據(jù)文件的大小 autoextend on next 10M -- autoextend 設(shè)置數(shù)據(jù)文件是否自動(dòng)擴(kuò)容，on是開啟【開啟后可以通 過“next xx”設(shè)置擴(kuò)容大小】，off是關(guān)閉 maxsize 200M -- maxsize設(shè)置上限大小和autoextend配合使用，不設(shè)置就是無限 permanent -- 設(shè)置永久表空間，設(shè)置了就是永久表空間 online; -- 聯(lián)機(jī)狀態(tài) ，以便數(shù)據(jù)庫用戶訪問其中的數(shù)據(jù)。但是數(shù)據(jù)庫管理員可以在對(duì)表空間進(jìn)行維護(hù)，備 份或恢復(fù)操作時(shí)，令表空間脫機(jī)(offline) -- 修改表空間名字 alter tablespace mytablespace rename to newtablespace; --- 追加數(shù)據(jù)文件到表空間 alter tablespace newtablespace add datafile ?'/usr/local/dba/testdbf_02.DBF' size 100M -- 文件多大 autoextend on next 10M -- 每次擴(kuò)容多少 maxsize 200M; -- 最大上限多少 -- 修改數(shù)據(jù)文件設(shè)置 alter database datafile '/usr/local/dba/testdbf_02.DBF' resize 500M; ??-- 大小500 -- 查看某個(gè)表空間狀態(tài) select * from sys.dba_tablespaces ?where TABLESPACE_NAME='NEWTABLESPACE'; -- 更改表空間狀態(tài)位offline---離線狀態(tài) alter tablespace NEWTABLESPACE offline; -- 更改表空間狀態(tài)位online--聯(lián)機(jī)狀態(tài) alter tablespace NEWTABLESPACE online; create user 用戶名 identified by "密碼" default tablespace 表空間名;?

修改用戶密碼

1、傳統(tǒng)sql語法（11g 可以使用）：alter user 用戶名 identified by 新密碼; 2、專用語法：password 用戶名

?刪除用戶

drop user 用戶名 cascade; --- cascades刪除時(shí)刪除該用戶所有表

完整演示

問題：用創(chuàng)建的用戶登錄發(fā)現(xiàn)無法登錄，原因是當(dāng)前用戶創(chuàng)建了卻沒有賦予權(quán)限，無權(quán)訪問

?

3、權(quán)限管理--DCL【重點(diǎn)】 權(quán)限管理是Oracle實(shí)現(xiàn)安全管理的一部分。通過授予不同用戶的系統(tǒng)權(quán)限與對(duì)象權(quán)限，可以控制用戶對(duì) 系統(tǒng)功能的數(shù)據(jù)庫對(duì)象的操作 oracle權(quán)限可分為三種：系統(tǒng)權(quán)限，對(duì)象權(quán)限，角色權(quán)限 （1）、系統(tǒng)權(quán)限： 是指在系統(tǒng)級(jí)控制數(shù)據(jù)庫的存取和使用的機(jī)制，即執(zhí)行某種SQL語句的能力。例如啟動(dòng)/停止數(shù)據(jù)庫，修 改數(shù)據(jù)庫參數(shù)，連接數(shù)據(jù)庫等權(quán)限。-----系統(tǒng)權(quán)限是針對(duì)于用戶設(shè)置的，用戶必須被授予相應(yīng)的系統(tǒng)權(quán) 限，才能連接到數(shù)據(jù)庫中進(jìn)行想要的操作。 ps: 系統(tǒng)權(quán)限很多，此處說一下常見的權(quán)限，其余的自行百度 CREATE SESSION —————————— 連接數(shù)據(jù)庫 CREATE TABLESPACE —————————— 創(chuàng)建表空間 ALTER TABLESPACE —————————— 修改表空間 DROP TABLESPACE —————————— ??刪除表空間 CREATE USER —————————— ??創(chuàng)建用戶 DROP USER —————————— 修改用戶 CREATE TABLE —————————— ??創(chuàng)建表 語句： grant 系統(tǒng)權(quán)限1，系統(tǒng)全選2 ... to 用戶 --- 賦予 revoke 系統(tǒng)權(quán)限1，系統(tǒng)全選2 ... from 用戶 --- 收回

2）、對(duì)象權(quán)限： 是指用戶對(duì)數(shù)據(jù)庫對(duì)象的操作權(quán)限。數(shù)據(jù)庫對(duì)象包括表，視圖，序列和存儲(chǔ)過程等。Oracle的對(duì)象權(quán)限 包括ALTER【更改】，DELETE【刪除】，EXECUTE【執(zhí)行】，INDEX【索引】，INSERT【添加】， SELECT【查詢】和UPDATE【刷新】。這些對(duì)象權(quán)限適用于不同的對(duì)象。 --- 表權(quán)限【用于賦予對(duì)其他用戶【不同表空間下的】表操作的權(quán)限】 grant 權(quán)限1，權(quán)限2 on 用戶B.表名 to 用戶A；--- 賦予用戶A戶用于對(duì)于用戶Bxxx表的xxx權(quán)限 revoke 權(quán)限1,權(quán)限2 on 用戶B.表名 from 用戶A; --- 收回用戶A用于對(duì)于用戶Bxxx表的xxx權(quán)限

--- 表空間權(quán)限： 新建的用戶針對(duì)自己所屬表空間的對(duì)象默認(rèn)是無權(quán)操作的，想要操作就要使用下列指令賦 予其對(duì)于自己所屬表空間對(duì)象的操作權(quán)限 grant unlimited tablespace to 用戶名; --- 給用戶授予全部的表空間的權(quán)限 revoke unlimited tablespace from 用戶名; --- 收回戶授予全部的表空間的權(quán)限

（3）、角色權(quán)限：

角色是可以授予用戶的相關(guān)權(quán)限的組，該方法使權(quán)限的授予，撤回更加容易管理

常用角色【內(nèi)置角色】： connect ----- 登陸權(quán)限 resource ----- 開發(fā)人員權(quán)限 dba ----- 管理員權(quán)限[全部權(quán)限] 語法： grant 角色名稱1,角色名稱2 to 用戶;

（4）、權(quán)限傳遞

ps：這個(gè)用于給其他賬戶賦權(quán)的，只有允許的傳遞的權(quán)限才可以賦予其他賬戶，只有系統(tǒng)權(quán)限和對(duì)象權(quán)限可以設(shè)置權(quán)限傳遞

權(quán)限傳遞有兩種方式with admin option 和 with grant option，這兩種方式的特點(diǎn)是 with admin option: 用戶A可以將擁有權(quán)限賦予用戶B，如果用戶A的該權(quán)限被收回了，用戶B是不會(huì)受到 影響，該方式用于系統(tǒng)權(quán)限 with grant option：用戶A可以將擁有權(quán)限賦予用戶B，如果用戶A的該權(quán)限被收回了，用戶B也會(huì)被收回， 該方式用于對(duì)象權(quán)限 with admin option: ps：用戶A可以將擁有權(quán)限賦予用戶B，如果用戶A的該權(quán)限被收回了，用戶B是不會(huì)受到影響，該方式 用于系統(tǒng)權(quán)限 with grant option： ps：用戶A可以將擁有權(quán)限賦予用戶B，如果用戶A的該權(quán)限被收回了，用戶B也會(huì)被收回，該方式用于 對(duì)象權(quán)限 （5）、角色定義 角色在oracle中是一組權(quán)限的結(jié)合體，角色中可以包含很多權(quán)限。當(dāng)把角色賦予用戶時(shí)，用戶就可以具 有角色內(nèi)的所有權(quán)限。Oracle中角色分為兩類 <1> 系統(tǒng)預(yù)定義角色 <2> 管理員自建角色 1、新建兩個(gè)用戶 create user tim1 identified by "tim1" default tablespace test_ts; create user tim2 identified by "tim2" default tablespace test_ts; 2、system用戶賦予權(quán)限create session 給tim1用戶 grant create session to tim1 with admin option; 3、切換用戶至tim1，然后tim1用戶賦予權(quán)限create session 給tim2用戶； grant create session to tim2 ; 4、此時(shí)tim1和tim2都可以登錄，然后system用戶回收tim1的create session權(quán)限，tim1肯定不能登 錄，但是tim2還是可以登錄的。 revoke create session from tim1; 1、scott用戶賦予select權(quán)限給tim1 grant select on scott.emp to tim1 with grant option; 2、切換用戶至tim1，然后tim1用戶賦予權(quán)限給tim2用戶； grant select on scott.emp to tim2; 3、scott用戶回收tim1用戶的select權(quán)限，tim1沒有對(duì)scott.emp查詢的權(quán)限,tim2用戶也會(huì)消失掉對(duì) scott.emp表的查詢權(quán)限 revoke select on scott.emp from tim1; connect ----- 登陸權(quán)限 resource ----- 開發(fā)人員權(quán)限 dba ----- 管理員權(quán)限 1、創(chuàng)建角色語法：create role 角色名; 2、給角色授權(quán)：grant 權(quán)限 to 角色名; ps：角色與角色之間也可以互相授權(quán)。例如：grant resource,dba to r1 3、把權(quán)限從角色中移除：revoke 權(quán)限 from 角色名； 4、刪除角色：drop role 角色名;

（6）、profile口令文件【了解】 ps：對(duì)賬戶進(jìn)行高級(jí)管理【有效期，密碼重復(fù)次數(shù)...】 <1>、賬戶鎖定 <2>、賦予用戶口令 create profile 口令名稱 limit 參數(shù) 下面這些limit的可選參數(shù)： password_life_time (天/unlimited[無限]) ---- 有效天數(shù) password_grace_time (天) --- 寬限天數(shù) failed_login_attempts 次數(shù) --- 輸入錯(cuò)誤多少次 password_lock_time (天); --- 鎖定幾天 .... alter user 用戶名 profile 口令;

?

<3>、用戶解鎖

alter user 用戶名 account unlock;

<4>、用戶鎖定

alter user 用戶名 account lock; --- 永久鎖定

<5>、刪除口令

drop profile 口令名稱 cascade; <6>、修改口令

alter profile 口令名 limit 口令限制代碼；

三、數(shù)據(jù)類型

oracle中數(shù)據(jù)類型分三類：數(shù)字型、字符型、日期型

1、數(shù)字類型

(1). number：長(zhǎng)度不限，只要是數(shù)字就行 (2). number(x)：為整數(shù)，并且長(zhǎng)度不超過x位 (3). number(x,y)：存在小數(shù)部分，總長(zhǎng)度不超過x位，小數(shù)部分為y位 ps:也可以使用int和decimal之類的，number相當(dāng)于int+double整合版本

2、字符型

(1). varchar2(x):長(zhǎng)度不超過x位【oracle推薦】 (2). varchar(x):長(zhǎng)度不超過x位 (3). char(x):固定長(zhǎng)度是x位，不夠則補(bǔ)空格 ps： 1、char是固定長(zhǎng)度，如果內(nèi)容無法填充滿，會(huì)將剩余的用空格補(bǔ)充；varchar2/varchar都是可變長(zhǎng)度，設(shè) 置只是一個(gè)最大長(zhǎng)度，如果沒有填充滿也不會(huì)補(bǔ)空格 2、varchar是標(biāo)準(zhǔn)sql里面的；varchar2是oracle提供的獨(dú)有的數(shù)據(jù)類型。 3、varchar對(duì)于漢字占兩個(gè)字節(jié)，對(duì)于數(shù)字，英文字符是一個(gè)字節(jié)，占的內(nèi)存?。籿archar2一般情況下把所 有字符都占兩字節(jié)處理。 具體要看數(shù)據(jù)庫使用的字符集，比如GBK，漢字就會(huì)占兩個(gè)字節(jié)，英文1個(gè)，如果是UTF-8，漢字一般占3個(gè)字 節(jié)，英文還是1個(gè)。 4、varchar對(duì)空串不處理；varchar2將空串當(dāng)做null來處理。 5、varchar存放固定長(zhǎng)度的字符串，最大長(zhǎng)度是2000；varchar2是存放可變長(zhǎng)度的字符串，最大長(zhǎng)度是 4000。

3、日期型

(1)、date:只需要日期 ，占用8字節(jié)，yyyy-MM-dd (2)、timestamp：需要日期+時(shí)間，占用12字節(jié)

四、自增主鍵

ps：oracle和mysql不同，不能使用auto_increment 設(shè)置主鍵，需要先創(chuàng)建一個(gè)自增序列【序列就是一個(gè)對(duì)象】

-- 1.先創(chuàng)建一個(gè)自增序列對(duì)象 create sequence 序列名【自定義】 屬性 ; 可選屬性： increment by 1 -- 每次自增1，寫負(fù)數(shù)就是自動(dòng)減少 start with 1 -- 開始值 nomaxvalue -- 沒有最大值 cache 20 -- 設(shè)置取值緩存數(shù)，先緩存一些數(shù)出來，效率快 end with 65535 -- 結(jié)束值 ，一般不用設(shè)置上限---和nomaxvalue二選一 nominvalue -- 沒有最小值，一般用于自減的時(shí)候

-- Oracle 12 c 可以利用默認(rèn)約束+自增序列實(shí)現(xiàn)主鍵自增 -- 自增序列.nextval:獲取下一個(gè)值 create table tab02( ??tid number(11) default 自增序列.nextval, ??price number(11,2), ??address varchar2(111), ??addtime date, ??primary key(tid) );

?舊版本--11g【通用寫法】 create table tab01( ??tid number(11), ??price number(11,2), ??address varchar2(111), ??addtime date, ??primary key(tid) ); -- 在插入時(shí)，使用自增序列 insert into tab01 values(my_auto_increment.nextval,20.50,'濟(jì)南',to_date('2023-01- 03','yyyy-MM-dd'));

五、DML語句 Oracle自帶事務(wù)，執(zhí)行DML語句后需要手動(dòng)commit【提交】/rollback【回滾】 1、添加語句

基礎(chǔ)版： insert into 表名(字段1,字段2,字段3) values(值1,值2,值3...); --- 如果表后寫字段，values 值對(duì)應(yīng)表后聲明字段；如果沒寫，就對(duì)應(yīng)表字段---和mysql的規(guī)則一樣 ps: 1、一次只能插入一行 2、時(shí)間類型不能直接寫字符串，需要用函數(shù)to_date轉(zhuǎn)換 to_date(時(shí)間,格式)： 格式有兩種對(duì)應(yīng)兩種不同時(shí)間類型， 字段為date類型時(shí)”yyyy-MM-dd”。字段為 timestamp類型時(shí)“yyyy-MM-dd hh24:mi:ss”

2、修改語句

update 表 set 字段=值，字段=值 ... where 條件

3、刪除語句

delete from 表 where 條件

where條件

1、算數(shù)運(yùn)算： + ， - ，* ， / 2、比較運(yùn)算：=，!= ，< , > ,>=,<= 3、介于之間：between A and B 4、包含不包含： in ， not in 5、非空不非空：is null ，is not null 6、關(guān)系運(yùn)算： and ，or 7、模糊查詢： like 8、子查詢的全部/任意： any ， all 9、字段類型轉(zhuǎn)換： ps：有一些類型需要進(jìn)轉(zhuǎn)換才可以使用，比如：時(shí)間和字符串的互轉(zhuǎn) --- to_date (字符串時(shí)間，字符串格式) 字段為date類型時(shí)”yyyy-MM-dd”。字段為timestamp類型時(shí)“yyyy-MM-dd hh24:mi:ss” --- to_char（其他類型,字符串格式） 可以將其他類型數(shù)據(jù)轉(zhuǎn)為字符串類型的數(shù)據(jù) --- to_number(字符串類型數(shù)字,數(shù)字的格式) 可以將字符串?dāng)?shù)字格式化為純數(shù)字

六、DQL語句

1、基本結(jié)構(gòu)

select 字段 from 表 where 分組前條件 group by 分組條件 having 分組后條件 order by 排序 條件; --- 和mysql相比沒有l(wèi)imit分頁

2、Oracle偽列【獨(dú)有,重點(diǎn)】 Oracle表的使用過程中，實(shí)際上表中除了我們?cè)O(shè)置的字段外，還有一些附加列，稱為偽列；偽列可以和 正常字段一樣被查詢使用【不能增刪改】，但是不存在于表中。

(1)、rownum（重點(diǎn)） 解釋：rownum會(huì)在你的查詢結(jié)果集中，為每一行標(biāo)識(shí)一個(gè)行號(hào)【從1開始】，可以利用rownum查詢行 數(shù) 注意：如果篩選條件中用rownum，那么只能用 < 或 <= 來查詢，其余的都查詢不到 作用：可以用于oracle分頁查詢

問題：如果后6行？rownum不能用除了

分頁實(shí)現(xiàn)【重點(diǎn)】：

ps：oracle沒有l(wèi)imit，想分頁須使用rownum

(2)、rowid

解釋：表中每一行數(shù)據(jù)再數(shù)據(jù)文件中有一個(gè)物理地址，rowid偽列返回的就是該物理地址

作用：

數(shù)據(jù)包準(zhǔn)備： -- 定義表people create table peopletab( pname varchar(10), pmoney number(11,2), pcity varchar(10) ); -- 插入數(shù)據(jù) insert into peopletab values('zhangsan',2500.00,'jinan'); insert into peopletab values('zhangsan',2500.00,'jinan'); insert into peopletab values('lisi',3000.00,'jinan'); insert into peopletab values('zhangsan',2500.00,'qingdao'); -- 提交 commit;

3、連接查詢

(1)、傳統(tǒng)sql寫法

<1>、內(nèi)連接/自然連接：將左右兩個(gè)表中相關(guān)的數(shù)據(jù)查詢出來 select 字段 from 表A inner join【或者簡(jiǎn)寫為jion】 表B on 連表?xiàng)l件 <2>、外連接： 1、左外連接：左邊表為驅(qū)動(dòng)表，會(huì)查詢出全部數(shù)據(jù)，右邊是被驅(qū)動(dòng)表，只出相對(duì)應(yīng)的數(shù)據(jù) select 字段 from 表A left outer join【left jion】 表B on 連表?xiàng)l件 2、右外連接：右邊表為驅(qū)動(dòng)表，會(huì)查詢出全部數(shù)據(jù)，做邊是被驅(qū)動(dòng)表，只出相對(duì)應(yīng)的數(shù)據(jù) select 字段 from 表A right outer join【right jion】 表B on 連表?xiàng)l件 3、全外連接：左右連邊表都是全部出來，就沒有驅(qū)動(dòng)被驅(qū)動(dòng)了 select 字段 from 表A full outer join【full jion】 表B on 連表?xiàng)l件

(2)、笛卡爾積寫法

<1>、內(nèi)連接/自然連接的笛卡爾積寫法： ps：普通連接查詢就是自然連接查詢的笛卡爾積寫法，這倆是一個(gè)東西，因此性能一樣的 select 字段 from 表1,表2... where 連表?xiàng)l件 and 其他條件 <2>、外連接笛卡爾積寫法： 1、左外連接笛卡爾積寫法 ps： 其中這個(gè)(+) 就相當(dāng)于告訴數(shù)據(jù)庫以xxx表為被驅(qū)動(dòng)表，另外一邊全出 select 字段 from 表1,表2... where 表1的關(guān)系字段 = 表2的關(guān)系字段(+) and 其他條件 2、右外連接笛卡爾積寫法 select 字段 from 表1,表2... where 表1的關(guān)系字段(+) = 表2的關(guān)系字段 and 其他條件

七、函數(shù)

1、數(shù)學(xué)函數(shù)

(1)、nvl(目標(biāo)字段，默認(rèn)數(shù)字【number類型】)---判斷字段是否為空，如果不為空返回字段值，為空返回 默認(rèn)值 (2)、abs(數(shù)字) --- 絕對(duì)值 (3)、mod(被除數(shù)，除數(shù)) --- 模運(yùn)算，取余數(shù) (4)、power(x，y) --- 冪函數(shù)，返回x的y次方 (5)、ceil(x) --- 向上取整，ceil(3.3) = 4 (6)、floor(x) --- 向下取整 (7)、round(x,y) --- 四舍五入，x被四舍五入的值，y保留幾位小數(shù)

2、字符函數(shù)

(1)、add_months(d【時(shí)間類型，手寫日期需要to_date轉(zhuǎn)換】,n) --- 返回d這個(gè)日期完后n個(gè)月的日期

(2)、last_day(d) --- 返回d日期所屬月份的最后一天

(3)、months_between(d1,d2) --- 返回兩個(gè)日期的月份差

（4）、round(d,fmt) --- 對(duì)日期d根據(jù)不同維度進(jìn)行四舍五入 fmt的格式有：yyyy/MM/dd/day[星期]/q[季度]/w[當(dāng)月第幾周]/iw[當(dāng)年第幾周]

3、聚合函數(shù)

(1)、sum ---- 求和 (2)、min 和 max --- 最值 (3)、avg --- 平均值 (4)、count --- 計(jì)數(shù)

4、分析函數(shù)

(1)、聚合函數(shù)分析函數(shù)

聚合函數(shù)（字段）over([partition by 字段][order by 字段]) --- 可以對(duì)聚合函數(shù)進(jìn)行分析 --- partition by 字段 ：以xxx分組，是對(duì)聚合函數(shù)的結(jié)果進(jìn)行分組 --- order by 字段 ： 排序

(2)、排名類分析

row_number()over([partition by 字段] order by 字段 ) --- 對(duì)每行數(shù)據(jù)進(jìn)行一個(gè)排名， row_number()類似于偽列rownum

rank()over([partition by 字段] order by 字段 ) ---對(duì)每行數(shù)據(jù)進(jìn)行一個(gè)排名，如果數(shù)據(jù)相同會(huì) 標(biāo)記相同的序號(hào)，后面的序號(hào)會(huì)越過

dense_rank()over([partition by 字段] order by 字段 ) ---對(duì)每行數(shù)據(jù)進(jìn)行一個(gè)排名，如果數(shù) 據(jù)相同會(huì)標(biāo)記相同的序號(hào)，后面的序號(hào)不會(huì)越過

5、行列轉(zhuǎn)換

CREATE TABLE student( id number(20) NOT NULL, name varchar(50), course varchar(50), score number(3) ) INSERT INTO student VALUES (1,'zhangsan', 'yuwen', 95); INSERT INTO student VALUES (2,'lisi', 'yuwen', 99); INSERT INTO student VALUES (3,'wangwu', 'yuwen', 80); INSERT INTO student VALUES (4,'zhangsan', 'shuxue', 86); INSERT INTO student VALUES (5,'lisi', 'shuxue', 96); INSERT INTO student VALUES (6,'wangwu', 'shuxue', 81); INSERT INTO student VALUES (7,'zhangsan', 'yingyu', 78); INSERT INTO student VALUES (8,'lisi', 'yingyu', 88);

INSERT INTO student VALUES (9,'wangwu', 'yingyu', 87); commit

一般顯示方式

行列轉(zhuǎn)換

八、存儲(chǔ)過程語法

-- 定義 create procedur 存儲(chǔ)過程名(參數(shù)列表【參數(shù)名 參數(shù)類型（in，out，inout） 數(shù)據(jù)類型】) is -- 聲明部分 標(biāo)量之類的 begin -- 流程 end; -- 調(diào)用 call 存儲(chǔ)過程名（...）

1、if else

if 條件1 then 操作 ; elsif 條件2 then 操作; ... else 操作; end if;

2 、case

case when 條件1 then 操作; when 條件2 then 操作; ... else 操作; end case;

3、loop循環(huán)

loop -- 循環(huán)體 if 條件 then ??exit; -- mysql結(jié)束符 leave，oracle是exit; end if; end loop;

4、while

while 條件 loop ??-- 循環(huán)體 end loop

5、for

for 循環(huán)變量 in (select 語句) loop -- 循環(huán)體 end loop;

6、游標(biāo)

（1）、隱式游標(biāo)

select 字段 into 變量 ....

（2）、顯示游標(biāo)

create procedure 名... is cursor 游標(biāo)名 is select語句; begin ??for 變量 in 游標(biāo) loop ????-- 循環(huán)體 ??end loop; end;

九、表分區(qū)

當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會(huì)變慢，應(yīng)用程序的性能就會(huì)下降，這時(shí)就應(yīng)該考慮對(duì)表 進(jìn)行分區(qū)。 表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到一個(gè)或多個(gè)表空間， 這樣查詢數(shù)據(jù)時(shí)，不至于每次都掃描整張表。

1、表分區(qū)創(chuàng)建 <1>、范圍分區(qū)【range】 就是根據(jù)數(shù)據(jù)庫表中某一字段的值的范圍來劃分分區(qū)

格式：

CREATE TABLE 表名 (字段 數(shù)據(jù)類型 約束...) -- 這一塊是建表語句 PARTITION BY RANGE (某個(gè)字段) -- range范圍分區(qū) ( ????PARTITION 分區(qū)名1 VALUES LESS THAN【LESS THAN是小于】 (值) [TABLESPACE 表 空間1], ????PARTITION 分區(qū)名2 VALUES LESS THAN (值) [TABLESPACE 表空間2], ????.... ????PARTITION 分區(qū)名n VALUES LESS THAN (MAXVALUE) [TABLESPACE 表空間n] ) ps：表分區(qū)語法mysql和oracle基本一致，但是oracle可以將分區(qū)和tablespace進(jìn)行組合

演示：

（1）、準(zhǔn)備3個(gè)表空間

create tablespace pts01 datafile 'F:/java/oracle/product/11.2.0/tablespaces/pts01.DBF' size 100M autoextend on next 10M maxsize 200M permanent online; create tablespace pts02 datafile 'F:/java/oracle/product/11.2.0/tablespaces/pts02.DBF' size 100M autoextend on next 10M maxsize 200M permanent online; create tablespace pts03 datafile 'F:/java/oracle/product/11.2.0/tablespaces/pts03.DBF' size 100M autoextend on next 10M maxsize 200M permanent online;

（2）、創(chuàng)建表分區(qū)

create table graderecord ( sno varchar2(10), -- 編號(hào) sname varchar2(20),-- 學(xué)生名 dormitory varchar2(3),-- 科目 grade int -- 分?jǐn)?shù) ) partition by range(grade) -- 創(chuàng)建范圍表分區(qū)，以grade字段為范圍條件，分成3分區(qū) ( -- 例如：分區(qū) '不及格' 當(dāng)grade 值小于 60 數(shù)據(jù)存儲(chǔ)到該分區(qū)，該分區(qū)所在表空間為pts01 partition bujige values less than(60) TABLESPACE pts01, --不及格 partition jige values less than(85) TABLESPACE pts02, --及格 -- maxvalue相當(dāng)于條件分支語句的else條件，不滿足上方的都會(huì)進(jìn)來，如果是空值也會(huì)進(jìn)入maxvalue partition youxiu values less than(maxvalue) TABLESPACE pts03 --優(yōu)秀 );

（3）、插入實(shí)驗(yàn)數(shù)據(jù)

insert into graderecord values('511601','魁','229',92); insert into graderecord values('511602','凱','229',62); insert into graderecord values('511603','東','229',26); insert into graderecord values('511604','亮','228',77); insert into graderecord values('511605','敬','228',47); insert into graderecord(sno,sname,dormitory) values('511606','峰','228');-- 缺考 insert into graderecord values('511607','明','240',90); insert into graderecord values('511608','楠','240',100); insert into graderecord values('511609','濤','240',67); insert into graderecord values('511610','博','240',75); insert into graderecord values('511611','錚','240',60); commit;

（4）、查看數(shù)據(jù)

select * from graderecord; -- 查看全部數(shù)據(jù) select * from graderecord partition(bujige); -- 查看bujige分區(qū)數(shù)據(jù) select * from graderecord partition(jige);-- 查看jige分區(qū)數(shù)據(jù) select * from graderecord partition(youxiu);-- 查看youxiu分區(qū)數(shù)據(jù)

<2>、散列分區(qū)【hash】

散列分區(qū)是根據(jù)字段的hash值進(jìn)行均勻分布，盡可能的實(shí)現(xiàn)各分區(qū)所散列的數(shù)據(jù)相等。

語法：

CREATE TABLE 表名 (字段 數(shù)據(jù)類型 約束...) PARTITION BY HASH (某個(gè)字段) -- hash是散列 ( ????PARTITION 分區(qū)名1 [TABLESPACE 表空間1], ????PARTITION 分區(qū)名2 [TABLESPACE 表空間2], ????.... ????PARTITION 分區(qū)名n [TABLESPACE 表空間n] );

演示：

create table graderecord2 ( sno varchar2(10), sname varchar2(20), dormitory varchar2(3), grade int ) partition by hash(grade) ( partition p1, partition p2, partition p3 ); insert into graderecord2 values('511601','魁','229',92); insert into graderecord2 values('511602','凱','229',62); insert into graderecord2 values('511603','東','229',26); insert into graderecord2 values('511604','亮','228',77); insert into graderecord2 values('511605','敬','228',47); insert into graderecord2(sno,sname,dormitory) values('511606','峰','228'); insert into graderecord2 values('511607','明','240',90); insert into graderecord2 values('511608','楠','240',100); insert into graderecord2 values('511609','濤','240',67); insert into graderecord2 values('511610','博','240',75); insert into graderecord2 values('511611','錚','240',60); insert into graderecord3 values('511611','錚','240',60); commit; select * from graderecord2; -- 查看全部數(shù)據(jù) select * from graderecord2 partition(p1); -- 查看p1分區(qū)數(shù)據(jù) select * from graderecord2 partition(p2);-- 查看p2分區(qū)數(shù)據(jù) select * from graderecord2 partition(p3);-- 查看p3分區(qū)數(shù)據(jù)

<3>、列表分區(qū)【list】

列表分區(qū)明確指定了根據(jù)某字段的某個(gè)具體值進(jìn)行分區(qū)，而不是像范圍分區(qū)那樣根據(jù)字段的值范圍來劃分的。

語法：

CREATE TABLE 表名 (字段 數(shù)據(jù)類型 約束...) PARTITION BY LIST (某個(gè)字段) -- list是列表 ( ????PARTITION 分區(qū)名1 values(值,值,值...) [TABLESPACE 表空間1], ????PARTITION 分區(qū)名2 values(值,值,值...) [TABLESPACE 表空間2], ????.... ????PARTITION 分區(qū)名n values(值,值,值...) [TABLESPACE 表空間n] );

演示：

create table graderecord3 ( sno varchar2(10), sname varchar2(20), dormitory varchar2(3), grade int ) partition by list(dormitory) ( partition p1 values('229') , partition p2 values('228'), partition p3 values('240') ); insert into graderecord3 values('511601','魁','229',92); insert into graderecord3 values('511602','凱','229',62); insert into graderecord3 values('511603','東','229',26); insert into graderecord3 values('511604','亮','228',77); insert into graderecord3 values('511605','敬','228',47); insert into graderecord3(sno,sname,dormitory) values('511606','峰','228'); insert into graderecord3 values('511607','明','240',90); insert into graderecord3 values('511608','楠','240',100); insert into graderecord3 values('511609','濤','240',67); insert into graderecord3 values('511610','博','240',75); insert into graderecord3 values('511611','錚','240',60); commit;

select * from graderecord3; -- 查看全部數(shù)據(jù) select * from graderecord3 partition(p1); -- 查看p1分區(qū)數(shù)據(jù) select * from graderecord3 partition(p2);-- 查看p2分區(qū)數(shù)據(jù) select * from graderecord3 partition(p3);-- 查看p3分區(qū)數(shù)據(jù)

<4>、復(fù)合分區(qū)

(1)、范圍-散列分區(qū)

語法：

CREATE TABLE 表名 (字段 數(shù)據(jù)類型 約束...) PARTITION BY RANGE (某個(gè)字段) -- 先進(jìn)行范圍的字段，只能是一個(gè)字段 subpartition by hash(字段1,字段2...) -- 后進(jìn)行散列的字段，注意此處可以設(shè)置多個(gè)字段散列 ( partition 范圍分區(qū)1 values less than(值1) [TABLESPACE 表空間] ??????????( ??????????????subpartition 散列分區(qū)1 [TABLESPACE 表空間], ??????????????subpartition 散列分區(qū)2 [TABLESPACE 表空間] ??????????????... ??????????), .... partition 范圍分區(qū)n values less than(maxvalue) [TABLESPACE 表空間] ??????????( ??????????????subpartition 散列分區(qū)3 [TABLESPACE 表空間], ??????????????subpartition 散列分區(qū)4 [TABLESPACE 表空間] ??????????????... ??????????) );

演示：

create table graderecord4 ( sno varchar2(10), sname varchar2(20), dormitory varchar2(3), grade int ) partition by range(grade) -- 范圍 subpartition by hash(sname,dormitory) -- 散列 ( ??partition p1 values less than(60) -- 成績(jī)小于60的會(huì)被散列到sp1和sp2中 ??( ??????subpartition sp1, ??????subpartition sp2 ??), ??partition p2 values less than(maxvalue) -- 其他成績(jī)的會(huì)被散列到sp3和sp4中 ??( ??????subpartition sp3, ??????subpartition sp4 ??) );

insert into graderecord4 values('511601','魁','229',92); insert into graderecord4 values('511602','凱','229',62); insert into graderecord4 values('511603','東','229',26); insert into graderecord4 values('511604','亮','228',77); insert into graderecord4 values('511605','敬','228',47);

insert into graderecord4(sno,sname,dormitory) values('511606','峰','228'); insert into graderecord4 values('511607','明','240',90); insert into graderecord4 values('511608','楠','240',100); insert into graderecord4 values('511609','濤','240',67); insert into graderecord4 values('511610','博','240',75); insert into graderecord4 values('511611','錚','240',60); commit; select * from graderecord4; -- 查看全部數(shù)據(jù) select * from graderecord4 partition(p2); -- 查看60分上成績(jī) select * from graderecord4 subpartition(sp3); -- 查看p2下散列的分區(qū) select * from graderecord4 subpartition(sp4);

(2)、范圍-列表分區(qū)

語法：

CREATE TABLE 表名 (字段 數(shù)據(jù)類型 約束...) PARTITION BY RANGE (某個(gè)字段) -- 先進(jìn)行范圍的字段，只能是一個(gè)字段 subpartition by LIST(字段1,字段2...) -- 后進(jìn)行散列的字段，注意此處可以設(shè)置多個(gè)字段散列 ( partition 范圍分區(qū)1 values less than(值1) [TABLESPACE 表空間] ??????????( ??????????????subpartition 列表分區(qū)1 values(值,值，值...) [TABLESPACE 表空間], ??????????????subpartition 列表分區(qū)2 values(值,值，值...) [TABLESPACE 表空間] ??????????????... ??????????), .... partition 范圍分區(qū)n values less than(maxvalue) [TABLESPACE 表空間] ??????????( ??????????????subpartition 列表分區(qū)3 values(值,值，值...) [TABLESPACE 表空間], ??????????????subpartition 列表分區(qū)4 values(值,值，值...) [TABLESPACE 表空間] ??????????????... ??????????) );

演示：

create table graderecord5 ( sno varchar2(10), sname varchar2(20), dormitory varchar2(3), grade int ) partition by range(grade) -- 成績(jī)范圍，分成及格/不及格 subpartition by list(dormitory) -- 列表，只按照科目分 ( ??partition bujige values less than(60) -- 不及格 ??( ??????subpartition sp229_bjg values('229'), -- 229不及格 ??????subpartition sp228_bjg values('228'), -- 228不及格 ??????subpartition sp240_bjg values('240') -- 240不及格 ??), 2、分裂表分區(qū) 拆分分區(qū)將一個(gè)分區(qū)拆分兩個(gè)新分區(qū)，拆分后原來分區(qū)不再存在。注意不能對(duì)HASH類型的分區(qū)進(jìn)行拆 分。 語法： 演示： ??partition jige values less than(maxvalue) -- 及格 ??( ??????subpartition sp229_jg values('229'), -- 229及格 ??????subpartition sp228_jg values('228'), -- 228及格 ??????subpartition sp240_jg values('240') -- 240及格 ??) ); insert into graderecord5 values('511601','魁','229',92); insert into graderecord5 values('511602','凱','229',59); insert into graderecord5 values('511603','東','229',26); insert into graderecord5 values('511604','亮','228',77); insert into graderecord5 values('511605','敬','228',47); insert into graderecord5 values('511606','峰','228',99); insert into graderecord5 values('511607','明','240',90); insert into graderecord5 values('511608','楠','240',100); insert into graderecord5 values('511609','濤','240',32); insert into graderecord5 values('511610','博','240',75); insert into graderecord5 values('511611','錚','240',60); commit; select * from graderecord5; -- 查看全部數(shù)據(jù) select * from graderecord5 partition(jige); -- 及格區(qū) select * from graderecord5 subpartition(sp228_jg);-- 228及格的 alter table 表 split partition 分區(qū)名 at【在】(值) into(partition 新分區(qū)1,partition 新分區(qū)2); ? drop table tstable; CREATE TABLE tstable ( ??tsid number(11) primary key, ??tsname varchar2(20), ??tssex varchar2(10), ??tsgrade number(11) ) ? PARTITION BY range (tsgrade) -- 基于成績(jī)來范圍分為及格不及格 ( ????PARTITION p1 values less than(60), -- 不及格 ????PARTITION p2 values less than(maxvalue) -- 及格 ) 3、合并分區(qū) 合并分區(qū)是將相鄰的分區(qū)合并成一個(gè)分區(qū)，結(jié)果分區(qū)將采用較高分區(qū)的界限，注意的是，不能將分區(qū)合 并到界限較低的分區(qū)。 語法：

ALTER TABLE 表名 MERGE PARTITIONS 分區(qū)1【低界限】,分區(qū)2【高界限】 INTO PARTITION 分區(qū)2 【高界限】; 演示： insert into tstable values(1,'張三','男',30); insert into tstable values(2,'張四','女',40); insert into tstable values(3,'張五','男',50); insert into tstable values(4,'張六','女',60); insert into tstable values(5,'張七','男',70); insert into tstable values(6,'張八','女',80); insert into tstable values(7,'張九','男',90); insert into tstable values(8,'張十','女',100); commit; -- 拆分分區(qū)，及格中還有良好和優(yōu)秀兩種分類，就要將p2【及格分區(qū)】分為L(zhǎng)H 和YX 以80分分界 alter table tstable split partition p2 at(80) into(partition LH,partition YX); ? select * from tstable; select * from tstable partition(p1); select * from tstable partition(p2); -- 拆分后p2不存在 select * from tstable partition(LH); -- 新分區(qū)LH不包含80的 select * from tstable partition(YX); -- 新分區(qū)YX包含80的

演示：

drop table tstable; CREATE TABLE tstable ( ??tsid number(11) primary key, ??tsname varchar2(20), ??tssex varchar2(10), ??tsgrade number(11) ) ? PARTITION BY range (tsgrade) -- 基于成績(jī)來范圍分為及格不及格 ( ????PARTITION bjg values less than(60), -- 不及格 ????PARTITION jg values less than(80), -- 及格 ????PARTITION yx values less than(maxvalue) -- 優(yōu)秀 ) insert into tstable values(1,'張三','男',30); insert into tstable values(2,'張四','女',40); insert into tstable values(3,'張五','男',50); insert into tstable values(4,'張六','女',60); insert into tstable values(5,'張七','男',70); insert into tstable values(6,'張八','女',80); insert into tstable values(7,'張九','男',90); insert into tstable values(8,'張十','女',100); 4、追加分區(qū) 注意：添加的分區(qū)界限應(yīng)該高于最后一個(gè)分區(qū)界限。 語法： 演示： 5、刪除分區(qū) commit; -- 要將及格和優(yōu)秀統(tǒng)稱為yx，就要合并分區(qū)jg和yx ALTER TABLE tstable MERGE PARTITIONS jg,yx INTO PARTITION yx; -- ps: 合并分區(qū)不能將內(nèi)容合并到界限低的分區(qū)中；所謂界限就是分區(qū)創(chuàng)建時(shí)的values的范圍 -- 例如： jg的values less than 80 ， yx的values less than maxvalue，所以yx界限高 -- 因此只能將jg合并到y(tǒng)x中 select * from tstable partition(yx); ALTER TABLE 表名 ADD PARTITION 分區(qū)名 VALUES LESS THAN(值); --追加范圍分區(qū) ALTER TABLE 表名 ADD PARTITION 分區(qū)名; -- 追加hash分區(qū) ALTER TABLE 表名 ADD PARTITION 分區(qū)名 values(值，值，值...); -- 追加list分區(qū) drop table tstable; CREATE TABLE tstable ( ??tsid number(11) primary key, ??tsname varchar2(20), ??tssex varchar2(10), ??tsgrade number(11) ) ? PARTITION BY range (tsgrade) -- 基于成績(jī)來范圍分為及格不及格 ( ????PARTITION bjg values less than(60), ????PARTITION jg values less than(80) -- 最大只能到80一下【79】 ) -- 正常追加80之前的都可以，因?yàn)橛蟹謪^(qū) insert into tstable values(1,'張三','男',30); insert into tstable values(2,'張四','女',40); insert into tstable values(3,'張五','男',50); insert into tstable values(4,'張六','女',60); insert into tstable values(5,'張七','男',70); commit; -- 追加80以上的因?yàn)闆]有分區(qū)，所以不行，因此要追加80以上的分區(qū) ALTER TABLE tstable ADD PARTITION ps VALUES LESS THAN(120); -- 將范圍設(shè)置到120下 【119】 insert into tstable values(6,'張八','女',80); insert into tstable values(7,'張九','男',90); insert into tstable values(8,'張十','女',100); commit; 語法： 演示： 6、截?cái)喾謪^(qū)【重置分區(qū)】 截?cái)嗄硞€(gè)分區(qū)是指刪除某個(gè)分區(qū)中的數(shù)據(jù)，并不會(huì)刪除分區(qū)，也不會(huì)刪除其它分區(qū)中的數(shù)據(jù)。當(dāng)表中即 使只有一個(gè)分區(qū)時(shí)，也可以截?cái)嘣摲謪^(qū)。 語法： ALTER TABLE 表名 DROP PARTITION 分區(qū)名; drop table tstable; CREATE TABLE tstable ( ??tsid number(11) primary key, ??tsname varchar2(20), ??tssex varchar2(10), ??tsgrade number(11) ) ? PARTITION BY range (tsgrade) -- 基于成績(jī)來范圍分為及格不及格 ( ????PARTITION p1 values less than(60), ????PARTITION p2 values less than(80), ????PARTITION p3 values less than(maxvalue) ) insert into tstable values(1,'張三','男',30); insert into tstable values(2,'張四','女',40); insert into tstable values(3,'張五','男',50); insert into tstable values(4,'張六','女',60); insert into tstable values(5,'張七','男',70); insert into tstable values(6,'張八','女',80); insert into tstable values(7,'張九','男',90); insert into tstable values(8,'張十','女',100); commit; -- 先查詢一下 select * from tstable; -- 刪除p2分區(qū) alter table tstable DROP partition p2; -- 再查詢一下 select * from tstable; select * from tstable partition(p1); select * from tstable partition(p3); select * from tstable partition(p2); -- 沒有 ALTER TABLE 表名 TRUNCATE PARTITION 分區(qū)名; 演示： 7、分區(qū)重命名 語法： 演示： drop table tstable; CREATE TABLE tstable ( ??tsid number(11) primary key, ??tsname varchar2(20), ??tssex varchar2(10), ??tsgrade number(11) ) ? PARTITION BY range (tsgrade) -- 基于成績(jī)來范圍分為及格不及格 ( ????PARTITION p1 values less than(60), ????PARTITION p2 values less than(80), ????PARTITION p3 values less than(maxvalue) ) insert into tstable values(1,'張三','男',30); insert into tstable values(2,'張四','女',40); insert into tstable values(3,'張五','男',50); insert into tstable values(4,'張六','女',60); insert into tstable values(5,'張七','男',70); insert into tstable values(6,'張八','女',80); insert into tstable values(7,'張九','男',90); insert into tstable values(8,'張十','女',100); commit; -- 先查詢一下 select * from tstable; -- 截?cái)鄍2分區(qū) alter table tstable TRUNCATE partition p2; -- 再查詢一下 select * from tstable; select * from tstable partition(p1); select * from tstable partition(p3); select * from tstable partition(p2); -- 存在分區(qū)，但是數(shù)據(jù)沒了 ALTER TABLE 表名 RENAME PARTITION 原分區(qū)名 TO 新分區(qū)名; drop table tstable; CREATE TABLE tstable ( ??tsid number(11) primary key, ??tsname varchar2(20), ??tssex varchar2(10), ??tsgrade number(11) ) ? PARTITION BY range (tsgrade) -- 基于成績(jī)來范圍分為及格不及格 ( ????PARTITION p1 values less than(60), 十、Oracle索引 索引可以提高查詢效率 1、Oracle索引注意： （1）、索引列盡量再where子句中提取，對(duì)使用頻繁的字段加入合適的索引 （2）、一個(gè)表中不是索引越多越好，索引的維護(hù)也要資源開銷 （3）、將表和索引建立到相同表空間中，可以簡(jiǎn)化表空間管理；將表和索引建立到不相同表空間中，可 以提高訪問性能 （4）、最好不要小表上建立索引【沒必要】 （5）、當(dāng)在大表上建立索引時(shí)，最好加上NOLOGGING【日志模式】選項(xiàng), 可以節(jié)省重做日志的空間， 降低索引建立時(shí)間，提高效率 （6）、連表時(shí)，建議在連接列上建立索引 2、Oracle索引分類 (1)、btree索引 Btree索引是oracle默認(rèn)索引類型，結(jié)構(gòu)和mysql的是一樣的 ????PARTITION p2 values less than(80), ????PARTITION p3 values less than(maxvalue) ) insert into tstable values(1,'張三','男',30); insert into tstable values(2,'張四','女',40); insert into tstable values(3,'張五','男',50); insert into tstable values(4,'張六','女',60); insert into tstable values(5,'張七','男',70); insert into tstable values(6,'張八','女',80); insert into tstable values(7,'張九','男',90); insert into tstable values(8,'張十','女',100); commit; -- 將p2重命名為jg ALTER TABLE tstable RENAME PARTITION p2 TO jg; -- 就要用重命名的新名來查詢 select * from tstable partition(jg); oracle日志模式分為（logging，force logging，nologging）,默認(rèn)情況是logging，就是會(huì)記錄到 redo日志中，force logging是強(qiáng)制記錄日志，nologging是盡量減少日志

Btree:多叉樹或平衡多路查找樹【概念重點(diǎn)】,一顆m叉的的BTREE特性如下： 1.m叉樹中每個(gè)節(jié)點(diǎn)最多包含m個(gè)元素； 2.除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)【沒有子分支節(jié)點(diǎn)】外，每個(gè)節(jié)點(diǎn)至少有ceil(m/2)個(gè)孩子 3.若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有兩個(gè)孩子 4.所有的葉子節(jié)點(diǎn)都在同一層 5.每個(gè)非葉子節(jié)點(diǎn)【帶有分支的節(jié)點(diǎn)】由n個(gè)key【上圖藍(lán)色小塊】與n+1個(gè)指針【上圖黃色小塊】組成，其中 key范圍是ceil(m/2)-1<=n<=m-

語法

創(chuàng)建索引： create index 索引名 on 表名(字段,字段,....) [tablespace 表空間] 刪除索引： drop index 索引名;

演示

創(chuàng)建索引： create index 索引名 on 表名(字段,字段,....) [tablespace 表空間] 刪除索引： drop index 索引名;

演示

(2)、位圖索引 對(duì)于一些特殊的表，比如一個(gè)表中只有一列、數(shù)據(jù)重復(fù)度高【例如性別】，此時(shí)建立Btree索引對(duì)表進(jìn)行 檢索時(shí)，回返回將進(jìn)一半的數(shù)據(jù)，Btree索引效率很低，此時(shí)就可以使用位圖索引。具體什么時(shí)候用有一 個(gè)參考的變量“基數(shù)” oracle推薦如果一個(gè)列的“基數(shù)”小于1%適合建立位圖索引【基數(shù)越小，重復(fù)率越 高】，例如一個(gè)表中10000條數(shù)據(jù)，一列中可取的值有100種，此時(shí)基數(shù)就是1%【值種類/總行數(shù) *100%】，一個(gè)表中10000條數(shù)據(jù)，一列中可取的值有10種，此時(shí)基數(shù)就是0.1% 語法：

創(chuàng)建索引： create bitmap index 索引名 on 表名(字段) [tablespace 表空間] 刪除索引： drop index 索引名;

演示

補(bǔ)充：oracle有個(gè)變量CREATE_BITMAP_AREA_SIZE 【位圖區(qū)大小】，該變量值越大位圖索引效率越 高，默認(rèn)8MB

alter system set CREATE_BITMAP_AREA_SIZE=字節(jié)數(shù)【默認(rèn)：8388608 （8MB）】

(3)、反向鍵索引 數(shù)據(jù)庫會(huì)自動(dòng)的表的主鍵創(chuàng)建Btree索引【主鍵索引】，通常的主鍵都是自增序列，如果使用默認(rèn)的 Btree索引，當(dāng)數(shù)據(jù)量巨大時(shí)會(huì)導(dǎo)致索引數(shù)據(jù)分布不均，葉子節(jié)點(diǎn)會(huì)偏向某一個(gè)方向，索引的鍵值會(huì)基本 分布在同一個(gè)葉塊中，容易出現(xiàn)索引塊爭(zhēng)用問題。因此就可以使用反向鍵索引，將鍵值分散到不同葉塊 中，減少索引塊爭(zhēng)用問題，提高效率 ps：反向鍵索引就是將索引鍵給倒轉(zhuǎn)過來，例如：1001，1002，1003 反轉(zhuǎn)為1001，2001，3001，這 樣就會(huì)分散到不同的葉塊中，從而降低索引塊爭(zhēng)用

語法

創(chuàng)建索引： create [bitmap] index 索引名 on 表名(字段) reverse [tablespace 表空間] 刪除索引： drop index 索引名;

演示

注意

反向鍵索引雖然解決了索引葉爭(zhēng)用問題，提高了效率；但是反向鍵索引會(huì)將索引散列到多個(gè)葉塊中了， 如果進(jìn)行范圍查詢時(shí)只能進(jìn)行全表的掃描了，性能反而降低了

柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫 Oracle介紹

http://yzkb.51969.com/

好文鏈接