Capítulo 9 SQL

SQL es un lenguaje estructurado de consulta (Structured Query Language, en inglés). diseñado específicamente para administrar información en sistemas de gestión de bases de datos de tipo relacional.

Puede considerarse un lenguaje de programación como tal, ya que cuenta con uso de variables, tipos de datos, elementos condicionales y lógicos. Es el estandar de facto para la gestión de datos y permite:

  • Consultar, actualizar y reorganizar datos
  • Crear y modificar la estructura de datos
  • Controlar el acceso a los datos

El uso de este lenguaje es altamente imperativo para todo profesional que tenga por objetivo acceder a altos volumenes y/o datos complejos.

SQL se creó en los 70s en los laboratorio de IBM como un lenguaje para manejar los datos de un sistema de gestión de datos llamado System R. Posteriormente en 1979 Oracle generó su propia versión denominada Oracle v2.

Mapa de visualización de las áreas de uso de SQL.

9.1 Tipos de Datos

Los tipos de datos soportados lo son por la mayoría de lso sistemas de bases de datos que soportan SQL, salvo algunas excepciones que son particulares a un sistema especifico. Tomar en cuenta que PostgreSQL utiliza adicionalmente tipos geométricos y direcciones de redes.11

En general los tipos de datos incluyen:

  • Numéricos
  • Moneda
  • Caracter
  • Binario
  • Fecha/hora
  • Lógicos (booleanos)
  • Enumerados
  • Geometricos
  • Redes
  • Bit String
  • Texto
  • UUID
  • XML
  • JSON
  • Arreglos
  • Compuestos (Composite)
  • Rangos
  • Identificadores de objetos
SELECT CAST(123 AS DECIMAL(5,2)) --returns 123.00
SELECT CAST(12345.12 AS NUMERIC(10,5)) --returns 12345.12000

Numericos

Los de tipo numéricos incluyen enteros y decimales con un almacenamiento desde -32768 a +32767 para “smallint”, hasta +9223372036854775807 para un “bigint

Serial Los tipos serial (serial, smallserial, bigserial no son tipos efectivos sino una convención para anotar un identificador único auto incrementado). AUTO_INCREMENT en otras bases de datos.

CREATE TABLE person (
    id SERIAL
)

Char y varchar

--
CHARACTER VARYING(n), VARCHAR(n)
CHARACTER (n), CHAR(n)

SELECT CAST('ABC' AS CHAR(10)) -- 'ABC ' (padded with spaces on the right)
SELECT CAST('ABC' AS VARCHAR(10)) -- 'ABC' (no padding due to variable character)
SELECT CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS CHAR(10)) -- 'ABCDEFGHIJ' (truncated to 10 characters)

Arrays

-- declarando un arreglo
SELECT INTEGER[];
SELECT INTEGER[3]; 
SELECT INTEGER[][]; 
SELECT INTEGER[3][3]; 
SELECT INTEGER ARRAY; 
SELECT INTEGER ARRAY[3];
-- creando un arreglo
SELECT '{0,1,2}';
SELECT '{{0,1},{1,2}}';
SELECT ARRAY[0,1,2];
SELECT ARRAY[ARRAY[0,1],ARRAY[1,2]];

9.2 Data Definition Language (DDL)

9.2.1 CREATE

CREATE TABLE person (
person_id BIGINT NOT NULL, 
last_name VARCHAR(255) NOT NULL, 
first_name VARCHAR(255),
age INT NOT NULL,
PRIMARY KEY (person_id)
);
-- alternativamente define la clave primaria en forma directa
CREATE TABLE person (
person_id BIGINT NOT NULL PRIMARY KEY, 
last_name VARCHAR(255) NOT NULL, 
first_name VARCHAR(255),
address VARCHAR(255),
city VARCHAR(255)
);

Crear una tabla a partir de otra existente.

-- crea una nueva tabla a partir de los datos de "person" que con registros que tengan sobre 30 años
CREATE TABLE people_over_30 AS SELECT * FROM person WHERE age > 30;

9.2.2 ALTER

9.2.3 DROP

9.2.4 TRUNCATE

9.3 Data manipulation Language (DML)

Un lenguaje de manipulación de datos (Data Manipulation Language, o DML en inglés) es una parte del lenguaje SQL que incluido por el sistema de gestión de base de datos permite a los usuarios llevar a cabo las tareas de consulta o manipulación de los datos, organizados por el modelo de datos adecuado.

DML esta orientado por tanto a la interacción con los datos propiamente tales dentro de la base de datos. Esta interacción incluye la selección, inserción, actualización y eliminación de datos (registros dentro de una BD).

9.3.1 SELECT

SELECT permite recuperar registros desde cero o más tablas. WHERE filtra los registros antes del agrupamiento y HAVING filtra los grupos creados por GROUP BY.

SELECT en PostgreSQL

[ WITH [ RECURSIVE ] with_query [, ...] ] 
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ [ AS ] output_name ] [, ...] 
[ FROM from_item [, ...] ] 
[ WHERE condition ] 
[ GROUP BY expression [, ...] ] 
[ HAVING condition [, ...] ] 
[ WINDOW window_name AS ( window_definition ) [, ...] ] 
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] 
[ ORDER BY expression [ ASC | DESC | USING operator ] 
[ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] 
[ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] 
{ ROW | ROWS } ONLY ] 
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]column2, ... 
FROM table_name;
SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

select *  from T_CLIENTES;
select *  from T_PEDIDOS;
SELECT *  from T_PRODUCTOS;

9.3.2 INSERT

Realiza operaciones de tipo INSERT, UPDATE y/o SELECT sobre una table destino basado en los resultados de un JOIN con una table en una transacción.

INSERT INTO table_name VALUES (value1, value2, value3,…);
table_name: name of the table.
value1, value2,.. : value of first column, second column,… 
INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2, value3,..);
table_name: name of the table.
column1: name of first column, second column …
value1, value2, value3 : value of first column, second column,… 
insert into UTILES  (codigo, descripcion, fecalta) values (1,'Lapiz',sysdate);
insert into UTILES  (codigo, descripcion, fecalta) values (2,'Goma',sysdate);
insert into UTILES  (codigo, descripcion, fecalta) values (3,'Sacapuntas',null);
insert into UTILES  values (4,'regla',sysdate);
insert into UTILES  values (5,'escuadra',null);
insert into UTILES  (codigo, descripcion)  values (6,'transportador');
insert into UTILES  (descripcion, codigo)  values ('compas',7);
insert into UTILES  (fecalta, codigo, descripcion ) values (sysdate, 8, 'estuche');

9.3.3 BULK INSERT

Realiza operaciones de tipo INSERT, UPDATE y/o SELECT sobre una table destino basado en los resultados de un JOIN con una table en una transacción.

SQL Server 2016 en adelante

BULK INSERT TableName FROM 'filePath’ 
WITH ( FORMAT = ‘CSV’, FIELDTERMINATOR = ‘,’, 
ROWTERMINATOR = '\n’, 
ROWS_PER_BATCH = 10000, FIRSTROW = 2, TABLOCK );

9.3.4 UPDATE

La sentencia UPDATE es usada para actualizar los datos de una tabla existente dentro de la base de datos.

Se pueda actualizar desde una a varias columnas según se requiera. Si se omite la clausula WHERE, el comando afecta a todos los registros de la tabla.

UPDATE estudiantes SET NAME = 'PRATIK' WHERE id = 20;
UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition;

donde: + table_name: nombre de la tabla + columnN: nombre de la columna + valueN: nuevo valor de la columna + condition: condición requerida para seleccionar las filas que deben ser actualizadas.

UPDATE nombre_tabla 
SET nombre_columna = valor  
[ WHERE condición [ AND condición] ]  

UPDATE UTILES SET  descripcion =  ‘Regla 30 cm’ WHERE  codigo= 4
UPDATE UTILES SET  fecalta =  sysdate;
UPDATE utiles SET fecalta = sysdate, descripcion=‘regla 30 cm.’ WHERE código = 4;

9.3.5 DELETE

Realiza operaciones de eliminación de registros desde las tablas.

DELETE FROM nombre_tabla 
WHERE nombre_columna = valor
DELETE FROM personas 
WHERE nombre = 'LUIS' AND apellido1 = 'LOPEZ' AND apellido2 = 'PEREZ'
DELETE nombre_tabla 
[ WHERE condición [ AND condición] ]

delete UTILES WHERE  codigo= 7;
delete UTILES;

9.3.6 Ejemplos

En el primer ejemplo de código, se devuelven todas las filas (no se especifica la cláusula WHERE) y todas las columnas (con *) de la tabla Product de la base de datos AdventureWorks2012.

USE AdventureWorks2012;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
USE AdventureWorks2012;
GO
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO

9.4 Data Control Language (DCL)

9.4.1 Ejemplos

9.5 Transaction Control Language (TCL)

9.5.1 Ejemplos

9.6 SQL Keywords

Tabla 9.1: Palabras claves en SQL (selección)
Keyword Detail
ADD Adds a column in an existing table
ADD CONSTRAINT Adds a constraint after a table is already created
ALTER Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table
ALTER COLUMN Changes the data type of a column in a table
ALTER TABLE Adds, deletes, or modifies columns in a table
ALL Returns true if all of the subquery values meet the condition
AND Only includes rows where both conditions is true
ANY Returns true if any of the subquery values meet the condition
AS Renames a column or table with an alias
ASC Sorts the result set in ascending order
BACKUP DATABASE Creates a back up of an existing database
BETWEEN Selects values within a given range
CASE Creates different outputs based on conditions
CHECK A constraint that limits the value that can be placed in a column
COLUMN Changes the data type of a column or deletes a column in a table
CONSTRAINT Adds or deletes a constraint
CREATE Creates a database, index, view, table, or procedure
CREATE DATABASE Creates a new SQL database
CREATE INDEX Creates an index on a table (allows duplicate values)
CREATE OR REPLACE VIEW Updates a view
CREATE TABLE Creates a new table in the database
CREATE PROCEDURE Creates a stored procedure
CREATE UNIQUE INDEX Creates a unique index on a table (no duplicate values)
CREATE VIEW Creates a view based on the result set of a SELECT statement
DATABASE Creates or deletes an SQL database
DEFAULT A constraint that provides a default value for a column
DELETE Deletes rows from a table
DESC Sorts the result set in descending order
DISTINCT Selects only distinct (different) values
DROP Deletes a column, constraint, database, index, table, or view
DROP COLUMN Deletes a column in a table
DROP CONSTRAINT Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint
DROP DATABASE Deletes an existing SQL database
DROP DEFAULT Deletes a DEFAULT constraint
DROP INDEX Deletes an index in a table
DROP TABLE Deletes an existing table in the database
DROP VIEW Deletes a view
EXEC Executes a stored procedure
EXISTS Tests for the existence of any record in a subquery
FOREIGN KEY A constraint that is a key used to link two tables together
FROM Specifies which table to select or delete data from
FULL OUTER JOIN Returns all rows when there is a match in either left table or right table
GROUP BY Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
HAVING Used instead of WHERE with aggregate functions
IN Allows you to specify multiple values in a WHERE clause
INDEX Creates or deletes an index in a table
INNER JOIN Returns rows that have matching values in both tables
INSERT INTO Inserts new rows in a table
INSERT INTO SELECT Copies data from one table into another table
IS NULL Tests for empty values
IS NOT NULL Tests for non-empty values
JOIN Joins tables
LEFT JOIN Returns all rows from the left table, and the matching rows from the right table
LIKE Searches for a specified pattern in a column
LIMIT Specifies the number of records to return in the result set
NOT Only includes rows where a condition is not true
NOT NULL A constraint that enforces a column to not accept NULL values
OR Includes rows where either condition is true
ORDER BY Sorts the result set in ascending or descending order
OUTER JOIN Returns all rows when there is a match in either left table or right table
PRIMARY KEY A constraint that uniquely identifies each record in a database table
PROCEDURE A stored procedure
RIGHT JOIN Returns all rows from the right table, and the matching rows from the left table
ROWNUM Specifies the number of records to return in the result set
SELECT Selects data from a database
SELECT DISTINCT Selects only distinct (different) values
SELECT INTO Copies data from one table into a new table
SELECT TOP Specifies the number of records to return in the result set
SET Specifies which columns and values that should be updated in a table
TABLE Creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table
TOP Specifies the number of records to return in the result set
TRUNCATE TABLE Deletes the data inside a table, but not the table itself
UNION Combines the result set of two or more SELECT statements (only distinct values)
UNION ALL Combines the result set of two or more SELECT statements (allows duplicate values)
UNIQUE A constraint that ensures that all values in a column are unique
UPDATE Updates existing rows in a table
VALUES Specifies the values of an INSERT INTO statement
VIEW Creates, updates, or deletes a view
WHERE Filters a result set to include only records that fulfill a specified condition

9.7 Procedimientos

Los procedimientos, más comunmente conocidos como procedimientos almacenados (Store Procedure) son conjunto de instrucciones destinado a ejecutar un proceso específico. estos pueden aceptar parámetros de entrada y pueden devolver ya sea un valor o un conjunto de registros.

9.8 Funciones

9.9 Disparadores (Triggers)