Bases de Datos Relacionales

Texto completo

(1)

Bases de Datos Relacionales Bases de Datos Relacionales

4ta. Parte

(2)

Objetivos de la Clase

o Sentencias SQL Select, Create, Insert, update, delete, truncate, drop.

o Modificar definiciones de tablas.

o Valores nulos

o Restricciones.

(3)

Sentencia Create

CREATE TABLE table_name ( { < column_definition > | < table_constraint > } [ ,...n ] ) Donde:

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ] ] } ][ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

……….

Ej: CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar (50))

CREATE TABLE MyCustomers2 (CustID int CONSTRAINT pkCustId PRIMARY KEY) CREATE TABLE MyOrders (OrderID int,

CustID int REFERENCES MyCustomers(CustID))

(4)

Lenguaje SQL Lenguaje SQL

o o CREATE CREATE

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][, ...]);

CREATE TABLE departments ( department_id int NOT NULL

,department_name VARCHAR(30) NOT NULL ,manager_id int

)

CREATE TABLE departments ( department_id int NOT NULL

,department_name VARCHAR(30) NOT NULL ,manager_id int

)

(5)

Sentencia Select into

La instrucción SELECT INTO crea una nueva tabla y la llena con el conjunto de resultados de la instrucción SELECT.

SELECT INTO se puede emplear para combinar datos de varias tabla s o vistas en una tabla.

Ejemplo:

SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode

INTO EmployeeAddresses FROM Contact AS c

JOIN Employee AS e ON e.ContactID = c.ContactID

JOIN EmployeeAddress AS ea ON ea.EmployeeID = e.EmployeeID JOIN Address AS a on a.AddressID = ea.AddressID

JOIN StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;

(6)

Lenguaje SQL Lenguaje SQL

o o INSERT INSERT

INSERT [ INTO ] objeto [ ( lista de columnas ) ] INSERT [ INTO ] objeto [ ( lista de columnas ) ] VALUES ( ( { DEFAULT | NULL | expresi

VALUES ( ( { DEFAULT | NULL | expresi ón } [ ,...n ] ) [ ó n } [ ,...n ] ) [ ,...n ] )

,...n ] )

INSERT INTO departments (department_id,

department_name, manager_id) VALUES ( 100 , 'Finance' , 108 )

INSERT INTO departments (department_id,

department_name, manager_id) VALUES ( 100 , 'Finance' , 108 )

(7)

Lenguaje SQL Lenguaje SQL

o o INSERT DESDE UN SELECT INSERT DESDE UN SELECT

INSERT [ INTO ] objeto [ ( lista de columnas ) ] INSERT [ INTO ] objeto [ ( lista de columnas ) ] (SELECT [(lista columnas)] FROM Tabla

(SELECT [(lista columnas)] FROM Tabla

INSERT INTO FACTURA (nromedidor ,periodo,FecEmision,Importe)

(select m.nromedidor, p.periodo, getdate(), ((m.estact - m.estant) * p.valormt) from MEDIDOR m

INNER JOIN SOCIO s ON s.nrosocio = m.nrosocio

inner join categoria c on c.codcategoria = s.codcategoria cross join periodo p)

INSERT INTO FACTURA (nromedidor ,periodo,FecEmision,Importe)

(select m.nromedidor, p.periodo, getdate(), ((m.estact - m.estant) * p.valormt) from MEDIDOR m

INNER JOIN SOCIO s ON s.nrosocio = m.nrosocio

inner join categoria c on c.codcategoria = s.codcategoria cross join periodo p)

(8)

Lenguaje SQL Lenguaje SQL

o o UPDATE UPDATE

UPDATE objeto UPDATE objeto

SET nombre columna = { expresi

SET nombre columna = { expresi ón | DEFAULT | ó n | DEFAULT | NULL }

NULL } [ ,...n ] [ ,...n ] [ FROM{ <

[ FROM{ < tabla tabla> } [ ,...n ] ] > } [ ,...n ] ] [ WHERE { <

[ WHERE { <condici condició ón n> ] > ]

UPDATE departments

SET department_name = ‘Account' Where department_id = 100

UPDATE departments

SET department_name = ‘Account' Where department_id = 100

(9)

Sentencia Alter table

Modifica una definición de tabla, agrega o quita columnas y restricciones.

Ejemplos:

Agrega nueva columna:

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL ;

Quita una Columna:

ALTER TABLE doc_exb DROP COLUMN column_b ;

Cambia el tipo de datos:

ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;

Agrega una columna con una restricción:

ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ;

Agrega una restricción no comprobada:

ALTER TABLE doc_exd WITH NOCHECK

ADD CONSTRAINT exd_check CHECK (column_a > 1)

Agrega una restricción default:

ALTER TABLE doc_exz ADD CONSTRAINT col_b_def

DEFAULT 50 FOR column_b ;

(10)

Sentencias Delete y truncate

La instrucción DELETE quita una o varias filas de una tabla o vista.

Ej:

1) DELETE

[FROM] tablax WHERE col1 = 100

2) DELETE FROM SalesPersonQuotaHistory WHERE SalesPersonID IN

(SELECT SalesPersonID FROM SalesPerson WHERE SalesYTD > 2500000.00 )

La instrucción TRUNCATE TABLE es un método rápido y no registrado para eliminar todas las filas de una tabla. TRUNCATE TABLE es funcionalmente e quivalente a la instrucción DELETE sin una cláusula WHERE. Sin embargo, TRUNCATE TABLE es m ás rápida y utiliza menos recursos de registro de sistema y de transa cciones.

La instrucción DELETE quita una a una las filas y graba una entrada en el reg istro de transacciones por cada fila eliminada.

Ej:

TRUNCATE table

(11)

Sentencia Drop Table

Elimina la definición de una tabla y todos los datos, índices, restricciones y especificaciones de permisos asociados.

Realiza un borrado físico de la tabla.

DROP TABLE < table_name >

Ejemplo:

DROP TABLE doc_exz

(12)

VALORES NULOS

NULL = NULL ? Funcion ISNULL()

Ej: Create table persona (id int, apellido varchar(20)) Insert into persona (id, apellido)

Values (1,null)

Create table historico_persona (id int, apellido varchar(20)) Insert into historico_persona (id, apellido)

Values (2,null) select *

from persona a

inner join historico_persona h on a.apellido = h.apellido

(13)

¿Qué Son las Restricciones?

o Las restricciones fuerzan las reglas a nivel de tabla.

o Las restricciones evitan la supresión de un registro si hay dependencias.

o Son válidos los siguientes tipos de restricción:

n NOT NULL n UNIQUE

n PRIMARY KEY n FOREIGN KEY n CHECK

n DEFAULT

(14)

Instrucciones sobre Restricciones

o Asigne un nombre a una restricción o Cree una restricción:

n Al mismo tiempo que se crea la tabla, o bien n Una vez creada la tabla.

o Defina una restricción a nivel de columna o de tabla.

o Visualice una restricción.

(15)

Definición de Restricciones

o Nivel de restricción de columna

o Nivel de restricción de tabla

column [CONSTRAINT constraint_name] constraint_type, column [CONSTRAINT constraint_name] constraint_type,

column,...

[CONSTRAINT constraint_name] constraint_type (column, ...),

column,...

[CONSTRAINT constraint_name] constraint_type (column, ...),

(16)

La Restricción NOT NULL

Asegura que no se permiten valores nulos para la columna:

Restricción NOT NULL (Ninguna fila puede contener

un valor nulo para

Ausencia de restricción NOT NULL

(Cualquier fila puede contener un valor nulo Restricción

NOT NULL

(17)

CREATE TABLE employees(

employee_id int,

last_name VARCHAR(25) NOT NULL, salary decimal(8,2),

commission_pct decimal(2,2),

Order_date datetime NOT NULL )

La Restricción NOT NULL

Se define a nivel de columna:

(18)

La Restricción UNIQUE

EMPLOYEES

Restricción UNIQUE

INSERT INTO

No permitido:

ya existe Permitido

(19)

La Restricción UNIQUE

Definida a nivel de tabla o de columna:

CREATE TABLE employees(

employee_id INT,

last_name VARCHAR(25) NOT NULL, email VARCHAR(25),

salary DECIMAL(8,2), commission_pct DECIMAL(2,2),

hire_date DATETIME NOT NULL, CONSTRAINT emp_email_uk UNIQUE(email))

(20)

La Restricción PRIMARY KEY

DEPARTMENTS

PRIMARY KEY

INSERT INTO No permitido

(valor nulo)

No permitido

(21)

CREATE TABLE departments(

department_id INT,

department_name VARCHAR(30) NOT NULL, manager_id INT,

location_id INT,

CONSTRAINT dept_id_pk PRIMARY KEY(department_id))

La Restricción PRIMARY KEY

Definida a nivel de tabla o de columna:

(22)

Definición de Restricciones:

A nivel de columna y a nivel de tabla

CREATE TABLE employees(

employee_id int CONSTRAINT emp_emp_id_pk PRIMARY KEY,

first_name VARCHAR(20),

job_id VARCHAR(10) NOT NULL)

CREATE TABLE employees(

employee_id int,

first_name VARCHAR(20),

job_id VARCHAR(10) NOT NULL, CONSTRAINT emp_emp_id_pk

PRIMARY KEY (EMPLOYEE_ID))

(23)

La Restricción FOREIGN KEY

DEPARTMENTS

EMPLOYEES

FOREIGN KEY

INSERT INTO No permitido (9 no existe)

Permitido Permitido PRIMARY

KEY

(24)

La Restricción FOREIGN KEY

Definida a nivel de tabla o de columna:

CREATE TABLE employees(

employee_id int,

last_name VARCHAR(25) NOT NULL, email VARCHAR(25),

salary decimal(8,2), commission_pct decimal(2,2),

hire_date DATETIME NOT NULL, department_id int,

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),

CONSTRAINT emp_email_uk UNIQUE(email));

(25)

Palabras Clave de la

Restricción FOREIGN KEY

• FOREIGN KEY : Define la columna de la tabla secundaria a nivel de restricción de tabla.

• REFERENCES: Identifica la tabla y la columna en la

tabla principal.

(26)

La Restricción CHECK

Especifica los valores que acepta un campo, evitando que se ingr esen valores inapropiados

CREATE TABLE employees(

employee_id int,

last_name VARCHAR(25) NOT NULL, email VARCHAR(25),

salary decimal(8,2), commission_pct decimal(2,2),

hire_date datetime NOT NULL, department_id int,

CONSTRAINT Employee_chk_slary

CHECK (Salary > 0 AND SALARY < 20000));

(27)

La Restricción DEFAULT

Especifica un valor por defecto para una columna

CREATE TABLE employees(

employee_id int,

last_name VARCHAR(25) NOT NULL, email VARCHAR(25),

salary decimal(8,2), commission_pct decimal(2,2),

hire_date datetime NOT NULL default getdate(), department_id int);

(28)

Ejemplo: DEFAULT + CHECK

Las restricciones pueden aplicarse en forma conjunta

CREATE TABLE persona

(dni int,

Ayn varchar(50),

sexo varchar(1) not null default 'M', CONSTRAINT persona_sexo CHECK (sexo in('M','F'))) CREATE TABLE persona

(dni int,

Ayn varchar(50),

sexo varchar(1) not null default 'M', CONSTRAINT persona_sexo CHECK (sexo in('M','F')))

(29)

Adición de una Sintaxis de Restricción

Utilice la sentencia ALTER TABLE para:

o Agregar o borrar una restricción, sin modificar su estructura

o Activar o desactivar restricciones

o Agregar una restricción NOT NULL utilizando la cláusula ALTER COLUMN

ALTER TABLE table

ADD [CONSTRAINT constraint] type (column);

ALTER TABLE table

ADD [CONSTRAINT constraint] type (column);

(30)

Restricción Recursiva

Agregue una restricción FOREIGN KEY a la tabla EMPLOYEES que indique que ya debe existir un

director como empleado válido en la tabla EMPLOYEES.

CREATE TABLE employees(

employee_id int, manager_id int,

first_name VARCHAR(20),

job_id VARCHAR(10) NOT NULL,

CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)) ALTER TABLE employees

ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id)

REFERENCES employees(employee_id);

(31)

Eliminación de una Restricción

o Elimine la restricción de director de la tabla EMPLOYEES.

ALTER TABLE employees

DROP CONSTRAINT emp_manager_fk Tablas del diccionario de datos:

sys.objects

sys.key_constraints sys.foreign_keys

ALTER TABLE employees

DROP CONSTRAINT emp_manager_fk Tablas del diccionario de datos:

sys.objects

sys.key_constraints sys.foreign_keys

(32)

-- Tablas y Constraints SELECT *

FROM sys.objects

WHERE name LIKE ‘%emp%‘

-- Ver Constraints definidos SELECT *

FROM sys.key_constraints WHERE name LIKE ‘emp%'

Diccionario de Datos

Tablas y Constraints:

(33)

BIBLIOGRAFIA

o Un Enfoque Practico del SQL.

ISBN 9789871076611. Morteo Francisco A. y Bocalandro Nicolas L.E. Editorial COOPERATIVAS

o ORACLE 11g. SQL, PL/SQL, SQL*PLUS.

ISBN 9782746053601. Gabillaud Jerome.

o Oracle SQL and PL/SQL Handbook.

ISBN 9780201752946. John Adolph Palinski

o SQL: Guia práctica para usuarios.

ISBN 9788441519152. Charte Ojeda, Francisco. Editorial ANAYA

o E. F. Codd, The Relational Model for Database

Management Version 2 (Reading, Mass.: Addison -Wesley, 1990).

o Varios Sitios WEB.

(34)

ESPACIO PARA PREGUNTAS

Figure

Actualización...

Referencias

Actualización...