Tema 2 Bases de Datos PDF

TEMA 2 BASES DE DATOS  Tema 2. Bases de datos Contenido GRADO EN FINANZAS Y CONTABILIDAD (FICO) 2.1 Introducción 2.2 Concepto y estructura de una...

TEMA 2 BASES DE DATOS  Tema 2. Bases de datos Contenido GRADO EN FINANZAS Y CONTABILIDAD (FICO) 2.1 Introducción 2.2 Concepto y estructura de una Base de Datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional 2.4 Sistemas de Gestión de Bases de Datos 2.5 Lenguaje SQL  Tema 2. Bases de datos 2.1 Introducción Base de datos Actualmente se generan al día millones y millones de datos: transacciones económicas, pedidos comerciales, correos electrónicos, datos recogidos por sensores, etc. El almacenamiento de estos datos para su posterior recuperación, tratamiento y transformación en información es uno de los desafíos más importantes dentro del mundo de la informática. Existen diversas soluciones para almacenar datos. Por ejemplo, se pueden almacenar datos en ficheros independientes, lo cual, aunque tiene alguna ventaja (la modificación de un fichero no afecta al resto), genera importantes inconvenientes, como un coste de almacenamiento elevado, la existencia de datos redundantes o la probabilidad alta de inconsistencia en los datos. La solución a estos problemas puede alcanzarse haciendo que los datos de todos los ficheros se unifiquen de forma homogénea y compartida, formando una estructura física y funcional conocida por el nombre de base de datos (BD).  Tema 2. Bases de datos 2.2 Concepto y estructura de una Base de Datos Metadatos Una base de datos se puede definir como un conjunto de datos relacionados que se encuentran agrupados o estructurados. La estructura la conforman el nombre de cada dato, los tipos de datos (números, texto, fechas, imágenes, etc.), los vínculos o relaciones, y las restricciones que deben cumplir esos datos (redundancia, consistencia, etc.). Estos cuatro componentes, que permiten definir la estructura de la base de datos, se denominan metadatos. Por lo tanto, una base de datos estará compuesta por los metadatos y los propios datos. La estructura de una base de datos es diseñada o descrita empleando algún tipo de modelo de datos.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo de datos Los modelos se utilizan en todo tipo de ciencias. Su finalidad es la de simbolizar una parte del mundo real de forma que sea más fácilmente manipulable. En definitiva, es un esquema mental (conceptual) en el que se intentan reproducir las características de una realidad específica (representa las características del sistema que se quiere modelar). En el caso de los modelos de datos, lo que intentan reproducir es un esquema conceptual de un conjunto de datos relacionados, es decir, de un Sistema de Información.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Entidad Relación (E-R) El Modelo Entidad Relación se caracteriza por utilizar una serie de símbolos y reglas para representar los datos y sus relaciones. Con este modelo se consigue representar de manera gráfica la estructura de una base de datos. Atributos de la entidad Atributo de la relación Entidad Relación Entidad Los principales elementos de un Modelo E-R son las entidades, las relaciones, los atributos de entidades y relaciones y los requerimientos del modelo.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Entidad Relación (E-R) METADATOS ELEMENTOS DEL MODELO E‐R Nombre y tipo Entidades y de dato atributos Relaciones Relaciones Restricciones Requerimientos  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R Entidad (concepto de interés): Grupo o conjunto de objetos, reales o abstractos, distinguibles de otros objetos y con cualidades similares. Se representan por un rectángulo dentro del modelo Entidad Relación y suelen tener por nombre un sustantivo representativo de los objetos que almacenan. Por ejemplo: (Modelo E-R para almacenar la información de un grado universitario). Una entidad Asignatura almacenará las distintas asignaturas que forman parte de un grado. Una entidad Alumno almacenará los datos de los alumnos que están matriculados en asignaturas. Una entidad Profesor almacenará los datos de los profesores que imparten clase en asignaturas. Una entidad Departamento almacenará los datos de los departamentos a los que pertenecen los profesores. Una entidad Aula almacenará los datos de las aulas en las que están asignadas las asignaturas. Una entidad Grupo almacenará los datos de los grupos en los que están asociados los estudiantes. Etc.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R Asignatura Alumno Profesor Asignatura 1 Alumno 1 Profesor 1 … … … Asignatura N Alumno N Profesor N Departamento Aula Grupo Departamento 1 Aula 1 Grupo 1 … … … Departamento N Aula N Grupo N  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R Atributos (características de las entidades): Propiedades asociadas y que caracterizan una entidad. Se representan por círculos o elipses que cuelgan o dependen de la entidad a la que pertenecen. Cada atributo permite un conjunto de valores determinado: dominio. Por ejemplo, un atributo que representa la fecha de nacimiento de una persona estará acotado superiormente por la fecha actual. No tiene sentido poner una fecha de nacimiento superior al día de hoy. Otro ejemplo, si se tiene un atributo que refleja la altura de una persona, su dominio serán los números positivos. No tiene sentido tener una persona que mida -1,70 m. Cada atributo permite un tipo de datos de terminado: número, texto, fecha, imagen, autonumérico, booleano, moneda, etc. Por ejemplo: (Modelo E-R para almacenar la información de un grado universitario). Atributos de Asignatura: Identificador, nombre, créditos, carácter, curso. Atributos de Alumno: DNI, nombre, dirección, email. Atributos de Profesor: Número de profesor, nombre, categoría, área. Atributos de Departamento: Identificador, nombre. Atributos de Aula: Identificador, capacidad. Atributos de Grupo: Identificador, tipo.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID nombre créditos carácter curso Asignatura DNI nombre dirección email Alumno NP nombre categoría área Profesor  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID nombre Departamento ID capacidad Aula ID tipo Grupo  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R El conjunto de atributos que permiten identificar unívocamente a un objeto concreto dentro de una entidad es denominado Clave. Si el número de atributos que forman la clave es el mínimo posible → Clave Primaria o principal. En el modelo se representa subrayando el atributo o conjunto de atributos que forman la clave primaria. Por ejemplo: (Modelo E-R para almacenar la información de un grado universitario). Clave primaria de Asignatura: Identificador (ID). Clave primaria de Alumno: DNI de alumno (DNI). Clave primaria de Profesor: Número de profesor (NP). Clave primaria de Departamento: Identificador (ID). Clave primaria de Aula: Identificador (ID). Clave primaria de Grupo: Identificador (ID).  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID nombre créditos carácter curso Asignatura DNI nombre dirección email Alumno NP nombre categoría área Profesor  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID nombre Departamento ID capacidad Aula ID tipo Grupo  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R Relaciones (conexiones o asociaciones): Conexiones entre entidades. Se representan dentro del diagrama con rombos, que normalmente llevan un verbo que describe la relación. Cada relación conecta o relaciona dos o más entidades. Las relaciones también pueden tener atributos. Un ejemplo de atributo de una relación puede ser la fecha en la que un alumno se ha matriculado en un grupo. Por ejemplo: (Modelo E-R para almacenar la información de un grado universitario). Relación entre Asignatura y Alumno (matricula) (atributo fecha matriculación). Relación entre Asignatura y Profesor (imparte). Relación entre Asignatura y Aula (asigna). Relación entre Alumno y Grupo (asocia). Relación entre Profesor y Departamento (pertenece).  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID créditos carácter nombre email nombre dirección Asignatura matricula Alumno curso DNI fecha ID créditos carácter área nombre nombre categoría Asignatura imparte Profesor curso NP  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID créditos carácter capacidad nombre DNI Asignatura asigna Aula curso nombre email tipo dirección ID Alumno asocia Grupo DNI  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R NP categoría área nombre ID nombre Profesor pertenece Departamento  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R Requerimientos (restricciones semánticas): Reflejan las restricciones semánticas de la relación entre entidades. Por ejemplo: (Modelo E-R para almacenar la información de un grado universitario). Cada alumno puede estar matriculado en varias asignaturas (al menos en una). Cada asignatura puede tener matriculados varios alumnos. Cada profesor puede impartir varias asignaturas. Cada asignatura puede estar impartida por varios profesores. En cada aula se pueden impartir varias asignaturas. Cada asignatura se puede impartir en varias aulas. Cada alumno puede estar asociado a varios grupos. En cada grupo pueden estar asociados varios alumnos. Cada profesor pertenece a un solo departamento. A cada departamento pueden pertenecer varios profesores. Todos los departamentos tienen un profesor que es el director en una fecha determinada.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R Estos requerimientos reflejan la cardinalidad de la relación (número de objetos de una entidad que se conectan con un objeto de otra entidad), que puede ser de tres tipos: 1:1 – Relaciona un único objeto de una entidad con un único objeto de otra entidad. 1:N – Relaciona un único objeto de una entidad con varios objetos de otra entidad. N:N – Relaciona varios objetos de una entidad con varios objetos de otra entidad. Por ejemplo: (Modelo E-R para almacenar la información de un grado universitario). Relación entre Asignatura y Alumno (matricula) (atributo fecha matriculación): N:N. Relación entre Asignatura y Profesor (imparte): N:N. Relación entre Asignatura y Aula (asigna): N:N. Relación entre Alumno y Grupo (asocia): N:N. Relación entre Profesor y Departamento (pertenece): 1:N. Relación entre Profesor y Departamento (dirige): 1:1. La cardinalidad se representa dentro del diagrama con flechas en las líneas que enlazan las relaciones con las entidades. También se pueden indicar de forma numérica. 1:1 – Flechas en ambos lados de la relación (junto a las entidades). 1:N – Flecha en el lado unitario de la relación (junto a la entidad). N:N – Ninguna flecha.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID créditos carácter nombre email nombre N N dirección Asignatura matricula Alumno curso DNI fecha ID créditos carácter área nombre nombre N N categoría Asignatura imparte Profesor curso NP  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R ID créditos carácter capacidad nombre ID N N Asignatura asigna Aula curso nombre email tipo dirección ID N N Alumno asocia Grupo DNI  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Elementos del Modelo E-R NP categoría área nombre ID nombre N 1 Profesor pertenece Departamento NP categoría área nombre ID nombre 1 1 Profesor dirige Departamento fecha  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Entidad Relación (E-R) Alumno asocia Grupo fecha matricula Asignatura asigna Aula imparte Profesor pertenece Departamento dirige (*) Simplificado sin mostrar los atributos de las entidades fecha  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional El Modelo Relacional es uno de los modelos más utilizados para la creación de bases de datos. Las formas de almacenar los datos en una computadora son variadas, dando lugar a distintos modelos de organización de datos: Modelos Orientados a Objetos, Modelos en Red, Modelos Jerárquicos, Modelos de Tabla, etc.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional Su principal característica es que se basa en la interconexión de tablas bidimensionales para la representación lógica de los datos y sus relaciones. No pueden existir dos tablas con el mismo nombre. Una dimensión representa las filas de la tabla o registros, que constituyen los datos que están contenidos en cada tabla. La otra dimensión representa las columnas de la tabla o campos, que constituyen los distintos atributos que componen la tabla. Por lo tanto, una tabla es un conjunto de registros (filas), cada una de ellas con datos en cada una de sus columnas (atributos). TABLA 1 Campo 1 Campo 2 Campo 3 Campo 4 Campo 5 Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato … … … … …  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional La relación entre tablas se lleva a cabo por medio de claves. Claves Primarias y Claves Foráneas. Una Clave Foránea es una referencia a una clave primaria de otra tabla. Las claves foráneas no necesitan ser únicas. Clave Primaria Clave Foránea TABLA 1 TABLA 2 Campo 1 Campo 2 Campo 3 Campo 4 Campo 5 Campo 6 Campo 7 Campo 1 Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato Dato … … … … … … … … Campo 1 es la Clave Principal de la TABLA 1 y Clave Foránea de la TABLA 2. Las claves principales y foráneas deben cumplir el principio de Integridad Referencial. Es decir, si se modifica o elimina una clave principal, debe modificarse o eliminarse la correspondiente clave foránea y viceversa.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional Para transformar un Modelo Entidad Relación a un Modelo Relacional seguiremos las siguientes reglas: Todas las entidades del Modelo E-R se transforma en una tabla. Cualquier atributo de una entidad se transforma en un campo dentro la tabla, manteniendo las claves primarias. Las relaciones se transforman según sea su cardinalidad, o bien generan una nueva tabla o modifican tablas existentes: Cardinalidad 1:1 – de manera general, la relación se transforma en una tabla cuyos campos son las claves primarias de las entidades que relaciona más los propios atributos. Cardinalidad 1:N – de manera general, la relación no se transforma en una tabla. La clave primaria de la entidad unitaria se convierte en un campo más de la tabla que representa la entidad n-aria. Esta clave primaria se convierte así en clave foránea. Cardinalidad N:N – de manera general, la relación se transforma en una tabla cuyos campos son las claves primarias de las entidades que relaciona más los propios atributos.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional Por ejemplo: (Modelo Relacional para almacenar la información de un grado universitario). ASIGNATURA (ID, nombre, curso, créditos, carácter) ALUMNO (DNI, nombre, email, dirección) MATRICULA (ID.asignatura, DNI.alumno, fecha) PROFESOR (NP, nombre, categoría, área, ID.departamento) IMPARTE (ID.asignatura, NP.profesor) AULA (ID, capacidad) ASIGNA (ID.asignatura, ID.aula) GRUPO (ID, tipo) ASOCIA (DNI.alumno, ID.grupo) DEPARTAMENTO (ID, nombre) DIRIGE (NP.profesor, ID.departamento, fecha) Cada entidad genera una tabla (ASIGNATURA, ALUMNO, PROFESOR, AULA, GRUPO, DEPARTAMENTO). La relación con cardinalidad 1:1 genera una nueva tabla (DIRIGE). La relación con cardinalidad 1:N propaga el atributo ID de DEPARTAMENTO (entidad unitaria) como un nuevo campo de la tabla PROFESOR (n-aria). Las relaciones con cardinalidad N:N generan nuevas tablas (MATRICULA, IMPARTE, ASIGNA, ASOCIA).  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional Cada atributo debe tener asignado un tipo de datos: ASIGNATURA IMPARTE ASOCIA ID: número. ID.asignatura: número. DNI.alumno: texto. nombre: texto. NP.profesor: número. ID.grupo: número. curso: número. créditos: número. carácter: texto. ALUMNO AULA DEPARTAMENTO DNI: texto. ID: texto. ID: número. nombre: texto capacidad: número. nombre: texto. email: texto. dirección: texto. MATRICULA ASIGNA DIRIGE ID.asignatura: número. ID.asignatura: número. NP.profesor: número. DNI.alumno: texto. ID.aula: texto. ID.departamento: número. fecha: fecha. fecha: fecha. PROFESOR GRUPO NP: número. ID: número. nombre: texto. tipo: texto. categoría: texto. área: texto. ID.departamento: número.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Modelo Relacional ASIGNATURA ASIGNA AULA IMPARTE ID nombre curso créditos carácter ID.asignatura ID.aula ID capacidad ID.asignatura NP.profesor 5573 Informática 1 6 Obligatoria 5573 B11 B11 40 5573 11456 5698 Estadística 1 6 Obligatoria 5698 B12 B12 40 5573 12987 … … … … … … … … … … … PROFESOR DIRIGE NP nombre categoría área ID.departamento NP.profesor ID.departamento fecha 11456 Profesor 1 PTUN MCPEE 1 11456 1 11/01/2010 12987 Profesor 2 AYUN MCPEE 1 11456 1 10/02/2020 … … … … … … … … DEPARTAMENTO ASOCIA GRUPO ID nombre DNI.alumno ID.grupo ID tipo 1 Economía Aplicada 12987665K 101 101 práctica 2 Economía y Administración de empresas 08451009L 102 102 práctica … … … … … … ALUMNO MATRICULA DNI nombre email dirección ID.asignatura DNI.alumno fecha 12987665K Alumno 1 [email protected] Dirección 1 5573 12987665K 07/07/2021 08451009L Alumno 2 [email protected] Dirección 2 5573 12987665K 08/07/2022 … … … … … … … BD = Metadatos + Datos  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Ejemplo Una empresa de distribución denominada NorthWindTrader quiere poner en marcha una base de datos que almacene los datos relacionados con su gestión. Es decir, datos relacionados con clientes, productos, proveedores, pedidos, etc. NorthWindTrader quiere almacenar los datos relacionados con los pedidos que recibe por parte de sus clientes. Evidentemente, puede recibir varios pedidos de un mismo cliente y cada pedido solamente puede pertenecer a un cliente. De cada pedido es necesario almacenar un identificador, la fecha en la que se ha realizado el pedido, la fecha en la que ha sido enviado al cliente, el nombre del envío, la dirección de envío, la ciudad destino, la provincia destino, el código postal, el país, los gastos de envío, los impuestos asociados, el tipo de pago, la fecha de pago y el estado del pedido. De cada cliente, es necesario almacenar un identificador, la compañía a la que pertenece, los apellidos, el nombre, la dirección de correo electrónico, el cargo, el teléfono del trabajo, el teléfono particular, el teléfono móvil, el número de fax, la dirección, ciudad, provincia, código postal, país y la dirección de página web. Cada pedido puede estar compuesto por varios productos y cada producto puede formar parte de diferentes pedidos. Es necesario almacenar la cantidad, el precio y el descuento aplicado a cada uno de los productos en cada uno de los pedidos. Además, de cada producto es necesario almacenar un identificador, un código de producto, el nombre del producto, una descripción, su coste, su precio de venta, el punto de pedido, el nivel objetivo, la cantidad por unidad, si está o no suspendido y la cantidad mínima de reposición.  Tema 2. Bases de datos 2.3 Modelos de Datos. El Modelo Entidad Relación y el Modelo Relacional Ejemplo Cada producto pertenece a una categoría de productos y en cada categoría podemos encontrar diferentes productos. De cada categoría solamente es necesario almacenar un identificador y su nombre. Los productos, son obtenidos por parte de NorthWindTrader a través de una serie de proveedores. Cada producto solo puede ser adquirido en un proveedor, mientras que cada proveedor, puede ofrecer varios productos. De cada proveedor es necesario almacenar un identificador, la compañía, los apellidos, el nombre, la dirección de correo electrónico, el cargo, el teléfono del trabajo, el teléfono particular, el teléfono móvil, el número de fax, la dirección, ciudad, provincia, código postal, país y página web. Los pedidos son preparados por empleados de la empresa. Cada pedido es preparado por un empleado y cada empleado puede preparar varios pedidos. De cada empleado es necesario almacenar un identificador, la compañía, los apellidos, el nombre, la dirección de correo electrónico, el cargo, el teléfono del trabajo, el teléfono particular, el teléfono móvil, el número de fax, la dirección, ciudad, provincia, código postal, país y página web. Finalmente, cada pedido es enviado al cliente por medio de una empresa de transportes. Cada empresa de transportes puede enviar varios pedidos. De cada empresa de transporte es necesario almacenar un identificador, la compañía, los apellidos, el nombre, la dirección de correo electrónico, el cargo, el teléfono del trabajo, el teléfono particular, el teléfono móvil, el número de fax, la dirección, ciudad, provincia, código postal, país y página web. Con esta información, diseñar el Modelo E-R y el Modelo Relacional que permita almacenar todos los datos necesarios para la gestión de la empresa NorthWindTrader.  Tema 2. Bases de datos 2.4 Sistemas de Gestión de Bases de Datos Sistemas gestores de bases de datos Entre las tablas que se generan mediante el Modelo Relacional y la base de datos física (el almacenamiento real de los datos) existe un medio o interfaz de software que recibe el nombre de Sistema de Gestión de Bases de Datos (SGBD) o Database management system (DBMS). Los SGBD permiten gestionar el uso de la base de datos por los usuarios, tanto gestionar los metadatos (crear tablas, eliminar tablas, definir atributos, tipos de los atributos, etc.), como gestionar los propios datos (añadir datos en una tabla, modificar datos, eliminar datos, recuperar datos, etc.). Hay muchos ejemplos de SGBD relacionales: Oracle, MySQL, SQLite, PostgreSQL, Microsoft SQL Server, etc.  Tema 2. Bases de datos 2.4 Sistemas de Gestión de Bases de Datos Sistemas gestores de bases de datos  Tema 2. Bases de datos 2.5 Lenguaje SQL Introducción Los SGBD utilizan el lenguaje Structred Query Language (SQL) para realizar todas sus actuaciones sobre la base de datos. Estas actuaciones pueden dividirse en dos grupos: Consultas de acción: permiten modificar la estructura o los datos de la base de datos. Consultas de selección: permiten obtener datos que cumplen unos determinados criterios o condiciones a partir de una o de una combinación de tablas.  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de acción Las principales consultas de acción tienen la siguiente sintaxis: CREATE TABLE: es utilizada para crear una nueva tabla en la base de datos. SQL Syntax Ejemplo CREATE TABLE nombre_tabla ( CREATE TABLE producto ( campo1 tipo restricciones, id int AUTO_INCREMENT NOT NULL UNIQUE PRIMARY KEY, campo2 tipo restricciones, código varchar(255), campo3 tipo restricciones, nombre varchar(255), … descripción varchar(65535), ); coste int, pvp int, puntopedido int, nivelobjetivo int, cantidadxunidad int, suspendido boolean, cantidadreposición int, idproveedor int FOREING KEY REFERENCES proveedor(id), idcategoría int FOREING KEY REFERENCES categoría(id) );  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de acción DROP TABLE: es utilizada para eliminar una tabla de la base de datos. SQL Syntax Ejemplo DROP TABLE nombre_tabla; DROP TABLE producto; ALTER TABLE: es utilizada para añadir, eliminar o modificar un campo en una tabla de la base de datos. SQL Syntax Ejemplo ALTER TABLE nombre_tabla ALTER TABLE producto ADD campo tipo restricciones; ADD reciclable boolean; ALTER TABLE nombre_tabla ALTER TABLE producto MODIFY COLUMN campo tipo restricciones; MODIFY COLUMN reciclable int; ALTER TABLE nombre_tabla ALTER TABLE producto RENAME COLUMN campo_viejo TO RENAME COLUMN reciclable TO sostenible; campo_nuevo; ALTER TABLE nombre_tabla ALTER TABLE producto DROP COLUMN campo; DROP COLUMN sostenible;  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de acción INSERT INTO: es utilizada para añadir datos en una tabla ya existente en la base de datos. SQL Syntax Ejemplo INSERT INTO nombre_tabla ( INSERT INTO producto ( campo1, campo2, campo3, …) id, código, nombre, descripción, coste, pvp, VALUES (dato1, dato2, dato3,…); puntopedido, nivelobjetivo, cantidadxunidad, suspendido, cantidadreposición, idproveedor, idcategoría) VALUES (1, 23, 'pan', 'descripción de pan', 25, 50, 20, 10, 5, 0, 40, 5, 2 ); INSERT INTO tabla_destino INSERT INTO producto2 SELECT * FROM tabla_origen SELECT * FROM producto WHERE condición; WHERE id = 1;  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de acción UPDATE: es utilizada para modificar datos de una tabla ya existente en la base de datos. SQL Syntax Ejemplo UPDATE nombre_tabla UPDATE producto2 SET campo1 = dato1, campo2 = dato2, … SET coste = 30 WHERE condición; WHERE id = 1; DELETE: es utilizada para eliminar datos de una tabla ya existente en la base de datos. SQL Syntax Ejemplo DELETE FROM nombre_tabla DELETE FROM producto2 WHERE condición; WHERE id = 1;  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de selección La principal consulta de selección tiene la siguiente sintaxis: SELECT: es utilizada para seleccionar datos. SQL Syntax Ejemplo SELECT campo1, campo2, … SELECT id, coste, pvp FROM nombre_tabla; FROM producto; A partir de SELECT, se pueden añadir diferentes cláusulas para refinar la selección de los datos. DISTINCT: es utilizada para seleccionar datos con valores diferentes. SQL Syntax Ejemplo SELECT DISTINCT SELECT DISTINCT campo1, campo2, … cantidadreposición FROM nombre_tabla; FROM producto;  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de selección ORDER BY: es utilizada para seleccionar datos y mostrarlos de manera ordenada, tanto ascendente como descendentemente. SQL Syntax Ejemplo SELECT campo1, campo2, … SELECT id, nombre, pvp, coste FROM nombre_tabla FROM producto ORDER BY ORDER BY coste DESC; campo1, campo 2, … ASC | DESC; WHERE: es utilizada para seleccionar datos que cumplan una determinada condición. SQL Syntax Ejemplo SELECT campo1, campo2, … SELECT * FROM nombre_tabla FROM producto WHERE condición; WHERE suspendido = 0; La selección puede combinarse con SELECT SUM(puntopedido) diferentes funciones: MIN, MAX, FROM producto COUNT; AVG, SUM, etc. WHERE puntopedido > 10;  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de selección SQL Syntax Ejemplo La condición puede combinarse con SELECT * los operadores AND, OR, NOT, o FROM producto bien con otros operadores. Por WHERE suspendido = 0 AND coste >= 30; ejemplo, concatenar texto ||. La condición puede combinarse con SELECT * el operador LIKE para buscar un FROM producto patrón determinado. Los patrones WHERE descripción LIKE '%de%'; utilizan dos comodines: % que representa 0 o más caracteres y _ que representa 1 único carácter. La condición puede combinarse con SELECT * el operador IN. El operador IN FROM producto permite especificar una lista de WHERE coste IN (20, 30); múltiples valores. La condición puede combinarse con SELECT * el operador BETWEEN. El operador FROM producto BETWEEN permite obtener datos WHERE coste BETWEEN 20 AND 30; entres dos valores determinados.  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de selección GROUP BY: es utilizada para seleccionar datos agrupando por un campo determinado. SQL Syntax Ejemplo SELECT campo1, campo2, … SELECT nombre, AVG(coste) FROM nombre_tabla FROM producto GROUP BY GROUP BY nombre; campo1, campo 2, … HAVING condición; AS: es utilizada para asignar un nombre temporal a un campo o a una tabla. SQL Syntax Ejemplo SELECT campo1 AS alias SELECT nombre, AVG(coste) AS 'Coste medio' FROM nombre_tabla; FROM producto GROUP BY nombre;  Tema 2. Bases de datos 2.5 Lenguaje SQL Consultas de selección INNER JOIN: es utilizada para seleccionar datos combinando dos o más tablas en base a un campo que relaciona dichas tablas. SQL Syntax Ejemplo SELECT tabla1.campo1, tabla2.campo2, … SELECT producto.nombre, categoría.nombre FROM tabla1 FROM producto INNER JOIN tabla2 INNER JOIN categoría ON tabla1.campoN = tabla2.campoN; ON producto.idcategoría = categoría.id; Para profundizar y practicar con el lenguaje SQL puede consultarse el siguiente Tutorial  Tema 2. Bases de datos 2.5 Lenguaje SQL Ejercicios Resolver los siguientes ejercicios utilizando la siguiente base de datos: 1.- Mostrar todos clientes de la ciudad de Londres. 2-. Mostrar todos los clientes que vivan en Londres o Madrid. 3.- Mostrar todos los clientes que vivan en Madrid o en Estados Unidos. 4.- Mostrar todos los productos con un precio entre 10€ y 15€. Ambos incluidos. 5.- Mostrar el nombre de todos los productos cuyo precio esté entre 10€ y 15€ y pertenezcan a la categoría de bebidas. 6.- Mostrar todos los pedidos que se han realizado entre el 1 de agosto de 1996 y el 10 de agosto de 1996. 7.- Mostrar el nombre, la ciudad y el país de los clientes de España, Alemania y Francia ordenados alfabéticamente primero por el nombre del país y posteriormente por el nombre de la ciudad. 8.- Mostrar todos los proveedores sin número de teléfono. 9.- Mostrar todos los proveedores con número de teléfono. 10.- Mostrar todos los productos cuyo nombre empieza por C y termina por e.  Tema 2. Bases de datos 2.5 Lenguaje SQL Ejercicios 11.- Mostrar todos los productos del proveedor “New Orleans Cajun Delights”. 12.- Mostrar todos los pedidos que contengan productos de los proveedores “Leka Trading” o “Mayumi’s”. 13.- Mostrar todos productos que vengan en cajas y sean de la categoría repostería. 14.- Mostrar los nombres de los clientes que han comprado algún producto cuyo nombre contenga la palabra “Queso” con un precio superior a 25€ en 1996. 15.- Incrementar el precio de los productos cuyo precio actual sea múltiplo de 10 en un 10%. 16.- Añadir un nuevo campo en la tabla productos que contenga el precio del producto incluyendo el IVA (21%). 17.- Mostrar el número de pedidos que se envían a cada país. 18.- Calcular el precio promedio de los productos de cada categoría. 19.- Mostrar las categorías de productos, el número de productos que tienen y el precio total de los productos de cada categoría. Considerar solamente los productos con un precio mayor a 20€.  Tema 2. Bases de datos 2.5 Lenguaje SQL Ejercicios 20.- Mostrar por cada transportista el número de envíos que ha realizado a Francia. 21.- Mostrar el nombre de todos los clientes que han comprado algún producto proporcionado por proveedores de su misma ciudad. Mostrar también el nombre del proveedor y de la ciudad en concreto. 22.- Crear una tabla llamada “Productos” con los mismos campos que la tabla “Products”. 23.- Copiar en la nueva tabla “Productos” todos los productos de la tabla “Products” cuyo nombre esté compuesto por más de una palabra. 24.- Añadir en la nueva tabla “Productos” un producto con identificador “78”, nombre “Mi producto”, proporcionado por el proveedor “1”, de la categoría “1”, suministrado en “12 botellas por caja” y un precio de “33”€. 25.- Eliminar de la nueva tabla “Productos” todos los productos que tengan como segunda letra de su nombre una “o”. 26.- Eliminar la tabla “Productos”. 27.- Crear un nuevo campo en la tabla detalle de pedido y calcular posteriormente en este nuevo campo el precio total de cada pedido considerando el precio del producto y la cantidad pedida.  Tema 2. Bases de datos 2.5 Lenguaje SQL Ejercicios 28.- Mostrar un listado con el nombre completo en un solo campo, la fecha de nacimiento y la edad de los empleados. 29. Mostrar las categorías de productos, el número de productos que tienen y el precio total de los productos de cada categoría. Mostrar solamente aquellas categorías con un precio total mayor a 300 €. 30. Mostrar el apellido de cada empleado, el número de pedidos que ha procesado y el volumen total que ha facturado redondeado a 2 decimales y ordenado de manera que en primer lugar aparezca el empleado con mayor facturación.  TEMA 2 BASES DE DATOS