Categoría: BD

preExamen

//Insertar el producto 'jamon' de precio 150 €

$conn = new PDO("mysql:host=localhost;dbname=tienda", "root", "");
$conn->query("insert into productos (nombre,precio) values ('jamon',150)");

$sql = "insert into productos (nombre,precio) values (:nombre,:precio)";
$st = $conn->prepare($sql);
$st->execute(array('nombre' => 'jamon', 'precio' => 150));


//Insertar x productos
$productos = array('tortilla' => 20, 'jamon' => 150, 'doritos' => 5, 'patatas' => 10);
$sql = "insert into productos (nombre,precio) values (:nombre,:precio)";
$st = $conn->prepare($sql);
$st->bindParam(':nombre', $nombre);
$st->bindParam(':precio', $precio);
foreach ($productos as $nombre => $precio) {
 $st->execute();
}



//Código para saber el número total de productos

$sql = "select count(*) as total from productos";
$st = $conn->query($sql);
$fila = $st->fetch();
echo $fila['total'];



//Código que me muestre por pantalla los nombres de los productos, me da igual como
$sql = "select nombre from productos";
$st = $conn->query($sql);
$productos = $st->fetchAll();
foreach($productos as $clave=>$valor){
 echo $valor['nombre']."<br/>";
}


//Función de framework que me devuelva el total de clientes de la ciudad que le pasemos
 include "framework.php";
 $bd = new tiendaBD();
print_r($bd->clientesDe('bcn'));
//Función de framework que me devuelva todos losproductos más caros del precio que le paso.
print_r($bd->productosMasCarosQue(100));

//Función de framework que me devuelva el total de clientes de la ciudad que le pasemos

 function clientesDe($ciudad){
 $sql="select count(*) as total from clientes where ciudad='$ciudad'";
 $res= $this->getSqlRows($sql);
 return $res[0]['total'];
 }
 
 //Función de framework que me devuelva todos losproductos más caros del precio que le paso.
 
 function productosMasCarosQue($cantidad){
 $sql="select * from productos where precio>=$cantidad";
 return $this->getSqlRows($sql);
 }

Ejercicio modelo entidad relación

Una empresa de fabricación de tintas nos ha pedido una base de datos.

La empresa necesita guardar información de sus clientes, concretamente el NIF y la razón social. Los clientes hacen pedidos, de los que queremos saber la fecha y la dirección de envío (calle, cp y población).

Los pedidos pueden ser de varios productos. De los productos queremos guardar la referencia, el precio y el stock que tenemos en almacén.

Por otro lado los productos pueden ser productos básicos o fabricados, los fabricados se hacen a partir de otros productos (que pueden ser base o fabricados). Necesitamos guardar la composición de los productos fabricados que incluye los productos, la cantidad de cada uno y el orden en el que hay que añadirlos.

Datos de prueba:

Clientes:

Sugus, nif b1234354

Grefusa, nif b75756756

S&D, nif b66612333

 

Productos:

Disolvente, 1 €, 2000

Emulsionante, 3 €, 1500

Colorante rojo, 4 €, 200

Colorante azul, 4 €, 300

Colorante amarillo, 4 €, 250

Tinta naranja, 10 €, 100

Tinta verde, 10 €, 100

 

La tinta naranja se compone en este orden de

Disolvente 5

Colorante rojo 7

Colorante amarillo 10

Emulsionante 10

La tinta verde se compone en este orden de

Disolvente 5

Colorante azul 7

Colorante amarillo 10

Emulsionante 10

Sugus hizo un pedido de 10 unidades de tinta verde el 2 de febrero para enviar a la calle Agla,5, 08001 Barcelona.

Sugus hizo un pedido de 10 unidades de tinta naranja el 5 de febrero para enviar a la calle Agla,5, 08001 Barcelona.

S&D hizo un pedido de 10 unidades de disolvente y 5 de colorante rojo el 10 de febrero para enviar al carrer perill, 15, 08012 Barcelona.

Se pide:

Insertar el cliente ‘Copistería Martínez’, ‘B21312312’
Modificar el precio de todos los productos para incrementar un 10%
Añadir 10 a la cantidad de la composición de todos los productos
¿Qué productos han comprado los clientes que tienen una ‘u’ en el nombre?
Añade el producto ‘aerosol rojo’, 10 €, 20
Elimina los productos cuyo nombre contenga aerosol
¿Qué clientes no tienen pedidos?
Muestra el total de ventas por cliente, incluyendo los que no tienen pedidos
Muestra el total de unidades vendidas por producto, incluyendo los que no tienen ninguna unidad vendidas
Lista de productos finales
Lista de productos que no son finales
Clientes que nunca han comprado un producto que no sea finales

 

CREATE TABLE `tintas`.`cliente` (
 `idcliente` INT NOT NULL AUTO_INCREMENT,
 `nif` VARCHAR(45) NOT NULL,
 `razon_social` VARCHAR(45) NOT NULL,
 PRIMARY KEY (`idcliente`) );

 CREATE TABLE `tintas`.`pedido` (
 `idpedido` INT NOT NULL AUTO_INCREMENT,
 `idcliente` INT NOT NULL,
 `fecha` DATE NOT NULL,
 `direccion` VARCHAR(45) NOT NULL,
 `cp` VARCHAR(45) NOT NULL,
 `poblacion` VARCHAR(45) NOT NULL,
 PRIMARY KEY (`idpedido`) );

 CREATE TABLE `tintas`.`producto` (
 `idproducto` INT NOT NULL AUTO_INCREMENT,
 `referencia` VARCHAR(45) NOT NULL,
`precio` DECIMAL(10,2) NOT NULL,
 `stock` INT NOT NULL,
 PRIMARY KEY (`idproducto`) );

 
 CREATE TABLE `tintas`.`lineaspedido` (
 `idlineaspedido` INT NOT NULL AUTO_INCREMENT,
 `idpedido` INT NOT NULL,
 `idproducto` INT NOT NULL,
 `cantidad` INT NOT NULL,
 `precio` DECIMAL(10,2) NOT NULL,
 PRIMARY KEY (`idlineaspedido`) );

 CREATE TABLE `composicion` (
 `idcomposicion` int(11) NOT NULL AUTO_INCREMENT,
 `idproducto_final` int(11) NOT NULL,
 `idproducto_comp` int(11) NOT NULL,
 `cantidad` int(11) NOT NULL,
 `orden` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`idcomposicion`)
) 


ALTER TABLE `tintas`.`pedido` 
ADD INDEX `fk_cliente_idx` (`idcliente` ASC) ;
ALTER TABLE `tintas`.`pedido` 
ADD CONSTRAINT `fk_cliente`
 FOREIGN KEY (`idcliente`)
 REFERENCES `tintas`.`cliente` (`idcliente`)
 ON DELETE RESTRICT
 ON UPDATE RESTRICT;

 
 ALTER TABLE `tintas`.`lineaspedido` 
ADD INDEX `fk_pedido_idx` (`idpedido` ASC) ,
ADD INDEX `fk_producto_idx` (`idproducto` ASC) ;
ALTER TABLE `tintas`.`lineaspedido` 
ADD CONSTRAINT `fk_pedido`
 FOREIGN KEY (`idpedido`)
 REFERENCES `tintas`.`pedido` (`idpedido`)
 ON DELETE RESTRICT
 ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_producto`
 FOREIGN KEY (`idproducto`)
 REFERENCES `tintas`.`producto` (`idproducto`)
 ON DELETE RESTRICT
 ON UPDATE RESTRICT;

 
 ALTER TABLE `tintas`.`composicion` 
ADD INDEX `fk_productof_idx` (`idproducto_final` ASC) ,
ADD INDEX `fk_productoc_idx` (`idproducto_comp` ASC) ;
ALTER TABLE `tintas`.`composicion` 
ADD CONSTRAINT `fk_productof`
 FOREIGN KEY (`idproducto_final`)
 REFERENCES `tintas`.`producto` (`idproducto`)
 ON DELETE RESTRICT
 ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_productoc`
 FOREIGN KEY (`idproducto_comp`)
 REFERENCES `tintas`.`producto` (`idproducto`)
 ON DELETE RESTRICT
 ON UPDATE NO ACTION;

 
 INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('b1234354', 'sugus');
INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('b75756756', 'grefusa');
INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('b66612333', 's&D');


INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Disolvente', '1', '2000');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Emulsionante', '3', '1500');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Colorante rojo', '4', '200');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Colorante azul', '4', '300');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Colorante amarillo', '4', '250');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Tinta naranja', '10', '100');
INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Tinta verde', '10', '100');

INSERT INTO `tintas`.`pedido` (`fecha`, `direccion`, `cp`, `poblacion`, `idcliente`) VALUES ('2017-2-2', 'Agla, 5', '08001', 'Barcelona', '1');
INSERT INTO `tintas`.`pedido` (`fecha`, `direccion`, `cp`, `poblacion`, `idcliente`) VALUES ('2017-2-5', 'Agla, 5', '08001', 'Barcelona', '1');
INSERT INTO `tintas`.`pedido` (`fecha`, `direccion`, `cp`, `poblacion`, `idcliente`) VALUES ('2017-2-10', 'Perill, 15', '08012', 'Barcelona',3);

INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('1', '7', '10', '10');
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('2', '6', '10', '10');
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('3', '1', '10', '3');
INSERT INTO `tintas`.`lineaspedido` (`idpedido`, `idproducto`, `cantidad`, `precio`) VALUES ('3', '3', '5', '4');


INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '1', '5', '1');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '3', '7', '2');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '5', '10', '3');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('6', '2', '10', '4');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '1', '5', '1');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '4', '7', '2');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '5', '10', '3');
INSERT INTO `tintas`.`composicion` (`idproducto_final`, `idproducto_comp`, `cantidad`, `orden`) VALUES ('7', '2', '10', '4');

select * from cliente join pedido using(idcliente)
join lineaspedido using (idpedido)
join producto using (idproducto)

select * from producto
join composicion on producto.idproducto=composicion.idproducto_final
join producto p2 on composicion.idproducto_comp=p2.idproducto

Insertar el cliente 'Copistería Martínez', 'B21312312'

INSERT INTO `tintas`.`cliente` (`nif`, `razon_social`) VALUES ('B21312312', 'Copistería Martínez');


Modificar el precio de todos los productos para incrementar un 10%

update producto
set precio=precio*1.1

Añadir 10 a la cantidad de la composición de todos los productos

update tintas.composicion
set cantidad=cantidad+10



Añade el producto 'aerosol rojo', 10 €, 20

INSERT INTO `tintas`.`producto` (`referencia`, `precio`, `stock`) VALUES ('Aerosol rojo', '10', '20');

Elimina los productos cuyo nombre contenga aerosol

delete FROM tintas.producto
where referencia like '%aerosol%';

¿Qué productos han comprado los clientes que tienen una 'u' en el nombre?
select distinct razon_social, referencia from cliente join pedido using(idcliente)
join lineaspedido using (idpedido)
join producto using (idproducto)
where razon_social like '%u%'

¿Qué clientes no tienen pedidos?
SELECT * FROM tintas.cliente left join pedido using (idcliente) where idpedido is null;

Muestra el total de ventas por cliente, incluyendo los que no tienen pedidos
select razon_social, ifnull(sum(cantidad*precio),0) total from cliente left join pedido using(idcliente)
left join lineaspedido using (idpedido)
group by razon_social

Muestra el total de unidades vendidas por producto, incluyendo los que no tienen ninguna unidad vendidas
select referencia, ifnull(sum(cantidad),0) total
from producto left join lineaspedido using(idproducto)
group by referencia

Lista de productos finales
select referencia from producto
left join composicion on producto.idproducto=composicion.idproducto_comp
where idcomposicion is null

Lista de productos que no son finales
select distinct referencia from producto
join composicion on producto.idproducto=composicion.idproducto_comp

Clientes que nunca han comprado un producto que no sea finales
select * from cliente 
where idcliente not in (
select distinct idcliente
from cliente left join pedido using(idcliente)
left join lineaspedido using (idpedido)
where idproducto in (select distinct idproducto from producto
join composicion on producto.idproducto=composicion.idproducto_comp
)
)

Más funciones de mysql

Determinar si una cadena es o no vocal:

CREATE FUNCTION `esvocal`(cadena char(1)) 
RETURNS tinyint(1)
BEGIN


RETURN lcase(cadena) regexp '[aeiou]';
END

Contar las vocales de una cadena:

CREATE 
FUNCTION `vocales`(cadena varchar(100)) 
RETURNS int(11)
BEGIN

declare total,cont int default 0;

while cont<length(cadena) do
set cont=cont+1;

if esvocal(substring(cadena,cont,1)) then
 set total=total+1;
end if;

end while;

RETURN total;
END

Cambiar las vocales a mayúsculas y el resto a minúsculas:

CREATE 
FUNCTION `texto_molon`(cadena varchar(100)) 
RETURNS varchar(100) 
BEGIN
declare cont int default 1;
declare res varchar(100) default '';
declare c varchar(1);
while cont<length(cadena) do
set c=substring(cadena,cont,1);
if esvocal(c) then
 set res=concat(res,ucase(c));
 else
 set res=concat(res,lcase(c));
end if;

set cont=cont+1;
end while;
RETURN res;
END

Ejemplos de funciones en mysql

Prueba de variables:

CREATE FUNCTION `borrame` ()
RETURNS INTEGER
BEGIN

declare var int;
declare nombre varchar(50);
declare num int;

set nombre='juan';

set var=5;

set var=var+3;

select count(actor_id) into num from actor;

RETURN num+var+length(nombre);
END

Usar un parámetro y un into variable:

CREATE FUNCTION `actores_como`(cadena varchar(50)) 
RETURNS int(11)
BEGIN

declare total int;
select count(actor_id) into total from actor 
where first_name like concat('%',cadena,'%') 
or last_name like concat('%',cadena,'%') ;
RETURN total;
END

Uso de if:

CREATE 
FUNCTION `tipo_sueldo`(sueldo INT) 
RETURNS varchar(10) 
BEGIN
DECLARE a VARCHAR(10);

IF sueldo>1500 then
RETURN 'ALTO';
ELSE
RETURN 'BAJO';
END IF;

END

Uso de case:

CREATE FUNCTION `tipo_sueldo_ampliado`(sueldo int) 
RETURNS varchar(30) 
BEGIN
declare tipo varchar(30);

case
 when sueldo<1500 then
 set tipo='bajo';
 when sueldo<2500 then
 set tipo='medio';
 else
 set tipo='alto';
end case;

RETURN tipo;
END

Uso de while:

CREATE FUNCTION `factorial`(num int)
 RETURNS int(11)
BEGIN
declare total,cont int default 1;

while cont<=num do
 set total=total*cont;
 set cont=cont+1;
end while;

RETURN total;
END

Ejemplos sentencias sql

Actores con más de 10 películas

select first_name, last_name, count(film_id) peliculas from
actor join film_actor using (actor_id)
group by first_name,last_name
having peliculas>30

Países con más de 10 ciudades

select country, count(city) cities
from country join city using (country_id)
group by country
having cities>10

Los diez países que más gastan

select country, sum(amount) total, avg(amount) media
from country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)
group by country
order by total desc
limit 0,10

El actor con más películas

select first_name, last_name, count(film_id) peliculas
from actor join film_actor using(actor_id)
group by first_name,last_name
order by peliculas desc
limit 1