SULINUX를 DB서버로 사용하기

제목 : SULINUX를 DB서버로 사용하기

ㅇ 제작 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 서버관리팀

ㅇ SULINUX  홈페이지 : www.sulinux.net

ㅇ 리눅스포털 홈페이지 : www.superuser.co.kr

MySQL 

  

MySQL은 Database프로그램이다. MySQL을 이용하면 데이터 관리가 효과적으로 관리 할수 있다. MySQL도 별도의 사용자가 등록되어 사용되므로 MySQL사용자 설정은 SULinux의 SSU 유틸리티를 이용하여 간단히 설정 할 수 있다. 자세한 내용은 SSU의 “6-3-3. MySQL 사용자 설정”을 참고하기 바랍니다.

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)

데이터파일 실행(sql*loader기능)

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

데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력

데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

사용자 생성 & 사용자에게 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이라는 사용자를 생성

create database kang;

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

grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 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 on kang.* to kang@localhost identified by 'kang';

Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 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 "정규표현식";

오라클 sysdate와 동일

insert into test values('12', now());

유닉스 time()함수 리턴값 사용

FROM_UNIXTIME(954788684)

UNIX_TIMESTAMP("2001-04-04 :04:04:04")

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)

인덱스 생성

- 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은 빠르기 때문에 이 단점이 상쇄된다.

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가 이루어진다.

제목 : SULINUX를 DB서버로 사용하기

ㅇ 제작 : 리눅스포털(www.superuser.co.kr) 수퍼유저코리아 서버관리팀

ㅇ SULINUX  홈페이지 : www.sulinux.net

ㅇ 리눅스포털 홈페이지 : www.superuser.co.kr

MySQL 

  

MySQL은 Database프로그램이다. MySQL을 이용하면 데이터 관리가 효과적으로 관리 할수 있다. MySQL도 별도의 사용자가 등록되어 사용되므로 MySQL사용자 설정은 SULinux의 SSU 유틸리티를 이용하여 간단히 설정 할 수 있다. 자세한 내용은 SSU의 “6-3-3. MySQL 사용자 설정”을 참고하기 바랍니다.

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)

데이터파일 실행(sql*loader기능)

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

데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력

데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

사용자 생성 & 사용자에게 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이라는 사용자를 생성

create database kang;

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

grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 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 on kang.* to kang@localhost identified by 'kang';

Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 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 "정규표현식";

오라클 sysdate와 동일

insert into test values('12', now());

유닉스 time()함수 리턴값 사용

FROM_UNIXTIME(954788684)

UNIX_TIMESTAMP("2001-04-04 :04:04:04")

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)

인덱스 생성

- 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은 빠르기 때문에 이 단점이 상쇄된다.

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가 이루어진다.