lopezatienza.com

Lenguaje de manipulación de datos DML.

Operaciones de inserción de datos.

INSERT guarda una o mas de una fila de datos en una tabla o vista de la base de datos. Debemos tener privilegios para realizar esta operación.

Los valores son insertados en cada columna de la tabla elegida a no ser que se especifique una lista de columnas a insertar, entonces, las columnas omitidas se rellenan con valores por defecto o nulos dependiendo de la definición de la tabla.

Si queremos insertas una única fila de datos la cláusula VALUES puede tener una lista de valores específico.

Si insertamos múltiples filas de datos debemos especificar una expresión SELECT que devuelva datos existentes de otra tabla para que se inserten en la actual. Esta tabla puede ser incluso la tabla objetivo de la inserción pero debemos tener cuidado ya que podemos generar un bucle sin fin.

La sintaxis especifica es :

INSERT [TRANSACTION transaction] INTO<object> [(col [, col …])]</object> {VALUES (<val> [, <val> …]) |</val></val>

<select_expr>};</select_expr> = tablename | viewname = {:variable |  | |  | udf ([ [,  …]]) | NULL | USER | RDB$DB_KEY | ? } [COLLATE collation]<constant> = num | 'string' | charsetname 'string'</constant><function> = CAST (<val> AS <datatype>)</datatype></val></function> | UPPER (<val>)</val> | GEN_ID (generator, <val>)</val>

Ejemplo, insertando valores en una sola fila:

INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENT) VALUES (7734, 'Marketing');

y ahora un conjunto de filas:

INSERT INTO DEPARTMENTS (DEPT_NO, DEPARTMENT, BUDGET)
SELECT DEPT_NO, 'Publications', BUDGET
FROM OLDDEPT
WHERE DEPARTMENT = 'Documentation';

Operaciones de actualización de datos.Para realizar actualizaciones sobre valores de una tabla usamos la sentencia UPDATE que cuenta con la siguiente sintaxis:

UPDATE [TRANSACTION transaction] {table | view}
SET col = <val> [, col = <val> …]</val></val>
[WHERE <search_condition> | WHERE CURRENT OF cursor];</search_condition>

La modificación afectará a todas las filas que cumplan la condición, si se especifica ésta. Si no se especifica condición, la modificación afectará a todas las filas de la tabla.

El valor que se asigne al atributo debe ser una constante, o el resultado de una subconsulta que genere un único valor.

UPDATE CITIES
SET POPULATION = POPULATION * 1.03;
UPDATE T_Proy
SET Dpto = ‘D25’,
Presupuesto = (SELECT Presupuesto + 200000
FROM T_Proy
WHERE Cod_Proy = ‘P23’)
WHERE Cod_Proy = ‘P23’;

UPDATE T_Proy
SET Dpto=’D25’
Presupuesto= Presupuesto + 2000000
WHERE Cod_Proy = ‘P23’;

Operaciones de eliminación de datos.

Eliminación de filas:

DELETE [TRANSACTION transaction] FROM table
{[WHERE <search_condition>] | WHERE CURRENT OF cursor};</search_condition>
<search_condition> = Condición de búsqueda definida en SELECT.</search_condition>

No se pueden eliminar partes de una fila.

Si no aparece la cláusula "WHERE" se eliminarán todas las filas de la tabla, no eliminándose la definición de ésta en el esquema.
Operaciones de consulta

LA sentencia SELECT soporta todos las consultas en SQL, puede recuperar una o mas de una filas de una tabla y puede devolver filas completas, o partes de filas como hace la operación proyección. Opcionalmente la sintaxis de SELECT puede usarse para especificar un criterio que restringe el número de columnas a devolver( restricción en álgebra relacional), seleccionar filas de una vista, seleccionar filas combinadas de dos o más tablas.

Una sentencia SELECT debe tener como mínimo:

una lista de columnas a recuperar (* indica todas), definida inmediatamente después de la palabra SELECT y el nombre de la tabla especificado después de la palabra FROM.

Si la recuperación solo devuelve una sola fila, podemos usar la cláusula INTO para asignar valores a variables, pero la cláusula WHERE debe asegurar que solo se recupera una fila.

Describamos algunas de las cláusulas más usadas:

· FROM: indica las tablas o vistas o las reuniones en las que se encuentran los atributos implicados en la consulta.

· WHERE: especifica la condición de búsqueda, puede contener e su interior otra sentencia SELECT llamándose subconsulta.

· UNION: operación union de álgebra relacional . Interbase no usa ni INTERSECT ni MINUS.

· GROUP BY: permite agrupar el resultado.

· HAVING: especifica una condición de selección del grupo.

· ORDER BY: permite ordenar el resultado.

Nombres de campos:

Para definir todos los campos usamos el *.

Podemos usar funciones de agregado para mostrar valores aunque no se especifique cláusula GROUP BY. En ese caso lel grupo lo forma toda la tabla (o todas las filas que cumplan la condición WHERE)

Las funciones de agregado son:

AVG( ), MIN( ), MAX( ), SUM( ), COUNT( ).
SELECT COUNT(*), MAX(EMP_NO), MIN(EMP_NO), SUM(SALARY)
FROM EMPLOYEE;

Si queremos que se eliminen los valores duplicados usamos la sentencia DISTINCT después del select.
FROM:

Permite definir las tablas que forman parte de la consulta, pudiendo asignar unalias a algunas o todas las tablas vistas y procedimientos almacenados implicados en las consultas. También aquí podemos incluir la palabra JOIN que más tarde detallaremos para definir reuniones. Veamos el ejemplo de asignación de alias

SELECT DEPARTMENT, DEPT_NO, LAST_NAME, FIRST_NAME,EMLOYEE.EMP_NO
FROM DEPARTMENT DEPT, EMPLOYEE EMP
WHERE DEPT_NO = 'Publications' AND DEPT.EMP_NO = EMP.EMP_NO;

WHERE

Especificamos las filas que deben intervenir en la consulta. Una clausula Where tiene una condición que puede tomar 3 valores diferentes cierto, falso o desconocido. Los elementos que forman parte de una clausula WHERE son los siguientes:

· Nombres de columnas de las tablas listadas detrás de FROM.

· Constantes, numéricas o de caracteres o fecha.

· Operador de concatenación de cadenas ||.

· Operadores aritméticos +, - , *, /.

· Operadores lógicos. NOT, AND, OR.

· Operadores de comparación<, >, <=, >=,= y <> y otros más especializados como ALL, ANY, BETWEEN, CONTAINING, EXISTS, IN, IS , LIKE, SINGULAR, SOME, y STARTING WITH.

· Procedimientos almacenados.

· Subconsultas es decir, otra sentencia SELECT.

· Paréntesis para saltar las prioridades.

ORDER BY

Permite estblecer un orden de visualización de las distintas filas recuperadas mediante una consulta, podemos especificar si su ordenación va a ser ascendente (ASC) o descendente (DESC)

ORDER BY col [COLLATE collation] [ASC | DESC]
[,col [COLLATE collation] [ASC | DESC] ...];

La opción COLLATE especifica un orden especial definido previamente y aplicable concretamente a estos datos a recuperar.

SELECT LAST_NAME, FIRST_NAME, PHONE_EXT FROM EMPLOYEE
WHERE PHONE_EXT IS NOT NULL
ORDER BY LAST_NAME DESC, FIRST_NAME;
GROUP BY…HAVING

Permite mostrar el resultado de la consulta agrupado conforme al valor de unos determinados campos. Devuelve finalmente una única fila por grupo formado. Podemos usar las funciones de agregado que vimos anteriormente, devolviendo entonces un valor por cada grupo formado.

Límites a GROUP BY.

· Todo campo que aparezca en un select debe aparecer en la cláusula group by. Aunque todo campo que aparezca en un group by no tiene porque aparecer en el select.

· No podemos usar campos calculados.

· es incompatible con la cláusula INTO y con una subconsulta que haga referencia a una vista que ya tenga una cláusula GROUP BY.

· Solo hay una cláusula GROUP BY por SELECT.

Si después de formar los grupos decidimos que algunos no van a mostrase, debemos usar la cláusula HAVING que determina una condición que deben cumplir los grupos. La única limitación importante de la condición de búsqueda es que no podemos usar una subconsulta que use las tablas de la consulta que la contiene.

SELECT DEPARTMENT, AVG(SALARY) FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DEPT_NO = E.DEPT_NO
GROUP BY DEPARTMENT
HAVING AVG(SALARY) > 60000
ORDER BY DEPARTMENT;

Reuniones JOIN.

Interbase define 2 tipos de reuniones básicas las internas o INNER JOIN y las externas OUTER JOIN.

INNER JOINS (REUNIONES)

Interbase soporta dos formas de declarar inner joins, con portabilidad con SQL estándar, se permite la forma antigua de listar las tablas en la cláusula FROM, y las columnas a comparar en la cláusula WHERE. Por ejemplo:

SELECT D.DEPARTMENT, D.MNGR_NO, E.SALARY FROM DEPARTMENT D, EMPLOYEE E
WHERE D.MNGR_NO = E.EMP_NO
AND E.SALARY*2 >= (SELECT SUM(S.SALARY) FROM EMPLOYEE S
WHERE D.DEPT_NO = S.DEPT_NO)
ORDER BY D.DEPARTMENT;

Pero proporciona tambien una sintaxis especial para estas reuniones:

SELECT col [, col ...] | *
FROM
<tablerefleft>[INNER] JOIN
<tablerefright></tablerefright></tablerefleft>[ON <searchcondition>]</searchcondition>[WHERE <searchcondition>];</searchcondition>que se diferencia en que se definen las tablas a la izquierda y derecha de la palabra JOIN y las columnas que sirven para realizar el join se listan después de la palabra ON especificando el tipo de join (equijoin, o theta join |x|).Por ejemplo:SELECT D.DEPARTMENT, D.MNGR_NO, E.SALARYFROM DEPARTMENT D INNER JOIN EMPLOYEE EON D.MNGR_NO = E.EMP_NOWHERE E.SALARY*2 &gt; (SELECT SUM(S.SALARY) FROM EMPLOYEE SWHERE D.DEPT_NO = S.DEPT_NO)
ORDER BY D.DEPARTMENT;

Un caso especial que merece la pena documentar por su posible utilidad es la posibilidad de crear join reflexivos, es decir reuniones entre la misma tabla por ejemplo:

SELECT R1.RIVER, R2.RIVER
FROM RIVERS R1 JOIN RIVERS R2 ON R2.OUTFLOW = R1.RIVER
ORDER BY R1.RIVER, R2.SOURCE;

OUTER JOINS

La sintaxis es la siguiente:

SELECT col [, col ...] | *
FROM
<tablerefleft>{LEFT | RIGHT | FULL} [OUTER] JOIN</tablerefleft>
<tablerefright>[ON <searchcondition>][WHERE <searchcondition>];</searchcondition>

Las reuniones externas producen filas donde aparecen todas las filas de una de las tablas (left o right) y parte de las filas de la otra tabla para las que la condición ON se cumple, apareciendo el resto de los valores a nulos. Las reuniones externas se usan comúnmente para comparar un subconjunto de datos con respecto al total de datos existentes.por ejemplo recuperar los países en los que nace un rio es tan interesante como y a la vez, ver los que no tienen nacimientos de ríos:

SELECT C.COUNTRY, R.RIVERFROM COUNTRIES C LEFT JOIN RIVERS R ON R.SOURCE = C.COUNTRYORDER BY C.COUNTRY;

Los outer joins pueden ser:·

Por la izquierda (LEFT OUTER JOIN) que se encarga de recuperar todas las filas de la tabla de la izquierda, y todas las de la derecha que cumplen la condición de la sentencia ON·
Por la derecha (RIGHT OUTER JOIN) que se encarga de recuperar todas las filas de la tabla de la derecha , y todas las de la izquierda que cumplen la condición de la sentencia ON.·
Por la derecha y la izquierda. FULL OUTER JOIN.La potencia de los Joins puede llegar a formar sentencias complejas que incluyan más de un join com por ejemplo:

SELECT COU.COUNTRY, C.CITY, T.TEAM, T.SPORT
FROM (CITIES CIT LEFT JOIN COUNTRIES COU ON COU.COUNTRY = CIT.COUNTRY)
INNER JOIN TEAMS T ON T.CITY = C.CITY
ORDER BY COU.COUNTRY;

Subconsultas

Una subconsulta es una sentencia SELECT entre paréntesis dentro de una cláusula WHERE, una subconsulta puede hacer referencia incluso a la misma tabla que la consulta “padre” . La sintaxis de esta situación es la siguiente:

SELECT [DISTINCT] col [, col ...]
FROM
<tableref>[,
<tableref>...]</tableref></tableref>WHERE {expression {[NOT] IN | comparison_operator}| [NOT] EXISTS} (SELECT [DISTINCT] col [, col ...]FROM
<tableref>[,
<tableref>...]</tableref></tableref>WHERE <search_condition>);</search_condition>

La subconsulta se evalúa antes que la consulta padre, a excepción de las consultas correlacionadas.Una subconsulta determina en la condición de búsqueda de la consulta en las siguientes formas:Produciendo un conjunto de valores que se evaluarán usando los operadores IN, ALL, ANY, SOME, EXIST· IN la comparación es cierta cuando el valor de la parte izquierda se encuentra dentro de la lista (que puede ser una subconsulta) que aparece a su derecha.

· ALL la comparación es cierta cuando todos los valores de la lista (que puede ser una subconsulta ) de la parte derecha cumplen la condición asociada.
· ANY SOME la comparación es cierta cuando alguno de los valores de la lista (que puede ser una subconsulta ) de la parte derecha cumplen la condición asociada.
· EXIST la comparación es cierta cuando la lista de la parte derecha (que puede ser una subconsulta) contiene o devuelve algún valor.
· SINGULAR la comparación es cierta cuando la lista de la parte derecha (que puede ser una subconsulta) contiene o devuelve un único valor.

Devolviendo un valor simple que se usa con un operador de comparación.

Testeando si los datos cumplen o no unas condiciones especiales usando el operador EXITS.

La cadena de padres e hijos puede complicarse con nietos, biznietos etc. Las subconsultas se suelen usar cuando no es posible recuperar datos con un simple SELECT.

SELECT COUNTRY, AREA
FROM COUNTRIES
WHERE AREA > (SELECT AVG(AREA) FROM COUNTRIES);
ORDER BY AREA;

Existen también otras subconsultas más complicadas, en las que la subconsulta varía por cada columna evaluada en la consulta padre, ya que un elemento de la condición de la consulta es una columna de alguna tabla de la consulta padre. por ejemplo:

SELECT COUNTRY
FROM COUNTRIES COU
WHERE 3 <= (SELECT COUNT (*)
FROM CITIES CIT
WHERE CIT.CITY = COU.CAPITAL);

El problema es que cada suconsulta se ejecuta por cada fila de la consulta padre.

Casos especiales: Tratamiento de nulos. Formato de fechas

Las columnas con nulos son ordenadas después de todas las restantes.

Los valores nulos no satisfacen una condición de reunión.

Un valor nulo puede ser evaluado en una condición devolviendo siempre el valor UNKNOW.

Con respecto a los operadores boléanos:

NOT con NULL es siempre desconocido.

NULL con AND es desconocido a no ser que el otr sea falso, siendo entonces falso.

NULL con OR retorna desconocido a no ser que el otro sea cierto, devolviendo entonces cierto.

En cuanto a los formatos de fechas válidos tanto para consultas como para inserciones de datos tenemos:

‘yyy-mm-dd’ ‘yyyy/mm/dd’ ‘yyyy mm dd

‘yyyy:mm:dd’ ‘yyyy.mm.dd’

‘mm-dd-yy’ ‘mm-dd-yyyy’ ‘mm/dd/yy’ ‘mm/dd/yyyy’

‘mm dd yy’ ‘mm dd yyyy’ ‘mm:dd:yy’ ‘mm:dd:yyyy’

‘dd.mm.yy’ ‘dd.mm.yyyy’

‘dd-xxx-yy’ ‘dd-xxx-yyyy’ ‘xxx-dd-yy’ ‘xxx-dd-yyyy’

‘dd xxx yy’ ‘dd xxx yyyy’ ‘xxx dd yy’ ‘xxx dd yyyy’

‘dd:xxx:yy’ ‘dd:xxx:yyyy’ ‘xxx:dd:yy’ ‘xxx:dd:yyyy’

xxx es una abreviatura de mes en inglés.

Ejemplo con la fecha ”Enero 22, 1943:”

INSERT INTO t1 VALUES (‘1943-01-22’);
INSERT INTO t1 VALUES (‘01/22/1943’);
INSERT INTO t1 VALUES (‘22.01.1943’);
INSERT INTO t1 VALUES (‘jan 22 1943’);

y si es el 2043 entonces

INSERT INTO t1 VALUES (‘01/22/43’);

Lenguaje de control de datos DCL

La seguridad en SQL se controla a nivel de tabla con los privilegios de acceso, una lista de operaciones que un usuario tiene permitido ejecutar sobre una tabla o vista. La sentencia GRANT asigna privilegios de acceso para una tabla o vista a un conjunto de usuarios, roles u objetos como procedimientos almacenados y triggers. GRANT puede permitir a los usuarios o procedimientos almacenados ejecutar procedimientos almacenados usando el privilegio EXECUTE y puede otorgar roles a los usuarios. Usaremos REVOKE para eliminar privilegios asignados previamente con GRANT.

GRANT puede ser usado para lo siguiente:

Conceder privilegios para SELECT, INSERT, UPDATE, DELETE y REFERENCES una tabla a usuarios, triggers, stored procedures, o views (opcionalmente WITH GRANT OPTION).

Conceder privilegios para SELECT, INSERT, UPDATE y DELETE una vista a usuarios, triggers, stored procedures, o views (opcionalmente WITH GRANT OPTION).

Conceder privilegios para SELECT, INSERT, UPDATE, DELETE y REFERENCES una tabla a un rol.

Conceder privilegios para SELECT, INSERT, UPDATE y DELETE una vista a un rol.

Conceder un rol a unos usuarios.

Conceder permiso EXECUTE de un procedimiento almacenado a usuarios, triggers, procedimientos almacenados o views (opcionalmente WITH GRANT OPTION).

La asignación por defecto en Interbase es que el propietario o creador del objeto es el que posee todos los permisos, y los demás usuarios no poseen ningún permiso a excepción del ADMINISTRADOR (SYSDBA).

Privilegios disponibles:

Por último usaremos los roles cuando las condiciones así lo determinen, asumiendo que necesitaremos 4 operaciones para asignar roles:

· Crear el rol con la sentencia CREATE ROLE.

· Asignar privilegios a el rol usando GRANT privilegio TO nombre de rol.

· Conceder el uso del rol a los usuarios usando GRANT nombre de rol TO usuarios.

· Especificar el rol que se usa cuando un usuario accede a una base de datos.

Autor: Antonio Lopez Atienza