21.01.13 Wed [036]

728x90

SQL

outer join

조인 조건에 만족하지 않는 행도 나타내는 조인 기법이다.

SELECT * FROM emp;

↓결과

 SELECT
    employee.ename
    || '의 매니저는 '
       || manager.ename
          || '입니다.'
FROM
    emp  employee,
    emp  manager
WHERE
    employee.mgr = manager.empno (+);

↓결과

<4가지>

DML(데이터 조작어)" select, insert ,update, delete -" 데이터 다루는 것

DDL(데이터 정의어) "create, alter ,crop ,rename ,truncate "-테이블 다루는 것

DCL TCL - 이거 두개는 나중에 배움.

<데이터 추가하는 insert 명령문>

일단 연습용으로 쿼리 복사

//테이블만 복사?

 CREATE TABLE DEPT02 AS SELECT * FROM DEPT WHERE 1=0;
//데이터 구조만 복사해서 새로운 테이블 생성

WHERE 1=1 은 언제나 '참' / OR 구문에서는 1=0;

 INSERT INTO dept02 (
    deptno,
    dname,
    loc
) VALUES (
    10,
    'accounting',
    'new york'
);
 
SELECT * FROM dept02;

결과값

 INSERT INTO dept02
VALUES (
    20,
    'research',
    'dallas'
);
//만약 테이블 지정안해주면 그냥 순서대로 알아서 들어간다.

결과값

그리고 commit; 시키는 것 잊지말기 - 그냥 변경된 내용을 모두 영구 저장한다. 라고 이해하기.

<내용까지 복사하는 create 명령문>

 create table emp01 as select * from emp;
//emp복사해서 emp01에 넣어라(이건 테이블 뿐만아니라 내용도 복사된다.)

<데이터 수정하는 update 명령문>

 모든 사원의 부서 번호를 30번으로 수정 후 확인하는 쿼리문
update emp01 set deptno = 30;

 //사원의 급여를 10% 인상시키는 update문을 만드는 쿼리문
update emp01 set sal = sal+(sal*0.1); 또는 sal*1.1;

 //모든 입사일을 오늘로 수정하는 쿼리문
update emp01 set hiredate=sysdate;

 //부서번호가 10번인 사원의 부서번호를 40번으로 수정하는 쿼리문
update emp01 set deptno=40 where deptno=10;

 //job컬럼값이 manager인 경우, 급여를 10% 인상하는 쿼리문
update emp01 set  sal=sal*1.1  where job='MANAGER';

 //1982년에 입사한 사원의 입사일을 오늘로 수정하는 쿼리문
update emp01 set hiredate=sysdate where hiredate between '1982/01/01' and '1982/12/31';
update emp01 set hiredate=sysdate where substr(hiredate, 1, 2)='82'; //이렇게해도 가능

 //SMITH사원의 부서번호는 20번으로, job컬럼값은 manager로 한꺼번에 수정
update emp01 set deptno=20, job='MANAGER' where ename='SMITH';

<데이터를 삭제하는 delete 명령문>

 create table dept01 as select * from dept;
select * from dept01;
delete from dept01;

 create table dept01 as select * from dept;
select * from dept01;
//다시 생성해서 연습...dept01을 복사해야함

 //30번 부서 삭제하기
delete from dept01 where deptno=30;

<DDL> - 안에 어떤 명령문이있는지 다 외울필요없지만 익숙해지기

문자 숫자 데이터 순으로 많이씀

<가변길이 문자데이터 VARCHRA2(size)>

varchar2(size) - 가변형이다. / 바차투 라고 읽는다.

 CREATE TABLE emp01 (
    empno  NUMBER(4), //배열공간 4개
    ename char(1000), //배열공간 1000개
    sal NUMBER(7, 2)
); 
//근데 1000개는 너무 많을 수 있다.
 
CREATE TABLE emp01 (
    empno  NUMBER(4),
    ename  VARCHAR2(1000),//varchar2를 쓰면 입력된 길이만큼만 배열공간가짐
    sal NUMBER(7, 2)
);

파일같은건 db에 저장 거의 안하기때문에(그냥 링크만 걸어놓음) ddl거의 안씀 - db 모델링 툴 로 다 사용하게 됨

char(size), varchar2(size), number, number(w)정도만 알아두기

 CREATE TABLE emp01 (
    empno  NUMBER(4),
    ename  VARCHAR2(20),
    sal    NUMBER(7, 2)
);

**desc 는 DTO만들때 유용하다. ex)desc emp01;

<컬럼추가하는 alter>

 //emp01 테이블에서 job 컬럼을 추가하고싶으면?
alter table emp01 add (job varchar2(9));
 
확인은 desc emp01; 로 하기

<컬럼 수정 변경 하는 alter modify>

alter table emp01 modify (job varchar(30));

출력화면

<기존 컬럼 삭제하는 drop> - 실무에서 테스트용 아니고서야 안씀

 alter table emp01 drop column job;
//emp01 테이블의 job컬럼을 삭제

<잘라내기 truncate>

delete, truncate , drop 명령어 차이

DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어 들지 않는다. 원하는 데이터만 지울 수 있다. 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.
TRUNCATE 명령어는 용량이 줄어 들고, 인덱스 등도 모두 삭제 된다. 테이블은 삭제하지는 않고, 데이터만 삭제한다. 한꺼번에 다 지워야 한다. 삭제 후 절대 되돌릴 수 없다.
DROP 명령어는 데이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.

<데이터 딕셔너리와 데이터 딕셔너리뷰 DD> 중요도하지않은데 정처기에서만 나옴

시스템테이블이다.

desc user_tables;

<서브쿼리> select문이 2개 들어가있는 문

 //사원들의 평균급여보다 더 많은 급여를 받는 사원을 검색하는 쿼리문
select ename, sal from emp where sal > (select avg(sal) from emp);

↓join의 기본 반드시 외우기 ★★★

 //SMITH가 속한 부서의 이름
 
 
//join 방식
일단 emp와 dept 에 있으니까 join!
select e.ename,d.dname from emp e, dept d where d.deptno=e.deptno; // join은 이거 무조건 외우기
 
select e.ename,d.dname from emp e, dept d where d.deptno=e.deptno and e.ename='SMITH';
 
//서브쿼리 방식
select dname from dept where deptno= (select deptno from emp where ename = 'SMITH');

 //연봉을 3000이상 받는 사원이 소속된 부서와 
동일한 부서에서 근부하는 사원들의 정보를 출력하는 쿼리문
 
select ename, sal, deptno from emp where deptno 
in (select distinct deptno from emp where sal >= 3000);
//결과물이 2개이상일땐 in으로 처리한다.

어제 게시판 list이어서...

content, write, command 부분 생성

 [content_view.jsp]
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
 
   <table width="500" cellpadding="0" cellspacing="0" border="1">
      <form action="modify.do" method="post">
         <input type="hidden" name="bId" value="${content_view.bId}">
         <tr>
            <td> 번호 </td>
            <td> ${content_view.bId} </td>
         </tr>
         <tr>
            <td> 히트 </td>
            <td> ${content_view.bHit} </td>
         </tr>
         <tr>
            <td> 이름 </td>
            <td> <input type="text" name="bName" value="${content_view.bName}"></td>
         </tr>
         <tr>
            <td> 제목 </td>
            <td> <input type="text" name="bTitle" value="${content_view.bTitle}"></td>
         </tr>
         <tr>
            <td> 내용 </td>
            <td> <textarea rows="10" name="bContent" >${content_view.bContent}</textarea></td>
         </tr>
         <tr >
            <td colspan="2"> <input type="submit" value="수정"> &nbsp;&nbsp; <a href="list.do">목록보기</a> &nbsp;&nbsp; <a href="delete.do?bId=${content_view.bId}">삭제</a> &nbsp;&nbsp; <a href="reply_view.do?bId=${content_view.bId}">답변</a></td>
         </tr>
      </form>
   </table>
   
</body>
</html>

 [BFrontController.java]
package edu.bit.ex.controller;
//패키지명 반드시 이렇게 쓰기
 
import java.io.IOException;
 
import javax.servlet.RequestDispatcher;
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 edu.bit.ex.command.BCommand;
import edu.bit.ex.command.BContentCommand;
import edu.bit.ex.command.BDeleteCommand;
import edu.bit.ex.command.BListCommand;
import edu.bit.ex.command.BModifyCommand;
import edu.bit.ex.command.BWriteCommand;
 
@WebServlet("*.do")
// 유저가 url치고들어오는데 뭘치고 들어오든 .do(controll로 들어오는거)면 다 받겠다.
public class BFrontController extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
	public BFrontController() {
		super();
 
	}
 
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		System.out.println("doGet");
		actionDo(request, response);
	}
 
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
 
		doGet(request, response);
	}
 
	private void actionDo(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException { // 한글처리했을때 에러나서 throws 추가
		System.out.println("actionDo"); // .do로 들어오면 actionDo로 표시하겠다.
 
		request.setCharacterEncoding("EUC-KR");
 
		String viewPage = null; // FrontController에서 view들을 결정하는것.
		BCommand command = null; // 그림에 command command 객체가 바로 이거.
		// Command가 인터페이스(자손구현하는것)이다.
 
		String uri = request.getRequestURI();
		String conPath = request.getContextPath();
		String com = uri.substring(conPath.length());
 
		System.out.println(uri);
		System.out.println(conPath);
		System.out.println(com);
 
		if (com.equals("/list.do")) {
			// .list.do 이렇게 치고 들어오게 링크주는것.
			command = new BListCommand();
			// ↑다형성 적용. BCommand는 부모
			command.execute(request, response);
			viewPage = "list.jsp";
		} else if (com.equals("/content_view.do")) {
			// .list.do 이렇게 치고 들어오게 링크주는것.
			command = new BContentCommand();
			// ↑다형성 적용. BCommand는 부모
			command.execute(request, response);
			viewPage = "content_view.jsp";
			// 얘로 받아와야하니까 수정.
		} else if (com.equals("/write_view.do")) {
			viewPage = "write_view.jsp";
			// 얘로 받아와야하니까 수정.
		} else if (com.equals("/write.do")) {
			// write.do에서 넘어오는거만듦
			command = new BWriteCommand();
			command.execute(request, response);
			viewPage = "list.do";
			// write가끝났으면 list.do로 돌아가야하니까
		} else if (com.equals("/delete.do")) {
			// write.do에서 넘어오는거만듦
			command = new BDeleteCommand();
			command.execute(request, response);
			viewPage = "list.do";
			// write가끝났으면 list.do로 돌아가야하니까
		} else if (com.equals("/modify.do")) {
			// write.do에서 넘어오는거만듦
			command = new BModifyCommand();
			command.execute(request, response);
			viewPage = "list.do";
			// write가끝났으면 list.do로 돌아가야하니까
		}
		RequestDispatcher dispatcher = request.getRequestDispatcher(viewPage);
		dispatcher.forward(request, response); //위치헷갈리지말기...
	}
}

 [BDao.java]
package edu.bit.ex.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
 
import edu.bit.ex.dto.BDto;
 
public class BDao {
	private DataSource dataSource;// 전역변수로 수정
 
	public BDao() {
 
		try {
			Context context = new InitialContext();
 
			dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oracle");
 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
	public ArrayList<BDto> list() {
 
		ArrayList<BDto> dtos = new ArrayList<BDto>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
 
		ResultSet resultSet = null;
 
		try {
			connection = dataSource.getConnection();
 
			String query = "select bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent from mvc_board order by bGroup desc, bStep asc";
 
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
 
			while (resultSet.next()) {
				int bId = resultSet.getInt("bId");
				String bName = resultSet.getString("bName");
				String bTitle = resultSet.getString("bTitle");
				String bContent = resultSet.getString("bContent");
				Timestamp bDate = resultSet.getTimestamp("bDate");
 
				int bHit = resultSet.getInt("bHit");
				int bGroup = resultSet.getInt("bGroup");
				int bStep = resultSet.getInt("bStep");
				int bIndent = resultSet.getInt("bIndent");
 
				BDto dto = new BDto(bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
				dtos.add(dto);
			}
 
		} catch (Exception e) {
 
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
 
				e2.printStackTrace();
			}
		}
		return dtos;
	}
 
	public BDto contentView(String bId) {
		BDto dtos = null;// 위에Array소스 복붙하고 얘는 배열받을필요없으니까 수정
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
 
		try {
			connection = dataSource.getConnection();
 
			String query = "select * from mvc_board where bId =?";
			// ?로 받기.이건 prepareStatement의 자바문법이니 외우기\
			// ?는 함수에서 변수로 넘어올때 쓴다.
 
			preparedStatement = connection.prepareStatement(query);
 
			preparedStatement.setInt(1, Integer.parseInt(bId)); // bId가 int여서 1로주고 파싱하기
			// 이렇게 쓰면 위에 ?에 자동으로 1이 들어간다.
 
			resultSet = preparedStatement.executeQuery();
 
			while (resultSet.next()) {
				int id = resultSet.getInt("bId");
				// 위에 bid있으니까 id로 수정
				String bName = resultSet.getString("bName");
				String bTitle = resultSet.getString("bTitle");
				String bContent = resultSet.getString("bContent");
				Timestamp bDate = resultSet.getTimestamp("bDate");
				int bHit = resultSet.getInt("bHit");
				int bGroup = resultSet.getInt("bGroup");
				int bStep = resultSet.getInt("bStep");
				int bIndent = resultSet.getInt("bIndent");
 
				dtos = new BDto(id, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
			}
 
		} catch (Exception e) {
 
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
 
				e2.printStackTrace();
			}
		}
		return dtos;
	}
 
	public void write(String bName, String bTitle, String bContent) {
		// 위에contentView소스 복붙.
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null; - 얘는 필요없다.
 
		try {
			connection = dataSource.getConnection();
 
			String query = "insert into mvc_board (bId, bName, bTitle, bContent, bHit, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, 0, mvc_board_seq.currval, 0, 0 )";
			// ?로 받기.이건 prepareStatement의 자바문법이니 외우기
			// ?는 함수에서 변수로 넘어올때 쓴다.
 
			preparedStatement = connection.prepareStatement(query);
 
			preparedStatement.setString(1, bName);
			preparedStatement.setString(2, bTitle);
			preparedStatement.setString(3, bContent);
 
			int rn = preparedStatement.executeUpdate();
			// insert는 update로 들어가줘야함
			System.out.println("insert 결과" + rn);
 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
 
	public void delete(int bId) {
		// 위에 write 소스 복붙.
		Connection connection = null;
		PreparedStatement preparedStatement = null;
 
		try {
			connection = dataSource.getConnection();
			String query = "delete from mvc_board where bId=?";
 
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, bId);
			int rn = preparedStatement.executeUpdate();
			//
			System.out.println("delete 결과" + rn);
 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
 
	public void modify(int bId, String bName, String bTitle, String bContent) {
		// 위에 write 소스 복붙.
		Connection connection = null;
		PreparedStatement preparedStatement = null;
 
		try {
			connection = dataSource.getConnection();
			String query = "update mvc_board set bName = ?, bTitle = ?, bContent = ? where bId = ?";
			preparedStatement = connection.prepareStatement(query);
 
			preparedStatement.setString(1, bName);
			preparedStatement.setString(2, bTitle);
			preparedStatement.setString(3, bContent);
			preparedStatement.setInt(4, bId);			
 
			int rn = preparedStatement.executeUpdate();
			System.out.println("modify 결과" + rn);
 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
}

 [BDeleteCommand.java]
package edu.bit.ex.command;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import edu.bit.ex.dao.BDao;
 
public class BDeleteCommand implements BCommand {
 
	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
 
		int bId = Integer.parseInt(request.getParameter("bId"));
	
		BDao dao = new BDao();
		dao.delete(bId);
 
	}
 
}

 [BContentCommand.java]
package edu.bit.ex.command;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import edu.bit.ex.dao.BDao;
import edu.bit.ex.dto.BDto;
 
public class BContentCommand implements BCommand {
 
	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		String bId= request.getParameter("bId");
		
		BDao  dao = new BDao();
		BDto dto = dao.contentView(bId); //BDao.java 에 생성
										//array에 ctrl+c,v
		
		request.setAttribute("content_view", dto);
 
	}
 
}

 [BModifyCommand.java]
package edu.bit.ex.command;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import edu.bit.ex.dao.BDao;
 
public class BModifyCommand implements BCommand {
 
	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		int bId = Integer.parseInt(request.getParameter("bId"));
		String bName = request.getParameter("bName");
		String bTitle = request.getParameter("bTitle");
		String bContent = request.getParameter("bContent");
 
		BDao dao = new BDao();
		dao.modify(bId, bName, bTitle, bContent);
 
	}
 
}

* 헷갈리는 거 정리*

request.setParameter() 와 request.getParameter()를 이용하면 String의 값 밖에는 주고 받을 수 없다.
그럼 Action으로 넘어온 값을 변경시킨후 JSP 페이지로 넘겨주기 위해서는 request.setAttribute() 를 써서 넘겨주고 JSP 페이지에서는 request.getAttribute()를 써서 받아야한다.
이때 request.setAttribute() 를 통해 넘겨준 값을 request.getParameter() 를 써서 받으려 해도 받지 못한다.
이때 type이 Object 이기 때문에 반드시 형변환을 해줘야 한다.
* action에서 객체를 request에 담아 보낼경우.
   request.setAttribute("객체명", 객체);
   ex) request.setAttribute("page",page)
   만약 form 의 hidden 값인 page 를 넘겨받았다면 그 값을 변경하여 다시 보낼수 있다.
이때 사용하는 것이 request.setAttribute() 이다.
request.getAttribute() 는 요청된 이름의 Value 를 Object로 넘겨준다.
getAttribute는 특정 요소노드 내에 특정 한 속성값을 가져오는 메소드이다.
setAttribute는 메소드는 속성값을 변경시키는 메소드이다.
사용방법은 객체명.setAttribute("속성노드명",새로운속성값);
Object x = request.getAttribute("객체")

1. 게시판 삭제와 업데이트(수정)를 구현하시오.

 [BDao.java]
public void delete(int bId) {
		// 위에 write 소스 복붙.
		Connection connection = null;
		PreparedStatement preparedStatement = null;
 
		try {
			connection = dataSource.getConnection();
			String query = "delete from mvc_board where bId=?";
 
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, bId);
			int rn = preparedStatement.executeUpdate();
			//
			System.out.println("delete 결과" + rn);
 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
 
	public void modify(int bId, String bName, String bTitle, String bContent) {
		// 위에 write 소스 복붙.
		Connection connection = null;
		PreparedStatement preparedStatement = null;
 
		try {
			connection = dataSource.getConnection();
			String query = "update mvc_board set bName = ?, bTitle = ?, bContent = ? where bId = ?";
			preparedStatement = connection.prepareStatement(query);
 
			preparedStatement.setString(1, bName);
			preparedStatement.setString(2, bTitle);
			preparedStatement.setString(3, bContent);
			preparedStatement.setInt(4, bId);			
 
			int rn = preparedStatement.executeUpdate();
			System.out.println("modify 결과" + rn);
 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

 [BDeleteCommand.java]
package edu.bit.ex.command;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import edu.bit.ex.dao.BDao;
 
public class BDeleteCommand implements BCommand {
 
	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
 
		int bId = Integer.parseInt(request.getParameter("bId"));
	
		BDao dao = new BDao();
		dao.delete(bId);
 
	}
 
}

 [BModifyCommand.java]
package edu.bit.ex.command;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import edu.bit.ex.dao.BDao;
 
public class BModifyCommand implements BCommand {
 
	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		int bId = Integer.parseInt(request.getParameter("bId"));
		String bName = request.getParameter("bName");
		String bTitle = request.getParameter("bTitle");
		String bContent = request.getParameter("bContent");
 
		BDao dao = new BDao();
		dao.modify(bId, bName, bTitle, bContent);
 
	}
 
}

2.아래를 sql 문으로 나타 내시오.
--1> 부서테이블의 모든 데이터를 출력하라.

 select * from dept;
desc dept;

--2> EMP테이블에서 각 사원의 직업, 사원번호, 이름, 입사일을 출력하라.

select job,empno,ename, hiredate from emp;

--3> EMP테이블에서 직업을 출력하되, 각 항목(ROW)가 중복되지 않게 출력하라.

select distinct job from emp ;

--4> 급여가 2850 이상인 사원의 이름 및 급여를 표시하는 출력하라.

select ename, sal from emp where sal>=2850 ;

--5> 사원번호가 7566인 사원의 이름 및 부서번호를 표시하는 출력하라.

select e.ename, d.deptno, e.empno from emp e,dept d where e.empno=7566;

--6> 급여가 1500이상 ~ 2850이하의 범위에 속하지 않는 모든 사원의 이름 및 급여를 출력하라.

select ename, sal from emp where sal not between 1500 and 2850;

--7> 1981년 2월 20일 ~ 1981년 5월 1일에 입사한 사원의 이름,직업 및 입사일을 출력하라.
입사일을 기준으로 해서 오름차순으로 정렬하라.

 select ename, job, hiredate from emp 
where  hiredate between '81/02/20' and '81/05/01' 
order by hiredate asc;

--8> 10번 및 30번 부서에 속하는 모든 사원의 이름과 부서 번호를 출력하되,
이름을 알파벳순으로 정렬하여 출력하라.

 select e.ename, d.deptno, d.dname
from emp e, dept d
where d.deptno in(10,30) order by e.ename asc;

--9> 10번 및 30번 부서에 속하는 모든 사원 중 급여가 1500을 넘는 사원의
이름 및 급여를 출력하라.
--(단 컬럼명을 각각 employee 및 Monthly Salary로 지정하시오)

 select e.ename as "employee" ,e.sal as "Monthly Salary" 
from emp e, dept d where d.deptno in(10,30) and sal>1500;

--10> 관리자가 없는 모든 사원의 이름 및 직위를 출력하라.

select ename, job, mgr from emp where mgr is null;

--11> 커미션을 받는 모든 사원의 이름, 급여 및 커미션을 출력하되, 급여를 기준으로
내림차순으로 정렬하여 출력하라.

select ename, sal, comm from emp where comm != '0' order by sal desc;

--12> 이름의 세 번째 문자가 A인 모든 사원의 이름을 출력하라.

select ename from emp where ename like '__A%';

--13> 이름에 L이 두 번 들어가며 부서 30에 속해있는 사원의 이름을 출력하라.

 select e.ename, d.deptno from emp e, dept d 
where e.ename like '%L%L%' and d.deptno = 30;

--14> 직업이 Clerk 또는 Analyst 이면서 급여가 1000,3000,5000 이 아닌
모든 사원의 이름, 직업 및 급여를 출력하라.

 select ename, job, sal from emp 
where sal not in(1000,3000,5000) and job in('CLERK','ANALYST');

--15> 사원번호, 이름, 급여 그리고 15%인상된 급여를 정수로 표시하되 컬럼명을
New Salary로 지정하여 출력하라.

select deptno, ename, sal, sal*1.5 as "New Salary" from emp;

--16> 15번 문제와 동일한 데이타에서 급여 인상분(새 급여에서 이전 급여를 뺀 값)을 추가해서
출력하라.(컬럼명은 Increase로 하라).

select deptno, ename, sal, (sal*1.5-sal) as "Increase" from emp;

--18> 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를
표시하는 쿼리를 작성하고 컬럼 별칭은 적당히 넣어서 출력하라.

select initcap(ename)as name, length(ENAME) as name_length from emp;

--19> 사원의 이름과 커미션을 출력하되, 커미션이 책정되지 않은
사원의 커미션은 'no commission'으로 출력하라.

 select ename, decode(comm,0,'no commission',null,'no commission',comm) from emp;
//↓선생님 풀이
select empno, deptno, nvl(to_char(comm), 'no commission') from emp;

오라클 sql developer에서 잘못 건드려서 파일 수정되었을때 다시 시간 되돌려서 복구하는 방법.

SELECT *
FROM emp
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '30' MINUTE);

728x90

저작자표시 비영리 변경금지 (새창열림)

'코딩 > 수업 정리' 카테고리의 다른 글

21.01.15 [038] Fri (0)	2021.01.15
21.01.14 Thu [037] (0)	2021.01.14
21.01.12 Tue [035] (0)	2021.01.12
21.01.11 Mon [034] (0)	2021.01.11
21.01.08 Fri [033] (0)	2021.01.08

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

공부 기록하는 blog

21.01.13 Wed [036]

SQL

어제 게시판 list이어서...

* 헷갈리는 거 정리*

'코딩 > 수업 정리' 카테고리의 다른 글

댓글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	SELECT
	employee.ename
	\|\| '의 매니저는 '
	\|\| manager.ename
	\|\| '입니다.'
	FROM
	emp employee,
	emp manager
	WHERE
	employee.mgr = manager.empno (+);

	CREATE TABLE DEPT02 AS SELECT * FROM DEPT WHERE 1=0;
	//데이터 구조만 복사해서 새로운 테이블 생성

	INSERT INTO dept02 (
	deptno,
	dname,
	loc
	) VALUES (
	10,
	'accounting',
	'new york'
	);

	SELECT * FROM dept02;

	INSERT INTO dept02
	VALUES (
	20,
	'research',
	'dallas'
	);
	//만약 테이블 지정안해주면 그냥 순서대로 알아서 들어간다.

	create table emp01 as select * from emp;
	//emp복사해서 emp01에 넣어라(이건 테이블 뿐만아니라 내용도 복사된다.)

	모든 사원의 부서 번호를 30번으로 수정 후 확인하는 쿼리문
	update emp01 set deptno = 30;

	//사원의 급여를 10% 인상시키는 update문을 만드는 쿼리문
	update emp01 set sal = sal+(sal0.1); 또는 sal1.1;

	//모든 입사일을 오늘로 수정하는 쿼리문
	update emp01 set hiredate=sysdate;

	//부서번호가 10번인 사원의 부서번호를 40번으로 수정하는 쿼리문
	update emp01 set deptno=40 where deptno=10;

	//job컬럼값이 manager인 경우, 급여를 10% 인상하는 쿼리문
	update emp01 set sal=sal*1.1 where job='MANAGER';

	//1982년에 입사한 사원의 입사일을 오늘로 수정하는 쿼리문
	update emp01 set hiredate=sysdate where hiredate between '1982/01/01' and '1982/12/31';
	update emp01 set hiredate=sysdate where substr(hiredate, 1, 2)='82'; //이렇게해도 가능

	//SMITH사원의 부서번호는 20번으로, job컬럼값은 manager로 한꺼번에 수정
	update emp01 set deptno=20, job='MANAGER' where ename='SMITH';

	create table dept01 as select * from dept;
	select * from dept01;
	delete from dept01;

	create table dept01 as select * from dept;
	select * from dept01;
	//다시 생성해서 연습...dept01을 복사해야함

	//30번 부서 삭제하기
	delete from dept01 where deptno=30;

	CREATE TABLE emp01 (
	empno NUMBER(4), //배열공간 4개
	ename char(1000), //배열공간 1000개
	sal NUMBER(7, 2)
	);
	//근데 1000개는 너무 많을 수 있다.

	CREATE TABLE emp01 (
	empno NUMBER(4),
	ename VARCHAR2(1000),//varchar2를 쓰면 입력된 길이만큼만 배열공간가짐
	sal NUMBER(7, 2)
	);

	CREATE TABLE emp01 (
	empno NUMBER(4),
	ename VARCHAR2(20),
	sal NUMBER(7, 2)
	);

	//emp01 테이블에서 job 컬럼을 추가하고싶으면?
	alter table emp01 add (job varchar2(9));

	확인은 desc emp01; 로 하기

	alter table emp01 drop column job;
	//emp01 테이블의 job컬럼을 삭제

	//사원들의 평균급여보다 더 많은 급여를 받는 사원을 검색하는 쿼리문
	select ename, sal from emp where sal > (select avg(sal) from emp);

21.01.13 Wed [036]

SQL

어제 게시판 list이어서...

* 헷갈리는 거 정리*

'코딩 > 수업 정리' 카테고리의 다른 글

관련글

댓글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	//SMITH가 속한 부서의 이름


	//join 방식
	일단 emp와 dept 에 있으니까 join!
	select e.ename,d.dname from emp e, dept d where d.deptno=e.deptno; // join은 이거 무조건 외우기

	select e.ename,d.dname from emp e, dept d where d.deptno=e.deptno and e.ename='SMITH';

	//서브쿼리 방식
	select dname from dept where deptno= (select deptno from emp where ename = 'SMITH');

	//연봉을 3000이상 받는 사원이 소속된 부서와
	동일한 부서에서 근부하는 사원들의 정보를 출력하는 쿼리문

	select ename, sal, deptno from emp where deptno
	in (select distinct deptno from emp where sal >= 3000);
	//결과물이 2개이상일땐 in으로 처리한다.

	[content_view.jsp]
	<%@ page language="java" contentType="text/html; charset=EUC-KR"
	pageEncoding="EUC-KR"%>
	<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
	<html>
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
	<title>Insert title here</title>
	</head>
	<body>

	<table width="500" cellpadding="0" cellspacing="0" border="1">
	<form action="modify.do" method="post">
	<input type="hidden" name="bId" value="${content_view.bId}">
	<tr>
	<td> 번호 </td>
	<td> ${content_view.bId} </td>
	</tr>
	<tr>
	<td> 히트 </td>
	<td> ${content_view.bHit} </td>
	</tr>
	<tr>
	<td> 이름 </td>
	<td> <input type="text" name="bName" value="${content_view.bName}"></td>
	</tr>
	<tr>
	<td> 제목 </td>
	<td> <input type="text" name="bTitle" value="${content_view.bTitle}"></td>
	</tr>
	<tr>
	<td> 내용 </td>
	<td> <textarea rows="10" name="bContent" >${content_view.bContent}</textarea></td>
	</tr>
	<tr >
	<td colspan="2"> <input type="submit" value="수정">    <a href="list.do">목록보기</a>    <a href="delete.do?bId=${content_view.bId}">삭제</a>    <a href="reply_view.do?bId=${content_view.bId}">답변</a></td>
	</tr>
	</form>
	</table>

	</body>
	</html>

	[BFrontController.java]
	package edu.bit.ex.controller;
	//패키지명 반드시 이렇게 쓰기

	import java.io.IOException;

	import javax.servlet.RequestDispatcher;
	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 edu.bit.ex.command.BCommand;
	import edu.bit.ex.command.BContentCommand;
	import edu.bit.ex.command.BDeleteCommand;
	import edu.bit.ex.command.BListCommand;
	import edu.bit.ex.command.BModifyCommand;
	import edu.bit.ex.command.BWriteCommand;

	@WebServlet("*.do")
	// 유저가 url치고들어오는데 뭘치고 들어오든 .do(controll로 들어오는거)면 다 받겠다.
	public class BFrontController extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public BFrontController() {
	super();

	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
	System.out.println("doGet");
	actionDo(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {

	doGet(request, response);
	}

	private void actionDo(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException { // 한글처리했을때 에러나서 throws 추가
	System.out.println("actionDo"); // .do로 들어오면 actionDo로 표시하겠다.

	request.setCharacterEncoding("EUC-KR");

	String viewPage = null; // FrontController에서 view들을 결정하는것.
	BCommand command = null; // 그림에 command command 객체가 바로 이거.
	// Command가 인터페이스(자손구현하는것)이다.

	String uri = request.getRequestURI();
	String conPath = request.getContextPath();
	String com = uri.substring(conPath.length());

	System.out.println(uri);
	System.out.println(conPath);
	System.out.println(com);

	if (com.equals("/list.do")) {
	// .list.do 이렇게 치고 들어오게 링크주는것.
	command = new BListCommand();
	// ↑다형성 적용. BCommand는 부모
	command.execute(request, response);
	viewPage = "list.jsp";
	} else if (com.equals("/content_view.do")) {
	// .list.do 이렇게 치고 들어오게 링크주는것.
	command = new BContentCommand();
	// ↑다형성 적용. BCommand는 부모
	command.execute(request, response);
	viewPage = "content_view.jsp";
	// 얘로 받아와야하니까 수정.
	} else if (com.equals("/write_view.do")) {
	viewPage = "write_view.jsp";
	// 얘로 받아와야하니까 수정.
	} else if (com.equals("/write.do")) {
	// write.do에서 넘어오는거만듦
	command = new BWriteCommand();
	command.execute(request, response);
	viewPage = "list.do";
	// write가끝났으면 list.do로 돌아가야하니까
	} else if (com.equals("/delete.do")) {
	// write.do에서 넘어오는거만듦
	command = new BDeleteCommand();
	command.execute(request, response);
	viewPage = "list.do";
	// write가끝났으면 list.do로 돌아가야하니까
	} else if (com.equals("/modify.do")) {
	// write.do에서 넘어오는거만듦
	command = new BModifyCommand();
	command.execute(request, response);
	viewPage = "list.do";
	// write가끝났으면 list.do로 돌아가야하니까
	}
	RequestDispatcher dispatcher = request.getRequestDispatcher(viewPage);
	dispatcher.forward(request, response); //위치헷갈리지말기...
	}
	}

	[BDao.java]
	package edu.bit.ex.dao;

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

	import javax.naming.Context;
	import javax.naming.InitialContext;
	import javax.sql.DataSource;

	import edu.bit.ex.dto.BDto;

	public class BDao {
	private DataSource dataSource;// 전역변수로 수정

	public BDao() {

	try {
	Context context = new InitialContext();

	dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oracle");

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

	public ArrayList<BDto> list() {

	ArrayList<BDto> dtos = new ArrayList<BDto>();
	Connection connection = null;
	PreparedStatement preparedStatement = null;

	ResultSet resultSet = null;

	try {
	connection = dataSource.getConnection();

	String query = "select bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent from mvc_board order by bGroup desc, bStep asc";

	preparedStatement = connection.prepareStatement(query);
	resultSet = preparedStatement.executeQuery();

	while (resultSet.next()) {
	int bId = resultSet.getInt("bId");
	String bName = resultSet.getString("bName");
	String bTitle = resultSet.getString("bTitle");
	String bContent = resultSet.getString("bContent");
	Timestamp bDate = resultSet.getTimestamp("bDate");

	int bHit = resultSet.getInt("bHit");
	int bGroup = resultSet.getInt("bGroup");
	int bStep = resultSet.getInt("bStep");
	int bIndent = resultSet.getInt("bIndent");

	BDto dto = new BDto(bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
	dtos.add(dto);
	}

	} catch (Exception e) {

	e.printStackTrace();
	} finally {
	try {
	if (resultSet != null)
	resultSet.close();
	if (preparedStatement != null)
	preparedStatement.close();
	if (connection != null)
	connection.close();
	} catch (Exception e2) {

	e2.printStackTrace();
	}
	}
	return dtos;
	}

	public BDto contentView(String bId) {
	BDto dtos = null;// 위에Array소스 복붙하고 얘는 배열받을필요없으니까 수정
	Connection connection = null;
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	try {
	connection = dataSource.getConnection();

	String query = "select * from mvc_board where bId =?";
	// ?로 받기.이건 prepareStatement의 자바문법이니 외우기\
	// ?는 함수에서 변수로 넘어올때 쓴다.

	preparedStatement = connection.prepareStatement(query);

	preparedStatement.setInt(1, Integer.parseInt(bId)); // bId가 int여서 1로주고 파싱하기
	// 이렇게 쓰면 위에 ?에 자동으로 1이 들어간다.

	resultSet = preparedStatement.executeQuery();

	while (resultSet.next()) {
	int id = resultSet.getInt("bId");
	// 위에 bid있으니까 id로 수정
	String bName = resultSet.getString("bName");
	String bTitle = resultSet.getString("bTitle");
	String bContent = resultSet.getString("bContent");
	Timestamp bDate = resultSet.getTimestamp("bDate");
	int bHit = resultSet.getInt("bHit");
	int bGroup = resultSet.getInt("bGroup");
	int bStep = resultSet.getInt("bStep");
	int bIndent = resultSet.getInt("bIndent");

	dtos = new BDto(id, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
	}

	} catch (Exception e) {

	e.printStackTrace();
	} finally {
	try {
	if (resultSet != null)
	resultSet.close();
	if (preparedStatement != null)
	preparedStatement.close();
	if (connection != null)
	connection.close();
	} catch (Exception e2) {

	e2.printStackTrace();
	}
	}
	return dtos;
	}

	public void write(String bName, String bTitle, String bContent) {
	// 위에contentView소스 복붙.
	Connection connection = null;
	PreparedStatement preparedStatement = null;
	// ResultSet resultSet = null; - 얘는 필요없다.

	try {
	connection = dataSource.getConnection();

	String query = "insert into mvc_board (bId, bName, bTitle, bContent, bHit, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, 0, mvc_board_seq.currval, 0, 0 )";
	// ?로 받기.이건 prepareStatement의 자바문법이니 외우기
	// ?는 함수에서 변수로 넘어올때 쓴다.

	preparedStatement = connection.prepareStatement(query);

	preparedStatement.setString(1, bName);
	preparedStatement.setString(2, bTitle);
	preparedStatement.setString(3, bContent);

	int rn = preparedStatement.executeUpdate();
	// insert는 update로 들어가줘야함
	System.out.println("insert 결과" + rn);

	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	try {
	if (preparedStatement != null)
	preparedStatement.close();
	if (connection != null)
	connection.close();
	} catch (Exception e2) {
	e2.printStackTrace();
	}
	}
	}

	public void delete(int bId) {
	// 위에 write 소스 복붙.
	Connection connection = null;
	PreparedStatement preparedStatement = null;

	try {
	connection = dataSource.getConnection();
	String query = "delete from mvc_board where bId=?";

	preparedStatement = connection.prepareStatement(query);
	preparedStatement.setInt(1, bId);
	int rn = preparedStatement.executeUpdate();
	//
	System.out.println("delete 결과" + rn);

	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	try {
	if (preparedStatement != null)
	preparedStatement.close();
	if (connection != null)
	connection.close();
	} catch (Exception e2) {
	e2.printStackTrace();
	}
	}
	}

	public void modify(int bId, String bName, String bTitle, String bContent) {
	// 위에 write 소스 복붙.
	Connection connection = null;
	PreparedStatement preparedStatement = null;

	try {
	connection = dataSource.getConnection();
	String query = "update mvc_board set bName = ?, bTitle = ?, bContent = ? where bId = ?";
	preparedStatement = connection.prepareStatement(query);

	preparedStatement.setString(1, bName);
	preparedStatement.setString(2, bTitle);
	preparedStatement.setString(3, bContent);
	preparedStatement.setInt(4, bId);

	int rn = preparedStatement.executeUpdate();
	System.out.println("modify 결과" + rn);

	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	try {
	if (preparedStatement != null)
	preparedStatement.close();
	if (connection != null)
	connection.close();
	} catch (Exception e2) {
	e2.printStackTrace();
	}
	}
	}
	}

	[BDeleteCommand.java]
	package edu.bit.ex.command;

	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;

	import edu.bit.ex.dao.BDao;

	public class BDeleteCommand implements BCommand {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {

	int bId = Integer.parseInt(request.getParameter("bId"));

	BDao dao = new BDao();
	dao.delete(bId);

	}

	}

	[BContentCommand.java]
	package edu.bit.ex.command;

	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;

	import edu.bit.ex.dao.BDao;
	import edu.bit.ex.dto.BDto;

	public class BContentCommand implements BCommand {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
	String bId= request.getParameter("bId");

	BDao dao = new BDao();
	BDto dto = dao.contentView(bId); //BDao.java 에 생성
	//array에 ctrl+c,v

	request.setAttribute("content_view", dto);

	}

	}

	[BModifyCommand.java]
	package edu.bit.ex.command;

	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;

	import edu.bit.ex.dao.BDao;

	public class BModifyCommand implements BCommand {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
	int bId = Integer.parseInt(request.getParameter("bId"));
	String bName = request.getParameter("bName");
	String bTitle = request.getParameter("bTitle");
	String bContent = request.getParameter("bContent");

	BDao dao = new BDao();
	dao.modify(bId, bName, bTitle, bContent);

	}

	}

	select ename, job, hiredate from emp
	where hiredate between '81/02/20' and '81/05/01'
	order by hiredate asc;

	select e.ename, d.deptno, d.dname
	from emp e, dept d
	where d.deptno in(10,30) order by e.ename asc;