본문 바로가기
뒷북 정리 (국비 교육)/sql, db

[oracle DB] DQL (Data Query Language)

by 규글 2021. 12. 17.

3. DQL (Data Query Language) (select 문)

  • operator / function / join / subquery / rownum

 

 

 

select column1, column2 from table명 select task, success from table;​

 

  1. select는 table의 내용을 확인할 때 사용한다. 그냥 원하는 column을 나열할 수도 있지만, max(column)이나 average(column)도 가능하며, 사칙연산도 할 수 있다. select의 결과 또한 임시이지만 table이다. 만약 아무 정보가 없다면 선택된 record가 없다는 메시지를 확인할 수 있다. 예시처럼 task column에 data를 넣고 다시 확인하면 내용을 확인할 수 있다. 우측 success column의 빈 공간은 비어있는 상태, null 이라고 한다. (지갑이 null. 참 nerd같은 선생님의 드립이 있었다.) 이 빈 공간을 채우고 싶다면 update문을 사용한다.

  2. * : wild card이다. 모든 정보를 의미한다. column의 수가 너무 많은 경우에, 그 모든 column의 이름을 전부 작성하기는 너무 번거롭기 때문에 *로 대체할 수 있다.
    select xxx as 'alias1', yyy as 'alias2'...

select xxx as alias1, yyy as alias2...​
  3. alias : 별칭이다. as 뒤의 이름으로 새롭게 column name을 정할 수 있다. 작은 따옴표인 ' '는 생략이 가능하며, 공백문자는 들어갈 수 없다. as 자체를 생략하고 column 이름과 별칭을 순서대로 나열하는 방법도 있다.

  4. from은 어떤 table로부터 data를 가져올 것인지를 작성한다. table 하나만 올 수도 있지만 ,(쉼표)로 구분해서 나열할 수도 있으며, 특정 select 문의 결과 자체로부터 data를 가져올 수도 있다.
  5. where은 어떤 조건에 해당하는 data를 가져올 것인지를 작성한다. 이 where 조건절이 존재하지 않는다면 table의 모든 data를 가져오겠다는 의미이다. 이곳에는 javascript에서 if문에 들어갔던 조건처럼 생긴 내용들을 작성하게 된다. 예를 들어 sal>=2000 과 같은 내용을 작성하면, 해당 내용이 true인 row만 남겨서 select 해준다. 실제로 oracle에서 비교 연산자를 이용해서 비교하기는 하지만 boolean type이 있는 것은 또 아니다.

  6. group by는 select하려는 row들을 group화 할 때 작성한다. 예를 들면 job이라는 column의 data가 같은 것들끼리 group화 할 수 있겠다. group by 절을 작성할 때는 group으로 묶을 column명이나 count와 같은 group function만 올 수 있다. 이 group by 절에 group function만 올 수 있는 것처럼, where 절에 group function은 허용되지 않는다. group화는 둘 이상의 column으로도 가능하며, 이때는 ,(쉼표)로 연결한다.
  7. having은 group by로 묶은 group에 조건을 줘서 추려낼 때 작성한다.

  8. order by는 가장 마지막에 수행되는 부분이다. select한 것들 중에서 어떤 방식으로 sort할 것인지를 작성한다.
    order by column1 desc : column1에 해당하는 내용들을 내림차순 정렬. (descend, desc table과 다른 desc이다.)
    order by column1 asc : column1에 해당하는 내용들을 오름차순 정렬. (ascend)

 

operator (연산자)

  1. 산술 연산자 : +, -, *, /. 산술 연산을 해서 select 할 수도 있다.
  2. 비교 연산자 : =, !=, >=, <=, >, <. 비교 연산자로 비교를 하긴 하지만 boolean type이 있는 것은 아니다.
  3. 논리 연산자 : and, or, not(!). javascript에서처럼 and 대신 &&, or 대신 ||를 사용할 수는 없다. not 대신 !를 사용하는 것은 가능하다.
  4. sql 연산자
    where column=a or column=b

where column in (a,b)​

    • in : or와 비슷한 역할을 한다. 해당 column에 대한 조건이 a or b가 된다.
      where column any(a,b,c)

where column any(1000,2000,3000)​
    • any : 조건을 비교함에 있어서 어느 하나라도 맞는다면 true이다. 예시의 경우는 column이 1000 이상인 경우 select 하게 될 것이다.
      where column all(a,b,c)

where column all(1000,2000,3000)
    • all : 조건이 모두 맞을 때 true이다. 예시의 경우는 column이 3000 이상인 경우 select 하게 될 것이다.
      where column between a and b

where column between 1000 and 2000​
    • between : column data가 a와 b 사이에 있는 경우 true이다. 사이라고 하지만 a와 b를 포함한다. 숫자 data에서 between이 동작하는 것은 당연하고, 문자열에서도 between이 동작한다.
      where column is null

where column is not null​
    • is null, is not null : 각각 null인 경우, null이 아닌 경우 true이다. data의 null 여부를 확인할 때 사용한다.
      where exists (select * from table명 where column='a')​
    • exists : data가 존재하면 true이다. 특정 data가 존재할 때 data를 뽑아낼 때 사용할 수 있을 것 같다. 예시의 경우는 select 되는 row가 한 줄이라도 있다면 true가 된다.
      where column like 'J%' : J로 시작

where column like '%J%' : J를 포함

where column like '_A%' : 두 번째 글자가 A

where column like '%ES' : ES로 끝남

where column like '81%' : 81로 시작​
    • like : 문자열을 비교할 때 사용하는 연산자이다. 때문에 keyword 검색에 사용한다. 중요한 부분이니 숙지한다. %는 blah blah를 의미한다. 이 자리에 글자가 없어도 된다.
      select column1 || '은' || column2 || '입니다.' from table명​
    • || : 결합 연산자이다. javascript 에서의 or 대신 사용하는 것이 아니다. 단순히 문자열을 연결해서 하나의 data로 return하고자 할 때 사용한다.

 

function (함수)

  • 단일행 함수과 복수행 함수가 있다.
    단일행 함수 : 하나의 row당 하나의 결과값을 반환하는 함수이다.
    복수행 함수 : 여러 개의 row당 하나의 결과값을 반환하는 함수이다.

단일행 함수 (문자함수)

chr(ASCII code)
ascii('문자')
concat(column명, '문자열')
initcap('문자열')
lower('문자열')
upper('문자열')
lpad('문자열', 전체 자릿수, '남는 자리를 채울 문자')
rpad('문자열', 전체 자릿수, '남는 자리를 채울 문자')
ltrim('문자열', '제거할 문자')
rtrim('문자열', '제거할 문자')
replace('문자열1', '문자열2', '문자열3')
substr('문자열', n1, n2)
length('문자열')
least('문자열1', '문자열2', '문자열3')
nvl(column명, 값)
  1. chr : 해당 ASCII code에 맞는 값을 return해준다.
  2. ascii : 해당 문자에 맞는 ascii code를 return해준다.
  3. concat : column data와 문자열을 연결한다.
  4. initcap : 문자의 시작을 대문자로 바꿔서 return한다.
  5. lower : 문자열을 소문자로 바꿔서 return한다.
  6. upper : 문자열을 대문자로 바꿔서 return한다.
  7. lpad : 문자열의 왼쪽에 남는 자릿수만큼 문자로 채워서 return한다.
  8. rpad : 문자열의 오른쪽에 남는 자릿수만큼 문자로 채워서 return한다.
  9. ltrim : 제거할 문자와 다른 게 나올 때까지 왼쪽부터 다 지워서 return한다.
  10. rtrim : 제거할 문자와 다른 게 나올 때까지 오른쪽부터 다 지워서 return한다.
  11. replace : 문자열1에 있는 내용 중 문자열2를 찾아서 문자열3으로 바꿔서 return한다.
  12. substr : 문자열의 n1 번째 위치에서부터 n2 개만큼 문자열을 빼온다.
  13. length : 문자열의 길이를 return한다.
  14. least : 문자열 중에 가장 우선되는 값을 return한다.
  15. nvl : 해당 column의 data가 null인 경우, 정해준 값을 return하도록 한다. nvl은 자주 쓰니까 꼭 기억하도록 한다.

 

단일행 함수 (숫자함수)

abs(숫자)
ceil(소수점이 있는 숫자)
floor(소수점이 있는 숫자)
round(숫자, 자릿수)
mod(숫자1, 숫자2)
trunc(숫자, 자릿수)
  1. abs : 숫자의 절댓값을 return한다.
  2. ceil : parameter와 같거나 큰 정수를 return한다.
  3. floor : parameter와 같거나 작은 정수를 return한다.
  4. round : 숫자의 소수점 이하 자릿수+1 에서 반올림한 수를 return한다. 방금 작성하다가 해봤는데, 놀랍게도 자릿수에는 음수가 들어간다. 음수는 소수점 이하가 아닌 0보다 큰 정수에서의 반올림을 수행해주더라.
  5. mod : 숫자1을 숫자2로 나눈 나머지를 return한다.
  6. trunc : 숫자를 소수점 이하 해당 자리수까지만 나타내어 return한다.

 

단일행 함수 (날짜)

sysdate
add_months(날짜, 더할 개월 수)
last_day(날짜)
months_between(날짜1, 날짜2)
  1. sysdate : 현재 시간을 return한다.
  2. add_months : 해당 날짜에 2개월을 더한 날짜를 return한다.
  3. last_day : 해당 날짜에 해당하는 달의 마지막 날을 return한다.
  4. months_between : 두 날짜 사이의 개월 수를 return한다.

 

단일행 함수 (문자 변환 함수)

to_char(sysdate, 'yyyy-mm-dd')
to_char(sysdate, 'yyyy:mm:dd')
to_char(sysdate, 'yyyy.mm.dd')
to_char(sysdate, 'yy.mm.dd')
to_char(sysdate, 'yy" 년 "mm" 월 "dd" 일 "')
to_char(sysdate, 'hh:mi:ss' )
to_char(sysdate, 'am hh:mi:ss' )
to_char(sysdate, 'pm hh:mi:ss' )
to_char(sysdate, 'hh24:mi:ss' )
to_char(sysdate, 'hh24" 시 "mi" 분 "ss" 초 "' )
to_char(sysdate, 'yy" 년 "mm" 월 "dd" 일 " hh24" 시 "mi" 분 "ss" 초 "')
to_char(sysdate, 'mm.dd day')
to_char(sysdate, 'mm.dd dy')
to_char(sysdate, 'mm.dd d')
  1. 문자 변환 함수는 사용 가능성 100%이다.
  2. 날짜는 문자로 보이지만 문자가 아니라 날짜이다.
  3. 년, 월, 일 같은 내용들은 그냥 작성하면 안되고, 큰 따옴표 " "로 감싸주어야 한다. 특수문자는 큰 따옴표로 감싼다.
  4. yyyy : 네 자리로 연도를 표기.
    yy : 마지막 두 자리로 연도를 표기.
    mm : 월 / dd : 일
    hh : 시 / hh24 : 시간을 24시 단위로 표기.
    mi : 분 / ss : 초
    day : x요일 / dy : x / d : 목요일일 경우 5 (일요일부터 1)

 

단일행 함수 (숫자, 날짜 변환 함수)

to_number('숫자에 대응되는 문자')
to_date('날짜에 대응되는 문자')
to_date('문자열', '형식')
  1. to_number : 문자열인 숫자를 숫자 type으로 바꿔서 return해준다.
  2. to_date : 해당 문자를 날짜로 바꿔준다. 특정 형식에 맞게 바꿔주기도 한다. table에 insert할 때, 날짜의 형식을 정해서 넣을 때 사용하는 것 같다.

 

복수행 함수 (group function)

count(column명)
sum(column명)
avg(column명)
max(column명)
min(column명)
  1. count : 해당 column에 data가 존재하는 row의 수를 return한다. null인 column은 count하지 않는다.
  2. sum : 해당 column의 data를 모두 더한 값을 return한다.
  3. avg : 해당 column 모든 data의 평균값을 return한다. null인 column은 제외한다. null인 column도 포함하고 싶다면 nvl을 이용하면 된다.
  4. max : 해당 column에서의 최댓값을 return한다.
  5. min : 해당 column에서의 최솟값을 return한다.

 

oracle join & ansi join

select column1, column2...
from table1, table2...
where join condition1 and condition2...
  1. join : 하나의 table로 원하는 column 정보를 참조할 수 없는 경우, 관련된 table을 논리적으로 결합하여 원하는 column 정보를 참조하는 방법이다. 2개 이상의 table을 모두 참조해서 원하는 정보를 추린 후 하나의 결과물을 만들어낼 때 사용한다. oracle join과 ansi join의 두 가지 작성법이 있고, join에는 inner join, outer join, self join, cross join이 있다.

  2. oracle join
    • inner join
      select ename, hiredate, emp.deptno, dname
from emp, dept 
where emp.deptno = dept.deptno 
and dname = 'ACCOUNTING';

select ename, hiredate, e.deptno, dname
from emp e, dept d
where e.deptno = d.deptno
and dname = 'ACCOUNTING';

      같은 이름의 column일 경우 어떤 table의 column인지를 명시해주어야 한다. 그렇지 않으면 어떤 table의 column인지 갈피를 잡지 못해서 오류를 낸다. 만약 이 예시의 경우 emp table의 data가 12개, dept table의 data가 4개라면 조건이 없을 때 몇 개가 select될까? 특별한 조건이 없다면 가능한 모든 조합을 만들기 때문에 48개가 select된다. (cross join)
      table에는 alias를 이용해서 별칭을 넣을 수 있다.

    • outer join (left outer / right outer / full outer)
      select e.empno, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno; (right outer join)

select e.empno, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno(+); (left outer join)

      inner product가 있으면 outer product가 있듯, inner join이 있다면 outer join이 있다.
      두 테이블을 참조하는데, 한 쪽에는 data가 없을 수 있지 않을까? 예시의 코드에서 emp table의 empno에 비어있는 cell이 있을 수도 있다. 그럴 때 비어있는 쪽에 (+)를 작성하는 방식으로 outer join을 쓴다. oracle join 작성법에는 full outer join은 안타깝게도 없다.

    • self join
      select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno ;​

      참조해야할 column이 자기 자신의 table에 있을 때 사용한다. 따로 표기법은 없고, from 절에서 table을 두 번 호출한다.

    • cross join
      select ename, hiredate, emp.deptno, dname
from emp, dept;
      두 table에서 가능한 모든 조합에 대한 결과 table을 만들어낸다.
  3. ansi join
    ansi란 'american national standards institution(미국 국립 표준 협회)' 에서 제시한 문법이다.  join condition 때문에 뚱뚱해지거나 해서 가독성이 떨어질 수 있다. 그럴 때, ansi join을 사용한다고 했다. 예시를 ansi join 문법으로 사용하면 다음처럼 작성할 수 있다. 물론 alias도 사용 가능하다.
    • inner join
      select ename, hiredate, emp.deptno, dname
from emp
inner join dept on emp.deptno = dept.deptno 
where dname = 'ACCOUNTING' ;

select ename, hiredate, deptno, dname
from emp
inner join dept using(deptno)
where dname = 'ACCOUNTING' ;
       만약 join 하는 column명이 같다면 예시의 아래처럼도 작성 가능하다.

    • outer join
      select e.empno, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno ;

select e.empno, d.deptno, d.dname
from emp e
right outer join dept d on e.deptno=d.deptno ;
       
      동일한 결과를 ansi join 방식으로 right outer join을 직접 작성하는 것으로 만들어낼 수 있다. right outer join은 오른쪽 table의 data를 기준으로 해서 왼쪽의 table을 가져다 붙이는 방식이다. data에 맞는 data라면 그대로 가져오지만, 없다면 null로 남겨둔다. left outer join은 반대이다.[각주:1]
      select e.empno, d.deptno, d.dname
from emp e
full outer join dept d on e.deptno=d.deptno ;

select e.empno, d.deptno, d.dname
from emp e
cross join dept d
      cf) full outer join이라는 것이 있다고 한다. 양쪽이 모두 기준이 될 수 있어서 해당 row들을 모두 가져오고, 부재하는 data는 null로 표기된다.

    • self join
      select e1.ename, e2.ename
from emp e1
join emp e2 on e1.mgr = e2.empno;​
      oracle join 작성법에서의 self join과 거의 비슷하다. ansi 작성법에서 table을 같게 해주는 방식으로 사용한다. 혼동의 여지가 없도록 alias를 이용하는 것을 권장한다.
    • select e1.ename, e2.ename
from emp e1
cross join emp e2;

      cf) cross join이라는 것이 있다고 한다. 따로 표기법이 존재하는 것은 아니고, 가능한 모든 조합을 결과 table로 만든다.

 

subquery

하나의 sql문에 포함된 또 다른 select 문을 말한다. 때문에 두 번 이상의 질의를 해서 얻을 수 있는 결과를 한 번의 질의로 가능하게 한다. 전체의 sql 문을 main-query 혹은 outer query 라고 하고, subquery를 inner query라고도 한다. subquery는 괄호로 반드시 묶어주어야 하고, 다음의 네 군데에 위치할 수 있고, 종류는 단일행 subquery와 복수행 subquery가 있다.

  1. select, delete, update 문의 from 절과 where 절
  2. select 문의 having 절
  3. insert 문의 into 이후
  4. update 문의 set 이후

 

단일행 subquery

subquery의 실행 결과가 하나의 column과 하나의 row만을 return해주는 query이다. 즉, 하나의 data만 return해주는 query이다.

(ex)

1. 'SMITH' 가 근무하는 부서명을 서브쿼리를 이용해서 출력해 보세요.
select dname
from dept
where deptno = (select deptno from emp where ename='SMITH');

2. ‘ALLEN' 과 같은 부서에서 근무하는 사원의 이름과 부서의 번호를 출력해 보세요.
select ename,deptno
from emp
where deptno = (select deptno from emp where ename='ALLEN'); 

3. 'ALLEN' 과 동일한 직책(job) 을 가진 사원의 사번과 이름, 직책을 출력해 보세요.
select empno, ename, job
from emp
where job = (select job from emp where ename='ALLEN');

4. 'ALLEN' 의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력해 보세요.
select ename, sal
from emp
where sal >= (select sal from emp where ename='ALLEN');

5. 'DALLAS' 에서 근무하는 사원의 이름, 부서번호를 출력해보세요.
select ename, deptno
from emp
where deptno = (select deptno from dept where loc='DALLAS');

6. 'SALES' 부서에서 근무하는 모든 사원의 이름과 급여를 출력해보세요.
select ename, sal
from emp
where deptno = (select deptno from dept where dname='SALES');

7. 자신의 직속 상관이 'KING' 인 사원의 이름과 급여를 출력해 보세요. 
select ename,sal
from emp
where mgr = (select empno from emp where ename='KING');

 

복수(다중)행 subquery

subquery의 실행 결과가 하나의 column과 여러 개의 row를 return해주는 query이다. 즉, 여러 개의 data를 return해주는 query이다. 다중행 subquery의 결과 값을 조건절에서 사용할 경우에는 반드시 in, all, any, exists와 같은 다중행 연산자와 함께 사용해야 한다.

 

(ex)

1. 급여를 3000 이상받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의
 이름과 급여, 부서번호를 출력해 보세요.
select ename,sal,deptno
from emp
where deptno in(select deptno from emp where sal>=3000);

2. in 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 사원번호, 급 
여, 부서번호를 출력해보세요. 
select empno, sal, deptno
from emp
where sal in(select max(sal) from emp group by deptno);

3. 직책이 MANAGER 인 사원이 속한 부서의 부서번호와 부서명과 부서의 위치를
출력해보세요.
select deptno, dname, loc
from dept
where deptno in(select deptno from emp where job='MANAGER');

4. 30번 부서의 사원중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는
사원의 이름과 급여를 출력해보세요.

- 단일 행 서브쿼리 -
select ename,sal
from emp
where sal > (select max(sal) from emp group by deptno having deptno=30);

- 다중 행 서브 쿼리 -
select ename,sal
from emp
where sal > all(select sal from emp where deptno=30);

5. 직책이 'SALESMAN' 보다 급여를 많이 받는 사원들의 이름과 급여를 출력하라.
 (any 연산자 이용)
select ename,sal
from emp
where sal > any(select sal from emp where job='SALESMAN');

6. 부서번호가 30번인 사원들의 급여중 최저 급여보다 높은 급여를 받는 사원의
이름, 급여를 출력해보세요.

- 단일행 서브 쿼리 -
select ename,sal
from emp
where sal > (select min(sal) from emp group by deptno having deptno=30);

- 다중행 서브 쿼리 - 
(any 연산자 사용) 
select ename,sal
from emp
where sal > any(select sal from emp where deptno=30);

7. 직책이 'SALESMAN' 인 사원의 최소 급여보다 많이 받는 사원들의 이름과 급여, 
직책을 출력하되 'SALESMAN' 은 출력하지 않습니다.
 (any 연산자 이용)
select ename, sal, job
from emp
where sal > any(select sal from emp where job='SALESMAN') and job != 'SALESMAN' ;

8. SMITH 와 동일한 직책을 가진 사원의 이름과 직책을 출력하세요
select ename, job
from emp
where job = (select job from emp where ename='SMITH');

9. 직책이 'SALESMAN' 인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들
의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. 
(all 연산자 이용)
select ename,sal
from emp
where sal > all(select sal from emp where job='SALESMAN') and deptno != 20;

10. 직책이 'SALESMAN' 인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원
들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. 
(any 연산자 이용)
select ename,sal
from emp
where sal > any (select sal from emp where job='SALESMAN') and deptno != 20;

 

넘어가기 전에...

  • table의 각 row에 차례로 순서를 부여하는 것이 가능할까? -> 'rownum'으로 가능하다. 추후 예정.
  • from 절의 subquery를 통해서 가져온 data에 rownum을 이어서 결과를 얻고 싶을 때, select *, rownum from (subquery)는 *를 인식하지 못한다. 그냥 select * from (subquery)는 가능하다. 왜 그럴까? 문법 자체가 * 다음에는 다른 column을 추가할 수 없다고 한다. 때문에 subquery에 이름을 부여해서, 예를 들면 result1 이라는 이름을 부여해서 select *.result, rownum from (subquery) result1 처럼 작성한다.
  • number라는 이름을 사용하는 것을 유의하자. 아마 number type 때문인 것 같다.

 

rowid & rownum

oracle에서 table을 생성하면 기본적으로 제공되는 column이다.

  1. rowid : row 고유의 id이다. row를 수정해도 변하지 않는다.
  2. rownum : row의 index이다. 1부터 시작하며 row 삭제시 변경될 수 있다.
    select rowid, rownum from table명;

select count(*) from table명;
select max(rownum) from table명;​
    이들은 select를 통해서 그 값을 확인할 수 있다. 그리고 만약 row의 개수를 알고 싶다면 사용할 수 있는 두 가지 방법이 있는데, 하나는 count를 사용하는 것이고, 하나는 max를 사용하는 것이다. count의 경우 실제로 count를 하고, max의 경우 이미 존재하는 column에서 max값을 찾기 때문에 후자가 속도가 빠르다.
  1. [Join] ANSI Join & Oracle Join, Join의 종류 (tistory.com) [본문으로]

'뒷북 정리 (국비 교육) > sql, db' 카테고리의 다른 글

[oracle DB] TCL (Transaction Control Language) / 기타  (2) 2021.12.17
[oracle DB] DML (Data Manipulation Language)  (0) 2021.12.17
[oracle DB] DDL (Data Definition Language)  (0) 2021.12.17
[oracle DB] DCL (Data Control Language)  (0) 2021.12.17
[oracle DB] oracle 설치 및 준비, SQL 명령어 소개  (0) 2021.12.17

관련글

댓글

티스토리툴바