Introducción a MySQL 8.0 (actualizado a MariaDB 11.4)

La forma que crearemos y manipularemos la información es por medio de un lenguaje llamado SQL (Structured Query Lenguage), diseñado por IBM en los años setenta.

CREATE TABLE nombre (definición de columnas)

Los tipo de las columnas ya se vieron en el capítulo anterior, pero los principales son:

1. Integer: Número entero con rango de -2,147,483,648 y 2,147,483,647

2. VarChar(n): Es una cadena con longitud variable, con un máximo de 255 caracteres. El parámetro n marca el máximo de la longitud esperada.

3. Text: Campo de texto que puede almacenar hasta 65,535 caracteres.

4. MediumText: Campo de texto que permite almacenar hasta 16,777,215 caracteres.

5. Datetime: Guarda una fecha en el formato YYYYDDMMHHMMSS

6. Timestamp: Tipo de campo que almacena la fecha actual en formato YYYDDMMHHMMSS

Crear una tabla

  La sentencia para crear una tabla es:

CREATE TABLE nombre (definición de columnas)

  Los tipo de las columnas ya se vieron en el capítulo anterior, pero los principales son:

  1. Integer: Número entero con rango de -2,147,483,648 y 2,147,483,647    2. VarChar(n): Es una cadena con longitud variable, con un máximo de 255 caracteres. El parámetro n marca el máximo de la longitud esperada.

Por medio del comando SHOW TABLES para visualizar las tablas de una base de datos.

Para insertar datos a una tabla, necesitamos la siguiente instrucción:

INSERT INTO tabla (columnas) VALUES (valores); Por ejemplo, para insertar un registro en nuestra tabla de contactos utilizaríamos la siguiente sentencia:

INSERT INTO alumnos (nombre, apellido, nacimiento, promedio, sexo, idEscuela, idSalon, idCurso)    

        VALUES ("Paco", "Arce", "1966-02-10", 8, "M", 1, 1,1);

Otra forma de insertar datos es por medio de un archivo de tipo texto que contiene las instrucciones SQL.  Por ejemplo:  En un procesador de palabra que no maneje caracteres especiales teclee las siguientes instrucciones: 

INSERT INTO escuelas VALUES(0,"Escuela 1"); 

INSERT INTO escuelas VALUES(0,"Libre de Derecho"); 

INSERT INTO escuelas VALUES(0,"Escuela 2"); 

INSERT INTO escuelas VALUES(0,"Abierta de economía");

La tercera sintaxis de la sentencia INSERT  podemos insertar varios registros a la vez.

En muchas ocasiones es necesario modificar los datos que ya se encuentran en nustras tablas. Para ello contamos con la siguiente sentencia: 

UPDATE [LOW_PRIORITY] tabla  SET columna=valor [,columna2=valor2...]  [WHERE condición]  [LIMIT n]

Para borrar un registro, o grupos de registros, se utilizará la sentencia DELETE:  DELETE [LOW_PRIORITY] FROM tabla  [WHERE condicion]  [LIMIT n]

Por medio de la sentencia LOAD DATA podemos cargar datos tabulares sin el formato SQL.

• Podemos limpiar una tabla con TRUNCATE TABLE tabla;

• No hay forma de recuperar los registros, a menos que tengamos un respaldo.

• Podemos ver las condiciones de una tabla con SHOW CREATE TABLE tabla.

Por medio de la función LOAD DATA podemos cargar datos en formato CSV

• Podemos seleccionar una base de datos desde la entrada a mysql:

• mysql -u root -p baseDatos

• Podemos importar un archivo desde la entrada a mysql con:

• mysql -u root -p baseDatos < archivo.sql

• Si añadimos una sentencia “use” como la primera sentencia del archivo, podemos omitirla de la línea de comando.

En esta clase encontrarás los apuntes de la sección.

Para consultar los datos utilizaremos la sentencia SELECT como se muestra a continuación en su versión simplificada:  SELECT [DISTINCT | ALL | otras_opciones] lista_selección  [INTO OUTFILE 'nombre_archivo' opciones_exportación]  FROM tabla_origen [, tabla_origen...]  [WHERE condición]  [LIMIT m,n];  [GROUP BY expresión_de_agrupación]  [HAVING condición_de_búsqueda]  [ORDER BY expresión_order_by [ASC | DESC]]

Especifica una condición de búsqueda para restrigir las filas que se van a devolver. Su sintaxis es la siguiente:

[WHERE condición_de_búsqueda | nombre de columna {*= | =*} nombre de columna]

El primer argumento "condición de búsqueda" limita las dilas devueltas en el conjunto de resultados mediante el uso de predicads.

El segundo argumento "nombre de columna {*= | =*} nombre de columna" funcionará para las combinaciones, las cuales se verán más adelante.

Por medio de la sentencia WHERE podemos consultar simultáneamente más de una tabla. Por ejemplo, en la base de datos el campo "idEscuela" de la tabla "alumnos" es la llave para unir la descripción de la escuela en la tabla "escuelas". Para unirlas debemos igualar en la sentencia WHERE idEscuela=id

Especifica las agrupaciones que se van a realizar en las dilas de salida y, en caso de incluir funciones de agregado como COUNT o MAX en la cláusula SELECT lista_selección calcula el valor de resumen de cada grupo.

Especifica las agrupaciones que se van a realizar en las dilas de salida y, en caso de incluir funciones de agregado como COUNT o MAX en la cláusula SELECT lista_selección calcula el valor de resumen de cada grupo.

Especifica las agrupaciones que se van a realizar en las dilas de salida y, en caso de incluir funciones de agregado como COUNT o MAX en la cláusula SELECT lista_selección calcula el valor de resumen de cada grupo.

La cláusula LIMIT nos permite seleccionar un conjunto de los renglones regresados por el select. Esto es de mucha utilidad cuando estamos mostrando registros por Internet, ya que nos permite "paginar" la selección y no saturar ni al cliente ni al servidor. Su sintaxis es:

[LIMIT n]

[LIMIT m,n]

IN efectúa una selección entre un conjunto de posibles resultados. También se tiene su contraparte NOT IN.

Operador condicional muy importante para seleccionar entre eun rango de columnas numéricas es el BETWEEN AND.

Por medio de esta comparación podemos buscar patrones dentro de las cadenas, ya que se podemos utilizar comodines, por ejemplo:

En esta clase analizaremos la función de agregado IS NULL

Las funciones de agregado realizan un cálculo sobre un conjunto de valores y devuelven un solo valor.

Con la excepción de COUNT, las funciones de agregado omiten los valores NULL.

Las funciones de agregado sólo se aceptan como expresiones en la lista de selección de una instricción SELECT.

Las funciones de agregado sólo se aceptan como expresiones en la lista de selección de una instricción SELECT.

Funciones de cadena: SUBSTR(), TRIM(), LTRIM() y RTRIM()

Funciones de cadena: SUBSTR(), TRIM(), LTRIM() y RTRIM()

Funciones de cadena: REPEATE(), REVERSE(), REPLACE()

Por medio de los operadores lógicos AND y OR podemos realizar selecciones más complejas con la sentencia SELECT.

En esta clase encontrarás los apuntes de la sección.

MySQL tiene varias funciones para el manejo de fechas que nos pueden ser de mucha utilidad. Aquí mostramos algunas de ellas.

La función DATE_ADD(fecha, INTERVAL expr unidad) añade o suma tiempo a una fecha.

• SELECT fechaPago,

• DATE_ADD(fechaPago, INTERVAL 45 DAY) AS vencimiento

• FROM pagos

• La función DATEDIFF() regresa el tiempo entre dos fechas.

• La función DATE_FORMAT() se utiliza para cambiar el formato de la fecha y de la hora.

Las funciones DATE(), MONTH() y YEAR()

Las funciones NOW y TIME para el manejo de las horas, minutos y segundos de una fecha.

Construir una fecha con MAKEDATE()

Funciones SEC_TO_TIME(), STR_TO_DATE(), TIME_FORMAT() y TIME_TO_SEC()

Las funciones para el manejo de semanas: WEEK, WEEKDAY, WEEKOFYEAR y YEARWEEK

En esta clase encontrarás los apuntes de la sección.

MySQL soporta las siguientes sintaxis de JOIN para la parte “tablas” de comandos SELECT y DELETE y UPDATE de múltiples tablas.

La cláusula LEFT JOIN regresa todos los renglones de la tabla izquierda (tabla1) que coincidan con los renglones de la tabla derecha (tabla2).

Si no hay coincidencias, se regresa un valor nulo.

La cláusula RIGHT JOIN regresa todos los renglones de la tabla2 (tabla derecha), que coincidan con los renglones de la tabla izquierda (tabla1).

El resultado es nulo (NULL) si el la tabla izquierda no hay coincidencia

La cláusula RIGHT JOIN regresa todos los renglones de la tabla2 (tabla derecha), que coincidan con los renglones de la tabla izquierda (tabla1).

El resultado es nulo (NULL) si el la tabla izquierda no hay coincidencias.

Simular la sentencia FULL OUTTER JOIN

Crear una tabla a partir de una sentencia SELECT

Crear un subquery

Utilizar la sentencia EXISTS en un subquery

• Una tabla derivada es una tabla virtual devuelta desde una declaración SELECT.

• Una tabla derivada es similar a una tabla temporal, pero se usa una tabla derivada en la instrucción SELECT.

• MySQL introdujo la expresión de tabla común o la función CTE desde la versión 8.0.

• Una expresión de tabla común es un conjunto de resultados temporal denominado que existe sólo dentro del ámbito ejecución de una instrucción SQL única por ejemplo, SELECT, INSERT, UPDATE, o DELETE.

• Una consulta inicial que forma el conjunto de resultados base de la estructura CTE. La parte de consulta inicial se denomina miembro de anclaje.

• Una parte de consulta recursiva es una consulta que hace referencia al nombre CTE, por lo tanto, se llama miembro recursivo. El miembro recursivo se une con el miembro de anclaje mediante un operador UNION ALL u UNION DISTINCT.

• Una tarea muy común en el desarrollo de aplicaciones: si tenemos una tabla “maestra”, por ejemplo la comanda de un restaurante, debemos sumarizar y actualizar los consumido (los platillos).

• Para ello debemos crear primero un select y el resultado lo actualizaremos en la tabla maestra por medio de un inner join.

Para seleccionar todos los registros de una tabla, excluyendo los que también existen en otra tabla, utiliza una subconsulta NOT IN o un LEFT JOIN con IS NULL, especificando la condición de coincidencia entre ambas tablas, generalmente usando una columna común como el ID.

• Para crear una vista en MySQL, usa la sintaxis

• CREATE VIEW nombre_vista AS consulta;

• Esta sentencia crea una tabla virtual basada en el resultado de la consulta, que puede incluir una o varias tablas, simplificando así consultas complejas.

Las funciones de ventana en MySQL son una característica avanzada que realiza cálculos sobre un conjunto de filas de tabla relacionadas con la fila actual, sin reducir el resultado a una sola fila.

Las Expresiones de Tabla Comunes (CTE) son un conjunto de resultados temporal con nombre que existen dentro de una única sentencia SQL (SELECT, INSERT, UPDATE o DELETE).

En esta clase encontrarás los apuntes de la sección.

Después de crear una tabla, es posible cambiar muchas de las opciones que fueron definidas cuando se creó originalmente, por ejemplo:

Agregar, modificar o eliminar columnas.

Así se puede cambiar el nombre, longitud, tipo de datos, la precisión, la escala y la aceptación de valores NULL de la columna, aunque existen algunas restricciones.

CHANGE especifica que la columna dada va a cambiarse de nombre_columna a nombre_columna_nuevo, o a modificarse al nuevo tipo de datos (si es que los nombres de las columnas coinciden).

Para ello usaremos la sentencia ADD [COLUMN], donde la declaración de columna comprende el nombre de la columna, así como su longitud, tipo y otras características, como UNSIGNED, NOT NULL, etc

Para eliminar una columna necesitamos la sentencia DROP [COLUMN] nombre_columna.
Si esta columna es parte del indice, se elimina del indice sin borrar este.
Si elimina todas las columnas del indice, este también se borra.

Renombrar una tabla con ALTER TABLE

Los comandos SHOW COLUMNS TABLE y EXPLAIN

Cambio de un valor predeterminado con ALTER TABLE

La sentencia ALTER TABLE y los índices

En esta clase encontrarás los apuntes de la sección.

• Las bases de datos también necesitan índices para realizar su búsqueda de forma mucho más optimizada.

• Cuando crear una tabla, te pedirá que un campo sea la “llave principal” o “primary key”.

• Creamos el índice “primary key” o “de clave primaria” al momento de crear la tabla.

• En este caso se usa la opción PRIMARY KEY al final de la definición de los campos, con una lista de los campos que serán parte del índice.

• Se les llama así a los indices que permiten que sus llaves de dupliquen.

• Por ejemplo, un índice con las columnas nombre+apellidos se podría duplicar con personas que tuvieran exactamente los mismos nombres y apellidos.

• Los índices únicos son básicamente como los índices ordinarios, excepto que los valores duplicados no son permitidos.

• Un índice FULLTEXT está diseñado para facilitar y optimizar la búsqueda de palabras clave en tablas que tienen grandes cantidades de información en campos de texto.

• En las columnas CHAR y VARCHAR se nos permite crear un índice que no use el campo por completo.

• Por ejemplo, a pesar de que el nombre de una persona puede ser de hasta 50 caracteres, es muy común que los nombres de las personas sean diferentes en los primeros 10 caracteres.

• En las columnas CHAR y VARCHAR se nos permite crear un índice que no use el campo por completo.

• Por ejemplo, a pesar de que el nombre de una persona puede ser de hasta 50 caracteres, es muy común que los nombres de las personas sean diferentes en los primeros 10 caracteres.

Usando índices efectivamente en MySQL

En esta clase encontrarás los apuntes de la sección.

• Una cuenta MySQL se define en términos de un nombre de usuario y el equipo o equipos desde los que el usuario puede conectar al servidor.

• Aunque en la versión 5.0.2 de MySQL existe una sentencia para crear usuarios, CREATE USER, en versiones anteriores se usa exclusivamente la sentencia GRANT para crearlos.

Podemos limitar los accesos, las consultas entre otras actividades.

• Podemos ver qué privilegios se han concedido a un usuario mediante la sentencia SHOW GRANTS.

• La salida de esta sentencia es una lista de sentencias GRANT que se deben ejecutar para conceder los privilegios que tiene el usuario.

• Para revocar privilegios se usa la sentencia REVOKE.

• Para eliminar usuarios se usa la sentencia DROP USER.

• Para eliminar el usuario es una buena práctica revocar primero todos sus privilegios:

En esta clase encontrarás los apuntes de la sección.

La definición de las llaves foráneas con FOREIGN KEY

• FOREIGN KEY (idSalario, categoriaSalario)

• REFERENCES salarios(id, categoria),

• FOREIGN KEY (idCursos)

• REFERENCES cursos(id)

• MySQL permite crear una tabla con las opciones CASCADE y RESTRICT.

• La opción CASCADE elimina o actualiza la fila de la tabla primaria (que contiene LLAVES PRIMARIAS) y elimina o actualiza automáticamente las filas coincidentes en la tabla secundaria (que contiene LLAVES FORÁNEAS).

• ALTER TABLE tabla

• ADD [CONSTRAINT [symbol]]

• FOREIGN KEY [índice] (col_name, ...)

• REFERENCES tabla2(col_name,...)

• [ON DELETE opcion]

• [ON UPDATE opcion]

• ALTER TABLE alumnos

• DROP FOREIGN KEY indice;

• MySQL le permite crear una tabla con la opción SET NULL. Al hacerlo, se eliminará o actualizará la fila de la tabla primaria y se establecerá la columna o columnas de llave foránea en la tabla secundaria en NULL.

• Puede usar SET NULL para DELETE y UPDATE.

• La restricción NOT ACTION funciona como RESTRICT. Es compatible con SQL estándard.

• SET DEFAULT no lo reconoce en INNODB.

A partir de MySQL 8.0.16, acepta las restricciones CHECK de tabla y columna para todos los motores de almacenamiento.

En esta clase encontrarás los apuntes de la sección.

• Para bloquear el acceso a una o varias tablas necesitamos la sentencia

• LOCK TABLES nombre_tabla1 [AS alias] {READ | [LOW PRIORITY] WRITE}[, nombre_tabla2 ...]

• Para cambiar la contraseña de un usuario, necesitamos la sentencia SET, por ejemplo

• SET PASSWORD FOR root@localhost = "hola";

• Para optimizar el espacio de una tabla necesitamos el comando

• OPTIMIZE TABLE nombre_tabla;

• Con ellos se compactará lo más posible la tabla.

Es importante hacer copias de seguridad de sus bases de datos por si sus tablas se pierden o se deterioran.

• Crear una base de datos

• Cargar el archivo de respaldo

• mysql -u root -p escuela_bk < escuela_dump.sql

En esta clase encontrarás los apuntes de la sección.