Category Archives: mysql

Pensando en cambiar de hosting

Será porque hoy es sábado y voy bien de tiempo con mis proyectos que tengo a medias, pero hoy me apetece escribir una entrada mas larga.

Llevo tiempo con mi pagina web en 1and1, y creo que es el momento de pensar si es el mejor hosting en el que puedo tener no solo el blog, sino todas las paginas correspondientes a mis aplicaciones móviles, tanto de IOS como de Android, ademas de aquellas necesarias para el correcto funcionamiento de varias aplicaciones que he desarrollado a lo largo de los años.

Ultimamente he desarrollado varias aplicaciones como Datercam para Android ( ya existía para IOS) que permite poner información como la fecha, la hora, las coordenadas gps y la ubicación ( obtenida mediante google ) a las fotos, para que no olvides cuando y donde hiciste una foto.

O mi ultima aplicación Emojime publicada hace 3 días, que permite coger una foto, bien sea de la cámara o del carrete, detectar las caras y cargar emoticonos de forma aleatoria, lo que permite “jugar” a encontrar la combinación de caras que mejor se adapte a la foto, una vez tienes el resultado puedes compartirla o guardarla.

Pero volviendo al tema con el que empece, últimamente he tenido problemas con mi pagina en 1and1, momentos en los que no puede acceder, errores internos del servidor ( que luego no dan mas información ) o simplemente que la velocidad no es la deseada.

A eso se junta que hace tiempo me cambiaron la tarifa de forma unilateral, después de 8 años con ellos ya no podían mantenerme el contrato que tenia con ellos, y me pasaban a uno superior ofreciendo cosas que no necesito y con ello un aumento de precio considerable.

Otro de los motivos por los que tengo que pensar en cambiar de hosting es que me gustaría que tuviera acceso a la base de datos de forma nativa mediante host y puerto, mientras que ahora solo puedo acceder mediante phpmyadmin una vez que me he logueado en el panel de control, lo que relentiza bastante el trabajo con base de datos.

Hace unos días que encontré un articulo  donde hablaban de tres hosting baratos y exponían una serie de pruebas que habian hecho para valorar cual era la mejor, los tres candidatos eran: Webempresa, Raiola y Sered, y las pruebas que realizaban eran: benchmark, velocidad de renderización, estabilidad del servidor o load impact entre otros.

Aquí me pilla de novato, pues no tenia ni idea de en que consistían muchas de las pruebas que se realizaban, pero me ha servido para aprender mas acerca de los pasos necesarios para que tu web esté en un hosting capaz de soportar cargas sin que el usuario se vea afectado. Es mas creo que estas pruebas pueden ser aplicadas en mi puesto de trabajo y se lo comentaré a la gente de sistemas para organizar un plan de actuación.

Aunque en el articulo si mostraban un ganador, como en casi todas las comparativas  cada una de ellas destacaba en algún punto por lo que el resultado no era fácil de determinar.

Como en el articulo no hablaban de si el acceso a base de datos era con phpmyadmin o si por el contrario permitían acceso directo no me queda mas remedio que preguntarlo directamente, recurro a llamar por teléfono y quedo a la espera, en unos segundos me atiende un empleado muy amable que me responde a mi gran duda, y la respuesta es afirmativa, CPANEL permite habilitar conexión externa con la base de datos, por lo que acaba de convencerme para tomarlo como claro candidato a ser mi proximo hosting, a ver si el proximo post ya está ahí alojado.

 

 

Reiniciar autoincremento en mysql

Si tenemos una tabla (nombredelatabla) con la clave autoincremental, con los valores:
1 pepito
2 jaimito
3 equivocado

Si borramos el 3, cuando queramos insertar un valor nos pondrá el 4.

para poder reutilizar el 3, tenemos que modificar la tabla:

Alter table nombredelatabla auto_increment=3

Donde el número es el que queremos que sea el siguiente

Evitar fechas 00/00/0000 con mysql

Cuando trabajamos con fechas en mysql se guardan en formato yyyy-mm-dd mientras que normalmente en pantalla las queremos ver en formato dd/mm/YYYY.

Para ellos vamos a ver las consultas necesarias para guardar y recuperar los datos y evitar que nos aparezcan datos 00/00/0000

Cuando recuperamos el valor ponemos en la sql

if(fecha is not null,date_format(fecha,'%d/%m/%Y'),'') as fec

Si llamamos siempre a date_format cuando el valor es null el resultado es 00/00/0000

Y a la hora de guardar solo formateamos la fecha si tiene valor

update la_tabla_que_sea set
 fecha=if ('".$_POST['campo_fecha_web']."'!='',str_to_date('".$_POST['campo_fecha_web']."','%d/%m/%Y'),null)
where id=id_necesario_para_actualizar

Con esto evitamos llamar a str_to_date si el valor está vacio, que es lo que origina que se guarde el 00/00/0000

Generar un numero aleatorio en sqlite

La función random() de sqlite devuelve un numero entre -9223372036854775808 y +9223372036854775807
Para obtener números solo positivos y con valores mas normales podemos utilizar la expresión:

ABS(RANDOM() % n_max)

Donde n_max indica el valor máximo que queremos para el random.

Insert or update

Creamos una tabla con un varios campos.
unique1

Creamos un indice único sobre el campo email
unique2

Ahora insertamos el primer valor, lo importante es el email:
unique3

Ahora intentamos insertar un nuevo registro pero manteniendo el mismo email:
unique4

Evidentemente nos tiene que fallar, pues el indice único está funcionando
unique5

Por ultimo modificamos la consulta y le añadimos una condición para que nos actualice uno de los datos en el caso de intentar un insert con una clave unica, por lo que ya no nos dará el error:
unique6

insert into usuarios (nombre,email,clave,fechains) 
values ('Prueba duplicate','prueba@prueba.com','clavenueva',now())
on duplicate key update fechains=now()

Trabajar con fechas en sqlite

Las bases de datos SQlite no tienen el tipo date por lo que las fechas se tienen que guardar como text.

Si queremos trabajar con fechas para buscar las que cumplan que sean mayores a …. etc, podemos utilizar la conversión a fecha del tipo yyyymmddhhiiss

Dada una tabla con los valores de fecha y hora:
sqlite1

Podemos ejecutar una consulta como :

select fecha,hora,id
from notificaciones 
where substr(fecha,7)||substr(fecha,4,2)||substr(fecha,1,2)||substr(hora,1,2) ||substr(hora,4,2) ||substr(hora,7,2)
> '20150901140920'

Que nos devuelve uno tal y como se esperaba

borrar registros mysql a partir de una select en la misma tabla

Si intentamos borrar registros de una tabla a partir de una select

delete from customers where customers.customerNumber in
(
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
)

obtenemos el error:
[Err] 1093 – You can’t specify target table ‘customers’ for update in FROM clause

Existen dos maneras de borrar registros de una tabla a partir de una select de la misma tabla:

usando in

delete from customers where customers.customerNumber in
(select customerNumber from (
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
) as t )

y con inner join

delete customers from customers inner join
(select customerNumber from (
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
) as t ) tt on customers.customerNumber=tt.customerNumber

es importante decir que tabla borras, pues al hacer el join la sentencia no sabria cual borrar.

 

Ejercicio mysql

Ejercicio

A partir del siguiente enunciado:

Se quiere almacenar información de los empleados, clientes, productos y pedidos de una empresa. De cada cliente queremos almacenar un código único, el nombre y la ciudad. Un cliente puede hacer varios pedidos, pero un pedido es dolo de un cliente. De cada pedido queremos almacenar un código único y la fecha del pedido. Cada pedido lo gestiona un empleado. Un empleado puede gestionar varios pedidos. De cada empleado queremos almacenar un código único, nombre y ciudad. Cada pedido puede incluir varias unidades de un solo producto. De cada producto almacenamos un código único su nombre y precio unitario.

Obtener las siguientes sentencias:

A. muestra un listado con las siguientes columnas: numero_de_pedido,nombre_cliente,nombre_empleado,nombre_producto,cantidad, precio_unitario y total_importe_pedido
B. Muestra un listado con las siguientes columnas: numero_de_pedidos, nombre_cliente, media_importe_pedidos y total_importe_pedidos para cada cliente ordenado por nombre de cliente
C. Muestra un listado con las siguientes columnas: numero_de_pedidos, numero_empleados, ciudad, media_importe_pedidos y total_importe_pedidos para cada ciudad de residencia de los empleados siempre que al menos en la ciudad haya 2 o mas empleados y que en total en la ciudad se hayan firmado al menos 6 pedidos.
D. Muestra todos los datos de los empleados que hayan firmado pedidos a clientes de su misma ciudad.

Solución

mysql_1

El script para crear las tablas seria:

DROP TABLE IF EXISTS `examen_cliente`;
CREATE TABLE `examen_cliente` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`ciudad` varchar(255) DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— —————————-
— Table structure for examen_empleado
— —————————-
DROP TABLE IF EXISTS `examen_empleado`;
CREATE TABLE `examen_empleado` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`ciudad` varchar(255) DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— —————————-
— Table structure for examen_pedido
— —————————-
DROP TABLE IF EXISTS `examen_pedido`;
CREATE TABLE `examen_pedido` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`fecha` date DEFAULT NULL,
`id_cliente` int(11) DEFAULT NULL,
`id_empleado` int(11) DEFAULT NULL,
`id_producto` int(11) DEFAULT NULL,
`cantidad` int(11) DEFAULT NULL,
PRIMARY KEY (`codigo`),
KEY `id_cliente` (`id_cliente`),
KEY `id_empleado` (`id_empleado`),
KEY `id_producto` (`id_producto`),
CONSTRAINT `fk_cliente2` FOREIGN KEY (`id_cliente`) REFERENCES `examen_cliente` (`codigo`),
CONSTRAINT `fk_empleado2` FOREIGN KEY (`id_empleado`) REFERENCES `examen_empleado` (`codigo`),
CONSTRAINT `fk_producto2` FOREIGN KEY (`id_producto`) REFERENCES `examen_producto` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=1014 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `examen_producto`;
CREATE TABLE `examen_producto` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`precio` float DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

A.
select p.codigo as numero_de_pedido,
c.nombre as nombre_cliente,
pr.nombre as nombre_producto,
p.cantidad,
pr.precio,
p.cantidad*pr.precio as total_importe_pedido
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo

B.
select count(*) as numero_de_pedidos,
c.nombre as nombre_cliente,
avg(p.cantidad*pr.precio) as media_importe_pedidos,
sum(p.cantidad*pr.precio) as total_importe_pedidos
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
group by c.codigo

C.
select count(p.codigo) as numero_de_pedidos,
count(e.codigo) as numero_empleados,
e.ciudad,
avg(p.cantidad*pr.precio) as media_importe_pedidos,
sum(p.cantidad*pr.precio) as total_importe_pedidos
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
group by e.ciudad having count(e.codigo)>=2 and sum(p.codigo)>=6

D.
select e.*,p.codigo,p.fecha,c.nombre,c.ciudad
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
where e.ciudad=c.ciudad

Check en mysql

Los check en mysql no funcionan

si creamos una tabla y le añadimos un check

CREATE TABLE cliente (
SD integer CHECK (SD > 0),
nombre varchar (30),
apellido varchar(30)
);

E intentamos introducir un dato que no cumpla el check no nos da ningun error:

INSERT INTO cliente values ('-2','abc','zz');
INSERT INTO cliente values (-3,'abc','zz');

Vemos que en la tabla tenemos los valores.

mysql_2

Para solventar el problema tenemos que recurrir a un trigger

CREATE TRIGGER check_trigger
BEFORE INSERT
ON cliente
FOR EACH ROW
BEGIN
IF NEW.SD<0 THEN
CALL `Error: El valor de SD tiene que ser mayor que 0`;
END IF;
END

Ahora si intentamos introducir un valor que no cumple los requisitos

INSERT INTO cliente values (-4,’tiene que fallar’,’por el trigger’);

Obtenemos un error, tal y como esperabamos:

[Err] 1305 – PROCEDURE clases.Error: El valor de SD tiene que ser mayor que 0 does not exist

Hemos resuelto el problema, no es del todo cierto, pues el trigger era solo para insert, si actualizamos un valor no salta:

update cliente set nombre=’cambiado’ where sd=-2

En mysql no se puede crear un trigger para insert or update como en oracle, por lo que hay que duplicar el trabajo.

CREATE TRIGGER check_trigger_update
BEFORE update
ON cliente
FOR EACH ROW
BEGIN
IF NEW.SD<0 THEN
CALL `Error: El valor de SD tiene que ser mayor que 0`;
END IF;
END

Ahora si ejecutamos la sentencia:

update cliente set sd=-4 where sd=-2

Nos devuelve un error, como era de esperar.