Archivo
Ejecutar consultas de larga duración en phpMyAdmin
En ocasiones queremos ejecutar una consulta utilizando phpMyAdmin cuya ejecución es demasiado larga en el tiempo, ya sea porque es muy compleja (o no está muy bien optimizada) o porque nuestro equipo es muy lento.
Ya sé que lo mejor es recurrir al terminal y desde la consola de mysql proceder a la ejecución, pero esto no es lo que quiero.
Nos debemos ir a la configuración de phpMyAdmin y modificar (o añadir) una línea.
Buscamos la carpeta de phpMyAdmin (si utilizamos XAMPP estará en algo como c:\xampp\phpMyAdmin) y dentro de la misma editamos el fichero config.inc.php
Buscamos la línea
$cfg['ExecTimeLimit']=300;
y le insertamos el valor 0 para no tener límite:
$cfg['ExecTimeLimit']=0;
Además, deberíamos modificar el entorno de ejecución de PHP, para ello, nos vamos a la carpeta de la instalación de PHP y abrimos el fichero php.ini
Modificamos las siguientes líneas:
max_execution_time = 300;
max_input_time = 600;
memory_limit = 128M;
Incluso la siguiente:
post_max_size = 80M;
Reiniciamos Apache y volvemos a probar la consulta.
Ahora todo funcionará y sí nos mostrará los resultados de la consulta (aunque tarde mucho).
.
Exportar consulta MySQL a CSV
Hay ocasiones en las que una consulta que ejecutas sobre phpMyAdmin es demasiado «lenta» y los resultados no se muestran, por lo que no puedes exportarlos a ningún otro formato.
Podrías cambiar la configuración de phpMyAdmin para que soportara resultados más largos, pero para no perder tiempo, podemos irnos a la consola y ejecutar desde ahí la exportación.
Tenemos dos opciones:
– Ejecutar desde la consola de MySQL
– Ejecutar desde el propio terminal MSDOS.
Desde la consola de MySQL:
Una vez que hemos accedido a la consola, nos hemos identificado correctamente y hemos conectado con la base de datos correcta, podríamos ejecutar la siguiente consulta:
SELECT campos
FROM tabla
WHERE condicion
INTO OUTFILE ‘fichero.csv’
FIELDS TERMINATED BY ‘;’
OPTIONALLY ENCLOSED BY ‘\»‘
LINES TERMINATED BY ‘\r\n’;
Gracias a lo anterior generamos un fichero CSV (no incluye los nombres de los campos en la primera fila) que separa los campos por ; y los registros por Intros. El texto de los campos se mete entre «.
El fichero será generado en la carpeta «mysql/data/».
Desde el propio terminal:
Directamente ejecutamos
mysql -h localhost -u root –password=laquesea mibasededatos \ -e «SELECT * FROM tabla where ….» -B > fichero_salida.csv
Inyección de SQL
Básicamente queremos que no nos la cuelen y no nos borren todas las tablas de nuestra base de datos utilizando el GET o utilizando un formulario.
¿Cómo consiguen hacer esto?
Imagina un formulario de USUARIO y CONTRASEÑA.
Imagina que en cualquiera de las dos cajas de texto escribe alguien:
pedrito’; drop table…
tampoco voy a poner toda la sentencia.
Si lo piensas, en tu código tendrás una consulta tal como:
select * from usuarios where nombre_usuario='.$usuario.'....;
Efectivamente, te han borrado las tablas.
Bueno, para evitar todo esto la solución es utilizar:
mysqli_real_escape_string($this->link, $usuario) en lugar del $usuario de antes.
mysqli::real_escape_string — mysqli_real_escape_string — Escapa los caracteres especiales de una cadena para usarla en una sentencia SQL, tomando en cuenta el conjunto de caracteres actual de la conexión.
Esta función se usa para crear una cadena SQL legal que se puede usar en una sentencia SQL. La cadena dada es codificada a una cadena SQL escapada, tomando en cuenta el conjunto de caracters actual de la conexión.
Los caracteres codifcados son NUL (ASCII 0), \n, \r, \, ‘, «, y Control-Z
Ejemplo:
<?php
$mysqli = new mysqli("localhost", "mi_usuario", "mi_contraseña", "world");
/* verificar la conexión */
if (mysqli_connect_errno()) {
printf("Falló la conexión: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TEMPORARY TABLE miCiudad LIKE City");
$ciudad = "'s Hertogenbosch";
/* esta consulta fallará debido a que no escapa $ciudad */
if (!$mysqli->query("INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
printf("Error: %s\n", $mysqli->sqlstate);
}
$ciudad = $mysqli->real_escape_string($ciudad);
/* esta consulta con $ciudad escapada funcionará */
if ($mysqli->query("INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
printf("%d fila insertada.\n", $mysqli->affected_rows);
}
$mysqli->close();
?>
Agrupamiento de una consulta de Unión
Estaba trabajando en alguna consulta de SQL sobre MySQL levemente compleja. Cogía datos de 3 tablas y generaba, mediante una UNION una serie de datos. A estos necesitaba hacerles un GROUP BY.
¿Cómo agrupar el resultado de una consulta de Unión?
Ejemplo: (SELECT id_accv, round(x) as X, round(y) as Y from accv where X is not NULL) UNION (SELECT id_accv, round(direccion.x) as X, round(direccion.y) as Y from accv, direccion where accv.X is NULL and fid_codvia2 is NULL and accv.fid_codvia = direccion.fid_codvia and accv.portal = direccion.acctexto) UNION (SELECT id_accv, round(interseccion.x) as X, round(interseccion.y) as Y from accv, interseccion where accv.X is NULL and accv.fid_codvia2 is not NULL and accv.fid_codvia = interseccion.fid_codvia and accv.fid_codvia2 = interseccion.fid_codvia2)
Con la anterior consulta consigo un listado de ID con sus coordenadas X e Y correspondientes.
Ahora, ¿cómo consigo agrupar los puntos y saber cuántos ID hay en cada punto?
Es fácil, hago un ALIAS de esta consulta dentro de la cláusula FROM de la consulta de agrupamiento. El resultado sería:
SELECT count(id_accv), x, y from ( (SELECT id_accv, round(x) as X, round(y) as Y from accv where year(fechaaccidente)=2013 and X is not NULL) UNION (SELECT id_accv, round(direccion.x) as X, round(direccion.y) as Y from accv, direccion where year(fechaaccidente)=2013 and accv.X is NULL and accv.fid_codvia2 is NULL and accv.fid_codvia = direccion.fid_codvia and accv.portal = direccion.acctexto) UNION (SELECT id_accv, round(interseccion.x) as X, round(interseccion.y) as Y from accv, interseccion where year(fechaaccidente)=2013 and accv.X is NULL and accv.fid_codvia2 is not NULL and accv.fid_codvia = interseccion.fid_codvia and accv.fid_codvia2 = interseccion.fid_codvia2) ) as Tablauni group by X, Y
Ejercicio: Piezas y Proveedores
- Crea la base de datos Suministros
- Crea las 3 tablas anteriores
- Inserta al menos 5 piezas y 5 proveedores (uno de ellos debe tener código HAL). También inserta en la tabla Suministra al menos 10 filas.
- Realiza consultas select para obtener:
- Nombres de todos los artículos
- Todos los datos de los proveedores
- Precio medio de todas las piezas
- Nombre de los proveedores que suministran la pieza 1.
- Nombres de piezas suministradas por proveedor cuyo código es HAL
- Nombre del proveedor que suministra la pieza más cara
- Aumentar los precios en una unidad
- Hacer constar que la empresa «Tornillos SL» (con código TOSL) va a comenzar a suministrarnos tornillos a 7 pesetas.
- Nombre de las piezas que suministra «Tornillos SL»
- Nombre del proveedor y número de piezas que suministra.
- Nombre de las piezas cuyo precio medio sea mayor a 10 euros y menor de 20.
- Diferencia entre el producto más caro y el más barato.
- Ordena los proveedores por nombre ascendentemente y muestra sólo los 3 primeros.
- Nombre de los proveedores que empiezan por A (si no tienes ninguno que empiece por A, utiliza otra letra para hacer la prueba).
..
CREATE TABLE SUMINISTRA(
codigopieza integer,
idproveedor varchar(4),
precio integer,
primary key (codigopieza,idproveedor),
key(codigopieza),
foreign key (codigopieza) references piezas(codigo)
on delete cascade on update cascade,
key(idproveedor),
foreign key (idproveedor) references proveedores(id)
on delete cascade on update cascade);
CONSULTAS:
SELECT * FROM PROVEEDORES;
SELECT AVG(PRECIO) FROM SUMINISTRA;
SELECT NOMBRE FROM PROVEEDORES, SUMINISTRA WHERE CODIGOPIEZA=1 AND
ID=IDPROVEEDOR;
SELECT NOMBRE FROM PIEZAS, SUMINISTRA WHERE IDPROVEEDOR='HAL' AND
CODIGOPIEZA=CODIGO;
SELECT NOMBRE FROM PROVEEDORES, SUMINISTRA WHERE IDPROVEEDOR=ID AND
PRECIO=(SELECT MAX(PRECIO) FROM SUMINISTRA);
UPDATE SUMINISTRA SET PRECIO=PRECIO+1;
INSERT INTO PROVEEDORES(ID,NOMBRE) VALUES ('TOSL', 'Tornillos SL');
INSERT INTO PIEZAS(CODIGO, NOMBRE) VALUES (17, 'Tornillo');
INSERT INTO SUMINISTRA VALUES (17, 'TOSL', 7);
SELECT PIEZAS.NOMBRE FROM PIEZAS, SUMINISTRA, PROVEEDORES
WHERE PROVEEDORES.NOMBRE='Tornillos SL' AND ID=IDPROVEEDOR AND
CODIGOPIEZA=CODIGO;
SELECT PROVEEDORES.NOMBRE, COUNT(*)
FROM PROVEEDORES, SUMINISTRA
WHERE IDPROVEEDOR=ID
GROUP BY IDPROVEEDOR;
SELECT NOMBRE
FROM PIEZAS, SUMINISTRA
WHERE CODIGO=CODIGOPIEZA
GROUP BY CODIGOPIEZA
HAVING AVG(PRECIO)>10 AND AVG(PRECIO)<20;
SELECT MAX(PRECIO) - MIN(PRECIO) AS DIFERENCIA
FROM SUMINISTRA;
SELECT NOMBRE
FROM PROVEEDORES
ORDER BY NOMBRE ASC
LIMIT 3
SELECT NOMBRE
FROM PROVEEDORES
WHERE NOMBRE LIKE 'A%';
Ejercicio: La tienda de Informática
- Crea la Base de datos «Tiendainformatica»
- Crea las dos tablas, fabricantes y artículos
- Inserta al menos 4 fabricantes y 10 artículos
- Realiza las siguientes consultas:
- Obtener los nombres de los productos de la tienda
- Obtener los nombres y los precios de los productos de la tienda
- Obtener el nombre de los productos cuyo precio sea menor o igual a 200 euros.
- Obtener todos los datos de los artículos cuyo precio esté entre 60 y 140 euros, ambos precios incluidos
- Obtener el nombre y el precio en pesetas (es decir, multiplicado por 166,386)
- Obtener el precio medio de todos los productos
- Precio medio de los artículos cuyo fabricante sea 2
- Número de artículos cuyo precio sea superior a 180 euros.
- Nombre y precio de los productos cuyo precio sea mayor o igual a 180 euros, ordenándolos descendentemente por precio y ascendentemente por nombre.
- Obtener un listado completo de artículos, incluyendo los datos del fabricante también
- Obtener un listado de artículos, incluyendo el nombre, precio y nombre de fabricante
- Obtener el precio medio de los productos de cada fabricante, mostrando solo los códigos de cada fabricante
- Hallar el precio medio de los productos de cada fabricante, mostrando el nombre del fabricante.
- Nombre de los fabricantes que ofrezcan productos cuyo precio medio sea superior a 150 euros
- Nombre y precio del artículo más barato.
- Añadir un nuevo producto: Altavoces de 70 euros del fabricante 2.
- Cambiar el nombre del producto 8 a Impresora Láser
- Aplicar un descuento del 10% a todos los productos
- Aplicar un descuento de 10 euros a todos los productos cuyo precio sea superior a 120 euros.
…. ALGUNAS CONSULTAS RESUELTAS:
SELECT * FROM ARTICULOS WHERE PRECIO BETWEEN 60 AND 140; SELECT * FROM ARTICULOS WHERE PRECIO>=60 AND PRECIO<=140; SELECT NOMBRE, PRECIO*166.386 AS PRECIOENPESETAS FROM ARTICULOS; SELECT AVG(PRECIO) FROM ARTICULOS; SELECT AVG(PRECIO) FROM ARTICULOS WHERE FABRICANTE=2; SELECT COUNT(CODIGO) FROM ARTICULOS WHERE PRECIO>180; SELECT NOMBRE, PRECIO FROM ARTICULOS WHERE PRECIO>=180; SELECT NOMBRE, PRECIO FROM ARTICULOS WHERE PRECIO>=180 ORDER BY PRECIO DESC, NOMBRE ASC; SELECT * FROM ARTICULOS, FABRICANTES WHERE ARTICULOS.FABRICANTE=FABRICANTES.CODIGO; SELECT ARTICULOS.NOMBRE, PRECIO, FABRICANTES.NOMBRE FROM ARTICULOS, FABRICANTES WHERE ARTICULOS.FABRICANTE=FABRICANTES.CODIGO; SELECT FABRICANTE, AVG(PRECIO) FROM ARTICULOS GROUP BY FABRICANTE; SELECT FABRICANTES.NOMBRE, AVG(PRECIO) FROM ARTICULOS, FABRICANTES WHERE ARTICULOS.FABRICANTE=FABRICANTES.CODIGO GROUP BY FABRICANTES.CODIGO; SELECT FABRICANTES.NOMBRE FROM FABRICANTES, ARTICULOS WHERE FABRICANTES.CODIGO=ARTICULOS.FABRICANTE GROUP BY FABRICANTES.CODIGO; HAVING AVG(PRECIO)>150; SELECT AVG(PRECIO), CIUDADES.NOMBRE FROM ARTICULOS, CIUDADES, FABRICANTES WHERE ARTICULOS.FABRICANTE=FABRICANTES.CODIGO AND FABRICANTES.CIUDAD=CIUDADES.CODIGO; SELECT NOMBRE, PRECIO FROM ARTICULOS WHERE PRECIO = (SELECT MIN(PRECIO) FROM ARTICULOS); UPDATE ARTICULOS SET NOMBRE='Impresora Láser' WHERE CODIGO=8; UPDATE ARTICULOS SET PRECIO=PRECIO-(PRECIO*0.1); UPDATE ARTICULOS SET PRECIO=PRECIO-10 WHERE PRECIO>120;
Importar y exportar datos
MySQL permite copiar tablas en diferentes formatos de texto, así como importar datos a partir de fichero de texto en diferentes formatos.
Esto se puede usar para exportar los datos de nuestras bases de datos a otras aplicaciones, o bien para importar datos desde otras fuentes a nuestras tablas. También se puede usar para hacer copias de seguridad y restaurarlas posteriormente.
Exportar a otros ficheros
Para extraer datos desde una base de datos a un fichero se usa la sentencia SELECT … INTO OUTFILE.
El resto de las cláusulas de SELECT siguen siendo aplicables, la única diferencia es que la salida de la selección se envía a un fichero en lugar de hacerlo a la consola.
La sintaxis de la parte INTO OUTFILE es:
[INTO OUTFILE 'file_name' export_options]
file_name es el nombre del fichero de salida. Ese fichero no debe existir, ya que en caso contrario la sentencia fallará.
En cuanto a las opciones de exportación son las mismas que para las cláusulas FIELDS y LINES de LOAD DATA. Su sintaxis es:
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
Estas cláusulas nos permiten crear diferentes formatos de ficheros de salida.
La cláusula FIELDS se refiere a las opciones de cada columna:
- TERMINATED BY ‘carácter’: nos permite elegir el carácter delimitador que se usará para separar cada columna. Por defecto, el valor que se usa es el tabulador, pero podemos usar ‘;’, ‘,’, etc.
- [OPTIONALLY] ENCLOSED BY ‘carácter’: sirve para elegir el carácter usado para entrecomillar cada columna. Por defecto no se entrecomilla ninguna columna, pero podemos elegir cualquier carácter. Si se añade la palabra OPTIONALLY sólo se entrecomillarán las columnas de texto y fecha.
- ESCAPED BY ‘carácter’: sirve para indicar el carácter que se usará para escapar aquellos caracteres que pueden dificultar la lectura posterior del fichero. Por ejemplo, si terminamos las columnas con ‘,’ y no las entrecomillamos, un carácter ‘,’ dentro de una columna de texto se interpretará como un separador de columnas. Para evitar esto se puede escapar esa coma con otro carácter. Por defecto se usa el carácter ‘\’.
La cláusula LINES se refiere a las opciones para cada fila:
- STARTING BY ‘carácter’: permite seleccionar el carácter para comenzar cada línea. Por defecto no se usa ningún carácter para ello.
- TERMINATED BY ‘carácter’: permite elegir el carácter para terminar cada línea. Por defecto es el retorno de línea, pero se puede usar cualquier otro carácter o caracteres, por ejemplo ‘\r\n’.
Por ejemplo, para obtener un fichero de texto a partir de la tabla ‘gente’, con las columnas delimitadas por ‘;’, entrecomillando las columnas de texto con ‘»‘ y separando cada fila por la secuencia ‘\r\n’, usaremos la siguiente sentecia:
mysql> SELECT * FROM gente
-> INTO OUTFILE "gente.txt"
-> FIELDS TERMINATED BY ';'
-> OPTIONALLY ENCLOSED BY '\"'
-> LINES TERMINATED BY '\n\r';
Query OK, 5 rows affected (0.00 sec)
mysql>
El fichero de salida tendrá este aspecto:
"Fulano";"1974-04-12" "Mengano";"1978-06-15" "Tulano";"2000-12-02" "Pegano";"1993-02-10" "Mengano";\N
La fecha para «Mengano» era NULL, para indicarlo se muestra el valor \N.
Importar a partir de ficheros externos
Por supuesto, el proceso contrario también es posible. Podemos leer el contenido de un fichero de texto en una tabla. El fichero origen puede haber sido creado mediante una sentecia SELECT … INTO OUTFILE, o mediante cualquier otro medio.
Para hacerlo disponemos de la sentencia LOAD DATA, cuya sintaxis más simple es:
LOAD DATA [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]
La cláusula LOCAL indica, si aparece, que el fichero está en el ordenador del cliente. Si no se especifica el fichero de texto se buscará en el servidor, concretamente en el mismo directorio donde esté la base de datos. Esto nos permite importar datos desde nuestro ordenador en un sistema en que el servidor de MySQL se encuentra en otra máquina.
Las cláusulas REPLACE e IGNORE afectan al modo en que se tratan las filas leídas que contengan el mismo valor para una clave principal o única para una fila existente en la tabla. Si se especifica REPLACE se sustituirá la fila actual por la leída. Si se especifica IGNORE el valor leído será ignorado.
La parte INTO TABLA tbl_name indica en qué tabla se insertarán los valores leídos.
No comentaremos mucho sobre las cláusulas FIELDS y LINES ya que su significado es el mismo que vimos para la sentencia SELECT … INTO OUTFILE. Estas sentencias nos permiten interpretar correctamente cada fila y cada columna, adaptándonos al formato del fichero de texto de entrada.
La misma utilidad tiene la cláusula IGNORE número LINES, que nos permite que las primeras número líneas no se interpreten como datos a importar. Es frecuente que los ficheros de texto que usaremos como fuente de datos contengan algunas cabeceras que expliquen el contenido del fichero, o que contengan los nombres de cada columna. Usando esta cláusula podemos ignorarlas.
La última parte nos permite indicar la columna a la que será asignada cada una de las columnas leídas, esto será útil si el orden de las columnas en la tabla no es el mismo que en el fichero de texto, o si el número de columnas es diferente en ambos.
Por ejemplo, supongamos que queremos añadir el contenido de este fichero a la tabla «gente»:
Fichero de datos de "gente" fecha,nombre 2004-03-15,Xulana 2000-09-09,Con Clase 1998-04-15,Pingrana
Como vemos, hay dos filas al principio que no contienen datos válidos, las columnas están separadas con comas y, como hemos editado el fichero con el «notepad», las líneas terminan con «\n\r». La sentencia adecuada para leer los datos es:
mysql> LOAD DATA INFILE "gente.txt"
-> INTO TABLE gente
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\r\n'
-> IGNORE 2 LINES
-> (fecha,nombre);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
El nuevo contenido de la tabla es:
mysql> SELECT * FROM gente; +-----------+------------+ | nombre | fecha | +-----------+------------+ | Fulano | 1974-04-12 | | Mengano | 1978-06-15 | | Tulano | 2000-12-02 | | Pegano | 1993-02-10 | | Mengano | NULL | | Xulana | 2004-03-15 | | Con Clase | 2000-09-09 | | Pingrana | 1998-04-15 | +-----------+------------+ 8 rows in set (0.00 sec) mysql>
Usuarios / Privilegios
Hasta ahora hemos usado sólo el usuario ‘root’, que es el administrador, y que dispone de todos los privilegios disponibles en MySQL.
Sin embargo, normalmente no será una buena práctica dejar que todos los usuario con acceso al servidor tengan todos los privilegios. Para conservar la integridad de los datos y de las estructuras será conveniente que sólo algunos usuarios puedan realizar determinadas tareas, y que otras, que requieren mayor conocimiento sobre las estructuras de bases de datos y tablas, sólo puedan realizarse por un número limitado y controlado de usuarios.
Los conceptos de usuarios y privilegios están íntimamente relacionados. No se pueden crear usuarios sin asignarle al mismo tiempo privilegios. De hecho, la necesidad de crear usuarios está ligada a la necesidad de limitar las acciones que tales usuarios pueden llevar a cabo.
MySQL permite definir diferentes usuarios, y además, asignar a cada uno determinados privilegios en distintos niveles o categorías de ellos.
Niveles de privilegios
En MySQL existen cinco niveles distintos de privilegios:
Globales: se aplican al conjunto de todas las bases de datos en un servidor. Es el nivel más alto de privilegio, en el sentido de que su ámbito es el más general.
De base de datos: se refieren a bases de datos individuales, y por extensión, a todos los objetos que contiene cada base de datos.
De tabla: se aplican a tablas individuales, y por lo tanto, a todas las columnas de esas tabla.
De columna: se aplican a una columna en una tabla concreta.
De rutina: se aplican a los procedimientos almacenados. Aún no hemos visto nada sobre este tema, pero en MySQL se pueden almacenar procedimietos consistentes en varias consultas SQL.
Crear usuarios
Aunque en la versión 5.0.2 de MySQL existe una sentencia para crear usuarios, CREATE USER, en versiones anteriores se usa exclusivamente la sentencia GRANT para crearlos.
En general es preferible usar GRANT, ya que si se crea un usuario mediante CREATE USER, posteriormente hay que usar una sentencia GRANT para concederle privilegios.
Usando GRANT podemos crear un usuario y al mismo tiempo concederle también los privilegios que tendrá. La sintaxis simplificada que usaremos para GRANT, sin preocuparnos de temas de cifrados seguros que dejaremos ese tema para capítulos avanzados, es:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
La primera parte priv_type [(column_list)] permite definir el tipo de privilegio concedido para determinadas columnas. La segunda ON {tbl_name | * | *.* | db_name.*}, permite conceder privilegios en niveles globales, de base de datos o de tablas.
Para crear un usuario sin privilegios usaremos la sentencia:
mysql> GRANT USAGE ON *.* TO anonimo IDENTIFIED BY 'clave'; Query OK, 0 rows affected (0.02 sec)
Hay que tener en cuenta que la constraseña se debe introducir entre comillas de forma obligatoria.
Un usuario ‘anonimo’ podrá abrir una sesión MySQL mediante una orden:
C:\mysql -h localhost -u anonimo -p
Pero no podrá hacer mucho más, ya que no tiene privilegios. No tendrá, por ejemplo, oportunidad de hacer selecciones de datos, de crear bases de datos o tablas, insertar datos, etc.
Conceder privilegios
Para que un usuario pueda hacer algo más que consultar algunas variables del sistema debe tener algún privilegio. Lo más simple es conceder el privilegio para seleccionar datos de una tabla concreta. Esto se haría así:
La misma sentencia GRANT se usa para añadir privilegios a un usuario existente.
mysql> GRANT SELECT ON prueba.gente TO anonimo; Query OK, 0 rows affected (0.02 sec)
Esta sentencia concede al usuario ‘anonimo’ el privilegio de ejecutar sentencias SELECT sobre la tabla ‘gente’ de la base de datos ‘prueba’.
Un usuario que abra una sesión y se identifique como ‘anonimo’ podrá ejecutar estas sentencias:
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | prueba | +----------+ 1 row in set (0.01 sec) mysql> USE prueba; Database changed mysql> SHOW TABLES; +------------------+ | Tables_in_prueba | +------------------+ | gente | +------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM gente; +----------+------------+ | nombre | fecha | +----------+------------+ | Fulano | 1985-04-12 | | Mengano | 1978-06-15 | | Tulano | 2001-12-02 | | Pegano | 1993-02-10 | | Pimplano | 1978-06-15 | | Frutano | 1985-04-12 | +----------+------------+ 6 rows in set (0.05 sec) mysql>
Como se ve, para este usuario sólo existe la base de datos ‘prueba’ y dentro de esta, la tabla ‘gente’. Además, podrá hacer consultas sobre esa tabla, pero no podrá añadir ni modificar datos, ni por supuesto, crear o destruir tablas ni bases de datos.
Para conceder privilegios globales se usa ON *.*, para indicar que los privilegios se conceden en todas las tablas de todas las bases de datos.
Para conceder privilegios en bases de datos se usa ON nombre_db.*, indicando que los privilegios se conceden sobre todas las tablas de la base de datos ‘nombre_db’.
Usando ON nombre_db.nombre_tabla, concedemos privilegios de nivel de tabla para la tabla y base de datos especificada.
En cuanto a los privilegios de columna, para concederlos se usa la sintaxis tipo_privilegio (lista_de_columnas), [tipo_privilegio (lista_de_columnas)].
Otros privilegios que se pueden conceder son:
- ALL: para conceder todos los privilegios.
- CREATE: permite crear nuevas tablas.
- DELETE: permite usar la sentencia DELETE.
- DROP: permite borrar tablas.
- INSERT: permite insertar datos en tablas.
- UPDATE: permite usar la sentencia UPDATE.
Para ver una lista de todos los privilegios existentes consultar la sintaxis de la sentencia GRANT.
Se pueden conceder varios privilegios en una única sentencia. Por ejemplo:
mysql> GRANT SELECT, UPDATE ON prueba.gente TO anonimo IDENTIFIED BY 'clave'; Query OK, 0 rows affected (0.22 sec) mysql>
Un detalle importante es que para crear usuarios se debe tener el privilegio GRANT OPTION, y que sólo se pueden conceder privilegios que se posean.
Revocar privilegios
Para revocar privilegios se usa la sentencia REVOKE.
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON
FROM user [, user] ...
La sintaxis es similar a la de GRANT, por ejemplo, para revocar el privilegio SELECT de nuestro usuario ‘anonimo’, usaremos la sentencia:
mysql> REVOKE SELECT ON prueba.gente FROM anonimo; Query OK, 0 rows affected (0.05 sec)
Mostrar los privilegios de un usuario
Podemos ver qué privilegios se han concedido a un usuario mediante la sentencia SHOW GRANTS. La salida de esta sentencia es una lista de sentencias GRANT que se deben ejecutar para conceder los privilegios que tiene el usuario. Por ejemplo:
mysql> SHOW GRANTS FOR anonimo; +--------------------------------------------------------------------+ | Grants for anonimo@% | +--------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'anonimo'@'%' IDENTIFIED BY PASSWORD '*5...' | | GRANT SELECT ON `prueba`.`gente` TO 'anonimo'@'%' | +--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
Nombres de usuarios y contraseñas
Como podemos ver por la salida de la sentencia SHOW GRANTS, el nombre de usuario no se limita a un nombre simple, sino que tiene dos partes. La primera consiste en un nombre de usuario, en nuestro ejemplo ‘anonimo’. La segunda parte, que aparece separada de la primera por el carácter ‘@’ es un nombre de máquina (host). Este nombre puede ser bien el de una máquina, por ejemplo, ‘localhost’ para referirse al ordenador local, o cualquier otro nombre, o bien una ip.
La parte de la máquina es opcional, y si como en nuestro caso, no se pone, el usuario podrá conectarse desde cualquier máquina. La salida de SHOW GRANTS lo indica usando el comodín ‘%’ para el nombre de la máquina.
Si creamos un usuario para una máquina o conjunto de máquinas determinado, ese usuario no podrá conectar desde otras máquinas. Por ejemplo:
mysql> GRANT USAGE ON * TO anonimo@localhost IDENTIFIED BY 'clave'; Query OK, 0 rows affected (0.00 sec)
Un usuario que se identifique como ‘anonimo’ sólo podrá entrar desde el mismo ordenador donde se está ejecutando el servidor.
En este otro ejemplo:
mysql> GRANT USAGE ON * TO anonimo@10.28.56.15 IDENTIFIED BY 'clave'; Query OK, 0 rows affected (0.00 sec)
El usuario ‘anonimo’ sólo puede conectarse desde un ordenador cuyo IP sea ‘10.28.56.15’.
Aunque asignar una constraseña es opcional, por motivos de seguridad es recomendable asignar siempre una.
La contraseña se puede escribir entre comillas simples cuando se crea un usuario, o se puede usar la salida de la función PASSWORD() de forma literal, para evitar enviar la clave en texto legible.
Si al añadir privilegios se usa una clave diferente en la cláusula IDENTIFIED BY, sencillamente se sustituye la contraseña por la nueva.
Borrar usuarios
Para eliminar usuarios se usa la sentencia DROP USER.
No se puede eliminar un usuario que tenga privilegios, por ejemplo:
mysql> DROP USER anonimo; ERROR 1268 (HY000): Can't drop one or more of the requested users mysql>
Para eliminar el usuario primero hay que revocar todos sus privilegios:
mysql> SHOW GRANTS FOR anonimo; +--------------------------------------------------------------------+ | Grants for anonimo@% | +--------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'anonimo'@'%' IDENTIFIED BY PASSWORD '*5...' | | GRANT SELECT ON `prueba`.`gente` TO 'anonimo'@'%' | +--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> REVOKE SELECT ON prueba.gente FROM anonimo; Query OK, 0 rows affected (0.00 sec) mysql> DROP USER anonimo; Query OK, 0 rows affected (0.00 sec) mysql>
Uniones
ambién es posible realizar la operación de álgebra relacional unión entre varias tablas o proyecciones de tablas.
Para hacerlo se usa la sentencia UNION que permite combinar varias sentencias SELECT para crear una única tabla de salida.
Las condiciones para que se pueda crear una unión son las mismas que vimos al estudiar el álgebra relacional: las relaciones a unir deben tener el mismo número de atributos, y además deben ser de dominios compatibles.
Veamos un ejemplo:
mysql> CREATE TABLE stock1 (
-> id INT NOT NULL,
-> nombre VARCHAR(30),
-> cantidad INT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE stock2 (
-> id INT NOT NULL,
-> nombre VARCHAR(40),
-> cantidad SMALLINT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.16 sec)
mysql> CREATE TABLE stock3 (
-> id INT NOT NULL,
-> nombre VARCHAR(35),
-> numero MEDIUMINT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO stock1 VALUES
-> (1, "tornillo M3x12", 100),
-> (2, "tornillo M3x15", 120),
-> (3, "tornillo M4x25", 120),
-> (4, "tornillo M5x30", 200);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO stock2 VALUES
-> (10, "tuerca M4", 120),
-> (11, "tuerca M3", 100),
-> (12, "tuerca M5", 87);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO stock3 VALUES
-> (20, "varilla 10", 23),
-> (1, "tornillo M3x12", 22),
-> (21, "varilla 12", 32),
-> (11, "tuerca M3", 22);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
Podemos crear una unión de las tres tablas, a pesar de que los nombres y tamaños de algunas columnas sean diferentes:
mysql> SELECT * FROM stock1 UNION
-> SELECT * FROM stock2 UNION
-> SELECT * FROM stock3;
+----+----------------+----------+
| id | nombre | cantidad |
+----+----------------+----------+
| 1 | tornillo M3x12 | 100 |
| 2 | tornillo M3x15 | 120 |
| 3 | tornillo M4x25 | 120 |
| 4 | tornillo M5x30 | 200 |
| 10 | tuerca M4 | 120 |
| 11 | tuerca M3 | 100 |
| 12 | tuerca M5 | 87 |
| 1 | tornillo M3x12 | 22 |
| 11 | tuerca M3 | 22 |
| 20 | varilla 10 | 23 |
| 21 | varilla 12 | 32 |
+----+----------------+----------+
11 rows in set (0.00 sec)
mysql>
El resultado se puede ordenar usando ORDER BY y también podemos seleccionar un número limitado de filas mediante LIMIT:
mysql> (SELECT * FROM stock1) UNION
-> (SELECT * FROM stock2) UNION
-> (SELECT * FROM stock3) ORDER BY id LIMIT 6;
+----+----------------+----------+
| id | nombre | cantidad |
+----+----------------+----------+
| 1 | tornillo M3x12 | 100 |
| 1 | tornillo M3x12 | 22 |
| 2 | tornillo M3x15 | 120 |
| 3 | tornillo M4x25 | 120 |
| 4 | tornillo M5x30 | 200 |
| 10 | tuerca M4 | 120 |
+----+----------------+----------+
6 rows in set (0.00 sec)
mysql>
Dentro de cada sentencia SELECT se aplican todas las cláusulas, proyecciones y selecciones que se quiera, como en cualquier SELECT normal.
La sintaxis completa incluye dos modificadores:
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
Los modificadores ALL y DISTINCT son opcionales, y si no se usa ninguno el comportamiento es el mismo que si se usa DISTINCT.
Con ALL se muestran todas las filas, aunque estén repetidas, con DISTINCT sólo se muestra una copia de cada fila:
mysql> SELECT id,nombre FROM stock1 UNION
-> SELECT id,nombre FROM stock2 UNION
-> SELECT id,nombre FROM stock3;
+----+----------------+
| id | nombre |
+----+----------------+
| 1 | tornillo M3x12 |
| 2 | tornillo M3x15 |
| 3 | tornillo M4x25 |
| 4 | tornillo M5x30 |
| 10 | tuerca M4 |
| 11 | tuerca M3 |
| 12 | tuerca M5 |
| 20 | varilla 10 |
| 21 | varilla 12 |
+----+----------------+
9 rows in set (0.00 sec)
mysql> SELECT id,nombre FROM stock1 UNION ALL
-> SELECT id,nombre FROM stock2 UNION ALL
-> SELECT id,nombre FROM stock3;
+----+----------------+
| id | nombre |
+----+----------------+
| 1 | tornillo M3x12 |
| 2 | tornillo M3x15 |
| 3 | tornillo M4x25 |
| 4 | tornillo M5x30 |
| 10 | tuerca M4 |
| 11 | tuerca M3 |
| 12 | tuerca M5 |
| 1 | tornillo M3x12 |
| 11 | tuerca M3 |
| 20 | varilla 10 |
| 21 | varilla 12 |
+----+----------------+
11 rows in set (0.00 sec)
mysql>
Sobre el resultado final no se pueden aplicar otras cláusulas como GROUP BY.
Composiciones externas
Composiciones externas
Al contrario que con las composiciones internas, las externas no proceden de un producto cartesiano. Por lo tanto, en estas pueden aparecer tuplas que no aparecen en el producto cartesiano.
Para hacer una composición externa se toman las tuplas de una de las tablas una a una y se combinan con las tuplas de la otra.
Como norma general se usa un índice para localizar las tuplas de la segunda tabla que cumplen la condición, y para cada tupla encontrada se añade una fila a la tabla de salida.
Si no existe ninguna tupla en la segunda tabla que cumpla las condiciones, se combina la tupla de la primera con una nula de la segunda.
En nuestro ejemplo se tomaría la primera tupla de personas2, con un valor de id igual a 1, y se busca en la tabla telefonos2 las tuplas con un valor de id igual a 1. Lo mismo para la segunda tupla, con id igual a 2.
En la tercera el id es 3, y no existe ninguna tupla en telefonos2 con un valor de id igual a 3, por lo tanto se combina la tupla de personas2 con una tupla de telefonos2 con todos los atributos igual a NULL.
Por ejemplo:
mysql> SELECT * FROM personas2 LEFT JOIN telefonos2 USING(id); +----+-----------+------------+-----------+------+ | id | nombre | fecha | numero | id | +----+-----------+------------+-----------+------+ | 1 | Fulanito | 1956-12-14 | 123456789 | 1 | | 1 | Fulanito | 1956-12-14 | 145654854 | 1 | | 1 | Fulanito | 1956-12-14 | 152452545 | 1 | | 2 | Menganito | 1975-10-15 | 254254254 | 2 | | 3 | Tulanita | 1985-03-17 | NULL | NULL | (1) | 4 | Fusganita | 1976-08-25 | 456545654 | 4 | | 4 | Fusganita | 1976-08-25 | 441415414 | 4 | +----+-----------+------------+-----------+------+ 7 rows in set (0.05 sec) mysql>
La quinta fila (1), tiene valores NULL para numero e id de telefonos2, ya que no existen tuplas en esa tabla con un valor de id igual a 3.
Las sintaxis para composiciones externas son:
referencia_tabla LEFT [OUTER] JOIN referencia_tabla [join_condition] referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla referencia_tabla RIGHT [OUTER] JOIN referencia_tabla [condición] referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla
La condición puede ser:
ON expresión_condicional | USING (lista_columnas)
La palabra OUTER es opcional.
Existen dos grupos de composiciones externas: izquierda y derecha, dependiendo de cual de las tablas se lea en primer lugar.
Composición externa izquierda
En estas composiciones se recorre la tabla de la izquierda y se buscan tuplas en la de la derecha. Se crean usando la palabra LEFT (izquierda, en inglés).
Las sintaxis para la composición externa izquierda es:
referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]
Veamos un ejemplo. Para empezar, crearemos un par de tablas:
mysql> CREATE TABLE tabla1 (
-> id INT NOT NULL,
-> nombre CHAR(10),
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.42 sec)
mysql> CREATE TABLE tabla2 (
-> id INT NOT NULL,
-> numero INT,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.11 sec)
mysql>
E insertaremos algunos datos:
mysql> INSERT INTO tabla1 VALUES
-> (5, "Juan"),
-> (6, "Pedro"),
-> (7, "José"),
-> (8, "Fernando");
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO tabla2 VALUES
-> (3, 30),
-> (4, 40),
-> (5, 50),
-> (6, 60);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
La composición izquierda sería:
mysql> SELECT * FROM tabla1 LEFT JOIN tabla2 USING(id); +----+----------+------+--------+ | id | nombre | id | numero | +----+----------+------+--------+ | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | | 7 | José | NULL | NULL | | 8 | Fernando | NULL | NULL | +----+----------+------+--------+ 4 rows in set (0.00 sec) mysql>
Se puede ver que aparecen dos filas con valores NULL, para los id 7 y 8.
En contraposición, una composición interna dará esta salida:
mysql> SELECT * FROM tabla1 JOIN tabla2 USING(id); +----+--------+----+--------+ | id | nombre | id | numero | +----+--------+----+--------+ | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | +----+--------+----+--------+ 2 rows in set (0.06 sec) mysql>
Composición externa derecha
En este caso se recorre la tabla de la derecha y se buscan tuplas que cumplan la condición en la tabla izquierda.
La sintaxis es equivalente:
referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]
Usando las mismas tablas que en el ejemplo anterior:
mysql> SELECT * FROM tabla1 RIGHT JOIN tabla2 USING(id); +------+--------+----+--------+ | id | nombre | id | numero | +------+--------+----+--------+ | NULL | NULL | 3 | 30 | | NULL | NULL | 4 | 40 | | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | +------+--------+----+--------+ 4 rows in set (0.00 sec) mysql>
Es lo mismo usar una composición derecha de las tablas tabla1 y tabla2 que una composición izquierda de las tablas tabla2 y tabla1. Es decir, la consulta anterior es equivalente a esta otra:
mysql> SELECT * FROM tabla2 LEFT JOIN tabla1 USING(id);
Composiciones naturales externas
Por supuesto, también podemos hacer composiciones externas naturales:
referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla
El problema es que si existen tuplas añadidas con respecto a la composición interna, no se eliminará ninguna columna. Los mismos ejemplos anteriores, como composiciones naturales externas serían:
mysql> SELECT * FROM tabla1 NATURAL LEFT JOIN tabla2; +----+----------+------+--------+ | id | nombre | id | numero | +----+----------+------+--------+ | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | | 7 | José | NULL | NULL | | 8 | Fernando | NULL | NULL | +----+----------+------+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM tabla1 NATURAL RIGHT JOIN tabla2; +------+--------+----+--------+ | id | nombre | id | numero | +------+--------+----+--------+ | NULL | NULL | 3 | 30 | | NULL | NULL | 4 | 40 | | 5 | Juan | 5 | 50 | | 6 | Pedro | 6 | 60 | +------+--------+----+--------+ 4 rows in set (0.00 sec) mysql>
Leitmotiv
Dalek-free
Camisetas cargadas de adrenalina. Llénalas de aventura
CursoProgramador 