master

分支 (1)

管理

管理

master

exam
/
exam.sql

-- MySQL dump 10.13  Distrib 5.7.17, for Linux (x86_64)
--
-- Host: localhost    Database: exam
-- ------------------------------------------------------
-- Server version	5.7.17-0ubuntu0.16.04.1

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `category`
--

DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `category`
--

LOCK TABLES `category` WRITE;
/*!40000 ALTER TABLE `category` DISABLE KEYS */;
INSERT INTO `category` VALUES (1,'信贷综合',0),(2,'会计',0),(3,'党政',0),(4,'信息',0),(5,'信贷',1),(10,'创新',1),(11,'扶贫',1),(12,'国际业务',1),(13,'粮棉油',1),(14,'信用审批',1),(15,'风险管理',1),(16,'基础设施',1),(17,'法律与内控合规',1),(18,'投资',1),(20,'办公室',0);
/*!40000 ALTER TABLE `category` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `city` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `city`
--

LOCK TABLES `city` WRITE;
/*!40000 ALTER TABLE `city` DISABLE KEYS */;
INSERT INTO `city` VALUES (1,0,'第一中学'),(2,1,'七年级'),(3,2,'一班'),(4,2,'二班'),(5,2,'三班'),(6,0,'安徽'),(8,6,'合肥'),(9,6,'淮北'),(10,6,'淮南'),(11,6,'阜阳'),(12,6,'宿州');
/*!40000 ALTER TABLE `city` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exam_page`
--

DROP TABLE IF EXISTS `exam_page`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exam_page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `city` int(11) NOT NULL,
  `room` int(11) NOT NULL,
  `score` smallint(6) NOT NULL COMMENT '得分',
  `page` int(11) NOT NULL,
  `question` longtext NOT NULL COMMENT '试卷内容，只有随机时使用',
  `answer` text NOT NULL COMMENT '答案',
  `start_time` datetime NOT NULL COMMENT '参考时间',
  `usetime` int(11) NOT NULL COMMENT '用时',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exam_page`
--

LOCK TABLES `exam_page` WRITE;
/*!40000 ALTER TABLE `exam_page` DISABLE KEYS */;
/*!40000 ALTER TABLE `exam_page` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `group`
--

DROP TABLE IF EXISTS `group`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `users` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `group`
--

LOCK TABLES `group` WRITE;
/*!40000 ALTER TABLE `group` DISABLE KEYS */;
INSERT INTO `group` VALUES (1,'信贷人员','[{\"id\": \"1\"}]'),(2,'会计人员','[{\"id\": \"4\"}]');
/*!40000 ALTER TABLE `group` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `page`
--

DROP TABLE IF EXISTS `page`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `category` int(11) NOT NULL COMMENT '分类ID',
  `type` tinyint(1) NOT NULL COMMENT '0随机，1手工',
  `time` int(11) NOT NULL COMMENT '考试时长',
  `total` int(11) NOT NULL COMMENT '总分',
  `passing` int(11) NOT NULL COMMENT '及格线',
  `question` text NOT NULL COMMENT '试题分配，json',
  `upset` tinyint(1) DEFAULT '0' COMMENT '1打乱选项顺序',
  `date` date NOT NULL,
  `user` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `page`
--

LOCK TABLES `page` WRITE;
/*!40000 ALTER TABLE `page` DISABLE KEYS */;
/*!40000 ALTER TABLE `page` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `question`
--

DROP TABLE IF EXISTS `question`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `question` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(500) NOT NULL,
  `category` int(11) NOT NULL COMMENT '分类',
  `type` tinyint(1) NOT NULL COMMENT '类型',
  `option` json NOT NULL COMMENT 'json格式',
  `level` tinyint(1) NOT NULL COMMENT '难易',
  `analysis` text NOT NULL COMMENT '解析',
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `question`
--

LOCK TABLES `question` WRITE;
/*!40000 ALTER TABLE `question` DISABLE KEYS */;
/*!40000 ALTER TABLE `question` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `room`
--

DROP TABLE IF EXISTS `room`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `page` int(11) NOT NULL,
  `time` datetime NOT NULL,
  `start` tinyint(4) NOT NULL DEFAULT '0' COMMENT '进场时间，0为不限',
  `city` int(11) DEFAULT NULL,
  `group` text,
  `stat` tinyint(1) NOT NULL COMMENT '0为考试，1为练习',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `room`
--

LOCK TABLES `room` WRITE;
/*!40000 ALTER TABLE `room` DISABLE KEYS */;
INSERT INTO `room` VALUES (1,'会计',1,'2017-02-04 09:00:00',0,1,'[{\"id\":\"2\",\"name\":\"会计人员\"}]',1);
/*!40000 ALTER TABLE `room` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sessions`
--

DROP TABLE IF EXISTS `sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sessions` (
  `id` varchar(40) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` int(10) unsigned NOT NULL DEFAULT '0',
  `data` blob NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sessions_id_ip` (`id`,`ip_address`),
  KEY `sessions_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sessions`
--

LOCK TABLES `sessions` WRITE;
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
INSERT INTO `sessions` VALUES ('1530f8d5c25f9af7aed6b0e2d8dc92778d3831e1','127.0.0.1',1486538028,'__ci_last_regenerate|i:1486538028;');
/*!40000 ALTER TABLE `sessions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `setting`
--

DROP TABLE IF EXISTS `setting`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `setting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `value` text NOT NULL,
  `desc` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `setting`
--

LOCK TABLES `setting` WRITE;
/*!40000 ALTER TABLE `setting` DISABLE KEYS */;
/*!40000 ALTER TABLE `setting` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `types`
--

DROP TABLE IF EXISTS `types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` tinyint(1) NOT NULL COMMENT '类型，0、客观题1、主观题',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `types`
--

LOCK TABLES `types` WRITE;
/*!40000 ALTER TABLE `types` DISABLE KEYS */;
INSERT INTO `types` VALUES (1,'单选题',0),(2,'多选题',0),(3,'判断题',0),(4,'填空题',0),(5,'问答题',1);
/*!40000 ALTER TABLE `types` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `password` char(32) NOT NULL,
  `email` varchar(200) DEFAULT NULL,
  `avatar` varchar(36) DEFAULT NULL,
  `level` tinyint(1) NOT NULL DEFAULT '1',
  `city` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'admin','202cb962ac59075b964b07152d234b70','','952e81433f2e59233998b0735c4a4135.jpg',9,1),(4,'admin1','202cb962ac59075b964b07152d234b70',NULL,'',1,2),(5,'123','','',NULL,1,3);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getCategoryChild`(rootId INT) RETURNS varchar(1000) CHARSET latin1
BEGIN
	DECLARE sTemp VARCHAR(1000);
	DECLARE sTempChd VARCHAR(1000);

	SET sTemp = '$';
	SET sTempChd =cast(rootId as CHAR);

	WHILE sTempChd is not null DO
		SET sTemp = concat(sTemp,',',sTempChd);
	SELECT group_concat(id) INTO sTempChd FROM  category where FIND_IN_SET(parent_id,sTempChd)>0;
		END WHILE;
	RETURN sTemp;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getCityChild`(rootId INT) RETURNS varchar(1000) CHARSET latin1
BEGIN
 DECLARE sTemp VARCHAR(1000);
 DECLARE sTempChd VARCHAR(1000);
 SET sTemp = '$';
 SET sTempChd =cast(rootId as CHAR);

 WHILE sTempChd is not null DO
  SET sTemp = concat(sTemp,',',sTempChd);
  SELECT group_concat(id) INTO sTempChd FROM city where FIND_IN_SET(parent_id,sTempChd)>0;
 END WHILE;
 RETURN sTemp;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getCategoryParent`(rootId INT) RETURNS varchar(1000) CHARSET latin1
BEGIN
	DECLARE sTemp VARCHAR(1000);
	DECLARE sTempPar VARCHAR(1000);
	SET sTemp = '';
	SET sTempPar =rootId;

		WHILE sTempPar is not null DO
				IF sTemp != '' THEN
			SET sTemp = concat(sTemp,',',sTempPar);
		ELSE
			SET sTemp = sTempPar;
		END IF;

		SET sTemp = concat(sTemp,',',sTempPar);
		SELECT group_concat(parent_id) INTO sTempPar FROM category where parent_id<>id and FIND_IN_SET(id,sTempPar)>0;
	END WHILE;

RETURN sTemp;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getCityParent`(rootId INT) RETURNS varchar(1000) CHARSET latin1
BEGIN
	DECLARE sTemp VARCHAR(1000);
	DECLARE sTempPar VARCHAR(1000);
	SET sTemp = '';
	SET sTempPar =rootId;

		WHILE sTempPar is not null DO
				IF sTemp != '' THEN
			SET sTemp = concat(sTemp,',',sTempPar);
		ELSE
			SET sTemp = sTempPar;
		END IF;

		SET sTemp = concat(sTemp,',',sTempPar);
		SELECT group_concat(parent_id) INTO sTempPar FROM city where parent_id<>id and FIND_IN_SET(id,sTempPar)>0;
	END WHILE;

RETURN sTemp;
END$$
DELIMITER ;


-- Dump completed on 2017-02-08 15:45:24