SQL

이승훈·2024년 1월 18일

SQL에 대해서 설명해주세요. C언어와 같은 프로그래밍 언어와 어떤차이가 있나요?

SQL이란?

  • RDBMS에서 데이터를 조작하는 명령어
  • 그 목적이 한정적이기 때문에 비교적 문법이 간결하고 사용하기 쉽다.

프로그래밍 언어와 차이점은?

  • 크게 보면 '목적'과 '실행 방식'이 있다.
  • 목적의 경우 SQL은 RDBMS에서 데이터를 다루기 위해 사용된다는 하나의 목적을 가지지만, 프로그래밍 언어는 여러 목적(시스템 개발, 프로그램 개발, 알고리즘 개발, AI 모델 학습, 기타 등등)으로 사용된다.
    • 그래서 보통 프로그래밍 언어는 SQL에 비해 더 복잡하며 배우기 어렵다.
  • 실행 방식의 경우 SQL은 사용자가 어떤(WHAT) 데이터를 원하는지만 명시하면, 옵티마이저가 내부적으로 최적의 실행 계획을 결정하여 그 '과정'(HOW)을 자동으로 처리해준다.
  • 반면 프로그래밍 언어의 경우 보통 원하는 결과를 얻기 위한 과정을 직접 기술해야 한다.

답변

SQL과 프로그래밍 언어는 각각의 목적과 실행 방식에 따라 차이가 있습니다.
SQL은 주로 관계형 데이터베이스에서 데이터를 조작하기 위해 사용됩니다. 반면 C언어와 같은 프로그래밍 언어는 다양한 목적의 소프트웨어를 개발하는 데 활용됩니다. 따라서 SQL은 비교적 간단하고 사용이 편리한 문법을 가지고 있지만, 프로그래밍 언어는 다양한 목적에 따라 더 복잡한 문법과 사용법을 가질 수 있습니다.

또한, C언어나 다른 프로그래밍 언어에서 코드를 작성할 때는 원하는 결과물을 얻기 위한 전체적인 '과정'을 상세하게 기술해야 합니다. 반면 SQL에서는 개발자가 원하는 데이터를 명시하면 옵티마이저가 최적의 실행 계획을 결정하여 '과정'을 자동으로 처리합니다. 따라서 SQL은 단순히 '무엇'을 원하는지를 명시하면 되고, '어떻게'는 데이터베이스 시스템이 처리하게 됩니다.

개발자가 작성한 SQL이 어떤 과정을 통해 실행 되는지 설명해주세요.

답변

✅ SQL 쿼리가 실행되는 과정은 크게 구문 분석 ▶ 최적화 ▶ 실행 단계로 나뉩니다.
사용자가 작성한 쿼리가 데이터베이스 시스템에 전달되면 먼저 Parser가 SQL 구문이 구조적으로 잘못되진 않았는지 확인하고 쿼리를 시스템이 이해할 수 있는 단위로 분해하는 과정을 거칩니다. 이후 옵티마이저에 의해 여러 실행 계획 중에 가장 효율적인 방법을 선택하는 과정을 거치게 되고, 마지막으로 최적화된 실행 계획에 따라 SQL 실행 엔진이 쿼리를 실행합니다.

참고: [DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

DML은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.

DML(Data Manipulation Language)의 약자로, 새롭게 데이터를 추가하거나 삭제하거나 내용을 갱신하는 등 데이터를 조작할 때 사용합니다. SQL의 가장 기본이 되는 명령어입니다. 가장 널리 사용되는 구문으로는 SELECT, INSERT, UPDATE, DELETE 등이 있습니다.

  • SELECT: 데이터베이스의 데이터를 검색
  • INSERT: 데이터를 데이터베이스 테이블에 추가
  • UPDATE: 데이터베이스 테이블의 기존 데이터를 수정
  • DELETE: 데이터베이스 테이블에서 특정 조건을 만족하는 데이터를 삭제

DDL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.

Data Definition Language의 약자로, 데이터베이스의 정의를 정의하거나 변경하는데 사용하는 언어입니다. DDL은 데이터베이스 객체를 생성, 수정, 삭제하는 데에 사용되며, 테이블, 인덱스, 뷰 등과 관련된 작업을 수행합니다.

대표적인 구문으로는 CREATE, ALTER, DROP, TRUNCATE 등이 있습니다.

  • CREATE: 데이터베이스 객체(테이블, 뷰, 인덱스)를 생성한다.
  • ALTER: 데이터베이스 객체를 수정하는 데 사용된다. (제약 조건이나 속성 등을 추가 및 재정의 가능)
  • DROP: 데이터베이스 객체를 삭제한다.
  • TRUNCATE: 테이블의 모든 데이터를 삭제, 테이블 구조는 유지
-- 테이블 생성
CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   ...
);

-- 뷰 생성
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 인덱스 생성
CREATE INDEX index_name
ON table_name (column1, column2, ...);

--- 테이블에 속성을 제약 조건과 함께 정의
alter table user
    ADD age integer not null default (0),
    ADD constraint age_limit CHECK (age < 120);

-- 테이블의 제약 조건 이름 및 명세를 수정
alter table user
    drop constraint age_limit,
    add constraint age_check check (age <= 100);

-- 테이블의 이름 변경
alter table user rename user_tbl;

-- 테이블의 속성 삭제
alter table user drop column age;

-- 테이블 삭제
DROP table foo;

-- 테이블의 모든 레코드 삭제
truncate table user;

DROP, TRUNCATE, DELETE에 각각에 대해 설명해주세요. 어떤차이가 있나요?

DROP, TRUNCATE, DELETE 세 명령어 모두 데이터베이스에 저장된 데이터를 삭제하기 위해 사용되는 명령어입니다. 다만 삭제의 범위와 대상에 따라 조금씩 차이가 있습니다. DELETE 의 경우 테이블의 행 일부 또는 전체를 지웁니다. TRUNCATE 명령어는 행 전체를 지우면서 동시에 행이 저장되어 있던 공간과 인덱스도 삭제하며, DROP 명령어는 인덱스, 행 데이터 뿐만 아니라 테이블 전체를 삭제합니다.

참고) DROP, TRUNCATE, DELETE

  • DROP, TRUNCATE, DELETE는 모두 데이터베이스에 저장된 데이터를 삭제하기 위해 사용되는 명령어이다.
  • 단 DELETE는 DML이며, DROP과 TRUNCATE는 DDL이고, 내부 동작 방식에 차이가 있다.
비교 항목DROPTRUNCATEDELETE
명령어 분류DDLDDLDML
삭제 범위테이블 전체행 전체 + 인덱스 등행 데이터 일부 ~ 전체
행 데이터 (레코드)전체 삭제전체 삭제일부~전체 삭제
데이터 저장공간삭제됨삭제됨남겨져 있음
인덱스삭제됨삭제됨남겨져 있음
테이블 컬럼, 제약 조건삭제됨남겨져 있음남겨져 있음
테이블 자체삭제됨남겨져 있음남겨져 있음

DCL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.

Data Control Language의 약자로, 데이터를 제어하는데 사용되는 언어입니다. 트랜잭션을 제어하는 명령과 데이터의 접근권한을 제어하는 명령이 포함되어 있습니다. 이를 통해 데이터의 보안, 무결성, 회복, 병행 제어을 보장할 수 있습니다. 대표적인 구문으로는 데이터 접근권한 제어를 위해 사용하는GRANT와 REVOKE, 트랜잭션 제어를 위한 COMMIT과 ROLLBACK 등이 있습니다.

  • GRANT: 사용자에게 특정 작업을 수행할 수 있는 권한을 부여한다.
  • REVOKE: 사용자의 권한을 취소하거나 회수한다.
  • COMMIT: 트랜잭션의 변경 내용을 영구적으로 데이터베이스에 적용한다.
  • ROLLBACK: 트랜잭션의 변경 내용을 취소하고 트랜잭션을 종료한다.
-- 사용자 권한 부여 명령어
GRANT ALL PRIVILEGES
ON [dbname.table_name] TO [user@host]
IDENTIFIED BY 'my_password';

GRANT ALL PRIVILEGES
ON testDB.testTable TO myuser@'%'
IDENTIFIED BY 'testPassword';

-- 권한 해제 명령어(INSERT, UPDATE, CREATE 권한 해제)
REVOKE insert, update, create
ON [dbname.table_name] TO [user@host];

-- 권한 해제 명령어(전체 권한 해제)
REVOKE ALL ON [dbname.table_name] TO [user@host];

"""
일련의 트랜잭션 명령어를 수행...
SELECT ...
UPDATE ...
"""

-- 이전까지의 작업을 완전히 저장
COMMIT;

-- 이전까지의 작업을 취소 (단 COMMIT 명령어 사용 이전에만 적용 가능)
ROLLBACK;

참조 무결성에 대해서 설명해주세요.

참조 무결성은 기본키와 참조 키 간의 관계가 항상 유지되도록 보장하는 것을 말합니다. 구체적으로는 참조자 테이블에 존재하는 외래 키는 NULL이거나 참조 중인 테이블에 실제로 존재하는 값이어야 합니다. 참조 무결성을 통해 테이블 간의 관계를 올바르게 유지하고 데이터의 정확성과 일관성을 보장할 수 있습니다.

정의

릴레이션 이 릴레이션 를 참조하는 경우, 의 FK는…

(1) Null 이거나
(2) Null 이 아닌 경우 에 실제로 존재하는 값으로 구성되어야 한다.

Q) 다음 중 참조 무결성을 위배하는 튜플은?

3, 4번 튜플의 경우 참조하고 있는 릴레이션에 존재하지 않는 값을 가리키고 있다. 따라서 참조 무결성에 위배된다.

CASCADE 설정에 대해서 설명해주세요.

CASCADE는 관계형 데이터베이스에서 외래 키 관계에 사용되는 설정입니다.
주로 참조 무결성을 유지하고 데이터의 일관성을 보장하기 위해 사용되며, 구체적으로는 관계를 맺는 테이블에서 기본 키 값이 변경되거나 삭제될 때, 해당 값을 참조하는 모든 테이블의 외래 키 값도 자동으로 변경되거나 삭제하도록 설정할 수 있습니다.

참조 무결성의 문제와 Cascade Option

참조 무결성은 관계를 맺는 테이블들 간의 정확성과 일관성을 보장해 주지만, 다른 테이블을 참조하는 외래 키는 NULL이거나 항상 같은 값을 가져야 하기 때문에, 참조받는 쪽에서 데이터의 수정을 강제적으로 막는다는 문제가 발생한다.

예를 들어 다른 테이블과 관계를 맺고 있는 속성의 이름을 변경하거나 데이터를 삭제하려는 경우, 해당 속성을 참조하고 있는 외래 키 값이 존재한다면 무결성 제약 조건으로 인해 DBMS가 이를 강제적으로 막는다.

그렇다고 참조 중인 외래 키의 값을 모두 삭제하고 작업을 수행할 수는 없는 노릇이다.
그렇다면 어떻게 해야 할까? 이러한 경우 Cascade option을 사용한다.

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

구체적인 Cascade Option

Cascade는 제약 조건에 해당하기 때문에 ALTER 구문과 함께 사용된다.

-- user 테이블에 외래 키 추가 및 제약 조건 설정 (ON DELETE CASCADE 옵션 추가)
ALTER TABLE user
    add column team_id bigint,
    add constraint fk_team_id foreign key (team_id)
        references team (id) on delete cascade;

위 예시에서 team과 user 테이블은 1:n으로 관계를 맺고 있다. 이전에는 team의 이름의 변경을 시도하면 무결성 제약 조건으로 인해 기본키에 대한 삭제/수정이 불가능하였다.

그러나 위와 같이 ON UPDATE CASCADE 옵션을 지정하면 위 명령어가 정상적으로 실행함을 확인할 수 있다. 이후 테이블을 출력해보면 team 테이블에서 id 1번에 해당하는 레코드 뿐만 아니라 해당 레코드를 참조하고 있는 user 테이블의 레코드 또한 삭제된 것을 확인할 수 있다.

VIEW에 대해서 설명해주세요.

VIEW는 본래 데이터베이스 객체로 등록할 수 없는 SELECT 명령을 기록한 데이터베이스 객체를 말합니다. VIEW를 사용하면 SELECT 명령을 객체로서 관리할 수 있으며, 뷰를 참조하면 그에 해당하는 SELECT 명령을 실행하여 그 결과를 테이블처럼 사용할 수 있습니다.

VIEW는 서브쿼리 부분을 대처하거나 복잡하고 자주 사용되는 SELECT 명령을 재사용하기 위해 사용됩니다.

데이터베이스 객체란?

  • 데이터베이스 안에 정의하는 모든 것
  • 즉, 테이블이나 인덱스, 뷰는 모두 데이터베이스 객체이다.

뷰 사용해보기

  • 뷰는 저장 공간을 필요로 하지 않는 가상 테이블이다.
    • 뷰와 뷰가 관리하는 SELECT 명령만 저장하기 때문에 테이블과 비교해 대량의 저장 공간을 필요로 하지 않는다.
    • 단 뷰를 참조하여 실제 테이블의 데이터를 결과로 얻을 수 있기 때문에 실체가 존재하지 않는 테이블이라 하여 가상 테이블로 불리기도 한다.
  • 뷰를 참조하면 뷰에 등록된 SELECT 명령이 실행된다.
    • 이 과정에서 CPU 자원을 소모한다.
    • 실행 결과는 일시적으로 메모리에 저장되며, 뷰를 참조할 때마다 명령이 실행된다.
    • 이런 임시 데이터는 쿼리가 종료될 때 같이 삭제된다.
# 뷰 생성
CREATE VIEW user_view AS SELECT * from user;
  
# 열을 지정해 뷰 생성
CREATE VIEW user_view_with_columns (user_name, team_id) AS
SELECT name, team_id
FROM user;

# 뷰 참조
select * from user_view as sq;

# 뷰 삭제
DROP VIEW user_view;

SELECT 절의 처리순서에 대해서 설명해주세요.

출처: https://jaehoney.tistory.com/191

SELECT 절의 처리 순서는 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY의 순서를 따릅니다. SELECT 명령을 요청하면 먼저 FROM 절에서 조회할 테이블을 지정합니다. 이후 WHERE절에서는 테이블에 조건에 맞는 데이터를 필터링합니다. 이후 GROUP BY절에서 선택한 칼럼을 기준으로 조회한 레코드 목록을 그룹핑하고, HAVING절이 존재하는 경우 지정된 조건에 따라 각 그룹을 필터링 합니다.

이와 같은 조건들을 처리하고 남은 데이터는 SELECT절을 통해 어떤 열을 출력할지 지정되고, 이때 ORDER BY절을 통해 어떤 순서로 행을 보여줄지 결정해주게 됩니다.
위와 같은 처리 순서를 거쳐 쿼리가 수행됩니다.

처리 순서가 중요한 이유

  • 내부 처리 순서가 중요한 이유는 순서를 모르면 쿼리를 제대로 작성하기 힘들기 때문이다.
  • 예를 들어 쿼리 작성 시 표현 축약을 위해 사용되는 별칭(alias)은 처리 순서를 숙지한다면 더 잘 활용할 수 있다.

쿼리 작성시 사용하는 별칭(as)는 보통 FROM 뒤에 명시되는 테이블 명이나 SELECT 에서 지정된 속성 명을 대신하는 이름으로 사용된다.

select u.id as id, u.name as name
from user as u
where u.id > 1;

이때 from 절에서 사용된 별칭을 select절과 where절에서 사용한 것을 알 수 있다.
이는 from이 두 명령어보다 먼저 처리되기 때문이다. 따라서 select 절에 사용되는 별칭은 from이나 where절에서 사용될 수 없다.

반면 order by 절의 경우 select 절 이후에 사용되기 때문에 다음과 같이 사용 가능하다.

SELECT ~ FOR UPDATE 구문에 대해서 설명해주세요.

참고: https://dololak.tistory.com/446

SELECT ~ FOR UPDATE 구문이란 데이터에 대한 작업 수행 간의 동시성 제어를 위해 특정 데이터에 대해 베타적 락(Lock)을 거는 것을 말합니다. 즉 어떤 사용자가 특정 집합의 레코드를 이용하고 있을 경우, 해당 데이터에 다른 사용자가 직접 수정할 수 없도록 막는 것을 말합니다.

예를 들어 어떤 테이블에 저장된 레코드에 2명 이상의 사용자가 동시에 접근하는 경우, 예상치 못한 결과 또는 오류를 발생시킬 수 있습니다. 이때 베타적 락을 사용한다면 여러 사용자가 동시에 같은 데이터를 수정하더라도 데이터의 무결성을 보장할 수 있습니다.

profile
이승훈
학생
이전 포스트

데이터베이스 & 관계형 데이터베이스 기본 개념

0개의 댓글