[MySQL] GROUP BY, HAVING / 비교연산자 / AND, OR, BETWEEN, IN / CASE

20 Nov 2021 - 2 minute read

GROUP BY

---

• 컬럼을 그룹화해서 데이터 조회하기

  • SELECT column_name FROM table_name 
    GROUP BY column_name;
    
    SELECT column_name1, column_name2, count(*) FROM table_name 
    GROUP BY column_name;
    
    SELECT CONCAT('문자열', column_name, ' ', COUNT(*), '문자열') 
    AS alias FROM table_name 
    GROUP BY column_name;
    

  • ex)

      SELECT CONCAT('In ', released_year, ' ', COUNT(*), ' book(s) released') 
      AS year FROM books GROUP BY released_year;
    

HAVING

---

• GROUP BY를 사용한 결과에 대하여 또 다시 조건을 설정할 수 있는데, 이 때 사용되는 것이 HAVING

• HAVING은 GROUP BY를 사용해야만 사용 가능하며, HAVING 조건절은 GROUP BY 결과에 대해서만 조건을 설정한다.

• WHERE, GROUP BY, HAVING, ORDER BY, LIMIT 순서로 조건 설정

• group by, having ex)

    create table cafemenu(
    num		int		primary key auto_increment,
    menu	char(15),
    price	int
    );
  
    insert into cafemenu
    (menu, price)
    values 
    ('아이스아메리카노', 4500),
    ('카푸치노', 4500),
    ('바닐라라떼', 5000),
    ('망고스무디', 5800),
    ('캐모마일', 4300);
  
    select * from cafemenu;
  

  

    select * from cafemenu group by price;
  

  

    select * from cafemenu
    where price between 4000 and 5000
    group by price having count(price)>=2;
  

  

비교연산자

---

• > / >= / < / <=

• SELECT column_name FROM table_name 
  WHERE column_name > 숫자 ORDER BY column_name;
  

• ex)

    SELECT -10 > -20 AND 'a' = 'A';
    # 1
  
    SELECT 40 <= 100 || -2 > 0;
    # 1
  

  • ‘true = 1’ / ‘false = 0’ 으로 반환

AND / OR / BETWEEN / IN

---

• AND

  • SELECT column_name FROM table_name 
    WHERE 조건절 AND 조건절;
    

• OR

  • SELECT column_name FROM table_name 
    WHERE 조건절 OR 조건절;
    

• BETWEEN / NOT BETWEEN

  • SELECT column_name FROM table_name 
    WHERE column_name BETWEEN A AND B
    

    • A와 B 사이의 모든 값 조회

    • = WHERE column_name A AND column_name B

  • SELECT column_name FROM table_name
    WHERE column_name NOT BETWEEN A AND B
    

• IN / NOT IN

  • SELECT column_name FROM table_name 
    WHERE column_name IN (A, B, C);
    

    • = SELECT column_name FROM table_name WHERE column_name = A or column_name = B or column_name = C;

  • SELECT column_name FROM table_name 
    WHERE column_name NOT IN (A, B, C);
    

    • = SELECT column_name FROM table_name WHERE column_name != A AND column_name != B AND column_name != C;

CASE

---

• WHEN-THEN 항상 함께 사용

• 모든 WHEN-THEN이 TRUE가 아닌 경우 ELSE값 반환 (≒switch-case)

• SELECT column_name,
      CASE 
          WHEN 조건절1 THEN 결과1
          WHEN 조건절2 THEN 결과2
          ...
          ELSE 결과
      END
  FROM table_name;
  

• ex)

    create table casetest(
    name		char(10),
    age			int
    );
  
    insert into casetest
    values
    ('James', 30),
    ('Alice', 25),
    ('Peter', 28),
    ('Julie', 34),
    ('Paul', 19);
  
    SELECT name as '이름',
        CASE
            WHEN age >=30
            THEN '30대'
            WHEN age >=20
            THEN '20대'
            ELSE '10대'
    END AS '나이'
    FROM casetest;