day02_Oracle

一、課程目標

常用函數：了解會使用 高級查詢：掌握（mysql,復習） 分頁查詢：偽列 關聯查詢：掌握（mysql,復習）

二、常用函數

2.1 Oracle字符函數

函數說明

ASCII	返回對應字符的十進制值
CHR	給出十進制返回字符
CONCAT	拼接兩個字符串，與`
INITCAP	將字符串的第一個字母變為大寫
INSTR	找出某個字符串的位置
INSTRB	找出某個字符串的位置和字節數
LENGTH	以字符給出字符串的長度
LENGTHB	以字節給出字符串的長度
LOWER	將字符串轉換成小寫
LPAD	使用指定的字符在字符的左邊填充
LTRIM	在左邊裁剪掉指定的字符
RPAD	使用指定的字符在字符的右邊填充
RTRIM	在右邊裁剪掉指定的字符
REPLACE	執行字符串搜索和替換
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串（以字節）
SOUNDEX	返回一個同音字符串
TRANSLATE	執行字符串搜索和替換
TRIM	裁剪掉前面或后面的字符串
UPPER	將字符串變為大寫

-- 字符函數 -- 求字符的長度 -- dual 是一個偽表，沒有實際應用，就是配合著sql語法而來的 select length('ALDKF') from dual; -- 截取字符 -- 參數解釋:HelloWord: 原字符，6：從第幾位開始 , 5: 截取多少位 -- 注意：索引是從1開始，不是0 select substr('HelloWorld',6,5) from dual; -- 字符拼接 -- concat('字符1','字符2') select concat('Hello','World') from dual; -- 注意：concat一次只能拼接兩個字符，如果有多個字符，需嵌套使用concat select concat(concat('Hello','World'),'MM') from dual; -- concat嵌套方式比較麻煩，可以使用|| 進行嵌套 select 'Hello' || 'World' || 'haha' from dual; -- 將字符轉換為小寫 select lower('ALDKF') from dual; -- 字符串替換 select replace('冬天就是好','冬天','切糕') from dual;

2.2 Oracle數學函數

函數說明

ROUND	四舍五入
TRUNC	截取數值
MOD(n1,n2)	返回一個n1除以n2的余數
CEIL	向上取整
FLOOR	向下取整
ABS	指定值的絕對值
POWER(n1,n2)	返回n1的n2次方

-- 數學函數 -- round四舍五入，默認小數點最近的一位，是否四佶五入 select round(123.456) from dual; -- 保留兩位小數，進行四舍五入 select round(123.456,2) from dual; -- trunc截取數值，默認將小數點后全部舍棄掉 select trunc(123.456) from dual; -- 保留兩位小數，進行截取數值 select trunc(123.456,2) from dual; -- mod 取模 select mod(10,3) from dual; -- ceil 向上取整 select ceil(123.456) from dual; -- floor 向下取整 select floor(123.456) from dual; -- abs 取絕對值 select abs(-123.456) from dual; -- power 求幾次冪 select power(2,3) from dual;

2.3 Oracle日期函數

函數說明

systimestamp	獲取當前日期和時間、小數點后面精確6位、時區、上下午
sysdate	獲取當前日期和時間
ADD_MONTHS	在當前日期基礎上加指定的月
LAST_DAY	獲取當前日期所在月的最后一天
TRUNC	日期截取

-- 日期函數 -- 獲取當前系統時間 select sysdate from dual; -- 加月份 select add_months(sysdate,4) from dual; -- 減月份，我們只需要數值為負值 select add_months(sysdate,-4) from dual; -- 獲取當前日期的所在的月份的最后一天 select last_day(sysdate) from dual; -- sysdate-33：到了上一個月，也就是獲取的是上一個月的最后一天 select last_day(sysdate-33) from dual; -- 截取日期 -- 默認按日截取，將時間截掉 select trunc(sysdate) from dual; -- 按月截取(把日載掉) select trunc(sysdate,'mm') from dual; -- 按年截取(把月載掉) select trunc(sysdate,'yyyy') from dual; -- 按小時截取 select trunc(sysdate,'hh') from dual; -- 按分鐘截取 select trunc(sysdate,'mi') from dual;

2.4 Oracle轉換函數

函數說明

CHARTOROWID	將 字符轉換到 rowid 類型
CONVERT	轉換一個字符節到另外一個字符節
HEXTORAW	轉換十六進制到 raw 類型
RAWTOHEX	轉換 raw 到十六進制
ROWIDTOCHAR	轉換 ROWID 到字符
TO_CHAR	轉換日期格式到字符串
TO_DATE	按照指定的格式將字符串轉換到日期型
TO_MULTIBYTE	把單字節字符轉換到多字節
TO_NUMBER	將數字字串轉換到數字
TO_SINGLE_BYTE	轉換多字節到單字節

-- 轉換函數-- 數字轉字符串 select to_char(100) from dual; -- 還可以與其他字符串進行拼接 select to_char(100)||'分' from dual; -- 日期轉字符串 -- 將當前的系統時間，按照yyyy-MM-dd模式轉換成字符串 select to_char(sysdate,'yyyy-MM-dd') from dual; select to_char(sysdate,'yyyy-MM-dd HH:mi:ss') from dual; -- 注意：年月日中間的連接符，不能是中文，比如:yyyy年MM月dd日 -- select to_char(sysdate,'yyyy年MM月dd日') from dual; -- 錯誤的寫法 -- 如果要yyyy年MM月dd日這種模式的日期時間，我們可以單獨把年月日截取出來進行拼接 select to_char(sysdate,'yyyy')||'年' ||to_char(sysdate,'MM')||'月' ||to_char(sysdate,'dd')||'日' from dual; -- 字符串轉日期 -- 將2020-03-11日期字符串按照yyyy-mm-dd模式轉換成Date對象 select to_date('2020-03-11','yyyy-mm-dd') from dual; -- 將字符串轉數值 select to_number('100') from dual; -- 驗證100是否是數值 select to_number('100')+100 from dual; -- 可以直接相加，會自動轉換為數值進行參與運算 select '100' + 100 from dual;

2.5 Oracle其他函數

2.5.1 nvl函數

• 語法

  NVL(檢測的值，需要是字符型，如果為 null 的值);

• 示例

  select NVL(NULL,0) from dual; select NVL('','哈哈') from dual; -- 返回哈哈 select NVL(null,'哈哈') from dual; -- 返回哈哈 select NVL(' ','哈哈') from dual; -- 返回空格

2.5.2 nvl2函數

• 需求

  使用nvl函數，判斷值是否為空，如果為空，將值替換為’補考’

  以學生表（student）英語成績為例

• sql演示

  select NVL(english,'補考') from student

  英語成績是number類型的，我們替換的值是字符類型的，所以報錯

• nvl2語法

  NVL2(檢測的值，如果不為 null 的值，如果為 null 的值);

• 示例

  select NVL2(english,to_char(english),'補考') from student;

2.6 Oracle聚合函數

函數說明

count(*) | count(主鍵)	計算表中的總記錄數
max	計算最大值
min	計算最小值
sum	計算和
avg	計算平均值

注意：聚合函數的計算，排除null值。

解決方案：

選擇不包含非空的列進行計算

nvl2或nvl函數

以student表為例，進行演示

• 查詢學生總數（null值處理）

  SELECT COUNT(id) FROM student; select count(NVL(english,0)) from student; SELECT COUNT(*) FROM student;

• 查詢數學成績總分

  SELECT SUM(math) FROM student;

• 查詢數學成績平均分

  SELECT AVG(math) FROM student;

• 查詢數學成績最高分

  SELECT MAX(math) FROM student;

• 查詢數學成績最低分

  SELECT MIN(math) FROM student;

三、DQL高級查詢

3.1 數據準備

-- 創建表 CREATE TABLE person (id number,name varchar2(20),age number,sex varchar2(5),address varchar2(100),math number,english number ); -- 插入記錄 INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (1,'馬云',55,'男','杭州',66,78); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (2,'馬化騰',45,'女','深圳',98,87); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (3,'馬景濤',55,'男','香港',56,77); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (4,'柳巖',20,'女','湖南',76,65); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (5,'柳青',20,'男','湖南',86,NULL); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (6,'劉德華',57,'男','香港',99,99); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (7,'馬德',22,'女','香港',99,99); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (8,'德瑪西亞',18,'男','南京',56,65); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (9,'唐僧',25,'男','長安',87,78); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (10,'孫悟空',18,'男','花果山',100,66); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (11,'豬八戒',22,'男','高老莊',58,78); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (12,'沙僧',50,'男','流沙河',77,88); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (13,'白骨精',22,'女','白虎嶺',66,66); INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES (14,'蜘蛛精',23,'女','盤絲洞',88,88); commit;

3.2 排序查詢

• 語法

  SELECT 字段名 FROM 表名 [WHERE條件] ORDER BY 字段名 [ASC|DESC];

• 示例

  -- 按照年齡的降序排序 select * from person order by age desc;

3.3 分組查詢

對一列數據進行分組，相同的內容分為一組，通常與聚合函數一起使用，完成統計工作

3.3.1 語法

SELECT 字段 1,字段 2... FROM 表名 [where條件] GROUP BY 分組字段 [HAVING 條件] [order by];

• 注意事項

  • 分組之后查詢的字段：分組字段、聚合函數

  • where 和 having 的區別？

    • where 在分組之前進行限定，如果不滿足條件，則不參與分組。having在分組之后進行限定，如果不滿足結果，則不會被查詢出來 where 對基本的條件篩選
    • where 后不可以跟聚合函數，having可以進行聚合函數的判斷。

  • where: 操作的數據源: 原始表

  • having: 操作的數據源: 結果集

3.3.2 案例演示

• 查詢男女各多少人

  SELECT sex,COUNT(*) FROM person GROUP BY sex;

• 查詢年齡大于25歲的人,按性別分組,統計每組的人數

  SELECT sex,COUNT(*) FROM person WHERE age >25 GROUP BY sex;

• 查詢年齡大于25歲的人,按性別分組,統計每組的人數,并只顯示性別人數大于2的數據

  SELECT sex,COUNT(*) FROM person WHERE age >25 GROUP BY sex HAVING COUNT(*)>2;

3.4 分頁查詢 *

偽列是Oracle提供的一個系統列值，在最左側，可以直接使用。

我們在 ORACLE 進行分頁查詢，需要用到ROWNUM 和嵌套查詢

3.4.1 需求

分頁查詢person表 每頁 3條記錄

3.4.2 簡單分頁查詢

首先顯示第一頁的3條數據

select rownum, p.* from person p where rownum <= 3

顯示第二頁的3條數據

select rownum,p.* from person p where rownum>3 and rownum<=6

發現查詢出來的沒有結果偽列的特性決定；

這是因為 rownum 是在查詢語句掃描每條記錄時產生的，所以不能使用“大于” 符號，只能使用“小于”或“小于等于” ，只用“等于”也不行

那怎么辦呢？我們可以使用子查詢來實現

select * from (select rownum r,p.* from person p) where r > 3 and r<= 6

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-PAKZljuY-1665832782011)(assets/image-
.png)]

3.4.3 排序分頁查詢

按照person表中的年齡降序進行查詢第二頁的數據

select * from (select rownum r,p.* from (select * from person order by age desc) p) where r > 3 and r<= 6 ;

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-lPNVeCDx-1665832782012)(assets/
)]

四、多表查詢

4.1 數據準備

-- 部門表 CREATE TABLE dept (id NUMBER PRIMARY KEY, -- 部門iddname VARCHAR2(50), -- 部門名稱loc VARCHAR2(50) -- 部門位置 );-- 添加4個部門 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'); INSERT INTO dept(id,dname,loc) VALUES (20,'學工部','上海'); INSERT INTO dept(id,dname,loc) VALUES (30,'銷售部','廣州'); INSERT INTO dept(id,dname,loc) VALUES (40,'財務部','深圳'); commit;-- 職務表 CREATE TABLE job (id NUMBER PRIMARY KEY,jname VARCHAR2(20), -- 職務名稱description VARCHAR2(50) -- 職務描述 );-- 添加4個職務 INSERT INTO job (id, jname, description) VALUES(1, '董事長', '管理整個公司，接單'); INSERT INTO job (id, jname, description) VALUES(2, '經理', '管理部門員工'); INSERT INTO job (id, jname, description) VALUES(3, '銷售員', '向客人推銷產品'); INSERT INTO job (id, jname, description) VALUES(4, '文員', '使用辦公軟件'); commit;-- 員工表 CREATE TABLE emp (id NUMBER PRIMARY KEY, -- 員工idename VARCHAR2(50), -- 員工姓名job_id NUMBER, -- 職務id 外鍵mgr NUMBER , -- 上級領導編號 *joindate DATE, -- 入職日期salary NUMBER(7,2), -- 工資 99999.99bonus NUMBER(7,2), -- 獎金 99999.99dept_id NUMBER, -- 所在部門編號 外鍵CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) );-- 添加員工 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孫悟空',4,1004,to_date('2000-12-17','yyyy-MM-dd'),'8000.00',NULL,20); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1002,'盧俊義',3,1006,to_date('2001-02-20','yyyy-MM-dd'),'16000.00','3000.00',30); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1003,'林沖',3,1006,to_date('2001-02-22','yyyy-MM-dd'),'12500.00','5000.00',30); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1004,'唐僧',2,1009,to_date('2001-04-02','yyyy-MM-dd'),'29750.00',NULL,20); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1005,'李逵',4,1006,to_date('2001-09-28','yyyy-MM-dd'),'12500.00','14000.00',30); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1006,'宋江',2,1009,to_date('2001-05-01','yyyy-MM-dd'),'28500.00',NULL,30); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1007,'劉備',2,1009,to_date('2001-09-01','yyyy-MM-dd'),'24500.00',NULL,10); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1008,'豬八戒',4,1004,to_date('2007-04-19','yyyy-MM-dd'),'30000.00',NULL,20); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1009,'羅貫中',1,NULL,to_date('2001-11-17','yyyy-MM-dd'),'50000.00',NULL,10); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1010,'吳用',3,1006,to_date('2001-09-08','yyyy-MM-dd'),'15000.00','0.00',30); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1011,'沙僧',4,1004,to_date('2007-05-23','yyyy-MM-dd'),'11000.00',NULL,20); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1012,'李逵',4,1006,to_date('2001-12-03','yyyy-MM-dd'),'9500.00',NULL,30); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1013,'小白龍',4,1004,to_date('2001-12-03','yyyy-MM-dd'),'30000.00',NULL,20); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1014,'關羽',4,1007,to_date('2002-01-23','yyyy-MM-dd'),'13000.00',NULL,NULL); commit;-- 工資等級表 CREATE TABLE salarygrade(grade NUMBER PRIMARY KEY, -- 等級losalary NUMBER, -- 最低工資hisalary NUMBER -- 最高工資 );-- 添加5個工資等級 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000); INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (2,12010,14000); INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (3,14010,20000); INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (4,20010,30000); INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (5,30010,99990); commit;

4.2 內連接

拿左表的記錄去匹配右表的記錄，若符合條件顯示（二張表的交集）

• 需求

  1.查詢所有員工的姓名，工資,入職日期和所在部門名稱

• sql語句

  select e.ename,e.salary,e.joindate,d.dname from emp e, dept d where e.dept_id = d.id;

4.3 外連接

• 左外連接

  展示左表全部，再去匹配右表記錄，若條件符合顯示，若條件不符合顯示NULL

  語法

  select ... from 左表 left [outer] join 右表 on 連接條件;

• 右外連接

  展示右表全部，再去匹配左表記錄，若條件符合顯示，若條件不符合顯示NULL

  語法

  select ...from 左表 right [outer] join 右表 on 連接條件;

• sql演示

  # 左外連接（推薦） -- 查詢所有員工信息及對應的部門名稱 SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id; -- 查詢所有部門及對應的員工信息 SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;# 右外連接（了解） -- 查詢所有部門及對應的員工信息 SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

4.4 子查詢 （重點復習)

一條select語句執行結果，作為另一條select語法的一部分。其實就是select語句的嵌套！

• 語法

  -- 【1】查詢結果單值 SELECT MAX(salary) FROM emp; -- 【2】查詢結果單列多行 SELECT salary FROM emp; -- 【3】查詢結果多行多列 SELECT * FROM emp;

  規律

  -- 【1】子查詢結果為單列，肯定作為條件在where后面使用select ... from 表名 where 字段 in (子查詢); -- 【2】子查詢結果為多列，一般作為虛擬表在from后面使用select ... from (子查詢) as 表別名;

• sql演示

  • 子查詢結果為單行單列

    -- 【1】查詢工資最高的員工是誰？ SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp); -- 【2】查詢工資小于平均工資的員工有哪些？ -- 2.1 先求出平均工資 SELECT AVG(salary) FROM emp; -- 2.2 查詢低于平均工資的員工 SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

  • 子查詢結果為單列多行

    -- 【1】查詢工資大于5000的員工，來自于哪些部門的名字 -- 1.1 查詢工資大于5000的員工 SELECT dept_id FROM emp WHERE salary >5000; -- 1.2 來自于哪些部門的名字 SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000); -- 【2】查詢開發部與財務部所有的員工信息 -- 2.1 根據部門名稱，查詢部門主鍵 SELECT id FROM dept WHERE `name` IN('開發部','財務部'); -- 2.2 根據部門id查詢員工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('開發部','財務部'));

  • 子查詢結果為多列多行

    -- 【1】 查詢員工入職日期是2011-11-11日之后的員工信息和部門信息 -- 1.1 根據 join_date 查詢 2011-11-11 之后的員工信息 SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';-- 1.2 根據1步驟的結果，跟部門表關聯 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

t WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);
```

```sql -- 【2】查詢開發部與財務部所有的員工信息 -- 2.1 根據部門名稱，查詢部門主鍵 SELECT id FROM dept WHERE `name` IN('開發部','財務部'); -- 2.2 根據部門id查詢員工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('開發部','財務部'));```

• 子查詢結果為多列多行

  -- 【1】 查詢員工入職日期是2011-11-11日之后的員工信息和部門信息 -- 1.1 根據 join_date 查詢 2011-11-11 之后的員工信息 SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';-- 1.2 根據1步驟的結果，跟部門表關聯 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

總結

以上是生活随笔為你收集整理的JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。