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: clau primària, clau forànea, NULL, UNIQUE

Restricció de clau primària

De la base de dades HR: fixem-nos amb la definició de la taula jobs: 

CREATE TABLE jobs (
	job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	job_title VARCHAR (35) NOT NULL,
	min_salary DECIMAL (8, 2) DEFAULT NULL,
	max_salary DECIMAL (8, 2) DEFAULT NULL
);

insert into jobs values(1,'programador', 2000, 5000);
ERROR 1062 (23000): Duplicate entry '1' for key 'jobs.PRIMARY'

insert into jobs values(20,'programador', 2000, 5000);
Query OK, 1 row affected (0.01 sec)

Restricció de clau forànea

De la base de dades HR: fixem-nos amb la definició de la taula employees: 

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)
);

mysql> select employee_id,first_name,job_id from employees;
...
|         206 | William     |      1 
...

mysql> update employees set job_id=20 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update employees set job_id=25 where employee_id=206;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`HR`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> update employees set job_id=1 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Restricció de NULL

De la taula job, està clar que la clau primaria és job_id. Si ens fixem en la definició de la taula, el job_title no pot ser NUL, i en canvi els salaris (màxim i mínim) sí que poden ser nuls.

Restricció de UNIQUE

Per trobar una restricció UNIQUE hem d'anar a la base de dades sakila. En el script sakila-schema.sql trobem la definició de la taula store: 

CREATE TABLE store (
  store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  manager_staff_id TINYINT UNSIGNED NOT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (store_id),
  UNIQUE KEY idx_unique_manager (manager_staff_id),
  KEY idx_fk_address_id (address_id),
  CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Fixem-nos en les dues línies: 

...
  manager_staff_id TINYINT UNSIGNED NOT NULL,
...
  UNIQUE KEY idx_unique_manager (manager_staff_id)
...

S'hagués pogut posar de forma més simplificada en una sola línia: 

  manager_staff_id TINYINT UNSIGNED NOT NULL UNIQUE,

Aquesta restricció ens està dient que el manager_staff_id no es pot repetir en aquesta taula. És a dir, que no pot haver-hi dues botigues amb el mateix manàger.

Anem a fer saltar aquesta restricció:

Només hi ha dues botigues i només hi ha dues persones en el staff: 

mysql> select store_id, manager_staff_id from store;
+----------+------------------+
| store_id | manager_staff_id |
+----------+------------------+
|        1 |                1 |
|        2 |                2 |
+----------+------------------+

mysql> select staff_id, first_name from staff;
+----------+------------+
| staff_id | first_name |
+----------+------------+
|        1 | Mike       |
|        2 | Jon        |
+----------+------------+

No podem fer que el manager_staff_id=1 sigui el manager de la botiga 1: 

mysql> update store set manager_staff_id=1 where store_id=2;
ERROR 1062 (23000): Duplicate entry '1' for key 'store.idx_unique_manager'

T4. 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=298121»