[DB (2)] DML 명령어, 함수 (Day 54)

코딩기록·2024년 12월 24일

DML INSERT WHERE between db delete in select sql update 국비지원교육

[ DML 명령어 ]

- INSERT INTO 테이블

구조

INSERT INTO GOODS
-- ! 입력하지 않을 경우 : NULL로 지정됨
-- column 순서는 아무렇게나 입력 가능
    (id, goods_name, price, created_at)
VALUES
    (1, '선풍기', 120000, SYSDATE);

FYI. 현재시간 입력 : SYSDATE

- UPDATE 테이블

구조

UPDATE 테이블명
SET 바꿀필드명 = 바뀔값
-- WHERE 조건문 없으면 모든 행에 적용됨
(WHERE 조건)

UPDATE TBL_STUDENT
SET gender_eng = 'FEMALE', gender_kor = '여성'
WHERE gender_eng = 'F';




## DELETE FROM 테이블
- 구조 
  ```sql
  -- 여기까지만 쓰면 표 내용 전체 삭제! 
  -- 단, TRUNCATE TABLE 테이블명과 달리, ROLLBACK 가능;
  DELETE FROM 테이블

- SELECT * FROM 테이블명

구조

-- DISTINCT : 중복값 제거
SELECT (DISTINCT)
     -- AS "칼럼명" : 화면에서 보이는 COLUMN 명 변경 (원본 데이터는 그대로임)
     -- 데이터 결합에서 한 열로 보이게 할 때 : ||
 	 employee_name || '의 사번은' || employment_no || '입니다' AS "직원 사번"

FROM 테이블명

- WHERE 절 (조건) : 개별 행에 대한 필터링(group by 전에 사용)

FYI. 우선 순위 : NOT > AND > OR

0. 값이 없을 때 : IS (NOT) NULL

SELECT * FROM tbl_student
WHERE department IS (NOT) NULL;

1. 특정 조건 제외 :

정확한 조건일 때 : != 혹은 <>

포함여부로 : NOT LIKE + %/_

-- 정확한 조건일 때 
SELECT * FROM tbl_student
WHERE gender != 'FEMALE' // '!=' : gender 칼럼 값이 female이 아닐 때
	AND WHERE nationality <> 'KOREAN' // '<>' : nationality가 korean이 아닐 때
    AND WHERE name NOT LIKE '김%' // NOT LIKE '%' : 성씨가 김씨가 아닐 때
    AND WHERE name NOT LIKE '이_' // NOT LIKE '_' : 이0 이 아닐 때 (이00은 제외되지 않음)
<br>

2. 숫자 범위 : (not) between

SELECT * FROM tbl_employee
-- WHERE 열 이름 (NOT) BETWEEN 시작값 and 끝값
WHERE age (NOT) BETWEEN 40 and 50;

3. 'Or' 혹은 '(NOT) IN()'

in()은 between, like 등과 결합하여 사용 불가

SELECT * FROM tbl_employee
-- '필드명 in () : 나이가 33 혹은 36
WHERE age in (33, 36) 
;

[ 함수 ]

1. Char vs Char 비교

Char(4) 'ABC' vs Char(6) 'ABC ' : 동일(길이가 서로 다르면 작은 쪽에 공백을 추가하여 길이를 같게 함)

2. Char vs VARCHAR

Char(4) 'ABC' vs VARCHAR(6) 'ABC' : 동일
Char(4) 'ABC' vs VARCHAR(6) 'ABC ' : 다름 (VARCHAR인데 공백을 뒀으면 이유가 있을 거임)
WHERE CHAR(4) 'SQLD' = 'SQLD ' : true
WHERE VARCHAR(6) 'SQLD ' = 'SQLD' : false

3. SUBSTR(string, start_position, length) : 추출

START POSITION : 1부터 시작
Length 포함하여 추출됨

4. ASCII('A') : 문자를 아스키코드로

5. CHR(97) : 아스키코드를 문자로

6. CONCAT('A', 'B') : 결합 (출력값 : AB)

7. LENGTH('ABC') : 길이 ( 출력값 : 3)

8. TRIM

TRIM(' HI ') -- 좌우 공백 제거
LTRIM(' HELLO ') -- 좌 공백 제거
RTRIM(' HELLO ') -- 우 공백 제거
LTRIM('HHHELLOhello', 'H') -- 왼쪽 H 3개 제거

9. PAD

RPAD('Steve', 2, '-'), -- 오른쪽에 주어진 문자를 채움 (출력값 : Steve--)
LPAD('Steve', 2, '*'), -- 왼쪽에 주어진 문자를 채움(출력 : **Steve)

10. REPLACE

REPLACE('Java Programmer Java', 'Java', 'BigData') AS "REPLACE"
-- Java를 BigData로 변경
REPLACE('Java Programmer', 'Java') AS "REPLACE" : java를 제거

11. 숫자 관련 함수

- MOD(27, 5) AS MOD, -- 나머지 값 반환

- CEIL(38.678) AS ceil, -- 올림값 반환

- FLOOR(38.678) AS floor, -- 내림값 반환

- ROUND(38.678, 2) AS round, -- 자리수까지 반올림

- TRUNC(38.678, 2) AS trunc -- 자리수 이하를 절삭

- ABS(-20) AS abs -- 절대값

- SIGN(99) AS sign -- 0보다 작으면 -1, 0보다 크면 1, 0이면 0

12. 날짜 관련 함수

- 현재 날짜 : SYSTIMESTAMP, SYSDATE

- 날짜 + 숫자 = 날짜 => 일(DAY) 수를 날짜에 더함

- 날짜 - 숫자 = 날짜 => 날짜에서 일 수를 뺌

- 날짜 - 날짜 = 일수 => 어떤 날짜에서 다른 날짜를 뺀 일수

- 날짜 + 숫자/24 = 날짜 => 날짜에 시간을 더함

- 날짜 + 숫자/24/60 => 날짜에 분을 더함

13. 날짜를 문자형식으로 변환 : TO_CHAR

TO_CHAR(SYSDATE, 'MM-DD') AS "01월 - 01일"
TO_CHAR(SYSDATE, 'FMMM-FMDD') AS "1월 - 1일"
TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS "년/월/일"
TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS') AS "시간 24시간 단위 표기",
TO_CHAR(SYSDATE, 'YY/MM/DD AM HH12:MI:SS') AS "오후 04:29:55"

14. 문자를 날짜로 변환 : TO_DATE

TO_DATE('20150101000000', 'YYYYMMDDHH24MISS'),
TO_DATE('20240101', 'YYYYMMDD'),
TO_DATE('202401','YYYYMM'),
TO_CHAR(SYSDATE, 'YYYYMM')

15. 숫자를 화폐 형식으로 변환 : TO_CHAR

TO_CHAR(9512 * 1.33, '$999,999.99') AS "달러",
TO_CHAR(1350000, 'L999,999,999') AS "원화",

16. 조건에 따라 새로운 값 부여 :

1) CASE WHEN 조건 THEN 보여줄 값 ELSE 보여줄값 END 칼럼명 부여

CASE WHEN sal_cd = '100001' THEN '기본급여'
     WHEN sal_cd = '100002' THEN '보너스급여'
     ELSE '기타'
     END sal_name,

2) CASE 조건 걸릴 칼럼 WHEN 조건 THEN 보여줄 값 ELSE 보여줄값 END 칼럼명 부여

  CASE sal_cd 
  	    WHEN '100001' THEN '기본급여'
        WHEN '100002' THEN '보너스급여'
        ELSE '기타'
   END sal_name

3) DECODE(조건 걸 칼럼명, 첫번째 조건, 첫번째 조건 보여줄 값, 기타 시 보여줄 값) AS 칼럼명

    DECODE(sal_cd, '100001', '기본급여', '100002', '보너스급여', '기타') AS sal_name

17. COALESCE : 첫번째 NULL이 아닌 값 반환

-- 각 직원의 phone_number, mobile_number, email 중 첫 번째로 NULL이 아닌 값을 contact_info로 반환합니다.
SELECT employee_id,
     COALESCE(phone_number, mobile_number, email) AS contact_info
FROM employees;

18. null 관련

- NULL 값을 포함한 연산 공식

NULL과의 연산 : 결과값 NULL

-- 표가 아래와 같을 때, SELECT a + b 하면 각행 값은 15,null, 45


a   |  b   | 
-----|------|
10  |  5   | 
20  | NULL |
30  |  15  |

- 집계함수(SUM, AVERAGE 등)에서 값이 NULL인 칼럼이 있을 때 : NULL 무시
```sql
-- 테이블이 아래와 같을 때, SUM(column1)하면 30이 출력됨 ------
column1
10
NULL
20

SELECT SUM(column1) FROM table;

- NULLIF(column1, column2) AS result FROM table_name;

: 두 개의 표현식을 비교하여 같으면 NULL을, 다르면 첫 번째 표현식을 반환.

-- result 칼럼에 값을 salary가 null이면 0으로, salary가 null이 아니면 기존 salary로 반환
SELECT NULLIF(salary, 0) AS result
FROM employees;

- NVL(null인지 확인할 열, null이면 표시할 값) AS 칼럼명 : Null Value Logic

-- DIRECT_MANAGER_EMP_NO 이 NULL이면 관리자 행의 VALUE를 '최상위 관리자'로 표시
SELECT 
  emp_no
  , emp_nm
  , NVL(direct_manager_emp_no, '최상위관리자') AS 관리자
FROM tb_emp;

- NVL2(NULL인지 확인할 열, NULL이 아니면 표시할 값, NULL이면 표시할 값)

-- direct_manager_emp_no이 null이면 직위 행을 회장님으로, 아니면 일반사원으로
SELECT 
  emp_nm,
  NVL2(direct_manager_emp_no, '일반사원', '회장님') AS 직위
FROM tb_emp;

19. 집계 함수 : NULL은 무시됨!

- COUNT(*) "지급 횟수",

- SUM(pay_amt) "지급 총액",

- AVG(pay_amt) "평균 지급액",

- MIN(pay_amt) "최대 지급액",

- MAX(pay_amt) "최소 지급액"

GROUP BY 칼럼명

SELECT
 emp_no,
 TO_CHAR(ROUND(AVG(pay_amt), 2), 'L999,999,999') "사원별 급여평균",
 TO_CHAR(SUM(pay_amt), 'L999,999,999.99') "사원별 총 급여 수령액"
FROM tb_sal_his
GROUP BY emp_no
ORDER BY emp_no
;

코딩기록

이전 포스트

[Spring] Http 요청 처리 - Controller, Service, Repository,(Day 53)

다음 포스트