'SQL'에 해당되는 글 102건

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

테이블 A

  a1 아이디
  a2 비밀번호
  a3 이름
  a4 자기소개

테이블 B
  b1 아이디
  b2 특기
  b3 취미


이렇게 두 테이블이 있을 때,

A 테이블의 a4 (자기소개) 필드의 데이터 뒤에 B테이블의 특기, 취미를 붙여넣고 싶다!

이럴 때 쿼리 한 문장으로 업데이트 할 수 있다.

두 테이블의 key 값은 아이디 A.a1 = B.b1
두 테이블을 조인(JOIN)할 때에는 서로 같이 묶어줄 칼럼이 있어야 한다.

UPDATE A, B SET A.a5 = CONCAT(A.a4, B.b2, B.b3) WHERE A.a1 = B.b1;

또는

UPDATE A INNER JOIN B ON A.a1 = B.b1 SET A.a5 = CONCAT(A.a4, B.b2, B.b3);

여기서 잠시 단순 Update 문과 비교해보면서 이해해보자.

Update 조인도 단순 Update 문과 다를게 하나도 없다. 참 쉽다.

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

UPDATE A, B

SET A.a5 = CONCAT(A.a4, B.b2, B.b3)

WHERE A.a1 = B.b1;

테이블이 1개 →테이블 2개 이상 으로 테이블이 좀더 늘어났다는 것

칼럼명은 어느 테이블의 칼럼인지 명확하게 구분해주고 있다는 것

조건절도 어느 테이블의 칼럼인지 명확하게 구분해주고 있다는 것

네이버지식인에 올라온 걸 테스트도 해볼겸 윈도우 기반 AutoSet9 의 phpmyadmin 기능 확인도 해볼겸 해서 간단한 테이블을 하나 만들었다.

-- 테이블 구조 `date`
CREATE TABLE IF NOT EXISTS `date` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `classcode` varchar(20) NOT NULL,
  `meeting_date` varchar(14) NOT NULL,
  `appointment_date` varchar(14) NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `classcode` (`classcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

테이블 생성과 동시에 Index 까지 설정했다.

phpmyadmin 에서는 인덱스 설정하는 것이 무척 쉽다.

하지만 테이블 설계하면서 인덱스(Index)까지 설정해주는 것이 좋다.

MySQL 명령어 상에서 데이터 Insert 는 아래와 같이 하면 된다.

INSERT INTO `date` (`uid`, `classcode`, `meeting_date`, `appointment_date`) VALUES
(1, 'y0001', '2015-06-01', '2015-05-01'),
(2, 'y0002', '2015-06-01', '2015-05-02');

테스트용으로 데이터를 추가한다고 하면 phpmyadmin 에서 쉽게 할 수 있다.

먼저 데이터베이스와 테이블을 선택한 다음에 아래 번호 순서대로 데이터를 입력해주기만 하면 된다.

4번을 눌러서 실행을 하고 나면 데이터가 입력되고

실제 MySQL Insert 명령을 통해서 입력되는 구문 형태를 보여준다.

여기서 uid 는 NULL 이라고 표기되면서 입력이 된 것을 알 수 있다.

이 의미는 자동으로 알아서 증가되도록 테이블 구조 설계를 했기 때문이다.

만약, php 와 연동하여 게시판 같은 테이블을 만든다고 하면, uid 칼럼은 데이터 입력시에는 고려하지 않아도 된다는 의미다. 물론 uid는 primary 키값이기 때문에 중요하다.

데이터가 잘 입력되었는지 확인해보자.

이제 지식인에 문의된 걸 해보자.

datediff 기능을 확인하기 위해 http://www.w3schools.com/sql/default.asp 에 접속하여

정의 및 사용법이 나온다. (http://www.w3schools.com/sql/func_datediff_mysql.asp 참조)

DATEDIFF(date1,date2)  -- MySQL

DATEDIFF(datepart,startdate,enddate)  -- MS-SQL Server

datepart : day, week, month, hour 등 어떤 기준으로 결과를 뽑아낼지 나온다.

이제 date 테이블에서 select 해보자.

select datediff(meeting_date, appointment_date) from date where classcode='y0001';

결과가 나왔다.

이제 테이블에서 약속일자(meeting_date) 와 약속(appointment_date) 날짜의 차이가 20 보다 작은 코드를 뽑아내고 싶다면 WHERE 조건절에 datediff 함수를 적용하면 된다.

select * from date where datediff(meeting_date, appointment_date) < 20;

윈도우 cmd 창을 띄우지 않고도 phpmyadmin 상에서 MySQL query 문 테스트가 얼마든지 가능하며 편하다.

MySQL 테이블 구조와 데이터를 내보내기를 하고 싶다면

본 게시글 작성을 위해 테스트한 테이블 구조 첨부하니 필요한 분은 받아서 테스트 해보세요..

testdb_date.sql

NULL 은 INDEX 만들어지지 않는다. NULL 이 많은 항목은 INDEX 부적합하다.

INDEX란, 데이터의 고유성을 확인하거나 검색 성능을 향상시키기 위해 사용하는 객체이다.

특정 칼럼에 INDEX를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.

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

속도 향상을 위해 index를 설정한다.
INDEX 를 걸었지만, SELECT 문이 제대로 인덱스를 타고 있는지 알아볼 필요가 있다.
MySQL 에서 쿼리의 실행 계획을 확인하려면 explain 명령을 사용하면 된다.

explain 명령은 SELECT 문 앞에 explain SELECT 를 붙이기만 하면 된다.

const : 매치되는 행이 하나만 존재

하나의 행이기 때문에 각 컬럼값은 나머지 연산에서 상수로 간주되며, 처음 한번만 읽어들이면 되기 때문에 엄청 빠르다.

possible_keys : 쿼리를 튜닝하는데 도움이 안되므로 possible_keys 칼럼은 그냥 무시해도 된다.

key : 사용된 인덱스를 나타내고, 쿼리를 튜닝할 때 key 칼럼에 의도했던 인덱스가 표시되는지

        확인하는 것이 중요하다.

        실행계획의 type 이 ALL 이면 key 칼럼은 NULL로 표시된다.

        실행계획의 type 이 index_merge 가 아닌 경우에는 반드시 테이블당 하나의 인덱스만 이용할 수 있다.

key_len : 사용된 가장 긴 key 부분을 나타. 실제 업무는 다중 칼럼으로 만들어진 인덱스가 더 많다.

             쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇개의 칼럼이 사용되었는지 알려준다.

ref : 접근 방법이 ref 방식이면 참조 조건으로 어떤 값이 제공되었는지 보여준다.

range : 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 <, >, <=, >=, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.

모든 쿼리가 이 접근 방법만 사용해도 어느 정도의 성능을 보장된다고 볼 수 있다.

- ref 컬럼은 이 타입의 조인에서 NULL 임

LIKE 검색을 할 경우 FULL SCAN 하는지 확인이 가능하다.

ALL : Full TABLE Scan 된다.

대부분의 경우에 아주 느린 성능을 보인다.가장 마지막에 선택되는 가장 비효율적인 방법이다. 빠른 응답을 사용자에게 보내줘야 하는 웹서비스와 같은 환경에서는 적합하지 않다.
LIKE 검색시 앞에 %를 붙이지 않으면 INDEX 를 탄다는 걸 알 수 있다.

이제 인덱스를 제거하고 다시 explain 해보자.

mysql > ALTER TABLE `bbs_file` DROP INDEX `mp3file`;

WHERE 조건에 사용되는 칼럼 mp3file 을 INDEX를 삭제하고 나서 LIKE 검색을 했더니 ALL(Full Scan)으로 변경되었다.

WHERE 조건에 사용되는 칼럼에 INDEX 가 설정되어 있어야만 검색속도가 빠르게 될 수 있다는 걸 확인했다.

mysql > ALTER TABLE `bbs_file` ADD UNIQUE (`mp3file`); -- mp3file 칼럼에 인덱스 추가

이제 두개의 테이블을 조인해서 원하는 결과를 조회하는 걸 해보자.

explain select d.uid, d.no, f.rand from bbs_data d, bbs_file f where d.no = f.no;
explain select d.uid, d.no, f.rand from bbs_file f, bbs_data d where f.no = d.no;

조인하는 테이블 순서는 상관이 없다는 걸 테스트해서 확인했다.

처음에는 ALL(Full Scan) 하고 다음에는 eq_ref 가 나왔다.

eq_ref : 조인수행을 위해 각 테이블에서 하나씩의 행만이 읽혀지는 형태.

조인(INNER JOIN) 에서 두번째 이후에 읽는 테이블(bbs_file)에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

- const 타입이외에 가장 훌륭한 조인타입이다.

- 조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우.

- 인덱스된 컬럼이 = 연산에 사용되는 경우.

- 비교되는 값은 상수이거나 이전조인결과의 컬럼값일 수 있다.

explain SELECT uid, no, (select classname from bbs_category where uid=bbs_data.category1) as cate1, (select classname from bbs_category where uid=bbs_data.category2) as cate2 from bbs_data;

더 많은 정보를 알고 싶으면 http://database.sarang.net/?inc=read&aid=24199&criteria=mysql 를 참조하세요.

성능 튜닝 문제 유형

ㅇ 조건절에서 비교하는 칼럼에 인덱스가 없는 경우

    - 조건절에 사용되는 칼럼 가운데 '='로 비교하는 칼럼을 선두에 두고 결합 인덱스를 생성하는 것이 좋다.

    - 조건절에서 사용하는 칼럼이 결합 인덱스의 선두 칼럼을 구성되어 있어야 성능 검색에 유리하다.

ㅇ 조인 조건으로 사용된 칼럼에 인덱스가 없는 경우

    - DBMS가 먼저 접근하는 테이블을 드라이빙 테이블이라고 한다.

    - 드라이빙 테이블을 어떤 테이블로 하느냐에 따라 전체적인 조인 수행 속도에 영향을 끼칠 수 있다.

ㅇ 스칼라 서브쿼리의 조건으로 사용된 칼럼에 인덱스가 없는 경우

ㅇ 조건절에 사용된 칼럼의 내/외부 변형으로 인덱스를 사용할 수 없는 경우

ㅇ IS NULL, IS NOT NULL을 사용한 비교로 인덱스를 사용할 수 없는 경우

ㅇ LIKE 로 비교하는 값의 앞에 %를 사용하여 인덱스를 사용할 수 없는 경우

    - 비교할 값의 앞에는 가급적 %를 사용하지 않아야 한다.

ㅇ OR로 연결된 조건 비교로 인덱스를 사용할 수 없는 경우

    - OR 구문을 사용해 여러 조건을 나열할 때에는,

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

    - OR로 연결된 조건을 비교하는 작업을 수행할 때에는 OR 뒤에서 부터 비교를 시작하기 때문이다.

ㅇ MINUS 구문 사용으로 인덱스를 사용할 수 없는 경우

ㅇ 부정형 비교로 인덱스를 사용할 수 없는 경우

    - <>, !=, NOT IN, NOT EXISTS 등의 표현식을 사용한 SQL

ㅇ 조인 후 GROUP BY 수행으로 비용이 증가한 경우

ㅇ IF조건절 및 함수를 과다하게 사용한 경우

ㅇ DISTINCT 구문 사용으로 테이블 전체를 검색하는 경우

ㅇ UNION ALL 구문 사용으로 테이블 전체를 검색하는 경우

※ 아주 기본적인 SQL만 제대로 이해하고 구현해도 SQL 성능의 많은 부분을 튜닝하는 것이 가능하다.

   SQL 튜닝의 최종적인 책임은 DBMS가 아닌 개발자에게 있음을 잊지 말아야 한다.

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 문법을 잘모르거나 자바스크립트를 잘 몰라서 헤매는 경우가 너무 많다.

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