Major/Database

데이터베이스 - SQL

안중환 2015. 11. 25. 17:05
  • SQL?
     : Structured Query Language
     : 사용자와 데이터베이스 시스템 간에 의사소통을 하기 위한 언어

  • SQL 유형별 종류
     
     
     

     - DDL(Data Definition Language)
      1. 테이블 이름과 칼럼은 A~Z까지의 문자, 0~9까지의 숫자, 그리고 $,#,_(Under Bar)를 사용할 수 있다. 그러나 공백은 사용할 수 없다.
       2. 테이블의 칼럼은 30자를 초과할 수 없고, 예약어를 사용할 수 없다.
       3. 한 테이블 안에서 칼럼 이름은 같을 수 없으며 다른 테이블에서의 칼럼 이름과는 같을 수 있다.

      ■ CREATE
    CREATE TABLE 테이블이름
( {열이름 데이터타입 [NOT NULL] [DEFALUT 값], }+
[PRIMARY KEY (열이름_리스트), ]
{[UNIQUE (열이름_리스트),]}*
{[FOREIGN KEY(열이름_리스트) 
 REFERENCES 기본테이블[(열이름_리스트)]
[ON DELETE 옵션]
[ON UPDATE 옵션],]}*
[CONSTRAINT 이름][CHECK(조건식)]);
    NOT NULL: NULL을 허용하지 않음
    UNIQUE: 중복된 값을 허용하지 않고, 항상 유일한 값을 갖도록 함 
    PRIMARY KEY     : NULL을 허용하지 않음. 중복된 값을 허용하지 않음, NOT NULL 조건과 UNIQUE 조건을 결합한 형태 
    UNIQUE: 조건을 결합한 형태
    FOREIGN KEY: 참조되는 테이블의 칼럼의 값이 존재하면 허용
    CHECK: 저장 가능한 데이터 값의 범위나 조건을 지정해 설정한 값만을 허용

      ■ ALTER
    ALTER TABLE 테이블명
([ADD 칼럼명 데이터_타입] [DEFAULT 값] |
 [DROP 칼럼명] [CASCADE] |
 [ALTER 칼럼명 (DROP DEFAULT | SET DEFAULT 값)]);

      ■ DROP
    DROP TABLE 테이블명
     
     - DML(Data Manipulation Language)
     
      ■ SELECT
     
    SELECT [DISTINCT] {*, 열_리스트}
FROM {테이블_리스트}
[WHERE 조건]
[ORDER BY 칼럼명 ASC | DESC]
[GROUP BY 칼럼명
 HAVING 조건]


      












     - 집계 함수 (출처: http://www.gurubee.net/)

    COUNT

    : COUNT 함수는 검색된 행의 수를 반환 한다.

    ?
    1
    2
    3
    4
    5
    6
    -- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다.
    SELECT COUNT(deptno) FROM dept;
     
    COUNT(DEPTNO)
    -------------
                4

    MAX

    : MAX 함수는 컬럼값 중에서 최대값을 반환 한다.

    ?
    1
    2
    3
    4
    5
    6
    -- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환.
    SELECT MAX(sal) salary FROM emp;
     
    SALARY
    -------
      5000  

    MIN

    : MIN 함수는 컬럼값 중에서 최소값을 반환 한다.

    ?
    1
    2
    3
    4
    5
    6
    -- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환
    SELECT MIN(sal) salary FROM emp;
     
     SALARY
    -------
        800   

    AVG

    : AVG 함수는 평균 값을 반환 한다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    -- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림
    SELECT ROUND(AVG(sal),1) salary
    FROM emp
    WHERE deptno = 30;
     
     SALARY
     ------
     1566.7

    SUM

    : SUM 함수는 검색된 컬럼의 합을 반환 한다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    -- 부서번호 30의 사원 급여 합계를 조회.
    SELECT SUM(sal) salary
    FROM emp
    WHERE deptno = 30;
     
     SALARY
    -------
       9400   

    STDDEV

    : STDDEV 함수는 표준편차를 반환 한다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    -- 부서번호 30의 사원 급여 표준편차를 반환.   
    SELECT ROUND(STDDEV(sal),3) salary
    FROM  emp
    WHERE deptno = 30;
     
      SALARY
    --------
     668.331 

    집계함수 예

    : 아래는 부서별 사원수, 최대급여, 최소급여, 급여합계, 평균급여를 급여합계 순으로 조회하는 예이다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT deptno 부서번호, COUNT(*) 사원수,
           MAX(sal) 최대급여, MIN(sal) 최소급여,
           SUM(sal) 급여합계, ROUND(AVG(sal)) 평균급여
     FROM emp
     GROUP BY deptno
     ORDER BY SUM(sal) DESC;
     
     
    부서번호   사원수  최대급여  최소급여   급여합계   평균급여
    ------- -------- --------- -------- --------- ---------
         20       5      3000       800    10875     2175
         30       6      2850       950     9400     1567
         10       3      5000      1300     8750     2917


    GROUP BY

    • : GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
    • : 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
    • : 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
    • : 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 부서별 사원수 조회
    SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
    FROM emp
    GROUP BY deptno
    ORDER BY COUNT(*) DESC;
     
     
    YEAR     부서번호     사원수
    ------ ---------- ----------
    2005년         30          6
    2005년         20          5
    2005년         10          3

    아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
           ROUND(SUM(sal)) "급여합계"
      FROM emp
     GROUP BY deptno;
     
     
      DEPTNO   COUNT(*)    급여평균    급여합계
    -------- ---------- ---------- ----------
          30          6       1567       9400
          20          5       2175      10875
          10          3       2917       8750

    아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
           MAX(sal) "최고급여액", MIN(sal) "최저급여액",
           SUM(sal) "급여합계"
    FROM emp
    GROUP BY job;
     
     
    JOB           인원수   평균급여액   최고급여액   최저급여액    급여합계
    ----------- -------- ---------- ---------- ---------- ----------
    CLERK              4     1037.5       1300        800       4150
    SALESMAN           4       1400       1600       1250       5600
    PRESIDENT          1       5000       5000       5000       5000
    MANAGER            3 2758.33333       2975       2450       8275
    ANALYST            2       3000       3000       3000       6000

    • : GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
    • : 아래 예제를 보면 GROUP BY는 말 그대로 그룹을 나누는 역할을 한다.
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- GROUP BY를 이용한 부서번호 조회 예
    SELECT deptno
    FROM emp
    GROUP BY deptno;
     
     
    DEPTNO
    ------
        30
        20
        10

    DISTINCT와 GROUP BY절

    • : DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
    • : DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
    • : GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
    • : 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
    • : 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
    • : 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.

    아래의 예제는 동일한 결과를 반환한다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- DISTINCT를 사용한 중복 데이터 제거
    SELECT DISTINCT deptno FROM emp;
     
     
    -- GROUP BY를 사용한 중복 데이터 제거
    SELECT deptno FROM emp GROUP BY deptno;
     
     
    DEPTNO
    ------
        30
        20
        10

    하지만 곰곰히 생각해 보면 GROUP BY와 DISTINCT는 각자 고유의 기능이 있다

    집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용 하도록 하자

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 아래와 같은 기능은 DISTINCT를 사용하는 것이 훨씬 효율적이다.
    SELECT COUNT(DISTINCT d.deptno) "중복제거 수",
           COUNT(d.deptno) "전체 수"
    FROM emp e, dept d
    WHERE e.deptno = d.deptno;
     
     
    -- 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다.
    SELECT deptno, MIN(sal)
    FROM emp
    GROUP BY deptno;

    HAVING 절

    • : WHERE 절에서는 집계함수를 사용 할 수 없다.
    • : HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
    •   HAVING 절은 GROUP BY절과 함께 사용이 된다.

    아래 예제는 사원수가 다섯 명이 넘는 부서와 사원수를 조회하는 예제이다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT b.dname, COUNT(a.empno) "사원수"
    FROM emp a, dept b
    WHERE a.deptno = b.deptno
    GROUP BY dname
    HAVING COUNT(a.empno) > 5;
     
     
    DNAME          사원수
    ------------ -------
    SALES              6

    아래 예제는 전체 월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회하는 예이다. 단 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬하였다.

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT job, SUM(sal) "급여합계"
    FROM emp 
    WHERE job != 'SALES'      -- 판매원은 제외
    GROUP BY job              -- 업무별로 Group By
    HAVING SUM(sal) > 5000     -- 전체 월급이 5000을 초과하는
    ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬
     
     
    JOB                  급여합계
    ------------------ ----------
    MANAGER                  8275
    ANALYST                  6000
    SALESMAN                 5600

    - 테이블에 별칭을 부여한 조인 연산

    SELECT s.STU_NAME, c.COU_NAME, c.COU_ID
FROM STUDENT s, COURES c
WHERE s.COU_ID = c.COU_ID;


    ■ INSERT
    INSERT INTO 테이블이름 { 칼럼명 } -- 생략 할 경우, CREATE했던 순서대로 삽입 됨
VALUES({ 칼럼값 })

    ■ UPDATE
    UPDATE 테이블이름
SET { SET 칼럼명 = 칼럼값 }
WHERE 조건

    ■ DELETE
    DELETE FROM 테이블이름
[WHERE 조건] -- 생략 할 경우, 모든 행이 삭제