sk39.sql 4.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132
  1. /*
  2. Navicat Premium Data Transfer
  3. Source Server : localhost_3306
  4. Source Server Type : MySQL
  5. Source Server Version : 50724
  6. Source Host : localhost:3306
  7. Source Schema : sk39
  8. Target Server Type : MySQL
  9. Target Server Version : 50724
  10. File Encoding : 65001
  11. Date: 19/11/2019 14:03:43
  12. */
  13. SET NAMES utf8mb4;
  14. SET FOREIGN_KEY_CHECKS = 0;
  15. -- ----------------------------
  16. -- Table structure for image
  17. -- ----------------------------
  18. DROP TABLE IF EXISTS `image`;
  19. CREATE TABLE `image` (
  20. `id` int(11) NOT NULL COMMENT '图片ID',
  21. `uid` int(11) NOT NULL COMMENT '图片所属用户ID',
  22. `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '图片url',
  23. PRIMARY KEY (`id`) USING BTREE
  24. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  25. -- ----------------------------
  26. -- Records of image
  27. -- ----------------------------
  28. INSERT INTO `image` VALUES (1, 1, 'images/Sunset.jpg');
  29. INSERT INTO `image` VALUES (2, 1, 'images/19185_en_1.jpg');
  30. INSERT INTO `image` VALUES (3, 2, 'images/19201_en_1.jpg');
  31. INSERT INTO `image` VALUES (4, 2, 'images/Blue hills.jpg');
  32. INSERT INTO `image` VALUES (5, 3, 'images/19230_en_1.jpg');
  33. INSERT INTO `image` VALUES (6, 3, 'images/19185_en_1.jpg');
  34. INSERT INTO `image` VALUES (7, 4, 'images/Water lilies.jpg');
  35. INSERT INTO `image` VALUES (8, 4, 'images/19542_en_1.jpg');
  36. INSERT INTO `image` VALUES (9, 5, 'images/Winter.jpg');
  37. INSERT INTO `image` VALUES (10, 5, 'images/19666_en_1.jpg');
  38. INSERT INTO `image` VALUES (11, 2, 'images/19889_en_1.jpg');
  39. -- ----------------------------
  40. -- Table structure for setting
  41. -- ----------------------------
  42. DROP TABLE IF EXISTS `setting`;
  43. CREATE TABLE `setting` (
  44. `id` int(11) NOT NULL COMMENT '设置ID',
  45. `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '键',
  46. `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '值',
  47. PRIMARY KEY (`id`) USING BTREE
  48. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  49. -- ----------------------------
  50. -- Records of setting
  51. -- ----------------------------
  52. INSERT INTO `setting` VALUES (1, 'GROUP_NAME', '小组名称');
  53. -- ----------------------------
  54. -- Table structure for user
  55. -- ----------------------------
  56. DROP TABLE IF EXISTS `user`;
  57. CREATE TABLE `user` (
  58. `id` int(11) NOT NULL COMMENT '用户ID',
  59. `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  60. `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '班级',
  61. `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系方式',
  62. PRIMARY KEY (`id`) USING BTREE
  63. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  64. -- ----------------------------
  65. -- Records of user
  66. -- ----------------------------
  67. INSERT INTO `user` VALUES (1, '张三', '17卫星维修G5-2', '13012345678');
  68. INSERT INTO `user` VALUES (2, '李四', '16航母制造G5', '13987654321');
  69. INSERT INTO `user` VALUES (3, '王五', '18飞机维修G3', '13579135791');
  70. INSERT INTO `user` VALUES (4, '李华', '16航母制造G5', '12345678901');
  71. INSERT INTO `user` VALUES (5, '小明', '17卫星维修G5-1', '13987654321');
  72. -- ----------------------------
  73. -- Table structure for video
  74. -- ----------------------------
  75. DROP TABLE IF EXISTS `video`;
  76. CREATE TABLE `video` (
  77. `id` int(11) NOT NULL COMMENT '视频ID',
  78. `uid` int(11) NOT NULL COMMENT '视频所属用户ID',
  79. `video` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频URL',
  80. PRIMARY KEY (`id`) USING BTREE
  81. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  82. -- ----------------------------
  83. -- Records of video
  84. -- ----------------------------
  85. INSERT INTO `video` VALUES (1, 1, 'videos/mov_bbb.mp4');
  86. INSERT INTO `video` VALUES (2, 1, 'videos/movie.mp4');
  87. INSERT INTO `video` VALUES (3, 2, 'videos/movie.mp4');
  88. INSERT INTO `video` VALUES (4, 2, 'videos/mov_bbb.mp4');
  89. INSERT INTO `video` VALUES (5, 3, 'videos/movie.mp4');
  90. INSERT INTO `video` VALUES (6, 3, 'videos/mov_bbb.mp4');
  91. INSERT INTO `video` VALUES (7, 4, 'videos/mov_bbb.mp4');
  92. INSERT INTO `video` VALUES (8, 4, 'videos/movie.mp4');
  93. INSERT INTO `video` VALUES (9, 5, 'videos/mov_bbb.mp4');
  94. INSERT INTO `video` VALUES (10, 5, 'videos/movie.mp4');
  95. -- ----------------------------
  96. -- Procedure structure for get_all
  97. -- ----------------------------
  98. DROP PROCEDURE IF EXISTS `get_all`;
  99. delimiter ;;
  100. CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all`()
  101. BEGIN
  102. #Routine body goes here...
  103. # 获取setting表
  104. SELECT `key`,`value` FROM setting;
  105. # 获取用户表
  106. SELECT * FROM user;
  107. # 获取图片
  108. SELECT * FROM image;
  109. # 获取视频
  110. SELECT * FROM video;
  111. END
  112. ;;
  113. delimiter ;
  114. SET FOREIGN_KEY_CHECKS = 1;