標籤：option 預設值 param val size prim 資料量 imm 資料庫物件
一、資料更新操作 DML文法中主要包括兩個內容：查詢與更新，更新主要包括：增加資料、修改資料、刪除資料。其中這些操作是離不開查詢的。
1、增加資料 文法：INSERT INTO 表名稱 [(欄位名稱1, 欄位名稱2，…)] VALUES(資料1，資料2…..)；
注意：
1、對於字串需要使用""括起來
2、對於時間可以使用to_date（）進行轉換
3、數字就直接寫 範例：向myemp表中插入一條資料INSERT INTO myemp（empno, job, sal, hiredate, ename, deptno, mgr,comm）VALUES(6666,‘清潔工‘,2000, TO_DATE(‘1988-10-10‘,‘yyyy-mm-dd‘),‘王二‘,40,7369,null);
2、修改資料 文法：UPDATE 表名稱 SET 欄位1=內容1，欄位2=內容2，..…[WHERE 更新條件(s)] 範例：要求將僱員編碼為7369的僱員，工資修改為810、傭金改為100 UPDATE myemp SET sal=810,comm=100 WHERE empno=7369; 範例：將所有在81年僱傭的僱員的僱傭日期修改為今天，工資增長20% UPDATE myemp SET hiredate=SYSDATE, sal=sal*1.2 WHERE hiredate BETWEEN ‘01-1月-1081‘ AND ‘31-12月-1981‘;
3、刪除資料文法：DELETE FROM 表名稱 [WHERE 刪除條件(s)];範例：刪除僱員編號為7369的僱員資訊DELETE FROM myemp WHERE empno=7369; 範例：刪除幾個員工的資訊（用IN）DELETE FROM myemp WHERE empno IN (7566,7788,7899); 二、事物的處理
1、事務的概念 事物：是保證資料完整性的一種手段。事物具備ACID原則，保證一個人更新資料的時候，其他人不能更新。
2、事務鎖的概念 交易處理的兩個核心命令： 提交事物：commit； 復原事物：rollback； 真正使用了commit提交的時候才表示更新是可以正常完成的。所有的更新操作都需要被事務所保護。 事務鎖：每一個session都有自己進行的交易處理。如果兩個不同的session更新了同一個資料，在一個session未更新完成之前，另一個session是不能進行更新操作的。這個過程就叫做事務鎖：在提交或復原更新之前，只能有一個session能夠對資料進行操作，實現了事物的隔離性
3、交易處理的思想 案例：如果突然需要對所有使用者的某一個欄位的內容進行更新，在使用者資料量足夠大的情況下，那麼這樣就會出現一個問題： 如果直接更新，那麼資料量足夠大的情況下，需要的時間也是非常長的，且在更新的時間段，所有的使用者不能登入系統做其他的操作，相當於系統癱瘓。這種做法是拿時間換空間 如果換一種做法：用空間來換時間：將客戶分為活躍使用者與非活躍使用者，優先更新活躍使用者的資訊，或者在使用者進行登入的時間進行使用者操作，這是一個周期完成的事情，並不是一下子完成的。 所謂的解決方案都是取決於時間複雜度或者空間複雜度，要不就是拿時間換空間，要不就是拿空間換時間，具體的做法還需結合具體的情況進行分析，但是這是一個最基本的想法。三、資料偽列
1、行號 行號是自動產生的，但是他是動態，並不會特定的指定某些行，他是根據你查詢出來的資料進行自動行產生的。 範例：SELECT ROWNUM, empno, ename, job FROM emp;rownum的作用：1、取得第一行的資料2、取得前N行的資料注意：rownum不能直接取得後幾行或者中間部分的資料 1、取得第一行的資料一般是用來取得表的資料結構，一般在實際的操作中，我們不會使用查詢全部的語句進行操作：select * from 表名。SELECT * FROM emp WHERE ROWNUM=1；
2、實現上下翻頁顯示CurrentPage，表示的是當前所在頁；LineSize，表示每頁顯示的資料行；文法：SELECT *FROM( SELECT ROWNUM rn, 列.... FROM 表名稱 WHERE ROWNUM <= currentPage*lineSize) temp
WHERE temp.rn>(currentPage-1)*lineSize；
3、行ID 所謂的ROWID指的是針對每行資料提供的物理地址。以”AAAR3qAAEAAAACHAAA"這個資料為例，ROWID組成：資料對象編號：AAAR3q資料檔案編號：AAE資料儲存的塊號：AAAACH資料儲存的行號：AAA
面試題：表中有許多完全重複的資料，要求將重複的資料刪除掉（只剩最後一個）；分析：表中的資料列資訊幾乎都是一樣的，所以如果按照已有的欄位刪除，那麼最終的結果都會被刪除掉。那麼現在即便資料重複了，在Oracle裡面存在一個ROWID，它的物理儲存地址也是不可能一樣的。在程式之中都會設計累加的操作，所以理論上來講，最早儲存資料的ROWID的內容應該是最小的。如果要想確認最小，利用MIN（）函數完成。現在mydept表中的資料又重複，那麼可以採用分組，按照重複內容分組之後統計出最小的ROWID（最早的ROWID）。 第一步：尋找出最小的rowid的資料SELECT deptno, dname, loc, MIN(ROWID)FROM mydeptGROUP BY deptno, dname, loc; 第二步：刪除rowid不在這其中的資料DELETE FROM mydept WHERE ROWID NOT IN( SELECT MIN(ROWID) FROM mydept GROUP BY deptno, dname, loc);
四、表的建立與管理
1、常用的資料類型 1、字串：使用VARCHAR2描述（其他資料庫使用VARCHAR）,200個字以內的都使用此類型，例如：姓名、地址、郵遞區號、電話、性別；2、數字：在Oracle之中使用NUMBER來描述數字，如果描述小數使用“NUMBER（m,n）”，其中n位為小數位，而m-n為整數位，但是資料庫也考慮了程式人員的習慣；-整數，使用INT；-小數，使用FLOAT。3、日期：使用DATE是最為常見的做法，但是在Oracle裡面DATE裡麵包含有日期時間，可是其他的資料庫裡面DATE可能只是日期，DATETIME才是表示日期時間；4、大本文資料：使用CLOB描述，最多可以保持4G的文字資訊；5、大對象資料：使用BLOB，保持圖片、音樂、電影，文字，最多可以保持4G。
從實際的開發來說就使用VARCHAR2、NUMBER、DATE、CLOB。
2、表的建立 文法：CREATE TABLE 表名稱 ( 列名稱 類型 [DEFAULT 預設值], 列名稱 類型 [DEFAULT 預設值], 列名稱 類型 [DEFAULT 預設值], .... 列名稱 類型 [DEFAULT 預設值]); 範例：建立一張名為member的表，他有四個描述：mid、name、birthday、noteCREATE TABLE member( mid NUMBER , name VARCHAR2(20) DEFAULT ‘無名氏‘ , birethday DATE DEFAULT SYSDATE, note CLOB); 向表中插入資料：INSERT INTO member(mid,name,birethday,note)VALUES (1,‘張三‘,TO_DATE(‘1890-10-10‘,‘yyyy-mm-dd‘),‘網資料線‘);
3、表的基本操作：表複製、表刪除、閃回技術
複製表：CREATE TABLE 表名稱AS 子查詢； 可以將查詢出來的子表作為一張表的形式複製給一個新的表格儲存下來範例：將所有30部門僱員資訊儲存在emp30表中CREATE TABLE emp30 AS SELECT * FROM emp WHERE deptno=30; 刪除表：在正常的情況下，資料表一旦被刪除了，是無法恢複的，請慎用：DROP TABLE 表名；範例：刪除資料表DROP TABLE dept; 閃回技術：FlashBack的功能給予了使用者的後悔機會，但是現在如果使用者想要去操作這個資源回收筒，那麼使用者而言具備查看、恢複、情況、徹底刪除幾項操作。 1、刪除資料表之後查看資源回收筒：SHOW RECYCBLEBIN; 2、恢複刪除的表FLASHBACK TABLE 表名 TO BEFORE DROP; 徹底刪除表格操作：1、徹底刪除person表DROP TABLE person PURGE;2、清空資源回收筒PURGE RECYCLEBIN；
4、恢複表的指令碼 這個指令碼可以對資料庫進行快速的恢複。該指令碼包含有如下的幾個內容：1、刪除原有的資料表；2、重新建立新的資料表；3、建立測試資料；4、進行提交。 代碼實現：--刪除資料表DROP TABLE member PURGE;--情況資源回收筒PURGE RECYCLEBIN;--建立資料表CREATE TABLE member( mid NUMBER, name VARCHAR2(20));--測試資料INSERT INTO member(mid, name) VALUES (1, ‘張三‘);INSERT INTO member(mid, name) VALUES (2, ‘李四‘);--提交事物COMMIT; 五、約束的建立與管理
1、資料的條件 為了保證資料表中的資料的完整性，資料需要滿足若干條件後才可以進行操作，約束是一把雙刃劍，約束的確會保證資料的合法性後才進行儲存，如果在一張表中有過多的約束的話，那麼更新的速度就一定會慢，在開發過程中，有些驗證的操作（約束）可以在程式中完成： 六種約束： 資料類型 非空約束 唯一約束 主鍵約束 檢查約束 外鍵約束
2、非空約束 非空約束：指表中的某一欄位的內容不允許為空白，如果使用非空的約束，只需要在某列的後面，利用NOT NULL 聲明就可以了。 在建立表的時候，在該列的後面添加not null聲明 create table member ( id number , name varchar2(15) default ‘無名氏‘ NOT NULL, sex varchar2(5), bithday date default sysdate); ---表示name的欄位這塊不允許為空白
3、唯一約束 唯一約束：在某一列的內容不能出現重複的情況，如身份證、郵箱等，設定唯一約束只需要在該列後面添加 unique 欄位即可 create table member ( id number , name varchar2(15) default ‘無名氏‘ NOT NULL, email varchar2(5) UNIQUE, bithday date default sysdate); ---表示email 欄位的內容必須為唯一的，不可以出現重複的資訊 注意：在唯一性限制式出錯的時候，不會像非空約束那樣直接給出是哪列的資料出現了問題，在實際開發中，可使用簡寫的方式，對於出錯資訊進行提示，以方便修改：在建立約束的時候，使用簡寫 約束名稱_欄位 的形式進行顯示：CONSTRAINT uk_email UNIQUE(email) create table member ( id number , name varchar2(15) default ‘無名氏‘ NOT NULL, email varchar2(5) , bithday date default sysdate, CONSTRAINT uk_email UNIQUE(email)); ---使用uk_email 的簡寫方式，可以在錯誤的時候進行簡單的提示，可以快速的捕捉到問題列 注意2：null不在唯一約束的判斷範圍之內
4、主鍵約束（primary key ,pk） 主鍵約束= 非空約束+唯一約束；設定為主鍵的列，既不可為空也不能出現重複的資料 定義主鍵約束：create table member ( id number , name varchar2(15) default ‘無名氏‘ NOT NULL， CONSTRAINT pk_id primary key(id) ）；----實現主鍵約束，請使用簡寫的方式
5、檢查約束 ：check 、ck在資料列生設定一些過濾條件，當過濾條件滿足時才可以進行操作如年齡的設定：年齡範圍0-120設定檢查約束：create table member ( id number , name varchar2(15) default ‘無名氏‘ NOT NULL， age number（3）， CONSTRAINT ck_age check(age between 0 and 120) ）；---實現列的條件約束
6、外鍵約束 （foreign ，fk） 作用：在兩張表有關聯的時候，比如父表與字表的關聯時，必須設定關聯欄位的關係，否則會導致在父表中不存在的資料，在子表中也是可以同樣插入的 create table member（ mid number， name varchar2（20）， constraint pk_mid primary key(mid) ----設定主鍵） create table book（ bid number， title varchar2（20）， mid number constraint fk_mid foreign key(mid) references member（mid） on delete cascade ----設定book中的mid為外鍵，且資料來源於member表中的mid ）----on delete cascad 是設定串聯刪除
注意：外鍵的限制
1、在刪除父表之前，要先刪除掉她的對應的全部的子表之後才可以進行刪除
強制移除父表的操作，不關心子表是否存在：drop table 表名稱 cascade constraint ---盡量不要使用強制移除，盡量使用先刪除子表，再刪除父表
2、如果要作為子表外鍵的父表列，那麼這個列必須要設定為唯一約束或者主鍵約束
3、如果現在主表中的某一行資料有對應的子表資料，那麼需要先刪除子表中的資料才可以刪除父表中的資料 ,刪除父表之後，子表的資料也會刪除
4、設定為外鍵的欄位，在主表之中必須是主鍵或者是唯一約束。串聯刪除：on delete cascad ：在刪除父表的資料的時候，會自動刪除子表關聯的資料串聯更新： on delete set null 現在刪除父表資料的時候，將對應的子表的資料的外鍵列設定為null
七、修改約束 100%禁止修改約束必須在表建立的時候就應該要建立完整，不能進行約束修改。六、資料庫的設計
1、第一範式： 資料表中的每一列的內容不可再分； 注意點： 1、對於日期描述堅決不能拆分為：年、月、日 2、對于姓名的欄位不可分為fistname、lastname
1、第二範式（多對多關係） 資料表中不存在非關鍵字段對任意一候選關鍵字段的部分函數依賴。
2、第三範式：（一對多關聯性） 首要考慮 資料表之中不存在非關鍵字段對任意一候選關鍵字段傳遞函數依賴。 七、其他的部分
一、資料庫的備份 1、資料的匯出與匯入，以使用者為單位 資料表中的資料的匯出與匯入： 需要先準備一個資料備份的目錄，如：在cmd中輸入 md 檔案夾名稱建立檔案夾 進入該目錄下，以命令列的形式操作：輸入exp命令，匯出資料 進入該目錄下，以命令列的形式操作：輸入imp命令，匯入資料 2、資料庫的冷備份，歸檔備份，指資料庫需要關閉服務，所有的事務都需要提交了。 備份檔案：1、控制檔案：控制著整個oracle 的執行個體資訊，可以使用‘v$controlfile’資料字典找到2、重做記錄檔：通過‘v$logfile’資料字典找到3、資料檔案：通過‘V$datefile’資料字典找到4、核心設定檔（Pfile）：使用‘SHOWPARAMETE pflie’找到Database Backup操作需要使用管理員進行操作： 1、資料庫管理員登陸：CONN sys/change_on_install AS sysdba;2、找到以上幾份檔案的路徑：select * from v$controlfile;select * from v$logfile;select * from V$datefile;SHOW PARAMETER Pfile;記錄下以上檔案的路徑 3、關閉oracle 服務：shutdown immediate;4、拷貝出所有的備份檔案5、重啟服務startup；
2、資料庫的其他的對象 一、同義字（oracle的專屬功能）同義字的定義：如果在sys使用者下，去使用select * from emp；的文法查詢emp表，他會報該表或者視圖不存在的提示，原因是因為emp表是屬於scott使用者的，所以在其他使用者下如果需要訪問該表，就應該要添加scott.emp ，這樣的過程就叫做同義字。文法：create 【public】 SYNONYM 同義字名稱 for 模式.表名稱例：將scott.emp 映射為semp;create SYNONYM semp for scott.emp; 注意：在建立同義字的時候，如果沒有將其建立為公用同義字的話，那麼該同義字就只能在建立的使用者下使用，不能在其他的使用者下使用。 建立公用的同義字： create PUBLIC SYNONYM semp for scott.emp; 使用帳號密碼登陸： CONN SYS/change_on_install as sysdba; 刪除同義字： drop SYNONYM SEMP;
二、索引來源與作用 在資料庫中進行資料查詢的時候，我們可以開啟追蹤器對資料庫的分析過程進行分析 開啟追蹤器：set autotrace on; 需要在sys使用者下才可以查看 根據查看後的結果，分析到：在資料的執行過程中，比如簡單的select * from emp where sal>1500; 該語句的執行過程是對該表進行全盤掃描，如果在資料量足夠大的情況下，如果後半部分的資料已經不滿足條件了，但是該語句還是會對後續的資料進行查詢，這樣就會造成資源的浪費也會拉低效能； 因此我們採取先排序的方式對資料進行排序，這樣就可以避免後續的資源被浪費了，但是不能使用order by ，因為order by 的執行順序在最後，是對已經篩選好的資料進行排序； 二叉數的排序方式： 選擇一個資料作為根節點，比節點數大或者等於的資料放在右子數上，比節點小的資料放在左子數上。（右大左小） 使用索引的方式實現對資料的分類搜尋，以提高效能節約資源。
三、建立索引 1、建立引索就必須要設定一個指定的欄位： 例：為scott.emp 表的sal欄位建立索引 create INDEX emp_sal_ind ON scott.emp(sal); 2、再對select * from scott.emp where sal>1500;進行查詢，此時查詢的方式是採取索引的方式進行查詢且根據的是rowid ：TABLE ACCESS BY INDEX ROWID /INDEX RANGE SCAN
3、缺陷 索引的局限性：索引可以提升查詢的速度與效能，但是也有很多的局限性，比如：如果資料一直在更改過程中的話，那麼索引就不好用了。 樹的維護是需要花費大量的時間的，如果現在不想重複的對樹進行維護，那麼就需要保證資料的不可更改與唯一性，所以在預設情況下會在主鍵約束上自動加上一個索引 在現實過程中：我們既希望保證使用者的回應速度快，沒有延遲，又能夠承受住使用者的大量更新，那麼我們可以這樣處理: 犧牲即時性： 分成兩個資料庫，一個資料庫儲存使用者需要查詢的資料，一個資料庫用來儲存使用者的更新的操作，在淩晨2-3點時，再將更新資料庫的資料將資料更新到查詢庫中。
四、使用者管理（使用者的維護與許可權的劃分） 使用者登陸： CONN sys/change_on_install AS SYSDBA; 建立新的使用者dog /wangwang (使用者名稱/密碼): 注意密碼要以英文字母開頭 CREATE USER dog IDENTIFIED BY wangwang; 賦予許可權： GRANT 許可權1 ，許可權2..... to 使用者名稱 例:GRANT create session to dog ; 問題來了，但是一個全新的使用者是沒有任何許可權的，需要這個使用者可以正常使用的話，就必須要每種許可權都使用單獨賦予的語句，這樣太過麻煩，所以引入角色的概念，不同的角色有不同的很多的許可權，就不需要再單獨賦予許可權了，只需要賦予該使用者角色就可以了。 CONNECT:串連角色，CONNECT角色中包含RESOURCE許可權 RESOURCE:訪問操作（訪問表及資料表空間） 角色賦予： GRANT CONNECT , RESOURCE TO dog; 使用者得到新的許可權後必須要重新登入後，才可以擷取新的許可權。登入操作並不是只是簡單的驗證使用者名稱與密碼，而是需要進行許可權的擷取與驗證。 修改使用者的密碼： ALTER USER 使用者名稱 IDENTIFIED BY 密碼 讓使用者的密碼到期： ALTER USER 使用者名稱 PASSWORD EXPIRE;---使用這條指令後，會讓使用者自己輸入新的密碼，從而進行密碼的更改。 鎖定使用者：ALTER USER 使用者名稱 ACCOUNT LOCK; 解鎖使用者：ALTER USER 使用者名稱 ACCOUNT UNLOCK;除了系統許可權之外，還需要使用對象的許可權：可以針對一個對象下的資料表進行訪問：四種許可權：INSERT/UPDATE/DELETE/SELECT 例：將scott.emp 的表的select 、insert許可權賦予dog使用者： GRANT SELECT, INSERT on scott.emp TO dog; 許可權的回收 REVOKE SELECT, INSERT on scott.emp FROM dog; 刪除使用者操作：
DROP USER 使用者名稱 CASCADE
3、序列一、自動成長列 序列 文法： create sequence 序列名稱 【MAXVALUE 最大值 | NOMAXVALUE】預設最大值：1.0000E+28 【MINVALUE 最小值 | NOMINVALUE】預設最小值為1 【INCREMENTBY 步長 】預設步長為1 【START WITH 開始值】 【CYCLE|NOCYCLE】預設為N，迴圈 【CACHE 緩衝個數|NOCACHE】預設為20個？這個好像是更改的，後續說 序列屬於資料庫物件的建立過程，屬於DDL的分類範疇，對於序列的而言，建立之後一定會在資料字典中儲存 例：CREATE SEQUENCE myseq; nextval ：取得序列的下一個內容，每一次調用序列的值都會增長 currval：表示取得序列的當前內容，每次調用序列都不會增長，就相當於顯示當前的序列 如果第一次沒有使用nextval取得值，那麼使用currval是不能顯示當前的資料，就像相當於沒有值 二、定義特殊的序列 建立一個表：create table mytab（ id number， name varchar2(20), constraint pk_id primary key (id)）; 建立表之後可以根據序列讓id進行自動成長：insert into mytab (id , name ) values (myseq.nextval,‘hello‘); 緩衝的作用：緩衝的作用是為了提高效能的，先準備資料，之後就可以直接使用了，但是在使用的過程中，會出現丟號的情況，該問題無法解決。 select myseq.nextval from dual; ---從虛擬表中尋找序列select sequence_name, cache_size ,last_number from user_sequences;---在資料字典中尋找相關的資訊 drop sequence myseq ;----刪除該序列create sequence myseq；---建立該序列INCREMENTBY 2；---設定步長為2START WITH 1000；---改變序列的開始值CYCLE；---設定迴圈 注意：last_number的值是等於步長*緩衝數量的 cache 的值必須小於cycle的值：緩衝的個數必須小於迴圈的值
4、視圖的定義與使用 一、視圖的建立 概念：利用視圖可以實現複雜的sql的語句的封裝操作視圖屬於DDL的定義範疇，文法如下：create【or replace】 view 視圖名稱 as 子查詢例：---給scott使用者建立視圖的許可權：CONN sys/change_on_install AS SYSDBA;GRANT CREATE VIEW TO scott;CONN SCOTT/TIGER; ---建立視圖:將emp表中的部門為10的人員資訊封裝成視圖create view myview as select * from emp where deptno=10; 視圖可以像普通的資料表那樣進行直接的查詢：select * from myview；---查詢的結果與之前的子查詢的結果是一樣的 視圖很少用drop語句進行刪除，因為在刪除與建立的過程中，會存在時間間隔，會導致存在問題，所以如果遇到視圖存在的問題，我們採取替換掉的方式將其進行更新： create or replace view myview as select * from emp where deptno=10;---如果視圖不存在，則建立，如果存在，則替換更新
二、視圖的更新操作 視圖中只是包含查詢語句的臨時資料，並不是真實存在，但是預設情況下，視圖是可以進行直接修改的create or replace view myview as select * from emp where deptno=10;更新視圖的部門編號：update myview set deptno=30 where empno=7369； 注意：1、保護建立條件不被更改：在視圖中更新的資料，同時也是會在原始的emp的資料同時更新：這樣的操作是不合理的，所以為了防止更改視圖導致資料的更新操作的情況，在建立視圖的時候使用 with check option 的子句，防止視圖的建立條件不被更改。create or replace view myview as select * from emp where deptno=10 with check option ; 2、視圖不可以修改除建立條件外的其他欄位，在建立視圖的時候，是屬於資料的映射，那麼本質上應該建立一個唯讀視圖，以保護資料不被修改
create or replace view myview as select * from emp where deptno=10 with read only
oracle中的資料集合操作
