SQL (2)

28 Mar 2019

4. DML: INSERT, UPDATE, DELETE

-- DDL
CREATE TABLE tableTest(
    id	int	null,  -- 비워놓아도 상관 x 
    name    varchar(10)  
);

ALTER TABLE tableTest add(regdate datetime)

DROP table tableTest;

INSERT

INSERT INTO tableTest(id, name, regdate) VALUES (1, 'kimmy', '2019-03-26')
INSERT INTO tableTest(id, regdate) VALUES (2, now())
INSERT INTO tableTest VALUES 0;
INSERT INTO tableTest VALUES (3, 'sally', now())

UPDATE

UPDATE tableTest
SET name = 'scott'
WHERE id = 3;

DELETE

DELETE FROM tableTest WHERE id = 3;

5. Sub Query

다른 query문에 포함된 query문

반드시 () 사용

반드시 연산자의 오른쪽에 와야함

ORDER BY 사용 불가

연산자

단일행 연산자

다중행 연산자: IN, ANY, ALL

다중열 비교

-- scott의 급여보다 더 많이 받는 직원의 이름, 업무, 급여를 조회
/*두번의 조회를 해야함*/
SELECT sal FROM scott_emp WHERE ename='scott'; 
SELECT ename, job, sal FROM scott_emp WHERE sal > 3000; 

--> 
SELECT ename, job, sal FROM scott_emp 
WHERE sal > (SELECT sal FROM scott_emp  WHERE ename='scott');


-- 사번이 7521의 업무와 같고, 급여가 7934보다 많은 직원의 사번, 이름, 업무, 급여조회
/*세번의 조회를 해야함*/
SELECT sal FROM scott_emp WHERE empno='7934';
SELECT job FROM scott_emp WHERE empno='7521';
SELECT ename, job, sal FROM scott_emp WHERE job='salesman' AND sal > 1300;

--> 
SELECT ename, job, sal FROM scott_emp 
WHERE job = (SELECT job FROM scott_emp WHERE empno='7521') 
AND sal > (SELECT sal FROM scott_emp WHERE empno='7934');

-- 업무별로 최소급여를 받는 직원의 사번, 이름, 부서코드를 조회
SELECT DISTINCT job FROM scott_emp;

SELECT min(sal) FROM scott_emp WHERE job='CLERK';
SELECT min(sal) FROM scott_emp WHERE job='SALESMAN';
SELECT min(sal) FROM scott_emp WHERE job='MANAGER';
SELECT min(sal) FROM scott_emp WHERE job='ANALYST';
SELECT min(sal) FROM scott_emp WHERE job='PRESIDENT';

SELECT job, min(sal) FROM scott_emp 
GROUP BY job;

SELECT empno, ename, deptno FROM scott_emp 
WHERE sal IN (800, 3000, 2450, 5000, 1250);

-->
SELECT empno, ename, deptno FROM scott_emp 
WHERE sal IN (SELECT min(sal) FROM scott_emp GROUP BY job);

-- note: FROM 절에서 SubQuery 사용 시, 테이블 이름이 사라진 경우 별명 꼭 붙이기
SELECT * FROM (SELECT * FROM scott_emp WHERE deptno=30 ) as t;

ANY: 단일행 연산자

-- 업무별로 최소급여보다 많이 받는 직원의 사번, 이름, 부서코드를 조회
SELECT empno, ename, deptno, sal FROM scott_emp
WHERE sal > 800 OR sal > 3000 OR sal > 2450 OR sal > 5000 OR sal > 1250;

SELECT empno, ename, deptno, sal FROM scott_emp
WHERE sal > 800;

-- 합치면
SELECT empno, ename, deptno ,sal, job FROM scott_emp
WHERE sal > ANY(SELECT min(sal) FROM scott_emp GROUP BY job);

ALL: 다중행 연산자

SELECT empno, ename, deptno, sal FROM scott_emp
WHERE sal >= 3000 AND sal >= 1300 AND sal >= 2975 AND sal >= 5000 AND sal >= 1600;

SELECT empno, ename, deptno ,sal, job FROM scott_emp
WHERE sal >= ALL(SELECT max(sal) FROM scott_emp GROUP BY job);

다중열 비교

SELECT sal, comm FROM scott_emp WHERE ename = 'miller';

-- 급여와 커미션이 30번 부서에 있는 직원의 급여와 커미션이 같은 직원에 대해 사번, 이름, 부서번호, 급여, 커미션 조회
SELECT ename, sal, comm, deptno FROM scott_emp WHERE deptno = 30;

-- 동시에 비교할 수 없음. 밀러가 나오면 안되는 결과임
SELECT empno, ename, deptno ,sal, job, comm FROM scott_emp
WHERE sal = ANY(SELECT sal FROM scott_emp WHERE deptno = 30) AND comm = ANY(SELECT comm FROM scott_emp WHERE deptno = 30);

SELECT empno, ename, deptno ,sal, job, comm FROM scott_emp
WHERE sal IN (SELECT sal FROM scott_emp WHERE deptno = 30) 
AND comm IN (SELECT comm FROM scott_emp WHERE deptno = 30); 

-->
SELECT empno, ename, deptno ,sal, job, comm FROM scott_emp
WHERE (sal, comm) IN (SELECT sal, comm FROM scott_emp WHERE deptno=30); 

-->
SELECT empno, ename, deptno ,sal, job, comm FROM scott_emp
WHERE sal = ALL(SELECT sal FROM scott_emp WHERE deptno = 30) AND comm = ALL(SELECT comm FROM scott_emp WHERE deptno = 30);

UPDATE scott_emp
SET sal = 1500 , comm = 300
WHERE ename ='miller';

상관 서브 쿼리

밖에 있는 서브쿼리가 먼저 시작해서 밖에서 가져다 준 결과를 안으로 다시 처리

상관서브쿼리는 서브쿼리만 실행시 실행되지 x
전체를 같이 실행시 서브쿼리도 같이 실행
서브쿼리는 상관이 되어 있음 -> 퍼포먼스가 안좋음

일반서브쿼리는 서브쿼리만 실행시 실행되지 x

-- 적어도 한명의 직원으로부터 보고를 받을 수 있는 직원의 이름, 업무, 입사일자, 급여를 조회
SELECT DISTINCT mgr FROM scott_emp;
SELECT ename, job, hiredate, sal FROM scott_emp
WHERE empno = 7902 OR empno = 7698 OR empno = 7839  OR empno = 7566 OR empno = 7782;

--> 
SELECT ename, job, hiredate, sal FROM scott_emp as  e1
WHERE exists (SELECT * FROM scott_emp as e2 WHERE e1.empno = e2.mgr);