Oracle數(shù)據(jù)庫常用的知識點(diǎn)匯總

1、Oracle數(shù)據(jù)庫常用的知識點(diǎn)匯總SQL語言的分類數(shù)據(jù)查詢語言(DQL)數(shù)據(jù)操縱語言(DML)事物控制語言(TCL)數(shù)據(jù)定義語言(DDL)數(shù)據(jù)控制語言(DCL)存儲過程、函數(shù)、包相關(guān)操作創(chuàng)建存儲過程不帶參數(shù)create or replace procedure 存儲過程名 isbeginPL/SQL語句end 存儲過程名;帶參數(shù)create or replace procedure 存儲過程名(參數(shù)1 in|out|in out 數(shù)據(jù)類型,參數(shù)2 in|out|in out 數(shù)據(jù)類型,.參數(shù)3 in|out|in out 數(shù)據(jù)類型) isbeginPL/SQL語句end 存儲過程名;創(chuàng)建函數(shù)c

2、reate function 函數(shù)名(參數(shù)1 數(shù)據(jù)類型,參數(shù)2 數(shù)據(jù)類型.參數(shù)n 數(shù)據(jù)類型) return 返回值類型 is變量名1 數(shù)據(jù)類型;變量名2 數(shù)據(jù)類型;.變量名n 數(shù)據(jù)類型;beginPL/SQL語句end;刪除函數(shù)drop function 函數(shù)名;程序包的規(guī)范create or replace package 包名 is函數(shù)前部分存儲過程前部分end 包名;程序包的主體create or replace package body 包名 is函數(shù)前部分和實(shí)現(xiàn)存儲過程前部分和實(shí)現(xiàn)end 包名;創(chuàng)建表空間create tablespace 表空間名 datafile 路徑 size

3、大小 reuse設(shè)置默認(rèn)表空間alter database default tablespace 表空間名重命名表空間alter tablespace 原表空間名 rename to 新表空間名刪除表空間drop tablespace 表空間名 cascade constraints從表空間中刪除數(shù)據(jù)文件alter tablespace 表空間名 drop datafile路徑控制文件的多路復(fù)用更改control_file文件alter system set control_files=C:appZGYoradataorclCONTROL01.CTL,C:appZGYflash_recover

4、y_areaorclCONTROL02.CTL,C:zgyCONTROL03.CTLscope=spfile;復(fù)制控制文件select name as 控制文件 from v$controlfile;表空間與數(shù)據(jù)文件的關(guān)系表空間與數(shù)據(jù)文件之間的關(guān)系非常密切，二者相互依存，創(chuàng)建表空間時(shí)必須創(chuàng)建數(shù)據(jù)文件，增加數(shù)據(jù)文件時(shí)也必須指定表空間。select tablespace_name,file_name,bytes from dba_data_files;Oracle11g的默認(rèn)表空間永久表空間創(chuàng)建表空間create tablespace tbs_test_1 datafileC:zgydatafil

5、esdatafile1.dbf size 10m extent management local uniform size 256k;create tablespace tbs_test_2 datafileC:zgydatafilesdatafile2.dbf size 10m extent management local autoallocate;設(shè)置默認(rèn)永久表空間alter database default tablespace 表空間名;alter database default tablespace tbs_test_2;在表空間中添加數(shù)據(jù)文件alter tablespace 已

6、有表空間名 add datafile絕對路徑 size 10m autoextend on next 5m maxsize unlimited;在表空間中刪除數(shù)據(jù)文件alter tablespace 已有表空間名 drop datafile絕對路徑;刪除表空間drop tablespace 表空間名 including contents cascade constraints;drop tablespace tbs_test_2 including contents cascade constraints;臨時(shí)表空間創(chuàng)建臨時(shí)表空間create temporary tablespace 表空間名

7、 tempfile絕對路徑 size 數(shù)字大小m;設(shè)置默認(rèn)臨時(shí)表空間alter database default temprory tablespace 表空間名;刪除臨時(shí)表空間數(shù)據(jù)表的相關(guān)操作創(chuàng)建表create table 表名(字段名1 數(shù)據(jù)類型 其他約束,字段名2 數(shù)據(jù)類型 其他約束,.字段名3 數(shù)據(jù)類型 其他約束);增加字段alter table 表名 add(字段名 數(shù)據(jù)類型);刪除字段刪除一列alter table 表名 drop column 字段名;刪除多列alter table 表名 drop(字段1,字段2,字段n);修改字段alter table 表名 modify 原字

8、段名 要更改的數(shù)據(jù)類型;修改表名alter table 原表名 rename to 新表名;刪除表drop table 表名 cascade constraints;非空約束alter table 表名 modify 字段名 not null;刪除約束alter table 表名 modify 字段名 null;主鍵約束alter table 表名 add constraint 約束名 primary key(字段名);刪除約束alter table 表名 drop constraint 約束名;唯一約束alter table 表名 add constraint 約束名 unique(字段名)

9、;刪除約束alter table 表名 drop constraint 約束名;外鍵約束alter table 表名 add constraint 約束名 foreign key(原表字段名) references 所聯(lián)系表(字段名);刪除約束alter table 表名 drop constraint 約束名;索引的操作創(chuàng)建B樹索引create index 索引名 on 哪個(gè)表(字段名) pctfree 數(shù)字 tablespace 表空間名;創(chuàng)建位圖索引create bitmap index 索引名 on 哪個(gè)表(字段名) tablespace 表空間名;創(chuàng)建函數(shù)索引create index

10、 索引名 on 哪個(gè)表(一個(gè)函數(shù)里面加字段名);刪除索引drop index 索引名;視圖的操作創(chuàng)建視圖create or replace view 視圖名 as 查詢語句; 刪除視圖drop view 視圖名;同義詞操作創(chuàng)建同義詞公有同義詞create public synonym 同義詞名 for 哪個(gè)表;私有同義詞create synonym 同義詞名 for 哪個(gè)表;刪除同義詞公有同義詞drop public synonym 同義詞名;私有同義詞drop synonym 同義詞名;用戶管理與權(quán)限分配創(chuàng)建用戶create user 用戶名 identified by 密碼 default

11、 tablespace 已有表空間名 temporary tablespace 已有臨時(shí)表空間名;修改用戶密碼alter user 用戶名 identified by 新密碼;解鎖用戶alter user 用戶名 account unlock;刪除用戶drop user 用戶名 cascade;為用戶授權(quán)grant 系統(tǒng)權(quán)限|角色名 to 用戶名;回收系統(tǒng)權(quán)限r(nóng)evoke 系統(tǒng)權(quán)限 from 用戶名;對象授權(quán)grant 對象權(quán)限 on 表名 to 用戶名;回收對象權(quán)限r(nóng)evoke 對象權(quán)限 on 表名 from 用戶名;創(chuàng)建角色create role 角色名 identified by 密碼;

12、為角色授系統(tǒng)權(quán)限grant 系統(tǒng)權(quán)限 to 角色名;授權(quán)角色給用戶grant 角色名 to 用戶名;刪除角色drop role 角色名;補(bǔ)充1.啟動和關(guān)閉數(shù)據(jù)庫1.啟動startup nomount|mount|open|forcenomount:表示啟動數(shù)據(jù)實(shí)例，加載數(shù)據(jù)庫，但不啟動數(shù)據(jù)庫open:默認(rèn)的啟動方式，表示啟動數(shù)據(jù)實(shí)例，裝載數(shù)據(jù)庫并啟動2.關(guān)閉shutdown normal|transactional|immediate|abortimmediate:常用的關(guān)閉數(shù)據(jù)庫功能2.數(shù)據(jù)塊是oracle邏輯存儲結(jié)構(gòu)中的最小存儲單位，也是執(zhí)行數(shù)據(jù)庫，輸入輸出操作的最小存儲單位。通過查詢數(shù)據(jù)

13、字典v$parameter來查詢數(shù)據(jù)塊的大小，oracle的數(shù)據(jù)是存儲在oracle數(shù)據(jù)塊中而不是在操作系統(tǒng)數(shù)據(jù)塊中。select name,value from v$parameter where name=db_block_size;3.表空間1.SYSTEM表空間及系統(tǒng)表空間，用于存放oracle系統(tǒng)內(nèi)部表和數(shù)據(jù)字典的數(shù)據(jù)。2.SYSAUX表空間是SYSTEM的輔助表空間，用于存儲除數(shù)據(jù)字典以外的其他數(shù)據(jù)。3.UODO表空間及撤銷表空間，用于存儲撤銷信息的表空間。4.USER表空間及用戶表空間，是oracle推薦用戶使用的表空間，可以在此表空間上創(chuàng)建各種數(shù)據(jù)對象，如表、索引、用戶等數(shù)據(jù)對

14、象。4.數(shù)據(jù)文件1.系統(tǒng)數(shù)據(jù)文件2.撤銷數(shù)據(jù)文件3.用戶數(shù)據(jù)文件5.日志文件1.重做日志文件2.歸檔日志文件6.服務(wù)器參數(shù)文件1.查看服務(wù)器參數(shù)2.修改服務(wù)器參數(shù)二、SQL*PLUS命令1.PAGESIZE變量用于設(shè)置從頂部標(biāo)題至頁結(jié)束之間的行數(shù)。2.NEWPAGE變量用于設(shè)置一頁中空行的數(shù)量。select user_id,username,account_status from dba_users;set newpage 2;3.LINESIZE變量用于設(shè)置一行中最多顯示的字符總數(shù)。4.PAUSE變量用于設(shè)置輸出結(jié)果是否滾動顯示。set pause on;set pause 按enter鍵繼

15、續(xù);5.NUMFORMAT變量用于顯示數(shù)值的默認(rèn)格式。語法:set numformat format;select ename,job,sal from scott.emp;6.SQL*PLUS常用命令1.HELP命令可以向用戶提供被查詢標(biāo)題、功能描述、縮寫形式和參數(shù)選項(xiàng)等信息。help startup;help index;-可以用這個(gè)命令來查看SQL*PLUS命令清單。2.DESCRIBE命令用于查詢制定對象的數(shù)據(jù)組成結(jié)構(gòu)。desc dba_tablespaces;select empno,ename,#desc scott.emp sal from scott.emp where job

16、=SALESMAN;3.SPOOL命令把查詢結(jié)果輸出到指定文件中。spool c:/emp.txtselect empno,ename,job,sal from scott.emp where job=SALESMAN;4.DEFINE命令用于定義一個(gè)用戶變量并且可以給它一個(gè)char值。define vjob=SALESMAN;define vjob;5.SHOW命令用于顯示SQL*PLUS系統(tǒng)變量的值或SQL*PLUS環(huán)境變量的值。show parameters db_block_size;-用于顯示當(dāng)前數(shù)據(jù)實(shí)例的數(shù)據(jù)塊大小。6.EDIT命令用來編輯SQL緩沖區(qū)或指定磁盤文件中的SQL語句或

17、PL/SQL塊。7.SAVE命令將SQL緩沖區(qū)中的最近一條SQL語句或PL/SQL塊保存到一個(gè)文件中。select * from scott.dept;save C:UsersZGYtest.sql;8.GET命令把一個(gè)SQL腳本文件放進(jìn)SQL緩沖區(qū)。get C:UsersZGYtest.sql;9.START和命令用來執(zhí)行一個(gè)SQL腳本文件。start C:UsersZGYtest.sql; C:UsersZGYtest.sql;10.COLUMN命令用于格式化查詢結(jié)果、設(shè)置列寬度、重新設(shè)置標(biāo)題等功能col sal format $9999.00select empno,ename,sal from scott.emp;11.TITLE和BTITLE命令分別用來設(shè)置打印時(shí)每頁的頂部和底部標(biāo)題。操作數(shù)據(jù)庫1.插入數(shù)據(jù)語法：insert into 表名(字段名) valuse(字段)2.更新數(shù)據(jù)語法：update set 字段