Resumen consultas muy bueno by Juan Manuel Huerta García

I.E.S. MARCOS ZARAGOZA

SISTEMAS GESTORES DE BASES DE DATOS

SQL



SQL fue desarrollado inicialmente por IBM como lenguaje de consulta de su SGBD DB2, en 1970 Es un lenguaje no procedimental (indica qué datos se desea manipular, pero no de qué manera hacerlo) Hay dos organizaciones, ANSI e ISO que tratan de favorecer la estandarización del lenguaje, no obstante, cada fabricante de SGBDs aporta sus propias características

INTRODUCCIÓN



SQL no se limita únicamente a realizar consultas sobre los datos almacenados

Lenguaje de Definición de Datos.  DML: Lenguaje de Manipulación de Datos.  DCL: Lenguaje de Control de Datos.

 DDL:

SQL significa Structured Query Language (Lenguaje Estructurado de Consulta)  SQL incluye soporte para los lenguajes:

INTRODUCCIÓN



de acceso a datos  Compartición de datos entre usuarios concurrentes  Integridad de datos

Altas, Bajas y Modificaciones

 Control



de datos  Recuperación de datos  Manipulación de datos

 Definición

SQL permite:

INTRODUCCIÓN



SQL  Fundamento relacional  Estructura de alto nivel similar al inglés  Consultas interactivas  Acceso a bases de datos desde lenguajes de programación  Múltiples vistas de datos  Arquitectura cliente/servidor

entre proveedores

 Estandarización

 Portabilidad

 Independencia

Características:

INTRODUCCIÓN

? *

Un carácter cualquiera

Cero o más caracteres

_ (subrayado)

ANSI SQL

 Los nombres SQL deben contener de 1 a 18 caracteres, comenzar con una letra, y que no pueden contener espacios o caracteres de puntuación especiales, exceptuando el guión de subrayado.

El estándar ANSI/ISO especifica que:

Access SQL

Carácter coincidente

Reglas del SQL ANSI

ď Ž

Pepe.Productos: Hace referencia a la tabla Productos de la que el usuario Pepe es propietario. ď Ž Productos.Clave: Hace referencia al atributo Clave de la tabla Productos. Es necesario poner el alias de la tabla cuando hay atributos en distintas tablas que tienen el mismo nombre.

Nombres de tabla y de columna

 Entero:

 Real:

234,45. 234

Numéricas: Se escriben directamente. Por ejemplo:



Alfanuméricas: Se escriben entre comillas preferentemente simples.  Fechas: Se escriben entre comillas preferentemente simples.



Constantes



 Funciones

de agregado

 Operadores

 Cláusulas

 Comandos

El lenguaje SQL está compuesto por:

Componentes de SQL

Para eliminar tablas e índices

Para modificar las tablas agregando campos o cambiando la definición de los campos.

DROP

ALTER

Para crear nuevas tablas, campos e índices

CREATE

Comando Descripción

Sentencias DDL

Para cargar lotes de datos en la base de datos en una Ăşnica operaciĂłn. Para modificar los valores de los campos y registros especificados Para eliminar registros de una tabla de una base de datos

INSERT

UPDATE

DELETE

Para consultar registros de la base de datos que satisfagan un criterio determinado

SELECT

Sentencias DML

Para especificar las condiciones que deben reunir los registros que se van a seleccionar Para separar los registros seleccionados en grupos específicos Para expresar la condición que debe satisfacer cada grupo Para ordenar los registros seleccionados de acuerdo con un orden específico

WHERE

GROUP BY

HAVING

ORDER BY

Para especificar la tabla de la cual se van a seleccionar los registros

FROM

Cláusulas

“O" lógico. Evalúa dos condiciones y devuelve un valor de verdar si alguna de las dos es cierta.

Negación lógica. Devuelve el valor contrario de la expresión.

NOT

“Y" lógico. Evalua dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas.

AND

Operador Uso

Operadores Lógicos

Mayor que Distinto de Menor 贸 Igual que Mayor 贸 Igual que Igual que Utilizado para especificar un intervalo de valores. Utilizado en la comparaci贸n de un modelo Utilizado para especificar registros de una base de datos

BETWEEN

Menor que

Operadores de comparaci贸n

Utilizada para devolver el número de registros de la selección Utilizada para devolver la suma de todos los valores de un campo determinado Utilizada para devolver el valor más alto de un campo especificado Utilizada para devolver el valor más bajo de un campo especificado

COUNT

SUM

MAX

MIN

Utilizada para calcular el promedio de los valores de un campo determinado

AVG

Funciones de agregado

Tamañ o 1 byte

1 byte 1 byte 4 bytes 8 bytes

Tipo de Datos

BINARY

BIT

BYTE

COUNTER (Autonumérico)

CURRENCY (Moneda)

Un entero escalable entre 922.337.203.685.477,5808 y 922.337.203.685.477,5807.

Un número incrementado automáticamente (de tipo Long)

Un valor entero entre 0 y 255.

Valores Si/No ó True/False

Para consultas sobre tabla adjunta de productos de bases de datos que definen un tipo de datos Binario.

Descripción

Tipos de datos estándar (I)

Un valor en punto flotante de precisión simple con un rango de -3.402823*1038 a -1.401298*10-45 para valores negativos, 1.401298*10-45 a 3.402823*1038 para valores positivos, y 0.

4 bytes

8 bytes

SINGLE

DOUBLE

Un valor en punto flotante de doble precisión con un rango de -1.79769313486232*10308 a -4.94065645841247*10-324 para valores negativos, 4.94065645841247*10-324 a 1.79769313486232*10308 para valores positivos, y 0.

Un valor de fecha u hora entre los años 100 y 9999.

Tamaño Descripción

DATETIME 8 bytes

Tipo de Datos

Tipos de datos estándar (II)

Un entero corto entre -32,768 y 32,767.

2 bytes

4 bytes

1 byte por carácter

Según se necesite

1 byte por carácter

SHORT

LONG ó INTEGER

LONGTEXT

LONGBINAR Y

TEXT CHAR

VARCHAR

Longitud variable 19

De cero a 255 caracteres

De cero 1 gigabyte. Utilizado para objetos OLE.

De cero a un máximo de 1.2 gigabytes. (MEMO)

Un entero largo entre -2,147,483,648 y 2,147,483,647.

Descripción

Tipo de Datos Tamaño

Tipos de datos estándar (III)

VARBINARY BOOLEAN LOGICAL LOGICAL1 YESNO INTEGER1 AUTOINCREMENT MONEY DATE TIME TIMESTAMP

BINARY BIT

BYTE COUNTER CURRENCY DATETIME

Tipo de Dato Sinรณnimos

Sinรณnimos de tipos de datos (I)

FLOAT4 IEEESINGLE REAL FLOAT FLOAT8 IEEEDOUBLE NUMBER NUMERIC INTEGER2 SMALLINT

SINGLE

DOUBLE

SHORT

Tipo de Dato Sinรณnimos

Sinรณnimos de tipos de datos (II)

Sinรณnimos INT INTEGER INTEGER4 GENERAL OLEOBJECT LONGCHAR MEMO NOTE ALPHANUMERIC CHAR CHARACTER STRING VARCHAR

Tipo de Dato

LONG

LONGBINAR Y

LONGTEXT

TEXT

Sinรณnimos de tipos de datos (III)

Creaciรณn de bases de datos



Además de la manipulación de datos, que se realiza por medio del lenguaje DML, con SQL se puede definir la propia estructura de la Base de Datos  Esto se realiza mediante el DDL  Así como el DML está bastante estandarizado, respecto al DDL, va a haber muchas dependencias respecto al SGBD que se esté utilizando

El lenguaje DDL



 CREATE,

que define y crea un objeto de la base de datos  DROP, que elimina un objeto existente en la base de datos  ALTER, que modifica la definición de un objeto de la base de datos

El núcleo del DDL está basado en tres cláusulas de SQL

El lenguaje DDL



USE



DATABASE Nombre_de_la_Base_de_Datos

Abre la base de datos.

Nombre_de_la_Base_de_Datos 26

Borra una Base de Datos que previamente está cerrada (CLOSE).

DATABASE Nombre_de_la_Base_de_Datos

Crea una base de datos con el nombre indicado.

DROP



CREATE

Dependiendo del SGBD utilizado, puede variar:

Creación y borrado de una BBDD

);

atributo ……

[NOT NULL]

atributo tipo(tamaño)[DEFAULT valor] [Índice],



Se realiza mediante la sentencia CREATE TABLE.La sintaxis básica es la siguiente: CREATE TABLE nombre_tabla (

Creación de una tabla

Es el nombre que tomará la

 tipo(tamaño):

es la indicación del tipo de dato que la columna podrá contener y su longitud.



En la definición de la tabla se pueden crear tantas columnas como sea necesario  El nombre de columna debe empezar por un carácter alfabético

tabla en la BBDD  atributo: Es el nombre de una de las columnas.

 nombre_tabla:

Creación de una tabla

La sintaxis que hay que usar es la siguiente: DEFAULT { valor | NULL }



NOT NULL, que indica que la columna no puede tomar el valor NULL.

 vínculos_de_columna:

son vínculos de integridad que se aplican a cada atributo concreto. Pueden ser:



 cláusula_defecto:

indica el valor de defecto que tomará la columna si no se le asigna uno explícitamente en el momento en que se crea la línea.

Creación de una tabla



 PRIMARY

KEY, que indica que la columna es la clave primaria de la tabla.  UNIQUE: indica que no se admiten duplicados para ese campo  Una definición de referencia (clave ajena) con la forma: REFERENCES Nom_tabla(clave) [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL }] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL }]

vínculos_de_columna: (continuación)

Creación de una tabla

(continuación)



PRIMARY KEY (columna1 [, columna2,…]) para crear claves primarias compuestas de más de una columna.  FOREIGN KEY (columna1 [, columna2, …]) definiciones_de_referencia para crear claves ajenas de más de una columna

 vínculo_de_tabla:

son vínculos de integridad que se pueden referir a más columnas de la tabla. Estos pueden ser:



CHECK (expresión_condicional) Ejemplo: CHECK (PRECIO<1000)

 vínculos_de_columna:

Creación de una tabla

CREATE TABLE Autor ( IDPublicacion INTEGER REFERENCES Publicacion(ID), IDPersona INTEGER REFERENCES Persona(ID), PRIMARY KEY (IDPublicacion, IDPersona));

CREATE TABLE Libro ( ID INTEGER PRIMARY KEY REFERENCES Publicacion(ID), Titulo VARCHAR(160) NOT NULL, Editor INTEGER NOT NULL REFERENCES Editor(ID), Volumen VARCHAR(16), Serie VARCHAR(160), Edicion VARCHAR(16), Mes_publicacion CHAR(3), Anyo_publicacion INTEGER NOT NULL, Comentario VARCHAR(255));

CREATE TABLE Publicacion ( ID INTEGER PRIMARY KEY, Tipo CHAR(18) NOT NULL);

Ejemplos:

Creaciรณn de una tabla en Informix

CHAR(30), DATETIME

); 33

Nombre CHAR(30), Fecha_Nac DATETIME, CONSTRAINT PK_Cole PRIMARY KEY (Num_Colegio)

CREATE TABLE Colegios ( Num_Colegio INTEGER NOT NULL,

); O bien:

Nombre Fecha_Nac

CREATE TABLE Colegios ( Num_Colegio INTEGER NOT NULL PRIMARY KEY,

Ejemplo de creaciรณn de tabla:

clave principal:

clave ajena:

[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL }] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL }]

CONSTRAINT FK_Nom_tabla_indices FOREIGN KEY (atributo1[, atributo2...]) REFERENCES Nom_tabla(Atributos_clave)

 Definir

CONSTRAINT PK_Nom_tabla_indices PRIMARY KEY (Atributos_clave_principal)

 Definir

Definir claves al final de la tabla

);



CONSTRAINT PK_Emple PRIMARY KEY (Apellidos, Nombre, Fecha_Nac)

CREATE TABLE Empleados ( Nombre CHAR(15), Apellidos CHAR(35), Fecha_Nac DATETIME, Telef CHAR(9),

Ejemplo creación de tabla con clave compuesta:

CONSTRAINT PK_Clie PRIMARY KEY (IDCliente) );



CREATE TABLE Clientes (IDCliente INTEGER NOT NULL, Nom CHAR(15) NOT NULL, Apel CHAR(35) NOT NULL,

Ejemplo de definición de una clave ajena I

CREATE TABLE Pedidos ( IDPedido INTEGER PRIMARY KEY, IDCliente INTEGER, NotasPedido CHAR(50), CONSTRAINT FK_Pedido_Cliente FOREIGN KEY (IDCliente) REFERENCES Clientes(IDCliente) ON UPDATE CASCADE ON DELETE CASCADE );

Ejemplo de definiciรณn de una clave ajena

); 38

CREATE TABLE Sucursal ( Num_Suc CHAR(2) NOT NULL, Ciudad CHAR(20) NOT NULL, Zona CHAR(8) NOT NULL, Director CHAR(3) NOT NULL, Objetivo_Año INTEGER NOT NULL, CONSTRAINT PK_Sucur PRIMARY KEY(Num_Suc), CONSTRAINT SK_ciu UNIQUE(Ciudad), CONSTRAINT FK_emple FOREIGN KEY(Director) REFERENCES INFEMPLE(Num_Emple)

Definición de una clave única



DROP DROPTABLE TABLEEmpleados; Empleados; 39

La sentencia puede fallar si hay reglas de integridad referencial que impidan el borrado de datos de tal tabla, y por tanto también impiden el borrado de la propia tabla

DROP TABLE nombre_de_tabla

Se especifica mediante la sentencia:

Borrado de una tabla



nuevas columnas a una tabla  Eliminar columnas de una tabla  Modificar las definiciones de campo de una tabla (no todos los SGBD permiten esta operación)  Crear restricciones de integridad  Eliminar restricciones de integridad

 Añadir

Permite básicamente las siguientes operaciones:

Modificación de tablas

ALTER TABLE nombre_tabla ADD nombre_col definición_col  La definición de la columna sigue la misma sintaxis que se ha visto en el apartado relativo a la instrucción CREATE TABLE.

Adición de una nueva columna:

ALTER ALTERTABLE TABLEEmpleados EmpleadosADD ADDCOLUMN COLUMNSalario SalarioCURRENCY; CURRENCY;



Modificación de tablas

que tener en cuenta que puede haber reglas de integridad definidas que impidan la eliminación de una columna de una tabla

 Hay

ALTER TABLE nombre_tabla DROP nombre_columna

Eliminación de una columna de la tabla

ALTER ALTERTABLE TABLEEmpleados EmpleadosDROP DROPCOLUMN COLUMNSalario; Salario;



Modificación de tablas

ALTER TABLE nombre_tabla ALTER COLUMN nombre_columna TO nuevo_nombre_columna tipo_datos

Modificación de una columna de la tabla (Interbase)

ALTER ALTERTABLE TABLEEmpleados EmpleadosALTER ALTERCOLUMN COLUMNNom NomCHAR(40); CHAR(40);

ALTER ALTERTABLE TABLEEmpleados EmpleadosALTER ALTERCOLUMN COLUMNNom NomTO TONombre NombreCHAR(25); CHAR(25);



Modificación de tablas



ALTER TABLE nombre_tabla ADD [CONSTRAINT nombre_restricción] {{PRIMARY KEY | UNIQUE} (col [, col …]) | FOREIGN KEY (col [, col …]) REFERENCES otra_tabla [(otra_col [,otra_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] | CHECK (condición)}

Creación de una Restricción

Modificación de tablas

ALTER TABLE nombre_tabla DROP CONSTRAINT nombre_restricción

Eliminación de una Restricción (tabla de índices de una clave principal, única o ajena):

ALTER ALTERTABLE TABLEEmpleados EmpleadosDROP DROPCONSTRAINT CONSTRAINTFK_Emple; FK_Emple;



Modificación de tablas



un índice sobre un campo se consigue mayor rendimiento en la ordenación y búsqueda sobre el mismo  Los índices van a ocupar espacio físico de almacenamiento en el sistema de archivos donde se ubica la BBDD.

 Con

Un índice es una estructura que proporciona un acceso rápido a las filas de una tabla en función a los valores de una o más columnas La presencia o ausencia de un índice es transparente al usuario de SQL sólo que:

Índices



El SGBD siempre creará un índice para la clave primaria de cada tabla

indexación también se considera más adecuada cuando la BBDD se emplea más para consultas que para la actualización de datos

 La

Será conveniente crear índices en aquellas columnas de una tabla que son utilizadas frecuentemente en condiciones de búsqueda y ordenación.

Índices

sintaxis básica es la siguiente:

CREATE [UNIQUE] [ASC | DESC] INDEX nombre_indice ON nom_tabla (col [, col …]);

 Su

Creación de índices:

CREATE CREATEUNIQUE UNIQUEINDEX INDEXSK_calle_num SK_calle_num ON ONBloqueCasas BloqueCasas(calle, (calle,numero) numero)WITH WITHDISALLOW DISALLOWNULL; NULL;



Índices

sintaxis básica es la siguiente:

DROP INDEX nombre_indice;

 Su

Eliminación de índices:

DROP DROPINDEX INDEXSK_calle_num SK_calle_numON ONBloqueCasas BloqueCasas;;

DROP DROPINDEX INDEXSK_calle_num; SK_calle_num;



Índices

Base de datos Catastro



CREATE TABLE BloqueCasas ( calle TEXT(30) NOT NULL, numero SHORT NOT NULL, metros_b SHORT, od_bloque LONG, CONSTRAINT PK_blo PRIMARY KEY (calle, numero), );  Para crear una relación 1 a 1 debemos definir laclave ajena de esta manera:  CREATE UNIQUE INDEX SK_calle_num ON BloqueCasas (calle, numero) WITH DISALLOW NULL;  ALTER TABLE BloqueCasas ADD CONSTRAINT FK_blo_vi FOREIGN KEY (calle, numero) REFERENCES Vivienda(calle, numero) ON DELETE CASCADE;

Definir una relación 1:1

Creación BD “SQL_LANS”

Esquema de la BD en SQL Server

CREATE TABLE CLIENTES ( NUM_CLIE INT EMPRESA CHAR(20) REP_CLIE INT LIMITE_CREDITO FLOAT );



CREATE DATABASE SQL_LANS; USE SQL_LANS;



NOT NULL, NOT NULL, NOT NULL, NOT NULL

Ejemplo de creación de una BD

CREATE TABLE PEDIDOS ( NUM_PEDIDO INT FECHA_PEDIDO DATETIME CLIE INT REP INT, FAB CHAR(3) PRODUCTO CHAR(5) CANT INT IMPORTE FLOAT );



NOT NULL, NOT NULL, NOT NULL, NOT NULL

NOT NULL, NOT NULL, NOT NULL,

NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL

CREATE TABLE OFICINAS ( OFICINA INT CIUDAD CHAR(15) REGION CHAR(10) DIR INT OBJETIVO FLOAT VENTAS FLOAT );



Ejemplo de creación de una BD

CREATE TABLE REPVENTAS ( NUM_EMPL INT NOT NULL, NOMBRE CHAR(15) NOT NULL, EDAD SMALLINT NOT NULL, OFICINA_REP INT NOT NULL, TITULO CHAR(10) NOT NULL, CONTRATO DATETIME NOT NULL, DIRECTOR INT NOT NULL, CUOTA FLOAT NOT NULL, VENTAS FLOAT NOT NULL );



NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL

CREATE TABLE PRODUCTOS ( ID_FAB CHAR(3) ID_PRODUCTO CHAR(5) DESCRIPCION CHAR(20) PRECIO FLOAT EXISTENCIAS INT );



Ejemplo de creación de una BD

ALTER TABLE Clientes ADD CONSTRAINT PK_Clie



ALTER TABLE Pedidos ADD CONSTRAINT PK_Pedidos PRIMARY KEY (NUM_PEDIDO);

ALTER TABLE Productos ADD CONSTRAINT PK_Productos PRIMARY KEY (ID_FAB, ID_PRODUCTO);

ALTER TABLE Repventas ADD CONSTRAINT PK_Repventas PRIMARY KEY (NUM_EMPL);



ALTER TABLE Oficinas ADD CONSTRAINT PK_Oficinas PRIMARY KEY (OFICINA);



PRIMARY KEY (NUM_CLIE);

USE SQL_LANS;



Definición de las claves principales



ALTER TABLE Clientes ADD CONSTRAINT FK_Clientes_RepVentas



ON DELETE CASCADE;

FOREIGN KEY (DIRECTOR) REFERENCES RepVentas(NUM_EMPL) ON DELETE SET NULL;

ALTER TABLE RepVentas ADD CONSTRAINT FK_Repventas_RepVentas

FOREIGN KEY (OFICINA_REP) REFERENCES Oficinas(OFICINA) ON DELETE SET NULL;

ALTER TABLE RepVentas ADD CONSTRAINT FK_Repventas_Oficinas

FOREIGN KEY (CLIE) REFERENCES Clientes(NUM_CLIE)

ALTER TABLE Pedidos ADD CONSTRAINT FK_Pedidos_Clientes

FOREIGN KEY (FAB,PRODUCTO) REFERENCES Productos(ID_FAB,ID_PRODUCTO) ON DELETE RESTRICT;

ALTER TABLE Pedidos ADD CONSTRAINT FK_Pedidos_Productos

FOREIGN KEY (REP) REFERENCES RepVentas(NUM_EMPL) ON DELETE SET NULL;

ALTER TABLE Pedidos ADD CONSTRAINT FK_Pedidos_Repventas

FOREIGN KEY (DIR) REFERENCES RepVentas(NUM_EMPL) ON DELETE SET NULL;

ALTER TABLE Oficinas ADD CONSTRAINT FK_Oficinas_RepVentas

FOREIGN KEY (REP_CLIE) REFERENCES RepVentas(NUM_EMPL) ON DELETE SET NULL;

USE SQL_LANS;



Definición de las claves ajenas



ALTER TABLE Clientes DROP CONSTRAINT FK_Clientes; ALTER TABLE Pedidos DROP CONSTRAINT FK_Pedidos_Repventas; ALTER TABLE Pedidos DROP CONSTRAINT FK_Pedidos_Productos; ALTER TABLE Pedidos DROP CONSTRAINT FK_Pedidos_Clientes; ALTER TABLE Repventas DROP CONSTRAINT FK_RepVentas_Oficinas; ALTER TABLE Repventas DROP CONSTRAINT FK_RepVentas_RepVentas;



#DROP INDEX FK_Clientes #DROP INDEX FK_Oficinas #DROP INDEX FK_Pedidos_Repventas #DROP INDEX FK_Pedidos_Productos #DROP INDEX FK_Pedidos_Clientes #DROP INDEX FK_RepVentas_Oficinas #DROP INDEX FK_RepVentas_RepVentas



ON Clientes; ON Oficinas; ON Pedidos; ON Pedidos; ON Pedidos; ON RepVentas; ON RepVentas;

USE SQL_LANS;



Borrar claves ajenas



DROP INDEX PK_Clie ON Clientes;  DROP INDEX PK_Oficinas ON Oficinas;  DROP INDEX PK_Pedidos ON Pedidos;  DROP INDEX PK_Productos ON Productos;  DROP INDEX PK_Repventas ON Repventas;

Borrar claves principales

USE SQL_LANS;

DROP TABLE CLIENTES;

DROP TABLE OFICINAS;

DROP TABLE PEDIDOS;

DROP TABLE PRODUCTOS;

DROP TABLE REPVENTAS;



Borrar tablas de la BD

Consultas simples



 ORDER

 HAVING

 GROUP

 WHERE

 FROM

 SELECT

La sentencia SELECT recupera datos de una base de datos y los devuelve en forma de una relación La sentencia SELECT puede llegar a constar de hasta seis cláusulas

Sentencia SELECT



la lista de tablas que contienen los datos a recuperar.  Los datos pueden proceder de más de una tabla

 Indica

FROM

los datos a recuperar.  Se pueden recuperar todas las columnas empleando el carácter asterisco (“*”).  Pueden ser datos contenidos en las tablas de la base de datos o bien especificarse operaciones para realizar cálculos (campos calculados).  Puede ser una constante. Ejemplo: ‘Total igual a ‘.

 Lista

SELECT

Sentencia SELECT



Se emplea para dar una condición de búsqueda para especificar las filas deseadas

Se emplea en consultas de resumen. Se verá en detalle más adelante

Indica una condición de selección solbre los grupos producidos por GROUP BY. Se verá en detalle más adelante



Permite ordenar los resultados de la consulta a través de una o de varias columnas, ascendente o descendentemente, a través de las cláusulas ASC (por defecto) y DESC

ORDER BY



HAVING



GROUP BY



WHERE

Sentencia SELECT



SELECT Apellidos, Nombre FROM Empleados WHERE Apellidos = 'King';

SELECT * FROM Pedidos WHERE Fecha_Envio = ´05/10/94’;

SELECT Id_Producto, Existencias FROM Productos WHERE Existencias <= Nuevo_Pedido;

SELECT Apellidos, Salario FROM Empleados WHERE Salario > 21000;

Algunos ejemplos de consultas SELECT

Sentencia SELECT



SELECT DISTINCT Nom_Proveedor FROM Pedidos;

 Ejemplo:

en las consultas de selección se incluye una clave primaria, el modelo relacional asegura que en los resultados no se van a producir duplicados.  Si no se incluye una clave primaria, se puede forzar la eliminación de duplicados incluyendo la cláusula DISCTINCT antes de la lista de campos.

 Si

Eliminación de duplicados:

Sentencia SELECT



la aplicación de la condición se pueden producir los siguientes resultados:

 En

Consta de la palabra clave WHERE seguida de una condición de búsqueda.  Conceptualmente, SQL recorre cada fila de la relación seleccionada y aplica a cada tupla la condición de búsqueda.

Cláusula WHERE

Si la condición de búsqueda da como resultado CIERTO la tupla se incluye en la relación resultado Si la condición de búsqueda da FALSO la tupla se excluye de la relación resultado Si la condición de búsqueda da QUIZÁS la tupla se excluye de la relación resultado

Cláusula WHERE





Test de comparación Test de rango Test de pertenencia a un conjunto Test de correspondencia a un patrón Test de valor nulo 70

Para realizar las búsquedas, SQL cuenta con distintos tipos de condiciones, que facilitan la expresividad y naturalidad en las selecciones Los predicados estándar son:

Cláusula WHERE



=, <>, <, <=, >, >=

Se emplean en él los operadores de comparación habituales



Comprueba si un dato se encuentra entre dos valores Se indica mediante el nombre de campo a comprobar, a continuación la palabra reservada BETWEEN, y los dos valores extremos del intervalo separados por la palabra AND. Ej: SELECT Nom, Dir FROM Empleados WHERE Sueldo BETWEEN 1000 AND 2000

TEST DE RANGO - BETWEEN



TEST DE COMPARACIÓN

Cláusula WHERE



Ejemplo: SELECT * FROM Pedidos WHERE Provincia In ('Madrid', 'Barcelona', 'Sevilla');

TEST DE PERTENENCIA A CONJUNTO – IN  Comprueba si un valor de dato coincide con uno de la lista de valores objetivo proporcionada.

Cláusula WHERE





Se emplea en la comparación de Cadenas de Caracteres Comprueba si el valor de la columna indicado se ajusta al patrón especificado Para especificar un patrón se emplean los caracteres comodín.

TEST DE CORRESPONDENCIA CON PATRÓN – LIKE

Cláusula WHERE





comodín ? de MS-DOS] 74

un único carácter, y sólo uno [Equivale el

_ (carácter de subrayado)  Corresponde con

comodín “*” de MS-DOS]

secuencia de cero o más caracteres [Equivale al

% (tanto por ciento)  Corresponde con una

Los caracteres comodín son los siguientes:

Cláusula WHERE



Consulta que muestra el código y el nombre de los empleados cuyo apellido empiece por “L”.  SELECT Codigo, Nombre FROM Empleados WHERE Apellidos LIKE ‘L%’;

Ejemplo de patrón LIKE







Ejemplo:

SELECT * FROM Empleados WHERE Sueldo IS NULL;

Esta comparación en realidad lo que hace es tratar de buscar un literal NULL que no es lo mismo que la falta de dato que indica el valor nulo del modelo relacional.

Para seleccionar las tuplas en las que cierto atributo toma un valor nulo hay que hacerlo mediante la comparación IS NULL Un error típico cometido en SQL es tratar de hacer una comparación del tipo CAMPO = NULL

TEST DE VALOR NULO – IS NULL

Cláusula WHERE

SELECT Apell, Nom FROM Empleados WHERE Sueldo>1000 AND Nom=‘Juan’;



Ejemplo:





AND (Y lógica) OR (O lógica) NOT (Negación lógica)

Para ello se pueden utilizar las palabras reservadas





Utilizando las reglas de la lógica se pueden combinar condiciones de selección simples para formar otras complejas.



Condiciones compuestas

Ejemplo: SELECT Nom, Apell FROM Empleados ORDER BY Apell, Nom; O bien: ORDER BY 2,1



Si no se especifica ninguna opción, los resultados devueltos por SQL quedarán ordenados por los índices definidos en las tablas. Se puede especificar el orden en que se desean recuperar los registros de las tablas mediante la cláusula: ORDER BY Lista de Campos [ASC | DESC] ORDER BY posición del campo en el SELECT



Ordenaciones



Por omisión SQL ordena los datos en secuencia ascendente (ASC) Para ordenar en secuencia descendente se puede emplear la palabra clave DESC Si se quiere realizar una ordenación en base a un campo calculado, como será un campo no presente en la BBDD, y por tanto no tiene nombre, se debe emplear el número de columna según aparece en la definición de la consulta

Ordenaciones



Consulta que muestra los empleados nacidos en 1990 con el sueldo aumentado un 20%. SELECT Nom, Apell, Sueldo*1,20 AS Nuevo_Sueldo FROM Empleados WHERE YEAR(Fecha_Nac)=1990 AND MONTH(Fecha_Nac)=6 AND Sueldo IS NOT NULL;

Consulta con expresión:



Consult a que nos muest r a el t ot al y la media de los sueldos de los empleados:  SELECT SUM(Sueldo) AS Tot al_ Sueldos, AVG(Sueldo) AS Media_ Sueldo FROM Empleados;  Podemos usar las siguient es f unciones de agr agado: SUM(), AVG(), MAX(), MI N(), 81 COUNT().

Consultas con funciones de agregado



Operación UNION del álgebra relacional



Las sentencias SELECT no pueden ser ordenadas, pero sí el resultado de la unión

Para ello será imprescindible que las relaciones origen sean compatibles Para realizar una unión, se utiliza la cláusula UNION intercalada entre dos sentencias SELECT



SQL permite realizar la unión de dos consultas

Uniones



Por omisión, en la unión de dos relaciones se eliminan las tuplas duplicadas. Si se quiere alterar el comportamiento normal de la unión, forzando el que no se eliminen los duplicados, se puede emplear la cláusula ALL inmediatamente después de la cláusula UNION

Uniones

яБо

SELECT ID_Fab, ID_Producto FROM Productos WHERE Precio > 2000.00 UNION [ALL] SELECT DISTINCT Fab, Producto FROM Pedidos WHERE Importe > 30000.00 ORDER BY 1,2;

Ejemplo de consulta union:

Consultas con dos o mรกs tablas

Consultas Multitabla





CLAVE PRIMARIA  CLAVE AJENA

SQL permite recuperar datos procedentes de dos o más tablas de una base de datos, generando nuevas relaciones Cuando se quiere recuperar datos de más de una tabla, lo habitual es que exista un nexo de unión entre ellas

Consultas Multitabla





CLAVE CLAVEPRIMARIA PRIMARIA == CLAVE CLAVEAJENA AJENA 87

Son las consultas multitabla fundamentales Recogen todas las tuplas formadas por la combinación de las tuplas de la primera tabla con las de la segunda, en los casos en los que el campo de combinación (CLAVE PRIMARIA –> CLAVE AJENA) tome valores idénticos en ambas partes, es decir:

Composiciones simples (combinación interna):

Consultas Multitabla

Además, para poder hacer la combinación, se igualarán en una condición del WHERE los campos CLAVE PRIMARIA y CLAVE AJENA

Este tipo de consultas se suele denominar también PADRE – HIJO, o MAESTRO – DETALLE.



La sintaxis básica es idéntica a la de las consultas de selección, en las que se indicarán los campos procedentes de cada tabla, y tras las cláusula FROM se nombrarán todas las tablas desde las que proceden



Consultas Multitabla

Del mismo modo, si existen varias columnas de emparejamiento (claves primaria y ajena formadas por múltiples campos), se pondrán todas, utilizando también la cláusula AND



Si además de incluir la condición de combinación se quieren añadir nuevas condiciones de selección, se puede hacer mediante el uso de AND



Consultas Multitabla

Con SQL también es posible emplear cualquier par de columnas para hacer una combinación, sin que estas guarden una relación PADRE – HIJO

Además se pueden generar combinaciones basadas en desigualdades, utilizando el resto de operadores de comparación.



Las técnicas empleadas para emparejar dos tablas se hacen extensibles a tres o más tablas



Consultas Multitabla



Para evitar el problema que esto supone, se pueden cualificar mediante: TABLA.CAMPO

Si además las tablas se nominan mediante ALIASES, se pueden cualificar los campos mediante los mismos: ALIAS.CAMPO



En consultas multitabla es habitual que haya coincidencias en los nombres de los campos



Nombres de campo cualificados:

Consultas Multitabla



Si se quiere hacer una selección de todos los campos de una de las tablas de la consulta, se puede realizar mediante SELECT NOM_TABLA.*



Si se utiliza SELECT * se seleccionarán todos los campos de todas las tablas implicadas en la consulta



Selección de todos los campos:

Consultas Multitabla

Pedidos.[ID de pedido], Clientes.[Nombre de la CompaĂąia], Clientes.[Ciudad] Pedidos.[Fecha de pedido] FROM Pedidos, Clientes WHERE Pedidos.[ID de cliente]=Clientes.[ID Cliente];

SELECT

Ejemplo consulta con dos tablas

SELECT Pedidos.[ID de pedido], Clientes.[Nombre de la CompaĂąia], Clientes.[Ciudad], Pedidos.[Fecha de pedido], [Precio unidad], Cantidad FROM Pedidos, Clientes,[Detalle de Pedido] WHERE Pedidos.[ID de cliente]=Clientes.[ID Cliente] AND Pedidos.[ID de Pedido] = [Detalle de Pedido].[ID pedido] AND Cantidad>3;

Ejemplo con tres tablas



Para ello se debe hacer uso de los alias de tabla, dando a cada una de las dos tablas emuladas un alias distinto

Para crear los alias basta con poner el nombre deseado detrás del nombre de cada tabla en la cláusula FROM



Para poder hacer una consulta Maestro – Detalle reflexiva hay que emular que los datos se encuentran distribuidos en dos tablas distintas en lugar de una



Relación Maestro – Detalle REFLEXIVA:

Consultas Multitabla



(0 ,n )

R IO S (1 )

R IO S

A fl u e n t e de

(0 ,n )

(0 ,1 )

A fl u e n t e de

(0 ,1 )

R IO S (2 )

Relación Maestro – Detalle REFLEXIVA:

Consultas Multitabla

FROM Rios, Rios AS Afluente_de WHERE Rios.Cod_Rio_Afluente = Afluente_de.Cod_Rio;

'es afluente de ', Afluente_de.Nom_Rio

SELECT Rios.Nom_Rio,

Ejemplo de reflexiva





cuando se realiza una consulta multitabla.

igualdad de campos en la cláusula WHERE,

SQL si no se indica ninguna condición de

Es la composición que hace por defecto

Producto Cartesiano:

Consultas Multitabla

(producto cartesiano), entonces:

todos los emparejamientos posibles

nombres de chicas y queremos sacar

nombres de chicos y otra tabla con

Ejemplo: Si tenemos una tabla con

ORDER BY 1;

FROM Chicos, Chicas

Chicos.Nombre

SELECT Chicas.Nombre,

ď Ž

Producto cartesiano



Esto es a lo que se llama COMBINACIÓN INTERNA

En los casos en que al comprobar la igualdad de los campos nexo da un valor QUIZAS, se omiten esas tuplas



100

SQL con las combinaciones que se han generado hasta el momento genera únicamente las tuplas que tienen coincidencia en valor en los campos nexo de las tablas implicadas



Combinación Interna

Consultas Multitabla



Combinación Externa Izquierda

Combinación Externa Derecha

Combinación Externa Simétrica



101

Se pueden dar los casos de:





Se puede especificar en SQL que incluyan las tuplas en los que la condición de igualdad de los campos nexo sea QUIZÁS



Combinación Externa

Consultas Multitabla

102

Se especifica mediante las cláusulas INNER JOIN y ON SELECT lista_campos FROM Tabla_1 INNER JOIN Tabla_2 ON Tabla_1.NEXO = Tabla_2.NEXO

COMBINACIÓN INTERNA





SQL 2 define una sintaxis específica para realizar las combinaciones internas y externas



Consultas Multitabla

103

Pedidos.[ID de pedido], Clientes.[Nombre de la Compañia], Clientes.[Ciudad] Pedidos.[Fecha de pedido] FROM Clientes INNER JOIN Pedidos ON Pedidos.[ID de cliente]=Clientes.[ID Cliente] WHERE Pedidos.[Fecha de pedido]<’12/12/1991’;

SELECT

Ejemplo combinación interna

104

SELECT Pedidos.[ID de pedido], Clientes.[Nombre de la CompaĂąia], Clientes.[Ciudad], Pedidos.[Fecha de pedido], [Precio unidad], Cantidad FROM Clientes INNER JOIN (Pedidos INNER JOIN [Detalle de Pedido] ON Pedidos.[ID de Pedido] = [Detalle de Pedido].[ID pedido] ) ON Pedidos.[ID de cliente]=Clientes.[ID Cliente] WHERE Cantidad>3;

Ejemplo combinaciĂłn con tres tablas



FULL:



Izquierda y derecha.

RIGHT: Derecha.



Izquierda.

LEFT:



Se especifica mediante las cláusulas [LEFT|RIGHT|FULL] OUTER JOIN y ON

105

SELECT lista_campos FROM Tabla_1 LEFT [OUTER] JOIN Tabla_2 ON Tabla_1.NEXO = Tabla_2.NEXO



COMBINACIÓN EXTERNA

Consultas Multitabla

106

Clientes.[Nombre de la Compañia], Clientes.[Ciudad], Pedidos.[ID de pedido], Pedidos.[Fecha de pedido] FROM Clientes LEFT JOIN Pedidos ON Pedidos.[ID de cliente]=Clientes.[ID Cliente];

SELECT

Ejemplo combinación externa por la izquierda

NULOS

Consultas Resumen

SQL permite resumir datos de la base de datos mediante un conjunto de funciones de columna.



108

Muchas peticiones de información no requieren un detalle de todos los registros implicados, sino una información resumida en base a la información total



Consultas Resumen





109

Distintos fabricantes suelen añadir en este campo muchas más funciones además de las estándar

Una función de columna SQL acepta una columna entera de datos como argumento, y produce un único resultado que resume toda la columna SQL estándar proporciona seis funciones de columna

Consultas Resumen





110

SUM() calcula el total de una columna AVG() calcula la media de una columna MIN() obtiene el valor mínimo de una columna MAX() obtiene el valor máximo de una columna COUNT() cuenta el número de valores de una columna COUNT(*) cuenta las filas de una consulta

Funciones de Columna Estándar:

Consultas Resumen





111

SELECT NOMBRE, SUM(NOTAS) FROM ALUMNOS

Ejemplo ilegal:

No se puede anidar funciones de columna No se puede mezclar funciones de columna y nombres de columna ordinarios en una lista de selección

Restricciones:

Consultas Resumen





112

Las funciones de columna aceptan una columna de valores como argumento y producen como resultado un resumen  Si alguno de los valores de la columna argumento toma el valor NULL, SQL lo ignorará en sus cálculos

Valores NULL

Consultas Resumen





113

Se puede hacer que el conjunto de valores de la columna pasada como argumento no contenga duplicados  Esto se hace anteponiendo la palabra clave DISTINCT justo delante del argumento de la función Ejemplo: SELECT COUNT(DISTINCT CIUDAD) FROM ALUMNOS

Eliminación de valores duplicados

Consultas Resumen







114

Puede haber varios campos de agrupación

Las consultas resumen vistas sólo producen una fila Con frecuencia es conveniente resumir los resultados no a nivel de total, sino a nivel de subtotales Esto se puede conseguir con SQL mediante la cláusula GROUP BY Una consulta agrupada constará al menos de dos campos: el de generación de grupos (de agrupación) y el resumen

Consultas Agrupadas – GROUP BY

Consultas Resumen





115

SQL seleccionará todas las filas que cumplan con los criterios de selección A continuación agrupará las columnas, de modo que se produzcan tramos en los que no varía el valor del campo de agrupación Para cada uno de estos tramos se aplicará la función de columna seleccionada, asociando el valor obtenido del cálculo al valor del campo de agrupación.

El funcionamiento de las consultas agrupadas se puede ver conceptualmente de la siguiente forma:

Consultas Resumen





No se pueden utilizar campos calculados para agrupar por ellos

Funciones de columna

Columnas de agrupación (afectadas por GROUP BY)

Una expresión que afecte a las combinaciones anteriores



116

Constantes



Los elementos de la lista de selección sólo pueden ser:



Las columnas de agrupación deben ser columnas efectivas

Restricciones de las consultas agrupadas:

Consultas Resumen



117

Una vez calculados los grupos, se pueden aceptar o rechazar mediante el uso de la cláusula HAVING, que se incluirá tras la especificación del GROUP BY



Ejemplo: SELECT Id_Familia, Sum(Stock) FROM Productos GROUP BY Id_Familia HAVING Sum(Stock) > 100

Funciona de manera similar a la cláusula WHERE, aunque ésta lo hace sobre filas individuales



Condiciones de selección de grupos – HAVING

Consultas Resumen



Resultadousando usando Resultado cláusulaHAVING HAVING lalacláusula

118

Resultadosin sinusar usar Resultado cláusulaHAVING HAVING lalacláusula

SELECT Grupo, SUM(Cuota) AS Suma_Cuota FROM Alumnos Solo tendrá en cuenta para WHERE [¿HA PAGADO?]=TRUE agrupar y sumar los registros que GROUP BY Grupo cumplan la HAVING AVG(Cuota)>6500; condición.

El atributo debe estar en GROUP BY

Ejemplo Consulta Resumen



FROM Oficinas, RepVentas WHERE Oficina = Oficina_Rep GROUP BY Ciudad HAVING COUNT(*) >=2; 119

SUM(RepVentas.Ventas) AS Suma_Ventas

SELECT Ciudad, SUM(Cuota) AS Suma_Cuotas,

Por cada oficina con dos o más personas, calcular la cuota total y las ventas totales para todos los vendedores que trabajan en la oficina. (Agrupar por Ciudad siempre que haya dos o más representantes).

Ejemplo Consulta Resumen con dos tablas

Consultas anidadas

Subconsultas

Una subconsulta es una consulta que aparece dentro de la cláusula WHERE o de la cláusula HAVING



121

SQL permite utilizar los datos obtenidos como resultado de una consulta como parte de otra



Subconsultas



122

Es aconsejable emplear nombres de columna cualificados

Los nombres de columna de la subconsulta pueden hacer referencia a la consulta principal





No puede ser la UNION de varias SELECT



Su lista de selección contiene un único elemento

No se puede especificar la cláusula ORDER BY en la subconsulta



La subconsulta debe producir una única columna de datos resultado



Restricciones de las subconsultas:

Subconsultas



En el cuerpo de una subconsulta es habitual tener que referirse al valor de una columna en la “fila actual” de la consulta principal.

Oficinas.Oficina); 123

Ejemplo: SELECT Oficinas.Ciudad FROM Oficinas WHERE Objetivo > (SELECT SUM(RepVentas.Cuota) FROM RepVentas WHERE Repventas.Oficina_Rep =



Referencias Externas:

Subconsultas

Conforme SQL recorre cada fila de la tabla OFICINAS, utiliza el valor Oficinas.Oficina de la fila actual como argumento para resolver la condición WHERE de la subconsulta



124

En el ejemplo anterior, en la subconsulta se hace referencia al campo Oficinas.Oficina de la consulta prinicipal.



Subconsultas



Test de pertenencia a un conjunto subconsulta

Test de existencia

Test de comparación cuantificada



125

Test de comparación subconsulta

Los tipos de condiciones de subconsultas son:





Una subconsulta siempre forma parte de una condición WHERE o HAVING



Condiciones de búsqueda en subconsultas:

Subconsultas



Compara el valor de una expresión con un valor único producido por la subconsulta

Si la subconsulta no produce filas o produce un valor NULL, el test de comparación devuelve NULL



126

Hace uso de los operadores típicos de comparación: (=,<,>,<=,>=,<>).



Test de comparación subconsulta:

Subconsultas

SELECT * FROM Productos WHERE PrecioUnidad > (SELECT AVG(PrecioUnidad) FROM Detalles de pedidos WHERE Descuento >= .25);

Importante: Importante: Si Sien enuna unaconsulta consultautilizamos utilizamosen enla lacláusula cláusula WHERE WHEREcualquiera cualquierade delos losseis seisoperadores operadores(=,<,>,<=,>=,<>), (=,<,>,<=,>=,<>), para paracomparar compararcon conuna unasubconsulta, subconsulta, yyno noutilizamos utilizamos ninguno ninguno de delos losoperadores operadoresANY, ANY,ALL, ALL, IN, IN,EXIST, EXIST,solamente solamente comparará compararácon conun un valor, valor,que queserá seráel elque queobtengamos obtengamos de dela la subconsulta subconsulta(nunca (nuncacon convarios). varios). 127



Ejemplo:



Se utiliza cuando se necesita comparar un valor de la fila que se está examinando con un conjunto de valores producidos por una subconsulta

El formato es prácticamente igual al de test de conjunto simple, solo que el conjunto en lugar de declararse explícitamente se obtiene por medio de una subconsulta



128

Compara un único valor de datos con una columna de valores producida por una subconsulta



Test de pertenencia a conjunto – IN

Subconsultas

SELECT * FROM Productos WHERE IdProducto IN (SELECT IdProducto FROM Detalles de pedidos WHERE Descuento >= .25);

Además, Además,puede puedeutilizar utilizarNOT NOTIN INpara pararecuperar recuperarsolamente solamenteaquellos aquellos registros registrosde delalaconsulta consultaprincipal principalpara paralos losque queningún ningúnregistro registrode delala subconsulta subconsultacontenga contengaun unvalor valorigual. igual. 129



El siguiente ejemplo devuelve todos los productos con descuento del 25 por ciento o mayor.

Utilice el predicado IN para recuperar sólo aquellos registros de la consulta principal para los cuales algún registro de la subconsulta contenga un valor igual.

Ejemplo:



No hay ninguna equivalencia en consultas simples, sólo se usa en subconsultas



130

productos para los cuales existe al menos un pedido en el que esté el producto en cuestión, de modo que el pedido tiene un importe de al menos 25.000 €

Listar los productos para los cuales se ha recibido un pedido de 25.000 € ó más  Listar los

Ejemplo:

Comprueba si una subconsulta produce alguna fila de resultados



Test de existencia – EXISTS

Subconsultas

131

Utilice Utiliceel elpredicado predicadoEXISTS EXISTS(con (conla lapalabra palabrareservada reservadaopcional opcionalNOT) NOT)en en comparaciones comparacionesverdadero/falso verdadero/falsopara paradeterminar determinarsisila lasubconsulta subconsultadevuelve devuelve algĂşn algĂşnregistro. registro.

SELECT Descripcion FROM Productos WHERE EXISTS (SELECT * FROM Pedidos WHERE Producto = ID_producto AND Fab = Id_Fab AND Importe >= 25000);

Ejemplo:





La subconsulta contiene una columna con un conjunto de valores.

Si el conjunto de valores no es vacío, el test de existencia EXISTS devuelve TRUE, y por tanto la tupla de la relación de la consulta principal queda seleccionada



132

SQL recorre la relación de la consulta principal y para cada tupla de la misma realiza una subconsulta.



Conceptualmente el funcionamiento es:

Test de existencia – EXISTS

Subconsultas



Con los test cuantificados ANY y ALL extienden el test de conjunto IN a los operadores de comparación, tales como > y <.

Se utilizan conjuntamente con uno de los seis operadores de comparación, para comparar un único valor de test con una columna de valores, producida por la subconsulta

Si alguna de las comparaciones individuales devuelve TRUE, el test ANY devuelve TRUE y la fila de la consulta principal queda seleccionada



133

Similar al test de conjunto IN



Tests cuantificados – ANY

Subconsultas

SELECT * FROM Productos WHERE PrecioUnidad > ANY (SELECT PrecioUnidad FROM Detalles_pedido WHERE Descuento >= .25);

Utilice Utilicelos lospredicados predicadosANY ANYooSOME, SOME,que queson sonsinónimos, sinónimos,para pararecuperar recuperar registros registrosde delalaconsulta consultaprincipal principalque quesatisfagan satisfaganla lacomparación comparacióncon conotros otros registros registrosrecuperados recuperadosen enlalasubconsulta. subconsulta.Es Esdecir decirsi sialguna algunade delas las comparaciones comparacionesindividuales individualesproduce produceun unvalor valorVERDADERO, VERDADERO,el eltest testANY ANY devuelve devuelveel elresultado resultadoVERDAD VERDAD(TRUE). (TRUE).El Eltest testANY ANYse sepuede puedeutilizar utilizarcon conlos los operadores operadores(=,<,>,<=,>=,<>). (=,<,>,<=,>=,<>). 134



Elsiguiente siguienteejemplo ejemplodevuelve devuelvetodos todoslos losproductos productoscuyo cuyoprecio preciopor porunidad unidad El esmayor mayorque quecualquier cualquierproducto productovendido vendidocon conun undescuento descuentodel del25 25por por es cientooomayor: mayor: ciento

Ejemplo de Tests ANY o SOME





La subconsulta genera una columna de datos, los cuales son comparados, según el operador de comparación seleccionado, uno a uno con el dato de la tupla actual de la consulta principal

Si alguna de las comparaciones da TRUE, la tupla de la relación principal es seleccionada.



135

La consulta principal genera una relación de datos, que es recorrida tupla a tupla



Conceptualmente:

Tests cuantificados – ANY

Subconsultas



Esta táctica elimina errores de interpretación, ya que el uso del test ANY puede llegar a se un tanto oscuro



136

Siempre se puede transformar una consulta con test ANY en una consulta con test EXISTS, trasladando la comparación al interior de la condición de búsqueda de la subconsulta



Tests cuantificados – ANY

Subconsultas

137

SELECT SELECT** FROM FROM Productos Productos WHERE WHEREEXISTS EXISTS (SELECT (SELECTPrecioUnidad PrecioUnidad FROM FROM Detalles_pedido Detalles_pedido WHERE WHEREDescuento Descuento>= >= .25 .25AND AND Productos.PrecioUnidad>Detalles_pedido.PrecioUnidad); Productos.PrecioUnidad>Detalles_pedido.PrecioUnidad);

Ejemplo con EXISTS



Si todas de las comparaciones individuales devuelve TRUE, el test ALL devuelve TRUE y la fila de la consulta principal queda seleccionada

Conceptualmente funciona igual que el test ANY, salvo la necesidad que el valor comparado de la consulta principal tiene que dar TRUE con cada comparación con los datos obtenidos en la subconsulta



138

El funcionamiento del test ALL es similar al del test ANY



Tests cuantificados – ALL

Subconsultas

 Si Sicambia cambiaANY ANYaaALL ALLen enel elejemplo ejemploanterior, anterior,la laconsulta consultadevolvería devolveríasólo sólo aquellos aquellosproductos productoscuyo cuyoprecio preciopor porunidad unidadfuese fuesemayor mayorque queelelde detodos todos los losproductos productosvendidos vendidoscon conun undescuento descuentodel del25 25por porciento cientooomayor. mayor. Esto 139 Estoes esmucho muchomás másrestrictivo. restrictivo.

 Utilice Utiliceelelpredicado predicadoALL ALLpara pararecuperar recuperarsólo sólolos losregistros registrosde dela laconsulta consulta principal principalque quesatisfagan satisfaganlalacomparación comparacióncon contodos todoslos losregistros registros recuperados recuperadosen enlalasubconsulta. subconsulta.

WHERE WHERE PrecioUnidad PrecioUnidad >> ALL ALL (SELECT (SELECT PrecioUnidad PrecioUnidad FROM FROM Detalles_pedido Detalles_pedido WHERE WHERE Descuento Descuento >= >= .25); .25);

  SELECT SELECT ** FROM FROM Productos Productos

Ejemplo de Tests ALL

Generalmente se obtiene mayor rendimiento en una composición que con el uso de las subconsultas.



140

En algunos SGBD la única diferencia que se encontrará será la diferencia de rendimiento entre unas y otras.

En esos casos, las dos soluciones son correctas





Muchas consultas realizadas mediante subconsultas se pueden obtener también mediante consultas multitabla.



Subconsultas y Composiciones

Esto es generalizable a los niveles que sea necesario



SELECT SELECTEmpresa Empresa FROM FROM Clientes Clientes WHERE WHERERep_Clie Rep_ClieIN IN (SELECT (SELECTNum_Empl Num_Empl FROM FROM RepVentas RepVentas WHERE WHEREOficina_Rep Oficina_RepIN IN (SELECT (SELECTOficina Oficina FROM FROMOficinas Oficinas WHERE WHERERegion Region=='Este')); 'Este'));

141

Lista Listalos losclientes clientes(Empresa) (Empresa)cuyos cuyosvendedores vendedoresestán estánasignados asignadosaaoficinas oficinas de dela laregión regiónde deventas ventasEste. Este.

Del mismo modo que una consulta principal puede hacer uso de una subconsulta, una subconsulta puede hacer uso al mismo tiempo de otra nueva subconsulta



Subconsultas Anidadas



142

Finalmente Finalmentela laconsulta consultamás másexterna: externa: SELECT SELECTEmpresa Empresa FROM FROMClientes Clientes WHERE WHERERep_Clie Rep_ClieIN IN(Subconsulta) (Subconsulta) Mostrará Mostrarálos losclientes clientes(Empresa) (Empresa)cuyos cuyosvendedores vendedorestienen tienen uno unode delos losnúmeros númerosde deempleados empleadosseleccionados. seleccionados.

SELECT SELECTNum_Empl Num_EmplFROM FROMRepVentas RepVentas WHERE WHEREOficina_rep Oficina_repIN IN(subconsulta) (subconsulta) No Nodevuelve devuelveuna unacolumna columnaque quecontiene contienelos losnúmeros númerosde deempleados empleadosde de los losvendedores vendedoresque quetrabajan trabajanen enuna unade delas lasoficinas oficinasseleccionadas. seleccionadas.

La Lasubconsulta subconsultasiguiente: siguiente:

Nos Nosdevuelve devuelveuna unauna unacolumna columnaque quecontiene contienelos losnúmeros númerosde deoficina oficina de delas lasoficinas oficinasde delalaregión regiónEste. Este.

SELECT SELECTOficina OficinaFROM FROMOficinas OficinasWHERE WHERERegión Región==‘Este’; ‘Este’;

En Eneste esteejemplo ejemplolalasubconsulta subconsultamás másinterna: interna:

Pasos para realizar la consulta anterior:

Esta optimización no se puede realizar si la subconsulta contiene referencias externas - CORRELACIÓN



143

Cuando esto es así, las implementaciones comerciales de servidores de BBDD tratan de evitar el repetir el cálculo de la subconsulta, para incrementar el rendimiento

Para muchas subconsultas, se producen los mismos resultados





Conceptualmente, SQL realiza una subconsulta tantas veces como tuplas existen en la relación obtenida por la consulta principal



Subconsultas Correlacionadas

Cuando una subconsulta aparece en la cláusula HAVING, funciona como parte de la selección de grupo de filas efectuada por la cláusula HAVING.

Además se pueden utilizar las subconsultas en las sentencias INSERT, DELETE, UPDATE como veremos más adelante.



144

Además de utilizar las consultas en la cláusula WHERE se pueden emplear igualmente en la cláusula HAVING



Subconsultas en HAVING

INSERT, DELETE, UPDATE

Actualizaciones de bases de datos



los datos de la BBDD

 Crear



la propia BBDD 146

Altas, Bajas y Modificaciones  El SGBD velará por la integridad de los datos, además de coordinar la simultaneidad de peticiones por parte de los usuarios

 Actualizar

Con SQL, además de poder realizar consultas para obtener resultados a partir de los datos incluidos en la BBDD, permite:

Actualizaciones de BBDD



147

Sentencias INSERT de SQL para añadir una fila  Sentencias INSERT de SQL para añadir varias filas  Utilidades de carga masiva de datos en la BBDD

en incluir nuevos registros de datos en las tablas de la BBDD  Los SGBD incluyen para ello:

 Consiste

ALTAS:

Introducción de datos en la BBDD

INSERT INTO Tabla (campo1, .., campoN) VALUES (valor1, ..., valorN)

Su sintaxis es la siguiente:

148

VALUES VALUES(112,’Henry (112,’HenryFord’, Ford’,46, 46,13, 13,‘Dir ‘DirVentas’, Ventas’,‘25/08/90’, ‘25/08/90’,NULL, NULL,NULL, NULL,0.0); 0.0);

( (Num_Empl, Num_Empl,Nombre, Nombre,Edad, Edad,Oficina_Rep, Oficina_Rep,Titulo, Titulo,Contrato, Contrato,Director, Director,Cuota, Cuota,Ventas Ventas) )

INSERT INSERTINTO INTORepVentas RepVentas



Sentencia INSERT de una fila



149

automáticamente inserta valores NULL a aquellas columnas no incluidas en una sentencia INSERT, a menos que esas columnas tengan definido un valor por defecto en la creación de la tabla  Se puede hacer explícitamente mediante la palabra clave NULL en el área VALUES

 SQL

Inserción de valores NULL:

Sentencia INSERT de una fila

se pretende insertar valores en todas las columnas de una tabla, SQL permite omitir la lista de los nombres de columna.  SQL supondrá que se insertan valores, tomando las columnas en el orden en el que se definieron

 Si

Inserción de todas las columnas

150

INSERT INSERTINTO INTORepVentas RepVentas VALUES VALUES(112,’Henry (112,’HenryFord’, Ford’,46, 46,13, 13,‘Dir ‘DirVentas’, Ventas’,‘25/08/90’, ‘25/08/90’,NULL, NULL,NULL, NULL,0.0); 0.0);



Sentencia INSERT de una fila

151

SELECT SELECT Num_Pedido, Num_Pedido,Fecha_Pedido, Fecha_Pedido,Importe Importe INTO INTO AntPedidos AntPedidos FROM FROM Pedidos Pedidos WHERE WHERE Fecha_Pedido Fecha_Pedido >= >= ‘01/01/90’; ‘01/01/90’;

Crear Crearla latabla tablaAntPedidos AntPedidoscon conlos loscampos camposNum_pedido, Num_pedido, Fecha_Pedido, Fecha_Pedido,eeImporte, Importe,para paraaquellos aquellospedidos pedidoscuya cuyafecha fechade de pedido pedidosea seaigual igualoosuperior superioralalprimer primerdía díadel delaño año1990. 1990.

Crear una tabla y añadir registros



152

Permite añadir múltiples filas desde un origen de datos a una tabla destino (la tabla debe de estar creada).  No se especifican los valores a insertar, sino que estos se obtienen a partir de la ejecución de una sentencia SELECT

Sentencia INSERT multifila



153

INSERT INTO Tabla (campo1, …, campoN) SELECT TablaOrigenX.campo1, …, TablaOrigenZ.campoN FROM TablaOrigenX, …, TablaOrigenZ WHERE Condiciones_de_Selección

Su sintaxis básica es la siguiente:

Sentencia INSERT multifila

154

INSERT INSERT INTO INTO AntPedidos AntPedidos (( Num_Pedido, Num_Pedido, Fecha_Pedido, Fecha_Pedido, Importe Importe )) SELECT SELECT Num_Pedido, Num_Pedido, Fecha_Pedido, Fecha_Pedido, Importe Importe FROM FROM Pedidos Pedidos WHERE WHERE Fecha_Pedido Fecha_Pedido << ‘01/01/90’; ‘01/01/90’;

Añadir Añadiraalalatabla tabla AntPedidos, AntPedidos,aquellos aquellosde delalatabla tablapedidos pedidoscuya cuyafecha fechade de pedido pedidosea seamenor menoralal01-01-90 01-01-90

Ejemplo:



155

Hay que tener en cuenta que un borrado en una tabla puede desencadenar borrados en cascada en otras tantas

Si se pretenden eliminar todos los registros de una tabla, se prescindirá de la condición expresada en la cláusula WHERE

DELETE FROM Tabla WHERE criterio  DELETE FROM Pedidos WHERE Fecha_Pedido < ‘15/11/89’;

Se realiza mediante la sentencia DELETE Su sintaxis básica es la siguiente:

Supresión de datos de la BBDD



156

ocasiones los borrados de datos se deben efectuar en base a datos contenidos en otras tablas  SQL no permite utilizar composiciones en sentencias DELETE  El modo de solucionar esta situación es por medio de subconsultas

 En

Borrados con Subconsultas

Supresión de datos de la BBDD

157

DELETE DELETE FROM FROM Pedidos Pedidos WHERE WHERE Rep Rep == (SELECT (SELECT Num_Empl Num_Empl FROM FROM RepVentas RepVentas WHERE WHERE Nombre= Nombre='Sue 'SueSmith' Smith'); );

Suprime Suprimelos lospedidos pedidosaceptados aceptadospor porSue SueSmith. Smith.

Ejemplo de borrado con subconsulta:



158

Si se pretende modificar todos los registros de una tabla, se prescindirá de la cláusula WHERE

UPDATE Tabla SET Campo1=Valor1, …, CampoN=ValorN WHERE Criterio;

Se realiza mediante la sentencia UPDATE  Su sintaxis básica es la siguiente:

Modificación de datos en la BBDD

159

UPDATE UPDATE RepVentas RepVentas SET SET Oficina_Rep Oficina_Rep == 11, 11, Cuota Cuota == 0.9 0.9 *Cuota *Cuota WHERE WHERE Oficina_Rep Oficina_Rep == 12; 12;

Cambia Cambiaaatodos todoslos losvendedores vendedoresde delalaoficina oficinade deChicago Chicago(número (número12) 12)aala la oficina oficinade deNew NewYork York(número (número11) 11)yyrebaja rebajasus suscuotas cuotasun un10%. 10%.

Ejemplo de UPDATE



160

ocasiones las modificaciones de datos se deben efectuar en base a datos contenidos en otras tablas  SQL no permite utilizar composiciones en sentencias UPDATE  El modo de solucionar esta situación es por medio de subconsultas

 En

Modificaciones con Subconsultas

Modificación de datos en la BBDD

Tablas virtuales

Vistas



162

permite acceder a los resultados de invocar a la ‘vista’ como si se tratase de una tabla de la BBDD

 SQL

Una vista es una consulta realizada en SQL, cuya definición está permanentemente almacenada en la BBDD, y que tiene además un nombre asignado

Vistas



163

acomodar el aspecto de una BBDD, de modo que distintos usuarios la vean desde diferentes perspectivas  Permiten restringir el acceso a los datos, permitiendo que diferentes usuarios sólo vean ciertas filas o columnas  Simplifican el acceso a la BBDD mediante la presentación de la estructura de los datos almacenados de manera más natural

 Permiten

Razones para utilizar las vistas:

Vistas



164

datos que muestra una vista no existe en la base de datos como un conjunto almacenado de valores, sino que son los resultados producidos de la ejecución de una consulta que define a la vista.  Una vez definida la vista, se puede ejecutar una sentencia SELECT sobre ella, como si se tratase de una tabla

 Los

Una vista es una “tabla virtual” en la BBDD, cuyos contenidos están definidos por una consulta

Vistas



165

sintaxis básica es: CREATE VIEW nombre_vista [(col_vista [,col_vista …])] AS SELECT … Siendo SELECT una sentencia de selección completa

Su

Se realiza mediante la sentencia CREATE VIEW

Creación de una Vista

166

(Cod_Oficina, (Cod_Oficina, Ciudad, Ciudad, Region) Region) AS AS SELECT SELECT OFICINA, OFICINA, CIUDAD, CIUDAD, REGION REGION FROM FROM Oficinas Oficinas WHERE WHERE Region Region == 'Este'; 'Este';

  CREATE CREATE VIEW VIEW OficinasEste OficinasEste

Define Defineuna unavista vistaque quecontenga contengaúnicamente únicamentelas lasoficinas oficinasde delalaregión región“Este”, “Este”, cuyo cuyonombre nombresea seaOficinasEste. OficinasEste.

Ejemplo:





167

La consulta para la definición de una vista puede incluir la cláusula GROUP BY En este caso, las vistas que se definen se denominan Vistas Agrupadas Una vista agrupada reúne los resultados de una consulta agrupada en una tabla virtual, permitiendo efectuar consultas adicionales sobre ella.

Vistas Agrupadas:

Vistas







168

Por ejemplo, se podría calcular MIN(AVG(A))  No todos los SGBD tienen esta capacidad

Como restricción, las vistas agrupadas no son actualizables Sin embargo, en los últimos SGBD, las vistas agrupadas pueden salvar la limitación que impone SQL sobre la anidación de funciones

Vistas Agrupadas:

Vistas





169

Una de las razones más frecuentes para utilizar vistas compuestas es simplificar las consultas multitabla. Se puede crear una vista compuesta que extrae sus datos de varias tablas, presentando los datos como si se tratase de una única tabla virtual

Vistas Compuestas:

Vistas





Hay vistas que pueden soportar algunas de las operaciones de actualización, y otras no Hay vistas que pueden soportar actualizaciones sobre un subconjunto de sus columnas

170

Las reglas de actualización de vistas dependen de cada SGBD.



Las reglas que impone SQL sobre las actualizaciones de vista son muy restrictivas Los productos comerciales suelen relajar estas reglas, ya que en muchos casos se pueden permitir ciertas actualizaciones que el estándar impediría.

Actualizaciones sobre vistas:

Vistas





171

Una vista actualizable puede incluir una condición WHERE Si se hace una inserción de un dato sobre una vista de este tipo, quizás el dato insertado no cumpla con la condición de selección, y por tanto después no aparecerá en la vista Esto se puede evitar si en la definición de la vista se incluye al final la cláusula WITH CHECK OPTION

Comprobación de actualizaciones sobre vistas – WITH CHECK OPTION

Vistas





172

DROP DROP VIEW VIEW NombreVista NombreVista

La sintaxis básica de la eliminación de una vista es la siguiente:

Eliminación de una vista:

Vistas

Disparadores

Triggers



 Modificaciones

 Borrados

 Inserciones

174

Un trigger o disparador es una respuesta a un evento que se produce en una BBDD, llevando a un cambio en la información contenida en la misma Los eventos que se producen en una BBDD son por tanto:

Triggers



175

En muchos SGBD se pueden llevar a cabo acciones tanto antes como después de que se lleven a cabo las acciones de inserción, modificación o borrado  Cada SGBD incorpora su propio dialecto de descripción de triggers

Triggers



La columna VENTAS del vendedor que aceptó el pedido debería incrementarse en el importe del pedido. El valor de EXISTENCIAS para ese producto debería disminuir en la cantidad solicitada.

176

Definiendo un disparador (trigger) de nombre NUEVOPEDIDO, haremos que estas actualizaciones de la Base de Datos se produzcan automáticamente.



Ejemplo de la Base de Datos SQL LANS Cuando añadimos un pedido a la tabla PEDIDOS, estos dos cambios también podrían tener lugar en la base de datos:

Desencadenadores o Triggers

Nombre del disparador

177

CREATE CREATE TRIGGER TRIGGER NuevoPedido NuevoPedido Tabla sobre la que actua ON Pedidos ON Pedidos FOR FOR INSERT INSERT Se activarรก cuando insertamos un AS registro en la tabla Pedidos. AS BEGIN BEGIN Importe insertado en UPDATE RepVentas Pedidos UPDATE RepVentas SET SET Ventas Ventas == Ventas Ventas ++ INSERTED.Importe INSERTED.Importe insertada en WHERE WHERE RepVentas.Num_Empl RepVentas.Num_Empl == INSERTED.Rep; INSERTED.Rep; Cantidad Pedidos UPDATE Productos UPDATE Productos SET SET Existencias Existencias == Existencias Existencias -- INSERTED.Cant INSERTED.Cant WHERE WHERE Productos.Id_Fab Productos.Id_Fab == INSERTED.Fab INSERTED.Fab AND AND Productos.Id_Producto Productos.Id_Producto == INSERTED.Producto; INSERTED.Producto; END; END;

Triggers - SQL SERVER

INSERT INTO Pedidos



178

NOTA: Para ver los TRIGGERS en SQL SERVER. Iremos al menú y seleccionaremos el programa: Inicio->Programas->SQL Server->Administrador Corporativo y en la base de datos SQL_LANS nos colocamos en la tabla Pedidos y al pulsar el botón derecho, si elegimos la opción Diseño tabla, en la lista de botones de arriba aparecerá un botón de desencadenadores, el cual, al pulsarlo nos saldrá el código del desencadenador.

VALUES (222222,'12/02/1990',2111,103,'ACI','4100X',2,100)

(NUM_PEDIDO,FECHA_PEDIDO, CLIE,REP,FAB, PRODUCTO, CANT, IMPORTE)



Para probar el disparador anterior

Este Estedisparador disparadorfuerza fuerzala laintegridad integridadreferencial referencialde dela larelación relación OFICINAS/REPVENTAS, OFICINAS/REPVENTAS, yytambién tambiénmuestra muestraun unmensaje mensaje cuando cuandouna unaactualización actualizaciónfalla. falla.

179

Cuando hay una inserción o CREATE CREATETRIGGER TRIGGERActualiza_rep Actualiza_rep un actualización en la tabla ON ONRepVentas RepVentas Repventas comprueba que el FOR FORINSERT, INSERT,UPDATE UPDATE nº de oficina insertado sea AS AS válido IF ((SELECT COUNT(*) IF ((SELECT COUNT(*) FROM FROMOFICINAS, OFICINAS,INSERTED INSERTED WHERE WHEREOFICINAS.OFICINA=INSERTED.OFICINA_REP)=0) OFICINAS.OFICINA=INSERTED.OFICINA_REP)=0) BEGIN BEGIN PRINT PRINT'Especificado 'Especificadoun unnumero numerode deoficina oficinainvalido' invalido' ROLLBACK ROLLBACKTRANSACTION TRANSACTION END END



Triggers – Ejemplo integridad referencial



180

Hay algunos SGBD que no soportan la actualización en cascada si se modifica un valor de la clave primaria. Si embargo, esto se puede realizar con disparadores. Por ejemplo: El siguiente disparador propaga en cascada cualquier actualización de la columna OFICINA en la tabla OFICINAS a la columna OFICINA_REP de la tabla REP_VENTAS:

Ejemplo borrado en cascada

181

Las Lasreferencias referenciasDELETED.OFICINA DELETED.OFICINAeeINSERTED.OFICINA INSERTED.OFICINAen enel eldisparador disparador se se refieren, refieren,respectivamente, respectivamente,aalos losvalores valoresde dela lacolumna columnaOFICINA OFICINAantes antes yydespués despuésde dela lasentencia sentenciaUPDATE. UPDATE.

CREATE CREATETRIGGER TRIGGERCambia_Oficina_Rep Cambia_Oficina_Rep ON ONOficinas Oficinas FOR FORUPDATE UPDATE AS AS IF IFUPDATE UPDATE(OFICINA) (OFICINA) BEGIN BEGIN UPDATE UPDATEREPVENTAS REPVENTAS SET SETREPVENTAS.OFICINA_REP=INSERTED.OFICINA REPVENTAS.OFICINA_REP=INSERTED.OFICINA FROM FROMREPVENTAS, REPVENTAS,INSERTED, INSERTED,DELETED DELETED WHERE WHEREREPVENTAS.OFICINA_REP=DELETED.OFICINA REPVENTAS.OFICINA_REP=DELETED.OFICINA END END

Ejemplo borrado en cascada

Transacciones





183

BEGIN TRANSACTION Para empezar la transacción END TRANSACTION Para finalizar la transacción SAVE TRANSACTION Para realizar un punto de Salvaguardia COMMIT TRANSACTION ó WORK señala el final correcto de una transacción. Informa al SGBD que la transacción está ahora completa; todas las sentencias que forman la transacción han sido ejecutadas y la B.D. es autoconsistente. ROLLBACK señala el final sin éxito de una transacción. Informa al SGBD que el usuario no desea completar la transacción ; en vez de ello el SGBD debe deshacer los cambios efectuados a la B.D. durante la transacción, para que la B.D. quede en el estado en que estaba antes de ejecutar la transacción

Una transacción es una secuencia de una o más sentencias SQL que juntas forman una unidad de trabajo. En las transacciones podemos usar las sentencias:

Transacciones

COMMIT COMMIT WORK WORK

184

UPDATE UPDATEPEDIDOS PEDIDOS SET SETCANTIDAD CANTIDAD==10, 10,IMPORTE IMPORTE=3550.00 =3550.00 WHERE WHERENUM_PEDIDO NUM_PEDIDO==113051 113051 UPDATE UPDATEREPVENTAS REPVENTAS SET SETVENTAS VENTAS==VENTAS VENTAS--1458.00 1458.00++3550.00 3550.00 WHERE WHERENUM_EMPL NUM_EMPL==108 108 UPDATE UPDATEOFICINAS OFICINAS SET SETVENTAS VENTAS==VENTAS VENTAS--1458.00 1458.00++3550.00 3550.00 WHERE WHEREOFICINA OFICINA==21 21 UPDATE UPDATEPRODUCTOS PRODUCTOS SET SETEXISTENCIAS EXISTENCIAS==EXISTENCIAS EXISTENCIAS++44-10 -10 WHERE WHEREID_FAB ID_FAB=='QSA' 'QSA' AND ANDID_PRODUCTO ID_PRODUCTO=='XK47' 'XK47'

BEGIN BEGINTRANSACTION TRANSACTION

Ejemplo de Transacciรณn

185

USE USENorthWind NorthWind DECLARE @Error int DECLARE @Error int que utilizaremos para almacenar un posible código de error --Declaramos una variable --Declaramos una variable que utilizaremos para almacenar un posible código de error BEGIN BEGINTRAN TRAN --Iniciamos --Iniciamoslalatransacción transacción UPDATE Products UPDATE ProductsSET SETUnitPrice=20 UnitPrice=20WHERE WHEREProductName ProductName=’Chai’ =’Chai’ --Ejecutamos la primera sentencia --Ejecutamos la primera sentencia SET SET@Error=@@ERROR @Error=@@ERROR --Si ocurre --Si ocurreun unerror erroralmacenamos almacenamossu sucódigo códigoen en@Error @Erroryysaltamos saltamosmediante medianteelel ----GOTO GOTOalaltrozo trozode decódigo códigoque quedeshara desharalalatransacción. transacción. IF (@Error<>0) GOTO TratarError IF (@Error<>0) GOTO TratarError --Si --Silalaprimera primerasentencia sentenciase seejecuta ejecutacon conéxito, éxito,pasamos pasamosaalalasegunda segunda UPDATE Products SET UnitPrice=20 WHERE ProductName=’Chang’ UPDATE Products SET UnitPrice=20 WHERE ProductName=’Chang’ SET SET@Error=@@ERROR @Error=@@ERROR --Y si --Y sihay hayun unerror errorhacemos hacemoscomo comoantes antes IF (@Error<>0) GOTO TratarError IF (@Error<>0) GOTO TratarError --Si --Sillegamos llegamoshasta hastaaquí aquíes esque quelos losdos dosUPDATE UPDATEse sehan hancompletado completadocon con --éxito y podemos “guardar” la transacción en la base de datos --éxito y podemos “guardar” la transacción en la base de datos COMMIT COMMITTRAN TRAN TratarError: TratarError: --Si --Siha haocurrido ocurridoalgún algúnerror errorllegamos llegamoshasta hastaaquí aquí IfIf@@Error<>0 THEN @@Error<>0 THEN BEGIN BEGIN PRINT PRINT‘Ha ‘Haecorrido ecorridoun unerror. error.Abortamos Abortamoslalatransacción’ transacción’ --Se lo comunicamos al usuario y deshacemos --Se lo comunicamos al usuario y deshacemoslalatransacción transacción --todo volverá a estar como si nada hubiera ocurrido --todo volverá a estar como si nada hubiera ocurrido ROLLBACK ROLLBACKTRAN TRAN END END

Ejemplo

¡Nada más lejos de la realidad!

186

Hay una interpretación incorrecta en cuanto al funcionamiento de las transacciones que esta bastante extendida. Mucha gente cree que si tenemos varias sentencias dentro de una transacción y una de ellas falla, la transacción se aborta en su totalidad.

Como se puede ver para cada sentencia que se ejecuta miramos si se ha producido o no un error, y si detectamos un error ejecutamos el bloque de código que deshace la transacción.

Transacciones



187

Estas dos sentencias se ejecutarán como una sola. Si por ejemplo en medio de la transacción (después del primer UPDATE y antes del segundo) hay un corte de electricidad, cuando el SQL Server se recupere se encontrará en medio de una transacción y, o bien la termina o bien la deshace, pero no se quedará a medias. El error está en pensar que si la ejecución de la primera sentencia da un error se cancelará la transacción. El SQL Server sólo se preocupa de ejecutar las sentencias, no de averiguar si lo hacen correctamente o si la lógica de la transacción es correcta. Eso es cosa nuestra. Por eso en el ejemplo que tenemos más arriba para cada sentencia de nuestro conjunto averiguamos si se ha producido un error y si es así actuamos en consecuencia cancelando toda la operación.

USE NorthWind BEGIN TRAN UPDATE Products SET UnitPrice=20 WHERE ProductName=’Chang’ UPDATE Products SET UnitPrice=20 WHERE ProductName=’Chang’ COMMIT TRAN

Si tenemos dos sentencias dentro de una transacción.

Transacciones

Procedimientos

NOTA: Los procedimientos son guardados en SQL Server en la carpeta de la la base de datos llamada Procedimientos Almacenados. 189

Para ejecutar el procedimiento usaremos: EXECUTE Cli_Credito 20000 , 60000 donde Cli_Credito es el nombre del procedimiento, y 20000, 60000 los 2 parรกmetros que pasamos al procedimiento.

CREATE CREATEPROCEDURE PROCEDURECli_Credito Cli_Credito @Credito_Inf MONEY, @Credito_Inf MONEY, @Credito_Sup @Credito_Sup MONEY MONEY AS AS SELECT SELECT** FROM FROM Clientes Clientes WHERE WHERELimite_Credito Limite_Credito BETWEEN BETWEEN @Credito_Inf @Credito_Inf AND AND@Credito_Sup @Credito_Sup

Crear Procedimientos

Funciones

Nombre del parรกmetro que pasamos a la funciรณn

191

CREATE CREATE FUNCTION FUNCTION dbo.Cube( dbo.Cube( @fNumber @fNumber float) float) RETURNS RETURNS float float AS AS BEGIN BEGIN RETURN RETURN(@fNumber (@fNumber**@fNumber @fNumber **@fNumber) @fNumber) END END

Funciรณn

Nombre de la funciรณn



192

IF IF@iNumber @iNumber <= <= 11 SET SET @i @i== 11 ELSE ELSE SET SET @i @i== @iNumber @iNumber ** dbo.Factorial(@iNumber dbo.Factorial(@iNumber --11)) RETURN RETURN(@i) (@i) END END

CREATE CREATEFUNCTION FUNCTION dbo.Factorial( dbo.Factorial( @iNumber @iNumber int int)) RETURNS RETURNSINT INT AS AS BEGIN BEGIN DECLARE DECLARE@i @iint int

Función factorial

193

CREATE CREATE FUNCTION FUNCTION dbo.AuthorsForState(@cState dbo.AuthorsForState(@cState char(2) char(2) )) RETURNS RETURNS TABLE TABLE AS AS RETURN RETURN (SELECT (SELECT ** FROM FROM Authors Authors WHERE WHERE state state == @cState) @cState)

Funciรณn con una tabla

Seguridad SQL



195

La seguridad es especialmente importante en un SGBD basado en SQL, ya que SQL permite hacer especialmente sencillo el acceso a la información contenida en las BBDD Los requerimientos de los sistemas en producción son muchos y muy variados, pero por resumir, algunos de ellos pueden ser en lo referente a SQL:

Seguridad SQL

196

datos de cualquier tabla deberían ser accesible sólo a un conjunto de usuarios, no a todos  Sólo algunos usuarios deben tener derecho de actualización de datos en una tabla en particular  Para algunas tablas, el acceso debería estar restringido en base a las columnas  Algunos usuarios deberían tener denegado el acceso mediante SQL interactivo, pero permitirles utilizar aplicaciones que utilicen tales tablas

 Los

Seguridad SQL



197

Cada SGBD tiene su propia implementación de los procesos de seguridad.

vez que el SGBD hace una operación sobre los datos, lo hace en nombre de algún usuario  El SGBD deberá actuar en consecuencia con los permisos que tal usuario tenga según la operación empleada y los datos afectados

 Cada

Los usuarios son los actores de la BBDD

Seguridad SQL

ď Ž

198

una vista y proporcionando un permiso de usuario para acceder a ella, pero no a sus tablas fuente, se puede permitir el acceso de un usuario Ăşnicamente a las columnas y filas seleccionadas.

ď&#x201A;¨ Definiendo

Las vistas juegan un papel esencial en la seguridad:

Vistas y Seguridad SQL



 UPDATE

 DELETE

 INSERT

 SELECT

199

El estándar SQL-1 especifica cuatro privilegios para tablas y vistas:

Privilegios



sintaxis básica es:

ALL [PRIVILEGES] | { SELECT | DELETE | INSERT | UPDATE [(col [, col …])]]

Siendo <privilegios>:

GRANT <privilegios> ON {nombre_tabla | nombre_vista} TO {PUBLIC | usuario, …, usuario [WITH GRANT OPTION]}

 Su

Cláusula GRANT

200

Concesión de Privilegios



sintaxis básica es:

ALL [PRIVILEGES] | { SELECT | DELETE | INSERT | UPDATE [(col [, col …])]]

Siendo <privilegios>:

REVOKE <privilegios> ON {nombre_tabla | nombre_vista} FROM {PUBLIC | usuario, …, usuario}

 Su

Cláusula REVOKE

201

Revocación de Privilegios

202

de datos “Pepe” correspondiente a BD_Factura

CREATE LOGIN Pepe WITH PASSWORD =‘m1a2n3o4l5o'; USE BD_Facturas; CREATE USER Pepe FOR LOGIN Pepe; Crea el usuario de base GO

Crear un usuario

Crea un inicio de sesión de servidor denominado “Pepe” con una contraseña

203

CREATE USER nom_usuario1 contraseña, nom_usuario2 contraseña, …

Crear usuario

204

Cambia el nombre del usuario de la base de datos BD_FACTURA “Pepe” a “Jose”.

USE BD_FACTURA; ALTER USER Pepe WITH NAME = Jose; GO

Cambiar el nombre de usuario

USE BD_FACTURA; DROP USER Jose; GO

Borra un usuario

205

206

CREATE GROUP nom_grupo1 contraseña, nom_grupo2 contraseña, …

Crear un grupo de usuarios

Cuando se haya agregado el usuario a un grupo, el usuario disfrutará de todos los permisos que se han otorgado a ese grupo



207

Añade uno o más usuarios a un grupo



ADD USER nom_usuario [,nom_usuario,..] TO nom_grupo

Añadir usuarios a un grupo



208

Eliminará el grupo o grupos especificados.  Los usuarios que pertenezcan a esos grupos no se verán afectados, pero dejarán de ser miembros de los grupos eliminados.

DROP GROUP nom_grupo

Borra un grupo

Si utilizamos DROP USER con la cláusula FROM, quitará del grupo los usuarios que indiquemos, pero no los borrará



209

Si utilizamos DROP USER sin la cláusula FROM, borrará los usuarios que indiquemos.



DROP USER nom_usuario1, nom_usuario2, …[FROM nom_grupo]

Borra un usuario

210

PASSWORD contraseña_nueva contraseña_anterior

ALTER USER nom_usuario

Cambia contraseña del usuario

211

ALTER DATABASE PASSWORD contraseña_nueva contraseña_anterior

Cambia la contraseña de la base de datos