Oracle

初识Oracle

Oracle数据库的主要特点

1. 支持多用户、大事务量的事务处理
2. 在保持数据安全性和完整性方面性能优越
3. 支持分布式数据处理
4. 具有可移植性

Oracle数据类型

字符数据类型

1. char 固定长度的字符串，列长度可以是1~2000字节，如果定义时未指明大小，默认占用1字节。
2. varchar2 数据类型支持可变长度的字符串
3. nchar 国家字符集

数值数据类型

1. number 可以存储正数、负数、零、定点数和精度为38为的浮点数

日期时间数据类型

1. date 用于存储表中的日期和时间数据
2. timesamp 用于存储日期的年、月、日、以及时间的小时、分和秒，秒值精确到小数点后6位，同时包含时区信息
3. systimestamp 函数返回当前时间日期、时间和时区

LOB数据类型

1. CLOB 能够存储大量字符数据
2. BLOB 可以存储较大的二进制对象
3. BFILE 将二进制文件存储在数据库外部的操作系统文件
4. NCLOB 用于存储大的nchar字符数据

Oracle中的伪列

rowid

数据库中每行都有一个行地址，rowid伪列返回该行地址，可以使用rowid值来定义表中的一行。

用途：

1. 能以最快的方式访问表中的一行
2. 能显示表的行是如何存储的
3. 可以作为表中行的唯一标识

如：
select rowid，ename from SCOTT.emp where ename='SMITH';

rownum

对于一个查询返回的每行，rownum伪列返回一个数值代表行的次序，限制查询返回行数

SQL语言

1. 数据定义语言（DDL）：create、alter、truncate（截断）、drop（删除）
2. 数据操纵语言（DML）：insert、select、delete、update
3. 事务控制语言（TCL）：commit（提交）、savepoint（保存点）、rollback（回滚）
4. 数据控制语言（DCL）：grant（授予）、revoke（回收）

数据定义语言

create table

语法：

create table[schema.]table(colum datatype[,column datatype[,...]]);

1.schema表示对象所有者
2.table表示表名称
3.column表示列的名称
4.datatype表示该列的数据类型及其宽度
如：

create table stuInfo(
       stuno number,
       stuname varchar2(16),
       stuage number,
       stuid number(18),
       stuseat number
);

命名规则：
1）表名首字符为字母
2）不能使用Oracle保留字来为表名
3）表名的最大长度为30字符
4）同一用户不同表不能具有相同的名称
5）可以使用下划线、数字和字母，不能使用空格和单引号

truncate table命令

删除表中记录不删除表结构
语法：

truncate table<tablename>;

数据操纵语言

选择无重复的行：

select distinct stuName,stuAge from stuInfo;

注意：
筛除内容全部相同的行，保留一行

带条件和排序的select命令

select stuNo，stuName，stuAge 
from stuInfo where stuAge>17 
order by stuName asc,stuAge desc;

使用列表名

是为列表表达式提供另一个名称，位于列表表达式后面，并显示在列标题中

select stuName as "姓名",stuAge as "年龄", stuId as "身份证号" 
from stuInfo

利用现有的创建新表

语法：

create table <newtable> as select{*|cloumn(s)}
from <oldtable>
[where <condition>];

如:

create table newStuInfo1
as
select stuName,stuNo,stuAge from stuInfo;

复制表结构不复制记录

create table newStuInfo2
as
select * from stuInfo where 1=2;

DML语言操作

1. 查看表中行数

select count(1) from stuInfo;

2. 取出stuName、stuAge不存在重复数据

select stuName，stuAge from stuInfo
group  by stuName,stuAge
having(count(stuName||stuAge)<2)

提示：
"||"为连接操作符

事务控制语言

1. commit：事务提交
2. rollback：回滚事务
3. savepolnt：事务中创建存储点
4. rollback to <savapoint_name> :将事务回滚到存储点

sql操作符

1. 比较操作符：=、!=、<、>、>=、between… and、in、like、is null
2. 逻辑操作符：and、or、not
3. 集合操作符:union（联合）、union all（联合所有）、intersect（交集）、minus（减集）
4. 连接操作符：||

转换函数

1. to_char：转换字符串类型
2. to_date：转换日期类型
3. to_number：转换值类型

Oracle数据库应用

创建表空间

create tablesspace tablespacename datafile 'filename'[size integer[k|M]]
[autoextend[off|no]];

如：

create tablespace app_info --创建名
datafile 'D:\data\app_info.dbf' --指定表空间的物理数据文件
size 10M --表空间初始大小
autoextend on --开启自动扩容
next 10M --下次扩容的大小

删除表空间

drop tablespace tablespacename

创建用户

create User user
identified by password
[default tablespace tablespace]
[temporary tablespace tablespace]

default tablespace 或temporary tablespace 为用户确定默认表空间或临时表空间

数据库权限

系统权限

1. create session：连接到数据库
2. create table：创建表
3. create view：创建视图
4. create sequence：创建序列

对象权限

1. 管理员直接向用户授予权限
2. 管理员将权限授予角色，然后再将角色授予一个或多个用户
   授予权限语法：

create 权限|角色 to 用户名；

撤销权限语法：

revoke 权限|角色 from 用户名;

序列

创建序列

1. 序列是用来生成唯一，连续的整数的数据库对象。
2. 序列通常用来生成主键或唯一键的值
3. 序列可以按升序排列，也可以按降序排列

create sequence squence_name
[start with integer]
[increment by integer]
[maxvalue integer | nomaxvalue]
[minvalue integer | nominvalue]
[cycle | nocycle]
[cache integer | nocache];

start with：指定要生成的第一个序列号
increment by：用于指定序列号之间的间隔，其默认值为1
maxvalue：指定序列可以生成的最大值
nomaxvalue：升序序列的最大值设为10²⁷，将降序序列的最大设为-1。默认选项
minvalue：指定序列的最小值
nominvalue：升序序列的最小值设为1，将降序序列的最小值设为-10²⁶。默认选项
cycle ：指定序列到最大值或最小值，将继续从头开始生成值
nocycle：指定序列到最大值或最小值后，将不能再继续生成值。默认选项
cache ：可以预先分配一组序列号，并将其保留在内存中，可以更快访问序列号
nocache：不会加快访问速度而预先分配序号列号。

--创建序列  从序号10开始，每次增加1，最大为2000，不循环，再增加会报错，缓存30个序号号
create sequence seql
start with 10
increment by 1
maxvalue 2000
nocycle
cache  30;

访问序列

1. nextval：创建序列后第一次使用nextval时将返回序列的初始值
2. currval：返回序列的当前值，即最后一次引用nextval时返回的值

更改序列

1. 设置或删除minvalue或maxvalue
2. 修改增量值
3. 修改缓存中序列号的数目

alter sequence [schema.]sequence_name
[incremnt by integer]
[maxvalue integer|nomaxvalue]
[minvalue integer|nominvalue]
[cycle|nocycle]
[cache integer|nocache];

删除序列

语法：

deop sequence [schema.]sequence_name;

同义词

1. 简化sql语句
2. 隐藏对象的名称和所有者
3. 为分布式数据库的远程对象提供了位置透明性
4. 提供对对象的公共访问

私有同义词

语法：

create [or replace] synonym [schema.]synonym_name
for [schema.]object_name;

公有同义词

语法：

create [or replace] public synonym synonym_name
for [schema.]object_name;

公有同义词和私有同义词区别

私有同义词只能在当前模式下访问，不能与当前模式的对象同名
公有同义词可被所有的数据库用户访问

删除同义词

语法：

drop [public] synonym [schema.] synonym_name;

索引

1.B树索引

create [unique] index index_name on table_name(colum_list)[tablespace tablespace_name];

2. 唯一索引

create unique index index_name on table_name(colum_list) ;

3. 反向键索引

create unique index index_name on table_name(colum_list) reveres;

4. 位图索引

create bitmap index index_name on table_name(colum_list);

删除索引

drop index index_bit_job;

PL/SQL编程

1. 声明常量

语法：
variable_name data_type[(size)][:=init_value]

2. 命名规则
   1. 标识符名不能超过30个字符
   2. 第一个字符必须为字母
   3. 不区分大小写
   4. 不能用“-”（减号）
   5. 不能是sql保留字

控制语句

条件控制语句

1. if

语法：
if<布尔表达式> then
pl/sql 和sql语句
end if;
或者
if<布尔表达式> then
pl/sql 和sql语句
else
其他语句
end if;

2. case

语法：
case 条件表达式
when 条件表达式结果1 then
语句断1
when 条件表达式结果2 then
语句断2
.....
[else 语句段]
end case;

循环控制

1.loop

语法：
loop 
要执行的语句;
exit when<条件语句> --条件满足，退出循环语句
end loop；

2. where

while <布尔表达式> loop
要执行的语句;
end loop;

3. for

for 循环计数器 in[reverese] 下限... 上限 loop
要执行的语句
end loop;

游标

1. 声明游标

语法:
cursor currsor_name [(parameter[,parameter]...)]
[return return_type] is select_statement;

2. 打开游标

语法:
open cursor_name[(parameters)];

3. 提取游标

语法:
fetch cursor_name into variables;

4. 关闭游标

close cursor_name;

存储过程

1.创建存储过程

create [or replace] procedure procedure_name
[parameter_list]
{is|as}
[local_declarations]
begin
executable_statements
[exception]
[exception_handlers]
end [procedure_name];

2. 调用存储过程

语法：
exec[ute] procedure_name (parameters_list);

3. 存储过程的参数模式

parameter_name[in|out|in out] datatype[{:=|default} expression]

4. 访问权限

--授予权限
grant execute on add_employee to A_oe;
--撤销权限
revoke execute on add_employee from A_oe;

5. 删除存储过程

drop procedure procedure_name;