Oracle beginner
강좌 정보
- manialab.co.kr
- 작성년도 : 2002-03
강사 정보
- 장동준
약어들
- Cartesian Product : 곱집합
제1회 오라클 설치하기
- 오라클 계정 필요함
- Database 11g Express Edition - 설치함.
- 개발 도구 : sqldeveloper-17.4.1.054.0712-x64.zip
- 접속 명령어
- connect 계정/pwd
다른점
참고 자료 :
제2회 SQL 맛보기
sqlplus 실행
- sqlplus 를 cmd에서 실행하거나 혹은 [Run SQL Command Line] 아이콘을 실행한다.
- 종료는 exit
기본적인 명령어들
사용자 생성 및 삭제
- 생성 : create user <사용자ID> identified by <패스워드>;
- 삭제 :
- drop user <사용자ID>;
- drop user <사용자ID> cascade;
- 이 경우에는 사용자 정보(테이블) 까지 함께 삭제함.
사용자 권한 부여 및 취소
- 사용자 권한 분류 :
- connect : 접속 권한
- resource : 자원 생성(테이블 이외)
- dba : 모든 권한 (?)
- 권한 부여
- grant connect[,resource] to <사용자ID>;
- 권한 취소
- revoke connect[,resource] from <사용자ID>;
테이블 목록 조회
- select * from tab;
테이블 생성
- greate table <테이블명>(컬럼);
테이블 정보 보기
- desc <테이블명>;
테이블 정보 넣기
- insert into <table명> values (컬럼정보);
테이블 전체 내용 조회
- select * from <table명>;
제3회 Oracle svrmgr란...
svrmgr 위치 10 버젼에는 존재하지 않는 것 같다. 다른 형태로 존재할 것이다.
- <INSTALL_HOME>\app\oracle\product\10.2.0\server\BIN
- 10i 이상 버젼에서는
- $ sqlplus / as sysdba
- $ sqlplus sys/manager as sysdba
Oracle 서버 관리자가 할 수 있는 일
- 오라클 DB의 시작 및 종료
- 오라클 DB 생성
- 테이터 파일 및 Redo로그 파일의 생성
- 오라클 DB 복구 및 생성
- 오라클 DB 사용자 생성
- 오라클 데이터 베이스 튜닝
- 시스템에 관련된 특수한 질의의 실행
주요 명령어들
- 시작과 종료
- startup/shutdown
- 백업과 복구
- archive log
- archive recover
- 접속 해지
- connect
- disconnect
- 주석
- rem
- 서버관리자 종료
- exit
- 도움말
- help
- help index : 도움말 항목 보기
- OS 명령어 잠시 사용하기
- host
- spool
- 서버 관리자상에서 사용하는 명령어와 출력을 외부 파일로 저장 하는데 사용함
- execute
- 외부 pl/sql 문을 실행하는데 사용
- describe (desc)
- 오라클 DB 객체의 구조를 보기 위해서 사용되는 명령어
오라클 시작/중단 그림
참고사이트
제4회 SQL *Plus 사용하기
SQL*Plus 요약
- SQL*Plus는 명령어는 SQL 문이 아님
- SQL*Plus 명령어는 SQL*Plus라는 툴 자체를 사용하기 위한 명령어
- 끝이 “;” (세미콜론)으로 끝나면 SQL문 그렇치 않으면 SQL*Plus 명령어 라고 할 수 있음.
SQL*Plus 명령어들
- append <<텍스트>> : 텍스트를 현재 조회한 행에 추가 한다.
- 단축명령어 : a
- list : 수행한 명령어 줄 보기
- 단축명령어 : l
- <<행번호>> : 행번호 출력하기
- change /<<old_text>>/<<new_text>>
- 단축명령어 : c
- ‘’를 사용시 대소문자가 정확하게 일치 해야 한다.
- del <<행번호>> : 행번호 삭제하기
- save <<파일이름>> : 버퍼에 있는 명령어를 주어진 패스의 파일로 저장한다.
- get <<파일이름>> : 주어진 파일에 있는 명령어를 버퍼로 가지고 온다.
- run : 버퍼에 있는 명령어 다시 실행 함.
- 단축명령어 : r
- start <<파일이름>> : 주어진 파일에 있는 명령어를 실행함.
- set : 환경설정하기
- show <<환경변수>> : 환경 설정을 보기
제5회 Oracle Listener
설명
- 정의 : 오라클 네트워크 관리자 : 네트워크를 이용해서 클라이언트에서 서버로 연결할 수 있게 해줌
- 추가 설명
- 참고 #1 ,
- 관련 파일 위치
- 디렉토리 위치 : <INSTALL_HOME>\app\oracle\product\10.2.0\server\BIN
- 파일명 : LSNRCTL.EXE
- 리스너 설정 관련 파일위치
- <INSTALL_HOME>\app\oracle\product\10.2.0\server\NETWORK\ADMIN
- listener.ora
- 조회 명령어 : status
제6회 Oracle SQL*Loader
설명
- 정의 : 외부 파일을 오라클 DB에 넣기 위해서 사용하는 유틸리티
- 파일 관련 정보
- 디렉토리 위치 : <INSTALL_HOME>\app\oracle\product\10.2.0\server\BIN
- 파일명 : sqlldr.exe
- 추가 설명
- 참고 #1
제7회 Oracle SQL 문 - select 문
Select 문
- 정의 : DB에서 정보를 질의(검색)하는 명령어.
- 형식 :
- SELECT [DISTICT] {*, column [alias],...}
FROM <table이름>;
- 참고
- scott 관련 예제 설명
- scott 예제 sql 위치
- <INSTALL_HOME>\app\oracle\product\10.2.0\server\RDBMS\ADMIN
- scott.sql
- 참고사이트 #1
함수
- NVL(컬럼,<null 일때 결과 값>)
alias 사용하기
- 사용목적은 긴 컬럼명을 간단하게 표현할 때 사용.
- 사용 방법 예
- select ename as name, sal “월급” , sal month_money from emp
- 한글을 “” 사용해야 한다.
연결 연산자(||)
- ‘문자열1’ || 컬럼 || ‘문자열2’
- 컬럼이 null 이어도 합친다.
중복해제거 (DISTINCT)
- 예제 : SELECT DISTINCT deptno FROM emp
제8회 Oracle SQL 문 - Where 절
Where 절
- 정의 : 조건
- 형식
- SELECT [DISTICT] {*, column [alias],...}
FROM <table이름>
[WHERE <조건들>];
- 예제
비교연산자 사용( <,>,<=,=>,=,<>)
- 예제 select ename,sal,comm
from emp
where sal < comm;
Between 연산자
- 정의 : 특정 범위에 대한 사용하는 연산자
- 형식 : Between A and B
- A와 B의 값을 모두 포함합니다.
- 예제
- select ename ,sal
from emp
where sal between 1000 and 1500;
In 연산자
- 정의 : IN 연산자 안에 있는 값에 대하여 테스트
- or 연산자와 비슷함
- 형식 : IN (a,b,c,d)
- a,b,c,d 값에 대해서 모두 시험하기
- 예제
- select empno, ename, sal, mgr
from emp
where mgr in(7902,7566,7788)
Like 연산자 사용
- 정의 : 검색 문자열 값에 대한 와일드카드 검색을 위해 사용
- % 는 문자가 없거나 하나이상
- _ 는 하나의 문자
- % , _ 를 조합하여 사용할 수 있다
- 실제 % 나 _ 를 조회할 때는 escape 문자(\)를 사용하여 조회 한다.
- 형식 : ‘S%’ , ‘S_’
- 예제
- select ename
from emp
where ename like ‘S%’ - select ename
from emp
where ename like '%A\_B%' escape '\';
IS NULL 연산자 사용
- 정의 : Null 값에 대한 테스트
- 형식 : is null
- 예제
- select ename, mgr
from emp
where mgr IS NULL;
AND 연산자 사용
- 정의 : AND 양쪽의 조건을 비교하여 참일 경우 true를 리턴
OR 연산자 사용
- 정의 : OR 양쪽의 조건을 비교하여 한 쪽이라도 참인 경우 true를 리턴
NOT 연산자 사용
- 정의 : 조건의 반대인 것만
연산자 우선 순위
- 비교연산자 > NOT > AND > OR
ORDER BY 사용
- 정의 : 행을 정렬할 때 사용
- ASC 오름차순
- DESC 내림차순
- NULL 값은 오름 차순에서는 제일 나중에 내림차순에서는 제일 먼저 나옴
- 열별칭을 사용할 수 있음
- 다중열을 이용하여 정렬할 수 있음. (생략시 asc값이 기본임)
- 예제
- select ename,job,deptno,hiredate
from emp
order by hiredate desc;
제9회 Oracle SQL 문 - 함수
단일 함수 정의
- 하나 이상의 인수를 받고 질의에 의해 리턴 될 각각의 행에 대해 하나의 값을 리터함.
- 임시 테이블 명 DUAL (참고#1)
대소문자 변환함수
- LOWER : 소문자로 변환
- UPPER : 대문자로 변환
- INITCAP : 첫번째 문자를 대문자로 변환 나머지는 소문자로 변환
문자 조작 함수
- CONCAT() : 값을 합친다. 두 개의 매개변수만 사용
- SUBSTR() : 지정된 길이 만큼의 문자열을 추출함.
- 1부터 시작 함.
- LENGTH() : 문자열의 길이를 숫자 값으로 반환
- INSTR() : 명명된 문자의 위치를 숫자 값으로 반환
- 1부터 시작함.
- LPAD() : 문자 값을 우측부터 채웁니다.
- 예: LPAD(‘1000’,5,’*’);
숫자함수
- ROUND : 명시된 소수점으로 반올림
- 예) ROUND(23.786,2) : 23.79
- 0, -1 , 1
- TRUNC : 명시된 소수점에서 버림
- 예) TRUNC(23.786,2) : 23.78
- MOD : 나누기를 한 후의 나머지 값
- 예) MOD(1600,300) : 100
날짜 연산
- 날짜 + 숫자 = 날짜
- 날짜 - 숫자 = 날짜
- 날짜 - 날짜 = 일수
- 날짜 + 숫자/24 = 날짜
- SYSDATE : 현재 날짜와 시간을 반환
- DUAL : 더미 테이블 , 모든 사용자가 사용 가능함.
- TO_DATE : 주어진 문자열을 날짜로 변환
날짜 함수
- MONTHS_BETWEEN(date1, date2) : date1 와 date2 사이의 월수를 리턴
- ADD_MONTHS(date,n) : 월수(n)을 date에 추가함.
- NEXT_DAY(date,’char’) : date다음의 명시된 요일의 날짜를 리턴
- LAST_DAY(date) : date를 포함하는 월의 마지막 날짜를 리턴
테이터형 변환
- TO_CHAR() : number와 date 형을 varchar2 문자 스트링으로 변환
- TO_NUMBER() : 숫자를 포함하는 문자 스트링을 숫자로 변환
- TO_DATE() : 날짜를 나타내는 문자 스트링을 날짜형으로 변환
NVL 함수
- NULL 값을 실제 값으로 변환
DECODE 함수
- case나 IF-THEN-ELSE 문장의 조건적 조회를 가능하게 함.
- 예)
- select ename, deptno, decode(deptno,10,’Accountin’,
20,’Research’,’Default’)
from emp;
- 현재 관련 예제에 오류가 있음.
제10회 Oracle SQL 문 - JOIN
JOIN 정의
- 한 테이블 이상에서 데이터를 질의해 가져 오는 것을 말함.
- 종류
- Equi Join
- None Equi Join
- Outer Join
- Self Join
- 사용방법
- select table1.col, table2.col
from table1, table2
where table1.col1 = table2.col2 - where 조건절에 join 조건을 작성합니다.
- 여러 개의 테이블에서 같은 이름의 칼럼이름을 사용한다면 컬럼이름 앞에 테이블 이름을 넣어 주어야 한다.
- 주의해야 할것
- Cartesian Product는 조인 조건이 생략될 때 발생하는데 만약 두개의 테이블이 존재한다면 첫번째 테이블의 모든행이 두번째 테이블의 모든행과 조인이 되는 것
- 튜닝
Join의 종류
- Equi Join
- Where 절에 ‘=’이 들어간 조인
- 앞부분이 데이타 많은 것을 넣는다. 그러면 튜닝됨
- Nonequi Join
- ‘=’ 아닌 다른 operator를 사용한 경우
- 예
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
- Outer join
- 일반적인 조인으로 얻을 수 없는 데이터를 구하고자 할 때
- 조인 조건을 만족하지 않는 행들도 보기 위해서 함.
- 상세 공부가 필요함.
- 참고#1
- 예
select e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno (+)= d.deptno
order by e.deptno
- Self join
- 두 개이상의 테이블이 하나의 테이블일 경우 즉 한 테이블에서 서로를 조인하는 경우
- 오라클을 느리게 할 수 있으니 주의 해서 사용해야 한다.
테이블 별칭
- 30자 까지 가능
- FROM 절에서 특별한 테이블 명을 위해서 사용됨.
- SELECT 문장 전체에서 테이블 명 대신에 사용 되어야 한다
- 의미가 있어야 함
- 현재의 SELECT 문장에 대해서만 유효
제11회 Oracle SQL 문 - 그룹 함수
정의
- 그룹당 하나의 결과가 주어지도록 행의 집합에 대해 연산을 하는 함수를 말합니다.
그룹함수의 종류
- 7가지
- AVG(평균)
- COUNT : NULL 값을 무시하지 않고 계산 ,나머지들은 무시
- MAX
- MIN
- STDDEV : 표준 편차
- SUM
- VARIANCE : 분산
- 숫자 데이터를 저장할 수 있는 열에 대해서
- AVG,SUM,VARIANCE, STDDEV 사용가능
- AVG와 SUM
- COUNT
예
- select mgr,avg(deptno)
from emp
group by mgr
having avg(deptno) >= 20
order by mgr asc;
제12회 Oracle SQL 문 - Sub Query
Main Query 와 Sub Query
정의
- 다른 sql문에 내장되어 있는 select 문장이라고 생각하면 됨
select 문장이 독립적으로 있는 것이 아니라 다른 sql 문장 안에 들어 있는 것
서브쿼리 형식
- select select_list from table : Main Query
where <연산자>(select select_list from table) : Sub Query - <연산자> : 비교연산자(>,<,=,in,...) 오른쪽에 괄호로 감싸서 나타낸다 having 절이나
from 절 에도 올 수 있다. - 괄호로 둘러 싸여져야 한다.
- 비교연산자의 우측에 있어야
- 단일행 서브쿼리에는 단일행 연산자
- 다중행 서브쿼리에는 다중행 연산자 (IN, Between)
- ANY : or 조건
- ALL : and 조건
수행 순서
- Sub Query가 먼저 실행되고 결과가 나온 다음 Main Query가 실행 된다.
예
- select * from emp
where sal >= (select sal from emp where ename=’SMITH’);
제13회 Oracle 테이블
정의
- 테이터를 저장하는 논리적 저장창고 이며 테이터베이스 스키마에 가장 근본이 되는 객체
테이블 생성
- CREATE TABLE 명령어로 생성
- 테이블 명은 문자로 시작해야 하고 문자 길이는 30자 이내여야 함
- A-Z,a-z,0-9,_,$,#
- 중복된 이름은 사용할 수 없음
다른 사용자 테이블 참조
- 테이블의 접두어로서 소유자 이름을 사용하면됨
- 예 scott.emp
사용자 시스템 테이블 조회 (각종 조회:링크)
- 사용자가 가지고 있는 테이블 목록 조회
- select * from USER_TABLES;
- 사용자가 가지고 있는 객체 타입 조회
- select DISTINCT OBJECT_TYPE from USER_OBJECTS;
- 사용자가 가지고 있는 테이블, 뷰
- select * from USER_CATALOG
다른 테이블에서 조회해서 새로운 테이블 생성하기
- create table dept_test2
as select empno,ename, sal * 12 as sal, hiredate
from emp where deptno = 30;
테이블 변경하기
- 컬럼 추가 : alter table <테이브명> add (<컬럼 정보>)
- 컬럼 변경 : alter table <테이브명> modify (<컬럼 정보>)
- 테이블 명 변경하기
- rename <old 테이블명> to <new 테이블명>
- 테이블의 모든 내용을 삭제하기
- truncate table <테이블명>
- 롤백 불가
- delete table <테이블명>
테이블 삭제
- drop table <테이블명>
제14회 Oracle 테이블 제약 조건
정의
- 부적합한 테이터가 테이블에 삽입되는 것을 방지 하기 위해 constraint 를 사용한다
종류
- NOT NULL : 이 열은 NULL값을 허용하지 않는다
- UNIQUE KEY : 테이블의 모든 행에 대해 유일한 값을 가져야 한다.
- PRIMARY KEY : UNIQUE + NOT NULL 테이블에 오직 하나만 존재
- FOREIGN KEY : 다른 테이블에서 참조하는 역할을 함.
조회
- 제약조건 조회
- select * from user_constraints;
생성
- 예:create table emp5(
empno number(4),
ename varchar2(10),
deptno number(7,2) not null,
constraint emp5_empno_pk primary key(empno),
constraint emp5_deptno_fk foreign key(deptno) references dept(deptno),
constraint emp5_ename_uk unique(ename)
); - 제약조건 추가
- alter table emp5
add constraint emp5_deptno_fk foreign key (deptno)
references dept(deptno)
- 제약조건 변경
- alter table emp5
modify ename not null
삭제
- 예
- alter table emp5
drop constraint emp5_deptno_fk
View 란
- 편리하게 보기 위해서 작성된다.
- 뷰와 snapshot의 차이점
- view : select 문을 쉽게 보기 위한 것(
- snapshot은 select 결과를 저장하기 위한 것
- 생성
- create view test_view
as select empno, ename, deptno from emp
where deptno=20
- 생성을 위해서 create view 관련 권한이 있어야 한다.
제15회 Oracle 시퀀스
정의
- 자동적으로 유일한 번호를 생성하는 오라클 데이터베이스의 객체의 하나임
- 한 사용자만 사용하는 것이 아니라 여러 사용자가 사용 가능 함.
- PRIMERY KEY나 UNIQUE KEY를 만들 때 자주 사용됨.
- 시퀀스를 번호를 재활용 할 수 없음. --> 여러 테이블에서는 사용하지 않는 것이 좋다.
생성
- create sequence <시퀀스이름>
increment by <증가치> :
start with <시작값> :
maxvalue <최대값> : 기본값은 10^27
minvalue <최소값> :
cycle : 순환여부 (nocycle)
cache : 메모리를 사용여부 (nocache)
조회
- select * from user_sequences;
시퀀스 사용하기
- 다음 값을 가져오기
- select <시퀀스이름>.nextval from dual;
- 현재 값을 가져오기
- select <시퀀스이름>.currval from dual;
시퀀스 삭제하기
- drop sequence <시퀀스이름>
인텍스 생성
- create index test_2 on emp(ename);
인텍스 조회
- select * from user_indexes;
인텍스 삭제
- drop index test_2;
| Google 문서: 온라인에서 문서를 만들고 수정해 보세요. Google LLC, 1600 Amphitheatre Parkway, Mountain View, CA 94043, USA 다른 사용자가 Google 문서의 문서를 나와 공유하여 발송된 이메일입니다. |  |
