Ejercicio Global BD
Queremos hacer una base de datos para una tienda online
Tenemos unas categorías de productos, que constan de un nombre y una descripción
De los productos queremos saber el nombre, una descripción, el precio, el stock que tenemos y un booleano si está de oferta o no.
UN producto puede pertenecer a varias categorías y una categoría puede tener varios productos.
Para comprar un producto hay que registrarse, deben introducir un nombre, un email y una contraseña. También una dirección de envío, un cp y una población.
Un cliente puede comprar varios productos que pueden ser comprados por varios clientes. Cada vez que se realiza una venta nos interesa saber la fecha y el precio al que se compró.
Una vez realizado vamos a obtener el modelo lógico.
Cada grupo tiene que crear los siguientes inserts:
– dos categorias
– Cinco productos
– Asignar tres productos a una sola categoría (la que queráis) y dos productos a las dos categorías
– Tres clientes
– Dos compras de los clientes de dos productos cada una.
Dejadlos como comentarios en esta entrada.
Si alguien ha acabado todo hasta aquí obtener lo siguiente:
Nümero y total de venta por categoría
Número y total de ventas por cliente
Utilizad los inserts de otros compañeros para rellenar vuestra base de datos. Pensad si hay algunas sentencias que no os servirán y el por qué. Como mínimo recuperéis una categoría, dos productos y dos clientes del resto de grupos.
Vamos a realizar dos vistas:
Una vista categoría_productos
Una vista productos_clientes
Que relacionen las tablas que hagan falta.
Con estas vistas haremos un select para averiguar el número de productos por categoría.
Mini ejercicio funciones fecha
Averiguar, en la base de datos sakila, todos los pagos que se han hecho el fin de semana.
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofweek
¿Cómo haría para averiguar el total del importe? Es decir, la suma global de todos estos pagos.
-- Todos los pagos realizados en fin de semana select * from payment where dayofweek(payment_date)=1 OR dayofweek(payment_date)=7; -- Todos los pagos realizados en fin de semana, la suma total select sum(amount) total from payment where dayofweek(payment_date)=1 OR dayofweek(payment_date)=7; -- Todos los pagos realizados en días laborables, la suma total select sum(amount) total from payment where not(dayofweek(payment_date)=1 OR dayofweek(payment_date)=7); -- Todos los pagos realizados en fin de semana agrupados por mes select month(payment_date) mes,sum(amount) total from payment where dayofweek(payment_date)=1 OR dayofweek(payment_date)=7 group by month(payment_date);
Ejercicio funciones
Quiero una consulta que me muestre:
Nombre completo del actor separado por espacio,
titulo de la película recortado a 10 letras,
Si la descripción de la película tiene la palabra ‘epic’ que ponga ¡ÉPICA!
Si la descripción de la película tiene la palabra ‘drama’ que ponga ‘Enternecedor drama’
Si el rating es ‘R’ que ponga ‘Para adultos’ y si no es ‘R’ que no ponga nada.
-- Esta sería la solución select concat_ws(' ',first_name,last_name) actor, left(title,10) pelicula, case when description like '%epic%' then 'Épica!!!!' when description like '%drama%' then 'Enternecedor drama' else '' end tipo, if (rating='R','Para adultos','') clasificacion from actor join film_actor using(actor_id) join film using(film_id); -- Esto serían alternativas y maneras de llegar al resultado select concat_ws(' ',first_name,last_name) actor, left(title,10) pelicula, substring(title,1,10) pel2, locate('epic',description)>0, description like '%epic%', if(description like '%epic%','Épica!!!!',''), replace(description, 'Epic','Épica!!!!'), if(description like '%drama%','Enternecedor drama',''), if(description like '%epic%','Épica!!!!',if(description like '%drama%','Enternecedor drama','')), case when description like '%epic%' then 'Épica!!!!' when description like '%drama%' then 'Enternecedor drama' else '' end, description, if (rating='R','Para adultos',''), case rating when 'R' then 'Para adultos' else '' end from actor join film_actor using(actor_id) join film using(film_id);
Mini ejercicio funciones
Quiero un listado de películas en las que mostraré:
título
Si el replacement_cost es menor de 20: barato
Si es mayor o igual: caro
SELECT title, if (replacement_cost<20,'barato','caro') tipo FROM sakila.film; select title, case when replacement_cost<20 then 'barato' else 'caro' end tipo from film
Ejemplos funciones MySql
Todas las funciones:
Referencia de funciones de MySql
Funciones de control de flujo:
CASE
value
WHEN [compare_value
] THEN result
[WHEN [compare_value
] THEN result
...] [ELSE result
] END
CASE WHEN [
condition
] THEN result
[WHEN [condition
] THEN result
...] [ELSE result
] END
Evalua una condición y dependiendo del resultado nos devuelve un valor u otro.
IF(
expr1
,valor_si_cierto
,valor_si_falso
)
Si expresión 1 es cierto devuelve valor_si_cierto y si no devuelve valor_si_falsi
Si la expresión 1 es nulo devuelve expresión 2
select rating, case rating when 'G' then 'Para todos los públicos' when 'PG' then 'Menores acompañados' when 'R' then 'Para adultos' else 'Otros' end tipo -- Alias para que no salga un churro from film select length, case when length<70 then 'Cortita' when length<130 then 'Normal' else 'Tostón' end duracion from film SELECT *, case when amount<4 then 'bajo' when amount <7 then 'medio' else 'alto' end as tipo , if (amount<6,'barato','caro') precio, if (amount between 4 and 6,'medio','') precio2, if(amount<4,'bajo',if(amount<7,'medio','alto')) tipo2 FROM sakila.payment; select case dayofweek(payment_date) when 1 then 'Domingo' when 2 then 'Lunes' when 3 then 'Martes' when 4 then 'Miércoles' when 5 then 'Jueves' when 6 then 'Viernes' when 7 then 'Sábado' end dias,count(payment_id) cuenta, sum(amount) total,avg(amount) media from payment group by dias; select *, case staff_id when 1 then 'Ana' when 2 then 'Juan' end as empleado FROM sakila.payment; select title, if(replacement_cost>20,concat('Descuento: ',replacement_cost*.9) ,replacement_cost) coste from film; select ifnull(8,0), ifnull(null,0),0+null,concat('Ana',null)
Funciones matemáticas:
Redondea un número a las posiciones decimales que queramos
select round(4.9), floor(4.9), ceil(4.9),truncate(4.9,0), round(-4.9), floor(-4.9), ceil(-4.9),truncate(-4.9,0), sign(4.9),sign(-4.9),abs(-4.9);
Funciones de cadena:
Concatena una o varias cadenas
CONCAT_WS(
separator
,str1
,str2
,...)
Lo mismo pero con un separador
LOCATE(
, substr
,str
)LOCATE(
substr
,str
,pos
)
Devuelve la posición de una cadena dentro de otra
Devuelve len caracteres de la cadena empezando por la izquierda
Devuelve la longitud de la cadena
LOWER(
str
) LCASE(str)
Pasa la cadena a minúsculas
Invierte la cadena
Devuelve len caracteres de la cadena empezando por la derecha
SUBSTRING(
, str
,pos
)SUBSTRING(
, str
FROM pos
)SUBSTRING(
, str
,pos
,len
)SUBSTRING(
str
FROM pos
FOR len
)
Para obtener subcadenas de una cadena desde una posición y con la longitud que queramos.
Pasa la cadena a mayúsculas
select concat(first_name,' ',last_name) cliente, concat_ws(' - ',first_name, last_name,email) nombre , length(first_name) longitud, left(first_name,2) izq, right(first_name,2) der, locate('@',email) pos, substr(last_name,4,2) subcadena, substr(email,1,locate('@',email)-1) lmail, substr(email,locate('@',email)+1) rmail, reverse(first_name) vuelta, lower(first_name) minusculas, upper(email) mayusculas from customer SELECT * FROM sakila.film where length(title)>20; select length(first_name), concat(first_name,' ',last_name), left(first_name,3), right(last_name,3) nombre, lcase(first_name), ucase(last_name) , concat(ucase(left(first_name,1)),lcase(substr(first_name,2))), reverse(first_name), replace(first_name,'P','##') from actor where first_name=reverse(first_name) update actor set first_name=concat(ucase(left(first_name,1)),lcase(substr(first_name,2))), last_name=concat(ucase(left(last_name,1)),lcase(substr(last_name,2))) where actor_id>=1
Funciones de fecha:
Fecha actual
Hora actual
Fecha y hora actual.
Formatea una fecha con el formato específicado.
DAYOFMONTH(
date
)DAYOFWEEK(
date
)DAYOFYEAR(
date
)
Devuelven el día del mes, de la semana y del año respectivamente
HOUR(
time
)MINUTE(
time
)SECOND(
time
)MONTH(
date
)YEAR(
date
)
Devuelven la hora, el minuto, el segundo, el mes y el año de una fecha
select curtime(),curdate(),now(), week(curdate()),year(curdate()),month(curdate()), day(curdate()),hour(curtime()),monthname(now()), dayname(now()),dayname('1985-03-07'), dayofyear(now()),last_day(now()), date_format(now(),'%e-%m-%y %H:%i'),utc_date() SELECT * FROM sakila.payment where dayofweek(payment_date)=3; SELECT * FROM sakila.payment where date_format(payment_date,'%w')=2; SELECT * FROM sakila.payment where day(payment_date)=day(curdate()) ;
Intervalos en Mysql:
https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals
Funciones de conversión:
Convierte una expresión al tipo que le digamos
select '8'>'100', cast('8' as signed)>cast('100' as signed);
Funciones de encriptación:
select aes_decrypt(aes_encrypt('Hola que tal','123'),'123'); select md5('hola que tal'), md5('tomateofñlkfñdfñlghfñgsipshdfisu'), sha1('hola que tal'), sha2('hola que tal', 256)
Funciones de información:
select database(), current_user(), last_insert_id(), row_count(), version(); insert into country(country) values('Utopía'); set @a=last_insert_id(); insert into city(city,country_id) values ('Nowhere',@a); insert into city(city,country_id) values ('Somewhere',@a);
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id
Ejemplos sql agrupados
Estadísticas de países:
select country pais,count(amount),sum(amount) total, avg(amount) media,max(amount) máximo, min(amount) minimo, variance(amount),std(amount) from country join city using(country_id) join address using(city_id) join customer using(address_id) join payment using(customer_id) group by pais order by media desc
Número de películas por actor:
SELECT first_name,last_name, count(film_id) total from actor left join film_actor using(actor_id) left join film using(film_id) group by first_name,last_name order by first_name desc
Ciudades por país:
select country,count(city_id) total from country join city using(country_id) group by country order by total asc
Categorías con más de 60 películas:
select name,count(film_id) total from category join film_category using(category_id) group by name having total>=60
Actor que ha participado en más películas:
select first_name,last_name,count(film_id) total from actor join film_actor using(actor_id) join film using(film_id) group by actor_id order by total desc limit 1
Total de categorías por actor (diferentes):
select distinct first_name,last_name,count(distinct name) from actor join film_actor using(actor_id) join film using(film_id) join film_category using(film_id) join category using(category_id) group by actor_id
Películas alquiladas por clientes:
select first_name,last_name, count(film_id) total from film join inventory using(film_id) join rental using(inventory_id) join customer using(customer_id) group by customer_id
Categoría más alquilada:
select name,count(customer_id) total from category join film_category using(category_id) join film using(film_id) join inventory using(film_id) join rental using(inventory_id) group by category_id order by total desc limit 1
Ejercicio agregados
Mostrar la categoría con más películas
Mostrar los cinco clientes que más han gastado
Mostrar los países que tengan menos de 10 clientes
Mostrar los actores que han trabajado en más de 20 películas
Mostrar los actores que han trabajado en 5 o más películas de acción
select name,count(film_id) total from category join film_category using (category_id) join film using (film_id) group by category_id order by total desc limit 1; select first_name, last_name, sum(amount) total from customer join payment using (customer_id) group by customer_id order by total desc limit 5; select country,count(customer_id) total from customer join address using (address_id) join city using (city_id) join country using (country_id) group by country_id having total<10; select first_name,last_name, count(film_id) total from actor join film_actor using (actor_id) join film using (film_id) group by actor_id having total>20; select first_name,last_name, count(film_id) total from actor join film_actor using (actor_id) join film using (film_id) join film_category using (film_id) join category using (category_id) where name='action' group by actor_id having total>=5;
Ejemplos agregados
select first_name, last_name, sum(amount) total , count(amount) numero , avg(amount) media, max(amount) maximo , min(amount) minimo from customer join payment using (customer_id) group by customer_id order by numero desc limit 5 -- limit solo un número: devuelve las primeras n filas dos numeros: a partir del primer número n filas -- limit 10: los 10 primeros. limit 10,10 a partir de la fila 10, 10 registros select first_name, last_name, sum(amount) total , count(amount) numero , avg(amount) media, max(amount) maximo , min(amount) minimo from customer join payment using (customer_id) where first_name like 'M%' -- Antes de calcular el total group by customer_id having total>150 -- Cuando ya se han calculado los totales -- ¿Cuál es el país con menos ciudades? select country, count(city_id) total from country join city using(country_id) group by country_id order by total asc limit 1; select country, count(customer_id) total from customer join address using(address_id) join city using(city_id) join country using(country_id) group by country_id order by total desc -- Películas y categorías select * from category join film_category using(category_id) join film using (film_id); -- Añado agrupación select name,count(film_id) from category join film_category using(category_id) join film using (film_id) group by category_id; -- Añado restricción sobre los campos -- ¿Cuantas películas de cada categoría tienen rating 'G' select name,count(film_id) total from category join film_category using(category_id) join film using (film_id) where rating='G' group by category_id; -- Añado restricción sobre el total -- ¿Cuantas categorías tienen más de 10 películas con rating 'G'? select name,count(film_id) total from category join film_category using(category_id) join film using (film_id) where rating='G' group by category_id having total>10
Funciones de agregado
https://www.mysqltutorial.org/mysql-group-by.aspx/
https://guru99.es/aggregate-functions/
select first_name, last_name, sum(amount) total , count(amount) numero , avg(amount) media, max(amount) maximo , min(amount) minimo from customer join payment using (customer_id) group by customer_id