1.내장함수
DMBS가 제공하는 내장함수와 사용자가 필요에 따라 직접 만드는 사용자정의함수가 있다.
SQL 내장함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환한다.
모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 한다.
.1) 숫자함수
|
함수 |
설명 |
|
ABS(숫자) |
숫자의 절댓값을 계산 ABS(-4.5) -> 4.5 |
|
CEIL(숫자) |
숫자보다 크거나 같은 최소의 정수 CEIL(4.1) -> 5 |
|
FLOOR(숫자) |
숫자보다 작거나 같은 최소의 정수 FLOOR(4.1) -> 4 |
|
ROUND(숫자, m) |
숫자의 반올림, m은 반올림 기준 자릿수 ROUND(5.36, 1) -> 5.40 |
|
LOG(n, 숫자) |
숫자의 자연로그 값을 반환 LOG(10) -> 2.30259 |
|
POWER(숫자, n) |
숫자의 n제곱 값을 계산 POWER(2, 3) -> 8 |
|
SQRT(숫자) |
숫자의 제곱근 값을 계산(숫자는 양수) SQRT(9.0) -> 3.0 |
|
SIGN(숫자) |
숫자가 음수면 –1.0이면 0, 양수면 1 SIGN(3.45) -> 1 |
.2) 문자함수
|
반환구분 |
함수 |
설명 |
|
문자값 반환 함수
s : 문자열 c : 문자 n : 정수 k : 정수 |
CONCAT(s1,s2) |
두 문자열을 연결 CONCAT(‘마당’,‘서점’) -> ‘마당 서점’ |
|
LOWER(s) |
대상 문자열을 모두 소문자로 변환 LOWER(‘MR.SCOTT’) -> ‘mr.scott’ |
|
|
LPAD(s,n,c) |
대상 문자열의 왼쪽부터 지정한 자리수ᄁᆞ지 지정한 문자로 채움 LPAD(‘Page 1’, 10, ‘*’) -> ‘****Page 1’ |
|
|
REPLACE(s1,s2,s3) |
대상 문자열의 지정한 문자를 원하는 문자로 변경 REPLACE(‘JACK & JUE’, ‘J’, ‘BL’) -> ‘BLACK & BLUE’ |
|
|
RPAD(s,n,c) |
대상 문자열의 오른쪽부터 지정한 자리수까지 지정한 문자로 채움 RPAD(‘AbC’,5,‘*’) -> ‘AbC**’ |
|
|
SUBSTR(s,n,k) |
대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환 SUBSTR(‘ABCDEFG’,3,4) -> ‘CDEF’ |
|
|
TRIM(c FROM s) |
대상 문자열의 양쪽에서 지정된 문자를 삭제 TRIM(‘=’ FROM ‘==BROWNING==’) -> ‘BROWNING’ |
|
|
UPPER(s) |
대상문자열을 모두 대문자로 변환 UPPER(‘mr.scott’) -> ‘MR.SCOTT’ |
|
|
숫자값 반환함수 |
ASCII(c) |
대상 알파벳 문자의 아스키 코드 값을 반환 ASCII(‘D’) -> 68 |
|
LENGTH(s) |
대상 문자열의 Byte 반환, 알파벳 1byte, 한글3byte (UTF8) LENGTH(‘CANDIDE’) -> 7 |
|
|
CHAR_LENGTH(s) |
문자열의 문자 수를 반환 CHAR_LENGTH(‘데이터’) -> 3 |
.3) 날짜,시간 함수
|
함수 |
반환형 |
설명 |
|
STR_TO_DATE(string, format) |
DATE |
문자열(STRING)데이터를 날짜형(DATE)으로 반환 STR_TO_DATE(‘2019-02-14’, ‘%Y-%m-%d’) -> 2019-02-14 |
|
DATE_FORMAT(date,format) |
STRING |
날짜형(DATE) 데이터를 문자열(VARCHAR)로 반환 DATE_FORMAT(‘2019-02-14’, ‘%Y-%m-%d’) -> ‘2019-02-14’ |
|
ADDDATE(date,interval) |
DATE |
DATE형의 날짜에서 INTERVAL 지정한 시간만큼 더함 ADDDATE(‘2019-02-14’, INTERVAL 10 DAY) -> 2019-02-24 |
|
DATE(date) |
DATE |
DATE형의 날짜 부분을 반환 DATE(‘2003-12-31 01:02:03’); -> 2013-12-31 |
|
DATEDIFF(date1,date2) |
INTEGER |
DATE 형의 date1 – date2 날짜 차이를 반환 DATEDIFF(‘2019-02-14’, ‘2019-02-04’) -> 10 |
|
SYSDATE |
DATAE |
DBMS 시스템상의 오늘 날짜를 반환하는 함수 SYSDATE() |
|
인자 |
설명 |
|
%w |
요일순서(0~6, Sunday=0) |
|
%W |
요일(Sunday~Saturday) |
|
%a |
요일의 약자(Sun~Sat) |
|
%d |
1달 중 날짜(00~31) |
|
%j |
1년 중 날짜(001~366) |
|
%h |
12시간(01~12) |
|
%H |
24시간(00~23) |
|
%i |
분(0~59) |
|
%m |
월 순서(01~12, January=01) |
|
%b |
월 이름 약어(Jan~Dec) |
|
%M |
월 이름(January~December) |
|
%s |
초(0~59) |
|
%Y |
4자리 연도 |
|
%y |
4자리 연도의 마지막 2자리 |
.4) NULL 값 처리
NULL 값이란 아직 지정되지 않은 값을 말한다.
NULL 값은 ‘0’, ‘’(빈문자), ‘’(공백)등과 다른 특별한 값이다.
NULL 값은 비교연산자로 비교가 불가능하다.
NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환된다.
..(1) NULL 값에 대한 연산과 집계함수
....- ‘NULL+숫자’ 연산의 결과는 NULL 이다.
....- 집계함수를 계산할 때 NULL이 포함된 행은 집계에서 빠진다.
....- 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되고, COUNT 함 .......수의 결과는 0이다.
..(2) NULL 값을 확인하는 방법 – IS NULL, IS NOT NULL
..(3) IFNULL 함수
IFNULL(속성, 값) : 속성 값이 NULL 이면 ‘값’으로 대치한다.
.5) 행번호 출력
..(1) SQL문 결과로 나오는 행에 번호를 붙이거나 행번호에 따라 결과의 개수를 조절
....- SET @변수 := 치환할 값;
부속질의(subquery)
하나의 SQL 문안에 다른 SQL문이 중첩된 질의
다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용
|
명칭 |
위치 |
영문 및 동의어 |
설명 |
|
스칼라 부속질의 |
SELECT 절 |
scalar subquery |
SELECT 절에서 사용되며 단일 값을 반환하기 때문에 스칼라 부속질의라고 한다. |
|
인라인 뷰 |
FROM 절 |
inline view, table subquery |
FROM절에서 결과를 뷰(view) 형태로 반환하기 때문에 인라인 뷰라고 한다. |
|
중첩질의 |
WHERE 절 |
nested subquery, predicate subquery |
WHERE 절에 술어와 같이 사용되며 결과를 한정시키기 위해 사용된다. 상관 혹은 비상관형태다. |
중첩질의 연산자의 종류
|
술어 |
연산자 |
반환 행 |
반환 열 |
상관 |
|
비교 |
=, >, <, >=, <=, <> |
단일 |
단일 |
가능 |
|
집합 |
IN, NOT IN |
다중 |
다중 |
가능 |
|
한정 |
ALL, SOME(ANY) |
다중 |
단일 |
가능 |
|
존재 |
EXISTS, NOT EXISTS |
다중 |
다중 |
필수 |
저장 프로그램
데이터베이스 응용 프로그램을 작서앟는데 사용하는 MySQL의 SQL 전용 언어
.1) 저장프로그램 – 프로시저(procedure)
...- 프로그램 로직을 프로시저로 구현하여 객체형태로 사용한다.
...- 저장프로그램은 일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념이다.
...- 저장프로그램의 구성 : 저장 루틴(routine), 트리거(trigger), 이벤트(event)
...- 저장 루틴 : 프로시저(procedure), 함수(function)
...- 프로시저는 선언부와 실행부(BEGIN-END)로 구분된다.
...- 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현한다.
...- 매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
...- 변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.
...- 프로시저의 끝을 ‘;’로 하면 프로시저 안의 명령어 끝을 나타내는 ‘;’와 혼돈이 생기기
......때문에 구문 구분자를 설정하는 ‘//’를 정의하고 마지막에 다시 ‘;’로 재정의한다.
...- 저장 프로그램의 제어문
|
구문 |
의미 |
문법 |
|
DELIMITER |
구문종료기호 설정 |
DELIMITER {기호} |
|
BEGIN-END |
프로그램 문을 블록화 시킴 중첩가능 |
BEGIN {SQL 문} END |
|
IF-ELSE |
조건의 검사 결과에 따라 문장을 선택적으로 수행 |
IF <조건> THEN {SQL문} [ELSE {SQL문}] END IF; |
|
LOOP |
LEAVE 문을 만나기 전까지 LOOP을 반복 |
[label:] LOOP {SQL 문 | LEAVE [label]} END LOOP |
|
WHILE |
조건이 참일 경우 WHILE 문의 블록을 실행 |
WHILE <조건> DO {SQL 문 | BREAK | CONTINUE} END WHILE |
|
REPEAT |
조건이 참일 경우 REPEAT 문의 블록을 실행 |
[label:] REPEAT {SQL 문 | BREAK | CONTINUE} UNTIL <조건> END REPEAT [label:] |
|
RETURN |
프로시저를 종료 상태값 반환 가능 |
RETURN [<식>] |
...(2) 커서(cursor)를 사용하는 프로시저
.....- 커서는 실행결과 테이블을 한 번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로
........가리키는데 사용한다.
|
키워드 |
역할 |
|
CURSOR <cursor 이름> IS <커서 정의> DECLARE <cursor 이름> CURSOR FOR |
커서를 생성 |
|
OPEN <cursor 이름> |
커서의 사용을 시작 |
|
FETCH <cursor 이름> INTO <변수> |
행 데이터를 가져옴 |
|
CLOSE <cursor 이름> |
커서의 사용을 끝냄 |
.2) 트리거(Trigger)
..- 데이터의 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 같이 실행되는
.....프로시저
..- 보통 데이터의 변경문이 처리되는 실행 전(BEFORE), 대신하여(INSTEAD OF),
.....실행 후 (AFTER)에 동작한다.
※프로시저, 트리거, 사용자 정의 함수의 공통점과 차이점
|
구분 |
프로시저 |
트리거 |
사용자 정의함수 |
|
공통점 |
저장 프로시저 |
||
|
정의방법 |
CREATE PROCEDURE 문 |
CREATE TRIGGER문 |
CREATE FUNCTION 문 |
|
호출방법 |
CALL문으로 직접호출 |
INSERT, DELETE, UPDATE 문이 실행될 때 자동으로 실행됨 |
SELECT 문에 포함 |
|
기능의 차이 |
SQL문으로 할 수 없는 복잡한 로직을 수행 |
기본값 제공, 데이터 제약 준수, SQL 뷰의 수정 , 참조무결성 작업등을 수행 |
속성 값을 가공하여 반환, SQL 문에서 직접 사용 |
