22일차 수업 정리(DB - Join, DDL, DML, Subquery)

 

**Self Join

    => 동일한 테이블을 가지고 Join

    => 하나의 테이블에 동일한 의미를 갖는 열이 2개 이상 존재하는 경우 가능

    => 동일한 의미를 갖는 컬럼을 이용해서 다른 정보를 조회하고자 할 때 사용

    => From절에 같은 이름의 2개의 테이블 설정, 미구분시 이름에 문제 발생(이름 뒤에 별명을 설정하여 테이블 구분)

    => From 절에서 설정한 별명은 이후 모든 절에 적용 되어야 함

    => Select 절의 별명 : 실제 별명이 아니라 출력하기 위한 것으로 별명을 써도되고, 원래 이름을 입력해도 됨

    => From절의 별명 : 데이터를 구분하기 위한 별명으로 이후에는 별명으로 사용

   

<실습 자료>

    => EMP 테이블 구조

        - EMPNO(사원번호) - 정수

        - ENAME(사원 명) - 문자열

        - JOB(담당업무) - 문자열

        - MGR(관리자 번호) - 정수

        - HIREDATE(입사일) - 날짜

        - SAL(급여) - 실수

        - COMM(상여금) - 실수

        - DEPTNO(부서번호) - 정수

 

    =>DEPT 테이블

        - DEPTNO(부서번호) - 정수

        - DNAME(부서명) - 문자열

        - LOC(지역) - 문자열

 

ex) EMP테이블에서 ENAME이 SCOTT인 사원의 관리자 이름을 조회
SELECT  e2.ENAME
FROM   EMP e1, EMP e2
WHERE  e1.ENAME = 'SCOTT' AND e1.MGR = e2.EMPNO;

ex) EMP테이블에서 EMPNO가 7369인 사원의 ENAME과 관리자의 ENAME 그리고 JOB을 조회

 

** ANSI JOIN

    => 미국 표준협회가 제시한 JOIN 문법

1. CROSS JOIN - Cartesian Product

    => 특별한 조인 조건 없이 2개 테이블의 모든 조합을 만들어 내는 것

    ex) SELECT ? FROM 테이블1 CROSS JOIN 테이블2;

    => 열의 갯수는 양쪽 테이블의 열 갯수의 합, 행의 갯수는 양쪽 테이블의 행 갯수의 곱이 됨

 

2. ANSI INNER JOIN

    => 양 쪽 테이블에 동일한 의미를 갖는 열의 값이 양쪽 테이블 모두에 존재하는 경우에만 결합하는 JOIN

    ex) SELECT * FROM 테이블1 INNERJOIN 테이블2 ON 테이블1.열이름 = 테이블2.열이름

         (EMP 테이블과 DEPT테이블의 DEPTNO는 부서번호)

    => 동일한 의미를 갖는 열 이름이 양쪽 테이블에서 동일할시 ON대신 USING 사용 가능(ON대신 USING(공통된 열명)

    => 양쪽 테이블의 공통된 의미를 갖는 열 이름이 같을 경우 INNER JOIN대신에 NATURAL JOIN이라고 설정해서

        공통된 열 이름 생략 가능

        ex) FROM EMP INNER JOIN DEPT USING(DEPTNO); = FROM EMP NATURAL JOIN DEPT;

 

3. ANSI OUTER JOIN

    => OUTER JOIN : 한쪽 테이블에만 존재하는 데이터도 JOIN에 참여

         ex) FROM 테이블1 [LEFT|RIGHT|FULL] OUTER JOIN 테이블2

              ON 테이블1.열이름 = 테이블2.열이름;

         ex) EMP 테이블의 DEPTNO = 10, 20 / DEPT 테이블의 DEPTNO = 10, 20, 40일때 40인 데이터를 조회시 사용

 

<예시>

SELECT *

FROM EMP FULL OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

 

**SET OPERATION

    => 동일한 구조를 갖는 테이블끼리 연산

1. UNION, UNION ALL : 테이블 2개를 합치는 것

2. INTERSACT : 양쪽 테이블에 공통으로 존재하는 행만 추출해서 새로운 테이블을 만드는 것

3. MINUS : 한 쪽 테이블에만 존재하는 행만 추출하여 새로운 테이블 제작

 

**JOIN의 단점

    => JOIN은 2개 테이블의 조합이라 시간이 많이 걸리는 작업이므로 JOIN을 사용하지 않고 해결하는 것이 좋음

 

**SUB QUERY

    => QUERY안에 다른 QUERY가 포함되는 것

    => SQL에서는 SUBQUERY가 반드시 ()안에 포함되어야 함

    => SUB QUERY는 MAIN QUERY가 수행되기 전에 1회만 실행

    => 보통은 SELECT 구문의 WHERE절이나 FROM절에서 사용

1. 단일행 서브쿼리

    => 서브 쿼리의 결과가 하나의 행이라서 단일 행 연산자를 사용할 수 없을 경우

    => 단일행 연산자(=, !=, <=, >=,<, >,

    ex) EMP 테이릅에서 ENAME이 SCOTT인 사원과 동일한 LOC에 근무중인 사원 ENAME과 SAL값을 조회

         (ENAME과 SAL은 EMP 테이블에 존재, LOC는 DEPT테이블에 존재

<예시>

SELECT ENAME, SAL FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME = 'COTT'

SELECT LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME = 'SCOTT';


ex) SCOTT인 사원과 부서번호가 동일한 부서 번호를 가진 사원의 ENAME과 SAL를 조회

SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT');AND

--SCOTT 빼기
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT') AND ENAME != 'SCOTT';


ex2) EMP 테이블에서 DEPT 테이블의 LOC가 DALLAS인 사원의 ENAME과 DEPTNO를 조회
    => 여러 테이블의 정보를 이용하더라도 조회하고자 하는 열이 하나의 테이블에 전부 존재한다면 서브 쿼리로 해결 가능

SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS';


ex3) EMP 테이블에서 SAL의 값이 ENAME이 MILLER사원의 SAL이상인 데이터의 ENAME과 SAL을 조회

SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT SAL FROM EMP WHERE ENAME = 'MILLER');


ex4) EMP 테이블에서 SAL값이 평균이상인 데이터의 ENAME과 SAL을 조회

SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT AVG(SAL) FROM EMP);

 

ex5) DNAME이 SALES인 사원의 ENAME과 JOB, SAL을 조회

    => DNAME은 DEPT 테이블에 존재하고, ENAME과 JOB, SAL은 EMP 테이블에 존재

    => 테이블 이름이 2개가 나오면 JOIN이나 SUB QUERY로 해결

    => 조회하고자 하는 컬럼이 하나의 테이블에 존재하면 SUB QUERY로 해결 가능

    => 조회하고자 하는 컬럼이 두개의 테이블에 나누어 존재하면 JOIN으로 해결

    => 2개의 테이블을 가지고 작업시 동일한 의미를 갖는 컬럼을 이용해야 함

SELECT ENAME, JOB, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

 

ex6) ENAME이 SCOTT인 사원과 동일한 LOC에 근무하는 사원의 ENAME과 SAL의 값을 조회

    => LOC는 DEPT테이블에 존재

    => EMP 테이블과 DEPT 테이블은 DEPTNO를 같이 소유

SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = (SELECT LOC FROM DEPT WHERE DEPTNO =
(SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT'))) AND ENAME != 'SCOTT';

 

2. 다중행 서브쿼리

    => 서브쿼리의 결과가 0개나 2개 이상이어서 단일 행 연산자로 비교가 불가능한 서브쿼리

    => IN, NOT IN, ANY, ALL등의 다중 행 연산자를 사용

    => 아래와 같이 작성시 DEPTNO가 2개 리턴되어 =로 비교할 수 없다고 에러 발생

ex) LOC가 DALLAS 이거나 CHICAGO인 곳에서 근무하는 사원들의 ENAME과 JOB을 조회

SELECT ENAME, JOB
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS' OR LOC = 'CHICAGO');
    => 서브쿼리를 이용하여 '='나 '!='로 비교시, 2개 이상의 행이 리턴되면 '=' 대신에 'IN', '!='대신에 'NOT IN'을 이용

 

ex2) EMP 테이블에서 DEPTNO가 30인 사원들 전체보다 SAL 이 더 많은 사원의 ENAME과 SAL을 조회

    => 서브쿼리의 결과가 2개 이상이라 에러 발생

    => ALL이나 ANY 대신에 MAX나 MIN 함수를 써도 가능

SELECT ENAME, SAL
FROM EMP
--WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30); --같은 결과
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

 

**DDL(Data Definition Language)

    => 데이터 구조와 관련된 SQL - 관리자의 언어

1. 오라클의 기본 자료형

  1) 숫자 : number(전체 자리수, 소수 자릿수)

    => 소수 자릿수 생략시 0

    => 전체 자릿수 생략시 38

  2) 문자열

    => char(자릿수) : 고정된 크기의 문자열

        - 문자열의 남는 부분이 공백으로 저장될 수 있어 .trim()이 필요

    => varchar2(자릿수) : 크기가 변하는 문자열

        - 데이터 크기에 변경 발생시 Row migration발생

        - 데이터의 길이가 변경되지 않을 문자열을 사용(ex - ID, 전화번호 등)

    => CLOB : 긴문자열(인덱스 적용 불가)

        - 빠르게 찾을 수는 없지만 긴 문자열 저장 가능(ex - 서평)

    => 한글 1글자는 3자리를 확보해야 함

    => 자릿수는 한번 정하면 자릿수 이상의 데이터는 저장이 안됨

  3) 날짜 및 시간

    => date : 1/1000초

    => timestamp : 1/1000000초

  4) 파일의 내용

    => BLOB(이미지등)

 

2. 테이블 생성

    CREATE TABLE 테이블명( 열이름 자료형 제약조건, ... );

  ex1) 게시판 테이블

    => 글번호 - 정수

    => 제목 - 문자열

    => 작성자 - 문자열

    => 작성일 - 날짜

    => 조회수 - 정수

 

CREATE TABLE board(

boardnum number(10),

boardtitle varchar2(300),

boardwriter varchar2(30),

boarddate date,

boardreadercnt number(10));

 

  ex2) 서평 게시판

    => 책제목 - 문자열

    => 저자 - 문자열

    => 출판사 - 문자열

    => 작성자 - 문자열

    => 작성 일자 - 날짜

    => 서평 - 문자열(긴거)

 

CREATE TABLE bookboard(

booktitle varchar2(300),

bookwriter varchar2(30),

bookcom varchar2(30),

writer varchar2(30),

writedate date,

bookpreview clob);

 

ex2) emp 테이블의 데이터를 복사해서 새로운 emp01테이블을 생성

CREATE TABLE EMP01
AS
SELECT *
FROM EMP


ex3) EMP테이블의 구조만 복사해서 EMP02 테이블 생성
    => 구조만 복사하고자 할때는 WHERE절에 무조건 거짓인 조건을 설정하여 데이터가 1개도 조회되지 않게 하면 됨

CREATE TABLE EMP02
AS
SELECT *
FROM EMP
WHERE 1=0;

 

3. 테이블의 구조 변경

    => 테이블에 열을 추가, 삭제, 자료형을 변경하는 것

  1) 열 추가

     ALTER TABLE 테이블명

     ADD(컬럼명 자료형 제약조건);

  2) 열의 자료형 변경

     ALTER TABLE 테이블명

     MODIFY(컬럼명 자료형 제약조건);

    => 기존의 데이터가 존재하는 경우 자료형이나 크기를 변경하는 것은 기존 데이터에 손실이 발생

  3) 열 삭제

     ALTER TABLE 테이블명

     DROP COLUMN 열이름;

    => 문법적으로 이상이 없는 경우에도 삭제가 안되는 문제(타 테이블에서 이열의 값을 참조하는 경우)

 

ex1) 기존 board 테이블에 문자 한글100자를 저장 할 수 있는 boardcontent 라는 열을 추가

ALTER TABLE BOARD
ADD(BOARDCONTENT VARCHAR2(300));


ex2) 기존 board 테이블의 boardcontent 라는 열의 자료형을 char(300)로 수정

alter table board
modify(boardcontent clob);

 
ex3) board 테이블에서 boardcontent 열을 삭제

alter table board
drop column boardcontent;

 

  4) 서비스 중 열을 삭제하는 경우

    => 데이터베이스가 서비스되고 있는 상태에서 열 삭제는 권장하지 않음

      (데이터베이스에 데이터 삽입, 삭제, 갱신 중에는 lock이 설정되어 다른 곳에서 삽입, 삭제, 갱신 작업을 수행불가)

    => 이런 경우 데이터 베이스의 열을 사용하지 못하게 하고, 한가해지면 그 때 삭제하는 방법을 권장

    => 사용 중지

        ALTER TABLE 테이블 명

        SET UNUSED(열이름)

    => 중지중인 열 삭제

        ALTER TABLE 테이블 명

        DROP UNUSED COLUMNS;

 

4. 테이블을 삭제

    => DROP TABLE 테이블 명

    => 삭제가 안되는 경우 : 이 테이블의 데이터를 다른 테이블에서 참조하는 경우

    => DROP TABLE BOARD;

    => 오류 : table or view does not exist : 테이블이나 뷰가 없어서 발생

 

5. 테이블에서 데이터만 삭제

    => 구조는 남겨두고 데이터만 삭제

    => TRUNCATE TABLE 테이블 명

    ex) EMP01 테이블의 데이터를 삭제(TRUNCATE TABLE EMP01;)

 

6. 테이블 이름 변경

    => RENAME 예전이름 TO 새로운 이름;

 

**DML - 데이터 조작 언어

    => 데이터를 삽입, 삭제, 갱신하는 명령어

1. 샘플 테이블 생성

    => 여자 배구 선수 정보 저장

        - 번호 : 정수 3자리

        - 이름 : 한글 물자열 10자리 - 변하지 않음

        - 소속팀 : 한글 문자열 12자리 -변할 수 있음

        - 연봉 : 정수 5자리

        - 데뷔연도 : 정수 4자리

 

        - 학번 : 정수 8자리

        - 이름 : 한글 물자열 10자리 - 변하지 않음

        - 학과 : 한글 문자열 20자리 - 변할 수 있음

        - 학년 : 정수 1자리

        - 전화번호 : 숫자, 특수문자 문자열 20자리

        - 입학년도 : 정수 4자리

 

CREATE TABLE STUDENT(

num varchar2(8),

name varchar2(30),

depart char(60),

grade number(1),

phonenum varchar2(20),

entranceyear number(4));

 

2. 데이터 삽입

  1) 컬럼 이름을 기재해서 삽입

     insert into 테이블 명(열 이름 나열)

     values(값을 나열)

    => 열 갯수와 값의 갯수가 일치해야 함

     ex) 20103411, 최성권, 정보보호학과, 4, 010-0000-0000, 2010

 

  2) 열 이름을 생략하고 삽입

    => 테이블을 만들때 사용한 순서대로 데이터를 대입

     insert into 테이블 명

     values(값을 나열)

  3) null 삽입

    => 값에 직접 null이라고 입력해도 되고 ''만 해도 됨

    => 데이터를 삽일 할 때 열 이름을 제외하고 삽입시, 기본값이 삽입됨

INSERT INTO student

values(15001100, '장성채', '', 4, null, '1500');

 

3. 데이터 수정 - update

update 테이블 명

set 수정할 열 이름 = 계산식이나 값, ...

where 조건;

    => where 생략시 테이블의 모든 데이터가 수정

  ex1) name이 최성권인 데이터의 학과를 게임공학과로 수정

update student

set depart = '게임공학과'

where name = '최성권';

 

4. 데이터 삭제 - delete

delete from 테이블명

where 조건;

    => 조건이 있으면 맞는 데이터만 삭제, 없으면 전체 삭제

    ex1) num이 2000보다 작은 학생을 삭제

DELETE student

WHERE grade < 2;

    ex2) 테이블의 모든 데이터 삭제

DELETE student;