허정수 (나우누리:wertyu)
|
1. 들어 가는 말 대학 2년 반 동안 공부를 하면서 무엇을 배웠는지 기억이 잘 안나지만, 절대 잊을 수 없는 한 마디가 있다. “정보화 시대에서 엔트로피는 ‘정보’이다.
물리 교수님으로부터 들은 이야기인데 논리적으로
따지자면 맞는 말은 아니지만 요즘 들어서 위의 말을 뼈저리게 실감하고
있다. 왜 이리 해야 할 것은 많고, 이쪽 저쪽에서 새로운 기술이
쏟아지는지. 놓치고 나면 혼자 도태되는 것 같아 모두 해보려고
해도 쉽게 되지도 않는다. 이번 시간에는 SQL 언어에 대해서 공부하도록 한다. 이미 지면이나 인터넷을 통해 SQL 언어가 많이 소개되어서, 다시 쓰는 것이 중복 투자가 아닌가 생각을 했지만, 아직 SQL 언어를 잘 모르는 사람들이 있다고 생각을 하고 SQL 언어에 대해 쓰기로 하였다. 다음 기사에서는 MySQL C API에 대해서 알아 보고, 마지막으로 2회에 걸쳐 PHP3와 MySQL을 이용한 Web와 DB의 연동에 대해서 알아 보기로 한다.
2. MySQL의 컬럼 타입 컬럼(Column)은 데이타 베이스를 이루는 가장 작은 단위이다. 컬럼들이 모여서 레코드(Record)를 이루고, 레코들들이 모여서 테이블(Table)이 된다. 테이블이 모여서 데이타 베이스를 이루게 된다. 즉, 데이타 베이스를 데이타들의 계층적 구조라고 말한다. TINYINT : 부호있는 수는 -128에서 127까지, 부호 없는 수는 0에서 255까지 표현할 수 있다. 1 바이트 SMALLINT : 부호있는 수는 -32768에서 32767까지, 부호 없는 수는 0에서 65535까지 표현할 수 있다. 2 바이트 MEDIUMINT : 부호있는 수는 -8388608부터 8388607까지, 부호없는 수는 0에서 16777215까지의 수를 표현할 수 있다. 3 바이트 INT : 부호있는 수는 -2147483648부터 2147483647까지, 부호없는 수는 0에서 4294967295까지의 수를 표현할 수 있다. 4 바이트 INTEGER : INT와 같다. BIGINT : 부호있는 수는 -9223372036854775808부터 9223372036854775807까지의 수를 표현할 수 있고, 부호없는 수는 0부터 18446744073709551615까지 표현할 수 있다. 8바이트 FLOAT :
단일 정밀도를 가진 부동 소수점을 나타낸다. 언제나 부호 있는
수이고, DOUBLE : 2배
정밀도를 가진 부동 소수점 수이다. 언제나 부호있는 수이고, DATE : 날짜를
표현하는 타입으로 ‘1000-01-01’부터 ‘9999-12-31’까지를 나타낼
수 있다. DATETIME : 날짜와
시간을 같이 나타내는 타입이다. ‘1000-01-01 00:00:00’부터 TIMESTAMP : ‘1970-01-01 00:00:00’부터 2037년까지 나타낼 수 있다.4바이트 TIME : 시간을 나타낸다. ‘-839:59:59’부터 ‘838:59:59’까지 나타낼 수 있다. YEAR : 년도를 나타낸다. 1901년부터 2155년, 0000년을 나타낼 수 있다. CHAR(M) : 고정 길이를 갖는 문자열을 저장할 수 있다. M은 1부터255까지이다. M이 20이면 20개의 문자를 저장할 수 있다. VARCHAR(M) : CHAR는 고정길이인 반면 VARCHAR는 가변 길이이다. 예를 들어 VARCHAR(20)인 컬럼에 10자만 저장을 하면, 실제로도 10자 만큼을 기억장소를 차지한다. 하지만, CHAR는 나머지 10자를 공백으로 메꾼다. TINYBLOB BLOB MEDIUMBLOB LONGBLOB
3. Database 만들기. 이번엔 Database를 만들어 보자. 이번 회에서는 SQL 언어를 소개하기 위해서 슈퍼마켓에서 물건을 관리하는 예를 들도록 하겠다. super라는 테이타베이스를 만드는 방법은 두 가지가 있다. 하나는 mysqladmin을 이용하는 방법이고, 다른 방법은 mysql에서 직접 만드는 방법이다. % mysqladmin create super %mysqlshow 이때 암호를 걸어 놨다면 -p 옵션을 붙여야 한다. 매번 -p 옵션을 붙이고, 암호를 입력하는 것이 귀찮다면, 홈 디렉터리에 .my.cnf 파이를 만들어 놓고, [client] 위의 두 줄을 적어 두면 된다. .my.cnf 파일은 다른 사람이 못 보도록 퍼미션을 주자. 데이타 베이스를 만드는 다른 방법은 mysql이라는 client 프로그램을 이용하여 SQL 문을 이용하는 방법이다. % mysql Type ‘help’ for help. mysql> CREATE DATABASE super ; 데이타 베이스가 만들어 지면, mysql> USE super ; 를 해야 super 데이타 베이스를 사용할 수 있다. 사용하고자 하는 데이타베이스는 mysql의 실행시에 옵션으로 붙여줄 수도 있고, USE database_name을 이용해서 지정할 수도 있다. mysql> show databases ;
4. 테이블 만들기. 슈퍼마켓에서 사용될 수 있는 예를 들기로 했으므로, 다음과 같이 세 개의 테이블을 만들 것이다.(단순히 예를 들기 위한 것으로 테이블 설계는 그다지 좋지 않다.) 1) 도매상에 대한 테이블 : 도매상의 이름, 도매상의 전화번호, 도매상의 E-Mail 주소를 저장한다. 2) 물건들에 대한 테이블 : 슈퍼마켓에서 파는 물건들에 대한 정보를 담고 있다. 물건종류별 상품 코드, 물건 이름, 가격, 팔고 남은 양, 납품 받은 도매상 이름. 3) 매출에 대한 테이블 : 물건 이름, 팔린 개수, 금액, 날짜. 위를 바탕으로 테이블을 만들어 보자. 이제 테이블 만들기를 비롯한 모든 SQL 명령은 mysql에서 입력하기로 한다. * 테이블 만들기 * 예) mysql> CREATE TABLE dome(name CHAR(20),
phone VARCHAR(20), email VARCHAR(30)) ; 만들어진 테이블을 확인하고 싶으면 mysql> SHOW TABLES; 이용하면 된다. mysql> SHOW TABLES ; * VARCHAR와 CHAR의 차이는 위에서 설명을 하였다. VARCHAR는 문자열의 크기만 사용을 하므로, 차지하는 용량을 줄일 수 있다. 하지만, 속도를 CHAR를 이용하는 것이 좀 더 빠르다. * PRIMARY KEY는 그 컬럼의 값이 두 개의 레코드에서 같은 값을 가질 수 없음을 나타낸다. 예를 들어 학번이나, 주민 등록 번호, 상품 코드 같은 것은 레코드 마다 유일하므로 PRIMARY로 한다. * AUTO_INCREMENT는 MySQL의 특징 중의 하나이다. 레코드를 삽입할 때마다 컬럼의 값을 자동으로 하나 씩 증가시켜 준다. 예를 들어 마지막 레코드의 No가 10이었으면 그 다음 레코드를 삽입할 때 자동으로 11이 된다. 게시판에서 게시물을 추가할 때 매우 편한 기능이다. 게시판에 600개의 글이 있다고 가정을 해보자. 다음 번 게시물의 번호는 601이 되어야 하는데, 만약 두 사람이 동시에 글을 저장하면 두 개의 게시물이 601번이 될 수도 있다. 이러한 일을 막기 위해서 lock과 unlock을 이용해야 하는데, AUTO_INCREMENT를 이용하면 간단하게 처리할 수 있다. AUTO_INCREMENT로 쓰일 컬럼은 꼭 PRIMARY KEY이던지 INDEX이어야 한다. 또한 INDEX로 쓰일 컬럼은 꼭 NOT NULL이어야 한다. AUTO_INCREMENT는 테이블당 한 개밖에 존재할 수 없다. ‘SHOW COLUMNS FROM 테이블이름’을 이용하여 테이블의 컬럼에 대한 정보를 얻을 수 있다.
5. 레코드를 삽입하기. 이젠 테이블도 만들어 졌으니, 테이블에 데이타를 입력하도록 하자. 테이블에 데이타를 입력하는 SQL 명령은 ‘INSERT’이다. 먼저 dome 테이블과 Item 테이블에 데이타를 입력해보자. * 레코드 삽입 * 예) mysql> INSERT INTO dome VALUES(‘A상점’,
‘222-2222’, ‘aa@xxx.com’) ; mysql> INSERT INTO Item VALUES(1111,
‘쪼꼬빠이’, /* 가격*/150, /* remain*/1000, ‘A상점’)
; /* 주석문 */ ( dome 테이블의 remaing 컬럼은 현재 팔고 남은 개수를 나타낸다. 지정된 개수 이하로 떨어 지면 주문을 하기 위해서 쓰였다.) 잘 삽입이 되었는지 결과를 보고 싶다면, mysql> SELECT * FROM dome ; 등으로 확인할 수 있다. SELECT에 대한 자세한
설명은 밑에 있다. 마지막으로 export 테이블에 데이타를 입력해 보도록 하자. export 테이블은 판매량을 기록하는 테이블로 만들었다. 상품의 이름과 판매량, 판매 기록, 판매 날짜 등을 저장한다. 다음과 같이 하여 레코드를 삽입할 수 있다. mysql> INSERT INTO export VALUES(NULL, ‘쓴라면’, 3, 3*400, CURRENT_DATE) ; * 위는 ‘쓴라면’이 3개가 팔렸다고 가정을
했다. 한 가지 더 고려를 해야 한다. item 테이블에 ‘쓴라면’에 관한 항목 중 remaining 컬럼에서 팔린 개수 만큼을 빼줘야 한다. 레코드의 값을 수정하기 위해선 UPDATE를 이용한다. * 레코드 수정 * UPDATE 테이블_이름 SET 컬럼=식,... [WHERE
조건] ([] 사이는 옵션을 나타낸다.) 예) ‘WHERE’이하는 조건을 나타낸다. 즉, name이 ‘쓴라면’인 레코드의 remain만을3만큼 감소 시킨다.(실수로 테이블의 이름을 item이라고 하면 안된다. MySQL은 테이블의 이름에 대해서는 대소문자를 구별한다. 컬럼의 이름은 안함). WHERE 이하의 조건을 쓰지 않으면 모든 레코드의 remain에서 3이 감소된다. 위를 계속 반복하여 export 테이블에 여러 개의
레코드를 삽입하자.
6. SELECT 위에서 잠시 SELECT에 대해 잠시 소개하였다.
SELECT는 테이블에서 레코드를 선택하여, 리턴하는 SQL 명령이다.
SELECT를 사용하여 자신이 원하는 레코드만을 선택할 수 있기 때문에
매우 중요하다. 이 글에서도 SELECT에 많은 부분을 할당하여 소개한다. mysql> SELECT * from dome ; +----+----------+------------+------------+-----------+ 140 | 21000
| 1999-05-17 | ( 물론 결과는 독자에 따라 다르다.) SELECT 뒤의 ‘*’는 모든 컬럼을 나타낸다. 즉 모든 컬럼을 리턴하라는 의미이다. 만약 name 컬럼만 리턴하고 싶다면, SELECT name FROM export 하면 된다. 그럼, 이제 SELECT의 파워를 느껴 보자. mysql>SELECT * from export WHERE when = ‘1999-05-15’ ; (지면 관계상 SELECT의 결과는 싣지 못하니 직접
확인하기 바란다.) mysql> SELECT SUM(how_much) FROM export
; SUM(expr)은 expr의 합을 리턴한다. 만약 1999-05-15의
판매액을 보고 싶다면 WHERE when = ‘1999-05-15’를
붙여 주면 된다. mysql> SELECT SUM(how_many) FROM export WHERE name=’쓴라면’ AND when = ‘1999-05-17’ ; 이렇게 하면 된다. ‘AND’ 혹은 ‘&&’은
‘논리적 그리고’를 나타낸다. mysql> SELECT * FROM export WHERE how_much >= 10000 ; 이렇게 하면 된다. SELECT를 이용하여 소트를 할 수도 있다. 판매액이 가장 많은 순서로 소트를 하여 보자. mysql> SELECT * FROM export ORDER BY how_much DESC ; DESC는 내림 차순이고, ACS는 올림 차순을 나타낸다.
mysql> SELECT when AS ‘날짜’, SUM(how_much)
as ‘수입’ FROM export GROUP BY when ; 위의 쿼리는 많은 것을 보여 주고 있다. * when AS ‘날짜’, * GROUP BY when : when의 컬럼에 대해서 같은 컬럼 값을 갖는 것에 대해서만 계산을 한다. 각 날짜별로 SUM을 구하므로 꼭 필요하다. 이번엔 주문을 하기 위해 도매상의 연락처를 알아 보자. 물건이 200개 이하일 때 도매상으로 주문을 하기로 한다. 주문을 직접할 수는 없기에 전화 번호와 E-mail 주소 만을 찾아 보자. 우리의 예에서는 물건이 3 개밖에 안되지만 예를 들어 보기로 한다. mysql> SELECT Item.name AS ‘상품명’,
dome.name AS ‘도매상’, * FROM Item, dome : 두 개의 테이블로부터 값을
리턴한다. 두 개의 테이블에서 리턴을 하므로, 어떤 테이블의 컬럼인지,
꼭 명시적으로 적어야 한다.
7. 파일로부터 레코드 입력을 받기, 결과를 파일로 저장하기. 이번에는 파일에 데이타가 있을 때, 이 파일을
테이블에 저장하는 방법과 SELECT로 리턴된 레코드를 파일에 저장하는
방법에 대해 설명을 한다. ex.txt) 이 파일의 내용을 export 테이블에 삽입하고 싶으면 mysql> LOAD DATA INFILE ‘root/doc/mysql/ex.txt’
INTO TABLE export 이렇게 하면 ex.txt 파일의 내용이 export 테이블에 삽입된다. * ‘root/doc/mysql/ex.txt’ : 파일의 경로가 절대 경로로 지정되었을 경우, 절대경로에서 파일을 찾는다. 상대 경로이거나 파일의 이름만 지정되었을 경우, 현재 사용 중인 데이타 베이스의 경로가 기준이 된다. * FIELDS TERMINATED BY ‘ ‘ ; : ex.txt 파이의 경우 문자열 간의 구분이 공백(‘ ‘)이므로 위처럼 쓰였다. 혹시 문자열 간의 구분이 TAB이라면 ‘\t’를 쓰면 된다. 혹시 ‘,’가 문자열 간의 구분 단위라면 TERMINATED BY ‘,’를 사용하면 된다. SELECT의 결과를 파일로 저장하기 위해선 SELECT .... INTO OUTFILE ... 을 쓴다. mysql> SELECT * FROM export INTO OUTFILE
‘/root/doc/mysql/out.txt’ 마찬가지로 파일의 경로를 상대 경로로 지정을 하면, 데이타 베이스가 있는 디렉터리를 기준을 삼고, 절대 경로로 지정을 하면 지정한 디렉터리에 파일이 생긴다.
8. 그 외.... 1) ALTER TABLE
mysql> ALTER TABLE Item RENAME GOODS ; Item 테이블을 GOODS로 바꾼다. mysql> ALTER TABLE export ADD comment VARCHAR(80) ; 위는 export 테이블에 comment라는 컬럼을 추가하는 예이다. mysql> ALTER TABLE export DROP COLUMN comment ; 위의 예는 export 테이블에서 comment라는 컬럼을 삭제하는 예이다. 2) DELETE FROM 테이블_이름 [WHERE 조건] 3) DROP TABLE 테이블_이름 4) SHOW - 테이블이나 컬럼에 대한 정보를
보여준다. 5) 패턴 매칭
9. 마치는 글 이번 회에서는 SQL 언어에 대해서 알아 보았다. SQL은 너무 나도 많은 분량이어서 중요한 것만을 요약하여 썼다. 이 글만 보면 부족한 점이 많으니 꼭 MySQL의 메뉴얼이나 SQL에 관한 책을 보기 바란다. 특히 SELECT와 함께 쓰이는 함수를 많이 소개 못 했는데, 매뉴얼을 보면 많은 함수들이 있다. 꼭 읽어 보기 바란다. 다음 회에는 MySQL의 C API를 이용하여 C나 C++로 짠 프로그램과 MySQL를 연동하는 방법을 알아 본다. 이 글을 읽으면서 질문이 있으면 wertyu@nownuri.net으로 메일을 보내주길 바란다. 《필자소개》 허정수님은 숭실대학교 컴퓨터학부에 재학 중이며
TEAM NNR에서 활동 중이다. |