'project/DB & SQL'에 해당되는 글 11건
- 2009.02.15 파이썬으로 만들어진 DB Designer
- 2009.01.21 MySQL ODBC 설정 및 MS Access의 테이블 내보내기
- 2009.01.08 SQLite 2
- 2008.06.28 프로세스 모델링 작성 절차
- 2008.06.28 데이터 모델링 작성절차
- 2008.05.05 인덱스 정리
- 2007.09.16 MySQL 사용법
- 2007.09.02 MySQL 강좌 Ⅲ
- 2007.09.02 MySQL 강좌 Ⅱ
- 2007.09.02 MySQL 강좌 I
- 2007.09.01 MySQL 및 Utility 설치
1. MySQL ODBC 드라이버를 설치
MySQL ODBC 드라이버는 MySQL 사이트에서 다운받을 수 있다.
2. 제어판의 관리도구를 선택하여 데이터 원본 (ODBC)을 더블클릭하면
밑의 ODBC데이터 원본 관리자가 표시된다.
3. 관리도구에서 데이터 원본 (ODBC) 를 선택
4. 사용자 DSN 또는 시스템 DSN을 선택해서 추가를 누른다.
사용자 DSN과 시스템 DSN의 차이는 아마도 여러명이 사용하는 컴퓨터일 경우에 발생할 텐데, 난 로컬호스트로 사용하고 혼자 사용하는 컴퓨터라서 시스템 DSN을 선택했다.
5. 추가를 누르고, MySQL ODBC 3.51 Driver 선택
[마침]버튼을 누르면 MySQL의 ODBC설정 창이 뜬다.
6. MySQL에 접속하기 위한 설정
Data Source Name에 DSN명을 넣고
Server는 자신의 컴퓨터에 MySQL이 설치되어 있으면 localhost를 입력한다(ODBC 설정 이전에 MySQL이 설치되어 있고, 데이터베이스가 생성되어 있어야 한다).
MySQL에 접속하기 위한 User명과 Password를 입력하고 Database의 컴보박스를 선택하면, MySQL에 만들어져 있는 데이터베이스가 표시된다. 그중에 자신이 쓸 데이터베이스를 선택한다.
위와 같이 세팅을 하고 Ok를 누르면 다음과 같이 제대로 잘 연결되었다고 나온다
이와 같이 Access 데이터를 MySQL로 포팅할 준비가 되었다면, 다음의 방법을 통해서 실제로 포팅할 수 있다.
1. Access를 열고, export하려는 테이블을 선택
2. 테이블 내보내기에서 MySQL ODBC를 선택한다.
위의 과정이 복잡하거나 어렵다면 다음의 프로그램을 사용해 보는 것도 나쁘지 않을 듯. 물론 난 안 써봤다.
http://www.bullzip.com/products/a2m/info.php
위의 설정과는 반대로, MySQL에서 직접 Access의 mdb를 ODBC를 통해서 접근하는 방법도 확인필요
MS SQL과 Python의 연동
http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html
http://www.joinc.co.kr/modules/moniwiki/wiki.php/Site/SQLite
| 1. 프로세스모델 | ||||||||||||||||||||||||||||||
| 1.1 프로세스모델의 정의 규모가 크고, 복잡한 업무를 분석할 때, 정해진 기간 내에 업무를 효율적으로 분석하여, 목표시스템에서 요구하는 기능을 만족할 수 있게 누락되거나 불필요한 업무를 골라내는 일이다. 또한 분석된 업무를 체계적으로 분류하여 목표시스템의 메뉴, 프로그램의 체계를 설계하여 품질이 우수한 시스템을 개발하는 일이 무엇보다도 중요하게 된다.
방법론을 사용하여 업무를 분석하면 업무를 체계적으로 정리하며, 누락되거나 불필요한 업무를 대부분 도출이 가능하게 됨은 물론, 데이터 모델을 작성하는 데에도 도움이 많이 된다.
주로 사용하는 프로세스 모델은 프로세스계층도(Process Hierarchy Diagram)가 있는데, 프로세스 계층도를 작성하기 위하여 간략한 설명과 작성절차 등을 설명하기로 한다. | ||||||||||||||||||||||||||||||
| 1.2 업무기능 분할(Business Function Decomposition) 업무 프로세스는 그 크기에 따라 기능영역(Function Area), 기능(Function), 프로세스(Process), 단위프로세스(Unit Process)로 상세화 수준(레벨; Level)I이 나뉘어진다. 예를 들면, 회계관리 업무는 기능영역, 재무회계관리, 세무회계관리는 기능이며 재무계획수립이나 법인세 산출은 프로세스이며, 재무제표자료 등록, 법인세 자료수정은 단위프로세스라고 할 수 있다.
프로세스계층도는 계획 단계에서부터 분석 단계까지 정보시스템화 대상이 되는 전체 업무영역을 계층적으로 표현하여 업무영역을 쉽게 이해하기 위하여 사용하는 기법이다. 사용자의 요구사항을 분석 시에 가장 어려운 문제는 업무기능을 적절하게 균형을 맞춰가며 구조적으로 분할하는 일이다.
이러한 작업은 분석자 개개인의 경험을 바탕으로 수행되고 있으나 그들의 견해와 관점의 차이로 말미암아 같은 업무를 분할한 결과가 일정하지 않고, 그 상세화 수준도 각각이다. 업무기능 분할은 대규모의 복잡한 업무영역을 분석하여 정보시스템을 구축하는데 매우 효과적인 방법이다. <그림2-1>은 기능 분할의 예이다. 통상적으로 정보시스템의 분석대상이 되는 기업이나 기관의 업무영역은 경영전략, 조직, 업무, 관련 자료로 이루어져 있다.
 <그림2-1> 업무기능분할(Business Function Decomposition)의 예 이 중에서 업무를 좀 더 상세히 설명한다면, 업무는 기업이나 기관에서 실제로 사용중인 전사 업무처리 지침서, 사업장이나 부서의 업무처리 지침서 또는 부서원별 업무분장내역, ISO 매뉴얼 등에 나타나 있으며 그러한 자료들과 현업 실무자들과의 업무 면담 내용을 가지고 업무 분석을 하게 된다.
특정 부서의 업무는 타 부서와의 From/To 업무가 있고 부서만의 고유한 업무가 있다. 회사를 구성하는 전 부서의 업무를 통합하고 분류하면 전사적인 업무 구성도가 나온다. 회사의 전체적인 업무를 업무기능영역, 하위의 서브업무기능, 그리고 서브업무기능들은 다시 업무프로세스, 그리고 다시 더 이상 나뉘어 질 수 없는 단위프로세스로 나뉘어 지게 된다. 이러한 단위프로세스는 프로그램 모듈이 되는데, 프로그램 모듈의 처리 절차는 프로시저라고 부른다. 프로세스계층도는 업무를 체계적으로 분할하는 훌륭한 도구이면서 구축될 정보시스템의 메뉴체계가 된다. 기능 분할을 하는데 있어서 바람직한 접근 방법은 최상층에서부터 분할을 시작하는 상향식 접근방식과 최하층에서 유사 기능을 묶어서 체계화해 올라오는 상향식 접근방법을 적절하게 병행하는 것이다. 이 두 가지 접근 방법을 병행하는데 있어서 바람직한 방향은 우선 하향식 접근방법에 따라 전체 기능을 기능의 최하위 계층인 단위프로세스로 구성하고 그것들을 상향식 접근방식에 의해 유사한 것끼리 집단화(Aggregation)하고 분류화(Classification)하면서 상위 프로세스를 인위적으로 만들어내는 식으로 두 가지 방법을 병행하며 결과를 보완하는 것이다. | ||||||||||||||||||||||||||||||
1.3 프로세스의 종류
| ||||||||||||||||||||||||||||||
| 1.4 분할 지침 기능 분할 작업을 수행 시에 유의해야 할 지침은 다음과 같다. 이러한 지침들은 업무를 자연스럽게 구조적으로 분할이 되게끔 하여주는 것으로서 분석가들은 명심해야 한다.
| ||||||||||||||||||||||||||||||
 | ||||||||||||||||||||||||||||||
| 2. 프로세스계층도 작성 사례 | ||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
RDBMS에서 검색속도를 높이기 사용하는 하나의 기술입니다.
INDEX는 색인입니다. 해당 TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 full scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 합니다.
이런 INDEX는 TREE구조로 색인화합니다. RDBMS 에서 사용하는 INDEX는 Balance Search Tree 를 사용합니다.실제로는 RDBMS 에서 사용되는 B-Tree 는 B-Tree 에서 파생된 B+ Tree 를 사용한다고 합니다.
참고로 ORACLE이나 MSSQL에서는 여러종류의 TREE를 선택하여 사용가능하다.
INDEX의 원리?
INDEX를 해당 컬럼에 주게 되면 초기 TABLE생성시 만들어진 MYD,MYI,FRM 3개의 파일중에서 MYI에 해당 컬럼을 색인화 하여 저장합니다. 물론 INDEX를 사용안할시에는 MYI파일은 비어 있습니다. 그래서 INDEX를 해당컬럼에 만들게 되면 해당컬럼을 따로 인덱싱하여 MYI 파일에 입력합니다. 그래서 사용자가 SELECT쿼리로 INDEX가 사용하는 쿼리를 사용시 해당 TABLE을 검색하는것이 아니라 빠른 TREE로 정리해둔 MYI파일의 내용을 검색합니다.
만약 INDEX를 사용하지 않은 SEELCT쿼리라면 해당 TABLE full scan하여 모두 검색합니다.
이는 책의 뒷부분에 찾아보기와 같은 의미로 정리해둔 단어중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을수 있는 개념과 같습니다. 만약 이 찾아보기 없다면 처음부터 끝까지 모든 페이지를 보고 찾아야 할것입니다.
INDEX의 장점?
보통 INDEX를 사용하지 않은 select쿼리와 INDEX를 사용한 쿼리의 검색속도는 6.5배가 차이납니다.이는 데이타양이 많아질수록 더욱더 차이납니다.
INDEX의 단점?
1.디스크용량 감소
인덱스를 사용하명 MYI파일에 추가 입력되기 때문에 디스크용량이 늘어납니다. 모든컬럼을 인덱스하면 데이타파일보다 인덱스파일이더 커질수도 있습니다. 적당하게 사용해야 합니다.
인덱스를 사용해도 디스크 용량은 염려할 정도로 많이 안먹으며 그에 비해 대부분의 경우는 퍼포먼스의 향상을 가져오게 되므로 좋은점이 더 많습니다.
2.INSERT,UPDATE속도 저하
해당 TABLE에 INDEX을 주게되면 INSERT,UPDATE가 조금 느려집니다. 왜냐하면 매번 해당 table과 table의 index를 검사해야 하기때문에 해당 table만 검사했을때보다 느리다.
INDEX의 목적?
RDBMS에는 INDEX가 있습니다. 인덱스의 목적은 해당 RDBMS의 검색 속도를 높이는데 있습니다.
SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때만 인덱스를 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있습니다.
※ DELETE,INSERT,UPDATE쿼리에는 해당 사항없으며 INDEX사용시 좀 느려집니다.
INDEX 를 사용해야 하는 경우
데이터 양이 많고 검색이 변경보다 빈번한 경우
인덱스를 걸고자 하는 필드의 값이 다양할 값을 가질 경우
(성별과 같이 데이터의 값의 종류가 일정한 경우 인덱스 효과 없음)
INDEX 사용시 조심할점
Index가 가해지는 필드는 가능한 Null값이 없어야 한다.
한 테이블에 5개 이상의 인덱스 적용은 권장하지 않는다.
(이화식 씨의 대용량 데이터베이스 설계 중에서)
인덱스를 사용한 필드를 조건에서 연산,가공하여 사용하면 인덱스효과는 없다.
ex : Select * from 테이블 where 인덱스필드 * 10 > 100
--> Select * from 테이블 where 인덱스필드 > 100 / 10 으로 사용
INDEX가 동작하지 않는 경우
다음 연산자는 인덱스를 타지 않는다.
not,<> 는 인덱스 사용못함(= >= <= 는 사용가능)
like '%value' 와 like '%value%'는 인덱스 사용못함(like 'like%'는 사용가능)
조건 컬럼을 가공하거나 연산을 하면 인덱스를 사용 못합니다.
문자열 타입에 인덱스를 걸경우 150 바이트 이하까지만 인덱스가 적용됩니다.
INDEX의 적용?
TABLE 생성과 동시에 해당 컬럼에 INDEX 주기
CREATE TABLE member (
idx int not null auto_increment primary key,
name varchar(10),
age varchar(10),
INDEX index1 (name),
INDEX index2 (age)
);
CREATE TABLE member (
idx int not null auto_increment primary key,
name varchar(10),
age varchar(10),
INDEX index1 (name,age)
);
설명:맨뒤에 INDEX index (name) 라고 주며 INDEX는 index생성을 말하며 index1(name)은 index명을 index1이라고 하며 사용자가 임의로 결정해 줍니다. name컬럼에 준다는 애기입니다.
ex)
레코드가 100개 있을때 explain select * from member where name='13'; 을 하면 rows가 1이 나온다.만약 index을 사용안하면 100이 나온다. 이는 검색을 위해 100개의 레코드를 검사했다는 애기입니다 rows가 1인것은 레코드를 1개 검사했다는 애기입니다.
TABLE이 생성되어있는경우 컬럼에 INDEX 삽입하기
alter table member add index index1(name);
TABLE이 생성되어있는 경우 INDEX 삭제하기
show keys from member1;
--본 쿼리로 인덱스명 확인
drop table member1 index drop key_name;
--인덱스명으로 index삭제(컬럼명 아님)
desc member1;
--key컬럼을 보고 MUL(index지정) 삭제되었나 확인
INDEX의 주의점?
1.인덱스가 해당 컬럼에 줄때는 반드시 not null이어야 한다.
INDEX의 사용(varchar)?
CREATE TABLE member1(
idx int not null auto_increment primary key,
name varchar(10),
age int,
INDEX index1 (name),
INDEX index2 (age)
);
insert into member1 (name,age) values ('11',11);
insert into member1 (name,age) values ('12',12);
insert into member1 (name,age) values ('13',13);
insert into member1 (name,age) values ('14',14);
CREATE TABLE member2(
idx int not null auto_increment primary key,
name varchar(10),
age int,
INDEX index1 (name,age)
);
insert into member2 (name,age) values ('11',11);
insert into member2 (name,age) values ('12',12);
insert into member2 (name,age) values ('13',13);
insert into member2 (name,age) values ('14',14);
CREATE TABLE member3(
idx int not null auto_increment primary key,
name varchar(10),
age int
);
insert into member3 (name,age) values ('11',11);
insert into member3 (name,age) values ('12',12);
insert into member3 (name,age) values ('13',13);
insert into member3 (name,age) values ('14',14);
explain select * from member1 where name ='13';
yes
explain select * from member2 where name ='13';
yes
explain select * from member3 where name ='13';
no
explain select * from member1 where age >10;
yes
explain select * from member2 where age >10;
no
explain select * from member3 where age >10;
no
설명 : 위와같이 age가 설정되면 index로 설정되면 age의 순서에 따라 정렬된 index를 가지기 때문에 레코드수의 증가와 거의 관계없이 query시간이 일정합니다. 그리고 당연히 order by age와 같이 정렬이 필요한 경우에도 따로 sort해줄 필요 없기 때문에 퀘리 시간이 짧아집니다.
explain select * from member1 where name ='13' and age =13;
yes
explain select * from member2 where name ='13' and age =13;
yes
explain select * from member3 where name ='13' and age =13;
no
explain select * from member1 where age =13;
yes
explain select * from member2 where age =13;
no
이유 : INDEX index1 (name,age)로 선언된 인덱스는 name,age의 값이 concate된 값이
index로 작용합니다.name이란 필드하나는 index의 역활을 하지 못합니다.
explain select * from member3 where age =13;
no
explain select * from member1 order by age;
no
explain select * from member2 order by age;
no
explain select * from member3 order by age;
no
explain select * from member1 where name='12' order by age;
yes
explain select * from member2 where name='12' order by age;
yes
explain select * from member3 where name='12' order by age;
no
explain select * from member1 where name like '12%' order by age;
yes
explain select * from member2 where name like '12%' order by age;
yes
explain select * from member3 where name like '12%' order by age;
no
explain select * from member1 where name like '%12' order by age;
explain select * from member1 where name like '%12%' order by age;
no
explain select * from member2 where name like '%12' order by age;
explain select * from member2 where name like '%12%' order by age;
no
explain select * from member3 where name like '%12' order by age;
explain select * from member3 where name like '%12%' order by age;
no
explain select * from member1 where name like '12%' or age =12 order by age;
no
explain select * from member2 where name like '12%' or age =12 order by age;
no
explain select * from member3 where name like '12%' or age =12 order by age;
no
INDEX의 사용(int,char,date)?
create table member(
idx int auto_increment primary key,
name char(20),
age int,
day date,
index index1(name),
index index2(age),
index index3(day)
);
insert into member values(null,'junsik1',11,date_add(now(),interval 1 day));
에서 차례대로
insert into member values(null,'junsik8',18,date_add(now(),interval 8 day));
까지 입력한다.
explain select * from member where day between '2005-07-19' and '2005-07-23';
index를 타지만 풀스캔을 한다.
explain select * from member where day = '2005/07/17'
yes
explain select * from member where name in('junsik1','junsik2');
yes
explain select * from member where age in('12','13','14');
no
explain select * from member where age between 12 and 14;
yes
explain select * from member where age < 14;
yes
3.1 중요 실행 파일과 데몬
- 중요 실행 파일
mysql - SQL 쉘
- client 프로그램
- mySQL 에 접속하여 쿼리를 수행하는 프로 그램
mysqladmin - MySQL을 관리하는 프로그램
- 데이터베이스의 생성, 삭제, 권한 설정 테이블을 읽어들이는 일을 수행
mysqld - MySQL 데몬
mysqlshow - MySQL 안에 있는 데이터베이스와 테이블 과 컬럼을 표시해주는 프로그램
safe_mysqld - MySQL을 안전하게 실행시키는 스크립트 프로그램 파일
isamchk - 테이블에 문제가 발생할 경우 수정하고, 테이블을 최적화 하는 프로그램
- 데몬을 수행 시키는 방법
% safe_mysqld 데몬을 안전하게 수행 % mysqld 데몬을 수행, 디폴트 포드 : 3306 % mysqld -P 333 포트 번호를 지정하여 데몬을 수행 - 부팅시 데몬을 자동수행시키는 방법
- /etc/rc.d/rc.local 파일에 등록
- rpm으로 설치한 경우 자동 등록
3.2 권한 설정
- MySQL을 처음 설치하면 root 만이 MYSQL 을 사용가능하도록 권한이 설정됨
- 현재 상태에서 설정된 데이타베이스를 확인 :mysqlshow 명령 사용
% mysqlshow -u root +-----------+ | Databases | +-----------+ | mysql | | test | +-----------+
- " -u root " : root 가 사용 한다는 옵션
- 최초 설치시 : mtsql과 test 두개의 데이블이 존재 - mysql 데이타베이스의 테이블 확인 : mysqlshow 명령 사용
% mysqlshow -u root mysql Database: mysql +--------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--------------+
- user 테이블이 사용자 권한에 관한 테이 블임
- user 테이블의 레코드 확인
% mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 3.22.22 Type 'help' for help. mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user; +-----------+------+- ---------+-------------+-------------+-------------+------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delet| +-----------+------+----------+-------------+-------------+-- ---+------+ | localhost | root | | Y | Y | Y | Y | | orange | root | | Y | Y | Y | Y | | localhost | | | N | N | N | N | | orange | | | N | N | N | N | +-----------+------+----------+-------------+-------------+---+ 4 rows in set (0.01 sec) mysql>
- "*.priv" 컴럼: 사용 권한에 대한 설정, Y 이면 권한을 가짐을, N이면 권한이 없음을 의미
- "Password" 컬럼 : 암호의 설정 상태를 의미, 현제 root는 암호가 없음 - root 사용자에 대하여 암호를 설정
mysql> update user set password = password('apple1234') => 암호 설정 - > where user='root' ; Query OK, 2 rows affected (0.13 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> quit => 종료 Bye % mysqladmin -u root reload => 권한이 변경된 경우 변경된 권한을 다시 읽어 들임 % mysql -u root -p => 암호가 있는 경 우 -p 옵션을 붙여서 mysql을 수행 Enter password: XXXXXXXXXX Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 3.22.22 Type 'help' for help. mysql> select * from user; +-----------+------+------------------+-------------+--------+-- ----------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv| +-----------+------+------------------+-------------+-------+-- ----------+ | localhost | root | 32b157395f7543c0 | Y | Y | Y | | orange | root | 32b157395f7543c0 | Y | Y | Y | | localhost | | | N | N | N | | orange | | | N | N | N | +-----------+------+------------------+-------------+-------+-- ----------+ 4 rows in set (0.01 sec) mysql>
3.3 사용자 추가
- "INSERT .. INTO" 명령을 이용 해서 mysql 데이타베이스의 user 테이블에 추가
% mysql -u root -p mysql Enter password:XXXXXXXXXX Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 to server version: 3.22.22 Type 'help' for help. mysql> insert into user values('localhost', 'wiseo', ==> 사용자 wiseo에게 모든 권한 -> password('apple2000'), 'Y', 'Y', 'Y', 'Y', -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', -> 'Y', 'Y'); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +-----------+-------+------------------+-------------+----- ----------+ | Host | User | Password | Select_priv | Insert_priv | Update_pri| +-----------+-------+------------------+-------------+-----+- ----------+ | localhost | root | 32b157395f7543c0 | Y | Y | Y | | orange | root | 32b157395f7543c0 | Y | Y | Y | | localhost | | | N | N | N | | orange | | | N | N | N | | localhost | wiseo | 6da73b5d2ab9ea69 | Y | Y | Y | +-----------+-------+------------------+-------------+---+- ----------+ 5 rows in set (0.01 sec) mysql>- 권한을 부여 하고자 한다면 해당 컬럼 의 값을 'Y'로 아니면 'N'으로 설정
- SELECT, INSERT, DELETE에 대한 권한만 주고자 한다면
mysql> insert into user values('localhost', 'wiseo', -> password('apple2000'), 'Y', 'Y', 'Y'); Query OK, 1 row affected (0.01 sec) mysql>
- "GRANT" 명령을 이용해서 mysql 데이타베이스의 user 테이블에 추가
mysql> grant all priviledges on *.* to wiseo@localhost ==> 사용자 wiseo에게 모든 권한 -> identified by 'apple2000' with grant option; Query OK, 0 rows affected (0.01 sec) mysql>
- "GRANT" 명령을 이용해서 RELOAD와 PROCESS 권한 만을 설정
mysql> grant reload,process on *.* to wiseo@localhost -> identified by 'apple2000' with grant option; Query OK, 0 rows affected (0.01 sec) mysql> - 권한을 변경한 경우 반드시 권한을 적용 하여야 함(2가지 방법)
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
% mysqladmin -u root -p reload Enter password: XXXXXXXX
3.4 암호
- root 암호를 잊어버린 경우
- MySQL 데몬의 실행을 중지
- 권한에 대한 검사를 하지 않고 데몬을 실행 : safe_mysql -Sg &
- mysql을 사용하여 암호를 제거
- mysqladmin reload를 이용해서 변경된 권한을 적용하고, 데몬을 다시 수행 - 암호의 저장
- 암호가 설정된 경우 모든 명령을 사용하기 위해서는 -p 옵션을 붙여 야 하고, 암호를 입력하여야 함
- 암호 입력을 생략하려면 홈디렉토리에 .my.cnf 파일을 다음과 같이 만들어야 함.% cat > .my.cnf [client] password=암호 ^D % chmod 600 .my.cnf => 다른 사람이 보지 못하도록 파일 접근 권한을 설정 %
3.5 컬럼의 유형
- 컬럼(Column)
- 데이타베이스를 구성 하는 최소 단위
- 컬럼들이 모여서 테이블(Table)을 구성 - 컬럼의 유형
TYNYINT - 부호 있는 정수 -128 ~ 127
- 부호 없는 정수 0 ~255
- 1 Byte
SMALLINT - 부호 있는 정수 -32768 ~ 32767
- 부호 없는 정수 0 ~65535
- 2 Byte
MEDIUMINT - 부호 있는 정수 -8388608 ~ 8388607
- 부호 없는 정수 0 ~16777215
- 3 Byte
INT 또는 INTEGER - 부호 있는 정수 -2147483648 ~ 2147483647
- 부호 없는 정수 0 ~4294967295
- 4 Byte
BIGINT - 부호 있는 정수 - 9223372036854775808 ~ 9223372036854775807
- 부호 없는 정수 0 ~18446744073709551615
- 8 Byte
FLOAT - 단일 정밀도를 가진 부동 소수 점
- -3.402823466E+38 ~3.402823466E+38
DOUBLE - 2 배 정밀도를 가진 부동 소수 점
- -1.79769313486231517E+308 ~ 1.79769313486231517E+308
DATE - 날짜를 표현하는 유형
- 1000-01-01 ~ 9999-12- 31
DATETIME - 날짜와 시간을 표현하는 유형
- 1000-01-01 00:00:00 ~ 9999- 12-31 23:59:59
TIMESTAMP - 1970-01-01 00:00:00 부터 2037년 까지 표현
- 4 Byte
TIME - 시간을 표현하는 유형
- -839:59:59 ~ 838:59:59
YEAR - 년도를 표현하는 유형
- 1901 년 ~ 2155년
CHAR(M) - 고정길이 문자열을 표현하는 유형
- M = 1 ~255
VARCHAR(M) - 가변길이 문자열을 표현하는 유형
- M = 1 ~ 255
TINYBLOB
TINYTEXT- 255개의 문자를 저장
- BLOB : BINARY LARGE OBJECT 의 약자
BLOB
TEXT- 63535개의 문자를 저장
MEDIUMBLOB
MEDIUMTEXT- 16777215개의 문자를 저장
LONGBLOB
LONGTEXT- 4294967295(4Giga)개의 문자 를 저장
3.6 데이타베이스 생성
- mysqladmin을 이용하는 방법
% mysqladmin create super Database "guestbook" created. % mysqlshow +-----------+ | Databases | +-----------+ | mysql | | guestbook | | test | +-----------+ %
- mysql을 이용하는 방법
% mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 3.22.22 Type 'help' for help. mysql> create datatbase guestbook; Query OK, 1 row affected (0.01 sec) mysql> show databases; +-----------+ | Databases | +-----------+ | mysql | | guestbook | | test | +-----------+ Query OK, 1 row affected (0.01 sec) mysql>
3.7 테이블 생성
- 테이블의 예 : 고객 테이블
테이블 명 : guest 컬럼 명 : 일련번호(no) -> 자동으로 부여되는 일련 번호, 키 이름(name) 나이(age) 전화번호(phone) 전자우편주소(email) 주소(address) - 테이블 생성 방법 : mysql 명령 모드에 서 CREATE 문을 사용
CREATE TABLE 테이블명 (컬럼의 형 식) ; - 테이블 생성 예제
% mysql mysql> use guestbook; Database changed mysql> CREATE TABLE guest(no INT PRIMARY KEY NOT NULL AUTO_INCREMENT, -> name CHAR (20), age TINYINT, phone VARCHAR(20),email VARCHAR (30), -> address VARCHAR(50)); Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; => 생성 된 테이블을 확인 +-------------------- -+ | Tables in guestbook | +---------------------+ | guest | +---------------------+ 1 row in set (0.00 sec) mysql> - PRIMARY KEY : 주 키로 설정
- NOT NULL : 반드 시 값을 가져야 함을 의미
- AUTO_INCREMENT : 레코드가 삽입될 때 마다 자동으로 값을 증가
3.8 레코드 삽입
- 삽입할 레코드 정보
no
name
age
phone
email
address
1
홍길동
20
510-0001
부산
2
김유신
22
510-1004
경주
3
강감찬
23
510-9000
대구
4
이순신
21
560-8000
여수
5
임꺽정
30
800-7000
대전
6
유관순
21
700-5555
서울
7
윤봉길
28
288-5757
인천
8
안중근
33
566-4545
수원
9
박문수
25
767-9090
충주
10
정철수
55
777-9999
광주
- 레코드 삽입 방법 : mysql 명령 모드에 서 INSERT 문을 사용
INSERT INTO 테이블명 VALUES(컬럼의 데 이터 값...) ; - 레코드 삽입 예제:
mysql> INSERT INTO guest VALUES(1,'홍길 동',20,'510-0001', -> 'hong@jisan.ac.kr','부산'); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO guest VALUES(NULL,'김유신',22,'510-1004', -> 'kim@jisan.ac.kr','경 주'); Query OK, 1 row affected (0.05 sec) (중략) mysql> select * from guest; +----+--------+------+----------+------------------+---------+ | no | name | age | phone | email | address | +----+--------+------+----------+------------------+---------+ | 1 | 홍길동 | 20 | 510-0001 | hong@jisan.ac.kr | 부산 | | 2 | 김유신 | 22 | 510-1004 | kim@jisan.ac.kr | 경주 | | 3 | 강감찬 | 23 | 510-9000 | gang@jisan.ac.kr | 대구 | | 4 | 이순신 | 21 | 560-8000 | lee@jisan.ac.kr | 여수 | | 5 | 임꺽정 | 30 | 800-7000 | lim@jisan.ac.kr | 여수 | | 6 | 유관순 | 21 | 700-5555 | you@jisan.ac.kr | 서울 | | 7 | 윤봉길 | 28 | 288-5757 | youn@jisan.ac.kr | 인천 | | 8 | 안중근 | 33 | 566-4545 | ann@jisan.ac.kr | 수원 | | 9 | 박문수 | 25 | 767-4545 | park@jisan.ac.kr | 충주 | | 10 | 정철수 | 55 | 777-9999 | jung@jisan.ac.kr | 광주 | +----+--------+------+----------+------------------+---------+ 10 rows in set (0.00 sec) mysql>
3.9 레코드 수정
- 레코드 수정 방법 : mysql 명령 모드에 서 UPDATE 문을 사용
UPDATE 테이블명 SET 컬럼명=식 ,... [ WHERE 조건 ]; - 레코드 수정 예제:
mysql> Update guest SET address='대전' where name='임꺽정'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from guest where name='임꺽정'; +----+--------+------+----------+-----------------+---------+ | no | name | age | phone | email | address | +----+--------+------+----------+-----------------+---------+ | 5 | 임꺽정 | 30 |800-7000 | lim@jisan.ac.kr | 대전 | +----+--------+------+----------+-----------------+---------+ 1 row in set (0.00 sec) mysql>
3.10 레코드 검색
- 검색 조건
- guest 테이블의 모든 레코드 검색하여 이 름과 나이 필드만 출력
- guest 테이 블의 모든 레코드 검색하여 25살 이하인 사람들의 이름만 출력
- guest 테이블의 모든 레코드를 나 이가 큰 순서로 정렬하여 이름과 나이 필드만 출력
- 전체 나이의 평균 출력
- 레코드 검색 방법 : mysql 명령 모드에 서 SELECT 문을 사용
SELECT 컬럼명 [AS 구문] ,.. FROM 테이블명 [WHERE 조건] [ORDER BY 정렬기준 컬럼 ACS|DESC ] [GROUP BY 구룹화 기준 컬럼 ];
- 레코드 검색 예제:
mysql> SELECT name,age from guest; +--------+------+ | name | age | +--------+------+ | 홍길동 | 20 | | 김유신 | 22 | | 강감찬 | 23 | | 이순신 | 21 | | 임꺽정 | 30 | | 유관순 | 21 | | 윤봉길 | 28 | | 안중근 | 33 | | 박문수 | 25 | | 정철수 | 55 | +--------+------+ 10 rows in set (0.00 sec) mysql> SELECT name,age from guest where age <= 25 ; +--------+------+ | name | age | +--------+------+ | 홍길동 | 20 | | 김유신 | 22 | | 강감찬 | 23 | | 이순신 | 21 | | 유관순 | 21 | | 박문수 | 25 | +--------+------+ 6 rows in set (0.00 sec) mysql> SELECT name,age from guest ORDER BY age DESC; +--------+------+ | name | age | +--------+------+ | 정철수 | 55 || 안중근 | 33 | | 임꺽정 | 30 | | 윤봉길 | 28 | | 박문수 | 25 | | 강감찬 | 23 | | 김유신 | 22 | | 이순신 | 21 || 유관순 | 21 | | 홍길동 | 20 | +--------+------+ 10 rows in set (0.01 sec) mysql> SELECT AVG (age) from guest ; +----------+ | AVG(age) | +----------+ | 27.8000 | +----------+ 1 row in set (0.07 sec) mysql> SELECT AVG (age) AS '평균 연령' from guest ; +-----------+ | 평균 연령 | +-----------+ | 27.8000 | +-----------+ 1 row in set (0.01 sec) mysql>
3.12 파일로 부터 레코드 입력받기와 결과를 파일 로 저장하기
3.12.1 파일에서 데이터를 테이블로 읽어 들이기
- 방법 : mysql 명령 모드에서
LOAD DATA INFILE '파일 경로명' INTO TABLE 테이블명 FIELDS TERMINATED BY '필드구분 자';
- 예제 :
% cat > guest.txt NULL,서태지,22,666- 9999,seo@jisan.ac.kr,진주 NULL,신승훈,33,999-6666,shin@jisan.ac.kr,청주 % mysql guestbook Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 to server version: 3.22.22 Type 'help' for help. mysql> LOAD DATA INFILE '/home/prof/wiseo/guest.txt' INTO TABLE guest -> FIELDS TERMINATED BY ',';=> 파일명은 반드시 절대경로명 Query OK, 2 rows affected (0.03 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 2 mysql>
3.12.2 테이블에서 데이터를 검색하여 파 일로 출력하기
- 방법: mysql 명령 모드에서
SELECT 문 INTO OUTFILE '출력파일 의 경로명';
- 예제 :
mysql> SELECT * from guest where age <= 25 -> INTO OUTFILE 'out.txt' -> FIELDS TERMINATED BY ',' ; => 상대경로명을 사용하면 기준 은 DB가 존재하는 디렉토리 mysql>
3.13 테이블의 이름 변경과 컬럼 구조 변경(ALTER 문)
3.13.1 테이블 이름 변경
- 방법: mysql 명령 모드에서
ALTER TABLE 테이블명 RENAME 새테 이블명 ;
- 예제
3.13.2 테이블 컬럼 구조 변경
- 방법: mysql 명령 모드에서
ALTER TABLE 테이블명 ADD 컬럼명 컬럼 유형; => 새로 운 컬럼을 추가 ALTER TABLE 테이블명 DROP COLUMN 컬럼명; => 컬럼을 삭제
- 예제
3.13 테이블의 삭제
- 방법: mysql 명령 모드에서
DROP TABLE 테이블 명 ;
허정수<나우누리:wertyu>
1. 들어 가는 말
지난 호에서는 MySQL을 이용하여 간단하게나마 슈퍼 마켓에서 매출을 관리하는 예를 들어 SQL 언어에 대해서 간단히 살펴 보았다.
기본적인 SQL 언어만 알아도 MySQL의 Client 프로그램을 이용하여 Data들을 관리할 수 있다. 하지만, SQL 언어를 모르거나 컴퓨터를 잘 다루지 못하는 사람들은 Database를 어떻게 쓸 수 있을까. 우리네 슈퍼마켓 아저씨들 중에서 SQL 언어를 사용해서 매출을 관리할 수 있을 아저씨들이 얼마나 있을까.
이번 호에서는 MySQL의 C API에 대해서 알아본다. MySQL의 C API를 이용하면 C나 C++로 짠 프로그램에서 MySQL 서버에 접속을 할 수 있다. (우리네 슈퍼마켓 아저씨는 어려운 SQL 언어를 배울 필요가 없다. 슈퍼마켓 아저씨를 낮춰보는 것이 아니므로 항의하는 일이 없었으면 한다.)
MySQL과 연동하는 방법은 C API만이 전부는 아니고, PHP3, Perl, MyODBC, JDBC를 이용하여 연동을 할 수 있다. 이번 회에서는 C API에 대해서 알아보도록 하고, 다음 회에서 PHP3와 MySQL을 이용하여 Web과 연동하는 법에 대해 알아 보도록 한다.
2. 프로그램 컴파일
C API를 소개하기 전에 일단 gcc의 옵션을 잘 모르는 독자를 위해 MySQL의 C API를 이용한 소스 코드를 컴파일하는 방법에 대해서 알아 본다.
먼저 다음의 예제를 보자.
#include <stdio.h>
#include <mysql.h>
#include <errno.h>
void main(void)
{ MYSQL mysql ;
mysql_init(&mysql) ;
if(!mysql_real_connect(&mysql, NULL, “사용자이름”,”암호”, NULL ,3306, (char *)NULL, 0))
{printf(“%s＼n”,mysql_error(&mysql));
exit(1) ;}
printf(“성공적으로 연결되었습니다.＼n”) ;
mysql_close(&mysql) ;}
위의 예제는 MySQL 서버에 접속을 하여, 연결이 제대로 되었을 경우 ‘성공적으로 연결되었습니다.’라는 메시지를 출력하고 종료하는 프로그램이다. 연결이 안 될 경우 왜 연결이 안 되는지 출력을 하게 된다. 에러가 나면 글의 후반부에 있는 ‘문제 해결’ 부분을 읽어 보길 바란다. 일단 위에 나온 함수들의 설명은 뒤에 하고 컴파일을 하는 방법에 대해 알아 보자.
위의 소스 코드가 con.c라고 가정을 한다면 다음과 같이 프롬프트에서 명령을 내리면 된다.
$ gcc -o con con.c -I/usr/local/include/mysql
-L/usr/local/lib/mysql -lmysqlclient
이렇게 하면 con이라는 실행 파일이 생기게 된다. -I 옵션은 헤더 파일의 경로를 정한다.
즉, mysql.h의 경로를 지정해 주며, 필자의 경우 그 경로가/usr/local/include/mysql이다. 각자 경로가 틀리므로 자신에게 맞게 변경을 하자. 보통 /usr/include/mysql 혹은/usr/local/include/mysql 혹은 /usr/include에 존재한다. /usr/include에 있을 경우에는 이 옵션을 주지 않아도 된다.
-l 옵션은 링킹시 사용할 라이브러리를 지정한다.
MySQL의 C API를 사용했을 경우에는 꼭 -lmysqlclient라는 옵션을 주어야 한다.
-L/usr/local/lib/mysql 옵션은 mysqlclient라는 라이브러리의 경로를 지정한다. 이 옵션도 사용자에 따라 다르다. MySQL의 설치 시에 어디에 라이브러리를 설치했는지 확인하자. 지정된 디렉터리 밑에서 libmysqlclient.so 라는 라이브러리를 볼 수 있다.
옵션과 경로를 잘 지정해 주었다면 위의 소스 코드는 잘 컴파일 되고, 실행시에 MySQL 데몬이 실행 중이라면 ‘성공적으로 연결되었습니다.’라는 메시지를 볼 수 있을 것이다.
3. C API 자료형
C API에서 쓰이는 자료형에 대해서 알아 보자.
MYSQL : Database와의 연결을 관리하는 구조체이다. 대부분의 C API 함수에서 쓰인다.
MYSQL_RES : SELECT 등 결과를 리턴하는 query의 결과를 나타내는 자료형이다.
MYSQL_ROW : MYSQL_RES에서 하나의 레코드씩 값을 얻어 올때 쓰이는 자료형이다.
MYSQL_FIELD : 필드의 이름과 필드의 타입 등 필드에 관한 정보를 저장하는 자료형이다.
4. MySQL Database에 연결하기
이제 모든 준비를 마췄으므로 본격적으로 MySQL C API에 대해서 알아보자.
* MYSQL* mysql_init(MYSQL *mysql)
mysql_real_connect()를 위하여 MYSQL 객체를 초기화한다. 특별히 하는 일은 없으나 MYSQL 객체를 초기화 하므로 mysql_real_connect() 전에 꼭 호출하여 주자.
* MYSQL* mysql_real_connect(MYSQL* mysql, const char* host, const char* user,
const char* passwd, const char* db, uint port, const char* unix_socket, uint client_flag)
host에 지정된 서버로 연결을 시도하는 함수이다.
mysql_get_client_info() 함수를 제외한 모든 API 함수를 사용하기 전에 꼭 호출하여야 하는 함수이다.
각 인자에 대한 설명은 다음과 같다.
mysql : MYSQL 변수에 대한 포인터 형이다.
host : 연결하고자 하는 서버의 IP Address 혹은 도메인 이름을 적어주면 된다.
NULL로 적어주면 localhost를 의미한다.
user : 접속시의 사용자 이름이다. NULL이면 현재 login한 user ID가 된다.
passwd : user의 암호를 나타낸다. NULL이면 암호가 없다는 의미이다.
db : 접속시에 사용하고자 하는 database를 나타낸다. NULL로 지정을 하면 연결 후에
mysql_select_db() 혹은 mysql_query()를 이용해서 지정할 수 있고,
database를 바꿀 수도 있다.
port : TCP/IP 연결시에 사용할 포트 번호를 나타낸다.
unix_socket : 보통 NULL로 하면된다.
client_flag : 이 인자도 보통 0으로 해주면 된다.
mysql_real_connect()는 성공적으로 연결이 되면, MYSQL 포인터를 넘겨주고 연결에 실패하였을 경우 NULL을 리턴한다.
연결에 실패 하였을 경우 이 글이 마지막에 있는 ‘문제 해결’부분을 읽어 보길 바란다. 사용 예는 위의 예제에 나와있다.
* void mysql_close(MYSQL* mysql)
서버와의 연결을 끊고 mysql에 할당되었던 메모리를 해제한다.
5. Query와 결과 값을 얻어 오기
서버와 성공적으로 연결이 되었다면, 이제 원하는 Query를 하고, 그 결과 값을 얻어 올 수 있다. Query를 할 수 있는 함수는 mysql_query()와 mysql_real query() 두 가지가 있다.
* int mysql_query(MYSQL* mysql, const char* query) :
query를 실행 시킨다. mysql 클라이언트에서 했던 것 처럼 query의 끝에 ‘;’가 포함되어서는 안 된다. query의 끝은 NULL 문자(‘\0’)이다. 따라서 바이너리 데이타가 섞인 query는 수행을 할 수 없다. 바이너리 데이타가 섞은 query의 중간에는 NULL 문자가 올 수도 있기 때문이다. query를 성공적으로 마췄다면 0을 리턴한다.
* int mysql_real_query(MYSQL* mysql, const char* query, unsigned int length) :
mysql_query()는 query의 끝을 NULL 문자로 구분을 하는데 반해, mysql_real_query는 query의 끝을 length 만큼의 길이로 구분한다. 따라서 NULL 문자를 포함한 바이너리 데이타가 있는 query도 수행을 할 수 있다. 또한mysql_query()는내부적으로strlen()를 사용하지만, mysql_real_query()는 그렇지 않으므로 좀더 빠르다. 마찬가지로 query를 성공적으로 수행하였을 경우 0을 리턴한다.
위의 함수를 이용하여 query를 수행할 수가 있다. 호출하는 방법은 매우 간단하다.
mysql_query(&mysql, “SELECT * FROM dome”) ;
위와 같이 query를 수행하면 된다.
query를 성공적으로 수행했다면, 이제 결과 값을 얻어 와야 한다.
* MYSQL_RES* mysql_store_result(MYSQL* mysql)
* MYSQL_RES* mysql_use_result(MYSQL* mysql)
위 두 함수는 모두 서버로부터 결과 값을 얻어 오는데, 차이는 query의 결과로 리턴되는 ROW들을 한꺼번에 모두 서버로부터 얻어 올 것인지(mysql_store_result()), 혹은 한번에 한 개의 ROW를 얻어 올 것(mysql_use_result())의 차이이다.
mysql_store_result()는 ROW들을 한 꺼번에 모두 얻어와 클라이언트의 메모리에 저장을 한다. 따라서 매번 ROW를 얻어 오기 위해 서버에 접근을 할 필요가 없으므로 속도가 빠르다. 대신 결과로 넘어온 ROW의 크기가 클 경우 많은 메모리가 필요하게 된다.
또 mysql_store_result()의 장점은 mysql_data_seek()나 mysql_row_seek()를 이용하여 현재 ROW에서 앞이나 뒤의 ROW로 자유자재로 왔다 갔다 할 수 있다는 것이다. 또한, mysql_num_rows()를 이용하여, 몇 개의 ROW가 리턴됐는지도 알 수 있다.
반면 mysql_use_result()는 한 번에 한 개의 ROW를 서버로부터 가져 온다. 따라서 메모리를 많이 사용하지 않는다. 하지만, mysql_store_result()와 같은 장점을 가지 지 않는다. 리턴되는 ROW가 특별히 크지 않은 경우라면, 보통 mysql_store_result()를 호출하는 것이 좋다.
결과 값을 얻어 왔다면 mysql_fetch_row()를 이용하여 각각의 row에 있는 데이타 들에 접근을 할 수 있다.
MYSQL_ROW mysql_fetch_row(MYSQL_ROW* result)
result에 있는 ROW들에서 한 개의 ROW를 얻어 온다. 한 개의 ROW에서 각각의 field는 배열 형태로 들어 있다. 더 이상 가져올 ROW가 없으면 NULL을 리턴한다.
현재의 result에 몇 개의 field가 있는지 mysql_num_fields()를 이용하여 알 수 있다.
int mysql_num_fields(MYSQL_RES* result)
이제 실제로 query를 수행하고, 결과를 출력하여 보자.
1: #include <stdio.h>
2: #include <mysql.h>
3: #include <errno.h>
4:
5: void main(void)
6: {
7: MYSQL mysql ;
8: MYSQL_RES* res ;
9: MYSQL_ROW row ;
10: int fields ;
11:
12: mysql_init(&mysql) ;
13:
14: if(!mysql_real_connect(&mysql, NULL, “사용자”,”암호”, “test”, 3306, (char *)NULL, 0))
15: {
16: printf(“%s＼n”,mysql_error(&mysql));
17: exit(1) ;
18: }
19:
20: if(mysql_query(&mysql, “USE super”) )
// mysql_query()는 query 수행시에 에러가 나게 되면
// 0이 아닌 값을 리턴한다.
{
printf(“%s＼n”, mysql_error(&mysql) ;
exit(1) ;
}
21: if(mysql_query(&mysql, “SELECT * FROM dome”) )
{
printf(“%s＼n”, mysql_error(&mysql) ;
exit(1) ;
}
22:
23: res = mysql_store_result( &mysql ) ;
24: fields = mysql_num_fields(res) ;
25:
26: while( ( row = mysql_fetch_row( res ) ))
27: {
28: for( cnt = 0 ; cnt < fields ; ++cnt)
29: printf(“%12s “, row[cnt]) ;
30:
31: printf(“＼n”) ;
32: }
33:
34: mysql_free_result( res ) ;
35: mysql_close(&mysql) ;
36: }
12번 줄은 mysql_init()을 이용하여 MYSQL 객체를 초기화 하는 과정이다.
14번 줄은 실제 MySQL 서버와 연결을 시도하는 부분이다. 연결에 실패하였을 경우 NULL을 리턴하므로 15-18번 줄에서, 에러 메시지를 출력하고 프로그램을 종료한다.
20번 줄은 “use super”라는 query를 이용하여 ‘super’ database를 사용하도록 지정하는 부분이다. 사용하고자 하는 database는 mysql_real_connect()에서 지정을 할 수도 있고, 프로그램 실행 중에 바꾸고자 할 때는 “use DB이름”과 같은query를 이용할 수도 있고, 혹은 mysql_select_db()를 이용할 수도 있다.
21번 줄은 “SELECT * FROM dome” query를 수행하는 부분이다. dome 테이블의 모든 컬럼을 리턴하도록 하였다.
23번 줄은 mysql_store_result()를 이용하여 결과 값을 저장하는 부분이다.
24번 줄은 mysql_num_fields()를 이용하여 현재 결과 값에 몇 개의 필드가 있는지 알아 내는 부분이다.
mysql_fetch_row()는 배열 형태로 각 필드에 접근을 하므로 배열의 인덱스를 알아내야 정확히 데이타들을 얻어 올 수 있다.
26-32번 줄은 실제로 각 ROW들로부터 데이타에 접근하는 부분이다.
mysql_fetch_row()를 이용하여 각각의 ROW를 얻어 온다(더이상 가져올 ROW가 없으면 NULL을 리턴한다.) 그 후 for문을 이용하여 ROW의 각각 필드를 출력하였다.
34-35번 줄은 res와 mysql에 할당된 메모리를 해제하는 부분이다. 하지만, query에 따라서는 ROW를 리턴하지 않는 query도 있다(UPDATE나 DELETE등) 만약 위의 예제에서 사용자가 UPDATE 등을 수행하였다면, 곧바로 Segmentation Fault가 나버린다.
이때는 mysql_num_fields()를 이용하여, 필드의 개수를 조사함으로써 문제를 해결할 수 있다. query가 ROW를 리턴하였다면, 필드는 최소한 0보다 크기 때문이다. 다음의 예제를 보자(참고로 밑의 예제는 MySQL Manual 19장에서 발췌한 내용이다.)
MYSQL_RES* result ;
unsigned int num_fields ;
unsigned int num_rows ;
if( mysql_query(&mysql, query_string) )
{
// mysql_query는 성공적으로
// 수행할 경우 0을 리턴하므로
// 이 부분이 실행 된다면
// 에러가 있다는 의미이다.
// 적절한 에러 핸들링을 해주다.
}
else // 성공적으로 query가 수행되었다.
{
result = mysql_store_result( &mysql ) ;
if( result ) // 리턴된 ROW가 있다 !
{
num_fields = mysql_num_fields( result ) ;
// row의 값을 얻어오는 루틴을 집어 넣으면 된다.
// 마지막에는 mysql_free_result()를 이용하여
// 메모리를 해제시키자.
}
else // 리턴된 ROW가 없다. ROW를 리턴하지 않는 query를 수행하였는지 // 혹은 query의 수행 중에 에러나 났는지 알 수 없다.
{
if( mysql_num_fields( &mysql ) == 0)
{
// ROW를 리턴하지 않는 query를 수행하였다.
num_rows = mysql_affected_rows( &mysql ) ;
// mysql_affected_rows()는 DELETE 등의
// query에서 몇 개의 ROW가 영향을 받았는지
// 리턴하는 함수이다.
}
else // 무언가 잘못된 것이 있다.
fprintf(“stderr,Error : %s＼n”,mysql_error( &mysql) ) ;
// mysql_error()는 가장 최근의 에러를 리턴하는 함수이다.
}
}
위의 예제를 이용하여, 결과를 리턴하는 query 든지(SELECT), 아니면 결과를 리턴하지 않는 query 든지(UPDATE, DELECT 등) 어떠한 query라도 수행을 성공적으로 할 수 있다.
이번에는 ROW의 필드에 대한 정보를 얻어 오는 방법에 대해서 알아 보자. MYSQL_FIELD라는 구조체가 쓰이는데 MYSQL_FIELD에는 다음과 같은 멤버들이 있다.
char* name : 필드의 이름을 나타낸다.
char* table : 현재 필드의 테이블 이름을 나타낸다.
char* def : 필드의 기본값을 나타낸다.
MYSQL_FIELD* mysql_fetch_field( MYSQL_RES* result )
: 한번에 하나의 MYSQL_FIELD를 리턴한다.
MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES* result )
: 배열의 형태로 모든 MYSQL_FIELD를 리턴한다.
예1)
MYSQL_FIELD *field ;
while( (field = mysql_fetch_field(result) )
printf(“field name %s＼n”, field -> name ) ;
예2)
unsigned int num_fields ;
unsigned int i ;
MYSQL_FIELD* fields ;
num_fields = mysql_num_fields( result ) ;
fields = mysql_fetch_fields( result ) ;
for( i = 0 ; i < num_fields ; ++i)
printf(“field name %s＼n”, fields[i].name) ;
위의 두 개의 예는 모두 동일안 결과를 출력한다.
6. 문제 해결
이번에는 C API를 사용한 소스 코드를 컴파일할 때 혹은, 실행 도중 만나는 문제점을 해결할 수 있는 방법을 설명한다.
1) “con.c:2: mysql.h: 그런 파일이나 디렉토리가 없음”
이 경우는 mysql.h의 경로를 찾을 수 없어서 나는 에러이다. 다시 한번 mysql.h가 어디에 있는지 확인을 하고, -I옵션으로 그 경로를 지정해 주자. -I옵션과 경로는 붙여 써야 한다.
2) “con.o(.text+0x11): undefined reference to `mysql_init’” 위와 같이
‘undefined reference......’ 라고 나오는 에러는 -lmysqlclient 옵션을 주지 않았기 때문이다.
3) “ld: cannot open -lmysqlclient: 그런 파일이나 디렉토리가 없음” 위의 에러는
-L옵션 뒤에 붙은 라이브러리의 경로가 잘못 되었기 때문이다.
libmysqlclient.so 파일의 경로를 찾아서 그 경로로 지정을 해 주자.
-I 옵션과 마찬가지로 -L과 경로는 붙여 써야 한다. 위의 에러들은 컴파일시에 옵션이 잘못되었을 경우 나오는 에러 메시지이다.
계속해서 프로그램의 실행 중에 나오는 에러 메시지를 보자.
4) “Can’t connect to local MySQL server (2)”
위의 에러는 MySQL의 서버에 연결을 할 수 없다는 메시지로서, MySQL 서버의 데몬이 실행 중이지 않을 때 나오는 메시지이다.
safe_mysqld 명령 등을 이용하여 데몬을 실행시켜 주자.
5) “Access denied for user: ‘rot@localhost’ (Using password: YES)”
접근이 금지되었다는 메시지로서 사용자 아이디를 잘못 입력하였 거나, 혹은 암호를 잘못 입력하였을 때 나오는 메시지이다.
MySQL의 사용자는 모두 mysql database의 user 테이블에 있으므로, 참고를 하여 적도록 하자.
6) “error in loading shared libraries libmysqlclient.so.6:
cannot open shared object file: No such file or directory”
MySQL의 라이브러리를 열지 못한다는 메시지이다. 컴파일 할 때 MySQL의 동적 라이브러리를 사용하느데, 동적 라이브러리이므로 실행시에도 라이브러리가 필요하게 된다.
libmysqlclient.so가 /usr/lib 혹은 /usr/lib/mysql 디렉터리에 존재하지 않을 경우에 발생하는 문제이다. 가장 간단한 해결법으로는 모든 MySQL 라이브러리를 /usr/lib/나 /usr/local/lib 밑으로 복사하는 것인데 별로 추천하는 방법은 아니다.
두 가지 방법이 있는데,
먼저 시스템의 운영자라면, /etc/ld.so.conf 파일에libmysqlclient.so가있는 경로를 적어 준 후에 ldconfig 라는 명령을 프롬프트에서 실행하여 주면 된다. 이러한 권한이
없는 일반 사용자라면, 자신의 쉘의 환경 변수를 이용하면 된다.
각자의 쉘이 맞게, LD_LIBRARY_PATH를 libmysqlclient.so가 있는 디렉터리로 지정을 해주자.
C 쉘 사용자는 setenv LD_LIBRARY_PATH 경로명 본 쉘 사용자는
export LD_LIBRARY_PATH 경로명 이렇게 해주면 된다.
보통 위의 6개의 에러가 가장 많이 발생한다. 혹시, 해결하지 못할 에러가 있다면 필자에게 메일을 보내면 친절히 답변해 주겠다.
7. Quick Reference
위에서 설명한 API만을 가지고도 서버에 연결하여, Query를 수행하고 그 결과를 확인 할 수 있다. 이번에는 C API의 중요한 함수들을 모아서, 함수의 프로토타입과 함수의 기능들에 대해서 간단히 알아도록 하자. 개인적으로 그다지 중요하지 않다고 생각되는 함수들은 제외시켰으므로 모든 C API를 보고 싶은 독자들은 MySQL Reference Manual의 19장을 보기 바란다. 참고로 필자가 참조한 메뉴얼은 MySQL 3.22.21용 메뉴얼이었다.
1) my_ulonglong mysql_affected_rows(MYSQL* mysql) INSERT, UPDATE, DELETE 등의 query로 영향을 받은 ROW의 수를 리턴한다.
2) void mysql_close(MYSQL* mysql) 서버와의 연결을 종료한다.
3) void mysql_data_seek(MYSQL_RES* result, unsigned int offset) result에서 임의의 ROW에 접근을 하도록 하는 함수이다.
offset이 row의 번호를 나타낸다. 0이면 처음 ROW, mysql_num_rows( result ) - 1은 마지막 row를 나타낸다.
4) unsigned int mysql_errno(MYSQL* mysql) 가장 최근에 mysql에 일어난 에러의 번호를 리턴한다.
5) char* mysql_error(MYSQL* mysql) 가장 최근에 일어난 에러 메시지를 리턴한다.
6) MYSQL_FIELD*mysql_fetch_field(MYSQL_RES* result)한번 호출할 때마다 하나의 필드에 대한 정보를 리턴한다.
7) MYSQL_FIELDS*mysql_fetch_fields(MYSQL_RES* result) 배열형대로 result의 필드에 대한 정보를 한꺼번에 리턴한다.
8) MYSQL_FIELD_OFFSETmysql_field_seek(MYSQL* mysql, MYSQL_FIELD_OFFSET offset)임의의 필드에 접근을 하도록 한다.
9) MYSQL_FIELD_OFFSETmysql_field_tell(MYSQL_RES* result)현재 필드의 offset을 리턴한다.
10) void mysql_free_result(MYSQL_RES* result) result에 할당된 메모리를 해제한다.
11) MYSQL* mysql_init(MYSQL* mysql) mysql 객체를 초기화한다.
인자가 NULL이면 새로운 MYSQL 객체를 생성하고, 초기화하여 리턴한다.
12) MYSQL_RES* mysql_list_dbs(MYSQL* mysql, const char* wild)
현재 서버에 있는 데이타베이스의 목록을 리턴한다. wild는 MySQL에서 사용할 수 있는 정규식을 나타낸다.
result = mysql_list_dbs( &mysql,”%” )는 모든 데이터베이스를 리턴하는 예이다. (%는 ‘모든’을 나타낸다)
13) MYSQL_RES* mysql_list_tables(MYSQL* mysql, const char* wild)
현재 데이타베이스에 있는 테이블들의 목록을 리턴한다.
14) unsigned int mysql_num_fields(MYSQL_RES*result) 혹은
unsigned int mysql_num_fields(MYSQL* mysql)필드의 수를 리턴한다.
15) my_ulonglong mysql_num_rows(MYSQL_RES* result) result에 총 몇 개의 ROW가 있는지 리턴한다. query 수행 후 mysql_store_result()를 호출하였을 경우에만 사용할 수 있고, mysql_use_result()는 사용할 수 없다.
16) int mysql_ping(MYSQL* mysql) 서버에 연결 중인지를 리턴한다. 연결이 끊어 졌을 경우, 다시 연결을 시도한다. 서버와 연결을 한 후, 오랫동안 가만히 있으면 서버가 연결을 끊어버리는데, 이런 경우에 사용한다.
17) int mysql_query(MYSQL* mysql, const char* query) query가 포인트 하는 쿼리를 수행한다. query의 끝은 NULL 문자이다. 성공적으로 query를 수행하였으면 0을 리턴한다.
18) MYSQL* mysql_real_connect(MYSQL* mysql, const char* host,
const char* user, const char* passwd, const char* db, uint port,
const char* unix_socket, unit client_flag ) host와의 연결을 시도한다.
인자별로 자세한 설명은 윗 부분에 되어 있다.
19) int mysql_real_query(MYSQL* mysql, const char* query,
unsigned int length)mysql_query()처럼 query를 수행하나, query의 끝이 legnth인 것이 다르다.
20) MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES* result)현재 ROW의 offset을 리턴한다.
21) int mysql_select_db(MYSQL* mysql, const char* db)사용하고자 하는 database를 변경한다.
mysql_query()를 이용하여 “use db이름”의 query를 수행해도 같은 결과를 얻는다.
22) int mysql_shutdown(MYSQL* mysql) 서버를 종료시킨다.
현재 사용자에게 shutdown 권한이 있어야 한다.
23) MYSQL_RES* mysql_store_result(MYSQL* mysql) query의 수행결과를 서버로부터 한 번에 모두 받아 온다.
24) MYSQL_RES* mysql_use_result(MYSQL* mysql) query의 수행결과를 서버로부터 한 개의 ROW 씩 받아 온다.
MySQL 강좌 Ⅱ
허정수 (나우누리:wertyu)
1. 들어 가는 말
대학 2년 반 동안 공부를 하면서 무엇을 배웠는지 기억이 잘 안나지만, 절대 잊을 수 없는 한 마디가 있다.
“정보화 시대에서 엔트로피는 ‘정보’이다.
일반적으로 엔트로피는 감소하는 방향으로 흐르는 것이 자연인데, 정보화 시대에서의
엔트로피인 ‘정보’는 무한으로 증가하고 있다. 따라서 인간은 언젠가 자멸할 것이다.”
물리 교수님으로부터 들은 이야기인데 논리적으로 따지자면 맞는 말은 아니지만 요즘 들어서 위의 말을 뼈저리게 실감하고 있다. 왜 이리 해야 할 것은 많고, 이쪽 저쪽에서 새로운 기술이 쏟아지는지. 놓치고 나면 혼자 도태되는 것 같아 모두 해보려고 해도 쉽게 되지도 않는다.
머리 속이 너무 복잡해서 정말 미칠 지경인 적도 있었다. 이런 일은 필자만이 느낀 것은 아니라고 생각한다. 정보화 시대를 살고 있는 독자들이라면 누구나 한번쯤은 겪어 봤을 거라 생각한다.(모든 것을 버리고 원시인처럼 자연으로 돌아가고 싶을 때도 있다.) 하지만 Database를 이용하여 정보들을 좀더 효율적으로 관리하고 정말 필요한 정보들을 얻을 수 있다면 그 ‘자멸’을 좀 더 늦출 수 있진 않을까?
이번 시간에는 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 : 단일 정밀도를 가진 부동 소수점을 나타낸다. 언제나 부호 있는 수이고,
-3.402823466E+38부터 3.402823466E+38까지의 수를 표현할 수 있다.
DOUBLE : 2배 정밀도를 가진 부동 소수점 수이다. 언제나 부호있는 수이고,
-1.79769313486231517E+308부터 1.7976931348623157E+308까지의 수를 표현할 수 있다.
DATE : 날짜를 표현하는 타입으로 ‘1000-01-01’부터 ‘9999-12-31’까지를 나타낼 수 있다.
3 바이트
DATETIME : 날짜와 시간을 같이 나타내는 타입이다. ‘1000-01-01 00:00:00’부터
‘9999-12-31 23:59:59’까지 나타낼 수 있다. 8 바이트
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
TINYTEXT : 255개의 문자를 저장할 수 있다. BLOB은 BINARY LARGE OBJECT의 약자이다.
BLOB
TEXT : 65535개의 문자를 저장할 수 있다.
MEDIUMBLOB
MEDIUMTEXT : 16777215개의 문자를 저장할 수 있다.
LONGBLOB
LONGTEXT : 4294967295(4기가)개의 문자를 저장할 수 있다.
문자열은 작은 따옴표(‘)나 큰 따옴표(“)로 묶인다.
3. Database 만들기.
이번엔 Database를 만들어 보자. 이번 회에서는 SQL 언어를 소개하기 위해서 슈퍼마켓에서 물건을 관리하는 예를 들도록 하겠다.
super라는 테이타베이스를 만드는 방법은 두 가지가 있다. 하나는 mysqladmin을 이용하는 방법이고, 다른 방법은 mysql에서 직접 만드는 방법이다.
% mysqladmin create super
Database “super” created.
%mysqlshow
+-------------+
| Databases |
+-------------+
| games |
| mysql |
| super |
| test |
+-------------+
이때 암호를 걸어 놨다면 -p 옵션을 붙여야 한다. 매번 -p 옵션을 붙이고, 암호를 입력하는 것이 귀찮다면, 홈 디렉터리에 .my.cnf 파이를 만들어 놓고,
[client]
password=암호
위의 두 줄을 적어 두면 된다. .my.cnf 파일은 다른 사람이 못 보도록 퍼미션을 주자.
데이타 베이스를 만드는 다른 방법은 mysql이라는 client 프로그램을 이용하여 SQL 문을 이용하는 방법이다.
% mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34 to server version: 3.22.20a
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에서 입력하기로 한다.
* 테이블 만들기 *
CREATE TABLE 테이블_이름( 컬럼의 형식 ) ;
예)
mysql> CREATE TABLE dome(name CHAR(20), phone VARCHAR(20), email VARCHAR(30)) ;
mysql> CREATE TABLE Item( Id INT PRIMARY KEY, name VARCHAR(40), cost INT,
-> remain INT, dome_name VARCHAR(30)) ;
mysql> CREATE TABLE export(No INT NOT NULL AUTO_INCREMENT, name VARCHAR(40),
-> how_many INT, how_much INT, when DATE, PRIMARY KEY(No)) ;
만들어진 테이블을 확인하고 싶으면
mysql> SHOW TABLES;
이용하면 된다.
mysql> SHOW TABLES ;
+-----------------+
| Tables in super |
+-----------------+
| Item |
| dome |
| export |
+-----------------+
* 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 테이블에 데이타를 입력해보자.
* 레코드 삽입 *
INSERT INTO 테이블_이름 VALUES(데이타 값...) ;
예)
mysql> INSERT INTO dome VALUES(‘A상점’, ‘222-2222’, ‘aa@xxx.com’) ;
mysql> INSERT INTO dome VALUES(‘B상점’, ‘333-2222’, ‘bb@yyy.com’) ;
mysql> INSERT INTO dome VALUES(‘C상점’, ‘444-2222’, ‘cc@zzz.com’) ;
mysql> INSERT INTO Item VALUES(1111, ‘쪼꼬빠이’, /* 가격*/150, /* remain*/1000, ‘A상점’) ; /* 주석문 */
mysql> INSERT INTO Item VALUES(1234, ‘맴시꼴라’, 800, 150 , ‘B상점’) ;
mysql> INSERT INTO Item VALUES(231 , ‘쓴라면’ , 400, 230 , ‘C상점’) ;
( dome 테이블의 remaing 컬럼은 현재 팔고 남은 개수를 나타낸다. 지정된 개수 이하로 떨어 지면 주문을 하기 위해서 쓰였다.)
잘 삽입이 되었는지 결과를 보고 싶다면,
mysql> SELECT * FROM dome ;
+-------+----------+------------+
| name | phone | email |
+-------+----------+------------+
| A상점 | 222-2222 | aa@xxx.com |
| B상점 | 333-2222 | bb@yyy.com|
| C상점 | 444-2222 | cc@zzz.com |
+-------+----------+------------+
3 rows in set (0.00 sec)
등으로 확인할 수 있다. SELECT에 대한 자세한 설명은 밑에 있다.
필자는 세 개의 레코드 밖에 삽입을 안 했지만, 시간이 남는 독자는 많은 레코드를 삽입하고 나머지 글을 읽어 주길 바란다.
마지막으로 export 테이블에 데이타를 입력해 보도록 하자. export 테이블은 판매량을 기록하는 테이블로 만들었다. 상품의 이름과 판매량, 판매 기록, 판매 날짜 등을 저장한다. 다음과 같이 하여 레코드를 삽입할 수 있다.
mysql> INSERT INTO export VALUES(NULL, ‘쓴라면’, 3, 3*400, CURRENT_DATE) ;
* 위는 ‘쓴라면’이 3개가 팔렸다고 가정을 했다.
* NULL : ‘No’ 컬럼은 AUTO_INCREMENT의 속성을 가진다.. NULL을 대입하면, 자동으로 ‘가장 이전에 입력했던 값 + 1’이 삽입된다. 일부러 마지막에 입력된 값을 확인하지 않아도 된다.
* CURRENT_DATE : ‘when’ 컬럼은 타입이 DATE이다. CURRENT_DATE는 현재의 날짜를 의미한다. 임의의 날짜를 입력하고 싶으면, ‘1999-02-12’혹은 19990212로 하면 된다(1999년 2월 12일을 나타낸다).
한 가지 더 고려를 해야 한다. item 테이블에 ‘쓴라면’에 관한 항목 중 remaining 컬럼에서 팔린 개수 만큼을 빼줘야 한다. 레코드의 값을 수정하기 위해선 UPDATE를 이용한다.
* 레코드 수정 *
UPDATE 테이블_이름 SET 컬럼=식,... [WHERE 조건] ([] 사이는 옵션을 나타낸다.)
예)
mysql> UPDATE Item SET remain = remain - 3 WHERE name=’쓴라면’ ;
‘WHERE’이하는 조건을 나타낸다. 즉, name이 ‘쓴라면’인 레코드의 remain만을3만큼 감소 시킨다.(실수로 테이블의 이름을 item이라고 하면 안된다. MySQL은 테이블의 이름에 대해서는 대소문자를 구별한다. 컬럼의 이름은 안함). WHERE 이하의 조건을 쓰지 않으면 모든 레코드의 remain에서 3이 감소된다.
위를 계속 반복하여 export 테이블에 여러 개의 레코드를 삽입하자.
계속해서 SELECT에 대해서 알아 보도록 한다. SELECT는 SQL 명령 중에서 가장 많이 쓰인다.
6. SELECT
위에서 잠시 SELECT에 대해 잠시 소개하였다. SELECT는 테이블에서 레코드를 선택하여, 리턴하는 SQL 명령이다. SELECT를 사용하여 자신이 원하는 레코드만을 선택할 수 있기 때문에 매우 중요하다. 이 글에서도 SELECT에 많은 부분을 할당하여 소개한다.
먼저, SELECT를 이용하여 위에서 만든 세 개의 테이블에 어떤 레코드가 있는지 보자.
mysql> SELECT * from dome ;
+-------+----------+------------+
| name | phone | email |
+-------+----------+------------+
| A상점 | 222-2222 | aa@xxx.com |
| B상점 | 333-2222 | bb@yyy.com |
| C상점 | 444-2222 | cc@zzz.com |
+-------+----------+------------+
3 rows in set (0.11 sec)
mysql> SELECT * FROM Item ;
+------+----------+------+--------+------------+
| Id | name | cost | remain | dome_name |
+------+----------+------+--------+------------+
| 1111 | 쪼꼬빠이 | 150 | 684 | A상점 |
| 1234 | 맴시꼴라 | 800 | 105 | B상점 |
| 231 | 쓴라면 | 400 | 164 | C상점 |
+------+----------+------+--------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM export ;
+----+----------+------------+------------+-----------+
| No | name | how_many | how_much | when |
+----+----------+------------+------------+-----------+
| 1 | 맴시꼴라 | 23 | 18400 | 1999-05-14 |
| 2 | 맴시꼴라 | 10 | 8000 | 1999-05-14 |
| 3 | 쓴라면 | 5 | 2000 | 1999-05-15 |
| 4 | 쓴라면 | 15 | 6000 | 1999-05-15 |
| 5 | 맴시꼴라 | 8 | 6400 | 1999-05-16 |
| 6 | 쪼꼬빠이 | 120 | 18000 | 1999-05-16 |
| 7 | 쓴라면 | 2 | 800 | 1999-05-16 |
| 8 | 쪼꼬빠이 | 45 | 6750 | 1999-05-16 |
| 9 | 맴시꼴라 | 4 | 3200 | 1999-05-16 |
| 10 | 쓴라면 | 32 | 12800 | 1999-05-17 |
| 11 | 쪼꼬빠이 | 11 | 1650 | 1999-05-17 |
| 12 | 쪼꼬빠이 |
140 | 21000 | 1999-05-17 |
| 13 | 쓴라면 | 12 | 4800 | 1999-05-17 |
+----+----------+------------+------------+-----------+
13 rows in set (0.00 sec)
( 물론 결과는 독자에 따라 다르다.)
SELECT 뒤의 ‘*’는 모든 컬럼을 나타낸다. 즉 모든 컬럼을 리턴하라는 의미이다. 만약 name 컬럼만 리턴하고 싶다면, SELECT name FROM export 하면 된다.
그럼, 이제 SELECT의 파워를 느껴 보자.
처음으로 특정일에 판매된 물품을 보고 싶으면 어떻게 할까. UPDATE에서 WHERE를 기억할 것이다. SELECT에서도 WHERE 뒤에 조건을 붙이면 된다.
mysql>SELECT * from export WHERE when = ‘1999-05-15’ ;
(지면 관계상 SELECT의 결과는 싣지 못하니 직접 확인하기 바란다.)
SELECT는 많은 함수들과 함께 쓰일 수 있다. 예를 들어 전체 판매 금액의 합을 보고 싶다면, 다음과 같이 하면 된다.
mysql> SELECT SUM(how_much) FROM export ;
+------------------+
| sum(how_much) |
+------------------+
| 109800 |
+------------------+
SUM(expr)은 expr의 합을 리턴한다. 만약 1999-05-15의 판매액을 보고 싶다면 WHERE when = ‘1999-05-15’를 붙여 주면 된다.
또한, 조건을 두 개 줄 수도 있다. 1999-05-17의 ‘쓴라면’의 판매량을 보고 싶다면,
mysql> SELECT SUM(how_many) FROM export WHERE name=’쓴라면’ AND when = ‘1999-05-17’ ;
이렇게 하면 된다. ‘AND’ 혹은 ‘&&’은 ‘논리적 그리고’를 나타낸다.
비교 연산자도 있다. 판매액이 10000원 이상인 상품 만을 보고 싶다면,
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 ;
+------------+-------+
| 날짜 | 수입 |
+------------+-------+
| 1999-05-14 | 26400 |
| 1999-05-15 | 8000 |
| 1999-05-16 | 35150 |
| 1999-05-17 | 40250 |
+------------+-------+
위의 쿼리는 많은 것을 보여 주고 있다.
* when AS ‘날짜’,
SUM(how_much) AS ‘수입’: 컬럼의 이름에 대해 알리아스(alias, 별명)를 붙여 준다. SUM(how_much)라고 출력을 하는 것 보다는 ‘수입’이라고 출력을 하는 것이 훨씬 더 읽기 쉬울 것이다.
* GROUP BY when : when의 컬럼에 대해서 같은 컬럼 값을 갖는 것에 대해서만 계산을 한다. 각 날짜별로 SUM을 구하므로 꼭 필요하다.
이번엔 주문을 하기 위해 도매상의 연락처를 알아 보자. 물건이 200개 이하일 때 도매상으로 주문을 하기로 한다. 주문을 직접할 수는 없기에 전화 번호와 E-mail 주소 만을 찾아 보자. 우리의 예에서는 물건이 3 개밖에 안되지만 예를 들어 보기로 한다.
mysql> SELECT Item.name AS ‘상품명’, dome.name AS ‘도매상’,
-> dome.phone AS ‘전화번호’, dome.email AS ‘E-mail’ FROM Item , dome
-> WHERE Item.remain <= 200 && dome.name = Item.dome_name ;
+----------+--------+----------+------------+
| 상품명 | 도매상 | 전화번호 | E-mail |
+----------+--------+----------+------------+
| 맴시꼴라 | B상점 | 333-2222 | bb@yyy.com |
| 쓴라면 | C상점 | 444-2222 | cc@zzz.com |
+----------+--------+----------+------------+
* FROM Item, dome : 두 개의 테이블로부터 값을 리턴한다. 두 개의 테이블에서 리턴을 하므로, 어떤 테이블의 컬럼인지, 꼭 명시적으로 적어야 한다.
* WHERE Item.remain <= 200 && dome.name = Item.dome_name :
Item의 remain이 200보다 작고, dome의 name과 Item의 dome_name이 같은 레코드만을 리턴한다.
7. 파일로부터 레코드 입력을 받기, 결과를 파일로 저장하기.
이번에는 파일에 데이타가 있을 때, 이 파일을 테이블에 저장하는 방법과 SELECT로 리턴된 레코드를 파일에 저장하는 방법에 대해 설명을 한다.
먼저 다음과 같은 파일이 있다고 하자.
ex.txt)
NULL 맴시꼴라 23 18400 ‘1999-05-17’
NULL 쪼꼬빠이 10 1500 ‘1999-05-17’
이 파일의 내용을 export 테이블에 삽입하고 싶으면
mysql> LOAD DATA INFILE ‘root/doc/mysql/ex.txt’ INTO TABLE export
> FIELDS TERMINATED BY ‘ ‘ ;
이렇게 하면 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’
> FIELDS TERMINATED BY ‘ ‘ ;
마찬가지로 파일의 경로를 상대 경로로 지정을 하면, 데이타 베이스가 있는 디렉터리를 기준을 삼고, 절대 경로로 지정을 하면 지정한 디렉터리에 파일이 생긴다.
8. 그 외....
1) ALTER TABLE
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 - 테이블이나 컬럼에 대한 정보를 보여준다.SHOW DATABASES : 데이타베이스들을 보여준다.
SHOW TABLES : 현재 데이타베이스에 있는 테이블을 보여준다.
SHOW COLUMNS FROM 테이블_이름 : 테이블에 있는 컬럼들에 대한 정보를 보여준다.
5) 패턴 매칭 MySQL에서는 ‘%’는 와일드 문자를 나타낸다.
예를 들어
mysql> SELECT * FROM Item WHERE dome_name like ‘%상%’ ; 는 Item 테이블의 dome_name 중 아무 문자로나 시작하고 중간에 ‘상’이 있고, 어떠한 문자로도 끝나는 레코드들을 리턴한다.
9. 마치는 글
이번 회에서는 SQL 언어에 대해서 알아 보았다. SQL은 너무 나도 많은 분량이어서 중요한 것만을 요약하여 썼다. 이 글만 보면 부족한 점이 많으니 꼭 MySQL의 메뉴얼이나 SQL에 관한 책을 보기 바란다. 특히 SELECT와 함께 쓰이는 함수를 많이 소개 못 했는데, 매뉴얼을 보면 많은 함수들이 있다. 꼭 읽어 보기 바란다.
다음 회에는 MySQL의 C API를 이용하여 C나 C++로 짠 프로그램과 MySQL를 연동하는 방법을 알아 본다.
이 글을 읽으면서 질문이 있으면 wertyu@nownuri.net으로 메일을 보내주길 바란다.
《필자소개》
허정수님은 숭실대학교 컴퓨터학부에 재학 중이며 TEAM NNR에서 활동 중이다.
리눅스 포커스(http://www.linuxfocus.com)의 자원 봉사자로 활동을 하고 있고, 리눅스 커널과 데이타 베이스에 관심이 많다.
－소개와 특징 및 설치－
허정수 : 나우누리〈wertyu〉
1. 들어 가는 말.
1997년 겨울, 필자는 SQL을 배우기 위해 DB 서버를 찾고 있었다. 여러 잡지와 책을 찾아 보았는데, 그때만 해도 mSQL과 PostgreSQL에 대한 내용 밖에 없었다. 평소 일반 사람들과 다른 삶을 살고 싶었던 필자는 일반 사람들이 많이 쓰지 않는 DB 서버를 찾다가 우연히 MySQL을 알게 되었고, MySQL에 반해 버렸다. 1997년 만해도 MySQL은 mSQL이나, PostgreSQL보다는 사용자가 적었다(우리 나라에서만 그런지는 모르지만). 몇 달전 리눅스 저널에서 각 분야별로 어떤 응용 프로그램이 가장 많은 사용자를 확보했나 조사를 한 적이 있다. DB 분야에서는 MySQL이 1등을 했다. 아이러니컬하게도 다른 사람이 많이 안 쓰는 DB 서버를 사용하기 위해 MySQL을 썼었는데, 지금은 가장 많은 사용자를 확보하였다.
이 글에서는 MySQL의 특징과 설치 방법, 설정 방법, CAPI를 이용하여 응용 프로그램과 연동하기, PHP3를 이용하여 연동하는 방법에 대해서 알아 본다. 이번 기사에서는 MySQL의 특징과 설치 방법, 설정 방법에 대해서 알아 본다.
2. MySQL 의 특징
MySQL의 메뉴얼을 보면 MySQL의 특징이 약 30가지 정도 나와 있다. 요약해 보면 다음과 같다.
* 커널 thread를 이용한 Multi thread를 지원하므로, CPU가 여러 개일 경우 이들 CPU를 잘 활용할 수 있다.
* 다양한 플랫폼을 지원한다.( Windows NT도 지원한다.)
* 아주 큰 데이타 베이스도 다룰 수 있다.
MySQL을 만든 회사는 5천만개의 레코드를 가진 데이타 베이스도 사용하고 있다.
* 다양한 나랏말로 에러 메시지를 표시한다.
* 최적화된 라이브러리를 사용한다.
하지만, MySQL의 가장 큰 특징을 들라고 하면, 빠른 속도를 들 수 있다. 그림 1에 MySQL과 오라클의 속도를 비교해 놓은 벤치 마크 결과가 있다.
[그림 1]
[그림 1]에서 알 수 있듯이, MySQL은 매우 빠르다. http://www.tcx.se/benchmark.html에 가면 다른 제품과도 비교해 놓은 자료가 많이 있으니, 관심있는 독자는 직접 확인하기 바란다.
그렇다면, 왜 MySQL이 다른 제품에 비해서 빠른지 궁금하지 않는가? MySQL은 속도 향상을 위해서 일부러 몇 가지 ANSI SQL을 구현하지 않다. 바로, Transaction과 Trigger이다. Transaction과 Trigger는 시스템의 자원을 많이 소모함으로 속도를 떨어지게 한다. MySQL은 Transaction과 Trigger를 일부러 구현하지 않았다.
2.1 MySQL에서 지원하지 않는 SQL 기능.
다음은 아직 MySQL이 지원하는 않는 기능들이다.
* Sub-select
SELECT * FROM table1 WHERE id IN( SELECT id FROM table2) ; 와 같은 것은 아직 MySQL에서 지원하지 않는다. 다만, INSERT .. SELECT .. 와 REPLACE .. SELECT 만이 지원된다. sub-select는 3.23.0 버전에서 사용가능할 것이라고 한다.
* SELECT INTO TABLE
MySQL은 SELECT .. INTO TABLE을 지원하지 않는다. SELECT .. INTO OUTFILE 만을 지원한다.
* Transaction
앞에서도 말했다시피 MySQL은 Transaction을 지원하지 않는다. Transaction은 시스템의 자원을 많이 소모함으로 전체적인 속도를 느리게 한다. 따라서, MySQL은 Transaction을 구현하지 않고, 대신에 LOCK TABLES/UNLOCK TABLES를 이용하여 Transaction과 같은 일을 할 수 있도록 하였다.
* Stored Procedure와 Trigger
Stored Procedure는 앞으로 지원할 계획이지만, Trigger는 속도를 떨어드리고, 별로 필요없는 쿼리라서 앞으로도 지원할 계획이 없다.
* Foreign Key
* View
3. MySQL의 설치
아쉽게도 MySQL의 라이센스 방식 때문에 레드햇 같은 리눅스 배포판에는 MySQL이 포함되어 있지 않다. MySQL의 라이센스 방식은 비상업용으로 MySQL이 쓰일 경우 무료이고, 상업적으로 쓰일 경우, 라이센스를 구입해야 한다. Win 95, Win 98, Win NT용 MySQL은 상업적이든 비상업적이드 라이센스를 구입해야 한다.
http://www.tcx.se/download.html에서 MySQL을 구할 수 있다.
배포되는 형식은 소스 코드, 바이너리, RPM이다. 이 글이 쓰여 지고 있는 지금의 최신 버전은 3.22.20a이다.
3.1 소스 코드로 설치하기.
초보자들은 컴파일을 두려워 하는 경향이 있다. 하지만 대부분의 프로그램은 autoconf와 automake를 이용하여 배포되므로 컴파일하기가 쉽다. 메뉴얼에 나온대로 잘 따라간다면 실패없이 단 한번에 설치를 마칠 수 있다.
가장 기본적인 과정은 다음과 같다.
% gzip -d mysql-Version.tar.gz
% tar xvf mysql-Version.tar
% cd mysql-Version
이제 실제 설치 단계이다.
% configure
% make
% make check
% make install
% cd scripts
% mysql_install_db
몇 줄만 치면 설치가 다 끝난다. 각 단계는 다음과 같은 일을 한다.
configure : 각자의 환경에 맞게 Makefile을 생성한다.
make : 컴파일을 한다.
make check : 컴파일이 제대로 되었는지 검사하는 단계이다.
(꼭 할 필요는 없다.)
make install : 만들어진 실행 파일을 원하는 디렉터리에 설치한다.
mysql_install_db : MySQL의 권한에 대한 테이블을 생성 한다.
MySQL을 처음 설치했을 때만 필요한 과정이고, 이전 버전의 MySQL에서 업그레이드를 하고 싶다면 mysql_fix_privilege_tables를 실행시키면 된다.
따로 MySQL이 설치될 디렉터리를 지정하지 않았다면, 기본적인 디렉터리는 /usr/local/이 된다. MySQL을 설치할 시스템이 자신이 슈퍼 유저인 시스템이라면 상관없겠으나, 학교 서버에 설치한다면, 경우는 대부분 /usr/local/에 설치할 권한이 없을 것이다. 이때, MySQL을 설치할 디렉터리를 지정해 줄 수 있다.
% configure --prefix=원하는 디렉터리
이렇게 하면 MySQL이 자신이 지정한 디렉터리에 설치된다. MySQL이 설치된 디렉터리의 bin 디렉터리에 실행 파일이 설치되고, include/mysql에 헤더 파일이 설치된다. lib/mysql에 라이브러리들이 설치되고, libexec에 MySQL의 데몬인 mysqld가 설치된다. var에는 실제 데이타들이 설치된다.
3.2 바이너리 배포판으로 설치하기.
일단 바이너리 배포판을 위의 홈페이지에서 받은 후에
% gzip -d mysql-Version-OS.tar.gz
% tar xvf mysql-Version-OS
% ln -s mysql-Version-OS mysql
% cd mysql
% scripts/mysql_install_db
이렇게 하면 설치가 끝난다.
3.3 rpm으로 설치하기.
rpm은 소스 코드 rpm과 바이너리 rpm이 있다.
rpm이 소스 코드를 담고 있다면,
% rpm -i MySQL-Version.src.rpm
% cd /usr/src/redhat/SOURCES
이후에는 소스 코드로 설치 하기 과정을 하면 된다.
바이너리의 경우 MySQL-Version.i386.rpm, MySQL-client-Version.i386.rpm MySQL-bench-Version.i386.rpm, MySQL-devel-Version.i386.rpm 의 4 개의 파일이 필요하다. rpm을 이용하여 위의 4 개 파일을 설치하면 된다. 이때 /var/lib/mysql에 실제 데이타들이 저장된다. 부팅시에 자동으로 데몬을 실행시키는 파일을 자동으로 만들어 준다.
3.4 MySQL 사용하기.
설치가 완료됐으니, 이제 MySQL을 사용해 보자. 먼저 어떤 실행 파일이 만들어 졌는지 살펴 보자. 많은 실행 파일이 있지만,많이 쓰이는 실행 파일만 정리하면 다음과 같다.
1) mysql
SQL 쉘로서, client 프로그램이다. MySQL에 접속하여, 쿼리를 수행하는 프로그램이다. 앞으로 많이 사용하게 될 것이다.
2) mysqladmin
MySQL을 관리하는 프로그램이다. 데이타 베이스를 만들고, 삭제하고, 권한에 대한 테이블을 다시 읽어 들이는 일을 한다.
3) mysqld
MySQL 데몬이다. MySQL을 사용하기 위해서 항상 실행 중이어야 한다.
4) mysqlshow
MySQL안에 어떤 데이타 베이스들이 있는지, 그 데이타 베이스들에는 어떤 테이블이 있는지, 테이블에는 어떤 컬럼이 있는지를 보여준다.
5) safe_mysqld
mysqld를 좀더 안전하게 실행시키는 스크립트 파일이다.
6) isamchk
테이블에 문제가 있을 때 문제점을 고치기도 하고, 테이블을 최적화한다.
MySQL의 사용하기 위해서 데몬이 실행 중이어야 한다고, 설명을 했다. 데몬을 실행시키는 방법은
% safe_mysqld
혹은
% mysqld
이다. MySQL은 3306번 포트를 사용한다. 혹시 다른 프로그램에서 먼저 3306번을 쓰고 있으면 데몬이 에러를 내면서 실행이 안 된다. 이때는
% mysqld -P 3333
처럼 사용할 포트 번호를 지정해 줄 수 있다.
매번 부팅시마다 데몬을 손수 실행시키기 귀찮다면, safe_mysqld를 /etc/rc.d/rc.local에 한줄 추가해주면 부팅시마다 자동으로 실행된다. (rpm으로 설치한 사람은 자동으로 되어 있다.)
3.5 권한 설정과 새로운 사용자 추가하기.
MySQL 설치 단계에서 mysql_install_db 단계가 권한에 대한 테이블을 설치하는 단계라고 하였다. MySQL을 처음 설치하면 root만이 MySQL을 사용하도록 권한이 주어여 있다. 일단 어떤 데이타 베이스들이 있는지 보자.
% mysqlshow -u root
+-----------+
| Databases |
+-----------+
| mysql |
| test |
+-----------+
‘-u root’가 바로 root로 사용을 한다는 옵션이다.
MySQL을 처음 설치하면 mysql, test라는 두 개의 데이타 베이스가 설치된다. mysql에 어떤 테이블이 있는지 보자.
% mysqlshow -u root mysql
Database: mysql
+--------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+--------------+
6 개의 테이블이 있다. 이 중 user 테이블이 사용자에 관한 테이블이다. 이제 mysql이라는 클라이언트 프로그램을 이용해서 MySQL을 사용해 보자.
% mysql -u root
mysql>
mysql 데이타 베이스를 사용하기 위해서 다음과 같이 한다.
mysql> use mysql
그럼 다음과 같이 하여, mysql 데이타 베이스의 user 테이블에 어떤 레코드가 있는지 보자.
mysql> select * from user ;
+----------+-------+---------+-------------+-
| Host | User | password | Select_priv |
+------- --+-------+----------+-------------+-
| localhost | root | | Y |
| inos.ml.org | root | | Y |
| localhost | | | N |
| inos.ml.org | | | N |
+------ ---+-------+----------+-------------+-
7 rows in set (0.00 sec)
위와 비슷하게 나왔을 것이다. SQL 언어를 잘 모르는 독자를 위해 설명하자면 ‘SELECT ... ’는 테이블로 부터 컬럼을 리턴하라는 쿼리이다. 모든 쿼리의 끝은 ‘;’ 혹은 ‘\g’로 끝난다. XXX_priv는 XXX에 대한 권한을 나타낸다. 즉 Select_priv가 ‘Y’이면 그 사용자는 select 쿼리를 수행할 수 있음을나타낸다.
| Host | User | password | Select_priv |
+---------+-------+-------------+----------- -+-
| localhost | root | | Y |
위는 localhost의 root가 패스워드는 없고, 모든 권한이 주어져 있다는 것을 나타낸다. 하지만 어떠한 유저라도
% mysql -u root
만 하면 MySQL에 대한 모든 권한을 가질 수 있으므로 매우 위험하다.
암호를 지정하는 방법은 다음과 같다.
mysql> UPDATE user SET password = password(‘새로운암호’)
> WHERE user=’root’ ;
쿼리가 길다면 두 줄에 나눠서 써도 된다. 쿼리의 끝을 나타내는’;’ 만 있으면 된다.
UPDATE는 컬럼의 값을 변경시키는 쿼리이다. (다음 기사에 SQL 언어에 대한 설명이 이어 진다.)
MySQL은 password를 암호화 한다. 따라서 암호를 지정할 때는 password(‘암호’) 처럼 password() 함수를 이용해야 한다.
이제 mysql을 잠시 종료하고, 암호가 제대로 되었는지 확인해 보자.
mysql> quit
권한에 대한 테이블을 변경하였을 경우에는 mysqladmin을 이용하여 꼭 변경된 권한을 다시 읽어 들여야 한다. 다음과 같이 한다.
% mysqladmin -u reload
위는 권한을 다시 읽어 들이란 명령이다. 위를 안하면, 변경되지 않은 내용을 가지고 있게 된다. 다시 mysql을 실행시켜 보자. 이제 암호가 있으므로 -p 옵션을 붙여야 한다.
% mysql -u root -p
Enter password:
3.6 새로운 사용자 추가 하기
이제 새로운 사용자를 추가해 보자. 사용자가 root만 있는 경우는없고, 모든 사용자가 모든 권한을 갖는 경우도 없다. 사용자에 따라서는 SELECT만 할 수 있는 권한을 줄 때도 있다. 사용자를 추가하려면, INSERT .. INTO를 이용해서 mysql의 user 테이블에 추가를 하면 된다.
% mysql -u root -p mysql
Enter password:
mysql> INSERT INTO user VALUES(‘localhost’,
‘new-user’, password(‘newpasswd’), ‘Y’,’Y’,’Y’,’Y’,‘Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’) ;
Query OK, 1 row affected (0.00 sec)
localhost의 new-user라는 사용자가 newpasswd라는 암호와 모든 권한으로 추가되었다. 모든 권한을 주고 싶지 않다면, 주고 싶지 않은 권한 부분을 ‘Y’으로 하면 된다.
만약 SELECT, INSERT, DELETE에 대한 권한만을 주고 싶다면
mysql> INSERT INTO user (host, user,password, Select_priv, Insert_priv, Delete_priv)
-> VALUES (‘localhost’, ‘new-user’,password(‘newpasswd’),
’Y’,’Y’,’Y’) ;
처럼 하면 다른 권한은 모두 자동으로 ‘N’이 된다. 사용자 추가는 INSERT외에 GRANT를 이용할 수도 있다.
mysql> GRANT ALL PRIVILEGES ON *.* TO new-user@localhost
IDENTIFIED BY ‘newpasswd’ WITH GRANT OPTION ;
위는 localhost의 new-user 가 newpasswd라는 암호로 모든권한을 갖는다는 것을 나타낸다.
mysql> GRANT RELOAD, PROCESS ON *.* TO new-user@localhost ;
는 localhost의 new-user가 RELOAD와 PROCESS의 권한을 갖는다는 나타낸다. INSERT와 GRANT 중 어느 것을 써도 상관없다.
위에서 권한이 바뀌면 mysqladmin reload를 이용해서 바뀐
권한을 적용해야 한다고 했다. 하지만, 다음과 같이 할 수도 있다.
mysql> FLUSH PRIVILEGES ;
4. 마치는 글
지금까지 MySQL의 특징과 설치법, 설정 방법에 대해 알아 보았다. 다음 시간에는 SQL 언어에 대해서 공부를 한다. 글을 읽으면서 모르는 점이 있다면 서슴치 말고 필자에게 메일을 보내주기 바란다.
마지막으로 암호를 잊어 버렸을 경우, 어떻게 해결할지에 대해서 설명을 하겠다. 암호를 잊어버리고서, 난감해 하는 사람들을 많이 봤다. 게중에는 아예 MySQL을 지우고 새로 설치하는 사람도 있다. 시간이 남는 사람이라면 새로 설치해도 되겠지만, 시간이 남아도 중요한 데이타가 있을 경우는 새로깔지 못할 것이다. 이런 경우 다음과 같이 하면 된다.
먼저 MySQL 데몬이 실행 중이라면, 데몬의 실행을 중지한다. 그후, 데몬을 다시 실행시키는데, 권한에 대한 검사를하지 않고 실행을 시킨다.
% safe_mysqld -Sg &
-Sg가 바로 권한 검사를 하지 않는다는 옵션이다. 이제 mysql을 이용해서 암호를 제거해 주면 된다. 그후 mysqladmin reload를 이용해서 바뀐 권한을 적용해주고 다시 데몬을 실행시키면 된다.
《필자소개》
허정수님은 숭실대학교 컴퓨터학부에 재학중이며 리눅스 포커스 자원봉사자로도 활동하고 있다. 리눅스 커널과 데이타베이스에 관심이 많다고 한다.
MySQL 설치
http://blog.naver.com/itexpert2007/30020808814
Mysql 관리 툴(SQLyog)
http://www.webyog.com/en/ > 상단 Downloads 선택 >SQLyog MySQL GUI - Community Edition >Binaries - Windows 다운로드 및 설치
