3. Creacion y manipulacion de tablas

3. Creacion y manipulacion de tablas Dataprix 5 Octubre, 2009 - 12:13

3.1. Crear tablas

3.1. Crear tablas Dataprix 6 Octubre, 2009 - 10:24

Una vez realizada la conexión con el servidor MySQL y después de abrir una base de datos, podemos crear tablas en ella de la siguiente manera:

mysql> create table personas (
-> nombre char(30),
-> dirección char(40),
-> teléfono char(15)
-> );
Query OK, 0 rows affected (0.02 sec)

En este caso, la sentencia create table construye una nueva tabla en la base de datos en uso. La tabla contiene tres columnas, nombre, dirección y teléfono, todas de tipo carácter y de longitudes 30, 40 y 15 respectivamente. Si se intenta guardar en ellas valores que sobrepasen esos límites, serán truncados para poderlos almacenar. Por ese motivo, es importante reservar espacio suficiente para cada columna. Si se prevé que muchos registros ocuparán sólo una fracción del espacio reservado, se puede utilizar el tipo varchar, similar a char, con la diferencia de que el valor ocupará un espacio menor al especificado si
la cadena es más corta que el máximo indicado, ahorrando así espacio de almacenamiento.

Los nombres de las columnas admiten caracteres acentuados.  Las tablas pueden eliminarse con drop table:

mysql> drop table personas;
Query OK, 0 rows affected (0.01 sec)

Alternativamente, se puede utilizar la sintaxis siguiente:

mysql> drop table if exists personas;

 

Atributos de columna

Atributo              Significado                                                                                                                            
null Se permiten valores nulos, atributo por omisión si no se especifica
lo contrario.
not null No se permiten valores nulos.
default valor Valor por omisión que se asigna a la columna.
auto_increment El valor se asigna automáticamente incrementando en uno el máximo valor registrado hasta ahora. Se aplica sólo a las columnas marcadas como clave primaria.
primary key

Señala al campo como clave primaria, implícitamente también lo declara como not null.

 

Veámoslo con un ejemplo:

mysql> create table personas (
-> nombre varchar(40) not null,
-> dirección varchar(50) null,
-> edo_civil char(13) default ’Soltero’,
-> num_registro int primary key auto_increment,
-> ) ;
Query OK, 0 rows affected (0.01 sec)
Nota                                                       
La definición de columnas tiene el siguiente formato:
nombre_columna tipo atributos.

En este caso la tabla contiene cuatro columnas, de las cuales nombre y edo_civil permiten valores nulos, en edo_civil está implícito al no declarar lo contrario. La columna num_registro no acepta valores nulos porque está definida como clave primaria.

Aunque la creación de una clave primaria puede declararse como atributo de columna, es conveniente definirla como restricción de tabla, como se verá enseguida.

También es posible indicar restricciones sobre la tabla y no sobre columnas especificas:

mysql> create table personas (
-> nombre varchar(40) not null,
-> nacimiento date not null,
-> pareja varchar(40),
-> proveedor int not null,
->
-> primary key (nombre,nacimiento),
-> unique (pareja),
-> foreign key (proveedor) references proveedores
-> );
Query OK, 0 rows affected (0.01 sec)

Restricciones de tabla

Restricción Significado
primary key Define la o las columnas que servirán como clave primaria. Las columnas que forman parte de la clave primaria deben de ser not null.
unique Define las columnas en las que no pueden duplicarse valores. Serán las claves candidatas del modelo relacional.
foreign key (columna)
references tabla
(columna2)
Define que los valores de columna se permitirán sólo si existen en tabla(columna2). Es decir, columna hace referencia a los registros de tabla, esto asegura que no se realicen referencias a registros que no existen.

 

Se definen tres restricciones sobre la tabla después de la definición de cuatro columnas:

•    La primera restricción se refiere a la clave primaria, compuesta por las columnas nombre y nacimiento: no puede haber dos personas que se llamen igual y que hayan nacido en la misma fecha. La clave primaria permite identificar de manera unívoca cada registro de la tabla.

Claves foráneas                                   
Las restricciones de tabla foreign key no tienen efecto alguno en MySQL 4.0 y anteriores, ya que esta característica no está implementada. Se admite en la sintaxis por compatibilidad, ya que será implementada en una versión posterior. En la versión 4.1, está soportada si se utiliza
el tipo de tabla InnoDB.

•    La segunda restricción define que la pareja de una persona debe ser única: dos personas no pueden tener la misma pareja. Todo intento de insertar un nuevo  registro  donde  el  nombre  de  la  pareja  ya  exista,  será  rechazado. Cuando se restringe una columna con unique, los valores null reciben un trato especial, pues se permiten múltiples valores nulos.

•    La tercera restricción afecta a la columna proveedor, sólo puede tomar valores que existan en la clave primaria de la tabla proveedores.

Las restricciones de tabla pueden definirse con un identificador útil para hacer referencias posteriores a la restricción:

mysql> create table personas (
-> nombre varchar(40) not null,
-> nacimiento date not null,
-> pareja varchar(40),
-> proveedor int not null,
->
-> constraint clave primary key (nombre,nacimiento),
-> constraint monogamo unique (pareja),
-> constraint trabaja_en foreign key (proveedor) references
proveedores
-> );

 

key / index

La definición de índices puede hacerse también en el momento de creación de la tabla, mediante la palabra clave key (o index), a la que deberemos proporcionar el nombre que vamos a asignar a esta clave y las columnas que la forman, entre paréntesis. Existen modificadores opcionales sobre el índice que nos permiten especificar si se trata de un índice único o múltiple (según puedan existir o no varios valores iguales del índice en la tabla).

En versiones recientes de MySQL existen otros tipos de índices (espaciales, de texto completo, etc.) para tipos de datos concretos y que ofrecen prestaciones adicionales.

3.2. Tipos de datos

3.2. Tipos de datos Dataprix 6 Octubre, 2009 - 10:31

MySQL cuenta con un rico conjunto de tipos de datos para las columnas, que es necesario conocer para elegir mejor cómo definir las tablas. Los tipos de datos se pueden clasificar en tres grupos:

•    Numéricos.   
•    Cadenas de caracteres
•    Fechas y horas

El valor null es un caso especial de dato, ya que al significar ausencia de valor se aplica a todos los tipos de columna. Los siguientes símbolos se utilizan en la definición y descripción de los tipos de datos en MySQL:

•    M - El ancho de la columna en número de caracteres.
•    D - Número de decimales que hay que mostrar.
•    L - Longitud o tamaño real de una cadena.
•    [ ] - Lo que se escriba entre ellos es opcional.

3.2.1. Tipos de datos numericos

3.2.1. Tipos de datos numericos Dataprix 6 Octubre, 2009 - 10:44

Los tipos de datos numéricos comprenden dos categorías, los enteros y los números con punto flotante.

Números enteros

La principal diferencia entre cada uno de los tipos de enteros es su tamaño, que va desde 1 byte de almacenamiento hasta los 8 bytes. Las columnas de tipo en- tero pueden recibir dos atributos adicionales, que deben especificarse inmediatamente después del nombre del tipo:

•    unsigned. Indica que el entero no podrá almacenar valores negativos. Es responsabilidad del usuario verificar, en este caso, que los resultados de las restas no sean negativos, porque MySQL los convierte en positivos.

•    zerofill. Indica que la columna, al ser mostrada, rellenará con ceros a la iz- quierda los espacios vacíos. Esto de acuerdo al valor especificado por M en la declaración del tipo. Una columna con el atributo zerofill es al mismo tiempo unsigned aunque no se especifique.

 

Ejemplo

create table números (
x int(4) zerofill not null,
y int(5) unsigned
);

El comando anterior crea una tabla con dos columnas. Ambas ocuparán un espacio de 4 bytes, pero al mostrarse, la columna x ocupará un espacio de 4 dígitos y la columna y, de 5.

 

Tanto zerofill como unsigned deben escribirse siempre antes que cualquier otro atributo de columna.

 

Tipos enteros

Tipo Espacio de almacenamiento Significado
tinyint[(M)] 1 byte Entero muy pequeño
smallint[(M)] 2 bytes Entero pequeño
mediumint[(M)] 3 bytes Entero mediano
int[(M)] 4 bytes Entero
bigint[(M)] 8 bytes Entero grande

Números con punto flotante

MySQL cuenta con los tipos float y double, de 4 y 8 bytes de almacenamiento. Además incluye el tipo decimal, que se almacena como una cadena de carac- teres y no en formato binario.

 

Números de punto flotante

Tipo Espacio de almacenamiento Significado
float 4 bytes Simple precisión
double 8 bytes Doble precisión
decimal M + 2 bytes Cadena de caracteres representando un número flotante

3.2.2. Cadenas de caracteres

3.2.2. Cadenas de caracteres Dataprix 6 Octubre, 2009 - 11:01

Cadenas de caracteres

Tipo Equivalente Tamaño máximo Espacio de almacenamiento
 char[(M)]  M bytes  M bytes
 varchar[(M)]  M bytes  L+1 bytes
 tinytext  tinyblob  28-1 bytes  L+1 bytes
 text  blob  216-1 bytes  L+2 bytes
 mediumtext  mediumblob  224-1 bytes  L+3 bytes
 longtext  longblob  232 -1 bytes  L+4 bytes
 enum('v1','v2',...)  65535 valores  1 o 2 bytes
 set('v1','v2',...)  64 valores  1 a 8 bytes

Si observamos la tabla, vemos que el único tipo de dato que siempre utiliza el tamaño especificado por M es el tipo char. Por este motivo, se ofrece el tipo varchar que ocupa sólo el espacio requerido por el valor de la columna.

Ejemplo

create table persona(
comentario char(250),
recado varchar(250)
);

La columna comentario ocupará 250 bytes de espacio de almacenamiento, sin importar el valor almacenado. Por el contrario, la columna recado ocupará sólo el espacio necesario según el valor asignado; por ejemplo, la cadena “Instalar MySQL” tiene 14 bytes de longitud, y el campo recado ocuparía 15 bytes para almacenarla.

Los tipos text y blob son equivalentes, pero text respeta las mayúsculas, mi- núsculas y caracteres acentuados en la ordenación.

Ejemplo del uso de los tipos enumerados o enum

create table persona(
edo_civil enum(’soltero’,’casado’,’viudo’,’divorciado’)
);

La columna edo_civil de la tabla en la sentencia anterior, solo podrá almacenar los valores ’soltero’, ’casado’, ’viudo’, ’divorciado’, que son especificados por el tipo enum. La columna ocupará el espacio de un byte, ya que los valores enum son representados internamente por números.

3.2.3. Fechas y horas

3.2.3. Fechas y horas Dataprix 6 Octubre, 2009 - 11:16

Fechas y horas

Tipo Espacio de almacenamiento Rango
 date  3 bytes  '1000-01-01' al ' 9999-12-31'
time 3 bytes  ' -838:59:59' a ' 838:59:59'
datetime 8 bytes  ' 1000-01-01-00:00:00'a ' 9999-12-31 23:59:59'
timestamp[(M)] 4 bytes  19700101000000 al año 2037
year[(M)] 1 bytes  1901 a 2155

3.3. Modificar tablas

3.3. Modificar tablas Dataprix 6 Octubre, 2009 - 11:21

3.3.1. Agregar y eliminar columnas en MySQL

3.3.1. Agregar y eliminar columnas en MySQL Dataprix 6 Octubre, 2009 - 11:30

Alterar la estructura de una tabla de MySQL es una tarea más frecuente de lo que uno puede imaginar en un principio. La  sentencia alter table permite una amplia gama de formas de modificar una tabla. La siguiente sentencia nos recuerda un poco a la estructura de la sentencia create table, en donde modificamos la tabla personal creada en la sección anterior.

Nota                                                       
Siempre es posible consultar la estructura de una tabla con el comando describe tabla.

 

 

 

 

mysql> alter table personal add (
-> mascota char(30) default ’perro’,
-> pasatiempo char (20) not null
-> );

Después de ejecutar la sentencia anterior, aparecen dos nuevas columnas en la tabla. Si queremos agregar una sola columna, podemos usar la sintaxis siguiente:

mysql> alter table personal add capital int not null
-> after nom;

 

Este formato de alter  table permite, además, insertar las columnas antes (before) o después (after) de una columna en cuestión.

Las columnas no deseadas pueden eliminarse con la opción drop.

mysql> alter table personal drop pasatiempo;

3.3.2. Modificar columnas

3.3.2. Modificar columnas Dataprix 6 Octubre, 2009 - 11:42

La modificación de una columna con la opción modify es parecida a volver a definirla.

mysql> alter table personal modify
-> mascota char (14) default ’gato’;

Después de la sentencia anterior, los atributos y tipo de la columna han cambiado por los especificados. Lo que no se puede cambiar con esta sintaxis es el nombre de la columna. Para ello, se debe utilizar la opción change:

m

ysql> alter table personal change nom
-> nombre char(20);

La columna que se llamaba nom cambia a nombre.

Nota                                                       
En general, una tabla no puede durar mucho tiempo con un order respecto a una columna, ya que las inserciones no se realizarán respetando el orden establecido. Solamente en tablas que no van a ser actualizadas es útil aplicar este comando.

Con el mismo comando alter table podemos incluso realizar la ordenación física de una tabla bajo una columna específica:

 

 

 

 

mysql> alter table personal order by nom;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

Finalmente, podemos cambiar de nombre la

tabla:

mysql> alter table personal rename gente;

 

 

rename table                                        
El comando rename table viejo_nombre to nuevo_nombre es una forma alternativa de cambiar el nombre a una tabla.

 

 

 

 

3.4. Otras opciones

3.4. Otras opciones Dataprix 6 Octubre, 2009 - 11:44

3.4.1. Copiar tablas

3.4.1. Copiar tablas Dataprix 6 Octubre, 2009 - 11:48

Aunque no existe un comando explícito para copiar tablas de una base de datos a otra, es posible utilizar el comando rename table para este propósito; basta con especificar la base de datos a la que pertenece una tabla:

mysql> rename table base_uno.tabla to base_dos.tabla;

También es posible crear una tabla nueva con el contenido de otra ya existente (copiando los datos):

mysql> create table nueva_tabla select * from otra_tabla;

La siguiente sentencia es equivalente, pero no copia los datos de la tabla origen:

mysql> create table nueva_tabla like otra_tabla;

3.4.2. Tablas temporales

3.4.2. Tablas temporales Dataprix 6 Octubre, 2009 - 11:50

MySQL permite la creación de tablas temporales, visibles exclusivamente en la sesión abierta, y guardar datos entre consultas. La creación de una tabla temporal sólo requiere la utilización de la palabra temporary en cualquier formato del comando create table. La utilidad de las tablas temporales se limita a consultas complejas que deben generar resultados intermedios que debemos consultar (hacer 'join' con ellas) varias veces o en consultas separadas. Internamente, MySQL genera también tablas temporales para resolver determinadas consultas:

mysql> create temporary table nueva_tabla ...