너는 개발자 지금 광고를 클릭 하고 있다.
위에 구글 애드센스를 클릭 하고 퍼가셈((((;゜Д゜)));

MYQUERY

# 해당 미션 사용자만 모음.
SELECT mm.members_uid, mm.missions_uid, m.user_id, m.uid, m.nick FROM members_missions mm
LEFT JOIN members m ON mm.`members_uid` = m.`uid`
WHERE mm.missions_uid = '5' AND mm.`phase_3` IS NOT NULL;


SELECT mr.`missions_uid`, r.`subjects`, r.`uid` FROM `missions_rewards` mr
LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid
WHERE mr.`missions_uid` = '5' ;



# 특정 문자열 바꾸기
UPDATE missions SET contents=REPLACE(contents, "eventpangpang.com", "atoara.com");




# 사용자가 미션을 수락 하였을 경우.
INSERT INTO `members_missions` VALUES (NULL, 1, 1, '2016-11-11 11:11:11',NULL,NULL,NULL);

# 관리자가 미션을 완수(승인) 하였을 경우.
UPDATE `members_missions` SET `phase_2` = '2016-11-11 11:11:11' WHERE `members_uid` = "1" AND `missions_uid` = "1";

# 유저가 미션을 완수하고 보상을 수령 하였을 경우.
UPDATE `members_missions` SET `phase_3` = '2016-11-11 11:11:11' WHERE `members_uid` = "1" AND `missions_uid` = "1";

# 최종 미션 완료
UPDATE `members_missions` SET `phase_4` = '2016-11-11 11:11:11' WHERE `members_uid` = "1" AND `missions_uid` = "1";

# 사용자 미션 목록
SELECT * FROM `members_missions` WHERE `uid` = '1';

# 멤버 uid에 따른 보상 넣어주기
INSERT INTO `members_rewards` VALUES (NULL, 1, 1);

# 미션 등록
INSERT INTO `missions` (`uid`, `subjects`, `images`, `description`, `contents`) VALUES (NULL, '테스트미션1', 'test_missions1.jpg', '테스트 미션 설명', '설명');
INSERT INTO `missions` (`uid`, `subjects`, `images`, `description`, `contents`) VALUES (NULL, '테스트미션2', 'test_missions2.jpg', '테스트 미션 설명', '설명');

# 미션 목록
SELECT * FROM `missions` ORDER BY uid DESC;
SELECT * FROM `missions` WHERE `uid` = '1';

# 보상 목록
SELECT * FROM `rewards` ORDER BY uid DESC;
SELECT * FROM `rewards` WHERE `uid`=1;

# 미션의 보상 삽입
INSERT INTO `missions_rewards` VALUES (NULL, 1, 1);
INSERT INTO `missions_rewards` VALUES (NULL, 1, 2);
INSERT INTO `missions_rewards` VALUES (NULL, 2, 1);
INSERT INTO `missions_rewards` VALUES (NULL, 2, 2);

# 보상 삽입
INSERT INTO `rewards` VALUES (NULL, '테스트보상2', 'test_reward2.jpg', '테스트 미션 설명', 99);


# 멤버 uid에 따른 미션 목록 조회
SELECT * FROM `members_missions` mm LEFT JOIN `missions` m ON mm.missions_uid = m.uid WHERE mm.`members_uid` = '406' ;

# 미션 목록에 따른 보상 가져오기
SELECT * FROM `missions_rewards` mr LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid WHERE mr.`missions_uid` = '1' ;
SELECT * FROM `missions_rewards` mr LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid WHERE mr.`missions_uid` = '2' ;

# 멤버 uid에 따른 보상 가져오기
SELECT * FROM `members_rewards` WHERE `members_uid` = '1';
SELECT * FROM `members_rewards` mr LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid WHERE mr.`members_uid` = '1' ;
SELECT * FROM `members_rewards` mr LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid WHERE mr.`members_uid` = '2' ;

# 모든 사용자 지우고 관리자만 살려놓기
DELETE FROM members WHERE uid != 1;
UPDATE members SET auth = 'users' WHERE uid != 1;

# 수량 뽑기
SELECT COUNT(uid) AS cnts FROM `members` WHERE `auth`='users';
SELECT COUNT(uid) AS cnts FROM `members_missions` WHERE phase_1 <> '' AND missions_uid = '2';
SELECT COUNT(uid) AS cnts FROM `missions_rewards` WHERE rewards_uid = '1';
SELECT COUNT(uid) AS cnts FROM `missions_rewards` WHERE rewards_uid = '2';

# 미션 목록에 따른 수행 멤버 가져오기
SELECT * FROM `members_missions` mm LEFT JOIN `members` m ON mm.members_uid = m.uid WHERE missions_uid = '7' ;

# 고객센터 넣기
INSERT INTO `customers`(`uid`,`category`,`names`,`titles`,`tels`,`emails`,`descriptions`,`created`) VALUES (1,'기타','원상필','테스트입니다.','020202020','naiyumie@gmail.com','없음.','2016-12-21 16:51:03'),(13,'제휴문의','테스트','냥엄마','010-0505-8544','1@1.com','인천에 살고 있는 36세 냥맘인데여.\r\n\r\n길냥이 키우는것도 인증 되나여-_-?\r\n\r\n','2016-12-26 18:05:25'),(14,'광고문의','테스트','개친구','010-0505-8548','1@1.com','반려견 세이브 프로젝트에 예산 10억을 지원 할까 합니다. ','2016-12-26 18:06:04'),(15,'광고문의','test','test3','test2','test1','test4','2016-12-27 11:01:46');

# 여러 조인
SELECT * FROM `members_missions` mm LEFT JOIN `members` m ON mm.members_uid = m.uid WHERE missions_uid = '7' ;
SELECT * FROM `members_missions` mm LEFT JOIN `missions` m ON mm.missions_uid = m.uid WHERE mm.`members_uid` = '406' ;
SELECT * FROM `missions_rewards` mr LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid WHERE mr.`missions_uid` = '5';


# 나의 미션 진행중 / 완료.
SELECT * FROM `members_missions` mm LEFT JOIN `missions` m ON mm.missions_uid = m.uid WHERE mm.`members_uid` = '405';
SELECT * FROM `members_missions` mm LEFT JOIN `missions` m ON mm.missions_uid = m.uid WHERE mm.`members_uid` = '405' AND mm.`phase_4` IS NULL;
SELECT * FROM `members_missions` mm LEFT JOIN `missions` m ON mm.missions_uid = m.uid WHERE mm.members_uid = '405' AND mm.phase_4 IS NOT NULL;

# 나의 보상 상품만
SELECT * FROM `members_rewards` mr LEFT JOIN `rewards` r ON mr.rewards_uid = r.uid WHERE r.type = '상품' AND mr.`members_uid` = '404' ;

# 핫 한 미션
SELECT * FROM `members_missions` mm LEFT JOIN `missions` m ON mm.missions_uid = m.uid WHERE mm.phase_4 != '' GROUP BY mm.missions_uid ORDER BY mm.uid DESC LIMIT 3;

# 신규미션
SELECT * FROM `missions` ORDER BY uid DESC LIMIT 3;

# cash
SELECT * FROM `rewards` r LEFT JOIN `members_rewards` mr ON mr.rewards_uid = r.uid WHERE mr.`members_uid` = '404' AND r.type = '캐시아웃' ;

# all user cash 초기화
UPDATE members SET cashout=0;

# TRUNCATE
DELETE FROM members WHERE user_id LIKE 'user%';
ALTER TABLE members AUTO_INCREMENT = 2;

TRUNCATE TABLE `members_rewards`;
ALTER TABLE members_rewards AUTO_INCREMENT = 1;

TRUNCATE TABLE `members_missions`;
ALTER TABLE members_missions AUTO_INCREMENT = 1;

# 최종 완료 되지 않은 미션
SELECT mm.uid, mm.members_uid, mm.missions_uid, mm.phase_1, mm.phase_2, mm.phase_3, mm.phase_4,
m.nick, m.user_id, mi.subjects, mi.images
FROM `members_missions` mm
LEFT JOIN `members` m ON mm.members_uid = m.uid
LEFT JOIN `missions` mi ON mm.missions_uid = mi.uid
WHERE mm.phase_4 IS NULL AND mm.phase_3 IS NOT NULL
ORDER BY mm.uid DESC
LIMIT 50;


# 최근 미션 진행 현황
SELECT mm.uid, mm.members_uid, mm.missions_uid, mm.phase_1, mm.phase_2, mm.phase_3, mm.phase_4,
m.nick, m.user_id, mi.subjects, mi.images
FROM `members_missions` mm
LEFT JOIN `members` m ON mm.members_uid = m.uid
LEFT JOIN `missions` mi ON mm.missions_uid = mi.uid
ORDER BY mm.uid DESC
LIMIT 50;


# 완수승인 및 보상이 수령 되지 않은 미션
SELECT mm.uid, mm.members_uid, mm.missions_uid, mm.phase_1, mm.phase_2, mm.phase_3, mm.phase_4,
m.nick, m.user_id, mi.subjects, mi.images
FROM `members_missions` mm
LEFT JOIN `members` m ON mm.members_uid = m.uid
LEFT JOIN `missions` mi ON mm.missions_uid = mi.uid
WHERE mm.phase_3 IS NULL AND mm.phase_4 IS NULL
ORDER BY mm.uid DESC
LIMIT 50;


TRUNCATE TABLE `rewards_p02`;
ALTER TABLE rewards_p02 AUTO_INCREMENT = 1;
SELECT * FROM `members_missions` mm LEFT JOIN `members` m ON mm.members_uid = m.uid WHERE missions_uid = '8' ;
SELECT * FROM rewards_p02 ORDER BY uid DESC LIMIT 1 ;
SELECT COUNT(uid) FROM `rewards_p02`;

UPDATE `missions` SET `limit_counts` = limit_counts-1 WHERE `uid` = 9;