본문 바로가기
JSP

230118_DB

by 경 훈 2023. 1. 18.

DataBase : 자료 저장소

관계형 DB

-> 행, 열

 

DBMS : 데이터베이스 관리시스템

ex) oracle , MySQL

 

쿼리 언어 : 질의어 (Query language)

 

정의어 : 테이블 관련

   create : 생성

   alter : 구조 추가, 삭제

   drop : 삭제

 

조작어 : 데이터 레코드 단위

출력 :  select * from 테이블명 where 조건

삽입 :  insert into 테이블명 values()

삭제 :  delete from 테이블명 where

갱신 :  update 테이블명 set 식 where

 

CRUD

삽입 insert ------> Create

검색(출력) select -------> Read

갱신 update ------> Update

삭제 delete ------> Delete

 

Oracle 사용방식

1.CLI -> Commeand Line Interface

2.CGI -> Command Graphic Interface

 

SQL> create table score(
  2  name varchar2(10),
  3  kor number(3));
  
  
SQL> select * from tab;

SQL> select * from score;
SQL> select * from score where kor>70;
SQL> insert into score values('홍길동',60);
SQL> delete from score where name='김김김';
SQL> update score set kor=60 where name='광교산';

* 컬럼 추가
SQL> alter table score add eng number(3);

SQL> update score set eng=50;

SQL> alter table score add tot number(3);
SQL> update score set tot=kor+eng;

* 컬럼 삭제
SQL> alter table myinfo drop column phone;

 

insert INTO score(name,kor,eng,mat) VALUES('홍길동',56,45,89);
UPDATE score SET tot=kor+eng+mat;
update score set avg=tot/3;
select * from score;
<%@ 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>
	<br><br><br><br>
	<div align="center">
		<a href="scoreForm.jsp"><input type="button" value="자료입력"></a>
		<a href="scoreout.jsp"><input type="button" value="자료출력"></a><br>
	</div>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
th,td {
	text-align: center;
}

</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="scoresave.jsp" method="post">
		<table border="1" align="center">
			<caption>회원정보입력</caption>
			<tr>
				<th>이름</th>
				<td><input type="text" name="name"></td>
			</tr>
			<tr>
				<th>국어</th>
				<td><input type="text" name="kor"></td>
			</tr>
			<tr>
				<th>영어</th>
				<td><input type="text" name="eng"></td>
			</tr>
			<tr>
				<th>수학</th>
				<td><input type="text" name="mat"></td>
			</tr>
			<tr>
				<td colspan="3" align="center"><input type="submit" value="전송">
				<input type="reset" value="초기화">
				<a href="scoreMenu.jsp"><input type="button" value="메뉴"></a>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
	<%!
	String name;
	int kor,eng,mat,tot;
	double avg;
	Connection con;//연결객체
	PreparedStatement psmt;//쿼리 실행문
	ResultSet rs;//결과저장 객체
	//드라이버연결
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	<%
	request.setCharacterEncoding("utf-8");
	name=request.getParameter("name");
	kor=Integer.parseInt(request.getParameter("kor"));
	eng=Integer.parseInt(request.getParameter("eng"));
	mat=Integer.parseInt(request.getParameter("mat"));
	tot=kor+eng+mat;
	avg=(double)tot/3;
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql="insert into score values(?,?,?,?,?,?)";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, name);
	psmt.setInt(2, kor);
	psmt.setInt(3, eng);
	psmt.setInt(4, mat);
	psmt.setInt(5, tot);
	psmt.setDouble(6, avg);
	int n = psmt.executeUpdate();
	if(n==1)
	{
		response.sendRedirect("scoreMenu.jsp");
	}
	%>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
th,td {
	text-align: center;
}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String name;
	int kor,eng,mat,tot;
	double avg;
	Connection con;//연결객체
	PreparedStatement psmt;//쿼리문 실행객체
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql = "select * from score";
	psmt=con.prepareStatement(sql);
	rs = psmt.executeQuery();
	%>
	<table border="1" align="center" width="600px">
		<tr>
			<th>이름</th><th>국어</th><th>영어</th>
			<th>수학</th><th>총점</th><th>평균</th>
		</tr>
		<%
		while(rs.next()) {
			name = rs.getString("name");
			kor = rs.getInt("kor");
			eng = rs.getInt("eng");
			mat = rs.getInt("mat");
			tot = rs.getInt("tot");
			avg = rs.getDouble("avg");
		%>
		<tr>
			<td><%=name%></td><td><%=kor%></td><td><%=eng%></td>
			<td><%=mat%></td><td><%=tot%></td><td><%=avg%></td>
		</tr>
		<%
		}
		%>
	</table>
	<div align="center">
		<a href="scoreMenu.jsp"><input type="button" value="메뉴"></a>
	</div>
</body>
</html>
<%@ 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>
	<br><br><br><br><br>
	<div align="center">
		<a href="form.jsp"><input type="button" value="입력"></a>
		<a href="out.jsp"><input type="button" value="출력"></a>
	</div>
</body>
</html>

 

<%@ 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>
	<form action="save.jsp" method="post">
		<table border="1" align="center">
			<tr>
				<th>아이디</th>
				<td><input type="text" name="id"></td>
			</tr>
			<tr>
				<th>패스워드</th>
				<td><input type="password" name="pw"></td>
			</tr>
			<tr>
				<th>전화번호</th>
				<td><input type="text" name="phone"></td>
			</tr>
			<tr>
				<th>나이</th>
				<td><input type="text" name="age"></td>
			</tr>
			<tr>
				<td colspan="3" align="center"><input type="submit" value="전송">
				<input type="reset" value="초기화">
				<a href="menu.jsp"><input type="button" value="메뉴"></a>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
	<%
	Connection con;//연결객체
	PreparedStatement psmt;//쿼리 실행문
	ResultSet rs;//결과저장 객체
	//드라이버연결
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	request.setCharacterEncoding("utf-8");
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	%>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
	<%!
	String id,pw,phone;
	int age;
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	id=request.getParameter("id");
	pw=request.getParameter("pw");
	phone=request.getParameter("phone");
	age=Integer.parseInt(request.getParameter("age"));
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql="insert into sample1 values(?,?,?,?)";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, id);
	psmt.setString(2, pw);
	psmt.setString(3, phone);
	psmt.setInt(4, age);
	int n = psmt.executeUpdate();
	if(n==1)
	{
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>

 

<%@ include file= "dbcon.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String id,pw,phone;
	int age;
	%>
	
	<%
	String sql = "select * from sample1";
	psmt=con.prepareStatement(sql);
	rs = psmt.executeQuery();
	%>
	<table border="1" align="center" width="600px">
		<tr>
			<th>아이디</th><th>비밀번호</th><th>전화번호</th><th>나이</th>
		</tr>
		<%
		while(rs.next()) {
			id = rs.getString("id");
			pw = rs.getString("pw");
			phone = rs.getString("phone");
			age = rs.getInt("age");
		%>
		<tr>
			<td><%=id%></td><td><%=pw%></td>
			<td><%=phone%></td><td><%=age%></td>
		</tr>
		<%
		}
		%>
	</table>
	<div align="center">
		<a href="menu.jsp"><input type="button" value="메뉴"></a>
	</div>
</body>
</html>

'JSP' 카테고리의 다른 글

230120_DB  (0) 2023.01.20
230119_DB  (0) 2023.01.19
230117_기본  (0) 2023.01.17
230116_CSS  (1) 2023.01.16
230113_HTML  (0) 2023.01.13

관련글

댓글

티스토리툴바