3. SQL함수
역할:
-데이터의 계산 수행
-개별 데이터 항목 수정
-행 그룹에 대한 출력 결과 조작
-표시할 날짜 및 숫자 형식 지정
-열 데이터 유형 변환
SQL함수에는 단일행 & 여러행 두가지 종류가있다.
3-1.단일 행 함수
단일 행만 연산해서 하나의 결과를 반환한다.
인수를 하나 이상 사용할 수 있고 query에서 반환하는 행마다 하나의 값을 반환하며, 중첩될 수 있다.
여러 유형이 있음(문자, 숫자, 날짜, 변환, 일반)
-문자 함수: 문자입력 --> 문자 or 숫자값 반환
대소문자 조작: LOWER, UPPER, INITCAP
문자 조작 함수: CONCAT(문자열 연결), SUBSTR(문자열 부분), LENGTH, INSTR(문자열의 위치), LPAD(왼쪽 폭조절), RPAD(오른쪽 폴조절), TRIM, REPLACE(문자열 대체)
-숫자 함수: 숫자입력 --> 숫자값 반환
ROUND(반올림)
TRUNC(잘라내기)
MOD(divide후 remainer 반환)
-날짜 함수: DATE 데이터 유형의 값에 동작함.
기본형은 DD-MON-RR임.
SYSDATE는 데이터베이스 서버의 현재 날짜 및 시간을 반환함.
select SYSDATE
from DUAL;이렇게 query문을 작성하면, sysdate이라는 column에 현재 날짜가 입력된 결과 나온다.
-날짜 계산: 연산 방식
date + number = [날짜] 날짜에 일수를 더함
date - number = [날짜] 날짜에 일수를 뺌
date - date = [일수] 한 날짜에서 다른 날짜를 뺌.
date + number/24 = [날짜] 날짜에 시간 수를 더함
-날짜 계산: 함수 방식
MONTHS_BETWEEN(date1, date2) ==> 개월 수(숫자)를 반환
ADD_MONTHS(date, n)
NEXT_DAY(date, 'char')
LAST_DAY(date)
ROUND( date[, 'format']) - format에 의해 지정된 단위로 반올림한 date를 반환
TRUNC(date[, 'format']) - 날짜의 시간 부분을 format으로 지정한 단위까지 남기고 버림.
-변환 함수: 값에 데이터 유형을 변환
Implicit 변환 - Oracle server에 의해 자동으로 변환을 수행
VARCHAR2 (or CHAR) --> NUMBER
VARCHAR2 (or CHAR) --> DATE
NUMBER --> VARCHAR2
DATE --> VARCHAR2
Explicit 변환 - user에 의해 수행
값의 데이터 유형을 변환하는 함수:
TO_CHAR(numer or date, ['format model'], 파라미터(optional)) - 형식모델 format을 사용하여
숫자 or 날짜 값을 VARCHAR2 문자열로 변환
사용자가 원하는 문자형식으로 날짜를 표현할 수 있게 해준다.
작은 따옴표로 묶어야하며, 대소문자를 구분한다!
*많이 사용하는 날짜 형식 모델 요소 (for formatting)
YYYY 네자리 연도(숫자)
YEAR 연도(문자)
MM 두자리 값으로 나타낸 달
MONTH 달 전체 이름
MON 세 자 약어로 나타낸 달
DY 세 자 약어로 나타낸 요일
DAY 요일 전체 이름
DD 숫자로 나타낸 달의 일.... 이외에도 더 많음.
example1)
TO_CHAR(hire_date, 'MM/'YY') ==> 01/21
hire_date 열의 값을 '월/년' format의 문자열로 변환.
example2)
TO_CHAR(hire_date, 'fmDD Month YYYY')==> 17 January 2021
채워진 공백을 제거하거나 선행 제로를 제거하여면 채우기 모드 "fm"요소를 사용하면 됨.
지정한 포멧의 숫자로 변환
-숫자 형식 모델 요소 (for formatting)
9 숫자 위치
0 선행 제로 표시
$ 맨앞에 달러 기호 fix
, 천단위를 표기하는 comma.... 이외에 많음
example)
TO_CHAR(salary, '$99,999.00') ==> $6,000.00
TO_NUMBER(char, ['format model'], 파라미터(optional)) - 숫자를 포함하는 문자열을 형식 모델 format으로
TO_DATE(char, ['format'], 파라미터(optional)) - 날짜를 나타내는 문자열을 format으로 지정한
포멧의 날짜로 변환. format 생략된 경우 DD-MON-YY로 변환됨
"fx"수정자는 TO_DATE 함수의 문자 인수 및 날짜 형식 모델이 정확하게 일치하도록 지정한다.
(즉, 대소문자를 제외하고 정확하게 일치해야함. 문자 인수에 여분의 공백이 있으면 안됨.)
-중첩 함수: 가장 안쪽에서부터 바깥쪽순으로 계산된다. 단일 행 함수는 여러번 중첩될 수 있다.
-일반 함수: NVL, NVL2, NULLIF, COALESCE, CASE, DECODE
null값이 포함되어도 아래 일반 함수를 사용해서 결과가 null이 되어버리지 않도록 조작할 수 있다.
NVL(expr1, expr2) - expr1이 null인 경우, null을 expr2로 변환한다.
주의할점: NVL을 사용하면 반환되는 값은 항상 expr1의 데이터 유형과 동일하다.
NVL2(expr1, expr2, expr3) - expr1이 null인 경우, expr3로 변환. expr1이 null이 아닌 경우 expr2로 변환
NULLIF(expr1, expr2) - expr1과 expr2가 비교되고 동일하지 않은 경우 expr1이 반환됨. 동일한 겨우 null 반환.
expr1에 null literal을 지정할 수는 없다.
COALESCE(expr1, expr2, ..., exprn) - NVL과 다른게 여러대의 expr 표현식을 사용할 수 있다.
expr1이 null이 아닌 경우, expr1을 반환. expr이 null이고 expr2가 null이 아닌경우, expr2를 반환
이렇게 n번째 expr까지 적용
-조건표현식
SQL문에서 if-then-else 논리를 사용할 수 있다.
CASE 표현식: 괄호 사용X, END라고 표기하여 끝을 맺어야한다.
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
ELSE else_expr]
END이런 모양으로 구현한다. return_expr와 else_expr에는 null 리터럴을 지정할 수 없다.
DECODE 함수: 괄호 사용O, default 값을 명시하고 따로 표기없이 끝난다.
DECODE(col | expr, search1, result1
[, search2, result2, ...]
[, default])이런 모양으로 구현한다. expr을 각 search 값과 비교한 후, 동일하면 result를 반환
example1)
select count(*) total,
sum(decode(to_char(hire_date,'yy'),05,1,0))"2005",
sum(decode(to_char(hire_date,'yy'),06,1,0))"2006",
sum(decode(to_char(hire_date,'yy'),07,1,0))"2007",
sum(decode(to_char(hire_date,'yy'),08,1,0))"2008"
from employees;==> decode(column|expression: hire_date의 년도, search:05년,
search조건에 해당하면 result:1개 카운트 추가, default:0개 카운트 추가)
example2)
select job_id,
sum(decode(department_id, 20, salary, 0))"Dept 20",
sum(decode(department_id, 50, salary, 0))"Dept 50",
sum(decode(department_id, 80, salary, 0))"Dept 80",
sum(decode(department_id, 90, salary, 0))"Dept 90",
sum(salary)"Total"
from employees
group by job_id;==>job_id로 그룹한 그룹에서 부서 20, 50, 80, 90에 각각해당하는 경우 급여 총액을 출력
3-2.여러 행 함수
행 그룹을 조작해서 그룹이 하나의 결과를 만들도록함.
-주요 그룹함수 및 역할:
AVG, SUM, VARIANCE, STDDEV함수는 NUMBER 데이터 유형에만 사용가능
AVG: null값을 무시하고 그룹위 평균값을 구한다.
COUNT: 없이는 null이 아닌 행의 수를 구하는데, 를 사용하면 중복행 및 null이 있는 행을 포함해서 count
3가지 형식-
COUNT(*)
COUNT(expr) - expr로 식별되는 열에서 null이 아닌 행의 수를 반환
COUNT(DISTINCT expr) - 위와같은 조건에서 중복이 아닌 행의 수를 반환
MAX: null값 무시 하고 최대값
MIN:null값 무시 하고 최소값
MAX,MIN함수는 모든 데이터 유형에 대해 사용 가능 (number, date, name(alphabetical order) etc...)
STDDEV: null값 무시 하고 표준편차
SUM: null값 무시 하고 합계
VARIANCE: null값 무시 하고 분산을 구한다
-key words 및 중요사항:
DISTINCT: 중복되지 않는 값만 고려하고,
ALL: 중복값을 포함한 모든 값을 고려한다. (default가 all이다)
expr인수를 가진 함수의 데이터 유형은 CHAR, VARCHAR2, NUMBER or DATE
모든 그룹함수는 null값을 무시한다. NVL, NVL2, COALESCE를 활용해서 null값을 치환할수있다
-그룹데이터 생성:
GROUP BY절을 사용해서 table의 행을 내부 그룹으로 나눈후, 그룹 함수를 사용해서 각 그룹에 대한 요약
정보를 구한다.
WHERE절을 사용해서 그룹을 제한할수는 업지만, GROUP BY로 나누기전에 WHERE을 사용해서 조건에 따라 행을 먼저 제외시킬 수 있다.
GROUP BY에서는 열 별칭 사용 불가능 하다.
GROUP BY절을 사용하면 Oracle server가 자동으로 오름차순으로 정렬함. 내림차순으로 정렬하고싶으면 ORDER BY DESC를 활용.
SELECT 목록의 열 중, 그룹함수가 아닌 열은 모두 GROUP BY절에 포함되어야함.
==>GROUP BY절에 포함된 열을 기준으로 테이블을 나누어서 각 그룹별로 그룹함수를 수행하는 것이다.그래서 SELECT 목록에 포함시키지 않아도 GROUP BY절에 표기되어있으면 괜찮다. 반면, group by 열은 select절에 포함시키지 않아도 괜찮다.
example1)
select department_id, AVG(salary)
from employees
where department_id is not null
group by department_id;example2)
select department_id, AVG(salary)
from employees
where salary > 10000
group by department_id;==> 이렇게 group하기전에 department_id 또는 salary 값에따라서 행을 먼저 제외시키고 group하는것은 OKAY.
-그룹 내 그룹:
하나 이상의 GROUP BY 열을 나열해서
그룹 및 하위 그룹에 그룹 함수를 적용해서 원하는 요약 정보를 얻는다.
example)
select department_id, job_id, SUM(salary)
from employees
group by department_id, job_id==> department_id로 그룹하고, 그 그룹내에서 job_id로 다시 한번 더 그룹으로 나눈다.
-잘못 작성하는 방식:
1. WHERE절에서 그룹 함수를 통해 GROUP을 제한할 수 없다
2. HAVING저을 사용해서 GROUP을 제한할 수 없다
example of error)
select department_id, AVG(salary)
from employees
where AVG(salary)>8000
group by department_id==> error발생. where절이 잘못 작성됨. where절로는 그룹을 제한할 수 없음. 이때는 where대신 having으로 작성하면 OKAY.
example)
select department_id, AVG(salary)
from employees
having AVG(salary)>8000==> 이렇게 fix하면, department_id로 그룹되어 각 그룹별 avg salary가 8k 이상인 그룹만 결과로 출력됨.
-그룹 결과 제한:
HAVING절을 사용해서 그룹을 제한할 수 있다. (기존에 WHERE절을 사용해서 선택할 행을 제한하는것과 동일한 방식임)
Having절안에 명시하는 group_condition에 지정한 조건이 TRUE인 그룹으로 제한이됨.
Oracle server가 having절을 처리하는 순서: 행이 그룹화되고 -> 그룹 함수가 적용되고 -> HAVING절과 일치하는 그룹이 표시된다.
문법상 group by앞에 having절이 올수는 있지만, 논리적으로 group by로 그룹이 먼저 형성되고, 그룹 함수가 계산된 후, select목록의 그룹에
having절이 적용되는것이 맞음. 그래서 having절은 group by절 후에 작성한다.
SELECT column, group function
FROM table
[WHERE condition] <--행을 제한
[GROUP BY group_by_expr]
[HAVING group_condition] <--그룹을 제한
[ORDER BY column];example)
select job_id, SUM(salary) "Payroll"
from employees
where job_id not like '%REP%'
group by job_id
having SUM(salary) > 13000
order by SUM(salary)==> job_id에 REP이 포함된 행들은 제외시킨 후, job_id로 그룹해서, 그룹내 salary총합이 13k 이상인 그룹만 총합의 값 순서대로 정렬한다.
4. Sub-Query
다른 select절에 삽입된 select문이다.
table 자체의 데이터에 종속된 조건을 사용해서 테이블에서 행을 선택할 때 유용하다.
여러 SQL절에 서브쿼리를 포함시킬 수 있다.
sub query는 중첩 select문, 하위 select문, 내부 select문이라고도 함.
일반적으로 subquery가 먼저 진행 된 후, 그 결과를 사용하여 메인 쿼리에 대한 결과를 찾음.
-syntax
sub query는 괄호로 묶는다.비교조건의 오른쪽에 sub query를 넣는다.
sub query의 order by절은 top-N 분석을 수행하지 않을 경우에는 필요X
-2가지 종류:
단일 행 sub query(내부 select문에서 한 행만 반환하는 query)<- 단일 행 연산자 사용
다중 행 sub query(내부 select문에서 여러 행을 반환하는 query)<- 다중 행 연산자 사용
-단일행 sub query:
Sub query 사용 cases:
sub query에서 그룹함수 사용
where절, having절에 sub query 사용
-sub query 사용시 주의할 점:
단일 행 sub query에 대해 여러 행이 반환되는 case
내부 query에서 행을 반환하지 않는 경우 (내부 sub query의 문법은 맞지만, 찾은 값이 존재하지않아서 main query로 반환할게 없다)
-다중행 sub query:
연산자: IN, ANY, ALL
IN: 목록에 있는 임의의 멤버와 동일
ANY or SOME: 값을 sub query에 의해 반환된 각 값과 비교
<ANY는 최대값보다 작음을, >ANY는 최소값보다 큼을, =ANY는 IN과 동일함.
ALL: 값을 sub query에 의해 반환된 모든 값과 비교 >ALL 최대값보다 큼을, <ALL은 최소값보다 작음을 나타냄.
5.데이터 조작
INSERT
-column이름을 명시하는것을 skip하면,반드시 column 모두를, 정해진 순서대로 값을 넣어야함.
insert into dept values(10, 'aaa', 100, 1700);
-columnm이름을 명시하면, 원하는것만, 원하는 순서로 값을 넣을 수 있음.
insert into dept (location_id,department_name, manager_id)
values(1900,'bbb',101);
-Java에서 column순서가 DB와 다를때에 위와같이 match만 해주면 입력 가능.
example1) 한개의 행 삽입:
insert into emp
values (101,'Gary','Heinz','fsd@gmail.com','212-321-2313',
TO_DATE('20/03/27','YY/MM/DD'),'CLERK',30000,null,100,70);
insert into emp
values (101,'Lary','Hall','uyt@gmail.com','212-801-9813',sysdate,
'CLERK',35000,null,100,90);example2) 다른 테이블에서 여러 행 복사 한번에 가능:
insert into dept
select * from departments where department_id>10 and
department_id<>80;UPDATE
example)
update emp
set job_id = (select job_id from employees where employee_id = 205),
salary = (select salary from employees where employee_id = 205)
where employee_id = 114;==> employee_id가 114인 행만 job_id와 salary를 위 조건에 맞게 수정한다.
-update시, 무결성 제약 조건 오류에 주의해야함!!
-현 table의 parent tabl에서 parent key가 존재하는 경우에만 정상적으로 업데이트 가능
--해당 table의 제약조건을 확인하고싶다면 왼쪽 table hierarchy에서 table을 더블클릭하면
-table의 정보를 조회하는 곳에서 제약조건 tab을 조회하면 됨.
-OR 쿼리 파일 내에서도 user constraints라는 keyword로 조회가 가능함.
*질문: table전체를 복사한 복사본 table에도 똑같은 제약조건이 주어지나?
==>NOPE 주어지지 않음. 그냥 복사본일뿐...
DELETE
-DELETE시에도, 무결성 제약 조건 오류에 주의해야함!!
-다른 테이블에서 외래key로 사용되는 key를 포함는 행은 삭제할 수 없다.
-자식 table과의 외래-연결을 없애야지 삭제 가능
Sub query
-INSERT문에서 서브쿼리 사용
-뷰로 insert하는 것
-실제 table이 존재하는 것이 아니라, 여기 서브쿼리문으로 가상 table이 만들어진것임
-뷰는 내요을 읽기위한 목적임. 내용을 insert하기 위함이 아니라서, 여기서 사용되는것은 바람직X
Explicit(명시적) 기본값 사용
example)
create table dept_loc(
dept_id number primary key,
loc_name varchar2(20) default '서울'
);==> 기본값을 '서울'로 설정
insert into dept_loc(dept_id) values(10);
insert into dept_loc values(20, default);
select * from dept_loc;==> default가 반영됨.
데이터 TRANSACTION
-commit: 데이터베이스 쓰기 완료=>내가 변경한 내용이 다른 user에게도 적용 ==> 취소 안됨.
-SQL실행하는 app이 정상종료
-DDL 문장 하나 실행. DDL 문장 하나는 실행 시 commit됨. (삭제와 같은 action)
-DCL 문장 하나 실행
주의사항
-insert 후 commit을 해야, application(java)에서 확인 할 수 있음.
6. 테이블 생성 및 관리
CHAR(x) <-- x로 고정된 문자 데이터 (값 크기를 지정한다)
VARCHAR2(y) <--가변길이 문자 데이터. (크기가 변경될 수 있음)
질문: VARCHAR2에서 y를 지정하는 이유는?
나중에 바꾸더라도 속성에 들어갈 데이터의 사이즈를 지정해야함.
VARCHAR대신 VARCHAR2를 사용함.
테이블 생성:
VARCHAR2(y) <--가변 변수 값 크기가 변경될 수 있음.
CHAR(x) <-- x로 변수의 값 크기를 지정한다.
테이블을 생성할때에 지정할 수 있는 여러가지 option들을 아래 예시를 통해 볼수있다.
예시)
CREATE TABLE NewOrders(
orderid NUMBER,
custid NUMBER NOT NULL,
bookid NUMBER NOT NULL,
saleprice NUMBER DEFAULT 10000 CHECK(saleprice > 1000),
orderdate DATE,
PRIMARY KEY(orderid),
FOREIGN KEY(custid) REFERENCES NewCustomers(custid) ON DELETE CASCADE);
)위 NewOrders 테이블은 orderid 주문번호를 primary key로 가지고있으며, custid는 NewCustomers 테이블의 custid를 참조하는 foreign key(외래키)이다.
외래키를 명시할때에는 반드시 참조되는 부모 테이블(부모 relation)이 존재해야하고, 참조되는 테이블의 기본키(primary key)이여야한다.
그리고 외래키 지정시, 부모테이블의 row(tuple)이 삭제되면 이를 참조했던 자식 테이블의 해당 tuples도 함께 삭제하는지 여부를 (ON DELETE CASCADE "연쇄 삭제") 키워드로 설정할 수 있다. 예시의 경우에는 만약 NewCustomers 테이블의 custid=x가 삭제된다면, 참조하는 NewOrders테이블에서 custid=x에 해당되는 tuples이 연쇄삭제된다.
또는 ON DELETE CASCADE대신, ON DELETE SET NULL 키워드를 사용해서 연쇄삭제는 아니지만, 자식 테이블의 해당 tuples에 null값을 주입하는 방법도 있다.
그리고 saleprice는 default (기본값)이 10000로 설정되어 만약 saleprice의 값이 비어있다면 자동으로 10000이 입력된다. 만약 saleprice에 값이 입력된다면, 반드시 1000이상의 숫자여야한다.
ALTER TABLE
drop column절로는 한번에 한 열만 삭제할 수 있음.
안사용하는 여러 column을 set unused로 지정한 후, drop unusesd columns로 여러개를 한번에 삭제할 수 있다.
unused로 지정되면 없는것처럼 조회됨. (DESC로 조회시)
주의사항
drop이나 unused 모두 해당 열이 외래키로 지정이 되어있으면 적용이 안된다. 제약조건을 disable해야 적용 가능함.
drop은 DDL문이기때문에, 실행 후 복구 할 수 없음.
References
1. 오라클로 배우는 데이터베이스 개론과 실습(IT COOKBOOK 한빛아카데미) -박우창, 남송휘, 이현룡 지음
