02 DISTINCT, WHERE, BETWEEN, IN, LIKE, SORT, FUNCTION

▶ q 연산자

q'[리터럴문자열]'
q'<리터럴문자열>'
q'{리터럴문자열}'
q'(리터럴문자열)'
q'!리터럴문자열!'

SELECT q'[My name's ]' || last_name || ' ' || first_name as name
FROM hr.employees;

SELECT q'[<My name's >]' || last_name || ' ' || first_name as name
FROM hr.employees;

 

---

 

▶ 중복행 제거 DISTINCT 
- distinct 키워드를 이용해서 중복을 제거할 수 있다.
- distinct 키워드는 SELECT 절 제일 앞에 한번만 사용해야 한다.

- unique 키워드를 이용해서 중복을 제거할 수 있다.

SELECT distinct department_id
FROM hr.employees;

SELECT distinct department_id, job_id
FROM hr.employees;


SELECT unique department_id
FROM hr.employees;

 

---

▶ WHERE 절
- 행을 제한하는 절
- 조건절
- 기준컬럼이 문자열, 날짜열이면 비교값은 작은따옴표로 묶어야 한다.
- 영문자는 대소문자를 구분한다.
- 날짜형식은 지역, 언어에 따라 기본 날짜 표시 형식이 다르다.
    한국 RR/MM/DD
    미국 DD-MON-RR
    
- 비교연산자
    = , > , < , >= , <= , [ != , ^= , <> ]

SELECT *
FROM hr.employees
WHERE last_name = 'King' ;

SELECT *
FROM hr.employees
WHERE last_name = 'KING' ;	-- 대문자, 소문자 검색 주의

SELECT *
FROM hr.employees
WHERE hire_date = '03/06/17' ;

SELECT *
FROM hr.employees
WHERE salary > 1000 ;

SELECT *
FROM hr.employees
WHERE department_id <> 50;

 

- 논리연산자
    AND : 두 조건이 모두 참일 경우 TRUE
    OR  : 두 조건 중에 하나가 참일 경우 TRUE
    NOT : FALSE 일 경우 TRUE, TRUE 일 경우 FALSE

SELECT *
FROM hr.employees
WHERE department_id = 20
OR salary >= 10000;

SELECT *
FROM hr.employees
WHERE department_id = 20
AND salary >= 10000;

 

- BETWEEN 하한값 AND 상한값 [ >= and <= ]
    범위 조건을 사용하여 값의 범위에 따라 행을 추출

[Q] EMPLOYEES 테이블에서 SALARY가 2500~3500 인 사원들의 정보 출력

SELECT *
FROM hr.employees
WHERE salary >= 2500
AND salary <= 3000;

SELECT *
FROM hr.employees
WHERE salary BETWEEN 2500 AND 3000;

[Q] EMPLOYEES 테이블에서 SALARY가 2500~3500 아닌 사원들의 정보 출력

SELECT *
FROM hr.employees
WHERE salary < 2500
OR salary > 3000;

SELECT *
FROM hr.employees
WHERE salary NOT BETWEEN 2500 AND 3000;

 

- 문자 범위도 가능 : 알파벳 순으로 인식

SELECT *
FROM hr.employees
WHERE last_name >= 'Abel'
AND last_name <= 'Austin';  

SELECT *
FROM hr.employees
WHERE last_name BETWEEN 'Abel' AND 'Austin';

SELECT *
FROM hr.employees
WHERE last_name NOT BETWEEN 'Abel' AND 'Austin';

SELECT *
FROM hr.employees
WHERE hire_date BETWEEN '01/01/01' AND '02/12/31';

 

---

▶ IN 연산자
- 각 목록의 값과 일치하는 값을 추출할때 사용
- 기준컬럼 = 비교값 or 기준컬럼 = 비교값
- 기준컬럼 IN (비교값, 비교값, ...)

SELECT *
FROM hr.employees
WHERE department_id = 10
OR department_id = 20
OR department_id = 30 ;

SELECT *
FROM hr.employees
WHERE department_id IN (10,20,30);

SELECT *
FROM hr.employees
WHERE NOT(department_id = 10
OR department_id = 20
OR department_id = 30) ;

SELECT *
FROM hr.employees
WHERE department_id <> 10
AND department_id <> 20
AND department_id <> 30 ;

SELECT *
FROM hr.employees
WHERE department_id NOT IN (10,20,30);

★ 논리연산자 우선순위 ★
    NOT > AND > OR

SELECT *
FROM hr.employees
WHERE department_id = 10
OR department_id = 20
OR department_id = 30
AND salary > 5000;

SELECT *
FROM hr.employees
WHERE department_id = 10
OR department_id = 20       -- 2 OR
OR (department_id = 30      -- 3 OR 
AND salary > 5000);         -- 1 AND : 급여 5000이상 조건은 id 30에만 해당

SELECT *
FROM hr.employees
WHERE (department_id = 10
OR department_id = 20
OR department_id = 30)		-- 1 OR 
AND salary > 5000;          -- 2 AND

SELECT *
FROM hr.employees
WHERE department_id IN (10,20,30)
AND salary > 5000;

 

---

NULL 연산자
- NULL 값을 체크하는 연산자
    IS NULL         NULL값이 있는, 결측치가 있는
    IS NOT NULL     NULL값이 없는, 결측치가 없는

SELECT *
FROM hr.employees
WHERE commission_pct IS NULL;

SELECT *
FROM hr.employees
WHERE commission_pct IS NOT NULL;

 

---

LIKE 연산자
- '문자' 패턴을 찾는 연산자
    % : 0개 이상의 문자를 찾을 때 사용
    _  : 1개 문자를 찾을 때 사용 

SELECT *
FROM hr.employees
WHERE last_name LIKE 'K%';

SELECT *
FROM hr.employees
WHERE last_name LIKE '_i%';

SELECT *
FROM hr.employees
WHERE last_name LIKE '__n%';    -- _ 2개 사용 = 2개 문자

ESCAPE '\'

- LIKE 연산자 사용시에 % 또는 _ 를 순수한 문자로 인식할 수 있는 방법

예)
JOB_ID
-------
HR_REP
HRREP
HR%REP  <--
HR_PROG
HR%PROG <--
HR_%REP

문) JOB_ID 값 중에 HR%로 시작되는 패턴을 추출

SELECT *
FROM hr.employees
WHERE job_id LIKE 'HR%'     --> 다나옴

SELECT *
FROM hr.employees
WHERE job_id LIKE 'HR\%%' ESCAPE '\';    --> \ 뒤에 오는 1글자를 문자로 인식

'\' 대신 임의 기호 사용 가능        
WHERE job_id LIKE 'HR^%' ESCAPE '^';

 

LIKE 주의!!

SELECT *
FROM hr.employees
WHERE hire_date LIKE '02%';

- 작동되지만 hire_date : 날짜형이고  LIKE : 문자패턴 찾기용으로 사용X --->> 악성코드
--> 날짜검색은 BETWEEN ~ AND

- 주의!! LIKE 연산자는 문자 패턴을 찾는 연산자이기떄문에 기준컬럼이 문자컬럼이 아닌

다른 유형의 타입의 컬럼을 사용하면 내부적으로 문자타입의 컬럼으로 형변환을 해야한다.

- 주의!! 인덱스가 걸려 있는 커럼이 형변환이 발생하면 인덱스 스캔이 아닌 TABLE FULL SCAN이 발생한다.

 

 

[Q] employees 테이블에 있는 데이터 중에 job_id 가 SA 로 시작되고
salary 값은 10000 이상 받는 사원들의 정보를 출력

SELECT *
FROM hr.employees
WHERE job_id LIKE 'SA%'
AND salary >= 10000;

[Q] last_name의 세번째 문자가 'a' 또는 'e' 글자가 포함된 사원들의 정보를 출력

SELECT *
FROM hr.employees
WHERE last_name LIKE '__a%'
OR last_name LIKE '__e%';

[Q] employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고
salary값은 10000이상 받고 2005년도에 입사한 사원들의 정보를 출력

SELECT *
FROM hr.employees
WHERE job_id LIKE 'SA%'
AND salary >= 10000
AND hire_date BETWEEN '2005/01/01' AND '05/12/31';
                    -- 설정된 지역 날짜 형식에 맞게 입력 또는 형식지정 필요
                    -- 한국 표준 년/월/일

 

---

▶ 정렬 SORT
- ORDER BY절을 이용해서 정렬한다.
- ORDER BY절은 SELECT문의 가장 마지막절에 기술해야 한다.
- asc (ascending)   오름차순 정렬, 기본값
- desc (descending) 내림차순 정렬

SELECT employee_id, salary, job_id, department_id
FROM hr.employees
ORDER BY department_id asc;

SELECT employee_id, salary, job_id, department_id
FROM hr.employees
ORDER BY department_id desc;

SELECT employee_id, salary * 12
FROM hr.employees
ORDER BY salary * 12 desc;  		
--> 표현식 그대로 작성

SELECT employee_id, salary * 12 ann_sal
FROM hr.employees
ORDER BY ann_sal desc;      		
--> 별칭 사용 가능

SELECT employee_id, salary * 12 "ann_sal"
FROM hr.employees
ORDER BY "ann_sal" desc;    		
--> 별칭에 큰따옴표 사용 시 똑같이 사용

SELECT employee_id, salary * 12 "ann_sal"
FROM hr.employees
ORDER BY 2 desc;            		
--> 2번째 칼럼으로 정렬 : 위치 표기법 사용

SELECT employee_id, department_id, salary * 12 "ann_sal"
FROM hr.employees
ORDER BY 2 asc, 3 desc;     		
--> 다중정렬

오라클에서는 정렬 시 null 값을 큰 값으로 인식

---

▶ 함수(function)
- 기능의 프로그램
- 단일행 함수 : 행당 조작하는 함수 (1개 행마다)
            입력값으로 한 행의 값이 들어가 출력값은 하나가 리턴한다.
- 단일행 함수는 여러 번 중첩할 수 있다.

 

▷ 문자함수
- upper : 대문자로 변환
- lower : 소문자로 변환
- initcap : 첫 글자만 대문자, 나머지는 소문자로 변환
- 데이터 입력 시점에 사용

SELECT last_name, upper(last_name), lower(last_name), initcap(last_name)
from hr.employees;

SELECT *
from hr.employees
WHERE last_name = 'KING';

주의!!

SELECT *
from hr.employees
WHERE upper(last_name) = 'KING';
--> 악성코드! Table Full Scan
--> 인덱스가 걸려있는 기준컬럼에 형식변환 코드 사용 시 Full Scan 걸린다.

- concat( , ) : 연결연산자와 동일한 함수, 2개만 연결 가능

SELECT last_name || first_name, concat(last_name, first_name)
FROM hr.employees;

SELECT last_name || first_name || job_id, 
        concat(concat(last_name, first_name), job_id)
FROM hr.employees;

SELECT last_name || first_name || job_id, 
    concat(concat(concat(concat(last_name, ' '), first_name), ' / '), job_id)
FROM hr.employees;

 

- length : 문자의 길이를 리턴하는 함수
- lengthb : 문자의 byte값을 리턴하는 함수
            영어,숫자   1byte
            한글           3byte

SELECT last_name, length(last_name), lengthb(last_name)
FROM hr.employees;

SELECT length('bigdata'), lengthb('bigdata'), length('빅데이터'), lengthb('빅데이터')
FROM dual;

 

- instr : 문자의 위치를 리턴하는 함수
instr(컬럼, 찾는문자열, 시작위치, 몇번째로 찾는 위치)

SELECT last_name, instr(last_name, 'a'), instr(last_name, 'a', 1, 2)
FROM hr.employees;

SELECT last_name, instr(last_name, 'a'), instr(last_name, 'a', 1, 2)
FROM hr.employees
WHERE instr(last_name, 'a', 1, 2) > 0;		-- 1번째 자리에서부터 a가 2번 위치한 자리의 값

 

- substr : 문자를 추출하는 함수
substr(컬럼(문자열), 시작점, 추출개수)

SELECT last_name, substr(last_name, 1,1), substr(last_name, 2,3)
FROM hr.employees;

SELECT last_name, 
        substr(last_name, 2, length(last_name)),
        substr(last_name, 2)
FROM hr.employees;

시작점
 1, 2, 3, 4, 5 : 왼쪽 앞에서부터
-5,-4,-3,-2,-1 : 오른쪽 뒤에서부터

SELECT last_name, 
        substr(last_name, length(last_name)),   -- 마지막글자 개수를 모를 경우
        substr(last_name, -1, 1),               -- 맨 뒤 1개
        substr(last_name, -2, 2)                -- 뒤에서 2번째부터 2개
FROM hr.employees;

 

- trim : 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
    trim('제거할문자' from '문자열')
    
- ltrim : 왼쪽(접두) 부분에 연속되는 문자를 제거하는 함수
    ltrim('문자열', '제거할문자')
        
- rtrim : 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
    rtrim('문자열', '제거할문자')

SELECT trim('a' from 'aaababcaa'),
        ltrim('aaababcaa', 'a'),
        rtrim('aaababcaa', 'a')
FROM dual;

 

- 공백문자 제거시에는 문자열만 입력 가능
- 입력창 만들 때 많이 사용

SELECT '  KING  ',
        trim(' ' from '  KING  '),
        trim('  KING  '),
        ltrim('  KING  '),
        rtrim('  KING  ')
FROM dual;

 

- replace : 문자를 다른 문자로 치환하는 함수
    replace(컬럼(문자열), 이전 문자, 새로운 문자)

SELECT replace('100-100', '-', '%'),
       replace('100-100', '-', ''),         -- 문자제거
       replace('  k i n g   ', ' ', '')
FROM dual;

 

- lpad : 문자의 자리를 고정시킨 후 문자값을 오른쪽 기준으로 채우고
        빈 왼쪽 공백을 다른 값으로 채우는 함수
    lpad(컬럼, 자리수, 공백채울문자)

- rpad : 문자의 자리를 고정시킨 후 문자값을 왼쪽 기준으로 채우고
        빈 오른쪽 공백을 다른 값으로 채우는 함수

SELECT salary, 
        '****' || salary,
        lpad(salary, 10, '*'),
        rpad(salary, 10, '*')
FROM hr.employees;

[Q] salary값을 1000당 * 출력

SELECT salary, lpad('*', salary / 1000, '*') STAR,
FROM hr.employees;

SELECT 
        lpad('', 5, '*'),       -- 문자열 공란 불가 = 입력대상이 없다 = null
        lpad(' ', 5+1, '*')     -- 보이진 않지만 공백이 들어간거임
FROM dual;

---

50개의 행 = 미리보기 // 스크롤을 끝까지 내려보자.