精通Java EE项目案例--基于Eclipse Spring Struts Hibernate (例子中的初始化SQL)

/* SQLyog Enterprise Trial - MySQL GUI v6.04 Host - 5.0.45-community-nt : Database - stu ********************************************************************* Server version : 5.0.45-community-nt */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; create database if not exists `stu`; USE `stu`; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*Table structure for table `admin` */ DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` ( `id` int(16) NOT NULL, `name` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `admin` */ insert into `admin`(`id`,`name`,`password`) values (1,'admin','admin'); /*Table structure for table `classes` */ DROP TABLE IF EXISTS `classes`; CREATE TABLE `classes` ( `id` varchar(32) NOT NULL, `tea_id` varchar(32) default NULL, `cour_id` varchar(32) default NULL, `room_id` varchar(32) default NULL, `cour_time` varchar(32) default NULL, PRIMARY KEY (`id`), KEY `FK_Reference_4` (`tea_id`), KEY `FK_classes` (`cour_id`), CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`cour_id`) REFERENCES `course` (`id`), CONSTRAINT `FK_Reference_4` FOREIGN KEY (`tea_id`) REFERENCES `teacher` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `classes` */ insert into `classes`(`id`,`tea_id`,`cour_id`,`room_id`,`cour_time`) values ('00701006','4','4','10-2','星期一/一节'),('060901','1','1','10-2','星期一/二节'),('070902','2','3','10-2','星期一/三节'); /*Table structure for table `course` */ DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` varchar(32) NOT NULL, `name` varchar(32) default NULL, `mark` int(11) default NULL, `prepare` varchar(32) default NULL, `dep` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `course` */ insert into `course`(`id`,`name`,`mark`,`prepare`,`dep`) values ('1','中文',3,'0','人文学院'),('2','软件开发',3,'0','计算机'),('3','java 程序设计',2,'模拟电路与数字电路','计算机'),('4','微机分',2,'0','电子系'),('5','c++',1,'0','计算机'),('6','java',2,'软件开发','计算机'),('7','模拟电路与数字电路',4,'0','数学系'); /*Table structure for table `enrol` */ DROP TABLE IF EXISTS `enrol`; CREATE TABLE `enrol` ( `class_id` varchar(32) default NULL, `stu_id` varchar(32) default NULL, `accept` varchar(32) default NULL, `score` varchar(32) default NULL, KEY `FK_enrol` (`class_id`), KEY `FK_Reference_2` (`stu_id`), CONSTRAINT `enrol_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`), CONSTRAINT `enrol_ibfk_2` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `enrol` */ insert into `enrol`(`class_id`,`stu_id`,`accept`,`score`) values ('060901','001','0',NULL),('00701006','001','0',NULL); /*Table structure for table `stu_enrol` */ DROP TABLE IF EXISTS `stu_enrol`; /*!50001 DROP VIEW IF EXISTS `stu_enrol` */; /*!50001 DROP TABLE IF EXISTS `stu_enrol` */; /*!50001 CREATE TABLE `stu_enrol` ( `class_id` varchar(32) default NULL, `name` varchar(32) default NULL, `stu_id` varchar(32) default NULL, `score` varchar(32) default NULL, `mark` int(11) default NULL, `totalmark` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 */; /*Table structure for table `student` */ DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` varchar(32) NOT NULL, `name` varchar(32) default NULL, `password` varchar(32) default NULL, `jiguan` varchar(32) default NULL, `department` varchar(32) default NULL, `sex` varchar(32) default NULL, `mark` int(11) default NULL, `tel` varchar(32) default NULL, `phone` varchar(32) default NULL, `email` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student`(`id`,`name`,`password`,`jiguan`,`department`,`sex`,`mark`,`tel`,`phone`,`email`) values ('001','liuyu','888','北京','计算机','女',0,'020-389778','1360897733','jj@sina.com.cn'),('002','wangbing','123','北京','电子系','男',0,'0731-48210457','1381239687','22@sian.com.cn'),('004','李南','88888','北京','机械系','男',0,'6535428','13906356582','linan@163.com'),('005','zhanglin','888888','上海','电子系','女',0,'3573244','1593658421','zhangling@21cn.net'); /*Table structure for table `teacher` */ DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `id` varchar(32) NOT NULL, `name` varchar(32) default NULL, `password` varchar(32) default NULL, `title` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `teacher` */ insert into `teacher`(`id`,`name`,`password`,`title`) values ('1','guohua','123','教授'),('2','王浩','123','副教授'),('3','刘冥','123','教授'),('4','张林','123','副教授'); /*View structure for view stu_enrol */ /*!50001 DROP TABLE IF EXISTS `stu_enrol` */; /*!50001 DROP VIEW IF EXISTS `stu_enrol` */; /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_enrol` AS (select `enrol`.`class_id` AS `class_id`,`course`.`name` AS `name`,`enrol`.`stu_id` AS `stu_id`,`enrol`.`score` AS `score`,`course`.`mark` AS `mark`,`student`.`mark` AS `totalmark` from (((`enrol` join `student`) join `classes`) join `course`) where ((`enrol`.`class_id` = `classes`.`id`) and (`classes`.`cour_id` = `course`.`id`) and (`enrol`.`stu_id` = `student`.`id`))) */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;