oracle学习之--数据字典和数据库约束和存储过程

思路：

a.用户表和数据字典表

用户表：被用户创建和维护,包含用户的信息

数据字典： dictionary （包括表明和注释） dba_tab_coumns,dba_constraints,dba_tables,dba_indexes //对象信息 dba_segments,dba_extents //空间分配信息 dba_tablespaces,dba_data_files //数据库结构

b. 数据约束：

1、NOT NULL 非空 //唯一一个只能在列级定义的约束!!! 可以在外部将列修改为非空 alter table tableName modify 列名 not null

2、UNIQUE Key 唯一键 //可以包含一个或多个空值 每个唯一约束对应一个唯一索引

3、PRIMARY KEY 主键 //相当于非空且唯一约束,也对应一个唯一索引,一个表只能有一个主键.联合主键的应用:每个学生可以有多个课程成

4、FOREIGN KEY 外键 //只能为主键值或空

5、CHECK 检察



c.索引，视图，top-n语句，序列 //相当于存储过程

d.存储过程的使用

e.jdbc连接类图



修改表中的语句：

a. 向表中插入列 alter table tableName add 列名(varhchar2) default(‘p’) //列名和数据类型

b. 修改列 alter table tableName modify 列名 varchar2(45)

c. 删除列 alter table 表名 drop column 列名

d. 删除表 ALTER TABLE employee //ddl语句不可回滚,释放存储空间 DROP COLUMN sex; //不能回滚,所有数据和结构和索引都将被删除

e. 修改表名 rename 原表名 to 新表名



Oracle数据库的表:用户表和数据字典表

用户表:

被用户创建和维护,包含用户的信息



数据字典表 相当于mssql中的内置表,存储各种特殊信息

被Oracle数据库创建和维护的一些表

包括了数据库的信息



是每个Oracle数据库的核心

用于描述数据库和它的所有对象

包括了只读的表和视图

被 SYS用户拥有

被 Oracle server维护

用户可以使用 SELECT访问



数据约束:

Oracle使用SYS_Cn格式命名约束，也可以由用户命名

创建约束的时机

1、在建表的同时创建

2、建表后创建

a、约束条件可定义在列级或表级，相应的语法略有区别。

b、约束条件可以构建在单列或多列的组合上,如果约束条件构建在多列组合上，那么定义约束条件只能在表级定义



常用约束：

1、NOT NULL 非空 //唯一一个只能在列级定义的约束!!! 可以在外部将列修改为非空 alter table tableName modify 列名 not null

2、UNIQUE Key 唯一键 //可以包含一个或多个空值 每个唯一约束对应一个唯一索引

3、PRIMARY KEY 主键 //相当于非空且唯一约束,也对应一个唯一索引,一个表只能有一个主键.联合主键的应用:每个学生可以有多个课程成

4、FOREIGN KEY 外键 //只能为主键值或空

5、CHECK 检察



约束名：SYS_C[number]

约束类型：（unique,primary key,foreign key,check）





在内部表级定义的格式:

表（

constraint 约束名（自定） 约束类型（约束的字段名）

）

在外部定义的格式:

alter table 表名 add constraint 约束名（自定）约束类型(约束字段一，约束字段二)



定义组合约束：

alter table 表名 add constraint 约束名（自定）约束类型(约束字段一，约束字段二)

定义外键约束：

表（

constraint 约束名（自定） foreign key（约束的字段名） references 表名（列名）

)

定义检查约束：

表(

constain 约束名 check（值>600）

)





删除约束

alter table table_name drop constraint 约束名

删除主键约束及相关的外键约束

alter table 表名 drop primary key cascade;





索引 自定维护，提高查询效率

创建索引格式：

create index 索引名 on 表名 (列名1，列名2);

删除索引:

drop index 索引名





视图

创建视图：

Create view 视图名 as select * from user

查询视图：

Selectd * from 视图名

查询视图结构：

Desc 视图名 describe 视图名

创建一个复杂的视图 多表查寻

Create or replace view 视图名

As select ……

插入数据(一般不用)

Insert into 视图名 values(1,2,3)

禁止对视图进行操作

Create or replace view 视图名 as select ……

with read only //视图设置为只读

删除视图：

Drop view 视图名





TOP—N分析

用Top-N分析法可以得到某列上最什么的前n个值. 例如: 薪水最高的前5个员工

1、使用了rownum这个伪列，这个伪列将会返回行号，可以作为返回记录的序列号显示。

2、在from后面使用了子查询，这是标准的行内视图的使用。

3、在子查询中使用了order by进行排序，在前面的子查询中不需要使用。

4、在主查询中通过where条件中的rownum伪列定义过滤条件，只返回最什么的前几行数据。用between and不能查询到数据，因此可以考虑将rownum变为表中的列，在进行查询



同义词

创建同义词

Create synonym b for 词名

查询：

Select * from 词名

删除：

Drop synonym b



序列 //相当于自增长列

a.是一数据库对象，利用它可生成唯一的整数。b.一般使用序列自动地生成表主键值或唯一键值

c.不直接连接到数据库中的任何表

语法：

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n|]
[minvalue n ];
[cache]

[NOCYCLE|CYCLE]

INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

MAXVALUE：指定序列可生成的最大值。

MINVALUE：指定序列的最小值
NOCYCLE：一直累加，不循环

CYCLE：累加到最大值循环

--currval :返回序列的当前值

--nextval 返回序列首次引用时的起始值，以后使用nextval的引用将使

Select 序列名.currval from dual

Select 序列名.nextVal from dual



创建一个序列

Create sequence 序列名

increment by 1 //步长

start with 1 //开始值

maxvalue 21 //最大值

minValue 1 //最小值

cycle(oncycle) //循环 不循环

cache 20 //设置缓冲值，最少为20，避免多次操作硬盘，提高速度





修改一个序列

alter sequence emp_seq
increment by 2
minvalue 3
maxvalue 99999



存储过程：用于完成特定操作或者任务procedures

创建一个带返回值的存储过程

--修改dept的表

create or replace procedure updatedept

(

v_deptno in number,

v_dname in varchar2,

v_loc in varchar2,

v_demo out varchar2

)

is

begin

update dept set dname=v_dname,loc=v_loc where deptno=v_deptno;

commit;

v_demo:='success';

end;





调用存储过程

declare

v_demo varchar2(34);

begin

updatedept(13,'789','678',v_demo);

dbms_output.put_line(v_out);

end



oracle总结:

1 select

* 简单的查询(distinct,is not null,where,like,in,between and)

* order by排序 asc desc

* oracle中的函数(数学函数,字符函数,日前函数,转换函数*,其他函数)--oracle的函数

* oralce中的聚合函数(avg count max min sum)

* where group by having的执行流程



* 多表查询

* oracle8i

* 等值连接

* 非等值连接

* 左外连接 右外连接

* 自连接

* oracle9i及以后的版本

* 自然连接

* 内连接 左外连接 右外连接 全外连接

* 子查询

2 dml

* insert update delete merge

3 事务

* 什么事事务

* commit

* rollback

4 DDl

* 创建（修改 删除）表 视图 同义词 序列

* 约束(非空唯一 主键 外键 检查)

* Top n分析方法

5 存储过程







Jdbc连接



Jdbc API 的接口



DriverManager：

Connection——》Statement——》ResultSet

Connection——》PreparedStatement——》ResultSet

Connection——》CallableStatement——》ResultSet