2020.04.08

1. MVC 패턴 (Model View Controller)
 : 유지보수를 위해 사용함

 

 

2. [과제] 전화번호부 6.0 : 데이터베이스 이용하여 수정하기

package phonebook06.db;

// 전화번호부 6.0 : 파일이 아닌 데이터 베이스로 변경
import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


// CONTROLLER 객체
//   어플리케이션의 동작, 데이터 처리(CRUD), (Business logic 담당)


public class PhonebookManager implements Pb, Closeable {

	// TODO : DB를 위한 변수를 선언
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// singleton적용
	private PhonebookManager() {

		// TODO : 
		// JDBC 프로그래밍
		//  클래스 로딩
		//  연결 Connection
		//  Statement (필요하다면) 생성
		
		try {
			// JDBC 드라이버 클래스를 메모리에 로드
			Class.forName(DRIVER);	// 동적 클래스 로딩
			//System.out.println("드라이버 클래스 로딩 성공");

			// DB와 connection을 맺음
			conn = DriverManager.getConnection(URL, USER, PASSWD);
			//System.out.println("DB Connection 연결");
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	
	private static PhonebookManager instance = null;
	public static PhonebookManager getInstance() {
		if (instance == null) {
			instance = new PhonebookManager();
		}
		return instance;
	} // end getInstance()

	
	// 전화번호부 생성 등록
	@Override
	public int insert(String name, String phoneNum, String memo) {

		// 매개변수 검증 : 이름 필수
		if (name == null || name.trim().length() == 0) {
			throw new PhonebookException("insert() 이름입력오류: ", Pb.ERR_EMPTY_STRING);
		}

		int cnt = 0;
		
		// TODO
		// SQL_INSERT 사용하여 INSERT
		// PreparedStatement 사용.., 여기서 close도 필요(PreparedStatement만...!!)
		try {
			// "INSERT INTO phonebook(pb_uid, pb_name, pb_phonenum, pb_memo) 
			// VALUES(phonebook_seq.nextval, ?, ?, ?)"
			pstmt = conn.prepareStatement(SQL_INSERT);
			pstmt.setString(1, name);
			pstmt.setString(2, phoneNum);
			pstmt.setString(3, memo);
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null) {pstmt.close();}
				if(rs != null) {rs.close();}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return 1;
	}

	
	@Override
	public PhonebookModel[] selectAll() {
		
		// TODO
		// SQL_SELECT_ALL 사용하기
		PhonebookModel[] pb = null;
		int rowCount = 0;
		
		try {
			// Row 행의 값을 찾기
			pstmt = conn.prepareStatement(SQL_COUNT_ALL);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				rowCount = rs.getInt("cnt");
			}
			
			pstmt.close();
			rs.close();

			// SQL_SELECT_ALL 사용하기
			// SELECT * FROM phonebook ORDER BY pb_uid DESC
			pstmt = conn.prepareStatement(SQL_SELECT_ALL);
			rs = pstmt.executeQuery();
			
			pb = new PhonebookModel[rowCount];
			
			int Number = 0;
			
			while(rs.next()) {
				pb[Number] = new PhonebookModel(rs.getInt(COL_LABEL_UID), 
						rs.getString(COL_LABEL_NAME), 
						rs.getString(COL_LABEL_PHONENUM),
						rs.getString(COL_LABEL_MEMO),
						rs.getDate(COL_LABEL_REGDATE));
				Number++;
			} // end while
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null) {pstmt.close();}
				if(rs != null) {rs.close();}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return pb;
	}
	

	// 특정 uid 의 데이터 검색 리턴
	// 못찾으면 null 리턴
	@Override
	public PhonebookModel selectByUid(int uid) {

		// TODO : 옵션
		PhonebookModel pb = null;
		
		try {
			// "SELECT pb_uidFROM phonebook WHERE pb_uid = ?"
			pstmt = conn.prepareStatement(SQL_SELECT_CHOOSE);
			pstmt.setInt(1, uid);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				pb = new PhonebookModel(rs.getInt(COL_LABEL_UID), 
						rs.getString(COL_LABEL_NAME), 
						rs.getString(COL_LABEL_PHONENUM),
						rs.getString(COL_LABEL_MEMO),
						rs.getDate(COL_LABEL_REGDATE));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null) {pstmt.close();}
				if(rs != null) {rs.close();}
				return pb;
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return null; // 못찾으면 null 리턴
	}// end selectByUid()

	@Override
	public int updateByUid(int uid, String name, String phoneNum, String memo) {

		// 매개변수 검증
		if (uid < 1)
			throw new PhonebookException("update() uid 오류: " + uid, Pb.ERR_UID);
		int cnt = 0;

		// TODO
		// SQL_UPDATE_BY_UID  사용
		try {
			// "UPDATE phonebook SET pb_name = ?, 
			// pb_phonenum = ?, pb_memo = ? WHERE pb_uid = ?"
			pstmt = conn.prepareStatement(SQL_UPDATE_BY_UID);
			pstmt.setString(1, name);
			pstmt.setString(2, phoneNum);
			pstmt.setString(3, memo);
			pstmt.setInt(4, uid);
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null) {pstmt.close();}
				if(rs != null) {rs.close();}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return cnt;
	}

	
	@Override
	public int deleteByUid(int uid) {
		// 매개변수 검증
		if (uid < 1)
			throw new PhonebookException("deleteByUid() uid 오류: " + uid, Pb.ERR_UID);

		int cnt = 0;
		
		// TODO
		// SQL_DELETE_BY_UID 사용
		try {
			// "DELETE FROM phonebook WHERE pb_uid = ?"
			pstmt = conn.prepareStatement(SQL_DELETE_BY_UID);
			pstmt.setInt(1, uid);
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null) {pstmt.close();}
				if(rs != null) {rs.close();}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return cnt;
	}

	
	// 현재 데이터중 가장 큰 uid 값을 찾아서 리턴
	private int getMaxUid() {
		int maxUid = 0;

		try {
			//"SELECT MAX(pb_uid) FROM phonebook"
			pstmt = conn.prepareStatement(SQL_SELECT_MIX);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				maxUid = rs.getInt("uid_max");
				return maxUid;
			} // end while
			
			System.out.println(rs);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null) {pstmt.close();}
				if(rs != null) {rs.close();}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return maxUid;
	}

	
	@Override
	public void close() throws IOException {

		// TODO
		// ResultSet
		// Statement 
		// Connection
		// 들 close()
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
	} // end close()


} // end PhonebookManager


// 예의 클래스 정의
// 예외발생하면 '에러코드' + '에러메세지'를 부여하여 관리하는게 좋다.
class PhonebookException extends RuntimeException {

	private int errCode = Pb.ERR_GENERIC;

	public PhonebookException() {
		super("Phonebook 예외 발생");
	}

	public PhonebookException(String msg) {
		super(msg);
	}

	public PhonebookException(String msg, int errCode) {
		super(msg);
		this.errCode = errCode;
	}

	// Throwable 의 getMessage 를 오버라이딩 가능
	@Override
	public String getMessage() {
		String msg = "ERR-" + errCode + "]" + Pb.ERR_STR[errCode] + " " + super.getMessage();
		return msg;
	}

} // end PhonebookException

 

package phonebook06.db;

// Controller 인터페이스
//	동작 정의하기 전에 '동작 설계'
public interface Pb {
	
	public static final String VERSION = "전화번호부 6.0";
	
	// 만들고자 하는 응용프로그램의 
	// '동작' 을 설계하는 것이 인터페이스다.
	// 이 인터페이스는  View 와 Controller 의 연결고리 역할을 하여.  모듈단위 유지관리를 용이하게 해준다. 
	
	// [동작 설계]
	// 1. 이름(name)과 전화번호(phoneNum)과 (memo) 값이 주어지면 전화번호 데이터(PhonebookModel)를   
	//		생성하여 저장, 날짜(regDate) 는 생성한 날짜로, uid 값은 자동 증가 값으로 
	//		성공하면 1, 실패하면 0 리턴
	// 2. 현재 전화번호부에 저장된 전화번호 데이터(PhonebookModel)들을 
	//		전부 불러들여서 리턴(PhonebookModel 배열로)
	// 3. 특정 uid 값을 가진 전화번호 데이터(PhonebookModel)을 찾아서 
	//		리턴, 없으면 null 리턴
	// 4. 특정 uid 값을 가진 전화번호 데이터(PhonebookModel)을 찾아서 
	//		주어진 이름(name) 과 전화번호(phoneNum) 과 (memo) 값 으로 변경.  
	//		성공하면 1, 실패하면 0 리턴
	// 5. 특정 uid 값을 가진 전화번호 데이터(PhonebookModel) 을 찾아서 삭제.  
	//		성공하면 1, 실패하면 0 리턴
	
	
	public abstract int insert(String name, String phoneNum, String memo);	/* 1 */
	public abstract PhonebookModel[] selectAll();	/* 2 */
	public abstract PhonebookModel selectByUid(int uid);	/* 3 */
	public abstract int updateByUid(int uid, String name, String phoneNum, String meno);	/* 4 */
	public abstract int deleteByUid(int uid);	/* 5 */

	
	//public static final int QUERY_FAIL = 0;
	
	
	// 에러코드 상수
	public static final int ERR_GENERIC = 0;			// 일반 오류
	public static final int ERR_INDEXOUTOFRANGE = 1;	// 인덱스 범위 벗어남
	public static final int ERR_EMPTY_STRING = 2;		// 입력문자열이 empty (혹은 null)인 경우
	public static final int ERR_UID = 3;		// uid가 없는경우
	
	// 에러문자열
	public static final String[] ERR_STR = {
			"일반오류",	// 0
			"인덱스오류",	// 1
			"문자열오류",	// 2
			"UID 오류"	// 3
	};
	
	
	// 1. Oracle 연동을 위한 정보들(상수들)을 정의(세팅)
	// JDBC 드라이버 클래스 정보
	public static final String DRIVER =
			"oracle.jdbc.driver.OracleDriver";
	// DB 서버 정보
	public static final String URL =
			"jdbc:oracle:thin:@localhost:1521:XE";
	// DB 사용자 계정 정보
	public static final String USER = "scott0316";
	public static final String PASSWD = "tiger0316";

	public static final String TBL_NAME = "phonebook";
	
	public static final String COL_LABEL_UID = "pb_uid";
	public static final String COL_LABEL_NAME = "pb_name";
	public static final String COL_LABEL_PHONENUM = "pb_phonenum";
	public static final String COL_LABEL_MEMO = "pb_memo";
	public static final String COL_LABEL_REGDATE = "pb_regdate";
	public static final String SEQ_NAME = "phonebook_seq";
	
	// 쿼리 문자열
	// PreparedStatement 방식
	public static final String SQL_COUNT_ALL
		= "SELECT COUNT(*) cnt FROM " + TBL_NAME;
	
	public static final String SQL_SELECT_ALL
		= "SELECT * FROM " + TBL_NAME
		+ " ORDER BY " + COL_LABEL_UID + " DESC";
	
	public static final String SQL_INSERT
		= "INSERT INTO " + TBL_NAME 
		+ "(pb_uid, pb_name, pb_phonenum, pb_memo) "
		+ "VALUES(" + SEQ_NAME + ".nextval, ?, ?, ?)";
	
	public static final String SQL_UPDATE_BY_UID
		= "UPDATE " + TBL_NAME + " SET "
		+ COL_LABEL_NAME + " = ?, "
		+ COL_LABEL_PHONENUM + " = ?, "
		+ COL_LABEL_MEMO + " = ? "
		+ "WHERE " + COL_LABEL_UID + " = ?";
	
	public static final String SQL_DELETE_BY_UID
		= "DELETE FROM " + TBL_NAME
		+ " WHERE " + COL_LABEL_UID + " = ?";
	
	// TODO : 추가로 필요한 쿼리문
	//SELECT * FROM phonebook WHERE pb_uid = 3;
	public static final String SQL_SELECT_CHOOSE
		= "SELECT * FROM " + TBL_NAME + " WHERE " 
		+ COL_LABEL_UID + " = ?";
	
	//SELECT MAX(pb_uid) FROM phonebook;
	public static final String SQL_SELECT_MIX
		= "SELECT MAX(" + COL_LABEL_UID 
		+ ") uid_max FROM " + TBL_NAME;

}

 

package phonebook06.db;

import java.io.IOException;
import java.util.InputMismatchException;
import java.util.Scanner;


// VIEW 객체
// 사용자와의 입출력 담당 (UI,..) 
public class PhonebookMain {

	private Scanner sc;
	private PhonebookManager pbCtrl;   // CONTROLLER 객체
	
	public static void main(String[] args) {
		PhonebookMain app = new PhonebookMain();
		app.init();   // 초기화
		app.run();   // 실행
		app.exit();   // 종료
	} // end main()
	
	
	// 응용프로그램을 초기화 
	public void init() {
		sc = new Scanner(System.in);
		pbCtrl = PhonebookManager.getInstance();  // CONTROLLER 생성
	}
	
	// 응용프로그램 구동
	public void run() {
		System.out.println(Pb.VERSION);
		
		int menu;
		
		while(true) {
			showMenu();  // 메뉴 표시
			try {				
				menu = sc.nextInt(); // 메뉴 입력
				sc.nextLine();
				
				switch(menu) {
				case Menu.MENU_INSERT:
					insertPhoneBook();
					break;
				case Menu.MENU_LIST:
					listPhonebook();
					break;
				case Menu.MENU_DELETE:
					deletePhonebook();
					break;
				case Menu.MENU_UPDATE:
					updatePhonebook();
					break;
				case Menu.MENU_QUIT:
					pbCtrl.close();   // 파일 저장
					System.out.println("프로그램을 종료합니다");
					return;
				default:
					System.out.println("잘못 입력하셨습니다");
				} // end switch			
			}  catch (PhonebookException ex) {
				System.out.println(ex.getMessage());
			} catch (InputMismatchException ex) {
				System.out.println("잘못된 입력입니다");
				sc.nextLine();
			} catch (IOException e) {
				e.printStackTrace();
			}
		} // end while	
	} // end run()
	
	// 응용프로그램 종료
	public void exit() {
		sc.close();
	}	
	
	// 전화번호부 입력
	public void insertPhoneBook() {
		// VIEW 역할 : 사용자 입출력
		System.out.println("-- 입력 메뉴 --");
		
		// 이름, 전화번호, 이메일 입력
		System.out.print("이름 입력:");
		String name = sc.nextLine();

		System.out.print("전화번호 입력:");
		String phoneNum = sc.nextLine();
		
		System.out.print("메모 입력:");
		String memo = sc.nextLine();

		// CONTROLLER에 연결 
		int result = pbCtrl.insert(name, phoneNum, memo);
		System.out.println(result + " 개의 전화번호 입력 성공");
	} // end insertPhoneBook()
	
	// 전화번호부 열람 (전체)
	public void listPhonebook() {
		// CONTROLLER 연결
		PhonebookModel[] data = pbCtrl.selectAll();
		
		// VIEW 역할 : 사용자 입출력
		System.out.println("총 " + data.length + " 명의 전화번호 출력");
		for(PhonebookModel e : data) {
			System.out.println(e);
		}
	} // end listPhonebook()
	
	// 전화번호부 수정
	public void updatePhonebook() {
		// VIEW : 사용자 입출력
		System.out.println("--- 수정 메뉴 ---");
		System.out.println("수정할 번호 입력:");
		int uid = sc.nextInt();
		sc.nextLine();
		
		if(pbCtrl.selectByUid(uid) == null) {
			System.out.println("존재하지 않는 uid : " + uid);
			return;
		}		
		
		// 이름, 전화번호, 이메일 입력
		System.out.print("이름 입력:");
		String name = sc.nextLine();

		System.out.print("전화번호 입력:");
		String phoneNum = sc.nextLine();
		
		System.out.print("메모 입력:");
		String memo = sc.nextLine();

		
		// CONTROLLER 연결
		int result = pbCtrl.updateByUid(uid, name, phoneNum, memo);
		System.out.println(result + " 개의 전화번호 수정 성공");
		
	} // end updatePhonebook()
	
	// 전화번호부 삭제
	public void deletePhonebook() {
		// VIEW : 사용자 입출력
		System.out.println("--- 삭제 메뉴 ---");
		
		System.out.println("삭제할 번호 입력:");
		int uid = sc.nextInt();
		sc.nextLine();
		
		// CONTROLLER
		int result = pbCtrl.deleteByUid(uid);
		System.out.println(result + " 개의 전화번호 삭제 성공");
		
	} // end deletePhonebook()
	
	
	
	public void showMenu() {
		System.out.println();
		System.out.println("전화번호부 프로그램");
		System.out.println("------------------");
		System.out.println(" [0] 종료");
		System.out.println(" [1] 입력");
		System.out.println(" [2] 열람");
		System.out.println(" [3] 수정");
		System.out.println(" [4] 삭제");
		System.out.println("------------------");
		System.out.print("선택: ");
	}

} // end class

 

package phonebook06.db;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;

// MODEL 객체
// 데이터 표현 객체
public class PhonebookModel implements Serializable{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -7457878413286439105L;
	// 멤버변수
	private int uid;       // unique id
	private String name;     // 이름
	private String phoneNum; // 전화번호
	private String memo; // 메모
	private Date regDate;   // 등록일시
	
	// 기본생성자
	public PhonebookModel() {
		this.name = "";
		this.phoneNum = "";
		this.memo = "";
		this.regDate = new Date();  // 생성되는 현재시간.
	}
	// 매개변수 생성자
	public PhonebookModel(String name, String phoneNum, String email) {
		this();
		this.name = name;
		this.phoneNum = phoneNum;
		this.memo = email;
	}
	
	public PhonebookModel(int uid, String name, String phoneNum, String email, Date regDate) {
		super();
		this.uid = uid;
		this.name = name;
		this.phoneNum = phoneNum;
		this.memo = email;
		this.regDate = regDate;
	}
	
	
	// getter & setter
	public int getUid() {return uid;}
	public void setUid(int uid) {this.uid = uid;}
	public String getName() {return name;}
	public void setName(String name) {this.name = name;}
	public String getPhoneNum() {return phoneNum;}
	public void setPhoneNum(String phoneNum) {this.phoneNum = phoneNum;}
	public String getMemo() {return memo;}
	public void setMemo(String memo) {this.memo = memo;}
	public Date getRegDate() {return regDate;}
	public void setRegDate(Date regDate) {this.regDate = regDate;}
	
	@Override
	public String toString() {
		String str = String.format("%3d|%s|%s|%s|%20s", 
				uid, name, phoneNum, memo, 
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(regDate));
		return str;
	}
	
	
}

 

package phonebook06.db;

public interface Menu {
	public static final int MENU_QUIT = 0;
	public static final int MENU_INSERT = 1;
	public static final int MENU_LIST = 2;
	public static final int MENU_UPDATE = 3;
	public static final int MENU_DELETE = 4;
}

** 쌤이 선택한 코드..!!

package phonebook06.db;

import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

// CONTROLLER 객체
//   어플리케이션의 동작, 데이터 처리(CRUD), (Business logic 담당)
public class PhonebookManager implements Pb, Closeable {

	// DB 를 위한 변수들 선언	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// singleton적용
	private PhonebookManager() {

		// JDBC 프로그래밍
		//  클래스 로딩
		//  연결 Connection
		//  Statement (필요하다면) 생성
		try {
			Class.forName(DRIVER);
			System.out.println("드라이버 로딩 성공");

			conn = DriverManager.getConnection(URL, USER, PASSWD);
			System.out.println("DB Connection 성공");

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static PhonebookManager instance = null;

	public static PhonebookManager getInstance() {
		if (instance == null) {
			instance = new PhonebookManager();
		}
		return instance;
	} // end getInstance()

	// 전화번호부 생성 등록
	@Override
	public int insert(String name, String phoneNum, String memo) {

		// 매개변수 검증 : 이름 필수
		if (name == null || name.trim().length() == 0) {
			throw new PhonebookException("insert() 이름입력오류: ", Pb.ERR_EMPTY_STRING);
		}

		int cnt = 0;

		// SQL_INSERT 사용하여 INSERT
		// PreparedStatement 사용..
		try {
			pstmt = conn.prepareStatement(SQL_INSERT);
			pstmt.setString(1, name); // 첫번째 ? 는 1부터 시작! ,type 도 잘보기
			pstmt.setString(2, phoneNum); // ?개수 = index개수 인듯 , ?개수만큼 데이터추가해줘야한다. 하나라도 없으면 에러 ;
			pstmt.setString(3, memo);
			cnt = pstmt.executeUpdate(); // DML은 정수값을 리턴한다 그 이유는??
			System.out.println(cnt + " 개 행(row) INSERT성공");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return cnt;
	}

	@Override
	public PhonebookModel[] selectAll() {
		
		// SQL_SELECT_ALL 사용
		ArrayList<PhonebookModel> pbList = new ArrayList<PhonebookModel>();

		try {
			pstmt = conn.prepareStatement(SQL_SELECT_ALL);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				String name = rs.getString(COL_LABEL_NAME);
				String phonenum = rs.getString(COL_LABEL_PHONENUM);
				String memo = rs.getString(COL_LABEL_MEMO);
				
				String Date = rs.getString(COL_LABEL_REGDATE);
				SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date to = transFormat.parse(Date);

				// System.out.println(name + " | " + phonenum + " | " + memo);
				PhonebookModel pb = new PhonebookModel(rs.getInt(COL_LABEL_UID), name, phonenum, memo, to);
				pbList.add(pb);
				//pb.setUid(rs.getInt(COL_LABEL_UID));// ********* 여기어려워요 생각합시다!!
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return pbList.toArray(new PhonebookModel[pbList.size()]);
	}

	// 특정 uid 의 데이터 검색 리턴
	// 못찾으면 null 리턴
	@Override
	public PhonebookModel selectByUid(int uid) {
		PhonebookModel pb = null;
		try {
			pstmt = conn.prepareStatement(SQL_SELECT_BY_UID);
			pstmt.setInt(1, uid);
			rs = pstmt.executeQuery();

			if (rs.next()) {

				String name = rs.getString(COL_LABEL_NAME);
				String phonenum = rs.getString(COL_LABEL_PHONENUM);
				String memo = rs.getString(COL_LABEL_MEMO);
				pb = new PhonebookModel(name, phonenum, memo);
				pb.setUid(rs.getInt(COL_LABEL_UID));// ********* 여기어려워요 생각합시다!!
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return pb;// 못찾으면 null 리턴
	}// end selectByUid()

	@Override
	public int updateByUid(int uid, String name, String phoneNum, String memo) {

		// 매개변수 검증
		if (uid < 1)
			throw new PhonebookException("update() uid 오류: " + uid, Pb.ERR_UID);

		if (name == null || name.trim().length() == 0) // 이름 필수
			throw new PhonebookException("update() 이름입력 오류: ", Pb.ERR_EMPTY_STRING);

		int cnt = 0;

		// SQL_UPDATE_BY_UID  사용
		try {
			pstmt = conn.prepareStatement(SQL_UPDATE_BY_UID);
			pstmt.setString(1, name);
			pstmt.setString(2, phoneNum);
			pstmt.setString(3, memo);
			pstmt.setInt(4, uid);
			cnt = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return cnt;
	}

	@Override
	public int deleteByUid(int uid) {
		// 매개변수 검증
		if (uid < 1)
			throw new PhonebookException("deleteByUid() uid 오류: " + uid, Pb.ERR_UID);

		int cnt = 0;
		
		// SQL_DELETE_BY_UID 사용
		try {
			pstmt = conn.prepareStatement(SQL_DELETE_BY_UID);
			pstmt.setInt(1, uid);
			cnt = pstmt.executeUpdate();
			System.out.println(cnt + " 개 행(row) DELETE성공");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cnt;
	}

	// 현재 데이터중 가장 큰 uid 값을 찾아서 리턴
	private int getMaxUid() {
		int maxUid = 0;

		// TODO : 옵션

		return maxUid;
	}

	@Override
	public void close() throws IOException {

		// ResultSet
		// Statement 
		// Connection
		// 들 close()..
		try {
			if (rs != null)rs.close();
			if (pstmt != null)pstmt.close();
			if (conn != null)conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

} // end PhonebookManager


// 예의 클래스 정의
// 예외발생하면 '에러코드' + '에러메세지'를 부여하여 관리하는게 좋다.
class PhonebookException extends RuntimeException {

	private int errCode = Pb.ERR_GENERIC;

	public PhonebookException() {
		super("Phonebook 예외 발생");
	}

	public PhonebookException(String msg) {
		super(msg);
	}

	public PhonebookException(String msg, int errCode) {
		super(msg);
		this.errCode = errCode;
	}

	// Throwable 의 getMessage 를 오버라이딩 가능
	@Override
	public String getMessage() {
		String msg = "ERR-" + errCode + "]" + Pb.ERR_STR[errCode] + " " + super.getMessage();
		return msg;
	}

} // end PhonebookException

 

package phonebook06.db;

// Controller 인터페이스
//   동작 정의하기 전에 '동작 설계'
public interface Pb {
	
	public static final String VERSION = "전화번호부 6.0";
	
	public abstract int insert(String name, String phoneNum, String memo); /* 1 */
	public abstract PhonebookModel[] selectAll(); /* 2 */
	public abstract PhonebookModel selectByUid(int uid); /* 3 */
	public abstract int updateByUid(int uid, String name, String phoneNum, String memo); /* 4 */
	public abstract int deleteByUid(int uid);  /* 5 */
	
	//public static final int QUERY_FAIL = 0;
	
    
	// 에러코드 상수
	public static final int ERR_GENERIC = 0;   // 일반 오류
	public static final int ERR_INDEXOUTOFRANGE = 1;  // 인덱스 범위 벗어남
	public static final int ERR_EMPTY_STRING = 2;  // 입력문자열이 empty (혹은 null) 인 경우
	public static final int ERR_UID = 3;  // uid 가 없는 경우
	
	// 에러문자열
	public static final String[] ERR_STR = {
			"일반오류",  // 0
			"인덱스오류", // 1
			"문자열오류", // 2
			"UID 오류",  // 3
	};	
	
	
	// 1. Oracle 연동을 위한 정보들(상수들)을 정의(세팅)
	// JDBC 드라이버 클래스 정보
	public static final String DRIVER =
			"oracle.jdbc.driver.OracleDriver";
	// DB 서버 정보
	public static final String URL =
			"jdbc:oracle:thin:@localhost:1521:XE";
	// DB 사용자 계정 정보
	public static final String USER = "scott0316";
	public static final String PASSWD = "tiger0316";
	
	public static final String TBL_NAME = "phonebook";
	
	public static final String COL_LABEL_UID = "pb_uid";
	public static final String COL_LABEL_NAME = "pb_name";
	public static final String COL_LABEL_PHONENUM = "pb_phonenum";
	public static final String COL_LABEL_MEMO = "pb_memo";
	public static final String COL_LABEL_REGDATE = "pb_regdate";
	public static final String SEQ_NAME = "phonebook_seq";
	
    
	// 쿼리 문자열
	// PreparedStatement 방식
	
	public static final String SQL_COUNT_ALL 
				= "SELECT COUNT(*) cnt FROM " + TBL_NAME;
	
	public static final String SQL_SELECT_ALL
			= "SELECT * FROM " + TBL_NAME 
			+ " ORDER BY " + COL_LABEL_UID + " DESC";
	
	public static final String SQL_INSERT 
			= "INSERT INTO " + TBL_NAME 
				+ "(pb_uid, pb_name, pb_phonenum, pb_memo) "
				+ "VALUES(" + SEQ_NAME + ".nextval, ?, ?, ?)"
				;
	
	public static final String SQL_UPDATE_BY_UID
				= "UPDATE " + TBL_NAME + " SET "
				+ COL_LABEL_NAME + " = ?, "
				+ COL_LABEL_PHONENUM + " = ?, "
				+ COL_LABEL_MEMO + " = ? "
				+ "WHERE " + COL_LABEL_UID + " = ?"
				;
	
	public static final String SQL_DELETE_BY_UID
				= "DELETE FROM " + TBL_NAME
				+ " WHERE " + COL_LABEL_UID + " = ?"
				;
	
	public static final String SQL_SELECT_BY_UID
	= "SELECT * FROM "+TBL_NAME
	+ " WHERE "+ COL_LABEL_UID+ " = ?"
	;
	
}

 

 

3. [폴더] 04_SingleRowFunction

1) SingleRow1_String.sql

-- INSTR() 함수
SELECT INSTR('A*B*C*', '*', 1, 1) FROM dual;	-- 2
SELECT INSTR('A*B*C*', '*', 1, 2) FROM dual;	-- 4
SELECT INSTR('A*B*C*', '*', 3, 2) FROM dual;	-- 6
-- 음수 인덱스의 경우 검색도 음의 방향으로 진행
SELECT INSTR('A*B*C*', '*', -4, 1) FROM dual;	-- 2
-- 없으면 0 리턴  
SELECT INSTR('A*B*C*', '*', -4, 2) FROM dual;	-- 0  
SELECT INSTR('A*B*C*', '*', -2, 2) FROM dual;	-- 2  

-- t_student 테이블 : tel 칼럼을 사용하여 101번 학과(deptno1)의 
-- 학생의 이름과 전화번호, ‘)’ 가 나오는 위치 출력 
--SELECT name, tel, INSTR(tel, ')', 1, 1) AS 위치 
SELECT name, tel, INSTR(tel, ')') AS 위치 
FROM t_student
WHERE deptno1 = 101;

-- t_student 테이블 : 1전공이 101 인 학생의 이름과 전화번호, 
-- 지역번호를 출력하세요. 지역번호는 숫자만!  / substr(), instr() 사용
SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, ')')- 1) 지역변수
FROM t_student
WHERE deptno1 = 101;


-- LTRIM(), RTRIM(), TRIM 함수
SELECT 
	LTRIM('슈퍼슈퍼슈가맨', '슈퍼') LTRIM, 
	LTRIM('슈퍼슈퍼슈가맨', '슈') LTRIM, 
	'      슈퍼슈가맨',
	LTRIM('      슈퍼슈가맨', ' ') LTRIM, 
	LTRIM('      슈퍼슈가맨') LTRIM,	-- 디폴트로 '공백' 제거
	RTRIM('우측 공백 제거      ') RTRIM,
	TRIM('       슈퍼맨              ') TRIM,	-- 좌우 공백 제거
	LTRIM('******10000', '*') 
FROM dual;

-- p_dept2 테이블에서 부서명(dname) 을 출력하되 
-- 오른쪽 끝에 ‘부’ 라는 글자는 제거하고 출력하세요.
SELECT * FROM t_dept2;

SELECT dname, RTRIM(dname, '부') RTRIM예제 
FROM t_dept2;

-- REPLACE 함수
SELECT REPLACE('슈퍼맨 슈퍼걸', '슈퍼', '파워') REPLACE
FROM dual;

SELECT REPLACE('아버지가 방에 들어간다', ' ', '')
FROM dual;

-- t_student 테이블에서 102번 학과(deptno1) 의 학생들의 이름을 출력하되 
-- 성 부분은 ‘#’  으로 표시되게 출력하세요 / replace() , substr()사용 
SELECT name, REPLACE(name, SUBSTR(name, 1, 1), '#') 학생
FROM t_student
WHERE deptno1 = 102;


-- t_student 테이블에서 101번 학과(deptno1)의 학생들의 이름을 
-- 출력하되 가운데 글자만 ‘#’으로 표시되게 출력하세요 / replace() , substr()사용
SELECT name, REPLACE(name, SUBSTR(name, 2, 1), '#') 학생
FROM t_student
WHERE deptno1 = 101;


-- t_student 테이블에서 1전공(deptno1)이 101번인 학생들의 
-- 이름과 주민등록번호를 출력하되 주민등록번호의 뒤 7자리는 ‘*’ 로 표시되게 출력
SELECT name, jumin, REPLACE(jumin, SUBSTR(jumin, 7, 7), '*******') 주민번호
FROM t_student
WHERE deptno1 = 101;


-- t_student 테이블에서 다음과 같이 1전공(deptno1)이 102번인 
-- 학생들의 이름(name)과 전화번호(tel), 전화번호에서 국번 부분만 ‘#’ 처리하여 출력하세요.
-- 단 모든 국번은 3자리로 간주합니다. replace() / substr() / instr() 사용
SELECT name, tel, REPLACE(tel, SUBSTR(tel, instr(tel, ')', 1) + 1, 3), '###') 전화번호
FROM t_student
WHERE deptno1 = 102;

 

2) SingleRow2_Null.sql

SELECT * FROM t_professor;

-- null값과의 연산 결과는 null이다!!
SELECT name, pay, bonus
FROM t_professor;

-- 그룹함수에서는 동작, null은 연산에서 제외되어 동작
SELECT SUM(pay), SUM(bonus) FROM t_professor;


-- nvl() 함수
SELECT name, pay, bonus,
	pay + bonus 총지급액,
	pay + nvl(bonus, 0) 총지급액
FROM t_professor;

-- Professor 테이블 : 101번 학과 교수들의 이름(name), 급여(pay), 
-- 보너스(bonus), 연봉(pay) 출력하기. 연봉은 pay * 12 + bonus 로 계산,  
-- bonus 가 없는 교수는 0 으로 계산
SELECT name 이름, pay 급여, bonus 보너스, 
	nvl(bonus, 0) bonus,
	pay * 12 + nvl(bonus, 0) 연봉
FROM t_professor WHERE deptno = 101;

SELECT name 이름, pay 급여,
	nvl2(bonus, bonus, 0) bonus,
	nvl2(bonus, pay * 12 + bonus, pay * 12) 연봉
FROM t_professor WHERE deptno = 101;

 

3) SingleRow3_Conversion.sql

-- 묵시적 자동 형변환
SELECT 1 + 1 FROM dual;
SELECT '1' + 1 FROM dual;	-- 자바와 반대..!!

SELECT to_number('1') + 1 FROM dual;


-- 형변환 함수  : TO_CHAR, TO_NUMBER, TO_DATE

-- ####################################
-- TO_CHAR (날짜 -> 문자)
SELECT 
	SYSDATE,
	TO_CHAR(SYSDATE, 'YYYY') 연도4자리,
	TO_CHAR(SYSDATE, 'RRRR') 연도Y2K버그이후,
	TO_CHAR(SYSDATE, 'YY') 연도2자리,
	TO_CHAR(SYSDATE, 'YEAR') 연도영문
FROM dual;

SELECT 
	TO_CHAR(SYSDATE, 'DD') 일숫자2자리,
	TO_CHAR(SYSDATE, 'DDTH') 몇번째날,
	TO_CHAR(SYSDATE, 'DAY') 요일,
	TO_CHAR(SYSDATE, 'Dy') 요일앞자리
FROM dual;

SELECT 
	TO_CHAR(SYSDATE, 'MM') 월2자리,
	TO_CHAR(SYSDATE, 'MON') 월3자리,
	TO_CHAR(SYSDATE, 'MONTH')  월전체,
	TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE=ENGLISH') 월영문3자리, 
	TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE=ENGLISH')  "월영문전체(대)", 
	TO_CHAR(SYSDATE, 'month', 'NLS_DATE_LANGUAGE=ENGLISH')  "월영문전체(소)",  
	TO_CHAR(SYSDATE, 'Month', 'NLS_DATE_LANGUAGE=ENGLISH')  "월영문전체(첫글자대)" 
FROM dual;

SELECT 
	TO_CHAR(SYSDATE, 'HH24') 시24hr,
	TO_CHAR(SYSDATE, 'HH')  시12hr,
	TO_CHAR(SYSDATE, 'MI') 분,
	TO_CHAR(SYSDATE, 'SS') 초,
	TO_CHAR(SYSDATE, 'HH:MI:SS') 
FROM dual;

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 날짜
FROM dual;

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY"년"MM"월"DD"일" HH24"시"MI"분"SS"초"') 날짜
FROM dual;

-- t_student 테이블의 생일(birthday) 이 3월인 학생의 이름(name) 과 생일(birthday) 를 
-- 다음과 같은 형식으로 출력하세요 (TO_CHAR 사용)
SELECT name, TO_CHAR(birthday, 'YYYY-MM-DD') 생일
FROM t_student
WHERE TO_CHAR(birthday, 'MM') = '03';

-- TO_CHAR : 대소문자 지정
SELECT
	SYSDATE,
	TO_CHAR(SYSDATE, 'Dy Month DD, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS A1,
	TO_CHAR(SYSDATE, 'dy month dd, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS A2,
	TO_CHAR(SYSDATE, 'DY MONTH DD, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS A3
FROM DUAL;

-- TO_CHAR() 함수  - 숫자를 문자로 변환
SELECT
	1234,
	TO_CHAR(1234, '99999') "9하나당 1자리",
	TO_CHAR(1234, '099999') "빈자리 0으로",
	TO_CHAR(1234, '$9999') "빈자리 $로",
	TO_CHAR(1234.1264, '9999.99') "소숫점이하2자리",
	TO_CHAR(123456789, '999,999,999')  "천단위에 구분기호"
FROM dual;

-- t_professor 테이블에서 101번 학과 교수들의 이름(name), 연봉(pay) 를 출력하세요,
-- 단, 연봉은 (pay * 12) + bonus로 계산하고 천 단위 구분 기호로 표시하세요.
-- (nvl() / to_char() 사용)
SELECT name, TO_CHAR((pay*12) + nvl(bonus, 0), '99,999') 연봉
FROM t_professor
WHERE deptno=101;


-- ####################################
-- TO_NUMBER() 함수 - 숫자로 변환
SELECT TO_NUMBER('123.45') FROM dual;


-- ####################################
-- TO_DATE() 함수 - 문자 -> 날짜로 변환
SELECT 
	TO_DATE('2020-04-08', 'YYYY-MM-DD') TO_DATE 
FROM dual;

-- t_professor 테이블에서 2000년 이전에 입사한 교수명과 입사일,
-- 현재 연봉과 10% 인상 후 연봉을 아래와 같은 양식으로 출력하세요.
-- 연봉은 보너스(bonus)를 제외한 pay * 12 로 계산하고
-- 연봉과 인상후 연봉은 천단위 구분 기호를 추가하여 출력하세요
SELECT name,
	TO_CHAR(hiredate, 'YYYY-MM-DD') 입사일,
	TO_CHAR(pay * 12, '99,9999') 연봉,
	TO_CHAR(pay * 12 * 1.1, '99,999') 인상후
FROM t_professor
WHERE To_CHAR(hiredate, 'YYYY') < '2000';

 

4) SingleRow4_Number.sql

-- 숫자 관련 단일행 함수들.

-- ROUND() 함수 : 반올림
SELECT 
	'ROUND',
	ROUND(12.34) "(12.34)",
	ROUND(12.536) "(12.536)",
	ROUND(12.536, 2) "(12.536, 2)",	-- 소숫점 3자리에서 반올림
	ROUND(16.345, -1) "(16.345, -1)"	-- 1의 자리에서 반올림
FROM dual;


-- TRUNC() 함수 : 자르기
SELECT
	'TRUNC',
	TRUNC(12.345) "(12.345)",         -- 소수점 자름 (디폴트)
	TRUNC(12.345, 2) "(12.345, 2)",   -- 소수점 3자리부터 자름
	TRUNC(12.345, -1) "(12.345, -1)"  -- 1의 자리부터 자름
FROM dual;


-- ROUND() TRUNC() CEIL() FLOOR() 함수 비교  
SELECT
	'12.5' "12.5",
	ROUND(12.5)	"ROUND",
	TRUNC(12.5) "TRUNC",
	CEIL(12.5) "CEIL",
	FLOOR(12.5) "FLOOR"
FROM dual;

SELECT
	'-12.5' "-12.5",
	ROUND(-12.5)	"ROUND",
	TRUNC(-12.5) "TRUNC",
	CEIL(-12.5) "CEIL",
	FLOOR(-12.5) "FLOOR"
FROM dual;

SELECT
	'12.1' "12.1",
	ROUND(12.1)	"ROUND",
	TRUNC(12.1) "TRUNC",
	CEIL(12.1) "CEIL",
	FLOOR(12.1) "FLOOR"
FROM dual;


-- 오라클은 % 연산자 없음
-- MOD() : 나머지 연산
SELECT 
	MOD(12, 10) "MOD(12, 10)",
	MOD(12.6, 4.1) "MOD(12.6, 4.1)"
FROM  dual;


-- POWER() 제곱
SELECT 
	POWER(3, 2) "POWER(3, 2)", 
	POWER(-3, 3) "POWER(-3, 3)", 
	POWER(10, -2) "POWER(10, -2)", 
	POWER(2, 1/2) "sqrt(2)",
	POWER(27, 1/3) "POWER(27, 1/3)" 
FROM dual;

 

5) SingleRow5_DateTime.sql

-- 날짜 함수
SELECT SYSDATE FROM dual;


-- 기본적인 날짜 함수
SELECT 
	SYSDATE "오늘",
	SYSDATE + 1 "내일(24hr뒤)",
	SYSDATE - 2 "그저께",
	SYSDATE + 1/24 "한시간뒤"
FROM dual;


-- 일자 차이 계산
SELECT 
	SYSDATE "오늘", 
	TO_DATE('2020-03-16 09:00:00', 'YYYY-MM-DD hh:mi:ss') "시작한날", 
	SYSDATE - TO_DATE('2020-03-16 09:00:00', 'YYYY-MM-DD hh:mi:ss') "경과" 
FROM dual;


-- MONTHS_BETWEEN : 날짜 사이의 개월수
SELECT 
	-- 규칙1 : 두 날짜 중 큰 날짜를 먼저 써야 양수 값으로 나옴
	MONTHS_BETWEEN('2012-03-01', '2012-01-01') "양수값", 
	MONTHS_BETWEEN('2012-01-01', '2012-03-01') "음수값",
	-- 규칙2: 두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값
	MONTHS_BETWEEN('2012-02-29', '2012-02-01') "2/29-2/1", 
	MONTHS_BETWEEN('2012-04-30', '2012-04-01') "4/30-4/1", 
	MONTHS_BETWEEN('2012-05-31', '2012-05-01') "5/31-5/1" 
FROM dual;

-- t_professor 테이블에서 오늘(SYSDATE)을 기준으로 
-- 근속연수, 근속개월,  근속일를 계산해서 출력
-- 날짜양식은 YYYY-MM-DD로, 근속개월, 근속일은
-- 반올림 하여 소수점 1자리까지 표현
SELECT
	name "이름", 
	TO_CHAR(SYSDATE, 'YYYY-MM-DD') "오늘", 
	TO_CHAR(hiredate, 'YYYY-MM-DD') "입사일",
	TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(hiredate, 'YYYY') "근속연수",
	ROUND(MONTHS_BETWEEN(SYSDATE, hiredate), 1) "근속개월",
	ROUND(SYSDATE - hiredate, 1) "근속일"
FROM t_professor;


-- ADD_MONTH() 개월 추가
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM dual;

SELECT
	SYSDATE,
	LAST_DAY(SYSDATE) "이번달 마지막날",
	NEXT_DAY(SYSDATE, '월')  "다음 월요일"
FROM dual;


-- 날짜의 ROUND() 함수  ,  하루의 반은 정오 12:00:00 이다. 이를 넘어서면 다음 날짜
-- 날짜의 TRUNC() 함수,  무조건 당일 출력.
-- 원서 접수나 상품 주문 등에서 오전까지 접수된 건은 당일 접수 처리. 오후접수는 익일 처리 등에서 사용.
SELECT
	SYSDATE,
	ROUND(SYSDATE),
	TRUNC(SYSDATE)
FROM dual;

 

 

4. [폴더] 05_AggregateFunction
1) Agg1.sql

SELECT * FROM t_professor;

-- 그룹함수에서는 NULL값은 계산에서 제외
SELECT COUNT(*), COUNT(hpage) FROM t_professor;
SELECT COUNT(bonus), SUM(bonus), AVG(bonus) FROM t_professor;
SELECT MAX(hiredate), MIN(hiredate) FROM t_emp;

-- null 허용 컬럼의 그룹함수 적용시
-- nvl, nvl2 사용해야 함
SELECT AVG(bonus), AVG(NVL(bonus, 0))
FROM t_professor;

-- ★★ 불가능하다! SELECT절에 group 함수와 group 함수가 아닌 것과는 절대로 같이 출력 불가 ★★
-- SELECT deptno, AVG(bonus)
-- FROM t_professor;

-- t_professor 테이블에서 ‘학과별’로 교수들의 평균 보너스를 출력하세요
SELECT deptno, ROUND(AVG(NVL(bonus, 0)), 1) 보너스평균
FROM t_professor
GROUP BY deptno;

-- t_professor 테이블 : 학과별(deptno) 그리고 직급별(position)로 
-- 교수들의 평균 급여를 계산하여 출력하세요
SELECT deptno, POSITION, avg(pay) 평균급여
FROM t_professor
GROUP BY deptno, position	-- 1. 학과별 그룹핑, 2. 직급별 그룹핑
ORDER BY deptno ASC, POSITION ASC;

-- 부서별 평균 급여를 출력하되, 평균 급여가 450보다 많은 학과만 출력
SELECT deptno, avg(pay) 평균급여
FROM t_professor
--WHERE avg(pay) > 450	-- 그룹함수는 WHERE 절에서 사용 불가
GROUP BY deptno;

-- HAVING : 그룹함수 결과에 대한 조건절
SELECT deptno, avg(pay) 평균급여
FROM t_professor
GROUP BY deptno
HAVING avg(pay) > 300;


-- <SELECT 쿼리문 순서>
-- SELECT
-- FROM
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY


-- t_emp 테이블: 매니저별(MGR)로 관리하는 직원들의 ‘매니저’, ‘직원수’와 
-- ‘급여총액’과 ‘급여평균’과 ‘교통비 (COMM) 평균’ 지급액 을 출력하세요.  
-- 단 사장님은 (job = president)제외 
SELECT 
	mgr 매니저,
	COUNT(*) 직원수, 
	SUM(sal) 급여총액, 
	TRUNC(AVG(sal)) 급여평균,
	AVG(NVL(comm, 0)) 교통비평균
FROM t_emp
WHERE job <> 'president'
GROUP BY mgr;

-- t_professor 테이블 :  직위가 정교수 혹은 조교수 인 분들 중에서 ‘과별(deptno)’로  
-- 과번호, 소속교수 총수, 근속일 평균, 급여평균, 보너스 평균을 출력해보세요
SELECT 
	deptno,
	count(*) 총인원,
	ROUND(AVG(SYSDATE - hiredate), 1) 근속평균,
	AVG(pay) 급여평균, 
	AVG(NVL(bonus, 0)) 보너스평균
FROM t_professor
WHERE POSITION LIKE '%교수'
GROUP BY deptno;

-- t_student 테이블 : 학과별(deptno1)로, 학과번호, 
-- 최대몸무게 - 최소몸무게 차이 값을 출력해보세요
SELECT deptno1 학과, max(weight) - min(weight) 최대최소몸무게차
FROM t_student
GROUP BY deptno1;

-- 그 차이가 30 이상인것만 출력하려면?
SELECT deptno1 학과, max(weight) - min(weight) 최대최소몸무게차
FROM t_student
GROUP BY deptno1
HAVING max(weight) - min(weight) >= 30;

 

 

5. [폴더] 06_Join

1) Join1.sql

-- JOIN

-- 20 레코드
-- FORM 절에 테이블에도 별칭(alias)를 줄 수 있다!
SELECT s.studno, s.name, s.deptno1
FROM t_student s;

-- 12 레코드
SELECT d.deptno, d.dname
FROM t_department d;

-- 240 레코드
SELECT *
FROM t_student s, t_department d;

-- 240 레코드에 5개의 컬럼만 뽑아냄..!!
SELECT s.studno, s.name, s.deptno1,
	d.deptno, d.dname
FROM t_student s, t_department d;	-- Oracle 방식

SELECT s.studno, s.name, s.deptno1,
	d.deptno, d.dname
FROM t_student s
	CROSS JOIN t_department d;	-- ANSI 방식


-- 카티션곱 (Cartesian Product)
-- 두개의 테이블을 JOIN 하게 되면, 
-- 각 테이블의 레코드들의 모든 조합이 출력된다.
-- WHERE 나 ON 등으로 JOIN 조건이 주어지지 않으면 
-- 모든 카티션곱이 출력된다. 


-- Equi Join (등가 Join)
-- Oracle 방식
SELECT s.name "학생이름", s.deptno1 "학과번호", d.dname "학과이름"
FROM t_student s, t_department d
WHERE s.deptno1 = d.deptno;	-- 조건(Equi Join)

-- ANSI 방식
SELECT s.name "학생이름", s.deptno1 "학과번호", d.dname "학과이름"
FROM t_student s JOIN t_department d ON s.deptno1 = d.deptno;	-- 조건(Equi Join)

SELECT * FROM t_student;

-- 제2전공은?
SELECT s.name "학생이름", s.deptno2 "제2학과", d.dname "제2학과이름"
FROM t_student s, t_department d
WHERE s.deptno2 = d.deptno;

-- t_student 테이블, t_professor 테이블 을 join하여 
-- ‘학생이름’, ‘지도교수 번호’, ‘지도교수이름’ 을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_professor;

-- Oracle 방식
SELECT s.name "학생이름", p.profno "지도교수 번호", p.name "지도교수 이름"
FROM t_student s, t_professor p
WHERE s.profno = p.profno;	-- join 조건

-- ANSI 방식
-- JOIN하고 ON은 쌍으로 들어가야 함..!!
SELECT s.name "학생이름", p.profno "지도교수 번호", p.name "지도교수 이름"
FROM t_student s JOIN t_professor p 
	ON s.profno = p.profno;	-- join 조건

-- t_student, t_department, t_professor 테이블을 join하여 
-- 학생의 이름, 학과이름, 지도교수 이름 을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_department;
SELECT * FROM t_professor;

-- Oracle 방식
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s, t_department d, t_professor p
WHERE s.deptno1 = d.deptno AND s.profno = p.profno;

-- ANSI 방식
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s 
	JOIN t_department d ON s.deptno1 = d.deptno
	JOIN t_professor p ON s.profno = p.profno;

-- t_emp2 직원 테이블과 t_post 직급 테이블을 조회하여 
-- 사원의 이름과 직급, 현재연봉, 해당직급의 연봉의 하한금액(s_pay)과 
-- 상한금액(e_pay)을 출력하세요
SELECT * FROM t_emp2;
SELECT * FROM t_post;

-- Oracle 방식
SELECT 
	e.name 사원이름, e.post 현재직급, e.pay 현재연봉, 
	p.s_pay 하한금액, p.e_pay 상한금액
FROM t_emp2 e, t_post p
WHERE e.post = p.post;

-- ANSI 방식
SELECT 
	e.name 사원이름, e.post 현재직급, e.pay 현재연봉, 
	p.s_pay 하한금액, p.e_pay 상한금액
FROM t_emp2 e JOIN t_post p ON e.post = p.post;

-- t_student - t_professor 테이블 join 하여 제1전공(deptno1)이 
-- 101번인 학생들의 학생이름과 지도교수 이름을 출력하세요

-- Oracle 방식
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s, t_professor p
WHERE s.profno = p.profno AND s.deptno1 = 101;

-- ANSI 방식
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s JOIN t_professor p ON s.profno = p.profno 
WHERE s.deptno1 = 101;

-- JOIN 조건은 반드시 ON으로 해야하나 
-- 일반적인 조건은 WHERE을 써도 되고 ON 다음에 AND를 써도 된다
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s JOIN t_professor p ON s.profno = p.profno 
AND s.deptno1 = 101;

2) Join2.sql

-- 비등가 조인(Non-Equi Join)

SELECT * FROM t_customer;
SELECT * FROM t_gift;

-- t_customer 테이블, t_gift 테이블을 join하여  
-- 고객의 마일리지 포인트별로 받을수 있는 상품을 조회하여 
-- 고객의 이름(c_name)과 포인트(c_point), 상품명(g_name)을 출력하세요
-- BETWEEN ~ AND ~ 사용

-- Oracle 방식
SELECT c.c_name 고객명, c.c_point POINT, g.g_name 상품명
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end;

-- ANSI 방식
SELECT c.c_name 고객명, c.c_point POINT, g.g_name 상품명
FROM t_customer c JOIN t_gift g
	ON c.c_point BETWEEN g.g_start AND g.g_end;

-- 앞 예제에서 조회한 상품의 
-- 이름(g_name)과  필요수량이 몇개인지 조회
-- (그룹함수 동원되어야 한다)

-- Oracle 방식
SELECT g.g_name 상품명, COUNT(*) 필요수량
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;

-- ANSI 방식
SELECT g.g_name 상품명, COUNT(*) 필요수량
FROM t_customer c JOIN t_gift g
	ON c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;

-- t_student 테이블과 t_exam01 시험성적 테이블, 
-- t_credit 학점 테이블을 조회하여  학생들의 이름과 점수와 학점을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_exam01;
SELECT * FROM t_credit;

-- Oracle 방식
SELECT s.name 학생이름, e.total 점수, c.grade 학점
FROM t_student s, t_exam01 e, t_credit c
WHERE s.studno = e.studno AND e.total BETWEEN c.min_point AND c.max_point;

-- ANSI 방식
SELECT s.name 학생이름, e.total 점수, c.grade 학점
FROM t_student s 
	JOIN t_exam01 e ON s.studno = e.studno
	JOIN t_credit c ON e.total BETWEEN c.min_point AND c.max_point;

-- t_customer 와 t_gift 테이블 join : 자기 포인트(c_point)보다 
-- 낮은 포인트의 상품 중 한가지를 선택할수 있다고 할때 '산악용자전거'를 
-- 선택할 수 있는 고객명(c_name)과 포인트(c_point), 상품명(g_name)을 출력하세요
SELECT * FROM t_gift;

-- Oracle 방식
SELECT c.c_name 고객명, c.c_point 뽀인트, g.g_name 상품명
FROM t_customer c, t_gift g
WHERE c.c_point >= g.g_start AND g.g_name = '산악용자전거';

-- ANSI 방식
-- 일반 조건문은 WHERE, AND 둘 다 사용 가능함
SELECT c.c_name 고객명, c.c_point 뽀인트, g.g_name 상품명
FROM t_customer c JOIN t_gift g
	ON c.c_point >= g.g_start 
	WHERE g.g_name = '산악용자전거';

SELECT c.c_name 고객명, c.c_point 뽀인트, g.g_name 상품명
FROM t_customer c JOIN t_gift g
	ON c.c_point >= g.g_start 
	AND g.g_name = '산악용자전거';

-- t_emp2, t_post 테이블 사용하여
-- 사원들의 이름(name)과 나이, 현재직급(post), ‘예상직급’을 출력하세요. 
-- ‘예상직급’은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미합니다. 
-- 나이는 오늘(SYSDATE)을 기준으로 하되 소수점 이하는 절삭하여 계산하세요
-- t_emp2 의 직급(post) 은 null 허용함에 주의)
-- 나이계산은 어떻게? (현재연도 - 생년월일연도) + 1, 
-- SYSDATE, TO_CHAR() 사용
SELECT * FROM t_emp2;
SELECT * FROM t_post;

-- Oracle 방식
SELECT 
	e.name 이름, 
	(TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1) "현재나이",
	NVL(e.post, ' ') "현재직급", p.post "예상직급"
FROM t_emp2 e, t_post p
WHERE (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1)
BETWEEN p.S_AGE AND p.E_AGE;

-- ANSI 방식
SELECT e.name "이름", 
	(TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1) "현재나이",
	NVL(e.post, ' ') "현재직급", p.post "예상직급"
FROM t_emp2 e INNER JOIN t_post p
	ON (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1)
	BETWEEN p.S_AGE AND p.E_AGE ;


-- OUTER Join (아우터 조인) : Oracle 방식은 없어..!!!
-- (ANSI 구문 only) 

-- t_student 테이블과 t_professor 테이블 Join 하여 
-- 학생이름과 지도교수 이름을 출력하세요. 
-- 단! 지도교수가 결정되지 않은 학생의 명단도 함께 출력하세요
-- 기존의 INNER Join 방식으로 먼저 만들어 보고
-- OUTER Join 을 해보고 비교해보자
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s LEFT OUTER JOIN t_professor p
	ON s.profno = p.profno;

SELECT s.name 학생이름, p.name 교수이름
FROM t_student s RIGHT OUTER JOIN t_professor p
	ON s.profno = p.profno;

-- t_student, t_professor 테이블 join :  
-- 학생이름과 지도교수 이름을 출력, 
-- 단! 지도 학생이 결정되지 않은 교수 명단도 출력
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s FULL OUTER JOIN t_professor p
	ON s.profno = p.profno;

-- t_student, t_professor 테이블 join :  
-- 학생이름과 지도교수 이름을 출력, 
-- 단! 지도 학생이 결정되지 않은 교수 명단, 
-- 지도교수가 결정되지 않은 학생명단 모두 출력

-- ** ORACLE 에는 FULL OUTER 가 있는데 MySql 에는 없다..  
-- UNION 과 조합해야 함

SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s FULL OUTER JOIN t_professor p
	ON s.profno = p.PROFNO;


-- SELF Join
-- 만약 원하는 데이터가 하나의 테이블에 다 들어 있을때 사용...!!

-- t_dept2 테이블에서 부서명 과 그 부서의 상위부서명을 출력하세요

-- Oracle 방식
SELECT d1.dname 부서명, d2.dname 상위부서명
FROM t_dept2 d1, t_dept2 d2
WHERE d1.pdept = d2.dcode;

-- ANSI 방식
SELECT d1.dname 부서명, d2.dname 상위부서명
FROM t_dept2 d1 JOIN t_dept2 d2
ON d1.pdept = d2.dcode;

-- t_professor 테이블 : 교수번호, 교수이름, 입사일, 
-- 그리고 자신보다 입사일 빠른 사람의 인원수를 출력하세요, 
-- 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하세요
-- hint: left outer 사용 / 그룹함수 사용
-- left outer 사용해야하는 이유는 조인형 교수님 때문임..!!
SELECT * FROM t_professor;

SELECT
	a.profno "교수번호", a.name "교수명", a.HIREDATE "입사일",
	count(b.hiredate) "빠른분들"
FROM
	t_professor a LEFT OUTER JOIN t_professor b
	ON b.HIREDATE < a.HIREDATE 
GROUP BY 
	a.profno, a.name, a.HIREDATE
ORDER BY 4;

 

 

6. INNER Join(이너 조인), OUTER Join(아우터 조인)
** INNER Join : Join에 참여하는 모든 테이블에 데이터가 존재하는 경우에만 결과 값을 출력
** OUTER Join : 한쪽 테이블에 데이터가 있고 한쪽 테이블에 없는 경우 데이터가 있는 쪽 테이블의 내용을 전부 출력
** 필연적으로 OUTER Join 은 DB 성능에 나쁜 영향 줌.

 

7. equi join(등가조인), non-equi join(비등가조인) -> INNER JOIN

 

8. LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN -> OUTER JOIN