MySQL 테이블 관련 연산 가이드

一. 테이블이란

데이터베이스는 여러 개의 데이터 테이블로 구성됩니다. 각 테이블은 여러 개의 필드를 가지며, 각 필드는 필드명, 데이터 구조, 제약 조건을 포함합니다.

二. 테이블 생성

# 문법:
create table 테이블명(
필드명1 타입[(너비) 제약조건],
필드명2 타입[(너비) 제약조건],
필드명3 타입[(너비) 제약조건]
);

# 주의사항:
1.同一 테이블 내에서 필드명은 중복될 수 없습니다
2.너비와 제약조건은 선택사항입니다
3.필드명과 타입은 필수입니다

四. 테이블 구조 확인

Desc 테이블명;

五. 데이터 타입

(1) 수치형 타입

- INT: 부호 있음/없음 지원. 부호 있음 범위 -2147483648~2147483647, 없음 범위 0~4294967295. 최대 너비 11자리
- TINYINT: 부호 있음 범위 -128~127, 없음 범위 0~255. 최대 너비 4자리
- SMALLINT: 부호 있음 범위 -32768~32767, 없음 범위 0~65535. 최대 너비 6자리
- MEDIUMINT: 부호 있음 범위 -8388608~8388607, 없음 범위 0~16777215. 최대 너비 9자리
- BIGINT: 최대 너비 20자리
- FLOAT(M,D): 부호만 지원. 기본값 (10,2)
- DOUBLE(M,D): 부호만 지원. 기본값 (16,4)
- DECIMAL(M,D): 부호만 지원

(2) 날짜 및 시간 타입

- DATE: YYYY-MM-DD 형식, 1000-01-01부터 9999-12-31까지
- DATETIME: YYYY-MM-DD HH:MM:SS 형식, 1000-01-01 00:00:00부터 9999-12-31 23:59:59까지
- TIMESTAMP: 타임스탬프, 1970-01-01 00:00:00부터 2037-12-31 23:59:59까지
- TIME: HH:MM:SS 형식, -838:59:59~838:59:59
- YEAR(2|4): 2자리 또는 4자리 연도 저장

(3) 문자열 타입

- CHAR(M): 고정 길이 문자열, 길이 1-255. 지정 길이보다 작으면 오른쪽 공백으로 채움
- VARCHAR(M): 가변 길이 문자열, 길이 1-255. 길이 지정 필수
- BLOB 또는 TEXT: 최대 길이 65535. 이진 데이터 저장용. BLOB은 대소문자 구분
- TINYBLOB/TINYTEXT: 최대 길이 255
- MEDIUMBLOB/MEDIUMTEXT: 최대 길이 16777215
- LONGBLOB/LONGTEXT: 최대 길이 4294967295
- ENUM: 열거형. 예: ENUM('A','B','C')

六. 제약 조건

제약 조건은 테이블에서 강제 적용되는 데이터 검증 규칙입니다. 생성된 테이블의 데이터 무결성과 정확성을 보장합니다.

기본키 제약조건

기본키(Primary Key)는 테이블의 각 레코드가 고유하게 구분되도록 합니다. 기본키 열의 데이터는 유일해야 하며 NULL 값을 허용하지 않습니다. 기본키를 사용하면 테이블의 레코드를 고유하게 식별할 수 있고, 외래키와 함께 다른 테이블과의 관계를 정의할 수 있으며, 데이터베이스 查询 속도를 향상시킬 수 있습니다.

# 기본키는 두 가지 유형으로 구분됩니다:

# 단일 필드 기본키:
create table department(
    dept_id char(4) primary key,
    dept_name varchar(30),
    employee_count int
);

# 복합 기본키:
create table department(
    dept_id char(4),
    dept_name varchar(30),
    employee_count int,
    primary key(dept_name, dept_id)
);

NOT NULL 제약조건

NOT NULL 제약조건은 필드 값이 NULL이 될 수 없음을 의미합니다.

문법: 필드명 필드타입 not null

create table department(
    dept_id char(4) primary key,
    dept_name varchar(30) not null,
    employee_count int
);

UNIQUE 제약조건

UNIQUE 제약조건은 해당 열의 값이 고유해야 함을 요구합니다.

- NULL 값을 허용하지만 NULL은 하나만 존재할 수 있습니다
- 한 테이블에 여러 개의 UNIQUE 필드를 선언할 수 있습니다
- 문법: 필드명 데이터타입 unique

create table department(
    dept_id char(4) primary key,
    dept_name varchar(30) not null,
    employee_count int unique
);

DEFAULT 제약조건

DEFAULT 제약조건은 특정 필드의 기본값을 지정합니다.

- 레코드 삽입 시 DEFAULT 제약조건이 있는 필드에 값을 할당하지 않으면 자동으로 기본값이 설정됩니다
- 문법: 필드명 데이터타입 default 기본값

create table department(
    dept_id char(4) primary key,
    dept_name varchar(30) not null default '-',
    employee_count int unique
);

AUTO_INCREMENT 필드

AUTO_INCREMENT 필드: 테이블당 하나의 AUTO_INCREMENT 필드만 존재할 수 있으며, 반드시 기본키의 일부여야 합니다. 기본값은 1부터 시작합니다.

# 다양한 제약조건이 포함된 테이블 생성 예시
CREATE TABLE scores(
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(10) NOT NULL,
    korean_score INT DEFAULT 0,
    english_score FLOAT UNIQUE
);

연습문제: 다음 CREATE TABLE 문장을 설명해보세요

create table products(
    p_code char(10) not null,
    category_id int not null default 0,
    p_name char(255) not null,
    p_price decimal(8,2) not null,
    primary key(p_code)
);

七. 테이블 구조 연산 - 추가, 삭제, 수정, 조회

1 -- 1. 테이블 생성 (엑셀 시트와 유사)

    create table member_list(
        field1 type[제약조건],
        field2 type,
        ...
        fieldn type
    )[character set 문자집합];

    -- 직원 테이블 employee 생성

    create table employee(
       emp_id int primary key auto_increment,
       emp_name varchar(20),
       emp_gender bit default 1,
       birth_date date,
       hire_date date,
       position varchar(20),
       salary double(5,2) unsigned,
       description text
    );

    /* 제약조건:
       primary key (NOT NULL + UNIQUE): 현재 레코드를 고유하게 구분할 수 있는 필드
       unique
       not null
       auto_increment: 기본키 필드는 반드시 수치 타입이어야 함
       foreign key: 외래키 제약조건 */

-- 2. 테이블 정보 조회
    desc 테이블명        -- 테이블 구조 확인
    show columns from 테이블명  -- 테이블 구조 확인
    show tables        -- 현재 데이터베이스의 모든 테이블 조회
    show create table 테이블명  -- 테이블 생성 문 확인

-- 3. 테이블 구조 수정
   -- (1) 열(필드) 추가
      alter table 테이블명 add [column] 열명 타입［제약조건］［first｜after 필드명］;
      alter table user add address varchar(30) not null unique first/after username;
      -- 여러 필드 추가
      alter table users2
            add address varchar(30),
            add age int first,
            add birthdate varchar(20) after name;

   -- (2) 열 타입 수정
      alter table 테이블명 modify 열명 타입［제약조건］［first｜after 필드명］;
      alter table users2 modify age tinyint default 20;
      alter table users2 modify age int after id;
   
   -- (3) 열 이름 수정
      alter table 테이블명 change [column] 열명 새열명 타입［제약조건］［first｜after 필드명］;
      alter table users2 change age Age int default 28 first;

   -- (4) 열 삭제
      alter table 테이블명 drop [column] 열명;
      -- 여러 열 삭제 방법
      alter table users2
            add salary float(6,2) unsigned not null after name,
            drop address;    

   -- (5) 테이블 이름 변경
      rename table 현재테이블명 to 새테이블명;
   -- (6) 테이블 문자집합 변경    
      alter table student character set utf8;

-- 4. 테이블 삭제
    drop table 테이블명;

-- 5. 기본키 추가/삭제
    alter table 테이블명 add primary key(필드명,...)
    alter table users drop primary key;

    예시:
    mysql> create table test5(num int auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
    create table test(num int primary key auto_increment);
    -- 기본키 삭제 방법?
    alter table test modify id int;
    alter table test drop primary key;

-- 6. 유니크 인덱스
    alter table 테이블명 add unique [index|key] [인덱스이름](필드명,...)
    alter table users add unique(name)
    alter table users add unique key user_name(name);
    
    -- 복합 유니크 인덱스 추가
    alter table users add unique index name_age(name, age);
    
    -- 유니크 인덱스 삭제
    alter table 테이블명 drop {index|key} 인덱스이름

6월 2일 16:08에 게시됨

괴물 클럽