레도널스의 개발자 되어가기

 는 영문, 숫자, 공백보다 크기가 작다.

플래시백

conn / as sysdba

SELECT * FROM v$option;

WHERE parameter like 'Flashback%';  ← 플래시백 유료 옵션 확인하기

* 플래시백 실습

conn hr/hr

UPDATE employees

SET salary = salary*1.5

WHERE employee_id = 141;

commit;

SELECT salary FROM employees

WHERE employee_id = 141;

=> 롤백은 안 되지만, undo 파일에 남아있을 것임.

* 플래시백 쿼리

SELECT employee_id, salary FROM employees

as of timestamp (systimestamp-5(24*60))

WHERE employee_id = 141;

* 플래시백 버전 쿼리

col versions_starttime for a25

col versions_endtime for a25

set line 120

SELECT versions_starttime, versions_endtime, salary

FROM employees

versions between timestamp minavalue and maxvalue

WHERE employee_id = 141;

Time Zones

* 시간대

표준 시 : 그리니치(Greenwich) 천문대 시간

우리나라 : 표준 시 + 9

- sysdate : date type을 반환

- systimestamp : timestamp(date/time) type을 반환

SELECT sysdate, systimestamp FROM dual;

-> 서버 시간

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 내 시간

ALTER SESSION set time_zone = '-07:00';

-> 나의 세션 설정 바꾸기(미국 시간)

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 다시 확인해보면 날짜가 바뀌어 있다.

※ 글로벌 환경에서는 시간대 조정이 필요할 수 있다.

* 시간 데이터 타입 확인하기

실습)

CREATE TABLE timetest1

(a date,

 b timestamp,

 c timestamp with time zone,

 d timestamp with local time zone);

INSERT INTO timetest1

VALUES(sysdate, sysdate, sysdate, sysdate);

SELECT * FROM timetest1;

>> local time zone : 클라이언트의 시간대로 바꿔준다.

>> 시간 정보에서 초(sec)의 정밀도는 기본(default)값이 6, 최대 9까지 지정 가능(Max 11byte)

SELECT * FROM tab;

※ DB에 있는 테이블 보기

* 데이터 타입 변경하기

CREATE TABLE dept50

AS

SELECT employee_id, last_name, hire_date, job_id

FROM employees

WHERE department_id = 50;

SELECT * FROM dept50;

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2);  ← 바로 변경 가능

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2) with time zone;  ← with time zone 속성은 테이블에 값이 들어있으면 불가

* Interval : 간격

CREATE TABLE timetest2

(a interval year to month,

 b interval day to second);

INSERT INTO timetest2

VALUES(interval '5-10' year to month,  ← 5년 10개월

       interval '90 12:30:30' day to second);  ← 90일 12시간 30분 30초

SELECT systimestamp+a, systimestamp+b

FROM dual, timetest2;

* extract : 날짜 함수 (TO_CHAR 등으로 변환하지 않고 날짜 그대로 추출)

* TO_YMINTERVAL, TO_DSINTERVAL

* Daylight Saving Time (DST, 일광시간절약제, 서머타임)

* 고급 서브쿼리

>> Multiple-column Subquery

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE (manager_id, department_id) IN (SELECT manager_id, department_id

                                      FROM empl_demo

                                      WHERE first_name = 'John')

                                      AND first_name <> 'John';

이 문장과 아래의 문장은 다르다.

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE manager_id IN

                 (SELECT manager_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND department_id IN

                 (SELECT department_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND first_name <> 'John';

※ IN이 AND보다 우선순위가 높다.

* Scalar Subqueries: Examples

SELECT employee_id, last_name,

       (CASE

        WHEN department_id = (SELECT department_id FROM departments

                              WHERE location_id = 1800)

        THEN 'Canada' ELSE 'USA' END) location

FROM employees;

SELECT employee_id, last_name

FROM employees e

ORDER BY (SELECT department_name

FROM departments d

WHERE e.department_id = d.department_id);

* 상호 관련(Correlated) 서브쿼리

- 메인 쿼리의 행이 다 떨어질 때까지 서브쿼리를 실행하여 비교하는 방법으로 수행됨.

- 구조가 좋지 않은 대표적인 SQL 중 하나.

ex)

SELECT last_name, salary, department_id

FROM employees outer_table

WHERE salary > (SELECT AVG(salary)

                FROM employees inner_table

                WHERE inner_table.department_id = outer_table.department_id);

-> 서브쿼리가 여러 번 실행된다.

※ 성능을 좋게 하는 SQL 문장 튜닝의 예 >

SELECT e.employee_id, e.last_name, e.salary, b.avg_sal, e.department_id

FROM employees e JOIN (SELECT department_id, AVG(salary) avg_sal

                       FROM employees

                       GROUP BY department_id) b

ON (e.department_id = b.department_id)

WHERE e.salary > b.avg_sal;

-> 조건을 FROM절에 작성.

* EXISTS 연산자 : 상호 관련 서브쿼리

- 조건에 맞는 것이 발견되면 추가로 더 이상 찾지 않는다.

- 성능을 개선할 수 있음.

ex)

SELECT employee_id, last_name, job_id, department_id

FROM employees outer

WHERE EXISTS (SELECT 'X'

              FROM employees

              WHERE manager_id = outer.employee_id);

* Correlated UPDATE : 상호 관련 업데이트

실습)

SELECT * FROM dept50;

ALTER TABLE dept50

ADD salary NUMBER(10);  ← 행을 추가한 후,

UPDATE dept50 d

SET salary = (SELECT salary FROM employees e

              WHERE e.employee_id = d.employee_id);

← 데이터를 기존에 있던 테이블과 똑같이 넣고 싶을 때 사용.

* Correlated DELETE : 상호 관련 삭제

* WITH문 : 인라인뷰를 확장한 기능.

ex)

WITH

dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total

               FROM employees e JOIN departments d

               ON e.department_id = d.department_id

               GROUP BY d.department_name),

avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg

             FROM dept_costs)

SELECT * FROM dept_costs

WHERE dept_total > (SELECT dept_avg

                    FROM avg_cost)

ORDER BY department_name;

HTML을 시작합니다.

* 메모장 저장 시 파일명을 " (큰 따옴표)으로 감싸면, txt 확장자가 붙지 않는다.

※ 메모장에서 작성 시 <meta charset="UTF-8"> 부분 때문에 글자가 깨진다.

* Constraint

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');

ALTER TABLE dept

DROP primary key;  <- 삭제가 되지 않는다.

ALTER TABLE dept

DROP primary key CASCADE;  <- 옵션을 추가해 준다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 본다.

ALTER TABLE dept

ADD primary key(department_id);

ALTER TABLE emp

ADD CONSTRAINT emp_deptid_fk foreign key(department_id)

REFERENCES departments(department_id);  <- 제약 조건을 다시 만들고,

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 보면, enabled 상태.

INSERT INTO dept

VALUES(10, 'CS', null, null);

UPDATE emp

SET department_id = 300

WHERE employee_id = 176;  <- 두 문장 모두 오류 발생

ALTER TABLE dept

DISABLE primary key;

ALTER TABLE emp

DISABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건을 끌 수 있다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 확인해 보면, 두 제약 조건은 disable 상태.

제약 조건을 disable하고, 테이블에 새로운 데이터를 입력 후 enable할 때 문제가 있으면 enable되지 않는다.

문제가 있는 해당 데이터(중복 등)를 삭제 후 enable 해주어야 한다.

DELETE FROM dept

WHERE department_name = 'CS';

UPDATE emp

SET department_id = 30

WHERE employee_id = 176;  <- 데이터 갱신 후

ALTER TABLE dept

ENABLE primary key;

ALTER TABLE emp

ENABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건 enable

* Cascading Constraints

연쇄 제약 조건

@c:\oraclexe\labs\cre_test1.sql  <- TEST1 테이블 생성

DESC test1;

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name = 'TEST1';

ALTER TABLE test1

DROP COLUMN col2;  <- 삭제가 된다.

ALTER TABLE test1

DROP COLUMN col1;  <- 삭제가 안 된다.

ALTER TABLE test1

DROP COLUMN col1

CASCADE CONSTRAINTS;  <- 옵션 추가하여 삭제.

DROP TABLE test1;  <- 테스트 끝났으니 테이블 삭제.

* DROP TABLE ... PURGE : 영구 삭제

DROP TABLE emp;

SELECT * FROM tab;

SHOW RECYCLEBIN;  <- 휴지통 보기

FLASHBACK TABLE emp TO BEFORE DROP;  <- 복원

DESC emp;

DROP TABLE emp PURGE;  <- 완전 삭제, FLASHBACK 불가.

PURGE RECYCLEBIN;  <- 휴지통 비우기

※ 오라클의 휴지통 특징

- 파일 이동이 없다. 따라서, 속도가 빠르다.

* Temporary Tables : 임시 테이블

- 장바구니 개념

CREATE GLOBAL TEMPORARY TABLE emp_temp1

ON COMMIT DELETE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

CREATE GLOBAL TEMPORARY TABLE emp_temp2

ON COMMIT PRESERVE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

< 재접속 >

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;  <- 테이블에 아무것도 없다.

INSERT INTO emp_temp1

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 50;

INSERT INTO emp_temp2

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 60;  <- 데이터 삽입

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;

COMMIT;

SELECT * FROM emp_temp1;  <- 테이블이 비워진다.

SELECT * FROM emp_temp2;  <- 테이블의 내용이 유지된다.

※ 임시 테이블은 세션 범위를 벗어나지 못한다.

* External Tables : 외부 테이블

- 자주 사용하지는 않지만, 버리지는 못하는 자료를 빼두는 용도로 사용

  ex) 은행 거래내역 등

- Oracle 9i부터 사용

- DML 불가

- 부득이하게 수정이 필요하면, 파일을 직접 수정

실습)

• SQL*Plus에서 권한 주기

> conn / as sysdba

Connected.

SYS> CREATE DIRECTORY dir_1

AS 'c:\oraclexe\test';

Directory created.

SYS> GRANT read, write ON DIRECTORY dir_1 TO hr;

Grant succeeded.

• SQL문 실행

@C:\oraclexe\labs\cre_empxt.sql;

DESC empxt;

SELECT * FROM empxt;

Manipulating Large Data Sets

생략

★ Oracle의 Flashback 기술에 대하여

 - 빠르게 취소하는 기능(commit이 된 것)

 - 과거에는 못 했는데 가능하도록 추가된 기능.

• Flashback Query - 9i ver.~             ┐
• Flashback Versions Query               │→ Undo data 읽기 방식(영구적이지 않음)
• Flashback Transaction Query - Undo SQL ┘
• Flashback Table ($)
• Flashback Data Archive ($) - 11g ver.~
• Flashback Undrop - Recyclebin
• Flashback Database ($)

※ ($) 표시는 유료 옵션

* Synonym : 동의어

>> 먼저, 권한 주기

Command Line에서 :

SQL> conn / as sysdba

Connected.

SQL> GRANT create synonym TO hr;

Grant succeeded.

>> 돌아와서,

CREATE SYNONYM e

FOR employees;

SELECT * FROM e;

>> Synonym 삭제

DROP SYNONYM e;

SQL 1 끝.

SQL 2 시작.

Controlling User Access

• System 권한 : DB System에 ~를 할 수 있는 권한.
  

- CREATE TABLE

- CREATE VIEW

- CREATE SYNONYM

• Object 권한 : 계정 간의, ...의 ~를 Access할 수 있는 권한.

- SELECT 

- UPDATE

* System 권한

• 100개가 넘는다.

  Access    ON Schema.object

• Select    ON scott.emp
• Insert
• Update
• Delete
• Alter
• Index
• Reference

• Role 생성하기 :

• Role에 권한 추가 :

• Role에서 권한 제거 :

• 현재 사용자의 권한 확인하기 :

• 암호 바꾸기

• CONNECT
• RESOURCE
• DBA

소프트웨어 패키지 관리 (계속)

3. 파일 압축 관리

1) tar (Tape ARchive)

• 파일과 디렉토리를 하나의 파일로 묶어주는 아카이브(archive)를 생성해주는 도구
• 원래 테이프 장치에서 아카이브를 생성하기 위해 설계
• 압축 기능이 없는 것은 아니지만, 단순히 파일들을 꾸러미 형태로 묶는 것이 주된 기능

• 사용법

- 형식

$ tar Function + Options  아카이브명.tar  디렉토리/파일

- Function

• c : 새로운 아카이브 생성
• x : 아카이브 해제
• u : 아카이브 비교하여 새로운 파일 추가
• t : 아카이브에 있는 파일 확인

- Options

• f : tar 아카이브 파일 지정
• v : 아카이브 생성 및 해제시 정보 출력
• z : tar.gz 형태로 압축 또는 해제 (확장자는 붙여줘야 함)
• j : tar.bz2 형태로 압축 또는 해제 (확장자는 붙여줘야 함)

• cf 옵션 (생성 및 파일 지정)

• v 옵션 (아카이브 생성 과정 보임)

• x 옵션 (해제)

• r 옵션 (새로운 디렉토리와 파일 추가)

• -C 옵션 (특정 경로에 아카이브 해제)

※ tar에서 가장 중요한 것, cvf와 xvf

• z 옵션 (gzip 유틸리티로 압축)

2) gzip & gunzip

• gzip 유틸리티는 파일을 압축하고 푸는 데 사용되는 GNU 압축 유틸리티
• gzip에 의해서 압축되는 파일은 .gz 확장자

3) bzip2 & bunzip2

• gzip보다 높은 압축률, gzip에 비해 압축 해제 속도는 느림.

4. RPM(Redhat Package Manager)을 이용한 패키지 관리

1) 명령

• -i 패키지명 : 설치
• -U 패키지명 : 업그레이드
• -e 패키지명 : 제거
• -q 패키지 목록 : 질의
• -V 패키지 목록 : 검증
• -y 패키지 목록 : 검증
• -b 패키지명 : 패키지 생성

5. YUM(Yellowdog Updater Modified)을 이용한 패키지 관리

1) RPM의 패키지 의존성 문제 해결

- RPM 패키지 설치 시 자동으로 의존성 문제 처리

- RPM 패키지를 안전하게 설치, 제거, 업그레이드

# yum install <패키지 명>

>> ON DELETE CASCADE

>> ON DELETE SET NULL

ex) 쇼핑몰에서 물건을 구매한 이력이 있는 회원이 탈퇴했을 때, 구매 이력은 남겨놓고 회원 정보만 삭제할 때 사용.

* 테이블 만들기에서 서브쿼리 사용

CREATE TABLE dept80

AS

SELECT employee_id, last_name, salary*12 ANNSAL, hire_date

FROM employees

WHERE department_id = 80;

※ 계산식 컬럼을 만들 때 별칭은 필수

* ALTER TABLE

>> READ ONLY / READ WRITE

* Dropping a Table

>> DROP TABLE dept80;

Creating Other Schema Objects

* What Is a View? : 뷰란?

한 테이블에서 내용을 제한적으로 보여주는 테이블

물리적으로 저장되지 않음.

* Creating a View

>> 뷰를 생성하기 전에, 생성할 수 있는 권한을 부여해준다.

SQL> conn / as sysdba

Connected.

SQL> GRANT create view TO hr;

Grant succeeded.

>> 권한 부여 후, 뷰를 생성한다.

CREATE VIEW empvu80

AS

SELECT employee_id, last_name, salary*12 ann_sal, hire_date

FROM employees

WHERE department_id = 80;

* 뷰 조회하기

SELECT view_name, text FROM user_views;

* 뷰 수정하기

>> 오라클에서는 CREATE OR REPLACE VIEW 문장을 사용.

   다른 DBMS에서는 ALTER TABLE를 사용하기도 함.

CREATE OR REPLACE VIEW empvu80

AS

SELECT employee_id, last_name, salary, hire_date

FROM employees

WHERE department_id = 80;

* View에서의 DML 명령어

>> DELETE → 심플 뷰에서는 대부분 가능

- 제약이 가장 적음.

- 무조건 다 되는 것은 아님.

ex)

DELETE FROM empvu80

WHERE employee_id = 176;

>> UPDATE → 대부분 가능

- 그 외 불가능한 조건은 DELETE 명령과 비슷함.

- 표현식으로 된 컬럼은 불가능.

ex)

UPDATE empvu80

SET salary=salary*1.2;

>> INSERT → 불가능

- 제약 조건 때문에 불가.

* 뷰 삭제

>> DROP VIEW view;

※ 뷰 만들기 실습

* Sequences : 시퀀스

시퀀스란?

- UNIQUE 값을 생성해주는 오라클 객체.

- 보통 PRIMARY KEY 값을 생성하기 위해 사용.

- 메모리에 Caching 되었을 때 시퀀스 값의 액세스 효율이 증가한다.

- 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

- 한 번 사용한 번호를 재활용하지 않는다.

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10

START WITH 200

NOCACHE

NOCYCLE;

SELECT * FROM departments;

INSERT INTO departments

VALUES(dept_deptid_seq.nextval, 'Support', null, 2500);  ← 다음에 사용할 시퀀스 번호 확인

ROLLBACK;

INSERT INTO departments

VALUES(dept_deptid_seq.nextval, 'Customer Support', null, 2500);

SELECT * FROM departments;

SELECT dept_deptid_seq.currval FROM dual;  ← 현재까지 사용한 시퀀스 번호 확인

CREATE SEQUENCE emp_empid_seq

INCREMENT BY 1

START WITH 415;

INSERT INTO employees

VALUES(emp_empid_seq.nextval, 'Jason', 'Kim', 'JKIM', '053.1236.1276', sysdate, 'IT_PROG', 6700, null, null, 60);

SELECT * FROM employees;

COMMIT;

>> 시퀀스 정보 보기

SELECT sequence_name, last_number

FROM user_sequences;

>> 시퀀스 수정하기

ALTER SEQUENCE

>> 시퀀스 삭제하기

DROP SEQUENCE

* Indexes : 색인

검색 성능을 향상시키기 위한 것.

>> Full Table Scan 방식과의 차이

=> Index 검색 방식

- ID와 Rowid(Pointer)를 함께 저장

- 밸런스 트리 구조

  Root - Branch - Leaf

- 자료가 많을 수록 효과가 좋음.

>> 인덱스가 자동으로 만들어지는 경우

- PRIMARY KEY 또는 UNIQUE : 중복 체크용

- 제약 조건 조회

SELECT table_name, constraint_name, constraint_type

FROM user_constraints

WHERE constraint_type IN ('U', 'P')

ORDER BY 1;

- 유니크 인덱스 조회

SELECT table_name, index_name, uniqueness

FROM user_indexes

ORDER BY 1;

- 자동으로 만들어지는 인덱스는 삭제 불가

>> 수동으로 인덱스 만들기

CREATE INDEX

※ BITMAP INDEX(비트맵 인덱스) : 0 or 1

* SQL 자동 추적

SQL> conn / as sysdba

Connected.

SQL> GRANT SELECT ANY DICTIONARY to HR;

Grant succeeded.

-> 권한 부여 후,

SELECT * FROM employees

WHERE employee_id = 176;

※ SQL Development의 자동 추적 기능(F6 키) → cost가 더 높을수록 Index를 만들어 주면 검색에 유리하다.

>> Sequence 만들기 실습

CREATE TABLE bigemp

AS

SELECT * FROM employees;

INSERT INTO bigemp

SELECT * FROM bigemp;

commit;

SELECT COUNT(*) FROM bigemp;

CREATE SEQUENCE bigemp_id_seq

INCREMENT BY 1

START WITH 1

NOCYCLE

NOCACHE;

UPDATE bigemp

SET employee_id = bigemp_id_seq.NEXTVAL;

>> Index 만들기 실습

CREATE INDEX bigemp_id_ix  

ON bigemp(employee_id);  ← employee_id의 index를 만듦.

CREATE INDEX bigemp_sal_ix

ON bigemp(salary);  ← salary의 index를 만듦.

SELECT * FROM bigemp

WHERE salary = 24000;

SELECT * FROM bigemp

WHERE salary*12 = 288000;

* salary의 index를 만들었으나, salary를 이용한 연산식을 조회할 때는 사용하지 않는다.

- 해결 방법 : 함수 기반 index를 만든다.

CREATE INDEX bigemp_annsal_ix

ON bigemp(salary*12);

SELECT * FROM bigemp

WHERE salary*12 = 288000;

5. 계정 사용자 정보 변경

1) usermod

-c 내용 : 사용자의 설명 필드 변경

-d 홈 디렉토리 : 사용자의 홈 디렉토리 변경

-e 만료일 : pw의 만료 일자 변경

-G 그룹 : 사용자의 그룹 변경

-s 기본 쉘 : 사용자의 로그인 쉘 변경

-u UID : 사용자의 UID 변경

-l 사용자명 : 사용자의 계정명 변경

[root@localhost ~]# usermod -s /bin/test ydvt5

[root@localhost ~]# 

[root@localhost ~]# cat /etc/passwd | grep ydvt5

ydvt5:x:1006:1008::/users/ydvt5:/bin/test

6. 사용자 계정 바꾸기

1) su (Substitude User)

7. 그룹 관리

1) 그룹 생성

# groupadd [-g gid] [-r] [-f] 그룹명

-g : 그룹의 고유 ID

-r : 그룹의 ID를 500 이하의 값으로 자동 지정

-f : 강제로 그룹 추가

2) 자신이 소속된 그룹 확인

# groups

3) 그룹 삭제

# groupdel [그룹명]

4) 그룹 속성 변경

# groupmod [-g gid] [-n 변경될 그룹명] 기존 그룹명

-g : 그룹의 고유 ID

-n : 변경될 그룹명

소프트웨어 패키지 관리

1. 개요

>> 리눅스 소프트웨어 형태

- tar라는 아카이브 도구에 의해서 묶여진 소스 코드 형태

- RPM 도구로 만들어진 RPM 바이너리 형태

>> tar

- 원하는 옵션을 부여하여 바이너리 파일을 만들어 설치 가능

- 바이너리 패키지가 배포되지 않은 최신 버전의 소프트웨어를 설치하거나 업데이트할 때 유용

- 모든 UNIX 계열 OS에서 사용 가능

>> RPM

- 레드햇 리눅스사에서 개발한 소프트웨어 패키지 관리 도구

- 누구나 쉽게 소프트웨어를 설치할 수 있는 장점

- 다른 패키지와 의존성 문제를 가지기 때문에 의존성 패키지가 설치되지 않은 경우 설치하는 불편함이 존재

>> YUM, APT

- RPM과 같은 의존성 문제 해결

- 미지의 RPM 패키지를 설치하고자 할 경우 RPM 도구보다 선호

2. 패키지 확장자 종류

※ 윈도우 시스템처럼 자동으로 확장자가 붙는 것이 아니라, 사용자가 확장자명을 지정