📃 SQL 개념

SQL이란??

• SQL은 관계형 데이터 베이스 시스템(RDBMS)에서 자료를 저장 및 처리하기 위해 설계된 언어입니다.
• SQL과 C언어와 같은 프로그래밍 언어 둘 다 프로그래밍 언어라고 볼 수 있습니다.
• 차이점은 SQL은 RDBMS에 저장된 데이터를 조회 및 가공하는데 최적의 성능과 편의를 제공하는 반면, C언어와 같은 프로그래밍 언어는 네트워크 통신, 파일 입출력 등과 같이 애플리케이션을 개발하는데 편의를 제공합니다.

---

SQL 실행 과정

Query cache(쿼리 캐시) - SQL 실행 결과를 메모리에 캐싱하는 역할을 하며 동일한 SQL 실행시 이전 결과를 즉시 반환한다. 테이블의 데이터가 변경되면 캐싱된 데이터를 삭제해야 하는데 이는 동시 처리 성능 저하를 발생시켜서 MYSQL 8.0부터 쿼리 캐시는 완전히 제거되었다.

Parser(파서) - 파서는 사용자의 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 한다. 쿼리의 기본 문법 오류는 이 단계에서 발견되며 사용자에게 오류 메시지를 전달한다.

Pre-processor(전처리기) - 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행한다. 존재하지 않거나 권한 상 사용할 수 없는 개체의 토큰은 걸러지게 됩니다.

Optimizer(옵티마이저) - 성능 최적화와 관계가 있다. 규칙 기반 최적화는 옵티마이저에 내장된 우선 순위에 따라 실행 계획을 수립하는 방식이고 비용 기반 최적화는 작업의 비용과 대상 테이블의 통계 정보를 활용해서 실행 계획 수립하는 방식이다.

Execution engine(실행 엔진) - 옵티마이저가 만든 계획대로 스토리지 엔진을 호출해 쿼리를 수행하는 역할이다.

Handler API(핸들러 API) - 쿼리 실행기에서 데이터를 쓰거나 읽기 위해 스토리지 엔진과 데이터를 주고받는 역할을 하는 API이다. 저장 엔진과의 상호 작용을 도와서 서버가 다양한 저장 엔진을 지원할 수 있도록 한다.

Storage Engine(저장 엔진) - 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다. 대표적으로 InnoDB, MYISAM 스토리지 엔진이 있다. 플러그인 형태로 제공되기 때문에 사용자는 원하는 스토리지 엔진을 골라서 사용할 수 있다. 핸들러 API에 의해 동작하며 핸들러라고 불린다.

---

DML/DDL/DCL

DML(Data Manipulation Language)

데이터베이스의 내부 데이터를 관리하기 위한 언어이다. 데이터를 조회, 추가, 변경, 삭제 등의 작업을 수행하기 위해 사용된다.

또한 DML은 조작하려는 테이블(Target)을 메모리 버퍼 위에 올려두고 변경을 수행하기 때문에 실시간으로 반영이 되지 않는다. Commit 명령어를 통해 Transaction을 종료해야 해당 변경 사항이 테이블에 반영된다.

그러나 SQL Server의 경우는 DML 경우도 Auto Commit으로 처리되기 때문에 실제 테이블에 반영하기 위해 Commit 명령어를 입력할 필요가 없다.

• SELECT: 데이터 베이스에서 데이터를 검색한다. 옵션 중에 DISTINCT는 중복된 데이터가 있는 경우 1건만 출력하고 옵션 All은 기본 옵션이며 중복되는 데이터도 모두 출력한다.
• INSERT: 테이블에 데이터를 추가하는 역할
• UPDATE: 테이블 내에 존재하는 데이터를 수정하는 역할
• DELETE: 테이블에서 데이터를 삭제하는 역할

테이블 전체를 삭제하는 경우, DELETE TABLE 보다 시스템 부하가 적은 TRUNCATE TABLE을 권장한다. 단, TRUNCATE TABLE의 경우는 삭제된 데이터의 로그가 없어 ROLLBACK이 불가하다.
하지만 SQL Server에서 TRUNCATE TABLE을 사용할 경우 사용자가 임의적으로 트랜잭션을 시작한 후 복구를 원한다면 ROLLBACK문을 이용해 테이블을 원 상태로 되돌릴 수 있다.

DDL(Data Definition Language)

테이블과 컬럼을 정의하는 명령어로 생성, 수정, 삭제 등의 데이터베이스 구조 또는 스키마를 결정하는 역할을 담당한다.

DDL은 직접 데이터베이스의 테이블에 영향을 미치기 때문에 명령어를 입력하는 순간 작업이 즉시 반영(Auto Commit)되므로 사용할 때 주의가 필요하다.

• CREATE: 테이블을 생성하는 역할
• ALTER: 테이블의 구조를 수정하는 역할
• DROP: 테이블을 삭제하는 역할(테이블의 모든 데이터와 구조 삭제)
• RENAME: 테이블의 이름을 변경하는 역할
• TRUNCATE: 테이블을 초기화하는 역할

DCL(Data Control Language)

DCL을 사용하면 데이터베이스에 접근하여 읽거나 쓰는 것을 제한할 수 있는 권한을 부여하거나 박탈할 수 있다.

불법적인 사용자로부터 데이터를 보호하기 위한 데이터 보안의 역할을 수행한다.

직접 데이터베이스의 테이블에 영향을 미치기 때문에 즉시 완료(AUTO COMMIT)되는 것을 유의해야 한다.

• GRANT: 데이터베이스에 대한 사용자의 액세스 권한 제공
• REVOKE: GRANT 명령으로 주어진 엑세스 권한 철회

---

CASCADE

CASCADE는 외래 키 제약조건과 관련해 사용되는 옵션이다. CASECADE 옵션은 외래 키 제약 조건과 관련된 테이블 간 데이터 변경 및 삭제 동작에 영향을 미치는 방법을 설정하는 것입니다. 참조 무결성 제약을 관리하는데 사용된다.

CASCADE UPDATE
테이블에서 외래 키 값이 변경된다면 참조하는 다른 테이블의 해당 외래 키 값들도 함께 변경된다.

CASCADE DELETE
테이블의 레코드가 삭제되면 해당 레코드를 외래키로 참조하고 있는 다른 테이블의 레코드도 함께 삭제된다. 외래 키가 가지고 있는 레코드가 삭제되면 해당 레코드를 참조하는 모든 레코드가 자동 삭제된다.

---

VIEW

뷰는 하나 또는 그 이상의 테이블을 조인해 만든 새로운 가상 테이블이다. 실제로 테이블을 생성한 것이 아니라서 물리적으로 존재하지 않는다. 특정 정보만 제공하고 싶은 경우(접근 제어)나 복잡한 쿼리를 가상 테이블로 만들어 간편하게 활용할 수 있다(관리 용이).

---

SELECT 절의 처리 순서

FROM 절
쿼리의 첫번째 실행 순서는 FROM절이다. FROM 절에서 조회할 테이블을 지정하고 Join을 실행하여 하나의 가상 테이블로 결합한다.

WHERE 절
테이블에서 조건에 맞는 레코드(행)를 필터링한다.

GROUP BY 절
선택한 칼럼을 기준으로 그룹핑한다.

HAVING 절
그룹핑 후에 각 그룹에 사용되는 조건절이다.
HAVING 절의 조건을 WHERE절에 사용할 수 있다면 WHERE절에 사용하는 것이 좋다. HAVING 절은 각 그룹에 조건을 걸기 때문에 성능이 떨어진다.

SELECT 절
여러 조건을 처리한 후 어떤 컬럼을 출력할지 선택한다.

ORDER BY 절
select 문의 실행 결과를 정렬하는 것이다.

LIMIT 절
조회된 결과의 개수를 제한하는 것이다.

---

SELECT ~FOR UPDATE

SELECT ~ FOR UPDATE 구문은 동시성 제어를 위해 특정 데이터에 대해 LOCK을 거는 기능입니다. 이를 통해서 선택한 행이 다른 트랜잭션에서 동시에 수정되는 것을 방지할 수 있다.

해당 행에 락을 걸었기 때문에 다른 트랜잭션들이 해당 행을 사용할 수 없다. 그래서 락이 오래 지속되지 않도록 주의해야 한다.

해당 트랜잭션 내에서만 적용되며 해당 트랜잭션이 커밋되거나 롤백되면 락이 해제된다.

---

GROUP BY / ORDER BY

GROUP BY
데이터를 그룹화 하는데 사용된다. 데이터베이스에서 특정 열의 값에 따라 결과를 그룹화하여 집계 또는 통계 작업을 수행하는데 쓰인다.

ORDER BY
데이터베이스에서 검색된 결과(selelct)를 특정 열을 기준으로 정렬하는데 사용된다.

---

JOIN

INNER JOIN
공통적인 부분만 SELECT하고 2개의 테이블의 컬럼값을 결합해 새로운 결과 테이블을 생성

OUTER JOIN
두 테이블이 가지고 있는 전체 부분을 SELECT 한 것. 조인 대상 테이블에서 특정 테이블의 데이터가 모두 필요한 상황에서 사용한다.

LEFT OUTER JOIN
LEFT OUTER JOIN은 공통적인 부분과 LEFT 테이블에 있는 데이터를 SELECT하는 것이다.

RIGHT OUTER JOIN
마찬가지로 RIGHT OUTER JOIN은 공통적인 부분과 RIGHT 테이블에 있는 데이터만 SELECT 하는 것이다.

CROSS JOIN
두 개 이상의 테이블 간의 모든 가능한 조합을 생성하는데 사용된다. 그래서 집합이 매우 커질 수 있기 때문에 사용에 주의해야 한다.

---

서브쿼리

SQL에서 사용되는 쿼리 안에 포함된 또 다른 쿼리를 뜻한다. 서브 쿼리는 다른 쿼리의 조건이나 연산에 사용되며 복잡한 데이터 요구사항을 해결하기 위해 유용하게 사용된다.
서브쿼리는 위치에 따라 SELECT절, WHERE절, FROM절 3가지로 나뉜다.

SELECT절
select 절에 사용되는 서브 쿼리는 하나의 열처럼 사용된다. 하지만 데이터의 양이 많아질수록 실행속도가 느려지기 때문에 잘 사용되지 않는다.

WHERE절
서브 쿼리를 사용해 조건에 만족하는 데이터를 필터링할 수 있다.

FROM절
서브 쿼리를 사용해 하나의 테이블처럼 사용된다.

---

DROP, TRUNCATE, DELETE의 차이

DROP은 데이터베이스 객체를 완전히 삭제해 데이터와 정의가 모두 사라진다. 삭제된 데이터는 되돌릴 수 없다.

TRUNCATE는 테이블의 모든 데이터를 삭제해 테이블의 구조는 유지된다. 삭제된 데이터는 되돌릴 수 없으며 대규모 데이터를 삭제할 때 유용하다.

DELETE는 테이블에서 조건에 맞는 행만 삭제하는 것이다. 삭제된 데이터는 트랜잭션에서 되돌릴 수 있다.

---

DISTINCT

DISTINCT를 사용해서 쿼리 결과에서 중복된 값을 제거할 수 있습니다. 중복된 값을 가진 행이 하나만 결과에 나타날 수 있습니다.

중복을 제거하는 과정에서 쿼리의 성능에 영향을 주기 때문에 데이터가 크다면 성능에 문제가 발생할 수 있습니다.

---

SQL Injection

악의적인 사용자가 임의의 SQL문을 주입하고 실행되게 하여 데이터베이스가 비정상적인 동작을 하도록 조작하는 행위이다.

예방 방법

준비된 문장(Prepared Statements)사용: 준비된 문장을 사용하게 되면 사용자의 입력 값이 데이터베이스의 파라미터로 들어가기 전에 DBMS가 미리 컴파일하여 실행하지 않고 대기합니다. 이를 통해서 단순 문자열을 인식해 공격자의 의도대로 작동하지 않는다.

입력값의 유효성 검사: 사용자로부터 받은 입력값을 검사하여 예상하지 못한 문자나 SQL 구문을 제거하거나 처리한다.

에러 메시지 노출 금지: 데이터베이스 에러 발생 시 따로 처리해 주지 않았다면 에러가 발생한 쿼리문과 함께 에러에 관한 내용을 반환해준다. 여기서 테이블명 및 컬럼명 그리고 쿼리문이 노출될 수 있기 때문에 오류 발생 시 사용자에게 보여주는 페이지를 구현해야 한다.