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

* TRUNCATE : WHERE절 없는 DELETE 명령처럼 모든 행을 삭제

  - 차이점은 DDL이기 때문에 자동 COMMIT.

SELECT * FROM sales_reps;

DELETE FROM sales_reps;

ROLLBACK;

※ DELETE는 ROLLBACK이 가능하다.

TRUNCATE TABLE sales_reps;

ROLLBACK;

※ TRUNCATE는 ROLLBACK이 불가능하다.

=> Undo를 사용하지 않기 때문에, 속도도 매우 빠르다.

* 자동 커밋/롤백

* SAVEPOINT는 ROLLBACK 시 사용, COMMIT하면 SAVEPOINT는 날아감.

* C:\oraclexe\app\oracle\oradata\XE 내의 파일 정보

* 데이터베이스는 아파트(또는 다세대 주택)에 비유될 수 있음.

* CREATE TABLE : 테이블 생성하기

실습)

CREATE TABLE member

(id        NUMBER(4),

 name      VARCHAR2(20),

 phone_no  VARCHAR2(11),

 email     VARCHAR2(40),

 join_date DATE);

DESC member;

INSERT INTO member

VALUES(1001, 'Jisang', '01012345678', 'wona23@gmail.com', sysdate);

SELECT * FROM member;

* 오라클에서 사용 가능한 데이터 타입

※ 이진 데이터(RAW, LONGRAW)는 용량 때문에 사용 시 주의

※ Bfile은 응용 프로그램에서 많이 쓰이는 형태

※ LOB 타입 이전에 사용된 LONG 타입(LONG, LONGRAW)의 데이터는 용량이 커서 테이블을 따로 저장해서 사용했었음.

- 테이블을 따로 만들고, 필요할 때 Join해서 사용.

※ LOB 타입은 링크 정보만 저장, 실제 데이터는 별도의 저장소에 저장하는 방식.

- Indexing 기법

* ROWID

ex)

SELECT employee_id, last_name, rowid

FROM employees;

>> ROWID 의미

-  6        3          6           3

  객체 | File No | Block 번호  | 행 번호

- ROWID는 일종의 Pseudo Column

- Primary Key보다 더 정확하게 중복 데이터를 식별할 수 있음

* DEFAULT Option : 기본 옵션

ex)

CREATE TABLE dept

       (deptno      NUMBER(2),

        dname       VARCHAR2(14),

        loc         VARCHAR2(13),

        create_date DATE DEFAULT sysdate);

INSERT INTO dept(deptno, dname)

VALUES(10, '관리부');

SELECT * FROM dept;

* Including Constraints : 제약 조건

- NOT NULL

- UNIQUE

>> NULL도 가능(NULL은 각각 UNIQUE하므로)

- PRIMARY KEY(PK)

>> 비교적 짧고, 숫자만으로 된 것이 가장 좋음. (주민등록번호보다는 학번)

- FOREIGN KEY(FK)

- CHECK

>> Foreign Key와 Check는 남발하면 그 자체로 제약 조건이 될 수 있어서, 최근에는 응용 프로그램 코드로 대체되는 경우가 많음. (리스트에서 선택하는 방식)

※ Bold는 많이 사용

실습)

CREATE TABLE member

(id NUMBER(4) Primary Key,   ← Column Level 제약 조건

name VARCHAR2(20) not null,  ← Column Level 제약 조건

phone_no VARCHAR2(11) CONSTRAINT member_phone_uk UNIQUE,  ← Column Level 제약 조건

email VARCHAR2(40),

join_date DATE,

CONSTRAINT member_email_uk UNIQUE(email)  ← Table Level 제약 조건

);

DESC member;

=> 제약 조건이 나타나지 않는다.

※ 제약 조건 확인 방법 >>

실습)

SELECT table_name, constraint_name, constraint_type

FROM user_constraints

WHERE table_name = 'MEMBER';  ← table_name은 반드시 대문자로 작성해야 함.

시스템 명령어(계속)

* 파이프 (|) : 한 명령의 출력을 다른 명령의 입력으로 리디렉션

ex) ls -al | sort | more

* df (Disk File system) : 하드 디스크의 용량이 얼마나 남아있는지 확인

>> df [옵션]

-a : 디폴트로 생략된 0 블록 크기의 파일 시스템도 보여줌

-i : 파일 크기를 블록 대신 inode로 디스크 정보를 보여줌

-k : 파일 크기를 킬로바이트(kb)로 보여줌

-T : 파일 시스템의 종류와 함께 디스크 정보를 보여줌

-t : fstype 값을 가진 파일 시스템에 대한 디스크 정보

-h : 쉬운 용량 표시 단위로 표기 (예:1G, 25M 1K)

* du (Disk Usage) : 사용한 디스크 용량에 대한 정보 제공

>> du [옵션][디렉토리명]

-a : 해당 경로에 대한 사용한 디스크 용량 표시

-k : 용량 표시 단위를 킬로 바이트(kb)로 표시

-m : 용량 표시를 메가 바이트(MB)로 표시

* find : 원하는 파일 위치 찾기

-amin n : n분 전에 마지막으로 액세스된 파일

-atime n : n*24시간 전에 마지막으로 액세스된 파일

-cmin n : n분 전에 마지막으로 변경된 파일

-ctime n : n*24시간 전에 마지막으로 변경된 파일

-empty : 비어있는 파일이나 디렉토리

-name <pattern> : Pattern으로 주어진 파일

-newer 파일 : “파일”보다 최신의 파일

* grep (Global Regular Expression Print) : 지정된 파일에 대해서 일치하는 라인 출력

>> grep [파일명(들)]

ex) cat /etc/passwd | grep fedora

2. 시스템 상태 확인 명령

1) 프로세스(Process)의 개념

* 프로세스(Process) : 주 기억장치에서 운영체제에 의해 실행 중인 프로그램

* 프로세스 번호 : 시스템에서 자동적으로 프로세스 식별번호(PID : Process Identifier Number)를 할당

>> 0 ~ 30,000개

2) 프로세스 상태 확인 명령

* ps (Process Status) : 프로세스의 상태 확인

>> ps [옵션][인수]

-a : All, 사용자의 단말기에 관련된 모든 프로세스의 상태정보 표시

-j : “job” 형식으로 표시

-l : Long, 자세하게 모든 상태 정보를 표시

-u 사용자명 : User, 기술된 사용자의 상태 정보를 표시

-s : 시그널 포맷으로 표시

-m : 메모리 정보 표시

-x : 터미널 제어 없이 프로세스 현황 표시

※ ps 옵션은 -(하이푼) 잘 안 붙이고 사용.

* kill : 프로세스 종료

>> kill [옵션] PID(들)

-1, -HUP : 프로세스를 재활성화

-9 : 프로세스 강제 kill

ex) ps ax | grep cupsd

3) 그 외 시스템 상태 확인 명령

* finger

- 시스템 사용자의 개인 정보를 나타내는 명령

- 상대방에게 메일을 보내기 위하여 메일을 수신할 사람의 login 명을 찾는 용도로 사용

- 여러 명이 사용하는 Multi-User Mode에서 다른 사용자에 대한 정보를 알려주며, who 명령보다는 정보들이 자세하게 나타남

ex) $ finger –s fedora

>> finger 옵션

-s : 간단한 형식으로 출력

-l : 자세한 형식으로 출력

-p : 설정된 계획을 제외하고 출력시킬 때 사용

>> finger의 필드와 의미

Login : login 할 때의 사용자 id

Name : 사용자의 이름

TTY : 터미널 이름

Idle : 아무 일도 하지 않는 시간

When : login한 시간

Where : 원격 시스템에서 login한 경우 원격 시스템의 호스트 이름

* tty : 단말기명 확인

* whereis : 명령어의 경로명 확인

>> whereis [명령어]

* time : 명령어의 실행 시간 측정

>> time [명령어]

* ping : 원격지 호스트의 동작 여부 확인

>> ping [원격지 호스트명]

사용자 계정 관리

1. 계정(account)의 개요

>> 건물명 Linux

>> 504호 주택 

- 계정 생성 (adduser)

- 주소 : /home/504

- 소유권 이전

2. 사용자 계정 생성

1) adduser

# adduser(or useradd) 계정명

ex)

# adduser ydvt

# cat /etc/passwd | grep ydvt  ->  /etc/passwd 파일에서 ydvt 키워드와 일치하는 줄 출력

2) adduser 옵션

-c 설명 : 패스워드 파일에 새로운 사용자 설명 추가

-d 디렉토리 : 새 계정에 대한 디렉토리 위치 지정

-e 유효기간 : 지정된 날짜에 사용자의 계정 삭제

-f 비활성일수 : 패스워드가 만기된 후 계정이 영구히 말소될 때까지의 시간

-u uid : 사용자의 ID 지정

-s shell : 사용자의 로그인 shell 지정

-n : 사용자 계정 추가 기본 모드를 지정하지 않을 때

-G [그룹, …] : 사용자를 기본 그룹 이외의 다른 그룹에 추가할 때

ex)

# adduser ydvt1 -c "Developer Course in Yedam"

실습)

[root@localhost ~]# adduser ydvt1 -c "Developer Course in Yedam"

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

ydvt1:x:1002:1002:Developer Course in Yedam:/home/ydvt1:/bin/bash

계정명|패스워드|PID|          설명          | 계정 위치 |  쉘(shell)

# mkdir /users

# adduser –d /users/ydvt2 ydvt2

# cat /etc/passwd | grep ydvt2

ydvt2:x:1003:1003::/users/ydvt2:/bin/bash

※ /users라는 디렉토리가 없는 상태에서 /users/ydvt2 디렉토리를 ydvt2 사용자의 홈 디렉토리로 지정하면 계정이 생성되지 않음

실습)

[root@localhost ~]# mkdir /users

[root@localhost ~]# adduser -d /users/ydvt2 ydvt2

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

ydvt2:x:1003:1003::/users/ydvt2:/bin/bash

# adduser ydvt3 –u 530 –d /users/ydvt3

실습)

[root@localhost ~]# adduser ydvt3 -u 530 -d /users/ydvt3

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

ydvt3:x:530:1004::/users/ydvt3:/bin/bash

# adduser ydvt4 –u 540 –d /users/ydvt4 –s /bin/false

실습)

[root@localhost ~]# adduser ydvt4 -u 540 -d /users/ydvt4 -s /bin/false

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

ydvt:x:1001:1001::/home/ydvt:/bin/bash

ydvt1:x:1002:1002:Developer Course in Yedam:/home/ydvt1:/bin/bash

ydvt2:x:1003:1003::/users/ydvt2:/bin/bash

ydvt3:x:530:1004::/users/ydvt3:/bin/bash

ydvt4:x:540:1005::/users/ydvt4:/bin/false

* 새로 생성된 계정 디렉토리에 있는 숨김 파일은 계정 추가시 /etc/skel 디렉토리 내에 있는 파일들이 사용자 홈 디렉토리로 복사됨.

3) adduser 명령 기본 설정값 변경

* 사용자 생성 명령어의 기본 설정값 확인

# useradd -D     (= adduser -D)

실습)

[root@localhost ~]# adduser -D -b /users

[root@localhost ~]# 

[root@localhost ~]# useradd -D

GROUP=100

HOME=/users

INACTIVE=-1

EXPIRE=

SHELL=/bin/bash

SKEL=/etc/skel

CREATE_MAIL_SPOOL=yes

# adduser –D –s /bin/false

※ shell을 false로 바꿔버리면, 접속(로그인) 불가...

실습)

[root@localhost ~]# adduser -D -s /bin/false

[root@localhost ~]# 

[root@localhost ~]# adduser -D

GROUP=100

HOME=/users

INACTIVE=-1

EXPIRE=

SHELL=/bin/false

SKEL=/etc/skel

CREATE_MAIL_SPOOL=yes

3. 사용자 계정 삭제

1) userdel

# userdel ydvt4

/users/ydvt5 디렉토리는 삭제되지 않음

# cd /users

# rm –r ydvt5

# userdel –r ydvt5

* 계정 서비스 보류 및 재서비스

- /etc/passwd의 두 번째 필드 값 ‘*’표시

- 재서비스할 경우 ‘*’ 문자 삭제

* 계정 발급하더라도 로그인 불가능

- /etc/passwd의 /bin/bash  →  /bin/false (or /sbin/nologin)

4. 계정 패스워드 지정 및 변경

1) passwd 파일 구조

/etc/shadow 파일에 암호화된 형태로 안전하게 저장.

2) passwd 변경 (시스템 관리자)

실습)

[root@localhost users]# adduser ydvt5

[root@localhost users]# passwd ydvt5

ydvt5 사용자의 비밀 번호 변경 중

새  암호:

잘못된 암호: The password is shorter than 8 characters

새  암호 재입력:

passwd: 모든 인증 토큰이 성공적으로 업데이트 되었습니다.

3) passwd 변경 (사용자)

실습)

[root@localhost users]# su - ydvt5

[ydvt5@localhost ~]$ passwd

ydvt5 사용자의 비밀 번호 변경 중

ydvt5에 대한 암호 변경 중  

(현재) UNIX 암호:

새  암호:

새  암호 재입력:

passwd: 모든 인증 토큰이 성공적으로 업데이트 되었습니다.

* 사용자가 패스워드 변경할 경우 유의사항

- 숫자로만 지정 불가

- 사전적인 쉬운 단어로 지정 불가

- 6자 이상

- 이전에 사용하던 패스워드 지정 불가

- 여러 문자와 숫자 조합이 가장 좋음

4) Shadow Password System

실습)

[ydvt5@localhost ~]$ su -

암호:

[root@localhost ~]# pwunconv

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

root:$6$lsRpERiSxLnCFuWm$.2z9ktq5DGoIwNdoSsHvyZ9kWu3MsASJBwyZOUsEBvY05iBxH0w.y6DmH26Xs5tw7oSr2EmY2eSbL7WEth/P0.:0:0:root:/root:/bin/bash

operator:*:11:0:operator:/root:/sbin/nologin

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

cat: /etc/shadow: 그런 파일이나 디렉터리가 없습니다

[root@localhost ~]# pwconv

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

root:$6$lsRpERiSxLnCFuWm$.2z9ktq5DGoIwNdoSsHvyZ9kWu3MsASJBwyZOUsEBvY05iBxH0w.y6DmH26Xs5tw7oSr2EmY2eSbL7WEth/P0.:17679:0:99999:7:::

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

root:x:0:0:root:/root:/bin/bash

operator:x:11:0:operator:/root:/sbin/nologin

5) 패스워드 관리 (chage)

# chage [옵션] [설정 내용] 사용자 계정

-l : chage 설정 내용 확인

-m : 새로운 pw를 변경할 수 있는 최소 일 수, 0은 언제나 변경 가능

-M : 유효한 pw의 최대 일수

-W : 사용자가 pw를 바꿀 때까지 경고할 일 수

-I : pw 유효기간이 경과된 후 사용자의 계정이 비활성화될 일 수

-E : 사용자가 계정을 사용할 수 있는 유효기간 (연도/월/일)

-d : 사용자가 pw를 변경한 마지막 일자

실습)

[root@localhost ~]# chage -l ydvt5

마지막으로 암호를 바꾼 날 : 5월 28, 2018

암호 만료 :안함

암호가 비활성화 기간 :안함

계정 만료 :안함

암호를 바꿀 수 있는 최소 날 수 : 0

암호를 바꿔야 하는 최대 날 수 : 99999

암호 만료 예고를 하는 날 수 : 7

[root@localhost ~]# chage -M 15 ydvt5

[root@localhost ~]# chage -l ydvt5

마지막으로 암호를 바꾼 날 : 5월 28, 2018

암호 만료 : 6월 12, 2018

암호가 비활성화 기간 :안함

계정 만료 :안함

암호를 바꿀 수 있는 최소 날 수 : 0

암호를 바꿔야 하는 최대 날 수 : 15

암호 만료 예고를 하는 날 수 : 7

서브쿼리 (계속)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

=> 실행 가능

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees

                GROUP BY department_id);

=> 실행 불가

SELECT employee_id, last_name, salary

FROM employees

WHERE salary IN (SELECT AVG(salary) FROM employees

                 GROUP BY department_id);

=> 실행 가능

* 서브쿼리의 결과값이 null일 때, 비교연산자를 사용하면 결과를 반환하지 않는다.

SELECT employee_id, last_name, manager_id, salary

FROM employees

WHERE manager_id = (SELECT manager_id FROM employees

                    WHERE last_name = 'Adam');

※ Adam이라는 사람이 존재하지 않을 때(null)

* Multiple-Row Subqueries : 여러 행 서브쿼리

>> ANY : 아무거나(어느 것 중 하나라도)

ex)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > ANY (SELECT AVG(salary) FROM employees

                    GROUP BY department_id);

※ = ANY와 IN은 같다.

ex)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary IN (SELECT AVG(salary) FROM employees

                 GROUP BY department_id);

=

SELECT employee_id, last_name, salary

FROM employees

WHERE salary = ANY (SELECT AVG(salary) FROM employees

                    GROUP BY department_id);

>> ALL : 모두

ex)

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > ALL (SELECT AVG(salary) FROM employees

                    GROUP BY department_id);

>> NOT IN : 같지 않다.

※ NOT IN으로 검색할 때, 서브쿼리 값에 NULL이 포함되어 있으면 결과를 반환하지 않는다.

   왜냐하면, IN은 OR 연산인데 앞에 NOT이 붙으면 부정이 되어 AND 연산으로 바뀌기 때문.

   따라서, WHERE절에 IS NOT NULL(등 NOT NULL 조건)을 습관적으로 써 주는 것이 규칙.

SELECT emp.last_name

FROM employees emp

WHERE emp.employee_id NOT IN (SELECT mgr.manager_id

                              FROM employees mgr

                              WHERE mgr.manager_id IS NOT NULL);

* Set Operators : 집합 연산자

>> 집합 연산은 기본적으로 결과값의 중복을 제거하고, 자동으로 행을 기준으로(행 단위로) 정렬해서 보여준다.

>> MINUS : 교환 법칙이 성립하지 않는다.

>> 데이터 타입이 서로 다른 테이블끼리의 합집합 방법

ex)

SELECT employee_id,   hire_date,     TO_CHAR(null) AS department_name

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name

FROM departments;

>> 집합을 정렬하는 방법(편법)

1) 순서를 정하기 위해 행을 추가한다. (한글, 영문, 숫자 등)

ex)

SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B'

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name, 'A'

FROM departments

ORDER BY 4;

2) 행에 별칭을 준다. (아무거나)

ex)

SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B' A_D

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name, 'A'

FROM departments

ORDER BY 4;

3) 추가한(의미없는) 행을 출력하지 않는 방법

ex)

COLUMN A_D NOPRINT

SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B' A_D

FROM employees

UNION

SELECT department_id, TO_DATE(null), department_name, 'A'

FROM departments

ORDER BY 4;

Manipulating Data

* Data Manipulation Language : DML (데이터 조작어)

>> SELECT / INSERT / UPDATE / DELETE

- Transaction(트랜잭션) 단위로 동작.

※ Transaction(트랜잭션)의 개념

>> INSERT문

ex)

INSERT INTO departments

VALUES(70,'Public Relations', 100, 1700);

- DB는 엑셀과 다르게, 행의 순서를 DBMS가 알아서 정한다(제어하지 못함). 논리적 구조.

- Session 단위로 작업하므로, Commit 하지 않으면 공유하지 않는다.

- NULL값 입력 방법

1) 암시적(Implicit) 방법

INSERT INTO departments(department_id, department_name)

VALUES (30, 'Purchasing');

2) 명시적(Explicit) 방법

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

- 치환 변수를 이용한 Insert

INSERT INTO departments(department_id, department_name, location_id)

VALUES (&deptno, '&dept_name', &loc_id);

>> Session 접속 중 (같은 유저로) 다시 접속하는 경우, 자동으로 commit.

>> 테이블 만들기(DDL 명령어)

ex)

CREATE TABLE sales_reps

AS 

SELECT employee_id id, last_name name, salary, commission_pct

FROM employees

WHERE 1=2;  =>  WHERE절을 일부러 틀리게 적으면, 데이터는 제외하고 테이블 구조만 가져와서 생성

※ CREATE와 같은 DDL 명령어는 자동 커밋.

>> UPDATE문

- WHERE절을 잘못 작성하면 아무것도 적용되지 않음.

- WHERE절을 작성하지 않으면 테이블의 모든 데이터가 업데이트됨. 주의!

* 트랜잭션의 특징

- 서로 다른 유저가 같은 테이블의 서로 다른 행을 수정하는 것은 가능

- 같은 테이블의 같은 행을 수정할 경우, 먼저 접속한 유저가 우선권을 가짐.

※ 자동 커밋되는 경우가 엄청 많이 존재한다. 툴에 따라 다르고 등등.

>> DELETE문

- UPDATE문과 비슷한 특징을 가짐.

- WHERE절을 잘못 작성하면 아무것도 적용되지 않음.

- WHERE절을 작성하지 않으면 테이블의 모든 데이터가 삭제됨. 주의!

* 파일 시스템 관리

1. 파일 시스템의 기능

파일의 생성, 수정, 제거 기능

파일 공유 기능

여러 종류의 접근 제어 기법 제공

파일간의 정보 전송 기능

복구 기능

심볼릭 링크 기능

암호화 및 복호화 기능

2. 파일의 종류

유닉스 시스템은 확장자 개념 자체가 없음

1) 보통 파일(Ordinary File)

2) 디렉토리 파일(Directory File) = 윈도우의 폴더 개념

3) 특수 파일(Special File)

- 대부분 /dev 디렉토리에 존재

3. 리눅스 시스템 디렉토리 구조

1) 루트 디렉토리 (/)

2) 부트 디렉토리 (/boot)

3) 시스템 설정 파일 디렉토리 (/etc)

4) 기본 실행 파일이 있는 바이너리 디렉토리 (/bin)

5) 디바이스 디렉토리 (/dev)

6) 사용자 계정 디렉토리 (/home)

등등

4. 허가권과 소유권

1) 형식

drwxr-xr-x  3 root root 4096  5월 23 11:17 test

파일 유형 / 파일 허가권(소유자/그룹/기타 사용자) / 링크 수 / 파일 소유자 이름 / 파일 소유 그룹 이름 / 파일 크기(byte) / 마지막 변경 날짜, 시간 / 파일 이름

2) 허가권

>> 파일의 접근 허가 권한 의미

- 읽기(read: r)

- 쓰기(write: w)

- 실행(execute: x)

- 디렉토리 파일과 일반 파일에서의 허가권은 다름

* <su>와 <su ->의 차이

>> su 뒤에 -을 붙이면 사용자의 홈 디렉토리로 이동

ex)

su - : 관리자 홈으로 이동(/root)

su - fedora : fedora 유저의 홈으로 이동(/home/fedora)

3) chmod : 권한 변경, 8진수 및 기호와 부호 이용

- 입력 리디렉션 (<) : 쓸 일 없음. 그렇지만 알아두자.

* 서브네팅(sub-netting)

>> 자신의 회사나 조직에 할당된 네트워크 ID의 IP 주소들을 가지고 내부적으로 여러 개의 서브넷(부분망)을 만드는 과정

- 이 때 자신이 할당받은 네트워크 ID를 가진 IP 주소들은 각각의 새로운 네트워크 ID를 가진 IP 주소들로 세분화

>> 서브네팅을 하기 전 알아야 할 사항

- 필요한 서브넷의 개수

- 각각의 서브넷에서 사용할 IP 주소의 개수

>> 서브넷 마스크로 AND 연산

>> Default Subnet Mask

A Class: 8bit | 255.0.0.0, (2^24)

B Class: 16bit | 255.255.0.0, (2^16)

C Class: 24bit  255.255.255.0, (2^8)

* Conditional Expressions : 조건문

>> IF-THEN-ELSE logic

- CASE expression : 주로 사용

- DECODE function : 오라클 전용. 해석만 할 줄 알면 됨.

>> 예문

SELECT last_name, job_id, salary,

       CASE job_id WHEN 'IT_PROG'  THEN 1.10*salary

                   WHEN 'ST_CLERK' THEN 1.15*salary

                   WHEN 'SA_REP'   THEN 1.20*salary

                   ELSE salary

       END AS "Revised_salary"

FROM employees;

※ WHEN절 앞에 컬럼명을 쓰게 되면 동등 비교밖에 못 한다. (BETWEEN절을 쓸 수 없다.)

SELECT last_name, job_id, salary,

       CASE WHEN salary BETWEEN 2500  AND 5000  THEN 'A'

            WHEN salary BETWEEN 5001  AND 10000 THEN 'B'

            WHEN salary BETWEEN 10001 AND 20000 THEN 'C'

            ELSE 'D'

       END AS salary_grade

FROM employees

ORDER BY 4;

>> NVL2문을 CASE문으로 바꿔보기

SELECT employee_id, last_name, salary, commission_pct,

       NVL2(commission_pct, 'SAL+COMM', 'SAL') income

FROM employees

WHERE department_id IN (50, 80);

=>

SELECT employee_id, last_name, salary, commission_pct,

       CASE WHEN commission_pct IS NOT NULL THEN 'SAL+COMM'

            WHEN commission_pct IS NULL     THEN 'SAL'

       END income

FROM employees

WHERE department_id IN (50, 80);

* Group Functions : 그룹 함수

-> 한 그룹에서 결과가 하나만 나오는 함수

 - AVG

 - COUNT

 - MAX

 - MIN

 - STDDEV

 - SUM

 - VARIANCE

※ Bold체는 모든 데이터 타입이 사용 가능한 함수

>> Group Functions Syntax 그룹 함수 문법

SELECT    group_function(column), ...

FROM      table

[WHERE    condition]

[ORDER BY column];

ex)

SELECT MAX(last_name), MIN(last_name)

FROM employees;

SELECT MAX(hire_date), MIN(hire_date)

FROM employees;

SELECT AVG(salary), SUM(salary)

FROM employees

WHERE department_id = 50;

>> 그룹 함수는 null값을 빼고 계산한다.

ex)

SELECT AVG(commission_pct), AVG(NVL(commission_pct, 0))

FROM employees;

>> COUNT 함수의 사용법

SELECT COUNT(department_id), COUNT(DISTINCT department_id), COUNT(*)

FROM employees;

>> 일반적으로 그룹 함수는 그룹 함수를 사용하지 않은 컬럼을 함께 쓸 수 없다.

SELECT department_id, SUM(salary)

FROM employees;

- 쓰려면 GROUP BY절이 필요.

SELECT department_id, SUM(salary)

FROM employees

GROUP BY department_id;

- GROUP BY에 컬럼을 더 넣으면 그룹을 더 세분화할 수 있다.

  ORDER BY절은 항상 마지막 줄에.

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY department_id, job_id

ORDER BY 1, 2;

- GROUP BY절의 조건은 WHERE절 대신 HAVING절을 사용.

  (HAVING절 사용 시 WHERE절은 생략 가능, 추가 조건이 필요할 때 사용)

  그리고, 아래 문장은 그룹 함수의 전체 syntax

SELECT department_id, job_id, SUM(salary)

FROM employees

WHERE employee_id > 120

GROUP BY department_id, job_id

HAVING SUM(salary) > 10000

ORDER BY 1, 2;

- 그룹 함수의 중첩은 일반 컬럼이 함께 쓰일 수 없다. 행 수가 다르므로.

SELECT ROUND(AVG(salary))

FROM employees

GROUP BY department_id;

SELECT department_id, MAX(ROUND(AVG(salary)))

FROM employees

GROUP BY department_id;

* Join

>> Natural Join : 컬럼명이 같아야 조인 가능

SELECT department_id, department_name, location_id, city

FROM departments NATURAL JOIN locations;

>> USING절

SELECT employee_id, last_name, department_id, department_name

FROM employees JOIN departments

USING (department_id);

>> ON절 -> 가장 많이 사용, 응용이 가능.

SELECT employee_id, last_name, employees.department_id, department_name

FROM employees JOIN departments

ON (employees.department_id = departments.department_id);

>> 별칭 사용 가능

   별칭을 사용하게 되면 그 문장에는 원래 테이블명 대신 별칭만 존재

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e JOIN departments d

ON (e.department_id = d.department_id);

* Equi Join, Non-Equi Join

>> Non-Equi Join : 동등하지 않은 조건의 테이블끼리 조인

ex)

SELECT e.employee_id, e.last_name, e.salary, s.grade_level

FROM employees e JOIN job_grades s

ON (e.salary BETWEEN s.lowest_sal AND s.highest_sal);

>> Natural Join, Using절은 무조건 Equi Join,

   On절에서 Equal을 사용하면 Equi Join, 아니면 Non-Equi Join

* OUTER joins : Join ~ On에서 파생된 것.

- LEFT OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

- RIGHT OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id);

- FULL OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id);

* 기본 조인은 테이블 2개이며, 테이블 3개, 1개 조인 방법도 있음.

1) 테이블이 하나일 때(같은 테이블일 때) -> 별칭을 이용하여 다른 테이블인 것처럼 만듦(셀프 조인)

ex)

SELECT e.employee_id, e.last_name, e.manager_id, m.last_name

FROM employees e JOIN employees m

                 ON (e.manager_id = m.employee_id);

2) 테이블이 3개일 때 -> 3-Way 조인

ex)

SELECT e.last_name, d.department_name, l.city

FROM employees e JOIN departments d

                 ON (e.department_id = d.department_id)

                 JOIN locations l

                 ON (d.location_id = l.location_id);

* CROSS joins : 경우의 수를 찾음. 확률 계산 시 이용.

* Join 정리. 구분.

>> 문법(syntax)

- Cross join

- Natural join

- Join ~ Using

- Join ~ On -> Outer join

>> 연산자

- Equi join

- Non-Equi join

>> Join에 만족하는 행 여부

- Inner join

- Outer join

>> Table 수

- Self join

- 3-way join

* 보너스 : Oracle 전용 Join 문법

>> Join을 사용하지 않음.

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

- Outer Join : 기호를 사용하여 행을 추가.

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id;

- 3-Way Join

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id

AND e.department_id > 50;

* IP 주소

- IP 주소의 효율적인 배정을 위하여 설계 시 클래스라는 개념 도입

- A, B, C, D, E 클래스가 있음

- 실제 사용하는 클래스는 A, B, C

- A클래스 : 가장 왼쪽 비트가 0으로 시작, 0 ~ 127

0 0000000  <-  0 ~

0 1111111  <- 127

- B클래스 : 가장 왼쪽 비트가 10으로 시작, 128 ~ 191

10 000000  <-  128 ~

10 111111  <-  191

- C클래스 : 가장 왼쪽 비트가 110으로 시작, 192 ~ 223

110 00000  <-  192 ~

110 11111  <-  223

SQL Functions

SQL 함수

1. Single-row Functions 단일행 함수

* 함수는 중첩 사용 가능

* Character 함수

SELECT employee_id, last_name, LOWER(last_name)

FROM   employees

WHERE  department_id = 50;

*

SELECT UPPER('oracle database'), LOWER('Oracle Database'), INITCAP('oracle database')

FROM dual;

* 계산식(dual 테이블 이용)

SELECT 23542*254 AS Result FROM dual;

*

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'king';

* 연결 함수 CONCAT

SELECT employee_id, CONCAT(first_name, last_name) AS Fullname  <-  AS는 생략 가능

FROM   employees;

SELECT employee_id, CONCAT(CONCAT(first_name,' '), last_name) Fullname

FROM   employees;

* SUBSTR (섭스타) : SUBSTR('text', 검색 시작 위치, 글자 수)

SELECT SUBSTR('HelloWorld', 6, 5) FROM dual;

SELECT SUBSTR('HelloWorld', -5, 5) FROM dual;

두 식은 같다.

검색 시작 위치에 음수를 쓰면 맨 뒤부터 찾음.

*

SELECT * FROM employees

WHERE SUBSTR(last_name, -1, 1) = 'n';

= WHERE last_name LIKE '%n';

last_name이 n으로 끝나는 사원을 찾음.

* INSTR (인스타)

SELECT INSTR('HelloWorld', 'l') FROM dual;  ->  'l'의 위치

SELECT INSTR('HelloWorld', 'l', 1, 2) FROM dual;  ->  1(1번째)부터 검색해서 'l'이 2번째 반환되는 위치

해당 사항이 없으면 0을 반환

*

SELECT * FROM employees

WHERE INSTR(last_name, 'A') <> 0;

* 패딩 함수 LPAD, RPAD (글자 수 포함, 나머지 문자는 지정한 문자로 채움)

- LPAD : 왼쪽부터 패딩을 준다.

- RPAD : 오른쪽부터 패딩을 준다.

ex)

SELECT employee_id, last_name, LPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT employee_id, last_name, RPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT RPAD(last_name, 15, '*') AS last_name,

       LPAD(salary, 10, '*') AS salary

FROM employees;

* TRIM 함수 : 잘라내기

ex)

SELECT TRIM('w' FROM 'window') FROM dual;  ->  모든 'w'

SELECT TRIM(LEADING 'w' FROM 'window'),  ->  선행하는(앞의) 'w'

       TRIM(TRAILING 'w' FROM 'window')  ->  따르는(뒤의) 'w'

FROM dual;

* Number Functions 숫자 함수

- 올림

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)

FROM dual;

- 버림

SELECT TRUNC(45.923,2), TRUNC(45.923,0), TRUNC(45.923,-1)

FROM dual;

- 나머지

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

* 날짜 연산하기

SELECT last_name, hire_date

FROM employees

WHERE hire_date < '1988-12-17';

* sysdate 함수

Database Server의 날짜.

서버와 클라이언트가 떨어져 있는 경우(서로 다른 타임존에 있는 경우) 시차가 발생할 수 있음.

SELECT sysdate FROM dual;

* sysdate의 표시 형식 변경하기

ex)

ALTER SESSION SET nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

: Session이 유지 중일 때만 날짜 형식 변경

* sysdate는 계산이 가능

ex)

SELECT sysdate +10, sysdate -10

FROM dual;  ->  날짜 계산

SELECT sysdate, sysdate +10/24, sysdate -10/24

FROM dual;  ->  시간 계산

SELECT sysdate, sysdate +10/(24*60), sysdate -10/(24*60)

FROM dual;  ->  분 계산

*

SELECT employee_id, hire_date, sysdate-hire_date

FROM employees;

date에 시간이 함께 나온다.

-

DESC employees;

해 보면, date 데이터 타입은 길이가 없음을 알 수 있다.

그렇지만 저장할 때는 시간도 같이 저장한다.

이것을 응용하면,

-

SELECT employee_id, hire_date, TRUNC(sysdate-hire_date)

FROM employees;

* 날짜 함수

1) MONTHS_BETWEEN : 두 날짜의 차이

ex)

SELECT employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date)

FROM employees;

응용)

SELECT employee_id, hire_date, ROUND(MONTHS_BETWEEN(sysdate, hire_date))

FROM employees;

2) ADD_MONTHS : 개월 더하기

SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 4)

FROM employees;

3) NEXT_DAY : 

SELECT sysdate, NEXT_DAY(sysdate, '월요일') FROM dual;

OR

SELECT sysdate, NEXT_DAY(sysdate, '월') FROM dual;

BUT

SELECT sysdate, NEXT_DAY(sysdate, 'Monday') FROM dual; <- 지역에 따라 안 됨

따라서,

일요일 1, 토요일 7

SELECT sysdate, NEXT_DAY(sysdate, 2) FROM dual;

4) LAST_DAY : 생략

5) ROUND : 날짜 초기화(기준에 따라)

ex)

- 'year' : 6월 30일 기준으로 반올림

SELECT sysdate, ROUND(sysdate, 'year') FROM dual;

- 'mm' : 15일 기준(30/2 = 15일)

- 'dd' : 하루 기준(24/2 = 12시간)

- 'd' : 일주일 기준(7/2 = 3.5일)

6) TRUNC : 날짜 초기화(이전)

* Conversion Functions : 변환 함수

1) TO_CHAR : 문자로

ex) SELECT TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

- 날짜 형식 앞에 'fm' 붙이면, 데이터 외에 필요없는 것(자리수 채우는, 0이나, 공백) 삭제

ex) SELECT employee_id, hire_date, TO_CHAR(hire_date, 'yy/mm/dd')

    FROM employees;  ->  18/05/07

    SELECT employee_id, hire_date, TO_CHAR(hire_date, 'fmyy/mm/dd')

    FROM employees;  ->  18/5/7

2) TO_NUMBER : 숫자로

ex)

SELECT employee_id, last_name, TO_CHAR(salary, 'L99,999.99') AS salary

FROM employees

WHERE salary > TO_NUMBER('$8,000', '$9,999');

3) TO_DATE : 날짜로

ex)

SELECT employee_id, last_name, TO_CHAR(hire_date, 'yyyy/month/dd') AS salary

FROM employees

WHERE hire_date > TO_DATE('31-12-99', 'dd-mm-rr');

* yy: 같은 세기에서 찾음(99, yy일 경우 2099년을 찾게 됨)

  rr: 현재 세기를 기준으로(100/2=50년), 뒤쪽의 데이터를 찾는다면 자동으로 앞의 것에서 찾음

* Nesting Functions : 함수 중첩

ex)

SELECT last_name,

UPPER(CONCAT(SUBSTR(last_name, 1, 8), '_US'))

FROM employees

WHERE department_id = 60;

* General Functions : 일반 함수

1) NVL (expr1, expr2) : 인수가 null일 경우, 계산 가능한 값(타입이 같아야 함)으로 변환

ex) SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary

    FROM employees;

- 계산하지 않는 경우에도 쓸 수 있음

ex) SELECT employee_id, last_name, salary, NVL(commission_pct, -1)

 FROM employees;

2) NVL2 (expr1, expr2, expr3) : expr1이 null이 아니면 expr2, null이면 expr3을 반환

ex)

SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary,

NVL2(commission_pct, 'COMM', 'NOCOMM') AS bigo

FROM employees;

3) NULLIF (expr1, expr2) : 인수가 같으면 null, 다르면 expr1을 반환

ex)  SELECT NULLIF('a','a') FROM dual;

ex2) SELECT first_name, last_name, NULLIF(LENGTH(first_name), LENGTH(last_name))

  FROM employees;

4) COALESCE (expr1, expr2, ..., exprn) : 처음으로 null이 아닌 인수의 값을 반환

ex) SELECT employee_id, commission_pct, manager_id,

 COALESCE(commission_pct, manager_id, 7777)

 FROM employees;