[DB] SQL(DDL)

*Rocky Linux 9.4와 MariaDB 기반으로 정리한 포스팅입니다. 

 

데이터를 담을 객체를 만들자: DDL(Data Definition Language)

DDL(Data Definition Language, 데이터 정의어)는데이터를 담을 객체(테이블, 데이터베이스, 컬럼..)을 생성/삭제/변경할 때 사용된다. 저번 포스팅에서 MariaDB를 설치하고, 최초 접속 및 비밀번호 설정하는 방법을 배웠으니, 이번엔 DDL을 이용해 데이터베이스와 테이블을 만들고 조작하는 방법을 알아보자. 

 

 먼저 show database; 명령어를 쳐보면 MariaDB를 설치했을 때, 기본적으로 생성되는 빌트인 데이터베이스(built-in database)들이 나온다.

<built-indatabase>

 

이러한 빌트인 데이터베이스들은 MariaDB를 운영하는데 기본적으로 필요한 정보들이 저장되어 있으므로, 아래의 설명을 참고하고, 지금 단계에서는 함부로 건들지 않는 것을 추천한다(MariaDB가 정상적으로 작동되지 않을 수도 있다). 

 

Built-in Database

mysql

• MariaDB(MySQL) 를 운영하는데 필요한 정보(중요한 메타데이터)들을 기록해 놓은 특수목적 DB
• 주요 정보: 사용자 계정 이름/비밀번호/권한 정보/스키마 등

information_schema

• MariaDB 안에 있는 데이터베이스, 데이블, 컬럼 등의 객체들에 대한 정보가 저장되는 곳
• 데이터베이스 구조와 관련된 정보를 저장하고, 사용자가 데이터베이스, 테이블, 열, 권한 등과 같은 다양한 메타데이터에 접근할 수 있도록 지원한다. 

performance_schema

• MySQL 및 MariaDB에서 제공하는 데이터베이스 내장 기능으로, 데이터베이스 서버의 성능을 모니터링하고 분석하는 데 필요한 정보를 제공
• 서버의 실행 상태와 성능 데이터를 수집하고, 이를 조회할 수 있는 테이블을 제공

test

• 테스트 목적으로 사용할 수 있게 제공되는 기본 데이터베이스
• 주로 개발과 테스트 용도로 사용됨(쿼리 및 기능 실험)

 

1) 데이터베이스 관련 명령어

show databases;

create database [생성할 DB명] // DB 생성

drop database [삭제할 DB명] // DB 제거

use [선택할 DB명] // DB 선택

<데이터베이스 생성/삭제/선택 명령어> 테

 

2) 테이블 관련 명령어

create table [생성할 테이블명] ( 

 [컬럼명1] [자료형] [제약조건1-1] [제약조건1-2] …. [제약조건1-n], 

 [컬럼명2] [자료형] [제약조건2-1] [제약조건2-2] …. [제약조건2-n] , 

 ....... 

 [컬럼명m] [자료형] [제약조건m-1] [제약조건m-2] …. [제약조건m-n] 

)

--예시--
create table memberTB(  

id char(10) unique not null,  

pass char(15) not null,  

name char(15) not null, 

point int unsigned default 100000,  

regdate date  

) DEFAULT CHARSET=utf8  


--해설--

1. memberTB라는 이름의 테이블을 생성하라 

2. id라는 이름의 컬럼을 생성하라. 문자만 입력가능, 최대 10바이트, 기본키 

3. pass라는 이름의 컬럼을 생성하라. 문자만 입력가능, 최대 15바이트, NULL 입력불가 

4. name라는 이름의 컬럼을 생성하라. 문자만 입력가능, 최대 15바이트, NULL 입력불가 

5. point라는 이름의 컬럼을 생성하라. 정수만 입력가능, 최대 15바이트, NULL 입력불가 

6. regdate라는 이름의 컬럼을 생성하라. 날짜만 입력가능. 

7. 테이블에 영어와 한글을 저장할 수 있도록 UTF-8 캐릭터셋 지정
(** 한글은 1글자에 2byte(EUC-KR), 3byte(UTF-8)을 차지한다 => char(15)로 하는 이유)

 

테이블을 생성할 때는 다음과 같이 테이블의 컬럼(column)에 대한 정보(자료형, 제약조건..)을 명시해줘야 한다. 따라서 CREATE TABLE 문을 정확하게 작성하기 위해서는 자료형(data type)과 제약 조건(constraints)에 대해 어느 정도 알고 있어야 한다. 매우매우 많지만 다음 정리를 참고해서 주로 사용하는 것 위주로 어느 정도는 알아두자. 

 

**자료형(Data Type)

int	정수(음수, 양수, 0)
float	부동소수(소숫점이 있는 수)
char	문자/문자열(string)
varchar	가변 문자열
date	날짜(YYYY-MM-DD)
datetime	시간을 포함한 날짜(YYYY-MM-DD HH:MM:SS)

*char과 varchar의 차이는 var(10)으로 자료형을 설정하고 5글자만 입력하더라도 10byte의 공간을 차지하는 것과 달리, varchar은 varchar(255)로 설정하고 1글자만 입력한다면, 1byte의 공간만 차지한다. varchar은 char에 비해 공간을 절약할 수 있다는 장점은 있지만, 동적으로 계산하는데 추가적인 계산이 필요하기 때문에 성능적인 면에서는 다소 불리하다. 

*더 많은 자료형에 대해서는 포스팅의 마지막 부분에 첨부할테니 필요하다면 참고하자!

 

**제약 조건(Constraints)

unique	컬럼에 중복되는 값을 입력할 수 없게 제한한다	ID, 주민번호..
not null	컬럼에 NULL을 허용하지 않음(빈칸 x)	ID, password..
check	조건에 맞는 값만 설정 가능
primary key	테이블의 기준열을 설정
foreign key	primary key에 있는 값만 설정 가능
default	기본값을 설정 (제약 조건은 아니지만, 같이 설정하는 경우가 많다)

*primary key를 설정하면 기본 값으로 NOT NULL과 unique가 함께 설정된다(고유해야 식별의 용도로 사용 가능하다).

 

**signed/unsigned

• signed: 양수와 음수 둘 다 저장 가능
• unsigned: 양수만 저장 가능한 자료형(음수는 불가)
• 숫자 관련 자료형들은 따로 설정하지 않으면 기본값으로 signed가 적용된다. 
• ex) int unsigned / float unsigned

 

 

다음으로는 ALTER TABLE을 이용하여 테이블의 구조를 변경하는 방법을 알아보자.

alter table [테이블명] add [컬럼명] [데이터타입] [제약조건] 
//컬럼 추가

alter table [테이블명]  change [기존컬럼명] [새로운컬럼명] [기존자료형] [기존제약조건] 
컬럼명 변경

alter table [테이블명] modify [기존컬럼명] [새자료형] [새제약조건]
//컬럼 데이터 타입, 제약 조건 변경

alter table [테이블명] drop [컬럼명] 
//컬럼 삭제

alter table [기존테이블명] rename [새테이블명] 
//테이블 이름 변경


--예시--
alter table memberTB add m_level int unsigned not null default 5

alter table memberTB change pass pwd char(15) not null 

alter table memberTB modify name char(60) null; 

alter table memberTB drop m_level;

alter table memberTB rename familyTB;

 

마지막으로 테이블의 목록 확인, 구조(컬럼) 확인, 제거하는 명령어는 다음과 같다. 

show tables; 
// 테이블 목록 확인(데이터베이스에 어떤 테이블이 있지?)

desc [확인할 테이블명]
// 테이블이 어떤 칼럼들로 이뤄져 있나

drop table [삭제할 테이블명]
// 테이블 삭제

<show tables, desc table>

 

 

 

 

힘들지만 오늘도 해낸 나를 위한 한 마디,

"게으름은 인간을 파멸시키는 주범이다", 벤자민 프랭클린

 

"스스로와 타협하고자 하는 생각이 든다면, 내가 진실로 성실하게 시간을 사용했는지부터 되물어보자"