DB Chapter4 SQL 고급 - SQL 내장 함수(ABS, CEIL...) / 문자 함수(CHR, CONTACT...) / REPLACE 함수 / LENGTH 함수 / 날짜, 시간 함수 / NULL 값 처리 / NVL 함수 / ROWNUM / 부속질의 / IN, NOT IN / ALL, SOME(ANY) / EXISTS, NOT EXISTS

02 Jul 2021 • DataBase

Part2. 데이터베이스 프로그래머

Chapter 04. SQL 고급

SQL 내장 함수

SQL 내장함수는 사용자가 만든 함수에 대비되는 용어로 DBMS에서 제공하는 함수이다. 상수나 열이름을 입력값으로 받아 호출되며, 단일 값을 결과로 반환한다. 모든 내장함수는 최초에 선언 될 때 유효한 입력값을 받아야 한다. 예를 들어 수학 함수의 입력값은 정수 또는 실수여야 한다. SELECT 절과 WHERE 절, UPDATE 절 등에서 모두 사용할 수 있다.

숫자 함수의 종류
함수 설명 예
ABS(숫자) 숫자의 절댓값 계산 ABS(-4.5) = 4.5
CEIL(숫자) 숫자보다 크거나 같은 최대의 정수 CEIL(4.1) = 5
FLOOR(숫자) 숫자보다 작거나 같은 최소의 정수 FLOOR(4.1) = 4
ROUND(숫자, m) m 자리를 기준으로 숫자 반올림 ROUND(5.36, 1) = 5.4
LOG(n, 숫자) 숫자의 자연로그 값 반환 LOG(10) = 2.30259
POWER(숫자, n) 숫자의 n제곱 값 계산 POWER(2, 3) = 8
SQRT(숫자) 숫자의 제곱근 값 계산(숫자는 양수) SQRT(9.1) = 3.0
SING(숫자) 숫자가 음수이면 -1, 0이면 0, 양수이면 1 SING(3.45) = 1

질의 4-2) 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오.

SELECT ROUND(4.875, 1)
FROM dual;

//결과는 4.9

dual은 실제로 존재하는 테이블이 아니라 오라클에서 일시적인 연산 작업에 사용하기 위해 만든 가상의 테이블이다.

1     2     3   .   4   5   6   7 ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ -3   -3   -1   0   1   2   3   4

에서 반올림 한다고 생각!

문자 함수

문자 함수의 종류 : 문자값 반환 함수(s는 문자열, c는 문자, n과 k는 정수)
함수 설명 예
CHR(k) 정수 아스키코드를 문자로 변환 CHR(68) = ‘D’
CONTACT(s1, s2) 두 문자열을 연결 CONTACT(‘마당’, ‘서점’) = ‘마당서점’
INITCAP(s) 문자열의 첫 번째 알파벳을 대문자로 변환 INITCAP(‘the sopa’) = ‘The Sopa’
LOWER(s) 대상 문자열을 모두 소문자로 변환 LOWER(‘MR. SCOTT’) = ‘mr. scott’
LPAD(s, n, c) 대상 문자열의 왼쪽부터 지정한 자릿수까지 지정한 문자로 채움 LPAD(‘PAGE 1’, 10, ‘’) = ‘***PAGE 1’
LTRIM(s1, s2) 대상 문자열의 왼쪽부터 지정한 문자들을 제거 LTRIM(‘<==>BBB<==>’, ‘<>=’) = ‘BBB<==>’
REPLACE(s1, s2, s3) 대상 문자열의 지정한 문자를 원하는 문자로 변경 REPLACE(‘JACK and JUE’, ‘J’, ‘BL’) = ‘BLACK and BLUE’
RPAD    
RTRIM    
SUBSTR(s, n, k) 대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환 SUBSTR(‘ABCDEFG’, 3, 4) =’CDEF’
TRIM(c FOME s)    
UPPER(s) 대상 문자열을 모두 대문자로 변환  
ASCII(c) 대상 알파벳 문자의 아스카코드 값을 반환 ASCII(‘D’) = 68
INSERT(s1, s2, n, k) 문자열 중 n번째 문자부터 시작하여 찾고자 하는 문자열 s2가 k번째 나타나는 문자열 위치 반환, 예제에서 3번째부터 OR가 2번째 나타나는 자릿수 INSERT(‘CORPORATE FLOOR’, ‘OR’, 3, 2) = 14
LENGTH(s) 대상 문자열의 글자 수 를 반환 LENGTH(‘CANDIDE’) = 7
REPLACE 함수

실수로 도서 제목을 잘못 입력한 경우 일일이 변경하지 않고 한꺼번에 변경할 수 있다.

LENGTH 함수

주의할 점은 세는 단위가 바이트가 아니라 문자라는 점이다. 일반적으로 영문자 ‘A’는 1바이트, 한글 ‘가’는 2바이트(유니코드에서는 3바이트)지만 LEN 함수는 영문자나 한글이나 결과를 1로 반환한다. 또한 공백도 하나의 문자로 간주한다. 참고로 LENGTHB 함수를 사용하면 모두 바이트 단위로 반환한다.

질의 4-6) 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.

SELECT SUBSTR(name, 1, 1) "성", COUNT(*) "인원"
FROM Customer
GROUP BY SUBSTR(name, 1, 1);

//결과
//성  인원
//장  1
//김  1
//추  1
//박  2

날짜, 시간 함수

함수 설명 예
TO_DATE(char, datetime) 문자형(CHAR)데이터를 DATE 형으로 반환 TO_DATE(‘2020-09-14’, ‘yyyy-mm-dd’) = 2020-09-14
TO_CHAR(date, datetime) DATE형 데이터를 문자열(CARTCHAR2)로 반환 TO_CHAR(TODATE(‘2020-09-14’, ‘yyyy-mm-dd’), ‘yyyymmdd’) = ‘20200914’
ADD_MONTHS(date, 숫자) 날짜에 지정한 달을 더해 DATE 형으로 반환(1:다음달, -1:이전 달) ADD_MONTHS(TO_DATE(‘2020-09-14’, ‘yyyy-mm-dd’), 12) = 2021-09-14
LAST_DAY(date) 날짜에 달의 마지막 날을 DATE형으로 반환 LAST_DAY(TO_DATE(‘2020-09-14’, ‘yyyy-mm-dd’)) = 2020-09-30

질의 4-7) 마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오.

SELECT orderid "주문번호", orderdate "주문일", orderdate+10 "확정"
FROM Orders;

//결과
//주문번호  주문일        확정
//1       20/07/01     20/07/11
datetime의 주요 인자

p.217 테이블 생략,, 힘듬

NULL 값 처리

아직 지정되지 않은 값을 말한다. 값을 알수도 없고 적용할수도 없다는 뜻이다. NULL은 ‘0’, ‘(빈문자)’, ‘‘(공백) 등과 다른 특별한 값이다. 또한 비교연산자로 비교할 수 없다. NULL값에 연산을 적용하면 결과는 NULL로 반환한다.

NULL 값을 확인하는 방법 - IS NULL, IS NOT NULL

WHERE 에 price=’‘;를 넣으면 안됌. 오류임. WHERE 에 price IS NULL; 이 사용 가능.

NVL 함수

NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력.

SELECT name “이름”, NVL (phone, ‘연락처없음’) “전화번호”

ROWNUM

오라클 내장함수는 아니지만 자주 사용되는 문법이다. 오라클 내부적으로 생성되는 가상 칼럼으로 SQL 조회 결과의 순번을 나타낸다. 자료를 일부분만 확인하여 처리할 때 유용하다.

질의 4-11) 고객목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오.

SELECT ROWNUM "순번", custid, name, phone
FROM Customer
WHERE ROWNUM <=2

//결과
//두줄만 보임..

부속질의

하나의 SQL문 안에 다른 SQL문이 중첩된 형태로 표현되는 질의를 말한다. 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공하는 데 사용한다. 부속질의는 위치와 역할에 따라 SELECT 부속질의(스킬라 부속질의), FROM 부속질의(인라인 뷰), WHERE 부속질의(중첩질의)로 구분된다. 보통 부속질의라고 하면 WHERE 부속질의를 말한다. 부속질의들 간에 구분이 필요할 경우 WHERE 부속질의를 중첩질의라고 부르기도 한다.

  • 스킬라 부속질의 : SELECT 절에서 사용되는 부속질의로 단일행, 단일열의 스칼라값을 반환한다.
  • 인라인 뷰 : FROM 절에서 사용되는 부속질의로 결과를 뷰(view)형태로 반환한다.
  • 중첩질의 : WHERE 절에서 사용되는 부속질의로 주 질의에 사용된 자료 집합의 조건을 서술한다.

P.226~227 필기 보기,,

IN, NOT IN

집합연산자 중 IN은 주 질의의 속성값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할을 한다. IN연산자에서 사용 가능한 부속질의는 결과로 다중 행, 다중 열을 반환 수 있다.

ALL, SOME(ANY)

ALL, SOME(ANY) 연산자는 비교 연산자와 함께 사용된다. ALL은 모든, SOME은 어떠한(최소한 하나라도)라는 의미를 가진다.

질의 4-15) 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

SELECT orderid, saleprice
FORM Orders
WHERE saleprice > ALL (SELECT saleprice
                        FROM Orders
                        WHERE custid='3');

EXISTS, NOT EXISTS

데이터의 존재 여부를 확인한다.

질의 4-16) EXISTS 연산자를 사용하여 ‘대한민국’에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice) "total"
FROM Orders od
WHERE EXISTS (SELECT *
              FROM Customer cs
              WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);

이후부터는 나중에?? CHAPTER 05 데이터베이스 프로그래밍(01. 데이터베이스 프로그래밍 방법, 02. PL/SQL, 03. 데이터베이스 연동 자바 프로그래밍, 04. 데이터베이스 연동 웹 프로그래밍) 건너뜀.

 DataBase  DB

Comments

Related Posts