DBMS-서브쿼리, join, 제약조건, 뷰

직접 내용 정리하고 만든 예이니 퍼가실 때는 출처를 남겨주세요 :)

※ 들어가기 전 공지사항

아래 세 개의 이전 발행글을 미리 보고 오시면 이번 포스팅을 보다 수월하게 이해할 수 있습니다.

1. DBMS-Table&레코드와 시퀀스 

DBMS-Table&레코드와 시퀀스

2. DBMS- 오라클 함수 1편

DBMS- 오라클 함수 1편

3. DBMS-데이터를 검색,수정,삭제(feat.오라클 함수 2편)

DBMS-데이터를 검색,수정,삭제(feat.오라클 함수 2편)

---

HR 계정

1. HR 계정을 사용하기 위해서 Run SQL Command Line에 아래와 같이 입력해보겠습니다.

conn/as sysdba;
alter user hr account unlock;
conn/as sysdba;
alter user hr identified by oracle;
conn hr/oracle;

2. SQL Developer 를 설치 하겠습니다.

3. 아래와 같은 창이 뜨면

Name에는 HR을 입력하고

사용자 이름에는 hr을 입력하고

비밀번호에는 oracle을 입력해준 뒤 접속해주세요.

 

▷HR 계정을 생성했다면, 실습하기 전 알아야 할 개념을 정리하겠습니다.

1. 키워드

(1) group by절

○ 데이터들을 원하는 그룹으로 나눌 수 있는 키워드로, where절과 order by 절 사이에 적으면 됩니다.

○ select 그룹을 지정할 컬럼, *수치함수(집계할 값) from 테이블명 group by 그룹을 지정할 컬럼; 을 이용합니다.

*참고로, 수치함수를 DBMS-데이터를 검색,수정,삭제(feat.오라클 함수 2편) 글을 통해 보셨을 꺼라 생각하고 넘어가겠습니다.

(2) having절

○ group by 절에 대한 조건식을 적는 키워드로, group by 절 바로 뒤에 적으면 됩니다.

○ select 그룹을 지정할 컬럼, 수치함수(집계할 값) from 테이블명 group by 그룹을 지정할 컬럼 having 조건식; 을 이용합니다.

2. subQuery

○ 정의

select 문안에 select가 포함된 SQL문입니다.

○ 사용 가능한 곳

select 절, from 절, where절, having 절, order by절,,... 등이 있습니다.

○ 종류

(1) 단일행 서브쿼리

-> 결과가 하나인 서브쿼리를 말하며, 이것은 비교연산자와 대입연산자를 이용합니다.

(2) 다중행 서브쿼리

-> 결과가 여러 개인 서브쿼리로, 이것은 where과 in,all,any,some 연산자를 이용합니다. 

(3) 다중열 서브쿼리

-> 여러 개의 컬럼을 검색하는 서브 쿼리로, 결과값이 하나가 나오더라도 컬럼이 두개입니다.

3. 제약조건

○ 의미

내가 원하는 데이터를 받아들이기(=무결성=불필요한 데이터를 방지하기) 위한 조건

○ 여러 개를 쓸 수 있습니다. 

○ 제약 조건들이 저장되는 테이블

select * from user_constraints;

○ 제약 조건을 비활성화

-> alter table 테이블명 disable constraint 제약조건명

-> primary key를 비활성화 할 때에는 alter table 테이블명 disable primary key;나 alter table 테이블명 disable constraint 제약조건명; 이용합니다.

-> unique, check 비활성화 할 때는 alter table 테이블명 unique(필드명) cascade 이용합니다.

○ 제약 조건을 활성화

alter table 테이블명 enable constraint 제약조건명; 입니다.

○ 제약조건 적용방법

constraint 제약조건이름 제약조건이라 쓰고 각 제약조건이름과 사용법은 아래와 같습니다. 

(1) not null

-> NULL 값을 허용하지 않는 조건으로, not null 조건은 테이블에 적용시 check 조건으로 변환된 뒤 check 컬럼명 != null로 씁니다.

(2) unique

-> 중복된 값을 허용하지 않는 조건입니다.

(3) primary key(기본키) 

-> not null + unique 조건으로, 테이블을 대표할 수 있는 데이터가 있는 컬럼일 때 사용하며, 테이블당 하나만 설정가능합니다.

-> 추가할 때는 alter table 테이블 add constraint 제약조건 제약조건(필드명);으로 씁니다.

-> 제약조건이 설정되어있으면 삭제 후 설정해야 하는 데 그때는 alter table 테이블명 drop 제약조건(필드명); 으로 씁니다.

-> 설정되어 있을 경우 자동으로 고유 인덱스(unique index) 가 생성됩니다.

(4) check

-> 조건에 맞는 값만 허용하는 조건입니다.

(5) default

-> 데이터를 넣지 않으면 자동으로 설정하는 조건입니다.

(6) foreigen key(외래키=참조키)

-> 값을 참조하고 있는 테이블의 primary key 내에 데이터가 존재해야 하는 조건으로, 자식테이블에서 foreign key를 설정합니다.

-> 참조되고 있는 테이블은 부모테이블로, 참조하고 있는 테이블은 자식테이블이 부릅니다.

이 때, 부모테이블은 미리 만들어져 있어야 하며, 부모테이블의 참조되는 컬럼에 존재하는 값만 테이블에 입력이 가능합니다.

-> 형식은 constraint 이름 제약조건(컬럼명) references 참조테이블명(컬럼명); 을 씁니다.

-> foreigen key를 설정하면 부모테이블은 기본적으로 삭제가 되지 않습니다.

-> 하지만, on DELETE CASCADE 참조되는 부모테이블의 행에 대한 delete를 허용합니다. 이때 부모테이블 행과 자식테이블 행이 같이 지워집니다. 

-> 또한, on delete set null 참조되는 부모테이블 행에 대한 delete를 허용합니다. 이 때 부모테이블의 행이 지워지면 자식테이블은 null 값으로 설정됩니다.

○ 제약조건 삭제

alter table 테이블명 drop constaraint 제약조건명;

○ 제약 조건 추가

alter table 테이블명 add (constaraint 제약조건이름 제약조건(필드명),...);

○ 예외발생

(1) 예외 발생시 적용시키기 위한 sql

alter table 테이블명 enable 제약조건 exceptions into exceptions;

(2) 예외 발생 레코드 찾기

select * from 테이블명 where rowid in (select row_id from exceptions);

참고로, rowid란.. 오라클에서 자동으로 만들어주는 컬럼을 말합니다.

4. join

○ 하나(또는 둘) 이상의 테이블에 있는 데이터를 조회하기 위해 사용하기 위한 SQL문으로, join 조건은 where절에 씁니다.

○ 만약 똑같은 열의 이름의 하나이상의 테이블이 존재하면 어떤 테이블을 쓰는지 모르는 것을 없애기 위해 컬럼앞에 테이블명을 붙인 후 별칭을 사용하면 편리합니다. 굳이 중복되지 않는 컬럼이더라도 테이블명 또는 별명을 붙여주는 것이 좋습니다. 이것은 데이터의 위치를 정확히 알려주므로 성능 향상에 도움이 됩니다.

○ 종류

(1) outer 조인

-> join 조건을 만족하지 않는 행들도 보기 위해 ouuter join을 사용합니다. 

-> 사용할 때는 정보가 부족한 쪽에 (+) 기호를 붙여 표현합니다.

(2) self 조인

->자기 조인을 조인하는 것입니다.

-> 조인 조건이 생략되거나 잘못된 경우 cartesian product가 발생할 수 있습니다.

-> 조인 조건을 잘못 적으면 잘못된 결과를 얻을 수 있기 때문에 where 절에 정확한 join 조건을 써야 합니다.

5. index(인덱스)

○ 인덱스란, 데이터의 위치를 참조하고 있는 번호입니다.

○ 사용하는 이유는 조회속도를 향상시키기 위한 데이터베이스 검색 기술입니다.

참고로, 이를 많이 사용하지 않아야 합니다. 또한 수정하지 않고 검색에 자주 이용되는 컬럼을 인덱스로 지정합니다. 왜냐하면 수정(갱신,삭제,삽입) 시 속도가 저하되기 때문입니다.

○ 인덱스는 물리적 공간에 저장되니다. 

참고로, 들어간 것을 검색하면서 참조하고 테이블안에 데이터가 보이는 것은 index이지만, 안 보이는 것은 시퀀스입니다.

○  unique와 primary key(기본키) 는 데이터가 있을 경우 unique index가 생성됩니다.

○ 생성할 때

-> null 값일 경우에는 error 가 납니다.

-> 단일 인덱스를 지정하는 방법은 create index 인덱스명 on 테이블명(컬럼명); 입니다.

-> 다중 인덱스 지정(복합인덱스)의 방법은 create index 인덱스명 on 테이블명(컬럼명, 컬럼명,...); 입니다.

○ 수정할 때

alter index 인덱스명 storage (pctincrease 0); 을 씁니다.

참고로, 재 생성할 때는 alter index 인덱스명 rebuild; 를 씁니다.

○ 삭제할 때

drop index 인덱스명; 을 씁니다.

참고로, unique와 primary key(기본키) 는 데이터가 있을 경우 unique index가 생성되므로 제약조건을 반드시 삭제해야 합니다.

6. View

○ 뷰란, 필요한 내용만 뽑아서 쉽게 볼 수 있도록 만든 테이블입니다.

○ 종류

-> 단순뷰 : 테이블 하나가지고 하는 것

-> 복합뷰 : 테이블 하나 이상 들어가는 것

-> 인라인뷰 : 조인문, 서브쿼리 테이블 적는 부분 from 절에 sql 문 하나 더 들어가는 것

○ 생성

create [or replace][fore|noforce] view 뷰이름 [(뷰를 이룰 컬럼 이름)]  as SQL문 [with read only] [with check option[constraint 제약조건]]

 

참고로, 대괄호([])의 부분은 필요없다면 생략해도 됩니다.

-> on replace : 인덱스에서 재생성이라고 생각하면 됩니다. 즉, 수정한다는 말입니다.

-> force|no force : 테이블이 존재하지 않아도 뷰를 만드는 옵션으로 비워두면 no force로 들어갑니다.

-> with read only는 읽기 전용뷰로 만든 옵션입니다. 즉, select 만 가능한다는 말입니다.

-> [with check option[constraint 제약조건]] : 제약 조건에 맞는 것만 수정할 수 있도록 하는 옵션입니다.

○ 뷰 권한 주기

권한이 없으면 뷰 생성이 불가하므로 아래와 같이 권한을 주어야 합니다.

grant select on 뷰 이름 to 사용자명

 

○  뷰를 삭제하기

drop view 뷰 이름

728x90

▷ 이제 실습으로 들어가보겠습시다.

실습 1

null 값이 있다면 제거한 상태로 데이터를 보여주세요.

1. employees 테이블에서 최소 급여를 받는 직원의 이름과 급여를 출력하세요.

2. GROUP BY절에 기술된 department_id 별로 반올림한 평균 급여를 구하고 department_id별로 오름차순하세요.

3. GROUP BY절에 기술된 department_id 별로 반올림한 평균 급여를 구한 뒤, 평균 급여가 5000을 초과하고 department_id별로 오름차순 한 것을 보여주세요.

4. jobs 테이블과 job_history 테이블의 외부 조인 조건을 job_id로 하고 jobs 테이블의 job id와 job title과 job_history 테이블의 job_id를 보여주세요. 

 

5. employees 테이블에서 first_name,last_name,email,phone_number을 뽑아 employees_info라는 뷰를 만들어주세요.

6. employees_info 뷰에 salary를 추가해주세요.

7. employees_info 뷰를 삭제해주세요.

/*1번문제*/
select first_name,last_name,salary 
from employees 
where salary 
= (select 
min(salary) 
from employees);
/*2번문제*/
select department_id,round(avg(salary))
from employees 
where department_id is not null 
group by department_id 
order by department_id asc ;
/*3번문제*/
select department_id,round(avg(salary))
from employees 
where department_id is not null 
group by department_id 
having round(avg(salary))>5000
order by department_id asc;
/*4번문제*/
select j.job_id,jh.job_id,j.job_title
from jobs j, job_history jh
where j.job_id(+)=jh.job_id;
/*5번문제*/
create view employees_info as
select first_name,last_name,email,phone_number 
from employees;
/*6번문제*/
create or replace view employees_info as
select first_name,last_name,email,phone_number,salary
from employees;
/*7번문제*/
drop view employees_info;

실습 2

○ 실습 2를 들어가기 전, DBMS-데이터를 검색,수정,삭제(feat.오라클 함수 2편) 편 내용 중 Ctrl+F를 눌러 '실습하기 전, 아래에 있는 코드를 이용해 테이블 하나를 만들고 시퀀스를 추출해봅시다.'를 입력 후 코드를 확인한 후 그 테이블를 만들고 시퀀스를 추출합시다. 

○ 이제 본 실습 2로 들어가겠습니다.

1. da_index라 불리는 member 테이블에 da 인덱스를 만들어보세요.

2. da_index를 삭제해주세요.

3. member 테이블을 삭제해주세요.

4. member_seq를 삭제해주세요.

답은 순서대로 입니다.

create index da_index on member(da);
drop index da_index;
drop table member;
drop sequence member_seq;

실습 3

1. NULL 값을 허용하지 않는 조건의 제약조건을 이용하여, 다음 조건을 만족하는 테이블을 만드세요.

테이블 이름 : test

no 숫자형태

id 가변 길이 데이터 타입

2. 1, '둘리'라는 값을 테이블에 넣으세요.

3. 중복된 값을 허용하지 않는 조건의 제약조건을 test에 추가하세요.

4. 아래의 결과가 나오게 하세요.

5. NULL 값을 허용하지 않는 조건의 제약조건을 비활성화 하세요.

6. null,'고길동'값을 테이블에 넣으세요.

7. 중복된 값을 허용하지 않는 조건의 제약조건을 비활성화 하세요.

8. 중복된 값을 허용하지 않는 조건의 제약조건을 활성화 하세요.

9. NULL 값을 허용하지 않는 조건의 제약조건을 삭제하세요.

10. test 테이블을 삭제하세요.

/*1번 문제*/
create table test(
no number constraint test_nn not null,
id varchar(200));
/*2번 문제*/
insert into test values(1,'둘리');
/*3번 문제*/
alter table test add constraint test_u unique(id);
/*4번 문제*/
insert into test values(1,'둘리');
/*5번 문제*/
alter table test disable constraint test_nn;
/*6번 문제*/
insert into test values(null,'고길동');
/*7번 문제*/
alter table test disable constraint test_u;
/*8번 문제*/
alter table test disable constraint test_u;
/*9번 문제*/
alter table test drop constraint test_nn;
/*10번 문제*/
drop table test;