[MySQL] DBeaver 테이블 생성, 수정, 연결 실습 (CREATE, UPDATE, ALTER)

 

오늘은 디비버에서 테이블을 생성하고, 내용을 변경하고, 마지막으로 테이블끼리 키로 연결해주는 법을 살펴보겠습니다. 

 

테이블 생성 및 삭제 

테이블 생성, 데이터 삽입, 그리고 삭제는 단일 실행만 가능합니다. 

 

CREATE

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

 

테이블을 생성할 때는 데이터 무결성을 보장하기 위해 아래와 같은 제약 조건을 추가해야 합니다. 

UNIQUE	해당 열에 중복된 값 입력 불가
NOT NULL	NULL 값 허용 안됨
PRIMARY KEY	기본키로 설정 (UNIQUE + NOT NULL)
FOREIGN KEY	다른 테이블과의 연결 칼럼
DEFAULT	값을 넣지 않을 경우 기본 값으로 설정
CHECK	값이 특정 범위 안에 들어오도록 설정 e.g. CHECK (AGE >= 10)
AUTO_INCREMENT	행이 추가될 때마다 자동으로 고유한 값을 가지도록 설정

 

테이블을 만들었으면 데이터를 넣어줘야겠지요? 

 

INSERT

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

 

DROP

 

DROP TABLE {테이블명};

---

 

바로 테이블을 하나 만들어봅시다. 

CREATE TABLE jobs (
    job_id VARCHAR(10) PRIMARY KEY, # 문자 10자까지 입력 허용, 기본키로 지정
    job_title VARCHAR(100),
    min_salary INT, # 정수형 데이터
    max_salary INT
);

 

쿼리를 실행시키면 아래와 같이 빈 테이블 jobs이 생성된 것과 속성이 제대로 들어간 것을 확인할 수 있습니다.

테이블 확인은 좌측 내비게이터에서 데이터베이스 새로고침 (F5) 후 테이블 보기 (F4) 로 확인할 수 있습니다. 

 

 

생성된 빈 테이블에 데이터를 넣어줍니다. 

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

 

테이블 삭제는 간단합니다. 

DROP TABLE jobs;

 

---

 

테이블 수정

UPDATE

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

 

DELETE

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

 

TRUNCATE

TRUNCATE 는 구조와 인덱스는 유지한 채로 모든 행을 삭제합니다.

TRUNCATE TABLE {테이블명};

 

---

직접 jobs 테이블의 내용을 수정해봅니다. 

job_id AD_ASST 의 max_salary 가 6000에서 8000으로 변경된 것을 확인할 수 있습니다. 

 

UPDATE jobs 
SET max_salary = 8000
WHERE job_id = 'AD_ASST' ;

 

 

앞서 CREATE, INSERT, DELETE 는 단일 사용만 가능하다고 언급했는데요

만약 기존 데이터 수정이 아닌 새로운 칼럼을 생성해야 하는 경우,

즉 테이블 자체를 변경해야 하는 경우 어떻게 해야 할까요?  

 

바로 ALTER 명령어를 사용하면 됩니다. 

 

 

ALTER

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

#컬럼 속성 변경
ALTER TABLE {테이블명}
MODIFY COLUMN {컬럼명} {자료형};

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

 

 

other라는 칼럼을 추가해서 데이터를 넣어봅니다. 

ALTER TABLE jobs
ADD COLUMN other VARCHAR(10);

UPDATE jobs 
SET other = 'new data';

 

☞  ALTER 와 SELECT CAST 는 뭐가 다른가요?

• ALTER는 테이블 자체의 속성을 변경해주는 것으로 데이터 원본을 수정하는 명령어
• SELECT CAST 는 SELECT 로 불러온 데이터를 보는 형식을 바꿔주는 것으로 실제 원본 데이터는 유지됨  

alter table employees 
modify column email varchar(255) ;

select manager_id, cast(manager_id as char(50))
from employees ; # 원본이 바뀌는 것은 아님, select로 보는 데이터만 바뀌는 것

 

 

---

 

테이블 연결

테이블 연결을 본격적으로 하기 전에 관계형 데이터베이스의 Key 에 대한 개념을 알아야 합니다.

기본 키 Primary Key	각 행을 고유하게 식별할 때 사용되는 칼럼	한 테이블에는 하나의 기본 키만이 존재 NULL 값을 가질 수 없음 각 행에 고유한 값으로 들어감
외래 키 Foreign Key	한 테이블의 칼럼이 다른 키의 기본 키를 참조하는 데 사용되는 칼럼	부모 테이블의 기본 키와 동일 제약조건을 통해 참조 무결성을 유지할 수 있음

 

☞ 키는 식별자와 어떻게 다른가요? 

• 식별자는 DB 설계 단계에서 사용하는 용어로, 각 행을 고유하게 식별할 수 있는 모든 속성 혹은 속성 조합
• 기본키는 각 행을 고유하게 식별하는데 사용되는 선택된 식별자로, 테이블 간의 관계를 기술하기 위해 사용

 

FOREIGN KEY REFERENCES

새로 테이블을 생성하는 경우, CREATE 하단에 아래 코드를 추가해주면 됩니다. 

반면 이미 생성된 테이블일 경우, ALTER 로 외래키를 추가해줍니다. 

# 새 테이블
CREATE TABLE {테이블명} (
{컬럼명1} {자료형} PRIMARY KEY  
{컬럼명2} {자료형} {제약조건}  
FOREIGN KEY ({컬럼1}) REFERENCES {연결테이블}({컬럼2})
ON UPDATE CASCADE
);

# 기존 테이블 수정
ALTER TABLE {테이블명}
ADD CONSTRAINT {규칙별명}
FOREIGN KEY ({컬럼1}) REFERENCES {테이블}({컬럼2});

 

---

 

jobs 테이블과 이어줄 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
);

 

employess 테이블 생성과 jobs 테이블과의 연결을 확인할 수 있습니다.  

 

job_id 로 연결된 두 테이블 중 하나의 테이블의 job_id 를 변경하면, 다른 테이블도 같이 변경됩니다. 

jobs 테이블의 JOB_ID를 AD_VP 에서 VP로 바꾸면 employees 테이블도 같이 변경됨을 확인할 수 있습니다. 

UPDATE JOBS
SET JOB_ID = 'VP'
WHERE job_id = 'AD_VP';

변경 전

변경 후

 

 

 

이상 SQL 의 DDL, DML 로 테이블을 생성 및 조작하는 방법에 대해 알아봤습니다. 

 

 

 

 

오랑우탄이 영어를 하고 오랑이가 쿼리마스터가 되는 그날까지~