JayK

Shell & Shell 내장 명령어

chsh [옵션] [사용자명] : 기본 셸 바꾸기

-s shell : 지정하는 셸(절대 경로)로 로그인 셸을 바꾼다

-l : /etc/shells 파일에 지정된 셸을 출력

※ 셸 내장 명령

echo [-n] [문자열...] : 화면에 한 줄의 문자열을 출력

-n : 마지막에 줄 바꿈을 하지 않는다.

[특수문자 *]

ls * : 현재 디렉토리의 모든 파일과 서브 디렉토리를 나열. 서브 디렉토리의 내용도 출력

cp * /tmp : 현재 디렉토리의 모든 파일을 /tmp 디렉토리 아래로 복사

ls -F t* : t, tmp, temp와 같이 파일명이 t로 시작하는 모든 파일의 이름과 종류를 출력

cp *.txt ../ch3 : 확장자가 txt인 모든 파일을 상위 디렉토리 밑의 ch3 디렉토리로 복사

ls -l h*d : 파일명이 h로 시작하고 d로 끝나는 모든 파일의 상세 정보를 출력

[ 특수문자 ?와 [] ]

ls t?.txt : t 다음에 임의의 한 문자가 오고 파일확장자가 txt인 모든 파일 이름 출력

ls -l tmp[135].txt : tmp1.txt, tmp3.txt, tmp5.txt 출력

ls -l tmp[1-3].txt : tmp1.txt, tmp2.txt, tmp3.txt 출력

ls [0-9]* : 파일명이 숫자로 시작하는 모든 파일 목록

ls [A-Zaz]*[0-9] : 영문으로 시작하고 숫자로 끝나는 모든 파일 목록 출력

[ 특수문자 ~와 - ]

~ : 홈 디렉토리

- : 이전 디렉토리

[특수문자 ; 과 | ]

; : 왼쪽부터 차례대로 ;로 실행

| : 동시에 실행

[특수문자 ‘’와 “”]

echo '$SHELL' : $SHELL 문자열이 출력

echo "$SHELL" : 환경변수 출력

[특수문자 ``]

echo "Today is `date`" : date 명령어는 실행 됨

※ 배시 셸 환경 설정

set : 셸 변수와 환경 변수 모두 출력

env : 환경변수만 출력

export [-n] [셸 변수] : 환경 변수 설정하기

-n : 환경 변수를 셸 변수로 바꿈

ex ) export SOME=test

unset 변수 : 지정된 변수 해제

※ 에일리어스와 히스토리

alias 이름 =‘명령’ ex ) alias rm = 'rm -r '

unalias 에일리어스 : 에일리어스 삭제

history : 명령 입력 기록

명령 재실행하기 : !

!! : 바로 직전에 실행한 명령 재실행

!번호 : 히스토리에서 해당 번호의 명령 재실행

!문자열 : 히스토리에서 해당 문자열로 시작하는 마지막 명령 재실행

※ 배시 셸 시스템 환경 설정 파일 

시스템 환경 설정 파일

사용자 환경 설정 파일

file [파일명] : 파일의 종류를 알려줌

groups [사용자명] : 사용자가 속한 그룹을 알려줌

※ 접근 권한 변경 명령

chmod [옵션] 파일 또는 디렉토리 : 파일이나 디렉터리의 접근 권한을 변경

-R : 하위 디렉터리까지 모두 변경 가능

※ 기본 접근 권한 확인하고 변경 

umask [옵션] [마스크 값] : 기본 접근 권한을 출력하거나 변경

-S : 마스크 값을 문자로 출력

※ 프로세스 관련 명령어

ps [옵션] : 현재 실행 중인 프로세스의 정보를 출력

-e : 시스템에서 실행중인 모든 프로세스 정보 출력

-f : 프로세스의 자세한 정보 출력

-u uid : 특정 사용자에 대한 모든 프로세스 정보 출력

-p pid : pid로 지정된 특정 프로세스의 정보를 출력

pgrep [옵션] [패턴] : 지정한 패턴과 일치하는 프로세스의 정보를 출력

-x : 패턴과 정확히 일치하는 프로세스 정보 출력

-n : 패턴을 포함하고 있는 가장 최근 프로세스 정보 출력

-u : 사용자 이름 : 특정 사용자에 대한 모든 프로세스 출력

-l : PID와 프로세스의 이름 출력

-t term : 특정 단말기와 관련된 프로세스의 정보 출력

kill [시그널] PID ... : 지정한 시그널을 프로세스에 보낸다

-2 : 인터럽트 시그널을 보낸다 (ctrl+c)

-9 : 프로세스 강제 종료한다

-15 : 프로세스가 관련된 파일을 정리하고 프로세스를 종료. 종료가 안될 수도 있음

pkill 명령을 이용해 프로세스 종료 

명령이름으로 찾아 종료하므로 같은 명령이 여러개 검색될 경우 한 번에 모두 종료

ex) ps - fp $(pgrep -x man)

포그라운드 작업과 백그라운드 작업

sleep 100 -> 포그라운드 작업

-> sleep 명령이 끝날때까지 기다림

백그라운드 작업

sleep 100& -> 백그라운드 작업

[1] 6852

$ -> 프롬프트가 바로 나와 다른 명령을 실행할 수 있다.

jobs [%작업 번호] : 백그라운드 작업을 모두 보여준다. 특정 작업 번호를 지정하면 해당 작업의 정보만 보여줌

※ 작업 전환 명령

nohup [명령]& : 로그아웃한 뒤에도 백그라운드 작업을 계속 실행

※정해진 시간에 반복 실행

crontab [-u 사용자 ID] [옵션] [파일 이름] : 사용자의 crontab 파일을 관리

-e : 사용자의 crontab 파일을 편집

-l : crontab 파일의 목록을 출력

-r : crontab 파일을 삭제

00 10 * * * /bin/ls -l > /home/yang/crontab : 10시 정각에 모든날에 crontab 폴더에 저 명령해서 나오는 값을 저장

 
※ 압축, 압축해제 명령어

tar 명령[옵션] [아카이브 파일] 파일 이름 : 파일과 디렉터리를 묶어 하나의 아카이브 파일 생성

-cvf : 아카이브생성

-tvf : 아카이브 내용보기

-xvf : 아카이브 압축 해제

-uvf : 아카이브 업데이트

-rvf : 아카이브에 파일 추가

gzip [옵션] 파일 이름 : 파일을 압축함

-d : 압축 해제

-l : 압축된 파일의 정보를 보여줌

-r : 하위 디렉토리를 이동하여 파일 압축

gunzip 파일 이름 : gz로 압축된 파일의 압축을 푼다.

Vi 에디터

[입력모드]

i : 현재 커서 앞에 입력

a : 현재 커서 뒤에 입력

o : 커서가 위치한 다음 행 입력

I : 커서가 위치한 행의 첫 칼럼 이동 입력

A : 커서가 위치한 행의 마지막 칼럼 이동 입력

O : 커서가 위치한 행의 앞에 입력

[파일 저장, 종료]

:q : 그냥 종료

:q! : 강제 종료

:w [파일명] : 작업 내용 저장 (파일 명 지정 시 새 파일로 저장)

:wq,:wq!,(명령모드에서) ZZ : 작업한 내용 저장하고 vi 종료

[커서 이동 명령]

h, j, k, l : 왼, 아래, 위, 우

^,0 : 현재행의 처음

$ : 현재 행의 마지막

- : 커서를 앞 행의 처음

+,Enter : 커서를 다음 행의 처음

H : 커서를 화면 맨 위로 이동

M : 커서를 화면 중간 행으로 이동

L : 커서를 화면의 맨 아랫 행 이동

w : 다음 단어 첫 글자로 이동

b : 앞 단어 첫 글자로 이동

e : 단어의 마지막 글자로 이동

[화면 이동 명령]

^u(ctrl+u) : 반 화면 위로 이동

^d(ctrl+d) : 반 화면 아래로 이동

^b(ctrl+b) : 한 화면 위로 이동

^f(ctrl+f) : 한 화면 아래로 이동

^y(ctrl+y) : 화면을 한 행만 위로 이동

^e(ctrl+e) : 화면을 한 행만 아래로 이동

[특정 행 바로 이동]

G(shift+g) : 파일 마지막 행으로 이동

행 번호G : 지정한 행 번호로 이동

:행 번호 : 지정한 행 번호로 이동 (마지막 행 모드)

:$ : 파일의 마지막 행으로 이동(마지막 행 모드)

[내용 수정하기]

r : 커서가 위치한 글자를 다른 글자로 수정

cw,#cw : 커서 위치부터 현재 단어의 끝가지 수정 #에는 수정할 단어의 수를 지정 ex)3cw

s,#s : 커서 위치부터 esc 키 입력까지 수정 #에는 수정할 글자 수

cc : 커서가 위치한 행 내용 모두 수정

C : 커서 위치부터 행의 끝까지 수정

[내용 삭제하기]

x,#x : 커서 위치의 글자 삭제. #에는 삭제할 글자 수 지정

dw, #dw : 커서 위치의 단어 삭제 , #에는 삭제할 단어 수 지정

dd, #dd : 커서 위치의 행 삭제, #에는 삭제할 행의 수 지정

D : 커서 위치부터 행의 끝까지 삭제

[명령 취소하기]

u : 명령 취소

U : 해당 행에서 한 모든 명령 취소

:e! : 마지막으로 저장한 내용 이후의 것을 버리고 새로 작업

[복사하기,잘라내기,붙이기]

yy, #yy : 커서가 위치한 행 복사. #에는 복사할 행 수 지정

p : 커서가 위치한 행 아래쪽에 붙임

P : 커서가 위치한 행 위쪽에 붙임

dd,#dd : 삭제와 같은 기능이지만 잘라내는 기능 포함

[범위 지정 명령]

1,$,% : 1행부터 마지막 행 까지 지정

1, . : 1행부터 커서가 있는 행까지 지정

., $ : 커서가 있는 행부터 마지막 행까지 지정

,-3 : 현재 행과 이전 세 번째 행까지 ( 총 네 행) 지정

10,20 : 10행부터 20행까지 지정

[마지막 행 모드에서 복사,잘라내기,붙이기]

:#y : #으로 지정한 행을 복사 ex) 3y는 3행을 복사

:<범위>y : 범위로 지정한 행을 복사 ex)2,4y는 2~4행을 복사

:#d : #으로 지정한 행을 잘라냄(삭제) ex) 3d는 3행을 잘라냄

:<범위>d : 범위로 지정한 행을 잘라냄(삭제) ex) 1,4d는 1~4행을 잘라냄

:pu : 현재 행 다음에 버퍼의 내용을 붙임

:#pu : #으로 지정한 다음의 버퍼 내용을 붙임 ex) 4pu

[검색하기]

/문자열 : 문자열 아래 방향으로 검색

?문자열 : 문자열을 위 방향으로 검색

n : 찾던 방향으로 다음 문자열 검색

N : 역방향으로 다음 문자열을 검색

[바꾸기]

:s/문자열1/문자열2/ : 커서가 위치한 행에서 첫 번째로 나오는 문자열1을 문자열2로 바꾼다

:s%/문자열1/문자열2/g : 파일 전체에서 모든 문자열1을 문자열2로 바꾼다

:<범위>s/문자열1/문자열2/ : 범위 내 모든 행의 각 행에서 첫 번째로 나오는 문자열1을 문자열2로 바꿈

:<범위>s/문자열1/문자열2/g : 범위 내 모든 행에서 문자열1을 문자열2로 바꾼다

:<범위>s/문자열1/문자열2/gc : 범위 내 모든 행에서 문자열1을 문자열2로 바꿀 때 수정할지 여부를 묻는다.

[파일 관련 명령]

:r 파일명 : 지정한 파일을 읽어들여 현재 커서 위치에 삽입

:e 파일명 : 지정한 파일로 전환 (기존 파일을 :w 로 저장한 뒤에 실행)

:n : vi 시작 시 여러 파일을 지정했을 경우 다음 파일로 작업 이동

:! [셸 명령] : vi 작업을 잠시 중단하고 셸 명령 실행 (enter키를 입력해야 함)

:sh : vi를 잠시 빠져나가 셸 명령을 실행한다 (vi로 돌아오려면 exit 명령을 입력해야 한다)

date : 날짜와 시간을 출력

clear : 화면을 지운다

man 명령어 : 명령 사용법을 화면에 출력

passwd :　사용자 계정의 비밀번호 변경

exit : 터미널 종료

cd [디렉토리명] : 디렉토리 이동

[기준]

/home/user1에서 /etc/passwd 이동

상대경로 : 현재위치를 기준으로 명시

ex) cd ./../../etc/passwd

절대경로 : 루트를 기준으로 명시

ex) cd /etc/passwd

pwd : 현재 위치 확인 (절대경로출력)

ls [옵션] 파일 또는 디렉토리 : 디렉토리 내용 출력

-a : 숨김 파일 출력

-d : 지정 디렉토리 자체 정보 출력

-i : inode 출력

-l : 파일 상세 정보 출력 (파일 종류 - : 일반 파일, d : 디렉토리 파일, l : 심벌릭링크)

-A : .(마침표)와 ..를 제외한 모든 파일 목록 추가

-F : 파일 종류 표시 ( * : 실행파일, / : 디렉토리, @ : 심볼릭 링크 )

-L : 심볼릭 링크 파일 일 때 원본 파일 정보 출력

-R : 하위 디렉토리 목록까지 출력

-r : 최신이 맨아래 (DESC)

-t : 수정 시간

mkdir [옵션] 디렉토리명1, 디렉토리명2, ... : 디렉토리 생성

-p : 하위 디렉토리까지 한번에 생성

cat [옵션] 파일명... : 파일 내용 출력

-n : 행 번호 출력

more [옵션] 파일명 : 파일 내용을 화면 단위로 출력 (space bar: 다음화면 출력, Enter : 한줄 씩 스크롤)

+행 번호 : 출력 시작할 행번호

less 파일명 : 파일 내용을 화면 단위로 출력

( j :한줄 씩 다음 행, k : 한줄 씩 이전 행, ctrl + f : 다음 화면 이동, ctrl +b : 이전 화면 이동)

tail [옵션] 파일명 : 파일의 뒷부분 몇 행을 출력

+행 번호 : 지정한 행부터 끝까지 출력

-숫자 : 화면에 출력할 행의 수

-f : 파일 출력이 종료되지 않고 주기적으로 출력

cp [옵션] 파일명1/디렉토리명1 파일명2/디렉토리명2 : 파일이나 디렉토리 복사

-i : 파일명 2가 존재하면 덮어 쓸 것 인지 물어봄

-r : 디렉토리를 복사할 때 지정

* 이름이 존재 시 덮어씀

mv [옵션] 파일명1/디렉토리명1 파일명2/디렉토리명2 : 파일을 이동

-i : 파일명2/데릭토리명2 존재시 덮을 것인지 물어봄

* 이름 존재시 이름명 변경

rm [옵션] 파일명/디렉토리명 : 파일을 삭제

-i : 지정한 파일을 삭제할지 물어봄

-r : 지정한 디렉토리를 삭제

ln [옵션] 원본파일명 링크파일명 : 파일 링크 생성

-s : 심벌릭 링크 파일 생성

* 하드링크는 링크는 되있어 하나수정시 같이 바뀌지만 원본을 삭제해도 그대로 남아있음

심볼릭 링크는 주소값만 가져와 용량차지를 하지 않으며 원본 삭제 시 사용 불가능함

touch [옵션] 파일명 : 빈 파일 생성

-a : 접근 시간 변경

-m : 수정 시간 변경

-t :　[[CC]YY]MMDDhhmm[.ss] : 시간 직접 입력 ( 오래되었을시는 시간표시 안됨 )

grep [옵션] 패턴 [파일명] : 지정한 패턴을 포함하는 행을 찾음 ex ) grep root /etc/passwd

-i : 대문자, 소문자 모두 검색

-l : 해당패턴 포함 파일 이름 출력

-n : 행 번호 출력

find 경로 검색 조건 [동작] : 조건에 맞는 파일을 지정한 위치에서 찾음

옵션 -name filename : 파일 이름으로 검색

-type 파일 종류 : 파일 종류로 검색

-user loginID : 지정한 사용자가 소유한 모든 파일 검색

-perm 접근 권한 : 지정한 사용 권한과 일치하는 파일 검색

동작 -exec 명령 {} \; : 검색된 파일에 명령을 실행

-ok 명령 {} \; : 사용자의 확인을 받아서 명령을 실행

whereis [옵션] 명령 :　지정된 경로에서 명령의 바이너리 파일이나 매뉴얼 파일의 위치를 찾음

-b : 바이너리 파일만 검색

-m : 매뉴얼 파일만 검색

-s : 소스 파일만 검색

which [명령어] : 명령어 파일의 위치를 찾아 경로나 에일리어스 출력

-- 튜닝 수업 환경 설정

alter system set sec_case_sensitive_logon=FALSE;

ALTER USER SYSTEM IDENTIFIED BY MANAGER;

DROP USER SCOTT CASCADE;

CREATE USER SCOTT IDENTIFIED BY TIGER

GRANT CONNECT,RESOURCE,DBA TO SCOTT;

 ( pfile일 경우 spfile로 변경 )

1. 대용량 샘플 dmp 파일 imp 수행

DDL문장과 딕셔너리

■ CREATE

이 명령은 새로운 오브젝트나 스키마를 생성할 때 사용하는 명령어

CREATE TABLE 한글테이블

( 컬럼1 NUMBER,

컬럼2 VARCHAR2(10),

컬럼3 DATE );

select * from user_tables

where table_name='한글테이블';

* 한글로도 테이블명을 선언해서 사용할 수 있음!

* 테이블 생성시 테이블명 주의사항

1.반드시 문자로 시작해야함

2. 최대 30byte 한글테이블일 경우 15글자

3. 테이블 한 계정에서 동일 명으로 사용 못함 (다른 계정시 중복가능) 

-- SCOTT USER의 DEFAULT TABLESPACE 확인

SELECT USERNAME, DEFAULT_TABLESPACE

FROM DBA_USERS

WHERE USERNAME='SCOTT';

--테이블의 테이블스페이스 확인

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME

FROM DBA_TABLES

WHERE TABLE_NAME='DDL_TEST';

--SCOTT 계정의 DEFAULT TABLESPACE를 EXAMPLE로 변경

ALTER USER SCOTT DEFAULT TABLESPACE EXAMPLE;

테이블 복사하기 (CTAS 구문)

CREATE TABLE DEPT3

AS

SELECT * FROM DEPT2;

기존 컬럼명 변경후 특정컬럼 가져오기

CREATE TABLE DEPT4

AS

SELECT DCODE AS "DCODE_1",DNAME AS "DNAME_1"

FROM DEPT2;

테이블 구조만 생성 (데이터없이)

CREATE TABLE DEPT5

AS

SELECT DCODE,DNAME

FROM DEPT2

WHERE 1=2;

가상 컬럼 생성(버츄얼컬럼)

CREATE TABLE VT001

( NO1 NUMBER,

NO2 NUMBER,

NO3 NUMBER GENERATED ALWAYS AS (NO1 +NO2) VIRTUAL);

DESC VT001;

INSERT INTO VT001(NO1,NO2)

VALUES(1,2);

SELECT * FROM VT001; 

--EMP2 테이블의 NAME,BIRTHDAY,PAY을 가지고 GRADE_A, GRADE_B, GRADE_C 테이블을 각각 생성하고

--EMP2 테이블에서 PAY가 30000000 미만이면 'C', 30000000이상 50000000 이하 'B', 50000000보다 크면 'A'가 되도록 하여

--열 이름을 GRADE로 지정하고 이름, 생일, PAY와 함께 등급별로 테이블을 분류하여 데이터를 INSERT 하여라

CREATE TABLE GRADE_A

AS SELECT NAME,BIRTHDAY,PAY,'ABC' AS "GRADE"

FROM EMP2

WHERE 1=2;

CREATE TABLE GRADE_B

AS SELECT NAME,BIRTHDAY,PAY,'ABC' AS "GRADE"

FROM EMP2

WHERE 1=2;

CREATE TABLE GRADE_C

AS SELECT NAME,BIRTHDAY,PAY,'ABC' AS "GRADE"

FROM EMP2

WHERE 1=2;

INSERT ALL

WHEN PAY < 30000000 THEN

INTO GRADE_C VALUES(NAME,BIRTHDAY,PAY,'C')

WHEN PAY BETWEEN 30000000 AND 50000000 THEN

INTO GRADE_B VALUES(NAME,BIRTHDAY,PAY,'B')

WHEN PAY > 50000000 THEN

INTO GRADE_A VALUES(NAME,BIRTHDAY,PAY,'A')

SELECT NAME,BIRTHDAY,PAY

FROM EMP2;

SELECT * FROM GRADE_C;

--PROFESSOR 테이블에서 홈페이지 주소가 없는 교수들은 ID로 된 홈페이지 주소로 변경하여라

--(홈페이지 주소 : HTTP://WWW.NAVER.COM/ID)

SELECT * FROM PROFESSOR;

UPDATE PROFESSOR

SET HPAGE = 'HTTP://WWW.NAVER.COM/'||ID

WHERE HPAGE IS NULL;

■ ALTER문

CREATE TABLE DEPT6

AS

SELECT DCODE, DNAME

FROM DEPT2

WHERE DCODE IN(1000,1001,1002);

-- 디폴트넣은거와 안넣은거의 차이 

ALTER TABLE DEPT6 ADD (LOC VARCHAR2(10));

--컬럼 추가시 DEFAULT 설정

ALTER TABLE DEPT6 ADD (LOC2 VARCHAR2(10) DEFAULT 'AAA');

--컬럼 추가후 DEFAULT 설정

ALTER TABLE DEPT6 ADD (LOC3 VARCHAR2(10));

ALTER TABLE DEPT6 MODIFY (LOC3 DEFAULT 'BBB');

SELECT * FROM DEPT6;

--CTAS DEPT7 생성 -> ADD COLUMN -> DEPT6 RENAME ;

--RENAME

ALTER TABLE DEPT6 RENAME COLUMN LOC2 TO AREA;

--DEPT6 은 DEPT7로

RENAME DEPT6 TO DEPT7;

--컬럼의 데이터 크기를 변경

ALTER TABLE DEPT7 MODIFY (DCODE VARCHAR2(10)); --이미 4크기의 데이터가 들어가 있기 때문에 작게는 변경 불가능

DESC DEPT7;

SELECT MAX(LENGTH(DCODE)) FROM DEPT7;

ALTER TABLE DEPT7 DROP COLUMN LOC3; -- 부하가 걸릴 수 있음 고려

SELECT * FROM DEPT7;

읽기 전용 테이블로 변경(읽기 O , 변경 X) [위험!!!!]

10G - 트리거를 사용 OR 제약조건 DISABLE/NOVALIDATE 등을 사용했어야함

CREATE TABLE T_READ

(NO NUMBER, NAME VARCHAR2(10));

INSERT INTO T_READ

VALUES (1,'AAA');

COMMIT;

ALTER TABLE T_READ READ ONLY;

SELECT TABLE_NAME, READ_ONLY

FROM USER_TABLES

WHERE TABLE_NAME = 'T_READ'

(SELECT 만 가능)

ALTER TABLE T_READ ADD (TEL NUMBER DEFAULT 111);

ALTER TABLE T_READ MODIFY (NAME VARCHAR2(15)); (UPDATE OPERATION ~~ 오류 뜸)

ALTER TABLE T_READ READ WRITE; ( READ ONLY 해제 ) [위험!!!!]

DROP TABLE T_READ; [ READ ONLY 상태에서 DROP 됨 DROP > READ ONLY ]

--1) 다음은 웹 사이트의 게시판을 사용하는 회원을 관리하기 위한 테이블 레이아웃이다. 회원테이블(member)을 생성하여라.

CREATE TABLE MEMBER2(

USERID VARCHAR2(10),

USERNAME VARCHAR2(10),

PASSWD VARCHAR2(10),

IDNUM VARCHAR2(13),

PHONE NUMBER(13),

ADDRESS VARCHAR2(20),

REGDATE DATE,

INTEREST VARCHAR2(15));

SELECT * FROM MEMBER2;

COMMENT ON TABLE MEMBER2 IS '게시판회원테이블';

COMMENT ON COLUMN MEMBER2.USERID IS '사용자아이디';

COMMENT ON COLUMN MEMBER2.USERNAME IS '회원이름';

COMMENT ON COLUMN MEMBER2.PASSWD IS '비밀번호';

COMMENT ON COLUMN MEMBER2.IDNUM IS '주민등록번호';

COMMENT ON COLUMN MEMBER2.PHONE IS '전화번호';

COMMENT ON COLUMN MEMBER2.ADDRESS IS '주소';

COMMENT ON COLUMN MEMBER2.REGDATE IS '가입일';

COMMENT ON COLUMN MEMBER2.INTEREST IS '관심분야';

SELECT * FROM USER_TAB_COMMENTS

WHERE TABLE_NAME='MEMBER2';

SELECT * FROM USER_COL_COMMENTS

WHERE TABLE_NAME ='MEMBER2';

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES;

--2) 회원테이블에 다음 회원 정보를 입력하여라.

-- (sunshinLee, 이순신, ssl000, 8701011120200, 02)333-2123, 서울, 2015/07/05, 컴퓨터)

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'; -- CREATE 문 안에 TO_DATE를 사용하면 부하

INSERT INTO MEMBER2(USERID,USERNAME,PASSWD,IDNUM,PHONE,ADDRESS,REGDATE,INTEREST)

VALUES('sunshinLee','이순신','ssl000','8701011120200','023332123','서울', '2015/07/05', '컴퓨터');

SELECT PHONE, TO_CHAR(PHONE,'00000000000') FROM MEMBER2;

SELECT * FROM MEMBER2;

--3) 회원 테이블의 구조와 데이터 집합이 같은 member_second 테이블을 생성하여라.

CREATE TABLE MEMBER_SECOND

AS

SELECT * FROM MEMBER2;

SELECT * FROM MEMBER_SECOND;

--4) 회원 테이블의 userid, username, passwd 컬럼만 복사해서 member_third 테이블을 생성하여라.

CREATE TABLE MEMBER_THIRD

AS SELECT USERID,USERNAME,PASSWD FROM MEMBER2;

SELECT * FROM MEMBER_THIRD;

--5) 회원 테이블과 구조는 동일하고 데이터를 가지지 않는 member_forth 테이블을 생성하여라.

CREATE TABLE MEMBER_FORTH

AS SELECT * FROM MEMBER2

WHERE 1=2;

SELECT * FROM MEMBER_FORTH;

--6) 회원 테이블에 email 컬럼을 추가하여라. 단, email 컬럼 데이터 타입은 varchar2(50)이다.

ALTER TABLE MEMBER2 ADD(EMAIL VARCHAR2(50));

DESC MEMBER2;

--7) 회원 테이블에 국적을 나타내는 country 컬럼을 추가하고 기본값을 ‘Korea’로 지정하여라.

ALTER TABLE MEMBER2 ADD(COUNTRY VARCHAR2(10) DEFAULT 'Korea');

/*OR*/ALTER TABLE MEMBER2 MODIFY COUNTRY DEFAULT 'Korea';

DESC MEMBER2;

--8) 회원 테이블에서 email 컬럼을 삭제하여라.

ALTER TABLE MEMBER2 DROP( EMAIL );

DESC MEMBER2;

--9) 회원 테이블의 address 컬럼의 데이터 크기를 30으로 증가시켜라.

ALTER TABLE MEMBER2 MODIFY( ADDRESS VARCHAR2(30) );

DESC MEMBER2;

--10) member_second 테이블 이름을 potential_member로 변경하여라.

RENAME MEMBER_SECOND TO POTENTIAL_MEMBER;

SELECT * FROM POTENTIAL_MEMBER;

TRUNCATE

TRUNCATE TABLE TABLE명;

DROP

DROP TABLE TABLE명;

--테스트용 테이블 생성

CREATE TABLE SCOTT.TEST_TBL1

AS SELECT * FROM SCOTT.STUDENT;

CREATE TABLE SCOTT.TEST_TBL2

AS SELECT * FROM SCOTT.STUDENT;

--테스트용 인덱스 생성

CREATE INDEX SCOTT.TEST_IND1

ON SCOTT.TEST_TBL1(STUDNO);

CREATE INDEX SCOTT.TEST_IND2

ON SCOTT.TEST_TBL2(STUDNO);

--테이블에 인덱스 존재여부 확인

SELECT INDEX_NAME, TABLE_NAME, STATUS,VISIBILITY

FROM DBA_INDEXES

WHERE TABLE_NAME LIKE 'TEST_%'

AND INDEX_NAME LIKE 'TEST_%';

--테이블 DROP

DROP TABLE SCOTT.TEST_TBL1;

SELECT * FROM SCOTT.TEST_TBL1;

** 차이점 DELETE : 디스크 상 공간은 그대로 가지고 있음 --> REORG 해야함

TURCATE : 용량은 줄고 구조는 살아있음

DROP : DROP TABLE을 하게되면 몽땅 삭제

--테이블 DROP 후 인덱스 재 확인 -> 테이블 TRUNCATE 시 해당 테이블의 인덱스도 같이 삭제됨

--인덱스 UNUSABLE ****INDEX TRUNCATE시 VALID로 다시바뀜

ALTER INDEX SCOTT.TEST_IND2 UNUSABLE;

TRUNCATE TABLE SCOTT.TEST_TBL2;

/

============== 차이 지웠을 때 구조적인 차이, 메모리 차이 HWM 하이워터마크=====================

--인덱스 UNUSABLE : 테이블 변경시 INDEX 반영이 안됨(그래서 리빌드가 필요함)

ALTER INDEX SCOTT.TEST_IND2 UNUSABLE;

TRUNCATE TABLE SCOTT.TEST_TBL2;

--인덱스 INVISIBLE ; 테이블 변경시 INDEX에 반영

ALTER INDEX SCOTT.TEST_IND2 INVISIBLE;

--인덱스 VISIBLE

ALTER INDEX SCOTT.TEST_IND2 VISIBLE;

--프로시저로 만든 TEST01 테이블에 인덱스 생성

CREATE INDEX SCOTT.TEST03_IND1 ON SCOTT.TEST03(NO);

--PLAN확인

SELECT NO

FROM SCOTT.TEST03

WHERE NO = 800; -- CTRL+E 실행계획

--인덱스 invisible 후 플랜 재 확인

ALTER INDEX SCOTT.TEST03_IND1 VISIBLE;

DML

DML: INSERT(입력) , UPDATE(변경) , DELETE (삭제) , MERGE(병합)

DDL : CREATE (생성) , ALTER(수정) , TRUNCATE(잘라내기), DROP(삭제)

TCL : COMMIT(확정) , ROLLBACK(취소)

SELECT : 어떤 분류에서는 DQL 라고 하기도 함

■ INSERT

INSERT 명령어는 테이블에 새로운 데이터를 입력할 때 사용

데이터를 입력할 때 숫자 값 이외에는 데이터를 ‘(홑따옴표)로 감싸야 함

INSERT INTO DEPT2(DCODE,DNAME,PDEPT,AREA)

VALUES (9000, '특판1팀', '영업부', '임시지역');

COMMIT;

* 삽입 할 컬럼명 입력 해주고 컬럼 위치에 맞게 values 입력. (컬럼 없을 시 null 값 입력)

INSERT INTO DEPT2

VALUES (9000, '특판1팀', '영업부', '임시지역');

COMMIT;

* 컬럼 개수와 values의 개수가 맞아야함

※ ITAS 문

INSERT INTO PROFESSOR2

SELECT * FROM PROFESSOR

WHERE POSITION='정교수'; --  (중복삽입가능)

* PROFESSOR 테이블의 POSITION이 정교수인 데이터들만 PROFESSOR2테이블에 INSERT 해줌

INSERT INTO PROFESSOR2(NAME,ID,POSITION,PAY,HIREDATE)

SELECT NAME, ID, POSITION, PAY,HIREDATE FROM PROFESSOR

WHERE POSITION = '조교수';

※INSERT ALL

여러 테이블에 여러 행 입력

INSERT ALL

INTO P_01(NO,NAME)

VALUES (1,'AAA')

INTO P_02(NO,NAME)

VALUES(2,'BBB')

SELECT * FROM DUAL;

COMMIT;

- -다른테이블의 데이터를 가져와서 입력하기

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;

CREATE TABLE STUDENT3

AS SELECT * FROM STUDENT

WHERE 1=2;

** 데이터는 없고 구조만 가지고 싶다면 where 1=2(껍데기만 생성)

--STUDENT테이블의 1,2학년은 STUDENT3 테이블로 3,4학년은 STUDENT4 테이블로 넣으세요

INSERT ALL

WHEN GRADE BETWEEN 1 AND 2 THEN

INTO STUDENT3

WHEN GRADE BETWEEN 3 AND 4 THEN

INTO STUDENT4

SELECT * FROM STUDENT;

■ UPDATE

UPDATE 문장은 기존 데이터를 다른 데이터로 변경할 때 사용하는 방법

UPDATE PROFESSOR

SET BONUS = 100

WHERE POSITION = '조교수';

COMMIT;

--PROFESSOR 테이블에서 차범철 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250만원이

-- 안 되는 교수들의 급여를 15% 인상하세요.

SELECT * FROM PROFESSOR

WHERE POSITION = (SELECT POSITION

FROM PROFESSOR

WHERE NAME ='차범철');

UPDATE PROFESSOR

SET PAY=PAY*1.15

WHERE POSITION = (SELECT POSITION

FROM PROFESSOR

WHERE NAME ='차범철')

AND PAY < 250;

**=> PAY가 1.15배 되서 update 된 것을 확인 할 수 있다.

--PROFESSOR 테이블에서 나한열 교수의 급여와 보너스를 주승재 교수와 동일하게 수정하여라

UPDATE PROFESSOR

SET (PAY,BONUS) = ( SELECT PAY,BONUS

FROM PROFESSOR

WHERE NAME = '주승재')

WHERE NAME = '나한열';

COMMIT;

--STUDENT 테이블에서 1학년 학생 중에 키가 175미만이고 몸무게가 60이상인 학생은 1학년의 평균 키와 평균

--몸무게로 수정하여라

UPDATE STUDENT

SET (WEIGHT,HEIGHT) = (SELECT AVG(WEIGHT),AVG(HEIGHT)

FROM STUDENT

WHERE GRADE = 1)

WHERE HEIGHT < 175 AND WEIGHT >= 60 AND GRADE = 1;

COMMIT;

■ DELETE

DELETE 문장은 데이터를 삭제하는 구문

DELETE는 바로 반환되지 않아 메모리에 남아있을 수 있음. (TRUNCATE는 바로 반환[ 복구불가능 ])

--DELETE 실습--

-- test table 생성

CREATE TABLE scott.test01 (no NUMBER, name VARCHAR2(20), addr VARCHAR2(20));

-- 프로시저를 통한 대용량 데이터 입력

BEGIN

FOR i IN 1..500000 LOOP

INSERT INTO scott.test01

VALUES(i, DBMS_RANDOM.STRING('A',19) ,

DBMS_RANDOM.STRING('Q',19) );

END LOOP;

COMMIT;

END;

select count(*) from scott.test01;

-- TEST01 테이블 사이즈 확인

select sum(bytes)/1024/1024 MB

from dba_segments

where owner = 'SCOTT'

and segment_name = 'TEST01'; --테이블명

-- 각 테이블 정보 확인

select table_name, num_rows, blocks, empty_blocks

from dba_tables

where owner='SCOTT'

and table_name='TEST01';

-- 통계 정보 생성

ANALYZE TABLE SCOTT.TEST01 COMPUTE STATISTICS;

생성후 위 각 테이블 정보 확인시

-- 실 사용 블럭 수 조회

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||

DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "실사용 블록수"

FROM SCOTT.TEST01;

-- 테이블의 테이블스페이스 조회

SELECT TABLE_NAME, TABLESPACE_NAME

FROM DBA_TABLES

WHERE TABLE_NAME='TEST01';

-- 테이블 전체 삭제

DELETE FROM SCOTT.TEST01;

COMMIT;

--> DELETE로 삭제하게 되면 INSERT시 HWM이후에 있는 주소 값에 삽입하게 된다. --> reorg 작업

** reorg작업 : full scan 후 빈 공간에 차례대로 정렬하는 작업

REORG 작업

-- 테이블 스페이스 MOVE

ALTER TABLE SCOTT.TEST01 MOVE TABLESPACE EXAMPLE;

MERGE(위험성 때문에 잘 사용안함)

MERGE 란 여러 테이블 데이터를 합치는 병합을 의미

이 명령어는 문법이 다소 복잡하므로 잘 살펴봐야함

->다량 중복데이터 관리할 때 사용

--MERGE 작업 QUERY 1 (pt_01 테이블과 p_total 테이블 병합)

merge into p_total total

using pt_01 p01

on (total.판매번호 = p01.판매번호)

when matched then

update set total.제품번호=p01.제품번호

when not matched then

insert values(p01.판매번호,p01.제품번호,p01.수량,p01.금액);

merge into p_total total

using pt_02 p02

on (total.판매번호 = p02.판매번호)

when matched then

update set total.제품번호=p02.제품번호

when not matched then

insert values(p02.판매번호,p02.제품번호,p02.수량,p02.금액);

--확인

select * from p_total;

select * from pt_01;

select * from PT_02;

commit;

Character set 이 맞지 않아 한글이 깨져 ?로 표시 된다 당혹감을 감출 수 없음..

Character Set은 각 나라는 영의 ASCII영역 뒤에 추가적인 바이트를 추가하는 방식으로 사용 한다고 한다

주로 한글은 2Byte, UTF8은 3Byte를 가진다고 한다

그러므로, 같은 ASCII문자로 구성하더라도 다른 값을 가르키거나 없기 때문에 ?가 나오는 것이다.

먼저 NLS_CHARACTERSET 을 확인 해보았다

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

이제 sysdba로 들어가 아래와 같이수행해준다.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET KO16MSWIN949;

alter database character set ko16mswin949
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

==> 잘 수행하다 이런 에러가 뜬다.

      찾아보니 새로운 캐릭터 셋은 반드시 기존 캐릭터 셋이여야만 한다고 한다.

      무슨 말인고 하니 아래 표를 참조하면 알 수 있다.

      (자세한 내용 : http://cafe.naver.com/mudong.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=548)

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949;

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SELECT * FROM nls_database_parameters where parameter = 'NLS_CHARACTERSET';

다시 조회 해보자

?????????????????????????????????????????????????????????????????????

한글로 됐다..............

캐릭터셋이 변경되면서 유실됐나보다 다시 데이터를 넣고 조회해보니

깔끔하게 나왔다!

SUB QUERY(서브쿼리)

■ 단일행 서브쿼리

--단일행 SUB QUERY 연습문제1

--STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 이윤나 학생과 1전공(DEPTNO1)이 동일한 학생들의 이름과

--1전공 이름을 출력하세요

SELECT S.NAME, D.DNAME

FROM STUDENT S, DEPARTMENT D

WHERE S.DEPTNO1 = (SELECT DEPTNO1

FROM STUDENT

WHERE NAME = '이윤나')

AND S.DEPTNO1 = D.DEPTNO;

--단일행 SUB QUERY 연습문제2

--PROFESSOR 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요

SELECT P.NAME, P.HIREDATE, D.DNAME

FROM PROFESSOR P, DEPARTMENT D

WHERE HIREDATE > (SELECT HIREDATE

FROM PROFESSOR

WHERE NAME = '송도권')

AND P.DEPTNO = D.DEPTNO;

--단일행 SUB QUERY 연습문제3

--STUDENT 테이블에서 1전공(DEPTNO1)이 101번 인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를

--출력하세요.

SELECT NAME,WEIGHT

FROM STUDENT

WHERE WEIGHT > (SELECT AVG(WEIGHT)

FROM STUDENT

WHERE DEPTNO1 = 101);

--단일행 SUB QUERY 연습문제4

--PROFESSOR 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의

--이름과 급여, 입사일을 출력하세요

SELECT NAME,PAY,HIREDATE

FROM PROFESSOR

WHERE PAY < (SELECT PAY

FROM PROFESSOR

WHERE NAME = '조인형')

AND HIREDATE = (SELECT HIREDATE

FROM PROFESSOR

WHERE NAME = '심슨');

■  다중 행 SUB QUERY 

SUB QUERY의 결과과 2건 이상 출력되는 것을 말한다. 다중 행 SUB QUERY는 SUB QUERY의 결과가 여러건 출력되기 때문에 단일 행 연산자를 사용할 수 없다. 그래서 다중 행 SUB QUERY의 경우 아래와 같이 별도의 연산자가 존재함

SAL > ANY (100,200,300) 으로 되면 ANY 자리에 최소값인 100이 반환

SAL > 100이라는 식으로 표현

SAL < ALL (100,200,300) 이 되면 SUB QUERY의 최소 값인 100이 반환되긴 하지만 식은

SAL < 100이 되 버림

--EMP 테이블의 부서 별로 가장 급여를 많이 받는 사원의 사원번호 , 이름, 급여 , 부서번호를 조회하여라

SELECT EMPNO,ENAME,SAL,DEPTNO

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

--다중 행 SUB QUERY

--EMP2 테이블과 DEPT2 테이블을 참조하여 근무지역(DEPT2 테이블의 AREA 컬럼)이 서울 지사인 모든 사원들의

--사번과 이름, 부서번호를 출력

SELECT EMPNO, NAME, DEPTNO

FROM EMP2

WHERE DEPTNO IN (SELECT DCODE

FROM DEPT2

WHERE AREA = '서울지사');

-- 업무가 SALESMAN 인 최소 한 명 이상의 사원보다 급여를 많이 받는 사원의 이름, 업무를 조회하여라

SELECT ENAME, JOB, SAL

FROM EMP

WHERE SAL > ANY( SELECT SAL

FROM EMP

WHERE JOB = 'SALESMAN')

AND JOB !='SALESMAN';

-- 다중 행 SUB QUERY 예 2

-- EMP2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급,

-- 연봉을 출력하세요. 단 연봉 출력 형식은 아래와 같이 천 단위 구분기호와 원 표시를 하세요

SELECT NAME, POSITION, PAY

FROM EMP2

WHERE 1=1

AND POSITION != '과장'

AND PAY > ANY(SELECT PAY

FROM EMP2

WHERE POSITION = '과장');

-- 다중 행 SUB QUERY 예 3

-- STUDENT 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중이 4학년 학생들의 체중에서 가장 적게

-- 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요.

SELECT NAME, GRADE, WEIGHT

FROM STUDENT

WHERE WEIGHT < ALL(SELECT WEIGHT

FROM STUDENT

WHERE GRADE =4)

■  다중 컬럼 SUB QUERY

서브쿼리로 출력되는 컬럼수가 2개 이상 출력되는 서브 쿼리

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

-- 다중 컬럼 SUB QUERY 1

-- STUDENT 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요

SELECT GRADE,NAME,HEIGHT

FROM STUDENT

WHERE (GRADE,HEIGHT) IN (SELECT GRADE,MAX(HEIGHT)

FROM STUDENT

GROUP BY GRADE)

ORDER BY 1;

-- 다중 컬럼sub query 연습문제 1:

-- PROFESSOR 테이블을 조회하여 각 학과 별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요

-- (학과이름순으로 오름차순 정렬)

SELECT P.NAME, D.DNAME

FROM PROFESSOR P , DEPARTMENT D

WHERE (P.DEPTNO,P.HIREDATE) IN ( SELECT DEPTNO,MIN(HIREDATE)

FROM PROFESSOR

GROUP BY DEPTNO )

AND P.DEPTNO = D.DEPTNO

ORDER BY D.DNAME ;

-- 다중 컬럼 SUB QUERY 연습문제 2

-- EMP2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름이 직급, 연봉을 출력하세요

-- 연봉순으로 오름차순 정렬하세요

SELECT NAME, PAY

FROM EMP2

WHERE (POSITION,PAY) IN ( SELECT POSITION,MAX(PAY)

FROM EMP2

GROUP BY POSITION)

ORDER BY PAY;

-- 다중 컬럼 SUB QUERY 연습문제 3 -- 다중행 컬럼 서브쿼리

-- EMP2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의

-- 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

-- ( 단, 연봉이 적은 순으로 나열하세요)

SELECT D.DNAME,E.NAME,E.PAY

FROM EMP2 E, DEPT2 D

WHERE PAY < ALL (SELECT AVG(PAY)

FROM EMP2

GROUP BY DEPTNO)

AND D.DCODE = E.DEPTNO

ORDER BY PAY;

--STUDENT 테이블을 이용하여 사용자 아이디가 BINGO인 학생과 같은 학년인 학생의

--학번, 이름, 학년을 출력하여라

SELECT STUDNO, NAME, GRADE

FROM STUDENT

WHERE GRADE = (SELECT GRADE

FROM STUDENT

WHERE ID = 'bingo');

--STUDENT 테이블을 이용하여 101번 학과 학생들의 평균 몸무게보다 적은 학생의 이름, 학과번호,몸무게를 출력하여라.

SELECT NAME, DEPTNO1, WEIGHT

FROM STUDENT

WHERE WEIGHT < ( SELECT AVG(WEIGHT)

FROM STUDENT

WHERE DEPTNO1 = 101 );

--STUDENT,DEPARTMENT 테이블을 이용하여 정보(PART:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라

SELECT STUDNO, NAME, DEPTNO1

FROM STUDENT

WHERE DEPTNO1 IN (SELECT DEPTNO

FROM DEPARTMENT

WHERE PART = 100);

--STUDENT 테이블을 이용하여 모든 학생 중에서 4학년 학생 중 키가 제일 작은 학생보다 키가 큰 학생의

--학번, 이름, 키를 출력하여라

SELECT STUDNO, NAME, HEIGHT

FROM STUDENT

WHERE HEIGHT > ANY( SELECT HEIGHT

FROM STUDENT

WHERE GRADE = 4);

-- STUDENT 테이블을 이용하여 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라

SELECT NAME, GRADE, WEIGHT

FROM STUDENT

WHERE (GRADE,WEIGHT) IN (SELECT GRADE,MIN(WEIGHT)

FROM STUDENT

GROUP BY GRADE);

■  상호 연관 SUB QUERY 

- 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리

- 메인쿼리와 서브쿼리간의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결

*주의*

   행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저하될 수 있음

--상호 연관 SUB QUERY

-- EMP2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의

--이름과 직급, 현재 연봉을 출력하세요

SELECT NAME, POSITION, PAY

FROM EMP2 A

WHERE PAY >= (SELECT AVG(PAY)

FROM EMP2 B

GROUP BY A.POSITION = B.POSITION);

■ 스칼라서브쿼리 : SELECT절에서 함수처럼 사용되는 쿼리문

- 하나의 레코드만 리턴이 가능( 반환값은 한 개) 두 개이상의 레코드는 리턴 X

- 일치하는 데이터가 없더라고 NULL 값을 리턴 할 수 있다.

SELECT D.DEPTNO, (SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO = D.DEPTNO)

FROM DEPT D

ORDER BY D.DEPTNO;

■ 인라인뷰(상호연관 SUB QUERY 보다 성능이 좋음)

- FROM 절에서 임시 공간에 테이블을 생성하여 사용하는 뷰와 비슷한 저장 형태

- FROM 절에 있는 서브쿼리가 인라인 뷰를 생성

- 효율적인 검색 가능

- FROM 절에 있는 서브쿼리에는 자주 별칭 사용

--STUDENT 테이블을 이용하여 각 학년 별로 평균키보다 큰 학생들의 이름, 학년, 키를 출력하여라

SELECT NAME, A.GRADE, HEIGHT

FROM STUDENT A, (SELECT GRADE,AVG(HEIGHT) AS "B_HEIGHT"

FROM STUDENT

GROUP BY GRADE) B

WHERE A.HEIGHT > B_HEIGHT

AND A.GRADE = B.GRADE

ORDER BY GRADE,HEIGHT;

JOIN

■ Cartesian Product(카티션 곱)

Join 쿼리 중에 WHERE 절에 기술하는 Join 조건이 잘못 기술 되었거나 아예 없을 경우 발생

ex) select e.first_name, d.department_name

        from employees e, departments d;

* employees : 107  , departments : 27 개의 row가 있다면 (107x27)개의 결과를 출력하게 됨

■ EQUI Join

양쪽 테이블에 같은 조건의 값이 존재할 경우 해당 데이터를 가져오는 JOIN 방법

Equal 연산자(=)를 사용해서 EQUI Join 이라고 함

select e.first_name , d.department_name
from employees e, departments d
where e.department_id = d.department_id
  and e.department_id = 110;

* 두 테이블을  JOIN 하여 dpartment_id가 110 인 row만 출력하게 함