Centro de Tecnología Informática
Introducción a
SQL y ORACLE
Centro de Tecnología Informática
Ignacio Coupeau, Cristina Pueyo y Jesús Used
21/9/95
Se puede hablar de propiedades características o campos característicos, y propiedades secundarias o campos secundarios según definan o complementen el elemento representado por el registro.
Por ejemplo, el registro empleados tiene los siguientes campos: DNI, Nombre, Apellidos, Edad, Población, Sueldo. Los campos DNI, Nombre y Apellidos son elementos o campos característicos. Los restantes son secundarios.
Un fichero o tabla es un conjunto de registros homogéneos con la misma estructura:
Cuando se tienen varias tablas o ficheros con algún campo en común, entonces pueden relacionarse y constituyen una base de datos relacional:
En el ejemplo anterior, puede extraerse la información relacionada en las dos tablas, por medio del campo común DNI; por ejemplo:
"Buscar en la tabla los puestos de trabajo desempeñados y la antigüedad del empleado con nombre = Luis"
Hasta hace un tiempo, y siempre que se deseara una base de datos especialmente diseñada, se utilizaban bases de datos con estructura jerárquica o de red, mediante anillos interconectados. La información afín está organizada en anillos (listas cíclicas), por ejemplo empresas, ciudades, trabajos... De cada anillo se pasa otro anillo de conceptos subordinados. Por ejemplo una base de datos de una empresa para su personal en varias ciudades puede ser:
Este tipo de bases son especialmente eficientes en búsquedas acordes con su estructura, por ejemplo: "[[questiondown]]qué ajustadores trabajan en Valencia"; pero ante otras preguntas como "[[questiondown]]Cuántas personas tiene contratadas mi empresa?" la búsqueda se hace bastante más difícil.
Además, las bases de datos en red y jerárquicas requieren un diseño específico ajustado a las consultas, por lo que no suele usarse ante las dificultades técnicas que plantea su desarrollo.
Un ejemplo de bases de datos jerárquica, aunque no basada en anillos múltiples, sería el de una base de datos construida según la organización jerárquica de las piezas que componen un vehículo:
El modelo relacional, basado en tablas, tiene en la actualidad una difusión mayor. Las búsquedas pueden ser mucho más flexibles, basadas en cualquier campo (DNI, Nombre, etc.). Para hacer búsquedas rápidas deben definirse campos índice. Los campos comunes por donde se conectan las tablas deben tener un índice definido.
Se conoce como gestor de bases de datos al programa de ordenador que sirve para definir, diseñar y utilizar los registros, ficheros y formularios de la base de datos. Generadores de bases de datos muy conocidos son ORACLE, SyBase, INFORMIX, FOX BASE, PARADOX, ACCESS...
El lenguaje de gestión de bases de datos más conocido en la actualidad es el SQL, Structured Query Language, que es un lenguaje estandar internacional, comúnmente aceptado por los fabricantes de generadores de bases de datos. En concreto, el gestor de bases de datos Oracle utiliza el lenguaje SQL.
El SQL trabaja con estructura cliente/servidor sobre una red de ordenadores. El ordenador cliente es el que inicia la consulta; el ordenador servidor es que atiende esa consulta. El cliente utiliza toda su capacidad de proceso para trabajar; se limita a solicitar datos al ordenador servidor, sin depender para nada más del exterior. Estas peticiones y las respuestas son transferencias de textos que cada ordenador cliente se encarga de sacar por pantalla, presentar en informes tabulados, imprimir, guardar, etc., dejando el servidor libre.
El SQL permite:
* Definir una base de datos mediante tablas
* Almacenar información en tablas.
* Seleccionar la información que sea necesaria de la base de datos.
* Realizar cambios en la información y estructura de los datos.
* Combinar y calcular datos para conseguir la información necesaria.
SQL es el lenguaje de comunicación entre el programa cliente y programa servidor; Oracle es un programa servidor, en el que está la base de datos propiamente dicha. El usuario accede con alguno de los programas cliente disponibles para consultar Oracle.
En este manual se explica como emplear SQL para:
* Crear y modificar la estructura de una tabla de datos.
* Seleccionar información de una tabla.
* Añadir datos a una tabla.
* Introducir información en una tabla.
* Realizar consultas entre tablas con campos comunes.
DEPT:
EMP:
CREATE TABLE DEPT (DEPTNO NUMBER(2),
DNAME CHAR(14),
LOC CHAR (13));
El comando crear tabla (CREATE TABLE) indica al servidor Oracle que nombre queremos poner a la tabla, los nombres de las columnas de la tabla (n[[ordmasculine]] de departamento, nombre del departamento y localidad) y el tipo de información que cada columna va a contener. La columna DEPTNO tendrá información numérica (2 dígitos), DNAME tendrá 14 caracteres y LOC tendrá 13 caracteres; de este modo especificamos la longitud máxima de cualquier dato que pueda ser almacenado en las columnas de la tabla.
Se procede de forma análoga con la tabla empleados EMP con el comando CREATE TABLE:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(9),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
En esta tabla se ha definido la columna n[[ordmasculine]] de empleado (EMPNO) como no nula, esto significa que cada campo de esa columna debe contener un valor. Esta especificación NOT NULL es un ejemplo de como ORACLE analiza los valores que se introducen en cada campo y comprueba que se cumple lo especificado.
Aunque el número máximo de caracteres definido para un campo sea -por ejemplo- 14, si sólo se ocupan 4, ORACLE solo utiliza cuatro en disco. Los valores nulos no ocupan espacio.
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO);
En este comando, primero se nombra la tabla de la base de datos en la que quieren insertarse los datos (DEPT), y a continuación la lista de valores que van a ir en cada columna (30, 'Ventas', 'Chicago')
Si se quiere ver el contenido de toda la tabla EMP, puede sustituirse toda la lista de columnas por un asterisco:
En los ejemplos, para facilitar la lectura, se escriben los comandos en diversas líneas, pero puede hacerse en una sola línea, por ejemplo:
SELECT * FROM DEPT; Headers(); PrintAll;
Para seleccionar determinados registros o filas de la base de datos, debe añadirse una condición con el comando WHERE al usar SELECT... FROM:
SELECT *
FROM EMP
WHERE DEPTNO=30;
Headers(); PrintAll;
obtiene todas las columnas pero de los empleados del Departamento número 30:
WHERE obliga a ORACLE a buscar en la información de la tabla y mostrar solo aquellas líneas o registros que cumple la condición.
SELECT ENAME, JOB, SAL FROM EMP
WHERE JOB = 'SALESMAN' AND SAL >=100;
En este caso se piden los datos del nombre, trabajo, salario de aquellos empleados de la tabla cuyo empleo sea 'SALESMAN'[1] y su salario mayor o igual que 100:
Para las condiciones alternativas, negativas (excluyentes) se utilizan los comandos OR, y NOT. Así por ejemplo:
pide los datos de empleados con categoría manager o que su salario sea mayor que 100:
El siguiente ejemplo pide un listado con los nombres, categorías y número de departamento de los empleados cuyo trabajo sea clerck (oficinista) y su departamento sea distinto[2] del 30,
y su resultado es el siguiente:
Mediante los comandos BEETWEEN y AND pueden pedirse datos comprendidos en un rango determinado. El ejemplo siguiente facilita los el nombre y el salario de los empleados cuyo salario esté comprendido entre 800 y 900:
SELECT ENAME, SAL
FROM EMP WHERE SAL BETWEEN 800 AND 900;
El comando IN permite seleccionar líneas cuyo campo contenga uno de los valores de una lista especificada entre paréntesis:
SELECT * FROM DEPT WHERE DEPTNO IN (10,30);
En este ejemplo, como la lista está compuesta por sólo 2 valores, podría haberse empleado OR para realizar la misma búsqueda:
SELECT * FROM DEPT WHERE DEPTNO='10' OR DEPTNO='30';
SELECT ENAME FROM EMP WHERE ENAME LIKE '__R%';
Headers();
PrintAll;
En este ejemplo se utiliza el operador LIKE de SQL. Además, mediante guiones se especifican las dos posiciones ('__R%'), y el signo % indica que puede seguir cualquier cadena de caracteres.
Los operadores BETWEEN, IN y LIKE, pueden ir precedidos por NOT y unidos con AND y OR, para formar una búsqueda tan completa como se necesite.
Como ejemplo suponga que desea obtener una lista de los empleados que trabajan en el departamento 30 pero ordenados por su salario. Esta ordenación no está limitada a un orden ascendente o a un único criterio, así por ejemplo, pueden ordenarse los empleados por puesto de trabajo, y dentro de esta ordenación ( los trabajos) por orden de salarios:
SELECT JOB,SAL,ENAME
FROM EMP
ORDER BY JOB,SAL DESC;
Headers();
PrintAll;
donde DESC indica orden descendente (de mayor a menor), y cuyo resultado es el siguiente:
SELECT JOB
FROM EMP;
Headers();
PrintAll;
al no haber ninguna condición en nuestra búsqueda, ORACLE trae a la pantalla todos los valores de la columna trabajos. En la lista obtenida hay repeticiones que pueden eliminarse especificando DISTINCT al escribir la búsqueda:
SELECT DISTINCT JOB FROM EMP;
Headers(); PrintAll;
En las bases de datos jerárquicas y en anillo, las relaciones son estáticas porque están perfectamente definidas en la estructura de la base de datos desde el diseño, por lo que las consultas deben seguir ese mismo esquema. En las bases de datos relacionales como ORACLE, las relaciones son dinámicas; se establecen en el momento de la consulta, y es posible extraer información según convenga en cada caso.
Por ejemplo, si se desea saber el nombre del departamento donde trabaja determinado empleado, y se intenta buscar en la tabla EMP (empleados), puede verse que no tiene columna con el nombre de departamento; sin embargo, la tabla de departamentos tiene el departamento (número y nombre). Como las dos tablas tienen una columna en común -el n[[ordmasculine]] de departamento-, es posible relacionar las dos tablas. Puede hacerse con dos búsquedas:
SELECT ENAME, DEPTNO
FROM EMP
WHERE ENAME = 'WARD';
SELECT LOC FROM DEPT WHERE DEPTNO = 30;
Pero puede llegarse al mismo resultado mediante una única búsqueda indicando la tabla y la columna separados por un punto. El ejemplo siguiente
SELECT ENAME,LOC FROM EMP,DEPT
WHERE ENAME='KING' AND EMP.DEPTNO =DEPT.DEPTNO;
Headers();
PrintAll;
busca los empleados en la tabla EMP cuyo nombre es KING y utiliza el valor del código del empleado localizado para buscar en la otra tabla (DEPT) el nombre del departamento de trabajo, y busca los registros donde coinciden los valores de las columnas EMPTO y DEPTO:
En el ejemplo anterior, en la tabla EMPL se busca la fila que contiene al empleado 'KING', se determina el número de departamento al que pertenece DEPTNO, y con el número de departamento, en la tabla DEPT se extrae el registro con el mismo valor de DEPTNO. La cláusula
EMP.DEPTNO =DEPT.DEPTNO
especifica que los registros de las tablas EMP y DEPT deben coincidir en el valor del campo o columna DEPTNO.
SELECT ENAME,SAL,COMM,SAL+COMM
FROM EMP
WHERE JOB='SALESMAN';
Headers();
PrintAll;
se obtiene un listado con una tercera columna que es la suma de salario y comisiones. La columna de la suma no es una columna real, es decir no está almacenada en nuestra base de datos, pero se construye dinámicamente como resultado de una búsqueda, y se puede operar con la columna resultado como si se tratara de una columna real.
Se adjuntan las principales funciones aritméticas:
Funciones aritméticas significado
+ suma
- resta
* producto
/ división
POWER exponenciación
ROUND redondeo
TRUNC trunca a entero
ABS valor absoluto
El ejemplo siguiente busca en la tabla EMP los registros cuyo campo ENAME suene parecido a 'SCHMIDT':
SELECT ENAME FROM EMP
WHERE SOUNDEX(ENAME)=SOUNDEX('SCHMIDT');
Headers(); PrintAll;
Se adjuntan las principales funciones de texto:
Funciones de texto significado
| concatena textos
LENGTH mide la longitud de un texto
SUBSTR corta un texto
INSTR inserta un texto dentro de otro
UPPER pone en mayúsculas
LOWER pone en minúsculas
SOUNDEX sonido de un texto
En el siguiente ejemplo se listan algunos datos de los empleados que trabajan en el Depto 30 y la fecha en formato DY DD MM YY (día de la semana, día, mes, año):
SELECT ENAME,JOB,
TO_CHAR(HIREDATE,'DY DD MON YYYY') HIREDATE
FROM EMP WHERE DEPTNO = 20;
Headers(); PrintAll;
ORACLE permite una amplia variedad de formatos:
Formato de fecha ejemplo
estándar 22-OCT-93
DAY MONTH DD, YYYY WEDNESDAY OCTOBER 17, 1993
Day DD Mon YYYY Wed 22 Oct 1993
DY "the" ddth "of" Month YYYY Wednesday the 22nd of October 1993
SYSDATE siempre nos da la fecha del día (fecha interna del ordenador). La expresión HIREDATE+ 4500 suma 4500 días a la fecha. La consulta muestra aquellos registros cuya fecha HIREDATE es de hace 4500 días.
ORACLE permite: calcular días, meses o años entre fechas; calcular la última fecha del mes; calcular la fecha del día siguiente.
SELECT DEPTNO,MAX(SAL)
FROM EMP GROUP BY DEPTNO;
Headers(); PrintAll;
En una búsqueda de grupos, cada línea en el resultado de la búsqueda, corresponde a un grupo de líneas de nuestra tabla, la columna que se pone a continuación de group by es aquella por la que queremos agrupar las líneas de la tabla. En el ejemplo anterior cada línea de la tabla EMP se incluye en uno de los tres grupos, uno para cada departamento, dependiendo de su valor en el campo DEPTO: todas las líneas de su mismo grupo tienen el mismo número de departamento.
Podemos combinar las funciones de grupo con las búsquedas relacionales. Además, hay tres funciones que pueden utilizarse con los grupos:
* SUM : Para sumar los valores de los campos, dentro de los grupos definidos por GROUP BY.
* COUNT: Para contar el número de líneas que entran en cada uno de esos grupos.
* AVG: Para saber la medida de los valores de campos específicos en cada grupo.
El siguiente ejemplo tiene como objetivo saber cuantos empleados están trabajando en cada categoría en cada departamento, cuantos secretarios hay en el departamento de ventas y, en esos grupos, cuál es la suma y media de los salarios:
SELECT DNAME,JOB,SUM(SAL),
COUNT(*),
AVG(SAL)
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DNAME,JOB;
Headers(); PrintAll;
Supongamos que interesa una búsqueda como la anterior, pero en la que sólo se necesita ver aquellos grupos que tengan al menos dos empleados:
SELECT ENAME,JOB,SUM(SAL),COUNT(*),AVG(SAL)
FROM EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME,JOB
HAVING COUNT(*)>=2
Headers(); PrintAll;
SELECT ENAME,JOB
FROM EMP
WHERE JOB =
(SELECT JOB
FROM EMP
WHERE ENAME = 'KING'));
ORACLE realiza las subbúsquedas antes, por que necesita el resultado de estas para las búsquedas.
Como ejemplo adicional puede buscarse el empleado que gana más que la media de todos los salarios de los empleados:
SELECT ENAME,SAL
FROM EMP
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP);
Headers(); PrintAll;
* UPDATE: Cambia valores almacenados en tablas.
* INSERT: Añade líneas a una tabla.
* DELETE: Borra líneas a una tabla.
UPDATE EMP
SET SAL=SAL+100
WHERE JOB='CLERCK';
Headers(); PrintAll;
un mensaje confirmará que la actualización ha tenido lugar.
Supongamos que en nuestra base de datos tenemos una tercera tabla llamada PROMOCIóN, con algunas de las columnas de la tabla DEPT y queremos introducir en ella la información de todos los vendedores que tengan de comisión una cifra superior al 25% de su salario, copiando la información desde la tabla EMP:
INSERT INTO PROMOCION (ENAME,JOB,SAL,COMM)
SELECT ENAME,JOB,SAL,COMM
FROM EMP
WHERE COMM > 0.25 * SAL;
El comando Insert utiliza una subbúsqueda.
DELETE FROM DEPT WHERE DEPTNO=40;
Cuando ORACLE ha realizado la orden aparece 1 record deleted. Puede pedirse un listado de todos los departamentos para comprobarlo:
SELECT * FROM DEPT;
Las condiciones para todos esos comandos demuestran como SQL emplea la misma sintaxis tanto para las búsquedas como para la manipulación de la información. En los comandos SELECT, UPDATE, INSERT y DELETE, la condición con WHERE es opcional.
* ALTER TABLE ADD: Añadir una columna a una tabla existente.
* ALTER TABLE MODIFY: Hacer una columna existente más larga.
Siguiendo con los ejemplos anteriores, se va a aumentar la base de datos modelo para poder asignar empleados a proyectos de igual forma que hay empleados asignados a departamentos. Se trata en definitiva de crear una nueva tabla y practicar los comandos ALTER TABLE en su diseño.
El cambio en la estructura de la base de datos exige varios pasos. Primero, con el comando CREATE, hay que añadir la tabla proyectos a la base de datos:
CREATE TABLE PROJ (PROJNO NUMBER(3) NOT NULL,
PNAME CHAR(5), BUDGET NUMBER(7,2));
En segundo lugar, con el comando INSERT, se introducen algunas líneas:
que son los proyectos en curso dentro de la empresa. Los datos introducidos en la nueva tabla, pueden examinarse con el comando SELECT actuando sobre la nueva tabla proyectos (PROJ):
SELECT * FROM PROJ;
Headers(); PrintAll;
Finalmente para que puedan hacerse búsquedas relacionales sobre las tablas PROY y EMP, debe añadirse una columna a EMP denominada número de proyecto (PROJNO), para que las tablas tengan alguna columna en común. Con este fin se utiliza el comando ALTER TABLE:
ALTER TABLE EMP ADD (PROJNO NUMBER(3));
A este comando se le indica el nombre de la tabla a modificar, la columna que se quiere añadir, y el tipo de información y su longitud máxima. Ahora puede seleccionarse la tabla EMP y ver que en cada línea hay un campo más.
SELECT * FROM EMP;
Headers(); PrintAll;
Como ejemplo del comando UPDATE, van a asignarse todos los empleados del Depto 20 y todos los vendedores al proyecto 101:
UPDATE EMP SET PROJNO=101 WHERE DEPTNO =20 OR JOB='SALESMAN'
9 records updated.
Puede consultarse la tabla para confirmar la actualización:
Para completar el ejemplo, se asignan el resto de empleados -los que no tienen proyecto asociado- al proyecto 102:
UPDATE EMP
SET PROJNO=102
WHERE PROJNO IS NULL;
5 records updated
La actualización de las líneas con el número de proyecto, completa la modificación de la base de datos ejemplo y permite relacionar los empleados con los proyectos como antes se hacía con los departamentos.
Es posible, ahora que las tablas tienen un campo común, el número de proyecto, hacer búsquedas relacionales:
SELECT ENAME,JOB,DEPTNO,PNAME
FROM EMP,PROJ WHERE EMP.PROJNO = PROJ.PROJNO;
Headers(); PrintAll;
CREATE TABLE DEPT (DEPTNO NUMBER(2),
DNAME CHAR(14),
LOC CHAR (13));
Pero puede plantearse en un momento dado, cambiar el ancho o capacidad de una columna. En el ejemplo siguiente, al intentar actualizar una columna con el valor 105000,
el SQL notifica un error debido a que esa columna sólo tiene capacidad para 7 dígitos incluyendo la coma y los dos decimales (7,2). Se necesita modificar la definición de la columna budget, manteniendo el tipo de información que contiene pero haciéndola más ancha:
ALTER TABLE PROJ MODIFY BUDGET NUMBER(8,2);
y aparece: 1 record updated.
Una vez modificada la capacidad, la orden
se procesa correctamente.
Las presentaciones o vistas (views) son tablas virtuales, como ventanas, a través de las cuales, vemos información almacenada en la base de datos. Los views no contienen información propia, pero puede operarse con ellas como si fueran tablas reales.
La utilización de views tiene tres ventajas:
* Simplifica el acceso a la información.
* Independencia de la información.
* Privacidad de la información.
Como ejemplo va a crearse un view como un subconjunto de la tabla completa, que nos muestre solamente algunas columnas de algunos empleados (los del Dpto n[[ordmasculine]] 10).
CREATE VIEW asigna un nombre al view y define su contenido en forma de una búsqueda.
Si intentamos introducir un empleado del dpto 20:
da un error que indica que ese dato es incompatible con las condiciones de construcción del view.