Oracle 설치와 기본 개념정리

▶▶ Oracle 설치와 SQL문 정리 및 연습

 

말로만 듣던 Oracle에 드디어 입문을 했다.

먼저 기업에서 사용하는 enterprise급 보다는 기초 연습을 위한  exepress용으로 설치를 했다.

 

파일을 설치하면서 비밀번호를 생성하고 마지막 창에서 oracle 창을 띄울 것인가 하는 질문에 OK를 선택하여 마무리를 한다.

 

새로 뜬 브라우저 창에서 설치하면서 설정 했던 내용으로 로그인을 한 후 USER의 비밀번호 재 설정과 함께 잠금을 해제해 준다.

 

해제한 계정으로 로그인하면 이제 연습을 할 수 있는 창이 뜨게 된다.

 

이제 SQL문을 여러가지로 다양하게 활용해 보자!!

 

DBMS 논리모델링
 -엔터티, 속성 / 주식별자

 

물리모델링
 -테이블, 컬럼 /

 

컴럼들로 구성된 데이터의 집합 : 로우(레코드)
다른 로우들과 구분해줄 식별자용 컬럼이 필요하다.
: 기본키 (Primary Key컬럼)

 

기본데이터(Jobs) 먼저 설계 후 세부데이테가(Employees) 만들어 진다.
ㄴ부모테이블                           ㄴ자식테이블

 

실선 : 주식별자 관계(부모테이블의 pk를 자식테이블의 pk로 참조)
점선 : 비식별자 관계(부모테이블의 pk를 자식테이블의 일반컬럼으로 참조)

 

참여자 수 : One to Many (1:M)
     One to One (1:1)
     Many to Many (M:N) - 논리모델링에서는 가능하지만 물리모델림에서는 불가

 

선택참여 : ㅇ 표시로 가능

 

DBMS에 데이터가 저장될때는 binary로 저장됨. 일반편집기로 내용을 확인할 수 없음
DBMS에 접근할 수 있는 전용툴이 필요(토드,오렌지...//유료이다.)

 

다양한툴, 다양한 DBMS를 사용하다 보니 표준화된 접근방법이 필요.
표준화된 언어인 SQL이 필요하게 되었다. (Structured Query Language.)

 -DDL (Data Definition Language) 정의
 -DML (Data Manufulation Language) 조작

 -DCL (Data Control Language) 제어

 

 

오늘 다룰 언어는 DML이다. DML의 기본을 살펴본다.
 - SELECT
 - INSERT
 - UPDATE
 - DELETE

 

SQL의 데이터 타입
 - 문자형
 - char : 고정길이 ex) test char(5)
    'hello'
    'hi'<-나머지 3byte도 메모
 - varchat2 : 가변길이 ex) test varchar2(5)
     'hello'
     'hi'<- 총2byte사용

 - 숫자형
 -number(최대길이, 소숫점이하자릿수)
 ex) test number(5, 2) <-tlftn
     123.45
     test number(5, 0) <-정수
     123

 - 날짜형 : date
 ex) test date
     '13/04/08'

/*
null값을 산술연산에 참여시키면 무한대값이 나옴, 결과:null
*/
SELECT employee_id "사원 번호", first_name, last_name, salary, commission_pct, salary+(salary*commission_pct) 실급여, hire_date
FROM employees

/*
null을 다른값으로 대치. NVL()함수이용.
*/
SELECT employee_id "사원 번호", first_name, last_name, salary, commission_pct, salary+(salary*NVL(commission_pct, 0)) 실급여, hire_date
FROM employees

 

/*
숫자형은 산술연산가능, 문자형은 결합연산가능
java에서 + 연산자, 오라클에서 ||연산자
*/

SELECT employee_id "사원 번호", first_name || '~' || last_name, salary, commission_pct, salary+(salary*NVL(commission_pct, 0)) 실급여, hire_date
FROM employees

 

/*
salary가 10000보다 큰 사원들만 검색하시오.
 */
SELECT *
FROM employees
WHERE salary > 10000

 

 

*SELECT구조
SELECT
FROM
[
JOIN ON
WHERE
GROUP BY
HAVING
ORDER BY
]

 

*SELECT 처리순서
FROM -> JOIN -> WHERE -> GROUP -> HAVING -> SELECT -> ORDER

 

/*
salary가 10000이상이고 20000이하인 사원들만 검색하시오.
*/
SELECT *
FROM employees
WHERE salary >= 10000 AND salary <= 20000

 

SELECT *
FROM employees
WHERE salary BETWEEN 10000 AND 20000

 

 

/*
job_id가 SA_MAN 이거나 SA_REP인 사원들만 검색하시오.
*/
SELECT *
FROM employees
WHERE job_id='SA_MAN' OR job_id='SA_REP'

 

 

/*축양형*/
SELECT *
FROM employees
WHERE job_id IN ('SA_MAN', 'SA_REP')

 

SELECT *
FROM employees
WHERE (job_id IN ('SA_MAN', 'SA_REP')) AND (salary BETWEEN 10000 AND 20000)

 

 

/* rownum 가상컬럼 */
SELECT rownum, employee_id, job_id, salary
FROM employees
WHERE (job_id IN ('SA_MAN', 'SA_REP')) AND (salary BETWEEN 10000 AND 20000)

 

 

/*LIKE연산자 */
SELECT rownum, employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '%e%'

/* e라는 글자 앞에 올수 있는 문자수는 하나이다 */
SELECT rownum, employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '_e%'

 

 

/* JOB_title이 Manager인 job_id와 job_title을 검색 */
SELECT rownum, job_id, job_title
FROM jobs
WHERE job_title LIKE '%Manager%'

 

 

/* 사원번호가 홀수인 사원의 사번, 이름을 검색하시오. */
SELECT rownum, employees_id, first_name || last_name
FROM employees
WHRER MOD(employees_id, 2) = 1;

 

 

/* 입사일이 94년인 사원의 이름과 입사일을 검색하시오. */
SELECT rownum, first_name || last_name, hire_date
FROM employees
WHERE hire_date LIKE '94%';

 

/* first_name의 3번째 글자가 an인 사원을 출력하시오. */
SELECT rownum, employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE '__an%';

 

/*
숫자형 함수 : ROUND() - 반올림, TRUNC() - 버림

*/

SELECT employee_id, first_name, salary, salary/3,
 ROUND(salary/3, 1), TRUNC(salary/3, 1)
FROM employees;

 

 

/*
문자형 함수 : INSTR(), SUBSTR()
dual은 더미테이블이다.
*/
SELECT 'hellojavaoracle' 문자, INSTR('hellojavaoracle','o') o갯수, SUBSTR('hellojavaoracle',1,3) 문자나눔,  LENGTH('hellojavaoracle') 문자형길이
FROM dual;

 

 

/*
날짜형 함수 : SYSDATE
*/
SELECT SYSDATE
FROM dual;

 

 

/*
TO_CHAR() : 날짜형 또는 숫자형을 문자형으로 변환
TO_NUMBRT() : 문자형을 숫자형으로 변환
TO_DATE() : 문자형을 날짜형으로 변환
ex) 날짜형 ->  숫자형
 (->문자형->)
*/

//날짜 빼기 날짜는 일수를 반환 해 준다.
SELECT TO_DATE('2013-04-10', 'YYYY-MM-DD')-SYSDATE
FROM dual;

SELECT TO_NUMBER('12345.67', '99999.99')
FROM dual;

 

 

/*
commission_pct(수당)을 받는 사원들을 출력하시오.
*/
SELECT *
FROM employees
WHERE commission_pct <>NULL;//Error

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

 

 

/*
각 사원사번, 이름, 업무명을 출력하시오.
*/
SELECT employee_id, first_name, last_name, job_title
FROM employees, jobs
WHERE employees.job_id=jobs.job_id

 

 

/*
표준화된 SQL JOIN 처리
*/
SELECT employee_id, first_name, last_name, job_title
FROM employees
JOIN jobs
 ON employees.job_id=jobs.job_id

 

/*
각 사원의 사번, 이름, 부서명을 출력하시오.
*/
SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments
 ON employees.department_id=departments.department_id

 

 

/*
별칭사용
from 절에서 별칭을 사용했으면 from절 부터 수행되기 때문에 그 뒤에는 별칭만 사용 가능해 진다.
*/
SELECT employee_id, first_name, last_name, department_name
FROM employees e
JOIN departments d
 ON e.department_id=d.department_id

 

/*
사원의 사번, 이름, 부서ID, 부서명, 업무ID, 업무명을 출력하시오
*/
SELECT employee_id, e.first_name, last_name, e.department_id, department_name, j.job_id, job_title
FROM employees e
JOIN departments d
 ON e.department_id=d.department_id
JOIN jobs j
 ON e.job_id = j.job_id

 

 

/*
급여가 10000이상인 사원의 사번, 이름, 급여, 부서ID, 부서명, 업무ID, 업무명을 출력하시오
*/
SELECT employee_id, e.first_name, last_name, e.department_id, department_name, j.job_id, job_title, e.salary
FROM employees e
JOIN departments d
 ON e.department_id=d.department_id
JOIN jobs j
 ON e.job_id = j.job_id
WHERE e.salary >= 10000

 

 

/*
사원의 사번, 이름 그 사원을 관리하는 관리자사번, 관리자이름을 출력하시오.
*/

SELECT e.employee_id 사번, e.first_name 사원명, m.employee_id 관리자사번, m.first_name 관리자명
FROM employees e
JOIN employees m
 ON e.manager_id=m.employee_id

 

/*
부서명이 Executive인 부서에 속한 사원의 사번, 이름과 그 사원을 관리하는 관리자사번, 관리자이름을 출력하시오.
*/

SELECT e.employee_id 사번, e.first_name 사원명, m.employee_id 관리자사번, m.first_name 관리자명
FROM employees e
JOIN employees m
 ON e.manager_id=m.employee_id
JOIN departments d
 ON e.department_id=d.department_id
WHERE department_name = 'Executive'

 

 

/*
department_id가 null인 사원을 출력하시오
*/
SELECT employee_id, first_name
FROM employees
WHERE department_id IS NULL;

 

 

/*
OUTER JOIN, employees 테이블의 모든 레코드는 출력대상이 됨.

LEFT OUTER JOIN은 employees를 기준으로 삼아서 모든 것을 출력하도록 하겠다는 것이다!
*/
SELECT e.employee_id 사번, e.first_name 사원명, m.employee_id 관리자사번, m.first_name 관리자명
FROM employees e
LEFT OUTER JOIN departments d
 ON e.department_id=d.department_id

//출력될 결과중에 부족한 쪽이 필요한데 그곳에 + 표시를 해서 모두 나타나게 해준다

(오라클 표현이며 아웃터조인이라고 보면 된다.)
SELECT e.employee_id 사번, e.first_name 사원명, e.first_name 관리자명, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+)

 

위에서 배운 내용들을 간단히 정리하면 아래와 같습니다.

+,-,*,/,MOD()
AND, OR, BETWEEN, IN, LIKE
=, <>