Bases de Datos MySQL

Tablas y consultas.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

1

Bases de Datos MySQL Recordemos el modelo

Entidad – Relación

de una escuela...

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

2

Bases de Datos MySQL

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

3

Bases de Datos MySQL

Propiedades de las entidades.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

4

Bases de Datos MySQL

La entidad “alumnos” se convierte en la tabla: Entidad Tabla Propiedad Columna Cada fila representa un Registro.

La clave puede estar formada por una o mas columnas

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

5

Bases de Datos MySQL Para crear la base de datos “escuela” utilizaremos el Sistema de Gestión de Bases de Datos MySQL

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

6

Bases de Datos MySQL Ventajas de MySQL: * Robusto * Código abierto * Forma parte de

LAMP

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

7

Bases de Datos MySQL Existe una herramienta, llamada phpMyAdmin , que permite administrar las bases de datos MySQL mediante una interfaz sencilla creada en PHP...

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

8

Bases de Datos MySQL Ejercicio: En una base de datos nueva, crear la tabla “alumnos”, con los campos

clave_alumno

,

nombre

,

edad

y

curso_actual

utilizando phpMyAdmin.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

9

Bases de Datos MySQL

Creación de una tabla: Indicar, para cada campo de la tabla los siguientes datos:

Field :

Nombre del atributo.

Type :

Tipo de dato del atributo.

Length/Values :

Indica la longitud máxima del tipo de dato (especialmente para VARCHAR).

Attributes :

UNSIGNED : Sólo toma valores positivos (para INTEGER, SMALLINT, ...).

Null :

El atributo puede tomar valores nulos?

(la clave o identificador de la tabla nunca puede tener valores nulos!!!).

Default :

Valor por defecto del atributo al añadir una nueva fila.

Extra :

AUTO_INCREMENT (normalmente se usa para generar, automáticamente, valores de la clave).

Primary :

Se debe marcar si el atributo es Clave Primaria (el identificador) de la tabla.

Index :

Crea un índice dentro de la tabla.

Unique:

Se debe marcar si el valor del atributo es único para toda la tabla.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

10

Bases de Datos MySQL

Tipos de Datos en MySQL:

Los tipos de datos que puede haber en un campo o atributo de una tabla forman tres grandes grupos: 1. Tipos Numéricos 2. Tipos de Fecha 3. Tipos de Cadena de Caracteres DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

11

Bases de Datos MySQL

1) Tipos numéricos:

Existen tipos de datos numéricos, que se pueden dividir en dos grandes grupos, los que incluyen valores decimales (coma flotante) y los que no.

TinyInt:

es un número entero con o sin signo. Con signo el rango de valores válidos va desde -128 a 127. Sin signo, el rango de valores es de 0 a 255

Bit ó Bool:

un número entero que puede ser 0 ó 1

SmallInt:

número entero con o sin signo. Con signo el rango de valores va desde -32768 a 32767. Sin signo, el rango de valores es de 0 a 65535.

MediumInt:

número entero con o sin signo. Con signo el rango de valores va desde -8.388.608 a 8.388.607. Sin signo el rango va desde 0 a 16777215.

Integer, Int:

número entero con o sin signo. Con signo el rango de valores va desde -2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295

BigInt:

número entero con o sin signo. Con signo el rango de valores va desde -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va desde 0 a 18.446.744.073.709.551.615.

Float:

número pequeño en coma flotante de precisión simple. Los valores válidos van desde -3.402823466E+38 a 1.175494351E-38, 0 y desde 1.175494351E-38 a 3.402823466E+38.

Real, Double:

número en coma flotante de precisión doble. Los valores permitidos van desde 1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde 2.2250738585072014E-308 a 1.7976931348623157E+308

Decimal, Dec, Numeric:

Número en coma flotante. El número se almacena como una cadena DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

12

Bases de Datos MySQL

2) Tipo Fecha:

Al almacenar fechas, hay que tener en cuenta que Mysql no comprueba de una manera estricta si una fecha es válida o no. Simplemente comprueba que el mes está entre 0 y 12 y que el día está entre 0 y 31.

Date:

Almacena una fecha. El rango de valores va desde el 1 de enero del 1001 al 31 de diciembre de 9999. El formato de almacenamiento es de: año-mes-dia

DateTime:

Combinación de fecha y hora. El rango de valores va desde el 1 de enero del 1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las 23 horas, 59 minutos y 59 segundos. El formato de almacenamiento es de año-mes-dia horas:minutos:segundos

TimeStamp:

año 2037. Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al

Time:

Almacena la hora. El rango de horas va desde -838 horas, 59 minutos y 59 segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de 'HH:MM:SS'

Year:

almacena un año. El rango de valores permitidos va desde el año 1901 al año 2155. El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar el año con dos o cuatro dígitos.

DPOM - Bases de datos MySQL. 13 Prof. Eugenio Tisselli.

Bases de Datos MySQL

3) Tipos de Cadena de Caracteres: Char(n):

almacena una cadena de longitud fija. La cadena podrá contener desde 0 a 255 caracteres.

VarChar(n):

almacena una cadena de longitud variable. La cadena podrá contener desde 0 a 255 caracteres.

Dentro de los tipos de cadena se pueden distinguir otros dos subtipos, los de tipo

Text

y los de tipo

BLOB

(Binary large Object). La diferencia entre un tipo y otro es el tratamiento que reciben a la hora de realizar ordenamientos y comparaciones. Mientras que el tipo

Text

se ordena sin tener en cuenta las Mayúsculas y las minúsculas, el tipo

BLOB

se ordena teniéndolas en cuenta.

Los tipos

BLOB

se utilizan para almacenar datos

binarios

como pueden ser ficheros.

TinyText y TinyBlob:

Columna con una longitud máxima de 255 caracteres.

Blob y Text:

un texto con un máximo de 65535 caracteres.

MediumBlob y MediumText:

un texto con un máximo de 16.777.215 caracteres.

LongBlob y LongText:

un texto con un máximo de caracteres 4.294.967.295. Hay que tener en cuenta que debido a los protocolos de comunicación en Internet, los paquetes pueden tener un máximo de 16 Mb.

Enum:

campo que puede tener un único valor de una lista que se especifica. El tipo Enum acepta hasta 65535 valores distintos

Set:

un campo que puede contener ninguno, uno ó varios valores de una lista. La lista puede tener un máximo de 64 valores.

DPOM - Bases de datos MySQL. 14 Prof. Eugenio Tisselli.

Bases de Datos MySQL

Ejercicio:

Insertar registros en la nueva tabla “alumnos”. Insertar al menos 4 registros de este tipo: DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

15

Bases de Datos MySQL El lenguaje standard para los sistemas de bases de datos relacionales se llama

SQL

.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

16

Bases de Datos MySQL

Componentes del lenguaje SQL: *

DML (Data Manipulation Language) Manipulación de los datos Seleccionar / Añadir / Modificar / Borrar

* DDL (Data Definition Language) Creación de objetos de la BD Creación de tablas, ...

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

17

Bases de Datos MySQL

DML

= Data Manipulation Language (Manipulación de los Datos) * Select Permite hacer

consultas

en la BD, obteniendo una nueva “tabla” con los resultados de la consulta * Insert Permite

añadir

registros en la BD (filas de una tabla) * Update Permite

modificar

registros en la BD (filas de una tabla) * Delete Permite

borrar

registros en la BD (filas de una tabla) DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

18

Bases de Datos MySQL

Consulta de datos:

SELECT

Notación: SELECT FROM ; Ejemplo sobre la tabla

alumnos

:

empleados (clave_alumno, nombre, edad, curso_actual)

SELECT nombre, edad FROM alumnos;

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

19

Bases de Datos MySQL

Para mostrar todos los campos (columnas) de la tabla alumnos: SELECT * FROM empleados; Para evitar mostrar filas duplicadas: SELECT

DISTINCT

nombre FROM alumnos; DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

20

Bases de Datos MySQL

Filtros

(WHERE). Sirven para aplicar condiciones (o restricciones) en la selección de datos.

Notación: SELECT FROM

WHERE ;

• Ejemplo: SELECT nombre, curso_actual FROM alumnos WHERE edad = 10; DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

21

Bases de Datos MySQL

Operadores de comparación aplicables en la cláusula WHERE:

= > < >= <= <>

Operadores lógicos aplicables en la cláusula WHERE: AND / OR / NOT Otros predicados aplicables en la cláusula WHERE:

BETWEEN

AND

{Rango de valores}

LIKE

{cadena de caracteres } Comodines: ‘%’, ‘_’

IS [NOT] NULL

{consultar si el atributo tiene valor o no} DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

22

Bases de Datos MySQL

Ejemplos:

AND:

SELECT nombre FROM alumnos WHERE curso_actual = 3 AND edad >= 10;

BETWEEN:

SELECT * FROM alumnos WHERE edad BETWEEN 8 AND 12;

LIKE:

SELECT * FROM alumnos WHERE nombre LIKE "M%" AND curso_actual = 3; DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

23

Bases de Datos MySQL

Ejercicios: 1. Obtener el nombre de todos los alumnos que actualmente estén cursando cuarto año.

2. Obtener todos los datos de los alumnos cuya edad sea menor o igual que diez años, o que estén en un curso anterior al tercero.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

24

Bases de Datos MySQL

Ordenación de los datos presentados (ORDER BY)

Notación: SELECT FROM [ WHERE ]

ORDER BY , ..., ;

Ejemplo: SELECT nombre, edad FROM alumnos ORDER BY edad DESC; {por defecto es ASC} DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

25

Bases de Datos MySQL

Funciones de agregación

COUNT() {devuelve el total de filas seleccionadas} SUM() {suma los valores de una columna} MIN() {devuelve valor mínimo columna} MAX() {devuelve valor máximo columna} AVG() {devuelve la mediana de la columna} ...

• Ejemplos: SELECT COUNT(*) FROM alumnos; SELECT AVG(edad) FROM alumnos WHERE curso_actual = 3; DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

26

Bases de Datos MySQL

Ejercicios: 3. Obtener la edad del mayor de los alumnos.

4. Obtener el número de alumnos cuya edad sea 11 años.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

27

Bases de Datos MySQL

Ejercicios: 5. Crear el resto de tablas: profesores, listas, aulas, asignaturas, horarios.

OJO: la tabla “listas” tiene los siguientes campos:

clave_alumno, clave_profesor

.

Ambos campos forman la clave de la tabla.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

28

Bases de Datos MySQL

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

29

Bases de Datos MySQL

Ejercicios extra: 1. Crear y ejecutar el comando SELECT para obtener los nombres (ordenados alfabéticamente) de los alumnos que tengan 10 años y estén en el tercer curso.

2. Crear y ejecutar el comando SELECT para obtener el nombre y curso actual de los alumnos cuya edad esté entre 11 y 12.

3. Obtener el número de alumnos cuya edad sean 10 años.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

30

Bases de Datos MySQL

Relaciones entre tablas: Recordemos que un aula puede tener varias asignaturas, según nuestro modelo… Por lo tanto, la relación entre las tablas está dada por la

relación entre sus campos

… DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

31

Bases de Datos MySQL

Aulas (

clave_aula

, nombre … ) Asignaturas (

clave_asignatura

, clave_aula

…

) En la tabla “Asignaturas”, clave_aula hace referencia a la clave del mismo nombre en la tabla “Aulas”.

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

32

Bases de Datos MySQL

Alumnos (

clave_alumno

, nombre … ) Listas (

clave_alumno

,

clave_profesor

) Profesores (

clave_profesor

, nombre … ) DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

33

Bases de Datos MySQL

Consultas en más de una tabla

Notación: SELECT FROM , , ... , [ WHERE ] Ejemplo: SELECT asignaturas.nombre, aulas.nombre

FROM asignaturas, aulas

WHERE aulas.clave_aula = asignaturas.clave_aula;

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

34

Bases de Datos MySQL

Ejemplo 1.

Nombres de las aulas donde se hace la asignatura de Historia: SELECT aulas.nombre FROM aulas, asignaturas WHERE asignaturas.nombre = 'Historia' AND aulas.clave_aula = asignaturas.clave_aula

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

35

Bases de Datos MySQL

Ejemplo 2.

Horarios y dias en los que se imparte la asignatura de Historia: SELECT horarios.hora_inicio, horarios.hora_fin, horarios.dia FROM horarios, asignaturas WHERE asignaturas.nombre = 'Historia' AND horarios.clave_asignatura = asignaturas.clave_asignatura ORDER BY horarios.dia, horarios.hora_inicio

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

36

Bases de Datos MySQL

Ejemplo 3.

Asignaturas que comienzan entre 8:00 y 10:00: SELECT asignaturas.nombre, horarios.hora_inicio, horarios.hora_fin, horarios.dia FROM asignaturas, horarios WHERE asignaturas.clave_asignatura = horarios.clave_asignatura AND horarios.hora_inicio BETWEEN '08:00:00' AND '10:00:00' ORDER BY horarios.dia, horarios.hora_inicio, asignaturas.nombre

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

37

Bases de Datos MySQL

Ejemplo 4.

Asignaturas que comienzan entre 8 y 10 el dia lunes: SELECT asignaturas.nombre, horarios.hora_inicio, horarios.hora_fin, horarios.dia FROM asignaturas, horarios WHERE asignaturas.clave_asignatura = horarios.clave_asignatura AND horarios.hora_inicio BETWEEN '08:00:00' AND '10:00:00' AND horarios.dia = 'lunes' ORDER BY horarios.dia, horarios.hora_inicio, asignaturas.nombre

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

38

Bases de Datos MySQL

Ejemplo 5.

Alumnos cuyo profesor es 'Roberto Juarez': SELECT DISTINCT alumnos.nombre FROM alumnos, profesores, listas WHERE profesores.nombre = 'Roberto Juarez' AND listas.clave_profesor = profesores.clave_profesor AND alumnos.clave_alumno = listas.clave_alumno ORDER BY alumnos.nombre

DPOM - Bases de datos MySQL. Prof. Eugenio Tisselli.

39