?? mysql.sql
字號:
DROP database IF exists auction;
CREATE database auction;
use auction;
--用戶表
create table auction_user(
user_id int(11) auto_increment,
username varchar(50) not null,
userpass varchar(50) not null,
email varchar(100) not null,
primary key(user_id),
unique(username)
);
INSERT INTO auction_user (username,userpass,email) VALUES ('tomcat','tomcat','spring_test@163.com');
INSERT INTO auction_user (username,userpass,email) VALUES ('mysql','mysql','spring_test@163.com');
--物品種類表
create table kind(
kind_id int(11) auto_increment,
kind_name varchar(50) not null,
kind_desc varchar(255) not null,
primary key(kind_id)
);
INSERT INTO kind (kind_name,kind_desc) VALUES ('電腦硬件','這里并不是很主流的產品,但價格絕對令你心動');
INSERT INTO kind (kind_name,kind_desc) VALUES ('房產','提供非常稀缺的房源');
--物品狀態表
create table state(
state_id int(11) auto_increment,
state_name varchar(10),
primary key(state_id)
);
INSERT INTO state (state_name) VALUES ('拍賣中');
INSERT INTO state (state_name) VALUES ('拍賣成功');
INSERT INTO state (state_name) VALUES ('流拍');
--物品表
create table item(
item_id int(11) auto_increment,
item_name varchar(255) not null,
item_pic varchar(255) not null,
item_desc varchar(255),
kind_id int(11) not null,
addtime date not null,
endtime date not null,
init_price double not null,
max_price double not null,
owner_id int(11) not null,
winer_id int(11),
state_id int(11) not null,
primary key(item_id),
FOREIGN KEY(kind_id) REFERENCES kind(kind_id),
FOREIGN KEY(owner_id) REFERENCES auction_user(user_id),
FOREIGN KEY(winer_id) REFERENCES auction_user(user_id),
FOREIGN KEY(state_id) REFERENCES state(state_id)
);
INSERT INTO item ( item_name , item_pic , item_desc, kind_id, addtime , endtime, init_price, max_price, owner_id, winer_id, state_id)
VALUES ( '主板', '老式主板', '老主板,還可以用', 1, '2006-07-06', '2008-07-10', 230, 250, 1, null, 1);
INSERT INTO item ( item_name , item_pic , item_desc, kind_id, addtime , endtime, init_price, max_price, owner_id, winer_id, state_id)
VALUES ( '顯卡', '老式顯卡', '老顯卡,還可以用', 1, '2006-07-03', '2008-07-05', 210, 210, 2, null, 3);
INSERT INTO item ( item_name , item_pic , item_desc, kind_id, addtime , endtime, init_price, max_price, owner_id, winer_id, state_id)
VALUES ( '老房子', '老式房子', '40年的老房子', 2, '2006-07-03', '2008-07-06', 21000, 25000, 2, 1, 2);
--競投歷史表
create table bid(
bid_id int(11) auto_increment,
user_id int(11) not null,
item_id int(11) not null,
bid_price double not null,
bid_date date not null,
primary key(bid_id),
unique(item_id , bid_price),
FOREIGN KEY(user_id) REFERENCES auction_user(user_id),
FOREIGN KEY(item_id) REFERENCES item(item_id)
);
INSERT INTO bid ( user_id , item_id , bid_price, bid_date)
VALUES ( 2, 1, 250, '2006-07-09');
INSERT INTO bid ( user_id , item_id , bid_price, bid_date)
VALUES ( 1, 3, 25000, '2006-07-05');
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -