[DB] JOIN, DCL, 테이블 복사

JOIN

• 테이블 조인: 테이블의 컬럼을 합하는 연산 (참고로 UNION 연산은 테이블의 행을 합하는 연산)

• 조회 테이블이 너무 많이 쪼개져있으면 느리기에 입력,수정, 삭제의 성능을 향상시키기 위해 사용

• 내부조인(INER JOIN)

  • 조인1(크로스 조인) : SELECT * FROM TBL_CUSTOM, TBL_BUY;
    • 첫번째 테이블의 행에 BUY를 하나씩 다 넣음 (4X7=28): 모든 경우의 수
    • 결과: 2개의 테이블 공통 컬럼인 CUSTOM_ID로 모든 경우의 수를 조합하여 합침
  • 조인2(동등 조인) : 조건식을 추가하여 컬럼 값이 같은 것만 조회

    1)SELECT * FROM 테이블1 , 테이블 2 WHERE T1.공통컬럼 = T2.공통컬럼;
    2)SELECT * FROM 테이블1 JOIN 테이블 2 ON T1.공통컬럼 = T2.공통컬럼;
    -------------------------------------------------------------------------------------
    -1
    SELECT * FROM TBL_CUSTOM tc ,TBL_BUY tb         --참고: 고객 중 한번도 구매하지 않은 이는 결과에 없음
    WHERE TC.CUSTOM_ID = TB.CUSTOMID;
    
    -2
    SELECT * FROM TBL_CUSTOM tc JOIN TBL_BUY tb
    ON TC.CUSTOM_ID = TB.CUSTOMID;

  • ON절의 조건은 JOIN이 되면서 실행되고 WHERE절의 조건은 JOIN이 모두 끝난 후 실행
    • 등가조인: ON 절에 등호가 있을때
    • 비등가조인:ON ON 절에 등호가 없을때
  • ON과 WHERE를 같이 사용할때와 ON만 사용할때의 결과가 같다면 ON만 사용하는 것이 좋음
  • USING: 중복되는 컬럼이 생길 시 맨앞으로 출력하여 중복 컬럼을 한개만 출력함
    • 컬럼 이름 뿐만 아니라 타입도 같아야함
    • 내부조인에서는 ON대신 USING으로 중복제거 가능

      SELECT*FROM TBL INERJOIN TBL2
      USING(NUM); ->NUM 겹치는 컬럼
      
      SELECT*FROM TBL INERJOIN TBL2
      ON TBL.NUM = TBL2. NUM;                    -> 둘 다 NUM이 중복되지 않은 결과로 나옴

  • 네츄럴조인(NATURAL JOIN) - 중복 알아서 제거
    • 코드

      SELECT*FROM TBL NATURAL JOIN TBL2;

• 외부조인(OUTER JOIN)

  • 두개의 테이블 중 조건이 거짓(혹은 NULL)이라도 지정한 테이블의 모든 정보가 검색되어야 할 때 사용
    • LEFT OUTER JOIN (왼쪽 테이블에 있는 정보는 다 가져옴)

      SELECT * FROM STADIUM LEFT OUTER JOIN TEAM
      ON HOMETEAM_ID = TEAM_ID;

    • RIGHT OUTER JOIN (오른쪽 테이블에 있는 정보 다 가져옴)
    • FULL OUTER JOIN ( 두 테이블에 있는 정보 다 가져옴)

• SELF JOIN

  • 하나의 테이블 내에서 공통된 값을 지닌 컬럼끼리 연결을 해서 정보를 찾음
  • 예시

    • EMP TABLE
      

      SELF JOIN
      SELECT * EMP E1 JOIN EMP E2
      ON E1.MGR =E2.EMPNO
      --E1에서 사원의 매니저 번호를 찾아서(MGR) E2에서 매니저를 찾음(EMPNO)

      

          SELECT E1.ENAME 사원, E2.NAME 매니저 EMP E1 JOIN EMP E2
          ONT E1.MGR =E2.EMPNO;
         
          

• 결과

DCL(DATA CONTROL LANGUAGE)

• DCL(DATA CONTROL LANGUAGE) 데이터 제어어
  • -GRANT: 권한 부여
    • GRANT 권한명 TO USER_NAME;
    • 코드
      • 유저만들기: grant create user to USER_NAME;
      • 권한주기: grant greate session to USER_NAME;
      • 유저의 기본 테이블 스페이스 설정: alter user baby default tablespace BABY;
        (유저 baby의 기본테이블을 BABY로 설정함)
      • 임시 스페이스 설정: alter user baby temporary tablespace TEMP;
      • 할당량 설정: alter user baby default tablespace BABY QUOTA unlimited on baby; (할당량 제한 없음)
      • 테이블 만들기 권한: grant create table to USER_NAME;
      • 복사 및 검색 권한: grant select on 테이블명 to 유저명;
      • 수정권한: grant update on 테이블명 to USER_NAME;
  • REVOKE: 권한 강탈
    • REVOKE 권한명 FROM USER_NAME;
  • A 계정의 테이블에 DML 사용을 위해서는 A계정에게 권한을 부여받아야 함
  • 권한의 집합체: 롤(ROLE)
    • CONNECT, RESOURCE ,DBA(거의 모든 권한)
      • grant connect to USER_NAME;
      • grant resource to USER_NAME;
• 12345를 54321로 바꾸고 싶다거나... 숫자를 통한 공통점 찾아내기
  • SET "LEVEL" = 6+"LEVEL";

테이블 복사

• 테이블 구조 복사
  • 기존 테이블의 필드 설정 그대로 복사 된다.
    단, 기존 테이블에 'Primary Key' 또는 'auto_increment' 가 설정 되어 있으면 복사 불가
    

    Create Table new_table like old_table
    Create Table IF NOT EXISTS new_table like old_table -- (new_table 이 없으면 복사)Copy

    

• 테이블 데이터 복사

  • 대상 테이블의 데이터만 복사하는 경우
    대상 테이블의 컬럼 중에 'auto_increment' 설정 이 된 컬럼이 있을 경우 해당 컬럼에 데이터 입력시 중복된 데이터가 있으면 오류 발생.
    

    sqlinsert into 테이블명1 (select * from 테이블명2)
    -- value()대신 서브쿼리가 들어감
    Insert Into destination_table (column_a, column_b) (select a, b from source_table)
    -- 원하는 필드의 부분 데이터만 복사가 가능Copy
  
    

• 테이블 구조와 데이터 복사
  • 테이블의 구조와 함께 데이터도 함께 복사가 된다.
    기존 테이블에 'Primary Key' 또는 'auto_increment' 가 설정 되어 있으면 해당 설정은 적용 되지 않고 값만 복사

    create table 만들 테이블명 as select * from 계정명.테이블명;
    
    create table buyTbl2 (select * from buyTbl) -- 서브쿼리를 쓰거나
    create table buyTbl2 as select * from buyTbl -- 괄호 대신 as 써도 된다

• 테이블 생성 쿼리 얻기
  • 테이블을 복사하는데 있어 섬세하게 부분 수정이 필요한 경우, 아예 복사할 테이블 구조를 생성하는 쿼리를 얻을 수 있음

    SHOW CREATE TABLE 대상테이블명; -- 실행하면 결과로 create table 대상테이블명 쿼리문을 얻을 수 있음