Oracle 1일차 - SQL SELECT

2020.08.04(화)

 

1. 오라클 RDMS(Relationship Database)

응용프로그램 (java(main), C*, C)와 데이터베이스(하드 디스크)를 연결해주는 응용프로그램 (실제 데이터 => 중계자)

= 데이터베이스 저장구조 (테이블 형태) => 2차원 구조

= XE : 전역 데이터베이스 (자동 생성)

* 오라클 단점 
 ======== 
       자바 => 중간에 오류 (종료) 
       오라클 => 오류가 나면 다음 문장을 실행 
                      (비절차적언어) 각 문장마다 독립적으로 실행하므로 넘어감 

 

package com.sist.db;
import java.sql.*;
import java.util.*;

public class DetaBaseMain {

	public static void main(String[] args) {
		try {
			// conn hr/happy
			Class.forName("oracle.jdbc.driver.OracleDriver"); // 드라이버 설치 => 클래스로 제작
			String url = "jdbc:oracle:thin:@localhost:1521:XE";
			// \\211.999.999.999/javaDev (XE가 javaDev같은 폴더명)
			// 연결
			Connection conn = DriverManager.getConnection(url,"hr","happy");
			// conn hr/happy 오라클에서 연결
			// SQL 문장 전송
			Scanner scan = new Scanner(System.in);
			System.out.print("검색어 입력:");
			String data=scan.next();
			//String data="A";
			String sql = "SELECT ename FROM emp WHERE ename LIKE '%'||?||'%'";
			// || => 문자열 결합, 논리연산자 (조건 OR 조건) && => 입력 ==> AND (자바의 Scanner 기능)
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, data); // 물음표에 값을 채움. 정수값은 setInt
			// 데이터 읽기
			ResultSet rs = ps.executeQuery(); //rs에 데이터 저장
			while(rs.next()) { //next가 커서를 이동함 데이터 하나씩 읽음
				System.out.println(rs.getString(1));
			}

		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}
	}
}

 

2. SQL(SELECT 데이터베이스로부터 저장되어 있는 데이터 검색하는데 사용)

 1) SQL문의 용어 정리

  - DISTINCT : 중복되는 행 제거

  - * : 테이블의 모든 column 출력

  - alias : 해당 column에 대해서 다른 이름 부여할 때 사용

  - table_name : 질의 대상 테이블명

  - WHERE : 조건을 만족하는 행들만 검색

  - codition : column, 표현식, 상수 및 비교 연산자

  - ORDER BY : 질의 결과 정렬을 위한 옵션 (ASC:오름차순(Default), DESC내림차순)

 

 2) SQL문의 작성 방법

  = 명령어는 대소문자 구별하지 않지만 저장된 데이터 찾을 때는 대소문자를 구분한다

   (emp데이터 중에 ename에서 A를 포함한(LIKE => java의 contains) 것 찾아오라)

SQL> SELECT ename FROM emp WHERE ename LIKE '%A%';

ENAME
--------------------
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES

7 rows selected.

SQL> SELECT ename FROM emp WHERE ename LIKE '%a%';

no rows selected

(=> 명령어는 대소문자 구분안함. 하지만 데이터 찾을 때 A와 a는 구분함)

 

= SQL 문장 한 줄 또는 여러 줄에 입력될 수 있다

= 일반적으로 키워드는 대문자로 입력

   다른 모든 단어, 즉 테이블 이름, 여 이름은 소문자로 입력 (권장)

= 가장 최근 명령어 1개가 SQL buffer에 저장

= SQL문 마지막 절의 끝에 " ; "를 기술하여 명령의 끝을 표시

 

 

3. SQL : 구조화된 질의 언어 => 문자열 형식으로 만들어 진다
 ===
    DML (데이터 조작언어) : 웹프로그래머 주로 사용
        = SELECT : 데이터 검색
        = INSERT : 데이터 추가
        = UPDATE : 데이터 수정
        = DELETE  : 데이터 삭제 => ROW 
    DDL (데이터 정의언어) : DBA => table, view, sequence, PL/SQL
        = CREATE : 생성
        = DROP : 삭제
        = ALTER : 수정
        = RENAME : 이름 변경
        = TRUNCATE : 잘라내기
    DCL (데이터 제어언어) : 권한 (사용)
        = GRANT : 권한 부여
           GRANT CREATE VIEW TO hr
        = REVOKE : 권한 해제
    TCL (트랜잭션 제어언어) : 일괄처리
        = COMMIT : 정상 수행
        = ROLLBACK : 취소

   ***** SELECT : 검색
   형식)
          =================================
          SELECT ()    ====> 필요한 데이터만, 전체 데이터
                   => 필요한 데이터
                         컬럼명, 컬럼명, ...
                   => 전체
                          *
          FROM table명
          ================================= 필수
          [
              WHERE 조건 (if)
              GROUP BY 그룹컬럼
              HAVING 그룹 조건
              ORDER BY 컬럼명 (ASC|DESC)
          ]
            (순서 지켜야함!)

SQL> DESC emp; // 컬럼종류 확인 및 파악
SQL> ed test1 // test1 메모장 생성
SQL> @test1 // test1 메모장 실행

//라인, 페이지 사이즈 수정
SQL> set linesize 300;
SQL> set pagesize 20;

-- emp (table) 모든 데이터 출력 
SELECT * FROM emp;

-- emp (table) 원하는 데이터 출력 
-- 사번(empno), 이름(ename), 입사일(hiredate), 직위(job) 
SELECT empno, ename, hiredate, job FROM emp; 
-- 사번, 이름, 입사일, 직위, 급여(sal)

SELECT empno, ename, hiredate, job, sal FROM emp; 

SQL> SELECT empno, ename, hiredate, job, sal FROM emp;

     EMPNO ENAME                HIREDATE JOB                       SAL
---------- -------------------- -------- ------------------ ----------
      7369 SMITH                80/12/17 CLERK                     800
      7499 ALLEN                81/02/20 SALESMAN                 1600
      7521 WARD                 81/02/22 SALESMAN                 1250
      7566 JONES                81/04/02 MANAGER                  2975
      7654 MARTIN               81/09/28 SALESMAN                 1250
      7698 BLAKE                81/05/01 MANAGER                  2850
      7782 CLARK                81/06/09 MANAGER                  2450
      7788 SCOTT                82/12/09 ANALYST                  3000
      7839 KING                 81/11/17 PRESIDENT                5000
      7844 TURNER               81/09/08 SALESMAN                 1500
      7876 ADAMS                83/01/12 CLERK                    1100
      7900 JAMES                81/12/03 CLERK                     950
      7902 FORD                 81/12/03 ANALYST                  3000
      7934 MILLER               82/01/23 CLERK                    1300

-- 옵션

-- 문자열 결합 => || *** 오라클 : 문자열 처리 => ''

-- "" 인용부호

-- SELECT * FROM emp WHERE ename = "KING" => 'KING';

 

-- 별칭

SELECT ename||'님의 이번달 급여는 '||sal||'입니다.' "메세지" FROM emp;

                                          => 일반문자는 ' '/ 별칭줄때는 " "

메세지
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SMITH님의 이번달 급여는 800입니다.
ALLEN님의 이번달 급여는 1600입니다.
WARD님의 이번달 급여는 1250입니다.
JONES님의 이번달 급여는 2975입니다.
MARTIN님의 이번달 급여는 1250입니다.
BLAKE님의 이번달 급여는 2850입니다.
CLARK님의 이번달 급여는 2450입니다.
SCOTT님의 이번달 급여는 3000입니다.
KING님의 이번달 급여는 5000입니다.
TURNER님의 이번달 급여는 1500입니다.
ADAMS님의 이번달 급여는 1100입니다.
JAMES님의 이번달 급여는 950입니다.
FORD님의 이번달 급여는 3000입니다.
MILLER님의 이번달 급여는 1300입니다.

-- 별칭

SELECT empno "사번", ename "이름", hiredate "입사일", sal "급여", comm as 성과급 FROM emp;

SQL> SELECT empno "사번", ename "이름", hiredate "입사일", sal "급여", comm as 성과급 FROM emp;

      사번 이름                 입사일         급여     성과급
---------- -------------------- -------- ---------- ----------
      7369 SMITH                80/12/17        800
      7499 ALLEN                81/02/20       1600        300
      7521 WARD                 81/02/22       1250        500
      7566 JONES                81/04/02       2975
      7654 MARTIN               81/09/28       1250       1400
      7698 BLAKE                81/05/01       2850
      7782 CLARK                81/06/09       2450
      7788 SCOTT                82/12/09       3000
      7839 KING                 81/11/17       5000
      7844 TURNER               81/09/08       1500
      7876 ADAMS                83/01/12       1100
      7900 JAMES                81/12/03        950
      7902 FORD                 81/12/03       3000
      7934 MILLER               82/01/23       1300

 

-- 정렬 : ORDER BY => INDEX

-- 급여가 많은 사원부터 출력

SQL> SELECT ename, sal FROM emp;

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
SCOTT                      3000
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
FORD                       3000
MILLER                     1300

14 rows selected.

SQL> SELECT ename, sal FROM emp ORDER BY sal DESC;

ENAME                       SAL
-------------------- ----------
KING                       5000
FORD                       3000
SCOTT                      3000
JONES                      2975
BLAKE                      2850
CLARK                      2450
ALLEN                      1600
TURNER                     1500
MILLER                     1300
WARD                       1250
MARTIN                     1250
ADAMS                      1100
JAMES                       950
SMITH                       800

14 rows selected.

 

* ORDER BY의 default값은 ASC다

SELECT ename, sal FROM emp ORDER BY sal ASC;

= SELECT ename, sal FROM emp ORDER BY sal;

= SELECT ename, sal FROM emp ORDER BY 2; (ename이 1번, sal이 2번)

오라클은 1번부터 시작함 (자바는 0번부터)

 

 

SQL>  @c:\javaDev\zipcode
1 row created.

1 row created.

SELECT COUNT(*) FROM zipcode;

  COUNT(*)
----------
     42625

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM zipcode WHERE dong='서교';

no rows selected

SQL> SELECT * FROM zipcode WHERE dong LIKE '%서교%';

ZIPCODE        SIDO                                     GUGUN                                    DONG
-------------- ---------------------------------------- ---------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BUNJI
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
121-836        서울특별시                               마포구                                   서교동
1∼337번지

121-837        서울특별시                               마포구                                   서교동
338∼353

 

4. 오라클

    1) 형식 
    2) 데이터형
    ============== 암기
    3) 응용
    4) 연동 ===> 자바, NodeJS(코틀린)
    5) 반복제거 (함수) => PL(Procedure Language 자바의 메소드)/SQL

    1) 형식
       데이터 검색 (SELECT)
       SELECT * | column1, column2...
       FROM (table명, view명(가상table), SELECT ~~) ==> 필수
       [
          조건 검색 : WHERE (if)
          그룹별 처리 : GROUP BY
          그룹 조건 : HAVING ==> 반드시 GROUP BY가 존재해야 사용가능
          정렬 : ORDER BY 
       ]
       ****** 오라클은 데이터 정렬이 안된 상태
               => 저장된 순서로 읽어온다
       **** 주의점
        (1) 대소문자는 구분하지 않는다 (단, 저장된 데이터는 대소문자 구분)

SQL> SELECT ename, sal FROM emp WHERE ename='king';

no rows selected

SQL> SELECT ename, sal FROM emp WHERE ename='KING';

ENAME                       SAL
-------------------- ----------
KING                       5000

        (2) 문자열 표현 : ''

        (3) 날짜 표현 : '' (문자열)

SQL> SELECT ename, sal, hiredate FROM emp WHERE hiredate>='80/01/01' AND hiredate<='80/12/31';

ENAME                       SAL HIREDATE
-------------------- ---------- --------
SMITH                       800 80/12/17

        (4) 숫자는 ''를 사용하지 않는다 

 

-- INLINE View => 페이징 기법

-- SELECT empno, ename FROM (SELECT * FROM emp);

 

-- 중복된 데이터를 제외하고 출력 : DISTINCT

-- 직위가 어떤 것이 있는지 확인

SQL> SELECT job FROM emp;

JOB
------------------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK

14 rows selected.

SQL> SELECT DISTINCT job FROM emp;

JOB
------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

 

-- JOIN

SQL> SELECT * FROM dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        
SQL> SELECT ename, emp.deptno, ename, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

ENAME                    DEPTNO ENAME                DNAME                        LOC
-------------------- ---------- -------------------- ---------------------------- --------------------------
SMITH                        20 SMITH                RESEARCH                     DALLAS
ALLEN                        30 ALLEN                SALES                        CHICAGO
WARD                         30 WARD                 SALES                        CHICAGO
JONES                        20 JONES                RESEARCH                     DALLAS
MARTIN                       30 MARTIN               SALES                        CHICAGO
BLAKE                        30 BLAKE                SALES                        CHICAGO
CLARK                        10 CLARK                ACCOUNTING                   NEW YORK
SCOTT                        20 SCOTT                RESEARCH                     DALLAS
KING                         10 KING                 ACCOUNTING                   NEW YORK
TURNER                       30 TURNER               SALES                        CHICAGO
ADAMS                        20 ADAMS                RESEARCH                     DALLAS
JAMES                        30 JAMES                SALES                        CHICAGO
FORD                         20 FORD                 RESEARCH                     DALLAS
MILLER                       10 MILLER               ACCOUNTING                   NEW YORK

 

    2) 오라클의 데이터형

(1) 문자형 =============> String

  CHAR(1~2000byte) ** 한글(2byte) CHAR(4) 

  ============================ 고정

  VARCHAR2(1~4000byte)

  CLOB(4GA) 줄거리, 뉴스, 자기소개

  ============================ 가변 (들어온 갯수만큼 바이트 메모리 지정)

   (* 배열은 고정이지만 ArrayList는 가변)

   CHAR(5) => 'A' => 메모리 (5byte)

 

(2) 숫자형 =============> int, double

  NUMBER 정수

  NUMBER (3) ==> 38자리까지

  NUMBER (7,2) 수, 소수점

 

    ex) 123.45

     NUMBER -> 123.45

     NUMBER(3) => 123

     NUMBER(5,2) => 123.45

 

(3) 날짜형 =============> java.util.Date

  DATE

  TIMESTAMP 경기

 

(4) 기타형 =============> java.io.InputStream

 (4GA) 동영상 그림파일, 증명사진

  BFILE 파일형식 증명사진

  BLOB 바이트형식 동영상

 

DESC dept; (구성 파악)

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

 

class DeptVO{

   int deptno

   String dname

   String loc

}