Modificant les dades: insert, update, delete

Introducció

És la secció 11 que estem seguint del tutorial:

• https://www.sqltutorial.org/
• https://www.sqltutorial.org/sql-insert/

Previ: regeneració de les dades de la base de dades HR

Dins del docker tenim el script /etc/HR.sql que és l'estructura i les dades de la nostra base de dades. Per executar aquest script i regenerar les dades ho podem fer de dues maneres: des de la consola, i des de dins del mysql

Entrem a la consola del docker:

$ docker exec -it mysql_m02bd /bin/bash
bash-4.4# 

bash-4.4# cd /etc
bash-4.4# ls HR.sql

El primer que hem de fer és entrar amb un editor de text (nano) al script HR.sql, i comentar les dues primeres línies:

bash-4.4# nano HR.sql

#CREATE DATABASE HR CHARACTER SET utf8 COLLATE utf8_general_ci;
#use HR;

NOTA: el caràcter coixinet (#) és un comentari.

Mira quin és el contingut del script: veuràs fàcilment tots els creates i els inserts.

I ara ja podem executar el script:

# mysql -u alumne -pkeiL2lai HR -f < /etc/HR.sql > /etc/control_errors.log 2>&1

# cat control_errors.log

La segona manera de fer-ho:

$ docker exec -it mysql_m02bd mysql -u alumne -pkeiL2lai HR

mysql> \. /etc/HR.sql
o bé
mysql> source /etc/HR.sql

Per tant, podem potinejar amb les dades tant com vulguem, que sempre podrem regenerar la base de dades. I com a última solució, recorda que sempre et pots tornar a descarregar el docker.

Insert

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES ( 'Dustin', 'Johnson', 'Child', 178 );

# comprovació:
SELECT * FROM dependents WHERE employee_id = 178;

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES 
( 'Cameron', 'Bell', 'Child', 192 ), 
( 'Michelle', 'Bell', 'Child', 192 );

SELECT * FROM dependents WHERE employee_id = 192;

Per fer aquestes comandes, els usuaris 178 i 192 han d'existir en la taula employees. Si no donaria un error de clau forània (veure més endavant quan parlem de restriccions).

Podem copiar una taula sencera dins d'una altra taula. Per tal de fer-ho necessitem la taula dependents_archive que tingui la mateixa estructura que dependents.

create table dependents_archive(
dependent_id int,
first_name varchar(50),
last_name varchar(50),
relationship varchar(50),
employee_id int
);

I ara ja podem omplir tota la taula, que funcionaria com una còpia de seguretat:

INSERT INTO dependents_archive SELECT * FROM dependents;
Query OK, 33 rows affected (0.02 sec)
Records: 33  Duplicates: 0  Warnings: 0

(fer-ho)

Update (todo)

UPDATE employees SET last_name = 'Lopez' WHERE employee_id = 192;

# comprovació:
SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = 192;

UPDATE dependents SET last_name = 'Lopez' WHERE employee_id = 192;

# fem un update amb una subquery

UPDATE dependents SET last_name =
( SELECT last_name FROM employees WHERE employee_id = dependents.employee_id);

Delete

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;

DELETE FROM dependents WHERE dependent_id = 16;

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;


DELETE FROM dependents WHERE employee_id IN(100 , 101, 102);

Tenim un empleat amb id=192, i dos fills d'aquest empleat. Primer esborrem l'empleat:

DELETE FROM employees WHERE employee_id = 192;

Si ara borrem els seus fills, veurem que surt: 0 rows affected. No s'han esborrat, perquè ja s'havien esborrat quan hem esborrat el pare. D'aquesta manera es manté la integritat de les dades.

DELETE FROM dependents WHERE employee_id = 192;

---

creat per Joan Quintana Compte, desembre 2021