柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù)基礎(chǔ)教程(Oracle)

http://yzkb.51969.com/

1 數(shù)據(jù)庫(kù)

1.1 什么是數(shù)據(jù)庫(kù)？

車(chē)庫(kù)：放汽車(chē)的倉(cāng)庫(kù)，實(shí)體化米庫(kù)：放大米的倉(cāng)庫(kù)，實(shí)體化數(shù)據(jù)庫(kù)：放數(shù)據(jù)的倉(cāng)庫(kù)，數(shù)據(jù)化

我們口中的數(shù)據(jù)庫(kù)：

是數(shù)據(jù)庫(kù)管理系統(tǒng)和數(shù)據(jù)庫(kù)組成倉(cāng)庫(kù)管理系統(tǒng)和倉(cāng)庫(kù)

1. 1.1 數(shù)據(jù)的發(fā)展歷史

生活中的存放數(shù)據(jù)的方式

筆記本、手機(jī)、竹筒、計(jì)算機(jī)文檔、表格、數(shù)據(jù)庫(kù)

手工管理：容易丟、修改困難、數(shù)據(jù)量多不好處理、不方便查找文檔文件：使用方便、什么都要面面俱到、電腦不能壞、查找不方便數(shù)據(jù)庫(kù)：彌補(bǔ)了大部分缺點(diǎn)，對(duì)使用人群有限制

優(yōu)勢(shì)：持久化，數(shù)據(jù)共享，數(shù)據(jù)一致性，數(shù)據(jù)安全性，高效性（SQL）

1.2 數(shù)據(jù)庫(kù)的特性

原子性：原子性確保一個(gè)事務(wù)被視為一個(gè)不可分割的工作單元。這意味著要么事務(wù)中的所有更改都提交到數(shù)據(jù)庫(kù)，要么都不提交。如果事務(wù)的任何部分失敗，整個(gè)事務(wù)都將回滾，數(shù)據(jù)庫(kù)恢復(fù)到之前的狀態(tài)。一致性：一致性確保事務(wù)將數(shù)據(jù)庫(kù)從一個(gè)有效狀態(tài)轉(zhuǎn)換為另一個(gè)有效狀態(tài)。它強(qiáng)制執(zhí)行對(duì)數(shù)據(jù)庫(kù)定義的完整性約束和規(guī)則，以使數(shù)據(jù)庫(kù)在事務(wù)之前和之后保持一致的狀態(tài)。隔離性：隔離性確保并發(fā)事務(wù)不會(huì)相互干擾。每個(gè)事務(wù)都像在隔離環(huán)境中執(zhí)行一樣，即使可能有多個(gè)事務(wù)同時(shí)執(zhí)行。隔離性防止出現(xiàn)臟讀、不可重復(fù)讀和幻讀等問(wèn)題。持久性：持久性保證一旦事務(wù)提交并將更改保存到數(shù)據(jù)庫(kù)中，即使系統(tǒng)發(fā)生故障（如斷電或崩潰），更改也會(huì)持久存在。更改變得永久且無(wú)法撤銷(xiāo)。

1.2.1 數(shù)據(jù)庫(kù)表之間的關(guān)系

一對(duì)一：每個(gè)員工只有一個(gè)工作時(shí)間一對(duì)多：一個(gè)顧客ID可以對(duì)應(yīng)很多個(gè)訂單ID，但是一個(gè)訂單只能有一個(gè)顧客多對(duì)多：很多菜可以對(duì)應(yīng)一個(gè)顧客，很多顧客可以對(duì)應(yīng)很多菜

1.2.2 數(shù)據(jù)庫(kù)的分類(lèi)

關(guān)系型數(shù)據(jù)庫(kù)：采用關(guān)系模型來(lái)組織數(shù)據(jù)的，處理簡(jiǎn)單的文字?jǐn)?shù)據(jù)，可以用表來(lái)存儲(chǔ)并且可以直觀表達(dá)的

常見(jiàn)的：Oracle、MySQL、SQL serve...

非關(guān)系型數(shù)據(jù)庫(kù)：主要針對(duì)的是高可用、高擴(kuò)展、性能，處理這種高并發(fā)的數(shù)據(jù)、表內(nèi)存儲(chǔ)不了的，視頻、音頻、圖片...

常見(jiàn)的：Mong DB、DB2、Redius...

關(guān)系型數(shù)據(jù)庫(kù)相當(dāng)于非關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)：

數(shù)據(jù)一致性：使用ACID來(lái)管理數(shù)據(jù)，保證了數(shù)據(jù)的完整性結(jié)構(gòu)化：采用的是表格化的方式來(lái)管理數(shù)據(jù)，結(jié)構(gòu)清晰可靠性：有更成熟的查詢語(yǔ)句(SQL)

1.2.3 什么是SQL？

是一個(gè)編程語(yǔ)言，可以用來(lái)增刪改查數(shù)據(jù)庫(kù)，是一種標(biāo)準(zhǔn)化語(yǔ)言

分類(lèi)：

DQL：數(shù)據(jù)查詢語(yǔ)句，查詢數(shù)據(jù)，關(guān)鍵詞：selectDML：數(shù)據(jù)操作語(yǔ)句，增刪改數(shù)據(jù)，關(guān)鍵詞：insert、update、deleteDDL：數(shù)據(jù)定義語(yǔ)句，創(chuàng)建數(shù)據(jù)庫(kù)的表、索引、視圖、游標(biāo)、序列，關(guān)鍵詞：create、define、alter、dropDCL：數(shù)據(jù)控制語(yǔ)句，用于對(duì)用戶的授權(quán)，關(guān)鍵詞：grant、alter、revokeTCL：事務(wù)控制語(yǔ)句，用于控制事務(wù)的提交、回滾等等，關(guān)鍵詞：commit、rollback

1.2.4 Oracle

1.2.4.1 什么是Oracle

甲骨文，是一個(gè)數(shù)據(jù)庫(kù)

版本：“”

Oracle 7：支持分布式數(shù)據(jù)庫(kù)和存儲(chǔ)過(guò)程O(píng)racle 8：支持Java、物理化視圖、分區(qū)表Oracle 9i（Internet）：支持XML、RACOracle 10g（grocery）：新增手動(dòng)調(diào)優(yōu)Oracle 11g：新增自動(dòng)調(diào)優(yōu)Oracle 12c（cloud）：新增存儲(chǔ)序列，并且支持云端

1.3 數(shù)據(jù)庫(kù)安裝

1.3.1 上傳解壓工具7z

上傳完安裝

1.3.2 上傳Oracle10ge安裝文件

上傳完成利用7z解壓

?1.3.3 找到安裝文件開(kāi)始安裝

1.3. 4 開(kāi)始安裝

?

?不用管這個(gè)頁(yè)面

?

1.3.5? 在2003里cmd測(cè)試連接

sqlplus / as sysdba

1.3.6?查看數(shù)據(jù)庫(kù)服務(wù)名

?select instance_name from v$instance

?解壓遠(yuǎn)程連接工具，路徑不能有中文和特殊符號(hào)

在instantclient的路徑輸入cmd打開(kāi)cmd窗口輸入下方命令測(cè)試連接

sqlplus sys/密碼@//數(shù)據(jù)庫(kù)IP:1521/服務(wù)名 as sysdba

數(shù)據(jù)庫(kù)IP就是你安裝數(shù)據(jù)庫(kù)的虛擬機(jī)的IP

?查詢用戶

?連接成功

配置環(huán)境變量，兩個(gè)地方，一個(gè)外部一個(gè)path內(nèi)部

路徑選擇你自己存放的路徑，不能有中文和特殊符號(hào)

?配置TNS協(xié)議

使用別名測(cè)試登錄

sqlplus sys/密碼@別名 as sysdba

中文配置的環(huán)境變量

NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

解壓PLSQL

?打開(kāi)登錄軟件

查詢?nèi)坑脩?/p>

select * from all_users;

?

修改密碼并解鎖scott用戶

alter user scott identified by "123456";

alter user scott account unlock;

使用scott用戶登錄

?永久注冊(cè)

?

?

測(cè)試

-- 查詢emp表的內(nèi)容

select * from emp;

-- 查詢scott用戶擁有的表

select * from user_tables;

?

什么是單表查詢？

在一張表內(nèi)的查詢操作，就叫單表查詢

PLSQL軟件

?默認(rèn)表EMP表介紹

2 查詢（select）

要求：

必須是英文符號(hào)養(yǎng)成習(xí)慣分號(hào)結(jié)尾表名，字段名不區(qū)分大小寫(xiě)，內(nèi)容區(qū)分大小寫(xiě)

2.1 基本查詢

select * from 表名;

select * from emp;

select * from salgrade;

2.1.1?指定字段查詢

字段：每個(gè)表內(nèi)容的標(biāo)題

*代表全部字段，多個(gè)字段查詢是，字段之間使用逗號(hào)隔開(kāi)

select 字段 from 表名;

select ename,job,sal from emp;

2.1.2 條件查詢（where）

select 字段 from 表名 where 條件;

-- 條件：字符用單引號(hào)包裹，數(shù)字不需要

select ename,job from emp where job = 'SALESMAN';

select ename,sal from emp where sal > 2000;

-- 工作是CLERK的人的工資

select sal,ename from emp where job = 'CLERK';

2.1.3 多個(gè)條件查詢（and）

select 字段 from 表名 where 條件1 and 條件2;

-- 工作是 CLERK 并且工資大于 1000 的人

select ename,sal,job from emp where job = 'CLERK' and sal > 1000;

-- 有工資大于2000并且是部門(mén)10的人

-- 有工資大于2000并且是部門(mén)10的人

2.1.4 Null值的判斷

is null ： 值是空值

is not null ： 不是空值，0不是空值

select 字段 from 表名 where 字段 判斷空值;

-- 查詢有獎(jiǎng)金的人

select comm,ename from emp where comm is not null;

-- 查詢沒(méi)有上級(jí)編號(hào)的人

select ename,mgr from emp where mgr is null;

2.1.5 別名（as）

給表或者字段取一個(gè)別名，方便我們記憶，as可以省略

select 字段 as 別名 from 表名 別名 ...;

注意事項(xiàng)

給表取了別名，使用字段時(shí)要用別名來(lái)聲明字段

select e.ename from emp e;

2.1.6 去重（distinct）

作用：去除重復(fù)值

select distinct(字段) from emp ...; -- 查詢有哪些部門(mén)

2.1.7 排序（order by）

作用：對(duì)查詢出來(lái)的數(shù)據(jù)進(jìn)行排序

asc：升序，一般不加，默認(rèn)升序

desc：降序

select 字段 from 表名 order by 字段 asc/desc ...;

-- 排序工資

select sal,ename from emp order by sal asc;

2.1.8 面對(duì)空值

默認(rèn)是最大值，可以使用 nulls first 和 nulls last 調(diào)整空值的順序

select 字段 from 表名 order by 字段 asc/desc nulls first/last ...;

-- 排序獎(jiǎng)金

select comm,ename from emp order by comm asc nulls first;

2.1.9 模糊查詢（like）

作用：部分匹配

_：一個(gè)下劃線代表一個(gè)字符

%：代表全部字符

select 字段 from 表名 where 字段 like '值' ...;

-- 查詢名字是S開(kāi)頭的

select ename from emp where ename like 'S____';

select ename from emp where ename like 'S%';

2.1.10 算數(shù)運(yùn)算符 （+-*/）

通常使用在字段中

select 字段+-*/ from 表名 ...;

-- 給每個(gè)員工漲 500 塊錢(qián)工資 select 500 + sal 新工資,sal 舊工資 from emp;

2.1.11 比較運(yùn)算符 （> < >= <= !=)

通常使用在條件里

!=：不等于

-- 查詢不是部門(mén) 10 的人

select ename,deptno from emp where deptno != 10;

2.1.12 邏輯運(yùn)算符 （and,or,not）

and：與的關(guān)系，表并列，連接兩個(gè)表達(dá)式，只有二者都成立菜返回結(jié)果

or：或的關(guān)系，連接兩個(gè)表達(dá)式，只有一個(gè)成立就可以返回結(jié)果

not：非的關(guān)系，用在表達(dá)式之前，表示取反

-- 查詢來(lái)自部門(mén) 10 或者部門(mén) 20 的人的姓名

select ename,deptno from emp where deptno = 10 or deptno = 20;

-- 查詢不是來(lái)自部門(mén) 10 或者部門(mén) 20 的人的姓名

select ename,deptno from emp where not(deptno = 10 or deptno = 20);

select ename,deptno from emp where not deptno in (10,20); -- 使用 in + or

2.1.13 區(qū)域條件（between and）

select 字段 from 表名 where 字段 between 條件1 and 條件2 ...;

-- 查詢工資在1000~2000之間的員工

select ename,sal from emp where sal between 1000 and 2000;

2.2 函數(shù)

通過(guò)函數(shù)名來(lái)使用一些特定的功能 sum：求和函數(shù)，函數(shù)是可以套用的，函數(shù)的變量位置不是單純的只能放字段、字符串，函數(shù)之間是可以套用的

2.2.1 字符串函數(shù)

2.2.1.1大小寫(xiě)控制（upper、lower）

upper：轉(zhuǎn)大寫(xiě)

lower：轉(zhuǎn)小寫(xiě)

select lower(ename) 小寫(xiě)名字 from emp;

select upper('aaaa') 大寫(xiě)字母 from dual;

2.2.1.2首字母大寫(xiě)（initcap）

select initcap(lower(ename)) 首字母大寫(xiě) from emp;

2.2.1.3 字符串拼接（concat，||）

select concat('Dear',ename) DearName from emp;

select 'Dear' || lower(ename) from emp;

2.2.1.4 字符串提?。╯unstr）

select ename,substr(ename,3,2) from emp;

2.2.1.5 字符串查找（instr）

select ename,instr(ename,'I',2) from emp;

-- 截取字符串 Hello world！當(dāng)中的 wo

select instr('Hello world!','wo') from dual;

如果不加最后一個(gè)截取個(gè)數(shù)，返回的值是截取的是第幾位

2.2.1.6 返回字符的長(zhǎng)度（length、lengthb）

英文的字符等于字節(jié)數(shù)，中文的1：2

select length(sname) 字符數(shù),lengthb(sname) 字節(jié)數(shù) from student;

小練習(xí)

-- 查找員工姓名是五個(gè)字母的員工 select length(ename) 長(zhǎng)度,ename from emp where length(ename) = 5;

2.2.1.7 左右填充函數(shù)（ipad，rpad）

ipad：左填充

rpad：右填充

select lpad(ename,10,'*') from emp; -- 給 SMITH 右邊填充 3 個(gè)星號(hào) select rpad(ename,8,'*') from emp;

2.2.1.8去除字符串前后的字符（trim）

使用from連接

select trim('*' from rpad(ename,8,'*')) 去除星號(hào) from emp;

2.2.1.9 字符串替換（replace）

select replace(concat('Dear',ename),'Dear','-') from emp;

2.2.2 數(shù)學(xué)函數(shù)

2.2.2.1 四舍五入（round）

select round(123.456) from dual;

截?cái)啵╰runc）

select trunc(123.456) from dual; -- 123

求余（mod）

mod(除數(shù)，被除數(shù))

select mod(100,3) from dual; -- 3

取整（ceil、floor）

ceil：向上取整，去除小數(shù)位整數(shù)位+1

floor：向下取整，去除小數(shù)位

select ceil(123.123),floor(456.789) from dual; -- 124 456

2.2.2.2 日期函數(shù)

當(dāng)前所用時(shí)間是 2023-07-25-13:50~

顯示當(dāng)前日期和時(shí)間（sysdate、systimestamp）

select sysdate,systimestamp from dual;

2.2.2.3 返回當(dāng)前時(shí)間和日期

current_date、current_timestamp、localtimestamp

select current_date,current_timestamp,localtimestamp from dual;

2.2.2.4 給指定的日期添加月份（add_months）

select hiredate,add_months(hiredate,2) from emp;

2.2.2.5 當(dāng)前月的最后一天（last_day）

select hiredate,last_day(hiredate) from emp;

2.2.2.6 抽取日期的單位（extract）

select extract(year from hiredate) year from emp;

select extract(month from hiredate) month from emp;

select extract(day from hiredate) day from emp;

select extract(hour from systimestamp) hour from dual;

select extract(minute from systimestamp) minute from dual;

select extract(second from systimestamp) second from dual;

2.2.2.7返回兩個(gè)日期之間的月份（months_between）

select ceil(months_between(sysdate,hiredate)) months from emp;

2.2.2.8 返回下一個(gè)周幾（next_day）

不是下一周是下一個(gè)

select next_day(sysdate,'星期五') from dual;

2.2.3 轉(zhuǎn)換函數(shù)

2.2.3.1 字符串轉(zhuǎn)日期（to_date）

select to_date('2023-07-25','yyyy-mm-dd') from dual;

2.2.3.2. 日期轉(zhuǎn)字符串（to_char）

select to_char(sysdate,'yyyy') year from dual; -- 23

select to_char(sysdate,'mm') months from dual; -- 07

select to_char(sysdate,'dd') day from dual; -- 25

select to_char(sysdate,'day') week from dual; -- 返回當(dāng)前星期幾 星期二

2.2.3.3 字符串轉(zhuǎn)數(shù)字（to_numbner）

select to_number('20230725') from dual;

-- 轉(zhuǎn)有效數(shù)字

select to_number('$123.456','$999.9999') from dual;

-- 16進(jìn)制轉(zhuǎn)10進(jìn)制

select to_number('19f','xxx') from dual;

2.2.3.4 通用函數(shù)

nvl

nvl(值1，值2) 如果值1為空返回值2

-- 如果獎(jiǎng)金為空，給他500獎(jiǎng)金

select nvl(comm,500) from emp;

nvl2

nvl2(值1，值2，值3) 如果值1為空返回值3，否則返回值2

select nvl2(comm,comm+500,1500) 漲獎(jiǎng)金,comm from emp;

nullif

nullif(值1，值2)，如果值1等于值2，返回null，反之返回值1

select nullif(500,null) from dual;

coalesce

coalesce(值1，值2，值3，....)返回第一個(gè)不為空的值

select coalesce(comm,deptno) from emp;

2.2.4 條件函數(shù)

case when

case when 值1 then 返回值1

when 值2 then 返回值2

...

else 其他值

end

-- 判斷員工的部門(mén)名稱(chēng)。如果是 10 我們叫 財(cái)務(wù)部

-- 如果是 20 我們叫 研發(fā)部

-- 如果是 30 我們叫 銷(xiāo)售部

select emp.*,

case when deptno = 10 then '財(cái)務(wù)部'

????????when deptno = 20 then '研發(fā)部'

????????when deptno = 30 then '銷(xiāo)售部'

else '其他部門(mén)'

end 部門(mén)名稱(chēng)

from emp;

decode

decode (條件，值1，返回值1，

????????????????????????值2，返回值2

???????????????????????? ...,

????????????????????????值n，返回值n，

????????????????????????其他值)

如果 條件 = 值1，輸出返回值1

如果 條件 = 值2，輸出返回值2

都不滿足 返回其他值

-- 判斷員工的部門(mén)名稱(chēng)。如果是 10 我們叫 財(cái)務(wù)部

-- 如果是 20 我們叫 研發(fā)部

-- 如果是 30 我們叫 銷(xiāo)售部

select emp.*,

???????????????? decode(deptno,10,'財(cái)務(wù)部',

????????????????????????????????????????????????20,'研發(fā)部',

????????????????????????????????????????????????30,'銷(xiāo)售部',

????????????????????????????????????????????????'其他部門(mén)') 部門(mén)名稱(chēng)

from emp;

2.2.5 聚合函數(shù)（分組函數(shù)）

括號(hào)里面放字段

max() -- 最大值

min() -- 最小值

avg() -- 平均值

count() -- 數(shù)量

sum() -- 求和

select max(sal),min(sal),avg(sal),count(sal),sum(sal) from emp;

count(*),count(1) 都代表總內(nèi)容數(shù)，多少行數(shù)據(jù)

select count(*) from emp;

2.2.5.1 分組語(yǔ)句 （group by）

對(duì)指定的字段分組，比如部門(mén)有10，20，30，如果是 group by deptno，就是對(duì)部門(mén)分組，分成10一組，20一組，30一組

select 字段 from 表名 where 條件 group by 字段 ...;

-- 求每個(gè)部門(mén)的平均工資

select deptno,trunc(avg(sal)) 平均工資 from emp group by deptno;

注意事項(xiàng)

1. 出現(xiàn)在 select 后面的字段，如果不是在分組函數(shù)中，那么他必須同時(shí)出現(xiàn)在 group by 語(yǔ)句當(dāng)中

2. 出現(xiàn)在 group by 后面的字段不一定出現(xiàn)在 select 后面

3. where 語(yǔ)句中不允許出現(xiàn) group by

分組語(yǔ)句的條件（having）

用法與 where 一樣，having 條件

區(qū)別：

1. having 服務(wù)對(duì)象是 group by，where 服務(wù)對(duì)象是字段

2. where 不能用分組函數(shù)，having 通過(guò)條件過(guò)濾分組函數(shù)

3. having 是在分組完成后執(zhí)行，where 是在分組前執(zhí)行，這也是為什么 where 不能服務(wù) group by 的原因

-- 分部門(mén)和職業(yè)統(tǒng)計(jì)員工的工資和，并且工資和 > 3000

1.group by deptno,job

2.having sum(sal) > 3000

3.deptno,job,sum(sal)

4.select deptno,job,sum(sal) from emp group by deptno,job having sum(sal) > 3000;

2.2.6 查詢順序

1. from table

2. where example

3. group by title

4. having

5. select

6. order by answer

3 連續(xù)查詢

3.1 笛卡爾積

兩個(gè)表的一種關(guān)聯(lián)方式，將第一張表中的每一行都與第二張表中的每一行組合，生成一個(gè)新的表

舉例：兩個(gè)表A和表B

表A

?表B

?笛卡爾積后---> 表C

3.2 等值連接查詢

我們從多張表中查詢數(shù)據(jù)的時(shí)候，我們根據(jù)表與表之間的關(guān)聯(lián)性來(lái)尋找對(duì)應(yīng)的數(shù)據(jù)

-- 我們查找 SMITH 的工作崗位的詳細(xì)信息

-- 先找到 SMITH

select ename from emp where ename = 'SMITH'

-- 找到 SMITH 的工作部門(mén)

select ename,deptno from emp where ename = 'SMITH'

-- 找到部門(mén) 20 的詳細(xì)信息

select * from dept where deptno = 20;

-- 使用笛卡爾積將兩張表拼接

select * from emp,dept;

-- 選擇有效的字段，emp表的 ename字段 ，deptno字段，dept表的全部字段

select emp.ename,emp.deptno,dept.* from emp,dept;

-- 只需要部門(mén)20，名字是SMITH

select e.ename,d.* from emp e,dept d where e.ename = 'SMITH' and d.deptno = 20; -- 笛卡爾積 select e.ename,d.* from emp e,dept d where e.ename = 'SMITH' and d.deptno = e.deptno; -- 等值連接

3.2 非等值連接查詢

連接兩個(gè)表時(shí)使用不等于運(yùn)算符來(lái)比較兩個(gè)表的列

-- 查找成績(jī)?cè)?0分以上，這門(mén)課的學(xué)分可以獲得，計(jì)算每個(gè)學(xué)生總學(xué)分和統(tǒng)計(jì)姓名

select s.*,m.*,c.* from student s,mark m,course c;

select * from student; select * from mark; select * from course;

-- 得到關(guān)聯(lián)字段

select s.*,m.*,c.* from student s,mark m,course c where s.sid = m.sid and m.cid = c.cid;

-- 不等值的條件

select s.*,m.*,c.* from student s,mark m,course c where s.sid = m.sid and m.cid = c.cid and cmark >= 60;

-- 去除重復(fù)數(shù)據(jù)

select sname,sum(cval) from student s,mark m,course c

where s.sid = m.sid and m.cid = c.cid and cmark >= 60

group by sname,s.sid ;

3.3 自連接

一張表當(dāng)多張表用

select a.*,b.* from dept a,dept b;

-- 查詢 emp 表中所有工資比部門(mén)平均工資高的員工信息

select * from emp;

select sal,ceil(avg(sal)) from emp group by deptno,sal having sal > avg(sal); -- 錯(cuò)誤 800 > 800/1

-- 連接兩張 emp 表

select e1.*,e2.* from emp e1,emp e2;

-- 拿到需要的字段 select e1.sal,e1.ename from emp e1,emp e2;

-- 等值連接

select e1.sal,e1.ename from emp e1,emp e2 where e1.deptno = e2.deptno;

-- 去除重復(fù)數(shù)據(jù)

select e1.sal,e1.ename,ceil(avg(e2.sal)) from emp e1,emp e2

where e1.deptno = e2.deptno

group by e1.deptno,e1.sal,e1.ename;

-- 添加條件

select e1.sal,e1.ename,ceil(avg(e2.sal)) from emp e1,emp e2

where e1.deptno = e2.deptno

group by e1.deptno,e1.sal,e1.ename having e1.sal > avg(e2.sal);

3.4 左外連接右外連接（left join，right join）

以左邊或者右邊的表為基準(zhǔn)表查詢數(shù)據(jù)，如果沒(méi)有數(shù)據(jù)補(bǔ)null值，判斷時(shí)添加on使用等值連接

create table testA (

????????????????tid number,

????????????????A_name varchar2(10)

);

insert into testA values(1,'A');

insert into testA values(2,'B');

insert into testA values(3,'C');

????????create table testB (

????????????????????????tid number,

????????????????????????B_name varchar2(10)

);

insert into testB values(1,'A');

insert into testB values(2,'B');

insert into testB values(3,'C');

insert into testB values(4,'D');

-- 刪除表

DROP TABLE testA;

DROP TABLE testB;

select * from testA;

select * from testB;

-- 左外連接

select * from testA left join testB on testA.tid = testB.tid

-- 右外連接

select * from testA right join testB on testA.tid = testB.tid

3.5 內(nèi)連接（inner join）

當(dāng)我們想要將兩個(gè)或者多個(gè)表中的數(shù)據(jù)進(jìn)行連接時(shí)可以使用內(nèi)連接，通過(guò)一個(gè)或者多個(gè)關(guān)聯(lián)條件，它會(huì)返回兩個(gè)表中匹配到的行，也就是說(shuō)在連接表中存在匹配的行時(shí)才會(huì)返回結(jié)果

inner join 可以簡(jiǎn)寫(xiě)成 join

create table emp_test (

????????????????emp_no number,

????????????????emp_name varchar2(10),

????????????????dept_no number

);

insert into emp_test values(1,'張三',1);

insert into emp_test values(2,'李四',2);

insert into emp_test values(3,'王五',2);

insert into emp_test values(4,'趙六',3);

????????create table dept_test (

????????????????dept_no number,

????????????????dept_name varchar2(10)

);

insert into dept_test values(1,'人事部');

insert into dept_test values(2,'技術(shù)部');

insert into dept_test values(3,'財(cái)務(wù)部');

select * from emp_test; select * from dept_test;

-- 查詢員工的姓名和所在部門(mén)的名稱(chēng)

select e.emp_name,d.dept_name from emp_test e,dept_test d

where d.dept_no = e.dept_no; -- 笛卡爾積

select e.emp_name,d.dept_name from emp_test e inner join dept_test d on d.dept_no = e.dept_no; -- 內(nèi)連接

-- 查詢每個(gè)學(xué)生的姓名和平均分

select s.sid,ceil(avg(cmark)) from student s, mark m

where s.sid=m.sid group by s.sid -- 笛卡爾積

select s.sid ,ceil(avg(cmark)) from student s inner join mark m on m.sid=s.sid group by s.sid -- 內(nèi)連接

比較笛卡爾積和內(nèi)連接：

內(nèi)連接：只返回滿足連接條件的結(jié)果集，可以過(guò)濾數(shù)據(jù)笛卡爾積：只是單純的連接兩個(gè)表的行，不會(huì)過(guò)濾出數(shù)據(jù)

優(yōu)勢(shì)

數(shù)據(jù)過(guò)濾：內(nèi)連接會(huì)根據(jù)關(guān)聯(lián)條件來(lái)過(guò)濾數(shù)據(jù)，只返回相關(guān)的行，減少了數(shù)據(jù)的查詢速度查詢效率：內(nèi)連接的查詢效率高于笛卡爾積資源占用：內(nèi)連接只返回有效數(shù)據(jù)，所以資源占用小

舉例

create table customers (

????????????????cid number,

????????????????cname varchar2(10),

????????????????clocal varchar(100)

);

insert into customers values(1,'張三','南京');

insert into customers values(2,'李四','揚(yáng)州');

insert into customers values(3,'王五','徐州');

insert into customers values(4,'趙六','蘇州');

create table orders (

????????????????oid number,

????????????????cid number,

????????????????odate varchar2(10)

);

insert into orders values(101,1,'07-01');

insert into orders values(202,2,'07-02');

insert into orders values(303,3,'07-03');

insert into orders values(404,4,'07-04');

select * from customers; select * from orders;

-- 返回匹配數(shù)據(jù)

select cname,clocal,oid,odate from customers inner join orders on customers.cid = orders.cid; select cname,clocal,oid,odate from customers inner join orders就是笛卡爾積的

select cname,clocal,oid,odate from customers,orders

所以相對(duì)于笛卡爾積來(lái)說(shuō)，少了一步，所以查詢時(shí)間肯定是比笛卡爾積塊

select cname,clocal,oid,odate from customers,orders where customers.cid = orders.cid;

4 高級(jí)查詢、開(kāi)窗函數(shù)、分頁(yè)查詢、TopN查詢

4.1 隨機(jī)查詢

dbms_random.value()

-- 產(chǎn)生 0 ~ 1 之間的隨機(jī)數(shù)，可以為0，不能為1

select dbms_random.value() from dual;

-- 產(chǎn)生 1 ~ 11 之間的隨機(jī)數(shù)

select dbms_random.value(1,11) from dual;

-- 產(chǎn)生 1 ~ 10 之間的隨機(jī)整數(shù)，含有10

select trunc(dbms_random.value(1,11),0) from dual;

-- 生成一個(gè)隨機(jī)小寫(xiě)字母97 ~ 122

select chr(trunc(dbms_random.value(97,123),0)) from dual;

-- 隨機(jī)返回學(xué)生表的五條數(shù)據(jù) -- 得到一張順序被打亂的新表

select * from student order by dbms_random.value();

-- 使用 rownum 返回五條數(shù)據(jù)

select rownum r,s.* from (select * from student order by dbms_random.value()) s where rownum <= 5

4.2 子查詢

4.2.1 什么是子查詢？

從一個(gè)表中查出來(lái)的部分?jǐn)?shù)據(jù)當(dāng)作另一個(gè)表的查詢條件，分為單行子查詢和多行子查詢

4.2.2 單行子查詢

查詢出來(lái)的結(jié)果只有一行，精確的匹配某個(gè)值

-- 查詢工資最高的員工 -- 找到最高的工資（5000）

select max(sal) from emp;

-- 拿最高的工資找人，最高的工資（5000）當(dāng)作條件

select ename,sal from emp where sal = (select max(sal) from emp)

-- 查詢學(xué)生，年齡比學(xué)號(hào)10005號(hào)學(xué)生大的其他學(xué)生的姓名

-- 找到 10005 號(hào)學(xué)生的年齡（21）

select sage from student where sid = 10005;

-- 比 21 大的其他學(xué)生的姓名

select sname from student where sage > 21;

select sname from student where sage > ( select sage from student where sid = 10005);

4.2.3 多行子查詢

使用 in any all 三種運(yùn)算符

4.2.3.1 in 運(yùn)算符

用于判斷某個(gè)值是否在子查詢返回的結(jié)果集中，在子查詢中返回的結(jié)果只要有一個(gè)值等于外層查詢中的某個(gè)值，就會(huì)返回結(jié)果

1. 找到對(duì)應(yīng)的值

2. 作為條件，里層和外層的值要對(duì)應(yīng)

-- 找出所有男生的成績(jī)

-- 先找到所有男生

select sid from student where ssex = '男';

-- 找出所有男生的成績(jī)

select * from mark; select sid,cmark from mark where sid in (select sid from student where ssex = '男');

4.2.3.2 多行子查詢是可以無(wú)限嵌套的

-- 找出所有男生的成績(jī)

select sid,cmark from mark where sid in (select sid from student where ssex = '男');

-- 找出所有男生的成績(jī)所對(duì)應(yīng)的科目

select * from course;

select * from course

where cid in (

select cid from mark

where sid in (

select sid from student

where ssex = '男'))

-- 找出科目所對(duì)應(yīng)的老師

select * from teacher

where tid in (

select tid from course

where cid in (

select cid from mark

where sid in (

select sid from student where ssex = '男')))

4.2.3.3 any運(yùn)算符

用于比較外層查詢中的某個(gè)值與子查詢返回的結(jié)果集中的任意一個(gè)值是否相等，在子查詢中返回的結(jié)果只要有一個(gè)值與外層查詢中的某個(gè)值相等，返回結(jié)果

-- 查詢年齡大于江蘇任意一個(gè)學(xué)生年齡的其他地區(qū)的學(xué)生信息

-- 查找江蘇學(xué)生的年齡

select sage from student where snativeplace = '江蘇';

-- 查詢比江蘇地區(qū)最小年齡大的學(xué)生的其他地區(qū)的學(xué)生信息

select sname,snativeplace,sage from student

where sage > any (select sage from student where snativeplace = '江蘇')

4.2.3.4 all運(yùn)算符

用于比較外層查詢中的某個(gè)值與子查詢返回的結(jié)果集中的全部值是否相等，在子查詢中返回的結(jié)果集中的所有值都與外層查詢中的某個(gè)值相等，才會(huì)返回結(jié)果

-- 查詢年齡大于江蘇地區(qū)所有學(xué)生年齡的其他地區(qū)的學(xué)生信息

select sname,snativeplace,sage from student

where sage > all (select sage from student where snativeplace = '江蘇') and snativeplace != '江蘇';

4.3 開(kāi)窗函數(shù)

4.3.1 開(kāi)窗函數(shù)over()

語(yǔ)法：over(值1 order by 值2)，根據(jù)值1，進(jìn)行區(qū)分，再分區(qū)內(nèi)按照值2進(jìn)行排序注意：over函數(shù)一般不單獨(dú)使用，會(huì)和關(guān)鍵字進(jìn)行配合

4.3.2 常用關(guān)鍵字

row_number： 用于為結(jié)果集中的每一行分配唯一的序號(hào)。這個(gè)序號(hào)是根據(jù) order by 子句定義的排序順序來(lái)分配的。rank：用于為結(jié)果集中的每一行分配一個(gè)排名。如果有多個(gè)行具有相同的值，則被分配相同的排名，下一個(gè)排名將被跳過(guò)dense_rank：與rank相似，但是具有多行相同值，被分配相同的排名，并且下一個(gè)排名不會(huì)被跳過(guò)lead：用于獲取結(jié)果中當(dāng)前行之后的第n行值?？梢允褂?partition by 子句將結(jié)果集分成多個(gè)分區(qū)sum：用于計(jì)算指定列的總和?？梢允褂?group by 子句將結(jié)果集分組lag：用于獲取結(jié)果中當(dāng)前行之前的第n行值。

4.3.2.1 row_numbe關(guān)鍵字的使用

-- 查詢學(xué)生分?jǐn)?shù)信息，并按照每個(gè)人的分?jǐn)?shù)進(jìn)行排序

select m.*,row_number()over(partition by m.sid order by m.cmark desc) 排名 from mark m

?-- 每個(gè)學(xué)生 前三名 的成績(jī)

select * from ( select m.*,row_number()over(partition by m.sid

order by m.cmark desc) paiming from mark m) where paiming <= 3;

-- 查詢學(xué)生表，跳過(guò)表中的偶數(shù)行

-- 根據(jù) sid 分配

select row_number()over(order by sid) num,s.* from student s

-- 跳過(guò)偶數(shù)行

select * from (

select row_number()over(order by sid) num,s.* from student s)

where mod(num,2) = 1;

4.3.2.2 rank關(guān)鍵字的使用

-- 查詢每個(gè)學(xué)生的成績(jī)，并且為每個(gè)學(xué)生計(jì)算排名

select m.*,rank()over(partition by m.sid order by m.cmark desc) paiming from mark m

?-- 查詢每個(gè)學(xué)生的成績(jī)，計(jì)算每個(gè)學(xué)生的排名，并列排名并且合成一個(gè)位置

select m.*,dense_rank()over(partition by m.sid order by m.cmark desc) paiming from mark m

4.3.2.3 max和min的使用

-- 查詢最大年齡和最小年齡

select distinct(max(sage)over()),min(sage)over() from student;

4.3.2.4 sum的使用

-- 求所有學(xué)生的年齡的連續(xù)求和、總合

-- 一共18組，總合 366

select sum(sage) over(order by sid) 連續(xù)求和,sum(sage) over() 總和 from student;

?

-- 分班級(jí)給學(xué)生的年齡求和、連續(xù)求和、總和，20歲以上的參與計(jì)算

select sclass,sname,sage, sum(sage)over(partition by sclass order by sid) 連續(xù)求和, sum(sage)over(partition by sclass) 總和

from student

where sage >= 20;

4.3.2.5 lag和lead關(guān)鍵字的使用

select sname,sage,

lead(sage) over(order by sage) 下一行,

lag(sage) over(order by sage) 上一行

from student

?

?-- 查詢第三行以下的數(shù)據(jù) row_number,rownum

select sname,sage,

????????row_number()over(order by sage desc) row_num,

????????lead(sage) over(order by sage desc) 下一行

????????from student

-- 子查詢

select *

????????from ( select sname,sage,

????????????????row_number()over(order by sage desc) row_num,

????????????????lead(sage) over(order by sage desc) 下一行

????????????????from student)

where row_num > 3;

4.4 分頁(yè)查詢

思考：如果表數(shù)據(jù)量特別大，想要一次性展現(xiàn)給用戶，頁(yè)面加載數(shù)據(jù)很多，導(dǎo)致查詢速度很慢，體驗(yàn)很差，如何解決

解決：使用分頁(yè)查詢進(jìn)行分頁(yè)展示

-- 語(yǔ)法 -- 每頁(yè)展示 m 條數(shù)據(jù) 查詢第 n 頁(yè)的數(shù)據(jù)

select * from (

????????select rownum r,t1.* from table1 t1(需要分頁(yè)的表)

????????where rownum <= m * n)t2

????????where r > m * n - m

-- 查詢學(xué)生表的 10 ~ 15 行的數(shù)據(jù)

-- 每頁(yè)分 5 條數(shù)據(jù)，查詢 第 3 頁(yè)的數(shù)據(jù)

-- m = 5,n = 3

select * from (

????????select rownum r,s.* from student s

????????where rownum <= 15)t

where r > 10;

4.5 TopN查詢

是一種查詢語(yǔ)句，可以返回指定數(shù)據(jù)集中的前N個(gè)符合條件的記錄，通常，TopN用在數(shù)據(jù)分析和業(yè)務(wù)決策很有效

比如：mark表存儲(chǔ)了學(xué)生的分?jǐn)?shù)，我們可以使用TopN來(lái)返回前N名分?jǐn)?shù)最高的學(xué)生記錄

4.5.1 rownum

這是Oracle當(dāng)中的一個(gè)“偽列”，它按照查詢結(jié)果集中的行號(hào)為每一行分配一個(gè)唯一的值，我們可以使用rownum實(shí)現(xiàn)

注意：在使用rownum的時(shí)候，我們必須放在子查詢中，并在外部查詢中進(jìn)行限制，否則會(huì)出現(xiàn)錯(cuò)誤的結(jié)果

4.5.1.1.rownum對(duì)于等于某個(gè)值的查詢條件

我們想使用rownum查詢第一行數(shù)據(jù)，rownum = 1，查詢第二行不能使用 rownum = 2，沒(méi)有結(jié)果，rownum無(wú)法使用 = 連接大于 1 的數(shù)，如果你想實(shí)現(xiàn) rownum = 2 這種效果，需要使用子查詢，rownum要取別名

--查詢學(xué)生表的第一條數(shù)據(jù)

select rownum r,student.* from student

where rownum = 1;

--查詢學(xué)生表的第六條數(shù)據(jù)

select * from (select rownum r,s.* from student s)

where r =6;

4.5.1.2.rownum對(duì)于大于某個(gè)值的查詢條件

查詢大于某值的記錄的時(shí)候，rownum > n（n是自然數(shù)）一般這種情況也是不行的，依然使用子查詢

-- 查詢第一行以后的數(shù)據(jù)

select rownum r,student.* from student

where rownum > 1;

-- 沒(méi)數(shù)據(jù) select * from (

select rownum r,s.* from student s)

where r > 1;

4.5.1.3.rownum對(duì)于小于某個(gè)值的查詢條件

可以直接使用 rownum < n，用來(lái)查詢前幾行的數(shù)據(jù)

-- 查詢前十行的數(shù)據(jù)

select rownum r,student.* from student

where rownum <= 10;

4.5.1.4.rownum查詢某個(gè)區(qū)間的數(shù)據(jù)

使用子查詢

-- 查詢年齡從高到低，第 5 ~ 8 行的數(shù)據(jù) -- 按照年齡排序

select * from student order by sage desc

-- 獲取每一行

select rownum r,s.* from (

select * from student order by sage desc) s

-- 查找 5 ~ 8 的數(shù)據(jù)

select * from (

select rownum r,s.* from (

select * from student order by sage desc) s)

where r between 5 and 8;

4.5.1.5.rownum和order by

rownum 是獲取數(shù)據(jù)的時(shí)候產(chǎn)生的序列號(hào)使用 order by 會(huì)打亂排序rownum 想獲取排序后的內(nèi)容，也要使用子查詢，將排好的查詢，作為子查詢重新查詢order by 是用來(lái)排序查詢結(jié)果，只能升序或者降序，按照一個(gè)或者多個(gè)排序，不能指定區(qū)間（where）rownum 是一個(gè)偽列，只是用來(lái)限制查詢結(jié)果的行數(shù)

5 事務(wù)、視圖、數(shù)據(jù)類(lèi)型、約束

5.1 什么是事務(wù)？

數(shù)據(jù)庫(kù)操作的一個(gè)邏輯單位，一般由一個(gè)或者多個(gè)數(shù)據(jù)庫(kù)操作組成。事務(wù)中的操作要么全部執(zhí)行，要么都不執(zhí)行，要么全部成功提交，要么全部失敗并回滾，保證數(shù)據(jù)的一致性和完整性

5.2 事務(wù)控制

只要是DML（增刪改）操作會(huì)自動(dòng)啟動(dòng)事務(wù)。一旦開(kāi)始了事務(wù)，所有的增刪改操作都會(huì)被納為是一個(gè)事務(wù)，直到事務(wù)被提交或回滾

特點(diǎn)：

一個(gè)事務(wù)內(nèi)的所有操作一起被提交或回滾提交或回滾后的其他事務(wù)與之前的事務(wù)沒(méi)有任何聯(lián)系當(dāng)事務(wù)被提交后，其他人才能看到事務(wù)提交后的結(jié)果

注意：

如果事務(wù)沒(méi)有提交或回滾，其他人再次嘗試執(zhí)行相同的事務(wù)操作會(huì)導(dǎo)致鎖定現(xiàn)象，無(wú)法完成事務(wù)。這時(shí)一旦事務(wù)上了鎖，必須等待鎖釋放才能繼續(xù)執(zhí)行相同的操作

5.3 事務(wù)控制的操作

5.3.1 永久保存 commit

-- 插入數(shù)據(jù)

insert into cour2 values(105,'歷史',64,1005);

-- 更新數(shù)據(jù)

update cour2 set cid = 1001 where cname = '科學(xué)';

-- 插入數(shù)據(jù)

insert into cour2 values(106,'物理',64,1006);

-- 提交操作，并永久保存

commit;

5.3.2 回滾 rollback

-- 可以回滾到事務(wù)開(kāi)始前的樣式

rollback;

-- 創(chuàng)建保存點(diǎn)

savepoint a;

-- 執(zhí)行事務(wù)操作

delete from cour where tid = 1001

-- 回滾到保存點(diǎn)

rollback to a

5.4 事務(wù)的特性

ACID，db-01

5.4.1 原子性

-- 開(kāi)啟事務(wù)

BEGIN;

-- 插入數(shù)據(jù)

?insert into customertype (cid,ctype,explain) values(4,'銅卡會(huì)員','消費(fèi)在30000元以上');

--更新數(shù)據(jù)

update customertype set ctype = '一般客戶' where cid = 1;

-- 事務(wù)結(jié)束

commit;

5.4.2 一致性

-- 開(kāi)啟事務(wù)

BEGIN

-- 插入數(shù)據(jù)

insert into customertype (cid,ctype,explain) values(5,'黑卡會(huì)員','消費(fèi)在70000元以上');

-- 事務(wù)結(jié)束

commit;

END;

-- 查詢數(shù)據(jù)

select * from customertype;

5.4.3 隔離性

-- 開(kāi)啟事務(wù)A

BEGIN TRANSACTION

-- 在事務(wù)A 中修改數(shù)據(jù)

UPDATE customertype SET ctype = 'yiban客戶' WHERE cid = 1;

commit;

END；

-- 在事務(wù)B 中 修改數(shù)據(jù)

BEGIN TRANSACTION

update customertype set ctype = '普通客戶' where cid = 1;

commit;

END；

-- 在事務(wù)A中查詢數(shù)據(jù)

select * from customertype;

commit;

-- 在事務(wù)B中查詢數(shù)據(jù)

select * from customertype;

commit;

5.4.4 持久性

-- 開(kāi)啟事務(wù)

BEGIN

-- 插入數(shù)據(jù)

insert into customertype (cid,ctype,explain) values(6,'至尊會(huì)員','消費(fèi)在100000元以上');

-- 事務(wù)結(jié)束

commit;

END;

-- 插入完 數(shù)據(jù)一直存在

select * from customertype;

5.5 數(shù)據(jù)共享

臟讀(Drity Read)：一個(gè)事務(wù)讀取到另一個(gè)事務(wù)尚未提交的數(shù)據(jù)。 事務(wù) A 讀取事務(wù) B 更新的數(shù)據(jù)，然后 B 回滾（RollBack）操作，那么 A 讀取到的數(shù)據(jù)是臟數(shù)據(jù)。

不可重復(fù)讀(Non-repeatable read)：一個(gè)事務(wù)中兩次讀取的數(shù)據(jù)的內(nèi)容不一致。 事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù) B 在事務(wù) A 多次讀取的過(guò)程中，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù) A 多次讀取同一數(shù)據(jù)時(shí)，結(jié)果 不一致。

幻讀(Durability )：一個(gè)事務(wù)中兩次讀取的數(shù)據(jù)量不一致。 系統(tǒng)管理員 A 將數(shù)據(jù)庫(kù)中所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為 ABCDE 等級(jí)，但是系統(tǒng)管理員 B 就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員 A 改結(jié)束后發(fā)現(xiàn)還有一條記錄沒(méi)有改過(guò)來(lái)，就好像發(fā)生了幻覺(jué)一樣，這就叫幻讀。

不可重復(fù)讀的和幻讀很容易混淆，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。 解決不可重復(fù)讀的問(wèn)題只需鎖住滿足條件的行，解決幻讀需要鎖表

事務(wù)的隔離級(jí)別

SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別：

READ-UNCOMMITTED(讀取未提交)：?最低的隔離級(jí)別，允許讀取尚未提交的數(shù)據(jù)變更READ-COMMITTED(讀取已提交)：?允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)REPEATABLE-READ(可重復(fù)讀)：?對(duì)同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改SERIALIZABLE(可串行化)：?最高的隔離級(jí)別，完全服從ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾

隔離級(jí)別 臟讀 不可重復(fù)讀 幻讀 READ-UNCOMMITTED(讀取未提交) √ √ √ READ-COMMITTED(讀取已提交) × √ √ REPEATABLE-READ(可重復(fù)讀) × × √ SERIALIZABLE(可串行化) × × ×

5.6 視圖

5.6.1 什么是視圖？

是數(shù)據(jù)庫(kù)中一種虛擬的表，它由一個(gè)人或者多個(gè)的查詢結(jié)果定義出來(lái)的，視圖并不存儲(chǔ)實(shí)際的數(shù)據(jù)，而是在訪問(wèn)時(shí)動(dòng)態(tài)生成結(jié)果。

優(yōu)點(diǎn)：

可以封裝查詢操作，提供簡(jiǎn)化、重用、和安全性等優(yōu)勢(shì)使用視圖可以將復(fù)雜的邏輯包裹起來(lái)，以便從視圖里直接檢索數(shù)據(jù)，就不用再次書(shū)寫(xiě)查詢語(yǔ)句

5.6.2 創(chuàng)建視圖

-- 語(yǔ)法

create view 視圖名 as

條件查詢語(yǔ)句

-- 創(chuàng)建視圖 保存高工資的員工數(shù)據(jù)

create view highsal as

select empno,ename,job,sal

from emp where sal > 2500;

-- 如果沒(méi)有權(quán)限，切換到 sys 用戶，授權(quán)

grant create view to scott;

-- 查看視圖

select * from highsal;

-- 更新視圖

update highsal set sal = sal * 1.1

-- 插入視圖

insert into highsal(empno,ename,job,sal)

values (7788,'Jack','SALESMAN',3000)

-- 刪除視圖

drop view highsal

5.6.3 表與視圖的區(qū)別

表：

數(shù)據(jù)庫(kù)中實(shí)際存儲(chǔ)的數(shù)據(jù)對(duì)象，具有固定的結(jié)構(gòu)和命名表由行和列組成，可以直接存儲(chǔ)數(shù)據(jù)表可以包含索引、約束、觸發(fā)器等數(shù)據(jù)庫(kù)對(duì)象對(duì)表可以執(zhí)行數(shù)據(jù)的增刪改查

視圖

視圖是數(shù)據(jù)庫(kù)中虛擬的表，是基于查詢結(jié)果出現(xiàn)的不存儲(chǔ)實(shí)際的數(shù)據(jù)，僅在動(dòng)態(tài)生成結(jié)果視圖可以簡(jiǎn)化查詢的操作，方便我們更快找到查詢結(jié)果可以做到和表一樣的操作

主要區(qū)別

存儲(chǔ)方式：表存儲(chǔ)實(shí)際的數(shù)據(jù)，視圖不是結(jié)構(gòu)定義：表是行和列的定義，視圖是查詢結(jié)果的定義數(shù)據(jù)保持：表一旦數(shù)據(jù)完成增刪改查永久保存，視圖只有在訪問(wèn)時(shí)菜生成數(shù)據(jù)數(shù)據(jù)修改：表的修改是永久的，視圖只讀的

5.7 數(shù)據(jù)類(lèi)型

5.7.1 按照類(lèi)型分類(lèi)

1.字符串類(lèi)型：char、varchar2、clob，用于存儲(chǔ)文本數(shù)據(jù)，char和varchar2分別是定長(zhǎng)和變長(zhǎng)的字符串類(lèi)型，clob是用于存儲(chǔ)大量文本數(shù)據(jù)的類(lèi)型

char：定長(zhǎng)，10，helloxxxxx

varchar2：變長(zhǎng)，10，hello

2.數(shù)值類(lèi)型：number，用于存儲(chǔ)數(shù)據(jù)類(lèi)型，包括整數(shù)、小數(shù)等?？梢灾付ň群头秶?/p>

3.日期類(lèi)型：date、timestamp，用于存儲(chǔ)日期和時(shí)間。date可以精確到秒，timestamp可以精確到毫秒

4.布爾類(lèi)型：boolean，只有兩個(gè)數(shù)據(jù)類(lèi)型，一個(gè)是true一個(gè)是false

5.二進(jìn)制類(lèi)型：blob，存儲(chǔ)圖片、音頻

6.不常用的：rowid、raw、long

5.7.2 數(shù)據(jù)類(lèi)型注意事項(xiàng)

1.char 類(lèi)型是固定長(zhǎng)度，處理速度比varchar2快，字符填充不滿的時(shí)候，需要使用 trim 把兩邊的空格去掉

2.varchar2一般用于英文和數(shù)字，nvarchar2一般用于中文和其他字符，我們通常還是使用varchar2

3.varchar2是可變長(zhǎng)度，放入幾個(gè)字符就是幾個(gè)字符，不能超過(guò)設(shè)定的字符

4.number（4，2）整數(shù)占4位小數(shù)占兩位，如果小數(shù)位數(shù)多，會(huì)自動(dòng)四舍五入截取指定的位數(shù)

5.number默認(rèn)是38位，放入 99.994 可以，99.995 不行

5.7.3 DDL

數(shù)據(jù)庫(kù)定義語(yǔ)句 5.7.4 常用關(guān)鍵詞

create：創(chuàng)建表、視圖、索引alter：修改表、列、約束drop：刪除表、視圖、索引revoke：撤銷(xiāo)其他角色對(duì)某個(gè)對(duì)象的訪問(wèn)權(quán)限

5.7.5 創(chuàng)建表create table ...

create table 表名(

????????字段1 類(lèi)型（長(zhǎng)度）,

????????字段2 類(lèi)型（長(zhǎng)度）,

????????....

);

-- 語(yǔ)法建表

create table stu(

????????id number(5),

????????name varchar2(50),

????????gender char(3),

????????tel number(11)

);

-- 子查詢建表

create table 表1 as select 字段 from 表2

-- 表1 根據(jù) 表2 創(chuàng)建的，兩個(gè)表一模一樣

create table stu1 as select * from student;

5.7.6 增加字段 alter ... add

alter table 表名 add 字段 類(lèi)型;

-- 給 stu 表增加一個(gè) age 字段

alter table stu add age number(3);

5.7.7 修改字段 alter modify / rename column

modify：修改數(shù)據(jù)類(lèi)型

rename column：修改字段名

-- 修改 number 為 varchar2

alter table stu modify id varchar2(10)

-- 修改 id 為 sid

alter table stu rename column id to sid

5.7.7.1 刪除字段 alter drop

-- 刪除 stu 表的 tel 字段

alter table stu drop column tel;

5.7.7.2 刪除表 drop

-- 刪除 stu1 表

drop table stu1;

5.7.7.3 重命名表 rename

-- 修改 stu 表名為 sttu

rename stu to sttu

練習(xí)

創(chuàng)建商品表，商品表中含有商品編號(hào)、商品名稱(chēng)、商品價(jià)格、商品說(shuō)明這幾個(gè)字段，創(chuàng)建完成以后，需要增加商品數(shù)量這個(gè)字段

表名：shop

商品編號(hào)：sp_id

商品名稱(chēng)：sp_name

商品價(jià)格：sp_price

商品說(shuō)明：sp_text

商品數(shù)量：sp_num

create table shop (

????????sp_id number(10),

????????sp_name varchar2(50),

????????sp_price number(8,2),

????????sp_text varchar2(200)

);

alter table shop add sp_num number(20)

select * from shop

5.7.8 DML

數(shù)據(jù)庫(kù)操作語(yǔ)言

常用關(guān)鍵字

select：查詢

insert：插入

update：更新

delete：刪除

merge：合并

upsert：更新數(shù)據(jù)，如果數(shù)據(jù)存在則更新，不存在則插入

replace：替換

truncate（trunce）：清空

5.7.8.1 插入

值要與字段對(duì)應(yīng)，并且符合字段的屬性

-- 可以缺少值，值與字段相對(duì)應(yīng)

insert into 表名(字段1，字段2，...) values (值1，值2，...)

-- 不可以缺少值，有多少字段，就有多少值

insert into 表名 values(值1，值2，...)

-- 子查詢插入數(shù)據(jù)

insert into 表1 select 字段 from 表2 -- 表2的數(shù)據(jù)插入給表1

-- 舉例

-- 全字段插入

insert into shop(sp_id,sp_name,sp_price,sp_text,sp_num)

values(10000004,'男士運(yùn)動(dòng)鞋',199.99,'男士網(wǎng)面夏季鞋',9999)

-- 不使用字段名插入

insert into shop values(10000002,'女士運(yùn)動(dòng)鞋',199.99,'女士透氣夏季運(yùn)動(dòng)鞋') -- 報(bào)錯(cuò)，因?yàn)槿鄙僦?insert into shop values(10000002,'女士運(yùn)動(dòng)鞋',199.99,'女士透氣夏季運(yùn)動(dòng)鞋',9999)

-- 全字段插入時(shí)可以選擇性插入數(shù)據(jù)，沒(méi)有選擇的字段為 null 值

insert into shop(sp_id,sp_name,sp_price,sp_text)

values(10000003,'男士運(yùn)動(dòng)鞋',199.99,'男士網(wǎng)面夏季鞋')

-- 子查詢插入數(shù)據(jù)

insert into sttu select sid,sname,ssex,sage from student;

5.7.8.2 更新

-- 指定條件修改數(shù)據(jù)

update 表名 set 字段 = 數(shù)據(jù) where 條件 -- 如果不指定條件，默認(rèn)全修改

-- 修改張三的年齡為 22 歲

update sttu set age = 22 where name = '蕭瑾';

-- 修改全部人的性別為 女

update sttu set gender = '女'

5.7.8.3 刪除

-- 指定條件刪除數(shù)據(jù)

delete from 表名 where 條件

-- 刪除李四的數(shù)據(jù)

delete from sttu where name = '李四'

-- 不加條件 默認(rèn)全刪除，和 truncate 一樣的效果

delete from sttu truncate table sttu

5.8 約束

對(duì)插入的數(shù)據(jù)進(jìn)行限制，比如：在姓名這個(gè)字段內(nèi)不能為空、性別只能設(shè)置為男或者女、手機(jī)號(hào)碼必須是11位

5.8.1 常見(jiàn)約束

主鍵約束 - primary key：用于唯一標(biāo)識(shí)表中每一行記錄的列，保證了該列的數(shù)據(jù)不重復(fù)并且不能為空唯一約束 - unique：保證該列的值不重復(fù)，但是可以為空外鍵約束 - foreign key ：將一個(gè)表中的列與另一個(gè)表中的列建立關(guān)聯(lián)，保證了數(shù)據(jù)的一致性和完整性選擇約束 - check：在插入或者更新記錄時(shí)，檢查指定列的值是否符合指定條件。插入的數(shù)據(jù)，必須選擇范圍里面的非空約束 - not null：保證該列的值不能為空

5.8.2 添加約束

5.8.2.1 建表時(shí)添加約束

create table employees(

????????employee_id number(5) primary key,

????????first_name varchar2(50) not null,

????????last_name varchar2(50) not null,

????????hire_date date not null,

????????salary number(10,2)

);

5.8.2.2 alter添加約束

-- 給 sid 添加約束

alter table student add primary key(sid)

-- 修改表字段時(shí)添加約束

alter table sttu modify name char(50) not null

-- 使用 constraint 添加唯一約束

alter table sttu add constraint sttu_uq unique(name)

-- 修改表數(shù)據(jù)的時(shí)候使用 constraint 添加選擇約束

alter table student add constraint ssex_check check(ssex in('男','女'))

-- 使用 alter 添加外鍵約束 和 級(jí)聯(lián)刪除（on delete cascade）

alter table mark

add constraint mk_su foreign key(sid) references student(sid) on delete cascade

alter table 目標(biāo)表

add constraint 外鍵名 foreign key(目標(biāo)字段) references 參考表(參考字段) on delete cascade

5.8.2.3 外鍵約束

在兩個(gè)表之間建立連接，可以是一個(gè)列或者是多個(gè)列，一個(gè)表可以有一個(gè)或者多個(gè)外鍵

a，b兩張表，a是主表，b是副表，b表的外鍵指向a表

如果要添加信息，必須先在a中添加，再去b添加，刪除是相反的，先刪除b再刪除a

create table teach(

tid number primary key,

tname varchar2(50) not null,

tsex char(3) not null check(tsex in ('男','女')),

tphone number(11) not null unique

);

insert into teach values(1001,'張三','男',12345678910);

insert into teach values(1002,'李四','女',12345678911);

insert into teach values(1003,'王五','男',12345678912);

insert into teach values(1004,'趙六','女',12345678913);

select * from teach;

create table cour(

cid number primary key,

cname varchar2(50),

ctime number,

tid number,

constraint fkey_tid foreign key (tid) references teach(tid)

);

select * from cour

-- 如果關(guān)聯(lián)的主表中沒(méi)有該 1005 tid 則數(shù)據(jù)插入失敗

insert into cour values(101,'語(yǔ)文',64,1005) -- 失敗

insert into cour values(101,'語(yǔ)文',64,1001) -- 成功

-- 外鍵刪除 -- 先刪除主表，報(bào)錯(cuò)

delete from teach where tid = 1001

-- 應(yīng)該先刪除副表

delete from cour where tid = 1001

-- 再刪除主表

delete from teach where tid = 1001

5.8.2.4 級(jí)聯(lián)刪除

當(dāng)一個(gè)表的數(shù)據(jù)被刪除時(shí)，與之相關(guān)聯(lián)的其他表中的記錄也會(huì)被自動(dòng)刪除的操作，這種刪除操作可以通過(guò)外鍵約束中的 on delete cascade 來(lái)實(shí)現(xiàn)

通常用于確保數(shù)據(jù)的一致性和完整性，可以同時(shí)刪除多個(gè)相關(guān)記錄

一般都是創(chuàng)建外鍵的時(shí)候創(chuàng)建級(jí)聯(lián)刪除，很少單獨(dú)指定

create table cour1(

cid number primary key,

cname varchar2(50),

ctime number,

tid number,

constraint fkey_tid1 foreign key (tid) references teach(tid) on delete cascade

);

insert into cour1 values(101,'語(yǔ)文',64,1001);

insert into cour1 values(102,'數(shù)學(xué)',64,1002);

insert into cour1 values(103,'英語(yǔ)',64,1003);

select * from cour1

-- 可以直接刪除主表內(nèi)容，會(huì)連帶著副表的內(nèi)容也刪除

delete from teach where tid = 1001

5.8.2.5 刪除約束

-- 刪除主鍵約束

alter table sttu drop primary key

-- 按照約束名刪除

alter table sttu drop constraint sttu_uq

-- 如果要?jiǎng)h除唯一、檢查、外鍵，需要添加 constraint 約束名

alter table sttu drop constraint SSEX_CHK

-- 去除非空

alter table sttu modify name varchar2(50) null

6 索引、序列、PLSQL

6.1 什么是索引？

索引是在數(shù)據(jù)庫(kù)中用于加快檢索速度的數(shù)據(jù)結(jié)構(gòu)。類(lèi)似于書(shū)本的目錄

為什么要用索引？

加快檢索的時(shí)間：數(shù)據(jù)庫(kù)允許系統(tǒng)直接定位到包含特定值的行，無(wú)需掃描整個(gè)表提升查詢的性能：因?yàn)橛辛怂饕诓樵兡承┨囟ㄖ禃r(shí)可以通過(guò)索引快速查詢支持唯一約束：通過(guò)唯一列上創(chuàng)建唯一索引，可以卻表表中的值是唯一的優(yōu)化連接操作：作表之間的連接查詢時(shí)，索引可以加快連接的速度，在連接字段上創(chuàng)建索引即可加速分組和排序：索引可以在執(zhí)行聚合函數(shù)和排序時(shí)提供更快的結(jié)果獲取內(nèi)容的速度：數(shù)據(jù)庫(kù)可以按照索引的順序來(lái)讀取數(shù)據(jù)，避免臨時(shí)表的創(chuàng)建和額外的排序操作

6.2 訪問(wèn)數(shù)據(jù)庫(kù)的方式

6.2.1 Oracle

順序訪問(wèn)：全表掃描，在沒(méi)有索引或者不滿足創(chuàng)建索引的條件時(shí)，回對(duì)整個(gè)表進(jìn)行掃描，逐個(gè)獲取數(shù)據(jù)索引訪問(wèn)：是通過(guò)遍歷索引來(lái)快速定位和查找符合條件的數(shù)據(jù)覆蓋索引：是一種特殊的索引訪問(wèn)方式，當(dāng)查詢只需要從索引中獲取數(shù)據(jù)時(shí)，可以直接利用覆蓋索引，無(wú)需訪問(wèn)實(shí)際的行數(shù)

6.2.2 索引的分類(lèi)

B-Tree：B樹(shù)索引，使用一種多層次的平衡樹(shù)結(jié)構(gòu)唯一索引：確保索引列中的值是唯一的，可以通過(guò)主鍵約束來(lái)自動(dòng)創(chuàng)建，也可以手動(dòng)創(chuàng)建聚簇索引：按照表的物理順序來(lái)存儲(chǔ)數(shù)據(jù)的索引，按照索引的鍵值排序，并且與索引的結(jié)構(gòu)相關(guān)聯(lián)，一個(gè)表只能有一個(gè)聚簇索引非聚簇索引：獨(dú)立于表的物理排序的索引，通過(guò)表中對(duì)應(yīng)行的邏輯指針來(lái)實(shí)現(xiàn)數(shù)據(jù)的快速訪問(wèn)

6.2.3 索引的優(yōu)缺點(diǎn)

缺點(diǎn)

占用存儲(chǔ)的空間：需要額外的空間來(lái)存儲(chǔ)索引，一旦包含多個(gè)索引時(shí)占有的空間會(huì)很多增加寫(xiě)操作的成本：索引的創(chuàng)建需要你手動(dòng)創(chuàng)建，當(dāng)然有些約束會(huì)自動(dòng)創(chuàng)建索引更新頻繁的表性能下降：對(duì)于經(jīng)常修改數(shù)據(jù)的表，索引的更新會(huì)影響表的性能索引選擇不當(dāng)：過(guò)多或者不必要的索引會(huì)降低查詢的性能，增加了數(shù)據(jù)庫(kù)的管理和維護(hù)的成本索引的維護(hù)：面對(duì)多個(gè)索引，維護(hù)起來(lái)需要消耗大量成本

6.2.4 索引的創(chuàng)建

create index 索引名 on 表名 (字段1,字段2....) create index sid_uq on sttu (sid); -- 定義主鍵約束或者唯一約束會(huì)自動(dòng)創(chuàng)建索引 create table idex_table ( did number(4), name varchar2(50), constraint index_uq unique (did), constraint index_prim primary key (name) );

6.3 創(chuàng)建索引的原則（重中之重）

1）最左前綴匹配原則，組合索引非常重要的原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(>、

2）較頻繁作為查詢條件的字段才去創(chuàng)建索引

3）更新頻繁字段不適合創(chuàng)建索引

4）若是不能有效區(qū)分?jǐn)?shù)據(jù)的列不適合做索引列

5）盡量的擴(kuò)展索引，不要新建索引

6）定義有外鍵的數(shù)據(jù)列一定要建立索引。

7）對(duì)于那些查詢中很少涉及的列，重復(fù)值比較多的列不要建立索引。

8）對(duì)于定義為text、image和bit的數(shù)據(jù)類(lèi)型的列不要建立索引。

6.3.1 創(chuàng)建索引時(shí)需要注意什么

非空字段應(yīng)該指定列為NOT NULL，空值很難查詢，除非你想存儲(chǔ)NULL，所以應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值；取值離散大的字段（變量各個(gè)取值之間的差異程度）的列放到聯(lián)合索引的前面，可以通過(guò)count()函數(shù)查看字段的差異值，返回值越大說(shuō)明字段的唯一值越多字段的離散程度高；索引字段越小越好：數(shù)據(jù)庫(kù)的數(shù)據(jù)存儲(chǔ)以頁(yè)為單位一頁(yè)存儲(chǔ)的數(shù)據(jù)越多一次IO操作獲取的數(shù)據(jù)越大效率越高。

6.4 什么是序列？

是數(shù)據(jù)庫(kù)中用于生成唯一數(shù)值序列的對(duì)象。他是一個(gè)獨(dú)立的數(shù)據(jù)庫(kù)對(duì)象，序列生成的數(shù)值通常用于為表的主鍵提供唯一的標(biāo)識(shí)符，或者作用于其他唯一遞增數(shù)值的列。序列生成的值不依賴(lài)于表中的數(shù)據(jù)，而是由數(shù)據(jù)庫(kù)自身維護(hù)和生成。

6.5 序列的操作

6.5.1 創(chuàng)建序列

-- 創(chuàng)建了一個(gè)序列，從 1 開(kāi)始每次遞增 1，nocache 參數(shù)表示不緩存序列的值，而是直接從數(shù)據(jù)庫(kù)中獲取

create sequence SequenceName

start with 1

increment by 1

nocache

-- 刪除序列

drop sequence SequenceName

6.5.2 舉例

-- 子查詢建表

create table testseq as select sid,sname,sage,ssex from student;

select * from testseq delete from testseq

-- 創(chuàng)建序列

create sequence test_sid;

-- 使用序列添加數(shù)據(jù)

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,'張三',20,'男');

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,'李四',19,'女');

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,'王五',21,'男');

6.5.3 自定義序列

-- 自定義序列

test_sid1 create sequence test_sid1

minvalue 10019 -- 定義最小值

start with 10019 -- 開(kāi)始值

maxvalue 99999 -- 序列最大值

increment by 1; -- 規(guī)定自增值

6.5.4 查看序列

-- 查看模當(dāng)前序列值

select test_sid1.currval from dual;

6.6 什么是PL/SQL？

集中處理一些可以復(fù)制、反復(fù)執(zhí)行的SQL操做

set serveroutput on

declare

聲明部分

begin

主體內(nèi)容

exception

捕獲異常

end;

6.6.1 基本輸出

begin

dbms_output.put_line('Hello World!');

end;

/

6.6.2 變量輸出

declare

i number;

-- 定義變量 i 是 number 類(lèi)型

begin

i := 30;

-- 給 i 賦值 dbms_output.put_line(i); -- 輸出 i

end;

6.7 PL/SQL異常

異常是執(zhí)行期間的錯(cuò)誤，PL/SQL支持程序員在程序中使用 EXPECTION 塊來(lái)捕獲此類(lèi)情況，并對(duì)錯(cuò)誤情況采取適當(dāng)?shù)拇胧?/p>

6.7.1 異常處理的語(yǔ)法

when 異常名 then 處理代碼塊

6.7.2 異常列表

1. ACCESS_INTO_NULL：訪問(wèn)未定義的對(duì)象。

2. CASE_NOT_FOUND：CASE語(yǔ)句中沒(méi)有匹配的WHEN條件，并且沒(méi)有設(shè)置ELSE。

3. COLLECTION_IS_NULL：集合元素未初始化。

4. CURSOR_ALREADY_OPEN：游標(biāo)已經(jīng)打開(kāi)。

5. DUP_VAL_ON_INDEX：唯一索引列上存在重復(fù)的值。

6. INVALID_CURSOR：對(duì)非法的游標(biāo)進(jìn)行操作。

7. INVALID_NUMBER：內(nèi)嵌的SQL語(yǔ)句無(wú)法將字符轉(zhuǎn)換為數(shù)字。

8. NO_DATA_FOUND：SELECT INTO語(yǔ)句未返回任何行，或者訪問(wèn)未初始化索引表的元素。

9. TOO_MANY_ROWS：執(zhí)行SELECT INTO語(yǔ)句時(shí)，結(jié)果集超過(guò)一行。

10. ZERO_DIVIDE：除數(shù)為0。

11. SUBSCRIPT_BEYOND_COUNT：下標(biāo)超過(guò)嵌套表或VARRAY的最大值。

12. SUBSCRIPT_OUTSIDE_LIMIT：使用嵌套表或VARRAY時(shí)，下標(biāo)指定為負(fù)數(shù)。

13. VALUE_ERROR：賦值時(shí)，變量長(zhǎng)度不足以容納實(shí)際數(shù)據(jù)。

14. LOGIN_DENIED：當(dāng)PL/SQL應(yīng)用程序連接到Oracle數(shù)據(jù)庫(kù)時(shí)，提供了不正確的用戶名或密碼。

15. NOT_LOGGED_ON：在沒(méi)有連接到Oracle數(shù)據(jù)庫(kù)的情況下嘗試訪問(wèn)數(shù)據(jù)。

16. PROGRAM_ERROR：PL/SQL內(nèi)部問(wèn)題，可能需要重新安裝數(shù)據(jù)字典和PL/SQL系統(tǒng)包。 17. ROWTYPE_MISMATCH：宿主游標(biāo)變量與PL/SQL游標(biāo)變量的返回類(lèi)型不兼容。

18. SELF_IS_NULL：在null對(duì)象上調(diào)用對(duì)象方法時(shí)使用了對(duì)象類(lèi)型。

19. STORAGE_ERROR：運(yùn)行PL/SQL時(shí)，超出了內(nèi)存空間。

20. SYS_INVALID_ID：無(wú)效的ROWID字符串。

21. TIMEOUT_ON_RESOURCE：Oracle在等待資源時(shí)超時(shí)。

6.7.3 處理異常

-- 除數(shù)為 0 的異常

declare

i number;

begin

i:=1/0;

exception

when zero_divide then

dbms_output.put_line('不能除0');

end;

-- 唯一索引列上存在重復(fù)的值

declare

????????v_empno number := 7369; -- 定義變量 v_empno 并賦值

begin

???????? insert into emp (empno) values (v_empno); -- 插入重復(fù)值

exception

????????when DUP_VAL_ON_INDEX then

????????????????dbms_output.put_line('存在相同的編號(hào)！');

end; /

-- CASE語(yǔ)句中沒(méi)有匹配的WHEN條件，并且沒(méi)有設(shè)置ELSE。

declare

????????v_grade char(1) := 'D';

begin

????????case v_grade

????????????????when 'A' then

????????????????????????dbms_output.put_line('優(yōu)秀');

????????????????when 'B' then

????????????????????????dbms_output.put_line('良好');

????????????????end case;

????????????????exception

????????????????????????when CASE_NOT_FOUND then

????????????????????????????????dbms_output.put_line('沒(méi)有匹配項(xiàng)');

end;

/

6.7.4 用戶輸入

-- 讓用戶輸入 sid 查找學(xué)生

declare -- 定義變量

????????v_sid number;

????????v_sname varchar2(30);

begin

????????v_sid :=&請(qǐng)輸入sid; -- & 提示輸出，輸入的 sid 存入進(jìn) v_sid 內(nèi)

????????-- 把 v_sid 當(dāng)做條件放進(jìn)查詢語(yǔ)句中，where 實(shí)際上判斷的是輸入的 sid

????????-- 將找到的 sname 通過(guò) into 存入變量 v_sname 內(nèi)

????????select sname into v_sname from student where sid = v_sid;

????????-- 輸出變量 v_sname

????????dbms_output.put_line(v_sname);

exception

????????-- 捕獲異常

????????when NO_DATA_FOUND then

????????dbms_output.put_line('沒(méi)有該學(xué)生！');

end;

6.8 循環(huán)

6.8.1 loop循環(huán)

loop

循環(huán)語(yǔ)句;

exit when 終止的條件;

循環(huán)條件必須更改;

end loop;

-- 循環(huán)輸出 1 ~ 指定值，用戶數(shù)指定值

declare

????????v_end number;

????????v_start number;

begin

????????v_end :=&請(qǐng)輸入結(jié)束的數(shù)字;

????????v_start := 1;

loop

????????dbms_output.put_line(v_start);

????????v_start := v_start + 1;

????????exit when v_start > v_end;

end loop;

end;

6.8.2 while循環(huán)

while(循環(huán)條件) loop

循環(huán)語(yǔ)句;

循環(huán)條件的改變;

end loop;

-- 循環(huán)輸出 1 ~ 指定值，用戶數(shù)指定值

declare

????????v_end number;

????????v_start number;

begin

????????v_end :=&請(qǐng)輸入結(jié)束的數(shù)字; v_start := 1;

????????-- 判斷開(kāi)始的條件 輸入的數(shù)字 大于 開(kāi)始的數(shù)字 才開(kāi)始循環(huán)，如果不滿足條件結(jié)束循環(huán) ?while(v_start <= v_end) loop

????????dbms_output.put_line(v_start);

????????v_start := v_start + 1;

end loop;

end;

6.8.3 for循環(huán)

for 變量名稱(chēng) in 變量的初始值..結(jié)束值 loop

循環(huán)語(yǔ)句;

end loop;

-- 循環(huán)輸出 1 ~ 指定值，用戶數(shù)指定值

declare

????????v_end number;

begin

????????v_end :=&請(qǐng)輸入結(jié)束的數(shù)字;

for v_start in 1..v_end loop

????????dbms_output.put_line(v_start);

end loop;

end;

6.8.4 分支語(yǔ)句

6.8.4.1 if語(yǔ)句

if 條件 then

滿足條件，執(zhí)行這里;

end if;

-- 判斷能否談對(duì)象

declare

????????age number;

????????begin age :=&請(qǐng)輸入年齡;

if age >= 15 then

????????dbms_output.put_line('可以談戀愛(ài)了');

end if;

end;

6.8.4.2 if...else

declare

????????age number;

begin

????????age :=&請(qǐng)輸入年齡;

????????if age >= 15 then

????????dbms_output.put_line('可以談戀愛(ài)了');

????????else

????????dbms_output.put_line('不可以談戀愛(ài)');

????????end if;

end;

6.8.4.3 if..elsif..else

declare

????????age number;

begin

????????age :=&請(qǐng)輸入年齡;

????????if age >= 15 then

????????dbms_output.put_line('可以談戀愛(ài)了');

????????elsif

????????age <= 6 then -- 不要忘了then

????????dbms_output.put_line('好好讀書(shū)！');

????????else

????????dbms_output.put_line('輸入范圍內(nèi)數(shù)字');

????????end if;

exception

????????when VALUE_ERROR then

????????dbms_output.put_line('數(shù)據(jù)不合法');

end;

6.8.4.4 goto 標(biāo)記點(diǎn)的用法

-- 打印偶數(shù)

declare

????????i number;

????????j number; begin i :=0;

-- 循環(huán)開(kāi)始的默認(rèn)值

loop

????????<> -- 標(biāo)記點(diǎn)

????????i := i+1; j :=&請(qǐng)輸入;

if mod(i,2)=1 then -- 通過(guò)求余函數(shù)判斷是奇數(shù)還是偶數(shù)

????????goto fo; -- 如果不是奇數(shù)就往下執(zhí)行，如果是奇數(shù)重來(lái)

????????end if;

????????exit

????????????????when i > j; -- 退出goto的條件

????????????????dbms_output.put_line(i);

????????????????end loop;

end;

7 函數(shù)、用戶命令、游標(biāo)、存儲(chǔ)過(guò)程

7.1 什么是函數(shù)

作為編程范式的一個(gè)組成部分，一段可重復(fù)使用的代碼塊，接收參數(shù)，并返回一個(gè)具體的值

7.1.1 函數(shù)使用

函數(shù)定義

create [or replace] function 函數(shù)名 (參數(shù) 類(lèi)型1,參數(shù)2 類(lèi)型2,...)

return 返回值類(lèi)型

is/as

???????? [定義變量]

begin

????????代碼塊

????????return 結(jié)果;

exception

????????...

end;

函數(shù)的參數(shù)模式只能是 in 模式，可以省略 in函數(shù)聲明的時(shí)候，必須使用 return 加返回值類(lèi)型return 的返回值類(lèi)型只需要告訴類(lèi)型，不需要定義長(zhǎng)度 return varchar2 變量: 數(shù)據(jù)類(lèi)型 varchar2(30)函數(shù)的結(jié)果必須通過(guò) return 返回出去，函數(shù)要有返回值

舉例

-- 無(wú)參函數(shù)

create or replace function sayHello

return varchar2

????????is begin return 'Hello World!';

end; select sayHello() from dual;

-- 有參函數(shù)

create or replace function sayHy(sname in varchar2)

return varchar2

is

????????rec varchar2(50);

begin

????????if sname is null or sname = '' then

????????????????rec := 'Hello!';

????????else

????????????????rec := 'Hello,'||sname;

????????end if;

????????return rec;

end;

select sayHy('') from dual;

練習(xí)

-- 根據(jù)員工的編號(hào)，計(jì)算員工的年收入（工資 + 獎(jiǎng)金）* 12

create or replace function calculateyearlyIncome(eno in emp.empno%type)

return number

is

????????v_sal emp.sal%type;

????????v_comm emp.comm%type;

begin

????????select sal,comm into v_sal,v_comm from emp where empno = eno;

????????if v_sal is null then

????????????????v_sal :=0;

????????end if;

????????if v_comm is null then

????????????????v_comm :=0;

????????end if;

????????return (v_sal + v_comm) * 12;

????????end;

select e.*,calculateyearlyIncome(e.empno) as 年收入 from emp e;

-- 傳入時(shí)間，返回入職時(shí)間比這個(gè)時(shí)間早的所有員工的平均工資

create or replace function calculateAvgSal(hdate in emp.hiredate%type)

return number

is

????????avg_sal number;

begin

????????select avg(sal) into avg_sal from emp where hiredate < hdate;

????????return avg_sal;

end;

select calculateAvgSal(TO_DATE('2000-01-01','YYYY-MM-DD')) as 平均工資 from dual;

7.2 查看所有用戶

select * from all_users;

7.2.1 創(chuàng)建用戶

需要管理員用戶完成，Oracle用戶分為三種身份

normal：普通用戶sysoper：數(shù)據(jù)庫(kù)操作員，可以打開(kāi)關(guān)閉數(shù)據(jù)庫(kù)服務(wù)器、備份數(shù)據(jù)庫(kù)、恢復(fù)數(shù)據(jù)、日志歸檔...sysdba：數(shù)據(jù)庫(kù)管理員，管理數(shù)據(jù)庫(kù)、管理用戶

create user 用戶名 identified by "密碼";

7.2.2 刪除用戶

drop user 用戶名 cascade;

7.2.3 解鎖用戶

unlock：解鎖

lock：鎖定

alter user 用戶名 account unlock;

7.2.4 修改密碼

alter user 用戶名 identified by "密碼";

7.2.5 權(quán)限

數(shù)據(jù)庫(kù)管理權(quán)限

create session：登錄權(quán)限

create table：創(chuàng)建表的權(quán)限

create index：創(chuàng)建索引的權(quán)限

create view：創(chuàng)建視圖的權(quán)限

create sequence：創(chuàng)建序列的權(quán)限

7.2.6 數(shù)據(jù)操作權(quán)限

insert：插入數(shù)據(jù)的權(quán)限

delete：刪除數(shù)據(jù)的權(quán)限

update：修改數(shù)據(jù)的權(quán)限

select：查詢數(shù)據(jù)的權(quán)限

舉例

-- 賦予權(quán)限

grant 權(quán)限名 to 用戶名;

-- 收回權(quán)限

revoke 權(quán)限名 from 用戶名;

7.2.7 角色

把很多權(quán)限組合成一個(gè)角色，然后將該角色賦給某個(gè)用戶，那么這個(gè)用戶就會(huì)擁有這個(gè)角色的權(quán)限

-- 授權(quán)

grant 權(quán)限名 on 表名 to 用戶1,用戶2....;

--

grant select,insert,update on CCOL$ to jack,yxy;

-- 收回權(quán)限

revoke 權(quán)限名 on 表名 from 用戶1,用戶2....;

有一些內(nèi)置的角色，connect、dba可以將權(quán)限賦給用戶

grant 內(nèi)置角色名 to 用戶名;

7.3 什么是游標(biāo)

游標(biāo)是SQL的內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式進(jìn)行定義

作用

用于臨時(shí)存儲(chǔ)從數(shù)據(jù)庫(kù)中提取的數(shù)據(jù)塊

7.3.1 分類(lèi)

顯示游標(biāo)、隱式游標(biāo)

舉例

select into 一次只能從數(shù)據(jù)庫(kù) 提取一行數(shù)據(jù)

declare

????????v_ename varchar2(50);

????????v_deptno number(10);

begin

????????v_deptno :=&plaseput;

????????select into v_ename from emp where deptno = v_deptno;

????????dbms_output.put_line(v_ename);

end;

/

7.3.1.1 隱式游標(biāo)

以下內(nèi)容會(huì)觸發(fā)隱式游標(biāo)

-- 插入數(shù)據(jù)

insert into testseq(sid,sname,sage,ssex)

???????? values(test_sid.nextval,'王五',21,'男');

-- 更新數(shù)據(jù)

update testseq set sname = '王wu' where sname = '王五'

-- 刪除數(shù)據(jù)

delete from testseq where sname = '王wu'

-- into 單行查詢

declare

????????v_ename varchar2(50);

????????v_empno number(10);

begin

????????v_empno :=&plaseput;

????????select ename into v_ename from emp where empno = v_empno; ????????dbms_output.put_line(v_ename);

end;

/

屬性

屬性 返回值類(lèi)型 含義 sql%rowcount 整形 代表DML語(yǔ)句成功執(zhí)行的數(shù)據(jù)行數(shù) sql%found 布爾型 值為true表是插入、刪除、更新、單行查詢等成功 sql%notfound 布爾型 值為true表是插入、刪除、更新、單行查詢等未找到所匹配的行 sdql%isopen 布爾型 在DML語(yǔ)句執(zhí)行過(guò)程為真執(zhí)行結(jié)束為假

用于判斷DML語(yǔ)句的執(zhí)行結(jié)果以及控制程序的流程

舉例

sql%rowcount 獲取 插入、更新、刪除操作影響的行數(shù)

-- 刪除 student 表中年齡大于等于 20 的人

-- 設(shè)置回滾點(diǎn)

savepoint a;

declare

????????row_count number;

begin

????????delete from student where sage >= 20;

????????-- 設(shè)置隱式游標(biāo)

????????row_count := sql%rowcount;

????????dbms_output.put_line('刪除成功'||row_count||'行數(shù)！');

end;

-- 回滾

rollback to a;

sql%found 判斷 DML 操作是否成功

-- 查詢 1005 號(hào)學(xué)生的姓名，判斷是否查詢成功

declare

????????s_sname varchar2(50);

begin

????????select sname into s_sname from student where sid = 10005;

????????if sql%found then

????????dbms_output.put_line('找到了：'||s_sname||'!');

????????else

????????????????dbms_output.put_line('沒(méi)找到！');

????????end if;

end;

/

sql%notfound 判斷 DML 操作是否找到任何一個(gè)匹配的行

-- 查詢 10018 號(hào)學(xué)生的姓名，判斷是否查詢成功

declare

????????s_sname varchar2(50);

begin

????????select sname into s_sname from student where sid = 10005;

????????if sql%notfound then

????????dbms_output.put_line('沒(méi)找到！');

????????else

????????dbms_output.put_line('找到了：'||s_sname||'!');

????????end if;

end;

/

-- 異常

declare

????????s_sname varchar2(50);

begin

????????select sname into s_sname from student where sid = 10019;

????????dbms_output.put_line('找到了：'||s_sname||'!');

exception

????????when no_data_found then

????????????????dbms_output.put_line('沒(méi)找到！');

end;

/

sdql%isopen 檢查隱式游標(biāo)在 DML 操作時(shí)的狀態(tài)

-- 假設(shè)游標(biāo) c_student 查詢了學(xué)生的姓名

-- fetch 語(yǔ)句 into 子句 與查詢的列數(shù)量匹配

-- 將結(jié)果賦值給變量 s_sname ，并輸出學(xué)生的姓名

-- 不使用 sql%isopen 來(lái)判斷游標(biāo)是否處于打開(kāi)狀態(tài)

-- 而是直接進(jìn)入循環(huán)并使用 c_student%notfound 作為循環(huán)退出的條件

-- 使用顯示游標(biāo)

declare

????????cursor c_student is

????????????????select sname from student;

????????s_sname varchar2(50);

begin

????????open c_student;

????????loop

????????????????fetch c_student into s_sname;

????????????????-- 當(dāng)沒(méi)有值可以獲取的時(shí)候退出循環(huán)

????????????????exit when c_student%notfound;

????????????????dbms_output.put_line('學(xué)生姓名：'||s_sname);

????????????????end loop;

????????????????dbms_output.put_line('未找到');

????????????????close c_student;

end;

/

-- 使用隱式游標(biāo)

declare

????????cursor c_student is

????????????????select sname from student;

????????s_sname varchar2(50);

begin

????????open c_student;

????????loop

????????????????fetch c_student into s_sname;

????????????????-- 當(dāng)沒(méi)有值可以獲取的時(shí)候退出循環(huán)

????????????????exit when c_student%notfound;

????????????????dbms_output.put_line('學(xué)生姓名：'||s_sname);

????????????????end loop;

????????????????dbms_output.put_line('游標(biāo)已關(guān)閉');

????????????????if c_student%isopen then

????????????????close c_student;

????????????????end if;

end;

/

7.3.1.2顯示游標(biāo)

需要我們自己手動(dòng)聲明、打開(kāi)、提取數(shù)據(jù)、關(guān)閉

-- 聲明游標(biāo)

cursor 游標(biāo)名 is select ...

-- 打開(kāi)游標(biāo)

open 游標(biāo)名

-- 提取數(shù)據(jù)

fetch 游標(biāo)名 into [v1,v2....]

fetch 游標(biāo)名 into v1

-- 關(guān)閉游標(biāo)

close 游標(biāo)名

-- 提取 SMITH 的數(shù)據(jù)

-- 提取 student 表中的 10001 的姓名和籍貫

declare

????????v_sname varchar2(10);

????????v_snativeplace varchar(10);

????????-- 聲明游標(biāo)

????????cursor stu_var is

????????????????select sname,snativeplace from student;

begin

????????-- 打開(kāi)游標(biāo)

????????open stu_var;

????????-- 提取數(shù)據(jù)

????????fetch stu_var into v_sname,v_snativeplace;

????????dbms_output.put_line(v_sname||','||v_snativeplace);

????????close stu_var;

end;

/

7.3.2 for循環(huán)游標(biāo)

-- 輸出 empno、job、ename

declare

????????cursor e_emp is

????????????????select * from emp;

????????????????-- 聲明和emp表中字段、類(lèi)型相同的變連

????????????????empInfo emp%rowtype;

????????????????cou number;

begin

????????-- 循環(huán)游標(biāo)e_emp，把值給empInfo

????????????????for empInfo in e_emp loop

????????-- 值在傳給cou

????????cou := e_emp%rowcount;

????????dbms_output.put_line(cou||'雇員編號(hào)：'||empInfo.empno||'，雇員工作：'||empInfo.job||',雇員名字：'||empInfo.ename);

????????end loop;

end;

7.3.3 while循環(huán)游標(biāo)

-- 輸出 empno、ename

declare

????????cursor mycur is select * from emp;

????????empInfo emp%rowtype;

begin

????????open mycur;

????????-- 使游標(biāo)指向下一行

????????fetch mycur into empInfo;

????????-- 判斷游標(biāo)的這一行是否有數(shù)據(jù)

????????while(mycur%found) loop

????????dbms_output.put_line('雇員編號(hào)：'||empInfo.empno||',雇員名字：'||empInfo.ename); fetch mycur into empInfo;

????????end loop;

end;

7.3.4 loop循環(huán)游標(biāo)

-- 輸出 empno、ename

declare

????????cursor mycur is select * from emp;

????????empInfo emp%rowtype;

begin

????????open mycur;

????????loop

????????fetch mycur into empInfo;

????????exit when mycur%notfound;

????????dbms_output.put_line('雇員編號(hào)：'||empInfo.empno||',雇員名字：'||empInfo.ename);

????????end loop;

end;

練習(xí)

-- 一次性上漲全部人的工資。根據(jù)他所在的部門(mén)上漲工資

-- 10 ：上漲 10%

-- 20 ：上漲 20%

-- 30 ：上漲 30%

-- 雖然上漲工資，但是最高不能超過(guò) 5000，如果工資超過(guò) 5000 那么工資就是5000

-- 通過(guò)查詢判斷是否成功 select * from emp;

declare

????????cursor c_emp is select empno,sal,deptno from emp;

????????v_empno emp.empno%type;

????????v_deptno emp.deptno%type;

????????v_sal emp.sal%type;

begin

????????for rec in c_emp loop

????????????????v_empno := rec.empno;

????????????????v_deptno := rec.deptno;

????????????????v_sal := rec.sal;

?????????????????-- 調(diào)整工資

????????????????CASE v_deptno

????????????????when 10 then

????????????????????????v_sal := least(v_sal + (v_sal * 0.1),5000);

????????????????when 20 then

????????????????????????v_sal := least(v_sal + (v_sal * 0.2),5000);

????????????????when 30 then

????????????????????????v_sal := least(v_sal + (v_sal * 0.3),5000);

????????????????else v_sal := v_sal * 1;

????????????????end case;

????????????????update emp set sal = v_sal where empno = v_empno;

????????????????end loop;

????????????????commit;

end;

7.4 什么是存儲(chǔ)過(guò)程

封裝了一段或者多段sql語(yǔ)句的pl/sql代碼塊

存儲(chǔ)過(guò)程的優(yōu)點(diǎn)

簡(jiǎn)化復(fù)雜操作：將很多條的sql語(yǔ)句封裝為一個(gè)獨(dú)立的單元，使用時(shí)只需要去調(diào)用該單元即可增加數(shù)據(jù)的獨(dú)立性：將數(shù)據(jù)庫(kù)基礎(chǔ)數(shù)據(jù)和程序隔離開(kāi)，基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)變化時(shí)，只需要存儲(chǔ)過(guò)程不需要修改原代碼提高安全性：減少了執(zhí)行多條sql語(yǔ)句報(bào)錯(cuò)提高性能：只需要編譯一次即可運(yùn)行多條sql語(yǔ)句

存儲(chǔ)過(guò)程的參數(shù)

in：定義輸入?yún)?shù)，用于傳遞參數(shù)給存儲(chǔ)過(guò)程out：定義輸出參數(shù)，用于從存儲(chǔ)過(guò)程獲取參數(shù)in out：定義輸入輸出參數(shù)

?7.4.1?沒(méi)有參數(shù)的存儲(chǔ)過(guò)程

-- 給指定的編號(hào)年齡添加2歲

-- stu_pro 是存儲(chǔ)過(guò)程名

create or replace procedure stu_pro

is

begin

????????update student set sage = sage + 2 where sid = 10001;

????????commit;

????????dbms_output.put_line('修改成功！');

end;

-- 調(diào)用存儲(chǔ)過(guò)程

begin

stu_pro;

end;

-- 查詢結(jié)果

select * from student;

7.4.2 有參數(shù)的存儲(chǔ)過(guò)程

-- 指定人添加指定年齡 -- 定義存儲(chǔ)過(guò)程并且傳入兩個(gè)參數(shù) var_1 和 var_2

create or replace procedure pro_stu(var_1 in varchar2,var_2 in number)

is

begin

update student set sage = sage + var_2 where sname = var_1;

commit;

dbms_output.put_line(var_1||'年齡增加了'||var_2||'歲！');

end;

declare

begin

????????pro_stu('李四',2);

end;

7.4.3 out 和 in out參數(shù)的存儲(chǔ)過(guò)程

create or replace procedure pro_out

(var_1 in out number,var_2 out student.sname%type,var_3 out student.sage%type)

is

begin

????????select sname,sage into var_2,var_3 from student where sid = var_1;

????????end;

declare

????????ex_var_1 number;

????????ex_var_2 student.sname%type;

????????ex_var_3 student.sage%type;

begin

????????ex_var_1 :=&請(qǐng)輸入sid;

????????pro_out(ex_var_1,ex_var_2,ex_var_3);

????????dbms_output.put_line('學(xué)號(hào)為：'||ex_var_1||'，姓名是：'||ex_var_2||',年齡是：'||ex_var_3);

end;

7.4.4 刪除存儲(chǔ)過(guò)程

drop procedure 存儲(chǔ)過(guò)程名

函數(shù)和存儲(chǔ)過(guò)程的區(qū)別

函數(shù)主要用于返回計(jì)算結(jié)果，存儲(chǔ)過(guò)程執(zhí)行sql語(yǔ)句關(guān)鍵字不一樣，函數(shù)function，存儲(chǔ)過(guò)程是procedure存儲(chǔ)過(guò)程可以由輸入輸出的參數(shù)，函數(shù)只有輸入的參數(shù)函數(shù)有返回值，存儲(chǔ)過(guò)程沒(méi)有存儲(chǔ)過(guò)程調(diào)用在plsql的代碼塊里，只能在sql語(yǔ)句

8 數(shù)據(jù)字典、數(shù)據(jù)庫(kù)設(shè)計(jì)、觸發(fā)器、in和existi

8.1 什么是數(shù)據(jù)字典？

提供了對(duì)數(shù)據(jù)結(jié)構(gòu)和對(duì)象元數(shù)據(jù)信息的查詢和訪問(wèn)，幫助你了解和管理數(shù)據(jù)庫(kù)的各個(gè)方面

舉例

-- 查詢 scott 用戶下所有表

select TABLE_NAME from all_tables where owner = 'SCOTT';

-- 查詢 emp 表中所有的字段

select * from all_tab_columns where TABLE_NAME = 'EMP';

-- 列出 emp 表的 索引列

select * from all_ind_columns where TABLE_NAME = 'EMP'

-- 列出 emp 表的 約束

select * from all_constraints where TABLE_NAME = 'EMP'

8.2 什么是數(shù)據(jù)庫(kù)三大范式？

第一范式：屬性不可再分

第二范式：在第一范式的基礎(chǔ)上，屬性完全依賴(lài)于主鍵

第三范式：在第二范式的基礎(chǔ)上，屬性不依賴(lài)于其他非主鍵屬性

舉例

第一范式

以上內(nèi)容就不符合，聯(lián)系方式字段還可以再分

修改

第二范式

互相之間都可以作為主鍵并且互相依賴(lài)，這是違背第二范式

修改

保證每張表只有一個(gè)主鍵依賴(lài)

?

第三范式

其他的屬性不可以當(dāng)主鍵，只有一個(gè)屬性可以當(dāng)作主鍵

舉例

-- 建表 -- 建一個(gè)訂單表，含有訂單內(nèi)容的信息

?CREATE TABLE orders1(

????????order_id NUMBER PRIMARY KEY,

????????customer_id NUMBER,

????????order_date DATE,

????????total_amount NUMBER,

????????CONSTRAINT fk_orders_customer

????????FOREIGN KEY(customer_id)

????????REFERENCES customers1(customer_id)

);

-- 建一個(gè)客戶表，含有客戶信息

CREATE TABLE customers1(

????????customer_id NUMBER PRIMARY KEY,

????????customer_name VARCHAR2(100),

????????customer_email VARCHAR2(100),

????????customer_phone VARCHAR2(100)

);

修改

查看是否存在能夠單獨(dú)當(dāng)主鍵的列或者會(huì)造成多個(gè)數(shù)據(jù)冗雜的列，需要單獨(dú)拎出來(lái)建表

-- 建一個(gè)訂單表，含有訂單內(nèi)容的信息

CREATE TABLE orders1(

????????order_id NUMBER PRIMARY KEY,

????????customer_id NUMBER,

????????order_date DATE,

????????CONSTRAINT fk_orders_customer

????????FOREIGN KEY(customer_id)

????????REFERENCES customers1(customer_id)

);

-- 建一個(gè)客戶表，含有客戶信息

CREATE TABLE customers1(

????????customer_id NUMBER PRIMARY KEY,

????????customer_name VARCHAR2(100)

);

-- detail

CREATE TABLE detail1(

????????order_id NUMBER,

????????item_id NUMBER,

????????item_quantity NUMBER,

????????item_price NUMBER,

????????CONSTRAINT pk_order_detail

????????PRIMARY KEY (order_id,item_id),

????????CONSTRAINT fk_order_detail

????????FOREIGN KEY(order_id)

????????REFERENCES order1(order_id),

????????CONSTRAINT fk_order_detail

????????FOREIGN KEY(item_id)

????????REFERENCES item1(item_id)

);

-- item

CREATE TABLE item1(

????????item_id NUMBER PRIMARY KEY,

????????item_name VARCHAR2(100),

????????item_description VARCHAR2(100),

????????item_price NUMBER

);

8.3 什么是觸發(fā)器？

本身是一種數(shù)據(jù)庫(kù)的對(duì)象，用于在指定的事件發(fā)生的時(shí)候會(huì)自動(dòng)執(zhí)行一段plsql代碼

功能

允許/限制對(duì)表的修改自動(dòng)生成派生列，自增字段強(qiáng)制保證數(shù)據(jù)的統(tǒng)一性提供審計(jì)和日志記錄防止無(wú)效的事務(wù)處理，為了避免鎖的發(fā)生啟用復(fù)雜的業(yè)務(wù)邏輯

語(yǔ)法

create or replace trigger 觸發(fā)器名

{before | after} {insert | update | delete} o

n 表名

[for each row]

begin

????????plsql代碼

end;

觸發(fā)時(shí)間：{before | after}

指明觸發(fā)器何時(shí)執(zhí)行

before：在數(shù)據(jù)庫(kù)操作之前

after：在數(shù)據(jù)庫(kù)操作之后

觸發(fā)事件：{insert | update | delete}

指明哪些數(shù)據(jù)庫(kù)的操作會(huì)觸發(fā)此觸發(fā)器

for each row：表示觸發(fā)器為每一行數(shù)據(jù)執(zhí)行一次，如果省略此選項(xiàng)觸發(fā)器只會(huì)執(zhí)行一次

舉例

-- 更新學(xué)生表之前觸發(fā)，限制不允許在周末修改表

create or replace trigger auth_stu

before insert or update or delete

on student

begin

-- 這里添加的是判斷條件

????????if (to_char(sysdate,'DY') = '星期二') then

????????????????raise_application_error(-20600,'不能修改');

????????????????end if;

????????end;

-- 在下午兩點(diǎn)前不允許插入數(shù)據(jù)

create or replace trigger time_stu

????????before insert on test_log

????????for each row

declare

????????current_time timestamp;

begin

????????current_time := systimestamp;

????????if current_time < timestamp '2023-08-01 14:00:00' then

????????????????raise_application_error(-20001,'不能插入');

????????end if;

end;

我們對(duì) test1 執(zhí)行操作，將記錄保存在 test_long內(nèi)

create table test1(

????????t_id number(4),

????????t_name varchar2(20),

????????t_age number(2),

????????t_sex char

);

create table test_log(

????????l_user varchar2(15),

????????l_type varchar2(15),

????????l_date varchar2(30)

);

-- 對(duì)test1 進(jìn)行操作，將操作日志保存在 test_log 內(nèi)

create or replace trigger test1_log

????????after delete or insert or update on test1

declare

????????v_type varchar2(15);

begin

????????if inserting then

????????????????v_type := 'insert';

????????????????dbms_output.put_line('已記錄');

????????elsif updating then

????????????????v_type := 'update';

????????????????dbms_output.put_line('已記錄');

????????elsif deleting then

????????????????v_type := 'delete';

????????????????dbms_output.put_line('已記錄');

????????end if;

insert into test_log

????????values(user,v_type,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

end;

select * from test1

select * from test_log

insert into test1 values(103,'楊芯葉',19,'男');

update test1 set t_age = 20 where t_age = 19;

delete test1 where t_id = 103

8.3 exists

邏輯計(jì)算符，exists 用于判斷子查詢返回的結(jié)果是否為空，如果不為空 exists 成立，主sql語(yǔ)句會(huì)執(zhí)行，反之不執(zhí)行

舉例

-- 如果部門(mén)名稱(chēng)中含有字母A，則查詢所有員工的信息

select * from emp where exists (

select * from dept where ename like '%A%' and deptno = emp.deptno)

not exists

-- 如果平均工資不小于 1500 的部門(mén)信息，則查詢所有部門(mén)信息(not exists)

select * from dept

where not exists (

select deptno from emp

where deptno = dept.deptno

group by deptno

having avg(sal) < 1500)

8.5 In

邏輯運(yùn)算符，用于判斷一個(gè)值是否存在與子查詢的結(jié)果集中，如果存在條件成立，主SQL語(yǔ)句執(zhí)行

如何選擇in和existi

假設(shè)B表做子查詢

A表有10000條記錄，B表有1000000條記錄，那么會(huì)遍歷10000 * 1000000次，效率很差，existsA表有10000條記錄，B表有100條，in比較快

結(jié)論

如果 子表 比 父表的數(shù)據(jù)多，用 exists，反之用 in此時(shí)主查詢有索引，in比較快not in 和 not exists，not exists 最快

練習(xí)

-- 返回?zé)o論是經(jīng)理還是非經(jīng)理，都沒(méi)有下屬的員工信息(not in)

select * from emp e

where e.empno not in (

select distinct(mgr) from emp)

-- 返回至少在一個(gè)職位上有員工的部門(mén)名稱(chēng)和地點(diǎn)

select d.dname,d.loc from dept d

where exists (

select 1 from emp e where e.deptno = d.deptno)

柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù)基礎(chǔ)教程(Oracle)

http://yzkb.51969.com/

參考閱讀