국비교육 22일차 : 오라클 기초
1. 테이블(또는 뷰)에서 데이터 정보를 꺼내와서 보는 명령어인 select 의 처리 순서★
★★ 중요 ★★
5. select 컬럼명, 컬럼명
-컬럼명 대신에 *(아스테리크)을 쓰면 모든 컬럼을 뜻하는 것이다.
1. from 테이블명(또는 뷰명)
2. where 조건절
-where 조건절이 뜻하는 것은 해당 테이블명(또는 뷰명)에서 조건에 만족하는 행(row)을 메모리(RAM)에 로딩(퍼올리는 것) 해주는 것이다.
3. group by 절
4. having 그룹함수조건절
6. order by 절
2. NULL을 처리해주는 함수
1) NVL
select nvl(1,2), nvl(null,2),
nvl('비','장마'), nvl('null','장마')
from dual;- from dual은 select 다음에 나오는 값들을 화면에 보여주기 위한 용도로 쓰이는 가상테이블
- 첫번째 자리의 값이 null이 아니라면 1이나 '비'를 출력해주고, null이라면 2나 '장마'를 출력해준다.
2) NVL2
- 대소문자 구분 안함
select nvl2(1,2,3), nvl2(null,2,3),
nvl2('비','장마','그만'), nvl2('null','장마','그만')
from dual;- 첫번째 자리의 값이 null이 아니면 두번째 자리의 값이, null이라면 세번째 자리의 값이 나옴
3. 컬럼명 변경
- hr 테이블
select employee_id AS "사원번호" -- 별칭(별명) alias
, first_name "이름" -- 별칭(별명) alias에서 as는 생략가능
, last_name 성 -- 별칭(별명) alias에서 ""는 생략가능
, salary "기본 급여" -- 별칭(별명) alias에서 공백을 주고자 한다면
-- 반드시 "" 해주어야 한다.
, commission_pct "수당퍼센티지"
, NVL(Salary + (Salary * Commission_Pct),Salary) 월급
, NVL2(commission_pct, salary + (salary * commission_pct), salary) Month_Salary
-- "" 안쓰면 대소문자 상관 없는데 "" 쓰면 대소문자를 구분한다.
-- 그래서 "" 잘 사용하지 않고 띄어쓰기는 _ 이용한다.
, department_id 부서번호
from EMPLOYEES;4. 비교연산자
- 같다 =
- 같지않다 != <> ^=
- 크다. 작다 > <
- 같거나크다. 같거나작다 >= <=
- NULL 은 존재하지 않는 것이므로 비교대상이 될 수가 없다.
그러므로 비교연산( = != <> ^= > < >= <= )을 할수가 없다.
그래서 비교연산을 하려면 nvl()함수, nvl2()함수를 사용하여 처리한다.
5. 컬럼들을 연결할 때(붙일 때)
- 오라클에서 컬럼들을 연결할 때는 문자 타입이든, 숫자 타입이든, 날짜 타입이든 관계없이 || 를 사용한다.
6. hr 사용한 예시1
- employees 테이블에 부서번호가 null인 직원들의 사원번호, 사원명, 월급, 부서번호를 출력하세요.
desc employees; -- 테이블의 구조부터 확인한다.(null 값이 있는지 없는지 등)
select employee_id 사원번호
, first_name || ' ' || last_name 사원명
, nvl(salary + (salary * commission_pct), salary) 월급
, department_id 부서번호 -- 화면에 퍼 올릴 때는 원래 부서 번호를 부여한다.
from employees
where nvl(department_id, -9999) = -9999;
-- 또는
-- where department_id is null; - department_id의 데이터가 null 값인 데이터에 -9999(실제로 사용되지 않는 부서번호)를 부여하여 null이 있어도 비교연산자의 사용이 가능하게 해준다.
- where는 이 조건에 만족하는 행들만 메모리에 퍼올린 것.
- from where은 퍼 올리기만 하는 거고 select로 화면에 보여주는 것이기 때문에 다시 원래 값을 보여주게 됨.
7. 데이터의 정렬
- 오름차순 정렬
select employee_id, first_name, last_name, salary, department_id
from employees
order by salary asc; -- asc 생략 가능- 내림차순 정렬
select employee_id, first_name, last_name, salary, department_id
from employees
order by salary desc; -- desc 는 생략 불가능
-- order by 4 desc; -- salary가 4번째 컬럼이기 때문에 이렇게 쓸 수도 있다. -
null의 정렬
정렬(오름차순, 내림차순)을 할 때 NULL은 존재하지 않는 것이므로 오라클에서는 NULL을 가장 큰 것으로 간주를 하고, 마이크로소프트사의 MS-SQL 에서는 NULL을 가장 작은 것으로 간주한다. -
1차 정렬, 2차 정렬
-- employees 테이블에서 부서번호별 오름차순 정렬을 한 후에 동일한 부서번호내에서는
-- 월급의 내림차순으로 정렬하여 사원번호, 사원명, 월급, 부서번호를 나타내세요.
select employee_id
, first_name
, last_name
, nvl(salary + (salary * commission_pct), salary) as month_salary
, department_id
from employees
order by 5 asc, 4 desc;
-- order by 5, 4 desc; -- asc 생략 가능
-- 1차 정렬, 2차 정렬8. 연산자
- AND OR IN() NOT
- IN()과 OR은 같다.
- AND와 OR가 혼용되어지면 AND가 우선적으로 실행되어지고, 연산자에 있어서 괄호 ( ) 가 가장 먼저 실행된다.
9. 범위 연산자
- > < >= <= between A and B
- 범위 연산자에 사용되는 데이터는 숫자 뿐만 아니라 문자, 날짜까지 모두 사용된다.
10. 현재 시간
select sysdate, current_date, localtimestamp, current_timestamp, systimestamp
from dual;
-- sysdate는 피씨의 시간 데이터
-- 22/06/30 22/06/30 22/06/30 15:27:54.040000000 22/06/30 15:27:54.040000000 ASIA/SEOUL 22/06/30 15:27:54.040000000 +09:00- 날짜 타입은 date 이다.
date 타입의 기본적인 표현방식은 'RR/MM/DD' 로 나타내어진다.
RR은 연도의 두자리만 나타내어주는데 50 ~ 99 는 1950 ~ 1999를 말하는 것이다.
RR은 연도의 두자리만 나타내어주는데 00 ~ 49 는 2000 ~ 2049를 말하는 것이다.
MM은 월이고, DD는 일이다. - 날짜 출력 형식 설정
select sysdate
, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
, to_char(sysdate, 'yyyy-mm-dd hh:mi:ss')
, to_char(sysdate, 'yyyy-mm-dd am hh:mi:ss')
, to_char(sysdate, 'yyyy-mm-dd pm hh:mi:ss')
from dual;- ★ 중요: 날짜를 나타낼 때 시,분,초가 없는 연,월,일만 나타내어 주면 자동적으로 0시 0분 0초가 된다. 즉, 날짜를 나타낼 때 시,분,초가 없는 연,월,일은 그 날짜의 자정을 뜻하는 것이다.
11. 테이블 생성하기
1) 테이블 생성하기
create table tbl_test01
(no number(5) -- no는 컬럼명, -99999~99999
,name Nvarchar2(20) -- 20글자까지 가능
,input_day date default sysdate -- 특정 값을 주지 않으면 자동적으로 현재 날짜가 입력
);
-- Table TBL_TEST01이(가) 생성되었습니다.2) 테이블에 데이터 입력하기(행row 추가하기)
insert into tbl_test01(no, name, input_day) values(1, '일번', sysdate);
-- 1 행 이(가) 삽입되었습니다.
insert into tbl_test01(name, no, input_day) values('이번', 2, sysdate);
insert into tbl_test01 values(3, '삼번', sysdate);
commit;
-- 커밋 완료.
select no, name, input_day, to_char(input_day, 'yyyy-mm-dd hh24:mi:ss')
from tbl_test01;
-- 테이블 출력해주기- 컬럼명의 순서에 따라서 데이터를 입력해주어야 한다.
- 컬럼명은 생략 가능
3) 데이터 수정하기(특정 컬럼의 값을 변경하기)
update tbl_test01 set name = '3번이다'
where no = 3
-- 1 행 이(가) 업데이트되었습니다.
-- no가 3 인 애만 메모리에서 퍼 올려서 수정
commit;
select no as 번호
, name as 성명
, to_char(input_day, 'yyyy-mm-dd hh24:mi:ss') as 입력날짜
from tbl_test01;4) 행 삭제하기
delete from tbl_test01
where no = 3;
-- 1 행 이(가) 삭제되었습니다.
commit;5) 테이블 삭제하기
drop table tbl_test01 purge;
-- Table TBL_TEST01이(가) 삭제되었습니다.벌써 수업 들은지 한달이 넘었다.. 나 잘하고 있는 거 겠지? 아직도 하나도 모르겠는 기분...
이런 저런 기록들