select( join전까지)

●테이블 목록 조회하기

--현재 접속한 데이터베이스내의 테이블 조회

select * from tab;

●테이블 정보 조회하기

--원하는 테이블의 구조를 조회한다. 
desc board;

 

●모든 컬럼의 데이터 가져오기

select * from 테이블명

●특정 컬럼의 데이터 가져오기

select 컬럼명1, 컬럼명2 from 테이블명

 

 

 

●NVL

https://coding-factory.tistory.com/296

[Oracle] Null값을 치환해주는 (NVL,NVL2) 함수 사용법 & 예제

 

●CONCAT

--문자열을 합치는 연산자이다.

--문자열 || 컬럼 || 문자열 || 컬럼

EX)

--사원들의 이름과 직무를 다음 양식으로 가져온다. 
--000사원의 직무는 ㅁㅁㅁ입니다. 

 

select ename ||'사원의 직무는'||job||'입니다.' 
from emp;

 

●DISTINCT

--select문을 통해 가져온 모든 row중에서 중복된 row를 제거하는 키워드

--select distinct 칼럼명 from 테이블명

 

EX)

사원들이 근무하고 있는 근무부서의 번호를 가져온다.

select distinct deptno 
from emp;

 

●BETWEEN AND

EX)

--입사년도가 1981년인 사원중에 급여가 1500이상인 사원의 
--사원번호, 이름, 급여, 입사일을 가져온다. 
select empno, ename, sal, hiredate  
from emp  
where hiredate >= '1981/01/01' and hiredate <= '1981-12-31' and sal >= 1500; 
또는
select empno, ename, sal, hiredate 
from emp 
where hiredate between '1981/01/01' and '1981/12/31' and sal >= 1500;

 

EX)

--급여가 2000보다 크거나 1000보다 작은사원의 사원번호, 이름, 급여를 가져온다. 
select empno, ename, sal 
from emp 
where sal > 2000 or sal < 1000;  
또는
select empno, ename, sal 
from emp 
where not(sal >= 1000 and sal <= 2000); 
또는
select empno, ename, sal 
from emp 
where not(sal between 1000 and 2000); 

 

●IN 특정 칼럼안에서 조건이 여러개일때

EX)

--직무가 CLERK, SALESMAN, ANALYST인 사원의 사원번호, 이름, 직무를 가져온다 
select empno, ename, job 
from emp 
where job = 'CLERK' or job = 'SALESMAN' or job = 'ANALYST'; 
또는
select empno, ename, job 
from emp  
where job in('CLERK','SALESMAN','ANALYST');

 

--사원 번호가 7499, 7566, 7839가 아닌 사원들의 사원번호, 이름을 가져온다. 
select empno, ename 
from emp 
where empno != 7499 and empno != 7566 and empno != 7839; 

select empno, ename 
from emp 
where not(empno = 7499 or empno = 7577 or empno = 7839); 


select empno, ename 
from emp 
where empno not in(7499,7566,7839);

 

●like연산자

select 컬럼명

from 테이블명

where 컬럼명 like '와일드카드';

 

_ : 글자하나를 의미한다.

% : 글자 0개 이상을 의미한다.

EX)

--이름이 F로 시작하는 사원의 이름과 사원 번호를 가져온다. 
select ename, empno 
from emp 
where ename like 'F%'; 
--이름이 s로 끝나는 사원의 이름과 사원번호를 가져온다. 
select ename, empno 
from emp 
where ename like '%S'; 

--이름에 A가 포함되어 있는 사원의 이름과 사원 번호를 가져온다. 
select ename, empno 
from emp 
where ename like '%A%'; 

--이름의 두번째 글자가 A인 사원의 사원 이름, 사원 번호를 가져온다. 
select ename, empno 
from emp 
where ename like '_A%'; 

--이름이 4글자인 사원의 사원이름, 사원번호를 가져온다. 
select ename,empno 
from emp 
where ename like'____';

 

●null비교

-null은 정해져 있지 않은 값 혹은 무한대의 의미를 갖는 값이다.

-이 때문에  -나 !=를 통해 컬럼의 값이 null인지 연산을 할수가 없다.

-이 때, is null 이나 is not null을 통해 null 비교가 가능하다.

EX)

--사원중에 커미션을 받지 않는 사원의 사원번호, 이름, 커미션을 가져온다. 
select empno, ename, comm 
from emp 
where comm is null;

 

※주의 

select empno, ename, comm 
from emp 
where comm != null;

이렇게 하게되면 값이 안뜸. 널값은 비교를 할 수 없기 때문에.

비교할 때는 is null이나 is not null을 사용해야 한다.

 

●정렬

order by

--사원의 사원번호, 이름, 급여를 가져온다. 급여를 기준으로 오름차순 정렬을 한다, 
select empno, ename, sal 
from emp 
order by sal asc; 

select empno, ename, sal 
from emp 
order by sal; 

--사원의 사원번호, 이름, 급여를 가져온다. 사원번호를 기준으로 내림차순 정렬을 한다. 
select empno, ename, sal 
from emp 
order by empno desc; 

--사원의 사원번호, 이름을 가져온다. 사원의 이름을 기준으로 오름차순 정렬을 한다, 
select empno, ename 
from emp 
order by ename; 

--사원의 사원번호, 이름, 입사일을 가져온다. 입사일을 기준으로 내림차순 정렬을 한다. 
select empno, ename, hiredate 
from emp 
order by hiredate desc; 

--직무가 SALESMAN인 사원의 사원이름, 사원번호, 급여를 가져온다. 급여를 기준으로 오름차순 정렬을 한다, 
select ename, empno, sal 
from emp 
where job='SALESMAN' 
order by sal; 

--1981년에 입사한 사원들의 사원번호, 사원 이름, 입사일을 가져온다. 사원 번호를 기준으로 내림차순 정렬을 한다. 
select empno, ename, hiredate 
from emp 
where hiredate >= '1981/01/01' and hiredate <= '1981/12/31'  
order by empno desc; 

--사원의 이름, 급여, 커미션을 가져온다. 커미션을 기준으로 오름차순 정렬을 한다. 
select ename, sal, comm 
from emp 
order by nvl(comm,5000) asc; 

 

●정렬기준이 2개일때
--사원의 이름, 사원번호, 급여를 가져온다. 급여를 기준으로 내림차순 정렬, 이름을 기준으로 오름차순 정렬 
select ename, empno, sal 
from emp 
order by sal desc, ename asc;

 

●숫자함수

컬럼에 저장되어 있는 숫자 값에 대해 처리를 하여 값을 가져올 수 있는 함수들을 의미

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm

 

--절대값 구하기 
select -10, abs(-10) from dual; 

--전직원의 급여를 2000삭감하고 삭감한 급여액의 절대값을 구한다. 
select sal, (sal-2000),abs(sal-2000) 
from emp; 

--소수점 이하 버림 
select 12.3456, floor(12.3456) from dual; 

--급여가 1500 이상인 사원의 급여를 15%삭감한다. 단 소수점 이하는 버린다. 
select sal, sal*0.85, floor(sal*0.85) 
from emp 
where sal >= 1500; 

--반올림 
select 12.3456, round(12.3456) from dual; 

select 12.8888, round(12.8888) from dual; 

select 888.8888, round(888.8888), round(888.8888,2), round(888.8888,-2) from dual; 

--급여가 2천이하인 사람들의 급여를 20%씩 인상한다. 단 10의 자리를 기준으로 반올림한다. 
select sal, sal*1.2, round(sal*1.2,-2) 
from emp 
where sal <= 2000; 

--버림. 자리수를 정할 수 있다. 
select 1112.3456, trunc(1112.3456), trunc(1112.3456,2), trunc(1112.3456,-2) from dual; 

--전 직원의 급여를 10의자리 이하를 삭감한다. 

select sal, trunc(sal, -2) from emp; 

--나머지 구하기. 10을 3으로 나눈 나머지, 10을 4로 나눈 나머지 
select mod(10,3), mod(10,4) from dual

 

●문자열 함수

-컬럼에 저장되어 있는 문자열에 대해 처리를 하여 값을 가져올 수 있는 함수들을 의미한다.

 

--대문자 -> 소문자 
select 'abcDEF', lower('abcDEF') from dual; 

--사원들의 이름을 소문자로 가져온다. 
select lower(ename) 
from emp; 

--소문자 --> 대문자 
select 'abcDEF', upper('abcDEF') from dual; 

--첫 글자만 대문자로 나머지는 소문자로 
select 'aBCdeF', initcap('aBCdeF') from dual; 

--사원이름을 첫글자는 대문자로 나머지는 소문자로 가져온다 
select initcap(ename) 
from emp; 

--문자열 연결 
select concat('abc','def') from dual; 

select concat(concat('ccc',concat('aaa','bbb')),'ddd') from dual; 

--사원들의 이름과 직무를 다음과 같이 가져온다. 
--사원의 이름은 ㅇㅇㅇ이고, 직무는 ㅁㅁㅁ입니다. 

select '사원의 이름은'||ename||'이고, 직무는'||job||'입니다.' 
from emp; 

--문자열의 길이 length는 길이, lengthb는 길이바이트 
select length('abcd'),lengthb('abcd'),length('안녕하세요'),lengthb('안녕하세요') from dual; 

--문자열 잘라내기 
select substr('abcd',3) from dual;  --3번째 글자 이후로 가져오겠다. 오라클 문자열은 1부터 시작 

select substrb('abcd',3) from dual; 

select substr('안녕하세요',3), substrb('안녕하세요',3) from dual; 

select substr('abcdefghi',3,4),substr('동해물과 백두산이',3,4) from dual; --3번째 글자부터 4개의 글자 

--문자열 찾기 
select instr('abcdabcdabcd', 'bc') from dual;   --두번째 글자에서 'bc'가 일치한다. 따라서 출력값이 2 

select instr('abcdabcdabcd','bc',3)from dual;   --3글자 뛰고 bc가 나오는 위치 

select instr('abcdabcdabcd','bc',3,2) from dual;    --3글자 뛰고 2번째 bc가 나오는 위치 

select instr('abcdefg','aaa') from dual;    --없는 글자는 0 

--사원의 이름중에 A라는 글자가 두번째에 나타나는 사원의 이름을 가져온다. 
select ename 
from emp 
where instr(ename,'A') = 2; 

select ename 
from emp 
where ename like'_A%'; 

--사원의 이름중에 A라는 글자가 두번째 이후에 나타나는 사원의 이름을 가져온다, 


select ename 
from emp 
where instr(ename,'A') > 1; 

--특정 문자열로 채우기 
select '문자열',  
        lpad('문자열',20),      
        rpad('문자열',20), 
        lpad('문자열',20,'a'), 
        rpad('문자열',20,'a') 
        from dual; 

--공백제거 
select '    문자열    ',ltrim('    문자열    '),rtrim('    문자열    '), 
trim('    문자열    ') from dual; 

--문자열 변경 
select 'abcdefg', replace('abcdefg','abc','kkkkk')from dual;    --문자열 'abcdefg'에서 'abc'를 'kkkkk'로 바꿨다. 

 

●날짜 함수

--현재 날짜 구하기 
select sysdate from dual; 

--날짜 데이터 연산 
select sysdate, sysdate -10 from dual;      --일수를 기준으로 -10일 해준 것 

--각 사원이 입사한 날짜로 부터 1000일 후가 되는 날짜를 가져온다. 
select ename, hiredate, hiredate + 1000 
from emp; 

--직무가 SALESMAN인 사원의 입사일 100일전 날짜를 가져온다 
select hiredate, hiredate - 100 
from emp 
where job = 'SALESMAN'; 

--전 사원의 근무 일을 가져온다. 
select sysdate - hiredate 
from emp; 

--반올림 
select sysdate, round(sysdate, 'CC') as "년도 두자리", round(sysdate, 'YYYY')as "월기준", 
            round(sysdate, 'DDD') as "시기준", round(sysdate, 'HH') as "분기준",round(sysdate,'MM')as"일기준", 
            round(sysdate,'DAY')as "주기준", round(sysdate, 'MI') as "초기준" 
from dual; 

--각 사원의 입사일을 월 기준으로 반올림한다, 
select hiredate, round(hiredate, 'YYYY') 
from emp; 

--버림 
select sysdate, trunc(sysdate, 'CC') as "년도 두자리", trunc(sysdate, 'YYYY') as "월", trunc(sysdate,'DDD') as "시", 
                trunc(sysdate, 'HH') as "분", trunc(sysdate,'MM') as "일", trunc(sysdate, 'DAY') as "주", 
                trunc(sysdate, 'MI') as "초" 
from dual; 

--1981년에 입사한 사원들의 사원번호, 사원이름, 급여, 입사일을 가져온다. 
select empno, ename, sal, hiredate 
from emp 
where hiredate >= '1981/01/01' and hiredate <= '1981/12/31'; 

select empno, ename, sal, hiredate 
from emp 
where hiredate between '1981/01/01' and '1981/12/31'; 

select empno,ename, sal, hiredate 
from emp 
where trunc(hiredate,'YYYY') = '1981/01/01'; 

--두 날짜 사이의 일수를 구한다. 
select sysdate - hiredate 
from emp; 

--모든 사원이 근무한 개월 수를 구한다. 
select months_between(sysdate, hiredate) 
from emp; 

--개월 수를 더한다. 
select add_months(sysdate, 100) from dual; 

--각 사원들의 입사일 후 100개월 되는 날짜를 구한다. 
select hiredate, add_months(hiredate,100) from emp; 

--지정된 날짜를 기준으로 지정된 요일이 몇일인지... 
select sysdate, next_day(sysdate, '월요일') from dual; --오늘 날짜를 기준으로 다음 월요일이 몇일인지 

--지정된 날짜의 월의 마지막 날짜를 구한다. 
select sysdate, last_day(sysdate) from dual; 

--to_char : 주로 오라클 -> 프로그램으로 이동시킬때 
select sysdate, to_char(sysdate, 'YYYY-MM-DD HH:MI:SS AM') from dual; 
select sysdate, to_char(sysdate, 'YYYY년MM월DD일 HH시MI분SS초 AM')from dual;  --오류가 발생한다. date form에는 한글이 들어갈 수 없다. 

--to_date : 주로 프로그램 -> 오라클 : 프로그램에서 얻은 데이터를 오라클로 이동시킬 때 
select to_date('2020/08/11 11:37:30 오후', 'YYYY-MM-DD HH:MI:SS AM')from dual; 

--사원들의 입사일을 다음과 같은 양식으로 가져온다. 
--1900-10-10 
select hiredate, to_char(hiredate, 'YYYY-MM-DD')from emp;

 

●DECODE

-값에 따라 반환값이 결정되는 구문이다.

 

--각 사원의 부서 이름을 가져온다. 
--10 : 인사과, 20 : 개발부, 30 : 경영지원팀, 40 : 생산부 
select empno, ename,  
        decode(deptno, 10, '인사과',  
                        20, '개발부',  
                        30, '경영지원팀',  
                        40, '생산부') 
from emp; 

--직급에 따라 인상된 급여액을 가져온다. 
--CLERK : 10%, SALESMAN : 15%, PRESIDENT : 200%, MANAGER : 5%, ANALYST : 20% 

select ename,job, sal, decode(job,'CLERK',SAL * 1.1, 
                    'SALESMAN',SAL * 1.15, 
                    'PRESIDENT',SAL * 2, 
                    'MANAGER',SAL * 1.05, 
                    'ANALYST', SAL * 1.2 
                    ) 
        from emp; 

 

●CASE

-조건에 따라 반환값이 결정되는 구문이다

EX)

--급여액 별 등급을 가져온다. 
--1000미만 : C등급, 1000이상 2000미만 : B등급, 2000이상 : A등급 
select empno, ename, 
        case when sal < 1000 then 'c등급' 
             when sal >= 1000 and sal <2000 then 'b등급' 
             when sal >= 2000 then 'a등급' 
             end 
from emp; 

--직원들의 급여를 다음과 같이 인상한다. 
-- 1000이하 : 100%, 1000초과 2000미만 : 50%, 2000이상 : 200% 
select empno, ename, 
    case when sal <= 1000 then sal *2 
        when sal > 1000 and sal < 2000 then sal * 1.5 
        when sal >= 2000 then sal * 3 
        end 
from emp; 

 

●그룹함수

select문을 통해 가져올 결과를 그룹으로 묶고 그룹내에서 지정된 컬럼의 총합, 평균등을 구할 수 있는 함수

sum : 총합

avg : 평균

count : 로우의 수

max : 최대 값

min : 최소 값

--사원들의 급여 총합을 구한다. 
select sum(sal) 
from emp; 

select sum(sal) 
from emp; 

--사원들의 커미션의 총합을 가져온다. 
select sum(comm) 
from emp; 

--급여가 1500이상인 사원들의 급여 총합을 구한다. 
select sum(sal) 
from emp 
where sal >= 1500; 

--20번 부서에 근무하고 있는 사원들의 급여 총합을 구한다. 
select  sum(sal) 
from emp 
where deptno = 20; 

--직무가 SALESMAN인 사원들의 급여 총합을 구한다. 
select sum(sal) 
from emp 
where job = 'SALESMAN'; 

--전 사원의 급여 평균을 구한다. 
select avg(sal) 
from emp; 

--커미션을 받는 사원들의 커미션 평균을 구한다. 
select avg(comm) 
from emp 
where comm is not null; 
--그룹함수를 쓸때는 어차피 null값을 전부 제거해준다. 
select avg(comm) 
from emp;    

--전사원의 커미션 평균을 구한다. 
select avg(nvl(comm,0)) 
from emp;   --그룹함수를 그냥 쓰면 null값을 자동으로 제거하고 계산하기 때문에 커미션이 null인(커미션을 받지 않는) 사원들은 제외하고 계산하기 때문에 전사원을 대상으로 한다면 null값에 0을 넣어주도록 nvl함수를 사용해야 한다. 

--커미션을 받는 사원들의 급여 평균을 구한다. 
select avg(sal) 
from emp 
where comm is not null; 

--30번 부서에 근무하고 있는 사원들의 급여 평균을 구한다. 
select avg(sal) 
from emp 
where deptno = 30; 

--직무가 SALESMAN인 사원들의 급여 + 커미션 평균을 구한다. 
select avg(sal+comm) 
from emp 
where job = 'SALESMAN'; 

--사원들의 총 수를 가져온다. 
select count(*) 
from emp; 
------- 
select count(empno) 
from emp;   --결과 : 12 


select count(comm) 
from emp; --결과 : 4. 그룹함수는 null인 부분을 다 빼버리고 count하기 때문이다. 즉 커미션을 받는 사원들의 총 수가 된다. 

--사원들의 급여 최대, 최소값을 가져온다. 
select max(sal), min(sal) 
from emp; 

 

●Group By

-그룹 함수를 사용할 경우 select~from~where절까지 모두 수행하여 가져온 결과를 하나의 그룹으로 묶어 총합, 평균등을 구할 수 있다.

-Group By절을 사용하면 select문을 수행하여 가져온 하나의 결과를 여러 그룹으로 나눠 그룹 각각의 총합과 평균등을 구할 수 있다.

 

--각 부서별 사원들의 급여 평균을 구한다. 
select deptno, avg(sal) 
from emp 
group by deptno; 

--group by는 컬럼에서 중복된 값들을 하나의 값으로 묶는다.(위의 예에서는 deptno가 10인 애들끼리 하나의 그룹, 20인 얘들끼리 하나의 그룹, 30인 얘들끼리 하나의 그룹) 
--group by를 사용해 그룹으로 묶을 때, group by의 조건이 된 칼럼을 같이 사용할 수 있다. 

--각 직무별 사원들의 급여 총합을 구한다. 
select job, sum(sal) 
from emp 
group by job; 

--1500이상 급여를 받는 사원들의 부서별 급여 평균을 구한다. 
select deptno, avg(sal) 
from emp 
where sal >= 1500 
group by deptno;

 

●Having

-Group by로 묶인 각 그룹들 중에 실제 가져올 그룹을 선택할 조건을 having절에 작성한다.

-Having은 Group By절의 조건이 된다.

EX)

--부서별 평균 급여가 2000이상인 부서의 급여 평균을 가져온다. 
select deptno,avg(sal) 
from emp 
group by deptno 
having avg(sal) > 2000; 

--부서별 최대 급여액이 3000이하인 부서의 급여 총합을 가져온다. 
select deptno, sum(sal) 
from emp 
group by deptno 
having max(sal) <= 3000; 

--부서별 최소 급여액이 1000이하인 부서에서 직무가 CLERK인 사원들의 급여 총합을 구한다. 
select deptno,sum(sal) 
from emp 
where job = 'CLERK' 
group by deptno 
having min(sal) <= 1000; 

--각 부서의 급여 최소가 900이상 최대가 10000이하인 부서의 사원들 중 1500이상의  
--급여를 받는 사원들의 평균 급여액을 가져온다. 
select deptno,avg(sal) 
from emp 
where sal > 1500 
group by deptno 
having min(sal) >= 900 and max(sal) <=10000;

 

※where은 row의 조건, having은 group의 조건

 

 

--인프런 - 윤재성의 Oracle SQL Database 11g PL/SQL Developer