생각정리

시퀀스( SEQUENCE ) 

- 자동번호 발생기(유일한 숫자)

- 역할 : 번호를 만들어주는 것

CREATE SEQUENCE 테이블명_컬러명

START WITH 시작값

INCREMENT BY 증가값

MINVALUE 최소값 

MAXVALUE 최대값

NOCYCLE / CYCLE 순환

NOCACHE / 

ex) 시퀀스

1. START WITH 1부터

2. INCREAMENT 증가

3. MINVALUE 1 1부터

4. MAXVALUE 100 100까지

5. NOCYCLE CYCLE시 MINVALUE값으로 복귀 > 중복값 발생

6. NOCACHE 뭉텡이로 가져와라

NEXTVAL  : PSEUDO 컬럼

- INSERT문에 포함시켜 사용

- 안 쓰는 것도 아니지만 번호를 주어야 할때 사용

- 공유가능한 객체 (여러 테이블에서 사용 가능)
- 응용프로그램에서 DB로 데이터 INSERT 할때
- PK컬럼의 VALUE 값 지정시 사용
- 번호의 GAP

   ㅇ DML ROLLBACK, 여러 테이블에서 동일한 SEQUENCE를 같이 사용시

인덱스(INDEX)

CREATE INDEX IDX_테이블명_컬럼명 
ON 테이블명 (컬럼명);

- 자주 검색되는 컬럼의 이름과 ROWID

- 테이블과는 별도의 저장장소를 사용

- 모든 컬럼에 INDEX를 만들면 SELECT문의 속도가 빨라진다?

  ㅇDML이 발생하면 INDEX에서 자동 수정

     ㅁ SELECT 속도 빨라지나 DML 엄청 느려짐 / 저장장소 낭비

     ㅁ 꼭 필요한 컬럼에만 사용 할 것

DML 속도가 떨어지는 것을 감수하면서 SELECT 속도가 향상되기를 기대

제한적으로 만듬

- 자주 검색되는 컬럼 ( WHERE 조건)

  ㅇ JOIN 조건으로 사용되는 컬럼

  ㅇPK, UK 제약이 걸려있는 컬럼 --> 항상 자동으로 INDEX를 생성

  ㅇ 분포가 고른 컬럼에 걸어준다.

      ㅁ 데이터가 비율이 월등하게 몰려있는 컬럼에 사용시 속도가 오히려 나빠짐

ex)책과 DB 비교

속도

DB SEARCH 방식

ROWID SEARCH 방식

- DB가 할 수 있는 가장 좋은 성능을 내는 SQL문 문장

FULL TABLE SCAN 방식

- 모두 읽어내기 때문에 성능이 가장 느림.

- ex) 책에서 한 페이지씩 확인

DB의 실행계획 확인 방법 (SQL 명령 진행 출력)

AUTO TRACE : SQL을 튜닝하는데 필요한 정보 출력

- 테이블(PLAN_TABLE)에 정보 저장

인덱스 D.D 조회

- 자동으로 생성된 인덱스 삭제 안 됨
  ㅇ 내부구현상 인덱스를 쓰고 있기 때
- 제약을 제거하면 인덱스도 자동으로 제거

- 인덱스의 저장구조 내부적구조 B TREE구조 이다

SYNONYM

- 동의어 ex) 태양, 해

- 오브젝트의 별칭(테이블의 별칭)

DCL(DATA CONTROL LANGUAGE) : GRANT, REVOKE 사용자 계정 권한부여

- 지정한 계정이 권한의 부여 및 회수 가능 

1. SYSTEM PRIVILEGE : DB전체에 영향, 모든 권한 : DBA

- 부여 GRANT 권한 TO 사용자명 ;

 ㅇ CONNECT 접속 권한 

 ㅇ RESOURCE 자원사용에 대한 권한 2개 이상의 권한은 , 콤마

- 회수 REVOKE 권한 FROM 사용자명 ;

2. OBJECT PRIVILEGE : OBJECT에만 국한 : 생성자

- GRANT 권한 ON OBJECT명 TO 사용자명

- REVOKE 권한 ON OBJECT명 FROM 사용자명

  ㅇ 모든 OBJECT X

DDL, DCL : AUTO COMMIT 발생

사용자 계정 생성 및 권한 부여

- USER1 생성 및 접속과 자원사용 권한 부여

- SELECT문 허가 필요 / 오브젝트 생성자에게 권한 획득

- SCOTT 'EMP' SELECT 권한을  USER1에게 부여

- 오브젝트 앞에 생성자의 USER_NAME을 입력해야 출력이 됨

- OBJECT 업데이트 권한 부여

- USER1 OBJECT 권한과 접속 권한 회수

계정 생성 다른 설정

- PASSWORD EXPIRE : 접속시 비밀번호 변경

  ㅇ PASSWORD 키워드 입력시도 변경 가능

  ㅇ 보안상 비밀번호 보이지 않음.

- ACCOUNT LOCK / UNLOCK 계정 잠금 및 잠금 해제

사용자 계정삭제

- 오브젝트(테이블) 하나라도 있으면 지워지지 않는다.

- CASCADE 사용 완전 삭제

DB에서 원격지로 접속하기

D:\app\LJY\product\11.2.0\dbhome_1\NETWORK\ADMIN

client : tnsnames,ora 수정

server : listener.ora 수정

방화벽에서 오라클의 접속만 허용

제어판 설정 > 방화벽 > 고급설정 > 인바운드규칙 > 새규칙 >

1.%SystemDrive%\app\JHTA\product\11.2.0\dbhome_1\BIN\oracle.exe 

2.%SystemDrive%\app\JHTA\product\11.2.0\dbhome_1\BIN\LSNRCTL.EXE 

3. 포트번호 오라클 1521 MYSQL 3306

접속방법 : SQLPLUS SCOTT/tiger@db1 @원격접속 표시자 

TIP.

1. sql파일을 prompt에 넣으면 주소가 나온다.

2. DROP 명령어는 실행시 고민 할 것

복습

QUEARY : SELECT

DML : INSERT, UPDATE, DELETE

TCL : COMMIT, ROLLBACK, SAVEPOINT

DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT

1. DATA DICTIONARY

2. USER-DEFINE

DATA DICTIONARY

- 자원의 효율적인 관리를 위해 데이터를 모아 놓는 곳(시스템 테이블)

- 저장할 데이터에 대한 관리정보를 가지고 있음

ㅇ 특징

- DATA DICTIONARY : META - DATA 

- 오라클 설치시에 자동으로 저장
- 오라클이 유지, 관리 (직접 수정, 삭제 불가)
- QUERY : 정보를 검색하는정도만 한다.

- 절대 DML 할 일 없다.

ㅇ DATA DICTIONARY 조회 요령
- 접두사 + 관심KEYWORD + 복수형 
- 접두사에 따라 접근범위가 달라짐 

ㅇ 종류 : USER_, ALL_, DBA_, V$ 
- USER_ 내가 생성한 것과 관련된 정보
- ALL_ 접근권한이 있는 모든것과 관련된 정보 

  ㅇ 일반 개발자 초급 USER 고급 ALL
- DBA_ DBA만 접근해야하는 관련된 정보 
- V$ 동적성능(메모리사용량, 동시접속자 등) 관련된 정보 

  ㅇ 관리자 DBA, V$

ㅇ 데이터 딕셔너리뷰

- 데이터 딕셔너리의 내용을 사용자가 이해할 수 있는 내용으로 변환하여 제공

ㅇ DBA_데이터 딕셔너리

- 관리자만 접근 가능한 객체 등의 정보를 조회

CONNECT SYSTEM/oracle

- 관리자 계정 전환

DESC DBA_USERS

- 사용자에 관한 정보를 가지고 있음

- scott은 볼 수 있는 권한이 없다.

SELECT USERNAME , PASSWORD

FROM DBA_USERS;

- 유저의 계정의 상태와 암호

SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS

-계정의 잠김유무 확인

ㅇ USER_TABLES 데이터 딕셔너리

- 자신이 생성한 테이블, 인덱스, 뷰 등과 같은

  자신의 계정이 소유한 객체의 정보를 저장

DESC USER_TABLES

- 컬럼을 보기 위해 작성

SELECT TABLE_NAME
FROM USER_TABLES;

- 내가 만든 테이블에 대한 정보

뷰 ( VIEW ) 

- 물리적 테이블의 근거한 논리적 가상테이블 역할

   ex)창문을 통해 비치는 배경

- SELECT문 몇 개로 아주 작은 바이트로 테이블을 복사해놓는 효과

- 별도의 저장장소 부여하지 않고 실행하는 순간 서브쿼리 동작 
- 원본이 수정되면 같이 수정된 것 처럼 보임 

- 뷰는 실제 테이블의 일부를 잘라 보여준다.

- 자주 쓰는 것에 이름을 부여

장점

- 보안에 유용 (ex)부서별로 볼 수 있는 정보의 제한)
- 액세스의 단순화 (ex)알바가 몰라도 할 수 있다)

뷰 생성

ORA-01031 : 권한이 불충분합니다. 

- 에러 발생

GRANT CREATE VIEW TO scott; 스코트에게 뷰 권한 부여

- 관리자로 접속 권한 부여

CREATE VIEW(TABLE) 테이블명
AS 
SELECT 컬럼명
FROM 테이블명 
WHERE 조건(condition)

- 서브쿼리를 이용 뷰의 생성

DESC USER_VIEWS

- 데이터 딕셔너리 뷰 조회

- 자신의 소유한 뷰의 컬럼 확인

SELECT TEXT 
FROM USER_VIEWS 
WHERE VIEW_NAME='EMPVW30'

- EMPVW30뷰의 정보를 개시

뷰의 수정

CREATE OR REPLACE VIEW 테이블명

AS

SELECT 컬럼명

FROM 테이블명

WHERE 조건(CONDITION)

- EMPVW30 있으면 CREATE 없으면 REPLACE로 대체

- 평상시 뷰를 생성 할 때 사용 가능

데이터베이스의 중요한 성질

1. 동시성

2. 무결성

동시성 : 동시에 여러사람

1. 부산B가 UPDATE를 통해 SCOTT의 월급을 200으로 변경 > COMMIT 실행 안함

2. 서울A가 UPDATE를 통해 SCOTT의 월급을 100으로 변경 > LOCK 상태

   ㅇ 자원을 독점적가지고 있으면 다른 사람이 일 할 수 없다. 

ex) 하나의 화장실 2명의 사용자
     

LOCK

- 트랜잭션간 상호 파괴적인 행위를 막기위한 오라클의 보호관리 메카니즘

특징
- 최소단위로 지정 ex)DML > LOW 기본적인 LOCK
- AUTO 자동으로 지정
- 관리자는 데이터베이스 및 테이블 자체를 잠궈버릴 수 있다.

- 최저 레벨이 로우 단위(컬럼이 아닌 로우)

DEAD LOCK

- 서로 WAIT하는 상태

- 오라클 자동 개입
  ㅇ 먼저 기다리는 사람의 마지막 명령어 한개 취소를 하면서 중재

- 업무프로세스의 문제

해결방법
- 시간 조절
- 업무이관

무결성

- 데이터베이스 내에 있는 데이터의 정확성 유지

- 테이블 생성시 부여된 성격 유지 > 간단한 DML에도 훼손되기 쉬움

DML의 제약조건 ( CONSTRAINT ) 

- 바람직하지 않은 데이터가 저장되는 것을 방지

- 테이블의 지정된 성격을 DML 할 때마다 검사하고 맞으면 진행

  맞지 않으면 STOP

 ㅇ 제약은 변경이 없다

 ㅇ 하나의 컬럼에 여러가지 제약 가능

제약조건 종류

제약조건 입력

1. 테이블 정의시

2. 테이블 생성후에 추가

테이블 정의시

1.1 컬럼레벨 정의 방식

- 컬럼명 DATATYPE [CONSTRAINT 제약명] 제약 종류

  REFERENCES 테이블명 (컬럼명) < FOREIGN KEY 

- 만약 제약명을 생략했다면 제약의 이름 X

  ㅇ 오라클이 제약명을 SYS_C숫자 형식 임의로 부여

1.2 테이블레벨 정의 방식

- 컬럼명 DATATYPE,

  컬럼명 DATATYPE,

  CONSTRAINT 제약명 제약 종류 (컬럼명),

  CONSTRAINT 제약명 제약 종류 (컬럼명),

- 단점 : 어느 컬럼인지 알 수 없다.

ex) 제약조건 입력 및 확인 예제

제약조건에 대한 정보를 확인
- 데이터 딕셔너리의 이름

  DESC USER_CONSTRAINTS 데이터 딕셔너리의 이름

- 오라클은 5가지 제약을 4가지로 표현 내부적 처리

  NOT NULL : CHECK의 특수한 일종

- 관리를 위해서 제약에 대한 이름을 잘 줄 것

- 부모키(FOREIGN KEY) 

parent key / master key, child key / salve key

테이블 생성후에 추가

ALTER TABLE 테이블명

ADD 테이블 레벨 정의 방식

제약 삭제

ALTER TABLE DEPT1
DROP CONSTRAINT DEPT1_LOC_UK;

- 테이블 전체를 삭제하면 제약 삭제

ROWNUM ( 의사열 / PSEUDO 컬럼 ) ROW ID ( 로우의 고유주소 )

- 각 행에 대한 일련번호 

- 입력된 순서대로 번호를 부여

- 조건을 만족하면 진행 데이터 조건을 만족하지 못하면 중단 > 내부적 스탑키

- 항상 작거나 같다 라는 식으로 적용 ( <= )

  ex)WHERE ROWNUM = 3 멈춰버림

- 어떤 테이블이든 가능하다.

- ROWNUM 오라클에만 있다 / 각 DB마다 키워드 다름. (통상 LIMIT QUERY)

- DISTINCT, ORDER BY, GOUPY BY 등이 있는 뷰 ( ROWID 사용불가 )

 - 입력된 순서대로 번호를 뽑아 부여하기 때문에 입력 후에 진행되는 ORDER BY를

   통해 정렬할 경우 부여된 번호의 순서가 바뀐다. 

ROWNUM을 순서대로 정렬하는 해결방법은?

인라인 뷰 ( INLINE VIEW )

- 쿼리문 내부에 뷰를 정의하여 사용

- SELECT문의 FROM절에 서브쿼리(SELECT문)

  ㅇ CREATE 명령어 사용하지 않음

- 1회용 뷰(일시적 사용)

SELECT 컬럼명

FROM ( 서브쿼리 SELECT문 )

WHERE 조건(condition)

용어

1. CONSTRAINT 제약조건

2. TOP-N쿼리

TIP

1. COL 컬럼명 FORMAT A10   

2. 오라클에서는 뷰를 많이 쓸 것을 권장.

3. DELETE 뒤에 FROM 생략 가능

4. 데이터베이스 : 동시에 여러사람, 운영체제 조작, 처리 될 수 있는 형태

5. SET SQLPROMPT "_USER>" : 현재 접속자 표기

문제

1교시

클라이언트 SQL DB

QUEARY : SELECT

종합문제

 SELECT RPAD(ename, 8, ' ') || lpad(' ',ROUND(SAL/100,0)+1, '*')
           || '(' || SAL || ')' "사원별 급여 현황"
 FROM emp
- 영업사원 판매현황시 적용

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

종류 : INSERT, UPDATE, DELETE

INSET : 테이블 ROW 추가

INSERT INTO 

VALUES (   )

ㅇ 암시적 삽입법

INSER INTO DEPT(DEPTNO, DNAME)

VALUES ( 60, 'C');

지정되지 않는 컬럼은 NULL값 대입

ㅇ 명시적 삽입법

INSERT INTO DEPT

VALUES (70, NULL, 'D')

주의 'NULL' 문자가 됨

UPDATE : 테이블 ROW 변경

update dept

set dname = 'A'

where deptno = 70;

DELETE : 테이블 삭제

delete dept

where dept in(50, 60 ,70);

3교시

TCL (Transaction Control Language) : 데이터 제어 언어

종류 : COMMIT, ROLLBACK

- 트랜잭션 : 일련의 작업처리를 위한 연관된 DML의 모음

  ㅇ 성격 : ALL or NOTHING ex) A은행 => B은행 입출금

- commit 명령어를 쓰기전까지 DB에 반영 안 됨.

  ㅇ DB는 여러 사람, 어플리케이션이 동시에 작업(무결성 유지)

- TCL을 만나면 현재 트랜잭션을 종료

  ㅇ 다음번 첫 SQL문을 만나면 새로운 트랜잭션이 시작

- 비정상 종료 AUTO ROLLBACK / 정상 종료 AUTO COMMIT / CREATE

- 굉장히 긴 트랜잭션일 경우

  ㅇ SAVE POINT를 통해 부분 ROLLBACK 가능 ex)입력값 오류 / ANSI 표준 X

- 한 번 뒤로 가면 다시 돌아 올 수 없다.

DDL(DATE DEFINITION LANGUAGE) : 데이터 정의어

종류 : CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT

- DDL 사용시 AUTO COMMIT 발생 ROLLBACK 불가

CREATE

주의사항

1. 시작은 반드시 영문자이여야 한다.
2. 예약어(SELECT 등)는 사용할수 없다.
3. 유일한 단어이어야 한다. (중복이 되면 안된다)
4. _, $, #은 사용가능하다.

ㅇ TABLE 생성

1. EMPS 테이블 생성

2. 컬럼 EMPNO, ENAME, SAL, HIREDATE

3. 숫자 4자리 컬럼

4. 문자 가변형 문자 20자

  - CHAR 고정문자 ex) CHAR(8) ABC입력 남은공간 유지

    ㅇ 장점 : 읽는 속도가 빠름(길이 고정)

    ㅇ 단점 : 공간낭비가 있음.

  - VARCHAR2 가변문자 ex) VARCHAR2(8) ABC 입력 남은 공간 유지

    ㅇ 장점 : 공간효율면에서 우수함

    ㅇ 단점 : 읽는 속도가 느림.

5. number(7, 2) 7자리까지 표현하는데 5자리는 정수 2자리는 소수점 표현

ㅇ 전체복사(서브쿼리 활용)

CREATE(생성할 때) TABLE COPY_EMP2 (COPY_EMP2 테이블을)

AS (처럼 하여라)

SELECT * FROM EMP; (EMP의 모든 컬럼처럼)

ㅇ 테이블 구조 복사 ROW값 비우기 (3가지 방법)

1. WHERE EMPNO = 9999;

2. WHERE EMPNO = NULL

3. WHERE 0 = 1

ㅇ 테이블 구조 전체 입력값 넣기(INSERT문에 서브쿼리 활용)

ㅇ 부분복사(서브쿼리 + WHERE문 추가)

CREATE(생성할 때) TABLE COPY_EMP2 (COPY_EMP2 테이블을)

AS (처럼 하여라)

SELECT * FROM EMP; (EMP의 모든 컬럼처럼)

WHERE DEPTNO = 10;

ALTER ( 변경 )

- 컬럼을 추가(ADD), 수정(MODIFY), 삭제(DROP) 

ㅇ ADD : 테이블 COLUMN 추가

ALTER TABLE EMP

ADD (컬럼명 데이터타입(  )); 

ㅇ RENAME : 테이블 COLUMN명 변경

ALTER TABLE EMP

RENAME COLUMN 컬럼명 TO 바꿀 컬럼명;

ㅇ MODYFY : 테이블 COLUMN 데이터타입 변경

ALTER TABLE EMP

MODIFY (컬럼명 데이터타입(  ));

- 자료를 줄이는 건 불가

- 숫자형(NUMBER) => 문자형(CHAR) 속성 변경시

   컬럼 안에 데이터 있을 시 변경 불가(CHAR형과 VARCHAR2형 사이는 가능)

ㅇ DROP : 테이블 COLUMN 삭제

ALTER TABLE EMP

DROP COLUMN 컬럼명;

ㅇ 부하가 많이 걸리고, 명령 종료전까지 테이블 사용 불가

- 논리적 데이터 삭제(물리적 데이터 유지)

ALTER TABLE EMP
SET UNUSED(컬럼명);

- 물리적 데이터 삭제

ALTER TABLE test2
DROP UNUSED columns;

DROP( 테이블 삭제 )

- DDL은 AUTO COMMIT ROLLBACK이 안 됨

- 존재 자체를 지움

테이블 삭제 

DROP TABLE COPY_EMP;

테이블 완전 삭제

DROP TABLE COPY_EMP PURGE;

ㅇ FLASHBACK ( 테이블 삭제 복구 )

- 해당 테이블을 드랍하기 직전으로 돌려줌

- DB마다 조금씩 다름

FLASHBACK TABLE COPY_EMP

TO BEBORE DROP;

ㅇ SHOW RECYCLEBIN(휴지통) 

ㅇ PURGE RYCYCLEBIN(휴지통 비우기)

RENAME ( 테이블명 변경)

RENAME COPY_EMP2 TO CEMP;

TRUNCATE ( 모든 RAW를 삭제 )

TRUNCATE TABLE CEMP;

- TRUNCATE 모두 삭제, 일부 삭제 x DDL  조건 부여 불가, 백업 x 속도 빠름

  ㅇ 차이점 DML DELETE CEMP; : ROLLBACK 가능, 조건 부여 가능, 

COMMENT ( 주석 생성 )

- 4000자까지 가능

COMMENT ON TABLE EMP 
IS 'EMPLOYEE TABLE';

COMMENT 주석 보기

DESC USER_TABLE_COMMENTS

SELECT * 
FROM USER_TAB_COMMENTS;

용어

condition : 조건

TIP.

1. 폭 조절 ex) COL "근속 기간" FROMAT A15 SQLPLUS 명령어

2. 순차대로 진행하면서 규칙을 찾자

3. 서브쿼리 조건 개수 확인

4. sqlplus 로그인부터 종료까지 하나의 트랜잭션

5. 현재 접속자로 표기 

5. ACCESS(MDB) 통상 DB라 하지 않는다.

6. MS-DOS WIN3.1 ( WIN NT > WIN 2000 ) WIN95 SE WIN ME

   WIN XP(보안패치 단종, NTFS(서버급 보안기능) VISTA 7 8 10

7. DB는 10의 9승 1초 단위로 히스토리가 남는다.

과제 1

1. 사원번호 7499 번사원의 월급을 5000으로 바꿔라 ?

2. 20번 부서의 사원들의 월급을 4000 으로 바꿔라 ?

3. 부서 테이블에 부서번호 50, 부서위치 Boston , 부서명 reserch 로 해서 data 를 입력하라 ?

4. 사원번호 7698 번 사원의 부서 번호를 7499번 사원의 부서번호로 바꿔라 ?

5. 10 번 부서의 사원들의 커미션을 smith 의 커미션으로 바꿔라 ?

6. 사원 테이블에 data 를 집어 넣는데 사원번호를 9901 이라고 하고 이름을 jackson , 직업을 salesman ,

   부서번호를 10 번 이라고 해서 data 를 집어 넣어라 ?

7. job 이 salesman 인 사원의 사원번호와 이름 과 직업을 사원테이블에 insert 하여라 ?

8. 7369번 사원과 같은 직업을 가진 사원들의 월급을 7698 번 사원의 월급으로 수정하여라 ?

9. jackson 사원의 data 만 삭제 하여라 ?

10. data 를 집어 넣는데 사원 번호를 9902, 이름을 동수 , 그리고 입사일을 현재 날짜로 집어 넣어라 ?

과제2

JOIN SQL99(버전)

CROSS JOIN

- WHERE절에 공동되는 컬럼에 의한 조인이 발생되지 않아 모든 데이터가 검색 결과로 나타나는 경우

  ex) EMP로우 14개 * DEPT로우 4개 WHERE절에 올바른 조인 조건을 지정 필

NATURAL JOIN(EQUI JOIN)
- 2개의 테이블 중 똑같은 컬럼이 하나

- 컬럼 이름으로 자연스럽게 붙임

- SELECT절 공통으로 붙인 컬럼명 앞에 테이블 별칭을 쓰면 안 된다.

JOIN USING

- 컬럼이 같은게 2개 이상 
- 어떤걸 기준으로 붙일지 모르니까 USING 뒤에 컬럼명 지정

JOIN ON

- 컬럼명이 다름 내용과 데이터타입은 같음

- 컬럼 이름 같은게 하나도 없다면 사용불가

LEFT OUTER JOIN(=LEFT JOIN)

- 왼쪽 테이블의 것은 조건에 부합하지 않더라도 모두 결합

RIGHT OUTER JOIN

- 오른쪽 테이블의 것은 조건에 부합하지 않더라도 모두 결합

FULL OUTER JOIN

- 양쪽 모두에 조건이 일치하지 않는 것들까지 모두 결합

Multiple Row Funtion

- 다중 입력 하나 출력(그룹함수)

- 여러개의 값을 하나의 결과로 계산하는 함수

- 그룹 함수와 일반 함수 동시 출력시 충동 => 오류 발생

- NULL값은 자동적으로 제외(일반 함수와 다르게 대체 필요없음)

COUNT : 조건을 만족하는 행(RAW)의 개수

  ㅇ NULL값 제외

  ㅇ 중복시 DISTINCT 함수 활용 ex) SELECT count(distinct job) FROM emp; 

MIN / MAX : 최소값 / 최대값

  ㅇ MIN과 MAX는 문자와 날짜도 사용 가능(아스키코드값)

SUM : 합계

AVG : 평균값

GROUP BY

- 특정 컬럼을 기준으로 그룹화하여 테이블에 존재하는 행들을 그룹별로 구분하기 위해 사용한다.

- 그룹 함수를 쓰되, 어떤 컬럼값을 기준으로 그룹 함수를 적용할지 기술해야 한다.

HAVING ~

- GROUP BY 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 보고자 할 때 사용한다.

- HAVING 그룹에 대한 제한 / WHERE은 ROW에 대한 제한

문제

1. 각 부서별로 최대 급여를 구하세요.
2. 각 직급(job)별로 최대 급여를 구하세요.

3. 각 부서별로 평균 급여를 구하세요.
4. 각 직급(job)별의 인원수를 구하세요.
5. 각 부서별 인원수와 보너스(comm)을 받는사람을 구하세요. 
6. 각 년도별로 입사한 인원수를 구하세요.

7. 부셔별 평균급여를 구하고 그 결과 평균급여가 2000 이상인 부서만 출력하세요.
8. 급여가 1000 이상인 사람들로 부서별 평균급여를 구하고 그 결과 평균급여가 2000 이상인 사람만 출력하세요. 

SUBQUERY

- SELECT문에 포함되어 있는 SELECT문

- SUB > MAIN (괄호 안에 먼저 처리)

ex1)JONES 사원보다 더 많은 급여를 받는 사원의 이름, 급여?

ex2)BLAKE 사원보다 늦게 입사한 사원의 이름 입사일?

ex3)SMITH보다 월급을 많이 받는 사원들의 이름과 월급을 출력

ex4)10번 부서의 사원들과 같은 월급을 받는 사원들의 이름과 월급, 부서번호를 출력

ex5)BLAKE와 같은 부서에 있는 사람들의 이름과 입사일을 출력하시오(단, BLAKE는 제외)

Single Row SubQuery

- 서브쿼리가 실행된 후에 RETURN 하는 값이 하나일 경우

Multiple Row SubQuery

- 서브쿼리가 실행된 후에 RETURN 하는 값이 여러 개일 경우

Multiple Column SubQuery

- SELECT 절에 컬럼에 2개 이상 있을 경우

주의사항

1. (SubQuery) 괄호 안에 있어야 된다.

2. SRS => SRO(=비교연산자)

3. MRS => MRO(ex)IN)

4. Sub Query 안에서는 ORDER BY 절 => 의미가 없다.

   따라서 지운하지 않는다.

   (단, ORACLE 8i 버젼부터 from 절에 사용하는 Sub Query의 경우

    ORDER BY 지원함.)

문제

1.SMITH보다 월급을 많이 받는 사원들의 이름과 월급을 출력

2. 10번 부서의 사원들과 같은 월급을 받는 사원들의 이름과 월급과 부서번호를 출력

3. BLAKE와 같은 부서에 있는 사람들의 이름과 입사일을 출력하되 BLAKE는 제외

4. 평균급여보다 많이 받는 사원들의 사번, 이름, 월급을 출력하되 내림차순으로

5. 이름에 'T'를 포함하는 사원들과 동일한 부서에서 근무하고 있는 사원의 사원번호와 이름을 출력

6. 30번부서의 최고급여를 받는 사원보다 더 많은 급여를 받는 전체사원을 출력하라

7. 부서위치가 DALLAS인 모든 사원의 이름, 부서번호 및 직업을 출력하라

8. SALES부서의 모든 사원에 대한 부서번호, 이름 및 직업을 출력하라

9. KING에게 보고하는 모든 사원의 이름과 급여를 출력

10. 자신의 급여가 사원평균급여보다 많으면서, 이름의 'S'자가 들어가는 사원과 동일

    부서인 모든 사원의 사원번호, 이름 및 급여를 출력

1. 자신의 급여가 평균급여보다 많다.

2. 이름의 S자가 들어가는 사람과 동일부서

3. 1+2의 조건을 모두 만족하는 사원의 사번, 이름, 급여 ?

TIP.

1. 전체 데이터베이스 내용 출력하고 규칙성 찾기

2. SQL 명령어 버퍼에 사용 할 수 없다.

3. 문자를 숫자로 바꾸는 건 안 된다. ex TO DATE

숙제

연습문제
STEP1
사원명, 입사일,       근속일
SCOTT  80/01/01     32767

STEP2
사원명, 입사일,       근속월
SCOTT  80/01/01     376개월

STEP3
사원명, 입사일,       근속년 
SCOTT  80/01/01     23(년)      5

STEP4
사원명, 입사일,       근속기간
SCOTT  80/01/01     23년 5개월(글자까지 나오도록)

3. 종합문제

사원별 급여 현황

------------------------------------------------

KING ******* (5000)

SCOTT ***** (3000)

SMITH ****( 1650)

1. 컬럼 1개

2. * $100

3. 반올림

4. 급여는 (급여)

5. 급여가 많은순 내림차순

DUAL 테이블

- 한 행으로 결과를 출력하기 위한 테이블

- 산술 연산이나 가상 컬럼 등의 값에 사용

- 오라클에서 제공하는 테이블

FUNTION(함수)

• Single Row Funtion 한 가지 입력 한 가지 출력
• Multipl Row Function 다중 입력 한 가지 출력

Single Row Funtion

- 함수명(INPUT) 내부처리 OUPUT

- 함수의 이름이 기능을 대변한다.

- 종류 : 문자대소, 문자조작, 숫자

1. 문자대소 : UPPER, LOWER, INITCAP

- UPPER : 대문자

  ex)UPPER(ENAME) 

- LOWER : 소문자

  ex)LOWER(ENAME)

- INITCAP : 글자의 첫자만 대문자 나머지 소문자 (이니셜 부분만 캐피탈)

  ex)INITICAP(ENAME)

  ※ SQL 함수 아님 오라클 함수 

2. 문자조작 : SUBSTAR, INSTR, LENGTH, LPAD, RPAD

- SUBSTR : 문자의 일부분을 자르다

  ㅇ SUBSTR( [추출할곳], [추출시작위치], [추출글자수] )

  ex)이름 SMITH / SUBSTR(ENAME, 4)  

- INSTR : 문자의 위치 

  ㅇ INSTR( [검색할곳], [특정문자], [검색시작위치], [결과선택] ) 

  ex)이름 SCOTT / INSTR(ENAME, 'A') 없으면 0 있으면 표기

- LENGTH : 문자의 길이 'SMITH' => 5

  ex)LENGTH(ename) 이름의 길이 출력 

- LPAD : 문자열 자릿수 채우기(왼쪾)

  ex) LPAD(SAL, 10, '*') 10자리에서 급여를 쓰고 왼쪽으로 채우다. 

- RPAD : 문자열 자릿수 채우기(오른쪽)

  ex) RPAD(SAL, 10, '*') 10자리에서 급여를 쓰고 오른쪽으로 채우다.

- CONCAT : 두 함수를 연결

  ex) 이름 ALLEN, 직업 SALESMAN / CONCAT(ENAME, JOB) ALLENSALESMAN

문제

1. emp 테이블에서 사원 이름(ename)을 소문자로 출력하세요.
2. emp 테이블에서 사원 이름의 첫글자만 대문자로 출력하세요. 
3. emp 테이블에서 사원 이름의 길이를 출력하세요.
4. emp 테이블에서 사원이름과 이름에 A가 몇번째 있는지 출력하세요.
5. emp 테이블에서 세번째 자리가 R인 사원의 정보를 출력하세요.
6. emp 테이블에서 이름의 끝자리가 N으로 끝나는 사원의 정보를 출력하세요.
7. AND나 BETWEEN을 사용하지 않고 87년도에 입사한 직원의 이름과 월급을 출력하세요.
8. emp 테이블에서 사원의 이름이 5글자인 사원의 이름을 첫글자만 대문자로 출력하세요.

3. 숫자 : ROUND, TRUNC , MOD

- ROUND : 반올림(소수점 기준)

ex) ROUND(45.129 ,2) 소수 두번째 자리 => 45.13

- TRUNC : 절삭(소수점 기준)

ex) TRUNC(45.192 ,1) 소수 첫번째 자리 => 45.1

- MOD : 나누어서 몫이 아닌 나머지를 구함

ex)MOD(101, 2) => 1

※ 회계업무에서 주로 사용

문제

1. 45.564을 소수 세번째 자리에서 반올림하여라.
2. 45.564를 반올림하여 정수로 만들어라.

3. 45.564를 1의 자리에서 반올림 하여라.
4. 45.564를 소수 첫번째 자리까지만 남기고 버림하여라.

5. 1200을 14로 나눈 나머지를 구하여라.
6. emp의 월급컬럼(sal)을 100으로 나눈뒤 그 나머지를 출력하여라.
7. emp에서 사번이 홀수인 사람의 정보를 출력하여라.

4. 날짜 : SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY

- SYSDATE : 오늘날짜, 현재시간

 ㅇ +와 - : + 다음 날 - 이전 날 ex)sysdate + 1, sysdate -1

ex) SYSDATE - HIREDATE 기간(ex)근속일수)

 ㅇ SYSDATE + HIREDATE 구현 안함(의미없음) 

 ㅇ 연월일만 표기하나 실제로 시분초도 나타냄.

ex)

- MONTHS_BETWEEN : 두 날짜간의 사이를 월 수로 표현

ex) MONTS_BETWEEN(SYSDATE, HIREDATE) 중첩함수(함수 안에 함수)

    ※ 항상 안 쪽부터 시작

- ADD_MONTHS  : date에 개월을 더한 date를 계산한다

ex)ADD_MONTHS(SYSDATE, 6) 현재 기준 6개월 후 출력

- NEXT_DAY : 해당 date를 기준으로 명시된 요일에 해당하는 날짜를 반환한다.

ex)NEXT_DAY(SYSDATE, '금')

- LAST_DAY : 해당 월의 마지막 날 표기 ex)월급날

ex)LAST_DAY('19/01/01') => 19/01/31 표기 

문제

1. emp테이블에서 이름과 입사일자 그리고 현재날까지의 경과일을 산출하세요.
   (소숫점을 빼버리고 해딩이름을 경과일로 바꾸세요.)
2. 1번문제를 경과일을 개월수로 바꿔서 산출하세요.
  (소숫점을 빼버리고 해딩이름을 경과개월수로 바꾸세요.)
3. emp테이블에서 입사후 첫 휴일(일요일)은 언제일지 구하세요.
4. emp테이블에서 입사후 6개월이 지난날짜 바로 다음 일요일을 구하세요.

5. 자료형 : TO_DATE, TO_CHAR, TO_NUMBER

   방법 : TO_변환형(기존값, '형식') 바뀌는 방향의 자료형

- TO_DATE : 문자열을 날짜 형으로 변환

  ex) SELECT SYSDATE-TO_DATE('1989/09/20', 'YYYY/MM/DD') 문자를 날짜로 변환

- TO_CHAR : 날짜나 숫자형을 문자로 변환

  ex) SELECT TO_CHAR(SYSDATE, 'CC YYYY/MM/DD:HH24:MI:SS') 세기 연월일 시간, 분, 초로 변환

  ex) SELECT ENAME, TO_CHAR(SAL, '$999,999.99') $ > L =>\ 로컬 통화기호

- TO_NUMBER : 문자형을 숫자형으로 변환

문제

1. 오늘날짜를 "xx년 xx월 xx일" 형식으로 출력하세요.
2.  지금현재 몇시 몇분인지 출력하세요.
3. 이번년 12월 31일까지 몇일이 남았는지 출력하세요.
4. emp에서 급여를 출력하는데, 세자리마다 쉼표(,)를 찍고 맨 앞에 $를 표시하시오.

    (해딩을 "이름", "급여"로 바꾸어 출력하여라.)

6. 일반 : NVL, DECODE, (CASE)

- NVL : 컬럼의 값이 NULL일 때 표기된 숫자 혹은 문자로 표현한다.(NULL VALUE)

  - 모든 컬럼에 사용 가능

  - 문자 = 문자, 숫자 = 숫자 입력되는 데이터값 일치 ex) nvl(to_char(mgr, '9999'), 'CEO')

    ex) SELECT ENAME "사원명" , SAL*12+NVL(COMM, 1) "연봉" comm이 널값을 가지고 있으면 1을 대입하라

문제

1. emp에서 이름과 매니져번호(mgr)를 출력하는데 매니져번호가 null이면 ceo로 출력하세요.

- DECODE : 여러가지 경우에 대해서 선택할 수 있도록 하는 기능

  ㅇ DECODE ( 컬럼, 조건 , 입력값, / 조건, 입력값 / 나머지 ) "별칭"

  ex)SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.1, 'CLEARK', SAL*1.05, SAL*1.01) "UPSAL"

- CASE : 여러 가지 경우에서 하나를 선택하는 함수

 ㅇ CASE WHEN 조건 THEN 입력값 else 나머지 end "별칭"

  ex)SELECT ENAME, JOB, SAL, CASE WHEN JOB='MANAGER' THEN SAL*1.1 WHEN JOB='CLERK' THEN SAL*1.05 
      ELSE SAL*1.1 END "UPSAL2"

문제

1. emp테이블에서 이름,직책,직책수당(Up Sal)을 출력하는데

직책수당을 직책이 점원이면 5%, 매니져면 15%를 인상하고 나머지는 그냥 출력하시오.(DECODE함수)
2. emp테이블에서 이름,직책,직책수당(Up Sal)을 출력하는데

직책수당을 직책이 점원이면 5%, 매니져면 15%를 인상하고 나머지는 그냥 출력하시오.(CASE함수) 

JOIN

- 2개 이상의 테이블을 마치 하나의 테이블처럼 연결

  올바른 연결을 위해 조건을 부여 이러한 조건을 조인 조건이라고 한다.

  (FROM 절에 2개이상 테이블 => 가능한 모든 조합 생성 => 조건필요)

- 잘 못되면 중복되는 데이터 발생

- 조인시 각 컬럼에 주소를 지정해주면

- 테이블 명칭 축약 가능 

- 테이블 N 조인 조건 N-1

EQUI-JOIN : 값을 일치

ex)WHERE EMP.DEPTNO = DEPT.DEPTNO =연산자 

ex)SELECT ENAME, DNAME, EMP.DEPTNO 두 테이블에 동일한 컬럼시 명확히 지정

NonEqui-JOIN : 값을 비교

ex)WHERE SAL BETWEEN LOSAL AND HISAL = 연산자를 사용하지 않음 

SELF-JOIN(테이블 별칭)

- WHERE절은 RAW단위로 이동

- 같은 테이블에 다른 별칭을 달아 복사한 거 같은 효과

- 조건 : 공통되는 값 일치

ex)FROM EMP E, EMP C

OUTER-JOIN

- JOIN 결과에 NULL인값을 추가

ex)WHERE E.MGR = C.EMPNO(+)

- MGR에는 NULL값이 있고 EMPNO에는 NULL값이 없다. (+) 입력 값을 일치

문제

1. 사원 이름이 SCOTT인 사원의 사번(empno), 이름(ename), 부서명(dname)를 출력하세요.
2. 사원이름과 급여(sal)와 급여등급(grade)을 출력하세요.
3. 위 2번문제에서 부서명을 추가시켜 출력하세요.
4. 사원이름과 매니저의 이름을 아래와 같은 형식으로 출력하세요.
     "XXX"의 매니져는 "XXX" 입니다. 
5. 부서번호가 30번인 사원들의 이름, 직급(job), 부서번호(deptno), 부서위치(loc)를 출력하세요.
6. 보너스(comm)을 받은사원의 이름, 보너스, 부서명, 부서위치를 출력하세요.
7. DALLAS에서 근무하는 사원들의 이름, 직급, 부서번호, 부서명을 출력하세요.
8. 이름에 'A'가 들어가는 사원들의 이름과 부서명을 출력하세요.
9. 사원이름, 부서번호와 해당사원과 같은 부서에 있는 사원을 출력하세요.
   (해당사원과 같은부서에 있는 사원의 해딩을 '동료'라고 설정해주세요)

ORDER BY ( 정렬 )

- 오름차순 : ASC,  ORDER BY (기본값)

- 내림차순 : DESC

- 데이터를 꺼내올때 정렬해서 꺼내온다.

- 동시에 2개 이상 컬럼이 올 수 있다.

  ex)ORDER BY SAL DESC, ENAME DESC 1차정렬 2차정렬 중복되는 수 2번째 DESC로 정렬

    - 1차로 월급을 정렬하고 2차로 중복되는 월급에서 이름을 알파벳 순서로 정렬

- 컬럼명에 별칭을 써도 정렬 가능

- 출력되는 컬럼명을 제외하고도 정렬 할 수 있다.

- SELECT절에 순서(POSTIONJ)대로 정렬 할 수 있다.

- ORDER BY SELECT문의 가장 마지막에 위치

  ex)ORDER BY 3 DESC 

문제)

1. 부서번호가 30인 사람의 이름,사원번호,부서번호를 사원번호로 오름정렬하여라. 
2. 이름과 급여의 데이터를 급여가 많은 순으로 정렬하라. 
3. 부서번호로 오름차순 정렬한후 급여가 많은 사람순으로 이름,부서번호,급여를 출력하라. 
4. 부서번호를 내림차순 정렬한후 ,직업순으로 오름정렬,급여순으로 내림정렬하여라. (전제 데이터출력)

숙제 [ 문제 ]

조인 실습 문제

1. 사원들의 이름, 부서번호, 부서이름을 출력

2. 30번 부서의 사원들의 이름, 직업 부서위치 출력

3. 커미션을 받는 사원의 이름, 부서이름 및 부서위치를 출력

4. DALLAS에서 근무하는 사원의 이름, 직업, 부서번호, 부서이름을 출력

5. 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력

6. 사원이름과 그 사원의 관리자 이름을 출력하라 (단 컬럼 HEADING을 EMPLOYEE, MANAGER로 출력)

7. 사원이름과 직업, 급여, 급여등급을 출력

8. 사원이름과 부서명과 월급을 출력하는데 월급이 3000이상인 사원을 출력 

9. 사원이름, 부서번호와 같은 부서에 근무하는 동료사원들을 출력

10. BLAKE이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력

1. 부서번호가 10번인 부서의 사람중 사원번호, 이름,월급을 출력하여라.
2. 사원번호가 7369인 사람중 이름,입사일,부서번호를 출력하라.

3. 이름이 ALLEN 인 사람의 모든 정보를 출력하라.
4. 입사일이 81/05/01인 사원의 이름,부서번호,월급을 출력하라.
5. 직업이 MANAGER 가 아닌 사람의 모든 정보를 출력하라.
6. 입사일이 81/04/02 이후에 입사한 사원의 정보를 출력하라.
7. 급여가 $800 이상인 사람의 이름,급여,부서번호를 출력하라.
8. 부서번호가 20번 이상인 사원의 모든 정보를 출력하라.
9. 이름이 K로 시작하는 사람보다 높은 이름을 가진 사람의 이름을 출력하라.
10. 입사일이 81/12/09 보다 먼저 입사한 사람들의 모든 정보를 출력하라.
11. 입사번호가 7698 보다 작거나 같은 사람들의 입사번호와 이름을 출력하라.
12. 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름,월급,부서번호,입사번호를 출력하라.
13. 급여가 1,600 보다 크고, $3,000 보다 작은 사람의 이름,직업,급여를 출력하라.
14. 이름이 B와 J사이의 모든 사원의 이름을 출력하라.
15. 입사일이 81년 이외에 입사한 사람의 입사일과 이름을 출력하라.
16. 직업이 MANAGER와 SALESMAN인 사람의 이름과 직업을 출력하라.
17. 부서번호가 20,30 번을 제외한 모든 사람의 이름,사원번호,부서번호를 출력하라.

18. 이름이 S로 시작하는 사원의 사원번호,이름,입사일,부서번호를 출력하라.
19. 입사일이 81년도인 사람의 입사일,이름을 출력하라.
20. 이름 중 A자가 들어가 있는 사람만 입사번호,이름을 출력하라.
21. 이름이 S로 시작하고 마지막 글자가 T인 사람의 이름을 출력하라.
22. 이름의 두번째 문자가 A인 사람의 이름을 출력하라.
23. 커미션이 NULL인 사람의 이름과 커미션을 출력하라.
24. 커미션이 NULL인 아닌 사람의 이름과 커미션을 출력하라.
25. 부서번호가 30번 부서이고,급여가 $1,500 이상인 사람의 이름,부서번호,월급을 출력하라.
26. 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의 사원번호,이름,부서번호를 출력하라.
27. 급여가 $1,500 이상이고,부서번호가 30번인 사원중 직업이 MANAGER인 사람의 급여,부서번호,직업을 출력하라.
28. 부서번호가 30인 사람의 이름,사원번호,부서번호를 사원번호로 오름정렬하여라. 
29. 이름과 급여의 데이터를 급여가 많은 순으로 정렬하라. 
30. 부서번호로 오름차순 정렬한후 급여가 많은 사람순으로 이름,부서번호,급여를 출력하라. 
31. 부서번호를 내림차순 정렬한후 ,직업순으로 오름정렬,급여순으로 내림정렬하여라. (전제 데이터출력)