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;
declare
age number(3) := 100;
sal number(8) := 100;
agesal number(9);
begin
agesal := 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<a2 then
    a3   :=a2-a1;
  else
    a3:=0;
  end if;
  dbms_output.put_line('a3='||a3);
end;
/

--循環
set serveroutput on;
declare
  v_i number(5) := 1;
  v_sum number(5) := 0;
begin
loop
v_sum := v_sum+v_i;
v_i :=v_i+1;
exit when v_i>100;
end loop;
dbms_output.put_line('sum='||v_sum);
end;
/

--for 循環
set serveroutput on;
declare
  v_sum number(5) := 0;
begin
-- 在for循環中可以不用再declare中聲明
for v_i in 1..100 loop
 v_sum := v_sum+v_i;
end loop
dbms_output.put_line('sum='||v_sum);
end;
/

cursor 游標

set serveroutput on;
declare
--聲明變量為表中列的類型
--通過%type 取emp表中empno的類型
v_empno EMP.EMPNO% TYPE;
v_ename EMP.ENAME% TYPE;
--聲明一個游標
-- 關鍵字   游標名   關鍵字      結果集合
cursor v_emp_cursor is select empno,ename from emp order by ename;
begin
--從游標中獲取數據
  --打開游標
open v_emp_cursor;
--取一行 取出以后，游標下移一行
fetch v_emp_cursor into v_empno, v_ename;
dbms_output.put_line(v_empno||','||v_ename);
 --關閉游標
close v_emp_cursor;
end;
/

set serveroutput on;
declare
v_empno EMP.EMPNO% TYPE;
v_ename EMP.ENAME% TYPE;
cursor v_emp_cursor is select empno,ename from emp order by ename;
begin
open 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;
begin
open v_dept_cursor;
loop
fetch 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 
begin
sum := 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的實現