schema.sql

来自「用struts hibernate 开发的论坛源码! 开发平台:eclipes」· SQL 代码 · 共 58 行

# Host: 127.0.0.1    Database: springaop_base
# ------------------------------------------------------
# Server version 5.0.17-nt

DROP DATABASE IF EXISTS `springaop_base`;
CREATE DATABASE `springaop_base`;
USE `springaop_base`;

CREATE TABLE `USERS` (
  `id` int(10) primary key auto_increment,
  `name` varchar(20) NOT NULL,
  `login_name` varchar(20) NOT NULL unique,
  `password` varchar(20) NOT NULL,
  `email` varchar(20) NOT NULL,
  `borrow_book_amount` int(10) default '0'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ROLES` (
	`id` int(10) primary key auto_increment ,
	`name` varchar(20) NOT NULL unique
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `USERS_ROLES`(
	user_id int(10) NOT NULL,
	role_id int(10) NOT NULL,
	FOREIGN KEY(user_id) REFERENCES users(id),
	FOREIGN KEY(role_id) REFERENCES roles(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `BOOKS` (
  `id` int(10) primary key auto_increment,
  `title` varchar(20) NOT NULL,
  `remark` varchar(40) default NULL,
  `user_id` int(10) default '1',
  FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `LEND_RECORDS` (
  `id` int(10) primary key  auto_increment,
  `create_date` datetime NOT NULL,
  `handle_type` int(1) NOT NULL,
  `user_id` int(10) NOT NULL,
  `book_id` int(10) NOT NULL,
   FOREIGN KEY(user_id) REFERENCES users(id),
   FOREIGN KEY(book_id) REFERENCES books(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;