코린이 잰이

JOIN

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는것

INNER JOIN

-join 중에서 가장 많이 사용한다.

-조건이 일치하는 값이 두 테이블에 모두 존재할 때 결과값이 나온다.

형식

SELECT 컬럼명 FROM 테이블명1 INNER JOIN 테이블명2 ON 조인조건 WHERE 검색조건

일단 테이블을 만들어 보겠습니다.

그럼 JOIN을 해보겠습니다.

두 테이블이 결합된 것을 볼 수 있습니다.

sum()외에  GROUP BY와 함께 사용되는 함수

한번빌릴때 평균적으로 책을 얼마나 빌리는지 알아보겠습니다.

쿼리문에 적은 as는 별칭은 정해준것입니다. id 의 별칭은 회원 평균을구한num의 별칭은 평균대출권수로 정해준 것입니다.

한번빌릴때 몇권씩 빌리는지 결과값이 나오는 것을 볼 수 있습니다.

여기서 aa의 결과값은 보기 힘듭니다. 저런 소수점을 조절하고 싶으면 CAST()함수를 사용하면 됩니다.

보이는 것과 같이 소수 3번째 자리까지 나타나는 것을 볼 수있다.

GROUP BY 

테이블 내의 데이터를 그룹별로 구분하여 통계적인 결과를 내기위해 사용합니다.

먼저 GROUP BY형식부터 알아보겠습니다.

SELECT 컬럼명1,SUM(컬럼명2) FROM 테이블명 GROUP BY 컬럼명1;

여기서 컬럼명1 에는 기준이되는 컬럼명을 넣어주셔야합니다. 그리고 컬럼명2에는 계산 가능한 값을 넣어주셔야 합니다.

도서관을 예를 들면 도서관 회원 번호가 있을것 입니다. 도서관 회원이 한번에 책을 2개 빌릴 수도 있고, 3개 빌릴수도 있고 1개 빌릴수도 있습니다.

그럴 때 회원 번호가 컬럼명1이고 빌린 갯수가 컬럼명 2라고 생각하시면됩니다. 

먼저 테이블을 만들고 데이터까지 넣어보겠습니다.

그리고 select를 해보시면 밑의 결과를 볼 수 있습니다.

보이시는 것과 같이 겹치는 것들이 여러개 있습니다.

aa가 빌린 책의 총합은 num인데 num을 다더하면 10권입니다. 마찬가지로 bb는 6권, cc는 12권, dd는 27권 입니다.

이런것을 좀 더 간편하게 보기 위해 GROUP BY를 사용합니다.

보이시는 것과 같이 num 안에 있는 데이터들이 ID를 기준으로 다 더해져서 결과 값이 나온 것을 볼 수 있습니다.

aa회원이 몇권을 빌렷는지 bb회원이 몇권을 빌렷는지 알기쉽게 정리해줍니다.

SELECT

테이블 내의 원하는 정보를 추출하는 명령어

형식

select 컬럼명 from 테이블 이름 where 조건

테이블 검색하기 

EMP라는 테이블의 데이터가 나오는 것을 볼 수있습니다. 

쿼리문에서 * 의 뜻은 모든 컬럼을 의미하는 것 입니다.

그러면 테이블 안에서 empno와 ename 의 데이터만 검색해보겠습니다.

empno와 ename의 내용만 나오는 것을 확인할 수 있습니다.

다음은  where 문까지 사용하여 empno가 7777인 행만 검색 해보겠습니다.

empno가 7777인 행만 나오는 것을 확인할 수 있습니다. 

만약 where 문을 두개 쓰고싶으면 관계연산자(and, or, not )를 쓰고 조건문을 또 써주시면 됩니다.!

BETWEEN

형식

SELECT * FROM 테이블명 WHERE 컬럼명BETWEEN 값 AND 값;

두개의 쿼리문을 다 사용해 봅시다. 결과가 같은것을 볼 수 있습니다.

보이는 것과 같이 BETWEEN 은 ~이상 ~이하의 값을 조회한다.

여기서 empno는 연속적인 값을 가지는 숫자이다. between은 연속적인 값 밖에 조회하지 못한다.

그럼 연속적인 값이 아닌 이산적인 값을 위해서는 무었을 써야할까?

IN

형식

SELECT * FROM 테이블명 WHERE 컬럼명 IN('데이터','데이터'....);

이 두개의 쿼리문 역시 사용하면 똑같이 나오는 것을 볼 수 있습니다.

숫자가 아닌 문자일 경우 연속된 값이 아니기 때문에 between 을 사용하지 못하지만

in 을 사용하게되면 좀더 간단하게 찾을 수 있다.

LIKE

형식

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '문자%';

이번에는 문자열 내용을 검색해 볼꺼다. 

위의 조건은 이름의 맨 앞 글자가 'A'이고, %는 뒤에는 어떤 문자가 있어도 상관이 없다는 말이다. 

ORDER BY

형식

SELECT * FROM 테이블 명 ORDER BY 컬럼명 ASCorDESC;

결과물에 영향을 미치지는 않지만 결과가 출력되는 순서를 조절하는 구문이다.

보이는 것과 같이 empno를 중심으로 오름차순으로 정렬된 것을 볼수있다.

order by에는  asc(오름차순), desc(내림차순) 가있는데 기본적으로 오름차순으로 정렬이된다.

DISTINCT

형식

SELECT DISTINCT 컬럼명 FROM 테이블 명 ;

중복된 것을 하나만 남기는 명령어입니다.

보이는 것 처럼 중복되는 직업을 제외하고 나타내었

데이터 사전이란?

-사용자와 데이터베이스의 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블의 집합

-사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신된다(사용자는 데이터 사전의 내용을 직접 수정하거나 삭제할 수 없음) 

-사용자가 이해할 수 있는 데이터를 산출해 줄 수 있도록 하기 위해서 읽기 전용 뷰 형태로 정보를 제공합니다.

데이터 사전은 크게 3가지로 나뉜다.

USER_데이터 사전

사용자와 가장 밀접하게 관련된 뷰로써 자신이 생성한 테이블, 인덱스, 뷰, 동의어 등의 객체나 해당 사용자에게 부여된 권한 정보를 제공한다.

사용자가 소유한 테이블에 대한 정보 조회

이 외에도

USER_SEQUENCES - 사용자가 소유한 시퀀스의 정보 조회

USER_INDEXES - 사용자가 소유한 인덱스의 정보 조회

USER_VIEWS - 사용자가 소유한 뷰의 정보 조회

USER_데이터 사전은 USER_뒤에 원하는 객체등을 기술해주면되는데 뒤에 기술되는 명칭은 S 가 붙은 

복수타입이다.

ALL_ 데이터 사전

-전체 사용자와 관련된 뷰로써 사용자가 접근할 수 있는 모든객체에 대한 정보를 조회할 수 있다.

-조회중인 객체가 누구의 소유인지를 확인하도록 하기위해 OWNER칼럼을 제공한다.

all_table로 자신이 소유한 혹은 권한을 부여받은 테이블에 대한 정보 조회

DBA_데이터 사전

DBA나 시스템 권한을 가진 사용자만 접근이 가능하다.

사용자가 다른경우 DBA로 시작하는 데이터 사전을 조회할 권한이 없기 때문에 SYSTEM계정으로 접속해야한다.

현재 제가 접속한 사용자는 hr인데 hr은 DBA로 시작하는 데이터 사전을 조회할 권한이 없기 때문에 

오류가 난다. 그러므로 권한이 있는 시스템 계정에서 조회 해보겠다.

ㅁㅁㅁ

시스템 계정 접속하기

dba_table로 테이블에 대한 정보 조회하기

너무많아서 캡쳐 불가능입니다.

2080개나되는 많은 자료들을 볼 수 있었다.

TRUNCATE TABLE - 기존 사용하던 테이블의 모든 로우를 제거

- 테이블의 구조는 그대로 유지

- 테이블의 데이터와 할당된 공간만 해지

- 테이블에 생성된 제약 조건과 연관된 인덱스, 뷰 , 동의어는 유지

형식

TRUNCATE TABLE 테이블명;

truncate table 을 사용하고나서 select는 안되지만 desc는 나타나는 것을 볼 수 있다.

DROP TABLE - 기존 테이블과 데이터 모두제거

삭제 불가능한 경우

- 삭제할 테이블의 기본 키나 고유 키를 다른 테이블에서 참조하고 있는 경우 

(참조하는 테이블(자식 테이블)을 먼저 제거후 제거해야한다.)

형식

DROP TABLE 테이블명;

DROP TABLE을 한 이후에는 테이블이 제거되었기 때문에 구조를 확인할 수 없다.

RENAME - 테이블을 포함한 객체의 이름을 변경하는 DDL명령문이다.

형식

RENAME 기존테이블명 TO 새테이블명

테이블명이  변경되었기 때문에 이전 테이블명으로는 테이블 구조를 확인할 수 없고,

새로운 테이블명으로 확인 가능하다.

ALTER TABLE - 기존 테이블의 구조 변경을 하는 DDL명령어 입니다.

테이블을 생성하면서 컬럼의 추가, 수정, 삭제를 할 때 사용합니다.

컬럼추가

ALTER TABLE 테이블명

ADD(컬럼명 DataType);

컬럼을 추가하는 형식입니다.

-추가된 컬럼의 마지막 부분에 생성이 됩니다.

-수정할 테이블에 기존의 추가한 데이터가 존재하면 컬럼 값은 NULL로 입력됩니다.

보이는 것과 같이 AGE 컬럼이 추가되었고, 데이터 값은 NULL로 입력됩니다.

컬럼변경

ALTER TABLE MODIFY 명령문을 이용하여 테이블에서의 컬럼의 타입, 크키, 기본 값 변경을 할 수 있다.

ALTER TABLE 테이블명

MODIFY(컬럼명 DataType);

처음 name의 크기변경을 할 때 5보다 큰 데이터 값이 있기 때문에 변경 불가

기존 데이터 타입보다 큰 30으로 변경 가능 

name 의 데이터값이 변경된것을 확인할 수 있습니다.

컬럼제거

- 2개 이상의 컬럼을 가진 기존테이블의 특정 컬럼과 컬럼의 데이터 삭제 가능

- 한번의 하나의 컬럼만 삭제가 가능

- 삭제된 컬럼은 복구 불가능

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

삭제된 것을 볼 수 있습니다.

SET UNUSED

시스템의 요구가 적을 때 컬럼을 제거할 수 있도록 하나 이상의 칼럼을 UNUSED로 표시한다.

실제로 테이블에서 제거되지 않는다. 

사용이유

테이블에 저장된 내용이 많을 경우 컬럼 삭제시 많은 시간이 소요된다. 그 시간중에 다른 사용자가 컬럼에 접근하면 , 테이블이 사용되고 있기에 다른 사용자는 해당 테이블을 이용할 수 없다. 이럴경우 rock이 발생하게된다. 

SET UNUSED옵션은 DROP명령 실행시 걸리는 시간보다 응답시간이 빠르고, UNUSED로 표시된 컬럼은 데이터가 존재해도 삭제된 것으로 처리되기 때문에 사용한다.

테이블에서 id제거하기

실제로 컬럼명이 나타나지 않는 것을 볼 수 있다. 

하지만 실제로 제거된 것이 아니다. 

테이블에서 현재 UNUSED로 표시된 모든 컬럼 제거하기

가상컬럼이란? 

oracle 11g부터 소개되었는데

- 하나의 테이블에 존재하는 다른 컬럼들을 이용하여 새로운 값을 만들어 내는 것을 말합니다.

만드는 방법은 

GENERATED ALWAYS AS(조건) VIRTUAL; - 테이터 컬럼명을 만든 곳 뒤에 붙인다.

걷보기엔 일반 테이블과 다를게 없습니다. 

그러면 데이터를 넣어보겟습니다.

젤 첫번째 오류는 가상컬럼의 제약조건에 위배되었는데 가상컬럼안에는 값을 직접 입력할 수 없습니다.

두번째 오류는 그래도 컬럼이라고 컬럼수가 부족하다는 것 입니다.

가상 컬럼의 조건으로 num1과 num2의 합을 주었기 때문에 4가 들어간 것을 볼 수있습니다.

또한 기존 값을 변경하면 가상컬럼의 값도 변경이 되는 것을 볼 수 있습니다.