SQL select 명령어 알아보기

< select >

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

-- [into 테이블명] from 테이블명...

-- where 조건... order by 기준키 [asc / desc]

select * from buser;

select * from sawon;

alter session set nls_date_format='YYYY-MM-DD';

< 칼럼 순서, 칼럼명 바꿔주기 >

select sawon_no,sawon_name,sawon_pay from sawon;

select sawon_pay,sawon_no,sawon_name from sawon;

select sawon_no as 사번, sawon_name as 직원명, sawon_pay 연봉 from sawon;

select sawon_no || sawon_name as 직원자료 from sawon;

select 10, '안녕', sawon_name from sawon; -- 그냥 데이터

select sawon_no,sawon_name || '님' as name from sawon;

select 'kbs', 10 / 3, 10 * 2 from dual; -- 더미 테이블

select sawon_name as 이름, sawon_pay * 0.02 as 세금 from sawon;

< 치환변수 >

select * from &table_name;

select sawon_no, sawon_name from sawon;

< 정렬 >

order by 칼럼명 [asc | desc]

select * from sawon order by sawon_no desc; -- 사원 번호별로

select * from sawon order by sawon_jik asc; -- 직급별로

select * from sawon order by sawon_jik asc, sawon_pay desc; -- 직급별로

select * from sawon order by sawon_jik, sawon_pay desc;

select * from sawon order by sawon_jik, sawon_gen, sawon_pay;

select sawon_name, sawon_pay, sawon_pay / 1000 * 1000

from sawon order by sawon_pay;

select sawon_name, sawon_pay, sawon_pay * 0.05 as tex

from sawon order by tex desc;

select sawon_name, jawon_jik, buser_num from sawon order by 2 desc, 3 asc ; -- position number 사용

select sawon_jik from sawon;

select distinct sawon_jik from sawon; -- 중복자료 배제

select distinct sawon_jik, sawon_name from sawon; --x

select distinct buser_num from sawon order by 1;

< 레코드(행) 제한 : where 조건 >

select * from sawon where sawon_jik='대리';

select * from sawon where sawon_no=3;

select * from sawon where sawon_ibsail='2011-03-03';

select * from sawon where sawon_no=3 or sawon_no >= ( 3+4);

select * from sawon where sawon_jik='사원'; or sawon_jik='대리';

select * from sawon where sawon_jik='사원'

and sawon_gen='남' and sawon_ibsail <= '2015-1-1';

select * from sawon where sawon_no >=5 and sawon_no <= 10;

select * from sawon where sawon_no >=5 and sawon_no >10;

select * from sawon where sawon_no >=5 and sawon_no <= 10;

select * from sawon where sawon_ibsail between '2010-1-1' and '2012-12-31';

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 >= '이' and sawon_name <='최';

select * from sawon where sawon_jik='대리' or sawon_jik='과장' or sawon_jik='부장';

select * from sawon where sawon_jik in('대리', '과장', '부장');

select * from sawon where sawon_jik in('대리','과장','부장') order by sawon_jik;

select * from sawon where buser_num in(10,30);

select * from buser;

select * from buser where buser_name in('총무부', '영업무');

select * from gogek where GOGEK_DAMSANO in(1,2,5);

select gogek_no, gogek_name,GOGEK_DAMSANO from gogek where GOGEK_DAMSANO in(1,2,5) order by GOGEK_DAMSANO desc;

< like 조건에는 %와 언더바(_) 사용 가능. 퍼센트는 0개 이상의 문자열 / 언더바는 하나의 문자) >

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 '박__'; -- 3글자 짜리 검색 _ _ 언더바 2개

select * from sawon where sawon_name like '이순%' or sawon_name like '이미%'; -- %가 1개일수도 2개일 수도 있음.

sawon_name like '이미_';

select * from sawon where sawon_pay like '5%';

select * from gogek;

select * from gogek where GOGEK_JUMIN like '%-1%';

< null인 자료 >

update sawon set sawon set sawon_jik=null where sawon_no=5;

commit;

select * from sawon;

select * from sawon where sawon_jik=null; -- x

select * from sawon where sawon_jik is null;

select * from sawon where sawon_jik is not null;