--物品狀態表
CREATE TABLE `test`.`state` (
  `state_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `state_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`state_id`)
)TYPE=MyISAM,default character set gb2312;

INSERT INTO state (state_name) VALUES ('拍賣中');
INSERT INTO state (state_name) VALUES ('拍賣成功');
INSERT INTO state (state_name) VALUES ('流拍');

--物品種類表
CREATE TABLE `test`.`kind` (
  `kind_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `kind_name` VARCHAR(45) NOT NULL,
  `kind_desc` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`kind_id`)
)TYPE=MyISAM,default character set gb2312;

INSERT INTO kind (kind_name,kind_desc) VALUES ('電腦硬件','最實惠的');
INSERT INTO kind (kind_name,kind_desc) VALUES ('房子','最好的房源');

--用戶表
CREATE TABLE `test`.`user` (
  `user_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL unique,
  `userpass` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`user_id`)
)TYPE=MyISAM,default character set gb2312;

INSERT INTO user (username,userpass,email) VALUES ('tomcat','tomcat','spring_test@163.com');
INSERT INTO user (username,userpass,email) VALUES ('mysql','mysql','spring_test@163.com');

--物品表
CREATE TABLE `test`.`item` (
  `item_id` INTEGER UNSIGNED AUTO_INCREMENT,
  `item_name` VARCHAR(45) NOT NULL,
  `item_remark` VARCHAR(45) NOT NULL,
  `item_desc` VARCHAR(45),
  `kind_id` INTEGER UNSIGNED NOT NULL,
  `addtime` DATETIME NOT NULL,
  `endtime` DATETIME NOT NULL,
  `init_price` DOUBLE NOT NULL,
  `max_price` DOUBLE NOT NULL,
  `owner_id` INTEGER UNSIGNED NOT NULL,
  `winer_id` INTEGER UNSIGNED,
  `state_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(`item_id`),
  CONSTRAINT `FK_kind_id` FOREIGN KEY `FK_kind_id` (`kind_id`)
    REFERENCES `kind` (`kind_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_state_id` FOREIGN KEY `FK_state_id` (`state_id`)
    REFERENCES `state` (`state_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_owner_id` FOREIGN KEY `FK_owner_id` (`owner_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_winer_id` FOREIGN KEY `FK_winer_id` (`winer_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)TYPE=MyISAM,default character set gb2312;

INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price,  max_price,  owner_id,  winer_id,  state_id)
	VALUES ( 'motherboard', 'old', 'old，but using', 1, '2006-07-06', '2008-07-10', 230, 250, 1,  null,  1);

INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price,  max_price,  owner_id,  winer_id,  state_id)
	VALUES ( 'xianka', 'old', 'old，but using', 1, '2006-07-03', '2008-07-05', 210, 210, 2,  null,  3);

INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price,  max_price,  owner_id,  winer_id,  state_id)
	VALUES ( 'bieshu', 'old', '40niandai', 2, '2006-07-03', '2008-07-06', 21000, 25000, 2,  1,  2);
	
INSERT INTO item ( item_name , item_remark , 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_remark , 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_remark , 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 `test`.`bid` (
  `bid_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `item_id` INTEGER UNSIGNED NOT NULL,
  `bid_price` DOUBLE NOT NULL,
  `bid_date` DATETIME NOT NULL,
  PRIMARY KEY(`bid_id`),
  CONSTRAINT `FK_user_id` FOREIGN KEY `FK_user_id` (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_item_id` FOREIGN KEY `FK_item_id` (`item_id`)
    REFERENCES `item` (`item_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)TYPE=MyISAM,default character set gb2312;

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');
	
	
	mysql --default-character-set=gb2312 -u root -p
亂碼: SHOW VARIABLES LIKE 'character_set_%'