2024.01.05(금)

🦭오늘 구현에 사용한 SQL keyword와 function

🏷️AS

• 별칭(ALIAS)
• 별칭이 사용된 query에서만 유효
• table의 column, 집계 함수(aggregate function) column, table 자체에 별칭 부여 가능

/* Using ALIAS with a column of a table */
SELECT column_name AS alias_name
FROM table_name;

/* Using ALIAS with an aggregate function */
SELECT SUM(column_name) AS alias_name
FROM table_name
WHERE condition(s);

/* Using ALIAS with a table */
SELECT alias_name_1.column1, alias_name_2.column1, ...
FROM table1 AS alias_name_1
INNER JOIN table2 AS alias_name_2
ON alias_name_1.matching_column = alias_name_2.matching_column;

🔍Subquery

• query 안의 query

SELECT col1, (SELECT ...) -- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...)         -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE col = (SELECT ...)  -- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)
/* [출처] https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EC%A0%95%EB%A6%AC#%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC%EC%9D%98_%EC%9C%84%EC%B9%98%EC%97%90_%EB%94%B0%EB%A5%B8_%EB%AA%85%EC%B9%AD */

🔢COUNT()

• COUNT(expr)
• null이 아닌 값의 개수 반환

/* Counts the total number of rows in a table */
SELECT COUNT(*) AS Count
FROM employees;

/* With WHERE condition */
SELECT COUNT(*) AS Count
FROM employees
WHERE gender = "F";

/* With GROUP BY */
SELECT gender, COUNT( gender ) AS Count
FROM employees
GROUP BY gender
--
+--------+--------+
| gender | Count  |
+--------+--------+
| M      | 179973 |
| F      | 120051 |
+--------+--------+

🫥EXISTS <Table subquery>

• EXISTS (SELECT ...)
• subquery가 하나 이상의 행을 반환하면 1(true) 반환 그렇지 않으면 0(false) 반환
• 행의 유무만 확인하기 때문에 값에 접근 불가능

/* In SELECT statement */
SELECT EXISTS (
  SELECT *
  FROM exists_vendors
  WHERE vendor_name LIKE 'A%'
) AS result;

/* In WHERE statement */
SELECT vendor_name
FROM exists_vendors AS v
WHERE EXISTS (
 SELECT *
 FROM exists_products AS p
 WHERE p.vendor_id = v.vendor_id
);
--
+-------------+
| vendor_name |
+-------------+
| Acme        |
| Beautiful   |
| Dazzling    |
+-------------+

✅API에 적용

• https://github.com/do0ori/book-store-project/blob/main/controllers/bookController.js#L24

  sql = `
    SELECT
        *,
        (SELECT COUNT(*) FROM likes WHERE book_id = books.id) AS like_count,
        EXISTS(SELECT * FROM likes WHERE user_id = ? AND book_id = books.id) AS liked
      FROM books
      ${whereClause}
      LIMIT ? OFFSET ?
  `;

• https://github.com/do0ori/book-store-project/blob/main/controllers/bookController.js#L60

  const sql = `
      SELECT
          books.*,
          category.name AS category_name,
          (SELECT COUNT(*) FROM likes WHERE book_id = books.id) AS like_count,
          EXISTS(SELECT * FROM likes WHERE user_id = ? AND book_id = books.id) AS liked
      FROM books
      LEFT JOIN category
      ON books.category_id = category.id
      WHERE books.id = ?
  `;

이렇게 쓰는 게 맞는지는 모르겠지만 오늘 구현한 내용에 대해 PR이랑 Issue도 작성해봤다.😋