[17]OracleDB

작성일 | In

OracleDB

  • 무료 배포 버전
  • 주의사항 : 컴퓨터 이름이 한글이면 설치가 용이하지 않을 수 있다.
    • 컴퓨터 포맷 후 바로 설치할 것을 추천
  • 다운로드 경로
    • https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
    • Oracle Database 11g Release 2 Express Edition for Windows 64
    • OracleXE112_Win64.zip
    • Database 11g Express Edition: 개인 개발자용
      • Intel Core2 Duo 2.9 GHz, 데이터가 11GB를 넘을 수 없음, 메모리가 1GB를 넘을 수 없음, CPU 1개에서만 실행가능.
    • Database 11g Enterprise/Standard Editions
      • Intel i3 이상, RAM 4GB, 오라클 서버의 모든 기능을 제공
  • Oracle 의 접근 Port 번호는 8080 (수정가능.)
    • 만약 다른 프로그램의 Port번호가 8080이거나 겹치면 바꿔주어야함
  • 내가 만든 데이타베이스에 접근하는 Port 번호 1521
  • 최고관리자 계정
  • Oracle: system 또는 sys
    • MySQL : root
    • MS-SQL: sa

Oracle Server 서비스 확인

  1. 시작 -> 서비스 -> OracleServiceXE 확인
  2. 시작 -> 모든프로그램 - Oracle Database 11g Express Edition -> Start Database / Stop Database

Oracle 명령어 입력 툴

  1. Oracle SQL Developer

    1. SQL Developer에서 ER Diagram 만드는 법.

      ->sqldeveloper -> 파일 ->Data Modeler -> 임포트 -> 테이블간의 관계설정을 다 해줌(ERdiagram)

  2. 시작 -> 모든프로그램 -> Oracle Database 11g Express Edition -> Run SQL Command Line

    -> connect 입력 후 아이디/비번 입력 (비밀번호는 화면에 안보임)

  3. 시작 -> cmd -> sqlplus 입력 후 아이디/비번 입력 (비밀번호는 안보임)

  4. 관리자 계정 아이디 system 또는 sys(as sysdba) 비밀번호는 지정(다운로드시)

Oracle 명령어 입력

  • Run Command Line

    • SQL -> connect

      ​ -> 아이디 / 비번

      ​ -> connected. (확인하기)

  • SQL Developer : 별도 설치 (오라클 로그인 필요)

  • 웹브라우저 : 시작 -> Oracle DB 11g XE -> Get Started -> http://127.0.0.1:8080/apex 또는 http://localhost:8080/apex

  • Eclipse : DataSource 연결

  • sqlplus : 시작 -> cmd -> sqlplus 아이디/비번 로그인.

데이터베이스 관련 명령어

  • SQL문(Stored Query Language)
  • 대소문자를 구분하지 않음
  • 종결문자 ;
  • ANSI(표준) 와 자신만의 DB명령어

테이블 스페이스 생성

  • Oracle DB
  • cmd -> sqlplus / as sysdba 로그인 후 진행
1
2
3
데이터베이스 생성
- My-SQL , Maria DB , MS-SQL
- create database DB명
  • 형식

데이터저장 파일경로는 c: 는 저장안됨.

예시) d:\java0514\java0514.dbf

1
2
3
4
5
create tablespace 테이블스페이스이름
datafile '데이터파일경로' size 초기사이즈
autoextend on
next 자동증가사이즈
maxsize 최대사이즈;

예시

1
2
3
4
5
SQL>create tablespace java0514
    datafile 'd:\java0514\java0514.dbf' size 50m
    autoextend on
    next 10m
    maxsize unlimited;
  • Oracle DB 서버 사용자 계정 만들기

인증받은 id에 한해서만 테이블스페이스에 저장하기.

exit 는 빠져나감!

1
2
create user 아이디 identified by 비번
default tablespace 테이블스페이스이름;

예시

exit로 빠져나온다음 sqlplus 아이디/비번 으로 로그인 하면 거부당함 이때 권한을 부여해야한다

1
2
SQL>create user java0514 indentified by 1234
default tablespace java0514;
  • 사용자 계정 삭제하기
1
drop user 아이디 cascade;
  • 사용자 권한 부여

connect 접속권한

resource 데이터베이스안에 있는 자원들을 사용할 수 있는 권한

dba 관리자권한

sqlplus / as sysdba 로그인 (super user)

1
grant connect, resource, dba to 아이디;

예시

1
grant connect, resource, dba to java0514;
  • 권한 취소
1
revoke 권한종류1, 권한종류2 from 계정

예시

1
revoke connect, resource from ora1210
  • 계정 수정
1
alter user 계정명 수정사항
  • 사용자 삭제
1
 DROP USER userid CASCADE;

SEQUENCE 시퀀스

  • 오라클에서 자동으로 일련번호를 부여
  • Oracle : 시퀀스
  • MySQL : IDENTITY
  • 시퀀스나 아이덴티티는 화면상 노출이 목표가 아니라 WHERE 조건을 쓰기 위함
  • 기술방법
    • 시퀀스명
    • INCREMENT BY 증가값 : 시작 값에서 증가할 값을 기술
    • START WITH 시작값 : 시퀀스의 초기값
    • MAXVALUE 최댓값 : 시퀀스의 최종값
    • MINVALUE 최솟값 : 시퀀스의 최솟값
    • CYCLE 반복횟수 : 반복횟수
    • CHACHE : 캐시 메모리에 저장한다
    • ORDER : 요청 순서대로 생성
  • 시퀀스 생성

옵션지정하지 않으면 자동으로 1부터 시작해서 1씩 증가

1
CREATE SEQUENCE 시퀀스명;

옵션 예시

1
2
3
CREATE SEQUENCE 시퀀스명
INCREMENT BY  증가값
START WITH 시작값;
  • 시퀀스 호출 함수

주의 : 시퀀스 생성 후 NEXTVAL을 호출해야 시퀀스에 초기값이 설정됨

임시테이블 DUAL

NEXTVAL : 다음값을 반환함. 다음번호 발급

1
SELECT 테이블명.NEXTVAL FROM DUAL;

CURRVAL : 현재값을 반환함. 최근 발급된 번호

1
SELECT 테이블명.CURRVAL FROM DUAL;
  • 시퀀스에서 일련번호 발생.
1
시퀀스명.NEXTVAL
  • 시퀀스 삭제
1
DROP SEQUENCE 시퀀스명;

시퀀스 문제

테이블에 데이터 입력시 시퀀스를 이용해서 id를 입력하도록. 206에서 시작하여 1씩 증가되고, 최대값은 999로 설정하여 시퀀스 생성하기.

시퀀스 이름 : test_seq

1
2
3
4
CREATE SEQUENCE test_seq
INCREMENT BY 1
START WITH 206
MAXVALUE 999;

시퀀스 목록 조회

1
2
SELECT * FROM USER_OBJECT
WHERE OBJECT_TYPE='SEQUENCE';

NVL() 함수를 사용해 일련번호 구하기

NVL() 함수를 이용한다.

  • NVL() 함수 : NULL값을 포함하는 칼럼을 특정한 값으로 전환

형식 : NVL(NULL값을 포함하고 있는 칼럼이나 식, 전환하고자 하는 목표값)

-> NULL값이 나오면 0으로 대체

1
SELECT NVL(칼럼 또는 식,0) FROM 테이블;

서브쿼리를 이용한 일련번호 구하는 식

1
2
3
4
5
6
7
8
9
SELECT NVL(칼럼 또는 식,0)+1 FROM 테이블;

INSERT INTO 테이블명 (일련번호, 칼럼1, 칼럼2, 칼럼3)
VALUES(
	(SELECT NVL(칼럼 또는 식,0)+1 FROM 테이블)	--일련번호
	,데이터1
	,데이터2
	,데이터3
);

Oracle DB 자료형

  1. 숫자형
    • INT 표준형
    • NUMBER 정수형
    • NUMBER() 실수형
      • ex ) NUMBER(5,2) -> 최대 5자리, 소수점2자리까지 표현 -> (999.99) 까지 표현가능
  2. 문자형
    • CHAR 표준형 , 고정형
      • ex) CHAR(5) : 5글자를 잡음 -> ‘SKY ‘ : 2칸의 공백이 있음.
      • ex) 우편번호
      • 속도는 고정형이 빠름
    • VARCHAR 표준형 , 가변형
      • ex) VARCHAR(5) : 5글자 잡음 -> ‘SKY’ : 남은 2칸은 제외됨. 가변형!
      • ex) 아이디, 비밀번호, 이름, 주소 ~~ (쓰이는 곳이 많음)
    • VARCHAR2 가변형
      • 4000 까지 가능
  3. 날짜형 (년월일시분초)
    • DATE
      • 현재 시스템 날짜값 함수 : SYSDATE

시퀀스 및 자료형 연습

  • 시퀀스 생성
1
CREATE SEQUENCE sungjuk_seq;
  • sungjuk 테이블 생성

not null : 빈 값을 허용하지 않음.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE sungjuk(
sno NUMBER NOT NULL 			--일련번호
,uname VARCHAR2(255) not null	--이름
,kor NUMBER NOT NULL			--국어
,eng NUMBER NOT NULL			--영어
,mat NUMBER NOT NULL			--수학
,aver NUMBER					--평균
,addr VARCHAR2(255)				--주소
,wdate DATE						--작성일
);
  • 행 추가 (Sample Data)

칼럼 생략할 수 있지만 알아보기 어려우니 생략하지 말자.

VALUES순서가 중요함

시퀀스의 일련번호로 순서를 정해주기 위함. >

​ sno 칼럼에 sungjuk_seq.NEXTVAL 순서를 넣어준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL
	   ,'무궁화',100,95,80,'Seoul',SYSDATE);
INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'진달래',90,50,90,'Jeju',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'개나리',20,50,20,'Jeju',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'봉선화',90,90,90,'Seoul',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'나팔꽃',50,50,90,'Suwon',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'선인장',70,50,20,'Seoul',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'소나무',90,60,90,'Busan',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'참나무',20,20,20,'Jeju',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'홍길동',90,90,90,'Suwon',SYSDATE);

INSERT INTO sungjuk(sno,uname,kor,eng,mat,addr,wdate)
VALUES(sungjuk_seq.NEXTVAL,'무궁화',80,80,90,'Suwon',SYSDATE);

레코드 갯수 확인

1
SELECT COUNT(*) FROM sungjuk;
  • 이름 ‘무궁화’를 조회하시오
1
2
3
SELECT *
FROM sungjuk
WHERE uname='무궁화';
  • 국어점수 50점 이하 조회하시오
1
2
3
SELECT *
FROM sungjuk
WHERE kor<=50;
  • 국영수 모두 90점 이상 조회하시오
1
2
3
SELECT *
FROM sungjuk
WHERE kor>=90 and eng>=90 and mat>=90;
  • 이름 개나리, 진달래, 무궁화 조회하시오
1
2
3
SELECT *
FROM sungjuk
WHERE uname in('개나리','진달래','무궁화');
  • 주소가 서울인 레코드의 평균을 구하시오
1
2
3
4
5
6
7
UPDATE sungjuk
SET aver=(kor+eng+mat)/3
WHERE addr='Seoul';

SELECT *
FROM sungjuk
WHERE addr='Seoul';
  • 수학점수 50~59점 사이 레코드를 조회하시오
1
2
3
4
SELECT *
FROM sungjuk
WHERE mat>=50 and mat<=59;
--또는 WHERE mat 	BETWEEN 50 AND 59;
  • 비어있는 평균값을 모두 구하시오
1
2
3
4
5
6
UPDATE sungjuk
SET aver=(kor+eng+mat)/3
WHERE aver is null;

SELECT *
FROM sungjuk;
  • 평균 70점이상 레코드를 이름순으로 정렬해서 조회하시오

ex) 게시판테이블에서 가장 최근에 작성한 글 순서로 출력

order by wdate desc –내림차순

08

1
2
3
4
SELECT *
FROM sungjuk
WHERE aver>=70
ORDER BY uname;
  • aver 칼럼의 데이터타입 number(6,2)로 수정하시오

aver 값이 있으면 안된다. null 초기화 시킨 후 구조를 바꾸고 UPDATE를 한다.

값이 있으면 구조변화가 안된다.

UPDATE sungjuk SET aver=null;

1
2
ALTER TABLE sungjuk
MODIFY(aver NUMBER(6,2));
  • 수학점수 50점 미만 학생들에게 수학점수 5점씩 추가하시오
1
2
3
UPDATE sungjuk
SET mat=mat+5
WHERE mat<50;
  • 이름에 ‘화’ 문자가 들어가 있는 레코드만 검색

LIKE 연산자는 문자열에 사용!!

1
2
3
SELECT *
FROM sungjuk
WHERE uname LIKE '%화%';
  • 주소가 ‘Seoul’ , ‘Jeju’이면서 이름에 ‘나’ 글자가 포함되어 있는 레코드 조회
1
2
3
SELECT *
FROM sungjuk
WHERE addr IN('Seoul','Jeju') AND uname LIKE '%나%';
  • 국영수 과목의 각 평균을 구하시오

AS : 특정 칼럼명, 테이블명을 일시적으로 리네임

1
2
3
4
SELECT AVG(kor) AS avg_kor,
AVG(eng) AS avg_eng,
AVG(mat) AS avg_mat
FROM sungjuk;

Oracle 함수

문자열 함수

  • LOWER : 해당 칼럼의 값을 소문자로 변환

dual은 임시 테이블

1
SELECT LOWER('Hello World') FROM dual;
  • UPPER : 대문자로 변환
1
SELECT UPPER('Hello World') FROM dual;
  • CONCAT : 두개의 문자열을 연결
1
SELECT CONCAT('Hello','World') FROM dual;
  • SUBSTR : 문자열 추출

6번째 자리부터 전체를 추출

1
SELECT SUBSTR('HelloWorld',6) FROM dual;

1~5번째까지 추출

1
SELECT SUBSTR('HelloWorld',1,5) FROM dual;
  • INSTR : 문자열에서 특정문자의 위치를 세는 함수, 특정문자가 없으면 0
1
SELECT INSTR('HelloWorld', 'W') FROM dual;
  • TRIM : 공백을 제거
1
SELECT TRIM(' SQLPLUS') FROM dual;
  • LTRIM : 좌측부터 문자열에서 해당 문자를 제거

우측에 제거하는 기호가 몇개가 있어도 해당된다면 모두 다 제거

1
SELECT LTRIM('*SQLPLUS', '*') FROM dual;
  • RTRIM : 우측부터 문자열에서 해당 문자 제거
1
SELECT RTRIM('SQLPLUS*', '*') FROM dual;
  • REPLACE : 문자열에서 해당 문자를 다른 문자로 바꾸어 줌
1
SELECT REPLACE('SEVLTL', 'L', 'EN') FROM dual;

숫자함수

  • ABS : 절대값
1
SELECT ABS(-7) FROM dual;
  • MOD : 나머지 반환

100

1
SELECT MOD(1500, 200) FROM dual;
  • CEIL

2

1
SELECT CEIL(1.123) FROM dual;

-1

소수점 첫째자리에서 해당 값을 올림 처리한 정수를 반환하고 해당 값보다는 크지만 가장 근접하는 최소값을 구하는 함수

1
SELECT CEIL(-1.623) FROM dual;
  • FLOOR

1

1
SELECT FLOOR(1.123) FROM dual;

-2

소수점 첫째자리에서 해당 값을 내림 처리한 정수를 반환하고 해당 값보다는 작지만 가장 근접하는 최대값을 구하는 함수

1
SELECT FLOOR(-1.123) FROM dual;
  • ROUND(n,m) : 해당 숫자 n에m자리까지 반올림
1
2
3
4
5
SELECT ROUND(17.825, 2) FROM dual;		--17.83
SELECT ROUND(17.825, 1) FROM dual;		--17.8
SELECT ROUND(17.825, 0) FROM dual;		--18
SELECT ROUND(17.825, -1) FROM dual;		--20
SELECT ROUND(17.825, -2) FROM dual;		--0
  • TRUNC(n,m) : 해당 숫자 n에서 m자리 까지 버림
1
2
3
4
5
SELECT TRUNC(17.825, 2) FROM dual;		--17.82
SELECT TRUNC(17.825, 1) FROM dual;		--17.8
SELECT TRUNC(17.825, 0) FROM dual;		--17
SELECT TRUNC(17.825, -1) FROM dual;		--10
SELECT TRUNC(17.825, -2) FROM dual;		--0

날짜 함수

  • SYSDATE : 현재 날짜, 시간
1
SELECT SYSDATE FROM dual;	--14/03/24
  • MONTHS_BETWEEN(n,m)

첫번째 날짜에서 두번째 날짜 사이 개월 수를 반환하는 함수.

MONTHS_BETWEEN(m,n) 순서바꿔도됨

1
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE(2014-12-11)) FROM dual;
  • ADD_MONTHS(m,n)

해당 날짜(m)에 개월 수(n)를 더한 날짜를 반환, 마이너스값도 할 수 있음.

1
SELECT ADD_MONTHS(SYSDATE,5) FROM dual;
  • NEXT_DAY(m,’n’)

해달 날짜(m)에서 최초로 도래하는 해당 요일(‘n’)의 날짜를 반환하는 함수

1
SELECT NEXT_DAY(SYSDATE,'금요일') FROM dual;	--14/03/28
  • LAST_DAY(m)

해달 날짜가 포함되어 있는 달의 마지막 날짜를 반환하는 함수.

1
SELECT LAST_DAY(SYSDATE) FROM dual;			--14/03/31

형변환 함수

  • TO_CHAR(원래날짜,’원하는 모양’)

숫자와 날짜를 문자로 변환해 주는 두가지 기능

  • TO_NUMBER(‘1’)
  • TO_DATE(‘문자’,’날짜포맷’)

함수 연습

  • 국영수 과목의 각 평균을 반올림해서 소수점 두 자리까지 출력 하시오.
1
2
3
4
5
SELECT
ROUND(AVG(kor),2) AS avg_kor,
ROUND(AVG(eng),2) AS avg_eng,
ROUND(AVG(mat),2) AS avg_mat
FROM sungjuk

연습

0624image01

  • 테이블 구조
1
2
3
4
5
6
7
8
9
10
CREATE TABLE emp(
empno INT 				NOT NULL
,ename VARCHAR(10) 			NOT NULL
,job VARCHAR(10) 			NOT NULL
,mrg CHAR(4) 				NOT NULL
,hiredate DATE 				NOT NULL
,sal INT				NOT NULL
,comm INT
,deptno INT	 			NOT NULL
);
  • 행 삽입
1
2
3
4
5
6
INSERT INTO emp(empno,ename,job,mrg,hiredate,sal,comm,deptno)
VALUES(7369,'smith','clerk','7902','1980-12-17',800,null,20);
INSERT INTO emp(empno,ename,job,mrg,hiredate,sal,comm,deptno)
VALUES(7499,'allen','salesman','7698','1981-02-20',1600,300,30);
INSERT INTO emp(empno,ename,job,mrg,hiredate,sal,comm,deptno)
VALUES(7521,'ward','salesman','7698','1981-02-22',1200,500,30);

0624image02