Transcript SQL básico
SQL
Informática aplicada
Contenido
• Definición de datos • Estructura básica de consultas • Operaciones con conjuntos • Funciones de agregación • Valores nulos • Subconsultas anidadas • Consultas complejas • Vistas • Modificación de la base de datos • Relaciones unidas
Historia
• Lenguaje de IBM Sequel desarrollado como parte del proyecto sistema R en el laboratorio de investigación de IBM en San Jose • Renombrado como Lenguaje estructurado de consultas (Structured Query Language (SQL) • Estándar ANSI y ISO de SQL: – SQL-86 – SQL-89 – SQL-92 – SQL: 1999 – SQL:2003 • Los sistemas comerciales ofrecen muchas, sino todas, las facilidades de SQL 92, más variaciones de estándar más recientes.
– NO todo funcionara en el sistema que utilizamos.
Lenguaje de Definición de Datos DDL
Permite la especificación de no solo conjuntos de relaciones sino que información de cada relación, incluyendo: • El esquema para cada relación • El dominio de valores asociado con cada atributo • Restricciones de integridad • El conjunto de índices a ser mantenido para cada relación • Información de seguridad y autorización para cada relación • La estructura de almacenaje físico para cada relación en disco.
Tipos de dominios en SQL
• Char(n). Cadena de longitud fija, con especificación de longitud n por el usuario • Varchar(). Cadena de caracteres de longitud variable, con especificación por el usuario de la longitud máxima n.
• Int. entero (un subconjunto de los enteros de la máquina) • Smallint. Entero pequeño (un subconjunto dependiente de la máquina de los enteros) • Numeric(p,d). Números de punto fijo, precisión especificada por el usuario de p dígitos con n dígitos a la derecha del punto decimal.
• Real, doble presicision, Punto flotante y punto flotante de doble precisión, depende de la máquina.
• Float(n). Número de punto flotante, con precisión definida por el usuario de n dígitos.
• Más en el cap. 4.
Construcción de creación de tablas
• Una relación SQL se define usando el comando create table .
create table r(A 1 , D 1 , A 2 , D 2 , …, A n (restricción de integridad 1 ), … (restricción de integridad k ) ); D n , – r es el nombre de la relación – Cada A i es un nombre de atributo en el esquema de la relación r.
– D i es un tipo de datos de valores en el dominio del atributo Ai.
• Ejemplo: Create table branch{ (branch_name char(15) not null, branch_city char(30), assets integer);
Construcción de borrado y alteración de tablas
• El comando borrada de la base de datos.
• El comando drop table alter table borra toda la información de la relación es para agregar atributos a una relación existente: – alter table r add A D • Donde a es el nombre del atributo a ser agregado a la relación r y D es el dominio de A.
– A todas las tuplas de Ase les asigna el valor null para el nuevo atributo.
• El comando alter table puede ser usado para borara un atributo de una relación: – alter table r drop A • Donde A es el nombre del atributo de la relación r.
– El borrado de atributos no es soportado por muchas bases de datos.
Estructura básica de consultas
• SQL está basado en operaciones de conjuntos y relacionales con algunas modificaciones y mejoras • Una consulta típica de SQLP tiene la forma:
select from
A 1 , A 2 , …,A n r 1 , r 2 , …, r n
where
P – A i – R i representa un atributo representa una relación – P es un predicado • El resultado de una consulta SQL es una relación
La cláusula select
• Le cláusula select lista los atributos deseados en el resultado de la consulta – Corresponde a la operación de proyección del álgebra relacional • Ejemplo: select branch_name from loan ; – Nota: Los nombres en SQL no distinguen entre mayúsculas y minúsculas • Estos es Branch_Name = BRANCH_NAME = branch_name • Algunos usan mayúsculas
La cláusula select cont.
• SQL permite duplicar relaciones asi como en los resultados de las consultas.
• Para forzar la eliminación de duplicados, inserte la palabra distinct después de select • Encontrar los nombres de todas las sucursales de la relación loan, y remover duplicados select distinct branch_name from
loan
; • La palabra reservada all especifica que los duplicados no sean removidos.
select all branch_name from loan ;
La cláusula select cont.
• Un asterisco en la cláusula select denota “todos los atributos”: select * from
loan
; • La cláusula select puede contener expresiones aritméticas involucrando +, -, * y /, y operando en constantes o atributos de las tuplas.
• La consulta: • select
loan_number,branch_name
,
amuont*100
• frpm
loan;
• Regresará una relación de la relación
loan
, excepto que el valor del atributo
amount
está multiplicado por 100
La cláusula where
• La cláusula where especifica una condición que debe ser satisfecha – Corresponde a la selección del álgebra relacional • Para encontrar todos los números de prestamos hechos en la sucursal Perryridge con cantidades mayores a 1200 select loan_number from loan where branch_name =‘Perryridge’ and amount>1200 ; • Los resultados de las comparacione3s pueden ser usados con conectores lógicos
and
,
or
, y
not
.
• Las comparaciones pueden ser aplicadas a resultados de expresiones aritméticas
La cláusula where cont.
• SQL incluye el operador de comparación between (entre) • Ejemplo: encuentre el número del préstamo de aquellos préstamos con cantidades prestadas entre $90000 y $100000.
select loan_number from loan where amount between 90000
and
100000;
La cláusula from
• La cláusula from consulta lista las relaciones involucradas en la – Corresponde al producto cartesiano del álgebra relacional • Encuentre elproducto cartesiano de borrower loan
select
* from borrower ,
loan
• Encuentre el nombre, número de préstamos cantidad de todos los clientes que tengan préstamos en la sucursal de Perryridge select customer_name ,
borrower.loan_number
,
amount
from borrower,loan where borrower.loan_number=loan.loan_number and
branch_name
=‘Perryridge’;
Operación de renombrado
• SQL permite el renombrado de relaciones y atributos mediante la cláusula
as
.
– nombre_viejo as nombre_nuevo • Encuentre los nombre, números de préstamo y cantidades de todos los clientes; renombre la columna loan_number como loan_id select customer_name,borrower.loan_number as
loan_id
,
amount
from borrower ,
loan
where borrower.loan_number =
loan.loan_number
;
Variables de tuplas
• Las variables de tuplas son definidas en la cláusula from vía el uso de la cláusula as.
• Encontrar los nombres de clientes y sus números de cuenta para todos los clientes que tengan un préstamo en la misma sucursal select customer_name,T.loan_number,S.amount from borrower as T , loan as S where T.loan_numbre=S.loan_number ; • Conjunto de nombres de sucursales cuyo capital es mayor que el capìtal de alguna sucursal de Brooklyn select distinct T.branch_name from branch T ,
branch S
where T.assets > S.assets and
S.branch_city
= 'Brooklyn'; • La palabra as es opcional y puede ser omitida
Operaciones de cadena
• SQL incluye un operador de verificación de cadenas para comparaciones en cadenas de caracteres. El operador “like” usa patrones que son descritos usando dos caracteres especiales: – Porciento(%). El carácter % concuerda con cualquier subcadena.
– Subraya(_). El carácter _ concuerda con cualquier carácter.
• nombre de clientes en calles con nombres terminados en "hill“ select customer_name from customer where customer_street like '%Hill'; • Para concordar con “Hill%” – like ‘Hill\%’ escape ‘\’ • SQL soporta una variedad de operadores de cadena tales como: – Concatenación (usando “||”) – Conversión de mayúsculas a minúsculas y viceversa.
– Encontrar longitud de cadena, extraer subcadenas, etc.
Ordenado del despliegue de tuplas
• lista alfabéticamente los nombres de los clientes que tengan un préstamo en la sucursal Perryridge
select distinct
customer_name from borrower,loan where borrower.loan_number=loan.loan_number
branch_name
='Perryridge' and order by customer_name ; • Podemos especificar desc para orden descendente o asc para ascendente, para cada atributo; el orden ascendente es por omisión.
– order by customer_name desc ;
Operaciones de conjuntos
• Las operaciones de conjuntos union , intersect all .
y except operan en relaciones y corresponde a los operadores del álgebra relacional • Cada una de las operaciones anteriores elimina automáticamente los duplicados; para mantener los duplicados use la versión correspondiente de multi conjunto union all , , intersect all , .
y except Suponga una tupla ocurriendo m veces en r y n veces en s, entonces ocurre • m+n veces en r
union
all s • min(m,n) veces en r
intersect
• max(0,m-n) veces en r
except
all s all s
Operaciones de conjunto
• encontrar todos los clientes que tienen préstamo o cuenta o ambos ( select customer_name from depositor )
union
( select customer_name from borrower ); • encontrar todos los clientes que tienen préstamo y cuenta ( select customer_name from depositor )
intersect
( select customer_name from borrower ); • encontrar todos los clientes que tienen cuenta pero no préstamo ( select customer_name from depositor )
except
( select customer_name from borrower );
Funciones de agregación
• Estas funciones operan en valores de multi conjunto de un columna de una relación, y regresan un valor •
Avg
: valor promedio • • • •
Min
: valor mínimo
Max
: valor máximo
Sum
: suma de valores
Count
: número de valores
Funciones de agregación cont.
• Balance promedio de todas las cuentas de Perryridge select avg(balance ) from account where branch_name =‘Perryridge’; • Número de clientes
select count
(*) from customer ; • Número de depositantes en el banco select count(distinct customer_name ) from depositor ;
Funciones de agregación agrupadas
• Nombres de sucursales que tengan al menos una cuenta, con tamaño de conjunto de clientes que tengan al menos una cuenta en esa sucursal select branch_name , count(distinct customer_name ) from depositor ,
account
where depositor.account_number
account.account_number
= group by branch_name ; • Nota: atributos en la cláusula función de agregación deben aparecer en la lista de
group by
.
select
fuera de la
Funciones de agregación – cláusula having
• Nombres de sucursales que tengan donde el promedio de saldo es mayor a $650 select branch_name , avg( balance ) from account group by branch_name
having avg
(
balance
)>650; Nota: los predicados en la cláusula having son aplicados después de la formación de los grupos mientras los predicados de la cláusula where son aplicados antes de la formación de los grupos.
Valores nulos
• Es posible que una tupla tenga valores nulos, denotados por null, para algunos de sus atributos.
• Null significa valor desconocido o que el valor no existe • El predicado is null puede ser usado para verificar valores nulos.
– Ejemplo: encontrar todos los números de cuenta que aparecen en la relación
loan
con valores nulos para
amount
.
select loan_number from loan where amount is null ; • El resultado de operaciones aritméticas involucrando null es null – Ej. 5 + null es null • Sin embargo, las funciones de agregación simplemente ignoran los valores null
Valores nulos y lógica trivaluada
• Cualquier comparación con null regrese desconocido – Ej. 5
OR
: (desconocido or true) = true (desconocido or falso) = desconocido (desconocido or desconocido ) = desconocido –
AND
: (desconocido and true) = desconocido (desconocido or falso) = false (desconocido or desconocido ) = desconocido –
NOT
: desconocido = desconocido – “
P
es desconocido” se evalua como true si el predicado P se evalua como desconocido.
• El resultado de la cláusula
where
si se evalua a desconocido.
es tratado como false
Null y agregados
• Total de las cantidades de los préstamos
select sum
(
amount
) from loan ; – La sentencia de arriba ignora los nulos – El resultado en null si no hay valores no-nulos para
amount
• Todas las operaciones de agregación excepto
count
(*) ignora las tupplas con null en los atributos
Subconsultas anidadas
• SQL provee un mecanismo para anidar consultas • Una subconsulta es una expresión
select from-where
que esta anidada en otra • Un uso común de subconsultas es probar pruebas de pertenencia a conjuntos, comparación de conjuntos y poner cardinalidad
Consultas ejemplo
• nombre de los clientes que tienen ambas una cuenta y un préstamo en el banco select distinct customer_name from borrower where customer_name in ( select customer_name from depositor ); • nombre de los clientes que tienen préstamo pero no tiene una cuenta en el banco select distinct customer_name from borrower where customer_name not
in
( select customer_name from depositor );
Consultas ejemplo
• Encontrar todos los clientes que tienen ambas una cuenta y un préstamo en la sucursal Perryridge select distinct customer_name from borrower ,
loan
where borrower.loan_number = loan.loan_number and
branch_name
= 'Perryridge'
and
customer_name in ( select customer_name from account ,
depositor
where account.account_number = depositor.account_number and
branch_name
= 'Perryridge'); • Nota: la consulta anterior se puede escribir de una manera más sencilla. La formulación anterior es solo para ilustrar.
Comparaciones de conjuntos
• Encontrar todas las sucursales que tienen el capital más grande que alguna sucursal de Brooklyn.
select distintic Tbranch_name from branch as T , branch as S where T.assets>S.assets
s.branch_city
=‘Brooklyn’ • Lo mismo usando la cláusula
some
( select assets from branch where branch_city =‘Brooklyn’);
Definición de la cláusula some
• F
Definición de la cláusula all
• F
Consulta ejemplo
• Encuentre los nombres de todas las sucursales que tiene un capital mayor que todas las sucursales en brookyn.
select branch_name from branch where assets> all ( select assets from branch where branch_city =‘Brooklyn’);
Prueba de relaciones vacías
• • • La construcción
exists
regresa el valor true si el argumento de la subconsulta está no vacío.
exists
r r
not exists
r r =
Consulta ejemplo
• Encuentre todos los clientes que tienen una cuenta e ntodas las sucursales localizadas en Brooklyn. select distinct S.customer_name from depositor as S
where not exists
( ( select branch_name from branch where branch_city ='Brooklyn')
except
( select R.branch_name from depostor as T , account as R where T.account_number=R.account_number and
S.customer_name=T.customer_name
); – (esquema usado en este ejemplo) – Note que X – Y= X Y – Nota: No puede escribir esta consulta usando = all y sus variantes
Prueba para ausencia de duplicados
• La construcción unique prueba si en una consulta hay cualquier tupla duplicada en el resultado.
• Encontrar todos los clientes que tienen a lo más una cuenta en la sucursal Perryridge.
select T.customer_name from depositor as T
where unique
( ( select R.customer_name from account , depositor as R where T.customer_name=R.customer_name and R.account_number=account.account_number and
account.branch_name
='Perryridge'); • Esquema usado en este ejemplo
Consulta ejemplo
• Encontrar todos los clientes que tienen al menos dos cuentas en la sucursal Perryridge.
select distinct T.customer_name from depositor as T
where not unique
( ( select R.customer_name
and
from account , depositor as R where T.customer_name=R.customer_name and
R.account_number=account.account_number
account.branch_name
='Perryridge');
Vistas
• Provee el mecanismo para ocultar ciertos datos de la vista de ciertos usuarios. Para crear una vista usamos el comando: –
create view
v
as
Consultas ejemplo
• Una vista consistiendo de sucursales y sus clientes create view all_customer as ( select branch_name ,
customer_name
from depositor ,
account
where depositor.account_number =
account.account_number
)
union
( select branch_name ,
customer_name
from borrower ,
loan
where borrower.loan_number =
loan.loan_number
); • Encontrar todos los clientes de la sucursal Perryridge.
select customer_name from all_customer Where branch_name =‘Perryridge’;
Relaciones derivadas
• Encuentre el saldo promedio de las cuentas de aquellas sucursales donde el saldo promedio es mayor que $1,200.
select branch_name ,
avg_balance
from
( select brach_name ,
avg
(
balance
) from account group by branch_name ) as result(branch_name,avg_balance ) where avg_balance>1200 ; Note que no hay necesidad de usar
having
, ya que computamos la relación (vista) temporal result en la cláusula from, y los atributos de result pueden ser usados directamente en la cláusula where.
Modificación de la base de datos borrado
• Borrar todos los registros de cuentas en la sucursal Perryridge delete from account where branch-name = ‘Perryridge’; • Borrar todas las cuentas en todas las sucursales localizadas en la ciudad de Needham.
delete from account where branch_name in ( select branch_name from branch where branch_city = ‘ Needham ’ )
delete from depositor
where account_number in ( select account_number from branch, account where branch_city = ‘ Needham ’ and branch.branch_name = account.branch_name);
Consulta ejemplo
• Borra los registros de todas las cuentas con saldos abajo del promedio en el banco
delete from
account
where
balance
< (
select avg
(balance)
from
account);
– Problema: conforme borramos tuplas de
deposit
, el saldo promedio cambia – Solución de SQL: • Primero, calcular saldo promedio, y encontrar la tuplas a borrar • Después, borrar todas las tuplas de arriba (sin recalcular avg o re-probar las tuplas)
Modificación de la base de datos – inserción
• Agregar una tupla a
account
insert into account
values
(‘A-9732’, ‘Perryridge’,1200); • O equivalente insert into account (
branch_name
,
balance
,
account_number
)
values
(‘Perryridge’, 1200, ‘A-9732’) • Agregar nueva tupla a
account
con saldo nulo insert into account
values
(‘A-777’,‘Perryridge’,
null
)
• • •
Modificación de la base de datos – inserción
Dar un regalo a los prestatarios de la sucursal Perryridge, una cuenta de ahorros de $200, utilizar el número de préstamo como número de cuenta.
insert into account select loan_number, branch_name, 200 from loan where branch_name = ‘Perryridge’; insert into depositor select customer_name, loan_number from loan, borrower
where
branch_name =
‘
Perryridge’ and loan.account_number = borrower.account_number; El enunciado select-from-where es evaluado por completo antes de que cualquier resultado sea insertado en la relación 8de otra forma la consulta insert into table 1
select
Causaría problemas * from table 1
Modificación de la base de datos – update
• Incrementar todas las cuentas con saldos sobre $10,000 un 6 %, todas las otras recibirán el 5%.
– Escriba dos enunciados update: update account set balance =balance*1.06
where balance>10000; update account set balance =balance*1.05
where balance<=10000; – El orden es importante
Enunciado case para actualizaciones condicionales
• La misma consulta anterior: Incrementar todas las cuentas con saldos sobre $10,000 un 6%, todas las otras recibirán el 5%.
update account set balance = case when balance<=10000 then balance*1.05
else balance*1.06
end;