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

------码字不易，给个关注呗^_^