'SQL'에 해당되는 글 94건

MySQL 에서 정규식을 접목하여 검색하면 OR 검색조건 작성이 심플해진다.

<?php
$sql="select * from test where eng regexp '가|나|다|라'";
// 정규식을 접목한 or 검색
$s1="가";
$s2="나";
$s3="다";
$s4="라";
$sql = "select * from test where eng regexp '".$s1."|".$s2."|".$s3."|".$s4."' ";
echo $sql;
?>

select * from test where eng regexp 'do?'; // ?는 마지막 글자가 0 또는 1개 일치 : d 또는 do가 일치
select * from test where name regexp '^..$'; // 시작부터 2글자로 끝나는 이름을 찾아라.

select * from test where eng regexp '^I'; // 첫글자가 I로 시작되는 문장을 찾아라

select * from test where eng regexp '^I\''; // 첫글자가 I'로 시작되는 문장을 찾아라

SELECT * FROM test WHERE col1 REGEXP '^[0-9]+$'; // 숫자로만 된 것을 찾아라

SELECT * FROM test WHERE col1 REGEXP '^[[:digit:]]+$'; // 숫자로만 된 것을 찾아라

SELECT * FROM test WHERE col1 NOT REGEXP '^[0-9]+$'; // 숫자로만 된 것을 제외하고 찾아라

SELECT * FROM test WHERE length(col1) > 0 and col1 NOT REGEXP '^[0-9]+$'; // 숫자 제외하고 찾아라

SELECT * FROM test WHERE col1 REGEXP '[0-9]+'; // 숫자와 문자를 포함하는 것을 찾아라

MySQL 테이블 구조 이해도를 높이기 위해서 칼럼 순서를 바꿀 필요가 있어서 변경했다.

treeview 테이블이다.

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 자료형 AFTER 다른컬럼;
alter table treeview modify column parent_id varchar(11) after id;

칼럼의 위치가 변경된 것을 확인할 수 있다.

alter table members modify column mfoneNO varchar(16) after phoneSE;
alter table members modify column userNM varchar(16) after userID;

PL/SQL(Procedure Language / Structured Query Language)은 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어. 주로 자료 내부에서 SQL 명령문만으로 처리하기에는 복잡한 자료의 저장이나 프로시저와 트리거 등을 작성하는 데 쓰인다.

주석 처리는 -- 또는 /*  */

CREATE OR REPLACE PROCEDURE 프로시져이름 (
    변수명 IN DATATYPE       -- Mode를 생략하면 default 로 IN
    변수명 OUT DATATYPE
    변수명 IN OUT DATATYPE
)
IS
    [선언문];  -- 변수의 선언

    -- v_cnt NUMBER := 0;   --프로시저 선언부에서 변수초기화하는 방법
BEGIN
    실행문;
    [EXCEPTION]
END;
/

/* 예제  */

CREATE OR REPLACE PROCEDURE update_sal
(
   /* IN  Parameter */
   v_empno    IN    NUMBER
)
IS
BEGIN

  UPDATE emp
  SET sal = sal  * 1.1
  WHERE empno = v_empno; -- PL/SQL 블록 내에서는 한 문장이 종료할 때마다 세미콜론(;) 을 사용.

  COMMIT;  -- 프로시저는 자동 커밋되지 않으므로 반드시 INSERT, UPDATE, DELETE 에서 COMMIT 해야한다.
END update_sal;
/     -- PL/SQL 블록은 행에 / 가 있으면 종결됨.

/* 프로시저 호출 */

EXECUTE update_sal(7369);

자료출처 : http://www.gurubee.net/lecture/1041  를 기준으로 다른 자료도 참조하면서 적었다.

교육은 받았는데 오라클 DB를 만질 일이 없다보니 교육도 집중해서 듣지 않아 세부적인 것은 잘 모른다.

그냥 간단한 것만 기록차원에서 적어둔다.

Function은 반드시 그 결과값을 리턴한다.

CREATE OR REPLACE FUNCTION 함수명 (
    변수명 [MODE] DATATYPE,
    변수명 [MODE] DATATYPE
)
RETURN 리턴할데이터타입 --값
IS
    [선언문];
BEGIN
    실행문;
    [EXCEPTION]
END;
/

CREATE OR REPLACE FUNCTION get_table_size
(p_table_name varchar2)

return number

is
  outval number;
BEGIN
  select ROUND(SUM(bytes)/1024/1024) INTO outval
  FROM user_segments 
  WHERE segment_name = p_table_name
  and segment_type ='TABLE'
  group by segment_name;
  RETURN outval;
END;
/

테이블 생성시 초보자들이 흔히 하는 실수는 primary key 부분 설계를 잘못하는 것 같다.

KIMSQ RB 의 테이블을 가져다 내 나름대로 간단하게 분석하고자 한다.

그누보드, KIMSQ RB 와 같은 빌더 들은 프로그램 고수가 만드는 거라고 보면 된다.
따라서 이런 빌더의 테이블 구조를 분석하는 것은 내 프로그램 지식 향상에 큰 도움이 된다.
여기서 테이블의 칼럼 하나 하나 분석하고자 하는 것은 아니다.

회원 ID를 저장할 때 테이블을 분리해서 저장하도록 설계되어 있다.

rb_s_mbrid 테이블의 uid 칼럼 = rb_s_mbrdata 테이블의 memberuid 와 관련이 있도록 설계되어 있다.

rb_s_mbrid 테이블의 uid 칼럼은 정수형 11자리, NOT NULL, AUTO_INCREMENT (자동증가) 로 되어 있다.

즉, 테이블에 데이터가 추가될 때마다 자동으로 증가되도록 설계되어 있다.

데이터를 삭제하고 새로운 데이터를 추가하면, 지워진 데이터 번호가 5번이라고 하자. 새로 추가된 데이터는 자동증가가 되므로 절대 5번이 되지 않는다. 데이터베이스의 기본 사상은 무결점이다.

PRIMARY KEY (`uid`), PRIMARY KEY (`memberuid`)

두개의 값은 항상 서로 같도록 설계하고 있다. rb_s_mbrid 테이블에서는 자동증가되도록 하고 다른 (rb_s_mbrdata) 테이블에서는 자동 증가는 없다.

PHP 코드 상에서 데이타를 저장할 때 두개의 테이블에 나누어서 저장되도록 되어 있다.

rb_s_mbrdata 테이블의 memberuid 칼럼은 rb_s_mbrid 테이블의 uid 칼럼의 FOREIGN KEY 다.

primary key 에 대해 검색하면 https://msdn.microsoft.com/ko-kr/library/ms179610.aspx 에 자세하게 나온다.

MS-SQL 에 대한 사항이지만, primary key 정의는 SQL 이 거의 동일하다고 보면 된다.

primary key

테이블에 대해 primary key 제약 조건을 지정하면 데이터베이스 엔진은 primary key 열에 대해 고유 인덱스를 자동으로 만들어 데이터 고유성을 적용한다.
또한 쿼리에서 primary key가 사용되는 경우 이 인덱스를 사용하여 데이터에 빠르게 액세스할 수 있다.
primary key 제약 조건이 두 개 이상의 열에 정의되는 경우 한 열에 중복된 값이 있을 수 있지만
primary key 제약 조건 정의에 있는 모든 열의 값 조합은 각각 고유해야 한다.

라고 나온다.

FOREIGN KEY

primary key 제약 조건과 달리 외래 키 제약 조건을 만들어도 해당 인덱스가 자동으로 생성되지 않는다.
외래 키 열은 쿼리에서 한 테이블의 외래 키 제약 조건 열을 다른 테이블의 기본 또는 고유 키 열과 연결하여 테이블의 데이터를 병합하는 조인에서 자주 사용된다.
데이터베이스 엔진 에서는 인덱스를 만들어 외래 키 테이블에 있는 관련 데이터를 빠르게 찾을 수 있다.

foreign key (칼럼명) references 부모테이블명(부모칼럼명) on delete cascade;

on delete rule 또는 on update rule 로 옵션을 지정할 수 있는데, 옵션을 주지 않으면 삭제와 변경이 제한된다.

primary key를 제대로 정의하는 것은 데이터베이스 디자인에 있어서 매우 중요한 출발점이다.
모든 테이블에는 primary key가 있어야 하며, 오직 하나의 primary key만 존재할 수 있다.
회원 테이블을 설계할 때 primary key 로 정의하는 칼럼은 uid 또는 idx 를 많이 사용한다.
id 또는 userID 칼럼은 중복없이 생성되도록 하기 위해서 unique index 를 설정한다.
primary key의 유무가 엄청난 성능 차이를 보인다.
일반적으로 primary key를 기준으로 데이터를 select 한다거나 primary key를 기준으로
다른 컬럼(들)의 값을 update 또는 delete하는 작업이 흔히 수행되기 때문에
테이블에 primary key를 정의해 주면 where 조건절에 primary key가 검색조건으로 사용된 쿼리들의 성능은 현저하게 향상된다.
primary key를 생성하지 않아서 primary key를 사용하는 쿼리들의 성능이 나쁜 것은 물론이며,
테이블에 잘못된 중복 데이터들이 저장됨으로 인하여 데이터 무결성까지 손상되어 있는 비극적인 상황까지 발전한 경우도 있다.

Primary key 컬럼은 반드시 NOT NULL로 정의해야 한다.

KEY `site` (`site`) : 인덱스 설정이 되어 있다는 의미다.

MySQL 의 인덱스는 여러개를 설정해도 실제 동작될 때에는 1개만 동작된다.

Oracle 은 여러개의 인덱스를 자동으로 선택하여 최적의 알고리즘으로 동작한다고 들었다.

따라서, 어떤 인덱스가 동작되도록 할 것인지는 설계자의 몫이다.

테이블 설계시 하나의 테이블에 모든 데이터가 저장되도록 하지 않고 다른 테이블에 분산해서 저장되도록 설계를 잘 하는 것이 중요하다.

테이블 검색시 문자열을 검색하는 것보다 숫자를 검색하면 성능이 훨씬 우수하다.

검색할 결과가 문자열이라고 해도 테이블 설계시 숫자로 검색되도록 설계하여 성능 향상이 되도록 하는 것도 필요하다.

CREATE TABLE IF NOT EXISTS `rb_s_mbrid` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `site` int(11) NOT NULL DEFAULT '0',
  `id` varchar(50) NOT NULL DEFAULT '',
  `pw` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`),
  KEY `site` (`site`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `rb_s_mbrdata` (
  `memberuid` int(11) NOT NULL,
  `site` int(11) NOT NULL DEFAULT '0',
  `auth` tinyint(4) NOT NULL DEFAULT '0',
  `sosok` int(11) NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '0',
  `comp` tinyint(4) NOT NULL DEFAULT '0',
  `admin` tinyint(4) NOT NULL DEFAULT '0',
  `adm_view` text NOT NULL,
  `email` varchar(50) NOT NULL DEFAULT '',
  `name` varchar(30) NOT NULL DEFAULT '',
  `nic` varchar(50) NOT NULL DEFAULT '',
  `grade` varchar(20) NOT NULL DEFAULT '',
  `photo` varchar(200) NOT NULL DEFAULT '',
  `home` varchar(100) NOT NULL DEFAULT '',
  `sex` tinyint(4) NOT NULL DEFAULT '0',
  `birth1` smallint(6) NOT NULL DEFAULT '0',
  `birth2` smallint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `birthtype` tinyint(4) NOT NULL DEFAULT '0',
  `tel1` varchar(14) NOT NULL DEFAULT '',
  `tel2` varchar(14) NOT NULL DEFAULT '',
  `zip` varchar(6) NOT NULL DEFAULT '',
  `addr0` varchar(6) NOT NULL DEFAULT '',
  `addr1` varchar(200) NOT NULL DEFAULT '',
  `addr2` varchar(100) NOT NULL DEFAULT '',
  `job` varchar(30) NOT NULL DEFAULT '',
  `marr1` smallint(6) NOT NULL DEFAULT '0',
  `marr2` smallint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `sms` tinyint(4) NOT NULL DEFAULT '0',
  `mailing` tinyint(4) NOT NULL DEFAULT '0',
  `smail` tinyint(4) NOT NULL DEFAULT '0',
  `point` int(11) NOT NULL DEFAULT '0',
  `usepoint` int(11) NOT NULL DEFAULT '0',
  `money` int(11) NOT NULL DEFAULT '0',
  `cash` int(11) NOT NULL DEFAULT '0',
  `num_login` int(11) NOT NULL DEFAULT '0',
  `pw_q` varchar(250) NOT NULL DEFAULT '',
  `pw_a` varchar(100) NOT NULL DEFAULT '',
  `now_log` tinyint(4) NOT NULL DEFAULT '0',
  `last_log` varchar(14) NOT NULL DEFAULT '',
  `last_pw` varchar(8) NOT NULL DEFAULT '',
  `is_paper` tinyint(4) NOT NULL DEFAULT '0',
  `d_regis` varchar(14) NOT NULL DEFAULT '',
  `tmpcode` varchar(50) NOT NULL DEFAULT '',
  `sns` text NOT NULL,
  `addfield` text NOT NULL,
  `cp` int(11) DEFAULT '0',
  `mcp` int(11) DEFAULT '0',
  `cpcode` int(11) DEFAULT NULL,
  `recommand` varchar(60) DEFAULT NULL,
  `broadcast` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`memberuid`),
  KEY `site` (`site`),
  KEY `auth` (`auth`),
  KEY `comp` (`comp`),
  KEY `sosok` (`sosok`),
  KEY `level` (`level`),
  KEY `admin` (`admin`),
  KEY `email` (`email`),
  KEY `name` (`name`),
  KEY `nic` (`nic`),
  KEY `sex` (`sex`),
  KEY `birth1` (`birth1`),
  KEY `birth2` (`birth2`),
  KEY `birthtype` (`birthtype`),
  KEY `addr0` (`addr0`),
  KEY `job` (`job`),
  KEY `marr1` (`marr1`),
  KEY `marr2` (`marr2`),
  KEY `sms` (`sms`),
  KEY `mailing` (`mailing`),
  KEY `smail` (`smail`),
  KEY `point` (`point`),
  KEY `usepoint` (`usepoint`),
  KEY `now_log` (`now_log`),
  KEY `d_regis` (`d_regis`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

테이블 칼럼을 조인(JOIN)을 고려하여 명칭이 가급적이면 중복되지 않게 처리하는 것이 편하고 중요하다는 걸 느끼고 있다.

// 테이블 칼럼 변경/추가

ALTER TABLE MEMBER ADD mfoneNO VARCHAR( 16 ) NULL DEFAULT NULL;
ALTER TABLE MEMBER ADD phoneBrand VARCHAR( 20 ) NULL DEFAULT NULL;
ALTER TABLE MEMBER ADD phoneModel VARCHAR( 20 ) NULL DEFAULT NULL;

ALTER TABLE MEMBER ADD `isUpdate` TINYINT( 2 ) NOT NULL DEFAULT '0' AFTER `isGanbu`;
ALTER TABLE MEMBER ADD `isDelete` TINYINT( 2 ) NOT NULL DEFAULT '0' AFTER `isUpdate`;

ALTER TABLE AccessLog CHANGE idx uid INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE AccessLog CHANGE userID LogID VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE AccessLog CHANGE userNM LogNM VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE AccessLog ADD date CHAR( 8 ) NOT NULL AFTER ipaddr,ADD YM CHAR( 6 ) NOT NULL AFTER date,ADD MD CHAR( 4 ) NOT NULL AFTER YM ;
update AccessLog a, AccessLog b SET a.date=concat(a.year,a.month,a.day),a.YM=concat(a.year,a.month),a.MD=concat(a.month,a.day) where a.uid=b.uid;

// 테이블 칼럼 삭제
ALTER TABLE AccessLog DROP date,DROP YM,DROP MD;

// 임시테이블에 저장하기

가장 최근에 접속한 날짜 기준으로 정렬하고 LogID 기준으로 1개씩만 추출하여 저장

INSERT INTO AccessLog_tmp
(ipaddr,date,YM,MD,year,month,day,time,OS,browser,LogID,LogNM,hit)
(SELECT ipaddr,max(date),YM,MD,year,month,day,time,OS,browser,LogID,LogNM,hit
    FROM AccessLog group by LogID order by date DESC
);

PHP 에서 코드를 작성시

$sql ="INSERT INTO AccessLog_tmp
(ipaddr,date,YM,MD,year,month,day,time,OS,browser,LogID,LogNM,hit)
(SELECT ipaddr,max(date),YM,MD,year,month,day,time,OS,browser,LogID,LogNM,hit
    FROM AccessLog group by LogID order by date DESC
)";
@mysql_query($sql);

필요해서 구현했거나 자주 사용할 수 있는 쿼리문을 작성하고 추가해둔다.

검색할 때 2016 또는 16 으로 검색해도 연도 검색이 가능하게 처리하는 로직

$arr['searchValue'] ='201607';
if(strpos(substr($arr['searchValue'],0,2), '20') !== false){
    $arr['searchValue'] = substr($arr['searchValue'],2,strlen($arr['searchValue']));
    echo '<br />'.$arr['searchValue'];
}

$strqry = 'mp'.$arr['searchValue'];
$sql =" where substring(userID,1,".strlen($strqry).")='".$strqry."'";

echo '<br />'.$sql;

데이터 : 2016-07-30 오전12:00:00
select date_format('20160730', '%Y-%m-%d') from 테이블;

엑셀 데이터를 DB에 집어넣기 위한 자료 만들어보는 방법이다.

먼저 테이블 구조를 살펴보자.

네이버지식인에서 문의한 테이블 구조를 가지고 작성한다.

CREATE TABLE IF NOT EXISTS `test` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(20) NOT NULL,
  `count` int(11) NOT NULL,
  `state` varchar(10) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

테이블에 데이터를 Insert 하는 쿼리문을 작성한다.

INSERT INTO test (id, count, state) VALUES ('test1', 6, 'a');

uid 칼럼은 자동 증가칼럼이므로 생략해도 된다.

만약 에러가 난다면

INSERT INTO test (uid, id, count, state) VALUES (NULL, 'test1', 6, 'a');

로 해주면 된다.

엑셀에서 쿼리문을 만들려면

=CONCATENATE("INSERT INTO test (id, count, state) VALUES ('",A3,"', ",B3,", '",C3,"');")

와 같은 Query 문을 만든다.

값이 들어있는 만큼 Drag 값을 채워나간다.

그런 다음에, EditPlus 같은 텍스트 에디터를 이용하여 결과값을 복사하여 EditPlus 에 복사해서 쿼리문을 확인한다.

phpMyAdmin 에서 SQL 에 쿼리문을 붙여넣기하고 실행한다.

또는 MySQL 에 직접 접속하여 붙여넣기를 한다.

테스트에 사용한 엑셀 파일

Excel_Query_make.xlsx

네이버지식인에 올라온 질문사항이 있어서 테스트 해보고 올려둔다.

1. 테이블 생성

CREATE TABLE IF NOT EXISTS `test` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(20) NOT NULL,
  `count` int(11) NOT NULL,
  `state` varchar(10) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `test` (`uid`, `id`, `count`, `state`) VALUES
(1, 'test1', 6, 'a'),
(2, 'test2', 6, 'a'),
(3, 'test1', -1, 'b'),
(4, 'test2', -2, 'b'),
(5, 'test2', -1, 'b'),
(6, 'test3', 5, 'a'),
(7, 'test3', 2, 'a'),
(8, 'test3', -3, 'b');

2. 쿼리문 작성

   쿼리문은 phpMyAdmin 에서 하면 편하고 좋다.

원하는 결과를 얻기 위해 하나씩 쿼리문을 입력하면서 확인한다.

SELECT id, sum(count), state FROM test group by id;
SELECT id, sum(count), state FROM test where state='a' group by id;
SELECT id, sum(count), state FROM test where state='b' group by id;

그런 다음에 실제 구하려는 쿼리문을 작성한다.

SELECT id, sum(case when state='a' then count else 0 end) as a_count, sum(case when state='b' then count else 0 end) as b_count FROM test group by id;

또는

SELECT id, sum(if(state='a', count, 0)) as a_count, sum(if(state='b', count, 0)) as b_count FROM test group by id;

자료를 검색해서 찾는 방법

구글에서 검색어 sql sum if condition 를 입력하고 자료를 찾으면 내가 원하는 걸 찾아볼 수 있다.

키포인트는 sum 인데 조건 sum 인거다. 그러므로 조건이 들어가는 sum 과 if 를 검색해보면 된다.

CASE WHEN 은 http://link2me.tistory.com/516 참조하면 된다. 하지만 이해가 부족할 수 있다. 구글링으로 게시글 읽어보고 원하는 걸 얻으면 된다.

네이버지식인에 올라온 셀프조인 문의사항이 있어서 작성해봤다.

먼저 테이블 구조 및 데이터는

CREATE TABLE IF NOT EXISTS `tb1` (
  `number` varchar(5) NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `tb1` (`number`, `name`, `age`) VALUES
('A101', '강신우', 40),
('A102', '김기덕', 28),
('A103', '김민호', 20),
('A104', '문소리', 23),
('A105', '박문수', 35);

윈도우버전 AutoSet9 에서 테스트를 해봤다.

이 화면에

SELECT a.name , a.age, COUNT(*)
FROM tb1 AS a JOIN tb1 AS b
WHERE a.age <=b.age
GROUP BY a.number;

쿼리문을 넣고 실행을 누른다.

그러면 결과를 다음과 같이 보여준다.

Join 의 개념을 모를때는 참 어렵게 생각했는데 알고보면 아무것도 아니다.

Join 은 그냥 물리적인 테이블 2개를 마치 1개처럼 생각하고 쿼리문을 만드는 거라고 생각하면 참 쉽다.

Self 조인은 하나의 물리적인 테이블을 가지고 2개의 가상테이블처럼 만들어서 원하는 결과를 조회하는 것이다.

원하는 결과를 도출하기 위해서 물리적인 테이블을 계속 생성하는 것 대신에 메모리상에서 논리적인 테이블을 만들어서 결과를 뽑아내는 거라고 보면 된다.

경우에 따라서는 물리적인 테이블을 생성해서 결과를 찾는 것이 빠를 수도 있다.

이 테이블에는 Index 가 설정되어 있지 않고 간단하게 Join 의 개념만 알고 싶은 것이라고 이해하면 된다.

SELECT a.number, a.name , a.age, b.number, b.name , b.age
FROM tb1 AS a JOIN tb1 AS b;

이 쿼리문은 가상 테이블 2개를 조인하는데 서로 공통의 분모에 해당하는 조건이 없는 경우다.

즉, 데이타가 5개 들어있다면 5 X 5 = 25 개의 결과를 보여준다.

여기서 데이터 정렬를 number 필드 기준으로 하고 싶다면 ORDER by a.number 를 추가하면 된다.

number 가 아닌 a.number 를 한 이유는 가상테이블 a 와 가상테이블 b 중에서 어떤 테이블을 기준으로 정렬할 것인가를 정해주는 것이기 때문이다.

이제 처음에 했던 쿼리문을 다시 해보자.

SELECT a.name , a.age, COUNT(*)
FROM tb1 AS a JOIN tb1 AS b
WHERE a.age <=b.age
GROUP BY a.number;
왜 a.age <= b.age 라고 했는지 위의 결과가 나온 그림을 보면 이해가 될 것이다.

그래도 이해가 안되면

SELECT a.number, a.name , a.age, COUNT(*)
FROM tb1 AS a JOIN tb1 AS b
GROUP BY a.number;
와 같이 Where 조건절을 빼고 조회를 해본다. 대신 a.number 칼럼을 추가해서 조회를 해준다.

이걸 보면 구하려고 하는 결과를 얻기 위해서 Where 조건을 왜 이렇게 해야 되는지 이해가 될 것이다.

phpMyAdmin 상에서 내가 해보고 싶은 Select 쿼리문을 만들어서 테스트를 해보고 원하는 결과가 나오는지 확인하면 정말 편하다.

SELECT a.name , a.age, COUNT(*)
FROM tb1 AS a JOIN tb1 AS b ON a.age <=b.age
GROUP BY a.number;

SELECT a.name , a.age, COUNT(*)
FROM tb1 AS a JOIN tb1 AS b
WHERE a.age <=b.age
GROUP BY a.number;

SELECT a.name , a.age, COUNT(*)
FROM tb1 a, tb1 b
WHERE a.age <=b.age
GROUP BY a.number;

동일한 결과를 보여주는 쿼리문이다. 첫번째는 ANSI Join 이고 마지막 것은 오라클 방식의 Join 이다.

모두 MySQL 에서 지원한다.

개인적으로는 오라클 방식의 조인이 편하다.

FROM 테이블 표시되는 부분에서 가독성이 더 좋다. 테이블을 여러개 조인할 때도 편하다.

order by uid ASC  // uid 오름차순 정렬
order by uid DESC  // uid 내림차순 정렬

order by content ASC  // content 오름차순 정렬
order by content DESC // content 내림차순 정렬

order by length(subject) ASC  // subject 길이 짧은 순 정렬
order by length(subject) DESC // subject 길이 긴 순 정렬

order by datetime desc, depth desc // 다중정렬 

// direct 칼럼이 값이 있으면 is_direct 를 내림차순으로 정렬하라

order by case when length(direct) > 0 then is_direct end DESC;

두개의 테이블을 조인해서 정렬순서를 정해야 할 경우가 있었는데 처음에 좀 헷갈려서 고생 좀 했다.

select c.idx,c.guloc,c.wloc,c.part,c.team,c.partNM from PART c, ORDER o where c.idx = o.idx ORDER BY gOrder, wOrder, pOrder, tOrder;

Update SQL 문에서 WHERE 조건절에 서브쿼리 조건을 넣어서 해야 할 경우가 있다.

목적 : 공백이 2칸 떨어진 칼럼을 찾아서 공백 1개로 변경

Update 를 하기전에 반드시 먼저 조회부터 하는 습관을 들여야 한다.

내가 하려는 것이 맞는지 확인차 한개만 업데이트를 해본다.

select uid, item, subject, content from data where uid=572;
update data SET subject = replace(subject,'  ',' ') where uid=572;

작업할 대상 전체를 조회하는 SQL 문을 만들어서 조회해본다.

select uid, item, subject, content from data where uid IN (select uid from data where subject LIKE '%  %');

// 에러 발생
update data SET subject = replace(subject,'  ',' ') where uid IN (select uid from data where subject LIKE '%  %');

같은 테이블에서 WHERE 조건절에 같은 테이블의 서브쿼리를 만들면 에러가 발생한다.

만약, 서브쿼리 SQL 문이 다른 테이블의 조건이었다면 에러가 발생하지 않을 것이다.

// 정상 실행
update data SET subject = replace(subject,'  ',' ') where uid IN (select uid FROM (select uid from data where subject LIKE '%  %') a);

같은 테이블에서 서브쿼리를 만들어야 한다면, 서브쿼리 문을 가상의 테이블로 만들어줘야 한다.

다른 테이블처럼 인식시키기 위해서다.

MySQL 에서는 가상 테이블에 별칭을 붙여줘야 제대로 동작된다.

WHERE 조건절에서 IN 을 사용한 이유는 서브쿼리 결과가 1개 아니라 여러개 나올 수 있기 때문이다.

Update 문에 대한 전반적인 내용을 살펴보려면 http://www.gurubee.net/lecture/2183 보면 도움될 것이다.

지식인에 오라클 SQL 인데 MYSQL 에서 에러가 발생한다는 질문이 있어서 테이블을 생성해봤다.

CREATE TABLE IF NOT EXISTS `department` (
`DEPTNO` int(11) unsigned NOT NULL,
`DEPTNAME` varchar(10) DEFAULT NULL,
`FLOOR` int(11) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO DEPARTMENT VALUES(1, '영업', 8);
INSERT INTO DEPARTMENT VALUES(2, '기획', 10);
INSERT INTO DEPARTMENT VALUES(3, '개발', 9);
INSERT INTO DEPARTMENT VALUES(4, '총무', 7);

-- EMPLOYEE 테이블 생성
CREATE TABLE IF NOT EXISTS `employee` (
  `EMPNO` int(11) unsigned NOT NULL,
  `EMPNAME` varchar(10) DEFAULT NULL,
  `TITLE` varchar(10) DEFAULT '사원',
  `MANAGER` int(11) DEFAULT NULL,
  `SALARY` int(11) NOT NULL,
  `DNO` int(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (EMPNO),
  CHECK(SALARY < 6000000),
  CHECK (DNO IN (1,2,3,4)),
  FOREIGN KEY(MANAGER) REFERENCES employee(EMPNO) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(DNO) REFERENCES department(DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO EMPLOYEE VALUES(4377, '이성래', '사장', NULL, 5000000, 2);
INSERT INTO EMPLOYEE VALUES(3426, '박영권', '과장', 4377, 3000000, 1);
INSERT INTO EMPLOYEE VALUES(3011, '이수민', '부장', 4377, 4000000, 3);
INSERT INTO EMPLOYEE VALUES(3427, '최종철', '사원', 3011, 1500000, 3);
INSERT INTO EMPLOYEE VALUES(1003, '조민희', '과장', 4377, 3000000, 2);
INSERT INTO EMPLOYEE VALUES(2106, '김창섭', '대리', 1003, 2500000, 2);
INSERT INTO EMPLOYEE VALUES(1365, '김상원', '사원', 3426, 1500000, 1);

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명.테이블명;