3. 데이터베이스 생성과 관리 - 패스트캠퍼스 백엔드 부트캠프 3기

1. 데이터베이스(스키마)

• MySQL에서는 데이터베이스를 스키마(Schema)로 표현
• 데이터베이스(스키마) 안에 여러 테이블이 포함됨
• 데이터베이스(스키마) 생성

CREATE DATABASE DBNAME;

• 데이터베이스(스키마) 조회

SHOW DATABASES;

• 데이터베이스(스키마) 사용

USE DBNAME;

• 데이터베이스(스키마) 삭제

DROP DATABASE DBNAME;

• 테이블 생성

CREATE TABLE 테이블이름 (
    열이름1 자료형, [DEFAULT 기본값] [NULL|NOT NULL],
    열이름2 자료형, [DEFAULT 기본값] [NULL|NOT NULL],
    ...
);

• 자료형
  • 숫자형
    • TINYINT : 1바이트의 정수(-128~127)
    • SMALLINT : 2바이트의 정수(-32768~32767)
    • MEDIUMINT : 3바이트의 정수(-8388608~8388607)
    • INT : 4바이트의 정수(-2147483648~2147483647)
    • BIGINT : 8바이트의 정수(-9223372036854775808~9223372036854775807)
    • FLOAT : 4바이트의 부동 소수점 실수
    • DOUBLE : 8바이트 부동 소수점 실수
    • DECIMAL : 고정 소수점 실수
  • 문자형
    • CHAR : 고정 길이 문자열
    • VARCHAR : 가변 길이의 문자열
    • BLOB : 바이너리 데이터
    • TEXT : 텍스트 데이터
  • 날짜/시간형
    • DATE : 날짜
    • TIME : 시간
    • DATETIME : 날짜와 시간(4 바이트)
    • TIMESTAMP : 날짜와 시간(8 바이트, Timezone적용: 시스템Timezone 변경되면 함께 변경)
  • 기타 데이터 타입
    • ENUM : 정해진 값들 중 하나만 저장
    • SET : 정해진 값들 중 여러 개를 저장
    • GEOMETRY : 지리 정보 저장
    • XML : XML 데이터 저장
    • JSON : JSON 데이터 저장
    • 그 외 기타
• 테이블 생성 예제1

CREATE TABLE users(
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    birthdate DATE,
    registration_date TIMESTAMP
);

• 추가 정보

CREATE TABLE users(
    user_id INT PRIMARY KEY AUTO INCREMENT, // 기본 키로 지정, 자동으로 1씩 증가
    username VARCHAR(50) NOT NULL,	// username은 비어서는 안된다.
    email VARCHAR(100)UNIQUE,	// 고유 값
    birthdate DATE,
    registration_date TIMESTAMP	// DEFAULT CURRENT_TIMESTAMP 디폴트 값으로 현재 시간설정
);

• 테이블 생성 예제2

CREATE TABLE members(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)	// 기본 키 지정
    CONSTRAINT pk_eg PRIMARY KEY(id)	// 제약조건 : pk_eg
);

• 테이블 생성 예제3

CREATE TABLE orders(
    id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    status VARCHAR(255) NOT NULL DEFAULT'pending',	// 디폴트 값 지정
    PRIMARY KEY(id)
);

CREATE TABLE products(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(category_id) REFERENCES categories (id)	
    // 외래 키 참조 category_id를 FK로 지정, 
    // categories의 id를 참조하여 해당 id가 있으면 등록 없으면 등록 실패
    
);

• 테이블들 조회

SHOW TABLES;
SHOW TABLES FROM DBNAME;

• 테이블 조회

DESCRIBE TABLENAME;	// DESC와 같음

• 테이블 변경 - 이름 변경

RENAME TABLE old_name TO new_name;

• 테이블 생성 예제5

CREATE TABLE users(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    birthdate DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE products(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

DESC users;
DESC categories;
DESC products;

• 테이블 변경 - ALTER TABLE
• alter_option : 열 추가, 열 삭제, 열 변경, 제약 추가/삭제 등...

ALTER TABLE tbl_name alter_option

# 열 추가
### ALTER TABLE 테이블이름 ADD 열정의;
ALTER TABLE members ADD COLUMN age INT;

# 열 삭제
### ALTER TABLE 테이블이름 DROP COLUMN 열이름;
ALTER TABLE members DROP COLUMN birth_date;

# 열 타입, 이름 변경
### ALTER TABLE 테이블이름 MODIFY 열정의;
ALTER TABLE members MODIFY age VARCHAR(255);
### ALTER TABLE 테이블이름 CHANGE 기존열이름 열정의;
ALTER TABLE members CHANGE age birth_date DATE;

# 기본 키 제약 추가
### ALTER TABLE 테이블이름 ADD PRIMARY KEY (열이름);
ALTER TABLE members ADD PRIMARY KEY(id);

# 외래 키 추가
### ALTER TABLE 테이블이름 ADD FOREIGN KEY (열이름) REFERENCES 참조할테이블(참조할열);
ALTER TABLE order_itmes ADD FOREIGN KEY (product_id) RFERENCES products(product_id);

# NOT NULL 제약 추가
### ALTER TABLE 테이블이름 MODIFY 열정의 NOT NULL
ALTER TABLE members MODIFY id VARCHAR(30) NOT NULL;


# NOT NULL 제약 삭제
ALTER TABLE members MODIFY id VARCHAR(30);

# 기본키 제약 삭제(제약 이름으로도 삭제 가능)
ALTER TABLE members DROP PRIMARY KEY;

• 테이블 삭제

DROP TABLE TABLENAME;		// 테이블 구조까지 삭제

TRUNCATE TABLE TABLENAME;	// 테이블 구조는 유지, 행만 삭제