20220801_MON
저장위치: SELCET_2.SQL
- DB 별칭부여하기
--모든 사원의 사번,이름,급여,연봉을 조회한다.
--(연봉은 급여 * 12로 간주한다)
--조회 컬럼의 이름을 변경가능하다--> '별칭'
-- SAL*12 >> ANNI_SAL(연봉)
SELECT EMPNO 사번,ENAME AS 사원명,SAL,SAL*12 AS ANNI_SAL
FROM EMP;-
컬럼명 바꾸는 방법
--> 컬럼 뒤에 바꾸고싶은 컬럼명을 바로 쓰거나 AS 와 함께 쓴다.<변경 전>
<변경 후>
-- 마지막컬럼 : 연봉조회(급여 *12 + 인센티브)
SELECT EMPNO 사번,ENAME AS 사원명,SAL,SAL*12 AS ANNI_SAL, SAL *12 +COMM AS 실제연봉
FROM EMP;
왜 인센티브가 NULL인 사원은 연봉값도 NULL값으로 나오는가?(맨위사진과 비교)
주의!!! NULL 은 0과 같지 않다!!! 데이터가 없는것도 아니다.
알수없는 값 NULL과 의 연산은 무조건 NULL 값이 나온다 그래서 주의해야한다!!!(시험예정)**
--연산 시 NULL값이 연산되는 결과는 무조건 NULL 이다.
SELECT COUNT(EMPNO) FROM EMP; -- COUNT() 함수 사용:테이블의 갯수 세는 함수
SELECT SUM(SAL) FROM EMP;
--인센티브 평균 구하기.
SELECT SUM(COMM) / COUNT(COMM) AS AVG
FROM EMP;--95(틀린 답 나옴) >> NULL 값 카운트하지 않기 때문에 (연산X)
--NVL() 함수 : (괄호안은 컬럼명) NULL값을 원하는 값으로 조회시 사용
SELECT COMM,NVL(COMM,0)--'COMM에서 NULL값은 0 으로 조회하라.'(괄호 안 두개의 값이 들어감)
FROM EMP;
--인센티브 갯수 구하기(NULL값대체 후)
SELECT COUNT(NVL(COMM,0))
FROM EMP;
--인센티브의 평균 구하기
SELECT SUM(COMM)/COUNT(NVL(COMM,0))
FROM EMP;
--사번과 사원명을 연결하여 하나의 컬럼으로 조회
SELECT * FROM EMP;--EX)1001_김사랑 (하나의 컬럼으로)
SELECT EMPNO ||'_'|| ENAME 사번_사원명-- '||' 문자열 연결할때 사용한다(문자는 '' 홀따옴표에 감싼다)
FROM EMP;
--부서번호 조회하기
SELECT DEPTNO 부서번호
FROM EMP;
--부서번호 조회하기 중복값은 제외하고( DISTINCT )
SELECT DISTINCT DEPTNO 부서번호
FROM EMP;
--급여가 300에서 600사이고 커미션은 NULL인 사원의 모든 정보를 조회하라
SELECT *
FROM EMP
WHERE SAL >= 300 AND SAL<= 600
AND COMM IS NULL;
!!주의!! NULL 은 '='사용 X cf)IN NOT NULL
--'BETWEEN' 사용할 때
SELECT *
FROM EMP
WHERE SAL BETWEEN 300 AND 600
AND COMM IS NULL;
--급여가 300이거나 500이거나 600인 사원의 모든 정보조회
SELECT *
FROM EMP
WHERE SAL = 300
OR SAL = 500
OR SAL =600;
--- IN() 함수 사용할 때
SELECT *
FROM EMP
WHERE SAL IN(300,500,600);
--급여가 300이거나 500인 사원 중 COMM이 NULL인 사원의 모든 정보조회
SELECT *
FROM EMP
WHERE (SAL = 300 OR SAL= 500) AND COMM IS NULL;--주의!! ()괄호 넣어 우선순위 적용 반드시 필요함
--조회시 정렬하기
--사번조회하되 급여가 높은 순으로 조회
SELECT *
FROM EMP
ORDER BY SAL DESC;--내림차순 cf)ASC(오름차순) 안적으면 기본값
--순서 중요!! SELECT > FROM > WHERE >ORDER BY(정렬)
--모든 사원의 정보를 조회하되
--직급기준 오름차순으로 정렬하고 만약 직급이 같다면
--급여 기준 내림차순 정렬
SELECT *
FROM EMP
ORDER BY JOB, SAL DESC;--정렬에서 문자연결은 ','쉼표 사용
- DB와 자바 연결 (MYDB.SQL 워크시트)
CREATE TABLE BOARD (
BOARD_NUM NUMBER PRIMARY KEY
, TITLE VARCHAR2(100) NOT NULL
, WRITER VARCHAR2(50) NOT NULL
, CONTENT VARCHAR2(100)
, READ_CNT NUMBER DEFAULT 0
, CREATE_DATE DATE DEFAULT SYSDATE
);
--참고-----------------------
SELECT * FROM EMP;
SELECT 1 + 1 FROM EMP; -- 1+1의 합계 '2'값이 테이블의 행갯수만큼 나온다.
SELECT 1 + 1 FROM DUAL; -- 1+1의 합계 '2'값이 하나만 나온다.
SELECT SYSDATE FROM DUAL; --오늘 날짜 조회
--참고---------------------------
INSERT INTO BOARD (BOARD_NUM,TITLE,WRITER)
VALUES (3,'제목1','김자바');
SELECT * FROM BOARD;
DELETE BOARD;--테이블 데이터 지우기
--다음에 들어갈 글번호를 조회
SELECT MAX(BOARD_NUM) + 1 FROM BOARD;--MAX() 함수 사용하기 가장 큰값 조회
INSERT INTO BOARD (--자동으로 글번호가 +1 갱신되기때문에 계속 삽입가능하다.
BOARD_NUM
, TITLE
, CONTENT
, WRITER
) VALUES (
(SELECT MAX(BOARD_NUM) + 1 FROM BOARD)
, '제목5'
, '내용입니다.'
, '홍길동'
);DELETE 테이블 실행 후 BOARD삽입 [실행 결과]
오류 보고 -
ORA-01400: NULL을 ("MYDB"."BOARD"."BOARD_NUM") 안에 삽입할 수 없습니다.
왜?(SELECT MAX(BOARD_NUM) + 1 FROM BOARD)이 NULL값이기 때문에 처음에는 게시글이 없어서(데이터가 하나도 없을 때)
SELECT MAX(BOARD_NUM) + 1 FROM BOARD; 도 NULL값이다. 연산에서 NULL들어가면 무조건 결과는 NULL값이다 0이 절대 아니다!!!
(문제) 게시글이 하나도 없을 때는 글번호를 1로 조회하라.
--MAX(BOARD_NUM) 값이 NULL값이어서 NULL이뜬것이 문제였기 때문에
--NULL을 0값으로 변경하여 조회하도록 조회하면 해결된다.(NVL()함수 사용)
SELECT NVL(MAX(BOARD_NUM),0) + 1
FROM BOARD;
- 디비-자바연결 게시판만들기
프로젝트명: BasicBoard_0801
①기본셋팅
-taglibs 파일 모두 넣기
-index 파일 만들기
-클래스dto 만들기(창고,통)
-클래스dao 만들기(디비연결)
-컨트롤러만들기
-게시판 리스트(목록)페이지만들기
<index파일 만들기>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:forward page="boardList.bo"></jsp:forward>
</body>
</html>
<dto만들기>
package dto;
public class BoardDTO {//변수명은 반드시 컬럼명과 맞춰준다!!
private String title;
private String writer;
private String createDate;
private String content;
private int readCnt;
private int boardNum;
public BoardDTO() {};
public BoardDTO(String title, String writer, String createDate, int readCnt, int boardNum) {
super();
this.title = title;
this.writer = writer;
this.createDate = createDate;
this.readCnt = readCnt;
this.boardNum = boardNum;
}
public BoardDTO(String title, String writer, String createDate, String content, int readCnt, int boardNum) {
super();
this.title = title;
this.writer = writer;
this.createDate = createDate;
this.content = content;
this.readCnt = readCnt;
this.boardNum = boardNum;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getCreateDate() {
return createDate;
}
public void setCreateDate(String createDate) {
this.createDate = createDate;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getReadCnt() {
return readCnt;
}
public void setReadCnt(int readCnt) {
this.readCnt = readCnt;
}
public int getBoardNum() {
return boardNum;
}
public void setBoardNum(int boardNum) {
this.boardNum = boardNum;
}
}
-----
<컨트롤러 만들기>
package controller;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
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 dao.BoardDAO;
import dto.BoardDTO;
@WebServlet("*.bo")
public class BoardController extends HttpServlet {
private static final long serialVersionUID = 1L;
private BoardDAO boardDAO;// ⓐ변수만 선언한다.
private List<BoardDTO> boardList = new ArrayList<>();
private BoardDTO board;
public BoardController() {
boardDAO = new BoardDAO();//ⓑ생성자에서 초기화 선언
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request, response);
}
protected void doProcess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//한글 인코딩 처리
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
String requestURI = request.getRequestURI();
String contextPath = request.getContextPath();
String command = requestURI.substring(contextPath.length());
System.out.println("command = " + command);
String page = "";
boolean isRediect = false;
//게시글 목록페이지로 이동
if(command.equals("/boardList.bo")) {
List<BoardDTO> boardList = boardDAO.selectBoardList();//객체명.메소드명()
//--> boardDAO.selectBoardList();는 자료형 리턴값List<BoardDTO> 으로 나에게 던져서 돌려준다.
//화면에 보여주기위해 돌려줄 때 데이터 던져주기!!
request.setAttribute("boardList", boardList);
page="board_list.jsp";
}
//글쓰기 등록 양식페이지이동
else if(command.equals("/regboardForm.bo")) {
page="reg_board_form.jsp";
}
//실제 글쓰기 등록페이지
else if(command.equals("/regboard.bo")) {
String title = request.getParameter("title");
String writer = request.getParameter("writer");
String content = request.getParameter("content");
String createDate = request.getParameter("createDate");
int readCnt =Integer.parseInt(request.getParameter("readCnt"));
int boardNum =Integer.parseInt(request.getParameter("boardNum"));
BoardDTO board = new BoardDTO();
board.setTitle(title);
board.setWriter(writer);
board.setContent(content);
board.setCreateDate(createDate);
board.setReadCnt(readCnt);
board.setBoardNum(boardNum);
boardDAO.insertBoard(board);
page= "insert_result.jsp";
}
else if(command.equals("/regboard.bo")) {
page="board_detail.jsp";
}
else if(command.equals("/regboard.bo")) {
page="board_detail.jsp";
}
//페이지이동에 사용되는 if문
if(isRediect) {
response.sendRedirect(page);
}
else {
RequestDispatcher dispatcher = request.getRequestDispatcher(page);
dispatcher.forward(request, response);
}
}
}
<dao만들기>
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import javax.naming.spi.DirStateFactory.Result;
import dto.BoardDTO;
public class BoardDAO {
//자바와 디비 연결할 객체
private Connection conn;
//쿼리를 실행할 객체
private PreparedStatement stmt;
//조회 결과 데이터를 저장할 객체(SELECT에서만 사용)
private ResultSet rs;
//쿼리문
private String sql;
private BoardDTO result;
//게시글 목록 조회
public List<BoardDTO> selectBoardList() {//마지막 리턴값의 자료형대로 VOID 바꿔야한다.
List<BoardDTO> BoardList = new ArrayList<>();//자료형 변수명 :List<BoardDTO> BoardList
//①쿼리문 작성하기
sql="SELECT BOARD_NUM,TITLE,WRITER,CONTENT,READ_CNT,CREATE_DATE FROM BOARD ORDER BY BOARD_NUM DESC";
//② 트라이캐치문 만들기
try {
conn = JDBCUtil.getConnection();
//stmt = conn.prepareStatement(sql);
//③ 쿼리실행 선택
rs = stmt.executeQuery();//조회할때 select문
//쿼리를 사용해서 조회한 데이터를 반복문사용해서 데이터룰 담아놓은 객체명RS(조회된 모든 데이터가지고있음) 에 데이터를 모두 담기.
//한줄 한줄 데이터를 담기위해 반복문을 사용하는 것
//그래서 한줄만 나오는 데이터는 반복문 필요 없다!!!
while(rs.next()) {//while괄호안은 조건이 맞으면 참거짓 >> rs에 다음데이터가 있습니까?
//왜 리스트를 사용하는가?
//테이블 한 줄에는 여러 문자열을 가진 데이터들을 가지고 있다.
//그래서 문자열 배열리스트가 필요하다.
//이 한줄 한줄을 클래스dto(창고,통)라는 통에 담는다.
//그래서 클래스dto자료형의 배열리스트를 만든다.
BoardDTO Board = new BoardDTO();
int boardNum = rs.getInt("BOARD_NUM");//데이터가져올때 객체명.get사용하기!!! 반드시 조회한 컬럼명으로 넣는다.
String title = rs.getString("TITLE");
String writer =rs.getString("WRITER");
String createDate =rs.getString("CREATE_DATE");
int readCnt =rs.getInt("READ_CNT");
// 각 데이터를 넣을 때 set사용하기.
Board.setBoardNum(boardNum);
Board.setTitle(title);
Board.setWriter(writer);
Board.setCreateDate(createDate);
Board.setReadCnt(readCnt);
//위에 둘을 한줄로 사용할 때,
//Board.setBoardNum(rs.getInt("BOARD_NUM"));
//-->RS(디비)에서 조회한 데이터 한줄한줄을 BOARD리스트(자바)에 모두 넣는다.
BoardList.add(Board);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(rs, stmt, conn);
}
return BoardList; //멤버리스트에 조회된 모든 데이터 가지고있어서 리턴메소드사용.
//리턴은 항상 마지막에 사용한다. 리턴사용하는 이유? 담은 데이터들을 사용할테니 나에게 던져줘라
}
//게시글 등록 메소드 만들기
public void insertBoard(BoardDTO board) {
sql = "INSERT INTO BOARD (BOARD_NUM,TITLE,WRITER,CONTENT,READ_CNT,CREATE_DATE) VALUES (?, ?, ?, ?, ?, ?)";
try {
conn = JDBCUtil.getConnection();
stmt = conn.prepareStatement(sql);
//?값 세팅하기(쿼리실행전!)
stmt.setInt(1,board.getBoardNum() );
stmt.setString(2, board.getTitle());
stmt.setString(3, board.getWriter());
stmt.setString(4, board.getContent());
stmt.setInt(5, board.getReadCnt());
stmt.setString(6, board.getCreateDate());
//쿼리실행(실제)
stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(rs, stmt, conn);
}
}
//[회원 상세 조회 기능 만들기]
public BoardDTO selectBoardDetail(String title) {
BoardDTO result = new BoardDTO();//멤버dto가 담겨질 통 하나 만들기
sql ="SELECT BOARD_NUM"
+ ", TITLE"
+ ", WRITER"
+ ", CONTENT"
+ ", READ_CNT"
+ ", CREATE_DATE"
+ "WHERE TITLE = ?";
try {
conn = JDBCUtil.getConnection();
stmt = conn.prepareStatement(sql);
stmt.setString(1,title);
stmt.executeQuery();//디비에서 데이터 조회된다.
rs =stmt.executeQuery();//select 조회는 rs로 데이터 받아올수있다.
//리스트는 필요없다 -> 쿼리실행시 한줄만 나오기때문에!!
//하지만 데이터 매개변수 하나하나 담을 필요가 필요해서 BoardDTO 한 개 필요하다.
// (전체가 쓸수있게 맨위에만들기)
// BoardDTO result = new BoardDTO();//멤버dto가 담겨질 통 하나 만들기
while (rs.next()) {
result.setTitle(rs.getString("title"));//rs db에서 뺀 값"id"를 멤버dto의 rs안에 id값 넣는다.
result.setWriter(rs.getString("writer"));//rs db에서 뺀 값"id"를 멤버dto의 rs안에 id값 넣는다.
result.setContent(rs.getString("content"));
result.setCreateDate(rs.getString("createDate"));
result.setBoardNum(rs.getInt("boardNum"));
result.setReadCnt(rs.getInt("readCnt"));
}//결국 디비에서 빼낸 모든 값들을 result가 담고있다!!!
//그래서 마지막 리턴시켜야한다.
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.close(rs, stmt, conn);
}
return result;
}
}
②
③
④
⑤
⑥
Dev.Vinch