Implementacion del lenguaje SQL en
MS-JET y otras B.D.
Este documento recoge mis apuntes personales tomados en un curso de
autoformación sobre el lenguaje SQL. No pretende ser un documento exhaustivo ni
contemplar todas las variantes del lenguaje, pero puede servir de guía de
referencia para el que este empezando.
SELECT DISTINCT Columna1 FROM tabla [WHERE Condición]
Selecciona solo los valores distintos de Columna1. (Si valores de Columna1
son 1,2,2,3 el SELECT solo devolverá 1,2,3)
SELECT Columna1, ‘Constante’, Columna2 FROM tabla [WHERE Condición]
Crea una selección con 3 columnas, fijando el valor de la 2ª columna como
‘Constante’.
SELECT Columna1, Columna2…. INTO NuevaTabla [IN BaseDatosExterna] FROM
Tabla1 [WHERE Condicion] Crea una tabla nueva con las columnas y registros
seleccionados de la tabla origen. La clausula IN permite crear la tabla nueva en
una base de datos externa (que no sea la propia)
Operadores:
Numericos:
+, -, *, / (ej. SELECT
Columna1 + 5 FROM Tabla WHERE Columna2 / 4 = Columna3 *5;)
El "-" delante de una columna numerica cambia su signo (ej. SELECT
–Columna1 FROM Tabla)
Resto de la división
MOD (ej. SELECT Columna1 MOD 2
FROM…)
% Resto de la division (Ej. SELECT
Columna1 FROM Tabla WHERE Columna1 % 2 = 0;) (*1)
En otras implementaciones es MOD(Dividendo, Divisor) (*1)
Alfanumericos
Concatenación
+ para concatenar cadenas (ej.SELECT
Columna1 + Columna2 FROM…..)
En otras implementaciones se usa "||" en vez de "+", o
bien la funcion CONCAT (*1)
Logicos
AND
OR
NOT Negativiza la condicion. (Ej. ….NOT
LIKE ‘A*’ / ….IS NOT NULL)
Agrupamiento
UNION Junta el resultado de dos queries, eliminando los elementos
duplicados del resultado
(Ej. SELECT Columna1 FROM Tabla1
UNION SELECT Columna1 FROM Tabla2;)
UNION ALL Junta el resultado de dos queries, sin eliminar los elementos
duplicados del resultado (Ej. SELECT
Columna1 FROM Tabla1 UNION ALL SELECT Columna1 FROM Tabla2;)
INTERSECT solo devuelve los elementos comunes a ambas selecciones (*1) (Ej. SELECT
Columna1 FROM Tabla1 INTERSECT SELECT Columna1 FROM Tabla2;)
MINUS devuelve las filas resultantes del primer query que no este
presentes en el segundo (*1)
(Ej. SELECT Columna1 FROM Tabla1 MINUS
SELECT Columna1 FROM Tabla2;)
Otros
IN Hacer referencia a un conjunto de valores
(Ej. SELECT Columna1 FROM Tabla1
WHERE Columna1 IN(‘Valor1’, ‘Valor2’, ‘Valor3’;)
BETWEEN hace referencia a un rango de valores (ambos inclusive)
(Ej. SELECT Columna1 FROM Tabla1 WHERE
Columna1 BETWEEN Valor1 AND Valor2;)
Funciones
Acumulación/Agrupación
COUNT devuelve el numero de filas resultantes de
la selección
(Ej. SELECT COUNT(*) FROM Tabla1 WHERE
……)
SUM devuele la suma de los valores de las columnas resultado
(Ej. SELECT SUM(Columna1), SUM(Columna2)
FROM …..)
AVG devuelve la media de los valores de las columnas resultado
(Ej. SELECT AVG(Columna1), AVG(Columna2)
FROM …..)
MAX devuelve el valor máximo de una columna resultado. El valor puede
ser numerico o alfanumerico. (Ej. SELECT
MAX(Columna1) FROM….)
MIN devuelve el valor mínimo de una columna resultado. El valor puede
ser numerico o alfanumerico. (Ej. SELECT
MIN(Columna1) FROM….)
VARIANCE devuelve el cuadrado de la desviación estandar (varianza) de
los valores de una columna (*1)
(Ej. SELECT VARIANCE(Columna1) FROM…)
En el caso de MS-JET VAR
devuelve la varianza de una muestra de la población y
VARP la varianza de la poblacion.
STDDEV devuelve la desviación estandar de los
valores de una columna (*1)
(Ej. SELECT STDDEV(Columna1) FROM…)
En el caso de MS-JET STDEV
devuelve la desviación estandar de una muestra de la población y
STDEVP la desviacion estandar de la poblacion.
Fecha/Hora
ADD_MONTHS añade meses a una fecha (*1) (Ej. SELECT
ADD_MONTHS(Columna1, 5) FROM….)
LAST_DAY devuelve el ultimo dia del mes correspondiente a una fecha (*1)
(Ej. SELECT LAST_DAY(Columna1) FROM….)
MONTHS_BETWEEN calcula los meses entre dos fechas (*1)
(Ej. SELECT MONTHS_BETWEEN(FechaFin,
FechaInic) FROM….)
NEW_TIME calcula la fecha/hora correspondiente a una zona horaria (*1)
(Ej. SELECT NEW_TIME(Fecha, ‘EDT’,
‘PDT’) FROM ….)
NEXT_DAY devuelve el siguiente dia de la semana especificado a la fecha (*1)
(Ej. SELECT NEXT_DAY(Fecha, ‘FRIDAY’)
FROM …)
SYSDATE devuelve la fecha del sistema (*1)
(Ej. SELECT Columna1 FROM Tabla1 WHERE
Columna1 < SYSDATE;)
Aritméticas
ABS devuelve el valor absoluto (sin signo) de un numero (Ej. SELECT
ABS(Columna1) FROM….)
FLOOR devuele el entero igual o menor al valor de la columna (*1) (Ej. SELECT
FLOOR(2.3) FROM….. devuelve un 2)
CEIL devuelve el entero igual a mayor al valor de la columna (*1)
(Ej. SELECT CEIL(2.3) FROM…..
devuelve un 3)
INT hace lo mismo que FLOOR y es
soportado por MS-JET
COS, SIN y TAN realizan las funciones
trigonometricas correspondientes, teniendo en cuenta que el argumento debe estar
en radianes, no en grados.
COSH, SINH y TANH realizan las funciones trigonometricas
correspondientes (*1)
EXP calcula el exponente
LN calcula el logaritmo natural (Ej. SELECT
LN(Columna1) FROM….) (*1)
LOG calcula el logaritmo en una base determinada
(Ej. SELECT LOG(Columna1, 10) FROM….)
En el caso de MS-JET LOG
calcula el logaritmo natural (Ej. SELECT
LOG(Columna1) FROM…)
MOD develve el resto de la division (Ej. SELECT
MOD(Dividendo, Divisor) FROM…)
En el caso de MS-JET
la sintaxis es SELECT Dividendo MOD
Divisor FROM….
POWER eleva un numero a una potencia (Ej. SELECT
POWER(Nro, Potencia) FROM …)
En el caso de MS-JET
se usa el operador ^ (Ej. SELECT Nro ^
Potencia FROM…)
SIGN devuelve un valor en funcion del signo. Si negativo devuelve –1,
si cero devuelve 0 y si positivo devuelve 1. (Ej. SELECT
Columna1 FROM Tabla1 WHERE SIGN(Columna1) = -1)
SQRT devuelve la raiz cuadrada de un numero.
En el caso de MS-JET se debe usar SQR.
Alfanumericas
CHR devuelve el carácter correspondiente a el codigo ASCII
(Ej. SELECT Codigo, CHR(Codigo) FROM….)
CONCAT concatena dos cadenas de caracteres
(Ej. SELECT CONCAT(Columna1, Columna2)
FROM ….)
En el caso de MS-JET
se debe usar el operador "+"
INITCAP Convierte la primera letra a mayusculas y el resto a minusculas (*1)
LOWER y UPPER convierten a minusculas y mayusculas
respectivamente
En el caso de MS-JET
se debe usar LCASE y UCASE
LPAD y RPAD rellenan con un carácter a la izquierda y derecha
respectivamente en una longitud dada (*1)
(Ej. SELECT LPAD(Columna1, 23, ‘*’)
FROM…)
LTRIM y RTRIM eliminan un cierto caracter de la izquierda y la derecha
respectivamente. En casod de no especificarse el carácter se asume que es el
carácter blanco.
(Ej. SELECT LTRIM(Columna1, ‘*’)
FROM…. / o bien SELECT LTRIM(Columna1) FROM….)
En el caso de MS-JET
no puede especificarse el carácter a eliminar y además existe TRIM que elimina
los blancos de la izquierda y la derecha.
REPLACE reemplaza una cadena de caracteres por otra. En caso de que no
se especifique la nueva cadena lo que hace es eliminar la antigua. (*1)
(Ej. SELECT REPLACE(Columna1, ‘Ini’,
‘Fin’) FROM… / o bien SELECT
REPLACE(Columna1, ‘Ini’) FROM…
SUBSTR extrae parte de una cadena de caracteres.
(Ej. SELECT SUBSTR(Columna1, Posicion,
Longitud) FROM…)
En el caso de MS-JET se
debe usar MID, LEFT o RIGHT
TRANSLATE traduce carácter a carácter según la tabla especificada (*1)
(Ej. SELECT TRANSLATE(Columna1, ‘ABCDE’,
‘12345’) FROM…)
INSTR busca la ocurrencia de una cadena dentro de otra
(Ej. SELECT INSTR(Columna1, ‘A’,
PosInicial, NroOcurrencia) FROM…)
Si no se especifican PosInicial y/o NroOcurrencia se asume 1
En el caso de MS-JET el
formato es INSTR(PosInicial, Columna1, ‘A’)
LENGTH devuelve la longitud del argumento. (Ej. SELECT
LENGTH(Columna1) FROM..) En el caso de MS-JET se
debe usar LEN(Arg)
Conversión
TO_CHAR convierte un numero en una cadena de caracteres
En el caso de MS-JET se
debe usar STR
TO_NUMBER convierte una cadena de caracteres en un numero En el caso de MS-JET se
debe usar VAL
Otras funciones
GREATEST and LEAST devuelven el valor mayor o menor
respectivamente de una serie de datos (*1)
USER devuelve el nombre del usuario de la base de
datos (*1)
Clausulas
WHERE
Para especificar la condicion de selección de registros
SELECT * FROM Tabla1 WHERE condicion
Algunos ejemplos de condiciones:
ColumnaAlfaNum = ‘cadena’ (Tambien se puede usar >, >=, <,
<=, <>, !=
(*1))
ColumnaNum = numero (Tambien se puede usar >, >=, <, <=,
<>, != (*1))
Columna IS NULL
Columna LIKE ‘cadena*’ / Columna LIKE ‘ca?ena’
Columna LIKE ‘cadena%’ / Columna LIKE ‘ca_ena’ (*1)
Columna STARTING WITH(‘cadena’) (*1)
ORDER BY
Para ordernar el resultado de la selección por una o varias columnas
SELECT * FROM Tabla1 ORDER BY Columna1 [,Columna2,…] [ASC]/[DESC]
Si no especifica ni ASC ni DESC se asume ordenación ascendente
Tambien se pueden combinar ordenaciones ascencentes y descendentes
(Ej. SELECT * FROM Tabla1 ORDER BY
Columna1 ASC, Columna2 DESC)
GROUP BY
Permite agrupar resultados.
SELECT Columna1, SUM(Columna2) FROM Tabla1 GROUP BY Columna1 Devuelve la suma de los valores de Columna2
correspondientes a cada uno de los valores diferentes de Columna1.
SELECT Columna1, COUNT(Columna2) FROM Tabla1 GROUP BY Columna1
Devuelve el numero de valores de Columna2 correspondientes a cada uno de los
valores diferentes de Columna1
También se pueden agrupar resultados por varias columnas (….GROUP BY
Columna1, Columna2,…..)
HAVING
Permite establecer condiciones dentro de agrupaciones, donde no se puede
usar la clausula WHERE
SELECT Columna1, SUM(Columna2) FROM Tabla1 GROUP BY Columna1 HAVING
SUM(Columna2) > Valor1
Devuelve la suma de los valores de Columna2 correspondientes a cada uno de los
valores diferentes de Columna1, simpre que el resultado de la suma sea mayor que
Valor1
Unión de tablas
Ejemplos con estas dos tablas:
Tabla PROD:
Cod
Descr
Precio
1
Producto1
100
2
Producto2
200
3
Producto3
300
4
Producto4
400
Tabla ORD:
CodOrden
CodProd
Cant
901
1
5
902
3
7
903
7
3
904
3
4
Cross-Join
SELECT Ord.CodOrden, Prod.Cod,
Prod.Descr, Ord.Cant FROM Prod, Ord;
Cruza ambas tablas dando como resultado una fila por cada combinación de
registros de ambas tablas. Es decir que si una tabla tiene 4 registros y la otra
otros 4, el resultado del select dará 16 filas (4x4)
Para especificar el nombre de una olumna de una tabla determinada se puede
asignar un alias a la tabla (Ej. SELECT
T1.Columna1, T2.Columna1 FROM Tabla1 T1, Tabla2 T2)
Resultado del Select
CodOrden
Cod
Descr
Cant
901
1
Producto1
5
901
2
Producto2
5
901
3
Producto3
5
901
4
Producto4
5
902
1
Producto1
7
902
2
Producto2
7
902
3
Producto3
7
902
4
Producto4
7
903
1
Producto1
3
….
….
….
….
Equi-Join
SELECT Ord.CodOrden, Prod.Cod, Prod.Descr, Ord.Cant FROM Prod, Ord
WHERE Prod.Cod = 3;
Da como resultado los elementos que cumplen la condicion especificada en la
clausula WHERE
Se pueden añadir mas tablas y mas condiciones a la clausula WHERE.
Internamente funciona haciendo un cross-join y aplicando la clausula WHERE
sobre ese resultado Resultado del Select
CodOrden
Cod
Descr
Cant
901
3
Producto3
5
902
3
Producto3
7
903
3
Producto3
3
904
3
Prodcuto3
4
Non-equi-join
SELECT Ord.CodOrden, Prod.Cod, Prod.Descr, Ord.Cant FROM Prod, Ord
WHERE Prod.Cod > 3;
Da como resultado los elementos que cumplen la condicion especificada en la
clausula WHERE, que puede ser cualquiera menos "="
Internamente funciona haciendo un cross-join y aplicando la clausula WHERE
sobre ese resultado Resultado del Select
CodOrden
Cod
Descr
Cant
901
4
Producto4
5
902
4
Producto4
7
903
4
Producto4
3
904
4
Prodcuto4
4
Inner-Join
SELECT Ord.CodOrden, Prod.Cod, Prod.Descr, Ord.Cant FROM Ord
JOIN Prod ON Ord.CodProd = Prod.Cod;
Da como resultado los elementos que tienen un valor
comun en una columna de cada tabla.
La condicion se evalua antes de juntar las tablas, de tal manera que solo
se juntan los registros que previamente cumplen la condicion
En el caso de MS-JET se debe especificar "….INNER
JOIN…." Resultado del Select
CodOrden
Cod
Descr
Cant
901
1
Producto1
5
902
3
Producto3
7
904
3
Producto3
4
Right-Outer
Join
SELECT Ord.CodOrden, Prod.Cod, Prod.Descr, Ord.Cant FROM Ord
RIGHT OUTER JOIN Prod ON Ord.CodProd = Prod.Cod;
Devuelve el cojunto completo de valores de la tabla de la derecha ("prod"
en este caso), dejando nulas las columnas de tabla de la izquierda que no
cumplen la condicion.
Resultado del Select
CodOrden
Cod
Descr
Cant
901
1
Producto1
5
2
Producto2
902
3
Producto3
7
904
3
Producto3
4
4
Prodcuto4
Left-Outer Join
SELECT Ord.CodOrden, Prod.Cod, Prod.Descr, Ord.Cant FROM Ord
LEFT OUTER JOIN Prod ON Ord.CodProd = Prod.Cod;
Devuelve el cojunto completo de valores de la tabla de la izquierda ("ord"
en este caso), dejando nulas las columnas de tabla de la derecha que no
cumplen la condicion.
Resultado del Select
CodOrden
Cod
Descr
Cant
901
1
Producto1
5
902
3
Producto3
7
903
3
904
3
Producto3
4
Sub-Queries
Una Sub-query es una query cuyo resultado es pasado como argumento a otra
query.
Ejemplo:
SELECT * FROM Ord
WHERE CodProd = (SELECT Cod FROM Prod
WHERE Descr = 'Producto3');
Devuelve las ordenes correspondientes al ‘Producto3’, sin necesidad de
saber el codigo del producto.
Resultado del Select
CodOrden
CodProd
Cant
902
3
7
904
3
4
Y si quisieramos obtener tambien la descripcion del producto entonces
podriamos usar:
SELECT Ord.CodOrden, Ord.CodProd, Prod.Descr, Ord.Cant FROM Ord, Prod
WHERE Ord.CodProd = Prod.Cod
AND Ord.CodProd = (SELECT Cod FROM Prod
WHERE Descr = 'Producto3');
Resultado del Select
CodOrden
CodProd
Descr
Cant
902
3
Producto3
7
904
3
Producto3
4
Esto solo funciona si la subquery (SELECT
Cod FROM Prod WHERE Descr = 'Producto3') solo devuelve
una fila. P.E. si hubieramoss usado SELECT
Cod FROM Prod WHERE Descr like 'Producto%' el select
daria error ya que devolveria 4 filas (Producto1, Producto2,….)
Y podriamos complicarlo mas, por ejemplo para averiguar las ordenes cuyo
importe este por encima de la media.
SELECT Ord.CodOrden, Ord.CodProd, Prod.Descr, Ord.Cant * Prod.Precio AS Total
FROM Ord,Prod
WHERE Ord.CodProd = Prod.Cod
AND Ord.Cant * Prod.Precio > (SELECT AVG(Ord.Cant * Prod.Precio) FROM Ord,
Prod
WHERE Ord.CodProd = Prod.Cod);
Resultado del Select
CodOrden
CodProd
Descr
Total
902
3
Producto3
2100
Sub-queries anidados
Un subquery anidado es un query incrustado en otro query.
Ejemplo:
SELECT * FROM Ord
WHERE Ord.CodProd IN (SELECT Cod FROM Prod
WHERE Precio <= 300);
Selecciona las ordenes de productos cuyo precio es inferior o igual a 300
Resultado del Select
CodOrden
CodProd
Cant
901
1
5
902
3
7
904
3
4
Un Subquery tambien puede ponerse en una clausual GROUP BY…HAVING
Ejemplo:
SELECT CodProd, SUM(Cant) AS NroVentas FROM Ord
GROUP BY CodProd
HAVING SUM(Cant) > (SELECT AVG(Cant) FROM Ord);
Selecciona los productos cuyo numero de ventas totales (SUM) es superior a la
media (AVG) de ventas de todos los productos.
Resultado del Select
CodProd
NroVentas
1
5
3
11
Sub-queries correlacionados
Los subqueries pueden hacer referencias externas lo que provoca que se
comporten de manera parecida a un "join".
Por Ejemplo:
SELECT * FROM Ord
WHERE (SELECT Descr FROM Prod
WHERE Ord.CodProd = Prod.Cod) = 'Producto3';
Es equivalente a:
SELECT Ord.CodOrden, Ord.CodProd, Ord.Cant FROM Ord, Prod
WHERE Ord.CodProd = Prod.Cod
AND Prod.Descr = 'Producto3';
En ambos casos el resultado del Select es:
CodOrden
CodProd
Cant
902
3
7
904
3
4
La palabra clave EXISTS
EXISTS toma un subquery como argumento y devuelve "verdadero" si el
subquery devuelve algo y "falso" si no devuelve nada.
Por ejemplo:
SELECT * FROM Prod
WHERE EXISTS (SELECT * FROM Prod
WHERE Prod.Cod = 3);
Devolverá la tabla "Prod" entera:
Cod
Descr
Precio
1
Producto1
100
2
Producto2
200
3
Producto3
300
4
Producto4
400
Ya que EXISTS (SELECT * FROM Prod
WHERE Prod.Cod = 3) devuelve "verdadero"
para cada una de las filas resultantes del SELECT
* FROM Prod
Y por otra parte:
SELECT * FROM Prod
WHERE EXISTS (SELECT * FROM Prod
WHERE Prod.Cod = 9);
No devolverá datos, ya que EXISTS
(SELECT * FROM Prod WHERE Prod.Cod = 3) devuelve
"falso"
Y si se utiliza EXISTS en un query correlacionado, este es evaluado en cada
caso implicado en la correlacion. Asi, por ejemplo para buscar los productos que
tiene alguna orden asociada se puede usar:
SELECT Cod, Descr FROM Prod
WHERE EXISTS (SELECT * FROM Ord
WHERE Ord.CodProd = Prod.Cod);
Que devolverá:
Cod
Descr
1
Producto1
3
Producto3
Las palabras clave ANY y SOME
Su funcion es identica y puede usarse tanto una como otra.
Comparan el resultado del subquery con cada una de las filas del query,
devolviendo "verdadero" por cada fila del query que tienen un
resultado en el subquery.
Asi P.E. tenemos otra manera de buscar los productos que tienen alguna orden
asociada:
SELECT Cod, Descr FROM Prod
WHERE Cod = ANY (SELECT CodProd FROM Ord);
Que también devolverá:
Cod
Descr
1
Producto1
3
Producto3
Esto puede parecer igual que el resultado obtenido usando "IN" (SELECT
Cod, Descr FROM Prod WHERE Cod IN (SELECT CodProd FROM Ord);).
La diferencia estriba que "ANY" puede ser usado con otros operadores
distintos de "=", tales como ">", "<",
"<>", etc.
Inserción de datos (INSERT)
INSERT….VALUES
INSERT INTO Tabla1 (Columna1, Columna2…) VALUES (‘ValorAlfa’, ValorNum…); Inserta un registro nuevo con los valores
especificados
No es obligatorio especificar las columnas a insertar, y en ese caso se
asumen todos las columnas en el orden en que estan definidas en la tabla. INSERT INTO Tabla1 VALUES (‘ValorAlfa’,
ValorNum…);
En caso de no especificar alguna columna en la insercion, esta quedara NULL
Si se intenta insertar algun valor ya existente en una columna definida como
UNIQUE se producira un error. Para controlar esto algunas implementaciones
pemiten la siguiente sintaxis: IF NOT EXISTS (SELECT * FROM Tabla1
WHERE ColumnaX = ValorX) INSERT INTO Tabla1 (ColumnaX, Columna2…) VALUES (ValorX,
Valor2…); (*1)
INSERT….SELECT
INSERT INTO Tabla1 [IN BaseDatosExterna] SELECT * FROM Tabla2; Inserta todos los registros de la tabla2 en la tabla1.
Si se especifica la clausula IN se insertan los registros en una tabla de una
base de datos externa.
INSERT INTO Tabla1 (Columna1, Columna2…) SELECT Columna1, Columna2 FROM
Tabla2 WHERE ……. Inserta las columnas especificadas de los registros de
la Tabla2 que cumplan la clausula WHERE
Actualización de datos (UPDATE)
UPDATE Tabla1 SET Columna1 = ‘ValorAlfa’, Columna2 = ValorNim… WHERE
Condicion
Actualiza las columnas con los valores especificados de los registros que
cumplan la condición.
UPDATE Tabla1 SET Columna1 = ‘ValorAlfa’, Columna2 = ValorNim…
Actualiza las columnas con los valores especificados de todos los
registros de la tabla
Algunas implementaciones permiten actualizar los valores de una tabla
basandose en los valores de otras tablas UPDATE Tabla1 SET Columna1 = Valor1,
Columna2 = Valor2… FROM Tabla2 [,Tabla3….] WHERE Condicion (*1)
Borrado de datos (DELETE)
DELETE FROM Tabla1 WHERE Condicion Borra todos los registros que cumplen la condicion
Creación de una base de datos (CREATE DATABASE)
CREATE DATABASE NombreBaseDatos
Crea la base de datos especificada (*1)
Creación de una tabla (CREATE TABLE)
CREATE TABLE TablaX (Columna1, TipoDatos[(Tamaño)] [NOT NULL], Columna2, TipoDatos[(Tamaño)] [NOT
NULL],………….);
Los tipos de datos posibles dependen de la implementacion de cada gestor de base
de datos.
Si no se especifica el tamaño se tomara el que tenga definido el sistema por
defecto.
Si se especifica NOT NULL esa columna no aceptara valores nulos.
En MS-JET tambien se pueden crear los indices mediante
la clausula CONSTRAINT. Estos son algunos ejemplos:
Crear una tabla con tres campos (Sin indice)
CREATE TABLE Clientes (Codigo
INTEGER, Nombre TEXT(14), Apellido TEXT(14));
Crear una tabla con tres campos y un indice unico formado por esos tres
campos
Algunos Gestores de Base de Datos tambien permiten crear una tabla a partir
de la definición de otra tabla mediante la clasula AS (*1) CREATE TABLE TablaX (Columna1,
Columna2….) AS (SELECT Columna1, Columna2…FROM TablaY WHERE Condicion);
Modificación de la estructura de una tabla (ALTER
TABLE)
ALTER TABLE TablaX ADD ColumnaX TipoDatos[(Longitud)] [NOT NULL]; Para añadir una columna a la tabla
ALTER TABLE TablaX MODIFY (ColumnaX TiposDatos[(Longitud)] [NULL]; Se puede utilizar en algunos gestores de B.D. para
cambiar de NOT NULL a NULL (*1)
En MS-JET tambien se pueden crear los indices mediante
la clausula CONSTRAINT. Por ejemplo:
Agregar una clave primaria a la tabla Prod
ALTER TABLE Prod ADD CONSTRAINT NuevaClave PRIMARY KEY (Cod);
En MS-JET tambien se pueden eliminar columnas o indices. Por
ejemplo:
Eliminar la columna precio de la tabla Prod
ALTER TABLE Prod DROP Precio;
Eliminar una clave primaria de la tabla Pod
ALTER TABLE Prod DROP CONSTRAINT NuevaClave;
Eliminación de una tabla (DROP TABLE)
DROP TABLE TablaX; Elimina permanentemente una
tabla de la base de datos.
Eliminación de una base de datos (DROP DATABASE)
Algunos gestores de B.D. soportan
DROP TABLE TablaX; para eliminar permanentemente
una base de datos. (*1)
Creacion de una vista (CREATE VIEW) (*1)
CREATE VIEW NombreView [(Columna1, Columna2,…)] AS
SELECT ….. FROM …..;
Eliminación de una vista (DROP VIEW) (*1)
DROP VIEW NombreView;
Creacion de un sinonimo (CREATE SYNONYM) (*1)
CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object[@dblink]
Eliminación de un sinónimo (DROP SYNONYM) (*1)
DROP [PUBLIC] SYNONYM synonym_name;
Creacion de un índice (CREATE INDEX)
El formato genérico de esta sentencia es CREATE
INDEX NombreIndice ON TablaX (Columna1, Columna2,…); pero
varia mucho según el gestor de B.D.
En el caso de MS-Jet el formato es:
CREATE [UNIQUE] INDEX Indice
ON TablaX (Campo1 [ASC / DESC][, Campo2 [ASC / DESC]…..]
[WITH {PRIMARY, DISALLOW NULL, IGNORE NULL}];
Eliminacion de un indice (DROP INDEX)
DROP INDEX NombreIndex;
En el caso de MS-Jet el formato es:
DROP INDEX NombreIndex ON TablaX;
Trabajo con transacciones
La implementacion es muy diferente de un gestor de B.D. a otro. Esta es la sintaxis
usada por SQL-Server, por poner un ejemplo.
BEGIN TRANSACTION NombreTransacción; para
iniciar una transaccion
COMMIT TRANSACTION NombreTransacción; para
finalizar una transaccion confirmando los cambios.
ROLLBACK TRANSACTION NombreTransacción; para
finalizar una transaccion cancelando los cambios.
SAVE NombreTransaccion NombreSavePoint;
establece un "SavePoint"
ROLLBACK TRANSACTION NombreSavePoint; deshace
los cambios realizados desde el "SavePoint" especificado.
Y este podria ser un ejemplo real.
BEGIN TRANSACTION
UPDATE Balances SET Curr_Bal = 25000 WHERE Account_ID = 5
SAVE TRANSACTION save_it
DELETE FROM Balances WHERE Account_ID = 5
ROLLBACK TRANSACTION save_it
COMMIT TRANSACTION
GO
Las B.D. MS-Access no soportan el trabajo con transacciones.
Seguridad de la base de datos
Los sistemas de base de datos mas sencillos, MS-Access por ejemplo, no
soportan practicamente ningun tipo de seguridad. Los ejemplos siguientes estan
basados en la implementacion de Personal Oracle 7.
Creacion de usuarios:
CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile];
Modificacion de usuarios
ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile] [DEFAULT ROLE { role [, role] ... | ALL [EXCEPT role [, role]
...] | NONE}];
Eliminacion de usuarios:
DROP USER user_name [CASCADE];
Asignacion de "Roles". Un "Rol"
es un privilegio o conjunto de privilegios que permiten a un usuario realizar
ciertas funciones en la base de datos.
Asignacion de "Roles" a usuarios
GRANT role TO user [WITH ADMIN OPTION];
Revocación de "Roles" a usuarios:
REVOKE role FROM user;
Algunos de los "roles" existentes son:
CONNECT (Puede consultar, modificar y crear tablas. Tambien puede crear
views)
RESOURCE (Tambien puede crear procedures, triggers e indices)
DBA (Todos los privilegios)
Asignacion de privilegios al usuario. Permiten asignar los permisos que
tienen los usuarios sobre los objetos de la B.D.
Se pueden asignar privilegios de sistema y privilegios de objeto
Los privilegios de sistema afectan a todo el sistema y se asignan con:
GRANT system_privilege TO {user_name | role | PUBLIC} [WITH ADMIN OPTION];
Los privilegios de objeto afectan solo al objeto en cuestion y se asignan
con:
GRANT {object_priv | ALL [PRIVILEGES]} [ (column
[, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column
[, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]
y se revocan con:
REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]
[CASCADE CONSTRAINTS]
Y ahora un caso practico:
Crear una tabla
CREATE TABLE SALARIES (NAME CHAR(30), SALARY NUMBER, AGE NUMBER);
Crear dos usuarios:
CREATE USER Jack IDENTIFIED BY Jack;
CREATE USER Jill IDENTIFIED BY Jill;
Asignarles los roles:
GRANT CONNECT TO Jack;
GRANT RESOURCE TO Jill;
Cargar la tabla con los siguientes datos:
NAME SALARY AGE
---- ------ ---
JACK 35000 29
JILL 48000 42
JOHN 61000 55
Hacer que Jack solo pueda hacer SELECTs
GRANT SELECT ON SALARIES TO Jack;
Y que Jill puede hacer SELECTs y ademas pueda actualizar la columna "Salary".
GRANT SELECT, UPDATE(SALARY) ON SALARIES TO Jill;
Ahora bien, para que puedan acceder a la tabla "Salaries" deberan
referenciarla (cualificarla) con el nombre de usuario propietario de la tabla
(el que la creó). P.E. SELECT *
FROM Bryan.SALARIES; suponiendo que el usuario que
el propietario de la tabla sea "Bryan".
Para no tener que cualificar la tabla cada vez con el nombre de su
propietario se puede crear una view. P.E.: CREATE
VIEW SALARY_VIEWAS SELECT
FROM Bryan.SALARIES;
Otra posibilidad seria que un usuario con rol DBA creara un sinonimo publico
con CREATE PUBLIC SYNONYM SALARY FOR
SALARIES;
Tambien se pueden usar views para controlar los datos a los que los usuarios
pueden accecer. P.E. para evitar que Jack pueda mirar los salarios de otras
personas:
CREATE VIEW JACK_SALARY AS SELECT * FROM BRYAN.SALARIES
WHERE NAME = 'JACK';
GRANT SELECT ON JACK_SALARY TO Jack;
Normalmente un usuario no puede pasar sus privilegios a otros usuarios, salvo
que estos le hayan sido concedidos especificando la clausula WITH GRANT OPTION.
Asi P.E.
GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO JILL
WITH GRANT OPTION;
Permitira que Jill pueda pasar estos privilegios a otros usuarios.
Tablas temporales
Son tablas de trabajo que solo existen hasta que el usuario se desidentifica
o finaliza la conexión con la B.D.
La implementacion difiere entre los diferentes gestores de B.D. y algunos no
lo soportan (MS-Access P.E.)
Estos ejemplos estan basados en la implementación del SQL-Server.
Con lo cual la tabla temporal tambien es creada en B.D. "tempdb",
pero no se elimina automaticamente al desidentificarse el usuario, sino que este
tendra que hacerlo expresamente con un DROP TABLE. (También todas las tablas
temporales se borran al rearrancar el SQL-Server)
Las tablas temporales son normalmente usadas para almacenar el resultado de
consultas complejas que puede ser usado en otras consultas.
Cursores
Los cursores permiten seleccionar un grupo de datos, moverse a traves del
grupo de registros (recordset) y examinar individualmente los datos a los que
apunta el cursor.
La implementacion difiere entre los diferentes gestores de B.D. y algunos no
lo soportan
Estos ejemplos estan basados en la implementación del SQL-Server (Transact-SQL).
DECLARE NombreCursor CURSOR
FOR sentencia_select
[FOR {READ ONLY | UPDATE [OF lista_nombres_columnas]}]
Por ejemplo: CREATE
Artists_Cursor CURSOR FOR SELECT * FROM Artists; crea
un cursor que contiene todos los registros de la tabla Artists, pero antes de
usarlo hay que abrirlo con:
OPEN Artists_Cursor;
Una vez abierto ya puede ser usado para moverse por las filas resultantes de
la consulta:
FETCH cursor_name [INTO fetch_target_list];
Cada vez que se hace un FETCH el cursor avanza a la siguiente fila.
Y a continuacion lo normal seria cerrar el cursor con:
CLOSE Artists_Cursor;
Y liberar la memoria asociada con el:
DEALLOCATE CURSOR Artists_Cursor;
Un ejemplo práctico que declara unas variables, las carga con los datos de
cada una de las filas y las imprime.
CREATE Artists_Cursor CURSOR FOR SELECT * FROM Artists
OPEN Artists_Cursor
FETCH Artists_Cursor INTO @name, @homebase, @style, @artist_id
WHILE (@@sqlstatus = 0)
BEGIN
PRINT @name
PRINT @homebase
PRINT @style
PRINT char(@artist_id)
FETCH Artists_Cursor INTO @name, @homebase, @style, @artist_id
END
CLOSE Artists_Cursor
DEALLOCATE CURSOR Artists_Cursor
Los cursores no son objetos de la base de datos sino que residen en memoria.
Creación y uso de procedimientos almacenados
Los procedimientos almacenados funciones conteniendo agrupaciones de
sentencias SQL. Estas funciones son llamadas y ejecutadas como las funciones de
C o Visual Basic por ejemplo.
Estos procedimientos estan almacenados como parte de la B.D.
En un entorno cliente-servidor una de las grandes ventajas del uso de
procedimientos almacenados es que estos son ejecutados en el servido,
transfiriendose al cliente solo los datos resultado de la ejecución.
Ademas, son guardados en la base de datos ya compilados y optimizados, con lo
cual aumenta la velocidad de ejecución.
La implementacion difiere entre los diferentes gestores de B.D. y algunos no
lo soportan
Estos ejemplos estan basados en la implementación del SQL-Server (Transact-SQL).
Los procedimientos almacenados aceptan parametros de entrada y salida. En
este ejemplo se define un procedimiento "Match_Homebase_To_Name" con
un parametro de entrada "homebase" y otro de salida "name".
CREATE PROCEDURE Match_Homebase_To_Name @homebase char(40), @name char(30)
output
AS
SELECT @name = name FROM ARTISTS WHERE homebase = @homebase
Como puede esperarse para eliminar un procedimiento almancenado se debe usar
DROP
DROP PROCEDURE procedure_name
Para modificar un procedimiento almacenado primero hay que eliminar el
antiguo. El script SQL usado normalmente en SQL server para hacer esto es:
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name = "procedure_name")
BEGIN
DROP PROCEDURE procedure_name
END
GO
CREATE PROCEDURE procedure_name
AS
.
.
Los procedimientos almacenados puede ser anidados. Es decir que uno puede
llamar a otros y estos a su vez a otros. Pero hay que tener en cuenta que:
Las variables y objetos de la B.D. creados en un procedimiento almacenado
son visibles por todos los procedimientos almacenados a los que el llame.
Diseñando y usando Triggers
Un trigger es basicamente un tipo especial de procedimiento almacenado que
puede ejecutarse como respuesta a un update, un insert o un delete
La implementacion difiere entre los diferentes gestores de B.D. y algunos no
lo soportan
Estos ejemplos estan basados en la implementación del SQL-Server (Transact-SQL).
CREATE TRIGGER trigger trigger_name
ON table_name
FOR {insert, update, delete}
AS SQL_Statements;
Las acciones ejecutadas en un trigger se ejecutan implicitamente como parte
de una transaccion. La secuencia seria:
Se ejecuta un BEGIN TRANSACTION implicito
Tiene lugar el INSERT, DELETE o UPDATE
Se llama al trigger y se ejecutan sus sentencias
El trigger hace ROLL BACK de la transaccion, o se hace un COMMIT implicito
de la misma.
En este ejemplo se activa un trigger para los INSERT y UPDATE de la tabla
RECORDINGS en el cual se comprueba que no se puedan añadir o modificar
registros de la tabla para los cuales no exista el codigo (artist_id) del
artista en la tabla de artistas.
CREATE TRIGGER check_artists
ON RECORDINGS
FOR INSERT, UPDATE AS
IF NOT EXISTS (select * from ARTISTS, RECORDINGS where ARTISTS.artist_id = RECORDINGS.artist_id) BEGIN
PRINT "Illegal Artist_ID!" ROLLBACK TRANSACTION
END
Restricciones a tener en cuenta:
No se pueden crear triggers para tablas temporales
Los triggers deben crearse para tabla en la B.D. activa
No se pueden crear triggers para views
Cuando se elimina una tabla tambien se eliminan todos los triggers
asociados a ella.
Los triggers se pueden anidar (dependiendo de la configuracion del gestor de
la B.D.). Es decir que la accion tomada dentro de un trigger puede provocar la
activacion de otro trigger, y este la otro, etc.
SQL embedido
El termino SQL embedido se utiliza para referirse al uso de procedimientos
almacenados dentro dentro de un programa de aplicación.
SQL estático
Las sentencias SQL son includidas directamente en el código del programa.
Este codigo no puede ser modificado en tiempo de ejecución.
SQL dinámico
Permite construir la sentencia SQL en tiempo de ejecución y pasarla al
gestor de la B.D. para su ejecución.
Este ejemplo ilustra el uso del SQL estático en una funcion C.
BOOL Print_Employee_Info (void)
{
int Age = 0;
char Name[41] = "\0";
char Address[81] = "\0";
/* Now Bind Each Field We Will Select To a Program Variable */
#SQL BIND(AGE, Age)
#SQL BIND(NAME, Name);
#SQL BIND(ADDRESS, Address);
/* The above statements "bind" fields from the database to variables from the program.
After we query the database, we will scroll the records returned
and then print them to the screen */
#SQL SELECT AGE, NAME, ADDRESS FROM EMPLOYEES;
#SQL FIRST_RECORD
if (Age == NULL)
{
return FALSE;
}
while (Age != NULL)
{
printf("AGE = %d\n, Age);
printf("NAME = %s\n, Name);
printf("ADDRESS = %s\n", Address);
#SQL NEXT_RECORD
}
return TRUE;
}
(*1) No soportado por MS-JET
También te puede interesar el programa SQL2MDB,
para ejecutar archivos .SQL contra bases de datos Access
Página principal
Mapa del sitio
Buscador Radio Sherlock
Terminos de servicio
Privacidad
Cookies