Resumen: Ésta es la segunda parte del curso de SQL, en esta parte vamos a centrarnos en los diversos comandos SQL, desde la creación de la tabla, su modificación y/o borrado.
Nos centraremos, sobre todo, en el comando SELECT, que es, a mi juicio, el más importante de todos.
Espero que esta segunda parte os sea amena e instructiva.
Como hemos visto en la primera entrega, para crear una tabla se usa el comando CREATE con el calificativo TABLE, en efecto el comando CREATE sirve para crear:
El comando CREATE indica al gestor que algo se va a crear, luego añadiremos quéy cómo.
Lo que ahora nos interesa es la creación de la tabla:
CREATE TABLE nombre ( columna tipo [DEFAULT valor] [NOT NULL], ... [INHERITS (hereda, ...)] [CONSTRAINT nom_cons CHECK (prueba), CHECK (prueba)] );Donde:
Nombre: | Es el nombre que se le da a la tabla y como sera referenciada por cualquier comando |
Columna: | Es el nombre como vamos a conocer esa columna |
Tipo: | Es el tipo de dato (varchar, char, int, date, time, timestamp), postgres tiene otros tipos de datos, pero no son compatibles SQL Ansi |
Valor: | El valor que tendrá por defecto |
Hereda: | Esto es propio a Postgres, define una herencia de otra tabla, esto es creara una entidad que contiene las columnas de la tabla que estamos creando y las heredadas |
Nom_cons: | Esto define una regla de integridad a respetar cada vez que se modifica una tupla |
Prueba: | Condición a comprobar |
CREATE TABLE países ( cod_pais integer NOT NULL, nombre varchar(30)) CONSTRAINT cod_raro CHECK (cod_pais > 0 AND cod_pais < 154);
Con este ejemplo hemos creado una tabla de países, cada vez que insertemos una nueva tupla se cumplirán estas condiciones:
ExecAppend: Fail to add null value in not null attribute cod_pais
ExecAppend: rejected due to CHECK constraint cod_raro
¿Qué quiere decir NULO? En SQL existen dos estados, datos o NO datos, en efecto nos puede interesar que un campo de un tupla NO tenga datos, tanto el cero (0) como el espacio son datos. SQL introduce el concepto de NULO y trabajar con él, un ejemplo practico:
Tengo una tabla con registro de facturas, con los siguiente campos: cliente, importe, fecha_emision, fecha_pago
Cuando creo la tupla, insertare los datos: cliente, importe, fecha_emision
Dejaré la fecha de pago a nulos, de esta forma podré conocer todas las facturas impagadas con el siguiente comando:
SELECT * FROM facturas WHERE fecha_pago IS NULL;
Alguien puede alegar que un cero (0) en el campo fecha_pago haría el mismo papel, es verdad, salvo que cero (0) no es una fecha y me impide crear fecha_pago de tipo fecha y poder aplicarle las funciones propias a fechas.
Ejemplos de creación con NULOS:
insert into paises values (15, NULL);o bien:
insert into paises (cod_pais) values (27);
La ausencia de campo (nombre) implica que éste recibirá el valor NULO.
En PostgreSQL la modificación SÓLO contempla el añadido de nueva(s) columna(s).
ALTER TABLE tabla ADD nombre tipo;
Donde:
Tabla | Nombre de la tabla a modificar |
Nombre | Nombre de la columna a añadir |
Tipo | Tipo de dato (ver CREATE TABLE) |
Ahora vamos a insertar datos en nuestra tabla:
INSERT INTO tabla [(columna, columna, ...)] VALUES (valor-1, valor-2, ...)o bien:
INSERT INTO tabla [(columna, columna, ...)] SELECT ....
Como hemos visto hay dos formas de insertar datos en una tabla, sea línea a línea o el resultado de una sub-select que puede devolver una o varias tuplas.
Cuando insertamos líneas en una tabla, SIEMPRE pondremos datos en todas las columnas incluso las que no mencionamos, estas se crearan con valores NULOS.
Si en el comando no especificamos que columnas vamos a rellenar, se entiende que vamos a dar datos para todas, ejemplo:
INSERT INTO paises VALUES (34, 'España');Esto sería incorrecto:
INSERT INTO paises VALUES (34);Pero, esto sí sería correcto:
INSERT INTO paises (cod_pais) VALUES (34);
Yo recomiendo que en comando embebidos en programas "C" o en funciones de la base de datos SIEMPRE se especifiquen las columnas que vamos a tocar, en efecto si añadimos una nueva columna a la tabla (ALTER TABLE), el próximo insert saldrá en error, Ejemplo:
Esto daría un error de parser, dado que falta el dato para población.
PostgreSQL, no genera error, crea la línea con el campo (población) a NULO, esto es solo una particularidad de PostgreSQL, cualquier otro gestor SQL daría error.
Luego nos queda el otro tipo de INSERT, el que se nutre de una sub-select.
Este tipo de insert se realiza, muy a menudo, para crear tablas temporales o tablas para realizar una tarea muy concreta de cálculos especulativos.
La parte reemplazada es la que toca a los datos ellos mismos, estos vienen dado por una instrucción SELECT que se realizada previamente a la inserción de los datos. La instrucción SELECT puede devolver una o varias tuplas, esa instrucción SELECT tiene las mismas restricciones que la propia SELECT.
!Aquí quería llegar yo! :-))
Nos hemos topado con la iglesia de los comandos SQL, el lenguaje SQL sin la SELECT sería como las lentejas sin chorizo (mira que lo he puesto difícil a los traductores :-) )
El comando SELECT nos permite acceder a los datos, pero con la salvedad que puede realizar búsquedas, uniones de tablas, funciones sobre los datos o sobre las reglas de búsqueda (predicado)
Un ejemplo:
select * from paises;Otro ejemplo:
SELECT a.nombre, SUM(poblacion) FROM paises a, provincias b, municipios c WHERE b.cod_pais = a.cod_pais AND (c.cod_pais = b.cod_pais AND c.cod_provincia = b.cod_provincia) AND poblacion IS NOT NULL GROUP BY a.nombre ORDER BY sum ASC;
M'explico, he pedido la población de todos los países ordenada por la población resultante en modo ascendente, sencillo. !NO! 8-O
Bien para esto he añadido una nueva columna (población) a la tabla municipios.
Esto queda así:create table municipios (cod_pais int, cod_provincia int, cod_municipio int, nombre_municipio varchar(60), poblacion int); insert into municipios values (1, 1, 1, 'Pais 1, Provincia 1, Municipio 1', 5435); insert into municipios values (2, 1, 1, 'Pais 2, Provincia 1, Municipio 1', 7832); insert into municipios values (3, 1, 1, 'Pais 3, Provincia 1, Municipio 1', 4129); insert into municipios values (1, 2, 1, 'Pais 1, Provincia 2, Municipio 1', 76529); insert into municipios values (2, 2, 1, 'Pais 2, Provincia 2, Municipio 1', 9782); insert into municipios values (3, 2, 1, 'Pais 3, Provincia 2, Municipio 1', 852); insert into municipios values (1, 3, 1, 'Pais 1, Provincia 3, Municipio 1', 3433); insert into municipios values (2, 3, 1, 'Pais 2, Provincia 3, Municipio 1', 7622); insert into municipios values (3, 3, 1, 'Pais 3, Provincia 3, Municipio 1', 2798); insert into municipios values (1, 1, 2, 'Pais 1, Provincia 1, Municipio 2', 7789); insert into municipios values (2, 1, 2, 'Pais 2, Provincia 1, Municipio 2', 76511); insert into municipios values (3, 1, 2, 'Pais 3, Provincia 1, Municipio 2', 98); insert into municipios values (1, 2, 2, 'Pais 1, Provincia 2, Municipio 2', 123865); insert into municipios values (2, 2, 2, 'Pais 2, Provincia 2, Municipio 2', 886633); insert into municipios values (3, 2, 2, 'Pais 3, Provincia 2, Municipio 2', 982345); insert into municipios values (1, 3, 2, 'Pais 1, Provincia 3, Municipio 2', 22344); insert into municipios values (2, 3, 2, 'Pais 2, Provincia 3, Municipio 2', 179); insert into municipios values (3, 3, 2, 'Pais 3, Provincia 3, Municipio 2', 196813); insert into municipios values (1, 1, 3, 'Pais 1, Provincia 1, Municipio 3', 491301); insert into municipios values (2, 1, 3, 'Pais 2, Provincia 1, Municipio 3', 166540); insert into municipios values (3, 1, 3, 'Pais 3, Provincia 1, Municipio 3', 165132); insert into municipios values (1, 2, 3, 'Pais 1, Provincia 2, Municipio 3', 0640); insert into municipios values (2, 2, 3, 'Pais 2, Provincia 2, Municipio 3', 65120); insert into municipios values (3, 2, 3, 'Pais 3, Provincia 2, Municipio 3', 1651462); insert into municipios values (1, 3, 3, 'Pais 1, Provincia 3, Municipio 3', 60650); insert into municipios values (2, 3, 3, 'Pais 2, Provincia 3, Municipio 3', 651986); insert into municipios values (3, 3, 3, 'Pais 3, Provincia 3, Municipio 3', NULL); commit work;
Ya lo se podríamos hacerlo por ALTER TABLE, pero debería usar el UPDATE y no lo he explicado, así que usáis el "cut & paste" y todos felices:-))
Ahora podemos ejecutar la QUERY, y el resultado debería ser:
nombre| sum ------+------- pais 1| 705559 pais 2|1212418 pais 3|2804018 (3 rows)Ahora verificamos:
sum ------ 791986 (1 row)
!!!!!! UNA DIFERENCIA !!!!!!
Miremos la tabla de provincias, falta la provincia 3, hagamos:
INSERT INTO PROVINCIAS VALUES (3, 1, 'Provincia 3, Pais 1'); INSERT INTO PROVINCIAS VALUES (3, 2, 'Provincia 3, Pais 2'); INSERT INTO PROVINCIAS VALUES (3, 3, 'Provincia 3, Pais 3');Y repetimos el comando, resultado:
nombre| sum ------+------- pais 1| 791986 pais 2|1872205 pais 3|3003629
Nos faltaba la provincia 3 de cada país.
Ahora para los que se han perdido, recordemos que las conjunciones entre tablas eran EXACTAS, es decir solo extrae datos si la condición dentro del predicado es exacto.
Miremos la primera parte del WHERE: b.cod_pais = a.cod_pais
Esto quiere decir que junto de la tabla países hacia provincias siempre que el código de país sea igual, ahora recordamos los datos de países que insertamos:
No los ejecutéis, es solo como ilustración.
create table paises (cod_pais integer, nombre varchar(30)); insert into paises values (1, 'pais 1'); insert into paises values (2, 'pais 2'); insert into paises values (3, 'pais 3'); commit work;Ahora los datos de provincias:
create table provincias (cod_provincia int, cod_pais int, nom_provincia varchar(30)); insert into provincias values (1, 1, 'Provincia 1, Pais 1'); insert into provincias values (2, 1, 'Provincia 2, Pais 1'); insert into provincias values (1, 2, 'Provincia 1, Pais 2'); insert into provincias values (2, 2, 'Provincia 2, Pais 2'); insert into provincias values (1, 3, 'Provincia 1, Pais 3'); insert into provincias values (2, 3, 'Provincia 2, Pais 3'); commit work;Faltan todas las provincias 3 de cada país, pero en la tabla de municipios sí que estaban los datos correspondientes a las provincias con código 3, así que es normal que no sumase los datos de los municipios de las provincias con código 3, por ser descartadas en la segunda parte del where:
AND (c.cod_pais = b.cod_pais AND c.cod_provincia = b.cod_provincia)
La provincia existia en la tabla de municipios pero NO en la tabla de provincias.
Para los que no habéis comprendido, os tomáis una aspirina, os vais a pasear al perro (si no tenéis perro, os vais a pasear sin perro), respirar un poco de aire fresco y volvéis a empezar desde la primera entrega.
Es muy importante el comprender como se realizan las conjunciones de datos, sin ello los desarrollos que hagamos pueden tener resultados imprevisibles.
Cerremos el paréntesis y empecemos con la sintaxis del comando SELECT.
SELECT [DISTINCT] expresion1 [AS nom-atributo] {, expresion-i [as nom-atributo-i]} [INTO TABLE classname] [FROM from-list] [WHERE where-clause] [GROUP BY attr_name1 {, attr_name-i....}] [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {, nom-atributo-i...}] [UNION {ALL} SELECT ...]Paso a paso:
DISTINCT: | esto es para eliminar tuplas duplicadas en salida |
expresion1: | que queremos en salida, normalmente una columna de una tabla de la lista FROM |
AS nom-atributo: | un alias para el nombre de columna, ej: manu=> select cod_pais from paises; cod_pais -------- 1 2 3 (3 rows) manu=> select cod_pais as pasi from paises; pasi ---- 1 2 3 (3 rows) |
INTO TABLE: | permite insertar las tuplas resultantes directamente en otra tabla (ver INSERT ... SELECT...) |
FROM: | lista de tablas en entrada |
WHERE: | predicado de la selección (criterios de unión y selección). |
GROUP BY: | Criterio de agrupación, ciertas funciones que se usan en (expresión) pueden necesitar una agrupación, es decir un criterio de discriminación y resultado |
ORDER BY: | Criterio de ordenación de las tuplas en salida, ASC orden ascendente, DESC orden descendente, USING por si la columna que define el orden no esta en la lista (expresión...) |
UNION ALL SELECT: | Esto define que se añadirá al resultado de la primera SELECT esta segunda SELECT que puede ser tablas distintas, pero devolviendo el mismo número de columnas. |
Hemos visto que los comandos SELECT no sólo devuelven datos de la BD sino que los puede modificar:
Esto nos devolverá el incremento a pagar de más de aumentar el sueldo un 10%.
Vamos a ver que funciones tenemos a disposición:
COUNT(): | devuelve la cantidad de tuplas no NULAS |
SUM(): | devuelve la suma total de una columna numérica |
AVG(): | devuelve el promedio de una columna numérica |
MIN(): | devuelve el valor mínimo de una columna |
MAX(): | devuelve el valor máximo de una columna |
FLOAT(int): | devuelve un FLOAT8, FLOAT(12345) |
FLOAT4(int): | devuelve un FLOAT4, FLOAT4(12345) |
INT(float): | devuelve un INT de un FLOAT/4, INT(123.456) |
LOWER(texto): | devuelve texto en minúsculas |
UPPER(texto): | devuelve texto en mayúsculas |
LPAD(texto, long, char): | rellena a la izquierda con char en longitud o long la columna texto |
RPAD(texto, long, char): | rellena a la derecha con char en longitud o long la columna texto |
LTRIM(texto, char): | quita en la izquierda de texto todo carácter char |
RTRIM(texto, char): | quita en la derecha de texto todo carácter char |
POSITION(cadena IN texto): | extrae de texto la posición de cadena, pero NO FUNCIONA |
SUBSTR(texto,desde[,hasta]): | extrae la subcadena de texto, de la posición desde y de haberla, hasta la posición hasta o el final de la cadena |
DATETIME(fecha, hora): | convierte a formato datetime una fecha (AAAA-MM-DD) y un a hora (HH:MM) |
Éstas eran unas pocas funciones existentes en SQL, éstas son las que se definen en SQL ANSI y además están presentes en Postgres95.
Hasta ahora hemos visto que en la sección WHERE del SELECT poníamos cosas como:
AND columna = valor
Esto es una pequeña muestra de lo que podemos poner o combinar:
AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE además los parentesis tienen relevancia, ejemplos:
WHERE columna IN (SELECT DISTINCT columna FROM tabla WHERE ....) columna IN ('valor1','valor2','valor3',...) (columna = 'valor' and columna = 'otro_valor' OR columna != 'valor')!= es igual a decir NOT EQUAL
WHERE columna LIKE '%Pepito%'El % es un comodín, en el ejemplo, sera verdadero si "Pepito" esta en el string
WHERE columna LIKE 'Pepito%'será verdadero si "Pepito" está al principio en el string
WHERE columna LIKE '%Pepito'será verdadero si "Pepito" está al final en el string
Poner aquí todas las opciones posibles del WHERE se sale de mis posibilidades y tiempo, sólo cabe decir que el límite está en la imaginación del programador o los limites del propio traductor del gestor.
Ahora ya podemos dejar el comando SELECT y centrarnos en los dos últimos.
El comando UPDATE permite modificar una o varias tuplas, dependiendo de la condición definida en el WHERE
UPDATE tabla SET columna-1 = expresisn-1 [, columna-i = expresisn-i] [WHERE condicisn]Donde:
tabla: | es la tabla a modificar, solo se podrá modificar una tabla a la vez |
columna: | es la columna que va a ser modificada |
expresión: | es el valor que va a recibir la columna, ese valor puede ser estatic o o el resultado de una función |
condición: | es la condición que define el ámbito de trabajo de la modificación, aquí son aplicables todas las reglas definidas para el SELECT |
DELETE FROM tabla [WHERE condicisn]Donde:
tabla: | es la tabla donde borrar tuplas, solo se podrá borrar en una tabla a la vez |
condición: | es la condición que define el ámbito de trabajo del borrado,
aquí so n aplicables todas las reglas definidas para el
SELECT NOTA: de no existir el WHERE el borrado afectara TODAS las tuplas de la tabla |
©
1998 Manuel Soriano Permission is granted to copy, distribute and/or modify this page under the terms of the GNU Free Documentation License, Version 1.1 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts and no Back-Cover Texts. |