디바이스 융합 자바(Java) day20 - 오라클(GROUP BY절,HAVING절,실행순서,페이징처리,조인(JOIN)-이퀄조인),자바&데이터베이스 연결 및 실습(추가,삭제,선택,목록출력)

SQL : 인터프리터 언어 / 채팅형 언어

-- 오라클 실행 순서
SELECT AGE,MAX(SCORE) -- 4 : 화면에 요청한 컬럼값(AGE, MAX(SCORE))을 출력
FROM STUDENT -- 1 : 테이블 선택
WHERE AGE=22 -- 2 : 조건절로 데이터를 선택
GROUP BY AGE; -- 3 : GROUPING

테이블 선택 -> 조건절로 데이터를 선택 -> GROUPING -> 화면에 요청한 컬럼값을 출력
>> 실행순서를 생각해 볼 것!

 

-- GROUP BY 절

SELECT * FROM USER_TABLES;
SELECT * FROM STUDENT;
SELECT AGE,MAX(SCORE) FROM STUDENT AGE=22;
  SELECT AGE FROM STUDENT WHERE AGE=22;
  SELECT MAX(SCORE) FROM STUDENT WHERE AGE=22;
  -- GROUP BY 절 : 데이터를 GROUPING하여 출력
SELECT AGE,MAX(SCORE) FROM STUDENT WHERE AGE=22 GROUP BY AGE;
-- 하나로 그룹핑해서..!
-- 오라클 실행 순서
  SELECT AGE,MAX(SCORE) -- 4 : 화면에 요청한 컬럼값(AGE, MAX(SCORE))을 출력
  FROM STUDENT -- 1 : 테이블 선택
  WHERE AGE=22 -- 2 : 조건절로 데이터를 선택
  GROUP BY AGE; -- 3 : GROUPING

 

-- HAVING 절

SELECT AGE,SUM(SCORE) FROM STUDENT WHERE SUM(SCORE)>=90 GROUP BY AGE;
  -- 나이때별 총 합 출력, 학생 데이터에서, 나이별로 그룹핑
  -- 조건 후의 그룹핑인데, 다중행함수가 등장할 곳도 아니고...
  -- 테이블 선택은 됐다.. 조건이 불분명! 그룹함수가 들어갔는데 어떤 함수를 그룹하라는 것인지 모른다
  -- 그룹 함수를 사용하여 조건을 줄 때에는 WHERE절을 쓰지 않는다
  -- HAVING절을 사용하여 쓴다
SELECT AGE,SUM(SCORE) FROM STUDENT GROUP BY AGE HAVING SUM(SCORE)>=90;
SELECT AGE,SUM(SCORE) FROM STUDENT WHERE AGE!=24 GROUP BY AGE HAVING SUM(SCORE)>=40 ORDER BY SUM(SCORE) ASC;
  SELECT AGE,SUM(SCORE) --5 
  FROM STUDENT --1
  WHERE AGE!=24 --2
  GROUP BY AGE --3
  HAVING SUM(SCORE)>=40 --4 : GROUPING이 먼저 진행이 완료된 상황
  ORDER BY SUM(SCORE) ASC; --6

 

-- 이름변경한 상태로 진행을 한다? 불가능하다!!

  SELECT AGE AS 나이,SUM(SCORE) --5 컬럼별칭이 5번이다 >> 근데 GROUPING이 3번.. 나이로 지정하는 것은 불가!
  FROM STUDENT --1
  WHERE AGE!=24 --2
  GROUP BY AGE --3
  HAVING SUM(SCORE)>=40 --4 : GROUPING이 먼저 진행이 완료된 상황
  ORDER BY SUM(SCORE) ASC; --6
  -- ★ 실행 순서 이슈는 매우 중요!

-- 이름을 변경하게 되면 뒤에서 AGE 지정에서 에러가 걸리기 때문에 진행 자체가 불가능! + 에러 발생

 

 

 

 

-- 기본적으로 보여줄 PRODUCT 테이블

>> 페이징 처리의 예시가 될 아이

>> 이 것과 비교하면 조인의 편리함 && 용이성 보일 것

-- 테이블 만들기
CREATE TABLE PRODUCT(
     PID INT PRIMARY KEY,
     NAME VARCHAR(20) NOT NULL,
     PRICE INT NOT NULL,
     CNT INT DEFAULT 0,
     CATEGORY VARCHAR(20) NOT NULL
);
SELECT * FROM PRODUCT;
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'운동화',59000,10,'신발');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'실내화',9900,24,'신발');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'샌들',28000,30,'신발');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'콜라',1200,100,'음료');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'사이다',900,95,'음료');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'단팥빵',1500,20,'빵');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'소보로',1400,21,'빵');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'도너츠',1300,22,'빵');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'만화책',23000,70,'도서');
INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'장편소설',21800,120,'도서');

 

 

-- ★★★★★ 페이징처리

   -- : 오라클에서만 쓰이는 기법 : ROWNUM <- MYSQL에서는 사용 불가
   -- ROWNUM : 오라클에서만 사용
   -- 자체적으로 매기는 PK 개념

   -- 그 개수만큼! ROWNUM<=숫자

 

   -- 속도 개선

       -- PID<=숫자 로 해도 되지 않나요?
       -- 전체에서 숫자보다 작니? 로 전부 찾아서 확인하고 출력
       -- >> 10개를 전부 방문하는 것이 된다.
       -- 데이터의 군이 많으면 많을 수록 ROWNUM을 쓰는 것이 좋다.

   -- 실무 페이징 처리에서는 목록을 101~110까지 보여주세요, 라고 한다..
       -- 서브 쿼리를 이용!
       -- 과거에 INSERT할 때 사용!
       -- INSERT라는 쿼리 안에 순서대로 오더링 하기 위해 SELECT를 또 추가!

SELECT * FROM () WHERE ???; -- 데이터를 볼건데 어디에 어딜 볼건지 아직 작성 안함
   -- 특정 부분() 에서 ???에 해당하는 만큼 출력하겠습니다.
SELECT * FROM (SELECT A.*, ROWNUM AS RNUM FROM (SELECT * FROM PRODUCT ORDER BY PID DESC) A WHERE ROWNUM<=5) WHERE RNUM>3;
-- 전체 부분에서 일부분! 
   -- A.8 << A.와일드카드
   -- SELECT A.*, ROWNUM AS RNUM FROM (SELECT * FROM PRODUCT ORDER BY PID DESC) A WHERE ROWNUM<=5
   -- 전체 테이블 부분인 A에서(벌칭 설정 가능) ROWNUM이 5 이하인 부분
   -- SELECT * FROM (SELECT A.*, ROWNUM AS RNUM FROM (SELECT * FROM PRODUCT ORDER BY PID DESC) A WHERE ROWNUM<=5) WHERE ???;
   -- RNUM이 3보다는 크다
SELECT * FROM (SELECT A.*, ROWNUM AS RNUM FROM (SELECT * FROM PRODUCT ORDER BY PID DESC) A WHERE ROWNUM<=6) WHERE RNUM>=4;

 

 

 

-- ★★★★★ 조인(JOIN)

     -- 이퀄 조인

-- 예시 
CREATE TABLE TEST1(
   ID INT PRIMARY KEY,
   NAME VARCHAR(20) NOT NULL,
   NUM INT NOT NULL -- 동아리<외래키>
);
INSERT INTO TEST1 VALUES(1,'티모',101);
INSERT INTO TEST1 VALUES(2,'아리',201);
INSERT INTO TEST1 VALUES(3,'세라핀',401);
INSERT INTO TEST1 VALUES(4,'모르가나',401);
INSERT INTO TEST1 VALUES(5,'미스포춘',301);
INSERT INTO TEST1 VALUES(6,'비에고',501);
INSERT INTO TEST1 VALUES(7,'아무무',501);
INSERT INTO TEST1 VALUES(8,'이즈리얼',301);
INSERT INTO TEST1 VALUES(9,'가렌',101);
INSERT INTO TEST1 VALUES(10,'애니',201);
CREATE TABLE TEST2(
   PID INT PRIMARY KEY,
   PNAME VARCHAR(20) NOT NULL
);
INSERT INTO TEST2 VALUES(101,'탑');
INSERT INTO TEST2 VALUES(201,'미드');
INSERT INTO TEST2 VALUES(301,'원딜');
INSERT INTO TEST2 VALUES(401,'서포터');
INSERT INTO TEST2 VALUES(501,'정글');
-- 관리가 용이하기 때문에
   -- 동아리 넘버만 저장해 놓으면 긴 한글 데이터를 중복해서 저장하지 않아도 되기 때문
   -- 최대한 중복없이 저장하려고 한다(잘게 저장)
-- 유지보수 용이
-- >> 중복데이터 최소화
SELECT * FROM TEST1;
SELECT NAME, PNAME FROM TEST1, TEST2 WHERE TEST1.NUM=TEST2.PID;
-- 여러개의 테이블을 조합하여 출력 가능 
   -- >> JOIN
   -- 이 예시에 해당하는 조인을 '이퀄 조인(EQUAL JOIN)'이라고 한다
   -- 조인 조건이 A.컬럼=B.컬럼

>> 이퀄 조인 사용 이유!

-- 관리가 용이하기 때문에
   -- 동아리 넘버만 저장해 놓으면 긴 한글 데이터를 중복해서 저장하지 않아도 되기 때문
   -- 최대한 중복없이 저장하려고 한다(잘게 저장)
-- 유지보수 용이

-- 연결이 두 개, 세 개도 가능하다!!

 

>> 이퀄조인 사용법

   -- 이 예시에 해당하는 조인을 '이퀄 조인(EQUAL JOIN)'이라고 한다
   -- 조인 조건이 A.컬럼=B.컬럼

 

>> 이퀄 조인 조건

-- 다른 테이블의 정보를 가져올 때에는 반드시 해당 테이블의 PK를 가져와야 한다!
-- 이렇게 가져온 상대 테이블의 PK 컬럼을 '외래키(FOREIGN KEY)'라고 한다.

 

 

>>  동아리의 주소와 동아리 회장을 표기하게 되면
      INSERT INTO TEST2 VALUES(101, '탑', 주소, 1); 이런식으로 표기가능

 

 

------------------------------------
JAVA와 데이터베이스의 연결
>> 저번에는 이클립스와 데이터베이스의 연동!
>> 오늘은 JAVA PROJECT와의 연결!
 DBMS 선정(오라클)
 JAVA와 연동
 [JDBC]
>> 순서를 외울 것!!!
 1. DBMS와 연동을 하기 위한 드라이버가 필요!
    1) DBMS(오라클, MYSQL)에 해당하는 드라이버 파일을 갖고있나?
    2) 드라이버 파일 정보(파일 데이터)를 자바(JAVA)에 적재(로드, load)할 수 있는가?
      .jar의 데이터를 JAVA에 load하는 방법
>> Class.forName("oracle.jdbc.driver.OracleDriver");

final String driverName = "oracle.jdbc.driver.OracleDriver";
try {
	Class.forName(driverName);
} catch (ClassNotFoundException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}

              >> try/catch로 감싸야한다..!!
 2. DB에 연결

>> PROJECT >> Properties >> Java Build Path >> Libraries >> Add Exteranal JARs...

final String url = "jdbc:oracle:thin:@localhost:1521:xe";
final String user = "ryo";
final String password = "1234";
Connection conn = null; // 스코프 문제로..
Statement stmt = null;
try {
	conn = DriverManager.getConnection(url, user, password);
	// Connection은 import 해주어야 한다!(3-1)
	// connection이 반환
	stmt = conn.createStatement();
	// sql 가져오는 것이기에 sql 가져올 것(3-2)
	// stmt(statement) 객체가 확보되어야
	// JAVA에서 SQL문을 작성할 수 있게 됨!
	stmt.executeQuery("INSERT INTO STUDENT VALUES(((SELECT NVL(MAX(NUM),0)+1 FROM STUDENT),'홍길동',30,11,010-3251-7965)");
	System.out.println("로그 : DB연결하여 쿼리문 실행완료");
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}

 

 

 3. 데이터를 write, read
//    1) connection 객체 -> conn
//    2) stmt(statement) 객체 -> read(SELECT), write(INSERT,UPDATE,DELETE)

>> 위의 2번 예시를 볼 것!

 

 4. DB와의 연결을 해제☆ << 계속해서 연결하는 메모리를 소모... 장기적으로 봤을 땐 좋지 않다.

finally {
	// 1) 연결한 순서를 생각하며 해제
	// 2) conn -> stmt -> stmt 해제 -> conn 해제
	try {
		stmt.close();
		conn.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

>> 2번의 try / catch문에 대입

Scanner sc = new Scanner(System.in);
System.out.print("이름 입력 : ");
String name = sc.next();
System.out.print("나이 입력 : ");
int age = sc.nextInt();
System.out.print("성적 입력 : ");
int score = sc.nextInt();
System.out.print("전화번호 입력 : ");
String phone = sc.next();
stmt.executeQuery("INSERT INTO STUDENT VALUES((SELECT NVL(MAX(NUM),0)+1 FROM STUDENT),'"+name+"',"+age+","+score+",'"+phone+"')");
System.out.println("로그 : DB연결하여 쿼리문 실행완료");

 

----강사님 실습..? 

PRODUCT 테이블에 상품 추가하기

 

System.out.print("문고 입력 : ");
String name = sc.next();
System.out.print("가격 입력 : ");
int price = sc.nextInt();
System.out.print("개수 입력 : ");
int cnt = sc.nextInt();
System.out.print("카테고리 입력 : ");
String category = sc.next();
stmt.executeQuery("INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'"+name+"',"+price+","+cnt+",'"+category+"')");

 

>> 전체코드 다시 올리기!

package class04;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Test01 {
	public static void main(String[] args) {
		// DBMS 선정(오라클)
		// JAVA와 연동
		// [JDBC]
		// 1. DBMS와 연동을 하기 위한 드라이버가 필요!
		//    1) DBMS(오라클, MYSQL)에 해당하는 드라이버 파일을 갖고있나?
		//    2) 드라이버 파일 정보(파일 데이터)를 자바(JAVA)에 적재(로드, load)할 수 있는가?
		//      .jar의 데이터를 JAVA에 load하는 방법
		final String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//      
		// 2. DB에 연결(url, user, password)
		// database >> new Oracle > properties > driverproperties에 url
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "ryo";
		final String password = "1234";
		Connection conn = null; // 스코프 문제로..
		Statement stmt = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			// Connection은 import 해주어야 한다!(3-1)
			// connection이 반환
			stmt = conn.createStatement();
			Scanner sc = new Scanner(System.in);
			/*
			System.out.print("이름 입력 : ");
			String name = sc.next();
			System.out.print("나이 입력 : ");
			int age = sc.nextInt();
			System.out.print("성적 입력 : ");
			int score = sc.nextInt();
			System.out.print("전화번호 입력 : ");
			String phone = sc.next();
			*/
			// sql 가져오는 것이기에 sql 가져올 것(3-2)
			// stmt(statement) 객체가 확보되어야
			// JAVA에서 SQL문을 작성할 수 있게 됨!
//			stmt.executeQuery("INSERT INTO STUDENT VALUES((SELECT NVL(MAX(NUM),0)+1 FROM STUDENT),'"+name+"',"+age+","+score+",'"+phone+"')");
			System.out.print("문고 입력 : ");
			String name = sc.next();
			System.out.print("가격 입력 : ");
			int price = sc.nextInt();
			System.out.print("개수 입력 : ");
			int cnt = sc.nextInt();
			System.out.print("카테고리 입력 : ");
			String category = sc.next();
			stmt.executeQuery("INSERT INTO PRODUCT VALUES((SELECT NVL(MAX(PID),0)+1 FROM PRODUCT),'"+name+"',"+price+","+cnt+",'"+category+"')");
			System.out.println("로그 : DB연결하여 쿼리문 실행완료");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 3. 데이터를 write, read
		//    1) connection 객체 -> conn
		//    2) stmt(statement) 객체 -> read(SELECT), write(INSERT,UPDATE,DELETE)
		// 4. DB와의 연결을 해제☆ << 계속해서 연결하는 메모리를 소모... 장기적으로 봤을 땐 좋지 않다.
		finally {
			// 1) 연결한 순서를 생각하며 해제
			// 2) conn -> stmt -> stmt 해제 -> conn 해제
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

 

-- 하나 표현, 다 표현

package class06;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test03 {
	public static void main(String[] args) {
		final String driverName = "oracle.jdbc.driver.OracleDriver";
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "ryo";
		final String passwd = "1234";
		final String sql_selectOne = "SELECT NAME FROM STUDENT WHERE NUM=1";
		final String sql_selectAll = "SELECT NAME FROM STUDENT";
		Connection conn = null;
		Statement stmt = null;
			try {
				Class.forName(driverName);
				conn = DriverManager.getConnection(url, user, passwd);
				stmt = conn.createStatement();
				/*
				ResultSet rs = stmt.executeQuery(sql_selectOne);
				if(rs.next()) {
					System.out.println("이름 : " + rs.getString("NAME"));
				}
				else {
					System.out.println("결과없음");
				}
				rs.close();
				*/
				ResultSet rs = stmt.executeQuery(sql_selectAll);
				// forEach, Iterator, ...
				while(rs.next()) {
					System.out.println("이름 : "+rs.getString("NAME"));
					System.out.println("나이 : "+rs.getInt("AGE"));
					System.out.println("성적 : "+rs.getInt("SCORE"));
					System.out.println();
				}
				rs.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				try {
					conn.close();
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	}
}