[JsCode]2주차 정리

수박이삼촌·2024년 1월 18일
CSDatabaseJSCODE
0

DataBase

목록 보기
6/10
post-thumbnail
  • SQL에 대해서 설명해주세요. C언어와 같은 프로그래밍 언어와 어떤차이가 있나요?
  • 개발자가 작성한 SQL이 어떤 과정을 통해 실행 되는지 설명해주세요.
  • DML은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.
  • DDL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.
  • DCL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.
  • 참조 무결성에 대해서 설명해주세요.
  • CASCADE 설정에 대해서 설명해주세요.
  • VIEW에 대해서 설명해주세요.
  • SELECT 절의 처리순서에 대해서 설명해주세요.
  • SELECT ~ FOR UPDATE 구문에 대해서 설명해주세요.
  • GROUP BY절에 대해서 설명해주세요.
  • ORDER BY절에 대해서 설명해주세요.
  • INNER JOIN과 OUTER JOIN의 차이점에 대해서 설명해주세요.
  • LEFT OUTER JOIN, RIGHT OUTER JOIN에 대해서 설명해주세요.
  • CROSS JOIN에 대해서도 설명해주세요.
  • 서브쿼리에 대해서 설명해주세요.
  • DROP, TRUNCATE, DELETE에 각각에 대해 설명해주세요. 어떤차이가 있나요?
  • DISTINCT에 대해서 설명해주세요. 사용해본 경험도 설명해주세요.
  • SQL Injection 공격이 무엇인지 어떻게 공격을 예방할 수 있는지 설명해주세요.
  • 알고 있는 SQL 안티패턴이 있다면 설명해주세요.
  • 페이지네이션을 구현한다고 했을때 쿼리를 어떻게 작성해야할까요?

SQL의 정의, C언어와 같은 프로그래밍 언어와 차이점

SQL은 'Structured Query Language'의 약자로, 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 조회하거나 조작할 때 사용하는 특수 목적의 프로그래밍 언어입니다. 반면, C언어는 범용 프로그래밍 언어로 시스템 프로그래밍 및 애플리케이션 개발에 널리 사용됩니다.

차이점

  • 목적
    SQL은 주로 데이터베이스 접근과 관련된 작업에 특화되어 있으며 선언적 언어의 특성을 가지고 있습니다. C언어는 다양한 종류의 소프트웨어를 개발할 수 있는 절차적 프로그래밍 언어입니다.

  • 사용법
    SQL은 데이터를 추출하거나 데이터베이스 구조를 변경하는 등의 데이터 중심의 간단한 표현으로 작성되지만, C언어는 복잡한 알고리즘과 로직을 구현할 때 사용됩니다.

  • 표현 방식
    SQL은 데이터를 어떻게 처리할 것인지 지정하는 것이 아니라 무엇을 할 것인가를 선언하는 방식으로 작성합니다. 반면, C언어는 구체적인 처리 과정을 단계별로 기술해야 합니다.

  • 실행 환경
    SQL은 주로 DBMS 내에서 실행되며, C언어는 운영 체제 위에서 실행되는 다양한 애플리케이션을 만들기 위해 사용됩니다.

SQL 실행 과정

  1. 클라이언트/서버 통신
    : 클라이언트(MySQL Workbench, 커맨드 라인 인터페이스, 프로그래밍 언어 API 등) 에서 SQL 쿼리를 데이터베이스 서버로 전송한다.

  2. 쿼리 파싱
    : 데이터베이스 서버가 쿼리를 받으면 먼저 쿼리를 파싱한다. 파서(parser)는 SQL문장을 이해할 수 있는 단위로 나누고 이때 문장의 문법이 유효한지, 키워드가 올바르게 사용되었는지, 사용된 컬럼과 테이블이 실제로 존재하는지 등을 확인하고 문법에 오류가 있으면 parser는 오류 메시지를 반환하고 프로세스를 중단한다.

  3. 최적화 및 실행 계획 생성
    : 파싱이 성공적으로 완료되면, 쿼리 최적화기(optimizer)가 작동하여 파싱된 쿼리를 가능한 한 효율적으로 실행할 수 있는 방법을 결정한다. 사용할 인덱스 결정, 조인 순서, 데이터를 읽는 방법 등을 포함하여 ‘실행계획’이라는 형태로 생성된다.

  4. 쿼리 실행
    : 실행 계획에 따라, 데이터베이스 서버는 스토리지 엔진을 통해 필요한 데이터를 불러오거나 변경하고 이 단계에서는 실제 데이터베이스 파일 또는 인덱스에 접근한다.

  5. 결과 반환
    : 쿼리 실행이 완료되면 MySQL 서버는 결과 세트(Result Set)를 클라이언트에게 반환한다. SELECT의 경우는 검색된 행들이 되고 INSERT, UPDATE, DELETE의 경우 영향을 받는 행의 수가 된다.

DML(Data Manipulation Language)

데이터의 삽입, 삭제, 수정, 검색 등의 처리를 요구하기 위해 사용합니다.

  • SELECT
    데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 "RETRIEVE" 라고도 합니다.
  • INSERT
    데이터베이스에 새로운 튜플을 집어넣는 명령어.
  • UPDATE
    데이터베이스 내에 있는 데이터의 수정을 하는 명령어.
  • DELETE
    데이터베이스 내에 있는 데이터의 삭제를 하는 명령어.

DDL(Data Definition Language)

A : 테이블을 생성하고 변경, 제거하는 기능을 제공합니다.

  • CREATE
    테이블을 생성하는 명령어.
  • ALTER
    테이블을 수정하는 명령어.
  • DROP
    테이블을 삭제하는 명령어.
  • RENAME
    테이블 명을 수정하는 명령어.
  • TRUNCATE
    테이블을 초기화하는 명령어.

DELETE와 DROP, TRUNCATE 차이

  • DELETE
    : 테이블 내 튜플(행) 데이터가 삭제되지만 데이터 용량은 지워지지 않습니다.삭제 후 RollBack이 가능합니다.
  • DROP
    : 테이블 자체를 삭제하는 명령어이므로 데이터와 용량 공간, 객체 모두 삭제됩니다. Auto Commit 기능으로, 삭제 후 RollBack이 불가능합니다.
  • TRUNCATE
    : 테이블 내 데이터와 저장 공간, 인덱스 모두 초기화합니다. Auto Commit 기능으로, 삭제 후 RollBack이 불가능합니다.

DCL(Data Control Language)

A : 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능 을 제공하는 언어입니다. 데이터베이스 관리자가 주로 사용합니다.

데이터베이스 인스턴스에 대한 권한으로는 읽기 권한, 입력 권한, 갱신 권한, 삭제 권한이 있습니다.

데이터베이스 스키마에 대한 권한으로는 색인 생성/삭제 권한, 테이블 생성 권한, 테이블 속성 변경 권한, 테이블 삭제 권한이 있다.

  • GRANT
    권한을 부여합니다.
  • REVOKE
    부여된 권한을 회수합니다.

참조 무결성

관계형 데이터베이스에서 데이터의 일관성을 보장하는 중요한 개념 중 하나입니다. 외래키가 참조하고 있는 기본 키와 상관 없는 값을 참조할 수 없습니다.

  1. 외래키 값은 참조중인 테이블의 기본키 값 중 하나이거나 NULL 값이어야 합니다.( 예, 신입사원은 부서가 아직 안 정해져 있어 부서ID에 null 값을 할당해주어야 한다.)
  2. 참조하는 테이블의 기본 키 값이 변경될 경우, 해당 값을 참조하는 외래 키 값도 함께 변경되어야 합니다.
  3. 참조하는 테이블의 특정 행이 삭제될 경우, 해당 행을 참조하는 외래 키를 가진 모든 행도 함께 삭제되어야 합니다.

Cascade

참조 무결성을 유지하기 위해 사용됩니다.
참조하고 있는 테이블의 기본키 값이 변경/삭제될 경우, 해당 외래키 값도 연쇄적으로 변경/삭제됩니다.


View

다른 테이블을 기반으로 만든 가상 테이블입니다.
일반 테이블과 달리 데이터를 실제로 저장하고 있지 않습니다. 물리적으로 존재하면서 실제로 데이터를 저장하는 일반 테이블과 달리, 뷰는 논리적으로만 존재합니다. 뷰를 만드는 데 기반이 되는 물리적인 테이블을 기본 테이블이라 합니다.

뷰 생성 : CREATE VIEW 뷰_이름 [(속성__리스트)]
AS SELECT 문
[WITH CHECK OPTION];

  • 제약 조건을 통해 중요한 정보를 가린 채 뷰를 만들면, 조회 시 뷰를 조회하는 방식으로 보안 처리가 가능합니다.
  • SELECT 문은 생성하고자 하는 뷰의 정의를 담고 있으며, ORDER BY를 사용할 수 없습니다.
  • 삽입/변경/삭제는 뷰의 제약 조건과 함께 실제 테이블의 제약조건도 만족해야 실행됩니다.
    • DISTINCT 키워드를 포함하여 정의한 뷰
    • GROP BY 절을 포함하여 정의한 뷰
    • 여러 개의 테이블을 조인한 뷰
  • 뷰의 장점
    • SQL문을 좀 더 쉽게 작성 가능.
    • 데이터의 보안 유지에 도움.
    • 데이터를 보다 편리하게 관리.

뷰 삭제 : DROP VIEW 뷰_이름;

  • 뷰를 삭제하더라도 기본 테이블은 영향을 받지 않습니다.
  • 만약 삭제할 뷰를 참조하는 제약조건이 존재한다면 수행되지 않습니다.

SELECT 절

SELECT 실행 순서

FROM and JOIN
-> WHERE
-> GROUP BY
-> HAVING
-> SELECT
-> ORDER BY
-> LIMIT

동시 제어

DB의 특정 데이터에 대한 동시 수정을 하게 될 경우 데이터의 일관성을 위협합니다.
따라서, 특정 데이터에 대한 Lock을 통해 수정 권한을 선점하는 방식의 pessimistic을 많이 활용합니다.

pessimistic 방법은 단일 프로세스냐 다중 프로세스냐에 따라 달라지는데요.

  • 단일 프로세스일 경우
    1. 메모리 Lock
    2. 파일에서 Lock 쓰기

  • 다중 프로세스일 경우
    1. Redis를 활용한 Lock
    2. DB 제공 기능 Lock

RDBMS에선 SELECT ~ FROM UPDATE를 통해 Lock이 가능합니다.
이 구문을 사용하면 선택된 행에 대한 잠금을 걸어 다른 트랜잭션에서 해당 행을 수정하거나 삭제하는 것을 방지할 수 있습니다.

출처 - https://youtu.be/rdbCwjct7h0?si=xkhd_dQx-JyNoyvF

ALL vs DISTINCT

둘 다 테이블 조회 쿼리문에서 사용되는 것으로,

  • ALL : 결과 테이블에서 튜플의 중복 허용
  • DISTINCT : 결과 테이블에서 튜플의 중복 제거

GROUP BY

특정 속성의 값이 같은 튜플을 모아 그룹화하여, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용합니다.

그룹에 대한 조건을 추가하려면 HAVING키워드와 함께 작성합니다.

ORDER BY

결과 집합을 특정 열이나 열들의 값에 따라 정렬하는 데 사용합니다.

  • ASC 오름차순 정렬
    • Null값 포함 시 Null이 맨 위
  • DESC 내림차순 정렬
    • Null 값 포함 시 Null이 맨 아래

JOIN

JOIN은 두 개 이상의 테이블을 관련있는 컬럼을 통해 결합하는데 사용됩니다.

두 개 이상의 테이블은 반드시 연관있는 컬럼이 존재해야 하며 이를 통해 JOIN된 테이블들의 컬럼을 모두 활용할 수 있습니다.

INNER JOIN

두 테이블에서 조인 조건에 일치하는 행만 반환합니다. 즉, 두 테이블의 교집합을 결과로 가져옵니다. 만약 조건에 일치하는 행이 없다면, 결과는 빈 집합이 됩니다.

SELECT a.menu_name,
	   b.category_name
  FROM tbl_menu a
 INNER JOIN tbl_category b 
    ON a.category_code = b.category_code;

OR

SELECT a.menu_name,
	   b.category_name
  FROM tbl_menu a
 INNER JOIN tbl_category b USING (category_code);

위 예시에서는 menu 테이블과 category 테이블에서 category code가 일치하는 행만 선택됩니다. (inner은 생략 가능)
실행결과

OUTER JOIN

두 테이블에서 조인 조건에 일치하지 않는 행도 반환합니다. OUTER JOIN은 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 세 가지 유형이 있으며, 각각은 결합하는 테이블의 한쪽 또는 양쪽에서 모든 행을 반환합니다.

위 예시에서는 Category 테이블의 모든 행과, 그에 해당하는 Menu 테이블의 행이 선택됩니다. 만약 Category 테이블에 일치하는 행이 없다면, 해당 부분은 NULL로 채워집니다.

LEFT OUTER JOIN

SELECT a.category_name,
   	   b.menu_name
  FROM tbl_category a
  LEFT OUTER JOIN tbl_menu b 
    ON a.category_code = b.category_code;

RIGHT OUTER JOIN

SELECT a.category_name,
   	   b.menu_name
  FROM tbl_category a
 RIGHT OUTER JOIN tbl_menu b 
    ON a.category_code = b.category_code;

CROSS JOIN

두 테이블의 모든 가능한 조합을 반환합니다. 이를 카테시안 곱(Cartesian Product)이라고도 합니다. CROSS JOIN은 조인 조건을 명시하지 않으며, 두 테이블의 각 행이 서로 결합된 결과를 가져옵니다.

SELECT a.menu_name,
	   b.category_name
  FROM tbl_menu a
 CROSS JOIN tbl_category b;

SELF JOIN

  • 같은 테이블 내에서 행과 행 사이의 관계를 찾기 위해 사용됩니다.
  • 카테고리 별 대분류 확인을 위한 SELF JOIN 조회에 사용됩니다.

예를 들어, 같은 직원들 내에 직급을 분류 하기 위해 사용됩니다.

SUBQUERY

서브쿼리(SUBQUERY)는 다른 쿼리 내에서 실행되는 쿼리입니다.

SUBQUERY의 결과를 활용해서 복잡한 MAINQUERY를 작성해 한번에 여러 작업을 수행할 수 있습니다.

종류

종류설명
스칼라 서브쿼리(Scalar Suquery) SELECT 절에 위치하며 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리 (단일행, 단일칼럼을 반환)
인라인 뷰(Inline View) FROM 절에 위치하는 서브쿼리로 결과는 반드시 하나의 테이블로 리턴되어야 한다.
중첩 서브쿼리(Nested Subquery) WHERE 절에 위치하며 결과집합을 한정하기 위한 서브쿼리로, 서브쿼리가 메인쿼리 컬럼을 참조할때 상관관계 있는 서브쿼리라고 한다. (단일행, 다중행을 반환)

활용

  • 서브쿼리와 메인 쿼리를 활용한 다중열 결과 조회

    • 서브 쿼리

      ```sql
SELECT category_code
  FROM tbl_menu
 WHERE menu_name = '민트미역국';
```


    • 메인 쿼리

      SELECT menu_code,
       menu_name,
       menu_price,
       category_code,
       orderable_status
  FROM tbl_menu;

    • 서브쿼리를 활용한 메인쿼리

      SELECT menu_code,
       menu_name,
       menu_price,
       category_code,
       orderable_status
  FROM tbl_menu
 WHERE category_code =
 (SELECT category_code
    FROM tbl_menu
   WHERE menu_name = '민트미역국');


SQL Injection

SQL Injection 공격은 악의적인 사용자가 웹 어플리케이션의 취약점을 이용하여 SQL 쿼리문을 조작하거나 주입함으로써 데이터베이스를 비정상적으로 조작하는 보안 공격입니다. 이를 통해 기밀 정보를 조회하거나 변경, 삭제하는 등의 행위가 가능해집니다.

예방 방법
1. 입력값 검증: 사용자로부터 받은 모든 입력값을 신뢰하지 않고 검증하는 것이 중요합니다. 특히 문자열을 처리할 때는 특수 문자를 이스케이프하거나 제거하는 등의 처리가 필요합니다.
2. SQL 파라미터화: SQL 쿼리문에서 변수를 사용하여 SQL Injection 공격을 방지할 수 있습니다. 이는 Prepared Statement 또는 Parameterized Query라고도 불립니다.
3. ORM(Object-Relational Mapping) 사용: SQL 쿼리문 대신 객체 지향 쿼리를 사용하여 SQL Injection을 방지할 수 있습니다.
4. 최소한의 접근 제한


SQL 안티패턴

SQL 안티패턴은 SQL 쿼리를 작성하거나 데이터베이스를 설계할 때 피해야 하는 잘못된 패턴이나 접근 방식을 의미합니다. 이러한 안티패턴은 성능 저하, 데이터 무결성 문제, 보안 취약점 등을 초래할 수 있습니다.

  1. SELECT *
    'SELECT *'를 사용하면 테이블의 모든 열을 가져옵니다. 이는 필요하지 않은 데이터까지 가져와 성능을 저하시키며, 애플리케이션에서 예상치 못한 결과를 초래할 수 있습니다. 필요한 열만 명시적으로 선택하는 것이 좋습니다.
  2. 임의의 SQL 쿼리 실행
    사용자 입력을 검증하지 않고 바로 SQL 쿼리에 사용하면 SQL Injection 공격에 취약해집니다. 항상 사용자 입력을 검증하고, 가능하다면 Prepared Statements를 사용해야 합니다.
  3. 인덱스 무시
    적절한 인덱싱 없이 큰 테이블에서 데이터를 조회하면 성능이 저하됩니다. WHERE, JOIN, ORDER BY 등의 연산에서 사용되는 열에 인덱스를 만드는 것이 중요합니다.
  4. NULL 처리 무시
    NULL은 SQL에서 특별한 의미를 가지며, 적절하게 처리되지 않으면 예상치 못한 결과를 초래할 수 있습니다. NULL이 가능한 열을 다룰 때는 항상 이를 고려해야 합니다.

Pagination 쿼리

현재 페이지와 한 페이지에 렌더링할 게시물 수를 변수로 받아, 각각 offset, limit로 조건문을 사용해 조회합니다.

SELECT * 
  FROM table
 ORDER BY SomeColumn
 LIMIT ?
OFFSET ?;

Reference

profile
수박이삼촌
Today I Learned
이전 포스트

[JsCode] 1주차 정리

다음 포스트

[DB] 순차적 I/O와 랜덤 I/O

0개의 댓글