조인 : 테이블이 2개의 자료들을 검색
조인 : 2개의 테이블에서 행은 곱하고
열은 더해서 2개의 테이블에서 검색
조인종류
내부조인(INNER) = join
: 동등조인 : 연산후 필드값이 동일한 것만 취하는것
: 자연조인 : 내부조인의 결과에서 중복된 필드를 제거
외부조인(OUTNER)
: 왼쪽외부 : 내부조인의 결과에 왼쪽의 값중에서 내부조인에 없는 값을 추가
: 오른쪽외부 : 내부조인의 결과에 오른쪽의 값중에서 내부조인에 없는 값을 추가
: 전체외부
중첩질의 : 질의문이 2개 있다
select 필드명 ...주질의문
from 테이블명
where 필드명 =(select 필드명 ...서브질의문
from 테이블명
where 조건);
반드시 서브질의문이 먼저 실행되어야 한다
select *
from aaa a, bbb b
where a.phone = b.phone;
--내부조인
select *
from aaa a join bbb b
on a.phone = b.phone;
--자연조인
select *
from aaa a NATURAL join bbb b;
select *
from aaa a LEFT join bbb b
on a.phone = b.phone;
--11번
select en.grade
from enrol en
where en.sno=(select st.sno
from student st
where st.sname='나연묵');
select en.grade
from student st, enrol en
where st.sno = en.sno and st.sname='나연묵';
select en.grade
from student st join enrol en
on st.sno = en.sno
where st.sname='나연묵';
select (en.mid+en.fin) tot
from student st, enrol en
where st.sno = en.sno and st.sname='나연묵';
--12번
select *
from student
where year!=4;
--13번
select *
from student
where sname like '%찬%';
--14번
select *
from student
where sname like '_찬_';
--15번
select *
from student
where sname like '__묵';
--16번
select st.sno,st.sname,en.cno,en.grade
from student st, enrol en
where st.sno = en.sno and st.sno='100';
select st.sno,st.sname,en.cno,en.grade
from student st join enrol en
on st.sno = en.sno
where st.sno='100';
select st.sno,st.sname,en.cno,en.grade
from student st join enrol en
on st.sno = en.sno and st.sno='100';
--17번
select st.sno,st.sname,en.cno,en.grade,en.mid,en.fin
from student st, enrol en
where st.sno = en.sno and st.sname='나연묵';
--18번
select count(*)
from enrol
where grade='A';
--19번
select sum(mid) midsum,sum(fin) finsum,round(avg(mid),1) midavg,round(avg(fin),1) finavg
from enrol
where sno='300';
--20번
select st.sno,st.sname,count(st.sno) 과목수
from student st,enrol en
where st.sno = en.sno
group by st.sno,st.sname
order by st.sno asc;
--21---------------------------------------
select co.prname
from enrol en, course co
where en.cno = co.cno and en.sno ='100';
select co.prname
from enrol en join course co
on en.cno = co.cno
where en.sno ='100';
select co.prname
from course co
where cno=(select cno
from enrol
where sno='100');
--22---------------------------------------
--학생 테이블에서 박종화 학생의 중간,기말 고사의 성적을 검색
select en.mid,en.fin
from student st, enrol en
where st.sno=en.sno and st.sname='박종화';
--23---------------------------------------
--학생테이블의 이찬영 학생이 수강신청과목을 검색
select co.cname
from student st, course co, enrol en
where en.cno = co.cno and st.sno = en.sno and st.sname='이찬영';
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<form action="save.jsp" method="post">
<table border="1" align="center">
<tr>
<th>날짜</th>
<td><input type="date" name="day"></td>
</tr>
<tr>
<th>도서명</th>
<td><input type="text" name="bname"></td>
</tr>
<tr>
<th>저자</th>
<td><input type="text" name="writer"></td>
</tr>
<tr>
<th>가격</th>
<td><input type="text" name="price"></td>
</tr>
<tr>
<th>내용</th>
<td>
<textarea rows="10" cols="25" name="bigo">
</textarea>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="전송">
<input type="reset" value="초기화">
</td>
</tr>
</table>
</form>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@page import="day12_db1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%request.setCharacterEncoding("utf-8");%>
<jsp:useBean id="dto" class="day12_db1.DTO"/>
<jsp:setProperty property="*" name="dto"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
DAO dao = new DAO();
dao.insert(dto);
response.sendRedirect("index.jsp");
%>
</body>
</html>
<%@page import="java.sql.ResultSet"%>
<%@page import="day12_db1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<%
DAO dao = new DAO();
ResultSet rs = dao.out();
%>
<table border="1" align="center" width="800px">
<tr>
<th>날짜</th><th>도서명</th><th>저자</th><th>가격</th>
<th>내용</th>
</tr>
<%
while(rs.next()) {
String day = rs.getString(1);
//day=day.substring(0,10);
String bname = rs.getString(2);
String writer = rs.getString(3);
int price = rs.getInt(4);
String bigo = rs.getString(5);
%>
<tr>
<td><%=day%></td><td><%=bname%></td><td><%=writer%></td>
<td><%=price%></td><td><%=bigo%></td>
</tr>
<%
}
%>
</table>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<form action="searchsave.jsp">
<table border="1" align="center">
<tr>
<th colspan="2">검색키</th>
</tr>
<tr>
<td>
<select name="sname">
<option value="bname">도서명
<option value="writer">저자
<option value="bigo">내용
</select>
</td>
<td>
<input type="text" name="svalue">
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="검색">
<input type="reset" value="초기화">
</td>
</tr>
</table>
</form>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@page import="java.sql.ResultSet"%>
<%@page import="day12_db1.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<%!
String sname,svalue;
%>
<%
request.setCharacterEncoding("utf-8");
sname=request.getParameter("sname");
svalue=request.getParameter("svalue");
DAO dao = new DAO();
ResultSet rs = dao.search2(sname, svalue);
%>
<table border="1" align="center" width="800px">
<tr>
<th>날짜</th><th>도서명</th><th>저자</th><th>가격</th>
<th>내용</th>
</tr>
<%
while(rs.next()) {
String day = rs.getString(1);
String bname = rs.getString(2);
String writer = rs.getString(3);
int price = rs.getInt(4);
String bigo = rs.getString(5);
%>
<tr>
<td><%=day%></td><td><%=bname%></td><td><%=writer%></td>
<td><%=price%></td><td><%=bigo%></td>
</tr>
<%
}
%>
</table>
</section>
<jsp:include page="footer.jsp"/>
</html>
package day12_db1;
public class DTO {
//날짜 도서명 저자 가격 내용
//검색 기능 도서명,저자,내용
String day,bname,writer;
int price;
String bigo;
public DTO() {}
public DTO(String day, String bname, String writer, int price, String bigo) {
super();
this.day = day;
this.bname = bname;
this.writer = writer;
this.price = price;
this.bigo = bigo;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String getBname() {
return bname;
}
public void setBname(String bname) {
this.bname = bname;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getBigo() {
return bigo;
}
public void setBigo(String bigo) {
this.bigo = bigo;
}
}
package day12_db1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DAO {
Connection con;
PreparedStatement psmt;
ResultSet rs;
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
public DAO() throws ClassNotFoundException {
Class.forName(driver);
try {
con = DriverManager.getConnection(url, "ezen", "12345");
} catch (SQLException e) {
e.printStackTrace();
}
}
public void insert(DTO dto) {
String day = dto.getDay();
String bname = dto.getBname();
String writer = dto.getWriter();
int price = dto.getPrice();
String bigo = dto.getBigo();
String sql = "insert into db01311 values (?,?,?,?,?)";
try {
psmt = con.prepareStatement(sql);
psmt.setString(1, day);
psmt.setString(2, bname);
psmt.setString(3, writer);
psmt.setInt(4, price);
psmt.setString(5, bigo);
psmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public ResultSet out() {
String sql = "select to_char(day,'yy-MM-dd'),bname,writer, "
+ "price,bigo from db01311";
try {
psmt = con.prepareStatement(sql);
rs = psmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public ResultSet search(String sname,String svalue) {
String sql = "select * from db01311 ";
if(sname.equals("bname")) {
sql+= " where bname like ?";
}
else if(sname.equals("writer")) {
sql+= " where writer like ?";
}
else {
sql+= " where bigo like ?";
}
try {
psmt = con.prepareStatement(sql);
psmt.setString(1, '%'+svalue+'%');
rs = psmt.executeQuery();
System.out.println(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public ResultSet search2(String sname,String svalue) {
String sql = "select * from db01311 where "+sname+" like ?";
try {
psmt = con.prepareStatement(sql);
psmt.setString(1, '%'+svalue+'%');
rs = psmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
}
댓글