¡Descarga BASE DE DATOS -MYSQL BÁSICO y más Guías, Proyectos, Investigaciones en PDF de Informática solo en Docsity!
MySQL básico
En esta Unidad
aprenderás a:
Q Instalar, configurar y utilizar
el sistema gestor de bases de
datos MySQL.
Q Realizar cualquier operación de
creación, mantenimiento o
eliminación de bases de datos
y tablas en MySQL.
Q Gestionar aspectos de seguridad
en MySQL, como el control de los
usuarios, o la realización de copias
de seguridad.
Q Instalar, configurar y utilizar un
paquete gráfico para realizar
visualmente la administración
y mantenimiento de una
base de datos MySQL.
Bases de datos y
software libre.
MySQL es un sistema gestor de bases de datos que se puede encuadrar dentro de la categoría de los progra- mas open-source.
El término open-source surgió del anteriormente usado free-software , que hacía referencia a los programas cuyo código estaba disponible para el usuario, fueran gratui- tos o no. Como free en inglés significa libre pero tam- bién gratis, se prestaba bastante a confusión, por lo que pasó a utilizarse open-source («código abierto»).
Aunque open-source no siempre implica que su uso sea gratuito, MySQL sí que lo es.
Mención aparte merecen las licencias de tipo GPL, que pueden consultarse en la siguiente página web: www.gnu.org/copleft/gpl.html.
En esta unidad trataremos el funcionamiento de MySQL por considerarlo altamente representativo de las bases de datos de libre uso.
A Orígenes e historia de MySQL
MySQL es un caso particular, pues se trata de un pro- grama de licencia open-source y gratuito pero que, sin embargo, está mantenido por una empresa, MySQL AB, con sede en Suecia.
El código fuente de MySQL está sólo relativamente abierto y disponible para modificaciones, puesto que es
la empresa MySQL AB la que contrata y coordina los tra- bajos de mantenimiento del producto. No obstante, los trabajadores contratados, procedentes de todo el mundo, son usuarios del producto que realizan sus encargos a través de Internet.
Orígenes
El origen de MySQL se remonta a la década de los ochenta. Michael Widenius, también conocido como Monty , un joven programador que realizaba complejas aplicaciones en lenguaje BASIC, al no encontrar un sis- tema de almacenamiento de archivos que le resultara satisfactorio, pensó en construir el suyo propio.
Años después, en 1995, y en colaboración con David Axmark, Widenius desarrolló un producto que básica- mente era el resultado de sus investigaciones, más dos aportaciones nuevas: el uso del lenguaje SQL y la acce- sibilidad a través de Internet. Así nació MySQL y tam- bién la empresa MySQL AB.
Evolución
La evolución de MySQL se produce con las sugerencias de los usuarios, canalizadas por la empresa MySQL AB, que contrata a programadores de todo el mundo para que, a través de Internet, vayan ampliando y mejorando el producto.
8.1 MySQL: un SGBD de código abierto
8.1 MySQL: un SGBD de código abierto
Un programa open-source es aquel cuyo código fuente está disponible para los usuarios y abierto a modificaciones.
En el caso de que el programa open-source se pueda adquirir gratis, el usuario debe distribuir también gratis los cambios que pueda realizar, o los productos que pueda desarrollar basándose en el original. En caso contrario, si el usuario desea hacer negocio con el producto, entonces debe adquirir la licencia comercial de pago.
Fig. 8.1. Michael Widenius.
A continuación, se abrirá una página donde aparece una exposición del producto, de las licencias de uso, de las novedades que ofrece la última distribución disponible y, finalmente, una exhaustiva relación de versiones a descargar, agrupadas por la plataforma a la que van diri- gidas (Linux, Solaris, Windows, etc.). Por nuestra parte, trabajaremos con la versión de Windows; por tanto, ire- mos bajando la página hasta encontrar la sección Win- dows Downloads (véase la Figura 8.3).
Como se puede apreciar, existen varias opciones. Por nuestra parte, elegiremos Windows Essentials , que es la versión más adecuada para el presente texto. Para empezar la descarga haremos clic en Pick a mirror , el enlace que puede verse a la derecha de la figura.
Una vez seleccionado el producto que deseamos descar- gar, surgirá una pantalla de registro, que se puede pasar por alto siguiendo hacia abajo o haciendo clic en Just take me to the downloads , y aparecerá una lista con los servidores disponibles desde los que se puede bajar el fichero de instalación. Esta lista será similar a la que se reproduce en la Figura 8.4.
La Web ha detectado, a través de la IP de mi ordenador, que el servidor más próximo y seguramente el que más me interesa está en España: se trata de RedIris. Así pues, elegiremos dicho servidor y, a continuación, esco- gemos la opción de bajada por http pulsando en el enlace correspondiente.
Una vez elegida la opción Guardar del cuadro de diá- logo, empezará la descarga del archivo.
Instalación
En primer lugar, es importante tener en cuenta que el fichero descargado sólo sirve para actualizar versiones de MySQL a partir de la 4.1.5. Si se dispone de una ver- sión más antigua, entonces es necesario desinstalarla previamente.
Para instalar la nueva versión, buscaremos la carpeta en la que hemos guardado el fichero de instalación. Des- pués, ejecutaremos dicho fichero haciendo doble clic sobre el mismo y, seguidamente, aparecerá una panta- lla como la que se reproduce en la Figura 8.5.
De entre las diferentes modalidades de instalación que se nos ofrecen a continuación, escogeremos la opción Typical. Esta modalidad es la más básica y la que se reco- mienda para un uso general y personal de la aplicación.
Las otras posibilidades de instalación son Complete , mediante la cual se instalan todas las características del producto, y Custom , que deja al usuario la elección sobre qué partes instalar y cuáles no.
8.1 MySQL: un SGBD de código abierto
Fig. 8.3. Opciones de descarga de MySQL para Windows.
Fig. 8.4. Servidores de descarga de MySQL.
Fig. 8.5. Inicio de la instalación de MySQL.
En el CD del alumno puedes encontrar el fichero de instalación de MySQL en su versión 4.1.14. Su nombre es mysql-essential-4.1.14-win32.msi.
En la Figura 8.6 podemos apreciar la pantalla con los tres modos de instalación.
Una vez pulsado el botón Siguiente (Next) , aparecerá una pantalla en la que se nos muestra un resumen de las opciones escogidas. En esta pantalla todavía dispo- nemos de la posibilidad de detener la instalación, o bien dar un paso atrás. Si decidimos continuar, pulsare- mos el botón Instalar (Install) para finalmente empe- zar la instalación propiamente dicha.
Tras las correspondientes pantallas de espera, una vez terminada la copia de ficheros a nuestro disco, se nos indicará la posibilidad de realizar un registro del pro- ducto: esta operación la dejaremos aplazada de momento. Así pues, procederemos a seleccionar Saltar registro (Skip sign-up) , tal como se puede observar en la Figura 8.7.
El registro del producto sirve, entre otras cosas, para tener acceso a contenidos especiales de la página web de MySQL. A continuación, aparecerá una pantalla donde se nos presentará la opción de configurar el ser- vidor de MySQL. Tal como se aprecia en la Figura 8.8, dejaremos marcada la casilla correspondiente y pulsare- mos Terminar (Finish) para que MySQL se configure y se finalice la instalación.
Configuración
Si hemos dejado marcada la casilla Configurar ahora el servidor MYSQL (Configure the MySQL Server now) de la última pantalla de instalación (véase la Figura 8.8), entonces en este momento empezará el proceso de con- figuración.
Conviene tener en cuenta que si tenemos versiones anteriores de MySQL instaladas en nuestra máquina, se nos ofrecerá la posibilidad de volver a configurar el pro- ducto, o bien desinstalarlo. De esta manera, se elimina- rán los servicios de Windows correspondientes.
Tanto si se trata de la primera instalación, como si estamos volviendo a configurar una instalación ante- rior, a continuación aparecerá una pantalla que nos invita a decidir si queremos llevar a cabo una instala- ción detallada (detailed) o estándar (standard).
Q (^) La instalación detallada propone una configuración automática y óptima para el servidor. Q (^) La instalación estándar ofrece una configuración para un uso general del producto. Además, la insta- lación estándar permite seguir el proceso paso a paso y, si procede, hacer cambios en las opciones por defecto que se van ofreciendo.
8.1 MySQL: un SGBD de código abierto
Fig. 8.6. Modos de instalación de MySQL.
Fig. 8.7. Registro on-line de MySQL.
Fig. 8.8. Fin de la instalación, con opción a iniciar el proceso de configuración.
8.1 MySQL: un SGBD de código abierto
Q (^) Entrar a nuestro servidor desde máquinas remotas, a través de la red o incluso desde Internet (Enable root access from remote machines). Por obvios motivos, esta posibilidad tampoco la contemplaremos. Esta segunda opción deberá activarse sólo si se instala MySQL en un servidor de red al cual se accederá desde los diferentes terminales que se le conectan.
Una vez pulsado el botón Siguiente (Next) , aparecerá una pantalla similar a la que se reproduce en la Figura 8.12, donde se nos ofrece la posibilidad de volver atrás o can- celar la instalación. Por nuestra parte continuaremos y, para ello, pulsaremos el botón Ejecutar (Execute).
Tras pulsar el botón Execute , irán marcándose las casi- llas que se ven en la Figura 8.12 según se vayan reali- zando las distintas tareas.
Si surge algún problema, entonces se detendrá el pro- ceso y aparecerá un aviso de error con una breve expli- cación de lo sucedido. Muchos de los errores que tienen lugar durante esta fase de la configuración provienen de instalaciones anteriores, con lo que la solución será desinstalar totalmente cualquier otra versión de MySQL que haya en el ordenador.
En otras ocasiones el problema puede deberse a la falta de permisos para crear el servicio, puesto que la insta- lación la debería realizar un usuario con permisos de administrador en el ordenador. El error también puede obedecer a la presencia de un servicio de MySQL ante- rior o incompatible con el que se intenta instalar. En este caso habría que eliminar los servicios que ocasio- nan el problema.
Los servicios de Windows se pueden eliminar de varias formas, pero no desde Herramientas Administrativas/Ser-
vicios. Una opción es ir al registro de Windows y buscar el siguiente apartado:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
Services
Desde este apartado localizaremos y eliminaremos el servicio correspondiente.
La otra opción es ir a Inicio/Ejecutar y teclear cmd.exe para salir del entorno MS-DOS. Seguidamente escribire- mos sc delete nombre-servicio ___ y, automática- mente, desaparecerá el servicio correspondiente. Así, por ejemplo, podríamos borrar el servicio de MySQL con sc delete mysql ___.
Finalmente, si todo el proceso se ha llevado a cabo de forma adecuada, debería aparecer una pantalla similar a la que se reproduce en la Figura 8.13.
Así pues, ya tenemos instalado y configurado nuestro servidor. En la Figura 8.14 puede observarse, dentro de la lista de servicios de Windows, el correspon- diente a MySQL.
Fig. 8.12. Ejecución del proceso de configuración.
Fig. 8.13. MySQL se ha instalado y configurado correcta- mente.
Si no has elegido la opción de inicio automático, debes arrancar manualmente el servicio cada vez que quieras trabajar con MySQL.
Fig. 8.14. El servicio Windows de MySQL.
8.1 MySQL: un SGBD de código abierto
Un SGBD cliente/servidor
MySQL, como SGBD cliente/servidor que es, está for- mado por dos componentes:
Q (^) Servidor. Se encarga de recoger, interpretar y ejecu- tar todas las peticiones de los usuarios de la base de datos. Todas las tareas de configuración que hemos realizado se refieren a él. Q (^) Cliente. Es la parte con la que trabajarán directa- mente los usuarios.
Ambos componentes pueden instalarse por separado en diferentes ordenadores, generalmente un servidor y varios clientes, si durante la instalación se elige la variante personalizada. Una vez presentado el modo de configurar el servidor, ha llegado el momento de abor- dar el componente cliente , que es el más importante de cara al usuario final.
En primer lugar, vamos a estudiar el programa cliente básico, que ofrece acceso a MySQL en modo texto desde una línea de comandos.
Caso práctico
Si has ido realizando los pasos explicados
en el Apartado anterior y todo ha
funcionado correctamente, entonces
tendrás MySQL instalado en tu ordenador.
Sin embargo, vas a efectuar una sencilla
comprobación.
Se pide
Comprueba si la variable de sistema PATH se ha actualizado, incluyendo ahora la referencia a las car- petas correspondientes a MySQL.
Solución
Hay dos formas de consultar el contenido de la varia- ble de sistema PATH. Una de ellas es, tal como se hacía en MS-DOS, saliendo a la línea de comandos (Inicio/Ejecutar/cmd.exe) y, a continuación, tecle- ando SET que nos muestra el contenido de todas las variables del sistema. En la Figura 8.15 puede verse el resultado.
Dado que nos encontramos en Windows, también podemos consultar el valor de cualquier variable del sistema. Para ello, nos dirigiremos al panel de con- trol, entraremos en Sistema y seleccionaremos segui- damente la pestaña Opciones avanzadas.
Como puede observarse en la Figura 8.16, en la parte inferior del recuadro aparece un botón con el título Variables de entorno. Si hacemos clic en dicho botón, veremos el contenido de las variables de sistema, tanto su valor en general como el valor actual para el usuario activo.
1
Fig. 8.15. Consulta de PATH desde la línea de comandos. Fig. 8.16. Consulta de las variables de sistema desde Windows.
8.2 El cliente de MySQL en modo texto
De esta manera, la sentencia completa que deberíamos introducir sería la que se aprecia en la Figura 8.20.
Las sentencias SQL deben acabar con un punto y coma. En caso contrario, dicha sentencia se considera inaca- bada y aparece una línea nueva cuyo indicador es una flecha. En esta línea terminaremos de escribir la senten- cia que ha quedado incompleta, tal como puede obser- varse en la Figura 8.21.
Por otra parte, si queremos abandonar la sentencia sin que ésta se ejecute, lo conseguiremos escribiendo \c. A modo de ejemplo, en la Figura 8.22 aparece una consulta donde se ha cometido un error y se cancela sin ejecutarse.
Finalmente, si deseamos salir de MySQL volviendo a la línea de comandos de Windows, podemos usar por igual exit o quit.
B
Uso de bases de datos en modo texto
Ahora que ya conocemos el entorno de trabajo, es hora de que presentemos el modo de acceder a las bases de datos para comenzar a utilizarlas.
En primer lugar, es importante conocer el sitio donde se guardan las bases de datos que iremos creando, así como el fichero de configuración en el cual se guarda la información acerca de dicha ubicación.
Localización de las bases de datos
Las bases de datos de MySQL se guardan, en entornos Windows, en la carpeta c:\Archivos de Programa
MySQL\MySQL Server 4.1\Data, su ubicación por defecto, como puede apreciarse en la Figura 8.23. Sin embargo, dicha ubicación puede cambiarse. Para ello deberíamos:
Q (^) Copiar todo el contenido de la carpeta Data a la nueva localización. Q (^) Modificar el fichero my.ini que se encuentra en la misma carpeta que Data , tal como también se observa en la Figura 8.23. Para ello, nos dirigiremos a la sección SERVER SECTION y cambiaremos el valor de la variable Datadir (véase la Figura 8.24).
Como ya sabes por unidades anteriores, en una sentencia SQL pueden utilizarse tanto funciones del lenguaje, como es el caso de version(), como variables del sistema, por ejemplo current_date.
Fig. 8.20. Consulta de variables en MySQL.
Fig. 8.21. Finalización de una sentencia incompleta.
Fig. 8.22. Interrupción de una sentencia. Fig. 8.23. Localización de la carpeta Data y el fichero de configuración my.ini.
8.2 El cliente de MySQL en modo texto
Dentro de la carpeta Data se irán creando diversas car- petas para las diferentes bases de datos que se vayan dando de alta. Por defecto, MySQL incluye una base de datos denominada mysql que guarda información sobre el propio sistema. En la Figura 8.25 puede observarse la subcarpeta correspondiente a dicha base de datos.
Utilización de las bases de datos
Lógicamente, deberemos saber qué bases de datos están disponibles antes de poder empezar a usarlas. Para obtener una lista de las bases de datos accesibles al usuario, usaremos la sentencia SHOW DATABASES. El resultado, en nuestro caso, es el que se muestra en la Figura 8.26.
Como se aprecia en esta figura, el número de bases de datos, así como sus nombres, coincide con el de subcar- petas dentro de Data , algo que ya se podía prever.
De todas formas, ya sabemos de unidades anteriores que las bases de datos están formadas por tablas y que son éstas las que contienen la información de la base de datos. Así pues, para realizar una consulta deberemos siempre conocer el nombre de las tablas en las que se encuentra la información que queremos recuperar.
Existe una sentencia para ver las tablas que forman una base de datos, pero para ello debemos, en primer lugar, abrir dicha base de datos.
La instrucción que permite abrir una base de datos es USE, seguida del nombre de la base de datos que se quiere abrir. Otra posibilidad que veremos más adelante consiste en utilizar el comando CONNECT. En la Figura 8.27 se muestra cómo se abre la base de datos mysql.
El mensaje Database changed nos indica que la apertura de la base de datos se ha realizado correctamente.
Una vez abierta la base de datos mysql , estamos en con- diciones de obtener una lista de las tablas que dicha base de datos contiene. Para ello, usaremos la instrucción SHOW TABLES sin ningún parámetro adicional, ya que no se necesita hacer referencia a la base de datos, puesto que sólo podremos listar las tablas que previamente hemos abierto y están contenidas en dicha base de datos. En la Figura 8.28 se muestra la lista de la base de datos en cuestión obtenida tras este proceso.
Fig. 8.24. Contenido del fichero my.ini.
Fig. 8.25. Subcarpetas para las bases de datos.
Fig. 8.26. Lista de bases de datos disponibles.
Al contrario de otros sistemas como Oracle, la conexión al SGBD no está asociada a la apertura de una base de datos en concreto, sino que este pro- ceso se realiza con posterioridad a la conexión.
Fig. 8.27. Apertura de una base de datos.
Si abrimos la base de datos mediante use y ejecutamos SHOW TABLES, comprobaremos que la base de datos está vacía, ya que la acabamos de crear. Para dar de alta una tabla usaremos otra sentencia que ya se cono- ce: CREATE TABLE.
Es conveniente conocer los tipos de datos que pode- mos usar en MySQL, a pesar de que no varían sustancial- mente respecto a los que se han ido presentando a lo largo de este manual. No obstante, en la Tabla 8.1 pre- sentamos los tipos de datos más importantes.
Al igual que ocurre con otros lenguajes, los tipos pue- den combinarse con otros atributos como, por ejemplo, UNSIGNED, que indica que sólo se admiten valores posi- tivos, o bien ZEROFILL, que hace que los espacios en blanco a la izquierda se rellenen con ceros.
A continuación, presentamos un Caso práctico que nos servirá para consolidar los conocimientos adquiridos.
Introducción y manipulación de datos
Las instrucciones para introducir y manipular datos en una tabla han sido ampliamente estudiadas en unidades anteriores. Sin embargo, aprovecharemos este punto para recordar las más importantes en la siguiente tabla.
8.2 El cliente de MySQL en modo texto
Al crear una tabla debes enumerar los campos que la formarán, así como el nombre y el tipo de cada campo.
Tipo Descripción
varchar (n) Puede contener cualquier carácter. Longitud variable con un tamaño máximo de n bytes, siendo el límite para n de 65536
char (n)
Puede contener cualquier carácter, pero con longitud fija de n bytes. Los espacios no ocupados se rellenan con blancos. Valor máximo de n es 255 date (^) Una fecha datetime (^) Una fecha y una hora time (^) Una hora year (n) (^) Un año con n dígitos (2 o 4) bit (n) (^) Un número de n bits. Sólo en versiones de MySQL superiores a la 5
bool (^) Lógico: 0 falso, 1 verdadero tinyint (n) (^) Un entero con un máximo de n cifras. Valores entre –128 y 127
smallint (n) (^) Un entero con un máximo de n cifras. Valores entre –32768 y 32767
mediumint (n) Un entero con un máximo de^ n^ cifras. Valores entre –8388608 y 8388607 int (n) Un entero con un máximo de^ n^ cifras. Valores entre –2147483648 y 2147483647 bigint (n) Un entero con un máximo de^ n^ cifras. Para valores no incluidos en los intervalos anteriores float (n, d) Un número real con^ n^ cifras de las cuales^ d^ son decimales. Valores entre –3.402823466E+38 y 3.402823466E+
double (n, d)
Un número real con n cifras de las que d son decimales. Para valores con mayor precisión que los anteriores, o no incluidos en el intervalo enum (‘a’, ‘b’, ‘c’...) Puede incluir cualquiera de los elementos enumerados en la lista
Tabla 8.1. Tipos de datos en MySQL.
Caso práctico
Vas a repasar la creación de tablas.
Se pide
En la base de datos pruebas debes crear dos tablas, alumnos y asignaturas , con la siguiente descripción:
alumno(#idalu, nomalu, fnacimiento) asignatura(#idasi, nomasi)
Solución
Con los conocimientos que hemos adquirido hasta el momento, podemos crear las tablas con las siguien- tes instrucciones:
mysql> create table alumnos (idalu int(4) unsigned, nomalu varchar(30), fnacimiento date); mysql> create table asignatura (idasi smallint(2) unsigned, nomasi varchar(20));
La solución propuesta es sólo una de las posibles soluciones a este caso. Es probable que se eche en falta la presencia de claves primarias, puesto que la definición de claves primarias, foráneas y otras res- tricciones similares se presentarán más adelante.
2
8.2 El cliente de MySQL en modo texto
No vamos a insistir en la sintaxis y el uso de estas ins- trucciones. Nos parece más importante incidir en aque- llos aspectos en los que MySQL dispone de alguna fun- cionalidad añadida o diferente.
Así pues, para insertar filas en una tabla podemos, ade- más de hacerlo manualmente con INSERT, aprovechar la instrucción LOAD DATA INFILE…INTO TABLE… que permite la importación de datos contenidos en un fichero de texto. Los requisitos para que esta sentencia funcione son los siguientes:
Q (^) El fichero de texto debe estar en la misma carpeta que la tabla a la cual desea importarse. Q (^) En el fichero de texto debe haber un tabulador de separación entre cada columna.
Supongamos que disponemos del fichero asig.txt con el formato que puede apreciarse en la Figura 8.31.
El fichero debe estar guardado en la misma carpeta pruebas , dentro de Data. El espacio entre las dos colum- nas ha de insertarse pulsando el tabulador y no la barra espaciadora.
A continuación, nos dirigiremos a MySQL, abriremos la base de datos pruebas , y teclearemos:
mysql> load data infile ‘asig.txt’ into table asignatura;
Como se puede observar, el nombre del fichero de texto va entre comillas simples, mientras que el nombre de la tabla se introduce sin ningún delimitador. Si el proceso se realiza correctamente, aparecerá por pantalla el número de filas que se han añadido a la tabla.
En la Figura 8.32 se puede apreciar el proceso completo, así como la posterior comprobación.
Si la importación da problemas, en ocasiones se resuelven añadiendo en el fichero de texto un carácter al final de cada línea que sirva para marcar el salto a la siguiente. Si optamos por esta posibilidad, entonces la instrucción de importación queda de la siguiente manera:
LOAD DATA INFILE ‘fichero.txt’ INTO TABLE tabla LINES TERMINATED BY ‘carácter’;
Como es lógico, deberá usarse como marcador de final de línea un carácter que no pueda encontrarse en otro punto de la fila, lo cual llevaría a confusión y a una errónea importación de los datos.
Si recordamos el funcionamiento de INSERT, la importa- ción que acabamos de hacer sería equivalente a haber tecleado desde la línea de comandos la siguiente ins- trucción:
mysql> insert into asignatura values(1,’OBDOC’),(2,’FOL’),(3,’RET’),(4,’IMESI’ ),(5,’IMAOC’);
Veamos seguidamente cómo implementar las claves, tanto primarias como ajenas, así como otras restriccio- nes de uso común.
Instrucción Función
INSERT INTO… (^) Introducir nuevas filas en la tabla
DELETE FROM… (^) Borrar filas en la tabla
UPDATE… (^) Modificar filas en la tabla
Tabla 8.2. Instrucciones de introducción y modificación de filas.
Fig. 8.31. El fichero de texto asig.txt.
Fig. 8.32. Importación de filas desde un archivo.
8.2 El cliente de MySQL en modo texto
nomciud varchar(25), idprov int(2) unsigned references provincia(idprov)); Lógicamente, para poder hacer referencia a la tabla pro- vincia , ésta debe haber sido declarada previamente. Como regla general, es conveniente declarar en primer lugar aquellas tablas que no contienen claves ajenas.
Es realmente importante tener en cuenta que los cam- pos enlazados (la clave primaria y la clave ajena) deben ser exactamente del mismo tipo. Por tanto, si idprov se hubiera definido como int(2) UNSIGNED en la tabla pro- vincia , y como int(2) en la tabla ciudad , no se acepta- ría la restricción de clave ajena y aparecería un error.
De la misma forma que ocurría con las claves primarias, las claves ajenas pueden definirse al final de la tabla. Sin embargo, cuando la clave ajena está formada por varios campos, entonces es obligatorio hacerlo de esta forma. Además, si la definición se incluye al final de la tabla, deberá incluirse la cláusula FOREIGN KEY. Así pues, en el caso anterior se debería escribir:
mysql> create table ciudad (idciu int(2) unsigned primary key, nomciu varchar(25), idprov int(2) unsigned, foreign key (idprov) references provincia(idprov));
Así mismo, el campo o campos que forman parte de la clave ajena deben estar indexados, para lo que usare- mos la cláusula INDEX y el nombre del campo o campos que forman el índice entre paréntesis. De esa forma, la creación de la tabla ciudad quedaría, de manera defini- tiva, como sigue:
mysql> create table ciudad (idciu int(2) unsigned primary key, nomciu varchar(25), idprov int(2) unsigned, index(idprov), foreign key (idprov) references provincia(idprov));
Aún queda otra cuestión importante por resolver: en MySQL las tablas pueden ser de varios tipos, entre ellos MyIsam o InnoDB. Aunque más adelante se volverá sobre esta cuestión, es importante saber que las claves ajenas sólo funcionarán si las tablas son de tipo InnoDB ,
al menos en la versión de MySQL que estamos utilizan- do. Es necesario realizar la comprobación, puesto que por defecto MySQL crea las tablas como MyIsam.
Otras restricciones
En la definición de campo es posible incluir otras res- tricciones, además de las de clave. Las más importantes son:
Q (^) Valor único. En ocasiones interesa que un campo que no pertenece a una clave primaria tenga, de todas formas, un valor único para cada fila de la tabla. Podemos pensar, por ejemplo, en el NIF de un fichero de clientes. En estos casos se utiliza la cláusula UNIQUE. He aquí el resultado:
mysql> create table cliente (idCli int(4)unsigned primary key, CliNom varchar(25), CliPob varchar(25), CliNif varchar(15) unique);
Q (^) Autoincrementable. Muchas veces la clave principal de una tabla es simplemente un código que no tiene mayor significado que la identificación de cada ele- mento. Si es así, puede ser interesante hacer que dicho código se vaya incrementando de forma automática cada vez que se da de alta una fila. Así, el primer cliente tendría el código 1 , el segundo tendría el 2 , y así sucesivamente, sin intervención del usuario. Para ello se usa la cláusula AUTO INCREMENT en la definición del campo. He aquí el resultado:
mysql> create table cliente (idCli int(4)unsigned primary key
auto_increment, CliNom varchar(25), CliPob varchar(25), CliNif varchar(15) unique);
Q (^) Valor nulo. Cuando un campo es clave primaria, no puede tener en absoluto un valor nulo, es decir, dejarse sin valor (no se debe confundir con valor 0 , o con espacios en blanco). Sin embargo, esa misma restricción puede aplicarse también a campos no
8.2 El cliente de MySQL en modo texto
clave, mediante la cláusula NOT NULL. Pensemos, por ejemplo, en que sea obligatorio poner un valor al NIF cuando se da de alta un cliente. He aquí el resultado: mysql> create table cliente (idCli int(4)unsigned primary key
auto_increment, CliNom varchar(25), CliPob varchar(25), CliNif varchar(15) not null unique);
Q (^) Valor por defecto. Al dar de alta una fila en una tabla, y antes de introducir algún valor, todos los campos tienen el valor NULL (vacío). A veces, existe un campo que tendrá siempre el mismo valor en todas las filas, salvo contadas excep- ciones. Por ejemplo, si en nuestro fichero de clientes sabe- mos que la gran mayoría son de Valencia, podemos hacer que el campo CliPob asuma, por defecto, el valor Valencia sin tener que escribirlo cada vez. Para ello, se usa la cláusula DEFAULT en la definición del campo. He aquí el resultado:
mysql> create table cliente (idCli int(4)unsigned primary key auto_increment, CliNom varchar(25), CliPob varchar(25) default ‘Valencia’, CliNif varchar(15) not null unique);
Es interesante conocer una particularidad del tipo enum: al declarar el valor por defecto, si se pone éste entre comillas simples, hará referencia al elemento de la lista cuyo valor coincida con el indicado; si, por el contrario, el valor por defecto consiste en un número sin comillas simples, entonces se está haciendo referencia al número de orden en la lista de enum.
Imaginemos que creamos una tabla de hoteles con un campo categoría cuyo valor puede ser 3, 4 o 5 y que queremos que, por defecto, asuma el valor 3. Esto se podría conseguir indistintamente mediante dos formas:
…categoría enum(‘3’,’4’,’5’) default ‘3’ …categoría enum(‘3’,’4’,’5’) default 1
En ambos casos, el valor por defecto será 3.
Caso práctico
Vas a aplicar los conceptos presentados hasta
ahora a las tablas creadas en el Caso práctico 2.
Recordemos cuáles eran:
mysql> create table alumnos (idalu int(4)unsigned, nomalu varchar(30), fnacimiento date); mysql> create table asignatura (idasi smallint(2) unsigned, nomasi varchar(20));
Se pide
En la base de datos pruebas , debes modificar la tabla alum- nos de forma que el campo idalu sea clave primaria y autoin- crementable, mientras que la fecha de nacimiento sea obli- gatoria. Además, debes modificar la tabla asignatura para que el campo idasi sea también clave primaria y autoincre- mentable.
Finalmente, para saber de qué asignaturas se ha matriculado cada alumno, es preciso crear la tabla matrícula. Para ello, deberás basarte en la siguiente descripción:
matrícula(#idalu, #idasi) alumnos asignatura
Solución
En primer lugar, para modificar la estructura de una tabla, se deberá usar ALTER TABLE junto con la cláusula ADD si estamos añadiendo elementos.
En nuestro caso, se añadirá a la tabla alumnos la propiedad de clave primaria para el campo idalu. Además, se modificarán los campos idalu y fnacimiento , el primero para hacerlo autoincre- mentable y el segundo para que no acepte el valor nulo.
Así pues, para modificar cualquier propiedad de un campo que no sea PRIMARY KEY, usaremos ALTER TABLE con la cláu- sula MODIFY.
3
8.3 Seguridad en MySQL
8.3 Seguridad en MySQL
Como casi siempre cuando se mencionan bases de datos, los principales aspectos de la seguridad están relacionados con el control de los accesos a la infor- mación, además de la protección de ésta. En este Apartado abordaremos cómo, de manera básica, se pueden realizar ambas tareas desde el mismo cliente texto de MySQL.
A Gestión de usuarios
La gestión de usuarios en MySQL es muy sencilla y no difiere prácticamente de lo que se ha ido presentando en unidades anteriores.
Este hecho obedece a que las sentencias SQL que ya conocemos se utilizan también en MySQL, así como los permisos típicos.
En cualquier caso, conviene repasar el uso de dichas sentencias, haciendo hincapié en las particularidades del producto que se está tratando.
Creación de usuarios en MySQL
A partir de la versión 5 de MySQL se podrán crear usua- rios mediante la sentencia CREATE USER típica, con la sintaxis siguiente:
CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password’] [, user [IDENTIFIED BY [PASSWORD] ‘password’]] ...
Como se puede apreciar, simplemente se indica el nom- bre de usuario y, de forma opcional, la contraseña. Sin embargo, en las versiones anteriores a la 5 como, por ejemplo, la que nosotros estamos utilizando, no existe todavía esta opción.
Por tanto, debemos buscar otro método para crear usua- rios. Mediante la siguiente sentencia este problema queda resuelto:
GRANT tipo-permiso [(lista-columnas)] ON {tabla | * | . | base-datos.*} TO usuario [IDENTIFIED BY ‘contraseña’]
[WITH GRANT OPTION]; Como se ha repetido en numerosas ocasiones, la senten- cia GRANT se emplea para asignar permisos a usuarios ya existentes. No obstante, en MySQL el usuario se crea automáticamente en caso de no existir. En esta versión, esta posibilidad es la única de la que disponemos para crear usuarios nuevos, a no ser que nos atrevamos a manipular directamente la tabla user.
Antes de mostrar un ejemplo, en la Tabla 8.3 presenta- mos los permisos que pueden asignarse en MySQL.
Veamos un ejemplo de esta instrucción haciendo uso de un Caso práctico.
Al igual que en otros SGBD, la sentencia que permite retirar permisos a los usuarios es REVOKE, cuya sintaxis es la siguiente:
REVOKE tipo-permiso [(lista-columnas)] ON {tabla | * | . | base-datos.*} FROM usuario;
Los tipos de permiso son los mismos que se presentan en la Tabla 8.3.
Permiso Operaciones permitidas
ALL (^) Todas
USAGE (^) Ninguna
ALTER (^) Modificar estructura
CREATE (^) Crear
DELETE, DROP (^) Borrar columnas/tablas
INSERT (^) Insertar
SELECT (^) Realizar consultas
UPDATE (^) Modificar datos
Tabla 8.3. Permisos en MySQL.
8.3 Seguridad en MySQL
Caso práctico
Vas a abrir la base de datos pruebas al exterior,
dejando la posibilidad de consultar información a
cualquier usuario.
Se pide
En la base de datos pruebas , crea un usuario con nombre invi- tado y contraseña hola que pueda acceder a cualquier tabla de la base de datos, pero sólo para realizar consultas.
Solución
Habilitaremos al usuario invitado para realizar consultas (per- miso SELECT) mediante la instrucción GRANT, que al mismo tiempo lo dará de alta. La ejecución del comando puede apre- ciarse en la Figura 8.38.
A continuación, saldremos de MySQL y volveremos a entrar como invitado, nos conectaremos a la base de datos pruebas y realizaremos una consulta sobre la tabla asignatura. El resul- tado de este proceso puede observarse en la Figura 8.39.
Seguidamente, trataremos de añadir una línea a la tabla asig- natura , la misma que acabamos de consultar. El resultado de este intento puede apreciarse en la Figura 8.40.
Aunque el mensaje de error no aclara del todo la situación, está claro que el sistema no permite que el usuario invitado inserte filas nuevas en la tabla. Esto obedece a que el único permiso que se le ha otorgado a dicho usuario es SELECT, y este permiso sólo le habilita para realizar consultas similares a la que ha efectuado en la Figura 8.38.
4
Fig. 8.38. Creación del usuario invitado.
Fig. 8.39. El usuario invitado realiza una consulta.
Fig. 8.40. El usuario invitado no puede insertar filas.
Consultar permisos
Para obtener una lista de los usuarios, debemos conec- tarnos a la base de datos mysql y ejecutar la siguiente consulta: mysql> select user from user;.
Así, obtendremos una lista con todos los usuarios defi- nidos. Para saber con qué bases de datos puede traba- jar un usuario y con qué permisos, usaremos SHOW GRANTS FOR usuario.
Supongamos que deseamos localizar al usuario invitado y consultar sus permisos en las diferentes bases de datos creadas. En la Figura 8.41 puede apreciarse el resultado del proceso. Fig. 8.41. Consulta de los permisos de un usuario.
