[CS] 데이터베이스_SQL

📌SQL의 정의와 기본 구문 이해(DDL/DML/DCL)

SQL(Structured Query Language)은 관계형 데이터베이스(RDBMS)에서 데이터를 관리하고 조작하기 위한 언어이다.

SQL은 다음과 같이 세 가지 주요 범주로 나뉜다.

1️⃣ DDL (Data Definition Language, 데이터 정의어)

• 데이터베이스 구조(스키마, 테이블, 인덱스 등)를 정의하고 변경하는 명령어
• 주요 명령어:
  • CREATE : 데이터베이스 객체(테이블, 인덱스 등) 생성
  • ALTER : 기존 객체의 구조 변경
  • DROP : 객체 삭제
  • TRUNCATE : 테이블의 모든 데이터를 삭제(구조는 유지)

-- 데이터베이스 생성
CREATE DATABASE my_database;

-- 테이블 생성
CREATE TABLE users (
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 컬럼 추가
ALTER TABLE users ADD COLUMN age INT;

-- 컬럼 데이터 타입 변경
ALTER TABLE users MODIFY COLUMN age SMALLINT;

-- 컬럼 삭제
ALTER TABLE users DROP COLUMN age;

--데이터베이스 삭제
DROP DATABASE my_database;

-- 테이블 삭제
DROP TABLE user;

-- users 테이블의 모든 데이터를 삭제, AUTO_INCREMENT 초기화
TNUNCATE TABLE users;

2️⃣ DML (Data Manipulation Language, 데이터 조작어)

• 테이블의 데이터를 조작하는 명령어
• 주요 명령어:
  • SELECT : 데이터 조회
  • INSERT : 데이터 삽입
  • UPDATE : 데이터 수정
  • DELETE : 데이터 삭제

-- 전체 데이터 조회
SELECT * FROM users;

-- 특정 컬럼만 조회
SELECT name, email FROM users;

-- 조건을 추가한 조회
SELECT * FROM users WHERE name = '홍길동';

-- 정렬하여 조회
SELECT * FROM users ORDER BY created_at DESC;

-- 특정 개수만 조회
SELECT * FROM users LIMIT 5;

-- 단일 행 삽입
INSERT INTO users (name, email) VALUES ('홍길동', 'hong@example.com');

-- 여러 개의 행 삽입
INSERT INTO users (name, email) VALUES ('장원영', 'won@example.com'), ('카리나', 'sm@example.com')

-- 특정 사용자의 이메일 수정
UPDATE users SET email= 'new@example.com' WHERE name = '홍길동'

-- 모든 데이터 삭제 (AUTO_INCREMENT 값 유지)
DELETE FROM users;

3️⃣ DCL (Data Control Language, 데이터 제어어)

• 데이터베이스에 대한 접근 권한을 제어하는 명령어
• 주요 명령어:
  • GRANT : 사용자에게 특정 권한 부여
  • REVOKE : 부여된 권한 철회

-- 특정 사용자에게 SELECT, INSERT 권한 부여
GRANT SELECT, INSERT ON my_database.users TO user1@localhost;

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON my_database.users TO user1@localhost;

-- 특정 사용자에게서 SELECT, INSERT 권한 회수
REVOKE SELECT, INSERT ON my_database.users TO user1@localhost;

-- 모든 권한 회수
REVOKE ALL PRIVILEGES ON my_database.users TO user1@localhost;

 

📌INNER JOIN, OUTER JOIN, CROSS JOIN의 차이

1️⃣ INNER JOIN

• 조인하는 두 테이블에서 공통된 값이 있는 행만 반환
• 일치하지 않는 데이터는 제외됨

SELECT A.*, B.*
FROM A 
INNER JOIN B ON A.id = B.id;

2️⃣ OUTER JOIN

• LEFT OUTER JOIN : 왼쪽(A) 테이블의 모든 데이터를 가져오고, 오른쪽(B) 테이블에서 일치하는 데이터가 없으면 NULL 반환

SELECT A.*, B.*
FROM A 
LEFT OUTER JOIN B ON A.id = B.id;

• RIGHT OUTER JOIN : 오른쪽(B) 테이블의 모든 데이터를 가져오고, 왼쪽(A) 테이블에서 일치하는 데이터가 없으면 NULL 반환

SELECT A.*, B.*
FROM A 
RIGHT OUTER JOIN B ON A.id = B.id;

• FULL OUTER JOIN : 두 테이블의 모든 행을 가져오되, 매칭되지 않는 경우 NULL 반환

SELECT A.*, B.*
FROM A 
FULL OUTER JOIN B ON A.id = B.id;

3️⃣ CROSS JOIN

• 모든 행의 조합을 반환 (카테시안 곱)
• 조건 없이 테이블을 조인하면 모든 경우의 수가 생성됨

SELECT A.*, B.*
FROM A 
CROSS JOIN B;

 

📌SELECT 절 처리 순서 및 GROUP BY와 ORDER BY

SQL 쿼리는 논리적 처리 순서와 작성 순서가 다르다.

1️⃣ SELECT 절 처리 순서 (논리적 실행 순서)

1. FROM : 테이블 지정
2. JOIN : 여러 테이블을 조인
3. WHERE : 조건에 맞는 행 필터링
4. GROUP BY : 그룹화 수행
5. HAVING : 그룹화된 데이터에 조건 적용
6. SELECT : 최종적으로 필요한 컬럼을 선택
7. ORDER BY : 정렬 수행
8. LIMIT/OFFSET : 결과 개수 제한

SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC;

2️⃣ GROUP BY와 ORDER BY

• GROUP BY : 특정 컬럼을 기준으로 데이터를 그룹화
• ORDER BY : 결과를 정렬 (ASC가 default)
  • ASC (오름차순) 또는 DESC (내림차순) 지정 가능

 

📌CASCADE와 참조 무결성의 개념

참조 무결성(Referential Integrity) 은 관계형 데이터베이스에서 외래 키(Foreign Key) 제약 조건을 유지하는 개념이다.

1. CASCADE (연쇄 삭제/갱신)
   • 부모 테이블의 데이터가 변경될 때, 이를 참조하는 자식 테이블의 데이터도 자동으로 변경
2. CASCADE 옵션
   • ON DELETE CASCADE : 부모 레코드 삭제 시 자식 레코드도 자동 삭제
   • ON UPDATE CASCADE : 부모 레코드의 기본 키 변경 시 자식 테이블의 외래 키도 자동 변경

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
);

고객 정보가 삭제되면 관련 주문도 자동으로 삭제됨

 

📌DROP, TRUNCATE, DELETE 차이와 활용

1️⃣DELETE : 특정 조건에 맞는 데이터를 삭제 (WHERE 사용 가능)

• 데이터만 삭제, 테이블 구조는 유지
• ROLLBACK 가능 (트랜잭션 적용됨)
• 특정 행만 삭제할 때

DELETE FROM employees WHERE department = 'Sales';

2️⃣TRUNCATE : 테이블의 모든 데이터를 삭제 (WHERE 사용 불가)

• 데이터만 삭제, 테이블 구조 유지
• ROLLBACK 불가능 (DDL로 처리됨)
• 모든 데이터를 삭제하지만 테이블 구조는 유지할 때

TRUNCATE TABLE employees;

3️⃣ DROP : 테이블 자체를 삭제

• 데이터뿐만 아니라 테이블 구조도 삭제됨
• ROLLBACK 불가능
• 테이블을 아예 삭제할 때

DROP TABLE employees;