ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • Oracle 쿼리 정리 [Ctrl + F 용]
    IT/DB 2019. 5. 26. 21:56

    #개요

    옛~날에 학원을 다닐때 Oracle 수업을 들으며 열심히 쿼리를 정리해뒀던 문서를 발견했다.

    오라클을 잘 쓰진 않지만, 필요로 할때 쿼리 예제를 빠르게 찾기위해 여기에 올려두려고 한다.

     

     

     


    1. SQL 기본

     

    cmd에서 실행:

    sqlplus scott/tiger

     

    cmd 한줄에서 보기:

    set linesize 150

     

    유저접속:

    conn xxxxx(계정명) /

    disc

     

    유저확인:

    show user

     

    구조보기:

    desc XXXX(테이블명)

     

    ============================

     

    1.컬럼에 산술연산 가능

    select ename, sal, sal*12 from emp;

    (널 처리 주의!) >  (x+Null = null)

     

    2. 별칭지정 (헤딩)

    select ename as "사원 이름", sal as"급여", sal*12 as "연봉" from emp

     

    3. 널처리

    함수이름(널 있는컬럼,대채값)

     nvl(comm,0)

     

    ex) select ename as"사원 이름" , sal as "급여" , (sal*12)+comm as "함수없는 보너스연봉", (sal*12)+ nvl(comm,0) as "함수포함 보너스연봉" from emp;

     

     

    4.중복된행 제거

    select DISTINCT deptno from emp;

     

     

    2. SQL 명령어

     

    조건연산자 : where

    select * from emp where sal <> 2000;

    ex) 부서번호가 10번인 사람들을 찾으시오

    select * from emp where deptno = 10;

     

    1. 비교연산자

    =, <, >, <> .....

    select * from emp where sal > 3000;

     

    2. 문자데이터 조회

    where aa  = 'X' -> 단일따옴표 안의 값을 찾음, 대소문자 구분

    select * from emp where ename = 'FORD';

     

    3.날짜데이터 조회

    날짜도 문자열과 마찬가지로 단일 따옴표 안에 기술

    select * from emp where hiredate <= '1982/01/01';

     

    4. 논리연산자

    AND , OR, NOT

     

    5. BETWEEN AND 연산자

    사이값 출력

    select * from emp where sal >= 2000 AND sal <= 3000;

    select * from emp where sal BETWEEN 2000 AND 3000;   //같은값

     

     

    6. IN연산자

    OR연산자와 같은 기능

    커미션이 300,500,1400 검색 > where comm IN(300,500,1400);

     

     

    7. like연산자와 와일드카드

     

    %는 뒤에 문자 모르겠을 때, 그 뒤로 문자 상관없이 출력

    select * from emp where ename like 'F%';

    select * from emp where ename like 'FORD%';

    select * from emp where ename like '%A%';

     

    _ %와 마찬가지로 어떤 문자든 상관 X , 그러나 단 한문자에만 해당

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

    select * from emp where ename like '__R%';

     

    NOT LIKE도 활용

     

    검색할 값중에 _문자가 있는경우 : where ename LIKE '%_%' ESCAPE '_';

     

    8. NULL

    NULL =가 아닌 is를 써야함

    select * from emp where COMM is NULL

     

    9.ORDER BY  : ASC(오름차순) / DESC (내림차순)

    where절 다음에 나옴, 생략할 시 ASC로 정렬;

    select * from emp order by sal DESC

    동일한 값의 정렬 > order by job ASC, ename ASC

     

     

    3. SQL 주요 함수

    DUAL 테이블 : 계산을 테스트해보기 위한 테이블

    1. 숫자함수

    ABS(대상) : 절대값을 구함

                 select -10 , ABS(-10) from dual;

    FLOOR(대상) : 소수점아래 버림

                 select 34.5678, FLOOR(34.5678) from dual;

    ROUND(대상, 자릿수) : 특정 자릿수에서 반올림

                 select 34.5678, ROUND(34.5678) from dual;

    TRUNC(대상, 자릿수) : 특정 자릿수에서 버림 : ROUND와 동일

                 자릿수 음수도 가능

    MOD : 나머지값 반환

                 select MOD(27,2) from dual;

     

    2.문자처리 함수

    UPPER(대상) : 입력값을 모두 대문자로 변환

    LOWER(대상) : 입력값을 모두 소문자로 변환 : 위와 동일

    INITCAP(대상) : 이니셜만 대문자로 변환하는 함수 : 위와 동일

                 select UPPER('Welcome to Oracle') from dual;

                 * 직급이 manager(소문자) 인 사원을 검색 :

                 select * from emp where LOWER(job) = 'manager';

    LENGTH(대상) : 문자 길이를 구함

    LENGTHB(대상) : 바이트 수를 알려줌

                 select LENGTH('오라클') from dual;

    SUBSTR(대상,시작위치,갯수) : 문자열 일부를 출력

    SUBSTRB(대상,시작위치,갯수) : 문자열 일부 바이트를 세서 출력

                 1 2 3 4 5 5 6 7 8 9 10...

                 시작위치를 음수로 표현할경우 뒤에서 추출 : ...-5 -4 -3 2 -1

                 select SUBSTR('Hello Oracle',2,5) from dual;

    INSTR(대상,찾을대상,x,y) : x자리부터 y번째 '대상'이 몇번짼지 추출

    INSTRB(대상,찾을대상,x,y) : 바이트를 추출

                 select INSTRB('데이터베이스','',3,1) from dual;

                 * 이름의 세번째 자리가 R로 끝나는 사원을 검색 (SUBSTR, INSTR 이용)

                 where INSTR(ename,'R',3,1) = 3; / where SIBSTR(ename,3,1) = 'R'

    LPAD/RPAD(대상,x,y) : x자리중 대상을 제외한 나머지를 y로 채움

                 select LPAD('oracle', 20,'#') from dual;

                 ex)주민번호 뒷자리를 감춤 등등...

    LTRIM/RTRIM(대상) : 대상의 왼/오른쪽 공백문자를 삭제

                 select LTRIM('    Oracle     ') from dual;

     

    3. 날짜함수

    SYSDATE : 시스템상의 날짜(오늘날짜)

                 select sysdate from dual;

                 * 근무일수 구하기

                 select sysdate-hiredate from emp;

    ROUND(대상,포멧) : 포멧모델을 참조하여 반올림

                 select ROUND(hiredate, 'MONTH') from emp;

    TRUNC(대상,포멧) : 포멧모델을 참조하여 버림

                 select hiredate, TRUNC(hiredate,'month') from emp;

    MONTHS_BETWEEN(대상1,대상2) : 두 날짜 사이 간격을 구함

                 * 근무개월수 :

                 select ename, MONTHS_BETWEEN (SYSDATE,hiredate) from emp;

    ADD_MONTHS(대상,숫자) : 개월 수를 더함

                 *오늘부터 6개월을 더함 :

                 select sysdate , ADD_MONTHS(SYSDATE, 3) from dual;

    NEXT_DAY(date,요일) : 해당 요일의 가장 가까운 날짜 반환

                 *오늘기준부터 수요일은 언제인지 :

                 select SYSDATE, NEXT_DAY(SYSDATE, '수요일') from dual;

    LATE_DAY(대상) : 해당달의 마지막 날짜 반환

                 * 입사한 달의 마지막 날짜 :

                 select HIREDATE, LAST_DAY(hiredate) from emp;

     

     

    4. 형변환함수

    4-1. TO_CHAR(날짜데이터,'출력형식') : 날짜를 문자열 형식으로 바꿈

                 날짜출력 : YYYY,YY,MM,MON,DAY,DY

                 시간출력 : AM,PM,HH12,HH24,MI,SS

                 숫자출력 : 0 9 L . ,

                 select TO_CHAR(sysdate, 'YYYY/MM/DD, HH24:MI:SS') from dual;

    4-2. TO_DATE('문자', '형식') : 문자열을 날짜형식으로 바꿈

                 select SYSDATE-TO_DATE('2008/01/01', 'yyyy/mm/dd') from dual;

    4-3  TO_NUMBER : 특정 데이터를 숫자로 변환

     

    5.NVL(컬럼,대체값) : NULL을 대체값으로 변환

                 select comm, nvl(comm,0) from emp;

     

    6. DECODE(컬럼, 조건 1, 결과 1, 조건2. 결과2, 조건3 결과3...) as 컬럼명 : 조건함수

                 select empno, ename, job, sal, decode(job,'ANAIYST',                                               sal*1.05,'SALESMAN',sal*1.1,'MANAGER',sal*1.15,'CLERK',sal*1.2)

                 as upsal from emp;

    7. CASE 표현식 WHEN 조건1 THEN 결과1

                               WHEN 조건2 THEN 결과2

                               WHEN 조건3 THEN 결과3

                               ELSE 결과n

                               END

     

    4. 그룹함수

     

    1. SUM, AVG, COUNT,MAX, MIN

                 널 제외하고 연산, 따로 널처리 안해줘도 가능

                 단일행 쿼리랑 중복사용불가

                 select COUNT(DISTINCT deptno) from emp;

    2.GROUP BY : 동일한 칼럼을 묶음

                 where절 이후에 사용

                 select avg(sal) from emp GROUP BY deptno;

    3.HAVING : 그룹의 결과를 제한하는 함수

                 부서별로 그룹 후 , 부서별 평균급여가 2000 이상인 값만 출력 :

                 select deptno, avg(sal) from emp group by deptno having avg(sal) >= 2000;

     

     

    5. 조인

    1.Cross Joine - 아무런 의미없음

                 select * from emp, dept;

     

    2.Equi Join - 일치하는 값 찾음

                 * EMP테이블의 부서번호와 DEPT 테이블의 부서명을 조인하라 :

                 select * from emp, dept where emp.deptno = dept.deptno;

                 * SCOTT의 부서명을 출력하라

                 select ename,dname,emp.deptno from emp, dept

                 where emp.deptno = dept.deptno ANd ename = 'SCOTT';

                 ## 테이블 별칭지정 : 모든 select 컬럼에 별칭소속을 명시해줘야함

                 select e. ename,d.dname,e.deptno from emp e, dept d

                 where e.deptno = d.deptno AND e.ename = 'SCOTT';

                 1). 뉴욕에 근무하는 사원의 이름과 급여를 출력

                 select ename, sal from emp, dept

                 where emp.deptno = dept.deptno AND loc = 'NEW YORK';

     

                 2). ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력

                 select ename, hiredate from emp,dept

                 where emp.deptno = dept.deptno AND dname = 'ACCOUNTING';

                 3). 직급이 MANAGER 인 사원의 이름 , 부서명을 출력

                 select ename, dname from emp,dept

                 where emp.deptno = dept.deptno AND job = 'MANAGER';

     

    3.Non-Equi Join : 특정 벙위 내에 있는지를 조사

                 *급여와 급여등급을 참조하여 출력

                 select ename,sal,grade from emp, salgrade

                 where sal BETWEEN losal AND hisal;

     

    4.Self Join : 테이블 자기 자신을 참조하여 조인

                 * self join 하여 매니저의 이름까지 출력

                 select employee.ename ,manager.ename from emp employee, emp manager

                 where employee.mgr = manager.empno;

                 1) 매니저가 KING인 사원들의 이름과 직급을 출력하시오.

                 select e1.ename, e1.job from emp e1, emp e2

                 where e1.mgr = e2.empno AND e2.ename = 'KING';

     

                 2) SCOTT과 동일한 부서에서 근무하는 사원의 이름을 출력

                 select e1.ename, e2.ename from emp e1, emp e2

                 where e1.deptno = e2.deptno

                 AND e1.ename = 'SCOTT

                 AND e2.ename <> 'SCOTT';

     

    5. Outer Join : 셀프조인시 널값도 처리

                 * 매니저 이름까지 출력시, KING의 정보까지도 출력

                 select employee.ename , manager.ename from emp employee, emp manager

                 where employee.mgr = manager.empno(+);

                 * 사원 테이블과 부서 테이블을 조인하여 사원명, 부서번호, 부서명을 출력

                 select e.ename, d.deptno, d.dname from emp e, dept d

                 where e.deptno(+) = d.deptno order by d.deptno;

     

    6. ANSI Cross Join : 영어 구문에 맞게끔 풀어서 작성되는 새로운 코드작성

                               - Full outer join 을 제외하고는 내용 중복

                 select *

                 from emp inner join dept

                 on emp.deptno = dept.deptno; -> USING (deptno)

     

     

    <예제 테이블 만들기>

    create table dept02(

    deptno number(2),

    dname varchar2(14));

    insert into dept02 values (10,'accounting');

    insert into dept02 values (30,'sales');

                

                 select * from dept01, dept02

                 where dept01.deptno = dept02.deptno; : 양쪽 (+) 못붙임

                 ------------------------------------------------

                 select * from dept01 full outer join dept02

                 using (deptno) order by deptno; : 양쪽누락정보 출력

     

     

    6. 서브쿼리 : 하나의 select 문장의 절 안에 포함된 또 하나의 select 문장

    1. 단일행 서브쿼리 : 서브쿼리의 수행결과 값이 오직하나의 로우()을 반환하는 쿼리

                 * SCOTT의 부서명을 알아내기 위한 서브쿼리문

                 select dname from dept where deptno =

                 (select deptno from emp where ename = 'SCOTT');

                 * 평균 급여보다 더 많은 급여를 받는 사원을 검색

                 select ename,sal from emp where sal >

                 (select avg(sal) from emp);

                 1. SCOTT과 같은 부서에서 근무하는 사원 이름과 부서 번호를 출력

                 select ename, deptno from emp where deptno =

                 (select deptno from emp where ename='SCOTT')

                 2. SCOTT과 동일한 직급을 가진 사원을 출력하

                 select * from emp where job =

                 (select job from emp where ename = 'SCOTT')

                 3. SCOTt과 급여가 동일하거나 더 많이 받는 사원명과 급여를 출력

                 select ename, sal from emp where sal >=

                 (select sal from emp where ename = 'SCOTT');

                 4. DALLAS에서 근무하는 사원의 이름, 부서번호를 출력

                 select ename, deptno from emp where deptno =

                 (select deptno from dept where loc = 'DALLAS');

                 5. SALES 부서에서 근무하는 모든 사원의 이름과 급여를 출력

                 select ename, sal from emp where deptno =

                 (select deptno from dept where dname = 'SALES');

     

     

                 6. 직속상관이 KING인 사원의 이름과 급여를 출력

                 select ename, sal from emp where mgr =

                 (select empno from emp where ename = 'KING');

     

    2. 다중행 서브쿼리 : 결과가 2개 이상 구해지는 쿼리문

    2-1. IN 연산자 : 서브쿼리 연산결과의 모든 값에 대한 결과출력

                 * 급여를 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원

                 select ename, sal, deptno from emp where deptno IN

                 (select DISTINCT deptno from emp where sal >= 3000);

                 7. 부서별로 가장 급여를 많이 받는 사원의 정보

                 (사원번호, 사원이름, 급여, 부서번호)를 출력하시오.(IN)

                 select empno,ename,sal,deptno from emp where sal in

                 (select max(sal) from emp group by deptno);

                 8. 직급이 MANAGER인 사람의 속한 부서의 부서 번호와 부서명, 지역을 출력

                 select deptno,dname,loc from dept where deptno IN

                 (select deptno from emp where job = 'MANAGER');

    2-2. ALL 연산자 : 서브쿼리의 검색결과와 모든 값이 일치 ( ex 최대값 )

                 * 30번 소속사원들 중에서 급여를 가장 많이 받는 사원보다

                 더 많은 급여를 받는 사람의 이름,급여를 출력

                 select ename,sal from emp where sal >

                 all (select sal from emp where deptno = 30);

                 9. 영업 사원들 보다 급여를 많이 받는 사원들의 이름과 급여와 직급을 출력하되,

                 영업사원은 출력X

                 select ename, sal, job from emp where sal >

                 all (select sal from emp where job ='SALESMAN');

    2-3. ANY 연산자 : 서브쿼리의 검색 결과와 하나이상 일치하면 참 ( ex 최소값)

                 * 부서번호가 30번인 사원들의 급여 중 가장 작은 값보다

                 많은 급여를 받는 사원 이름,급여 출력

                 select ename,sal from emp where sal >

                 any(select sal from emp where deptno = 30);

                 10. 영업 사원들의 최소 급여보다 많이 받는 사원들의

                 이름과 급여와 직급을 출력하되, 영업사원 출력X

                 select ename, sal, job from emp where sal >

                 any(select sal from emp where job = 'SALESMAN') AND job <> 'SALESMAN';

                 11. SMITH와 동일한 직급을 가진 사원의 이름과 직급을 출력.(단일행 서브쿼리)

                 select ename,job from emp where job =

                 (select job from emp where ename = 'SMITH');

                

     

     

                 12. 직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는

                 사원들의 이름,급여를 출력하되, 부서번호가 20번인 사원은 제외한다.

                 (ALL연산자 사용)

                 select ename, sal from emp where sal >

                 all (select sal from emp where job = 'SALESMAN') AND deptno != 20;

                 13.직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 많이 받는

                 사원들의 이름과 급여를 출력하되, 부서번호가 20번인 사원은 제외한다.

                 (ANY연산자 사용)

                 select ename, sal from emp where sal >

                 any (select sal from emp where job = 'SALESMAN') AND deptno != 20;

     

     

    7. DDL  테이블 자체 구조 생성,수정,삭제

    1. CREATE TABLE - 테이블 정의

    create table example( 컬럼명 데이터타입() ,...... )

                 create table emp01 (

                                            empno number(4),

                                            ename varchar(20),

                                            sal number(7,2)

                                            );

     

                 create table dept01 (

                                            deptno number(2),

                                            dname varchar2(14),

                                            loc varchar(13)

                                            );

                 1.1 서브쿼리로 테이블 생성하기 (테이블 복사)

                 create table 테이블명

                 as 서브쿼리

                 create table emp02 as select * from emp;

                 create table emp03 as select empno, ename from emp;

                 create table emp04 as select empno, ename, sal from emp;

                 1.2 (테이블 틀(컬럼명, 자료유형)만 복사)

                 create table dept02 as select * from dept where 1=0;

     

     

     

    2.ALTER TABLE - 테이블 구조변경

                 2-1. ADD : 컬럼 추가

                 * DEPT02 테이블에 문자 타입의 부서장(DMGR) 칼럼을 추가해 봅시다.

                 alter table emp01

                 add(jop varchar2(9));

                 2-2. MODIFY : 컬럼 데이터타입 변경

                 * 직급(JOB) 칼럼을 최대 30글자까지 저장할 수 있게 변경해 보도록 하자.

                 alter table emp01

                 modify(JOB varchar2(30));

                 2-3. DROP : 컬럼 삭제

                 * EMP01 테이블의 직급 칼럼을 삭제해 보도록 합시다.

                 alter table emp01 drop column job;

                 2-4. SET UNUSED : 테이블 논리적으로 사용제한

                 alter table emp02 set unused(job);

     

    3. DROP TABLE 테이블 구조 삭제

                 drop table emp02;

     

    4. TRUCATE : 테이블 틀은 놔두고 로우만 삭제

                 truncate table emp02;

     

    5. RENAME 테이블명 변경

                 rename old_name to new name;

                 rename table dept6 to dept7

                

    8. DML  테이블 내용 추가,수정,삭제

    1. INSERT  테이블 내용 추가

    insert into dept01

    (deptno, dname, loc)

    values(10,'accounting','new york');

    -모든 컬럼에 맞게 채워넣는경우, 컬럼이름 생략가능

    insert into dept01

    values(20,'accounting','korea');

                 *서브 쿼리문을 이용하여 다음과 같은 구조로 SAM01 테이블을 생성하시오.

                 create table SAM01

                 as select empno, ename, job, sal from emp where 1=0;

                 *SAM01 테이블에 다음과 같은 데이터를 추가하시오.

                 insert into SAM01

                 values ( 1030, 'BANANA','NURSE',15000);

               1.1 .NULL

                 - NULL값 입력방법 : 암시적, 명시적

                               암시적 - 컬럼명 리스트에 컬럼을 생략 > 암시적으로 null값 할당

                               명시적 - values 리스트에 null을 명시적으로 입력

                 - desc로 검색 후, 컬럼에 not null 제약조건이 있는경우 null값 입력 불가능

                 - 암시적으로 null값의 삽입

                               *지역명이 결정되지 않은 30번 부서에 부서명만 입력

                               insert into dept01

                               (deptno, dname)

                               values (30,'sales');

                 - 명시적으로 null값의 삽입

                               insert into dept01

                               values (40,'operations',null);

                               - null 대신에 ''를 사용할 수 있음

                               insert into dept01

                               values (50, '','chicago');

     

                 1.2. 서브쿼리로 데이터 삽입

                 - INSERT 명령문에서 지정한 컬럼의 개수나 데이터 타입이

                   서브 쿼리를 수행한 결과와 동일해야 함

                 1) creat table dept02 as select * from dept where 1=0;

                 2) insert into dept02 select * from dept;  - 한번에 여러개 행 등록!

     

                 *4.문제 1에서 생성한 SAM01 테이블에 서브 쿼리문을 사용하여

                   EMP 에 저장된 사원 중 10번 부서 소속 사원의 정보를 추가하시오.

                 insert into sam01

                 select empno, ename, job, sal from emp where deptno = 10;

     

                 1.3. 다중 테이블에 다중 행 입력하기

                 insert all

                 into emp_hir values (empno, ename, hiredate)

                 into emp_mgr values (empno, ename, mgr)

                 select empno, ename, hiredate, mgr from emp where deptno = 20;

     

                 - 하나의 테이블에 다중행 입력하기

                 insert all

                 into dept01 values(40,'총무부','서울')

                 into dept01 values(50,'인사부','부산')

                 select * from dual;

     

                 
                 1.4 조건에 맞게 테이블값 삽입

                 insert all

                 when profno between 1000 and 1999 then

                 into p_01 values(profno,name)

                 when profno between 2000 and 2999 then

                 into p_02 values(profno,name)

                 select profno,name from professor;

     

     

    2. UPDATE - 테이블 내용 수정

                 2.1 모든행 변경

                 * 모든 사원의 부서번호를 30번으로 수정합시다.

                               update emp01

                               set deptno = 30;

                 * 이번엔 모든 사원의 급여를 10% 인상시키는 UPDATE 문을 보겠습니다.

                               update emp01

                               set sal = sal*1.1;

                 * 모든 사원의 입사일을 오늘로 수정하려면 다음과 같이 합니다.

                               update emp01

                               set hiredate = sysdate;

                 2.2 특정행 변경

                 * 부서번호가 10번인 사원의 부서번호를 30번으로 수정합시다.

                               update emp01

                               set deptno = 30

                               where deptno = 10;

                 * 급여가 3000 이상인 사원만 급여를 10% 인상합시다.

                               update emp01

                               set sal = sal*1.1

                               where sal >= 3000;

                 *1987년에 입사한 사원의 입사일이 오늘로 수정합시다.

                 사원의 입사일을 오늘로 수정한 후에 테이블 내용을 살펴봅시다.

                               update emp01

                               set hiredate = sysdate

                               where substr(hiredate,1,2) = '87';

                 * SAM01 테이블에 저장된 사원 중 급여가 10000 이상인 사원들의 급여만

                   5000원씩 삭감하시오.

                               update sam01 set sal = sal-5000 where sal >= 10000;        

                 2.3 2개이상 컬럼값 변경

                 * SCOTT 사원의 입사일자는 오늘로, 급여를 50 으로 커미션을 4000 으로 수정

                               update emp01

                               set hiredate = sysdate, sal = 50, comm = 4000

                               where ename = 'SCOTT';

                 2.4 서브쿼리로 수정하기

                               update dept02

                               set loc= (select loc from dept02 where deptno = 40)

                               where deptno = 20;

     

                

                 * 서브 쿼리문을 사용하여 EMP 테이블의 저장된 데이터의 특정 컬럼만으로

                   구성된 SAM02 테이블을 생성 후 DALLAS 에 위치한 부서 소속 사원들의

                   급여를 1000 인상

                               update sam02

                               set sal = sal+1000

                               where deptno = (select deptno from dept where loc = 'DALLAS');

                              

                 2.5 서브쿼리를 이용해 두개 이상의 칼럼에 대한 값 변경

                 * 서브 쿼리를 이용해서 부서번호가 20인 부서의 부서명과 지역명을

                    부서 번호가 40번인 부서와 동일하게 변경하도록 해 봅시다.

                               update dept01

                               set (dname, loc) = (select dname, loc

                                                         from dept01

                                                         where deptno = 40)

                               where deptno = 20;

                 *서브 쿼리문을 사용하여 SAM02 테이블의 모든 사원의 급여와 입사일을

                   이름이 KING 인 사원의 급여와 입사일로 변경하시오.

                               update sam02

                               set (sal,hiredate) =

                               (select sal,hiredate from sam02 where ename = 'KING')

     

    2.DELETE - 테이블의 행(로우) 삭제

                 * SAM01 테이블에서 직급이 정해지지 않은 사원을 삭제하시오.

                               delete from sam02

                               where job is null;

                 * 사원 테이블에서 부서명이 SALES인 사원을 모두 삭제해봅시다.

                               delete from emp01

                               where deptno = (select deptno from dept where dname = 'SALES');

                 * SAM02 테이블에서 RESEARCH 부서 소속 사원들만 삭제하시오.

                               delete from sam02

                               where deptno = (select deptno from dept where

                                                         dname = 'RESEARCH')    

     

     

    9. 트랜젝션 관리

    1. COMMIT

                 - Transaction 작업 내용을 실제 DB에 저장

                 - 이전 데이터가 완전히 업데이트 됨

    2. ROLLBACK

                 - Transaction 작업 내용을 취소

                 - 이전 commit 한 곳 까지만 복구

     

    3. 자동 commit 혹은 ROLLBACK 되는경우

                 -sql plus가 정상 종료되었다면 자동으로 commit 되지만,

                 비정상적으로 종료되었을땐 자동으로 rollback

                 - ddl dcl 명령문이 수행된 경우 자동으로 commit (쿼리 잘못쳐도 commit)

     

    4. SAVEPOINT : 트랜젝션 분할

                 savepoint c1 / save point c2;

                 rollback to c1 / rollback to c2

     

     

    10. 무결성 제약조건

     

    <emp dept의 제약조건을 살펴보자>

    select table_name, constraint_name, constraint_type, r_constraint_name

    from user_constraints

    where table_name in('DEPT','EMP');

     

    1. NOT NULL - null을 허용하지 않는다.

                 create table emp02 (

                               empno number(4) NOT NULL,

                               ename varchar2(10) NOT NULL,

                               job varchar2(9),

                               deptno number(2) );

     

    2. UNIQUE - 중복된 값을 허용하지 않는다. 항상 유일한 값, 널 허용

                 create table emp03 (

                               empno number(4) UNIQUE,

                               ename varchar2(10) NOT NULL,

                               job varchar2(9),

                               deptno number(2) );

     

     

     

    3. PRIMARY KEY - null을 허용하지 않고 중복된 값을 허용하지 않는다.

                 - not null unique를 합친 제약조건

                 create table emp03(

                               empno number(4) CONSTRAINTS emp03_empno_pk PRIMARY KEY,

                               ename varchar2(10) CONSTRAINTS emp03_ename_nn NOT NULL,

                               job varchar2(10),

                               deptno number(2) );

     

    4. FOREIGN KEY : 참조되는 테이블의 칼럼의 값이 존재하면 안된다.

                 create table emp12 (

                               empno number(4) PRIMARY KEY,

                               varchar2(10)  NOT NULL,

                               deptno number(2) REFERENCES dept(deptno)

                 );

    5. CHECK : 저장 가능한 데이터의 값의 범위나 조건을 지정

                 create table emp13 (

                               empno number(4) PRIMARY KEY,

                               ename varchar2(10)  NOT NULL,

                               deptno number(2) REFERENCES dept(deptno),

                               sal number(7,2) CHECK(sal BETWEEN 500 AND 5000),

                               gender varchar2(1) CHECK (gender IN('M','F'))

                 );

    6. 제약조건명 사용자가 지정

                 테이블명_    칼럼명   _제약조건 유형

                 EMP04  _   EMPNO  _      UK

    create table emp06 (

                 empno number(4) CONSTRAINT emp06_empno_pk PRIMARY KEY,

                 ename varchar2(10) CONSTRAINT emp06_ename_nn NOT NULL,

                 deptno number(2) CONSTRAINT emp06_deptno_fk

                                                                       REFERENCES dept(deptno),

                 sal number(7,2) CONSTRAINT emp06_sal_ck

                                                                       CHECK(sal BETWEEN 500 AND 5000),

                 gender varchar2(1) CONSTRAINT emp06_gender_ck

                                                                       CHECK (gender IN('M','F')),

                 loc varchar2(13) DEFAULT '서울'

     );

     

     

    7. 복합키 설정

    create table member01(

                 name varchar2(10),

                 address varchar2(30),

                 hphone varchar2(16),

                 CONSTRAINT member01_combo_pk PRIMARY KEY(name,hphone)

                 );

     

    8. 제약조건 추가하기

               alter table emp01

                 add CONSTRAINT emp_empno_pk PRIMARY KEY(empno);

     

                 alter table emp01

                 add CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)

                 REFERENCES dept(deptno);

                 8-1 NOT NULL 추가하기

                               alter table emp01

                               modify ename CONSTRAINT emp01_ename_nn NOT NULL;

                 8-2 제약조건 제거하기

                               alter table emp01

                               drop constraint emp01_ename_nn;

     

     

    11. 사용자관리

    1. 사용자 생성

    CREATE USER user01 identified by 1234;

     

    2. 데이터베이스에 접속할 수 있는 권한

    GRANT create session to user01;

     

    3. 테이블 생성할 수 있는 권한

    GRANT create table to user01;

     

    4. 객체 권한부여

    scott -> grant select on emp to user01;

    user01 -> select * from scott.emp

     

     

     

     

    12. (VIEW)

    1. 뷰 만들기

                 권한부여 : grant create view to scott;

                 뷰만들기 : create view emp_view30

                                            as

                                            select empno, ename, deptno from emp_copy

                                            where deptno = 30

     

    2. 그룹연산자 이용해서 뷰 만들기

                 2.1 부서별 평균급여 뷰

                 create view view_sal

                 as

                               select deptno, sum(sal) 총급여, avg(sal) 평균급여

                               from emp_copy group by deptno;

                 2.2 부서별 최대,최소급여 뷰

                 create view MaxMin_emp

                 as

                               select d.dname 부서번호, max(e.sal) 최대급여, min(e.sal) 최소급여

                               from emp_copy e, dept_copy d

                               where e.deptno = d.deptno

                               group by d.dname;

     

    3. 뷰 기능

               3.1 CREATE OR REPLAC

                  - 존재하지 않는 않는 뷰일 경우 생성, 존재하는 뷰일 경우 변경

                 create or replace view emp_view30

                 as

                 select empno, ename, sal from emp_copy

                 where deptno = 30;

     

                 3.2 FORCE 옵션

                  - 기본 테이블 없이 뷰 생성, 일반적으로는  NOFORCE가 디폴트값

                 create or replace force view notable_view

                 as

                 select empno, ename, deptno

                 from employees

                 where deptno = 30;

                

     

     

               3.3 WITH CHECK OPTION

                - 조건 컬럼 값 변경 못하게 함

                 create or replace view emp_view30

                 as

                 select empno, ename, deptno

                 from emp_copy

                 where deptno = 30 with check option;

     

               3.4 WITH READ ONLY

                - 뷰를 통해 기본 테이블의 변경을 못하게 제한

                 create or replace view emp_view30

                 as

                 select empno, ename, deptno

                 from emp_copy

                 where deptno = 30 with read only;

                 3.5 TOP-N

                - N명 까지만 구함

                         1) ROWNUM

                                    create or replace view view_hire

                                            as

                                            select empno, ename, hiredate

                                            from emp

                                            order by hiredate;

     

                                            select rownum,empno,ename,hiredate

                                            from view_hire

                                            where rownum <= 5;

                               2) 인라인 뷰

                                    select rownum, empno, ename, hiredate

                                            from (select empno, ename,hiredate

                                                         from emp order by hiredate)

                                            where rownum <= 5;

                              

     

     

     

     

     

    13. 시퀸스

    시퀸스 : 행 구분을 위한 자동번호생성기

    생성 양식

    create sequence sequence_name

                               start witn n

                               increment by n

                              

    시퀸스 생성

    create sequence dept_deptno_seq

                               start with 10

                               increment by 10;

     

    기본키에 시퀸스 접목

    insert into emp01

    values(emp_seq.nextval, 'julia',sysdate);

    댓글

다치지 말고 운동하자.