국비 교육 36일차 - JOIN, JAVA-ORACLE 연동

오전에는 무난하게 진도를 나가는 듯 했으나 자바 연동하기 시작하면서 또 이제 새로운것들을 배우고 나니

아 역시 빡세구나.. 다시 상기되면서..

하여튼 열심히 해야지

 

일단 오전에는 오라클을 했기 때문에 그것부터 먼저 정리하도록 하겠다. 

 

/*
    JOIN : 한개 이상의 테이불에서 원하는 데이터 추출
           ========
    = 조인 조건 
     1. 같은 데이터를 가지고 있어야 한다 
        영화정보 = 맛집정보 (지역)
    2. 항상 컬럼명이 동일하지 않아도 된다.
        (기본 : 중복이 안된 상태-primary key, 참조하는 상태-foreign key)
    3. JOIN은 데이터 여러개의 테이블에서 검색 : SELECT문장에서 사용이 가능 
    = 조인 종류 => 이차원 FOR
     1. INNER JOIN (NULL일 경우 허용하지 않는다) - 교집합 (일반 USER)
        = EQUI_JOIN : 연산자 (=) 
        형식)
            ORACLE JOIN : 오라클에서만 사용하는 JOIN 명령어
            => SELECT A.col1, A.col2, B.col1, B.col2
                FROM A,B
                WHERE A.col3=B.col3
                           ===
                기본은 컬럼명 앞에 테이블명, 테이블 별칭을 사용한다
                예외) 테이블에서 컬럼명이 다르면 생략이 가능, 같은 컬럼명이면 별칭 혹은 테이블명 꼭 붙여여함 
                => 테이블명.컬럼명
                => 테이블별칭.컬럼명
                => 테이블별팅
                    FROM A a, B b
            ANSI JOIN : 표준회된 조인 (다른 데이터베이스에서 사용이 가능)
            => SELECT A.col1, A.col2, B.col1, B.col2
                FROM A JOIN B => , 대신 (INNER) JOIN => INNER는 생략이 가능
                ON A.col3=B.col3 => WHERE 대신 ON
                           ===
                기본은 컬럼명 앞에 테이블명, 테이블 별칭을 사용한다
                예외) 테이블에서 컬럼명이 다르면 생략이 가능, 같은 컬럼명이면 별칭 혹은 테이블명 꼭 붙여여함 
                => 테이블명.컬럼명
                => 테이블별칭.컬럼명
                => 테이블별팅
                    FROM A a, B b
        = NON_EQUI_JOIN : 연산자 (=가 아닌 연산자) >= AND <=, BETWEEN ~ AND (포함이 되어 있는 상태)
    2. OUTER JOIN (INNER JOIN + 알파) => NULL값일 경우에 결과값을 가지고 온다 - 관리자 모드
        = LEFT OUTER JOIN => INTERSECT + MINUS (A-B)
            1. ORACLE JOIN
            SELECT A.col1, B.col1, B.col2, A.col2
            FROM A, B
            WHERE  A.col3=B.col3(+);
            2. ANSI JOIN
            SELECT A.col1, B.col1, B.col2, A.col2
            FROM A LEFT OUTER JOIN B
            WHERE  A.col3=B.col3;
        = RIGHT OUTER JOIN => INTERSECT + MINUS (B-A)
             1. ORACLE JOIN
            SELECT A.col1, B.col1, B.col2, A.col2
            FROM A, B
            WHERE  A.col3(+)=B.col3;
            2. ANSI JOIN
            SELECT A.col1, B.col1, B.col2, A.col2
            FROM A RIGHT OUTER JOIN B
            WHERE  A.col3=B.col3;
        = FULL OUTER JOIN => UNION ALL (사용빈도 낮음)
             1. ANSI JOIN
            SELECT A.col1, B.col1, B.col2, A.col2
            FROM A FULL OUTER JOIN B
            WHERE  A.col3=B.col3;
*/

--사원명, 부서명, 급여등급을 출력하라.
SELECT ename, dname, grade 
FROM emp, dept, salgrade
WHERE emp.deptno=dept.deptno
AND sal BETWEEN losal AND hisal;  

/*
급여 1000이상인 사원을 대상으로 조사!
		 부서번호별, job별 평균 급여를 구하되
		 평균급여가 2000 이상인
		 부서의 부서no, job, 평균 급여를 출력하시오

*/
SELECT deptno, job, AVG(sal)
FROM emp
WHERE sal>=1000 
GROUP BY deptno, job
HAVING AVG(sal)>2000;

-- 사원들의 이름, 부서 번호, 부서 이름을 출력하라?
SELECT ename, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno;

-- 30번 부서의 사원들의 이름, 직업, 부서위치를 출력하라?
SELECT ename, job, loc 
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND emp.deptno = 30;

--수당을 받는 사원의 이름, 직업, 부서위치를 출력하라?
SELECT ename, job, loc 
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND emp.comm IS NOT NULL AND emp.comm>0;

--DALLAS에서 근무하는 사원의 이름, 직업, 부서번호, 부서이름을 출력하라.
SELECT ename, job, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND dept.loc LIKE '%DALLAS%';

--이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라?
SELECT ename, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND ename LIKE '%A%';

-- 사원이름과 그 사원의 관리자 이름을 출력하라
SELECT e1.ename "employee", e2.ename "manager"
FROM emp e1, emp e2
WHERE e1.mgr=e2.empno(+);

--사원이름과 직업, 급여, 급여등급을 출력하라?
SELECT ename, job, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal;

-- 사원이름과 부서명과 월급을 출력하는데 월급이 3000이상인 사원을 출력하라?
SELECT ename, dname, sal
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND sal>=3000;

-- BLAKE이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라?
SELECT ename, hiredate
FROM emp
WHERE hiredate> (SELECT hiredate FROM emp WHERE ename='BLAKE');


-- 부서명이 SALES 부서사원 중 가장 많은 급여를 받는 사원의 급여는?
SELECT MAX(sal)
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND dname LIKE 'SALES%';

--
SELECT DISTINCT *
FROM emp, dept
WHERE emp.deptno=dept.deptno;

-- emp(ename,job), dept(전체)
SELECT ename, job, dept.*
FROM emp, dept
WHERE emp.deptno=dept.deptno;

어제 했던 부분 복습 INNER JOIN과 OUTER 조인 활용해서 SELECT문으로 데이터 검색해올 수 있다. 자세한 부분은 어제 포스팅에 해뒀다

 

-- 20210206 DDL
/*
    DQL : SELECT => ROW단위
    DDL : 생성(정의언어) = CREATE, DROP, TRUNCATE(데이터만 지우고 테이블 형식은 남김), ALTER, RENAME => COLUMN단위 
            1) 데이터 저장 공간 (메모리공간) : 테이블 
            2) 가상 저장공간 (보안) : VIEW (SELECT 문장)
            3) 자동증가번호 : 중복없는 데이터 - SEQUENCE
            4) 찾기 (최적) : INDEX
            5) PL/SQL (FUNCTION, PROCEDURE, TRIGGER) : 재사용 
    1. 오라클 데이터형
        문자저장 => String
         = CHAR : 고정 바이트, 1~2000byte (한글은 1000 byte까지만 가능)
                  ==========
                  CHAR(10) => 숫자, 알파벳 (1글자~10글자까지 사용이 가능)
                              한글 (1글자~5글자까지 사용이 가능)
                  CHAR(10) => 'A' => 메모리 크기 10BYTE
                              'AB' => 메모리 크기 10BYTE
                  데이터 크기가 고정된 경우
         = VARCHAR2 : 가변 바이크, 1~4000byte
                      ==========
                      VARCHAR2(10) => 'A' => 메모리 크기 1byte
                                      'AB' => 메모리 크기 2byte
         = CLOB : 가변 바이트, 4GB (2GB:LONG)
                 ============
                 2000자를 넘었을 때 사용 EX) 영화줄거리, 자기소개, 회사소개...
        숫자저장
         = NUMBER (p,s) p : 1~38 ,  s:-84~127 
                        ==          ==
                        숫자 자리수  소수점 이하 자리수 
            NUMBER => 14자리수 // 자바로 받을때는 int가 아니라 long으로 받아야함
            NUMBER(4) => 0~9999
            NUMBER(7,2) => 정수 (7자리까지 사용이 가능), 실수 (5자리까지 사용, 2자리는 소수점으로 사용)
                           =========================  ========
                           int                         double
         
        날짜저장
         = DATE : default, java.util.Date
         = TIMESTAMP : 기록경주 (Date를 보완)
        기타저장 (동영상, 이미지, 사진) InputStream
         = BLOB : 2진파일로 저장
         = BFILE : 파일 형식으로 저장
    2. 제약조건 (정형화된 데이터) => 이상현상 방지 (수정,삭제,추가) =>  데이터는 중복
        = PRIMARY KEY : UNIQUE+NOT NULL
        = UNIQUE : 중복이 없는 데이터 저장 (NULL 값을 허용)
        = NOT NULL : NULL값을 허용하지 않는다
        = FORIGN KEY : 참조 키 
        = CHECK : 지정된 데이터 첨부
        = DEFAULT : INSERT시 데이터가 추가가 없는 경우
    DML : INSERT, UPDATE, DELETE, MERGE
*/
-- 생성 (테이블 : 데이터를 저장하는 공간)
/*
    문법 사항
    = 알파벳이나 한글로 시작한다 (한글은 윈도우, 리눅스가 한글 표기법이 다르다)
    = 테이블명 (컬럼)은 30byte사용이 가능 (한글 15자)
    = 같은 데이터베이스안에서 (XE) 같은 테이블명을 사용 할 수 없다
           ==========               ======
           폴더                       파일
    = 숫자는 사용이 가능 (앞에 사용 금지) 
    = 특수문자 사용이 가능 ( _ , $) => 단어가 2개이상일떄 _ 사용
    = 키워드는 사용할 수 없다 (사용하지마 말라고 권장하는 것)
    
    
    생성 과정
    CREATE TABLE Table명(
        컬럼명 데이터형, name VARCHAR(34) 제약조건
        컬럼명 데이터형 sex CHAR(4) 제약조건
    )
*/

-- emp (empno(정수), ename(문자), job(문자), mgr(정수), hiredate(날짜), sal(실수), comm(실수), deptno(정수) 
/*CREATE TABLE emp2 (
    empno NUMBER(4),
    ename VARCHAR2(34),
    job VARCHAR2(20), 
    mgr NUMBER(4), 
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(2)
);*/
--DROP TABLE emp2;

--SELECT LENGTHB('홍길동') FROM DUAL;
--CATS => 기존의 테이블을 복사하는 방법

CREATE TABLE emp3 
AS
    SELECT * FROM emp WHERE 1=2; -- 조건이 FALSE 이기 때문에 데이터는 카피가 안되고 테이블 형태만 복사됨
    
SELECT * FROM emp3;
DESC emp3;

CREATE TABLE emp4
AS
    SELECT * FROM emp; -- 조건이 FALSE 이기 때문에 데이터는 카피가 안되고 테이블 형태만 복사됨
    
SELECT * FROM emp4;
DESC emp4;

-- 데이터가 커서 들어가지 않음, 오류 발생코드
INSERT INTO emp4 VALUES(8000, '안녕하세요', 'MANAGER', 7788, SYSDATE, 3000, 100, 10);

-- 테이블의 데이터형 바꿔줌
ALTER TABLE emp4 MODIFY ename VARCHAR(20);

ALTER TABLE emp4 ADD regdate DATE DEFAULT SYSDATE;


ALTER TABLE emp4 DROP COLUMN regdate;
-- 전체 데이터를 삭제 (테이블 유지)
TRUNCATE TABLE emp4;
-- DDL (COMMIT포함)

-- 테이블 이름 변경
RENAME emp4 TO emp5;


ALTER TABLE emp5 RENAME COLUMN hiredate TO regdate;
DROP TABLE emp5;
DROP TABLE emp3;

SELECT 문은 ROW단위로 데이터를 검색하는 DQL 언어였다면 

COLUMN단위로 데이터를 조작하는 DDL언어를 오늘 새로 배웠다. 

데이터 정의언어로, 테이블 단위로 생성하거나 삭제, 컬럼추가 변경 등의 조작을 할 수 있는 언어이다. 

 

-- 2021-02-16 DDL
/*
    DDL 명령어 => column단위, COMMIT => 복구가 어렵다
    = CREATE
        생성 (데이터 저장 공간 : 테이블)
        1. 다른 테이블 복사 
            = 테이블 형식만 복사
            CREATE TABLE table명
            AS
             SELECT * FROM table_name
             WHERE 100=200; (false 조건) ==> JOIN을 걸어서 테이블 여러개의 데이터를 모아서 관리
            = 테이블 데이터까지 복사
            = 사용자 정의 (새로운 테이블을 만든다)
    = ALTER
    = DROP
    = RENAME
    = TRUNCATE
*/
/*CREATE TABLE emp_dept
AS
SELECT emp.*,dname,loc
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND 1=0;

SELECT *
FROM emp_dept;

DESC emp_dept;
*/
-- 테이블 => 데이터 복사
CREATE TABLE emp_dept_grade
AS
SELECT emp.*, dname, loc, grade
FROM emp, dept, salgrade
WHERE emp.deptno=dept.deptno
AND sal BETWEEN losal AND hisal;


SELECT * FROM emp_dept_grade;

-- 컬럼변경

/*
        이름       널?       유형           
        -------- -------- ------------ 
        EMPNO    NOT NULL NUMBER(4)    
        ENAME             VARCHAR2(10) 
        JOB               VARCHAR2(9)  
        MGR               NUMBER(4)    
        HIREDATE          DATE         
        SAL               NUMBER(7,2)  
        COMM              NUMBER(7,2)  
        DEPTNO            NUMBER(2)    
        DNAME             VARCHAR2(14) 
        LOC               VARCHAR2(13) 
        GRADE             NUMBER
*/

--INSERT INTO test VALUES('2');

DESC emp_dept_grade;
/*
    컬럼변경 => 
    형식) ALTER TABLE table_name MODIFY 변경할 컬럼명 데이터형 [제약조건]
    컬럼추가 => 
    형식) ALTER TABLE table_name ADD 추가할 컬럼명 데이터형 [제약조건]
         등록일 추가 => DATE regdate
         이메일 추가 => email VARCHAR2(260)
    컬럼삭제
    컬럼이름변경
    
*/
ALTER TABLE emp_dept_grade MODIFY ename VARCHAR2(51);
ALTER TABLE emp_dept_grade MODIFY deptno NUMBER(3);

ALTER TABLE emp_dept_grade ADD regdate DATE DEFAULT SYSDATE;
ALTER TABLE emp_dept_grade ADD email VARCHAR(260) DEFAULT 'http:\\';


/*
    컬럼삭제
    형식) ALTER TABE table
*/
ALTER TABLE emp_dept_grade DROP COLUMN regdate;

ALTER TABLE emp_dept_grade RENAME COLUMN enmail TO homepgae;
/*
    테이블 이름 변경 
    RENAME emp_dept_grade TO edg;
*/
/*
    데이터 잘라내기
    TRUNCATE TABLE table_name
*/
TRUNCATE TABLE edg;

select * from edg;

/*
    테이블 삭제
    DROP TABLE table_name;
*/

 DROP TABLE edg;


-- 266 page


DELETE FROM emp;
select * from emp;
ROLLBACK;
-- 273 page 테이블 복사 

DESC genie_music;
conn hr/happy

SELECT * FROM genie_music;

DDL 언어의 경우 COMMIT도 포함되어 있어서 한번 실행하면 ROLLBACK으로 되돌릴수가 없다. 주의해서 사용해야한다. 

 

그 다음시간에는 응용으로 자바와 오라클을 연결하는 걸 배웠다. 어려웠다. 

package com.sist.dao;
// 오라클 연결하는 프로그램 
import java.util.*;// ArrayList
import java.sql.*;
public class MusicDAO {
   // 연결하는 클래스 
   private Connection conn;//오라클 연결 
   // SQL문장으르 전송하는 클래스 
   private PreparedStatement ps;
   private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
   //1. 연결 드라이버 설치 => 한번만 사용 (생성자)
   public MusicDAO()
   {
	   try
	   {
		   Class.forName("oracle.jdbc.driver.OracleDriver");
	   }catch(Exception ex){}
   }
   // 2. 연결 
   public void getConnection()
   {
	   try
	   {
		   conn=DriverManager.getConnection(URL,"hr","happy");
		   // 오라클 => conn hr/happy
	   }catch(Exception ex) {}
   }
   // 3. 해제
   public void disConnection()
   {
	   try
	   {
		   if(ps!=null) ps.close();// 통신(송신:OutputStream,수신:BufferReader)
		   if(conn!=null) conn.close();// 연결기기: Socket
	   }catch(Exception ex) {}
   }
   // 4. 기능 => 뮤직데이터 50개를 받아서 저장 ==> 저장된 데이터 브라우저에서 읽어서 출력 
   public ArrayList<MusicVO> musicListData()
   {
	   ArrayList<MusicVO> list=new ArrayList<MusicVO>();
	   try
	   {
		   // 1. 연결 
		   getConnection();
		   // 2. SQL문장을 만든다
		   String sql="SELECT no,title,poster,singer,album,state,idcrement "
				     +"FROM genie_music "
				     +"ORDER BY 1";
		   ps=conn.prepareStatement(sql);// 전송준비 오라클에 문장을 써준것
		   // 3. SQL문장 전송 
		   ResultSet rs=ps.executeQuery(); // run으로 실행시킨 것 
		   // 4. 결과값을 가지고 온다 
		   // 5. ArrayList에 담는다 
		   /*
		    *    =================
		    *     no title...
		    *    =================
		    *     1    ==          | next()
		    *    =================
		    *     2    ==          | next()
		    *    =================
		    *     3    ==          | next()
		    *    =================
		    *     4    ==          | next()
		    *    =================
		    *                      | next() => false
		    *    
		    */
		   while(rs.next())
		   {
			   MusicVO vo=new MusicVO();
			   vo.setNo(rs.getInt(1));
			   vo.setTitle(rs.getString(2));
			   vo.setPoster(rs.getString(3));
			   vo.setSinger(rs.getString(4));
			   vo.setAlbum(rs.getString(5));
			   vo.setState(rs.getString(6));
			   vo.setIdcrement(rs.getInt(7));
			   list.add(vo);
		   }
		   rs.close();
		   // 6. 대기상태 
	   }catch(Exception ex)
	   {
		   ex.printStackTrace();
	   }
	   finally
	   {
		   disConnection(); // 닫기 (오류,정상 => 무조건 오라클을 닫는다)
	   }
	   return list;
   }
   
}