23-01-31 (1) 데이터베이스 (NULL, SQL연산자, 날짜함수)

 

복습

비교연산

논리연산 (AND, OR, NOT)

SQL전용 연산자 

 

 

1. NULL

데이터베이스의 NULL은 미확인값이나 아직 적용되지 않은 값을 의미

0도 아니며 스페이스도 아닌 정의되지 않은 값

NULL값과 다른값과의 연산결과는 NULL

  예) NULL+1 = NULL. 

NULL은 단독으로 인덱스를 사용할수 없으므로 가능한 정의가 가능한값(특정값)으로 정한다면 (0,99 등) 인덱스를 이용할 수 있다.

NULL의 대상은 칼럼이다.

 

정의

컬럼에 아무런 값이 없을 경우, 컬럼은 NULL상태 라고 이야기를 함, NULL을 포함했다고 정의함

모든 데이터타입에 NULL이 들어갈 수 있음

 

참조

오라클 데이터베이스는 현재 길이가 '0'인 값에 대하여는 문자형 값은 NULL로 취급함

 

SQL함수 상에서의 NULL

모든 스칼라함수(일반적인 함수 / REPLACE, NVL, CONCAT는 제외)들은 NULL변수를 입력 받았을경우 NULL값 리턴이 가능함.

대부분의 집계(AGGREGATE)함수는 NULL무시하고 수행됨

 - 집계함수는 행이 줄어들 수 있다.

1000, NULL, NULL, NULL, 2000에 대한 AVG는 1500으로 리턴됨

 

NULL값을 테스트 하기 위한 비교방법은 IS NULL, IS NOT NULL이다.

만약 다른 조건으로 NULL을 함께 사용할경우엔 결과는 UNKNOWN으로 리턴됨.

NULL은 데이터의 부족함을 나타내고, 즉 NULL은 같음('=') 또는 같지않음('≠')을 다른 값과 비교하거나 다른 NULL과 비교할 수 없다.

 

조건상의 NULL

UNKNOWN 형태의 평가는 대부분 FALSE와 동일함

WHERE절의 평가가 UNKNOWN으로 리턴 될 경우 SELECT절에는 아무런 ROW가 출력되지 않음

 

예제

--교수 테이블에서 보직수당이 없는 교수의 이름, 직급, 보직수당을 출력하여라

SELECT NAME, POSITION, COMM
FROM PROFESSOR
WHERE COMM IS NULL;

3행에서 IS NULL을 IS NOT NULL으로 입력하면 보직수당을 받는 교수의 이름, 직급, 보직수당이 나온다.

 

산술식에서 NULL처리

 

 

 

 

--교수테이블에서 급여에 보직수당을 더한 값은 SAL_COM이라는 별명으로 출력

SELECT NAME, SAL, COMM, SAL+COMM SAL_COM
FROM PROFESSOR;

 

 

 

2. 연산자 우선순위

산술 > 비교 > 논리 > SQL > 결합 > 집합

 

사용 예

-- 102번 학과의 학생 중에서 1학년 또는 4학년 학생의 이름, 학년, 학과번호를 출

SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE DEPTNO = 102 AND (GRADE = 1 OR GRADE = 4);

 

 

3. 집합연산자

테이블을 구성하는 행집합에 대해서 구분집을 결과로 반환하는 연산자

합병가능 : 칼럼수가 같고, 대응되는 칼럼끼리 데이터타입이 동일해야함

명령문(SELECT)와 명령문 사이에 사용함

 

집합연산자 종류

1) UNION 연산은 수학의 집합 연산에서 합집합을 의미, 두개의 테이블에 속하면서 중복되는 행을 제외한 행 집합을 결과집합으로 반환

2) UNION ALL 연산은 중복되는 행을 제외하지 않고 모두 출력

 

3) MINUS 차집합을 의미, 테이블A, B에서 A테이블에 속하지만 B테이블에는 속하지 않는 행 집합을 결과집합으로 반환

4) INTERSECT 연산은 교집합, 두개의 테이블에 모두 속하는 행 집합을 결과집합으로 반환

 

 

 

 집합 연산을 위한 테이블 생성

1학년이면서 몸무게가 70kg 이상인 학생의집합(stud_heavy)과

1학년이면서 101번학과에 소속된학생(stud_101)으로 구성된 두개의 테이블생성

 

CREATE TABLE STUD_HEAVY
AS SELECT *
FROM STUDENT
WHERE WEIGHT >= 70 AND GRADE = '1';

CREATE TABLE STUD_101
AS SELECT *
FROM STUDENT
WHERE DEPTNO = 101 AND GRADE = '1';

SELECT * FROM STUD_HEAVY;

SELECT * FROM STUD_101;

STUD_HEAVY

STUD_101

SELECT * FROM STUD_HEAVY
UNION ALL
SELECT * FROM STUD_101;

2행에 집합연산자를 사용하면 된다.

 

4. 정렬

SQL명령문에서 검색된 결과는 테이블에 데이터가 입력된 순서대로 출력

하지만, 데이터의 출력 순서를 특정 컬럼을 기준으로 오름차순 또는 내림차순으로 정렬하는 경우가 자주 발생

여러개의 칼럼에 대해 정렬 순서를 정하는 경우도 발생

 

4-1 기본적인 정렬 방법

문자 값은 알파벳순으로 출력되고, 한글은 가나다 순으로 출력된다

숫자값은 가장 작은 값으로 먼저 출력된다.

날자값은 과거의 날짜순으로 출력된다.

 

4-2 사용법

ORDER BY 칼럼이나 표현식을 기준으로 출력결과를 정렬할 때 사용 (언급된 칼럼에 가상의 번호를 부여하여 사용가능)

ASC 오름차순으로 정렬, 기본 값 생략가능

DESC 내림차순으로 정렬, 생략불가

SELECT * FROM STUDENT ORDER BY STUDNO DESC;

질의 결과

 

 

4-3 ORDER BY절 사용시 정렬 기준의 원칙

 - 디폴트는 오름차순 정렬이 원칙

 - NUMBER TYPE은 가장 적은 값 부터 출력됨

 - 날짜 타입은 가장 빠른값 부터 출력됨

 - 문자 값은 알파벳 순서로 출력됨

 - NULL값은 오름차순에서는 제일 나중에 내림차순에는 제일 먼저 출력

 - SELECT절에 포함되지 않는 칼럼도 정렬하기위한 ORDER BY절에 올수 있음

 

5. 다중열 정렬

 

SELECT ENAME, JOB, DEPTNO, SAL

FROM EMP

ORDER BY DEPTNO, SAL DESC;

 

먼저 언급됐던 deptno가 먼저 오름차순으로 정렬되고 sal이 내림차순이 된다.

순서를 뒤바꿀수 없다.

 

189p 실습예제 복습필요

 

6. SQL함수의 개념

 

6-1. SQL함수

컬럼의 값이나 데이터 타입의 변경하는경우

숫자 또는 날짜 데이터의 출력 형식을 변경하는 경우

하나이상의 행에 대한 집계를 하는경우

 

6-2. SQL함수의 유형

단일 행 함수(스칼라 함수) : 테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수

데이터값을 변경하는데 주로 사용, 행별로 함수를 적용하여 하나의 결과를 반환

단일행 함수의 사용법

FUNCTION_NAME(COLUMN ㅣ EXPRESSION, [ARG1, ARG2, ... ])

 

 

 

복수 행 함수 : 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수

 

6-3 문자함수

문자데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수

 

※ 종류

1) 대소문자 변환 함수

INITCAP 문자열의 첫번째 문자만 대문자로 변환

LOWER 문자열 전체를 소문자로 변환

UPPER 문자열 전체를 대문자로 변환

SELECT userid, LOWER(USERID), UPPER(USERID)
FROM STUDENT
WHERE STUDNO = 20101;

 

2) 문자열 길이 반환 함수

 LENGTH 문자열의 길이를 반환

 LENGTHB 문자의 바이트수를 반환

SELECT LENGTH('홍길동'), LENGTHB('홍길동') FROM DUAL;

 

 

부서 테이블에서 부서 이름의 길이를 문자 수와 바이트 수로 각각 출력

SELECT DNAME, LENGTH(DNAME), LENGTHB(DNAME)
FROM DEPARTMENT;

3)문자조작 함수

CONCAT 두 문자열을 결합, '||' 와 동일 (CONCAT('sql', 'plus;) -> sqlplus)

SUBSTR 특정 문자 또는 문자열 일부를 추출 (SUBSTR('SQL*PLUS',5,4) -> PLUS)

INSTR 특정 문자가 출현하는 첫번째 위치를 반환 (숫자타입 반환)(INSTR('SQL*PLUS','*') - > 4

LPAD 왼쪽에다가 지정문자를 덧댐 (LPAD('SQL', 5, '*') -> **SQL)

RPAD 왼쪽으로 정렬 후 오른쪽으로 지정문자를 삽입 (RPAD('SQL',5,'*') -> SQL**)

LTRIM 왼쪽 지정 문자 삭제 (LTRIM('*SQL', '*') -> SQL

RTRIM 오른쪽 지정 문자 삭제 (RTRIM('SQL*', '*') -> SQL

 

4) SUBSTR 함수

문자열에서 M번째 문자부터 N개의 문자 추출

M이 음수이면 시작하는 위치는 문자열의 마지막

N 생략시 M부터 마지막문자까지 추출

SELECT SUBSTR('ABCDE', 0, 3) FROM DUAL;

4-1) SUBSTRB

SUBSTR 글자기준

SUBSTRB 바이트기준

예제

학생테이블에서 1학년 학생의 주민등록번호에서 생년월일과 태어난 달을 추출하여 이름, 주민번호, 생년월일, 태어난달을 추출

SELECT NAME, IDNUM, SUBSTR(IDNUM,1,6), SUBSTR(IDNUM, 3,2) 
FROM STUDENT
WHERE GRADE = 1;

 

5) INSTR (JAVA의 INDEXOF랑 비슷함)

문자열중에서 사용자가 지정한 특정 문자가 포함된 위치를 반환하는 함수

N번째부터 M번째 CHAR의 위치를 찾음

만약 N이 음수면 CHAR의 뒤부터 찾음

 

사용법

SELECT INSTR('ABCDABCDABCD', 'BC', -1) FROM DUAL;

SELECT INSTR('ABCDABCDABCD', 'BC', -1, 2) FROM DUAL;

 

부서테이블의 부서이름 칼럼에서 '과'글자의 위치를 출력

SELECT DNAME, INSTR(DNAME, '과')
FROM DEPARTMENT;

 

6) LPAD, RPAD

문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입하는 함수

 

사용 예

 

교수테이블에서 직급칼럼의 왼쪽에 *문자 삽입후 10바이트 출력 교수아이디 칼럼은 오른쪽에 +문자 삽입후 12바이트로 출력

SELECT POSITION, LPAD(POSITION, 10, '*'), 
USERID, RPAD(USERID, 12, '+')
FROM PROFESSOR;

 

7) LTRIM, RTRIM (PAD와 반대)

문자열에서 특정문자를 삭제하기 위해 사용

함수의 인수에서 삭제할 문자를 지절하지 않으면 문자열의 앞뒤 부분에 있는 공백문자를 삭제

더이상 일치하는게 없을때까지 제거

 

사용 예

부서테이블에서 부서이름의 마지막 글자인 '과'를 삭제하여 출력

SELECT DNAME, RTRIM(DNAME, '과')
FROM DEPARTMENT;

8) 그 외

SELECT TRIM('     가나다라     **') FROM DUAL;
--왼쪽 공백만 제거

SELECT REPLACE('ABCDEABCDE', 'A', 'F') FROM DUAL;
-- A를 F로 변경

 

7. 숫자함수

 - 숫자 데이터를 처리하기 위한 함수

ROUND, TRUNC(절삭, 버림)가 자주쓰임

MOD는 정수만 사용가능

 

1) ROUND

 

사용 예

교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 반올림 한 값과 소숫점 왼쪽 첫째자리에서 반올림 한 값을 출력

 

SELECT NAME, SAL, SAL/22 , ROUND(SAL/22), ROUND(SAL/22, 2), ROUND(SAL/22, -1)
FROM PROFESSOR;

 

2) TRUNC

지정한 소수점 자리수 이하를 절삭한 결과 값을 반환하는 함수

 

3) MOD함수

나누기 연산후에 나머지를 출력하는 함수

 

사용 예

교수테이블에서 101번학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력

SELECT NAME, SAL, COMM, MOD(SAL, COMM)
FROM PROFESSOR
WHERE DEPTNO = 101;

 

3) CEIL, FLOOR

 

 

8. 날짜 함수

날짜함수는 날짜 데이터 타입에 사용하는 함수

 

1) 날짜 계산

 - 날짜 계산은 날짜 데이터에 더하기, 빼기의 연산을 하는 기능

하루를 1으로 두고 시간은 1/24로 계산하면 시간을 계산할 수 있다.

 

사용 예

교수번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력

SELECT NAME, HIREDATE+30, HIREDATE+60
FROM PROFESSOR
WHERE PROFNO = 9908;