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

no title

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;

참고

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

저작자표시

'CS > DB' 카테고리의 다른 글

\| 니앙팽이 - DB \| 9 \| SQL - DCL(데이터 제어어) (0)	2023.09.14
\| 니앙팽이 - DB \| 7 \| SQL - DDL(데이터 정의어) (0)	2023.09.14
\| 니앙팽이 - DB \| 6 \| 조인 (내부, 외부) (0)	2023.09.14
\| 니앙팽이 - DB \| 5 \| 관계형 DBMS & 데이터 무결성 (0)	2023.09.08
\| 니앙팽이 - DB \| 4 \| DB 시스템 개요 (0)	2023.09.07

8. DML 데이터 조작어

1. INSERT

2. DELETE

3. UPDATE

4. SELECT-1

1. SELECT 기본 검색

2. WHERE 조건 지정 검색

3. ORDER BY 정렬 검색

4. 서브쿼리

5. SELECT-2

0. 그룹 함수

1. GROUP BY

2. HAVING

6. JOIN & 통합질의

집합 연산자

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

1. PROCEDURE

2. 사용자 정의 함수

3. TRIGGER

9. 제어문

1. IF ELSE

2. LOOP

참고

'CS > DB' 카테고리의 다른 글

티스토리툴바

1. `SELECT` 기본 검색

2. `WHERE` 조건 지정 검색

3. `ORDER BY` 정렬 검색

1. `GROUP BY`

2. `HAVING`