학원 32일차 - Oracle

仁·2021년 7월 10일
오라클학원
0

2021.05.12

ex01.sql

Oracle, SQL Developer 설치

  • Oracle

    oracle.com > Software > Oracle Database > Download Oracle Database 19c > 사이트 하단의 Oracle Database 11g Release 2 Express Edition for Windows 64

  • SQL Developer

    oracle.com > Windows 64-bit with JDK 8 included

  • 데이터베이스 계정 (관리자)암호 설정

주석

-- 단일라인 주석

/*
    다중라인 주석
*/

오라클, Oracle

  • 회사명, 제품명
  • 데이터베이스(Database) -> 데이터베이스 관리 시스템(Database Management System, DBMS) -> 관계형 데이터베이스 관리 시스템(Relational DBMS, RDBMS)
  • 프로젝트하실 때 DBMS 뭐 쓰셨어요? -> Oracle 11g 사용했습니다.
  • 프로젝트하실 때 DB 클라언트는 뭐 쓰셨어요? -> SQL Developer 사용했습니다.

다운로드

  1. OracleXE112_Win64.zip
    • 데이터베이스 프로그램(DMBS)
  2. sqldeveloper-20.4.1.407.0006-x64.zip
    • 데이터베이스 클라이언트 프로그램

데이터, Data

  • 가공된 정보

데이터베이스, Database

  • 데이터의 집합
  • 데이터의 집합을 지원하는 프로그램
  • 오라클(Oracle)

데이터베이스 관리 시스템, Database Management System

  • 데이터베이스 + 추가 작업 -> 통합 시스템
  • 오라클(Oracle)

관리 시스템의 필요성 -> 아래의 기능들을 지원

  1. 데이터 무결성

    • 데이터에 오류가 있으면 안된다.
    • 제약 조건(Constraint)를 사용한다.

  2. 데이터 독립성

    • 데이터베이스에 변화가 발생하더라도 관계된 응용 프로그램들은 영향을 받지 않는다.

  3. 보안

    • 데이터베이스내의 데이터를 함부로 접근 방지
    • 소유주나 접근 권한이 있는 사용자만 접근 가능... 통제 가능

  4. 데이터 중복 최소화

    • 동일한 데이터가 여러곳에 여러번 저장되는 것을 방지한다.

  5. 데이터 안정성

    • 데이터 백업/복원 기능들 제공한다.

DBMS 종류

  1. 계층형 DBMS
  2. 망형 DBMS
  3. 관계형 DBMS > 현재 > 데이터를 표형태로 저장/관리
  4. 객체지향형 DBMS
  5. 객체관계형 DBMS

관계형 데이터베이스 관리 시스템 -> 제품 종류

  1. Oracle

    • Oracle
    • 기업용

  2. MS-SQL

    • Microsoft
    • 기업용

  3. MySQL

    • Oracle
    • 개인용 + 기업용

  4. MariaDB

    • MySQL 기반
    • 무료
    • 개인용 + 기업용

  5. PostgreSQL

    • 포스트그레스큐엘
    • 무료
    • 개인용 + 기업용

  6. DB2

    • IBM
    • 메인프레임

  7. Access

    • MS
    • 개인용 + 소규모

  8. 티베로

    • 티맥스(TMax)

  9. SQLite

    • 경량
    • 모바일 등..

Oracle(데이터베이스 서버)

-> UI가 없는 프로그램 -> 서비스

데이터베이스 클라이언트 프로그램

-> UI가 없는 오라클에 접속을 해서 -> 조작을 도와주는 툴

  1. SQL Developer

    • 무료
    • Oracle

  2. Toad

    • 유료
    • 점유율 최상

  3. SQLGate

  4. DataGrip(JetBrain)

    • 30일 평가판
    • 대학교 이메일(무료)

  5. Eclipse

  6. SQL*Plus

    • 오라클을 설치하면 자동으로 같이 설치되는 클라이언트 툴 (cmd에서 사용)
    • CLI(Command Line Interface)

오라클 버전

  • Oracle 11g Express Edition -> 안정성이 높음
  • Oracle 1.0 ~ 21c

오라클 에디션

  • Express Edition
    • 무료. 상용 가능(개발용). 기능제한
    • 11g XE, 18c XE
  • Enterprise Edition
    • 상용

오라클 설치
-> 오라클이 잘 동작하고 있는 지 어떻게 확인??
-> 시작 or 종료는 어떻게 ???

  1. 실행창 -> services.msc
  2. OracleXXX
    • OracleServiceXE (실행중)
      • 데이터베이스 프로그램
    • OracleXETNSListner (실행중)
      • 오라클과 클라이언트 프로그램을 연결시켜주는 프로그램

  • OracleXETNSListener, OracleServiceXE 더블클릭 -> 시작유형(자동(자동으로 하면 컴퓨터 킬때 자동으로 실행됨, 컴퓨터가 무거워짐) or 사용안함(오라클을 한동안 사용을 안한다면 사용안함 선택.))

  • 프로그램이 동작안할때 services.msc에 들어가서 OracleServiceXE랑 OracleXETNSListner이 실행중 떠있는지 확인 할 것!!
  • 접속 - 새로만들기/데이터베이스 접속 선택

Name : 서버주소.계정명

Name : localhost.system

사용자 이름 : system

비밀번호 : java1234

비밀번호 저장: 체크

호스트 이름 : 오라클 서버 주소(도메인, IP)

포트(네트워크 프로그램은 포트를 통과해야한다. -> 1521번은 오라클) : 1521

SID(서비스 이름, 오라클은 여러개를 설치할 수 있음, 오라클 식별자) : xe (무료버전은 xe, 무료버전은 한번만 설치 가능.)

(열려 있는) 파일

-> 스크립트 파일(＊.sql), 워크 시크 파일(＊.sql)
-> 오라클(데이터베이스서버)와 대화를 하기 위한 작업 파일(자바 -> 소스파일(*.java))
-> 문장 단위 실행

스크립트 실행 방법

  1. 실행할 명령어(문장)을 선택한다.(블럭잡기)
    • 마우스 또는 Shift + 방향키(Home, End)
  2. 실행
    • Ctrl + Enter
  • 자바와 달리 코드의 실행 순서가 없음 -> 실행 하고 싶은 문장을 블럭 잡아서 실행한다.(= 문장 단위 실행)
show user;

select*from tabs;

계정

  1. 관리자 계정

    • sys, system

  2. 일반 사용자 계정

    • 권한이 일부 제한되어 있는 계정
    • 생성
    • 학습용 계정 제공 > scott, hr(사용 금지 -> 해제)

잠겨있는 계정 활성화 하기

-- 계정이 잠겨있다. -> 활성화
-- alter user 계정명 account unlock;
alter user hr account unlock; --User HR이(가) 변경되었습니다.

-- 비밀번호 변경
alter user hr identified by java1234; --User HR이(가) 변경되었습니다.

-- 위의 두줄을 한번에 가능 (계정 해제 & 비번 바꾸기)
alter user hr account unlock identified by java1234; 

-- system vs hr 어떤 계정으로 실행? -> ? -> (system)154 vs (HR)7 -> 명령어는 같아도 접속한 계정에 따라 결과가 달라진다.
select * from tabs;

-- 지금 작업하고 있는 계정의 이름을 알려줌
show user;


-- 오전수업진도 :  데이터베이스 서버 설치(오라클 서버) -> 데이터베이스 클라이언트 툴(SQL Developer) -> hr 계정 활성화 -> DB 서버 접속
  • HR계정에 연결

  • 현재 접속한 계정이 어떤 걸로 선택 되어있는지 체크!!!

DB 관련 직무

  1. DBA

    • 데이터베이스 관리자

  2. DB 프로그래머

    • DB 작업 전문

  3. 응용 프로그래머

    • DB 작업 일부

localhost.hr 구성요소

  • 테이블
  • 뷰
  • 인덱스
  • 패키지
  • 프로시저
    ...
    ..

관계형 데이터베이스

  • 데이터를 표형태로 저장/관리한다.
  • 데이터끼리의 관계를 관리한다.
  • 표(테이블)의 집합 ★

테이블

  • 열(컬럼)의 집합 > 테이블의 구조 > 스키마(Scheme)

열, Column

  • 컬럼, 필드(Field), 속성(Attribute), 특성(Property)
  • 세부 정보
  • 열 정의 : 테이블을 선언하는 작업, 테이블의 구조 정의 , 같은 열에는 같은 형식의 데이터만 넣을 수 있음

행, Row

  • 행, 로우, 레코드(Record), 튜플(Tuple)
  • 테이블에 실체화된 데이터 1건
  • Object(객체)
  • 열들의 집합, 각 열에 해당하는 데이터의 집합

클라이언트(서비스를 제공받는 사람) <-> 오라클

  • 명령을 주고 받음 > 오라클이 알아들을 수 있는 언어로 주고 받아야함. > SQL 사용

SQL, Structured Query Language

  • 구조화된 질의 언어
  • 사용자(클라이언트 툴)가 관계형 데이터베이스와 대화할 때 사용하는 언어
  • 자바에 비해 자연어에 가깝다.

  1. DBMS 제작사와 독립적이다.

    • SQL은 모든 DBMS에 공통이다.

  2. 표준 SQL(ANSI-SQL)

    • 모든 DBMS는 표준 SQL를 지원하도록 설계되어 있다.
    • 버전 : SQL-86 ... SQL92... SQL-2011

  3. 대화식 언어다.

    • 질문(명령어) > 답변 > 질문 > 답변 > 질문...

SQL종류 (오라클 기준)

  1. ANSI SQL
    • 표준 SQL(모든 제품에 동일)
  2. PL/SQL
    • 자체 SQL
    • 오라클에서만 동작되는 명령어(제품의 차별성)

ANSI SQL 종류

  • 명령어들을 성격에 따라 분류
  1. DDL
    • Data Definition Language
    • 데이터 정의어
    • 구조를 만드는 언어
    • 테이블, 뷰, 사용자, 인덱스 등의 객체(DB Object)를 생성,수정,삭제하는 명령어
      a. create : 생성
      b. drop : 삭제
      c. alter : 수정
    • 데이터베이스 관리자
    • 데이터베이스 담당자
    • 프로그래머(일부)
  2. DML
    • Data Manipulation Language
    • 데이터 조작어
    • 데이터베이스의 데이터를 추가,수정,삭제,조회하는 명령어
      a. select : 읽기(****)
      b. insert : 추가
      c. update : 수정
      d. delete : 삭제
    • 데이터베이스 관리자
    • 데이터베이스 담당자
    • 프로그래머(***)
  3. DCL
    • Data Control Language
    • 데이터 제어어
    • 계정, 보안, 트랜잭션 등을 제어
      a. commit
      b. rollback
      c. grant
      d. revoke
    • 데이터베이스 관리자
    • 데이터베이스 담당자
    • 프로그래머(일부)
  4. DQL
    • Data Query Language
    • 데이터 질의어
    • DML 중에 select만을 이렇게 따로 칭한다.
  5. TCL
    • Transaction Control Language
    • DCL중에 commit, rollback만을 이렇게 따로 칭한다.

오라클 기본 인코딩

  • ~ 8i버전 : EUC-KR
  • 9i 버전 ~ : UTF-8 ★
-- 현재 계정(HR)이 소유하고 있는 테이블 목록 보여주세요.
-- SQL은 키워드의 대소문자를 구분하지 않는다.
select * from tabs; -- 수업
SELECT * FROM tabs; -- 대문자 표기를 더 많이 사용 (가독성이 좋아서)

-- 소문자로 적고 Alt + 홑따옴표 -> 대문자로 변환
SELECT * FROM TABS;

-- Ctrl + Space
SELECT
    *
FROM tabs;

ex02_datatype.sql

ANSI-SQL 자료형

  • 오라클 자료형
  • 데이터베이스 > 데이터 > 자료형

  1. 숫자형

    • 정수 + 실수

      a. number

      • (유효자리) 38자리 이하의 숫자를 표현하는 자료형

      • 5~22byte

      • 1x10-130 ~ 9.99999x10+125 -> 무한이라고 생각하자..

      • number(precision, scale)

        1. precision : 소수 이하를 포함한 전체 자릿수(1~38)
        2. scale : 소수 이하의 자릿수(0~127)
          ex) number -> 38자리까지 표현 가능한 모든 숫자(정수, 실수 포함)
          ex) number(3) -> 3자리까지 표현 가능한 숫자(정수) -> -999 ~ +999
          ex) number(4,2) -> 4자리까지 표현 가능 숫자(실수) -> -99.99 ~ +99.99
          ex) number(10,3) -> -9999999.999 ~ +9999999.999

      • 숫자형 상수(리터럴)

        1. 정수: 123
        2. 실수: 3.14

      • byte, int, float -> 오라클? -> 사용X

-- 테이블 삭제하기
drop table tblType;

-- 테이블 생성하기
create table tblType (
    -- 컬럼 구성
    -- 컬럼명 자료형
    
    -- num number     -- 38자리까지 표현 가능(정수만 가능)
    -- num number(3)  -- 3자리까지 표현 가능(정수만 가능)
    num number(4,2)   -- 소수2자리 포함 4자리까지 표현 가능(실수만 가능)
    
);


-- 테이블의 모든 내용을 가져오시오.
-- 에러 : ORA-00942: table or view does not exist (테이블이 안만들어졌거나 테이블이름에 오타가 났을때 -> 테이블이 존재하지 않습니다.)
select * from tblType;


-- 데이터 추가하기
insert into tblType (num) values (100); 
insert into tblType (num) values (200);
insert into tblType (num) values (300);
insert into tblType (num) values (99.99);
insert into tblType (num) values (99.9999999999);
insert into tblType (num) values (3.14);
insert into tblType (num) values (9999); -- 에러 : ORA-01438: value larger than specified precision allowed for this column (자릿수를 넘은 수라서)
insert into tblType (num) values (12345.6789);
insert into tblType (num) values (12345678901234567890123456789012345678); -- 38자리
insert into tblType (num) values (-12345678901234567890123456789012345678);
insert into tblType (num) values (123456789012345678901234567890123456789);
insert into tblType (num) values (1234567890123456789012345678901234567890123456789);
  • num number : 뒤에 0이 들어가는 이유 -> 유효숫자를 저장하는 가수부에 한계때문. 유효숫자 38자리를 넘으면 0으로 표현한다.

  1. 문자형
    • 문자 + 문자열
    • 자바의 String과 유사
    • n(X) vs n(O)
    • var(X) vs var(O) -> var : 공간의 크기가 변한다. (가변)
a. **char** (★★★★)

- 고정 자릿수 문자열 -> 공간보다 데이터가 짧아도 나머지 공간은 그대로 차지한다. (공백으로)

  a. **char(n)** : n자리 문자열, n(바이트)

  - 최소 크기: 1바이트
  - 최대 크기: 2000바이트
            ex) char -> X
            ex) char(3) -> 최대 3바이트까지 문자열을 저장 -> 인코딩? -> UTF-8 -> 영어(3글자), 한글(1글자)
            ex) char(10) -> 최대 10바이트까지 문자열을 저장 -> 영어(10글자), 한글(3글자)
            ex) char(2000) -> 영어(2000글자), 한글(666글자)

b. **nchar**
   - n : national > 유니코드 지원 > UTF-16 동작(모든 문자 -> 2바이트로 취급)
        - **nchar(n)** : n자리 문자열, n (= 문자 수)

            - 최소 크기: 1글자(2바이트)

            - 최대 크기: 1000글자(2000바이트)
         - 영어가 아닌 문자를 저장할 때 용이

            

c. **varchar2** (★★★★★★★)
   - 가변 자릿수 문자열 -> **데이터 크기만큼 공간의 크기가 줄어든다.** -> 장점 : 공간활용성이 좋다. 단점 : 공간회수 비용이 든다, 

        - **varchar2(n)** : n자리 문자열, n(바이트)
- 최소 크기 : 1바이트
  
- 최대 크기 : 4000바이트
        


d. **nvarchar2**

- 문자수 저장 + 가변

  

e. **clob, nclob**

- lob(Large Object) - 모든 종류의 데이터 중에 큰 자료형을 넣기 위해 만들어진 자료형

- 대용량 텍스트 저장 자료형
  - 128TB (무한..)
  - 잘 사용 안함. (참조형)
drop table tblType;

create table tblType (
    
    -- 컬럼을 여러개 늘릴떄 ,로 연결한다.
    -- name char(3),     
    -- name char(10),    
    -- nick varchar2(10)
    
    name char(10),
    nick nchar(10)
    
);

select * from tblType;

insert into tblType (name) values ('abc');
insert into tblType (name) values ('abcd'); -- 에러 : value too large for column "HR"."TBLTYPE"."NAME" (actual: 4, maximum: 3) (HR계정이 소유하고있는 TBLTYPE타입의 테이블에 NAME컬럼에 값이 너무 크다. 최대 3바이트까지만 가능하다. )
insert into tblType (name) values ('김');
insert into tblType (name) values ('홍길동'); -- (actual: 9, maximum: 3)
insert into tblType (name) values ('123'); -- 아라비안 숫자 1글자당 1바이트
insert into tblType (name) values ('&*('); -- 특수문자 1글자당 1바이트


insert into tblType (name, nick) values ('홍길동', '바보');
insert into tblType (name, nick) values ('가', '가');

-- 눈에 명시적으로 보이는 데이터는 같지만, 데이터베이스에 저장될때 모습은 다르다.
-- char -> 'abc       '
-- varchar2 -> 'abc'
insert into tblType (name, nick) values ('abc', 'abc');


insert into tblType (name, nick) values ('홍길동', '홍길동');
insert into tblType (name, nick) values ('홍길동님', '홍길동님');
insert into tblType (name, nick) values ('홍길동', '홍길동님');
insert into tblType (name, nick) values ('홍길동', '홍길동님안녕하세요.');
insert into tblType (name, nick) values ('홍길동', '홍길동님안녕하세요.!'); -- 에러 : 오버플로우

  1. 날짜/시간형

    • 자바 Calendar, Date
      a. date (★★)

      • 년월일시분초
      • 7byte
      • 기원전 4712년 1월 1일 ~ 9999년 12월 31일

      b. timestamp
      - 년월일시분초 + 밀리초(나노초) -> 잘 안쓰임.. 나노초까지 쓸 일이 없음.(활용도 낮음)

      c. interval

      • 틱값
      • 시간
      • number로 대신할 수 있어서 잘 안쓰임.

  1. 이진 데이터 자료형 -> 잘 안쓰임.

    • 텍스트가 아닌 데이터

    • 이미지, 영상, 음악 등...
      a. blob

      • 128TB

    • 인코딩이 발생함 (비용발생, 느려짐)

    • 해결책 : 게시판 -> 첨부 파일 -> 첨부 파일명(+경로) 만 저장-> 문자열 -> varchar2

drop table tblType;
 
 create table tblType(
    name varchar2(30),  -- 이름
    age number(3),      -- 나이
    birthday date       -- 생일
 );
 
 insert into tblType (name, age, birthday) values ('홍길동', 20, '1995-05-15');
 
 select*from tblType;

profile
仁
모르면 괴롭고 알면 즐겁다.
이전 포스트

Java 코드 조각

다음 포스트

학원 33일차 - Oracle

0개의 댓글

관련 채용 정보