ANY/ALL/SOME(SubQuery), 정렬, DISTINCT, LIMIT, CREATE TABLE … SELECT, GROUP BY, HAVING, 집계 함수
SELECT문
ANY/ALL/SOME(SubQuery), 정렬, DISTINCT, LIMIT, CREATE TABLE … SELECT, GROUP BY, HAVING, 집계 함수
ANY/ALL/SOME 그리고 서브쿼리(SubQuery, 하위쿼리)
-
서브쿼리
▪ FROM/WHERE 절에 SELECT 문을 제시
▪ 서브 쿼리는 반드시 ()안에 작성
SELECT Name, height FROM userTBL WHERE height > 177;키가 177보다 큰 사람 출력
SELECT Name, height FROM userTbl
WHERE height > (SELECT height FROM userTbl WHERE Name = '김경호');김경호의 키보다 큰 사람 출력
SELECT Name, height FROM userTbl
WHERE height >= (SELECT height FROM userTbl WHERE addr = '경남');경남에 사는 사람보다 키가 큰 사람 출력
-
ANY/ALL
SELECT height FROM userTbl WHERE addr = '경남';
SELECT Name, height FROM userTbl
WHERE height >= ANY (SELECT height FROM userTbl WHERE addr = '경남');
SELECT Name, height FROM userTbl
WHERE height >= ALL (SELECT height FROM userTbl WHERE addr = '경남');
-
SOME
▪ ANY와 같은 의미
▪ = 연산자와 사용 → IN과 동일
SELECT Name, height FROM userTbl
WHERE height = SOME (SELECT height FROM userTbl WHERE addr = '경남');
SELECT Name, height FROM userTbl
WHERE height IN (SELECT height FROM userTbl WHERE addr = '경남');
원하는 순서대로 정렬하여 출력
-
오름차순 : ASC (디폴트, 생략가능)
-
내림차순: DESC
SELECT Name, mDate FROM userTbl ORDER BY mDate;
SELECT Name, mDate FROM userTbl ORDER BY mDate DESC;
SELECT Name, height FROM userTbl ORDER BY height DESC, name ASC;
중복된 것은 하나만 남기는 DISTINCT
SELECT addr FROM userTbl;
SELECT addr FROM userTbl ORDER BY addr;
SELECT DISTINCT addr FROM userTbl;
출력하는 개수를 제한하는 LIMIT
USE employees;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 5 ;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 0, 5; -- LIMIT 5 OFFSET 0 과 동일
테이블을 복사하는 CREATE TABLE … SELECT
-
기존 테이블과 동일한 구조로 테이블 생성
-
KEY 제약조건은 복사되지 않음
▪ 필드의 이름, 타입, 길이, NULL 여부가 동일
-
특정 컬럼(SELECT 절 제시) 또는 특정 행만(WHERE절 제시) 복사 가능
형식]
CREATE TABLE 새로운 테이블명(SELECT 복사할열 FROM 기본테이블명)
USE sqlDB;
CREATE TABLE buyTbl2 (SELECT * FROM buyTbl);
SELECT * FROM buyTbl2;
CREATE TABLE buyTbl3 (SELECT userID, prodName FROM buyTbl);
SELECT * FROM buyTbl3;
GROUP BY 및 HAVING 그리고 집계 함수
-
GROUP BY 절
▪ 특정 컬럼에 대해 동일한 값을 가지는 행들을 하나의 행으로 처리
▪ 통계 작업에 사용
SELECT userID, amount
FROM buyTbl
ORDER BY userID;
SELECT userID, SUM(amount)
FROM buyTbl
GROUP BY userID;
SELECT userID AS '사용자 아이디', SUM(amount) AS '총 구매 개수'
FROM buyTbl
GROUP BY userID;
SELECT userID AS '사용자 아이디', SUM(price*amount) AS '총 구매액'
FROM buyTbl
GROUP BY userID;
-
집계 함수
USE sqlDB;
SELECT AVG(amount) AS '평균 구매 개수'
FROM buyTbl ;
SELECT userID, AVG(amount) AS '평균 구매 개수'
FROM buyTbl
GROUP BY userID;
SELECT name, MAX(height), MIN(height) FROM userTbl;
SELECT Name, MAX(height), MIN(height)
FROM userTbl
GROUP BY Name;
SELECT Name, height
FROM userTbl
WHERE height = (SELECT MAX(height)FROM userTbl)
OR height = (SELECT MIN(height)FROM userTbl) ;
전체 행의 개수
SELECT COUNT(*) FROM userTbl;
지정한 컬럼에 NULL있는 행은 제외
SELECT COUNT(mobile1) AS '휴대폰이 있는 사용자'
FROM userTbl;
-
HAVING 절
▪ GROUP BY 결과에서 필터링
USE sqlDB;
SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
FROM buyTbl
GROUP BY userID ;
SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
FROM buyTbl
WHERE SUM(price*amount) > 1000
GROUP BY userID ;
SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
FROM buyTbl
GROUP BY userID
HAVING SUM(price*amount) > 1000 ;
SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
FROM buyTbl
GROUP BY userID
HAVING SUM(price*amount) > 1000
ORDER BY SUM(price*amount) ;
-
ROLLUP
▪ 중간 합계와 총 합을 출력
SELECT num, groupName, SUM(price * amount) AS '비용'
FROM buyTbl
GROUP BY groupName, num
WITH ROLLUP;
SELECT groupName, SUM(price * amount) AS '비용'
FROM buyTbl
GROUP BY groupName #
WITH ROLLUP;
댓글