[ MySQL ] 02. 데이터베이스 , 데이터형 , 제약조건

데이터베이스 , 데이터형 , 제약조건

 

 

https://survey.stackoverflow.co/2023/#technology

Stack Overflow Developer Survey 2023

 

 

 

 

 

정리

다음은 MySQL의 DML, DCL, DDL 명령어를 표로 정리한 내용입니다:

유형풀 네임기능주요 명령어

유형	설명	종류
DML	데이터를 조회, 삽입, 수정, 삭제하는 작업	SELECT, INSERT, UPDATE, DELETE
DCL	데이터베이스 사용 권한을 부여하거나 취소하는 작업	GRANT, REVOKE
DDL	데이터베이스 구조를 정의, 수정, 삭제하는 작업	CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT

 

•  DML (데이터 조작 언어): 데이터 조작과 관련된 명령어 ( CRUD ) **
  • SELECT: 데이터 조회
  • INSERT: 데이터 삽입
  • UPDATE: 데이터 수정
  • DELETE: 데이터 삭제
• DCL (데이터 제어 언어): 사용자 권한과 관련된 명령어
  • GRANT: 권한 부여
  • REVOKE: 권한 회수
• DDL (데이터 정의 언어): 데이터베이스 구조와 관련된 명령어
  • CREATE: 테이블, 데이터베이스 등 생성
  • ALTER: 테이블 구조 수정
  • DROP: 테이블, 데이터베이스 삭제
  • TRUNCATE: 테이블의 모든 데이터 삭제
  • RENAME: 테이블 또는 데이터베이스 이름 변경
  • COMMENT: 테이블이나 컬럼에 주석 추가

 

 

---

 

데이터 형식

공식문서

사이트

바로가기

 

 

정수형(Integer Types)

명령어	설명
TINYINT	1바이트, -128에서 127 또는 0에서 255 범위의 작은 정수.
SMALLINT	2바이트, -32,768에서 32,767 또는 0에서 65,535 범위의 정수.
MEDIUMINT	3바이트, -8,388,608에서 8,388,607 또는 0에서 16,777,215 범위의 정수.
INT	4바이트, -2,147,483,648에서 2,147,483,647 또는 0에서 4,294,967,295 범위의 정수.
BIGINT	8바이트, -9,223,372,036,854,775,808에서 9,223,372,036,854,775,807 또는 0에서 18,446,744,073,709,551,615 범위의 정수.

 

 

 

고정 소수점

명령어	설명
DECIMAL 또는 NUMERIC	소수점 고정 위치 숫자, 정확한 소수점 숫자를 저장.

 

 

 

부동소수점(Floating-Point Types)

명령어	설명
FLOAT	4바이트, 단정도 부동소수점 숫자.
DOUBLE	DOUBLE PRECISION: 8바이트, 배정도 부동소수점 숫자.

 

 

 

문자열(String Types)

명령어	설명
CHAR	고정 길이 문자열, 최대 255자.
VARCHAR	가변 길이 문자열, 최대 65,535자.
TINYTEXT	최대 255자.
TEXT	최대 65,535자.
MEDIUMTEXT	최대 16,777,215자.
LONGTEXT	최대 4,294,967,295자.

 

검색시 CHAR가 더 빠르고  VARCHAR 컬럼 길이값이 4글자보다 적을 경우 CHAR로 자동 변환됩니다.

 

 

바이너리(Binary Types)

명령어	설명
BINARY	고정 길이 바이너리 데이터, 최대 255바이트.
VARBINARY	가변 길이 바이너리 데이터, 최대 65,535바이트.
TINYBLOB	최대 255바이트의 바이너리 데이터.
BLOB	최대 65,535바이트의 바이너리 데이터.
MEDIUMBLOB	최대 16,777,215바이트의 바이너리 데이터.
LONGBLOB	최대 4,294,967,295바이트의 바이너리 데이터.

 

 

 

날짜 및 시간(Date and Time Types)

명령어	설명
DATE	YYYY-MM-DD 형식.
TIME	HH:MM:SS 형식.
DATETIME	날짜와 시간, YYYY-MM-DD HH:MM:SS 형식.
TIMESTAMP	날짜와 시간, YYYY-MM-DD HH:MM:SS 형식, 현재 시간으로 자동 업데이트 가능.
YEAR	연도, 4자리 (YYYY) 또는 2자리 (YY).

 

 

그외

 

 

• ENUM
  • 열거형, 미리 정의된 값 중 하나를 저장.
  • 예: ENUM('value1', 'value2', 'value3')
• SET
  • 집합형, 미리 정의된 값 중 여러 개를 저장.
  • 예: SET('value1', 'value2', 'value3')

 

 

 

예제

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age TINYINT UNSIGNED,
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN,
    profile_picture BLOB,
    preferences SET('email', 'sms', 'push_notification')
);

 

 

• id: INT형, 자동 증가, 기본 키.
• name: VARCHAR(100), 최대 100자, NULL 불가.
• age: TINYINT형, 음수가 아닌 작은 정수.
• salary: DECIMAL(10, 2), 총 10자리 숫자 중 소수점 이하 2자리.
• hire_date: DATE형, 날짜.
• is_active: BOOLEAN, 논리 값 (0 또는 1).
• profile_picture: BLOB, 바이너리 데이터.
• preferences: SET, 미리 정의된 값 중 여러 개를 저장.

 

 

---

 

 

 

제약조건

제약조건	설명	예
NOT NULL	컬럼에 NULL 값을 허용하지 않음. 반드시 값을 입력해야 함	name VARCHAR(50) NOT NULL
UNIQUE	컬럼의 모든 값이 고유해야 함. 동일한 값이 중복 입력될 수 없음	email VARCHAR(100) UNIQUE
PRIMARY KEY	테이블에서 각 행을 고유하게 식별하는 컬럼. 각 테이블에 하나만 존재 가능. 자동으로 NOT NULL 및 UNIQUE 속성 포함	id INT PRIMARY KEY
FOREIGN KEY	다른 테이블의 PRIMARY KEY를 참조하여 테이블 간의 관계를 정의함. 데이터 참조 무결성을 보장	FOREIGN KEY (user_id) REFERENCES users(id)
CHECK	컬럼에 입력될 수 있는 값에 대한 조건을 설정. MySQL에서는 CHECK는 8.0.16 버전부터 지원됨	age INT CHECK (age >= 18)
DEFAULT	특정 컬럼에 값이 지정되지 않았을 때 기본값을 설정	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
AUTO_INCREMENT	숫자형 컬럼에서 값이 자동으로 증가되도록 설정. 주로 PRIMARY KEY에 사용됨	id INT PRIMARY KEY AUTO_INCREMENT
INDEX	검색 속도를 빠르게 하기 위해 사용. 중복된 값을 허용하며, 여러 개의 인덱스를 생성할 수 있음	CREATE INDEX idx_name ON users(name)

 

1. NOT NULL: 이 제약조건은 특정 컬럼이 NULL 값을 허용하지 않도록 합니다. 즉, 데이터가 입력될 때 반드시 값이 있어야 합니다.
2. UNIQUE: 이 제약조건은 컬럼의 모든 값이 고유하도록 보장합니다. 동일한 값을 중복해서 입력할 수 없습니다.
3. PRIMARY KEY: PRIMARY KEY는 테이블의 각 행을 고유하게 식별하는 역할을 합니다. 하나의 테이블에 하나만 존재하며, 자동으로 NOT NULL과 UNIQUE 제약조건을 포함합니다.
4. FOREIGN KEY: FOREIGN KEY는 테이블 간의 관계를 정의하는 데 사용되며, 다른 테이블의 PRIMARY KEY를 참조하여 데이터 참조 무결성을 보장합니다.
5. CHECK: 특정 컬럼의 값에 대해 조건을 설정하는 제약조건입니다. 예를 들어, age 컬럼에 18세 이상의 값만 허용하려면 CHECK (age >= 18)으로 설정할 수 있습니다.
6. DEFAULT: 컬럼에 값이 입력되지 않았을 때 기본값을 설정할 수 있습니다. 예를 들어, created_at 컬럼에 기본적으로 현재 시간이 입력되도록 할 수 있습니다.
7. AUTO_INCREMENT: AUTO_INCREMENT는 주로 PRIMARY KEY로 사용되는 숫자형 컬럼에서 자동으로 값을 증가시키는 역할을 합니다.
8. INDEX: INDEX는 테이블의 특정 컬럼에 인덱스를 생성하여 데이터 검색 속도를 빠르게 합니다.

 

PRIMARY KEY

테이블의 각 행을 고유하게 식별하는 열 또는 열 집합입니다. 한 테이블에 하나의 PRIMARY KEY만 있을 수 있으며, PRIMARY KEY로 설정된 열은 자동으로 NOT NULL과 UNIQUE 제약조건을 가집니다.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

 

 

FOREIGN KEY

다른 테이블의 PRIMARY KEY 또는 UNIQUE 열을 참조하는 열입니다. 데이터의 참조 무결성을 유지합니다.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

 

 

UNIQUE

열 또는 열 집합이 고유한 값을 가지도록 보장합니다. 여러 UNIQUE 제약조건을 한 테이블에 적용할 수 있습니다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

 

 

 

NOT NULL

열에 NULL 값을 허용하지 않습니다.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

 

 

DEFAULT

열에 대한 기본값을 지정합니다. 행이 삽입될 때 명시적인 값이 제공되지 않으면 기본값이 사용됩니다.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'Pending'
);

 

AUTO_INCREMENT

정수형 열에서 사용되며, 새로운 행이 삽입될 때마다 자동으로 고유한 값이 증가합니다. PRIMARY KEY 또는 UNIQUE 제약조건과 함께 사용됩니다.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

 

CHECK

열의 값이 특정 조건을 만족하도록 제한합니다. MySQL 8.0.16 이상 버전에서 지원됩니다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

 

 

예제 테이블

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

 

 

• id: AUTO_INCREMENT와 PRIMARY KEY 제약조건을 가집니다.
• name: NOT NULL 제약조건을 가집니다.
• email: UNIQUE 제약조건을 가집니다.
• age: CHECK 제약조건을 가집니다 (18세 이상).
• salary: NOT NULL 제약조건을 가집니다.
• hire_date: DEFAULT 제약조건을 가집니다 (기본값은 현재 날짜).
• department_id: FOREIGN KEY 제약조건을 가집니다 (다른 테이블의 id를 참조).

 

 

 

---

 

 

 

 

1. 데이터베이스 생성

데이터베이스를 생성하려면 CREATE DATABASE 명령어를 사용합니다.

CREATE DATABASE 데이터베이스명;

 

 

2. 데이터베이스 존재 여부 확인 후 생성

이미 존재하는 데이터베이스를 생성하지 않으려면 IF NOT EXISTS를 사용합니다.

CREATE DATABASE IF NOT EXISTS my_database;

 

 

3. 데이터베이스 진입 (사용)

USE 명령어로 특정 데이터베이스로 진입하여 그 데이터베이스 내에서 작업을 할 수 있습니다.

USE my_database;

 

 

4. 데이터베이스 목록 확인

모든 데이터베이스를 확인하려면 SHOW DATABASES 명령어를 사용합니다.

SHOW DATABASES;

 

5. 테이블 생성

문법 

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

 

 

• table_name: 생성할 테이블의 이름
• column1, column2: 테이블의 각 컬럼 이름
• datatype: 해당 컬럼의 데이터 타입 (예: INT, VARCHAR(50), DATE 등)
• constraints: (선택사항) 해당 컬럼에 적용할 제약조건 (예: NOT NULL, PRIMARY KEY, AUTO_INCREMENT 등)

 

 

 

테이블을 생성할 때는 CREATE TABLE을 사용합니다. 

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

 

 

• id: 자동으로 증가하는 정수형 기본 키
• name: 100자 이내의 문자열, NOT NULL 제약조건
• email: 100자 이내의 고유한 문자열 (중복 허용되지 않음)
• created_at: 기본값으로 현재 시간을 사용하는 타임스탬프

 

테이블이 이미 존재할 경우 에러를 방지하려면 IF NOT EXISTS 옵션

CREATE TABLE IF NOT EXISTS table_name (
    ...
);

 

 

6. 테이블에 데이터 추가

데이터를 삽입할 때는 INSERT INTO를 사용합니다. 예를 들어, student 테이블에 데이터를 추가하는 방법은 다음과 같습니다.

INSERT INTO student (name, student_number, address, phone_number)
VALUES ('John Doe', 'S1234567', '123 Main St', '555-1234');

 

7. 테이블 이름 변경

테이블의 이름을 변경하려면 RENAME TABLE을 사용합니다.

RENAME TABLE student TO students_info;

 

 

8. 테이블 삭제

테이블을 삭제할 때는 DROP TABLE 명령어를 사용합니다.

 

DROP TABLE IF EXISTS student;

 

 

9. 필드(컬럼) 이름 변경

테이블의 필드 이름을 변경할 때는 ALTER TABLE과 CHANGE를 사용합니다.

ALTER TABLE student CHANGE name full_name VARCHAR(100);

 

 

10. 필드(컬럼) 삭제

필드를 삭제하려면 ALTER TABLE과 DROP COLUMN을 사용합니다.

 

ALTER TABLE student DROP COLUMN phone_number;