[MySQL] SubQuery(서브쿼리), ORDER BY, DISTINCT, GROUP BY, HAVING, VIEW

SELECT [Colmun] FROM [Table] WERHER [ROW]

 

SubQuery (서브쿼리)

SQL의 SELECT 명령문이며 Query 안에 있는 Query를 말한다.

보통 WHERE 절 안의 추가적인 질문이 연속될 때 사용한다.

※ Query : 질의문 

 

SubQuery의 종류

• Nested Subqueries : 중첩 서브쿼리. WHERE절 안에서 작성
• Inline View : FROM 절 안에서 작성
• Scalar Subqueries : SELECT 절 안에서 작성

 

 

▶ 김경호의 키보다 크거나 같은 사람의 이름과 키를 조회한다.

SELECT name, height FROM usertbl WHERE height >= (SELECT height FROM usertbl WHERE name='김경호');

 

▶ 성시경보다 나이가 많은 사람의 모든 정보를 가져와라.

SELECT * FROM usertbl WHERE birthYear < (SELECT birthYear FROM usertbl WHERE name = '성시경');

 

▶ 경남 사람보다 큰 사람의 이름과 키를 가져와라.

SELECT name, height FROM usertbl WHERE height > (SELECT height FROM usertbl WHERE addr = '경남');

원인 : Subquery가 1개 이상이기 때문에 정보를 비교할 수 없어 에러가 난다.

→ 해결 : ANY와 ALL을 통해 Subquery를 완성해준다.

 

 

먼저 경남사람의 키는 173과 170이 있다는걸 확인할 수 있다.

SELECT height FROM usertbl WHERE addr='경남';

 

→ ANY를 통한 해결.

※ ANY = 다수의 조건중 하나라도 만족하면 식을 인정한다. || 연산이다.

SELECT name, height FROM usertbl WHERE height > ANY(SELECT height FROM usertbl WHERE addr = '경남');

170보다 크거나 173보다 큰 두가지 조건이 하나라도 만족 되면 식이 성립되기 때문에 가장 작은 경남사람의 키(170) 보다 큰 사람의 정보를 가져오게 되는것이다.

 

→ ALL을 통한 해결.

※ ALL = 다수의 조건 모두가 만족하면 식을 인정한다. && 연산이다.

SELECT name, height FROM usertbl WHERE height > ALL(SELECT height FROM usertbl WHERE addr = '경남');

170보다 크고 173보다 큰 조건이 만족 되야 하기 때문에 가장 큰 경남사람의 키(173) 보다 큰 사람의 정보를 가져오게 되는것이다.

 

---

 

 

조회 결과 정렬

ORDER BY

• 조회결과를 정렬하는 구문이다.
• 조회결과에 영향을 주지 않는다.
• SELECT 문의 끝에 위치한다.

 

 

DESC (Descending) : 내림차순

ASC (Ascending) : 오름차순

 

 

 

모든 정보를 키가 큰 순서대로(내림차순) 정렬하라.

SELECT * FROM usertbl ORDER BY height DESC;

 

키가 작은 순서(오름차순) 다음 생년이 작은 순서(오름차순) 대로 모든 정보를 정렬하라.

SELECT * FROM usertbl ORDER BY height ASC, birthYear ASC;

 

 

 

---

 

 

중복제거

DISTINCT

하나의 정보를 중복 없이 조회하기 위한 상황에서 사용한다.

 

※ 여러개의 컬럼을 선택할시 DISTINCT가 무효화 된다.

SELECT  DISTINCT addr, name  FROM usertbl;

 

 

현재 가지고 있는 주소를 확인한다.

SELECT addr FROM usertbl;

DISTINCT를 통해 중복된 정보를 제거하여 정보를 확인한다.

SELECT DISTINCT addr FROM usertbl;

 

※ addr과 name 두가지 정보를 중복 없이 확인하는것은 무결성에 어긋나기 때문에 두가지 정보가 모두 조회되며 에러로 출력되지 않는 이유는 MySql에서 DISTINCT는 옵션이기 때문이다. Oracle에서는 Error로 출력된다.

SELECT DISTINCT addr, name FROM usertbl;

 

 

 

---

 

 

그룹핑

특정 종목별 데이터의 집합 필요한 상황에서 자주 쓰인다.

GROUP BY

• 조회 결과를 특정 기준으로 Grouping을 수행한다.
• WHERE절 다음에 위치한다.

~ 별로 ~ 하라

EX) 회월 별로 구매 상품의 평균 가격을 구해라

 

집계 함수 (집합 함수) 의 종류

• AVG(...) : 평균
• MIN(...) : 최소값
• MAX(...) : 최대값
• COUNT(...) : 행의개수

 

 

 

▷ userID 별로 amount의 합을 가져와라.

SELECT userID, SUM(amount) FROM buytbl GROUP BY userID

 

▷ userID 별 price의 합을 가져와라.

SELECT userID, SUM(price * amount) FROM buytbl GROUP BY userID;

+ 합계의 컬럼 이름을 AS로 바꿔준다.

SELECT userID, SUM(price * amount) AS 합계 FROM buytbl GROUP BY userID;

 

▷ userID별 평균 구매액을 가져와라.

SELECT userID, AVG(Price * amount) AS 평균구매액 FROM buytbl GROUP BY userID;

 

▷ userID별 구매 건수를 가져와라.

SELECT userID, COUNT(userID) AS 구매건수 FROM buytbl GROUP BY userID;

 

▷ height가 가장 크고 가장 작은 정보의 userID와 height를 가져와라.

SELECT name, height FROM usertbl WHERE height = (SELECT MAX(height) FROM usertbl) OR height = (SELECT MIN(height) FROM usertbl);

+ OR문은 두 조건중 성립하는 조건 모두를 출력하며 AND문은 해당 조건이 모두 성립해야 식이 출력한다.

 

▷ mobile1의 수를 가져와라.

SELECT COUNT(mobile1) FROM usertbl;

 

 

---

 

HAVING 절

사용자별 총 구매액수를 조회할 때 사용한다.

WHERE는 집계 함수 결과에 적용할 수 없기 때문에 HAVING이 필요하다.

 

▷ 사용자 구매 총액이 1000보다 높은 userId와 구매 총액을 가져와라.

SELECT userID, SUM(price * amount) AS `구매총액` FROM buytbl GROUP BY userID HAVING `구매총액` > 1000;

 

 

---

 

VIEW

• 사용자가 반복적으로 원하는 하나 이상의 테이블을 선택하여 제작하는 새로운 테이블이다.
• 사용자에게 접근이 허용된 자료만을 제한적으로 보여주는 가상 테이블이다.

VIEW의 필요성

• 논리적으로 데이터가 독립성을 가질 수 있다.
• 다양하게 응용할 수 있다.
• 보안성이 있다.

VIEW의 생성

CREATE VIEW v_usertbl AS SELECT userID, name, addr FROM usertbl;

 

 

 

---

+ 예제

 

이승기보다 나이가 많고 addr가 서울인 name을 출력하시오.

SELECT name, birthYear, addr FROM usertbl WHERE birthYear < (SELECT birthYear FROM usertbl WHERE name ='이승기') AND addr = '서울'

 

이승기보다 나이가 많고 addr 가 서울인 사람의 수를 출력하시오.

SELECT COUNT(name) AS'이승기보다 나이많고 서울 사는 사람' FROM usertbl WHERE birthYear < (SELECT birthYear FROM usertbl WHERE name ='이승기') AND addr = '서울'