API tools faq
paste
Guest User

sql

a guest
Feb 5th, 2017
55
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.08 KB | None | 0 0
raw download clone embed print report
  1. /*
  2. Navicat MySQL Data Transfer
  3.  
  4. Source Server : my sql-conexion
  5. Source Server Version : 50716
  6. Source Host : localhost:3306
  7. Source Database : gestioncentros
  8.  
  9. Target Server Type : MYSQL
  10. Target Server Version : 50716
  11. File Encoding : 65001
  12.  
  13. Date: 2017-02-05 18:18:28
  14. */
  15.  
  16. SET FOREIGN_KEY_CHECKS=0;
  17.  
  18. -- ----------------------------
  19. -- Table structure for centros
  20. -- ----------------------------
  21. DROP TABLE IF EXISTS `centros`;
  22. CREATE TABLE `centros` (
  23. `cod_centro` smallint(6) NOT NULL,
  24. `tipo_centro` char(1) DEFAULT NULL,
  25. `nombre` varchar(30) DEFAULT NULL,
  26. `direccion` varchar(26) DEFAULT NULL,
  27. `telefono` varchar(10) DEFAULT NULL,
  28. `num_plazas` smallint(5) unsigned DEFAULT NULL,
  29. PRIMARY KEY (`cod_centro`)
  30. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  31.  
  32. -- ----------------------------
  33. -- Records of centros
  34. -- ----------------------------
  35. INSERT INTO `centros` VALUES ('10', 'S', 'IES El Quijote', 'Avda. Los Molinos 25', '965-887654', '538');
  36. INSERT INTO `centros` VALUES ('15', 'P', 'CP Los Danzantes', 'C/Las Musas s/n', '985-112322', '250');
  37. INSERT INTO `centros` VALUES ('22', 'S', 'IES Planeta Tierra', 'C/Mina 45', '925-443400', '300');
  38. INSERT INTO `centros` VALUES ('45', 'P', 'CP Manuel Hidalgo', 'C/Granada 5', '926-202310', '220');
  39. INSERT INTO `centros` VALUES ('50', 'S', 'IES Antoñete 1', 'C/Los Toreros 21', '989-406090', '310');
  40. INSERT INTO `centros` VALUES ('60', 'P', 'CP Antoñete 2', 'C/Los Toreros 22', '989-406092', '300');
  41.  
  42. -- ----------------------------
  43. -- Table structure for clientes
  44. -- ----------------------------
  45. DROP TABLE IF EXISTS `clientes`;
  46. CREATE TABLE `clientes` (
  47. `numclie` int(6) unsigned NOT NULL AUTO_INCREMENT,
  48. `nombre` varchar(25) COLLATE utf8_spanish_ci NOT NULL,
  49. `repclie` int(4) NOT NULL,
  50. `limitecredit` decimal(12,2) unsigned zerofill NOT NULL,
  51. PRIMARY KEY (`numclie`),
  52. KEY `repclie` (`repclie`)
  53. ) ENGINE=MyISAM AUTO_INCREMENT=2425 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  54.  
  55. -- ----------------------------
  56. -- Records of clientes
  57. -- ----------------------------
  58. INSERT INTO `clientes` VALUES ('2101', 'Luis García Antón', '106', '0000065000.00');
  59. INSERT INTO `clientes` VALUES ('2102', 'Alvaro Rodríguez', '101', '0000065000.00');
  60. INSERT INTO `clientes` VALUES ('2103', 'Jaime Llorens', '105', '0000050000.00');
  61. INSERT INTO `clientes` VALUES ('2105', 'Antonio Canales', '101', '0000045000.00');
  62. INSERT INTO `clientes` VALUES ('2106', 'Juan Suárez', '102', '0000065000.00');
  63. INSERT INTO `clientes` VALUES ('2107', 'Julián López', '110', '0000035000.00');
  64. INSERT INTO `clientes` VALUES ('2108', 'Julia Antequera', '109', '0000055000.00');
  65. INSERT INTO `clientes` VALUES ('2109', 'Alberto Juanes', '103', '0000025000.00');
  66. INSERT INTO `clientes` VALUES ('2111', 'Cristóbal García', '103', '0000025000.00');
  67. INSERT INTO `clientes` VALUES ('2112', 'María Silva', '108', '0000050000.00');
  68. INSERT INTO `clientes` VALUES ('2113', 'Luisa Maron', '104', '0000020000.00');
  69. INSERT INTO `clientes` VALUES ('2114', 'Cristina Bulini', '102', '0000020000.00');
  70. INSERT INTO `clientes` VALUES ('2115', 'Vicente Martínez', '101', '0000020000.00');
  71. INSERT INTO `clientes` VALUES ('2117', 'Carlos Tena', '106', '0000035000.00');
  72. INSERT INTO `clientes` VALUES ('2118', 'Junipero Álvarez', '108', '0000060000.00');
  73. INSERT INTO `clientes` VALUES ('2119', 'Salomón Bueno', '109', '0000025000.00');
  74. INSERT INTO `clientes` VALUES ('2120', 'Juan Malo', '102', '0000050000.00');
  75. INSERT INTO `clientes` VALUES ('2121', 'Vicente Ríos', '103', '0000045000.00');
  76. INSERT INTO `clientes` VALUES ('2122', 'José Marchante', '105', '0000030000.00');
  77. INSERT INTO `clientes` VALUES ('2123', 'José Libros', '102', '0000040000.00');
  78. INSERT INTO `clientes` VALUES ('2124', 'Juan Bolto', '102', '0000040000.00');
  79.  
  80. -- ----------------------------
  81. -- Table structure for empleados
  82. -- ----------------------------
  83. DROP TABLE IF EXISTS `empleados`;
  84. CREATE TABLE `empleados` (
  85. `numemp` int(4) unsigned NOT NULL AUTO_INCREMENT,
  86. `nombre` varchar(25) COLLATE utf8_spanish_ci NOT NULL,
  87. `edad` int(3) DEFAULT NULL,
  88. `oficina` int(3) DEFAULT NULL,
  89. `titulo` enum('representante','dir ventas','dir general') COLLATE utf8_spanish_ci NOT NULL,
  90. `contrato` date NOT NULL,
  91. `jefe` int(4) DEFAULT NULL,
  92. `cuota` decimal(12,2) DEFAULT NULL,
  93. `ventas` decimal(12,2) DEFAULT NULL,
  94. PRIMARY KEY (`numemp`),
  95. KEY `oficina` (`oficina`),
  96. KEY `jefe` (`jefe`)
  97. ) ENGINE=MyISAM AUTO_INCREMENT=111 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  98.  
  99. -- ----------------------------
  100. -- Records of empleados
  101. -- ----------------------------
  102. INSERT INTO `empleados` VALUES ('101', 'Antonio Viguer', '45', '12', 'representante', '1986-10-20', '104', '300000.00', '305000.00');
  103. INSERT INTO `empleados` VALUES ('102', 'Alvaro Jaumes', '48', '21', 'representante', '1986-12-10', '108', '350000.00', '474000.00');
  104. INSERT INTO `empleados` VALUES ('103', 'Juan Rovira', '29', '12', 'representante', '1987-03-01', '104', '275000.00', '286000.00');
  105. INSERT INTO `empleados` VALUES ('104', 'José González', '33', '12', 'dir ventas', '1987-05-19', '106', '200000.00', '143000.00');
  106. INSERT INTO `empleados` VALUES ('105', 'Vicente Pantalla', '37', '13', 'representante', '1988-02-12', '104', '350000.00', '368000.00');
  107. INSERT INTO `empleados` VALUES ('106', 'Luis Palomo', '52', '11', 'dir general', '1988-06-14', null, '275000.00', '299000.00');
  108. INSERT INTO `empleados` VALUES ('107', 'Jorge Gutiérrez', '49', '22', 'representante', '1988-11-14', '108', '300000.00', '186000.00');
  109. INSERT INTO `empleados` VALUES ('108', 'Ana Bustamante', '62', '21', 'dir ventas', '1989-10-12', '106', '350000.00', '361000.00');
  110. INSERT INTO `empleados` VALUES ('109', 'María Sunta', '31', '11', 'representante', '1991-10-12', '106', '300000.00', '392000.00');
  111. INSERT INTO `empleados` VALUES ('110', 'Juan Victor', '41', null, 'representante', '1990-01-13', '104', null, '76000.00');
  112.  
  113. -- ----------------------------
  114. -- Table structure for oficinas
  115. -- ----------------------------
  116. DROP TABLE IF EXISTS `oficinas`;
  117. CREATE TABLE `oficinas` (
  118. `oficina` int(2) NOT NULL AUTO_INCREMENT,
  119. `ciudad` varchar(12) COLLATE utf8_spanish_ci NOT NULL,
  120. `region` enum('norte','sur','centro','este','oeste') COLLATE utf8_spanish_ci NOT NULL,
  121. `dir` int(8) DEFAULT NULL,
  122. `objetivo` decimal(12,2) DEFAULT NULL,
  123. `ventas` decimal(12,2) DEFAULT NULL,
  124. PRIMARY KEY (`oficina`),
  125. KEY `dir` (`dir`)
  126. ) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  127.  
  128. -- ----------------------------
  129. -- Records of oficinas
  130. -- ----------------------------
  131. INSERT INTO `oficinas` VALUES ('11', 'Valencia', 'este', '106', '575000.00', '693000.00');
  132. INSERT INTO `oficinas` VALUES ('12', 'Alicante', 'este', '104', '800000.00', '735000.00');
  133. INSERT INTO `oficinas` VALUES ('13', 'Castellón', 'este', '105', '350000.00', '368000.00');
  134. INSERT INTO `oficinas` VALUES ('21', 'Badajoz', 'oeste', '105', '725000.00', '836000.00');
  135. INSERT INTO `oficinas` VALUES ('22', 'A Coruña', 'oeste', '108', '300000.00', '186000.00');
  136. INSERT INTO `oficinas` VALUES ('23', 'Madrid', 'centro', '108', null, null);
  137. INSERT INTO `oficinas` VALUES ('24', 'Madrid', 'centro', '108', '250000.00', '150000.00');
  138. INSERT INTO `oficinas` VALUES ('26', 'Pamplona', 'norte', null, null, null);
  139. INSERT INTO `oficinas` VALUES ('28', 'Valencia', 'este', null, '900000.00', null);
  140.  
  141. -- ----------------------------
  142. -- Table structure for pedidos
  143. -- ----------------------------
  144. DROP TABLE IF EXISTS `pedidos`;
  145. CREATE TABLE `pedidos` (
  146. `codigo` int(5) NOT NULL AUTO_INCREMENT,
  147. `numpedido` int(6) NOT NULL,
  148. `fechapedido` date NOT NULL,
  149. `clie` int(6) NOT NULL,
  150. `rep` int(4) NOT NULL,
  151. `fab` enum('aci','rei','fea','bic','imm','qsa') COLLATE utf8_spanish_ci NOT NULL,
  152. `producto` varchar(5) COLLATE utf8_spanish_ci NOT NULL,
  153. PRIMARY KEY (`codigo`),
  154. KEY `clie` (`clie`),
  155. KEY `rep` (`rep`),
  156. KEY `producto` (`producto`)
  157. ) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  158.  
  159. -- ----------------------------
  160. -- Records of pedidos
  161. -- ----------------------------
  162. INSERT INTO `pedidos` VALUES ('1', '110036', '2010-12-20', '2107', '110', 'aci', '4100z');
  163. INSERT INTO `pedidos` VALUES ('2', '110036', '2010-12-20', '2117', '106', 'rei', '2a44l');
  164. INSERT INTO `pedidos` VALUES ('3', '112963', '2011-04-27', '2103', '105', 'aci', '41004');
  165. INSERT INTO `pedidos` VALUES ('4', '112968', '2003-12-29', '2102', '101', 'aci', '41004');
  166. INSERT INTO `pedidos` VALUES ('5', '112975', '2011-01-29', '2111', '103', 'rei', '2a44g');
  167. INSERT INTO `pedidos` VALUES ('6', '112979', '2003-09-29', '2114', '108', 'aci', '4100z');
  168. INSERT INTO `pedidos` VALUES ('7', '112983', '2011-04-27', '2103', '105', 'aci', '41004');
  169. INSERT INTO `pedidos` VALUES ('8', '112987', '2010-12-19', '2103', '105', 'aci', '4100y');
  170. INSERT INTO `pedidos` VALUES ('9', '112989', '2011-11-27', '2101', '106', 'fea', '114');
  171. INSERT INTO `pedidos` VALUES ('10', '112992', '2004-04-01', '2118', '108', 'aci', '41002');
  172. INSERT INTO `pedidos` VALUES ('11', '112993', '2011-02-25', '2106', '102', 'rei', '2a45c');
  173. INSERT INTO `pedidos` VALUES ('12', '112997', '2011-03-22', '2124', '107', 'bic', '41003');
  174. INSERT INTO `pedidos` VALUES ('13', '113003', '2011-01-23', '2108', '109', 'imm', '779c');
  175. INSERT INTO `pedidos` VALUES ('14', '113007', '2010-12-19', '2112', '108', 'imm', '773c');
  176. INSERT INTO `pedidos` VALUES ('15', '113012', '2011-04-22', '2111', '105', 'aci', '41003');
  177. INSERT INTO `pedidos` VALUES ('16', '113013', '2011-07-24', '2118', '108', 'bic', '41003');
  178. INSERT INTO `pedidos` VALUES ('17', '113024', '2011-06-21', '2114', '108', 'qsa', 'xk47');
  179. INSERT INTO `pedidos` VALUES ('18', '113027', '2011-01-23', '2103', '105', 'aci', '41002');
  180. INSERT INTO `pedidos` VALUES ('19', '113034', '2011-10-23', '2107', '110', 'rei', '2a45c');
  181. INSERT INTO `pedidos` VALUES ('20', '113042', '2010-12-19', '2113', '101', 'rei', '2a44r');
  182. INSERT INTO `pedidos` VALUES ('21', '113045', '2011-06-19', '2112', '108', 'rei', '2a44r');
  183. INSERT INTO `pedidos` VALUES ('22', '113048', '2011-01-20', '2120', '102', 'imm', '779c');
  184. INSERT INTO `pedidos` VALUES ('23', '113049', '2011-03-22', '2118', '108', 'qsa', 'xk47');
  185. INSERT INTO `pedidos` VALUES ('24', '113051', '2011-06-23', '2118', '108', 'qsa', 'xk47');
  186. INSERT INTO `pedidos` VALUES ('25', '113055', '2004-03-18', '2108', '101', 'aci', '4100x');
  187. INSERT INTO `pedidos` VALUES ('26', '113057', '2011-10-19', '2111', '103', 'aci', '4100x');
  188. INSERT INTO `pedidos` VALUES ('27', '113058', '2003-06-21', '2108', '109', 'fea', '112');
  189. INSERT INTO `pedidos` VALUES ('28', '113062', '2011-06-21', '2124', '107', 'bic', '41003');
  190. INSERT INTO `pedidos` VALUES ('29', '113065', '2011-05-21', '2106', '102', 'qsa', 'xk47');
  191. INSERT INTO `pedidos` VALUES ('30', '113069', '2011-07-19', '2109', '107', 'imm', '773c');
  192.  
  193. -- ----------------------------
  194. -- Table structure for personal
  195. -- ----------------------------
  196. DROP TABLE IF EXISTS `personal`;
  197. CREATE TABLE `personal` (
  198. `cod_centro` smallint(6) NOT NULL,
  199. `dni` int(10) unsigned NOT NULL,
  200. `apellidos` varchar(30) DEFAULT NULL,
  201. `funcion` varchar(15) DEFAULT NULL,
  202. `salario` float(7,2) DEFAULT NULL,
  203. PRIMARY KEY (`dni`),
  204. KEY `cod_centro` (`cod_centro`),
  205. CONSTRAINT `personal_ibfk_1` FOREIGN KEY (`cod_centro`) REFERENCES `centros` (`cod_centro`) ON UPDATE CASCADE
  206. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  207.  
  208. -- ----------------------------
  209. -- Records of personal
  210. -- ----------------------------
  211. INSERT INTO `personal` VALUES ('10', '1010100', 'Gómez Bueno, Elisa', 'LIMPIADORA', '1200.00');
  212. INSERT INTO `personal` VALUES ('10', '1212120', 'García García, Sabina', 'LIMPIADORA', '1200.00');
  213. INSERT INTO `personal` VALUES ('10', '1313130', 'Ruano Cerezo, Manuel', 'ADMINISTRATIVO', '1800.00');
  214. INSERT INTO `personal` VALUES ('15', '1414140', 'Albarrán Serrano, Alicia', 'ADMINISTRATIVO', '1800.00');
  215. INSERT INTO `personal` VALUES ('15', '1515150', 'Muñoz Rey, Felicia', 'ADMINISTRATIVO', '1800.00');
  216. INSERT INTO `personal` VALUES ('22', '1616160', 'Marín Marín, Pedro', 'ADMINISTRATIVO', '1800.00');
  217. INSERT INTO `personal` VALUES ('22', '1717170', 'Peinado Gil, Elena', 'CONSERJE', '1750.00');
  218. INSERT INTO `personal` VALUES ('45', '1818180', 'Sarro Molina, Carmen', 'CONSERJE', '1750.00');
  219. INSERT INTO `personal` VALUES ('22', '1919190', 'Flores Pérez, Fernando', 'CONSERJE', '1300.00');
  220.  
  221. -- ----------------------------
  222. -- Table structure for productos
  223. -- ----------------------------
  224. DROP TABLE IF EXISTS `productos`;
  225. CREATE TABLE `productos` (
  226. `idfab` enum('fea','rei','aci','bic','imm','qsa') COLLATE utf8_spanish_ci NOT NULL,
  227. `idproducto` varchar(5) COLLATE utf8_spanish_ci NOT NULL,
  228. `descripcion` varchar(15) COLLATE utf8_spanish_ci NOT NULL,
  229. `precio` decimal(8,2) NOT NULL,
  230. `existencias` int(3) NOT NULL,
  231. PRIMARY KEY (`idproducto`)
  232. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  233.  
  234. -- ----------------------------
  235. -- Records of productos
  236. -- ----------------------------
  237. INSERT INTO `productos` VALUES ('fea', '112', 'cubo', '148.00', '115');
  238. INSERT INTO `productos` VALUES ('fea', '114', 'cubo', '243.00', '15');
  239. INSERT INTO `productos` VALUES ('rei', '2a44g', 'pas', '350.00', '14');
  240. INSERT INTO `productos` VALUES ('rei', '2a44l', 'bomba l', '4500.00', '12');
  241. INSERT INTO `productos` VALUES ('rei', '2a44r', 'bomba r', '4500.00', '12');
  242. INSERT INTO `productos` VALUES ('rei', '2a45c', 'junta', '79.00', '210');
  243. INSERT INTO `productos` VALUES ('aci', '41001', 'arandela', '58.00', '277');
  244. INSERT INTO `productos` VALUES ('aci', '41002', 'bisagra', '80.00', '167');
  245. INSERT INTO `productos` VALUES ('aci', '41003', 'art t3', '112.00', '207');
  246. INSERT INTO `productos` VALUES ('aci', '41004', 'art t4', '123.00', '139');
  247. INSERT INTO `productos` VALUES ('bic', '41005', 'manivela', '652.00', '3');
  248. INSERT INTO `productos` VALUES ('aci', '4100x', 'junta', '26.00', '37');
  249. INSERT INTO `productos` VALUES ('aci', '1400y', 'extractor', '2888.00', '25');
  250. INSERT INTO `productos` VALUES ('aci', '4100z', 'mont', '2625.00', '28');
  251. INSERT INTO `productos` VALUES ('bic', '41089', 'rodamiento', '225.00', '78');
  252. INSERT INTO `productos` VALUES ('bic', '41672', 'plato', '180.00', '0');
  253. INSERT INTO `productos` VALUES ('imm', '773c', 'reostato', '795.00', '28');
  254. INSERT INTO `productos` VALUES ('imm', '775c', 'reostato 2', '1425.00', '5');
  255. INSERT INTO `productos` VALUES ('imm', '779c', 'reostato 3', '1875.00', '0');
  256. INSERT INTO `productos` VALUES ('imm', '887h', 'caja clavos', '54.00', '223');
  257. INSERT INTO `productos` VALUES ('imm', '887p', 'perno', '25.00', '24');
  258. INSERT INTO `productos` VALUES ('imm', '887x', 'manivela', '475.00', '32');
  259. INSERT INTO `productos` VALUES ('qsa', 'xk47', 'red', '355.00', '38');
  260. INSERT INTO `productos` VALUES ('qsa', 'xk48', 'red', '134.00', '203');
  261. INSERT INTO `productos` VALUES ('qsa', 'xk48a', 'red', '117.00', '37');
  262.  
  263. -- ----------------------------
  264. -- Table structure for profesores
  265. -- ----------------------------
  266. DROP TABLE IF EXISTS `profesores`;
  267. CREATE TABLE `profesores` (
  268. `cod_centro` smallint(6) NOT NULL,
  269. `dni` int(10) unsigned NOT NULL,
  270. `apellidos` varchar(30) DEFAULT NULL,
  271. `especialidad` varchar(16) DEFAULT NULL,
  272. PRIMARY KEY (`dni`),
  273. KEY `cod_centro` (`cod_centro`),
  274. CONSTRAINT `profesores_ibfk_1` FOREIGN KEY (`cod_centro`) REFERENCES `centros` (`cod_centro`) ON UPDATE CASCADE
  275. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  276.  
  277. -- ----------------------------
  278. -- Records of profesores
  279. -- ----------------------------
  280. INSERT INTO `profesores` VALUES ('10', '2222222', 'Martínez Salas, Fernando', 'INFORMÁTICA');
  281. INSERT INTO `profesores` VALUES ('10', '3333333', 'Bueno Zarco, Elisa', 'MATEMÁTICAS');
  282. INSERT INTO `profesores` VALUES ('10', '4444444', 'Montes García, M.Pilar', 'MATEMÁTICAS');
  283. INSERT INTO `profesores` VALUES ('15', '5555555', 'Ramos Ruiz, Luis', 'LENGUA');
  284. INSERT INTO `profesores` VALUES ('15', '6666666', 'Rivera Silvestre, Ana', 'DIBUJO');
  285. INSERT INTO `profesores` VALUES ('15', '7777777', 'De Lucas Fdez, M.Angel', 'LENGUA');
  286. INSERT INTO `profesores` VALUES ('22', '8888888', 'Ruiz Lafuente, Manuel', 'MATEMÁTICAS');
  287. INSERT INTO `profesores` VALUES ('45', '9999999', 'Serrano Laguía, María', 'INFORMÁTICA');
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!