TIL_210405_SQL로 Oracle Database 다루기, 자바&Oracle 연결

Oracle Database 다루는 방법 1. SQL Command Line을 이용

                                               2.  sqldeveloper 이용 

 

목차

1. 계정

1) db user 계정 생성

2) 사용자 계정에 권한 부여

3) 사용자 계정 접속

4) 사용자 계정 비밀번호 변경

 

2. 테이블

1) 테이블 생성

2) 테이블 구조 조회

3) 생성된 테이블 조회

4) 기존 테이블에서 새로운 필드 추가 / 삭제

5) 테이블 구조 변경

 

3. 데이터

1) 데이터 추가(삽입)

2) 생성된 데이터 조회

3) 특정 데이터 조회

4) 특정 데이터 변경

5) 데이터 삭제/ 불필요 테이블 삭제

6) 데이터 저장

 

 

 

 

1. 계정

 

1) db user 계정 생성

 

 (1) 사용자 계정이 없을 경우,  반드시 시스템 계정으로 로그인

      SQL> conn sys as sysdba

              conn    :   데이터베이스 접속 명령어 
              sys       :  데이터베이스 시스템
              as        :   ~~로써
              sysdba  :   데이터베이스 시스템 관리자
              db       :    데이터베이스
              a         :    Administator (관리자)

              Enter password:     <= pwd를 입력해도 보이지 않음 (오라클 설치시 입력했던 pwd)
              Connected.            <= 로그인 성공시 

 

 

(2) 사용자 계정 생성 (오라클은 사용자 계정 생성해도 바로 사용 X, 권한 따로 부여해야 함)

SQL> create user 생성할 사용자 계정명 identified by pw입력;

User created.   <= 계정 생성 성공시 

 

 

2)사용자 계정에 권한 부여

SQL> grant 권한, 권한, 권한 to 부여받을 사용자계정명;
SQL> grant create view to 사용자 계정명;

ex : grant connect, resource to qorwjddus;

          grant : 권한 부여하는 명령어
          접속 권한명 : connect
          테이블 등을 생성할 수 있는 권한 : resource
          view를 생성할 수 있는 권한 : create view 

Grant succeded.   <= 권한 부여 성공시 

 

 

3) 사용자 계정 접속

SQL> conn bjy/qorwjddus96;

Connected;

 

 

4) 사용자 계정 비밀번호 변경

alter user 변경할 사용자 계정명 identified by 변경할 pw입력;

 

 

2. 테이블

 

1) 테이블 생성 (현재 어떤 계정에 접속하고 있는지 반드시 확인 !!!)

create table 테이블명 (필드명 데이터형, 필드명 데이터형);  // 한줄에 안쓰고 나눠서 쓰는 것도 가능함 

ex : SQL> create table member (   
      2  name varchar2(10),    
      3  userid varchar2(10),
      4  pwd varchar2(10)
      5  );

       varchar2(byte 크기) => var : 가변성 + char
       varchar2(10) : 10byte 크기로 설정
        => 'abcde' => 5byte로 변경
        => 'abcdefghij' => 10byte
        => 'aaaaabbbbbccccc' => 10byte 넘어가므로 오류 
        (왜 가변성 캐릭터 사용하지 ? 웹상에선 데이터가 가장 비싸기에 낭비하지 않으려고 크기 조절해준다)

 

 

 

2) 테이블 구조 조회

SQL> desc member; (desc : description 테이블 구조 보는 명령어 ) 

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(10)
 USERID                                             VARCHAR2(10)
 PWD                                                VARCHAR2(10)

 

 

3) 생성된 테이블 조회

SQL> select * from tab;

(테이블명) TNAME                                                TABTYPE (테이블형태)
------------------------------------------------------------ --------------
CLUSTERID ----------
MEMBER                                                             TABLE

 

 

4) 기존 테이블에서 새로운 필드 추가 / 삭제 

(1) 필드 추가 

SQL> alter table 테이블명 add 추가필드명 데이터타입;

ex: alter table student add age number(3);

(2) 기존 필드 삭제

SQL> alter table 테이블명 drop column 삭제필드명; (데이터 타입 안써도 된다)

 

5) 기존 테이블 구조 변경

(1) 컬럼명 변경

SQL> ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 변경컬럼명;

(2) 테이블에 이미 존재하는 컬럼 수정 (단, NOT NULL을 제외한 제약조건은 수정 불가하므로 삭제 후 재생성)

SQL> ALTER TABLE 테이블명 MODIFY (컬럼명 변경데이터 타입 OR 제약조건);

예) ALTER TABLE test MODIFY (job VARCHAR(30));
    ALTER TABLE EMP MODIFY (NUMBER DEFAULT 0);
    ALTER TABLE EMP MODIFY (ENAME CONSTRAINTS EMP_ENAME_NN NOT NULL);

(단 자료가 없는 경우 데이터 타입 변경, 컬럼 크기 변경 모두 가능하나, 자료가 있는 경우 데이터 타입은 변경 X,

컬럼은 더 작은 크기로는 변경 X)

3. 데이터

1) 데이터 추가(삽입)

   - 띄어쓰기 주의할 것
   - 데이터 삽입하게 되면 맨마지막에 추가된다

    - 테이블의 필드명 순서대로 데이터 삽입할 시 테이블명의 필드명 생략 가능

       (데이터 순서 바꿔서 삽입하고 싶다면 필드명도 데이터 순서에 맞춰서 작성)

    - 모든 필드를 다 채워서 삽입할 필요 X

SQL> insert into 테이블명 (필드명, 필드명) vaules (데이터, 데이터);

ex : insert into member (name, userid, pwd) values ('백정연', 'raccoon', '960215');
=>   insert into member values ('백정연', 'raccoon', '960215');            // 테이블 필드명 생략 가능 

※ 오류 주의사항 : 1) 필드 갯수, 데이터 갯수 다른 경우 

                             2) 필드 데이터 타입, 데이터의 데이터 타입 다른 경우

                             3) 문자열 데이터는 무조건 ' '로 감싸서 입력

 

 

 

 

 

 

2) 생성된 데이터 조회

SQL> select * from 테이블명;  // 자바에서도 그대로 사용됨, * : 모든 필드

ex : select * from member;

 

 

 

3) 특정 데이터 조회

(1) 특정 필드의 데이터 검색

select 필드명 from 테이블명;

ex : select name from member;
        select userid, pwd from member;

 

 

 

(2) 특정 조건의 원하는 데이터 조회

SQL> select * from 테이블명 where 필드명='원하는 데이터';
      
     1) 아이디가 raccoon인 사람의 모든 정보 조회
    => select * from member where userid='raccoon';

     2) 아이디가 'somi'이고 비밀번호가 '3434;인 사람의 모든 정보 조회
    => select * from member where userid='somi' AND pwd='1234';

        조회 : select
       조건 (where) : userid = 'raccoon'   // sql에서는 =이 대입 연산자가 아니라 같다라는 뜻 !
       사람의 정보 :  * (모든 필드)
       조회할 테이블명 : member

 

 

 

 

 

4) 특정 데이터 변경

update 테이블명 set 변경필드명=변경값 where 검색필드명=검색값;

- 이소미 2명의 아이디가 동일하므로 한명의 아이디 변경해야할 시
  비밀번호가 '1234'이고 아이디가 'somi'인 사람의 아이디를 'somi2'로 변경 

SQL> update member set userid='somi2' where userid='somi' AND pwd='1234';

 

 

 

5) 데이터 삭제/ 불필요 테이블 삭제

* 데이터 삭제
delete from 테이블명;     => 해당 테이블의 모든 데이터 삭제되니 주의
delete from 테이블명 where 검색필드명='검색필드값';

SQL> delete from member where userid='somi2';

* 불필요 테이블 삭제
drop table 테이블명;

 

 

 

6) 데이터 저장 

  ※ 데이터 삽입 완료 후 꼭 하기 !!!!!!!!!!

  (참고로 cmd창에서 열심히 테이블, 데이터 생성해도 껐다 키면 다 날라감...)

SQL> commit;

 

 

 

포준 SQL 4개(ANTI SQL) : 어떤 db에서든 표준적으로 쓰이는 SQL 명령어 ( 꼭 외우자 ) 

select ~ from : 정보 조회  => 글목록 (비유적으로)
insert into : 데이터 삽입  => 글저장
update ~ set : 데이터 수정(변경)   => 글수정
delete from : 데이터 삭제          => 글삭제

 

---

자바 & 오라클 연결 방법 (자바에서 오라클 db로 접속하는 순서)

1. 드라이버 로딩 - "oracle.jdbc.driver.OracleDriver" // OracleDriver는 클래스

   

    => Class.forName("oracle.jdbc.driver.OracleDriver");

 

2. 드라이버 매니저 이용해 db에 접속 후 Connection 객체 생성 (db 접근하기 위해 db서버로 먼저 접속)

     Connection Class : java&db 연결해주는 클래스로 객체 생성시 db 주소, 사용자 id, pwd 필요

    (Connection 객체 사용 후 마지막엔 close)

 

    => String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";   // oracle db 주소 

                              (만약 오라클 구매해서 쓰면 thin, 아이피번호, 마지막 XE가 바뀜)

    =>  Connection conn = DriverManager.getConnection(url, "bjy", "qorwjddus96")

 

3. Connection 객체로부터 SQL 명령어(쿼리) 실행 도와주는 객체 얻기

    (Statement - 단순 조회, PreparedStatement - 쿼리문& 변수 혼합될경우 사용)

 

    => Statement stmt = conn.createStatement();

 

4. 원하는 쿼리문 작성

    => String query = "select * from member";

 

5. 특정 사용자 테이블서 원하는 쿼리문으로 실행한 결과 저장할 ResultSet Class 객체 생성

    => ResultSet rs= stmt.executeQuery(query);

 

6. 원하는 작업 수정, 삭제, 조회, 추가

    ResultSet 객체의 boolean next() 메서드 이용해 레코드 한줄씩 읽을 수 있다

   (필드부분 = meta영역이며 첫번째열 index = 1 / 데이터영역 = record 영역이며 첫번째행 index = 0 )

 

 

 

 => rs.getString("필드명" / 필드명 index);     // 해당 필드의 타입에 따라 getInt(), getByte()....

 

 

 

 

자바 & 오라클 연결 예시 

 

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC_Class {

	public JDBC_Class() {
		// TODO Auto-generated constructor stub
	}
	
	/** 오라클 데이터베이스에 접속하는 메서드**/
	public static void oracleConnection() {
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("드라이브 로딩 성공 !");
			
			// db server 주소
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
			Connection conn = DriverManager.getConnection(url, "bjy", "qorwjddus96");
			System.out.println("접속 성공!");
			
			// 쿼리문 실행 위한 객체 생성
			Statement stmt = conn.createStatement();
			
			String query = "select * from member";
			ResultSet rs= stmt.executeQuery(query); 
			// executeQuery() : 해당 쿼리 실행 (db 내부로 가서 실행, 자바에서 실행X)
			//                : ResultSet 타입 객체 반환 (특정 사용자 테이블의 원하는 쿼리문 실행된 결과 저장하는 객체)
			
			System.out.println("select * from member 실행 성공 !");
			
			ResultSetMetaData rsmd = rs.getMetaData(); // 메타데이터 영역, memeber 테이블 필드들(name, pwd, userid)
			int num = rsmd.getColumnCount();
			System.out.println("조회된 테이블의 컬럼(필드)의 갯수 : " + num);
			// 만약 String query = "select name, userid from member"; 일경우
			// 필드개수는 2개로 조회된다 
			
			// 레코드 영역 행은 0부터 시작, 메타데이터 열은 1부터 시작 !!!!!!!!!!!1
			while(rs.next()) {
				System.out.println(rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3));
			}
		
		} catch (ClassNotFoundException e) { // 외부 클래스 로딩하기에 존재
			System.out.println("드라이브 로딩 실패!" + e.getMessage());
		} catch (SQLException e) {
			 System.out.println("접속 실패" + e.getMessage());
		}
	
	
	
	} // end of oracleConnection()

}

package jdbc;

public class MainClass {

	public static void main(String[] args) {
		JDBC_Class.oracleConnection();
		
	}

}

실행결과