티스토리 뷰
<JOIN>
- cross join
- equi join
- non equi join
- self join
- outer join
<ANSI JOIN> - 다른 DBMS와 호환되어지는 조인을 사용
- cross join
from emp, dept
->from emp cross join dept
- inner join
on emp.deptno =detp.deptno
using(deptno)
- natural join(on,using을 필요로 하지 않는다)
- [left|right|full] outer join
<서브쿼리>
select 컬럼명, 데이터
from 테이블, (서브쿼리)
where 컬럼명 연산자 비교데이터(서브쿼리)
<DDL> 데이터 정의어
- 객체에 대한 생성, 삭제, 변경, rename, truncate
- 자동적인 Commit이 일어남
create table 테이블명
(
컬럼명1 자료형,
컬럼명2 자료형,
컬럼명3 자료형
);
- 기존테이블에 구조(컬럼명O, 자료형O, 제약X)와 데이터를 복사
create table 테이블명
as
서브쿼리
select * from 테이블명 - 모든 구조와 데이터를 복사
select 컬럼명1,컬럼명2 from 테이블명 - 특정속성에 대한 구조와 데이터복사
select * from 테이블명 where 조건식 - 특정행에 대한 구조와 데이터복사
select * from 테이블명 where 거짓조건식 - 테이블 구조만 복사(데이터X)
<테이블 구조 변경> ALTER
1. 컬럼추가 (job)
ALTER TABLE 테이블명
ADD (컬럼명 자료형);
alter table emp7
add (job varchar(15));
2. 컬럼속성변경 (ename)
ALTER TABLE 테이블명
MODIFY (컬럼명 자료형)
alter table emp7
modify(ename varchar2(15));
3. 컬럼삭제하기(job)
ALTER TABLE 테이블명
DROP COLUMN 컬럼명;
alter table emp7
drop column job;
alter table emp7
set unused(sal);
alter table emp7
drop unused columns;
--->테이블이 변경되었습니다.
<객체삭제> drop
형식)
DROP TABLE 테이블명;
drop table emp7;
--->테이블이 삭제되었습니다.
drop table emp6;
==================================================
show recyclebin; (휴지통보기)
purge table em05; (휴지통에서 특정테이블 삭제)
purge recyclebin; (휴지통비우기)
flashback table em05 to before drop; (테이블 살리기)
==================================================
show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP6 BIN$gEz5HOxXSi6VfsP3HD71fw==$0 TABLE 2013-05-02:10:42:59
EMP7 BIN$2sFK9r0lRCuPtgzwPK7bNA==$0 TABLE 2013-05-02:10:41:46
flashback table emp6 to before drop;
--->플래시백이 완료되었습니다.
purge recyclebin
--->휴지통이 지워졌습니다.
<테이블내의 모든 행삭제> truncate --복원X
- 데이터 저장공간 삭제
형식)
TRUNCATE TABLE 테이블명;
※참고: 특정행을 삭제하고자 했을때 DML명령어 delete(복원O)사용
drop table emp7;
create table emp7
as
select empno,ename,sal
from emp;
truncate table emp7;
--->테이블이 잘렸습니다.
이름 널? 유형
----------------------------------------- -------- ------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
SQL> select * from emp7;
선택된 레코드가 없습니다.
create table emp8
as
select empno,ename,sal,deptno
from emp;
select *
from emp8
order by deptno;
EMPNO ENAME SAL DEPTNO
----- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
7566 JONES 2975 20
7902 FORD 3000 20
7876 ADAMS 1100 20
7369 SMITH 800 20
7788 SCOTT 3000 20
7521 WARD 1250 30
7844 TURNER 1500 30
7499 ALLEN 1600 30
EMPNO ENAME SAL DEPTNO
----- ---------- ---------- ----------
7900 JAMES 950 30
7698 BLAKE 2850 30
7654 MARTIN 1250 30
문제) emp8테이블에서 30번부서에 속한 사원 행을 삭제.
delete from emp8
where deptno=30;
EMPNO ENAME SAL DEPTNO
----- ---------- ---------- ----------
7934 MILLER 1300 10
7782 CLARK 2450 10
7839 KING 5000 10
7902 FORD 3000 20
7876 ADAMS 1100 20
7566 JONES 2975 20
7369 SMITH 800 20
7788 SCOTT 3000 20
truncate table emp8
where deptno=20;
--->ORA-03291: 부적절한 truncate 옵션 - storage 키워드의 누락
<객체이름변경> rename
형식)
RENAME oldName TO newName
rename emp8 to emp88;
--->테이블 이름이 변경되었습니다.
-------------------------------------------------------
<DML>
- 데이터 조작어
- Date Manipulation Langquage
- (insert, delete, update)
<INSERT문> 행 추가
형식)
INSERT INTO 테이블명
[(컬럼명1,컬럼명2,컬럼명3)]
VALUES (데이터,데이터,데이터);
--->[]: 만약 컬럼명을 생략한다면 테이블내의 모든 컬럼을 의미!
13,'홍길동','2013/05/01' --날짜에 ''안쓰면 나누기로 인식
'2013-05-01' -- 빼기로 인식
drop table dept01;
create table dept01
as
select * from dept
where 1=0;
SQL> desc dept01
이름 널? 유형
----------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
insert into dept01 (deptno,dname,loc)
values(10,'총무부','서울');
SQL> select * from dept01;
DEPTNO DNAME LOC
---------- -------------- ----
10 총무부 서울
insert into dept01 (dname,loc,deptno)
values(20,'개발부','수원');
--->ORA-01722: 수치가 부적합합니다
insert into dept01 (dname,loc,deptno)
values('개발부','수원',20);
SQL> select * from dept01;
DEPTNO DNAME LOC
---------- -------------- ----
10 총무부 서울
20 개발부 수원
insert into dept01
values('자재부','대전',30);
---> 컬럼명 생략하면 기본구조의 순서에 맞추어 전체 컬럼이 표시됨
여기서는(deptno,dname,loc)
insert into dept01
values(30,'자재부','대전',13);
--->ORA-00913: 값의 수가 너무 많습니다
insert into dept01
values(30,'자재부');
--->ORA-00947: 값의 수가 충분하지 않습니다
insert into dept01
values(30,'자재부','대전');
DEPTNO DNAME LOC
------ -------------- ---
10 총무부 서울
20 개발부 수원
30 자재부 대전
<NULL값 입력>
create table dept02
as
select * from dept
where 0=1;
1. 명시적 null입력
insert into dept02(deptno,dname,loc)
values(10, '총무부',null);
2. 컬럼명 생략
insert into dept02(deptno,dname)
values(20, '개발부');
3.''입력
insert into dept02(deptno,dname,loc)
values(30, '자재부','');
문제) 아직 부서위치가 확정되지 않은 부서의 수는?
select count(*)
from dept02
where loc is null;
COUNT(*)
--------
3
<서브쿼리를 통한 데이터입력>
형식)
insert into 테이블명
서브쿼리
create table dept03
as
select * from dept
where 1=0;
select * from dept03;
insert into dept03
select * from dept;
select * from dept03;
DEPTNO DNAME LOC
------ -------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
문제) dept테이블의 20,40부서를 복사하여 아래 dept04테이블에 데이터를 입력하시오.
create table dept04
(
deptno number(2),
dname varchar2(15),
loc varchar2(15)
);
insert into dept04
select *
from dept
where deptno=20 or deptno=40;
insert into dept04
select *
from dept
where deptno in (20,40);
insert into dept04
select *
from dept
where mod((deptno/10),2)=0;
insert into dept04
select *
from dept
where mod(deptno,20)=0;
<다중테이블에 다중행입력>
insert into 테이블명1, 테이블명2 --->X, 에러
1. INSERT ALL
INTO 테이블명1 VALUES(서브쿼리컬럼명)
INTO 테이블명2 VALUES(서브쿼리컬럼명)
서브쿼리;
예)
into dept03 values (deptno,dname,loc)
select deptno, dname, loc
from dept;
create table emp_hir
as
select empno,ename,hiredate
from emp
where 1=0;
create table emp_mgr
as
select empno,ename,mgr
from emp
where 0=1;
insert all
into emp_hir values(empno,ename,hiredate)
into emp_mgr values(empno,ename,mgr)
select empno,ename,hiredate,mgr
from emp
where deptno=30;
--->12 개의 행이 만들어졌습니다. (hir: 6개, mgr: 6개)
SQL> select * from emp_hir;
EMPNO ENAME HIREDATE
---------- ---------- --------
7499 ALLEN 81/02/20
7521 WARD 81/02/22
7654 MARTIN 81/09/28
7698 BLAKE 81/05/01
7844 TURNER 81/09/08
7900 JAMES 81/12/03
SQL> select * from emp_mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7698 BLAKE 7839
7844 TURNER 7698
7900 JAMES 7698
2. INSERT ALL
WHEN 조건식 THEN INTO 테이블명 VALUES (서브쿼리컬럼명)
서브쿼리;
create table emp_hir
as
select empno,ename,hiredate
from emp
where 1=0;
create table emp_sal
as
select empno,ename,sal
from emp
where 0=1;
insert all
when hiredate >= '1981/10/01'
then into emp_hir values(empno,ename,hiredate)
when sal > 1400
then into emp_sal values(empno,ename,sal)
select empno,ename,hiredate,sal
from emp;
--->14 개의 행이 만들어졌습니다. (hir:6, sal:8)
SQL> select * from emp_hir;
EMPNO ENAME HIREDATE
---------- ---------- --------
7788 SCOTT 87/04/19
7839 KING 81/11/17
7876 ADAMS 87/05/23
7900 JAMES 81/12/03
7902 FORD 81/12/03
7934 MILLER 82/01/23
6 개의 행이 선택되었습니다.
SQL> select * from emp_sal;
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7902 FORD 3000
8 개의 행이 선택되었습니다.
<Update문> - 행데이터 수정, 갱신
형식)
UPDATE 테이블명
SET (수정하고자하는)컬럼명=(변경할)데이터, 컬럼명2=데이터
[WHERE 조건식];
※ where절을 생략하면 모든행 수정, where절을 명시하면 특정행 수정을 의미.
문제) 전체 사원의 급여를 10%인상하시오.
drop table emp02;
create table emp02
as
select empno,ename,sal
from emp;
EMPNO ENAME SAL
----- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
update emp02
set sal=sal*1.1;
EMPNO ENAME SAL
----- ---------- ----------
7369 SMITH 880
7499 ALLEN 1760
7521 WARD 1375
7566 JONES 3272.5
7654 MARTIN 1375
7698 BLAKE 3135
7782 CLARK 2695
7788 SCOTT 3300
7839 KING 5500
7844 TURNER 1650
7876 ADAMS 1210
EMPNO ENAME SAL
----- ---------- ----------
7900 JAMES 1045
7902 FORD 3300
7934 MILLER 1430
문제) 30번 부서에 속한 사원의 급여를 10%인상하시오.
drop table emp02;
create table emp02
as
select empno,ename,sal,deptno
from emp;
SQL> select * from emp02 order by deptno;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
7566 JONES 2975 20
7902 FORD 3000 20
7876 ADAMS 1100 20
7369 SMITH 800 20
7788 SCOTT 3000 20
7521 WARD 1250 30
7844 TURNER 1500 30
7499 ALLEN 1600 30
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7900 JAMES 950 30
7698 BLAKE 2850 30
7654 MARTIN 1250 30
update emp02
set sal = sal*1.1
where deptno=30;
--->6 행이 갱신되었습니다.
SQL> select * from emp02 order by deptno;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
7566 JONES 2975 20
7902 FORD 3000 20
7876 ADAMS 1100 20
7369 SMITH 800 20
7788 SCOTT 3000 20
7521 WARD 1375 30
7844 TURNER 1650 30
7499 ALLEN 1760 30
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7900 JAMES 1045 30
7698 BLAKE 3135 30
7654 MARTIN 1375 30
UPDATE 테이블명
SET 컬럼명=서브쿼리
[WHERE 조건식];
drop table dept02;
create table dept02
as
select * from dept;
select * from dept02;
문제)
20번부서의 위치를 10번부서의 위치와 일치시키시오.
update dept02
set loc=(select loc
from dept02
where deptno=10)
where deptno=20;
select * from dept02;
SQL> select * from dept02;
DEPTNO DNAME LOC
---------- -------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH NEW YORK
30 SALES CHICAGO
40 OPERATIONS BOSTON
문제)
40번부서가 10번부서로 통합되었습니다.
40번부서의 부서명과 부서위치를 10번부서와 같게 변경하시오.
update dept02
set (dname,loc)=(select dname,loc
from dept02
where deptno=10)
where deptno=40;
SQL> select * from dept02;
DEPTNO DNAME LOC
---------- -------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH NEW YORK
30 SALES CHICAGO
40 ACCOUNTING NEW YORK
<테이블 행삭제> delete
형식)
DELETE FROM 테이블명--오라클은 from 안써도 되지만 꼭 서야하는 dbms도 있다
[WHERE conditions];
※where절을 생략하면 전체행(모든데이터) 삭제,
where절 명시하면 특정행 삭제를 의미.
문제) 부서테이블의 모든 행을 삭제
drop table dept02;
create table dept02
as
select * from dept;
SQL> select * from dept02;
DEPTNO DNAME LOC
---------- -------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
delete from dept02;
--->선택된 레코드가 없습니다.
문제) 부서테이블에서 40번부서만 삭제
insert into dept02
select * from dept;
delete from dept02
where deptno=40;
--->1 행이 삭제되었습니다.
SQL> select * from dept02;
DEPTNO DNAME LOC
---------- -------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
문제) 사원테이블에서 SALES부서에 근무하는 사원행을 삭제
drop table emp02;
create table emp02
as
select empno,ename,deptno
from emp;
delete from emp02
where deptno=(select deptno
from dept
where dname='SALES');
--->6 행이 삭제되었습니다.
SQL> select * from emp02;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7566 JONES 20
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7876 ADAMS 20
7902 FORD 20
7934 MILLER 10
<테이블 합병> MERGE
- 테이블구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능.
- 기준테이블을 변경(참조하는 테이블을 사용)
형식)
MERGE INTO 기준테이블명
USING 테이블명
ON (합병에 필요한 공통컬럼)
WHEN MATCHED
THEN update문
WHEN NOT MATCHED
THEN insert문
준비)
drop table emp01;
--기준테이블
create table emp01
as
select * from emp;
drop table emp02;
--참조테이블
create table emp02
as
select * from emp
where job='MANAGER';
SQL> select empno,ename,sal,job from emp02;
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7566 JONES 2975 MANAGER
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
update emp02
set job='학생';
SQL> select empno,ename,sal,job from emp02;
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7566 JONES 2975 학생
7698 BLAKE 2850 학생
7782 CLARK 2450 학생
--EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
insert into emp02 values (8000,'홍길동','사원',7788,sysdate,4000,null,30);
SQL> select empno,ename,sal,job from emp02;
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7566 JONES 2975 학생
7698 BLAKE 2850 학생
7782 CLARK 2450 학생
8000 홍길동 4000 사원
MERGE INTO emp01 --insert, update할 테이블명
USING emp02
ON (emp01.empno=emp02.empno)
WHEN MATCHED
THEN
update --merge into에 이미 명시되어있기 때문에 emp01을 뺀다
set emp01.job=emp02.job,
emp01.mgr=emp02.mgr,
emp01.hiredate=emp02.hiredate,
emp01.sal=emp02.sal,
emp01.comm=emp02.comm,
emp01.deptno=emp02.deptno
WHEN NOT MATCHED
THEN
insert --into 테이블명 -> 이미 명시되어있다
values(emp02.EMPNO,emp02.ENAME,emp02.JOB,emp02.MGR,emp02.HIREDATE,emp02.SAL,emp02.COMM,emp02.DEPTNO);
Merge전)
SQL> select empno,ename,sal,job from emp01;
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
Merge후)
--->4 행이 병합되었습니다.
SQL> select empno,ename,sal,job from emp01;
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 학생
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 학생
7782 CLARK 2450 학생
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
8000 홍길동 4000 사원