단아산보

지역 테이블을 작업하다 보면 서울특별시 처럼 full name 으로 쓰기도 하고  서울 처럼 short name 으로 줄여서 쓰는 경우도 있어서 해당 부분에 맞게 데이터를 업데이트 해야할 일이 종종 생긴다. 

아래 쿼리문은 그런 경우 테이블 전체에서 수정해야 하는 쿼리문을 한번에 update 할 수 있다. 업데이트 구문에서도 case 구분을 잘쓰면 유용하다. 

UPDATE code_bjd_short
SET kf_name = CASE
    WHEN kf_name LIKE '서울특별시%' THEN REPLACE(kf_name, '서울특별시', '서울')
    WHEN kf_name LIKE '부산광역시%' THEN REPLACE(kf_name, '부산광역시', '부산')
    WHEN kf_name LIKE '대구광역시%' THEN REPLACE(kf_name, '대구광역시', '대구')
    WHEN kf_name LIKE '인천광역시%' THEN REPLACE(kf_name, '인천광역시', '인천')
    WHEN kf_name LIKE '광주광역시%' THEN REPLACE(kf_name, '광주광역시', '광주')
    WHEN kf_name LIKE '대전광역시%' THEN REPLACE(kf_name, '대전광역시', '대전')
    WHEN kf_name LIKE '울산광역시%' THEN REPLACE(kf_name, '울산광역시', '울산')
    WHEN kf_name LIKE '세종특별자치시%' THEN REPLACE(kf_name, '세종특별자치시', '세종')
    WHEN kf_name LIKE '경기도%' THEN REPLACE(kf_name, '경기도', '경기')
    WHEN kf_name LIKE '강원도%' THEN REPLACE(kf_name, '강원도', '강원')
    WHEN kf_name LIKE '충청북도%' THEN REPLACE(kf_name, '충청북도', '충북')
    WHEN kf_name LIKE '충청남도%' THEN REPLACE(kf_name, '충청남도', '충남')
    WHEN kf_name LIKE '전라북도%' THEN REPLACE(kf_name, '전라북도', '전북')
    WHEN kf_name LIKE '전라남도%' THEN REPLACE(kf_name, '전라남도', '전남')
    WHEN kf_name LIKE '경상북도%' THEN REPLACE(kf_name, '경상북도', '경북')
    WHEN kf_name LIKE '경상남도%' THEN REPLACE(kf_name, '경상남도', '경남')
    WHEN kf_name LIKE '제주특별자치도%' THEN REPLACE(kf_name, '제주특별자치도', '제주')
    ELSE kf_name
END;

쿠폰이나 구독권이나 사용 가능 기간을 start_date 와 expir_date 로 테이블에 필드를 잡았다면 현재 일자를 입력 받아서 유효한지 아닌지를 체크 하려면 count 를 사용해도 되지만 true, false 로 반환하는 방법도 있다. 아래 쿼리문은 true, false 반환하는 예시다.  

초보때 쿼리문을 뽑을때 데이터가 없으면 해당 날짜에 포함되는 데이터가 없으면 조회 되는 row 자체가 없기에 date_condition 으로 접근 자체가 안되는걸 모르고 한참 삽질을 한적이 있는데 예시와 같이 case 문으로  뽑을 수도 있다. 보통은 count 로 해서 0보다 크면 조건으로 많이 작업한다. 

SELECT 
	CASE WHEN EXISTS ( 
		SELECT 1 FROM cupon 
		WHERE user_seq = ${user_seq} AND start_date <= #{today} AND expir_date >= #{today} 
    ) 
	THEN 'true' ELSE 'false' END AS date_condition

count 가 조금 더 코드가 간소하고 명확하기도 하다. 

SELECT COUNT(date_condition) as date_condition_cnt
FROM cupon
WHERE user_seq=${user_seq} AND start_date <=#{today} AND expir_date >= #{today}

오늘은 이미 되어 있는 쿼리문이어서 다른 곳에서도 사용하는 곳이 있어서 부득이하게 수정하긴 했는데 지금 같은 케이스틑 count 가져오는게 편리한거 같다. 

현재 일자 보다 시간단위로 시간 분 까지 체크해서 작은 데이터를 모두 셀렉트 하는 쿼리문 

SELECT *,
  LEFT(SO.sv_time, 2) AS sv_time_hours,
  RIGHT(SO.sv_time, 2) AS sv_time_minutes
FROM shop_order SO
WHERE 1=1
  AND SO.ci_gubun = 'CS001'
  AND SO.ord_status > 'OR10'
  AND SO.ci_maching_state='MS10'
  AND CONCAT(SO.sv_start_date, ' ', LEFT(SO.sv_time, 2), ':', RIGHT(SO.sv_time, 2)) < NOW();

1000 으로 sv_time 에 데이터가 있다면 left 와 right 함수로 잘라서 가운데는 : 로 붙여서 날짜 타입으로 concat 로 문자열을 합쳐서 now 함수와 비교하면 조회 된다. 

만약 어제 일자 까지로 조회 하고 싶다면 CURDATE() - INTERVAL 1 DAY

SELECT * FROM shop_order SO
WHERE 1=1 
AND SO.ci_gubun = 'CS001' 
AND SO.ord_status > 'OR10'
AND SO.ci_maching_state='MS10' 
AND sv_start_date < CURDATE() - INTERVAL 1 DAY;

로 현재 일자에서 마이너스 1 day 를 해서 어제 일자 보다 작은 일자를 모두 찾는다. 

mysql 에서 문자열을 자르는 함수는 substr 이다. 

col 필드가 '20B10' 이라면 

아래 쿼리문의 결과값은 'B' 

특정 문자열의 결과값만 필요할 때 유용하다. 

SELECT SUBSTR(col, 3, 1) FROM table_name;

LENGTH(): 문자열의 길이를 반환

예제>

SELECT LENGTH('Hello, world!'); -- 결과: 13

UPPER(): 문자열을 대문자로 변환

예제>

SELECT UPPER('Hello, world!'); -- 결과: HELLO, WORLD!

CONCAT(): 두 개 이상의 문자열을 결합

예제>

SELECT CONCAT('Hello', ', ', 'world', '!'); -- 결과: Hello, world!

REPLACE(): 문자열에서 특정 문자열을 다른 문자열로 대체

예제>

SELECT REPLACE('Hello, world!', 'world', 'MySQL'); -- 결과: Hello, MySQL!

SELECT CONCAT(etc1, ' ', etc2) as delv_addr1
,CONCAT(etc1, ':', etc2) as delv_addr1
 FROM gi_code;

mysql 쿼리문 예제

select 조회시 문자열 합치기 

문자열 사이에 특수 문자 추가 해서 합치기