출처 : http://overoid.tistory.com/tag/total

SQLite Databae에 대한 다섯번째 포스트입니다. 이번 포스트에서는 SQLite의 Core 함수에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1.Core Function

 coalesce(X,Y,...) coalesce() 함수는 Argument 중에서 첫번째로 Not Null인 Argument값을 리턴하는 함수입니다. 만일 모든 인자가 null이면 null을 리턴합니다.
 ifnull(X,Y) ifnull() 함수는 두 인자중에서 첫번째로 Not Null인 인자값을 리턴합니다. 만일, 둘다 null이면 null을 리턴합니다. Ifnull() 함수는 인자가 두개인 coalesce() 함수와 동일합니다.
 length(X) 길이값을 리턴하는 함수입니다. 만일 X 인자가 null이면 null을 리턴합니다.
 like(X,Y)
 like(X,Y,Z)
Like 함수는 “Y LIKE X [ESCAPE Z]”구문과 동일합니다.
 lower(x) 소문자로 변환 합니다.
 upper(X) 대문자로 변환 합니다.
 ltrimX)
 ltrim(X,Y)
ltrim(X)는 X 값 중 왼쪽편의 공백을 제거하는 함수입니다.
ltrim(X,Y)는 X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수입니다.
 
select ltrim(" ZZZZabcZZ ", " aZ")
=> 좌측문자열에서 부터 공백,a,Z 문자열이 있으면 제거하고, 처음으로 공백,a,Z가 아닌 문자열부터 출력합니다. 즉, “bcZZ”가 출력됩니다.
ltrim(" ZZZZabcZZ ", " ") 는 ltrim(" ZZZZabcZZ ")과 동일합니다.
 rtrim(X)
 rtrim(X,Y)
rtrim(X)는 우측편 공백 제거
rtrim(X,Y)는 ltrim(X,Y)와 동일한 방식이지만 우측편부터 매칭되는 글자를 제거합니다. 예를들어 select rtrim(" ZZZZabcZZ ", " Z") 문장은 우측편부터 공백과 Z를 빼고 처음으로 공백과 Z가 아닌 글자, 즉 c 까지 글자가 나타납니다. 결과값: “ ZZZZabc”
 trim(X)
 trim(X,Y)
trim(X)는 양쪽 공백 제거
trim(X,Y) 는 Y에 해당되는 글자를 양쪽 끝에서 부터 제거하고 나머지 글자만 리턴함. 예) trim(" ZZZZabcZZ ", " Z") => “abc” trim은 ltrim과 rtrim을 각각 적용한것과 동일한 결과가 나타납니다.
 max(X,Y,..) 인자값들 중 최대값을 리턴합니다.

create table t1 (coll integer, col2 integer, col3 integer);

insert into t1 values(1,2,3);
insert into t1 values(5,3,1);

select max(col1, col2, col3) from t1;
 min(X,Y,...) 인자값들 중 최소값을 리턴합니다.
 nullif(X,Y) 두 인자가 서로 같으면 null을 리턴, 서로 다르면 X값을 리턴합니다.
nullif('x','y') => ‘x’ , nullif('x','x') => null 리턴
 quote(X) Quote()함수는 single quotation을 escape 해줍니다. ‘값을 ‘’ 로 변경합니다.
Insert나 update 시에 사용하면 유용할 듯 합니다.
select quote("girl's mouse") => 'girl''s mouse'
 random(*) -9223372036854775808 부터 +9223372036854775807 숫자 사이의 임의의 수를 리턴합니다.
 randomblob(N) N으로 지정된 bytes의 랜덤 바이너리 데이터를 생성합니다.
 hex(X) 바이너리 값을 hex 값으로 변경합니다.
select hex(randomblob(16))
 replace(X,Y,Z) X 문자열 중에서 Y문자열을 Z로 변경합니다.
select replace('1/12/2009','1','x') => "x/x2/2009"
 round(X)
 round(X,Y)
반올림 함수. Y는 소수점 자리. Y가 없으면 0으로 처리합니다.
Round(3.5) => 4, round(2.555, 2) => 2.56
 substr(X,Y)
 substr(X,Y,Z)
substr() 함수는 X문자열 중에서 Y번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수입니다. Z가 생략되면 Y번째 문자열부터 문자열 끝까지 리턴합니다. Y의 최소값은 1입니다. 자바에서는 0으로 시작하지만 SQLite에서는 1부터 인덱스가 시작합니다. 만일 Y가 –(마이너스)값이면 문자열 우측끝부터 카운팅을 시작합니다.
select substr("string", 1, 3) => str
select substr("string", 0, 3) => st
select substr("string", -1, 3) => g
select substr("string", -3, 3) => ing
select substr("string", 2) => tring
 typeof(X)  X 표현식에 대한 데이터 타입을 리턴합니다. 리턴값은 “null”, “integer”, “real”, “text”, “blob” 중의 하나입니다.

2. Aggregation Function (집합 함수)

avg(X) 그룹내의 Not Null값의 평균값을 리턴합니다. X 컬럼값이 문자열이나 BLOB이면 0으로 간주하고 처리합니다.
count(*)
count(X)
count(X)는 X가 Not Null값을 가진 레코드의 개수 리턴합니다.
count(*)는 그룹내의 모든 rows의 수를 리턴합니다.
group_concat(X)
group_concat(X,Y)
X가 not null인 경우 그룹내의 모든 문자열을 콤마(,)를 구분자로 해서 문자열을 합쳐서 리턴합니다. Y가 주어지면 Y값이 구분자로 처리됩니다.
max(X) 그룹내의 값들중 최대값을 리턴합니다.
min(X) 그룹내의 값들중 최소값을 리턴합니다. 모든값이 Null이면 Null을 리턴합니다.
sum(X)
total(X)
Sum과 total은 그룹내의 Not Null값의 합계를 리턴합니다. X의 모든 값이 Null인 경우 Sum()은 Null을 리턴하고 Total()은 0.0을 리턴합니다.

/* Aggregation Function Test */

create table t1 (
type integer,
id integer,
name varchar(10),
value real,
data text
);

insert into t1 values(1,1,'a',0.0,'1');
insert into t1 values(1,2,'b',3.5,NULL);
insert into t1 values(1,3,NULL,3.1,'2');
insert into t1 values(1,4,'c',3.5,'10');
insert into t1 values(1,5,'d',NULL,NULL);

-- count(*)와 count(X)의 차이 비교
select count(*), count(name), count(data)
from t1
group by type

-- result
count(*) count(name) count(data)
5 4 3

-- group_concat test query
select group_concat(id,' > ') as exp
from t1;

-- result
exp
1 > 2 > 3 > 4 > 5

select group_concat(data,' > ') exp
from t1
group by type

-- result
exp
1 > 2 > 10

/* sum(), tatal() function test */
select sum(data), total(data)
from t1
where id in (2,5)
group by type;

-- result
sum(data) total(data)
<null>    0

생각보다 SQLite에서 지원되는 내장 함수가 너무 적습니다. SQLite는 각 언어별로 확장 기능을 가지고 있긴 하지만, 쉽게 사용하는게 아니라서.. 한번의 SQL로 처리 가능한 것을 상당 부분 프로그램 코드에서 처리해야 하지 않을까 싶습니다. 그럼에도 불구하고 group_concat() 같은 기능의 함수는 Oracle에서도 11g가 되어서야 내장 함수로 제공할 정도로 대부분의 DB에서 제공하지 않는 함수인데.. SQLite에서 구현이 되어 있다는 게 놀라울 따름입니다.

이것으로 SQLite Function 부분을 마치며, 다음 포스트에서는 DateTime 및 DateTime Formatting에 대해서 알아보도록 하겠습니다.



저작자 표시
신고


두개의 서버가 있고 하나를 백업디비로 할때 사용했다.
미러링이라고 보면 된다. 양방향 동기화는 아니고 마스터서버에서 슬레이브서버로 복사한다는 개념(?)..
간단한 설정 방법을 남긴다.

♦ MySQL Replication 설정
Master Mysql 5.0.45 IP : xxx.xxx.xxx.xxx
Slave Mysql 5.0.45 IP : xxx.xxx.xxx.xxx
참고 : Mysql 버전이 서로 다를 경우, 높은 버전은 Slave만 가능하다.

♦ Master Server 설정
my.cnf 파일을 편집한다.
# vi /etc/my.cnf
-------------------------------
server-id = 1                          1로 셋팅한다.
log-bin=mysql-bin                      주석(#)처리가 되어 있다면 풀어준다
-------------------------------
위 속성은 중복 되면 안된다. 전체파일을 잘 살펴 보아야 한다.

mysql 접속후 슬레이브에서 접속할 계정을 만든다.
# mysql> GRANT REPLICATION SLAVE ON db명.* TO 계정아이디@'%' IDENTIFIED BY '비번';
(%대신 IP주소를 넣으면 그 IP로부터 접속하는 slave에 대해서만 접속을 허용하겠다는 의미)
# mysql>FLUSH TABLES WITH READ LOCK;
# mysql>show master status;
-----------------------------------------------------------------
   File                Postion          Binlog_Do_DB         Binlog_Ignore_DB
   mysql-bin.000003       98
-----------------------------------------------------------------
위 출력물은 컴퓨터마다 다를 것이다. 슬레이브 서버에 적용시킬때 쓰인다.

이제 마스터 서버의 db데이터를 덤프한다.
# mysqldump -u root -p db명 > 파일명.sql
# mysql>UNLOCK TABLES;

♦ Slave Server 설정
my.cnf 파일을 편집한다.
# vi /etc/my.cnf
---------------------------------
server-id = 2                            이줄이 없을 경우나 1로 셋팅 되어있을 경우나 모두 2로 셋팅
replicate-do-db='db명'            전체 디비를 설정할려면 이줄은 삭제, 한 개의 디비만 리플리케이션 시킬려면        디비명을 적는다. 2개 이상의 경우 다음줄부터 똑같이 적는다.
---------------------------------
마스터 서버에서 덤프한 파일을 이용하여 복구한다.
# mysql -u root -p db명 < 파일명.sql

# mysql>CHANGE MASTER TO
 ->MASTER_HOST='192.168.0.10',
 ->MASTER_USER='계정명',
 ->MASTER_PORT=3306,
 ->MASTER_PASSWORD='패스워드',
 ->MASTER_LOG_FILE='mysql-bin.000003', 마스터의 상태보기 명령에서 나온 파일명
 ->MASTER_LOG_POS=98;   마스터의 상태보기 명령에서 나온 포지션

# mysql> SHOW SLAVE STATUS\G;
♦ Master & Slave 설정확인
마스터 # mysql>SHOW PROCESSLIST\G;
슬레이브 # mysql>SHOW PROCESSLIST\G;

이후 직접 마스터서버에서 insert 또는 delete를 이용하여 테스트를 해본다.

♦ 필수 ♦
mysql-bin.00003 등 뒤부분의 숫자가 올라가면서 파일이 생성된다. 또한 마스터 서버에서 삽입, 삭제, 업데이트 되는 부분은 이 파일을 이용하여 슬레이브 서버에 동기화를 시킨다. 마스터 서버가 리스타트 될시 슬레이브 서버와 연결된 부분이 끊기게 된다.
이때는 마스터 서버에서 # mysql>show master status; 를 이용하여 파일명과 포지션을 획득하고
슬레이브 서버에서
# mysql> stop slave;          중지시키고
# mysql>CHANGE MASTER TO
 ->MASTER_HOST='192.168.0.10',
 ->MASTER_USER='계정명',
 ->MASTER_PORT=3306,
 ->MASTER_PASSWORD='패스워드',
 ->MASTER_LOG_FILE='mysql-bin.000003', 마스터의 상태보기 명령에서 나온 파일명
 ->MASTER_LOG_POS=98;   마스터의 상태보기 명령에서 나온 포지션
CHANGE 명령을 이용하여 파일과 포지션을 다시 셋팅한다.

# mysql> start slave;

 

저작자 표시
신고