Oracle、mysql資料庫增、刪、改

一、Oracle體系結構
1、概念關係
資料庫與例項：Oracle只有一個資料庫，但可以有多個例項
Oracle資料庫與例項一對多，一般情況下只有一個例項，我們平常說的Oracle資料庫名，其實是Oracle的例項名
例項與使用者一對多
例項與表空間一對多
表空間與使用者一對多
2、Oracle結構
物理結構：資料檔案(xxx.dbf、xxx.ora)
邏輯結構：資料庫 表空間 段 區 Oracle資料塊

二、Oracle實用命令及語句
在物理機的dos視窗輸入：sqlplus system/password@192.168.25.128:1521/orcl
查詢所有表空間：select tablespace_name from dba_tablespaces;
查詢所有角色：select * from dba_sys_privs;
檢視所有使用者：select * from all_users;
檢視當前使用者：show user
透過scott登入可以做測試：sqlplus scott/tiger@192.168.25.128:1521/orcl
賬號鎖定報錯：ERROR:ORA-28000: the account is locked
解決：重新用system登入，執行alter user scott account unlock;給scott使用者解鎖
密碼過期報錯：ERROR:ORA-28001: the password has expired
解決：重新輸入密碼tiger即可
檢視當前使用者下的所有表：select table_name from user_tables;
檢視錶結構：desc emp
清屏：host cls
設定行寬：set linesize 150
顯示行寬：show linesize
設定列寬：col ename for a8（8個字元的寬度）
設定每頁多少條資料：set pagesize 20
顯示每頁多少條資料：show pagesize
將命令列的語句寫入到指定的目下的指定的檔案中：
指定寫到哪個目錄的哪個檔案上：spool d:\1.text
寫入完畢：spool off
編輯之前輸入過的命令：
輸入ed，然後回車
該命令主要用在當前一個sql語句寫錯時，又不想重新寫，這個時候可以用ed命令調出記事本
對先前的命令進行編輯（如果該命令用在Linux系統中，調出來的是vi編輯器）
執行之前快取過的命令：/

三、建立表空間+建立使用者+系統管理員授權
1、建立表空間tablespace
create tablespace waterbos
datafile 'c:\waterbos.dbf'
size 100m
autoextend on
next 10m;

2、建立使用者user
    create user xuming
    identified by itcast    default tablespace waterbos;
    3、給建立的新使用者授權grant
    ORA-01045: user lacks CREATE SESSION privilege; logon denied
    解決辦法：系統管理員給新使用者授權：
    grant create session,resource to 使用者名稱; 等同於grant connect,resource to 使用者名稱;
    grant dba to 使用者名稱;
    
    張三        超級管理員      對A資源的增加
    李四        普通管理員      對A資源的刪除
    王五        專案經理        對A資源的修改
    趙六        產品經理        對A資源的查詢
    錢七      技術顧問        對B資源的查詢

四、建立表+維護表結構
DDL：Data Definition Language 資料定義語言 如：create、alter、drop、truncate
DML：Data Manipulation Language 資料操縱語言 如：select、update、delete、insert
DCL:Data Control Language 資料控制語言 如：grant、revoke
1、oracle常用資料型別
char,varchar2,long,number,date,clob,blob

2、建立表+約束    1）基本建立表語句
        create table myemp3(
            id number(10),
            name varchar2(10)  constraint myemp3_name_nn not null,--非空約束
            gender varchar2(4) default '男',--預設值
            deptno NUMBER(2),
            email varchar2(20),
            constraint myemp3_id_pk primary key(id), --主鍵約束
            constraint myemp3_gender_ck check(gender in('男','女')), --檢查約束
            constraint myemp3_email_uk unique(email), --唯一約束
            constraint myemp3_deptno_fk foreign key(deptno) references dept(deptno) on delete set null --外來鍵約束            -- 沒有這種寫法constraint myemp3_name_nn not null
        );
        
    2）使用子查詢建立表
        create table emp2 as select * from emp where 1=2;
        由於1=2為假，該語句只複製了結構，沒有複製資料           
3、修改表    1）修改表中列  
        oracle: alter table myemp3 modify ename vachar2(20); 不能帶column
        mysql : alter table myemp3 modify (column) ename varchar(20);
        
    2）增加表中列  
        oracle: alter table myemp3 add gender varchar2(4);
        mysql : alter table myemp3 add (column) gender varchar(4);
        
    3）刪除表中列  
        oracle: alter table myemp3 drop column gender; 必須帶column
        mysql : alter table myemp3 drop (column) gender;
        
    4）重新命名錶中列名 
        oracle: alter table myemp3 rename column ename to myname; 必須帶column
        mysql : alter table myemp3 change (column) ename myname varchar(20);
        
    5）重新命名錶名 
        oracle: rename myemp3 to myemp4;
        mysql : rename table mysqltname3 to mysqltname4; 必須帶table        
4、刪除表    1）徹底刪除表
        drop table myemp3 purge; 不加purge是暫時放到回收站中了        
    2）drop之後的資料放到了recyclebin回收站中
        檢視回收站：show recyclebin;
        徹底清除回收站：purge recyclebin;
        徹底刪除表：drop table myemp3 purge;
        檢視回收站表資料： select * from "BIN$cd/KYgV5RY6/RxGTjn2Skg==$0";
        
    3）閃回
        flashback table myemp3 to before drop;

五、對錶資料操作（insert,update,delete）
1）向表中插入資料(insert into values)
insert into t_owners values(2,'張三',1,'3-3','7895',sysdate,1);
insert into t_owners2 select * from t_owners;
insert into 表名(id,name) values(xx,yy),(xx1,yy1) （mysql中可以這樣寫,oracle不能這樣寫）
向多張表中插入資料：insert all into 表名(id,name) values(xx1,yy1) into 表名(id,name) values(xx2,yy2);

2）修改表中資料(update set)
    update 表名 set dateddd=dateadd-3,col1=col1+1;
    3）刪除表中資料
    使用delete刪除資料   delete from myemp where empno=7369;
    使用truncate 刪除整張表資料   truncate table myemp;功能上與delete from myemp; 相同    delete和truncate 刪除資料的區別：
        (1)delete可以回滾，truncate不可以
        (2)truncate比delete效率要高    實際企業開發中刪除資料庫中資料注意點：
        (1)先把要刪除的資料備份
        (2)確認用於刪除的sql語句無誤
        (3)儘量選擇能夠回滾資料的方式delete
        (4)drop > truncate > delete

六、匯入和匯出
1）整庫匯入匯出
exp system/itcast full=y
exp system/itcast full=y file=abc.dmp
imp system/itcast full=y
imp system/itcast full=y file=abc.dmp

2）按使用者匯入與匯出
    exp system/itcast owner=xuming file=abc.dmp
    imp system/itcast file=abc.dmp fromuser=xuming    
3）按表匯入匯出
    exp xuming/itcast file=a.dmp tables=t_owners;
    imp xuming/itcast file=a.dmp tables=t_owners;
    4）mysql匯入匯出
    第一種匯入資料：
    mysql -uroot -proot
    mysql>create database crm;
    mysql>use crm;
    mysql>source d:/crm.sql
    第二種匯入資料：
    先建立好資料庫crm，執行
    mysql -uroot -proot crm < d:\crm.sql
    mysql匯出資料
    mysqldump -uroot -proot crm > d:\crm1.sql

七、總結oracle與mysql在專案中使用區別
1、匯入jar包不同
oracle：ojdbc14.jar 版本可能會變
mysql：mysql-connector-java-5.1.7-bin.jar 版本可能會變
注意：不同專案，jar包版本會有區別
2、jdbc獲取連線寫法
oracle：Connection connection = DriverManager.getConnection(" jdbc:oracle:thin:@192.168.25.128:1521:orcl", "xuming","itcast");
mysql：Connection connection = DriverManager.getConnection("jdbc: ", "root", "root");
3、配置檔案
# oracle jdbc properties
jdbc.url = jdbc:oracle:thin:@localhost:1521:xe
jdbc.driver= oracle.jdbc.driver.OracleDriver
jdbc.user = bos
jdbc.password = bos

    #mysql jdbc properties
    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/bos?characterEncoding=utf-8
    jdbc.user=root
    jdbc.password=root

=========================================================================

一、單表查詢
完整語句：select from where group by having order by
1、簡單條件查詢
=,!=,>,>=,<,<=
like
and 和or 邏輯運算，and的優先順序大於or
between and:小值在前，大值在後；左右兩邊都包括邊界
is null/is not null：在mysql和oracle中沒有=null和!=null
not like,not between and,not in
2、去重：select distinct deptno,mgr保證deptno和mgr兩個列組成的值的唯一性
3、排序
order by sal,deptno desc;
desc只能對deptno進行降序排列
底層實現的順序：首先按照sal升序排列，然後如果有相同的sal，再按照deptno進行降序排列。
4、偽列rowid和rownum
rowid:每一行的實體地址 rownum:每一行排序的序號
select rowid,rownum,empno,ename from emp;
ROWID ROWNUM EMPNO ENAME
------------------ ---------- ---------- ------
AAAMfPAAEAAAAAgAAA 1 7369 SMITH
AAAMfPAAEAAAAAgAAB 2 7499 ALLEN
AAAMfPAAEAAAAAgAAC 3 7521 WARD
AAAMfPAAEAAAAAgAAD 4 7566 JONES
AAAMfPAAEAAAAAgAAE 5 7654 MARTIN
AAAMfPAAEAAAAAgAAF 6 7698 BLAKE
AAAMfPAAEAAAAAgAAG 7 7782 CLARK
AAAMfPAAEAAAAAgAAH 8 7788 SCOTT
AAAMfPAAEAAAAAgAAI 9 7839 KING
AAAMfPAAEAAAAAgAAJ 10 7844 TURNER
AAAMfPAAEAAAAAgAAK 11 7876 ADAMS

5、聚合函式：min(),max(),avg(),sum(),count(),其中count(對於null值行直接濾過)
    group by 分組    select deptno,max(sal) from emp group by deptno;//這裡的deptno為非聚合函式的列，所有需要參與到分組中去
    select deptno,job,max(sal) from emp group by deptno,job;//多列分組，首先按照deptno分組，相同的組再按照job分組
    select empno,deptno,sum(sal) from emp group by empno,deptno;
    聚合函式group by使用having來過濾分組後的結果，
    普通的列使用where過濾    
6、別名：select ename as "員工姓名",sal 工資 from emp;
    as可以加，也可以省略，如果別名包含了特殊的關鍵詞必須加上""；否則可以不用加""
    oracle中字串和日期都是單引號，只有別名才是雙引號。

二、多表查詢
只要兩個表能夠建立關聯關係，兩個表都能夠聯合查詢
1、內連線：在笛卡爾集上選擇了滿足on條件的記錄行，連線的是兩個相同的列的值
（笛卡爾集：記錄條數是多個表記錄乘積，列數是多個表的列之和）
顯式內連線：select * from emp e inner join dept d on e.deptno=d.deptno;
隱式內連線：select * from emp e , dept d where e.deptno=d.deptno;

2、外連線：左外連線left join on/right join on  
    遵循的SQL99語法    select * from emp left join dept on emp.deptno=dept.deptno;
    oracle中(+)        select * from emp where emp.deptno=dept.deptno(+);
    select d.deptno 部門編號,d.dname 部門名稱,count(e.empno) 部門總人數 from emp e, dept d  where e.deptno(+)=d.deptno 
group by d.deptno,d.dname order by d.deptno;

三、子查詢
1、子查詢寫法
where後的子查詢：先查子查詢，子查詢的結果作為主查詢的過濾條件
select ename from emp e where e.deptno in
(select dept.deptno from dept where deptno>10);
from後的子查詢：先查子查詢，子查詢的結果，可以看成一張表，被主查詢查
select t.部門總人數 from (select d.deptno 部門編號,d.dname 部門名稱,
count(e.empno) 部門總人數 from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname order by d.deptno) t;
select後的子查詢：先查主查詢，主查詢的結果作為子查詢的引數，最後再查主查詢
select ename 員工編號,
(select dname from dept where dept.deptno=emp.deptno) 部門名稱 from emp;
注意：select後面的子查詢，一定是單行子查詢（只返回一條結果記錄）

2、子查詢運算子    單行子查詢使用(=,<>,>,>=,<,<=)，多行子查詢使用單行運算子和(in(),any(),all())

四、分頁查詢
1、rownum
一張表不經過任何操作預設帶有rownum行號,經過排序操作之後，該行號也隨著排序了，但不是從1-2-3排序的
為了重新按照1-2-3排序，我們把rownum當成列來使用而不是行號
2、可以提煉成公式：
頁碼pageNo=1 每頁條數pageSize=5
select * from
(select rownum r,t.* from
(select * from emp order by sal desc) t
where rownum <=pageNo pageSize)
where r>(pageNo-1)pageSize;

    select * from emp order by sal desc limit 0,5;
    select * from emp order by sal desc limit (pageNo-1)*pageSize,pageSize;

五、函式：任何一個函式都有返回值 （只作瞭解，把握可以處理哪幾種資料，以及處理資料功能）
參考"oracle函式大全(分類顯示).chm"
字元函式
length(),concat()和||,substr()

數值函式round() trunc()select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五 from dual;日期函式
sysdate  systimestampnext_day(,'星期一')last_day()trunc()months_between()add_months()轉換函式to_char() 可以轉換成日期或者指定格式to_number() 字串轉換成數字to_date() 字串轉換日期
其他函式
關於null的函式：nvl(a,b)    a為null，則返回bnvl2(a,b,c) a為null,則返回c,否則返回b
在mysql中nullif(a,b) a=b，則返回null,否則返回a
在mysql中使用ifnull(a,b) 如果a為null ,則返回b
條件語句函式（重要）case when then else end 
decode(ename,'',,,,)分析排名函式()rank() over(order by xxx )       相同的值排名相同，排名跳躍DENSE_RANK() over(order by xxx)  相同的值排名相同，排名連續ROW_NUMBER() over(order by xxx)  連續的排名，無論值是否相等

六、集合操作
union 去重,union all不去重
集合參與運算(並集，交集，差集)，筆記中圖案的黃色部分為查詢結果
差集：a minus b a-（a和b的交集）
集合操作必須滿足如下原則：
1、兩個集合的列數要一致
2、兩個集合中的列要順序相同，對應型別相同
order by放在最後；
select ename,sal from emp
union all
select sal,ename from emp; 會報錯
=========================================================================

一、檢視
1、普通檢視（複雜查詢結果放到一張虛擬表中,對檢視的操作其實是構成檢視基表操作）
建立檢視：create or replace view myview_view as select * from myemp3;
刪除檢視：drop view myview;
理解：檢視一般不推薦做插入、修改操作，推薦做查詢，
因為如果遇到一些特殊語句，insert,update 操作就不行，
企業開發中用的最多的是with read only檢視

2、物化檢視（能夠說出來，物化檢視是怎麼一個原理）
    建立檢視時是否生成資料：BUILD IMMEDIATE（預設，建立即生成）,BUILD DEFERRED
    重新整理方法有三種： FAST（增量重新整理）,COMPLETE（全量重新整理） ,FORCE（預設強制重新整理）
    重新整理的模式有兩種：ON DEMAND（預設，手動重新整理） 和 ON COMMIT（自動重新整理）
    理解：物化檢視儲存基於基表的資料，也可以稱為快照，可以理解成一種特殊的表。
     
    刪除物化檢視：drop materialized view myemp3_materialized;
    3、  普通檢視與物化檢視的異同    1）相同：都指向一段sql語句    2）不同：普通檢視相當於虛擬表；物化檢視會真正生成一張特殊的表    3）查詢物化檢視和查詢表的效率一樣    
4、增量重新整理的物化檢視    1）增量重新整理跟全量重新整理的區別？提示：下載app    2）建立物化檢視日誌，記錄基表資料的變化：insert、update、delete
    3）建立物化檢視的sql語句裡面（as後面）一定要有rowid，
        物化檢視日誌表的rowid和物化檢視表的rowid做比較

二、序列（主鍵自增使用）
建立序列：create sequence myemp3_seq;
刪除序列：drop sequence myemp3_seq;
使用序列：在insert into myemp3 values(myemp3_seq.nextval,'張三','男',10,' 101001@qq.com');
獲取當前值使用myemp3_seq.currval
注意：myemp3_seq.nextval 每呼叫一次nextval 指標向後移動一位，也就是該序列增加一次；

create sequence seq_test999
increment by 10start with 1minvalue 0maxvalue 190cycle;會報錯：CACHE值必須小於CYCLE值（因為一次CACHE的值有重複的會出錯）-- cache值：預設20 指20個數 
-- cycle值：ceil((maxvalue-minvalue)/abs(increment))-- CACHE值必須小於等於CYCLE值
需要滿足一個公式：cache <= ceil((maxvalue-minvalue)/abs(increment))
       1 11 21 31 ... 191 20個數
cache  1 11 21 31 ... 191 1 11 21 ...191場景：發訊息 1、2、3、4、5、6、720 <= ceil((190-0)/10)改成
create sequence seq_test999
increment by 10start with 1minvalue 0maxvalue 191cycle;

三、同義詞(給其他物件取一個別名，方便其他使用者呼叫和縮寫物件名稱)
建立同義詞：create synonym mysys_synonym for myemp3; for 可以是其他物件
刪除同義詞：drop synonym mysys_synonym;

四、索引（提高檢索速度）
建立索引：create index myemp3_index on myemp3(name,email) 聯合(複合)索引
刪除索引：drop index myemp3_index;
建立索引的場景：
該列值很多（覆蓋很多條記錄），經常被查詢，例如where ename="",where A.xx=B.xx
如果該列值經常做update操作不適合建索引
=========================================================================

一、plsql的基本結構和變數宣告
1、基本結構
declare
--宣告變數
begin
--plsql體執行其他操作
exception --有異常捕獲處理可以加上該關鍵字
--異常處理
end;

2、變數宣告：（常量、普通變數、引用普通型別變數、引用行型別變數、異常型別變數、遊標型別變數）set serveroutput on  --注意這裡使用sqldeveloper 工具需要將控制檯輸出開啟一次就行DECLARE
    id constant number(2):=1;--使用constant 關鍵詞定義常量
    name VARCHAR2(10):='悟空';--定義指定型別變數
    mysal emp.sal%type;--定義引用存在表的列型別
    myrow emp%rowtype;--定義引用存在錶行型別，相當於java中物件，該行型別包含所有列   select * into v_account from xx
    no_data exception;--異常定義
    cursor c1 is select sal from emp;--遊標定義，該c1中相當於java中集合，給
    cursor c2(dno number) is select sal from emp where  deptno=dno;---帶參遊標定義，該c2相當於java中帶泛型集合BEGIN
    raise no_data;exception
    when no_data then xx;
    when others then  xx;
    --常量就不能再賦值了 id:=2 錯誤    SELECT sal INTO mysal FROM emp WHERE empno=7369;
    SELECT * INTO myrow FROM emp WHERE empno=7369;
    dbms_output.put_line(id);
    dbms_output.put_line(name);
    dbms_output.put_line(mysal);
    dbms_output.put_line(myrow.ename || '   ' || myrow.sal);END;

二、條件判斷語句
1、select case when then when then else end
2、select decode()
3、if then elsif then else end if;
if xx then xx
elsif xx then xx --注意不能寫成elseif
elsif xx then xx
else xx
end if;---注意加上分號

三、迴圈語句
第1種：
for i in 1..100 --退出迴圈是根據迴圈次數來定的
loop
end loop;
遊標使用for in格式更簡單
for 不需要定義任意變數名稱 in c1;
loop
end loop;
第2種：
while --此處while後面宣告退出迴圈條件
loop
end loop;
第3種：
open c1; ---此處開啟遊標
loop
fetch c1 into xx;
exit when c1%notfound; --此處exit when 宣告退出迴圈條件
end loop;
close c1; --此處關閉遊標

四、儲存過程
第1種建立方式：不帶引數
create or replace procedure mypro1 as
--這裡可以宣告變數
begin
end;
第2種建立方式：帶in和out引數，引數個數不限制
create or replace procedure mypro2(eno in emp.empno%type,name out varchar2) as
--這裡可以宣告變數
myrow emp%rowtype;
begin
select * into myrow from emp where empno=eno;
name:=myrow.ename;
end;
plsql呼叫儲存過程：
execute mypro1;---對於無引數儲存過程可以這樣呼叫
或者
call mypro1(xxx)--對於只有in 引數可以這樣呼叫
或者
declare --儲存函式和儲存過程都可以這樣呼叫
name varchar2;
begin
mypro2(7369,name);---此處宣告一個變數用於接收out引數輸出
end;

五、儲存函式
1、建立儲存函式：
create or replace function myfn1(eno in emp.empno%type) return varchar2 as
--實際return name 的name型別一定要和定義的return varchar2要一致
--這裡可以宣告變數
myrow emp%rowtype;
name varchar2;
begin
select * into myrow from emp where empno=eno;
name:=myrow.ename;
return name;
end;
2、plsql呼叫儲存函式：
select myfn1(7369) from dual;
或者
declare
name varchar2;--此處要宣告一個變數用於接收返回的結果
begin
name:=mypro2(7369);
dbms_output.put_line(ename);
end;

六、java api 呼叫儲存過程和儲存函式
DriverManager.getConncetion() -> Connection ->CallableStatement prepareCall
-> 設定傳入引數直接透過設定型別setInt() ->
設定傳出引數，需要registerOutParameter
CallableStatement execute();

CallableStatement呼叫儲存過程和儲存函式的語句{call mypro1(?,?)}  呼叫儲存過程{?=call myfn1(?)}   呼叫儲存函式

七、觸發器
1、前置與後置
前置觸發器：sql語句commit之前
後置觸發器：sql語句commit之後
2、從影響行數來看
insert into emp() values() 插入了3條記錄
行級觸發器：觸發3次
表級觸發器（或者說語句觸發器）：觸發了1次
3、語法
create or replace trigger 觸發器名
before|after
delete|update|insert
on 表名
for each row--行級觸發器
begin
pl/sql語句
end;

====================================================================================================================================

oracle總結：
一、資料庫基本功底(ddl+dml（insert/update/delete）+單表+多表)
基本語句：select from where group by having order by
多表：where連線內連線+on連線的外連線
jdbc操作相關：
Statement/PreparedStatement/CallableStatement
con.prepareCall("儲存函式和儲存過程")
mysql連線語法：jdbc:
oracle連線語法： jdbc:oracle:thin:@192.168.25.128:1521:orcl

二、資料庫最佳化
1、sql語句最佳化
1）查詢的時候儘量使用列名；如：select empno,所有列名寫出來 from emp;
2）分組查詢有聚合函式,如果使用過濾，使用having
普通查詢帶條件，使用where
3）在子查詢和多表查詢之間選擇，儘量使用多表查詢
4）在集合運算中，如果union 和union all都可以，考慮使用union all因為union需要去掉重複的
5）能夠不要使用集合運算就不要使用集合運算
6）對於多個條件連線，可以將false的放在and最右邊，可以將true的條件放在or的最右邊

2、索引最佳化
    建立索引：單個列建立索引以及多個列上建立索引（複雜或者聯合索引）
    怎麼建立索引：建立索引的兩個場景需要斟酌
    1）建立索引：在某一個列上建立索引，必須考慮到該列的值是否覆蓋更廣，並且查詢很頻繁
    2）不建立索引：某一個列經常被改變，就不要建立索引

三、實際中常用的：
DML insert+update+delete+select
DDL create alter drop truncate