1. 내장함수

 

벤더에서 제공하는 함수 내장함수 vs 사용자가 정의할 있는 함수

내장 함수 - 단일행 함수

       - 다중행 함수  - 집계함수

   - 그룹함함수

   - 윈도우함수

입력되는 값이 아누리 많아도 출력은 하나만 된다는 M : 1관계

 

단일행 함수의 종류 (oracle/sqlserver)

종류

내용

함수

문자형

문자를 입력하면 문자나 숫자값 반환

Lower, upper, substr/substiring, length/len, Ltrim, Rtrim, trim, ASCII

숫자형

숫자 입력하면 숫자값 반환

Abs, mod, round, trunc, sign, chr/char, ceil/ceiling floor, exp, log, ln, power, sin, cos, tan

날짜형

DATE 타입의 값을 연산

Sysdate/getdate, extract/datepart, to_number(to_char('YYYY','MM','DD')/YEAR,MONTH,DAY

변환형

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

To_number, to_char, to_date / cast, convert

NULL관련

NULL처리하기 위한 함수

NVL/ISNULL, NULLIF, COALESCE

 

특징

  1. Select, where, order by 절에서 사용가능
  2. 행들에 대한 개별적인 작용하여 조작
  3. 여러 인자를 입력해도 하나의 결과만 리턴
  4. 인자로 상수, 변수, 표현식 사용 가능하고 하나의 인수를 가지는 경우도 있고 여러개의 인수도 가질 있음
  5. 함수의 중첩 가능

 

2. 문자형함수


함수

설명

LOWER(문자열)

소문자로 변경

UPPER(문자열)

대문자로 변경

ASCII(문자열)

숫자를 ASCII코드로 변경

CHR/CHAR(ASCII번호)

ASCII코드를 문자나 숫자로 변경

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

Oracle, mysql에서만, 문자열1 문자열2 연결, || + 함수와 동일

SUBSTR/SUBSTRING(문자열, M[, N ])

문자열 m위치에서 n개의 문자 길이에 해당하는 문자 추출, n 생략되면 추출

LENGTH/LEN(문자열 [, 지정문자])

문자열의 개수를 숫자값으로 변환

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

첫문자부터 지정문자가 나타나면 해당 문자 제거, sqlserver 사용 불가

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

마지막문자부터 지정문자가 나타는 동안 해당 문자 제거, sqlserver 사용 불가

TRIM ([leading|trailing|both] 지정문자 from 문자열)

머리말, 꼬리말 또는 양쪽 지정문자 제거, sqlserver 사용 불가

 


 

DUAL 테이블의 특성

  • 사용자 SYS 소유, 모든 사용자가 엑세스 가능한 테이블
  • DUMMY 테이블
  • 'X'라는 값이 들어있는 1건을 포함
  • SQLserver 에서는 select 있어도가능하여 DUAL 필요없음

 

3. 숫자형 함수


함수

설명

ABS(숫자)

절대값

SIGN(숫자)

양수인지, 음수인지, 0인지 구별

MOD(숫자1, 숫자2)

숫자1 숫자2 나누어 나머지 리턴,  % 함수랑 동일

CEIL/CEILING(숫자)

올려서 정수로 반환

FLOOR(숫자)

내려서 정수로 반환

ROUND(숫자 [, m])

소수점 m 자리에서 반올림

TRUNC(숫자 [, m])

소수점 m 자리에서 버림, sqlserver에서 사용불가

 SIN, COS, TAN

삼각함수

EXP, POWER, SQRT, LOG, LN

지수, 거듭제곱, 제곱근, 자연로그 리턴

 


 

4. 날짜형 함수

 

함수

설명

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)

날짜데이터에서 // 데이터 출력

TO_NUMBER제외하면 문자열로 출력됨

 

5. 변환형 함수


특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수

종류

설명

명시적 데이터 유형 변환

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

암시적 데이터 유형 변환

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

암시적 데이터 변환은 성능저하 발생, 알아서 계산하지 않는 에러 발생할 있어서 명시적 데이터 유형 변환 방법 사용 필요

 

Oracle 함수

설명

TO_NUMBER(문자열)

숫자로 변환

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

문자열로 변환

TO_DATE(문자열 [, FORMAT])

날짜로 변환

 

SQL server 함수

설명

CAST(expression AS date_type[(length)])

목표 데이터 유형으로 변환

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

목표 데이터 유형으로 변환

예제  SELECT TEAM_ID, CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합 FROM TEAM;

 

6. CASE 표현


단일행 CASE 표현의 종류

함수

설명

  CASE

     SIMPLE_CASE_EXPRESSION 조건

     ELSE 표현절

  END

 

SIMPLE_CASE_EXPRESSION 조건에 따라서

맞으면 then 수행 아니면 else 수행 ( EQUI = 조건 )

CASE EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR ELSE 표현절 END

  CASE

     SEARCHED_CASE_EXPRESSION 조건

     ELSE 표현절

  END

 

SEARCHED_CASE_EXPRESSION 조건에 따라서 맞으면 then 수행 아니면 else 수행

( EQUI 뿐만 아니라 = , >, >= , < , <= 가능) 중첩사용 가능

CASE WHEN CONDITION THEN RETURN_EXPR ELSE 표현절 END

 

  DECODE(표현식, 기준값, 값1, … , default)

오라클에서만 사용하는 함수

표현식 값이 기준값이면 1출력, 기준값이 없음 디폴트값

SIMPLE_CASE_EXPRESSION 조건이랑 동일

예시 중첩 : SELECT ENAME, SAL,

CASE WHEN SAL >= 2000

THEN 1000

ELSE (CASE WHEN SAL >= 1000 THEN 500 ELSE 0 END)

END as BONUS FROM EMP;

 

7. NULL 관련 함수

 

. NVL/ISNULL 함수

 

NULL 특성

  • 정의되지 않은 값으로 0 또는 공백과는 다르다
  • NOTNULL, PK아니면 널값 포함 가능하다
  • 값을 포함하는 연산의 경우 결과는

 

함수

설명

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

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

표현식1 결과값이 NULL이면 표현식 2값이 출력된다.

, 표현식 1, 2 데이터 타입이 같아야함

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

표현식1 표현식2 같으면 NULL, 같지 않으면 표현식1 출력

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

임의의 개수 표현식에서 NULL 아닌 최초의 표현식을 나타냄

 

. NULL 공집합

  • 일반적인 NULL/ISNULL 함수 사용
  • 공집합의 NULL/ISNULL 함수 사용 : 공집합을 대상으로 하지 않음

 

. NULLIF

. COALESCE

표현식1, 2 둘중 1번의 값이 있음 1번을 2번에 값이 있음 2번을 1,2 둘다 있다면 1번을 둘다 없다면 NULL 표현

 

 분모에 0이면 무조건 에러, null 무슨 연산을 하여도 null


'자격증 공방 > SQLD' 카테고리의 다른 글

제 3-8절 ORDER BY절  (0) 2018.07.07
제 3-7절 GROUP BY, HAVING 절  (0) 2018.07.07
제 3-5절 WHERE 절  (0) 2018.07.07
제 3-4절 TCL (TRANSACTION CONTROL LANGUAGE)  (0) 2018.07.07
제 3-3절 DML (DATA MANIPULATION LANGUAGE)  (0) 2018.07.07

 

1. WHERE 조건절 개요

원하는 자료만 검색하기 위해서나 이상의 테이블에 대한 조건을 기술하거나 결과를 제한하기 위한 조건을 기술

SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명]

FROM 테이블명

WHERE 조건식;

  • 컬럼명
  • 비교연산자
  • 문자, 숫자, 표현식
  • 비교칼럼 (조인 사용시)

 

2. 연산자의 종류

구분

연산자

비교연산자      

= , >= , >, <, <=         

부정 비교 연산자

!=, ^=, <>(표준), NOT칼럼명=, NOT칼럼명 >

SQL연산자     

Between A and B, In(list), Like'비교문자열', IS NULL

부정 SQL 연산자

NOT Between A and B,  NOT In(list), NOT IS NULL

논리연산자

AND, OR, NOT

 

처리 우선순위

괄호 NOT 연산자 비교연산자, SQL연산자 AND OR

 

3. 비교연산자

 

Char varchar2 같은 문자형 타입을 가진 칼럼은 인용부호( ' ' , " ") 묶어서 비교 처리 해야함

문자 유형 비교 방법

구분

비교방법

양쪽 모두가 CHAR

서로 다른 CHAR형이면 작은쪽에 SPACE 추가하려 길이를 같게 비교

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

달라진 첫번쨰 문자의 값에 따라 크기 결정

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

한쪽이 VARCAHR2

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

길이가 다르면 짧은것이 끝날떄까지만 비교 길기가 긴것이 크다고 판단

길이가 같고 다른것이 없으면 같음

NOT NULL까지 길이 포함

상수값과 비교시

상수를 변수타입으로 바꾸고 비교

 

4. SQL연산자

    • IN 연산자 : SQL 문장을 짧게 만들어주면서도 성능측면에서도 장점을 가질수 있는 매우 유용한 연산자, 사용 권장

Ex) WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));

  • LIKE 연산자 : % (0 이상의 어떤 문자를 의미), _ (1개인 단일 문자 의미)
  • Between A and B 연산자
  • IS NULL 연산자 : 값이 존재하지 않는 , 확정되지 않은 표현할때 사용

NULL값과의 수치연산은 NULL, NULL과의 비교연산은 FALSE, 어떤 값과도 비교 없음

 

5. 논리연산자

우선순위 : ( ) NOT AND OR

 

6. 부정 연산자

  

 

7. ROWNUM, TOP

 

. ROWNUM

오라클의 ROWNUM 임시로 부여되는 일련번호, 원하는 만큼만 가지고 때는 where 절에서 개수 제한 가능

SELECT 컬럼명 FROM 테이블명 WHERE ROWNUM = N;

SELECT 컬럼명 FROM 테이블명 WHERE ROWNUM <= N+1;

 

고유한 키나 인덱스 만들수 있음

UPDATE 테이블명 SET COLUMN1 = ROWNUM;

 

. TOP

SQL server 제한

TOP (Expression) [PERCENT] [WITH TIES]

  • Expression : 반환할  행수
  • Rercent : 쿼리 결과 집합에서 처음 Expression % 포함된 행만 반환(백분율 환산)
  • With Ties : order by 절이 지정된 경우에만 사용, 같은 값이 있다면 추가 행을 출력할 있도록 지정

'자격증 공방 > SQLD' 카테고리의 다른 글

제 3-7절 GROUP BY, HAVING 절  (0) 2018.07.07
제 3-6절 함수  (0) 2018.07.07
제 3-4절 TCL (TRANSACTION CONTROL LANGUAGE)  (0) 2018.07.07
제 3-3절 DML (DATA MANIPULATION LANGUAGE)  (0) 2018.07.07
제 3-2절 DDL  (0) 2018.07.07

 

1. 트랜잭션 개요

데이터 베이스의 논리적 연산 단위

분리될수 없는 이상의 데이터 베이스 조작

분할할 없는 최소의 단위

하나의 논리적인 작업단위를 구성하는 세부적인 연산들의 집합

DML(update, delete, insert ..)등의 데이터 수정문이 대상임

Ex) 은행의 계좌이체

 

트랜잭션의 특성

특성

설명

원자성

정의된 연산은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아야 , 에러가 생기면 전부 롤백해야함

일관성

실행전의 데이터베이스 내용이 잘못되어있지 않았따면 실행 후에도 내용이 잘못되면 안됨

고립성

실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨

지속성

성공적으로 수행되면 갱신한 데이터베이스 내용은 영구적으로 저장

동시 접근이 불가능하도록 다양한 레벨의 잠금 기능을 제공

트랜잭션 수행시 잠금(LOCKING)상태가

 

트랜잭션에 대한 격리성이 낮은 경우 발생하는 문제점

  1. Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는
  2. Non-Repeatable Read : 트랜잭션 내에서 같은 쿼리를 수행 했는데, 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에

    쿼리 결과가 다르게 나타남

  1. Phantom Read : 트랜잭션 내에서 같은 쿼리를 수행 했는데, 번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

 

2. COMMIT

COMMIT이나 ROLLBACK 이전의 데이터 상태

데이터 변경의 이전 태로 복구 가능, select문으로 결과 확인 가능, 타인은 현재 사용자가 수행한 명령 결과만 볼수 있음, LOCKING 되어있음

COMMIT 이후 상태

변경 사항이 데이터베이스에 반영, 이전 데이터는 영원히 잃어버림, 모든 사용자는 결과 확인 가능, 잠금이 풀리고 다른 사용자들도 조작 가능

ORACLE에서는 DML이후 COMMIT ROLLBACK 수행해야 트랜잭션 종료

SQL server 기본적으로 AUTO COMMIT 모드

 

SQL server 트랜잭션

  1. AUTO COMMIT : DBMS 트랜잭션을 컨트롤
  2. 암시적 트랜잭션 : 사용자가 명시적으로 COMMIT 또는 ROLLBACK 처리, 세션 단위로 설정은 SET IMPLICIT TRANSACTION ON 사용
  3. 명시적 트랜잭션 : 시작과 모두 사용자가 명시적으로 지정하는 방식 BEGIN으로 시작하고 COMMIT 또는 ROLLBACK으로 종료

 

3. ROLLBACK

데이터 변경사항이 취소되고 이전상태로 복구

SQL server AUTO COMMIT이고 임의적으로 수행하려면 명시적으로 트랜잭션을 선언해야함

 

COMMIT ROLLBACK 효과

  • 데이터 무결성 보장
  • 영구적인 변경 데이터 변경사항 확인 가능
  • 논리적으로 연관된 작업으 그룹핑하여 처리 가능

 

4. SAVEPOINT

저장점을 정의하면 현시점에서 SAVEPOINT까지 트랜잭션 일부만 변경 가능

 

ORACLE

SQL server

저장점 정할

SAVEPOINT SVPT1;

SAVE TRANSACTION SVTR1;

트랜잭션을 저장점 까지 실행할

ROLLBACK TO SVPT1;

ROLLBACK TRANSACTION SVTR1;


 

해당 테이블에 데이터의 변경 하는 INSERT, UPDATE, DELETE 수행시 데이터의 무결성을 보장하는 것이 COMMIT&ROLLBACK 의 목적

 

[Oracle의 트랜잭션]

자동 시작되고 COMMIT 또는 ROLLBACK을 실행한 시점에서 종료

단, 다음의 경우에는 COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료된다.

  • DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
  • 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
  • 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

 

[SQL Server의 트랜잭션]

DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식

  • 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

'자격증 공방 > SQLD' 카테고리의 다른 글

제 3-6절 함수  (0) 2018.07.07
제 3-5절 WHERE 절  (0) 2018.07.07
제 3-3절 DML (DATA MANIPULATION LANGUAGE)  (0) 2018.07.07
제 3-2절 DDL  (0) 2018.07.07
제 3-1절 관계형 데이터베이스 개요  (0) 2018.07.07

+ Recent posts