(SQL 개발자) 주제 Ⅱ – SQL 사용하기

색인

---

과정 Ⅱ – SQL 기초 및 응용

1. SQL 사용

◦ 표준 조인
◦ 집합 연산자
◦ 계층적 쿼리 및 셀프 조인
◦ 하위 쿼리
◦ 그룹 기능
◦ 윈도우 기능
◦ DCL
◦ 절차적 SQL

---

표준 조인

1. NATURAL JOIN: 동일한 이름을 가진 모든 컬럼에 대한 등가 조인

– USING 조건절 또는 ON 조건절은 사용할 수 없습니다.

– 동일한 데이터 유형의 열만 조인할 수 있습니다.

– 별칭 또는 테이블 이름은 허용되지 않습니다.

– SQL: table1 NATURAL JOIN table2에서 열 선택;

2. INNER JOIN: 행의 값이 같은 열을 조인

– USING 조건절 또는 ON 조건절을 사용해야 합니다.

– 두 테이블에 동일한 이름의 열이 있는 경우 SELECT 절에 별칭이 필요합니다.

– SQL: SELECT 열 FROM table1 A INNER JOIN table2 B ON A.column = B.column(ANSI/ISO 표준)

3. 조건부 USING 절: 동등 조인을 수행한 컬럼과 동일한 이름의 컬럼에 대한 동등 조인.

– SQL Server에서 지원하지 않음

– 조건절에 별칭이나 테이블 이름이 없습니다.

SQL: SELECT 열 FROM table1 A JOIN table2 B USING(열 이름);

4. ON 조건 설정 : 이름이 다른 컬럼간 조인 가능

– 별칭 또는 테이블 이름 필요

– 괄호는 필수가 아닙니다.

SQL: SELECT 열 FROM table1 A JOIN table2 ON (A.column = B.column);

5. CROSS JOIN: 가능한 모든 조합으로 조인

– 두 세트의 M*N 데이터 조합 발생

– 조인 조건이 없을 때 발생

SQL: table1, table2에서 열 선택;

6. OUTER JOIN : 조인 조건에서 행의 값이 동일하지 않은 열을 조인

– USING 조건절 또는 ON 조건절 필요

– LEFT OUTER JOIN : 왼쪽 테이블 데이터 조회 후, 오른쪽 테이블 데이터 조회

– SQL: table1에서 열 선택 A LEFT OUTER JOIN table2 B ON A.column = B.column;

– SQL: table1 A, table2 B에서 열 선택 A.column = B.column(+); (신탁)

– RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대

– SQL: table1 A에서 열 선택 오른쪽으로 OUTER JOIN table2 B ON A.column = B.column;

– SQL: table1 A, table2 B에서 열 선택 A.column(+) = B.column; (신탁)

– FULL OUTER JOIN : 조인시 좌우 테이블의 데이터를 모두 읽어 JOIN하여 결과를 생성한다.

– 중복데이터 제거(중복데이터 1행 처리)

– SQL: table1에서 열 선택 A FULL OUTER JOIN table2 B ON A.column = B.column;

---

집합 연산자

1. 일반적인 집합 연산: 조인 없이 여러 테이블에서 관련 데이터 검색

2. 순수한 관계 연산

---

계층적 쿼리 및 셀프 조인

1. 계층적 쿼리: 계층적 데이터를 쿼리하는 데 사용됨(Oracle 제공)

– Hierarchical Data: 엔터티가 순환 데이터 모델로 설계되었을 때 발생

– LPAD: 계층적 검색 결과를 명확히 하기 위해 사용(LEVEL 값으로 결과 데이터 정렬) (예: LPAD(‘ ‘, 4) )

2. SQL Server 계층적 쿼리: CTE(Common Table Expression)를 사용한 재귀 호출

3. Self-join: 테이블의 두 열이 관계가 있는 경우(동일한 테이블 간 조인)

– 동일한 테이블이 FROM 절에 두 번 이상 나타납니다.

– 별칭 사용 필요

---

하위 쿼리

1. 서브쿼리: SQL 문 안의 SQL 문

주의

– 하위 쿼리 주위에 괄호 사용

– 하위 쿼리는 단일 행 또는 다중 행 비교 연산자와 함께 사용할 수 있습니다.

단일 행 비교 연산자의 경우 하위 쿼리는 하나 이하의 결과를 가져야 하며 다중 행 비교 연산자의 경우 결과 수는 관련이 없습니다.

– ORDER BY는 하위 쿼리에서 사용할 수 없습니다.

– SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에서 사용할 수 있습니다.

2. 반환 데이터 유형별 분류

– 단일 행 서브쿼리: 실행 결과가 1개 이하인 서브쿼리

– 단일 행 비교 연산자와 함께 사용
– =, <, >, <=, >=, <>

– 다중행 서브쿼리: 실행 결과가 여러 개인 서브쿼리

– 여러 줄 비교 연산자와 함께 사용

– 다중 열 하위 쿼리: 여러 열을 실행 결과로 반환

– 메인 쿼리 조건과의 비교에 주로 사용(비교할 컬럼의 개수와 위치가 동일해야 함)

3. 스칼라 하위 쿼리: 값을 반환하위 쿼리

– SELECT 절에서 사용되는 하위 쿼리

네 번째 보기: 가상 테이블

– FROM 절에 사용된 뷰는 인라인 뷰입니다.

장점

– 독립성: 테이블 구조 변경의 자동 미러링

– 편의성 : 간단한 쿼리 작성, 자주 사용하는 SQL 문장을 뷰로 생성하여 사용 가능

– 보안: 뷰 생성 시 컬럼 제외 가능

5. WITH: 서브쿼리와 함께 뷰로 사용할 수 있는 문장

SQL: WITH viewname AS (SELECT * FROM viewname WHERE ~ );

---

그룹 기능

1. ANSI/ISO 표준 데이터 분석 기능

– 집계 기능

– 그룹 기능

– 윈도우 기능

2. 그룹 기능 : 합계 계산 기능, 0 빼기 및 집계결과 값이 없는 행은 출력되지 않습니다.

-그룹화: 그룹 함수로 생성된 합계를 분리하는 함수

소계 또는 합계를 계산하면 1을 반환하고 그렇지 않으면 0을 반환합니다.

---

윈도우 함수

1. 윈도우 기능: 여러 행 간의 관계를 정의하는 기능

– 쌓을 수 없다

– 행 간의 관계를 정의하거나 행과 행을 비교 계산하는 기능
– 키워드로 OVER 구문이 필요합니다.
– 기존에 일부 기능을 사용하였고, WINDOW 기능을 위해 일부 기능을 새롭게 추가하였습니다.
– ARGUMENTS(인수): 함수에 따라 0~N개의 인수를 지정할 수 있습니다.

2. 창 함수 구문

SQL: 창 함수 SELECT (A) OVER (PARTITION BY 열 ORDER BY 열 창 절) FROM table-name;

– PARTITION BY: 그룹화 기준(기준에 따라 전체 세트를 소그룹으로 나눕니다.)

– ORDER BY: 순위 기준

– Windowing 절(WINDOWING): 함수의 대상이 되는 행의 범위 지정

– ROWS는 물리적 결과 행의 수를 지정하고 RANGE는 논리 값으로 범위를 지정합니다.

– BETWEEN A AND B: 섹션 식별

– SQL Server에서 지원하지 않음

3. 순위 기능

– RANK: 중복 순위를 포함하여 값이 같으면 중복 순위가 출력되며, 다음 순위는 해당 숫자만큼 건너뛰고 반환

(예: 1, 2, 2, 4 …. )

– DENSE_RANK: 중복 순위 무시(중간 순위는 비우지 않음)

(예: 1, 2, 2, 3 …. )

– ROW_NUMBER : 단순히 행 번호를 표시하고 값에 관계없이 고유한 순위를 지정합니다.

(예: 1, 2, 3, 4 …. )

4. 일반 집계 함수

– 합, 최대, 최소, 평균, 개수

5. 행 순서 기능: (SQL Server는 지원되지 않음)

– FIRST_VALUE: 파티션 별로 창에 가장 먼저 나타난 값을 가져옴(MIN함수로도 동일한 결과를 얻을 수 있음)

– LAST_VALUE: 각 파티션에 대한 창의 마지막 값을 가져옵니다(동일한 결과는 MAX 함수로 얻을 수 있음).

– LAG: 각 파티션에 대한 창에서 이전 행의 값을 가져올 수 있습니다.

– LEAD: 각 파티션에 대한 윈도우의 다음 행 값을 얻을 수 있습니다.

6. 관계 함수

– PERCENT_RANK(): 퍼센티지 순서로 첫 번째가 0, 마지막이 1이며 백분율은 소수점으로 계산됩니다.

– CUME_DIST(): 현재 행 아래 값을 포함하는 누적 백분율, 누적 분포에서의 위치는 0에서 1 사이의 값을 가짐

– RATIO_TO_REPORT: 합계에 대한 값의 백분율(소수점까지)

– NTILE(A): 파티션당 총 케이스 수를 N 등분(1부터 시작)으로 나눕니다.

---

DCL

1. DCL: 사용자를 변경하거나 권한을 제어하는 ​​명령어

– GRANT: 승인

SQL: GRANT 권한 ON 개체 TO 사용자 이름;

– REVOKE: 권한 삭제

SQL: REVOKE 권한 ON 개체 TO 사용자 이름;

2. 특전

– SELECT, INSERT, UPDATE, DELETE, ALTER, ALL : DML 관련 권한

– REFERENCES : 특정 테이블을 참조하는 제약조건을 생성할 수 있는 권한

– INDEX: 지정한 테이블에 인덱스를 생성할 수 있는 권한

3. 오라클 사용자

– SCOTT: 테스트용 샘플 사용자

– SYS: DBA 권한을 가진 최상위 사용자

– SYSTEM : DBA가 DB에 모든 시스템 권한을 부여

4. 역할: 권한 세트

– 사용자에게 즉시 권한을 부여하기 위해 사용

---

절차적 SQL

1. 절차적 SQL: 일반 개발 언어와 같이 절차적 지향의 프로그램을 작성할 수 있는 능력

– 조건에 따라 SQL 문의 연속 실행 또는 분기 처리를 통해 특정 기능을 수행하는 메모리 엔진 구축

2. 메모리 모듈

– PL/SQL 문을 DB 서버에 저장하고 사용자와 응용 프로그램 간에 교환하도록 설계된 SQL 구성 프로그램의 일종

– 독립적으로 또는 다른 프로그램에서 실행할 수 있는 완전한 실행 가능 프로그램

3.PL/SQL

– 기능별 모듈화가 가능한 블록 구조

– 변수와 상수를 선언하여 SQL 문 간의 값 교환

– IF, LOOP와 같은 절차적 언어로 가능한 절차적 프로그램

– DBMS 정의 오류 또는 사용자 정의 오류를 정의하여 사용할 수 있음

– PL/SQL은 Oracle과 통합되어 호환성이 높음

– 애플리케이션 성능 개선

– 블록단위로 처리 -> 통신량을 줄일 수 있음.

Declare(선언부) : BEGIN~END 절에서 사용할 변수와 인자에 대한 정의 및 데이터 타입 선언부

시작 : 개발자가 처리하고자 하는 SQL문, 각종 비교문, 제어문을 이용하여 필요한 로직 처리

Exception(Exception Handling Unit): 블록에서 발생한 에러 처리 로직 정의, 선택 요소

4. T-SQL: SQL 서버를 제어하는 ​​언어

5. UDF: SQL 절차 논리와 함께 데이터베이스에 저장된 일련의 명령문

– RETURN을 통해 값을 반환해야 합니다.

6. 프로시저: EXCUTE 프로시저 이름으로 실행

– BEGIN~END 절 내에서 COMMIT, ROLLBACK 등의 트랜잭션 종료 명령어 사용 가능

7. 트리거: DML 문이 실행되면 자동으로 실행되는 프로그램

– COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령은 BEGIN~END 절 내에서 사용할 수 없습니다.