데이터베이스 스터디 2주차

SQL과 범용 프로그래밍 언어의 차이는?

SQL은 데이터베이스 전용 언어이다.

데이터베이스의 데이터와 메타 데이터를 생성하고 처리하는 문법만 가지고 있다.

SQL은 사용자가 처리를 원하는 데이터가 무엇인지만 제시하고 데이터를 어떻게 처리해야 하는지를 언급할 필요가 없어

비절차적 데이터 언어의 특징을 띤다고 할 수 있다.

 

C언어 같은 프로그래밍 언어는 범용 프로그래밍 언어로,

시스템 프로그래밍, 응용 프로그램 개발, 게임 개발 등 다양한 분야에서 사용된다.

 

이처럼 SQL과 프로그래밍 언어는 목적에서 차이가 있다.

SQL의 실행 과정

SQL이 실행되는 과정은 크게 파싱, 최적화, 실행 세 단계로 나눌 수 있다.

 

1. 파싱

• SQL문장을 토큰 단위로 나눠서 문장의 구조를 분석하고, 문법적인 오류를 찾아낸다.
• 토큰은 SQL 문법에서의 기본 단위로, 예를 들면 키워드, 식별자, 연산자 등이 될 수 있다.

2. 최적화:

• 쿼리 옵티마이저는 파싱된 SQL 문장을 분석하고 다양한 실행 계획은 고려한다.
• 이 중에서 최소 비용을 갖는 최적의 실행 계획을 선택하고, 이를 실행 계획 트리로 변환한다.

3. 실행:

• 작업을 실행하고 결과를 사용자에게 반환한다.

최적화 단계에서는 데이터베이스 엔진이 효율적인 실행 계획을 선택하기 위해 인덱스 사용, 조인 순서 변경 등의 다양한 최적화 기법을 적용한다.

SQL 기능

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

테이블에 데이터를 검색, 삽입, 수정, 삭제하는데 사용하며 SELECT, INSERT, DELETE, UPDATE 문 등이 있다.

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

테이블이나 관계의 구조를 생성하는데 사용하며 CREATE, ALTER, DROP 문등이 있다.

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

데이터의 사용 권한을 관리하는데 사용하며 GRANT, REVOKE 문 등이 있다.

 

 

참조 무결성

참조 무결성은 관련 있는 테이블 간의 일관성을 말한다.
테이블간의 관계는 기본키와 외래키로 표현되기 때문에 참조 무결성을 지키기 위해서는
참조 무결성 제약조건(=외래키 제약조건)에 따라야 한다.

외래키의 제약조건

1. 부모 릴레이션(참조되는 릴레이션)의 기본키와 자식 릴레이션(참조하는 릴레이션)의 외래키는 도메인이 동일해야 한다.

2. 외래키는 부모 릴레이션의 기본키에 존재하는 값이어야 한다. (다만 NULL값은 사용이 허용될 수 있다.)

3. 자식 릴레이션에서 참조하고 있는 값을 부모 릴레이션에서 삽입, 삭제, 수정하기 위해서는 제약조건 옵션에 따라야 한다.

 

삽입

자식 릴레이션에 새로운 투플을 삽입할 때에는

1. 도메인 무결성 제약 조건을 확인

2. 개체 무결성 제약조건에 위배되는 값이 없는지 확인

3. 외래키 값이 부모 릴레이션의 기본기에 존재하는지 확인한다

4. 모든 제약조건에 위배되지 않는다면 삽입 (NULL값을 허용했다면 외래키값이 없어도 가능)

 

삭제

명령어	의미
RESTRICTED	자식 릴레이션에서 참조하고 있을 경우 부모 릴레이션의 삭제 작업 거부
CASCADE	자식 릴레이션의 관련 투플을 같이 삭제
DEFAULT	자식 릴레이션의 외래키를 디폴트 값으로 변경
NULL	자식 릴레이션의 외래키를 NULL값으로 변경

 

 

수정

수정은 삭제와 삽입 명령이 연속해서 수행된다고 보면 된다

부모 릴레이션의 수정이 일어날 경우 삭제 옵션에 따라 처리된 후 문제가 없으면 다시 삽입 제약 조건에 따라 처리된다.

 

ON DELETE CASCADE

- 부모 테이블에서 데이터를 삭제할 경우 연결된 자식테이블의 데이터도 함께 삭제된다.

ON UPDATE CASCADE

- 부모 테이블에서 데이터가 수정될 경우 연결된 자식테이블의 데이터도 함께 수정된다.

 

VIEW

DB에 존재하는 가상 테이블이다.

실제 테이블처럼 행과 열을 가지고 있지만 실제로 데이터를 저장하고 있지는 않고, 데이터를 보여주는 역할만 수행한다.

SQL로 생성한 데이터를 뷰를 통해 가상 테이블을 만들어 편리하고 빠르게 데이터 조회 가능하다.

 

VIEW 장점

- 미리 정의 된 뷰를 일반 테이블처럼 사용할 수 있고 재사용 가능해서 편리하다.

- 특정 정보만 제공할 수 있어 보안성이 높다.

 

VIEW 단점

- 한번 정의된 뷰는 변경할 수 없다.

- 독립적인 인덱스를 가질 수 없다.

- 뷰로 구성된 내용에 대한 삽입, 삭제, 수정에 제약이 따른다.

SQL 질의 처리 순서

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

SELECT FOR UPDATE

특정 행을 선택한 후, 해당 행에 대한 잠금(lock)을 설정하는 SQL 구문

동시성 제어를 위해 사용한다.

여러 트랜잭션 간에 데이터의 일관성을 유지하고, 동시에 여러 트랜잭션이 동일한 데이터를 변경하려고 할 때 충돌을 방지할 수 있다.

GROUP BY

지정된 열에서 동일한 값을 가진 행을 하나의 그룹으로 묶어주는데 사용하는 SQL 명령어

COUNT(), MAX(), MIN(), SUM(), AVG() 등 집계 함수와 함께 사용된다.

GROUP BY에서 SELECT 할수 있는 칼럼은 GROUP BY에 나열된 칼럼 혹은 집계함수이다.

 

ORDER BY

ORDER BY 절은 SQL 쿼리 결과를 정렬하는 데 사용된다.

정렬은 주로 특정 열을 기준으로 오름차순(ASC)이나 내림차순(DESC)으로 수행됩니다.

ASC가 기본으로 적용된다.

SELECT column1, column2, ... 
FROM table_name 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...;

 

 

HAVING

HAVING절은 GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다.

 

여러 가지 JOIN

INNER JOIN: 두 테이블 간에 일치하는 행만을 반환

SELECT * 
FROM table1 
INNER JOIN table2 ON table1.column = table2.column;

 

OUTER JOIN: 일치하지 않는 행도 결과에 포함

 

LEFT OUTER JOIN: 왼쪽 테이블의 모든 행을 포함하고, 오른쪽 테이블과 일치하는 경우에는 해당 행을 포함

 

RIGHT OUTER JOIN: 오른쪽 테이블의 모든 행을 포함하고, 왼쪽 테이블과 일치하는 경우에는 해당 행을 포함

 

CROSS JOIN은 두 테이블 간의 모든 가능한 조합을 반환

결과는 첫 번째 테이블의 각 행과 두 번째 테이블의 모든 행의 결합

 

서브쿼리

서브쿼리는 다른 쿼리 안에 포함된 쿼리로, 외부 쿼리의 조건에 따라 결과가 동적으로 변경된다.

서브쿼리는 SELECT, FROM, WHERE, HAVING, 등 여러 부분에서 사용된다.

SELECT column1 
FROM table1 
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);



DROP

테이블 자체를 삭제한다.

테이블 구조와 데이터를 모두 삭제하기 때문에 주의해야 한다.

DROP TABLE table_name; TRUNCATE: 

TRUNCATE

테이블의 모든 행을 삭제하지만 테이블 구조는 유지한다.

롤백이 불가능하며 빠르게 수행된다.

TRUNCATE TABLE table_name; 

DELETE

테이블의 행을 삭제한다.

롤백이 가능하며 조건에 따라 특정 행을 삭제할 수 있다.

DELETE column1
FROM table_name 
WHERE condition;  

DISTINCT

DISTINCT는 중복된 값을 제거하여 결과 집합에서 고유한 값을 반환한다.

SELECT DISTINCT column1, column2, ... 
FROM table_name; 

 

SQL Injection

 

SQL Injection은 악의적인 사용자가 입력 필드에 SQL 코드를 삽입하여 데이터베이스를 공격하는 것이다.

예방하기 위해 입력 값을 검증하고, 파라미터화된 쿼리 또는 프리페어드 스테이트먼트를 사용하는 것이 중요하다.

 

SQL 안티패턴

 

SELECT * : 모든 열을 선택하는 것은 불필요한 데이터 전송을 유발할 수 있기 때문에

필요한 열만 선택하는 것이 좋다.

 

Non-SARGable WHERE절:

SARGable(Search ARGument Able)이 아닌 WHERE 절은 인덱스를 효과적으로 사용하지 못하게 할 수 있다.

 

페이지네이션을 구현할 때의 쿼리

페이지네이션을 구현할 때는 LIMIT 및 OFFSET을 사용하여 특정 범위의 결과를 가져온다.

예를 들어, 10개씩 가져오는 경우:

SELECT column1, column2, ... 
FROM table_name LIMIT 10 OFFSET 0; -- 첫 번째 페이지 

SELECT column1, column2, ... 
FROM table_name LIMIT 10 OFFSET 10; -- 두 번째 페이지

 

 

MySQL에서 페이지네이션을 구현하는 기본적인 방법은 LIMIT 및 OFFSET을 사용하는 것이지만,

대규모 테이블에서는 OFFSET을 사용할 때 성능 이슈가 발생할 수 있다.

 

더 효율적인 페이지네이션을 위해 OFFSET을 피하고, LIMIT과 OFFSET 대신에 WHERE 조건을 사용하여 페이지 범위를 지정할 수 있다.

SELECT *
FROM users
WHERE user_id > 0  -- 적절한 기준 값으로 변경
ORDER BY user_id
LIMIT @PageSize;