Archivo
Averiguar el número de tablas de una base de datos
La consulta es sencilla:
select count(*) from information_schema.tables where table_schema = ‘nombredelabasededatos‘;
Dentro de cada servidor de base de datos de MySQL/MariaDB tendremos una base de datos llamada «information_schema«. Dentro de la misma, una tabla llamada tables. Es en esta en la que encontraremos el catálogo de todas las tablas del servidor.
En un hosting que utilizo para mis pruebas he utilizado esta consulta y funciona, por lo que imagino que tengo privilegios (de lectura) de la tabla. (Me planteo si podré consultar las tablas del resto de usuarios, por ahora me voy a quedar con la duda).
Esta tabla tiene los siguientes campos:
TABLE_CATALOG varchar(512) No
TABLE_SCHEMA varchar(64) No
TABLE_NAME varchar(64) No
TABLE_TYPE varchar(64) No
ENGINE varchar(64) Sí NULL
VERSION bigint(21) UNSIGNED Sí NULL
ROW_FORMAT varchar(10) Sí NULL
TABLE_ROWS bigint(21) UNSIGNED Sí NULL
AVG_ROW_LENGTH bigint(21) UNSIGNED Sí NULL
DATA_LENGTH bigint(21) UNSIGNED Sí NULL
MAX_DATA_LENGTH bigint(21) UNSIGNED Sí NULL
INDEX_LENGTH bigint(21) UNSIGNED Sí NULL
DATA_FREE bigint(21) UNSIGNED Sí NULL
AUTO_INCREMENT bigint(21) UNSIGNED Sí NULL
CREATE_TIME datetime Sí NULL
UPDATE_TIME datetime Sí NULL
CHECK_TIME datetime Sí NULL
TABLE_COLLATION varchar(32) Sí NULL
CHECKSUM bigint(21) UNSIGNED Sí NULL
CREATE_OPTIONS varchar(2048) Sí NULL
TABLE_COMMENT varchar(2048) No
.
Trabajar en MySQL/MariaDB con un campo varchar en lugar de date
Sí, hay veces que cometes el error de importar datos desde un CSV, un TXT o un XLS a una BD MySQL/MariaDB y no te das cuenta de que has almacenado un campo, que en realidad es una fecha, como una cadena de caracteres.
Es decir, tengo mi campo «Cumpleaños» como Varchar en lugar de Date.
Cuando comiences a hacer consultas te darás cuenta de que tienes un problema, sobre todo cuando empieces a consultar por años, meses, días de la semana, etc.
Lo lógico es hacer un ALTER TABLE seguido de un UPDATE, generando un campo nuevo, ahora sí como date y llenándolo de los valores que inicialmente almacenaste en el varchar, pero hay veces que ya no se puede. Imposible. Porque has desarrollado ya todo y no puedes cambiar la estructura de la BD.
Chapuza para solucionarlo: la función STR_TO_DATE
Esta función recibe 2 parámetros, el nombre del campo y el patrón que sigue la fecha almacenada en esa cadena.
Ejemplo:
select sum(aviones) as 'Número de viajes', year(STR_TO_DATE(fechaida, '%d/%m/%Y')) as Año from viajes group by Año
Lo único «complicado» es el patrón. Tienes que ponerlo exactamente como está almacenado en la base de datos en la cadena de caracteres. En el ejemplo sería algo como:
20/05/2017
.
Agrupamiento de una consulta de Unión
Estaba trabajando en alguna consulta de SQL sobre MySQL levemente compleja. Cogía datos de 3 tablas y generaba, mediante una UNION una serie de datos. A estos necesitaba hacerles un GROUP BY.
¿Cómo agrupar el resultado de una consulta de Unión?
Ejemplo: (SELECT id_accv, round(x) as X, round(y) as Y from accv where X is not NULL) UNION (SELECT id_accv, round(direccion.x) as X, round(direccion.y) as Y from accv, direccion where accv.X is NULL and fid_codvia2 is NULL and accv.fid_codvia = direccion.fid_codvia and accv.portal = direccion.acctexto) UNION (SELECT id_accv, round(interseccion.x) as X, round(interseccion.y) as Y from accv, interseccion where accv.X is NULL and accv.fid_codvia2 is not NULL and accv.fid_codvia = interseccion.fid_codvia and accv.fid_codvia2 = interseccion.fid_codvia2)
Con la anterior consulta consigo un listado de ID con sus coordenadas X e Y correspondientes.
Ahora, ¿cómo consigo agrupar los puntos y saber cuántos ID hay en cada punto?
Es fácil, hago un ALIAS de esta consulta dentro de la cláusula FROM de la consulta de agrupamiento. El resultado sería:
SELECT count(id_accv), x, y from ( (SELECT id_accv, round(x) as X, round(y) as Y from accv where year(fechaaccidente)=2013 and X is not NULL) UNION (SELECT id_accv, round(direccion.x) as X, round(direccion.y) as Y from accv, direccion where year(fechaaccidente)=2013 and accv.X is NULL and accv.fid_codvia2 is NULL and accv.fid_codvia = direccion.fid_codvia and accv.portal = direccion.acctexto) UNION (SELECT id_accv, round(interseccion.x) as X, round(interseccion.y) as Y from accv, interseccion where year(fechaaccidente)=2013 and accv.X is NULL and accv.fid_codvia2 is not NULL and accv.fid_codvia = interseccion.fid_codvia and accv.fid_codvia2 = interseccion.fid_codvia2) ) as Tablauni group by X, Y
Consultas SELECT sobre BD Contactos
Una vez importada a MySQL la BD CONTACTOS.MDB hacer las siguientes consultas:
- Número de registros de la tabla Usuarios
- Nombre y apellidos de las personas que viven en Valencia.
- Nombre, apellidos y dirección de correo de las mujeres que no están casadas.
- Alias y número de teléfono de los hombres que tienen la EGB o el Bachillerato.
- Alias, número de teléfono móvil y dirección de correo de los hombres que hablan castellano y tienen los ojos marrones
- Alias de las mujeres que les gusta el deporte
- Alias de Mujeres heterosexuales o bisexuales que miden más de 1.60m
- Alias de Hombres que no tienen fobias y cuyos ingresos mensuales sean mayores de 1000 euros
- Número de hombres por cada ciudad
- Número medio de hijos que tienen las mujeres que pesan más de 70 kg
- Alias de las mujeres rubias de entre 25 y 40 años.
- Altura media de los hombres calvos
- Nombre de las ciudades en la que la media del peso de los hombres es superior a 80.
- Nombre de las ciudades en las que hay más de 5 mujeres dadas de alta
Ejercicio: Piezas y Proveedores
- Crea la base de datos Suministros
- Crea las 3 tablas anteriores
- Inserta al menos 5 piezas y 5 proveedores (uno de ellos debe tener código HAL). También inserta en la tabla Suministra al menos 10 filas.
- Realiza consultas select para obtener:
- Nombres de todos los artículos
- Todos los datos de los proveedores
- Precio medio de todas las piezas
- Nombre de los proveedores que suministran la pieza 1.
- Nombres de piezas suministradas por proveedor cuyo código es HAL
- Nombre del proveedor que suministra la pieza más cara
- Aumentar los precios en una unidad
- Hacer constar que la empresa «Tornillos SL» (con código TOSL) va a comenzar a suministrarnos tornillos a 7 pesetas.
- Nombre de las piezas que suministra «Tornillos SL»
- Nombre del proveedor y número de piezas que suministra.
- Nombre de las piezas cuyo precio medio sea mayor a 10 euros y menor de 20.
- Diferencia entre el producto más caro y el más barato.
- Ordena los proveedores por nombre ascendentemente y muestra sólo los 3 primeros.
- Nombre de los proveedores que empiezan por A (si no tienes ninguno que empiece por A, utiliza otra letra para hacer la prueba).
..
CREATE TABLE SUMINISTRA(
codigopieza integer,
idproveedor varchar(4),
precio integer,
primary key (codigopieza,idproveedor),
key(codigopieza),
foreign key (codigopieza) references piezas(codigo)
on delete cascade on update cascade,
key(idproveedor),
foreign key (idproveedor) references proveedores(id)
on delete cascade on update cascade);
CONSULTAS:
SELECT * FROM PROVEEDORES;
SELECT AVG(PRECIO) FROM SUMINISTRA;
SELECT NOMBRE FROM PROVEEDORES, SUMINISTRA WHERE CODIGOPIEZA=1 AND
ID=IDPROVEEDOR;
SELECT NOMBRE FROM PIEZAS, SUMINISTRA WHERE IDPROVEEDOR='HAL' AND
CODIGOPIEZA=CODIGO;
SELECT NOMBRE FROM PROVEEDORES, SUMINISTRA WHERE IDPROVEEDOR=ID AND
PRECIO=(SELECT MAX(PRECIO) FROM SUMINISTRA);
UPDATE SUMINISTRA SET PRECIO=PRECIO+1;
INSERT INTO PROVEEDORES(ID,NOMBRE) VALUES ('TOSL', 'Tornillos SL');
INSERT INTO PIEZAS(CODIGO, NOMBRE) VALUES (17, 'Tornillo');
INSERT INTO SUMINISTRA VALUES (17, 'TOSL', 7);
SELECT PIEZAS.NOMBRE FROM PIEZAS, SUMINISTRA, PROVEEDORES
WHERE PROVEEDORES.NOMBRE='Tornillos SL' AND ID=IDPROVEEDOR AND
CODIGOPIEZA=CODIGO;
SELECT PROVEEDORES.NOMBRE, COUNT(*)
FROM PROVEEDORES, SUMINISTRA
WHERE IDPROVEEDOR=ID
GROUP BY IDPROVEEDOR;
SELECT NOMBRE
FROM PIEZAS, SUMINISTRA
WHERE CODIGO=CODIGOPIEZA
GROUP BY CODIGOPIEZA
HAVING AVG(PRECIO)>10 AND AVG(PRECIO)<20;
SELECT MAX(PRECIO) - MIN(PRECIO) AS DIFERENCIA
FROM SUMINISTRA;
SELECT NOMBRE
FROM PROVEEDORES
ORDER BY NOMBRE ASC
LIMIT 3
SELECT NOMBRE
FROM PROVEEDORES
WHERE NOMBRE LIKE 'A%';
Importar y exportar datos
MySQL permite copiar tablas en diferentes formatos de texto, así como importar datos a partir de fichero de texto en diferentes formatos.
Esto se puede usar para exportar los datos de nuestras bases de datos a otras aplicaciones, o bien para importar datos desde otras fuentes a nuestras tablas. También se puede usar para hacer copias de seguridad y restaurarlas posteriormente.
Exportar a otros ficheros
Para extraer datos desde una base de datos a un fichero se usa la sentencia SELECT … INTO OUTFILE.
El resto de las cláusulas de SELECT siguen siendo aplicables, la única diferencia es que la salida de la selección se envía a un fichero en lugar de hacerlo a la consola.
La sintaxis de la parte INTO OUTFILE es:
[INTO OUTFILE 'file_name' export_options]
file_name es el nombre del fichero de salida. Ese fichero no debe existir, ya que en caso contrario la sentencia fallará.
En cuanto a las opciones de exportación son las mismas que para las cláusulas FIELDS y LINES de LOAD DATA. Su sintaxis es:
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
Estas cláusulas nos permiten crear diferentes formatos de ficheros de salida.
La cláusula FIELDS se refiere a las opciones de cada columna:
- TERMINATED BY ‘carácter’: nos permite elegir el carácter delimitador que se usará para separar cada columna. Por defecto, el valor que se usa es el tabulador, pero podemos usar ‘;’, ‘,’, etc.
- [OPTIONALLY] ENCLOSED BY ‘carácter’: sirve para elegir el carácter usado para entrecomillar cada columna. Por defecto no se entrecomilla ninguna columna, pero podemos elegir cualquier carácter. Si se añade la palabra OPTIONALLY sólo se entrecomillarán las columnas de texto y fecha.
- ESCAPED BY ‘carácter’: sirve para indicar el carácter que se usará para escapar aquellos caracteres que pueden dificultar la lectura posterior del fichero. Por ejemplo, si terminamos las columnas con ‘,’ y no las entrecomillamos, un carácter ‘,’ dentro de una columna de texto se interpretará como un separador de columnas. Para evitar esto se puede escapar esa coma con otro carácter. Por defecto se usa el carácter ‘\’.
La cláusula LINES se refiere a las opciones para cada fila:
- STARTING BY ‘carácter’: permite seleccionar el carácter para comenzar cada línea. Por defecto no se usa ningún carácter para ello.
- TERMINATED BY ‘carácter’: permite elegir el carácter para terminar cada línea. Por defecto es el retorno de línea, pero se puede usar cualquier otro carácter o caracteres, por ejemplo ‘\r\n’.
Por ejemplo, para obtener un fichero de texto a partir de la tabla ‘gente’, con las columnas delimitadas por ‘;’, entrecomillando las columnas de texto con ‘»‘ y separando cada fila por la secuencia ‘\r\n’, usaremos la siguiente sentecia:
mysql> SELECT * FROM gente
-> INTO OUTFILE "gente.txt"
-> FIELDS TERMINATED BY ';'
-> OPTIONALLY ENCLOSED BY '\"'
-> LINES TERMINATED BY '\n\r';
Query OK, 5 rows affected (0.00 sec)
mysql>
El fichero de salida tendrá este aspecto:
"Fulano";"1974-04-12" "Mengano";"1978-06-15" "Tulano";"2000-12-02" "Pegano";"1993-02-10" "Mengano";\N
La fecha para «Mengano» era NULL, para indicarlo se muestra el valor \N.
Importar a partir de ficheros externos
Por supuesto, el proceso contrario también es posible. Podemos leer el contenido de un fichero de texto en una tabla. El fichero origen puede haber sido creado mediante una sentecia SELECT … INTO OUTFILE, o mediante cualquier otro medio.
Para hacerlo disponemos de la sentencia LOAD DATA, cuya sintaxis más simple es:
LOAD DATA [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]
La cláusula LOCAL indica, si aparece, que el fichero está en el ordenador del cliente. Si no se especifica el fichero de texto se buscará en el servidor, concretamente en el mismo directorio donde esté la base de datos. Esto nos permite importar datos desde nuestro ordenador en un sistema en que el servidor de MySQL se encuentra en otra máquina.
Las cláusulas REPLACE e IGNORE afectan al modo en que se tratan las filas leídas que contengan el mismo valor para una clave principal o única para una fila existente en la tabla. Si se especifica REPLACE se sustituirá la fila actual por la leída. Si se especifica IGNORE el valor leído será ignorado.
La parte INTO TABLA tbl_name indica en qué tabla se insertarán los valores leídos.
No comentaremos mucho sobre las cláusulas FIELDS y LINES ya que su significado es el mismo que vimos para la sentencia SELECT … INTO OUTFILE. Estas sentencias nos permiten interpretar correctamente cada fila y cada columna, adaptándonos al formato del fichero de texto de entrada.
La misma utilidad tiene la cláusula IGNORE número LINES, que nos permite que las primeras número líneas no se interpreten como datos a importar. Es frecuente que los ficheros de texto que usaremos como fuente de datos contengan algunas cabeceras que expliquen el contenido del fichero, o que contengan los nombres de cada columna. Usando esta cláusula podemos ignorarlas.
La última parte nos permite indicar la columna a la que será asignada cada una de las columnas leídas, esto será útil si el orden de las columnas en la tabla no es el mismo que en el fichero de texto, o si el número de columnas es diferente en ambos.
Por ejemplo, supongamos que queremos añadir el contenido de este fichero a la tabla «gente»:
Fichero de datos de "gente" fecha,nombre 2004-03-15,Xulana 2000-09-09,Con Clase 1998-04-15,Pingrana
Como vemos, hay dos filas al principio que no contienen datos válidos, las columnas están separadas con comas y, como hemos editado el fichero con el «notepad», las líneas terminan con «\n\r». La sentencia adecuada para leer los datos es:
mysql> LOAD DATA INFILE "gente.txt"
-> INTO TABLE gente
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\r\n'
-> IGNORE 2 LINES
-> (fecha,nombre);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
El nuevo contenido de la tabla es:
mysql> SELECT * FROM gente; +-----------+------------+ | nombre | fecha | +-----------+------------+ | Fulano | 1974-04-12 | | Mengano | 1978-06-15 | | Tulano | 2000-12-02 | | Pegano | 1993-02-10 | | Mengano | NULL | | Xulana | 2004-03-15 | | Con Clase | 2000-09-09 | | Pingrana | 1998-04-15 | +-----------+------------+ 8 rows in set (0.00 sec) mysql>
Uniones
ambién es posible realizar la operación de álgebra relacional unión entre varias tablas o proyecciones de tablas.
Para hacerlo se usa la sentencia UNION que permite combinar varias sentencias SELECT para crear una única tabla de salida.
Las condiciones para que se pueda crear una unión son las mismas que vimos al estudiar el álgebra relacional: las relaciones a unir deben tener el mismo número de atributos, y además deben ser de dominios compatibles.
Veamos un ejemplo:
mysql> CREATE TABLE stock1 (
-> id INT NOT NULL,
-> nombre VARCHAR(30),
-> cantidad INT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE stock2 (
-> id INT NOT NULL,
-> nombre VARCHAR(40),
-> cantidad SMALLINT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.16 sec)
mysql> CREATE TABLE stock3 (
-> id INT NOT NULL,
-> nombre VARCHAR(35),
-> numero MEDIUMINT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO stock1 VALUES
-> (1, "tornillo M3x12", 100),
-> (2, "tornillo M3x15", 120),
-> (3, "tornillo M4x25", 120),
-> (4, "tornillo M5x30", 200);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO stock2 VALUES
-> (10, "tuerca M4", 120),
-> (11, "tuerca M3", 100),
-> (12, "tuerca M5", 87);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO stock3 VALUES
-> (20, "varilla 10", 23),
-> (1, "tornillo M3x12", 22),
-> (21, "varilla 12", 32),
-> (11, "tuerca M3", 22);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
Podemos crear una unión de las tres tablas, a pesar de que los nombres y tamaños de algunas columnas sean diferentes:
mysql> SELECT * FROM stock1 UNION
-> SELECT * FROM stock2 UNION
-> SELECT * FROM stock3;
+----+----------------+----------+
| id | nombre | cantidad |
+----+----------------+----------+
| 1 | tornillo M3x12 | 100 |
| 2 | tornillo M3x15 | 120 |
| 3 | tornillo M4x25 | 120 |
| 4 | tornillo M5x30 | 200 |
| 10 | tuerca M4 | 120 |
| 11 | tuerca M3 | 100 |
| 12 | tuerca M5 | 87 |
| 1 | tornillo M3x12 | 22 |
| 11 | tuerca M3 | 22 |
| 20 | varilla 10 | 23 |
| 21 | varilla 12 | 32 |
+----+----------------+----------+
11 rows in set (0.00 sec)
mysql>
El resultado se puede ordenar usando ORDER BY y también podemos seleccionar un número limitado de filas mediante LIMIT:
mysql> (SELECT * FROM stock1) UNION
-> (SELECT * FROM stock2) UNION
-> (SELECT * FROM stock3) ORDER BY id LIMIT 6;
+----+----------------+----------+
| id | nombre | cantidad |
+----+----------------+----------+
| 1 | tornillo M3x12 | 100 |
| 1 | tornillo M3x12 | 22 |
| 2 | tornillo M3x15 | 120 |
| 3 | tornillo M4x25 | 120 |
| 4 | tornillo M5x30 | 200 |
| 10 | tuerca M4 | 120 |
+----+----------------+----------+
6 rows in set (0.00 sec)
mysql>
Dentro de cada sentencia SELECT se aplican todas las cláusulas, proyecciones y selecciones que se quiera, como en cualquier SELECT normal.
La sintaxis completa incluye dos modificadores:
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
Los modificadores ALL y DISTINCT son opcionales, y si no se usa ninguno el comportamiento es el mismo que si se usa DISTINCT.
Con ALL se muestran todas las filas, aunque estén repetidas, con DISTINCT sólo se muestra una copia de cada fila:
mysql> SELECT id,nombre FROM stock1 UNION
-> SELECT id,nombre FROM stock2 UNION
-> SELECT id,nombre FROM stock3;
+----+----------------+
| id | nombre |
+----+----------------+
| 1 | tornillo M3x12 |
| 2 | tornillo M3x15 |
| 3 | tornillo M4x25 |
| 4 | tornillo M5x30 |
| 10 | tuerca M4 |
| 11 | tuerca M3 |
| 12 | tuerca M5 |
| 20 | varilla 10 |
| 21 | varilla 12 |
+----+----------------+
9 rows in set (0.00 sec)
mysql> SELECT id,nombre FROM stock1 UNION ALL
-> SELECT id,nombre FROM stock2 UNION ALL
-> SELECT id,nombre FROM stock3;
+----+----------------+
| id | nombre |
+----+----------------+
| 1 | tornillo M3x12 |
| 2 | tornillo M3x15 |
| 3 | tornillo M4x25 |
| 4 | tornillo M5x30 |
| 10 | tuerca M4 |
| 11 | tuerca M3 |
| 12 | tuerca M5 |
| 1 | tornillo M3x12 |
| 11 | tuerca M3 |
| 20 | varilla 10 |
| 21 | varilla 12 |
+----+----------------+
11 rows in set (0.00 sec)
mysql>
Sobre el resultado final no se pueden aplicar otras cláusulas como GROUP BY.
Composiciones externas
Composiciones externas
Al contrario que con las composiciones internas, las externas no proceden de un producto cartesiano. Por lo tanto, en estas pueden aparecer tuplas que no aparecen en el producto cartesiano.
Para hacer una composición externa se toman las tuplas de una de las tablas una a una y se combinan con las tuplas de la otra.
Como norma general se usa un índice para localizar las tuplas de la segunda tabla que cumplen la condición, y para cada tupla encontrada se añade una fila a la tabla de salida.
Si no existe ninguna tupla en la segunda tabla que cumpla las condiciones, se combina la tupla de la primera con una nula de la segunda.
En nuestro ejemplo se tomaría la primera tupla de personas2, con un valor de id igual a 1, y se busca en la tabla telefonos2 las tuplas con un valor de id igual a 1. Lo mismo para la segunda tupla, con id igual a 2.
En la tercera el id es 3, y no existe ninguna tupla en telefonos2 con un valor de id igual a 3, por lo tanto se combina la tupla de personas2 con una tupla de telefonos2 con todos los atributos igual a NULL.
Por ejemplo:
mysql> SELECT * FROM personas2 LEFT JOIN telefonos2 USING(id); +----+-----------+------------+-----------+------+ | id | nombre | fecha | numero | id | +----+-----------+------------+-----------+------+ | 1 | Fulanito | 1956-12-14 | 123456789 | 1 | | 1 | Fulanito | 1956-12-14 | 145654854 | 1 | | 1 | Fulanito | 1956-12-14 | 152452545 | 1 | | 2 | Menganito | 1975-10-15 | 254254254 | 2 | | 3 | Tulanita | 1985-03-17 | NULL | NULL | (1) | 4 | Fusganita | 1976-08-25 | 456545654 | 4 | | 4 | Fusganita | 1976-08-25 | 441415414 | 4 | +----+-----------+------------+-----------+------+ 7 rows in set (0.05 sec) mysql>
La quinta fila (1), tiene valores NULL para numero e id de telefonos2, ya que no existen tuplas en esa tabla con un valor de id igual a 3.
Las sintaxis para composiciones externas son:
referencia_tabla LEFT [OUTER] JOIN referencia_tabla [join_condition] referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla referencia_tabla RIGHT [OUTER] JOIN referencia_tabla [condición] referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla
La condición puede ser:
ON expresión_condicional | USING (lista_columnas)
La palabra OUTER es opcional.
Existen dos grupos de composiciones externas: izquierda y derecha, dependiendo de cual de las tablas se lea en primer lugar.
Composición externa izquierda
En estas composiciones se recorre la tabla de la izquierda y se buscan tuplas en la de la derecha. Se crean usando la palabra LEFT (izquierda, en inglés).
Las sintaxis para la composición externa izquierda es:
referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]
Veamos un ejemplo. Para empezar, crearemos un par de tablas:
mysql> CREATE TABLE tabla1 (
-> id INT NOT NULL,
-> nombre CHAR(10),
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.42 sec)
mysql> CREATE TABLE tabla2 (
-> id INT NOT NULL,
-> numero INT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.11 sec)
mysql>
E insertaremos algunos datos:
mysql> INSERT INTO tabla1 VALUES
-> (5, "Juan"),
-> (6, "Pedro"),
-> (7, "José"),
-> (8, "Fernando");
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO tabla2 VALUES
-> (3, 30),
-> (4, 40),
-> (5, 50),
-> (6, 60);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
La composición izquierda sería:
mysql> SELECT * FROM tabla1 LEFT JOIN tabla2 USING(id); +----+----------+------+--------+ | id | nombre | id | numero | +----+----------+------+--------+ | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | | 7 | José | NULL | NULL | | 8 | Fernando | NULL | NULL | +----+----------+------+--------+ 4 rows in set (0.00 sec) mysql>
Se puede ver que aparecen dos filas con valores NULL, para los id 7 y 8.
En contraposición, una composición interna dará esta salida:
mysql> SELECT * FROM tabla1 JOIN tabla2 USING(id); +----+--------+----+--------+ | id | nombre | id | numero | +----+--------+----+--------+ | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | +----+--------+----+--------+ 2 rows in set (0.06 sec) mysql>
Composición externa derecha
En este caso se recorre la tabla de la derecha y se buscan tuplas que cumplan la condición en la tabla izquierda.
La sintaxis es equivalente:
referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]
Usando las mismas tablas que en el ejemplo anterior:
mysql> SELECT * FROM tabla1 RIGHT JOIN tabla2 USING(id); +------+--------+----+--------+ | id | nombre | id | numero | +------+--------+----+--------+ | NULL | NULL | 3 | 30 | | NULL | NULL | 4 | 40 | | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | +------+--------+----+--------+ 4 rows in set (0.00 sec) mysql>
Es lo mismo usar una composición derecha de las tablas tabla1 y tabla2 que una composición izquierda de las tablas tabla2 y tabla1. Es decir, la consulta anterior es equivalente a esta otra:
mysql> SELECT * FROM tabla2 LEFT JOIN tabla1 USING(id);
Composiciones naturales externas
Por supuesto, también podemos hacer composiciones externas naturales:
referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla
El problema es que si existen tuplas añadidas con respecto a la composición interna, no se eliminará ninguna columna. Los mismos ejemplos anteriores, como composiciones naturales externas serían:
mysql> SELECT * FROM tabla1 NATURAL LEFT JOIN tabla2; +----+----------+------+--------+ | id | nombre | id | numero | +----+----------+------+--------+ | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | | 7 | José | NULL | NULL | | 8 | Fernando | NULL | NULL | +----+----------+------+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM tabla1 NATURAL RIGHT JOIN tabla2; +------+--------+----+--------+ | id | nombre | id | numero | +------+--------+----+--------+ | NULL | NULL | 3 | 30 | | NULL | NULL | 4 | 40 | | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | +------+--------+----+--------+ 4 rows in set (0.00 sec) mysql>
Composiciones internas
Para hacer una composición interna se parte de un producto cartesiano y se eliminan aquellas tuplas que no cumplen la condición de la composición.
En el ejemplo anterior tenemos 24 tuplas procedentes del producto cartesiano de las tablas personas2 y teléfonos2. Si la condición para la composición es que personas2.id=telefonos2.id, tendremos que eliminar todas las tuplas en que la condición no se cumpla.
Estas composiciones se denominan internas porque en la salida no aparece ninguna tupla que no esté presente en el producto cartesiano, es decir, la composición se hace en el interior del producto cartesiano de las tablas.
Para consultar la sintaxis de las composiciones ver JOIN.
Las composiciones internas usan estas sintaxis:
referencia_tabla, referencia_tabla referencia_tabla [INNER | CROSS] JOIN referencia_tabla [condición]
La condición puede ser:
ON expresión_condicional | USING (lista_columnas)
La coma y JOIN son equivalentes, y las palabras INNER y CROSS son opcionales.
La condición en la cláusula ON puede ser cualquier expresión válida para una cláusula WHERE, de hecho, en la mayoría de los casos, son equivalentes.
La cláusula USING nos permite usar una lista de atributos que deben ser iguales en las dos tablas a componer.
Siguiendo con el mismo ejemplo, la condición más lógica para la composición interna entre personas2 y teléfonos2 es la igualdad entre el identificador de persona en la primera tabla y el atributo persona en la segunda:
mysql> SELECT * FROM personas2, telefonos2
-> WHERE personas2.id=telefonos2.id;
+----+-----------+------------+-----------+----+
| id | nombre | fecha | numero | id |
+----+-----------+------------+-----------+----+
| 1 | Fulanito | 1956-12-14 | 123456789 | 1 |
| 1 | Fulanito | 1956-12-14 | 145654854 | 1 |
| 1 | Fulanito | 1956-12-14 | 152452545 | 1 |
| 2 | Menganito | 1975-10-15 | 254254254 | 2 |
| 4 | Fusganita | 1976-08-25 | 456545654 | 4 |
| 4 | Fusganita | 1976-08-25 | 441415414 | 4 |
+----+-----------+------------+-----------+----+
6 rows in set (0.73 sec)
mysql>
Esta consulta es equivalente a estas otras:
mysql> SELECT * FROM personas2 JOIN telefonos2
-> ON (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 JOIN telefonos2
-> WHERE (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 INNER JOIN telefonos2
-> ON (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 CROSS JOIN telefonos2
-> ON (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 JOIN telefonos2 USING(id);
En cualquier caso, la salida sólo contiene las tuplas que emparejan a personas con sus números de teléfono. Las tuplas correspondientes a personas que no tienen ningún número no aparecen, como por ejemplo las correspondientes a «Tulanita». Para las personas con varios números, se repiten los datos de la persona para cada número, por ejemplo con «Fulanito» o «Fusganita».
Composición interna natural
Consiste en una proyección sobre un producto cartesiano restringido. Es decir, sólo elegimos determinadas columnas de ambas tablas, en lugar de seleccionar todas.
Podemos hacer esto a partir de una composición general, eligiendo todas las columnas menos las repetidas:
mysql> SELECT personas2.id,nombre,fecha,numero
-> FROM personas2, telefonos2
-> WHERE personas2.id=telefonos2.id;
+----+-----------+------------+-----------+
| id | nombre | fecha | numero |
+----+-----------+------------+-----------+
| 1 | Fulanito | 1956-12-14 | 123456789 |
| 1 | Fulanito | 1956-12-14 | 145654854 |
| 1 | Fulanito | 1956-12-14 | 152452545 |
| 2 | Menganito | 1975-10-15 | 254254254 |
| 4 | Fusganita | 1976-08-25 | 456545654 |
| 4 | Fusganita | 1976-08-25 | 441415414 |
+----+-----------+------------+-----------+
6 rows in set (0.00 sec)
mysql>
Como la columna id existe en ambas tablas estamos obligados a usar el nombre completo para esta columna. En este caso hemos optado por personas2.id, pero hubiese sido igual usar telefonos2.id.
También podemos definir alias para las tablas, y conseguir una consulta más compacta:
mysql> SELECT t1.id,nombre,fecha,numero
-> FROM personas2 AS t1, telefonos2 AS t2
-> WHERE t1.id=t2.id;
Por supuesto, podemos usar JOIN y ON en lugar de la coma y WHERE:
mysql> SELECT t1.id,nombre,fecha,numero
-> FROM personas2 AS t1 JOIN telefonos2 AS t2
-> ON t1.id=t2.id;
Pero tenemos una sintaxis alternativa mucho mejor para hacer composiciones internas naturales:
referencia_tabla NATURAL JOIN referencia_tabla
Por ejemplo:
mysql> SELECT * FROM personas2 NATURAL JOIN telefonos2; +----+-----------+------------+-----------+ | id | nombre | fecha | numero | +----+-----------+------------+-----------+ | 1 | Fulanito | 1956-12-14 | 123456789 | | 1 | Fulanito | 1956-12-14 | 145654854 | | 1 | Fulanito | 1956-12-14 | 152452545 | | 2 | Menganito | 1975-10-15 | 254254254 | | 4 | Fusganita | 1976-08-25 | 456545654 | | 4 | Fusganita | 1976-08-25 | 441415414 | +----+-----------+------------+-----------+ 6 rows in set (0.02 sec) mysql>
Consultas Multitabla
Hasta ahora todas las consultas que hemos usado se refieren sólo a una tabla, pero también es posible hacer consultas usando varias tablas en la misma sentencia SELECT.
Esto nos permite realizar otras dos operaciones de álgebra relacional que aún no hemos visto: el producto cartesiano y la composición.
Leitmotiv
Dalek-free
Camisetas cargadas de adrenalina. Llénalas de aventura
CursoProgramador 