본문 바로가기
DBMS

[Oracle] 다양한 검색과 출력_Oracle 함수

by amoomar 2022. 1. 28.
반응형

 

이번 포스팅에서는 orcle함수 중에서도 사용도가 높은 함수들에 대해 다루어보았다. 숫자함수, 날짜함수, 변환함수, 추가함수등에 대한 내용을 활용하여 원하는 데이터를 원하는 방식으로 전환하여 출력할 수 있도록 하는 방법에 대해 알아보았다. 아래에 간략한 목차와 그에 다루는 내용을 정리하였다.

 

1. 다양한 검색
: 변환함수를 사용하여 대소문자에 관계없이 검색이 가능하도록 한다.
2. 랜덤출력
: SQL쿼리를 사용하여 다양한 랜덤의 출력이 가능하도록 한다.
3. 날짜형식
: SQL쿼리 등을 사용하여 날짜와 시간을 내가 원하는 형식에 맞춰 출력되도록 한다.
4. 마스킹
: 변환함수를 응용하여 개인정보 등을 원하는 부분을 가려 출력하거나, 잘라내어 출력되도록 한다.
5. 그룹화
: TableDB 내의 다양한 데이터들 그룹화하는 방법에 대해 정리한다.

 

 

 

 

1. 다양한 검색

: 대소문자의 구분 없이 검색이 가능하도록 하게 하는 방법과, 여러 조건문을 사용하여 검색에 대한 다양성을 증폭시키는 방법에 대해 정리하였다.

 

1) 이론

 

① Lower & Upper

SELECT  데이터범위(ex.*)  FROM  테이블명  WHERE LOWER(조건이 되는 구성);
SELECT 데이터범위(ex.*)   FROM  테이블명  WHERE UPPER(조건이 되는 구성);
  • 조건을 대문자/소문자로 바꿔주는 역할
  • 두 가지 모두 같은 수행
  • 테이블 내 특정 문자를 조회할 경우 조회할 문자가 소문자인지 대문자인지 확실하지 않을 때 검색하기 위해 필요

 

② AND & OR

--and--
SELECT  user_data  FROM  user_table  WHERE 조건 AND 조건,.....(AND 조건);
--or--
SELECT  user_data  FROM  user_table  WHERE 조건 OR 조건;
--and & or--
SELECT user_data FROM user_table WHERE 조건 AND (조건 OR 조건);
  • AND
    • 두가지 조건이 모두 충족되는 정보를 출력
    •  AND 연산자는 WHERE  절 뒤에 원하는 만큼 추가하여 정보를 가공 가능
  • OR
    • 조건 중 한가지라도 만족 하는 정보를 출력
  • AND & OR
    • AND 연산자는 OR 연산자보다 우선순위에 있다.
    • 정확한 값을 얻으려면 OR 연산자를 먼저 처리해야 하기 때문에 OR 연산자의 조건에 괄호로 묶는 것이 좋다.

 

*AND & OR의 괄호처리*

WHERE  절에서 AND, OR 같은 논리 연산자를 사용할 때는 각 연산자를 모두 괄호로 묶어주는 것이 좋다. 기본 평가 순서에 따라 제대로 해석되리라고 안심하지 말고, 직접 순서를 정해 의도대로 묶어야 한다. 괄호 사용에 따른 단점은 전혀 없으며, 오히려 조건을 보다 명확하게 이해하는데 도움이 된다.

 

③ IN

--in의 사용--
SELECT  user_data  FROM  user_table  WHERE 조건 IN (조건1, 조건2);
--or의 사용--
SELECT  user_data  FROM  user_table  WHERE 조건=조건1 OR 조건=조건2;
  • 값은 콤마( , )로 구분하여 괄호 내에 묶으며, 이 값 중에서 하나 이상과 일치하면 조건에 맞는 것으로 평가
  • 여러 값을 OR 관계로 묶어 나열하는 조건을 WHERE  절에 사용할 때 쓸 수 있는 키워드

 

*IN의 사용 이유*

  1. 목록에 넣을 값이 여러 개일 때, IN 연산자가 보다 쓰기도 쉽고, 이해하기도 쉽다.
  2. IN을 사용하면 평가 순서를 보다 쉽게 관리 할 수 있고, 연산자 수도 줄어든다.
  3. IN 연산자가 OR 연산자보다 실행 속도가 빠르다.
  4. IN의 가장 큰 장점은 IN 연산자에 다른 SELECT 문을 넣을 수 있다.
  5. 동적인 WHERE 절을 만들 때 더 크게 활용

 

④ NOT

--not의 사용--
SELECT  user_data  FROM  user_table  WHERE NOT 조건 IN (조건1, 조건2);
--not의 사용 없이 아래와 같이 쓸 수 있다--
SELECT  user_data  FROM  user_table  WHERE 조건 != 조건1 AND 조건 <> 조건2
  • !=, < > 와 쓰임이 같다
  • 다른 연산자와는 달리 필터링 할 열의 앞에 사용
  • 바로 뒤에 오는 조건을 부정하는  역할을 한다. 즉, 단독사용이 불가능하다.
  • 조건을 부정할 때 사용되는 WHERE 절의 키워드

 

 

 

2) 실습

: VO와 Utill Class의 내용은 생략되었다.

 

① SQL파일

create table fruit(
	name varchar(20) not null,
	price int default 0,
	cnt int default 0
);

insert into fruit values('ApplE', 1000, 3);
insert into fruit values('APPLE', 1100, 4);
insert into fruit values('apple', 1200, 5);
insert into fruit values('banana', 1200, 6);
insert into fruit values('coconut', 1300, 5);
insert into fruit values('grape', 900, 7);

select * from fruit;

drop table fruit;

 

② DAO

: 쿼리문을 중심으로 내용을 파악할 수 있도록 한다!

package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class FruitDAO {

	Connection conn;
	PreparedStatement pstmt;
	
    //쿼리문을 중심으로 내용 파악
	final String selectAll="select * from fruit";
	
	final String selectAll_lower = "select * from fruit where lower(name) like lower('%'||?||'%')";
	final String selectAll_upper = "select * from fruit where upper(name) like upper('%'||?||'%') order by price desc";
	
	final String selectAll_and = "select * from fruit where price>=? and name like '%'||?||'%'";
	
	final String selectAll_or = "select * from fruit where price=? or cnt=?";
	
	final String selectAll_and_or = "select * from fruit where name like '%'||?||'%' and (price=? or cnt=?)";
	
	final String selectAll_in = "select * from fruit where price in (?,?)";
	
	final String selectAll_not_in = "select * from fruit where not price in (?,?)";
	final String selectAll_not_instead = "select * from fruit where price != ? and price <> ?";
	
	final String selectAll_avg = "select * from fruit where price <= (select avg(price) from fruit)";
	final String selectAll_in_avg = "select * from fruit where cnt in ((select avg(cnt) from fruit), ?)";
	
	// selectAll
	public ArrayList<FruitVO> selectAll(FruitVO vo) {
		ArrayList<FruitVO> datas=new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_lower
	public ArrayList<FruitVO> selectAll_lower(FruitVO vo) {
		ArrayList<FruitVO> datas = new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_lower);
			pstmt.setString(1, vo.getName());
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_upper
	public ArrayList<FruitVO> selectAll_upper(FruitVO vo) {
		ArrayList<FruitVO> datas = new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_upper);
			pstmt.setString(1, vo.getName());
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_and
	public ArrayList<FruitVO> selectAll_and(FruitVO vo) {
		ArrayList<FruitVO> datas=new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_and);
			pstmt.setInt(1, vo.getPrice());
			pstmt.setString(2, vo.getName());
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_or
	public ArrayList<FruitVO> selectAll_or(FruitVO vo) {
		ArrayList<FruitVO> datas=new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_or);
			pstmt.setInt(1, vo.getPrice());
			pstmt.setInt(2, vo.getCnt());
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_and_or
	public ArrayList<FruitVO> selectAll_and_or(FruitVO vo) {
		ArrayList<FruitVO> datas=new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_and_or);
			pstmt.setString(1, vo.getName());
			pstmt.setInt(2, vo.getPrice());
			pstmt.setInt(3, vo.getCnt());
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_in
	public ArrayList<FruitVO> selectAll_in(ArrayList<FruitVO> datas) {
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_in);
			pstmt.setInt(1, datas.get(0).getPrice());
			pstmt.setInt(2, datas.get(1).getPrice());
			ResultSet rs=pstmt.executeQuery();
			datas=new ArrayList<FruitVO>();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_not_in
	public ArrayList<FruitVO> selectAll_not_in(ArrayList<FruitVO> datas) {
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_not_in);
			pstmt.setInt(1, datas.get(0).getPrice());
			pstmt.setInt(2, datas.get(1).getPrice());
			ResultSet rs=pstmt.executeQuery();
			datas=new ArrayList<FruitVO>();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_not_instead
	public ArrayList<FruitVO> selectAll_not_instead(ArrayList<FruitVO> datas) {
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_not_instead);
			pstmt.setInt(1, datas.get(0).getPrice());
			pstmt.setInt(2, datas.get(1).getPrice());
			ResultSet rs=pstmt.executeQuery();
			datas=new ArrayList<FruitVO>();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_avg
	public ArrayList<FruitVO> selectAll_avg(FruitVO vo) {
		ArrayList<FruitVO> datas=new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_avg);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}
	
	// selectAll_in_avg
	public ArrayList<FruitVO> selectAll_in_avg(FruitVO vo) {
		ArrayList<FruitVO> datas = new ArrayList<FruitVO>();
		conn = JDBCUtil.connect();
		
		try {
			pstmt=conn.prepareStatement(selectAll_in_avg);
			pstmt.setInt(1, vo.getCnt());
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				FruitVO data = new FruitVO();
				data.setName(rs.getString(1));
				data.setPrice(rs.getInt(2));
				data.setCnt(rs.getInt(3));
				
				datas.add(data);
			}
			rs.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return datas;
	}

}

 

 

 

 

 

2.  랜덤생성

: 숫자와 문자열의 랜덤생성 방법에 대해 정리하였다.

 

1) 랜덤생성_숫자

--음,양수의 정수 랜덤--
SELECT DBMS_RANDOM.RANDOM FROM DUAL;

--실수 랜덤--
SELECT DBMS_RANDOM.VALUE FROM DUAL; 0과 1사이
SELECT DBMS_RANDOM.VALUE(최소값, 최대값) FROM DUAL; 범위사이

--양수인 정수 랜덤 : 실수랜덤 응용--
SELECT CEIL(DBMS_RANDOM.VALUE(1,100)) FROM DUAL;범위사이의 실수를 올림하여 출력
SELECT FLOOR(DBMS_RANDOM.VALUE(1,100)) FROM DUAL;범위사이의 실수를 내림하여 출력

 

2) 랜덤생성_문자열

--문장의 기본 구성--
SELECT DBMS_RANDOM.STRING( ‘사용할 코드’ , 길이 ) FROM DUAL;

 

*사용되는 코드*

  • U : upper의 약자로, 대문자의 랜덤
  • L : lower의 약자로, 소문자의 랜덤
  • A : 대 · 소문자 혼합의 랜덤
  • X : 대문자와 숫자가 혼합된 랜덤
  • P : 사용 가능한 문자로 구성된 랜덤(ex. 대소문자, 숫자, 특수기호) 

 

3) 랜덤수의 삽입

insert into 테이블명 values (select dbms_random.랜덤의 조건 from dual) from 테이블명;

 

 

 

 

3. 날짜형식

: SQL문의 date타입 중 sysdate를 디폴트값으로 생성하게 되면 '현재날짜'가 삽입되게 된다. 이때 직접 설정하고싶다면 to_date를 사용할 수 있다.

TO_DATE('2021/01/26 17:05:03','YYYY/MM/DD HH24:MI:SS')

 

1) SimpleDateFormat

: SimpleDateFormat클래스를 사용하면 날짜데이터를 원하는 형식에 맞춰 저장 혹은 출력할 수 있다.

기본적인 예제들을 통해 개념을 잡기 좋은 블로그의 링크를 첨부하였다.

 

https://junghn.tistory.com/entry/JAVA-%EC%9E%90%EB%B0%94-%EB%82%A0%EC%A7%9C-%ED%8F%AC%EB%A7%B7-%EB%B3%80%EA%B2%BD-%EB%B0%A9%EB%B2%95SimpleDateFormat-yyyyMMdd

 

[JAVA] 자바 날짜 포맷 변경 방법(SimpleDateFormat) yyyyMMdd

Date클래스를 이용하여 현재 날짜를 출력하면 영문으로 된 날짜를 리턴한다. 특정 문자열 포맷으로 얻고 싶으면 java.text.SimpleDateFormat 클래스를 이용하면 된다. 다음은 오늘 날짜를 yyyy 년 MM월 dd일

junghn.tistory.com

 

 

 date타입의 객체생성을 통해 SimpleDateFormat으로 형식을 Format(재설정)하여 사용하는 방법과 String을 date타입으로 형변환 하여 원하는 형식을 Format하는 방식으로 사용이 가능함을 확인할 수 있다. 이때 SDF(SimpleDateFormat)은 문자열을 date타입으로 형변환하기 위한 SDF와 형식을 지정하기 위한 SDF로 총 두번 사용된다.

 

 

 

 

2) 예시

 

① 예제 1

: TableDB의 date타입 구성에 현재날짜와 시간을 삽입하고, 원하는 형식으로 변환하여 TableDB에 저장하는 예시이다.

package date;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Scanner;

public class Test {

   public static void main(String[] args) {

      Scanner sc = new Scanner(System.in);

      final String driverName="oracle.jdbc.driver.OracleDriver";//로드
      final String url="jdbc:oracle:thin:@localhost:1521:xe";
      final String user="ham";
      final String passwd="1234";//연결
      Connection conn=null;
      SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//년/월/일 시:분:초
      String strDate = dateFormat.format(Calendar.getInstance().getTime());//현재의날짜시분초가등록

      try {
         Class.forName(driverName);//로드
         conn=DriverManager.getConnection(url, user, passwd);//연결
         String sql = "insert into student(snum,sname,datetime1) values((select nvl(max(snum),0)+1 from student),?,TO_DATE(?,'yyyy/mm/dd hh24:mi:ss'))";//쿼리준비
         PreparedStatement pstmt=conn.prepareStatement(sql); //공간준비           
         pstmt.setString(1, "하이코딩"); // 작성자
         pstmt.setString(2, strDate); // 시간
         int cnt=pstmt.executeUpdate();//숫자리턴실행
         System.out.println(cnt+"건등록");            
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

 

② 예제 2

: 다양한 날짜형식들을 지정해 console창으로 확인하고, 예제 1의 데이터가 저장되어있는 TableDB를 기준으로 몇분 전에 삽입된 데이터인지 확인할 수 있다.

package date;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.Locale;

/*
  (생년월일, 가입일, 게시글)
  YYYY-MM-DD
  MM.DD.YYYY
  YYMMDD
  
  (댓글)
  HH:MM:SS
  HH:MM
*/
public class DateTest2 {

	public static void main(String[] args) {

		final String driverName = "oracle.jdbc.driver.OracleDriver"; // 1.로드,2.연결3.준비(문장준비,공간준비)4.실행

		final String url = "jdbc:oracle:thin:@localhost:1521:xe";

		final String user = "kim";

		final String passwd = "1234";

		Connection conn = null;

		Date date1 = null;

		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy년 MM월 dd일 HH시 mm분");

		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy - MM - dd / HH : mm");

		SimpleDateFormat sdf3 = new SimpleDateFormat("yyyy/MM/dd HH : mm");

		SimpleDateFormat sdf4 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

		ArrayList<Date> dList = new ArrayList<Date>();

		try {

			Class.forName(driverName);// 로드

			conn = DriverManager.getConnection(url, user, passwd);// 연결

			String sql = "select datetime1 from student";// 쿼리준비

			PreparedStatement pstmt = conn.prepareStatement(sql);// 공간준비

			ResultSet rs = pstmt.executeQuery();// 실행

			while (rs.next()) {

				date1 = rs.getTimestamp("datetime1");// 열의이름

				System.out.println("게시글 형식");

				System.out.println(sdf1.format(date1));// 년,월,일,시,분

				System.out.println(sdf2.format(date1));// 년-월-일/ 시:분

				System.out.println(sdf3.format(date1));// 년/월/일 시:분

				System.out.println("댓글 형식");

				System.out.println(sdf4.format(date1));// 년/월/일 시:분:초

				dList.add(sdf4.parse(sdf4.format(date1)));// 0

				System.out.println("구분선----------------");

			}
		} catch (Exception e) {

			e.printStackTrace();

		}

		System.out.println("=======댓글 형식 구체적으로 다시 만듬======");

		System.out.println("댓글 형식"); // 시간 계산

		SimpleDateFormat date = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss", Locale.KOREA);// 한국시간

		Date dCurrent = Calendar.getInstance().getTime();

		try {

			dList.add(date.parse("2022/01/19 01:05:07"));// 1

		} catch (ParseException e) {

			e.printStackTrace();

		}

		for (Date v : dList) {

			long diff = dCurrent.getTime() - v.getTime();

			long sec = diff / 1000;// 초

			long min = sec / 60;// 분

			long hour = min / 60;// 시간

			long day = hour / 24;// 1일24시간 (일)

			long week = day / 7;// 일(일주일) (몇주전)

			long month = week / 4;// 4주로 나누어서 달을 구했다.

			long year = month / 12;// 월과 12달을 나누어서 몫을구한다. 년도를 구했다.

			if (min == 0) { // 0분이면 초를가져와서출력

				System.out.println("약 " + sec + "초 전");

			} else if (hour == 0) {// 분0시간같으면 분을가져와서출력

				System.out.println("약 " + min + "분 전");

			} else if (day == 0) {// 0일이면 시간을 가져와서 출력

				System.out.println("약 " + hour + "시간 전");

			} else if (week == 0) {// 0주이면 일을 가져와서출력

				System.out.println("약 " + day + "일 전");

			} else if (month == 0) {// 0월이면 주를 가져와서출력

				System.out.println("약 " + week + "주 전");

			} else if (year == 0) {// 0년도이면 달을 가져와서 출력

				System.out.println("약 " + month + "달 전");
			}

			else {// 그외에는 년도를 출력

				System.out.println("약 " + year + "년 전");

			}

			System.out.println(date.format(v) + " 에 작성된 댓글 입니다.");// 배열방에 날짜를 가져와서 형식에맞게 출력

		}
	}
}

 

 

 

 

4. 마스킹

: 예시를 위해 아래와같은 샘플데이터를 생성한다.

create table userinfo(
	id varchar(40) not null,
	name varchar(20) not null,
	phone varchar(30) not null,
	email varchar(50) not null
);

insert into USERINFO (id, name, phone, email) values ('asdf@naver.com', '김길동', '010-1234-5678', 'asdf@naver.com');
insert into USERINFO (id, name, phone, email) values ('qwert@naver.com', '왕밤빵', '02-124-6551', 'qwert@nate.com');
insert into USERINFO (id, name, phone, email) values ('rhrhrht@naver.com', '박또치', '011-1234-6551', 'rhfef@hanmail.net');

 

 

1) INSTR

select instr(문자열,찾을문자,시작지점,몇번째) from 테이블명;
  • 찾을 문자가 존재하는 INDEX를 반환한다.
  • 시작지점과 몇번째인지를 작성하지 않을시 디폴트=1
  • 찾는 문자가 존재하지 않는다면 INDEX는 0으로 반환된다.

 

--2번째에 있는 -의 인덱스를 세줘--
select instr(phone, '-', 2) from userinfo;  
--뒤 기준 가까이에 있는 -의 인덱스를 세줘--
select instr(phone, '-',-1) from userinfo;
--1번째에 있는 -의 인덱스를 세줘--
select instr(phone, '-') from userinfo; 
--뒤 기준 두번째로 있는 -의 인덱스를 세줘--
select instr(phone, '-', -1,2) from userinfo;

 

2) SUBSTR

select substr (선택할 구성, start, lenght) from 테이블명;

 

  • 지정한 시작범위부터 길이까지에 해당하는 INDEX의 값들을 반환한다.
  • 길이를 지정하지 않으면 자동으로 문자열의 끝이 LENGHT로 설정된다.

 

--id에서 1부터 @를 제외한 것까지를 반환--
select substr (id, 1, instr(id, '@')-1) from userinfo;
--골뱅이만 반환--
select substr (id, instr(id, '@'), 1) from userinfo;
--phone에서 중간만 출력 : 1번째-의 길이부터, 두번째-까지의 길이 빼기 첫번째-까지의 길이만큼 반환--
select substr (phone, instr(phone, '-')+1, (instr(phone, '-',-1)-1)-instr(phone, '-')) from userinfo;

 

3) REPLACE

select replace(선택구성, 지정범위, 바꿀문자열) from 테이블명;
  • 선택한 문자열의 지정한 범위들을 지정한 문자열로 바꾸어 출력할 수 있다.
  • 단, 지정한 범위의 길이에 상관없이 지정한 문자열만을 반환하므로 자리수를 맞출 순 없다.

 

--phone중간번호를 *로 변경--
select replace(phone, substr(phone, instr(phone, '-')+1, (instr(phone, '-',-1)-1)-instr(phone, '-')),'*') "nameHide" from userinfo;

 

3) CONCAT

select concat(문자열1, 문자열2) from 테이블명;
  • 문자열을 연결해주는 기능을 한다.
  • 단, 두개의 문자열만을 인식할 수 있기에 출력할 문자열이 3개일 경우 CONCAT을 이중으로 사용한다.
  • 간편하게 "||"로 CONCAT과 동일한 기능을 사용할 수 있지만, 개발자의 작업환경에 따라 지원되지 않는 경우도 발생한다.

 

select concat(NAME, ID) from userinfo;
select concat(CONCAT(NAME,PHONE), ID) from userinfo;
select NAME || PHONE || ID from userinfo;

 

4) LPAD & RPAD

select lpad(문자열, BYTE크기, 채울문자) from 테이블명;
select Rpad(문자열, BYTE크기, 채울문자) from 테이블명;
  • PAD앞의 L과 R은 각각 왼쪽, 오른쪽을 지정하며, 해당되는 문자열을 지정한 길이에 맞춰 지정한 문자로 채워준다.
  • 만약 선택한 문자열의 길이가 지정한 길이보다 길다면, 문자열의 길이를 내가 지정한 길이만큼 잘라내어 출력된다.
  • 공백과 기호는 1BYTE, 글자는 2,3BYTE정도로 계산된다.

 

--PHONE이 20BYTE가 될때까지 왼쪽에 ㅇ을 채움--
select lpad(phone, 20, 'ㅇ') from userinfo;
--5BYTE가 될때까지 ㅇ을 왼쪽에서 채우고, 나머지는 * 삽입--
select lpad('*', 5, 'ㅇ') from userinfo;

 

* 응용 *

--이메일형식의 ID를 앞의 2글자만 보여주고, 나머지는 길이만큼 *출력. @이후로는 정상출력--
select substr(id, 1, 2) || lpad('*',instr(id,'@')-3,'*') || substr(id,instr(id,'@')) from userinfo;
--이름이 3자 이상일 경우에도 가운데 글자수만큼 *로 치환--
select (substr(tname,1,1)) || (lpad('*' ,length(tname)-2,'*')) || (substr(tname,-1,1)) as tnamehide from test;

 

 

 

5. 그룹화

: 한 TableDB에 수많은 DB가 존재할 경우 데이터들을 같은 주제(칼럼:열)끼리 그룹을 지정할 수 있으며, 이는 주제별 검색이 필요할때 유용하게 사용할 수 있다. (각 동아리의 평균 출력, 동아리 총 인원 출력, 각 동아리중 평균 90이 넘는 동아리만 출력 등의 검색)

 

*일반적으로 조건을 달때는 where을 쓰지만, 그룹화에서는 having을 사용한다.*

 

1) 칼럼별 출력_그룹화

: 테이블에서 확인하고싶은 열에 해당하는 정보만을 출력하기 위해 사용된다. 때에따라 as나 ""를 통해 별칭을 정할 수 있다.

select 표시할 칼럼 from 테이블 group by 기준 칼럼;
select comics as 만화책 from heroes group by comics;

 

2) 칼럼의 조건 출력_그룹화 제한

: 제시한 조건에 해당하는 열만을 출력할 수 있다.

select 표시할 칼럼 from 테이블 group by 기준 칼럼;
--파워의 평균이 85이하인 만화책만 내림차순 출력--
group by comics having avg(power)<=85 order by power desc;

 

3) 최대값, 최소값, 평균, 반올림

: 각 함수들의 괄호에 칼럼을 넣게 되면 그 칼럼에 해당되는 결과를 확인할 수 있다.

max(칼럼)
min(칼럼)
avg(칼럼)
round(avg(칼럼), 소수점자리수)
테이블DB를 코믹칼럼으로 그룹화 하고
그 그룹의 최대값, 최소값, 평균(실수이므로 올림처리)들을 내림차순 정렬하여 출력
select comics,max(power) as maximum,min(power) as
minimum,round(avg(power),1) as average from heroes group by comics 
order by average desc;

 

* 다른 TableDB의 그룹화 *

  • join : 서로 다른 테이블을 연결하여 하나의 테이블처럼 사용
  • inner join : 두 테이블의 공통된 데이터만 추출(교집합)
  • outer join : 기준 테이블은 데이터 전체, 다른 테이블은 공통된 행만 추출
  • full outer join : 전체 출력

 

 

반응형

'DBMS' 카테고리의 다른 글

[MySQL] 설치 & 이관작업  (0) 2022.02.03
[Oracle] 능력단위평가 풀이  (0) 2022.01.28
[Oracle] 프로젝트의 설계 & 두개의 TableDB  (0) 2022.01.26
[Oracle] 웹크롤링_샘플데이터처리  (0) 2022.01.25
[Oracle] 웹크롤링_기초  (0) 2022.01.24

관련글

티스토리툴바