-- phpMyAdmin SQL Dump
-- version phpStudy 2014
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2020 �?07 �?05 �?06:40
-- 服务器版本: 5.5.53
-- PHP 版本: 5.5.38
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- 数据库: `book`
--
-- --------------------------------------------------------
--
-- 表的结构 `book`
--
CREATE TABLE IF NOT EXISTS `book` (
`book_id` char(10) NOT NULL,
`book_name` varchar(15) NOT NULL,
`book_first_address` varchar(15) NOT NULL,
`book_state` int(1) NOT NULL,
`book_press` varchar(10) DEFAULT NULL,
`book_author` varchar(8) NOT NULL,
`book_version` char(11) DEFAULT NULL,
`book_second_address` varchar(11) DEFAULT NULL,
`book_store_account` int(11) DEFAULT NULL,
PRIMARY KEY (`book_id`),
KEY `book_first_address` (`book_first_address`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- 转存表中的数据 `book`
--
INSERT INTO `book` (`book_id`, `book_name`, `book_first_address`, `book_state`, `book_press`, `book_author`, `book_version`, `book_second_address`, `book_store_account`) VALUES
('001', '西游记', '新校区一楼', 0, '南阳理工', '施耐庵', '1.0', '1001', 2),
('002', '数据库基础及应用技术', '新校区二楼', 0, '清华大学出版社', '何玉洁', '5.0', '2002', 1),
('003', '网络信息安全', '老校区三楼', 0, '机械工业出版社', '刘军', '6.0', '3003', 0);
-- --------------------------------------------------------
--
-- 替换视图以便查看 `bookview`
--
CREATE TABLE IF NOT EXISTS `bookview` (
`索书号` char(10)
,`书名` varchar(15)
,`作者` varchar(8)
,`出版社` varchar(10)
,`图书状态` int(1)
);
-- --------------------------------------------------------
--
-- 表的结构 `borrow`
--
CREATE TABLE IF NOT EXISTS `borrow` (
`book_id` char(11) NOT NULL DEFAULT '',
`reader_id` char(11) NOT NULL DEFAULT '',
`book_name` char(11) NOT NULL,
`book_author` char(11) NOT NULL,
`out_date` time NOT NULL,
`in_date` time DEFAULT NULL,
`yh_date` time DEFAULT NULL,
`fine` int(11) DEFAULT NULL,
`clstate` tinyint(1) DEFAULT NULL,
`manager_id` char(11) DEFAULT NULL,
PRIMARY KEY (`book_id`,`reader_id`),
KEY `manager_id` (`manager_id`),
KEY `reader_id` (`reader_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- 转存表中的数据 `borrow`
--
INSERT INTO `borrow` (`book_id`, `reader_id`, `book_name`, `book_author`, `out_date`, `in_date`, `yh_date`, `fine`, `clstate`, `manager_id`) VALUES
('001', '91384899', '西游记', '施耐庵', '14:15:36', '00:00:01', '00:00:01', 12, 1, '001'),
('002', '91384810', '数据库基础及应用技术', '何玉洁', '00:00:01', '00:00:02', '00:00:03', 12, 1, '001'),
('003', '91384811', '网络信息安全', '刘军', '00:00:01', '00:00:05', '00:00:04', 12, 1, '002');
-- --------------------------------------------------------
--
-- 替换视图以便查看 `borrowview`
--
CREATE TABLE IF NOT EXISTS `borrowview` (
`读者编号` char(11)
,`书名` varchar(15)
,`作者` varchar(8)
,`借阅日期` time
,`到期日期` time
);
-- --------------------------------------------------------
--
-- 替换视图以便查看 `fineview`
--
CREATE TABLE IF NOT EXISTS `fineview` (
`读者编号` char(11)
,`书名` varchar(15)
,`借阅日期` time
,`归还日期` time
,`罚款` int(11)
,`处理状态` tinyint(1)
);
-- --------------------------------------------------------
--
-- 替换视图以便查看 `historyview`
--
CREATE TABLE IF NOT EXISTS `historyview` (
`读者编号` char(11)
,`书名` varchar(15)
,`借阅日期` time
,`归还日期` time
);
-- --------------------------------------------------------
--
-- 表的结构 `manager`
--
CREATE TABLE IF NOT EXISTS `manager` (
`manager_id` char(10) NOT NULL,
`manager_name` char(5) NOT NULL,
`manager_gender` char(2) DEFAULT NULL,
`manager_right` char(5) DEFAULT NULL,
`manager_pwd` char(12) NOT NULL,
`manager_address` char(10) DEFAULT NULL,
`manager_phone` char(10) DEFAULT NULL,
PRIMARY KEY (`manager_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- 转存表中的数据 `manager`
--
INSERT INTO `manager` (`manager_id`, `manager_name`, `manager_gender`, `manager_right`, `manager_pwd`, `manager_address`, `manager_phone`) VALUES
('001', '陈真', '男', 'admin', '123456', '一楼', '114'),
('002', '梁一天', '男', 'admin', '123456', '二楼', '911');
-- --------------------------------------------------------
--
-- 表的结构 `reader`
--
CREATE TABLE IF NOT EXISTS `reader` (
`reader_id` char(10) NOT NULL,
`reader_name` char(10) NOT NULL,
`reader_gread` char(10) NOT NULL,
`reader_gender` char(2) NOT NULL,
`reader_phone` char(10) NOT NULL,
`reader_department` char(10) NOT NULL,
`reader_profession` char(10) NOT NULL,
`reader_lbid` char(10) NOT NULL,
PRIMARY KEY (`reader_id`),
KEY `reader_lbid` (`reader_lbid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- 转存表中的数据 `reader`
--
INSERT INTO `reader` (`reader_id`, `reader_name`, `reader_gread`, `reader_gender`, `reader_phone`, `reader_department`, `reader_profession`, `reader_lbid`) VALUES
('91384899', '李大花', '大一', '女', '110', '计算机', '信息安全', '在校学生'),
('91384810', '王翠花', '大二', '女', '120', '数学系', '统计学', '在校学生'),
('91384811', '张铁柱', '大四', '男', '119', '文法', '汉语言', '在校学生');
--
-- 触发器 `reader`
--
DROP TRIGGER IF EXISTS `READER_DELETE`;
DELIMITER //
CREATE TRIGGER `READER_DELETE` BEFORE DELETE ON `reader`
FOR EACH ROW DELETE FROM BORROW WHERE BORROW.READER_ID = old.READER_ID
//
DELIMITER ;
-- --------------------------------------------------------
--
-- 表的结构 `readertype`
--
CREATE TABLE IF NOT EXISTS `readertype` (
`lb_id` char(5) NOT NULL,
`lbname` char(20) NOT NULL,
`lbbqx` char(4) NOT NULL,
`lbqx` char(3) NOT NULL,
`lcnum` int(11) DEFAULT NULL,
PRIMARY KEY (`lb_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- 转存表中的数据 `readertype`
--
INSERT INTO `readertype` (`lb_id`, `lbname`, `lbbqx`, `lbqx`, `lcnum`) VALUES
('在校学生', '学生', '30天', '毕业前', NULL),
('毕业生', '非学生', '0', '', NULL),
('老师', '教师', '45', '', NULL);
-- --------------------------------------------------------
--
-- 替换视图以便查看 `readerview`
--
CREATE TABLE IF NOT EXISTS `readerview` (
`读者姓名` char(10)
,`类型` char(10)
,`学院` char(10)
,`专业` char(10)
);
-- --------------------------------------------------------
--
-- 表的结构 `read_room`
--
CREATE TABLE IF NOT EXISTS `read_room` (
`reading_room_id` char(10) NOT NULL,
`reading_room_name` varchar(15) DEFAULT NULL,
`reading_room_address` char(13) DEFAULT NULL,
`reading_room_principal` varchar(6) DEFAULT NULL,
PRIMARY KEY (`reading_room_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- 转存表中的数据 `read_room`
--
INSERT INTO `read_room` (`reading_room_id`, `reading_room_name`, `reading_room_address`, `reading_room_principal`) VALUES
('新校区一楼', '文学借阅室', '1001', '001'),
('新校区二楼', '专业书籍', '2002', '002'),
('老校区三楼', '专业书籍', '3003', '002');
-- --------------------------------------------------------
--
-- 视图结构 `bookview`
--
DROP TABLE IF EXISTS `bookview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bookview` AS select `book_id` AS `索书号`,`book_name` AS `书名`,`book_author` AS `作者`,`book_press` AS `出版社`,`book_state` AS `图书状态` from `book`;
-- --------------------------------------------------------
--
-- 视图结构 `borrowview`
--
DROP TABLE IF EXISTS `borrowview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
- 1
- 2
- 3
- 4
- 5
前往页