Oracle知识点总结

Oracle知识点总结

根据阎赫老师讲义整理Zealjiang

1、Oracle数据库的安装和配置

⑴Oracle Internet级数据库

SQLServer 中小企业级数据库

Access 桌面级数据库

⑵Oracle的安装

注意：来源和目标的目录不允许有中文或空格

⑶Oracle数据库启动时必需开启的后台服务

①OracleOrahome90TNSListener 使第三方的软件或语言访问

②OracleServiceETC Oracle的实例 CRUD 增删改查

注意：②中的ETC是你起的实例的名字

⑷Oracle的开发工具

①DOS界面的开发平台 -> 运行->sqlplus

②Oracle本身基于DOS的平台 ->运行-> sqlplusw

③Oracle Enterprise Manager Console

④PL/SQL Developer7.1.5

⑸创建一个表的完整流程

①创建一个数数库

例子：创建一个数据库 ETC , 物理文件放在F:\ ，初始化1m ,自增长开启

create tablespace etc

datafile 'f:\etc.dbf'

size 1m

autoextend on;

删除表空间

drop tablespace 数据库名称;

②为该数据库创建一个帐号和密码

语法： create user 用户名称

identified by 密码

default tablespace 数据库名称

注意：1、在Oracle中账号和数据库one2one绑定

2、Oracle的密码不允许为纯数字或空

3、需要在system账号完成动作

修改Oracle的账号密码

语法：alter user 用户

identified by 新密码

③用grant 权限 to 用户为该帐户授权

语法： grant 权限名称 to 用户;

撤销权限

语法：revoke 权限名称 from 用户名;

④在该帐号下创建表

⑹Oracle中的事务处理

Transacation 事务

特点：整体提交(commit) 整体回滚(rollback)

事务的四个特性

①原子性不可分割

②持久性 -> 当数据整整写入到数据库物理文件中后，该数据被持久化

③隔离性 -> 事务之间相互独立互不干扰

④一致性 -> 数据安全

⑺Oracle中的保留点(还原点)

关键字：savepoint

使用方法：savepoint 名称;

如进行保留点回滚

rollback to mark2

⑻Oracle备份数据表数据

①create table 备份表名称 as 查询语句;

②Oracle备份表结构

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

2、Oracle的函数

⑴function （系统预定义函数）

函数和存储过程相比，在于函数必须有返回值，而存储过程只有输出参数。

语法：create or replace function 函数名称

return 返回类型

as

PL/SQL语句块

例子：输入一个员工工号，返回该员工所在部门平均工资

create or replace function fun_demo1(eno emp.empno%type) return emp.sal%type

as

avgsal emp.sal%type;

begin

select avg(sal) into avgsal from emp where empno=eno; return avgsal;

end;

调用：select fun_demo1(7788) from dual;

public int add(int num1,int num2){

int res = 0;

res = num1+num2;

return res;

}

⑵单行函数(单值函数) ：函数有且只有返回1个值

|-字符函数 substr instr

①字符串连接函数：concat

concat(字符串1,字符串2)

Oracle中字符连接操作符号 " || "

②字符串对齐函数：lpad -> left padding 左填充 /rpad

语法：lpad(字符串,预留位置,填充字符)

SQL: select lpad(ename,10,' ') from emp;

③字符串截取函数：substr -> substring

语法：substr(字符串,截取的起始位置,截取个数)

substr(字符串,截取的起始位置)

SQL:> select substr(ename,1,3) frome emp;

④字符查找函数：instr -> inner string

语法：instr(字符串,待找的字符,查找的起始位置,出现次数) SQL:> select ename,instr(ename,'T',1,1) from emp;

注意：当返回值为0时，字符不存在

⑤函数：initcap -> initial captor

语法：initcap(字符串)

SQL:> select initcap(ename) from emp;

⑥函数：length

语法：length(字符串)

SQL:> select * from emp where length(ename)=5;

⑦函数：lower / upper

语法：lower(字符串)

SQL:> select lower(ename) from emp;

|-日期函数 sysdate add_months

①函数：sysdate

SQL:> select sysdate from dual; dual-无实际意义，函数测试或其他测试使用

②月份差：months_between

语法：months_between(日期1,日期2)

SQL: > select * from emp where

months_between(sysdate,hiredate)>=144

③函数：last_day

语法：last_day(日期数据)

SQL:> select * from emp where

last_day(hiredate)-2=hiredate;

④函数：add_months

语法：add_months(时间,添加月份)

SQL：>select add_months(sysdate,3) from dual;

|-转换函数 to_char to_date

①转换函数 to_char

语法：to_char(时间数据,制定格式)

SQL:> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss d ddd day') from dual;

yyyy-mm-dd 年月日

hh24:mi:ss 时分秒(24)

d 一周第几天

ddd 一年的第几天

day 星期几

②to_date: 将文本类型数据按照制定格式转换成时间类型数据

语法：to_date('具体的时间字符数据','时间字符串格式')

to_date('2010-01-19','yyyy-mm-dd')

注意：Oracle中的date字段类型及其特殊

|-数学函数 abs ceil floor

abs(n) 绝对值

ceil(n) 向上取值 ceilling天花板 ceil(1.1) -> 2

floor(n) 向下取值 floor地板 floor(2.9) -> 2

mod(m,n) 求模取余 mod(5,2) -> 1

power(m,n) m的n次方 power(2,8) ->256

round(m,n) 四舍五入 m待处理数据 n精度 round(3.1415,2) -> 3.14

trunc(m) 整数截取 trunc(3.1415) = 3

sign(m) 符号

sqrt(m) 平方根

|-混合函数 user nvl

①user : 返回当前登录账号

SQL> select user from dual;

②nvl : 替空函数

语法：nvl(字符串,替换后的数据)

注意：替换后的数据必须与该字段类型保持一致

SQL> select ename,nvl(comm,0) from emp;

⑶分析函数：数据分析和挖掘功能 Data Mining rank dense_rank

作用：主要用于排名使用

函数：rank

①语法：rank() over (order by 字段名称)

SQL:> select rank() over(order by sal desc),ename,sal from emp; 若排名相同下一个将跳转

SQL:> select dense_rank() over(order by sal desc),ename,sal from emp; 连续

②语法：rank(参数1，参数2) within group(order by 字段1，字段

2)

SQL:> select ename,rank(2850,'BLAKE') within group(order by

sal,ename) from emp;

⑷分组函数：类似于SQLServer中的聚合函数 sum avg count

group by 分组关键字 having 条件

3、Oracle数据库的查询

⑴制定区间查询

Oracle中伪列：Oracle为每张数据表(包括用户自定义创建的数据表)都

自动创建两个位列分别是：rowid、rownum

通过命令：SQL> desc 表名; --查看表结构

rowid: 由18个字符组成唯一标识每一行

rownum: 行号

⑵介绍问题：使用Oracle分层查询以数据结构显示emp表中的人员关系语法：select lpad(ename,level*5,' ') from emp

connect by prior empno=mgr

start with mgr is null;

⑶并集：查询部门10的办事员和部门20的经理

关键字：union all

select * from emp where deptno=10 and job='CLERK'

union all

select * from emp where deptno=20 and job='MANAGER';

注意：union all不会消除查询结果中的重复数据, union会消除查询结果中的重复记录

⑷交集：查询部门10和部门20都有的工作类型

关键字：intersect

select job from emp where deptno=10

intersect

select job from emp where deptno=20;

⑸差集：查询部门30中有，而部门10中没有的工作类型

关键字：minus

select job from emp where deptno=30

minus

select job from emp where deptno=10;

4、Oracle数据库的数据对象

Oracle的数据库对象：用户、表、约束、序列、视图、同义词和索引

定义：但凡使用"create" 开头创建的对象称之为数据库对象。

⑴锁定/解除用户

语法：alter user 用户名称 account lock;

注意：该指令一般在system账号下输入

解除用户锁定

语法：alter user 用户名称 account unlock;

SQL > alter user scott account unlock;

注意：Oracle 11g 中 scott账号是默认锁定的，需要进行解锁处理。

⑵用户授权/撤销

grant 权限名称 to 用户名称;

撤销用户权限

revoke 权限名称 from 用户名;

⑶表

数据字段的类型

①数字类型：number

语法：number(长度,精度)

number(5,2) 数字长度为3，精度为2 范围：-999.99 ~ 999.99 number(5) 数字长度为5，精度为0 范围：-99999 ~ 99999

number 精度默认为0 范围：-32767 ~ 32768

②字符类型：varchar2 可变长度 char 字符类型

语法：varchar2(长度)

③日期类型：date

④long数据类型：该字段最大存储空间为2GB，该字段不允许添加索引

LOB数据类型：该字段最大存储空间为4GB，该字段不允许添加索引 CLOB ：大字符类型 -> 文章(小说，cnki)

BLOB ：大二进制类型 -> 图片、音频、视频.....多媒体文件

FLOB ：文件定位器 -> 内存指针

⑷创建数据表的语法结构：

create table 表名

(

字段名称1 类型 [约束],

字段名称1 类型 [约束],

……

字段名称n 类型 [约束]

)

①使用 desc 表名查看表结构

②给表添加字段address和telephone

语法：alter table 表名 add(字段名称类型);

③删除表字段address

语法：alter table 表名 drop column 字段名称;

④修改表中已有字段的类型

语法：alter table 表名 modify(字段名称新类型);

⑤修改表名称

语法：rename 原表名 to 新表名;

约束

完整性约束 = 准确性 + 一致性

⑸约束的四大分类：

①实体完整性约束 (行约束) -> 尽量减少数据表中数据的冗余(重复的数据)

技术实施：主键约束、唯一约束

语法：alter table 表名 add primary key(字段名称);

altet table 表名 add unique(字段名称);

添加非空约束

语法：alter table 表名 modify(字段名称 not null);

②域完整性约束 (列约束) -> 达到数据的准确性，控制数据的大小或范围或格式

技术实施：check检查约束

alter table 表名 add constraint 约束名称 check(条件);

③引用完整性约束 (表间约束) -> 达到数据的一致性

技术实施：外键约束

添加外键约束

alter table 外键表 add constraint 约束名称 foreign key(外键字段) references 主键表(主键字段);

删除外键约束

语法：alter table 表名 drop constraint 约束名称;

删除匿名约束

语法：alter table 表名 modify(字段名称 null);

④自定义完整性约束 -> 以上三种数据库内置约束不能满足开发人员的需求是，需要开发人员自定一些约束条件

技术实施：触发器

⑹使用sql脚本批量插入数据

使用命令 SQL > @路径+文件名称

SQL > @f:/a.sql;

⑺序列

定义：Oracle中使用sequence来实现字段的自增长功能，和SQLServer中的identity属性类型

语法：create sequence 序列名称

start with 起始数字

increment by 增长量;

用法：序列对象通过两个重要的属性进行访问取值

.nextval -> next value -> 下一个值

例子：select seq_1.nextval from dual;

.currval -> current value -> 当前值

例子：select seq_1.currval from dual;

如何实现其自增长功能

通过语法结构将序列对象与对应的数据表进行绑定，实现其自增长功能。

例子： create table users(userid number(2),username varchar2(10));

create sequence seq_users start with 1 increment by 1;

insert into users values(seq_users.nextval,'alvin');

注意：Oracle建议一个序列对象尽与一张数据表进行绑定

技巧：create sequence 序列名称; 默认从1每次增长1

⑻视图

作用：1、简化复杂的SQL语句

2、提高数据的访问安全性

语法：create or replace view 视图名称

as

复杂的SQL查询语句

视图是一张虚拟的数据表，在Table对象中不存在，只存在于内存中

注意：若视图由1张基表组成，修改视图将会修改基表数据，若视图由多张表组成，则不会修改基表数据

⑼同义词

作用：Oracle中的同义词提供各种数据库对象(表)的别名，目的在于提高数据表访问的安全性，尤其多用户并发访问时。

语法：create synonym 表的别名 for 表名;

SQL > create synonym bak1 for emp;

扩展：公有同义词 public

语法：create public synonym 别名 for 表名;

⑽索引

作用：提高SQL查询语句按照制定字段查询的效率

语法：create index 索引的名称 on 数据表(字段)

例子：为hiredate字段添加索引，提高按日期查询的SQL语句的效率

create index idx_hire on emp(hiredate)

原理：空间换效率

SQL语句索引优化规则

①不带where条件的SQL语句一定不能使用索引

②在where条件中有索引的字段不能使用函数

例子：假设我们查询在1987年参加工作的所有员工

SQL >select * from emp where to_char(hiredate,'yyyy')='1987';

SQL >select * from emp where hiredate=to_date('1987','yyyy');

③在where条件中有索引的字段不能参与运算

例子：查询在10000天以前参加工作的员工信息

SQL >select * from emp where sysdate-hiredate>10000;

SQL >select * from emp where hiredate

④尽量少用 not in , not exists , like '%' 等关键字

⑤书写查询语句的时候，应该首先考虑关联查询、其次考虑集合查询，最后考虑子查询

⑥where条件中限制性强的条件应该写在where最后的一个条件

where条件从右向左经行条件解析

⑦多表查询中，驱动表应该是返回数据少的表

例子：查询部门account 的所有员工信息

SQL > select e.*,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';

⑾Oracle中常用的数据字典

Oracle是以中以表管表的模式，同时数据字典是有表或视图组成。

数据字典的分类：

USER_xxx ：表示当前用户所拥有的数据库对象

ALL_xxxx ：表示当前用户与权力查看的数据库对象

DBA_xxxx ：表示数据中所拥有的全部对象，只有在超级管理员级别下才可查看。

xxxx ：代表数据库对象的复数形式，例如：tables indexes .......

5、Oracle数据库的多表查询

⑴等连接：select emp.ename,dept.dname from emp,dept where

emp.deptno=dept.deptno;

使用内连接模式编写：

⑵内连接：inner join ......on......

select emp.ename,dept.dname from emp inner join dept on

emp.deptno=dept.deptno;

分析：部门为主显示字段，dept为主表

姓名为辅助显示字段，emp为辅助表

⑶外连接：select d.dname,e.ename from emp e,dept d where

e.deptno(+)=d.deptno;

6、Oracle数据库的游标

游标cursor

游标的分类：隐式游标：Oracle自动应以一个隐式游标名称为SQL ,该游标不被程序员控制，自动开启、操作及结束。

显式游标：可供程序员自己创建及操作

|--- 静态游标

|--- 动态游标

⑴静态游标loop循环游标/ for循环游标

====loop循环游标=====

步骤1：创建一个游标

cursor 游标名称is SQL查询语句;

步骤2：开启游标

open 游标名称; -> 执行定义的SQL查询语句并将结果集合存放到游标中

步骤3：使用loop循环遍历游标中的数据并进行相应处理

loop

fetch 游标名称into 变量;

exit when 游标名称%notfound;

.....

end loop;

步骤4：关闭游标

close 游标名称;

例子：显示部门编号为10的员工姓名

declare

--步骤1：创建一个游标

cursor mycur is select ename from emp where deptno=10;

en emp.ename%type;

begin

--步骤2：开启游标

open mycur;

--步骤3：使用loop循环遍历游标中的数据并进行相应处理loop

fetch mycur into en;

exit when mycur%notfound;

dbms_output.put_line(en);

end loop;

--步骤4：关闭游标

close mycur;

end;

=======for循环游标=======

declare

--步骤1：创建一个游标

cursor mycur is select * from emp where deptno=10;

erow emp%rowtype;

begin

--步骤3：使用for循环遍历游标中的数据并进行相应处理for erow in mycur

loop

dbms_output.put_line(erow.ename);

end loop;

end;

⑵动态游标(扩展)

作用：使用游标变量高度重用(只能使用loop循环) 语法结构：

步骤1：声明一个动态游标类型

type 类型名称 is ref cursor;

步骤2：使用声明号的类型创建一个游标变量

游标名称类型名称;

步骤3：打开游标并且绑定SQL语句

open 游标名称 for SQL查询语句

步骤4：使用循环遍历游标中的数据并进行处理

loop

fetch 游标名称 into 变量;

exit when 游标名称%notfound

.....数据处理......

end loop;

步骤5：重复3~4