oracle

1. 数据库介绍

数据库的分类

1、关系型数据库：

(1)、sqlserver使用比较少和net配合使用的时候较多

(2)、mysql开源数据库

(3)、db2银行，财务等系统使用较多

(4)、oracle复杂但是安全

2、非关系型数据库

(1)、hive数据仓库

(2)、hbase列数据库

(3)、redis缓存数据库，内存数据库，如应用在新浪微博

(4)、MongoDB文档数据库

(5)、eo4j图数据库

2. 访问数据库&连接

1、首先开启的服务：window+r---->services.msc--->找到oracle开头的服务，Listener,ORCL启动

2、进入cmd命令提示符

3、sqlplus /nolog 使用无账户状态登录

4、输入conn scott/[email protected]; 连接数据库用户名/密码@数据库实例名称；

5、如果账户是锁定的，需要提前解锁

(1)、使用管理员账户登录 conn sys/123456(账号/密码)@orcl as sysdba;

(2)、解锁账户：alter user scott account unlock;

6、重新进行第4步骤

7、select * from tab；查询当前所有表，在oracle中有默认的四张表：emp,dept,salgrade,bonus

8、select * from emp；查询emp表中的数据。

er、连接公司数据库

1、公司分配一个账户，用户名和密码，服务器的地址，需要连接的数据库名称

2、打开net manage,选择本地中的服务命名，按照步骤填写

11、oracle

3、选择服务命名之后点击添加网络服务名（自己创建）

11、oracle

4、选择tcp/ip协议

5、选择公司的服务器的ip地址

6、填写数据库的名称

11、oracle 想

7、完成

注意：如果访问的时候出现没有监听服务，重新启动即可，重新配置监听服务

3. oralce介绍

1、数据库的版本发展

Oracle8i：i表示的internet,向网络发展，过渡版本，只有一张vcd。8i是过渡性产品。

Oracle9i：是现在使用最广泛版本，8i的升级版。1CD

Oracle10g：700M过渡性产品,其中g表示的是网格计算。以平面网格，以中心查找。(grid)

Oracle11g：完整性产品,最新版本2G。

Oracle12c(cloud)

2、安装的Oracle注意事项

1)、关闭防火墙 2)、断开网络

3、oracle用户

1)、sys 超级管理员 2)、system 普通管理员 3)、scott 普通的用户

4、racle 11g服务详细介绍及哪些服务是必须开启的？

安装oracle 11g R2中的方法成功安装Oracle 11g后，共有7个服务，这七个服务的含义分别为：

（1） Oracle ORCL VSS Writer Service：Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

（2） OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）

（3） OracleJobSchedulerORCL：Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

（4） OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

（5） OracleOraDb11g_home1ClrAgent：Oracle数据库.NET扩展服务的一部分。（非必须启动）

（6） OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动，下面会有详细详解）。

（7） OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(必须启动)

6、那么在开发的时候到底需要启动哪些服务呢？

要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用。

注：ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OracleService+数据库名

7、服务

（1）所有的服务改成"手动"

（2）启动两个

1)、监听服务：OracleOraDb10g_home1TNSListener

监听客户端的连接

2)、数据库服务：OracleServiceORCL

命名规则：OracleService+实例名

4. DDL

一、什么是DDL？

数据定义语言(Dat a definition language)是对数据表的操作，如对表的增删改create、alter、drop、rename、truncate

二、create 怎么创建？

1、create table创建表

(1)、怎么创建表？

create table <table_name>( column1 datatype [not null] [primary key], [constraint <约束名> 约束类型 (要约束的字段) ] ）

创建表表名（列名数据类型）

说明：　

datatype --是oracle的数据类型,可以查看附录。

nut null --可不可以允许资料有空的（尚未有资料填入）。

primary key --是本表的主键。

constraint --是对表里的字段添加约束.(约束类型有 check,unique,primary key,not null,foreign key)。

示例:

create table stu(

id number(8) primary key,

name varchar2(20) not null,

sex varchar2(8),

clsid number(8),

constraint u_1 unique(name),

constraint c_1 check (sex in ('male','female'))

);

-----------------------------------------------------------------------------------------

(2)、怎么复制表？

create table <table_name> as <select 语句>(需注意的是复制表不能复制表的约束);

创建表表名作为 select* 要复制表的表名

示例:

create table test as select * from emp;

如果只复制表的结构不复制表的数据则:

create table test as select * from emp where 1=2;

2、create index 创建索引

create [unique] index <index_name> on <table_name>(字段 [asc|desc]);

创建 [唯一] 索引索引名在表(字段)

unique --确保所有的索引列中的值都是可以区分的。[asc|desc] --在列上按指定排序创建索引。

(1)、什么时候创建索引？

a.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。

b.不要试图对表创建两个或三个以上的索引。

c.为频繁使用的行创建索引。

示例

create index i_1 on emp(empno asc);

3、创建同义词

同义词即是给表或视图取一个别名。

create synonym <synonym_name> for <tablename/viewname>

创建同义词同义词名为了表/视图

示例:

create synonym mm for emp;

三、alter怎么修改？

(1)、向表中添加新字段

alter table <table_name> add (字段1 类型 [not null],字段2 类型 [not null].... );

修改表表名添加（字段类型）

(2)、修改表中字段

alter table <table_name> modify(字段1 类型,字段2 类型.... );

修改表表名修改（字段类型）

(3)、删除表中字段

alter table <table_name> drop(字段1,字段2.... );

修改表表名删除（字段类型）

(4)、修改表的名称

rename <table_name> to <new table_name>;

修改表表名对（新表名）

(5)、对已经存在的表添加约束

alter table <table_name> add constraint <constraint_name> 约束类型 (针对的字段名);

修改表表名添加约束（约束名）

示例：

alter table emp add constraint s_f foreign key (deptno) references dept(deptno);

a、对表里的约束禁用;

alter table <table_name> disable constraint <constraint_name>;

b、对表里的约束重新启用;

alter table <table_name> enable constraint <constraint_name>;

c、删除表中约束

alter table <table_name> drop constraint <constraint_name>;

示例:

alter table emp drop constraint <primary key>;

四、drop怎么删除？

(1)、删除表

drop table <table_name>;

示例

drop table emp;

--------------------------------------------------------------------------------

(2)、删除索引

drop index <index_name>;

示例

drop index i_1;存储

------------------------------------------------------------------------------

(3)、删除同义词

drop synonym <synonym_name>;

示例

drop synonym mm;

(4)、练习复习

5. DML

什么是DML?

数据操纵语言 Data Manipulation Language 对表中数据的增、删、改、查对数据的操作（insert delete update select）

注：在执行过程中默认提交因为存在setAutoCommit(true),如果不要自动提交则设置为setAutoCommit(false),执行语句,结束恢复现场setAutoCommit(true)，DML自动提交

一、怎么插入数据？

1、插入记录

insert into table_name (column1,column2,...) values ( value1,value2, ...);

示例

insert into emp (empno,ename) values(9500,'aa');

2、把一个表中的数据插入另一个表中

insert into <table_name> <select 语句>

示例

create table a as select * from emp where 1=2;

insert into a select * from emp where sal>2000;

二、怎么查询记录？

1、查询记录

(1)、一般查询

select [distinct] <column1 [as new name] ,columns2,...> from <table1>

[where <条件>] [group by <column_list>] [having <条件>] [order by <column_list> [asc|desc]]

distinct --表示去重复的行

where --按照一定的条件查找记录

group by --分组查找(需要汇总时使用)

having --分组的条件

order by --对查询结果排序

(2)、要显示全部的列可以用*表示

示例：select * from emp;

(3)、where 语句的运算符

where <条件１>and<条件２> --两个条件都满足

示例：select * from emp where deptno=10 and sal>1000;

(4)、where <条件１>or<条件２> --两个条件中有一个满足即可

示例：select * from emp where deptno=10 or sal>2000;

(5)、where not <条件> --不满足条件的

示例：select * from emp where not deptno=10;

(6)、where in(条件列表) --所有满足在条件列表中的记录

示例：select * from emp where empno in(7788,7369,7499);

(7)、where between .. and　..　 --按范围查找

示例：select * from emp where sal between 1000 and 3000;

(8)、where 字段 like --主要用与字符类型的字段

示例1：select * from emp where ename like '_c%'; --查询姓名中第二个字母是'c'的人

'-' 表示任意字符; '%' 表示多字符的序列；

(8)、where 字段 is [not] null --查找该字段是[不是]空的记录

2、汇总数据是用的函数

(1)、sum --求和

示例：select deptno,sum(sal) as sumsal from emp group by deptno;

avg --求平均值 max --求最大值 min --求最小值 count --求个数

3、子查询

select <字段列表> from <table_name> where 字段运算符(<select 语句>);

示例：select * from emp where sal=(select max(sal) from emp);

4、运算符

(1)、any

示例：select * from emp where sal>any(select sal from emp where deptno=30) and deptno<>30;

--找出比deptno=30的员工最低工资高的其他部门的员工

(2)、all

select * from emp where sal>all(select sal from emp where deptno=30) and deptno<>30;

--找出比deptno=30的员工最高工资高的其他部门的员工

5、连接查询

select <字段列表> from <table1,table2> where table1.字段[(+)]=table2.字段[(+)]

示例：select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

6、查询指定行数的数据

select <字段列表> from <table_name> where rownum<行数;

示例：select * from emp where rownum<=10;--查询前10行记录

注意rownum只能为1 因此不能写 select * from emp where rownum between 20 and 30;

要查第几行的数据可以使用以下方法:

select * from emp where rownum<=3 and empno not in (select empno from emp where rownum<=3);

结果可以返回整个数据的3-6行;

不过这种方法的性能不高；如果有别的好方法请告诉我。

三、更新数据

update table_name set column1=new value,column2=new value,...where <条件>

更新表名设置字段=值

示例：update emp set sal=1000,empno=8888 where ename='scott'

四、删除数据

delete from <table_name> where <条件>

删除来自表名 where

示例

delete from emp where empno='7788'

6. DCL&TCL

什么是DCL?

数据控制语言，对数据库用户的操作

1.授权

grant <权限列表> to <user_name>;

2.收回权限

revoke <权限列表> from <user_name>

oracle 的权限列表

connect 连接

resource 资源

unlimited tablespace 无限表空间

dba 管理员

session 会话

什么是TCL?

事务控制语言

1.commit 提交；

2.rollback [to savepoint]　回滚;

3.savepoint <savepoint> 保存位置。

7. 创建视图

1、什么是视图？

(1)、视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候, 只是重新执行sql。

(2)、视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

(3)、视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

(4)、视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。

2、创建视图

create [or replace] view <view_name> as<select 语句>; 其中or replace --表示替换以有的视图

创建视图：create or replace view v_test01 as select * from emp

在查询视图：select * from v_test01；

查找用户视图：select * from user_views where view_name ='v_test01';

3、删除视图

用“drop view” 撤销。删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义.

drop view v$_emp_dept；删除视图

删除数据：delete from view_name where …

同样, 当视图依赖多个基表时, 不能使用此语句来删除基表中的数据. 只能删除依赖一个基表的数据.

delete from v_test01 where ename='cai30';

删除视图：drop view v_test01;

4、创建只读视图

(1)、创建只读视图

create or replace view v_test01 as select * from emp with read only

(2)、测试插入数据

insert into v_test01(ename,empno,job,deptno) values('cai30',9921,'salesman',10)

(3)、使用视图

select dname from dept, v$_temp where v$_temp.deptno = dept.deptno and grade = (select min(grade) from v$_temp);

8. 创建序列

1、什么是序列？

序列是oracle专有的对象，它用来产生一个自动递增的数列

2、创建序列

create sequence <sequencen_name> increment by n start with n

[maxvalue n][minvalue n] [cycle|nocycle] [cache n|nocache];

increment by n --表示序列每次增长的幅度;默认值为1.

start with n --表示序列开始时的***。默认值为1.

maxvalue n --表示序列可以生成的最大值(升序).

minvalue n --表示序列可以生成的最小值(降序).

cycle --表示序列到达最大值后，在重新开始生成序列.默认值为 nocycle。

cache --允许更快的生成序列.

示例:

create sequence se_1 increment by 1 start with 100 maxvalue 999999 cycle;

3、修改序列

alter sequence <sequencen_name> increment by n start with n

[maxvalue n][minvalue n] [cycle|nocycle] [cache n|nocache];

4、删除序列

drop sequence <sequence_name>

5、使用序列

(1)、currval

返回序列的当前值，注意在刚建立序列后,序列的currval值为null，所以不能直接使用。

可以先初始化序列:

方法：select <sequence_name>.nextval from dual;

示例：select se_1.nextval from dual; 之后就可以使用currval属性了

(2)、nextval 返回序列下一个值；

序列.nextval -->下个值

–select seq_empcopy_id.nextval from dual

–insert into empcopy (empno,ename) values (seq_empcopy_id.nextval, ‘TEST’);

(3)、查看序列的当前值

select <sequence_name>.currval from dual;

示例:select se_1.currval from dual;

11、oracle

9. 授权用户&角色

1、权限控制

使用system用户为scott增加权限：grant create view,create table to scott;

使用system用户为scott解锁：alter user scott account unlock;

2、用户管理

(1)创建用户

语法：create user username identified by password

红色字体为用户名密码。

create user test01 identified by bb123;

(2)查看用户是否创建

SQL>select username from dba_users;

(3)使用管理员账号创建用户

Sys system

(4)修改用户密码,将John用户的口令修改为 newpsw。

SQL> alter user John identified by newpsw;

(5)删除John操作：

SQL> drop user John cascade;

(6)测试John是否存在

SQL> conn John/[email protected];

3、用户授权

(1)账户授权语法：

grant privileges [ON object_name] to username

将权限privileges授予用户username

SQL>grant create session to John;

(2)授权：连接权限登录：

SQL>conn John/[email protected];

(3)将scott用户的emp表所有权限授予John，则使用下列命令：

SQL>grant all on scott.emp to John;

select * from scott.emp

(4)如果要收回授予用户John的scott用户表emp的所有权限，使用下列SQL语句：

SQL>revoke all on scott.emp from John;

(5)查看自己的权限

select * from user_sys_privs;

4、账户锁定和解锁

(1)alter user 用户名 account lock; （锁定）

(2)alter user 用户名 account unlock;（解锁)

11、oracle

10. 数据类型

11、oracle

1、

①number(x,y) ：数字类型，最长x位，y位小数

②varchar2（maxlength）：变长字符串,这个参数的上限是32767字节 ,声明方式如下VARCHAR2（L），L为字符串长度，没有缺省值，作为变量最大32767个字节

③char(max_length) 定长字符串最大2000字节

④DATE:日期类型 (只能精确到秒。)

⑤TIMESTAMP:时间戳 (精确到微秒)

⑥long：长字符串，最长2GB

了解类型

①CLOB：最大长度4G -->大对象很少使用:如果存在大对象，一般的解决方案存入文件地址（地址为程序所在应用服务器的相对路径）。

②BLOB：存二进制文件

11、oracle

2、数据库对象的命名规则

（1）必须以字母开头

（2）可包括数字和三个特殊字符（# _ $）

（3）不要使用oracle的保留字

（4）同一用户下的对象不能同名

11、oracle

11. sql语言

1、实验用数据表

(1)、scott用户表的介绍

select * from tab;//查询当前用户下的所有表,tab为系统表名

select * from user_tables;//详细的查询当前用户下的所有表, user_tables为系统表名。

desc 表名； //查看表结构

查看所有表：select table_name from user_tables;

查看表结构：describe dept;(或者desc dept;)

(2)、emp雇员表

Empno: 雇员工号 Ename: 雇员名字

Job:工作。（秘书、销售、经理、分析员、保管）

Mgr(manager):经理的工号 Hiredate:雇用日期

Sal: 工资 Comm: 津贴 Deptno: 所属部门号

(3)、dept部门表（department）

Deptno:部门号 Dname:部门名字 Loc: 地址

(4)、salgrade表à一个公司是有等级制度，用此表表示一个工资的等级

(5)、Grade:等级 losal:最低工资 hisal:最高工资

(6)、bonus奖金表：表示一个雇员的工资及奖金。

Ename:雇员名字， job:工作， sal: 工资 comm:津贴

2、添加注释

(1)、为表添加注释

comment on table emp is 雇员表';

(2)、为列添加注释

comment on column emp.Empno is '雇员工号';

3、select语句

(1)、检索单个列

select 列名 from 表名;

(2)、检索多个列

select 列名1, 列名2,列名3 from 表名;

(3)、检索所有列

select * from 表名;

使用通配符的优点：书写方便、可以检索未知列

使用通配符的缺点：降低检索的性能

(4)、给检索出的列起个别名

select job "gong zuo" from emp;

select job as "gong zuo" from emp;

注意只查询固定列数据的时候，建议不要使用 * 效率低！

(5)、select语句的算数表达式

对于number型数据可以使用算数操作创建表达式（+,,*,/）

对于DATE型数据可以使用部分算数操作创建表达式（+ -）

4、distinct必须放在开头

多字段，每个字段不一样才去重

5、where

条件比较

＝，！＝,<>（也是不等于），<,>,<=,>=,any,some,all

is null,is not null

between x and y

in（list），not in（list）

exists（sub－query）

like _ ,%,escape \ _\% escape \

6、逻辑复合条件

not，（and，or） and优先级高

列出deptno为10或者30，并且工资>2000的所有人。

select * from Emp where （deptno=30 or deptno=10） and sal>2000;

这个命令列出的人中薪水有<2000的，为什么（不能，因为and的优先级高于or）

计算次序问题的解决，最好用括号进行分组处理

7、SQL优化问题：

AND: 把检索结果较少的条件放到后面

OR: 把检索结果较多的条件放到后面

8、like

(1)、条件表达式中字符串匹配操作符是like

%通配符à表示任意字符出现任意次数

_通配符à表示任意字符出现一次

(2)、技巧和注意事项：

不能过度使用通配符。如果其他操作符能达到目的，就不要使用通配符。

确实需要使用通配符时，除非绝对必要，否则不要把通配符用到搜索模式最开始处，因为这样搜索起来是最慢的。

select ename from emp where ename like %ALL%;

select ename from emp where ename like _A%;

9、order by

(1)、按照单个列排序

order by col

(2)、降序和升序：

order by col desc (asc)

(3)、按多个列排序（优先级）

order by col1 desc(asc), col2 desc(asc)

10、创建计算的字段

(1)、为什么需要计算字段？

我们经常需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序中重新格式化。

(2)、计算字段并不实际存在于数据库表

(3)、Sql允许select子句中出现+，-，*，/以及列名和常数的表达式

(4)、拼接字段(||, +)

首选|| (mysql中||表示or，一般用concat() )

(5)、字符串的连接操作符

将列或者字符与其他列连接，用双竖线表示（||），用||可以把两列或者多列查询结果集合并到一起，产生的结果列是另一个字符表达式，连接表达式中出现字符数据，字符型的数据必须要使用 '',在连接表达式中，出现null，就是原来的字符型数据

select ename|| is a ||job from emp；（列名是ename|| is a ||job）

select ename, sal, sal * 12 from emp；

select ename, sal, sal * 12 + 500 from emp ；

select ename, sal, sal * （12 + 500） from emp ；

11、通用函数nvl（字段，0）如果不为null就是字段值，如果为nvl就是0

(1)、Sql中允许列值为空，空值用保留字NULL表示。NULL不同与0或者空格，它就是代表了一个不确定的内容。任何含有null值的数学表达式最后的结果都是null,null不等于0。空值+空字符串连接，也就是原来的字符串

(2)、空值是指不可用，为分配的值，空值不等于0或者空格，任意类型都可以支持空

(3)、空值的运算表达式，包括空值的任何类型算术表达式都等于空，

select ename,sal,comm,(sal*12)+comm income from emp;

可以通过函数nvl将NULL转换成为一个actual value

select ename, comm, nvl(comm,0) from emp

select ename,sal,comm ,(sal*12)+nvl(comm,0) income from emp

12、union all 全集 union 并集（去重） intersect 交集 minus 差集

(1)、union/union all运算：将查询的返回组合成一个结果， union all不过滤重复。

SELECT product_id FROM order_items UNION SELECT product_id FROM inventories;

SELECT location_id FROM locations UNION ALL SELECT location_id FROM departments;

(2)、intersect运算：返回查询结果中相同的部分。

SELECT product_id FROM inventories INTERSECT SELECT product_id FROM order_items;

13、minus运算：返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。

SELECT product_id FROM inventories MINUS SELECT product_id FROM order_items;

14、练习

--建议：所有查询的表都要加别名

select e.ename as "雇员姓名",e.sal as "雇员薪水" from emp e;

select e.ename "雇员姓名",e.sal "雇员薪水" from emp e;

select e.ename 雇员姓名,e.sal 雇员薪水 from emp e;

select e.ename "雇员姓名",e.sal 雇员薪水 from emp e;

--查询部门编号

--distinct去除重复的数据

select distinct e.deptno from emp e;

select e.deptno from emp e

注意：

(1)、SQL语言是不区分大小写的，包括登录的用户名，密码都是不区分大写的

(2)、在查询过程中，对于数值的数据，都可以执行+,,*,/运算，可以给查询字段起别名，有不同的方式，可以用""也可以不用，可用as,也可以不用，如果别名中间有空格要加""，否则报错

(3)、运算符的优先级

乘法和除法的顺序是从左到右，同级运算的顺序是从左到右，表达式中使用括号可增强改变优先级的运算顺序

12. sql函数

	字符函数名称		描述
	CONCAT(字符串1,字符串2)		将字符串1和字符串2连接成一个新的字符串示例: select CONCAT(job,ename) from emp
	LPAD(字段,总的大小,添充字符)		左填充即向右对齐示例: select empno,lpad(sal,10,'*') from emp
	RPAD(字段,总的大小,添充字符)		右填充即向左对齐示例: select empno,rpad(sal,10) from emp
	LOWER(字符串)		将字符串全部变成小写;
	UPPER(字符串)		将字符串全部变成大写;
	INITCAP(字符串)		将字符串变成第一个字母大写,其余都变成小写;
	LENGTH(字符串)		求出字符串的长度;
	SUBSTR(字符串,开始位置,长度)		从字符串中取子串; 示例: select substr(ename,2,3) from emp;--从ename的第2位开始取3位
	INSTR(字符串,字符)		查看字符是否在字符串中存在;不存在返回0;存在则返回字符所在的的位置；如果有两个以上的字符则返回第一个的位置.示例:select instr(ename,'S') from emp;
	TRIM(字符 FROM 字符串)		去掉字符串首尾的字符; 示例: select trim('S' from ename) from emp;
	TO_CHAR()		将不是其他类型转成字符类型；对于日期型可以控制其格式:TO_CHAR(日期,'格式'); 其中格式有: 'YYYY' --以4为显示年; 'YEAR' --以标准格式显示年； 'MM' ; 'MON' ; 'DD' ; 'DAY'; 'HH' ; 'MI' ;'SS'
	REPLACE(字符串,字符串1,字符串2)		将字符串中的字符1替换成字符2;示例: select replace(ename,'SC','SS') from emp;
	TRANSLATE(字符串,字符串1,字符串2)		替换多的字符;示例: select translate(ename,'SH','AB') from emp; --表示将ename中的'S'换成'A','H'换成'B';
	ASCII(char)		求字符的ascii码
	NLSSORT(字符串)		对字符串排序.
数学函数名称		描述
ABS(数字)		一个数的绝对值
	CEIL(数字)		向上取整;不论小数后的书为多少都要向前进位; CEIL(123.01)=124; CEIL(-123.99)=-123;
	FLOOR(数字)		向下取整;不论小数后的书为多少都删除; floor(123.99)=123;floor(-123.01)=-124;
	MOD(被除数,除数)		取余数;MOD(20,3)=2
	ROUND(数字,从第几为开始取)		四舍五入;ROUND(123.5,0)=124; ROUND(-123.5,0)=-124; ROUND(123.5,-2)=100;ROUND(-123.5,-2)=-100;
	SIGN(数字)		判断是正数还是负数;正数返回1，负数返回-1，0返回0;
	SQRT(数字)		对数字开方；
	POWER(m,n)		求m的n次方；
TRUNC(数字,从第几位开始)		切数字;TRUNC(123.99,1)=123.9 TRUNC(-123.99,1)=-123.9 TRUNC(123.99,-1)=120 TRUNC(-123.99,-1)=-120 TRUNC(123.99)=123
GREATEST(数字列表)		找出数字列表中最大的数;示例: select greatest(100,200,-100) from dual; --结果为200
	LEAST(数字列表)		找出数字列表中最小的数;
	SIN(n)		求n的正旋
	COS(n)		求n的余旋
	TAN(n)		求n的正切
	ACos(n)		求n的反正切
	ATAN(n)		求n的反正切
	exp(n)		求n的指数
	LN(n)		求n的自然对数,n必须大于0
	LOG(m,n)		求n以m为底的对数,m和n为正数,且m不能为0
日期函数名称		描述
ADD_MONTHS(日期,数字)		在以有的日期上加一定的月份;示例:select add_months(hiredate,20),hiredate from emp;
LAST_DAY(日期)		求出该日期的最后一天.
	MONTHS_BETWEEN(日期1，日期2)		求出两个月之间的天树（注意返回的天数为小数);示例:select months_between(sysdate,hiredate) from emp;
	NEW_TIME(时间,时区,'gmt')		按照时区设定时间.
	NEXT_DAY(d,char)		返回d指定的日期之后并满足char指定条件的第一个日期
	其他函数名称		描述
	VSIZE(类型)		求出数据类型的大小;
	NVL(字符串,替换字符)		如果字符串为空则替换，否则不替换

1、SQL函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只将取出的数据进行处理，不会改变数据库中的值。

2、Sql函数可以分为组函数和单行函数。

组函数又被称作聚合函数，用于对多行数据进行操作并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句。

单行函数对单个数值进行操作，并返回一个值。

11、oracle

3、字符函数

字符函数全以字符作为参数，返回值分为两类：一类返回字符值，一类返回数字值

–concat（string1，string2）连接两个字符串 ||

–initcap（string）string中每个单词首字母大写

–Lower(string) 以小写形式返回string

–lpad,rpad 填充字符型数据向右填充左对齐，向左填充右对齐

–ltrim/rtrim （string1,string2）

–trim(A from B)

–Substr（）提取字符串的一部分substr（string，1，2）

–upper(string）以大写形式返回string

–Instr（）字符串出现的位置, instr（ string ，’A‘）

–Length（）字符串长度

4、时间函数

select current_time() from dual;---- mysql:时间。

select current_date() form dual; ---mysql；日期

select current_timestamp() from dual;---mysql：日期时间

5、mysql日期和字符相互转换方法

date_format(date,'%Y-%m-%d') -------------->oracle中的to_char(); 日期格式化

str_to_date(‘date’,’%Y-%m-%d') --------->oracle中的to_date(); 字符串转为日期格式

	%Y：代表4位的年份 %y：代表2位的年份	%m：代表月, 格式为(01……12)
	%c：代表月, 格式为(1……12)	%d：代表月份中的天数,格式为(00……31)
	%e：代表月份中的天数, 格式为(0……31)	%H：代表小时,格式为(00……23)
	%k：代表小时,格式为(0……23)	%h：代表小时,格式为(01……12)
	%I：代表小时,格式为(01……12)	%l：代表小时,格式为(1……12)
	%i：代表分钟, 格式为(00……59)	%r：代表时间,格式为12 小时(hh:mm:ss [AP]M)
	%T：代表时间,格式为24 小时(hh:mm:ss)	%S：代表秒,格式为(00……59) %s：代表秒,格式为(00……59)

6、mysql时间字符转换

select date_format(now(),'%Y');

select date_format(now(),'%Y-%c-%d %h:%i:%s');

SELECT STR_TO_DATE('Jul 20 2013 7:49:14:610AM','%b %d %Y %h:%i:%s:%f%p') from DUAL;

-- 执行后得到结果: 2013-07-20 07:49:14.610000

7、数字函数

(1)、数字函数以NUMBER类型为参数返回NUMBER值

round（number，n）返回四舍五入后的值，n如果为正整数则是保留的小数位，如果为负数就是小数点左其n为开始四舍五入

–select round(23.652) from dual; 24

–select round(23.652, 2) from dual; 23.65 保留两位小数

–select round(23.652, -1) from dual; 20 从小数点向左一位开始，四舍五入

–select round(23.652, -2) from dual; 0

(2)、trunc（number，n）

–select trunc(23.652) from dual; 23

–select trunc(23.652, 2) from dual; 23.65

–select trunc(23.652, -1) from dual; 20

(3)、mod（x，y）求余数

–select mod(13,5) from dual;

(4)、ceil()上取整 select ceil(19.2) from dual; 20

(5)、floor()下取整 select floor(19.2) from dual; 19

8、日期和时间函数

(1)、Oracle以内部数字格式存储日期:世纪,年,月,日,小时,分钟,秒

–sysdate/current_date

(2)、以date类型返回当前的日期

–Add_months(d,x) 返回加上x月后的日期d的值

–LAST_DAY(d) 返回的所在月份的最后一天

–Months_between(date1,date2) 返回date1和date2之间月的数目

(3)、从日期中加或减一个数值,以得当一个日期结果值

–select sysdate+2 from dual;

–select sysdate-2 from dual;

(4)、两个日期相减以便得到他们相差多少天

–select ename,round((sysdate-hiredate)/7) weeks from emp where deptno=10

9、转换函数

(1)、标量数据可以有类型的转换，转换分为两种，隐式类型转换和显示类型转换。

(2)、隐式类型转换可用于：

–字符和数字的相互转换 &字符和日期的相互转换

–VARCHAR2 or char－－number

–VARCHAR2 or char －－date

–number－－varchar2

–date－－varchar2

–select * from emp where empno=to_number('8000')

–select * from emp where hiredate='20-2月-1981'

(3)、尽管数据类型之间可以进行隐式转换，仍建议使用显示转换函数，以保持良好的设计风格。

Select ‘999’-10 from dual;

11、oracle

10、TO_CHAR函数操作日期

11、oracle

11、TO_CHAR(date, 'fmt')

(1)、用于将日期或时间戳转换成varchar2类型字符串，如果指定了格式字符串，则用它控制结果的结果。

–格式控制串由格式元素构成。

–格式控制串必须用单引号括起来

select to_char(sysdate, 'dd-mon-yy hh24:mi:ss') "Rigth Now" from dual;

select ename, hiredate, to_char(hiredate,'yyyy/mm/dd') from emp

select sysdate, to_char(sysdate,'yyyy-mon-dd hh12:mi:ss') from dual;

12、to_char（num,format）

(1)、用于将Number类型参数转换为varchar2类型，如果指定了format，它会控制整个转换。

select to_char(sal, ‘$99,999.9999’) salary from emp where ename = ‘ALLEN’;

select to_char(sal, ‘$00,000.0000’) salary from emp where ename = ‘ALLEN’;

select to_char(123456, '99,99,00') from dual;

13、to_number & to_data

(1)、to_date (String,format)

–将char或varchar2类型的string转换为date类型

–Select to_date('04,05,19,10,23,40','yy,mm,dd,hh12,mi,ss') from dual;

–select to_date('2004-09-19','yyyy-mm-dd') from dual;

(2)、to_number(String,format)

–将char或varchar2类型的string转换为number类型

11、oracle

–select to_number('$39343.783','$99990.000') from dual;

–select to_number('11.231','999.999') from dual;

14、单行函数嵌套

单行函数可被嵌入到任何层

嵌套函数从最深层到最低层求值

11、oracle

（1）显示没有上级管理的公司首脑

–没有上级领导的雇员 mgr显示为boss

–select ename,nvl(to_char(mgr),’boss’) from emp where mgr is null;

（2）显示员工雇佣期满6个月后下一个星期五的日期

–Select next_day(add_months(hiredate,6),’Friday’) from emp order by hiredate;

17、其他函数

（1）显示没有上级管理的公司首脑

–没有上级领导的雇员 mgr显示为boss

–select ename,nvl(to_char(mgr),’no manager’)

from emp where mgr is null;

（2）显示员工雇佣期满6个月后下一个星期五的日期

–Select to_char(next_day(add_months(hiredate,6),’Friday’),’ fmDay,Month ddth,YYYY’) “review”

from emp order by hiredate;

18、练习：

--1、查询82年员工

select * from emp e where to_char(e.hiredate,'yyyy') = '1982';

--2、查询36年工龄的人员

--更改这样比较简单（精确到天）

select *from emp e where round((sysdate-e.hiredate)/365)=36;

--3、显示员工雇佣期 6 个月后下一个星期一的日期

select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'星期一') from emp e;

--4、找没有上级的员工，把mgr的字段信息输出为 "boss"

--使用decode

select e.ename,decode(nvl(e.mgr,0),0,'BOSS' ) mgr from emp e where e.mgr is null;

select e.ename,case nvl(e.mgr,0) when 0 then 'BOSS' end mgr from emp e where e.mgr is null;

--5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%

select e.*,decode(e.deptno,10,e.sal*1.1, 2 0,e.sal*1.15,30,e.sal*1.2,e.sal*1.18) 涨薪 from emp e;

13. 数值型函数

ABS(x)

【功能】返回x的绝对值

【参数】x，数字型表达式

【返回】数字

【示例】

select abs(100),abs(-100) from dual

sign(x)

【功能】返回x的正负值

【参数】x，数字型表达式

【返回】数字，若为正值返回1，负值返回-1，0返回0

【示例】

select sign(100),sign(-100),sign(0) from dual;

返回较大的最小整数ceil()

返回较小的最大整数floor()

返回截取后的值trunc()

power(x,y)

【功能】返回x的y次幂

【参数】x，y 数字型表达式

【返回】数字

【示例】

select power(2.5,2),power(1.5,0),power(20,-1) from dual;

返回：6.25,1,0.05

【相近】exp(y)

返回e的y次幂。（e为数学常量）

【关系】z=power(x,y),则y=1/log(z,x) (条件z,x>0)

exp(y)

【功能】返回e的y次幂（e为数学常量）

【参数】y，数字型表达式

【返回】数字

【示例】

select exp(3),exp(0),exp(-3) from dual;

返回：20.0855369,1 ,0.049787068

【相近】power(x,y)

返回e的y次幂。

【相反】ln(y)

返回e为底的自然对数。

log(x,y)

【功能】返回以x为底的y的对数

【参数】x，y,数字型表达式，

【条件】x,y都必须大于0

【返回】数字

【示例】

select power(4,2),log(16,2),1/log(16,4) from dual;

返回：16,0.25,2

select power(6.5,3),log(274.625,3),1/log(power(6.5,3),6.5) from dual;

返回： 274.625 ， 0.195642521 ， 3

【相近】ln(y)

返回e为底的y的对数。（e为数学常量）

【关系】z=power(x,y),则y=1/log(z,x) (条件z,x>0)

ln(y)

【功能】返回以e为底的y的对数（e为数学常量）

【参数】y，数字型表达式 (条件y>0)

【返回】数字

【示例】

select exp(3),exp(-3),ln(20.0855369),ln(0.049787068) from dual;

返回：20.0855369 , 0.049787068 , 3 , -3

【相近】log(x,y)

返回以x为底的y的对数

【相反】exp(y)

返回e的y次幂

mod(x,y)

【功能】返回x除以y的余数

【参数】x,y，数字型表达式

【返回】数字

【示例】

select mod(23,8),mod(24,8) from dual;

返回：7,0

round(x[,y])

【功能】返回四舍五入后的值

【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。

【返回】数字

【示例】

select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;

返回： 5555.67 , 5600 , 5556

【相近】trunc(x[,y])

返回截取后的值，用法同round(x[,y]),只是不四舍五入

sqrt(x)

【功能】返回x的平方根

【参数】x数字型表达式

【返回】数字

【示例】

select sqrt(64),sqrt(10) from dual;

返回：8 , 3.16227766

三角函数

SIN(x)

【功能】返回一个数字的正弦值

【示例】select sin(1.57079) from dual;

返回： 1

SIGH(x)

【功能】返回双曲正弦的值

【示例】select sin(20),sinh(20) from dual;

返回：0.91294525, 242582598

COS(x)

【功能】返回一个给定数字的余弦

【示例】select cos(-3.1415927) from dual;

返回： -1

COSH(x)

【功能】返回一个数字反余弦值

【示例】select cosh(20) from dual;

返回：242582598

TAN

【功能返回数字的正切值

【示例】select tan(20),tan(10) from dual;

返回：2.2371609 ,0.64836083

TANH

【功能返回数字n的双曲正切值

【示例】select tanh(20),tan(20) from dual;

返回：1 ,2.2371609

ASIN(x)

【功能】给出反正弦的值

【示例】select asin(0.5) from dual;

返回：0.52359878

ACOS(x)

【功能】给出反余弦的值

【示例】select acos(-1) from dual;

返回：3.1415927

ATAN(x)

【功能】返回一个数字的反正切值

【示例】 select atan(1) from dual;

返回：0.78539816

14. 字符型函数

ASCII(x1)

【功能】：返回字符表达式最左端字符的ASCII 码值。

【参数】：x1，字符表达式

【返回】：数值型

【示例】

SQL> select ascii('A') A,ascii('a') a,ascii(' ') space,ascii('示') hz from dual;

A A SPACE hz

--------- --------- --------- ---------

65 97 32 51902

【说明】在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。

如果最左端是汉字，只取汉字最左半边字符的ASCII 码

【互反函数】：chr()

CHR(n1)

【功能】：将ASCII 码转换为字符。

【参数】：n1,为0 ~ 255，整数

【返回】：字符型

【示例】

SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C

-- -

赵 A

【互反函数】：ASCII

CONCAT(c1,c2)

【功能】连接两个字符串

【参数】c1，c2 字符型表达式

【返回】字符型

同：c1||c2

【示例】

select concat('010-','88888888')||'转23' 高乾竞电话 from dual;

高乾竞电话

----------------

010-88888888转23

INITCAP(c1)

【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;

【参数】c1字符型表达式

【返回】字符型

【示例】

SQL> select initcap('smith abc aBC') upp from dual;

UPP

-----

Smith Abc Abc

LOWER(c1)

【功能】：将字符串全部转为小写

【参数】：c1，字符表达式

【返回】：字符型

【示例】

SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

【同类】UPPER()将字符串全部转为大写。

UPPER(c1)

【功能】将字符串全部转为大写

【参数】c1，字符表达式

【返回】字符型

【示例】

SQL> select upper('AaBbCcDd') upper from dual;

UPPER

--------

AABBCCDD

【同类】LOWER()将字符串全部转为小写

NLS_INITCAP(x[,y])

【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;

【参数】x字符型表达式

【参数】Nls_param可选，

查询数据级的NLS设置:select * from nls_database_parameters;

例如：

指定排序的方式(nls_sort=) 。

nls_sort=SCHINESE_RADICAL_M（部首、笔画）

nls_sort=SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））

【返回】字符型

【示例】

select nls_initcap('ab cde') "test",

nls_initcap('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;

返回：Ab Cde, A C B D E

NLS_LOWER(x[,y])

【功能】返回字符串并将字符串的变为小写;

【参数】x字符型表达式

【参数】Nls_param可选，指定排序的方式(nls_sort=) 。

SCHINESE_RADICAL_M（部首、笔画）

SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））

【返回】字符型

【示例】

select nls_LOWER('ab cde') "test",nls_LOWER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;

返回：ab cde,a c b d e

select nls_initcap('ab cde') "test",nls_initcap('a c b d e','NLS_LANGUAGE=AMERICAN') "test1" from dual;

NLS_UPPER(x[,y])

【功能】返回字符串并将字符串的转换为大写;

【参数】x字符型表达式

【参数】Nls_param可选，指定排序的方式(nls_sort=) 。

SCHINESE_RADICAL_M（部首、笔画）

SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））

【返回】字符型

【示例】

select NLS_UPPER('ab cde') "test",NLS_UPPER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;

返回：AB CDE,A C B D E

INSTR(C1,C2[,I[,J]])

【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

【说明】多字节符(汉字、全角符等)，按1个字符计算

【参数】

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 第J次出现的位置,默认为1

【返回】数值

【示例】select instr('oracle traning','ra',1,2) instring from dual;

返回：9

【示例】select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;

返回：3,5

INSTRB(C1,C2[,I[,J]])

【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

【说明】多字节符(汉字、全角符等)，按2个字符计算

【参数】

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 第J次出现的位置,默认为1

【返回】数值

【示例】select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;

返回：3,5

LENGTH(c1)

【功能】返回字符串的长度;

【说明】多字节符(汉字、全角符等)，按1个字符计算

【参数】C1 字符串

【返回】数值型

【示例】

SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;

length('高乾竞') length('北京市海锭区') length('北京TO_CHAR')

----------------- ---------------- ----------------------------

3 6 9

LENGTH(c1)

【功能】返回字符串的长度;

【说明】多字节符(汉字、全角符等)，按2个字符计算

【参数】C1 字符串

【返回】数值型

【示例】

SQL> select length('高乾竞'),lengthB('高乾竞') from dual;

length('高乾竞') lengthB('高乾竞')

----------------- ----------------

3 6

LENGTHC(c1).LENGTH2(c1).LENGTH4(c1)

【功能】返回字符串的长度;

【说明】多字节符(汉字、全角符等)，按1个字符计算

【参数】C1 字符串

【返回】数值型

【示例】

SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;

Oracle中的字符函数中，有一类函数是求字符长度的函数，length、lengthB、lengthC、length2、length4几个函数中比较常用的是length、lengthB。

他们的含义分别是：

Length函数返回字符的个数，使用定义是给定的字符集来计算字符的个数

LENGTHB给出该字符串的byte

LENGTHC使用纯Unicode

LENGTH2使用UCS2

LENGTH4使用UCS4

下面使一些例子：

Select length('你好') from dual; 2

Select lengthB('你好'),lengthC('你好'),length2('你好'), length4('你好') from dual;

LPAD(c1,n[,c2])

【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止

【参数】C1 字符串

n 追加后字符总长度

c2 追加字符串,默认为空格

【返回】字符型

【说明】如果c1长度大于n，则返回c1左边n个字符

如果如果c1长度小于n，c2和c1连接后大于n，则返回连接后的右边n个字符

【示例】

SQL> select lpad('gao',10,'*') from dual;

lpad('gao',10,'*')

-----------------

*******gao

不够字符则用*来填满

【相似】RPAD()在列的右边粘贴字符

【相反】LTRIM() 删除左边出现的字符串

RTRIM(c1,[,c2])

【功能】删除右边出现的字符串

【参数】C1 字符串

c2 追加字符串,默认为空格

【返回】字符型

【示例】

SQL> select RTRIM('gao qian jingXXXX','X') text from dual;

text

-----------------

gao qian jing

【相似】LTRIM()删除左边出现的字符串

【相反】RPAD() 在列的右边粘贴字符

REPLACE(c1,c2[,c3])

【功能】将字符表达式值中，部分相同字符串，替换成新的字符串

【参数】

c1 希望被替换的字符或变量

c2 被替换的字符串

c3 要替换的字符串，默认为空(即删除之意，不是空格)

【返回】字符型

【示例】

SQL> select replace('he love you','he','i') test from dual;

test

------------------------------

i love you

SUBSTR(c1,n1[,n2])

【功能】取子字符串

【说明】多字节符(汉字、全角符等)，按1个字符计算

【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.

【返回】字符型

【示例】

SQL> select substr('13088888888',3,8) test from dual;

test

--------

08888888

SUBSTRB(c1,n1[,n2])

【功能】取子字符串

【说明】多字节符(汉字、全角符等)，按2个字符计算

【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.

【返回】字符型,如果从多字符右边开始,则用空格表示。

【示例】

select substr('我手机13012345678',4,11),substrb('我手机13012345678',4,11),substrb('我手机13012345678',3,11) test from dual;

返回:13012345678, 机13012345,手机1301234

TRANSLATE(c1,c2,c3)

【功能】将字符表达式值中，指定字符替换为新字符

【说明】多字节符(汉字、全角符等)，按1个字符计算

【参数】

c1 希望被替换的字符或变量

c2 查询原始的字符集

c3 替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符

如果c3长度大于c2，则c3长出后面的字符无效

如果c3长度小于c2，则c2长出后面的字符均替换为空(删除)

如果c3长度为0，则返回空字符串。

如果c2里字符重复，按首次位置为替换依据

【返回】字符型

【示例】

select TRANSLATE('he love you','he','i'),

TRANSLATE('重庆的人','重庆的','上海男'),

TRANSLATE('重庆的人','重庆的重庆','北京男士们'),

TRANSLATE('重庆的人','重庆的重庆','1北京男士们'),

TRANSLATE('重庆的人','1重庆的重庆','北京男士们') from dual;

返回：i love you,上海男人,北京男人,1北京人,京男士人

删除左边和右边字符串trim()

15. 日期函数

sysdate

【功能】：返回当前日期。

【参数】：没有参数，没有括号

【返回】：日期

【示例】select sysdate hz from dual;

返回：2008-11-5

返回指定月数后的日期add_months();

返回2个日期间隔月数month_between();

四舍五入后的期间的第一天round()

返回日期所在期间的第一天trunc()

返回下周某一天的日期next_day()

last_day(d1)

【功能】：返回日期d1所在月份最后一天的日期。

【参数】：d1，日期型

【返回】：日期

【示例】select sysdate,last_day(sysdate) hz from dual;

返回：2008-11-5，2008-11-30

NEW_TIME(dt1,c1,c2)

【功能】：给出时间dt1在c1时区对应c2时区的日期和时间

【参数】：dt1，d2 日期型

【返回】：日期时间

【参数】：c1,c2对应的时区及其简写

大西洋标准时间：AST或ADT

阿拉斯加_夏威夷时间：HST或HDT

英国夏令时：BST或BDT

美国山区时间：MST或MDT

美国中央时区：CST或CDT

新大陆标准时间：NST

美国东部时间：EST或EDT

太平洋标准时间：PST或PDT

格林威治标准时间：GMT

Yukou标准时间：YST或YDT

【示例】

select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,

to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

BJ_TIME LOS_ANGLES

------------------- -------------------

2008.11.05 20:11:58 2008.11.06 03:11:58

【示例】

select sysdate bj_time,

new_time(sysdate,'PDT','GMT') los_angles from dual;

BJ_TIME LOS_ANGLES

------------------- -------------------

2008-11-05 20:11:58 2008-11-06 03:11:58

extract(c1 from d1)

【功能】：日期/时间d1中，参数(c1)的值

【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)

【参数表】：c1对应的参数表详见示例

【返回】：字符

【示例】

select extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,

extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,

extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年 from dual;

select extract (YEAR from date '2001-2-16' ) from dual;

select sysdate 当前日期,extract(hour from timestamp timestamp sysdate) 小时,extract(DAY from sysdate ) 日,

extract(MONTH from sysdate ) 月,extract(YEAR from sysdate ) 年 from dual;

localtimestamp

【功能】：返回会话中的日期和时间

【参数】：没有参数，没有括号

【返回】：日期

【示例】select localtimestamp from dual;

返回：14-11月-08 12.35.37.453000 上午

current_timestamp

【功能】：以timestamp with time zone数据类型返回当前会话时区中的当前日期

【参数】：没有参数，没有括号

【返回】：日期

【示例】select current_timestamp from dual;

返回：14-11月-08 12.37.34.609000 上午 +08:00

current_date

【功能】：返回当前会话时区中的当前日期

【参数】：没有参数，没有括号

【返回】：日期

【示例】select current_date from dual;

返回：2008-11-14

dbtimezone

【功能】：返回时区

【参数】：没有参数，没有括号

【返回】：字符型

【示例】select dbtimezone from dual;

SESSIONTIMEZONE

【功能】：返回会话时区

【参数】：没有参数，没有括号

【返回】：字符型

【示例】select dbtimezone,SESSIONTIMEZONE from dual;

返回:+00:00 +08:00

INTERVAL c1 set1

【功能】：变动日期时间数值

【参数】：c1为数字字符串或日期时间字符串，set1为日期参数

【参数表】：set1具体参照示例

【返回】：日期时间格式的数值,前面多个+号

以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟

【示例】

select

trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)

trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)

trunc(sysdate)+(interval '1' hour), --加1小时(1/24)

trunc(sysdate)+(INTERVAL '1' DAY), --加1天(1)

trunc(sysdate)+(INTERVAL '1' MONTH), --加1月

trunc(sysdate)+(INTERVAL '1' YEAR), --加1年

trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒

trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒

trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟

trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟

from dual;

16. 转换函数

chartorowid(c1) 。。

【功能】转换varchar2类型为rowid值

【参数】c1,字符串，长度为18的字符串，字符串必须符合rowid格式

【返回】返回rowid值

【示例】

SELECT chartorowid('AAAADeAABAAAAZSAAA') FROM DUAL;

【说明】

在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。

在重复的记录中，可能所有列的内容都相同，但rowid不会相同.

ROWIDTOCHAR(rowid) 。。

【功能】转换rowid值为varchar2类型

【参数】rowid,固定参数

【返回】返回长度为18的字符串

【示例】

SELECT ROWIDTOCHAR(rowid) FROM DUAL;

【说明】

在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。

在重复的记录中，可能所有列的内容都相同，但rowid不会相同.

CONVERT(c1,set1,set2)

【功能】将源字符串c1 从一个语言字符集set2转换到另一个目的set1字符集

【参数】c1,字符串，set1,set2为字符型参数

【返回】字符串

【示例】

select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver

------

strutz

select convert(name,'us7ascii','zhs16cgb231280') "conversion" from dual;

HEXTORAW(c1)

【功能】将一个十六进制构成的字符串转换为二进制

【参数】c1,十六进制的字符串

【返回】字符串

【示例】

select HEXTORAW('A123') from dual;

RAWTOHEX(c1)

【功能】将一个二进制构成的字符串转换为十六进制

【参数】c1,二进制的字符串

【返回】字符串

【示例】

select RAWTOHEX('A123') from dual;

TO_CHAR(x[[,c2],C3])

【功能】将日期或数据转换为char数据类型

【参数】

x是一个date或number数据类型。c2为格式参数，c3为NLS设置参数

如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。

如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。

NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

【返回】varchar2字符型

【说明1】x为数据型时

c1格式表参考：

序号格式简例说明

1 ,(逗号) '9999,999' 逗号,一般以千分位出现,作为分组符号使用.如果需要您也可以当作是十分位,百分位出现,可以出现N次,视乎数字的大小而定.

变态的例子是 to_char(1234,'9,9,9,9').

注意事项:只能出现在整数部分.

2 .(点号) '99.99' 点号,不要念为"句号",句号是个圆圈,点好只能出现在小数点对应的地方.只能出现一次.

to_char(1234.34,'9,9,9,9.99')

注意事项:只能出现在一个地方,就是原来数据小数点位置

3 $(美元符号) '$999.99' 美元.其实你可以放在任意地方(在10G下)

to_char(1234.34,'9,9,9,9.$99')

注意事项:只能出现一次.

4 0(零) '0999.99' 零.在对应位置返回对应的字符,如果没有则以'0'填充.

to_char(0.34,'9,9,9,0.$99')='$0.34';to_char(1234,'9999.00')='1234.00';

注意事项:这是一个强制的符号,对应位没有,则以'o'填充,这是9很大不同地方

5 9 '999.99' 9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符.

to_char(123,'999.99')=123.00; TO_CHAR(123,'99999.9')=123.0;

注意事项:对于0和9而言,如果格式的位数不如数字的位数多,会返回'#'.

譬如to_char(12345,'9999')='#####'

6 B(空格符) 'B999' 没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置.

'S'||TO_CHAR(1234,'99B99')='S 1234';

注意事项:只能出现在整数部位.

7 C(国际货币符号) 'C9999' 在特定的位置返回一个ISO货币符号(就是NLS_ISO_CURRENCY参数所代表的值)

TO_CHAR(1233,'C9999')='CNY1234' ,这是新的国际标准RMB,关于这个可查询"国际货币符号"

注意事项:只能出现在整数部位第一位.

可以通过alter session set NLS_ISO_CURRENCY='JAPAN';来修改当前会话的设置.

8 D(ISO 小数位符号) '999D99' 这是"点号"的国际版本(ISO),作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号.

注意事项:没有特别需要一般不要用这个格式符号.也不要轻易修改参数值.

也可用alter sesssion set 来修改.

alter session set nls_numeric_characters='!,'; to_char(1234.34,'9999d99')=1234!34

9 EEEE(科学计算符) 9.9EEEE 科学计算符号

TO_CHAR(2008032001,'9.9EEEE')='2.01E+09',由于是科学计算方法,所以小数位前面加一个9或者0即可,多个是没有意义的.

10 G(分组符号) 999G999 是逗号(,)的的ISO标准,作为分组符号使用,可以放在多个地方使用.

TO_CHAR(123456,'999G9G99')=123,4,56

注意事项:同第八项 -D, 此外如果要转换出小数点,则要和D配合使用,不能和点号配合.

11 L(本地货币符号) 'L999' 是C的本地版本.可以放在整个格式的最前面和最后面.

TO_CHAR(123456,'999G9G99D00L')=123,4,56.00￥

注意事项:同第七项 C

12 MI(负号) '9999MI' 如果是负数,在尾部加上负号(-),如果是正数,则尾巴加上空格

to_char(1234,'9999mi')||'S'||TO_CHAR(-5678,'9999MI') =1234 S5678-

注意事项:只能放在格式尾巴

13 PR(符号) 9999PR 是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来.

TO_CHAR(-1234.89,'9G999D00PR')=<1,234.89>

注意事项:同12

14 RN(rn) RN(rn) 把整数(1-3999)转换为罗马字符.RN表示转为大写,rn表示小写的.

declare

i int;

begin

for i in 1..20 loop

dbms_output.put_line(to_char(i,'RN'));

end loop;

end;

注意事项:只能自己使用,不能和其它符号组合使用.

15 S '9999S' 是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后.

TO_CHAR(-1234,'S9999')=-1234;TO_CHAR(1234,'S9999')=+1234

16 TM TM9/TMe 使用这个参数等于没有用参数to_char(number)一样,应为'tm9'是默认的格式参数.

to_char(1234,'tme')=1234

注意事项:格式要么是TM9,要么是TME.

当数字长度超过64位时候,TM9的输出等同于TME的输出.

17 U U999 双币符号,例如欧元.作用同11的L

TO_CHAR(999,'U999')=￥999

注意事项：通过NLS_DUAL_CURRENCY 控制

18 V 999V9 这是个比较古怪，又不是很常使用的符号。它的作用在于做一个计算。

例如TO_CHAR(N,'999V9'),以p表示V的位置,则该表达式＝to_char(N×(10的P-1次方)).但是9个数又必须保证大于等于乘积之后表示的位数.

TO_CHAR(5,'9V')=5*1=5;

TO_CHAR(5,'9V9')=5*10=50

TO_CHAR(5,'9V99')=500

TO_CHAR(50,'9V99')='######' 9的个数不够

注意事项：格式中不能和小数表达写在一起，但是可以混合货币等。

19 X xxxx 转换为16进制。

TO_CHAR(100,'XX')= 64

注意事项：数值必须是大于等于0的整数。前面只能和0或者FM组合使用.

20 　　通过以上的例子，我们了解了各种数字的格式。可以说格式太多样，难于记在脑子，最好是作为一个参考存在着.

归类：

数值类： 0,9,

分组类： (.),(,),D,G ,其中点好和逗号因为表示不明显，所以用小括号凸显。

货币类：　$,C,L,U

计算转换类：EEEE,RN,V,X

正负符号：MI,PR,S

其它类：B

正统类：TM

【示例】

to_char(1210.73, '9999.9') 返回 '1210.7'

to_char(1210.73, '9,999.99') 返回 '1,210.73'

to_char(1210.73, '$9,999.00') 返回 '$1,210.73'

to_char(21, '000099') 返回 '000021'

to_char(852,'xxxx') 返回' 354'

【说明2】x为日期型,c2可用参数

序号格式简例说明

1 - / , . ; : 略时间分隔符号，除了标准的几个，还允许用文字作为分割符号。

"text" 例如 to_char(sysdate,'YYYY"年"mm"月"dd"日"')=2008年04月24日

2 AD 　即拉丁文Anno Domini的简写，表示公元.会根据nls的不同转换为公元或者ad等

A.D. 无特殊注意事项

3 AM 　上午的简写 ,同pm, p.m. （下午) , 中文环境输出为上午（如果是上午）

A.M.

4 BC 　虽然标准的写法是B.c. (c小写）或者BC,好在Oracle不讲究这个。表示公元前

B.C.

5 CC 　返回世纪，以阿拉伯数字表示

SCC 如果年的后两位介于01-99那么，返回前两位+1，否则返回前两位

6 D 　一周之中的某天，返回的是序号1-7

7 DAY 　一周之中的某天，不过返回的是星期几而已，这和语言设置有关系，在中国环境 NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE ，用星期一到星期天表示

8 DD 　月份中的某天(1-31)

9 DDD 　年份中的某天（1-366）

10 DL 'DL' 返回长的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。例 2008年4月28日星期一

限制：除了DL，其它什么的都不能设置。

11 DS 　返回短的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。例如 2008-04-28

限制：除了DL，其它什么的都不能设置。

12 DY 　日期的简称，就是星期几（当然这指的是中国环境下)

13 E 　纪元简称，但是只适合以下集中日历：日本皇室，中华民国,太过佛历

14 EE 　纪元全程，适合情况同E

15 FF [1..9] 　就是毫秒,如果不更上数字就是用默认的精度。

只能用于timestamp类型的。

16 FM 　值得注意的一个函数：不返回任何内容。

有点不明白oracle为什么设置这个东西.

17 FX 　同上

18 HH 　表示小时，为12小时制，同hh12(1-12)

19 HH12 　表示小时，为12小时制(1-12)

20 HH24 　表示小时，为24小时制（0-23）

21 IW 　 ISO标准的星期序号(1-52,或者1-53）

22 IYYY 　 IYY,IY,I, ISO年(4位）的4,3，2，1位数字(倒数）

IYY to_char(to_date(21120401,'yyyymmdd'),'iyyy, iyy,iy,i')=2112, 112,12,2

23 J 　儒略日(多用于天文的一种日历）,从公元前4712年一月一日算起，得出的结果是个整数，算法大体为（公元日期+4712）*儒略日历年平均天数

24 MI 　秒（0-59）

25 MM 　 2位月（1-12）

26 MON 　月的简称，和国家有关系NLS_DATE_LANGUAGE，例如04在中文环境下用4月表示.

27 MONTH 　月的名称，国家有关系NLS_DATE_LANGUAGE,目前在中文下04表示为4月。

28 PM 　同am,a.m.表示下午

P.M.

29 Q 　季度（1-4）

30 RM 　用罗马数字表示的月份，I ,II ,III ,IV ,V ,VI ,VII ,VIII,IX ,X ,XI ,XII

31 RR 　有点四舍五入表示年的意思，具体的用法有那么一点点复杂。

以s表示输入的年份最后两位，c表示当前的年份最后两位,其输出结果(新的年份前两位）可以用函数r=f(s,c)来表示,s2,c2分别表示s,c的前两位。

1）s=[0,49],c=[0,49],则r=c2

2) s=[0,49],c=[50,99],则 r=c2+1

3) s=[50,99],c=[0,49],则r=c2-1

4) s=[50,99],c=[50,99],则 r=c2

简而言之就是靠近当前年份原则，如果和当前年份同区域那么就一样，如果比当前区域大，那么就是当作是当前世纪前一世纪，否则就是下一个世纪。

举例来说，以to_date为例子

SQL> select to_date('89-01-01','rr-mm-dd') ,to_date('12-01-01','rr-mm-dd') FROM DUAL;

TO_DATE('89-01-01','RR-MM-DD') TO_DATE('12-01-01','RR-MM-DD')

------------------------------ ------------------------------

1989-01-01 2012-01-01

我想oracle会搞这个东东出来，估计有两个考虑一个是为了方便，一个是为了对付百年或者千年问题。

32 RRRR 　如果输入参数只有两位，则同rr，否则就同yyyy作用.

33 SS 　秒（0-59），一分钟内

34 SSSSS 　一天从午夜开始的累积秒数.(0-86399)

35 TS 　返回短日期格式内容，包括时分秒等,只能和dl,ds组合使用，格式是:

dl ts或者dl ts ,中间以空格间隔开。TO_CHAR(SYSDATE,'TS')=下午 4:50:04

表现形式受NLS_TERRITORY 和NLS_LANGUAGE影响。

36 TZD 　夏令时制信息，时区简写加上夏令时信息，必须和格式tzr设置的时区对应。

包括下面三个TZ开头的，都是和时区相关，并不是直接用在to_char

37 TZH 　时区中的小时，例如hh:mi:ss.fftzh:tzm'

38 TZM 　时区中的分钟.

39 TZR 　时区中的区域信息，必须是数据库支持的时区，例如US/Pacific

40 WW 　和iw类似，也是表示星期的序号，从年的第一天算起到年的最后一个第七天。二者取值基本相同。（1-53），例如2008-01-01 到2008-01-07 算1，2008-01-09~2008-01-13 算2

41 W 　一个月中的星期序号，其算法同ww,不过是局限在一月之内而已，和iso的不同。

42 X 　代表本地根符号，没有特别用处，只能和timestamp类型一起使用.

43 Y,YYY 　四位年，用都好分隔例如2,008

44 YEAR 　发音表达的年，例如 2008=two thousand eight

SYEAR S前缀表示公元前BC

45 YYYY 　四位年，S前缀表示公元前BC

SYYYY

46 YYY 　一次表示后面3，2，1位的年，例如2008 可以分别取值为008,08,8

　总结　从以上看，主要就是表示时间几个部分的格式：世纪、年，月，日，时，分,秒，毫秒，以及其它一些混合格式。每个时间部分都可以有多种的表达方式，通过这样归类就比较容易记忆。

很多格式可以组合使用，这样最终可以形成足够丰富的表达其形势；

其次很多格式和nls是密切相关的；最后某些输出（返回）和格式大小写是有关系的，这在中文环境下体现不出来（目前来没有看到），但是english环境下就名下，以to_char(sysdate,'day')为例子，如果是西文环境是返回sun(假设sysdate位于周末），如果to_char(sysdate,'DAY')则返回SUN

【示例】

to_char(sysdate,'d') 每周第几天

to_char(sysdate,'dd') 每月第几天

to_char(sysdate,'ddd') 每年第几天

to_char(sysdate,'ww') 每年第几周

to_char(sysdate,'mm') 每年第几月

to_char(sysdate,'q') 每年第几季

to_char(sysdate,'yyyy') 年

SQL> select to_char(sysdate,' PM yyyy-mm-dd hh24:mi:sssss AD year mon day ddd iw') FROM DUAL;

TO_CHAR(SYSDATE,'PMYYYY-MM-DDH

--------------------------------------------------------------------------------

上午 2008-03-27 09:58:35917 公元 two thousand eight 3月星期四 087 13

SQL> SELECT TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF5') FROM DUAL;

TO_CHAR(SYSTIMESTAMP,'HH24:MI:

------------------------------

10:02:28.90000

SQL>SELECT TO_CHAR(SYSDATE,'DS DL') FROM DUAL

TO_CHAR(SYSDATE,'DSDL')

-----------------------------------

2008-03-27 2008年3月27日星期四

【示例】带C3示例

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

返回：monday

TO_NUMBER(X[[,c2],c3])

【功能】将字符串X转化为数字型

【参数】c2,c3,字符型，参照to_char()

【返回】数字串

【相反】 to_char(date[[,c2],c3])

【示例】

select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;

转换为16进制。

TO_CHAR(100,'XX')= 64

nls_charset_id(c1)

【功能】返回字符集名称参应id值

【参数】c1,字符型

【返回】数值型

sql> select nls_charset_id('zhs16gbk') from dual;

nls_charset_id('zhs16gbk')

--------------------------

852

nls_charset_name(n1)

【功能】返回字符集名称参应id值

【参数】n1,数值型

【返回】字符型

sql> select nls_charset_name(852) from dual;

nls_char

--------

zhs16gbk

17. 聚组函数

AVG([distinct|all]x)

【功能】统计数据表选中行x列的平均值。

【参数】all表示对所有的值求平均值,distinct只对不同的值求平均值，默认为all

如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值

【示例】

环境：

create table table3(xm varchar(8),sal number(7,2));

insert into table3 values('gao',1111.11);

insert into table3 values('zhu',5555.55);

commit;

执行统计：

select avg(distinct sal),avg(all sal),avg(sal) from table3;

结果： 3333.33 2592.59 2592.59

SUM([distinct|all]x)

【功能】统计数据表选中行x列的合计值。

【参数】all表示对所有的值求合计值,distinct只对不同的值求合计值，默认为all

如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值

【示例】

环境：

create table table3(xm varchar(8),sal number(7,2));

insert into table3 values('gao',1111.11);

insert into table3 values('zhu',5555.55);

commit;

执行统计：

select SUM(distinct sal),SUM(all sal),SUM(sal) from table3;

结果： 6666.66 7777.77 7777.77

VARIANCE([distinct|all]x)

【功能】统计数据表选中行x列的方差。

【参数】all表示对所有的值求方差,distinct只对不同的值求方差，默认为all

如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值

【示例】

环境：

create table table3(xm varchar(8),sal number(7,2));

insert into table3 values('gao',1111.11);

insert into table3 values('zhu',5555.55);

commit;

执行统计：

select VARIANCE(distinct sal),VARIANCE(all sal),VARIANCE(sal) from table3;

结果： 9876523.4568 6584348.9712 6584348.9712

count(*|[distinct|all]x)

【功能】统计数据表选中行x列的合计值。

【参数】

*表示对满足条件的所有行统计，不管其是否重复或有空值(NULL)

all表示对所有的值统计,默认为all

distinct只对不同的值统计，

如果有参数distinct或all，需有空格与x(列)隔开，均忽略空值(NULL)。

【参数】x，可为数字、字符、日期型及其它类型的字段

【返回】数字值

count(*)=sum(1)

【示例】

环境：

create table table3(xm varchar(8),sal number(7,2));

insert into table3 values('gao',1111.11);

insert into table3 values('zhu',5555.55);

insert into table3 values('',1111.11);

insert into table3 values('zhu',0);

commit;

执行统计：

select count(*),count(xm),count(all xm),count(distinct sal),count(all sal),count(sal),sum(1) from table3;

结果： 5 4 4 3 5 5 5

返回统计最大值max()；

返回统计最小值min();

MIN([distinct|all]x)

【功能】统计数据表选中行x列的最大值。

【参数】all表示对所有的值求最大值,distinct只对不同的值求最大值，默认为all

如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，可为数字、字符或日期型字段

【返回】对应x字段类型

注：字符型字段，将忽略空值(NULL)

【示例】

环境：

create table table3(xm varchar(8),sal number(7,2));

insert into table3 values('gao',1111.11);

insert into table3 values('zhu',5555.55);

insert into table3 values('',1111.11);

insert into table3 values('zhu',0);

commit;

执行统计：

select MIN(distinct sal),MIN(xm),MIN(distinct xm),MIN(all xm) from table3;

结果：0 gao gao gao

1、在数字类型数据使用AVG and SUM 函数

–select sum(sal), avg(sal), max(sal) , min(sal) from emp;

（1）MIN and MAX适用于任何数据类型

–select min(hiredate ) ,max(hiredate) from emp;

（2）组函数除了count(*)外，都跳过空值而处理非空值

–select count(*) from emp;

–select count(comm) from emp;

–select count(1) from emp;

–不能计算空值

select count(distinct deptno) from emp;

2、组函数不能处理null

select avg(comm) from emp;

（1）NVL函数迫使分组函数包括空值

select avg(nvl(comm,0)) from emp;

3、数据分组

（1）创建分组

–group by 子句

–Group by 子句可以包含任意数目的列。

（2）除组函数语句外，select语句中的每个列都必须在group by 子句中给出。

–如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。

–Group by 子句必须出现在where子句之后，order by 子句之前。

（3）过滤分组（having子句)

–Where过滤行，having过滤分组。

–Having支持所有where操作符。

（4）分组和排序

–一般在使用group by 子句时，应该也给出order by子句。

（5）使用GROUP BY子句将表分成小组

（6）结果集隐式按降序排列,如果需要改变排序方式可以使用Order by 子句

（7）出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中

select deptno,avg(sal) from emp group by deptno

（8）GROUP BY 列可以不在SELECT列表中

select avg(sal) from emp group by deptno

（9）不能在 WHERE 子句中使用组函数.不能在 WHERE 子句中限制组. 使用Having 对分组进行限制

select avg(sal) from emp group by deptno having avg(sal) > 1000;

4、select字句顺序

11、oracle

5、Sql语句执行过程：

（1）读取from子句中的基本表、视图的数据，[执行笛卡尔积操作]。

（2）选取满足where子句中给出的条件表达式的元组

（3）按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组

（4）按select子句中给出的列名或列表达式求值输出

（5）Order by子句对输出的目标表进行排序。

6、练习

--avg

select avg(e.sal) from emp e;

--sum

select sum(e.sal) from emp e;

--count

select count(*) from emp e;

select count(e.ename) from emp e;

select count(e.mgr) from emp e;

select count(e.deptno) from emp e;

--计算总数的时候是最高效的

select count(2) from emp e;

--max

select max(e.sal) from emp e;

--min

select min(e.sal) from emp e;

--求员工薪资大于平均薪资的人员

--分组函数不可以用在where条件查询中

select * from emp e where e.sal > (select avg(e.sal) from emp e);

--group by

--求每个部门的平均薪资

--只能取组函数的值以及group by表达式中的字段值

select e.deptno,avg(e.sal) from emp e group by e.deptno,e.comm;

--求部门平均薪资大于2000的部门

select e.deptno,avg(e.sal) from emp e having avg(e.sal) > 2000 group by e.deptno;

18. 其他函数

greatest(exp1,exp2,exp3,……,expn)

【功能】返回表达式列表中值最大的一个。如果表达式类型不同，会隐含转换为第一个表达式类型。

【参数】exp1……n，各类型表达式

【返回】exp1类型

【示例】

SELECT greatest(10,32,'123','2006') FROM dual;

SELECT greatest('kdnf','dfd','a','206') FROM dual;

greatest(exp1,exp2,exp3,……,expn)

【功能】返回表达式列表中值最大的一个。如果表达式类型不同，会隐含转换为第一个表达式类型。

【参数】exp1……n，各类型表达式

【返回】exp1类型

【示例】

SELECT greatest(10,32,'123','2006') FROM dual;

SELECT greatest('kdnf','dfd','a','206') FROM dual;

user

【功能】返回当前会话对应的数据库用户名。

【参数】无

【返回】字符型

uid

【功能】返回当前会话所对应的用户id号。

【参数】无

【返回】字符型

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

【功能】根据条件返回相应值

【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null

注：值1……n 不能为条件表达式,这种情况只能用case when then end解决

·含义解释：　　

　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)　　

　　该函数的含义如下：　　

　　IF 条件=值1 THEN

　　RETURN(翻译值1)

　　ELSIF 条件=值2 THEN

　　RETURN(翻译值2)

　　......

　　ELSIF 条件=值n THEN

　　RETURN(翻译值n)　　

　　ELSE

　　RETURN(缺省值)

　　END IF

或：

　　when case 条件=值1 THEN

　　RETURN(翻译值1)

　　ElseCase 条件=值2 THEN

　　RETURN(翻译值2)

　　......

　　ElseCase 条件=值n THEN

　　RETURN(翻译值n)　　

　　ELSE

　　RETURN(缺省值)

　　END

【示例】

　　·使用方法：　　

　　1、比较大小　　

　　select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

　　sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1　　

　　例如：

　　变量1=10，变量2=20

　　则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。　　

　　2、表、视图结构转化　　

　　现有一个商品销售表sale，表结构为：　　

　　month　　　 char(6)　　　　　 --月份

　　sell　　　　number(10,2)　　　--月销售金额　　

　　现有数据为：　　

　　200001　　1000

　　200002　　1100

　　200003　　1200

　　200004　　1300

　　200005　　1400

　　200006　　1500

　　200007　　1600

　　200101　　1100

　　200202　　1200

　　200301　　1300　

　　想要转化为以下结构的数据：　　

　　year　　　char(4)　　　　　 --年份

　　month1　　number(10,2)　　　--1月销售金额

　　month2　　number(10,2)　　　--2月销售金额

　　month3　　number(10,2)　　　--3月销售金额

　　month4　　number(10,2)　　　--4月销售金额

　　month5　　number(10,2)　　　--5月销售金额

　　month6　　number(10,2)　　　--6月销售金额

　　month7　　number(10,2)　　　--7月销售金额

　　month8　　number(10,2)　　　--8月销售金额

　　month9　　number(10,2)　　　--9月销售金额

　　month10　　number(10,2)　　　--10月销售金额

　　month11　　number(10,2)　　　--11月销售金额

　　month12　　number(10,2)　　　--12月销售金额

　　结构转化的SQL语句为：

　　create or replace view

　　v_sale(year,month1,month2,month3,month4,month5,month6,　　

　　month7,month8,month9,month10,month11,month12)

　　select

　　substrb(month,1,4),

　　sum(decode(substrb(month,5,2),'01',sell,0)),

　　sum(decode(substrb(month,5,2),'02',sell,0)),

　　sum(decode(substrb(month,5,2),'03',sell,0)),

　　sum(decode(substrb(month,5,2),'04',sell,0)),

　　sum(decode(substrb(month,5,2),'05',sell,0)),

　　sum(decode(substrb(month,5,2),'06',sell,0)),

　　sum(decode(substrb(month,5,2),'07',sell,0)),

　　sum(decode(substrb(month,5,2),'08',sell,0)),

　　sum(decode(substrb(month,5,2),'09',sell,0)),

　　sum(decode(substrb(month,5,2),'10',sell,0)),

　　sum(decode(substrb(month,5,2),'11',sell,0)),

　　sum(decode(substrb(month,5,2),'12',sell,0))

　　from sale

　　group by substrb(month,1,4);

COALESCE(c1, c2, ...,cn)

【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值

【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null

【返回】同参数类型

【说明】从Oracle 9i版开始，COALESCE函数在很多情况下就成为替代CASE语句的一条捷径

【示例】

select COALESCE(null,3*5,44) hz from dual; 返回15

select COALESCE(0,3*5,44) hz from dual; 返回0

select COALESCE(null,'','AAA') hz from dual; 返回AAA

select COALESCE('','AAA') hz from dual; 返回AAA

COALESCE(c1, c2, ...,cn)

【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值

【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null

【返回】同参数类型

【说明】从Oracle 9i版开始，COALESCE函数在很多情况下就成为替代CASE语句的一条捷径

【示例】

select COALESCE(null,3*5,44) hz from dual; 返回15

select COALESCE(0,3*5,44) hz from dual; 返回0

select COALESCE(null,'','AAA') hz from dual; 返回AAA

select COALESCE('','AAA') hz from dual; 返回AAA

rownum

【功能】返回当前行号

【参数】无

【返回】数值型

case [<表达式>]

when <表达式条件值1> then <满足条件时返回值1>

[when <表达式条件值2> then <满足条件时返回值2>

……

[else <不满足上述条件时返回值>]]

end

【功能】当：<表达式>＝<表达式条件值1……n> 时，返回对应 <满足条件时返回值1……n>

当<表达式条件值1……n>不为条件表达式时，与函数decode()相同，

decode(<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2> ……,<不满足上述条件时返回值>)

【参数】

<表达式> 默认为true (逻辑型)

<表达式条件值1……n> 类型要与<表达式>类型一致，

若<表达式>为字符型，则<表达式条件值1……n>也要为字符型

【注意点】

1、以CASE开头，以END结尾

2、分支中WHEN 后跟条件，THEN为显示结果

3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加

4、END 后跟别名

5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略，得注意条件先后顺序

【示例】

建立环境：

create table xqb

(xqn number(1,0));

insert into xqb xqn values(1);

insert into xqb xqn values(2);

insert into xqb xqn values(3);

insert into xqb xqn values(4);

insert into xqb xqn values(5);

insert into xqb xqn values(6);

insert into xqb xqn values(7);

commit;

查询结果：

SELECT xqn,

CASE

WHEN xqn = 1 THEN '星期一'

WHEN xqn = 2 THEN '星期二'

WHEN xqn = 3 THEN '星期三'

else '星期三以后'

END 星期

FROM xqb

另类写法

SELECT xqn,

CASE xqn

WHEN 1 THEN '星期一'

WHEN 2 THEN '星期二'

WHEN 3 THEN '星期三'

else '星期三以后'

END 星期

FROM xqb

decode正确表达：

SELECT xqn,

decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期

FROM xqb

decode错误表达：

SELECT xqn,

decode(TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后') 星期

FROM xqb

组合条件表达：

SELECT xqn,

CASE

WHEN xqn <= 1 THEN '星期一'

WHEN xqn <= 2 THEN '星期二' --条件同：not(xqn<=1) and xqn<=2

WHEN xqn <= 3 THEN '星期三' --条件同：not(xqn<=1 and xqn<=2) and xqn<=3

else '星期三以后'

END 星期

FROM xqb

Oracle包utl_inaddr

作用:用于取得局域网或Internet环境中的主机名和IP地址.

1、utl_inaddr.get_host_address 环境中IP地址

如果查询失败，则提示系统错误

查询www.qq.com的IP地址

select UTL_INADDR.get_host_address('www.qq.com') from dual;

查询本机IP地址

select UTL_INADDR.get_host_address() from dual;

查询局域网内yuechu的IP地址

select UTL_INADDR.get_host_address('yuechu') from dual;

2、UTL_INADDR.get_host_name返回环境中主机名

返回本机主机名

select UTL_INADDR.get_host_name() from dual;

返回局域网内指定IP地址的主机名

select UTL_INADDR.get_host_name('192.168.0.156') from dual;

返回intrenet中指定IP地址的网址

select UTL_INADDR.get_host_name('219.153.50.84') from dual;

19. 92和99语法

一、sql:1992语法的连接（将连接条件和过滤条件全部放到where字句中）

（1）语法规则：

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

（2）在 WHERE 子句中写入连接条件

（3）当多个表中有重名列时，必须在列的名字前加上表名作为前缀

（4）连接的类型：

等值连接 -- Equijoin

非等值连接 -- Non-equijoin

外连接 -- Outer join

自连接 -- Self join

2、92语法

（1）数据来自于多张表 ,92表连接

注意: 明确引用同名的列，必须使用表名或者别名区分

（2）一、迪卡尔积：行数等于表的行数相乘，列数等于表的列数想加

select 字段列表 from 表1,表2,表3....

（3）二、等值连接：取关系列相同的记录

select 字段列表 from 表1,表2,表3....

where 表1.列=表2.列 and 表1.列=表3.列

（4）三、非等值连接：取关系列不同的记录 != > < >= <= between and

select 字段列表 from 表1,表2,表3.... where 表1.列!=表2.列 and 表1.列!=表3.列

（5）四、自连接:(特殊的等值连接) 列来自于同一张表,不同角度看待表

select 字段列表 from 表1 e,表1 m where e.列1=m.列2

（6）五、外连接: 在等值基础上，确保一张表(主表)的记录都存在从表满足则匹配，不满足补充null

3、等值连接

（1）语法规则：

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

（2）主外键

在外键表中的映射字段称为外键 Foreign key

在主键表中的唯一字段称为主键 Primary key

4、非等值连接

（1）<,>,<=,>=,!=连接时称非等值连接

select * from emp,salgrade where sal between losal and hisal

（2）外连接运算符是 (+)

笛卡尔积

select count(*) from emp

select count(*) from dept

select emp.empno,dept.loc from emp,dept

（3）检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

（4）检索出的列的数目将是第一个表中的列数加上第二个表中的列数

（5）应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据

5、连接中使用表名

使用表的别名简化了查询

select e.empno,e.ename,e.deptno,d.deptno,d.loc from emp e,dept d where e.deptno=d.deptno

为了连接n个表，至少需要n-1个连接条件。

6、外连接

（1）为了在操作时能保持这些将被舍弃的元组，提出了外连接的概念，使用外连接可以看到不满足连接条件的记录

–外连接运算符是 (+)

–有左外连接和右外连接

（2）左外连接显示左边表的全部行

–SELECT table.column, table.column FROM table1, table2 WHERE table1.column = table2.column(+);

（3）右外连接显示右边表的全部行

–SELECT table.column, table.column FROM table1, table2 WHERE table1.column(+) = table2.column

1、左外: 主表在左边，+号在右边

2、右外: 主表在右边，+号在左边

记住：+号位置与它们的名字相反

7、自连接

（1）查找每个员工的上级主管

select worker.ename||’ works for ‘||manager.ename from emp worker,emp manager where worker.mgr=manager.empno

二、sql:1999语法的连接（将连接条件使用join on的方式进行表连接）

（1）sql1992的语法规则暴露了这样的缺点：语句过滤条件和表连接的条件都放到了where子句中。当条件过多时，联结条件多，过滤条件多时，就容易造成混淆。

（2）SQL1999修正了整个缺点，把联结条件，过滤条件分开来，包括以下新的TABLE JOIN的句法结构：

–CROSS JOIN 相当于笛卡尔积

–NATURAL JOIN 自然连接，将两个表连接起来通过一个共有的字段，但是不需要指明是哪个字段

–USING子句必须要将连接的字段在using中指明，并且访问的时候不能加表名

–ON子句

–LEFT OUTER JOIN

–RIGHT OUTER JOIN

–FULL OUTER JOIN 将两个表的所有数据都显示出来

–Inner outer join 相当于自连接

9、交叉连接

（1）CROSS JOIN产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个WHERE子句一样

select emp.empno,emp.ename,emp.deptno,dept.loc from emp ，dept;

（2）可以使用CROSS JOIN 来达到相同的结果

select emp.empno, emp.ename, emp.deptno, dept.loc from emp cross join dept;

10、自然连接

（1）NATURAL JOIN子句基于两个表中列名完全相同的列产生连接

–两个表有相同名字的列

–数据类型相同

–从两个表中选出连接列的值相等的所有行

select * from emp natural join dept Where deptno = 10;

（2）自然连接的结果不保留重复的属性

11、using创建连接

（1）using子句引用的列在sql任何地方不能使用表名或者别名做前缀, 同样适合natural子句

select e.ename,e.ename,e.sal,deptno,d.loc from emp e join dept d using(deptno) where deptno=20

12、使用on创建连接

（1）自然连接的条件是基于表中所有同名列的等值连接，为了设置任意的连接条件或者指定连接的列，需要使用ON子句，连接条件与其它的查询条件分开书写，使用ON子句使查询语句更容易理解

select ename,dname from emp join dept on emp.deptno=dept.deptno where emp.deptno=30;

（2）检索雇员名字、所在单位、薪水等级：这三个信息在三个表里面，所以只能用多表联结

select ename,dname,grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;

13、左外连接

（1）在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值。

select e.ename,d.deptno,d.dname from dept d left outer join emp e on e.deptno=d.deptno

select e.ename,d.deptno,d.dname from emp e,dept d where d.deptno=e.deptno(+);

14、右外连接

（1）RIGHT OUTER JOIN中会返回所有右边表中的行，即使在左边的表中没有可对应的列值。

select e.ename,d.deptno,d.dname from emp e right outer join dept d on e.deptno=d.deptno （99）

select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno; （92）

--inner join 默认内连接

--on 连接表的条件

select * from emp e inner join dept d on e.deptno=d.deptno

select * from emp e join dept d on e.deptno=d.deptno

select * from emp e join dept d using(deptno)

15、子查询

（1）SQL允许多层嵌套。子查询，即嵌套在其他查询中的查询。

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

（2）理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。

–子查询要用括号括起来

–将子查询放在比较运算符的右边(增强可读性)

16、子查询种类

（1）按照子查询返回的记录数，子查询可以分为单行子查询和多行子查询

11、oracle

17、单行子查询

（1）子查询返回一行记录使用单行记录比较运算符

11、oracle

（2）我们要查询有哪些人的薪水是在整个雇员的平均薪水之上的：

a.首先求所有雇员的平均薪水

select avg(sal+nvl(comm,0)) from emp

b.然后求：

select ename,empno, sal, sal+nvl(comm,0) from emp where sal+nvl(comm,0)

>(select avg(sal+nvl(comm,0)) from emp);

此处嵌套的子查询在外层查询处理之前执行

18、多行子查询

子查询返回多行行记录使用集合比较运算符

11、oracle

（1）在多行子查询中使用in

我们要查在雇员中有哪些人是经理人，也就是说，有哪些人的empno号在mgr这个字段中出现过，这个时候，应当首先查询mgr中有哪些号码，然后再看看有哪些人的雇员号码在此出现：

select empno, ename from emp where empno in ( select distinct mgr from emp );

（2）在多行子查询中使用all找出部门编号为20的所有员工中收入最高的职员

select * from emp where sal >= all( select sal from emp where deptno = 20) and deptno = 20

（3）在From子句中使用子查询

我们要求每个部门平均薪水的等级，可以这样考虑，首先将每个部门的平均薪水求出来，然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级。

a.先求出每个部门平均薪水的表t。

b.将t和salgrade进行关联查询就可以了。

select * from salgrade s, (select deptno,avg(sal) avg_sal from emp group by deptno) t where t.avg_sal between s.losal and s.hisal;

（4）rownum < 6 可以判断选择多少行

decode(条件，值)

20. sql数据更新

1、Sql的数据更新包括数据插入、删除和修改3个操作.

（1）往表中插入数据的语句是insert语句，方式有两种，一种是元组值的插入，一种是查询结果的插入

（2）元组值的插入语法如下：

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

（3）一次插入操作只插入一行

insert语句

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1111,'gao','clerk',7902,sysdate,10000,3000,40)

（4）此处插入的元组中列的个数、顺序与emp的结构完全一致，因此表名之后的列名可以省略不写

insert into emp values(2222,'gaohs','clerk',7902,sysdate,10000,3000,40)

（5）可以只插入部分列

insert into emp(empno,ename) values (3333,'xiaozhang')

2、但要求省略的列必须满足下面的条件：

（1）该列定义为允许Null值。

（2）在表定义中给出默认值，这表示如果不给出值，将使用默认值。

（3）如果不符合上面两个条件，将会报错。不能成功插入。

3、可以用insert语句把一个select语句的查询结果插入到一个基本表中，语法如下：

Insert into tablename(column,..) select * from tablename2

（1）创建一个临时表

create table temp as select * from emp where 1 = 2

（2）执行插入

insert into ss select * from emp;

4、delete语句

（1）SQL的删除操作是指从基本表中删除元组，语法如下：

DELETE [FROM] table [WHERE condition];

（2）其语义是从基本表中删除满足条件表达式的元组

（3）Delete from table 表示从表中删除一切元组

（4）如果想从表中删除所有的行，不要使用delete，可使用truncate table 语句，完成相同的工作，但是速度更快（没有事务）。

5、Update语句

（1）Update语句用于修改基本表中元组的某些列，其语法如下：

UPDATE table SET column = value [, column = value] … [WHERE condition];

（2）其语义是：修改基本表中满足条件表达式的那些元组的列值，需修改的列值在set子句中指出。

6、练习1

21. 事务处理

1、事务（Transaction）是一个操作序列。

这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。

2、事务是为了保证数据库的完整性

3、事务不能嵌套

4、在oracle中，没有事务开始的语句。一个Transaction起始于一条DML(Insert、Update和Delete )语句，结束于以下的几种情况：

（1）用户显式执行Commit语句提交操作或Rollback语句回退。

（2）当执行DDL(Create、Alter、Drop)语句事务自动提交。

（3）用户正常断开连接时，Transaction自动提交。

（4）系统崩溃或断电时事务自动回退。

5、DDL语句执行自动提交事务

（1）insert into test02

（2）select * from emp where emp.deptno=10;

（3）create table test04 as

（4）select * from emp where 1=2

6、Commit &Rollback

（1）Commit表示事务成功地结束，此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都以交付实施。每个Commit语句都可以看成是一个事务成功的结束，同时也是另一个事务的开始。

（2）Rollback表示事务不成功的结束，此时告诉系统，已发生错误，数据库可能处在不正确的状态，该事务对数据库的更新必须被撤销，数据库应恢复该事务到初始状态。每个Rollback语句同时也是另一个事务的开始。

（3）一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB），以后就不能用rollback进行撤销。

（4）执行一个 DDL ，dcl语句或从 SQL*Plus正常退出，都会自动执行commit命令。

savepoint test01

rollback to test01

7、事务四大特征：原子性，一致性，隔离性和持久性。

（1）原子性（Atomicity）

一个原子事务要么完整执行，要么干脆不执行。这意味着，工作单元中的每项任务都必须正确执行。如果有任一任务执行失败，则整个工作单元或事务就会被终止。即此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的。

（2）一致性（Consistency）

一致性代表了底层数据存储的完整性。它必须由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(即，数据预期所表达的现实业务情况不相一致)。例如，在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。支付宝账号100 你读到余额要取，有人向你转100 但是事物没提交（这时候你读到的余额应该是100，而不是200）这种就是一致性

（3）隔离性（Isolation）

隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响。

（4）持久性（Durability）

持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证，所作的修改在任何系统瘫痪时不至于丢失。

8、提交或者回滚前数据的状态

–以前的数据可恢复

–当前的用户可以看到DML操作的结果

–其他用户不能看到DML操作的结果

–被操作的数据被锁住,其他用户不能修改这些数据

–数据的修改被永久写在数据库中.

–数据以前的状态永久性丢失.

–所有的用户都能看到操作后的结果.

–记录锁被释放,其他用户可操作这些记录.

9、语句将放弃所有的数据修改

–修改的数据被回退.

–恢复数据以前的状态.

–行级锁被释放.

22. 约束

1、约束constraint

（1）当我们创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则.

2、Oracle 支持下面五类完整性约束:

（1）NOT NULL 非空

（2）UNIQUE Key 唯一键

（3）PRIMARY KEY 主键

（4）FOREIGN KEY 外键

（5）CHECK 自定义检查约束

3、Oracle使用SYS_Cn格式命名约束，也可以由用户命名

4、创建约束的时机

–在建表的同时创建

–建表后创建

5、约束从作用上分类，可以分成两大类：

–表级约束：可以约束表中的任意一列或多列。可以定义除了Not Null以外的任何约束。

–列级约束：只能约束其所在的某一列。可以定义任何约束。

6、约束用于确保数据库数据满足特定的商业逻辑或者企业规则，如果定义了约束，并且数据不符

7、合约束，那么DML操作（INSERT、UPDATE、DELETE）将不能成功执行。约束包括NOT NULL、UNIQUE、PRIMARY KEY、FOREING KEY 以及CHECK等五种类型

8、定义约束

（1）列级约束：

column [CONSTRAINT constraint_name] constraint_type

（2）表级约束：

column ,..., [CONSTRAINT constraint_name] constraint_type (column,...)

（3）定义NOT NULL约束

NOT NULL 约束只能在列级定义，不能在表级定义

CREATE TABLE emp01( eno INT NOT NULL, name VARCHAR2(10) CONSTRAINT nn_name2 NOT NULL, salary NUMBER(6,2));

（4）列级约束: 从形式上看，在每列定义完后马上定义的约束，在逗号之前就定义好了。

carete table parent(c1 number primary key );

create table child (c number primary key , c2 number references parent(c1));

（5）表级约束: 从形式上可以看出与列级约束的区别了吧。

create table child( c number , c2 number , primary key (c2), foreign key(c2) references parent(c1));

（6）有些时候，列级约束无法实现某种约束的定义，比如联合主键的定义，就要用到表级约束:

create table test(id1 number , id2 number, primary key(id1, id2));

9、主键约束是数据库中最重要的一种约束。在关系中，主键值不可为空，也不允许出现重复，即关系要满足实体完整性规则。

–主键从功能上看相当于非空且唯一

–一个表中只允许一个主键

–主键是表中能够唯一确定一个行数据的字段

–主键字段可以是单字段或者是多字段的组合

–Oracle为主键创建对应的唯一性索引

10、建议命名

（1）约束_表名_字段可以保证唯一性。如果太长，可用缩写。

（2）同一字段可以有多个约束，但是约束之间不要冲突

11、主键可用下列两种形式之一定义

（1）主键子句

在表的定义中加上如下子句 primary key(列)

（2）主键短语

在主属性的定义之后加上primary key字样。

（3）上述形式Oracle会自动命名约束，可自己给约束起名

create table t3( id number(4), constraint t3_pk primary key(id) )

12、非空约束（not null）

（1）确保字段值不允许为空，只能在字段级定义

CREATE TABLE employees( employee_id NUMBER(6), name VARCHAR2(25) NOT NULL, salary NUMBER(8,2),

hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL )

13、唯一约束（unique）

唯一性约束条件确保所在的字段或者字段组合不出现重复值

唯一性约束条件的字段允许出现空值

14、Oracle将为唯一性约束条件创建对应的唯一性索引

CREATE TABLE employees( id NUMBER(6), name VARCHAR2(25) NOT NULL UNIQUE, email VARCHAR2(25), salary NUMBER(8,2), hire_date DATE NOT NULL, CONSTRAINT emp_email_uk UNIQUE(email) );

15、check约束

（1）Check约束用于对一个属性的值加以限制，在check中定义检查的条件表达式，数据需要符合设置的条件

create table emp3 ( id number(4) primary key, age number(2) check(age > 0 and age < 100), salary number(7,2), sex char(1), constraint salary_check check(salary > 0) )

（2）在这种约束下，插入记录或修改记录时，系统要测试新的记录的值是否满足条件

16、关系模型

为了维护数据库中的数据与现实世界的一致性，关系数据库的数据与更新操作必须遵循下列三类完整性规则：

（1）实体完整性规则

这条规则要求关系中在组成主键的属性上不能有空值。

（2）参照完整性规则

这条规则要求“不引用不存在的实体”。例如：deptno是dept表的主键，而相应的属性也在表emp中出现，此时deptno是表emp的外键。在emp表中，deptno的取值要么为空，要么等于dept中的某个主键值。

17、用户定义的完整性规则

用户定义的完整性规则反应了某一具体的应用涉及的数据必须满足的语义要求。

18、外键约束（foreign key）

（1）外键是表中的一个列，其值必须在另一表的主键或者唯一键中列出

（2）作为主键的表称为“主表”，作为外键的关系称为“依赖表”

（3）外键参照的是主表的主键或者唯一键

（4）对于主表的删除和修改主键值的操作，会对依赖关系产生影响，以删除为例：当要删除主表的某个记录（即删除一个主键值，那么对依赖的影响可采取下列3种做法：

1.RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。

2.CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除

3.SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值

FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) [ON DELETE [CASCADE|SET NULL]] 如省略on短语，缺省为第一中处理方式。

19、约束的添加撤销

（1）可增加或删除约束，但不能直接修改

alter table tablename

（2）增加

add constraint con_name unique(col)

（3）删除

drop constraint com_name [cascade]

23. 索引

1、索引是为了加快对数据的搜索速度而设立的。索引是方案（schema）中的一个数据库对象,与表独立存放.

（1）索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O

（2）Sql中的索引是非显示索引，也就是在索引创建以后，在用户撤销它之前不会在用到该索引的名字，但是索引在用户查询时会自动起作用。

2、索引的创建有两种情况

（1）自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引.

（2）手动: 用户可以创建索引以加速查询

3、开发中使用索引的要点：

–1. 索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。

–2. 索引数据可能要占用大量的存储空间。

–3. 并非所有的数据都适合于索引。唯一性不好的数据（如省）从索引的到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多。

–4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选。

–5. 可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序，则这种4、索引没有用处。

（1）在一列或者多列上创建索引.

CREATE INDEX index ON table (column[, column]...);

（2）下面的索引将会提高对EMP表基于 ENAME 字段的查询速度.

CREATE INDEX emp_last_name_idx ON emp (ename)

（3）通过DROP INDEX 命令删掉一个索引.

–DROP INDEX index;

（4）删掉 UPPER_LAST_NAME_IDX 索引.

–DROP INDEX upper_last_name_idx;

24. oracle分页技术

1、Oracle分页:

（1）Oracle下select语句每个结果集中都有一个伪字段存在，这个字段的名字叫做rownum.用来标识每条记录的行号，行号从1开始，每次递增1

（2）只能使用：<,<=

（3）注意：Oracle中的rownum的是在取数据的时候产生的序号。当rownum和order by一起使用时，会首先选出符合rownum条件的记录，然后再进行排序，这会给我们的查询带来难度。

2、使用Top-N分析法可以得到某列上最什么的前n个值. 例如:薪水最高的前5个员工?

（1）最大的几个值和最小的几个值都可以通过Top-N 分析的方法得到.

（2）Top-N 分析的语法

SELECT [column_list], ROWNUM

FROM (SELECT [column_list]

FROM table

ORDER BY Top-N_column)

WHERE ROWNUM <= N;

3、分页

select r,ename,sal from (select rownum r,ename,sal from (select * from emp order by sal desc) ) where r>5 and r <=10;

4、rowid

（1）rowid 是oracle实际存在的值，是唯一的值

（2）rownum 是一个虚拟的顺序值，前提是一定要排序

select emp.*,rowid from emp;

delete from emp e where rowid not in(select min(rowid) from emp group by ename)

5、删除重复数据

（1）如何只显示重复数据，或不显示重复数据

（2）显示重复：select * from tablename group by id having count(*)>1

（3）不显示重复：select * from tablename group by id having count(*)=1

（4）删除重复数据原型：

delete from temp where rowid not in (

select min(rowid) from emp group by ename having count(*) >= 1)

25. 三范式

第一范式：列不可分

第二范式：不能存在部分依赖

第三范式：传递依赖

26. oracle常用命令

命令		描述
DESC 表名		查看表的信息.
	SET SERVEROUT [ON\|OFF]		设置系统输出的状态.
	SET PAGESIZE <大小>		设置浏览中没页的大小
	SET LINESIZE <大小>		设置浏览中每行的长度
	SET AUTOPRINT [ON\|OFF]		设置是否自动打印全局变量的值
	SELECT SYSDATE FROM DUAL		查看当前系统时间
	ALTER SESSION SET nls_date_format='格式'		设置当前会话的日期格式示例:ALTER SESSION SET nls_date_format='dd-mon-yy hh24:mi:ss'
	SELECT * FROM TAB		查看当前用户下的所有表
SHOW USER		显示当前用户
HELP TOPIC		显示有那些命令
	SAVE <file_name>		将buf中的内容保存成一个文件
	RUN <file_name>		执行已经保存的文件;也可以写成@<file_name>
	GET <file_name>		显示文件中的内容
	LIST		显示buf中的内容
	ED		用记事本打开buf,可以进行修改
	DEL 行数		删除buf中的单行
	DEL 开始行结束行		删除buf中的多行
	INPUT 字符串		向buf中插入一行
	APPEND 字符串		将字符串追加到当前行
	C/以前的字符串/替换的字符串		修改buf中当前行的内容
	CONNECT		连接
	DISCONNECT		断开连接
	QUIT		退出sql*plus
	EXP		导出数据库（可以在DOS键入exp help=y 可以看到详细说明) 示例: exp scott/tiger full=y file=e:\a.dmp; --导出scott下的所有东西 exp scott/tiger tables=(emp,dept) file=e:\emp.dmp --导出scott下的emp,dept表
	IMP		导入数据库（可以在DOS键入imp help=y 可以看到详细说明) imp scott/tiger tables=(emp,dept) file=e:\emp.dmp

可以通过help <命令>获得命令的帮助

27. 重要练习

--99语法和92语法

--查询所有的员工信息以及他的部门信息

--1、查询所有员工的信息

select * from emp e ;

--2、查询部门信息p0

select * from dept d;

--3、查询所有信息

select * from emp e,dept d where e.deptno = d.deptno;

--查询所有员工信息以及部门信息和薪水等级

select * from emp e,dept d,salgrade sg where e.deptno = d.deptno and e.sal between sg.losal and sg.hisal;

--sql语法：92语法，99语法

--笛卡尔积：第一张表的每一条数据都要和第二张表的每条记录都进行连接

select * from emp e,dept d,salgrade sg;

--等值连接

select * from emp e,dept d where e.deptno = d.deptno;

--非等值连接

select deptno from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;

--查询每位员工的信息以及其经理的信息

--自连接

select * from emp e,emp manager where e.mgr = manager.empno;

--查询所有员工姓名和部门名称

--外连接

--左外连接

select * from emp e,dept d where e.deptno = d.deptno(+);

--查询所有部门信息及其员工的信息

--右外连接

select * from emp e,dept d where e.deptno(+) = d.deptno;

--注意：左外连接(+)在等号的右边，右外连接(+)在等号的左边

--查询薪水大于2000的员工信息和部门信息

select * from emp e,dept d where e.sal>2000 and e.deptno = d.deptno;

--92语法中，过滤条件和连接条件全部放到where语句中，显得杂乱无章，产生99语法

--99语法

--cross join 相当于92语法的笛卡尔积

select * from emp e cross join dept d;

--natural join 相当于92语法的等值连接但是不包含重复的列，自己匹配连接条件，不需要制定连接的字段

select * from emp e natural join dept d;

--using 相当于92语法的等值连接但是连接的字段需要使用using制定

--字段必须是两个表共有的，连接的列在查询的时候不能制定别名，不能有限定

select deptno from emp e join dept d using(deptno);

--on 完整的等于92语法的等值连接

select * from emp e join dept d on e.deptno = d.deptno;

--左外连接

select * from emp e left outer join dept d on e.deptno = d.deptno;

--右外连接

select * from emp e right outer join dept d on e.deptno = d.deptno;

--全连接

select * from emp e full join dept d on e.deptno = d.deptno;

--内连接

select * from emp e inner join dept d on e.deptno = d.deptno;

-- (1)查询DEPT表显示所有部门名称.

select d.dname from dept d;

-- (2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为"年收入"。（NVL(comm,0) comm取空值时用0替代）

select e.ename, (e.sal+nvl(e.comm,0))*12 年收入 from emp e;

-- (3)查询显示不存在雇员的所有部门号。 1、先查出拥有雇员的部门号，2、从所有部门号中排除

select d.deptno from dept d where d.deptno not in(select distinct e.deptno from emp e);

-- (1)查询EMP表显示工资超过2850的雇员姓名和工资。

select e.ename,e.sal from emp e where e.sal > 2850;

-- (2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。

select e.ename,e.sal from emp e where e.sal<1500 or e.sal>2850;

-- (3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。

select e.ename,e.deptno from emp e where e.empno = 7566;

-- (4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。

select e.ename,e.sal from emp e where (e.deptno = 10 or e.deptno = 30) and e.sal >1500;

-- (5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。

select e.ename,e.sal from emp e where e.ename like '_A%';

-- (6)查询EMP表显示补助非空的所有雇员名及其补助。

select e.ename,e.comm from emp e where e.comm is not null;

-- (1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序。

select e.ename,e.sal,e.hiredate from emp e order by e.ename;

-- (2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序。

select e.ename,e.job,e.hiredate from emp e where e.hiredate> '01-2月-1981' and e.hiredate < '01-5月-1981' order by e.hiredate;

-- (3)查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序。

select e.ename,e.sal,e.comm from emp e where e.comm is not null order by e.sal asc,e.comm desc;

--1、求平均薪水最高的部门的部门编号

--1、求每个部门平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

--2、求平均薪水最高

select max(t.v_sal) from (select e.deptno,avg(e.sal) v_sal from emp e group by e.deptno) t;

--3、求部门编号

select t.deptno

from (select e.deptno, avg(e.sal) v_sal from emp e group by e.deptno) t

where t.v_sal = (select max(t.v_sal)

from (select e.deptno, avg(e.sal) v_sal

from emp e

group by e.deptno) t);

--2、求部门平均薪水的等级（含义一个部门每个人薪水相加后平均的值的等级--平均薪水后的等级）

--1、部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

--2、求等级

select t.deptno, t.v_sal, sg.grade

from (select e.deptno, avg(e.sal) v_sal from emp e group by e.deptno) t

join salgrade sg

on t.v_sal between sg.losal and sg.hisal;

--3、求部门平均的薪水等级（含义一个部门每个人薪水等级相加后平均的值--薪水等级后的平均）

--1、求每个人的薪水等级

select e.deptno,sg.grade from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;

--2、部门的平均等级

select t.deptno, avg(t.grade)

from (select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal) t

group by t.deptno;

--4、求薪水最高的前5名雇员

--1、先将表按照薪水排序

select * from

(select * from emp e order by e.sal desc) where rownum < 6;

--5、求薪水最高的第6到10名雇员

--建议使用

select * from

(select t.*,rownum rw from

(select * from emp e order by e.sal desc) t where rownum < 11) tt where tt.rw >5 ;

select * from

(select t.*,rownum rw from

(select * from emp e order by e.sal desc) t) tt where tt.rw >5 and tt.rw<11 ;

--1. 求部门中薪水最高的人

--1、按照部门分组

select e.deptno,max(e.sal) from emp e group by e.deptno;

--2. 求部门平均薪水的等级

--3. 求部门平均的薪水等级

--4. 雇员中有哪些人是经理人

select *

from emp e

where e.empno in

(select distinct e.mgr from emp e where e.mgr is not null);

--5. 不准用组函数，求薪水的最高值

--第一种

select e.ename,e.deptno,e.sal from emp e where e.sal =

(select max(e.sal) from emp e);

--第二种

select e.ename ,e.deptno,e.sal from emp e where e.sal >= all( select e.sal from emp e)

--第三种

select t.ename,t.deptno,t.sal from

(select * from emp e order by e.sal desc) t where rownum < 2;

--6. 求平均薪水最高的部门的部门编号

--1、部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

--2、求最高的薪水

select max(t.v_sal) from

(select e.deptno,avg(e.sal) v_sal from emp e group by e.deptno) t;

--3、取部门号

select t.deptno from

(select e.deptno,avg(e.sal) v_sal from emp e group by e.deptno) t where t.v_sal = (select max(t.v_sal) from

(select e.deptno,avg(e.sal) v_sal from emp e group by e.deptno) t);

组函数嵌套写法(对多可以嵌套一次，group by 只对内层函数有效)

7. 求平均薪水最高的部门的部门名称

select d.dname

from (select e.deptno, avg(e.sal) v_sal from emp e group by e.deptno) t

join dept d

on t.deptno = d.deptno

where t.v_sal = (select max(t.v_sal)

from (select e.deptno, avg(e.sal) v_sal

from emp e

group by e.deptno) t);

8. 求平均薪水的等级最低的部门的部门名称

--1、部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

--2、求等级

select sg.grade,t.deptno from (select e.deptno,avg(e.sal) v_sal from emp e group by e.deptno) t join salgrade sg on t.v_sal between sg.losal and sg.hisal

--3、求最低等级

select min(tt.grade) from

(select sg.grade,t.deptno from (select e.deptno,avg(e.sal) v_sal from emp e group by e.deptno) t join salgrade sg on t.v_sal between sg.losal and sg.hisal

) tt

--4、求部门名称

select d.dname

from (select sg.grade, t.deptno

from (select e.deptno, avg(e.sal) v_sal

from emp e

group by e.deptno) t

join salgrade sg

on t.v_sal between sg.losal and sg.hisal) t01

join dept d

on t01.deptno = d.deptno

where t01.grade =

(select min(tt.grade)

from (select sg.grade, t.deptno

from (select e.deptno, avg(e.sal) v_sal

from emp e

group by e.deptno) t

join salgrade sg

on t.v_sal between sg.losal and sg.hisal) tt);

9. 求部门经理人中平均薪水最低的部门名称

--1、求部门经理

select distinct e.mgr from emp e where e.mgr is not null;

--2、求部门经理的信息

select * from emp e where e.empno in (select distinct e.mgr from emp e where e.mgr is not null);

--3、求平均薪水

select avg(t.sal) v_sal, t.deptno

from (select *

from emp e

where e.empno in

(select distinct e.mgr from emp e where e.mgr is not null)) t

group by t.deptno

--4、求最低的平均薪水

select min(t01.v_sal) from ( select avg(t.sal) v_sal, t.deptno

from (select *

from emp e

where e.empno in

(select distinct e.mgr from emp e where e.mgr is not null)) t

group by t.deptno) t01

--5、求部门名称

select d.dname

from (select avg(t.sal) v_sal, t.deptno

from (select *

from emp e

where e.empno in (select distinct e.mgr

from emp e

where e.mgr is not null)) t

group by t.deptno) t02

join dept d

on t02.deptno = d.deptno

where t02.v_sal = (select min(t01.v_sal)

from (select avg(t.sal) v_sal, t.deptno

from (select *

from emp e

where e.empno in

(select distinct e.mgr

from emp e

where e.mgr is not null)) t

group by t.deptno) t01);

10. 求比普通员工的最高薪水还要高的经理人名称(not in)

--1、求经理人

select distinct e.mgr from emp e where e.mgr is not null;

--2、求普通员工

select * from emp e where e.empno not in(select distinct e.mgr from emp e where e.mgr is not null) and e.mgr is not null;

--3、最高薪水

select max(t.sal) from ( select * from emp e where e.empno not in(select distinct e.mgr from emp e where e.mgr is not null) and e.mgr is not null) t

--4、求经理人的其他信息

select * from emp e where e.empno in( select distinct e.mgr from emp e where e.mgr is not null);

--5、大于员工最高薪水

select t.ename

from (select *

from emp e

where e.empno in

(select distinct e.mgr from emp e where e.mgr is not null)) t

where t.sal > (select max(t.sal)

from (select *

from emp e

where e.empno not in

(select distinct e.mgr

from emp e

where e.mgr is not null)

and e.mgr is not null) t);

11. 求薪水最高的前5名雇员

12. 求薪水最高的第6到第10名雇员

13. 求最后入职的5名员工

--求部门下雇员的工资>2000 人数

select count(*) from emp e where e.sal>2000 group by e.deptno;

--部门里面工龄最小和最大的人找出来

--1、每个部门工龄最小的人的入职时间

select e.deptno, max(e.hiredate) from emp e group by e.deptno;

--2、每个部门工龄最大的人的入职时间

select e.deptno, min(e.hiredate) from emp e group by e.deptno;

--3、求其他信息

select *

from emp e,(select e.deptno,max(e.hiredate) maxh

from emp e

group by e.deptno

union

select e.deptno,min(e.hiredate) minh

from emp e

group by e.deptno) t

where e.hiredate = t.maxh and e.deptno = t.deptno ;

select e1.deptno, e1.ename, e1.hiredate

from emp e1,

(select min(e.hiredate) mind, max(e.hiredate) maxd, e.deptno

from emp e

group by e.deptno) mm2

where (e1.hiredate = mm2.mind

or e1.hiredate = mm2.maxd )and e1.deptno = mm2.deptno;

--1、查询10号部门中编号最新入职的员工，工龄最长的员工的个人信息。

--2、从“software”找到‘f’的位置，用‘*’左或右填充到15位，去除其中的‘a’。

select instr('software','f'),lpad('software',15,'*') ,rpad('software',15,'*'),replace('software','a','') from dual;

--3、查询员工的奖金，如果奖金不为NULL显示‘有奖金’，为null则显示无奖金

select decode(nvl(e.comm,0),0,'无奖金','有奖金') from emp e;

--4、写一个查询显示当前日期，列标题显示为Date。再显示六个月后的日期，下一个星期日的日期，该月最后一天的日期。

select sysdate "Date",add_months(sysdate,6),next_day(sysdate,'星期日'),last_day(sysdate) from dual;

--5、查询EMP表按管理者编号升序排列，如果管理者编号为空则把为空的在最前显示

select * from emp e order by e.mgr nulls first;

--6、求部门平均薪水

--7、按部门求出工资大于1300人员的部门编号、平均工资、最小佣金、最大佣金,并且最大佣金大于100

select e.deptno,avg(e.sal),min(e.comm),max(e.comm) from emp e where e.sal>1300 and e.comm >100 group by e.deptno;

--8、找出每个部门的平均、最小、最大薪水

select avg(e.sal),min(e.sal),max(e.sal) from emp e group by e.deptno

--9、查询出雇员名，雇员所在部门名称，工资等级。

select e.ename, d.dname, sg.grade

from emp e

join dept d

on e.deptno = d.deptno

join salgrade sg

on e.sal between sg.losal and sg.hisal;

select * from emp e,dept d where e.deptno = d.deptno(+);

select * from student_score;

--行转列

姓名语文数学英语

--1、

select ss.name,sum(case ss.subject when '语文' then ss.score end ) 语文,

sum(case ss.subject when '数学' then ss.score end ) 数学,

sum(case ss.subject when '英语' then ss.score end )英语

from student_score ss group by ss.name;

--2、

select ss.name,

sum(decode(ss.subject, '语文', ss.score, 0)) 语文,

sum(decode(ss.subject, '数学', ss.score, 0)) 数学,

sum(decode(ss.subject, '英语', ss.score, 0)) 英语

from student_score ss group by ss.name;

--3、不使用case when ,decode

select distinct ss.name, ss01.score 语文, ss02.score 数学,ss03.score 英语

from student_score ss

join (select ss.name, ss.score

from student_score ss

where ss.subject = '语文') ss01

on ss.name = ss01.name

join (select ss.name, ss.score

from student_score ss

where ss.subject = '数学') ss02

on ss.name = ss02.name

join (select ss.name, ss.score

from student_score ss

where ss.subject = '英语') ss03

on ss.name = ss03.name;

select ss.name, avg(ss01.score) 语文, min(ss02.score) 数学,max(ss03.score) 英语

from student_score ss

join (select ss.name, ss.score

from student_score ss

where ss.subject = '语文') ss01

on ss.name = ss01.name

join (select ss.name, ss.score

from student_score ss

where ss.subject = '数学') ss02

on ss.name = ss02.name

join (select ss.name, ss.score

from student_score ss

where ss.subject = '英语') ss03

on ss.name = ss03.name group by ss.name;

--经典面试题1

select * from test;

select max(decode(t.type, 1, t.value)) 姓名,

min(decode(t.type, 2, t.value)) 性别,

min(decode(t.type, 3, t.value)) 年龄

from test t

group by t.t_id;

--经典面试题2

select * from tmp;

select t.rq,

count(decode(t.Shengfu, '胜', 1)) 胜,

count(decode(t.shengfu, '负', 1)) 负

from tmp t

group by t.rq;

--经典面试题4.2'

select t.name,

case

when t.语文 > 80 then

'优秀'

when t.语文 > 60 then

'及格'

else

'不及格'

end 语文,

case

when t.数学 > 80 then

'优秀'

when t.数学 > 60 then

'及格'

else

'不及格'

end 数学,

case

when t.英语 > 80 then

'优秀'

when t.英语 > 60 then

'及格'

else

'不及格'

end 英语

from (select ss.name,

avg(ss01.score) 语文,

min(ss02.score) 数学,

max(ss03.score) 英语

from student_score ss

join (select ss.name, ss.score

from student_score ss

where ss.subject = '语文') ss01

on ss.name = ss01.name

join (select ss.name, ss.score

from student_score ss

where ss.subject = '数学') ss02

on ss.name = ss02.name

join (select ss.name, ss.score

from student_score ss

where ss.subject = '英语') ss03

on ss.name = ss03.name

group by ss.name) t;

--经典面试题5

select * from yj01;

select t.deptno,

sum(decode(t.month, '一月份', t.yj)) 一月份,

sum(decode(t.month, '二月份', t.yj)) 二月份,

sum(decode(t.month, '三月份', t.yj)) 三月份

from yj01 t

group by t.deptno order by t.deptno;

--将null的地方显示null

select t.deptno,

decode(nvl(t.一月份, 0), 0, 'null', t.一月份) 一月份,decode(nvl(t.二月份, 0), 0, 'null', t.二月份) 二月份,

decode(nvl(t.三月份, 0), 0, 'null', t.三月份) 三月份

from (select t.deptno,

sum(decode(t.month, '一月份', t.yj)) 一月份,

sum(decode(t.month, '二月份', t.yj)) 二月份,

sum(decode(t.month, '三月份', t.yj)) 三月份

from yj01 t

group by t.deptno

order by t.deptno) t;

select t.deptno,

case nvl(t.一月份,0) when 0 then 'null' else to_char(t.一月份) end 一月份

from (select t.deptno,

sum(decode(t.month, '一月份', t.yj)) 一月份,

sum(decode(t.month, '二月份', t.yj)) 二月份,

sum(decode(t.month, '三月份', t.yj)) 三月份

from yj01 t

group by t.deptno

order by t.deptno) t;

--题目要求：根据Oracle数据库scott模式下的emp表和dept表，完成下列操作。

--（1）查询20号部门的所有员工信息。

select * from emp where deptno = 20;

--（2）查询所有工种为CLERK的员工的工号、员工名和部门名。

select empno,ename,deptno from emp where job like 'CLERK';

--（3）查询奖金（COMM）高于工资（SAL）的员工信息。

select * from emp where comm > sal;

--（4）查询奖金高于工资的20%的员工信息。

select * from emp where comm > (sal*0.2);

--（5）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。

select * from emp

where (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLERK');

--（6）查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息。

select * from emp

where job not in ('MANAGER','CLERK') and sal >= 2000 ;

--（7）查询有奖金的员工的不同工种。

select distinct job from emp where comm is not null;

--（8）查询所有员工工资和奖金的和。

select ename,(sal+nvl(comm,0)) salcomm from emp;

--（9）查询没有奖金或奖金低于100的员工信息。

select * from emp where (comm is null or comm < 100) ;

--（10）查询各月倒数第2天入职的员工信息。

select * from emp where hiredate in (select (last_day(hiredate)-1) from emp);

--（11）查询员工工龄大于或等于10年的员工信息。

select * from emp where (sysdate - hiredate)/365 >= 10 ;

--（12）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

--（13）查询员工名正好为6个字符的员工的信息。

select * from emp where length（ename）= 6 ;

--（14）查询员工名字中不包含字母“S”员工。

select * from emp where ename not in (select ename from emp where ename like '%S%') ;

select * from emp where ename not like ‘%S%’;

--（15）查询员工姓名的第2个字母为“M”的员工信息。

select * from emp where ename like '_M%';

--（16）查询所有员工姓名的前3个字符。

select substr(ename,1,3) from emp ;

--（17）查询所有员工的姓名，如果包含字母“s”，则用“S”替换。

select replace(ename,'s','S') from emp ;

--（18）查询员工的姓名和入职日期，并按入职日期从先到后进行排列。

select ename,hiredate from emp order by hiredate asc ;

--（19）显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列。

select ename,job,sal,comm from emp order by job desc,sal asc ;

--（20）显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序。

select ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm') from emp order by to_char(hiredate,'mm'),to_char(hiredate,'yyyy');

--（21）查询在2月份入职的所有员工信息。

select * from emp where to_char(hiredate,'mm') = 2 ;

--（22）查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。

select ename,floor((sysdate-hiredate)/365)||'年'||floor(mod((sysdate-hiredate),365)/30)||'月'||cell(mod(mod((sysdate-hiredate),365),30))||'天' from emp ;

--（23）查询至少有一个员工的部门信息。

select * from dept where deptno in (select distinct deptno from emp where mgr is not null) ;

--（24）查询工资比SMITH员工工资高的所有员工信息。

select * from emp where sal > (select sal from emp where ename like 'SMITH') ;

--（25）查询所有员工的姓名及其直接上级的姓名。

select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--（26）查询入职日期早于其直接上级领导的所有员工信息。

select * from emp where empno in (select staempno from (select empno staempno,hiredate stahiredate,mgr from emp) t join emp on t.mgr=emp.empno and stahiredate < hiredate) ;

--（27）查询所有部门及其员工信息，包括那些没有员工的部门。

select * from dept left join emp on emp.deptno=dept.deptno order by dept.deptno ;

--（28）查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

--（29）查询所有工种为CLERK的员工的姓名及其部门名称。

select ename,dname from emp join dept on job like 'CLERK' and emp.deptno=dept.deptno ;

--（30）查询最低工资大于2500的各种工作。

select job from (select min(sal) min_sal,job from emp group by job) where min_sal > 2500 ;

--（31）查询最低工资低于2000的部门及其员工信息。

select * from emp where deptno in (select deptno from (select min(sal) min_sal,deptno from emp group by deptno) where min_sal < '2000') ;

--（32）查询在SALES部门工作的员工的姓名信息。

select ename from emp where deptno = (select deptno from dept where dname like 'SALES');

--（33）查询工资高于公司平均工资的所有员工信息。

select * from emp where sal > (select avg(sal) from emp) ;

--（34）查询与SMITH员工从事相同工作的所有员工信息。

select * from emp where job in (select job from emp where ename like 'SMITH') and ename not like 'SMITH' ;

--（35）列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。

select ename,sal from emp where sal =any (select sal from emp where deptno = 30) ;

--（36）查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。

select ename,sal from emp where sal >all (select sal from emp where deptno = 30) ;

--（37）查询每个部门中的员工数量、平均工资和平均工作年限。

select dname,count,avg_sal,avg_date from dept join (select count(*) count,avg(sal) avg_sal,avg((sysdate-hiredate)/365) avg_date,deptno from emp group by deptno) t on dept.deptno = t.deptno ;

--（38）查询从事同一种工作但不属于同一部门的员工信息。

select distinct t1.empno,t1.ename,t1.deptno from emp t1 join emp t2 on t1.job like t2.job and t1.deptno <> t2.deptno ;

--（39）查询各个部门的详细信息以及部门人数、部门平均工资。

Select dept.*,person_num,avg_sal from dept,(select count(*) person_num,avg(sal) avg_sal,deptno from emp group by deptno) t where dept.deptno = t.deptno ;

--（40）查询各种工作的最低工资。

select job,min(sal) from emp group by job ;

--（41）查询各个部门中的不同工种的最高工资。

select max(sal),job,deptno from emp group by deptno,job order by deptno,job ；

--（42）查询10号部门员工以及领导的信息。

select * from emp where empno in (select mgr from emp where deptno=10) or deptno = 10 ；

--（43）查询各个部门的人数及平均工资。

select deptno,count(*),avg(sal) from emp group by deptno ；

--（44）查询工资为某个部门平均工资的员工信息。

select * from emp where sal in (select avg(sal) avg_sal from emp group by deptno) ;

--（45）查询工资高于本部门平均工资的员工的信息。

select emp.* from emp join (select deptno,avg(sal) avg_sal from emp group by deptno) t on emp.deptno=t.deptno and sal>avg_sal ;

--（46）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

select emp.*,avg_sal from emp join (select deptno,avg(sal) avg_sal from emp group by deptno) t on emp.deptno=t.deptno and sal>avg_sal ;

--（47）查询工资高于20号部门某个员工工资的员工的信息。

select * from emp where sal >any(select sal from emp where deptno=20);

--（48）统计各个工种的人数与平均工资。

select job,count(*),avg(sal) from emp group by job ;

--（49）统计每个部门中各个工种的人数与平均工资。

select deptno,job,count(*),avg(sal) from emp group by deptno,job order by deptno,job;

--（50）查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。

select emp.* from emp join (select sal,comm from emp where deptno = 10) t on emp.sal=t.sal and nvl(emp.comm,0)=nvl(t.comm,0) and emp.deptno != 10;

--（51）查询部门人数大于5的部门的员工的信息。

select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5);

--（52）查询所有员工工资都大于1000的部门的信息。

select * from dept where deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal < 1000)) ;

--（53）查询所有员工工资都大于1000的部门的信息及其员工信息。

select * from emp join dept on dept.deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal < 1000)) and dept.deptno=emp.deptno;

--（54）查询所有员工工资都在900~3000之间的部门的信息。

select * from dept where deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal not between 900 and 3000)) ;

--（55）查询所有工资都在900~3000之间的员工所在部门的员工信息。

select * from emp where deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal not between 900 and 3000)) ;

--（56）查询每个员工的领导所在部门的信息。

select * from (select e1.empno,e1.ename,e1.mgr mno,e2.ename mname,e2.deptno from emp e1 join emp e2 on e1.mgr=e2.empno) t join dept on t.deptno=dept.deptno ;

--（57）查询人数最多的部门信息。

select * from dept where deptno in (select deptno from (select count(*) count,deptno from emp group by deptno) where count in (select max(count) from (select count(*) count,deptno from emp group by deptno)));

--（58）查询30号部门中工资排序前3名的员工信息。

select * from emp where empno in (select empno from (select empno,sal from emp where deptno=30 order by sal desc) where rownum < 4) ;

--（59）查询所有员工中工资排在5~10名之间的员工信息。

select * from emp where empno in (select empno from (select empno,rownum num from (select empno,sal from emp order by sal desc)) where num between 5 and 10 ) ;

select empno from (select empno,sal from emp order by sal desc) where rownum <= 10 minus select empno from (select empno,sal from emp order by sal desc) where rownum < 5 ;

--（60）向emp表中插入一条记录，员工号为1357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日。

insertinto emp(empno,ename,sal,deptno,hiredate) values (1357,'oracle',2050,20,to_date('2002年5月10日','yyyy"年"mm"月"dd"日"')) ;

--（61）向emp表中插入一条记录，员工名字为FAN，员工号为8000，其他信息与SMITH员工的信息相同。

--（62）将各部门员工的工资修改为该员工所在部门平均工资加1000。

update emp t1 set sal = (select new_sal from (select avg(sal)+1000 new_sal,deptno from emp group by deptno) t2 wher e t1.deptno = t2.deptno ) ;

--1、查询82年员工

select e.* from emp e where to_char(e.hiredate, 'yy') like '82';

select e.* from emp e where to_char(e.hiredate,'yyyy')='1982';

--2、查询32年工龄的人员

select round(sysdate-e.hiredate)/365, e.ename,e.hiredate from emp e where round((sysdate-e.hiredate)/365)=32;

--3、显示员工雇佣期 6 个月后下一个星期一的日期

select next_day(add_months(e.hiredate,6),2) from emp e ;

--4、找没有上级的员工，把mgr的字段信息输出为 "boss"

select decode(e.mgr,null,'boss','中国好声音') from emp e;

--5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%

select decode(e.deptno,10,e.sal*1.1,20,e.sal*1.15, e.sal*1.18) 涨工资 ,e.deptno, e.sal from emp e ;

--Oracle_练习与答案

--1.求部门中薪水最高的人

select ename,sal,emp.deptno from emp

join (select deptno,max(sal) max_sal from emp group by deptno) t

on (emp.deptno = t.deptno and emp.sal = t.max_sal);

select ename, sal, deptno

from emp

where sal in (select max(sal) from emp group by deptno);

--2.求部门平均薪水的等级

select deptno, avg_sal, grade from

(select deptno,avg(sal) avg_sal from emp group by deptno) t

join salgrade

on (t.avg_sal between salgrade.losal and salgrade.hisal);

--3. 求部门平均的薪水等级

select deptno, avg(grade) avg_sal_grade from (select deptno, grade from emp

join salgrade on emp.sal between salgrade.losal and salgrade.hisal) group by deptno;

--4. 雇员中有哪些人是经理人

select distinct e2.ename manager from emp e1 join emp e2 on e1.mgr = e2.empno;

select ename from emp where empno in (select mgr from emp);

--5. 不准用组函数，求薪水的最高值

select distinct sal max_sal from emp where sal not in

(select e1.sal e1_sal from emp e1 join emp e2 on e1.sal < e2.sal);

select * from (select * from emp order by sal desc) t where rownum <2

--6. 求平均薪水最高的部门的部门编号

select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno)

where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal from emp group by

deptno) );

--组函数嵌套写法(对多可以嵌套一次，group by 只对内层函数有效)

select deptno, avg_sal from

(select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal =

(select max(avg(sal)) from emp group by deptno);

--7. 求平均薪水最高的部门的部门名称

select t1.deptno, dname, avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t1 join dept on t1.deptno = dept.deptno where avg_sal = (select max(avg_sal) from

(select deptno,avg(sal) avg_sal from emp group by deptno) );

select dname from dept where deptno = (select deptno from

(select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal =

(select max(avg_sal) from

(select deptno,avg(sal) avg_sal from emp group by deptno) ) );

--8. 求平均薪水的等级最低的部门的部门名称

--select dname from dept join (select deptno, grade from

(select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade

on (t.avg_sal between salgrade.losal and salgrade.hisal) ) t

on dept.deptno = t.deptno where t.grade = (select min(grade) from

(select avg(sal) avg_sal from emp group by deptno) t join salgrade

on (t.avg_sal between salgrade.losal and salgrade.hisal) );

--9.求部门经理人中平均薪水最低的部门名称

select dname from (select deptno, avg(sal) avg_sal from emp where empno in (select mgr from emp)group by deptno)t join dept on t.deptno = dept.deptno where avg_sal = (select min(avg_sal) from (select avg(sal) avg_sal from emp where empno in

(select mgr from emp) group by deptno) t );

--10. 求比普通员工的最高薪水还要高的经理人名称(not in)

select ename from emp where empno in (select mgr from emp) and sal > (select max(sal) from (select e2.sal from emp e1 right join emp e2 on e1.mgr = e2.empno where e1.ename is null) t );

select ename from emp where empno in (select mgr from emp) and sal >

(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) );

--//NOT IN遇到NULL则返回NULL，必须排除NULL值

--11. 求薪水最高的前5名雇员

select empno,ename from (select * from emp order by sal desc) where rownum<=5;

--12. 求薪水最高的第6到第10名雇(!important)

select ename,sal from (select t.*,rownum r from (select * from emp order by sal desc) t

) where r>=6 and r<=10;

--13. 求最后入职的5名员工

select ename, to_char(hiredate,'YYYY"年"MM"月"DD"日"') hiredate from (select t.*,rownum r from (select * from emp order by hiredate desc)t )

where r<=5;

select ename, to_char(hiredate,'YYYY"年"MM"月"DD""') hiredate from (select t.*,rownum r from

(select * from emp order by hiredate)t

)where r>(select count(*)-5 from emp);

28. 异常类型

异常		描述
CURSOR_ALREADY_OPEN		试图"OPEN"一个已经打开的游标
DUP_VAL_ON_INDEX		试图向有"UNIQUE"中插入重复的值
	INVALID_CURSOR		试图对以关闭的游标进行操作
	INVALID_NUMBER		在SQL语句中将字符转换成数字失败
	LOGIN_DENIED		使用无效用户登陆
	NO_DATA_FOUND		没有找到数据时
	NOT_LOGIN_ON		没有登陆Oracle就发出命令时
	PROGRAM_ERROR		PL/SQL存在诸如某个函数没有"RETURN"语句等内部问题
	STORAGE_ERROR		PL/SQL耗尽内存或内存严重不足
	TIMEOUT_ON_RESOURCE		Oracle等待资源期间发生超时
	TOO_MANY_ROWS		"SELECT INTO"返回多行时
	VALUE_ERROR		当出现赋值错误
	ZERO_DIVIDE		除数为零

11、oracle

oracle

1. 数据库介绍

2. 访问数据库&连接

3. oralce介绍

4. DDL

5. DML

6. DCL&TCL

7. 创建视图

8. 创建序列

9. 授权用户&角色

10. 数据类型

11. sql语言

12. sql函数

13. 数值型函数

14. 字符型函数

15. 日期函数

16. 转换函数

17. 聚组函数

18. 其他函数

19. 92和99语法

20. sql数据更新

21. 事务处理

22. 约束

23. 索引

24. oracle分页技术

25. 三范式

26. oracle常用命令

27. 重要练习

28. 异常类型

相关推荐