이안의 평일코딩

Oracle 5일차 - SQL 복수행함수 본문

Back-end/Oracle

Oracle 5일차 - SQL 복수행함수

이안92 2020. 8. 10. 16:54
반응형

2020.08.10(월)

1. 정규식 함수 => regexp_like

SQL> CREATE TABLE regTable(
     tag VARCHAR2(1000)
     );
Table created.

SQL> INSERT INTO regTable VALUES('ABC123');
SQL> INSERT INTO regTable VALUES('ABC 123');
SQL> INSERT INTO regTable VALUES('ABC  123');
SQL> INSERT INTO regTable VALUES('abc123');
SQL> INSERT INTO regTable VALUES('abc 123');
SQL> INSERT INTO regTable VALUES('abc  123');
SQL> INSERT INTO regTable VALUES('a1b2c3');
SQL> INSERT INTO regTable VALUES('aabbcc123');
SQL> INSERT INTO regTable VALUES('가나다123');
SQL> INSERT INTO regTable VALUES('?/!@#$*&');
SQL> INSERT INTO regTable VALUES('\~*()..');
SQL> INSERT INTO regTable VALUES('123123');
SQL> INSERT INTO regTable VALUES('123abc');
SQL> INSERT INTO regTable VALUES('abc');

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM regTable;

TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC123
ABC 123
ABC  123
abc123
abc 123
abc  123
a1b2c3
aabbcc123
가나다123
?/!@#$*&
\~*()..
123123
123abc
abc

14 rows selected.

 

^  => ^A  => A% (A로 시작하는 모든 데이터)

$  => A$  => %A

.   => 임의의 문자 (모든 문자)

*  => 여러문자, 0문자 [A-Z]* => 대문자 알파벳이 여러개 존재, 없을 수 있다

+  => 1글자 이상

[]  => 해당문자 [AB] [A-Z] [a-z] [0-9] [가-힣]

[^] => [^A] => A를 제외

 

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag, '[a-z]');

TAG
-----------
abc123
abc 123
abc  123
a1b2c3
aabbcc123
123abc
abc

7 rows selected.
SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'[가-힣]');

TAG
---------
가나다123
SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'[A-Za-z]');

TAG
---------------------------
ABC123
ABC 123
ABC  123
abc123
abc 123
abc  123
a1b2c3
aabbcc123
123abc
abc

10 rows selected.

 

공백

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'[a-z] ');

TAG
-------------------------------------------------------------
abc 123
abc  123

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'[a-z] [0-9]');

TAG
--------------------------------------------------------------
abc 123

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'[a-z]  [0-9]');

TAG
---------------------------------------------------------------
abc  123

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'[[:space:]]');
                                            //공백 들어간 데이터 찾아오기

TAG
----------------------------------------------------------------
ABC 123
ABC  123
abc 123
abc  123
SQL> SELECT * FROM emp WHERE REGEXP_LIKE(ename,'^M(A|O)');

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30

SQL> SELECT * FROM emp WHERE REGEXP_LIKE(ename,'^[^a-z]');

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 80/12/17        800                    20
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30
      7782 CLARK                MANAGER                  7839 81/06/09       2450                    10
      7788 SCOTT                ANALYST                  7566 82/12/09       3000                    20
      7839 KING                 PRESIDENT                     81/11/17       5000                    10
      7844 TURNER               SALESMAN                 7698 81/09/08       1500                    30
      7876 ADAMS                CLERK                    7788 83/01/12       1100                    20
      7900 JAMES                CLERK                    7698 81/12/03        950                    30
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10

14 rows selected.

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'^[^a-z]');

TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC123
ABC 123
ABC  123
가나다123
?/!@#$*&
\~*()..
123123
123abc

8 rows selected.

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'^[^A-Z]');

TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
abc123
abc 123
abc  123
a1b2c3
aabbcc123
가나다123
?/!@#$*&
\~*()..
123123
123abc
abc

11 rows selected.

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'^[^0-9]');

TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC123
ABC 123
ABC  123
abc123
abc 123
abc  123
a1b2c3
aabbcc123
가나다123
?/!@#$*&
\~*()..
abc

12 rows selected.

SQL> SELECT * FROM regTable WHERE REGEXP_LIKE(tag,'^[^가-힣]');

TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC123
ABC 123
ABC  123
abc123
abc 123
abc  123
a1b2c3
aabbcc123
?/!@#$*&
\~*()..
123123
123abc
abc

13 rows selected.

 

 

2. 집합함수

 =통계, 분석

 

*** COUNT : 갯수

    COUNT(*) ====> NULL값을 포함

    COUNT(컬럼명) ==> NULL값을 제외

    => 로그인, ID중복 체크

*** MAX : 최대값

     => 자동 증가 번호

MIN

AVG

SUM

*** ROLLUP

*** CUBE

*** RANK, DENSE_RANK

SQL> SELECT COUNT(*), COUNT(mgr), COUNT(comm) FROM emp;

  COUNT(*) COUNT(MGR) COUNT(COMM)
---------- ---------- -----------
        14         13           3
SQL> SELECT MAX(sal) "최대급여", MIN(sal) "최소급여", COUNT(*) "사원수", SUM(sal) "급여 총합", 
            AVG(sal) "급여 평균" FROM emp;

  최대급여   최소급여     사원수  급여 총합  급여 평균
---------- ---------- ---------- ---------- ----------
      5000        800         14      29025 2073.21429

 

-- 10, 20, 30 ==> 별로 인원수, 급여합, 급여 평균

SQL> SELECT COUNT(*) "인원수", SUM(sal) "급여합", AVG(sal) "급여 평균" FROM emp WHERE deptno=10;

    인원수     급여합  급여 평균
---------- ---------- ----------
         3       8750 2916.66667
SQL> SELECT deptno, COUNT(*), SUM(sal), AVG(sal) FROM emp;
SELECT deptno, COUNT(*), SUM(sal), AVG(sal) FROM emp
       *
ERROR at line 1:
ORA-00937: not a single-group group function

그룹이 아닌 싱글이라서 오류남 (dpetno) => GROUP BY로 묶으면 됨

SQL> SELECT deptno, COUNT(*), SUM(sal), AVG(sal) FROM emp GROUP BY deptno;

    DEPTNO   COUNT(*)   SUM(SAL)   AVG(SAL)
---------- ---------- ---------- ----------
        30          6       9400 1566.66667
        20          5      10875       2175
        10          3       8750 2916.66667

 

-- 입사년도별로 인원수, 급여합, 급여 평균

SQL> SELECT TO_CHAR(hiredate, 'YYYY'), COUNT(*), SUM(sal), AVG(sal) FROM emp
     GROUP BY TO_CHAR(hiredate, 'YYYY');

TO_CHAR(   COUNT(*)   SUM(SAL)   AVG(SAL)
-------- ---------- ---------- ----------
1980              1        800        800
1983              1       1100       1100
1982              2       4300       2150
1981             10      22825     2282.5

 

-- 부서별로 급여합, 평균을 구한다 => 전체 평균보다 많이 받는 부서만 출력

그룹조건은 WHERE(단일행)이 아닌 HAVING

SQL> SELECT deptno, COUNT(*), SUM(sal), AVG(sal) FROM emp
     GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp);

    DEPTNO   COUNT(*)   SUM(SAL)   AVG(SAL)
---------- ---------- ---------- ----------
        20          5      10875       2175
        10          3       8750 2916.66667

 

3. 연습문제

 

*** 주의 (서버 연결시)

라이브러리 jdk1.8.0_251로 수정해야 오류가 사라짐.

 

EmpVO.java

package com.sist.dao;

import java.util.Date;

/*
 * 이름          유형           
--------  ------------ 
EMPNO      NUMBER(4)    
ENAME      VARCHAR2(10) 
JOB        VARCHAR2(9)  
MGR        NUMBER(4)    
HIREDATE   DATE         
SAL        NUMBER(7,2)  
COMM       NUMBER(7,2)  
DEPTNO     NUMBER(2)  
 */
public class EmpVO {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private int sal;
	private int comm;
	private int deptno;
	
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public int getComm() {
		return comm;
	}
	public void setComm(int comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	
}

 

EmpDAO.java

package com.sist.dao;
import java.util.*;
import java.sql.*;
public class EmpDAO {
	private Connection conn;
	private PreparedStatement ps;
	private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
	private static EmpDAO dao;
	
	public EmpDAO() {
		try {
			Class.forName("oracle.jdbc.dirver.OracleDriver");
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}
	}
	
	public void getConnection() {
		try {
			conn = DriverManager.getConnection(URL, "hr", "happy");
		}catch(Exception ex) {}
	}
	
	public void disConnection() {
		try {
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();
		}catch(Exception ex) {}
	}
	
	public ArrayList<EmpVO> empAllData(){
		ArrayList<EmpVO> list = new ArrayList<EmpVO>(); 
		try {
			getConnection();
			String sql="SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno "
					+"FROM emp";
			ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			while(rs.next()) {
				EmpVO vo = new EmpVO();
				vo.setEmpno(rs.getInt(1));
				vo.setEname(rs.getString(2));
				vo.setJob(rs.getString(3));
				vo.setMgr(rs.getInt(4));
				vo.setHiredate(rs.getDate(5));
				vo.setSal(rs.getInt(6));
				vo.setComm(rs.getInt(7));
				vo.setDeptno(rs.getInt(8));
				list.add(vo);
			}
			rs.close();
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}finally {
			disConnection();
		}
		return list;
	}
}

 

EmpServlet.java(servlet)

package com.sist.dao;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;

@WebServlet("/EmpServlet")
public class EmpServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=EUC-KR");
		PrintWriter out = response.getWriter();
		
		EmpDAO dao = new EmpDAO();
		ArrayList<EmpVO> list = dao.empAllData();
		
		out.println("<html>");
		out.println("<body>");
		out.println("<center>");
		out.println("<h1>사원 정보</h1>");
		out.println("<table width=800 border=1 bordercolor=black>");
		out.println("<tr>");
		out.println("<th>사번</th>");
		out.println("<th>이름</th>");
		out.println("<th>직위</th>");
		out.println("<th>사수</th>");
		out.println("<th>입사일</th>");
		out.println("<th>급여</th>");
		out.println("<th>성과금</th>");
		out.println("<th>부서</th>");
		out.println("</tr>");
		for(EmpVO vo : list) {
			out.println("<tr>");
			out.println("<td>"+vo.getEmpno()+"</td>");
			out.println("<td>"+vo.getEname()+"</td>");
			out.println("<td>"+vo.getJob()+"</td>");
			out.println("<td>"+vo.getMgr()+"</td>");
			out.println("<td>"+vo.getHiredate()+"</td>");
			out.println("<td>"+vo.getSal()+"</td>");
			out.println("<td>"+vo.getComm()+"</td>");
			out.println("<td>"+vo.getDeptno()+"</td>");
			out.println("</tr>");
		}
		out.println("</table>");
		out.println("</center>");
		out.println("</body>");
		out.println("</html>");
	}
}
반응형

'Back-end > Oracle' 카테고리의 다른 글

Oracle 7일차 - INNER JOIN  (0) 2020.08.12
Oracle 6일차 - SQL 복수행함수2  (0) 2020.08.11
Oracle 4일차 - SQL 단일행함수2 / 지니뮤직  (0) 2020.08.07
Oracle 3일차 - SQL 단일행함수  (0) 2020.08.06
Oracle 2일차 - SQL WHERE 조건문  (0) 2020.08.05
'Back-end/Oracle' Related Articles more
Comments