Database
[Database] Ch2.테이블 생성 및 데이터 조작(DML) Ch3.데이터검색 Ch4. Stored Function
OptimizerStart
2025. 5. 4. 23:05
데이터 타입
VARCHAR 가변 문자열 cf.VARCHAR2 는 안씀
CHAR 고정 문자열. 1~ 2자리
NUMBER 부동 소수점 38자리까지
NUMBER(p,s)
- 전체 자리수 (점 미포함) p 자리
- 실수부 개수 s
- 정수부 최대 자릿수 = p – s전체 7자리, 정수는 7 - 2 = 5자리까지만 가능. 2는 소수자리 개수. 정수만 7자리 전체 쓸 수 없음.
- ex. NUMBER(7,2)
- 음수 저장 가능
DATE 날짜.
- 시간 : 연속적, 정수
- DB 저장 시 정수형태로 저장됨. 사람이 읽을 떄만 쪼개서 사용함.
LONG 큰 Text
RAW LONGRAW 큰 Binary
SQL 명령어 종류
Data Retrieve 데이터 검색 조회
- SELECT Read
- DML은 Write만 가능한 것
Data Manipulation Language DDL 데이터 조작
- data를 다루는 명령어, 자료구조를 다루는 기능 ex. CH11 컬렉션과 프레임워크
- INSERT, UPDATE, DELETE Write
Data Definition Language DDL 데이터 정의어
- DB의 구조를 다루는 명령. 자료구조
- CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
insert
- 1개의 행(객체)를 삽입
- cf. update, delete 는 한번에 여러 행(객체) 다룸
- 테이블 설계시 not null 설정된 값은 필수로 값 삽입해야함.
*
- 모든 컬럼
cf. not name 특정 컬럼을 제외하는 명령어는 존재하지 않음
select
- 출력문
- 산술식을 적용한 컬럼에 AS로 이름 붙이기
- [ JAVA ] for 와 print 문
- 사용 연산자
- 산술식, AS, || 컬럼 합성, DISTINCT, * (와일드카드)
for(Row r : S_EMP) print(1);
<=>
select 1 from S_EMP; // 행의 개수 만큼 1이 들어간 채로 출력
스키마 (= 사용자)
- 객체(테이블, 뷰, 시퀀스)가 어느 사용자에게 속해있는지 구분하기 위해 사용.
ex. student.s_emp - 명령어 delete 해도 commit 안하면 데이터는 유지됨.
- 문자열 리터럴 ‘’ 작은 따옴표
join = cartisian product × + project π
alias
- 영어 1글자 쓰는 건 이중 따옴표(””) 사용 안해도됨.
- as 단어 생략 가능
- 대소문자 구별, 두 단어(띄어쓰기 있는 경우)이상이면 이중 따옴표(””) 반드시 작성
- 사용 상황
- select 문에서 산술 연산 수행 시
select name, mailid as E from s_emp; -- O
select name, mailid as "E" from s_emp; -- O
select name, mailid as Email from s_emp; -- O
select name, mailid as Email id from s_emp; -- 오류 발생
select name, mailid as 이메일 from s_emp; -- O
컬럼 합성 ||
- 컬럼들을 합성하여 하나의 컬럼으로 출력. 문자열 컬럼.
- 합성할 때 문자열은 ‘’ 단일 따옴표로 작성해야함. 이중 따옴표 오류남
cf. mysql 에는 없는 연산. concatenate() 함수 이용
select name || ' ' || title as "겹쳐서" from s_emp;
select name || ' ::::' || title as "이름 : 직책"
from s_emp;distinct
- 졍렬 필요함. for문 2번이므로 부담이 큼 튜닝 🟢
- 주의
- distinct 뒤에 오는 모든 컬럼에 중복제거 적용
select distinct title, name from s_emp; -- distinct 는 title, name 모두 중복 제거함.
order by
- 데이터 정렬
- 주의사항
- DISTINCT를 사용한 경우 ORDER BY 절에 지정된 컬럼이 반드시 SELECT 리스트에 포함ORA-01791: SELECT 식이 부적합합니다
- [오류 내용] 01791. 00000 - "not a SELECTed expression"
-- where -- filter
select distinct title from s_emp
where title like '%부장' -- filter java에서는 if(title.endsWith("부장"))
order by 1 desc, 2 asc ;
-- order by name asc; -- 왜 오류나냐?
where
- 필터
- [ JAVA ] if문
- 사용 가능 연산자
- BETWEEN, IN, NOT IN, LIKE, AND, OR
BETWEEN
1000 ≤ X ≤ 2000 등호 양쪽 포함
형변환
수동(직접) 형변환 to_date(’16/12/31’) ⇔ 문자열 → Date타입 자동 형변환 WHERE start_date < ‘16/12/31’ char → Date타입
- 자동 형변환 제공 : 문자열 ↔ 숫자 비교 , 문자열 ↔ 날짜 비교
- ex. 'id' 숫자가 ''로 감싸져서 문자열이면 to_number()를 자동으로 넣어줄까? yes
규칙
- Oracle에서는 자동 형변환이 가능하지만, 데이터베이스마다 설정이 다를 수 있기 때문에 수동 형변환을 해야함.
IN
[ JAVA ] OR
NOT IN 아무것도 일치하지 않는경우
-- 'id' 숫자가 ''로 감싸져서 문자열이면 to_number()를 자동으로 넣어줄까? yes
select * from s_emp
--where id > '20';-- ok
where id > to_number('20');
와일드 카드
- % 0~N개 포함
- _ 1개 포함
- [퀴즈] '17%'
- 유사한것을 찾아야 해서 성능 느림 🟢 튜닝
- %17%, %17 보단 17%빠름. 앞에서 부터 비교해서.
논리 연산자 AND OR
- AND가 OR보다 우선순위 높음
- 규칙 괄호 치기
문자형 함수
- LENGTH(문자값)
cf. lengthb() byte 단위로 길이 출력. exlengthb(’홍길동’) - DB 문자 인코딩
- 저장 바이트는 인코딩이 결정
- UTF-8 한글 1글자 3byte (최신 oracle)
- UTF-16 / EUC -KR 2byte
select length('홍길동') from dual; -- 3
select lengthb('홍길동') from dual;-- 9
LPAD(문자열1, 전체자릿수, 채울문자)
- 왼쪽부터 채울문자를 채움.
RPAD(문자열1, 전체자릿수, 채울문자)
- 오른쪽부터 채울문자를 채움.
화면 출력 폭 차이
전각 문자 1글자를 2글자 차지
- 한글, 한자
반각 1글자 차지
저장 바이트와 출력 바이트 차이
- 저장 바이트 수 차이 ⇒인코딩(Character Set)
- 화면 출력 폭 차이 ⇒전각/반각(East Asian Width), 인코딩도 관여함. 어느 문자표에서 문자를 가져올지 지정.
dual
- dummy table
- 단순 함수를 출력해서 확인해볼 때, 임의로 넣을 테이블
cf.mysql은 select now();
translate(문자값, from대체하고싶은문자 , to대체할결과의문자 )
치환된 결과의 문자 개수 동일
길이
- from = to : OK
- from < b : 짤림
- from > to :OK
select translate('나는이름입니다.', '이름', '개발자') from dual; -- 나는개발입니다. from과 to의 글자가 같아야 치환 됨.
select translate('나는개발자입니다.', '개발자', '이름') from dual; -- ok. 나는이름입니다.
replace(문자값, from바꾸고싶은문자, to바꿀결과문자)
치환된 결과 문자 개수 달라질 수 있음
select replace('Jack and Jul', 'J', 'KUNDOL') from dual; -- KUNDOLack and KUNDOLul. from과 to의 문자 길이가 달라도 됨.
숫자형 함수
오라클에서 NUMBER 타입은 불필요한 0을 생략해서 보여줌. ex.16.112000 → 16.112
ROUND(숫자값, a)
- a > 0 소수점 a자리 기준으로 반올림. 즉, a +1 자리에서 반올림
- a = 1, 소수점 첫번째 자리 기준으로 반올림, 소수점 두번째자리에서 반올림후 소수점 첫번째 자리까지만 남게 만듦
- a = 0 소수 첫째 자리에서 반올림. 정수부만 존재
- a < 0 정수부에서 a자리 기준으로 a + 1 자리에서 반올림
- a = -1, 10의 자리 기준으로 일의자리 반올림
select round(355.165,4) from dual; -- 355.165. 소수점 넷째자리에서 반올림후 줌. 없으므로 0을 반올림하는듯 1
select round(355.166,4) from dual; -- 다섯째 자리에서 반올림하여 4자리까지 돌려주는데, 넷째, 다섯째 없어서 0은 출력 안함.
select round(32156.96674,3) from dual; -- 32156.967
select round(32156.96674,0) from dual; -- 32157
select round(32156.96674,-1) from dual; -- 1의 자리를 보고 10의 자리를 올리거나 내림 . 1의 자리에서 반올림 후 1의 자리 이하 버림(소수포함) 32160
select round(32156.96674,-2) from dual; -- 10의 자리를 보고 100의 자리를 올리거나 내림 .10의 자리에서 반올림 후 10의자리 이하 버림. 32200
select round(32156.96674,-3) from dual; -- 100의 자리를 보고 1000의 자리를 올리거나 내림 .100의 자리에서 반올림 후 100의 자리 이하 버림. 32000
select round(32156.96674,-4) from dual; -- 1000의 자리를 보고 10000의 자리를 올리거나 내림 .1000의 자리에서 반올림 후 1000의 자리 이하 버림. 30000
select round(32156.96674,-5) from dual; -- 10000의 자리를 보고 100000의 자리를 올리거나 내림 .
MOD(숫자값, a)
- a 숫자값을 나누기 할 수
- 숫자값 나머지의 부호는 항상 숫자값을 따라감.
mod(19, 0) 0으로 나눌 경우
- 오라클에서는 나눗셈의 나머지를 구할 수 없는 경우 dividend (나누기 할 수) 를 반환하도록 설계됨. 무한대 아님.
- cf. 일반 나눗셈 19/0의 경우는 divisor is equal to zero 오류 발생
select mod(19,3) from dual; -- 1
select mod(-30, 4) from dual; -- -2
select mod(19,0) from dual; -- 0. * 무한대가 아니네? 0으로 나눈 나머지인데
날짜형 함수 ⭕ 중요
ROUND(날짜값, 자리수)
- 자리수 ’YEAR’, ‘MONTH’, ‘DAY’ ‘mi’
- 작은 따옴표로 감싸고 있어야함. 안하면 에러 발생
- 날짜값에는 '25/05/25' 문자 형식으로 오면 에러 발생. 반드시 to_date()로 형변환 해줘야 함.
- [에러] ORA-01722: 수치가 부적합합니다 01722. 00000 - "invalid number
| now | trunc_minute | round_minute |
| 2025-04-30 14:23:46 | 2025-04-30 14:23:00 | 2025-04-30 14:24:00 |
select round(sysdate , 'dd') from dual; -- 25/05/01 12시 넘으니 내일로 반올림
select round(sysdate , 'month') from dual; -- 25/05/01 15일 이상이니 반올림.
select round(sysdate , 'year') from dual; -- 25/01/01
select round(sysdate , 'mi') from dual; -- 25/04/30
select round('25/05/25', 'month') from dual; -- 에러.ORA-01722: 수치가 부적합합니다
-- 01722. 00000 - "invalid number"
select round(to_date('25/05/25'), 'month') from dual; -- 형변환
변환형 함수
TO_NUMBER()
- 문자를 숫자로 변환
- [ JAVA ] pareInt()
TO_CHAR()
숫자나 날짜를 문자열로 변환
- 250429 - TO_CAHR( 값, 형식) → “25/04/29” 형식 지정 필수 yyyymmdd
- 250429 ← to_date( ) - “25/04/29”
- 형식
- YYYY, YY(끝자리2개), MON(3자리 영문,SEP), MM(2자리숫자), DD(날짜day, 31), MI(분), SS(초)
| 요소 | 의미 |
| 9 | 일반적인 숫자. 그외 로 하면 에러남 |
| 0 | 앞의 빈자리 0으로 채움 |
| $ | 달러 기호 표시 |
| L | 지역 통화 단위. 알아서 출력 |
| . 점 | 소수점 표시 |
| , 콤마 | 천단위 표현 |
-- 숫자를 문자로 변환
select to_char(3500000, '$1,111,111') from dual; --invalid number format model" 9로 형식을 써야 일반적인 숫자로 인식함.
select to_char(3500000, '$9,999,999') from dual; -- $3,500,000
select to_char(3500000, 'l9,999,999') from dual; -- ￦3,500,000 알아서 지역에 맞게 원 단위 출력함
select to_char(35000, 'l0,999,999') from dual; -- ￦0,035,000 형식으로 지정한 수보다 작으면 0으로 채움
select to_char(35000, 'l0,999.999') from dual; -- #################### 형식 지정에서 정수는 최대 4자리인데, 숫자는 5자리이므로 마스크 길이만큼 # 출력
select to_char(3500, 'l0,999.999') from dual; -- ￦3,500.000
TO_DATE()
- 문자를 날짜(DATE)로 변환
- DATE 타입: 초단위 정수 ms (1000분의 1초)
그룹 함수
- 모든 그룹함수는 null 값을 고려하지 않음. 단, count(*) 제외
주의사항
- count(*) vs. count (salry) 차이
- count(*) 행의 개수
- count(salary) null 제외한 행 개수
- avg() 계산에서는 나누는 수를 count(*)가 아닌 count(salary) null 제거된 개수임.
select count(*) from ex_test; -- 4
select count(salary) from ex_test; -- 3
select avg(salary ) as "평균" from ex_test; -- 200. avg 계산에서는 나누는 수를 count(*)가 아닌 count(salary) null 제거된 개수임.
nvl(salary, 0)
null값 대신 0으로 저장. oracle 함수.
함수를 여러개 겹쳐 사용
안쪽부터 바깥쪽 순서로 적용
-- emp.sal 이 NULL 이면 0으로,
-- 반올림(Round) 후 천 단위 구분자·소수점 둘째 자리까지 표시
SELECT TO_CHAR(
ROUND( NVL(sal, 0) , 2 ), -- NVL → ROUND
'FM999G999G990D00' -- → '1,234.50' 같은 포맷
) AS formatted_sal
FROM emp;