[데이터베이스] SQL 및 관련 개념

조수훈·2023년 9월 21일
CSDatabaseTILsql
0

DataBase

목록 보기
7/11
post-thumbnail

SQL(Structured Query Language)

구조적 쿼리 언어(SQL)은 관계형 데이터베이스에서 정보를 저장, 업데이트, 제거 , 검색을 할수 있으며, 데이터베이스의 성능을 유지 관리하고 최적화하는 쿼리 언어입니다.
SQL 은 쿼리 언어로, 테이블에서 데이터를 추출하고 조작하는데 집중하므로, 운영체제, 임베디드 시스템, 게임개발등 응용 프로그램 등에 사용되는 C언와는 다르다고 할수있습니다.

SQL 쿼리 실행 과정

쿼리가 처리되는 과정은 크게 3가지 단계가 있습니다.

  1. 구문 분석(Parsing)
    Query 의 문법적, 의미적 오류를 체크하고, 재사용 가능한 SQL 인지 확인합니다, 또한 쿼리실행계획(Execution plan)을 수립합니다.

  2. 쿼리 실행(Execution)
    쿼리 실행 단계에서는 Parsing 을 통해 생성된 실행 계획을 기반으로 실제로 데이터베이스 작업을 수행합니다. 이 단계에서는 데이터를 읽거나 수정하며, 쿼리의 목적에 따라 다양한 작업을 수행합니다.

  3. 패치(Fetch)
    실행된 값을 가져오는 절차입니다. Select만 해당됩니다. 반환하는 값이 없는 Insert, Update, Delete 는 미해당 됩니다.

SQL문의 종류(DDL, DML, DCL)

DDL(Data Definition Language) - 데이터 정의어

데이터베이스를 정의하는 언어를 말하며 테이블을 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 언어를 말합니다.

  • CREATE : 데이터 베이스, 테이블 등을 생성하는 역할을 합니다.
  • ALTER : 테이블을 수정하는 역할을 합니다.
  • DROP : 데이터베이스, 테이블을 삭제하는 역할을 합니다.
  • TRUNCATE : 테이블을 초기화 시키는 역할을 합니다.

DML(Data Manipulation Language) - 데이터 조작어

정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어를 말합니다.

  • SELECT : 데이터를 조회하는 역할을 합니다.
  • INSERT : 데이터를 삽입하는 역할을 합니다.
  • UPDATE : 데이터를 수정하는 역할을 합니다.
  • DELETE : 데이터를 삭제하는 역할을 합니다.

DCL(Data Control Language) - 데이터 제어어

데이터베이스에 접근하는 권한을 주거나 변경하는 등의 역할을 하고, 트랜잭션을 명시하거나 조작할수 있는 언어를 말합니다.

  • GRANT : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한을 부여합니다.
  • REVOKE : 특정 데이터베이스 사용자에게 특정 작업에 대한 권한을 박탈, 회수합니다.
  • COMMIT : 트랜잭션의 작업이 정상적으로 완료되었음을 관리자에게 알려줍니다.
  • ROLLBACK : 트랜잭션의 작업이 비정상적으로 종료 되었을때 원래의 상태로 복구합니다.

무결성

무결성이란 데이터베이스에서 저장된 값들에 대하여 여러가지 제한을 통하여 데이터에 대한 신뢰를 보장하게 하여 일관성을 유지 시켜주는 특징을 말합니다.

영역 무결성(Domain Integrity)

  • 한 컬럼에 대해서 NULL 의 허용 여부와 타당한 데이터 값들을 지정합니다.
  • 자료형, 규칙과 제약, 값 범위 등을 제한합니다.

참조 무결성(Referential Integrity)

  • 기본 키와 참조 키 간의 관계가 항상 유지됨을 보장합니다.
  • 참조되는 테이블의 행을 이를 참조하는 참조키가 존재하는 한 삭제될 수 없으며, 기본키도 변경 될 수 없습니다.

개체 무결성(Entity Intgerity)

  • 테이블에 있는 모든 행들이 유일한 식별자를 가질 것을 요구합니다.

Cascade

참조 무결성 제약 조건이 깨지는 명령을 실행할 경우 일반적으로 명령을 거부합니다. 하지만 Cascade 옵셔을 통해 참조 무결성이 깨지는 것을 허용하면서 관련된 모든 값을 업데이트 하거나 삭제할수 있도록 할 수 있습니다.

뷰(View)

뷰는 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체이며, 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블입니다.
뷰는 저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 간주됩니다.
뷰는 데이터 보정작업, 처리과정 시험 등 임시적인 작업을 위한 용도로 활용됩니다.
뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화 합니다.

  • 여러 테이블의 조인과 그룹핑과 같은 복잡한 쿼리를 뷰에 저장시켜놓고, 저장한 뷰의 정보만 가져와 더 쉽고 편리하게 사용할 수 있습니다. (일종의 함수 형태)
  • 데이터 보안에서도 유리한데, 뷰를 보면 컬럼과 데이터만 공개되므로 원천 테이블 정보를 알 수 없습니다.

SELECT 절의 쿼리 실행 순서

sql 쿼리 문을 실행하는데에는 순서가 존재합니다. SELECT 쿼리문은 크게 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 총 6단계를 거칩니다.

1. FROM + JOIN
FROM 절에서는 조회할 테이블을 지정합니다. 이후 JOIN 을 실행하여 하나의 가상 테이블로 결합합니다.
2. WHERE
WHERE 절에서는 테이블에서 조건에 맞는 데이터를 필터링합니다.
3. GROUP BY
GROUP BY 절에서는 선택한 칼럼을 기준으로 그룹핑합니다.
4. HAVING
HAVING 절은 그룹핑 후에 각 그룹에 사용되는 조건 절입니다. 쉽게 말해 그룹을 필터링한다고 생각하면 됩니다.
5. SELECT
SELECT 절은 여러 조건들을 처리한 후 남은 데이터에서 어떤 열을 출력해줄지 선택합니다.
6. ORDER BY
어떤 열까지 출력할지 정했다면 행의 순서를 어떻게 보여줄지 정렬 해줍니다.

SELECT FOR UPDATE

SELECT FOR UPDATE 쿼리는 가정 먼저 LOCK을 획득한 SESSION의 SELECT 된 ROW들이 UPDATE 쿼리후 COMMIT 되기 이전까지 다른 SESSION들은 해당 ROW들을 수정하지 못하도록 하는 기능입니다. (동시성 제어)

INNER JOIN, OUTER JOIN

INNER JOIN

두 테이블의 교집합을 얻습니다.
두 테이블을 연결할 때 가장 많이 사용하는 것이 내부 조인입니다. 그냥 조인이라고 부르면 내부 조인을 의미합니다.

OUTER JOIN

내부 조인은 두 테이블에 모두 데이터가 있어야 교집합이 나오지만, 외부 조인은 한쪽에만 데이터가 있어도 합집합으로 데이터가 나오게 됩니다.

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

LEFT OUTER JOIN: 왼쪽 테이블의 모든 값이 출력되는 조인입니다.
RIGHT OUTER JOIN: 오른쪽 테이블의 모든 값이 출력되는 조인입니다.
FULL OUTER JOIN: 왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인입니다.

CROSS JOIN

CROSS JOIN 은 교차 조인이라고 하며, 두 테이블의 모든 행을 조합하여 결과를 생성합니다. CROSS JOIN 은 카테시안 곱 또는 카테시안 조인이라고 부르기도 한다.
CROSS JOIN 은 별도의 조인 칼럼이 없고, 두 테이블의 모든 행이 서로 한번씩 조인이 된다.

서브쿼리(Subquery)

서브쿼리란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미합니다.
서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브 쿼리는 내부쿼리라고도 부릅니다.
서브쿼리는 괄호() 로 감싸져서 표현됩니다.

DROP, TURNCATE, DELETE

DROP

테이블 자체를 완전히 날려버리는 방식 입니다. 테이블 제차가 모두 지워지며, 해당 테이블에 생성되어있던 모든 인덱스도 사라집니다. 자동 COMMIT이 되는 명령어이기 때문에, 이미 지운 데이터는 되돌릴 수 없습니다.

TRUNCATE

전체 데이터를 한번에 삭제하는 방식입니다.
TRUNCATE TABLE 을 하면 CREATE TABLE을 한 직후의 상태와 같습니다.
자동 COMMIT 이 되는 명령어이기 때문에, 이미 지운 데이터는 되돌릴수 없습니다.

DELETE

WHERE 절을 사용하여 테이블에 있는 데이터를 하나하나 선택하여 제거하는 방식입니다.
WHERE절을 사용하지않고 테이블의 모든 데이터를 삭제하더라도, 내부적으로는 한줄 한줄 일일히 제거하는 과정을 거칩니다.
DELETE된 데이터는 COMMIT 명령어를 사용하기 전이라면, ROLLBACK 명령어를 통해 되돌릴 수 있습니다.

DISTINCT

쿼리문에서 데이터의 중복을 제거할 때는 DISTINCT 라는 키워드를 사용한다. DISTINCT 를 사용하면 쿼리문을 분석할때 중복을 제거한 결과를 반환한다는 것을 직관적으로 파악이 가능하다. 하지만 상황에 따라서 GROUP BY 절을 사용하여 중복을 제거할 수도 있다.
DISTINCT 키워드를 사용하여 데이터 중복을 제거 할때는 SELECT 절에 DISTINCT 키워드만 명시하면 되므로 쿼리문이 복잡하지 않고 간결하다.

SELECT DISTINCT
       a.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.deptno = b.deptno
   AND a.sal > 1500

SQL Injection, 예방 방법

SQL Injection

SQL Injection 이란 응용 프로그램 보안 상의 허점을 의도적으로 이용해, 조작한 SQL쿼리문을 실행되게 함으로써 데이터베이스를 비정상적으로 조작하는 공격 기법입니다.
공격에 성공하게 되면 조직 내부의 민감한 데이터나 개인 정보를 획득할 수 있으며, 심각한 경우에는 조직의 데이터 전체를 장악하거나 완전히 손상시킬 수 있습니다.

예방 방법

  1. ORM 사용
    ORM을 사용하여 데이터베이스 쿼리를 생성하는 것이 안전합니다. ORM 은 대부분 SQL Injection 을 방지하는 기능을 내장하고 있습니다.
  2. 입력 값에 대한 검증
    검증 로직을 추가하여 미리 설정한 특수문자들이 들어왔을 때 요청을 막아낸다.
  3. Error Message 노출 금지
    데이터베이스 에러 발생 시 따로 처리를 해주지 않았다면, 에러가 발생한 쿼리문과 함께 에러에 관한 내용을 반환해 줍니다. 여기서 테이블명, 컬럼명, 쿼리문이 노출이 될 수 있기 때문에, 오류발생 시 사용자에게 보여줄 수 있는 페이지를 따로 제작하거나 메시지박스를 띄우도록 해야합니다.
  4. Prepared Statement 구문사용
    SQL 쿼리를 생성할 때 사용자 입력을 직접 쿼리 문자열에 삽입하지 말고, 매개 변수화된 쿼리(prepared statement)를 사용합니다. 이를 통해 입력 데이터가 쿼리에 주입될 수 없습니다.

SQL 안티패턴

SQL 안티 패턴이란 SQL 쿼리나 데이터베이스 설계에서 흔히 발생하는 잘못된 패턴을 말합니다. SQL 안티 패턴은 성능 저하, 데이터 무결성 손상, 보안 취약점 등의 문제를 야기할 수 있습니다. 따라서 SQL 안티 패턴을 인식하고 피하는 것이 중요합니다.

SELECT * FROM

SELECT * FROM 은 테이블의 모든 컬름을 조회하는 쿼리입니다. 이 쿼리는 테이블의 구조가 변경되거나 컬럼이 추가되면 예상치 못한 결과를 가져올 수 있습니다. 또한 필요하지 않은 컬럼까지 조회하므로 네트워크 부하와 메모리 사용량을 증가시킵니다. 따라서 필요한 컬럼만 명시적으로 조회하는 것이 좋습니다.

사용자 입력을 직접 쿼리 문자열에 삽입

사용자 입력을 직접 동적 쿼리로 생성하는 것은 SQL 인젝션 공격에 취약합니다.
SQL 인젝션 공격이란 악의적인 사용자가 입력값에 SQL 구문을 삽입하여 데이터베이스에 접근하거나 조작하는 공격입니다.
사용자 입력을 직접 동적 쿼리로 생성하면 입력값을 검증하지 않고 쿼리에 포함시키므로 SQL 인젝션 공격에 노출될 수 있습니다. 따라서 Prepared Statement 구문을 사용하는 것이 좋습니다.

잘못된 데이터 타입의 사용

데이터 타입은 데이터의 종류와 크기를 정의하는 것입니다. 데이터 타입을 잘못 사용하면 데이터의 정확성과 효율성이 떨어질 수 있습니다.
예를 들어, 날짜나 시간을 문자열로 저장하면 날짜나 시간 관련 함수를 사용할 수 없고, 정렬이나 비교도 제대로 되지 않습니다. 또한, 숫자를 문자열로 저장하면 숫자 관련 연산이나 집계가 불가능하고, 공간도 낭비됩니다. 따라서 데이터의 성격과 범위에 맞는 데이터 타입을 사용하는 것이 좋습니다.

페이지네이션 구현

페이지네이션을 SQL 로 구현하는 방법에는 OFFSET 과 LIMIT를 사용하는 방법과 , WHERE 절과 인덱스를 사용하는 방법이 있습니다.

OFFSET 과 LIMIT 사용

OFFSET을 사용하여 페이지네이션을 구현하는 경우, 데이터베이스는 시작 지점부터 일정한 수의 행을 건너뛴 후 데이터를 반환합니다. 예를 들어,한 페이지가 20개이고, 3번째 페이지를 가져오려면 처음 40개의 항목을 건너뛴 다음 20개의 항목을 가져옵니다.
이 방법의 장점은 쿼리가 간단하고 직관적이며, 대부분의 데이터베이스 시스템에서 지원됩니다.
그러나 대량의 데이터를 처리할 때는 성능 문제가 발생할 수 있습니다. OFFSET 값이 커질수록 데이터베이스는 더 많은 행을 건너뛰어야 하므로 데이터베이스 부하가 증가할 수 있습니다.

SELECT * 
FROM posts 
LIMIT 20 OFFSET 40;

WHERE 절과 인덱스 사용

WHERE 절과 인덱스를 사용하여 페이지네이션을 구현하는 경우, 데이터베이스는 조건에 맞는 행을 직접 선택합니다. 예를 들어, 페이지 크기가 20이고 페이지 번호가 3인 경우, 조건을 사용하여 41번째부터 60번째 행을 선택합니다.
이 방법은 대체로 성능이 더 우수합니다. WHERE 절과 인덱스를 사용하면 데이터베이스는 특정 페이지의 데이터만 가져오므로 OFFSET을 사용하는 것보다 더 효율적입니다.
그러나 쿼리가 더 복잡해지고 더 많은 노력이 필요할 수 있으며, 모든 데이터베이스 시스템이 이 방법을 지원하지는 않을 수 있습니다.

SELECT * 
FROM posts 
WHERE id <= 41 AND id > 60 
ORDER BY id DESC;
profile
조수훈
잊지 않기 위해 기록하기
이전 포스트

[데이터베이스] MySQL 및 InnoDB

다음 포스트

[데이터베이스] 인덱스(1)

0개의 댓글