¿Estás empezando a trabajar con las bases de datos relacionadas?. Hoy te traigo un generador automático de consultas básicas en SQL,una herramienta que te ayudará mucho.
Cuando uno empieza a estudiar cualquier lenguaje de programación, una cosa que no se puede dejar de lado es aprender a hacer consultas básicas a nuestra Base de Datos SQL (o cualquier otra, pero en este caso utilizaremos SQL).
Sobre la base de datos lo que solemos hacer el típico en indispensable CRUD, del que ya hablamos en esta anterior entrada. CRUD son las siglas de las operaciones que podemos hacer contra la base de datos:
CREATE (crear) , READ (leer) , UPDATE (actualizar) , DELETE (borrar)
Pues empecemos.
Normalmente, cuando empezamos con las bases de datos, muchos nos acordamos de las típicas hojas de Excel. Hojas interminable y llenas de datos, muchas veces repetidos o desestucturados.
UN CONSEJO…
SQL (Structured Query Language) es un lenguaje estándar e interactivo de acceso a bases de datos relacionales que permite especificar diversos tipos de operaciones en ellas, gracias a la utilización del álgebra y de cálculos relacionales, el SQL brinda la posibilidad de realizar consultas con el objetivo de recuperar información de las bases de datos de manera sencilla. Las consultas toman la forma de un lenguaje de comandos que permite seleccionar, insertar, actualizar, averiguar la ubicación de los datos, y más.
Este script pretende ser una ayuda para todos aquellos que empiezan a trabajar con SQL, en ningún caso pretende simplificar ni limitar su uso ya que es tremendamente extenso. Estoy seguro que será de gran utilidad para empezar el camino ayudándote a entender la estructura básica de estas bases de datos.
Sigamos…
En una base de datos relacionada, lo que se suele hacer es un sistema de Entidad-Relación, que nos permite relacionar tablas sin tener que tenerlo todo junto en una misma. Por ejemplo:
Imaginemos una tienda con clientes y comerciales. En lugar de tener una tabla con todos los datos, para hacer una base de datos relacionada creamos una tabla cliente, una tabla comercial, y una tabla intermedia que las una.
De ésta manera, esta tabla pedido, nos dirá qué cliente tiene relación con cada comercial mientras que en las tablas comercial y cliente sólo colocaremos los datos necesarios de cada comercial y de cada cliente (nombre, ciudad, otros datos…).
El esquema sería algo parecido a ésto:
Como puedes ver, la tabla central «pedido» hemos de incluir las FOREIGN KEYS de cada una de las otras tablas, en este caso id_cliente (enlazada con id de la tabla cliente) e id_comercial (enlazada con id de la tabla comercial)
MUY IMPORTANTE: Para que este Script funcione correctamente,los campos referenciados en las Foreign Keys ha de tener el mismo nombre.
Verás que id_comercial e id_cliente aparecen con ese mismo nombre en la tabla pedidos.
Sigamos con el ejemplo.
Es este caso sería muy sencillo hacer cualquier tipo de consulta, ya que únicamente se compone de 3 tablas. Si quisieramos saber la lista clientes tiene el comercial con id_comercial 1, la consulta sería así:
select id_cliente, concat(cliente.nombre," " ,cliente.apellido1)as nombre_cliente from cliente join pedido using (id_cliente) join comercial using (id_comercial) where id_comercial = 1 group by id_cliente
Y el resultado sería éste:
| id_cliente | nombre_cliente |
| 2 | Adela Salas |
| 6 | María Santana |
| 7 | Pilar Ruiz |
Pero claro, este tipo de tablas tan sencillas es poco probable que te las encuentres. Lo más normal será encontrarte con una base de datos con decenas de tablas interrelacionadas entre ellas . Por ejemplo, una base de datos tipo SAKILA.
SAKILA es una base de datos creada específicamente para poder practicar y aprender a trabajar sobre una base de datos de estas dimensiones. Éste es su esquema:
Sería muy facil sacar la lista de películas de un actor o el género más común entre las películas pero, ¿ y si te pidieran la lista de películas por orden de más dias de alquiler a menos?
Ahora deberíamos ir al esquema de nuestra base de datos (si lo tenemos) y empezar a recorrerla para llegar desde la tabla film a la tabla rental. También hay que tener en cuenta que, en muchas ocasiones y según cómo estén creadas las tablas relacionadas, no siempre hay un sólo camino.
Y éste script te mostrará todas las opciones posibles para que tú elijas la que más te interesa. Te lo muestro.
Ejecutamos nuestro script en LOCALHOST(no funciona desde servidor) y simplemente hemos de seleccionar :
- La base de datos sobre la que queremos operar, en este caso sakila, y le damos a Enviar.
- El origen, en este caso seleccionaremos film
- El final, en este ejemplo, seleccionamos rental.
Y una vez le clickamos a Enviar, nos mostrará todos los caminos posibles que unen las tablas film y rental. En este caso hay más de 20 opciones posibles.
Ahora, simplemente elegimos la primera opción y la modificamos según nuestras necesidades. Si la ponemos tal cual
select * from film join inventory using (film_id) join rental using (inventory_id)
recibiremos un resultado con todos los datos de las tablas que hemos usado, film, inventory y rental, que puede llegar a ser exageradamente pesada.
Pero nosotros no necesitamos todos esos datos ya que solo queremos saber la lista de películas por orden de más dias alquiladas a menos.
Para esto sólo tenemos que adaptar nuestra consulta inicial a la siguiente:
/* seleccionamos el film_id, el titulo (title) y la suma de los dias de alquiler de cada película, que lo obtenemos haciendo la diferencia entre el rental_date y return_date. Este dato lo guardamos como DateDiff*/ select film_id,title, sum(DATEDIFF( return_date, rental_date))AS DateDiff from film join inventory using (film_id) join rental using (inventory_id) /*agrupamos el resultado por film_id ya que tenemos varios alquileres de cada película. Si no lo hicieramos así, nos devolvería una pelicula con todas las horas de alquiler sumadas. Y el resultado lo ordenamos descendentemente para que nos muestra la pelicula más alquilada al principio*/ group by film_id order by DateDiff DESC
Y con esta consulta, recibimos la siguiente tabla:
Ya tenemos el listado de peliculas ordenadas por las más alquiladas a las menos.
Gracias a este script, a parte de facilitarte la tarea de realizar cunsultas algo más complicadas de lo normal, también puedes comprobar que las relaciones entre las tablas están correctamente ajustadas.
Y para finalizar, aqui te dejo el script.
Se compone de 2 archivos php:
- index.php
- camino.php
Modifica los datos de acceso a tu base de datos si es necesario:
$user = 'root'; $pass = ''; $server = 'localhost';
Y con esto ya lo tendrás todo listo para empezar a probar el script. Espero que sea de tu interés y te sirva para aprender, como me sirvió a mi.
Que lo disfrutes!!
index.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body style="text-align: center;">
<h2>Selecciona Base de datos:</h2>
<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'])) {
?>
<hr/>
<h3>Seleccione las tablas origen y fin</h3>
<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
}
?>
</body>
</html>
camino.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<?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 "<div style='width:23%;float:left;padding:1%'>
<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></div>";
}
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);
}
}
}
?>
</body>
</html>
No dudes en compartirlo con los tuyo o hacer algún comentario si lo deseas.