SQLD 요약 - DDL

원본

DDL은 이전글에서도 짧게 설명했듯이

데이터 정의어 (Data Definition Language)이다.

 

저의 사용 DB는 mariadb이고, MySQL, Oracle과 큰 차이가 없다.

 

https://beetr.tistory.com/84

SQLD 요약 - 관계형 데이터베이스 개요

 

DDL은 테이블의 구조를 정의한다고 했다. 테이블에 어떤 칼럼이 어떤 자료형을 가지는지 정의해야 올바른 데이터를 저장할 수 있다.

데이터 유형 (Data Type)

DB의 테이블에 특정 자료를 입력할 때, 그 자료가 어떤 형태인지 정의해 놓는 것이라고 생각하면 된다.

예를 들어 나이 데이터를 받는데 "김개똥"과 같은 데이터가 오면 안 되기 때문에 정해놓는 것이다.

다른 종류의 데이터가 들어온다면 DB는 에러를 발생시킨다.

 

대표적으로 관계형 DB에는 SQL, MySQL, MariaDB 등 다양한 DB가 있어  데이터 유형은 약간씩 차이가 있다.

숫자 Type 예시

기준	유형
ANSI/ISO	Numeric, Decimal, Dec, Smallint, Integer, int, BigInt, Float, Double Precision
SQL Server, Sybase	ANSI/ISO 기준에 맞춘  여러 숫자타입 + Money, SmallMoney
Oracle	Number

 

벤더 별로 장단점이 있으므로 사용자가 여러 상황을 고려해서 어떤 벤더사의 DB를 사용할 것인지 선택하면 된다.

 

 

1. Create Table

1. 테이블과 칼럼 정의

먼저 고유식별자(PK)를 선정한다. 고유 식별자는 1 ~ 여러개의 칼럼으로도 만들어질 수 있다.

그리고 DB에 관리할 칼럼들을 정의한다.

혹시 다른 테이블과 연관을 지어야 한다면, 외래키(FK)를 사용할 수 있다.

 

아래처럼 예를 들 수 있다.

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=document&uid=338

위에서 부서와 사원은 외래키를 통해 연결되어 있다.

사원에 부서 ID를 넣음으로 부서와 사원이 연결될 수 있다.

위처럼 칼럼을 정의한다는 것은 DB에 넣을 자료들을 하나씩 분리하는 것을 의미한다.

 

2. 제약 조건

제약조건은 데이터의 무결성을 유지하기 위한 방법이다.

테이블을 생성할 때 만들 필요는 없지만, ALTER TABLE 명령어로 추가, 수정할 때 이미 데이터가 입력되었다면 

복잡해지기 때문에 초기에 잘 잡아놓는 것이 중요하다.

 

제약조건의 종류는 위를 참고하면 될 것 같다.

 

추가적으로 Default라는 제약조건의 키워드가 있는데,

Null로 들어가는 걸 바꿀 수 있다.

 

예제로 간단한 테이블을 하나 만들어 봤다.

create table test1 (
	num int auto_increment primary key,
	id varchar(50) default 'ㅋㅋ',
	name varchar(50) not null
);

여기서 데이터를 

num은 auto_increment(자동 증가) 로 걸어주어 자동으로 값이 증가하고,

id는 적지 않고 name에만 '김개똥' 이라는 값을 줬을 때 

위와 같이 정상적으로 실행되는 것이 보인다.

 

test1에 있는 모든 데이터를 select 문으로  조회해 보면 위와 같이 1, ㅋㅋ, 김개똥

이 나오는 걸 볼 수 있다.

 

정리하자면 default 옵션은 값이 주어지지 않았을 때 기본값을 준다는 것을 알 수 있다.

 

만든 테이블의 구조를 확인할 때는 어떻게 할까?

간단하게 desc 테이블 이름을 입력하면 된다.

물론 DBeaver나 workbench 등 sql문 작성을 도와주는 tool이 있다면 더 쉽지만, 

배우는 입장에서는 명령어를 써보는 게 좋다고 생각한다.

 

테이블 복사하기

기존의 테이블을 복사할 때는 테이블 생성구문인

create table '테이블이름'( ~~~~~ );

를 사용하지 않고 create table '테이블'  AS Select ~~~ 를 쓰면 된다.

 

실제적으로 test1의 칼럼은 num id name으로 3개였는데, select 구문에서 2개를 선택해 2개만 가져오게 되었다.

편리하지만, Not nul을 제외한 다른 제약조건은 없어지기 때문에 조심!

 

2. ALTER TABLE (테이블 수정하기)

우리는 테이블을 생성할 때  CREATE TABLE을 사용했다.

생성된 테이블을 수정할 때는  ALTER TABLE 을 사용하면 된다.

 

테이블은 업무적인 요구 사항 변경 및 운영상 변경해야 할 때 수정될 수 있다.

ALTER TABLE로는 칼럼을 추가, 삭제 / 제약조건 추가, 삭제 등의 작업을 할 수 있다.

 

Add Column (칼럼 추가)

아래와 같고 [] 안의 것은 선택사항이다.

사용법은 아래 이미지와 같다.

1.  Alter Table '테이블명' Add ('컬럼명' '자료형' ) 또는
2.  Alter Table '테이블명' add column '컬럼명' '자료형' 

default , not null 은 가능한데 PK가 이미 정의되어 있다면

SQL Error [1068] [42000]: (conn=14) Multiple primary key defined

를 뱉는다.

 

Drop Column (칼럼 추가)

테이블에서 필요 없는 칼럼을 삭제할 수 있고, 데이터가 있어도 상관없다.

한 번에 한 개의 칼럼만 삭제할 수 있고, 최소 하나 이상의 칼럼이 테이블에 있어야 한다.

삭제된 칼럼은 복구가 불가능하다.

 

사용법은 Add column과 유사하다.

사용법은 alter table '테이블명' drop column '칼럼명'; 이다.

 

Modify Column (칼럼 수정)

 

속성을 변경

-- Oracle
ALTER TABLE 테이블명 MODIFY ('칼럼1' '데이터유형' [DEFAULT 식] [NOT NULL], '칼럼2' 데이터 유형 …);

-- MariaDB
ALTER TABLE 테이블명 MODIFY COLUMN 칼럼명 칼럼자료형 [DEFAULT 식] [NOT NULL];

여러 개의 속성을 바꾸고 싶으면 Oracle의 경우 위 Query처럼 하면 되고, 

MariaDB는 세미콜론 전에 콤마(,)를 찍 은 후 MODIFY COLUMN 문을 또 작성해 주면 된다.

 

 

-- MariaDB
alter table test1 change '원래컬럼명' '바꿀컬럼명' '원래컬럼 데이터타입' [null | not null 등];
alter table test1 change whtea whtea2 int(50) null;

위처럼 칼럼타입도 변경할 수 있다 (물론 컬럼 안에 데이터가 있다면 에러가 난다.)

 

SQL은 속성 변경 시 기존 데이터가 훼손될 수 있기 때문에 varchar(50)에서 varchar(20) 처럼 적은 용량으로 갈 수 없다.

 

 

칼럼의 이름을 변경

-- Oracle
alter table test1 rename column whteaa to whtea;

-- MariaDB
alter table test1 change 원래컬럼명 바꿀컬럼명 데이터타입 [null | not null 등];
alter table test1 change whtea whtea2 int(50) null;

MariaDB는 속성까지 써줘야 한다.

 

 

ADD Constraint (제약조건 추가)

테이블 생성 이후에 필요에 의해 칼럼의 제약조건을 추가할 수 있다.

 

-- SQL
Alter Table test2 Add Constraint [제약조건]
Alter Table test2 Add Constraint test2_fk foreign key (test3.num) References test3(num);

 

DROP Constraint (제약조건 삭제)

test 테이블에 test3_fk라는 제약조건을 추가했다.

 

Drop Constraint의 명령어는 다음과 같다.

-- Oracle & MariaDB
Alter table test3 drop constraint test3_fk;
Alter table 테이블명 drop constraint 제약조건이름;

 

 

Rename Table(테이블 이름변경)

간단하게 테이블 이름을 변경하는 질의문이다!

 

-- Oracle & MariaDB
rename table 테이블명 to 바꿀테이블명;

 

Drop Table (테이블 삭제)

테이블 자체를 지우고 싶을 때 Drop Table을 사용한다.

초기에 테이블을 잘못 만들었거나, 테이블이 필요가 없어졌을 때 삭제하기 위해 사용한다.

-- Oracle & MariaDB
drop Table 테이블명;

Truncate Table ( 테이블 비우기 )

이건 테이블을 DROP 하는 것과 다르다.

Truncate는 테이블 구조는 놔두고, 저장된 데이터를 싹- 비우는 것이다.

 

비유하자면 drop은 박스를 통째로 갖다 버리는 것이고, truncate는 내용물만 비우는!

명령어는 간단하다. 

-- Oracle & MariaDB
Truncate Table 테이블명;