oracle基礎面試題「oracle數字轉字元串函數」

知識點：

1.Oracle中常用的字元處理函數（2）

to_char（）：把其他類型的數據轉換為字元類型

『100』字元

100 數字

to_char（數字：）把數字轉換為字元起顯示作用

select to_char(10000000)||’哥未來的工資’

from dual

2. between….and…

案例：求出emp表中員工的工資在2000-5000之間的所有員工的編號，姓名，職位，工資

–select empno,ename,job,sal

from emp

where sal between 2000 and 5000

列名 between 初值 and 終值

該列的列值從初值到終值之間的所有的列值

案例：查詢emp表中員工的編號在7499到7902之間所有員工的編號，姓名，職位，入職時間，最後根據編號進行降序排列

— select empno,ename,job,hiredate

from emp

where empno between 7499 and 7902

order by empno desc

列名 not between 初值 and 終值

案例：查詢emp表中工資不在1000-2000之間的所有員工的編號，姓名，職位，工資

— select empno,ename,job,sal

from emp

where sal not between 1000 and 2000

案例：查詢emp表中員工的編號不在7566-7902之間所有員工的編號，姓名，職位，最後根據編號進行降序排列

— select empno,ename,job

from emp

where empno not between 7566 and 7902

order by empno desc

案例：查詢emp表中員工的編號是7369,7499,7566,7698,7844,7900的員工的姓名，職位，工資（or）

— select empno,ename,job,sal

from emp

where empno=7369 or empno=7499 or empno=7566

or empno=7698 or empno=7844 or empno=7900

3.in：查詢同一個列的多個列值，等價於多個or

列名 in（列值1，列值2，列值3）……

案例：查詢emp表中員工的編號是7369,7499,7566,7698,7844,7900的員工的姓名，職位，工資（or）

— select empno,ename,job,sal

from emp

where empno in(7369,7499,7566,7698,7844,7900)

案例：查詢emp表中員工的姓名是SLLEN,JONES,BLAKE,CLARK,KING的所有信息

— select * from emp

where ename in(‘ALLEN’,’JONES’,’BLAKE’,’CLARK’,’KING’)

列名 not in（列值1，列值2，列值3…）

案例：查詢emp表中工資不是800,2975,2850,2450,5000的所有員工的編號，姓名，工資

— select empno,ename,sal

from emp

where sal not in(800,2975,2850,2450,5000)

案例：查詢emp表中員工的職位不是clerk，salesman，manager，analyst的員工信息

— select * from emp

where job not in(‘CLERK’,’SALESMAN’,’MANAGER’,’ANALYST’)

4.數值處理函數：主要處理數值

1）abs（列名/數值）：求出絕對值

— select abs(100) from dual—100

— select abs(0) from dual ——-0

— select abs(-100) from dual—–100

2)sqrt(數值）：求出平方根

10*10=100 10就是100的平方根

–select sqrt（100） from dual– 10

3）power（底數，指數）：求出乘方

–select power(10,3) from dual–1000

4)mod(參數1，參數2)：求出餘數

–select mod(10,3) from dual– 1

5)sign(數值)：判斷數值的正負性，如果數值是正數。返回值是1；如果數值是負數，返回值是-1；如果數值是0，返回值是0

–select sign(100) from dual————— 1

–select sign(-100) from dual———— -1

–select sign(0) from dual—————— 0

6)round():四捨五入

round(參數1，參數2)：

參數1：要進行四捨五入的數據

參數2：如果是正數，表示保留幾位小數；如果是0，表示只有整數；如果是負數，表示對小數點前第幾位進行四捨五入。

–select round(45.926,2) from dual——- 45.93

–select round(45.926,1) from dual——- 45.9

–select round(45.926,0) from dual——- 46

— select round(45.926,-1) from dual—– 50

–:注釋，只起到解釋說明的作用，不參與程序的執行

–select round(45.926,-2) from dual—— 0

–select round(55.926,-2) from dual—— 100

7)trunc（）：截取函數

trunc（參數1，參數2）

參數1：要截取數值。

參數2：如果是正數，表示保留幾位小數；如果是0，表示捨棄所有的小數：如果是負數，表示捨棄小數點前第幾位

–select trunc(45.926,2) from dual — 45.92

–select trunc(45.926,1) from dual– 45.9

— select trunc(45.926,0) from dual– 45

–select trunc(45.926,-1)from dual– 40

–select trunc(45.926,-2)from dual– 0

5.約束：設計表的時候提前對表中的數據設置一些規則，只有滿足這些規則，才可以把數據插入到表中，這些規則就是約束。

約束的類型：

主鍵約束

唯一約束

檢查約束

外鍵約束

默認值約束

非空約束

6.主鍵約束：主鍵約束的作用用來保持數據的唯一性，避免出現冗餘的數據（防止表中出現完全相同的數據）。

1）一張表中只能有一個主鍵（主鍵約束），被主鍵約束修飾的列，該列的列值必須要非空而且唯一。

2）可以使用主鍵約束修飾一個列或者多個列的組值。

create table worker1(

id number(4) primary key,

name varchar2(50),

age number(3)

)

–insert into worker1 values(1,’王勃’,16)

反例：

–insert into worker1(name,age)

volues(‘李密’,21)

3)在創建表的時候指定主鍵約束的名字

create table worker2(

id number(4) constraint pk_id_w2

primary key,

name varchar2(50)

)

insert into worker2 values(1,’張三’)

–反例

insert into worker2 values(1,’李四’)

4）如果使用主鍵約束修飾多個列的組合值，稱為聯合主鍵（複合主鍵）

5)創建表的時候，設置聯合主鍵

create table worker3(

in number(3),

name varchar2(50)

age number(3),

constraint pk_id_name_w3

primary key(id,name)

)

6)修改表的時候設置主鍵約束

格式：

alter table 表名

add constraint 約束名

primary key(列名1，列名2，列名3…)

create table worker4(

id number(4),

name varchar2(50)

)

alter table worker4

add constraint pk_id_w4

primary key(id)

create table worker5(

id number(4),

name varchar2(50),

job varchar2(50),

age number(3)

)

alter table worker5

add constraint pk_id_name_w5

primary key(id,name)

a案例：創建一張表customer2，IDnumber（4），name

varchar2(50),password varchar2(50),age number(3),address varchar2(50),修改customer2的時候設置主鍵約束pk_id_name_cus2修飾id和name的組合值

— create table customer2(

id number(4),

name varchar2(50),

password varchar2(50),

age number(3),

address varchar2(50)

)

alter table customer2

add constraint pk_id_name_cus2

primary key(id,name)

7）刪除主鍵：

a）格式1：只能刪除主鍵

alter table 表名 drop primary key

alter table worker1 drop primary ket

案例：刪除worker2，worker3中的主鍵

— alter table worker2 drop primary key

alter table worker3 drop primary key

b）格式2：

alter table 表名 drop constraint 約束名

alter table worker4 drop constraint pk_id_w4

案例：刪除worker5中的主鍵約束

7日期處理函數：date

1）常用的日期格式：

YYYY-MM-dd:年月日

y:年份 m：月份 d：幾號

eg：1999-12-11

yyyy-mm-dd hh24：mi：ss 年月日 時分秒

h：小時 mi：分鐘 ss：秒鐘

eg：2011-11-11 11:11:11

默認的日期格式：

dd-mon月-yy

eg：2012-12-22 22-12月-12

2）sysdate;當前的系統時間

select sysdate from dual

sysdate以天為單位

案例：查詢今天，昨天，明天

select sysdate,sysdate-1,sysdate+1 from dual

3）to_char（日期數據，’日期格式’）：把日期類型數據（date）按照指定的格式轉換為char類型字元串顯示。

select sysdate,

to_char(sysdate,’yyyy-mm-dd’)

from dual

案例：查詢當前的系統時間，按照yyyy-mm-dd hh24：mi：ss的格式顯示

— select sysdate,

to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)

from dual

案例：查詢emp表中員工的姓名，職位，工資，入職時間，入職時間按照yyyy-mm-dd hh:mi:ss格式顯示

select ename,job,sal,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp

4）使用to_char（）函數獲得日期的某一個部分

select sysdate,to_char(sysdate,’yyyy’) from dual

案例：查詢emp表中員工的編號，姓名，入職時間以及入職的月份

— select empno,ename,hiredate,

to_char(hiredate,’mm’)from emp

案例：查詢emp表中12月份入職的員工的編號，姓名，職位入職時間

— select empno,ename,job,hiredate from emp

where to_char(hiredate,’mm’)=’12’

5)to_date(‘日期字元串’,’日期的格式’)：把滿足日期格式的char類型數據轉換為對應的date類型的數據，經常用於插入操作。

create table worker6(

id number(4) primary key,

name varchar2(50),

hiredate date

)

insert into worker6

values(1,’李世民’,to_date(‘2000-10-20 13:11:15′,’yyyy-mm-dd hh24:mi:ss’))

往worker6插入3條數據

6使用默認的日期格式插入數據

insert into worker6

values(5,’曹操’,’12-12月-09′)

不能插入時分秒

往worker6表中插入3條數據

values(6,’夏侯惇’,’15-10月-16′)

insert into worker6

values(7,’典韋’,’31-12月-17′)

insert into worker6

values(8,’夏侯淵’,’30-11月-15′)

修改：

update worker6 set hiredate=to_date(‘2013′,’yyyy’)

7）months_between(d1,d2):求出日期d1和d2之間間隔了多少個月

案例：查詢emp表中員工的姓名，職位，工資，入職時間，以及工作了多少個月

select ename,job,sal,hiredate,sysdate,

months_between(sysdate,hiredate)

from emp

8)add_months(d1,數字)：在日期d1之後數字個月

select add_months(sysdate,6) from dual

知識點：

1.唯一約束：唯一約束用於指定某一個或者多個列的組合值具有唯一性，防止在該列中輸入重複的列值。

1）使用唯一約束修飾的列，該列的列值必須唯一，但是可以輸入空值。

2）一張表中可以出現多個唯一約束。

3）不要使用唯一約束修飾主鍵所在的列。

create table worker7(

id number(4) primary key,

name varchar2(50) unique

)

insert into worker7 values(1,’張三’)

insert into worker7 values(2,null)

–反例

insert into worker7 values(3,’張三’)(違反唯一約束條件)

4）使用一個唯一約束修飾多個列的組合值（唯一值）

5）修改表的時候，設置唯一約束

alter table 表名

add constreint 約束名

unique(列名1，列名2，列名3…)

create table worker8(

in number(4) primary key,

name varchar2(50)

)

alter table worker8

add constraint uq_name_w8

unique(name)

insert into worker8 values(1,’李白’)

反例：

insert into worker8 values(2,’李白’)

create table worker9(

id number(4) primary key,

name varchar2(50),

password varchar2(50),

email varchar2(50)

)

alter table worker9

add constraint uq_name_password_w9

unique(name,password)

案例：創建一張表BOOK3，id number(4)

name varchar2(50) 書名, author carchar2(50) 作者,pub varchar2(50) 出版社， numinput namber（10）進貨量， 修改book3，設置主鍵約束pk_id_name_b3修飾ID和name的列，設置唯一約束uq_author_pub_b3修飾author和pub的列

— create table book3(

id nu mber(4),

name varchar2(50),

author varchar2(50),

pub varchar2(50),

numinput number(10)

)

alter table book3

add constraint pk_id_name_b3

primary key(id,name)

alter table book3

add constraint pk_author_pub_b3

unique(author,pub)

6.刪除唯一約束：

格式：

alter table 表名

drop constraint 約束名

alter table worker8

drop constraint uq_name_w8

案例：刪除worker9中的唯一約束

alter table worker9

arop constraint uq_name_password_w9

7)主鍵約束和唯一約束之間的區別

a）一張表中只能定義一個主鍵約束，但可以定義多個唯一約束。

b）對於指定為主鍵修飾一個列或者多個列的組合值，其中任何一個列都不能出現空值，而對於唯一約束的列，該列的列值可以為空。

2索引：索引是建立在表中列上的資料庫對象，用於提高數據的查詢速度。

1）索引是提高查詢效率的機制。

2）索引一旦創建以後就由Oracle系統自動進行維護，編寫sql語句的時候不需要知道使用的是哪一個索引

補充：

having 子句：對分組以後的數據再次進行過濾，經常跟聚合函數結合使用

格式

select 列名/聚合函數

from 表名

where 條件

group by 列名

having 子句

order by 列名/聚合函數/別名 asc/desc

where條件對整張表中所有的數據進行過濾

having子句對分組以後的數據進行過濾

–案例

查詢emp表中每一個部門最低工資高於900的部門編號，人數，工資總和以及最低工資，最後根據部門編號進行升序排列

select deptno,count(*),sum(sal),min(sal)

from emp

group by deptno

having min(sal)>900

order by deptno

語法規則：首先執行where條件，對表中所有的數據過濾，然後使用group by進行分組，之後通過having子句對分組以後的數據再次進行過濾，最後執行order by進行排序

注意：having子句一定要跟group by集合使用，而且having子句經常跟聚合函數結合使用

練習：查詢emp表中名字中沒有字母A，或者所在部門編號是30號部門，查詢每個部門最高工資低於5000的部門編號，人數，平均工資，最高工資，最後根據人數進行升序排列，如果人數一致，根據最高工資進行降序排列

用到group by 分組，select只能有一個列名，後面均為聚合函數，此列名作為分組列名

select deptno,count(*),max(sal),avg(sal)

from emp

where deptno=30 or ename not like ‘%A%’

group by deptno

having max(sal)<5000

order by count(*) asc, max(sal) desc

練習2：查詢emp表中含有上級領導，並且崗位不是SALESMAN，每個職位人數小於3個人的職位名稱，人數，平均工資，工資總和，根據人數進行升序排列，如果人數一致根據平均工資再進行升序排列

select job,count(*),avg(sal),sum(sal)

from emp

where mgr is not null and job<>’SALESMAN’

group by job

having count(*)<3

order by count(*),avg(sal)

關聯查詢（表的連接查詢）

所需要查詢的數據來源多張表，使用關聯查詢，把多張表連接起來進行查詢

案例：查詢emp表中員工的編號，姓名以及所在部門的編號，部門名稱

格式：

select 別名1.*/列名，列名2.*/列名

from 表名 別名1,，表2別名2

where 關聯條件

select e.empno,e.ename,e.deptno,d.dname

from emp e,dept d

笛卡爾積現象：

是數學中的一個概念，表示兩個表中的每一行數據任意組合，在表的關聯查詢中，如果沒有關聯條件，則表中的數據會出現乘積現象，稱為笛卡爾積

關聯條件：用來描述兩張表之間的關聯關係，通過添加關聯條件，有效的避免出現笛卡爾積現象

emp表和dept表之間的關聯關係：emp表中的deptno等於dept表中的deptno

關聯條件：emp.deptno=dept.deptno

練習：查詢emp表中的員工編號，姓名，職位，以及所在部門的編號，地址，最後根據部門編號進行升序排列，如果部門編號一致根據員工的編號進行降序排列

select e.empno,e.ename,e.job,e.sal,d.deptno,d.loc

from emp e, dept d

where e.deptno=d.deptno

order by deptno,empno desc

非等值連接：關聯條件不是使用「=」進行連接

案例：查詢emp表中員工的姓名，職位，工資以及該工資的等級

select e.ename,e.job,e.sal,s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal

分析：emp表中員工的工資sal在salgrade（工資等級表）中的最低工資losal和最高工資hisal之間

emp表和salgrade表之間的關聯關係：

e.sal between s.losal and s.hisal

練習:查詢工資的等級在1,3,4,5等級下員工的編號，姓名，職位，工資以及當前的工資等級，最後根據工資的等級進行降序排序，如果等級一致，根據員工的編號進行升序排列

select e.empno,e.ename,e.job,e.sal,s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal and s.grade in(1,3,4,5)

order by s.grade desc,e.empno

練習2：查詢emp表中員工的編號，姓名，職位，工資，該工資的等級以及該員工所在部門的編號，名稱

select e.ename,e.job,e.sal,s.grade,d.deptno,d.dname

from emp e, salgrade s,dept d

where e.sal between s.losal and s.hisal and e.deptno=d.deptno

練習3：查詢emp表中職位是SALESMAN，MANAGER,ANALYST,PRESIDENT下員工的編號，姓名，職位，工資，獎金，入職時間，工資的等級，以及該等級最低工資所在部門的名稱，最後根據工資的等級進行升序排列，如果工資等級一致，根據員工的編號降序排列

select e.empno,e.ename,e.job,e.sal,e.comm,e.hiredate,s.grade,s.losal,d.dname

from emp e, salgrade s,dept d

where e.sal between s.losal and s.hisal and e.deptno=d.deptno and

e.job in(‘SALESMAN’,’MANAGER’,’ANALYST’,’PRESIDENT’)

order by s.grade,e.empno desc