[MySQL & PHP] 6장 테이블 변경하기

정리

1. 테이블의 칼럼 구조 변경하기

실제로 데이터베이스를 운용할 때, 테이블이 완성되고 나서도 테이블을 변경해야 하는 경우가 자주 있습니다. 예를 들어, '좀 더 많은 문자를 입력하고 싶다' 거나 '칼럼을 추가하고 싶다', '칼럼 이름을 변경하고 싶다' 라는 요구 사항이 있으면 테이블을 변경해야 합니다.

ALTER TABLE 명령

칼럼 구조를 변경할 때에는 ALTER TABLE 명령을 사용합니다. 변경 내용에 따라 다음과 같이 MODIFY 나 ADD, CHANGE, DROP 구문을 함께 사용합니다.

● 칼럼의 정의를 변경할 때	ALTER TABLE ... MODIFY ...
● 칼럼을 추가할 때	ALTER TABLE ... ADD ...
● 칼럼의 이름과 정의를 변경할 때	ALTER TABLE ... CHANGE ...
● 칼럼을 삭제할 때	ALTER TABLE ... DROP ...

먼저, 칼럼 구조를 변경하는 데 필요한 자료형 변경과 칼럼 추가, 칼럼 위치 변경, 칼럼 이름 변경, 칼럼 삭제에 대해 알아봅시다.

칼럼의 자료형은 언제든지 변경할 수 있습니다. 예를 들어, VARCHAR형으로 설정한 칼럼에 대량의 문자를 입력하기 위해 TEXT형으로 설정해야 할 경우에도 변경할 수 있습니다.

물론, 서로 대응할 수 있는 자료형이어야 합니다. 대응할 수 없는 자료형으로 변경하면 오류가 발생합니다. 또는 변경되었다 해도 무의미한 값이 되거나, 전부 또는 일부 데이터가 지워지는 일도 있으니 주의해야 합니다. 예를 들어, 100자를 입력할 수 있는 칼럼을 VARCHAR(50) 형으로 변경하면 당연히 50자 이후부터는 삭제되고 맙니다.

칼럼의 자료형을 변경할 때에는 자음 명령을 사용합니다.

칼럼의 자료형 변경

alter table 테이블_이름 modify 칼럼_이름 자료형;

먼저, 사원정보 테이블 tb1과 같은 내용의 테이블 tb1C를 준비합니다.

create table tb1C select * from tb1;

새로운 테이블 tb1C 를 생성하려하는데 tb1 의 모든 레코드를 그대로 복사 하겠습니다.

show tables;

(데이터 베이스 db1 이 선택된 상태에서) db1 안의 모든 테이블을 보여주겠습니다.

desc tb1C;

테이블의 tb1C의 칼럼 구조를 표시하겠습니다.

테이블 tb1C 의 칼럼 구조를 변경해 보겠습니다. 10자 까지만 입력할 수 있는 칼럼 name을 100자 까지 입력할 수 있도록 변경 합니다.

alter table tb1C modify name varchar(100);

테이블 tb1C 의 칼럼 name 의 자료형을 글자를 100까지 입력할수 있는 자료형으로 변경합니다.

desc tb1C;

변경된 후의 tb1C의 칼럼 구조를 살펴 봅니다.

칼럼의 추가하기

alter table 테이블_이름 add 칼럼_이름 자료형;

칼럼을 추가하기 전에 테이블 tb1c 의 칼럼 구조를 확인해 봅시다.

desc 테이블_이름;

하게되면 해당 테이블의 칼럼 구조를 확인할 수 있습니다.

age 맨 뒤에 칼럼 이름인 birth 를 만들고 날짜와 시간을 넣을 수 있는 자료형 DATETIME 을 추가해 보도록 합니다.

alter table tb1C add birth datetime;

테이블 tb1C 에 칼럼을 추가하는데 맨 뒤에 자료형이 datetime인 birth 라는 이름의 칼럼을 추가하겠습니다.

desc tb1C;

테이블 tb1C의 칼럼 구조를 표시하겠습니다.

이렇게 생성한 칼럼 birth 에 사원정보 레코드를 추가해 봅시다.

구체적으로

테이블 tb1C 에 number가 'N111', name이 '손연재', age가 33, birth가 '1980-11-10' 인 레코드를 추가하겠습니다.

insert into tb1C value ('N111', '손연재', 33, '1980-11-10');

테이블 tb1C 에 number 'N111', name '손연재', age 33, birth 1980-11-10 인 레코드를 추가합니다.

select * from tb1C;

그리고 테이블 tb1C 안의 모든 레코드를 표시합니다.

※ 테이블 tb1E 에는 칼러의 위치를 자유롭게 지정해서 칼럼을 만들어 보도록 합시다.

추가할 칼럼의 위치를 자유롭게 지정하기

alter table 테이블_명 add 칼럼_이름 자료형 after 뒤에넣을_칼럼_이름;

먼저 테이블 tb1E 를 만들어 봅시다.

create table tb1E select * from tb1;

새로운 테이블 tb1E 를 만드는데 tb1 안의 모든 레코드를 가져오겠습니다.

desc tb1E;

테이블 tb1E 의 칼럼 구조를 표시하겠습니다.

alter table tb1E add birth datetime after number;

테이블 tb1E 에 칼럼이름이 birth 를 만들고 자료형으론 datetime 를 설정합니다. 하지만 이 칼럼의 위치는 number의 뒤쪽으로 넣어주겠습니다.

desc tb1E;

그리고 나서 테이블 tb1E 의 칼럼 구조를 확인해 봅니다.

칼럼의 순서 변경하기

그러면, 칼럼 birth가 추가된 사원정보 테이블 tb1C 의 칼럼 순서를 변경해 보겠습니다.

구체적으로는 테이블 tb1C의 칼럼 birth 의 위치를 맨 앞으로 변경해 보겠습니다. 또한, 변경하고나서 테이블의 칼럼 구조를 표시해 보겠습니다.

alter table tb1C modify birth datetime first;

테이블 tb1C 의 칼럼중 birth, 자료형 datetime 인 칼럼을 선택해 칼럼들 중에서 제일 앞으로 보내겠습니다.

desc tb1C;

테이블 tb1C의 칼럼을 구조를 표시하겠습니다.

칼럼의 이름과 자료형 변경하기

위쪽에 추가한 테이블 tb1C의 칼럼 birth는 생년월일을 입력하는 칼럼인데 시간 데이터인 00:00:00 까지 입력되었습니다. 역시, DATETIME형이 아닌 날짜만 입력할 수 있는 DATE형으로 설정하는 것이 좋겠습니다.

그래서 이번에는 칼럼 birth의 자료형을 DATE형으로 변경하고, 이름도 birthdate로 변경해 보겠습니다.

칼럼 이름을 포함한 자료형과 위치 변경하기

alter table 테이블_이름 change 변경_전_칼럼_이름 변경_후_칼럼_이름 변경_후_자료형;

alert table tb1C change birth birthdate date;

테이블 tb1C의 칼럼중 birth 를 선택하여 birthdate 라는 이름의 칼럼명으로 정정해줍니다. 자료형은 시분초를 안쓰기 때문에 date 로 바꿔줍니다.

desc tb1C;

테이블 tb1C의 칼럼을 구조를 표시하겠습니다.

칼럼 삭제하기

마지막으로 '생년월일'을 입력하는 칼럼을 삭제해 보겠습니다.

칼럼 삭제하기

alter table 테이블_이름 drop 삭제할_칼럼_이름;

alert table tb1C drop birthdate;

테이블 tb1C 에 birthdate이름의 칼럼을 삭제 합니다.

desc tb1C;

테이블 tb1C의 칼럼을 구조를 표시하겠습니다.

※ 이처럼 칼럼을 삭제하면 해당 열에 저장되어 있던 데이터도 당연히 삭제됩니다. 이 예에서는 생년월일을 입력한 데이터가 삭제되었습니다. 단, 다른 칼럼에는 아무런 영향이 없습니다.

2. 기본 키란 무엇인가?

- 유일이란?

데이터베이스로 생성한 대량의 레코드 중에서 '조건에 맞는 1개의 특정 레코드'를 구별할 수 있어야 합니다. 예를들어, 사원 이름에 사원 ID 를 중복되지 않도록 부여한다거나 1개의 상품코드에는 1개의 가격만 설정해야 합니다.

- 기본 키란?

유일한 레코드를 만들려면 다른 칼럼과 구별하고자 특별한 속성을 설정해야 합니다.

이럴 때 사용하는것이 기본 키(PRIMARY KEY)입니다. 기본 키는 수많은 레코드 중 하나를 특정하기 위해 사용하는 식별자입니다.

기본 키는 다음과 같은 특징을 갖는 칼럼입니다.

■ 값이 중복되지 않는다.

■ 반드시 데이터를 입력해야 한다 (NULL을 입력할 수 없다.)

다음은 테이블을 만들 때 기본 키를 설정하는 방법 입니다.

테이블을 만들 때 기본 키도 함께 설정하기

create table 테이블_이름 (칼럼_이름 자료형 primary key...);

기본 키 생성하기

다음 명령을 실행하면 기본 키로 설정한 INT형의 칼럼 a와 VARCHAR(10)형의 칼럼 b로 구성된 테이블 eigen이 생성됩니다.

create table eigen (a int primary key, b varchar(10));

Key 항목에 PRI 라고 표시된 것이 기본 키(PRIMARY KEY)입니다. 또한,

칼럼 a의 Null은 NO라고 표시가 되어있습니다. 이는 NULL은 입력할 수 없다는 것입니다. 즉. '반드시 값을 입력해야한다.' 라는 의미입니다.

그럼 실험을 해보겠습니다. 정말 Null 값은 들어갈 수 없는지 확인을 해보면 되니깐요.

insert into eigen value (1, '아');

테이블 이름이 eigen 인 테이블에 칼럼 a 의 값으론 1을 넣어주고 칼럼 b 의 값으론 '아' 를 추가시켜 주겠습니다. 이제, 칼럼 a에는 중복되는 값은 1이나 아무것도 없다는 의미인 NULL을 입력할 수 없습니다.

확인을 위해 칼럼 a 에 값 1 과 NULL 을 넣어보겠습니다.

insert into eigen (a) value (1);

테이블이름이 eigen 인 테이블에 칼럼 a 의 자료형으로 1을 입력하였습니다.

-기본값이 설정되어 있어 에러코드를 띄웁니다.

insert into eigen (a) value (NULL);

테이블이름이 eigen 인 테이블에 칼럼 a 의 자료형으로 NULL을 입력하였습니다.

-없는 값을 넣으면 안된다고 에러코드를 띄웁니다.

그럼 1과 null 값을 제외한 다른 숫자를 넣어보겠습니다.

스크린샷에 보이는 대로 칼럼 a 에 1과 NULL 값을 제외한 다른 값이 정확히 들어간걸 확인할 수 있습니다.

기본키로 설정한 칼럼에는 insert나 update명령으로 이미 존재하는 값을 입력할 수 없습니다. 그렇기 때문에, 기본 키로 설정하면 유일한 레코드를 만들 수 있습니다.

또한, '중복되지 않는다'라고 제한할 수 있는 고유 키(또는 유니크 키, UNIQUE KEY)를 설정할 수도 있습니다. 다음 명령을 실행하면 고유 키인 INT형의 칼럼 a와 VARCHAR(10)형의 칼럼 b로 구성된 테이블 uniq가 생성됩니다.

create table uniq (a int unique, b varchar(10));

Key 항목에는 UNI 라는 고유값이 들어가 있고

Key 항목 왼쪽의 Null 항목에 YES 라고 되있는걸 확인하실 수 있습니다.

'값을 입력하지 않아도 되지만, 중복은 허용하지 않는 칼럼이 설정 되었습니다.

이렇게 NULL 값이 들어가 있는걸 확인 할 수 있구요.

이렇게 중복은 허용하지 않기에 에러도 띄우는걸 확인 할 수 있습니다.

3. 연속 번호 기능이 있는 칼럼 설정하기

이번에는 1부터 1씩 증가하는 숫자가 자동으로 입력되는 칼럼을 설정해 보겠습니다.

예를 들어, 명단이나 목록의 번호를 저장하는 칼럼에 매번 숫자를 입력하려면 번거롭기도 하고 입력 오류가 발생할 수 도 있습니다. 이럴 때에는 1, 2, 3, 4...처럼 연속 번호를 자동으로 입력할 수 있으면 편리 합니다.

연속 번호 기능이 있는 칼럼 정의하기

연속 번호 기능이 있는 칼럼을 만들려면 다음과 같은 설정이 필요합니다.

1 : 자료형은 INT 등의 정수형

연속 번호 기능이 있는 칼럼은 INT형이나 TINYINT형, SMALLINT형 등의 정수형 이어야 합니다.

자료형	의미	대응하는 범위
INT	오른쪽 범위 안에 있는 정수	-214783648 ~ 2147483647
TINYINT	매우 작은 정수	-128 ~ 127
SMALLINT	작은 정수	-32768 ~ 32767

2 : AUTO_INCREMENT 선언

INT형에 AUTO_INCREMENT라는 연속 번호를 설정하는 키워드를 선언합니다.

3 : PRIMARY KEY 등으로 설정해서 유일하게 만들기

연속 번호 기능이 있는 칼럼에는 PRIMARY KEY로 설정해서 기본 키로 하는 등 유일해야 합니다.

CREATE TABLE 명령으로 테이블을 만들 때에는 (a INT, b VARCHAR...)라고 기술하는데, 이때 AUTO_INCREMENT와 PRIMARY KEY 는 INT형 뒤에 기술합니다.

또한, AUTO_INCREMENT를 설정한 칼럼은 당연히 중복되면 안 됩니다.(유일한 상태)

이렇게 중복되지 않는 값이 자동으로 입력되는 칼럼은 기본 키로 설정하기에 매우 적합하다고 할 수 있습니다.

연속 번호 기능이 있는 연습용 테이블 series를 만들어 보겠습니다.

연속 번호 기능이 있는 칼럼 a와 VARCHAR(10)형의 칼럼 b로 구성된 테이블 series를 만들어 보겠습니다. 그리고 생성한 테이블의 구조를 확인해 보겠습니다.

create table series (a int auto_increment primary key, b varchar(10));

테이블 series 를 만들며 칼럼 a 엔 숫자형자료형을 입력하지만 이 칼럼에 들어가는 값은 연속된 번호값를 가지며 또한 유일합니다. 그리고 칼럼 b 에는 문자형 데이터를 입력할 수 있고, 10자 까지 입력이 가능합니다.

DESC로 표시된 Key 항목 중에 PRI라고 표시된 부분이 기본 키로 설정한 PRIMARY KEY입니다. 또한, Extra 항목에 auto_increment라고 표시되어 있는데 이는 연속 번호 기능을 나타냅니다.

MySQL 이 아닌 다른 RDBMS에서 연속 번호 기능 설정은 다릅니다.

그러면 연습용 테이블 series에 데이터를 입력하고 나서, 연속 번호가 입력이 되는지 확인해 보겠습니다. 칼럼 a는 연속 번호가 자동으로 입력되기 때문에 이번에는 칼럼 b에만 데이터를 입력하겠습니다.

연속 번호 기능이 설정된 칼럼에 연속 번호가 자동으로 입력되게 하려면, 0을 입력하거나 아무것도 입력하지 않으면(NULL 을 입력) 됩니다.

테이블 series의 칼럼 b 에 '자', '축', '인'을 각각 추가합니다.

여기에서는 자, 축, 인 을 입력했지만, 연속 번호가 입력되는 것만 확인하는 것이라면 어떤 값을 입력해도 상관 없으니, 계속해서 연습해 보도록 합니다. 같은 명령을 반복할 때에는 ↑ 키를 활용하면 편리합니다.

※ 한글을 입력했을때 깨지거나 입력했던 데이터가 빈 공백으로 나올경우엔

설정을 한번 확인해 보시기 바랍니다. 전의 게시글 참조...;;

저와 셋팅이 동일하다면

cmd 를 키고나서

set character set euckr;

한번에 windows cmd 의 한글 문제는 해결이 됩니다.

연속 번호 기능이 있는 칼럼에는 값을 자유롭게 설정할 수 있습니다.

예를 들어, INSERT INTO series VALUES (100, '묘'); 라고 하면, a에는 100이 입력되고 그 뒤에 오는 레코드에는 101이라는 연속 번호가 입력됩니다. 즉, 이미 입력된 최댓값 +1부터 입력됩니다. 단, PRIMARY KEY(기본키) 속성이 설정되어 있기 때문에 이미 존재하는 값을 중복해서 입력할 수는 없습니다.

그러면 이 테이블에 있는 모든 레코드를 삭제하고 나서, 다시 레코드를 입력한다면 연속 번호는 어떻게 될까요? 예를 들어, DELETE FROM series 명령을 실행하면 모든 레코드가 삭제됩니다. 계속해서 INSERT INTO series (b) VALUES ('xx')를 실행해 보겠습니다.

delete from series;

테이블 series 의 모든 레코드를 삭제 합니다.

insert into series (b) values ('xx');

테이블 series 의 칼럼 b에 문자형 자료형 'xx' 를 넣어봅니다.

그러면 번호가 1부터 부여되지 않고 이제까지 있던 숫자의 최댓값 +1부터 입력됩니다.

만약, 모든 레코드를 삭제하고 연속 번호를 입력(초기화)하려면, 다음과 같이 AUTO_INCREMENT의 값을 초기화 해야 합니다.

AUTO_INCREMENT의 값 초기화 하기

ALTER TABLE 테이블_이름 AUTO_INCREMENT=0;

또한, 이미 존재하는 값보다 큰 값으로 설정할 때에도 이 명령으로 번호를 초기화할 수 있습니다.

그리고 연속 번호 기능이 있는 칼럼에 0을 입력하려고 해도 0은 입력되지 않습니다. 예를들어, INSERT INTO series VALUES (0,'진'); 을 실행하면 규칙대로 연속 번호가 입력될 뿐입니다. 따라서 '연속 번호 기능이 있는 칼럼에는 항상 0을 입력하다.' 라고 기억하면 실수할 일도 줄어들 것입니다.

4. 칼럼의 초깃값 설정하기

다음은 아무것도 입력하지 않으면 사전에 설정한 데이터가 자동으로 입력되는 칼럼, 즉 칼럼의 초깃값(기본값)을 설정하는 방법입니다. 이때에는 칼럼에 DEFAULT 키워드를 설정합니다.

칼럼의 초깃값 설정하기

CREATE TABLE 테이블_이름 (칼럼_이름 자료형 DEFAULT 초깃값...);

사원정보 테이블 tb1과 같은 구조의 테이블 tb1G에 칼럼의 초깃값을 설정해 보겠습니다.

이름 칼럼(name)에 아무 값도 입력하지 않으면, '이름 미입력'이 입력되는 기능을 추가합니다. 그리고 정의를 변경하고 나서 칼럼을 표시해 보겠습니다.

create table tb1G select * from tb1;

새로운 테이블 tb1G 를 생성하려하는데 tb1 의 모든 레코드를 그대로 복사 하겠습니다.

desc tb1G;

테이블 tb1G 의 칼럼 정보를 표시합니다.

칼럼 정보를 다시한번 확인해 보는 이유는 저기 name 에 값이 입력이 되지 않을 경우 '이름 미입력'이 출력이 되는 형태로 만들려고 합니다.

alter table tb1G modify name varchar(10) default '이름 미입력';

테이블 tb1G 의 칼럼 name 자료형을 문자를10자 까지만 입력할수 있고 값을 안넣으면 기본값으로 '이름 미입력'이 출력되게 변경 합니다.

칼럼 구조를 변경한 상태에서 number와 age 칼럼에만 데이터를 입력하고, name 칼럼에는 아무것도 입력하지 않으면 어떻게 되는지 알아보겠습니다.

사원번호 number에는 'N999', 나이 age에는 38을 입력해 보겠습니다.

insert into tb1G (number, age) values ('N999', 38);

테이블 tb1G 의 number와 age 칼럼에 데이터 'N999'(number), 38(age) 를 집어 넣습니다.

칼럼 name에는 default값 '이름 미입력'이 입력되었습니다. 물론, 이 초깃값은 아무것도 입력되지 않았을 때 설정되는 값이며, 칼럼 name에는 자유롭게 데이터를 입력할 수 있습니다. 예를 들어, 칼럼 name에 '회복맨'이라고 입력하면, '회복맨'이라고 저장됩니다.

5. 색인의 역할과 설정 방법

색인이란?

테이블에 있는 데이터를 검색할 때, 데이터의 양이 많을수록 모든 레코드를 검색하는데 시간이 오래 걸립니다. 이럴 때, 테이블의 '색인'을 만들면 편리합니다.

테이블에 설정하는 색인을 인덱스(INDEX)라고도 합니다. 사전에 테이블에 색인을 설정하고, 검색할 때에는 테이블이 아닌 색인을 이용합니다. 이렇게 하면 검색 시간을 단축할 수가 있습니다.

이전까지의 예제에서 처럼 10개의 레코드 정도로 구성된 테이블을 다룰 때에는 색인의 유무가 아무런 의미가 없다고 느낄 수 있습니다. 그러나 기업에서 실제로 운용하는 규모의 큰 테이블에서는 색인이 처리 속도에 큰 영향을 미칩니다.

또한, 기본 키를 설정한 경우에는 색인이 자동으로 설정됩니다.

색인은 다음과 같이 설정합니다.

색인 설정하기

CREATE INDEX 색인_이름 ON 테이블_이름(칼럼_이름);

테이블 tb1G의 칼럼 number에 my_ind라는 이름의 색인을 설정 해봅니다.

create index my_ind on tb1G(number);

새로운 색인 my_ind 를 만드는데 이 색인을 테이블명 tb1G의 칼럼(number)에 할당합니다.

이렇게 만들어진 색인을 표시해봅시다.

색인 확인하기

SHOW INDEX FROM 테이블_이름;

show index from tb1G;

테이블 tb1G의 색인을 확인합니다.

이대로는 보기가 불편하기 때문에 명려운의 마지막에 쌍반점(;) 대신 \G(G는 대문자)를 입력해서 실행 결과를 보기 편하게 표시해 보겠습니다.

show index from tb1G \G

테이블 tb1G 의 색인 정보를 표시합니다.

이렇게 등록했던 색인도 삭제 할 수 있습니다.

다음 명령을 실행하면 설정한 색인이 삭제됩니다.

색인 삭제하기

DROP INDEX 색인_이름 ON 테이블_이름;

테이블 tb1G에 설정한 색인 my_ind를 삭제해 봅시다.

drop index my_ind on tb1G;

색인 이름이 my_ind 를 삭제하는데 이 색인을 삭제할 테이블은 tb1G 입니다.

show index from tb1G \G

테이블 tb1G의 색인 정보를 표시해 주세요.

색인의 처리 속도의 관계

실제로 색인을 설정하면 반드시 검색 시간이 단축된다고 단정 지을 수는 없습니다. 검색 조건에 따라서는 색인을 사용하지 않는 때도 있으며, 오히려 색인을 사용하면 검색 속도가 늦어지는 때도 있습니다.

예를 들어, 같은 값(중복 값)이 많을 때에는 색인을 사용하지 않는 것이 좋다라는 말을 많이 합니다. 극단적인 예로, 한 칼럼의 값이 모두 'yes'또는 'no'로만 되어 있는 경우, 이 칼럼에는 색인을 설정해도 검색 속도가 향상되지 않습니다.

또한, 색인이 설정된 테이블에서 데이터를 변경하면 이전에 작성했던 색인은 다시 작성됩니다. 따라서, 색인을 이용하면 검색 속도는 향상시킬 수 있지만, 데이터의 변경 속도는 오히려 늦어질 수가 있습니다.

그리고 색인을 잘못 설정하게 되면, 검색 결과에는 아무런 영향을 미치지 않지만, 데이터베이스 전체의 처리 속도에는 영향을 미칠 수 있습니다.

색인 설정은 데이터베이스 전체의 처리 효율에 영향을 미치는 중요한 문제입니다. 이런 처리효율에 대하 대책을 '튜닝'이라고 하는데, 얼마만큼 효율적으로 색인을 설정하는가 하는 문제는 데이터베이스 관리자의 실력을 보여줄 좋은 기회입니다.

체크!

★ ALTER TABLE... 명령을 사용해서 칼럼 구조를 변경하거나 추가, 삭제할 수 있다.

★ 칼럼에 연속 번호 기능을 설정할 수 있다. 연속 번호를 입력할 수 있다.

★ AUTO_INCREMENT=..을 사용해서 연속 번호의 초깃값을 설정할 수 있다.

★ DEFAULT를 사용해서 칼럼의 초깃값을 설정할 수 있다.

★ 색인의 내용을 표시하고, 삭제할 수 있다.