쭈글이의 잡동사니: 10월 2010

2010년 10월 18일 월요일

MYSQL 기본 문법

9. MYSQL 기본 문법
Mysql은 대화식 db입니다. 주로 서버에 연결을 하고 질문을 실행하며 결과를 화면에 출력해주는 일을 합니다. 무엇보다 아파치와 php를 연동했을 때 그 진가를 발휘합니다. 여기서는 리눅스용 mysql을 위주로 설명할 것이며 다른 db의 함수도 비교하겠습니다.

9.1 Mysql을 시작하기 전에
사용 가능한 이름

mysql을 한글 지원으로 컴파일했다면 한글 이름을 줘도 되지만 이것은 사용하지 않는 편이 좋습니다.
그리고 이름엔 "." 문자를 사용할 수 없고 첫 글자는 영문으로 사용해야 합니다.
또, 최대 64자까지 지원되며, 별명은 최대 256자까지 지원됩니다.
이름의 구성은 영문,숫자, "_" , "$"로 이루어져야 합니다. Mysql을 유닉스나 리눅스
계열에서 사용한다면 그 운영체제의 환경을 따르기 때문에 대소문자를 구별해서 데이터베이스 이름, table
이름이 저장됩니다.
Win32계열의 경우는 대소문자를 구분하지 않지만 하나의 문에서 대소문자를 같이 사용할 수는 없습니다.
하지만 별명은 대소문자를 구분해 줍니다.

그림.9-1 필자의 mysql 작업 환경

9.2 주석의 사용

각 라인의 끝에 오는 "#"과 여러 라인을 사용할 수 있는 "/* */"가 있습니다.

9.3 컬럼 타입

mysql의 컬럼은 종류가 다양합니다. 그래서 보기 쉽게 아래 표를 만들어 보았습니다.

Type Option (B)-최대표시, (F)-소수점이하자릿수
---------------------------------------------------------------------------------------------------------------------
TINYINT 정수형(-128~127), (B), [UNSIGNED]-정수형(0~255)

SMALLINT 정수형(-32768~32767), (B), [UNSIGNED]-정수형(0~65535)

MEDIUMINT 정수형(-8388606~8388607), (B), [UNSIGNED]-정수형(0~16777215)

INT 정수형(-2147483648~2147483647), (B), [UNSIGNED]-정수형(0~4294967295)

INTEGER INT와 동일

BIGINT 정수형(-9223372036854775808~9223372036854775807), (B),
   [UNSIGNED]-정수형(0~18446744073709551615)

FLOAT(정밀도) 부동소수점실수, (정밀도)-"(4,8)", (4)-단정도
   부동소수점실수, (8)-배정도 부동소수점실수, 범위는 FLOAT, DOUBLE과 같습니다.

FLOAT(L, F) 단정도 부동소수점 실수, (B,F), (-3.402823466E+38 ~ 1.175494351E-38, 0,
   1.175494351E-38 ~ 3.402823466E+38)
DOUBLE 배정도 부동소수점 실수, (B,F), (-1.7976931348623157E+308 ~ -2.2250738585072014E-308,0,
   2.2250738585072014E-308 ~ 1.7976931348623157E+308)

DOUBLE PRECISION~REAL PRECISION(B,F), REAL(B,F) DOUBLE와 동일

DECIMAL 부동 소수점 실수 CHAR 형태로 동작, (B,F), F가 0이면 소수점이하는 저장되지 않습니다.
   범위는 DOUBLE와 같습니다.

NUMERIC ECIMAL과 동일

DATE 날짜형(1000-01-01 ~ 9999-12-31)
   기본타입 - YYYY-MM-DD

DATETIME 날짜와 시간형(1000-01-01 00:00:00 ~ 9999-12-31 23:59:59)
   기본타입 - YYYY-MM-DD HH:MM:SS

TIMESTAMP 타임스템프형(1970-01-01 ~ 2037년 임의 시간),
   (B) - (14,12,8,6) B 값이 없을 경우 INSERT, UPDATE시 동작된 시간으로 자동적으로 저장됩니다.
   기본형식 - YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD,YYMMDD

TIME 시간형(-838:59:59 ~ 838:59:59) 기본형식 - HH:MM:SS

YEAR 년도형(1901 ~ 2155, 0000)

CHAR 고정폭 문자열, (B) - (1~255) B 만큼 오른쪽으로 공백 채워 저장 출력 시 공백은 출력안됨,
   [BINARY] - 검색 시 대소문자 구분

VARCHAR 가변폭 문자열, (L) - (1 ~ 255) 문자열 공백이 제거된 후 저장,
   [BINARY] - 검색 시 대소문자 구분

TINYBLOB / TINYTEXT BOLB, TEXT형, 최대길이 255문자

BLOB / TEXT BOLB, TEXT형, 최대길이 65535문자

MEDIUMBLOB / MEDIUMTEXT BOLB, TEXT형, 최대길이 16777215문자

LONGBLOB / LONGTEXT BOLB, TEXT형, 최대길이 4294967295문자

ENUM 문자열 목록형, 최대 65535개, 저장된 문자열 목록 중에 오직 한가지만 얻을 수 있습니다.

SET 문자열 목록형, 최대 64개, 저장된 문자열 목록 중에 0, 1개 이상을 얻을 수 있습니다.
---------------------------------------------------------------------------------------------------------------------

9.4 접속 하기

./mysql -h 호스트명 -u 유저 -p ("mysql>" 프롬프트가 나타나고 쿼리를 실행하면 됩니다.)
디폴트 인스톨을 했다면 "/usr/local/mysql/bin" 디렉토리에서 접속 명령을 실행합니다.
물론 -p 옵션은 유저에 해당하는 비밀 번호입니다.
위 명령을 실행하고 엔터를 치면 passw 입력 란이 뜨고 거기에 passw를 입력하면 됩니다.

9.5 접속 끊기

"mysql>"에서 "quit" 또는 " ctrl + d "를 누르면 됩니다.

여기서 "quit" 명령은 " ; " 없이 실행합니다. ("quit" , " use")

그림. 9-2 mysql 접속하기

9.6 mysql 버전과 지금 날짜 알아보기

mysql> select version( ), current_date( );

그림. 9-3 mysql 버전과 지금 날짜 알아보기

위 그림에서 1 row는 하나의 쿼리 결과를 말하며 (0.03 sec)는 쿼리 시간을 말합니다.

그림. 9-4 now( ) 함수

위 그림에서 각 명령의 연결은 " , "로 하며 select 실행 때 명령의 마지막을 " ; "로

표시합니다. " ; " 이 없을 경우 위와 같이 명령을 계속 입력하게끔 합니다.

9.7 데이터베이스 만들기(database와 table 만들기)
그럼 이제 직접 database를 만들고 여러 가지 사용하는 방법을 알아보겠습니다. 먼저 만들기 전에 기본적으로 만들어진 database를 확인해 봅니다.

데이터베이스 보기

mysql>show databases ;

그림. 9-5 show databases

(해당 데이터베이스의 테이블을 확인하려면 "show tables;" 명령으로 확인 가능합니다.)
위 그림을 보면 현재 database 항목이 여러 개 나오는 것을 볼 수 있는데 디폴트 값은"mysql" 과 "test"
값만이 나올 것입니다. 사용자에 따라 다르게 나올 수도 있습니다.

"mysql" db - 사용자 접근권한 정보를 가지고 있습니다.
"test" db - 말 그대로 test를 해볼 수 있는 db 입니다.

(지금부터 하는 모든 명령은 root 계정으로 실행합니다. 뒷 부분에 계정의 설정과 권한 등을 설명 하겠습니다.)

database 선택해서 사용하기

mysql>use database명;

test를 선택하고 실행하면 test database를 선택하고 changed 되는 것을 볼 수 있습니다.

특정 database로 바로 접속하기

"./mysql -h 호스트명 -u 유저명 -p 데이터베이스명"

이제 database를 만들어 보겠습니다. 일단 만들기 전에 중요한 부분은 설계를 해봐야 한다는 것입니다.
이 데이터베이스를 어디에 사용할 것이며 어떤 항목들을 넣어야 하나 여러 가지를 시험한 후 작성하는게
안전합니다. 물론 만들고 난 후 수정할 수도 있습니다.
만들 database명은 "work"로 임의로 정했습니다. 이 데이터베이스의 역할은 어느 누가 맡은 일을
몇번 했나 알아보는 간단한 database입니다.

데이터베이스 만들기

mysql>create database work ;

그림. 9-6 데이터베이스 만들기

"show" 명령으로 확인하면 work가 만들어진 것을 볼 수 있습니다.

그림. 9-7 데이터베이스 확인

work 데이터베이스의 사용을 위해 use 명령을 실행하고 다음 테이블을 확인합니다.
물론 새로 만든 데이터베이스이기 때문에 테이블은 비었다고 나옵니다.
(자! 그럼 table를 만들어 보겠습니다. 다른 방법도 있지만 여기선 직접하는 방법을 택했습니다.
다른 방법은 txt 파일을 만들어 파일을 실행하는 방법입니다. table명은 "works"로 하겠습니다.)

테이블 만들기

mysql>create table 테이블명 ( 열이름 자료형, 열이름 자료형, ... ) ;

그림. 9-8 테이블 만들기

테이블을 만들 때의 자료형은 앞 부분의 표를 확인해 보기 바랍니다.
자료형의 형태는 alter table를 사용해서 바꾸어줄 수 있습니다.
그럼 지금까지 만든 테이블의 자료형과 열의 이름을 확인하는 방법을 알아보겠습니다.

테이블 자료형 알아 보기

mysql>describe 테이블명 ;

그림. 9-9 테이블 자료형 알아보기

(여기서 name은 이름, hab는 취미, own은 담당, sex는 성 , work는 마지막 작업일, no는 작업할 횟수로 표현했습니다.)

9.8 테이블에 데이터 입력하기
이제 테이블에 데이터를 입력할 차례입니다. 이것 또한 직접 하나씩 입력하는 방법과 파일로 한꺼번에 입력하는 방법이 있습니다.

테이블에 데이터 입력하기(직접)

mysql>insert into 테이블명 values(‘자료명’ , ‘ ..’ , ‘..’) ;
이것은 해당 테이블을 새롭게 생성했을 경우 처음 입력할 때 주로 사용 합니다.
자료의 입력 순서는 각 열에 해당하는 인자 순으로 나열하면 되고, 만약 해당 열에 자료를 넣지 않으려면
"null" 을 사용합니다.

테이블에 데이터 입력하기(파일)

mysql>load data local infile "파일명" into table 테이블명 ;

모든 자료 입력은 테이블 열의 이름순으로 하면 됩니다. 그리고 중요한 것은 열과 열 사이는 tab 키로 해야
하는 것입니다. 어떠한 에디터를 사용하건 확장자나 이름은 중요하지 않습니다. 또, 빈 공간이 없이 입력을
해야 합니다. 열의 항목을 비워 두려면 " /n "을 사용하면 됩니다.

그림. 9-10 works_table.sql

위 파일을 보면 공백이 없는 것이 확인 될 것입니다.
이 파일은 "works_table.sql"로 만들었고 2) 번의 방법으로 해당 테이블에 입력했습니다.

그림. 9-11 테이블에 데이터 입력하기(파일)

위 그림에서 해당 파일의 내용을 테이블에 입력하고 테이블 내용을 다시 확인했습니다.

9.9 테이블에서 정보 검색하기
select문을 사용해서 다양한 방법으로 검색을 할 수 있습니다.

기본 형식

mysql>select 검색명 from 테이블명 where 검색조건;

검색명은 여러 개 나열할 수도 있습니다. 가령 name,work, … 이런 식으로 ","를 사용합니다.
"*"를 사용해서 전부를 검색할 수도 있습니다.
그리고 검색조건은 사용하지 않아도 무관 합니다.
그럼 where의 여러 가지 형태를 보기로 하겠습니다.

where 검색 조건

mysql>select 검색명 from 테이블명 where 검색조건(and , or, like , regexp)

(다른 부분은 뒷 부분 주요함수 부분에서 다루겠습니다.)

where and 사용하기

mysql>select * from works where (no = "4" and sex = "f");

and일 경우 (둘 다 참이어야 합니다.)

그림. 9-12 where and 예 1

위 그림은 여자 중에 일을 4번 한 사람을 찾는 것입니다.

mysql>select * from works where (own = "청소" and hab = "잠자기");

그림. 9-13 where and 예 2

where ( own = "청소" and hab = "잠자기" ) 이것은 맡은 일이 청소이면서 취미가 잠자기인
사람을 찾는 것입니다. 여기까지는 검색명을 "*"로 사용했기 때문에 열 전부를 보여 주었습니다.
특정한 열만 보려면 그 열의 이름을 적어주면 됩니다.

where or 사용 하기

mysql>select * from works where (own = "청소" or hab = "농구");

or일 경우는 (둘 중 적어도 하나는 참이어야 합니다)

그림. 9-14 where or 예

그리고 위 방법 말고도 and와 or을 같이 사용하는 방법과 한 개 이상을 사용하는 방법 여러 가지가 있습니다.

where and, or 사용하기

mysql>select * from works where (sex = "m" and work >= "2000-01-20") or
   (sex= "m" and hab= "잠자기");

그림. 9-15 where and, or 예

그림을 보면 "where( 문장 and 문장 ) or ( 문장 and 문장 )" 사용한걸 볼 수 있습니다.

자, 그럼 이제 특정한 문자의 패턴 일치를 비교해서 검사하는 방법을 살펴보겠습니다.
두 가지 방법이 있는데 like와 regexp 입니다. 각각의 패턴을 검사하는 방식을 살펴보겠습니다.

where like 사용하기

mysql>select * from work where 열명 like "찾을문자%", "%찾을문자", "%찾을문자%", "____" ;

여기서 like 다음 나오는 형식은 차례로 찾을 문자로 시작하는 단어, 찾을 문자로 끝나는 단어, 찾을 문자를
중간에 포함한 단어, 4개로 이루어진 문자 식으로 해석하면 됩니다.
이 명령은 주로 게시판의 검색에 사용하면 편합니다. 뒤에 게시판 구현에서 잘 살펴 보기 바랍니다.

where regexp 사용하기

mysql>select * from work where 열명 regexp "^[jJ]" , "^.{5}" ;

regexp 형식을 보면 좀 생소한 기호가 있을 것입니다. 이 기호는 이 표현식(정규 표현식)에 사용하는 몇가지
기호입니다. 그리고, regexp 표현은 대소문자를 가리기 때문에 검색 조건을 입력할 때 주의를 요합니다.
위 형식은 차례로 문자 처음이 소문자 j 대문자 J로 시작하는 조건을 검색, 5개의 문자로 이루어진 단어
검색을 의미합니다.

Regexp 기호

   . - 문자 하나를 나타냅니다.
   * - 앞에 나온 문자의 0개 이상 반복합니다.
   ^ - 문자열의 처음을 나타냅니다.
   $ - 문자열의 끝을 나타냅니다.
   [,] - 괄호 안의 문자열 일치를 확인합니다.
   {,} - 반복을 나타냅니다.

그림. 9-16 regexp 예

위 그림은 works 테이블에서 필드가 name인 항목 중 알파벳 b로 시작하는 사람을 찾아 줍니다.

order by 사용하기

mysql>select 열명 from 테이블명 order by 열명;

order by는 인자 순으로 열명에 해당하는 자료를 정렬해 주는 명령입니다. 자료를 순서대로 보기위한 좋은
방법입니다. 게시판을 만들 경우 이 명령을 꼭 한번은 사용해야 할 것입니다.

그림. 9-17 order by 예

위 그림을 보면, work 순으로 차례로 정렬한 모습을 볼 수 있습니다.

제일 처음 일을 그만 둔 사람을 위 명령으로 쉽게 찾을 수 있습니다.

order by DESC 사용하기

mysql>select 열명 from 테이블명 order by 열명 desc ;

desc 옵션을 사용하면 해당 열명을 역순으로 정렬해 줍니다. 뒤 경매 게시판에서도 볼 수 있지만 이 옵션은
유용하게 쓰입니다. 게시판의 경우 사람들은 순서대로 보다 역순으로 봐야지만 제일 최근 자료를 볼수 있는
것입니다. 이 명령은 제일 최근까지 일했던 사람을 역순으로 제일 위에 오게 출력해 줍니다.
mysql의 경우 이 명령이 순차적인 것보다 조금 느리게 출력될 경우도 있습니다. 물론 자료가 많을 경우입니다.
하지만 한번 실행하고 난 뒤는 거의 속도 차이가 없다고 보면 됩니다.
여기서 주의 할 부분은 desc 인자 앞의 열명만 적용이 된다는 것입니다.
그 앞에 다른 열명을 적어 줘도 역순으로 정렬되지 않습니다.

그림. 9-18 order by DESC 예

위에서 볼 수 있듯이 desc 인자의 역할은 되지 않고 있습니다.

레코드 수 보기

mysql>select count(*) from work ;

총 레코드 수를 계산해 줍니다.

그림. 9-19 count( ) 예

위 명령과 group by 명령을 같이 사용할 경우 더 강력한 기능을 합니다.

group by 사용하기

mysql>select 열명,count(*) from 테이블명 group by 열명 ;

해당 그룹의 레코드에 관해 숫자를 파악해서 출력해주는 역할을 합니다. (count(*)와 같이 사용할 경우)

그림. 9-20 group by 예 1

출력의 결과를 보면 일의 종류에 따라 배치된 사람의 수를 파악할 수 있습니다.

그림. 9-21 group by 예 2

위 그림을 보면 jun이 물주기 역할을 두개나 받았다는 걸 알 수 있습니다. 또, 총 맡은 일을 볼 수도 있습니다.
이렇게 여러 가지를 그룹으로 출력해 주는 명령은 유용하게 쓰일 수 있습니다.

그림. 9-22 한 사람이 맡은 일 수

9.10 테이블 지우기와 수정
만들어진 테이블을 지우거나 다시 수정하는 방법은 몇 가지가 있습니다. 게시판의 사용 시에 이 부분은 꼭 들어갑니다. 데이터의 삭제와 추가 등에 사용됩니다.

테이블 지우기

mysql>delete from 테이블명 ;

해당 테이블을 삭제합니다.

테이블에 필드(열) 추가하기

mysql>alter table 테이블명 add 열명 자료형태 ;

테이블에 다른 열을 추가시킵니다.
만약 게시판의 admin 툴을 만든다면 이 방법을 적용할 수 있습니다. 지금까지 말한 모든 설명들은 데이터베이스
admin 툴을 만들 때 사용하는 중요한 기본 질의들입니다.

그림. 9-23 테이블에 필드(열) 추가하기

위 그림에서 tel 필드가 새롭게 추가된 것을 볼 수 있습니다.

테이블의 특정 필드(열) 삭제하기

mysql>alter table 테이블명 drop 열명 ;

해당 테이블의 특정 필드(열)를 삭제합니다.

그림. 9-24 테이블의 특정 필드(열) 삭제하기

위 그림에서 tel 항목이 삭제된 것을 볼 수 있습니다.

테이블 특정 레코드 삭제하기

mysql>delete from 테이블명 where 열명 = ‘레코드명’(데이터명);

해당 테이블에서 특정한 레코드만 삭제합니다.

그림. 9-25 특정 레코드 삭제하기

위 그림에서 name이 glee라는 레코드만 지워진 것을 볼 수 있습니다.

테이블 특정 레코드 수정하기

mysql>update 테이블명 set 열명= ‘레코드명’(데이터명) where 열명= ‘레코드명’;

이 방법은 해당 테이블의 특정 레코드의 내용만 수정할 때 사용합니다.

그림. 9-26 특정 레코드 수정하기

위 그림에서 name가 bian인 레코드 중 hab가 "?" 에서 컴고치기로 고쳐진 것을 볼 수 있습니다.

9.11 mysql 접근과 권한설정
이 제 mysql에 대한 접근하는 방법은 익숙하리라 생각합니다. 그러면 이제 각 데이터베이스 별 접근과 권한에 관한 설정들을 알아 보겠습니다. 뒤의 게시판 등의 소스에는 그냥 root 권한으로 설정했지만 여러분들이 이 부분을 수정하고 여러 가지 다른 방법으로 데이터베이스 접근을 하도록 만들 수 있습니다.

mysql 권한을 설정하기 전에

먼저 mysql 클라이언트에 접속을 합니다. 그리고 디폴트로 설치된 mysql의 데이터베이스 항목을 살펴 보겠습니다.

그림. 9-27 데이터베이스 보기

위 그림은 아직 데이터베이스를 추가로 만들지 않은 여러분들과는 조금 다릅니다.
디폴트는 mysql과 test 데이터베이스만 있습니다.
그럼 mysql의 전반적인 설정들이 들어 있는 mysql 데이터베이스를 선택하겠습니다.

그림. 9-28 mysql 데이터베이스 내용

위 그림에서 나열된 테이블 중 db와 user을 살펴 보겠습니다.

db 테이블 - 각 데이터베이스의 이름과 호스트, 사용자 등의 권한을 설정합니다.
user 테이블 - mysql을 사용할 수 있는 유저와 해당 유저의 권한을 설정합니다.

그림. 9-29 db 테이블 속성

위 그림은 db 테이블의 속성들입니다. 이 테이블에 해당 유저들이 사용할 데이터베이스를 설정하고 권한을 줍니다.

데이터베이스 사용 user 등록하기

여기서는 test 데이터베이스의 사용자(cry)를 등록해 보겠습니다.

그림. 9-30 db 테이블 쿼리 (Host,Db,User)

위 그림에서 볼 수 있듯이 db 테이블은 데이터베이스의 소유자를 등록하는 곳입니다.
(Test 데이터베이스의 소유자는 지금 아무도 설정되지 않았습니다.)
그리고 user 테이블은 데이터베이스를 사용하는 user을 등록하는 곳입니다. 즉, db 테이블에 소유자가 등록되어
있더라도 user 테이블에 그 소유자(사용자)가 존재하지 않으면 안됩니다.
처음 mysql의 설정에서 root를 설정했습니다. 이 root 계정은 뭐든지 다 할 수 있는 계정이므로 데이터베이스별로
관리를 할 수 있고 모든 권한이 없는 해당 데이터베이스만 관리 하는 계정이 필요로 한 것입니다.
db 테이블의 속성을 보면 여러 가지 데이터베이스에서 질의하는 속성 들을 볼 수 있을 것입니다.
그 중 사용하게 할 질의 들은 ‘y’ 그렇지 못하게 할 경우는 ‘n’ 등을 줌으로 해서 데이터베이스 관리자의 권한을
정할 수 있습니다. 해당 질의 권한을 설정하는 부분은 총 10개 항목이 있습니다.
앞에서 insert 문과 update 문을 보았기 때문에 테이블을 다루는 방법은 이제 습득했을 줄 압니다.
여기서는 기존에 존재하는 test 테이블을 수정하는 것이기 때문에 update문을 사용 했습니다.
만약 새로운 데이터베이스를 db 테이블에 추가할 경우는 당연히 insert 문을 이용하면 되겠습니다.

그림. 9-31 test 데이터베이스 사용자 등록

insert into 문을 이용한 새로운 데이터베이스 등록 역시 다음과 같은 식으로 해주면 됩니다.

insert into db values("해당 열" , "해당 열" …….);

Update 방법은 기존에 있던 행을 말 그대로 업데이트 한 것 입니다.
필자의 경우 update가 조금 헛갈리는 경우가 있어 여러분들도 혹 그런 분이 있을 거 같아 insert 대신
update문을 사용해 봤습니다.

그럼, 해당 항목들이 업데이트 되었는지를 select 문을 이용해 확인해 보겠습니다.

select Host,Db, User from db ;

그림. 9-32 db 테이블의 test 데이터베이스 사용자 추가 확인

Mysql 사용자 등록하기

user 테이블의(mysql 데이터베이스에서) test 데이터베이스를 사용할 cry 사용자를 등록해 보겠습니다.
우선 user 테이블의 속성들을 확인합니다.
user 테이블 역시 해당 질의에 관한 권한이 있습니다. 총 14개 항목이 존재합니다.
여기서는 insert into 문을 사용해서 추가를 하겠습니다.

그림. 9-33 user 테이블 속성

위 그림에서 유저를 등록하기 전에 상기 할 부분은 각각의 질의에 관한 권한 설정을 명확히 정한 다음
등록하는 것입니다. cry라는 사용자가 test 데이터베이스 만을 관리하는 것이라면 질의 모두 "n"으로
해야 될 것입니다. 하지만 cry가 다른 데이터베이스도 관리하길 원한다면 질의 중 필요한 항목만 "y"로
선택하면 됩니다.

그럼 다음을 입력해서 user 테이블의 등록 상태를 확인해 보도록 하겠습니다.

select Host, User, Password from user ;

그림. 9-34 user 테이블 쿼리(Host,User,Password)

위 그림에서 Password 항목을 보면 알 수 없는 숫자와 알파벳으로 되어진 것을 볼 수 있습니다.
이것은 mysql이 passwd을 저장할 때 암호화해서 저장하기 때문입니다.
그런 이유로 insert into 문으로 암호 열을 입력할 때는 password() 함수를 사용해서 입력해야 합니다.

insert into user (Host,User,Password) values("192.168.0.1", "cry", password("cry98"));

이렇게 실행하면 됩니다.(passwd 부분은 여러분들이 정하는 곳입니다.)
여기서는 Host, User, Password 항목만 추가했습니다. 그것은 나머지 열의 질의 권한 항목은 디폴트가 "n"이기
때문에 입력하지 않아도 됩니다.

그림. 9-35 user 테이블에 cry 사용자 등록

위 그림에서 192.168.0.1의 Host와 cry 유저 그리고 암호가 등록된걸 확인할 수 있습니다.
이 설정들은 mysql을 재시동하고 다시 접속할 때부터 적용됩니다.

mysql 사용자 테스트

cry 계정으로 192.168.0.1 호스트로 접속해 보겠습니다.

그림. 9-36 mysql 추가 사용자 접속 확인

물론 앞에서 설정한 "cry98" 암호로 접속해야 됩니다.

그림. 9-37 cry 계정 데이터베이스 사용 가능 테스트

위 그림에서 cry 계정의 처음 권한 설정에 의해 test 데이터베이스만 사용 가능함을 볼 수 있습니다.

여기까지 대략적인 mysql의 사용자 권한 설정과 추가에 대해 살펴 보았습니다.
이러한 방법들을 이용해서 나중에 게시판에 적용하면 많은 도움이 될 것입니다.
참고로, 뒷 장의 게시판 부분과 나머지 소스들의 mysql 연동은 root 권한으로 설정되어 있습니다.
하지만 여러분들이 원하는 계정을 추가한 다음 입 맛에 맞게 얼마든지 수정할 수 있습니다.

9.12 mysql 데이터의 복구와 backup
데 이터의 백업은 정말로 중요한 일 중에 하나입니다. 필자는 컴퓨터를 통째로 날린 적이 있기 때문에 더욱 백업에 열을 올립니다. Mysql 뿐아니라 php 역시 tar로 반드시 백업하는 습관을 가져야 합니다. 요즘의 에디터들은 대부분 작업하다가 종료한 파일에 대해 자동적으로 백업 파일을 생성해 저장합니다. 그 덕분에 필자가 이렇게 소스를 여러분들에게 보여 줄 수 있었던 것 같습니다. 하지만 방심은 금물이듯 하루라도 백업하지 않으면 손가락이 마비될 정도로 백업해도 후회는 안 할 것입니다. Mysql의 복구는 /usr/local/mysql/bin 디렉토리에 존재하는 isamchk를 이용해서 합니다. isamchk의 경우 상당히 세밀한 방법으로 데이터베이스의 파손 여부를 점검하고 복구합니다.

isamchk 사용하기

#isamchk 옵션 테이블명

Mysql의 경우 대부분 isamchk를 돌릴 정도의 에러 발생은 적은 편입니다. 하지만, 기계는 얼마든지 예고없이

정지하거나 말썽 일으킬 소지를 가지고 있기 때문에 꼭 필요한 유틸리티입니다.

isamchk 옵션

   #isamchk --help로 다양한 옵션을 확인하기 바랍니다.
   #isamchk 테이블명 - 해당 테이블의 에러를 점검합니다. 상당한 부분까지 에러를 점검해 줍니다.
   #isamchk -e 테이블명 - 해당 테이블의 모든 데이터를 점검해 줍니다. -i 옵션을 추가할경우 통계도 보여 줍니다.
   #isamchk -r -q 테이블명 - 해당 테이블의 에러를 쉽고 빠르게 복구해 줍니다.

이 같이 isamchk를 사용함으로써 안전하게 복구할 수 있습니다. 더 상세한 방법은 --help를 사용해서 확인하기 바랍니다.

mysql의 백업하기

#mysqldump -h 호스트명 -u 유저명 -p 데이터베이스명 > 백업되어질 파일명

Mysql 백업의 경우 /usr/local/mysql/bin 디렉토리에 존재하는 mysqldump를 이용하는 방법입니다.

그림. 9-38 mysqldump 예

   위의 test 데이터베이스를 test_bak.sql 파일로 저장했습니다. 물론 저장된 파일은 /bin 디렉토리에 생성됩니다.

저장된 파일 복구하기

#mysql -h 호스트명 -u 유저명 -p 데이터베이스명 < 백업된 파일명

해당 백업 파일을 다시 원상 복구 시킵니다.

참고로 mysql의 데이터베이스를 삭제하는 방법은 다음과 같습니다.

drop database 데이터베이스 명;

간혹 테이블 지우는 명령은 아는데 데이터베이스 지우는 명령을 모르는 분들이 많더군요.

root 패스워드를 잃어 버렸을때, 패스워드를 변경하는 방법

root 패스워드를 잃어 버렸을때, 패스워드를 변경하는 방법

(1) ps -aux를 이용하여 mysqld 프로세스를 kill 시킨다.

   [root@dbadb local]# ps -aux | grep mysql

(2) mysqld를 시작한다. 이때 --skip-grant-tables 옵션을 사용한다.

   [root@dbadb local]# ./mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &

   [root@dbadb local]# ./mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &
   [1] 643
   [root@dbadb local]# Starting mysqld daemon with databases from /usr/local/mysql/var

(3) root 패스워드를 변경한다.

   [root@dbadb local]# cd mysql/bin
   [root@dbadb mysql]# bin/mysql
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 1 to server version: 4.0.16

   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

   mysql> use mysql;
   mysql> update user set password=password('newpassword') where user='root';
   mysql> flush privileges;

   mysql> quit;
   Bye

자주쓰는 명령 정리

# root암호설정 - root로 로그인하여 해야함
% mysqladmin -u root password '변경암호'
% mysqladmin -u root -p기존암호 password '변경암호'

root암호변경설정
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root -p password 'new-password'
/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'

DB작업
DB생성: mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )
DB삭제: mysql> drop database DB명
DB사용: mysql> use DB명 (엄밀히 말하자면, 사용할 'default database'를 선택하는 것이다.)
DB변경: mysql> alter database db명 DEFAULT CHARACTER SET charset (4.1이상에서만 available)

MySQL 연결
mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

데이터파일 실행(sql*loader기능)
mysql>load data infile "데이터파일" into table 테이블명 ;
데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력
데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

질의 파일 실행
쉘프롬프트상에서
mysql -u 사용자 -p DB명 < 질의파일
or
mysql프롬프트상에서
mysql> source 질의파일

쉘프롬프트상에서 질의 실행
dbakorea@lion board]$ mysql mysql -u root -pxxxx -e \
> "INSERT INTO db VALUES(
> 'localhost', 'aaa', 'aaa',
> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"

사용자 생성 & 사용자에게 DB할당
shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('localhost','DB명','사용자','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('%','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

CASE 2: GRANT명령을 이용한 사용자 생성(이 방법이 권장된다)
kang이라는 DB를 만들고, 이 DB를 아래에서 나열된 권한을 가진 kang이라는 사용자를 생성
* 권한종류는 mysql> help grant types 로 알 수 있다.

create database kang;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@localhost identified by 'kang';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@'%' identified by 'kang';

* 해당 DB에 모든 권한을 주려면 아래와 같이 생성한다.
grant ALL on kang.* to kang@localhost identified by 'kang';
grant ALL on kang.* to kang@'%' identified by 'kang';

mysql> create database kang;
Query OK, 1 row affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@localhost identified by 'kang';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@'%' identified by 'kang';
Query OK, 0 rows affected (0.01 sec)

mysql>

여러가지 명령정리
mysql> show variables; 서버의 variables(설정사항)출력
mysql> show variables like 'have_inno%' 조건에 맞는 variables만 출력
mysql> show databases; database목록
mysql> show tables; 현재DB의 테이블목록(temporary table은 출력하지 않음)
mysql> show tables from db명; 지정된 db명이 소유한 테이블목록
mysql> show tables like 'mem%'; 조건에 맞는 테이블목록만 출력
mysql> show index from 테이블명; 인덱스 보기
mysql> show columns from 테이블명; 테이블구조(describe 테이블명, explain 테이블명)
mysql> show table status; 현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status from db명; 지정된 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show create table 테이블명; 해당 테이블 생성 SQL문 출력
mysql> rename table 테이블1 to 테이블2; 테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; rename multiple tables
mysql> rename table db1명.테이블명 to db2명.테이블명; 테이블을 다른 DB로 이동
mysql> alter table 테이블명 add 컬럼명 데이터타입; 컬럼추가
mysql> alter table 테이블명 del 컬럼명; 컬럼제거
mysql> alter table 테이블명 modify 컬럼명 컬럼타입; 컬럼명에 지정된 컬럼타입의 변경
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입 컬럼명 변경
mysql> alter table 테이블명 type=innodb; 테이블type변경
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)
mysql> select version(); MySQL서버버전 출력
mysql> create table 테이블2 as select * from 테이블1; 테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; 테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)
mysql> insert into 테이블2 select * from 테이블1; 테이블1의 데이터를 테이블2에 insert

테이블이 존재여부 파악
DROP TABLE IF EXISTS 테이블명;
CREATE TABLE 테이블명 (...);
프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을 파악할 수 있다.
ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우 full scan이 발생하므로 사용하지 마라.
대신 select * from 테이블명 where 0; 을 사용하라. 질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.

접속
mysql {-h 접속호스트} -u 사용자 -p 사용DB
-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.
mysql> INSERT INTO user VALUES('접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호 '),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

검색조건(where)
regular expression을 지원하다니 신기하군..
mysql> select * from work where 열명 regexp "정규표현식";

백업 & 복구
mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (상이한 머쉰)
mysql -u dbakorea -p db_dbakorea < dbakorea.sql

mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea

테이블 생성구문만을 화면에서 보려면 다음과 같이 --no-data를 사용한다. 테이블명을 생략하면 모든 테이블 출력
mysqldump -u 유저명 -p --no-data db명 테이블명

테이블 검사
isamchk

오라클 sysdate와 동일
insert into test values('12', now());

유닉스 time()함수 리턴값 사용
FROM_UNIXTIME(954788684)
UNIX_TIMESTAMP("2001-04-04 :04:04:04")

MySQL 디폴트 DB&로그파일 위치
/var/lib/mysql
/var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이스라고 볼 수 있다.

replace
해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)
replace into test values('maddog','kang myung gyu')'

explain
explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌
mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |
| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
2 rows in set (0.01 sec)

temporary table
크기가 큰 테이블에 있는 subset에 대한 질의라면 subset을 temporary table에 저장한 후 질의하는 것이 더 빠를 경우가 있다.
temporary table는 세션내에서만 유효하고(현재 사용자만이 볼수 있다는 뜻), 세션종료시 자동적으로 drop된다.

create temporary table (...);
create temporary table (...) type=heap; 디스크가 아닌 메모리에 테이블 생성

존재하는 permanent table의 테이블명과 동일하게 생성할 수 있으며,
temporary table은 permanent table보다 우선시되어 처리된다.
4.0.7의 감마버전에서 테스트하면 결과는 약간 달라진다. 버그인건지..

mysql> create table test (id varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values('dbakorea');
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table test(id varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----------+
| id |
+----------+
| dbakorea |
+----------+
1 row in set (0.00 sec)

Table Type에 다른 Files on Disk

ISAM .frm (definition) .ISD (data) .ISM (indexes)
MyISAM .frm (definition) .MYD (data) .MYI (indexes)
MERGE .frm (definition) .MRG (list of constituent MyISAM table names)
HEAP .frm (definition)
BDB .frm (definition) .db (data and indexes)
InnoDB .frm (definition)

보통 mysqldump를 사용하여 백업을 수행하여 다른 DB서버에 데이터를 restore하면 된다.
MySQL은 별다른 작업없이 데이터파일을 단순히 복사(copy)하는 것만으로도 다른 서버에
DB을 이동시킬 수 있다. 하지만, 이런 방식이 지원되지 않는 table type도 있다.

ISAM: machine-dependent format하기때문에..
BDB : .db파일에 이미 테이블위치가 encode되어 있기때문에..
MyISAM, InnoDB, MERGE :가능(machine-independent format)

별다른 지정을 하지 않았다면 디폴트 TABLE type이 MyISAM이므로, 무난히 migration할 수 있다.
floating-point컬럼(FLOAT,DOUBLE)이 있다면 이러한 방식이 실패할 수 도 있다.

쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우
mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다.
나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.
/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.
주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}
MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.
socket file을 지정하는 --socket이라는 옵션으로 다음과 같이 지정하면 된다.

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test

하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.
mysql은 설정사항을 다음 3가지 파일에서 검색한다.

/etc/my.cnf global options(MySQL 전체적으로 사용되는 옵션 정의)
mysql-data-dir/my.cnf 특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)
~/.my.cnf 사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.
소켓파일의 지정은 다음줄을 넣어주면 된다.

socket = /tmp/mysql.sock

== /etc/my.cnf예 ==
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

MySQL에서 통계처리시
orderby, groupby 는 sort_buffer를 늘여준다.(show variables)

live table(smslog)에서 모든 질의를 처리하지 말고 summary table에 질의결과를 저장해 재질의 처리한다.
summary table이 heap-type table가 가능한지 확인할 것.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
   tblTemp1.fldOrder_ID > 100;

join이 subselect보다 빠르다.
join시 사용되는 컬럼은 동일한 column type과 길이를 가져야만 최적의 속도를 보장한다.
즉, 동일 column type이지만 길이가 다르다면(char(11), char(10)), 동일한 컬럼도메인으로 변경해주는 것이 좋다.
where의 in은 optimize되어 있으므로 빠르다
insert,select는 동시에 수행가능하다.(어떻게?)
explain으로 질의과정 점검

varchar to/from char
conversion varchar를 char로 변경할 경우 모든 컬럼타입을 동시에 변경해야 한다.
반대의 경우, 하나만 char->charchar변경시 다른 모든 컬럼도 varchar로 변경됨
참.. 특이하구만..

mysql> CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80));
Query OK, 0 rows affected (0.05 sec)

mysql> desc chartbl;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> alter table chartbl modify name char(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table chartbl modify name char(40), modify address char(80);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(40) | YES | | NULL | |
| address | char(80) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

"For each article, find the dealer(s) with the most expensive price."

표준안
   SELECT article, dealer, price
   FROM shop s1
   WHERE price=(SELECT MAX(s2.price)
   FROM shop s2
   WHERE s1.article = s2.article);

수정안(최적화)
   CREATE TEMPORARY TABLE tmp (
   article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
   price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);

   LOCK TABLES shop read;

   INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

   SELECT shop.article, dealer, shop.price FROM shop, tmp
   WHERE shop.article=tmp.article AND shop.price=tmp.price;

   UNLOCK TABLES;

   DROP TABLE tmp;

==============================================================================
MySQL 특성정리
==============================================================================
primary key, foreign key지원
index 지원(15개컬럼, 256byte까지)
MySQL에서의 Stored Script개념 => SQL server language
commit-rollback개념 => lock tables(lock table test write -> 트랜잭션.. -> unlock tables)
컬럼명길이: 64자까지, 컬럼 Alias: 256자까지
not case-sensitive: keywords, functions, column, index명
case-sensitive: database, table, alias명
키워드,함수명은 대소문자구별이 없지만, db명과 table명은 Unix계열이라면 case-sensitive하다.
(이는 오브젝트명이 OS의 fs에 따라 저장되기 때문이다. 서버의 lower_case_table_names 변수를
1로 설정하면 오브젝트명은 모두 소문자로 저장되므로 유닉스-윈도간 호환성을 높일 수 있다.

지원되지 않는 부분:
Stored Procedure(5.0이상부터 지원된다고 함)
View(5.0이상부터 지원된다고 함)
Trigger(5.0이상부터 지원된다고 함)
subquery(4.1이상부터 지원된다고 함)
union, union all(4.0이상부터 지원됨)

[테이블 type에 따른 인덱스 특성]
Index Characteristic ISAM MyISAM HEAP BDB InnoDB
NULL values allowed No Yes As of 4.0.2 Yes Yes
Columns per index 16 16 16 16 16
Indexes per table 16 32 32 31 32
Maximum index row size (bytes) 256 500 500 500/1024 500/1024
Index column prefixes allowed Yes Yes Yes Yes No
BLOB/TEXT indexes allowed No Yes(255 bytes max) No Yes (255 bytes max) No

인덱스 생성
- alter table을 이용한 인덱스 생성이 더 flexible함
- 인덱스명은 생략가능

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD UNIQUE 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD PRIMARY KEY (인덱스컬럼);
ALTER TABLE 테이블명 ADD FULLTEXT (인덱스컬럼);

CREATE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

unique인덱스와 primary key인덱스와의 차이
unique은 null허용하지만, primary key는 null허용 안함
unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

테이블생성시 지정
CREATE TABLE 테이블명
(
  ... column declarations ...
  INDEX 인덱스명 (인덱스컬럼),
  UNIQUE 인덱스명 (인덱스컬럼),
  PRIMARY KEY (인덱스컬럼),
  FULLTEXT 인덱스명 (인덱스컬럼),
...

);

index prefix 생성
- 컬럼의 전체길이중 일부만 인덱스로 사용
- supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables
- 지정되는 길이는 byte단위가 아닌 charater단위이므로, multi-byte character일 경우 주의
- blob, text 컬럼타입일 경우, index prefix 가 유용(255 길이까지 가능)

CREATE TABLE 테이블명
(
  name CHAR(30) NOT NULL,
  address CHAR(60) NOT NULL,
  INDEX (name(10),address(10))
);

인덱스 삭제
DROP INDEX 인덱스명 ON 테이블명;
ALTER TABLE 테이블명 DROP INDEX 인덱스명;
ALTER TABLE 테이블명 DROP PRIMARY KEY;

outer join

[MySQL]
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;

SELECT
  student.name, student.student_id,
  event.date, event.event_id, event.type
FROM
  student, event
  LEFT JOIN score ON student.student_id = score.student_id
   AND event.event_id = score.event_id
WHERE
  score.score IS NULL
ORDER BY
  student.student_id, event.event_id;


:= 문장을 이용한 변수의 설정

현재 dbakorea의 데이터베이스강좌게시판에 등록된 총 게시물은 43개이다. 43개의 강좌를 읽은 수(hit수)는 각각 다르다.
평균 hit수를 구해 보자.

mysql> select @total_hit := sum(hit), @total_record := count(*) from zetyx_board_database;
+------------------------+---------------------------+
| @total_hit := sum(hit) | @total_record := count(*) |
+------------------------+---------------------------+
| 3705 | 43 |
+------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> select @total_hit/@total_record as 평균HIT;
+-----------------+
| 평균HIT |
+-----------------+
| 86.162790697674 |
+-----------------+
1 row in set (0.00 sec)

select substring(subject from 9) from zetyx_board_database where substring(subject, 1, 8) = '[ORACLE]';

보통 상용DBMS들이 row-level locking을 지원한다. 쉽게 말해 레코드단위로 락킹한다는 말이다.
반면, MySQL의 MyISAM 테이블타입은 table-level locking을 사용한다.
쉽게 말하면, insert, update, delete작업은 전체 테이블에 락을 걸고 처리된다는 것이다.
row-level락보다 비효율적이지만,.. MySQL은 빠르기 때문에 이 단점이 상쇄된다.

Compressed MyISAM(packed MyISAM)
정적인 테이블데이터는 압축하여 20-60%정도의 공간을 절약할 수 있다.
Production데이터를 CD로 받아서 차후 디스크에 풀지 않고 CD자체로 바로 사용할 수도 있다.
gzip등으로 백업받으면 이를 푸는 과정이 필요할 것이다.
% myisampack dbakorea.myi

데이터베이스 게시판의 Merge Table에 좀 더 자세한 내용을 적어 두었다.

RAID Table
1개의 테이블은 OS상에 3개의 파일로 구성된다.
스키마파일(.frm), data파일(.myd), index파일(.myi)
MySQL의 RAID테이블은 데이터파일(.myd)을 여러개의 파일들로 구성하는 것이다.

create table raid_test (...)
type=myisam raid_type=striped raid_chunks=4 raid_chunsize=8

테이블을 4개의 데이터파일로 나누고, 8kb단위로(8kb stripe) 라운드로빈 방식으로 write가 이루어진다.