목차 1. 내장함수 01. 내장함수(SQL 내장함수, NULL 값 처리, ROWNUM)
2. 부속질의
3. 뷰
4. 인덱스
1. 내장함수 <-> 사용자정의 함수(이건 안할 것)
차이 : 누가 만들었냐
내장함수 : 오라클 개발자들이 이미 만들어놓은 것을 우리가 씀!
오라클의 함수 1. 함수(=function) : 반환값(return)이 있음
2. 프로시저(=procedure) : 반환값(return)이 없음
자바/파이선/자바스크립트에서는 method(메서드)라고 함 : 함수, 프로시저 구분하지 않음
sum(), avg(), count(), max(), min() 등 -> 함수
2. 부속질의(Sub Query) : select from where절이 메인절에 하나 더 추가 된 것
위치에 따라 3개로 나뉨 - select 뒤 (select from where절) -> 스칼라 (값) 부속질의
- from 뒤 (select from where절) -> 인라인 부속질의
- where 뒤 (select from where절) -> 부속질의 : 가장 많이 씀
3. 뷰 : view(객체) -> table과 비슷함
view -> 보여준다 -> 조회, 검색 : select 용도로 주로 사용 -> 값이 바뀌었는지 적용 됐는지 확인
4. 인덱스 : index(객체) -> 목차
-> 테이블에 있는 데이터를 빨리 찾기 위한 용도로 차례대로/순서대로 숫자 부여(sequence)
-> 테이블 앞쪽에 임의의 숫자 부여 : index를 응용해서 정렬이 안되면 order by 씀
ex) 계좌DB(데이터가 엄청 많은)에서 "홍길동" 검색하려고 order by를 쓰면 동명이인도 많고
계좌별로도 정렬 -> 오래걸림 : order by는 상대적인 느린 검색! -> index를 쓰면 더 빨라짐
-> data가 더 많아질수록 진가를 발휘
CREATE TABLE(객체) user
CREATE USER(객체) madang
01. 내장함수
SQL 내장 함수
-> 함수 : 값을 입력 -> 결과 값을 돌려줌
ex) a 입력 -> a+7 -> 10 반환값 : a+7에 f1이라는 이름 붙임 -> b = f1(3) = 10(반환값)
ex) sum(price) -> 이거 하나 자체가 값이 됨
ex) count(custid) -> 회원의 수가 값으로 나옴 : 자체가 값이 된다 -> 반환값, return값이 있다.
SQL 주요 내장 함수
1.1 수학 함수 -> 기본 문장은 무조건
Select
From ;
-> 여기서는 테이블이 없으므로 From Dual;로 해줌 -> 그러면 테이블 없이도 가능!
- ABS 함수 : 절댓값을 구하는 함수
ex) -78과, +78의 절댓값을 구하시오.
select abs(-78), abs(+78)
from dual;
- ROUND 함수 : 반올림한 값을 구하는 함수
ex) 4.875를 소수 쳇째 자리까지 반올림한 값을 구하시오
select round(4.875, 1)
from dual;
- 숫자 함수의 연산
ex) 고객별 평균 주문 금액을 백 원 단위로 반올림한 값을 구하시오
select custid as 고객번호, round(avg(saleprice),-2) as 평균주문금액
from orders
group by custid;
1.2 문자 함수
- REPLACE : 문자열을 치환하는 함수
ex) 도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오.
select bookid, replace(bookname, '야구', '농구'), publisher, price
from book;
- LENGHT : 글자의 수를 세어주는 함수 (단위가 바이트(byte)가 아닌 문자 단위)
ex) 굿스포츠에서 출판한 도서의 제목과 제목의 글자 수를 확인하시오.
select bookname "제목", length(bookname) "글자수", lengthb(bookname) as "바이트수"
from book
where publisher = '굿스포츠';
- SUBSTR : 지정한 길이만큼의 문자열을 반환하는 함수
ex) 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.
select SUBSTR(name,1, 1) "성", count(*) "인원"
from customer
group by substr(name, 1, 1);
1.3 날짜, 시간 함수
ex) 마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오.
select orderid "주문번호", orderdate "주문일", orderdate+10 "확정일"
from orders;
- TO_DATE : 문자형으로 저장된 날짜를 날짜형으로 변환하는 함수
- TO_CHAR : 날짜형을 문자형으로 변환하는 함수
ex) 마당서점이 2014년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오. 단 주문일은 'yyyy-mm-dd 요일' 형태로 표시한다.
select orderid "주문번호", TO_CHAR(orderdate, 'yyyy-mm-dd dy') "주문일", custid "고객번호", bookid "도서번호"
from orders
where orderdate = '14-07-07';
-> 주문일은 이미 DATE형식이므로 문자 형식으로 내가 표현하고 싶은대로 변환
select orderid "주문번호", TO_CHAR(orderdate, 'yyyy-mm-dd dy') "주문일", custid "고객번호", bookid "도서번호"
from orders
where orderdate = TO_DATE('20140707', 'yyyymmdd');
- SYSDATETIME : 오라클의 현재 날짜와 시간을 반환하는 함수
- SYSTIMESTAMP : 현재 날짜, 시간과 함께 초 이하의 시간과 서버의 TIMEZONE까지 출력함
ex) DBMS 서버에 설정된 현재 시간과 오늘 날짜를 확인하시오.
select sysdate from dual; -> 20/12/18
select TO_Char(sysdate, 'yyyy/mm/dd day hh24:mi:ss') "날짜시간" from dual;
-> 2020/12/18 금요일 10:48:15
[예제]
다음 내장 함수의 결과를 적으시오
1) ABS(-15) :
select abs(-15) from dual; = 15
2) CEIL(15.7) :
select ceil(15.7) from dual; = 16
3) COS(3.14159) :
select COS(3.14159) from dual; = -0.99999999999647923060461239250850048324
4) FLOOR(15.7) :
select FLOOR(15.7) from dual; = 15
5) LOG(10, 100) :
select log(10, 100) from dual; -> 2
6) MOD(11, 4) :
select mod(11, 4) from dual; -> 3
7) POWER(3, 2) :
select power(3, 2) from dual; -> 9
8) ROUND(15.7) :
select round(15.7) from dual; -> 16
9) SIGN(-15) :
select sign(15) from dual; -> 1
10) TRUNC(15.7) :
select trunc(15.7) from dual; -> 15
11) CHR(67) : 정수 아스키 코드를 문자로 반환
select chr(67) from dual; -> C
12) CONCAT('HAPPY', 'Birthday') :
select concat('HAPPY', 'Birthday') from dual; -> HAPPYBirthday
13) LOWER('Birthday') :
select lower('Birthday') from dual; -> birthday
14) LPAD('Page 1', 15, '*.'); 대상 문자열의 왼쪽부터 지정한 자리수까지 지정한 문자로 채움
select LPAD('Page 1', 15, '*.') from dual; -> *.*.*.*.*Page 1
15) LTRIM('Page 1', 'ae') :
select LTRIM('Page 1', 'ae') from dual; -> Page 1
select LTRIM('Page 1', 'P') from dual; -> age 1
select LTRIM('Page 1', 'Pa') from dual; -> ge 1
16) REPLACE('JACK', 'J', 'BL') :
select REPLACE('JACK', 'J', 'BL') from dual; -> BLACK
17) RPAD('Page 1', 15, '*.') :
select RPAD('Page 1', 15, '*.') from dual;
-- 예상 : Page 1*.*.*.*.* -> 정답
18) RTRIM('Page 1', 'ae') :
select RTRIM('Page 1', 'ae') from dual; -> Page 1
select RTRIM('Page 1', 'e 1') from dual; -> Pag
19) SUBSTR('ABCDEFG', 3, 4) : -> substring = 부분문자
select SUBSTR('ABCDEFG', 3, 4) from dual; -> CDEF
20) TRIM(LEADING 0 FROM '00AA00')
: 대상 문자열의 양쪽에서 지정된 문자를 삭제(문자열만 넣으면 기본값으로 공백 제거)
TRIM(c FROM s) -> ex) TRIM( '=' FROM '= =>BROWINING<= =') -> >BROWNING<
select TRIM(leading 0 from '00AA00') from dual;
select TRIM(' > 나라 사랑 < ') from dual; -> > 나라 사랑 <
select TRIM(0 from '00AA00') from dual; -> AA
21) UPPER('Birthday') :
select UPPER('Birthday') from dual; -> BIRTHDAY
22) ASCII('A') :
select ASCII('A') from dual; -> 65
23) INSTR('CORPORATE FLOOR','OR', 3, 2) :
select INSTR('CORPORATE FLOOR', 'OR', 3, 2) from dual; -> 14
-> INSTR(s1, s2, n, k) -> 문자열 s1 중 n번째 문자부터 시작하여 찾고자하는 문자열 s2가 k 번째 나타나는 문자의 열 위치 반환
-> 문자열 CORPORATE FLOOR에서 3번째 문자(R)부터 시작해서 찾고자하는 문자열 'OR'이 2번째로 나타나는 문자의 열 위치 반환
-> 3번째 분자부터 OR이 2번째 나오는 지점인 마지막 FLOOR에서의 OR이 14번째에 나타남
24) LENGTH('Birthday') :
select length('Birthday') from dual; -> 8
25) ADD_MONTHS('14/05/21', 1) :
select ADD_MONTHS('14/05/21', 1) from dual; -> 14/06/21
26) LAST_DAY(SYSDATE) : -> date형의 날짜에서 달의 마지막 날을 반환
select LAST_DAY(SYSDATE) from dual; -> 20/12/31 : 이번 달의 마지막 날 반환
select LAST_DAY('2020/11/08') from dual; -> 20/11/30
select LAST_DAY(TO_DATE('2020/11/08')) from dual;
select LAST_DAY(TO_DATE('2020/11/08', 'yyyy/mm/dd')) from dual;
27) NEXT_DAY(SYSDATE, '월요일') : SYSDATE를 기준으로 그 다움 월요일에 해당하는 날짜
select NEXT_DAY(SYSDATE, '월요일') from dual; -> 20/12/21 다음 월요일 날짜
select NEXT_DAY(SYSDATE, '토요일') from dual; -> 20/12/19 다음 토요일 날짜
select NEXT_DAY(SYSDATE, '금요일') from dual; -> 20/12/25 오늘이 금요일이므로 그 다음 금요일의 날짜
28) ROUND(SYSDATE) :
select ROUND(SYSDATE) from dual; -> 20/12/18
select ROUND(TO_DATE('2020-12-18 23:59', 'YYYY-MM-DD HH24:mi')) from dual;
-> 2020-12-19 23:59 반올림하면 2020-12-20일이 됨
select ROUND(TO_DATE('2020-12-31 11:30', 'YYYY-MM-DD HH24:mi')) from dual;
-> 20/12/31
select ROUND(TO_DATE('2020-12-31 12:30', 'YYYY-MM-DD HH24:mi')) from dual;
-> 21/01/01 시간을 기준으로 반올림 하는 듯
29) SYSDATE :
slect sysdate from dual; -> 20/12/18
30) TO_CHAR(SYSDATE) :
select TO_CHAR(SYSDATE) from dual; -> 20/12/18
select TO_CHAR(SYSDATE, 'yymmdd') from dual; -> 201218
-> 날짜형 데이터 20/12/18을 1201218의 문자형 데이터로 변경
select TO_DATE(SYSDATE, 'yyyy-mm-dd') from dual; -> 20/12/18
31) TO_CHAR(123) :
select TO_CHAR(123) from dual; -> 123 -> 숫자 123(오른쪽정렬)을 문자 123으로 변환(왼쪽 정렬됨)
32) TO_DATE('12 05 2014', 'DD MM YYYY') :
select TO_DATE('12 05 2014', 'DD MM YYYY') from dual;
-> 14/05/12 문자 12 05 2014를 날짜로 변환(2014년 05월 12일)
select TO_DATE('12 05 2014', 'MM DD YYYY') from dual;
-> 14/12/05 문자 12 05 2014를 날자로 변환(2014년 12월 05일)
33) TO_NUMBER('12.3') :
select TO_NUMBER('12.3') from dual; -> 12.3 : 문자 12.3을 숫자 12.3으로 변환
34) DECODE(1, 1, 'aa', 'bb') : -> 이게뭐지?
select DECODE(1, 1, 'aa', 'bb') from dual; -> aa
select DECODE(1, 2, 'aa', 'bb') from dual; -> bb
select DECODE(2, 1, 'aa', 'bb') from dual; -> bb
select DECODE(2, 2, 'aa', 'bb') from dual; -> aa
35) NULLIF(123, 345) : -> ? 무슨 뜻인가
select NULLIF(123 ,345) from dual; -> 123
36) NVL(NULL, 123) : -> 빈 칸이 있을 경우에 123으로 해라(값이 없을 경우에 123으로 해라)
select NVL(NULL, 123) from dual; -> 123
select NVL(NULL, 456) from dual; -> 456
select NVL(NULL, '-') from dual; -> - : 빈 칸이 있을 경우 - 로 처리해라
- 반환함수 : 숫자 -> 문자로 문자 -> 숫자로 바꿔 줌
ex) 문자 1을 숫자 1로, 숫자 1을 문자 1로 변환
아스키코드 : 기본 -> american standard code : 알파벳만 씀
-> 확장 -> 유니코드 : 확장버전 -> 한글도 가능
- Code : IT에서 말하는 코드는 '부호'
-> 옛날 모스부호도 코드
컴퓨터는 'a'라는 글자를 다룰 수 없음(0, 1의 이진수만 사용) -> 문자에 코드 부여 ex) a는 1, b는 2, c는 3 .. 처럼
아스키코드, 유니코드에서는 a부터 순서대로 96, 97 .. 의 코드를 부여함
-> 00000000 | 00000000 | 00000000 | 00000000 -> 이진수(binary)만 가능 -> 0, 1을 표현할 수 있는 한 자리 : bit
8비트(2^3) 8비트 8비트 8비트 -> 32 비트나 됨
-> 22222222 = 2^8 = 1byte = 8bit로 하자!
8비트 | 8비트 | 8비트 | 8비트
ex) 글자 bada의 코드가 예를 들어 2141(10진수)라면 -> 2진수로 바꾸면
00000010 | 00000001 | 00000100 | 00000001
-> 1000 : 10^3 컴퓨터는 10진수 X, 사람에게는 익숙
2. NULL 값 처리
- 초기값
- 0 : 정수, 0.0 : 실수
- '' : 없음
- ' ' : 공백문자
float a;
실수형 변수명 변수a에는 실수가 들어갈 것임
int a;
정수형
person a;
자료형 -> int, float처럼 원래 있던게 아니고 내가 만든 자료형임 -> 숫자도, 문자도 아님... (a에 뭘 넣어야 하는가?)
person -> 객체
ex) height h1; -> h에는 175.5 등으로 실수형으로 들어감
person a1; -> a1는 NULL -> 참조가 없다.
- NULL 값이란?
: 아직 지정되지 않은 값 -> 처음부터 지정되지 않음 -> 값 자체가 없음
NULL 값은 '0', '(빈 문자)', '(공백)' 등과 다른 특별한 값
NULL 값은 비교 연산자로 비교가 불가능함
NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환됨
- 집계 함수를 사용할 때 주의할 점
NULL+숫자 연산의 결과는 NULL
집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠짐
해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT 함수의 결과는 0
- NULL 값에 대한 연산과 집계 함수
1) Mybook 테이블 생성하기
CREATE TABLE Mybook(
bookid NUMBER,
price NUMBER);
또는
2) Mybook 테이블에 값 넣어주기
INSERT INTO Mybook(bookid, price)
values(1, 10000);
INSERT INTO Mybook(bookid, price)
values(2, 20000);
INSERT INTO Mybook(bookid)
values(3);
또는
-> 하고나면 변경사항 커밋!!!
select price+100
from mybook
where bookid =3; -> 결과 : (null)
-> 3번의 price가 이면 계산이 되어 나올텐데 0이 아닌 아예 입력되지 않아 계산 불가능
select sum(price), avg(price), count(*), count(price)
from mybook;
NULL 값을 확인하는 방법 - IS NULL, IS NOT NULL
- NULL 값을 찾을 때는 '=' 연산자가 아닌 'IS NULL'을 사용
- NULL이 아닌 값을 찾을 때는 '<>' 연산자가 아닌 IS NOT NULL 사용
NVL : NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
NVL(속성, 값) -> 속성 값이 NULL이면 '값'으로 대치한다
ex) 이름, 전화번호가 포함된 고객목록을 보이시오. 단, 전화번호가 없는 고객은 '연락처없음'으로 표시한다.
select name "이름", NVL(phone, '연락처없음') "전화번호"
from customer;
3. ROWNUM : 실제로 활용을 보통 안해도 됨(row - 행에 번호를 부여함 -> index와 다름)
-> 입력(insert) 되는 순서대로 자동적으로 번호 부여
-> 이걸 사용하지 말고 내가 순번을 명시해서 넣어서 사용 : 이것이 트리거, 시퀀스
- 내장 함수는 아니지만 자주 사용되는 문법
- 오라클에서 내부적으로 생성되는 가상 컬럼으로 SQL 조회 결과의 순번을 나타냄
- 자료를 일부분만 확인하여 처리할 때 유용
ex) 고객 목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오.
select rownum "순번", custid, name, phone
from customer
where rownum <= 2;
Mybook 테이블을 생성하고 NULL에 관한 다음 SQL 문에 답하시오. 질의의 결과를 보면서 NULL에 대한 개념을 정리해보시오.
(1) select * from mybook;
(2) select bookid, NVL(price, 0) from mybook; -> bookid3번의 price가 0으로 표시될 것
(3) select * from mybook where price is null; -> bookid 3번의 모든 내용이 출력될 것
(4) select * from mybook where price=' '; -> null과 공백은 다르므로 아무것도 출력되지 않을 것
(5) select bookid, price+100 from mybook; -> 3번 책은 (null)값, 나머지는 +100된 price 값이 나올 것
(6) select sum(price), avg(price), count(*) from mybook where bookid >= 4;
-> bookid는 3번까지 있으므로 sum(price) 값은 null, avg(price) 값도 null, count(*)는 셀것이 없으니 0개
(7) select count(*), count(price) from mybook; -> count(*) 총 3개의 책, count(price) price의 값은 2개
(8) select sum(price), avg(price) from mybook;
-> null은 제외하고(아예 모수에서 뺌) 1, 2번 책의 price 더함 : 10000 + 20000 = 30000
-> null을 제외하고(아예 계산에서 뺌) 1, 2번 책의 price의 평균을 구함 (10000+20000)/2 = 15000
-> 만약 null이 아니라 0이었다면 (10000+20000+0)/3 = 10000이 출력되었을 것
ROWNUM에 관한 다음 SQL 문에 답하시오. 데이터는 마당서점 데이터베이스를 이용한다.
(1) select * from book;
(2) select * from book where rownum <= 5; -> 가장 먼저 입력된 책 목록 5개가 나올 것
(3) select * from book where rownum <= 5 order by price;
부속질의 : 메인이 되는 select
from
where 중 어느것 뒤에 오느냐에 따라
select (select ~ from ~ where의 부속질의) -> 스칼라 (값) 부속질의
from
where
select
from (select ~ from ~ where의 부속질의) -> 인라인 부속질의
where
select
from
where (select ~ from ~ where의 부속질의)-> 부속질의 : 가장 많이 씀
↓ 아래는 모두 from 뒤에 부속질의가 왔으므로 인라인 부속질의!
(4)
select *
from (select * from book) b -> as b 와 같다. (select * from book)이 부속절을 b라는 별칭? 으로 요약 한 것!
where rownum <= 5 추후에 b문장 안에 order by 등의 다른 작업을 하거나 간편하게 쓰려고
order by price; 지금은 없어도 됨
(5)
select *
from (select * from book where rownum <= 5) b -> 인라인 뷰
order by price; ㄴ> 가상의 테이블 : 뷰
(6)
select *
from (select * from book where rownum <= 5 order by price) b;
+
select * from book order by price;
-> 이 상태에서
select * from book where rownum <= 5;
-> 결과 확인
그런데 이 경우
select *
from (select * from book order by price)
where rownum <= 5; ㄴ> 이걸 먼저 수행
-> select * from book order by price
위처럼 가격별로 정렬된 상태에서
select *
from (select * from book order by price)
where rownum <= 5;
-> 수행 시
rownum이 이렇게 바뀌고 요렇게 5개가 결과로 나오게 됨
-> 결과 확인
'프로그래밍 > 자바(java) 융합개발자 2차' 카테고리의 다른 글
| [취성패] 자바 배우기 - 6일차 일지(부속질의 - 스칼라, 인라인 뷰, 중첩질의2) (0) | 2020.12.21 |
|---|---|
| [취성패] 자바 배우기 - 5일차 일지 2(부속질의 - 스칼라, 인라인 뷰, 중첩질의1) (0) | 2020.12.18 |
| [취성패] 자바프로그래밍 - 4일차 학습일지(SQL-데이터 정의어) (0) | 2020.12.17 |
| [취성패] 자바프로그래밍 - 3일차 학습일지(SQL-조인, 부속질의) (0) | 2020.12.16 |
| [취성패] 자바 프로그래밍 - 1, 2일차 학습일지(SQL) (0) | 2020.12.15 |