13. SQL 작성 (select / order_by / where / distinct)

SELECT

# SELECT FROM 연습
use menudb; -- 1. 테이블을 사용할 수 없기 때문에 접근허용 해주기

# 단일 컬럼 조회
SELECT menu_name 
  FROM tbl_menu;

# 다중 컬럼 조회
SELECT menu_code, menu_name, menu_price, category_code, orderable_status  -- 2. 모든 컬럼은 *로도 표현할 수 있다 (하지만 현업에서는 지양하는 바이다. 기능을 모르니까)
  FROM tbl_menu;

# 연산자나 now 함수도 사용가능하다 / (Oracle에서는 금지. 연산자 사용을 위해서는 FROM DUAL을 사용해야 한다)
SELECT 12 + 17;
SELECT 12 - 17;
SELECT 12 * 17;
SELECT 12 / 17;
SELECT 12 % 17;

SELECT now()

 

 

ORDER BY

SELECT menu_code, menu_name, menu_price
  FROM tbl_menu
  ORDER BY menu_name; 
  
  SELECT menu_code, menu_name, menu_price
  FROM tbl_menu
  ORDER BY menu_name DESC;
  
# 같은 값끼리의 정렬
SELECT menu_code, menu_name, menu_price
  FROM tbl_menu
  ORDER BY menu_price, menu_name;
  
# 컬럼의 연산 결과로 정렬이 가능함
SELECT menu_code, menu_name, menu_price, menu_code * menu_price
  FROM tbl_menu
  ORDER BY menu_code * menu_price;
 
#별칭을 사용한 정렬이 가능
SELECT menu_code, menu_name, menu_price, menu_code * menu_price as '연산결과'
  FROM tbl_menu
  ORDER BY '연산결과';
  
# 내림차순 정렬 시 NULL이 맨 끝으로 온다 (default)
# IS NULL을 붙이면 내림차순 정렬 시 NULL을 맨처음으로 : DESC 생략 불가 
SELECT category_code, category_name, ref_category_code
FROM tbl_category
ORDER BY ref_category_code IS NULL DESC, ref_category_code DESC; -- null값을 뒤로 보내기

 

WHERE

# WHERE

-- 1. 비교 연산자 
SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE orderable_status = 'Y';

SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE orderable_status != 'Y'; -- 또는 <> 연산자를 사용하면 된다

# 대소 비교
SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE menu_price <= 10000;

# 이건 안돼요
-- SELECT menu_name, menu_price, orderable_status
-- FROM tbl_menu
-- WHERE 10000 < menu_price <= 20000;

-- 2. AND
SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE 10000 < menu_price AND menu_price <=20000;

-- 3. OR
SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE menu_price > 30000
OR menu_name = '열무김치라떼';

-- 4. BETWEEN
SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE menu_price BETWEEN 10000 AND 20000;

SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE menu_price NOT BETWEEN 10000 AND 20000;

-- 5. LIKE
SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE menu_name LIKE '%김치%';

SELECT menu_name, menu_price, orderable_status
FROM tbl_menu
WHERE menu_name NOT LIKE '%김치%';

-- 6. IN 
SELECT menu_name, menu_price, category_code
FROM tbl_menu
WHERE category_code = 4
OR category_code = 5
OR category_code = 6;

SELECT menu_name, menu_price, category_code
FROM tbl_menu
WHERE category_code IN (4,5,6);

SELECT menu_name, menu_price, category_code
FROM tbl_menu
WHERE category_code NOT IN (4,5,6);

-- 7. IS NULL
SELECT category_code, category_name, ref_category_code
FROM tbl_category
WHERE ref_category_code IS NULL;

SELECT category_code, category_name, ref_category_code
FROM tbl_category
WHERE ref_category_code IS NOT NULL;

 

DISTINCT

# DISTICNT

# 고윳값 찾기
SELECT DISTINCT category_code
FROM tbl_menu
ORDER BY category_code;

SELECT DISTINCT ref_category_code
FROM tbl_category;

SELECT DISTINCT category_code, orderable_status
FROM tbl_menu
ORDER BY category_code, orderable_status;

 

LIMIT

# LIMIT

SELECT menu_code, menu_name, menu_price
  FROM tbl_menu
ORDER BY menu_price DESC;

-- offset 2, row count = 5
SELECT menu_code, menu_name, menu_price
  FROM tbl_menu
ORDER BY menu_price DESC
LIMIT 2, 5; # 2부터 가져올 행의 수 5개

 

GROUP BY / 집계함수 / HAVING

# Group BY

# 단일 컬럼 GROUP BY + COUNT 함수
SELECT category_code, COUNT(*)
  FROM tbl_menu
GROUP BY category_code;

# 단일 컬럼 GROUP BY + SUM,AVG 함수
SELECT category_code, COUNT(category_code) AS '개수', SUM(menu_price) AS '합', AVG(menu_price) AS '평균가격'
FROM tbl_menu
GROUP BY category_code;

# 다중 컬럼 GROUP BY + COUNT 함수 
SELECT category_code, menu_price, COUNT(*)
FROM tbl_menu
GROUP BY category_code, menu_price
ORDER BY category_code, menu_price;

# Group BY

# 단일 컬럼 GROUP BY + COUNT 함수
SELECT category_code, COUNT(*)
  FROM tbl_menu
GROUP BY category_code;

# 단일 컬럼 GROUP BY + SUM,AVG 함수
SELECT category_code, COUNT(category_code) AS '개수', SUM(menu_price) AS '합', AVG(menu_price) AS '평균가격'
FROM tbl_menu
GROUP BY category_code;

# 다중 컬럼 GROUP BY + COUNT 함수 
SELECT category_code, menu_price, COUNT(*)
FROM tbl_menu
GROUP BY category_code, menu_price
ORDER BY category_code, menu_price;

-- <!-----------------------------------------------------------------------!>--

# HAVING
SELECT category_code, COUNT(*)
  FROM tbl_menu
GROUP BY category_code
HAVING category_code BETWEEN 5 AND 8;

-- <!-----------------------------------------------------------------------!>--

# ROLLUP (MySQL의 특징)
#1. 컬럼 한 개를 활용해 GROUP BY 후 ROLLUP => 총계(합계) 출력
SELECT category_code, SUM(menu_price)
  FROM tbl_menu
GROUP BY category_Code
WITH ROLLUP; # 합산 값들을 출력해주는 기능 

#2. 컬럼 두 개를 활용해 GROUP BY 후 ROLLUP => 중간 합계와 총계 출력
-- 먼저 나온 컬럼의 총합을 구하고, 이후에 나오는 컬럼의 총합까지 구하는 방식
SELECT category_code, menu_price, COUNT(menu_price)
FROM tbl_menu
GROUP BY category_code, menu_price
WITH ROLLUP