Home » MySQL 8: lenguaje y diseño de datos

MySQL 8: lenguaje y diseño de datos

MySQL 8 

Lenguaje y diseño de bases de datos

Publicado Mayo/2021

364 págs.

Versión impresa y kindle

MySQL es el sistema de gestión de bases de datos de código abierto más popular del mundo.

En este libro se enseñan todos los conceptos fundamentales para dominar la codificación del lenguaje MySQL y el diseño y modelado de bases de datos con ejemplos completos y reales.

Con este libro se aprenderá: el lenguaje SQL, el diseño y modelado, la programación avanzada y la administración de las bases de datos.

Detalladamente explicados y con muchos ejemplos se tratan estos temas:

  • Cómo utilizar WorkBench y la consola para gestionar MySQL.
  • Cómo utilizar todas las sentencias SQL para crear, consultar, actualizar y administrar bases de datos.
  • Cómo son los tipos de datos que admite MySQL.
  • Cómo realizar cargas iniciales de datos y copias de seguridad.
  • Cómo realizar consultas complejas con combinaciones de tablas y vistas.
  • Cómo diseñar una base de datos paso a paso, desde el requerimiento funcional.
  • Cómo elegir las claves primarias y los índices adicionales.
  • Cómo saber el rendimiento de una consulta a la base de datos.
  • Cómo detectar las entidades que conformarán una base de datos.
  • Cómo normalizar una base de datos.
  • Cómo crear diagramas Entidad/Relación y cómo llegar al modelo físico.
  • Cómo crear claves externas para asegurar la integridad de datos.
  • Cómo crear vistas y sacarle provecho a las subconsultas.
  • Cómo utilizar transacciones y bloqueos.
  • Cómo crear procedimientos almacenados y funciones.
  • Cómo implementar desencadenantes (triggers) y eventos.
  • Cómo realizar tareas de administración de usuarios y la concesión de privilegios.
  • Y mucho más…
Tabla de contenido

Capítulo 1: Primeros pasos 19
¿Qué es una base de datos? 19
MySQL 19
Obtención del software 20
Conexión con el servidor 30
Otros modos de conexión 32
Reconfiguración del servidor 32
Un paseo por MySQL Workbench 33
Objetos de una bases de datos 38

Capítulo 2: Conceptos de bases de datos 43
Definición de base de datos 43
Motivos que justifican el uso de una base de datos 44
Sistemas de gestión de base de datos 44
Bases de datos relacionales 45
Modelo de base de datos relacional 45
Diseño de base de datos relacionales 45
Pasos en un diseño de una base de datos 46
Definir precisamente el requerimiento 47
Modelo conceptual 47
Diagrama conceptual 48
Modelo lógico 49
Relaciones 49
Elección del tipo de relación 50
Entidad asociada 50
Modelo físico 51
Establecer las relaciones 54
Unos conceptos básicos antes de revisar el modelo 55
¿Cómo decido qué datos van en cada tabla? 55
Claves 55
Clave primaria 55
Clave externa o foránea 56
Índices 56
Revisión del modelo 58
Ingeniería Modelo -> BD 59
Generación automática del script sql 60
Fichas informativas de Workbench. 62
Ingeniería inversa: BD -> Modelo 63
Integridad de datos 65
Integridad de entidad 65
Integridad referencial 65
Modificación y sincronización del modelo 66
Integridad de dominio 69
Normalización 69
Formas normales 69
Primera forma normal 70
Segunda forma normal 71
Tercera forma normal 72
Forma normal Boyce-Codd 73
Cuarta forma normal 73
Quinta forma normal 74
Desnormalización 74
Cuestionario 75

Capítulo 3: Introducción al lenguaje SQL 77
MySQL y SQL 77
Una rápida revisión de las sentencias SQL 78
Sentencias de definición 78
Sentencias de consulta 78
Sentencias de manipulación 78
Sentencias de control de datos 79
Sentencias de control de transacciones 79
Creación y uso de una base de datos 79
Revisión de la base de datos de prueba 79
Cómo obtener información de la base de datos con Workbench 80
Modelo entidad-relación 83
Símbolos en un modelo entidad-relación 83
Diagrama EER de la base de ejemplo 84
Cuestionario 86

Capítulo 4: Sentencias de definición de datos: DDL 87
Sentencias CREATE/ALTER/DROP 87
Sentencia Create Database: creación de una base de datos 88
Conjunto de caracteres CHARSET 88
Motor de almacenamiento de base de datos 89
Creación de la base de datos 90
¿Dónde se ha creado físicamente la base de datos? 91
Eliminación de la base de datos 92
Sentencia Create Table: creación de una tabla 92
Creación de una tabla por copia 94
Creación con copia de definición 94
Creación con copia de definición y contenido 94
Eliminación de una tabla 95
Sentencia Create View: creación de una vista 95
Uso restringido de las vistas en operaciones de actualización 97
WITH CHECK OPTION 97
Definición de opciones de restricciones de integridad 98
Verificación del contenido de una columna (CHECK) 98
Dar un nombre a una restricción (CONSTRAINT) 99
Definir una valor predeterminado (DEFAULT) 99
Exigir que un campo no quede vacío (NOT NULL) 99
Verificar la unicidad de un valor en una tabla (UNIQUE) 100
Definición de claves e índices de una tabla 100
Clave primaria 101
Clave externa 102
ON DELETE/ON UPDATE 103
Creación de índices 103
Cuándo crear un índice 104
Análisis de la conveniencia de un índice 106
Cuándo actúa el índice 107
Cómo saber el rendimiento de una consulta 108
Uso de filesort 109
Costo de cada consulta 109
FULLTEXT: índice para buscar dentro de textos 110
Índices compuestos 114
Duplicación involuntaria de índices 115
Modificación de la definición de las tablas 116
Sentencia Rename: cambio de nombre de la tabla 116
Sentencia Alter: modificación de tablas 116
Añadido de columnas 116
Modificación de la definición de la columna 118
Eliminación de columnas 119
Sentencia Drop: eliminación de elementos de una base de datos 119
Eliminación de base de datos 119
Eliminación de tabla 120
Eliminación de índice 120
Cambio de una clave externa 120
Sentencia TRUNCATE TABLE: vaciado de la tabla 120
Información acerca de los objetos de la base de datos 121
Motores de almacenamiento 122
Motor de almacenamiento MEMORY 123
Motor de almacenamiento Merge 124
Motor de almacenamiento MyISAM 125
Motor de almacenamiento InnoDB 125
Cuestionario 126

Capítulo 5: Tipos de datos 129
Tipos de columnas soportados por MySQL 129
Tipos numéricos 129
Enteros 129
Decimales 130
Siempre valores exactos 130
Con posibles valores aproximados 131
Booleanos 131
Tipo de caracteres y cadena de caracteres 132
CHAR y VARCHAR 132
TEXT 133
ENUM 134
SET 135
Tipo BLOB 135
Tipos de fecha y hora 136
Consideraciones generales de la fecha y hora 136
DATE 137
TIMESTAMP 138
TIME 139
YEAR 139
Tipo JSON 140
Tipos de valores en JSON 140
Ejemplo del uso de las columnas JSON 141
Inserción de un tipo JSON 141
Modificación del dato de tipo JSON 142
Extracción de datos de un objeto JSON 143
Extracción de un elemento de un array 143
Extracción de un objeto anidado 144
Modificación parcial de un objeto JSON 145
Acceso a partes de un array 146
Tipo Spatial 146
Sistema de referencia espacial 147
Por un momento volvemos al plano 148
Gestión de puntos geográficos 149
Cálculo de distancias entre puntos 150
¿Adentro o afuera de un área? 151
Otras funciones para trabajar con relaciones geométricas 152
Atributos de las columnas según el tipo de dato 153
AUTO_INCREMENT 153
Cuestionario 154

Capítulo 6: Sentencias de consultas 157
Sentencia SELECT 158
Expresiones matemáticas en la consulta 160
Precedencia en el cálculo de una fórmula 162
ALIAS: asignación de nombres significativos 162
DISTINCT: eliminación de duplicados en la lista resultante 163
WHERE: Filtrado de filas 163
Condiciones de filtrado compuestas con AND, OR o NOT 164
Prioridad de operadores AND/OR 165
LIMIT: Limitación del conjunto de respuesta 166
Operadores auxiliares para el filtrado 167
Operador IN 167
Operador BETWEEN 168
Operador IS NULL 168
Operador LIKE 169
REGEXP: Uso de expresiones regulares 170
Order by: Ordenamiento de los resultados 173
Filtrado en campos de tipo fecha/hora 175
Subconsultas 175
Subconsultas en cláusula WHERE 176
Subconsultas en cláusula FROM 177
Subconsultas repetitivas 177
Palabra clave ALL 178
Palabras claves ANY o SOME 179
Palabra clave IN 180
Palabra clave EXISTS/ NOT EXISTS 180
SELECT… INTO 180
Combinación de tablas 181
INNER JOIN 182
JOIN sin usar la cláusula JOIN 184
Combinación de tablas múltiples en INNER JOIN 185
OUTER JOIN 187
Combinación de tablas múltiples en OUTER JOIN 190
USING 194
NATURAL JOIN 194
CROSS JOIN 195
UNION 195
JOIN entre tablas de diferentes bases de datos 197
JOIN versus subconsulta 197
Sentencia TABLE 198
Funciones 198
Funciones de agregación 199
Función COUNT() y COUNT(DISTINCT) 199
Función AVG() 199
Función MAX() 200
Función MIN() 200
Función SUM() 200
GROUP BY/HAVING: agrupamiento de los resultados 201
WITH ROLLUP: modificador de la cláusula GROUP BY 203
ORDER BY: ordenamiento de los resultados 205
Funciones matemáticas 205
Operaciones sobre columnas de una fila 207
Funciones lógicas condicionales 207
Función IF() 207
CASE 208
IFNULL() 209
COALESCE() 210
Funciones de cadenas de caracteres 210
CONCAT() 211
LEFT(), RIGTH() 211
LENGTH() 212
LTRIM(), RTRIM() 212
LOCATE() 212
SUBSTRING() 213
TRIM() 213
REPLACE() 214
REVERSE() 214
UCASE() o UPPER(), LCASE() o LOWER() 215
Funciones de fecha y hora 215
Funciones y variables para obtención de datos de fecha y hora 215
Funciones para cálculos con fecha y hora 218
Funciones para dar formato a un dato de fecha y hora 223
Do: ejecución de expresiones 225
Handler: consultas avanzadas 226
Cuestionario 228

Capítulo 7: Sentencias de actualización 231
Copia de la base de datos 231
Restauración de una base de datos 233
Sentencias de actualización de datos 233
Sentencia Insert 233
Inserciones múltiples 237
Clave primaria por autoincremento 238
Inserción con SELECT 240
Inserción en vistas 241
Sentencia Replace: otro tipo de inserción 241
Sentencia Update 242
Modificación de una única fila 242
Modificación incondicional 243
Modificación de múltiples filas 244
Modificación en múltiples tablas 245
Modificación con IN SELECT 246
Sentencia Delete 246
Eliminación de una única fila 247
Eliminación incondicional 247
Eliminaciones en tablas múltiples 248
Eliminación de múltiples filas 248
Eliminación con SELECT 249
Otras sentencias de manipulación de datos 249
Sentencia LOAD DATA: inserción rápida desde archivos de texto 249
Cuestionario 251

Capítulo 8: Procedimientos y funciones 253
Procedimientos almacenados 253
Creación de procedimientos almacenados desde MySQL Workbench 254
Eliminación del procedimiento almacenado 256
Invocación del procedimiento almacenado 256
Múltiples sentencias SQL 257
Pase de parámetros a un procedimiento almacenado 257
Parámetros de salida 258
Parámetros con valor por defecto 259
Validación de parámetros 259
Uso de variables 261
Sentencia SET 261
Sentencia SELECT … INTO 262
Sentencia DECLARE 262
Otras declaraciones con DECLARE 264
Declaración de condiciones 264
Declaración de manipuladores 265
Formatos de los comentarios en procedimientos almacenados 265
Estructuras de control de flujo en programas almacenados 265
Sentencia IF 266
Sentencia CASE 266
Sentencia LOOP 267
Sentencia LEAVE 267
Sentencia ITERATE 267
Sentencia REPEAT 267
Sentencia WHILE 268
Sentencia RETURN 268
Cursores 268
Abrir un cursor 269
Obtención de un conjunto de datos 269
Cierre del cursor 269
Procedimiento almacenado: ejemplo práctico 269
Visualización de los procedimientos almacenados de una base de datos 270
Funciones 271
Desencadenantes (triggers) 273
Eventos 276
Desactivación temporal de un evento 277
Cuestionario 277

Capítulo 9: Transacciones y bloqueos 279
Funcionamiento de las transacciones 279
Base de datos para practicar transacciones 280
Transacción de ingreso de dinero en una cuenta 281
Sobre el modelo ACID 282
Limitaciones de las transacciones 282
Transacciones y procedimientos almacenados 283
El efecto de la variable autocommit 283
Fuera de una transacción 283
Dentro de una transacción 283
Características de las transacciones 283
Concurrencia y bloqueos: caso práctico 284
Concurrencia de accesos 285
Niveles de aislamiento 286
Casuística de los accesos concurrentes 287
Pérdida de actualizaciones 287
Lecturas de datos no confirmados (dirty reads) 288
Lecturas repetidas pero diferentes 289
Lecturas fantasmas 290
Abrazo de la muerte 291
Bloqueos 291
LOCK TABLES: bloqueos de tablas 292
UNLOCK TABLES: desbloqueos de tablas 292
Interacción entre bloqueos y transacciones 292
Bloqueo con tablas transaccionales 292
Bloqueo en lecturas 293
Cuestionario 293

Capítulo 10: Sentencias de control 295
Control de accesos 295
El esquema mysql 296
Usuarios 296
¿Qué usuarios y privilegios tenemos definidos en el servidor? 297
Cambio de contraseña de un usuario 297
Forzar el cambio de contraseña 298
Drop user: eliminación de un usuario 298
Administración de privilegios 299
Sentencia GRANT 299
Comprobación de privilegios concedidos 300
Sentencia REVOKE 301
Niveles de privilegios 302
Tipos de privilegios 302
Precauciones al otorgar privilegios 303
Roles 304
Cuestionario 305

Capítulo 11: Programas desde línea de comandos 307
Programas MySQL 307
mysqld 308
Programas cliente 308
mysql 309
Primer uso 309
Forma de escritura de comandos 312
Opciones del programa mysql 312
Comandos de mysql 315
Ejemplos de uso de mysql 316
Conexión con el servidor 316
Cambio de contraseña 317
Desconexión del cliente 318
Creación de una base de datos 318
Creación de una tabla a partir de un archivo de sentencias (lote batch) 318
Estado del servidor y conexión en uso 318
Escritura en un archivo de registro 319
Otros ejemplos de los distintos comandos del cliente mysql 319
Cambio de usuario 320
Cuestionario 321

Capítulo 12: Modelado de datos en la práctica 323
Introducción 323
Resumen de pasos en un diseño de una base de datos 323
Descripción del requerimiento 324
Modelo conceptual 326
Herramienta de diagramado 327
¿Cómo puedo descubrir qué entidades necesito en mi base de datos? 327
La técnica de la simplificación 328
Cómo saber la importancia relativa de cada entidad 329
Propuesta inicial de entidades, atributos y tipos de datos 329
Entidades fuertes y débiles 330
Relaciones 330
Diagrama ER 331
Modelo lógico 333
Cardinalidad resultante de la relación 333
Resolución de las Relaciones 335
Entidades del modelo y claves foráneas: primera versión 335
Relación muchos a muchos 336
Entidades definitivas del modelo lógico y tipos de datos 337
Diagrama revisado 338
Diagrama del modelo lógico 340
Modelo físico 341
Creación del diagrama físico 341
Modelo físico: versión previa 342
Normalización 342
Revisión para la normalización 343
Primer problema detectado 343
Segundo problema detectado 344
Tercer problema detectado 345
Diagrama del modelo físico de la base de datos fla 346
Ingeniería Modelo -> BD 346
Carga inicial de la información de la base de datos 352
Entidades fuertes 352
Tabla actividades 352
Tabla usuarios 353
Tabla calendario 353
Tabla empresas 354
Entidades débiles 354
Tabla usuario_actividad 355
Tabla ingresos 355
Tabla gastos 356
Tablas corrientes 357
Tabla amortizables 357
Tabla detalle_amortizaciones 358
Tabla liquidaciones 358
Cuestionario 359