單行函數(shù)

概述

oracle數(shù)據(jù)庫中，內(nèi)置了很多常用的函數(shù)，整體分為：

1. 單行函數(shù)
   • 字符函數(shù)
   • 日期函數(shù)
   • 數(shù)字函數(shù)
2. 轉(zhuǎn)換函數(shù)
3. 聚合函數(shù)

單行函數(shù)，也可以稱為單值函數(shù)，每操作一行數(shù)據(jù)（某個(gè)字段值），都會返回一個(gè)結(jié)果，意思是對每一行數(shù)據(jù)都做相同的操作

聚合函數(shù)，也可以稱為多行函數(shù)、分組函數(shù)、組函數(shù)，它可以操作多行數(shù)據(jù)，并返回一個(gè)結(jié)果，這個(gè)結(jié)果是一個(gè)具有相同屬性的小組，一般會結(jié)合著group分組來使用，當(dāng)然也可以單獨(dú)使用，那么默認(rèn)全部數(shù)據(jù)就是一個(gè)小組。

轉(zhuǎn)換函數(shù)，可以將一個(gè)類型的數(shù)據(jù)轉(zhuǎn)換為另一種類型的數(shù)據(jù)

啞表

Oracle中，有一張?zhí)厥獾谋恚篸ualdual；它是一個(gè)單行單列的虛擬表，是Oracle內(nèi)部自動(dòng)創(chuàng)建的，這個(gè)表只有1列：DUMMY，數(shù)據(jù)類型為VERCHAR2(1)，dual表中只有一個(gè)數(shù)據(jù)'X'

在實(shí)際使用中，啞表主要用來選擇系統(tǒng)變量或求一個(gè)表達(dá)式的值，因?yàn)橐褂胐ual來構(gòu)造完成的查詢語法

例如，查詢表達(dá)式1+1的結(jié)果

select 1+1 from dual;

注意，只有oracle數(shù)據(jù)庫中有這個(gè)啞表 dual

字符函數(shù)

CONCAT(X,Y)，連接字符串X和Y

select concat('Hello','World') as result from dual;
//運(yùn)行結(jié)果：
RESULT
--------------------
HelloWorld

INSTR(X,STR[,START][,N)，從X中查找str，可以指定從start開始，也可以指定從n開始

select instr('Hello World','o') as result from dual;
//運(yùn)行結(jié)果：    
RESULT
----------
5

select instr('Hello World','o',-1) as result from dual;
//從后往前查找，從最后一個(gè)開始
//運(yùn)行結(jié)果：    
RESULT
----------
8

注意instr可以替換like模糊查詢，instr(X,START)>0;說明能夠查詢得到，如果顯示2就是在規(guī)定的字符串中，存在兩個(gè)成功匹配的信息

INITCAP(X)，X首字母轉(zhuǎn)換為大寫，其他字母小寫，通過空格進(jìn)行轉(zhuǎn)換

select initcap('bRIUP study') as result from dual;
//運(yùn)行結(jié)果：
RESULT
----------
Briup Study

REPLACE(X,old,new)，在X中查找old，并替換成new，全局替換

select replace('hello tt world','tt','www') as result from dual;
//運(yùn)行結(jié)果：
RESULT
----------------------------------
hello www world

這些函數(shù)是可以嵌套使用的

// 把first_name和last_name倆個(gè)列的值連接到一起，并且首字大寫，其他子小寫
select initcap(concat(first_name,last_name)) as namefrom s_emp;

數(shù)字函數(shù)

日期函數(shù)

sysdate，是Oracle中用來表示當(dāng)前時(shí)間的關(guān)鍵字，并且可以使用它來參與時(shí)間運(yùn)算。

sysdate參與時(shí)間的加減操作的時(shí)候，單位是天

-- 顯示當(dāng)前時(shí)間
select sysdate from dual;

--顯示時(shí)間:明天的這個(gè)時(shí)候
select sysdate + 1 from dual;

--顯示時(shí)間:昨天的這個(gè)時(shí)候
select sysdate - 1 from dual;

--顯示時(shí)間:1小時(shí)之后的這個(gè)日期
select sysdate + 1/24 from dual;

oracle中不同的會話環(huán)境中，日期數(shù)據(jù)默認(rèn)的格式也不同

alter session set nls_language='simplified chinese';

select sysdate from dual;

SYSDATE
--------------
02-9月 -20

 alter session set nls_language=english;
 
 select sysdate from dual;
 
 SYSDATE
 ------------
 02-SEP-20

常見的日期函數(shù)

months_between

-- 30天之后和現(xiàn)在相差多少個(gè)月
select months_between(sysdate+30,sysdate) from dual;


--運(yùn)行結(jié)果：    
RESULT
----------
1

add_months

-- 指定日期，往后推2個(gè)月
select add_months('01-10月-2020',2) as result from dual;


-- 運(yùn)行結(jié)果：
RESULT
--------------
01-12月-20

next_day

離當(dāng)前時(shí)間最近的下一個(gè)星期5是哪一個(gè)天

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

round

-- 把當(dāng)前日期四舍五入到月
select round(sysdate,'MONTH') from dual;

-- 把當(dāng)前日期四舍五入到年
select round(sysdate,'YEAR') from dual;

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

概述

轉(zhuǎn)換函數(shù)主要有三種：

• TO_CHAR，把一個(gè)數(shù)字或日期數(shù)據(jù)轉(zhuǎn)換為字符
• TO_NUMBER，把字符轉(zhuǎn)換為數(shù)字
• TO_DATE，把字符轉(zhuǎn)換為日期

to_char

把一個(gè)數(shù)字或日期數(shù)據(jù)轉(zhuǎn)換為字符

日期轉(zhuǎn)為字符的常用格式：

select to_char(sysdate,'yyyy mm MONTH mon MON D DD DDD DAY DY') from dual;
select to_char(sysdate,'dd-mm-yy') from dual;
select to_char(sysdate,'yy-mm-dd') from dual;
select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yy-mm-dd hh:mi:ss AM')from dual;

to_number

把字符轉(zhuǎn)換為數(shù)字

select to_number('1000') from dual;

這個(gè)寫法是錯(cuò)的，abc不能轉(zhuǎn)換為數(shù)字

select to_number('abc') from dual;

to_date

把字符轉(zhuǎn)換為日期

select to_date('10-12-2022','dd-mm-yyyy') as result from dual;
select to_date('25-5月-22','dd-month-yy') as result from dual;
select to_date('22/5月/25','yy/month/dd') as result from dual;
select to_date('25-MAY-22','dd-MONTH-yy') as result from dual;

函數(shù)嵌套

例如，先把'hello'和'world'連接起來，再轉(zhuǎn)換為全部字母大寫，然后再從第4個(gè)字符開始，連著截取4個(gè)字符

select substr(upper(concat('hello','world')),4,4) as result from dual;

注意，函數(shù)f1的返回類型，必須是函數(shù)f2的參數(shù)類型，那么它們之間才可以嵌套

多表查詢

多表查詢，又稱表聯(lián)合查詢，即一條sql語句涉及到的表有多張，表中的數(shù)據(jù)通過特定的連接，進(jìn)行聯(lián)合顯示

在數(shù)據(jù)庫中，如果直接查詢倆張表，那么其查詢結(jié)果就會產(chǎn)生笛卡兒積

select count(*) from s_emp;
select count(*) from s_dept;
select count(*) from s_emp,s_dept;

s_emp表中25條數(shù)據(jù)，s_dept表中12條數(shù)據(jù)，查詢倆張表，數(shù)據(jù)倆倆組合，會得到300條數(shù)據(jù)

其實(shí)，s_emp表中的每一條數(shù)據(jù)，和s_dept表中的每一條數(shù)據(jù)進(jìn)行倆倆組合，這里面大多數(shù)的數(shù)據(jù)是沒有意義的，為了這種避免笛卡爾積的產(chǎn)生，在多表查詢的時(shí)候，可以使用連接查詢來解決這個(gè)問題。

連接查詢又可以大致分為：

• 等值連接
• 不等值連接
• 外連接
  • 左外連接
  • 右外連接
  • 全連接
• 自連

等值連接

利用一張表中某列的值，和另一張表中某列的值相等的關(guān)系，把倆張表連接起來，滿足條件的數(shù)據(jù)才會組合

查詢員工的名字、部門編號、部門名字

select last_name,dept_id,s_dept.id,name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id;

不等值連接

工資等級表salgrade：

• gradeName列表示等級名稱
• losal列表示這個(gè)級別的最低工資數(shù)
• hisal列表示這個(gè)級別的最高工資數(shù)

-- 查詢出員工的名字、職位、工資、工資等級名稱
select  e.last_name, e.title, e.salray, s.gradeName 
from    s_emp e, salgrade s
where   e.salray 
between s.losal and s.hisal

外連接

/*
連接查詢
    如需要多張數(shù)據(jù)表的數(shù)據(jù)進(jìn)行查詢,則可通過連接運(yùn)算符實(shí)現(xiàn)多個(gè)查詢
內(nèi)連接 inner join
    查詢兩個(gè)表中的結(jié)果集中的交集
外連接 outer join
    左外連接 left join
        (以左表作為基準(zhǔn),右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
    右外連接 right join
        (以右表作為基準(zhǔn),左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
        
等值連接和非等值連接

自連接
*/


-- 查詢參加了考試的同學(xué)信息(學(xué)號,學(xué)生姓名,科目編號,分?jǐn)?shù))
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,確定查詢的列來源于兩個(gè)類,student  result,連接查詢
(2):確定使用哪種連接查詢?(內(nèi)連接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右連接(也可實(shí)現(xiàn))
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 等值連接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左連接 (查詢了所有同學(xué),不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同學(xué)(左連接應(yīng)用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考題:查詢參加了考試的同學(xué)信息(學(xué)號,學(xué)生姓名,科目名,分?jǐn)?shù)，三表查詢)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

口訣：我們想把表中的多出的一個(gè)數(shù)據(jù)查詢并顯示出來，那么就在另一張表上面添加一個(gè)加號(+)，意思就是查一下缺考的同學(xué)，那么說result中不存在當(dāng)前學(xué)生考試成績，為null，那么就要在null表上加上一個(gè)加號(+)

select s.studentno,studentname,subjectno,StudentResult
from student s,result r
where s.studentno = r.studentno(+);

全連接

查詢所有員工以及對應(yīng)的部門的名字，沒有任何員工的部門也要顯示出來，沒有部門的員工也要顯示出來

select last_name,dept_id,name
from s_emp full outer
join s_dept
on s_emp.dept_id=s_dept.id

LAST_NAME          DEPT_ID NAME
--------------- ---------- ---------------
Havel                   45 Operations
Ropeburn                50 Administration
Velasquez               50 Administration
tom                          
                           st

可以看出，左右倆邊的表中，新增的數(shù)據(jù)tom和st，原來等值連接不上，現(xiàn)在也全都被查詢出來了。

自連接

自連接就是一張表，自己和自己連接后進(jìn)行查詢

例如，查詢每個(gè)員工的名字以及員工對應(yīng)的管理者的名字

select s1.last_name,s2.last_name manager_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id;

其實(shí)，可以給這一張表，起倆個(gè)不同的別名，然后當(dāng)成倆張不同的表，進(jìn)行查詢就行了

操作結(jié)果集

每一條sql語句，查詢出的一個(gè)結(jié)果，都可以被稱為結(jié)果集。

如果有倆條sql語句，它們分別查詢出的結(jié)果集，都包含完全一致的字段名稱和類型，那么我們可以使用下面的關(guān)鍵字對倆個(gè)結(jié)果集進(jìn)行操作：

• union，取倆個(gè)結(jié)果集的并集
• union all，把倆個(gè)結(jié)果集合在一起顯示出來
• minus，第一個(gè)結(jié)果集除去第二個(gè)結(jié)果集和它相同的部分
• intersect，求倆個(gè)結(jié)果集的交集

前提條件是，倆個(gè)結(jié)果集中查詢的列要完全一致（名稱和類型）

取倆個(gè)結(jié)果集的并集

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
union
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

第一個(gè)結(jié)果集除去第二個(gè)結(jié)果集和它相同的部分

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
minus
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

rownum

Oracle中，有一個(gè)特殊的關(guān)鍵字rownum，被稱為：偽列。

rownum只有Oracle數(shù)據(jù)中才有。

rownum（偽列），就像表中的列一樣，但是在表中并不存在。

image-20200916113038625.png

偽列，可以根據(jù)查詢結(jié)果的條數(shù)，自動(dòng)生成，并且一定是從1開始連續(xù)不斷的數(shù)字

偽列rownum的本質(zhì)就是給查詢的一行行結(jié)果標(biāo)上行號

如果偽列用在where條件中，那么它只能有以下操作：

• rownum如果是==相同==的條件，那么偽列==只能等1==

  select last_name
  from s_emp
  where rownum=1
  

• rownum如果是==大于==的條件，那么偽列==只能大于0==

  select last_name
  from s_emp
  where rownum>0
  

如果大于其他值，那么就查詢不出任何結(jié)果

• rownum可以小于任何數(shù)

Oracle數(shù)據(jù)庫中偽列rownum最核心的作用就是：完成分頁查詢