3.6. Ejemplos de consultas comunes

MySQL 5.0

3.6. Ejemplos de consultas comunes

Aquí tiene ejemplos de como resolver algunos problemas comunes mediante MySQL.

Algunos de los ejemplos emplean la tabla para contener el precio de cada artículo (número de item) para ciertos distribuidores (dealers). Suponiendo que cada distribuidor tiene un único precio fijo por cada artículo, entonces (, ) es una clave primaria para los registros.

Inicie la utilidad de línea de comandos mysql y seleccione una base de datos:

shell> mysql 

(En la mayoría de las instalaciones de MySQL, podrá emplear la base de datos ).

Puede crear e ingresar datos a la tabla del ejemplo utilizando estas sentencias:

mysql> CREATE TABLE shop (
    -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    -> dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    -> price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    -> PRIMARY KEY(article, dealer));
mysql> INSERT INTO shop VALUES
    -> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    -> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

Luego de ejecutar estas sentencias, la tabla debería tener el siguiente contenido:

mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.1. El valor máximo de una columna

¿Cuál es el número de ítem más alto?

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

3.6.2. El registro que tiene el valor máximo de determinada columna

Tarea: Encontrar el número, distribuidor y precio del artículo más costoso.

En MySQL 5.0 (y en SQL estándar), esto se hace fácilmente con una subconsulta:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

Otra solución es ordenar las columnas por precio, en forma descendente, y obtener solamente el primer registro utilizando la cláusula , específica de MySQL:

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1;

Nota: Si hubiera varios artículos que presenten el precio más alto, cada uno a 19.95, la solución sólo mostraría el primero de ellos.

3.6.3. Máximo de columna por grupo

Tarea: Encontrar el precio más alto por artículo.

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

3.6.4. Los registros de un grupo que tienen el máximo valor en alguna columna

Tarea: Para cada artículo, encontrar el o los distribuidores con el precio más alto.

En MySQL 5.0 (y en SQL estándar), este problema puede resolverse con una subconsulta como esta:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

3.6.5. Utilización de variables de usuario

Se pueden emplear variables de usuario de MySQL para retener resultados sin necesidad de almacenarlos en variables del lado del cliente. (Consulte Sección 9.3, “Variables de usuario”.)

Por ejemplo, para encontrar los artículos con el precio más alto y más bajo se puede hacer lo siguiente:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.6. Usar claves foráneas (foreign keys)

En MySQL, las tablas soportan restricciones de claves foráneas. Consulte Capítulo 15, El motor de almacenamiento . Consulte también Sección 1.7.5.5, “Claves foráneas (foreign keys)”.

No se requiere una restricción de clave foránea para simplemente unir dos tablas. Para otros tipos de tabla que no sean , es posible, al momento de definir una columna, utilizar una cláusula (), la cual no tiene efecto real y funciona solamente como un recordatorio o comentario de que la columna que se está definiendo está dirigida a hacer referencia a una columna en otra tabla. Al emplear esta sintaxis es muy importante comprender que:

  • MySQL no efectúa ningún tipo de o comprobación para asegurarse de que realmente existe en (o incluso que existe).

  • MySQL no realiza ningún tipo de acción sobre tal como borrar filas en respuesta a acciones ejecutadas sobre filas en la tabla que se está definiendo; en otras palabras, esta sintaxis no produce por sí misma un comportamiento u . (Inclusive cuando se puede escribir una cláusula u como parte de la cláusula , estas son también ignoradas).

  • Esta sintaxis crea una columna; no crea ninguna clase de índice o campo clave.

  • Esta sintaxis causará un error si se la emplea durante la definición de una tabla .

Una columna creada de esta forma se puede utilizar como columna de unión, como se muestra aquí:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

Cuando se usa de esta manera, la cláusula no es mostrada en la salida de o :

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

El uso de como comentario o "recordatorio" en la definición de una columna funciona en tablas y .

3.6.7. Buscar usando dos claves

Un empleando una única clave es bien optimizado, como es el manejo de

El único caso difícil es la búsqueda sobre dos diferentes claves combinadas con :

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

Esto se ha optimizado a partir de MySQL 5.0.0. Consulte Sección 7.2.6, “Index Merge Optimization”.

En MySQL 5.0 tambien se puede resolver eficientemente este problema utilizando una que combine la salida de dos sentencias separadas. Consulte Sección 13.2.7.2, “Sintaxis de .

Cada sentencia busca en solamente una clave y puede ser optimizada:

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

3.6.8. Calcular visitas diarias

El siguiente ejemplo muestra cómo se pueden utilizar las funciones de bits para calcular la cantidad de dias de un mes que un usuario ha visitado una página Web.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

La tabla del ejemplo contiene valores de año, mes y dia que representan las visitas de los usuarios a la página. Para determinar en cuántos días diferentes del mes se produjeron las visitas, se emplea esta consulta:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

La cual devuelve:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

La consulta calcula cuantos días diferentes aparecen en la tabla para cada combinación de año y mes, removiendo automáticamente las entradas duplicadas.

3.6.9. Utilización de AUTO_INCREMENT

El atributo puede utilizarse para generar un identificador único para cada nueva fila:

CREATE TABLE animals (
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (id)
             );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),
                                  ('lax'),('whale'),('ostrich');
SELECT * FROM animals;

Lo cual devuelve:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Para obtener el valor más recientemente generado se puede utilizar la funcion SQL o la función del API de C . Estas funciones son específicas de cada conexión, de modo que su valor de retorno no es afectado por las inserciones realizadas a través de otras conexiones.

Nota: Para una inserción de múltiples filas, / retornan el valor de la primera de las filas insertadas. Esto permite que las inserciones de múltiples filas sean reproducidas correctamente en otros servidores en una configuración de replicación.

Para tablas y se puede especificar sobre una columna secundaria en un índice de múltiples columnas. En este caso, el valor generado para la columna es calculado como . Esto es útil cuando se desea colocar datos en grupos ordenados.

CREATE TABLE animals (
             grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
             );
INSERT INTO animals (grp,name) VALUES('mammal','dog'),('mammal','cat'),
                  ('bird','penguin'),('fish','lax'),('mammal','whale'),
                  ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

Lo cual devuelve:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Nótese que en este caso (cuando la columna es parte de un índice de múltiples columnas), los valores son reutilizados si se elimina la fila con el valor más alto en cualquier grupo. Esto ocurre incluso para tablas , en las que los valores normalmente no son reutilizados

Si la columna es parte de varios índices, MySQL generará valores secuenciales empleando el índice que comienza con la columna , si hay uno. Por ejemplo, si la tabla contiene los índices e , MySQL ignoraría el índice al generar valores secuenciales. Como resultado, la tabla contendría una secuencia simple, sin considerar el valor .