JDBC_개념_Day_08

-동기 (내가 끝날떄까지 못해)

-비동기 (같이하자)

 

비동기 통신AJAX

 

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

‘구조’를 잡고 시작하는것이 중요합니다.

시작화면 : index.jsp

헤더 : header.jsp

footer : footer.jsp

 

회원등록 :  memberInsert.jsp

      등록 : dbInsert.jsp             

      조회 : listSelectUpdate.jsp   

 

회원목록 조회/수정 : listSelectUpdate.jsp    

 

회원매출조회  : saleSelect.jsp

 

회원정보수정 : update.jsp    

      수정 :  dbupdate.jsp    

      조회 : listSelectUpdate.jsp    

 

홈으로 :  index.jsp

회원정보 테이블 

          회원번호 custno

      회원성명 custname

      연락처   phone

      주소     address

      가입일자 joindate

      고객등급 grade

      도시코드 city

 

회원매출정보  테이블

      회원번호  cutno

      판매번호  salenol

      단가      pcost

      수량      amount

      가격      price

      상품코드  pcode

      판매일자  sdate

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

 

◐shopdb 

 

◐1◐memberInsert.jsp

◐2◐memberInsert.js

◐3◐memberInsert.js

 

◐mysql _ 20240227_JDBC_tea

drop database if exists shopdb;

create database shopdb;

use shopdb;

 

drop table if exists member_tbl_02;

create table member_tbl_02(

   custno int primary key auto_increment,

    custname varchar(20),

    phone varchar(13),

    address varchar(60),

    joindate date,

    grade char(1),

    city char(2)

);

 

select * from member_tbl_02;

alter table member_tbl_02 auto_increment = 100001;

 

insert into member_tbl_02 values (null,'김행복','010-1111-2222','서울 동대문구 휘경1동', '20151202','A','01');

insert into member_tbl_02 values (null,'이축복','010-1111-3333','서울 동대문구 휘경2동', '20151206','B','01');

insert into member_tbl_02 values (null,'장믿음','010-1111-4444','울릉군 울릉읍 독도1리', '20151001','B','30');

insert into member_tbl_02 values (null,'최사랑','010-1111-5555','울릉군 울릉읍 독도2리', '20151113','A','30');

insert into member_tbl_02 values (null,'진평화','010-1111-6666','제주도 제주시 외나무골', '20151225','B','60');

insert into member_tbl_02 values (null,'차공단','010-1111-7777','제주도 제주시 감나무골', '20151211','C','60');

drop table if exists money_tbl_02;

create table money_tbl_02(

   cutno int not null,

    salenol int not null,

    pcost int,

    amount int,

    price int,

    pcode varchar(4),

    sdate date,

    primary key(cutno, salenol)

);

select * from money_tbl_02;

 

insert into money_tbl_02 values ( 100001,20160001,500,5,2500,'A001','20160101');

insert into money_tbl_02 values ( 100001,20160002,1000,4,4000,'A002','20160101');

insert into money_tbl_02 values ( 100001,20160003,500,3,1500,'A008','20160101');

insert into money_tbl_02 values ( 100002,20160004,2000,1,2000,'A004','20160102');

insert into money_tbl_02 values ( 100002,20160005,500,1,500,'A001','20160103');

insert into money_tbl_02 values ( 100003,20160006,1500,2,3000,'A003','20160103');

insert into money_tbl_02 values ( 100004,20160007,500,2,1000,'A001','20160104');

insert into money_tbl_02 values ( 100004,20160008,300,1,300,'A005','20160104');

insert into money_tbl_02 values ( 100004,20160009,600,1,600,'A006','20160104');

insert into money_tbl_02 values ( 100004,20160010,3000,1,3000,'A007','20160106');

-- 이렇게 나오게 하기위해, jsp에다가 이 쿼리문을 사용하면 됩니다.

select mb.custno, mb.custname, mb.grade, sum(mo.price) as price

from member_tbl_02 as mb

inner join money_tbl_02 as mo

on mb.custno = mo.cutno

group by mb.custno, mb.custname, mb.grade

order by sum(mo.price) desc; -- 내림차순정렬