[Oracle] UPDATE/DELETE __개발공부 35일차

subquery 

 

subquery란?

ㄱ. 하나의 SQL 문장의 절에 부속된 또 다른 select 문장으로, 
    두 번의 질의를 수행해야 얻을 수 있는 결과를 한 번의 질의로 해결할 수 있는 문장이다.

ㄴ.  > >= < <=      + 오른쪽 ( 서브쿼리 )     
     IN, SOME, ANY

ㄷ. 질의가 미지정된 값을 근거로 할 때 유용하다.  
ㄹ. subquery는 mainquery의 조건으로 사용된다.
     WHERE 조건절  안에   (서브쿼리 )                   
ㅁ. subquery의 결과는 main outer query에 의해 사용된다. 
ㅂ. subquery의 실행 순서는 subquery를 먼저 실행하고, 그 결과를 mainquery에 전달하여 실행한다.
ㅅ.  WHERE , HAVING, INSERT  INTO 절, UPDATE SET 절, SELECT , DELETE FROM 절에서 서브쿼리를 사용할 수 있다.
ㅇ. 서브쿼리는 ORDER BY 절을 포함할 수 없다. ( 예외, 인라인 뷰에서는 사용할 수 있다. )

 

 

 

Oracle 함수

 

1) 기능  2) 매개변수  3) 리턴값(리턴자료형)

ㄱ. NVL( expr1, expr2) , NVL2( , , )  ==>널값을 처리하는 함수 *** 

ㄴ. SUBSTR( 문자열,         postition                    [, length] )
                             0 , 1  첫번째 문자부터
                             -n 음수  뒤에서부터 n번째

ㄷ. UPPER( 컬럼명 또는 문자열 )
ㄹ. TO_CHAR( 날짜형 , 'RRRR' 또는 'YYYY') 년도          '2010'   '01'     -> 문자열로 반호나
     EXTRACT( YEAR FROM hiredate ) 년도                  2010     1
ㅁ. REGEXP_LIKE ()
ㅂ. CONCAT()      ||
ㅅ. MOD() 나머지 구하는 함수                   % X

 

 

 

--WHERE REGEXP_LIKE( ssn, '^7\d{5}-[13579]' ) 

 

 

레코드 추가하기

 

[형식]
     INSERT INTO 테이블명 ( 컬럼명,,, ) VALUES ( 컬럼값,,, );
     COMMIT;

 

 INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50, 'QC100%T', 'SEOUL' );

 

 

*레코드 추가시 흔히 발생하는 오류

-- 부서 추가       50 ,  아름다운부서 , 서울
INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50, '아름다운부서' , '서울');
오류 보고 -
ORA-12899: value too large for column "SCOTT"."DEPT"."DNAME" (actual: 18, maximum: 14)

DESC dept;
DNAME           VARCHAR2(14)   14바이트 문자열   한글 4문자 

한글 6문자 == 18바이트
한글 1문자 ==  3바이트

-- VSIZE() 함수
SELECT VSIZE('아')  -- 3바이트
, VSIZE('아름다운부서') -- 18바이트
, VSIZE('a')          -- 1바이트   알파벳 대소문자    한 문자는 1바이트    
FROM dual;

 

 

 

레코드 수정하기

 

[형식]
     UPDATE 테이블명
     SET    수정할컬럼=수정할값,수정할컬럼=수정할값,수정할컬럼=수정할값,,,
     [WHERE 조건식];

 

 

UPDATE dept
     SET    dname = 'SA%LES'
     WHERE  deptno = 30;   -- WHERE 조건절이 없으면 모든 행(레코드)가 수정..
     COMMIT;

 

 

UPDATE dept
     --SET dname = 'SALES'
     SET dname = REPLACE( dname, '%', '')
     WHERE deptno = 30;

COMMIT;

 

 

문제) dept 테이블에서 부서명에   'r' 문자열 포함하면 부서번호를 1증가 시키는 쿼리 작성하세요..

 

 UPDATE dept
       SET deptno = deptno + 1      -- 11/21/31/41
       WHERE REGEXP_LIKE( dname , 'r' , 'i' );

 

 

 

레코드 삭제하기

 

 

[형식]
       DELETE FROM 테이블명 -- WHER 조건절이 없으면  모든 레코드(행) 삭제
       WHERE   조건식;  loc   PK

 

 

parent          deptno (참조)              child
      dept 테이블                    emp 테이블
      부모테이블의 부서번호            자식테이블의 부서번호(외래키,FK) 컬럼에서 참조

     관계형 데이터 모델
     
     개체  - 관계(연관성)-    개체
     릴레이션
     테이블
     부모테이블               자식테이블
     dept                        emp
     deptno(PK)               deptno 참조키, 외래키, FK
                                  empno PK 고유키

 

 

-- [ RR과 YY 기호의 차이점 ] --
    '97/01/12'       '03/12/21'
RR   1997/01/12      2003/12/21
YY   2097/01/12      2003/12/21
         2000년대(SYSDATE)

 

 

문제) 현재 시스템의 날짜/시간 정보를 얻어오는 코딩.
JAVA : Date d = new Date();       d.toLocalString()
       Calendar c = Calendar.getInstance();  c.toString()

Oracle :   SYSDATE  함수   
--  2000년대 /  21세기
SELECT SYSDATE , TO_CHAR( SYSDATE, 'CC')
FROM   dept;  -- 4개의 행(레코드)

 

 

dual

 

 

 ***dual 이란 ? ***
1) SYS 관리자계정이 소유하고 있는 테이블( 오라클 표준 테이블 )
2) 행 1개 , 컬럼 1개인 dummy 테이블
    DESC dual;
    DUMMY 컬럼    VARCHAR2(1) 
3) 일시적으로 날짜연산, 산술연산할 때 자주 사용.

SELECT SYSDATE, CURRENT_DATE  -- 22/04/07 22/04/07
         , CURRENT_TIMESTAMP
FROM sys.dual;

4) 스키마.테이블명( sys.dual ) -> PUBLIC 시노님( sysnoym )설정했기 때문에
SELECT SYSDATE
FROM dual;
5) dual 테이블은 오라클 설치하면 자동으로 만들어지는 테이블이다.

오라클 함수 :  루트4 == 2 == sqrt(4)         

 

 

 

SYNONYM 

시노님( SYNONYM ) 이란?
1) HR 접속해서  
   SELECT *
   FROM emp;

2) [PUBLIC] 생략하면 Private 시노님
【형식】
CREATE [PUBLIC] SYNONYM [schema.]synonym명
   FOR [schema.]object명;

3) PUBLIC 시노님은 모든 사용자가 접근 가능하기 때문에 생성 및 삭제는 오직 DBA만이 할 수 있다.

4)  PUBLIC 시노님의 생성 순서
 1단계 : SYSTEM/SYS 권한으로 접속한다.

 2단계 : PUBLIC 옵션을 사용하여 시노님을 생성한다.

 

CREATE PUBLIC SYNONYM arirang FOR scott.emp;
--SYNONYM ARIRANG이(가) 생성되었습니다.

 3단계 : 생성된 시노님에 대해 객체 소유자로 접속한다. 
 4단계 : 시노님에 권한을 부여한다.
           GRANT SELECT ON emp  TO HR;

-- Grant을(를) 성공했습니다.

(5단계) : 시노님 삭제하기

DROP PUBLIC SYNONYM arirang;
--SYNONYM ARIRANG이(가) 삭제되었습니다.

 

MOD() / REMAINDER () 

MOD()와 REMAINDER () 함수의 차이점

 

둘다 나머지를 구하는 함수이지만 MOD는 소수점을 절삭하고 REMAINDER 는 소수점을 반올림한다.

 

MOD         : n2 - n1 * FLOOR (n2 / n1)    절삭함수

REMAINDER : n2 - n1 * ROUND (n2 / n1)    반올림함수

SELECT MOD(5,2)           -- *****
          ,REMAINDER(5,2)
FROM dual;

 

 

SET(집합) 연산자

 

1) UNION 연산자     - 합집합
SELECT deptno, empno, ename, job  
FROM emp
WHERE deptno = 20
UNION
SELECT deptno, empno, ename, job  
FROM emp
WHERE job = 'MANAGER';

-- 3명
20 7566 JONES MANAGER  ***
30 7698 BLAKE MANAGER
10 7782 CLARK MANAGER

-- 3명
20 7369 SMITH CLERK
20 7566 JONES MANAGER  ***
20 7902 FORD ANALYST
 

2) UNION ALL 연산자                - 합집합 + ALL (중복가능)

3) INTERSECT 연산자                 - 교집합

4) MINUS 연산자                      - 차집합

 

 

 

 

-- [ 오라클 연산자 ] 
-- [ 오라클 함수   ]
-- [ 오라클 자료형 ]
         LOB 자료형
         CLOB 자료형

1) 비교 연산자 : where 절에서 사용               숫자, 날짜, 문자    true, false,  null
                >  >=  <  <=   != ^=  <>   =

SELECT 3 > 5 
FROM dept;      
    - LOB 자료형은 비교연산자를 사용할 수 없지만, PL/SQL에서는 CLOB 데이터를 비교할 수 있다.
    - ANY, SOME, ALL   SQL 연산자  X

2) 논리 연산자  : where 절에서 사용   AND, OR, NOT 연산자       true, false, null

3) SQL 연산자  :    [NOT] IN ( list )
                   [NOT] BETWEEN a AND b
                   [NOT] LIKE
                   IS [NOT] NULL
    ANY, SOME, ALL  SQL연산자 + 비교연산자
    
    NOT EXISTS          SQL연산자       where      ( 상관(Correlated )서브쿼리  존재하면 true 반환)
    == NOT IN

4) NULL 연산자   - IS NULL,   IS NOT NULL

5) 연결 연산자     ||              CONCAT()

6) 산술 연산자   +  -  *   /             나머지 함수 MOD()