본 시리즈에서는 데이터베이스의 개념을 정리하고 필요시 실습을 진행합니다.
- 실습 환경(Server)
- 원격 서버 (OS) : AWS-EC2 (
Ubuntu-24.04) - DBMS(관리 시스템) : MariaDB (
10.11.8-MariaDB)
- 원격 서버 (OS) : AWS-EC2 (
- 실습 환경(Client)
- Client OS : Windows 11
- DB Tool (IDE) : JetBrains DataGrip (
2024.2.2) - Java (JDBC) : JDK 21 (
correto-21) - Java IDE : IntelliJ IDEA (
Ultimate Edition)
- DB 실습 예제
- MySQL
world database - https://dev.mysql.com/doc/index-other.html
- MySQL
DataBase - SQL 개요 및 DDL & DCL
1. SQL이란?
SQL(Structured Query Language)는 관계형 데이터베이스(RDBMS)에서 데이터를 정의하고 조작하기 위한 언어입니다.
- SQL은 데이터베이스에서 데이터를 삽입, 조회, 수정, 삭제할 수 있는 명령어를 제공하며, 데이터베이스의 구조를 정의하거나 데이터의 무결성을 유지하고 보안을 관리하는 데도 사용됩니다.
1.1 SQL의 특징
- 표준 언어:
- SQL은 국제 표준으로 채택된 데이터베이스 언어로, 대부분의 RDBMS(MySQL, Oracle, PostgreSQL 등)에서 공통적으로 사용됩니다.
- 데이터 조작의 용이성:
- SQL을 사용하면 데이터를 쉽게 조회하거나 조작할 수 있으며, 복잡한 조건을 포함한 질의(query)도 작성할 수 있습니다.
- 관계형 데이터 관리:
- SQL은 데이터 간의 관계를 설정하고 관리할 수 있으며, 다양한
JOIN연산을 통해 여러 테이블을 조합하여 원하는 데이터를 추출할 수 있습니다.
- SQL은 데이터 간의 관계를 설정하고 관리할 수 있으며, 다양한
- 권한 및 트랜잭션 관리:
- SQL은 데이터베이스 접근 권한을 제어할 수 있는 DCL(Data Control Language) 명령어와 트랜잭션을 관리하는 TCL(Transaction Control Language) 명령어를 제공합니다.
1.2 SQL의 주요 분류
SQL 명령어는 용도에 따라 크게 다음과 같은 네 가지로 분류됩니다.
- DDL (Data Definition Language):
- 데이터베이스와 테이블 구조를 정의하는 언어로,
CREATE,ALTER,DROP과 같은 명령어가 포함됩니다.
- 데이터베이스와 테이블 구조를 정의하는 언어로,
- DML (Data Manipulation Language):
- 데이터베이스의 데이터를 조회하거나 조작하는 언어로,
SELECT,INSERT,UPDATE,DELETE명령어가 포함됩니다. - 참고로 정해진 스키마 내에서 질의(query)를 통해 데이터를 조회하는 언어를 DQL(Data Query Language)로 따로 분류하는 경우도 있습니다. (여기선 DML에 포함하여 함께 다룹니다)
- 데이터베이스의 데이터를 조회하거나 조작하는 언어로,
- DCL (Data Control Language):
- 데이터베이스 접근 권한을 제어하는 언어로,
GRANT,REVOKE명령어가 포함됩니다.
- 데이터베이스 접근 권한을 제어하는 언어로,
- TCL (Transaction Control Language):
- 트랜잭션을 관리하는 언어로,
COMMIT,ROLLBACK,SAVEPOINT명령어가 포함됩니다. - 참고로 TCL을 DCL에 포함시켜서 분류하는 경우도 있습니다. 그래서 여기선 DCL 파트에서 TCL를 함께 다룰 것입니다.
- 트랜잭션을 관리하는 언어로,
SQL은 이러한 다양한 기능을 통해 데이터베이스의 구조를 정의하고 데이터를 조작하며, 데이터 무결성과 보안을 유지할 수 있는 강력한 도구입니다.
2. DDL (Data Definition Language)
DDL(Data Definition Language)은 데이터베이스와 그 안의 객체(테이블, 인덱스 등)의 구조를 정의하고 관리하는 명령어들로 구성된 언어입니다.
- DDL 명령어는 데이터베이스의 스키마와 객체 구조를 정의하거나 수정, 삭제할 때 사용됩니다.
2.1 CREATE
CREATE 명령어는 새로운 데이터베이스, 테이블, 인덱스 등을 생성할 때 사용됩니다.
- 데이터베이스나 테이블의 이름과 필요한 속성, 데이터 타입 등을 정의하여 데이터베이스 객체를 생성할 수 있습니다.
- 참고: 서버에서는 데이터베이스 사용자 계정을 생성할 때도
CREATE명령어를 사용할 수 있습니다.
예시
-- 데이터베이스 생성
CREATE DATABASE my_database;
-- 테이블 생성
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 사용자 계정 생성 (서버)
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
2.2 ALTER
ALTER 명령어는 기존의 테이블이나 데이터베이스 객체를 수정할 때 사용됩니다.
- 테이블의 열(Column)을 추가, 삭제, 수정하거나 인덱스 등을 추가할 수 있습니다.
예시
-- 테이블에 열 추가
ALTER TABLE users ADD COLUMN age INT;
-- 열의 데이터 타입 변경
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
-- 열 삭제
ALTER TABLE users DROP COLUMN age;
-- 열 이름 변경
ALTER TABLE users2 RENAME TO users1;2.3 DROP
DROP 명령어는 데이터베이스, 테이블, 인덱스 등의 객체를 삭제할 때 사용됩니다.
- 삭제된 객체는 복구가 불가능하므로 주의해서 사용해야 합니다.
예시
-- 데이터베이스 삭제
DROP DATABASE my_database;
-- 테이블 삭제
DROP TABLE users;
-- 사용자 계정 삭제
DROP USER 'new_user'@'localhost';2.4 TRUNCATE
TRUNCATE 명령어는 테이블의 모든 데이터를 일괄 삭제할 때 사용됩니다.
DELETE명령어와 달리 로그를 기록하지 않아 빠르게 대량의 데이터를 삭제할 수 있습니다.- 테이블의 구조는 남겨두고 데이터만 삭제할 때 유용합니다.
예시
-- 테이블의 모든 데이터 삭제 (테이블 구조는 유지)
TRUNCATE TABLE users;2.5 RENAME
RENAME 명령어는 테이블의 이름을 변경할 때 사용됩니다.
- 여러 테이블의 이름을 동시에 변경할 수도 있습니다.
예시
-- 테이블 이름 변경
RENAME TABLE old_table_name TO new_table_name;
-- 여러 테이블 이름 변경
RENAME TABLE old_table_name1 TO new_table_name1,
old_table_name2 TO new_table_name2;
DDL 명령어 요약
| 명령어 | 설명 |
|---|---|
| CREATE | 새로운 데이터베이스나 테이블, 인덱스, 사용자 계정을 생성 |
| ALTER | 기존의 테이블이나 데이터베이스 객체를 수정 |
| DROP | 데이터베이스, 테이블, 인덱스, 사용자 계정을 삭제 |
| TRUNCATE | 테이블의 모든 데이터를 일괄 삭제 |
| RENAME | 테이블의 이름을 변경 |
DDL 명령어는 데이터베이스 구조와 객체를 정의하고 수정할 때 중요한 역할을 하며, 데이터베이스 설계의 기본이 되는 명령어들로 구성되어 있습니다.
3. Constraints (제약조건)
제약조건(Constraints)은 데이터베이스에서 테이블에 저장되는 데이터의 무결성과 일관성을 유지하기 위해 적용하는 규칙입니다.
- 제약조건을 설정하면 특정 조건을 만족하지 않는 데이터가 테이블에 입력되는 것을 방지할 수 있습니다.
3.1 PRIMARY KEY
PRIMARY KEY는 테이블 내에서 각 행을 고유하게 식별할 수 있는 열(Column)이나 열의 조합입니다.
- 하나의 테이블에는 하나의 PRIMARY KEY만 있을 수 있으며, 해당 열은 중복될 수 없고 NULL 값을 허용하지 않습니다.
- 기본 키는 데이터 검색과 연관성 설정에 중요한 역할을 합니다.
예시
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50)
);3.2 AUTO_INCREMENT
AUTO_INCREMENT는 숫자 데이터 타입의 열에 적용하여 자동으로 고유한 값을 생성하는 데 사용됩니다.
- 주로
PRIMARY KEY와 함께 사용하여 각 행의 고유 ID를 자동으로 증가시키며, 값이 자동으로 지정되므로 수동으로 입력할 필요가 없습니다. AUTO_INCREMENT는 정수 데이터 타입 열에서만 사용할 수 있습니다.
예시
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
price DECIMAL(10, 2)
);3.3 NOT NULL
NOT NULL 제약조건은 열에 NULL 값이 저장되는 것을 허용하지 않도록 설정합니다.
- 특정 열이 반드시 값을 가져야 하는 경우에 사용되며, 데이터의 필수 입력을 강제합니다.
예시
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);3.4 UNIQUE
UNIQUE 제약조건은 특정 열이 고유한 값만 가질 수 있도록 설정합니다.
- 한 테이블에서 여러 개의 열에 UNIQUE 제약조건을 적용할 수 있으며, 하나의 열뿐만 아니라 여러 열의 조합에도 적용할 수 있습니다.
PRIMARY KEY와 달리 테이블에 여러 개의UNIQUE제약조건을 설정할 수 있습니다.
예시
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
3.5 FOREIGN KEY
FOREIGN KEY는 다른 테이블의 PRIMARY KEY나 UNIQUE 열을 참조하여 테이블 간의 관계를 정의합니다.
FOREIGN KEY제약조건을 사용하여 데이터베이스 내에서 데이터 무결성을 유지하고, 참조 무결성 제약을 통해 삭제와 업데이트의 제약을 설정할 수 있습니다.
예시
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
3.6 CHECK
CHECK 제약조건은 열에 입력되는 데이터가 특정 조건을 만족해야만 저장되도록 설정합니다.
CHECK제약조건을 사용하여 값의 범위나 조건을 설정하여 유효성을 검사할 수 있습니다.MySQL은CHECK제약조건을 제한적으로 지원하므로MariaDB와 호환되는 환경에서 사용할 때 주의가 필요합니다.
예시
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
CHECK (age >= 18)
);제약조건(Constraints) 요약
| 제약조건 | 설명 |
|---|---|
| PRIMARY KEY | 각 행을 고유하게 식별하는 열, NULL과 중복 값 허용 안 됨 |
| AUTO_INCREMENT | 숫자 타입 열에서 자동으로 고유 값을 생성, 주로 PRIMARY KEY와 함께 사용 |
| NOT NULL | 열에 NULL 값을 허용하지 않음, 필수 입력 필드 설정 |
| UNIQUE | 열에 고유한 값만 허용, 중복 값 허용 안 됨 |
| FOREIGN KEY | 다른 테이블의 PRIMARY KEY를 참조하여 테이블 간 관계 설정 |
| CHECK | 열에 입력되는 데이터가 특정 조건을 만족해야 함 |
제약조건(Constraints)은 데이터베이스의 데이터 무결성과 정확성을 유지하는 중요한 요소입니다. 이러한 제약조건을 적절히 설정함으로써 데이터베이스의 일관성을 보장하고, 데이터의 유효성을 검증할 수 있습니다.
4. DCL & TCL (Data & Transaction Control Language)
DCL(Data Control Language)과 TCL(Transaction Control Language)은 데이터베이스의 접근 권한과 트랜잭션을 제어하는 데 사용되는 SQL 명령어들입니다.
4.1 DCL (Data Control Language)
DCL은 데이터베이스 사용자에 대한 권한을 제어하는 언어로, 특정 사용자에게 데이터베이스 접근 권한을 부여하거나 회수할 수 있습니다.
- 주로 데이터 보안과 접근 제어를 위한 명령어가 포함됩니다.
GRANT
특정 사용자에게 데이터베이스, 테이블, 열 등에 대한 접근 권한을 부여합니다.
- 권한의 종류에는
SELECT,INSERT,UPDATE,DELETE와 같은 기본적인 데이터 조작 권한뿐만 아니라CREATE,DROP과 같은 데이터 정의 권한도 포함됩니다.
-- 사용자에게 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON my_database.my_table TO 'user_name'@'localhost';
-- 사용자에게 모든 권한 부여
GRANT ALL PRIVILEGES ON my_database.* TO 'user_name'@'localhost';REVOKE
특정 사용자에게 부여된 권한을 회수합니다.
- 회수할 권한을 명시하여 사용자가 더 이상 특정 작업을 수행하지 못하도록 할 수 있습니다.
-- 사용자에게서 SELECT 권한 회수
REVOKE SELECT ON my_database.my_table FROM 'user_name'@'localhost';
-- 사용자에게서 모든 권한 회수
REVOKE ALL PRIVILEGES ON my_database.* FROM 'user_name'@'localhost';참고: 권한 부여와 회수는 데이터베이스 보안을 강화하는 중요한 방법으로, 특히 다수의 사용자가 접근하는 환경에서 필수적입니다.
4.2 TCL (Transaction Control Language)
TCL은 데이터베이스에서 트랜잭션(Transaction)을 제어하는 언어로, 데이터의 일관성을 유지하고 여러 작업을 하나의 논리적 단위로 묶어 관리할 수 있게 합니다.
- 트랜잭션(Transaction)이란 하나의 작업 단위로, 모두 성공적으로 완료되거나, 그렇지 않으면 전부 취소되는 작업의 집합을 의미합니다. (Transaction 및 ACID 원칙에 대해선 추후에 따로 자세히 다룹니다)
COMMIT
- 트랜잭션이 성공적으로 완료되었음을 명시하고, 변경 사항을 영구적으로 저장합니다.
- 트랜잭션의 마지막에 사용하여 데이터베이스에 반영합니다.
BEGIN;
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
COMMIT;ROLLBACK
- 트랜잭션 중 오류가 발생하거나 작업을 취소하고자 할 때, 트랜잭션이 수행한 모든 변경 사항을 원래 상태로 되돌립니다.
- 트랜잭션을 시작한 후
COMMIT되지 않은 상태에서ROLLBACK을 수행하면, 모든 작업이 취소됩니다.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
-- 오류가 발생했을 경우
ROLLBACK;SAVEPOINT
- 트랜잭션 내에서 특정 지점을 저장 지점으로 설정하여, 부분적으로
ROLLBACK할 수 있게 합니다. SAVEPOINT를 사용하여 트랜잭션 내에서 여러 지점을 저장하고, 필요에 따라 특정SAVEPOINT로 돌아가 수정할 수 있습니다.
BEGIN;
INSERT INTO orders (order_id, amount) VALUES (1, 100);
SAVEPOINT savepoint1;
INSERT INTO orders (order_id, amount) VALUES (2, 200);
-- 특정 지점으로 되돌아감
ROLLBACK TO savepoint1;
COMMIT;
참고: 트랜잭션 관리는 데이터베이스의 일관성과 무결성을 보장하는 중요한 기능입니다. 특히 여러 명령어를 하나의 논리적 작업으로 묶어서 처리할 때 유용합니다.
DCL & TCL 명령어 요약
| 명령어 | 설명 |
|---|---|
| GRANT | 사용자에게 데이터베이스 접근 권한을 부여 |
| REVOKE | 사용자에게 부여된 접근 권한을 회수 |
| COMMIT | 트랜잭션을 성공적으로 완료하고 변경 사항을 영구적으로 저장 |
| ROLLBACK | 트랜잭션 중 오류가 발생하거나 취소할 때, 모든 변경 사항을 원래 상태로 되돌림 |
| SAVEPOINT | 트랜잭션 내에서 특정 지점을 저장하여 부분적으로 ROLLBACK 가능 |
DCL은 데이터베이스의 보안과 접근 제어를 담당하며, TCL은 데이터베이스의 일관성과 무결성을 유지하기 위한 트랜잭션 제어를 담당합니다. 이 두 언어는 데이터베이스의 안정성과 보안을 보장하는 데 필수적입니다.
마무리
이번 포스팅에서는 SQL의 개요와 주요 명령어 분류, DDL(Data Definition Language), 제약조건(Constraints), DCL(Data Control Language) 및 TCL(Transaction Control Language)에 대해 다루어 보았습니다.
- 이를 통해 관계형 데이터베이스에서 SQL을 통해 데이터 구조를 정의하고, 데이터 무결성과 보안을 유지하며, 접근 권한과 트랜잭션을 관리할 수 있습니다.
DDL 명령어 요약
| 명령어 | 설명 |
|---|---|
| CREATE | 새로운 데이터베이스나 테이블, 인덱스, 사용자 계정을 생성 |
| ALTER | 기존의 테이블이나 데이터베이스 객체를 수정 |
| DROP | 데이터베이스, 테이블, 인덱스, 사용자 계정을 삭제 |
| TRUNCATE | 테이블의 모든 데이터를 일괄 삭제 |
| RENAME | 테이블의 이름을 변경 |
제약조건(Constraints) 요약
| 제약조건 | 설명 |
|---|---|
| PRIMARY KEY | 각 행을 고유하게 식별하는 열, NULL과 중복 값 허용 안 됨 |
| AUTO_INCREMENT | 숫자 타입 열에서 자동으로 고유 값을 생성, 주로 PRIMARY KEY와 함께 사용 |
| NOT NULL | 열에 NULL 값을 허용하지 않음, 필수 입력 필드 설정 |
| UNIQUE | 열에 고유한 값만 허용, 중복 값 허용 안 됨 |
| FOREIGN KEY | 다른 테이블의 PRIMARY KEY를 참조하여 테이블 간 관계 설정 |
| CHECK | 열에 입력되는 데이터가 특정 조건을 만족해야 함 |
DCL & TCL 명령어 요약
| 명령어 | 설명 |
|---|---|
| GRANT | 사용자에게 데이터베이스 접근 권한을 부여 |
| REVOKE | 사용자에게 부여된 접근 권한을 회수 |
| COMMIT | 트랜잭션을 성공적으로 완료하고 변경 사항을 영구적으로 저장 |
| ROLLBACK | 트랜잭션 중 오류가 발생하거나 취소할 때, 모든 변경 사항을 원래 상태로 되돌림 |
| SAVEPOINT | 트랜잭션 내에서 특정 지점을 저장하여 부분적으로 ROLLBACK 가능 |
다음 포스팅에서는 이러한 RDBMS에서 데이터를 조회, 삽입, 수정, 삭제하는 DML(Data Manipulation Language) 명령어를 다룰 예정입니다.
