[Query] Day 2 - 명령어 (날짜함수 포함)

1. 💻 작업

입력

타입 & 전체 정보 출력

desc personnel;
select * from tab;

1. || : concat

• 별칭을 써서 컬럼값 붙이기 (' '문자처리) - 열제한

select pname ||' '||pno from personnel;
select ' 사원번호' || pno ||'의 이름은'||pname '입니다' from personnel;

• where : 조건문 (+ 컬럼명 = ' ')

select * from personnel where job = 'SALESMAN'; --비교는 반드시 대문자

2. 연산자

• 연산자 =,>,>=,<,<=, <>(같지않음)

--사원의 급여가 2000이하인 사원
SELECT * FROM PERSONNEL
WHERE PAY <= 2000;

• where startdate = 'YY-MM-DD'

--90년 12월17일에 입사한 사원
SELECT * FROM PERSONNEL
WHERE STARTDATE = '90-12-17'; --문자처리할때만 ' '

SELECT * FROM PERSONNEL WHERE STARTDATE = '1990/12/17'; --이것도 가능

• 특정 이름 출력하기

--이름이 JAMES인 사원
SELECT * FROM PERSONNEL WHERE PNAME = 'JAMES';

3. where ~ in ~

• AND / OR / NOT + (컬럼명) - 그리고 / 아니면 / 아닌

• where ~ in ~ : 문자열 연산자

--부서번호(DNO)가 10번이고, 급여(PAY)가 3000이상인 사원
SELECT * FROM PERSONNEL WHERE DNO = 10 AND PAY >= 3000;

--직업이 SALESMAN이고 90년 이후에 입사한 사원 검색
SELECT * FROM PERSONNEL WHERE JOB = 'SALESMAN' AND STARTDATE > '90-12-31'; --'91-01-01'도 가능

--91년 9월에 입사한 사원
SELECT * FROM PERSONNEL WHERE STARTDATE >= '91-09-01' AND STARTDATE < '91-10-01' ;

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

--부서번호가 30번이거나 급여가 3000이상인 사원
SELECT * FROM PERSONNEL WHERE DNO = 30 OR PAY>=3000;

--직업이 SALESMAN이거나 1990년 이후에 입사한 사원
SELECT * FROM PERSONNEL WHERE JOB = 'SALESMAN' OR STARTDATE > '90-12-31';

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

--업무가 SALESMAN,CLERK인 사원 
SELECT * FROM PERSONNEL WHERE JOB = 'SALESMAN' OR JOB = 'CLERK';

SELECT * FROM PERSONNEL WHERE JOB IN ('SALESMAN','CLERK'); --문자열 연산자 IN

--업무가 SALESMAN,CLERK이 아닌 사원
SELECT * FROM PERSONNEL WHERE JOB <> 'SALESMAN' AND JOB <> 'CLERK'; --둘다 부정이면 긍정 AND
SELECT * FROM PERSONNEL WHERE JOB NOT IN ('SALESMAN','CLERK'); 
SELECT * FROM PERSONNEL WHERE NOT JOB IN ('SALESMAN','CLERK'); 

4. Between A and B

• BETWEEN a AND b

-- 급여가 1000에서 2000사이의 사원
SELECT * FROM PERSONNEL WHERE PAY >= 1000 AND PAY <= 2000;
SELECT * FROM PERSONNEL WHERE PAY BETWEEN 1000 AND 2000;

-- 91년 9월에 입사한 사원
SELECT * FROM PERSONNEL WHERE STARTDATE BETWEEN '91-09-01' AND '91-09-30';

--부서번호가 20과 30사이인 사원
SELECT * FROM PERSONNEL WHERE DNO BETWEEN 20 AND 30; --중간 사이값도 EX, 21,22...나옴
SELECT * FROM PERSONNEL WHERE DNO IN (20,30); 

5. % (와일드카드)

• 특정 단어 & 이름 & 번호가 있는 조건

• % : 와일드카드 : 어디에든 매치 가능
  - ex) 90% : 90으로 시작 + 뒤에 ~~~~
  - ex) %90 : 앞에 ~~~~ + 90으로 끝나야 함

--이름이 A로 시작되는 사원
SELECT * FROM PERSONNEL WHERE PNAME LIKE 'A%'; -- WHERE PNAME = A*인 문자 = A로 시작하는 모든 이름 가져와

--사원번호 111_인 사원
SELECT * FROM PERSONNEL WHERE PNO LIKE '111_'; --언더바 자리수 
SELECT * FROM PERSONNEL WHERE PNO LIKE '1__1'; 

--90년도에 입사한 사원
SELECT * FROM PERSONNEL WHERE STARTDATE LIKE '%02%'; --연도 필요 없고, 2월달에 입사한 사원

• table 정보 안에서 특정 조건 지정하기

SELECT * FROM PERSONNEL WHERE REGEXP_LIKE(PNO, '11[^2]'); --3번째가 2가 아닌 데이터

SELECT * FROM PERSONNEL WHERE REGEXP_LIKE(PNO, '1[1,2]'); --두세번째에 1또는 2가 나오도록

SELECT REGEXP_SUBSTR('SUZI@NAVER.COM', '[^@]+') FROM DIVISION; --뒤에 몇자가 되어도 상관없음 , 골뱅이가 아닌 데이터 -- TABLE에 있는 데이터 개수 만큼 실행됨
SELECT REGEXP_SUBSTR('SUZI@NAVER.COM', '[^@]+') FROM PERSONNEL;
SELECT REGEXP_SUBSTR('SUZI@NAVER.COM', '[^@]+') FROM DUAL; -- 결과 값 하나니까 DUAL쓰기 (오라클에만 존재)

SELECT * FROM DUAL; --데이터 하나 '

SELECT (100+20) FROM DUAL; --존재하지않는 데이터를 SELECT했을 때 DUAL의 가상테이블을 만듦

6. NULL

• NULL

SELECT MANAGER FROM PERSONNEL;

SELECT * FROM PERSONNEL WHERE MANAGER = NULL; --값 X

--DESC PERSONNEL;
--MANAGER =  숫자임

SELECT * FROM PERSONNEL WHERE MANAGER IS NULL;
SELECT * FROM PERSONNEL WHERE MANAGER IS NOT NULL; --NULL이 아닌 데이터 

6. 숫자함수 (우선순위/반올림/절삭/올림/버림/나머지/절대값/대.소문자/개수/substr/index번호)

• 우선순위 (AND / OR)

--업무가 MANAGER이고 급여가 1500이상인 사원 또는 업무가 SALESMAN인 사원을 찾으시오
SELECT * FROM PERSONNEL WHERE (JOB = 'MANAGER' AND PAY > = 1500) OR  = 'SALESMAN'; --AND가 먼저 연산됨
SELECT * FROM PERSONNEL WHERE (JOB = 'MANAGER' OR JOB = 'SALESMAN') AND PAY >= 1500;

SELECT * FROM PERSONNEL WHERE JOB IN ('PRESIDENT', 'SALESMAN') AND PAY >= 1500;

• (ROUND : 반올림)

SELECT ROUND(45.275,1) FROM DUAL; -- 2까지 표시하는데 7이 반올림돼서  45.3
SELECT ROUND(45.275,-1) FROM DUAL; --5까지 표시하는데 5에서 반올림돼서 50

--  1  2  3  4  .  5  6  7 
-- -4 -3 -2 -1  0  1  2  3

SELECT ROUND(45.275,0) FROM DUAL; -- .2니까 반올림못해서 
SELECT ROUND(45.675) FROM DUAL;  --,0을 생략하면 뒤에서 반올림

SELECT PNO,PNAME,PAY,ROUND(PAY,-2) FROM PERSONNEL;

• (TRUNC : 절삭)

SELECT TRUNC (45.245,2) FROM DUAL;
SELECT TRUNC (45.245,-1) FROM DUAL;

SELECT PNO,PNAME,PAY,TRUNC(PAY,-2) FROM PERSONNEL;

• (CELI/FLOOR : 무조건 올림/내림) - 자리값 X , 무조건 정수까지 밖에 표현 못함

SELECT CEIL(461.11) FROM DUAL; 

SELECT FLOOR(461.99) FROM DUAL; 

• (MOD : 나머지)

SELECT MOD(10,3) FROM DUAL;

• (ABS : 절대값 - 무조건 양수값)

SELECT ABS(-123) FROM DUAL;

• (SIGN : 양수:1, 음수:-1, 0:0 반환)

SELECT SIGN(100/30), SIGN(-100/30), SIGN (100-100) FROM DUAL;

• (POWER : 지수 2^4)

 SELECT POWER(2,4) FROM DUAL;

• (SQRT : 제곱근 루트√)

SELECT SQRT(9) FROM DUAL ; 

• (UPPER / LOWER : 대문자/소문자)

SELECT UPPER('ORACLE') FROM DUAL; 

SELECT LOWER('ORACLE') FROM DUAL;

SELECT * FROM DIVISION
WHERE DNAME = UPPER('SALES'); -- 튜닝 (하드웨어 / SQL)

• (initcap : 첫번째 글자 대문자로 바꿔라)

select initcap ('korea fighting') from dual;

• (concat : 컬럼 두개의값만 붙여서 써줌)

select pname || dno from personnnel; --여러개 컬럼 가능 
select concat(pname,dno) from personnel;

select 'KOREA' || 'FIGHTING' from dual;
select concat('KOREA','FIGHTING') from dual;

• (length : 개수)

select length('KOREA') from dual; 
select length('korea fighting') form dual; --공백의 개수도 세어줌

• (substr : 인덱스 x부터 y 수만큼 출력)

• (instr : 인덱스의 번호를 읽어옴)

select substr('ABCDEFG',1,3) from dual;--ABC
select substr('ABCDEFG',3,2) from dual; --CD
select substr('ABCDEFG',-3,2) from dual; --마이너스(-)는 뒤에서 세기 EF
select substr('ABCDEFG',-4) from dual; --뒤에서 4자리부터 끝까지 출력 DEFG

select instr('ABCDEFG','C') from dual; --C 
select instr('AAAAAAA','A') from dual; --1 (처음 찾는 A)
select instr('ABCDEFG','a') from dual; --0 (사용하지 않는 숫자이므로)

7. rapd / rtin / ltrim

• (rpad : 비어있는 오른쪽 채워라)

• (rtrim : 오른쪽 해당 글자 다 지우기 )

• (ltrim : 해당 글자 지우다가 다른 글자 만나면 멈추기)

select rpad(pname,15,'*') from personnel; --15자리 중 오른쪽 나머지 부분 *채우기
select rpad (substr ('940523-2754624',1,8),14,'*') jumin from dual;

select rtrim('ABBBBBB','B')from dual; --오른쪽에 있는  B다지우기
select rtrim('A     ',' ')from dual; 

select ltrim('BBBABBB','B')from dual; -- B를 지우다가 다른 알파벳(A)를 만나면 멈춤 

8. 날짜함수 (sysdate)

• (날짜함수: sysdate)

select sysdate from dual; --현재 시스템 시간 불러옴

select sysdate + 10 from dual; -- 10일 후 

select startdate,startdate-1,startdate+1 from PERSONNEL;
--사원들이 오늘까지 근무한 년수를 구하시오

select sysdate, startdate, round((sysdate - startdate)/365) || '년차' years from PERSONNEL;

select startdate,round(startdate,'YEAR') from personnel; --년을 기준으로 반올림 함 
select startdate,round(startdate,'MONTH') from personnel; --15일 기준으로 반올림 함


select startdate,trunc(startdate,'MONTH') from personnel; -- 15일 기준으로 버림


--months_between : 두 날짜사이의 개월수 
select round(months_between(sysdate, '2002-06-01'))/12 from dual;

--last_day : 입사한달의 말일
select startdate,last_day(startdate) from personnel;

--next_day : 다음번에 오는 요일
select next_day(sysdate,'월요일') from dual;

--add_months : 24개월 뒤 
select add_months(sysdate,24) from dual;

--extract : 년월일 추출
SELECT STARTDATE, 
EXTRACT(YEAR FROM STARTDATE) YEAR,
EXTRACT(MONTH FROM STARTDATE) MONTH,
EXTRACT(DAY FROM STARTDATE) DAY
FROM PERSONNEL;


--변환함수
select sysdate,to_char(sysdate,'D') from dual; --주의 수 
select sysdate,to_char(sysdate,'DY') from dual;
select sysdate,to_char(sysdate,'RM') from dual;
select sysdate,to_char(sysdate,'YYYY') from dual;
select sysdate,to_char(sysdate,'DD') from dual;
select sysdate,to_char(sysdate,'MM') from dual;
select sysdate,to_char(sysdate,'MON') from dual;--월
select sysdate,to_char(sysdate,'YYYY-MM-DD') from dual;
select sysdate,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; -- 15시 등 24시간 단위로 표시
select sysdate,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS:SSSS') from dual; 

SELECT TO_CHAR(12506,'99,999')FROM DUAL; -- 콤마 찍힘

--서수로 보여짐
select sysdate,to_char(sysdate,'DD') from dual;
select sysdate,to_char(sysdate,'DDSP') from dual; --영어
select sysdate,to_char(sysdate,'DDTH') from dual;--th
select sysdate,to_char(sysdate,'DDSPTH') from dual;--서수th


--to_date강제 날짜 바꾸기
select to_date('12-6월-23','YY-MONTH-DD') +1 from dual; --(문자 ,날짜로 바꾸고) +날짜는 연산이 가능하니까 +1
select to_date('11:05','HH:MI') from dual;

--to_number : 숫자로 형변환
select to_number(123) + 100 from dual; 0
select to_number('abc')+100 from dual; -- 문자 -> 숫자 xxxxx

9. NVL(함수,0)

• ( NVL : 있으면 적고 / 없으면 0)

select bonus,nvl(bonus,0) from personnel; --없으면 0 있으면 bonus
select manager, nvl(manager,'none manger') from personnel; --x manager는 숫자니까 
select manager, nvl(to_char(manager),'none manger') from personnel; --문자로 변환

10. decode 파생컬럼

• ( decode / 파생컬럼)

  파생컬럼 : pay*1.2 등 select한 순간에만 보여짐

• 특정 조건이 만족되면 해당 값을 반환하는 함수 - 다양한 값을 비교

-각 사원의 급여를 부서번호가 10인경우 10% 보너스, 부서번호가 20인경우 20%보너스,
--나머지는 30%를 더해서 출력하시오

select pname,bonus,dno,pay,decode(dno,10,pay*1.1,20,pay*1.2,pay*1.3) "인상분" from personnel;


--급여가 3500이상인 경우 'GOOD'을, 미만인 경우에는 'POOR'로 출력
--decode + sign을 이용하시오

select pname,pay,decode(sign(pay-3500),1,'GOOD','POOR')GRADE from PERSONNEL; --sign(pay-3500) = 위 예제 dno