'SQL'에 해당되는 글 102건

SUBTIME(start_value, time) 은 start_value 에서 time만큼을 차감한 결과를 돌려준다.

SELECT SUBTIME('2015-08-01 01:30:10','02:50:20') AS subtime
// subtime -> 2015-07-31 22:39:50

SELECT SUBTIME('2015-08-01 01:30:10','2015-08-01 02:50:20') AS subtime
// subtime -> NULL

SELECT SUBTIME('2015-08-01 01:30:10.126587','00:50:20') AS subtime
// subtime -> 2015-08-01 00:39:50.126587

SELECT SUBTIME('2015-08-01 01:30:10.126587','00:50:20.020202') AS subtime
// subtime -> 2015-08-01 00:39:50.106385

SELECT SUBTIME('01:30:10.126587','02:50:20.020202') AS subtime
// subtime -> -01:20:09.893615

SELECT SUBTIME('01:30:10.126587','-02:50:20.020202') AS subtime
// subtime -> 04:20:30.146789

SELECT SUBTIME("10:24:21", "5");

결과는 10:24:16 ("5"는 5초를 의미)

CURDATE() 현재 연-월-일, CURTIME() 현재 시:분:초,

NOW()와 SYSDATE() 현재 연-월-일 시:분:초 출력

현재시간으로 부터 30분까지의 결과를 반환하고자 한다면....

where regdate > subtime(current_timestamp(), '0:30:0') and regdate < current_timestamp()

다른 방법으로

where regdate >= date_add(now(), interval -30 minutes)

where regdate >= date_add(now(), interval -1 day)  // 1일전

MySQL/MariaDB 백업, 복구에 대한 명령어를 적어둔다.

## 모든 DB 백업
mysqldump -uroot -p --all-databases > alldbbackup.sql

## 모든 DB 복구
mysql -u root -p < alldbbackup.sql

## 특정 DB 백업

mysqldump -uroot -p --databases kchart > kchart.sql

# kchart DB에서 특정 테이블 여러개 백업
mysqldump -uroot -p kchart allowHosts > allowHosts.sql
mysqldump -uroot -p kchart allowHosts > allowHosts.sql
mysqldump -uroot -p kchart allowHosts SYS_MEMBER > sys_allow_backup.sql

## DB 접속

mysql -u root -p

// DB 생성
create database kchart default character set utf8;

## root 비밀번호 설정
use mysql;
update user set password=password('설정할root비번') where user='root';
flush privileges;

## DB 사용권한 등록

## username, password, dbname을 각각 상황에 맞게 수정한다.

use mysql;
create user username@localhost identified by 'password';
grant all privileges on dbname.* to username@localhost;
flush privileges;

### 사용자 현황 검색

use mysql;
select host, user, password from user;

## DB 백업본 업로드
mysql -u root -p
비밀번호 입력
use kchart;
source partmember.sql;

테이블 칼럼을 phpMyAdmin 을 이용하여 추가하다보니 명령어를 잘 모르겠다.

그래서 명령어를 추가해 둔다.

칼럼 추가

ALTER TABLE 테이블명
ADD 추가할 컬럼명  데이터 유형;

ADD isDelete TINYINT( 2 ) NOT NULL ,
ADD OStype TINYINT( 2 ) NOT NULL ,
ADD phoneBrand VARCHAR( 20 ) NULL ,
ADD phoneModel VARCHAR( 20 ) NULL ,
ADD level TINYINT( 2 ) NOT NULL DEFAULT '1',
ADD chosung VARCHAR( 10 ) NULL ,
ADD ismobileNO TINYINT( 2 ) NOT NULL DEFAULT '1'

칼럼 변경

ALTER TABLE members

CHANGE isDelete isDelete TINYINT( 2 ) NOT NULL DEFAULT '0'

칼럼 삭제

ALTER TABLE 테이블명
DROP 삭제할 컬럼명;

ALTER TABLE members
  DROP isUpdate,
  DROP isDelete;

특정 칼럼 다음에 필드 추가

ALTER TABLE members ADD isUpdate TINYINT( 2 ) NOT NULL DEFAULT '0' AFTER isGanbu ,
ADD isDelete TINYINT( 2 ) NOT NULL DEFAULT '0' AFTER isUpdate

칼럼 순서 변경

ALTER TABLE members modify column corpID VARCHAR( 12 ) AFTER idx;

phpMyAdmin 에서 테이블명 변경을 했더니 아래와 같은 방식으로 처리가 된다.

CREATE TABLE `helpdesk`.`positionMN` (

MySQL 사용자 권한 부여 방법인데 찾기가 귀찮아서 여기에 적어둔다.

<?php

$DB['host'] = 'localhost';

$DB['name'] = 'kkkope2018';

$DB['user'] = 'kkkope';

$DB['pass'] = '!kkkope@#';

$DB['head'] = 'rb';

$DB['port'] = '3306';

$DB['type'] = 'MyISAM';

//use mysql;

//grant all privileges on kkkope2018.* to kkkope@localhost identified by '!kkkope@#';

//flush privileges;

?>

두개의 테이블에서 불일치하는 데이터를 찾는 SQL 명령으로 idx 는 index를 타는 칼럼이다.

select * from favoriteBook where staffidx not in (select idx from member);

select * from FavoriteBook where staffidx not in (select idx from SYS_MEMBER);

불일치 데이터를 찾아서 지우는 SQL 문

delete from FavoriteBook where staffidx not in (select idx from member);

delete from FavoriteBook where staffidx not in (select idx from SYS_MEMBER);

select * from FavoriteBook where myidx not in (select idx from SYS_MEMBER);
delete from FavoriteBook where myidx not in (select idx from SYS_MEMBER);

누락된 데이터 찾기

SELECT b.fieldB, a.fieldA FROM tableA a RIGHT OUTER JOIN tableB b ON a.fieldA = b.fieldB WHERE a.fieldA IS NULL;

불일치 데이터 찾기

두 테이블의 uid는 서로 같고 특정 필드 업데이트에 따른 불일치가 되는 데이터 찾기

즉 백업 이후에 변경된 데이터 찾기 같은 셈이다.

select a.uid,a.direct,b.direct,a.is_checking,b.is_checking from data_0218 a, data_0220 b where a.uid=b.uid and a.is_direct=5 and a.is_checking=0 and b.is_checking NOT IN(0);

Toad for MySQL 파일을 다운로드하여 설치를 해봤다.

다운로드 :  http://software.dell.com/products/toad-for-mysql/

윈도우에 설치한 AutoSet9 에서 구동하는 MySQL DB 접속을 해보니 접속이 잘된다.

테이블 구조 설계를 위한 Database Diagram 을 할 수 있어서 엄청 편하다.

코딩을 하다보면 테이블 구조 파악이 쉽지 않아서 애를 먹는 경우가 있는데 이걸 이용해서 구조 설계를 해두면 엄청 편리할 거 같다.

SQL 에 대한 개념을 이해하려고 책도 참 여러권 샀다. 다 정독한건 아니고 필요한 곳만 찾아 본것이 많다.

하지만 난 아직 초보다. 프로그래머가 아니라 취미로 배우니 수준이 올라가지 않는다.

SQL 교육을 수강하고 책을 보면서 나름 터득한 걸 적어본다.

나중에 강의하게 되면 설명 쉽게 해줄 생각으로 좀 정리한 자료다.

SQL Aliases
- 칼럼명을 별칭(Aliases)으로 할 수도 있고, 테이블명을 별칭으로 할 수도 있다.

https://www.w3schools.com/sql/sql_join.asp 사이트를 참조하면 개념을 쉽게 이해할 수 있다.

영문이라 처음에는 이해가 잘 안될 수도 있다. 하지만 쿼리문을 계속 사용하다보면 이해가 될 것이다.

무엇이든 배우는데 가장 중요한 것은 개념에 대한 이해다. 개념을 이해하면 응용은 자연스럽게 된다.

select o.OrderID, c.CustomerName 등으로 select 절에 나열(Query 속도상 필요한 칼럼만 뽑는게 좋음)

Orders 테이블의 것만 조회하고 싶다면, select o.* from .... 이라고 하면 된다.

아래 그림의 교집합 안에서 조건에 맞는 걸 또 추출하고 싶다면, Where 조건절을 추가한다.

SELECT o.* FROM Orders o INNER JOIN Customers c ON o.CustomerID=c.CustomerID where o.CustomerID='userid'; // userid 는 PHP 에서 변수로 받은 거라고 보면 됨.

SELF JOIN 은 두개의 물리적인 테이블을 만들어서 원하는 결과를 뽑아내야 하지만, 그럴 필요없이 메모리 공간에 하나의 논리적인 테이블을 만들어서, 물리적인 테이블과 조인하여 결과를 추출하는 것이라고 이해하면 된다.

Update FavoriteBook f INNER JOIN COMMON c ON f.grpidx=c.grpidx SET f.grpNM=c.grpNM where f.grpidx=2;

- FavoriteBook f INNER JOIN COMMON c ON f.grpidx=c.grpidx : 한개의 테이블이라고 생각한다.

- Update 테이블명 SET  WHERE 조건

- 오라클 쿼리방식으로 한다면

  Update FavoriteBook f, COMMON c SET f.grpNM=c.grpNM where f.grpidx=c.grpidx and f.grpidx=2;

순수하게 A만 뽑아주는 쿼리

SELECT a.id, a.name, a.age, b.mobile, b.internet 
FROM a LEFT JOIN b on a.id = b.id 
WHERE b.id is null
order by a.id ASC

MySQL 은 FULL OUTER JOIN 은 없다.

LEFT JOIN 과 RIGHT JOIN 을 UNION 으로 묶어서 처리하면 된다.

간단하게 정리한 것이지만 도움되면 댓글이나 공감 눌러주세요.

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);