Govern/Sql developer -DML,DDL,제약

07-01(목) 서브쿼리DML씨탁스

Mary's log 2021. 7. 1. 13:01

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

서브쿼리  복수 연산자

-반드시 복수 행 연산자 사용해야함. 단일행 연산자 사용X

(연산자종류)

IN : 결과값이 복수일때
ANY : <  > 사용, 하나라도 일치하면 참
ALL : <  > 사용, 모든 값이 일치하면 참

EXIST: 반환값이 존재하면 메인쿼리실행, 없으면 실행X

*여기서 ANY, ALL은 연산자 제외하고 그냥 < > 부등호만 써도 될까?

  ㄴ안됨! WHERE 컬럼 < (SELET~)이면 서브쿼리의 반환값이 여러개라 이 중 뭐보다 작고 커야하는지 인식이 안됨

  ㄴ그래서 꼭 부등호 뒤에 ANY나 ALL 넣어야함

 

■ SELECT * FROM 테이블

WHERE 컬럼 IN (SELECT ~서브쿼리 반환값이 여러개 나와야함) ;

-서브쿼리 값이 여러개인데(서브쿼리절) 그거랑 맞는 컬럼의 조건이(WHERE절), 

반영된 테이블의(FROM절), 전부(*)컬럼

예제)
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY JOB ) ;

--(JOB을 묶고 각각 최소값 월급을 나타내는 서브쿼리)

--그 값들을 월급을 지닌 레코드의

--사번,이름,등등을 골라라. 그러면 최소값이 5개 나오고, 전체레코드에선 적어도5개 이상(그 이상은 중복으로) 나오겟지

 

SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE>
(SELECT HIREDATE
FROM EMP
WHERE ENAME='FORD');


SELECT ENAME, SAL                    --이름과 월급을 나타내라. 그럼 최소2개(WARD,FORD)와 같은 월급자들이 나옴
FROM EMP
WHERE SAL IN                            --같은 값을 지닌 레코드들의 
(SELECT SAL                               -- 월급 2개의 값과
FROM EMP
WHERE ENAME IN('WARD', 'FORD')) ; --사원이름이 WARD,FORD일때
--또는 ENAME='WARD' OR ENAME='FORD';

 

■ SELECT * FROM 테이블

WHERE 컬럼 > < ALL (SELECT ~서브쿼리 반환값이 여러개여야함) ;

-서브쿼리 값이 여러개인데(서브쿼리절) 그거랑 모두ALL 맞는 컬럼의 조건이(WHERE절), 

반영된 테이블의(FROM절), 전부(*)컬럼

-WHERE 컬럼  >   ALL  ( SELECT ~  ) : '서브커리의 최소값'보다 크면 된다는거
-WHERE 컬럼  <   ALL  ( SELECT ~  ) : '서브커리의 최대값'보다 작으면 된다는거

 

예제) SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL
(SELECT SAL
FROM EMP
WHERE JOB='MANAGER');


■ SELECT * FROM 테이블

WHERE EXISTS (SELECT ~서브쿼리 존재여부?) ;

-결과가 하나라도 존재하는지 '여부를 확인'
-결과가 하나라도 없으면 FALSE ➤ 메인쿼리 실행 안됨

-결과가 하나라도 있으면 TRUE  ➤ 메인쿼리 실행 됨

 

 

서브쿼리  다중컬럼 서브쿼리

■ PAIRWISE

   ㄴ컬럼을 쌍으로 묶어서 동시비교
-서브쿼리 SELECT 뒤의 컬럼이,

  앞의 WHERE 절 컬럼과
  이름, 순서, 갯수가 다 맞아야함
★ex)
SELECT DEPTNO, EMPNO, ENAME, SAL    --이건 서브쿼리값이랑 어차피 같은데 뭐하러 쓴담...?
FROM EMP
WHERE(DEPTNO, SAL)                --그것들과 부서번호와 월급이 같은
IN (SELECT DEPTNO, MAX(SAL)  --부서번호와, 각각 부서번호의 월급 최대값들을 지닌 레코드들.
FROM EMP
GROUP BY DEPTNO); --복수행, --부서번호로 묶고
--서브쿼리의 SELECT DEPTNO는 앞의 WHERE(DEPTNO, 에 붙고,
--서브쿼리의 SELECT MAX(SAL)은 앞의 WHERE (-, SAL)과 붙어야함

 

■ UNPAIRWISE  ★★이걸 더 중요하게 보기

   ㄴ컬럼별로 나누어 비교하고 나중에 AND 연산처리
-서브쿼리를 2개 만듦. 결과값은 위랑 같은데 하나였던 서브쿼리를 2개로 나눈거임
ex) SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO   --부서번호들을 지닌 레코드들,과 일치하는 부서번호
FROM EMP
GROUP BY DEPTNO)                        --부서번호로 묶고
AND SAL IN (SELECT MAX(SAL)          --월급 최대값들을 지닌 레코드들,과 일치하는 월급들
FROM EMP
GROUP BY DEPTNO);                      --부서번호로 묶고

 

∴대충 정리하면

 

SELECT 컬럼1,컬럼2 FROM TABLE(이후 T)

WHERE (컬럼a, 컬럼b) IN (SELECT 컬럼a, 컬럼b' FROM T GROUP BY 컬럼a)

  ㄴ이게 pairwise

 

SELECT 컬럼1,컬럼2 FROM TABLE(이후 T)

WHERE 컬럼a IN (SELECT 컬럼a  FROM T GROUP BY 컬럼a)

AND    컬럼b IN (SELECT 컬럼b' FROM T GROUP BY 컬럼a)

  ㄴ이게 unpairwise

 

★인라인 뷰★

   ㄴFROM 뒤에도 쓸수 있음!!

-필요한 컬럼들로 또다른 가상의 테이블을 만들고, 거기서 필요한 것만 꺼내서 쓰겠다

 

서브쿼리 ➤ SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(sAL) TOTAL_AVG, COUNT(*) CNT
FROM EMP
GROUP BY DEPTNO;                          --부서번호로 묶고

메인 쿼리 ➤ SELECT E.DEPTNO, TOTAL_SUM, TOTAL_AVG, CNT
FROM (SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(sAL) TOTAL_AVG, COUNT(*) CNT FROM EMP  GROUP BY DEPTNO) E, DEPT D                --서브쿼리로 위에서 만든 가상테이블1 "별칭은 E" 과
                                              --기존에 있던 DEPT 테이블2 "별칭은 D"
WHERE E.DEPTNO=D.DEPTNO;      --조인

 

★상관 서브쿼리(스칼라 서브쿼리)★

1 row 1 column 반환

-바깥 쪽 쿼리의 컬럼 중 하나가, 안쪽 서브쿼리의 조건에 이용됨
예제로 반복하면서 이해하기

<조인을 쓴 SQL>
메인쿼리➤ SELECT * FROM EMP E, DEPT D   
WHERE E.DEPTNO=D.DEPTNO; --오라클조인

메인쿼리➤ SELECT DNAME 부서명 FROM EMP E, DEPT D   ---2. 부서명만 보여줘
WHERE E.DEPTNO=D.DEPTNO;  --1. 오라클조인으로  EMP와 DEPT를 연결한 것 중에서

= 위아래 마크한게 결국 같은 말임

<서브쿼리를 쓴 SQL>
서브쿼리➤ SELECT DNAME FROM EMP, DEPT    --1.오라클조인하고 부서명만 보여줘
WHERE DEPT.DEPTNO=EMP.DEPTNO;  --2.근데 메인쿼리의 EMP에 넣으면 같은 EMP가 중복이니까
                                                     서브쿼리의 EMP는 지우고 메인쿼리의 EMP별칭 E로 바꿔줌

메인쿼리➤ SELECT 
(SELECT DNAME FROM DEPT 
WHERE DEPT.DEPTNO=E.DEPTNO)"부서명"--이 서브쿼리만 쓸 수 없는게 메인쿼리의 E.DEPTNO 별칭이 쓰였음
FROM EMP E ;

 

 


♬♬♬♬실습문제♬♬♬♬ 웤샵아님

↓더보기 누르기

더보기

1. 사원 테이블에서 BLASKE보다 급여가 많은 사원들의 사번,이름,급여를 검색하시오

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>
(SELECT SAL
FROM EMP
WHERE ENAME='BLAKE');

2. 사원 테이블에서 MILLER보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오.

SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE<
(SELECT HIREDATE
FROM EMP
WHERE ENAME='MILLER');

3. 사원 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 부서코드, 급여를 검색하시오.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>
(SELECT AVG(SAL) FROM EMP);

4. 사원 테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색하시오.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL=
(SELECT MAX(SAL)
FROM EMP);

--8번 먼저. 'WARD'와 입사년도가 같은 사원의 이름과 입사년도를 검색하시오
SELECT ENAME, TO_CHAR(HIREDATE,'YYYY')입사년도
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY')=
(SELECT TO_CHAR(HIREDATE,'YYYY')
FROM EMP
WHERE ENAME='WARD');

--5번 SALGRADE가 2등급인 사원들의 평균 급여보다 적게 받는 사원 정보를 검색하시오.
SELECT * FROM EMP
WHERE SAL<
(SELECT AVG(SAL) FROM EMP
JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL
WHERE GRADE=2);  

--6번 'SMITH'의 급여등급과 같은 등급의 사원이름과 등급을 검색하시오
--ANSI 조인 JOIN USING,ON쓰면 ANSI임
SELECT ENAME, GRADE
FROM EMP
JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL
where grade=
(SELECT GRADE FROM emp
JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL
WHERE ENAME='SMITH');
--오라클 조인
SELECT ENAME, GRADE
FROM EMP E, SALGRADE S
WHERE SAL BETWEEN LOSAL AND HISAL
AND grade=
(SELECT GRADE FROM emp
JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL
WHERE ENAME='SMITH'); 

--7번 'SALES' 부서의 인원수보다 작은 부서의 부서명과 인원수를 검색하시오


 

--9번 'SMITH'의 부서이름, 부서지역 검색(조인 이용하지 말 것)

--오라클조인 

SELECT ENAME, DNAME, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND ENAME='SMITH';

--ANSI조인

SELECT ENAME, DNAME, LOC
FROM EMP E
JOIN DEPT D
USING (DEPTNO)
WHERE ENAME='SMITH';

SELECT ENAME, DNAME, LOC
FROM EMP E
JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
AND ENAME='SMITH';
--서브쿼리로만 사용 (조인 사용 안하고)
SELECT ENAME, DNAME, LOC
FROM EMP E, DEPT D
WHERE D.DEPTNO=(
SELECT E.DEPTNO FROM EMP
WHERE ENAME='SMITH')
AND ENAME='SMITH';   

 

♬♬♬♬실습문제 끝♬♬♬♬

 

★★★★웤샵..★★★★

 DML 

Data Manipulation Language

 

■ 의사코드 ROW NUM        => '페이지수 보기' 할 때 잘 쓰임

&amp;amp;nbsp;&amp;amp;nbsp;

ex) SELECT*FROM EMP
WHERE ROWNUM<10; --원래 12행인데 10미만,9개만 보여짐 --'페이지 5개씩 보기' 이런거

 

■ ROW ID

&amp;amp;nbsp;&amp;amp;nbsp;

SQL( ) 종류 명령문
DML 데이터 조작어 +commit必要
(Data Manipulation Language)		 insert   (신규 데이터 입력)  --행추가
update (기존 데이터 수정)
delete  (기존 데이터 삭제)
TCL 트랜잭션 처리어
(Transaction Control Language)		 commit    (트랜잭션 확정)
rollback   (트랜잭션 취소)
savepoint (책갈피)
*내일 수업
DDL 데이터 제어어 +commit안해도자동
(Data Definition Language)		 create (객체 생성)
alter   (객체 수정)
drop   (객체 삭제)  --보통 프로젝트 '드롭'했다고 하듯
rename  (객체 이름 변경)
truncate (객체 저장공간 삭제)

 

책 236쪽

일단 DEPT 테이블 들어가서 [열] 보기

 

ex) 

-DEPT테이블의 열 보기
DEPTNO의 NUMBER (4.0) =>4자리 숫자
DNAMEDML VARCHAR2(14BYTE) =>한글 4글자, 영어 7글자까지만 사용가능하고 그 이상은 오류남

*오라클은 한글 3바이트 사용, 영어 2바이트 사용


-EMP테이블의 열 보기
HIREDATE의  DATE => RR/YYYY 뭐 이런거...

*NOT NULL, NULLABLE NO =>해당 컬럼에는 NULL값이 올 수 없음!!! 

*기본키          =Primary Key ( [MODEL]에선 짧게 P) 중복안되고, 널값도 안됨

 참조키,외래키 =Foreign Key ( [MODEL]에선 짧게 F) 다른 테이블을 참조하고 있다는 뜻임 

Q) 그럼, 어떤 직원 한명 찾으려면 뭐로 찾는게 제일 좋을까?

A) 사번.  이름은 동명이인이 있을 수 있음

DML  ★★★★INSERT INTO VALUES 단일행 입력★★★★

(새로운 행 추가) =>이건 입력순서 매일 복습하기 나중에 자바에서 씀

 

   INSERT INTO 테이블명 (컬럼명들...)

   VALUES (값들...) ; 

-INTO 테이블 명 뒤에 컬럼명들이 전부 생략될거면,

 VALUES의 값들은 테이블생성시 컬럼순서와 맞춰서 적어줘야함

 

ex) INSERT INTO DEPT(DEPTNO,DNAME,LOC)
VALUES (90,'인사과','서울');
COMMIT;

**이건 책내용**

■ 데이터 저장 시점에서 값을 모르거나 확정되지 않았을 때

NULL값 저장되는 컬럼에 저장하는 방법 (기본키나 안되는 컬럼엔X)

 

①묵시적 방법 = 자동적 방법

원래는   INSERT INTO 테이블(컬럼A, 컬럼B, 컬럼C ) VALUES ( 값1, '값2', 널값 ) ; 인데

컬럼C가 기본키X고 널값사용가능 컬럼이면,

INSERT INTO 테이블(컬럼A, 컬럼B ) VALUES ( 값1, '값2' ) ; 이렇게 해서 컬럼C에 널값이 들어가게 하는 방법

근데, 널값이 아니고 0이나 공백을 넣고 싶다면 0이나 ' '를 명시적 방법으로 꼭 적어야함

 

②명시적 방법

 직접 NULL 이나 ''를 눈에 보이게 직접 입력하는 방법

 

 

■ 쌤이 알려준 방법 ■  =>이건 입력순서 매일 복습하기
1. INSERT 사용예시 1:모든 컬럼을 다 명시
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(90, '인사과','서울');
2. 1:모든 컬럼을 다 명시 => 순서변경
INSERT INTO DEPT(DEPTNO, LOC, DNAME) VALUES(80, '경기','인사과');
3. VALUES에 모든 컬럼을 다 적을 때 컬럼명 삭제가능
INSERT INTO DEPT VALUES (70, '영업','강남');
4. 특정컬럼에 NULL 저장 (묵시적)
INSERT INTO DEPT(DEPTNO, LOC) VALUES(65, '제주');
5. 특정컬럼에 NULL 저장 (명시적)
INSERT INTO DEPT(DEPTNO, DNAME,LOC) VALUES (60, NULL, null);
INSERT INTO DEPT VALUES (50, 'AA', NULL);

-롤백=>테이블 위에서 삭제하고 COMMIT 누르기

 

DML  테이블에 특정데이터 입력

① 특수값 입력 (SYSDATE, USER)

INSERT INTO EMP 

VALUES (9000, USER, '연구원', 7839, SYSDATE, 5000, NULL, 90); 

*USER(=사용하고 있는 계정이름),SYSDATE 가 자동으로 반영됨

*USER는 명령어기 때문에 하나의 이름으로 쓰려면 안됨

 

근데,

★★Q) 왜 아래 에러가 나는지?★★

명령의 3 행에서 시작하는 중 오류 발생 -
INSERT INTO EMP(EMPNO, ENAME, JOB, 
MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(9000,USER,'연구원',7839,SYSDATE,5000,NULL,90)
오류 보고 -
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

====>>맨 마지막 DEPTNO가 DEPT 테이블의 참조키인데
DEPT테이블에 DEPTNO=90이 없음.
DEPT테이블에 DEPTNO 90 INSERT,COMMIT하고
EMP INSERT 다시 하면 삽입됨


② 특정 데이터 타입으로 입력( RR/MM/DD 형식, TO_DATE()사용 )
INSERT INTO EMP(EMPNO, ENAME,JOB,MGR, HIREDATE ,SAL,COMM,DEPTNO)
VALUES(9001,'홍길동','MANAGER',7839, '2000/01/01' ,2000,NULL,30); --RR/MM/DD형식

INSERT INTO EMP(EMPNO, ENAME, JOB,MGR, HIREDATE ,SAL,COMM,DEPTNO)
VALUES(9002,'임꺽정','MANAGER',7839, TO_DATE('1999-12-13','YYYY/MM/DD') ,2000,NULL,30);

 

 

책247쪽

 INSERT 다중 행 입력

-서브쿼리로 다중입력 가능

-테이블 삭제:
테이블 오른쪽 클릭하고 [ 테이블-삭제-되도록 '계단식 제약조건,비우기' 체크체크하고 '적용' ]

 

 

★★★ CTAS  ★★★
DML은 아닌데 기존테이블의 틀,타입만 가져옴

=> 기존 테이블을 신규 테이블로 복사가능!!! 원하는 컬럼과 해당컬럼형식도 가져올수있음

 

① 컬럼과 형식 복사해서 테이블 생성하기(안에 데이터는 없는상태) 껍데기만 가져오기

CREATE TABLE COPY_EMP
AS
SELECT EMPNO, ENAME    --사실 이건 서브쿼리인거임
FROM EMP WHERE 1=2;  

--수학적으로 1은 2가 아님. 오류는 아니고 false기 때문에, 데이터는 아무것도 안뜨고, 컬럼 EMPNO,ENAME 헤더만 뜸.

--그걸 신규 테이블에 [헤더와 데이터형식]까지 복사하는 거임, 대신 제약조건은 복사가 안됨

 

② 테이블에 한꺼번에 데이터 넣는, 다중 행 입력하기 *이땐 VALUES 안쓰고 컬럼 이름은 맞아야함

INSERT INTO 테이블 (컬럼1, 컬럼2, ....)

SELECT 컬럼1, 컬럼2, ...  FROM EMP;

 

저작자표시 비영리 변경금지 (새창열림)