Manejando MySQL desde la linea de comandos

viernes 18 marzo 2011 | In English

Si nuestro servicio de hospedaje nos ofrece un shell SSH y tiene MySQL instalado, o si tenemos MySQL instalado en nuestra computadora, podemos administrar una base de datos desde la interfase de linea de comandos. Si desarrollamos programas que requieres del uso de una base de datos MySQL, es muy probable que ya estemos familiarizado con las peticiones de SQL. Utilizando el comando mysql podemos enviar estas peticiones a la base de datos.

Temas
Conectándose a la base de datos
Enviando peticiones al shell de MySQL
Usando el editor
Procesando un archivo por lotes Peticiones MySQL para manipular tablas

Peticiones MySQL para manipular registros en una tabla

Peticiones MySQL preparadas
Notas al pie
Recursos adicionales

Podemos enviar peticiones directamente al shell de MySQL, editar estas peticiones en un editor de texto separado que definimos con la variable de entorno EDITOR, o podemos utilizar un archivo con peticiones de MySQL (un script o lote) a ser ejecutadas por el interprete de MySQL.

Conectándose a la base de datos

Esta guía asume que ya tienes creada una base de datos, así como un usuario con los privilegios necesarios para hacer las operaciones que se requieren en la base de datos.

Los cuatro parámetros que necesitamos para establecer una conexión a la base de datos es el host donde reside la base de datos, el nombre de usuario, la contraseña y el nombre de la base de datos que vamos a manipular.

mysql -h [host] -D [base de datos] -u [usuario] -p

Esto te pedirá la contraseña, para que no sea guardada en el historial, por ejemplo:

mysql -h servidor.jveweb.net -D nombre_base_de_datos -u juan -p

Puedes especificar la contraseña en el comando agregando la contraseña junto a -p, no dejes un espacio entre -p y la contraseña para conectarte de esta manera, aunque no usar la contraseña en el comando es recomendable, por ejemplo:

mysql -h servidor.jveweb.net -D nombre_base_de_datos -u juan -punacontraseña

El parámetro -D para especificar la base de datos a usar desde que nos conectamos también es opcional, si no lo usas puedes ver una lista de las bases de datos disponibles usando show databases; y seleccionar la base de datos a usar con use [nombre base de datos]; en la linea de comandos de mysql, por ejemplo: use usuarios;

Si funcionó, obtendremos un resultado similar a este:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6324623
Server version: 5.1.39-log MySQL Server

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input 
statement.

mysql>

Para terminar la sesión escribe quit. Si te estás conectando a una base de datos ubicada en un host externo, es recomendable el uso de SSL al conectarse a la base de datos, para hacer esto usa el parámetro --ssl

Enviando peticiones al shell de MySQL

Una vez que estamos en el shell de MySQL, podemos enviar peticiones de MySQL. Para ejecutarlas tenemos que terminarlas con un punto y coma (;), o con \g, por ejemplo:

show tables;

La petición no es ejecutada hasta que el punto y coma es encontrado, esto nos permite escribir peticiones de MySQL en lineas múltiples, por ejemplo:

show
tables
;

Si queremos presentar los resultados verticalmente, necesitamos terminar las peticiones con \G en vez de un punto y coma o \g

Usando el editor

En sistemas basados en Unix como Linux, el comando edit desde dentro del shell de mysql lanza el editor que está definido en la variable de entorno EDITOR. Cuando usamos el comando edit, si habíamos hecho una petición previamente, el editor será abierto con esta petición, esto es muy útil para hacer correcciones a la última petición, de otra manera solo obtendremos un editor vacío para escribir lo que necesitemos. Una vez que terminemos de editar la petición, guardamos, salimos del editor, y entonces usamos un punto y coma o \g para ejecutar la petición(es) que acabamos de escribir.

Para configurar la variable de entorno EDITOR, usamos export, en este ejemplo yo configuro vim ya que es mi editor preferido, pero puedes utilizar uno más fácil como nano. El editor por default es vi:

export EDITOR=vim

Para revisar el valor de la variable de entorno EDITOR, podemos utilizar:

echo $EDITOR

Procesando un archivo por lotes

Podemos ejecutar un archivo por lotes de peticiones MySQL utilizando:

mysql -u usuario -pcontraseña -h host -D nombre_base_de_datos < archivo_lotes.sql

O, si estamos dentro de el shell de mysql, podemos usar:

source archivo_lotes.sql

Peticiones MySQL para manipular tablas

Quien tenga el trabajo de crear scripts y programas que interactúan con MySQL seguramente está familiarizado con estas peticiones, pero ya que utilizo mi propio sitio web como referencia pondré aquí algunas peticiones comunes.

Listar tablas existentes en la base de datos

show tables;

Mostrar información de las tablas en la base de datos

show tables solo nos mostrará los nombres de las tablas en la base de datos, para ver toda la información sobre las tablas, usa:

show table status;

La información presentada sobre la tabla es:

  • Name - El nombre de la tabla
  • Engine - Mecanismo de la tabla (MyISAM, InnoDB, Memory, CVS, etc.)
  • Version - Número de versión del archivo .frm de la tabla
  • Row_format - El formato de almacenamiento de las filas (Dinámico, Redundante, etc.)
  • Rows - Número de filas en la tabla
  • Avg_row_length - Longitud promedio de las filas
  • Data_length - Longitud del archivo de datos
  • Max_data_length - La máxima longitud del archivo de datos
  • Index_length - La longitud del archivo índice
  • Data_free - Número de bytes asignados pero no usados
  • Auto_increment - El próximo valor de auto-incremento
  • Create_time - Cuando fue creada la tabla
  • Update_time - Cuando fue actualizado el archivo de datos por última vez
  • Check_time - Cuando fue revisada la tabla por última vez
  • Collation - El set de caracteres y la colación de la tabla
  • Checksum - El checksum vivo
  • Create_options - Opciones extras utilizadas cuando fue creada la tabla
  • Comment - El comentario de la tabla

Podemos especificar de que tabla queremos ver la información utilizando:

show table status like 'nombre_de_la_tabla';

Y podemos buscar en otro campo por cierto valor, por ejemplo, para mostrar todas las tablas que usan el mecanismo de almacenamiento MyISAM, podemos utilizar:

show table status where `Engine` like 'MyISAM';

Crear una nueva tabla

Este es un ejemplo del comando para crear una tabla, agregué muchos campos diferentes para la referencia de como declararlos.

create table `nombre_base_de_datos`.`tabla_de_prueba` (
`campo_id` int( 11 ) unsigned not null auto_increment comment 'clave primaria',
`campo_indice1` int( 11 ) unsigned not null comment 'un índice',
`campo_indice2` int( 11 ) unsigned not null comment 'un índice',
`campo_indice3` int( 11 ) unsigned not null comment 'un índice',
`campo_unico1` int( 11 ) unsigned not null comment 'un campo único',
`campo_unico2` int( 11 ) unsigned not null comment 'un campo único',
`campo_unico3` int( 11 ) unsigned not null comment 'un campo único',
`campo_varchar` varchar( 100 ) not null comment 'un campo varchar',
`campo_date` date not null comment 'un campo date',
`campo_datetime` datetime not null comment 'un campo datetime',
`campo_float` float not null comment 'un campo float',
`campo_longtext` longtext not null comment 'un campo longtext',
`campo_bool` bool not null comment 'un campo bool',
`campo_char` char( 1 ) not null comment 'un campo char',
`campo_tinyint` tinyint not null comment 'un campo tinyint',
primary key ( `campo_id` ) ,
index ( `campo_indice1` , `campo_indice2`, `campo_indice3` ) ,
unique ( `campo_unico1` , `campo_unico2`, `campo_unico3`)
) engine = myisam character set utf8 collate utf8_general_ci comment =
'comentarios de tabla';

Listar los campos en una tabla

show columns from `tabla_de_prueba`;

Cambiar el nombre de un campo en una tabla

alter table `tabla_de_prueba` change `campo_indice_1` `nuevo_nombre_campo` int(11) unsigned not null;

Agregar un campo a una tabla y hacerlo un índice

alter table `tabla_de_prueba` add `nuevo_campo_indice` int(11) unsigned not null, add index(`nuevo_campo_indice`);

Quitar un índice de una tabla

alter table `tabla_de_prueba` drop index `nuevo_campo_indice`;

Quitar un campo de una tabla

alter table `tabla_de_prueba` drop `nuevo_campo_indice`;

Agregar campos después de un campo especificado

alter table `tabla_de_prueba` add `a_borrar` varchar(12) not null after `campo_date`;

Agregar campos al comienzo de la tabla

alter table `tabla_de_prueba` add `a_borrar_2` varchar(12) not null first;

Agregar múltiples campos a la tabla

alter table `tabla_de_prueba` add `a_borrar_3` varchar(12) not null after `a_borrar`, add `a_borrar_4` varchar(12) not null after `a_borrar_3`;

Borrar campos en una tabla

alter table `tabla_de_prueba` drop `a_borrar`, drop `a_borrar_2`, drop `a_borrar_3`, drop `a_borrar_4`;

Renombrar una tabla

rename table `nombre_base_de_datos`.`nombre_original` to `nombre_base_de_datos`.`nuevo_nombre`;

Cambiar el comentario de una tabla

alter table `tabla_de_prueba` comment='Los comentarios';

Cambiar el valor de auto incremento de una tabla

alter table `tabla_de_prueba` auto_increment=3;

Reparar una tabla

repair table `tabla_de_prueba`;

Optimizar una tabla

optimize table `tabla_de_prueba`;

Borrar todos los registros en una tabla

truncate table `tabla_de_prueba`;

Borrar una tabla

drop table `tabla_de_prueba`;

Peticiones MySQL para manipular registros en una tabla

Voy a utilizar las siguientes dos tablas ficticias para los ejemplos que voy a estar utilizando referentes a la manipulación de registros.

Tabla de estados (estados)
estado_id estado_nombre
1 Jalisco
2 Guanajuato
3 Hidalgo
Tabla de ciudades (ciudades)
ciudad_id ciudad_nombre ciudad_poblacion estado_id
1 Guadalajara 1494134 1
2 Tequila 33155 1
3 Zapopan 1243538 1
4 Tonalá 408729 1
5 Tlaquepaque 563006 1
6 Guanajuato 171709 2
7 Celaya 468064 2
8 León 1436733 2
9 Pachuca 275578 3
10 Tizayuca 100562 3

Mostrar los registros de una tabla

select [campos] from `tabla_nombre` [where condiciones] [order by orden1 asc/desc,orden2 asc/desc] [limit inicio,limite];

select [x.campo,y.campo] from `tabla1` x, `tabla2` y where y.`id`=x.`indice` [condiciones extras] [order by x.campo,y.campo] [limit inicio,limite];

El número de combinaciones que podemos hacer con el comando select es enorme, así que trataré de cubrir algunos usos comunes en los siguientes ejemplos. Los campos que usamos siguiendo el select son los campos que serán mostrados, y el orden en que los especificamos es el orden en el que serán mostrados. Después de esto especificamos la tabla o tablas donde estamos buscando.

Definiendo el where podemos especificar múltiples condiciones separadas por espacios. Si estamos utilizando más de una tabla en la búsqueda, necesitamos hacer una comparación de igual entre los campos que ligan ambas tablas. Los últimos ejemplos son sobre hacer estas peticiones en más de una tabla.

Las condiciones pueden ser dadas con operadores de comparación, como =, <, >, <=, >=, <> or !=. La expresión LIKE nos permite hacer comparaciones simple de patrones, podemos utilizar % como carácter comodín. between ... and ... nos permite especificar un rango de valores. Y podemos especificar más de una condición utilizando los operadores lógicos AND o &&, OR o ||, NOT o !, o XOR.

Podemos ordenar el resultado utilizando order by y especificando el campo que queremos utilizar para ordenar, y si queremos que el orden sea ascendente (asc) o descendente (desc), podemos usar más de un campo para hacer el ordenamiento, en cuyo caso el primer campo será usado primariamente para el orden, y el segundo campo será usado cuando el primer campo tenga más de una instancia.

Y finalmente, el valor limit define desde que registro comenzaremos a mostrar, y cuantos registros serán mostrados.

Espero que en los próximos ejemplos esto se volverá mucho más claro, toma las dos tablas como referencia para ver los resultados de cada comando.

select * from `estados`;

+-----------+---------------+
| estado_id | estado_nombre |
+-----------+---------------+
|         1 | Jalisco       |
|         2 | Guanajuato    |
|         3 | Hidalgo       |
+-----------+---------------+
3 rows in set (5.14 sec)

select * from `ciudades` where `ciudad_id` = '3';

+-----------+---------------+------------------+-----------+
| ciudad_id | ciudad_nombre | ciudad_poblacion | estado_id |
+-----------+---------------+------------------+-----------+
|         3 | Zapopan       |          1243538 |         1 |
+-----------+---------------+------------------+-----------+
1 row in set (0.90 sec)

select `ciudad_nombre`,`ciudad_poblacion` from `ciudades` order by `ciudad_poblacion` asc;

+---------------+------------------+
| ciudad_nombre | ciudad_poblacion |
+---------------+------------------+
| Tequila       |            33155 |
| Tizayuca      |           100562 |
| Guanajuato    |           171709 |
| Pachuca       |           275578 |
| Tonalá        |           408729 |
| Celaya        |           468064 |
| Tlaquepaque   |           563006 |
| Zapopan       |          1243538 |
| León          |          1436733 |
| Guadalajara   |          1494134 |
+---------------+------------------+
10 rows in set (0.04 sec)

select `ciudad_nombre` from `ciudades` where `estado_id` = '2' order by `ciudad_nombre` desc;

+---------------+
| ciudad_nombre |
+---------------+
| León          |
| Guanajuato    |
| Celaya        |
+---------------+
3 rows in set (0.85 sec)

select * from `ciudades` limit 2,3;

+-----------+---------------+------------------+-----------+
| ciudad_id | ciudad_nombre | ciudad_poblacion | estado_id |
+-----------+---------------+------------------+-----------+
|         3 | Zapopan       |          1243538 |         1 |
|         4 | Tonalá        |           408729 |         1 |
|         5 | Tlaquepaque   |           563006 |         1 |
+-----------+---------------+------------------+-----------+
3 rows in set (0.06 sec)

select `ciudad_nombre` from `ciudades` where `ciudad_nombre` like 'G%';

+---------------+
| ciudad_nombre |
+---------------+
| Guadalajara   |
| Guanajuato    |
+---------------+
2 rows in set (0.04 sec)

select * from `ciudades` where `ciudad_poblacion` between '500000' and '1000000';

select * from `ciudades` where `ciudad_poblacion`>='500000' and `ciudad_poblacion`<='1000000';

+-----------+---------------+------------------+-----------+
| ciudad_id | ciudad_nombre | ciudad_poblacion | estado_id |
+-----------+---------------+------------------+-----------+
|         5 | Tlaquepaque   |           563006 |         1 |
+-----------+-------------+--------------------+-----------+
1 row in set (0.04 sec)

Nota: Mientras que ambas peticiones regresarían los mismos registros, utilizar between es más rápido que utilizar dos comparaciones, así que si estás lidiando con un rango de valores, siempre utiliza between.

select c.`ciudad_nombre`,s.`estado_nombre` from `estados` s, `ciudades` c where c.`estado_id`=s.`estado_id` order by c.`ciudad_nombre`;

+---------------+---------------+
| ciudad_nombre | estado_nombre |
+---------------+---------------+
| Celaya        | Guanajuato    |
| Guadalajara   | Jalisco       |
| Guanajuato    | Guanajuato    |
| León          | Guanajuato    |
| Pachuca       | Hidalgo       |
| Tequila       | Jalisco       |
| Tizayuca      | Hidalgo       |
| Tlaquepaque   | Jalisco       |
| Tonalá        | Jalisco       |
| Zapopan       | Jalisco       |
+---------------+---------------+
10 rows in set (0.06 sec)

select c.`ciudad_nombre`,s.`estado_nombre` from `estados` s, `ciudades` c where c.`estado_id`=s.`estado_id` and c.`ciudad_nombre` like 'G%';

+---------------+---------------+
| ciudad_nombre | estado_nombre |
+---------------+---------------+
| Guadalajara   | Jalisco       |
| Guanajuato    | Guanajuato    |
+---------------+---------------+
2 rows in set (0.05 sec)

select c.`ciudad_nombre`,s.`estado_nombre`,c.`ciudad_poblacion` from `estados` s,`ciudades` c where s.`estado_id`=c.`estado_id`;

+---------------+---------------+------------------+
| ciudad_nombre | estado_nombre | ciudad_poblacion |
+---------------+---------------+------------------+
| Guadalajara   | Jalisco       |          1494134 |
| Tequila       | Jalisco       |            33155 |
| Zapopan       | Jalisco       |          1243538 |
| Tonalá        | Jalisco       |           408729 |
| Tlaquepaque   | Jalisco       |           563006 |
| Guanajuato    | Guanajuato    |           171709 |
| Celaya        | Guanajuato    |           468064 |
| León          | Guanajuato    |          1436733 |
| Pachuca       | Hidalgo       |           275578 |
| Tizayuca      | Hidalgo       |           100562 |
+---------------+---------------+------------------+
10 rows in set (0.05 sec)

select c.`ciudad_nombre`,s.`estado_nombre`,c.`ciudad_poblacion` from `estados` s, `ciudades` c where s.`estado_id`=c.`estado_id` order by s.`estado_nombre` desc,c.`ciudad_poblacion` asc;

+---------------+---------------+------------------+
| ciudad_nombre | estado_nombre | ciudad_poblacion |
+---------------+---------------+------------------+
| Tequila       | Jalisco       |            33155 |
| Tonalá        | Jalisco       |           408729 |
| Tlaquepaque   | Jalisco       |           563006 |
| Zapopan       | Jalisco       |          1243538 |
| Guadalajara   | Jalisco       |          1494134 |
| Tizayuca      | Hidalgo       |           100562 |
| Pachuca       | Hidalgo       |           275578 |
| Guanajuato    | Guanajuato    |           171709 |
| Celaya        | Guanajuato    |           468064 |
| León          | Guanajuato    |          1436733 |
+---------------+---------------+------------------+
10 rows in set (0.15 sec)

Contar los registros de una tabla

select count(*) from `tabla_nombre` [where condiciones];

Las condiciones son opcionales, y pueden tener el mismo formato de las condiciones que utilizamos en peticiones select, esto simplemente nos devuelve el número de registros, por ejemplo:

select count(*) from `ciudades`;

+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.06 sec)

Sumar los registros de una tabla

select sum(`ciudad_poblacion`) from `ciudades`;

+-------------------------+
| sum(`ciudad_poblacion`) |
+-------------------------+
|                 6453216 |
+-------------------------+
1 row in set (0.05 sec)

Insertar un registro

insert into `estados` (`estado_nombre`) values ( "Oaxaca");

insert into `ciudades` (`ciudad_nombre`,`ciudad_poblacion`,`estado_id`) values ('Oaxaca','258008',LAST_INSERT_ID());

En este caso agrego un nuevo estado, y una nueva ciudad, nota que en el campo estado_id estoy utilizando como valor la función LAST_INSERT_ID(), que me da el valor de la última ID que fue insertada. Si quisiera insertar más de un registro con esta ID, podemos usar la misma petición de insertar para la inserción de más de un campo. Voy a tomar la petición pasada e insertar en vez de eso tres registros:

insert into `estados` (`estado_nombre`) values( "Oaxaca");

insert into `ciudades` (`ciudad_nombre`,`ciudad_poblacion`,`estado_id`) values
('Oaxaca','258008',LAST_INSERT_ID()),
(`Salina Cruz`,`76219`,LAST_INSERT_ID()),
(`Zaragoza`,`85869`,LAST_INSERT_ID());

Otra función útil que utilizo para llenar un valor es la función NOW() en campos de tipo datetime, utilizo esto mucho para manejar el tiempo de creación o modificación de registros. Por ejemplo, asumiendo que tuviéramos un campo llamado tiempo_de_creacion, podríamos usar:

insert into `estados` (`estado_nombre`,`tiempo_de_creacion`) values ('Sonora', NOW());

Actualizar un registro

update `ciudades` set `ciudad_nombre`='Algún nombre',`ciudad_poblacion`='1000000' where `ciudad_id`='5';

Cuando estamos actualizando un registro, necesitamos especificar que registro es el que queremos actualizar, usualmente la llave primaria es utilizada para este propósito debido a que es un valor único. Por supuesto, podemos modificar muchos registros en la misma petición si más de un registro cumple con la condición. Por ejemplo digamos que todos los registros creados el 12 de Febrero de 2010 se volverán "activos" cambiando el valor de un campo llamado activo de '0' a '1', aquí está lo que haríamos:

update `cuentas` set `activo`='1' where `fecha_de_creacion` between '2010-02-12 00:00:00' and '2010-02-12 23:59:59';

Borrar un registro

delete from `estados` where `estado_id`='8';

La petición delete es más sencilla que una petición update pero algo similar, todos y cada uno de los registros que cumplan la condición(es) serán borrados. Se muy cuidadoso con esta petición, si estás apuntando a registros específicos, siempre usa la llave primaria para no golpear otro registro por accidente.

Peticiones MySQL preparadas

La razón por la que aprendí sobre peticiones preparadas fue debido a la seguridad que ofrecen cuando estoy utilizando PHP para realizar las peticiones a la base de datos en un sitio web. Sin embargo, el uso de peticiones preparadas desde la linea de comandos nos ofrece la habilidad de definir una petición una vez y entonces llamarla cuantas veces queramos, cambiando solamente el parámetro que usamos. Por ejemplo, para mostrar un registro de la tabla de ciudades, mostrando el nombre del estado en vez de estado_id, siempre tendría el mismo formato:

select c.`ciudad_nombre`,s.`estado_nombre` from `estados` s, `ciudades` c where c.`estado_id`=s.`estado_id` and c.`ciudad_id` = ?;

En el ejemplo, estamos utilizando el ? como un apartado para la ID de el registro que queremos mostrar de esta manera. Esto es útil también cuando estamos utilizando una petición como esta desde PHP y necesitamos usar un valor que recibimos de alguien más. Antes de la existencia de peticiones preparadas, necesitábamos filtrar muy cuidadosamente la entrada de un usuario para poder prevenir un ataque de inyección de sql que tiene el potencial de borrar nuestra base de datos o darle acceso a una persona no autorizada. Al separar la lógica de MySQL de los datos evitamos este problema, ya que MySQL no interpreta el parámetro, simplemente lo maneja como datos. Otra ventaja es que es más rápido utilizar peticiones preparadas de MySQL.

Hasta donde se, las peticiones preparadas solo funcionan con SELECT, INSERT, UPDATE, REPLACE, DELETE y CREATE TABLE. Veamos un ejemplo utilizando la petición previa. Primero que nada, creamos la petición preparada y la nombramos mostrar_ciudad:

prepare mostrar_ciudad from "select c.`ciudad_nombre`,s.`estado_nombre` from `estados` s, `ciudades` c where c.`estado_id`=s.`estado_id` and c.`ciudad_id` = ?";

Después preparamos el parámetro, llamado una_ciudad, en este caso:

set @una_ciudad = "2";

Y ejecutamos la petición preparada mostrar_ciudad usando el parámetro una_ciudad:

execute mostrar_ciudad using @una_ciudad;

+---------------+---------------+
| ciudad_nombre | estado_nombre |
+---------------+---------------+
| Tequila       | Jalisco       |
+---------------+---------------+
1 row in set (0.04 sec)

Notas al pie

Recuerda marcar como índice los campos que planeas usar frecuentemente para las búsquedas, esto hará más rápidas las peticiones a la base de datos.

Todos los ejemplos de uso, especialmente las peticiones preparadas al final, son en preparación para una serie de publicaciones sobre como utilizar MySQL desde PHP y python, ya que esos lenguajes son los que he estado utilizando más recientemente, y ash, pero esa es otra historia.

Recursos adicionales

Lista de funciones y operadores de MySQL (En Inglés)
http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html

El script usado para crear las tablas usadas como ejemplo.
Cambia 'nombre_db' por el nombre de tu base de datos.
Descargar Script

Categorías: Comandos, FOSS, Linux, MySQL