MySQL 4일차

MySQL은 DDL을 지원한다.

DDL로 데이터 구조를 생성, 변경, 제거할 수 있다.

DB 생성, 변경, 삭제 DDL은 아래와 같다.

1. 생성

DB 생성 시에는 아래와 같은 방법으로 생성한다.

1. default character set : 문자를 컴퓨터에 저장할 때 사용하는 규칙들의 집합
2. collate : 문자들을 서로 비교할 때 사용하는 규칙들의 집합

실제 사용 예시는 아래와 같다.

create database dbtest
default character set utf8mb3 collate utf8mb3_general_ci;

2. 변경

DB 변경 시에는 아래와 같은 방법으로 변경한다.

실제 사용 예시는 아래와 같다.

alter database dbtest
default character set utf8mb3 collate utf8mb3_general_ci;

3. 삭제

DB 삭제 시에는 아래와 같은 방법으로 삭제한다.

실제 사용 예시는 아래와 같다.

drop database dbtest;

테이블 생성 시에는 아래와 같은 방법으로 생성한다.

제약 조건의 종류는 아래와 같다.

실제 사용 예시는 아래와 같다.

CREATE TABLE samsung_member (
	idx INT NOT NULL AUTO_INCREMENT,
    userid VARCHAR(16) NOT NULL,
    username VARCHAR(20),
    userpwd VARCHAR(16),
    emailid VARCHAR(20),
    emaildomain VARCHAR(50),
    joindate TIMESTAMP NOT NULL DEFAULT current_timestamp,
    constraint samsung_member.pk PRIMARY KEY (idx)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

제약 조건은 아래 따로 설정해주고, 이름도 붙여주는 것이 좋다.

MySQL은 데이터에 접근할 수 있도록 인덱스를 지원한다.

인덱스를 사용하면 데이터의 검색, 입력, 수정, 삭제 시간을 줄일 수 있다.
인덱스는 클러스터형 인덱스와 보조 인덱스가 있다. 각 인덱스의 특징은 아래와 같다.

특정 나열된 데이터들을 일정 기준으로 정렬해 주는 인덱스를 클러스터형 인덱스라고 한다.
클러스터형 인덱스의 특징은 아래와 같다.

1. 테이블 당 하나만 생성 가능하다.
2. 보조 인덱스보다 검색은 더 빠르지만, 입력과 수정과 삭제는 더 느리다.
3. MySQL에선 PK -> UNIQUE & NOT NULL -> 임의로 보이지 않는 컬럼 순으로
   클러스터형 인덱스를 지정한다.

후보키에 부여 가능한 인덱스를 보조 인덱스라고 한다.
보조 인덱스의 특징은 아래와 같다.

1. 테이블 당 여러개 생성 가능하다.
2. 생성시 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
3. 클러스터형 인덱스보다 입력과 수정과 삭제는 더 빠르지만, 검색은 더 빠르다.

인덱스의 문제점은 아래와 같다.

따라서 인덱스를 생성할 때는 아래와 같은 전략을 따른다.

MySQL은 사용자가 인덱스를 지정할 수 있도록 지원한다.

클러스터형 인덱스는 alter table로, 보조 인덱스는 create index로 인덱스를 생성할 수 있다.

보조 인덱스 생성 예시를 살펴보자. 단순 보조 인덱스는 아래와 같이 생성 가능하다.

create index samsung_address_area_idx
on samsung(address_area)

show index from samsung; 로 인덱스를 확인해보면 아래와 같다.

첫번째 인덱스는 클러스터형 인덱스로 기본키라 클러스터형 인덱스로 자동 설정됐다.
두번째 인덱스가 설정한 단순 보조 인덱스이다.

고유한 보조 인덱스는 아래와 같이 생성한다.

create unique index samsung_user_name_idx
on samsung(user_name);

이 때, user_name이 unique 하지 않으면 오류가 발생한다.

show index from samsung; 로 인덱스를 확인해보면 아래와 같다.

두번째 인덱스가 고유한 보조 인덱스이다.
고유한 보조 인덱스가 설정된 후 해당 열에 값이 중복되는 데이터를 insert 하면
오류가 발생한다.

클러스터형 인덱스는 alter table로, 보조 인덱스는 drop index로 인덱스를 삭제할 수 있다.
인덱스를 모두 삭제할 때는 보조 인덱스부터 삭제한다.

아래는 보조 인덱스 삭제 예시이다.

drop index samsung_user_name_idx on samsung;

show index from samsung; 로 인덱스를 확인해보면 아래와 같다.

MySQL은 View를 지원한다.

데이터베이스에 존재하는 가상의 테이블을 View라고 한다.
View는 필요한 데이터만 보여줌으로써 보안성이나 편의성을 확보하는 데 활용된다.

View의 특징은 아래와 같다.

1. 실제 행과 열을 가지고 있다.
2. 테이블처럼 물리적으로 저장되는 것은 아니다.
3. 테이블과 달리 실제 데이터가 없고 SQL만 저장한다.
4. 삽입, 삭제, 갱신 작업에 많은 제약이 걸리지만 가능하다.

View는 아래와 같은 방법으로 생성한다.

실제 사용 예시는 아래와 같다.

create or replace view vsam 
as 
select * 
from sam99 
where a > 3

위 코드의 의미는 아래와 같다.

sam99 table에서 a 속성값이 3 초과인 행만 모아 vsam 이란 View를 만들거나 교체한다.