[Oracle] 알짜 SQL 기본 상식 10가지

ALL

- 서브쿼리의 여러개의 결과를 모두 만족해야한다.

SELECT *
  FROM userTBL 
 WHERE height > ALL (SELECT height FROM userTBL WHERE addr = '경남');

ANY(SOME)

- 서브쿼리의 여러개의 결과 중 한가지만 만족해도 된다.

- " = ANY "는 "IN"과 같은 의미로 사용된다.

SELECT *
  FROM userTBL 
 WHERE height > ANY (SELECT height FROM userTBL WHERE addr = '경남');

SELECT *
  FROM userTBL 
 WHERE height = ANY (SELECT height FROM userTBL WHERE addr = '경남');

SELECT *
  FROM userTBL 
 WHERE height IN (SELECT height FROM userTBL WHERE addr = '경남');

DISTINCT

- 중복된 것은 한개씩만 보여주면서 출력된다.

SELECT DISTINCT addr
  FROM userTBL

ROWNUM

- ORDER BY 를 사용하지 않고 ROWNUM을 사용하면 항상 테이블에 저장된 상위의 5건만 조회한다.

- 즉 조회할 때마다 같은 데이터가 늘 반복해서 나올 수 밖에 없다.

SELECT employee_id, hire_date
  FROM employees
 WHERE ROWNUM <= 5

SAMPLE(퍼센트)

- '퍼센트'는 0초과 100미만의 값이어야한다.

- SAMPLE(퍼센트) 문을 사용하면 임의의 데이터 5%를 추출한다.

- employees의 총 건수가 107 건인 경우, 107*0.05=5.35 -> 약 5건이 반환된다.

SELECT employee_id, hire_date
  FROM employees SAMPLE(5)

CAST() 함수

- 소수점을 조절하고 싶다면 CAST() 함수를 사용하면 된다.

- CAST(숫자 AS 변환할형식)으로 사용한다.

SELECT userID, CAST(AVG(amount) AS NUMBER(5,3)) AS "평균구매개수"
  FROM buyTBL
 GROUP BY userID

USERID	평균구매개수
BBK	4.75
SSK	5
EJW	1.333

COUNT() 함수

- COUNT(*) -> 모든 개수 출력

- COUNT(열이름) -> 해당 열에서 NULL값인 것은 제외하고 카운트 한다.

ROLLUP() 함수

SELECT idNum, groupName, SUM(price*amount) AS "비용"
  FROM buyTb1
 GROUP BY ROLLUP(groupName, idNum)

IDNUM	GROUPNAME	비용
7	서적	75
8	서적	30
11	서적	15
(null)	서적	120
5	의류	150
9	의류	50
(null)	의류	200
(null)	(null)	320

- idNum이 NULL로 되어있는 추가행이 각 그룹의 소합계를 의미하고, 또 마지막 행은 각 소합계의 합계, 즉 총합계의 결과가 나왔다.

- idNum은 Primary Key이며 그룹화가 되지않는 효과를 위해서 넣어준 것이다. 약 소합계 및 총합계만 필요하다면 아래의 쿼리(ROLLUP에 idNum을 뺌.)를 이용하면 된다.

SELECT groupName, SUM(price*amount) AS "비용"
  FROM buyTb1
 GROUP BY ROLLUP(groupName)

GROUPNAME	비용
서적	120
의류	200
(null)	320

GROUPING_ID() 함수

- GROUPING_ID() 함수의 결과가 0이면 데이터이고, 1이면 합계를 위해서 추가된 열이라고 보면 된다.

SELECT groupName, SUM(price*amount) AS "비용"
     , GROUPING_ID(groupName) AS "추가행여부"
  FROM buyTb1
 GROUP BY ROLLUP(groupName)

GROUPNAME	비용	추가행여부
서적	120	0
의류	200	0
(null)	320	1

CUBE() 함수

- ROLLUP()과 비슷한 개념이지만 CUBE()는 다차원 정보의 데이터를 요약하는데 더 적당하다.

WITH절

[ WITH <Sub Query> ]
SELECT select_list [ INTO new_table ]
[ FROM table_source ]
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ] 
[ ORDER BY order_expression [ASC | DESC ] ]

- WITH절은 CTE(Common Table Expression) 을 표현하기 위한 구문이다.

- CTE는 기존의 뷰, 파생 테이블, 임시테이블 등으로 사용되던 것을 대신할 수 있으며, 더 간결한 식으로 보여지는 장점이 있다.

비재귀적 CTE

- 단순한 형태이며 복잡한 쿼리문장을 단순화시키는데에 적합하게 사용될 수 있다.

WITH CTE_테이블이름(열 이름)
AS
(
    <쿼리문>
)
SELECT 열 이름 FROM CTE_테이블이름

WITH abc(userID, total)
AS
( SELECT userID, SUM(price*amount)
    FROM buyTb1
   GROUP BY userID
 )
 
 SELECT *
   FROM abc
  ORDER BY total DESC

- 복잡한 쿼리를 WITH구문으로 만들어서 SELECT문을 단순화 시켜준다.

WITH
AAA(userID, total)
  AS
    (SELECT userID, SUM(price*amount) FROM buyTb1 GROUP BY userID),
BBB(sumtotal)
  AS 
    (SELECT SUM(total) FROM AAA),
CCC(sumavg)
  AS 
    (SELECT sumtotal/(SELECT count(*) FROM buyTb1) FROM BBB)
    
SELECT * FROM CCC

- CCC 쿼리문에서는 AAA, BBB를 참조할 수 있지만, AAA의 쿼리문에는 BBB나 CCC를 참조할 순 없다.

- 즉, 아직 정의되지 않은 CTE를 미리 참조할 수 없다.

재귀적 CTE

- 자기자신을 반복적으로 호출한다는 의미를 내포한다.

WITH CTE_테이블이름(열이름)
AS
(
    <쿼리문1: SELECT * FROM 테이블A>
    UNION ALL
    <쿼리문2: SELECT * FROM 테이블A JOIN CTE_테이블이름>
)
SELECT * FROM CTE_테이블이름

- 트리구조로 되어있는 조직 테이블을 재귀적으로 호출할 때 사용하곤 한다.

- 쿼리문1 : 앵커멤버(Anchor Member:AM), 쿼리문2 : 재귀멤버(Recursive Member:RM) 라고 지칭한다. 

<empTb1>

emp(직원이름)	manager(상관이름)	department(부서)
나사장	없음(NULL)	없음(NULL)
김재무	나사장	재무부
김부장	김재무	재무부
이부장	김재무	재무부
우대리	이부장	재무부
지사원	이부장	재무부
이영업	나사장	영업부
한과장	이영업	영업부
최정보	나사장	정보부
윤차장	최정보	정보부
이주임	윤차장	정보부

WITH empCTE(empName, mgrName, dept, empLevel)
AS
(
    (SELECT emp, manager, department, 0
       FROM empTb1
      WHERE manager = '없음' ) --상관이 없는 사람이 사장
     UNION ALL
    (SELECT empTb1.emp, empTb1.manager, empTb1.department, empCTE.empLevel+1
       FROM empTb1 INNER JOIN empCTE
         ON empTb1.manager = empCTE.empName)
)
SELECT * FROM empCTE ORDER BY dept, empLevel;

<결과값>

empName	mgrName	dept	empLevel
나사장	없음	없음	0
이영업	나사장	영업부	1
한과장	이영업	영업부	2
김재무	나사장	재무부	1
김부장	김재무	재무부	2
이부장	김재무	재무부	2
우대리	이부장	재무부	3
지사원	이부장	재무부	3
최정보	나사장	정보부	1
윤차장	최정보	정보부	2
이주임	윤차장	정보부	3

http://www.yes24.com/Product/Goods/58731074

 

이것이 오라클이다 - YES24