'SQL'에 해당되는 글 99건

DB 에 있는 데이터를 전부 다른 DB로 옮기고 싶다면 어떻게 해야 할까?

phpmyadmin 을 이용하여 DB에 있는 테이블 구조와 데이터 전부를 파일로 저장하고 나서,

다른 DB를 phpmyadmin 상에서 생성한다.

그리고 나서 업로드 기능을 이용하여 SQL 파일을 전부 읽어들인다.

MySQL 에서 테이블 구조 그대로 복사하고자 할 경우가 있다.

이럴 경우에 phpmyadmin 을 이용하면 간단하게 해결할 수가 있다.

하지만 MySQL 기본 명령어를 알아둘 필요도 있으므로 기록해둔다.

CREATE TABLE `test`.`bbs_category` (
`uid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`display` tinyint( 4 ) NOT NULL DEFAULT '1',
`classname` varchar( 60 ) NOT NULL ,
`relateduid` int( 11 ) NOT NULL DEFAULT '0',
`classname_eng` varchar( 80 ) DEFAULT NULL ,
`comment` varchar( 255 ) DEFAULT NULL ,
PRIMARY KEY ( `uid` ) ,
KEY `classid` ( `relateduid` ) ,
KEY `classname` ( `classname` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

테이블 구조가 서로 동일한 경우에는 아래 명령어로 데이터가 그대로 복사한다.

만약, 같은 DB내에서 복사할 경우에는 `DB명`은 생략해도 된다.

INSERT INTO `복사할DB명`.`복사할 테이블명`
SELECT *
FROM `원본DB명`.`원본테이블명` ;

INSERT INTO `test`.`bbs_category`
SELECT *
FROM `data`.`bbs_category` ;

테이블의 엔진 형태를 변경하는 방법

ALTER TABLE `bbs_category` ENGINE = MYISAM;
ALTER TABLE `bbs_category` ENGINE = InnoDB;

MySQL 기본 인코딩이 어떻게 설정되어 있는지 확인하는 명령어

mysql> show variables like 'c%';

개별 데이터베이스의 기본 인코딩 값을 UTF-8으로 설정

mysql > ALTER DATABASE `DBname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql > ALTER DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

명령어를 통해서 MySQL 기본 인코딩을 변경하는 걸 처리했다.

Database 를 생성할 때 기본 인코딩 값을 UTF-8 로 설정하는 방법

mysql > CREATE DATABASE `DBname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

기본적으로 처리하기 위해서는 MySQL 환경설정변수 파일(my.cnf)을 수정해줘야 한다.

collation_server=utf8_unicode_ci
character_set_server=utf8
#collation_server=euckr_korean_ci;
#character_set_server=euckr;

[client]
default-character-set=utf8
#default-character-set=euckr

환경설정 정보를 수정해주고 나면 mysql 을 재기동 해줘야 한다.

서버 문자셋과 클라이언트 문자셋이 서로 일치하는지 확인하는 명령어 status 를 통해 확인해보면 서로 일치하는 걸 알 수가 있다.

MySQL 의 버전을 알아보는 명령어

mysql > SHOW VARIABLES LIKE "%version%";

MySQL 뿐만 아니라 오라클에서도 테이블 구조 보기 명령어는 같다.

mysql > desc 테이블명;  -- 해당 테이블로 이동을 한 후 보기를 한 경우

mysql > desc DB명.테이블명;  -- 다른 DB에서 테이블 구조 조회

mysql > explain DB명.테이블명;

mysql > describe DB명.테이블명;

MySQL 에서 테이블에 칼럼을 추가/삭제/수정하고자 할 경우 명령어다.

홈페이지 버전업을 하면서 기존 테이블에 필드를 추가해둔 것이 있어서 추가된 필드만 별도로 MySQL 상에서 미리 작업하면 편리할 거 같아서 테스트해보고 기록해둔다.

// 칼럼 추가

ALTER TABLE `테이블명` ADD `추가할 칼럼명` 칼럼 타입 AFTER `현재 칼럼명`;
ALTER TABLE `rb_bskrbbs_data` ADD `hidden1` TINYINT( 4 ) NOT NULL DEFAULT '0' AFTER `hidden`;
ALTER TABLE `rb_bskrbbs_data` ADD `category1` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category`;


한번에 여러개 칼럼 추가할 때
ALTER TABLE `rb_bskrbbs_data`
ADD `category1` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category` ,
ADD `category2` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category1` ,
ADD `category3` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `category2`;

// 칼럼 삭제

ALTER TABLE `테이블명` DROP `칼럼명`;

ALTER TABLE `rb_bskrbbs_data` DROP `category1`;

한번에 여러개 칼럼 삭제할 때

ALTER TABLE `rb_bskrbbs_data`
  DROP `category1`,
  DROP `category2`,
  DROP `category3`;

// 컬럼명 변경 및 타입 변경
ALTER TABLE `테이블명` change `컬럼명` `변경할컬럼명` varchar(20);

ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category1` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category4` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `rb_bskrbbs_data` CHANGE `category4` `category1` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

// 컬럼 타입 수정
ALTER TABLE `테이블명` modify `컬럼명` varchar(14);

ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category1` CHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `rb_bskrbbs_data` CHANGE `category1` `category1` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE `rb_bskrbbs_data` modify `category1` VARCHAR( 120 );
ALTER TABLE `rb_bskrbbs_data` modify `category1` CHAR( 100 );
ALTER TABLE `rb_bskrbbs_data` modify `category1` VARCHAR( 100 );

// 테이블명 수정
ALTER TABLE `테이블명` rename `변경할테이블명`;

// 테이블 삭제
DROP TABLE `테이블명`;

phpMyAdmin 을 이용하면 위 내용을 알 필요가 없이 처리할 수가 있다.

UNION 은 서로 다른 테이블에서 같은 유형의 결과를 얻고 싶을 때 사용한다.

물론 같은 테이블을 UNION 으로 해서 결과를 얻어낼 수도 있다.

테이블 A 와 테이블 B 가 있는데, 테이블 A의 칼럼명과 테이블 B의 칼럼명이 서로 달라도 칼럼의 데이터형식이 호환되면 UNION 으로 결과를 얻어낼 수가 있다.

물론, 테이블 A 와 테이블 B의 칼럼수가 서로 달라도 상관없다.

하지만 화면에 출력될 칼럼 수는 동일하게 개수를 맞춰야 한다.

SELECT column_1, column_2 FROM 테이블 A WHERE 조건

UNION

SELECT column_1, column_2 FROM 테이블 B WHERE 조건;

SELECT column_1, column_2 FROM 테이블 A WHERE 조건

UNION ALL

SELECT column_1, column_2 FROM 테이블 B WHERE 조건;

UNION 과 UNION ALL 의 차이점은 조회결과에 중복을 포함하지 않느냐, 포함하느냐 이다.

중복을 포함해서 결과를 가져오라고 하면 속도는 당연히 더 빠르다.

하지만, 두 개의 테이블에서 E-Mail 을 추출해서 메일을 보낸다고 하면 중복이 발생하면 안되므로 UNION 을 사용해야 한다. UINO 으로 결합하면 중복을 제거한다.

UNION 으로 결합해서 나온 결과를 정렬을 한다면 어떻게 해야 할까?

SELECT column_1, column_2 FROM 테이블 A WHERE 조건

UNION

SELECT column_1, column_2 FROM 테이블 B WHERE 조건

ORDER BY column_1, column_2;

MySQL 에서 DB에 쌓이는 게시물의 가장 최근 10개의 글을 보여주는 쿼리문

SELECT 칼럼1, 칼럼2 FROM 테이블명 ORDER BY uid DESC LIMIT 10;

PHP 와 연동하여 LIMIT 다음에 나오는 숫자를 변수로 받으면 숫자만큼 최근 게시물을 화면에 뿌려줄 수 있다.

최근 게시물 10개를 랜덤하게 뿌려주고 싶을 때의 쿼리문

(SELECT 칼럼1, 칼럼2 FROM 테이블명 ORDER BY uid DESC LIMIT 10) order by rand();

VBA 에서는 Instr 함수는 셀에서 찾는 위치를 반환해주는 기능이라 유용하게 사용하고 있다.

MySQL 에서도 InStr 함수가 있어서 테스트를 해보니 VBA 에서 사용하는 InStr 함수와 똑같다.

<그림출처 : http://www.w3resource.com/mysql/string-functions/mysql-instr-function.php>

select cat1name, cat2name,findtext from catkeyword where instr('뭔가 냄새가 나',findtext) > 0;

VBA 와 연동하여 SELECT 문의 결과를 엑셀 셀에다가 뿌려줄 수 있다.

물론 MySQL 에 직접 접속하여 테스트를 해보고나서 엑셀 VBA 와 연동하여 테스트를 해봤다.

키워드를 MySQl 테이블에 계속 추가하고, 엑셀 VBA 에서 포함여부를 체크하는 루틴을 만들면 된다.

Application.StatusBar = "셀: " & C.Address(0, 0) & " / " & C.Text & " 진행중..."
sSQL = "SELECT cat1name, cat2name"
sSQL = sSQL & " FROM catkeyword"
sSQL = sSQL & " Where instr('" & C.Text & "',findtext) > 0 "

1. 중복된 갯수가 n 개 이상인

SELECT 필드명, count(*) as 변수명 FROM 테이블명 GROUP BY 필드명 HAVING 변수명 > n;

또는

SELECT 필드명, count(*)  FROM 테이블명 GROUP BY 필드명 HAVING count(*) > n;

// 중복된 값을 찾고자 하는 필드명을 지정한다. 

// 가령 mp3rand 라고 지정하면 GROUP BY mp3rand

칼럼에 INDEX 를 걸기 전과 건 후의 속도 차이를 보면 엄청난 차이가 발생한 걸 알 수 있다.

2. 중복된 데이터 추출

3. 중복된 것 모두 찾기

SELECT 필드명, count(*) FROM 테이블명 GROUP BY 필드명

영문, 한글자까지 문자의 정확한 길이를 알려면 CHAR_LENGTH() 함수를 사용한다.

SELECT eng, CHAR_LENGTH(eng) FROM data WHERE uid =번호;

어떤 정보를 조회, 수정, 삭제 하는 업무를 수행할 때, 하나의 테이블만 가지고 작업을 하는 경우는 거의 없다.

대부분 두개 이상의 테이블을 연결하여 원하는 데이터를 조회하거나 변경하고 삭제한다.

양쪽 테이블에 같은 조건이 존재할 경우의 값만 가져오는 Join 을 EQUI Join (등가 조인)이라고 한다.

DBMS가 먼저 접근하는 테이블을 드라이빙 테이블이라고 하는데, 드라이빙 테이블을 어떤 테이블로 하느냐에 따라 전체적인 조인 수행 속도에 영향을 끼칠 수 있다.

기본적인 구문은

SELECT column_name(s)
FROM tableA
INNER JOIN tableB
ON tableA.column_name = tableB.column_name;

w3schools.com 에 이렇게 나오는데 여기서 column_nams(s) 라는 말이 잘 와닿지 않을 수 있다.

아래 JOIN 구문을 보면 이해가 명확해진다.

테이블명을 칼럼마다 전부 적어주려면 구문이 길어지므로 별칭으로 간단하게 a, b 등을 사용한다.

SELECT 하는 column 이 테이블A 에 있는 column 인지 테이블B 에 있는 column 인지 명확하게 해주어야 한다.

칼럼(column)이 명확하게 구분되는 경우에는 안적어줘도 되지만, 명확하게 하는게 좋다. 테이블A, 테이블B가 다른 테이블이 아니라 같은 테이블을 조인하는 걸 Self JOIN 이라고 한다.

select a.no, b.mp3 from 테이블A a, 테이블B b where a.no = b.relateduid;

select a.no, b.mp3 from 테이블A a JOIN 테이블B b ON a.no = b.relateduid;   -- ANSI JOIN

위 2개의 JOIN Query 문은 동일하다.

테이블A 와 테이블B 사이의 콤마 대신에 JOIN, Where 대신에 ON 을 사용하게 다르다.

만약, 3개의 테이블을 JOIN 한다고 하면....

select a.no, b.mp3, c.membername from 테이블A a, 테이블B b, 테이블C c WHERE a.no = b.relateduid AND a.relateduid = c.memberid;

select a.no, b.mp3, c.membername from 테이블A a JOIN 테이블B b ON a.no = b.relateduid JOIN 테이블C c ON a.relateduid = c.memberid;

테이블A 의 특정 칼럼의 데이터를 업데이트 해야 하는 경우

다른 테이블(테이블 B)의 데이터를 바탕으로 테이블 A의 특정 칼럼을 업데이트 해야 하는 경우가 있다.

UPDATE 테이블명A a JOIN 테이블명B b
ON a.조인할 컬럼명 = b.조인할 컬럼명
SET 변경할 컬럼명 = 변경할값
( WHERE 절 )

의 쿼리문으로 해결할 수 있다.

약  4만건의 데이터를 JOIN Update 를 했더니 0.92초 걸렸다.

테이블 칼럼에 중복되는 자료가 존재하면 안되기 때문에 테이블 설계시에 중복발생이 되지 않도록 인덱스를 걸어두었다.

이미 등록한 자료를 다시 등록을 시도해봤다.

load data local infile '/home/httpd/test/test.csv'
into table `rand_mp3file`
fields terminated by ';'  -- 칼럼을 구분할 구분자를 지정
lines terminated by '\r\n'   -- Row(행)을 구분해줄 구분자를 지정
ignore 1 lines     -- 등록할 파일의 첫줄을 무시하고 등록하라
 (`relatedno` ,
  `mp3file`  ,
  `mp3rand`
);

총 5개의 레코드인데 전부 Skipped 된 걸로 나온다.

즉 등록된 자료는 하나도 없는 것으로 나온다.

UNIQUE INDEX 를 추가하고 업데이트 방식을 해보고자 한다.

중복된 자료가 있을 경우와 중복되지 않은 자료가 있을 경우 어떤 현상이 생기는지 궁금해졌다.

mysql > show index from 테이블명;

첫번째 데이터 등록은 잘 등록되었는데, 추가 자료를 등록할 때는 어떻게 등록해야 UNIQUE 하게 자료가 잘 등록될까?

등록하기 전에 이 자료가 중복이 발생되는 자료인지 중복검사를 하는 방법을 알아봐야겠다.

물론 엑셀 VBA 와 MySQL 을 연결해서 검사를 하면 중복 여부는 확인이 가능하다.

문제는 속도가 너무 느리다는 단점이 있다.

하나의 데이터를 검색할 때마다 테이블의 자료 갯수만큼 반복해야 하니까~~

NULL 은 INDEX 가 만들어지지 않는다. 따라서 NULL 이 많은 항목은 INDEX 로는 부적합하다.

INDEX 와 JOIN 에 대한 이해만 정확하게 해도 최적화된 SQL 을 작성하는데 큰 도움이 된다.

INDEX란, 데이터의 고유성을 확인하거나 검색 성능을 향상시키기 위해 사용하는 객체이다. 특정 칼럼(column)에 INDEX를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.

INDEX를 생성할 때 수행하는 정렬 작업은 이후 해당 데이터를 빠르게 검색하는데 중요한 역할을 한다.

INDEX 설계는 이론적으로 설계 단계에서 완성해야 한다. 프로젝트 운영중에도 INDEX 설계를 해야 하는 경우는 자주 발생한다. 요구사항 변경으로 설계 단계에서 예측하지 못했던 INDEX 가 필요해질 수 있기 때문이다.

INDEX의 가장 중요한 기능은 데이터의 조회 속도를 빠르게 하는 것이다.

기본적으로 조건절(WHERE) 에 등장하는 칼럼과 조인 조건으로 사용되는 칼럼에는 INDEX를 만드는 것이 성능면에서 유리하다.

조인 조건으로 사용된 칼럼에 INDEX 가 없으면 FULL TABLE SCAN 이 발생하면서 성능이 저하된다.

스칼라 서브쿼리 내에서 사용하는 조인 조건 칼럼에도 INDEX 를 생성해야 한다.

조회는 거의 하지 않고 데이터의 입력 및 변경 작업을 주로 하는 테이블은 최소한의 INDEX를 생성하는 것이 좋다.

ORDER BY 절에 제시되는 칼럼에도 INDEX를 생성하면 정렬 부하를 줄일 수 있지만, 조건절에 자주 등장하는 칼럼과 적절하게 조합하여 INDEX 를 만들면 훨씬 큰 성능을 기대할 수 있다.

UNIQUE INDEX 란 인덱스 안에 있는 Key 값에 중복되는 데이터가 없다는 뜻이다. UINQUE INDEX 가 설정되어 있다는 뜻은 해당 테이블의 칼럼에 중복된 값이 없다는 뜻이고, 앞으로도 중복된 값이 들어올 수 없다는 뜻이다.

mysql > show index from 테이블명;  -- 테이블에 생성된 인덱스 조회

mysql > alter table 테이블명 ADD UNIQUE INDEX (칼럼명);

(칼럼1, 칼럼2) 등 다중으로 할 수도 있다.

mysql > alter table 테이블명 ADD INDEX (칼럼명);

mysql > alter table 테이블명 DROP INDEX 칼럼명;

INDEX 를 추가/삭제를 한 테이블을 내보내기를 해서 보자.

CREATE TABLE IF NOT EXISTS `rand_mp3file` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `relatedno` int(11) NOT NULL DEFAULT '0',
  `mp3file` varchar(12) NOT NULL,
  `mp3rand` varchar(30) NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `display` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `mp3rand` (`mp3rand`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

처음 테이블 생성시에 INDEX 추가 명령어를 몰라도 추가하고 난 다음에 테이블을 내보내기해서 보면 인덱스가 어떻게 설정되어 있는지 확인할 수 있다.

phpmyadmin 상에서는 더 쉽게 인덱스를 추가할 수 있다.

그런데 간혹 동작이 안되는 경우가 있다. MySQL 에서 직접 해보면 에러가 발생해서 안되는 것이었다.

에러 메시지가 어떤 것인지 확인이 안되다보니...

인덱스(INDEX)를 사용하지 못하는 경우

ㅇ 칼럼의 내외부 변형

ㅇ LIKE 검색시 앞에 %를 사용

ㅇ 여러 칼럼에 대한 OR 조건 사용 : OR 를 사용하여 여러 칼럼에 대한 조건을 나열할 때,

     INDEX를 포함하지 않는 칼럼이 있다면, FULL TABLE SCAN 을 한다.

     OR 로 연결된 조건을 비교하는 작업을 수행할 때는 OR 뒤부터 비교를 시작하므로,

     결과를 만족하는 값이 많은 조건을 뒤에 두는 것이 성능면에서 유리하다.

ㅇ 부정형(<>, !=, NOT IN, NOT EXISTS) 비교, IS NULL, IS NOT NULL 을 사용한 비교

MySQL 의 날짜, 시간함수에 대해 알아보자.

from dual 의 dual 은 실제 테이블이 아니라, MySQL 에서 기본 제공한다. MySQL 이 제공하는 기본 함수 이해는 직접 명령어를 입력해보는 것이다.

1. 현재 날짜

select curdate() from dual;

2. 현재 시간

select curtime() from dual;

3. now()

select now() from dual;

4. 날짜 형식 변환

select DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') from dual;

* Y : 4자리 연동, y : 2자리 연도, m : 2자리 월

   H : 24시간 형식, h : 12시간 형식, i : 2자리 분, s : 2자리 초

select date_format(now(),'%Y-%m-%d') from dual;

DB에 있는 특정 필드의 값을 표현한다고 할때를 가정하고 직접 날짜를 입력했다.

5. 날짜의 요일을 숫자로 반환하는 dayofweek

요일의 시작은 일요일 1, 월요일 2, 화요일 3, 수요일 4, 목요일 5, 금요일 6, 토요일이 7이다.

select dayofweek(sysdate()) from dual;

* sysdate()는 이 함수가 실행되는 서버 시간을 가져오고, 다른 함수들은 쿼리가 실행되는 시간을 가져온다.

select dayofweek('20140527') from dual;

6. 날짜의 요일을 숫자로 반환하는 Weekday

월요일 0, 화요일 1, 수요일 2, 목요일 3, 금요일 4, 토요일 5, 일요일 6을 반환

select weekday(sysdate()) from dual;

select dayofyear(sysdate()) from dual;

8. 1월 1일부터 해당날짜까지의 주수를 반환

 select week(sysdate(), 0) from dual;

9. 특정한 날짜 기준으로 이전/이후 날짜를 계산

날짜를 계산하기 위해서 보통 date_add 함수를 사용한다.

select now(), DATE_ADD(now(), interval 3 month) from dual; -- 지금날짜와 3개월 후의 날짜를 출력

select date_add(sysdate(), interval -10 day) from dual; -- 10일전의 날짜

select date_sub(sysdate(), interval 1 day) from dual;

 select date_add(sysdate(), interval 1 month) from dual;

* interval 숫자 Type → second(초), minute(분), hour(시간), day(일), week(주), month(월), year(년)

select * from tableName
where (date between 시작날짜 AND 마지막날짜)
AND IF(date=시작날짜, time>=시작시간, IF(date=마지막날짜, time<=마지막시간, 1))";

인터넷을 검색해보면 Optimize는 너무 자주해도 안좋고 적당한 선에서 하면 좋다고 되어 있다.

MySQL DB 타입이 innoDB 인 경우에는 Optimize(최적화)를 안해도 된다고 한다.

innoDB는 락에 대해서 다양하게 관리를 해주기때문에 (행단위) 데이타 변경에 강하고

MyISAM 은 읽기위주의 솔루션에서 강점을 발휘한다.

// MySQL 특정 DB의 모든 테이블 최적화
mysqlcheck -u root -p --optimize DB명;

mysql의 replace() 함수는 문자열 치환 함수이다.
사용방법은 오라클의 regexp_replace와 동일하다.

ㅇ 치환할 데이터 확인하기

select replace(컬럼명, '원래 문자열', '바꿀 문자열') from 테이블명;

ㅇ 데이터 치환하기

update 테이블명 SET 칼럼명 = replace(칼럼명, '원래 문자열','바꿀 문자열') WHERE 조건;

MySQL 테이블의 Type을 잘못 지정해서 에러가 발생한 원인을 찾느라고 개고생을 했다.

PHP 프로그램으로 구조변경을 하면서 새로 코딩을 하고, 테스트를 하는데 엉뚱한 결과가 계속 나왔다.

PHP 코드를 계속 살펴보고, 잘못된 것이 없는데 왜 그럴까 하면서

테이블 데이터를 Truncate 로 날려보기를 수십번 ...

이 방법 저 방법을 사용하면서 코드 에러 나는 부분을 찾아보고자

echo "<script>alert('$content')</script>";

에러가 발생하는 부분에 자바스크립트로 찍어가면서 확인을 해봤다.

팝업창에 나온 숫자는 분명이 280 이다.

그런데 MySQL DB 테이블에는 숫자가 127로 찍힌다.

순간 불현듯이 떠오른건 테이블의 타입을 tinyint(4) 로 선언하게 기억났다.

tinyint 는 - 128 ~ 127 사이의 값을 DB 테이블에 저장한다.

숫자가 280 인데 DB가 받아들일 수 있는 최대 숫자가 127 이다보니 자동으로 숫자를 변환해서 127로 계속해서 기록을 했던 것이다. ㅠㅠㅠㅠ

이것 때문에 2시간 이상을 헛고생을 했다. 맥이 빠진다. 해결해야 할 사항이 아직도 많은데....

프로그램의 로직에는 아무런 문제가 없는데, PHP 문법을 잘모르거나 자바스크립트를 잘 몰라서 헤매는 경우가 너무 많다.

그렇다고 집중해서 코딩을 배울 나이도 아니고... 흐미

// 전화번호 마스킹처리(**) 하여 보여주기
// REPLACE(바꿀려는 필드명, '수정전 문자열', '수정후 문자열')

use 접속할DB명;
select uid, replace(tel,substring(tel,5,2),'**') from data limit 10;

select uid, replace(tel,right(tel,2),'**') from data limit 10;

select uid, tel, substring(tel,1,3),replace(tel,right(tel,2),'**') from data where tel LIKE '01%';

오라클 Decode 함수는 자주 사용하는 함수이므로 익혀두면 개발하면서 유용하게 사용할 수 있다.

decode 함수는 MySQL 이나 MS-SQL 에서는 사용할 수 없다.

하나의 값만 비교하는 경우

  decode(값, 조건, true return, false return)

여러 값과 비교하는 경우

  decode(값, 조건1, true return1, 조건2, true return2, false return)

오라클에는 left, right 가 없다. 오직 substr 로 해결해야 한다.

테이블에 정의되지 않은 칼럼, 1월 ~ 12월은 쿼리상에서 만들어낸 데이터이다. 이를 보통 Entity 재정의라고 한다.

SELECT
상품,
sum(decode(substr(접수일자, 1, 6), '201401', 1, 0)) jan,
sum(decode(substr(접수일자, 1, 6), '201402', 1, 0)) feb,
sum(decode(substr(접수일자, 1, 6), '201403', 1, 0)) mar,
sum(decode(substr(접수일자, 1, 6), '201404', 1, 0)) apr,
sum(decode(substr(접수일자, 1, 6), '201405', 1, 0)) may,
sum(decode(substr(접수일자, 1, 6), '201406', 1, 0)) jun,
sum(decode(substr(접수일자, 1, 6), '201407', 1, 0)) jul,
sum(decode(substr(접수일자, 1, 6), '201408', 1, 0)) aug,
sum(decode(substr(접수일자, 1, 6), '201409', 1, 0)) sep,
sum(decode(substr(접수일자, 1, 6), '201410', 1, 0)) oct,
sum(decode(substr(접수일자, 1, 6), '201411', 1, 0)) nov,
sum(decode(substr(접수일자, 1, 6), '201412', 1, 0)) dec
FROM
(
 SELECT 접수번호, 접수일자, 접수시간, 처리일자, 처리시간, 상품, 유형, 접수채널, 접수자명, 처리자명
 FROM goods
)
where 유형 = '해제요청' AND 상담 LIKE '%해제%'
GROUP BY 상품;

http://www.gurubee.net/lecture/1028

에 DECODE 와 CASE 함수에 대해 설명하고 있다.

select goods, count(*) from data group by goods;

하나의 칼럼을 다중 IF 문으로 처리하여 숫자 대신 문자로 표시하는 방법이다.

select goods, if(goods = 0, "미가입",if(goods = 1, "휴대폰", if(goods = 2, "API", if(goods=6, "타사", if(goods = 8, "분배", "해지"))))) as "휴대폰", count(*) from data group by goods;

또다른 방법은 CASE WHEN 을 사용하는 것이다.

select goods, case goods when 0 then "미가입" when 1 then "휴대폰" when 2 then "API" when 6 then "타사" when 8 then "분배" ELSE "해지" END as "휴대폰", count(*)  from data group by goods;

둘다 결과는 동일하게 나온다.

CASE WHEN END 를 사용하는 것이 좀더 편하고 좋다.