UNIDAD 8 Lenguaje SQL (Structured Query Language) 1.
-
Upload
pili-burgo -
Category
Documents
-
view
233 -
download
0
Transcript of UNIDAD 8 Lenguaje SQL (Structured Query Language) 1.
UNIDAD 8Lenguaje SQL (Structured Query Language)
1
Introducción2
Características
del lenguaje
Resultado del esfuerzo conjunto de dos grupos:• ANSI – American National Standars
Institute• ISO – International Standards
Organitation SQL constituye un factor fundamental
en el éxito de las bases de datos relacionales
Es un lenguaje declarativo (no procedural), es decir, se indica “que” se busca pero no el “como”
SQL3
Evolución SQL 1
SQL 2
SQL 3 (extiende SQL 2 con conceptos de OO)
SQL4
Tipos de Operaciones
1. Lenguaje de Definición de Datos (DDL)
2. Lenguaje de Manipulación de Datos (DML): consultas y actualizaciones
3. Definición de vistas
4. Definición de restricciones de integridad
5. Especificación de seguridad y autorización
6. Especificación de control de transacciones
7. Reglas para inclusión en lenguajes (C, PASCAL, etc.)
SQL: DDL y DML
Incluye sentencias que permiten definir los objetos de la Base de Datos
Las sentencias DDL generan cambios en el diccionario de datos, el cual contiene metadatos
Es un lenguaje que permite a los usuarios acceder o manipular los datos de la base de datos
5
• DDL DML
SQL
Create Alter Drop
Select Insert Update Delete
6
DDL DML
1- DDL7
CREATE DOMAIN
CREATE DOMAIN nombre_dominio AS tipo_dato [DEFAULT valor];
1- DDL8
CREATE TABLE
CREATE TABLE nombre_tabla (nombre_columna tipo_dato/dominio
[NOT NULL] [DEFAULT valor], [nombre_columna tipo_dato/dominio
[NOT NULL] [DEFAULT valor], ...],
[ PRIMARY KEY (nombre_columna [, nombre_columna,...]), ]
[ FOREIGN KEY (nombre_columna [, nombre_columna,...]),
REFERENCES nombre_tabla (nombre_columna [, nombre_columna,...]) ];
1- DDL9
DROP TABLE
DROP TABLE nombre_tabla [RESTRICT/CASCADE];
1- DDL10
ALTER TABLE
ALTER TABLE nombre_tabla ADD nombre_columna tipo_dato/dominio [DEFAULT valor];
ALTER TABLE nombre_tabla DROP nombre_columna;
2 - DML
Muticonjunto o bolsa
Conjunto
11
SQL Algebra
2 - DML12
SELECT
(Forma más simple)
SELECT lista_atributosFROM nombre_tabla[WHERE condición];
2 - DML13
SELECT SELECT lista_atributosFROM [nombre_tabla [,
nombre_tabla,...]] / [join][WHERE condición][UNION | UNION ALL | INTERSECT |
MINUS Subconsulta][GROUP BY lista_atributos [HAVING
condición]][ORDER BY lista_atributos];
2 - DML14
join [join] :: = nom_tabla1 NATURAL JOIN nom_tabla2 /nom_tabla1 JOIN nom_tabla2 ON condición /nom_tabla1 INNER JOIN nom_tabla2 ON condición /nom_tabla1 LEFT [OUTER] JOIN nom_tabla2 ON condición /nom_tabla1 RIGHT [OUTER] JOIN nom_tabla2 ON condición
2 - DML: Tablas Ejemplo15
Tabla DEPT
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Tabla DEPT
4 filas
2 - DML: Tablas Ejemplo16
Tabla DEPT
Tabla EMPEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
SMITHALLENWARD
JONESMARTIN
BLAKECLARKSCOTT
KINGTURNER
ADAMSJAMESFORD
MILLER
CLERKSALESMANSALESMANMANAGER
SALESMANMANAGERMANAGER
ANALYSTPRESIDENTSALESMAN
CLERKCLERK
ANALYSTCLERK
7902 7698 7698 7839 7698 7839 7839 7566
7698 7788 7698 7566 7782
17/12/8020/02/8122/02/8102/04/8128/09/8101/05/8109/06/8119/04/8717/11/8108/09/8123/05/8703/12/8103/12/8123/01/82
800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300
1400
0
20 30 30 20 30 30 10 20 10 30 20 30 20 10
14 filas
2 - DML: Proyección
SELECT epno, ename
FROM EMP;
(proyección1.sql)
17
Algebra SQL
π epno, ename EMP
2 - DML: Proyección (atributo)
SELECT jobFROM EMP;
SELECT distinct jobFROM EMP;
(proyección2 y 3.sql)
18
Algebra SQL
π job EMP
2 - DML: Proyección (atributo)
SELECT *FROM EMP;
Proyecta todos los atributos de la tabla
(proyección4.sql)
19
Algebra SQL
EMP
2 - DML: Proyección (atributo)
SELECT ename AS
nombreFROM EMP;
(proyección5.sql)
20
Algebra SQL
ρnombre (π ename EMP )
2- DML21
Proyección Los valores que se pueden proyectar son:
Atributos Constantes Expresiones Funciones
2 - DML: Proyección22
Expresiones y Constantes
SELECT ename as nombre, sal/2 as salario_quincena
FROM EMP;(proyección5.sql)
SELECT ename as nombre, ‘ cobra quincenalmente ’,
sal/2 as salario_quincena, ‘pesos’FROM EMP;
(proyección5.sql)
2 - DML: Proyección23
Funciones SELECT count(*)FROM EMP;
(proyección5.sql)
2 - DML: Selección
SELECT empno, ename
FROM EMPWHERE DEPTNO=20;
(seleccion1.sql)
24
Algebra SQL
π empno,ename(σ deptno=20
(EMP))
2 - DML: Selección25
Condiciones compuestas
SELECT empno, enameFROM EMPWHERE DEPTNO=20 and SAL<1000;
La condición puede utilizar cualquiera de los operadores relacionales y operadores lógicos para generar expresiones más complejas (para cualquier tipo de dato)
2 - DML: Selección26
Condiciones con nulos
SELECT empno, enameFROM EMPWHERE DEPTNO is null;
La condición puede utilizar el operador is que permite evaluar si un atributo tiene un valor nulo o no.
2 - DML: Selección27
Condiciones con operador el “like “
SELECT *FROM scott.empWHERE ename LIKE 'A%';
(selección2_1.sql) El operador like compara parte de
una cadena de caracteres, donde: % sustituye a una cantidad
arbitraria de caracteres _ sustituye a un solo carácter
2- DML28
Operadores de comparación para consultas simples (sin subqueries)
Operadores de comparación de conjuntos <columna> [NOT] IN (<lista de
valores>) Select * from EMP where DEPTNO in
(20,30); Operadores de valores nulos
<columna> IS [NOT] NULL (ya vimos un ejemplo)
Operadores de condiciones de dominios <columna> [NOT] BETWEEN <valor
inferior> AND <valor superior>
2 - DML29
Operadores de comparación para consultas más complejas
Un subquery valuado en valor
<expresión> [NOT] IN (<subquery>)
En este caso <expresión> es comparada con el conjunto que devuelve el subquery
2 - DML (continuación)30
Operadores de comparación para consultas más complejas
<expresión> <operador de comparación> [ANY/ALL] (<subquery>)
donde <expresión> puede ser una columna o un valor computado
En este caso <expresión> es comparada con cada valor seleccionado por el subquery Any: Evalúa verdadero si existe al menos
una tupla seleccionada en el subquery que satisfaga la condición. Si el subquery da vacío, la condición es falsa.
All: Evalúa verdadero si todas las tuplas seleccionadas por el subquery satisfacen la comparación. Si el subquery es vacío, la condición es verdadera.
2 - DML: Ejemplos31
Operadores de comparación para consultas más complejas
Mostrar todos los empleados que trabajan en el depto 10 y que ganan al menos tanto como algún empleado del departamento 30
SELECT *FROM scott.empWHERE deptno=10 and sal >= any
(SELECT sal FROM scott.emp WHERE deptno=30);
2 - DML: Ejemplos32
Operadores de comparación para consultas más complejas
Mostrar todos los empleados que no trabajan en el depto 30 y que ganan más que todos los empleados que trabajan en el departamento 30
SELECT *
FROM scott.emp
WHERE deptno <> 30
and sal >= all
(SELECT sal
FROM scott.emp
WHERE deptno=30);
2 - DML33
Equivalencias de operadores para consultas más complejas (con subqueries)
In = any
Not in <> all
Equivale
Equivale
2 - DML34
Operadores de comparación para consultas más complejas (con subqueries)
Un subquery para test de existencia<NOT> EXISTS (<subquery>)
En este caso: Exists: Evalúa verdadero si el
subquery devuelve tuplas.
Not Exists: Evalúa verdadero si el subquery no genera tuplas, es decir, devuelve una tabla vacía.
2 - DML: Ejemplos35
Operadores de comparación para consultas más complejas (con subqueries)
Mostrar todos los datos de los departamentos que no tienen empleados
SELECT *FROM scott.deptWHERE not exists
(SELECT * FROM scott.emp WHERE emp.deptno = dept.deptno);
2 - DML36
Ordenamiento
SELECT ........[ORDER BY lista_atributos
ASC/DESC];
Ordena la salida (tabla resultante) según el/los atributos especificados
2 - DML: Producto Cartesiano
EMP x DEPT SELECT *FROM EMP , DEPT;
(producto.sql)
37
Algebra SQL
2 - DML: Natural Join
π ename, job, dname (EMP *
DEPT)
SELECT ename, job, dnameFROM EMP NATURAL JOIN
DEPT;
(join2.sql)
(Los atributos sobre los cuales se hace el JOIN deben tener el mismo nombre en ambas tablas)
38
Algebra SQL
2 - DML: Natural Join39
Natural Join construido con producto cartesiano Algebra
SELECT epno,ename, job, mgr, hiredate, sal, comm, deptno, dname, loc
FROM EMP, DEPTWHERE EMP.deptno=
DEPT.deptno;
(join1.sql)
2 - DML: Join
πename, job, dname
(EMP EMP.deptno =
DEPT.deptno
DEPT)
SELECT ename, job, dname FROM EMP JOIN DEPT ON EMP.deptno = DEPT.deptno;
(join3.sql) SELECT ename, job,
dname FROM EMP INNER JOIN DEPT ON EMP.deptno = EPT.deptno;
(join3_1.sql)
40
Algebra SQL
2 - DML: Join41
Join con restricción extra
SELECT ename, job, dnameFROM EMP JOIN DEPT ON
EMP.deptno = DEPT.deptnoWHERE EMP.deptno = 10;
(join3_1.sql)
2 - DML: Outer Join42
Outer Join LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
2 - DML: Outer Join43
Left Outer Join
SELECT deptno,dname, ename, job FROM DEPT NATURAL JOIN EMP;El departamento 40 no sale informado porque
no existe ningún empleado asignado a él
(join5.sql) SELECT DEPT.deptno,dname, ename, job FROM DEPT LEFT OUTER JOIN EMP ON
DEPT.DEPTNO=EMP.DEPTNO;El departamento 40 sale informado a pesar
de que no existen empleados asignados a él
(join6.sql)
2 - DML: Outer Join44
Right Outer Join
SELECT deptno,dname, ename, job FROM DEPT NATURAL JOIN EMP;
El departamento 40 no sale informado porque no existe ningún empleado asignado a él
(join5.sql) SELECT ename, job, dept.deptno,dname FROM EMP RIGHT OUTER JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO;El departamento 40 sale informado a pesar
de que no existen empleados asignados a él
(join7.sql)
2 - DML: Outer Join45
Full Outer Join
SELECT lista_atributosFROM nombre_tabla FULL OUTER
JOIN nombre_tabla ON condición;
2 - DML: Union
π ename (σ deptno=20 or
deptno=30 EMP)
π ename (σ deptno=20
EMP) U π ename (σ
deptno=30 EMP)
SELECT ename FROM EMP WHERE deptno=20
or deptno=30 ;
SELECT ename FROM EMP WHERE deptno=20
UNIONSELECT ename FROM EMP WHERE deptno=30;(union1.sql)
46
Algebra SQL
2 - DML: Union
En este caso, si existen tuplas repetidas se eliminan
En este caso, si existen tuplas repetidas no se eliminan
47
Union Union All
2 - DML: Intersección48
Intersección
Departamentos que tienen administrativos y analistas entre sus empleados (CLERK y ANALYST)
π deptno(σ job=`CLERK´ EMP) ∩
π deptno(σ job=`ANALYST´ EMP)
2 - DML: Intersección49
Intersección
Select deptno from empwhere job='CLERK'
INTERSECTSelect deptno from empwhere job=‘ANALYST';
(interseccion1.sq
l)Ver (depts_jobs.sql)
2 - DML: Intersección50
Intersección
SELECT dept.deptno, dnamefrom dept where deptno in
(Select deptno from emp where job='CLERK')
and deptno in (Select deptno
from emp where job=‘ANALYST');
(interseccion2.sq
l)
2 - DML: Minus
π deptno Dept – π deptno EMP
select deptno from deptminusselect deptno from emp;
select deptno from deptwhere deptno not in (select deptno from emp);
Departamentos que no tienen empleados
51
Algebra SQL
(minus1.sql)
(minus2.sql)
2 - DML: Exists vs. In52
Exists vs. In
Las subconsultas vinculadas con un In pueden seleccionar más de un atributo, obviamente compatible con los atributos expresados en el where.
Todas las consultas resueltas con IN pueden ser resueltas con Exists.
Idem para los negados (Not In – Not Exists)
2 - DML: Division
πx,y A
DIVIDE BY
πy B
Select A.xfrom Awhere not exists
(select * from B where not exists
(select * from A AX where AX.x=A.x and AX.y=B.y));
53
Algebra SQL
2 - DML: Alias54
Alias SELECT lista_atributosFROM nombre_tabla
nombre_alias.....;
2 - DML: Funciones de Resumen
55
Funciones SUM AVG COUNT MAX MIN
2 - DML: Funciones Resumen
56
Funciones de Resumen
SELECT Sum(nombre_atributo*)FROM nombre_tabla;
SELECT Avg(nombre_atributo*)FROM nombre_tabla;
SELECT Count(*)FROM nombre_tabla;
* El atributo debe ser numérico
2 - DML: Grupos
57
Group y Having
SELECT ........[GROUP BY lista_atributos [HAVING condición]];
Agrupa las tuplas que tienen igual valor del/ de los atributos especificados en lista_atributos.
Muestra una tupla representativa del grupo (resume el grupo). Este valor debe ser único al grupo y además si es un atributo, debe estar especificado en la cláusula group by.
(agrup cantidad emp x depto.sql)
(agrup con join.sql)
2 - DML: Grupos
58
Group y Having
SELECT ....[GROUP BY lista_atributos [HAVING condición]];
Agrupa las tuplas que tienen igual valor del/ de los atributos especificados en lista_atributos.
Muestra una tupla representativa del grupo (resume el grupo). Este valor debe ser único al grupo y además si es un atributo, debe estar especificado en la cláusula group by.
El HAVING permite seleccionar los grupos que cumplen la condición indicada
(agrup cantidad emp x depto.sql)(agrup con join.sql)
(agrup cantidad emp x depto con condicion.sql)
2 – DML59
Actualización
Inserción de tuplas
Modificación de tuplas
Eliminación de tuplas
2 – DML: Inserción60
Insert con values
INSERT INTO <tabla> [<colum_i, …, colum_j>] VALUES (<valor_i, …, valor_j>);
Para cada columna debe ser especificado un valor.
Si no se especifica una columna de la tabla, se almacenará en ella un valor nulo.
Si no se especifican las columnas se asumirá el orden especificado en el create.
3 – DML: Inserción61
Insert con subquery
INSERT INTO <tabla> [<colum_i, …, colum_j>]
<query>;
Ejemplo:insert into hr.jobs select * from otrohr.jobs;
3 – DML: Modificación62
Update UPDATE <tabla>
SET<colum_i>=<expr_i>, …, <expr_j> = <expr_j>
[WHERE <condición>];
Donde: <expr> puede ser una constante, una expresión
aritmética o un query
Ejemplo:UPDATE EMP
SET sal = (select min(sal) from EMP where job=‘MANAGER’)
WHERE job=‘SALESMAN’ and deptno=20;
3 – DML: Modificación63
Update UPDATE <tabla>
SET (<columna i, …, columna j>) = <query>
[WHERE <condición>];
3 – DML: Eliminación64
DELETE DELETE FROM <tabla> [WHERE <condición>];
3 - Vistas
65
Definición de Vistas
Una vista es definida como una consulta sobre una o más relaciones, guardadas con un nombre en el diccionario de datos
¿Por qué usar vistas? Ocultar información a algunos
usuarios Hacer más simples algunas
consultas Modularidad en el acceso a la base
de datos
3 - Vistas
66
Creación de Vistas
CREATE VIEW <nombre de la vista> [<columna(s)>] AS
<sentencia select> [ WITH CHECK OPTION ];
Si [<columna(s)>] no está especificado, las columnas toman el mismo nombre de los correspondientes a la trabla generada en la sentencia select.
Una vista es evaluada cada vez que es accedida. La definción de una vista puede usar otra vista. La cláusula WITH CHECK OPTION provoca que si se
intenta insertar en la vista una tupla que no satisface la condición de la vista, esa inserción es rechazada. Los Updates son similarmente rechazados si el nuevo valor no satisface la condición.
3 - Vistas
67
Uso de Vistas
Una vista puede ser usada en una sentencia SELECT en forma idéntica que una tabla.
No toda vista puede ser actualizada automáticamente (insert, update, delete).
La dificultad de la actualización de vistas radica en que éstas deben ser traducidas a actualizaciones sobre las tablas bases sobre las que está definida la vista (create trigger … instead of … )
3 - Vistas68
Condiciones para que una vista pueda actualizarse automática-mente
La cláusula from debe usar una sola tabla
La clausula select contiene sólo nombres de atributos, es decir, no tiene: expresiones funciones de agregación especificación de distinct
No debe incluir la cláusula group by en su definción
No se ha incluido en la clausula select algún atributo que en la tabla base tiene la restricción de not null
4 – Restricciones de Integridad
69
Tipos
(En cuanto a la forma de definirse)
Una restricción puede definirse sintácticamente de dos formas: Como parte de la definición de
una columnaEl nombre de la restricción es
asignada automáticamente por el motor de base de datos.
Como parte de la definición de la tabla.El nombre de la restricción
puede ser especificado por el usuario.
4 – Restricciones de Integridad
70
Tipos
(En cuanto al objeto sobre el que se define)
De tabla / columna Unicidad (unique). Clave primaria (primary key). Integridad referencial (foreign key -
references). Control (check).
De dominio Se aplican a cada columna (de
cualquier tabla) que está basada en ese dominio, actuando como una restricción de tabla.
Pueden incluir una condición referida a los valores del dominio.
4 – Restricciones de Integridad
71
Tipos Aserción
Una restricción de control (check constraint) definida independientemente de cualquier tabla.
Incluyen una condición que puede referir al contenido de filas individuales de una o varias tablas, al contenido de tablas enteras o a un estado determinado por las relaciones entre varias tablas.
4 – Restricciones de Integridad
72
Comprobación
Todas las restricciones son comprobadas con los mismos mecanismos y procedimientos:
Cada restricción tiene una condición que se evalúa a cierto o a falso.
Cada restricción tiene un modo (constraint mode): DEFERRED => diferido Las restricciones con el modo DEFERRED
son comprobadas al final de la ejecución de un grupo lógico de sentencias SQL (transacción).
IMMEDIATE => inmediato Las restricciones con el modo IMMEDIATE
son comprobadas después de la ejecución de cada sentencia SQL.
4 – Restricciones de Integridad
73
Comprobación
Cada restricción tiene un modo por defecto que es el utilizado al iniciar una nueva sesión SQL o una nueva transacción.
<modo de restricción> ::= { INITIALLY DEFERRED | INITIALLY IMMEDIATE } [ [ NOT ] DEFERRABLE ]
4 – Restricciones de Integridad
74
Restricciones de Tabla /Columna
Unicidad (unique) Dos filas de la tabla no
pueden tener el mismo valor no nulo en las columnas indicadas.
Puede aplicarse al conjunto completo de columnas
4 – Restricciones de Integridad
75
Restricciones de Tabla / Columna
Clave primaria (primary key) Es una restricción de unicidad
pero en la cual las columnas no pueden tomar valor nulo (integridad de entidades)
4 – Restricciones de Integridad
76
Restricciones de Tabla / Columna
Integridad referencial (foreign key- references) Representa una clave ajena o
foránea. Incluye una o más columnas
referenciantes (referencing columns) y las correspondientes columnas referenciadas (referenced columns) de una tabla base referenciada (referenced table), que puede ser la referenciante.
Las columnas referenciadas deben tener una restricción de unicidad en la tabla referenciada.
4 – Restricciones de Integridad
77
Restricciones de Tabla / Columna
Control (check). Incluye una condición de
búsqueda que sólo puede referir a elementos de la tabla que la incluye.
4 – Restricciones de Integridad
78
Sintaxis <definición de restricción de tabla> ::=
[ CONSTRAINT <nombre de restricción> ] <restricción de tabla> [ <modo de restricción> ]
<restricción de tabla> ::={ <definición de restricción de unicidad>| <definición de restricción referencial>| <definición de restricción de control> }
4 – Restricciones de Integridad
79
Sintaxis <definición de restricción de unicidad> ::=
{ { UNIQUE | PRIMARY KEY } ( <lista de columnas> )
| UNIQUE ( VALUE ) }
<definición de restricción referencial> ::=
FOREIGN KEY ( <lista columnas referenciantes> ) <especificación de referencia>
<especificación de referencia> ::= REFERENCES <nombre tabla referenciada> [ (<lista columnas referenciadas>) ] [ <acciones referenciales> ]
4 – Restricciones de Integridad
80
Sintaxis <acciones referenciales> ::=
{ <regla de modificación> [ <regla de borrado> ]| <regla de borrado> [ <regla de modificación> ] }
<regla de modificación> ::=ON UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
<regla de borrado> ::=ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
4 – Restricciones de Integridad
81
Sintaxis <definición de restricción de control> ::=
CHECK (<condición >)
4 – Restricciones de Integridad
82
Sintaxis Las restricciones de columna son restricciones asociadas a una única columna de una tabla:
<restricción de columna> ::={ NOT NULL| { UNIQUE | PRIMARY KEY }| <especificación de referencia>| <definición de restricción de control> }
4 – Restricciones de Integridad
83
Sintaxis <restricción de dominio> ::=[ CONSTRAINT <nombre de restricción> ]CHECK (<condición de busqueda>)[ <modo de restricción> ]
4 – Restricciones de Integridad
84
Aserciones:
Sintaxis
<definición de aserción> ::=
CREATE ASSERTION <nombre de restricción>
CHECK (<condición de busqueda>)
[ <modo de restricción> ]
5 – Seguridad85
Usuarios create <nombre usuario>
identified by <password>;
5 – Seguridad86
Conceder/ Revocar permisos
grant <lista de privilegios> [on <objeto/s>]
to <usuario/s o rol/es> [with grant option];
revoke <lista de privilegios> [on <objeto/s>]
from <usuario/s o rol/es> [restrict | cascade];
6 –Transacciones87
Transacciones:
Commit
Rollback
Una transacción consiste en una secuencia de instrucciones de consulta y actualizaciones.
Inicio: Implícitamente una transacción comienza cuando se ejecuta la primer instrucción SQL
Finalización: Una transacción puede terminar con Commit: Compromete la transacción actual, es
decir, hace que los cambios realizados por la transacción sean permanentes en la base de datos
Rollback: Causa el retroceso de la transacción , es decir, deshace todas las actualizaciones realizadas por las instrucciones SQL de la transacción;. El estado de la base de datos se restaura al que existía previo a la ejecución de la transacción