[DB] join, ALTER

* INNER JOIN 이어서...

- join에 참여하는 모든 테이블에 데이터가 존재하는 경우에만 결과 값을 출력한다.

 

+ EQUI Join(등가 join)

- where절에 기술되는 join조건으로 양쪽 테이블에 같은 조건이 존재할 경우의 값만을 가져오는 join이다.

- equal연산지(=)를 사용해서 equi join이라고 한다.

ex) 학생테이블(student)과 학과테이블(department)테이블을 사용하여 학생이름, 1전공 학과번호(deptno1), 1전공 학과이름을 출력하라.

+oracle join 구문

select 테이블명. 컬럼명,,, ...

from 테이블명, 테이블명...

where 조인조건 and 조인 조건 ... ;

+ANSI join 구문

select 테이블명.컬럼명,,, ...

from 테이블명 alias

join 조인할 테이블명

on 조인 조건;

ex)

 

student		department
홍길동 김철수 김영희 이영수	1 2 3	1 2 3

(간략하게 나타내었다.)

 

- 이때 department의 값을 기준으로 equi join을 하면 이영수는 출력되지 않는다. equi join의 특성으로 양쪽 테이블에 모두 데이터가 존재해야 결과에 나온다. 

 

+ Non-Equi Join(비등가 Join)

- 같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 join을 수행할 때 사용한다.

 

* OUTER Join

- 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법이다.

- 이 join방식은 DB성능에 아주 나쁜 영향을 줄 수 있다는 것을 명심하고 주의해야 한다. why? A테이블과 B테이블을 outer join을 수행해서 A테이블에 있는 데이터를 다 검색하는 경우, 만약 A테이블에 인덱스가 있어도 인덱스를 쓰지 않고 Full Scan을 하기 때문이다.

- 데이터가 없는 테이블을 선정할 경우, inner join과 같은 결과가 나온다.

- 문법

+oracle join 구문

select 테이블명. 컬럼명,,, ...

from 테이블명 alias, 테이블명 alias...

where 조인조건 and 조인 조건(+) ... ;

- 데이터가 없는 조건 쪽에 (+)기호를 붙인다.

+ANSI join 구문

select 테이블명.컬럼명,,, ...

from 테이블명 (alias)

left outer join 조인할 테이블명

on (조인 조건);

- on조건절 중에 데이터가 있는 행을 출력하기 하기 위해 left outer join 구문을 사용한다. 반대는 right를 사용한다.

 

* self join

- 테이블 하나를 join하는 것

- 출력용으로 사용하는 테이블과 조건용으로 사용하는 테이블을 구분하여 사용한다.

- 문법(출력용, 검색용)

select 컬럼명,,,

from 테이블명 alias, 조인할 테이블명 alias,,,

where 조인조건 and 검색조건

ex)

select e1.ename, e1.empno, e1.sal

from emp e1, emp e2

where e1.sal > e2.sal;

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

-- self join

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

 

-- 문) 사원명이 scott인 사원보다 연봉을 많이 받는 사원의 사원명, 사원번호, 연봉을 조회하라

select e1.ename, e1.empno, e1.sal

from emp e1, emp e2

where e1.sal > e2.sal and e2.ename = 'SCOTT';

 

-- *잘못된 결과 출력

select e1.ename, e1.empno, e2.sal

from emp e1, emp e2

where e1.sal > e2.sal and e2.ename = 'SCOTT';

-- └e1이 가리키고 있는 KING의 연봉이 아니라 e2가 가리키고 있는 SCOTT의 연봉이 출력된다.

select e1.ename, e1.empno, e2.sal

from emp e1, emp e2

where e1.sal > e2.sal and e1.ename = 'SCOTT';

-- └조건절에 e2가 아닌 e1의 ename을 찾기 때문에 잘못된 결과가 출력된다.

 

-- *서브쿼리로 바꿔서 출력하라

select ename, empno, sal

from emp

where sal > (select sal from emp where ename = 'SCOTT');

 

 

* ALTER

- 계정의 변경, 테이블의 변경, 제약사항의 변경 등을 할 수 있다.

 

+ 계정의 변경

- 계정을 잠그거나 열때에 사용한다.

- 관리자 계정만 가능하다.(system, sys, sysdba)

- 문법

alter user 계정명 account lock|unlock;

 

+ 테이블 변경

- 컬럼 추가, 추가된 컬럼은 가장 뒤로 붙는다.

- 문법

컬럼 추가 : alter table 테이블명 add 추가할 컬럼명 데이터형(크기) 제약사항

컬럼 삭제 : alter table 테이블명 drop 컬럼

데이터형 변경

- 레코드가 존재하면 동일 데이터형에서 크기만 변경된다.

- 레코드가 존재하지 않으면 데이터형 자체가 변경된다.

alter table 테이블명 modify 변경할 컬럼명 데이터형(크기) 제약사항;

컬럼의 이름변경 : alter table 테이블명 rename column 컬럼명 to 변경할 컬럼명

 

+ 제약사항의 변경

제약사항의 활성화, 비활성화

- 비 활성화 된 상태에서 제약사항에 위배되는 데이터가 추가되면 다시 활성화 될 수 없다,

alter table 테이블명 enable|disable 제약사항명;

제약사항 삭제

alter table 테이블명 drop constraint 제약사항명;

제약사항 추가

alter table 테이블명 add constraint 제약사항명 제약사항종류(적용컬럼);

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

-- alter

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

 

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

-- 테이블 변경

 

create table test_alter(

name varchar2(15 byte),

aeg number(3),

addr number(22)

);

-- └일부러 틀리게 만든 테이블이다.

 

-- *위 테이블에 id컬럼 추가

alter table test_alter add id varchar2(12);

desc test_alter;

 

-- *위 테이블 컬럼의 컬럼명 변경

alter table test_alter rename column aeg to age;

desc test_alter;

 

-- *위 테이블의 컬럼 삭제

alter table test_alter drop column addr;

desc test_alter;

 

-- *위 테이블 컬럼 수정하기

-- 1. 데이터형 변경

alter table test_alter modify addr varchar2(100);

desc test_alter;

-- └성공

alter table test_alter modify id char(12);

-- └동일형으로 변경 성공

alter table test_alter modify id number(12);

-- └다른형으로도 변경 성공

alter table test_alter modify id char(6);

-- └데이터가 없는 경우에는 크기를 작게도 변경 가능

 

select * from test_alter;

insert into test_alter(name, age,id) values('이진기',26,'1214');

commit;

 

alter table test_alter modify id char(12);

-- └데이터가 존재할시 변경 불가능(error msg : 데이터 유형을 변경할 열은 비어 있어야 합니다)

alter table test_alter modify id number(14);

-- └그러나 데이터가 존재하더라도 크기는 변경할 수 있다

alter table test_alter modify id number(8);

-- └하지만 원래 크기보다 작게는 변경할 수 없다.

 

 

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

-- 제약사항

 

-- *제약사항 추가

alter table test_alter add constraint pk_test_alter primary key(id);

select * from user_constraints;

 

insert into test_alter(name, age, id) values('김종현',25,'0408');

insert into test_alter(name, age, id) values('김종현',25,'0408');

-- └error msg : 무결성 제약 조건(SCOTT.PK_TEST_ALTER)에 위배됩니다

 

-- *제약사항의 비활성화

select * from user_constraints;

alter table test_alter disable constraint pk_test_alter;

select * from user_constraints;

-- └user_constraints테이블의 status컬럼의 값으로 확인할 수 있다.

 

-- 비활성화 후 무결설 제약 조건에 위배되게 중복값을 넣어도 아무런 문제가 없다.

insert into test_alter(name, age, id) values('김종현',25,'0408');

insert into test_alter(name, age, id) values('김종현',25,'0408');

select * from test_alter;

commit;

 

-- *제약사항의 활성화

-- 1. 위배되는 데이터가 있으면 활성화되지 않는다.(위에 insert문을 통해 중복값을 넣었으므로 기본키 조건에 부합하지 않는다.)

alter table test_alter enable constraint pk_test_alter;

-- └error msg : (SCOTT.PK_TEST_ALTER)을 검증할 수 없습니다 - 잘못된 기본 키입니다

 

delete from test_alter;

commit;

-- 2. 위배되는 데티어가 없으면 활성화된다.

alter table test_alter enable constraint pk_test_alter;

select * from user_constraints;

 

-- 제약사항 삭제

alter table test_alter drop constraint pk_test_alter;

select * from user_constraints;