sowon's Programming

자바와 DB의 연동

(1) JDBC (Java Database Conectivity)

1) 해당 DB의 드라이버 준비 : ojdbc6.jar

2) java.sql.*

- Conuection : DB와의 연결정보

- Statement : 명령어 전달 및 실행

- ResultSet : 결과를 프로그램으로 가져올때 사용

3) ResultSet 명령어가 select일때만 쓰임

- DB로부터 가져온 결과를 임시로 저장하는 객체

- 하나의 테이블을 저장할 수 있는 구조

- 연결 지향성

- 처음에는 반드시 next()를 호출해야 한다.

- 레코드 단위로 이동할 수 있는 메서드

next()

previous()

first()

last()

4) PreparedStatement

// 복잡한 쿼리문을 해소할 수 있다.

5) CallableStatement

- 프로시저 호출

Trigger

- 묵시적으로 수행되는 procedure 프로시저

- 트리거는 table과 별도로 database에 저장

- 트리거는 view에 대해서가 아니라 table에 관해서만 정의 될 수 있다.

- 행 트리거 : 컬럼의 각각의 행의 데이터 행 변화가 생길때마다 실행,

 그 데이터 행의 실제값을 제어 할 수 있다.

- 문장 트리거 : 트리거 사건에 의해 단 한번 실행됨,

 컬럼의 각 데이터 행을 제어 할 수 없다.

트리거 문법

CREATE [OR REPLACE] TRIGGER trigger_name

BEFORE | AFTER

trigger_event ON table_name

[FOR EACH ROW]

[WHEN (condition)]

PL/SQL block

- BEFORE : INSERT, UPDATE, DELETE 문이 실행되기 전에 트리거가 실행

- AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행

- trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있다.

- FOR EACH ROW : 이 옵션이 있으면 행 트리거가 된다.

VIEW

(1) 정의 : 실제 테이블을 여러 관점에서 투영한 가상 테이블

(2) 목적 

1) 사용자의 편의

2) 관리와 보안성

(3) 문법

CREATE [OR REPLACE] VIEW 뷰 이름 [(컬럼명,...)] AS SubQuery(select문);

/*영업부 직원 중 사번, 이름, 급여, 커미션을 view로 작성

create view vw_emp_sales as 

select empno, ename, sal, comm from emp where deptno=30;

재수정 삭제했다가 다시 만든것

create or replace view vw_emp_sales as

select empno, ename, sal, comm from emp where deptno=30;

*/

- user_views 

desc user_views 로 볼수잇다.

내가만든 view의 이름을 볼경우 

select view_name from user_views;

- user_objects // view의 모든 정보를 볼 수 있다.

=================문제==================

/*영업부를 제외하고 급여가 2000이상인 직원들 중 사번, 이름, 급여, 부서, 부서명, 급여등급을 조회하는 테이블을 뷰로 만들어라*/

select empno, ename, sal, e.deptno, d.dname, s.grade from emp e 

inner join dept d on e.deptno = d.deptno

inner join salgrade s on e.sal>=s.losal and e.sal<=s.hisal

where sal>=2000 and e.deptno !=30;

Data Integrity(데이터 무결성)

(1) 무엇을 지킬것인가? 어떻게 지킬것인가

1) 실제(Entity) 무결성

 - 중복된 데이터 방지

 - primary Key, Unique

2) 영역(domain) 무결성

 - Check 

3) 참조(Reference) 무결성

 - Foreign Key /*자식 테이블에 참조 당함*/

(1) 칼럼(필드)의 속성

1) NN(Not Null) 속성

- Primary Key, not null

2) ND(Not Diplicated) 속성

- Primary Key, Unique

3) NC(Not Change) 속성 //변경 할수 없는 것

(3) Primary Key

1) 하나의 테이블에 단 한개만 설정

2) 여러 개의 필드르 ㄹ묶어서 설정 가능

 =============실습=============

1) testuser1으로 접속

create table tblExam()

/*nn속성을 하게하는 방법 not null*/

create table tblExam(

 id    number        not null,

 name  varchar2(10)   null

)

insert into tblExam(name) values('임꺽정'); 

/*nn속성으로 id 없이 넣기 불가능하다*/

/*id는 중복값 안됨*/

/*pimary key 넣기*/

/*1. 테이블 만들때 처음에 생성, 

2 이미 테이블 만들어진 상태에서 기본키 수정으로 넣기*/

create table tblExam(

 id    number         null,

 name  varchar2(10)   null

);

select * from tblExam;

alter table tblExam ADD constraint pk_id primary key(id);

insert into tblExam(id, name) values(null, '홍길동');

insert into tblExam(id, name) values(1, '홍길동');

insert into tblExam(id, name) values(1, '임꺽정'); 

/*임꺽정 무결성 제약조건에 위배됨 */

desc user_constraints;

select constraint_name, table_name from user_constraints;                                 /*어떤 제약이 걸려있는지 보고싶으면 이 쿼리문 이용*

(4) Uuique

1) 하나의 테이블에 여러 개 설정 가능

2) 중복 방지

create table tblExam(

 id    number    ,

 name  varchar2(10),

 constraints uk_id unique(id)

);

select constraint_name, table_name from user_constraints;

 /*어떤 제약이 걸려있는지 보고싶으면 이 쿼리문 이용*/

purge recyclebin; /*휴지통 비우기*/

/*절대로 중복을 허락하지 않음 unique 하지만 null값은 안막아짐*/

insert into tblExam(id,name) values(1, '홍길동'); 

insert into tblExam(name) values('임꺽정'); 

select * from tblExam;

(5) 필드 수정 , 추가, 삭제

1) 필드 수정

alter table tblExam modify name varchar2(20);

 /*말로만 수정이지 원래는 삭제했다가 다시만드는것*/

/*varchar 가변길이 문자열의 길이가 일정하지 않을때 

최대길이를 기준*/

2) 필드 추가

alter table tblExam ADD zipcode char(7);

/*char 고정길이 문자열의 길이가 일정할때 

한글자든 두글자를 입력하던 메모리를 7글자로 고정 성능상 좋음*/

3) 필드 삭제

alter table tblExam Drop column zipcode; 

/*삭제할때는 어떤것을 삭제할것인지 써야됨*/

(6) Default

create table tblExam(

 id    number   default 0 ,

 name  varchar2(10),

point number(3,2)

);

insert into tblExam  ( point) values( 3.14);

/*name에 값을 입력하지 않으면 '무명씨'라고 자동입력되게 처리*/

alter table tblExam modify name default '무명씨';

select * from tblExam;

(7) SEQUENCE

- 자동으로 카운팅하여 일련번호를 리턴함

- 중복값 들어갈 수 없음

===========================================

Create sequence seq_num;

-SEQUENCE를 사용하기 위해서는 정수형만 가능하다.

insert into tblExam(id, name, point) values(seq_num.nextVal, '박소원');

(8) Check

drop table tblExam;

/*city에 입력할 수 있는 도시는 단 서울, 경기, 인천 뿐이다 .*/

create table tblExam(

id number,

name varchar2(10),

city varchar2(10),

constraints ck_city check(city = '서울' or city = '경기'

 or city = '인천')

)

insert into tblExam(id, name, city) values(seq_num.nextVal, '박소원', '서울');

insert into tblExam(id, name, city) values(seq_num.nextVal, '홍길동', '제주'); 

/*체크 제약조건이 위배 왜냐면 서울,경기, 인천만 해놈*/

(9) Foreign key

Create table tblDept(

deptno char(3),

dname varchar2(10)

);

insert into tblDept(deptno, dname) values('100', '영업부');

insert into tblDept(deptno, dname) values('101', '마케팅부');

create table tblEmp(

empno number,

ename varchar2(10),

hiredate date,

deptno char(3)

);

insert into tblEmp (empno, ename, hiredate, deptno) 

valuse(1, '홍길동', sysdate, '100');/*deptno를 기본키로 설정해줘야됨*/

alter table tblDept add constraints pk_deptno Primary key(deptno);

alter table tblemp add constraints pk_deptno_emp Primary key(deptno);

/*테이블 안에 조건이 안맞는 것이 있으면 테이블 변경이 안됨*/

alter table tblEmp add constraints fk_dept_emp_deptno foreign                 key(deptno) references tblDept(deptno);    

update tblEmp set deptno = '101' where empno=2;

Transaction

(1) "All Or Nothing"

(2) 명령어

commit // 완전히 완료 되었을때

roolback // 원상태로 돌리기  

(3) DML

(4) 동기화 //동시사용을 못하게 

*table을 실수로 지웠을때, roolback과 비슷한 관계 

 table 삭제시, 휴지통개념으로 들어간다.

 show recyclebin 으로 목록 볼수 있음

 flashback table 테이블명 to before drop; 지우기 전으로 되돌리고싶다

 단, oracle 에서만 사용 가능

1. 커미션을 받는 직원에 대해 이름, 부서명, 근무지를 조회

select ename, dname, loc from emp INNER JOIN dept ON emp.deptno=dept.deptno where emp.comm IS NOT NULL;

2. 이름 중간에 L자가 있는 직원에 대해 이름, 업무, 부서명, 근무지 조회

select ename, job, dname, loc from emp INNER JOIN dept ON emp.deptno=dept.deptno where emp.ename like '%L%';

3. 각 직원들에 대해서 그들의 관리자보다 먼저 입사한 직원에 대해 이름, 입사일, 관리자이름, 관리자 입사일을 조회

select e1.ename, e1.hiredate as 직원입사, e2.hiredate as 관리자입사, e1.mgr, e2.ename from emp e1 INNER JOIN emp e2 ON e1.mgr = e2.empno where e1.hiredate <= e2.hiredate;

*4. 말단사원의 사번, 이름, 업무, 부서번호, 근무지를 조회

select empno, ename, job, emp.deptno, loc from emp right JOIN dept ON emp.deptno = dept.deptno where empno not in(select mgr from emp where mgr is not null);

*** 테이블을 하나 작성한다.(tblBook)

author title

-----------------------

최주현 하늘과 땅

최주현 바다

유은정 바다

박성우 문

최주현 문

박성우 천국

최지은 천국

최주현 천국

박성우 고슴도치

서금동 나

CREATE TABLE book(

author varchar2(15),

title varchar2(15)

);

INSERT INTO book VALUES('최주현','하늘과 땅');

INSERT INTO book VALUES('최주현','바다');

INSERT INTO book VALUES('유은정','바다');

INSERT INTO book VALUES('박성우','문');

INSERT INTO book VALUES('최주현','문');

INSERT INTO book VALUES('박성우','천국');

INSERT INTO book VALUES('최지은','천국');

INSERT INTO book VALUES('최주현','천국');

INSERT INTO book VALUES('박성우','고슴도치');

INSERT INTO book VALUES('서금동','나');

5. 한권의 책에 대해서 두명 이상의 작가가 쓴 책을 검색하시오.

예시) 책이름  작가명  작가명

select b1.title, b1.author 작가명1, b2.author 작가명2 from book b1 inner JOIN book b2 ON b1.title = b2.title and b1.author < b2.author;

6. 한권의 책에 대해서 세명의 작가가 쓴 책을 검색하시오.

예시) 책이름  작가명  작가명 작가명

select b1.title, b1.author 작가명1, b2.author 작가명2, b3.author 작가명3 from book b1 inner JOIN book b2 ON b1.title = b2.title inner join book b3 on b3.title = b2.title and b1.author < b2.author and b1.author > b3.author;

JOIN

(1) 여러 개의 테이블을 병합하여 하나의 결과를 도출하기 위한 방법

(2) 종류

1) Cartasian JOIN(데카르트 조인, 데카르트 곱)

2) Equi JOIN

- 공통 필드의 레코드를 가져오는 방법

- Inner Join(Natural Join)

3) Non Equi Join

- 공통 필드가 없을 때 사용가능

4) Outer JOIN

- Extended Join(Inner join + 공통되지 않은 레코드)

- 종류

Left Outer Join

Right Outer Join

Full Outer Join

5) Self Join

- 원하는 데이터들이 한 테이블에 있을 때 사용.

(3) 문법

1) 표준 문법

2) 자체 DBMS SQL문법

T-SQL : MS SQL Server

FL-SQL : Oracle

(4) SET 연산자

1) UNION

2) UNION ALL

3) INTERSECT

4) MINUS

=======================

SELECT deptno from emp union select deptno from dept;

SELECT deptno from emp union all select deptno from dept; //합집합

SELECT deptno from emp intersect select deptno from dept;

SELECT deptno from emp minus select deptno from dept;

/* 직원의 사번, 이름 업무 관리자 관리자 이름을 조회*/

select empno, ename, job, mgr, ename, from emp inner join emp on emp.mgr = emp.empno;

본 문제는, Oracle Database 11g 실습 계정에 대한 문제 입니다.

1. emp테이블에서 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균(보너스가 NULL이 아닌 평균, NULL포함한 평균),등록되어 있는 부서의 수(총개수와 중복을 제외한 개수)를 구하여 출력

select count(empno), count(comm), avg(comm), avg(nvl(comm,0)), count(deptno), count(distinct deptno) from emp;

2. 부서인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 조회

select deptno, count(*), sum(sal) from emp  group by deptno having(count(empno)>4);

3. 급여가 최대 2900 이상인 부서에 대해 부서번호, 평균 급여, 급여의 합을 조회

select deptno, avg(sal), sum(sal) from emp group by deptno having(max(sal)>2900);

4. 최고급여와 최소급여의 차이는 얼마인가?

select max(sal), min(sal) , max(sal)-min(sal) from emp;

5. 예시)

년도 count min max avg sum

---------------------------------------------

80 1 800 800 800 800

81 10 950 5000 2282.5 22825

82 2 1300 3000 2150 4300

83 1 1100 1100 1100 1100

select substr(hiredate,1,2) as 년도, count(*) as count, min(sal) as min, max(sal) as max, avg(sal) as avg, sum(sal)as sum from emp group by substr(hiredate,1,2) having(substr(hiredate,1,2)>=80 and substr(hiredate,1,2)<=83 )order by substr(hiredate,1,2);

6. 예시)

TOTAL 1980 1981 1982 1983

---------------------------------------

14 1 10 2 1

select count(*)as total, count(decode(to_char(hiredate,'YYYY'),'1980',0)) as "1980",

count(decode(to_char(hiredate,'YYYY'),'1981',0)) as "1981",

count(decode(to_char(hiredate,'YYYY'),'1982',0)) as "1982",

count(decode(to_char(hiredate,'YYYY'),'1983',0)) as "1983" from emp;

7. 예시)

업무 10 20 30 Total

-------------------------------------------------

ANALYST 6000 6000

CLERK 1300 1900 950 4150

MANAGER 2450 2975 2850 8275

PRESIDENT 5000 5000

SALESMAN 5600 5600

select distinct job as 업무, sum(decode(deptno,10,sal)) as "10",

sum(decode(deptno,20,sal)) as "20", sum(decode(deptno,30,sal)) as "30",

sum(nvl(decode(deptno,10,sal),0))+sum(nvl(decode(deptno,20,sal),0))+sum(nvl(decode(deptno,30,sal),0)) Total from emp group by job;

---------- Sub Query 문제

8. BLAKE와 같은 부서에 있는 모든 직원의 사번, 이름, 입사일자 조회

select empno, ename, hiredate from emp where deptno = (select deptno from emp where ename='BLAKE');

9. SELECT empno, ename, deptno, sal, comm FROM emp WHERE (sal, comm) IN(SELECT sal, comm FROM emp WHERE deptno=30);

  이 쿼리를 수정하여 comm이 null인 직원도 출력할 수 있도록 하시오

select empno, ename, deptno, sal, comm from emp where (sal, nvl(comm,0)) in(select sal, nvl(comm,0) from emp where deptno=30);

10. 평균 급여 이상을 받는 직원들의 사번, 이름을 조회. 단, 급여가 많은 순으로 정렬

select empno, ename, sal from emp where sal>=(select avg(sal) from emp) order by sal desc;

11. 이름에 'T'자가 들어가는 직원이 근무하는 부서에서 근무하는 직원의 사번, 이름, 급여 조회

select empno, ename, sal from emp where deptno in(select deptno from emp where ename like'%T%');

12. 부서의 위치가 dallas인 모든 직원에 대해 사번, 이름, 급여, 업무를 조회 // dept테이블

select empno, ename, sal, job from emp where  deptno = (select deptno from dept where loc='DALLAS');

13. King에게 보고하는 모든 직원의 이름과 부서,업무,급여를 조회

select ename, deptno, job, sal from emp where mgr = (select empno from emp where ename='KING');

14. 월급이 30번 부서의 최저 급여보다 높은 직원의 사번, 이름, 급여를 조회

select empno, ename, sal from emp where sal >(select min(sal) from emp where deptno=30);

15. 10번 부서에서 30번 부서의 직원과 같은 업무를 하는 직원의 이름과 업무를 조회

select  ename, job from emp e where deptno=10 and job in(select job from emp where  deptno=30 and (e.job = job));

SubQuery 중첩 쿼리 

(1) 다른 query문에 포함된 query문

scott의 급여보다 더 많이 받는 직원의 이름 업무 급여를 조회

select sal from emp where ename='SCOTT';

select ename, sal from emp where sal>(select sal from emp where ename='SCOTT');

(2) 반드시 ()를 사용가능

(3) 연산자의 오른쪽에서 사용가능

(4) order by 사용 불가

(5) 결과값에 따른 유형

1) 단일행

2) 다중행

3) 다중열

(6) 연산자

1) 단일행

>, <, >=, <=, =, != ...

2) 다중행

in , any, all, exists not, ...

any 는 or의 뜻 하나만 만족

all 은 and의 뜻 모두 만족

(7) 종류

1) 연관 서브쿼리

2) 비연관 서브쿼리

문제 

/*사번이 7521의 업무와 같고 급여가 7934보다 많은 직원의 사번, 이름, 업무, 급여*/

/*서브쿼리 안썼을경우*/

select empno, ename, sal,job from emp;

select empno, ename, sal , job from emp where job='SALESMAN' and sal>1300;

/*서브쿼리 쓸때*/

select empno, ename, sal, job from emp where job=(select job from emp where empno=7521) and sal>(select sal from emp where empno=7934);

/*업무별로 최소 급여를 받는 직원의 사번, 이름, 업무, 급여, 부서번호 조회*/

select min(sal) from emp group by job;

select empno, ename, job, sal FROM EMP WHERE sal = 800 or sal = 1250 or sal = 5000 or sal 2450 or sal 3000;

select empno, ename, job, sal, deptno  from emp where sal in(select min(sal) from emp group by job);

/*업무별로 최소 급여보다 많은 급여를 받는 직원의 사번, 이름, 업무, 급여 , 부서번호 조회*/

select empno, ename, job, sal, deptno  from emp where sal > any(select min(sal) from emp group by job);

/*업무별로 최대 급여 이상 급여를 받는 직원의 사번, 이름, 업무, 급여, 부서번호 조회*/

select max(sal) from emp group by job;

select empno, ename, job, sal, deptno from emp where sal>= all(select max(sal) from emp group by job);

/*적어도 한명의 직원으로부터 보고를받을 수 있는 직원의 이름, 업무, 입사일자, 급여를 조회*/

select empno, ename, job, hiredate, sal from emp e where exists(select * from emp where e.empno=mgr);

다중열

/*사번이 7934인 직원의 급여를 1500으로 수정 보너스를 300으로 수정*/

update emp set sal=1500, comm=300 where empno=7934;

update emp set sal=1300, comm=null where empno=7934;

select sal, comm from emp where empno=7934;

/*급여와 보너스가 30번 부서에 있는 직원의 급여와 보너스가 같은 직원에 대해 사번, 이름, 부서번호, 급여, 보너스를 조회*/

select empno, ename, deptno, sal, comm from emp where (sal,comm) in(select sal,comm  from emp where deptno=30);

DML(Data Manipulation Language)

(1) INSERT, UPDATE, DELETE

(2) 실습

1) 계정 생성

create user 계정ID identified by 비밀번호;

2) 권한 주기

grant connect, resource to 계정ID;

- connect 접속, resource 수정같은것

3) 테이블 생성

create table tbltest(

id number,

name varchar2(10),

hiredate date

);

4) 데이터 입력

삽입 insert into 테이블명(필드명,...) values(실제값,...) 

5) 데이터 수정

update 테이블명 set 필드명 = 값...

6) 데이터 삭제

delect from 테이블명  where // 테이블안에있는 모든 데이터 삭제