190529_DAY35, <복습>,
<조인>
- 두 개 이상의 테이블을 참조해서 데이터를 검색
- from 뒤에 두개이상의 테이블 정의
조인의종류)
cross join, equi join, non-equi join, self join, outer join
cross join
- Where절 없이 사용하는 조인
- 각 테이블이 갖는 행의 곱만큼 결과행을 생성
FROM emp, deptequi join
- 테이블 간의 공통된(연관된) 칼럼을 '='을 통해 비교
FROM emp, dept WHERE emp.deptno = dept.deptnonon-equi join
- 대소비교, 범위를 표현하는 연산자를 사용할 때
FROM emp, salgrade WHERE emp.sal between salgrade.losal AND salgrade.hisalself 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(+);
<ANSI 조인>
- 표준조인, DBMS종류와 상관없이 실행.
- FROM 뒤에 ',' 콤마를 사용하지 않는다.
- cross join, inner join, natural join, outer join
cross join
FROM emp CROSS JOIN deptinner 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 deptouter 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 |
