SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos
description
Transcript of SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos
![Page 1: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/1.jpg)
4.1
SQL-Data Definition Language (DDL)SQL-Data Definition Language (DDL)Lenguaje de Definición de DatosLenguaje de Definición de Datos
Esquema de cada relación.
Dominios de cada atributo.
Restricciones de integridad.
Restricciones de seguridad para cada relación.
![Page 2: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/2.jpg)
4.2
Tipos de Datos en SQLTipos de Datos en SQL(volveremos sobre ellos)(volveremos sobre ellos)
char(n). Cadena de longitud fija. La longitud es n caracteres. varchar(n). Cadena de longitud variable. La longitud máxima es n
caracteres. (text) int/integer. Entero. smallint. Entero corto. numeric(p,d). Numero en formato de coma fija, con precisión de p dígitos,
con d dígitos a la derecha de la coma decimal. (1-> 0.9999) real, double precision. numero en coma flotante y número en coma
flotante con doble precisión. float(n). Número en coma flotante con una precisión no menor de n
dígitos. El valor NULL esta permitido para todos los atributos a menos que se
prohíba explícitamente. not null prohíbe el uso del valor NULL. La construcción create domain en SQL-92 crea tipos de datos definidos
por el usuariocreate domain nombre-persona char(20) not null
(typedef in C)
![Page 3: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/3.jpg)
4.3
Ejemplo de DominioEjemplo de Dominiohacerhacer
--drop function domain_username_constraint_check(text) ;
create or replace function
domain_username_constraint_check (text)
returns boolean as '
select
case
when (length($1) >= 6) then true
else false
end
;
' language 'sql';
--drop domain username;
create domain username as text
check (domain_username_constraint_check(value));
create TABLE midominio2( nombre username);
--version equivalente
create TABLE midominio2( nombre text check (length(nombre) >= 6));
![Page 4: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/4.jpg)
4.4
Día y Hora en SQL (Cont.)Día y Hora en SQL (Cont.)practica fecha creacionpractica fecha creacion
date. Fecha (día del año), año (4 dígitos), mes y dia Ej. date ‘2001-7-27’
time. hora del día, en horas, minutos y segundos. E.j. time ’09:00:30’ time ’09:00:30.75’
timestamp: día y hora E.j. timestamp ‘2001-7-27 09:00:30.75’
Interval: periodo de tiempo E.j. Interval ‘1’ day la diferencia entre “date/time/timestamp” da un “interval” “Interval” se puede sumar a “date/time/timestamp”
Se pueden extraer valores independientes de “date/time/timestamp” E.j. extract (year from r.comienzo)
![Page 5: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/5.jpg)
4.5
Creación de Tablas Creación de Tablas ejemploejemplo
Para crear una tabla se usa la orden CREATE TABLE. Es necesario especificar (al menos) el nombre de la tabla, los nombres de las columnas y el tipo de dato. Por ejemplo:
CREATE TABLE tablita (
nombre1 char(20), -- hola
nombre2 integer -- que tal
);
Este comando crea una tabla llamada tablita con dos columnas llamadas nombre1 y nombre2.
![Page 6: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/6.jpg)
4.6
Destrucción de TablasDestrucción de Tablas
Cuando las tablas no sean necesarias se pueden borrar con la orden DROP TABLE. Por ejemplo:
DROP TABLE tablita;
Todos los comandos CREATE tienen una pareja DROP
![Page 7: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/7.jpg)
4.7
Columnas Auxiliares Creadas por PostgeSQL Columnas Auxiliares Creadas por PostgeSQL ejemplo, pgaccesejemplo, pgacces
Cada tabla tienen varias columnas definidas por la base de datos. Los nombres de estas columnas auxiliares no se pueden usar como nombres de las columnas definidas por el usuario.
oid tableoid xmin cmin xmax cmax ctid
![Page 8: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/8.jpg)
4.8
Valores por Defecto Valores por Defecto ejemploejemplo
Se le puede asignar un valor por defecto a una columna. Cuando se crea la tupla si no se le asigna ningún valor a a alguna de las columnas está cogerá su valor por defecto. (si no se declara explícitamente el valor por defecto es NULL)
A la hora de definir la tabla los valores por defecto van tras la declaración de la columna. Esto es:
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text, --soy un comentario
precio numeric(10,2) DEFAULT 9.99
);
![Page 9: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/9.jpg)
4.9
Valores por Defecto Valores por Defecto ejemploejemplo
CREATE SEQUENCE producto_no_seq;
CREATE TABLE productos (
producto_no integer PRIMARY KEY DEFAULT nextval('producto_no_seq'),
nombre text,
precio numeric(10,2) DEFAULT 9.99
);
CREATE UNIQUE INDEX producto_no ON productos ( producto_no );
![Page 10: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/10.jpg)
4.10
RestriccionesRestricciones
“ Check”, Restriccion arbitraria
“ Not-Null”, El atributo no acepta valores nulos
“ Unique”, El atributo no acepta valores repetidos
“ Primary Keys”, El atributo es clave primaria
“ Foreign Keys”, El atributo es clave extranjera
![Page 11: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/11.jpg)
4.11
CHECK CHECK ejemploejemplo
Permite especificar que los valores de una columna deben satisfacer una expresión. Por ejemplo ser positivo.
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric(10.2) CHECK (precio > 0)
);
La restricción debe definirse DESPUES del tipo de dato. Se define usando la palabra CHECK seguida de una expresión entre paréntesis
![Page 12: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/12.jpg)
4.12
CHECK ejemplo
Permite especificar que los valores de una columna deben satisfacer una expresión. Por ejemplo ser positivo.
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric(10,2) CONSTRAINT precio_positivo CHECK (precio > 0)
);
CONSTRAINT nombre_ligadura puede usarse con las otras restricciones
![Page 13: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/13.jpg)
4.13
CHECKCHECK
Las restricciones pueden involucrar varias columnas pero no varias tablas.
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric CHECK (price > 0),
precio_rebajado numeric CHECK
(precio_rebajado > 0),
CHECK (precio > precio_rebajado )
);
![Page 14: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/14.jpg)
4.14
CHECK, AdvertenciaCHECK, Advertencia
“CHECK” se satisface si la expresion evaluada es “TRUE” o “NULL”.
![Page 15: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/15.jpg)
4.15
NOT NULL NOT NULL ejemploejemplo
Indica que el atributo no puede valer “NULL”
CREATE TABLE productos (
producto_no integer NOT NULL,
nombre text NOT NULL,
precio numeric(10,2)
);
![Page 16: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/16.jpg)
4.16
NOT NULLNOT NULL
Pueden existir varias restricciones referidas al mismo atributo, el order no importa.
CREATE TABLE productos (
producto_no integer NOT NULL,
nombre text NOT NULL,
precio numeric(10,2) NOT NULL CHECK (precio > 0)
);
![Page 17: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/17.jpg)
4.17
UNIQUE UNIQUE ejemploejemplo
Asegura que un determinado valor no esta repetido en una columna.
CREATE TABLE productos (
producto_no integer UNIQUE,
nombre text,
precio numeric(10,2)
);
![Page 18: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/18.jpg)
4.18
UNIQUE UNIQUE ejemploejemplo
Alternativamente se puede usar la sintaxis
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric,
UNIQUE(producto_no,nombre)
);
Pregunta: ¿producto_no y nombre deben ser únicos o la pareja debe ser única?
![Page 19: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/19.jpg)
4.19
UNIQUE ejemplo
¿Esta tabla es equivalente a la anterior?
CREATE TABLE productos (
producto_no integer UNIQUE,
nombre text UNIQUE,
precio numeric(10.2)
);
CREATE TABLE productos (
producto_no integer,
nombre text,
precio numeric(10.2),
UNIQUE(producto_no,nombre)
);
![Page 20: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/20.jpg)
4.20
UNIQUE vs CHECKUNIQUE vs CHECK
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
CREATE TABLE example (
a integer,
b integer,
c integer,
CHECK (a > 0 AND b > 0)
);
![Page 21: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/21.jpg)
4.21
Clave Primaria Clave Primaria ejemploejemplo
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text,
precio numeric
);
CREATE TABLE ejemplo (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
![Page 22: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/22.jpg)
4.22
Clave Primaria: ¿Es esto equivalente? ejemplo
CREATE TABLE productos (
a integer PRIMARY KEY,
b text PRIMARY KEY,
c numeric
);
CREATE TABLE ejemplo (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
![Page 23: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/23.jpg)
4.23
Pregunta:Pregunta:
¿Es equivalente UNIQUE a PRIMARY KEY?
![Page 24: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/24.jpg)
4.24
Clave Extranjera Clave Extranjera ejemplo,triggerejemplo,trigger
La restriccion “REFERENCES” asegura que los valores de una determinada columna debe ser identicos a los valores que aparecen en otra determinada columna que puede estar en otra tabla
CREATE TABLE productos ( producto_no integer PRIMARY KEY, nombre text, precio numeric);
CREATE TABLE pedidos ( pedido_no integer PRIMARY KEY, producto_no integer REFERENCES productos (producto_no), cantida integer);
PostgeSQL no nos dejara crear pedidos sobre productos que no existan
![Page 25: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/25.jpg)
4.25
Triggers (una interrupción) Triggers (una interrupción) ejemploejemplo
Un “trigger” es un pedazo de codigo SQL que se ejecuta automaticamente cuando se invoca una cierta acción
SQL 99 (subset)
Por ejemplo: Para guardar la fecha de actualización:
![Page 26: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/26.jpg)
4.26
TriggersTriggerstemplate1,plpgsql-sqltemplate1,plpgsql-sql
CREATE TABLE tomate( tomate_no int PRIMARY KEY, color char(10),
modificadoen timestamp);
DROP FUNCTION modificacion();CREATE FUNCTION modificacion() RETURNS TRIGGER AS 'BEGIN NEW.modificadoen := ''now''; RETURN NEW;END;'LANGUAGE 'plpgsql' WITH (isstrict);
DROP TRIGGER t_modificacion on tomate;CREATE TRIGGER t_modificacion BEFORE INSERTON tomateFOR EACH ROW EXECUTE PROCEDURE modificacion();
![Page 27: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/27.jpg)
4.27
Clave ExtranjeraClave Extranjera
¿Qué pasa si se borra el producto_no en la tabla productos?
CREATE TABLE productos (
producto_no integer PRIMARY KEY,
nombre text,
precio numeric);
CREATE TABLE pedidos (
pedido_no integer PRIMARY KEY,
producto_no integer REFERENCES productos
(producto_no),
cantida integer); ¿Qué pasa si se borra/cambia el producto_no en la tabla
productos?
![Page 28: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/28.jpg)
4.28
Poblar las tablas anterioresPoblar las tablas anteriores
INSERT into productos VALUES (1,'aaa',12.1);
INSERT into productos VALUES (2,'bbb',12.1);
INSERT into pedidos VALUES (1,1,5);
INSERT into pedidos VALUES (2,3,7);
![Page 29: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/29.jpg)
4.29
Clave Extranjera Clave Extranjera ejemploejemplo
CREATE TABLE productos ( producto_no integer PRIMARY KEY, nombre text, precio numeric);
CREATE TABLE pedidos ( pedido_no integer PRIMARY KEY, producto_no integer REFERENCES productos (producto_no), cantida integer);
CREATE TABLE producto_pedido ( producto_no integer REFERENCES productos ON DELETE
RESTRICT, pedido_no integer REFERENCES pedidos ON DELETE
CASCADE, cantidad integer, PRIMARY KEY (producto_no, pedido_no) );“According to the SQL standard, specifying either RESTRICT or CASCADE is
required. No database system actually implements it that way, but whether the default behavior is RESTRICT or CASCADE varies across systems. “
![Page 30: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/30.jpg)
4.30
Clave primaria y Clave ExtranjeraClave primaria y Clave Extranjera
La clave primaria y extranjera pueden usar los mismos atributos
CREATE TABLE producto_pedido (
producto_no integer REFERENCES productos,
pedido_id integer REFERENCES orders,
cantidad integer,
PRIMARY KEY (producto_no, pedido_id)
); Importante, una clave extranjera no puede ser clave extranjera
de otra relación.
CREATE TABLE inventario (
product_no integer REFERENCES pedidos
producto_no,
![Page 31: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/31.jpg)
4.31
Modificar tablas:“Drop” y “Alter” Modificar tablas:“Drop” y “Alter” ejemploejemplo
La orden drop table nombre_de_la_tabla elimina la tabla nombre_de_la_tabla (y toda información relacionada con ella) de la base de datos.
La orden alter table nombre_de_la_tabla se usa para añadir atributos a una relación.
alter table productos add column A integer
El valor inicial de los atributos es NULL (a menos que se especifique un valor por defecto).
La orden alter table se puede usar para borrar atributos de una tabla
alter table productos drop A
![Page 32: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/32.jpg)
4.32
Añadir/Modificar una restricción Añadir/Modificar una restricción ejemploejemplo
ALTER TABLE productos ADD CHECK (name <> '');
ALTER TABLE productos ADD CONSTRAINT some_name UNIQUE (producto_no);
ALTER TABLE productos ADD FOREIGN KEY (producto_group_id) REFERENCES product_groups;
ALTER TABLE productos ALTER COLUMN product_no SET NOT NULL;
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
ALTER TABLE products RENAME COLUMN product_no TO product_number;
![Page 33: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/33.jpg)
4.33
Herencia Herencia ejemploejemplo
Una tabla puede estar basada en otra (ISA)
CREATE TABLE ciudades (
nombre char(30),
poblacion float,
altura int -- en metros
);
CREATE TABLE capitales (
pais char(30)
) INHERITS (ciudades);
Cada tupla de capitales contiene TODOS los atributos de ciudades. ¿Qué pasa con capitales si “altero” ciudades?
![Page 34: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/34.jpg)
4.34
Etc…Etc…
Las tablas son los objeto “centrales” en una base de datos pero no son los únicos objetos que existen en una base de datos: Vistas
Funciones
Triggers
etc…
![Page 35: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/35.jpg)
Ejemplo BancoEjemplo Banco
![Page 36: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/36.jpg)
4.36
Ejemplo Banco IEjemplo Banco I
sucursal (nombre-sucursal, ciudad-sucursal, capital)
cliente (nombre-cliente, calle-cliente, ciudad-cliente)
cuenta (numero-cuenta, nombre-sucursal, saldo)
prestamo (numero-prestamo, nombre-sucursal, cantidad)
cliente-cuenta (nombre-cliente^, número-cuenta^)
cliente-prestamo (nombre-cliente^, numero-prestamo^)
![Page 37: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/37.jpg)
4.37
Ejemplo Banco II Ejemplo Banco II error/ejemploerror/ejemplo
CREATE TABLE sucursal(nombre-sucursal char(30),ciudad-sucursal char(30),capital char(30),PRIMARY KEY (nombre-sucursal))
CREATE TABLE cliente(nombre-cliente char(30),calle-cliente char(30),ciudad-cliente char(30),PRIMARY KEY (nombre-cliente ))
Debemos poner NOT NULL a todo?
CREATE TABLE cuenta(numero-cuenta numeric(25),nombre-sucursal char(30),saldo numeric(15,2) NOT NULL,PRIMARY KEY (numero-cuenta ))
CREATE TABLE prestamo (numero-prestamo numeric(25),nombre-sucursal char(30),cantidad numeric (15,2) NOT
NULL,PRIMARY KEY (numero-cuenta ))
Hay al menos tres errores en estas ordenes: ; - vs. _ numero_cuenta
![Page 38: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/38.jpg)
4.38
Ejemplo Banco IIIEjemplo Banco III
CREATE TABLE cliente_cuenta (
nombre_cliente char(30) REFERENCES cliente,
numero_cuenta numeric(25) REFERENCES cuenta,
PRIMARY KEY (nombre_cliente, número_cuenta)
);
CREATE TABLE cliente_prestamo (
nombre_cliente char(30) REFERENCES cliente,
numero_prestamo numeric(25) REFERENCES prestamo,
PRIMARY KEY (nombre_cliente, número_prestamo)
);
![Page 39: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/39.jpg)
4.39
Última preguntaÚltima pregunta
CREATE TABLE i1 (i int, I int);
CREATE TABLE i2 ('i' int, 'I' int);
CREATE TABLE i3("i" int, "I" int);
comentar pgaccess
![Page 40: SQL-Data Definition Language (DDL) Lenguaje de Definición de Datos](https://reader036.fdocument.pub/reader036/viewer/2022081503/568157c9550346895dc550a2/html5/thumbnails/40.jpg)
FINFIN