SQL select 명령어 및 데이터 정렬 예제

728x90
< 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;







본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
본문은 '남궁성, JAVA의 정석 3rd Edition' 교재를 공부하면서 주요 내용을 요약하고 있습니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.





728x90

'DATA' 카테고리의 다른 글

SQL subquery 연습문제 풀이  (0) 2018.01.17
SQL union, merge, subquery 명령어 요약  (0) 2018.01.16
SQL join 명령어 요약  (0) 2018.01.16
SQL select 명령어 및 내장함수 예제  (0) 2018.01.15
SQL 시퀀스 명령어 요약  (0) 2018.01.11
SQL 명령어 정리 #1  (2) 2018.01.10