SQL

데이터 정의

CREATE TABLE

• CREATE TABLE 테이블_이름(
      속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
      [PRIMARY KEY (속성_리스트)]
      [UNIQUE (속성_리스트)]
      [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
      [ON DELETE 옵션] [ON UPDATE 옵션]
      [CONSTRAINT 이름] [CHECK(조건)]
  );
  

• 테이블 생성

• 속성의 정의

  • INTEGER (INT)
  • SMALLINT
  • DECIMAL(p,s) 또는 NUMERIC(p,s): 고정 소수점 숫자를 저장, p는 전체 자릿수, s는 소수점 이하 자릿수
  • REAL, FLOAT(n): 실수형 데이터를 저장, 부동 소수점 숫자
  • DOUBLE PRECISION: REAL보다 더 큰 범위의 부동 소수점 숫자를 저장
  • CHAR(n): 고정 길이 문자열을 저장, n은 문자열의 길이
  • VARCHAR(n), CHARACTER VARYING(n): 가변 길이 문자열을 저장, n은 문자열의 최대길이
  • DATETIME, DATE: 날짜 정보
  • TIME: 시간 정보를 저장
  • TIMESTAMP: 날짜와 시간 정보를 함꼐 저장

• NOT NULL

  • 해당 속성이 NULL 값을 허용하지 않음

• DEFAULT

  • 해당 속성의 기본값 지정

• PRIMARY KEY

  • 기본키 지정
  • 각 튜플을 유일하게 식별할 수 있게 한다.

• UNIQUE

  • 대체키 지정, 기본키와 유사하게 튜플일 유일하게 식별
  • NULL 허용, 한 테이블에 여러 개 지정 가능

• FOREIGN KEY

  • 외래키 지정, 참조 무결성을 지키기 위해 사용
  • ON DELETE 옵션: NO ACtion, CASCADE, SET NULL, SET DEFAULT
  • ON UPDATE 옵션: 위와 동일

• CHECK

  • 특정 속성에 대한 제약조건 지정
  • CONSTRAINT 키워드로 제약 조건에 이름 부여 가능

속성 추가

• ALTER TABLE 테이블_이름
    ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];

• //MySQL 예시
  ALTER TABLE students
    ADD age INT NOT NULL DEFAULT 18;

속성 삭제

• ALTER TABLE 테이블_이름 DROP 속성_이름 CASCADE | RESTRICT;

• CASCADE 와 RESTRICT 옵션으로 처리 방법 선택

• //MySQL 예시
  ALTER TABLE students
    DROP COLUMN age;

제약조건 추가

• ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;

• //MySQL 예시
  ALTER TABLE students
    ADD CONSTRAINT chk_age CHECK (age >= 18);

제약조건 삭제

• ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;

• //MySQL 예시
  ALTER TABLE students
    DROP CHECK chk_age;

테이블 삭제

• DROP TABLE 테이블_이름 CASCADE | RESTRICT;

• //MySQL 예시
  DROP TABLE students;

---

데이터 조작

데이터 검색

• SELECT [ALL | DISTINCT] 속성_리스트 FROM 테이블_리스트;
• 속성의 이름, 테이블의 이름은 콤마로 구분하여 나열 가능
• 검색 결과를 테이블 형태로 반환
• 모든 속성을 검색하기 위해서는 *를 사용
• ALL 키워드는 튜플의 중복을 허용
• DISTINCT 키워드는 튜플의 중복 제거
• AS 키워드로 변경할 이름을 지정하면 속성의 이름을 변경하여 출력
• 산술식을 이용한 검색 가능

• SELECT * FROM employees;
  SELECT DISTINCT department FROM employees;
  SELECT first_name, last_name, salary*1.1 AS adjusted_salary FROM employees;

조건 검색

• SELECT [ALL | DISTINCT] 속성_리스트 FROM 테이블_리스트 WHERE 조건;
• 논리 연산자와 비교 연산자 사용
  • =
  • <>
  • <
  • >
  • <=
  • >=

• SELECT * FROM employees WHERE salary > 50000;
  SELECT first_name, last_name FROM employees WHERE department = 'IT' AND salary >= 60000;

LIKE 연산자를 사용한 검색

• 검색 조건을 정확히 알지 못해 부분적으로 일치하는 데이터 검색 가능
• 문자열을 이용하는 조건에만 사용 가능
• %: 0개 이상의 문자
• _ : 한 개의 문자

• SELECT * FROM employees WHERE first_name LIKE 'J%';
  SELECT * FROM employees WHERE email LIKE '%@example.com';

NULL을 이용한 검색

• IS NULL
• IS NOT NULL
• = NULL 이나 <> NULL 로 표현하지 않고 위 방법 사용

• SELECT * FROM employees WHERE manager_id IS NULL;
  SELECT * FROM employees WHERE last_evaluation IS NOT NULL;

정렬 검색

• SELECT [ALL | DISTINCT] 속성_리스트 FROM 테이블_리스트 WHERE 조건 ORDER BY 속성_리스트 [ASC | DESC];
• 사용자가 원하는 순서로 출력하기 위해 ORDER BY 사용
• 기본 정렬은 오름 차순
• 먼저 나온 조건이 우선순위가 높음

• SELECT * FROM employees ORDER BY last_name ASC, first_name ASC;
  SELECT * FROM employees WHERE department = 'IT' ORDER BY salary DESC;

집계함수를 이용한 검색

• COUNT: 속성 값의 개수
• MAX : 최댓값
• MIN : 최솟값
• SUM : 합계 (숫자 데이터만 가능)
• AVG : 평균 (숫자 데이터만 가능)
• 집계함수는 NULL은 제외하고 계산
• 집계함수는 WHERE 절에서는 사용불가
  • SELECT 절이나 HAVING 절에서만 사용 가능
• COUNT는 테이블의 모든 속성에 적용하여 개수 계산
• DISTINCT 키워드를 사용해 특정 속성 값의 중복 제거 후 집계함수 적용가능

• SELECT COUNT(*) FROM employees;
  SELECT COUNT(DISTINCT department) FROM employees;
  SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees;

그룹별 검색

• GROUP BY: 테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색 하기 위해 사용
• HAVING: 그룹에 대한 조건 추가
• 그룹별로 검색할 때는 집계 함수나 GROUP BY 절에 있는 속성 외의 속성은 SELECT 절에서 사용불가

• SELECT department, COUNT(*) FROM employees GROUP BY department;
  SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;

여러 테이블에 대한 조인 검색

• 조인 검색: 여러 테이블을 연결하여 검색

• 조인 속성: 테이블을 연결해주는 속성, 조인 검색을 하기 위해 필요

• 조인 속성의 이름은 달라도 되지만 도메인은 동일해야 함

• SELECT employees.first_name, employees.last_name, departments.name FROM employees JOIN departments ON employees.department_id = departments.id;

• MySQL Join 예시

  • Implicit join (암시적 조인)

    • WHERE 절에서 테이블 간의 관계를 정의

    • SELECT *
      FROM employees e, departments d
      WHERE e.department_id = d.id;

  • Explicit join (명시적 조인)

    • 테이블 간의 관계를 FROM 절에서 JOIN 키워드를 사용해 정의

    • SELECT *
      FROM employees e
      JOIN departments d ON e.department_id = d.id;

  • Inner join

    • 두 테이블에서 일치하는 행만 반환하는 조인
    • 일치하는 데이터가 없으면 결과에 포함 안함

    • SELECT *
      FROM employees e
      INNER JOIN departments d ON e.department_id = d.id;

  • Left outer join

    • 왼쪽 테이블의 모든 행과 일치하는 오른쪽 테이블의 행을 반환
    • 일치하지 않는 경우 NULL 사용

    • SELECT *
      FROM employees e
      LEFT OUTER JOIN departments d ON e.department_id = d.id;

  • Right outer join

    • 오른쪽 테이블의 모든 행과 일치하는 왼쪽 테이블의 행을 반환
    • 일치하지 않는 경우 NULL 사용

    • SELECT *
      FROM employees e
      RIGHT OUTER JOIN departments d ON e.department_id = d.id;

  • Full outer join

    • 두 테이블의 모든 행을 반환
    • 일치하지 않는 경우 NULL 값이 사용

    • SELECT *
      FROM employees e
      FULL OUTER JOIN departments d ON e.department_id = d.id;

  • Equi join

    • 두 테이블 간의 관계를 등호(=)를 사용해 정의하는 조인

    • SELECT *
      FROM employees e
      JOIN departments d ON e.department_id = d.id;

  • Natural join

    • 두 테이블에서 동일한 이름을 가진 모든 열에 대해 암시적으로 equi join 수행

    • SELECT *
      FROM employees
      NATURAL JOIN departments;

  • Cross join (Cartesian join)

    • 두 테이블의 모든 가능한 행 조합을 반환

    • SELECT *
      FROM employees e
      CROSS JOIN departments d;

  • Self join

    • 테이블이 자기 자신과 조인되는 경우
    • 주로 계층 구조나 상하 관계를 표현하는 데 사용
      • 이 경우에는 별칭(alias)을 사용하여 테이블을 구분

    • SELECT e1.name AS employee_name, e2.name AS manager_name
      FROM employees e1
      JOIN employees e2 ON e1.manager_id = e2.id;

서브 쿼리를 이용한 검색

• SELECT 문 안에 또 다른 SELECT 문을 포함 가능 (main query 와 sub query)

• sub query는 괄호로 묶어 작성, ORDER BY 절 사용 불가, 상위 쿼리보다 먼저 수행

• 단일 행 서브 쿼리는 일반 비교 연산자 사용 가능

• 다중 행 서브 쿼리는 일반 비교 연산자 사용 불가능

  • IN

    • 목록 내의 값 중 하나와 일치하는지 확인

    • SELECT * FROM employees WHERE department_id IN (1, 2, 3);

  • NOT IN

    • 목록 내의 어떤 값과도 일치하지 않는지 확인

    • SELECT * FROM employees WHERE department_id NOT IN (1, 2, 3);

  • EXISTS

    • 서브쿼리 결과의 존재 확인

    • SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.id = employees.department_id);

  • NOT EXISTS

    • 서브쿼리의 결과가 존재하지 않는지 확인

    • SELECT * FROM employees WHERE NOT EXISTS (SELECT 1 FROM departments WHERE departments.id = employees.department_id);

  • ALL

    • 비교 연산자와 함께 사용
    • 서브쿼리의 모든 결과와 조건을 만족하는지 확인

    • SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 1);

  • ANY 또는 SOME

    • 비교 연산자와 함께 사용
    • 서브쿼리의 결과 중 적어도 하나가 조건을 만족하는지 확인

    • SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 1);

데이터 삽입

직접 삽입

• 특정 테이블에 새로운 튜플을 추가
• 속성과 해당 값들을 명시적으로 지정
• INTO 절과 VALUES 일대일 대응

• INSERT INTO employees (id, first_name, last_name, age)
  VALUES (1, 'Alice', 'Johnson', 42);

서브쿼리 사용 데이터 삽입

• 다른 테이블에서 검색한 데이터를 대상 테이블에 삽입
• SELECT 문을 사용하여 원하는 데이터 선택

• INSERT INTO employees_backup (id, first_name, last_name, age)
  SELECT id, first_name, last_name, age FROM employees WHERE age > 40;

데이터 수정

• 테이블에 저장된 튜플의 특정 속성 값을 수정
• 수정할 속성 값과 조건을 지정

• UPDATE employees
  SET age = age + 1
  WHERE first_name = 'John' AND last_name = 'Doe';

데이터 삭제

• 테이블에서 특정 조건에 맞는 튜플 삭제
• WHERE 절을 사용하여 삭제할 조건 지정, 생략하면 모든 튜플 삭제

• DELETE FROM employees WHERE age > 60;

뷰

• 테이블 혹은 다른 뷰를 기반으로 만든 가상 테이블

• 논리적으로 존재하며 일반 테이블과 동일한 방법으로 사용 가능

• 기본 테이블: 기반이 되는 물리적인 테이블

• CREATE VIEW EmployeeView (EmployeeID, FullName, Department)
  AS SELECT id, CONCAT(first_name, ' ', last_name), department
  FROM employees;
  

• 생성된 뷰는 일반 테이블 처럼 검색 가능

• 삽입 삭제 수정도 가능하나 항상 가능한 것은 아님

• 뷰에 대한 작업은 기본 테이블에 대한 쿼리문으로 변환 후 수행

• 변경 가능한 뷰

  • CREATE VIEW SimpleEmployeeView (EmployeeID, FirstName, LastName)
    AS SELECT id, first_name, last_name
    FROM employees;

• 변경 불가능한 뷰

  • CREATE VIEW AggregatedEmployeeView (Department, TotalSalary)
    AS SELECT department, SUM(salary)
    FROM employees
    GROUP BY department;

  • 기본 키가 포함되지 않으면 변경 불가
  • 집계 함수로 새로 계산된 내용을 포함하면 변경 불가
  • DISTINCT, GROUP BY 포함하면 불가
  • 조인한 경우 불가(?) 아닐 수도 있음

• 삭제

  • DROP VIEW EmployeeView CASCADE;
     DROP VIEW EmployeeView RESTRICT;

Stored Procedure

쿼리문의 집합, 편리하게 사용할 수 있다.

장점

• 보안 : 쿼리를 알 수 없기 때문에 테이블의 형태를 외부에 감출 수 있다.
• 짧은 패킷 길이 : 명령어 길이가 비교적 짧기 때문에
• 애플리케이션 로직을 분리할 수 있다.

단점

• 처리 성능

JOIN 방식

Nested Loop Join

• 외부 테이블이 작고 내부 테이블의 결합 키 필드에 인덱스가 존재
• 실행계획에 따라 옵티마이저가 외부테이블 결정

Sorted Merge Join

• 조인 칼럼으로 정렬하여 조인 (두 테이블 모두 정렬)
• 인덱스가 없는 경우
• 정렬할 데이터가 많다면 부담
• 외부 테이블에만 인덱스가 있는 경우
• MySQL 8 기준으로 안씀

Hash Join

• 빌드 단계: 레코드가 작아 해시 테이블을 만들기 용이한 테이블로 메모리에 생성
• 프로브 단계: 나머지 테이블을 읽으면서 일치하는 것 찾기
• 주로 대용량 데이터에서 사용
• 외부 테이블이 Hash 공간에 전부 담길 수 있는 양일 때
• 해시 충돌 위험
• 중복 데이터가 적을 때