문자 연결자
|| Oracle에서는 문자 연결자가 ||이다.
select ename, job || '분야' from emp중복값 제거
distinct 컬럼명 앞에 붙힌다.
select distinct job from emp정렬
order by 컬럼명 [asc|desc]
- asc 오름차순 정렬 (생략 시 오름차순)
- desc 내림차순 정렬
select job from emp order by job desc- nulls last NULL 값을 마지막에 출력
- nulls first NULL 값을 처음에 출력
where 조건
-
문자열은 대소문자를 구분한다.
-
결과값이 항상 false일 경우 출력값이 없다.
where 1000 >= 3000- 결과값이 항상 true일 경우 전체 레코드가 출력된다.
where 1000 <= 3000- in 연산자
or 연산자와 같은 결과이다.
where height in (165, 195, 196)- any 연산자
in 연산자와 동일하다.
where height = any (165, 195, 196)-
all 연산자
and 연산자와 같은 결과이다.
where -
between 연산자
where 컬럼명 between 값 and 값
이상 ~ 이하 범위 내 해당되는 레코드가 출력된다.
where height between 192 and 195- like 연산자
where 컬럼명 like '와일드카드(%|_)'
% 모든 경우를 나타냄
-- 이름 자체가 '가'이거나 '가'로 시작하는 사람
where player_name like '가%'_ 한 글자를 나타냄
-- 가로 시작하고 이름이 두 글자인 사람
where player_name like '가_'escape 와일드카드를 문자로 인식함
-- 영문 이름에 '%' 문자가 포함된 선수
where e_player_name like '%\%%' escape '\'group by
group by 컬럼명
컬럼을 그룹화한다.
집계 함수와 함께 쓰여 그룹별로 집계를 낸다.
-- 부서별 사원 수
select deptno, count(*)
from emp
group by deptno;having
having 조건
전체 데이터를 집단화한 뒤 조건에 맞는 값을 필터링 한다. (group by 후 실행)
-- 부서별 인원 수가 5명 이상인 결과만 조회 (having절에서만 그룹 함수 사용 가능)
select deptno, count(*)
from emp
group by deptno
having count(*) >= 5;NULL
- 현재까지 정해지지 않은 미정의 값
- 존재하지 않는 값
- 알 수 없는 값
- NULL을 포함한 모든 연산은 결과값이 NULL
- NULL과 0은 다른 의미
-- null + 300은 null임
select comm + 300 from emp;
-- null 제외하기
select comm from emp where comm is not null
-- null만 보이기
select comm from emp where commit is null데이터베이스 언어 종류
-
데이터 정의어 (DDL, Data Definition Language)
create, alter, drop, rename, comment, truncate -
데이터 조작어 (DML, Data Manipulation Language)
select, insert, delete, update -
데이터 제어어 (DCL, Data Control Language)
grant, revoke, commit, rollback
데이터 정의어 (DDL)
테이블 생성
create table 테이블명 (
컬럼명 데이터타입
)
create table books (
no number,
name varchar2(30)
);테이블 구조 변경
alter table 테이블명 모드 작업
alter table books add (author varchar2(30))date format 변경
alter session set nis_date_format = 'YYYY/MM/DD'
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'
테이블명 변경
rename 기존테이블명 to 변경할테이블명
rename books to t_books테이블 주석
- 설정
comment on table 테이블명 is 주석
comment on table t_books is '출판 서적 목록'- 확인
select table_name, comments from user_tab_comments where table_name = '테이블명'
select table_name, comments from user_tab_comments where table_name = 'T_BOOKS'레코드 삭제 (truncate)
truncate table 테이블명
truncate는 DDL이기 때문에 commit이 자동으로 수행되어 rollback이 안 된다.
또한 기억공간까지 삭제한다.
truncate table t_books테이블 삭제
drop table 테이블명
drop table t_books데이터 조작어 (DML)
레코드 삽입
insert into 테이블명 (컬럼명) values (값)
- 컬럼명 생략
모든 컬럼의 값을 입력해야 한다.
insert into 친구 values ('Chris', '01011112222', '2007/08/28')레코드 변경
update 테이블명 set 컬럼명 = '변경할 값' where 변경할 레코드 조건
조건절이 없으면 전체 레코드가 변경되므로 주의해야 한다.
update 친구 set 휴대폰 = '01099998888' where 이름 = 'Chris'데이터 제어어 (DCL)
뷰
물리적 테이블로부터 유도된 가상의 테이블을 말한다.
- select로 나온 테이블은 하드에 저장된 실제 데이터(물리적 테이블)를 메모리로 불러온 가상의 테이블, 뷰이다.
- commit 전에는 뷰(메모리)의 데이터만 바뀐다.
- commit 명령어를 쓰면 실제 데이터베이스에 있는 데이터가 바뀐다.
자동 커밋 옵션
- 현재 커밋 옵션 보기
show autocommit- 자동으로 커밋하기
set autocommit on- 수동으로 커밋하기
set autocommit off내장 함수
문자 함수
lower
소문자로 출력한다.
select lower(ename) from empupper
대문자로 출력한다.
select upper(ename) from empinitcap
첫 글자만 대문자로 출력한다.
select initcap(ename) from emplength
길이를 출력한다.
select length(ename) from emplengthb
영문 1byte, 한글 3byte로 byte 수를 출력한다.
substr
substr(대상, 시작 위치, 개수)
문자열의 일부(시작 위치 포함)만 추출해서 사용한다. (0과 1은 동일하다.)
select substr(e_player_name, 3, 4) from player_tsubstrb
영문은 1byte, 한글은 3byte로 추출한다. (한글 4, 5 같은 것은 3과 같음)
-- KAI, 가
select substrb(e_player_name, 1, 3), substrb(player_name, 1, 3)
from player_t
where player_name = '가이모토';lpad, rpad
lpad(대상, 확보할 크기, 왼쪽 남은 공간 채울 문자)
rpad(대상, 확보할 크기, 오른쪽 남은 공간 채울 문자)
특정 자릿수만큼 채워서 표시할 때 사용한다.
-- ^^^^^SMITH
select lpad(ename, 10, '^') from emp;
-- SMITH^^^^^
select rpad(ename, 10, '^') from emp;ltrim, rtrim, trim
ltrim(대상, 제거할 문자)
rtrim(대상, 제거할 문자)
trim(제거할 문자 from 대상)
왼쪽 끝, 오른쪽 끝, 양쪽 끝에 나오는 특정 문자를 제거한다.
-- ALLEN -> LLEN
select ltrim(ename, 'A') from emp;
-- SCOTT -> SCO
select rtrim(ename, 'T') from emp;
-- SMITH -> MITH, JAMES -> JAME
select trim('S' from ename) from emp;replace
replace(대상, 찾을 문자열, 변경할 문자열)
특정 문자열을 다른 문자열로 변경한다.
-- SCOTT -> *?OTT
select replace(ename, 'SC', '*?') from emp;translate
translate(대상, 찾을 문자열, 변경할 문자열)
특정 문자열을 다른 문자열로 순서대로 변경한다.
-- S는 *로, C는 ?로 변경
-- JONES -> JONE*, SCOTT -> *?OTT
select translate(ename, 'SC', '*?') from emp;instr
instr(대상, 찾을 문자열, 시작 위치, 횟수)
지정한 문자가 어느 위치에 나오는지 알려준다. (없을 경우 0)
0부터 시작 불가능하다.
-- WARD 2
select instr(ename, 'A', 1, 1) from emp;
-- ADAMS 3
select instr(ename, 'A', 1, 2) from emp;concat
concat(컬럼1, 컬럼2)
컬럼1의 레코드와 컬럼2의 레코드를 이어준다.
||와의 차이점 concat은 두 개만 가능하다.
숫자 함수
round
round(숫자[, 자리])
숫자를 자리까지 반올림하여 보여준다.
-- 7678
select round(7677.567) from dual;
-- 7677.57
select round(7677.567, 2) from dual;
-- 8000
select round(7677.567, -3) from dual;round(날짜[, 형식])
날짜를 형식 까지 반올림하여 보여준다.
(7월이면 다음 년도, 16일이면 다음 월)
-- 2021-05-24 01:25:59, 2021-06-01 00:00:00
select sysdate, round(sysdate, 'mm') from dual;trunc
trunc(숫자[, 자리])
숫자를 자리 뒤를 잘라서 보여준다.
-- 7677
select trunc(7677.567) from dual;
-- 7677.56
select trunc(7677.567, 2) from dual;
-- 7000
select trunc(7677.567, -3) from dual;trunc(날짜[, 형식])
날짜를 형식 뒤를 잘라서 보여준다.
-- 2021-05-24 01:25:59, 2021-05-01 00:00:00
select sysdate, trunc(sysdate, 'mm') from dual;ceil
ceil(숫자)
숫자를 올림한다.
-- 4, -3
select ceil(3.3), ceil(-3.3) from dual;floor
floor(숫자)
숫자를 내림한다.
-- 3, -4
select floor(3.3), floor(-3.3) from dual;mod
mod(피제수, 제수)
피제수 % 제수이다. (나머지)
-- 1
select mod(10, 3) from dual;sign
sign(숫자)
숫자가 0이면 0, 음수이면 -1, 양수이면 1을 출력한다.
-- 0, -1, 1
select sign(0), sign(-5), sign(5) from dual;집계 함수
(= 그룹 함수, 집단 함수)
select절에는 집계 함수끼리만 사용할 수 있다. (group by에 있는 컬럼명은 함께 사용 가능)
sum
sum(컬럼명)
합계를 구한다.
avg
avg(컬럼명)
평균을 구한다.
max
max(컬럼명)
최댓값을 구한다.
min
min(컬럼명)
최솟값을 구한다.
count
count(컬럼명)
*일 경우 NULL 값을 포함한 전체 레코드 수를 출력한다.
컬럼명일 경우 NULL 값을 제외한 레코드 수를 출력한다.
날짜 함수
시스템 날짜 출력
select sysdate from dual
더하기
- 1일 더하기
select sysdate + 1 from dual;- 한 시간 더하기
select sysdate + (1 / 24) from dual;- 70분 더하기
select sysdate + (1 / 24 / 60) * 70 from dual;
select sysdate + (1 / (24 * 60)) * 70 from dual;- 1초 더하기
select sysdate + (1 / 24 / 60 / 60) from dual;
select sysdate + (1 / (24 * 60 * 60)) from dual;add_months
add_months(날짜, 개월수)
날짜를 기준으로 개월수 후를 출력한다.
-- 오늘을 기준으로 10개월 후
select add_months(sysdate, 10) from dual;months_between
months_between(날짜1, 날짜2)
날짜1과 날짜2사이에 몇 개월이 지났는지 출력한다.
-- 485.22... (생략)
select months_between(sysdate, hiredate) from emp;last_day
last_day(날짜)
날짜 달의 마지막 날짜를 출력한다.
select last_day(sysdate) from dual;변환 함수
-- string + int의 경우 string이 int로 바뀌어 연산된다.
'0070' + 0050to_char
to_char(날짜[, 형식])
날짜 정보를 문자열로 바꾼다.
필요한 일부 요소만 추출해서 사용 가능하다.
- day
요일 - d
요일 번호 (일요일 먼저) - dd
일 - ddd
연 기준 일 - yyyy
연 - mm
월 - hh
시간 - hh24
24시간 - mi
분 - ss
초 - sssss
자정부터 몇 초
to_char(숫자[, 표시 형태])
- 999,999
천 단위 콤마, 공백 채움 - 000,000
천 단위 콤마, 0으로 채움
to_date
to_date(문자열, 형식)
날짜를 생성한다. (지정하지 않는 값들은 기본값으로 적용)
없는 값으로 설정하면 에러가 발생한다. (13월 등)
-- 2012-05-01 00:00:00
select to_date('12', 'yy') from dual;extract
extract(추출 정보 from 날짜)
날짜에서 추출 정보를 추출한다.
연, 월, 일을 혼합해서 사용할 수는 없다.
- day
- month
- year
select extract(day from sysdate) from dual;NULL 함수
nvl
nvl(컬럼명, 기본값)
NULL 값을 기본값으로 변경해준다.
select nvl(comm, 0) from emp;nvl2
nvl2(컬럼명, NULL이 아닐 때, NULL일 때)
select nvl2(comm, comm + 100, 0) from emp;비교 함수
nullif
nullif(컬럼명, 비교값)
컬럼명의 레코드와 비교값이 같지 않다면 레코드 값, 같다면 NULL을 출력한다.
-- 20, 20
-- 30, NULL
select deptno, nullif(deptno, 30) from emp;case 표현식
case when 조건 then 출력값 else 출력값 end
조건이면 출력값 출력, 해당되지 않는 모든 것은 else 출력값을 출력한다.
-- 30, 'A팀' ...
select deptno, case when deptno = 30 then 'A팀'
when deptno = 20 then 'B팀'
when deptno = 40 then 'C팀'
else 'F팀'
end
from emp;decode
decode(컬럼명, 값, 처리, 기타 처리)
컬럼명의 레코드 값이 값이면 처리를 출력하고, 해당되지 않는 모든 것은 기타 처리를 출력한다.
-- 30, 'A팀'...
select deptno, decode(deptno, 30, 'A팀', 20, 'B팀', 40, 'C팀', 'F팀') from emp;