SQL<DDL, DML, 확장자>

1. 테이블 생성하기(DDL)

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

CREATE TABLE에서는 컬럼별로 제약조건을 설정할 수 있음.

제약 조건이란 ?

- 데이터의 무결성을 보장하는데 도움이 되는 시스템

 

제약 조건의 종류

ex) JOBS 테이블 생성하기

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

 

2. 데이터 조작하기(DML)

 

1) 데이터 삽입하기

#기본 구성
INSERT INTO {테이블명} ({컬럼1}, {컬럼2}) VALUES
(데이터, 데이터),
(데이터, 데이터),
...,
(데이터, 데이터);

 

ex) JOBS 테이블

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);

 

2) 데이터 수정하기

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

where로 조건을 부여하여 조건에 맞는 값만 수정

 

ex) Employee 테이블

...

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

 

3) 데이터 삭제하기

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

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

where절이 없으면 모든 행 삭제, 있으면 특정 조건을 만족하는 행 삭제

 

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

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

 

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

-- 테이블 삭제 DROP TABLE employees;

 

3. 테이블 연결하기

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 );

foreign key(외래키)로 jobs 테이블의 job_id를 사용하여 연결해준 모습

ERD

ERD에서도 연결이 된 것을 확인할 수 있다.

외래키를 참조하는 이유?

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

 - 연결된 컬럼이 한쪽 테이블에서 바뀐다면? 다른 쪽도 바뀌어야 함.

 

관계형 데이터베이스는 서로 밀접하게 연관 되어있으므로 외래키와 같이 테이블을 연결하는 중요한 정보는 일관성있게 처리하는 것이 매우 중요하다. 따라서 사전에 ERD를 탐색하는 것이 중요하다 라는 것을 알게되었다.

 

4. CTE와 VIEW

임시테이블을 생성할 일이 생겼을 때, 

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

② 데이터 베이스 용량이 늘어나지 않았으면 할 때 사용한다.

 

1) CTE Expression

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

WITH 구문으로 정의한다.

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

 

2) VIEW

가상 테이블의 개념

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

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

 

추가 참고)

■ 인덱스 생성

CREATE INDEX {인덱스 별명} ON {테이블명} ({컬럼명});

■ 인덱스 삭제

DROP INDEX {인덱스 별명} ON {컬럼명};

■ 테이블 변경 : ALTER

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

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

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