: 객체를 조회할 때마다 일일이 객체의 소유자를 지정하는 것이 번거로울 경우 동의어를 정의하면 긴 이름대신 간단한 이름으로 접근할 수 있도록 하는 명령어 입니다.
1) 동의어 생성
1) 권한을 부여한다.
grant create synonym to scott; -- 동의어를 만들 수 있도록 허용
2) 동의어 생성
create synonym systab for system.table_systbl;
-- system.table_systbl 이 스키마를 동의어로 systab 만듭니다.
( 간단히 접근하기 위함, 이름이 길때 등...) 사용합니다.
2) 비공개 동의어 생성
: 객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어로 해당 사용자만 사용할 수 있습니다.
create role test_role; -- 롤을 하나 생성을 했다.
grant connect, resource, create synonym to test_role;
-- connect + resource+ create synonym 권한을 test_role 에 줍니다.
grant select on scott.dept to test_role;
-- 위에 생성한 권한 + scott.dept 에대한 select 권한까지 받았다.
* 사용자에게 롤 부여
grant test_role to tester10; -- tester10, tester11 에게 권한을 부여 하였다.
grant test_role to tester11;
* 부여받은 권한 실행
select * from scott.dept; -- 스키마와 함께 써야한다.
3) 공개 동의어 생성
: 권한을 주는 사용자가 정의한 동의어로 누구나 사용할 수 있습니다. 또한 SYNONYM 앞에 PUBLIC를 붙여서 정의합니다.
create public synonym PubDept for scott.dept; -- 공개 동의어 생성
* 사용자에게 롤 부여
grant test_role to tester12;
select * from PubDept; -- 공개 동의어
4) 비공개 동의어/ 공개 동의어 제거하기
* 비공개 동의어 삭제 -- 비공개 동의어인 dept는 동의어를 소유한 사용자로 접속한 후 제거해야 합니다.
drop synonym dept;
* 공개 동의어 삭제
drop public synonym PubDept;
데이터 제어어(DCL : Data Control Language)란? = 사용자(User) 권한(Role) =
데이터 베이스에 접속하기 위해서는 계정이 필요합니다. 계정의 권한을 줄 수 있는 계정은 마스터 아이디만 가능합니다. 사용자 계정은 어떻게 하는지, 계정에 접속해서 데이터를 저장할 수 있게 권한을 줄 수 있을지 이러한 것을 가능하게 하는 명령어가 DCL이라고 합니다.
1) 계정 생성
형태 : create user 유저명 identified by 비밀번호 ;
create user thomas identified by tiger; -- thomas 유저가 생성 되었습니다.
- 접속 ERROR 가 나오고 있습니다. 접속하려면 접속의 권한도 부여되어야 합니다.
2) 데이터 베이스 접속 권한 부여 ( 권한의 키워드 : grant )
형태 : grant creat session to 유저명;
grant create session to thomas;
3) 테이블 생성 권한
- 테이블 생성 권한을 받지 않으면 위의 사진 처럼 ERROR 가 나옵니다.
grant create table to thomas;
- 유저에 얼마나 저장공간을 부여해야 합니다.
4) 테이블 스페이스 확인 (할당 키워드 : quota)
: 테이블 스페이스(table space)는 디스크 공간을 소비하는 테이블과 뷰 그리고 그 밖의 다른 데이터베이스 객체들이 저장되는 장소 입니다. -- 오라클 xe 버전의 경우 메모리 영역은 system으로 할당 -- 오라클 full 버전의 경우 메모리 영역은 users으로 할당
alter user thomas quota 2m on system ; -- xe 버전
alter user thomas quota 2m on users; -- full 버전
5) with admin option : system 의 속성을 부여받습니다.
: 사용자에게 시스템 권한을 with admin option 과 함께 부여하면 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고 부여 받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받게 됩니다.
* 계정 생성 및 권한 부여
create user tester3 identified by tiger; -- 유저 생성
grant create session to tester3 with admin option; -- 접속의 권한 부여
grant create table to tester3; -- 테이블 생성 권한 부여
alter user tester3 quota 2m on system; -- 저장 공간 확보
-- 다른 계정에게 create session 을 할 수 있는 권한이 생겼다.
6) 테이블 객체에 대한 select 권한 부여 ( scott/emp -> thomas )
grant select on emp to thomas;
select * from emp; --error
- SCOTT에 EMP 필드를 찾아볼 수 있게 권한을 부여했는데 왜? 오류일까요?
7) 스키마(SCHEMA) : 객체를 소유한 사용자명을 의미 합니다.
select * from scott . emp; -- 사용자 계정 이름 . 컬럼이름
select * from emp;
-- 대상을 명확하게 밝히지 않으면 어느것을 출력해야 하는지 알지 못하기 때문에 오류거 납니다.
8) 사용자에게 부여된 권한 조회
: 어떤 권한을 가지고 있는지 정확하게 알고 싶을 때 사용합니다.
-- user_tab_privs_made : 현재 시용자가 다른 사용자에게 부여한 권한 정보를 알려줍니다.
-- user_tab_privs_recd : 자신에게 부여된 사용자 권한을 알고 싶을 떄 사용합니다.
9) 비밀번호 변경시 ( 계정을 생성하는 것과 동일합니다.)
alter user thomas identified by thomas; (pw: tiger -> thomas)
10) 객체 권한 제거
revoke select on emp from thomas;
11) with grant option : 사용자 계정가지고 있는 권한을 받는 것입니다.
grant select on emp to tester1 with grant option;
-- tester1 에게 권한 scott 의 emp 를 select 할 수 있는 권한을 받았습니다.
-- 그리고 자기가 만든 테이블은 아니지만 다른 계정에게 권한을 부여할 수 있는 권한을 받았습니다.
12) 사용자 계정 제거
drop user tester3;
13) 권한(ROLE) 여러 작업들이 권한 부여로 실행되어져야 합니다.
create user tester3 identified by tiger;
grant connect, resource to tester3; -- 테이블을 만들고 뷰를 만드는 것이 가능해졌다.
connect Role :사용자가 데이터베이스에 접속 가능하도록 하기 위해서 다음과 같이 가장 기본적인 시스템 권한 8가지 묶어 놓은 권한을 말합니다.
resource Role :사용자가 객체(테이블, 시퀀스, 뷰)를 생성할 수 있도록 시스템 권한을 묶어 놓은 권한을 말합니다.
DBA Role : 사용자들이 소유한 데이터베이스 객체를 관리하고 사용자들이 작성하고 변경하고 제거할 수 있도록 하는 모든 권한을 말합니다.
인덱스 = sql 명령문의 처리 속도를 향상시키기 위해서 컬럼에 생성하는 오라클 객체로서 빠른 검색을 위한 용도로 사용합니다.
인덱스 장점 vs 단점
● 장점
• 검색 속도를 빨라지게 합니다.
• 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킵니다.
•아무리 많은 데이터가 되어 있더라도 데이터베이스가 빠르게 진행시킨다.
● 단점
• 인덱스를 위한 추가적인 공간이 필요합니다.
• 인덱스를 생성하는데 시간이 걸립니다.
• 데이터의 변경 작업(insert/update/delete)이 자주 일어날 경우에는 오히려 성능이 떨어집니다.
- 결과적으로 성능을 위해서는 필수적인 기능이라고 할 수 있습니다. 데이터의 온전성을 유지하기 위해서 단점이 있음에도 장점이 더 크기 때문에 사용되고 있다고 생각하시면 됩니다.
1) 인덱스 정보 조회하기
select index_name, table_name, column_name
from user_ind_columns
where table_name in('EMP', 'DEPT');
2) 조회 속도 비교하기
select distinct empno, ename
from emp01
where ename = 'ANGEL'; -- 1.324초(INDEX 연결x)
select distinct empno, ename
from emp01
where ename = 'ANGEL'; -- index 생성 이후 1.324초 에서 0.213초 로 줄어들었다.
3) 인덱스 생성
기본키나 유일키가 아닌 컬럼에 대해서 인덱스를 지정하려면 create index 명령어를 사용.
create index idx_emp01_ename
on emp01(ename);
-- index 를 만들어서 emp01에 연결 시키는 것입니다. / index 생성 시간은 오래 걸립니다. 46.762초
: 순서를 뜻하는 것으로, 몇 가지 작동을 어떤 기준에 따라 공간적 또는 시간적으로 순서를 정해 놓는 것을 말합니다.
즉, 오라클에서 행을 구분하기 위해서 기본 키를 두는데 그 기본 키는 중복 된 값을 가질 수 있으므로 항상 유일한 값을 가져야 합니다. 그런 기본 키가 유일한 값을 갖도록 사용자가 직접 생성해내는 것이 아니라 숫자를 자동으로 생성해 주는 명령어를 의미합니다.
1) 테이블의 시퀀스 생성
형태 : create squence 테이블 이름 start with increment by N;
create sequence memos_seq start with 1 increment by 1;
-- 시작의 값을 1로하고 증가하는 값 1로 하여 계속 증가하게 합니다.
2) 데이터 입력: 일련번호 포함
형태 : insert into 테이블(컬럼1, 컬럼2...) values(시퀀스.nextval,'내용2'...);
num 에 memos_seq.nextVal 넣어서 자동으로 증가하도록 합니다.
insert into memos(num, name) values(memos_seq.nextVal,'홍길동');
insert into memos(num, name) values(memos_seq.nextVal,'이순신');
insert into memos(num, name) values(memos_seq.nextVal,'강감찬');
-- memos_seq.nextVal : 1씩 증가 하면서 하나의 값을 반환해 줍니다.
-- 데이터 베이스가 자동으로 숫자를 관리해 줍니다.
3) 현재 시퀀스가 어디까지 증가되어져 있는지 확인
형태 : selet 시퀀스 이름. currval from dual;
select memos_seq.currVal from dual;
-- CURRVAL : 현재 값을 반환해줍니다.
4) 시퀀스 수정
형태 : alter sequence 시퀀스 이름 maxvalue N;
alter sequence memos_seq maxvalue 4; -- 최대 증가값을 4까지로 제한.
insert into memos(num, name) values(memos_seq.nextVal, '안중근'); -- 삽입 완료.
insert into memos(num, name) values(memos_seq.nextVal, '안창호'); -- error 최대값 초과
5) 시퀀스 삭제
형태: drop sequence 시퀀스 명;
drop sequence memos_seq;
6) 시퀀스 없는 상태에서 자동 증가값 구현
insert into memos(num,name)
values((select max(num)+1 from memos), '세종대왕');
-- 최대값에 +1을 해서 최종의 결과 값을 num의 값에 들어가게하는 것이다.
-- select max(num) from memos; 언제든 많이 사용되는 형태입니다. 기억해두세요.
TIP)
시퀀스는 컴퓨터를 부팅 또는 다른 사람의 파일을 가지고 와서 사용할 때, 파일의 수정사항에 대해서 내부에 가지고 있는 시퀀스가 다름으로 시퀀스의 번호가 random 으로 나누어질 수 있습니다. 그렇기 때문에 새롭게 모든 시퀀스를 삭제하고 순차적으로 진행해야 합니다. 이러한 이유로 시퀀스 없는 상태에서 자동 증가값 구현 하는 사람들이 많으며 시퀀스 번호로 일련번호를 넣는것을 선호하지 않습니다. 그래서 그룹 함수의 max 함수를 사용하여 번호를 삽이하는 형태로 구현을 선호 합니다.
: 실직적으로 데이터가 존재하지는 않는 테이블입니다. 이 테이블을 가상 테이블이라고도 부릅니다. 실제 정보를 가지고 있는 테이블을 물질 테이블이라고 하고 그 테이블에서 필요한 정보만 가상의 테이블을 만들어서 읽어 오는 것입니다.
● 뷰의 특징
- 테이블이라고 이름을 붙이는 이유는 기능 자체가 테이블과 동일하기 때문입니다.
- 실제 테이블의 정보를 뷰를 통해서 볼 수 있고, 제한적으로 필요한 내용만 사용이 가능하게 해줍니다.
- 실질적적인 물리적인 테이블이 존재하고 있어야지만 뷰를 생성할 수 있습니다.
- as 다음으로 마치 서브쿼리문과 유사합니다.
- 결과를 출력하기 위해서 맨번 select 문을 입력하기란 여간 번거로운 일이 아닐 수 없습니다. 뷰는 이 같이 번거로운
select 문을 매번 입력하는 대신 보다 쉽게 원하는 결과를 얻고자 하는 바람에서 출발했다고 할 수 있습니다.
● 뷰의 사용 이유
- 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화 시킬 수 있습니다.
- 보안에 유리합니다.
ex) 인사과 뷰 -- 필요한 정보들만 보여주고, 개인의 월급 같은 경우는 숨길 수 있습니다.
● 뷰의 동작 원리
1. 사용자가 뷰에 대해서 질의를 하면 user_views에서 뷰에 대한 정의를 조회합니다. 2. 기본 테이블에 대한 뷰의 접근 권한을 살핍니다. 3. 뷰에 대한 질의를 기본테이블에 대한 질의로 변환합니다. 4. 기본 테이블에 대한 질의를 통해 데이터를 검색합니다. 5. 검색된 결과를 출력합니다.
이제부터 자세히 예시를 통해서 설명하겠습니다.
1) 뷰 생성하기
create view emp_view30 --테이블 생성 하듯이 명령을 내릴 수 있습니다.
as
select select empno, ename, deptno
from emp_copy
where deptno = 30;
-- (오류 발생) 오류 보고 -ORA-00936: missing expression : 권한이 부족합니다.
※ view를 만들기 위해서는 마스터 아이디에게 권한을 부여 받아야지 처리할 수 있습니다.
2) 권한 부여 하기
- 명령 프롬프트로 마스터 아이드를 로그인하여 권한을 부여 합니다.
SQL> sqlplus system/admin1234 SQL> show user SQL> grant create view to scott;
권한을 준 이후에 뷰 생성 가능해집니다. 또한 view 권한을 부여하면 삭제가 되기 이전까지 그 권한을 가지고 있을 수 있습니다.
create view emp_view30
as
select empno, ename, deptno
from emp_copy
where deptno = 30;
emp_view30 뷰 생성 완료!
[예제] 기본 테이블은 emp_copy 로 합니다. 20번 부서에 소속된 사원들의 사번과 이름, 부서번호, 상관의 사번을 출력하기 위한 select 문을 emp_view20 이라는 이름의 부로 정의해 보세요.
create view emp_view20
as
select empno, ename, deptno, mgr
from emp_copy
where deptno = 20;
3) 뷰를 통해 데이터 저장하기
insert into emp_view30 values(8000, 'ANGEL',30);
- 기본 테이블에도 뷰에서 입력한 값이 삽입 됩니다. - 주의) 기본 테이블이 무결성 데이터 제약 조건에 맞지 않을 때는 삽입이 불가합니다. - 즉, 무결성 계약 조건이 걸려있지 않을 때 가능합니다.
-- update, delete도 사용 가능
4) 단순 뷰 사용 특징
● 단순 뷰에 대한 데이터 조작
insert into emp_view30
values(8010, 'CHELSOO', 30);
-- 삽입에 있어서 기본 데이터는 폴인키가 걸려 있지 않다면 DEPTNO의 값이 무엇이든 넣어도 상관 없습니다.
● 단순 뷰의 컬럼에 별칭 부여하기
create view emp_view_copy(사원번호 , 사원명, 급여, 부서번호)
as
select empno, ename , sal, deptno from emp_copy;
-- 뷰 이름 뒤에 가로를 넣어 그 안에 필드명 순서대로 이름을 넣습니다.
create view view_sal
as
select deptno, sum(sal) as "급여 합", avg(sal) as"급여 평균"
from emp_copy
group by deptno;
-- 그룹함수에 별칭을 입력해주면 값이 출력이 됩니다.
-- 없는 컬럼 항목의 이름을 부여해서 출력 되어지게끔 해줍니다.
select * from emp_view_copy where deptno =30;
-- 주의) 뷰에 별칭을 붙이고 필드명으로 출력을 하려고 하면 오류가 납니다(ERROR)
select * from emp_view_copy where 부서번호 =30;
-- 별칭으로 입력을 하면 값이 출력 됩니다.
5) 복합뷰 : : 2개 이상의 테이블 값을 한번에 가져와서 수행할 수 있습니다.
create view emp_view_dept
as
select empno, ename, sal, e. deptno, dname, loc
from emp e, dept d
where e.deptno = d. deptno
order by empno desc;
- 공통의 컬럼이 존재하면 어떤 테이블의 값인지 적어줘야 합니다.
6) 뷰 삭제
drop view emp_view_dept ;
7) 뷰의 다양한 옵션
● or replace
: 존재하지 않는 뷰이면 새로운 뷰를 생성하고, 기존에 존재하는 뷰이면 그 내용을 변경합니다.
- 동일 이름의 뷰를 만들면 변경합니다. (수정과 생성을 한번에!!)
create or replace view emp_view30
as
select empno, ename, comm, deptno
from emp_copy
where deptno = 30; -- 위의 테이블을 삭제하고 새로운 테이블을 덮어 씌운다.
● force / noforce
force : 기본테이블이 존재하지 않을 때도 뷰를 생성해야 하는 경우 사용하는 옵션입니다.
- 데이터에 대한 처리 보다 의미나 의지치만 확인 할 때 사용하는 개념으로 가지고 있으면 됩니다.(임시적 개념)
noforce : 기본테이블이 존재하는 경우만 뷰가 생성(default) 합니다.
- 아무것도 지정하지 않았을 때는 noforce로 처리됩니다.
<force>
create or replace force view employees_view
as
select empno, ename, deptno
from employees
where deptno = 30;
-- 경고: 컴파일 오류와 함께 뷰가 생성되었습니다. 경고는 발생하고 있지만 생성은 완료합니다.
-- 임시적으로 뷰 테이블을 생성하고 싶을 때 사용할 수 있습니다. 하지만 데이터 삽입은 불가합니다.
● with check option
: 뷰를 생성할 대 조건 제시에 사용된 컬럼 값을 변경 못하도록 하는 기능 입니다. - 뷰를 설정할 때 조건으로 설정한 칼럼 이외의 다른 커럼의 내용은 변경할 수 있습니다.
create or replace view view_chk30
as
select empno, ename, sal, comm, deptno
from emp_copy
where deptno = 30 with check option
update view_chk30
set deptno =20
where sal >= 900;
-- ERROR : 오류 보고 -ORA-01402: view WITH CHECK OPTION where-clause violation
-- with check option deptno의 값(deptno) 을 변경 불가하도록 만듭니다.
● with read only
: 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용 수정을 불가능하게 만드는 옵션입니다.
create or replace view view_read30
as
select empno, ename, sal, comm, deptno
from emp_copy03
where deptno = 30 with read only;
select * from view_read30;
update view_read30
set comm = 3000;
-- ERROR : SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view
-- with read only; 읽기 전용으로 명령을 수행할 수 없다.
with check option VS with read only (차이점)
with check option는 뷰를 설정할 때 조건으로 설정한 컬럼이 아닌 컬럼에 대해서는 변경이 가능하고,
① 서브 쿼리는 하나의 select 문장의 절 안에 포함된 또 하나의 select문 문장입니다. (메인 쿼리 / 서브 쿼리)
② Sub Query 부분은 WHERE 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 합니다.
③ 서브 쿼리는 메인 쿼리가 실행되기 이전에 한번만 실행이 됩니다.
④ 특별한 경우 (Top-n 분석 등)를 제외하고는 Sub Query 절에 Order by 절이 올 수 없습니다.
⑤ 단일 행 Sub Query 와 다중 행 Sub Query 에 따라 연산자를 잘 선택해야 합니다.
단일행 서브 쿼리와 다중행 서브 쿼리에 대해서 자세하게 예시를 통해서 설명하겠습니다.
1) 단일 행 서브쿼리 ( 결과 = 한개)
[예제1] scott와 동일한 직급(job)을 가진 사원을 출력하는 sql 문을 서브퀴리를 이용해서 작성해 보세요.
select ename, job from emp where job = (select job from emp where ename = 'SCOTT');
select ename, job from emp where job -- 메인쿼리
(select job from emp where ename = 'SCOTT') -- 서브쿼리
진행순서 서브쿼리 → 메인쿼리 → 출력
[예제2] scott의 급여와 동일하거나 더 많은 사원 이름과 급여를 출력해보세요.
select ename, sal from emp where sal > (select sal from emp where ename = 'SCOTT');
select ename, sal from emp where sal > -- 메인 쿼리
(select sal from emp where ename = 'SCOTT'); -- 서브 쿼리
[예제3] 전체 사원 평균 급여보다 더 많은 급여를 받는 사원을 출력하세요 [서브 쿼리 & 그룹 함수]
select ename, sal, job from emp where job > (select avg(sal) from emp);
select ename, sal, job from emp where job > -- 메인 쿼리
(select avg(sal) from emp); -- 서브 쿼리 / 그룹함수 avg를 활용하여 평균을 구합니다.
* 그룹함수와 컬럼을 다이렉드로 연결 할 수 없습니다.
2) 다중 행 서브쿼리 ( 결과 = 2개 이상)
[예제1] 급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 정보
select ename, sal, deptno from emp where dept = (select deptno from emp where sal >= 3000);--ERROR
A = B(10.20) - 2개의 값
(복수의 값의 수를 가지고서는 비교 연산자가 이루어지지 않기 때문에 오류가 일어납니다.)
다중 행 서브쿼리의 값을 출력하기 위해서는 추가적인 연산자가 필요합니다.
① in 연산자
: 메인 쿼리의 비교 조건 ('='연산자로 비교할 경우)이 서브쿼리의 결과 중에서 하나라도 일치(or)하면 참입니다.
in 연산자를 사용하여 위의 문제를 다시 풀어보겠습니다.
select ename, sal, deptno from emp where deptno in
(select deptno from emp where sal >= 3000);
비교 연산자를 대신하여 in 연산자를 사용하여 값을 출력하였습니다.
[예제2] in 연사자를 이용하여 부서별 가장 급여를 많이 받는 사원의 정보 (사원번호, 사원명,급여, 부서번호)를 출력하세요.
SELECT empno,ename,sal,deptno
from emp
where sal in
(select max(sal) from emp group by deptno);
그룹함수를 활용하여 각 부서의 최대월급을 출력합니다.
② all 연산자
: 메인 퀴리의 비교 조건이 서브퀴리의 검색 결과와 일치하면 참이다. 찾아진 값에 대해서 and 연산을 해서
모두 참이면 참이 되는 셈입니다. >all 은 모든 비교 값도다 크냐 고 묻는 것이므로 최대값보다 크면 참이 됩니다.
[예제1] 30번(부서번호) 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력하세요.
①
select enanme, sal from emp where sal >
(select max(sal) from emp where deptno = 30); -- 단일행 서브쿼리& 그룹함수
②
select ename, sal
from emp
where sal > all (select sal
from emp
where deptno =30);
-- 각각의 급여를 받는 사람이 있었는데 그중에 제일 많이 받는 사람이 2850 달라였는데
-- all 은 모든 값을 만족하는건 이기 때문에 최대값 보다 더 큰 결과를 도출 하고 싶을 때 사용합니다.
[예제2] 영업사원(salesman)들보다 급여를 많이 받는 사원들의 이름과 급여를 출력하되 영업사원은 출력하지 않게 명령문을 작성해 보세요.
select job,ename, sal from emp where sal >all
(select sal from emp where job = 'SALESMAN') -- 여기까기만 구해도 값은 같습니다.
and job <> 'SALESMAN'; -- 하지만 조건의 가독성을 높이기 위해서 and문까지 처리해주면 좋습니다.
③ any 연산자
: 메인 쿼리의 비교 조건이 서브쿼리의 검색 결과와 하나 이상만 일치하면 참입니다. > any는 찾아진 값에 대해서 하나라도 크면 참이 됩니다.
그러므로 찾아진 값에서 가장 작은 값, 즉 최소값 보다 크면 참이 됩니다.
[예제1] 부서번호가 30번인 사원들의 급여 중에서 가장 낮은 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력하는 명령문을 작성해 보세요.
단일행 서브쿼리 사용 (+ 그룹 함수)
select deptno,ename, sal from emp where sal >
(select min(sal) from emp where deptno = 30)
order by sal desc;
다중행 서브쿼리 사용
select deptno,ename, sal, job from emp where sal > any
(select sal from emp where deptno = 30);
JOIN? 두 개 이상의 테이블에 나뉘어져 있는 데이터를 한 번의 SQL 문으로 원하는 결과를 얻을 수 있는 기능입니다.
좀 더 효율적으로 빠르게 하기 위해서 JOIN 을 통해서 나뉘어진 테이블의 결과 중 원하는 것만 가져올 때 사용합니다.
종류
내용
cross join
2개 이상의 테이블이 조인될 때 where절에 의해 공통되는 컬럼에 의한 결합이 발생
하지 않는 경우를 의미합니다.
equi join
조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을
연결하여 결과를 생성하는 방법입니다.
non-equi join
동일 컬럼이 없이 다른 조건을 사용하여 join 조건에 특정 범위 내에 있는지를 조사
하기 위해서 조건절에 조인 조건을 = 연산자 이외의 비교 연산자를 이용합니다.
self join
하나의 테이블 내에서, 자기 자신과 조인을 통해 원하는 자료를 얻는 방법입니다.
outer join
조인 조건에 만족하지 못해서 해당 결과를 출력시에 누락이 되는 문제점이 발생된다.
해당 레코드(row)를 출력하고 싶을 때 사용하는 join 방법입니다.
지금부터 각 조건들의 특징과 사용방법을 예시들을 통해서 설명하겠습니다.
1) cross join : 2개 이상의 테이블이 조인될 때 where절에 의해 공통되는 칼럼에 의한 결합이 발생하지 않는 경우
-- 공통이 되어지는 컬럼의 이름 가지고 결함됩니다.
형태 : select * from 필드명1, 필드명2;
select * from emp, dept;
-- 하나가 아닌 2개의 테이블을 지정 (dept 4 + mep 14 = 총 56/ 단순결합)
직관적이지 않으므로 많이 사용하지 않습니다.
2) equi join: 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 방법
-- 동일 칼럼을 기준으로 조인합니다.
형태 : select * from 테이블1, 테이블2 where 테이블1. 필드명 = 테이블2. 필드명; (필드명은 같은 것을 사용)
select * from emp, dept where emp. deptno = dept.deptno;
-- 같은 데이터를 하나로 결합하여 출력.(dept 4 equi join mep 14 = 총 14)
-- 조건에 만족하는 내용을 선별하는 것이다.
※ 컬럼명 앞에다가 테이블명을 기술하여 컬럼 소속을 명확히 밝힐 수 있습니다.
select emp.ename, dept.dname, dept.loc, emp. deptno
from emp, dept
where emp.deptno = dept.deptno
and ename = 'SCOTT';
※ 테이블 옆에 별칭을 부여할 때는 별칭으로 사용할 수 있습니다. (테이블 이름이 길때 유용하게 많이 사용합니다.)
select e.ename, d.dname, d.loc, e. deptno
from emp e , dept d
where e.deptno = d.deptno
and e.ename = 'SCOTT;
-- 테이블 이름에 별칭을 붙일 때는 as 를 붙이지 않습니다.
-- 별칭을 붙였을 때는 별칭으로만 사용해야하며, 테이블명을 사용하면 안됩니다.
3) non-equi join: 동일 컬럼이 없이 다른 조건을 사용하여 join 조인 조건에 특정 범위 내에 있는지를 조사하기 위해서 조건절에 조인 조건을 = 연산자 이외의 비교 연산자를 이용/ 동일 칼럼이 없이 다른 조건을 사용하여 조인
1) 연산자 활용
select ename, sal, grade from emp, salgrade
where sal>= losal and sal <= hisal;
2) between 활용 (between A and B)
select ename, sal, grade from emp, salgrade
where sal between losal and hisal ;
위의 내용을 자세하게 설명하겠습니다.
select ename, sal, grade, dname -- 겹치는 것이 없기 때문에 따로 별칭을 붙일 필요가 없다.
from emp, dept, salgrade -- 테이블 이름 / 이대로 출력 하면 (14*5*4가 추력된다)
where emp. deptno = dept. deptno -- equi join / 이대로 출력하면 (14 항목)
and sal between losal and hisal ; -- 정보를 추가 / 14항목 출력!
4) self join: 하나의 테이블 내에서, 자기 자신과 조인을 통해 원하는 자료를 얻는 방법
-- 한 테이블 내에서 조인합니다.
(mgr : 사수번호/ empno : 사원번호 )/ 같은 테이블 안에서 데이터를 정보를 얻습니다.
select employee.ename, employee.mgr , manager.ename
from emp employee, emp manager -- 하나의 테이블을 두개의 테이블 인거 처럼 별칭을 이용하여 사용
where employee. mgr = manager. empno; -- 결과 13개 / 사장은 사수가 없기 때문에 나오지 않습니다.
(5)outer join: 조인 조건에 만족하지 못해서 해당 결과를 출력시에 누락이 되는 문제점이 발생된다. 해당 레코드(row)를 출럭하고 싶을 때 사용하는 join 방법
-- 조인 조건에 만족하지 않는 행도 나타낸다.
select employee. ename, employee. mgr, manager. ename
from emp employee, emp manager
where employee. mgr = manager. empno(+) ; -- = 을 기준으로 값을 출력하고 싶지 않은 쪽에 (+)을 붙입니다.
★ ANSI 조인 : 데이터베이스 회사들이 모두 이 기능을 구현해 놓았습니다. ( 표준 )
1) Ansi Cross join = oracle (cross join)
● Ansi Cross join
select * from emp cross join dept;
● oracle cross join
select * from emp, dept;
2) Ansi Inner join = oracle (Equi join)
①
● Ansi Inner join
select ename, dname, emp. deptno
from emp inner join dept
on emp. deptno = dept. deptno;
● Ansi Inner join
select ename, dname, deptno
from emp inner join dept
using (deptno);
● oracle Equi Join
select ename, dname , emp. deptno
from emp , dept
where emp. deptno = dept. deptno;
②
● Ansi Inner join
select ename, dname, emp. deptno
from emp inner join dept
on emp. deptno = dept. deptno
where ename = 'SCOTT';
● oracle Equi Join
select ename, dname , emp. deptno
from emp , dept
where emp. deptno = dept. deptno
and ename = 'SCOTT';
3) natural join
select ename, dname from emp natural join dept;
-- 두 테이블에서 공통이 되는 컬럼 항목을 찾아서 공통이 되어지는 결과를 출력해줍니다.
4) Ansi Outer join = oracle(outer join)
● Ansi Outer join
select * from dept10 left outer join dept11
on dept10.deptno = dept11.deptno; -- left는 왼쪽 값의 데이터 dept10 를 추가적으로 출력해준다.
select * from dept10 right outer join dept11
on dept10.deptno = dept11.deptno; -- right는 오른쪽 값의 데이터 dept11를 추가적으로 출력해준다.
select * from dept10 full outer join dept11
on dept10.deptno = dept11.deptno; -- 모든 데이터의 값을 출력해준다.
● oracle outer join
select * from dept10, dept11
where dept10.deptno = dept11. deptno(+);
-- 플러스는 dept11(30.영업부)의 내용이 나올주 알았는데, 결과값은 (20,연구부)가 출력
select * from dept10, dept11
where dept10.deptno(+) = dept11. deptno;
-- 플러스는 dept10(20, 연구부)의 내용이 나올주 알았는데, 결과값은 (30,영업부)가 출력
select * from dept10, dept11
where dept10.deptno(+) = dept11. deptno(+);
-- 양쪽의 더하기를 넣어서 모두 출력하고 싶지만 오라클은 지원해 주지 않습니다.(ERROR)
