:: 작업장

 종류

내용 

함수의 예 

 문자형 함수

문자를 입력하면 문자나 숫자 값을 반환한다. 

LOWER, UPPER, 

SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII 

숫자형 함수

 숫자를 입력하면 숫자 값을 반환한다

ABS, MOD, ROUND, SIGN, 

CHR/CHAR, CEIL/CEILING,

FLOOR, EXP,LOG, LN, POWER, SIN, COS, TAN 

날짜형 함수

DATE 타입의 값을 연산한다. 

SYSDATE/GENDATE,

EXTRACT/DATEPART, 

TO_NUMBER(TOCHAR(d.'yyyy' | 'MM'|'DD')) /

YEAR | MONTH | DAY

변환형 함수

문자, 숫자, 날짜형 값의 데이터 타입을 변환한다. 

TO_NUMBER, TO_CHAR,

TO_DATE / CAST,

CONVERT 

NULL 관련 함수

NULL을 처리하기 위한 함수  

NVL/ISNULL,

NULLIF,

COALESCE 

문자형 함수

함수 설명 

LOWER(문자형)

문자열의 알파벳 문자를 소문자로 바꿔준다. 

UPPER(문자형)

문자열의 알파벳 문자를 대문자로 바꿔준다. 

ASCII(문자)

문자나 숫자를 ASCII코드 번호로 바꿔준다. 

CHR/CHAR(ASCII 번호)

ASCII 코드 번호를 문자로 바꿔준다. 

 CONCAT(문자열1, 문자열2)

문자열1과 문자열2를 연결한다

- 오라클의 ||

- SQL server의 + 와 같은 역할 

 SUBSTR/ SUBSTRING

(문자열, m [,n])

문자열 중 m위치에서 n 개의 문자길이에 해당하는 문자를 돌려준다.(n이 생략될 경우 마지막 문자까지) 

 LENGTH /LEN(문자열)

문자열의 개수를 숫자값으로 돌려준다. 

 LTRIM

(문자열 [, 지정문자])

문자열의 첫 문자부터 확인해서 지정문자가 나타나면 해당 문자를 제거한다.

SQL server경우 LTRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. 

 RTRIM

(문자열 [,지정문자])

문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다.

SQL server경우 RTRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. 

TRIM

([leading | trailing | both 지정문자 FROM 문자열)

문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자를 제거한다.

(leading | trailing | both가 생략되면 both가 디폴트)

SQL server경우 TRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다.  

 LOWER('SQL Expert')

sql expert 

 UPPER('SQL Expert')

SQL EXPERT 

 ASCII('A')

65 

 CHR(65) / CHAR(65)

A 

CONCAT('RDBMS','SQL')

'RDBMS' || 'SQL' / 
'RDBMS' +' SQL'

RDBMSSQL 

SUBSTR('SQL Expert',5,3)

/ SUBSTRING('SQL Expert',5,3)

 Exp

SUBSTR('SQL Expert',5)

 Expert ( 맨끝까지 출력)

LENGTH('SQL Expert') / 

LEN('SQL Expert')

 10 (공백도 길이로 간주)

LTRIM('xxxYYYZZxYZ','x')

 YYYZZxYZ 

(왼쪽부터 x를 지우며 x와 다른값을 만나는 이후 문자열에 대해서는 전체다 출력.)

RTRIM('XXYYzzXYzz','z')

XXYYzzXY

(오른쪽부터 x를 지우며 x와 다른값을 만나는 이후 문자열에 대해서는 전체다 출력.)

RTRIM('XXYYzzXYzz      ')

XXYYzzXYzz

* 공백 제거 결과출력

공백제거 및 CHAR와 VARCHAR 데이터 유형을 비교할 때 용이하게 사용됨.

 TRIM('x' FROM 'xxYYZZxYZxx')

YYZZxYZ 

TRIM(leading 'x' FROM 'xxYYZZxYZxx')

YYZZxYZxx

(LTRIM과 같은결과)

TRIM(trailing 'x' FROM 'xxYYZZxYZxx')

xxYYZZxYZ

(RTRIM과 같은결과)

 숫자형 함수

함수설명 

 ABS(숫자)

숫자의 절대값 리턴 

 SIGN(숫자)

숫자가 양수인지 음수인지 0 인지 구분 

 MOD(숫자1,숫자2)

숫자1을 숫자2로 나눈 값의 나머지 리턴

% 연선자로 대체가능 (7%3) 

 CEIL/CEILING(숫자)

숫자보다 크거나 같은 최소 정수를 리턴한다. 

 FLOOR(숫자)

숫자보다 작거나 같은 최대 정수를 리턴한다. 

 ROUND(숫자 [, m])

숫자를 소수점 m+1자리에서 반올림하여 리턴.

m이 생략되면 디폴트 값은 0 

 TRUNCATE(숫자 [,m])

숫자를 소수 m+1 자리에서 버린 후 리턴

m 이 생략되면 디폴트 값은 0

SQL server에서는 제공하지 않는다. 

 SIN, COS, TAN

삼각함수 값을 리턴 

EXP()

POWER()

SQRT()

LOG()

LN()

숫자의 지수 리턴

거듭제곱 리턴

제곱근 리턴

자연로그값 리턴 

 숫자형 함수 사용

결과 값 및 설명 

 ABS(-15)

15 

 SIGN(-20)

-1 

 SIGN(0)

0 

 SIGN(20)

1 

 MOD(7/3)

1 

 7%3

1 

 CEIL(38.123) / CEILING(38.123)

39  

 CEIL(38) / CEILING(38)

38  (같으면 같은값) 

 CEIL(-38.123) / CEILING(- 38.123)

-38 ( -38이 -38.123 보다 크므로)

 FLOOR(38,123)

38 

 FLOOR(-38.123)

-39 (-39가 -38.123 보다 작으므로) 

 FLOOR(-38) 

-38 (같으면 같은값)

 ROUND(38.5235,3)

38.524 ( 3째 자리까지 표현 4째자리 반올림) 

 ROUND(38.5235,1)

38.5 (소수 첫재짜리 까지 표현 둘째자리 반올림)

 ROUND(38.5235,0)

39 

 ROUND(38.5235)

39 (인수가 없을 경우 0이 DEFAULT) 

 TRUNC(38.5235,3)

38.523 

 TRUNC(38.5235,1)

38.5 

 TRUNC(38.5235,0)

38 

 TRUNC(38.5235)

38(인수가 없을 경우 0이 DEFAULT) 

 날짜형 함수

함수 설명 

 SYSDATE / GETDATE()

 현재 날짜와 시각을 출력한다.

 EXTRACT('YEAR' | 'MONTH' | DAY' from d)

/ DATEPART('YEAR' | 'MONTH' | DAY ,d)

날짜 데이터에 년/월/일 데이터를 출력할 수 있다.

시간 / 분 /초도 가능함 

 TO_NUMBER(TO_CHAR(d,'YYYY')

/ YEAR(d),

 TO_NUMBER(TO_CHAR(d,'MM')

/ MONTH(d),

 TO_NUMBER(TO_CHAR(d,'DD')

/ DAY(d)

날짜 데이터에서 년/월/일 데이터를 출력할 수 있다.

ORACLE EXTRACT YEAR/MONTH/DAY옵션이나

SQL SERVER DEPART YEAR/MONTH/DAY 옵션과 같은 기능이다.

TO_NUMBER 함수 제외시 문자로 출력. 

 연산

결과 

설명 

 날짜 + 숫자

날짜 

숫자만큼의 날수를 날짜에 더한다 

 날짜 - 숫자 

날짜 

숫자만큼의 날수를 날짜에 뺀다 

 날짜1 - 날짜2 

날짜 수 

다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다 

 날짜 + 숫자/24

날짜 

시간을 날짜에 더한다 

 종류

 설명

 명시적 데이터 유형 변환

데이터 변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우 

 암시적 데이터 유형 변환

데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우 

 변환형 함수- ORACLE

함수설명 

 TO_NUMBER(문자열)

alhanumeric 문자열을 숫자로 변환한다. 

 TO_CHAR(숫자 | 날짜 [, FORMAT])

숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환한다 

 TO_DATE(문자열 [, FORMAT])

문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다. 

변환형 함수 -  SQL server 

함수 설명 

 CAST (expression AS data_type [(length)])

expression을 목표 데이터 유형으로 변환한다. 

 CONVERT (data_type [(length)], expression [,style])

expression을 목표 데이터 유형으로 변환한다. 

 SCOTT@PROD>select sysdate from dual;

SYSDATE

---------

28-AUG-15

SCOTT@PROD>select TO_CHAR(SYSDATE,'YYYY/MM/DD')

  2  FROM DUAL;

TO_CHAR(SY

----------

2015/08/28

SCOTT@PROD>SELECT TO_CHAR(SYSDATE,'YYYY,MON,DAY') 

  2  FROM DUAL;

TO_CHAR(SYSDATE,'YYYY,MON,DAY')

------------------------------------------------------

2015,AUG,FRIDAY

SCOTT@PROD>SELECT TO_CHAR(SYSDATE,'YYYY.MM.DD') FROM DUAL;

TO_CHAR(SY

----------

2015.08.28

SELECT CONVERT(VARCHAR(10)M GETDATE(),11) AS CURRENTDATE

CURRENT DATE

________________

2015/08/28

SCOTT@PROD>SELECT TO_CHAR(123456789/1200,'$999,999,999.99')

  2  FROM DUAL;

TO_CHAR(12345678

----------------

     $102,880.66

SELECT TO_CHAR(123456789/1200,'$9999999.99') FROM DUAL;

TO_CHAR(1234

------------

  $102880.66

* 원하는 형식을 9와 , 로 자를 수 있다.

SELECT TO_CHAR(123456789/1200,'L9999999.99') FROM DUAL;

TO_CHAR(1234

------------

  ￦102,880.66

* L을 쓰면 로컬 화폐 단위로 출력한다.

 CASE 표현

함수설명 

 CASE

    SIMPLE_CASE_EXPRESSION 조건

    ELSE  표현절

END

SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건 내의 THEN 절을 수행하고 조건이 맞지 않는 경우 ELSE 절을 수행한다. 

 CASE

  SEARCHED_CASE_EXPRESSION 조건

  ELSE   표현절

END

SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건내의 THEN 절을 수행하고 조건이 맞지 않는경우 ELSE 절을 수행한다 

 DECODE( 표현식, 기준값1, 값1 

[, 기준값2,값2,...., 디폴드값])

ORACLE에서만 사용되는 함수로 표현식의 값이 기준값1이면 값1을 출력하고 기준값2임ㄴ 값2를 출력한다. 그리고기준값이 없으면 디폴트 값을 출력한다. CASE 표현의 SIMPLE_CASE_EXPRESSION 조건절과 동일하다. 

SELECT LOC

 CASE LOC

            WHEN  'NEW YORK' THEN 'EAST'

            WHEN  'BOSTON' THEN 'EAST'

            ELSE 'ETC'

            END as AREA

FROM DEPT;

LOC             AREA

---------------- ----------

NEW YORK EAST

BOSTON     EAST

SELECT ENAME,

               CASE WHEN SAL >=3000 THEN 'HIGH'

                          WHEN SAL >=1000 THEN 'MID'

                          ELSE 'LOW'

               END AS SALARY_GRADE

FROM EMP;

ENAME   SALARY_GRADE

---------- ----

GOODBOY    LOW

ALLEN   MID

WARD   MID

JONES   MID

MARTIN   MID

BLAKE   MID

CLARK   MID

SCOTT   HIGH

KING   HIGH

TURNER   MID

ADAMS   MID

JAMES   LOW

FORD   HIGH

MILLER   MID           

 연산

연산의 결과 

NULL + 2, 2 + NULL

NULL 

NULL - 2, 2 - NULL

NULL 

NULL * 2, 2 * NULL

NULL

NULL / 2, 2 / NULL

NULL 

 일반형 함수

함수설명 

 NVL(표현식1, 표현식2)

/ ISNULL(표현식1, 표현식2)

표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다.

단, 표현식과 표현식2의 결과 데이터 값이 같아야 한다.

NULL관련 가장 많이 사용되는 함수이므로 중요함. 

NULLIF(표현식1, 표현식2)

표현식1이 표현식2와 같으면 NULL,

같지않으면 표현식 1을 리턴한다. 

COALESCE(표현식1,표현식2,....)

임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다. 

 구분

연산자 

의미 

비교연산자

 = 

같다 

 >

크다 

 > =

크거나 같다

 < = 

작거나 같다 

 SQL 연산자

BETWEEN a AND b

a보다 크거나 같고 b보다 작거나 같다  

IN( list)

list에 있는 값 중에서 어느 하나라도 일치하면 된다. 

LIKE '비교문자열'

비교문자열과 형태가 일치하면 된다.

(%, _) 사용 

IS NULL

NULL 값인 경우 

AND

앞에 있는 조건과 뒤에 오는 조건이 참이되면 결과도 참이된다. 

(둘다 참) 

 OR

앞의 조건이 참이거나 뒤의조건이 참이 되어야 결과도 참이된다 

(둘 중 하나 이상 참)

NOT 

뒤에 오는 조건에 반대되는 결과를 되돌려준다. 

 부정비교연산자

! = 

같지 않다.

^ = 

같지 않다. 

 < >

같지않다. 

 NOT 칼럼명 = 

 ~ 와 같지않다. 

 NOT 칼럼명 > 

 ~ 보다 크지 않다 

 부정 SQL 연산자

NOT BEETWEEN a AND b 

a 보다 크거나 같지않으며 b보다 작거나 같지않다. 

 NOT IN (list)

list 값과 일치하지 않는다. 

 IS NOT NULL

NULL 값을 갖지 않는다. 

 구분

비교방법 

 비교연산자의 양쪽이 모두 CHAR 유형인 경우

길이가 서로 다른 CHAR형 타입이면 작은 쪽에 SPACE를 추가하여 길이를 같게한 후 비교한다. 

서로 다른 문자가 나올 때까지 비교한다.

달라진 첫 번째 문자의 값에 따라 크기를 결정한다.

BLANK의 수만 다르다면 서로 같은 값으로 결정한다.

 비교 연산자의 어느 한쪽이 VARCHAR인 경우

서로 다른 문자가 나올 때 까지 비교한다 

길이가 다르다면 짦은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다.

길이가 같고 다른 것이 없다면 같다고 판단한다.

VARCHAR는 NOT NULL까지 길이를 말한다. 

 상수값과 비교할 경우

상수 쪽을 변수 타입과 동일하게 바꾸고 비교한다. 

변수 쪽이 CHAR 유형 타입이면 위의 CHAR 유형 타입의 경우를 적용한다.

변수 쪽이 VARCHAR 유형 타입이면 위의 VARCHAR 유형 타입의 경우를 적용한다. 

 와일드카드

설명 

 %

0개 이상의 어떤 문자를 의미함 

 _

1개 인 단일 문자를 의미함. 

SCOTT@PROD>SELECT ENAME FROM EMP WHERE ENAME LIKE 'J%';

ENAME

----------

JONES

JAMES

* _ (언더바) 와일드카드 사용할 때는 길이를 정확히 맞춰야함.

SCOTT@PROD>SELECT ENAME FROM EMP WHERE ENAME LIKE 'J__';

no rows selected

SCOTT@PROD>SELECT ENAME FROM EMP WHERE ENAME LIKE 'J____';

ENAME

----------

JONES

JAMES

 구성

SCOTT@PROD>select * from emp order by empno;

     EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

            1 noname

      7369 seungho    singer       7902 17-DEC-80     800    20

      7499 ALLEN      people       7698 20-FEB-81   1600        300   30

      7521 WARD       actor       7698 22-FEB-81   1250        500   30

* WHERE절에 = 연산자를 통한 NULL값 비교

1) ' ' 표기

SELECT * 

FROM EMP 

WHERE JOB = 'NULL';

2) 

SELECT * 

FROM EMP 

WHERE JOB = NULL;

1,2 결과 모두 원하는 결과를 출력할 수 없다.

3) IS NULL 사용

SELECT * 

FROM EMP

WHERE JOB IS NULL ;

결과

    EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

1 noname

* NULL인 값만 조회 가능

4) IS NOT NULL 사용

SELECT *

FROM EMP

WHERE JOB IS NOT NULL;

     EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 seungho    singer       7902 17-DEC-80     800    20

      7499 ALLEN      people       7698 20-FEB-81   1600        300   30

      7521 WARD       actor       7698 22-FEB-81   1250        500   30

*  NULL 이 아닌 값만 조회 가능.

 한 건의 행만 가져오고 싶을 때

- SELECT * FROM EMP WHERE ROWNUM = 1;

- SELECT * FROM EMP WHERE ROWNUM <= 1;

- SELECT * FROM EMP WHERE ROWNUM < 2;

두 건 이상의 N행을 가져오고싶을 때

- SELECT * FROM EMP WHERE ROWNUM <= N;

- SELECT * FROM EMP WHERE ROWNUM < N+1;

 한 건의 행만 가져오고 싶을 때

- SELECT TOP(1) ENAME FROM EMP;

두 건 이상의 N행을 가져오고싶을 때

- SELECT TOP(N) ENAME FROM EMP;