[DBMS] Common(3) - SQL

개요 및 표준

SQL

• Structured Query Language; 구조화된 질의어
• 관계형 데이터베이스의 데이터를 관리하고 처리하기 위한 표준 언어
• 대부분의 관계형 DBMS(MySQL, PostgreSQL, Oracle 등)에서 사용됨

SQL 표준

• ANSI (미국 표준 협회)와 ISO(국제 표준화 기구)에서 SQL 표준을 제정하고 관리
• DBMS 제조사는 이 표준을 따르면서도 자사만의 고유한 기능이나 문법 추가하기도 함

---

SQL 문법

기능에 따라 크게 네가지 종류로 구분

DDL(Data Definition Language)

• 데이터베이스 스키마를 정의, 수정, 삭제하는데 사용
• 데이터베이스 객체(테이블, 인덱스, 뷰 등)를 생성하거나 변경할 때 사용
• 주요 명령여
  • CREATE : 데이터베이스 객체 생성
    • CREATE DATABASE : 데이터베이스 생성
    • CREATE TABLE : 테이블 생성 (컬럼 이름, 데이터 타입, 제약조건 등 정의)
    • CREATE INDEX : 인덱스 생성
    • CREATE VIEW : 뷰 생성
  • ALTER : 기존 데이터베이스 객체 구조 변경
    • ALTER TABLE : 테이블에 컬럼을 추가/수정/삭제, 제약조건 추가/삭제 등
  • DROP : 데이터베이스 객체 삭제
    • DROP DATABASE : 데이터베이스 삭제
    • DROP TABLE : 테이블 삭제 (데이터와 스키마 모두 삭제)
    • DROP INDEX : 인덱스 삭제
    • DROP VIEW : 뷰 삭제
  • TRUNCATE
    • 테이블의 모든 데이터를 삭제하지만, 테이블 구조는 유지
    • DELETE 보다 빠르고 ROLLBACK 불가능할 수 있음

---

DML (Data Manipulation Language)

• 데이터베이스에 저장된 데이터를 검색, 삽입, 수정, 삭제하는데 사용
• 주요 명령어
  • SELECT : 데이터베이스에서 데이터를 조회
    • FROM : 데이터를 가져올 테이블을 지정
    • WHERE : 데이터를 필터링하기 위한 조건 지정
    • GROUP BY
      • 특정 컬럼의 값을 기준으로 데이터를 그룹화
      • 집계함수(COUNT, SUM, AVG, MIN, MAX 등)와 함께 사용
    • HAVING
      • GROUP BY 로 그룹화된 결과에 대한 조건을 지정
      • WHERE 은 그룹화 이전 개별 행에 조건을 적용하고 , HAVING 은 그룹화 후에 조건 적용
    • ORDER BY : 조회된 데이터를 특정 컬럼을 기준으로 정렬 (오름차 ASC, 내림차 DESC)
  • INSERT : 데이터 베이스에 새로운 데이터 삽입
  • UPDATE : 테이블에 저장된 기존 데이터 내용 수정, WHERE 절을 사용해 수정할 행 지정
  • DELETE : 테이블에서 데이터를 삭제, WHERE 절을 사용하여 삭제할 행을 지정

집계 함수

여러 행의 값을 그룹화하여 하나의 결과 값을 계산하는 함수

• COUNT(*) 또는 COUNT(column_name) : 행의 개수 계산, COUNT(column_name) 은 NULL 제외
• SUM(column_name): 숫자 값 총합 계산
• AVG(column_name) : 숫자 값 평균 계산
• MIN(column_name) : 최솟값
• MAX(column_name) : 최솟값

---

DCL (Data Control Language)

• 데이터의 보안, 접근 권한 부여/회수 등을 제어하는데 사용
• 주요 명령어
  • GRANT : 특정 사용자 또는 역할에게 데이터베이스 객체에 대한 권한(SELECT , INSERT , UPDATE , DELETE 등) 부여
  • REVOKE : 특정 사용자 또는 역할에게 부여했던 권한을 회수

---

TCL (Transaction Control Language)

• 트랜잭션(논리적인 작업 단위)을 관리하고 제어하는데 사용
• 주요 명령어
  • COMMIT : 트랜잭션 내에서 수해오딘 모든 변경 사항을 데이터베이스에 영구적 반영
  • ROLLBACK : 트랜잭션 내에서 수행된 변경 사항을 모두 취소하고 트랜잭션 시작 이전 상태로 되돌림
  • SAVEPOINT : 트랜잭션 내에서 중간 저장점 설정, ROLLBACK 시 SAVEPOINT 까지 되돌릴 수 있음

---

조인(JOIN) 연산

• 조인(JOIN)
  • 둘 이상의 테이블에 있는 데이터를 서로 연결하여 결과를 하나의 테이블처럼 보여주는 연산
  • 관계형 데이터베이스에서 테이블 간의 관계를 활용하여 데이터를 결합하는데 필수적
• 주요 조인 종류
  • INNER JOIN : 두 테이블에서 조인 조건에 맞는 행들만 결합, 가장 흔히 사용됨
  • LEFT JOIN (LEFT OUTER JOIN) : 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 조인 조건에 맞는 행을 결합. 오른쪽 테이블에서 조건에 맞지 않는 행은 NULL 값으로 채워짐
  • RIGHT JOIN (RIGHT OUTER JOIN) : 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 조인 조건에 맞는 행을 결합. 왼쪽 테이블에서 조건에 맞지 않는 행은 NULL 값으로 채워짐
  • FULL JOIN (FULL OUTER JOIN) : 왼쪽 테이블과 오른쪽 테이블의 모든 행을 결합. 조건에 맞지 않는 행은 NULL 값으로 채워짐
  • CROSS JOIN : 두 테이블의 모든 행을 가능한 모든 조합으로 결합. 결과 행 수는 ‘왼쪽 테이블 행수 * 오른쪽 테이블 행 수’
• 예시용 자료
  • employees 테이블   

    emp_id	name	department_id
    1	Armstrong	10
    2	Bill	20
    3	Chet	10
    4	Dave	30

  • departments 테이블 

    department_id	department_name
    10	HR
    20	Engineering
    40	Marketing

• INNER JOIN

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d on e.department_id = d.department_id;

name	department_name
Armstrong	HR
Bill	Engineering
Chet	HR

• LEFT JOIN

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d on e.department_id = d.department_id;

name	department_name
Armstrong	HR
Bill	Engineering
Chet	HR
Dave	NULL

• RIGHT JOIN

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d on e.department_id = d.department_id;

name	department_name
Armstrong	HR
Bill	Engineering
Chet	HR
NULL	Marketing

---

서브쿼리

• 서로 다른 SQL문 안에 포함된 SELECT 문을 의미
• 괄호로 묶어서 사용하며, 메인 쿼리에 필요한 데이터를 조회하는데 사용
• WHERE 절, FROM 절, SELECT 절 등 다양한 위치에 올 수 있음
• 예시용 테이블

emp_id	name	department_id	salary
1	Armstrong	10	5000
2	Bill	20	6000
3	Chet	10	5500
4	Dave	30	4000
5	Ella	10	7000

• 예시 1 : 서브쿼리를 이용한 최고 연봉자 조회

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

• 예시 2: 특정 부서 평균 연봉보다 연봉 높은 직원 조회

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = 10
);

• 예시 3: 특정 부서에 속한 특정 이름 직원 조회

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM employees
    WHERE name = 'Armstrong'
);