Modificant les dades: insert, update, delete

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

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

T1. 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.

T2. Modificació de les dades

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

Update

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;

T3. Restrictions en cascada

vestuari: base de dades sense restriccions en cascada

Fixa't en aquestes dues taules que pots veure en el script /etc/vestuari.sql: 

#OBRA (id_obra, obra, year, notes)
CREATE TABLE OBRA (
	id_obra smallint PRIMARY KEY,
	obra VARCHAR(50) NOT NULL,
	year SMALLINT NOT NULL,
	notes VARCHAR(255)
);

#PERSONATGE (id_pers, pers, rol, id_obra (FK))
CREATE TABLE PERSONATGE (
	id_pers smallint PRIMARY KEY,
	pers VARCHAR(50) NOT NULL,
	rol VARCHAR(50) NULL,
	id_obra SMALLINT NOT NULL,
	FOREIGN KEY (id_obra) REFERENCES OBRA(id_obra)
);

HR: base de dades amb restriccions en cascada

Fixa't en aquestes dues taules que pots veure en el script /etc/HR.sql: 

CREATE TABLE employees (
	employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) DEFAULT NULL,
	last_name VARCHAR (25) NOT NULL,
	email VARCHAR (100) NOT NULL,
	phone_number VARCHAR (20) DEFAULT NULL,
	hire_date DATE NOT NULL,
	job_id INT (11) NOT NULL,
	salary DECIMAL (8, 2) NOT NULL,
	manager_id INT (11) DEFAULT NULL,
	department_id INT (11) DEFAULT NULL,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

CREATE TABLE dependents (
	dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	relationship VARCHAR (25) NOT NULL,
	employee_id INT (11) NOT NULL,
	FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

creat per Joan Quintana Compte, desembre 2021

Obtingut de «http://wiki.joanillo.org/index.php?title=Modificant_les_dades:_insert,_update,_delete&oldid=298117»