day21 - 0823
날짜 자료형 Date
- 데이터에 시간을 설정할 때 사용하는 형태
- 년, 월, 일, 시, 분, 초(.0 단위)까지 저장 가능
- 문자열과 변환이 가능 (to_char(), to_date() 함수)
- 현재 시간을 자동으로 계산해주는 객체가 존재한다 (sysdate)
- date는 계산이 가능하다 (date + 1은 하루 뒤를 의미한다)
Date 자료형을 쓰는 테이블 컬럼 생성 및 값 입력
1) to_date('문자열', '날짜형식') : 문자열을 날짜 형식으로 변경
2) sysdate : 현재 시간 입력
-- 테이블 생성
create table 테이블명(
컬럼명 date
);
-- 1) to_date('문자열', '날짜형식)
insert into 테이블명(컬럼명) values(to_date('문자열', '날짜형식'));
-- ex)
insert into 테이블명(컬럼명) values(to_date('2022-08-28', 'yyyy-mm-dd'));
-- 2) sysdate
-- 시스템에 설정된 현재 시간 입력
insert into 테이블명(컬럼명) values(sysdate);
-- 시스템에 설정된 현재 시간의 5일 뒤
insert into 테이블명(컬럼명) values(sysdate + 5);
-- 시스템에 설정된 현재 시간의 5분 뒤
insert into 테이블명(컬럼명) values(sysdate + 5/24/60);
- 날짜형식
| yyyy | 년도 |
| mm | 월 |
| dd | 일 |
| hh | 시간(12시간제) |
| hh24 | 시간(24시간제) |
| mi | 분 |
| ss | 초 |
** to_char(컬럼명, 형식) : 날짜, 숫자 등의 값을 원하는 형식의 문자열로 변경
- to_char(컬럼명, '날짜형식')
select to_char(컬럼명, 'yyyy-mm-dd hh24:mi:ss') from 테이블명;
날짜의 요소 추출 함수 extract
extract(날짜 요소 from 컬럼명)- date 자료형 컬럼에서 해당 날짜 요소(년, 월, 일 등)만 추출
| year | 년 |
| month | 월 |
| day | 일 |
| hour | 시 |
| minute | 분 |
| second | 초 |
기본키 (Primary Key)
- 테이블 생성시 부여할 수 있는 조건 중 하나
- 테이블을 대표하는 항목
- not null + unique의 기능 (주로 sequence로 값을 부여한다)
- 테이블당 하나만 존재할 수 있다 (** 복합키 : 여러 테이블컬럼을 묶어서 primary key로 정할 수 있다)
조회(select)
- 저장되어있는 데이터를 가져오도록 하는 명령
select 컬럼명 from 테이블명;- 컬렴명에 * 입력 시 : 모든 컬럼 불러오기
조회 조건 설정 where
select 컬럼명 from 테이블명 where 조건;- 컬렴명에 * 입력 시 : 해당하는 조건을 만족하는 모든 컬럼 불러오기
1) 숫자 조건 설정
-- (Q) 가격(price)이 2000원 이하의 상품만 조회
select * from product where price <= 2000;
-- (Q) 가격(price)이 1000원 이상 2000원 이하인 상품만 조회
select * from product where price >= 1000 and price <= 2000;
select * from product where price between 1000 and 2000;
-- (Q) 가격(price)이 1000원인 상품
select * from product where price = 1000;
-- (Q) 가격(price)이 1000원이 아닌 상품
select * from product where price != 1000;
select * from product where price <> 1000;
2) 문자열 조건 설정
-- (Q) 종류(type)가 아이스크림 또는 과자인 데이터만 조회
-- (1) type = '아이스크림' or type = '과자'
select * from product where type = '아이스크림' or type = '과자';
-- (2) type in ('아이스크림', '과자')
select * from product where type in ('아이스크림', '과자');
★★ 문자열의 유사 검색 조건 설정
- like를 이용한 유사 검색
-- like를 이용한 유사 검색
select 컬럼명 from 테이블명 where 컬럼명 like 문자열;
-- (Q) 이름(name)이 '바'로 시작하는 모든(*) 상품 조회
-- like는 %를 "있어도 되고 없어도 되는 값"으로 인식한다
select * from product where name like '바%';- instr를 이용한 유사 검색
instr(컬럼명, 찾는문자열) : 찾는 문자열의 index(숫자)를 반환
select 컬럼명 from 테이블명 where instr(컬럼명, 찾는문자열) > 0;
-- (Q) 이름(name)이 '바'로 시작하는 모든(*) 상품 조회
-- instr은 지정한 글자가 항목의 몇 번째 위치에 시작하는지 반환 (index는 1부터 시작 <-> Java는 0)
select * from product where instr(name, '바') > 0;
ex 1) 이름(name)이 '바'로 시작하는 모든(*) 상품 조회
-- like 사용
select * from product where name like '바%';
-- instr 사용
select * from product where instr(name, '바') = 1;
ex 2) 이름(name)에 '바'가 포함된 모든(*) 상품 조회
-- like 사용
select * from product where name like '%바%';
-- instr 사용
select * from product where instr(name, '바') > 0;
ex 3) 이름(name)이 '바'로 끝나는 모든(*) 상품 조회
-- like 사용
select * from product where name like '%바';
-- instr 사용
select * from product where instr (name, '바') = length(name);
** 첫 글자에 대한 유사 검색에 한해서만 like를 사용하는 방식이 좋고 나머지는 instr을 사용하는 방식이 좋다
3) 날짜 조건 설정
-- (Q) 제조년도(made)에서 년도가 2020년인 상품을 조회
select * from product where extract(year from made) = 2020;
-- (Q) 제조년도(made)에서 월이 여름(6,7,8월)에 생산한 제품을 조회
-- 1) 월에 대한 extract 함수의 결과에 6, 7, 8 중 하나가 포함(in)되어 있을 때
select * from product where extract(month from made) in (6, 7, 8);
-- 2) 월에 대한 extract 함수 결과가 6 또는 7 또는 8일 때
select * from product where extract(month from made) = 6
or extract(month from made) = 7
or extract(month from made) = 8;
-- 3) 월에 대한 extract 함수 결과가 6에서 8 사이일 때 (between)
select * from product where extract(month from made) between 6 and 8;
-- 4) 유사 검색
select * from product where made like '%/06/%' or made like '%/07/%' or made like '%/08/%';
-- 5) 제조년도(made)의 월(mm)을 문자열로 바꾼 값에 06, 07, 08 중 하나가 포함(in)되어 있을 때
select * from product where to_char(made, 'mm') in('06', '07', '08');
** 특정 날짜 범위의 데이터 조회
ex) 생산년도(made)가 2019년 6월 1일부터 2019년 8월 31일 사이인 데이터만 조회할 때
- 2019년 8월 31일은 23시 59분 59초까지 조회할 것
select * from product where made between to_date('2019-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2019-08-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
정렬 (Order)
- 조회한 데이터를 원하는 기준에 맞게 재배치
- 정렬은 항상 SQL문 마지막 부분에 나와야 한다 (먼저 정렬할 데이터가 정해져야 하기 때문)
select 컬럼명 from 테이블명 order by [asc/desc];| 오름차순(asc, ascending) | 내림차순(desc, descending) | |
| 숫자 | 낮은 숫자에서 높은 숫자 순으로 정렬 | 높은 숫자에서 낮은 숫자 순으로 정렬 |
| 이름 | 사전의 순으로 정렬 | 사전의 역순으로 정렬 |
| 날짜 | 오래된 날짜에서 최산 날짜 순으로 정렬 | 최신 날짜에서 오래된 날짜 순으로 정렬 |
함수(Function)
듀얼 테이블 (dual table)
- 오라클 자체에서 제공하는 내장 테이블
- SYS 사용자가 소유하지만 모든 사용자가 사용할 수 있다
- 오직 하나의 컬럼을 가지고 있는 dummy 테이블
- 테이블을 생성할 필요 없이 함수의 결과값을 반환받을 수 있다
select 항목명 from dual;
- 단일 행 함수 (Signle-row function)
1) 문자 관련
| INTICAP(문자열) | 입력 문자열의 첫 글자만 대문자로 변환 |
| LOWER(문자열) | 입력 문자열을 전부 소문자로 변환 |
| UPPER(문자열) | 입력 문자열을 전부 대문자로 변환 |
| LENGTH(문자열) | 입력 문자열의 길이를 출력 |
| LENGTHB(문자열) | 입력 문자열의 길이(byte)를 출력 |
| CONCAT(문자1, 문자2) | 두 입력 문자열을 결합하여 출력 |
| SUBSTR(문자열, 시작위치, 길이) | 입력 문자열의 시작위치부터 길이만큼 문자열 추출 |
| SUBSTRB(문자열, 시작위치, 길이) | 입력 문자열의 시작위치(byte)부터 길이(byte)만큼 문자열 추출 |
| INSTR(문자열, 문자) | 입력 문자열에서 특정 문자의 INDEX 추출 |
| INSTRB(문자열, 문자) | 입력 문자열에서 특정 문자의 INDEX(byte) 추출 |
| LPAD(문자열, 문자) | 입력 문자열의 왼쪽에 특정 문자를 결합 |
| RPAD(문자열, 문자) | 입력 문자열의 오른쪽에 특정 문자를 결합 |
| LTRIM(문자열, 문자) | 입력 문자열에서 왼쪽의 특정 문자를 삭제 |
| RTRIM(문자열, 문자) | 입력 문자열에서 오른쪽의 특정 문자를 삭제 |
| REPLACE(문자열, 바꿀문자, 치환 문자) | 입력 문자열에서 특정 문자를 다른 문자로 치환 |
| REGEXP_REPLACE | 입력 문자열에서 특정 패턴을 찾아 치환 |
| REGEXP_INSTR | 입력 문자열에서 특정 패턴의 시작 INDEX 반환 |
| REGEXP_SUBSTR | 입력 문자열에서 특정 패턴을 찾아 반환 |
| REGEXP_LIKE | 입력 문자열에서 특정 패턴을 찾아 반환 |
| REGEXP_COUNT | 입력 문자열에서 특정 패턴의 횟수를 반환 |
2) 숫자 관련
| ROUND(숫자, 자릿수) | 주어진 숫자를 특정 자릿수 이하에서 반올림 |
| TRUNC(숫자, 자릿수) | 주어진 숫자를 특정 자릿수 이하에서 버림 |
| MOD(숫자, 나눌 숫자) | 주어진 숫자를 특정 숫자로 나눈 후 나머지 출력 |
| CEIL(숫자) | 주어진 숫자와 가장 근접한 큰 정수 출력 |
| FLOOR(숫자) | 주어진 숫자와 가장 근접한 작은 정수 출력 |
| POWER(숫자, 승) | 주어진 숫자의 특정 숫자 승(power)을 출력 |
** 형변환 함수
| CHR(숫자) | 입력 숫자에 해당하는 ASCII 코드 번호 문자를 출력 |
| ASCII(문자) | 입력 문자에 해당하는 ASCII 코드 번호 출력 |
3) 집계 함수
| SUM(컬럼명) | 해당 컬럼의 합계 출력 |
| AVG(컬럼명) | 해당 컬럼의 평균 출력 |
| MAX(컬럼명) | 해당 컬럼의 최대값 출력 |
| MIN(컬럼명) | 해당 컬럼의 최소값 출력 |
| COUNT(컬럼명) | 해당 컬럼의 데이터 갯수 출력 (NULL값 제외) |
Sub Query
- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
주의사항
1) 서브 쿼리를 괄호()로 감싸서 사용
2) 서브 쿼리는 단일 행 또는 복수행 비교 연산자와 함께 사용할 수 있다
3) 서브 쿼리에서는 정렬(ORDER BY)을 사용할 수없다
단일 행 서브 쿼리
- 단일행 비교 연산자(=, >, <, >=, <=, <>)와 함께 사용
- 서브 쿼리의 결과 행이 1행 이하일 것
ex) product 테이블에서 제조일자(made)가 가장 최신인 상품의 모든 정보(*) 조회
-- 1) 서브 쿼리 작성 : 생산일자(made)가 가장 최신인 행의 값
select max(made) from product;
-- 2) 단일 행 서브 쿼리 : 생산일자(made)가 가장 최신인 상품의 모든 정보(*)
select * from product where made = (select max(made) from product);
복수행 서브 쿼리
- 복수행 비교 연산자(IN, ALL, ANY, SOME)과 함께 사용
- 서브 쿼리의 결과 행이 1행을 초과해도 상관 없음
Top N Query
- 결과 집합의 전체 내용 중 일부분만 가져오는 방식
ROWNUM
- Oracle의 테이블 또는 join된 행 집합에서 행(row)을 선택하는 순서를 나타내는 숫자를 반환
- 임시 테이블인 TMP(Temporary)에 ROWNUM 함수를 이용하여 행 번호를 부여하면 where 조건을 통해 원하는 행만
Top N Query 작성
1) 어떤 조건으로 조회된 결과 테이블에 ROWNUM 함수를 사용하여 행 번호를 부여하고 TMP(임시 테이블)로 명명
2) ROWNUM에 의해 부여된 행 번호에 대해 where로 조건을 부여하여 원하는 범위의 행만 불러오기
ex) 2020년에 만든 상품(product) 중에서 가장 최근에 만든 상품 3개를 조회
-- 1) 생산일자(made) 내림차순 정렬
select * from product order by made desc;
-- 2) 정렬된 테이블에 rn이라는 이름의 rownum을 부여한 후 이 테이블을 TMP(임시테이블)로 명명하고 모든 행 불러오기
select TMP.*, rownum rn from (select * from product order by made desc)TMP;
-- 3) TMP에서 rownum이 3 이하인 행만 불러오기
select * from (select TMP.*, rownum rn from (select * from product order by made desc)TMP) where rn <= 3;