[개발 개념] SQLD Oracle 정리

SQLD 2장 정리

SQLD 정리

---

• 데이터 형
  • NUMBER
    • NUMBER 데이터형은 숫자 데이터를 저장하기 위해서 제공됨
    • NUMBER(precision, scale) 형식을 가지고있음
    • precision은 소수점을 포함한 전체 자리수를 의미하며, scale은 소수점 이하 자리수를 지정함
  • DATE
    • DATE는 세기, 년, 월, 일, 시간, 분, 초의 날짜 및 시간 데이터를 저장하기 위한 데이터 형
    • 기본 날짜 형식은 “YY/MM/DD” 형식을 가짐
  • CHAR
    • 문자 데이터를 저장하기 위한 자료형
    • CHAR는 고정 길이 문자 데이터를 저장함
    • 입력된 자료의 길이와는 상관없이 정해진 길이만큼 저장 영역을 차지하며 최소 크기는 1
  • VARCHAR2
    • VARCHAR2 데이터 형은 가변적인 길이의 문자열을 저장하기 위해서 제공
    • VARCHAR2는 저장되는 데이터에 의해서 저장공간이 할당되므로 메모리 낭비를 줄일 수 있음
• SELECT 문
  • SELECT 문은 데이터를 조회하기 위한 SQL 명령문
  • 다음은 부서 테이블의 내용을 살펴보기 위한 쿼리문

  SELECT * FROM dept;
  

  SELECT 다음에 *를 입력하면 지정된 dept 테이블의 모든 컬럼을 조회함
  • 다음 명령어를 입력하여 특정 데이터만 볼 수 있음

  SELECT empno, ename
  from emp
  

  • 급여로 사원이름, 월급, 연봉을 구하는 식은 산술 연산자를 통해 구할 수 있음’

  SELECT ename, sal, sal*12
  FROM emp;
  

• NULL
  • NULL의 정의
    • 0 이 아니다
    • 빈 공간이 아니다
    • 미확정, 알 수 없는 값을 의미함
    • 어떤 값인지 알 수 없지만 어떤 값이 존재하고 있다
    • 널은 ? 혹은 무한대의 의미이기 때문에 연산, 할당, 비교가 불가능하다
    • 만약 산술식을 사용하여 어떤값 + NULL값을 계산한다면 NULL 값이 답이 된다
• AS로 컬럼에 별칭 부여하기
  • 컬럼 이름대신 별칭을 출력하고자 하면 컬럼을 기술한 바로 뒤에 AS라는 키워드를 쓴 후 별칭을 입력한다

  SELECT ename, sal*12+nvl(comm+0) as Annsal
  from emp;
  

  • 별칭을 부여할때 대소문자를 섞어서 기술하거나 띄어쓰기를 하고싶은 경우 다음과 같이 명령문을 사용한다

  select ename, sal*12+nvl(comm,0) "A n n s a l"
  from emp;
  

  위와 같이 “”를 사용한경우 한글도 사용 가능하다
• Concatenation 연산자
  • Concatenation 연산자를 사용하면 컬럼과 특정 값 사이에 공백이 생긴다

  select ename || 'is a' || job
  from emp;
  

• DISTINCT 키워드
  • SELECT를 사용할때 중복을 제거하기 위해서 DISTINCT를 사용한다

  select distinct deptno
  from emp;
  

• WHERE 조건과 비교 연산자
  • WHERE 문을 사용하면 조건에 맞는 데이터를 검색할 수 있다

  SELECT *
  FROM EMP
  WHERE DEPTNO=10;
  

  사원번호 10에 해당하는 모든 컬럼의 정보를 가져온다알맞는 컬럼과 테이블 조건을 입력해준다

  SELECT *
  FROM EMP
  WHERE SAL BETWEEN 2000 AND 3000;
  

  급여가 2000 이상 3000 이하이면 검색

  SELECT *
  FROM EMP
  WHERE COMM IN(300,500,1400);
  

  커미션이 300, 500, 1400 이면 검색

  SELECT *
  FROM EMP
  WHERE COMM NOT IN(300,500,1400);
  

  커미션이 300,500,1400이 모두 아닌경우 검색

  SELECT *
  FROM EMP
  WHERE SAL NOT BETWEEN 2000 AND 3000;
  

  급여가 2000 미만이거나 3000 초과인 사원
  • 날짜 테이터 조회

  SELECT * 
  FROM EMP
  WHERE HIREDATE<='1982/01/01';
  

  • AND, OR, NOT 같은 논리 연산자를 사용하여 검색할 수 있다
    • AND → 둘다 참이면 참

    SELECT *
    FROM EMP
    WHERE DEPTNO=10 AND JOB='MANAGER';
    

    사원번호가 10이고 직업이 매니저이면 검색됨
    • OR → 하나만 참이면 참

    SELECT *
    FROM EMP
    WHERE DEPTNO=10 OR JOB='MANAGER';
    

    부서가 10번이거나 직업이 매니저이면 검색
    • NOT → 참이면 거짓, 거짓이면 참

    SELECT *
    FROM EMP
    WHERE NOT DEPTNO=10;
    

    사원번호가 10이 아니면 검색
• SELECT * FROM EMP WHERE SAL <2000 OR SAL > 3000;
• SELECT * FROM EMP WHERE COMM<>300 AND COMM<>500 AND COMM<>1400;
• SELECT * FROM EMP WHERE COMM=300 OR COMM=500 OR COMM=1400;
• SELECT * FROM EMP WHERE SAL >= 2000 AND SAL <= 3000;
• SELECT EMPNO, ENAME, SAL FROM EMP WHERE ENAME='FORD';
• 와일드카드 (%) 사용하기
  • 찾으려는 이름이 F로 시작하는 것은 알지만 그 뒤의 문자는 모를 경우 %를 사용하여 검색

  SELECT *
  FROM EMP
  WHERE ENAME LIKE'F%';
  

  • 이름 중 A를 포함하는 모든 사원을 검색

  SELECT *
  FROM EMP
  WHERE ENAME LIKE '%A%';
  

  • N으로 끝나는 모든 사원

  SELECT *
  FROM EMP 
  WHERE ENAME LIKE '%N';
  

  • 이름의 두 번째 글자가 A인 사원

  SELECT *
  FROM EMP
  WHERE ENAME LIKE '_A%';
  

  • 이름의 세 번째 글자가 A인 사원

  SELECT *
  FROM EMP
  WHERE ENAME LIKE '__A%';
  

  • 이름에 A를 포함하지 않는 사원을 검색

  SELECT *
  FROM EMP
  WHERE ENAME NOT LIKE '%A%';
  

• NULL을 위한 연산자
  • 커미션을 받지 않는 즉 커미션 컬럼에 NULL이 저장되어 있는 사원 검색

  SELECT *
  FROM EMP
  WHERE COMM=NULL;
  

  SELECT *
  FROM EMP
  WHERE COMM IS NULL;
  

  • 커미션 컬럼이 NULL 이 아닌 사원

  SELECT *
  FROM EMP
  WHERE COMM IS NOT NULL;
  

• 정렬
  • 오름차순

  SELECT *
  FROM EMP
  ORDER BY SAL ASC;
  

  SELECT *
  FROM EMP
  ORDER BY ASC;
  

  • 내림차순

  SELECT *
  FROM EMP
  ORDER BY SAL DESC;
  

  • 문자순으로 정렬

  SELECT *
  FROM EMP
  ORDER BY ENAME;
  

  • 날짜 오름차순으로 정렬 (오래된 시점부터)

  SELECT *
  FROM EMP
  ORDER BY HIREDATE DESC;
  

  • 다향한 정렬

  SELECT *
  FROM EMP
  ORDER BY SAL DESC, ENAME ASC;
  

• 조인
  • 조인의 필요성
    • 만약 원하는 정보가 두개 이상의 테이블에 나누어져 있다면 많아지면 많아질수록 지금까지 배운 SQL문으로는 한계가 있다
  • CROSS Join
    • 특별한 키워드 없이 SELECT 문의 FROM 절에 사원 부서 테이블을 콤마로 연결하여 연속하여 기술

    SELECT *
    FROM EMP, DEPT
    

    Cross Join의 결과를 보면 사원 테이블에 부서에 대한 상세정보가 결합되긴 했지만, 조인 될때 아무런 조건을 제시하지 않았기 때문에 사원 한명에 대해서 DEPT 테이블의 로우와 결합된 형태이기에 Cross Join의 결과는 아무런 의미를 갖지 못한다
  • 조인 조건
    • Equi Join
      • 동일 컬럼을 기준으로 조인함
      • 다음은 사원 정보를 출력할 때 각 사원들이 소속된 부서의 상세 정보를 출력하기 위해서 두개의 테이블을 조인함

      SELECT *
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO
      

      EMP, DEPT 테이블의 공통 컬럼인 DEPTNO의 값이 일치되는 조건을 WHERE 절에 기술하여 사용함AND를 사용하여 이름이 SCOTT인 사원을 찾아냄위와 같이 동일한 이름의 칼럼을 사용하면 테이블 소속이 불확실하기 때문에 애매모호한 상태라는 오류 메시지가 출력된다위와 같이 컬럼 앞에 테이블 명을 명시하면 값을 구할 수 있음위와 같이 별칭을 사용하여 검색할 수 있음
    • SELECT E.ENAME, D.DNAME, E.DEPTNO, D.DEPTNO FROM EMP E , DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME = 'SCOTT';
    • SELECT EMP.ENAME, DEPT.DNAME, EMP.DEPTNO FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DETPNO AND ENAME = 'SCOTT';
    • SELECT ENAME, DNAME, DEPTNO FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME='SCOTT';
    • SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME='SCOTT';
    • Non-Equi Join
      • 동일 컬럼이 없이 다른 조건을 사용하여 조인함
      • Non-Equi Join은 조인 조건에 특정 범위 내에 있는지를 조사하기 위해서 WHERE 절에 조인 조건을 = 연산자 이외의 비교 연산자를 사용함

      SELECT ENAME, SAL, GRADE
      FROM EMP, SALGRADE
      WHERE SAL BETWEEN LOSAL AND HISAL;
      

      급여 등급을 5개로 나누어 SALGRADE에서 정보를 얻어 와서 각 사원의 급여 등급을 지정해보도록 함. 이를 위해서 EMP 테이블과 SALGRADE 테이블을 조인하도록 함. 다음으로 사원의 급여가 몇 등급인지 살펴볼 수 있다
    • Self Join
      • 한 테이블 내에서 조인함
      • 조인은 두 개 이상의 서로 다른 테이블을 서로 연결하는 것뿐만 아니라, 하나의 테이블 내에서 조인을 해야만 원하는 자료를 얻는 경우가 생김
      • 말 그대로 자기 자신과 조인을 맺는 것을 말함

      SELECT EMPLOYEE.ENAME || ‘의 매니저는 ’
      	|| MANAGER.ENAME || ‘입니다.’ 
      FROM EMP EMPLOYEE, EMP MANAGER
      WHERE EMPLOYEE.MGR = MANAGER.EMPNO;
      

      • Outer Join
        • 조인 조건에 만족하지 않는 행도 나타냄
        • 조인 조건에 만족하지 못하였더라도 해당 로우를 나타내고 싶을 때에 사용하는 것이 외부 조인 (Outer Join)
        • 외부 조인은 NULL 값이기에 배제된 행을 결과에 포함시킬 수 있으며 다음과 같이 “(+)” 기호를 조인 조건에서 정보가 부족한 칼럼 이름 뒤에 덧붙임
        • 다음 예제는 EMPNO가 NULL인 사원은 없으므로 manager.empno 뒤에 “(+)”를 덧붙임

        SELECT EMPLOYEE.ENAME || '의 매지저는'
        	|| MANAGER.ENAME || '입니다'
        FROM EMP EMPLOYEE, EMP MANAGER
        WHERE EMPLOYEE.MGR = MANAGER.EMPNO(+);
        

        위와 같이 사용하면 SELF 조인에서 조건에 맞지 않아 배제됐던 내용이 추가됨
    • ANSI Cross Join
      • 크로스 조인은 테이블 상호간에 연결될 수 있는 모든 경우의 수를 산출하여 나타내는 조인

      SELECT *
      FROM EMP CROSS JOIN DEPT;
      

    • ANSI Inner Join
      • ON
        • FROM 다음에 ANSI Inner Join이란 단어를 사용하여 조인할 테이블 이름을 명시하고 ON 절을 사용하여 조인 조건을 명시함

        SELECT ENAME, DNAME
        FROM EMP INNER JOIN DEPT
        ON EMP.DEPTNO = DEPT.DEPTNO
        

      • USING
        • 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING 절에서 조인할 컬럼을 지정하여 구문을 간단히 나타냄

        SELECT EMP.ENAME, DEPT.DNAME
        FROM EMP INNER JOIN DEPT
        USING (DEPTNO);
        

      • NATURAL Join
        • NATURAL JOIN을 사용하여 조건 없이 사용 가능함
        • EMP와 DEPT에 DEPTNO라는 같은 이름의 컬럼이 있기 때문에 다음과 같이 편하게 사용 가능

        SELECT EMP.ENAME, DEPT.DNAME
        FROM EMP NATURAL JOIN DEPT; 
        

      • LEFT/RIGHT OUTER JOIN
        • OUTER JOIN은 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른 쪽 테이블에는 데이터가 존재하지 않을 경우 강제로 데이터를 출력시키는 방법이다
        • 다음과 같은 명령어로 지정한 방향의 데이터를 출력할 수 있다

        SELECT *
        FROM DEPT01 LEFT OUTER JOIN DEPT02
        USING(DEPTNO);
        

        SELECT *
        FROM DEPT01 RIGHT OUTER JOIN DEPT02
        USING(DEPTNO);
        

        • 양 방향을 모두 OUTER JOIN 처리할 수 있는 FULL OUTER JOIN

        SELECT *
        FROM DEPT01 FULL OUTER JOIN DEPT02
        USING(DEPTNO);
        

• 서브 쿼리
  • 서브 쿼리는 하나의 SELECT 문자의 절 안에 포함된 또 하나의 SELECT 문장
  • 그룹 함수를 사용한 단일 행 서브 쿼리

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

  평균 급여보다 더 많이 받는 사원을 검색
  • 다중 행 서브 쿼리
    • IN
      • 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 참

      SELECT ENAME, SAL, DEPTNO
      FROM EMP
      WHERE DEPTNO IN (SELECT DISTINCT DETPNO 
      									FROM EMP WHERE SAL >= 3000);
      

    • ANY,SOME
      • 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나이상 일치하면 참

      SELECT ENAME , SAL
      FROM EMP
      WHERE SAL > ANY (SELECT SAL
      									FROM EMP
      									WHERE DPEPTNO =30);
      

    • ALL
      • 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참

      SELECT ENAME, SAL
      FROM EMP
      WHERE SAL > ALL (SELECT SAL  FROM EMP
      										WHERE DEPTNO = 30);
      

    • EXIST
      • 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참
• CREATE
• CREATE TABLE EMP01( EMPNO NUMBER(4), ENAME VARCHAR2(20), SAL NUMBER(7,2));
• ALTER
  • ALTER TABLE 명령문은 기존 테이블의 구조를 변경하기 위한 DDL 명령문
    • ADD → 새로운 칼럼 추가

    ALTER TABLE EMP01
    ADD (JOB VARCHAR2(9));
    

    • MODIFY → 기존 칼럼을 수정

    ALTER TABLE EMP01
    MODIFY(JOB VARCHAR2(30));
    

    • DROP → 기존 칼럼을 삭제

    ALTER TABLE EMP01
    DROP COLUMN JOB;
    

    • SET UNUSED → 논리적으로 사용 제한

    ALTER TABLE EMP02
    SET UNUSED(JOB);
    

• TRUNCATE → 기존에 사용하던 테이블의 모든 로우를 제거

TRUNCATE TABLE EMP02;

• RENAME → 기존에 사용하던 테이블의 이름을 변경하기 위한 명령어

RENAME EMP02 TO TEST;

• 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 다음과 같이 출력

SELECT TOP(2) WITH TIES ENAMES, SAL FROM EMP
ORDER BY SAL DESC;

• GROUP BY 절 특성
  • GROUP BY 절을 통해 소그룹별 기준을 정한 후 SELELCT 절에 집계 함수를 사용
  • 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다
  • GROUP BY 절에서는 SELECT절과는 달리 ALIAS 명을 사용할 수 있다
  • 집계 함수는 WHERE 에는 올 수 없다
  • HAVING절은 기준 항목이나 소그룹 집계 함수를 이용한 조건을 표시할 수 있다