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

Chappie: 코딩 좋아하는 사람·2022년 7월 3일

SQL IS NULL SQL LIKE sql sql coalesce

SQL_COOK

목록 보기

4/35

복습

공통 규칙

쿼리문에서 키워드 및 사용자 지정 식별자 모두 항상 소문자를 사용한다
- 예)
```
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은 이러한 별칭을 지정하지 않고도 사용할 수 있다

`CONCAT`

열 값을 이어 붙일 수 있도록 해주는 내장 함수이다

'이름'의 급여는 'X원'입니다라는 결과셋을 반환하겠다

select concat(ename,'\'s salary is ', sal) as message
	from emp;
>>
+-------------------------+
| message                 |
+-------------------------+
| SMITH's salary is 800   |
| ALLEN's salary is 1600  |
| WARD's salary is 1250   |
| JONES's salary is 2975  |
| MARTIN's salary is 1250 |
| BLAKE's salary is 2850  |
| CLARK's salary is 2450  |
| SCOTT's salary is 3000  |
| KING's salary is 5000   |
| TURNER's salary is 1500 |
| ADAMS's salary is 1100  |
| JAMES's salary is 950   |
| FORD's salary is 3000   |
| MILLER's salary is 1300 |
+-------------------------+

특수문자를 그대로 쓸 수 있게 해주는 이스케이프 지원 기호 \(백슬래시)가 SQL에서도 그대로 쓰일 수 있는 것 또한 확인되었다

`CASE WEHN ... THEN ... END (+AS X)`

일반적인 언어에서의 if문을 SQL에서도 쓸 수 있다는 것을 배웠다.

salesman이라면 'target'을 출력하고 / 이외에는 'non-target'이라고 출력하는 결과셋을 반환하겠다

select ename,
		case when job = 'salesman' then 'target'
	        else 'non-target'
	        end as Search_target
        from emp;
>>
+--------+---------------+
| ename  | Search_target |
+--------+---------------+
| SMITH  | non-target    |
| ALLEN  | target        |
| WARD   | target        |
| JONES  | non-target    |
| MARTIN | target        |
| BLAKE  | non-target    |
| CLARK  | non-target    |
| SCOTT  | non-target    |
| KING   | non-target    |
| TURNER | target        |
| ADAMS  | non-target    |
| JAMES  | non-target    |
| FORD   | non-target    |
| MILLER | non-target    |
+--------+---------------+

문자열에 대해서도 정상적으로 CASE WHEN이 작동되는 것을 확인했다

무작위 n개 레코드 반환하기

MariaDB에서는 RAND()라는 내장함수를 사용하여 난수를 생성한다
LIMIT를 사용하여 출력되는 row의 개수를 조절할 수 있다
FETCH FIRST n ROWS ONLY도 출력되는 row의 개수를 조절하는 데에 사용할 수 있다
ORDER BY와 함께 사용한다
'emp' 테이블에서 무작위 7개의 행을 반환해보겠다

select * from emp
	order by rand() limit 7
>>
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|  7788 | SCOTT  | ANALYST  | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|  7902 | FORD   | ANALYST  | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|  7566 | JONES  | MANAGER  | 7839 | 2006-04-02 | 2975 | NULL |     20 |
+-------+--------+----------+------+------------+------+------+--------+


select * from emp
	order by rand() fetch first 7 rows only
>>
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|  7902 | FORD   | ANALYST  | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|  7788 | SCOTT  | ANALYST  | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK    | 7782 | 2007-01-23 | 1300 | NULL |     10 |
|  7900 | JAMES  | CLERK    | 7698 | 2006-12-03 |  950 |    0 |     30 |
+-------+--------+----------+------+------------+------+------+--------+

두 가지 방식 모두 정상적으로 출력해주고 있다

1. 11 `NULL`값 찾기

Q. 특정 열에 대해 값이 `NULL`인 모든 행을 찾으려고 한다

A. 값이 NULL인지 여부를 확인하려면 `IS NULL`을 사용한다

select * 
	from emp
    where comm is null
>>
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+

null은 자기 자신을 포함하여 다른 무엇과도 비교할 수 없다
따라서 null 여부의 판별에는 = 또는 !=을 사용할 수 없음에 유의하자
null의 판별에는 반드시 IS NULL을 사용하도록 한다

+ `IS NOT NULL`을 사용하여 주어진 열에서 `NULL`이 없는 행을 찾을 수도 있다

select *
	from emp
    where comm is not null
>>
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 2006-12-03 |  950 |    0 |     30 |
+-------+--------+----------+------+------------+------+------+--------+

IS NOT NULL은 말 그대로 NULL값이 아닌 것 즉, 값을 가진 행만 확인하여 반환한다

1. 12 `NULL`값을 실젯값으로 변환하기

Q. `NULL`이 포함된 '행'에서 해당 `NULL` 대신 `NULL이 아닌 값`을 반환하려고 한다

A. `COALESCE` 함수를 사용하여 `NULL`값을 실젯값으로 대체한다

select coalesce(comm, 0)
	from emp
>>
+-------------------+
| coalesce(comm, 0) |
+-------------------+
|                 0 |
|               300 |
|               500 |
|                 0 |
|              1400 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
+-------------------+

# aliase를 붙여보았다
select coalesce(comm, 'is null') as comm_is_null
	from emp
>>
+--------------+
| comm_is_null |
+--------------+
| is null      |
| 300          |
| 500          |
| is null      |
| 1400         |
| is null      |
| is null      |
| is null      |
| is null      |
| 0            |
| is null      |
| 0            |
| is null      |
| is null      |
+--------------+

COALESCE 함수는 하나 이상의 값을 인수(argument)로 사용한다
이 함수는 목록에서 NULL값이 아닌 값을 반환한다 (?)
NULL에 관한 작업을 수행할 때, 각 DBMS에서 제공하는 내장함수를 활용하는 것이 가장 좋다
COALESCE 함수는 모든 DBMS에서 작동하며 CASE 또한 그러하다

select case
		when comm is not null then comm
        else 'is null'
        end as 'is comm null?'
        from emp;
>>
+---------------+
| is comm null? |
+---------------+
| is null       |
| 300           |
| 500           |
| is null       |
| 1400          |
| is null       |
| is null       |
| is null       |
| is null       |
| 0             |
| is null       |
| 0             |
| is null       |
| is null       |
+---------------+

CASE WHEN THEN END도 조건에 따라 COALESCE와 동일하게 작동함을 볼 수 있다

1. 13 패턴 검색하기

Q. 특정 부분 문자열 또는 패턴과 일치하는 행을 반환하려고 한다 / 만약 부서코드 값이 10 또는 20이고 이름에 'I'값을 포함하거나 직급명이 'ER'로 끝나는 사원만 반환하려고 한다면?

A. `LIKE`연산자를 SQL 와일드카드 문자 중 하나인 `%` 연산자와 함께 사용한다

select ename, job
	from emp
    where detpno in (10, 20)
    and (ename like '%I%' or job like '%ER')
>>
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| JONES  | MANAGER   |
| CLARK  | MANAGER   |
| KING   | PRESIDENT |
| MILLER | CLERK     |
+--------+-----------+

% 연산자는 LIKE 패턴 일치 연산에 쓰일 때, 모든 문자 시퀀스와 일치한다
즉, %연산자는 다른 모든 문자를 대신한다는 뜻이다
'i'를 %연산자로 감싸면 'i'의 앞뒤로 존재하는 모든 문자를 %로 대체하여 검색하겠다는 의미가 된다
% 연산자가 공백도 포함하는지를 알아보겠다
첫 글자가 'i'로 시작하는 데이터를 하나 삽입해둘 것이다

insert into sqlcook.emp
values(7777, 'IREN', 'TESTER', 0000, '2022-07-03', 9000, 9000, 10)

select * from emp;
>>
+-------+--------+-----------+------+------------+------+------+--------+
| 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 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+

중간 위치에 '7777, IREN...'으로 들어간 데이터를 볼 수 있다.

LIKE '%i%'가 'i'로 시작하는 자료를 검색할까?
%가 공백을 대체할까?

select ename 
		from emp
        where ename like '%i%'
>>
+--------+
| ename  |
+--------+
| SMITH  |
| MARTIN |
| IREN   |
| KING   |
| MILLER |
+--------+

포함한다는 것을 알 수 있었다
그렇다면 'i'로 시작하는 것만 찾고 싶을 땐 어떻게 할까

select ename as 'name start with "i"'
		from emp
        where ename like 'i%'
>>
+---------------------+
| name start with "i" |
+---------------------+
| IREN                |
+---------------------+

반드시 'i'가 첫 번째라는 의미에서 'i' 앞의 %를 빼고 'i'로 시작해주면 된다
더불어, '' 와 ""의 사용도 다른 언어에서와 동일하게 작동한다는 것도 확인했다

정리

1. 11장: `NULL`에 관한 작업

NULL에 관한 작업은 IS NULL, IS NOT NULL을 사용한다
부등호로 뭔가를 했던 것 같은데 모두가 ORM의 도움이었던 것 같다
django생각이 난다...

1. 12장: `COALESCE`는 `NULL`에 대한 `CASE WHEN THEN END` 작업을 간소화해놓았다

진심으로 처음 보는 함수였다
NULL에 관한 작업이 많은데 이 모든 작업을 CASE WHEN THEN END로 처리하려면
쿼리가 너무 길어지고 속도 저하가 있어서 만들어진 걸까 싶다

1. 13장: `LIKE`와 `%`

자주 사용했던 구문이다
선별 의도를 명확하게 하기 위해 as를 써주는 습관을 들이면 더 좋을 것 같다

Chappie: 코딩 좋아하는 사람

Can do this all day

이전 포스트

SQL Cook: 레코드 검색

다음 포스트

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

SQL_COOK

복습

공통 규칙

`CONCAT`

`CASE WEHN ... THEN ... END (+AS X)`

무작위 n개 레코드 반환하기

1. 11 `NULL`값 찾기

Q. 특정 열에 대해 값이 `NULL`인 모든 행을 찾으려고 한다

A. 값이 NULL인지 여부를 확인하려면 `IS NULL`을 사용한다

+ `IS NOT NULL`을 사용하여 주어진 열에서 `NULL`이 없는 행을 찾을 수도 있다

1. 12 `NULL`값을 실젯값으로 변환하기

Q. `NULL`이 포함된 '행'에서 해당 `NULL` 대신 `NULL이 아닌 값`을 반환하려고 한다

A. `COALESCE` 함수를 사용하여 `NULL`값을 실젯값으로 대체한다

1. 13 패턴 검색하기

Q. 특정 부분 문자열 또는 패턴과 일치하는 행을 반환하려고 한다 / 만약 부서코드 값이 10 또는 20이고 이름에 'I'값을 포함하거나 직급명이 'ER'로 끝나는 사원만 반환하려고 한다면?

A. `LIKE`연산자를 SQL 와일드카드 문자 중 하나인 `%` 연산자와 함께 사용한다

정리

1. 11장: `NULL`에 관한 작업

1. 12장: `COALESCE`는 `NULL`에 대한 `CASE WHEN THEN END` 작업을 간소화해놓았다

1. 13장: `LIKE`와 `%`

SQL Cook: 레코드 검색

SQL Cook: 쿼리 결과 정렬

0개의 댓글

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

SQL_COOK

복습

공통 규칙

CONCAT

CASE WEHN ... THEN ... END (+AS X)

무작위 n개 레코드 반환하기

1. 11 NULL값 찾기

Q. 특정 열에 대해 값이 NULL인 모든 행을 찾으려고 한다

A. 값이 NULL인지 여부를 확인하려면 IS NULL을 사용한다

+ IS NOT NULL을 사용하여 주어진 열에서 NULL이 없는 행을 찾을 수도 있다

1. 12 NULL값을 실젯값으로 변환하기

Q. NULL이 포함된 '행'에서 해당 NULL 대신 NULL이 아닌 값을 반환하려고 한다

A. COALESCE 함수를 사용하여 NULL값을 실젯값으로 대체한다

1. 13 패턴 검색하기

Q. 특정 부분 문자열 또는 패턴과 일치하는 행을 반환하려고 한다 / 만약 부서코드 값이 10 또는 20이고 이름에 'I'값을 포함하거나 직급명이 'ER'로 끝나는 사원만 반환하려고 한다면?

A. LIKE연산자를 SQL 와일드카드 문자 중 하나인 % 연산자와 함께 사용한다

정리

1. 11장: NULL에 관한 작업

1. 12장: COALESCE는 NULL에 대한 CASE WHEN THEN END 작업을 간소화해놓았다

1. 13장: LIKE와 %

SQL Cook: 레코드 검색

SQL Cook: 쿼리 결과 정렬

0개의 댓글

`CONCAT`

`CASE WEHN ... THEN ... END (+AS X)`

1. 11 `NULL`값 찾기

Q. 특정 열에 대해 값이 `NULL`인 모든 행을 찾으려고 한다

A. 값이 NULL인지 여부를 확인하려면 `IS NULL`을 사용한다

+ `IS NOT NULL`을 사용하여 주어진 열에서 `NULL`이 없는 행을 찾을 수도 있다

1. 12 `NULL`값을 실젯값으로 변환하기

Q. `NULL`이 포함된 '행'에서 해당 `NULL` 대신 `NULL이 아닌 값`을 반환하려고 한다

A. `COALESCE` 함수를 사용하여 `NULL`값을 실젯값으로 대체한다

A. `LIKE`연산자를 SQL 와일드카드 문자 중 하나인 `%` 연산자와 함께 사용한다

1. 11장: `NULL`에 관한 작업

1. 12장: `COALESCE`는 `NULL`에 대한 `CASE WHEN THEN END` 작업을 간소화해놓았다

1. 13장: `LIKE`와 `%`