BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
F. I. M.
L.S.I.I.S
LUIS MENGUAL GALÁN
BASES DE DATOS Elementos Básicos de SQL
http://www.personal.fi.upm.es/~lmengual/base_datos/bd.html
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
OBJETIVOS
Describir los elementos básicos del lenguaje declarativo SQL, el más utilizado en los sistema gestores de Bases de Datos
Estudiar las restricciones en la creación de las Bases de datos como elemento básico para garantizar su integridad
Estudiar el proceso de inserción y actualización de tablas Comprender la sintaxis de acceso a la información de las
bases de datos Análisis de ejemplos prácticos de contrucción de esquemas
y acceso a la información
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
1. Introducción al lenguaje SQL 2. Lenguaje de Definición de Datos (Data Definition Language, DDL)
Tipos de Datos Dominios Creación de Tablas Restricciones En SQL Restricciones y Valores En Los Atributos Claves Integridad Referencial Y Claves Extranjeras Borrado de Tablas Modificación de la Base de Datos
ÍNDICE
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ÍNDICE
3. Lenguaje de Manipulación de Datos (Data Manipulation Language, DML) Modificación de las Bases de Datos
Inserción Borrado Actualizaciones
Consultas SELECT Selección básica
Eliminación Duplicados (DISTINCT) Selección Condicional Simple (WHERE) Selección Condicional Compuesta (WHERE AND/OR) Selección Tuplas en Función de Valores (IN) Selección Función de Rango de Valores (BETWEEN) Selección Búsqueda de Patrones (LIKE) Selección Ordenación de Resultados (ORDER BY) Selección Funciones Aritméticas (AVG, COUNT, MAX, MIN, SUM) Selección Grupos tuplas (GROUP BY) Selección y limitar Funciones Aritméticas (HAVING) Selección con Alias Selección 2 tablas (join natural) Selección Concatenación Resultados (CONCAT) Selección Subcadenas Resultados (SUBSTR)
SQL Avanzado CONSULTAS ANIDADAS Operador EXISTS Subconsultas en la cláusula FROM Operador UNION
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
BIBLIOGRAFÍA
“FUNDAMENTOS DE SISTEMAS DE BASES DE DATOS”. R. Elmasri, S.B. Navathe, Pearson Addison Wesley. 2007
“FUNDAMENTOS DE BASES DE DATOS” Quinta Edicion. A. Silberschatz, H. Korth, S. Sudarshan. Prentice Hall. 2006
“THE COMPLETE REFERENCE MYSQL”. Vikram Vaswani. Edited by McGraw Hill 2004
HIGH PERFORMANCE MySQL. B. Schuartz, P. Zaitsev, V. Tkachenko, J.D. Zaodny, A. Lentz, D.J. Balling. O´Reilly.2008
“MySQL ADMINISTRATOR´S”. S.K.. Cabral, K. Murphy. Wiley Publishing. 2009
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
El Modelo Relacional es el principal modelo lógico de datos en la actualidad
Se encuentra en un nivel de abstracción inferior al modelo de E-R
Los diseños de Bases de Datos a menudo se realizan en el modelo E-R, y después se traducen al modelo relacional
En el modelo relacional se utiliza un grupo de tablas para representar los datos y las relaciones entre ellos
MODELO RELACIONAL
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Una Base de Datos Relacional consiste en un conjunto de tablas, a cada una de las cuales se le asigna un nombre exclusivo
Cada Tabla viene a corresponder a una entidad o una relación del modelo de E-R
Cada Columna de la Tabla se denomina Atributo. Corresponde con los atributos de las entidades o relaciones del modelo de E-R
Para cada Atributo hay un conjunto de valores permitidos, llamado Dominio de ese Atributo
Cada Fila de la tabla se denomina Registro. En una Fila o Registro, cada Columna (atributo) toma un valor
MODELO RELACIONAL Tablas, Registros, Atributos
Tabla: Alumnos
Dni Nombre Fecha_Nac
72.124.563 Silvia Rodríguez 12/09/1990
12.345.235 Carlos Martinez 13/03/1991
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
El concepto de atributo identificador (clave primaria) del modelo E-R se mantiene en el modelo relacional
– Será un grupo de atributos para los que cualquier combinación de valores no se pueden repetir en dos registros diferentes. De esta manera, los valores de los atributos de la clave primaria identifican de forma única a cada registro.
Cuando un atributo de una Tabla B hace referencia a atributo de otra Tabla A se dice que es una clave ajena de la tabla B
La clave ajena en la tabla B sólo puede tomar los valores que estén en algún registro de la clave principal de la tabla A.
MODELO RELACIONAL Clave Primaria, Ajena
Tabla: Matriculaciones
Dni Cod_Asignatura Nota
72.124.563 023456 (Física) 8
12.345.235 023121 (Química) 5
12.345.235 023212(Dibujo) 5
7.375.911 No vale
Tabla: Alumnos
Dni Nombre Fecha_Nac
72.124.563 Silvia Rodríguez 12/09/1990
12.345.235 Carlos Martinez 13/03/1991
Clave Primaria Clave Ajena
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La descripción de cada tabla se denomina Esquema. Se representa de la siguiente forma: nombre de la tabla, luego los atributos clave primaria, si es que los hay, y por último el resto de atributos).
El conjunto de los esquemas de una base de datos se denomina Diagrama de Esquema.
Al cuadro de cada esquema, se añade una flecha que une las claves ajenas que pudiera haber en ese esquema con la correspondiente clave primaria de otro esquema a la que hace referencia
MODELO RELACIONAL Diagramas de Esquema
Alumnos
Dni
Fecha_Nac
Nombre
Matriculaciones
Dni
Cod_Asignatura
Nota
Clave Primaria (Primary Key)
Clave Ajena (Foreing Key)
Tabla Padre Tabla Hija
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Para cada entidad se crea una única tabla – Se le asigna el nombre de la entidad y cuyos atributos son los atributos
de la entidad. La clave primaria de la tabla será el identificador principal de la entidad.
La transformación de las relaciones depende del tipo de correspondencia
MODELO RELACIONAL Paso de Diagramas E-R a Modelo Relacional (I)
Todo modelo E-R puede representarse por medio de tablas relacionales. Para ello las reglas son las siguientes:
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
MODELO RELACIONAL Paso de Diagramas E-R a Modelo Relacional (II)
Transformación de relaciones 1:N (I)
Empresa Trabajador Trabaja-en
Domicilio
Nombre
Cif
(0,N) (0,1)
Direccion
Nombre
Dni
Empresa( Cif, Nombre, Domicilio)
Trabajador( Dni, Nombre, Direccion, Cif)
Empresa
Cif
Domicilio
Nombre
Trabajador
Nombre
Direccion
Dni
Cif
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
MODELO RELACIONAL Paso de Diagramas E-R a Modelo Relacional (II)
Transformación de relaciones n:m (II)
Alumnos Asignaturas Matricula
Nombre
Fecha_nac
Dni
Nota
(0,N) (0,M)
Curso
Nombre
Codigo
Alumnos
Dni
Fecha_Nac
Nombre
Matricula
Dni
Codigo
Nota
Asignaturas
Nombre
Curso
Codigo
Alumnos( Dni, Nombre, Fecha_nac)
Matricula( Dni, Codigo, Nota)
Asignaturas( Codigo, Nombre, Curso)
Clave primaria
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Restricciones Bases de Datos
Dominios – Un dominio describe un conjunto de posibles valores para cierto atributo.
Como un dominio restringe los valores del atributo, puede ser considerado como una restricción.
Clave única – Cada tabla puede tener uno o más campos cuyos valores identifican de
forma única cada registro de dicha tabla, – No pueden existir dos o más registros diferentes cuyos valores en dichos
campos sean idénticos. – Pueden existir varias claves únicas en una determinada tabla, y a cada
una de éstas suele llamársele candidata a clave primaria.
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
INTEGRIDAD Bases de Datos
Clave primaria – Una clave primaria es una clave única elegida entre todas las candidatas
Sólo puede existir una clave primaria por tabla y ningún campo de dicha clave puede contener valores NULL.
Clave Ajena (clave foránea) – Una clave ajena es una referencia a una clave en otra tabla. Las claves
ajenas no necesitan ser claves únicas en la tabla donde están y sí a donde están referenciadas.
Clave índice – Las claves índice surgen con la necesidad de tener un acceso más rápido
a los datos. Los índices pueden ser creados con cualquier combinación de campos de una tabla.
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Sistema de Base de Datos Relacional
DNI Apellidos Nombre Puesto Edad
123456789 Pérez García Francisco Director 45
987654321 Martín Gómez Rául Ingeniero 30
… … … … …
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ATAQUES A UNA BASE DE DATOS Acceso a la información (I)
cdcol.cds
SGBD +
BD
CODIGO JAVA
Internet
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
public static void main (String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { // Paso 1: Cargar el JDBC driver. String driverName = "com.mysql.jdbc.Driver"; Class.forName(driverName); // Paso 2: Establecer la conexión con una BD String serverName = "localhost:3306"; String mydatabase = ""; String url = "jdbc:mysql://" + serverName + "/" + mydatabase; String username = "root"; String password = ""; connection = DriverManager.getConnection(url, username, password); System.out.println("Conexion establecida con BD \n"); // Paso 3: Ejecutar una consulta statement = connection.createStatement(); resultSet= statement.executeQuery("select * from cdcol.cds where interpret = 'Groove Armada'"); // Paso 4: Obtener la estructura de una Tabla System.out.println("RESULTADO: \n ") ; String Titulo = null; while (resultSet.next()) { Titulo = resultSet.getString("titel"); String Interprete = resultSet.getString("interpret"); int Año = resultSet.getInt("jahr"); Long id = resultSet.getLong("id"); System.out.println(Titulo + ", " + Interprete + ", " + Año + ", "+id); }
ATAQUES A UNA BASE DE DATOS Acceso a la información (II)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
// Paso 5: Liberar Recursos y cerrar conexión statement.close(); resultSet.close(); connection.close(); System.out.println("Liberados recursos y cerrada conexion"); } catch(SQLException se) { //se.printStackTrace(); System.out.println("Mensaje errorr : "+se.getMessage()); System.out.println("Codigo error : "+se.getErrorCode()); System.out.println("Estado SQL : "+se.getSQLState()); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch(Exception e) { System.out.println("Se produjo un error inesperado: "+e.getMessage()); } finally { try { System.out.println("Ejecuto finally \n"); if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (connection != null) connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
ATAQUES A UNA BASE DE DATOS Acceso a la información (II)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ATAQUES A UNA BASE DE DATOS Acceso a la información (III)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ATAQUES A UNA BASE DE DATOS Acceso a la información (IV)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL Structured Query Language
Lenguaje Declarativo – Especifica qué es lo que se quiere y no cómo
conseguirlo, por lo que una sentencia no establece explícitamente un orden de ejecución
1980
2001
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
1982
ANSI encarga el desarrollo de un lenguaje relacional
1987
SQL/86 estándar ISO1989
SQL/89 -> Integridad referencial1992
SQL/92 -> Versión extendida de SQL
2000
SQL3 -> Capacidades multimedia
1981
SQL estándar de facto
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL Structured Query Language
Un Lenguaje de Definición de Datos (Data Definition Language, DDL)
– Tareas de definición de las estructuras que almacenarán los datos (CREATE, DROP , ALTER, TRUNCATE)
Lenguaje de Manipulación de Datos (Data Manipulation Language, DML)
– Tareas de consulta o manipulación de los datos (SELECT, INSERT, DELETE Y UPDATE)
Lenguaje de Control de Datos (Data Control Language, DCL)
– Controlar el acceso a los datos contenidos en la Base de Datos (GRANT, REVOKE)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Ventajas de SQL
Independencia de los fabricantes Portabilidad a cualquier tipo de plataforma SQL está estandarizado Basado en el modelo relacional Lenguaje de alto nivel Consultas interactivas ad-hoc Utilización en Lenguaje de programación Múltiples vistas de los datos Lenguaje de base de datos Definición dinámica de datos Arquitectura cliente/servidor
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
INT (INTEGER) para almacenar números enteros (4B) – En un rango de -2147463846 a 2147483647. Si configuramos este dato como unsigned,
el rango es 0 a 4294967295 TINYINT es un número entero con rango de valores válidos desde -128 a 127. Si se
configura como unsigned (sin signo), el rango de valores es de 0 a 255 (1B) SMALLINT, para números enteros, con rango desde -32768 a 32767. Si se configura como
unsigned, 0 a 65535. (2B) MEDIUMINT para números enteros; el rango de valores va desde -8.388608 a 8388607. Si se
configuracomo unsigned, 0 a 16777215 (3B) BIGINT número entero con rango de valores desde -9223372036854775808 a
9223372036854775807. Unsigned, desde 0 a 18446744073709551615. (8B)
FLOAT , DOUBLE representa números decimales (4/8B),
– Float: 3.402823466E+38 a -1.175494351E-38, 0, y de 1.175494351E-38 a 3.402823466E+38
– Double: 1.7976931348623157E+308 a -2.2250738585072014E-308, 0, y de 2.2250738585072014E-308
BIT o BOOL(BOOLEAN), para un número entero que puede ser 0 ó 1
Manipulación de Tablas Tipos de Datos
Tipos Numéricos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
CHAR (n) – Este tipo se utiliza para almacenar cadenas de longitud
fija. Su longitud abarca desde 1 a 255 caracteres.
VARCHAR (n) – Al igual que el anterior se utiliza para almacenar cadenas,
en el mismo rango de 1-255 caracteres, pero en este caso, de longitud variable
TEXT – Se usa para cadenas con un rango de 255 - 65535
caracteres
Manipulación de Tablas Tipos de Datos
Tipos de caracteres
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
BINARY(M) – El tipo BINARY es similar al tipo CHAR, pero almacena
cadenas de datos binarios en lugar de cadenas de caracteres no binarias.
VARBINARY(M) – El tipo VARBINARY es similar al tipo VARCHAR, pero
almacena cadenas de bytes en lugar de cadenas de caracteres no binarias.
BLOB – Se usa para cadenas binarias con un rango de 255 -
65535 caracteres MEDIUMLOB 16Mbytes LONGBLOB 4Gbytes
Tipos Binarios
Manipulación de Tablas Tipos de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
DATE para almacenar fechas. – El formato por defecto es YYYY-MM-DD desde 0000 00 00 a 9999 12 31
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 – Combinación de fecha y hora. El rango va desde el 1 de enero
de 1970 al año 2037. El formato de almacenamiento coincide en algunos gestores con DATETIME
Tipos relativos al tiempo
Manipulación de Tablas Tipos de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
TIME almacena una hora. – El rango de horas va desde -838 horas, 59 minutos y 59 segundos a 838,
59minutos 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
Tipos relativos al tiempo
Manipulación de Tablas Tipos de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL incluye un conjunto de funciones que manejan el tiempo actual y que se pueden aplicar a consultas (current_date(), date_sub(),hour(), week()
SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
Manipulación de Tablas Tipos de Datos - Funciones
Consulta que selecciona las tuplas que tienen un valor en “date-col” que está dentro de los 30 últimos días
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Los dominios se pueden utilizar como tipos de datos Permite cambiar el tipo simultáneamente a varios
atributos
Manipulación de Tablas Tipos de Datos. Creación de Dominios
CREATE DOMAIN ciudades AS
CHAR(15) DEFAULT ‘???’
CREATE DOMAIN dominio [AS] tipo-datos
[DEFAULT valor_defecto] [CHECK condicion]
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
CREATE TABLE "nombre_tabla”("columna 1" "tipo_de_datos_para_columna_1“,"columna 2" "tipo_de_datos_para_columna_2“,... )
Manipulación de Tablas Crear tablas
CREATE TABLE cliente (Nombre char(50), Apellido char(50), Dirección char(50), Ciudad char(50), País char(25), Fecha_nac date);
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Se puede limitar el tipo de información que una
tabla /columna puede mantener Tipo de Restricciones
– NOT NULL – UNIQUE – CHECK – Clave Primaria – Clave Ajena
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
En forma predeterminada, una columna puede ser
NULL. Si no desea permitir un valor NULL en una columna, se puede colocar una restricción en esta columna especificando que NULL no es ahora un valor permitido.
Restricción NOT NULL
CREATE TABLE Cliente (Dni integer NOT NULL, Apellido varchar (30) NOT NULL, Nombre varchar(30)); Las columnas “Dni” y “Apellido” no incluyen NULL, mientras que “Nombre” puede incluir NULL.
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La restricción UNIQUE asegura que todos los valores
en una columna sean distintos.
Restricción UNIQUE
CREATE TABLE Cliente (Dni integer Unique, Apellido varchar (30), Nombre varchar(30)); La columna “Dni” no puede incluir valores duplicados, mientras dicha restricción no se aplica para columnas “Apellido” y “Nombre”.
Una columna que se especifica como clave primaria también puede ser única
Al mismo tiempo, una columna que es única puede o no ser clave primaria.
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La restricción CHECK asegura que todos los valores
en una columna cumplan ciertas condiciones.
Restricción CHECK
CREATE TABLE Cliente (Dni integer CHECK (Dni> 0), Apellido varchar (30), Nombre varchar(30)); La columna “SID” sólo debe incluir enteros mayores a 0.
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Las claves primarias pueden especificarse cuando se
crea la tabla (utilizando CREATE TABLE) o cambiándola estructura existente de la tabla (utilizando ALTER TABLE)
Claves Primarias
CREATE TABLE Cliente (Dni integer, Nombre varchar(30), Apellido varchar(30), PRIMARY KEY (Dni));
CREATE TABLE Cliente (Dni integer PRIMARY KEY, Nombre varchar(30), Apellido varchar(30));
ALTER TABLE Cliente ADD PRIMARY KEY (Dni);
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Una clave ajena es un campo (o campos) que señala la clave
primaria de otra tabla. El propósito de la clave externa es asegurar la integridad referencial de los datos. En otras palabras, sólo se permiten los valores que se esperan que aparezcan en la base de datos.
Claves Ajenas (I)
Cliente
Dni
Apellido
Nombre
Pedidos
fecha
Cantidad
npedido
Cliente_dni
En el ejemplo anterior, la columna Cliente_Dni en la tabla Pedidos es una clave ajena señalando la columna Dni en la tabla Cliente
Clave Primaria (Primary Key)
Clave Ajena (Foreing Key)
Clave Primaria (Primary Key)
Manipulación de Tablas Restricciones en las tablas
Tabla Padre Tabla Hija
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Claves Ajenas (II)
CREATE TABLE Pedidos (npedido integer, Fecha date, Cantidad double, Cliente_Dni integer, Primary Key (npedido), Foreign Key (Cliente_Dni) references Ciente(Dni));
CREATE TABLE Pedidos (npedido integer primary key, Fecha date, Cantidad double, Cliente_Dni integer references Ciente(Dni));
Las claves ajenas pueden especificarse cuando se crea la tabla
(utilizando CREATE TABLE)
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Las claves ajenas tambien pueden especificarse
cambiando la estructura existente de la tabla (utilizando ALTER TABLE)
Claves Ajenas (III)
ALTER TABLE Pedidos ADD FOREIGN KEY (Cliente_Dni) REFERENCES Cliente(Dni); ALTER TABLE Pedidos ADD CONSTRAINT fk_orders1 FOREIGN KEY (Cliente_Dni) REFERENCES Cliente(Dni);
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
¿Qué ocurre si se borra registro de una tabla Padre (con un atributo referenciado en una tabla Hija)?
Opciones – No se puede borrar una tupla en la tabla padre si no se
borra la tupla asociada en la tabla Hija (ON DELETE RESTRICT)
– Al borrar una tupla en la tabla padre se borra la tupla asociada en la tabla Hija (ON DELETE CASCADE)
Manipulación de Tablas Restricciones en las tablas
Borrado en tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
¿Qué ocurre si actualiza un registro de una tabla Padre (con un atributo referenciado en una tabla Hija)?
Opciones – No se puede actualizar una tupla en la tabla padre si no
se actualiza la tupla asociada en la tabla Hija (ON UPDATE RESTRICT)
– Al actualizar una tupla en la tabla padre se actualiza la tupla asociada en la tabla Hija (ON UPDATE CASCADE)
Manipulación de Tablas Restricciones en las tablas
Actualización en tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
create table Pedidos( npedido INTEGER, fecha DATE, Cantidad DOUBLE, cliente_Dni VARCHAR(10), PRIMARY KEY (npedido), FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB;
La opción más recomendable en la mayoría de los casos sería “ON DELETE RESTRICT ON UPDATE CASCADE”
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ON DELETE CASCADE. – Con esta opción escrita a continuación del atributo al que se hace
referencia, lo que se consigue es que si se borra en la tabla a la que hace referencia el atributo una de las tuplas, las tuplas que contengan ese valor en la tabla donde se ha definido la clave ajena se eliminaran automáticamente
ON DELETE SET NULL. – Con esta opción lo que se consigue es que al eliminar uno de los valores
en la tabla referenciada, los valores del atributo definido como clave ajena, se ponen con valor nulo (NULL)
ON UPDATE CASCADE. – Si se modifica alguno de los valores de la tabla referenciada,
automáticamente se actualizan y toman el mismo valor.
ON UPDATE SET NULL. – Si se modifica alguno de los valores en la tabla referenciada, las tuplas
que contengan ese valor en la tablas en la que se ha definido la clave ajena toman valor nulo (NULL) en ese atributo.
Manipulación de Tablas Restricciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Una vez que se crea la tabla en la base de datos, hay
muchas ocasiones donde uno puede desear cambiar la estructura de la tabla. Los casos típicos incluyen los siguientes:
– Agregar una columna – Eliminar una columna – Cambiar el nombre de una columna – Cambiar el tipo de datos para una columna – Especificación de Clave Primaria – Especificación de Clave Ajena
Manipulación de Tablas Modificación de tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La sintaxis SQL para ALTER TABLE es
ALTER TABLE "nombre_tabla" [modificar especificación]
[modificar especificación] depende del tipo de modificación que deseamos realizar. Para los usos mencionados anteriormente, las instrucciones [modificar especificación] son: Agregar una columna: ADD “columna 1” “tipos de datos para
columna 1” Eliminar una columna: DROP “columna 1” Cambiar el nombre de una columna: CHANGE “nombre antiguo
de la columna” “nuevo nombre de la columna” “tipos de datos para la nueva columna“
Cambiar el tipo de datos para una columna: MODIFY “columna 1” “nuevo tipo de datos”
Manipulación de Tablas Modificación de tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ALTER table cliente add Direccion varchar(20) (Agregamos una columna denominada “dirección” a esta tabla)
Cliente
Dni
Nombre
integer
varchar (30)
Apellido varchar (30)
Dirección varchar (20)
Cliente
Dni Nombre
integer
varchar (30)
Apellido varchar (30)
Manipulación de Tablas Modificación de tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
ALTER table Cliente modify NomApp varchar(60) (Modificamos el tipo de datos para “NomApp ” a 60 caracteres)
ALTER table cliente change Nombre NomApp varchar(30) (Ahora renombramos la columna “Nombre" a “NomApp)
Dirección varchar (20)
Cliente
Dni NomApp
integer
varchar (30)
Apellido varchar (30)
Dirección varchar (20)
Cliente
Dni NomApp
integer
varchar (60)
Apellido varchar (30)
Manipulación de Tablas Modificación de tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Cliente Dni
NomApp
integer varchar (60)
Direccion varchar (20)
ALTER table Cliente drop Apellido (Finalmente, eliminamos la columna “apellidos”)
Dirección varchar (20)
Cliente
Dni NomApp
integer
varchar (60)
Apellido varchar (30)
Manipulación de Tablas Modificaciones en las tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL permite el borrado de tablas mediante el comando:
DROP TABLE "nombre_tabla"
Si una tabla tiene restricciones asociadas no se elimina hasta que no se eliminen las restricciones
Manipulación de Tablas Borrado de tablas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
En SQL, hay básicamente dos formas para insertar datos
en una tabla: Una es insertar una fila por vez, y la otra es insertar filas múltiples por vez
INSERT INTO "nombre_tabla" ("columna1", "columna2", ...) VALUES ("valor1", "valor2", ...)
INSERT INTO "tabla1" ("columna1", "columna2", ...) SELECT "columna3", "columna4", ... FROM "tabla2"
Manipulación de Tablas Inserción de Datos
INSERT INTO VALUES (V11,V12 ,…V1n), (V21,V22 ,…V2n), ….., (Vn1,Vn2 ,…Vnn)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
INSERT INTO Cliente (Dni, NomApp, Dirección) VALUES (72223, ‘juan lopez garcia’, ‘arturo soria 25’);
INSERT INTO Cliente (Dni, NomApp, Dirección) SELECT Dni, NomApp, Dirección FROM personal
Cliente Dni
NomApp
integer varchar (60)
Direccion varchar (20)
INSERT INTO Cliente (Dni, NomApp) VALUES (43102, ‘carlos martinez salvador’); (Si un atributo no se indica toma el valor null)
Manipulación de Tablas Inserción de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Una vez que hay datos en la tabla, podríamos tener la
necesidad de modificar los mismos. Para hacerlo, utilizamos el comando UPDATE. La sintaxis para esto es,
UPDATE "nombre_tabla" SET "columna_1" =[nuevo valor], "columna_2" = [nuevo valor], … "columna_n" =[nuevo valor], WHERE {condición}
Manipulación de Tablas Actualización de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
UPDATE Cliente SET direccion=‘Arturo soria12’ WHERE Nombre=‘Carlos Soto’ AND Dni= 7124563
Tabla: Cliente Dni Nombre Direccion
7124563 Carlos Soto San Lorenzo 9
9345235 Ricardo Garcia Astorga 22
8345233 Juan Lopez Gran vía 2
Tabla: Cliente Dni Nombre Direccion
7124563 Carlos Soto Arturo soria 12
9345235 Ricardo Garcia Astorga 22
8345233 Juan Lopez Astorga 22
Manipulación de Tablas Actualización de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
A veces podemos desear deshacernos de los registros
de una tabla. Para ello, utilizamos el comando DELETE FROM. La sintaxis para esto es,
DELETE FROM "nombre_tabla" WHERE {condición}
Se borran las tuplas que cumplen cierta condición Si no hay condición se borran todas las tuplas de la tabla A diferencia de DROP TABLE no se borra la estructura de
la tabla
Manipulación de Tablas Borrado de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
DELETE FROM Cliente WHERE Nombre=‘Carlos Soto’
Tabla: Cliente Dni Nombre Direccion
7124563 Carlos Soto San Lorenzo 9
9345235 Ricardo Garcia Astorga 22
8345233 Juan Lopez Gran vía 2
Tabla: Cliente Dni Nombre Direccion
9345235 Ricardo Garcia Astorga 22
8345233 Juan Lopez Astorga 22
Manipulación de Tablas Borrado de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Nos podemos deshacer de todos los datos de
una tabla pero conservando la estructura de la Tabla
Borrado de Todos los Datos de una Tabla
TRUNCATE TABLE "nombre_tabla"
Manipulación de Tablas Borrado de Datos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SELECT lista_atributos FROM lista_tablas WHERE condiciones GROUP BY lista_atributos HAVING condiciones ORDER BY lista_atributos;
Consulta de Datos Consulta Básica
Comando SELECT
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La consulta básica en SQL permite consultar
los valores de los atributos de una tabla Se pueden producir duplicados
SELECT "nombre_columna" FROM "nombre_tabla"
Consulta de Datos Consulta Básica
Comando SELECT
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
2010/01/07 Sevilla
Madrid
2010/01/05
2010/01/08
Tienda
250
300
Barcelona 2010/01/08 700
SELECT Tienda FROM Información_ventas
Resultado: Madrid Sevilla Madrid Barcelona
Consulta de Datos Consulta Básica
Comando SELECT
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SELECT nos permite tomar toda la información de una
columna (o columnas) en una tabla. Esto, obviamente, significa necesariamente que habrá redundancias.
Para evitar los duplicados se utiliza la palabra DISTINCT
SELECT DISTINCT "nombre_columna" FROM "nombre_tabla”
Consulta de Datos Eliminación Duplicados
Comando SELECT DISTINCT
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Sevilla
Madrid
Tienda
250
300
Barcelona 700
SELECT DISTINCT Tienda FROM Información_ventas
Resultado: Madrid Sevilla Barcelona
Consulta de Datos Eliminación Duplicados
Comando SELECT DISTINCT
2010/01/07
2010/01/05
2010/01/08
2010/01/08
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Podríamos desear seleccionar condicionalmente los datos de una tabla. Para ello, utilizamos la cláusula clave WHERE. La sintaxis es la siguiente:
Consulta de Datos Selección Condicional Simple
SELECT "nombre_columna" FROM "nombre_tabla" WHERE "condición"
Comando SELECT … WHERE
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Operadores Uso < Menor que
> Mayor que
<> Distinto de
<= Menor o igual que
>= Mayor o igual que
= Igual que
AND Devuelve verdadero si las expresiones a izquierda y a derecha son ambas verdaderas
OR Devuelve verdadero si cualquiera de las expresiones a derecha o izquierda del OR, son verdaderas.
NOT Invierte la lógica de la expresión que está a su derecha. Si es verdadera, mediante NOT pasa a ser falsa.
AND Devuelve verdadero si las expresiones a izquierda y a derecha son ambas verdaderas
Consulta de Datos Selección Condicional Simple: Operadores
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Sevilla
Madrid
Tienda
250
300
Barcelona 700
SELECT Tienda FROM Información_ventas WHERE Ventas>1000
Resultado: Madrid
Consulta de Datos Selección Condicional Simple
Comando SELECT … WHERE
2010/01/07
2010/01/05
2010/01/08
2010/01/08
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La condición que afecta al comando SELECT puede ser una condición compuesta.
Las condiciones compuestas están formadas por múltiples condiciones simples conectadas por AND u OR.
No hay límites en el número de condiciones simples que pueden presentarse en una sola instrucción SQL.
Consulta de Datos Selección Condicional Compuesta
SELECT "nombre_columna" FROM "nombre_tabla" WHERE "condición simple" {[AND|OR] "condición simple"}+
Comando SELECT … WHERE ….AND/OR
{}+ significa que la expresión dentro de las llaves ocurrirá una o más veces. Note que AND u OR pueden utilizarse indistintamente. Además, podemos utilizar el símbolo paréntesis () para indicar el orden de la condición.
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Condicional Compuesta
Comando SELECT … WHERE ….AND/OR
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Sevilla
Cuenca
Tienda
250
300
Barcelona 700
SELECT Tienda FROM Información_Ventas WHERE Ventas > 1000 OR (Ventas < 500 AND Ventas> 275)
Resultado: Madrid Cuenca
2010/01/07
2010/01/05
2010/01/08
2010/01/08
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La palabra clave IN nos permite seleccionar las tuplas en función de valores conocidos de los atributos
Consulta de Datos Selección Tuplas en Función de Valores
SELECT "nombre_columna" FROM "nombre_tabla" WHERE "nombre_columna" IN (''valor1', ''valor2', ...)
Comando SELECT … WHERE ….IN
El número de valores en los paréntesis pueden ser uno o más, con cada valor separado por comas. Los valores pueden ser números o caracteres.
Si hay sólo un valor dentro del paréntesis, este comando es equivalente a WHERE "nombre_columna" = 'valor1
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Tuplas en Función de Valores
Comando SELECT … WHERE ….IN
SELECT * FROM Información_ventas WHERE Tienda IN ('Madrid', 'Sevilla')
Resultado:
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
07/01/2010 Sevilla
Cuenca
Tienda
250
300
Barcelona 700
Madrid 1500 05/01/2010
Sevilla 250 2010/01/07
2010/01/05
2010/01/08
2010/01/08
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Mientras que la palabra clave IN ayuda a limitar el criterio de selección para uno o más valores discretos, la palabra clave BETWEEN permite la selección de un rango. La sintaxis para BETWEEN es la siguiente:
Consulta de Datos Selección Función de Rango de Valores
SELECT "nombre_columna" FROM "nombre_tabla" WHERE "nombre_columna" BETWEEN 'valor1' AND 'valor2'
Comando SELECT … WHERE.…. BETWEEN `valor1’ AND `valor2’
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Función de Rango de Valores
SELECT * FROM Información_ventas WHERE Fecha BETWEEN '2010/01/06' AND '2010/01/10'
Resultado:
Cuenca 08/01/2010 300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Comando SELECT … WHERE.…. BETWEEN `valor1’ AND `valor2’ Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Cuenca
Tienda
300
Barcelona 700
Sevilla 250 2010/01/07
2010/01/05
2010/01/08
2010/01/08
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
LIKE es otra palabra clave que se utiliza en la cláusula WHERE.
LIKE le permite hacer una búsqueda basada en un patrón en vez de especificar exactamente lo que se desea (como en IN) o determinar un rango (como en BETWEEN).
Consulta de Datos Selección Búsqueda de Patrones
SELECT "nombre_columna" FROM "nombre_tabla" WHERE "nombre_columna" LIKE {patrón*}
Comando SELECT …WHERE….. LIKE (patrón)
*{patrón} generalmente consiste en comodines
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Función de Rango de Valores
SELECT * FROM SELECT2.Información_ventas WHERE Tienda LIKE '%e%'
Resultado:
Cuenca 08/01/2010 300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Comando SELECT …WHERE….. LIKE (patrón)
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Cuenca
Tienda
300
Barcelona 700
Sevilla 250 2010/01/07
2010/01/05
2010/01/08
2010/01/08
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
'A_Z': Toda línea que comience con 'A', otro carácter y termine con 'Z‘.
– Por ejemplo, 'ABZ' y 'A2Z' deberían satisfacer la condición, mientras 'AKKZ' no debería (debido a que hay dos caracteres entre A y Z en vez de uno).
'ABC%': Todas las líneas que comienzan con 'ABC'. – Por ejemplo, 'ABCD' y 'ABCABC' ambas deberían satisfacer la
condición.
'%XYZ': Todas las líneas que terminan con 'XYZ'. Por ejemplo, 'WXYZ' y 'ZZXYZ’ ambas deberían satisfacer la condición.
'%AN%': Todas las líneas que contienen el patrón 'AN' en cualquier lado.
– Por ejemplo, 'LOS ANGELES' y 'SAN FRANCISCO' ambos deberían satisfacer la condición.
Consulta de Datos Selección Búsqueda de Patrones
Comando SELECT …WHERE….. LIKE (patrón)
EJEMPLOS DE PATRONES LIKE:
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Con frecuencia necesitamos enumerar el resultado obtenido de una consulta utilizando los comandos SELECT y WHERE en un orden particular. Esto podría ser en orden ascendente, en orden descendente,
En tales casos, podemos utilizar la palabra clave ORDER BY para alcanzar este objetivo. La sintaxis para una instrucción ORDER BY es la siguiente:
Consulta de Datos Selección Ordenación de Resultados
Comando SELECT …WHERE….. ORDER BY
[] significa que la instrucción WHERE es opcional. Sin embargo, si existe una cláusula WHERE, viene antes de la cláusula ORDER BY ASC significa que los resultados Se mostrarán en orden ascendente, y DESC significa que los resultados se mostrarán en orden descendente. Si no se especifica ninguno, la configuración predeterminada es ASC.
SELECT "nombre_columna" FROM "nombre_tabla" [WHERE "condición"] ORDER BY "nombre_columna" [ASC, DESC]
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Es posible ordenar por más de una columna. En este caso, la cláusula ORDER BY anterior se convierte en
Consulta de Datos Selección Ordenación de Resultados
Comando SELECT …WHERE….. ORDER BY
Suponiendo que elegimos un orden ascendente para ambas columnas, el resultado se clasificará en orden ascendente según la columna 1. Si hay una relación para el valor de la columna 1, se clasificará en orden ascendente según la columna 2.
ORDER BY "nombre1_columna" [ASC, DESC], "nombre2_columna" [ASC, DESC]
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Ordenación de Resultados
SELECT Tienda, Ventas, Fecha FROM Información_ventas ORDER BY Ventas DESC,
Resultado:
Comando SELECT …WHERE….. ORDER BY
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Cuenca
Tienda
300
Barcelona 700
Sevilla 250
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Cuenca
Tienda
300
Barcelona 700
Sevilla 250 2010/01/07
2010/01/05
2010/01/08
2010/01/08
2010/01/05
2010/01/08
2010/01/08
2010/01/07
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Ordenación de Resultados
SELECT Tienda, Ventas, Fecha FROM Información_ventas ORDER BY Ventas DESC, Fecha DESC
Resultado:
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Cuenca
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 1500
07/01/2010 Sevilla 250
Comando SELECT …WHERE….. ORDER BY
Tabla: Información_Ventas
Ventas Fecha
Madrid
1500
Cuenca
08/01/2010
08/01/2010
Tienda
300
Barcelona
05/01/2010 1500
07/01/2010 Sevilla 250
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Es posible hacer cálculos matemáticos con los valores de algunos atributos, tales como sumas, o sacar un promedio. SQL tiene varias funciones aritméticas, y estas son: AVG, COUNT, MAX, MIN, SUM
La sintaxis para una instrucción que incluya alguna función sería:
Consulta de Datos Selección Funciones Aritméticas
Comando SELECT …….. AVG, COUNT, MAX,MIN,SUM
SELECT "tipo de función"("nombre_columna") FROM "nombre_tabla"
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Funciones Aritméticas
Comando SELECT …….. AVG, COUNT, MAX,MIN,SUM
SELECT SUM(Ventas) FROM Información_ventas
Resultado:
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
SUM(Ventas): 2750
2 750 representa la suma de todas las entradas de Ventas: 1500 € + 250 € + 300 € + 700 €.
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Otra función aritmética es COUNT. Esto nos permite contar el número de filas en una tabla determinada. La sintaxis es,
Consulta de Datos Selección Funciones Aritméticas
Comando SELECT …….. AVG, COUNT, MAX,MIN,SUM
*SELECT COUNT ("nombre_columna") FROM"nombre_columna"
*Select count (“nombre columna”) no contabiliza los valores nulos Para contar el número de tuplas de una tabla select(*)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Funciones Aritméticas
Comando SELECT …….. AVG, COUNT, MAX,MIN,SUM
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
SELECT COUNT(Tienda) FROM Información_Ventas Resultado: Count(Tienda) 4
SELECT COUNT(DISTINC Tienda) FROM Información_Ventas Resultado: COUNT(DISTINC Tienda) 3
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Es bastante frecuente que queramos aplicar funciones aritméticas no a todas las tulpas de un campo sino a grupos de tulpas que tengan el mismo valor de un atributo
Utilizaremos la cláusula GROUP BY Si en una consulta se utiliza la cláusula GROUP BY los
únicos atributos que pueden aparecen en la cláusula SELECT son los que aparecen en la lista de atributos de la cláusula GROUP BY o agregados
Consulta de Datos Selección Funciones Aritméticas
Comando SELECT ….. AVG, COUNT, MAX,MIN,SUM… GROUP BY…
SELECT "nombre1_columna", SUM("nombre2_columna") FROM "nombre_tabla" GROUP BY "nombre1-columna"
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección Funciones Aritméticas
SELECT Tienda, SUM(Ventas) FROM Información_Ventas GROUP BY Tienda
Resultado:
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tienda, SUM(Ventas) Madrid 1800 Sevilla 250 Barcelona 700
Comando SELECT ….. AVG, COUNT, MAX,MIN,SUM… GROUP BY…
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Otra cosa que la gente puede querer hacer es limitar el resultado según la suma correspondiente (o cualquier otra función).
La cláusula HAVING se coloca generalmente cerca del fin de la instrucción SQL
Comando SELECT ….. AVG, COUNT, MAX,MIN,SUM… HAVING…
SELECT "nombre1_columna", SUM("nombre2_columna") FROM "nombre_tabla" GROUP BY "nombre1_columna" HAVING (condición de función)
Consulta de Datos Selección y limitar Funciones
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Comando SELECT ….. AVG, COUNT, MAX,MIN,SUM… HAVING…
Consulta de Datos Selección y limitar Funciones
SELECT Tienda, SUM(Ventas) FROM Información_Ventas GROUP BY Tienda HAVING SUM(Ventas) > 1500 Resultado: Tienda SUM(Ventas) Madrid 1800 €
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Hay dos tipos de alias que se utilizan con mayor frecuencia. Alias de columna y alias de tabla.
Los alias de columna existen para ayudar en la organización del resultado.
El segundo tipo de alias es el alias de tabla. Esto se alcanza al colocar un alias directamente luego del nombre de tabla en la cláusula FROM.
– Esto es conveniente cuando desea obtener información de dos tablas separadas
SELECT "alias_tabla"."nombre1_columna" "alias_columna" FROM "nombre_tabla" "alias_tabla"
Consulta de Datos Selección con Alias
Comando SELECT ….. “alias_tabla…………….”alias_columna”
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección con Alias
Comando SELECT ….. “alias_tabla…………….”alias_columna”
SELECT A1.Tienda Tiendas, SUM( A1.ventas ) "Total Ventas" FROM SELECT4.Información_Ventas A1 GROUP BY A1.Tienda
Resultado:
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tiendas, Total_Ventas Madrid 1800 Sevilla 250 Barcelona 700
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección 2 tablas (join natural)
Puede que se necesite recuperar información de dos tablas
Haciendo el producto cartesiano se obtienen tuplas no válidas
La tuplas invalidas se eliminan con la clusula WHERE haciendo coincidir los atributos semejantes de tablas diferentes
Comando SELECT .. From Tabla1, Tabla2 WHERE Tabla1.atr=Tabla2.atr
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección 2 tablas (join natural)
Comando SELECT .. From Tabla1, Tabla2 WHERE Tabla1.atr=Tabla2.atr
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
Supóngase que se quieren obtener las ventas por región 1. Hacemos el producto cartesiano de las tuplas de una tabla por la otra 2. Eliminamos las tuplas que no tengan sentido 3. Seleccionamos la columna agrupando tuplas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección 2 tablas (join natural)
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tabla: Zonas_Ventas
Centro-Sur
Región
Madrid
Tienda
Madrid 1500
Madrid
05/01/2010
08/01/2010 300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Centro-Sur Madrid
Centro-Sur Madrid
Centro-Sur Madrid
Madrid 1500
Madrid
05/01/2010
08/01/2010 300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Madrid 1500
Madrid
05/01/2010
08/01/2010 300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Noreste Zaragoza
Noreste Zaragoza
Noreste Zaragoza
Noreste Zaragoza
Noreste Barcelona
Noreste Barcelona
Noreste Barcelona
Noreste Barcelona
Centro-Sur Sevilla
Centro-Sur Sevilla
Centro-Sur Sevilla
Centro-Sur Sevilla
El producto Cartesiano genera tuplas que no son de utilidad en la consulta
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Consulta de Datos Selección 2 tablas (Join Natural)
Comando SELECT .. From Tabla1, Tabla2 WHERE Tabla1.atr=Tabla2.atr
Tabla: Información_Ventas
Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Tabla: Zonas_Ventas
Centro-Sur
Región
Madrid
Tienda
07/01/2010 Sevilla 250
Centro-Sur Madrid
Barcelona 08/01/2010 700 Noreste Barcelona
Centro-Sur Sevilla
SELECT A1.Región REGION, SUM( A2.Ventas ) VENTAS FROM join1.Zona_Ventas A1, join1.Información_Ventas A2 WHERE A1.Tienda = A2.Tienda GROUP BY A1.región Resultado: REGIÓN VENTAS Centro-Sur 2050 Noreste 700
Tabla Resultante
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Algunas veces es necesario combinar en forma conjunta (concatenar) los resultados de varios campos diferentes.
Cada base de datos brinda una forma para realizar esto (||, +)
Comando SELECT .. CONCAT
CONCAT(cad1, cad2,cad3, ...): Concatenar
Consulta de Datos Selección Concatenación Resultados
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
SELECT CONCAT(Región,Tienda) FROM Zona_Ventas WHERE Tienda = ‘Madrid';
Resultado :
‘Centro-SurMadrid’
Consulta de Datos Selección Concatenación Resultados
Comando SELECT .. CONCAT
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
La función de subcadena en SQL se utiliza para tomar una parte de los datos almacenados
Esta función tiene diferentes nombres según las diferentes bases de datos (SUBSTR(), SUBSTRING())
SUBSTR(str,pos): Selecciona todos los caracteres de <str> comenzando con posición SUBSTR(str,pos,len): Comienza con el carácter <pos> en la cadena <str> y selecciona los siguientes caracteres <len>.
Consulta de Datos Selección Subcadenas Resultados
Comando SELECT .. SUBSTR
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
SELECT SUBSTR(Tienda, 3) FROM Zona_Ventas WHERE Tienda = ‘Madrid';
Resultado : ‘drid’
SELECT SUBSTR(Tienda, 2,4) FROM Zona_Ventas WHERE Tienda = ‘Barcelona';
Resultado : ‘arce’
Comando SELECT .. SUBSTR
Consulta de Datos Selección Subcadenas Resultados
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Es posible incorporar una instrucción SQL dentro de otra. Cuando esto se hace en las instrucciones WHERE o HAVING, tenemos una construcción de subconsulta.
La sintaxis es la siguiente:
[Operador de Comparación] podrían ser operadores de igualdad tales como =, >, <, >=, <=. También puede ser un operador textual como "LIKE". La parte en rojo se considera como la "consulta interna", mientras que la parte en verde se considera como la "consulta externa".
SELECT "nombre1_columna" FROM "nombre1_tabla" WHERE "nombre2_columna" [Operador ] (SELECT "nombre3_columna" FROM "nombre2_tabla" WHERE [Condición] )
SQL AVANZADO CONSULTAS ANIDADAS
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SELECT SUM(Ventas) FROM Información_Ventas WHERE Tienda IN (SELECT Tienda FROM Zona_ventas WHERE región = 'Noreste') SUM(Ventas) 2050
Tabla: Información_Ventas
Ventas Fecha
Zaragoza 1500
Zaragoza
05/01/2010
08/01/2010
Tienda
300
Madrid 08/01/2010 700
07/01/2010 Barcelona 250
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
SQL AVANZADO CONSULTAS ANIDADAS
“ventas de todas las tiendas de la Noreste”
La cláusula IN se puede usar para enlazar la consulta interna y la consulta externa en una consulta anidada (Se pueden utilizar muchos operadores tales como >, <, o =)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SELECT SUM(a1.Ventas) FROM Informacion_Ventas a1 WHERE a1.Tienda IN (SELECT Tienda FROM Zona_ventas a2) SUM(Sales) 2750
Tabla: Información_Ventas
Ventas Fecha
Zaragoza 1500
Zaragoza
05/01/2010
08/01/2010
Tienda
300
Madrid 08/01/2010 700
07/01/2010 Barcelona 250
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
SQL AVANZADO CONSULTAS ANIDADAS
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
EXISTS es un operador especial que simplemente
verifica si la consulta interna arroja alguna fila. Si lo hace, entonces la consulta externa procede. De no
hacerlo, la consulta externa no se ejecuta, y la totalidad de la instrucción SQL no arroja nada.
SELECT "nombre1_columna" FROM "nombre1_tabla" WHERE EXISTS (SELECT * FROM "nombre2_tabla" WHERE [Condición])
SQL AVANZADO Operador EXISTS
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SELECT SUM(Ventas) FROM Informacion_Ventas WHERE EXISTS (SELECT *FROM Zona_ventas WHERE Region=‘Noreste’) SUM(Sales) 2750
Tabla: Información_Ventas
Ventas Fecha
Zaragoza 1500
Zaragoza
05/01/2010
08/01/2010
Tienda
300
Madrid 08/01/2010 700
07/01/2010 Barcelona 250
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
SQL AVANZADO Operador EXISTS
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO EJEMPLO
Película(titulo, año, duración, en_color, nombre_estudio, numero) Protagoniza(titulo_pelicula, año_película, nombre_actor) Actor(nombre, dirección, sexo, fecha_nacimiento) Productora(numero, nombre, direccion, valor) Estudio(nombre, dirección)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Subconsultas que devuelven un valor escalar
SELECT P.nombre AS ‘Nombre de productora’ FROM película F, productora P WHERE F.numero = P.numero AND F.titulo = ‘Pretty woman’;
Consulta: el nombre de la productora de la película Pretty woman
SELECT nombre AS ‘Nombre de productora’ FROM productora WHERE numero = (SELECT numero FROM pelicula WHERE titulo = ‘Pretty woman’);
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Subconsultas que devuelven un valor escalar
Consulta: Actores que han protagonizado siete o más películas
SELECT nombre, fecha_nacimiento AS ‘Fecha de nacimiento’ FROM actor WHERE 7 <= (SELECT COUNT(*) FROM protagoniza WHERE nombre_actor = actor.nombre);
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Subconsultas que devuelven tablas
Consulta: El nombre y la fecha de nacimiento de aquellos actores que no protagonizan ninguna película
SELECT nombre, fecha_nacimiento AS ‘Fecha de nacimiento’ FROM actor WHERE NOT EXISTS (SELECT * FROM protagoniza WHERE nombre_actor = actor.nombre);
* NOT EXISTS devuelve true si la tabla es vacía y false si la tabla contiene elementos
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Subconsultas que devuelven tablas
Consulta: Aquellas películas cuyas productoras tengan un valor mayor a 10 millones
SELECT titulo FROM pelicula WHERE numero IN (SELECT numero FROM productora WHERE valor > 10000000);
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Subconsultas en la cláusula FROM (I)
SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column2) AS t2;
SELECT ... FROM (subquery) [AS] name ... La clausula [AS] es obligatoria ya que cada tabla en la claúsula FROM debe de tener un nombre
Consulta: Calcular el valor medio de la sumas de la columna1 en los grupos formados en la tabla t1 agrupando por la columna2
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Subconsultas en la cláusula FROM (I)
Tabla: Información_Ventas
Ventas Fecha
Zaragoza 1500
Zaragoza
05/01/2010
08/01/2010
Tienda
300
Madrid 08/01/2010 700
08/01/2010 Barcelona 250
Tabla: Zonas_Ventas
Centro-Sur
Noreste
Región
Noreste
Madrid
Zaragoza
Tienda
Barcelona
Sevilla Centro-Sur
Madrid 09/02/2010 400
08/02/2010 Barcelona 850
tienda SUM(ventas) Barcelona 1100 Madrid 1100 Zaragoza 1800
SELECT tienda, SUM(ventas) FROM anidadofrom1.Información_Ventas GROUP BY tienda
SELECT max(sum_colum1) FROM (SELECT SUM(ventas) AS sum_colum1 FROM anidadofrom1.Información_Ventas GROUP BY tienda) AS t2;
max(sum_colum1) 1800
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
El propósito del comando SQL UNION es combinar los resultados de dos consultas juntas
Una restricción de UNION es que todas las columnas correspondientes necesitan ser del mismotipo de datos
cuando utilizamos UNION, sólo se seleccionan valores distintos (similar a SELECT DISTINCT)
SQL AVANZADO UNION
La sintaxis es la siguiente: [Instrucción SQL 1] UNION [Instrucción SQL 2]
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Ventas_Tiendas Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tabla: Ventas_Internet Fecha
250
320
Ventas
750
535
07/01/2010
10/01/2010
11/01/2010
12/01/2010
SELECT Fecha FROM Ventas_Tienda UNION SELECT Fecha FROM Ventas_Internet Resultado: Fecha 05-Jan-2010 07-Jan-2010 08-Jan-2010 10-Jan-2010 11-Jan-2010 12-Jan-2010
SQL AVANZADO UNION
-> Todas las Fechas donde se hizo una operación de ventas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
El propósito del Comando SQL UNION ALL es también combinar los resultados de dos consultas juntas.
La diferencia entre UNION ALL y UNION es que, mientras UNION sólo selecciona valores distintos, UNION ALL selecciona todos los valores.
SQL AVANZADO UNION ALL
[Instrucción SQL 1] UNION ALL [Instrucción SQL 2]
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Ventas_Tiendas Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tabla: Ventas_Internet Fecha
250
320
Ventas
750
535
07/01/2010
10/01/2010
11/01/2010
12/01/2010
SELECT Fecha FROM Ventas_Tienda UNION ALL SELECT Fecha FROM Ventas_Internet Resultado: Fecha 05/01/2010 07/01/2010 08/01/2010 08/01/2010 07/01/2010 10/01/2010 11/01/2010 12/01/2010
SQL AVANZADO UNION ALL
-> Fechas donde se hicieron ventas bien sea en las tiendas o en Internet
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
SQL AVANZADO Intersección en Columnas
Tabla: Ventas_Tiendas Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tabla: Ventas_Internet Fecha
250
320
Ventas
750
535
07/01/2010
10/01/2010
11/01/2010
12/01/2010
SELECT Fecha FROM Ventas_Tienda WHERE Fecha in (SELECT fecha FROM Ventas_Internet)
-> Fechas donde hay ventas tanto en tiendas como en Internet
SELECT VT.Fecha FROM Ventas_Tienda VT WHERE EXISTS (SELECT * FROM Ventas_Internet VI WHERE VT.Fecha=VI.Fecha)
Resultado: Fecha 07/01/2010
SELECT VT.Fecha FROM Ventas_Tienda VT, Ventas_internet VI WHERE VT.fecha = VI.fecha
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Tabla: Ventas_Tiendas Ventas Fecha
Madrid 1500
Madrid
05/01/2010
08/01/2010
Tienda
300
Barcelona 08/01/2010 700
07/01/2010 Sevilla 250
Tabla: Ventas_Internet Fecha
250
320
Ventas
750
535
07/01/2010
10/01/2010
11/01/2010
12/01/2010
SELECT DISTINCT Fecha FROM Ventas_Tienda WHERE Fecha NOT IN (SELECT fecha FROM Ventas_Internet)
SELECT DISTINCT VT.Fecha FROM Ventas_Tienda VT WHERE NOT EXISTS (SELECT * FROM Ventas_Internet VI WHERE VT.Fecha=VI.Fecha)
Resultado: Date 05/01/2010 08/01/2010
-> Fechas donde hay ventas en tiendas , pero no en Internet
SQL AVANZADO Diferencia en Columnas
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Operadores de Asignación
“:=“ Asigna un valor “= “Asigna un valor (como parte de una
sentencia SET o como parte de la cláusula SET de una sentencia UPDATE)
SELECT @nombre:=nombre FROM Set1.cliente WHERE dni='7211545v'; SELECT @npedido:=npedido FROM Set1.Pedidos WHERE (fecha='2013/01/05' and cliente_dni='7211545v'); SELECT @nombre; SELECT @npedido set @newdni='7211546v'; SELECT @newdni; UPDATE Set1.cliente SET Dni=@newdni WHERE Dni='7211545v';
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Transaciones Mysql (I)
Una Transacción es un conjunto de instrucciones que se ejecutan en forma indivisible o atómica
Un SGDB se dice que es Transaccional (ACID complain) si tiene la con las funcionalidades necesarias para que sus transacciones tengan las características ACID*
– Atomicidad – Consistencia – Aislamiento – Persistencia
*ACID es un acrónimo de Atomicity, Consistency, Isolation and Durability: ( Atomicidad, Consistencia, Aislamiento y Durabilidad)
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Transaciones Mysql (II)
SET AUTOCOMMIT=0; START TRANSACTION; INSERT INTO Autocommit10.Pago (fecha, cantidad, id_pedido) VALUES ('2013/01/05',100,1); UPDATE Autocommit10.Cuenta SET saldo=saldo-100 WHERE Cliente_dni='7211545v'; COMMIT;
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Transaciones Mysql (III)
SET AUTOCOMMIT=0; START TRANSACTION; SET @cantidad=100; SELECT @npedido:=npedido FROM Autocommit11.Pedidos WHERE (fecha='2013/01/05' and cliente_dni='7211545v'); INSERT INTO Pago (fecha, cantidad, id_pedido) VALUES ('2013/01/05',@cantidad,@npedido); UPDATE Cuenta SET saldo=saldo-@cantidad WHERE Cliente_dni='7211545v'; COMMIT;
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
Transaciones Mysql (IV) SET AUTOCOMMIT=0; START TRANSACTION; INSERT INTO Autocommit12.Cliente VALUES ('7211541v','Juan','Garzon Rodriguez'); ROLLBACK;
SET AUTOCOMMIT=0; START TRANSACTION; INSERT INTO Autocommit12.Cliente VALUES ('7211541v','Juan','Garzon Rodriguez'); DELETE FROM Autocommit12.Cliente WHERE Dni='7211545v'; COMMIT;
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
FUNCIONES TIEMPO MYSQL (I)
Obtener la fecha y hora en MySQL: select now(); # ejemplo: '2010-01-12 10:50:43‘ Obtener solo día mes y año en MySQL: select CURDATE(); # ejemplo: '2010-01-12' #sin hora Obtener hora actual en MySQL: select curTime(); #Selecciona la hora Obtener día, mes, año, u hora de una fecha en MySQL: select YEAR(NOW()); #Selecciona el año select MONTH (NOW()) as mes; #Selecciona el mes select DAY(NOW()) as dia; #Selecciona el día select TIME(NOW()) as hora; #Selecciona la hora Select LAST_DAY(NOW()); # Selecciona el ultimo dia del mes
BASES DE DATOS - Elementos Básicos de SQL
© Luis Mengual
FUNCIONES TIEMPO MYSQL (Ii)
Sumar o restar días a una fecha con DATE_ADD o DATE_SUB en MySQL: DATE_ADD(fecha,INTERVAL valor tipo), DATE_SUB(fecha,INTERVAL valor tipo) Sumar tiempo en MySQL: select DATE_ADD(NOW(),INTERVAL 20 DAY); # Agrega 20 días a la fecha actual select DATE_ADD(NOW(),INTERVAL 30 MINUTE); # Agrega 30 minutos a la fecha actual select DATE_ADD(NOW(),INTERVAL 50 YEAR); #Agrega 50 años a la fecha actual select DATE_ADD(NOW(),INTERVAL '10-5' YEAR_MONTH); #Agrega 10 años 5 meses a la fecha actual Restar tiempo en MySQL: select DATE_SUB(NOW(),INTERVAL 8 YEAR); #Resta 8 años a la fecha actual select DATE_SUB(NOW(),INTERVAL 24 HOUR); #Resta 24 horas a la fecha actual select DATE_SUB(NOW(),INTERVAL '7-2' YEAR_MONTH); #Resta 7 años dos meses a la fecha actual select DATE_SUB(NOW(),INTERVAL '2:10' MINUTE_SECOND); Restar dos fechas: DATEDIFF(fecha_1,fecha_2) devuelve el número de días entre la fecha fecha_1 y la fecha_2 SELECT DATEDIFF(NOW(),'2002-11-02'); #cuantos días han pasado SELECT DATEDIFF(NOW(),'2010-03-20'); #Cuantos días faltan