2024-11-29

테이블에 새로운 필드를 생성하고 그 값을 외부로부터 삽입하고 싶을 때

ALTER TABLE item_simulator.BasicItems
ADD COLUMN itemCode INT;

item_simulator라는 데이터베이스에 BasicItems라는 테이블이 있다. BasicItems 테이블에 itemCode라는 이름의 필드를 추가한다. INT형의 자료만 입력될 수 있다.

UPDATE item_simulator.BasicItems bi
JOIN item_simulator.Items i
ON bi.itemId = i.itemId
SET bi.itemCode = i.itemCode;

BasicItems테이블과 Items테이블을 itemId 컬럼을 기준으로 join한다. 그리고 BasicItems 쪽을 수정하는데, BasicItems테이블의 itemCode에 Items테이블의 itemCode를 할당해주면 된다.
(JOIN은 단순히 데이터를 조회하기 위한 수단이며, 테이블이나 데이터 자체는 변경되지 않는다.)

특정 조건에 해당하는 레코드의 어느 필드를 다른 값으로 수정하고 싶을 때

UPDATE item_simulator.Characters
SET characterMoney = 10000000
WHERE accountId = 5

accountId가 5인 캐릭터들을 대상으로, 그들의 게임 머니를 10000000으로 설정하는 쿼리는 위와 같이 작성하면 좋다.

다중 테이블 조인 및 집계

SELECT c.characterName, COUNT(i.itemId) AS itemCount
FROM item_simulator.Characters c
LEFT JOIN item_simulator.CharacterItems ci ON c.characterId = ci.characterId
LEFT JOIN item_simulator.BasicItems i ON ci.itemId = i.itemId
GROUP BY c.characterId
HAVING itemCount > 5
ORDER BY itemCount DESC;

캐릭터별로 보유한 아이템 수를 세고, 5개 이상의 아이템을 가진 캐릭터를 내림차순으로 정렬하여 조회한다.

조건부 집계

CASE 문을 사용하여 특정 조건에 따라 집계하는 방법이다.

SELECT 
    c.characterClass,
    SUM(CASE WHEN ci.itemId IS NOT NULL THEN 1 ELSE 0 END) AS itemCount,
    SUM(c.characterMoney) AS totalMoney
FROM item_simulator.Characters c
LEFT JOIN item_simulator.CharacterItems ci ON c.characterId = ci.characterId
GROUP BY c.characterClass;

각 클래스별로 보유한 아이템 수와 총 게임 머니를 집계한다.

윈도우 함수 사용하기

SELECT 
    characterId, 
    characterMoney, 
    RANK() OVER (ORDER BY characterMoney DESC) AS moneyRank
FROM item_simulator.Characters;

각 캐릭터의 게임 머니에 따라 순위를 매겨 조회한다.

복잡한 서브쿼리

서브쿼리를 통해 특정 조건을 만족하는 데이터를 조회한다.

SELECT *
FROM item_simulator.BasicItems
WHERE itemId IN (
    SELECT ci.itemId
    FROM item_simulator.CharacterItems ci
    WHERE ci.characterId IN (
        SELECT c.characterId
        FROM item_simulator.Characters c
        WHERE c.characterLevel > 10
    )
);

레벨이 10 이상의 캐릭터가 보유한 아이템을 조회한다.

트랜잭션을 사용한 원자적 작업

여러 작업을 원자적으로 수행할 때 사용하는 예.

START TRANSACTION;

UPDATE item_simulator.Characters
SET characterMoney = characterMoney - 1000
WHERE characterId = 1;

INSERT INTO item_simulator.CharacterItems (characterId, itemId)
VALUES (1, 2);

COMMIT;

캐릭터의 머니를 차감하고 아이템을 추가하는 작업을 트랜잭션으로 묶어 원자적으로 처리한다.

데이터 변경 후 결과 조회

데이터를 변경한 후 그 결과를 조회하는 방법.

UPDATE item_simulator.BasicItems
SET itemPrice = itemPrice * 1.1
WHERE itemCategory = 'Weapon';

SELECT * FROM item_simulator.BasicItems
WHERE itemCategory = 'Weapon';

무기 카테고리의 아이템 가격을 10% 인상한 후, 해당 아이템들을 조회한다.

CTE (Common Table Expressions) 사용하기

CTE를 사용하여 가독성을 높이고 복잡한 쿼리를 간소화한다.

WITH RankedItems AS (
    SELECT 
        itemId, 
        itemName, 
        RANK() OVER (ORDER BY itemPrice DESC) AS priceRank
    FROM item_simulator.BasicItems
)
SELECT *
FROM RankedItems
WHERE priceRank <= 10;

가장 비싼 10개의 아이템을 조회하는 쿼리다.