SQL 개발자 (SQLD) 자격증 따기 Part.2 섹션 1.

DML 이어서

12) NULL 값 조회

- NULL의 특징

NULL은 모르는 값을 의미한다

NULL은 값의 부재를 의미한다

NULL은 숫자 혹은 날짜를 더하면 NULL이 된다

NULL은 어떤 값을 비교할 때 '알 수 없음'이 반환된다

- NULL 값 조회

NULL을 조회할 경우 IS NULL를 사용하고 NULL값이 아닌 것을 조회할 경우 IS NOT NULL을 사용

NULL 관련 함수

(1) NVL : NULL이면 다른 값으로 바꾸는 함수 ex) NVL(mgr, 0)이면 mgr 칼럼이 NULL이면 0으로 바꾼다

(2) NVL2 : NVL 함수와 DECODE 함수를 하나로 만든 것 ex) NVL2(mgr, 1, 0) ~ mgr이 NULL이 아니면 1, NULL이면 0을 반환

(3) NULLIF : 두 개의 값이 같으면 NULL을, 같지 않으면 첫 번째 값을 반환한다 ex) NULLIF(exp1, exp2)

(4) COALESCE : NULL이 아닌 최초의 인자값 반환 ex) COALECSE(exp1, exp2 ..)

 

GROUP 연산

1) GROUP BY문

- GROUP BY는 테이블에서 소규모 행을 그룹화해 합계, 평균, 최댓값, 최솟값 등을 계산할 수 있다

- HAVING 구문을 활용한 조건문

- ORDER BY를 사용해 정렬할 수 있다

2) HAVING문

GROUP BY에 조건을 사용하기 위해 필요하고 WHERE절에는 집계 함수를 사용할 수 없지만 HAVING은 가능하다

3) COUNT 함수

COUNT 함수는 행 수를 계산하는 함수, COUNT(*)는 NULL을 포함한 모든 행 수를 계산한다. COUNT(칼럼명)은 NULL 값을 제외한 행 수를 계산한다

 

SELECT문 실행 순서

- SELECT문의 실행 순서는 FROM WHERE, GROUP BY, HAVING, SELECT, ORDER BY순으로 진행된다

 

명시적 형변환과 묵시적 형변환

형변환이라는 것은 두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것, 명시적, 묵시적 형변환으로 나뉜다

 

내장형 함수 : SQL 내장형 함수

(1) 내장형 함수

- 내장형 함수로는 형변환 함수, 문자열 및 숫자형 함수, 날짜형 함수가 있다

(2) DUAL 테이블

DUAL 테이블은 Oracle 데이터베이스에 의해 자동 생성되는 테이블이다

Oracle 데이터베이스의 모든 사용자가 임시로 사용할 수 있는 테이블로 내장형 함수를 실행할 때도 사용할 수 있다

(3) 내장형 함수의 종류

1) ASCII(문자) : ASCII 코드로 변환

2) CHAR(아스키코드) : 문자로 변환

3) SUBSTR(문자열 m, n) : substring

4) CONCAT(문자열 1, 문자열 2) : 1과 2를 결합한다

5) LOWER(문자열) : 소문자 변환

6) UPPER(문자열) : 대문자 변환

7) LENGTH(문자열) or LEN(문자열) : 공백 포함 문자열 길이

8) LTRIM(문자열, 지정문자) : 왼쪽에서 지정 문자를 삭제, 지정 문자가 없다면 공백 삭제

9) RTRIM(문자열, 지정문자) : 오른쪽

10) TRIM(문자열, 지정문자) : 양쪽

11) SYSDATE : 오늘 날짜

12) EXTRACT : 연도만

13) TOCHAR : 숫자나 날짜를 문자열로 변환

내장형 함수 : 숫자형

ABS - 절댓값

SIGN - 음수, 양수, 0을 구분

MOD - 나머지 계산

CEIL or CEILING - 최소 정수

FLOOR - 최대 정수

ROUND(숫자, m) - 소수점 m 자리에서 반올림

TRUNC(숫자, m) - 소수점 m 자리이하 숫자 버림 (m 기본값 0)

 

DECODE와 CASE문

1) DECODE

DECODE문으로 IF문을 구현할 수 있다. ex) DECODE(조건식, 참일 경우 반환값, 거짓일 경우 반환값)

2) CASE

CASE문은 IF ~ THEN, ~ELSE-END처럼 사용할 수 있다.

CASE[조건문] WHEN 조건 1 THEN 결과 1 .... ELSE 결과 END

 

ROWNUM과 ROWID

1) ROWNUM

- ROWNUM은 오라클 데이터베이스의 select문 결과에 대해 논리적인 일련번호를 부여

- ROWNUM은 조회되는 행 수를 제한할 때 많이 사용

- ROWNUM은 화면에 데이터를 출력할 때 부여되는 논리적 순번이다. 만약 ROWNUM을 사용해 페이지 단위 출력을 하기 위해서는 인라인 뷰를 사용해야 한다.

- 인라인 뷰는 select문에서 from절이 사용되는 서브쿼리를 의미

- ROWNUM의 값이 순차적으로 증가하는 ROWNUM 데이터를 얻고 싶을 때 인라인 뷰를 사용한다

 

* TOP(SQL Server), LIMIT(MYSQL), ROWNUM(Oracle)은 같은 의미! ~ 객관식 출제 가능

2) ROWID

- ROWID는 오라클 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값

- ROWID는 "SELECT ROWID, EMPNO FROM EMP와 같은 SELECT문으로 확인할 수 있다

- ROWID를 통해 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있다

 

WITH

- WITH 구문은 서브쿼리를 사용해 임시 테이블이나 뷰처럼 사용할 수 있는 구문

- 서브쿼리 블록에 별칭 지정 가능

- 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단

 

UNION : 2개의 테이블의 데이터를 합해서 출력하는데 중복값 제외

UNION ALL : 중복값도 모두 출력

 

DCL

1) Grant

Grant문은 데이터베이스 사용자에게 권한을 부여

데이터베이스 사용을 위해 권한이 필요하며 연결, 입력, 수정, 삭제, 조회를 할 수 있다

WITH GRANT OPTION : 특정 사용자에게 권한을 부여할 수 있는 권한을 부여

(준 관리자 부여, A -> B -> C 이후 B를 취소하면 B와 C의 권한이 모두 회수)

WITH ADMIN OPTION : 테이블에 관한 모든 권한 부여(A -> B -> C 이후 B를 취소하면 B의 권한만 회수)

2) REVOKE

REVOKE문은 데이터베이스 사용자에게 부여된 권한을 회수

REVOKE privileges ON object FROM user;

 

TCL

1) COMMIT

COMMIT은 DML로 변경한 데이터를 물리적인 데이터베이스에 반영한다

- 변경 이전 데이터는 삭제된다. 다른 모든 데이터베이스 사용자는 변경된 데이터를 볼 수 있다

- COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제된다

- COMMIT이 완료되면 다른 모든 DB 사용자는 변경된 데이터에 조작할 수 있다. COMMIT이 완료되면 하나의 트랜잭션이 종료된다

2) ROLLBACK

ROLLBACK를 실행하면 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료한다

- 이전 COMMIT 지점까지 복구한다

ROLLBACK을 실행하면 LOCK이 해제되고 다른 사용자도 데이터베이스 데이터를 조작할 수 있다

3) SAVEPOINT : 게임에서 중간 저장의 개념

- SAVEPOINT는 트랜잭션을 작게 분할해 관리하는 것으로 SAVEPOINT를 사용하면 지정된 위치 이후 트랜잭션만 ROLLBACK할 수 있다

- 지정은 SAVEPOINT (세이브포인트 이름)

- 지정된 SAVEPOINT까지만 데이터 변경을 취소하고 싶은 경우 "ROLLBACK TO (세이브포인트 이름)"을 실행

- ROLLBACK을 실행할 경우 SAVEPOINT와 관계없이 모든 변경사항을 저장하지 않는다