기초 SQL 정리

--비교연산자 like ,between  and , in

select *

from EMP02_EMP

where emp02_person_id like '%2';

select *

from EMP02_EMP

where emp02_emp_id between 2000001 and 2000032;

select *

from EMP02_EMP

where emp02_emp_id in (2000001, 2000002, 2000003);

select *

from EMP02_EMP

order by emp02_note desc;

--컬럼의 별칭을 통한 정렬

select emp02_emp_id 사원

from EMP02_EMP

order by 사원;

select emp02_emp_id, emp02_person_id, emp02_emp_no

from EMP02_EMP

order by emp02_emp_id, emp02_person_id, emp02_emp_no;

--논리연산자 사용 and or

select *

from EMP02_EMP

where emp02_emp_id >2000005

and emp02_person_id like'%4';

select *

from EMP02_EMP

where emp02_emp_no >200019

or emp02_emp_kind_cd like'01%';

--논리연산자 not

select *

from EMP02_EMP

where emp02_emp_kind_cd not in ('010');

--단일행 함수의 종류 [문자, 숫자, 날짜, 일반, 변환]

--단일행 함수 중 문자함수의 사용의 예

select *

from EMP01_PERSON;

where lower(emp01_email_addr) = 'hnhwang@hdfund.co.kr';

select emp01_person_id,concat(emp01_company,emp01_kor_nm),length(emp01_kor_nm)--, instr(emp01_nat_cd,'K')

from EMP01_PERSON;

--where wubstr(emp01_company_id,3);   

--단일행 함수의 변환함수 사용의 예

select emp01_person_id, to_char(emp01_birthday,'DD Month YYYY') hiredate

from EMP01_PERSON;

--그룹함수 사용 [숫자데이터, 날짜데이터]

select avg(emp01_person_id), sum(emp01_person_id), max(emp01_person_id), min(emp01_person_id)

from EMP01_PERSON;

--그룹함수 사용 [count 사용]

select count(emp01_person_id)

from EMP01_PERSON

where EMP01_GENDER_CD = 'M';

--그룹함수 having절 적용

select *

from EMP01_PERSON

group by EMP01_KOR_NM

having EMP01_PERSON_ID > 2000006;

--equijoin

select *

from EMP01_PERSON, EMP02_EMP

where emp01_person_id = emp02_person_id;

--서브퀴리 

select *

from EMP01_PERSON

where emp01_person_id > (

select emp01_person_id

from EMP01_PERSON

where EMP01_COMPANY_ID =2004475

);

select emp01_person_id

from EMP01_PERSON

where EMP01_COMPANY_ID =2004475;