close
프로필 배경
프로필 로고

Jiihyun's log

CS/DB · 2025. 1. 9. fullscreen 넓게보기

[DB] SQL 톺아보기

SQL이란?

Structured Query Language의 약자로서,

구조화된 데이터베이스에 질의(데이터 삽입, 조회, 수정, 삭제)를 할 때 사용하는 언어를 칭함

=> 주로 관계형 데이터베이스라는 카테고리에 속하는 제품들이 공통적으로 데이터베이스 서버를 제어할 때 사용하는 언어

 

e.g. SQL(SELECT * FROM Categories;)를 입력하면 데이터베이스에서 사진에 있는 데이터들을 얻을 수 있다.

SELECT * FROM Categories;

 

SQL과 프로그래밍 언어와의 차이점

  SQL 프로그래밍 언어
목적  DB에서 데이터를 추출/조작을 위해 만든 언어 일반적인 문제 해결과 프로그램 개발을 위한 언어
동작 방식 선언형 언어
=> '무엇을'할지 기술하면,
DB 엔진이 어떻게 작업할지 결정함		 절차적 또는 객체지향 언어
=> '어떻게'할지 명시
사용자가 세부 로직과 실행 순서 정의함
사용 환경 데이터베이스 서버 애플리케이션 전반

 

SQL 실행 과정

  1. SQL 입력: SQL 문장을 작성하여 DBMS에 전달
  2. 파싱: DBMS가 SQL의 문법과 의미를 분석하여 파싱 트리 생성
    • 문법 분석(Syntax Analysis): SQL의 문법 구조를 확인
      • SELECT, FROM, WHERE 같은 키워드와 테이블 및 컬럼 이름이 올바르게 사용되었는지 확인
    • 의미 분석(Semantic Analysis): 사용된 테이블, 컬럼이 실제 데이터베이스에 존재하는지 확인
  3. 재작성: SQL을 효율적으로 재구성
    • SQL Rewriter가 중복된 서브쿼리를 제거하고 *뷰나 별칭을 명확한 형태로 변환
      • SELECT * FROM Categories; ➡️ SELECT CategoryId, CategoryName, Description From Categories;
  4. ⭐️최적화⭐️: SQL 실행 전에 DBMS가 프로시저를 작성하고 컴파일해서 가장 효율적인 실행 계획을 생성함
    • 비용 기반 최적화: 테이블 크기, 인덱스 여부, 데이터 분포 등을 고려하여, 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간이 적은 가장 빠른 실행 방법 선택
    • 실행 계획 생성: 테이블 스캔(Full Table Scan), 인덱스 스캔(Index Scan), 조인 방식 등 최적화된 방법 결정
  5. 실행: 실행 엔진이 실행 계획에 따라 쿼리를 실행
    • 스캔: 테이블 또는 인덱스를 검색
    • 필터링: 조건에 맞지 않는 데이터를 제외
    • 조합: 여러 테이블 간의 조인을 수행
  6. 결과 반환: 사용자 또는 애플리케이션에 결과 전달

 

SQL 문법 종류

  • 데이터 조작 언어(DML:Data Manipulation Language)
    • 데이터베이스의 내부 데이터를 관리하기 위한 언어로, 데이터 조회, 추가, 변경, 삭제 등의 작업 수행
    • 데이터가 변동되어도 ROLLBACK하여 되돌릴 수 있음(영구적x)
    • 변경될 테이블을 메모리 버퍼 위에 올려두고 변경을 수행하므로 실시간으로 테이블이 변경되지 않음
      => commit 명령어를 통해 Transaction을 종료해야 변경 사항이 테이블에 반영됨
    • SELECT, INSERT, UPDATE, DELETE
  • 데이터 정의 언어(DDL: Data Definition Language)
    • 테이블과 컬럼을 정의하는 명령어로 생성, 수정, 삭제 등의 데이터 전체 골격을 결정
    • 명령어를 입력하는 순간 즉시 반영되므로 사용할 때 주의 필요
    • CREATE, ALTER, DROP, RENAME, TRUNCATE(테이블 초기화)
  • 데이터 제어 언어(DCL:Date Control Language)
    • 데이터를 관리 목적으로 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용
    • 데이터베이스에 접근하여 읽기*쓰기 등 권한을 부여 및 박탈 가능 & 트랜잭션 명시 및 조작 가능
    • GRANT(권한 부여), REVOKE(권한 삭제)
  • 트랜잭션 제어 언어(TCL: Transaction Control Language)
    • DCL과 비슷한 맥락이지만 데이터를 제어하는 언어가 아닌 트랜잭션을 제어할때 사용
    • 논리적인 작업 단위를 묶어 DML에 의해 조작된 결과를 트랜잭션별로 제어
    • COMMIT, ROLLBACK, SAVEPOINT

DROP vs TRUNCATE vs DELETE

  DROP TRUNCATE DELETE
목적 테이블 자체 삭제(구조, 인덱스 등 모두 삭제) 전체 데이터 삭제(테이블 구조는 유지) 특정 행 데이터 삭제
WHERE 조건 사용 불가능 불가능 가능
시스템 부하 적다 적다 크다
데이터 복구 불가능 불가능 가능(트랜잭션 사용시)
메모리 차지율 적음 적음 높음
속도 가장 빠름 빠름  느림

 

SELECT 쿼리 실행 순서

일반적으로 논리적 처리 순서에 따라 실행됨

  1. FROM: 쿼리의 대상이 되는 테이블 또는 뷰를 선택한다- MySQL 같은 경우 이때 JOIN을 수행하여 여러 테이블을 결합함
  2. WHERE: 테이블 혹은 뷰 안에서 특정 조건을 만족하는 행만 필터링하여 선택
  3. GROUP BY: 같은 기준을 가진 행들을 하나의 그룹으로 묶음(그룹화)
    - 각 그룹에 대한 연산 결과(합, 평균, 갯수 등)을 산출하기 위해 집계함수 사용
    - GROUP BY 내에 있는 컬럼은 반드시 SELECT 절에도 존재해야 함
    - 집계함수: count,sum,avg,max,min, stddeb(표준편차), variance(분산) 존재
  4. HAVING: 그룹화된 데이터에 대해 조건 적용
  5. SELECT: 조회할 열을 선택
  6. DISTINCT: 중복된 행 제거
  7. ORDER BY: 결과를 특정 컬럼값을 기준으로 정렬
    - default=오름차순
    - 여러개 컬럼을 기준으로 정렬 가능
  8. LIMIT/OFFSET: 결과에서 반환할 행의 개수나 시작 지점을 제한- MySQL이나 PostgreSQL 등에서는 LIMIT과 OFFSET을 사용하며, Oracle에서는 ROWNUM 사용
SELECT DISTINCT            - 카테고리와 total_sales인 sum의 값이 같은 필드 중복 제거
    category, 
    SUM(price * quantity) AS total_sales
FROM sales                                
WHERE price >= 100
GROUP BY category
HAVING SUM(price * quantity) >= 3000
ORDER BY total_sales DESC
LIMIT 2 OFFSET 0;

 

SELECT FOR UPDATE

이 쿼리를 실행하면 LOCK을 획득하고,

따라서 해당 세션이 UPDATE 쿼리 후 commit 하기 전까지 다른 세션들이 해당 행을 수정하지 못하도록 한다

SELECT * FROM AUTHOR WHERE id = 2 FOR UPDATE;
 =====
UPDATE AUTHOR SET name='지현' where id = 2;
commit;
 =====
 
 업데이트(커밋 포함)가 일어나는 동안 다른 세션에서 접근 불가!

 

참조 무결성(Referential integrity )

하나의 속성이 다른 테이블의 속성을 참조하고 있다면, 참조한 해당 속성이 반드시 존재하도록 보장하는 것

=> 기본키(Primary Key)와 참조키(Foreign Key) 간의 관계가 항상 유지되도록 보장하는 것

=> PK를 참조하는 FK가 있다면, 해당 PK는 수정과 삭제가 불가능

 

e.g. 아래 BOOK 테이블에서 AUTHOR 테이블의 ID(1,2)를 참조하고 있기 때문에, 

AUTHOR 테이블의 값들은 수정/삭제가 불가능함

ID NAME AGE
1 김철수 20
2 김영희 35

AUTHOR 테이블

ID BOOK_NAME AUTHOR_ID
1 철수의 인생 에세이 1
2 영희가 알려주는 SQL 2

BOOK 테이블

 

만약 아래와 같이 수정 또는 삭제하는 쿼리를 날리시 참조 무결성을 위반한다는 에러가 발생한다

!! Referential integrity constraint violation

UPDATE AUTHOR set id = 3 WHERE name='김철수';

DELETE FROM AUTHOR where id = 1;

 

Cascade

그럼 AUTHOR 테이블의 값들은 수정/삭제 하고 싶으면 어떻게 해야할까?

이때 cascade 옵션을 이용할 수 있다.

 

cascade란?

DB의 값을 수정/삭제할 때, 해당 값을 참조하고 있는 레코드 역시 종속적으로 수정/삭제를 가능하게 함

 

ALTER TABLE AUTHOR ADD FOREIGN KEY (author_id) 
	REFERENCES BOOK (id) ON UPDATE CASCADE;

 

이후 AUTHOR의 id를 3으로 바꾸면

UPDATE AUTHOR SET id = 3 WHERE id = 1;

 

아래와 같이 AUTHOR를 참조하고 있던 값들도 자동으로 바뀌는 걸 확인할 수 있다

ID BOOK_NAME AUTHOR_ID
1 철수의 인생 에세이 3
2 영희가 알려주는 SQL 2

 

View

데이터베이스 내 테이블에서 파생된 가상의 테이블을 의미

 

특징

  • 실제로 테이블을 보여주지만, 데이터가 물리적으로 저장하는 것이 아닌라 논리적으로 존재함
    => 뷰테이블을 삭제해도 원본 테이블에 영향을 미치지 않음
  • 뷰테이블이 바라보고 있는 테이블을 수정or삭제하면 뷰테이블도 같이 반영됨
  • 실제 존재하는 테이블내 원하는 데이터만 추출하여 보여줄 수 있음
  • 생성된 뷰는 또 다른 뷰를 생성하는데 사용할 수 있음

 

3) view테이블의 단점

- 한번 정의된 뷰는 수정이 불가능하므로, 수정을 원한다면 삭제 후 재생성을 해야 한다

- 뷰에 인덱스를 구성할 수 없음

CREATE(또는 REPLACE) VIEW (뷰 이름) AS  -- CREATE 대신 REPLACE를 사용하면 같은 이름의 기존 뷰를 무시하고 대체
(
SELECT COL1, COL2, ...
FROM (원본 테이블명)
WHERE (조건)  -- WHERE, JOIN 등 테이블을 조회하는 방식, 조건 등을 일종의 함수처럼 정의하면 된다
);

-- 위처럼 한 번 생성해두면, 아래처럼 조회 가능
SELECT * FROM (뷰 이름);

-- 뷰 삭제
DROP VIEW (삭제할 뷰 이름);

 

 

Subquery (서브쿼리)

  • 한 쿼리문 내에 또 다른 쿼리문을 포함시키는 것을 의미
  • GROUP BY 절을 제외하고 모든 부분에서 사용 가능

출처: https://yeong-jin-data-blog.tistory.com/entry/%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EA%B8%B0%EB%B3%B8-%EA%B0%9C%EB%85%90

  • 반드시 괄호 안에 작성해야함 &  괄호 안에서 세미콜론(;) 사용x
  • SELECT 쿼리만 서브쿼리로 작성 가능
  • 주로 데이터를 추출하거나 필터링하기 위해 사용

위치별 서브쿼리 용어

  • SELECT 절
    • 스칼라 서브쿼리
    • 결과가 반드시 하나의 값이어야 한다
    • 일치하는 데이터가 없더라도 NULL값 리턴 가능
  • FROM 절
    • 인라인 뷰 서브쿼리
    • 결과가 반드시 하나의 테이블이어야 한다
    • 서브쿼리에 사용할 테이블은 반드시 별칭(alias)을 지정해줘야 한다
  • WHERE 절
    • 중첩 서브쿼리
    • 하나의 값을 반환하지만,
      결과값을 여러개 얻고 싶으면 any() 또는 all()을 사용

서브쿼리 관련 예문 참고하기 좋은 블로그: https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EC%A0%95%EB%A6%AC

 

[MYSQL] 📚 서브쿼리 개념 & 문법 💯 정리

서브쿼리(Subquery) 서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미한다. 서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는 내부쿼리(inner query)라

inpa.tistory.com

 

SQL Injection

- 해커에 의해 조작된 SQL 쿼리문이 데이터베이스에 그대로 전달되어 비정상적 명령을 실행시키는 공격 기법

 

공격 방법

  1. 클래식 SQL 인젝션: 사용자 입력값을 그대로 SQL 쿼리에 삽입하여 악의적인 SQL 코드를 실행하는 기본적인 공격 방법
  2. 블라인드 SQL 인젝션: 공격자가 데이터베이스의 데이터를 직접 조회하지 않고, 참/거짓의 결과를 통해 정보를 추출
    e.g. 'OR '1'='1';  사용
  3. UNION 기반 SQL 인젝션: UNION SQL 연산자를 사용하여 원래의 쿼리 결과에 추가적인 선택 결과를 결합하는 방식
  4. 에러 기반 SQL 인젝션: CAST 함수와 같은 함수를 통해 강제적으로 에러를 발생시키고, 해당 에러 메세지에서 데이터베이스의 정보 추출
  5. 스택쿼리 SQL 인젝션: 하나의 SQL 문장 뒤에 추가적은 SQL 문장을 넣어 실행
    e.g. 기존 쿼리 뒤에 DROP TABLE users;와 같은 악의적인 쿼리를 추가

방어 방법

  1. 유저에게 받은 값을 직접 SQL로 넘기지 말고, prepared statement 사용
    => 실행할 SQL 구문을 미리 Prepared Statement로 정해두고, 사용자 입력값에 따라 매핑하여 파라미터로만 전달
  2. view를 활용하여 원본 데이터베이스 테이블에는 접근 권한을 높이고,
    일반 사용자는 view로만 접근하여 에러를 볼 수 없도록 만들기
  3. 에러처리를 잘해서 테이블 정보를 사용자들에게 공개하지 않도록하기

 

SQL 안티패턴

- 성능, 보안, 가독성, 유지보수 등의 문제를 초래하는 잘못된 SQL 쿼리를 말함

  1. SELECT *  사용
    - 테이블의 구조 변경 또는 컬럼이 추가될 시 예상치 못한 결과가 발생할 수 있음
    - 필요하지 않은 컬럼까지 전부 조회하므로 네트워크 부하와 메모리 사용량을 증가시킴
    => 필요한 열만 명시적으로 선택하는 것을 추천
  2. 인덱스 남용 또는 미사용
    - 인덱스를  너무 많이 생성하여 쓰기 성능이 저하되거나, 생성하지 않아 읽기 성능이 저하되는 경우가 발생할 수 있음
    => 검색이 사용되는 열에 적절한 인덱스 추가
  3. 숫자를 문자열로 저장하는 등 잘못된 데이터 타입 사용
    => 데이터 성격에 맞는 타입을 사용하여 관련 연산을 수행가능하게 하기
  4. 쿼리에 비지니스 로직 포함
    - 로직이 데이터베이스에 종속되며 유지보수가 어려워짐
    => db는 데이터 저장 및 조회에 집중하고, 애플리케이션 코드에서 비지니스 로직 구현

 

페이지네이션

- 목적: 전체 데이터를 한번에 조회하면, 데이터가 많을 시 매우 느려지기 때문에
페이지네이션을 통해 데이터를 조금씩 나눠 가져오고 사용자가 원할 때 다음 데이터를 가져옴

 

 

참고자료

https://as-j.tistory.com/113

 

'CS > DB' 카테고리의 다른 글

[DB] Connection & DB Session  (0) 2025.01.27
[DB] 정규화  (0) 2025.01.23
[DB] 인덱스 정의, 동작 방식, 종류 등 톺아보기(MySQL 기준)  (0) 2025.01.14
[DB] JOIN 알아보기  (0) 2025.01.09
[DB] 데이터베이스 기본 개념 톺아보기  (0) 2025.01.03
CS/DB 관련 글
더 보기

티스토리툴바