* 프로그래머스, 타입스크립트로 함께하는 웹 풀 사이클 개발(React, Node.js) 5기 강의 수강 내용을 정리하는 포스팅.

* 원활한 내용 이해를 위해 수업에서 제시된 자료 이외에, 개인적으로 조사한 자료 등을 덧붙이고 있음.

1. count()

• 데이터베이스에서 특정 조건에 맞는 행(row)의 개수를 반환하는 집계 함수.

SELECT COUNT(column_name) FROM table_name WHERE condition;

• COUNT(column_name): 지정된 열에서 NULL이 아닌 값의 개수를 반환합니다.
• COUNT(*): 해당 테이블의 모든 행의 개수를 반환합니다. (NULL 값 포함)

-- 특정 카테고리에 있는 책의 개수 확인
SELECT COUNT(*) AS book_count 
FROM books 
WHERE category_id = 1;

-- 조회 결과
book_count
10

언제 사용해야하는가?

• 데이터의 개수를 세어야 할 때. 특정 조건에 맞는 데이터의 갯수를 알아야 할 때.

• 예를 들어, 사용자가 얼마나 등록되었는지, 특정 조건을 만족하는 데이터가 몇 개인지 확인하거나 할 때.

전체 데이터 개수 확인:

SELECT COUNT(*) FROM users;

• 테이블 전체의 레코드 개수 조회.

조건에 맞는 데이터 개수:

SELECT COUNT(*) 
FROM orders 
WHERE status = 'completed';

• 완료된 주문의 개수를 확인.

그룹별 데이터 개수 (GROUP BY와 함께 사용):

SELECT category_id, COUNT(*) AS book_count 
FROM books 
GROUP BY category_id;

• 각 카테고리에 속하는 책의 수 확인.

2. AS

• 별칭(Alias)을 설정하는 데 사용.

• 열 이름이나 테이블 이름을 가독성 있게 만들거나, 계산된 값을 임시 이름으로 표현할 때 유용.

SELECT column_name AS alias_name FROM table_name;

• AS는 선택 사항으로, 생략해도 동일한 결과를 얻을 수 있다. 하지만 명시적으로 작성하면 가독성이 좋아진다.

-- COUNT() 결과에 별칭을 붙임
SELECT COUNT(*) AS total_users FROM users;

-- 복잡한 계산에 별칭 사용
SELECT price * 1.1 AS final_price FROM books;

-- 조회 결과
total_users
100
final_price
110.00

언제 사용해야하는가?

결과를 읽기 쉽게 표현해야 할 때:

• 복잡한 계산, 함수 결과, 열 이름을 간단하고 의미 있는 이름으로 변경.

임시 열을 만들어 사용할 때:

• 쿼리 결과를 다른 쿼리에서 사용하거나, 가독성을 높이기 위해 열에 별칭 지정.

별칭으로 결과 가독성 향상:

SELECT COUNT(*) AS total_users 
FROM users;

• 결과에 의미 있는 이름(total_users)을 부여.

계산된 값에 별칭 부여:

SELECT price, price * 1.1 AS final_price 
FROM books;

• 책 가격(price)에서 10%를 더한 값을 final_price라는 이름으로 표시.

서브쿼리 결과를 활용:

SELECT avg_price_table.avg_price AS category_avg_price
FROM (
    SELECT category_id, AVG(price) AS avg_price
    FROM books
    GROUP BY category_id
) avg_price_table;

• 서브쿼리에서 별칭(avg_price_table)을 지정해 외부 쿼리에서 재사용.

3. 서브쿼리 (Subquery)

• 쿼리 안에 포함된 또 다른 쿼리.

• 내부 쿼리의 결과를 사용해 외부 쿼리를 실행하는 구조.

• 서브쿼리는 () 안에 작성, SELECT, FROM, WHERE, HAVING 등의 절에서 사용할 수 있다.

• 결과가 단일 값, 다중 행, 또는 테이블일 수 있다.

SELECT 절에서 사용

SELECT (SELECT COUNT(*) FROM books) AS total_books;

FROM 절에서 사용

SELECT * 
FROM (SELECT id, name FROM users WHERE active = 1) AS active_users;

WHERE 절에서 사용

SELECT * 
FROM books 
WHERE category_id = (SELECT id FROM categories WHERE name = 'Fiction');

-- 특정 카테고리에 포함된 책의 평균 가격이 전체 평균보다 높은 경우 조회
SELECT name, price
FROM books
WHERE price > (
    SELECT AVG(price) 
    FROM books
);

-- 조회 결과
name	price
Book A	150
Book B	200

언제 사용해야하는가?

내부 쿼리의 결과를 이용해야 할 때:

• 하나의 쿼리로 필요한 데이터를 얻기 힘들고, 중간 데이터를 만들어서 그 결과를 다시 사용하는 경우.

다른 테이블에서 특정 조건의 데이터를 가져올 때:

• 특정 값을 기준으로 조건을 걸어야 하는 경우.

특정 값 비교:

SELECT name, price 
FROM books 
WHERE price > (
    SELECT AVG(price) 
    FROM books
);

• 책 가격이 전체 평균보다 높은 책 조회.

최신 데이터 가져오기:

SELECT * 
FROM orders 
WHERE created_at = (
    SELECT MAX(created_at) 
    FROM orders
);

• 가장 최근에 생성된 주문만 조회.

다른 테이블에서 조건 설정:

SELECT * 
FROM books 
WHERE category_id = (
    SELECT id 
    FROM categories 
    WHERE name = 'Fiction'
);

• 'Fiction' 카테고리에 해당하는 책 조회.

테이블에 없는 데이터 필터링 (NOT IN과 함께 사용):

SELECT * 
FROM users 
WHERE id NOT IN (
    SELECT user_id 
    FROM orders
);

• 주문하지 않은 사용자를 조회.

4. EXISTS

• 특정 조건을 만족하는 데이터가 존재하는지 확인.

• EXISTS는 서브쿼리의 결과가 존재하면 True를, 존재하지 않으면 False를 반환.

• 결과의 내용보다 존재 여부에 초점.

• 서브쿼리는 () 안에 작성되며, 조건절(WHERE, HAVING 등)에서 사용.

SELECT column_name
FROM table_name
WHERE EXISTS (
    SELECT 1
    FROM another_table
    WHERE condition
);

• 서브쿼리의 결과가 반환되는 첫 번째 행에서 평가를 멈춤(효율적).
• 반환되는 값 자체가 아니라, 결과의 존재 여부를 확인.
• 조건의 부정은 NOT EXISTS를 사용.

관련 데이터가 존재하는 경우만 조회:

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

• 사용자가 주문한 적이 있는 경우만 조회.

관련 데이터가 없는 경우만 조회 (NOT EXISTS):

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

• 주문하지 않은 사용자를 조회.

다른 테이블에서 조건 확인:

SELECT c.id, c.name
FROM categories c
WHERE EXISTS (
    SELECT 1
    FROM books b
    WHERE b.category_id = c.id
);

• 책이 하나라도 포함된 카테고리만 조회.

테이블 비교 및 필터링:

SELECT * 
FROM users 
WHERE EXISTS (
    SELECT 1
    FROM subscriptions s
    WHERE s.user_id = users.id AND s.status = 'active'
);

• 활성화된 구독을 보유한 사용자만 조회.

언제 사용해야 하는가?

데이터의 존재 여부만 확인하고 싶을 때:

• 조건을 만족하는 행이 존재하는지 여부가 중요할 때.

다른 테이블과 연관된 데이터를 필터링할 때:

• 특정 테이블에 관련 데이터가 있는지 또는 없는지 확인.

성능 최적화가 필요한 경우:

• EXISTS는 조건을 만족하는 첫 번째 행만 확인하므로, 데이터 규모가 클 때 효율적.

특정 데이터 존재 여부 확인:

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

• 주문이 있는 사용자를 조회.

조건에 부합하지 않는 데이터 필터링 (NOT EXISTS):

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

• 주문하지 않은 사용자를 조회.

연관 테이블 확인:

SELECT c.name
FROM categories c
WHERE EXISTS (
    SELECT 1
    FROM books b
    WHERE b.category_id = c.id
);

• 책이 포함된 카테고리 조회.

5. 트랜잭션TRANSACTION

• 복수의 작업이 순차적으로 진행되었을 때, 만약 중간에 에러가 발생했을 경우.

• 이전에 진행되었던 작업들을 모두 취소하는 방법.

• 가령 쇼핑몰에서 결제 기능을 구현했다고 가정해보자.

• 결제가 이루어짐 -> 결제 정보가 DB에 저장됨의 순서로 진행된다고 했을 때..

• 만약 결제 정보를 DB에 저장하려는 시점에서 에러가 발생된다면?

• 결제는 되었는데, 그 정보가 DB에 저장되지 않기 때문에 상품이 발송되지 않는다!

• 이런 황당한 사태를 방지하기 위해 사용하는 것이 바로 트랜잭션TRANSACTION.

• 여러 테이블 간의 연관된 작업이 존재하거나, 여러 단계의 작업이 서로 의존성을 갖거나, 데이터의 일관성을 유지해야 하는 경우에 사용해야 한다.

트랜잭션TRANSACTION은 어떻게 사용하는가?

// 좋아요 추가 기능
exports.addLike = async (userId, bookId) => {
    // 데이터베이스 연결을 가져옵니다. 트랜잭션을 사용하기 위해 connection 객체를 활용합니다.
    const connection = await db.getConnection();
    try {
        // 트랜잭션 시작
        await connection.beginTransaction();

        // 1. book_id가 유효한지 확인합니다.
        //    - EXISTS 서브쿼리를 사용하여 books 테이블에 해당 book_id가 존재하는지 확인합니다.
        //    - EXISTS는 조건을 만족하는 첫 번째 레코드를 찾으면 즉시 종료하므로 성능이 좋습니다.
        const checkQuery = `
            SELECT EXISTS (
                SELECT 1 FROM books WHERE id = ?
            ) AS book_exists
        `;
        const [[{ book_exists }]] = await connection.execute(checkQuery, [bookId]);
        if (!book_exists) {
            throw new Error('존재하지 않는 도서 ID입니다.'); // 도서 ID가 유효하지 않으면 에러를 던집니다.
        }

        // 2. 좋아요 추가
        //    - likes 테이블에 user_id와 book_id를 삽입합니다.
        //    - FOREIGN KEY 제약 조건으로 인해 user_id와 book_id가 각각 users, books 테이블에 존재해야 합니다.
        const likeQuery = `INSERT INTO likes (user_id, book_id) VALUES (?, ?)`;
        await connection.execute(likeQuery, [userId, bookId]);

        // 3. books 테이블의 좋아요 수 업데이트
        //    - COUNT()를 사용하여 likes 테이블에서 해당 book_id의 좋아요 개수를 계산합니다.
        //    - 계산된 값을 books 테이블의 likes 필드에 업데이트합니다.
        const updateQuery = `
            UPDATE books
            SET likes = (
                SELECT COUNT(*) FROM likes WHERE book_id = ?
            )
            WHERE id = ?
        `;
        await connection.execute(updateQuery, [bookId, bookId]);

        // 트랜잭션 커밋: 모든 작업이 성공적으로 수행되었으므로 변경 사항을 확정합니다.
        await connection.commit();
    } catch (error) {
        // 트랜잭션 롤백: 오류 발생 시 모든 변경 사항을 취소합니다.
        await connection.rollback();
        throw error; // 에러를 호출한 곳으로 다시 전달합니다.
    } finally {
        // 데이터베이스 연결 해제: 사용한 connection 객체를 반환합니다.
        connection.release();
    }
};

• 학습 프로젝트로 구현한 좋아요 추가 기능을 기반으로 설명해보자면..

• 좋아요 추가 기능은 like 테이블과 book 테이블 양쪽에 데이터를 저장하고 수정하게 된다.

• 만약에 트랜잭션TRANSACTION을 사용하지 않았는데, 작업 도중에 에러가 발생한다면?

• 좋아요는 추가됐지만 카운트는 증가하지 않는 상황, like 테이블과 book 테이블의 데이터가 서로 일치하지 않는 상황 등이 발생되면서 전체 시스템 운영에 문제가 발생할 수도 있다.

• 따라서 작업 수행 전에, connection 객체를 불러온 뒤 이를 기반으로 beginTransaction을 호출하여 트랜잭션 작업을 열어준 뒤..

• 트랜잭션 내부에서 필요한 작업들을 모두 수행하게 한 뒤, commit()을 호출하여 작업 내용을 반영하게 하거나..

• 에러가 발생한다면 rollback()을 호출해서 작업 내용을 모두 무효화하면 된다.

• 어떤 결과가 도출되더라도, release()를 호출하여 사용한 connection 객체는 해제해주어야 한다.

격리성 (Isolation)

• 트랜잭션에는 이외에도 1가지 더 유용한 기능이 존재한다.

• 하나의 트랜잭션이 실행되는 동안 다른 작업이 트랜잭션으로 개입하지 못하게 하는 것.

• 가령 여러 사용자가 동시에 동일한 도서에 좋아요를 누르는 경우, 트랜잭션이 완료되기 전에는 likes 카운트가 잘못 계산되지 않도록 보호가 이루어진다.

• 쉽게 말하면 2명 이상의 사용자가 동시에 좋아요 추가를 눌러도, 트랜잭션 덕분에 에러가 발생하지 않고 기능이 잘 동작한다는 뜻.

• 그 이유는 잠금(Locking) 메커니즘이나 격리 수준(Isolation Level)이라는 개념이 포함되는데.. 이 포스팅에서는 여기까지는 다뤄보진 않겠다.