[SQLD] SQL 기본 및 활용(2)

Dahui Kim·2023년 3월 13일
SQLDsqlsql활용서브쿼리윈도우함수절차형SQL트랜잭션트리거
0

데이터베이스

목록 보기
4/5

표준 JOIN

  • FROM 절 JOIN 형태
    • INNER JOIN : JOIN 조건에서 동일한 값이 있는 행만 변환, USING 이나 ON 절을 필수적으로 사용, Default 옵션으로 생략가능, CROSS JOIN 및 OUTER JOIN과 함께 사용불가
      • NATURAL JOIN : 두 테이블 간의 동일한 이름을 갖는 모든 칼럼에 대해 EQUI JOIN을 수행, NATURAL JOIN이 명시 되면 추가로 USING, ON, WHERE 절에서 JOIN 조건을 정의할 수 없다. SQL Server은 지원하지 않음, INNER JOIN의 하위 개념, ALIAS나 테이블명을 사용할 수 없다.
    • USING 조건절 : 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다. JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다, SQL Sever은 지원하지 않는다.
    • ON 조건절 : 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있다. ALIAS나 테이블명을 반드시 사용해야한다. 임의의 JOIN 조건을 사용할 수 있다. ON 조건절에 데이터 검색 조건을 추가할 수 있다.
    • CROSS JOIN : 카테시안 곱(PRODUCT), 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.
      • 카테시안 곱(Cartesian Product, 곱집합) : 두 집합 A, B의 원소들로 만들어지는 모든 순서쌍 (a,b)들의 집합
    • OUTER JOIN : JOIN 조건에서 동일한 값이 없는 행도 반환 가능핟. USING이나 ON 조건절을 반드시 사용해야한다. SQL 식에서 (+) 안붙은 쪽으로 JOIN 한다.
    • LEFT OUTER JOIN : 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.
    • RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대
    • FULL OUTER JOIN : 좌우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 중복 데이터는 삭제한다. (LEFT, RIGHT JOIN의 합집합)

집합 연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용, *SELECT절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용 가능* ⇒ 조건에 맞지 않을 경우 에러 반환

SELECT 칼럼명_1 FROM 테이블명 A WHERE 조건절
집합연산자
SELECT 칼럼명_2 FROM 테이블명 B WHERE 조건절
  • 일반 집합 연산자 : ALIAS 는 처음 테이블, 정렬은 마지막 테이블 기준
    • UNION : 합집합(중복 행 1개로), 정렬 O
    • UNION ALL : 합집합(중복 행도 표시), 정렬 X
    • INTERSECT : 교집합(중복 행도 1개로)
    • MINUS : 차집합(중복 행 1개로) → SQL Server은 EXCEPT로 구현
    • CROSS JOIN : 곱집합(PRODUCT)
  • 순수 관계 연산자 : 관계형 DB를 새롭게 구현
    • SELECT → WHERE 절 구현
    • PROJECT → SELECT 절로 구현
    • NATURAL JOIN → 다양한 JOIN으로 구현
    • DIVIDE → 사용 X

계층형 질의와 셀프 조인

  • 계층형 질의 : 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용한다. 오라클 지원
    • 계층형 데이터 : 동일 테이블에 계층적으로 상하위 데이터가 포함된 데이터로 엔터티를 순환관계 데이터 모델로 설계할 때 발생한다.
  • 계층형 질의 가상 칼럼
    • LEVEL : 루트 데이터 1에서 리프 데이터까지 1씩 증가한다.
    • CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면 1, 아니면 0
    • CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션시 사용가능)
  • 계층형 질의 구문
    SELECT 칼럼명
FROM 테이블명
WHERE 조건절
START WITH 조건
CONNECT BY [NOCYCLE] 조건
[ORDER SIBLINGS BY 칼럼]
    • START WITH : 계층 구조 전개의 시작 위치 지정
    • CONNECT BY : 다음에 전개될 자식 데이터 지정
      • PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다. 반대는 역방향
    • NOCYCLE : 동일한 데이터가 전개되지 않는다.
    • ORDER SIBLINGS BY : 형제 노드 간의 정렬을 수행한다.
  • 계층형 질의 사용 함수
    • SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
    • CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자.
  • SQL Server 계층형 질의 : CTE(Common Table Expression)로 재귀 호출
  • 셀프 조인 : 한 테이블 내에서 두 칼럼이 연관 관계가 있는 경우 동일 테이블 사이의 조인. FROM 절에 동일 테이블이 2번 이상 나타난다. ALIAS 필수

서브 쿼리

하나의 SQL 문 안에 포함되어있는 또다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용된다.

  • 주의사항
    • 서브쿼리를 괄호로 감싸서 사용
    • 서브쿼리는 단일 행(반드시 1건 이하) 또는 복수 행(결과 건수와 상관 X) 비교 연산자와 함께 사용가능
    • 서브쿼리에서는 ORDER BY 사용 불가
    • SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용가능
  • 종류
    • 동작 방식에 따른 분류 : 연관/비연관
      • 비연관 서브쿼리 : 메인쿼리 칼럼을 가지고 있지 않은 서브쿼리, 메인 쿼리에 값을 제공하기 위한 목적으로 주로 사용
      • 연관 서브쿼리 : 서브 쿼리가 메인 쿼리 칼럼을 가지고 있는 형태, 일반적으로 메인 쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인할 때 주로 사용
    • 반환 데이터에 따른 분류 : 단일 행/다중 행/다중 칼럼
      • 단일 행 서브쿼리 : 실행 결과 1건 이하, 단일 행 비교 연산자(=, <, > 등)
      • 다중 행 서브쿼리 : 실행결과 여러 건, 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS 등)
      • 다중 칼럼 서브쿼리 : 실행결과 컬럼 여러 개
    • 스칼라 서브쿼리 : 한 행, 한 칼럼만을 반환하는 서브쿼리, SELECT 절에 사용하는 서브쿼리
  • 뷰(View) : 가상의 테이블, FROM 절에 사용하는 뷰는 인라인 뷰라고 함(ORDER BY 사용가능), 실행 시점에 SQL 재작성하여 수행된다.
    CREATE VIEW 뷰 이름 AS SELECT ...;
DROP VIEW 뷰 이름;
    • 장점
      • 독립성 : 테이블 구조 변경 자동 반영
      • 편리성 : 복잡한 쿼리를 뷰로 생성해 관련 쿼리를 단순하게 작성가능, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용가능
      • 보안성 : 뷰를 생성할 때 칼럼을 제외할 수 있음
  • WITH : 서브쿼리를 이용하여 뷰로 사용할 수 잇는 구문
WITH 뷰 이름 AS (SELECT ...)

그룹 함수(Group Function)

합계 계산 함수, NULL을 빼고 집계, 결과값 없는 행은 출력하지않는다.

  • ANSI/ISO 표준 데이터 분석 함수 : 집계 함수, 그룹 함수, 윈도우 함수
  • 종류
    • GROUPING : 그룹 함수에서 생성되는 합계를 구분해주는 함수, 집계 표시면 1, 아니면 0 반환
    • ROLLUP : 소계(subtotal)를 생성하기 위해 사용된다. GROUP BY의 확장된 형태로, 사용이 쉬우며 병렬로 수행이 가능하다. 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터 집계에 적합하다. Grouping 칼럼의 수를 N이라고 했을 때 N+1 레벨의 소계가 생성된다. 인수 순서에 주의.
    • CUBE : 결합 가능한 모든 값에 대해 다차원적인 집계를 생성해 ROLLUP에 비해 다양한 데이터를 얻을 수 있지만 시스템에 많은 부하를 준다. 2^N
    • GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있다. 다양한 소계 집합을 생성(원하는 부분의 소계만 손쉽게 추출 가능)할 수 있다.

윈도우 함수

행과 행 사이의 관계를 쉽게 정의하거나 행과 행간을 비교, 연산하는 함수

  • 종류
    • 순위 관련 함수
      • RANK : 동일한 값에 대해서는 동일한 순위를 부여한다. (1, 2, 2, 2, 4)
      • DENSE_RANK : 동일한 순위를 하나의 등수로 간주한다. (1, 2, 2, 2, 3)
      • ROW_NUMBER : 동일한 값이라도 고유한 순위를 부여한다. (1, 2, 3, 4, 5)
    • 집계 관련 함수
      • SUM : 파티션별 윈도우의 함을 구할 수 있다.
      • MAX/MIN : 파티션별 윈도우의 최대/최소값을 구할 수 있다.
      • AVG : 원하는 조건에 맞는 데이터에 대한 통계 값
    • 그룹 내 행 순서 함수 (SQL Sever X)

      • FIRST_VALUE : 파티션별 윈도우의 처음 값

      • LAST_VALUE : 파티션별 윈도우의 마지막 값

        ⇒ 동일한 등수를 의도적으로 정렬하고 싶다면 인라인 뷰, ORDER BY를 이용한다.

      • LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값

        LAG (인수, 몇 행 전, 없는 경우)

      • LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값

        LEAD (인수, 몇 행 뒤, 없는 경우)
    • 비율 관련(SQL Sever X)
      • RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼)에 대한 행별 칼럼 값의 백분율을 0과 1 사이의 소수점으로 반환, 개별 RATIO 합은 1 0<결과값≤1
      • PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것 0, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율 반환 0≤결과값≤1
      • CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적 백분율 반환 0<결과값≤1
      • NTILE : 파티션별 전체 건수를 인수(argument) 값으로 N등분한 결과를 구할 수 있다.

DCL

유저를 생성하고 권한을 제어할 수 있는 명령어

  • 오라클 VS SQL Server 사용자 아키텍쳐 차이
    • 오라클 : 유저를 통해 DB에 접속, ID와 PW 방식으로 인스턴스에 접속하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여
    • SQL Server : 인스턴스에 접속하기 위해 로그인 생성, 인스턴스 내 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성 후 로그인과 유저를 매핑, Windows 인증 방식과 혼합 모드 방식 존재
  • 사용자 권한 : 사용자가 SQL문을 실행하기 위해 필요한 권한
    • GRANT : 권한 부여
    • REVOKE : 권한 취소
    • 오라클
      -- 임시 사용자 사용자_1에게 유저 생성 권한 부여
GRANT CREATE USER TO 사용자_1;
CONN 사용자명_1/PW;
CREATE USER 유저명 IDENTIFIED BY 비밀번호;
-- 로그인 위해 CREATE SESSION 권한 부여
CONN 사용자_1/PW;
GRANT CREATE SESSION TO 유저명;
CONN 유저명/PW;
-- 테이블 생성에도 CREATE TABLE 권한 부여 필요
    • SQL Server
      -- 유저 생성 전 로그인 생성 sa
CREATE LOGIN 로그인명 WITH PASSWORD='비밀번호';
DEFAULT_DATABASE=로그인 후 최초 접속할 데이터베이스명;
-- SQL Server엠서 유저는 DB마다 존재해 유저 생성 위해 생성하는 유저가 속할 DB로 이동
USE 데이터베이스명;
GO
CREATE USER 유저명 FOR LOGIN 로그인명 WITH DEFAULT_SCHEMA=dbo;
  • 모든 유저는 각각 자신이 생성한 테이블 외 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야한다.

    • ROLE : 유저에게 알맞은 권한들을 한 번에 부여, 시스템 권한과 오브젝트 권한을 모두 부여 가능, 유저에게 직접 부여될 수도 있고 다른 ROLE에 포함되어 유저에게 부여될 수도 있다. 권한을 직접 부여하는 것보다 빠르고 안전하게 유저를 관리할 수 있다.

      CONN ID/PW;
CREATE ROLE 롤이름;
GRANT 부여권한, ... TO 롤이름;
GRANT 롤이름 TO 유저명;

    • SQL Server에서는 ROLE을 자주 사용하지 않는 대신 서버 수준 역할(인스턴스 수준 요구) 및 데이터베이스 수준 역할을 이용해 로그인 및 사용자 권한을 제어한다.

절차형 SQL

다른 개발 언어들과 같이 절차 지향적인 프로그램이 가능하도록 절차형 SQL을 제공(오라클:PL/SQL, SQL Server:T-SQL)한다. SQL문의 연속적인 실행이나 조건에 따른 분기 처리를 이용해 특정 기능을 수행하는 저장 모듈을 생성 가능하다. Procedure, User Defined Function, Trigger 등이 있다.

  • PL/SQL : 블록 구조(DML/Query 문장, if/loop 등 절차형 언어 사용가능하며 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.
    • 특징
      • 블록 구조로 되어있어 각 기능별 모듈화 가능
      • 변수, 상수 등을 선언해 SQL 문장 간 값을 교환
      • IF/LOOP 등의 절차형 언어를 사용해 절차적 프로그래밍이 가능
      • DBMS 정의 에러나 사용자 정의 에러를 정의해 사용 가능
      • PL/SQL은 오라클에 내장되어 호환성이 좋다.
      • 응용 프로그램 성능 향상
      • 블록 단위 처리로 통신량을 줄일 수 있다.
      • 블록 프로그램을 입력받으면 SQL 문장과 프로그램 문장을 구분해 처리한다.
        • SQL 문장 → 오라클 서버의 SQL Statement Executor
        • 프로그램 문장 → PL/SQL 엔진
    • 구조
      DECLARE
	선언부(변수, 상수)
BEGIN
	실행부
EXCEPTION
	예외 처리부
END
      • DECLARE : BEGIN ~ END 절에서 사용도리 변수와 인수에 대한 정의 및 데이터 타입 선언부
      • BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하기 위해 필요한 로직 처리
      • EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 발생하는 에러 처리 방법 정의
    • 기본 문법

      • Stored Procedure 통해 기본적인 문법 정리

        CREATE OR REPLACE PROCEDURE 프로시저명 (argument_1 mode data_type_1,...) IS AS ..
BEGIN ...
EXCEPTION ...
END /;

      • OR REPLACE 절은 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰겠다는 뜻

      • 지정가능한 MODE 종류 : IN(운영체제 → 프로시저), OUT(프로시저 → 운영체제), INOUT(두 기능 동시에)

      • / : DB에 프로시저를 컴파일하라는 명령어

  • T-SQL : SQL Server를 제어하기 위한 언어, MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완한 것
    • 기능
      • 변수 선언 기능 @@-전역변수(시스템 함수, SQL 서버 내장된 값), @-지역변수(사용자가 자신의 연결 시간 동안만 사용)
      • 데이터 타입 제공
      • 산술/비교/논리연산자 사용 가능
      • 흐름 제어 기능 : IF-ELSE, WHILE, CASE-THEN 사용 가능
      • 주석 기능 : -- 한 줄 주석, /* */ 범위 주석
    • 구조
      DECLARE
	선언부(변수, 상수)
BEGIN
	실행부
EXCEPTION
	예외 처리부
END
      • DECLARE : BEGIN ~ END 절에서 사용도리 변수와 인수에 대한 정의 및 데이터 타입 선언부
      • BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하기 위해 필요한 로직 처리
      • EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 발생하는 에러 처리 방법 정의
    • 기본 문법

      • Stored Procedure 통해 기본적인 문법 정리

        CREATE PROCEDURE 스키마명.프로시저명 @parameter_1 data_type_1 mode, ..
WITH AS ...
BEGIN ...
ERROR 처리 ...
END;

DROP PROCEDURE 스키마명.프로시저명;

      • 프로시저의 변경이 필요한 경우 ALTER 구문을 사용해야한다.

      • 지정가능한 MODE 종류

        • VARYING : 결과 집합이 출력 매개변수로 사용되도록 지정(CURSOR 매개변수에만 적용)
        • DEFAULT : 기본값이 지정되어있으면 해당 매개변수를 지정하지 않아도 기본값으로 수행한다.
        • OUT/OUTPUT : 프로시저에서 처리된 결과값을 EXECUTE문 호출시 반환
        • READONLY : 프로시저 본문 내에서 매개변수를 업데이트하거나 수정할 수 없음, 매개변수 유형이 사용자 정의 테이블 형식인 경우 READONLY 를 지정해야한다.

      • 지정가능한 WITH 옵션

        • RECOMPILE : DB 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일됨. 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제할 떄 RECOMPILE 쿼리 힌트 사용
        • ENCRYPTION : CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환됨. 원본을 볼 수 있는 방법이 없어 반드시 백업을 해두어야한다.
        • EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다.
  • TRIGGER : 특정 테이블에 DML 문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램(하나의 트랜잭션 안에서 일어나는 일련의 작업이라 할 수 있다). DB 보안의 적용이 유효하지 않은 트랜잭션의 예방, 업무규칙 자동 적용 등에 사용될 수 있다.
  • Procedure VS Trigger
    • Procedure : BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능. EXECUTE 명령어로 실행
    • Trigger : BEGIN ~ END 절 내에 사용 불가. 생성 후 자동 실행 ROLLBACK을 하면 하나의 트랜잭션이 취소가 되어 Trigger로 입력된 정보까지 하나의 트랜잭션으로 인식해 모두 취소된다.
profile
Dahui Kim
긍정적인 생각, 적극적인 생활
이전 포스트

[SQLD] SQL 기본 및 활용(1)

다음 포스트

[SQLD] SQL 기본 및 활용(3)

0개의 댓글