Introducción

He publicado hace algunos días algunos ejercicios de consultas en MySQL; puedes verlo aquí. En aquel entonces sólo fueron consultas simples, en este caso veremos cómo editar/actualizar y eliminar, así como el procedimiento para vaciar una tabla.

También trabajaremos con algunas funciones de cadena.

Estructura y datos de la tabla

Para trabajar y realizar nuestros ejercicios utilizaremos la tabla y los datos que ya he dejado en el post anterior, pero la vuelvo a poner a continuación:

/*
   Si no tienes una base de datos, copia las siguientes dos líneas que no están comentadas
*/CREATE DATABASE IF NOT EXISTS pruebas;
USE pruebas;

/*
   Si ya tienes una base de datos, sólo copia lo siguiente
*/CREATE TABLE tblUsuarios (
   idx INT PRIMARY KEY AUTO_INCREMENT,
   usuario VARCHAR(20),
   nombre VARCHAR(20),
   sexo VARCHAR(1),
   nivel TINYINT,
   email VARCHAR(50),
   telefono VARCHAR(20),
   marca VARCHAR(20),
   compañia VARCHAR(20),
   saldo FLOAT,
   activo BOOLEAN
);

INSERT INTO tblUsuarios 
VALUES 
('1','BRE2271','BRENDA','M','2','brenda@live.com','655-330-5736','SAMSUNG','IUSACELL','100','1'),
('2','OSC4677','OSCAR','H','3','oscar@gmail.com','655-143-4181','LG','TELCEL','0','1'),
('3','JOS7086','JOSE','H','3','francisco@gmail.com','655-143-3922','NOKIA','MOVISTAR','150','1'),
('4','LUI6115','LUIS','H','0','enrique@outlook.com','655-137-1279','SAMSUNG','TELCEL','50','1'),
('5','LUI7072','LUIS','H','1','luis@hotmail.com','655-100-8260','NOKIA','IUSACELL','50','0'),
('6','DAN2832','DANIEL','H','0','daniel@outlook.com','655-145-2586','SONY','UNEFON','100','1'),
('7','JAQ5351','JAQUELINE','M','0','jaqueline@outlook.com','655-330-5514','BLACKBERRY','AXEL','0','1'),
('8','ROM6520','ROMAN','H','2','roman@gmail.com','655-330-3263','LG','IUSACELL','50','1'),
('9','BLA9739','BLAS','H','0','blas@hotmail.com','655-330-3871','LG','UNEFON','100','1'),
('10','JES4752','JESSICA','M','1','jessica@hotmail.com','655-143-6861','SAMSUNG','TELCEL','500','1'),
('11','DIA6570','DIANA','M','1','diana@live.com','655-143-3952','SONY','UNEFON','100','0'),
('12','RIC8283','RICARDO','H','2','ricardo@hotmail.com','655-145-6049','MOTOROLA','IUSACELL','150','1'),
('13','VAL6882','VALENTINA','M','0','valentina@live.com','655-137-4253','BLACKBERRY','AT&T','50','0'),
('14','BRE8106','BRENDA','M','3','brenda2@gmail.com','655-100-1351','MOTOROLA','NEXTEL','150','1'),
('15','LUC4982','LUCIA','M','3','lucia@gmail.com','655-145-4992','BLACKBERRY','IUSACELL','0','1'),
('16','JUA2337','JUAN','H','0','juan@outlook.com','655-100-6517','SAMSUNG','AXEL','0','0'),
('17','ELP2984','ELPIDIO','H','1','elpidio@outlook.com','655-145-9938','MOTOROLA','MOVISTAR','500','1'),
('18','JES9640','JESSICA','M','3','jessica2@live.com','655-330-5143','SONY','IUSACELL','200','1'),
('19','LET4015','LETICIA','M','2','leticia@yahoo.com','655-143-4019','BLACKBERRY','UNEFON','100','1'),
('20','LUI1076','LUIS','H','3','luis2@live.com','655-100-5085','SONY','UNEFON','150','1'),
('21','HUG5441','HUGO','H','2','hugo@live.com','655-137-3935','MOTOROLA','AT&T','500','1');

Una vez que tengamos todo listo, vamos allá.

Funciones de cadena

Las funciones de cadenas son utilizadas para trabajar con strings, son útiles pero personalmente recomiendo usarlas sólo en casos en donde estemos forzados a hacerlo. No usarlas trae algunas ventajas:

• Hacemos que la base de datos sea menos dependiente: si después necesitamos mover nuestras consultas a PgSQL, SQL Server o incluso SQLite sólo necesitaremos en algunos casos cambiar el tipo de dato, pero nuestro código puede mantenerse igual.
• Dejamos que el servidor descanse: podemos, en el caso del ambiente cliente servidor, hacer las operaciones a las cadenas desde el cliente, evitando así sobrecargar al servidor y dejando que éste se encargue únicamente de volcar los datos.

Nota: esto aplica en bases de datos en donde haya millones de filas, en las demás tal vez ni se note la diferencia de rendimiento.

Sin embargo, nunca está de más aprender a utilizarlas, y para pequeños ejercicios nunca vendrán mal. Por otro lado, repito, si son operaciones sencillas no causarán un gran impacto al servidor.

Obtener parte izquierda

La sintaxis es:

LEFT(texto, cuántos)

En donde el texto puede ser un texto arbitrario como “Hola” o un dato.

Obtener clave del curso

Si miramos a la base de datos, la columna usuario tiene una clave conformada por otra clave y un número consecutivo. Por ejemplo, FP9D1801-01.

Podemos usar LEFT para obtener únicamente la clave (FP9D1801), sin el número consecutivo, usando esto:

SELECT LEFT(usuario, 8) FROM tblAlumnos LIMIT 1;

Recordemos que LEFT toma una cadena, en este caso será la columna usuario. Después, el segundo parámetro indica cuántos queremos, comenzando desde el inicio. Sólo queremos 8 caracteres.

Finalmente limitamos la consulta a 1, ya que no queremos que salgan todos los datos, pues serían repetitivos.

Obtener iniciales de los alumnos

Otra cosa para la que nos puede servir LEFT es para obtener únicamente la inicial de los nombres de los alumnos. En este caso, usaríamos:

SELECT LEFT(nombre, 1) AS Iniciales FROM tblAlumnos;

Indicamos que queremos la parte izquierda de nombre, ¿y cuántos queremos? sólo 1, es decir, la primera letra. Recuerda que el AS es para poner un alias al resultado.

En este caso no usamos LIMIT, en la consulta anterior sí, porque los datos eran repetitivos. Pero en este caso todas las iniciales (en teoría) son diferentes.

Obtener parte derecha

Esto es lo inverso de LEFT, y sigue la misma sintaxis:

RIGHT(texto, cuántos)

Sólo que en este caso, los “cuántos” comienza a contarlos desde la derecha. Es decir, si le mandamos algo como:

RIGHT(“Hola”, 1) devolvería “a”

Contrario a LEFT, que con:

LEFT(“Hola”, 1) devolvería “H”

Obtener los ID’s de los alumnos

Hace un momento, de la cadena parecida a FP9D1801-01 sacábamos la parte del curso, es decir, algo como FP9D1801. Ahora hagamos lo contrario, sacar únicamente el ID, el cuál sería 01, 02, etcétera.

Para ello, usamos:

SELECT RIGHT(usuario, 2) AS id FROM tblAlumnos;

Analizando podemos ver que desde la derecha tomamos los 2 dígitos, cosa que nos dará 01, 02, etcétera.

Obtener los ID’s de los alumnos varones

Casi la misma consulta que la anterior, pero con una condición en donde sexo sea H. La consulta queda así:

SELECT RIGHT(usuario, 2) AS id FROM tblAlumnos WHERE sexo = 'H';

Obtener los ID’s de los alumnos que no son varones

En este caso podríamos hacer una condición en donde sexo sea M, pero se está pidiendo una negación. La consulta queda así:

SELECT RIGHT(usuario, 2) AS id FROM tblAlumnos WHERE sexo <> 'H';

Parte media de una cadena

Ya vimos LEFT y RIGHT, ahora veamos a la función MID, que tiene la siguiente sintaxis:

MID(texto, desdeDónde, cuántos)

Vemos que en este caso sólo se agrega un parámetro “desdeDónde”. Analicemos…

En LEFT, desdeDónde es 1, pues comienza desde el primer carácter. En RIGHT, desdeDónde es la longitud de la cadena, pues comienza desde el último carácter.

Y MID nos permite especificar ese “desdeDónde” para tener un poco más de control.

Por cierto, recomiendo echarle un ojo a la función SUBSTR y SUBSTRING que me parecen mejores, personalmente.

Obtener período del curso

Trabajaremos de nuevo con los datos que tienen el formato “FP9D1801-01”. Ya obtuvimos la clave (FP9D1801), también el id (01) pero ahora queremos sólo el período, que en este caso sería 1801.

La consulta queda así:

SELECT MID(usuario, 5, 4) AS periodo FROM tblAlumnos LIMIT 1;

Vamos a cortar a “usuario”.

¿Desde dónde? desde la posición 5 (lo que me cae mal es que esto nos confunde, pues los índices comienzan en 1, contrario a los arreglos que conocemos, en donde empiezan en 0).

¿Cuántos? 4, pues eso mide “1801”.

Hacemos el límite porque no tiene caso consultar todos los registros, ya que son repetitivos al menos en este caso.

Longitud de una cadena

Ahora veamos otra operación curiosa, llamada CHAR_LENGTH que toma una cadena como entrada y devuelve su longitud. Su sintaxis es:

CHAR_LENGTH(cadena)

Y los casos de uso podrían ser:

• CHAR_LENGTH(“Hola”) => 4
• CHAR_LENGTH(“Maggie”) => 6
• CHAR_LENGTH(“Los espacios cuentan”) => 20

Longitud de los nombres de los alumnos

El ejercicio pide devolver el nombre, y la longitud del mismo. Es fácil, la consulta queda así:

SELECT nombre, CHAR_LENGTH(nombre) FROM tblAlumnos;

Recordemos que CHAR_LENGTH puede tomar la columna de una base de datos.

Concatenar cadenas

Concatenar una cadena es juntar una con otra. Por ejemplo, si concatenamos “Ho” y “la” obtenemos “Hola”. La sintaxis es:

CONCAT(dato1, dato2, dato3, …datoN);

Es decir, podemos concatenar infinitas columnas. Veamos algunos casos de uso:

• CONCAT(“Ho”, “la”, ” mundo”) => “Hola mundo”
• CONCAT(“”, “”, “”, “H”, “”, “ola”) => “Hola”

Imprimir “Alumno” seguido del nombre del alumno

Para este caso no bastará simplemente seleccionar el nombre del alumno, sino que debe decir “Alumno” antes.

En otras palabras, en lugar de decir “Pedro” dirá “Alumno Pedro”. La consulta es la siguiente:

SELECT CONCAT('Alumno ', nombre) FROM tblAlumnos;

Devolver la letra inicial mas la letra final y longitud del nombre de los alumnos

Esta es una consulta un tanto más compleja. Lo que pide es que, de un nombre como “LUIS” devuelva LS4, ya que quiere la primera letra (L), la última (S) y la longitud (4). Para ello, la consulta queda así:

SELECT nombre, CONCAT(LEFT(nombre, 1), RIGHT(nombre, 1), CHAR_LENGTH(nombre)) AS clave FROM tblAlumnos;

Simplemente obtenemos los datos separados y finalmente concatenamos.

Buscar índice de una subcadena en una cadena

Esta función es muy útil. Permite buscar una aguja en un pajar, y devuelve la posición de una subcadena en una cadena. Si no encuentra nada, devuelve 0. Por cierto, no le importan las mayúsculas o minúsculas, las trata por igual. La sintaxis es:

INSTR(pajar, aguja)

Algunos casos de uso:

• INSTR(“Hola”, “o”) => 2
• INSTR(“Adiós”, “Z”) => 0
• INSTR(“El perro ladra”, “perro”) => 4

Esta función también sirve para determinar si existe o no existe una subcadena en una cadena. Si devuelve 0 es que no, en caso contrario significa que sí.

Localizar la posición donde existe el primer espacio en el campo nombre

Usando INSTR con el primer argumento siendo nombre y el segundo el carácter ” ” (por favor, notar el espacio) la consulta queda así:

SELECT INSTR(nombre, " ") FROM tblAlumnos;

 

Función IF

Esta función no va aquí, pues no trata con cadenas sino con cualquier condición. Pero es necesario ponerla. La sintaxis es:

IF(algunaCondicion, enCasoDeQueSeCumpla, enCasoDeQueNo)

Casos de uso:

• IF(5 < 20, “Sí”, “No”) => Sí

No puedo hacer muchos casos de uso, mejor veamos el ejercicio.

Imprimir dama o caballero según sea el caso, seguido del nombre del alumno

Dado un nombre como “Fernando”, cuyo sexo es H se tiene que imprimir “Caballero Fernando”. Si el nombre es, por ejemplo, “María” debe imprimir “Dama María”. Para esto usaremos CONCAT e IF:

SELECT CONCAT(IF(sexo = 'H', 'Caballero', 'Dama'), ' ', nombre) AS nombreFormal FROM tblAlumnos;

Espero que el IF se entienda. Primero comparamos si sexo es ‘H’. En caso de que sí, vamos a seleccionar “Caballero”, en caso de que no, “Dama”.

Luego, vamos a concatenar un espacio independientemente de si es hombre o mujer. Y finalmente concatenamos el nombre.

Upper y lower

No dedicaré mucho a esto, basta con explicar que upper convierte todo a mayúsculas y lower lo contario. Casos de uso para upper:

• UPPER(“HolA”) => “HOLA”
• UPPER(“Qué tal? 123”) => “QUÉ TAL? 123”

Para lower:

• LOWER(“AdIÓs”) => “adiÓs” (malditos acentos, aunque extrañamente para upper sí funcionan)
• LOWER(“foo”) => “foo”

Devolver en nombre propio el color favorito de los alumnos de CDMX

Aquí viene otra consulta que me gusta. Al principio me confundí, porque ¿qué demonios es nombre propio? yo lo conozco como Capitalizado.

En fin, se refiere a que si el color es “ROJO” o “rojo” o “RoJo” debemos devolverlo como “Rojo”; es decir, con la primera letra mayúscula y las demás minúsculas.

Cabe mencionar que debe mostrar sólo aquellos en donde el alumno sea de CDMX.

Si vemos la tabla, todos los datos están en mayúscula. Así que para la primera letra no tenemos que hacer ninguna conversión, pero para las demás sí. Lo explico aquí:

Supongamos que tenemos “AZUL”. Cortamos la primer letra con LEFT(colorFavorito, 1) y obtenemos A. Luego, queremos ZUL convertido a minúsculas, pero mm ¿cómo sabemos que será ZUL? tenemos que cortar con RIGHT, pero ¿cuántos? aquí viene a salvarnos CHAR_LENGTH.

Si CHAR_LENGTH devuelve la longitud, debemos cortar con RIGHT la cantidad de longitud – 1. Siguiendo el caso de “AZUL”, CHAR_LENGTH devuelve 4, pero al restarle 1 es 3. Luego hacemos un RIGHT(colorFavorito, CHAR_LENGTH(colorFavorito) – 1) y listo.

Con una combinación de estas funciones y CONCAT, podemos obtener lo que queremos. La consulta queda:

SELECT nombre, CONCAT(LEFT(colorFavorito, 1), LOWER(RIGHT(colorFavorito, CHAR_LENGTH(colorFavorito) - 1))) AS color FROM tblAlumnos WHERE origen = 'CDMX'; 

Con esto terminamos las funciones de cadena y las consultas, al fin podemos pasar a algo más interesante.

UPDATE, o cómo actualizar/editar registros

Update nos permite cambiar datos. Podemos cambiar a todos los de la tabla a un nuevo valor, asignarle un valor dependiendo del valor que ya existe, o usar una condición combinada.

Veamos los ejemplos para tener más claro el concepto, pero primero la sintaxis:

UPDATE nombreTabla SET columna = nuevoValor, otraColumna = otroValor WHERE algunaCondición

La cláusula WHERE no es necesaria, sólo es para ilustrar. Y podemos actualizar sólo una columna, o 2, o 3, o las que tenga nuestra tabla.

Cambiar el sueldo del usuario con el idx 20 a $30000

Al usuario con idx 20, vamos a cambiarle el valor que tiene en ingresos. Para ello, usamos:

UPDATE tblAlumnos SET ingresos = 30000 WHERE idx = 20;

Cambiar el sueldo del usuario con el idx 20 a $33000 usando su nombre de usuario

La misma consulta que la anterior, pero ahora usando “usuario” como condición en WHERE. Así:

UPDATE tblAlumnos SET ingresos = 33000 WHERE usuario = 'FP9D1801-20';

Esto sirve para demostrar que no siempre tenemos que utilizar el índice (aunque es muy recomendable, por la velocidad).

Aumentar sueldo sin saber valor

En los casos anteriores usábamos un valor definido por nosotros al actualizar. Pero, ¿qué pasa si queremos aumentar el sueldo independientemente de su valor? por ejemplo, aumentarle $1000 sin importar si su sueldo es $3000, $4000, etcétera.

La consulta queda así:

UPDATE tblAlumnos SET ingresos = ingresos + 2000 WHERE usuario = 'FP9D1801-20';

Como podemos observar, usamos SET ingresos = ingresos + 2000. Es al mismo usuario con el que hemos venido trabajando, pero por favor noten que utilizamos ingresos como una variable.

Cambiar origen

Al mismo usuario ahora vamos a asignarle un nuevo origen: Puebla. La consulta es realmente sencilla:

UPDATE tblAlumnos SET origen = 'PUEBLA' WHERE usuario = 'FP9D1801-20';

Cambiar créditos y color favorito

UPDATE tblAlumnos SET creditos = 320, colorFavorito = 'NEGRO' WHERE usuario = 'FP9D1801-20';

Ahí vemos que podemos establecer muchos valores, no sólo uno.

Incrementar los ingresos de los alumnos del estado de Puebla en un 2 %

Esta consulta me gustó mucho. Dice que a los ingresos de los alumnos en puebla hay que aumentarles el 2 % sin importar el valor de los mismos. Por ejemplo, si ingresos es 100 ahora será 102.

Para ello usamos lo siguiente:

UPDATE tblAlumnos SET ingresos = ingresos + (ingresos * (0.02)) WHERE origen = 'PUEBLA';

Esto fue lo que propuse, pero existe otra manera más fácil:

UPDATE tblAlumnos SET ingresos = ingresos * 1.02 WHERE origen = 'PUEBLA';

Ya que no es necesario sumar si multiplicamos por 1, pues 1 representa el 100 %. De todas maneras ambos funcionan, aunque recomiendo el segundo.

Actualizar el campo color de la siguiente manera: Poner como BLUE cuando color sea CAFE, VERDE, ROJO o AZUL

Fácil, usando la cláusula IN que vimos en el post anterior. La consulta queda así:

UPDATE tblAlumnos SET colorFavorito = 'BLUE' WHERE colorFavorito IN('CAFE', 'VERDE', 'ROJO', 'AZUL');

Igualmente podemos hacer muchos OR, pero me parece más elegante IN.

Eliminar registros con DELETE

Ahora para terminar veamos la sintaxis de DELETE. Es así:

DELETE FROM nombreTabla WHERE algunaCondición;

Eliminar aquellos alumnos en donde color favorito sea rosa

Es sencillo, pues es combinar DELETE con un WHERE. Queda así:

DELETE FROM tblAlumnos WHERE colorFavorito = 'ROSA';

Eliminar registros en donde el origen sea PUEBLA y el color favorito sea BLUE

DELETE FROM tblAlumnos WHERE origen = 'PUEBLA' AND colorFavorito = 'BLUE';

Bonus: vaciar tabla

DELETE FROM tblAlumnos;

O también:

TRUNCATE tblAlumnos;

La diferencia es que TRUNCATE reinicia el contador de autoincremento, mientras que DELETE no. Aunque de hecho ya vimos la forma de reiniciar dicho contador.

Conclusión

Con esto terminamos por hoy, espero poder traer más ejercicios. Por cierto, te recomiendo visitar el primer post: Ejercicios resueltos con MySQL parte 1.