구리

TIL_210408_레코드 검색, 재명명, LIKE 연산자 본문

DataBase

TIL_210408_레코드 검색, 재명명, LIKE 연산자

guriguriguri 2021. 4. 11. 21:41

목차

레코드 기본 검색

- 레코드 검색 기본 예시

- select절에 간단한 연산 예시

- 키워드 distinct

- from절에 두 개 이상의 테이블이 포함된 질의

- order by

재명명 연산

필드의 재명명

 

LIKE 연산자

- 문자열 패턴 종류

- LIKE 연산자를 자바에서 쿼리문으로 실행시 주의 사항

 

 

 

 

레코드 검색 

 

레코드 검색 기본 구조

select 필드리스트      => 함수(count), 간단한 연산까지는 가능함
from 테이블리스트    => 내가 사용할 테이블명들, 혹은 테이블명에 대한 별명(테이블 재명명)도 가능
where 조건              => 조건이 따로 없을 경우 생략 가능

 

레코드 검색 예시

select  name, dept_name

from   department, student

where  department.dept_id = student.dept_id; 

 

 

select절에 간단한 연산 예시

select name, 2021 - year_emp     // year_emp : 입사한 연도 , 2021 - year_emp : 지금까지 재직한 연수 

from                    professor;

 

 

키워드 dintinct

중복 레코드 제거 후 검색할 때 사용하는 키워드

 

예) student 테이블에서 모든 학생들의 주소를 중복 제거 후 추출 

    select    distinct     address

    from                    student;

 

 

from절에 두 개 이상의 테이블이 포함된 질의 

- from절에 두 개 이상의 테이블이 있는데 select절에 동일 필드명이 있을 경우 안전하게 테이블명을 명시

 

예) 학생의 이름, 사번, 학과명 출력하기

     select student.name, student.stu_id, department.dept_name 

     from   student, department

     where   student.dept_id = department.dept_id;

 

 

 

예) 컴공과 3학년 학생들의 학번 검색 예시

    select   student.stu_id

    from   student, department

    where   student.dept_id = department.dept_id and

    student.year = 3 and

    department.dept_name='컴퓨터공학과'

 

 

 

order by 

레코드의 순서 지정 키워드로 검색 결과를 정렬하여 출력하는 기능

select문 맨 마지막에 order by절을 추가하여 검색

ordey by ASC (기본값으로 오름차순으로 정렬), DESC (내림차순으로 정렬)

 

예) 3학년, 4학년의 이름과 학번, 검색 (단 이름으로 오름차순 정렬 후 동명이인일 경우 학번으로 오름차순 정렬)

    select   name, stu_id

    from   student

    where   year = 3 or year = 4

    order by   name, stu_id

 

 

 

예) 3학년, 4학년의 이름과 학번, 검색 (단 이름으로 내림차순 정렬 후 동명이인일 경우 학번으로 오름차순 정렬)

     select stu_id, name
     from student
     where year=3 OR year=4
     order by name DESC, stu_id;

 

 

 

 

재명명 연산

테이블이나 필드명에 대한 재명명으로 실제 테이블 이름이 수정되거나 필드 이름이 바뀌는 것은 아님

질의를 처리하는 과정 동안만 일시적으로 사용 

 

예) student 테이블과 department 테이블을 조인하여 학생들의 이름과 소속 학과 이름을 검색

    select   s.name, d.dept_name

    from   student s, department d

    where  s.dept_id = d.dept_id

 

 

 

- 동일 테이블이 두 번 사용되는 예

 

예) student 테이블에서 '김광식' 학생과 주소가 같은 학생들의 이름, 주소를 검색 1

   select   s2.name

   from   student s1, student s2

   where   s1.address = s2.address and s1.name = '김광식'

 

 

 

예) student 테이블에서 '김광식' 학생과 주소가 같은 학생들의 이름, 주소를 검색 2

        select s2.name, s1.address

        from student s1, student s2

        where s1.address=s2.address AND s1.name='김광식';

 

 

이 쿼리문의 실행방식을 보면 다음과 같다

s1 테이블의 한 행과 s2의 첫번째 행부터 마지막 행까지 비교해가며 조건에 맞는 s1.name 과 s2.address를 저장한다s1 테이블의 첫번째 행이 끝나면 두번째 행과 s2의 모든 행과 비교하고 이런 방식으로 s1 테이블의 마지막 행까지 비교하고 결과를 출력한다

 

 

해당 쿼리문 진행 방식

 

필드의 재명명 

질의 실행 결과를 출력할 때 원래 필드의 이름 대신 재명명된 이름으로 출력할 때 사용

 

예) 교수들의 이름, 직위, 재직연수를 출력

    (새로 만든 필드명은 자바에서 ResultMetaData()를 이용해 필드명 뽑아낼 수 있다)

    select   name 이름, position 직위, 2021-year_emp 재직연수

    from   professor

 

 

 

 

 

LIKE 연산자 

문자열에 대해서는 일부분만 일치하는 경우를 찾아야할 때 사용

'=' 연산자 대신 'like'연산자를 이용

형식 where 필드명 like 문자열 패턴 

 

 

문자열 패턴 종류 

 

_  :  임의의 한 개 문자를 의미

% :  임의의 여러 개 문자를 의미

예)   '%서울%'    :  '서울'이란 단어가 포함된 문자열

       '%서울'        :  '서울'이란 단어로 끝나는 문자열

       '서울%'        :  '서울'이란 단어로 시작하는 문자열

        ‘_ _ _’           :  정확히 세 개의 문자로 구성된 문자열

       ‘_ _ _%’        :  최소한 세 개의 문자로 구성된 문자열

 

예) student 테이블에서 김씨 성을 가진 학생들의 모든 정보를  찾는 질의

     select  *

     from   student

     where   name like '김%'

 

 

 

예) student 테이블에서 여학생들만 검색하여 정보를 찾는 질의

    select   *

    from   student

    where   resident_id like '%-2%'

 

 

 

LIKE 연산자를 자바에서 쿼리문으로 실행시 주의 사항

 

 

예) 김씨로 시작하는 이름을 가진 학생의 이름과 주소를 요청하는 쿼리문을 자바에서 실행시    

     Statement 사용시 : query = "SELECT name, address FROM student WHERE name LIKE '김%'";

     PreparedStatement 사용시 : query = "SELECT * FROM student WHERE name LIKE ? || '%'";

                                          ( 물음표와 '%' 사이에 || 꼭 삽입하기 )

 

 

예) 이름에 '광'이 포함된 학생의 이름과 주소를 요청하는 쿼리문을 자바에서 실행시

     PreparedStatement 사용시 : query = "SELECT name, address FROM student WHERE name LIKE '%' || ? || '%'";

 

 

지금까지 배운 쿼리문을 토대로 이클립스에서 쿼리문 실행하는 메서드 생성 후 결과 출력

 

package com.bjy;

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

/** 오라클 접속관련 클래스 
 * connectOracle() : 접속전용
 * select(String query) : select 구문 실행
 * **/
public class JdbcClass {
	private Connection conn = null; // 접속 결과 저장
	private Statement stmt = null; // Connection 객체로부터 Statement 객체 얻어냄
	                               // select 쿼리문 실행 도와줘
	
	private PreparedStatement pstmt = null; // Connection 객체로부터 PreapredStatement 객체 얻어냄
	                                        // 쿼리 내부에 변수가 있을 경우 사용
	
	private ResultSet rs = null;  // select 쿼리문의 결과값을 ResultSet 객체로 반환시 사용 
	private int n=0;              // select, update, delete의 결과 값을 저장받는 변수
	
	private final String ORACLE_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
	private final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private final String ORACLE_USER = "bjy";
	private final String ORACLE_PWD = "qorwjddus96";
	
	public JdbcClass() {
		
	}
	
	/** 오라클 db 접속 전용 메서드 **/
	public void oralceConnect() {
		
		try {
			Class.forName(this.ORACLE_DRIVER);
			this.conn = DriverManager.getConnection(this.ORACLE_URL, this.ORACLE_USER, this.ORACLE_PWD);
			System.out.println(this.ORACLE_URL + "," + this.ORACLE_USER + "로 접속 성공!");
			
		} catch (ClassNotFoundException e) {
			System.err.println("드라이브 로딩 실패 : " + e.getMessage());
		} catch(SQLException e) {
			System.err.println("오라클 접속 실패 : " + e.getMessage());
		}
	}
	
	/** select문을 실행하고 결과 확인 메서드**/
	public void select(String query) {
		try {
			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			
			while(this.rs.next()) {
				System.out.println("이름 : " + rs.getString(1) + ", 학과명 : " + rs.getString(2));
				
			}
			
		} catch (SQLException e) {
			System.out.println("select 쿼리문 실행 오류  : " + e.getMessage());
		}
		
	}
	
	/** 중복값을 제거하는 메서드 **/
	public void selectDistinct(String query) {
		try {
			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("학생 주소 : " + rs.getString(1));
			}
		} catch (SQLException e) {
			System.out.println("Distinct 쿼리 실행 오류 : " + e.getMessage());
		}
	}
	
	/** 교수들의 재직연수 (2021년 기준) 구하는 메서드  **/
	public void selectProfessor(String query) {
		try {
			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("교수이름 : " + rs.getString(1) + "," + "재직연수 : "+ rs.getInt(2));
			}
		} catch (SQLException e) {
			System.out.println("Professor 쿼리 실행 오류 : " + e.getMessage());
		}
	}
	
	/** 학생들의 이름, 학번, 그리고 소속 학과의 이름을 검색하여 출력하는 메서드**/
	public void select3(String query) {
		try {
			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("학생이름 : " + rs.getString(1) + ", 학번 : "+ rs.getString(2) + ", 소속 학과 : " +rs.getString(3));
			}
		} catch (SQLException e) {
			System.out.println("select3 쿼리 실행 오류 : " + e.getMessage());
		}
	}
	
	/** 3, 4학년 학생들의 이름과 학번을 검색 : 동일인의 경우 학번 오름차순 정렬하여 출력하는 메서드 **/
	public void selectNameDeptId(String query) {
		try {
			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("고학년 학생이름 : " + rs.getString(1) + ", 학번 : "+ rs.getString(2));
			}
		} catch (SQLException e) {
			System.out.println("selectNameDeptId 쿼리 실행 오류 : " + e.getMessage());
		}
	}
	
	/** '김광식' 학생과 주소가 같은 학생들의 이름과 주소를 검색하여 출력하는 메서드 **/
	public void selectNameAddress(String query) {
		String name = "김광식";
		try {
			this.pstmt = this.conn.prepareStatement(query);
			this.pstmt.setString(1, name);
			this.rs = this.pstmt.executeQuery();
			
			while(this.rs.next()) {
				System.out.println("이름 : " + rs.getString(1) + ", 주소 : " + rs.getString(2));
			}
		}catch(SQLException e) {
			System.out.println("selectNameAddress 쿼리 실행 오류 : " + e.getMessage());
		}
	}
	
	/** 필드명을 임시로 한글로 바꿔고 출력하는 메서드 **/
	public void ChangeKr(String query) {
		String n1 = "이름";
		String n2 = "직위";
		String n3 = "재직연수";
		
		try {

			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			ResultSetMetaData meta = rs.getMetaData();
			int cols = meta.getColumnCount();
			
			for(int i=1;i<=cols;i++) {
				System.out.print(meta.getColumnName(i) + "\t ");
			}
			
			System.out.println();
			
			while(rs.next()) {
				System.out.print(rs.getString(1) + "\t" + rs.getString(2) + " \t" + rs.getString(3));
				System.out.println();
			}
			
		}catch(SQLException e) {
			System.out.println("ChangeKr 쿼리 실행 오류  : " + e.getMessage());
		}
		
	}
	
	/** 성이 김씨인 학생의 이름과 주소 : Statement**/
	public void likeSelectStatement(String query) {
		try {
			this.stmt = this.conn.createStatement();
			this.rs = this.stmt.executeQuery(query);
			
			while(rs.next()) {
				System.out.println("이름 : " + rs.getString(1) + ", 주소 : " + rs.getString(2));
			}
		} catch (SQLException e) {
			System.out.println("likeSelectStatement 쿼리 실행 오류 ; " + e.getMessage());
		}
	}
	
	/** 원하는 변수를 대입하여 쿼리문의 결과를 출력하는 메서드 **/
	public void likeSelectPreparedStatement(String query, String value) {
		try {
			this.pstmt = this.conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			
			// rs.first() / rs.last() 등를 사용하려면 필수
			// ResultSet.TYPE_SCROLL_INSENSITIVE : ResultSet을 만들면 한번 커서가 지나간 다음에 다시 되돌릴 수 있는 옵션.
			// ResultSet.CONCUR_UPDATABLE : 현 커서의 위치값을 업데이트 하는 옵션
			
			this.pstmt.setString(1, value);
			this.rs = this.pstmt.executeQuery();

			if(this.rs.next()) {
				this.rs.beforeFirst();
				
				System.out.println("이름 \t 주소");
				while (rs.next()) {
					System.out.printf(rs.getString(1) + "\t");
					System.out.println(rs.getString(2) + "\t" +rs.getRow());
				}
			}else {
				System.out.println("조회된 결과 없습니다..");
			}

		} catch (SQLException e) {
			System.err.println("likeSelectPreparedStatement ERR : " + e.getMessage());
		}
	}
	
	
	
}

메서드를 MainClass에서 실행 

package com.bjy;

public class MainClass {

	public static void main(String[] args) {
		JdbcClass jdbc = new JdbcClass();
		jdbc.oralceConnect();
		String query = null;

		/** 학생의 이름과 학과명을 출력 **/
		System.out.println("모든 학생의 이름, 확과명 검색 ");
		query = "SELECT name, dept_name ";    // 띄어 쓰기 꼭 주의!!! name뒤에 쓰어띄기 안하면 오류
		query += "FROM department, student ";
		query += "WHERE department.dept_id=student.dept_id";
		
		jdbc.select(query);
		
		System.out.println();
		
		/** 중복값 제거 : 학생들 주소를 출력 **/
		System.out.println("모든 학생들의 주소를 검색 (중복제거)");
		query = "SELECT distinct address FROM student ";
		jdbc.selectDistinct(query);
		
		System.out.println();
		
		/** 교수들의 재직연수 (2021년 기준) **/
		System.out.println("모든 교수들의 재직연수 검색");
		query = "SELECT name, 2021-year_emp FROM professor";
		jdbc.selectProfessor(query);
		System.out.println();
		
		/** 학생들의 이름, 학번, 그리고 소속 학과의 이름을 검색 **/
		System.out.println("학생들의 이름, 학번, 소속 학과 이름 검색");
		query = "select name, stu_id, dept_name ";
		query += "from department,student ";
		query += "where department.dept_id=student.dept_id";
		jdbc.select3(query);
		System.out.println();
		
		/** 3, 4학년 학생들의 이름과 학번을 검색 : 동일인의 경우 학번 오름차순 정렬 **/
		System.out.println("3,4학년 학생들의 이름, 학번 검색");
		query = "select name, dept_id ";
		query += "from student ";
		query += "where year=3 OR year=4 order by dept_id";
		jdbc.selectNameDeptId(query);
		System.out.println();
		
		/** '김광식' 학생과 주소가 같은 학생들의 이름과 주소를 검색 **/
		System.out.println("김광식 학생과 주소가 같은 학생들의 이름, 주소 검색");
		query = "select s2.name, s2.address ";
		query += "FROM student s1, student s2 ";
		query += "WHERE s1.address=s2.address AND s1.name=?";
		jdbc.selectNameAddress(query);
		System.out.println();
		
		/** 필드명을 임시로 한글로 바꿔고 출력**/
		System.out.println(" 필드명 한글로 교체 ");
		query = "SELECT name 이름, position 직위, 2021-year_emp 재직연수 FROM professor";
		
		jdbc.ChangeKr(query);
		System.out.println();
		
		/** 성이 김씨인 학생의 이름과 주소 : Statement**/
		System.out.println("성이 김씨인 학생들의 이름과 주소 ");
		query = "SELECT name, address ";
		query += "FROM student ";
		query += "WHERE name LIKE '김%'";
		jdbc.likeSelectStatement(query);
		System.out.println();
		
		/** 이름에 '광' 이 포함된 학생의 이름과 주소 : PreparedStatement **/
		System.out.println("이름에 '광' 이 포함된 학생의 이름과 주소 검색");
		String value = "광";
		query = "select name, address ";
		query += "from student ";
		query += "where name LIKE '%' || ? || '%'";
		jdbc.likeSelectPreparedStatement(query, value);
		System.out.println();
		
		/** 이름에 '김' 으로 시작하는 학생의 이름과 주소 : PreparedStatement **/
		System.out.println("이름에 '김' 으로 시작하는 학생의 이름과 주소 검색");
		value = "김";
		query = "select name, address ";
		query += "from student ";
		query += "where name LIKE ? || '%'";
		jdbc.likeSelectPreparedStatement(query, value);
		System.out.println();
		
		/** 이름이 '정' 으로 끝나는 학생의 이름과 주소 : PreparedStatement **/
		System.out.println("이름이 '정' 으로 끝나는 학생의 이름과 주소 검색 ");
		value = "정";
		query = "select name, address ";
		query += "from student ";
		query += "where name LIKE '%' || ?";
		jdbc.likeSelectPreparedStatement(query, value);
		System.out.println();

	}

}

 

 

JdbcClass에서 마지막 메서드인 likeSelectPreparedStatement() 내부를 보면 PreparedStatement 생성시 매개변수로 

ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE가 오는데 rs.first(), rs.last() 등의 메서드를 사용하기 위해선 필수적이다.

 

여기서 ResultSet 속성의 메서드를 간단히 알아보겠다.

메서드 설명
boolean first() 커서를 첫번째로 이동시킬 수 있는지에 대해 true, false 반환하며
true면 커서를 첫번째 레코드로 이동시킨다
boolean last() 커서를 마지막 레코드 행으로 이동시킬 수 있는지에 대해 true, false 반환하며 true면 커서를 마지막 레코드 행으로 이동시킨다 
boolean next() 커서를 다음으로 이동할 수 있는지에 대해 true, false 반환하며
true면 커서를 다음으로 이동시킨다
boolean previous() 커서를 이전 행으로 이동시킬 수 있는지에 대해 true, false 반환하며
true면 커서를 이전 행으로 이동시킨다  
int getRow() 현재 커서가 가리키고 있는 행 번호를 반환한다
boolean isFirst() 커서가 처음인지 true, false 반환한다
boolean isLast() 커서가 마지막인지에 대해 true,false 반환한다
void beforeFirst() 커서를 제일 위로(빈 공간임)
void afterLast() 커서를 제일 뒤로(빈 공간임) 

 

'DataBase' 카테고리의 다른 글

TIL_210413_SQL활용(숫자 함수, 날짜 함수, 문자처리 함수, 형변환 함수...)  (0) 2021.04.13
TIL_210412_SQL활용(집합연산, 집계함수, 중첩질의, VIEW, SEQUENCE)  (0) 2021.04.12
TIL_210408_데이터&데이터베이스, 관계형 데이터베이스, 릴레이션,키  (0) 2021.04.08
TIL_210406_Statement, PreparedStatement  (0) 2021.04.08
TIL_210405_SQL로 Oracle Database 다루기, 자바&Oracle 연결  (0) 2021.04.05
'DataBase' Related Articles more