INSERT UPDATE DELETE nos van a proporcionar la manipulación de datos en SQL, como en las anteriores publicaciones explicamos la creación de tablas, ya tenemos creada nuestra base de datos Customerdb en PostgreSQL siguiendo las indicaciones de Learning Project, y también, tenemos datos con la introducción que hicimos en la última publicación.
INSERT UPDATE DELETE
Con nuestra base de datos Customerdb ya creada en PostgreSQL, siguiendo las indicaciones de Learning Project, vamos a aprender la manipulación de datos SQL con INSERT, UPDATE y DELETE.Código Xules
Mi recomendación es que veas las anteriores publicaciones de este tutorial de Postgresql, pero como cada uno aprende a su manera y a su ritmo, a continuación tienes los scripts de la base de datos y población de datos que facilitamos en capítulos anteriores, eso sí tendrás que tener instalado PostgreSQL, en nuestra primera publicación explicábamos como preparar el entorno, y facilitábamos enlaces para la instalación en diferentes sistemas operativos.
Script para la creación de las tablas del esquema de Customerdb definido en Learning Project:
Script para la población de datos de Customerdb:
Como vamos a explicar con pruebas reales como manipular los datos en la base de datos PostgreSQL para ello vamos a ver la sintaxis de:
- INSERT
- Inserción de nuevas filas en la tabla seleccionada. Documentación de PostgreSQL: Inserting Data.
- UPDATE
- Actualización de uno o varios parámetros de una o varias filas, como veremos a continuación. Documentación de PostgreSQL: Updating Data.
- DELETE
- Borrado de uno o varios registros de la tabla. Documentación de PostgreSQL: Deleting Data.
SQL INSERT
Creamos nuevas filas en la tabla seleccionada está es la sintaxis de SQL INSERT (PostgreSQL documentation):
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Como veremos podemos insertar una o más filas especificándolo en los valores de la expresión, o muchas filas como resultado de una consulta.
Para hacer nuestras pruebas iniciales vamos a usar la tabla ya creada cb_language para ello vamos a insertar un nuevo idioma, recordemos antes como era la tabla que vamos a usar:
CREATE TABLE cb_language ( idlanguage character varying(6) NOT NULL, namelanguage character varying(60) NOT NULL, isactive character(1) NOT NULL DEFAULT 'N'::bpchar, languageiso character(2), countrycode character(2), isbaselanguage character(1) NOT NULL DEFAULT 'N'::bpchar, issystemlanguage character(1) NOT NULL DEFAULT 'N'::bpchar, CONSTRAINT pk_cb_language PRIMARY KEY (idlanguage), CONSTRAINT u_cb_language_namelanguage UNIQUE (namelanguage), CONSTRAINT ch_cb_language_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT ch_cb_language_isbaselang_check CHECK (isbaselanguage = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT ch_cb_language_issysang_check CHECK (issystemlanguage = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) );
Donde los valores para nuestro idioma serán, según el orden la tabla los siguientes: ‘ex_EX’,’Español (España)’, ‘Y’, ‘ex’, ‘EX’, ‘Y’ y ‘N’, veamos ahora dos opciones de INSERT para introducir estos datos:
- En la opción 1: indicamos específicamente que columnas vamos a añadir en el orden que queramos, este orden se puede variar, lo que es importante no despistarse en la correspondencia:
INSERT INTO cb_language(
idlanguage, namelanguage, isactive, languageiso, countrycode,
isbaselanguage, issystemlanguage)
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N');
- El resultado que nos mostrará por consola será algo parecido a:
Query returned successfully: one row with OID 61758 inserted, 31 ms execution time.
- En la opción 2: como vamos a insertar todos los valores, podemos dejar sin indicar los campos que utilizamos, teniendo en cuenta que el orden es de la creación de la tabla:
INSERT INTO cb_language
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N');
Puedes saltar al final de este tutorial para ver la consulta de como borrar este registro nuevo creado, para ver el resultado de la creación recuerda que puedes usar:
SELECT * FROM cb_language WHERE idlanguage = 'ex_EX'
También, usando la sintaxis de INSERT podemos usar RETURNING * que nos devolverá directamente el resultado, quedaría así:
INSERT INTO cb_language
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N')
RETURNING *;
Usando PgAdmin este el resultado que se obtiene al final, fíjate en la imagen, para ver que al final de cada consulta pongo un ; para que se pueden ejecutar seguidas una tras otra:
Resultado de la consulta INSERT + SELECT
Vamos a ver como podemos hacer un INSERT a partir de una consulta, si estas empezando te diré que aunque ahora no lo parezca te resultará útil, sería así:
INSERT INTO cb_language SELECT 'ex_EX', 'Español (España)', isactive, languageiso, countrycode, isbaselanguage, issystemlanguage FROM cb_language WHERE idlanguage = 'es_ES'
Ya queda para ti hacer pruebas y comprobar porque esta consulta es correcta, y la que te voy a dejar a continuación, te aconsejo, que pruebes, que cambies los valores de mi consulta, por ejemplo, así:
INSERT INTO cb_language SELECT 'ex_EX', namelanguage, isactive, languageiso, countrycode, isbaselanguage, issystemlanguage FROM cb_language WHERE idlanguage = 'es_ES'
Familiarizarnos con los errores que se producen en las consultas y como los muestra el sistema nos ayudará a comprenderlo e interpretar mejor los mensajes, esto será importante más adelante cuando hagamos consultas o manejemos una mayor cantidad de datos, en ese caso, si no estás acostumbrado los errores no serán tan fáciles de ver como ahora.
Por último, veremos la inserción de varias columnas con un único comando, está es la sintaxis:
INSERT INTO cb_language
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N'),
('e2_EX','Español 2 (España)', 'Y', 'e2', 'EX', 'Y', 'N'),
('e3_EX','Español 3 (España)', 'Y', 'e3', 'EX', 'Y', 'N'),
('e4_EX','Español 4 (España)', 'Y', 'e4', 'EX', 'Y', 'N')
RETURNING *;
Introducimos varias filas con un comando INSERT
SQL UPDATE
Vamos a ver la actualización de datos que es 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.
Para actualizar usamos el comando SQL UPDATE (PostgreSQL documentation) que tiene la siguiente sintaxis:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Veamos actuemos sobre la fila creada en la explicación anterior, por ejemplo:
UPDATE cb_language SET isactive = 'N', isbaselanguage = 'N' WHERE idlanguage = 'ex_EX'
Es importante establecer bien las restricciones en la clausula WHERE para no afectar a valores que no queremos cambiar, en este caso al usar el id de la tabla WHEREidlanguage nos aseguramos que solo modificamos los valores de ese registro, comprueba los resultados con el SELECT, o usando RETURNING *, al igual que vimos en el caso anterior.
Podemos actuar sobre todos los idiomas de la tabla, afectando a todos los registros en el valor que queramos, en este caso, por ejemplo, para activar todos los idiomas, es decir, indicar en la columna isactive el valor ‘Y’:
UPDATE cb_language SET isactive = 'Y';
De la misma forma variando las condiciones en la WHERE podemos afectar al conjunto de registros que queramos.
SQL DELETE
Después de explicar cómo agregar datos y cómo cambiarlos, para completar el trío INSERT UPDATE DELETE, nos falta ver como eliminar los datos usando el comando SQL DELETE (PostgreSQL documentation), esta es la sintaxis:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Vamos a ver como eliminamos la columna que hemos creado en el apartado anterior, en este primer caso filtramos por el id con lo que nos aseguramos que solo borramos el registro que queremos:
DELETE FROM cb_language WHERE idlanguage = 'ex_EX';
Vamos a ver como eliminaríamos el grupo creado anteriormente con los ids: ex_EX, e2_EX, e3_EX y e4_EX, en este caso, les hemos asignado el mismo countrycode, así que, después de comprobar que solo tenemos estos cuatro elementos, le añadimos en este caso RETURNING * que en este caso nos devuelve los valores eliminamos, este el SQL:
DELETE FROM cb_language WHERE countrycode = 'EX' RETURNING *;
Este sería el resultado usando RETURNING *:
Usando RETURNING con DELETE
Finalmente, nos queda la más sencilla, pero la más peligrosa, el borrado de todos los registros de la tabla:
DELETE FROM cb_language;
Como resumen, os dejo la imagen de esta consulta donde utilizo los tres comandos SQL: INSERT UPDATE DELETE, si has entendido bien lo explicado entenderás el resultado final:
INSERT UPDATE DELETE
Espero que te haya sido útil la publicación como aprendizaje de INSERT UPDATE DELETE.Xules
¿Y AHORA QUÉ?
Iniciate en el desarrollo de aplicaciones empresariales web con el proyecto Learning Project con PostgreSQL.
Desarrollo en 6 pasos de una aplicación con Spring Roo con PostgreSQL
En este tutorial aprenderemos a hacer una aplicación Web desde cero usando Spring Roo con PostgreSQL Código Xules
Aprende OpenXava con PostgreSQL
OpenXava es un marco de trabajo AJAX para desarrollo rápido de aplicaciones web empresariales.
INSERT UPDATE DELETE nos van a proporcionar la manipulación de datos en SQL, como en las anteriores publicaciones explicamos la creación de tablas, ya tenemos creada nuestra base de datos Customerdb en PostgreSQL siguiendo las indicaciones de Learning Project, y también, tenemos datos con la introducción que hicimos en la última publicación.
INSERT UPDATE DELETE
Con nuestra base de datos Customerdb ya creada en PostgreSQL, siguiendo las indicaciones de Learning Project, vamos a aprender la manipulación de datos SQL con INSERT, UPDATE y DELETE.Código Xules
Mi recomendación es que veas las anteriores publicaciones de este tutorial de Postgresql, pero como cada uno aprende a su manera y a su ritmo, a continuación tienes los scripts de la base de datos y población de datos que facilitamos en capítulos anteriores, eso sí tendrás que tener instalado PostgreSQL, en nuestra primera publicación explicábamos como preparar el entorno, y facilitábamos enlaces para la instalación en diferentes sistemas operativos.
Script para la creación de las tablas del esquema de Customerdb definido en Learning Project:
Script para la población de datos de Customerdb:
Como vamos a explicar con pruebas reales como manipular los datos en la base de datos PostgreSQL para ello vamos a ver la sintaxis de:
- INSERT
- Inserción de nuevas filas en la tabla seleccionada. Documentación de PostgreSQL: Inserting Data.
- UPDATE
- Actualización de uno o varios parámetros de una o varias filas, como veremos a continuación. Documentación de PostgreSQL: Updating Data.
- DELETE
- Borrado de uno o varios registros de la tabla. Documentación de PostgreSQL: Deleting Data.
SQL INSERT
Creamos nuevas filas en la tabla seleccionada está es la sintaxis de SQL INSERT (PostgreSQL documentation):
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Como veremos podemos insertar una o más filas especificándolo en los valores de la expresión, o muchas filas como resultado de una consulta.
Para hacer nuestras pruebas iniciales vamos a usar la tabla ya creada cb_language para ello vamos a insertar un nuevo idioma, recordemos antes como era la tabla que vamos a usar:
CREATE TABLE cb_language ( idlanguage character varying(6) NOT NULL, namelanguage character varying(60) NOT NULL, isactive character(1) NOT NULL DEFAULT 'N'::bpchar, languageiso character(2), countrycode character(2), isbaselanguage character(1) NOT NULL DEFAULT 'N'::bpchar, issystemlanguage character(1) NOT NULL DEFAULT 'N'::bpchar, CONSTRAINT pk_cb_language PRIMARY KEY (idlanguage), CONSTRAINT u_cb_language_namelanguage UNIQUE (namelanguage), CONSTRAINT ch_cb_language_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT ch_cb_language_isbaselang_check CHECK (isbaselanguage = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT ch_cb_language_issysang_check CHECK (issystemlanguage = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) );
Donde los valores para nuestro idioma serán, según el orden la tabla los siguientes: ‘ex_EX’,’Español (España)’, ‘Y’, ‘ex’, ‘EX’, ‘Y’ y ‘N’, veamos ahora dos opciones de INSERT para introducir estos datos:
- En la opción 1: indicamos específicamente que columnas vamos a añadir en el orden que queramos, este orden se puede variar, lo que es importante no despistarse en la correspondencia:
INSERT INTO cb_language(
idlanguage, namelanguage, isactive, languageiso, countrycode,
isbaselanguage, issystemlanguage)
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N');
- El resultado que nos mostrará por consola será algo parecido a:
Query returned successfully: one row with OID 61758 inserted, 31 ms execution time.
- En la opción 2: como vamos a insertar todos los valores, podemos dejar sin indicar los campos que utilizamos, teniendo en cuenta que el orden es de la creación de la tabla:
INSERT INTO cb_language
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N');
Puedes saltar al final de este tutorial para ver la consulta de como borrar este registro nuevo creado, para ver el resultado de la creación recuerda que puedes usar:
SELECT * FROM cb_language WHERE idlanguage = 'ex_EX'
También, usando la sintaxis de INSERT podemos usar RETURNING * que nos devolverá directamente el resultado, quedaría así:
INSERT INTO cb_language
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N')
RETURNING *;
Usando PgAdmin este el resultado que se obtiene al final, fíjate en la imagen, para ver que al final de cada consulta pongo un ; para que se pueden ejecutar seguidas una tras otra:
Resultado de la consulta INSERT + SELECT
Vamos a ver como podemos hacer un INSERT a partir de una consulta, si estas empezando te diré que aunque ahora no lo parezca te resultará útil, sería así:
INSERT INTO cb_language SELECT 'ex_EX', 'Español (España)', isactive, languageiso, countrycode, isbaselanguage, issystemlanguage FROM cb_language WHERE idlanguage = 'es_ES'
Ya queda para ti hacer pruebas y comprobar porque esta consulta es correcta, y la que te voy a dejar a continuación, te aconsejo, que pruebes, que cambies los valores de mi consulta, por ejemplo, así:
INSERT INTO cb_language SELECT 'ex_EX', namelanguage, isactive, languageiso, countrycode, isbaselanguage, issystemlanguage FROM cb_language WHERE idlanguage = 'es_ES'
Familiarizarnos con los errores que se producen en las consultas y como los muestra el sistema nos ayudará a comprenderlo e interpretar mejor los mensajes, esto será importante más adelante cuando hagamos consultas o manejemos una mayor cantidad de datos, en ese caso, si no estás acostumbrado los errores no serán tan fáciles de ver como ahora.
Por último, veremos la inserción de varias columnas con un único comando, está es la sintaxis:
INSERT INTO cb_language
VALUES ('ex_EX','Español (España)', 'Y', 'ex', 'EX', 'Y', 'N'),
('e2_EX','Español 2 (España)', 'Y', 'e2', 'EX', 'Y', 'N'),
('e3_EX','Español 3 (España)', 'Y', 'e3', 'EX', 'Y', 'N'),
('e4_EX','Español 4 (España)', 'Y', 'e4', 'EX', 'Y', 'N')
RETURNING *;
Introducimos varias filas con un comando INSERT
SQL UPDATE
Vamos a ver la actualización de datos que es 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.
Para actualizar usamos el comando SQL UPDATE (PostgreSQL documentation) que tiene la siguiente sintaxis:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Veamos actuemos sobre la fila creada en la explicación anterior, por ejemplo:
UPDATE cb_language SET isactive = 'N', isbaselanguage = 'N' WHERE idlanguage = 'ex_EX'
Es importante establecer bien las restricciones en la clausula WHERE para no afectar a valores que no queremos cambiar, en este caso al usar el id de la tabla WHEREidlanguage nos aseguramos que solo modificamos los valores de ese registro, comprueba los resultados con el SELECT, o usando RETURNING *, al igual que vimos en el caso anterior.
Podemos actuar sobre todos los idiomas de la tabla, afectando a todos los registros en el valor que queramos, en este caso, por ejemplo, para activar todos los idiomas, es decir, indicar en la columna isactive el valor ‘Y’:
UPDATE cb_language SET isactive = 'Y';
De la misma forma variando las condiciones en la WHERE podemos afectar al conjunto de registros que queramos.
SQL DELETE
Después de explicar cómo agregar datos y cómo cambiarlos, para completar el trío INSERT UPDATE DELETE, nos falta ver como eliminar los datos usando el comando SQL DELETE (PostgreSQL documentation), esta es la sintaxis:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Vamos a ver como eliminamos la columna que hemos creado en el apartado anterior, en este primer caso filtramos por el id con lo que nos aseguramos que solo borramos el registro que queremos:
DELETE FROM cb_language WHERE idlanguage = 'ex_EX';
Vamos a ver como eliminaríamos el grupo creado anteriormente con los ids: ex_EX, e2_EX, e3_EX y e4_EX, en este caso, les hemos asignado el mismo countrycode, así que, después de comprobar que solo tenemos estos cuatro elementos, le añadimos en este caso RETURNING * que en este caso nos devuelve los valores eliminamos, este el SQL:
DELETE FROM cb_language WHERE countrycode = 'EX' RETURNING *;
Este sería el resultado usando RETURNING *:
Usando RETURNING con DELETE
Finalmente, nos queda la más sencilla, pero la más peligrosa, el borrado de todos los registros de la tabla:
DELETE FROM cb_language;
Como resumen, os dejo la imagen de esta consulta donde utilizo los tres comandos SQL: INSERT UPDATE DELETE, si has entendido bien lo explicado entenderás el resultado final:
INSERT UPDATE DELETE
Espero que te haya sido útil la publicación como aprendizaje de INSERT UPDATE DELETE.Xules
