[MySQL] alter table 명령어

alter table 명령어는 모르시는 분이 없듯이 테이블의 스키마를 변경 할 수 있게 끔 해주는 아주 유용한 명령어입니다.
alter table 명령어가 없으면 아주 끔찍한 일이 벌어질 수도 있습니다. create table...과 drop table의 남발!! 정말 끔찍하지 않습니까? 그런데 테이블의 스키마가 무엇이냐구요? 

이것 역시 모르시는 분이 단 한사람도 없겠지만 쉽게 말해서 테이블의 구조라고 이해하시면 됩니다. 관계형 데이타베이스를 이용할려면 이놈들을 속성(attribute)들로 뭉쳐진 의미있는 정보의 단위로 이끌어 내야 하는데 이러한 것을 엔티티(Entity)라고 합니다. 정확히 말하면 이 엔티티의 구조를 설명한 게 스키마입니다! 이 스키마는 논리적 스키마와 물리적 스키마로 분리해서 부르는데 특정회사의 데이타베이스에 맞게 스키마를 표현하게 되면 물리적 스키마라고 부릅니다.
엔티티가 물리적 스키마에 의해서 실제 데이타베이스에 적용된 것이 테이블입니다. 더 자세한 사항은 시중에 나와 있는 데이타베이스 설계 관련 서적을 참고하세요! 없는 실력으로 더 자세한 설명은 도저히 무리입니다.^^

alter table 명령어는 각 회사의 데이타베이스에 따라서 약간씩 차이가 있습니다. 여기서는 제가 맨날 까먹는 alter table 명령어 중 MySQL 용 몇개를 오로지 안까먹기 위해서 적어둘려고 합니다. 사실 팁은 아닙니다.^^

일단 alter table 명령어를 쓰기 위해서 간단한 테이블을 하나 만들겠습니다. 테이블 이름은 mytable이고 컬럼은 id와 name만 가지고 있는 아주 간단한 예제용 테이블입니다.
 

아주 simple 하고 좋은데 id와 name외에 주소를 뜻하는 addr 컬럼을 추가해 봐야 하겠습니다.

[테이블에 새로운 컬럼 추가하기]

형식) alter table [테이블명] add column [추가할 컬럼명] [추가할 컬럼 데이타형]
 

뒤에 not null 을 안 붙이면 null 허용으로 컬럼이 추가됩니다. 위의 명령어에 의해서 addr 이라는 컬럼이 mytable에 추가 되었습니다. 그런데 주소를 뜻하는 addr 컬럼의 자릿수가 웬지 부족해 보입니다. varchar(100)으로 늘려봐야 겠습니다.

[테이블의 컬럼 타입 변경하기]

형식) alter table [테이블명] modify column [변경할 컬럼명] [변경할 컬럼 타입]
 

desc mytable로 확인해 보면 addr 컬럼이 varchar(100)으로 정확하게 변경되었습니다.주소 컬럼에는 맞지 않겠지만 addr 컬럼을 int 형에 null 허용으로 변경해 보고 싶습니다.
 

desc mytable로 확인 해보니 int 형에 null 허용으로 변경되었습니다. 주소가 int 형이라니 말이 안됩니다. int 형이라면 아무래도 나이가 맞을 것 같습니다. addr의 컬럼명 자체를 age 로 바꿔야 겠습니다. 

[테이블의 컬럼 이름 변경하기] 

형식) alter table [테이블명] change column [기존 컬럼명] [변경할 컬럼명] [변경할 컬럼 타입]
 

addr 컬럼명이 age 라는 컬럼명으로 정확히 바뀌었습니다. 그치만 애초에 age 컬럼은 필요가 없었습니다. 테이블에서 age 컬럼을 삭제 해야 하겠습니다.

[테이블 컬럼 삭제하기]

형식) alter table [테이블명] drop column [삭제할 컬럼명]
 

desc mytable로 확인해보니 age 컬럼이 삭제되어져 있습니다. 뭔가 허전합니다. id 컬럼에 인덱스(Index)를 주면 허전함이 달래질 것 같습니다.

[테이블 컬럼에 인덱스 주기]

형식) alter table [테이블명] add index 인덱스명( 인덱스를 줄 컬럼1, 인덱스를 줄 컬럼2,...)
 

위에서는 myindex 라는 인덱스명으로 id 컬럼에 index 가 추가되었습니다. 인덱스를 줄 컬럼을 한개 이상 써 넣으면 복수개의 컬럼에 대해서도 인덱스를 생성할 수 있습니다. 이번에는 id에 주어진 index 를 삭제해 보겠습니다.

[테이블 컬럼에 인덱스 삭제하기]

형식 ) alter table [테이블명] drop index 인덱스명
 

위에서는 처음에 줬던 index 인 myindex 를 삭제하고 있습니다. 참고로 인덱스를 확인하기 위해서는 아래의 명령어를 쓰시면 됩니다. mytable은 테이블 명입니다.
 

alter table로 인덱스도 추가하고, 날려봤는데 primary key 는 못 만들까요? 당연히 만들수 있습니다. 그대신 primary key 는 조건이 있습니다. primary key를 만들려는 컬럼에 조건이겠지요!!! 레코드에 값이 추가 안 되어있을 때는 괜찮지만, 값이 들어있다면 column 에 null 값이 들어 있는지, 중복된 값이 존재하는 column 인지를 따져봐야 합니다. null 값이 없고, 중복되는 column 이 없다면 primary key를 만들 수 있습니다.

[테이블에 primay key 만들기]

형식 ) alter table [테이블명] add primary key ( 키를 줄 column명1, 키를 줄 column명2, ... );
 

위에서는 mytable의 id 컬럼에 primary key 를 만들고 있습니다. primary key 는 composite key 가 가능하므로 여러개의 column을 묶어서 primary key 로 사용할 수도 있습니다.

만들어진 primary key를 지워야 할 때도 있습니다.

[테이블에 primay key 삭제하기]

형식) alter table [테이블명] drop primary key;
 

위에서는 mytable에 만들었던 primary key를 삭제하고 있습니다. 참고로 primary를 만들때는 index가 자동으로 primary key 컬럼에 추가됩니다.

primary key도 다시 날려버리고 아주 좋습니다. 마지막으로 테이블명을 변경해 보고 싶습니다. 사실 mytable이라는 테이블명이 아주 마음에 안 들었습니다.

[테이블 명 바꾸기]

형식) alter table [원본 테이블명] rename [새로운 테이블명];

alter table에 대해서 대충 알아 봤습니다. 그 유명한 다른 primary key를 뽀려온 foreign key 에 대한 부분도 정리 했으면 좋겠는데.. 오늘은 제 시간 관계 상 여기서 정리를 끝내겠습니다.

Mysql을 처음 설치할 때 database root 계정으로 사용할 password를 설정한다. 하지만 시간이 오래 지나서 그때 설정한 password를 기억할 수 없다면 다음의 방법으로 재설정할 수 있다. (Ubuntu 12.04 기준)

Step 1. 실행중인 mysql service를 중지 시킨다.

Step 2. Password를 검사하지 않도록 mysql 환경설정 파일을 수정한다.
: /etc/mysql/my.conf file에 skip-grant-tables를 추가하면 password를 검사하지 않는다.

Step 3. 새로운 설정 값으로 mysql service를 실행한다.

Step 4. root 계정으로 mysql database를 연다.

Step 5. root password를 재설정한다.

Step 6. my.conf를 복원하고 mysql service를 재실행 시킨다. 

오늘은 mysql 설치시 사용자를 추가하는 방법에 대해 남겨볼까 한다. 할때마다 잘 기억이 안나서 검색엔진에 항상 의존을 하게 되는데 다음에는 검색엔진에 의존하지 않고 바로 블로그에서 검색해서 보면 좋을 듯 하다. 

사용자를 생성하는 방법은 여러가지가 있는데 아래 방법이 가장 쉽고 심플하다. 다른 방법들은 쿼리문을 직접 던져야 하기 때문에 솔직히 말해서 좀 불편하다. 혹시나 오랜만에 해 보거나 처음해 보는 사람들을 위해서 서버에 mysql을 설치한 후 mysql에 로그인한 후 아래 명령를 실행시켜준다.

-  mysql 서버 로그인하기

- 다른 PC에서 mysql 서버로 접속을 하기 위한 사용자 추가

- 위 사용자에게 모든 것을 할 수 있는 권한 주기

- 위 사용자에게 특정 DB를 관리할수 있는 권한 주기

- 로컬PC에서 mysql로 접속하기 위한 사용자 추가

- 위 사용자에게 모든 것을 할 수 있는 권한 주기

- 위 사용자에게 특정 DB를 관리할 수 있는 권한 주기

대부분 사용자를 생성하고 권한을 줄때는 특정 DB를 관리하는 계정을 따로 만들기 위해서이다. 그러므로 사용자를 생성하고 권한을 줄때는 특정 DB를 관리할 수 있는 권한만 주면 된다.

MySQL에서 사용자의 로그인 보안 수준을 높이기 위해서,
MySQL 4.0.x 이하 버전과 MySQL 4.1.x 이상 버전의 PASSWORD() 함수의 구현 알고리즘이 달라졌다.
사실 아주 오래전 이야기이지만, 아직도 MySQL 4.0.x를 사용하는 사이트가 많고,
최근 들어서 MySQL 5.1이나 5.5 버전으로 업그레이드를 준비하면서 이런 내용이
업그레이드에 걸림돌이 되는 경우가 많은 것으로 보인다.



MySQL 4.0.x 이하 버전
  - PASSWORD()
  - OLD_PASSWORD() 함수는 없음


  mysql> SELECT PASSWORD('mypass');
  +--------------------+
  | PASSWORD('mypass') |
  +--------------------+
  | 6f8c114b58f2ce9e   |
  +--------------------+


MySQL 4.1.x 이상 버전
  - PASSWORD()         ==> 버전업된 암호화 함수
  - OLD_PASSWORD()     ==> 기존 암호화 (MySQL 4.0.x의 PASSWORD()와 동일)


  mysql>select PASSWORD('mypass');
  +-------------------------------------------+
  | password('mypass')                        |
  +-------------------------------------------+
  | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
  +-------------------------------------------+
  
  mysql>select OLD_PASSWORD('mypass');
  +------------------------+
  | old_password('mypass') |
  +------------------------+
  | 6f8c114b58f2ce9e       |
  +------------------------+


위 결과를 보면, 새로운 암호화 알고리즘에 의해서 암호화된 내용은 암호 제일 앞에 "*" 마크가 붙게 되므로, 새로운 암호인지 예전 암호인지 쉽게 구분할 수 있다.


그런데, old_passwords=1로 설정하게 되면, MySQL 4.1.x 이상 버전에서도 아래와 같이 MySQL 4.0.x와 같은 결과를 보여주게 된다.
MySQL 4.1.x 이상 버전에서도, 이전 버전과 동일하게 짧은 암호화 문장으로 변환됨
  root@localhost:(none)>select PASSWORD('mypass');
  +--------------------+
  | PASSWORD('mypass') |
  +--------------------+
  | 6f8c114b58f2ce9e   |
  +--------------------+
  
  root@localhost:(none)>select OLD_PASSWORD('mypass');
  +------------------------+
  | OLD_PASSWORD('mypass') |
  +------------------------+
  | 6f8c114b58f2ce9e       |
  +------------------------+


MySQL 4.1.x 이상 버전에서 old_passwords=0 (기본 설정값)인 경우에는, mysql.user 테이블의 비밀번호가 
예전 버전에서 생성된 짧은 비밀번호라면 로그인할 수 없게 된다.


하지만, MySQL 4.1.x 이상 버전에서도 mysql.user 테이블에 사용자의 비밀번호가 예전 버전인 경우 로그인할 수 있도록 
해주기 위해서 old_passwords 라는 옵션을 1로 설정할 수 있도록 해둔 것이다.


근본적인 원인은 MySQL이 업그레이드되면서 기존 비밀번호의 암호화 수준을 보완하여 더 보안 수준을 높이면서 
이런 문제가 야기되었다는 것이며, 가능하다면 업그레이드된 긴 암호를 사용하는 것이 좋아 보인다.


더 중요한 것은
MySQL의 PASSWORD() 함수는 MySQL의 사용자 자체의 계정 및 비밀번호를 관리하기 위한 함수이지
일반 서비스용 계정및 암호를 관리하는 용도로는 적합하지 않다는 것이다.


예를 들어서 MySQL 4.0.x 버전의 회원정보가 예전 방식의 PASSWORD() 함수로 암호화되어서 저장되어져 있다면,
MySQL 4.1.x 이상의 PASSWORD() 함수로는 로그인이 되지 않게 될 것이다. (이 경우에는 OLD_PASSWORD()를 사용해야만 로그인을 할수 있게 된다.)
하지만, 이 경우 예전 버전의 PASSWORD()함수의 암호화 내용을 새로운 버전의 PASSWORD() 함수로 대체할 수 없다.
(한번 암호화된 문장은 다시 풀어낼 수 없는 형태 - 비대칭형 암호화 - 이기 때문)
서비스용 계정이 MySQL의 사용자 계정 암호화 방식에 의존하게 되면 이런 문제가 야기될 수 있으므로 
필요시에는 MD5와 같은 알고리즘을 사용할 것을 추천한다.


또한, 이렇게 두개 버전의 암호화 방식이 존재하는 상태에서
서비스용 MySQL의 버전 업그레이드와 연관되어서 명확히 해결하지 않고 업그레이드를 진행하게 되면,
일부 사용자의 암호는 예전 버전이고, 또 일부 사용자는 새로운 암호화 버전을 사용하도록 되어버리면
걷잡을 수 없는 혼란에 빠져들고, 이를 처리하기 위해서 또 한번 쿼리와 IF ~ ELSE가 필요해질 수도 있다는 것이다.

7.3.3 MySQL 내장 함수 (MySQL전용 함수)

- IFNULL(A, B) : A가 null 이면 B를 A가 null이 아니면 A를 그대로 반환한다.

- ISNULL(A) : A가 null 이면 true(1) , null 이 아니면 false(0) 을 반환한다.

- NOW() : 현재시간 반환

- SYSDATE() : 현재시간 반환

** 일반적인 웹서비스에서 NOW()가 아닌 SYSDATE()를 꼭 사용해야 이유는 없다. 기능은 동일하지만 SYSDATE()는 '호출시점'에 따라 값이 결정되기 떄문에 인덱싱 등에 문제를 가진다.

    where from_data > SYSDATE() 는 실행때마다 SYSDATE값이 달라진다, 이건 상수가 아닌상태이다!!

- DATE_FOMRAT : Date Type -> String

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H시 %i분 %s초') AS cur_str;

- STR_TODATE : 문자열을 날자 타입으로 변경

  SELECT STR_TO_DATE( '2011-04-30', '%Y-%m-%d') as cur_dt;

- DATE_ADD, DATE_SUB : 날자 타입의 가감

  SELECT DATE_ADD( NOW(), INTERVAL 1 DAY ) as tomorrow;

  SELECT DATE_ADD( NOW(), INTERVAL -1 DAY ) as yesterday;

** YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 의 값이 있고 [ INTERVAL n 단위 ] 형식으로 입력하면 된다.

- RPAD(),  LPAD() : 문자의 좌우측에 문자를 덧붙여 지정된 길이로 만든다.

  SELECT RPAD("Cloee', 10, '_'); -> Cloee_____

- RTRIM(), LTRIM(), TRIM() : 문자의 우측, 좌측, 양측의 공백(Space, NewLine, Tab)을 제거

- CONCAT : 문자열을 연결, 인자의 갯수는 제한이 없다.

- CONCAT : 문자열을 구분자를 넣어서 연결해준다.

  SELECT CONCAT_WS(',', 'A', 'B', 'C') --> A,B,C

- GROUP_CONCAT :  값들을 정렬한후, 연결하거나 구분자설정, 중복제거등 유용하다.

  ex1-SELECT GROUP_CONCAT(dept_no) 

  ==> d001, d002, d003, d004 

  FROM dept (dept모든 레코드에서 dept_no 칼럼을 기본 구분자, 로 연결한 값을 반환)

  ex2- SELECT GROUP_CONCAT(dept_no, SEPARATOR '|') FROM dept

  ==> d001|d002|d003}d004 

  ex3- SELECT GROUP_CONCAT(dept_no, ORDER BY dept_name DESC) from dept

  ==> d007,d008,d004,d001

  dept_name 역순 정렬 -> dept_no 들의 연결값을 가져옴

  ex4- SELECT GROUP_CONCAT(DISTINCT dept_no ORDER BY dept_name DESC) from dept

  ==> d007,d008,d006,d004

  ex3과 동일하지만 중복된 dept_no가 있다면 제거하고 가져온다.

** 제한적인 메모리 버퍼를 사용하는데 TOAD나 SQLYog에서는 단순한 Warnning지만, JDBC로 연결할 때는 SQLException이 발생하므로, GROUP_CONCAT의 결과가 버퍼를 초과하지 않도록 주의해야한다.

- CASE WHEN (Function가 아닌 Syntax)

SELECT emp_no, frst_name 

CASE gender WHEN 'M TEHN 'Man' 

 WHEN 'F' THEN 'Woman' 

ELSE 'Unknown' 

END AS gender

FROM emp LIMIT 10

 ** CASE WHEN 절이 일치해야만 THEN 이하도 실행이 된다.   서브쿼리->CASE WHEN으로 성능 향상을 꾀할 수 있다.(P407)

- MD5, SHA : 비대칭형 암호화 알고리즘 (SHA - SHA-1알고리즘, 160bit 해시 값, MD5 - Message Digest알고리즘 128bit 해시 값)

 중복의 가능성이 매우 낮기 때문에, 길이가 긴 데이터를 줄여서 인덱싱하는 용도로도 사용한다.(url값 인덱싱)