Nos permite unicamente ver los datos que tenemos almacenados en la BD.

Estructura basica de un query select.

SELECT

[DISTINCT] *, columns, or expressions

[FROM tabla(s)]

[JOIN tabla(s) ON condicion(es)]

[WHERE condicion(es)]

[GROUP BY columna(s)]

[HAVING condicion(es)]

[ORDER BY columna(s)]

Los alias son opcionales, pero mejoran la visualización de los datos.

Podemos asignar el alias justo a la derecha del campo, expresion o tabla, de forma opcional podemos agregar la palabra “AS” y luego el alias.

Un alias es una forma de renombrar un campo, expresion o tabla, en el caso de las columnas y expresiones el alias asignado será lo que se visualizará en el resultado.

En el caso de alias para tablas puede ser una forma abreviada de referirse a la tabla, son muy practicos cuando se hacen Joins entre varias tablas.

Cuando en un mismo query hay varias tablas, y mas de una tabla tienen columnas con el mismo nombre un alias puede ayudar a reducir ambiguedad.

SELECT first_name primer_nombre, last_name AS primer_apellido FROM actor;

SELECT first_name, last_name FROM actor A;

SELECT first_name, last_name FROM actor AS A;

Define una condición (o varias) que debe cumplirse para que los datos sean devueltos. Los operadores utilizados en la cláusula WHERE (o cualquier condición definida en la cláusula) no tienen efecto en los datos almacenados en las tablas. Sólo afectan a los datos devueltos cuando se invoca la vista. Se puede incluir en una instrucción SELECT, UPDATE o DELETE.

PostgreSQL evalua la clausula WHERE despues del FROM y antes del SELECT yORDER BY.

Si se asignan alias a las columnas en el SELECT , esos alias no pueden ser usados en el WHERE.

Operadores de Comparación

• Típicos (=, !=, <>, <, <=, >, >=)

• AND: Para unir dos condiciones, ambas deben ser verdaderas.

• OR: Para unir dos condiciones, una condición debe ser verdadera.

• IS NULL: Para obtener las filas donde X columna tiene valor null.

• BETWEEN: para identificar un rango de valores.

• NOT: Para negar una condición.

• LIKE: es posible especificar valores que son solamente similares a los valores almacenados.

• IN: permite determinar si los valores en la columna especificada de una tabla están contenidos en una lista definida o contenidos dentro de otra tabla.

La sintaxis de la clausula IN es la siguiente:

valor IN (valor1, valor2, ...)

valor IN (SELECT columna FROM tabla);

Al evaluar la condicion con el operador IN se retornará true si value hace match con almenos un elemento de la lista ej., value1 , value2 , …

SELECT customer_id, rental_id, return_date

FROM rental

WHERE customer_id IN (1, 2)

ORDER BY return_date DESC;

Una alternativa a usar el IN podria ser utilizar varias condiciones utilizando OR.

SELECT rental_id, customer_id, return_date

FROM rental

WHERE customer_id = 1

OR customer_id = 2

ORDER BY return_date DESC;

El query con el operador IN es mas corto y legigle que el query con igual (=) y OR. Ademas, PostgreSQL ejecuta el query con IN mas rapido que la lista de OR.

Tambien es posible combinar los operadores IN y NOT para seleccionar el valor que no esta incluido en la lista.

SELECT customer_id, rental_id, return_date

FROM rental

WHERE customer_id NOT IN (1, 2);

Ejemplo del operador IN con una subconsulta o subquery.

SELECT customer_id, rental_id, return_date

FROM rental

WHERE customer_id IN (SELECT customer_id FROM customer WHERE active = 1 );

Sirve para evaluar si un valor se encuentra dentro de un rango de valores.

La sintaxis es:

valor BETWEEN limite_inferior AND limite_superior;

Y es equivalente a:

valor >= limite_inferior and valor <= limite_superior

Para evaluar que el valor no este en un rango de valores se puede combinar con NOT.

valor NOT BETWEEN limite_inferior AND limite_superior;

El cual seria equivalente a:

valor < limite_inferior and valor > limite_superior

Ejemplo:

SELECT customer_id, payment_id, amount

FROM payment

WHERE amount BETWEEN 8 AND 9;

SELECT customer_id, payment_id, amount

FROM payment

WHERE amount NOT BETWEEN 8 AND 9;

SELECT customer_id, payment_id, amount, payment_date

FROM payment

WHERE payment_date BETWEEN '2007-02-07' AND '2007-02-15';

SELECT first_name, RIGHT(first_name, 1) final

FROM actor

WHERE RIGHT(first_name, 1) BETWEEN 'a' AND 'e';

Es posible especificar valores que son solamente similares a los valores almacenados.

• Signo de porcentaje (%): representa cero o más caracteres desconocidos.

• Guión bajo (_): representa exactamente un carácter desconocido.

Los comodines % y _ pueden ser usados en cualquier posición del patron, pueden usarse ambos comodiones en un mismo patron y pueden repetirse n veces.

SELECT first_name, last_name

FROM customer

WHERE first_name LIKE 'A%';

SELECT first_name, last_name

FROM customer

WHERE first_name LIKE '_her%'

ORDER BY first_name;

El string ‘A%’ es llamado patron.

El operador like se puede combinar con el operador NOT para filtrar las filas que no cumplan el patron.

SELECT first_name, last_name

FROM customer

WHERE first_name NOT LIKE 'A%';

Si el patrón dentro del operador LIKE no contiene ningun comodin (% o _) entonces el comportamiento sera el mismo que el operador igual ( =).

SELECT first_name, last_name

FROM customer

WHERE first_name LIKE 'Jamie';

SELECT first_name, last_name

FROM customer

WHERE first_name = 'Jamie';

Tambien existe el operador ILIKE el cual es similar a LIKE. Con la diferencia que el operador ILIKE verifica el patron sin importar mayusculas y minisculas (case-insensitive).

Con la instrucción UPDATE se pueden modificar datos en una o más filas para una o más columnas.

• La cláusula UPDATE y la cláusula SET son obligatorias, mientras que la cláusula WHERE es opcional.

• La cláusula WHERE funciona aquí de una forma muy parecida a como lo hace en la instrucción SELECT.

• Solamente las filas que cumplen con estas condiciones son actualizadas.

UPDATE nombre_tabla

SET columna1 = valor1,

    columna2 = valor2,

    ...

WHERE condicion(es)

[RETURNING * | output_expression AS output_name];

Contiene solamente dos cláusulas, y una sola de ellas es obligatoria.

• La cláusula WHERE, la cual es similar a la cláusula WHERE en una instrucción SELECT y en una instrucción UPDATE, requiere que se especifique una condición de búsqueda.

• Si no se incluye una cláusula WHERE en la instrucción DELETE, todas las filas serán eliminadas de la tabla especificada.

• Es importante comprender que la instrucción DELETE no elimina la tabla en sí, sino solamente filas en la tabla (la instrucción DROP TABLE, se utiliza para eliminar definiciones de tabla de la base de datos).

* Es muy importante ser cuidados al momento de ejecutar un query DELETE para no eliminar registros que no se debian eliminar.**

Al eliminar con el id (llave primaria) nos aseguramos que solo una fila podrá ser eliminada.

Con otras condiciones que abarquen mas filas podriamos eliminar mas filas.

Sintaxis:

DELETE FROM nombre_tabla

WHERE condicion(es);

DELETE

FROM country

WHERE country_id = 141;

DELETE

FROM country

WHERE country_id >= 141

AND country_id <= 150;

DELETE

FROM country;

DELETE

FROM country

WHERE country_id >= 139

RETURNING country_id;

Lenguaje de definición de datos

Se usan para crear, modificar o borrar objetos en una base de datos como tablas, vistas, activadores, y almacenar procedimientos.

Los nombres de los objetos deben de ser unicos.

Las instrucciones DDL más comunes son:

CREATE, ALTER y DROP.

Ejemplos:

• CREATE TABLE para crear una tabla.

• ALTER TABLE para modificar las características de una tabla.

• DROP TABLE para borrar la definición de la tabla de la base de datos.

CREATE DATABASE dvdrental;

¿Que es una Tabla?

Son objetos de esquema que tienen los datos de SQL.

Tipos de Tablas:

• Tablas persistentes

• Tablas Temporales

• Tablas Variables

Sintaxis del create table:

CREATE TABLE [IF NOT EXISTS] nombre_tabla (

   columna1 tipodedato(longitud) contraint_de_columna,

   columna2 tipodedato(longitud) contraint_de_columna,

   columna3 tipodedato(longitud) contraint_de_columna,

   contraints_de_tabla

);

Constraints

Con el fin de asegurar la integridad de los datos, SQL proporciona una serie de restricciones de integridad, reglas que se aplican a la base de datos para restringir los valores que se pueden colocar en esas tablas. Se pueden aplicar restricciones a columnas individuales, a tablas individuales o a múltiples tablas.

Constraints más comunes:

• NOT NULL: Asegura que en una columna no haya valores nulos.

• UNIQUE: Asegura que cada valor sea único.

• PRIMARY KEY: Una combinación de NOT NULL y UNIQUE. Identifica cada fila de una tabla.

• FOREIGN KEY: Evita que se destruya la relación entre tablas.

• CHECK: Garantiza que los valores de una columna cumplan una condición.

• DEFAULT: Asigna un valor por default cuando ningún valor es especificado.

• CREATE INDEX: Sirve para leer información más rápidamente.

Los constraints podemos definirlos al crear las tablas o podemos utilizar ALTER TABLE para definirlos.

Una vez definida una tabla, es posible cambiar su estructura por medio de sentencias ALTER.

Sintaxis alter table:

ALTER TABLE nombre_tabla accion;

Podemos usar la clausula ALTER para:

• Agregar llave foranea

• Agregar columnas

• Renombrar columnas

• Eliminar columnas

• Modificar la llave primaria.

• Agregar contraints.

ALTER TABLE users ADD COLUMN birthdate DATE;

ALTER TABLE users ADD COLUMN state DATE;

ALTER TABLE users DROP COLUMN state;

ALTER TABLE users RENAME COLUMN birthdate TO birth_date;

SELECT *

FROM users;

ALTER TABLE users RENAME TO dvdrental_users;

Podemos usar DROP para eliminar objetos de la base de datos o toda la base de datos:

DROP eliminar el objeto no únicamente los datos.

DROP DATABASE nombre_base_de_datos;

DROP TRIGGER nombre_trigger;

DROP FUNCTION nombre_funcion;

DROP TABLE dvdrental_users;

Un componente importante de cualquier base de datos relacional es la correlación que puede existir entre dos tablas cualesquiera. En SQL podemos unir las tablas en una instrucción. Una operación join es una operación que hace coincidir las filas en una tabla con las filas de manera tal que las columnas de ambas tablas puedan ser colocadas lado a lado en los resultados de la consulta como si éstos vinieran de una sola tabla.

Joins:

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL OUTER JOIN

• SELF JOIN

• CROSS JOIN

• NATURAL JOIN

Realizan operaciones sobre un grupo o un set de datos. Comúnmente son utilizadas con la cláusula GROUP BY para generar grupos y resultados sobre esos grupos.

Algunas funciones comunes

• AVG: Para promediar valores

• COUNT: Para contar registros

• COUNT(DISTINCT): Para contar registros únicos.

• MAX: Devuelve el valor máximo.

• MIN: Devuelve el valor mínimo.

• SUM: Para sumar valores.

• STRING_AGG: concatena una lista de strings y pone un separador entre ellos.

• ARRAY_AGG: acepta un grupo de valores y los retorna en un array.

Identificar duplicados con COUNT

La función COUNT puede ser utilizada para contar duplicados.

GROUP BY

Es posible utilizar más de una función de agregación en un mismo query. Todos los campos individuales que están junto a la función de agregación en la clausula SELECT debe ir tambien en la clausula GROUP BY.

HAVING

A diferencia de la cláusula WHERE, la cláusula HAVING se refiere a grupos, no a filas individuales. Se aplica a los resultados después de haberse agrupado (en la cláusula GROUP BY). Tiene la ventaja de permitir el uso de funciones establecidas tales como AVG o SUM, que no se pueden utilizar en la cláusula WHERE a menos que se coloquen dentro de una subconsulta.

Sirven para combinar el resultado de varios queries como un solo resultado.

• El número de columnas y el orden en el SELECT de ambos queries debe de ser el mismo.

• Los tipos de datos deben de ser compatibles.

• UNION excluye del resultado los duplicados en la combinación.

• UNION ALL incluye en el resultado los duplicados den la combinación.

Sintaxis:

SELECT campo(s)

FROM nombre_tabla_A

UNION | UNION ALL | INTERSECT

SELECT campos(s)

FROM nombre_tabla_B

Proporcionan una forma de acceder a datos en múltiples tablas con una sola consulta. Puede agregarse a una instrucción SELECT, INSERT, UPDATE o DELETE para permitir a esa instrucción utilizar los resultados de la consulta arrojados por la subconsulta. La subconsulta es esencialmente una instrucción SELECT incrustada que actúa como una puerta de entrada a los datos en una segunda tabla.

Se pueden en dos categorías generales:

• Las que pueden arrojar múltiples filas

• Las que pueden arrojar solamente un valor

Tabla derivada

El resultado de la subconsulta tambien puede ser utilizado como que fuera una tabla asignandole un alias y seleccionar datos de ese resultado.

Una subconsulta tambien puede tener mas subconsultas, como una cadena.

• Al utilizar una varias subconsultas es importante ser cuidados ya que puede llegar a afectar significativamente el rendimiento del query.

Es un conjunto de resultados con nombre temporal al que puede hacer referencia dentro de una instrucción SELECT, INSERT, UPDATE o DELETE. El CTE también se la puede usar en una vista.

Sintaxis:

WITH + alias + AS + (QUERY CTE) Query que hace referencia al CTE

WITH Actor_films AS (

SELECT actor_id, COUNT(film_id) total_films

FROM film_actor

GROUP BY actor_id

)

SELECT A.actor_id, A.first_name, A.last_name, F.total_films

FROM actor A

INNER JOIN Actor_films F ON F.actor_id = A.actor_id

WHERE F.total_films >= 40;

Es una tabla virtual que a diferencia de las tablas base persistentes, en la vista no hay datos almacenados.

Permiten seleccionar información específica de una o más tablas, basada en las instrucciones de consulta en esa definición. Una vez que se crea una vista, simplemente se invoca llamándola por su nombre en una consulta como en una tabla base. Los datos entonces se presentan como si se buscaran en una tabla base.

CREATE VIEW actors_with_A

AS

SELECT actor_id, last_name, first_name

FROM actor

WHERE last_name LIKE 'A%';

SELECT *

FROM actors_with_A;

SELECT A.first_name, A.last_name, F.title, F.description

FROM film F

INNER JOIN film_actor FA ON FA.film_id = F.film_id

INNER JOIN actors_with_A A ON A.actor_id = FA.actor_id

WHERE F.rental_duration > 5;

DCL (Data Control Language)

Lenguaje de control de datos

Un usuario en una base de datos puede ser una persona o un programa de aplicación

Permiten controlar quién o qué usuario tiene acceso a objetos específicos en la base de datos.

Las instrucciones DCL permiten controlar el tipo de acceso que cada usuario tiene a los objetos de una base de datos.

Las principales instrucciones DCL son:

GRANT y REVOKE.

Ejemplos:

• GRANT para dar permiso de lectura a una tabla.

• REVOKE para quitar todos los permisos a un usuario sobre una tabla.

GRANT + Privilegios + ON + nombre_objeto + TO nombre_usuario;

REVOKE + Privilegios + ON + nombre_objeto + FROM nombre_usuario;

• GRANT sentencia para otorgar permisos.

• SELECT para asignar permiso de lectura sobre la tabla especificada.

• INSERT para asignar permiso de inserción sobre la tabla especificada.

• UPDATE para asignar permiso de actualización sobre la tabla especificada.

• DELETE para asignar permiso de eliminación sobre la tabla especificada.

• ON luego de este texto se indica el nombre de la tabla a la cual se le quiere asignar los permisos.

• TO luego de este texto se indica el nombre del usuario al cual se le quiere asignar los permisos.

• ALL PRIVILEGES para asignar todos los permisos sin listarlos uno por uno.

Roles

Para evitar asignar permisos de forma individual a cada usuario, los grupos de permisos se pueden agrupar por roles y luego asignar el rol a un usuario.

Un usuario puede tener más de un rol y un rol puede tener muchos permisos y usuarios.