데이터베이스 데이터조작어(DML)

 

가장 쉬우면서도 가장 중요한 내용

 

- DISTINCT : 중복되는 행을 제거하는 옵션.
- * : 테이블의 모든 column을 출력.
- alias : 해당 column에 대해서 다른 이름을 부여할 때 사용.
- table_name : 질의 대상 테이블 명
- WHERE : 조건을 만족하는 행들 만 검색
- condition : column, 표현식, 상수 및 비교 연산자
- ORDER BY : 질의 결과 정렬을 위한 옵션(ASC:오름차순(Default),DESC내림차순)

 

 

 

WHERE절에 사용될 수 있는 SELECT 연산자

BETWEEN a AND b	a와 b사이의 데이터를 출력.(a, b값 포함)
IN (list)	list의 값 중 어느 하나와 일치하는 데이터를 출력
LIKE	문자 형태로 일치하는 데이터를 출력(%, _사용)
IS NULL	NULL값을 가진 데이터를 출력 합니다.
NOT BETWEEN a AND b	a와 b사이에 있지않은 데이터를 출력(a, b값 포함하지 않음)
NOT IN (list)	list의 값과 일치하지 않는 데이터를 출력
NOT LIKE	문자 형태와 일치하지 않는 데이터를 출력
IS NOT NULL	NULL값을 갖지 않는 데이터를 출력

 

- 일반적으로 NOT 연산자는 쿼리를 두번사용하기에, 사용을 권장하지 않는다.

 

 

 

- select문의 구조

- where : 테이블안에 있는 행을 선택한다. (한줄)

- select와 from사이에 있는 구간은 컬럼을 빼온다.

- 기본필드 : 기본컬럼 (국어,영어,수학), 연산필드 : 국어,영어,수학의 총점

- ② : where이 생략되면 전체 레코드가 추출이 된다.

- ④ : 오름차순, 내림차순 정렬 선언

 

select
sabun,saname,sapay
from sawon

select
   sabun as 사번,
   saname 사원명,
   sapay "pay of year", -- 공백이나 특수문자를 헤딩에 넣고 싶으면 더블커팅""안에 명칭을 넣는다.
   sapay*0.1 as 보너스,
   round(sapay/12.0) as 월급
from sawon

 

- select와 from 사이에 선언하는 것은 컬럼명이다. 이것을 헤딩이라고 표현함.

- 헤딩명을 변경해서 읽어올 수도 있다. (단, java에서 불러올 때는 처음만들때 정식으로 지정한 컬럼명으로 지정해주어야함)

 

-- 사원테이블에서 10번부서 직원 조회
select * from sawon
where deptno = 10
	
-- 사원테이블에서 10,30번 부서 직원 조회
select * from sawon
where deptno in(10,30) -- ex)1
where deptno=10 or deptno=30 -- ex)2

-- 사원테이블에서 연봉 3500이상 받는 직원
select * from sawon
where sapay >= 3500

-- 사원테이블에서 연봉 3000에서 4000사이를 받는 직원
select * from sawon
where sapay between 3000 and 4000
where sapay>=3000 and sapay<=4000

--사원테이블에서 10번부서의 남자직원 조회
select * from sawon
where deptno=10 and sasex='남자';

-- 사원테이블에서 2000년 이후에 입사한 직원 조회
select * from sawon
where sahire >= '2000-1-1'

-- ★유의★사원테이블에서 2000 ~ 2003년 사이의 입사한 직원 조회
-- 입사일자 : '2003-12-31 13:00:00'
select * from sawon
-- where sahire >= '2000-1-1' and sahire <= '2003-12-31'
-- '2003-12-31 00:00:00'까지만 계산되기 때문에 그 시간 이후의 31일 입사자는 조회가 안된다.
where sahire >= '2000-1-1' and sahire < '2004-01-01'

-- cf)날짜/시간 확인
select to_char(sysdate,'YYYY-MM-dd HH:mi:ss') from dual
select to_char(to_date('2004-1-1','YYYY-MM-DD'),'YYYY-MM-dd HH:mi:ss')
from dual

where deptno = 10의 결과

문자를 날짜로 변환, 날짜를 문자로 변환하는 함수

-- ★유의★사원테이블에서 2000 ~ 2003년 사이의 입사한 직원 조회
-- 입사일자 : '2003-12-31 13:00:00'
select * from sawon
-- where sahire >= '2000-1-1' and sahire <= '2003-12-31'
-- '2003-12-31 00:00:00'까지만 계산되기 때문에 그 시간 이후의 31일 입사자는 조회가 안된다.
where sahire >= '2000-1-1' and sahire < '2004-01-01'

-- 문자형 -> 날짜 변환
select * from sawon where sahire >= to_date('2000-1-1 13:00:22','YYYY-MM-DD HH24:MI:SS')
-- 여기서 문자형을 날짜로 변환해주는 이유는 날짜와 날짜의 값을 비교하기 위해서 변경했다.

-- cf)날짜/시간 확인
select to_char(sysdate,'YYYY-MM-dd HH:mi:ss') from dual
select to_char(to_date('2004-1-1','YYYY-MM-DD'),'YYYY-MM-dd HH:mi:ss')
from dual

-- 날짜 -> 문자 -> 숫자
-- +연산자 : 숫자, 날짜
select
   sabun,saname,sahire,
   to_number(to_char(sahire,'YYYY')) -- 1) 연도날짜를 문자로 변경 'YYYY' 2) 문자였던 년도를 숫자로 변경
   as "hire year",
   to_number(to_char(sahire,'MM')) -- 1) sahire 컬럼에 속해있는 날짜를 월로 변경 2) 문자였던 월을 숫자로 변경
   as "hire month"
from sawon

 

-- 사원테이블에서 7월에 입사자 조회
select * from sawon
where to_number(to_char(sahire,'MM')) = 7

-- 사원테이블에서 봄에 입사자 조회
where to_number(to_char(sahire,'MM')) = 3 or to_number(to_char(sahire,'MM')) = 4 or to_number(to_char(sahire,'MM')) = 5

-- 사원테이블에서 입사년도 2002 ~ 2003년인 직원 조회
select * from sawon
where to_number(to_char(sahire,'YYYY')) between 2002 and 2003
where to_number(to_char(sahire,'YYYY')) in(2002,2003)

-- 사원테이블에서의 10번부서의 과장, 대리를 구하라
select * from sawon
where deptno = 10 and sajob in ('과장','대리');

-- 사원테이블에서 여자직원중 1995년 이후에 입사한 직원 조회
select * from sawon
where sasex = '여자' and to_number(to_char(sahire,'YYYY')) > 1995

 

 

- like를 활용한 유사검색

-- like 연산자 : 문자열 유사검색식(문자열에만 사용할 수 있다)
-- 필드 like '%_'
-- % : 모든문자(갯수상관없다)
-- _ : 모든문자(1개)

-- 사원테이블에서 이씨 성씨를 갖는 직원 조회
select * from sawon
where saname like '이%' -- 이씨로 시작하는데 뒤에 무슨 글자든 상관이 없다 %
-- 게시판 내에 있는 글을 찾을 때 유사검색 사용

-- 사원테이블에서 이름의 2번째 글자가 '미'인 직원 조회
select * from sawon
where saname like '_미%' -- underscore(_)는 글자수 표현 (앞에 %가 붙으면 안되는 이유는 %는 글자수 상관없이 받아버리기 때문이다)

-- 고객테이블에서 여자고객만 조회
-- gojumin = '760815-1325467'
select * from gogek
where gojumin like '______-2%'
   or gojumin like '______-4%'
   or gojumin like '______-6%'
   or gojumin like '______-8%'
   or gojumin like '______-0%'

-- 고객테이블에서 서울에 거주하는 고객
select * from gogek
where goaddr like '서울%'

 

 

- 숫자 반올림, 소수점 자리 전부 없애고, 정수만 출력하기

 

http://gurubee.net/lecture/1024

Numeric Functions (숫자형 함수)

 

http://gurubee.net/lecture/1025

Character Functions (문자형 함수)

 

 

- substring과 like는 문자데이터에서만 사용할 수 있다.

- 날짜데이터나 숫자데이터에서 사용할 경우 to_char()을 사용해 타입변환을 해준뒤, 사용한다.

-- 사원테이블에서 이씨성을 조회
select sabun,saname, substr(saname,1,1) as 이씨성 from sawon
where substr(saname,1,1) = '이'

-- 고객테이블에서 서울사는 고객 조회
select gobun,goname,goaddr , substr(goaddr,1,2) as 시도 from gogek
where substr(goaddr,1,2) = '서울'

-- 고객테이블에서 여자고객만 조회
--            12345678901234 <- index(오라클 1base)
-- gojumin = '760815-1325467'
select gobun,goname,substr(gojumin,8,1) gender_code from gogek
-- where to_number(substr(gojumin,8,1)) in (0,2,4,6,8)
where mod(to_number(substr(gojumin,8,1)),2) = 0

substr    : 문자데이터에만 사용
to_char() : 날짜데이터

-- 사원테이블에서 입사계절이 봄인 사원 조회(sahire:date)
select * from sawon
where to_number(to_char(sahire,'MM')) in (3,4,5)

-- 고객테이블에서 출생계절이 봄인 고객 조회(gojumin:varchar2)
select gobun, goname, to_char(substr(gojumin,3,2)) as month from gogek
where to_number(substr(gojumin,3,2)) in (3,4,5)

 

 

 

- 날짜 date 함수

 

http://gurubee.net/lecture/1026

Datetime Functions (날짜 함수)

 

-- add_months
select sysdate 입대날짜, add_months(sysdate,18) 제대날짜 from dual;
select sysdate 제대날짜, add_months(sysdate,-18) 입대날짜 from dual;
select months_between(add_months(sysdate,5),sysdate) from dual;

-- 날짜함수 이용(사원테이블에서 근속년수)
-- floor, TRUNC를 활용해 소수점자리를 떼어낸다.
-- 월급 : 연봉/12
-- 퇴직금 : 월급*년수 + 월급*(근무월수/12)
-- 퇴직금 : (총근무월수 * 월급)/12
select
   sabun, saname, sahire, sapay,
   floor(months_between(sysdate,sahire)/12) as 근무년수,
   TRUNC(mod(months_between(sysdate,sahire),12),0) as 근무월수,
   round(sapay/12,0) as 월급,
   round(sapay/12 * floor(months_between(sysdate,sahire)/12) + sapay/12 * mod(months_between(sysdate,sahire),12)/12,0) as 퇴직금1,
   round(months_between(sysdate,sahire)*(sapay/12)/12,0) as 퇴직금2
from sawon

 

 

- inline view를 활용하여 

select 
  s.*,
  월급*근무년수 + 월급*(근무월수/12) as 퇴직금1
from
(
    select
       sabun, saname, sahire, sapay,
       floor(months_between(sysdate,sahire)/12) as 근무년수,
       TRUNC(mod(months_between(sysdate,sahire),12),0) as 근무월수,
       round(sapay/12,0) as 월급,
    from sawon
) s