SQL Cook: 레코드 검색

Chappie: 코딩 좋아하는 사람·2022년 7월 2일
SQL limitcase when then else then endmaria fetch firstmaria rand()sql concatsql cookbooksql random
0

SQL_COOK

목록 보기
3/35
post-thumbnail

공통 규칙

  • 쿼리문에서 키워드 및 사용자 지정 식별자 모두 항상 소문자를 사용한다

    • 예)
      select empno, ename
	from emp;

  • 많은 해법에서 '인라인 뷰(inline view)' 또는 FROM절의 서브쿼리를 사용한다
    ANSI SQL 표준에 따르면 이러한 뷰에 테이블 별칭을 지정해야 한다
    따라서 'X' 및 'Y'와 같은 별칭을 사용하여 인라인 뷰에서 결과셋을 식별한다

    • 예)
      select job, sal
	from (select job, max(sal) sal
  		from emp
        group by job) x;
      마지막 괄호 뒤에 문자 'x'는 FROM절의 하위 쿼리에 의해 반환되는 '테이블 명'을 뜻한다

      단, Oracle은 이러한 별칭을 지정하지 않고도 사용할 수 있다

제1 장: 레코드 검색

1.1 테이블의 모든 행과 열 검색하기

select * from emp

대화형으로 즉석에서 실행하는 쿼리에서는 SELECT *을 사용하는 편이 더 쉽다.
그러나 프로그램 코드를 작성할 때는 각 열을 개별 지정하는 것이 더 좋다.

  • 성능은 동일하다
  • 그러나 쿼리에서 어떤 열을 반환하는지 명확하게 알 수 있다
  • 다른 사용자가 테이블을 이해하기 쉽다
  • SELECT *이 예상과 다른 열을 가져올 문제를 방지할 수 있다

1.2 테이블에서 행의 하위 집합 검색하기

Q. 테이블에서 특정 조건을 충족하는 행만 보려고 한다

A. WHERE 절로 가져올 행을 지정한다

select *
	from emp
    where deptno = 10

>>
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
  • 대부분의 벤더(RDBMS 관리 프로그램)은 =, <, >, <=, >=, !, <>와 같은 일반 연산자를 지원한다
  • 여러 조건을 걸고 싶다면 AND 또는 OR 및 ()를 지정하여 수행할 수 있다

1.3 여러 조건을 충족하는 행 찾기

Q. 여러 조건을 충족하는 행을 반환하려고 한다

A. OR 및 AND 절과 함께 WHERE절을 사용한다

select *
	from emp
    	where deptno = 10
        	or comm is not null
            or sal <= 2000 and deptno = 20
            
>>
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
  • 'deptno'의 값이 10인 자료
  • 'comm'의 값이 있는 자료
  • 'deptno'의 값이 20인 사람 중 'sal'의 값이 2000 이하인 자료

괄호가 있다면 괄호 안의 조건을 함께 평가한다.

select *
	from emp
    	where (deptno = 10
        	or comm is not null
            or sal <= 2000) 
        and deptno = 20

>>
+-------+-------+-------+------+------------+------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+-------+------+------------+------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 2005-12-17 |  800 | NULL |     20 |
|  7876 | ADAMS | CLERK | 7788 | 2008-01-12 | 1100 | NULL |     20 |
+-------+-------+-------+------+------------+------+------+--------+

1.4 테이블에서 열의 하위 집합 검색하기

Q. 테이블에서 모든 열이 아닌 특정 열의 값을 보려고 한다

A. 보려고 하는 열만 지정해준다

select ename, deptno, sal
	from emp
  • 불필요한 데이터 검색에 드는 시간을 줄인다
  • 네트워크를 통한 데이터 검색을 할 때 특히 중요할 수 있다 (성능 향상)

1.5 열에 의미 있는 이름 지정하기

Q. 쿼리에서 반환된 열의 이름을 더 알아보기 쉽게 변경하려고 한다

A. AS 키워드를 사용한다

select sal as salary, comm as commission
	from emp
>>
+--------+------------+
| salary | commission |
+--------+------------+
|    800 |       NULL |
|   1600 |        300 |
|   1250 |        500 |
|   2975 |       NULL |
|   1250 |       1400 |
|   2850 |       NULL |
|   2450 |       NULL |
|   3000 |       NULL |
|   5000 |       NULL |
|   1500 |          0 |
|   1100 |       NULL |
|    950 |          0 |
|   3000 |       NULL |
|   1300 |       NULL |
+--------+------------+
  • AS 키워드를 사용하여 쿼리에서 반환된 열에 새 이름을 지정하는 행위를 별칭 지정(aliasing)이라고 한다
  • 이렇게 사용자가 지정한 새 이름을 별칭(aliase)라고 한다

1.6 WHERE절에서 별칭이 지정된 열 참조하기

Q. 별칭을 적용하고 WHERE절에서도 별칭을 참조하려 하는데 실패한다

select sal as salary, comm as commission
	from emp
    where salary < 5000
>>
ERROR 1054 (42S22): Unknown column 'salary' in 'where clause'

A. 쿼리를 인라인 뷰로 감싸서 별칭이 지정된 열을 참조한다

select *
	from (
    	select sal as salary, comm as commission
        	from emp
            ) x
            where salary < 5000
>>
+--------+------------+
| salary | commission |
+--------+------------+
|    800 |       NULL |
|   1600 |        300 |
|   1250 |        500 |
|   2975 |       NULL |
|   1250 |       1400 |
|   2850 |       NULL |
|   2450 |       NULL |
|   3000 |       NULL |
|   1500 |          0 |
|   1100 |       NULL |
|    950 |          0 |
|   3000 |       NULL |
|   1300 |       NULL |
+--------+------------+
  • WHERE절은 SELECT절을 시행하기 전에 판단된다
    • 첫 번째 쿼리에서 WHERE절을 평가할 때는 아직 'salary'와 'commission'이 존재하지 않는 상태이다
  • FROM절은 WHERE절보다 먼저 평가된다
    • 따라서 본 쿼리를 FROM절 안에 배치하면 해당 쿼리의 결과가 가장 바깥의 WHERE절보다 먼저 평가되고 그 후 바깥의 WHERE절이 별칭을 확인하게 된다

1.7 열 값 이어 붙이기

Q. 여러 열의 값을 하나의 열로 반환하려 한다

다음과 같은 결과셋을 생성하려 한다.

CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

'이름'과 '직무'가 필요함을 알 수 있다.
그러나 위의 결과셋을 생성하는 데 필요한 '이름'과 '직무'는 서로 다른 열에서 가져와야 한다.

A. 이럴 때에 내장함수를 이용한다

case: Oracle / Postgre

select ename||' WORKS AS A '||job as msg
	from emp
    where deptno = 10

case: MySQL

select concat(ename, ' WORKS AS A ', job) as msg
	from emp
    where deptno = 10
>>
+---------------------------+
| msg                       |
+---------------------------+
| CLARK WORKS AS A MANAGER  |
| KING WORKS AS A PRESIDENT |
| MILLER WORKS AS A CLERK   |
+---------------------------+
  • 공통적으로 CONCAT함수를 사용한다
    Oracle / Postgre에서는 CONCAT함수의 단축키를 ||로 쓰는 것이다
  • 중간에 문자열도 ''를 사용하여 추가할 수 있다

1.8 SELECT문에서 조건식 사용하기

Q. SELECT문의 반환값에 대해 IF-ELSE 연산을 수행하려 한다

예) 급여의 값이 2000 이하라면 'UNDERPAID'를 반환하고 / 4000 이상이라면 'OVERPAID'를 반환하려 한다 / 2000과 4000 사이의 값에 대해서는 'OK'가 반환되도록 결과셋을 생성하려 한다면 어떻게 해야할까?

A. CASE식을 사용하여 SELECT문에서 조건식을 직접 수행한다

select ename, sal,
		case when sal <= 2000 then 'UNDERPAID' 
        	 when sal >= 4000 then 'OVERPAID'
             else 'OK'
        end as status
    from emp
>>
+--------+------+-----------+
| ename  | sal  | status    |
+--------+------+-----------+
| SMITH  |  800 | underpaid |
| ALLEN  | 1600 | underpaid |
| WARD   | 1250 | underpaid |
| JONES  | 2975 | ok        |
| MARTIN | 1250 | underpaid |
| BLAKE  | 2850 | ok        |
| CLARK  | 2450 | ok        |
| SCOTT  | 3000 | ok        |
| KING   | 5000 | overpaid  |
| TURNER | 1500 | underpaid |
| ADAMS  | 1100 | underpaid |
| JAMES  |  950 | underpaid |
| FORD   | 3000 | ok        |
| MILLER | 1300 | underpaid |
+--------+------+-----------+
  • CASE식을 사용하면 쿼리로 반환된 값에 대한 조건식을 수행할 수 있다
  • CASE식에 별칭을 붙여서 더 읽기 쉬운 결과셋을 반환할 수 있다
  • ELSE절은 선택사항이다
    단, ELSE절을 생략하면 CASE식에 부합하지 않는 행에 대해 NULL을 반환한다

1.9 반환되는 행 수 제한하기

Q. 쿼리에서 반환되는 행 수를 제한하려고 한다 / 순서는 상관없으며 몇 개의 행이든 가능하다

A.내장 함수를 사용한다

case: Oracle

  • WHERE절에서 ROWNUM을 사용한다
select * from emp
	where rownum <= 5

Oracle에서 사용하는 ROWNUM은 조금 다르게 작동한다

위와 같은 쿼리를 실행했을 때
1. 첫 번째 행을 가져오고 '첫 번째'라고 부른다
2. 조건인 5행이 지났는지 확인한다
3. '첫 번째'이기 때문에 행을 반환한다 / 만약 그렇지 않다면 반환하지 않는다
4. 다음 행을 가져와서 '두 번째'라고 부른다
5. 조건인 5행이 지났는지 점검한다
...

따라서, ROWNUM = 5를 사용하여 다섯 번째 행만 반환하려 한다면
1. 첫 번째 행을 가져오고 '첫 번째'라고 부른다
2. 조건인 5가 맞는지 확인한다
3. '첫 번째'이므로 가져온 행을 반환하지 않고 삭제한다
4. 다음 행을 가져와서 '첫 번째'라고 부른다 / 쿼리에서 반환되는 첫 번째 행은 말 그대로 '첫 번째'이기 때문이다
5. 3번 단계로 이동한다

이런 식으로 쿼리 수행에 실패하게 된다
Oracle에서는 이렇듯 '동등 조건을 사용하는 것은 좋지 않다

case: MySQL / Postgre

  • LIMIT을 사용한다
select * from emp
	limit 5
>>
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 2005-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
+-------+--------+----------+------+------------+------+------+--------+

1.10 테이블에서 n개의 무작위 레코드 반환하기

Q. 테이블에서 특정 개수의 무작위 레코드를 반환하려고 한다 / 연속 실행 시 각기 다른 5개의 행이 뭉친 집합을 생성하도록 다음 구문을 수정한다면?

select ename, job
	from emp

A. 내장 함수를 사용한다 / ORDER BY절에 함수를 사용하여 행을 무작위 정렬하고 1.9에서 사용한 기법으로 특정 개수를 정한다

case: Oracle

  • 내장 패키지의 DBMS_RANDOM에 있는 함수 VALUE를 ORDER BY 및 내장 함수 ROWNUM과 함께 사용한다
select *
	from(
    select ename, job
    	from emp
        	order by dbms_random.value()
            )
    where rownum <= 5

case: MySQL

  • 내장 함수 RAND를 ORDER BY 및 FETCH와 함께 사용한다
select ename, job
	from emp
    order by rand() fetch first 5 rows only
>>
+--------+----------+
| ename  | job      |
+--------+----------+
| FORD   | ANALYST  |
| BLAKE  | MANAGER  |
| ADAMS  | CLERK    |
| SCOTT  | ANALYST  |
| MARTIN | SALESMAN |
+--------+----------+
  • FETCH FIRST 숫자 rows only는 LIMIT과 같은 기능을 한다
    + select * from emp fetch first 5 rows only : 결과셋을 반환한다
  • ORDER BY절은 함수의 반환값을 받아 결과셋의 순서를 변경하는 데 사용할 수 있다
  • ORDER BY절의 함수가 실행된 후 반환할 행 수를 제한한다

ORDER BY절의 함수와 숫자 상수 사용을 혼동하지 않도록 주의한다.
ORDER BY절에 숫자 상수를 지정한다는 것은 SELECT 목록의 순서 위치 중에서, 그 수에 해당하는 열에 따라 정렬하도록 요청한다는 의미이다.
ORDER BY절에 함수를 지정하면, 각 행에 대해 계산한 함수의 결과에 따라 정렬이 수행된다.

ORDER BY ?

  • 크기에 따라 정렬한다
  • 기본적으로 오름차순 정렬이다
  • 내림차순에는 마지막에 DESC를 붙여준다

정리

1. 6장

  • 평가의 순서가 신기했다
  • FROM -> WHERE -> SELECT 이므로 이를 이용하여 FROM절에 alias를 붙이고 WHERE절에서 alias를 확인하는 기술은 도움이 크게 될 것 같다

1. 7장

  • select concat(열이름, '', 열이름) as 출력할 col 이름
  • 함수를 이용하여 결과셋을 문장처럼 만드는 것은 처음 알았다
  • 아주 유용하게 쓸 수 있을 것 같다

1. 8장

  • if - else if... - else
  • case when 조건식 then 출력 - else - end
  • 이런 게 있는지도 처음 알았고 함수처럼 작동하는 것이 놀라웠다
  • 매우 유용하게 쓸 것 같다

1. 10장

  • 무작위는 언제나 쓸 만하다
  • FETCH FIRST 개수 ONLY도 LIMIT와 함꼐 알아둬야 할 것 같다

오라클은 전반적으로 복잡해보인다. 아직 초반이기 때문에 나중은 알 수 없지만 Oracle을 병행해야 할지에 대한 생각을 해봐야겠다.
profile
Chappie: 코딩 좋아하는 사람
Can do this all day
이전 포스트

SQL Cook: DBeaver & DB 연동

다음 포스트

SQL Cook: 레코드 검색(마무리)

0개의 댓글