[2020 정보처리기사 실기 - SQL 응용] 1. 응용 SQL 작성하기

 

 

 

* SQL

 

1) 

 - SQL 개념 : 관계 대수와 관계 해석을 기반으로 집단함수, 그룹화, 갱신 연산 등을 추가하여 개발한 데이터베이스 질의 언어

 - SQL 특징

   비절차적 언어 : 원하는 바만 명시, 원하는 것을 처리하는 방법은 명시하지 않음

   대화식 SQL : 사용자가 명령문을 명쳥랑에 입력하고 실행 결과를 확인하는 방식

   내포된 SQL : 프로그래밍 언어에 SQL문을 내포해서 응용프로그램이 실해오딜 때 SQL 문이 작동하는 방식

 - SQL 종류 : DDL, DML, DCL

 

2) SQL 문법

- 자연어에 가까운 문법을 가지고 있어서 배우기가 쉬움

 - 데이터를 어떻게 가져올 것인가 말고, 어떤 데이터를 원하는지만 기술

 - SQL 명령어나 문법은 표준화 되어 있음, 모든 관계형 데이터베이스 제품에서 공통적으로 사용가능

 - 기본 규칙

   SQL 명령어와 테이블명, 컬럼명 같은 객체 이름은 대소문자를 구분하지 않으나 데이터베이스에 저장된 데이터 값은 대소문자 구분

   SQL 명령문은 세미콜론으로 종료

 

 

* 데이터 검색 및 조작 명령문 작성

 

 - 데이터 검색하기 : SELECT (정렬 시 ORDER BY, 모두 고를 때 *)

 - 데이터 조작하기 :  INSERT, DELETE, UPDATE

 

 

* DCL 명령문 작성

 

1)

 - 기본개념 : 데이터베이스 규정이나 기법을 정의하고 제어하는 언어

                       사용자 권한 부여, 취소, 트랜잭션을 제어하는 명령어

 - DCL 종류 : COMMIT, ROLLBACK, SAVEPOINT, GRANT, REVOKE

 

2) 사용자 권한 제어

- GRANT로 권한허가, REVOKE로 권한회수

- GRANT : 객체의 생성자는 객체에 대한 모든 권한을 가짐

                    생성자는 자신이 소유한 임의의 객체에 대한 특정 권한을 다른 사용자에게 역할 허가 할 수 있음

                    GRANT절 - SELECT, INSERT, DELETE, UPDATE, REFERENCES 권한 나열

                    WITH GRANT OPTION 부여받은 권한을 다른 사용자에게 허가 가능

 - REVOKE : 취소하게 되면 WITH GRANT OPTION으로 허가한 권한도 연쇄적으로 취소됨

                       권한을 허가했던 사람만 그 권한을 취소할 수 있음

 - 역할 : 여러 사용자들에 대한 권한 관리를 단순화하기 위해 역할을 사용

               사용자에게 허가할 수 있는 연관된 권한들의 그룹을 말함

               각 사용자는 여러 역할들에 속할 수 있고, 사용자들이 동일한 역할을 가질 수 있음

               여러 사용자들에게 허가하는 대신 이 권한들을 역할에게 허가하고, 역할을 각 사용자에게 허가하기 편리

 - 객체 권한과 시스템 권한

    PUBLIC : 키워드를 사용해서 권한을 허가하면 모든 사용자에게 권한을 부여하게 됨

 - SCOTT

    GRANT CREATE SESSION TO SCOTT : SCOTT유저에게 DB연결 권한을 부여

    GRANT CREATE SESSION, CREATE TABLE TO userA : userA에게 테이블 생성권한과 DB 연결권한 동시 부여

    GRANT SELECT, INSERT, DELETE, UPDATE ON student TO userA : student 테이블 읽기, 쓰기, 삭제, 수정 권한 user에게 부여

    GRANT DBA TO SCOTT : SCOTT 유저에게 DBA 권한 부여

    REVOKE SELECT, INSERT, DELETE, UPDATE ON student FROM userA : 회수

    REVOKE CREATE SESSION FROM SCOTT : SCOTT 유저의 DB연결 권한 회수

 

3) 트랜잭션제어

 - COMMIT : 트랜잭션이 성공적으로 종료, 데이터베이스에 완전 반영

                        INSERT, UPDATE, DELETE 문장을 사용한 후 변경 작업이 완료되었음을 DB에 알려주기 위해 사용

 - ABORT : 트랜잭션이 비성공적으로 종료됐음, 트랜잭션 원자성 보장을 위해 갱신한 사항을 트랜잭션 수행 전 상태로 되돌림

 - 동시성 제어 : 다수 사용자가 데이터베이스를 동시에 접근하도록 허용하면서 일관성을 유지하는 기능

 - 회복 : 데이터베이스를 갱신하는 도중에 시스템이 고장 나도 데이터베이스의 일관성을 유지하는 기능

    로그를 이용한 회복 : 로그를 검사해서 로그에 로그 레코드오아 로그 레코드가 모두 존재하는 트랜잭션을 재수행

                                           로그 레코드는 로그에 존재하지만 로그 레코드가 존재하지 않는 트랜잭션 취소

    로그 먼저 쓰기 : 데이터베이스 버퍼가 로그 버퍼보다 먼저 디스크에 기록되면

                                  로그 버퍼가 디스크에 기록되기 전에 시스템이 다운되었거나

                                  재기동되었을 때 주기억 장치는 휘발성이라서 데이터베이스 버퍼와 로그 버퍼의 내용은 남지 않음

                                  로그 레코드가 없으면 데이터의 이전 값을 알 수 없어서 트랜잭션 취소가 불가능함

 - ROLLBACK : 테이블에 입력한 데이터나 수정한, 삭제한 데이터에 대해 COMMIT 이전에 변경 사항을 취소할 경우 사용

 - SAVEPOINT 

    체크포인트 : 재수행할 트랜잭션의 수를 줄이기 위해 주기적으로 체크포인트를 수행

                            체크포인트 시점에 디스크 상에서 로그와 ㄷ이터베이스의 내용이 일치하게 됨

    COMMIT, ROLLBACK, SAVEPOINT를 이용한 트랜잭션 제어

    SAVEPOINT문 : 현재 트랜잭션 내에 저장점을 표시하여 트랜잭션을 더 작은 부분으로 나눔

    ROLLBACK TO SAVEPOINT : 현재 트랜잭션에서 지정된 SAVEPOINT 이후에 갱신된 내용만 되돌림

 

4) 집계성 SQL 작성

 - 총합, 평균 등의 데이터 분석을 위해 복수 개의 행을 기준으로 데이터를 분석하는 SQL문을 말함

 - 집계함수, 그룹함수, 윈도우함수로 구성

 - 집계함수  Aggregation Function

   GROUP BY 절과 함께 사용되어 복수 행에 대한 개수, 합계, 평균, 최소, 최대값등 계산 

   COUNT, SUM, SVG, MAX, MIN, STDDEV, VARIANCE

   애트리뷰트에 적용되어 단일 값을 반환, SELECT와 HAVING절에만 나타남, COUNT(*)를 제외한 모든 함수는 널 값 제거후 계산

   DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 중복을 제거

   그룹화 방법 : GROUP BY 절을 이용해서 분석할 데이터 행을 분류할 기준을 정의

 - 그룹함수 Group Function

   CUBE() 함수 : 결합 가능한 모든 값에 대해 다차원 집계를 생성, 

                              내부적으로 대상 컬럼의 순서를 변경하여 또 쿼리를 수행하고 총계는 양쪽 쿼리에서 모두 수행 후 한쪽에서 제거

                              ROLLUP에 비해 계산이 많음

   GROUPING SETS() 함수 : 집계 대상 컬럼들에 대한 개별 집개를 구함

                                                    ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 생성

                                                    GROUP  BY 한 SQL 들을 UNION ALL 해서 보여주는 결과와 같음

 - 윈도우 함수 Window Function

   행과 행간의 관계를 쉽게 정의하기 위해 만든 함수로 분석함수 또는 순위함수라고도 함

   일반적으로 집계함수나 순위함수 등을 이용해서 그룹을 나눠 분석을 수행

   윈도우 함수 종류 : 순위관련 함수(RANK, DENSE_RANK, ROW_NUMBER)

                                     집계관련 함수 (SUM, MAX, MIN, AVG, COUNT)

                                     행 순서관련 함수 (FIRST_VALUE, LAST_VALUE, LAG, LEAD)

                                     비율관련 함수 (CUME_DIST, PERCENT_RANK, NTILE)

   윈도우 함수 문법 : WINDOW_FUNCTION(ARGUMENTS) 순위 집계 행순서 비율 관련 윈도우 함수를 기술

                                     PARTITION BY 전체 집합을 소그룹으로 나누기 위한 기준 컬럼을 기술

                                     ORDER BY 어떤 항목에 대해 순위를 지정할 지 기준 기술

                                     WINDOWING 함수의 대상이 되는 행 기준의 범위를 ROWS 또는 RANGE로 지정

                                     ROWS는 현재 행의 앞뒤 행들을 범위로 정하고

                                     RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위 표시

   순위 관련 함수 : RANK() ORDERBY와 특정 컬럼에 대한 순위를 구함, PATITION BY와 함께면 특정 범위내에서 순위를 구함

                                 DENSE_RANK() RANK 함수와 흡사하지만 동일한 순위를 하나의 건수로 간주

                                 ROW_NUMBER() 동일한 값이라도 고유한 순위 부여

   집계 관련 함수 : 분석할 대상 파티션을 나눈 후 집계 함수를 이용해 분석을 수행할 수 있음

   행 순서 관련 함수 : FISRT_VALUE() 파티션별 윈도우에서 가장 먼저 나온 값을 구함, MIN을 활용해도 같은 결과를 얻음

                                      LAST_VALUE() 파티션별 윈도우에서 가장 나중에 나온 값을 구함, MAX를 활용해도 같은 결과를 얻음

                                      LAG(1,2,3) 1은 입력컬럼, 2는 몇 번째 앞의 행을 가져올지 결정,

                                                            3은 파티션의 첫번째 행의 경우 가져올 데이터가 없어 NULL값일 경우 변경할 값 입력

                                      LEAD(1,2,3) : 1은 입력컬럼, 2는 몇번 째 행을 가져올지 결정

                                                                3은 파티션의 마지막 행의 경우 가져올 데이터가 없어  NULL값일 경우 변경할 값 입력

   비율 관련 함수 : PERCENT_RANK() 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 

                                                                      제일 늦게 나오는 것으로 1로 하여 값이 아닌 행의 순서별 백분율을 구함

                                 CUBE_DIST() 파티션별 윈도우에서 전체건수에 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함

                                 NTILE() 파티션별 전체 건수 ARGUMENT 값으로 N등분한 결과 구함

                                 RATIO_TO_REPORT() 파티션 내 전체 SUM 값에 대한 행별 칼럼 값의 백분율을 소수점을 구함

                                                                             SQL server에서는 지원하지 않음

 

 

 

---