[SQL]-SELECT절

1. 기본 SELECT절 

SELECT * FROM tbl_menu;

SELECT
  	 menu_code
	,menu_name
	,menu_price
	,category_code
	,orderable_status
 FROM tbl_menu;

1-1. REUSLT SET: 결과 집합

1-2. 외래키로 조인해서 가져오기

1-3. FROM절이 없는 SELECT

-- from절 없는 select 해보기
SELECT 7+3;
SELECT 10*3;
SELECT 6%3;
-- 현재시간
SELECT NOW();
--  문자열 붙이기 
SELECT concat ('유',' ',' ',' ','관순');
SELECT CONCAT('메뉴 이름은: ', menu_name, '이고, 가격은 ', menu_price) FROM tbl_menu;
-- 별칭(alias) 
SELECT 7+3 AS '합', 10*2 AS '곱', 4/2; 
SELECT NOW() AS '현재 시각';
SELECT 7+3 AS '합 입니다.'; -- 별칭을 달 때 별칭에 특수기호(. ? ! 등)가 있다면
									 -- 싱글쿼테이션(')을 반드시 추가한다.

2. ORDER BY 절

 2-1. ORDER BY 기본 문법

 

 2-2. 내림 차순하기

이때 ASC는 생략 가능하다. 이유는 ORDER BY 절의 기본은 오름차순이기 때문이다.

2-3. 별칭(alias)로 확인하기

2-4. field 함수를 이용하기

 

맨 첫번째 값의 위치를 도출하는 함수

(a는 두번째 에서 2번째에 있음을 검색하여 위치를 찾는 함수)

 

2-5. null 값

1. 오름차순시 null이 먼저 나옴

2. 내림차순시 null이 마지막에 나온다.

 

그렇다면 내림차순에서 null이 먼저 나오게 하려면 어떻게 해야 하는가?

1. null이 먼저 나오게 오름차순을 한다.

2. 정렬하고자하는 필드 앞에 '-' 를 붙여 값이 있는 데이터를 내림차순으로 수정한다.

3. 이때 NULL은 값이 비워져 있는 것이므로 '-' 를 붙여도 위치는 변하지 않는다. (값이 있는 경우에만 -에 반응한다)

3. WHERE 절

3-1. <>, != ,>,< 등의 비교연산자 사용

3-2. BETWEEN A AND B

 

3-3. LIKE '%'

3-4. IN(A,B,C)

3-5. IS NULL, IS NOT NULL

 

 

wherer 절의 in,like,between 연산자는 바로 앞에 not을 붙이면 부정이 되며, is null만 다르므로 주의한다.

4. SELECT ,FROM, ORDER BY 통합 사용

5. DISTINCT

5-1. DISTINCT 기본 사용

5-2. 상위 카테고리만 조회하기

-- 1) where 절 활용
SELECT 
		*
	FROM tbl_category
	WHERE ref_category_code IS NULL;
	
-- 2) distinct 활용하기
SELECT 
		DISTINCT ref_category_code
	FROM tbl_category
	WHERE ref_category_code IS not null;
	
-- 3) 서브쿼리 활용하기
SELECT 
	*
	FROM tbl_category
	WHERE category_code IN(
	SELECT ref_category_code
		FROM tbl_category
		WHERE ref_category_code IS NOT null
	);

	SELECT ref_category_code
		FROM tbl_category
		WHERE ref_category_code IS NOT NULL;

6. LIMIT

6-1. LIMIT(시작 인덱스,개수)

 

LIMIT 키워드를 통해 슬라이싱(페이징)을 할 수 있다. oracle에는 없지만 mysql,mariadb에는 있다.

6-2. LIMIT 를 통한 길이 제한

SELECT 
	*
	FROM tbl_menu
	LIMIT 10; -- 10(0~9 인덱스)개까지

7. JOIN

7-1. INNER JOIN

 

메뉴를 보지만 카테고리를 보고 싶은 경우에는? 두 테이블을 Join 하여 조회한다.

 inner join (inner는 생략 가능)

<특징>

1. ON or USING 키워드 활용
2. JOIN 은 별칭이 필수이다.

-- 2) using을 활용
-- join할 테이블들의 매핑 컬럼명들이 동일한 경우에만 사용가능
SELECT 
		  a.menu_name
		, b.category_name
		, a.category_code
	FROM tbl_menu a
	JOIN tbl_category b USING (category_code);

7-2.  OUTTER JOIN

7-2-1. LEFT OUTTER JOIN

-- 1) left join
SELECT 
		 a.category_name
		,b.menu_name
	FROM tbl_category a
	LEFT JOIN tbl_menu b ON(a.category_code=b.category_code);

7-2-2. RIGHT OUTTER JOIN

-- 2) right join
SELECT 
		  a.menu_name
		 ,b.category_name
	FROM tbl_menu a
	RIGHT JOIN tbl_category b ON(a.category_code=b.category_code);

7-3. CROSS JOIN

 외래키 일치 상관없이 그냥 전부다 가져온다.

 

7-4. SELF JOIN

계층형 테이블에서 적용한다. 

 

8. GROUP BY

그룹 함수(count,sum,avg,max,min)를 적용하기 위해 사용

8-1. 기본 문법

-- 메뉴가 존재하는 카테고리 그룹 조회
SELECT 
		category_code -- distinct 한것과 같다
		FROM tbl_menu
   GROUP BY category_code; -- category_code 컬럼을 기준으로 그룹화

<결과>

8-2. count, sum, avg 함수 사용

-- 1) count 함수
SELECT
	  COUNT(*) AS '모든 카테고리 행'
	 ,COUNT(ref_category_code) AS '상위 카테고리가 존재하는 카테고리' -- null값 제외
FROM tbl_category;

-- 2) sum 함수
SELECT
			 category_code
			,SUM(menu_price)
		FROM tbl_menu
	GROUP BY category_code;
	
-- 3) avg 함수
SELECT
			 category_code
			,sum(menu_price)
			,floor(avg(menu_price)) -- 버림
			,COUNT(category_code)
		FROM tbl_menu
	GROUP BY category_code;

<결과>

1. count

2. sum

 

3. avg

8-3. HAVING

HAVING절은 GROUP BY절의 조건문으로 사용한다.

-- 4) having 절
SELECT
			 SUM(menu_price)
			,category_code
		FROM tbl_menu
	GROUP BY category_code 
	HAVING category_code BETWEEN 5 AND 9;

SELECT
			 SUM(menu_price)
			,category_code
		FROM tbl_menu
	GROUP BY category_code 
	HAVING SUM(menu_price) >= 20000;
	
SELECT
		 menu_price
		FROM tbl_menu
	GROUP BY category_code 
	HAVING menu_price BETWEEN 7000 AND 90000;
	
SELECT
		 category_code
		,AVG(menu_price)
	FROM tbl_menu
	WHERE menu_price >10000
	GROUP BY category_code
	HAVING AVG(menu_price) >12000 -- having절은 주로 그룹함수 or 그룹 단위 조건
		ORDER BY 1 DESC;

 

8-4. ROLL UP

-- ROLLUP: 그룹함수랑 같이 사용함
-- group을 묶을 때 하나의 기준(하나의 컬럼)으로 그룹화하여 rollup
-- 총 합계 등을 이용할때 사용	
SELECT
			 sum(menu_price)
			,category_code
		FROM tbl_menu
	GROUP BY category_code WITH ROLLUP;

-- group을 묶을 때 여러개의 기준(여래그이 컬럼)으로 그룹화하여 rollup
SELECT
			  SUM(menu_price)
			, menu_price
			, category_code
		FROM tbl_menu
	GROUP BY menu_price, category_code -- menu_price 
		WITH ROLLUP;

9. SUBQUERYES

9-1. 기본 문법

-- '민트 미역국'과 같은 카테고리의 메뉴를 조회
-- sub query는 위치에 맞춰서 들여
SELECT 
		  menu_name
		, category_code
	FROM tbl_menu
	WHERE category_code =(SELECT category_code 
										FROM tbl_menu
									WHERE menu_name='민트미역국');

 

9-2. SUBQUERYES를 이용한 문제

가장 많은 메뉴가 포함된 카테고리를 조회하라

 

9-2-1. 카테고리 별 개수, 코드, 이름 조회

SELECT COUNT(b.category_code) AS category_count
			,b.category_code
			,c.category_name
			FROM tbl_menu b
			JOIN tbl_category c ON b.category_code=c.category_code
			GROUP BY b.category_code;

 

<결과>

 

9-2-2. 1의 결과를 새로운 테이블로 사용하여 From 절에 이용

SELECT 
	 MAX(a.category_count) AS '메뉴개수'
	,a.category_code
	,a.category_name
	-- 조회한 테이블을 하나의 테이블로 취급한다.
	FROM (SELECT COUNT(b.category_code) AS category_count
			,b.category_code
			,c.category_name
			FROM tbl_menu b
			JOIN tbl_category c ON b.category_code=c.category_code
			GROUP BY b.category_code) a; -- (select로 새로 만든 테이블은  반드시 별칭을 부여한다.)

 

<결과>

 

9-3. 상관서브쿼리

메인 쿼리가 서브쿼리의 결과에 영향을 주는 경우 상관 서브쿼리라고 한다.
SUBQUERY를 활용해 카테고리별 평균 가격보다 높은 가격의 메뉴 조회

SELECT                                                   
       menu_code                                         
     , menu_name                                         
     , menu_price                                        
     , category_code                                     
     , orderable_status                                  
  FROM tbl_menu a     
                                     
 WHERE menu_price > (SELECT AVG(menu_price)
                       FROM tbl_menu                  
                      WHERE category_code = a.category_code);​

 

결과

9-4. EXISTIS

 💡 where절에서 사용하며, 결과가 하나라도 존재하면 true, 아니면 false를 반환한다.

-- 카테고리 중에 메뉴에 부여된 카테고리들의 카테고리 이름 조회후 오름차순 정렬
SELECT 
		a.category_name
	FROM tbl_category a
	WHERE EXISTS (SELECT menu_code -- 반환되는 메뉴 행
						FROM tbl_menu b
						WHERE b.category_code=a.category_code)

 

10. SET OPERATOR

 

 💡 자바에서 중복을 제거한 자료구조: set
SET 연산자는 두 개 이상의 SELECT문의 결과 집합을 결합하는데 사용한다.
SET 연산자를 통해 결합하는 결과 집합의 컬럼이 동일해야 한다.(SET 하고자 하는  )
따라서, join과 달리 컬럼명이 하나라도 다르면 안되며, result set이 세로로 합쳐져서 반환된다.
SET은 JOIN과 같이 중복을 제거하여 조회할 수 있는 공통점도 존재한다.

 

10-1. UNION(합집합)

두 개 이상의 SELECT 문의 결과를 결합하여 중복된 레코드를 제거한 후 반환하는 SQL 연산자이다.

SELECT
       menu_code
     , menu_name
     , menu_price
     , category_code
     , orderable_status
  FROM tbl_menu
 WHERE category_code = 10
 UNION
SELECT
       menu_code
     , menu_name
     , menu_price
     , category_code
     , orderable_status
  FROM tbl_menu
 WHERE menu_price < 9000;​

 

<실행 결과>

10-2.  UNION ALL (중복이 제거 되지 않은 합집합)

UNION과 달리 두 개 이상의 SELECT 문의 결과를 결합하며 중복된 레코드를 제거하지 않고 모두 반환하는 SQL 연산자이다.

10-3. INTERSECT (교집합)

두 SELECT 문의 결과 중 공통되는 레코드만을 반환하는 SQL 연산자이다.
MYSQL과 MARIADB는 INTERSECT가 공식적으로 지원되지 않는다.
 하지만 INNER JOIN 과 IN 연산자 활용해서 구현하는 것은 가능하다.

10-3-1. INNER JOIN 활용으로 교집합 구현

SELECT
       a.menu_code
     , a.menu_name
     , a.menu_price
     , a.category_code
     , a.orderable_status
  FROM tbl_menu a
 INNER JOIN (SELECT menu_code
                  , menu_name
                  , menu_price
                  , category_code
                  , orderable_status
               FROM tbl_menu
              WHERE menu_price < 9000) b ON (a.menu_code = b.menu_code);

<결과>

<코드 설명>

1. 서브쿼리 실행: 메뉴가격이 9000이하인 값들을 반환( inner view 생성) 

2. a(별칭) 테이블과 b테이블 inner join을 통해 두테이블의 중복행 제거

10-3-2. IN 연산자 활용으로 교집합 구현

--  2) in 연산자 활용
SELECT
     	  a.menu_code
		, a.menu_name
		, a.menu_price
		, a.category_code
		, a.orderable_status
	FROM tbl_menu a

	-- 여기서 "IN"이 아닌 "="을 사용할 수 없다.
	-- 왜냐하면 서브쿼리가 다중행으로 반환하기 때문이다.(where 조건문에 의해)
	WHERE a.menu_code IN (SELECT b.menu_code
									FROM tbl_menu b
									WHERE b.menu_price <9000);

 

 

10-4. MINUS (차집합)

-- outter join을 이용						
SELECT
     	  a.menu_code
		, a.menu_name
		, a.menu_price
		, a.category_code
		, a.orderable_status
	FROM tbl_menu a
	LEFT JOIN (SELECT 
					  	  b.menu_code
						, b.menu_name
						, b.menu_price
						, b.category_code
						, b.orderable_status
					FROM tbl_menu b
					WHERE b.menu_price <9000 ) c ON (a.menu_code=c.menu_code)
	WHERE  c.menu_code IS NULL;

 

<결과>

 

<코드설명>

1. 서브쿼리를 통해 메뉴가격이 9000 이하인 값을 추출(inner view 생성)

2. 테이블 a(별칭)과 b를 left join하여 a기준으로 조인하여 a테이블에서 9000이하인 값만 남고, 나머지는 null로 표시됨. 이를 통해 교집합 부분만 값이 있고, 나머지는 null값

3. null값인 부분을 반환하여 2에서 구한 교집합 부분은 제거

 

 

<차집합 원리>

 

11. 절(clause)실행순서