{"id":955,"date":"2016-06-10T23:36:43","date_gmt":"2016-06-10T23:36:43","guid":{"rendered":"http:\/\/hardsoftsecurity.es\/?p=955"},"modified":"2016-06-10T23:36:43","modified_gmt":"2016-06-10T23:36:43","slug":"bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones","status":"publish","type":"post","link":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/","title":{"rendered":"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones."},"content":{"rendered":"

Hola buenas hoy os traigo un peque\u00f1o ejercicio de bases de datos relacionales, el cual os voy a ense\u00f1ar el resultado del c\u00f3digo programado.<\/p>\n

\"mysql-logo-1329320869\"<\/p>\n

Comenzamos con el c\u00f3digo, la primera parte que mostrare ser\u00e1 el dise\u00f1o f\u00edsico de la base de datos:<\/p>\n

\/**********************************************************************************************************************************************************************\/<\/p>\n

\/*********************************************** \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 F\u00cdSICO \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0*********************************************************************\/
\nDROP TABLE IF EXISTS DDM_TUTORES;
\nCREATE TABLE IF NOT EXISTS DDM_TUTORES(
\nid INT PRIMARY KEY,
\nNombreResponsable VARCHAR(255) NOT NULL,
\nParentesco VARCHAR(50) NOT NULL,
\nTelefonoResponsable CHAR(9) NOT NULL
\n)ENGINE=INNODB CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;<\/p>\n

DESCRIBE DDM_TUTORES;<\/p>\n

\"Screenshot_1\"<\/p>\n

DROP TABLE IF EXISTS DDM_ALUMNOS;
\nCREATE TABLE IF NOT EXISTS DDM_ALUMNOS(
\nid INT PRIMARY KEY,
\nNombreAlumno VARCHAR(250) NOT NULL,
\nFechaNacimiento DATE NOT NULL,
\ntutor INT NOT NULL,
\nnumexpedientes INT NOT NULL DEFAULT 0,
\nCONSTRAINT FK1ALUMNOSTUTORES FOREIGN KEY(tutor) REFERENCES DDM_TUTORES(id) ON UPDATE CASCADE ON DELETE NO ACTION
\n)ENGINE=INNODB CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;<\/p>\n

DESCRIBE DDM_ALUMNOS;<\/p>\n

\"Screenshot_2\"<\/p>\n

DROP TABLE IF EXISTS DDM_EXPEDIENTES;
\nCREATE TABLE IF NOT EXISTS DDM_EXPEDIENTES(
\nid INT PRIMARY KEY,
\nalumno INT NOT NULL,
\nFechaExpediente DATE NOT NULL,
\nMotivoExpediente VARCHAR(250) NOT NULL,
\nCONSTRAINT FK2EXPEDIENTES FOREIGN KEY(alumno) REFERENCES DDM_ALUMNOS(id) ON UPDATE CASCADE ON DELETE NO ACTION
\n)ENGINE=INNODB CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;<\/p>\n

DESCRIBE DDM_EXPEDIENTES;<\/p>\n

\"Screenshot_3\"<\/p>\n

DROP TABLE IF EXISTS DDM_TOTALES;
\nCREATE TABLE IF NOT EXISTS DDM_TOTALES(
\nid INT PRIMARY KEY,
\ntotalumnos INT NOT NULL DEFAULT 0,
\ntotexpedientes INT NOT NULL DEFAULT 0,
\ntottutores INT NOT NULL DEFAULT 0
\n)ENGINE=INNODB CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;<\/p>\n

DESCRIBE DDM_TOTALES;<\/p>\n

INSERT INTO DDM_TOTALES(id) VALUES
\n(1);<\/p>\n

\"Screenshot_4\"<\/p>\n

\/**********************************************************************************************************************************************************************\/<\/p>\n

\/*********************************************** \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 TRIGGERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0****************************************************************\/<\/p>\n

DROP TRIGGER IF EXISTS CUENTAEXPEDIENTES;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE TRIGGER CUENTAEXPEDIENTES AFTER INSERT ON DDM_EXPEDIENTES
\nFOR EACH ROW
\nBEGIN
\nUPDATE DDM_ALUMNOS
\nSET numexpedientes = numexpedientes + 1
\nWHERE DDM_ALUMNOS.id = NEW.alumno;<\/p>\n

UPDATE DDM_TOTALES
\nSET totexpedientes = totexpedientes + 1
\nWHERE DDM_TOTALES.id = 1;<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

\/* ***********************************************************************************************\/
\nDROP TRIGGER IF EXISTS CUENTATUTORES;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE TRIGGER CUENTATUTORES AFTER INSERT ON DDM_TUTORES
\nFOR EACH ROW
\nBEGIN
\nUPDATE DDM_TOTALES
\nSET tottutores = tottutores + 1
\nWHERE DDM_TOTALES.id = 1;<\/p>\n

END \/\/<\/p>\n

DELIMITER ;
\n\/* ***********************************************************************************************\/
\nDROP TRIGGER IF EXISTS CUENTAALUMNOS;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE TRIGGER CUENTAALUMNOS AFTER INSERT ON DDM_ALUMNOS
\nFOR EACH ROW
\nBEGIN
\nUPDATE DDM_TOTALES
\nSET totalumnos = totalumnos + 1
\nWHERE DDM_TOTALES.id = 1;<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

\"Screenshot_5\"<\/p>\n

 <\/p>\n

 <\/p>\n

\/**********************************************************************************************************************************************************************\/<\/p>\n

\/*********************************************** \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 INSERTS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0****************************************************************\/
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(1,’Alberto Grau L\u00f3pez’,’Padre’,’600600600′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(2,’Ana M\u00e9ndez Sanz’,’Madre’,’612612612′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(3,’Luc\u00eda \u00c1lvarez Rilo’,’Madre’,’699699699′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(4,’Antonio Abad P\u00e9rez’,’Padre’,’634634634′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(5,’Elena P\u00e9rez P\u00e9rez’,’Madre’,’600700800′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(6,’Alicia V\u00e1zquez Egea’,’Madre’,’654654654′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(7,’Alberto L\u00f3pez Gala’,’Tutor Legal’,’678678678′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(8,’Jos\u00e9 Mateo Soto’,’Padre’,’677677677′);
\nINSERT INTO DDM_TUTORES(id, NombreResponsable, Parentesco, TelefonoResponsable) VALUES
\n(9,’Rosa Aledo Mu\u00f1oz’,’Madre’,’611611611′);
\nSELECT * FROM DDM_TUTORES;<\/p>\n

\"Screenshot_6\"
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(1,’Alba Grau P\u00e9rez’,’1998-02-05′,’1′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(2,’Pablo Gea Sanz’,’1997-03-19′,’2′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(3,’Rafael Gil Rilo’,’1997-04-01′,’3′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(4,’Ana Abad Lara’,’1998-01-03′,’4′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(5,’Eva Abad Lara’,’1999-07-20′,’4′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(6,’Carla Rojo Sala’,’2000-05-05′,’5′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(7,’Jos\u00e9 Pardo Olmo’,’2000-10-19′,’6′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(8,’Beatriz Vera Cobos’,’1999-12-12′,’7′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(9,’Pablo Mateo Lara’,’2000-08-14′,’8′);
\nINSERT INTO DDM_ALUMNOS(id, NombreAlumno, FechaNacimiento, tutor) VALUES
\n(10,’Carlos Pino Sabina’,’1999-06-06′,’9′);<\/p>\n

SELECT * FROM DDM_ALUMNOS;<\/p>\n

\"Screenshot_7\"
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(1,’1′,’2013-10-14′,’Agresi\u00f3n verbal a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(2,’2′,’2014-01-27′,’Agresi\u00f3n f\u00edsica a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(3,’3′,’2014-02-20′,’Hurto de material del centro’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(4,’4′,’2014-02-26′,’agresi\u00f3n verbal a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(5,’5′,’2014-03-02′,’Rotura del mobiliario de clase’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(6,’6′,’2014-05-03′,’Agresi\u00f3n verbal a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(7,’1′,’2015-01-23′,’Agresi\u00f3n verbal a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(8,’7′,’2015-02-03′,’Rotura del mobiliario de clase’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(9,’8′,’2015-02-17′,’Agresi\u00f3n f\u00edsica a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(10,’9′,’2015-03-07′,’Hurto de material del centro’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(11,’10’,’2015-04-15′,’Agresi\u00f3n verbal a un compa\u00f1ero’);
\nINSERT INTO DDM_EXPEDIENTES(id, alumno, FechaExpediente, MotivoExpediente) VALUES
\n(12,’1′,’2015-05-23′,’Hurto de material del centro’);<\/p>\n

SELECT * FROM DDM_EXPEDIENTES;<\/p>\n

SELECT * FROM DDM_ALUMNOS;<\/p>\n

SELECT * FROM DDM_TOTALES;<\/p>\n

\"Screenshot_8\"<\/p>\n

 <\/p>\n

TABLA DDM_ALUMNOS CON EL FUNCIONAMIENTO DE LOS TRIGGER<\/strong>:<\/p>\n

\"Screenshot_9\"<\/p>\n

TABLA DDM_TOTALES CON EL FUNCIONAMIENTO DE TODOS LOS TRIGGER<\/strong>:<\/p>\n

\"Screenshot_10\"<\/p>\n

 <\/p>\n

 <\/p>\n

\/**********************************************************************************************************************************************************************\/<\/p>\n

\/*********************************************** \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CONSULTAS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0****************************************************************\/<\/p>\n

SELECT DDM_ALUMNOS.NombreAlumno AS ‘nombre’, DDM_ALUMNOS.FechaNacimiento AS ‘fechanacimiento’, DDM_EXPEDIENTES.FechaExpediente AS ‘fecha’, DDM_EXPEDIENTES.MotivoExpediente AS ‘motivo’, DDM_TUTORES.NombreResponsable AS ‘nombre’, DDM_TUTORES.Parentesco AS ‘parentesco’, DDM_TUTORES.TelefonoResponsable AS ‘telefono’
\nFROM DDM_ALUMNOS, DDM_EXPEDIENTES, DDM_TUTORES
\nWHERE DDM_ALUMNOS.id = DDM_EXPEDIENTES.alumno AND DDM_TUTORES.id = DDM_ALUMNOS.tutor;<\/p>\n

\"Screenshot_11\"<\/p>\n

SELECT COUNT(DDM_ALUMNOS.id) AS ‘N\u00ba TOTAL DE ALUMNOS’, A.E AS ‘N\u00ba TOTAL DE EXPEDIENTES’
\nFROM DDM_ALUMNOS, (SELECT COUNT(DDM_EXPEDIENTES.id) AS ‘E’
\nFROM DDM_EXPEDIENTES) A;<\/p>\n

\"Screenshot_12\"<\/p>\n

SELECT COUNT(DDM_EXPEDIENTES.id) AS ‘N\u00ba TOTAL DE EXPEDIENTES’, C.A AS ‘N\u00ba DE EXPEDIENTES 2014’, Q.A AS ‘N\u00ba DE EXPEDIENTES 2015’
\nFROM DDM_EXPEDIENTES, (SELECT COUNT(DDM_EXPEDIENTES.id) AS ‘A’
\nFROM DDM_EXPEDIENTES
\nWHERE YEAR(DDM_EXPEDIENTES.FechaExpediente) = ‘2014’) C, (SELECT COUNT(DDM_EXPEDIENTES.id) AS ‘A’
\nFROM DDM_EXPEDIENTES
\nWHERE YEAR(DDM_EXPEDIENTES.FechaExpediente) = ‘2015’) Q;<\/p>\n

\"Screenshot_13\"<\/p>\n

SELECT COUNT(DDM_ALUMNOS.id) AS ‘N\u00ba TOTAL DE ALUMNOS’, S.A AS ‘N\u00ba DE ALUMNOS MAYORES DE EDAD’, ROUND ((S.A\/COUNT(DDM_ALUMNOS.NombreAlumno))*100,2) AS ‘PORCENTAJE’
\nFROM DDM_ALUMNOS, (SELECT COUNT(DDM_ALUMNOS.NombreAlumno) AS ‘A’
\nFROM DDM_ALUMNOS
\nWHERE TRUNCATE((TO_DAYS(SYSDATE()) – TO_DAYS(DDM_ALUMNOS.Fechanacimiento)) \/ 365.25,0) > 17) S;<\/p>\n

\"Screenshot_14\"<\/p>\n

SELECT DDM_ALUMNOS.NombreAlumno AS ‘Alumno M\u00e1s Viejo’, DDM_ALUMNOS.FechaNacimiento AS ‘Nacimiento Alumno M\u00e1s Viejo’, A.B AS ‘Alumno M\u00e1s Joven’, A.C AS ‘Nacimiento Alumno M\u00e1s Joven’
\nFROM DDM_ALUMNOS, (SELECT DDM_ALUMNOS.NombreAlumno AS ‘B’, DDM_ALUMNOS.FechaNacimiento AS ‘C’
\nFROM DDM_ALUMNOS
\nWHERE DDM_ALUMNOS.FechaNacimiento IN(SELECT MAX(DDM_ALUMNOS.FechaNacimiento)
\nFROM DDM_ALUMNOS)) A
\nWHERE DDM_ALUMNOS.FechaNacimiento IN(SELECT MIN(DDM_ALUMNOS.FechaNacimiento)
\nFROM DDM_ALUMNOS);<\/p>\n

\"Screenshot_15\"<\/p>\n

SELECT DDM_TUTORES.NombreResponsable , DDM_TUTORES.parentesco, COUNT(DDM_ALUMNOS.tutor) AS ‘N\u00ba de DDM_ALUMNOS tutelados’
\nFROM DDM_TUTORES,DDM_ALUMNOS
\nWHERE DDM_TUTORES.id = DDM_ALUMNOS.tutor
\nGROUP BY DDM_TUTORES.NombreResponsable, DDM_TUTORES.parentesco
\nHAVING COUNT(DDM_ALUMNOS.tutor) = (SELECT MAX(GG.A) FROM (
\nSELECT COUNT(DDM_ALUMNOS.tutor) AS A
\nFROM DDM_TUTORES,DDM_ALUMNOS
\nWHERE DDM_TUTORES.id = DDM_ALUMNOS.tutor
\nGROUP BY DDM_TUTORES.NombreResponsable, DDM_TUTORES.parentesco) GG);<\/p>\n

\"Screenshot_16\"<\/p>\n

\/**********************************************************************************************************************************************************************\/<\/p>\n

\/*********************************************** \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0FUNCIONES Y PROCEDURES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ****************************************************************\/<\/p>\n

DROP PROCEDURE IF EXISTS NUMEXPEDIENTES;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE PROCEDURE NUMEXPEDIENTES(IN nombre VARCHAR(250), OUT expediente TEXT)
\nBEGIN<\/p>\n

DECLARE motivo TEXT DEFAULT “”;
\nDECLARE contador INT DEFAULT 0;
\nDECLARE numexp INT DEFAULT 0;<\/p>\n

SET numexp = (SELECT COUNT(DDM_EXPEDIENTES.alumno)
\nFROM DDM_EXPEDIENTES, DDM_ALUMNOS
\nWHERE DDM_EXPEDIENTES.alumno = DDM_ALUMNOS.id AND DDM_ALUMNOS.NombreAlumno LIKE nombre);
\nIF numexp > 0 THEN
\nSET expediente = CONCAT(‘El alumno ‘,nombre,’ TIENE ‘,numexp,’ expedientes\\n’);
\nELSE
\nSET expediente = CONCAT(‘El alumno ‘,nombre,’ tiene’,numexp,’ expedientes’);
\nEND IF;<\/p>\n

WHILE contador < numexp DO
\nSET motivo = (SELECT DDM_EXPEDIENTES.MotivoExpediente
\nFROM DDM_EXPEDIENTES, DDM_ALUMNOS
\nWHERE DDM_EXPEDIENTES.alumno = DDM_ALUMNOS.id AND DDM_ALUMNOS.NombreAlumno LIKE nombre LIMIT contador,1);<\/p>\n

SET expediente = CONCAT(expediente,’….’,contador+1,’ +’,motivo,’\\n’);
\nSET contador = contador + 1;<\/p>\n

END WHILE;<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

CALL NUMEXPEDIENTES(‘Alba Grau P\u00e9rez’,@a);
\nSELECT @a;<\/p>\n

CALL NUMEXPEDIENTES(‘Pablo Gea Sanz’,@a);
\nSELECT @a;<\/p>\n

CALL NUMEXPEDIENTES(‘Rafael Gil Rilo’,@a);
\nSELECT @a;<\/p>\n

\"Screenshot_17\"<\/p>\n

DROP PROCEDURE IF EXISTS TUTORES;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE PROCEDURE TUTORES(IN nombre VARCHAR(250), OUT resultado TEXT)
\nBEGIN<\/p>\n

DECLARE tutor TEXT DEFAULT “”;
\nDECLARE contador INT DEFAULT 0;
\nDECLARE numtute INT DEFAULT 0;
\nSET numtute = (SELECT COUNT(DDM_ALUMNOS.id)
\nFROM DDM_ALUMNOS, DDM_TUTORES
\nWHERE DDM_ALUMNOS.tutor = DDM_TUTORES.id AND DDM_TUTORES.NombreResponsable LIKE nombre);<\/p>\n

IF numtute > 0 THEN
\nSET resultado = CONCAT(‘El tutor: ‘,nombre,’ tiene ‘,numtute,’ tutelados.\\n’);
\nELSE
\nSET resultado = CONCAT(‘El tutor: ‘,nombre,’ tiene ‘,numtute,’ tutelado.\\n’);
\nEND IF;<\/p>\n

WHILE contador < numtute DO
\nSET tutor = (SELECT DDM_ALUMNOS.NombreAlumno
\nFROM DDM_ALUMNOS, DDM_TUTORES
\nWHERE DDM_ALUMNOS.tutor = DDM_TUTORES.id AND DDM_TUTORES.NombreResponsable LIKE nombre LIMIT contador, 1);
\nSET resultado = CONCAT(resultado,’…’,contador + 1,’ +’,tutor,’\\n’);
\nSET contador = contador + 1;<\/p>\n

END WHILE;
\nEND \/\/<\/p>\n

DELIMITER ;<\/p>\n

CALL TUTORES(‘Alberto L\u00f3pez Gala’,@b);
\nSELECT @b;<\/p>\n

CALL TUTORES(‘Antonio Abad P\u00e9rez’,@b);
\nSELECT @b;<\/p>\n

CALL TUTORES(‘Alicia V\u00e1zquez Egea’,@b);
\nSELECT @b;<\/p>\n

CALL TUTORES(‘Jos\u00e9 Mateo Soto’,@b);
\nSELECT @b;<\/p>\n

\"Screenshot_18\"<\/p>\n

DROP FUNCTION IF EXISTS FTUTORES;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE FUNCTION FTUTORES( nombre VARCHAR(250) )
\nRETURNS TEXT<\/p>\n

BEGIN<\/p>\n

DECLARE alum TEXT DEFAULT “”;
\nDECLARE contador INT DEFAULT 0;
\nDECLARE numalu INT DEFAULT 0;
\nDECLARE resultado TEXT DEFAULT “”;<\/p>\n

SET numalu = (SELECT COUNT(DDM_TUTORES.id)
\nFROM DDM_TUTORES, DDM_ALUMNOS
\nWHERE DDM_ALUMNOS.tutor = DDM_TUTORES.id AND DDM_TUTORES.NombreResponsable LIKE nombre);<\/p>\n

IF numalu > 0 THEN
\nSET resultado = CONCAT(‘El tutor: ‘,nombre,’ tiene ‘,numalu,’ tutelados\\n’);
\nELSE
\nSET resultado = CONCAT(‘El tutor: ‘,nombre,’ no tiene ningun alumno tutelado\\n’);
\nEND IF;<\/p>\n

WHILE contador < numalu DO
\nSET alum = (SELECT DDM_ALUMNOS.NombreAlumno
\nFROM DDM_ALUMNOS, DDM_TUTORES
\nWHERE DDM_ALUMNOS.tutor = DDM_TUTORES.id AND DDM_TUTORES.NombreResponsable LIKE nombre LIMIT contador, 1);
\nSET resultado = CONCAT(resultado,’—>’,contador + 1,’ +’,alum,’\\n’);
\nSET contador = contador + 1;
\nEND WHILE;<\/p>\n

RETURN(resultado);<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

SELECT FTUTORES(‘Jos\u00e9 Mateo Soto’);<\/p>\n

SELECT FTUTORES(‘Alicia V\u00e1zquez Egea’);<\/p>\n

SELECT FTUTORES(‘Antonio Abad P\u00e9rez’);<\/p>\n

SELECT FTUTORES(‘Alberto L\u00f3pez Gala’);<\/p>\n

\"Screenshot_19\"<\/p>\n

\/* ************************************************************************************************************ *\/
\n\/* ************************************ CASE **************************************************************** *\/<\/p>\n

DROP FUNCTION IF EXISTS NOMBRETUTOR;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE FUNCTION NOMBRETUTOR(nombre INT)<\/p>\n

RETURNS TEXT<\/p>\n

BEGIN<\/p>\n

DECLARE nombretotal TEXT DEFAULT ‘NO FUNCIONA’;<\/p>\n

CASE nombre
\nWHEN 1 THEN SET nombretotal = ‘Jose’;
\nWHEN 2 THEN SET nombretotal = ‘David’;
\nELSE
\nSET nombretotal = ‘EL NUMERO DEL MES INTRODUCIDO ES INCORRECTO DEBE SER UN NUMERO DEL 1 AL 2’;
\nEND CASE;<\/p>\n

RETURN(nombretotal);<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

SELECT NOMBRETUTOR(‘1’);<\/p>\n

SELECT NOMBRETUTOR(‘2’);<\/p>\n

\"Screenshot_20\"<\/p>\n

\/*************************************************************************************************************\/
\n\/********************************************** MID procedure ***********************************************\/<\/p>\n

DROP PROCEDURE IF EXISTS NOMBREMID;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE PROCEDURE NOMBREMID(IN nombre VARCHAR(250), OUT resultado TEXT)
\nBEGIN
\nDECLARE contador INT DEFAULT 0;
\nDECLARE numletras TEXT DEFAULT “”;
\nDECLARE consumid TEXT DEFAULT “”;<\/p>\n

SET numletras = (SELECT CHARACTER_LENGTH(nombre));
\nSET resultado = CONCAT(‘El nombre introducido tiene: ‘,numletras,’caracteres\\n’);
\nWHILE contador < numletras DO
\nSET consumid = (SELECT MID(nombre,contador + 1,1));
\nSET resultado = CONCAT(resultado,’ Letra: ‘,consumid);
\nSET contador = contador + 1;<\/p>\n

END WHILE;<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

CALL NOMBREMID(‘David’,@c);
\nSELECT @c;<\/p>\n

CALL NOMBREMID(‘Jara’,@c);
\nSELECT @c;<\/p>\n

CALL NOMBREMID(‘Fernando’,@c);<\/p>\n

SELECT @c;<\/p>\n

CALL NOMBREMID(‘Jose’,@c);
\nSELECT @c;<\/p>\n

CALL NOMBREMID(‘Adrian’,@c);
\nSELECT @c;<\/p>\n

\"Screenshot_21\"<\/p>\n

\/*************************************************************************************************************\/
\n\/********************************************** MID FUNCION ***********************************************\/<\/p>\n

DROP FUNCTION IF EXISTS NOMBREMIDF;<\/p>\n

DELIMITER \/\/<\/p>\n

CREATE FUNCTION NOMBREMIDF(nombre VARCHAR(250))
\nRETURNS TEXT
\nBEGIN
\nDECLARE contador INT DEFAULT 0;
\nDECLARE numletras TEXT DEFAULT “”;
\nDECLARE consumid TEXT DEFAULT “”;
\nDECLARE resultado TEXT DEFAULT “”;<\/p>\n

SET numletras = (SELECT LENGTH(nombre));
\nSET resultado = CONCAT(‘El nombre introducido: ‘,nombre,’ tiene ‘,numletras,’ caracteres. \\n’);
\nWHILE contador < numletras DO
\nSET consumid = (SELECT MID(nombre,contador + 1,1));
\nSET resultado = CONCAT(resultado,’ Letra: ‘,consumid);
\nSET contador = contador + 1;<\/p>\n

END WHILE;<\/p>\n

RETURN(resultado);<\/p>\n

END \/\/<\/p>\n

DELIMITER ;<\/p>\n

SELECT NOMBREMIDF(‘David’);<\/p>\n

SELECT NOMBREMIDF(‘Jara’);<\/p>\n

SELECT NOMBREMIDF(‘Fernando’);<\/p>\n

SELECT NOMBREMIDF(‘Jose’);<\/p>\n

SELECT NOMBREMIDF(‘Adrian’);<\/p>\n

\"Screenshot_22\"<\/p>\n

 <\/p>\n

 <\/p>\n

Gracias por leer el blog. Saludos, @DavidDeMaya.<\/p>\n","protected":false},"excerpt":{"rendered":"

Hola buenas hoy os traigo un peque\u00f1o ejercicio de bases de datos relacionales, el cual os voy a ense\u00f1ar el resultado del c\u00f3digo programado.<\/p>\n","protected":false},"author":1,"featured_media":956,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[3],"tags":[74,75,76],"class_list":["post-955","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-guias","tag-bases","tag-datos","tag-mysql"],"yoast_head":"\nBases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones. - Hardsoft Security<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/\" \/>\n<meta property=\"og:locale\" content=\"es_ES\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones. - Hardsoft Security\" \/>\n<meta property=\"og:description\" content=\"Hola buenas hoy os traigo un peque\u00f1o ejercicio de bases de datos relacionales, el cual os voy a ense\u00f1ar el resultado del c\u00f3digo programado.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/\" \/>\n<meta property=\"og:site_name\" content=\"Hardsoft Security\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/HardSoftDavid\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-10T23:36:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"413\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DavidDeMaya\" \/>\n<meta name=\"twitter:site\" content=\"@DavidDeMaya\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Tiempo de lectura\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/#\\\/schema\\\/person\\\/dfe289b1e4ae2daf72695e85a8b3dd7a\"},\"headline\":\"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones.\",\"datePublished\":\"2016-06-10T23:36:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/\"},\"wordCount\":1980,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/i0.wp.com\\\/hardsoftsecurity.es\\\/wp-content\\\/uploads\\\/2016\\\/06\\\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1\",\"keywords\":[\"bases\",\"datos\",\"mysql\"],\"articleSection\":[\"Gu\u00edas\"],\"inLanguage\":\"es\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/\",\"url\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/\",\"name\":\"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones. - Hardsoft Security\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/i0.wp.com\\\/hardsoftsecurity.es\\\/wp-content\\\/uploads\\\/2016\\\/06\\\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1\",\"datePublished\":\"2016-06-10T23:36:43+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/#\\\/schema\\\/person\\\/dfe289b1e4ae2daf72695e85a8b3dd7a\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#breadcrumb\"},\"inLanguage\":\"es\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"es\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#primaryimage\",\"url\":\"https:\\\/\\\/i0.wp.com\\\/hardsoftsecurity.es\\\/wp-content\\\/uploads\\\/2016\\\/06\\\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1\",\"contentUrl\":\"https:\\\/\\\/i0.wp.com\\\/hardsoftsecurity.es\\\/wp-content\\\/uploads\\\/2016\\\/06\\\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1\",\"width\":800,\"height\":413},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/index.php\\\/2016\\\/06\\\/10\\\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Portada\",\"item\":\"https:\\\/\\\/hardsoftsecurity.es\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/#website\",\"url\":\"https:\\\/\\\/hardsoftsecurity.es\\\/\",\"name\":\"Hardsoft Security\",\"description\":\"Chasing (In)Security\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/hardsoftsecurity.es\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"es\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/hardsoftsecurity.es\\\/#\\\/schema\\\/person\\\/dfe289b1e4ae2daf72695e85a8b3dd7a\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"es\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/868972cb4e72674a12503e080ee081285c7df2fde82787c96a74bca20781cad7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/868972cb4e72674a12503e080ee081285c7df2fde82787c96a74bca20781cad7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/868972cb4e72674a12503e080ee081285c7df2fde82787c96a74bca20781cad7?s=96&d=mm&r=g\",\"caption\":\"david\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones. - Hardsoft Security","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/","og_locale":"es_ES","og_type":"article","og_title":"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones. - Hardsoft Security","og_description":"Hola buenas hoy os traigo un peque\u00f1o ejercicio de bases de datos relacionales, el cual os voy a ense\u00f1ar el resultado del c\u00f3digo programado.","og_url":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/","og_site_name":"Hardsoft Security","article_publisher":"https:\/\/www.facebook.com\/HardSoftDavid","article_published_time":"2016-06-10T23:36:43+00:00","og_image":[{"width":800,"height":413,"url":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1","type":"image\/jpeg"}],"author":"david","twitter_card":"summary_large_image","twitter_creator":"@DavidDeMaya","twitter_site":"@DavidDeMaya","twitter_misc":{"Escrito por":"david","Tiempo de lectura":"11 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#article","isPartOf":{"@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/"},"author":{"name":"david","@id":"https:\/\/hardsoftsecurity.es\/#\/schema\/person\/dfe289b1e4ae2daf72695e85a8b3dd7a"},"headline":"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones.","datePublished":"2016-06-10T23:36:43+00:00","mainEntityOfPage":{"@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/"},"wordCount":1980,"commentCount":1,"image":{"@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1","keywords":["bases","datos","mysql"],"articleSection":["Gu\u00edas"],"inLanguage":"es","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/","url":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/","name":"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones. - Hardsoft Security","isPartOf":{"@id":"https:\/\/hardsoftsecurity.es\/#website"},"primaryImageOfPage":{"@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#primaryimage"},"image":{"@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1","datePublished":"2016-06-10T23:36:43+00:00","author":{"@id":"https:\/\/hardsoftsecurity.es\/#\/schema\/person\/dfe289b1e4ae2daf72695e85a8b3dd7a"},"breadcrumb":{"@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#breadcrumb"},"inLanguage":"es","potentialAction":[{"@type":"ReadAction","target":["https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/"]}]},{"@type":"ImageObject","inLanguage":"es","@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#primaryimage","url":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1","contentUrl":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1","width":800,"height":413},{"@type":"BreadcrumbList","@id":"https:\/\/hardsoftsecurity.es\/index.php\/2016\/06\/10\/bases-de-datos-relacionales-mysql-diseno-fisico-triggers-insertado-de-datos-consultas-procedures-y-funciones\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Portada","item":"https:\/\/hardsoftsecurity.es\/"},{"@type":"ListItem","position":2,"name":"Bases De Datos Relacionales, MYSQL. Dise\u00f1o F\u00edsico, Triggers, Insertado De Datos, Consultas, Procedures y Funciones."}]},{"@type":"WebSite","@id":"https:\/\/hardsoftsecurity.es\/#website","url":"https:\/\/hardsoftsecurity.es\/","name":"Hardsoft Security","description":"Chasing (In)Security","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/hardsoftsecurity.es\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"es"},{"@type":"Person","@id":"https:\/\/hardsoftsecurity.es\/#\/schema\/person\/dfe289b1e4ae2daf72695e85a8b3dd7a","name":"david","image":{"@type":"ImageObject","inLanguage":"es","@id":"https:\/\/secure.gravatar.com\/avatar\/868972cb4e72674a12503e080ee081285c7df2fde82787c96a74bca20781cad7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/868972cb4e72674a12503e080ee081285c7df2fde82787c96a74bca20781cad7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/868972cb4e72674a12503e080ee081285c7df2fde82787c96a74bca20781cad7?s=96&d=mm&r=g","caption":"david"}}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7xGFx-fp","jetpack-related-posts":[{"id":1465,"url":"https:\/\/hardsoftsecurity.es\/index.php\/2018\/02\/13\/como-hacer-mysql-mas-seguro\/","url_meta":{"origin":955,"position":0},"title":"\u00bfC\u00f3mo hacer MySQL m\u00e1s seguro?","author":"david","date":"febrero 13, 2018","format":false,"excerpt":"Hola a tod@s hoy os traigo una peque\u00f1a entrada donde vamos a ver como hacer nuestro servidor de MySQL m\u00e1s seguro, esto nos permitir\u00e1 tener un control completo de nuestro servidor, ya que en estos tiempos existen much\u00edsimos bots continuamente escaneando la red para atacar nuestros servicios. Voy a dar\u2026","rel":"","context":"En \u00abGu\u00edas\u00bb","block_context":{"text":"Gu\u00edas","link":"https:\/\/hardsoftsecurity.es\/index.php\/category\/guias\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":3638,"url":"https:\/\/hardsoftsecurity.es\/index.php\/2024\/01\/25\/como-usar-sqlmap-guia-con-caso-practico\/","url_meta":{"origin":955,"position":1},"title":"\u00bfC\u00f3mo usar SQLMap? Gu\u00eda con caso pr\u00e1ctico.","author":"david","date":"enero 25, 2024","format":false,"excerpt":"\u00a1Hola! Hoy vamos a sumergirnos en el fascinante mundo de SQLMap, una herramienta indispensable para aquellos que se dedican a garantizar la seguridad de las bases de datos y, por supuesto, para los curiosos del pentesting. Imagina tener una navaja suiza que puede detectar y explotar vulnerabilidades en bases de\u2026","rel":"","context":"En \u00abGu\u00edas\u00bb","block_context":{"text":"Gu\u00edas","link":"https:\/\/hardsoftsecurity.es\/index.php\/category\/guias\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2024\/01\/image-2.png?fit=724%2C362&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2024\/01\/image-2.png?fit=724%2C362&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2024\/01\/image-2.png?fit=724%2C362&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2024\/01\/image-2.png?fit=724%2C362&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1611,"url":"https:\/\/hardsoftsecurity.es\/index.php\/2018\/06\/25\/explicacion-de-honeypot-e-instalacion-de-kippo-y-kippo-graph\/","url_meta":{"origin":955,"position":2},"title":"Explicaci\u00f3n de Honeypot e instalaci\u00f3n de kippo y kippo-graph.","author":"david","date":"junio 25, 2018","format":false,"excerpt":"Hola tod@s, hoy os traigo una entrada, donde explico que es un honeypot, creo que es muy importante implementar este tipo de soluciones en nuestra red, ya que nos permite simular servicios en producci\u00f3n y as\u00ed poder analizar los ataques de los ciberdelincuentes y poder endurecer los servicios que est\u00e1n\u2026","rel":"","context":"En \u00abGu\u00edas\u00bb","block_context":{"text":"Gu\u00edas","link":"https:\/\/hardsoftsecurity.es\/index.php\/category\/guias\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/06\/honeypot_big_logo-c551a80e.png?fit=1000%2C715&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/06\/honeypot_big_logo-c551a80e.png?fit=1000%2C715&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/06\/honeypot_big_logo-c551a80e.png?fit=1000%2C715&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/06\/honeypot_big_logo-c551a80e.png?fit=1000%2C715&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":341,"url":"https:\/\/hardsoftsecurity.es\/index.php\/2015\/02\/08\/pentesting-de-seguridad-comprobar-si-tu-sistema-de-bases-de-datos-es-realmente-vulnerable-a-cve2012-2122\/","url_meta":{"origin":955,"position":3},"title":"Pentesting de seguridad, comprobar si tu sistema de bases de datos es realmente vulnerable a cve:2012-2122","author":"david","date":"febrero 8, 2015","format":false,"excerpt":"Vulnerabilidad en la base de datos Mysql 5.0.51a Vulnerabilidad CVE 2012-2122, la posibilidad de autenticarse como usuario con m\u00e1ximos privilegios (root) \u00fanicamente realizando conexiones simult\u00e1neas, con CUALQUIER contrase\u00f1a introducida como par\u00e1metro y contra un servidor vulnerable. Al cabo de un n\u00famero indeterminado de intentos fallidos, el servidor aceptar\u00e1 la conexi\u00f3n\u2026","rel":"","context":"En \u00abGu\u00edas\u00bb","block_context":{"text":"Gu\u00edas","link":"https:\/\/hardsoftsecurity.es\/index.php\/category\/guias\/"},"img":{"alt_text":"metasploit","src":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2015\/02\/metasploit.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1438,"url":"https:\/\/hardsoftsecurity.es\/index.php\/2018\/02\/01\/como-explotar-inyeccion-sql-con-sqlmap\/","url_meta":{"origin":955,"position":4},"title":"Como explotar inyecci\u00f3n SQL con SQLmap.","author":"david","date":"febrero 1, 2018","format":false,"excerpt":"Hola a tod@s, aprovechando la anterior entrada\u00a0que trata de la b\u00fasqueda autom\u00e1tica de p\u00e1ginas web vulnerables a inyecciones SQL, aprovechar\u00e9 esta entrada para hacer una gu\u00eda b\u00e1sica de como explotar esta vulnerabilidad con la herramienta SQLmap. As\u00ed describe la web oficial de SQLmap la herramienta: SQLmap es una herramienta de\u2026","rel":"","context":"En \u00abGu\u00edas\u00bb","block_context":{"text":"Gu\u00edas","link":"https:\/\/hardsoftsecurity.es\/index.php\/category\/guias\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/02\/sqlinjection2.png?fit=1140%2C600&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/02\/sqlinjection2.png?fit=1140%2C600&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/02\/sqlinjection2.png?fit=1140%2C600&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/02\/sqlinjection2.png?fit=1140%2C600&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2018\/02\/sqlinjection2.png?fit=1140%2C600&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":1235,"url":"https:\/\/hardsoftsecurity.es\/index.php\/2017\/06\/10\/hardening-centro-datos\/","url_meta":{"origin":955,"position":5},"title":"Hardening De Nuestro Centro De Datos.","author":"david","date":"junio 10, 2017","format":false,"excerpt":"Hola a todos, hoy os traigo m\u00ed proyecto de grado superior, en especial el grado superior de Administraci\u00f3n de Sistemas Inform\u00e1ticos en Red, este proyecto esta enfocado a la seguridad de los centro de datos. En el documento del proyecto se explicar\u00e1n algunas soluciones tomadas en un centro de datos\u2026","rel":"","context":"En \u00abGu\u00edas\u00bb","block_context":{"text":"Gu\u00edas","link":"https:\/\/hardsoftsecurity.es\/index.php\/category\/guias\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2015\/12\/cropped-cybersecurity_image.jpg?fit=1200%2C898&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2015\/12\/cropped-cybersecurity_image.jpg?fit=1200%2C898&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2015\/12\/cropped-cybersecurity_image.jpg?fit=1200%2C898&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2015\/12\/cropped-cybersecurity_image.jpg?fit=1200%2C898&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2015\/12\/cropped-cybersecurity_image.jpg?fit=1200%2C898&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"jetpack_likes_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/hardsoftsecurity.es\/wp-content\/uploads\/2016\/06\/mysql-logo-1329320869.jpg?fit=800%2C413&ssl=1","_links":{"self":[{"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/posts\/955","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/comments?post=955"}],"version-history":[{"count":2,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/posts\/955\/revisions"}],"predecessor-version":[{"id":981,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/posts\/955\/revisions\/981"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/media\/956"}],"wp:attachment":[{"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/media?parent=955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/categories?post=955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hardsoftsecurity.es\/index.php\/wp-json\/wp\/v2\/tags?post=955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}