jnjosjk0965
commented
9 months ago use traveldb;
drop table user;
CREATE TABLE user ( userEmail VARCHAR(64) NOT NULL, userPwd VARCHAR(32) NOT NULL, userNName VARCHAR(50), -- 유저 닉네임 userEName VARCHAR(50), -- 유저 영어이름 userPassport VARCHAR(10), -- 여권번호 userCountry VARCHAR(20), -- 국가/지역 userBirth DATE, -- 생년월일 isAdmin boolean default false, PRIMARY KEY (userEmail) -- 기본키 : 이메일 ); drop table reservation; drop table flight;
CREATE TABLE Hotel ( hotelId VARCHAR(8) NOT NULL , -- api에서 가져옴 hotelName VARCHAR(50) NOT NULL , latitude FLOAT , longitude FLOAT , PRIMARY KEY ( hotelId ) ); CREATE TABLE Flight ( flightId VARCHAR(22) NOT NULL, -- yymmdd + airlinecode + 3digit x 2 flightPrice INTEGER NOT NULL, outboundflightNo varchar(3) not null, outboundSeatClass VARCHAR(20) NOT NULL, outboundAirline CHAR(2) NOT NULL, outboundDuration VARCHAR(8), outboundDepartureTime varchar(20), outboundDepartureAirport CHAR(3), outboundArrivalTime varchar(20), outboundArrivalAirport CHAR(3), returnflightNo varchar(3) not null, returnSeatClass VARCHAR(20) NOT NULL, returnAirline CHAR(2) NOT NULL, returnDuration VARCHAR(8), returnDepartureTime varchar(20), returnDepartureAirport CHAR(3), returnArrivalTime varchar(20), returnArrivalAirport CHAR(3), PRIMARY KEY (flightId) );
CREATE TABLE room ( roomId VARCHAR(100) NOT NULL , -- api에서 가져옴 checkInDate DATE , checkOutDate DATE , category VARCHAR(30) , beds INTEGER , bedType VARCHAR(20) , hotelId VARCHAR(8) NOT NULL , hotelPrice INTEGER , PRIMARY KEY ( roomId ) ); CREATE TABLE reservation ( resvId INTEGER NOT NULL AUTO_INCREMENT, resvDate DATE DEFAULT (CURRENT_DATE) , userEmail VARCHAR(64) NOT NULL , flightId VARCHAR(12) NOT NULL , roomId VARCHAR(100) NOT NULL , adults INTEGER , children INTEGER , PRIMARY KEY ( resvId ), FOREIGN KEY (flightId) REFERENCES flight(flightId), FOREIGN KEY (roomId) REFERENCES room(roomId) );
select * from user;
insert into user(userEmail, userPwd, isAdmin) values('admin','admin',true);
스크립트 내용 use traveldb;
drop table user; truncate table user;
CREATE TABLE user ( userEmail VARCHAR(64) NOT NULL, userPwd VARCHAR(32) NOT NULL, userNName VARCHAR(50), -- 유저 닉네임 userEName VARCHAR(50), -- 유저 영어이름 userPasssport VARCHAR(10), -- 여권번호 userCountry VARCHAR(20), -- 국가/지역 userBirth DATE, -- 생년월일 isAdmin boolean default false, PRIMARY KEY (userEmail) -- 기본키 : 이메일 );
select * from user;