200911 SW활용 01 - Oracle DB / SQL활용 01 - SQL Developer

데이터베이스 개론

 

1. 데이터베이스의 개념

- DataBase Management System

- 데이터의 중복을 막을 수 있고 자료의 일관성을 유지할 수 있다.

- 데이터를 쉽게 검색할 수 있고 데이터의 통합이 쉽다.

- 여러 사람이 함께 자료를 열람할 수 있고 보안을 적용하기가 쉽다.

- 대용량의 기억 공간을 제공한다.

 

 

2. DBMS의 종류 및 규모

- Oracle : 중대규모 기업의 데이터베이스로 사용. Java

- My-SQL : 중소 기업. MS기반 운영체제만 사용 가능. PHP, Java

               .asp 닷넷

               (→ 요즘엔 MariaDB 사용)

- SQLite : 스마트폰 및 임베디드 기기의 내장 데이터베이스로 사용. Android, Iphone

- 오피스 : 엑셀 .xls

             파워포인트 .ppt

             워드 .doc

             액세스 .mdb

※ 컴활1급 : .xls + .mdb

 

 

3. 데이터베이스의 형태

1) 관계형 데이터베이스 RDBMS (Relational Database Management System)

   - SQL문이 필요하다.

   - 예시) Oracle DB, MySQL, MariaDB, MS-SQL

2) NoSQL 데이터베이스

   - SQL문이 필요 없다.

   - 예시) MongoDB

 

 

4. 데이터베이스의 용어

- 테이블 table : 데이터베이스의 저장 기본 단위

- 칼럼 column, 필드, 열

- 레코드 record, 행, row, 줄

- 예시) 성적 테이블

          이름     국어   영어   수학   평균

        ---------------------------------------

         홍길동    100     90     80    95

 

 

 

[Oracle Database 설치]

www.oracle.com/index.html

 

 

1) Products → Oracle Database

 

Products → Infrastructure → Software → Oracle Database 클릭

 

 

2) Download Oracle Database 19c 클릭

 

 

 

3) 하단의 Oracle Database 11g Release 2 Express Edition for Windows 64 클릭

 

Oaracle Database Express Edition → Oracle Database 11g Release 2 Express Edition for Windows 64

 

 

4) Oracle Database 11gR2 Express Edition for Windows x64 클릭

 

 

 

5) Download OracleXE112_Win64.zip

 

I reviewed and accept the Oracl License Agreement 체크 → Download OracleXE112_Win64.zip

 

 

6) Download OracleXE112_Win64.zip 파일의 압출을 풀어 설치

 

설치 파일 : /Disk1/setup.exe

 

7) Oracle Database 11g Express Edition 설치

 

Next 클릭

 

 

8) 설치 경로를 변경하지 않고 설치한다. 

 

Next 클릭

 

 

9) 사용자(최고 관리자 SYS, SYSTEM) 비밀번호 설정

 

 

 

10) 설치 설정을 확인 후 Install 클릭

 

포트 번호 기본값

- 1521 : Oracle database listener

- 2030 : Oracle Services for Microsoft Transaction Server

- 8080 : HTTP port for the Oracle Database XE graphical user interface

 

 

※ 오라클 포트번호를 8080로 지정하면 추후 톰캣을 설치할 때 8080은 선점되었기 때문에 다른 번호를 사용해야 한다.

 

 

 

참조) 

docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#XEINW125

Database Express Edition Installation Guide - Contents

 

 

1) Database 11g Express Edition : 개인 개발자용

   - Intel Core2 Duo 2.9 GHz

   - 데이터가 11gb를 넘을 수 없음

   - CPU 1개에서만 실행 가능

 

2) Database 11g Enterprise/Standard Editions

   - Intel i3 이상, RAM 4gb

   - 오라클 서버의 모든 기능을 제공

 

3) 최고 관리자 계정

   - Oracle : system 또는 sys

   - MySQL : root

   - MS-SQL : sa

 

4) 패스워드는 편의를 위해 '1234'로 지정했습니다.

    Enter Password : 1234

    Confirm Password : 1234

 

 

※ 주의 사항

   - 오라클 설치시 컴퓨터의 이름은 반드시 영문이어야 한다.

   - 컴퓨터 포맷 후 바로 설치할 것을 추천한다.

 

 

2. 오라클 서버 삭제 : 제어판 → 프로그램 추가/제거 → Oracle Database 11g Express Edition → 제거

 

 

3. 오라클 서버 서비스 시작/중지

   ① 돋보기

       → 서비스 입력

       → OracleServiceXE

 

 

       ※ 서비스 중지 상태가 되면 오라클이 실행되지 않는다. 상태를 늘 체크해야 한다. 

 

   

   ② 시작

       → 모든 프로그램

       → Oracle Database 11g Express Edition

       → Start Database / Stop Database

 

Stop Database

 

Start Database

 

 

 

4. 오라클 서버 연결 테스트

   시작

   → 모든 프로그램

   → Oracle Database 11g Express Edition

   → Run SQL Command Line

   → SQL>connect

       Enter user-name: system   최고 관리자 계정 아이디

       Enter password: 1234       비밀번호 (화면에 출력되지 않음)

 

Run SQL Command Line connect 확인 완료

 

       ※ 연결이 성공하면 Connected가 뜨고 실패하면 서버가 실행 중인지를 확인해야 한다.

           Path 경로를 설정하는 이유이다.

 

오라클을 설치하면 환경 변수 설정이 되어 있다.

 

 

 

 

Oracle XE 11g 사용자 계정 생성

 

cmd에서 실행

① >sqlplus

    Enter user-name: system

    Enter password: 1234

 

cmd 창에서 Oracle Database 접속

 

② >sqlplus system/1234

 

 

③ sys계정으로 로그인

    - 관리자 계정으로 접속(Data Base Administrator)

    >sqlplus / as sysdba

 

 

 

 

 

SQL문

- Stored Query Language

- 어떻게 작업을 수행할 것인가가 아니라 무엇을 해야할 지에 대해 정의하는 결과 중심적 언어

- 절차적 언어가 아니다.

  cf) 자바

- SQL 명령어는 대소문자를 구분하지 않는다.

- ANSI(표준)와 자신만의 DB명령어를 가지고 있다. 

- 종결문자 ; 

- 주석 --

 

1) DCL 명령어 ; Data Control Language 제어어

   - 사용자가 별로 사용할 일이 없음. 호스팅 업체가 주로 사용

   - grant : 사용자 접근 권한 부여

   - revoke : 사용자 접근 권한 취소

   - deny : 특정 사용자만 접근 차단

 

2) DDL 명령어 ; Data Definition Language 정의어

   - DB 및 TABLE에 대한 정의

   - Create : 생성

   - Drop : 삭제

   - Alter : 수정

 

3) DML 명령어 ; Data Manipulation Language 조작어

   - 레코드 작업

   - Select : 조회 및 검색

   - Insert : 삽입

   - Update : 수정

   - Delete : 삭제

 

 

 

 

데이터베이스 생성 및 삭제

 

1) 데이터베이스 목록 보기

    show databases;

 

2) 데이터베이스 생성

   형식) create database DB명;

   예시) create database mydb;

 

3) 데이터베이스 삭제

   형식) drop database DB명;

   예시) drop database mydb;

 

4) 데이터베이스 사용

    형식) use DB명;

    예시) use mydb;

 

※ 데이터베이스 : MySQL, MariaDB, MS-SQL

 

 

 

 

테이블

 

1) 테이블 스페이스 만들기

    - Data file이 하나 이상 모여서 Tablespace라는 논리적 저장 공간을 형성

 

① 테이블 스페이스(테이블의 저장 공간) 생성

    형식) create tablespace 테이블스페이스명

           datafile '데이터파일경로' size 초기사이즈

           autoextend on

           next 자동증가사이즈

           maxsize 최대사이즈;

 

    예시) SQL>create tablespace java0815

                   datafile 'd:\java0812\java0815.dbf' size 50m

                   autoextend on

                   next 10m

                   maxsize unlimited;

 

② 테이블 스페이스 종류 확인

    형식) select tablespace_name, contents

            from dba_tablespaces;

 

③ 테이블 스페이스 삭제

    형식) drop tablespace 테이블스페이스명

            including contents and datafiles

            cascade constraints;

 

※ 11g와 12c간의 호환 명령어 : alter session set "_ORACLE_SCRIPT"=true;

 

 

2) 사용자 계정 만들기

형식) create user 아이디 identified by 비밀번호

       default tablespace 테이블스페이스명;

 

SQL>create user java0812 identified by 1234

       default tablespace java0812;

SQL>exit

 

>sqlplus java0812/1234  - 로그인 거부

 

 

3) 사용자에게 권한 부여

>sqlplus / as sysdba

 

SQL>grant connect, resource, dba to java0812;

SQL>exit

 

>sqlplus java0812/1234  - 로그인 성공

 

※ connect : 접속 권한

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

   dba : 관리자 권한  

 

 

 

 

cmd 출력

 

데이터베이스 관련 명령어 (SQL문)

- cmd

- 툴 : SQL Developer

 

① 테이블 생성

    형식) create table 테이블명(

               칼럼명1 자료형 제약조건

              , 칼럼명2 자료형 제약조건

              , 칼럼명3 자료형 제약조건

            );

 

② 테이블 삭제

    형식) drop table 테이블명;

    예시) drop table sungjuk;

 

③ 레코드 조회 및 검색

    형식) select 칼럼명1, 칼럼명2 from 테이블명;

    예시) select * from sungjuk;

           select kor, eng, mat from sungjuk;

 

④ 레코드 추가 및 행 추가

    형식) insert into 테이블명(칼럼명1, 칼럼명2)

           values (값1, 값2);   

 

 

 

예제1) 다음 성적 테이블을 생성하시오.

 

    이름     국어   영어   수학   평균

    -------------------------------------

    홍길동    100     90     80    95

 

① 성적 테이블 생성 

    create table sungjuk (

        name varchar(20) not null

       ,kor int

       ,eng int

       ,mat int

       ,aver int

    );

 

② 성적 테이블 삭제

    drop table sungjuk;

 

 

 

예제2) null값을 부여하지 않는 성적 테이블을 생성하시오.

 

① 성적 테이블 생성 

    create table sungjuk(

        name varchar(20) not null

       , kor int not null

       , eng int not null

       , mat int not null   

       , tot int null           -- null 생략 가능

       , aver int null         

    );

 

② 성적 테이블 삭제

    drop table sungjuk;

 

 

 

관계형 DB에서 테이블의 핵심 기능

C Create 생성  → insert문

R Read   조회  → select문

U Update 수정 → update문

D Delete 삭제  → delete문

 

 

③ 성적 테이블 데이터 생성

    insert into sungjuk(name, kor, eng, mat)

    values('홍길동', 80, 90, 75);

 

    결과값

    ERROR at line 1:

    ORA-00942: table or view does not exist

 

 

    ※ 성적 테이블이 존재하는 상태에서 데이터를 큰값으로 생성하는 경우

    결과값

    too many values 

    ERROR at line 2:

    ORA-12899: value too large for column

 

 

 

예제3) varchar(50) 자료형의 성적 테이블을 생성한 후 여러 가지 데이터를 조회하시오.

 

① 성적 테이블 생성 

   create table sungjuk(

       uname varchar(50) not null

      , kor int not null

      , eng int not null

      , mat int not null

      , tot int null          --총점

      , aver int null        --평균

   );

 

② 레코드 추가

    insert into sungjuk(uname, kor, eng, mat)

    values ('홍길동', 50, 60, 30);

    insert into sungjuk(uname, kor, eng, mat)

    values ('무궁화', 30, 30, 40);

    insert into sungjuk(uname, kor, eng, mat)

    values ('진달래', 90, 90, 20);

    insert into sungjuk(uname, kor, eng, mat)

    values ('개나리', 100, 60, 30);

    insert into sungjuk(uname, kor, eng, mat)

    values ('라일락', 30, 80, 40);

    insert into sungjuk(uname, kor, eng, mat)

    values ('봉선화', 80, 80, 20);

    insert into sungjuk(uname, kor, eng, mat)

    values ('대한민국', 10, 65, 35);

    insert into sungjuk(uname, kor, eng, mat)

    values ('해바라기', 30, 80, 40);

    insert into sungjuk(uname, kor, eng, mat)

    values ('나팔꽃', 30, 80, 20);

    insert into sungjuk(uname, kor, eng, mat)

    values ('대한민국', 100, 100, 100);

 

③ 성적 테이블 레코드 조회 및 검색

    select kor, eng, mat from sungjuk;

    select uname, tot, aver from sungjuk;

    select uname, kor, eng, mat, tot, aver from sungjuk;

 

    - 모든 칼럼 조회하기

      select * from sungjuk;

 

    - 해당 데이터를 예쁘게 출력하기

      set linesize 300;

      set pagesize 300;

      column uname format a10;  - 10칸 내에서 출력

      select * from sungjuk;

 

      결과값

 

tot, aver는 설정이 안됐으므로 없는 상태

 

 

 

예제4) 성적 테이블 레코드 수정 및 삭제하기

 

① 레코드(총점) 수정하기

    형식) update 테이블명

            set 칼럼명1=값1, 칼럼명2=값2

    예시) update sungjuk 

           set tot=kor+eng+mat;

 

    update sungjuk

    set tot=kor+eng+mat;

    

    select * from sungjuk;

 

    결과값

 

 

 

② count( ) 함수

    - 레코드 갯수 카운트 count( )

      예시) select count(uname) from sungjuk;

             select count(uname) as cnt from sungjuk;

 

            - as 생략 가능

             select count(kor) cnt from sungjuk;

             select coung(aver) cnt from sungjuk;

 

 

    select count(uname)

    from sungjuk;

 

    결과값

 

 

 

    select count(tot)

    from sungjuk;

 

    결과값

 

 

 

 

    - NULL값은 카운트 대상이 아니다.

      select count(aver)

      from sungjuk;

 

      결과값

 

 

 

 

    - 전체 레코드 갯수

      select count(*)

      from sungjuk;

 

      결과값

 

 

       ※ 전체 레코드 갯수를 카운트할 때는 칼럼에 NULL값이 있을 수 있기 때문에 칼럼명을 사용하지 않는다.

          예시) select count(*) cnt from sungjuk;

 

 

③ 레코드 삭제하기

    - 모든 행 삭제하기

      delete from sungjuk;

 

    - 명령어 취소하기

      rollback;

 

    - 명령어 완료하기

      commit;

 

 

 

 

[Oracle SQL Developer 설치]

www.oracle.com/index.html

 

 

1) 홈페이지 하단의 Developers

 

홈페이지 하단 Resources for → Developers

 

 

2) Databases

 

Technologies → Databases

 

 

3) Oracle SQL Developer에서 Overview 선택

 

Tools → Oracle SQL Developer → Overview

 

 

4) SQL Developer 선택

 

 

 

5) Windows 32-bit/64-bit Download 클릭

 

 

 

6) sqldeveloper-20.2.0.175.1842-no-jre.zip 설치 후 압축 풀기

 

설치 파일 : sqldeveloper

 

- sqldeveloper.exe 실행하기

  jdk 설치 폴더 물어 봄 → C:\Program Files\Java\jdk-11.0.8 선택

 

- Oracle 접속하기

  Name        : database

  사용자 이름 : system

  비밀번호     : 1234

  호스트 이름 : localhost

  포트           : 1521

  SID            : xe

 

  참고) developer0513.tistory.com/76

Sql Developer 설치하기