테이블 관리

테이블 만들기

CREATE CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
    컬럼 정의,
    ...)
    [테이블 옵션 지정]

테이블의 이름은 2강을 참조하여 식별자 이름을 지정하는 규칙에 따릅니다. MySQL은 테이블 이름과 동일한 파일명을 만들기 때문에 파일명에 적합한 이름을 사용해야 합니다. 3강의 컬럼 타입을 참고로 컬럼들을 정의합니다. 2강에서 설명한 바와 같이 테이블의 저장 타입이 MyISAM인 경우 테이블 정의 정보를 담는 .frm 파일과 테이블 데이터를 저장한 .MYD 파일, 그리고 테이블 인덱스 정보를 담고 있는 .MYI 파일이 만들어 집니다. InnoDB 타입인 경우는 tablespace에 1개의 데이터 정보와 2개의 로그 정보를 담는 파일이 만들어 집니다.

TEMPORARY를 지정하면 1회용 테이블로 만들어져서 현재 연결이 유지되는 동안만 사용이 가능합니다. 현재 연결을 종료하면 자동으로 삭제됩니다. IF NOT EXISTS 지정은 이미 동일한 이름의 테이블이 존재할 경우 에러없이 문장의 실행을 중지시킵니다. 배치처리를 위해 사용합니다.

제약(Contraint)

컬럼 제약

컬럼정의: 컬럼_타입 [NOT NULL | NULL] [DEFAULT default_value]
    [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
    [COMMENT 'string']
    [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
    [STORAGE {DISK|MEMORY|DEFAULT}]
    [reference_definition]

NOT NULL

컬럼의 값으로 NULL을 입력할 수 없습니다. 이 제약이 지정된 컬럼은 반드시 유효한 값을 가져야 합니다.

PRIMARY KEY

컬럼은 반드시 NOT NULL로 지정되어야 하며, 중복 값을 가질 수 없습니다. 이 컬럼은 자동으로 인덱싱됩니다. 또한 테이블당 한 개의 PRIMARY KEY만을 정의할 수 있습니다. 하나 이상의 컬럼을 PRIMARY KEY로 지정하는 복합키를 만들 경우 컬럼 제약을 사용할 수 없고 테이블 제약을 사용하여 PRIMARY KEY를 지정해야 합니다.

UNIQUE

중복 값을 허용하지 않는 컬럼을 정의합니다. PRIMARY KEY와 달리 NULL을 허용합니다. 테이블당 여러 개의 UNIQUE를 허용합니다. 인덱싱이 이뤄집니다.

DEFAULT 'value'

컬럼의 디폴트 값을 지정합니다. INSERT시 컬럼에 값을 지정하지 않고 로우가 추가되면 디폴트 값이 자동으로 저장됩니다.

AUTO_INCREMENT

정수 타입의 컬럼에 지정할 수 있으며 컬럼에 NULL이나 0을 입력하면 자동으로 해당 컬럼의 가장 큰 값의 다음 값으로 증가합니다. 테이블 당 한 개의 제약만 사용할 수 있으며 인덱싱이 되어야 합니다.

REFERENCES

참조 키를 지정합니다.

테이블 제약

create_definition:
      col_name column_definition
    | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
        [index_option] ...
    | {INDEX | KEY} [index_name] [index_type] (index_col_name,...)
        [index_option] ...
    | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
        [index_name] [index_type] (index_col_name,...)
        [index_option] ...
    | {FULLTEXT | SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
        [index_option] ...
    | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) reference_definition
    | CHECK (expr)

PRIMARY KEY(컬럼 목록)

컬럼 제약을 통해 기본키를 지정할 수 있지만 기본키외에 복합키를 지정할 때 사용합니다. 일반적으로 기본키 지정시에도 사용합니다.

INDEX(컬럼 목록), KEY(컬럼 목록)

인덱스 컬럼입니다. ASC나 DESC를 옵션으로 사용하여 오름차순이나 내림차순으로 지정할 수 있습니다.

UNIQUE(컬럼 목록)

컬럼 제약과 동일한 속성을 가집니다.

FULLTEXT

Full-text 인덱스를 지원합니다. MATCH()와 같은 전체 문장을 대상으로 한 Full-text 검색 함수의 사용시 함께 지정되어야 합니다.

FORIGN KEY

외래키의 지정시 사용합니다.

CHECK

InnoDB 테이블의 경우 외래 키 제약 조건에 대한 확인을 제공합니다. 대부분의 다른 저장 엔진들은 무시합니다.

테이블을 만들어 봅시다.

컬럼 타입

숫자 타입

MySQL은 숫자 타입의 컬럼에 지정된 자리수에서 모자란 칸만큼 "0"으로 채워주는 "ZEROFILL" 속성을 지정하면 자동으로 숫자 타입의 컬럼에 "UNSIGNED" 속성을 추가합니다. "UNSIGNED" 속성과 "SIGNED" 속성을 함께 지원하고 있으며 디폴트로 부호있는 숫자를 쓰도록 되어 있기 때문에 "SIGNED"는 사용하지 않습니다.

"SERIAL"은 "BIG UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE"의 별칭(alias)으로 사용됩니다. 정수 타입 컬럼의 정의시 "SERIAL DEFAULT VALUE"는 "NOT NULL AUTO_INCREMENT UNIQUE"의 별칭(alias)입니다.

BIT[(M)]

비트 필드 타입. M은 bit의 길이를 나타내며 1 ~ 64의 값을 가질 수 있습니다. M을 생략시 디폴트 값은 1입니다.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

부호있는 타입은 -127 ~ 128의 값을 가지며, 부호없는 타입은 0 ~ 255의 값을 가집니다. 1byte 크기를 가집니다.

BOOL, BOOLEAN

TINYINT(1)의 동일한 표현(synonym)입니다. 0은 false를 의미하며 0이 아닌 값은 true를 의미합니다. "TRUE"와 "FALSE"는 그저 "1"과 "0"의 별칭(alias)으로 사용됩니다.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

부호있는 타입은 -32768 ~ 32767의 값을 가지며, 부호없는 타입은 0 ~ 65535의 값을 가집니다. 2byte 크기를 가집니다.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

부호있는 타입은 -8388608 ~ 8388607의 값을 가지며, 부호없는 타입은 0 ~ 16777215의 값을 가집니다. 3byte 크기를 가집니다.

INT[(M)] [UNSIGNED] [ZEROFILL]

부호있는 타입은 -2147483648 ~ 2147483647의 값을 가지며, 부호없는 타입은 0 ~ 4294967295의 값을 가집니다. 4byte 크기를 가집니다.

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

INT의 동일한 표현(synonym)입니다.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

부호있는 타입은 -9223372036854775808 ~ 9223372036854775807의 값을 가지며, 부호없는 타입은 0 ~ 18446744073709551615의 값을 가집니다. 8byte 크기를 가집니다.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

M은 전체 자리수(0 ~ 23)를 나타내며 D는 소수점 자리수를 나타냅니다. 부호있는 타입은 -3.402823466E+38 ~ -1.175494351E-38의 값을 가지며, 부호없는 타입은 0과 1.175494351E-38 ~ 3.402823466E+38의 값을 가집니다. 4byte 크기를 가집니다.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

M은 전체 자리수(24 ~ 53)를 나타내며 D는 소수점 자리수를 나타냅니다. 부호있는 타입은 -1.7976931348623157E+308 ~ -2.2250738585072014E-308의 값을 가지며, 부호없는 타입은 0과 2.2250738585072014E-308 ~ 1.7976931348623157E+308의 값을 가집니다. 8byte 크기를 가집니다.

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

DOUBLE의 동일한 표현(synonym)입니다. 만일 REAL AS FLOAT SQL 모드가 켜져 있으면 REAL은 DOUBLE보다는 FLOAT의 동일한 표현이 됩니다.

FLOAT(p) [UNSIGNED] [ZEROFILL]

p의 값이 0 ~ 23이면 FLOAT 타입으로 23 ~ 53이면 DOUBLE 타입으로 사용됩니다.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

십진 상수로 저장되며 M은 65까지 D는 30까지의 값을 가질 수 있으며 M과 D는 디폴트로 각각 10과 0 값을 가집니다.

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

DECIMAL의 동일한 표현입니다. FIXED 시노님은 다른 데이터베이스 시노님과 호환됩니다.

날자 타입

DATE

'1000-01-01' ~ '9999-12-31' 범위의 기간을 표현하며, MySQL은 'YYYY-MM-DD' 형식으로 표시합니다.

DATETIME

'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' 범위의 기간을 표현하며, MySQL은 'YYYY-MM-DD HH:MM:SS' 형식으로 표시합니다.

TIMESTAMP

'1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC 기간을 표현합니다. TIMESTAMP는 INSERT와 UPDATE시 자동으로 시간이 증가하고 NULL 값을 지정하면 최초 실행 시간이 자동으로 입력됩니다. MySQL은 'YYYY-MM-DD HH:MM:SS' 형식으로 표시합니다.

TIME

'-838:59:59' ~ '838:59:59' 범위의 시간을 표현하며, MySQL은 'HH:MM:SS' 형식으로 표시합니다.

YEAR[(2|4)]

4자리의 경우 1901 ~ 2155 범위의 년도와 0000년을 표현합니다. 2자리인 경우 70 ~ 69 범위의 년도를 표현하며 1970년에서 2069년을 의미합니다. MySQL은 'YYYY' 형식으로 표시합니다.

문자 타입

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

고정 길이 문자 타입으로 오른쪽 공백은 무시됩니다. 1 ~ 255 길이를 갖습니다. NATIONAL 속성이 지정되면 UTF-8을 이용합니다. 줄여서 NCHAR로 선언합니다.

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

가변 길이 문자 타입으로 오른쪽 공백은 무시됩니다. 1 ~ 65,535 길이를 갖습니다. UTF-8로 저장시 21,844 길이로 저장됩니다. MySQL은 255자까지는 1byte로 저장하며 255자를 넘는 경우 2byte로 저장합니다. VARCHAR는 CHARACTER VARYING의 줄임말입니다.

BINARY(M)

CHAR 타입과 비슷하지만 바이너리 바이트 데이터를 저장합니다.

VARBINARY(M)

VARCHAR 타입과 비슷하지만 바이너리 바이트 데이터를 저장합니다.

TINYBLOB

최대 255 (2⁸ – 1) 바이트의 BLOB 타입 데이터를 저장합니다.

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

최대 255 (2⁸ – 1) 글자의 문자 데이터를 저장합니다. 멀티 바이트의 경우 최대 길이내에서 저장됩니다.

BLOB[(M)]

최대 65,535 (2¹⁶ – 1) 바이트의 BLOB 타입 데이터를 저장합니다.

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

최대 65,535 (2¹⁶ – 1) 글자의 문자 데이터를 저장합니다.

MEDIUMBLOB

최대 16,777,215 (2²⁴ – 1) 바이트의 BLOB 타입 데이터를 저장합니다.

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

최대 16,777,215 (2²⁴ – 1) 글자의 문자 데이터를 저장합니다.

LONGBLOB

최대 4,294,967,295 혹은 4GB (2³² – 1) 바이트의 BLOB 타입 데이터를 저장합니다.

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

최대 4,294,967,295 혹은 4GB (2³² – 1) 글자의 문자 데이터를 저장합니다.

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

Enumeration 타입. 인자로 지정된 value1, value2, ... 중 하나의 값이나 NULL 값을 저장할 수 있습니다. 그외에 인자로 지정되지 않은 문자의 경우 공백으로 저장됩니다. 최대 65,535개까지 지정할 수 있습니다.

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

집합(set) 타입. ENUM과 달리 한 컬럼에 인자로 지정된 데이터를 0개 이상 저장할 수 있습니다. SET 타입의 컬럼은 최대 64개의 인자 집합을 저장할 수 있습니다.

데이터베이스를 만들어 봅시다.

이름 지정하기

식별자 이름을 만들기 위해서는 기본적으로 예약어나 특수 문자를 사용할 수 없지만 backtick(`) 문자로 묶으면 사용 가능합니다. "_"과 "$"는 특수문자가 아닌 일반 문자로 사용합니다.식별자는 숫자로 시작할 수 있지만 "1e"나 "1e3"과 같은 이름으로는 시작할 수 없습니다. "Me"나 "MeN"(M과 N은 정수를 의미함)과 같은 형식은 지수 형식의 숫자 표현이므로 식별자로 사용할 수 없습니다. MySQL은 사용자 정의 변수 사용이 가능하지만 사용자 정의 변수 이름은 직접 사용할 수 없습니다. 식별자는 Unicode(UTF-8)로 저장됩니다. multi-bytes 문자의 경우 최대길이를 유의해야 합니다.

MySQL은 마침표(.)로 구분된 식별자를 사용하여 테이블안의 컬럼 이름 등을 지정할 수 있습니다.

주석의 사용

• "#"(해쉬문자)로 시작하는 행.
• "-- "로 시작하는 행. MySQL의 연산자 표현과 충돌 문제로 "--" 뒤에 반드시 공백이 포함되어야 합니다.
• "/*"에서 "*/" 사이의 여러 행.

인코딩과 정렬

MySQL은 다양한 인코딩을 지원합니다. character set과 collation을 사용하여 적절한 인코딩을 지정해야 올바른 데이터의 정렬을 사용할 수 있습니다. 다음은 한글 사용시 필요한 collation과 character set입니다.

Collation의 접미사로 사용되는 "_ci"(Case Insensitive)는대소문자 구별 없음을 "_cs"(Case Sensitive)는 대소문자 구별을 "_bin"(binary)는 이진 데이터임을 의미합니다. Collation의 이름이 의미하는 것처럼 한글 및 유니코드 사용시 데이터의 알파벳은 저장시 대소문자의 구분은 되나 정렬시 대소문자의 구별을 하지 않습니다.

데이터베이스 만들고 지우기

CREATE DATABASE [IF NOT EXISTS] 데이터베이스_이름;

DROP DATABASE [IF EXISTS] 데이터베이스_이름;

데이터베이스를 만들거나 지울 때 데이터베이스의 유무에 따른 에러를 발생시키지 않도록 하기 위해서 "IF NOT EXISTS"와 "IF EXSISTS" 옵션을 사용합니다. 데이터베이스가 만들어지면 데이터베이스 저장 타입이 MyISAM일 경우 데이터베이스 이름과 같은 테이블 정보를 담고 있는 ".frm"확장자를 갖는 파일과 데이터를 담고 있는 ".MYD"(MYData) 확장자를 갖는 파일, 그리고 인덱스 정보를 담고 있는 ".MYI"(MYIndex) 확장자를 갖는 3개의 파일이 만들어 집니다. InnoDB 타입으로 저장할 경우 특별한 옵션이 없으면 tablespace 정보를 가지고 있는 10MB 크기의 "ibdata1" 파일과 log 정보를 가지고 있는 2개의 5MB 크기의 "ib_logfile0"와 "ib_logfile1"이 만들어 집니다.

CREATE DATABASE [IF NOT EXISTS] 데이터베이스_이름
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

추가로 데이터베이스를 만들 때 인코딩을 지정할 수 있습니다.

목차

1. MySQL 소개 - 데이터베이스는 무엇에 쓰는 물건인고? : DDL, DML
2. 제2강 데이터베이스 만들기
3. 제3강 필드의 타입
4. 제4강 테이블을 만들어 봅시다. - 제약(CONSTAINT), 테이블 수정하기
5. 강의실습 준비 - 우편번호 파일 준비하기
6. 기본적인 질의문 - SELECT ... FROM, Alias, ORDER BY, LIMIT
7. 아무거나 불러오지 말자 - WHERE 절 : 비교연산자, 논리연산자
8. BETWEEN ... AND, 집합연산(IN), LIKE 와 와일드카드
9. 당신의 생일을 데이터베이스에 남겨봅시다. - 날짜 및 시간 처리
10. 문자열 처리
11. 정규표현식 EXPR
12. GROUP BY 절과 HAVING절, 그룹 함수
13. 숫자 처리
14. INSERT ... VALUES, INSERT ... SET, INSERT ... SELECT, LOAD DATA INFILE(Bulk 데이터 입력하기)
15. UPDATE
16. DELETE, TRUNCATE
17. Subquery문
18. DDL 심화학습 (Strorage engine)
19. 트랜젝션의 처리
20. 권한부여 - GRANT, REVOKE

-- 시도 필드값 50개 출력
SELECT sido
FROM ziptable
LIMIT 50;
-- '서울'에는 '구'가 몇 개?
SELECT COUNT(*)
FROM ziptable
WHERE sido = '서울';
-- 시도는 총 몇 개?
SELECT COUNT(DISTINCT sido) -- 중복방지
FROM ziptable;
-- 서울시에 구는 총 몇 개?
SELECT COUNT(DISTINCT gugun)
FROM ziptable
WHERE sido = '서울';
-- 경기도 상록구에는 동이 총 몇 개? 필드명은 '동 수'
SELECT COUNT(DISTINCT dong) AS '동 수'
FROM ziptable
WHERE sido = '경기' AND gugun LIKE '%상록구%';
-- !=, <>

-- 9월 11일
-- 정렬
-- 경기도 상록구의 동을 내림차순으로 정렬하여 출력
SELECT DISTINCT dong
FROM ziptable
WHERE sido = '경기' AND gugun LIKE '%상록구%'
ORDER BY dong DESC;
-- 장안동이 있는 도 내림차순 , 구군은 오름차순 출력
SELECT DISTINCT gugun, sido
FROM ziptable
WHERE dong LIKE '%장안동%'
ORDER BY sido DESC, gugun ASC;
-- '대가리'나 '고도리'가 포함된 도 내림차순, 구군 오름차순
SELECT sido, gugun, dong
FROM ziptable
WHERE dong LIKE '%대가리%'
OR dong LIKE '%고도리%';