Query(질의), select,order by,숫자 함수, 문자 함수, 내장 함수,Conditional Expression(조건 표현식)

--Query (질의) : select

select [option...] [db명.소유자명.테이블명.]칼럼명 [as 별명]...

[into 테이블명 ] from 테이블명 where 조건 ... order by 기준키 [asc,desc]
select * from sawon;

select sawon_no, sawon_name, sawon_pay from sawon; --일부의 칼럼만 읽기
select sawon_name, sawon_pay, sawon_no from sawon;  --원본 테이블의 칼럼 순서와 관계없고,

                                                                                클라이언트에서 보낸 sql명령의 순서대로

                                                                                 저장된다.
select sawon_no as 사번, sawon_name 직원명 from sawon;  -- 별명을 줄 때는 as를 써도되고 빼도 된다.
select sawon_no as "사 번", sawon_name "직 원 명" from sawon; --공백을 " "로 묶으면 줄수있긴하지만 잘안준다.
select 10 as aa, 'hello' as bb, 12/3 as cc from dual;  --dual : 가상의 테이블
select sawon_name || '님' as 직원명 from sawon;  --SQL문 내에서의 문자열 더하기:  ||
select sawon_name || sawon_pay as name_pay from sawon;
select sawon_name, sawon_pay as 연봉, sawon_pay * 0.02 as 세금 from sawon;  (x) 주의 --연산에 의해서 구할 수 있는 값은 칼럼으로 만들지 않는다.

--*참고*
select * from &table_name; --그냥 보고 넘길것 참고내용.   치환변수 : 프로그래머에게는 벼로....필요없음

 

--정렬 : order by 칼럼명[asc | desc]
SELECT * FROM sawon order by sawon_no desc;  --sawon_no을기점으로 거꾸로 나옴.
SELECT * FROM sawon order by sawon_no asc;  --오름차순
select * from sawon order by sawon_name;   --asc생략 가능
select sawon_no, sawon_name, sawon_gen, sawon_jik, sawon_pay
from sawon order by sawon_gen asc, sawon_jik asc, sawon_pay desc;
select sawon_name, sawon_jik, sawon_gen from sawon order by 1 asc, 3 asc; --order by 기준키로 position number 가능

select distinct sawon_jik from sawon;   -- distinct : 중복자료의 배제(직급을 검색하면 사원이 여러명이고,, 여러명인 칼럼들의 중복을 배제하고 검색한다.

 

--레코드 제한
select * from sawon where sawon_jik='대리';  --jik에서 대리 레코드만 나온다. 단 ' ' 묶어주는것을 잊지말자.
select * from sawon where sawon_no=5;  --숫자를 검색할때 '' 붙여도 되고 안붙여도 된다.
select * from sawon where sawon_ibsail='2002-03-02';  --날짜는 ' ' 꼭 둘어야한다.(즉 날짜와 문자열은 꼭 둘러야하고, 나머지는 안둘러도 상관없다.)
--alter session set nls_date_format='YYYY-MM-DD'; --오라클명령 : 날짜 형식 변경 (외울필요없다.)
select * from sawon where sawon_jik='사원' and sawon_pay <= 2500;  -- and 조건을 줄 수 있다.(or, not도 가능.)
select * from sawon where sawon_jik='사원' and (sawon_gen='여' or sawon_ibsail <= '2002-12-31'); --or는 앞에 조건과 상관없이 또는 뒤에 조건을 만족하는 것.
select * from sawon where sawon_no >= 5 and sawon_no <= 10;  -- 아래와 같다.
select * from sawon where sawon_no between 5 and 10; -- and에서는 between 연산자를 쓸 수 있다. 윗 줄과 같은 내용이된다.
select * from sawon where sawon_ibsail between '2000-1-1' and '2005-12-31';
select * from sawon where sawon_no < 5 or sawon_no > 10;
select * from sawon where not(sawon_no < 5 or sawon_no > 10); --조건을 줄 때 부정을 주게되면 속도가 떨어진다. 그러므로 긍정적 형태로 주는게 좋다.
select * from sawon where sawon_no not between 5 and 10; --between 앞에도 not을 붙여서 부정을 줄 수 있다.
select * from sawon where sawon_pay + 1000 >= 3000;  --기존의 값에 값을 바꿔서 비교를 해줄 수 도있다.

select * from sawon where sawon_name='한국남'; 
select * from sawon where sawon_name >= '박';  --문자를 크거나 작은지 비교 할 수 있다.
select ascii('a'), ascii('A'), ascii('가'), ascii('나') from dual;   -- 위를 비교 할 수 있는 이유는 아스키코드값으로 문자를 읽어오기 때문이다.
select * from sawon where sawon_name between '김' and '최';  --'김'으로 시작하는.... '최'까지만 나오기때문에 '최XX'인 자료는 안나온다.
select * from sawon where sawon_jik='대리' or sawon_jik='과장'; --아래와 같은 뜻.
select * from sawon where sawon_jik in('대리','과장'); --in 나열 연산( 위와 같은 뜻.)
select * from sawon where buser_num in(20, 40) order by buser_num desc; -- 즉 (buser_num=20 or buser_num=40) 과 같은 뜻이다.(in연산자는 끼리끼리 모아줄 수 있다는 장점이 있다.)

select * from sawon where sawon_name like '이%'; -- like 연산자 : '이'로 시작되는... 이란 뜻(뒤에 %는 이부터 아무거나 쓰라는 기호이다.엑세스에서는 이*.doc 이런형식.)
select * from sawon where sawon_jik like '%장';  -- 마지막 글자만 '장'으로 끝나면 된다는 뜻.
select * from sawon where sawon_name like '%순%';--이름안에 '순'이라는 이름을 가진사람 모두 나와라, 굳이 꼭 가운데 일필요는 없음. 아무데나 있으면 찾아짐.
select * from sawon where sawon_name like '이순%';
select * from sawon where sawon_name like '이_라'; --'이', '라'는 꼭 나오고 가운데 한글자만 아무거나 써도 된다는 뜻.
select * from sawon where sawon_name like '___'; --이름이 세글자인 사람 나와라~~~~~~~   (칸 하나당 한글자를 의미한다.)
select * from sawon where sawon_name like '이\%'; --이름안에서 %,\를 찾으려고 하면 앞에 \(백슬레시)를 붙여서 문자를 찾아낸다.
select * from sawon where sawon_pay like '3%';  --연봉이 3으로 시작되는(즉, 숫자에 대해서도 like연산자를 쓸 수 있다.)
select * from gogek where gogek_jumin like '_______1%'; --남자만 표시하기
select * from gogek where gogek_jumin like '%-1%';   --위와 같다.(남자만 표시하기)
update sawon set sawon_jik=null where sawon_no=5; -- 이명령만 쳤을경우에는 원본데이터는 자료가 안바뀐다.
commit;  -- ***********  커밋을 써주면 원본자료에도 적용이된다.

select * from sawon;
select * from sawon where sawon_jik=null;  --성립이 안된다. null자료는 비교할 자료가 없기때문에 찾을 수 없다.
select * from sawon where sawon_jik is null; -- is null : null인 자료를 찾으려면 이런식으로 해줘야한다.
select * from sawon where sawon_jik is not null;  --null인 자료만 빼고 나머지를 불러준다. null 제외, null제외

select sawon_no as 사번, sawon_name as 직원명, sawon_jik as 직급, sawon_pay as 연봉, sawon_pay / 12 as 보너스, sawon_ibsail as 입사일 from sawon
where sawon_jik in('사원','대리','과장') and sawon_pay between 2000 and 4500 and sawon_ibsail >= '2000-1-1' order by sawon_jik asc, sawon_pay desc;

 

--연산자 우선 순위
-- () > 산술 > 연결 > 비교 > is null, like, in > between > not > and > or

 

--내장 함수 : 제작사가 제공하는 함수, 자료, 조작의 효율성 향상이 목적
--문자 함수 :
select 'hello', upper('hello'),lower('HELLO'),initcap('hello world')  from dual;  -- upper : 소문자를 대문자로, lower :대문자를 소문자로, initcap:첫글자만 대문자로
select concat('hello', 'world') from dual; -- concat: 문자열 연결 함수.
select substr('hello world', 3) from dual; --substr: 문자열 추출(3으로 줬기때문에 3번째부터 문자열 추출. 결과 : 'llo world')
select substr('hello world',3,5) from dual; -- 3번째 글자부터 5번째 글자까지. (결과 : 'llo w' )
select substr('hello world',-3,2) from dual;  -- (-기호를 붙이면 뒤에서부터가 된다. 즉 뒤에서부터 2개 글자까지만 추출) 결과: 'rl'
select length('hello world') from dual;  -- length : 문자열의 길이를 얻는다.
select instr('hello world', 'e')from dual; --instr : 문자의 위치를 알아낸다.
select instr('hello world', 'o',6)from dual; --문자를 뒤지는데 첫번째부터 뒤치지말고, 6번째 문자부터 'o'를 찾아라! 그래서 그게 몇번째 있는지 알아낸다.
select lpad('hello', 10,'*')from dual; --총 10자리를 확보해서 hello로 채우고 나머지는 '*'로 왼쪽을 채워라. (왼쪽을 채우는 이유는 lpad 이기때문이다. rpad도 있다.)
select rpad('hello', 10,'*')from dual; --총 10자리를 확보해서 hello로 채우고 나머지는 '*'로 오른쪽을 채워라.
select replace('011.111.1111','.','-') from dual; --replace :문자대체('.'을 '-'로 바꿔주어서 표시)

문) sawon 테이블에서 이름에 '이'가 포함된 직원이 있으면 '이'부터 2자만 출력
답:--
select sawon_no, sawon_name, substr(sawon_name,instr(sawon_name,'이'),2) from sawon where sawon_name like '%이%';

 

--숫자 함수
select round(45.678,1) from dual;  -- round: 반올림 함수 (소수 첫번째 자리에서 반올림 하라는 뜻)
select round(45.678,0), round(45.678),round(45.678,1), round(45.678,-1) from dual; --첫번째 : 1자리에서 반올림 하시오, 두번째: 첫번째랑 같은의미(즉 0은 생략가능), 세번째: 소수 첫번째 자리에서 반올림하시오.  네번째: 10자리에서 반올림하시오
select sawon_name, sawon_pay, round(sawon_pay * 0.025,2) as tex from sawon;
--sql서버에게 계산을 너무 많이 시키는 것은 안좋다. 그냥 끌고와서 java에서 계산해줘도 괜찮다.
select trunc(45.678, 1), trunc(45.678) from dual;  --trunc : 절삭 (자리수 버림.(x)반올림아니다.)
select mod(15,2) from dual; --나머지를 구하는 것.  java에서 15%2 의미다.
--...인터넷 검색하면 더 많이 나온다(오라클 숫자 함수)

--날짜 함수
select sysdate, sysdate +100, sysdate -100 from dual;  --오라클이 가지고 있는 날짜 데이터를 가지고온다. (날짜 유효 범위:BC 4712년 ~ AD 9999년)
select sawon_ibsail, round((sysdate - sawon_ibsail)/7) as 근무주 from sawon;
select sawon_name, sawon_ibsail from sawon where (sysdate - sawon_ibsail) * 24 > 50000; --근무한 지 5만 시간이 경과한 직원
select months_between('2013-01-10', sysdate) from dual;  -- months_between : 월 차를 구한다.
select add_months('2013-5-5', 3), add_months('2013-5-5', -3)  from dual; --add_months : 월 합을 구한다. (즉 +3은 3개월을 더해주고, -3은 3개월을 뺴준다.)
select sysdate, last_day(sysdate) from dual; --  last_day : 그 달의 마지막날을 구해준다.
select sysdate, next_day(sysdate,'금') from dual; -- next_day : 지금 시간을 시점으로 다가오는 '금'요일은 몇일인지를 구할 수 있다.
--언어변경: 영어로 바꾸기 alter session set nls_language='american';
--언어변경: 한글로 바꾸기 alter session set nls_language='korean'
select round(sysdate, 'YEAR'), trunc(sysdate, 'YEAR') from dual;
select sawon_name, sawon_ibsail, round(sawon_ibsail , 'YEAR') from sawon; --날짜(년)에 (절반이 넘어가느냐 안넘어 가느냐)에 대한 반올림
select sawon_name, sawon_ibsail, round(sawon_ibsail , 'MONTH') from sawon; --날짜(월)에 대한 반올림

--형 변환 함수 : 문자 -> 날짜
select sysdate, sysdate - to_date('2001-1-1','YYYY-MM-DD') from dual; -- to_date : 문자열을 날짜로 형변환 시켜준다. 뒤에 형식을 지정해줘야한다. 'YYYY-MM-DD',,,등으로
select sysdate, sysdate - to_date(sawon_ibsail, 'YYYY-MM-DD') from sawon;

--형 변환 함수 : 날짜 -> 문자
select to_char(sysdate, 'YYYY"년" MM"월" DD"일" HH"시" MI"분"') from dual; -- to_char(바꿀것) : 문자로 형변환, 또 형식도 줄수있다. '""' 따옴표를 잘 볼 것!
select to_char(sysdate, 'YYYY-MM-DD'), to_char(sysdate, 'YY-MON-DDD'), to_char(sysdate, 'WW'), to_char(sysdate, 'W'), to_char(sysdate, 'Q')from dual;  -- 각종 형식~~ MON: ?월, DDD: 1일부터~ 몇일째인지, WW:올해의 몇주차인지, W: 이번달의 몇주차인지, Q: 1년을 1/4로 나워서 몇분기 인지 표시

--형 변환 함수 : 숫자 -> 문자
select sawon_name, to_char(sawon_ibsail, 'DDD"일"') as 그해몇일 from sawon; --사원의 입사일을 찾아서 그해의 몇일째 인지 알아보는 것
select to_char(123,'99999'), to_char(123,'09999'), to_char(1234,'$9,999'), to_char(1234.4567,'9,999.99') from dual;  --99999 : 무효의 0를 공백으로 바꿔준다.  09999 : 맨앞에만 0으로 채우고 나머지 4자리는 공백으로 채워준다.

-- 기타 함수
--NVL(value1, value2) : value1값이 null이면 value2값으로 대체하는 함수
select sawon_name, sawon_jik,nvl(sawon_jik,'임시직') from sawon;  --nvl(value1, value2) : value1값이 null이면 value2값으로 대체하는 함수
select sawon_name, sawon_jik,nvl(sawon_jik,0) from sawon;
select * from sawon;
--update sawon set sawon_pay=null where sawon_no=4;
--commit;
select sawon_name, sawon_pay from sawon;
select sawon_name, nvl(sawon_pay, 1000) from sawon;

 

--NVL2((value1, value2, value3) : value1값을 평가해서 null이 아니면 value2가되고, null이면 value3이 된다.
select sawon_name, nvl2(sawon_jik,'정규직','임시직'), sawon_pay, nvl2(sawon_pay, sawon_pay*0.1, nvl(sawon_pay,0)) as 특별수당 from sawon;

--nullif(value1, value2) : 두 개의 값이 일치하면 null, 일치하지 않으면 value1을 준다.
select length('123'), nullif('123','있음'),nullif('','있음')from dual;
select sawon_name, sawon_jik, nullif(sawon_jik, '대리') from sawon; -- 두개의 값을 비교해서 같으면 null을 찍고 다르면 원래있던 값을 찍어준다.
select sawon_name, sawon_jik, nullif(sawon_jik, '사원') from sawon;

--rank(), dense_rank()  : 순위 결정 함수 : 반드시 over과 함께 해야한다.
select sawon_no, sawon_name, sawon_pay,
rank() over(order by sawon_pay desc),    --rank() : 동점 자가 있을 경우에 카운트를 처리하고 넘어간다.즉 1,2,2,4.....
dense_rank() over(order by sawon_pay desc) --dense_rank()  : 동점 자가 있을 경우 처리 안함.  즉 1,2,2,3,4........
from sawon where sawon_pay is not null;

--내장함수는 네이버 또는 카페 db-50 번글 참조

 

 

--Conditional Expression(조건 표현식)
case 표현식 when 비교값 then 결과값...[else 결과값 n] end as 칼럼별명
select case 10/5 when 5 then '안녕' when 2 then '수고' else '기타' end as 결과 from dual;
-- 10/5의 값이 5일경우 '안녕' 2일 경우 '수고' 그게아니면 '기타'

--**형식1)
select sawon_name,
case sawon_pay when 4300 then '연봉 4300' when 2200 then '연봉 2200' else '기타연봉' end as 결과, sawon_pay from sawon; --값이 정확히 맞아 떨어져야 한다.

select sawon_name, sawon_pay, case sawon_jik when '사장' then sawon_pay*0.05
when '부장' then sawon_pay*0.04 when '과장' then sawon_pay*0.03 when '대리' then sawon_pay*0.02 else sawon_pay * 0.01 end as donation from sawon;

--**형식2)
select sawon_name,
case when sawon_gen='남' then 'M' when sawon_gen='여' then 'F' end as gender from sawon;

select sawon_name, sawon_pay,
case when sawon_pay >= 4000 then '우수연봉'
when sawon_pay >= 3500 then '보통연봉'
else '저조연봉' end as result from sawon where sawon_jik in('사원','대리','과장');

--decode (oracle 전용) : 비교시 사용 --오라클에서만 사용되기때문에 되도록 case문으로 비교를 구현하는게 좋다. 이건 참고만 할 것.
select sawon_name, sawon_pay, sawon_jik, decode(trunc(sawon_pay/1000), 4,'A',3,'A',2,'B',1,'C',0,'D') grade from sawon where sawon_pay < 5000;

select sawon_name, decode(buser_num, 10,'총무부',20,'영업부',30,'전산부',40,'관리부','무소속') as dept from sawon;

select * from buser;

select * from sawon;

 

-------------------<   연습문제  > -------------------------

 

-- 문1) 10년 이상 근무하면 '특별', 5년 이상이면 '우수', 그 외는 '일반'이라 표현, 1990년 이후 입사한 직원만 참여  <case문 사용>
-- 수당(pay 기준) : 10년이 이상 10%, 5년 이상 5%, 나머지는 3% 지급(정수로 표현 : 반올림)
-- 출력 형태  ==> 직원명   연봉     근무년수  표현   특별수당
--                홍길동 80,000,000    11     특별    150,000

select sawon_name as 직원명,to_char(sawon_pay*10000,'999,999,999') as 연봉, round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) as 근무년수,
case when round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) > 10 then '특별'
when round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) > 5 then '우수' else '일반' end as 표현,
case when round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) > 10 then to_char((sawon_pay*1000),'99,999,999')
when round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) > 5 then to_char((sawon_pay*500),'99,999,999') else to_char((sawon_pay*300),'99,999,999') end as 특별수당 from sawon where sawon_ibsail > '1990.01.01';

 
-- 문2) 입사 후 7년 이상이면 '왕고참', 3년 이상이면 '고참', 나머지는 '보통'이라 출력    <case문사용>
-- 출력 형태 ==>  직원명   직급    입사년월일       근무개월수   구분    부서
--                홍길동   부장   1999년 3월12일     7890      왕고참    총무부

select sawon_name as 직원명, sawon_jik as 직급, to_char(sawon_ibsail, 'YYYY"년"MM"월"DD"일"') as 입사년월일, trunc(months_between(sysdate, sawon_ibsail)) as 근무개월수,
case when round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) > 7 then '왕고참'
when round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365) > 3 then '고참' else '보통' end as 구분,
case buser_num when 10 then '총무부' when 20 then '영업부' when 30 then '전산부' else '관리부' end  from sawon;

-- 문3) 각부서별로 실적에 따라 급여를 달리 인상하려 한다. 10,30번 부서는 각 10%, 20% 인상하고, 나머지 부서는 
--  동결할 경우의 급여를 decode 함수를 사용하여 출력하라.
-- 출력형태 ==>   사번    직원명     부서    연봉       인상액    장기근속    <== 10년이상
--                  1     홍길동    영업부 80,000,000   800,000        O  or  X

select sawon_no as 사번, sawon_name as 직원명, case buser_num when 10 then '총무부' when 20 then '영업부' when 30 then '전산부' else '관리부' end as 부서명, to_char(sawon_pay*10000,'999,999,999') as 연봉,
decode(buser_num, 10,to_char((sawon_pay*0.1)*10000,'999,999,999'),30,to_char((sawon_pay*0.3)*10000),'999,999,999'),to_char((sawon_pay*0),'999,999,999')) as 인상액,
decode(round((sysdate-to_date(sawon_ibsail,'YYYY-MM-DD'))/365),1,'X',2,'X',3,'X',4,'X',5,'X',6,'X',7,'X',8,'X',9,'X','O') as 장기근속 from sawon where sawon_pay is not null ;