這篇文章主要講解了“Oracle和MySQL數據庫增、刪、改的方法是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“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: mysql://localhost:3306/mybatis?characterEncoding=utf-8", "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 <=pageNopageSize)
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: mysql://localhost:3306/mybatis?characterEncoding=utf-8
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

感謝各位的閱讀，以上就是“Oracle和mysql數據庫增、刪、改的方法是什么”的內容了，經過本文的學習后，相信大家對Oracle和mysql數據庫增、刪、改的方法是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創新互聯，小編將為大家推送更多相關知識點的文章，歡迎關注！