| 니앙팽이 - DB | 8 | SQL - DML(데이터 조작어)

8. DML 데이터 조작어

---

1. INSERT

1. 단일 행튜레 삽입

/******************************************************
* 기본형
******************************************************/
INSERT
INTO        _TABLE_(COFIATT1, COFIATT2, COFIATT3, ...)
VALUES       (VAL1, VAL2, VAL3, ...)

/******************************************************
* 모든 속성 사용시
******************************************************/
INSERT INTO _TABLE VALUES(VAL1, VAL2, VAL3, ...)

/******************************************************
* SELECT문으로 삽입
******************************************************/

INSERT
INTO        _TABLE_(COFIATT1, COFIATT2, COFIATT3, ...)
SELECT      ...     FROM        ...         WHERE

INSERT      INTO HIGH_SALARY(ENAME, TITLE, SAL)
SELECT      EMPNAME, TITLE, SALARY
FROM        EMPLOYEE
WHERE       SALARY >= 300000;

---

2. DELETE

/******************************************************
* 기본형
******************************************************/
DELETE 
FROM        _TABLE_ 
WHERE       _조건_

---

3. UPDATE

/******************************************************
* 기본형
******************************************************/
UPDATE      _TABLE_
SET         COFIATT1 = VAL1, COFIATT2 = COFIATT2 * 1.05
WHERE       EMPNO = 2106

---

4. SELECT-1

1. SELECT 기본 검색

/******************************************************
* 1. 전체 선택
******************************************************/
SELECT * FROM _TABLE_

/******************************************************
* 2. 일부 열 선택
******************************************************/
SELECT COFIATT1, COFIATT2, COFIATT3 FROM _TABLE_

/******************************************************
* 3. 하나의 결과 중복 제거
******************************************************/
SELECT DISTINCT COFIATT FROM _TABLE_

/******************************************************
* 4. 산술 연산 사용
******************************************************/
SELECT COFIATT1, COFIATT2, COFIATT3(+,-,*,/)_NUM_ FROM _TABLE_

/******************************************************
* NULL AS
******************************************************/
SELECT 
    COFIATT,
    NULL AS NONDEFINITED_COFIATT
FROM 
    _TABLE_x

2. WHERE 조건 지정 검색

/******************************************************
* 1. 비교 연산자
******************************************************/

SELECT _ FROM _ WHERE   _COFIATT_   =         _VAL_;
SELECT _ FROM _ WHERE   _COFIATT_   <>        _VAL_;
SELECT _ FROM _ WHERE   _COFIATT_   (>, >=)   _VAL_;
SELECT _ FROM _ WHERE   _COFIATT_   (<, <=)   _VAL_;

/******************************************************
* 2. 논리 연산자
******************************************************/

SELECT _ FROM _ WHERE   _COFIATT_   NOT       _VAL_;
SELECT _ FROM _ WHERE   _COFIATT1_ > VAL1 AND _COFIATT2_ > VAL2
SELECT _ FROM _ WHERE   _COFIATT1_ > VAL1 OR _COFIATT2_ > VAL2

/******************************************************
* 3. LIKE 연산자
******************************************************/
SELECT _ FROM _ WHERE   EMPNAME LIKE "str%"

SELECT _ FROM _ WHERE INTAKE_CONDITION NOT IN ("Aged")
SELECT _ FROM _ WHERE INTAKE_CONDITION <> "Aged"
SELECT _ FROM _ WHERE INTAKE_CONDITION NOT LIKE "%Aged%"


/******************************************************
* 4. 널값 비교
* SELECT _ FROM _ WHERE _COFIATT_ = NULL (❌)
******************************************************/
SELECT _ FROM _ WHERE   _COFIATT_ IS NULL;
SELECT _ FROM _ WHERE   _COFIATT_ NOT NULL;

/******************************************************
* 5. 이하는 모두 거짓
******************************************************/
NULL <  300
NULL =  300
NULL <> 300
NULL =  NULL
NULL <> NULL

3. ORDER BY 정렬 검색

ORDER BY COFIATT1 ASC, COFIATT2 DESC;

4. 서브쿼리

중첩질의 & 한개의 COFIATT반환
조건절에 서브쿼리를 넣어서 그 검색 결과를 피연산자로 사용한다.

SELECT _ FROM _ WHERE COFIATT = (_SUBQUARY_)
SELECT _ FROM _ WHERE COFIATT IN (1, 2, 3, 4)
SELECT _ FROM _ WHERE COFIATT NOT IN (1, 4)
SELECT _ FROM _ WHERE COFIATT {=, <>, <, >} ANY (1, 2, 3, 4)
SELECT _ FROM _ WHERE COFIATT {=, <>, <, >} ALL (1, 2, 3, 4)

/******************************************************
* 동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.
******************************************************/

SELECT 
    NAME
FROM 
    ANIMAL_INS
WHERE DATETIME = (
    SELECT MIN(DATETIME)
    FROM ANIMAL_INS
);

중첩질의의 결과가 빈 테이블(릴레이션)인지 확인하기

SELECT _ FROM _ WHERE EXISTS = (_SUBQUARY_)

SELECT      EMPNAME
FROM        EMPLOYEE AS E
WHERE       EXISTS 
            (
                SELECT      *
                FROM        DEPARTMENT AS D
                WHERE       E.DNO = D.DEPTNO
                            AND (DEPTNAME = "영업" OR DEPTNAME = "개발")
            );

# 다음 중첩 질의에 E의 COFIATT을 가져온 것을 볼 수 있다.
# 외부 질의에 선언된 일부 COFIATT을 참조하는 질의를
# "상관 중첩 질의" 라고 한다.

---

5. SELECT-2

0. 그룹 함수

한 COFIATT에 적용되고, 단일값만 반환한다.
SELECT 절이나, HAVING 절에서만 사용 가능
DISTINCT 키워드 사용 가능 집단함수가 적용되기 전에 먼저 중복을 제거함

그룹 함수	기능
COUNT(COFIATT)	행튜레의 값들의 개수
SUM(COFIATT)	값들의 합
AVG(COFIATT)	값들의 평균값
MAX(COFIATT)	값들의 최대값
MIN(COFIATT)	값들의 최소값
STDDEB(COFIATT)	값들의 표준편차
VARIANCE(COFIATT)	값들의 분산을 구함

1. GROUP BY

묶인 COFIATT의 중복제거를 하고나온 _Value_들 각각각 으로 행튜레가 묶인다.

• 이떄 사용된 COFIATT을 grouping attribute라고 한다.
• 그리고 SELECT절에 결과가 하나의 값을 가지는 COFIATT, 집단함수, grouping attribute으로 COFIATT이 이루어지는 테이블(릴레이션)이 생긴다.

SELECT      DNO, AVG(SALARY), MAX(SALARY)
FROM        EMPLOYEE
GROUP BY    DNO

/******************************************************
* 1. <상여금> 테이블에서 부서별 상여금의 평균을 구하시오
******************************************************/
SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서

/******************************************************
* 2 : <상여금> 테이블에서 부서별 튜플 수를 검색하시오.
******************************************************/

SELECT 부서, COUNT(*) AS 근무자수
FROM 상여금
GROUP BY 부서

/******************************************************
* 3 : <상여금> 테이블에서 '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오.
******************************************************/

SELECT 부서, COUNT(*) AS 근무자수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2

/******************************************************
* 예제 4 : <상여금> 테이블의 '부서', '상여내역' 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. 
* (단, 속성명은 '상여금합계'로 하고, ROLLUP 함수를 사용할 것)
* GROUP BY ROLLUP(_COFFIATT1_, _COFFIATT2_, ...) 결과에 대해 소계와 전체 합계를 적어준다.
******************************************************/

SELECT 부서, 상여내역, SUM(상여금)상여금합계
FROM 상여금
GROUP BY ROLLUP(부서(KEY), 상여금합계);

/******************************************************
* 동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.
******************************************************/

SELECT
    COUNT(DISTINCT NAME) AS "COUNT"
FROM
    ANIMAL_INS;

/*OR*/

SELECT 
    COUNT(a.name)
FROM (
    SELECT  NAME
    FROM ANIMAL_INS
    GROUP BY NAME
)

/******************************************************
* PRODUCT 테이블과 OFFLINE_SALE 테이블에서 
* 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 
* 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.
******************************************************/

SELECT PRODUCT_CODE, PRICE * SUM(SALES_AMOUNT) AS SALES
FROM
    PRODUCT INNER JOIN OFFLINE_SALE
    USING(PRODUCT_ID)
GROUP BY PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC;

/******************************************************
* 만원 단위의 가격대 별로 상품을 나누면
* 가격대가 0원 ~ 1만원 미만인 상품은 PRODUCT_ID 가 2인 상품 1개,
* 가격대가 1만원 이상 ~ 2만원 미만인 상품들은 PRODUCT_ID 가 1, 4, 6인 상품 3개,
* 가격대가 2만원 이상 ~ 3만원 미만인 상품은 PRODUCT_ID 가 3인 상품 1개,
* 가격대가 3만원 이상 ~ 4만원 미만인 상품은 PRODUCT_ID 가 5인 상품 1개,
* 에 각각 해당하므로 다음과 같이 결과가 나와야 합니다.
******************************************************/
SELECT 
    FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP,
    COUNT(*) AS PRODUCTS
FROM 
    PRODUCT
GROUP BY 
    PRICE_GROUP
ORDER BY
    PRICE_GROUP;
/******************************************************
* 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 
* 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 
* 이때 결과는 시간대 순으로 정렬해야 합니다.
******************************************************/
SELECT 
    HOUR(DATETIME) AS HOUR,
    COUNT(*) AS "COUNT"
FROM 
    ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR <= 19
ORDER BY HOUR

2. HAVING

• 만들어진 그룹에 대해 HAVING절의 COFIATT는 "중복되지 않는, 행튜레에 단 하나의 값만 나타나는 그룹을 묶었던 grouping attribute" 또는 "집단함수에 포함된" 가져와 조건을 명시한다.
• 그리고 그 조건에 맞는 그룹 테이블(릴레이션) 행튜레 값을만 질의 결과에 나타난다.

SELECT      DNO, AVG(SALARY), MAX(SALARY)
FROM        EMPLOYEE
GROUP BY    DNO
HAVING      AVG(SALARY) >= 2500000;

---

6. JOIN & 통합질의

/******************************************************
1. 내부 조인
******************************************************/
SELECT      ...
FROM        
    R_TABLE AS R INNER JOIN S_TABLE AS S
    ON R.COFIATT {=, <>, <, >} S.COFIATT;

/******************************************************
1-1. 만약 두 테이블의 행 이름이 같다면 중복 제거를 위해
******************************************************/
SELECT      ...
FROM        
    R_TABLE AS R INNER JOIN S_TABLE AS S
    USING(COFIATT)

/******************************************************
2. 외부 조인
******************************************************/
# 왼쪽 조인
SELECT      ...
FROM        
    R_TABLE AS R LEFT OUTER JOIN S_TABLE AS S
    ON R.COFIATT {=, <>, <, >} S.COFIATT;

# 오른쪽 조인
SELECT      ...
FROM
    R_TABLE AS R RIGHT OUTER JOIN S_TABLE AS S
    ON R.COFIATT {=, <>, <, >} S.COFIATT;x

# FULL OUTER JOIN
SELECT      ...
FROM
    R_TABLE AS R FULL OUTER JOIN S_TABLE AS S
    ON R.COFIATT {=, <>, <, >} S.COFIATT;

/******************************************************
* '경제' 카테고리에 속하는 도서들의 
* 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
* 결과는 출판일을 기준으로 오름차순 정렬해주세요.

* 효율적인 JOIN을 위해서
* 미리 ON에 조건절을 붙인다. 
******************************************************/

SELECT 
    BOOK_ID, 
    AUTHOR_NAME, 
    DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM 
    BOOK AS B INNER JOIN AUTHOR AS A
    ON (B.CATEGORY = '경제') AND (B.AUTHOR_ID = A.AUTHOR_ID)
ORDER BY 
    PUBLISHED_DATE;


SELECT NAME, ANIMAL_INS.DATETIME
FROM
    ANIMAL_INS LEFT OUTER JOIN ANIMAL_OUTS
    USING(ANIMAL_ID,ANIMAL_TYPE,NAME)
WHERE NAME IS NOT NULL AND ANIMAL_OUTS.DATETIME IS NULL
ORDER BY ANIMAL_INS.DATETIME
LIMIT 3;

# 자체 조인
SELECT      E.EMPNAME, M.EMPNAME
FROM        EMPLOYEE AS E, EMPLOYEE AS M
WHERE       E.MANAGER = M.EMPNO

# 조인과 ORDER BY의 결합
SELECT      DEPTNAME, EMPNAME, TITLE, SALARY
FROM        EMPLOYEE AS E, DEPARTMENT AS D
WHERE       E.DNO = D.DEPTNO
ORDER BY    DEPTNAME, SALARY DESC;

집합 연산자

2개의 SELECT문에서 검색한 COFIATT의 개수와, 데이터 유형이 동일해야 한다.

(_QUARY1_)
{
    UNION(중복행은 한번만), 
    UNION ALL(중복도 그대로 출력), 
    INTERSECT(공통된 행만 출력), 
    EXCEPT(첫번쨰를 남기고 중복 삭제)
}
(_QUARY2_)
ORDER BY COFIATT DESC;

---

7. PROCEDURE & 사용자 정의함수 & 트리거

1. PROCEDURE

필요할떄마다 가져와서 사용 가능한 일련의 SQL 작업 절차형 SQL

① 프로시저 생성

CREATE [OR REPLACE] PROCEDURE _PROCEDURE_NAME_(
    패 IN TYPE, 러 IN TYPE, 미 IN TYPE,
    터 IN TYPE, 들 OUT TYPE..
)
BEGIN (
    DECLARE 
        변수 선언
    SQL;
    EXCEPTION
        WHEN ... THEN 
            SQL
)
END

EXEC _PROCEDURE_NAME_;

② 프로시저 삭제

DROP PROCEDURE _PROCEDURE_NAME_;

2. 사용자 정의 함수

기본적으로 프로시어와 동일하며, 종료시 단일값을 반환 한다.
호출 쿼리 작성하느 법

① 사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION _FUNCTION_NAME_(
    패 IN TYPE, 러 IN TYPE, 미 IN TYPE, 터 IN TYPE
)
RETURN TYPE
BEGIN (
    DECLARE 
        변수 선언
    SQL;
    RETURN 반환값;
)
END

② 사용자 정의 함수 삭제

DROP FUNCTION _FUNCTION_NAME_;

3. TRIGGER

• 데이터 베이스의 갱신과 같은 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는 프로시져
• 데이터 무결성을 유지하기 위해 사용되는 도구다 필수임!~
• IN, OUT 을 통해 실행되는것이 아닌 동작 시기를 지정해서 사용한다.

① 트리거 생성

CREATE
    TRIGGER _TRIGER_NAME_
    [AFTER  {INSERT, DELETE, UPDATE} ON _TABLE_] # 혹은
    [BEFORE {INSERT, DELETE, UPDATE} ON _TABLE_]
[REFERENCING [NEW | OLD] AS _NEW_TABLE_] # 트리거를 수행하기 위한 테이블, 트리거가 실행할떄 필연적으로 테이블이 달라져서..
[FOR EACH ROW]
[WHEN <조건>]
BEGIN (
    <SQL문(들)>
)
END

② 트리거 삭제

DROP TRIGGER _TRIGGER_NAME_

• 이벤트의 가능한예로는
  • 투플 삽입, 투플 삭제, 테이블(테이블(릴레이션)) 투플 수정등이 있음
  • 그리고 삽입 삭제 수정등이 일어난
  • 이펜트 이전(BEFORE) 수행할것인지,
  • 이펜트 이후(AFTER) 수행할것인지 구분한다.

---

9. 제어문

1. IF ELSE

DECLARE
    ...;
BEGIN (
    IF      조건 THEN
        statements1;
    ELSEIF  조건 THEN
        statements2;
    ELSE    조건
        statements3;
    END IF;
)
END;

2. LOOP

DECLARE
    ...;
BEGIN (
    LOOP (
        statements1;
        statements2;
        ...;
        EXIT WHEN _조건_;
    )
    END LOOP;
)
END;

---

참고

1. https://velog.io/@gillog/DB-MySQL-NULL-처리IFNULL-CASE-COALESCE