자주 쓰는 Mysql 쿼리문

- 특정 문자 체해서 업데이트

UPDATE write_clean SET wr_content = REPLACE(_content, '\\', '') where id = '1';
UPDATE write_clean SET wr_content = REPLACE(_content, '/userfiles', '/data/files/userfiles') where id = '1';

 

 

- select 한 다음에 update

$aaa = "  UPDATE admin_member
  SET member_level = CASE
      WHEN member_level = 0 THEN 1 /** member_level이 0 이었을 때 1로 업데이트 **/
      ELSE member_level
      END where member_id = 'seitahyi14';

// 레벨이 2인 일반회원을 조회한 후 레벨 업
$LvSql1 = "update g5_member set mb_level = 5 
          where mb_level = 2
          and mb_id IN
          (
          select mb_id from 
          (
          SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
          FROM g5_shop_order 
          WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
          group by mb_id having price >= ".$default['level_up_price']."
          ) t
          )";

// 레벨이 5이고, 한해 200만원의 결제 총액이 아닌 일반회원을 조회한 후 레벨 다운
$LvSql2 = "update g5_member set mb_level = 2 
          where mb_level = 5
          and mb_id IN
          (
          select mb_id from 
          (
          SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
          FROM g5_shop_order 
          WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
          group by mb_id having price < ".$default['level_up_price']."
          ) t
          )";

// 레벨이 2인 기업회원을 조회한 후 레벨 업
$LvSql1 = "update g5_member set mb_level = 6 
          where mb_level = 4
          and mb_id IN
          (
          select mb_id from 
          (
          SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
          FROM g5_shop_order 
          WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
          group by mb_id having price >= ".$default['level_up_price']."
          ) t
          )";

// 레벨이 5이고, 한해 200만원의 결제 총액이 아닌 일반회원을 조회한 후 레벨 다운
$LvSql2 = "update g5_member set mb_level = 4 
          where mb_level = 6
          and mb_id IN
          (
          select mb_id from 
          (
          SELECT mb_id, sum(od_receipt_price - od_send_cost - od_send_cost2) as price
          FROM g5_shop_order 
          WHERE od_time BETWEEN '".$start_date."' AND '".$end_date."'
          group by mb_id having price < ".$default['level_up_price']."
          ) t
          )";

 

다중 문자열 치환 -

select code,
       case
           when code = '0'
               then '사료'
           when code = '1'
               then '간식'
           when code = '2'
               then '영양제'
           when code = '3'
               then '용품'
           else 'ETC'
           end as 상품카테고리,
from table;
출처: https://thalals.tistory.com/339 [힘차게, 열심히 공대생:티스토리]

 

 

select 조회 후 insert -

insert into g5_write_Clean (wr_id, wr_num, wr_parent, mb_id, wr_name, wr_email, wr_ip, wr_subject, wr_content, wr_password, wr_datetime, wr_last, wr_hit, wr_good, wr_nogood) select BD_SEQ, CONCAT('-',BD_IDX), BD_IDX, MEM_ID, BD_W_NAME, BD_W_EMAIL, BD_W_IP, BD_TITLE, if(BD_ITEM2='', BD_CONT, CONCAT(BD_ITEM2, '----<br><br>', BD_CONT)) , BD_PWD, BD_REG_DATE, BD_EDIT_DATE, BD_VIEW_CNT, BD_GOOD_CNT, BD_BAD_CNT FROM gameculture_clean where CONF_SEQ = '17';

 

 

insert INTO g5_board_file (bo_table, wr_id, bf_source, bf_file, bf_download, bf_filesize, bf_datetime) SELECT CONCAT('Reference'), BD_SEQ, IMG_F_NAME, IMG_F_NICK, IMG_DOWN_CNT, IMG_F_SIZE, IMG_REG_DATE FROM gameculture_b_img_data where CONF_SEQ = '8';