Category Archives: mysql

Partimos de la siguiente estructura:

Donde tenemos datos en las dos tablas

Nombres:

Etiquetas:

el resultados que queremos es:

La sql necesaria es:

select e.*,
	(SELECT GROUP_CONCAT(tag SEPARATOR ', ') FROM tags where idnombre=e.id) as tags
	from nombres e

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

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

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.

Creamos una tabla con un varios campos.

Creamos un indice único sobre el campo email

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

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

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

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:

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

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

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

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