Java41: 数据库五(Oracle)

数据库设计：

三范式(OLD)

列的值唯一，不能有重复的列值

属性完全依赖于主键

必须满足第一范式

必须有主键

其他列必须完全依赖于主键

属性不依赖于其他非主属性（第二的加强）

必须满足第二范式

去除传递依赖

（在特定的场合，对效率的考虑 如：专门做冗余的时候，不要遵守第三）

Oracle 序列

可以通过序列来生成主键 一般的一个序列为一个表服务，也可以多个

创建序列

create sequence 序列名 start with 数值 incremet by 数值

| 不写 都是 1 |

删除序列

drop sequence 序列名

通过伪列 nextval 获取下一个值

select seq_stu.nextval from dual;

获取当前值 currval

select seq_stu.currval from dual;

create sequence seq_stu start with 100 incremet by5;select seq_stu.nextval from dual;insert into stu (id) values(seq_stu.nextval);

完整：

create table stu(  s_id number(10),  s_name varchar2(50),  constraint s_pk primary key(s_id))create sequence Seq_stu start with 100 increment by 5;select * from stu;

package jdbc;public class Stu {        private int id;        private String name;        public int getId() {                return id;        }        public void setId(int id) {                this.id = id;        }        public String getName() {                return name;        }        public void setName(String name) {                this.name = name;        }        }

package jdbc;import java.sql.Connection;import java.sql.PreparedStatement;public class StuDAO {        private static final String SQL = "insert into stu(s_id,s_name) values(seq_stu.nextval,?)";        public void save(Stu stu) throws Exception{                Connection con = null;                try{                        con = DBUtils.getConnection();                        PreparedStatement stmt = con.prepareStatement(SQL);                        stmt.setString(1,stu.getName());                        stmt.executeUpdate();                           }catch(Exception e){                        throw e;                }finally{                        if(con != null){                                con.close();                        }                }                        }}

package jdbc;import static org.junit.Assert.*;import org.junit.Test;public class TestStuDAO {        @Test        public void test() throws Exception {                StuDAO s = new StuDAO();                Stu ss = new Stu();                ss.setName("lmdtx");                s.save(ss);        }}

ER图(开源社区有真相)

找你喜欢的或者公司习惯的（工具） 实在不行a4纸

研究业务需求

设计绘制E-R关系图

设计文档

该写啥就写啥

user_tables 是oracle中表 想要查看所有的表 就可以查看这个表 就好了

select * from user_tables;

索引 Index

为了提升查询效率

二叉树;hash

经常要根据某个列进行查询，;选取的列不超过总数的10%

基于这个列的查询效率高

占用空间，插入时效率低

主键默认创建索引

索引和表 放在不同的表空间，这样效率更高

创建，删除

create index i_stu_name on stu(name);

drop index i_stu_name;

select * from user_indexes;--查询所有的索引

视图 View

方便权限划分

简化复杂查询

就是一段sql 查询出来的结果，想一个表，但是不是表

创建视图要有权限

grant create view to scott;

--创建视图create view stu_view as select id,name,sex from stu;

drop view stu_view  删除

对view 可以DQL

对简单view 可以DML

create view v_emp_1 as select empno,ename,job from emp;select * from v_emp_1;insert into emp (empno,ename,job) values(7333,'LMDTX','CEO');select * from v_emp_1 where empno=7333;insert into v_emp_1 values(7777,'lmdtx','CTO')select * from emp where empno=7777;create view v_emp_dept as select * from emp inner join dept using(deptno);   --视图的聚合函数部分需要使用别名create view v_emp_avg_sal as select job,avg(sal) from emp group by job order by avg(sal) ;create view v_emp_dept as select deptno,dname,empno,ename,job from emp inner join dept using(deptno);select * from v_emp_dept;--不能插入了insert into v_emp_dept values(10,'ACCOUNTING ',7111,'DSY','CTO');--没有约束的时候create view v_emp_sal2 as select * from emp where sal >1500;--可以插入，但是有问题，在 sal>1500 中插入 sal 是1000的insert into v_emp_sal2(empno,ename,sal) values(7474,'dsy',1000);select * from emp where empno=7474;--条件检查约束 可以插件数据是否可以通过该视图插入(是否符合该视图的查询条件)create view v_emp_sal3 as select * from emp where sal >1500 with check option constraint check_v_emp_sal_1;--就不能插入了  insert into v_emp_sal3(empno,ename,sal) values(6000,'dsy',1000);--只读视图create view v_emp_sal3 as select * from emp where sal >1500 with check read only check_v_emp_sal_2;

简单view

复杂view

检查view

只读view

外键约束

不是有外键就要添加外键约束

--建表建外键create table emp2(  id number(11),  name varchar2(20) not null,  sal number(12,2) not null,  deptid number(4),  constraint pk_emp2 primary key(id),  constraint fk_emp2 foreign key(deptid) references dept(id));--主键create table dept(  id number(4),  name varchar2(20) not null,  constraint pk_dept2 primary key(id));--在表中添加外键约束alter table service add constraint fk_service_account foreign key(account_id) references account(id);--删除外键约束alter table service drop constraint fk_service_account;

水平分割

垂直分割

存储过程

运行在数据库内部对数据进行操作的一段程序

oracle 中用PL/SQ 或者ProC

PL/SQL块

declare

--变量的声明

age number(3) := 100;

sal number(8);

agesal number(9);

--开始

begin

--程序

c := age+sal;

dbms_output.put_line();

--结束

end

/

set serveroutput on;declareage number(3) := 100;sal number(8) := 100;agesal number(9);beginagesal := age+sal;dbms_output.put_line('agesal='||agesal);end;/

--if判断set serveroutput on;declare  a1 number(5) := 100;  a2 number(5) := 100;  a3 number(5) ;begin  if a1   >a2 then    a3   :=a1+a2;  elsif a1
set serveroutput on;declarev_empno EMP.EMPNO% TYPE;v_ename EMP.ENAME% TYPE;cursor v_emp_cursor is select empno,ename from emp order by ename;beginopen v_emp_cursor;loop--循环fetch v_emp_cursor into v_empno, v_ename;exit when v_emp_cursor%notfound;--使用%notfound  作为退出条件dbms_output.put_line(v_empno||','||v_ename);end loop;close v_emp_cursor;end;/

rowtype

set serveroutput on;declare--定义一个结构体v_dept dept%rowtype;cursor v_dept_cursor is select deptno,dname,loc from dept;beginopen v_dept_cursor;loopfetch v_dept_cursor into v_dept;exit when v_dept_cursor%notfound;dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);end loop;end;/


简单的
create or replace procedure jisuanqi(a in number,b in number,sum out number,sub out number)as beginsum := a+b;sub := a-b;end;/
package other;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.Types;import online.zongxuan.netctoss.utils.DBUtils;public class TestCallProcedure {        public static void main(String[] args) throws Exception{                Connection con = DBUtils.getConnection();                //创建可调用的Statement 就是可以调用存储过程                CallableStatement ctmt = con.prepareCall("call jisuanqi(?,?,?,?)");                //设置输入参数                ctmt.setInt(1, 200);                ctmt.setInt(2, 100);                //注册输出参数                ctmt.registerOutParameter(3, Types.INTEGER);                ctmt.registerOutParameter(4, Types.INTEGER);                ctmt.execute();                System.out.println(ctmt.getInt(3));                //获取第二个输出(也就是设置的第四个参数)                //ctmt.getInt(2);                System.out.println(ctmt.getInt(4));                con.close();        }}



DAO

1、EJB（死难用）
2、Hibernate（沿袭EJB但是好用，自动的生成sql，效率不高）
3、MyBatis（更轻量，自己写sql）

导入MyBatis
填写定义的配置文件（xml）
编写实体类
定义DAO(定义接口)
定义和DAO接口对应的SQL语句（xml）
在配置文件中引用该xml
调用MyBatis 的apt 获得DAO的实现