Shulou(Shulou.com)06/01 Report--

Database design:

Three paradigms (OLD)

Column values are unique and cannot have duplicate column values

Property is completely dependent on the primary key

Must satisfy the first paradigm

There must be a primary key

Other columns must be completely dependent on the primary key

Attributes do not depend on other non-primary attributes (second enhancement)

Must satisfy the second paradigm

Remove transitive dependency

(on specific occasions, the consideration of efficiency, such as: when specializing in redundancy, do not follow the third.)

Oracle sequence

You can use a sequence to generate a sequence of primary keys to serve a table, or multiple

Create a sequence

Create sequence sequence name start with numeric incremet by numeric

| | 1 if you leave it unwritten | |

Delete sequence

Drop sequence sequence name

Get the next value through the pseudo column nextval

Select seq_stu.nextval from dual

Get the current value 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)

Complete:

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 * 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 Public void save (Stu stu) throws Exception {Connection con = null; try {con = DBUtils.getConnection (); PreparedStatement stmt = con.prepareStatement (SQL); stmt.setString (1memstu.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 diagram (the open source community has the truth)

It's not good to find the tools you like or the company is used to.

Research business needs

Design and draw Emurr diagram

Design document

Write what you should write.

User_tables is a table in oracle. If you want to view all the tables, you can just view this table.

Select * from user_tables

Index Index

In order to improve query efficiency

Binary tree; hash

It is often necessary to query according to a column, and the selected columns do not exceed 10% of the total.

The query based on this column is efficient

Takes up space and is inefficient when inserting

The primary key creates an index by default

Indexes and tables are placed in different tablespaces, which is more efficient

Create, delete

Create index i_stu_name on stu (name); drop index all indexes are queried by select * from user_indexes;--

View View

Convenient authority division

Simplify complex queries

It is the result of a sql query. Think of a table, but not a table.

You need permission to create a view.

Grant create view to scott

-- create a view create view stu_view as select id,name,sex from stu;drop view stu_view delete

You can DQL view.

DML can be used for simple view

Create view v_emp_1 as select empno,ename,job from emp;select * from vault emptiness 1 * insert into emp (empno,ename,job) values (7333); select * from v_emp_1 where empno=7333;insert into v_emp_1 values (7777) select * from emp where empno=7777;create view v_emp_dept as select * from emp inner join dept using (deptno) -- the aggregate function part of the view needs to use the alias 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 vested emptiness depends on the insert into v_emp_dept values-cannot be inserted into the CTO. -- when there are no constraints, create view v_emp_sal2 as select * from emp where sal > 1500 from emp where empno=7474-can be inserted, but there is a problem. In sal > 1500, insert insert into v_emp_sal2 (empno,ename,sal) values with a sal of 1000 (7474); select * Murray -- conditional check whether data can be inserted through this view (whether it meets the query criteria of this view) create view v_emp_sal3 as select * from emp where sal > 1500 with check option constraint check_v_emp_sal_1;-- cannot be inserted into insert into v_emp_sal3 (empno,ename,sal) values (6000) -- read-only view create view v_emp_sal3 as select * from emp where sal > 1500 with check read only check_v_emp_sal_2

Simple view

Complex view

Check view

Read-only view

Foreign key constraint

If you don't have a foreign key, you should add a foreign key constraint.

-- Foreign keys create table emp2 (id number (11), name varchar2 (20) not null, sal number (12Power2) not null, deptid number (4), constraint pk_emp2 primary key (id), constraint fk_emp2 foreign key (deptid) references dept (id);-- Primary key create table dept (id number (4), name varchar2 (20) not null, constraint pk_dept2 primary key (id)) -- add foreign key constraint alter table service add constraint fk_service_account foreign key (account_id) references account (id) to the table;-- delete foreign key constraint alter table service drop constraint fk_service_account

Horizontal division

Vertical segmentation

Stored procedure

Run a program that manipulates data within a database

Use PL/SQ or ProC in oracle

PL/SQL block

Declare

-- declaration of variables

Age number (3): = 100

Sal number (8)

Agesal number (9)

-- start

Begin

-- Program

C: = age+sal

Dbms_output.put_line ()

-- end

End

/

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

-- if judges set serveroutput on;declare A1 number (5): = 100; a2 number (5): = 100; a3 number (5); begin if A1 > a2 then A3: = A1 then a2; elsif a1100 sum=' end loop;dbms_output.put_line ('sum=' | | v_sum); end;/--for cycle set serveroutput on;declare v_sum number (5): = 0 Begin-- can no longer declare for in 1.. 100 in the for loop without declaring it in declare loop v_sum: = v_sum loopdbms_output.put_line ('sum=' | | v_sum); end;/

Cursor cursor

Set serveroutput on;declare-- declares the variable as the type of column in the table-- takes the type v_empno EMP.EMPNO% TYPE;v_ename EMP.ENAME% TYPE;-- of the empno in the emp table through% type and declares a cursor-- keyword cursor name keyword result set cursor v_emp_cursor is select empno,ename from emp order by ename;begin-- gets data from the cursor-- opens the cursor open v_emp_cursor -- after the row is taken out, the cursor moves down one line fetch v_emp_cursor into v_empno, and the cursor moves down one line (v_empno | |','| | v_ename);-- closes the cursor close vandalism emptiness cursorsorcross 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-- loop fetch v_emp_cursor into v_empno, end loop;close notfound exit when vandalism emptiness cursorter% notabilityuse% notfound as exit condition dbms_output.put_line (v_empno | |','| | v_ename); end loop;close vandalism emptiness cursorority /

Rowtype

Set serveroutput on;declare-- defines a structure v_dept dept%rowtype;cursor v_dept_cursor is select deptno,dname,loc from dept;beginopen vaulting deptress cursorter v_dept dept%rowtype;cursor v_dept_cursor is select deptno,dname,loc from dept;beginopen loopfetch v_dept_cursor into vandalism deptExp exit vascal deptCursor% notnamed dbmsdescribe output.putSecretline (v_dept.deptno | |','| | v_dept.dname |','| | v_dept.loc); end loop;end;/

Simple

Create or replace procedure jisuanqi (ain number,b in number,sum out number,sub out number) as beginsum: = Connection con DBUtils.getConnection () throws Exception {Connection con = DBUtils.getConnection () / / to create a callable Statement, you can call the stored procedure CallableStatement ctmt = con.prepareCall ("call jisuanqi (?)"); / / set the input parameter ctmt.setInt (1200); ctmt.setInt (2100) / / Registration output parameters ctmt.registerOutParameter (3, Types.INTEGER); ctmt.registerOutParameter (4, Types.INTEGER); ctmt.execute (); System.out.println (ctmt.getInt (3)) / / get the second output (that is, the fourth parameter set) / / ctmt.getInt (2); System.out.println (ctmt.getInt (4)); con.close ();}}

DAO

1. EJB (difficult to use)

2. Hibernate (follow EJB but easy to use, automatically generate sql, inefficient)

3. MyBatis (lighter, write your own sql)

Import MyBatis

Fill in the defined configuration file (xml)

Write entity classes

Define DAO (define interface)

Define the SQL statement (xml) corresponding to the DAO interface

Reference the xml in the configuration file

Call the apt of MyBatis to get the implementation of DAO

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.