Clase 14 - Guion de clase

Vista previa del material en texto

Clase 14. Lenguaje Estructurado de Consulta SQL. Parte 1
Introducción
Structured Query Language (SQL) es un lenguaje de alto nivel para la manipulación de bases de datos relacionales. Se dice que es un lenguaje declarativo porque a través de él se indica qué se quiere hacer, pero no cómo hay que hacerlo. La ventaja de este enfoque es que el usuario que interacciona con la base de datos no requiere (al menos en un nivel inicial) conocer cómo internamente se ejecuta la consulta, sino que es el motor de la base de datos quien se encarga de traducir las sentencias SQL en un conjunto de líneas de código de bajo nivel. De esta manera, cualquier persona con conocimiento del modelo de datos en cuestión puede interactuar con la base de datos para realizar consultas, añadir nuevos registros, eliminarlos, actualizarlos, modificar los objetos que conforman la base de datos e incluso modificar la estructura de la misma.
Las sentencias que provee SQL pueden clasificarse en alguno de los siguientes sublenguajes:
· Lenguaje de Definición de Datos (DDL, Data Definition Language): consta de un conjunto de sentencias para la definición y modificación de la base de datos y sus tablas.
· Lenguaje de Manejo de Datos (DML, Data Manipulation Language): consta de un conjunto de sentencias para la manipulación de lo datos almacenados.
· Lenguaje de Control de Datos (DCL, Data Control Language): consta de un conjunto de sentencias para la administración de los privilegios de los distintos usuarios que se conectarán a la base de datos
· Lenguaje de Control de Transacciones (TCL, Transactions Control Language): consta de un conjunto de sentencias para la gestión de transacciones. Una transacción es un conjunto de sentencias que se ejecutan como si fuesen una sola. En general, las sentencias que forman parte de una transacción se interrelacionan entre sí, y no tiene sentido que se ejecute una sin que se ejecuten las demás. 
A lo largo del curso se mostrarán ejemplos utilizando la base de datos de W3School provista en este enlace. Si bien la herramienta cuenta con varias limitaciones en comparación con un sistema de gestión de bases de datos real, será suficiente para permitirnos un primer acercamiento a SQL, realizar consultas, dar de alta nuevos registros y modificar los existentes.
Lenguaje de Definición de Datos (DDL)
El Lenguaje de Definición de Datos consta de un conjunto de sentencias que permiten modificar los objetos que conforman la base de datos. Mediante este tipo de operaciones es posible definir nuevas tablas, modificarlas, eliminarlas o vaciarlas.
Las sentencias básicas de DDL son:
· CREATE
· ALTER
· DROP
· TRUNCATE
Sentencia CREATE
Definición del esquema de la base de datos
La creación de la base de datos y su esquema de puede realizarse de dos maneras:
1. Utilizando la interfaz gráfica del Sistema Gestor de Base de Datos (SGBD)
2. Por medio de la sentencia CREATE SCHEMA.
CREATE SCHEMA shop;
Luego de la creación, se deberá seleccionar la base de datos recién creada con la sentencia USE:
USE shop;
A partir de aquí, todas las sentencias SQL que se ejecuten harán referencia a la base de datos shop.
La sentencia CREATE también puede utilizarse para definir las tablas que conformarán la base de datos. Para crear una tabla de clientes con columnas CustomerID, CustomerName, ContactName, Address, City, PostalCode y Country ejecute
CREATE TABLE 'shop'.'Customers' (
 'CustomerID' INT,
 'CustomerName' VARCHAR(40),
 'ContactName' VARCHAR(40),
 'Address' VARCHAR(45),
 'City' VARCHAR(25),
 'PostalCode' VARCHAR(15),
 'Country' VARCHAR(25),
 PRIMARY KEY ('CustomerID')
);
A cada columna se asigna un tipo de dato, los tipos de datos principales son:
· INT o INTEGER: Un entero de longitud media
· CHAR(n): Una cadena de caracteres de longitud fija n.
· VARCHAR(n): Una cadena de caracteres de longitud variable. Es posible fijar una longitud máxima n entre los paréntesis.
· BOOL o BOOLEAN: Un valor booleano, donde 0 indica falso y 1 indica verdadero.
· DECIMAL(size, d): Un número decimal de punto fijo. Opcionalmente se puede especificar la cantidad total de dígitos en size y la cantidad de dígitos posteriores al punto decimal en d.
Restricciones de columna
Las restricciones de columna permiten agregar una capa de seguridad a las columnas de una tabla, con el objetivo de evitar la inserción de registros inválidos, por ejemplo, que contengan valores nulos en campos requeridos.
Una determinada columna puede tener una, ninguna o varias de las siguientes restricciones:
· NOT NULL: indica que la columna no puede tener valores nulos.
· UNIQUE: indica que la columna no puede tener valores repetidos.
· PRIMARY KEY: indica que la columna es la clave primaria de la tabla, y por ende no puede tener valores nulos ni repetidos.
· REFERENCES: indica que la columna es clave foránea a la columna referenciada de la tabla referenciada.
· CHECK: indica que la columna debe cumplir las condiciones especificadas.
Para el ejemplo de clientes, en todo momento nos interesa toda la información que dispongamos de ellos. Para ello, se debe especificar en la definición de la tabla que todas las columnas tengan la restricción NOT NULL. Para realizar este cambio, se puede proseguir de dos maneras: 
· elimina la tabla con la sentencia DROP TABLE, e inmediatamente crea otra con las restricciones necesarias:
	DROP TABLE shop;
	CREATE TABLE 'shop'.'Customers' (
 'CustomerID' INT NOT NULL,
 'CustomerName' VARCHAR(40) NOT NULL,
 'ContactName' VARCHAR(40) NOT NULL,
 'Address' VARCHAR(45) NOT NULL,
 'City' VARCHAR(25) NOT NULL,
 'PostalCode' VARCHAR(15) NOT NULL,
 'Country' VARCHAR(25) NOT NULL,
 PRIMARY KEY ('CustomerID')
);
· modifica la tabla existente para incorporar las restricciones con la sentencia ALTER TABLE:
ALTER TABLE 'shop'.'Customers' 
CHANGE COLUMN 'CustomerName' 'CustomerName' VARCHAR(45) NOT NULL ,
CHANGE COLUMN 'ContactName' 'ContactName' VARCHAR(45) NOT NULL ,
CHANGE COLUMN 'Address' 'Address' VARCHAR(45) NOT NULL ,
CHANGE COLUMN 'City' 'City' VARCHAR(45) NOT NULL ,
CHANGE COLUMN 'PostalCode' 'PostalCode' VARCHAR(15) NOT NULL ,
CHANGE COLUMN 'Country' 'Country' VARCHAR(25) NOT NULL ;
Si bien ambas maneras son válidas, es preciso tener algunas consideraciones cuando tenga que realizar modificaciones sobre la tabla cargada:
· Si opta por la primera forma, perderá toda la información almacenada en la tabla.
· Si opta por la segunda forma, obtendrá un error al ejecutar la sentencia si, por ejemplo, intenta imponer una restricción del tipo NOT NULL en una columna que contiene valores nulos o bien si intenta imponer una restricción del tipo UNIQUE en alguna columna que contiene valores repetidos.
Definiciones por defecto
Es posible asignar valores por defecto para determinadas columnas agregando DEFAULT, tanto en la creación de la tabla como en la modificación de la misma
ALTER TABLE 'shop'.'Consumer' 
CHANGE COLUMN 'Country' 'Country' VARCHAR(25) NOT NULL DEFAULT 'Argentina' ;
Referencias a otras columnas
Suponga que cuenta con una tabla Categories con información relacionada a las distintas categorías de producto que comercializa el negocio. Al propietario del mismo le interesa registrar productos con su respectiva categoría, para lo cual se deberá especificar una columna CategoryID al momento de creación de la tabla Products. Esta columna será clave foránea a la columna CategoryID de la tabla Categories.
CREATE TABLE 'shop'.'Products' (
 'ProductID' INT NOT NULL,
 'ProductName' VARCHAR(20) NOT NULL,
 'SupplierID' INT NOT NULL,
 'CategoryID' INT NOT NULL,
 'Unit' VARCHAR(15) NOT NULL,
 'Price' DECIMAL() NOT NULL,
 PRIMARY KEY ('ProductID'),
 CONSTRAINT 'fk_Products_Category'
 FOREIGN KEY ('CategoryID')
 REFERENCES 'shop'.'Categories' ('CategoryID')
 ON DELETE RESTRICT
 ON UPDATE CASCADE);
Observaciones:
· Las sentencia ON DELETE RESTRICT indica a la base de datos que deberá dar un error si se intenta eliminar una categoría para la cual existen productos asociados. Esto se realiza para mantener laintegridad de los datos almacenados, ya que en caso contrario podrían quedar productos sin tener una categoría asignada.
· La sentencia ON UPDATE CASCADE indica a la base de datos que deberá permitir realizar actualizaciones de la columna CategoryID de la tabla Categories, pero que estas modificaciones deberán propagarse en cascada hacia el resto de tablas que la referencian.
· A la clave foránea se le debe asignar un nombre único, en este caso se la llamó ‘fk_Products_Category’.
Borrado del contenido de una tabla
En ocasiones se requiere vaciar por completo el contenido de una tabla para luego seguir utilizándose. Una opción es borrar por completo la tabla con la sentencia DROP TABLE y luego volver a crearla con la sentencia CREATE TABLE. Sin embargo, existe una opción más conveniente: eliminar el contenido con la sentencia TRUNCATE.
TRUNCATE TABLE Products;
Lenguaje de Manipulación de Datos (DML)
El Lenguaje de Manipulación de Datos consta de un conjunto de sentencias que permiten llevar a cabo las tareas de consulta o modificación de los datos contenidos en la base de datos.
Las principales sentencias DML son:
· SELECT FROM: para consultar una o más columnas de una tabla.
· INSERT: para insertar de nuevas filas a una tabla.
· UPDATE: para actualizar las filas de una tabla.
· DELETE: para eliminar las filas de una tabla.
La sentencia SELECT FROM tiene la particularidad de que no produce ningún cambio en el estado de la base de datos y simplemente puede pensarse como una operación de lectura de la información almacenada. En cambio, las tres restantes sí que tienen la capacidad de producir algún cambio en los registros almacenados, por lo que se deberá prestar especial atención al utilizarlas ya que un simple error al escribir una sentencia puede corromper la información almacenada.
Consultas simples
La sentencia SELECT FROM permite consultar una o más columnas de una única tabla. 
Para seleccionar un subconjunto específico de columnas:
SELECT CustomerID, CustomerName, Address FROM Customers;
O bien se pueden consultar todas la columnas utilizando la wildcard *
SELECT * FROM Customers;
Para añadir filtros sobre la consulta se añade la cláusula WHERE seguida de una condición de guarda booleana que se evalúa para cada fila resultado de la consulta. Por ejemplo, para consultar los productos con categoría 5 ejecutar
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE CategoryID = 5
En toda cláusula WHERE, es posible filtrar por varias condiciones mediante los operadores AND y OR. Por ejemplo
SELECT ProductID, ProductName, CategoryID, Price
FROM Products
WHERE CategoryID = 5 
AND Price < 20;
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE CategoryID = 5 
	OR CategoryID = 7;
A su vez, podemos asignarle un alias opcional a cada tabla involucrada en la consulta. Esto permitirá luego referenciar la tabla por medio de su alias, lo que ahorrará mucho tiempo de escritura a medida que se vayan complejizando las consultas. 
SELECT p.ProductID, p.ProductName, p.CategoryID 
FROM Products AS p
WHERE p.CategoryID = 5
Predicados simples
Un predicado es una condición que se evalúa resultando en dos valores posibles: verdadero o falso. Su utilidad reside al incluirlos dentro de las cláusulas WHERE para facilitar el filtrado de los resultados. 
Los predicados básicos que se incluyen en SQL son:
· BETWEEN
· IS NULL
· LIKE
Predicado BETWEEN
Retorna verdadero si los valores almacenados en una determinada columna están entre los dos valores que se especifican. Funciona con varios tipos de datos como INT, FLOAT, DATE, TIME y DATETIME.
Por ejemplo, puede ser útil para consultar un rango de productos
SELECT ProductID, ProductName, 
FROM Products
WHERE ProductID BETWEEN 3 AND 15;
o bien para consultar sólo aquellas órdenes de compra realizadas entre el 04/07/1996 y 10/07/1996:
SELECT * FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-04’ AND ‘1996-07-10’
NOTA: el formato de la fecha no es al que estamos acostumbrados. Si bien algunos sistemas de bases de datos aceptan fechas en distintos formatos, el estándar comúnmente aceptado es AAAA-MM-DD.
Predicado IN
El predicado IN retorna verdadero si los valores de una determinada columna están en un determinado conjunto de valores.
Por ejemplo, es útil filtrar aquellos clientes que tienen un ID específico:
SELECT CustomerID, CustomerName FROM Customers
WHERE CustomerID IN (1, 7, 9);
Predicado LIKE
El predicado LIKE retorna verdadero si un determinado campo de tipo CHAR o VARCHAR cumple con un patrón determinado. Resulta útil para buscar palabras clave en una determinada columna. Por ejemplo,
SELECT * FROM PRODUCTS
WHERE ProductName LIKE 'Chef%';
retorna sólo aquellos productos cuyo nombre comienza con ‘Chef’.
Notar que la cadena de caracteres pasada a LIKE tiene una wildcard o carácter comodín “%”, que significa ‘varios, uno o ningún carácter, sin importar cuáles sean’. Por otro lado, existe también la wildcard “_” que significa ‘un único carácter, sin importar cuál sea’. Por ejemplo, 
SELECT * FROM PRODUCTS
WHERE ProductName LIKE '_h%';
retornará todos los productos cuyo nombre tiene una ‘h’ en la segunda posición.
Como se mencionó, el predicado LIKE es útil para buscar palabras clave en una determinada columna. Por ejemplo, se puede filtrar la columna Unit (unidad de medida) para mostrar sólo aquellos productos que se venden en botella
SELECT * FROM PRODUCTS
WHERE Unit LIKE '%bottle%';
Predicado IS NULL
El predicado IS NULL retorna verdadero si el campo está vacío. Por ejemplo, se puede utilizar para consultar aquellos productos que aún no tienen un precio asignado
SELECT ProductID, ProductName
FROM Products
WHERE Price IS NULL;
o bien para consultar únicamente aquellos productos que sí tienen un precio asignado
SELECT ProductID, ProductName
FROM Products
WHERE Price IS NOT NULL;