¡Descarga Ejemplos de consultas comunes y más Guías, Proyectos, Investigaciones en PDF de Sistemas de Gestión de Bases de Datos solo en Docsity!
¿Porqué usar scripts? Algunas razones:
- Si se utiliza una consulta repetidamente (como ser, cada dia o cada semana), hacer un script evitará volver a tipearla cada vez
que se desea ejecutarla.
- Se pueden crear nuevas consultas a partir de otras existentes que se le parezcan, copiando y editando el archivo de script.
- El modo por lotes también puede ser útil cuando se está creando una consulta, en especial si tiene comandos de múltiples lineas
o múltiples sentencias. Si se comete un error, no se necesita retipear todo. Solamente editar el script, para corregir el error, y
volver a ejecutarlo mediante mysql.
- Si se ejecuta una consulta que produce una salida muy extensa, se puede ejecutar a traves de un paginador en lugar de verla de-
saparecer rápidamente por la parte superior de la pantalla:
shell> mysql < batch-file | more
- Se puede enviar la salida a un archivo, para posterior proceso:
shell> mysql < batch-file > mysql.out
- Se puede distribuir el script a otras personas, para que puedan tambien ejecutar los comandos.
- Algunas situaciones no permiten la interactividad, por ejemplo, cuando se ejecuta una consulta a través de una tarea de cron
(en Unix). En este caso, debe emplearse el modo por lotes.
El formato de salida es más breve cuando se usa modo por lotes que cuando se utiliza mysql interactivamente. Por ejemplo, la sa-
lida devuelta para SELECT DISTINCT species FROM pet se ve así cuando se ejecuta en modo interactivo:
| species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+
Mientras que, en modo por lotes, presenta este aspecto:
species bird cat dog hamster snake
Si desea obtener el formato por lotes para una salida producida interactivamente, utilice mysql -t. Para incluir en la salida los
comandos que se ejecutan, utilice mysql -vvv.
También pueden ejecutarse archivos de script desde el prompt mysql utilizando los comandos source o .
mysql> source filename; mysql> . filename
3.6. Ejemplos de consultas comunes
Aquí tiene ejemplos de como resolver algunos problemas comunes mediante MySQL.
Algunos de los ejemplos emplean la tabla shop para contener el precio de cada artículo (número de item) para ciertos distribuido-
res (dealers). Suponiendo que cada distribuidor tiene un único precio fijo por cada artículo, entonces (article, dealer) es una
clave primaria para los registros.
Inicie la utilidad de línea de comandos mysql y seleccione una base de datos:
shell> mysql base-de-datos
(En la mayoría de las instalaciones de MySQL, podrá emplear la base de datos test).
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áu-
sula LIMIT, 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 LIMIT 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 colum-
na
(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 REFERENCES no es mostrada en la salida de SHOW CREATE TABLE o DESCRIBE:
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=latin
El uso de REFERENCES como comentario o "recordatorio" en la definición de una columna funciona en tablas MyISAM y Ber-
keleyDB.
3.6.7. Buscar usando dos claves
Un OR empleando una única clave es bien optimizado, como es el manejo de AND
El único caso difícil es la búsqueda sobre dos diferentes claves combinadas con OR:
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 UNION que combine la salida de dos sen-
tencias SELECT separadas. Consulte Sección 13.2.7.2, “Sintaxis de UNION”.
Cada sentencia SELECT 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 usua-
rio 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 t 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 AUTO_INCREMENT 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 AUTO_INCREMENT más recientemente generado se puede utilizar la funcion SQL LAST_INSERT_ID() o
la función del API de C mysql_insert_id(). Estas funciones son específicas de cada conexión, de modo que su valor de re-
torno no es afectado por las inserciones realizadas a través de otras conexiones.
Nota: Para una inserción de múltiples filas, LAST_INSERT_ID()/mysql_insert_id() retornan el valor AU-
TO_INCREMENT de la primera de las filas insertadas. Esto permite que las inserciones de múltiples filas sean reproducidas co-
rrectamente en otros servidores en una configuración de replicación.
Para tablas MyISAM y BDB se puede especificar AUTO_INCREMENT sobre una columna secundaria en un índice de múltiples co-
lumnas. En este caso, el valor generado para la columna AUTO_INCREMENT es calculado como
MAX(auto_increment_column)+1 WHERE prefix=given-prefix. Esto es útil cuando se desea colocar datos en gru-
pos 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 | +--------+----+---------+
