Funciones y operaciones en MySQL
Módulo 5
PreviousSistema de Gestión de Bases de Datos RelacionalMySQLNextOperaciones para la proyección de información MySQL
Last updated
Was this helpful?
Módulo 5
Last updated
Was this helpful?
Con el paso de esta semana el estudiante y lector se va acercando más y más al manejo del SGBD MySQL que le van permitiendo tener una mayor claridad en el manejo de una base de datos y todas las funciones necesarias para la manipulación de los datos almacenadas.
Esta módulo tiene como propósito trabajar las diferentes características con las que cuenta MySQL, además algunas operaciones para la manipulación de datos en este motor de base datos. En esta módulo también se le mostrará al lector el uso de Clever Cloud, plataforma que le ayude a tener una base de datos remota en la nube y de esta manera los proyectos que se deben construir cuenten con esta posibilidad, para trabajar sobre las mismas estructuras y datos almacenados.
Clever Cloud es una plataforma de automatización de TI para administrar todo el trabajo de operaciones de una problemática de un proyecto de software que desea implementar en la nube.
Clever Cloud es un proveedor de soluciones PaaS (Plataforma como servicio) optimizadas por tiempo de ejecución para tecnologías tales como: Java, Scala, Nodejs, MySQL, MogoDB, PHP, entre otros.
Clever Cloud servirá como servicio para tener alojada una base de datos en MySQL con la finalidad de tener centralizada esta y pueda servir a los equipos de trabajo en manipular la mismas estructuras y datos con las que cuente el proyecto.
Nube inteligente
Fundada en 2010
Prueba gratis
2. Clic en el botón Join with Github para crear una cuenta mediante GitHub y facilitar el registro.
3. Clic en el botón Authorize CleverCloud, con el cual se le permite a la plataforma tener acceso a los repositorios de la cuenta, debido que los proyectos en GitHub se pueden sincronizar para desplegarlos en esta plataforma.
4. Confirme la contraseña y con eso terminara la configuración de la cuenta.
5. Ingrese al correo con el cual tiene asociada la cuenta con GitHub, e ingrese al enlace que llega en este correo, donde se explica los permisos que se le otorgaron a la Clever Cloud.
7. Clic sobre el botón CONNECT de la sección GitHub Login, con lo cual podrá iniciar sesión con GitHub.
6. Una vez se confirma la contraseña en el punto 4 se puede ingresar a esta interfaz, donde se muestra el nombre, correo y se confirme los términos y condiciones de la plataforma. Dar clic sobre el botón CREATE MY ACCOUNT.
9. Completar los datos en este formulario. Luego sobre la sección Add an organization.
10. Clic sobre el botón + Create…. Seguido, aparecerá una sección donde seleccionaremos la opción an add-on.
11. Clic sobre el botón SELECT de la región de MySQL.
12. Elija el plan DEV, con el cual no se requiere pagar y se tendrá una cuenta de 10 MB de almacenamiento, suficiente para los proyectos que se manejan. Vaya al final de la página y clic sobre el botón NEXT.
13. Asigne un nombre al complemento que se va crear para la plataforma (este no es el nombre de la base de datos, si no, un nombre para el proyecto donde se pueden tener varias bases de datos, pero por el tipo de cuenta, solo se nos otorga el acceso a una sola base de datos). Además, seleccione la zona y por último clic en el botón NEXT.
14. En esta interfaz se pueden encontrar los datos de conexión, el nombre de la base de datos, usuario, contraseña y otras configuraciones con las cuales podemos realizar administración de la base de datos. Por último, dar clic sobre la sección PHPMyAdmin.
15. La interfaz de PHPMyAdmin es el mismo cliente explicado en el módulo anterior de base de datos y desde el cual se puede realizar la administración de la base de datos, creando las tablas y estructuras necesarias para el almacenamiento de la información. De igual manera se puede correr script’s SQL, dado que ya se haya creado previamente de manera local o en otro motor de MySQL.
Empezaremos a ver aspectos bastante técnicos y específicos de MySQL, uno de los primeros en los que haremos énfasis, será el motor de almacenamiento.
Un motor de almacenamiento es el encargado de almacenar, gestionar y recuperar toda la información de una tabla. Es por ello, es de suma importancia que nosotros conozcamos la existencia de estos motores; cuáles son sus principales diferencias y en qué casos es bueno utilizar uno u otro, de esta forma que podamos garantizar un mejor performance en nuestras aplicaciones.
Para que nosotros conozcamos que motor de almacenamiento podemos utilizar basta con ejecutar la siguiente sentencia en nuestra terminal.
Obtendremos el siguiente listado e información adicional del motor.
En este módulo nos concentraremos en MyISAM y InnoDB.
MyISAM es el motor por default de MySQL. Una de las principales ventajas de este motor es la velocidad al momento de recuperar información. MyISAM es una excelente opción cuando las sentencias predominantes en nuestra aplicación sean de consultas. Esta es una de las razones por las cuales MyISAM es tan popular en aplicaciones web.
La principal desventaja de MyISAM recae en la ausencia de atomocidad, ya que no se comprueba la integridad referencial de los datos. Se gana tiempo en la inserción, sí, pero perdemos confiabilidad en los datos.
InnoDB dota a MySQL de un motor de almacenamiento transaccional (conforme a ACID) con capacidades de commit (confirmación), rollback (cancelación) y recuperación de fallos. InnoDB incrementan el rendimiento y la capacidad de gestionar múltiples usuarios simultáneos. No se necesita un bloqueo escalado en InnoDB porque los bloqueos a nivel de fila ocupan muy poco espacio. InnoDB también soporta restricciones FOREIGN KEY.
La principal ventaja de este motor recae en la seguridad de las operaciones. InnoDB permite la ejecución de transacciones, esto nos garantiza que los datos se persisten de forma correcta y si existe algún error podamos revertir todos los cambios realizados.
Algo interesante a mencionar sobre InnoDB es que este motor realiza un bloqueo total sobre una tabla cuando es ejecutada una se las siguientes sentencias: Select, Insert, Update, y Delete.
InnoDB
MyISAM
· Soporte de transacciones
· Bloqueo de registros
· Nos permite tener las características ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad), garantizando la integridad de nuestras tablas.
· Es probable que si nuestra aplicación hace un uso elevado de INSERT y UPDATE notemos un aumento de rendimiento con respecto a MyISA
· Mayor velocidad en general a la hora de recuperar datos.
· Recomendable para aplicaciones en las que dominan las sentencias SELECT ante los INSERT /UPDATE.
· Ausencia de características de atomicidad ya que no tiene que hacer comprobaciones de la integridad referencial, ni bloquear las tablas para realizar las operaciones, esto nos lleva como los anteriores puntos a una mayor velocidad.
Si deseamos cambiar el motor de almacenamiento por defecto, lo podemos hacer desde la configuración. Existen dos formas de hacer esto. La primera, es modificar el archivo my.cnf o my.ini.
La segunda forma es hacerlo directamente desde nuestra sección, basta con ejecutar la siguiente sentencia.
Si deseamos crear una tabla utilizando un motor en particular, debemos seguir la siguiente estructura.
El charset es un conjunto de símbolos y codificaciones, es decir, la forma en que la base de datos guarda internamente los datos. Mientras que el collation es el conjunto de reglas que se aplican para comparar caracteres en un charset, es decir, indica a la base de datos como debe comparar los datos.
Suponga que tenemos un alfabeto de cuatro letras: A, B, a, b. Asignamos a cada letra un número: A = 0, B = 1, a = 2, b = 3. La letra A es el símbolo, el número 0 es el código para A y la combinación de las cuatro letras y sus códigos es el juego de caracteres (charset).
Suponga que queremos comparar dos cadenas de valores, A y B. La forma más sencilla de hacerlo es mirar sus códigos: 0 para A y 1 para B. Como 0 es menor que 1, diremos que A es menor que B. Lo que acabamos de hacer, es aplicar un collation a nuestro charset. El collation es un juego de reglas (en este caso solo una): “campara los códigos”. Llamamos a este collation, el más simple de todos los collation posible, collation binario (binary collation).
La codificación de datos más adecuada en la mayor parte de los casos es UTF-8 (alias de "utf8mb3"), ya que implementa todos los caracteres que se puedan usar en cualquier idioma, así como gran cantidad de símbolos matemáticos que, sin pertenecer a un idioma específico, son de uso relativamente común. Para implementar otros caracteres extremadamente especiales hay otras codificaciones adecuadas.
UTF-8 (Unicode Transformation Format-8) es un formato de codificación de caracteres, en el que cada carácter es representado por un octeto (8 bits). También existen UTF-16 y UTF-32. La ventaja de UTF-8 respecto a estos otros es que es compatible con versiones anteriores de ASCII.
Desde MySQL 5.5.3 se debería usar utf8mb4 en lugar de utf8. Ambos se refieren a la codificación UTF-8, pero la antigua utf8 tenía una limitación específica de MySQL que impedía el uso de caracteres numerados por encima de 0xFFFD (primeros 65,536 caracteres Unicode).
Se basa en el estándar Unicode para la clasificación y comparación, que clasifica con precisión en una amplia gama de idiomas. Se utiliza las reglas Unicode para ordenar y comparar, emplea un algoritmo bastante complejo para ordenar correctamente en una amplia gama de idiomas y cuando se utiliza una amplia gama de caracteres especiales. Estas reglas deben tener en cuenta las convenciones específicas de cada idioma; no todo el mundo ordena sus caracteres en lo que llamaríamos “orden alfabético”.
Veamos como se codifica la base de datos.
Al crear las tablas, a estas se les puede indicar la codificación si se requiere, en caso contrario, se deja vació para que tome por defecto la codificación asignada desde la creación de la base de datos.
La creación y mantenimiento de usuarios, está estrechamente ligado con la asignación de privilegios en MySQL.
Hasta ahora hemos usado sólo el usuario 'root', que es el administrador, y que dispone de todos los privilegios disponibles en MySQL.
Sin embargo, normalmente no será una buena práctica dejar que todos los usuarios con acceso al servidor tengan todos los privilegios. Para conservar la integridad de los datos y de las estructuras será conveniente que sólo algunos usuarios puedan realizar determinadas tareas, y que otras, que requieren mayor conocimiento sobre las estructuras de bases de datos y tablas, sólo puedan realizarse por un número limitado y controlado de usuarios.
Los conceptos de usuarios y privilegios están íntimamente relacionados. No se pueden crear usuarios sin asignarle al mismo tiempo privilegios. De hecho, la necesidad de crear usuarios está ligada a la necesidad de limitar las acciones que tales usuarios pueden llevar a cabo.
MySQL permite definir diferentes usuarios, además, asignar a cada uno determinados privilegios en distintos niveles o categorías de ellos.
Para crear un usuario en MySQL se puede hacer de modo gráfico o a través de un comando.
1. Veamos, como se puede crear gráficamente mediante PhpMyadmin.
a. Inicie sesión en PhpMyadmin y sin seleccionar ninguna base de datos, diríjase al link de la barra horizontal superior “Cuentas de usuarios” ( si no ha cambiado los datos usuario, el usuario por defecto es root y la contraseña es vacío ).
b. Diríjase a la sección “Nuevo” y clic sobre “Agregar cuenta de usuario”, con el fin de crear una nueva cuenta de usuario en MySQL.
c. Diligenciemos solamente la región de “Información de la cuenta “, con los datos que se solicitan allí. Al finalizar, ir a la parte inferior y dar clic en botón “Continuar”.
Nombre de usuario
Debe ser un usuario único por host, es decir, que se puede repetir, pero no debe existir un mismo usuario con un mismo host.
Nombre de Host
El nombre de host corresponde a la dirección IP o DNS (contexto) desde donde se está realizando la conexión. Por ejemplo, localhost o 127.0.0.1, solo permite conectarse de manera local. Pero si en ese campo se expresa una IP diferente como 192.168.56.1, esto indica, que solo desde una maquina con esta IP y con este usuario se puede realizar conexión. También se puede hacer uso del comodín % para otorgar privilegios globales, es decir, que se puede conectar desde cualquier máquina.
Contraseña
Contraseña que se le asigne a un usuario que se conecte por un host. Se recomienda que la contraseña no esté vacía y que tenga una combinación de caracteres con el fin de darle mayor seguridad. También se puede elegir la opción que genere una contraseña automáticamente.
Debe volver a escribir
Se confirma la contraseña ingresada.
2. Veamos que lo hecho anteriormente, se puede realizar en un solo comando.
b. Ejecuta el comando anterior en la sección SQL y clic en ejecutar.
Una vez creados los usuarios, no es mucho lo que se pueda hacer mucho con estos, ya que se deben asignar los privilegios para que se puedan ingresar a las bases de datos y se puedan realizar las diferentes operaciones. Para darle solución a esta problemática, podremos asignar privilegios a los usuarios que ya tenemos creados.
En MySQL existen cinco niveles distintos de privilegios:
Globales: se aplican al conjunto de todas las bases de datos en un servidor. Es el nivel más alto de privilegio, en el sentido de que su ámbito es el más general.
De base de datos: se refieren a bases de datos individuales, y por extensión, a todos los objetos que contiene cada base de datos.
De tabla: se aplican a tablas individuales, y por lo tanto, a todas las columnas de esas tabla.
De columna: se aplican a una columna en una tabla concreta.
De rutina: se aplican a los procedimientos almacenados. Aún no hemos visto nada sobre este tema, pero en MySQL se pueden almacenar procedimientos consistentes en varias consultas SQL.
Para efectos prácticos, explicaremos la asignación global y en una base de datos. Vemos como asignar los privilegios:
1. Asignemos los permisos globales mediante la interfaz gráfica.
a. Vamos a la sección “Cuentas de usuarios” (donde se deben observar los usuarios que se han creado hasta el momento) y luego se debe dar clic sobre el vínculo “Editar privilegios”.
b. Luego debemos seleccionar la opción “Seleccionar todo”, en la sección de “Privilegios globales” y por último nos dirigimos a la parte inferior y damos clic en el botón “Continuar”. Con esta alternativa le daremos privilegios a nivel de datos, estructuras y administrativas sobre todas las bases de datos que estén presentes en el motor de base de datos.
2. La asignación de los permisos globales (tal como el procedimiento anterior), se pueden se puede realizar mediante el siguiente comando o instrucción.
3. Ahora asignemos los permisos a una sola base de datos.
a. Vamos a la sección “Cuentas de usuarios” y luego se debe dar clic sobre el vínculo “Editar privilegios”.
b. Vamos a la sección “Base de Datos”, luego seleccionamos la base de datos a la cual asignaremos los privilegios y luego se debe dar clic sobre el botón continuar.
c. Aquí debemos elegir los privilegios que asignaremos al usuario sobre la base de datos; al igual que los privilegios globales, se pueden asignar los privilegios a una base de datos a nivel de Datos, Estructura y Administración. Para este caso, asignaremos todos los permisos sobre esta base de datos. Por últimos damos clic sobre continuar, para finalizar la asignación.
4. La asignación de los permisos en la base de datos (tal como el procedimiento anterior), se pueden se puede realizar mediante el siguiente comando o instrucción.
Una clave principal es un identificador único, de una o de varias columnas NOT NULL que identifica de forma única una fila de una tabla. Se crea un índice, y si no se declara explícitamente como NOT NULL, MySQL los declarará de manera silenciosa e implícita.
Para definir una llave primaria en MySQL se debe utilizar la instrucción PRIMARY KEY, el cual se debe declarar en la definición de la tabla. Es recomendable, que cada que cada tabla tenga una llave primaria. InnoDB creará automáticamente uno en su ausencia (como se ve en la documentación de MySQL), aunque esto es menos deseable.
Para crear una llave primaria en PhpMyadmin, es muy sencillo definirla, veamos:
De igual manera podemos definir esta llave primaria mediante el script. Si la clave principal consta de una sola columna, la cláusula PRIMARY KEY se puede colocar en línea con la definición de la columna, veamos:
El atributo "unsigned" (sin signo) permite sólo valores positivos.
También es posible definir una clave principal que comprenda más de una columna. Esto se puede hacer, por ejemplo, en la tabla secundaria de una relación de clave externa. Una clave primaria de varias columnas se define enumerando las columnas participantes en una cláusula PRIMARY KEY separada. La sintaxis en línea no está permitida aquí, ya que solo una columna puede declararse PRIMARY KEY línea. Por ejemplo:
Auto Increment es una función que opera en tipos de datos numéricos. Genera automáticamente valores numéricos secuenciales cada vez que se inserta un registro en una tabla para un campo definido como incremento automático.
En MySQL, puede crear una columna que contenga una secuencia de números (1, 2, 3, etc.) utilizando el atributo AUTO_INCREMENT. El atributo AUTO_INCREMENT se utiliza cuando necesita crear un número único para que actúe como clave principal en una tabla.
En MySQL, un valor NULL significa desconocido. Un valor NULO es diferente de cero (0) o una cadena vacía ''.
Un valor NULO no es igual a nada, ni siquiera a sí mismo. Si compara un valor NULL con otro valor NULL o cualquier otro valor, el resultado es NULL porque se desconoce el valor de cada valor NULL.
Para indicar que un valor falta, es desconocido o no aplica, se usa el valor NULL. Por ejemplo, el número de teléfono de un cliente potencial puede ser NULL y puede agregarse más tarde.
Cuando crea una tabla, puede especificar si una columna acepta valores NULL o no utilizando la restricción NOT NULL. En el siguiente ejemplo, se declara una tabla (cliente) y se indica cuales campos no pueden ir con valor NULL por defecto, es decir, al crear un registro, estos campos deben tener un valor asignado, veamos:
Para este caso los campos nombre, apellido y procedencia, siempre deben ir asignados con valores diferentes de NULL, cuando insertemos o actualizamos registros. El siguiente ejemplo, donde se inserta un nuevo registro, entregará un error debido a que el nombre no puede ir con un NULL. El campo id, por ser una llave primaría, nunca puede tomar valores NULL.
A diferencia del siguiente INSERT donde el campo correo y telefono si pueden ir con valores NULL.
La sentencia INSERT INTO se utiliza para insertar nuevos registros a una tabla.
Sintaxis
Se puede escribir la sentencia INSERT INTO de dos maneras.
La primera forma no especifica los nombres de las columnas en las que se inserta los datos, sólo se especifican los valores:
La segunda forma específica tanto los nombres de las columnas como los valores a insertar:
La estructura de la tabla es:
En este ejemplo, especificamos los valores solo para las columnas: titulo y prioridad. Para las otras columnas, MySQL utiliza los valores predeterminados.
La columna tarea_id se genera automáticamente mediante la secuencia AUTO_INCREMENT cada vez que se inserte una fila en la tabla.
Las columnas fecha_inicio, fecha_fin y descripción toma el valor NULL por defecto.
En este caso en el campo prioridad toma el valor por defecto 3.
Para insertar un valor de fecha literal en una columna, utilice el siguiente formato:
En este formato:
YYYY representa un año de cuatro dígitos, por ejemplo, 2018.
MM representa un mes de dos dígitos, por ejemplo, 01, 02 y 12.
DD representa un día de dos dígitos, por ejemplo, 01, 02, 30.
La función CURRENT_DATE() devuelve la fecha actual del sistema y lo inserta en este caso en los campos fecha_inicio y fecha_fin.
La siguiente instrucción inserta tres filas en la tabla: tarea
La sentencia UPDATE se utiliza para actualizar registros ya existentes de una tabla.
Nos permite elegir los campos a actualizar y los datos con que actualizarlos.
La sintaxis básica de la cláusula UPDATE es la siguiente:
Tomemos como referencia el ejemplo anterior.
Si desea eliminar un registro de cualquier tabla MySQL, puede usar el comando SQL DELETE FROM.
El siguiente bloque de código tiene una sintaxis SQL genérica del comando DELETE para eliminar datos de una tabla MySQL.
Si no se especifica la cláusula WHERE, todos los registros se eliminarán de la tabla.
Puede especificar cualquier condición utilizando la cláusula WHERE.
Puede eliminar registros en una sola tabla a la vez. El siguiente bloque de código tiene una sintaxis
Tomemos como referencia el ejemplo anterior.
Elimina todas las filas de una tabla. internamente hace un DROP (eliminación de la tabla) de la tabla y después hace un CREATE (crear la estructura) de la misma tabla.
TRUNCATE TABLE es similar a la sentencia DELETE pero sin la cláusula WHERE. Tiene la desventaja de que no se puede borrar selectivamente toda la tabla y la ventaja de que TRUNCATE TABLE es más rápido y utiliza menos recursos.
Tomemos como referencia el ejemplo anterior.
Crear una cuenta en la plataforma Clever Cloud (Anexar capturas de pantalla).
Corra el script SQL de la base de datos “Colegio La Trinidad”, realizado en el módulo 4 (Anexar capturas de pantalla).
Verificar y ajustar el script SQL de la base de datos “Colegio La Trinidad”, teniendo en cuenta que:
Todas las tablas deben tener como motor de almacenamiento InnoDB.
Todas las tablas tengan aplicadas correctamente las llaves primarias.
Todas las tablas se les haya asignado correctamente la instrucción NOT NULL en los campos que se requiera.
Crear 5 instrucciones (mínimamente) de inserción (INSERT) en las diferentes tablas de la base de datos “Colegio La Trinidad”.
Crear 5 instrucciones (mínimamente) de actualización (UPDATE) en las diferentes tablas de la base de datos “Colegio La Trinidad”.
Crear 5 instrucciones (mínimamente) de eliminación (DELETE) en las diferentes tablas de la base de datos “Colegio La Trinidad”.
Crear 5 instrucciones (mínimamente) de vaciado (TRUNCATE) en las diferentes tablas de la base de datos “Colegio La Trinidad”.
1. Ingresar a la plataforma mediante el enlace y ingrese a la sección Sign Up Free.
6. Vuelva nuevamente al enlace y presione sobre el botón Login.
Nota: Si se esta trabajando con XAMPP y no le es posible iniciar sesión con un usuario diferente al root, dejamos este vídeo donde explican como cambiar esta configuración que esta predeterminada ().
