Mini ejercicio

Con los datos del mongodb:

db.inventory.insertMany([
   { item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
   { item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
   { item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
   { item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
   { item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
]);

Buscar los documentos que tengan en dim_cm un valor entre 10 y 15

Ejercicio MongoDB

Vamos a crear una base de datos llamada tienda.

Y dentro de la tienda vamos a crear una colección teléfonos.

Insertaremos los siguientes datos:


[{"name" : "AC3 Phone", "brand" : "ACME", "type" : "phone", "price" : 200, "rating" : 3.8,"warranty_years" : 1, "available" : true },
{  "name" : "AC7 Phone", "brand" : "ACME", "type" : "phone", "price" : 320, "rating" : 4,"warranty_years" : 1, "available" : false },
{  "name" : "AC3 Series Charger", "type" : [ "accessory", "charger" ], "price" : 19, "rating" : 2.8,"warranty_years" : 0.25, "for" : [ "ac3", "ac7", "ac9" ] },
{  "name" : "AC3 Case Green", "type" : [ "accessory", "case" ], "color" : "green", "price" : 12, "rating" : 1,"warranty_years" : 0 },
{  "name" : "Phone Extended Warranty", "type" : "warranty", "price" : 38, "rating" : 5,"warranty_years" : 2, "for" : [ "ac3", "ac7", "ac9", "qp7", "qp8", "qp9" ] },
{  "name" : "AC3 Case Black", "type" : [ "accessory", "case" ], "color" : "black", "price" : 12.5, "rating" : 2,"warranty_years" : 0.25, "available" : false, "for" : "ac3" },
{  "name" : "AC3 Case Red", "type" : [ "accessory", "case" ], "color" : "red", "price" : 12, "rating" : 4,"warranty_years" : 0.25, "available" : true, "for" : "ac3" },
{ "name" : "Phone Service Basic Plan", "type" : "service", "monthly_price" : 40,"rating" : 3, "limits" : { "voice" : { "units" : "minutes", "n" : 400, "over_rate" : 0.05 }, "data" : { "units" : "gigabytes", "n" : 20, "over_rate" : 1 }, "sms" : { "units" : "texts sent", "n" : 100, "over_rate" : 0.001 } }, "term_years" : 2 },
{  "name" : "Phone Service Core Plan", "type" : "service", "monthly_price" : 60, "rating" : 3, "limits" : { "voice" : { "units" : "minutes", "n" : 1000, "over_rate" : 0.05 }, "data" : { "n" : "unlimited", "over_rate" : 0 }, "sms" : { "n" : "unlimited", "over_rate" : 0 } }, "term_years" : 1 },
{ "name" : "Phone Service Family Plan", "type" : "service", "monthly_price" : 90,"rating" : 4, "limits" : { "voice" : { "units" : "minutes", "n" : 1200, "over_rate" : 0.05 }, "data" : { "n" : "unlimited", "over_rate" : 0 }, "sms" : { "n" : "unlimited", "over_rate" : 0 } }, "sales_tax" : true, "term_years" : 2 }]

Después crearemos las siguientes búsquedas:

Elementos cuyo ‘type’ sea ‘service’.
Elementos cuyo ‘type’ sea ‘service’ y el precio mensual > 50
Elementos cuyo ‘type’ sea ‘service’ O el precio mensual > 50

MongoDB Consultas

https://docs.mongodb.com/manual/tutorial/query-documents/

https://docs.mongodb.com/manual/reference/operator/query/#query-selectors

Si yo en sql quiero aquellos registros cuya qty > 50

where qty>50

Si yo en MongoDB quiero lo mismo tengo que ponerlo así:

qty:{$gt:50}

campo:{operador:valor}


-- Buscar todos

db.inventory.find();

-- Buscar por igualdad. El campo debe valor lo mismo que lo que le pongo

db.inventory.find( { status: "D" } )

-- Buscar usando un operador. Ojo porque la sintaxis es un poco extraña

db.inventory.find( { qty: {$gt:50} } )

-- Operador in

db.inventory.find({status:{$in:["A","D"]}});

-- Por defecto, si tenemos varias condiciones, se entiende que se tienen que cumplir todas (AND)

db.inventory.find( { status: "A", qty: { $lt: 30 } } )

db.inventory.find({
$or: [
	{status:"D"},
	{qty:{
		$lt:50
		}
	}
]
})

db.inventory.find({$or: [{status:"D"},{qty:{$lt:50}}]})

-- esto

db.inventory.find( {
     status: "A",
     $or: [ { qty: { $lt: 30 } }, { item: /^p/ } ]
} )

-- sería equivalente a: SELECT * FROM inventory WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")

db.inventory.find({qty:{$gte:40,$lte:60}})

-- En búsquedas anidadas por defecto se entiende la igualdad abosulta en valores y orden:

db.inventory.find( { size: { w: 21,h: 14,  uom: "cm" } } ) -- esto no lo encuentra

db.inventory.find({size:{h:14}}) -- Esto tampoco

db.inventory.find( { size: { h: 14, w: 21, uom: "cm" } } ) -- Esto sí

-- Para buscar por un valor concreto independientemente de optras propiedades usamos el . (punto)

db.inventory.find({"size.h":8.5})

-- Podemos usar varias condiciones tanto de propiedades anidadas como de propiedades normales

db.inventory.find( { "size.h": { $lt: 15 }, "size.uom": "in", status: "D" } )

-- Al buscar en array la igualdad es como en los anidades: mismos valores en mismo orden
-- Las dos consultas siguientes nos devuelven diferentes resultados

db.inventory.find( { tags: [ "blank","red"] } )

db.inventory.find( { tags: ["red", "blank"] } )

-- Si yo omito los corchetes y uso un valor (o un operador) se busan los elementos que contengan el valor o que cumplan la condición:

db.inventory.find({tags:"red"}) -- Busca los que tengan 'red' en el array

db.inventory.find({tags:{$in:["red","blue"]}}) -- Que tengan "red" o "blue" en alguna parte del array

db.inventory.find( { dim_cm: { $gt: 25 } } ) -- Algún elemento del array sea mayor de 25

-- Las dos condiciones se tienen que cumplir pero lo pueden cumplir elementos diferentes del array
-- Es decir, una condición la puede cumplir un elemento y la otra otro.

db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )

-- Las dos condiciones las tiene que cumplir juntas al menos un elemento del array

db.inventory.find( { dim_cm: { $elemMatch: { $gt: 15, $lt: 20 } } } )

-- Busco en un array en una posición concreta (índice de base 0)

db.inventory.find( { "dim_cm.1": { $gt: 25 } } )

-- Buscar los documentos cuyo array tiene una longitud determinada

db.inventory.find( { "tags": { $size: 3 } } )

-- Buscar los documentos en cuyo array haya un elemento que cumpla esa condición:

db.inventory.find( { "instock.warehouse": "C" } )

db.inventory.find( { "instock.qty": {$gte:40}} )

-- Que me muestre sólo los campos 'item' y 'status'. El id se ve siempre por defecto

db.inventory.find( { status: "A" }, { item: 1, status: 1 } )

-- Que me muestre sólo los campos 'item' y 'status'. El id lo oculto

db.inventory.find( { status: "A" }, { item: 1, status: 1, _id:0 } )

-- Que se muestren todos los campos menos 'size' e 'instock'

db.inventory.find( { status: "A" }, { size:0,instock:0 } )

-- Podemos usar $slice para obtener en la proyección subconjuntos de un array, y permite valores negativos

db.inventory.find( { status: "A" }, { item: 1, status: 1, instock: { $slice: -1 } } )

-- Busca los documentos que no tengan la propiedad o que valga nulo

db.inventory.find( { item: null } )

-- Busca los documentos que no tengan la propiedad, si la tienen y vale nulo no los encuentra

db.inventory.find( { item : { $exists: false } } )

-- Busca los documentos que SI tengan la propiedad aunque valga nulo

db.inventory.find( { item : { $exists: true } } )


MongoDB insert

https://docs.mongodb.com/manual/tutorial/insert-documents/

db.collection.insertOne() Inserts a single document into a collection.
db.collection.insertMany() db.collection.insertMany() inserts multiple documents into a collection.
db.collection.insert() db.collection.insert() inserts a single document or multiple documents into a collection.

SIEMPRE empezamos con db, NO nuestra base de datos.

Si nuestra colección no existe se crea. Así que ojo con poner el nombre bien.

Ejemplo:

db.actor.insertOne({nombre:"Ana",apellidos:"Pi"});

db.actor.insertMany([{
nombre:"Eva",
apellido:"Pou",
intereses:["drama","comedia"]
},
{
nombre:"Ana",
apellido:"Ros",
pelicula:{
	titulo:"Drama en la cumbre",
	anyo:2020
	}
},
{
nombre:"Ana",
apellido:"Ros",
pelicula:{
	titulo:"Drama en la cumbro",
	anyo:2020
	}
}
])

db.inventory.insertOne(
   { item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } }
)

db.inventory.insertMany([
   { item: "journal", qty: 25, tags: ["blank", "red"], size: { h: 14, w: 21, uom: "cm" } },
   { item: "mat", qty: 85, tags: ["gray"], size: { h: 27.9, w: 35.5, uom: "cm" } },
   { item: "mousepad", qty: 25, tags: ["gel", "blue"], size: { h: 19, w: 22.85, uom: "cm" } }
])

MongoDB

https://www.mongodb.com/es

Probarlo sin instalar:

https://docs.mongodb.com/manual/tutorial/getting-started/

Instalar en windows:

https://docs.mongodb.com/manual/tutorial/install-mongodb-on-windows/

https://www.genbeta.com/desarrollo/mongodb-que-es-como-funciona-y-cuando-podemos-usarlo-o-no

Tutorial básico:

https://www.diegocalvo.es/tutorial-de-mongodb-con-ejemplos/

https://geekflare.com/es/mongodb-queries-examples/

https://www.codigofuente.org/series/mongodb/

Mini ejercicio funciones

Crear una función que le pasemos el id de una película y nos deuelva lo siguiente:

‘Para adultos’ Si el rating es ‘R’
‘Para todas las edades’ si no lo es

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `tipo_pelicula`
(p_film_id int) RETURNS varchar(50) CHARSET latin1
BEGIN
declare v_rating varchar(5);

select rating into v_rating from film 
where film_id=p_film_id;

if v_rating='R' then
	return 'Para adultos';
else
	return 'Para todos los públicos';
end if;

END$$

DELIMITER ;

Ejemplos de funciones en Mysql


DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
FUNCTION `doble`(numero int) RETURNS int(11)
BEGIN

RETURN numero*2;
END$$

DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `tipo_pelicula`
(p_film_id int) RETURNS varchar(50) CHARSET latin1
BEGIN
declare v_length int;
select length into v_length from film 
where film_id=p_film_id;

if v_length<=140 then
return 'CORTA';
else
RETURN 'LARGA';
end if;

END$$

DELIMITER ;

DELIMITER $$
USE `sakila`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `factorial`(p_numero int) RETURNS int(11)
BEGIN
declare total,cont int default 1;

while cont<=p_numero do
 set total=total*cont;
 set cont=cont+1;
end while;
RETURN total;
END$$

DELIMITER ;

Uso:


select doble(8);

select title,length,tipo_pelicula(film_id) from film;

select factorial(5);

Procedimiento almacenado alta clientes

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente2` (in p_country varchar(50),
in p_city varchar(50),
in p_address varchar(50),
in p_first_name varchar(50),
in p_last_name varchar(50),
out p_customer_id int)
BEGIN

declare v_country_id int;
declare v_city_id int;
declare v_address_id int;
declare v_customer_id int;

select country_id into v_country_id from country
where country=p_country;

IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;

select city_id into v_city_id from city
where city=p_city and country_id=v_country_id;

IF v_city_id is null then
 insert into city(city,country_id)
 values (p_city,v_country_id);
 select last_insert_id() into v_city_id;
END IF;

select address_id into v_address_id from address
where address=p_address and city_id=v_city_id;

IF v_address_id is null then
 insert into address(address,city_id)
 values (p_address,v_city_id);
 select last_insert_id() into v_address_id;
END IF;

select customer_id into v_customer_id from customer
where first_name=p_first_name and last_name=p_last_name and address_id=v_address_id;

IF v_customer_id is null then
 insert into customer(first_name,last_name,address_id,store_id)
 values (p_first_name,p_last_name, v_address_id,1);
 select last_insert_id() into v_customer_id;
END IF;

select v_customer_id into p_customer_id;

END$$
delimiter ;

Uso:

call alta_cliente('Spain','Logroño','Laurel, 4','Juan','Pi',@id);
select @id;

Mini ejercicio sub consultas

1.- Películas con más actores de la media

Pistas:

¿Cual es la media de actores por película? Por un lado necesitamos obtener el total de actores por película, que no lo sabemos. Una consulta entre film, film_actor y actor con un count de actores y un group by de películas.

Con ese total podemos averiguar la media de dos maneras:
a) Creando una vista: Si creo una vista total_actores_pelicula solo tengo que lanzar un avg sobre el total de los actores.
b) Creando una subconsulta con tabla temporal. Si no quiero crear una vista utilizo la consulta anterior como tabla temporal y calculo el avg sobre el total.

Una vez tenemos la media volvemos a la consulta original del total de actores por película. SI la tenemos en una vista no nos hace falta repetirla, en caso contrario la volvemos a hacer. Y ponemos como condición que el total de actores sea mayor que la media.

-- Total de actores por película

select title, count(actor_id) from film join film_actor using(film_id)
group by film_id;

-- ¿Media?
-- Creando una vista
create view total_actores_por_pelicula as
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id;

select avg(total) from total_actores_por_pelicula;

-- Sin vista, con subtabla o tabla derivada

select avg(total) from (
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
) temporal;

-- Si tengo la consulta la puedo usar

select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
having total>(
select avg(total) from total_actores_por_pelicula
);

-- Si no repito la subconsulta

select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
having total>(
select avg(total) from (
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
) temporal
);

2.- Clientes con películas alquiladas por encima de la media

select avg(total) from (
select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id) temporal;

select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id
having total>(select avg(total) from (
select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id) temporal)

3.- Clientes que no hayan alquilado películas de acción

-- Los que sí han alquilado acción
select first_name, last_name, name
from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
where name='Action';

-- Los que no estén en la lista anterior

select first_name, last_name from customer
where customer_id not in (
select customer_id
from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
where name='Action'
)