데이터분석 study/SQL

TIL_24.08.01_DDL, DML

justdata 2024. 8. 1. 20:12

▶key의 개념 알기

이름 설명 특징
기본 키(Primary Key) 각 행을 고유하게 식별하게 사용되는 하나 이상의 컬럼 - 한 테이블에는 하나의 키만 존재
- NULL 값을 가질 수 없음
- 각 행에 고유한 값
외래 키(Foreign Key) 한 테이블의 컬럼이 다른 키의 기본키를 참조하는데 사용되는 컬럼 - 부모 테이블의 기본키와 동일
- 제약조건을 통해 참조 무결성을 유지할 수 있음

 

● 식별자 vs 기본키

    ○ 식별자는 데이터베이스 설계 단계에서 사용하는 용어이며

         각 행을 고유하게 식별할 수 있는 모든 속성 혹은 속성 조합을 나타냄

 

    ○ 기본키는 각 각행을 고유하게 식별하는데 사용되는 선택된 식별자이며

         테이블간의 관계(JOIN 등)을 기술하기 위해 사용

 

● 참조무결성이란 두 테이블 간의 관계에서 데이터의 일관성을 유지하기

     위한 규칙 --> 추후 두 테이블의 외래키를 설명할 때 부가 설명 예정

 

● 그 외에도 기본키가 되지 못한 후보키, 대체키, 복합키, 슈퍼키 등이 

    있다.

 

 

▶테이블 생성하기 (DDL) 

CREATE TABLE {테이블명} (
{컬럼1} {자료형} PRIMARY KEY  #일반적으로PK가 첫 번째옴
{컬럼2} {자료형}
{제약조건}    #서로 관계를 가지고 있는 RDB 특성상 제약조건이 필요할때가 있음
);

 

  * CREATER TABLE 에서는 컬럼별로 제약조건을 설정할 수 있다.

     제약조건은 데이터의 엄격한 관리를 위해서 정해 놓은 규칙들이다.

     데이터의 무결성(결함이 없음)을 보장하는데 도움이 되는 시스템.

 

* 제약조건 종류

이름 설명
UNIQUE 해당 열에서 중복된 값 입력 불가
NOT NULL NULL 값 허용 안됨
PRIMARY KEY 각 행의 고유한 식별자이면서 NULL 존재 불가
(UNIQUE + NOT NULL)
FOREIGN KEY 한 테이블에서 다른 테이블의 컬럼 연결
DEFEAULT 값을 넣지 않을 경우 기본 값
CHECK 값이 특정 범위 안에 들어오도록 설정
(ex. CHECK (AGE>=18))
AUTO_INCREMENT 행이 추가될 때마다 자동으로 고유한 값을 가지도록 설정

 

EX)

CREATER TABLE jobs(
	job_id VARCHAR(10) PRIMARY KEY,
    job_title VARCHAR(100), 
    min_salary INT,
    max_salary INT
);

 

 

▶데이터 조작하기(DML)

   - 테이블 데이터 삽입하기

INSERT INTO {테이블명} ({컬럼1}, {컬럼2}) VALUES
(데이터, 데이터),
(데이터, 데이터)
...
(데이터, 데이터)

 

EX)

INSERT INTO jobs (job_id, job_title, min_salary, max_salary) VALUES
('AD_PRES', 'President', 20080, 40000),
('AD_VP','Administration Vice President', 15000, 30000),
('AD_ASST','Administration Assistant', 3000, 6000);

 

 

EX) HR.Employee 테이블 생성, 데이터 추가하기

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    phone_number VARCHAR(20),
    hire_date DATE,
    job_id VARCHAR(10),
    salary INT,
    commission_pct DECIMAL(5, 2),
    manager_id INT);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id) VALUES
(100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17', 'AD_PRES', 24000, NULL, NULL),
(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21', 'AD_VP', 17000, NULL, 100),
(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13', 'AD_VP', 17000, NULL, 100)

 

 

● 데이터 수정하기

UPDATE {테이블명}
SET {업데이트할 컬럼} = {값}
WHERE {조건식}  #where 조건문 없다면 모든 데이터 수정

 

ex)

UPDATE employees
SET salary = 20000
WHERE job_id = 'AD_VP' AND salary = 17000;

 

● 데이터 삭제하기

    - DELETE는 특정 행을 삭제, 구조와 인덱스는 유지

DELTE FROM {테이블명} 
WHERE {조건식}; #where 조건문 없으면 모든 데이터 삭제

 

EX)

-- 특정 조건을 만족하는 행 삭제
DELETE FROM employees WHERE job_id = 'AD_VP';

-- 테이블의 모든 행 삭제
DELETE FROM employees;

 

  - TRUNCATE : 모든 데이터를 삭제하나 구조와 인덱스는 유지

 

 EX)

-- 테이블의 모든 행 삭제
TRUNCATE TABLE employees;

 

 - DROP : 테이블 자체를 삭제하여 구조와 인덱스 모두 날림

-- 테이블 삭제
DROP TABLE employees;

 

 

▶테이블 연결하기

 

-테이블 생성

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    phone_number VARCHAR(20),
    hire_date DATE,
    job_id VARCHAR(10),
    salary INT,
    commission_pct DECIMAL(5, 2),
    manager_id INT,
    FOREIGN KEY (job_id) REFERENCES jobs(job_id)
    ON UPDATE CASCADE
);

 

- 데이터 넣기

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id) VALUES
(100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17', 'AD_PRES', 24000, NULL, NULL),
(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21', 'AD_VP', 17000, NULL, 100),
(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13', 'AD_VP', 17000, NULL, 100)

 

● 외래키와 참조를 설정하는 이유는?

    - 데이터는 결점이 없어야 한다. --> 무결성

    - 만약 연결된 Column이 한쪽이 바뀐다면 다른쪽도 당연히 바뀌어야 한다.

 

#JOBS 테이블의 JOB_ID: AD_VP 를 VP로 바꿔보자
UPDATE JOBS
SET JOB_ID = 'VP'
WHERE job_id = 'AD_VP';

 

● 테이블 변경 : ALTER

#컬럼 추가
ALTER TABLE {테이블명}
ADD COLUMN {컬럼명} {자료형};

#컬럼 삭제
ALTER TABLE {테이블명}
DROP COLUMN {컬럼명};

#외래 키 추가
ALTER TABLE {테이블명}
ADD CONSTRAINT {규칙별명}
FOREIGN KEY ({컬럼1}) REFERENCES {테이블}({컬럼2});

 

▶CTE 와 VIEW

    - 데이터베이스에서 테이블 생성 권한이 없거나

    - 데이터베이스 용량이 늘어나지 않았으면 하는 상황일 때

       'CTE Expression 과 view' 사용

 

● CTE Expression

     - 서브쿼리를 만들 듯이 일시적으로 사용할 수 있는 결과

     - WITH 구문으로 정의

WITH {TEMP1} AS
( {쿼리내용})
SELET * FROM {TEMP1};

 

● VIEW 

    - 가상테이블의 개념

    - 데이터베이스에 실제 데이터를 저장하는 것이 아닌 실시간 참조 (VIEW)

CREATE VIEW {뷰이름} AS
SELET * FROM {테이블명};