면접족보 21/01/07_SQL문

1. SQL 명령문이란?

· 데이터베이스를 처리하기 위해 고안된 언어로 자료를 검색하고 수정, 삭제하는 기능을 한다. (DB표준 언어)

· SQl 명령문의 종류로는 DDL, DML, DCL, DQL 등이 사용된다.

※DESC/SELECT/WHERE 절

1)DESC: 테이블 구조 확인하기 위한 명령어  
  desc emp;
2)SELECT: 테이블에 저장된 데이터를 조회하기 위한 명령어
   select * from emp;
3)WHERE: SELECT문에서 마지막에 표기되며, 원하는 행을 검색하고자 할 때 사용
   select * from emp where sal <= 3000;   (급여(sal)가 3000 이하인 것을 출력)

 

※산술/비교/논리 연산자

1)산술 연산자: +, -, / , * 등의 연산자 사용 
   select ename, sal, sal+100 from emp;  (sal값에 100을 더하여 출력)
2)비교 연산자: =, >, <, >=, <=, <>, !=, ^= 등의 연산자 사용
  select * from emp where deptno <> 20;  (부서번호(deptno)가 20이 아닌 사원에 관한 정보만 출력)
  select empno, ename, sal from emp where ename = 'FORD';  (이름이 FORD인 사람의 사번, 이름, 급여를 출력)
  select * from emp where hiredate >= '1982/01/01'; (1982년1월1일 이후 입사한 사원을 출력, 1982.01.01 표현도 가능)
3)논리 연산자: AND, OR, NOT 키워드 사용
  select * from emp where job = 'MANAGER' and deptno = 10;  (job이 manage이고, 10번 부서인 사원 출력)
  select * from emp where job = 'MANAGER' or deptno = 10;  (job이 manage이거나 10번 부서인 사원 출력)
  select * from emp where not deptno 10;  (부서번호(deptno)가 10이 아닌 사원 출력)

※BETWEEN/IN 연산자

1)BETWEEN: 두 개의 조건 사이에 해당되는 행을 반환
  select * from emp where sal between 2000 and 3000;  (급여가 2000~3000 사이의 사원을 검색)
  select * from emp where sal not between 2000 and 3000;  (급여가 2000 미만이거나 3000 초과인 사원을 검색)
  select * from emp where hiredate between '1987/01/01' and '1987/12/31';  (1987년에 입사한 사원을 출력)
2)IN: OR 연산자와 비슷한 용도로 사용
  select * from emp where comm in (300, 500, 1400);  (커미션이 300 혹은 500 혹은 1400인 사원 검색)
  select * from emp where not comm in (300, 500, 1400);  (커미션이 300 혹은 500 혹은 1400이 아닌 사원 검색)

 

※LIKE 연산자와 와일드 카드

1)%: 컬럼 값에 해당 문자가 포함되는지 검색할 때 사용
  select ename from emp where ename LIKE 'F%';  (이름이 F로 시작하는 사원 출력)
  select ename from emp where ename LIKE '%A%';  (위치 상관 없이 이름 중에 A가 들어있는 사원 출력)
  select ename from emp where ename LIKE '%N';  (이름이 N으로 끝나는 사원 출력)
  select ename from emp where ename not LIKE '%A%';  (이름에 A를 포함하지 않는 사원 출력)
2)_ : 어떠한 문자가 오든 상관없다는 의미로 사용
  select ename from emp where ename LIKE '_A%';  (이름의 두 번째 글자가 A인 사원 출력)
  select ename from emp where ename LIKE '__A%  (이름의 세 번째 글자가 A인 사원 출력)

※NULL 연산자

· 컬럼이 NULL인지 아닌지를 비교/검색할 떄 사용

  select * from emp where comm is null;  (커미션을 받지 않는 사원 출력)
  select * from emp where comm is not null;  (커미션을 받는 사원 출력)

 

※ORDER BY절: 정렬할 때 사용

1)ASC(오름차순): 숫자-작은 값부터, 문자-사전 순으로 정렬
  select * from emp order by sal asc;  (사원들의 급여를 오름차순으로 정렬)
2)DESC(내림차순): 숫자-큰 값부터, 문자-사전 반대 순으로 정렬
  select * from emp order by hiredate desc;  (가장 최근에 입사한 사원부터 정렬)

※DISTINCT/별칭/연결 연산자

1)DISTINCT: 동일한 데이터 값들이 중복 출력되지 않도록 하는 연산자
   select distinct job from emp;  (job을 중복제거하여 출력) 
   select distinct deptno from emp order by deptno asc;  (사원들이 소속되어 있는 부서의 번호를 중복제거하여 출력)
2)별칭 연산자: 컬럼 이름에 별칭 부여(의미를 알아 보기 힘든 경우에 사용), as + " " 방식으로 표기하며 as는 생략 가능
  select ename as "이름" from emp;  (ename을 이름으로 변경하여 출력)
  select ename, sal*12 + nvl(comm, 0) "연봉" from emp;  (연봉 컬럼에 sal에 12를 곱하고 comm을 더한 값을 출력) 
3)연결 연산자: || 수직바를 사용해서 표현하며, || 내에 있는 문자열을 사용하여 두 개의 컬럼 값을 연결
  select ename || ' is a ' || job from emp;  (SMITH is a CLERK 이와 같은 형식으로 값이 출력)

※DUAL 테이블

· dual은 단순히 산술연산 결과를 하나의 행으로 출력하기 위한 테이블, 무엇인가를 확인하는 용도로 사용
  select 24*60 from dual;  (24*60만 실행하면 오류 발생, dual을 이용하여 24*60 값을 출력)
  select sysdate from dual;  (당일 날짜 출력)

※숫자 함수

1)ABS: 절대값 구하는 함수
  select -10, abs(-10) from dual;  (-10의 절대값을 출력)
2)FLOOR: 소수점 아래를 버리는 함수
  select 34.5432, floor(34.5432) from dual;  (34 출력)
3)ROUND: 특정 자리에서 반올림하는 함수
  select 34.5678, round(34.5678) from dual;  (35 출력)
  select 34.5678, round(34.5678, 2) from dual;  (34.57 출력, 소수점 자리 2번째까지 표현)
  select 34.5678, round(34.5678, -1) from dual;  (30 출력, 소수점 자리에서 역순을 의미하며 첫째자리(4)에서 반올림)
4)TRUNC: 특정 자리에서 잘라내는 함수
  select trunc(34.5678,2), trunc(34.5678, -1), trunc(34.5678), trunc(34.5678, 0) from dual;  (34.56 30 34.5678 34 출력)
5)MOD: 나누기 연산 후 나머지를 출력하는 함수
  select mod(27, 2), mod(27, 5), mod(27, 7) from dual;  (1, 2, 6 출력)

 

※연습문제※

♣Bean을 이용하여 가위바위보 게임을 작성하시오♣

 

<구현 화면>

 

 

<Game.java>

 public class Game {
    private int my; 
    private int com = (int)(Math.random() * 3) + 1;
 
    public Game() {
    }
    public int getMy() {
        return my;
    }
    public void setMy(int my) {
        this.my = my;
    }
    public int getCom() {
        return com;
    }
    public void setCom(int com) {
        this.com = com;
    }
    public String getResult() {
        if((my==1)&&(com==2)){
            return "패";
        }else if((my==1)&&(com==3)){
            return "승리";
        }else if((my==2)&&(com==1)){
            return "승리";
        }else if((my==2)&&(com==3)){
            return "패";
        }else if((my==3)&&(com==1)){
            return "패";
        }else if((my==3)&&(com==2)){
            return "승리";
        }else {
            return "무승부";
        }
    }
 }

 

<game.html>

 <head>
 <meta charset="EUC-KR">
 <title>Insert title here</title>
 <style>
    img{
        width:550px;
        height:400px;
    }
 </style>
 </head>
 <body>
    <h1>가위바위보 게임</h1>
    <img src="https://img.fmnation.net/files/attach/images/6765486/663/423/024/34e3fa17c49a31cb1bd9a3b3e528172e.jpeg"><br/>
    <form action="game2.jsp" method="post">
        <select name="name">
            <option value="1" selected="selected">가위</option>
            <option value="2">바위</option>
            <option value="3">보</option>
        </select>
        <input type="submit" value="제출">
    </form>
 </body>

 

<game.jsp>

 <body>
    <%!
    int my, com;
    %>
 
    <%
    request.setCharacterEncoding("EUC-KR");
    my = Integer.parseInt(request.getParameter("name"));
    com = (int)(Math.random()*3) + 1;
    %>
 
    <h1>당신이 낸 것</h1>
   
    <%
        if(my == 1){
    %>
    <img src="가위.JPG"><br/>
    <%
        }else if(my == 2){
    %>
    <img src="바위.JPG"><br/>
    <%
        }else{
    %>
    <img src="보.JPG"><br/>
    <% } %>
    
    <h1>컴퓨터가 낸 것</h1>    
 
    <%
        if(game.getCom() == 1){
    %>
    <img src="가위.JPG"><br/>
    <%
        }else if(game.getCom() == 2){
    %>
    <img src="바위.JPG"><br/>
    <%
        }else{
    %>
    <img src="보.JPG"><br/>
    <% 
        }    
    %><br/>
 
    <%= game.getResult() %>
    <a href="game.html">다시하기</a>
 </body>

 

 

 

♣커넥션풀을 이용하여 emp 리스트를 출력하시오♣

 

<구현 화면>

 

<emp.jsp>

 <%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
 <%! 
    Connection connection;
    Statement statement;
    ResultSet resultSet;
    
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String uid = "scott";
    String upw = "tiger";
    String query = "select * from emp";
 %>
 <!DOCTYPE html>
 <html>
 <head>
 <meta charset="EUC-KR">
 <title>Insert title here</title>
 </head>
 <body>
   <table border="1">
        <tr>
            <td>EMPNO</td>
            <td>ENAME</td>
            <td>JOB</td>
            <td>MGR</td>
            <td>HIREDATE</td>
            <td>SAL</td>
            <td>COMM</td>
            <td>DEPTNO</td>    
        </tr>
        <% 
            try{
                Class.forName(driver);
            
                connection = DriverManager.getConnection(url,uid,upw);
                statement = connection.createStatement();
                resultSet = statement.executeQuery(query);
            
                while(resultSet.next()){
                    String empno = resultSet.getString("EMPNO");
                    String ename = resultSet.getString("ename");
                    String job = resultSet.getString("job");
                    String mgr = resultSet.getString("mgr");
                    String hiredate = resultSet.getString("hiredate");
                    String sal = resultSet.getString("sal");
                    String comm = resultSet.getString("comm");
                    String deptno = resultSet.getString("deptno");            
        %>
            <tr>
                <td><%=empno%></td>
                <td><%=ename%></td>
                <td><%=job%></td>
        <%
                    if(mgr==null){
                        out.print("<td>" + "\t" + "</td>");
                    }else
                        out.print("<td>" + mgr + "</td>");
        %>
                <td><%=hiredate%></td>
                <td><%=sal%></td>
        <%
                    if(comm==null){
                        out.print("<td>" + "\t" + "</td>");
                    }else
                        out.print("<td>" + comm + "</td>");
        %>
                <td><%=deptno%></td>
            </tr>
        <% 
                }
        %>
 
        <%
            }catch(Exception e){                
            }finally{
                try{
                    if(resultSet != null) 
                        resultSet.close();
                    if(statement != null) 
                        statement.close();
                    if(connection != null) 
                        connection.close();
                }catch(Exception e){
                }
            }
        %>
    </table>
 </body>
 </html>