Download El Lenguaje SQL - OCW

El Lenguaje SQL
TEMA V
© Grupo de Bases de Datos Avanzadas – Univ. Carlos III de Madrid
El Lenguaje SQL
Índice
V.1 SQL como Lenguaje de Definición de Datos
V.1.1 Definición del esquema
V.1.2 Evolución del esquema
V.2 SQL como Lenguaje de Manipulación de
Datos
V.2.1 Actualizaciones
V.2.2 Consultas
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
V.1 Introducción
V.3 SQL como Lenguaje de Control
TEMA V
FBD3 V.2
1
El Lenguaje SQL
1. Introducción
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
¿QUÉ ES SQL?
(Structured Query Language)
‰ LENGUAJE DE:
ƒ Definición
ƒ Manipulación
o Consulta interactiva
o Programación
ƒ Control
‰ OPERA CON CONJUNTOS DE REGISTROS
‰ LENGUAJE AUTOCONTENIDO Y NO
PROCEDIMENTAL
TEMA V
FBD3 V.3
El Lenguaje SQL
1. Introducción
‰ IBM lo desarrolla en los años 70 (SEQUEL, SEQUEL-2, SQL)
‰ en los 80, la ANSI (American National Standard Institute) lo
transforma en estándar para la definición y manipulación
de datos en RDBMS. Aparecen numerosos SGBD basados en
SQL o con la misma apariencia que este.
‰ en los 90 se amplían sus capacidades: SQL embebido (89),
varias
a ias revisiones
e isiones del estándar
estánda (SQL92
(SQL92, SQL9
SQL9x),
) y versiones
e siones
propias de ciertos SGBDR.
‰ hoy, su uso es generalizado en todos los SGBD relacionales
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
HISTORIA DE SQL
FBD3 V.4
2
1. Introducción
El Lenguaje SQL
SQL 92
SQL 89
SQL 86-87
Intersección de
implementaciones
existentes
+
•Disparadores
•L. procedimental...
de restricciones
•Orientación al Objeto
•Sintaxis de dominios
TAD
•Tablas temporales
Encapsulamiento
Jerarquías
•Nuevos tipos de datos
Herencia
•L.
L. manipulac. esquema
IDO...
•Combinación externa
+ •Mejor tratamiento
Integridad
Referencial
básica
•SQL dinámico...
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL3
Imagen tomada de [1]
TEMA V
FBD3 V.5
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
Nivel Lógico
Global
TEMA V
Nivel
Externo
Nivel Físico
Definición
del
Esquema
CREATE DOMAIN
CREATE VIEW
CREATE TABLE
CREATE ASSERTION
CREATE INDEX
Evolución
del
Esquema
ALTER DOMAIN
ALTER TABLE
DROP INDEX
DROP VIEW
DROP DOMAIN
DROP TABLE
DROP ASSERTION
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SENTENCIAS DE DEFINICIÓN DE DATOS
FBD3 V.6
3
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<definición de dominio>::= CREATE DOMAIN nombre de dominio [AS]
<tipo de datos>
[DEFAULT <opción por defecto>]
[<restricción de dominio>]
<restricción de dominio>::= [<definición de nombre de restricción>]
<definición de restricción de verificación>
[<atributos de restricción>]
<definición de nombre de restricción>::= CONSTRAINT <nombre de
restricción>
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Definición de Dominios
<definición de restricción de verificación>::= CHECK ( <condición> )
TEMA V
FBD3 V.7
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
Ejemplo:
ALUMNO (num_mat, nombre, ciudad, cod_grupo)
DC/UC
GRUPO (cod_grupo, curso, turno, num_alums)
CREATE DOMAIN nomb_valido AS CHAR(20);
CREATE DOMAIN nota_valida INTEGER
CHECK (VALUE
(
BETWEEN 0 AND 10);
)
CREATE DOMAIN turno_valido CHAR(1)
DEFAULT ‘M’
CHECK (VALUE IN (‘M’,’T’));
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Definición de Dominios
FBD3 V.8
4
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<definición de tabla>::= CREATE [TEMPORARY] TABLE <nombre de tabla>
( <elemento de tabla> [ { , <elemento de tabla>}......] )
‰ Los elementos de tabla son:
<definición de columna>::= <nombre de columna>{<tipo de datos> |
<dominio>}
[<cláusula de valor por defecto>]
[ d fi i ió d
[<definición
de restricción
t i ió d
de columna>.......]
l
]
<definición de restricción de tabla/columna>::= [<definición de nombre de
restricción>]
<restricción de tabla/columna>
[<atributos de restricción>]
TEMA V
FBD3 V.9
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<definición de integridad referencial>::= FOREIGN KEY (<columnas que
referencian>) REFERENCES <tabla y columnas referenciadas>
[ MATCH <tipo de correspondencia> ]
[ <acción referencial disparada> ]
‰ Donde:
<tabla y columnas referenciadas>::= <nombre de tabla> [(<lista de
columnas referenciadas>)]
<acción referencial disparada>::=
<regla de modificación> [ <regla de borrado>]
|
<regla de borrado> [<regla de modificación>]
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de tablas
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de tablas
FBD3 V.10
5
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de tablas
<regla de modificación> ::= ON UPDATE <acción referencial>
<regla de borrado> ::= ON DELETE <acción referencial>
<acción referencial> ::=
CASCADE
| SET NULL
| SET DEFAULT
|
NO ACTION
TEMA V
FBD3 V.11
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de tablas
Ejemplo:
CREATE TABLE grupo
(
cod_grupo CHAR(3),
curso CHAR(1) NOT NULL,
turno TURNO_VALIDO,
TURNO VALIDO
num_alums INTEGER(2),
PRIMARY KEY (cod_grupo),
CHECK (curso>’0’ AND curso<‘4’)
);
TEMA V
FBD3 V.12
6
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de tablas
Ej
Ejemplo:
l
CREATE TABLE alumno (
num_mat INTEGER,
nombre NOMB_VALIDO UNIQUE,
ciudad CHAR(25) NOT NULL,
cod_grupo CHAR(3),
);
PRIMARY KEY (num_mat),
FOREIGN KEY (cod_grupo) REFERENCES grupo
ON DELETE CASCADE
ON UPDATE CASCADE
TEMA V
FBD3 V.13
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<definición de aserción>::= CREATE ASSERTION
<nombre de restricción>
<verificación de aserción>
[<atributos de restricción>]
<verificación de aserción> :: = CHECK ( <condición>)
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de aserciones
FBD3 V.14
7
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
Ejemplo:
Todos los alumnos de Madrid tienen que estar matriculados en
turno de tarde
CREATE ASSERTION madrid_tarde
CHECK (alumnos.ciudad<>‘MADRID’ OR grupo.turno=‘T’);
TEMA V
FBD3 V.15
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<definición de vista>::= CREATE VIEW <nombre de vista>
[ ( <lista de columna> ) ] AS
<expresión consulta>
[WITH CHECK OPTION]
Ejemplo:
CREATE VIEW alumnos_madrid AS
(SELECT * FROM alumno
WHERE ciudad=‘Madrid’);
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de vistas
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de aserciones
FBD3 V.16
8
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<definición de índice>::= CREATE [UNIQUE] INDEX
<nombre del índice>
ON <nombre de tabla>
(<lista de columna>)
Ejemplo:
CREATE INDEX ind_alumno ON Alumno(ciudad, cod_grupo);
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Creación de índices
FBD3 V.17
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<borrar dominio>::= DROP DOMAIN <nombre de dominio>
<borrar tabla>::= DROP TABLE <nombre de tabla>
<borrar vista>::= DROP VIEW <nombre de vista>
‰ Ejemplo:
DROP DOMAIN turno_valido;
DROP TABLE alumno;
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Actualización de esquemas
DROP ASSERTION ciudad_turno;
TEMA V
FBD3 V.18
9
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
<modificación de tabla>::=ALTER TABLE <nombre de tabla>
ADD | MODIFY | DROP
<elementos>
donde elementos son COLUMN, CONSTRAINT
Ejemplo:
ALTER TABLE alumno
l
DROP COLUMN nombre;
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Actualización de esquemas
ALTER TABLE alumno
ADD COLUMN precio INTEGER;
TEMA V
El Lenguaje SQL
FBD3 V.19
2. SQL como Lenguaje de
Definición de Datos
<definición de disparador>::=
[CREATE] TRIGGER [<nombre de disparador>]<tiempo acción disparador>
<evento disparador> ON <nombre tabla> [REFERENCING <lista de alias
para valores antiguos o nuevos> ]
<acción disparada>
Donde:
<tiempo de acción> ::=
AFTER
| BEFORE
<evento disparador< ::=
INSERT
| DELETE
| UPDATE [ OF <lista de columnas disparador>]
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Disparadores
FBD3 V.20
10
El Lenguaje SQL
2. SQL como Lenguaje de
Definición de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Disparadores
<alias para valores antiguos o nuevos> ::=
{OLD [ AS ] <nombre correlación valores antiguos>
NEW [ AS ] <nombre correlación valores nuevos>}
| {OLD_TABLE [ AS ]<alias de tabla de valores antiguos>
NEW_TABLE [ AS ]<alias de tabla de valores nuevos>}
<acción disparada> ::=
[ FOR EACH { ROW | STATEMENT}]
[ WHEN ( <condición de búsqueda>) <sentencia SQL disparada>
<sentencia SQL disparada> ::=
<sentencia de procedimiento SQL>
| BEGIN ATOMIC
| <sentencia de procedimiento SQL>;…
END
TEMA V
El Lenguaje SQL
FBD3 V.21
2. SQL como Lenguaje de
Definición de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Disparadores
‰ Ejemplo:
CREATE TRIGGER modificar_alum
AFTER UPDATE OF cod_grupo ON Alumno
REFERENCING OLD_TABLE AS v_alum
NEW_TABLE AS n_alum
FOR EACH ROW
BEGIN ATOMIC
(UPDATE Grupo SET num_alums = num_alums + 1
WHERE Grupo.cod_grupo=n_alum.cod_grupo);
(UPDATE Grupo SET num_alums = num_alums - 1
WHERE Grupo.cod_grupo=v_alum.cod_grupo);
END
TEMA V
FBD3 V.22
11
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Sentencias de manipulación
Operaciones de actualización:
INSERT
MODIFY
DELETE
p
de consulta o recuperación
p
Operación
SELECT
TEMA V
FBD3 V.23
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Sintaxis para la inserción
<sentencia de inserción>::= INSERT INTO <nombre tabla>
[(columna [,columna]....) ]
VALUES <( valor [,valor].....)>
Ejemplo:
INSERT INTO alumno VALUES (‘016’, ‘Juan López’,
‘Barcelona’, ‘3’);
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operación de actualización
FBD3 V.24
12
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Sintaxis de la modificación
<sentencia de modificación>::= UPDATE <nombre tabla>
SET { <nombre de columna> = <valor>
[, <nombre de columna> = <valor>]...}
[WHERE <condición de búsqueda>]
Ejemplo:
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operaciones de actualización
UPDATE grupo SET turno=‘T’ WHERE curso=‘3’;
TEMA V
FBD3 V.25
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Sintaxis del borrado
<sentencia de borrado>::= DELETE FROM <nombre tabla>
[WHERE <condición de búsqueda>]
Ejemplo:
DELETE FROM alumno
WHERE num_mat=16;
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operaciones de actualización
FBD3 V.26
13
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operación de consulta o recuperación
Sintaxis de la consulta
<sentencia de consulta>::=
SELECT (*|ALL|DISTINCT|<columna [,columna]...>)
FROM <nombre_tabla>[[,nombre_tabla]...]
WHERE <condición de búsqueda>
[GROUP BY <lista de columnas>
[HAVING <condición de búsqueda>] |
ORDER BY <lista de atributos> [ASC|DESC]]
ALL => selecciona todas las columnas
DISTINCT => suprime
p
columnas duplicadas
p
TEMA V
FBD3 V.27
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Sintaxis de la consulta
<condición de búsqueda>::= <atributo><operador>
<atributo|valor>[AND|OR|
<atributo><operador> <atributo|valor>]...
<operador>::=<,>,<=,>=,between, like,in ...
Ejemplo:
SELECT * FROM alumno WHERE nombre LIKE ‘A%’
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operación de consulta o recuperación
FBD3 V.28
14
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Funciones de Agregado
‰ Funciones que se aplican sobre las tuplas que componen
cada grupo resultante de aplicar una cláusula GROUP BY.
‰ Las funciones de agregado definidas en SQL92 son:
COUNT, MAX, MIN, SUM y AVG.
‰ Ejemplo:
SELECT ciudad, COUNT(*) FROM alumno GROUP BY
ciudad;
TEMA V
FBD3 V.29
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Orden de ejecución de cláusulas
‰ Se eligen las tuplas que cumplen las condiciones de la cláusula
WHERE
‰ Se construyen los grupos según la cláusula GROUP BY
‰ Se calculan los resultados de las funciones de agregado para cada
grupo, si las hay
‰ Se eliminan aquellos
q
g
grupos
p q
que no cumplen
p
las restricciones
definidas en la cláusula HAVING
‰ Por último, se ordenan las tuplas de salida según las columnas
indicadas en ORDER BY
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operación de consulta o recuperación
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
Operación de consulta o recuperación
FBD3 V.30
15
3. SQL como Lenguaje de
Manipulación de Datos
El Lenguaje SQL
Extensión de Ejemplo
cod_grupo
curso Turno
G 81
A
M
G 82
B
T
Grupo
Num_mat nombre ciudad Cod_grupo
Alumno
334
Luis
Madrid
G81
335
Ana
Madrid
G81
336
Jorge
Lugo
G81
337
Jose
Burgos
G81
TEMA V
FBD3 V.31
3. SQL como Lenguaje de
Manipulación de Datos
El Lenguaje SQL
Selección
num_mat, nombre
TEMA V
σ
(
ciudad=‘Madrid’
(alumno))
SELECT num_mat,nombre
FROM alumno
WHERE (ciudad=‘Madrid’);
Num _m at
n o m b re
334
Luis
335
A na
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
π
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
FBD3 V.32
16
3. SQL como Lenguaje de
Manipulación de Datos
El Lenguaje SQL
Proyección
πnombre,ciudad (alumno)
SELECT nombre, ciudad
FROM alumno;
Nom bre
Ciudad
Luis
Madrid
Ana
Madrid
Jorge
Lugo
Jose
Burgos
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
FBD3 V.33
3. SQL como Lenguaje de
Manipulación de Datos
El Lenguaje SQL
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
Proyección
SELECT ciudad
FROM alumno;
πciudad (alumno)
Ciudad
Madrid
¿Proyección?
Madrid
Lugo
Burgos
TEMA V
FBD3 V.34
17
3. SQL como Lenguaje de
Manipulación de Datos
El Lenguaje SQL
Producto Cartesiano
alumno
SELECT *
FROM alumno, grupo;
x grupo
Cod_grupo
curso
turno
Num_mat
nombre
ciudad
Cod_grupo
G81
A
M
334
Luis
Madrid
G81
G81
A
M
335
Ana
Madrid
G81
G81
A
M
336
Jorge
Lugo
G81
G81
A
M
337
Jose
Burgos
G81
G82
B
T
334
Luis
Madrid
G81
G82
B
T
335
Ana
Madrid
G81
G82
B
T
336
Jorge
Lugo
G81
G82
B
T
337
Jose
Burgos
G81
TEMA V
FBD3 V.35
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Combinación Natural
cod_grupo, curso, nombre
(
alumno * cod_grupo grupo)
TEMA V
C o d _ g ru
upo
cu rs
so
n o m b re
e
G81
A
Luis
G81
A
Ana
G81
A
Jorg e
G81
A
Jose
SELECT G.cod_grupo, G.curso,
A.nombre FROM alumno AS A,
grupo AS G WHERE
(A.cod_grupo=G.cod_grupo);
SELECT grupo.cod_grupo, curso,
nombre FROM alumno INNER JOIN
grupo ON
(alumno.cod_grupo=curso.cod_gru
po);
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
π
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
FBD3 V.36
18
El Lenguaje SQL
3. SQL como Lenguaje de
Manipulación de Datos
Combinación Externa
π
cod_grupo, curso, nombre
(
alumno */ cod_grupo grupo )
C o d _ g ru p o
cu rs o
n o m b re
G81
A
Luis
G81
A
A na
G81
A
Jorg e
G81
A
Jose
G82
B
TEMA V
El Lenguaje SQL
SELECT grupo.cod_grupo, curso,
nombre FROM alumno RIGHT
OUTER JOIN grupo ON
(alumno.cod_grupo=curso.cod_gru
po);
FBD3 V.37
4. SQL como Lenguaje de
Control
‰ Autorización de acceso:
<sentencia de concesión>::=GRANT <privilegios> ON
<nombre de objeto> TO <usuario>[, <usuario>]
[WITH GRANT OPTION]
Donde:
<privilegios>::= ALL PRIVILEGES | <lista de acción>
<lista de acción>::= SELECT | DELETE | INSERT | UPDATE
| REFERENCES | USAGE
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
‰ El creador de un objeto es siempre el propietario del mismo
y tiene todos los privilegios sobre el mismo.
TEMA V
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
SQL y el Álgebra Relacional
FBD3 V.38
19
El Lenguaje SQL
4. SQL como Lenguaje de
Control
<sentencia de revocación>::= REVOKE [GRANT OPTION FOR]
<privilegios> ON <nombre de objeto> FROM
<usuario>[, <usuario>] <comportamiento de borrado>
Donde:
<comportamiento de borrado>::= CASCADE | RESTRICT
‰ Ejemplo:
€ GRANT DELETE ON Alumno TO Jose;
€ REVOKE INSERT, UPDATE (precio) ON Alumno FROM Jose,
Juan CASCADE
TEMA V
El Lenguaje SQL
FBD3 V.39
Bibliografía
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
1. Miguel,
g , A. De,, Piattini,, M. Fundamentos y modelos de
Bases de Datos, Ed. Rama 1999
2. Miguel, A. De, Piattini, M. y Marcos, E. Diseño de Bases
de Datos Relacionales, Ed. Rama 1999
3. Miguel, A. De, Martínez, P., Castro, E., Cavero, J.M.,
Cuadra, D., Iglesias, A.M. y Nieto, C. Diseño de Bases de
Datos. Problemas Resueltos, Ed. Rama, 2001
4. Oszu, M.T. y Valduriez, P., Principles of Distributed
database systems, 2ª Edición, Prentice Hall, 1999
Tema I
© Grupo de Basses de Datos Avanzadas – Univ. Carloos III de Madrid
‰ Revocar p
privilegios:
g
FBD3 V.40
20