달란트(Talanton)

-- 은노기 shoppingmall 프로젝트
create database jsptest;
create user 'jspid'@'localhost' identified by 'jsppass';
create user 'jspid'@'%' identified by 'jsppass';
grant all privileges on jsptest.* to 'jspid'@'localhost';
grant all privileges on jsptest.* to 'jspid'@'%';
commit;

create table member(
  id varchar(50) not null primary key,
  passwd varchar(16) not null,
  name varchar(10) not null,
  reg_date datetime not null
);

alter table member
     add (address varchar(100) not null,
          tel varchar(20) not null);
          
desc member;

insert into member(id, passwd, name, reg_date, address, tel) 
values('kingdora@dragon.com','1234','김개동', now(), '서울시', '010-1111-1111');

insert into member(id, passwd, name, reg_date, address, tel) 
values('hongkd@aaa.com','1111','홍길동', now(), '경기도', '010-2222-2222');

select * from member;

alter table member modify passwd varchar(60) not null;

create table board(
  num int not null primary key auto_increment,
  writer varchar(50) not null,
  subject varchar(50) not null,
  content text not null,
  passwd varchar(60) not null,
  reg_date datetime not null,
  ip varchar(30) not null,
  readcount int default 0,
  ref int not null,
  re_step smallint not null,
  re_level smallint not null
);

desc board;

--쇼핑몰

create table manager(
 managerId varchar(50) not null primary key,
 managerPasswd varchar(60) not null
);

insert into manager(managerId,managerPasswd)
values('bookmaster@shop.com','123456');

insert into manager(managerId,managerPasswd)
values('ksseo63@naver.com','ekffksxm0');

create table book(
  book_id int not null primary key auto_increment,
  book_kind varchar(3) not null,
  book_title varchar(100) not null,
  book_price int not null,
  book_count smallint not null,
  author varchar(40) not null,
  publishing_com varchar(30) not null,
  publishing_date varchar(15) not null,
  book_image varchar(16) default 'nothing.jpg',
  book_content text not null,
  discount_rate tinyint default 10,
  reg_date datetime not null
);

create table qna(
  qna_id int not null primary key auto_increment,
  book_id int not null,
  book_title varchar(100) not null,
  qna_writer varchar(50) not null,
  qna_content text not null,
  group_id int not null,
  qora tinyint not null,
  reply tinyint default 0,
  reg_date datetime not null
);

create table bank(
  account varchar(30) not null,
  bank varchar(10) not null,
  name varchar(10) not null
);

insert into bank(account, bank, name)
values('11111-111-11111','내일은행','오내일');

create table cart(
  cart_id int not null primary key auto_increment,
  buyer varchar(50) not null,
  book_id int not null,
  book_title varchar(100) not null,
  buy_price int not null,
  buy_count tinyint not null,
  book_image varchar(16) default 'nothing.jpg'
);

create table buy(
  buy_id bigint not null,
  buyer varchar(50) not null,
  book_id varchar(12) not null,
  book_title varchar(100) not null,
  buy_price int not null,
  buy_count tinyint not null,
  book_image varchar(16) default 'nothing.jpg',
  buy_date datetime not null,
  account varchar(50) not null,
  deliveryName varchar(10) not null,
  deliveryTel varchar(20) not null,
  deliveryAddress varchar(100) not null,
  sanction varchar(10) default '상품준비중'
);