Oracle约束、视图、索引、事务、序列、DML、JDBC

最新推荐文章于 2024-04-19 17:49:05 发布

蝎子乐乐

最新推荐文章于 2024-04-19 17:49:05 发布

阅读量120

点赞数 3

文章标签： oracle 数据库 java

本文链接：https://blog.csdn.net/qq_43472662/article/details/131075040

版权

Oracle

约束

不推荐将逻辑约束(检查约束、默认约束)放在前端，后端的选择判断语句是更好的选择

类型和写法

主键约束
唯一约束
非空约束
检查约束
外键约束

写法一：创建表时添加约束使用默认名称

create table  tb_user(
  --主键约束
  userid number(5) primary key,
  --外键约束
  cid number(5) references clazz(cid),
  --非空约束
  username varchar2(30) not null, 
  --检查约束 
  userpwd varchar2(20) check(length(userpwd) between 4 and 18), 
  --唯一约束
  email  varchar2(30) unique,
  --默认约束
  regtime date default(sysdate)
);

写法二：创建表同时创建约束指定约束名称

create table  tb_user(
  --主键约束
  userid number(5) constraint pk_user primary key,
  --外键约束
  cid number(5) constraint fk_cid references clazz(cid),
  --非空约束
  username varchar2(30) constraint nameNotNull not null, 
  --唯一约束
  email  varchar2(30) unique,
  --默认约束
  regtime date default(sysdate)
);

写法三：在结构结束之前为当前表中指定字段添加约束

create table  tb_user(
  userid number(5),
  username varchar2(30),
  userpwd varchar2(20) constraint nn_user_pwd not null , 
  age number(3) default(18) ,
  gender char(3) default('男'),
  email  varchar2(30),
  regtime date default(sysdate),
  --主键约束
  constraint pk_user_id  primary key (userid),
  --外键约束
  constraint fk_cid foreign key(cid) references clazz(cid),
  --非空约束
  constraint name_not_null check(username is not null),
  --检查约束
  constraint ck_user_name check(length(username)between 4 and 20) , 
  --唯一约束
  constraint uq_user_email unique(email)
);

写法四：追加约束

--主键约束
alter table tb_user add constraint pk_user_id  primary key (userid);
--外键约束
alter table tb_user add constraint fk_cid foreign key(cid) references clazz(cid);
--检查约束
alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ;
--唯一约束 
alter table tb_user add constraint uq_user_email unique(email);
--默认与非空
alter table tb_user modify age default 18;
alter table tb_user modify username not null;

启用和禁用

在我们需要大规模插入数据且确定数据满足约束要求时禁用约束再进行插入可以显著提高执行效率
我们禁用和启用约束是根据约束名称来操作的，使用自定义的约束名可以方便我们进行操作

我们可以使用如下的方式来启用约束或者禁用约束

ALTER TABLE tb_user disable constraint 约束名;
ALTER TABLE tb_user enable constraint 约束名;

存在外键约束时的表的修改和删除

我们将引用其他表的属性作为外键的表称作从表，被引用的表称作主表
当我们对从表进行修改操作时，外键属性值必须来自主表被引用的属性存在的值或者为null
在对从表进行删除时没有特殊情况
在对主表进行删除或者修改操作时，会有特殊情况出现。此时我们需要对操作语句进行一些处理。
- 主表修改或删除了从表中引用的属性值
处理一：将从表中牵涉到的元组删除
处理二：删除主从表的外键依赖
处理三：删除主表的同时级联删除主外键约束，使用以下语句实现
```
drop table tb_txt cascade constraints;
```

视图

视图是建立在结果集与表之间的虚拟表
作用是简化封装sql
普通用户一般没有创建视图的权限，可以登录sys管理员账号使用 grant dba to 账户名 赋予用户权限，同样的可以使用 revoke dba from 账户名 回收用户权限

可以使用以下语句创建一个视图：

--格式
create or replace view 视图名 as select语句 
--实例
create or replace view vw_xixi_haha as select empno,ename,sal,deptno from emp;

使用以上语句创建的视图在对视图或进行修改时会修改数据源，对数据源的修改也会同步到对视图的查询上

可以在创建视图时添加只读选项限制用户修改视图：

create or replace view vw_xixi_haha as select empno,ename,sal,deptno from emp with read only;

视图使用以下语句进行删除：
```
drop view 视图名;
```

索引

索引可以提高查询效率
索引相当于目录
索引是透明的,一个字段上是否添加了索引对字段的使用没有任何影响
大数据量前提下,做查询才会提高效率,如果频繁做增删,反而会降低效率,索引也是数据库的对象之一,需要维护
唯一性较好,少量修改,大量查询的字段适合添加索引
Oracle数据库自动为主键添加索引

可以使用以下语句添加和删除一个索引：

	--添加索引
	create index 索引名on 表名(字段名);
	--删除索引
	drop index 索引名;
	```

事务

特点

原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事
务进行的所有数据修改，必须在所有相关的表中得到反映。
隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
.持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢
失。

隔离等级

隔离级别	脏读	不可重复读	幻读
Read uncommitted（读未提交）	是	是	是
Read committed（读已提交）	否	是	是
Repeatable read（可重复读）	否	否	是
Serializable（串行读）	否	否	否

Oracle 默认的隔离级别是 read committed。
Oracle 支持上述四种隔离级别中的两种:read committed 和 serializable。除此之外， Oralce 中还定义
Read only 和 Read write 隔离级别。
Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集。
Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。
丢失更新：两个事务同时存储，一个存储 100 ，一个存储 200，最终可能至存储了 200 或者 100，那
另一个的更新就没成功，即结果不为预想的 300
脏读：事务 T1 更新了一行数据，还没有提交所做的修改，T2 读取更新后的数据，T1回滚，T2 读取的数
据无效，这种数据称为脏读数据。
不可重复读 ：事务 T1 读取一行数据，T2 修改了 T1 刚刚读取的记录，T1 再次查询，发现与第一次读取
的记录不相同，称为不可重复读。
幻读：事务 T1 读取一条带 WHERE 条件的语句，返回结果集，T2 插入一条新纪录，恰好也是 T1 的
WHERE 条件，T1 再次查询，结果集中又看到 T2 的记录，新纪录就叫做幻读。

事务的开启

Oracle在进行增删改操作时默认自动开启

事务的关闭

成功
- 正常执行完成的 DDL 语句：create、alter、drop
- 正常执行完 DCL 语句 GRANT、REVOKE
- 正常退出的 SQLPlus 或者 SQL Developer 等客户端
- 如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)
- 手动提交：使用 commit
失败
- rollback ，手动回滚
- 非法退出意外的断电

序列

序列相当于一个管理流水号的工具
管理类似主键字段的值,数值型的,有变化规律的，与mysql的属性自增作用类似但是性质有较大区别
序列没有与表与字段绑定，序列的删除不影响之前的使用
一次使用序列需要先获取下一个最新值

序列的创建和使用：

 --create sequence 序列名  start with  起始值  increment by 步进; 
 create sequence seq_deptno start with 70  increment by 1; 
 --当前值 序列名.currval
 select seq_deptno.currval from dual;
 --最新值 序列名.nextval
 select seq_deptno.nextval from dual;

DML

插入

--insert into 表名 values(值列表);
insert into  dept values(60,'教学部','上海');
--insert into 表(指定列) values(值列表);
insert into dept(deptno,dname) values(70,'营销部');
--insert into 表名 select 查询列 from 源表 where 过滤数据; 
insert into emp_his2 select ename,sal from emp where deptno = 30;
--insert into 表(指定列) select 查询列 from 源表 where 过滤数据; 
insert into emp_his2(ename) select ename from emp where deptno = 10;

删除

--删除一个或多个或所有数据
--delete from 表名; 删除一张表中所有数据
delete from emp_his2;
----delete from 表名 where 行过滤条件; 满足条件的数据删除
delete dept where deptno = 70;

更新

--update 表名 set 字段=值 [,....] where  过滤行记录;
update dept set loc='三亚' where deptno = 70;
update dept set dname='A'||dname,loc='青岛' where deptno in (50,60,70);

--update 表名 set (字段列表) =(select 字段列表 from 源表 where 过滤源表记录) where 更新记录的条件手动更改字段值：
update dept set (dname,loc) =(select ename,job from emp where empno=7369) where deptno in (50,60);

JDBC

实现多数据库访问的方式

在这里插入图片描述
Java提供API接口，又各个数据库厂商根据自己需求实现接口，接口的功能对外一致，功能的具体实现各个数据库自有区分

操作流程

加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

建立连接

 Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE",
                "SCOTT",
                "TIGER"
        );

准备sql
```
String sql = "SELECT * FROM DEPT";
```

构建处理块

Statement state = conn.createStatement();

发送sql，回收结果

ResultSet result = state.executeQuery(sql);

处理结果

  while(result.next()){
            int deptno = result.getInt(1);
            String dname = result.getString("dname");
            String loc = result.getString(3);
            System.out.println(deptno+"-->"+dname+"-->"+loc);
        }