版權聲明：本文為部落客原創文章，遵循 CC 4.0 BY-SA 版權協定，轉載請附上原文出處連結和本聲明。
本文連結：https://blog.csdn.net/zhao1299002788/article/details/101757751
1 : Oracle 簡介 : 是美國ORACLE公司(甲骨文)提供的以分布式資料庫為核心的一組軟體産品,是目前最流行的客戶/伺服器IP，端口，使用者名、密碼，點選：連接配接
(CLIENT/SERVER)或B/S體系結構的資料之一.
2 : Oracle資料庫的體系結構:
資料庫: database
Oracle資料庫是資料的實體存儲.這就包括(資料檔案ORA或者DBF,控制檔案,聯機日志,參數檔案).其實Orcale資料庫的概念和其他資料不一樣,這裡的資料庫是一個作業系統隻有一個庫.可以
看做是Orcale就隻有一個大資料庫.
例如 :一個Oracle執行個體有一系列的背景程序和記憶體結構組成.一個資料庫可以有n個執行個體.
資料檔案(dbf) :
資料檔案是資料庫的實體存儲機關.資料庫的資料是存儲在表空間中的,真正是在某一個或者多個資料檔案中.而一個表空間可以由一個或多個資料檔案組成,一個資料檔案隻能屬于一個表空間.
一旦資料檔案被加入到某個表空間後,就不能删除這個檔案,如果要删除某個資料檔案,隻能删除其所屬于的表空間才行.
表空間 :
表空間是Oracle對實體資料庫上相關資料檔案(ORA或者DBF檔案)的邏輯映射.一個資料庫再邏輯上被劃分成一到若幹個表空間,每個表空間包含了在邏輯上相關聯的一組結構.每個資料庫至少有一個表
空間(稱之為system表空間).
每個表空間由同一磁盤上的一個或多個檔案組成,這些檔案叫資料檔案(datafile).一個資料檔案隻能屬于一個表空間.
使用者 :
使用者是在執行個體下建立的.不同執行個體中可以建相同名字的使用者.表的資料,是使用者放入某一個表空間的,而這個表空間會随機把這些表資料放到一個或者多個資料檔案中.
由于oracle的資料庫不是普通的概念,oracle是有使用者和表空間對資料進行管理和存放的.但是表不是有表空間去查詢的,而是由使用者去查的.因為不同使用者可以在同一個表空間建立同一個名字的表,表裡區分就是使用者了.
Oracle 應用開發實戰
一、Oracle 的基本概念和安裝
Oracle 簡介
ORACLE 資料庫系統是美國ORACLE 公司（甲骨文）提供的以分布式資料庫為核心的一
組軟體産品，是目前最流行的客戶/伺服器(CLIENT/SERVER)或B/S 體系結構的資料庫之一。
比如SilverStream 就是基于資料庫的一種中間件。ORACLE 資料庫是目前世界上使用最為廣
泛的資料庫管理系統，作為一個通用的資料庫系統，它具有完整的資料管理功能；作為一個
關系資料庫，它是一個完備關系的産品；作為分布式資料庫它實作了分布式處理功能。但它
的所有知識，隻要在一種機型上學習了ORACLE 知識，便能在各種類型的機器上使用它。
Oracle10g 的安裝
1. 解壓oracle 資料庫安裝包，如果是win7 或者win8 系統右鍵點選setup.exe 選擇相容性，
以xp 方式，并且以管理者方式運作，以及其他所有使用者都按着此規則如圖
2. 如果是xp 系統可以直接并輕按兩下解壓目錄下的setup.exe，出現安裝界面，如下：
3. 輸入密碼和确認密碼，如：baidu，點選下一步，出現如下進度條，
注：此密碼即是管理者密碼。
11
4. 檢查先決條件，選中紅框所示的選擇框，如下圖：
5. 點選“下一步”，出現“概要”界面，點選“安裝”。
11
6. 出現安裝進度條，等待安裝完成，如下圖：
7. 安裝完成後，自動運作配置向導，如下圖，等待其完成：
8. 完成後，出現“密碼管理”界面，點選“密碼管理”，如下圖：
11
9. 将SCOTT 和HR 使用者的溝去掉（解鎖這兩個賬戶），如下圖所示，點選“确定”：
10. 回到“密碼管理”界面，點選“确定”，如下圖：
11. 安裝結束，點選“退出”。
11
虛拟網卡設定
本機和虛拟機之間能互相通路，它們的IP 段必須相同，但是本機将會連接配接不同的網絡
環境（比如教室、宿舍、家庭），那麼本機的IP 段會産生變化就連不上虛拟機了，為了避
免這種情況我們讓本機和虛拟機之間用虛拟網卡的方式互相通信，配置方式參考如下文檔：
PLSQL Developer 用戶端工具的安裝
1. 網絡的測試
參考：
11
2. 安裝PLSQL Developer 用戶端
3. 中文亂碼的處理
二、Oracle 資料庫的體系結構
資料庫：database
Oracle 資料庫是資料的實體存儲。這就包括（資料檔案ORA 或者DBF、
控制檔案、聯機日志、參數檔案）。其實Oracle 資料庫的概念和其它
資料庫不一樣，這裡的資料庫是一個作業系統隻有一個庫。可以看作
是Oracle 就隻有一個大資料庫。
執行個體：
11
一個Oracle 執行個體（Oracle Instance）有一系列的背景程序（Backguound
Processes)和記憶體結構（Memory Structures)組成。一個資料庫可以有n
個執行個體。
資料檔案（dbf）:
資料檔案是資料庫的實體存儲機關。資料庫的資料是存儲在表空間中
的，真正是在某一個或者多個資料檔案中。而一個表空間可以由一個
或多個資料檔案組成，一個資料檔案隻能屬于一個表空間。一旦資料
檔案被加入到某個表空間後，就不能删除這個檔案，如果要删除某個
資料檔案，隻能删除其所屬于的表空間才行。
表空間：
表空間是Oracle 對實體資料庫上相關資料檔案（ORA 或者DBF 檔案）
的邏輯映射。一個資料庫在邏輯上被劃分成一到若幹個表空間，每個
表空間包含了在邏輯上相關聯的一組結構。每個資料庫至少有一個表
空間(稱之為system 表空間)。
每個表空間由同一磁盤上的一個或多個檔案組成，這些檔案叫資料文
件(datafile)。一個資料檔案隻能屬于一個表空間。
使用者：
使用者是在執行個體下建立的。不同執行個體中可以建相同名字的使用者。
注： 表的資料，是有使用者放入某一個表空間的，而這個表空間會随機把這
些表資料放到一個或者多個資料檔案中。
由于oracle 的資料庫不是普通的概念，oracle 是有使用者和表空間對
資料進行管理和存放的。但是表不是有表空間去查詢的，而是由使用者
去查的。因為不同使用者可以在同一個表空間建立同一個名字的表！這
裡區分就是使用者了！
11
SCOTT 使用者和HR 使用者
Oracle 為了讓學習者更好的進行學習，在安裝成功後，也建立了初始的使用者，其中SCOTT
與HR 就是初始的普通使用者。這些使用者下面都預設存在了表結構，我們重點掌握SCOTT 使用者
下的所有表，如下所示：
SCOTT 使用者下的表
11
HR 使用者下的表
11
三、基本查詢
sql 簡介
結構化查詢語言(Structured Query Language)簡稱SQL(發音：/ˈɛs kjuː ˈɛl/ "S-Q-L")，結構
化查詢語言是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系數
據庫系統；同時也是資料庫腳本檔案的擴充名。結構化查詢語言是進階的非過程化程式設計語言，
允許使用者在高層資料結構上工作。它不要求使用者指定對資料的存放方法，也不需要使用者了解
具體的資料存放方式，是以具有完全不同底層結構的不同資料庫系統, 可以使用相同的結構
化查詢語言作為資料輸入與管理的接口。結構化查詢語言語句可以嵌套，這使它具有極大的
靈活性和強大的功能。
DML(資料庫操作語言): 其語句包括動詞INSERT，UPDATE 和DELETE。它們分别用于添
加，修改和删除表中的行。也稱為動作查詢語言。
DDL(資料庫定義語言): 其語句包括動詞CREATE 和DROP。在資料庫中建立新表或删除
表（CREAT TABLE 或DROP TABLE）；為表加入索引等。DDL 包括許多與人資料庫目錄
中獲得資料有關的保留字。它也是動作查詢的一部分。
DCL(資料庫控制語言):它的語句通過GRANT 或REVOKE 獲得許可，确定單個使用者和使用者
組對資料庫對象的通路。某些RDBMS 可用GRANT 或REVOKE 控制對表單個列的訪
問。
Select 語句的文法格式和示例
1. 查詢文法
Select * |列名from 表名
11
2.别名用法
在查詢的結果列中可以使用别名
Select 列名别名，列名别名，... from emp;
别名中，有沒有雙引号的差別就在于别名中有沒有特殊的符号或者關鍵字。
3.消除重複的資料
Select distinct *|列名, ... from emp;
使用distinct 可以消除重複的行，如果查詢多列的必須保證多列都重複才能去掉重複
4. 查詢中四則運算
查詢每個雇員的年薪
select ename, sal*12 from emp;
select ename, sal*12 income from emp;
11
Sql 中支援四則運算“+，-，*，/”
什麼是空值？
空值是無效的，未指定的，未知的或不可預知的值
空值不是空格或者0 。
注意：*、包含null 的表達式都為null
*、空值永遠不等于空值
11
連接配接符||
字元串連接配接查詢
Mysql 中實作方法：
查詢雇員編号，姓名，工作
編号是：7369 的雇員， 姓名是：smith，工作是：clerk
字元串的連接配接使用‘||’
四、條件查詢和排序
使用where 語句對結果進行過濾
比較運算符
11
其他比較運算符
邏輯運算符
Where 語句示例
1. 非空和空的限制
示例：查詢每月能得到獎金的雇員
分析：隻要字段中存在内容表示不為空，如果不存在内容就是null，
文法：列名IS NOT NULL
為空列名IS NULL
11
範例：查詢工資大于1500 并且有獎金領取的雇員
分析：多個查詢條件同時滿足之間使用‘AND’
範例:查詢工資大于1500 或者有獎金的雇員
分析：多個查詢條件或滿足，條件之間使用“OR”
範例：查詢工資不大于1500 和沒有獎金的人
文法：NOT(查詢條件)
2.範圍限制
範例：基本工資大于1500 但是小于3000 的全部雇員
分析：sal>1500, sal<3000
11
Between and 等于sal > =1500 and sal <= 3000
範例：查詢1981-1-1 到1981-12-31 号入職的雇員
分析：between and 不僅可以使用在數值之間，也可以用在日期的區間
範例：查詢雇員名字叫smith 的雇員
在oracle 中的查詢條件中查詢條件的值是區分大小寫的
範例:查詢雇員編号是7369,7499,7521 的雇員編号的具體資訊
如果使用之前的做法可以使用OR 關鍵字
11
實際上，此時指定了查詢範圍，那麼sql 可以使用IN 關鍵字
文法: 列名IN (值1，值2，....)
列名NOT IN (值1， 值2，...)
其中的值不僅可以是數值類型也可以是字元串
範例:查詢雇員姓名是’SMITH’,’ALLEN’,’WARD’的雇員具體資訊
3．模糊查詢
在常用的站點中經常會有模糊查詢，即：輸入一個關鍵字，把符合的内容全部的查詢出
來，在sql 中使用LIKE 語句完成。
在LIKE 中主要使用以下兩種通配符
“%”：可以比對任意長度的内容
“_”:可以比對一個長度的内容
範例：查詢出所有雇員姓名中第二個字元包含“M”的雇員
在LIKE 中如果沒有關鍵字表示查詢全部
11
查詢名字中帶有“M”的雇員
在oracle 中不等号的用法可以有兩種形式“<>”和“!=”
範例：查詢雇員編号不是7369 的雇員資訊
11
使用order by 對結果排序
1.排序的文法
在sql 中可以使用ORDER BY 對查詢結果進行排序
文法：SELECT * |列名FROM 表名{WEHRE 查詢條件} ORDER BY 列名1 ASC|DESC，列名
2...ASC|DESC
範例：查詢雇員的工資從低到高
分析：ORDER BY 列名預設的排序規則是升序排列，可以不指定ASC，如果按着降序排列必
須指定DESC
11
如果存在多個排序字段可以用逗号分隔
注意ORDER BY 語句要放在sql 的最後執行。
2.排序中的空值問題
當排序時有可能存在null 時就會産生問題，我們可以用nulls first , nulls last 來指定null 值
顯示的位置。
--查詢雇員的工資從低到高
select * from emp order by sal nulls first;
select * from emp order by sal desc nulls last ;
11
五、單行函數
什麼是SQL 的函數？
函數的類型
單行函數
11
字元函數
示例：
接收字元輸入傳回字元或者數值，dual 是僞表
1. 字元串的連接配接可以使用concat 可以使用“||”建議使用“||”
concat('hello', 'world')
2. 字元串的截取，使用substr，第一個參數是源字元串，第二個參數是開始索引，第
三個參數長度，開始的索引使用1 和0 效果相同
substr('hello', 1,3)
3. 擷取字元串的長度
length('hello')
4. 字元串替換，第一個參數是源字元串，第二個參數被替換的字元串，第三個是替換
11
字元串
replace('hello', 'l','x')
數值函數
日期函數
Oracle 中的日期：
Oracle 中的日期型資料實際含有兩個值: 日期和時間。
預設的日期格式是DD-MON-RR 。
日期的數學運算
在日期上加上或減去一個數字結果仍為日期
兩個日期相減傳回日期之間相差的天數
可以用數字除24
日期函數示例
1. 範例：查詢雇員的進入公司的周數。
分析：查詢雇員進入公司的天數(sysdate – 入職日期)/7 就是周數
11
2. 獲得兩個時間段中的月數：MONTHS_BETWEEN()
範例：查詢所有雇員進入公司的月數
3. 獲得幾個月後的日期：ADD_MONTHS()
範例：求出三個月後的日期
轉換函數
11
TO_CHAR 函數對日期的轉換
日期的格式：
TO_CHAR 函數對數字的轉換
數字轉換的格式：
TO_NUMBER 和TO_DATE 函數
11
示例：
1. TO_CHAR:字元串轉換函數
範例：查詢所有的雇員将将年月日分開，此時可以使用TO_CHAR 函數來拆分
拆分時需要使用通配符
年：y, 年是四位使用yyyy
月：m, 月是兩位使用mm
日：d, 日是兩位使用dd
在結果中10 以下的月前面被被補了前導零，可以使用fm 去掉前導零
2. TO_NUMBER:數值轉換函數
TO_NUMBER 可以把字元串轉換成數值
3. TO_DATE:日期轉換函數
TO_DATE 可以把字元串的資料轉換成日期類型
11
通用函數
什麼是通用函數？
這些函數适用于任何資料類型，同時也适用于空值
常用的通用函數
通用函數示例
1．空值處理nvl
範例：查詢所有的雇員的年薪
我們發現很多員工的年薪是空的，原因是很多員工的獎金是null，null 和任何數值計算都是
null，這時我們可以使用nvl 來處理。
11
條件表達式
什麼是條件表達式？
在SQL 語句中使用IF-THEN-ELSE
實作的方式：
CASE 表達式：SQL99 的文法，類似Basic，比較繁瑣
DECODE 函數：Oracle 自己的文法，類似Java，比較簡介
CASE 表達式
DECODE 函數
條件表達式示例: 根據10 号部門員工的工資，顯示稅率
六、多行函數
什麼是多行函數？
分組函數作用于一組資料，并對一組資料傳回一個值。
也叫：組函數、分組函數
組函數會忽略空值；NVL 函數使分組函數無法忽略空值
11
常用的多行函數
多行函數示例
1.統計記錄數count()
範例：查詢出所有員工的記錄數
不建議使用count(*)，可以使用一個具體的列以免影響性能。
2.最小值查詢min()
範例：查詢出來員工最低工資
3.最大值查詢max()
範例：查詢出員工的最高工資
11
4.查詢平均值avg()
範例：查詢出員工的平均工資
5.求和函數sum()
範例：查詢出20 号部門的員工的工資總和
分組資料
範例：查詢每個部門的人數
11
範例：查詢出每個部門的平均工資
範例：查詢出來部門編号，和部門下的人數
我們發現報了一個ORA-00937 的錯誤
注意：
1. 如果使用分組函數，SQL 隻可以把GOURP BY 分組條件字段和分組函數查詢出來，
不能有其他字段。
2. 如果使用分組函數，不使用GROUP BY 隻可以查詢出來分組函數的值
過濾分組資料
範例：查詢出部門平均工資大于2000 的部門
11
WHERE 和HAVING 的差別
最大差別在于：where 後面不能有組函數
練習 SQL語句 :
/*
基本查詢
select 列名|* from 表名
*/
select * from emp;
select emp.*,1+1 from emp;
select 1+1 from emp where empno=7369--太麻煩
--擷取系統時間
select sysdate from dual;
-------------------------------------------
/*
dual 虛拟表 沒有實際意義 為了補全oracle資料庫查詢文法
*/
--檢視虛拟表
select * from dual;
select 1+1 from dual;
/*
查詢員工表中所有記錄
*/
select * from emp;
--查詢員工的編号和姓名
select empno,ename from emp;
--給列起别名特殊字元（#$）和數字需要使用雙引号處理
select empno as "員工編号",ename "員工姓名",job 工作 from emp;
select empno "員工!編号",ename "員工姓名",job 工作 from emp;
select empno "員工!編号",ename "員工姓名",job "工 作" from emp;
select empno "員工!編号",ename "員工姓名",job 123 from emp;
select empno "員工!編号",ename "員工姓名",job 工#作 from emp;
--查詢員工的工作 --distinct 關鍵字去重重複記錄
select distinct job from emp;
--查詢員工的年薪 null值參與運算結果為空
--使用nvl函數處理空值 nvl(v1,v2) v1是被判斷的數值 v1為空v2是傳回值 -- 不為空 傳回v1
select sal*12+comm from emp;
select sal*12+nvl(comm,0) 年薪 from emp;
--列值的拼接 員工編号7369員工姓名SMITH
--mySql 可以用一個函數 concat實作
--oracle資料庫使用concat函數實作 oracle隻支援兩個參數做拼接
select concat('員工編号',empno) from emp;
select concat(concat('員工編号',empno),'員工姓名') from emp;--多重嵌套實作麻煩
--oracle資料庫 特有連接配接符 ||
--java '員工編号'+empno+'員工姓名'+ename
select '員工編号: '||empno||'員工姓名: '||ename 員工簡介 from emp;
/*
條件查詢
需要使用關鍵字 where 條件 目的是過濾出滿足條件的記錄
where 後的條件表達式
比較運算表達式 > < >= <= = != <>
邏輯運算 and or not
其他運算 between and 判斷區間 包含邊界
in not in 判斷範圍
like 模糊查詢 % 比對0個或多個
_ 占位一個字元
is null is not null 判斷空值
*/
---查詢員工的工作不是MANAGER的員工資訊
select * from emp where job !='MANAGER';
select * from emp where job <>'MANAGER';
--查詢員工工作是SALESMAN 并且工資>1500的員工資訊
select * from emp where job = 'SALESMAN' and sal >1500
select * from emp where job = 'SALESMAN' or sal >1500
--查詢員工的工資在 1500 和3000範圍内
select * from emp where sal between 1500 and 3000;--會不會提取正好1500和3000？
--查詢員工的工作 是MANAGER 或 PRESIDENT
select * from emp where job ='MANAGER' or job = 'PRESIDENT'
--使用in來實作
select * from emp where job in ('MANAGER','PRESIDENT')
--查詢員工姓名包含M的員工資訊
select * from emp where ename like '%M%'
--查詢員工姓名第二位為M的員工資訊
select * from emp where ename like '_M%'
--查詢有獎金的員工資訊
select * from emp where comm is not null
--過濾獎金為0的資料
select * from emp where comm is not null and comm != 0
select * from emp where comm > 0
/*
根據需求對資料做排序
order by 列 排序類型
倒叙 desc
正序 asc 預設的 可以不寫
*/
--查詢員工資料 按照工資做倒叙排序
select * from emp order by sal desc
select * from emp order by sal --預設正序
--按照獎金倒叙排序 取第一位最高工資的
select * from emp order by comm desc
--空值倒叙排序 預設在記錄之上 處理null值顯示記錄結尾
--nulls last 指定空值記錄 置于記錄末尾
select * from emp order by comm desc nulls last
select * from emp order by comm
/*
函數
單行函數
了解概念： 針對每條記錄中的數值處理 隻會影響本條記錄
影響多少條 傳回多少條 nvl()
數值函數
字元函數
日期函數
轉換函數
通用函數
多行函數 聚合函數
了解概念： 針對表中多條記錄參與運算 結果是一條記錄
count() 統計記錄數
sum() 求和運算
avg() 求平均值
min() 求最小值
max() 求最大值
*/
/*
數值函數 對數值做處理的函數
四舍五入 round(v1,v2) v1是原始的數值,v2是保留的小數位數
截取 trunc(v1,v2) v1是原始的數值,v2是保留的小數位數 不做四舍五入
取餘數 mod(v1,v2) 10%3 答案 --1
*/
--47.628四舍五入操作
select round(47.628) from dual; --48 預設小數位0
select round(47.628,0) from dual; --48
select round(47.628,1) from dual; --47.6
select round(47.628,2) from dual; --47.63
select round(47.628,-1) from dual; --50
select round(47.628,-2) from dual; --0
--47.628截取操作
select trunc(47.628) from dual; --47
select trunc(47.628,0) from dual; --47
select trunc(47.628,1) from dual; --47.6
select trunc(47.628,2) from dual; --47.62
select trunc(47.628,-1) from dual; -- 40
select trunc(97.628,-2) from dual; --0
--mod函數求餘數
select mod(10,3) from dual; --1
/*
字元函數 對字元串處理
擷取字元串長度 length()
截取字元串 substr(v1,v2,v3) v1是原始字元串 v2是截取起始位 v3 截取的長度
替換字元串 replace(v1,v2,v3) v1是原始字元串 v2被替換的字元串 v3 替換後的字元
替換比對的所有字元
大小寫轉換 upper() lower()
用于驗證碼 Xy8Yo xy98yo XY8YO
*/
---abcde
select length('abcde') from dual;
---截取字元串 從0和1開始都是第一位開始截取
select substr('abcde',0,2) from dual; --ab
select substr('abcde',1,2) from dual; -- ab
select substr('abcde',-1,2) from dual; -- e
select substr('abcde',-2,2) from dual; -- de
--替換字元串
select replace('hello','l','o') from dual; --heooo
--查詢員工表中姓名為SMITH
select * from emp where ename=upper('smith');
select * from emp where ename=upper('SMITH');
select * from emp where ename=upper('SmiTH');
/*
通用函數
nvl(v1,v2) 處理空值使用 v1為空傳回v2
不為空是v1本身
nvl2(v1,v2,v3) v1是被判斷的數值 不為空v2是傳回值 為空v3也是傳回值
concat(v1,v2) 隻能傳兩個參數 做拼接使用
*/
select nvl2(null,2,3) from dual;
/*
轉換函數
數值和字元的轉換
日期和字元的轉換
轉換數值 to_number('123')
轉換字元 to_char(v1,v2) v1是被轉換的資料v2是轉換後的格式
轉換日期 to_date(v1,v2) v1是被轉換的字元(日期格式的字元串) v2是轉換的日期格式
*/
select to_number('123') +1 from dual;--124
select '123'+1 from dual; --to_number 有點雞肋
--查詢員工的工資800 9000 将工資轉換成貨币格式
select to_char(sal,'$9,999,999.00') from emp;
---日期和字元的互換
--查詢目前日期 轉換字元
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;--oracle資料分鐘使用mi
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'day') from dual;--擷取星期
---字元串轉日期
select to_date('2017-09-13','yyyy-mm-dd') from dual;
select to_date('2017-09-13 11:56:56','yyyy-mm-dd hh:mi:ss') from dual;
select to_date('2017-09-13','yyyy-mm-dd hh:mi:ss') from dual;--如果是JSP頁面取日期 00:00:00
select to_date('2017-09-13 23:56:56','yyyy-mm-dd hh24:mi:ss') from dual;--24表明24小時制
/*
日期函數
擷取系統時間 sysdate
給系統增加時間 add_months(v1,v2) v1是時間，v2是增加的月數
擷取系統時間間隔的月數 months_between(v1,v2) 兩個參數都是日期
*/
--查詢3個月之後的日期
select add_months(sysdate,3) from dual;
--擷取間隔的月數 擷取員工到目前為止入職多少個月
select months_between(sysdate,hiredate) from emp;
--擷取間隔的天數 日期和日期做運算結果為天數
select round(sysdate-hiredate) from emp;
/*
多行函數 聚合函數
特點是 空值記錄不參與運算 忽略空值的記錄
count() 統計記錄數
sum() 求和運算
avg() 求平均值
min() 求最小值
max() 求最大值
*/
--統計員工表記錄數如果表中存在索引 三種形式效率一緻
-- 如果沒有索引 使用後兩種
select count(*) from emp; ---公認沒有索引效率最低
select count(empno) from emp;
select count(100) from emp;
--使用獎金列作統計
select count(comm) from emp; --4
select sum(comm) from emp; --2200
select avg(comm) from emp; --550
/*
聚合函數搭配分組使用
group by 分組的列
分組後過濾資料使用 having
分組之前使用where
使用規則
如果使用group by做分組 那麼 select 語句中隻能查詢分組使用的列
和 聚合函數
*/
--查詢每個部門的平均工資
select deptno,avg(sal) from emp group by deptno
--多個列作分組
select ename,deptno,avg(sal) from emp group by deptno,ename
--将job和部門做分組
select job,deptno,count(*) from emp group by deptno,job
--查詢部門平均工資>2000的部門編号
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000
--起别名做條件 oracle不可以 mySql可以
select deptno,avg(sal) s from emp group by deptno having s >2000
--mySql 和oracle都不可以
select sal s from emp where s>1500 --條件不識别别名
--查詢部門中員工的工資大于1500的部門平均工資
select deptno,avg(sal) from emp where sal>1500 group by deptno;
select deptno,avg(sal) from emp group by deptno;
/*
條件表達式 if.. 處理語句 else
是所有sql資料庫都支援的
case 判斷列
when 列值 then
顯示值
when 列值 then
顯示值
else
預設值
end
*/
--查詢員工的工作 使用漢字顯示 MANAGER 經理 PRESIDENT 總裁
select case job
when 'MANAGER' then
'經理'
when 'PRESIDENT' then
'總裁'
else
'普通員工'
end
from emp;
--預設值不寫 比對不上顯示空
select case job
when 'MANAGER' then
'經理'
when 'PRESIDENT' then
'總裁'
end
from emp;
---資料庫oracle 有個特有條件表達式 decode(v1,v2,v3) v1是被判斷的列，v2是等于值，v3是顯示值
select decode(job,'MANAGER','經理','PRESIDENT','總裁','普通員工') from emp;
練習2 :
1. 查詢工資大于12000的員工姓名和工資
2. 查詢員工号為176的員工的姓名和部門号
3. 選擇工資不在5000到12000的員工的姓名和工資
4. 選擇雇用時間在1998-02-01到1998-05-01之間的員工姓名，job_id和雇用時間
5. 選擇在20或50号部門工作的員工姓名和部門号
6. 選擇在1994年雇用的員工的姓名和雇用時間
7. 選擇公司中沒有管理者的員工姓名及job_id
8. 選擇公司中有獎金的員工姓名，工資和獎金級别
9. 選擇員工姓名的第三個字母是a的員工姓名
10. 選擇姓名中有字母a和e的員工姓名
11. 顯示系統時間
12. 查詢員工号，姓名，工資，以及工資提高百分之20%後的結果（new salary）
13. 将員工的姓名按首字母排序，并寫出姓名的長度（length）
14. 查詢各員工的姓名，并顯示出各員工在公司工作的月份數
15. 查詢員工的姓名，以及在公司工作的月份數（worked_month），并按月份數降序排列
select * from employees;
select first_name,salary from employees where salary > 12000;
select first_name,manager_id from employees where employee_id = 176;
select first_name,salary from employees where salary < 5000 or salary > 12000;
select first_name,job_id,hire_date from employees where hire_date > 1998-02-01 and hire_date < 1998-05-01;
select FIRST_NAME "名字",employee_id "員工id",hire_date "入職時間" from EMPLOYEES where hire_date between '1-2月-1998' and '1-5月-1998';
select first_name,department_id from employees where department_id = 20 or department_id = 50;
select first_name,hire_date from employees where hire_date between '1-1月-1994' and '31-12月-1994';
select first_name,salary,commission_pct from employees where commission_pct is not null and commission_pct != 0;
select first_name from employees where first_name like '__a%';
select first_name from employees where first_name like '%a%e%' or first_name like '%e%a%';
select employees.*,1+1 from employees;
select sysdate from dual;
select employee_id,first_name,salary,salary*1.2 from employees;
select first_name,length(first_name) from employees order by substr(first_name,0,1) desc;
select first_name,months_between(sysdate,hire_date) from employees;
select first_name,round(months_between(sysdate,hire_date)) from employees;
select first_name,round(months_between(sysdate,hire_date)) from employees order by round(months_between(sysdate,hire_date)) desc;
select first_name,job_id from employees where manager_id = 0 or manager_id is null; 複制