Base de Datos MySQL: Crear Tabla, Insertar y Recuperar Datos
Ahora que conocemos como escribir y ejecutar sentencias, es tiempo de acceder a una base de datos, crear una tabla, insertar y recuperar datos.
Usar una base de datos
Ahora que conocemos como escribir y ejecutar sentencias, es tiempo de acceder a una base de datos. Supongamos que tenemos diversas mascotas en casa (nuestro peque�o zool�gico) y deseamos tener registros de los datos acerca de ellas. Podemos hacer esto al crear tablas que guarden esta informaci�n, para que posteriormente la consulta de estos datos sea bastante f�cil y de manera muy pr�ctica. Esta secci�n muestra como crear una base de datos, crear una tabla, incorporar datos en una tabla, y recuperar datos de las tablas de diversas maneras. La base de datos "zool�gico" ser� muy simple (deliveradamente), pero no es dif�cil pensar de situaciones del mundo real en la cual una base de datos similar puede ser usada. Primeramente usaremos la sentencia SHOW para ver cu�les son las bases de datos existentes en el servidor al que estamos conectados: mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.00 sec) mysql> Es probable que la lista de bases de datos que veamos sea diferente en nuestro caso, pero seguramente las bases de datos "mysql" y "test" estar�n entre ellas. En particular, la base de datos "mysql" es requerida, ya que �sta tiene la informaci�n de los privilegios de los usuarios de MySQL. La base de datos "test" es creada durante la instalaci�n de MySQL con el prop�sito de servir como �rea de trabajo para los usuarios que inician en el aprendizaje de MySQL. Se debe anotar tambi�n que es posible que no veamos todas las bases de datos si no tenemos el privilegio SHOW DATABASES. Se recomienda revisar la secci�n del manual de MySQL dedicada a los comandos GRANT y REVOKE. Si la base de datos "test" existe, hay que intentar accesar a ella: mysql> USE test Database changed mysql> Observar que USE, al igual que QUIT, no requieren el uso del punto y coma, aunque si se usa �ste, no hay ning�n problema. El comando USE es especial tambi�n de otra manera: �ste debe ser usado en una s�la l�nea. Podr�amos usar la base de datos "test" (si tenemos acceso a ella) para los ejemplos que vienen a continuaci�n, pero cualquier cosa que hagamos puede ser eliminada por cualquier otro usuario que tenga acceso a esta base de datos. Por esta raz�n, es recomendable que preguntemos al administrador MySQL acerca de la base de datos que podemos usar. Supongamos que deseamos tener una base de datos llamada "zoologico" (n�tese que no se est� acentuando la palabra) a la cual s�lo nosotros tengamos acceso, para ello el administrador necesita ejecutar un comando como el siguiente: mysql> GRANT ALL on zoologico.* TO MiNombreUsuario@MiComputadora -> IDENTIFIED BY 'MiContrase�a'; En donde MiNombreUsuario es el nombre de usuario asignado dentro del contexto de MySQL, MiComputadora es el nombre o la direcci�n IP de la computadora desde la que nos conectamos al servidor MySQL, y MiContrase�a es la contrase�a que se nos ha asignado, igualmente, dentro del ambiente de MySQL exclusivamente. Ambos, nombre de usuario y contrase�a no tienen nada que ver con el nombre de usuario y contrase�a manejados por el sistema operativo (si es el caso).Crear una base de datos
Si el administrador cre� la base de datos al momento de asignar los permisos, podemos hacer uso de ella. De otro modo, nosotros debemos crearla:
mysql> USE zoologico ERROR 1049: Unknown database 'zoologico' mysql> El mensaje anterior indica que la base de datos no ha sido creada, por lo tanto necesitamos crearla. mysql> CREATE DATABASE zoologico; Query OK, 1 row affected (0.00 sec) mysql> USE zoologico Database changed mysql> Bajo el sistema operativo Unix, los nombres de las bases de datos son sensibles al uso de may�sculas y min�sculas (no como las palabras clave de SQL), por lo tanto debemos de tener cuidado de escribir correctamente el nombre de la base de datos. Esto es cierto tambi�n para los nombres de las tablas. Al crear una base de datos no se selecciona �sta de manera aut�matica; debemos hacerlo de manera expl�cita, por ello usamos el comando USE en el ejemplo anterior. La base de datos se crea s�lo una vez, pero nosotros debemos seleccionarla cada vez que iniciamos una sesi�n con mysql. Por ello es recomendable que se indique la base de datos sobre la que vamos a trabajar al momento de invocar al monitor de MySQL. Por ejemplo: shell>mysql -h casita -u blueman -p zoologico Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 to server version: 3.23.38-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> Observar que "zoologico" no es la contrase�a que se est� proporcionando desde la l�nea de comandos, sino el nombre de la base de datos a la que deseamos acceder. Si deseamos proporcionar la contrase�a en la l�nea de comandos despu�s de la opci�n "-p", debemos de hacerlo sin dejar espacios (por ejemplo, -phola123, no como -p hola123). Sin embargo, escribir nuestra contrase�a desde la l�nea de comandos no es recomendado, ya que es bastante inseguro.Crear tabla
Crear la base de datos es la parte m�s f�cil, pero en este momento la base de datos est� vac�a, como lo indica el comando SHOW TABLES:
mysql> SHOW TABLES; Empty set (0.00 sec) La parte un tanto complicada es decidir la estructura que debe tener nuestra base de datos: qu� tablas se necesitan y qu� columnas estar�n en cada tabla. En principio, necesitamos una tabla que contenga un registro para cada una de nuestras mascotas. �sta puede ser una tabla llamada mascotas, y debe contener por lo menos el nombre de cada uno de nuestros animalitos. Ya que el nombre en s� no es muy interesante, la tabla debe contener alguna otra informaci�n. Por ejemplo, si m�s de una persona en nuestra familia tiene una mascota, es probable que tengamos que guardar la informaci�n acerca de quien es el due�o de cada mascota. As� mismo, tambi�n ser�a interesante contar con alguna informaci�n m�s descriptiva tal como la especie, y el sexo de cada mascota. �Y que sucede con la edad?. Esto puede ser tambi�n de inter�s, pero no es una buena idea almacenar este dato en la base de datos. La edad cambia conforme pasa el tiempo, lo cual significa que debemos de actualizar los registros frecuentemente. En vez de esto, es una mejor idea guardar un valor fijo, tal como la fecha de nacimiento. Entonces, cuando necesitemos la edad, la podemos calcular como la diferencia entre la fecha actual y la fecha de nacimiento. MySQL proporciona funciones para hacer operaciones entre fechas, as� que no hay ning�n problema. Al almacenar la fecha de nacimiento en lugar de la edad tenemos algunas otras ventajas: Podemos usar la base de datos para tareas tales como generar recordatorios para cada cumplea�os pr�ximo de nuestras mascotas. Podemos calcular la edad en relaci�n a otras fechas que la fecha actual. Por ejemplo, si almacenamos la fecha en que muri� nuestra mascota en la base de datos, es f�cil calcular que edad ten�a nuestro animalito cuando falleci�. Es probable que estemos pensando en otro tipo de informaci�n que ser�a igualmente �til en la tabla "mascotas", pero para nosotros ser� suficiente por ahora contar con informaci�n de nombre, propietario, especie, nacimiento y fallecimiento. Usaremos la sentencia CREATE TABLE para indicar como estar�n conformados los registros de nuestras mascotas. mysql> CREATE TABLE mascotas( -> nombre VARCHAR(20), propietario VARCHAR(20), -> especie VARCHAR(20), sexo CHAR(1), nacimiento DATE, -> fallecimento DATE); Query OK, 0 rows affected (0.02 sec) mysql> VARCHAR es una buena elecci�n para los campos nombre, propietario, y especie, ya que los valores que almacenar�n son de longitud variable. No es necesario que la longitud de estas columnas sea la misma, ni tampoco que sea de 20. Se puede especificar cualquier longitud entre 1 y 255, lo que se considere m�s adecuado. Si resulta que la elecci�n de la longitud de los campos que hemos hecho no result� adecuada, MySQL proporciona una sentencia ALTER TABLE que nos puede ayudar a solventar este problema. El campo sexo puede ser representado en una variedad de formas, por ejemplo, "m" y "f", o tal vez "masculino" y "femenino", aunque resulta m�s simple la primera opci�n. El uso del tipo de dato DATE para los campos nacimiento y fallecimento debe de resultar obvio. Ahora que hemos creado la tabla, la sentencia SHOW TABLES debe producir algo como: mysql> SHOW TABLES; +---------------------+ | Tables_in_zoologico | +---------------------+ | mascotas | +---------------------+ 1 row in set (0.00 sec) mysql> Para verificar que la tabla fu� creada como nosotros esperabamos, usaremos la sentencia DESCRIBE: mysql> DESCRIBE mascotas; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | nombre | varchar(20) | YES | | NULL | | | propietario | varchar(20) | YES | | NULL | | | especie | varchar(20) | YES | | NULL | | | sexo | char(1) | YES | | NULL | | | nacimiento | date | YES | | NULL | | | fallecimento | date | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) mysql> Podemos hacer uso de la sentencia DESCRIBE en cualquier momento, por ejempo, si olvidamos los nombres � el tipo de las columnas en la tabla.Cargar datos en una tabla
Despu�s de haber creado la tabla, ahora podemos incorporar algunos datos en ella, para lo cual haremos uso de las sentencias INSERT y LOAD DATA.
Supongamos que los registros de nuestras mascotas pueden ser descritos por los datos mostrados en la siguiente tabla.
| Nombre | Propietario | Especie | Sexo | Nacimiento | Fallecimento |
|---|---|---|---|---|---|
| Fluffy | Arnoldo | Gato | f | 1999-02-04 | |
| Mau | Juan | Gato | m | 1998-03-17 | |
| Buffy | Arnoldo | Perro | f | 1999-05-13 | |
| FanFan | Benito | Perro | m | 2000-08-27 | |
| Kaiser | Diana | Perro | m | 1998-08-31 | 1997-07-29 |
| Chispa | Omar | Ave | f | 1998-09-11 | |
| Wicho | Tom�s | Ave | 2000-02-09 | ||
| Skim | Benito | Serpiente | m | 2001-04-29 |
Recuperar informaci�n de una tabla
La sentencia SELECT es usada para obtener la informaci�n guardada en una tabla. La forma general de esta sentencia es:
SELECT LaInformaci�nQueDeseamos FROM DeQueTabla WHERE Condici�nASatisfacer Aqu�, LaInformaci�nQueDeseamos es la informaci�n que queremos ver. Esta puede ser una lista de columnas, o un * para indicar "todas las columnas". DeQueTabla indica el nombre de la tabla de la cual vamos a obtener los datos. La cla�sula WHERE es opcional. Si est� presente, la Condici�nASatisfacer especifica las condiciones que los registros deben satisfacer para que puedan ser mostrados.Seleccionando todos los datos
La manera m�s simple de la sentencia SELECT es cuando se recuperan todos los datos de una tabla: mysql> SELECT * FROM mascotas; +--------+-------------+-----------+------+------------+---------------+ | nombre | propietario | especie | sexo | nacimiento | fallecimiento | +--------+-------------+-----------+------+------------+---------------+ | Fluffy | Arnoldo | Gato | f | 1999-02-04 | NULL | | Mau | Juan | Gato | m | 1998-03-17 | NULL | | Buffy | Arnoldo | Perro | f | 1999-05-13 | NULL | | FanFan | Benito | Perro | m | 2000-08-27 | NULL | | Kaiser | Diana | Perro | m | 1998-08-31 | 1997-07-29 | | Chispa | Omar | Ave | f | 1998-09-11 | NULL | | Wicho | Tom�s | Ave | NULL | 2000-02-09 | NULL | | Skim | Benito | Serpiente | m | 2001-04-29 | NULL | | Pelusa | Diana | Hamster | f | 2000-03-30 | NULL | +--------+-------------+-----------+------+------------+---------------+ 9 rows in set (0.00 sec) Esta forma del SELECT es �til si deseamos ver los datos completos de la tabla, por ejemplo, para asegurarnos de que est�n todos los registros despu�s de la carga de un archivo. Por ejemplo, en este caso que estamos tratando, al consultar los registros de la tabla, nos damos cuenta de que hay un error en el archivo de datos (mascotas.txt): parece que Kaiser ha nacido despu�s de que ha fallecido!. Al revisar un poco el pedigree de Kaiser encontramos que la fecha correcta de nacimiento es el a�o 1989, no 1998. Hay por lo menos un par de maneras de solucionar este problema: Editar el archivo "mascotas.txt" para corregir el error, eliminar los datos de la tabla mascotas con la sentencia DELETE, y cargar los datos nuevamente con el comando LOAD DATA: mysql> DELETE FROM mascotas; mysql> LOAD DATA LOCAL INFILE "mascotas.txt" INTO TABLE mascotas; Sin embargo, si hacemos esto, debemos ingresar los datos de Pelusa, la mascota de nuestra hermana Diana. La segunda opci�n consiste en corregir s�lo el registro err�neo con una sentencia UPDATE: mysql> UPDATE mascotas SET nacimiento="1989-08-31" WHERE nombre="Kaiser"; Como se mostr� anteriormente, es muy f�cil recuperar los datos de una tabla completa. Pero t�picamente no deseamos hacer esto, particularmente cuando las tablas son demasiado grandes. En vez de ello, estaremos m�s interesados en responder preguntas particulares, en cuyo caso debemos especificar algunas restricciones para la informaci�n que deseamos ver.Seleccionar registros particulares
Podemos seleccionar s�lo registros particulares de una tabla. Por ejemplo, si deseamos verificar el cambio que hicimos a la fecha de nacimiento de Kaiser, seleccionamos s�lo el registro de Kaiser de la siguiente manera:
mysql> SELECT * FROM mascotas WHERE nombre="Kaiser"; +--------+-------------+---------+------+------------+--------------+ | nombre | propietario | especie | sexo | nacimiento | fallecimento | +--------+-------------+---------+------+------------+--------------+ | Kaiser | Diana | Perro | m | 1989-08-31 | 1997-07-29 | +--------+-------------+---------+------+------------+--------------+ 1 row in set (0.00 sec) La salida mostrada confirma que el a�o ha sido corregido de 1998 a 1989. La comparaci�n de cadenas es normalmente no sensitiva, as� que podemos especificar el nombre como "kaiser", "KAISER", etc. El resultado de la consulta ser� el mismo. Podemos adem�s especificar condiciones sobre cualquier columna, no s�lo el "nombre". Por ejemplo, si deseamos conocer qu� mascotas nacieron despu�s del 2000, tendr�amos que usar la columna "nacimiento": mysql> SELECT * FROM mascotas WHERE nacimiento >= "2000-1-1"; +--------+-------------+-----------+------+------------+---------------+ | nombre | propietario | especie | sexo | nacimiento | fallecimiento | +--------+-------------+-----------+------+------------+---------------+ | FanFan | Benito | Perro | m | 2000-08-27 | NULL | | Wicho | Tom�s | Ave | NULL | 2000-02-09 | NULL | | Skim | Benito | Serpiente | m | 2001-04-29 | NULL | | Pelusa | Diana | Hamster | f | 2000-03-30 | NULL | +--------+-------------+-----------+------+------------+---------------+ 4 rows in set (0.00 sec) Podemos tambi�n combinar condiciones, por ejemplo, para localizar a los perros hembras: mysql> SELECT * FROM mascotas WHERE especie="Perro" AND sexo="f"; +--------+-------------+---------+------+------------+---------------+ | nombre | propietario | especie | sexo | nacimiento | fallecimiento | +--------+-------------+---------+------+------------+---------------+ | Buffy | Arnoldo | Perro | f | 1999-05-13 | NULL | +--------+-------------+---------+------+------------+---------------+ 1 row in set (0.00 sec) La consulta anterior usa el operador l�gico AND. Hay tambi�n un operador l�gico OR: mysql> SELECT * FROM mascotas WHERE especie = "Ave" OR especie = "Gato"; +--------+-------------+---------+------+------------+---------------+ | nombre | propietario | especie | sexo | nacimiento | fallecimiento | +--------+-------------+---------+------+------------+---------------+ | Fluffy | Arnoldo | Gato | f | 1999-02-04 | NULL | | Mau | Juan | Gato | m | 1998-03-17 | NULL | | Chispa | Omar | Ave | f | 1998-09-11 | NULL | | Wicho | Tom�s | Ave | NULL | 2000-02-09 | NULL | +--------+-------------+---------+------+------------+---------------+ 4 rows in set (0.00 sec) El operador AND y el operador OR pueden ser intercambiados. Si hacemos esto, es buena idea usar par�ntesis para indicar como deben ser agrupadas las condiciones: mysql> SELECT * FROM mascotas WHERE (especie = "Gato" AND sexo = "m") -> OR (especie = "Perro" AND sexo = "f"); +--------+-------------+---------+------+------------+---------------+ | nombre | propietario | especie | sexo | nacimiento | fallecimiento | +--------+-------------+---------+------+------------+---------------+ | Mau | Juan | Gato | m | 1998-03-17 | NULL | | Buffy | Arnoldo | Perro | f | 1999-05-13 | NULL | +--------+-------------+---------+------+------------+---------------+ 2 rows in set (0.00 sec)Seleccionar columnas particulares
Si no deseamos ver los registros completos de una tabla, entonces tenemos que usar los nombres de las columnas en las que estamos interesados separ�ndolas por coma. Por ejemplo, si deseamos conocer la fecha de nacimiento de nuestras mascotas, debemos seleccionar la columna "nombre" y "nacimiento":
mysql> SELECT nombre, nacimiento FROM mascotas; +--------+------------+ | nombre | nacimiento | +--------+------------+ | Fluffy | 1999-02-04 | | Mau | 1998-03-17 | | Buffy | 1999-05-13 | | FanFan | 2000-08-27 | | Kaiser | 1989-08-31 | | Chispa | 1998-09-11 | | Wicho | 2000-02-09 | | Skim | 2001-04-29 | | Pelusa | 2000-03-30 | +--------+------------+ 9 rows in set (0.00 sec) Para conocer qui�n tiene alguna mascota, usaremos la siguiente consulta: mysql> SELECT propietario FROM mascotas; +-------------+ | propietario | +-------------+ | Arnoldo | | Juan | | Arnoldo | | Benito | | Diana | | Omar | | Tom�s | | Benito | | Diana | +-------------+ 9 rows in set (0.00 sec) Sin embargo, debemos notar que la consulta recupera el nombre del propietario de cada mascota, y algunos de ellos aparecen m�s de una vez. Para minimizar la salida, agregaremos la palabra clave DISTINCT: mysql> SELECT DISTINCT propietario FROM mascotas; +-------------+ | propietario | +-------------+ | Arnoldo | | Juan | | Benito | | Diana | | Omar | | Tom�s | +-------------+ 6 rows in set (0.03 sec) Se puede usar tambi�n una cla�sula WHERE para combinar selecci�n de filas con selecci�n de columnas. Por ejemplo, para obtener la fecha de nacimiento de los perritos y los gatitos, usaremos la siguiente consulta: mysql> SELECT nombre, especie, nacimiento FROM mascotas -> WHERE especie = "perro" OR especie = "gato"; +--------+---------+------------+ | nombre | especie | nacimiento | +--------+---------+------------+ | Fluffy | Gato | 1999-02-04 | | Mau | Gato | 1998-03-17 | | Buffy | Perro | 1999-05-13 | | FanFan | Perro | 2000-08-27 | | Kaiser | Perro | 1989-08-31 | +--------+---------+------------+ 5 rows in set (0.00 sec)Ordenar registros
Se debe notar en los ejemplos anteriores que las filas regresadas son mostradas sin ning�n orden en particular. Sin embargo, frecuentemente es m�s f�cil examinar la salida de una consulta cuando las filas son ordenadas en alguna forma �til. Para ordenar los resultados, tenemos que usar una cl�usula ORDER BY.
Aqu� aparecen algunos datos ordenados por fecha de nacimiento:
mysql> SELECT nombre, nacimiento FROM mascotas ORDER BY nacimiento; +--------+------------+ | nombre | nacimiento | +--------+------------+ | Kaiser | 1989-08-31 | | Mau | 1998-03-17 | | Chispa | 1998-09-11 | | Fluffy | 1999-02-04 | | Buffy | 1999-05-13 | | Wicho | 2000-02-09 | | Pelusa | 2000-03-30 | | FanFan | 2000-08-27 | | Skim | 2001-04-29 | +--------+------------+ 9 rows in set (0.00 sec) En las columnas de tipo caracter, el ordenamiento es ejecutado normalmente de forma no sensitiva, es decir, no hay diferencia entre may�sculas y min�sculas. Sin embargo, se puede forzar un ordenamiento sensitivo al usar el operador BINARY. Para ordenar en orden inverso, debemos agregar la palabra clave DESC al nombre de la columna que estamos usando en el ordenamiento: mysql> SELECT nombre, nacimiento FROM mascotas ORDER BY -> nacimiento DESC; +--------+------------+ | nombre | nacimiento | +--------+------------+ | Skim | 2001-04-29 | | FanFan | 2000-08-27 | | Pelusa | 2000-03-30 | | Wicho | 2000-02-09 | | Buffy | 1999-05-13 | | Fluffy | 1999-02-04 | | Chispa | 1998-09-11 | | Mau | 1998-03-17 | | Kaiser | 1989-08-31 | +--------+------------+ 9 rows in set (0.00 sec) Podemos ordenar m�ltiples columnas. Por ejemplo, para ordenar por tipo de animal, y poner al inicio los animalitos m�s peque�os de edad, usaremos la siguiente consulta: mysql> SELECT nombre, especie, nacimiento FROM mascotas -> ORDER BY especie, nacimiento DESC; +--------+-----------+------------+ | nombre | especie | nacimiento | +--------+-----------+------------+ | Wicho | Ave | 2000-02-09 | | Chispa | Ave | 1998-09-11 | | Fluffy | Gato | 1999-02-04 | | Mau | Gato | 1998-03-17 | | Pelusa | Hamster | 2000-03-30 | | FanFan | Perro | 2000-08-27 | | Buffy | Perro | 1999-05-13 | | Kaiser | Perro | 1989-08-31 | | Skim | Serpiente | 2001-04-29 | +--------+-----------+------------+ 9 rows in set (0.00 sec) Notar que la palabra clave DESC aplica s�lo a la columna nombrada que le precede.