190527_DAY33 decode, case , join
DQL(데이터 조회,검색,출력)
- select
DQL문법)
1. select 컬럼명 2. from 테이블명 3. where 조건식 4. group by 컬럼명 5. having 조건식 6. order by 컬럼명;실행순서) 2- 3- 4- 5- 1(DB로 부터의 조회,인출)- 6 - 1(정렬된 결과를 출력)
select distinct 조회컬럼명1 [as] 별명, 컬럼명2 || 컬럼명3, 데이터, 산술연산, 함수호출(예: length(ename)), 그룹함수호출(count(),sum(),max(),min(),avg()) from 테이블명 where 조건식 --------- 컬럼명 연산자 데이터 ------- 비교연산자(> < >= <= = != ^= <>) like '패턴%_' in (value_list) 예) deptno in (10,20,30) ==> deptno = 10 OR deptno = 20 OR deptno = 30 ename in ('ADAMS','MILLER') ==> ename = 'ADAMS' OR ename = 'MILLER' between 최소값 and 최대값 예) sal >= 2000 and sal<=3000 sal between 2000 and 3000 ==> 2000이상~ 3000이하 sal not between 2000 and 3000 ==> 2000미만, 3000초과 not sal between 2000 and 3000 ==> 2000미만, 3000초과 = null (실행결과 없음) is null (컬럼의 값이 null과 같다면) is not null (컬럼의 값이 null과 같지않다면) <부정연산자> not 조건식 not (조건식1 or 조건식2) not in not like not between 최소값 and 최대값 group by (그룹묶을수 있는)컬럼명 예) group by deptno ===> (10번부서의 사원들) (20번부서의 사원들) (30번부서의 사원들) having (그룹에 대한) 조건식 ===> group by와 반드시 함께 사용!! ===> deptno = 10 : 3개의 그룹중 (10번부서의 사원들만) avg(sal) >= 2000 : 10번,20번,30번 부서 각각의 평균 급여를 비교 ---- ---- ---- (사원3명 급여 더하기/3) (사원5명 급여 더하기/5) (사원6명 급여 더하기/6) order by (정렬할 )컬럼명 [ASC|DESC]; ---------- 생략하면 ASC(오름차순) 기본값 컬럼명1, 컬럼명2 ------ ------- 1차정렬 1차정렬결과에 대한 2차정렬DML(데이터 조작 - 추가, 수정, 삭제)
- insert , update , delete
DDL(데이터[객체-table,view,sequence,trigger] 정의) : Auto Commit
create, alter, drop, rename, truncate
emp테이블명 ----> employee테이블명
구조변경(컬럼수 변경-emp테이블에 email컬럼을 추가 -ename 15byte를 30byte로 변경)
DCL
- DB관리자가 일반사용자에게 권한 부여, 권한 회수
------- ------- grant revoke
- DB관리자가 일반사용자에게 권한 부여, 권한 회수
TCL
논리적인 (DML)작업단위: Transaction
DB에 물리적인 반영, 논리 작업 전체 취소, 부분 작업 취소
commit rollback savepoint(저장점)
DECODE 함수
형식
DECODE(컬럼명, 비교데이터1, 결과데이터1 비교데이터2, 결과데이터2......)사원들의 사원번호(empno), 사원명(ename), 부서번호(deptno), 부서명(dname)을 출력하시오
select empno, ename, deptno, decode(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATIONS') 부서명 from emp; EMPNO ENAME DEPTNO 부서명
7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING문제) 사원들의 사원번호(empno), 사원명(ename), 부서번호(deptno), 부서명(dname),
사원번호에 대한 홀수 또는 짝수를 출력하시오 ==> decode함수 사용!!select empno, ename, deptno, decode(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATIONS') 부서명, decode( mod( empno,2 ), 0, '짝수', 1, '홀수') 홀짝 from emp; EMPNO ENAME DEPTNO 부서명 홀짝 ---------- -------------------- ---------- -------------------- ------------ 7369 SMITH 20 RESEARCH 홀수 7499 ALLEN 30 SALES 홀수 7521 WARD 30 SALES 홀수 7566 JONES 20 RESEARCH 짝수 7654 MARTIN 30 SALES 짝수 7698 BLAKE 30 SALES 짝수 7782 CLARK 10 ACCOUNTING 짝수 7788 SCOTT 20 RESEARCH 짝수 7839 KING 10 ACCOUNTING 홀수 7844 TURNER 30 SALES 짝수 7876 ADAMS 20 RESEARCH 짝수 7900 JAMES 30 SALES 짝수 7902 FORD 20 RESEARCH 짝수 7934 MILLER 10 ACCOUNTING 짝수
<CASE함수>
형식
CASE WHEN 조건식1 THEN 결과데이터1 WHEN 조건식2 THEN 결과데이터2 WHEN 조건식3 THEN 결과데이터3 ELSE 결과데이터n END문제
사원들의 사원번호(empno), 사원명(ename), 부서번호(deptno), 부서명(dname), 사원번호 에 대한 홀수 또는 짝수를 출력하시오 => CASE문 사용 SELECT empno, ename, deptno, CASE WHEN deptno = 10 then 'ACCOUNTING' WHEN deptno = 20 then 'RESEARCH' WHEN deptno = 30 then 'SALES' WHEN deptno = 40 then 'OPERATIONS' ELSE 'OPERATIONS' END "부서명", CASE WHEN mod(empno, 2) = 0 then '짝수사원번호' ELSE '홀수사원번호' END "짝홀" FROM EMP;
★<조인> JOIN
한개 이상의 테이블에서 데이터를 조회하기 위해 사용
두개 이상의 테이블을 참조해서 데이터를 검색
from 뒤에 두 개 이상의 테이블을 명시
예)
- FROM emp, dept
- FROM emp,emp
문제)
SCOTT 사원이 근무하는 부서의 부서번호와 부서명, 부서위치를 출력하시오. SELECT deptno, dname, loc FROM emp WHERE ename = 'SCOTT'; SELECT deptno, dname, loc FROM dept WHERE deptno = 20; => SCOTT사원이 근무하는 부서명은 'RESEARCH' 이고 'DALLAS'에 위치하고 있습니다. 이건 하나로 합쳐서! SELECT ename, dname FROM emp, dept; // 14행 8열 * 14행 3열 = 56행 11열 // 결과행 : 56행 , 특정한 조건을 주지 않는다면 조인시 각 테이블이 갖는 행의 수를 곱한 형태의 결과가 출력 from emp,edpt,salgrade; 로 하면 14 *4 * 5 select ename, deptno, dname, loc from emp, dept; //ORA-00918: column ambiguously defined 라는 에러 발생, emp와 dept가 공통적으로 deptno를 갖고 있기 때문 select emp.ename, emp.deptno, dept.dname, dept.loc from emp, dept; select ename, emp.deptno, dname, loc from emp, dept; ==> 56행 4열 출력 ----- 사원1명 과 부서4개행을 매핑
select ename, emp.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno;
==> 14행 4열 출력
----
사원1명 과 부서 1개행을 매핑
select ename, emp.deptno, dname, loc
from emp, dept
where ename = 'SCOTT'
and
emp.deptno = dept.deptno; 문제) MILLER사원이 근무하는 부서정보를 아래와 같이 출력하시오.
==> MILLER사원은 10번 부서에 근무하고 부서명은 ACCOUNTING이고 위치는 NEW YORK입니다.
select ename, emp.deptno, dname, loc
from emp, dept
where ename = 'MILLER'
and
dept.loc = 'NEW YORK';
<EQUI JOIN>
- 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이
일치하는 행을 연결하여 결과를 생성하는 조인.
※ 참고 : select * from emp,dept;
===> 특별한 조건없이 두 테이블을 곱(14행 * 4행)의 형태로 출력: CROSS JOIN
문제) 'NEW YORK'에서 근무하는 사원의 이름과 급여를 출력하시오.
```sql
select ename, sal,dept.deptno, loc
from emp, dept
where loc = 'NEW YORK'
and
emp.deptno = dept.deptno;
select ename, hiredate
from emp, dept
where dname = 'ACCOUNTING'
and emp.deptno = dept.deptno;문제) ACCOUNTING부서 소속 사원들의 이름과 입사일(YYYY/MM/DD)을 출력하시오.
select ename, hiredate, emp.deptno, DNAME
from emp, dept
where DNAME = 'ACCOUNTING'
and
emp.deptno = dept.deptno;문제) 직급이 MANAGER인 사원의 이름, 부서명을 출력하시오.
select ename, hiredate, JOB , emp.DEPTNO
from emp, dept
where JOB = 'MANAGER'
and
emp.deptno = dept.deptno;특정 범위내에 있는 값을 조회하기 위해 (대소)비교연산자를 사용.
문제
각 사원의 사원번호, 사원명, 급여, 급여등급(1~5등급)을 출력하시오 select empno, ename, sal, grade from emp, salgrade -- 14* 5 = 70행!! where sal between losal and hisal; -- 14행!! -- sal >= losal and sal <= hisal
문제) ALLEN사원의 사원번호, 사원명, 급여, 급여등급, 부서명을 출력하시오.
select empno, ename, sal, grade, dname
from emp, salgrade, dept
where ename = 'ALLEN'
and
emp.deptno = dept.deptno
and
sal between losal and hisal;
------
<SELF JOIN>
- 마치 같은 테이블이 두개인 것처럼 자기 자신의 테이블과 조인하는 것.
- 문제
SCOTT사원의 사원번호, 사원명, 급여, 직속상사명을 출력하시오
select my.empno, my.ename, my.sal, mgr.ename
from emp my, emp mgr
where my.ename = 'SCOTT'
and
my.mgr = mgr.empnil;
SCOTT사원과 동일한 근무지(부서)에서 근무하는 사람(동료사원)의 이름을 출력
select e1.ename 기준사원명, e2.ename 동료사원명
from emp e1, emp e2
where e2.ename <> 'SCOTT'
and
e1.ename = 'SCOTT'
and
e1.deptno = e2.deptno;
------
<outer Join>
- 테이블 조인 중 한쪽 테이블에 존재하지 않는 데이터로 인해서
출력되지 않는 행을 출력하고 싶을 때 사용.
- '(+)'기호 사용
- 문제
전체사원)각 사원의 이름과 그사원의 직속 상사이름을 출력하시오.
select e1.ename 사원명, e2.ename 직속상사명
from emp e1, emp e2
where e1.mgr = e2.empno; -- 예상 출력행의 수: 14
===> 13행 출력( 전체사원수는 14명!! ---> e1.mgr이 null인 KING사원은 배제)
각 사원의 이름과 부서번호, 부서명, 부서위치를 출력하시오
select ename, d.deptno, dname, loc
from emp e, dept d
where e.deptno = d.deptno;
14명 사원에 매핑되지 않은 부서정보(40)도 함께 출력하고 싶다.
select ename, d.deptno, dname, loc
from emp e, dept d
where e.deptno (+)= d.deptno;
<Join관련 문제>
```sql
--문제1) 사원들의 이름, 부서번호, 부서이름을 출력
select ename, emp.deptno, dept.dname
from emp, dept
where emp.deptno (+)= dept.deptno;
--문제2) 부서번호가 30번인 사원들의 이름, 직급, 부서번호, 부서위치를 출력
select ename, job, emp.deptno, dept.loc
from emp, dept
where dept.deptno = 30
and
emp.deptno (+)= dept.deptno;
--문제3) 커미션을 받는 사원의 이름, 커미션, 부서이름, 부서위치 출력(커미션 0은 제외)
select ename, comm, dname, loc, emp.deptno
from emp, dept
where comm != 0
and
comm is not null
and
emp.deptno = dept.deptno;
--문제4) DALLAS에서 근무하는 사원들의 이름, 직급, 부서번호, 부서이름을 출력
select ename, job, emp.deptno, dname
from emp, dept
where loc = 'DALLAS'
and
emp.deptno = dept.deptno;
--문제5) 사원이름에 'A'가 들어가는(포함하는) 사원들의 이름과 부서이름을 출력
select ename, dname
from emp, dept
where ename like '%A%'
and
emp.deptno = dept.deptno;
--문제6) 사원이름과 직급, 급여, 급여등급을 출력
select ename, job, sal, grade
from emp, dept, salgrade
where emp.deptno = dept.deptno
and
sal between losal and hisal;
--문제7) 사원들의 이름과 그 사원과 같은 부서에 근무하는 사원의 사원명, 부서번호를 출력
--===> 자기 이름을 제회한 동료(56행) 정보 출력!!
select e2.ename "동료명", e1.ename "사원명", e2.deptno
from emp e1, emp e2
where e2.deptno = e1.deptno
and
e1.empno ^= e2.empno;
ANSI 조인 = DBMS의 종류와 상관없이 실행되는 표준 조인
from 테이블명1, 테이블명2
-> 콤마 제거
select empno, ename, dname
from emp, dept;
---> 14행 * 4행 : 56행
select empno, ename, dname
from emp cross join dept;
---> 56행
- 테이블 사이에 inner join사용
동등 조건식 ON(조건절!!) 뒤에 사용 - where절을 추가하는 것이 가능
- on절은 반드시 추가!!
- on절에 동등비교와 추가적인 조건식을 기술 가능.
문제) 전체 사원에 대한 사원번호, 사원명, 부서명을 출력.
select empno, ename, dname
from emp inner join dept;
---> ORA-00905: missing keyword (on조건절을 명시!!)
select empno, ename, dname
from emp inner join dept
on emp.deptno = dept.deptno;
EMPNO ENAME DNAME
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7566 JONES RESEARCH
7902 FORD RESEARCH
7876 ADAMS RESEARCH
7369 SMITH RESEARCH
7788 SCOTT RESEARCH
7521 WARD SALES
7844 TURNER SALES
7499 ALLEN SALES
문제) JAMES사원에 대한 사원번호, 사원명, 부서명을 출력하시오.
select empno, ename, dname
from emp inner join dept
on emp.deptno = dept.deptno -- 공통 컬럼 매핑
where ename='JAMES';
EMPNO ENAME DNAME
7900 JAMES SALES<Inner Join에 USING절 사용하기>
형식
USING(공통컬럼명) select empno, ename, dname from emp inner join dept USING (deptno);-- 14행출력 ※USING절 사용시 공통된 컬럼에 대해 emp.deptno 또는 dept.deptno 사용 불가!! ---> 특정 테이블이 아닌 USING절에서 정의한 deptno컬럼명만 사용!! select empno, ename, emp.deptno, dname -- 에러발생 from emp inner join dept USING (deptno); select empno, ename, deptno, dname -- 실행OK from emp inner join dept USING (deptno);
<.Natural Join>
select empno, ename, dname
from emp natural join dept; --두 테이블이 갖는 컴럼 중 공통된 이름의 컬럼을 동등조건해줌
--문제) ADAMS의 사우너번호, 사원명, 부서명을 출력
select empno, ename, dname
from emp natural join dept
where ename = 'ADAMS';<.ANSI Outer Join>
형식
FROM 테이블명1 (LEFT | RIGHT | FULL) [OUTER] JOIN 테이블명2 --조건절은 ON!! 문제) 각 사원의 이름과 그 사원의 직속 상사이름을 출력하시오 select e1.ename "사원명", e2.ename "직속상사명" from emp e1 left join emp e2 on e1.mgr = e2.empno; --mgr에는 null 있고 empno에는 null 없다. 문제) 각 사원의 이름과 부서번호, 부서명, 부서위치를 출력하시오 (14명 사원이 속하지 않은 40번 부서에 대해서 출력) select ename, dept.deptno, dname, loc --여기서 deptno은 emp.deptno 쓰면 누락 속성 생김. from emp right join dept --right outer join으로 풀네임 써도 됨. on emp.deptno = dept.deptno; -- dept에 null값 있음
delete와 truncate, drop의 차이
- delete는 데이터를 지우는것이지 테이블 용량은 줄어들지 않는다
- truncate는 용량이 줄어들고 인덱스들도 모두 삭제, 테이블은 삭제 않고 데이터만 삭제 ( 삭제 후 되돌릴 수 없다.)
- dtop은 테이블 전체를 삭제 공간, 객체를 삭제 (삭제 후 되돌릴 수 없다.)'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글
| 190529_DAY35, <복습>, <DDL>, <DML> (0) | 2019.05.29 |
|---|---|
| 190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작 (0) | 2019.05.28 |
| 190524_Day32 DB3 복습 및 진도 (0) | 2019.05.24 |
| 190523_Day31 DB2 (0) | 2019.05.23 |
| 190522_Day30 DB(Oracle)-SQL (0) | 2019.05.22 |