Aprendiendo PHP, parte 24: Introducción a MySQL, creación de tablas

23 Dic

Continuando con el diagrama de nuestra base de datos, ahora podemos pasar a la acción, creando nuestra primer base de datos y sus tablas. Pero para esto esto primero vamos a detenernos en algo muy importante, las tablas como ya sabemos tienen atributos, y estos atributos tienen un tipo de dato, dependiendo de si son cadenas de texto, números o fechas.

En esta ocasión no voy a mostrar todos los tipos de datos que existen, pero sí los que a mí parecer, son los más usados:

Tipos de datos numéricos

Existen cinco tipos de datos numéricos, estos dependerán del valor máximo que necesitemos guardar, del volumen de información. Por ejemplo si tuviésemos un blog, no será el mismo tipo de dato el que usemos para guardar la cantidad de categorías, que no deberían ser muchas; 5, 10, 15; que la cantidad de comentarios que puede tener ese blog, que pueden llegar a ser miles.

En primer lugar, cada tipo de dato soportará hasta un valor máximo dependiendo de si tiene signo o no. O sea, si soporta valores negativos o sólo positivos. Por ejemplo, el tipo de dato tinyint, configurado sin signo (sólo números positivos) tendrá un rango que irá desde el valor 0 hasta el 255, de lo contrarió será desde -128 hasta 127. Este tipo de dato nos podría ser útil para guardar por ejemplo las categorías de un foro que nombramos antes, con 255 sería más que suficiente, pero no para guardar las publicaciones o comentarios de ese blog, ya que puede haber muchísimo más que 255.

Los valores numéricos son:

Tipo Bytes Valor Mínimo Valor Máximo
(Con signo/Sin signo) (Con signo/Sin signo)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

(Fuente: http://dev.mysql.com/doc/refman/5.0/es/numeric-types.html)

Tipos de datos de cadenas de caracteres

En MySQL existen cuatro grupos de tipos de datos para guardar cadenas.

Por un lado tenemos char y varchar, los cuales nos permitirán guardar cadenas de hasta  255 caracteres. La principal diferencia entre ambos es que char usa la cantidad de caracteres que asignemos, rellenando los caracteres restantes con espacios. Por ejemplo podemos configurar nuestro atributo como un char de 30 caracteres, pero si insertamos registros con valores menores a 30, por ejemplo un valor de 12, éste se guardará con los caracteres que ingresamos, y automáticamente agregará espacios en blanco hasta completar los 30. Esto con varchar no sucede, sólo se usarán la cantidad de caracteres del registro guardado. Por esto se suele tener preferencia por varchar, ya que nos permite ahorrar memoria comparado con char.

char y varchar son útiles para guardar cadenas pequeñas, por ejemplo nombres de personas. Pero si necesitamos guardar valores muy grandes, deberíamos usar blog o text, que nos permitirán almacenar extensas cadenas como por ejemplo la letra de una canción. La diferencia entre ambas es que blog almacena datos binarios, mientras que text nos permiten guardar cadenas de caracteres. Por este motivo, text es más utilizado que blog, ya que es más fácil de trabajar con sus valores.

También tenemos los tipos de datos binary y varbinary, que son igual a char y varchar, pero a  diferencia de estos, permiten guardar datos binarios y no cadenas de texto.

Y por últimos tendremos los tipos de datos enum y set, que nos permitirán guardar un valor extraído de una lista. Por ejemplo un atributo sexo, que sólo podrá ser dos valores: ‘hombre’ o ‘mujer’. La diferencia entre ambos es que enum sólo guardará un valor de la lista especificada, mientras que set puede guardar varios de estos separado por coma, por ejemplo: ‘hombre,mujer’.

Tipos de datos de fecha y hora

Los tipos de dato de fecha y hora son muy útiles a la hora de hacer seguimientos, para guardar la fecha de un suceso o la hora del mismo, o incluso ambos.

Estos tipos de datos pueden ser date, para guardar una fecha, el formato, por ejemplo para el 23 de Diciembre del año 2013, debe ser de la siguiente manera: ‘2013-12-23’. Para guardar la hora deberíamos usar el tipo de dato time, el formato es algo como esto: ’00:56:41′, separado en ‘horas:minutos:segundos’.

Si en cambio quisiéramos guardar la fecha y hora en un mismo campo deberíamos usar datetimetimestamp. La diferencia entre ambos es que datetime guarda sus valores con el siguiente formato: ‘2013-12-23 00:56:41’, mientras que timestamp comprenderá el formato unix, osea la cantidad de segundos desde 1 de Enero de 1970 a las 00:00:00, la misma fecha y hora que guardamos con datetime podríamos guardarla en una columna timestamp de esta forma ‘1387756601’. Otra diferencia entre ambos es que si bien datetime tiene un formato más amigable ante los ojos humanos, timestamp ocupa menos memoria que el primero.

Para profundizar sobre tipos de datos, podés consultar el manual de MySQL:

http://dev.mysql.com/doc/refman/5.0/es/column-types.html

Crear base de datos

Para crear nuestra base de datos, primero debemos asegurarnos que en nuestro localhost esté encendido el servicio MySQL. Así que vamos a abrir nuestro panel de control del xampp, y además de iniciar el servicio Apache, como siempre hacemos, también vamos a pulsar ‘Start’ en la segunda opción, la de MySQL.

Ambos servicios, tanto Apache como MySQL,  deben estar encendidos.

Ahora vamos a abrir nuestro navegador y vamos a ingresar la siguiente dirección:

http://localhost/phpmyadmin

Aquí tendremos un panel con nuestras base de datos, así que vamos a pulsar en el botón ‘Bases de datos’

Aquí debemos ingresar un nombre para nuestra base de datos y el cotejamiento, en este último vamos a seleccionar el valor: ‘utf8_spanish2_ci’, para que nuestras tablas soporten cadenas con caracteres especiales como tildes.

Y acto seguido vamos a seleccionar, en la lista de la izquierda, la base de datos que acabamos de crear

Bien, ya tenemos nuestra base de datos, ahora sólo nos resta crear nuestras tablas, basadas en el diagrama que habíamos creado la última vez.

Cada vez que tengamos que ejecutar código SQL, debemos pulsar el botón, valga la redundancia: ‘SQL’

Empecemos por crear la tabla nacionalidad, pulsamos ‘SQL’ y escribimos en el campo de entrada el siguiente código:

CREATE TABLE nacionalidad (
   nacionalidad_id tinyint(3) unsigned not null auto_increment primary key,
   valor varchar(50) not null
)engine=innoDB;

Una vez ingresado el código pulsamos el botón ‘Continuar’ para crear nuestra tabla.

Ahora vamos a analizar el código.

En primer lugar escribimos:

CREATE TABLE nombre_de_la_tabla()engine=innoDB;

Esto es muy fácil de entender. Creamos una tabla con un nombre X, dentro de la apertura y cierre de paréntesis vamos a definir los atributos de esta tabla. Por último al finalizar con la creación de la tabla definimos el motor de la tabla, que en nuestro caso será innoDB.

Los atributos serán sólo dos, la clave primaria y el valor, osea el nombre de la nacionalidad.

nacionalidad_id tinyint(3) unsigned not null auto_increment primary key,

Lo primero que debemos hacer es definir el nombre del atributo y luego su tipo, entre paréntesis indicamos la cantidad de dígitos que tendrá éste. En este caso definimos tinyint, que sin signo soporta hasta 255 caracteres, lo que es más que suficiente para guardar la cantidad de nacionalidades que necesitamos. Como tinyint es un valor numérico especificamos que es unsigned (en criollo sin signo, sin valores negativos)

Finalmente ingresamos tres instrucciones más para este atributo. Que no sea null (not null), auto_increment y primary key (clave primaria o identificador) Estos dos últimos valores en conjunto nos permitirán que cuando insertemos un nuevo registro en esta tabla, no sea necesarios setear la clave primaria, por defecto comenzará desde 1, seguirá en 2, 3, 4, 5 y así sucesivamente.

valor varchar(50) not null

Y por último ingresamos el atributo ‘valor’ que será de tipo de dato varchar de 50 caracteres y not null. Con 50 caracteres como máximo es suficiente.

Además como éste es el último atributo para esta tabla, no debemos ingresar coma al final.

Bien, ahora para continuar vamos a crear nuestra tabla usuario, pero antes de continuar vamos a retomar lo que vimos en la última publicación, la relación entre tablas. Recordar que la tabla usuario y nacionalidad están unidas, en una relación de 1 a n (uno a muchos) Entonces vamos a crear la tabla de la siguiente forma:

CREATE TABLE usuario (
   usuario_id int(10) unsigned not null auto_increment primary key,
   nombre varchar(50) not null,
   apellido varchar(50) not null,
   email varchar(50) not null unique,
   contrasena varchar(50) not null,
   fecha_alta datetime not null,
   fecha_modificacion datetime not null,
   nacionalidad_id tinyint(3) unsigned not null,
   foreign key(nacionalidad_id) references nacionalidad(nacionalidad_id)
   on delete cascade on update cascade
)engine=innoDB;

Bien, en primer lugar vamos a usar una nueva instrucción para nuestro código SQL, la palabra reservada unique, que nos permite que el valor de cada registro nuevo a insertar no sea repetido, osea que no haya otro registro con ese mismo nombre. Esto es muy útil en este campo, email, ya que no debería haber dos usuarios con el mismo email.

Por otro lado agregamos dos campos nuevos fecha_alta y fecha_modificacion, que nos permitirá guardar la fecha y hora en que se inserta un nuevo usuario y la fecha y hora cuando se modifica, respectivamente. Esto es muy útil para hacer seguimientos.

fecha_alta datetime not null,
fecha_modificacion datetime not null,

Por último creamos un atributo nacionalidad_id, que será una clave externa, que estará unida a la clave primaria nacionalidad_id, de la tabla nacionalidad. Vale aclarar que ambas deben coincidir en el tipo de dato y la cantidad de dígitos que soportan, además como la clave primaria nacionalidad_id de la tabla nacionalidad es unsigned, ésta también debe serlo.

nacionalidad_id tinyint(3) unsigned not null,

Ahora para finalizar la unión entre ambas tablas, debemos usar la palabra reservada foreign key y el nombre del atributo que será una clave externa, y references para indicar con qué tabla debemos unirla y entre paréntesis el nombre del atributo de esta tabla.

nacionalidad_id tinyint(3) unsigned not null,
foreign key(nacionalidad_id) references nacionalidad(nacionalidad_id)

Y con respecto a las líneas:

on delete cascade on update cascade

Esto nos permitirá que todo cambio y eliminación de registros en la tabla padre, en nuestro caso nacionalidad, afectará a los registros relacionados con la tabla hija, en nuestro caso usuario. Otro valor, en lugar de cascade podría ser restrict, el cual no permitirá modificar o eliminar registros de la tabla padre, si está relacionados con registro de la tabla hija.

Bien, seguimos creando la tabla telefono:

CREATE TABLE telefono(
   telefono_id int(10) unsigned not null auto_increment primary key,
   valor varchar(50) not null,
   usuario_id int(10) unsigned not null,
   foreign key (usuario_id) references usuario(usuario_id)
   on delete cascade on update cascade
)engine=innoDB;

Recordemos que la tabla usuario y telefono tienen una relación de 1 a 1, por esto la clave externa podría estar en cualquiera de las dos tablas, pero en este caso la agregaremos telefono, esto nos facilita un poco más las cosas, ya que no siempre un usuario tendrá un teléfono, pero cuando se registre uno nuevo, le indicamos como clave externa el id del usuario al que le pertenece.

Continuamos con la clave interes, para guardar los intereses que puede llegar a tener un usuario:

CREATE TABLE interes(
   interes_id tinyint(2) unsigned not null auto_increment primary key,
   valor varchar(100)
)engine=innoDB;

Pero cómo habíamos dicho que la tabla interes tendría una relación de n a n (muchos a muchos) con la tabla usuario, y que también este tipo de relaciones generan una nueva tabla intermedia. Entonces vamos a crear esa tabla:

CREATE TABLE usuario_interes(
   usuario_interes int(10) unsigned not null auto_increment primary key,
   usuario_id int(10) unsigned not null,
   interes_id tinyint(2) unsigned not null,
   foreign key(usuario_id) references usuario(usuario_id)
   on delete cascade on update cascade,
   foreign key(interes_id) references interes(interes_id)
   on delete cascade on update cascade
)engine=innoDB;

Como se ve en el ejemplo, la tabla usuario_interes, hará de puente, uniendo en cada registro un usuario y un interés, guardando el identificador para cada uno.

Bien, con esto tenemos nuestra primer base de datos, con sus tablas y las tres posibles relaciones. En la próxima publicación aprenderemos a hacer consultas en estas tablas.

Saludos!

Anterior: Aprendiendo PHP, parte 23: Introducción a MySQL, tablas y relaciones

Siguiente: Aprendiendo PHP, parte 25: Introducción a MySQL, registros

Redes sociables

    2 thoughts on “Aprendiendo PHP, parte 24: Introducción a MySQL, creación de tablas

    Deja un comentario

    Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *


    *