12.20 오늘의 공부

오늘 배운 다양한 SQL 함수(문자, 숫자, 날짜)

★instr : 문자의 위치를 리턴하는 함수, instr(칼럼, 찾는 문자열, 시작점, 몇 번째로  찾아야 할지)
★substr : 문자를 개수만큼 추출하는 함수, substr(칼럼, 시작점, 추출 개수)
★substrb : 문자를 바이트 수만큼 추출하는 함수 (문자, 어디서부터 추출할지 위치, 바이트)
★trim : 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
★replace : 문자를 다른 문자로 치환하는 함수, replace(문자열[칼럼], old문자[이전 문자], new문자[바꿀 문자])
★round : 지정된 소수점 자릿수 값을 반올림하는 함수
★trunc : 지정된 소수점 자릿수 값을 truncate(버리는) 함수
★ceil : 숫자 값을 가장 큰 정수로 반환하는 함수(올림?)
★floor : 숫자값을 가장 작은 정수로 반환하는 함수(내림?)

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

★mod : 나머지 값 리턴(어떤 값을 나눈 나머지를 반환하는 함수)

★power : 거듭제곱

★to_char : date타입을 char형으로 변환하는 함수

★ months_between : 두 날짜 간의 달수를 리턴하는 함수

★add_months : 달수를 더하거나 빼는 함수

★next_day : 입력한 날짜를 기준으로 찾고자 하는 요일의 첫 번째 날짜를 반환하는 함수(현재 접속한 지역의 언어로 해야 함)

★last_day : 기준 날짜의 달의 마지막 날짜를 리턴하는 함수

 

SELECT * FROM tab; --테이블들 보기

desc employees --employees 테이블의 정보

SELECT upper(last_name), lower(last_name), initcap(last_name)
FROM employees
WHERE lower(last_name) = 'king'

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

 

SELECT * FROM nls_database_parameters; --파라미터 확인

NLS_CHARACTERSET AL32UTF8 : 한글이 3byte
                    KO16MSWIN949 : 한글이 2byte, 한국어, 일본어, 영어(일부 언어)

 

★instr :문자의 위치를 리턴하는 함수, instr(칼럼, 찾는 문자열, 시작점, n번째 위치)

SELECT last_name, instr(last_name, 'a'), instr(last_name, 'a',1,2)

FROM employees;

-- last_name칼럼의 데이터들의 소문자 'a'가 처음으로 나오는 위치를 검색

--첫 번째 자리에서 시작해서 'a'가 두 번째로 나오는 위치 검색

 

[문제 13] 문자열 중에 두 번 이상 a가 나오는 last_name?

SELECT last_name
FROM employees
WHERE instr(last_name,'a',1,2)>0;

SELECT last_name
FROM employees
WHERE last_name like '%a%a%';

※시작점
-일반적인 순서 12345
-반대 순서 -5-4-3-2-1

★substr : 문자를 개수만큼 추출하는 함수, substr(칼럼, 시작점, 추출 개수)
SELECT last_name, substr(last_name,1,2),
    substr(last_name,2,2),
    substr(last_name,-2,2),
    substr(last_name,-1,1)
FROM employees;

 

★substrb : 문자를 바이트 수만큼 추출하는 함수(문자, 추출할 시작점, 바이트)

 

SELECT substrb('abcde',1,2), substrb('가나다라마바사',1,6)
FROM dual;

 

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

 

SELECT trim('a' FROM 'aabbacaa'),
    length('  aaaa  bb cc  '),
    length(trim(' ' from ' aaa ')),
    length(trim(' aaa bb cc ')),
    ltrim('aabacaa', 'a'),
    rtrim('aabacaa', 'a')
FROM dual;

 

[문제 14] employees 테이블에 last_name 칼럼의 값 중에  "J" 또는 "A" 또는 "M"으로 시작하는 사원들의 last_name(첫 번째 문자는
대문자, 나머지는 모두 소문자)과 last_name의 길이를 표시하는 query(select문)를 작성합니다.
사원들의 last_name을 기준으로 결과를 오름차순 정렬해 주세요.

SELECT initcap(last_name), length(last_name)
FROM employees
WHERE instr(last_name,'J',1,1) = 1 
    OR instr(last_name, 'A',1,1) =1
    OR instr(last_name, 'M',1,1) =1 --기준값이 다르기때문에 IN함수사용불가능
ORDER BY initcap(last_name);

--------------- 강사님 답---------------------
SELECT initcap(last_name),length(last_name)
FROM employees
WHERE last_name like 'J%'
    OR last_name like 'A%'
    OR last_name like 'M%'
ORDER BY 1;

SELECT initcap(last_name),length(last_name)
FROM employees
WHERE substr(last_name,1,1) ='J'
    OR substr(last_name,1,1) ='A'
    OR substr(last_name,1,1) ='M' 
ORDER BY 1;

SELECT initcap(last_name),length(last_name)
FROM employees
WHERE substr(last_name,1,1) in('J','A','M') --IN은 기준값이 같아서 사용 가능
ORDER BY 1;

------------------------------------------------

 

[문제15] employees테이블에서 department_id(부서 코드)가 50번 사원들 중에 last_name에 두 번째 위치에 "a"글자가 있는 사원들을 조회하세요.

SELECT *
FROM employees
WHERE department_id = 50
    AND instr(last_name,'a',2,1) = 2;

--강사님 답
SELECT *
FROM employees
WHERE department_id = 50
    AND last_name like '_a%';

SELECT *
FROM employees
WHERE department_id = 50
    AND substr(last_name,2,1) = 'a';  
    --예)substr(last_name,1,1) = 'a' ->> 만약 last_name 'aaufling'가 있으면은 'aaufling'는 누락된다
    -- and (instr(last_name,'a',1,1) =2 or instr(last_name, 'a', 2,1) =2); 
    --결국 instr(last_name,'a',2,1)=2;로 하면 다 찾을 수 있다.
    

 

★ replace : 문자를 다른 문자로 치환하는 함수, replace(문자열[칼럼], old문자[이전 문자], new문자[바꿀 문자])
SELECT replace('100-001','-','%'),
    replace('100-001','-'), -- '-'를 없애겠다(공백 문자가 아님)
    replace('100-001','-','') --''는 기본값(중간에 스페이스바x), 바꾸고 싶으면 '바꿀문자' 넣으면 됨(new문자를 입력하지않으면 ''문자열이 기본값으로 입력된다)
FROM dual;

SELECT trim(' 100      001  '), replace('  100    001  ',' ')
from dual;

 

SELECT last_name, replace(last_name, 'A')
FROM employees;    

 

SELECT last_name,
    substr(last_name,1,1),
    substr(last_name,2,1),
    substr(last_name,3,1),
    substr(last_name,-1,1)
FROM employees
WHERE substr(last_name, -1,1) = 'g';

 

SELECT last_name,
    instr(last_name, 'a',1,1),
    instr(last_name, 'a',1,2), --첫 번째 위치에서부터 2번째 a가 나오는 위치
    instr(last_name, 'a',2,1)  --두 번째 위치에서부터 첫 번째 a가 나오는 위치
FROM employees;

 

SELECT instr('aaufling','a',1,1), instr('aaufling','a',1,2)
FROM dual;

 

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

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

 

SELECT last_name, lpad(last_name,'20','*')
FROM employees;

 

[문제16] salary에 있는 값을 1000당 * 출력

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

★SELECT문
-데이터베이스에 있는 데이터를 검색하는 문
SELECT 컬럼, 컬럼
FROM 테이블
WHERE 행을 제한하는 부분 표현
ORDER BY 컬럼기준으로 정렬

like 문자 패턴을 찾는 연산자

문자 함수
lower, upper, initcap, length, lengthb, substr, substrb, instr, replace, trim, ltrim, rtrim, lpad, rpad, concat

숫자 함수

★round : 지정된 소수점 자릿수 값을 반올림하는 함수
select round(45.926,2), round(45.926,1), round(45.926,0), round(45.926,-1), round(55.926,-2), round(45.926,-2)
from dual;

★trunc : 지정된 소수점 자릿수 값을 truncate(버리는) 함수
select trunc(45.926,2), trunc(45.926,1), trunc(45.926,0), trunc(45.926,-1), trunc(55.926,-2), trunc(45.926,-2)
from dual;

★ceil : 숫자 값을 가장 큰 정수로 반환하는 함수
SELECT round(10.1), ceil(10.1), ceil(10.00000000000001)
FROM dual;

★floor : 숫자 값을 가장 작은 정수로 반환하는 함수
SELECT trunc(10.1), floor(10.1), floor(10.00000001)
FROM dual;

★mod : 나머지 값 리턴(어떤 값을 나눈 나머지를 반환하는 함수)
SELECT 12/5, mod(12,5)
from dual;

 

[문제 17] employees 테이블에 있는 employee_id, last_name, salary, 
salary를 10% 인상된 급여를 계산하면서 계산된 급여는 소수점은 반올림해서 정수 값으로 표현하고 
열 별칭은 New Salary로 표시하세요.

SELECT employee_id, last_name, salary, round(salary + salary/10,0) "New Salary"
FROM employees;

---------------------강사님 답------------------------

SELECT employee_id, last_name, salary, round(salary * 1.1) "New Salary",
        salary - round(salary * 1.1) "diff New Salary" --차이
FROM employees;

 

★power : 거듭제곱
SELECT 2*2*2, power(2,10)
FROM dual;

★날짜 함수
sysdate : 현재 서버 날짜를 리턴하는 함수
systimestamp : 현재 서버 날짜 시간 타임존 리턴하는 함수
current_date : 현재 클라이언트의 날짜를 리턴하는 함수 
current_timestamp : 현재 클라이언트의 날짜, 시간, 타임존
localtimestamp : 현재 클라이언트의 날짜, 시간을 리턴하는 함수

 

SELECT sysdate, systimestamp, current_date, current_timestamp, localtimestamp
from dual;

※날짜 계산
날짜 + 숫자(일수) = 날짜
날짜 - 숫자(일수) = 날짜
날짜 - 날짜 = 일수
날짜 + 날짜 = 오류!!!!
날짜 + 시간 = 날짜
날짜 - 시간 = 날짜

 

SELECT sysdate + 100, sysdate - 100
FROM dual;

desc employees

SELECT employee_id, sysdate - hire_date
FROM employees;

SELECT sysdate + 12/24 
FROM dual;

 

SELECT systimestamp + 12/24, localtimestamp + 12/24, current_timestamp + 12/24
FROM dual;

 

★to_char : date타입을 char형으로 변환하는 함수
SELECT sysdate + 12/24,
        to_char(sysdate + 12/24, 'yyyy-mm-dd hh24:mi:ss'), --12시간 +
        to_char(sysdate + 800/24, 'yyyy-mm-dd hh24:mi:ss'), --800시간 +
        to_char(sysdate + 800/(24*60), 'yyyy-mm-dd hh24:mi:ss'), --60분 +
        to_char(sysdate + 800/1440, 'yyyy-mm-dd hh24:mi:ss'), --60분 +
        to_char(sysdate + 800/(24*60*60), 'yyyy-mm-dd hh24:mi:ss'), --60초 +
        to_char(sysdate + 800/86400, 'yyyy-mm-dd hh24:mi:ss') --60초 +
FROM dual;

 

SELECT * FROM nls_session_parameters;

 

★ months_between : 두 날짜 간의 달수를 리턴하는 함수
SELECT employee_id, 
        sysdate - hire_date, --근무일수
        months_between(sysdate, hire_date)--근무개월 수
FROM employees;

 

[문제 18] 15년 이상 근무한 사원들의 사원번호(employee_id), 입사날짜(hire_date), 근무개월 수를 조회하세요.

SELECT employee_id, hire_date, months_between(sysdate, hire_date)
FROM employees
where months_between(sysdate, hire_date) >=180;

SELECT employee_id, hire_date, months_between(sysdate, hire_date)
FROM employees
where months_between(sysdate, hire_date)/12 >=15;

 

 

★add_months : 달수를 더하거나 빼는 함수
SELECT sysdate, add_months(sysdate,5), add_months(sysdate,-5)
from dual;

★next_day : 입력한 날짜를 기준으로 찾고자 하는 요일의 첫 번째 날짜를 반환하는 함수(현재 접속한 지역의 언어로 해야 함)
SELECT next_day(sysdate, '금요일') --미래 시점에 가장 가까운 요일(오늘 x)
FROM dual;

[문제 19] 사원의 last_name, hire_date 및 근무 6 개월 후 월요일에 해당하는 날짜를 조회하세요. 열 별칭은 REVIEW로 지정합니다

SELECT last_name, hire_date, next_day(add_months(hire_date,6), '월요일') "REVIEW" --요일이 월요일이 나오면 다음 주 월요일로 나오는 것은 고민해보기(나중에 배운다고 함)
FROM employees;

★last_day : 기준 날짜의 달의 마지막 날짜를 리턴하는 함수
SELECT sysdate, last_day(sysdate), last_day(add_months(sysdate,2))
FROM dual;