Gestión de Bases de Datos

Herramientas de usuario

Herramientas del sitio

Traza:
bloque4:sql

Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Ambos lados, revisión anteriorRevisión previa
Próxima revisión		Revisión previa
bloque4:sql [2022/04/29 07:54] – [Cliente de bases de datos] fernandobloque4:sql [2024/09/16 19:34] (actual) – editor externo 127.0.0.1
Línea 197: Línea 197:
 ALTER TABLE alumnos ADD CONSTRAINT nombre_fk_1 FOREIGN KEY (id_curso) REFERENCES cursos(id); ALTER TABLE alumnos ADD CONSTRAINT nombre_fk_1 FOREIGN KEY (id_curso) REFERENCES cursos(id);
 ALTER TABLE alumnos DROP FOREIGN KEY nombre_fk; ALTER TABLE alumnos DROP FOREIGN KEY nombre_fk;
- 
--- Restaura el valor de AUTO_INCREMENT a 1. Debe ser mayor al índice más alto 
-ALTER TABLE alumnos AUTO_INCREMENT = 1; 
  
 -- Renombar tabla -- Renombar tabla
Línea 220: Línea 217:
 El tipo ''CHAR'' permite almacenar cadenas de caracteres de longitud fija entre 1 y 255 caracteres. La longitud de la cadena se debe especificar entre paréntesis en el momento de la declaración (''cadena CHAR(25)'').  El tipo ''CHAR'' permite almacenar cadenas de caracteres de longitud fija entre 1 y 255 caracteres. La longitud de la cadena se debe especificar entre paréntesis en el momento de la declaración (''cadena CHAR(25)''). 
  
-Por otro lado, el tipo ''VARCHAR'' permite almacenar cadenas de caracteres variables de hasta 4.000            caracteres. La declaración del tipo ''VARCHAR'' es similar a la de un tipo CHAR (''cadena VARCHAR(25)''). La principal y única diferencia entre estos dos tipos, es que el tipo ''CHAR'' declara una cadena fija de la longitud que se especifica mientras que en la declaración de un tipo ''VARCHAR'' lo que se especifica es un tamaño máximo, la cadena sólo ocupará el tamaño necesario para almacenar el dato que contenga (hasta llegar al máximo). En cualquier caso, no es posible almacenar cadenas de mayor tamaño al especificado en su declaración, puesto que el SGBD truncará el valor almacenándose sólo hasta la longitud establecida.+Por otro lado, el tipo ''VARCHAR'' permite almacenar cadenas de caracteres variables. La declaración del tipo ''VARCHAR'' es similar a la de un tipo CHAR (''cadena VARCHAR(25)''). La principal y única diferencia entre estos dos tipos, es que el tipo ''CHAR'' declara una cadena fija de la longitud que se especifica mientras que en la declaración de un tipo ''VARCHAR'' lo que se especifica es un tamaño máximo, la cadena sólo ocupará el tamaño necesario para almacenar el dato que contenga (hasta llegar al máximo). En cualquier caso, no es posible almacenar cadenas de mayor tamaño al especificado en su declaración, puesto que el SGBD truncará el valor almacenándose sólo hasta la longitud establecida.
                          
 == Tipo TEXT == == Tipo TEXT ==
Línea 231: Línea 228:
 === Tipos numéricos === === Tipos numéricos ===
  
-Para la representación de tipos de datos numéricos. Los tipos más utilizados son ''BIT'', ''TINYINT'', ''INT'', ''BIGINT'', ''FLOAT'' y ''DOUBLE'', para la representación de números enteros de menor o mayor tamaño, y para números en coma flotante de menor o mayor precisión, respectivamente.+Para la representación de tipos de datos numéricos. Los tipos más utilizados son ''BIT'', ''TINYINT'', ''INT'', ''BIGINT'', para la representación de números enteros de menor o mayor tamaño.
                          
 +Para número decimales tenemos los tipos ''FLOAT'' y ''DOUBLE'', números en coma flotante de menor o mayor precisión, respectivamente. En este caso conviene tener en cuenta los problemas de precisión ((https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html)) que existen con estos tipos de datos. Si necesitamos almacenar valores en los que es muy importante la precisión decimal, podemos utilizar ''DECIMAL(d,n)'', //d// representa la cantidad de digitos del valor, y //n// la cantidad de decimales después de la coma.
 +
 En ocasiones el rango de los valores negativos resultará prescindible (claves numéricas, valores de dinero, cantidades, . . .) por lo que será posible ampliar el rango positivo de un tipo numérico añadiendo la restricción ''UNSIGNED'' tras definir el tipo de éste. En ocasiones el rango de los valores negativos resultará prescindible (claves numéricas, valores de dinero, cantidades, . . .) por lo que será posible ampliar el rango positivo de un tipo numérico añadiendo la restricción ''UNSIGNED'' tras definir el tipo de éste.
  
Línea 239: Línea 238:
 </code> </code>
                          
-Para el caso de los números de coma flotante conviene tener en cuenta los problemas de precisión ((https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html)) que existen con estos tipos de datos. 
  
 === Tipos para fechas === === Tipos para fechas ===
  
-Los tipos más utilizado para almacenar valores de fechas (''DATE'') o fechas con hora (''DATETIME''). Por defecto            el formato más utilizado es ''DD/MM/YY'' ó ''DD/MM/YYYY''.+Los tipos más utilizado para almacenar valores de fechas (''DATE'') o fechas con hora (''DATETIME''). Por defecto            el formato utilizado es ''YYYY-MM-DD'' y ''YYYY-MM-DD HH:MM:SS'' respectivamente. 
 + 
 +También se puede usar el tipo ''TIMESTAMP'' para almacenar una marca de tiempo (fecha y hora ''YYYY-MM-DD HH:MM:SS''). Además, permite el uso de la constante ''CURRENT_TIMESTAMP'' en la definición de la columna al definirle un valor por defecto cuando se crea la tabla. 
  
-También se puede usar el tipo ''TIMESTAMP'' para almacenar una marca de tiempo (fecha y hora). Además, permite el uso de la constante ''CURRENT_TIMESTAMP'' en la definición de la columna al definirle un valor por defecto cuando se crea la tabla.+La diferencia entre ''TIMESTAMP'' frente a ''DATETIME'', es que a pesar de mostrar el mismo formato, TIMESTAMP almacena también la zona horaria, y devuelve el valor modificado si cambiamos la zona horaria del servidor.
  
 === Tipo booleano === === Tipo booleano ===
  
-Permite almacenar valores lógicos Verdadero/Falso o Sí/No. Cuando usamos el tipo ''BOOLEAN'' a la hora de definir una columna internamente MySQL lo define usando el tipo ''TINYINT(1)'', que simplemente almacena los valores 0 y 1 para indicar los valores lógicos Verdadero y Falso respectivamente. Así, podremos utilizar los valores ''TRUE'' ó ''FALSE'' o directamente asignar 1 ó 0 para asignar valor.+Permite almacenar valores lógicos Verdadero/Falso o Sí/No. Cuando usamos el tipo ''BOOLEAN'' para definir un campo MySQL define internamente la columna como del tipo ''TINYINT'', utilizando los valores 0 y 1 para indicar los valores lógicos Verdadero y Falso, respectivamente. Así, podremos utilizar los valores ''TRUE'' ó ''FALSE'' o directamente asignar 1 ó 0 para asignar valor.
  
 ==== Restricciones ==== ==== Restricciones ====
  
 Las restricciones se pueden establecer, o no, a las columnas de cada tabla para forzar a que los datos almacenados Las restricciones se pueden establecer, o no, a las columnas de cada tabla para forzar a que los datos almacenados
-en ellas cumplan una serie de condiciones, con la finalidad de que la información sea más correcta. Por ejemplo,        podemos obligar a que un campo donde almacenamos el DNI de una persona tenga una longitud mínima, o bien un        campo donde almacenamos la categoría de un equipo de fútbol, sólo pueda tomar unos determinados valores        predefinidos (benjamín, juvenil, cadete, . . .) o bien podemos hacer que un campo no pueda repetirse, por        tratarse de un valor único (DNI, NSS, teléfono, email, . . .).+en ellas cumplan una serie de condiciones, con la finalidad de que la información sea más correcta. Por ejemplo,        podemos obligar a que un campo donde almacenamos el DNI de una persona tenga una longitud mínima, un campo donde almacenamos la categoría de un equipo de fútbol, sólo pueda tomar unos determinados valores predefinidos (benjamín, juvenil, cadete, . . .) o bien podemos hacer que un campo no pueda repetirse, por tratarse de un valor único (DNI, NSS, teléfono, email, . . .).
  
 Hay que tener en cuenta que, por lo general, las restricciones se definen en línea con la definición del campo        (tal y como se muestra en la sintaxis de la sentencia de ''CREATE TABLE'', pero de forma opcional también        pueden ser definidas por separado justo debajo de la definición de todos los campos de la tabla. Hay que tener en cuenta que, por lo general, las restricciones se definen en línea con la definición del campo        (tal y como se muestra en la sintaxis de la sentencia de ''CREATE TABLE'', pero de forma opcional también        pueden ser definidas por separado justo debajo de la definición de todos los campos de la tabla.
Línea 280: Línea 280:
 </code> </code>
  
-Hay que tener en cuenta que a la hora de definir claves primarias compuestas (la componen 2 ó más campos de            la tabla), ésta deberá ser definida forzosamente tras la definición de los campos involucrados, siguiendo            esta sintaxis+Hay que tener en cuenta que a la hora de definir claves primarias compuestas (dos ó más columnas), ésta deberá ser definida forzosamente tras la definición de los campos involucrados, siguiendo            esta sintaxis
 <code sql> <code sql>
 CREATE TABLE personas( CREATE TABLE personas(
Línea 291: Línea 291:
  
 === Autonumérico === === Autonumérico ===
- +Es una propiedad que solo se puede aplicar a claves primarias de tipo entero. Hace que en cada inserción de nuevos registros en esa tabla, la clave primaria se genere automáticamente de forma secuencial. Es realmente útil ya que nos evita tener que dar valor a esa columna, siendo el motor de la base de datos el encargado de que sea siempre distinta.
-Especialmente útil en el caso de aquellas columnas que se definan como claves primarias de cada tabla, resulta añadir la restricción de campo autonumérico, siempre y cuando ésta sea una columna de tipo entero. De esa manera será el SGBD el encargado de asignarle valor de forma automática, siempre asignando un valor entero de forma secuencial a medida que se van insertando las filas en dicha tabla.+
  
 La forma de definirlo es añadiendo la restricción ''AUTO_INCREMENT'' en la definición de la columna que se ha definido como clave primaria: La forma de definirlo es añadiendo la restricción ''AUTO_INCREMENT'' en la definición de la columna que se ha definido como clave primaria:
Línea 298: Línea 297:
 id INT PRIMARY KEY AUTO_INCREMENT id INT PRIMARY KEY AUTO_INCREMENT
 </code> </code>
 +
 +Como detalle, cualquier inserción fallida en la tabla seguirá incrementando el índice autonumérico aunque no se inserten los datos. Podemos resetearlo:
 +<code sql>
 +ALTER TABLE mitabla AUTO_INCREMENT = 1;
 +</code>
 +Si la tabla no está vacía, debe ser mayor que el último valor de esa columna.
  
 === Clave ajena === === Clave ajena ===
  
-Una clave ajena está formada por una o varias columnas que hacen referencia a una clave primaria de otra o de            la misma tabla. Se pueden definir tantas claves ajenas como sea necesario (no hay límite) en cada tabla. El valor de la            columna o columnas que son clave ajena debe estar entre los valores de la clave primaria de la tabla a la que hacen referencia (integridad referencial). Así, a la hora de definir una clave ajena, deberemos indicar con la cláusula ''REFERENCES'' la tabla y el campo clave primaria a la que ésta hace referencia .+Una clave ajena está formada por una o varias columnas cuya finalidad es almacenar valores que existen en la columna/s clave primaria de otra tabla (o la misma) a la que hace referencia. De este modo los sistemas relacionales aseguran que las relaciones entre tablas tienen significado.  
 + 
 +Al definir una columna como clave ajena ''FOREIGN KEY'' el motor de la base de datos nos obliga a que todos los valores que contenga esa columna existen siempre en la columna de la clave primaria de la tabla con la que se relaciona. Este concepto se conoce como **Integridad Referencial**.
  
 Las claves ajenas se deben definir después de la definición de los campos de la tabla: Las claves ajenas se deben definir después de la definición de los campos de la tabla:
Línea 313: Línea 320:
        
    FOREIGN KEY (id_curso) REFERENCES cursos (id)    FOREIGN KEY (id_curso) REFERENCES cursos (id)
-    +       
-   -- También se le puede indicar un nombre concreto:+   -- También se puede crear indicandole un nombre concreto a la restricción:
    CONSTRAINT 'fk_cursos' FOREIGN KEY (id_curso) REFERENCES cursos (id)    CONSTRAINT 'fk_cursos' FOREIGN KEY (id_curso) REFERENCES cursos (id)
 ); );
 </code> </code>
  
-El ejemplo anterior haría referencia a una definición de tabla como la siguiente:+El ejemplo anterior haría referencia tabla //cursos//:
 <code sql> <code sql>
 CREATE TABLE cursos( CREATE TABLE cursos(
Línea 336: Línea 343:
        
    FOREIGN KEY (id_curso) REFERENCES cursos (id),    FOREIGN KEY (id_curso) REFERENCES cursos (id),
-   FOREIGN KEY (id_curso) REFERENCES cursos (id)+   FOREIGN KEY (id_profesor) REFERENCES profesores (id)
 </code> </code>
  
-En caso que la clave ajena haga referencia a una clave primaria compuesta:+Si la clave ajena hace referencia a una clave primaria compuesta:
  
 <code sql> <code sql>
Línea 345: Línea 352:
 </code> </code>
  
-En cualquiera de los casos hay que tener en cuenta que habrá que definir primero el campo con el tipo de dato correcto (el mismo que dicho campo en la tabla donde aparece como clave principal) y luego la propia definición de dicho campo como clave ajena.+Las definiciónes de la columna y de la restricción de clave ajena se hacen de forma independiente, pero debemos tener en cuenta que para crear la restricción de clave ajena sobre una columna, **su tipo de datos debe ser exactamente igual al de la clave primaria a la que hace referencia**.
  
-Habrá que tener en cuenta que mientras que un campo definido como clave ajena haga referencia a un campo            definido como clave primaria, la fila de la segunda tabla no podrá ser eliminada hasta que no lo haga la fila que le hace referencia (integridad referencial). Para evitar estos problemas (aunque no siempre es un problema) es posible definir la restricción de clave ajena añadiendo la cláusula ''ON DELETE'' o bien ''ON UPDATE'' para el caso de una actualización. De esa manera, cuando se vaya a eliminar o actualizar una fila a cuya clave primaria se haga referencia, podremos indicar a MySQL que operación queremos realizar con las filas que le hacen referencia:+Habrá que tener en cuenta que mientras que un campo definido como clave ajena haga referencia a un campo            definido como clave primaria, la fila de la segunda tabla no podrá ser eliminada hasta que no lo haga la            fila que le hace referencia (integridad referencial). Para evitar estos problemas (aunque no siempre es            un problema) es posible definir la restricción de clave ajena añadiendo la cláusula ''ON DELETE'' o bien ''ON UPDATE'' para el caso de una actualización. De esa manera, cuando se vaya a eliminar o actualizar una fila a cuya clave primaria se haga referencia, podremos indicar a MySQL que operación queremos realizar con las filas que le hacen referencia:
  
   * **RESTRICT**: Se rechaza la operación de eliminación/actualización   * **RESTRICT**: Se rechaza la operación de eliminación/actualización
Línea 370: Línea 377:
  
   * Una columna clave ajena nunca podrá ser ''AUTO_INCREMENT'', ya que rompería el propósito de la relación.   * Una columna clave ajena nunca podrá ser ''AUTO_INCREMENT'', ya que rompería el propósito de la relación.
-  * La columna deberá ser del mismo tipo (y atributos) que la columna a la que se referencia. +  * La columna/s clave ajena debe ser del mismo tipo de datos que la columna clave primaria a las que se referencia. 
-  * La columna deberá ser un índice. A partir de Mysql 8 y versiones compatibles de MariaDB, se hace automáticamente.+  * La columna deberá ser un índice. A partir de Mysql 8 y versiones compatibles de MAriaDB, se hace automáticamente.
   * Si la columna se define como obligatoria (''NOT NULL'') no podrá contener la claúsula (''SET NULL'') para los casos de borrado (''ON DELETE'') o actualización (''ON UPDATE'').   * Si la columna se define como obligatoria (''NOT NULL'') no podrá contener la claúsula (''SET NULL'') para los casos de borrado (''ON DELETE'') o actualización (''ON UPDATE'').
   * Toda restricción de clave ajena tiene un nombre. Para ver el nombre de las restricciones de una tabla: ''SHOW CREATE TABLE <nombre_tabla>''   * Toda restricción de clave ajena tiene un nombre. Para ver el nombre de las restricciones de una tabla: ''SHOW CREATE TABLE <nombre_tabla>''
Línea 393: Línea 400:
 === Condiciones === === Condiciones ===
  
-De forma más genérica, podemos forzar a que los valores de determinados campos de la tabla cumplan una            ciertas condiciones. En caso contrario no se permitirá la inserción de esa fila en dicha tabla.+De forma más genérica, podemos forzar a que los valores de determinados campos de la tabla cumplan una            ciertas condiciones.
  
 Lo habitual es definir una columna como de tipo enumeración (''ENUM'' en MySQL) si queremos indicar que solamente una serie de valores (definidos) son válidos: Lo habitual es definir una columna como de tipo enumeración (''ENUM'' en MySQL) si queremos indicar que solamente una serie de valores (definidos) son válidos:
Línea 404: Línea 411:
 === Valores únicos === === Valores únicos ===
  
-La restricción ''UNIQUE'' evita valores repetidos en una misma columna. Al contrario que ocurre con la restricción ''PRIMARY KEY'', la restricción de valor único se puede aplicar a varias columnas de una misma tabla y admite el valor ''NULL''. Con respecto a esta última consideración, conviene saber que si una columna se define como ''UNIQUE'', sólo una de sus filas podrá contener el valor ''NULL''+La restricción ''UNIQUE'' evita valores repetidos en una misma columna. Al contrario que ocurre con la restricción ''PRIMARY KEY'', ''UNIQUE'' sí admite el valor ''NULL''. Con respecto a esta última consideración, conviene saber que si una columna se define como ''UNIQUE'', sólo una de sus filas podrá contener el valor ''NULL''. Del mismo modo que la clave primaria, esta restricción se puede aplicar a una columna o a un conjunto de columnas.
  
 <code sql> <code sql>
 email VARCHAR(100) UNIQUE email VARCHAR(100) UNIQUE
 +
 +// o para varias columnas
 +. . .
 +email VARCHAR(100),
 +login VARCHAR(50),
 +UNIQUE (email, login)
 </code> </code>
  
 +=== Índices ===
 +
 +Los índices se utilizan para obtener datos de las tablas de una forma más rápida. En definitiva, lo que el SGBD        hace es asociar el valor de una columna (sobre la que definimos el índice) con su posición en la tabla. De esa         manera será más rápido buscar sobre esa columna puesto que al encontrar el valor, el SGBD conocerá su posición        en la tabla.
 +
 +Se recomienda su uso en aquellas columnas sobre las que se vayan a realizar búsquedas en una tabla. Por ejemplo,        si tenemos una tabla donde almacenamos información sobre Libros, nos podría interesar crear un índice en el        campo autor, puesto que puede ser muy común buscar qué libros ha escrito un autor determinado. Además, será un        valor que contendrá pocos valores repetidos, por lo que maximizará el beneficio de usar un índice.
 +
 +<code sql>
 +CREATE TABLE libro(
 +id INT ...,
 +titulo ...,
 +autor VARCHAR(20),
 +INDEX autor_index (autor)
 +);
 + 
 +-- También puedo añadirlo cuando la tabla ya se ha creado
 +CREATE INDEX indice_autor ON libro(autor);
 +</code>
 +
 +Por otra parte, los índices presentan algún inconveniente como puede ser el hecho de que ocupan espacio en la        tabla, y dependiendo del caso podría llegar a ocupar más espacio que la propia tabla, por lo que hay que tener        cuidado a la hora de escoger una columna como índice. También hay que tener en cuenta que hay que actualizar el        índice cada vez que se modifica la columna en la tabla por lo que no resulta conveniente elegir como índices        aquellas columnas que creamos que van a escribirse con mucha frecuencia.
 +
 +{{ indice.jpg |Uso de índices}}
 ===== Creación de un script SQL ===== ===== Creación de un script SQL =====
  
bloque4/sql.1651218848.txt.gz · Última modificación: 2024/09/16 19:34 (editor externo)