06 SQL 기초

SQL 개요

SQL(Structured Query Language)는 관계형 데이터베이스에서 데이터를 관리하고 처리하는 데 사용되는 표준 프로그래밍 언어
SQL은 데이터를 정의하고, 검색하며, 조작하는 기능을 제공함으로써 데이터베이스 관리 시스템(DBMS)에서 핵심적인 역할을 수행함
데이터를 삽입(Insert), 조회(Select), 수정(Update), 삭제(Delete) 등 다양한 작업을 수행할 수 있음

SQL의 역할

데이터 정의: 데이터베이스 스키마(테이블, 열, 데이터 타입 등)를 정의함
데이터 조작: 테이블에 데이터를 삽입, 조회, 수정, 삭제할 수 있음
데이터 제어: 데이터에 대한 권한을 제어하고 트랜잭션을 관리할 수 있음
데이터 무결성 유지: 데이터의 정확성과 일관성을 보장함

SQL의 주요 구성 요소

SQL은 각 명령어가 데이터베이스에서 수행하는 작업의 성격에 따라 4 가지로 구분됨
1. DDL (Data Definition Language) : 데이터베이스 구조를 정의하는 언어 (예: CREATE, ALTER, DROP)
2. DML (Data Manipulation Language) : 데이터베이스 내의 데이터를 조작하는 언어 (예: SELECT, INSERT, UPDATE, DELETE)
3. DCL (Data Control Language) : 데이터베이스의 권한을 관리하는 언어 (예: GRANT, REVOKE)
4. TCL (Transaction Control Language) : 트랜잭션을 제어하는 언어 (예: COMMIT, ROLLBACK)

1. 데이터 정의 언어(DDL)

데이터 정의 언어(DDL, Data Definition Language)는 데이터베이스의 구조(스키마)를 정의하거나 수정하는 명령어를 제공함. 주로 테이블이나 데이터베이스를 생성하거나 수정할 때 사용됨
주요 명령어
- CREATE : 새로운 데이터베이스 객체(테이블, 인덱스 등)를 생성
- ALTER : 기존 데이터베이스 객체를 수정
- DROP : 데이터베이스 객체를 삭제

CREATE

새로운 데이터베이스 객체(테이블, 인덱스 등)를 생성필요한 컬럼과 각 컬럼의 타입 리스트를 정의해주어야 함

CREATE TABLE new_schema.employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

ALTER

기존 데이터베이스 객체를 수정
Employees 테이블에 DATE 타입의 hire_date라는 컬럼 추가

ALTER TABLE employees ADD hire_date DATE;

DROP

데이터베이스 객체를 삭제
테이블 전체를 삭제함

DROP TABLE employees;

2. 데이터 조작 언어(DML)

데이터 조작 언어(DML, Data Manipulation Language)는 데이터베이스에 저장된 데이터를 조작하는 데 사용되는 명령어로, 데이터를 삽입, 수정, 삭제, 조회할 때 사용됨
- 주요 명령어
- SELECT: 데이터를 조회
- INSERT: 새로운 데이터를 테이블에 삽입
- UPDATE: 기존 데이터를 수정
- DELETE: 데이터를 삭제

SELECT

데이터베이스에서 데이터를 조회함
조회하고자 하는 컬럼과 조건을 필터링할 수 있음

SELECT name, salary FROM employees WHERE salary > 6000;

INSERT

새로운 데이터를 테이블에 삽입함

INSERT INTO employees (employee_id, name, salary) 
VALUES (8,'홍길동', 6000), (9, '김영희', 7200);

UPDATE

기존 데이터를 수정함

UPDATE employees SET salary = 6500 WHERE employee_id = 5;

DELETE

데이터를 삭제함
삭제하고자 하는 데이터의 조건 입력

DELETE FROM employees WHERE employee_id = 7;

3. 데이터 제어 언어(DCL)

데이터 제어 언어(DCL, Data Control Language)는 데이터베이스에 대한 접근을 제어하고, 사용자의 권한을 관리하는 데 사용됨
주요 명령어
- GRANT: 사용자에게 특정 권한을 부여
- REVOKE: 사용자에게 부여된 권한을 취소

GRANT

사용자에게 특정 권한을 부여함
사용자에게 특정 권한을 부여하기 위해 우선 사용자를 생성함

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user10041'; -- 새로운 유저 생성
CREATE USER 'username'@'localhost' IDENTIFIED BY 'testPwd1004';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'; -- 유저 권한 부여
GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';

SELECT user, host FROM mysql.user; -- 유저 리스트 확인

생성한 사용자 정보로 DB 접속
사용자에게 부여되지 않은 권한을 실행하면 동작하지 않고 에러 발생

DELETE FREOM new_schema.employees WHERE employee_id = 1;

REVOKE

사용자에게 부여된 권한을 취소함

REVOKE INSERT ON employees FROM 'user1'@'localhost';

이전에는 실행되었던 INSERT가 더 이상 실행되지 않음

INSERT INTO employees (employee_id, name, salary) VALUES (10, '김상덕', 6000);

4. 트랜잭션 제어 언어(TCL)

트랜잭션 제어 언어(TCL, Transaction Control Language)는 데이터베이스 트랜잭션을 제어하는 데 사용됨
트랜잭션은 데이터베이스에서 수행하는 일련의 작업으로, 모든 작업이 성공하거나 실패할 때만 데이터가 변경되도록 보장함
주요 명령어
- COMMIT: 트랜잭션에서 수행한 모든 변경 사항을 저장
- ROLLBACK: 트랜잭션에서 수행한 모든 변경 사항을 취소
- SAVEPOINT: 트랜잭션 내에서 되돌릴 지점을 설정

보충 – 트랜잭션이란?
- 트랜잭션은 데이터베이스에서 한 번에 일어나는 작업의 묶음을 의미함
- 이 작업의 묶음이 성공적으로 끝나면 그동안 일어난 모든 변화가 데이터베이스에 저장되고, 
실패하면 모든 변화가 취소되어 데이터가 원래 상태로 돌아가는 것을 보장함

- 예를 들어 은행 어플에서 계좌이체를 할 때 아래와 같은 두 가지 동작이 일어나야 함
    1. A의 계좌에서 돈을 차감
    2. B의 계좌에 돈을 추가
- 두 작업은 반드시 모두 성공하거나, 둘 다 실패해야 함
- A의 계좌에서 돈은 빠져나갔는데, B의 계좌에 돈이 들어가지 않는다면 문제 발생
- 트랜잭션을 사용하면 이러한 일을 방지할 수 있음
- 두 작업을 하나의 트랜잭션으로 묶으면, 둘 다 성공하면 완료되고, 
둘 중 하나라도 실패하면 전체 작업이 취소되어 원래 상태로 돌아가는 것

트랜잭션의 4가지 중요한 특성 (ACID)
- 트랜잭션은 데이터베이스에서 데이터를 안전하게 처리하기 위해 ACID라는 4가지 특성을 갖고 있음
1. 원자성 (Atomicity): 트랜잭션의 모든 작업이 모두 성공하거나 모두 실패해야 함. 일부만 완료되고 일부는 실패하는 일이 없도록 보장
2. 일관성 (Consistency): 트랜잭션이 완료되면 데이터베이스는 항상 일관된 상태를 유지해야 함. 예를 들어, 데이터베이스에 제약 조건이 있으면, 트랜잭션이 끝났을 때 이 조건이 여전히 만족되어야 함
3. 고립성 (Isolation): 여러 트랜잭션이 동시에 실행되더라도, 각 트랜잭션은 독립적으로 처리되어야 함. 다른 트랜잭션의 작업이 끝나기 전에는 그 영향을 받지 않도록 해야함
4. 지속성 (Durability): 트랜잭션이 성공적으로 완료되면, 그 결과는 영구적으로 저장됨. 이후 시스템에 문제가 발생하더라도 데이터는 손실되지 않음

트랜잭션 과정
- 트랜잭션 시작: 트랜잭션의 작업을 시작함, 이때 데이터베이스는 변경을 추적하기 시작하고, 
이후 일어나는 작업들이 트랜잭션에 포함됨
- 작업 실행 : 여러 데이터베이스 작업을 처리, 
하나의 트랜잭션 안에서는 INSERT(데이터 추가), UPDATE(데이터 수정), DELETE(데이터 삭제) 같은 작업이 
포함될 수 있으며 이러한 일련의 작업들이 논리적으로 하나의 작업으로 묶여 진행됨

- 커밋 (Commit)  : 모든 작업이 성공하면, 트랜잭션을 완료(확정)하고 데이터베이스에 반영
- 롤백 (Rollback) : 중간에 오류가 발생하면, 트랜잭션을 취소하고 데이터베이스를 원래 상태로 되돌림
- 트랜잭션 종료    : 트랜잭션이 커밋되든 롤백되든, 모든 작업이 끝나면 트랜잭션은 종료됨. 
이 단계에서 데이터베이스는 더 이상 트랜잭션을 추적하지 않고, 새로운 작업이 시작될 수 있는 상태로 돌아감

COMMIT

트랜잭션이 성공적으로 완료되었음을 데이터베이스에 알리고, 해당 트랜잭션의 모든 변경 사항을 확정함

-- 트랜잭션 시작
START TRANSACTION;

-- 데이터 삽입
INSERT INTO employees (employee_id, name, salary) VALUES (11, '최철수', 8000);

-- 데이터 업데이트
UPDATE employees SET salary = salary + 500 WHERE name = '유재석';

-- 현재 상태를 확인 (변경사항이 반영되었는지 확인)
SELECT * FROM employees;

-- COMMIT 또는 ROLLBACK 결정
-- 트랜잭션을 완료하고 변경 사항을 확정할 때:
COMMIT;

ROLLBACK

트랜잭션 중 문제가 발생했을 경우, 해당 트랜잭션에서 수행한 모든 변경 사항을 취소하고 이전 상태로 되돌림
변경 사항 없음

-- 트랜잭션 시작
START TRANSACTION;

-- 데이터 삽입
INSERT INTO employees (employee_id, name, salary) VALUES (12, '영숙', 8000);

-- 데이터 업데이트
UPDATE employees SET salary = salary + 500 WHERE name = '유재석';

-- 현재 상태를 확인 (변경사항이 반영되었는지 확인)
SELECT * FROM employees;

-- COMMIT 또는 ROLLBACK 결정
-- 트랜잭션을 취소하고 원래 상태로 되돌리고 싶을 때:
ROLLBACK;

PreviousDBeaver, Mysql 오토커밋 설정 관련 Next예시데이터 쿼리문

Last updated 8 months ago