Archivo
Trabajar en MySQL/MariaDB con un campo varchar en lugar de date
Sí, hay veces que cometes el error de importar datos desde un CSV, un TXT o un XLS a una BD MySQL/MariaDB y no te das cuenta de que has almacenado un campo, que en realidad es una fecha, como una cadena de caracteres.
Es decir, tengo mi campo «Cumpleaños» como Varchar en lugar de Date.
Cuando comiences a hacer consultas te darás cuenta de que tienes un problema, sobre todo cuando empieces a consultar por años, meses, días de la semana, etc.
Lo lógico es hacer un ALTER TABLE seguido de un UPDATE, generando un campo nuevo, ahora sí como date y llenándolo de los valores que inicialmente almacenaste en el varchar, pero hay veces que ya no se puede. Imposible. Porque has desarrollado ya todo y no puedes cambiar la estructura de la BD.
Chapuza para solucionarlo: la función STR_TO_DATE
Esta función recibe 2 parámetros, el nombre del campo y el patrón que sigue la fecha almacenada en esa cadena.
Ejemplo:
select sum(aviones) as 'Número de viajes', year(STR_TO_DATE(fechaida, '%d/%m/%Y')) as Año from viajes group by Año
Lo único «complicado» es el patrón. Tienes que ponerlo exactamente como está almacenado en la base de datos en la cadena de caracteres. En el ejemplo sería algo como:
20/05/2017
.
Pasar a DATE un campo VARCHAR en MySQL/MariaDB
Nos enfrentamos a lo siguiente:
Por ejemplo, hemos importado un fichero CSV a MySQL/MariaDB utilizando phpMyAdmin. Este proceso de importación funcionó perfectamente, pero, como no habíamos creado la estructura de la tabla previamente, los campos de tipo fecha nos los ha generado como cadena en la base de datos. Es decir, tengo varios campos VARCHAR y quiero convertirlos en tipo DATE.
En primer lugar nos creamos un campo tipo DATE, sobre el que haremos el UPDATE.
Si nuestro campo VARCHAR tiene el formato: 07/05/2017 (es lo típico que nos pasará si importamos desde un CSV), tenemos que convertirlo en 2017-05-07 para que funcione correctamente.
La sentencia UPDATE sería algo tal que así:
UPDATE `mitabla` SET `campofecha` = CONCAT(SUBSTRING(`campocadena`,7,4),’-‘,SUBSTRING(`campocadena`,4,2),’-‘,SUBSTRING(`campocadena`,1,2)) WHERE 1
Posteriormente, eliminamos el campocadena y renombramos el campofecha para que tenga el nombre primitivo.
UPDATE (Modificación de un registro de una tabla)
De las actividades con tablas esta es la más larga. Vamos a resolverlo implementando tres páginas, la primera un formulario de consulta del mail de un alumno, la segunda otro formulario que nos permita cargar su mail modificado y la última registrará el cambio en la tabla.
El formulario de consulta del mail del alumno es similar a problemas anteriores:
<html>
<head>
<title>Problema</title>
</head>
<body>
<form action="pagina2.php" method="post">
Ingrese el mail del alumno:
<input type="text" name="mail"><br>
<input type="submit" value="buscar">
</form>
</body>
</html>
La segunda página es la más interesante y con conceptos nuevos:
<html>
<head>
<title>Problema</title>
</head>
<body>
<?php
$conexion=mysql_connect("localhost","root","") or
die("Problemas en la conexion");
mysql_select_db("base1",$conexion) or
die("Problemas en la selección de la base de datos");
$registros=mysql_query("select * from alumnos
where mail='$_REQUEST[mail]'",$conexion) or
die("Problemas en el select:".mysql_error());
if ($reg=mysql_fetch_array($registros))
{
?>
<form action="pagina3.php" method="post">
Ingrese nuevo mail:
<input type="text" name="mailnuevo" value="<?php echo $reg['mail'] ?>">
<br>
<input type="hidden" name="mailviejo" value="<?php echo $reg['mail'] ?>">
<input type="submit" value="Modificar">
</form>
<?php
}
else
echo "No existe alumno con dicho mail";
?>
</body>
</html>
Lo primero que podemos observar es que si el if se verifica verdadero se ejecuta un bloque que contiene código HTML:
if ($reg=mysql_fetch_array($registros))
{
?>
<form action="pagina3.php" method="post">
Ingrese nuevo mail:
<input type="text" name="mailnuevo" value="<?php echo $reg['mail'] ?>">
<br>
<input type="hidden" name="mailviejo" value="<?php echo $reg['mail'] ?>">
<input type="submit" value="Modificar">
</form>
<?php
}
Es decir que podemos disponer bloques de PHP dispersos dentro de la página.
Otro concepto importante es como enviar el mail del primer formulario a la tercer página, esto se logra con los controles de tipo «hidden», este tipo de control no se visualiza en el formulario pero se envía al presionar el botón submit.
Si queremos que el control text se cargue con el mail ingresado en el formulario anterior debemos cargar la propiedad value con dicho valor:
<input type="text" name="mailnuevo" value="<?php echo $reg['mail'] ?>">
Por último la pagina3.php es la que efectúa la modificación de la tabla propiamente dicha. Con el mail ingresado en la pagina1.php, el mail modificado en la pagina2.php se efectúa el update.
<html>
<head>
<title>Problema</title>
</head>
<body>
<?php
$conexion=mysql_connect("localhost","root","") or
die("Problemas en la conexion");
mysql_select_db("base1",$conexion) or
die("Problemas en la selección de la base de datos");
$registros=mysql_query("update alumnos
set mail='$_REQUEST[mailnuevo]'
where mail='$_REQUEST[mailviejo]'",$conexion) or
die("Problemas en el select:".mysql_error());
echo "El mail fue modificado con exito";
?>
</body>
</html>
Tengamos en cuenta que el segundo formulario nos envía dos datos: $_REQUEST[mailnuevo] y $_REQUEST[mailviejo].
Pasar una BD de Access a MySQL con phpMyAdmin
De manera práctica vamos a ver cómo podemos pasar una BD completa realizada en Access a MySQL utilizando phpMyAdmin.
Si entramos en phpMyAdmin podemos utilizar la opción Importar dentro del menú Más:
Aparece la siguiente página:
Como se puede ver, soporta ficheros en formato SQL, CSV, ODS y XML.
En esta ocasión vamos a utilizar ODS. ¿Por qué? El formato ODS va a ser capaz de leer las tildes y ñ sin problema.
ODS es un formato abierto que utiliza Openoffice.org. Para obtener este formato, debemos abrir nuestro fichero de Access, sobre cada una de las tablas, exportarla a formato Excel (que es el más parecido a ODS).
Una vez obtenido el fichero Excel, lo abrimos. Tras abrirlo, utilizaremos Guardar Como para obtener el fichero ODS.
Ya tenemos generado nuestro fichero ODS.
Ahora volvemos a phpMyAdmin y abrimos la opción IMPORTAR.
Pulsamos el botón Examinar y seleccionamos el fichero ODS anteriormente generado. Veremos cómo automáticamente cambia el Formato del fichero a Hoja de Cálculo de Open Document.
Antes de pulsar el botón Continuar, seleccionaremos el Check La primer línea del archivo contiene los nombres de columna de la tabla.
Si todo ha ido bien, aparecerá un mensaje que nos informará de cuántas estructuras se han creado y cuántas consultas se han ejecutado.
Nos ha creado una base de datos que se llama ODS_DB, podemos cambiarle el nombre.
Nos ha creado una tabla cuyo nombre es igual al nombre del fichero ODS.
Las tablas se han importado casi correctamente. Los nombres de los campos los ha cogido perfectamente, pero nos faltarían algunos detalles.
En primer lugar, la llave primaria, seleccionamos el campo o campos que sean llave primaria (en este ejemplo, el campo ID) y en la parte de abajo de la página pulsamos el botón Primaria
La consulta que ejecuta es la siguiente:
ALTER TABLE `usuarios` ADD PRIMARY KEY ( `ID` ) ;
Otro problema que teníamos era con las fechas. En el ejemplo, tenemos el campo FECHA NACIMIENTO, que es una fecha, pero no está almacenado en MySQL como Date.
Cambiar simplemente el tipo del campo de Varchar a Date no funciona, ya que en el Varchar están almacenadas las fechas en el formato dd-mm-yyyy y ese formato no lo soporta MySQL.
Deberíamos crearnos un campo nuevo, de tipo Date. Tras crearlo, debemos realizar un UPDATE como el siguiente:
UPDATE usuarios SET fechanueva=STR_TO_DATE(fechanacimiento, ‘%Y/%m/%d’);
En el campo nuevo ya tendríamos los valores de manera correcta, así que el campo antiguo podemos borrarlo. Y el campo nuevo renombrarlo para que aparezca con el nombre correcto.
Algo parecido pasa con los campos BOOLEANOS, no los coge correctamente y almacena valores de tipo cadena VERDADERO y FALSO.
Así, deberíamos crear un campo nuevo, de tipo BOOLEAN, y actualizarlo utilizando la siguiente consulta:
UPDATE usuarios SET fumadornuevo=true where fumador=’VERDADERO’;
UPDATE usuarios SET fumadornuevo=false where fumador=’FALSO’;
Con esto tendríamos todo terminado para una única tabla. Si nuestra Base de Datos de Access tiene varias tablas, con llaves externas, tendremos que ir importando todos los ficheros generados para cada tabla.
..
UPDATE Actualizar filas
Actualizar filas
Podemos modificar valores de las filas de una tabla usando la sentencia UPDATE. En su forma más simple, los cambios se aplican a todas las filas, y a las columnas que especifiquemos.
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
Por ejemplo, podemos aumentar en un 10% la población de todas las ciudades de la tabla ciudad3 usando esta sentencia:
mysql> UPDATE ciudad3 SET poblacion=poblacion*1.10; Query OK, 5 rows affected (0.15 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> SELECT * FROM ciudad3; +---------+-----------+ | nombre | poblacion | +---------+-----------+ | Berlín | 6600000 | | Londres | 11000000 | | Madrid | 7920000 | | París | 10120000 | | Roma | 10450000 | +---------+-----------+ 5 rows in set (0.00 sec) mysql>
Podemos, del mismo modo, actualizar el valor de más de una columna, separandolas en la sección SET mediante comas:
mysql> UPDATE ciudad5 SET clave=clave+10, poblacion=poblacion*0.97; Query OK, 4 rows affected (0.05 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> SELECT * FROM ciudad5; +-------+--------+-----------+ | clave | nombre | poblacion | +-------+--------+-----------+ | 11 | Madrid | 6790000 | | 12 | París | 8730000 | | 13 | Berlín | 3395000 | | 14 | Roma | 7760000 | +-------+--------+-----------+ 4 rows in set (0.00 sec) mysql>
En este ejemplo hemos incrementado el valor de la columna ‘clave’ en 10 y disminuido el de la columna ‘poblacion’ en un 3%, para todas las filas.
Pero no tenemos por qué actualizar todas las filas de la tabla. Podemos limitar el número de filas afectadas de varias formas.
La primera es mediante la cláusula WHERE. Usando esta cláusula podemos establecer una condición. Sólo las filas que cumplan esa condición serán actualizadas:
mysql> UPDATE ciudad5 SET poblacion=poblacion*1.03
-> WHERE nombre='Roma';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM ciudad5;
+-------+--------+-----------+
| clave | nombre | poblacion |
+-------+--------+-----------+
| 11 | Madrid | 6790000 |
| 12 | París | 8730000 |
| 13 | Berlín | 3395000 |
| 14 | Roma | 7992800 |
+-------+--------+-----------+
4 rows in set (0.00 sec)
mysql>
En este caso sólo hemos aumentado la población de las ciudades cuyo nombre sea ‘Roma’. Las condiciones pueden ser más complejas. Existen muchas funciones y operadores que se pueden aplicar sobre cualquier tipo de columna, y también podemos usar operadores booleanos como AND u OR. Veremos esto con más detalle en otros capítulos.
Otra forma de limitar el número de filas afectadas es usar la cláusula LIMIT. Esta cláusula permite especificar el número de filas a modificar:
mysql> UPDATE ciudad5 SET clave=clave-10 LIMIT 2; Query OK, 2 rows affected (0.05 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> SELECT * FROM ciudad5; +-------+--------+-----------+ | clave | nombre | poblacion | +-------+--------+-----------+ | 1 | Madrid | 6790000 | | 2 | París | 8730000 | | 13 | Berlín | 3395000 | | 14 | Roma | 7992800 | +-------+--------+-----------+ 4 rows in set (0.00 sec) mysql>
En este ejemplo hemos decrementado en 10 unidades la columna clave de las dos primeras filas.
Esta cláusula se puede combinar con WHERE, de modo que sólo las ‘n’ primeras filas que cumplan una determinada condición se modifiquen.
Sin embargo esto no es lo habitual, ya que, si no existen claves primarias o únicas, el orden de las filas es arbitrario, no tiene sentido seleccionarlas usando sólo la cláusula LIMIT.
La cláusula LIMIT se suele asociar a la cláusula ORDER BY. Por ejemplo, si queremos modificar la fila con la fecha más antigua de la tabla ‘gente’, usaremos esta sentencia:
mysql> UPDATE gente SET fecha="1985-04-12" ORDER BY fecha LIMIT 1; Query OK, 1 row affected, 1 warning (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> SELECT * FROM gente; +---------+------------+ | nombre | fecha | +---------+------------+ | Fulano | 1985-04-12 | | Mengano | 1978-06-15 | | Tulano | 2000-12-02 | | Pegano | 1993-02-10 | +---------+------------+ 4 rows in set (0.00 sec) mysql>
Si queremos modificar la fila con la fecha más reciente, usaremos el orden inverso, es decir, el descendente:
mysql> UPDATE gente SET fecha="2001-12-02" ORDER BY fecha DESC LIMIT 1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM gente; +---------+------------+ | nombre | fecha | +---------+------------+ | Fulano | 1985-04-12 | | Mengano | 1978-06-15 | | Tulano | 2001-12-02 | | Pegano | 1993-02-10 | +---------+------------+ 4 rows in set (0.00 sec) mysql>
Cuando exista una clave primaria o única, se usará ese orden por defecto, si no se especifica una cláusula ORDER BY.
Sintaxis de Update
UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
O:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
UPDATE actualiza columnas de filas existentes de una tabla con nuevos valores. La cláusula SET indica las columnas a modificar y los valores que deben tomar. La cláusula WHERE, si se da, especifica qué filas deben ser actualizadas. Si no se especifica, serán actualizadas todas ellas. Si se especifica la cláusula ORDER BY, las filas se modificarán en el orden especificado. La cláusula LIMIT establece un límite al número de filas que se pueden actualizar.
La sentencia UPDATE soporta los modificadores siguientes:
- Si se usa la palabra LOW_PRIORITY, la ejecución de UPDATE se retrasará hasta que no haya otros clientes haciendo lecturas de la tabla.
- Si se especifica IGNORE, la sentencia UPDATE no se abortará si se producen errores durante la actualización. Las filas con conflictos de claves duplicadas no se actualizarán. Las filas para las que la actualización de columnas se puedan producir errores de conversión se actualizarán con los valores válidos más próximos.
..
Tipos de sentencias SQL
En SQL tenemos bastantes sentencias que se pueden utilizar para realizar diversas tareas.
Dependiendo de las tareas, estas sentencias se pueden clasificar en tres grupos principales (DML, DDL,DCL), aunque nos quedaría otro grupo que a mi entender no está dentro del lenguaje SQL sino del PLSQL.
| SENTENCIA | DESCRIPCIÓN | ||
|
Recupera datos de la base de datos. Añade nuevas filas de datos a la base de datos. Suprime filas de datos de la base de datos. Modifica datos existentes en la base de datos. |
||
|
Añade una nueva tabla a la base de datos. Suprime una tabla de la base de datos. Modifica la estructura de una tabla existente. Añade una nueva vista a la base de datos. Suprime una vista de la base de datos. Construye un índice para una columna. Suprime el índice para una columna. Define un alias para un nombre de tabla. Suprime un alias para un nombre de tabla. |
||
|
Concede privilegios de acceso a usuarios. Suprime privilegios de acceso a usuarios Finaliza la transacción actual. |
||
|
Define un cursor para una consulta. Abre un cursor para recuperar resultados de consulta. Recupera una fila de resultados de consulta. Cierra un cursor. |
Componentes sintácticos
La mayoría de sentencias SQL tienen la misma estructura.
Todas comienzan por un verbo (select, insert, update, create), a continuación le sigue una o más clausulas que nos dicen los datos con los que vamos a operar (from, where), algunas de estas son opcionales y otras obligatorias como es el caso del from.
Consultas de actualización en Access
Las consultas de actualización son consultas que permiten modificar los datos almacenados en una tabla, modifican el contenido de los registros de una tabla. Se pueden modificar de golpe todos los registros de la tabla o sólo los que cumplan una determinado condición.
Para crear una consulta de actualización:
Abrimos una nueva consulta en vista diseño.
Añadimos la tabla que queremos actualizar.
Haz clic en el botón Actualizar de la pestaña Diseño:
A partir de ese momento la cuadrícula cambia de aspecto, han desaparecido las filas Orden: y Mostrar: por carecer de sentido aquí y en su lugar tenemos la fila Actualizar a: como puedes ver en el ejemplo que te ofrecemos a continuación:
- El Origen de la consulta puede ser una tabla, una consulta o una combinación de tablas.
- En la cuadrícula QBE solamente ponemos el campo o campos que intervienen en los criterios de búsqueda y los campos que se quieren actualizar.
- En la fila Actualizar a: escribimos la expresión que calcula el nuevo valor a asignar al campo.
La expresión puede ser un valor fijo, un nombre de campo del origen o cualquier expresión basada en campos del origen, también podría ser un parámetro.
Esta expresión debe generar un valor del tipo de dato apropiado para la columna indicada.
La expresión debe ser calculable a partir de los valores de la fila que se está actualizando.
- Si para el cálculo de la expresión se utiliza una columna que también se modifica, el valor que se utiliza es el antes de la modificación, lo mismo para la condición de búsqueda.
- Para que la actualización afecte a una parte de los registros de la tabla tendremos que seleccionar los registros a actualizar mediante un criterio de búsqueda. Si la consulta no incluye criterio de búsqueda se actualizarán todos los registros de la tabla. En nuestro ejemplo hemos incluido el criterio de búsqueda [Código curso] = 0, y en la fila Actualizar a: del campo [código curso] hemos puesto nulo, lo que significa que actualizará el campo código curso al valor nulo en los registros donde código curso sea igual a cero.
- Si actualizamos una columna definida como parte de una relación, esta columna se podrá actualizar o no siguiendo las reglas de integridad referencial. (Ver unidad 6)
- Para ver los datos que se modificarán antes de realizar la actualización podemos hacer clic sobre el tipo de vista Hoja de datos de la pestaña Inicio.
- Para ejecutar la consulta hacer clic sobre el icono . Al ejecutar la consulta se realizará la actualización de la tabla.
- Cuando el valor a dejar en el campo que actualizamos es un valor fijo, lo ponemos en la fila Actualizar a: sin más, Access se encargará de añadir las comillas si el campo es de tipo texto o las # # si el campo es de tipo fecha.
- Cuando el valor a dejar en el campo que actualizamos está contenido en un campo de esa misma tabla tenemos que poner el nombre del campo entre [ ] para que Access no lo confunda con un valor fijo de tipo texto y le añada las comillas. Por ejemplo, supongamos que hemos añadido a la tabla alumnado un nuevo campo Provincia para almacenar en él la provincia del alumno y como la mayoría de nuestros alumnos viven en capital de provincia queremos crear una consulta para rellenar el campo provincia de todos los alumnos con el nombre de su localidad y luego cambiar manualmente los pocos alumnos cuya localidad no coincida con la provincia.
En la consulta a crear habría de poner en la cuadrícula la columna Provincia y en la fila Actualizar a: poner [Poblacion] entre corchetes para que Access entienda que tiene que coger el valor del campo Población.
También podemos utilizar en la fila Actualizar a: una expresión basada en el campo que estamos actualizando u otro campo que también actualizamos en esa consulta. En estos casos se utilizará, para calcular la expresión, los valores antes de la actualización. por ejemplo queremos subir un 5% el precio de nuestros artículos, la expresión a escribir en la fila Actualizar a: del campo precio sería [precio] * 1,1 (esta expresión es equivalente a [precio] + ([precio] * 10 /100)).
- Cuando el valor a utilizar se encuentra en otra tabla tenemos que definir el origen de la consulta de tal forma que cada fila del origen contenga el campo a actualizar y el campo que contiene el valor a utilizar para la actualización. Por ejemplo supongamos que hemos añadido un campo horas restantes a la tabla alumnado para guardar el número de horas que le quedan al alumnos para acabar el curso. Podríamos crear una consulta para actualizar ese campo con el nºhoras del curso ya que se supone que al principio a todos los alumnos les quedará la totalidad de horas del curso. En este caso el origen de la consulta tiene que contener el campo horas restantes y el campo nºhoras del curso en el que está matriculado el alumno, por eso habría que combinar las tablas Alumnado y Cursos. La consulta quedaría así:
..
Leitmotiv
Dalek-free
Camisetas cargadas de adrenalina. Llénalas de aventura
CursoProgramador 