190529_DAY35, <복습>, <DDL>, <DML>

용진용진 2019. 5. 29. 21:32

2019. 5. 29. 21:32

190529_DAY35, <복습>, ,

<조인>

두 개 이상의 테이블을 참조해서 데이터를 검색
from 뒤에 두개이상의 테이블 정의

조인의종류)

cross join, equi join, non-equi join, self join, outer join

cross join
- Where절 없이 사용하는 조인
- 각 테이블이 갖는 행의 곱만큼 결과행을 생성
```
FROM emp, dept
```
equi join
- 테이블 간의 공통된(연관된) 칼럼을 '='을 통해 비교
```
FROM emp, dept
WHERE emp.deptno = dept.deptno
```
non-equi join
- 대소비교, 범위를 표현하는 연산자를 사용할 때
```
FROM emp, salgrade
WHERE emp.sal between salgrade.losal AND salgrade.hisal
```
self join
- 같은 테이블을 두 번 이상 참조 조회.
- 마치 테이블이 두개 이상 존재하는 것처럼 사용
- 조회하고자 하는 데이터가 같은 행에 없을 때 사용! (SMITH의 MGR 7902(FORD)로 FORD의 정보 조회할 때)
```
SELECT e1.ename, e2.ename
FROM emp e1, emp e2
WHERE e1.ename = 'SMITH'
    AND
    e1.mgr = e2.empno;
```

outer join

조회 결과행에 배제된 (빠진) 행을 출력하고 싶을 때 사용

SELECT e1.ename || 
'사원의 상사는'||
e2.ename||
'입니다.'
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno;
-- 기대 : 14명 사원 각각의 직속 상사명 출력
-- 결과 : 13row... 뭔가 빠졌네
-- 원인 : 매핑되지 않는 행이 발생
    -- 예
    WHERE e1.mgr = e2.empno;
    -- KING 의 mgr은 NULL , NULL = 데이터 => 결과도 NULL, 검색행에 누락됨.
    -- ※ NULL은 비교, 연산, 할당 불가!

-- 해결 : where e1.mgr = e2.empno(+);

SELECT e1.ename || 
'사원의 상사는'||
NVL(e2.ename, '[미정]')||
'입니다.' "상사는 누구?"
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno(+);

표준조인, DBMS종류와 상관없이 실행.
FROM 뒤에 ',' 콤마를 사용하지 않는다.
cross join, inner join, natural join, outer join

cross join
```
FROM emp CROSS JOIN dept
```

inner join

FROM emp INNER JOIN dept
-- ON 공통칼럼 비교 -- 반드시 ON 정의, but USING 절로 대체가 가능
ON emp.deptno = dept.deptno

USING (deptno)

WHERE 추가 기능 -- 추가적인 조건 검색

natural join
- 두 테이블을 비교해서 동일한 이름의 컬럼을 자동 using 해준다
- 단, 단점으로는 동일한 이름으로 의도치 않은 컬럼도 묶어버림.
```
FROM emp NATURAL JOIN dept
```

outer join

FROM emp (LEFT 혹은 RIGHT 혹은 FULL) [OUTER] JOIN dept

-- 오라클 문법에서는 모자란쪽(부족한쪽) 에 (+)를 붙이지만
-- 
SELECT e1.ename || 
'사원의 상사는'||
NVL(e2.ename, '[미정]')||
'입니다.' "상사는 누구?"
FROM emp e1 LEFT OUTER JOIN emp e2
ON e1.mgr = e2.empno;

<서브쿼리>

특정 테이블에서 검색된 결과를 다른 테이블에 전달하여 새로운 결과를 검색 할 때 사용

메인 쿼리 이외의 추가되는 모든 SELECT문

SELECT 컬럼명 -- 메인쿼리
        (SELECT ~ FROM ~) -- 서브쿼리

FROM 테이블명
        (SELECT ~ FROM ~) -- 서브쿼리, 인라인 뷰
                        -- 서브쿼리의 결과가 하나의 테이블에 대한 뷰처럼 사용되기에 이렇게 부르는 것이다. 

출처: https://narwhals.tistory.com/9 [IT 공부 블로그]

WHERE 컬럼명 연산자 데이터 
        논리연산자( OR | AND )
        컬럼명 연산자 ( SELECT ~ FROM ~ ) -- 서브쿼리

-- 단 서브쿼리에서 지켜주어야 하는 것!
-- SELECT 서브쿼리는 1행 1열
-- FROM 서브쿼리는 1행 1열도 되고, 다수행 다수열도 가능
-- WHERE 서브쿼리는 결과행 1개 : 연산자 =, > , <
--                        2개이상 : 연산자에 등호, 부등호를 사용 불가!! , 에러발생, 이를 IN, ANY, SOME, ALL, EXISTST 등(Multi Row Operator, 다중 행 연산자)를 통해 해결!
-- 다중 행 연산자 사용
WHERE deptno IN (
                   SELECT deptno
                    FROM dept
                    WHERE deptno < 30
               )
WHERE sal > ANY (
                   SELECT sal
                   FROM emp
                   WHERE deptno = 20
             )

WHERE sal > ALL (
                   SELECT sal
                   FROM emp
                   WHERE deptno = 20
             )    

WHERE exists (서브쿼리) -- TRUE FALSE로 반환, 결과행 0개 : FALSE, 결과행 1개이상 : TRUE

<DDL.>

CREATE, DROP, ALTER, RENAME, TRUNCATE

<테이블생성>

CREATE TABLE 테이블명
(
    컬럼명1 자료형, 
    컬럼명2 자료형
);
--테이블 객체생성! 구조만생성, 데이터는 없음 => 앞으로 INSERT명령문 통해 행단위의 입력!

<테이블생성2 - 서브쿼리를 이용한 테이블 생성>

기존 테이블의 구조(컬럼, 자료형) 와 데이터를 복사
제약(CONSTRAINT)은 복사에서 제외!
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

형식)

-- 예전에 했던 복사 테이블 생성 방식

SELECT tname, tabtype 
FROM tab
WHERE tname LIKE '%E%';

SELECT tname, tabtype
FROM tab
WHERE tname LIKE 'EM%';

DROP TABLE emp2;

CREATE TABLE emp2
AS SELECT * FROM emp;

<테이블 생성3 >

서브쿼리를 이용한 테이블 생성 , 원하는 컬럼과 행만 복사

CREATE TABLE 테이블명
AS 서브쿼리;

SELECT tname, tabtype from tab; -- 조회

FROM emp
WHERE deptno = 30;

-- 생성
CREATE TABLE emp3
AS SELECT empno, ename, job -- 선택컬럼, 모든컬럼은 * 로
FROM emp -- 원본테이블
WHERE deptno = 30; -- 행제어 WHERE절 생략시 모든 행

--오라클 조회어
DESC emp3;

<데이블 생성4>

서브쿼리를 이용한 테이블 생성 : 구조(컬럼, 자료형)만 복사

CREATE TABLE emp4
AS SELECT * FROM emp
WHERE '길동' = '라임';
-- 1 = 0,  1>2

select * from emp4;
-- no rows selected

<테이블의 구조 변경하기> ALTER

컬럼추가, 컬럼삭제, 컬럼의 자료형, 바이트 수를 변경

ALTER TABLE emp4
ADD  (email  varchar2(20));
--==> Table altered.

desc emp4;

ALTER TABLE emp4
DROP COLUMN email;
--==> 테이블 변경

※ALTER 정리
ALTER TABLE 테이블명
- 컬럼 추가 : ADD ( 추가 컬럼명 자료형 )
- 컬럼 수정 : MODIFY ( 기존 컬럼명 자료형 )
- 컬럼 삭제 : DROP COLUMN 삭제할 컬럼명
- 컬럼이름변경 : RENAME COLUMN 기존컬럼명 TO 새컬럼명
- 컬럼사용안함 : SET UNUSED (컬럼명)
- UNUSED 설정 된 컬럼들 삭제 : DROP UNUSED COLUMN

<테이블 객체 삭제>

형식)

DROP TABLE 테이블명;

SELECT tname, tabtype from tab;
- 시스템테이블
- 연결계정이 갖는 테이블명과 테이블 타입을 출력

DROP TABLE emp2;

<테이블 객체 이름 변경>

형식)

RENAME old_객체명  TO  new_객체명;

CREATE TABLE emp2
AS SELECT * FROM emp;

SELECT tname, tabtype from tab;

RENAME emp2 TO 사원테이블;

select tname, tabtype from tab;


<테이블의 전체 데이터 지우기>

- 형식)

  - TRUNCATE TABLE 테이블명;

  ```SQL
  SELECT COUNT (*) FROM 사원테이블;

  TRUNCATE table 사원테이블;  

  ---> Table truncated. (테이블이 잘렸습니다!! : 테이블내의 모든 데이터가 삭제 되었음)
  ---> ※주의: truncate명령어를 통해 삭제된 데이터는 복구가 불가능!!

<DML.> Data Manipulation Language : 데이터 조작어

INSERT(입력), DELETE(삭제), UPDATE(수정, 재입력) --행단위 실행

<INSERT.> : 테이블에 (행) 데이터 추가 (최초입력)!

형식

INSERT INTO 테이블명 [( 컬럼명1, 컬럼명2, 컬럼명3)] -- 생략가능
            VALUES (데이터1, 데이터2, 데이터3);
            --만약, 테이블명 뒤에 컬럼명을 생략하는 경우
            -- 테이블 구조에 있는 순서에 따라 전체 컬럼명이 명시된 것으로 간주!!
            ---> 명시된 컬럼의 갯수와 데이터 갯수는 반드시 일치.
              ---> 컬럼의 순서에 맞는 자료형 데이터가 와야함.

INSERT INTO dept
VALUES (50, '대전', '자재부');

create table dept2
as select * from dept;

select * from dept2;

insert into dept2 (deptno, dname, loc)
values ( 50, '기획부', '남터');

select * from dept2;


<데이터 입력시 에러 예>

```sql
insert into dept2 (deptno, dname)  values (60,'자재부','대전');
==> ORA-00913: too many values  

insert into dept2 (deptno, dname)  values (60);
==> ORA-00947: not enough values

insert into dept2 (deptno, dname, loc)  values ('자재부','대전',60);
또는
insert into dept2     values ('자재부','대전',60);
                               *
==> ORA-01722: invalid number

<NULL값 입력방법>

컬럼명을 명시하지 않는다.

   create table dept3
   as select * from dept;

   insert into dept3  (deptno, dname)
               values (50    , '개발부');

select * from dept3;

문제) 아직 부서의 위치가 정해지지 않은 부서는 어디?
     select dname
     from   dept3
     where  loc  is null;   
     ==> 개발부

문제) 데이터가 들어가는 자리에 직접 null을 명시.
   insert into dept3  (deptno, dname, loc)  values (60,'기획부', null);
   insert into dept3  values (60,'기획부', null); -- 이렇게 줄일 수 있다.

 select * from dept3;

 문제) 데이터가 들어가는 자리에 ''를 입력
insert into dept3 (deptno, dname, loc)
 values (70,    '총무부', '');

 문제) 아직 부서의 위치가 정해지지 않은 부서는 어디?
    select dname
    from dept3
    where loc is null;

<서브쿼리로 행 추가>

형식

create table dept4
as select * from dept
   where 1=0;        -- dept테이블의 구조(컬럼)만 복사, 데이터 복사X

select * from dept4;
---> no rows selected       

insert into dept4
select * from dept; 

문제) dept5테이블을 생성하고(dept테이블의 구조만 복사, 데이터복사X)
     dept테이블의 20,40번 부서의 정보(부서번호, 부서이름)를 서브쿼리를 통해 입력하시오.
drop table dept5;

     create table dept5
     as select * from dept
        where 1=0;    

     insert into dept5           ---> 컬럼명 생략은 전체 컬럼 즉, (deptno, dname, loc) 생략 간주
     select deptno, dname       -- 2열
     from   dept   -- 4행
     where  deptno  IN (20,40);  -- 2행

---> ORA-00947: not enough values  : 데이터가 충분하지 않음!!

     insert into dept5 (deptno, dname)        
     select deptno, dname       -- 2열
     from   dept   -- 4행
     where  deptno  IN (20,40); 
---> 2 rows created.

문제) 만약 dept테이블의 부서번호가 10단위로 구성되어 있다고 가정했을때
     10,30,50,70,....번의 부서의 정보를 dept5테이블에 옮기시오.(서브쿼리를 통한 행추가!!)
     ----------------
     십의 자리수가 홀수인 부서!!

     insert into dept5 -- (deptno, dname, loc)
     select deptno, dname, loc
     from   dept
     where  mod( deptno/10,  2)  = 1;
---> 2 rows created.     

SQL> select * from dept5;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        20 RESEARCH
        40 OPERATIONS
        10 ACCOUNTING                   NEW YORK
        30 SALES                        CHICAGO

<다중테이블에 다중 로우(행) 입력>

형식

INSERT ALL
    INTO 테이블명1 VALUES (COL, COL, COL)
    INTO 테이블명2 VALUES (COL, COL, COL)
    INTO 테이블명3 VALUES (COL, COL, COL)
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9
FROM 테이블명;

문제) emp테이블에서 30번 부서 사원들의 정보를 얻어서(조회해서)
emp_hir(구조만 : empno, enmae, hiredate)테이블과
emp_mgr(구조만 : empno, ename, mgr )테이블에 데이터를 입력하시오.

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 1 = 0;

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;

<조건 (WHEN)에 의해 다중테이블에 다중 로우 입력하기>

형식

WHEN 조건식 THEN 실행할 문장

테스트테이블 생성)
CREATE TABLE emp_hir2
AS SELECT empno, ename, hiredate
FROM emp
WHERE 1 = 0;

CREATE TABLE emp_sal
AS SELECT empno, ename, sal
FROM emp
WHERE 1 = 0;

문제) 1982년 이후에 입사한 사원의 정보를 emp_hir2테이블에 입력하고
급여가 2000이상을 받는 사원의 정보를 emp_sql테이블에 입력하시오.
(insert all사용, 데이터정보는 emp테이블에서 조회)

INSERT all
    WHEN hiredate >= to_date('1982/01/01')
    THEN INTO emp_hir2 VALUES (empno, ename, hiredate)
    WHEN sal >= 2000
    THEN INTO emp_sal VALUES (empno, ename, sal)
SELECT empno, ename, hiredate, sal
FROM emp;

<UPDATE.> : 기존 데이터에 대한 수정, 갱신, 재입력

형식

UPDATE 테이블명
SEL 컬럼명1 = (변경할) 데이터, 컬럼명2 = (변경할) 데이터
[WHERE 조건식];

-- 주의 : UPDATE와 DELETE문을 실행하기 전 반드시 WHERE절 사용을 고려하자
-- 왜? 만약 WHERE절 사용하지 않는다면 전체행에 대한 수정 또는 삭제가 실행되기 때문

DROP TABLE dept3;

CREATE TABLE dept3
AS SELECT * FROM dept3;

SELECT * FROM dept3;

문제) 30번 부서의 급혀를 10% 인상하라
DROP TABLE emp3;

CREATE TABLE emp3
AS SELECT empno, ename, sal, deptno
FROM emp;

UPDATE emp3
SET sal = sal*1.1
WHERE deptno = 30;

SELECT * FROM emp3;

<서브쿼리를 이용한 데이터 수정>

DROP TABLE dept4;

CREATE TABLE dept4
AS SELECT * FROM dept;

문제) 부서번호 20번의 부서명과 지역을 40번 부서와 동일하게 변경하시오!

UPDATE dept4
SET dname = (
            SELECT dname
            FROM dept4
            WHERE deptno = 40
            ),
     loc = (
             SELECT loc
             FROM dept4
             WHERE deptno = 40
             )
WHERE deptno =20;

UPDATE dept4
SET ( dname, loc ) = (
             SELECT dname, loc
             FROM dept4
             WHERE deptno = 40
             ) --from where이 동일함으로 이렇게 묶어 줄 수 있다!
WHERE deptno =20;

<DELETE.> : ( 행 단위 )데이터 삭제명령어

형식

DELETE FROM 테이블명 --전체 행 삭제(모든데이터), 보통 drop해서 드문 일
[WHERE 조건식]; --특정 행 삭제

문제) 부서테이블(DEPT4)내의 모든 부서를 삭제하시오.
DELETE FROM dept4;

문제) DEPT4테이블을 유지한 상태(rollback하지 말고)에서     
     DEPT테이블의 데이터들을 복사(입력)하시오.
UPDATE dept4
VALUES (데이터);

INSERT INTO dept4
서브쿼리;

INSERT INTO dept4
SELECT deptno, dname, loc
FROM dept;

SELECT * FROM dept4;
문제) 20번 부서를 삭제하시오.

DELETE FROM dept4
WHERE deptno = 20;

SELECT * FROM dept4;

문제) SALES부서에 속한 사원들을 사원테이블(EMP3)에서 삭제하시오.
DROP TABLE emp3;

CREATE TABLE emp3
AS SELECT * FROM emp;

SELECT * FROM emp3;
--------------------------------
delete from emp3
where  deptno = 30;

delete from emp3
 where  deptno = (select deptno
                      from  dept
                      where dname='SALES');    
----> 6 rows deleted.

select empno, ename, deptno, dname
from       emp3   inner join dept
using   (deptno);

<MERGE.> 합병(병합)

구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능.
기존에 존재하는 행이 있다면 새로운 값으로 갱신(update)되고
존재하지 않는 행이 있다면 새로운 행으로 추가(insert)해라!!

형식

   MERGE INTO 기준테이블명   별명
       USING  참조테이블명   별명
       ON (매칭 조건식)
   WHEN MATCHED      -- 매칭되는 행이 있다면
      THEN 업데이트문
   WHEN NOT MATCHED  -- 매칭되는 행이 없다면 
      THEN 추가문;

CREATE TABLE emp11 -- 기분테이블
AS SELECT * FROM emp;

CREATE TABLE emp12 -- 참조테이블
AS SELECT * FROM emp
WHERE job = 'MANAGER';

UPDATE emp12 SET job = '사원';
INSERT INTO emp12 (empno, ename, job,   mgr, hiredate, sal, comm, deptno)
           VALUES (9000, '나길동','사원', 7788, sysdate, 2000, null, 30); -- emp11에는 없는 사원

SELECT empno, ename, job FROM emp12;

MERGE INTO emp11 e1
    USING emp12 e2
    ON (e1.empno = e2.empno)
WHEN MATCHED -- 매칭되는 행이 있다면
    THEN -- 업데이트(변경)문
        UPDATE SET --SET앞에 생략
            e1.job = e2.job,
            e1.mgr = e2.mgr,
            e1.sal = e2.sal,
            e1.comm = e2.comm,
            e1.deptno = e2.deptno
WHEN NOT MATCHED -- 매칭되는 행이 없다면
    THEN -- 추가문
        INSERT VALUES (e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno);

궁금상자
- 테이블 조회하기
    SELECT * FROM all_all_tables;
- 테이블 목록 조회(접속한 계정)
    SELECT * FROM tabs; -- 그런데 이렇게 하면 어마어마하게 많이 나온다.
    SELECT tname, tabtype FROM tab; -- 이정도로만 조회하도록 하자!
- 테이블을 지우고 싶은데 일일이 지우기에는 너무 귀찮다... 어떻게 하면 편하게 지울 수 있을 까?
    모든 테이블을 지울 때에는
    SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;

    CASCADE CONSTRAINTS는 부모, 자식 테이블에서 우선적으로 자식테이블을 삭제한 후에야 부모테이블을 지울 수 있는데 이 옵션으로 참조되어진 모든 값을 오류없이 같이 지워버리기 때문에 사용, 단 참조값들은 참조 연결만 끊어지고 삭제되지 않는다. = 제약조건 삭제
- DELECATE TRUNCATE DTOP의 차이 (밑의 표 참조)

	장	단
DELETE( DML )	원하는 데이터 삭제	테이블 용량 감소 안한다.
TRUNCATE ( DDL )	테이블 자료 모두 삭제	원하는 데이터만 삭제 불가, 복구가 힘듬( ROLLBACK 안됨. )
DROP ( DDL )	테이블 자체 삭제

'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글

190531_DAY <뷰>, <시퀀스>, <PL/SQL> (0)	2019.05.31
190530_DAY36 트랜잭션, 무결성제약조건, 데이터 딕셔너리 (0)	2019.05.30
190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작 (0)	2019.05.28
190527_DAY33 decode, case , join (0)	2019.05.27
190524_Day32 DB3 복습 및 진도 (0)	2019.05.24

Yong Study

190529_DAY35, <복습>, <DDL>, <DML>

'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글

+ Recent posts

티스토리툴바