Fuente: https://www.youtube.com/watch?v=jxIEDKzGrOs&list=PL8gxzfBmzgex2nuVanqvxoTXTPovVSwi2
Programas utilizados: Pgadmin4 y Postgres.
CREATE DATABASE
CREATE DATABASE nombre_database;
Creamos una base de datos
llamada: nombre_database
.
-- Este es el script que se genera si lo hacemos a traves de la interfaz grafica de pgadmin4:
CREATE DATABASE nombre_database
WITH
OWNER = ownername
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;
Creamos una base de datos
llamada: nombre_database
, damos el nombre del dueño de la base de datos: ownername
(para este ejemplo). La codificación, en este caso es UTF8
y el límite de conexiones en -1.
DROP DATABASE IF EXISTS
DROP DATABASE IF EXISTS "nombre_database";
-- Tenemos que estar desconectados de la base de datos a borrar.
Borramos la base de datos si existe
llamada: nombre_database
.
CREATE TABLE
CREATE TABLE
persona(
id_persona int NOT NULL,
nombre varchar(20),
dni varchar(10)
);
Creamos una tabla
llamada persona
que tiene 3 columnas: id_persona
, nombre
y dni
.
INSERT
INSERT INTO
persona
VALUES
('3', 'Maria', '12345');
Insertamos dentro
de la tabla persona
los valores en todas las columnas que tiene la tabla (id_persona
, nombre
y dni
).
INSERT INTO
persona(nombre, dni)
VALUES
('Maria', '12345');
Insertamos dentro
de la tabla persona
únicamente los valores en las columnas previamente dichas: nombre
y dni
.
SELECT
SELECT
*
FROM
persona;
Lista todos los registros con todas las columnas (ya que seleccionamos
con el carácter *
que refiere a todo
) de la tabla persona
.
WHERE AND
SELECT
*
FROM
persona
WHERE
id > 2;
Lista todos los registros donde
el id
de la entidad sea mayor a 2.
--... igual al anterior
WHERE
id > 2 AND nombre = 'Jose';
Lista todos los registros donde
el id
de la entidad sea mayor a 2 y
la columna nombre
sea igual a Jose
.
UPDATE
UPDATE
persona
SET
nombre = 'Jose'
WHERE
id = 2;
Actualiza
la tabla persona
actualizando
, valga la redundancia, la columna nombre
al valor Jose
donde
el id
sea igual a 2.
ALTER
ALTER TABLE
ALTER TABLE persona
ADD COLUMN apellido varchar(20);
-- No se puede poner NOT NULL si ya la tabla persona contiene registros/datos agregados.
Alteramos la tabla persona
y añadimos una columna apellido
con límite de 20 caracteres.
ALTER TABLE persona
RENAME COLUMN apellido TO nombre;
Alteramos la tabla persona
y renombramos una columna de apellido
a nombre
.
ALTER TABLE persona
DROP COLUMN nombre;
Alteramos la tabla persona
y eliminamos la columna nombre
ALTER COLUMN
ALTER TABLE persona
ALTER COLUMN nombre SET NOT NULL;
Alteramos la columna nombre
para decir que no puede tener NULL
como valor.
ALTER TABLE persona
ALTER COLUMN nombre DROP NOT NULL;
Alteramos la columna nombre
para borrar la restricción de la columna de no poder tener NULL
como valor. Ahora acepta NULL
como valor
ALTER TABLE persona
ALTER COLUMN nombre TYPE character varying;
-- No se puede poner tipos de datos que no acepten el tipo de dato al que pertenece el dato ya existente en la columna (en el caso de que dicho dato exista)
Alteramos la columna nombre
para cambiar el tipo de dato aceptado a character varying
.
SERIAL
CREATE TABLE
persona(
id serial PRIMARY KEY NOT NULL,
-- Otras columnas...
);
Creamos una tabla donde la columna id es de tipo serial
, esto quiere decir que va a generar automáticamente e incrementándose cuando insertemos datos.
DELETE DROP TRUNCATE
DELETE
DELETE FROM persona;
Vacía la tabla persona
(borra todos los registros). Cuando se agregan nuevos registros los id generados automáticamente no empiezan desde 1, sino que siguen desde el último id de la entidad borrada. 1, 2, 3; new=> 4, 5, 6.
DROP
DROP TABLE persona;
Borra la tabla persona
con sus registros. Se tiene que volver a crear la tabla y los id generados automáticamente empiezan desde 1.
TRUNCATE
TRUNCATE TABLE persona;
Vacia la tabla persona
como DELETE
.
TRUNCATE TABLE persona RESTART IDENTITY;
Vacía la tabla persona
. Cuando se agregan nuevos registros los id generados automáticamente empiezan desde 1.
VALORES POR DEFAULT
CREATE TABLE
persona(
--... otras columnas
telefono varchar(10) DEFAULT 'Unknown'
);
Creamos una tabla con una columna llamada telefono
con un valor por default
de 'Unknown'
COLUMNAS CALCULADAS
SELECT
nombre,
salario,
(salario + 1500) AS bono
FROM
plantilla;
Lista los registros con 3 columnas, la última es una creada a partir del salario
+ 1500 y es nombrada como bono
ORDER BY
SELECT
*
FROM
persona
ORDER BY
nombre DESC;
Lista todos los registros ordenadas por nombre
de forma descendente
.
--... igual que el anterior.
ORDER BY
salario ASC;
Lista todos los registros ordenadas por salario
de forma ascendente.
LIKE
SELECT
nombre
FROM
persona
WHERE
nombre LIKE '%e%';
Muestra los nombres de personas que tengan una letra e
minúscula en el campo nombre
.
--... igual que el anterior
nombre LIKE 'e%';
Muestra los nombres de las personas que tengan una letra e
minúscula como primera letra en el campo nombre
.
--... igual que el anterior
nombre LIKE '%E';
Muestra los nombres de las personas que tengan una letra e
mayúscula como última letra en el campo nombre
.
--... igual que el anterior
nombre LIKE '%e_';
Muestra los nombres de las personas que tengan una letra e
minúscula como anteúltima letra en el campo nombre
.
COUNT
SELECT
COUNT(*)
FROM
plantilla;
Muestra una columna de la suma de los registros dentro de plantilla
SUM
SELECT
SUM(salario)
FROM
planilla;
Muestra en una columna la suma total de todos los valores de salario
.
MIN MAX
-- Usando MIN
SELECT
MIN(salario)
FROM
plantilla;
Muestra en una columna el salario
con el valor más bajo.
-- Usando MAX
SELECT
MAX(salario)
FROM
plantilla;
Muestra en una columna el salario
con el valor más alto.
AVG
SELECT
AVG(salario)
FROM
planilla;
Saca el promedio entre todos los números de la columna.
SELECT
nombre,
AVG(salario)
FROM
plantilla
GROUP BY
nombre;
Saca el promedio dentro de cada grupo por nombre.
HAVING
Para usarlo necesitamos la cláusula GROUP BY
.
WHERE
trabaja con SELECT
y HAVING
trabaja con GROUP BY
.
SELECT
nombre, salario
FROM
plantilla
WHERE
nombre = 'Jose'
GROUP BY
nombre, salario
HAVING
salario > 3000;
Muestra los registros de nombre
'Jose' que en salario
tengan más de 3000.
DISTINCT
SELECT
DISTINCT nombre
FROM
persona;
Lista los nombres de las diferentes registros. Si uno o muchos registros tienen el mismo nombre solo se muestra 1 vez. No muestra nombres
repetidos.
BETWEEEN
SELECT
*
WHERE
salario
BETWEEN
2000 AND 5000;
Lista todos los registros donde
el valor de la columna salario sea entre
2000 y 5000.
-- ... igual que el anterior
NOT BETWEEN
2000 AND 5000;
Lista todos los registros donde
el valor de la columna salario no sea entre
2000 y 5000.
UNIQUE
ALTER TABLE persona
ADD CONSTRAINT uq_salario
UNIQUE(salario)
Restringe que la columna salario
de la tabla persona
no pueda tener valores repetidos, tienen que ser único
.
DROP CONSTRAINT
ALTER TABLE persona
DROP CONSTRAINT uq_salario
Elimina la restricción uq_salario
. En este ejemplo quitamos la restricción de valor único
en la columna de salario
FOREIGN KEY
-- Añadimos una columna llamada codigo_empresa en la tabla 'planilla'.
ALTER TABLE planilla
ADD codigo_empresa integer
-- Añadimos una llave foranea tomando el id de la tabla 'empresa' para podes añadirla en la columna 'codigo_empresa'.
ALTER TABLE planilla
ADD CONSTRAINT test_fk
FOREIGN KEY(codigo_empresa)
REFERENCES empresa (id)
UPDATE planilla SET codigo_empresa = '2'
Creamos una llave foránea para la tabla planilla
en la columna codigo_empresa
, a partir de la columna id
de la tabla empresa
. Ahora en la columna codigo_empresa
de la tabla planilla
solo se pueden poner valores que tenga la columna id
de la tabla empresa
, de otra forma nos daría error.
FUNCTION (stored procedure)
Funcion con parametros con nombre:
-- Creamos la funcion o la reemplazamos en el caso de que exista, y la guardamos.
CREATE OR REPLACE FUNCTION
Suma (num1 int, num2 integer)
RETURNS
integer AS
$$
SELECT num1 + num2;
$$
LANGUAGE SQL;
-- Ejecutamos la funcion guardada y retornaria una columna 'suma' con valor 200.
SELECT Suma ('50', '150');
Creamos una funcion
llamada suma
con dos parametros de tipo int
e integer
llamados num1
y num2
respectivamente.
Declaramos que retorna
un integer
y entre pares de símbolo $
indicamos la lógica de la función.
Luego indicamos el lenguaje utilizado.
Finalmente llamamos la funcion suma
y le pasamos los dos argumentos que necesita.
Funcion con parametro sin nombre:
CREATE FUNCTION
SearchSalary(varchar)
RETURNS
integer AS
$$
SELECT salary FROM company
WHERE name = $1
$$
LANGUAGE SQL
SELECT SearchSalary('Eduard')
Creamos una funcion
llamada SearchSalary
, decimos que va a tener un parámetro de tipo varchar
y que retornara un integer
.
Hacemos referencia al primer parámetro con $1
(si queremos hacer referencia al segundo parámetro, si hubiera, lo haríamos con $2 y así).
Finalmente llamamos a la función y le pasamos el nombre para que nos retorne su salario.
Funcion sin parametros y sin retornar resultado:
CREATE FUNCTION
InsertPersons ()
RETURNS
void AS
$$
INSERT INTO planilla VALUES ('Armando','3','65000');
INSERT INTO planilla VALUES ('Esteban','4','46000');
INSERT INTO planilla VALUES ('Quito','5','92000');
$$
LANGUAGE SQL
SELECT InsertPersons()
Creamos una función
sin parámetros y no retorna ningún valor. Solo inserta los datos en la tabla planilla
.
CREATE FUNCTION
SearchInfo (int)
RETURNS
planilla AS
$$
SELECT * FROM planilla
WHERE id = $1
$$
LANGUAGE SQL
SELECT SearchInfo(1)
Creamos una función
que retorna una columna que solo tiene una fila y esta contiene todas las columnas de la tabla planilla
con los datos encontrados según el id
que hayamos pasado a la función SearchInfo
.
LIMIT (Top) OFFSET
LIMIT
SELECT
*
FROM
planilla
LIMIT
5
Lista los primeros 5 o el número que pongamos después de LIMIT
. Sirve para limitar el número de resultados/registros.
OFFSET
SELECT
*
FROM
planilla
LIMIT
3
OFFSET
4
A partir de la 4 fila/registro lista las 3 filas/registros siguientes.
-- 1 Pepe < OFFSET (Ignora)
-- 2 Juan < OFFSET (Ignora)
-- 3 Roberto < OFFSET (Ignora)
-- 4 Julio < OFFSET (Ignora)
-- 5 German < LIMIT (Lista)
-- 6 Ana < LIMIT (Lista)
-- 7 Sandra < LIMIT (Lista)
-- 8 Eduardo
-- 9 Maria
-- OUTPUT:
-- 5 German
-- 6 Ana
-- 7 Sandra
TRIGGER
-- Esta funcion se va a guardar dentro de 'Trigger Functions'
CREATE FUNCTION
SP_Test()
RETURNS
TRIGGER AS
$$
BEGIN
-- A la hora de hacer el UPDATE
-- 'old': Si quiero guardar la informacion que ya estan en la planilla.
-- 'new': Si quiero guardar la informacion nueva que se ingresa a la tabla.
INSERT INTO "Log_Triggers" VALUES (old.nombre, old.dni, old.salario);
-- Para que el Trigger haga algo es necesario un RETURN
RETURN NEW;
END
$$
-- Indicamos que vamos a usar codigo mas de postrgreSQL
LANGUAGE PLPGSQL;
-- Creamos el trigger, asignamos cuando se acciona y que ejecuta.
CREATE TRIGGER
TR_Update
BEFORE UPDATE ON
planilla
FOR EACH ROW
EXECUTE PROCEDURE SP_Test();
-- Para probar el trigger:
UPDATE
planilla
SET
nombre = 'Eduardo',
dni = '123456',
salario = '20000'
WHERE nombre = 'Eduard';
Creamos una función la cual va a ser ejecutada por el Trigger
. Esta función en el caso de usar old
toma los datos que estaban en la tabla planilla
antes de ser actualizados y los guarda dentro de la tabla Log_Triggers
. O en el caso de usar new
guarda los datos nuevos que se ingresan a la tabla.
Creamos el Trigger
, le decimos que se ejecute antes
de actualizar
la tabla planilla
y que por cada
fila
ejecute
el procedimiento
SP_Test()
. En este caso es la función que creamos líneas arriba.
CREATE FUNCTION
SP_TR_Insert()
RETURNS
TRIGGER AS
$$
-- Para declarar variables usamos 'DECLARE'
DECLARE
-- User => es reemplazado por el nombre de usuario que esta usando la BD.
username Varchar(250) := User;
datenow date := current_date;
timenow Time := current_time;
BEGIN
INSERT INTO
"Log_Triggers"
VALUES (new.nombre, new.dni, new.salario, username, datenow, timenow);
RETURN NEW;
END
$$
LANGUAGE PLPGSQL;
CREATE TRIGGER
TR_Insert
AFTER INSERT ON
planilla
FOR EACH ROW
EXECUTE PROCEDURE SP_TR_Insert();
Creamos una funcion
llamada SP_TR_Insert
. Dentro declaramos variables y guardamos el nombre del usuario que utiliza la base de datos, la fecha y la hora en la que se ejecuta la función.
En la lógica de la función hacemos que guarde los nuevos datos que se van a ingresar y las variables que declaramos líneas arriba.
Finalmente creamos el Trigger
que va a activarse después
de insertar
en la tabla planilla
. Por cada fila que ingresamos ejecuta la función SP_TR_Insert
.
OR IN
OR
SELECT
*
FROM
planilla
WHERE
dni = '2' OR dni = '11' OR dni = '3'
Listamos de la tabla planilla
todos los registros que en la tabla dni
tengan como valor 2, 11 o 3. Usando el operador lógico OR
IN
SELECT
*
FROM
planilla
WHERE
dni IN ('2','11','3')
Listamos de la tabla planilla
todos los registros que en la tabla dni
tengan como valor 2, 11 o 3. Pero esta vez usando IN
y al lado un paréntesis con el o los valores que deben tener para ser listados.
VIEW
-- Creamos una vista a partir de determinadas columnas de la tabla planilla:
CREATE VIEW
view_dataperson
AS SELECT
nombre,dni
FROM
planilla;
Creamos una vista
llamada view_dataperson
que muestre solo las columnas que nosotros le digamos. Si se realiza un cambio en la tabla planilla
también se va a cambiar en la vista view_dataperson
.
-- Podemos consultar la vista como si fuera una tabla normal:
SELECT
*
FROM
view_dataperson
UNION
SELECT
nombre,
dni
FROM
planilla
-- Si queremos no filtre los repetidos usamos 'UNION ALL' en vez de 'UNION'
UNION
SELECT
nombre,
id
FROM
persona;
Lista todos los registros que haya en la tabla planilla
y persona
con las columnas que seleccionemos. En este caso usamos UNION
por lo tanto los registros repetidos van a ser filtrados.
Si queremos que no se filtren los registros repetidos hay que agregar ALL
luego de UNION
quedando: UNION ALL
.
SELECT
nombre,
dni,
'Planilla' AS Origen
FROM
planilla
UNION ALL
SELECT
nombre,
id,
'Persona'
FROM
persona;
Lista todos los registros (hasta los repetidos) que haya en la tabla planilla
y persona
con las columnas que seleccionemos. Además creamos una columna nueva llamada Origen
la cual contiene el nombre de la tabla de la cual pertenece el registro 'Planilla' o 'Persona'.
CREATE VIEW
view_union
AS SELECT
nombre,
dni
FROM
planilla
UNION
SELECT
nombre,
id
FROM
persona;
Por último podemos crear una vista para guardar los registros y consultarlo después sin tener que armar la unión de las dos tablas.
INNER JOIN (Simple join)
SELECT
*
FROM
planilla
INNER JOIN
persona
ON
planilla.dni = persona.id
Lista los registros que en las columnas de las tablas seleccionadas tienen el mismo valor.
En este caso hacemos un INNER JOIN
entre las tablas planilla
y persona
donde la relación sea entre la columna dni
de planilla
y la columna id
de persona
.
( A | AB | B )
LEFT JOIN
SELECT
*
FROM
planilla
-- Es lo mismo escribir 'LEFT JOIN' que 'LEFT OUTER JOIN'
LEFT JOIN
persona
ON
planilla.dni = persona.id;
Lista todos los registro que existen en la tabla de la izquierda
que en este caso seria planilla
y los registros que comparten el mismo valor en la columna dni
e id
de la tabla planilla
y persona
respectivamente.
( A | AB | B )
RIGHT JOIN
SELECT
*
FROM
planilla
-- Es lo mismo escribir 'RIGHT JOIN' que 'RIGHT OUTER JOIN'
RIGHT JOIN
persona
ON
planilla.dni = persona.id;
Lista todos los registro que existen en la tabla de la derecha
que en este caso seria persona
y los registros que comparten el mismo valor en la columna dni
e id
de la tabla planilla
y persona
respectivamente.
( A | AB | B )
FULL JOIN
SELECT
*
FROM
planilla
-- Es lo mismo escribir 'FULL JOIN' que 'FULL OUTER JOIN'
FULL JOIN
persona
ON
planilla.dni = persona.id;
Muestra todas las columnas de la tabla planilla
y persona
una al lado de la otra con todos los registros de ambas tablas. Donde no haya relación entre la columna dni
de la tabla planilla
y la columna id
de la tabla persona
los campos van a ser rellenados con [null]
.
( A | AB | B )
CROSS JOIN
SELECT
*
FROM
planilla
CROSS JOIN
persona
Lista cada registro de la tabla de la izquierda
por el número de registros totales de la tabla derecha
.
CROSS JOIN
no utiliza ON
Ejemplo:
-- nombre dni salario id nombre cedula
-- Jose 2 2500 20 Francisco 12345
-- Jose 2 2500 21 Daniel 21354
-- Jose 2 2500 22 Pedro 35421
-- Maria 3 4500 20 Francisco 12345
-- Maria 3 4500 21 Daniel 21354
-- Maria 3 4500 22 Pedro 35421
-- David 4 6500 20 Francisco 12345
-- David 4 6500 21 Daniel 21354
-- David 4 6500 22 Pedro 35421
WITH CHECK OPTION
CREATE VIEW
view_person
AS SELECT
*
FROM
"Persona"
WHERE
"Pais" = 'Costa Rica';
Primero necesitamos una vista
INSERT INTO
view_person
VALUES
('12345', 'Manuel', 'Vargas', 'Colombia', '43');
Insertamos un registro en la vista view_person
el cual el 'Pais' del registro no es 'Costa Rica' sino 'Colombia'. Esto hará que no se inserte en la vista view_person
sino en la tabla "Persona" causando un registro "fantasma" y posiblemente generando errores a largo plazo.
Para evitar esto se utiliza WITH CHECK OPTION
:
CREATE VIEW
view_person
AS SELECT
*
FROM
"Persona"
WHERE
"Pais" = 'Costa Rica'
WITH CHECK OPTION;
Antes de realizar un UPDATE o un INSERT primero se va a fijar en el criterio de la vista, que "Pais" solo tenga de valor 'Costa Rica'. Si cumple va a seguir con la operación, pero si no, nos va a dar un error.
ABS CBRT CEILING FLOOR
ABS
SELECT ABS(-30);
-- Salida esperada: 30
Devuelve el valor absoluto
del número ingresado.
CRBT
SELECT CBRT(27);
-- Salida esperada: 3
Devuelve la raíz cubica
del número ingresado.
CEILING
SELECT CEILING(15.33);
-- Salida esperada: 16
Devuelve el número ingresado redondeado para arriba
.
FLOOR
SELECT FLOOR(15.58);
-- Salida esperada: 15
Devuelve el número ingresado redondeando para abajo
.
POWER ROUND SIGN SQRT
POWER
SELECT POWER(2,4);
-- Salida esperada: 16
Devuelve el primer número
ingresado (2) elevado a la potencia
del segundo número
ingresado (4).
ROUND
SELECT ROUND(24.82);
-- Salida esperada: 25
SELECT ROUND(24.824,2);
-- Salida esperada: 24.82
SELECT ROUND(24.825,2);
-- Salida esperada: 24.83
SELECT ROUND(24.8,4);
-- Salida esperada: 24.8000
Devuelve el primer número
ingresado redondeado, si se ingresa un segundo número
este va a determinar cuantos números decimales va a tener luego del punto.
Redondea para arriba a partir del 5.
Redondea para abajo a hasta el 4.
Si se necesitan mas decimales se le agregan 0 hasta cumplir con el segundo número ingresado
.
SIGN
SELECT SIGN(0);
-- Salida esperada: 0
SELECT SIGN(234);
-- Salida esperada: 1
SELECT SIGN(-234);
-- Salida esperada: -1
Devuelve 1 si el número ingresado es positivo.
Devuelve 0 si el número ingresado es 0.
Devuelve -1 si el número ingresado es negativo.
SQRT
SELECT SQRT(9);
-- Salida esperada: -1
Devuelve la raíz cuadrada
del número ingresado.
MOD PI RANDOM TRUNC
MOD
SELECT MOD(33,2);
-- Salida esperada: 1
Devuelve el resto de la división
del primer número
ingresado con respecto al segundo número
ingresado.
PI
SELECT PI();
-- Salida esperada: 3.141592653589793
Devuelve el número Pi
.
RANDOM
SELECT RANDOM();
-- Salida esperada: 0.699286726400242
-- Salida esperada: 0.6155701055762748
-- Salida esperada: 0.2833145983469656
Devuelve un número aleatorio
entre 0 y 1.
TRUNC
SELECT TRUNC(-57.35)
-- Salida esperada: -57
SELECT TRUNC(-57.35736434,2)
-- Salida esperada: -57.35
Si no se ingresa un segundo número, se retorna el primer número ingresado sin decimal.
Si se ingresa un segundo número, se retorna el primer número ingresado con tantos decimales según el segundo número ingresado.
CHAR_LENGTH UPPER LOWER POSITION
CHAR_LENGTH
SELECT CHAR_LENGTH('Hola mundo');
-- Salida esperada: 10
Retorna el número de caracteres
del texto ingresado.
UPPER
SELECT UPPER('Hola mundo');
-- Salida esperada: 'HOLA MUNDO'
Devuelve el texto ingresado pero en mayúsculas
LOWER
SELECT LOWER('Hola Mundo');
-- Salida esperada: 'hola mundo'
Devuelve el texto ingresado pero en minúscula
POSITION
SELECT POSITION('mundo' in 'Hola mundo');
-- Salida esperada: 6
Devuelve la posición del primer texto
ingresado dentro del segundo texto
ingresado.
Si no encuentra la palabra devuelve 0.
SUBSTRING TRIM
SUBSTRING
SELECT SUBSTRING('Hello World' FROM 2 FOR 6);
-- Salida esperada: 'ello W'
Desde la posición 2 (empieza a contar desde el 1) toma los caracteres hasta la posición 6 y lo retorna.
Sirve para extraer una sección del texto pasándole dos números refiriendo a la posición de inicio y fin de la extracción.
TRIM
SELECT TRIM(' Hola Mundo ');
-- Salida esperada: 'Hola Mundo'
Retorna el texto pero sin espacios
al principio
ni al final
.
SELECT TRIM(LEADING '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo---'
Retorna el texto, pero sin el carácter escogido
al principio
del texto, si hay un carácter al final este va a seguir estando.
SELECT TRIM(TRAILING '-' FROM '--Hola Mundo---');
-- Salida esperada: '--Hola Mundo'
Retorna el texto, pero sin el carácter escogido
al final
del texto, si hay un carácter al principio este va a seguir estando.
SELECT TRIMG(BOTH '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo'
Retorna el texto pero sin el carácter escogido
en ambos
lados, al principio y al final.
LTRIM RTRIM SUBSTR LPAD RPAD
LTRIM
SELECT LTRIM(' Hola Mundo');
-- Salida esperada: 'Hola Mundo'
SELECT LTRIM('___Hola Mundo', '_');
-- Salida esperada: 'Hola Mundo'
Retorna el texto, pero sin espacios a la izquierda
y si queremos remover un carácter
en específico a la izquierda
del texto lo hacemos pasándole el carácter
luego del texto separado con una coma.
RTRIM
SELECT LTRIM('Hola Mundo ');
-- Salida esperada: 'Hola Mundo'
SELECT LTRIM('Hola Mundo___', '_');
-- Salida esperada: 'Hola Mundo'
Retorna el texto, pero sin espacios a la derecha
y si queremos remover un carácter
en específico a la derecha
del texto lo hacemos pasándole el carácter
luego del texto separado con una coma.
SUBSTR
SELECT SUBSTR('Hola Mundo___', 5);
-- Salida esperada: ' Mundo___'
Retorna el texto a partir de la posición que le digamos, en este caso a partir de la posición 5.
SELECT SUBSTR('Hola Mundo___', 5, 8);
-- Salida esperada: ' Mundo__'
Retorna el texto a partir de la posicion que le digamos, en este caso a partir de la posicion 5. Y hasta la posicion del segundo numero que le pasemos, en este caso 8.
LPAD
SELECT LPAD('Hola Mundo',15,'-');
-- Salida esperada: '-----Hola Mundo'
Retorna el texto, pero le agrega a la izquierda
(al principio) del mismo el carácter que le indiquemos.
Siempre se va a detener cuando el largo o el número de caracteres sea el número que le pasemos o en este caso el 15.
RPAD
SELECT RPAD('Hola Mundo',15,'-');
-- Salida esperada: 'Hola Mundo-----'
Retorna el texto, pero le agrega al la derecha
(al final) del mismo el carácter que le indiquemos.
Siempre se va a detener cuando el largo o el número de caracteres sea el número que le pasemos o en este caso el 15.
DATE TIME TIMESTAMP EXTRACT()
DATE
SELECT CURRENT_DATE;
-- Salida esperada: 2021-11-01
Retorna la fecha
en la que se corre esta consulta.
Año-Mes-Día
TIME
SELECT CURRENT_TIME;
-- Salida esperada: 02:52:07.291573+00:00
Retorna la hora
en la que se corre esta consulta.
Hora:Minutos:Segundos:Milisegundos+ZonaHoraria
TIMESTAMP
SELECT CURRENT_TIMESTAMP;
-- Salida esperada: 2021-11-01 02:54:04.825941+00
Retorna la fecha
y hora
en la que se corre esta consulta.
Año-Mes-Día Hora:Minutos:Segundos:Milisegundos+ZonaHoraria
EXTRACT()
-- Retorna en que año estamos:
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
-- Salida esperada: 2021
-- Retorna en que mes estamos:
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);
-- Salida esperada: 11
-- Retorna en que dia estamos:
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 1
-- Retorna la hora:
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);
-- Salida esperada: 3
-- Retorna en que siglo estamos:
SELECT EXTRACT(CENTURY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 21
-- Retorna en que parte del cuarto del año estamos:
SELECT EXTRACT(QUARTER FROM CURRENT_TIMESTAMP);
-- Salida esperada: 4
-- Retorna en que numero de dia de la semana estamos:
SELECT EXTRACT(DOW FROM CURRENT_TIMESTAMP);
-- Salida esperada: 1
-- Retorna el numero de dias que pasaron desde principio del año:
SELECT EXTRACT(DOY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 305
-- Retorna el numero de meses que pasaron desde principio del año:
SELECT EXTRACT(WEEK FROM CURRENT_TIMESTAMP);
-- Salida esperada: 44
NULL
SELECT
*
FROM
person
WHERE
pais IS NULL;
Retorna todos los registros donde la columna pais
no tenga el valor NULL
SELECT
*
FROM
person
WHERE
pais IS NULL;
Retorna todos los registros donde la columna pais
tenga como valor NULL
SEQUENCE
-- Creamos la secuencia
CREATE SEQUENCE
sec_index
START WITH
1
INCREMENT BY
20
MINVALUE
1
MAXVALUE
100
CYCLE;
Si no ponemos valores MINVALUE
Y MAXVALUE
se setean los de por defecto: -9223372036854775808
y 9223372036854775807
.
Si no queremos que sea CYCLE
agregamos un NO
al inicio: NO CYCLE
. O simplemente lo quitamos.
-- Verificamos la secuencia creada
SELECT * FROM "sec_index";
-- Salida esperada:
-- sequence_name last_value start_value increment_by max_value min_value cache_value >>
-- sec_index 1 1 20 100 1 1
-- >> log_cnt is_cycled is_called
-- 0 true false
-- A mi me salio:
-- last_value log_cnt is_called
-- 1 0 false
En last_value
indica cuál es el número actual.
En is_called
indica si la secuencia fue iniciada/llamada.
-- Para iniciar/llamar la secuencia:
SELECT NEXTVAL('sec_index');
-- Salida esperada: 1
-- Salida esperada: 21
-- Salida esperada: 41
-- Salida esperada: 61
-- Salida esperada: 81
-- Salida esperada: 1
-- Salida esperada: 21
-- ... ya que es un ciclo vuelve a reiniciarse.
-- Si no es 'CYCLE' nos devolveria un error al querer superar el valor maximo.
DROP SEQUENCE sec_index;
Eliminamos la secuencia sec_index
.
SUBCONSULTA
DENTRO DE SELECT
SELECT
nombre,
apellido,
pais_residencia,
(
SELECT
MAX(precio) AS precio_max
FROM
precios
WHERE
pais = pais_residencia
)
FROM
persona;
Lista las columnas nombre
, apellido
, pais_residencia
y precio_max
. En este último tenemos el precio máximo de la tabla precios
coincidiendo el valor de la columna pais_residencia
de la tabla persona
con el valor de la columna pais
de la tabla precios
.
DENTRO DE WHERE
SELECT
nombre,
apellido
pais_residencia
FROM
persona
WHERE
pais_residencia = (SELECT pais FROM precios LIMIT 1);
Lista los registros si coincide que algún registro de la tabla persona tenga en la columna pais_residencia
el mismo valor que la subconsulta
, del valor de la columna pais
del primer registro de la tabla precios
.
DENTRO DE WHERE IN
SELECT
nombre,
apellido,
pais_residencia
FROM
persona
WHERE
pais IN (SELECT pais FROM precios WHERE pais LIKE '%C%');
Lista los registros que coinciden que la columna pais
de la tabla persona
tengan el mismo valor que los resultados de la subconsulta
de países que tengan C mayuscula
en la columna pais
de la tabla pais
.
Ejemplo:
-- nombre apellido pais_residencia
-- Alex Chacon Colombia
-- Maria Solis Costa Rica
-- Ana Alfaro Costa Rica
-- Otto Solano Colombia
DELETE UPDATE CON SUBCONSULTA
UPDATE
UPDATE
persona
SET
pais_residencia = (SELECT pais FROM precios ORDER BY precio LIMIT 1 OFFSET 10)
WHERE
pais_residencia IS NULL;
Actualiza los campos que tengan NULL
de la columna pais_residencia
de la tabla persona
con los datos de la subconsulta
sacado de la columna pais
de la tabla precios
ignorando los primeros 10 registros y solo trayendo 1 registro.
DELETE
DELETE FROM
persona
WHERE pais_residencia IN (SELECT pais FROM precios WHERE pais LIKE '%er%');
Borra los registros de la tabla persona
donde los registros tengan en la columna pais_residencia
el valor de los resultados de la subconsulta
. La subconsulta trae la columna pais
de la tabla precios
donde en su valor contenga: er
.
INSERT CON SUBCONSULTA
-- Existe una tabla llamada "precios"
-- Existe una tabla vacia llamada "precios_maximos"
INSERT INTO
precios_maximos
SELECT
pais, max(precio)
FROM
precios
GROUP BY
pais
Insertamos en la tabla vacía de precios_maximos
los resultados de la subconsulta
, la misma trae el precio maximo
de cada pais
sin repetir el valor de la columna pais
de la tabla precios
.
VARIABLES
-- NO SE PUEDE DECLARAR VARIABLES FUERA DE UN BLOQUE.
DECLARE @variable int;
SET @variable = 50;
SELECT @variable;
-- Nos da error.
Retornaría un error, ya que las variables tienen que declararse dentro de una estructura de bloque.
DO $$
DECLARE x int := 50;
y int := 500;
z int;
BEGIN
z := x * y;
-- Para que retorne el valor de z utilizamos '%':
RAISE NOTICE '%',z;
END
$$;
-- Salida esperada: NOTICE: 2500
-- DO
IF
DO $$
BEGIN
IF EXISTS(SELECT pais FROM precios WHERE pais = 'Iraq') THEN
RAISE NOTICE 'El pais SI ha sido encontrado';
ELSE
RAISE NOTICE 'El pais NO ha sido encontrado';
END IF;
END $$
Retorna 'El país SI ha sido encontrado' o 'El país NO ha sido encontrado' según si la condición dentro de los paréntesis de EXISTS
es true o falsa.
WHILE
DO
$$
DECLARE x int := (SELECT COUNT(id) FROM precios) -- x = 396
y int := 0;
BEGIN
WHILE (y < x)
LOOP
RAISE NOTICE 'Vuelta # %', y;
y := y + 1;
END LOOP;
END
$$
-- Salida esperada:
-- NOTICE: Vuelta # 0
-- NOTICE: Vuelta # 1
-- NOTICE: Vuelta # 2
-- NOTICE: Vuelta # 3
-- NOTICE: Vuelta # 4
-- NOTICE: Vuelta # 5
-- NOTICE: Vuelta # 6
-- NOTICE: Vuelta # 7
-- NOTICE: Vuelta # 8
-- NOTICE: Vuelta # 9
-- NOTICE: Vuelta # 10
-- ...
-- NOTICE: Vuelta # 393
-- NOTICE: Vuelta # 394
-- NOTICE: Vuelta # 395
-- DO
CASE
SELECT
pais,
precio,
CASE WHEN pais = 'Spain' THEN 'Vuelo con Escalas'
WHEN pais = 'Turkey' THEN 'Vuelo Retrasado'
ELSE 'Vuelo Normal'
END AS travel_status
FROM
precios;
-- Salida esperada:
-- pais precio travel_status
-- Turkey $89.28 Vuelo con Escalas
-- Spain $9.87 Vuelo Retrasado
-- Brazil $95.24 Vuelo Normal
-- Italy $12.22 Vuelo Normal
EXPORTAR IMPORTAR BASE DE DATOS PGADMIN4
EXPORTAR
Click derecho en la base de datos > Backup...
> Elegimos una dirección donde se guardara el backup > Role name
: postgres
> Y para terminar le damos al botón: Backup
IMPORTAR
Click derecho en Databases
> Create
> Database...
> Le damos un nombre > Finalizamos la creación con Save
.
Click izquierdo en la nueva base de datos que creamos y luego click derecho > Restore...
> En la fila de Filename
le damos a los tres puntitos (...
) para elegir el archivo que queremos importar > Para poder ver el archivo hay que cambiar el Format
a sql
> Seleccionamos el archivo de extensión .sql
> Seleccionamos en Role name
: postgres
> Y para terminar le damos al botón: Restore
.
EXPORTAR IMPORTAR UNA TABLA A CSV
EXPORTAR
Click derecho a la tabla > Import/Export...
> Le damos click en los tres puntitos (...
) para ubicar donde se va a guardar el archivo exportado > Format
: csv
> Header
: Yes
> Delimiter
: ,
> Para finalizar le damos al botón OK
.
IMPORTAR
Click derecho a la tabla > Import/Export...
> Cambiamos a importar en Import/Export
: Import
> Seleccionamos el archivo con los tres puntitos (...
) > Format
: csv
> Delimiter
: ,
> Para finalizar le damos al botón OK
.
CURSOR
Es una variable donde vamos a guardar el contenido de una registro resultado de una consulta
DO
$$
DECLARE
registro Record;
cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN
OPEN cur_precios;
FETCH cur_precios INTO registro;
RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;
END
$$
LANGUAGE PLPGSQL;
Creamos un cursor
llamado cur_precios
que consulta las columnas de la tabla precios
ordenados por la columna pais
.
Abrimos el cursor
y luego hacemos un FETCH
para insertar un registro de cur_precios
dentro de la variable registro
.
Por último damos una noticia de cuanto vale registro.pais
y registro.precio
cada vez que ejecutemos este script.
Al ejecutar el script va a insertar un registro a la tabla registro
, hasta que el cursor
no tenga registros a que apuntar en cur_precios
.
Cabe destacar que el FETCH
va haciendo que el CURSOR
vaya apuntando al siguiente registro dentro de cur_precios
. Si hacemos dos FETCH
seguidos va a correr el primer FETCH
, se guarda un registro dentro de registro
y luego va a correr el segundo FETCH
con el registro que le sigue al primero dentro de cur_precios
para guardarlo dentro de registro
.
CURSOR CON WHILE
DO
$$
DECLARE
registro Record;
cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN
OPEN cur_precios;
-- Mientras que encuentre registros el WHILE va a seguir funcionando.
-- Cuando no encuentre registros el WHILE va a terminar.
WHILE (FOUND) LOOP
FETCH cur_precios INTO registro;
END LOOP;
RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;
END
$$
LANGUAGE PLPGSQL;
El script esta vez toma todos los registros dentro del cursor
cur_precios
y los inserta dentro de la tabla registro
.
El script se detiene solo, ya que el WHILE
se va a detener cuando el cursor
no tenga registros a que apuntar dentro de cur_precios
.
CURSOR CON FOR
DO
$$
DECLARE
registro Record;
cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN
-- Por cada registro dentro de 'cur_precios' lo guarde dentro de 'registro'
FOR registro IN cur_precios LOOP
RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;
END LOOP;
END
$$
LANGUAGE PLPGSQL;
Con FOR no hace falta abrir el cursor. Hace lo mismo que el WHILE pero en forma más resumida.
Top comments (0)