Restricciones en tablas y características de tabla en MySQL

Restricciones en tablas

DE COLUMNA

• PRIMARY KEY: claves primarias
• REFERENCES: claves ajenas
• NULL/ NOT NULL: campos optativos/ campos obligatorios
• DEFAULT: para columnas con valores por defecto
• UNIQUE: Identifica cada fila de una tabla.
• CHECK: columnas con comprobación Ejemplos: código postal lo puedo definir como un tipo de texto o numérico. Filtramos un rango de valor
• AUTO_INCREMENT: para columnas que sean auto numéricas

DE TABLA

• GENERAL (sin nombre)
  • PRIMARY KEY: clave primaria
  • FOREIGN KEY…REFERENCES clave secundaria…clave ajena
• CONSTRAINT (con nombre)

Integridad referencial

Para conseguir integridad diferencial en MySQL, realizamos dos opciones. Aplicar el motor InnoDB a las tablas y enlazar las columnas mediante claves ajenas. Existen dos posibles eventos, que son ON DELETE y ON UPDATE. Y cuatro posibles valores para cada uno de ellos: RESTRICT, CASCADE, SET NULL, NO ACTION.

ON DELETE: se elimina el registro principal, también se eliminan los registros secundarios.

ON UPDATE: se cambia la clave primaria principal, el valor secundario también cambiará para reflejar eso.

RESTRICT: Es el comportamiento por defecto, que impide realizar modificaciones que atentan contra la integridad referencial.

CASCADE: Borra o Actualiza los registros de la tabla dependiente cuando se borra o actualiza el registro de la tabla principal (en una sentencia DELETE).

SET NULL: Establece a NULL el valor de la clave secundaria cuando se elimina el registro en la tabla principal o se modifica el valor del campo referenciado.

NO ACTION: El servidor MySQL rechaza la operación de eliminación o actualización para la tabla primaria si hay un valor de clave externa relacionado en la tabla referenciada.

CARACTERÍSTICAS DE TABLA

ENGINE: Nos permite definir el motor que utilizara la tabla. Los dos motores más utilizados a día de hoy son MySAM y InnoDB

• InnoDB: es más completo, permite integridad referencial y es más pesado y lento.
• MySAM no permite tantas características y es más liguero y rápido.

AUTO_INCREMENT: es la característica de la tabla que me permite saber cuál será el siguiente valor para el campo auto_incremental.

CHARACTER SET: conjunto de caracteres que quiero darle a la tabla spanis_ci

COLLATE: conjunto de caracteres que le damos a la tabla spanis_ci2

CHECKSUM: comprobación de fila, nos permite guardar información para saber si una fila ha sido dañada o modificada, es un sistema de control de errores para la filas de una tabla.

COMMENT: nos permite poner una descripción para una tabla.

MAX_ROWS: nos permite indica el número máximo de filas que debe ser capaz de almacenar la tabla.

MIN_ROWS: preparara la tabla para tener como mínimo x filas, pero puede llegar a tener en algún momento 0 filas.

Son características principales de la tabla y que la definen e implicar características concretas en todas sus columnas.

Modificadores de tablas

ALTER TABLE: nos permite modificar una tabla

• ADD: Nos permite añadir una nueva columna o restricción.
• Ejemplo: ALTER TABLE socios_2a ADD (dirección_correo_e varchar (9)); “no puede ser obligatoria”. ALTER TABLE socios_2a ADD crédito INT(10) AFTER teléfono;
• DROP: nos permite borra una columna o restricción.
• MODIFY: nos permite modificar el tipo de dato y las restricciones de una columna.
• CHANGE: nos permite renombrar una columna y cambiar su tipo de dato.
• RENAME TO: nos permite renombrar la tabla.

TEMA 6

DML

• SELECT (consulta)
• INSERT (nuevas filas)
• DELETE (borrar filas)
• UPDATE (modificar filas)

UPDATE: la sentencia UPDATE permite modificar el contenido existente de cualquier columna de cualquier fila de una tabla. Usaremos UPDATE cuando necesitemos modificar los datos.

DELETE: sentencia SQL que permite el borrado de registros de una tabla.

Funciones de comparación:

GREATEST: le pasamos dos o más valores y nos indica cual es el mayor valor

LEAST: le damos dos valores y nos indica cual es el menor valor.

IFNULL: devuelve un valor especificado si la expresión es NULL. Si la expresión NO ES NULA, esta función devuelve la expresión otra vez.

-ISNULL: devuelve 1 o 0 dependiendo de si una expresión es NULL. Si la expresión es NULL, esta función devuelve 1. De lo contrario, devuelve 0. La indicamos un valor y nos indica si es nulo o no. Ejemplo le decimo si está vacío ese campo, si es verdad nos devuelve un 1 y si es mentira nos devuelve un cero.

STRCMP: compara las cadenas basándose en los códigos ASCII, para decirnos si son iguales o una es mayor que otra. (Es bastante utilizada)

Funciones del sistema:

DATABASE: te retorna el nombre de la base actual y si no tienes ninguna. Nos devuelve la base de datos en uso. Si no hay ninguna nos

USER: nos dice el nombre del usuario actualmente conectado

VERSION: muestra la versión actual del servidor mediante una cadena de texto.

Integridad referencial

A la hora de borrar no podemos borrar filas referenciales mediante clave desde la misma u otras tablas.

Las ajenas sirven para relacionar tablas y limitar los valores que puede tomar esa columna a los existentes en la columna que referencian o a nulos

Mysan: dan mayores prestaciones al no , y no tiene que hacer integridad diferencial.

Ventajas

MySAM: es el más adecuado para una alta tasa de consultas "selectivas" y operaciones no transaccionales.

InnoDB: es el más adecuado para operaciones paralelas de inserción / actualización / eliminación (debido al bloqueo de nivel de fila) y operaciones transaccionales (debido a la función de retroceso).

El motor de memoria (HEAP): es el más adecuado para un acceso rápido a los datos, ya que todo está almacenado en la RAM.

Contras

MyISAM: es peor para una alta tasa de consulta de "inserción / actualización". (debido al bloqueo de nivel de la mesa).

InnoDB: es peor cuando hay una combinación de operaciones no transaccionales y de solo lectura.

El motor de memoria (HEAP): es peor para el uso a largo plazo (debido a problemas de integridad de datos) y operaciones transaccionales.

Filtros WHERE

Condiciones impuestas a los registros para estar incluidos en el resultado.

Los filtros se construyen mediante expresiones (combinación de operadores, operados y funciones - resultado).

• Operadores: aritméticos (+,-,*,/,%),
• Operadores relacionales (>,<,><,>=,<=,=).
• Operadores lógicos (AND, OR, NOT).
• IN(permite comparar con varios valores y todos son aceptados),IS NOT, LIKE(nos permite comparar patrones de texto),LIMIT(a la hora de mostrar los resultados podemos limitar cuales vamos a sacar) y los paréntesis.
• Operados constantes: (1,2,3 "Nacho", "2010-01-02)
• Operadores variables (columna1, columna2 …)
• Funciones  

SELECT * FROM ;

SELECT FROM WHERE = AND (=valor> OR=);

Select FROM WHERE IN (,

SELECT FROM WHERE BETWEEN AND ;

SELECT FROM WHERE IS [NOT] NULL;  [IS NULL/IS NOT NULL]

SELECT FROM WHERE LIKE ;

SELECT FROM LIMIT X,YA la hora de mostrar los resultados podemos delimitar cuales son los que vamos a sacar. X indica el número de filas del resultado que si quiero mostrar. Mientras que Y indica el índice de la primera fila que quiera mostrar.

Operador IS: me permite saber si el valor es nulo o no nulo (IS NULL/IS NOT NULL)

Ordenación

Order by

• |
• ASC: ascendente (por defecto) | DESC:descendente
• … …

Es la cláusula que nos permite ordenar el resultado de una consulta, podemos ordenar por una o varias columnas del resultado indicando cuales por nombre o por número.

Filtro LIMIT

Condiciones impuestas al número de registros devueltas por el resultado. El primer registro devuelto es el registro de índice 0.

Admite dos sintaxis:

• Recuperar X registros devueltos, (los primeros).
• A partir del Y de todos los registros devueltos recuperar X registros (con desplazamiento previo).

Un numero reduce las filas del resultado.

Si indicamos dos números, estamos indicando el índice de la primera fila que quiero mostrar, el Segundo número me indica cuantas quiero mostrar. Ejemplo 2,3

Consultas resumen

Consultas complejas que extraen información calculada de los conjuntos de registros. Convierte un conjunto variable de registros en un dato simple

Funciones de columna /funciones resumen

Sum: Devuelva la suma del campo "Cantidad" en la tabla

Avg: Devuelva el valor promedio para la columna. Ejemplo: "Precio" en la tabla "Productos":

Min: Encuentre el precio del producto más barato en la tabla

Max: Encuentre el precio del producto más caro en la tabla

Count: Devuelve el número de productos en la tabla.

Podemos añadir filtros restrictivos a los grupos

Los recoge con el mismo valor para una col.concreta pueden sergroup by

El resultado de las funciones (func) resumen se aplicara a todos los registros o por grupos a todos los grupos, si necesitamos consultar a la vez func resum o columna es obligatorio crear grupo. El orden de la sintaxis es el que apareceSelect-from-where -group byorder by -limit

El orden de ejecución para una consulta primero se realiza el where, el GROUP BY se crean los grupos, HAVING se filtran los grupos.

Filtros resumen

Clausulas group by having

Orden de sintaxis

SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT

Orden de ejecución

WHERE, GROUP BY, HAVING

Subconsultas

Select (                       … …    )                          SELECT(          … …    )

Tipos de subconsultas:

• Comparación(=,>=,<=,<>,>,<) solo me puede devolver un valor, si me devuelve más de uno, no se pueden utilizar.
• Pertenecía a conjunto (IN)
• Existencia(exists(existe), no exists(no existe))
• ALL: devuelve verdadero si alguno de los valores de la subconsulta cumple la condición.
• ANY: devuelve verdadero si todos los valores de subconsulta cumplen la condición.
• Multivalor: que tiene varios valores o puedes utilizar varios valores.
• Having: se utiliza para incluir condiciones, having remplaza a where, ya que esta no puede utilizar SUN ni MAX.

Las subconsultas filtran los datos de otras consultas. Estos filtros podrán aplicarse a la cláusula web o a la cláusula Having. Según queramos filtra requisitos o grupos. Las subconsultas deben coincidir con el número de registro y de columnas con la consulta. Las subconsultas no suelen aparecer a la derecha del operador. En la subconsulta no suele utilizarse ordenación. Los nombres de columnas que aparecen en las subconsultas pueden referirse a la consulta principal. Se conoce como referencias estrenas.

Operador de existencia, se puede usar tanto para asistencia con para cuando no existe. Filtramos la consulta si existen filas en la subconsulta asociada.

Operadores cuantificados

ALL: me sirve para comparar con todos y ANY para comparar solo con algunos.

ANY: permite que se cumpla la condición en algún caso

Consultas multivalor: utilizar más de una columna, en el apartado select de la subconsulta.

Importante

Subconsultas anidadas

select (                      select(                        Select(…        …        )          )          )

Reflejan la potencia del lenguaje sql estructurado.

Nuestra estructura se basa en filas y columnas dentro de otra tabla y así se crea la base de datos.

Visualizar el número de departamento donde trabaja el empleado más veterano, en el mismo puesto que GIL.

Ejemplo: Averiguar la cuidad donde juega el jugador más alto de la nba.

SELECT ciudad FROM equipos WHERE nombre=(SELECT nombre_equipo FROM jugadores WHERE altura=(SELECT MAX(altura) FRO