/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50556 (5.5.56)
 Source Host           : localhost:3306
 Source Schema         : online_quiz_website

 Target Server Type    : MySQL
 Target Server Version : 50556 (5.5.56)
 File Encoding         : 65001

 Date: 03/05/2023 12:34:04
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for detailed_topic
-- ----------------------------
DROP TABLE IF EXISTS `detailed_topic`;
CREATE TABLE `detailed_topic`  (
  `detailed_topic_id` int(11) NOT NULL COMMENT '详细题目id',
  `topic_id` int(11) NOT NULL COMMENT '题目id',
  `topic_title` text CHARACTER SET utf8 COLLATE utf8_bin NULL COMMENT '题目标题',
  `topic_check_1` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '选项1',
  `topic_check_2` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '选项2',
  `topic_check_3` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '选项3',
  `topic_check_4` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '选项4',
  `topic_check_5` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '选项5',
  `topic_answer` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '题目答案',
  `topic_answer_parse` text CHARACTER SET utf8 COLLATE utf8_bin NULL COMMENT '题目解析',
  `frequency` int(8) NOT NULL DEFAULT 0 COMMENT '解题次数',
  PRIMARY KEY (`detailed_topic_id`) USING BTREE,
  INDEX `topic_id`(`topic_id`) USING BTREE,
  CONSTRAINT `detailed_topic_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`topic_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of detailed_topic
-- ----------------------------
INSERT INTO `detailed_topic` VALUES (221227101, 21227001, '在-2，-1，0，2这四个数中，最大的数是（）。', '-2', '-1', '0', '2', NULL, '2', '正数比负数大', 1);
INSERT INTO `detailed_topic` VALUES (221227102, 21227001, '比较-3，-1，-2的大小，下列判断正确的是（）。', '-3<-2<1', '-2<-3<1', '1<-2<-3', '1<-3<-2', NULL, '-3<-2<1', '根据实数的大小比较法则，正数大于0，0大于负数，两个负数相比，绝对值大的反而小，因此，-3<-2<0<1', 0);
INSERT INTO `detailed_topic` VALUES (221227103, 21227001, '如果收入80元记作+80元，那么支出20元记作（）。', '+20元', '-20元', '+100元', '-100元', NULL, '-20元', '在一对具有相反意义的量中，先规定其中一个为正，则另一个就用负表示。因此，收入和支出相对。', 0);
INSERT INTO `detailed_topic` VALUES (221229101, 21229002, '下列各式中，与2a是同类项的是（）。', '3a', '2ab', '-3a^2', 'a^2b', NULL, '3a', '同类项是所含的字母相同，并且相同字母的指数也相同的项，因此，2a中的字母是a，a的指数为1', 0);
INSERT INTO `detailed_topic` VALUES (221229102, 21229002, '单项式-(2xy^2)/5的系数是________。', 'null', 'null', 'null', 'null', NULL, '-2/5', '单项式的系数：单项式中的数字因数', 0);
INSERT INTO `detailed_topic` VALUES (230112101, 30112002, '方程x+2=1的解是（）。', '3', '-3', '1', '-1', NULL, '-1', '根据等式的性质，移项`x=1-2`，即可求出方程的解', 0);
INSERT INTO `detailed_topic` VALUES (230112102, 30112001, '方程2x-1=0的解是x=______。', 'null', 'null', 'null', 'null', NULL, '1/2', '根据等式的性质，解方程步骤中移项，系数化为1，移项得：`x=1/2`.', 0);
INSERT INTO `detailed_topic` VALUES (230112103, 30112001, '在实数范围定义运算“&”：a&b=2a+b,则x&(x-6)=0的实数x是______。', 'null', 'null', 'null', 'null', 'null', '2', '`x&(x-6)=0，2x+(x-6)=0，3x=6，x=2`', 0);
INSERT INTO `detailed_topic` VALUES (230112104, 30112002, '方程4x-3=x的解是（）。', 'x=1', 'x=-1', 'x=2', 'x=-2', NULL, 'x=1', '移项合并得：`3x=3，解得：x=1`', 0);
INSERT INTO `detailed_topic` VALUES (230112105, 21229003, '函数y=\\sqrt[x](x+1)中的自变量x的取值范围是（）。', ' x\\geq0', ' x\\ne-1', 'x>0', 'x\\geq0`且`x\\ne-1', NULL, 'x\\geq0', '根据二次根式的性质和意义，被开方数大于或等于0，分母不等于0，可以求出`x`的范围。', 0);
INSERT INTO `detailed_topic` VALUES (230123101, 21229003, '测试', '测试', '测试', '测试', '测试', NULL, '测试', '测试', 0);
INSERT INTO `detailed_topic` VALUES (230126100, 30126100, '在数轴上把2对应的点移动5个单位长度后，所得对应点表示数（）。', '7', '-3', '7或者-3', '不能确定', NULL, '7或者-3', '暂无解析', 0);
INSERT INTO `detailed_topic` VALUES (230126101, 30126102, '在数轴上，原点及原点左边的所表示的数是（  ）。', '正数', '负数', '不是负数', '不是正数', NULL, '不是正数', '暂无解析', 0);
INSERT INTO `detailed_topic` VALUES (230225100, 30225100, '在体育课的立定跳远测试中，以2.00m为标准，若小明跳出2.35m，可记作+0.35m，则小亮跳出了1.85m，可记作（  ）', '+0.15m', '-0.15m', '+0.35m', '-0.35m', NULL, '-0.15m', '解：1.85-2.00=-0.15（米），小亮跳出了1.85米，应记作-0.15m \n故选：B', 0);

-- ----------------------------
-- Table structure for detailed_user
-- ----------------------------
DROP TABLE IF EXISTS `detailed_user`;
CREATE TABLE `detailed_user`  (
  `detailed_user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_account` int(10) NOT NULL,
  `nickname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '昵称',
  `remark` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '备注',
  `gender` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '性别',
  `phone` bigint(11) NULL DEFAULT NULL COMMENT '手机号',
  `email` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '邮箱',
  `address` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '省份',
  `question_numbers` int(11) NULL DEFAULT NULL COMMENT '做题数量',
  `question_errors` int(11) NULL DEFAULT NULL COMMENT '错误数量',
  `question_true` int(11) NULL DEFAULT NULL COMMENT '正确数量',
  `question_accuracy_rate` float(5, 2) NULL DEFAULT NULL COMMENT '正确率',
  PRIMARY KEY (`detailed_user_id`, `user_account`) USING BTREE,
  INDEX `user_account`(`user_account`) USING BTREE,
  INDEX `email`(`email`) USING BTREE,
  CONSTRAINT `detailed_user_ibfk_1` FOREIGN KEY (`user_account`) REFERENCES `user_login` (`login_account`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `detailed_user_ibfk_2` FOREIGN KEY (`email`) REFERENCES `user_login` (`login_Mail`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of detailed_user
-- ----------------------------
INSERT INTO `detailed_user` VALUES (1, 10000, '兔子猫', '当你为错过太阳而哭泣的时候，你也要错过群星了', '男', 17671066395, '857826959@qq.com', '湖北省', 0, 0, 0, 0.00);
INSERT INTO `detailed_user` VALUES (2, 10004, '若离', '好好学习，天天向上', '男', 17671066395, '857826959@qq.com', '湖北省', 0, 0, 0, 0.00);
INSERT INTO `detailed_user` VALUES (3, 10001, '兔子喵', '一花一世界', '男', 1767106635, '857826959@qq.com', '广东省', 2, 1, 1, 50.00);
INSERT INTO `detailed_user` VALUES (4, 10023, '用户', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `detailed_user` VALUES (5, 10088, '用户', NULL, NULL, 17671066395, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `detailed_user` VALUES (6, 10091, '蔡志强', NULL, '男', 17671066395, '857826959@qq.com', '湖北省武汉市', NULL, NULL, NULL, NULL);

-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ` messageContext` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_account` int(11) NULL DEFAULT NULL,
  `message_reply` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `settle_status` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`message_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Records of message
-- ----------------------------
INSERT INTO `message` VALUES (1, '浏览器资源加载太慢了，处理一下', 10001, '好的好的', 1);
INSERT INTO `message` VALUES (2, '慢慢慢', 10023, '收到', NULL);
INSERT INTO `message` VALUES (3, '1111', 10001, '88', NULL);

-- ----------------------------
-- Table structure for person_type
-- ----------------------------
DROP TABLE IF EXISTS `person_type`;
CREATE TABLE `person_type`  (
  `person_type_id` int(1) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户类型id',
  `person_type_name` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户类型名称',
  `person_type_code` char(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户类型码',
  PRIMARY KEY (`person_type_id`) USING BTREE,
  INDEX `person_type_code`(`person_type_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of person_type
-- ----------------------------
INSERT INTO `person_type` VALUES (1, '做题者', 'PersonType1');
INSERT INTO `person_type` VALUES (2, '命题者', 'PersonType2');
INSERT INTO `person_type` VALUES (3, '管理员', 'PersonType3');

-- ----------------------------
-- Table structure for topic
-- ----------------------------
DROP TABLE IF EXISTS `topic`;
CREATE TABLE `topic`  (
  `topic_id` int(11) NOT NULL COMMENT '题目id',
  `subject_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '科目',
  `grade` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '年级',
  `chapter_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '章节名称',
  `knowledge_point` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '知识点',
  `topic_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '题目类型',
  PRIMARY KEY (`topic_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of topic
-- ----------------------------
INSERT INTO `topic` VALUES (21227001, '数学', '七年级', '有理数', '正数和负数', '选择题');
INSERT INTO `topic` VALUES (21229001, '数学', '七年级', '有理数', '有理数的加减乘除以及乘方', '选择题');
INSERT INTO `topic` VALUES (21229002, '数学', '七年级', '整式', '整式', '填空题');
INSERT INTO `topic` VALUES (21229003, '数学', '七年级', '有理数', '有理数的加减乘除以及乘方', '填空题');
INSERT INTO `topic` VALUES (30112001, '数学', '七年级', '方程与不等式', '一元一次方程', '填空题');
INSERT INTO `topic` VALUES (30112002, '数学', '七年级', '方程与不等式', '一元一次方程', '选择题');
INSERT INTO `topic` VALUES (30121001, '测试', '测试', '测试', '测试', '测试');
INSERT INTO `topic` VALUES (30123001, '数学', '七年级', '测试', '测试', '测试');
INSERT INTO `topic` VALUES (30123100, '数学', '七年级', '测试', '测试', '选择题');
INSERT INTO `topic` VALUES (30123101, '数学', '八年级', '12', '13', '判断题');
INSERT INTO `topic` VALUES (30123102, '数学', '七年级', '整数', '正数和负数', '填空题');
INSERT INTO `topic` VALUES (30126100, '数学', '七年级', '有理数', '数轴', '选择题');
INSERT INTO `topic` VALUES (30126102, '数学', '七年级', '有理数', '数轴', '选择题');
INSERT INTO `topic` VALUES (30225100, '数学', '七年级', '有理数', '正数和负数', '选择题');
INSERT INTO `topic` VALUES (30422100, '数学', '', '', '', '');

-- ----------------------------
-- Table structure for topic_help
-- ----------------------------
DROP TABLE IF EXISTS `topic_help`;
CREATE TABLE `topic_help`  (
  `tip_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '提示编号',
  `tip_title` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '提示标题',
  `tip_context` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '提示内容',
  `tip_status` int(1) NULL DEFAULT 0 COMMENT '1 禁用',
  PRIMARY KEY (`tip_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of topic_help
-- ----------------------------
INSERT INTO `topic_help` VALUES (1, '内容编辑', '选择题四个选项都需编辑', 0);
INSERT INTO `topic_help` VALUES (2, '内容编辑', '判断题编辑A,B两个选项', 0);
INSERT INTO `topic_help` VALUES (3, '内容编辑', '填空题不用编辑选项', 0);
INSERT INTO `topic_help` VALUES (4, '帮助提示', '由于前端数据不能实时渲染，导致数学公式格式发生变化，请在帮助栏中查看提示。', 0);
INSERT INTO `topic_help` VALUES (5, '帮助提示', '学习计划功能中，目前只对“每日一组”这一功能开放，其他功能暂定。', 0);

-- ----------------------------
-- Table structure for user_login
-- ----------------------------
DROP TABLE IF EXISTS `user_login`;
CREATE TABLE `user_login`  (
  `login_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `login_account` int(10) NOT NULL COMMENT '用户账号',
  `login_password` char(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户密码',
  `login_identity` char(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户类型',
  `login_status` int(1) NOT NULL DEFAULT 0 COMMENT '账号状态 -1 禁用 0 正常 1 冻结',
  `login_Mail` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户邮箱',
  PRIMARY KEY (`login_id`, `login_account`) USING BTREE,
  INDEX `login_account`(`login_account`) USING BTREE,
  INDEX `login_identity`(`login_identity`) USING BTREE,
  INDEX `login_account_2`(`login_account`, `login_Mail`) USING BTREE,
  INDEX `login_Mail`(`login_Mail`) USING BTREE,
  INDEX `login_Mail_2`(`login_Mail`, `login_id`, `login_account`) USING BTREE,
  INDEX `login_id`(`login_id`, `login_Mail`) USING BTREE,
  CONSTRAINT `user_login_ibfk_1` FOREIGN KEY (`login_identity`) REFERENCES `person_type` (`person_type_code`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of user_login
-- ----------------------------
INSERT INTO `user_login` VALUES (1, 10001, 'cai200215', 'PersonType1', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (2, 10000, '12345', 'PersonType3', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (6, 10004, 'cai123', 'PersonType3', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (24, 10022, '666666', 'PersonType1', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (25, 10023, '9de0c1', 'PersonType1', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (26, 10088, '444444', 'PersonType1', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (27, 10089, 'cai123', 'PersonType1', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (28, 10090, 'cai123', 'PersonType1', 0, 'cai');
INSERT INTO `user_login` VALUES (29, 10091, 'caizhiqiang', 'PersonType1', 0, '857826959@qq.com');
INSERT INTO `user_login` VALUES (30, 10092, 'a30201', 'PersonType1', 0, '857826959@qq.com');

-- ----------------------------
-- Table structure for user_topic
-- ----------------------------
DROP TABLE IF EXISTS `user_topic`;
CREATE TABLE `user_topic`  (
  `user_topic_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户题目id',
  `user_account` int(10) NOT NULL COMMENT '用户账号',
  `detailed_topic_id` int(10) NOT NULL COMMENT '详细题目id',
  `topic_answer` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '题目答案',
  `user_answer` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户答案',
  `favorites` int(1) NOT NULL DEFAULT 0 COMMENT '是否收藏',
  `replied_count` int(7) NOT NULL DEFAULT 0 COMMENT '答题次数',
  `replied_true_count` int(7) NOT NULL DEFAULT 0 COMMENT '正常次数',
  PRIMARY KEY (`user_topic_id`) USING BTREE,
  INDEX `detailed_topic_id`(`detailed_topic_id`) USING BTREE,
  INDEX `user_account`(`user_account`) USING BTREE,
  CONSTRAINT `user_topic_ibfk_1` FOREIGN KEY (`detailed_topic_id`) REFERENCES `detailed_topic` (`detailed_topic_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `user_topic_ibfk_2` FOREIGN KEY (`user_account`) REFERENCES `user_login` (`login_account`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of user_topic
-- ----------------------------
INSERT INTO `user_topic` VALUES (1, 10022, 221227101, '-3<-2<1', '1<-3<-2', 0, 1, 0);
INSERT INTO `user_topic` VALUES (2, 10001, 221227101, '2', '2', 1, 16, 12);
INSERT INTO `user_topic` VALUES (3, 10001, 221227103, '-20元', '-20元', 1, 2, 2);
INSERT INTO `user_topic` VALUES (4, 10001, 221227102, '3', '2', 0, 3, 2);
INSERT INTO `user_topic` VALUES (6, 10001, 230112104, 'x=1', 'x=1', 0, 6, 4);
INSERT INTO `user_topic` VALUES (7, 10001, 230112101, '-1', '-1', 1, 10, 7);
INSERT INTO `user_topic` VALUES (8, 10001, 230126100, '7或者-3', '7或者-3', 0, 8, 6);
INSERT INTO `user_topic` VALUES (9, 10001, 230123101, '测试', '测试', 0, 14, 10);
INSERT INTO `user_topic` VALUES (10, 10001, 230225100, '-0.15m', '-0.15m', 0, 15, 11);
INSERT INTO `user_topic` VALUES (11, 10088, 230112101, '-1', '-3', 1, 2, 1);
INSERT INTO `user_topic` VALUES (12, 10088, 221227103, '-20元', '-20元', 0, 4, 2);
INSERT INTO `user_topic` VALUES (13, 10088, 230112104, 'x=1', 'x=1', 0, 5, 3);
INSERT INTO `user_topic` VALUES (14, 10091, 221229101, NULL, NULL, 1, 0, 0);

-- ----------------------------
-- View structure for getusertopicinfo
-- ----------------------------
DROP VIEW IF EXISTS `getusertopicinfo`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `getusertopicinfo` AS select count(if((`user_topic`.`favorites` <> 0),1,NULL)) AS `favoritesCount` from `user_topic` where (`user_topic`.`user_account` = 10001);

-- ----------------------------
-- Procedure structure for count_pages
-- ----------------------------
DROP PROCEDURE IF EXISTS `count_pages`;
delimiter ;;
CREATE PROCEDURE `count_pages`(IN `pageSize` int)
BEGIN
		#Routine body goes here...
		#利用concat拼接sql,execute中执行拼接的sql
    set @sqlStr=concat('select ceiling((count(0) /',pageSize,')) As total from detailed_topic');
    PREPARE stmt from @sqlStr;
		EXECUTE stmt; 
-- 		sql语句调用
-- 		CALL count_pages(2,'topic');
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for getFavoritesCount
-- ----------------------------
DROP PROCEDURE IF EXISTS `getFavoritesCount`;
delimiter ;;
CREATE PROCEDURE `getFavoritesCount`(IN `userAccount` int)
BEGIN
	#Routine body goes here...
	#获取用户题目收藏数量
	select count(if((`user_topic`.`favorites` <> 0),1,NULL)) AS `favoritesCount` from `user_topic` where (`user_topic`.`user_account` = userAccount);
	
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for show_page
-- ----------------------------
DROP PROCEDURE IF EXISTS `show_page`;
delimiter ;;
CREATE PROCEDURE `show_page`(IN current_page int,IN page_size int,IN `My_Table` varchar(20))
BEGIN
		#Routine body goes here...
		set @sqlStr=concat('SELECT * FROM ', My_Table," limit ",(current_page-1)*page_size," , ",page_size);
    PREPARE stmt from @sqlStr;
		EXECUTE stmt; 
		-- 		sql语句调用
		-- 		CALL count_pages(2,'topic');
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for show_page_tables
-- ----------------------------
DROP PROCEDURE IF EXISTS `show_page_tables`;
delimiter ;;
CREATE PROCEDURE `show_page_tables`(IN currentPage int,IN pageSize int)
BEGIN
	#Routine body goes here...
	set @sqlStr=concat('SELECT * FROM topic,detailed_topic ',"WHERE topic.topic_id=detailed_topic.topic_id limit ",(currentPage-1)*pageSize," , ",pageSize);
    PREPARE stmt from @sqlStr;
		EXECUTE stmt; 
		-- 		sql语句调用
		-- 		CALL count_pages(2,'topic');

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for show_page_tables_DetailedUser
-- ----------------------------
DROP PROCEDURE IF EXISTS `show_page_tables_DetailedUser`;
delimiter ;;
CREATE PROCEDURE `show_page_tables_DetailedUser`(IN `currentPage` int,IN `pageSize` int)
BEGIN
	#Routine body goes here...
	set @sqlStr=concat('SELECT user_account,nickname,remark,gender,phone,email,address,question_numbers,question_errors,question_true,question_accuracy_rate,login_status,login_id,login_password,login_identity FROM detailed_user,user_login WHERE login_account=user_account limit ',(currentPage-1)*pageSize," , ",pageSize);
	 PREPARE stmt from @sqlStr;
	EXECUTE stmt; 

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for show_tabsLink_User
-- ----------------------------
DROP PROCEDURE IF EXISTS `show_tabsLink_User`;
delimiter ;;
CREATE PROCEDURE `show_tabsLink_User`()
BEGIN
	#Routine body goes here...
	SELECT DISTINCT knowledge_point FROM topic ;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for show_userClient_TopicInfo
-- ----------------------------
DROP PROCEDURE IF EXISTS `show_userClient_TopicInfo`;
delimiter ;;
CREATE PROCEDURE `show_userClient_TopicInfo`(IN `currentPage` int,IN `pageSize` int)
BEGIN
	#Routine body goes here..

	set @showTopic=concat("SELECT detailed_topic_id, knowledge_point,topic_title,frequency,topic_type FROM detailed_topic,topic WHERE detailed_topic.topic_id=topic.topic_id GROUP BY detailed_topic_id asc LIMIT ",(currentPage-1)*pageSize," , ",pageSize);
  PREPARE stmt from @showTopic;
	EXECUTE stmt; 

END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;