04강

SQL

 

• INDEX

  • B*Tree INDEX로 기본 저장

    • 비교 후 작은 수는 왼쪽 크면 오른쪽으로 데이타를 저장

  • 필요성

    • 검색을 빠르게 한다.
    • SORT 하지 않고도 SORT된 정보를 얻을 수 있다.

  • INDEX를 사용하지 못하는 경우

    1. WHERE no != 100
    2. WHERE no IS NULL
    3. WHERE SUBSTR(job_id, 1,2) = 'ST'

• Synonyms(동의어, 별명)

  • Private Synonym

    1. select * from employees;
    2. create synonym emp for employees;
    3. select * from emp;

  • Public Synonym(주의 할점 : 내게도 같은 이름의 table이가 있다면 내가를 참조함.)

    1. userA : GRANT select on employees TO userB;
    2. userB : SELECT * FROM userA.employees;
    3. DBA : CREATE PUBLIC SYNONYM emp FOR userA.employees;
    4. userB : SELECT * FROM emp;
• Privileges
• Roles

• 딕셔너리

  • select * from session_privs (현재 User의 권한을 보여줌)

• FlashBack (테이블을 되살려 주세요)

  • drop table pop;   (테이블 삭제)
  • show recyclebin;   (휴지통 보기)
  • Flashback Table pop To Before Drop;   (테이블 되살리기)
  • select * from pop;   (조회하면...잘 나옴)
  • drop table pop purge;   (휴지통 안거치고 삭제됨)

• Internal table

  • Meta data + Row 모두 db내부에 저장됨

• External table

  • Meta data 는 db내부에 저장하고 Row는 os에 파일로 저장한다.
  1. CREATE TABLE exp_load
       (emp_id number(5), emp_name VARCHAR2(5))
       ORGANIZATION EXTERNAL
         (TYPE ORACLE_LOADER
         DEFAULT DIRECTORY dpump_files
         ACCESS PARAMETERS
           (RECORDS FIXED 10
           FIELDS TERMINATED BY ","
             (emp_id CHAR(5), emp_name CHAR(5))
           )
         LOCATION('exam1.dat')
       )

• INSERT ALL

  1. INSERT ALL
       INTO sal_history VALUES(empid, hiredate, sal)
       INTO mgr_history VALUES(empid, mgr, sal)
     SELECT employee_id empid, hire_date hiredate,
       salary sal, manager_id mgr
     FROM employees
     WHERE employee_id > 200;

  • INSERT FIRST OR ALL TEST

    • FIRST = (첫번째 조건이 성립된다면 그놈만 INSERT하고 다른건 안넣지롱)
    • ALL = (조건만 맞으면 다 넣는다.)

  1.  

     • CREATE TABLE special_sal
       AS
       SELECT department_id deptid, salary sal
       FROM employees WHERE 1=2
     • CREATE TABLE hiredate_history_00
       AS
       SELECT department_id deptid, hire_date hiredate
       FROM employees WHERE 1=2
     • CREATE TABLE hiredate_history_99
       AS
       SELECT department_id deptid, hire_date hiredate
       FROM employees WHERE 1=2
     • CREATE TABLE hiredate_history
       AS
       SELECT department_id deptid, hire_date hiredate
       FROM employees WHERE 1=2
     • INSERT FIRST
           WHEN sal > 25000 THEN
             INTO special_sal VALUES(deptid, sal)
           WHEN hiredate LIKE('%00%') THEN
             INTO hiredate_history_00 VALUES(deptid, hiredate)
           WHEN hiredate LIKE('%99%') THEN
             INTO hiredate_history_99 VALUES(deptid, hiredate)   
           ELSE
             INTO hiredate_history VALUES(deptid, hiredate)
         SELECT department_id deptid, sum(salary) sal,
           MAX(hire_date) hiredate
         FROM employees
         GROUP BY department_id
     • ----------------------------------END---------------------------------

• MERGE

  • 있는 데이타는 UPDATE 되고 없으면 INSERT 된다.
  • 조인에 참여한 COLUMN은 업데이트 할 수 없다.
• Rollup(a, b, c)

1.  

   • group by a, b, c

     union

     group by a, b

     union

     group by a

     union

     group by ()

   • rollup(a, b,..., n) n+1개의 group by 절의 union이다.

• cube(a, b, c)

1.  

   • group by a, b

     union All

     group by a

     union All

     group by b

     union All

     group by ()

• Regexp_like 사용법

1.  

   • select * from employees
     where regexp_like (last_name, '([aeiou])\1', 'i')

 

 

 

 

 

이 글은 스프링노트에서 작성되었습니다.