Mes: junio 2018

Ejemplo trigger

CREATE TABLE `customer_log` (
 `idcustomer_log` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `user` varchar(45) DEFAULT NULL,
 `fecha` datetime DEFAULT NULL,
 `nombre` varchar(45) DEFAULT NULL,
 `nombre_ant` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idcustomer_log`)
)

DELIMITER //
CREATE TRIGGER cliente_actualizado BEFORE UPDATE ON customer
FOR EACH ROW BEGIN

 DECLARE vUser varchar(50);

 -- Find username of person performing the INSERT into table
 SELECT USER() INTO vUser;

 -- Insert record into audit table
 INSERT INTO customer_log
 ( customer_id,
 fecha,
 user,nombre,nombre_ant)
 VALUES
 ( NEW.customer_id,
 SYSDATE(),
 vUser,NEW.first_name, OLD.first_name );

END;//

DELIMITER ;

Ejemplos desencadenadores (triggers)

Al borrar:

CREATE TRIGGER `sakila`.`actor_BEFORE_DELETE` BEFORE DELETE ON `actor` FOR EACH ROW
BEGIN

if OLD.actor_id between 1 and 20 then
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes borrar';
else
 insert into actor_log(first_name, last_name) values (OLD.first_name, OLD.last_name);
end if;
END

Al insertar:

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_INSERT` BEFORE INSERT ON `payment` FOR EACH ROW
BEGIN
if NEW.amount>=10 then
 set NEW.amount=9.99;
end if;
END

Al modificar:

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_UPDATE` BEFORE UPDATE ON `payment` FOR EACH ROW
BEGIN

if NEW.amount > OLD.amount then
 set NEW.amount=OLD.amount;
end if;

END

Ejercicios Northwind

Insertar en Customers la compañía ‘Compañía XX’ con ‘Ana’, ‘Pi’ como contacto

insert into customers (company,first_name,last_name)
values ('Compañía XX','Ana','Pi')

Modificar en Customer el campo email_address y poner ‘Sin email’

update customers
set email_address='Sin email'

Eliminar el Customer con id = 20

delete from customers
where id=20

Seleccionar todos los proveedores (suppliers) cuyo trabajo sea ‘Sales Manager’

SELECT * from suppliers where job_title='Sales manager'

Seleccionar todos los pedidos (orders) de junio

select * from orders
where month(order_date)=6

Calcular el número de pedidos por cliente

SELECT company,count(orders.id) total FROM customers left join orders on customers.id=orders.customer_id
group by customers.id

Calcular el total de ventas por empleado

SELECT last_name,first_name,sum(quantity*unit_price*(1-discount)) total FROM employees join orders on employees.id=orders.employee_id
join order_details on orders.id=order_details.order_id
group by employees.id

Averiguar el producto más caro

SELECT * FROM `products` order by list_price desc limit 1

Averiguar el número de ventas por producto

select products.*, sum(quantity*unit_price*(1-discount)) total from products join order_details on products.id= order_details.product_id
group by products.id

Mostrar los clientes con más de 10000 en ventas

select customers.*, sum(quantity*unit_price*(1-discount)) total from customers join orders on customers.id= orders.customer_id
join order_details on orders.id= order_details.order_id
group by customers.id
having total>10000

Crear joins en base de datos

index.php:

 <h1>Camino entre tablas</h1>
        Escoja tabla:
        <form>
            <select name="bd">
                <?php
                $user = 'root';
                $pass = '';
                $server = 'localhost';

                $dbh = new PDO("mysql:host=$server", $user, $pass);
                $dbs = $dbh->query('SHOW DATABASES');

                while (( $db = $dbs->fetchColumn(0) ) !== false) {
                    echo "<option>".$db . '</option>';
                }
                ?>
            </select>
            <input type="submit" value="Enviar"></form>
        <?php
        if (!empty($_GET['bd'])){
            ?>
        <p>Seleccione las tablas origen y fin</p>
        <form action="camino.php">
            <input type="hidden" name="bd" value="<?=$_GET['bd']?>">
            <label>Origen:</label>
            <select name="origen">
                <?php
                 $dbs = $dbh->query('USE '.$_GET['bd']);
                 $dbs = $dbh->query('SHOW TABLES');
                 
                while (( $db = $dbs->fetchColumn(0) ) !== false) {
                    echo "<option>".$db . '</option>';
                }
                ?>
            </select>
            <label>Fin:</label>
            <select name="fin"><?php
                 $dbs = $dbh->query('USE '.$_GET['bd']);
                 $dbs = $dbh->query('SHOW TABLES');
                 
                while (( $db = $dbs->fetchColumn(0) ) !== false) {
                    echo "<option>".$db . '</option>';
                }
                ?></select>
             <input type="submit" value="Enviar">
        </form>
        <?php
        }

camino.php

<?php

$bd = $_GET['bd'];
$origen = $_GET['origen'];
$fin = $_GET['fin'];

$user = 'root';
$pass = '';
$server = 'localhost';

$dbh = new PDO("mysql:host=$server", $user, $pass);
$dbs = $dbh->query('use ' . $bd);

$tablas = getTables($dbh);
$tablas = setFKPaths($tablas, $dbh, $bd);

$caminos = [];
buscarCamino($origen, $fin, $origen, 0);
usort($caminos, function($a, $b) {
    return strlen($a) - strlen($b);
});
foreach ($caminos as $camino) {
    $camino = explode(",", $camino);
    echo "<h4>select * from " . $camino[0];
    for ($i = 1; $i < count($camino); $i++) {
        $c = explode("/", $camino[$i]);
        echo " join " . $c[0] . " using (" . $c[1] . ")<br/>";
    }
    echo "</h4>";
}

function getTables($dbh) {
    $tablas = [];
    $dbs = $dbh->query('SHOW TABLES');

    while (( $db = $dbs->fetchColumn(0) ) !== false) {
        $tablas[$db] = [];
    }
    return $tablas;
}

function setFKPaths($tablas, $dbh, $bd) {
    $dbs = $dbh->query("select * from information_schema.INNODB_SYS_FOREIGN where id like '$bd%'");
    $fks = $dbs->fetchAll();
    foreach ($fks as $fk) {
        $dbs = $dbh->query("select * from information_schema.INNODB_SYS_FOREIGN_COLS where id = '$fk[0]'");
        $fc = $dbs->fetchAll();
        $t1 = explode("/", $fk[1])[1];
        $t2 = explode("/", $fk[2])[1];
        $k = $fc[0][1];
        if (!in_array($t1 . "/" . $k, $tablas[$t2])) {
            $tablas[$t2][] = $t1 . "/" . $k;
        }
        if (!in_array($t2 . "/" . $k, $tablas[$t1])) {
            $tablas[$t1][] = $t2 . "/" . $k;
        }
    }
    return $tablas;
}



function buscarCamino($inicio, $fin, $camino, $prof) {
    global $caminos;
    global $tablas;

    if ($inicio == $fin) {
        $caminos[] = $camino;
        return;
    }
    foreach ($tablas[$inicio] as $destino) {
        $d = explode("/", $destino)[0];
        if (strpos($camino, "," . $d . "/") === false && strpos($camino, $d . ",") === false) {
            buscarCamino($d, $fin, $camino . "," . $destino, $prof + 1);
        }
    }
}

Ejemplos subqueries

Pagos por encima de la media:


select * from payment where amount>(
select avg(amount) from payment
)

Películas de la categoría 1:


select distinct first_name,last_name from actor join film_actor using(actor_id) where film_id in
(
select film_id from film_category where category_id=1
)

Películas con el mayor coste de reemplazo:


select * from film where replacement_cost=(
select max(replacement_cost) from film
)

Películas de la categoría con más alquileres:


select * from film join film_category using(film_id)
where category_id in (
select category_id 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
having count(rental_id)=(
select max(total) from (
select count(rental_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
) alias_falso
)
)

 

Categorías – países

SELECT
country, name
FROM
category
JOIN
film_category ON category.category_id = film_category.category_id
JOIN
film ON film.film_id = film_category.film_id
JOIN
inventory ON film.film_id = inventory.film_id
JOIN
store ON inventory.store_id = store.store_id
JOIN
customer ON store.store_id = customer.store_id
JOIN
address ON address.address_id = customer.address_id
JOIN
city ON city.city_id = address.city_id
JOIN
country ON country.country_id = city.country_id