SELECT
menu_name AS menu -- 별칭 사용 가능
FROM tbl_menu;
SELECT
menu_code
, menu_name
, menu_price
FROM tbl_menu
ORDER BY menu_price; -- ASC는 오름차순/DESC는 내림차순
-- ORDER BY menu_price ASC;
SELECT
menu_name
, menu_price
, orderable_status
FROM tbl_menu
WHERE orderable_status = 'Y';
SELECT
DISTINCT category_code
, orderable_status
FROM tbl_menu;
SELECT
select_list
FROM
table_name
LIMIT [offset], [row_count];
-- offset: 시작할 행의 번호(인덱스 체계)
-- row_count: 이후 행부터 반환 받을 행의 개수
SELECT
a.menu_name
, b.category_name
FROM tbl_menu a
-- INNER JOIN tbl_category b ON a.category_code = b.category_code;
JOIN tbl_category b ON a.category_code = b.category_code;
-- JOIN tbl_category b USING (category_code);
SELECT
a.category_name
, b.category_name
FROM tbl_category a
JOIN tbl_category b ON a.ref_category_code = b.category_code
WHERE a.ref_category_code IS NOT NULL;
SELECT
category_code
, COUNT(*) -- COUNT, SUM, AVG 등 함수 활용 가능
FROM tbl_menu
GROUP BY category_code
HAVING category_code BETWEEN 5 AND 8;
HAVING
ROLL UP
SELECT
region,
product,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region, product);
| region | product | total_sales |
|--------|---------|-------------|
| Asia | Laptop | 10000 |
| Asia | Tablet | 5000 |
| Asia | NULL | 15000 | -- Asia 지역의 총 매출
| Europe | Laptop | 8000 |
| Europe | Tablet | 6000 |
| Europe | NULL | 14000 | -- Europe 지역의 총 매출
| NULL | NULL | 29000 | -- 전체 총 매출
SELECT
menu_code
, menu_name
, menu_price
FROM tbl_menu
WHERE category_code = (SELECT category_code
FROM tbl_menu
WHERE menu_name = '민트미역국');
상관 서브쿼리
EXISTS
CTE(Common Table Expressions)
WITH menucate AS (
SELECT menu_name
, category_name
FROM tbl_menu a
JOIN tbl_category b ON a.category_code = b.category_code
)
SELECT
*
FROM menucate
ORDER BY menu_name;