Seguimos avanzando con nuestro aprendizaje SQL con MariaDB para ello vamos a ver con detalle las estructuras para añadir, actualizar y borrar datos, es decir hacemos con MYSQL INSERT UPDATE DELETE , sobre una tabla de la base de datos, para ello recordaremos lo aprendido en la sesión 2 con la creación de una nueva tabla cb_currency, como ya indicamos a lo largo del curso crearemos una base de datos con 8 tablas basadas en el proyecto Learning Project, y que nos permiten estudiar y aprender todo lo necesario sobre SQL utilizando MariaDB.
Antes de empezar te recomendamos, si no lo has hecho ya, que te veas la anterior publicación Curso MariaDB (2): Creando las tablas en SQL, estos son los puntos que vamos a ver en esta publicación:
MariaDB SQL
Seguimos el aprendizaje desarrollando el proyecto Learning Project para la base de datos Customerdb para MariaDB estudiando la sintaxis SQL.
Código Xules
Lo que vamos ver
- Creamos las tabla cb_currency
- SELECT básico y manipulación de datos
- SQL INSERT
- SQL UPDATE
- SQL DELETE
- Bonus de datos para cb_currency
Para esta parte vamos a crear la tabla cb_currency siguiendo el orden natural de creación para que las relaciones entre las tablas se puedan establecer correctamente, con la explicación que vamos a ver en esta publicación nos permitirá ir probando las tablas, añadiendo datos y haciendo las publicaciones más dinámicas, después de la creación de la tabla cb_currency utilizaremos esta para ver como añadir, actualizar y borrar datos, dónde aprovecharemos para explicar detenidamente la sintaxis de INSERT UPDATE DELETE en SQL
1. Creamos la tabla cb_currency
La tabla cb_currency, es nuestra tabla de monedas para el proyecto, la utilizaremos para definir la moneda del país (cb_country y cb_enterprise por ejemplo), este el código completo que a continuación explicamos:
CREATE TABLE cb_currency ( idcurrency INT NOT NULL AUTO_INCREMENT, currency VARCHAR(60) NOT NULL, description VARCHAR(255) NOT NULL , isactive VARCHAR(1) NOT NULL DEFAULT 'N', isocode VARCHAR(3) NOT NULL, cursymbol VARCHAR(10), precisionstd DECIMAL(10,0) NOT NULL, precisioncost DECIMAL(10,0) NOT NULL, precisionprize DECIMAL(10,0) NOT NULL DEFAULT 0, CONSTRAINT pk_cb_currency PRIMARY KEY (idcurrency), CONSTRAINT u_cb_currency_currency UNIQUE (currency), CONSTRAINT u_cb_currency_isocode UNIQUE (isocode) ) ENGINE = InnoDB COMMENT='Tabla donde se definen las monedas disponibles y sus relaciones a partir de las monedas bases.'; GRANT ALL ON TABLE cb_currency TO xulescode;
Usamos idcurrency como clave primaria un tipo integer con AUTO_INCREMENT (generación automática de un contador único):
Algunas consideraciones a tener en cuenta al definir los valores AUTO_INCREMENT:
- Puede utilizarse para generar una identidad única para nuevas filas. Al insertar un nuevo registro a la tabla el valor automáticamente será incrementado.
- Las columnas AUTO_INCREMENT comienzan a partir del 1 de forma predeterminada. El valor generado automáticamente nunca puede ser inferior a 0.
- Cada tabla puede tener sólo una columna AUTO_INCREMENT. Debe definirse como una clave (no necesariamente la clave principal o clave UNIQUE). Si la clave consiste en varias columnas, la columna AUTO_INCREMENT tiene que ser el primero, a menos que el motor de almacenamiento es Aria o MyISAM.
Para la definición de clave primaria que explicamos en la anterior publicación, usamos el siguiente código SQL:
CONSTRAINT pk_cb_currency PRIMARY [Nombre de la clave] KEY (idcurrency) [Indicamos el campo que forma la clave primaria]
En este caso también definimos como clave única currency, es decir, que este valor no se puede repetir, para ellos utilizamos: CONSTRAINT u_cb_currency_currency UNIQUE (currency), lo mismo hacemos con isocode: CONSTRAINT u_cb_currency_isocode UNIQUE (isocode) recordemos la explicaciòn de su uso:
CONSTRAINT u_cb_currency_currency [Nombre para la clave única] UNIQUE (currency) [Indicamos el campo que queremos definir como único]
Para consultar los tipos de datos que podemos usar en MariaDB aquí tienes toda la información: Data Types, veamos los tipos básicos utilizados hasta ahora:
- INT
- Integer desde -2147483648 hasta 2147483648
- INTEGER
- Sinónimo para INT
- DECIMAL
- A packed «exact» fixed-point number
- DEC, NUMERIC, FIXED
- Sinónimo para DECIMAL
- VARCHAR
- Cadena con tamaño variable, como puedes ver en las definiciones de las tablas utilizamos diferentes tamaños de cadenas para limitar los tamaños de las variables utilizadas según nos interese.
2. SELECT BÁSICO Y MANIPULACIÓN DE DATOS EN SQL
En esta introducción al SELECT básico vamos a ver como mostrar por terminal o en editor SQL los datos de una tabla, en una posterior publicación veremos conceptos más avanzados como la relación de tablas para obtener los datos, …, empecemos por lo más sencillo.
Cláusulas SQL SELECT y SQL FROM
Este es el ABC de las consultas de bases de datos con estas dos cláusulas podemos listar los datos de cualquier tabla, usando cualquier administrador de bases de datos, o directamente desde la consola, nosotros en este caso como ya dijimos antes utilizamos DBeaver.
La sintaxis SQL que utilizaremos incluye los elementos SELECT y FROM:
- SELECT nos sirve para indicar que campos queremos mostrar, como introducción diremos que podemos escribir los campos separador por comas, o si los queremos listar todos usar el símbolo *, con lo cual la base de datos entenderá que tiene que mostrar todas las columnas de la tabla.
- FROM aquí indicamos las tabla o tablas que vamos a consultar, y por lo tanto, obtener los datos. Las relaciones entre varias tablas lo dejamos para una publicación de consultas avanzadas, aquí solo utilizaremos la tabla de la que queremos extraer los datos.
Por lo tanto la consulta sobre cb_currency sobre todos los campos de la tabla quedaría así:
SELECT * FROM cb_currency;
Si queremos extrare solo unas columnas las identificamos con el nombre separadas por comas: :
SELECT idcurrency, currency, description FROM cb_currency;
En ambos casos, cómo todavía no tenemos datos en la base de datos los resultados nos muestran que el número de filas es cero, este es el resultado si lo hacemos por línea de comandos:
Si hacemos los mismo por ejemplo con la primera consulta en DBeaver el resultado es que nos muestra 0 filas, pero nos indica las columnas que hemos seleccionado de la tabla:
Cláusulas SQL WHERE y ORDER BY
Ahora que ya sabemos como consultar los datos de una tabla vamos a ver como podemos establecer filtros sobre la misma con la claúsula SQL WHERE y posteriormente ver cómo ordenar los resultados con la claúsula SQL ORDER BY:
El funcionamiento de WHERE es sencillo en primer lugar se coloca siempre a continuación de FROM y podemos filtrar por una o varias columnas, por ejemplo, buscando las filas que tengan un valor determinado en una columna:
SELECT *
FROM cb_currency
WHERE currency = 'EURO';
Hay muchas más posibilidades que veremos en las consultas avanzados, como filtrar por aquellas monedas que empiecen por E, esto se hace utilizando LIKE en vez de = , os lo dejo ahí para los que queráis ir explorando, nosotros lo explicaremos más adelante.
La claúsula ORDER BY nos va a permitir ordenar el resultado de la consultar por uno o varios campos, pudiendo escoger el orden ascendente (ASC) o descendente (DESC), se añade a continuación de la claúsula WHERE si la utilizamos, por ejemplo:
SELECT *
FROM cb_currency
ORDER BY currency ASC;
Al final, de este con datos ya en la tabla exploraremos la utilidad de estos elementos de la sintaxis de SELECT.
A continuación vamos a ver como manipular los datos en la base de datos MariaDB con SQL aprendiendo la sintaxis de:
- INSERT
- Inserción de nuevas filas en la tabla seleccionada.
- UPDATE
- Actualización de uno o varios parámetros de una o varias filas, como veremos a continuación.
- DELETE
- Borrado de uno o varios registros de la tabla.
3. SQL INSERT
INSERT es el comando que utilizamos para añadir (insertar) nuevos registros en una tabla, este es el primer comando que vamos a estudiar en el aprendizaje de MySQL INSERT UPDATE DELETE:
La sintaxis es la siguiente, no te asustes, la explicaremos paso a paso:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ]
En nuestro aprendizaje vamos a usar la tabla que acabamos de crear cb_currency, que es donde se registran las monedas de los diferentes países, para ello vamos a insertar una nueva moneda, veamos cómo.
En la tabla cb_currency que hemos creado tenemos como clave primaria idcurrency que es un valor autonumérico, esto quiere decir que no lo introducimos nosotros sino que se genera automáticamente al añadir un registro. Esto tiene su incidencia ya que al hacer el INSERT será un campo que no incluyamos.
INSERT INTO con VALUE
Vamos con el primer ejemplo, creando una nueva moneda, los valores para nuestra moneda serán los siguientes:
- currency: Euxo
- descripcion: Euxo (Xules)
- isactive: Y
- isocode: ‘EUX
- cursymbol: X
- precisionstd: 2
- precioncost: 4
- precisionprize: 4
Para crear esta nueva moneda usamos la sintaxis de INSERT en este caso indicamos específicamente que columnas vamos a añadir en el orden que queremos (este lo podemos variar), tal y como vemos en el ejemplo:
INSERT INTO cb_currency(currency, description,
isactive, isocode, cursymbol,
precisionstd, precisioncost, precisionprize)
VALUES ('Euxo', 'Euxo (Xules)', 'Y', 'EUX', 'X', 2, 4, 4);
No es obligatorio añadir todos los campos, por ejemplo, si tuviesemos un campo que va por defecto o que puede tener valor NULL podríamos realizar un INSERT sin incluir ese campo entre las columnas del registro.
Otra opción de introducir los datos si mantemos el orden en el que están las columnas en la tabla es el siguiente:
INSERT INTO cb_currency
VALUES (null, 'Euxo', 'Euxo (Xules)', 'Y', 'EUX', 'X', 2, 4, 4)
El resultado en este ambos será algo así:
MariaDB [customerdb]> INSERT INTO cb_currency VALUES (null, 'Euxo', 'Euxo (Xules)', 'Y', 'EUX', 'X', 2, 4, 4); Query OK, 1 row affected (0.273 sec)
Ahora ya podemos usar lo aprendido en el apartado 2 con SELECT así que para ver el resultado de la creación recuerda que puedes usar, por ejemplo estas consultas:
SELECT *
FROM cb_currency;
SELECT *
FROM cb_currency
WHERE currency = 'Euxo';
Este resultado sería si lo hacemos por terminal sería algo así:
INSERT INTO con SET
Otra forma de crear un nuevo registro es utilizar el INSERT con SET el resultado sería el mismo , esta es la sintaxis:
INSERT INTO cb_currency
SET currency = 'Euxo',
description = 'Euxo (Xules)',
isactive = 'Y', isocode = 'EUX', cursymbol = 'X',
precisionstd = 2, precisioncost = 4, precisionprize= 4;
Si ejecutamos esto en DBeaver está es la información que obtenemos y como lo podemos ejecutar simplemente pulsando el botón play:
DBeaver comprobación INSERT
Para ir viendo alguna cosilla con DBeaver, tenemos que tener en cuenta que podemos hacer esto de forma visual con el programa tanto ver datos como insertar nuevos registros, para abrir la gestión de la tabla haz doble clic sobre la misma, se abrirá una ventana con 3 pestañas: Propiedades (estructura de la tabla), Datos (registros de la tabla) y Diagrama E-R (esquema visual entidad relación de la tabla), en la imagen te muestro la pestaña de datos:
INSERT DE VARIOS REGISTROS
Con INSERT también podemos insertar valores de varios registros al mismo tiempo, incluso veremos más adelante que es posibnle insertar datos usando una consulta SQL, pero para no liarnos vamos con la insercción de varios registros al mimo tiempo:
INSERT INTO cb_currency(currency, description,
isactive, isocode, cursymbol,
precisionstd, precisioncost, precisionprize)
VALUES ('Eux1', 'Eux1 (Xules)', 'Y', 'EX1', '1', 2, 4, 4),
('Eux2', 'Eux2 (Xules)', 'N', 'EX2', '2', 2, 4, 4),
('Eux3', 'Eux3 (Xules)', 'N', 'EX3', '3', 2, 4, 4),
('Eux4', 'Eux4 (Xules)', 'N', 'EX4', '4', 2, 4, 4),
('Eux5', 'Eux5 (Xules)', 'Y', 'EX5', '5', 2, 4, 4);
4. SQL UPDATE
La actualización de registros de una tabla se realiza con el comando UPDATE, este nos permite la modificación de los datos que ya están en la base de datos. Se pueden actualizar filas individuales, todas las filas de una tabla, o un subconjunto de todas las filas. Además, cada columna se puede actualizar por separado ya que las otras columnas no se ven afectadas.
El comando UPDATE tiene la siguiente sintaxis:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
[PARTITION (partition_list)]
SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Vamos a comprender la sintaxis con un ejemplo de forma muy sencilla, simplemente vamos a actualizar la columna isactive con el valor N para el registro que tiene el valor de Eux1 en la columna currency que hemos creado anteriormente, solo modificaremos este registro ya que el campo currency lo hemos establecido como único, esto quiere decir que en la tabla solo puede haber un registro con ese valor en esa columna:
UPDATE cb_currency
SET isactive = 'N'
WHERE currency = 'Eux1';
Es bastante sencillo, después del UPDATE indicamos la tabla sobre la que vamos a actuar currency, en la clausula SET indicamos la actualización a realizar, y por último, con la clausula WHERE condicionamos los registros que se van a ver involucrados, en este caso con currency = ‘Eux1’, solo se verá un registro afectado. El resultado que nos muestra la consola nos indicará el número de registros afectados:
Query OK, 1 row affected (0.218 sec) Rows matched: 1 Changed: 1 Warnings: 0
Para ver la actualización ponemos un ejemplo de como podemos concatenar varias senticias SQL, en este caso vamos a ver como concatenamos un UPDATE y un SELECT, como vemos en la imagen:
UPDATE de varios registros
Con el comando UPDATE podemos actualizar varios registros al mismo tiempo y varios campos, para ello vamos a actualizar la columna isactive de todos los registros al valor Y; y además, a la columna description le vamos a añadir el texto *MONEDA* a todos los registros.
Vamos a utilizar esta actualización para conocer alguna función nueva, para llevar a cabo la actualizacíon del texto añadiendo *MONEDA* tenemos que utilizar la CONCAT que nos permite concatenar varios valores separándolos por comas, así de sencillo: CONCAT(description,’ ‘, ‘*MONEDA*’).
De todos los registros que ya tenemos en la tabla solo vamos actualizar una selección para ello introducimos en la secuencia de UPDATE el filtro con IN con los valores separados por comas para el campo currency: currency IN (‘Eux1’, ‘Eux2’, ‘Eux3’, ‘Eux4′,’Eux5’)
UPDATE cb_currency
SET isactive = 'Y',
description = CONCAT(description,' ', '*')
WHERE currency IN ('Eux1', 'Eux2', 'Eux3', 'Eux4','Eux5');
Para comprobar el resultado utilizamos como ya sabemos la consulta con SELECT y vemos como utilizar el comparador LIKE que nos sirve para filtrar por aquellos valores que empiecen por Eux con el símbolo % como e puede ver en el ejemplo:
SELECT *
FROM cb_currency
WHERE currency LIKE 'Eux%';
Visualización en Dbeaver del UPDATE de varios registros
Este es el resultado con la ejecución de las consultas, finalizando las explicaciones del comando UPDATE dentro del estudio de MySQL INSERT UPDATE DELETE con DBeaver:
5. SQL DELETE
Después de explicar cómo agregar datos y cómo cambiarlos, para completar el trío MySQL INSERT UPDATE DELETE para MariaDB (MySQL), nos falta ver como eliminar los datos usando el comando SQL DELETE, esta es la sintaxis:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_list)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
[RETURNING select_expr [, select_expr ...]]
Veamos como eliminamos la columna que hemos creado en el apartado anterior, filtramos por la columna única currency de la tabla con lo que nos aseguramos que solo borramos el registro que queremos:
DELETE FROM cb_currency
WHERE currency = 'Euxo';
Así, lo veremos si lo hacemos por línea de comandos:
MariaDB [customerdb]> DELETE FROM cb_currency WHERE currency = 'Euxo'; Query OK, 1 row affected (0.052 sec)
Utilizando RETURNING * (disponible a partir de MariaDB 10.0.5) nos devuelve los datos que hemos borrado, en este caso utilizamos otra columna que también hemos definido como única isocode :
DELETE FROM cb_currency
WHERE isocode = 'EUX';
RETURNING *;
Finalmente, nos queda la más sencilla, pero la más peligrosa, el borrado de todos los registros de la tabla:
DELETE FROM cb_currency;
6. Bonus de datos cb_currency para practicar MYSQL INSERT UPDATE DELETE
Para que podáis hacer todas las pruebas que querais con bastantes datos os dejo este bonus con un script de INSERTS para las tablas que ya hemos visto de idiomas (cb_language) y monedas (cb_currency) reales para que podais practicar todo lo visto en esta lección del curso orientado a MYSQL INSERT UPDATE DELETE:
Espero que te haya sido útil la publicación como aprendizaje de MySQL INSERT UPDATE DELETE
Xules
