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