단아산보

지역 테이블을 작업하다 보면 서울특별시 처럼 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;

쿼리문에서 되도록 group by 를 안쓰는 구조로 테이블구조를 잡는것이 좋지만 의외로 group by 를 해야 하는 경우가 꽤 많이 발생한다. 이때 그룹핑을 했을때 페이징 관련 처리 방법을 php 코드를 예시로 아래와 같이 정리해 본다. 

group by 예시 쿼리문

SELECT column1, COUNT(*)
FROM your_table
GROUP BY column1
ORDER BY COUNT(*) DESC
LIMIT 10;

group by 로 php 코드에서 페이징 처리 예시

<?php
// 데이터베이스 연결 설정
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// 페이지 번호와 페이지당 항목 수 설정
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1; // 현재 페이지
$limit = 10; // 페이지당 항목 수
$offset = ($page - 1) * $limit; // OFFSET 계산

// 총 게시물 수를 가져오기
$totalQuery = $pdo->query("SELECT COUNT(*) FROM board");
$totalRows = $totalQuery->fetchColumn();
$totalPages = ceil($totalRows / $limit); // 총 페이지 수

// 데이터 가져오기
$query = $pdo->prepare("SELECT * FROM board ORDER BY created_at DESC LIMIT :offset, :limit");
$query->bindParam(':offset', $offset, PDO::PARAM_INT);
$query->bindParam(':limit', $limit, PDO::PARAM_INT);
$query->execute();
$posts = $query->fetchAll(PDO::FETCH_ASSOC);

// 결과 출력
foreach ($posts as $post) {
    echo "<h2>" . htmlspecialchars($post['title']) . "</h2>";
    echo "<p>" . htmlspecialchars($post['content']) . "</p>";
}

// 페이지 네비게이션
echo '<div class="pagination">';
if ($page > 1) {
    echo '<a href="?page=' . ($page - 1) . '">이전</a>';
}

for ($i = 1; $i <= $totalPages; $i++) {
    if ($i == $page) {
        echo '<strong>' . $i . '</strong>'; // 현재 페이지 표시
    } else {
        echo '<a href="?page=' . $i . '">' . $i . '</a>';
    }
}

if ($page < $totalPages) {
    echo '<a href="?page=' . ($page + 1) . '">다음</a>';
}
echo '</div>';
?>

쿼리문을 작성하다보면 특이한 경우의 수가 많이 생긴다. 보통은 제품명을 하나로 입력하지만 고객사에서 자동으로 입력되길 원해서 필터값을 모두 조합해서 등록해야 하는 경우도 생긴다. 그런데 이때 필터값은 보통 코드 테이블에 공통으로 관리 하는데 그러면 테이블 join 을 해서 subquery 로 가져 와야 하는데 이런 단어들은 어떻게 합쳐야 할찌 난감할때가 있다. 

등록이 안된 필터는 null 이나 공백을 내 놓을테니 그 부분도 대응을 해야 한다. 아래 예시는 필터 정보를 | 기호로 하나로 합쳐서 select 해주는 쿼리문이다. CONCAT 는 문자열을 합치는 용도로 쓰는데 의외로 많이 쓰이기 때문에 알아 두는것이 유용하다. 

$sql = "SELECT 
      CONCAT(
        COALESCE((SELECT name FROM material_category WHERE id = G.goods_cate), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_1), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_2), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_3), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_4), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_5), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_6), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_7), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_8), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_9), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_10), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_11), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_12), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_13), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_14), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_15), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_16), '')
      ) AS full_name
    FROM goods G
    WHERE goods_idx = :goods_idx";

$stmt = $this->pdo->prepare($sql);
$stmt->bindValue(':goods_idx', $goods_idx, \PDO::PARAM_INT);
$stmt->execute();

// fetch()로 결과를 가져오고, full_name만 반환
$result = $stmt->fetch(\PDO::FETCH_ASSOC);
return $result['full_name'] ?? null; // full_name이 없을 경우 null 반환

이렇게 합쳐진 full_name 만 return 해주는 함수 예시로 pdo 로 DB 연동을 하고 있다면 참조 하면 좋을거 같다.  

테이블을 만들다 보면 종종 autoICrement 로 자동증가로 키값을 잡는 경우가 있다. 주로 키값을 관리하는 테이블로 관리를하지만 경우에 따라서는 자동증가 기능도 사용하곤 하는데 작업을하다 보면 insert 후에 그 키값을 받아 와서 다른테이블에  insert 해야 하는 경우가 있다. 이럴때 유용하게 사용할 수 있는 함수는 lastInsertId 다. 

트랙잭션 내에서는 커밋하기 전에 넣어 줘야 값을 담아 온다. 커밋 뒤에 넣어서 안되는줄 알았는데 순서를 바꿨더니 값이 잘 나온다. 자동 증가값 키가 필요 하다면 참고 하면 좋겠다.  

$this->pdo->beginTransaction();

$stmt->execute(); // 쿼리 실행

$lastId = $this->pdo->lastInsertId(); // 자동 증가값 가져오기

$this->pdo->commit(); // 트랜잭션 커밋

1. 테이블 생성

CREATE TABLE SubjectScores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score INT NOT NULL,
    semester VARCHAR(20),
    year INT
);

2. 샘플 데이터 입력구문

INSERT INTO SubjectScores (student, subject, score, semester, year) VALUES
('Kim Min-soo', 'English', 85, '1st Semester', 2024),
('Kim Min-soo', 'Math', 90, '1st Semester', 2024),
('Kim Min-soo', 'Science', 80, '1st Semester', 2024),
('Lee Young-hee', 'English', 78, '1st Semester', 2024),
('Lee Young-hee', 'Math', 88, '1st Semester', 2024),
('Lee Young-hee', 'Science', 92, '1st Semester', 2024),
('Park Cheol-soo', 'English', 95, '1st Semester', 2024),
('Park Cheol-soo', 'Math', 85, '1st Semester', 2024),
('Park Cheol-soo', 'Science', 88, '1st Semester', 2024),
('Kim Ji-young', 'English', 82, '1st Semester', 2024),
('Kim Ji-young', 'Math', 79, '1st Semester', 2024),
('Kim Ji-young', 'Science', 91, '1st Semester', 2024);

3. 학생별 과목별 평균과 랭킹 구하는 쿼리문

SELECT student, subject, AVG(score) AS average_score,
       RANK() OVER (PARTITION BY subject ORDER BY AVG(score) DESC) AS rank
FROM SubjectScores
GROUP BY student, subject;

나이 계산은 은근히 스크립트로 계산 하려면 코드를 많이 써야 한다. sql 쿼리문에서 나이 계산이 되어야 하는 경우도 종종 있어서 쿼리문에서 바로 뽑아서 쓸수 있는 코드 예시를 공유해본다. 

회원코드값으로 user 테이블에서 생년월일 필드로 만나이를 계산하는 공식이다. 

mysql 쿼리문에서 나이 계산하기

SELECT
    DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(birth_year, '-', birth_month, '-', birth_day), '%Y') - 
    (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(CONCAT(birth_year, '-', birth_month, '-', birth_day), '00-%m-%d')) AS age
FROM
    user
WHERE
    user_seq = #{user_seq};

리액트에서 나이 계산 하기 

import React, { useState } from 'react';

const AgeCalculator = () => {
  const [birthDate, setBirthDate] = useState('');
  const [age, setAge] = useState(null);

  const calculateAge = (dateString) => {
    const birthDate = new Date(dateString); // YYYY-MM-DD 형태의 문자열을 Date 객체로 변환
    const today = new Date();

    // 만 나이 계산
    let calculatedAge = today.getFullYear() - birthDate.getFullYear();
    if (today < new Date(today.getFullYear(), birthDate.getMonth(), birthDate.getDate())) {
      calculatedAge--;
    }
    
    return calculatedAge;
  };

  const handleSubmit = (e) => {
    e.preventDefault();
    setAge(calculateAge(birthDate));
  };

  return (
    <div>
      <form onSubmit={handleSubmit}>
        <input
          type="date" // HTML5의 date 입력 형식 사용
          value={birthDate}
          onChange={(e) => setBirthDate(e.target.value)}
        />
        <button type="submit">나이 계산하기</button>
      </form>
      {age !== null && <p>만 나이: {age}세</p>}
    </div>
  );
};

export default AgeCalculator;

쿠폰이나 구독권이나 사용 가능 기간을 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 를 해서 어제 일자 보다 작은 일자를 모두 찾는다. 

null체크 예제

SELECT IFNULL(SUM(CASE WHEN points < 0 THEN 0 ELSE points END), 0) AS total_points
FROM your_table;

sql 쿼리문에서 null 체크 와 case 문 동시에 적용하는 예제 

points 의 값이 null 일 경우에도 0 

마이너스 일 경우에도 0 으로 select 가 된다. 

date 형식 예제

SELECT DATE_FORMAT(datetime_column, '%Y-%m-%d') FROM table_name;

datetime 컬럼일 경우 날짜 포맷을 2024-04-01 로 출력 하는 예제 

date_format 를 사용하면 가능하다. 

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!