eU5TSSVa

1-- Ejercicios creación de tablas mysql - Juan José Martínez Solano - 29/11/2019
2
3-- Creación de esquema
4CREATE SCHEMA IF NOT EXISTS noviembre DEFAULT CHARACTER SET utf8 ;
5CREATE USER LazyReflexes@172.18.0.1 IDENTIFIED BY 'PepeGrillo';
6
7GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, REFERENCES, CREATE TEMPORARY TABLES ON noviembre.* TO 'LazyReflexes'@'172.18.0.1';
8USE noviembre;
9
10-- Valores únicos 1
11CREATE TABLE IF NOT EXISTS tabla (
12  id INT NOT NULL AUTO_INCREMENT,
13  nombre VARCHAR(55) NULL,
14  PRIMARY KEY (`id`),
15  UNIQUE INDEX nombre_UNIQUE (`nombre` ASC))
16  ENGINE = InnoDB;
17
18INSERT INTO tabla(nombre) values ('test');
19INSERT INTO tabla(nombre) values ('test'); -- Error, no se permite introducir el mismo valor dos veces en la tabla
20INSERT INTO tabla(nombre) values (NULL);
21INSERT INTO tabla(nombre) values (NULL); -- Se permite introducir el valor NULL indefinidamente
22
23select * from tabla;
24
25-- Valores únicos 2
26CREATE TABLE IF NOT EXISTS tabla2 (
27  id INT NOT NULL AUTO_INCREMENT,
28  nombre VARCHAR(55) NOT NULL,
29  PRIMARY KEY (`id`),
30  UNIQUE INDEX nombre_UNIQUE (`nombre` ASC))
31  ENGINE = InnoDB;
32
33INSERT INTO tabla2(nombre) values ('test2');
34INSERT INTO tabla2(nombre) values ('test2'); -- Error, no permite duplicados
35INSERT INTO tabla2(nombre) values (NULL); -- Error, no se permite NULL
36
37select * from tabla2;
38
39-- Valores únicos 3
40
41/*
42CREATE TABLE IF NOT EXISTS puestos (
43  `id` INT NOT NULL AUTO_INCREMENT,
44  `puesto` VARCHAR(45) NULL,
45  `nombre` VARCHAR(45) NULL,
46  `trabajador` varchar(128) AS (IF(puesto<nombre,CONCAT(puesto,nombre),CONCAT(nombre,puesto))),
47  PRIMARY KEY (`id`),
48  UNIQUE KEY trabajador(trabajador))
49ENGINE = InnoDB;
50*/
51
52CREATE TABLE IF NOT EXISTS puestos (
53  `id` INT NOT NULL AUTO_INCREMENT,
54  `puesto` VARCHAR(45) NULL,
55  `nombre` VARCHAR(45) NULL,
56  UNIQUE (puesto, nombre),
57  PRIMARY KEY (`id`))
58  ENGINE = InnoDB;
59  
60insert into puestos (puesto, nombre) values ('oficinista', 'Pepe');
61insert into puestos (puesto, nombre) values ('oficinista', 'Pepe'); -- No admite una entrada duplicada.
62
63insert into puestos (puesto) values (NULL);
64insert into puestos (puesto) values (NULL); -- Admite el valor NULL indefinidamente
65insert into puestos (nombre) values (NULL);
66insert into puestos (nombre) values (NULL); -- Admite el valor NULL indefinidamente
67
68insert into puestos (puesto, nombre) values (NULL, NULL);
69insert into puestos (puesto, nombre) values (NULL, NULL); -- Admite el valor NULL en ambas columnas indefinidamente
70
71select * from puestos;
72
73-- Valor por defecto
74CREATE TABLE IF NOT EXISTS persona (
75  `f_nacimiento` DATE NULL DEFAULT '2199-12-31',
76  `id` INT NOT NULL AUTO_INCREMENT,
77  `nombre` VARCHAR(45) NOT NULL,
78  PRIMARY KEY (`id`))
79ENGINE = InnoDB;
80
81insert into persona(nombre) values ('Persona Test 1');
82insert into persona(nombre, f_nacimiento) values ('Persona Test 2', NULL); -- Ajusta el valor de f_nacimiento a NULL
83
84select * from persona;
85
86-- checks 1
87
88CREATE TABLE IF NOT EXISTS `nota` (
89  `id` INT NOT NULL AUTO_INCREMENT,
90  `nombre` VARCHAR(45) NOT NULL,
91  `nota` INT NOT NULL CHECK (nota >= 0 and nota <= 10),
92  PRIMARY KEY (`id`))
93  ENGINE = InnoDB;
94  
95insert into nota (nombre, nota) values ('Pepe', 7);
96insert into nota (nombre, nota) values ('Pepe', 11); -- Error, el valor de nota supera el límite establecido por CHECK
97
98desc nota;
99
100show create table nota;
101
102show tables; -- sólo sirve después de usar el use
103
104show databases;
105
106select * from coche;
107
108select * from persona;
109
110show create table coche;
111
112update persona set nombre = 'Juan' where id = 2;
113
114update persona set nombre = 'Antonio' where id = 3;
115
116insert into coche (marca, modelo, persona_id)
117values ('seat', 'ibiza', 1);
118
119insert into coche (marca, modelo, persona_id)
120values ('renault', 'megane', 1);
121
122delete from persona where id = 1; -- error por restricción
123update persona set id=7 where id = 1; -- error por restricción
124
125CREATE TABLE IF NOT EXISTS `noviembre`.`coche` (
126  `id` INT NOT NULL AUTO_INCREMENT,
127  `marca` VARCHAR(45) NULL,
128  `modelo` VARCHAR(45) NULL,
129  `persona_id` INT NOT NULL,
130  PRIMARY KEY (`id`),
131  INDEX `fk_coche_persona_idx` (`persona_id` ASC),
132    FOREIGN KEY (`persona_id`)
133    REFERENCES `noviembre`.`persona` (`id`)
134    ON DELETE NO ACTION
135    ON UPDATE NO ACTION)
136ENGINE = InnoDB;
137
138alter table coche drop foreign key `fk_coche_persona`; -- quitamos la restricción (se pone entre comillas el constraint)
139
140alter table coche -- modificamos la acción al borrar, eliminando la restricción y haciendo "cascada"
141	add CONSTRAINT `fk_coche_persona`
142	FOREIGN KEY (`persona_id`)
143    REFERENCES `noviembre`.`persona` (`id`)
144    ON DELETE cascade
145    ON UPDATE cascade; -- si eliminamos una persona, los coches de essa persona se eliminan
146    
147select * from coche;
148
149delete from persona where id=1;
150
151select * from persona;