Oracle資料庫基本操作（一） —— Oracle資料庫體繫結構介紹、DDL、DCL、DML

本文主要介紹Oracle資料庫體繫結構以及相關DDL、DCL、DML語句的操作。 ...

一、Oracle資料庫介紹

1、基本介紹

　　Oracle資料庫系統是美國ORACLE公司（甲骨文）提供的以分散式資料庫為核心的一組軟體產品，是目前最流行的客戶/伺服器(CLIENT/SERVER)或B/S體繫結構的資料庫之一。

2、Oracle資料庫的體繫結構

　　①資料庫：database

　　Oracle資料庫是數據的物理存儲。這包括（數據文件ORA或DBF、控制文件、聯機日誌、參數文件）。Oracle數據的概念和其他資料庫不一樣，這裡的資料庫是一個操作系統只有一個庫。可以看做是Oracle就只有一個大資料庫。

　　②實例

　　一個Oracle實例（Oracle Instance）有一系列的後臺進程和記憶體結果組成。一個資料庫可以有N個實例。

　　③數據文件（dbf）

　　數據文件是資料庫的物理存儲單位。資料庫的數據是存儲在表空間中的，真正是在某一個或者多個數據文件中。而一個表空間可以由一個或多個數據文件組成，一個數據文件只能屬於一個表空間。一旦數據文件被加入某個表空間後，就不能刪除這個文件，如果要刪除某個數據文件，只能刪除其所屬與的表空間才行。

　　④表空間

　　表空間是Oracle對物理資料庫上相關數據文件（ORA或者DBF文件）的邏輯映射。一個資料庫在邏輯上被劃分成一到若幹個表空間，每個表空間包含了在邏輯上相關聯的一組結構。每個資料庫至少有一個表空間（稱之為system表空間）。

　　每個表空間由同一磁碟上的一個或多個文件組成，這些文件叫數據文件（datafile）。一個數據文件只能屬於一個表空間。

　　⑤用戶

　　用戶是在實例下建立的。不同實例中可以建相同名字的用戶。

　　註：表的數據，是有用戶放入某個表空間的，而這個表空間會隨機吧這些表數據放到一個或者多個數據文件中。

　　由於Oracle的資料庫不是普通的概念，Oracle是由用戶和表空間對數據進行管理和存放的。但是表不是有表空間去查詢的，而是由用戶去查的。因為不同用戶可以在同一個表空間建立同一個名字的表，這裡區分就是用戶了。

　　Oracle資料庫【地球】可以有多個實例【亞洲、歐洲等等】，而每個實例【亞洲】可以分為多個表空間（邏輯上，人為的）【中國、印度、巴基斯坦等等】，每個表空間可以存在多個用戶【中國（表空間），省政府（用戶）】，每個用戶【【省政府】管理該表空間下的指定數據文件【下轄土地】。

二、Oracle資料庫基本操作

　　DDL （ Data Definition Language 數據定義語言）語句:用來定義資料庫對象（庫、表、列等），創建、刪除、修改庫、表結構。

　　DML（Data Manipulation Language 數據操作語言）語言：用來定義資料庫記錄（數據），增、刪、改表記錄；

　　DCL（Data Control Language 數據控制語言）語言：用來調用訪問許可權和安全級別；

　　DQL（Data Query Language 數據查詢語言）語言：用來查詢記錄（數據）；

（一）DDL語句管理表

　　假設要開發一個項目: 1. 創建表空間 2. 創建用戶（授權） 3. 用戶去建表

　　1、創建表空間（由管理員賬號創建）

　　語法:

create tablespace 表空間的名稱
datafile '存放的位置' //指定表空間對應的數據文件存放位置
size 初始大小 
autoextend on //自動增長,當表空間存儲都占滿時,自動增長
next 每次增長多少

　　示例：

create tablespace mallproject
datafile 'c:\mallproject.dbf'
size 100m
autoextend on
next 10m;

--刪除表空間
drop tablespace mallproject;

　　2、創建用戶

　　①創建用戶語法：

create user 用戶名 identified by 密碼 default tablespace mallproject

　　密碼必須不能以數字及特殊開頭。

　　示例：

create user zhansan identified by zs123 default tablespace mallproject;

　　②用戶授權：

--授權DCL:grant
--授權zhansan擁有創建會話許可權
grant create session to zhansan;

　　Oracle 中已存在三個重要的角色：connect角色（是授予最終用戶的典型權利，最基本的），resource角色（是授予開發人員的），dba角色（擁有全部特權，是系統最高許可權，只有DBA才可以創建數據結構，並且系統許可權也需要DBA授出，且DBA用戶可以操作全體用戶的任意基表，包括刪除）。

-- 授予角色
grant dba to zhansan;
-- 登錄zhansan賬號，可操作任意用戶的表
select * from scott.emp;

　　③刪除用戶及用戶下的數據

drop user 用戶名 cascade;
-- 刪除zhansan用戶及其下的數據
drop user ZHANSAN cascade;

　　3、創建表

　　①語法：

create table 表名(
       列名 列的類型 [列的約束],
       列名 列的類型 [列的約束]
);

　　②列的類型:

- varchar2(總長度) : 表示的可變長度字元串,但是不能超過最大長度
  - varchar2(10) : 占5個長度字元
- char(總長度) : 表示的固定的長度字元串
- number(總長度,小數位數): 表示的數字類型
- date : 年月日時分秒
- timestamp : 時間戳表示的時間更加精確可信
- LONG : 大文本的類型最大可存2G
- CLOB : Character Large Object 字元大對象, 最大可存4G
- BLOB : Binary Large Object 二進位大對象 , 最大可存4G
  - 用戶頭像 ---> 100kb

　　③示例：

create table person(
    pid number,
    pname vachar2(20),
    age number,
    sal number(10,2)
);

　　④使用子查詢創建表

--使用子查詢創建表
--相當於是複製表,結構和表中的數據
create table emp as select * from scott.emp;--只複製表結構
create table emp1 as select * from scott.emp where 1=2;

　　4、修改表

　　①添加語法：

alter table 表名稱 add(列名1 類型 [default 預設值]，列名2 類型 [default 預設值]....)

　　②修改語法：

alter table 表名稱 modify(列名1 類型 [default 預設值]，列名2 類型 [default 預設值]....)

　　③修改列名：

alter table 表名稱 rename column 列名1 to 列名2；

　　④示例：

-- 添加列
alter table person add sex VARCHAR2(10);
-- 同時添加多列
ALTER TABLE person ADD(
    mobile VARCHAR2(10),
    adddress VARCHAR2(100)
);
-- 重定義列
alter table person modify mobile number;
-- 修改列名
alter table person rename column sex to gerder;
-- 刪除列
delete table person drop column address;
-- 修改表名
rename person to p;

　　5、刪除表

-- 刪除表
drop table p;

　　6、約束

①表的五大約束:

- 主鍵約束: primary key 非空並且唯一
- 唯一約束: unique 唯一可以為空
- 非空約束: not null 不能為空
- 檢查約束: check(條件), mysql支持這個語法,但是沒有效果
- 外鍵約束: 約束從表中記錄必須是參考主表中的記錄
  - 強制刪除表
  - 級聯刪除

②示例：

create table person(
    pid number primary key,
    pname varchar2(20) unique,
    age number not null,
    gender varchar2(10) check(gender in('男','女'))
);
-- 報 ORA-02290: check constraint (ZHANSAN.SYS_C005421) violated 錯
insert into person values(1,'zs',18,'妖');

/*
    外鍵約束: 約束從表中記錄必須是參考主表中的記錄   
                   商品表 -----> 分類表ID
*/
-- 分類表
create table category(
       cid number primary key,
       cname varchar2(20)
);
-- 商品表
create table product(
       pid number primary key,
       pname varchar2(20),
       cno number
);

-- 添加外鍵約束
alter table product add foreign key(cno) references category(cid);

insert into category values(1,'手機數位'); 
insert into product values(1,'錘子手機',1);

-- 強制刪除category表(不建議使用) 
-- 先解除外鍵約束,然後再刪除自己 
drop table category cascade constraint;  

-- 級聯刪除 
alter table product add foreign key(cno) references category(cid) on delete cascade; 

insert into category values(1,'手機數位'); 
insert into category values(2,'鞋靴箱包'); 
insert into product values(1,'錘子手機',1);
insert into product values(2,'耐克跑鞋',2); 

-- 級聯刪除:刪除category 中的1號記錄,級聯刪除 
-- 刪除主表中記錄的時候,會先去刪除從表中對應的記錄,然後再刪除主表中記錄 
delete from category where cid = 1;

（二）DML（詳細請參考MySQL基本操作篇）

　　1、插入記錄

-- 使用子查詢插入記錄
insert into emp select * from scott.emp where deptno=10;

　　2、更新記錄

　　語法：

update 表名 set 列名 = 列的值 [where 條件]

　　3、刪除記錄

　　delete和truncate的區別：

delete	truncate
逐條刪除數據	先刪除表然後再創建表
DML	DDL
支持事務	不支持事務
刪除數據可以回滾	不能回滾
	刪除所有記錄,執行效率高

　　4、事務

　　①事務的概念：一組SQL操作,要麼都成功,要麼都失敗。

　 ②事務的特型:ACID

- 原子性: 事務是最小不可分割的單元
- 一致性: 事務執行前後,數據的狀態要保持一致
- 隔離性: 一個事務在執行的時候,不受其它事務影響
- 持久性: 事務一旦提交,數據就持久的保存到磁碟上

　　③若不考慮事務的隔離級別,會出現什麼問題: 臟讀, 不可重覆讀, 虛讀/幻讀

- 臟讀 : 一個事務讀取到了另一個事務未提交的數據
- 不可重覆讀: 一個事務多次讀取,前後數據不一致(update)
- 虛讀/幻讀: 一個事務多次讀取,前後數據不一致(insert)

　　④隔離級別:

　　　　mysql:

- READ UNCOMMITTED 讀取未提交問題: 臟讀, 不可重覆讀, 虛讀
- READ COMMITTED 讀取已提交問題:不可重覆讀, 虛讀
- REPEATABLE READ 可重覆讀問題: 虛讀
- SERIALIZABLE 串列化,一個事務在執行的時候,其它事務處於等待狀態沒有問題

　　　　Oracle:

- READ COMMITTED (預設)
- SERIALIZABLE
- READ ONLY

　　⑤事務的保存點: savepoint 保存點的名稱

　　事務回滾到保存點: rollback to 保存點

　　應用場景——數據遷移: 1000萬條 1--50萬設置保存點--100萬事務

　　　示例：

create table louceng(
    lou number primary key
);

BEGIN
-- 業務層
    INSERT INTO louceng VALUES(1) ; 
    INSERT INTO louceng VALUES(2) ; 
    INSERT INTO louceng VALUES(3) ; 
    SAVEPOINT sanlou ; 
    INSERT INTO louceng VALUES(4) ; 
    INSERT INTO louceng VALUES(4) ; 
    INSERT INTO louceng VALUES(5) ; 
    COMMIT ; 
EXCEPTION 
-- 處理異常
    WHEN others THEN
        ROLLBACK TO sanlou ; 
        COMMIT;
END ;

select * from louceng;