Ediciones ENI Editorial | líder en informática
Profundizando
Las subconsultas
Es posible insertar los resultados de una consulta en otra. Esto se puede encontrar después de la cláusula WHERE o bien sustituir una constante detrás de una cláusula IN o EXISTS, por ejemplo.
Hay dos tipos de subconsultas: anidadas o correlacionadas.
1. Las subconsultas anidadas
En función de lo que puede devolver la sub-SELECT, esta no se podrá situar en cualquier sitio.
Si el resultado de la consulta situada en una sub-SELECT solo recupera una fila, se podrá utilizar la subconsulta en lugar de cualquier constante.
Por ejemplo, si queremos recuperar todas las habitaciones que tienen una cama individual con ducha, es necesario hacer una sub-SELECT que recupere el identificador de la tabla TIPOSHABITACION que se corresponde con la descripción 1 cama individual con ducha, y después comprobar que la columna TipoHabitacion de la tabla HABITACIONES se corresponde con el valor de la sub-SELECT.
Antes de probar la consulta completa, es preferible probar la subconsulta para comprobar su validez y que devuelve una única fila.
Ejemplo
SELECT idtipohabitacion FROM tiposhabitacion WHERE
descripcion = '1 cama individual con ducha'; muestra:
IDTIPOHABITACION
----------
1 A continuación, se puede incluir la subconsulta en la consulta principal de la siguiente manera:
SELECT hoteles.etiqueta, numhabitacion FROM Habitaciones
INNER JOIN hoteles ON hoteles.idhotel =habitaciones.hotel
where tipohabitacion = (SELECT idtipohabitacion FROM tiposhabitacion
where descripcion = '1 cama individual con ducha'); Resultado
ETIQUETA ...Las importaciones y exportaciones de datos
Según las bases de datos, hay herramientas de importación y exportación de datos, como SQL Loader para Oracle, que se describe a continuación. En SQL Server, es posible utilizar la herramienta bcp o la herramienta de importación/exportación a partir de SQL Server Management Studio. No vamos a describir estas herramientas; la última se utiliza de manera muy intuitiva.
La herramienta más adecuada para industrializar la importación o exportación de datos es un ETL (Extract Transform and Load) como SSIS del editor de Microsoft, incluido en la licencia de SQL Server (excepto la versión Express), Talend en código abierto, Oracle Data Integrator, Pentaho, Stambia, etc.
Desde el cliente Oracle SQL Developer, todo lo que tiene que hacer es hacer clic derecho en una tabla y elegir Exportar ... o Copiar a Oracle y seguir las instrucciones
1. Carga de datos masiva con SQL*Loader
Después de haber creado los esqueletos de las diferentes tablas, ahora hay que alimentarlas.
Cuando existe un histórico, puede ser interesante cargar rápidamente y de forma masiva todo este histórico.
Antes de nada, habrá que poner este histórico en el formato esperado para que la carga funcione con la herramienta elegida.
Retomemos por ejemplo la tabla PELICULAS que llenamos con múltiples INSERT en el capítulo La manipulación de los datos (LMD) - Ejercicios de aplicación.
TABLA PELICULAS
Consulta de creación de la tabla (sintaxis estándar):
CREATE TABLE PELICULAS (IDENT_PELICULAS INTEGER,
TITULO VARCHAR(50),
GENERO1 VARCHAR(20),
GENERO2 VARCHAR(20),
FECHA_ESTRENO DATE,
PAIS SMALLINT,
IDENT_DIRECTOR...Algunos conceptos de rendimiento
En la utilización de una base de datos, a menudo se encuentran problemas de tiempos de respuesta grandes en una consulta u otra.
Las razones son múltiples, se puede tratar de una consulta que no utiliza ningún índice, de una tabla muy grande, de uniones múltiples, de problemas de acceso a disco o de capacidad de memoria, etc.
Lo que se denomina el « tuning » de una base de datos es muy complejo y necesita mucha experiencia y conocimientos de bases de datos y sistemas operativos.
Las reglas básicas cuando se escribe una consulta son:
-
Comprobar que los criterios de búsqueda (WHERE) utilizan los índices.
-
Comprobar que las uniones entre tablas se hacen sobre las claves de las tablas y que los índices son correctos en estas tablas.
-
Comprobar que la selección no devuelve millones de filas.
-
Comprobar que las estadísticas de la base de datos están activadas y actualizadas regularmente (sobre todo en Oracle).
-
No utilizar muchas funciones en un mismo SELECT.
Las estadísticas son datos que sirven a la base de datos para saber qué camino es el más adecuado para obtener un dato.
1. Utilización de EXPLAIN PLAN
Existe un medio para saber el camino que utiliza el SGBDR para acceder a un elemento. Hay que utilizar el comando EXPLAIN PLAN que analiza el comando e indica el camino elegido. Para ello, se almacenan los elementos en una tabla: PLAN_table en Oracle.
La sintaxis a utilizar es esta:
EXPLAIN PLAN SET STATEMENT_ID='<identificador>' INTO PLAN_TABLE FOR
SELECT ... ... ; Se indica al SGBDR que almacene en una tabla llamada «PLAN_TABLE» bajo el identificador elegido (STATEMENT_ID) los análisis realizados sobre la consulta que se indica después del SELECT.
Ejemplo con un SELECT sobre tres tablas, el identificador es ’TEST-REND’
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID='TEST-REND';
EXPLAIN PLAN SET STATEMENT_ID='TEST-REND' INTO PLAN_TABLE FOR
SELECT Hoteles.nombre
, Habitaciones.NumHabitacion
, TipoHabitacion.TipoCama
, TipoHabitacion.NumeroCama
, TipoHabitacion.Descripcion
FROM Habitaciones, TipoHabitacion, HOTELES
WHERE Habitaciones.TipoHabitacion = TipoHabitacion.idTipoHabitacion
AND hoteles.idhotel = habitaciones.Hotel
AND numerocama in (1, 3); Se comienza eliminando de la tabla las filas...
Las tablas del sistema
Los SGDBR utilizan para sus necesidades un conjunto de tablas para almacenar todos los elementos creados por un usuario. Todos los objetos se almacenan en las llamadas tablas del sistema.
Podemos acceder a ellas simplemente con el comando:
SELECT * FROM <Nombre tabla>; 1. Tablas del sistema para tablas y columnas
a. Oracle
|
Tabla |
Contenido |
|
ALL_COL_COMMENTS |
Lista todos los comentarios de las columnas de todas las tablas. |
|
ALL_TABLES |
Lista todas las tablas. |
|
ALL_TAB_COLUMNS |
Lista todas las columnas de todas las tablas. |
b. MySQL
|
Tabla |
Contenido |
|
INFORMATION_SCHEMA.TABLES |
Lista todas las tablas. |
|
INFORMATION_SCHEMA.COLUMNS |
Lista todas las columnas de todas las tablas. |
c. SQL Server
SQL Server almacena estas tablas en una base de datos de sistema llamada master.
|
Tabla |
Contenido |
|
sys.tables |
Lista todas las tablas. |
|
Sys.all_columns |
Lista todas las columnas de las tablas. |
2. Tablas del sistema para índices y vistas
a. Oracle
|
Tabla |
Contenido |
|
ALL_INDEXES |
Lista todos los índices. |
|
ALL_IND_COLUMNS |
Lista todas las columnas de todos los índices. |
|
ALL_VIEWS |
Lista todas las vistas. |
b. MySQL
|
Tabla |
Contenido |
|
INFORMATION_SCHEMA. STATISTICS |
Lista toda la información sobre los índices. |
|
INFORMATION_SCHEMA. VIEWS |
Lista todas las vistas. |
c. SQL Server
|
Tabla |
Contenido |
|
Sys.indexes |
Lista toda la información de los índices. |
|
Sys.views |
Lista todas las vistas de usuario. |
|
Sys.all_views |
Lista todas las vistas. |
3. El resto de tablas del sistema
a. Oracle
|
Tabla |
Contenido |
|
ALL_CATALOG |
Lista todas las tablas, vistas, secuencias y sinónimos. |
|
ALL_CONSTRAINTS |
Lista las restricciones. |
|
ALL_OBJECTS |
Lista todos los objetos a los que puede acceder el usuario. |
|
ALL_SEQUENCES |
Lista las secuencias. |
|
ALL_SYNONYMS |
Lista los sinónimos. |
|
ALL_TRIGGERS |
Lista todos los triggers. |
|
ALL_TRIGGERS_COLS |
Lista todas las columnas de los triggers. |
|
ALL_USERS |
Lista los usuarios declarados. |
b. MySQL
|
Tabla |
Contenido |
|
INFORMATION_ SCHEMA.SCHEMATA |
Lista todas las tablas, vistas, secuencias y sinónimos. |
|
INFORMATION_ SCHEMA.CONSTRAINTS |
Lista todas las restricciones. |
|
INFORMATION_SCHEMA. COLUMN_PRIVILEGES |
Lista todos los objetos a los que puede acceder el usuario. |
|
INFORMATION_SCHEMA.USER_ PRIVILEGES |
Lista los usuarios declarados. |
|
INFORMATION_SCHEMA. COLUMN_PRIVILEGES |
Lista los privilegios sobre las columnas. |
|
INFORMATION_SCHEMA.TABLE_ PRIVILEGES |
Lista los privilegios sobre las tablas. |
|
INFORMATION_ SCHEMA.ROUTINES |
Lista las funciones... |
Los metadatos, funciones y procedimientos de sistema de SQL Server
Procedimientos de sistema de descripción completa:
-
exec sp_helpdb
-
exec sp_help ’Hoteles’
-
exec sp_helpdb ’RESAHOTEL’
-
exec sp_linkedservers
Funciones de sistema:
-
select DB_NAME()
-
select db_id()
-
select db_name(2)
-
select DB_ID(’RESAHOTEL’)
-
select SUSER_NAME()
-
select getdate()
-
select SYSDATETIME()
-
select HOST_NAME() --máquina
Variables de sistema:
-
select @@SERVERNAME --instancia
-
select @@VERSION
Algunos scripts útiles
1. Saber el tamaño real de una columna
En una columna declarada como VARCHAR, puede ser interesante saber el tamaño real de cada valor.
Esta consulta permite además ordenar el resultado.
Sintaxis:
SELECT <nombre de columna>, LENGTH (RTRIM(<nombre de columna>))
FROM <nombre tabla> WHERE ..
ORDER BY LENGTH (TRIM(<nombre de columna>)),<nombre de columna> Ejemplo Oracle:
SELECT LENGTH(TRIM(descripcion)) as longituddesc, descripcion
FROM tiposhabitacion
ORDER BY longituddesc; Exemple SQL Server
SELECT LEN(TRIM(descripcion)) as longituddesc, descripcion
FROM tiposhabitacion
ORDER BY longituddesc; Resultado
LONGITUDDESC DESCRIPCION
------------ --------------------------------------------------
24 1 cama individual con ducha
24 1 cama doble con ducha
25 2 camas doble con ducha
26 2 camas individuales con ducha
34 1 cama XL y 1 cama individual con baño
35 1 cama doble con baño y WC separados
36 2 camas doble con baño y WC separados
37 1 cama doble con ducha y WC separados
38 2 camas doble con ducha y WC separados 2. Buscar y eliminar duplicados en una tabla
A menudo nos encontramos con filas duplicadas en una tabla después de una mala manipulación o de un error en la aplicación que no controle los duplicados.
Si retomamos la tabla TIPOSHABITACION y añadimos la fila n° 13 con 1 cama individual con ducha que existe ya en la fila 1.
INSERT INTO tiposhabitacion VALUES (13, 1, 'cama individual' ,'1
cama individual con ducha'); Contenido de la tabla TIPOSHABITACION
IDTIPOHABITACION NUMEROCAMA TIPOCAMA DESCRIPCION
---------------- ---------- -------------------- --------------------------
1 1 cama individual 1 cama individual con ducha ...Ejercicios
Primer ejercicio
Crear una consulta que recoja todas las películas que tienen en su casting un actor francés.
Segundo ejercicio
Mostrar los actores que tienen el mismo apellido que otro actor.
Soluciones a los ejercicios
Primer ejercicio
SELECT SUBSTR(PELICULA.TITULO,1,20) TITULO, PELICULA.FECHA_ESTRENO,
SUBSTR((REAL.NOMBRE||' '||REAL.APELLIDO),1,25) DIRECTOR,
SUBSTR(RTRIM(ACTOR.NOMBRE||' '||ACTOR.APELLIDO),1,25) ACTOR,
ACTOR.FECHA_NACIMIENTO NA,ACTOR.NUM_PELICULA NUMPELICULAS,
ESTAD.PRESUPUESTO,ESTAD.NUM_ENTRADA_FRANCIA NUMENTRADAS
FROM PELICULA PELICULA, DIRECTOR REAL, CASTING CAST,
ACTOR ACTOR, ESTADISTICA ESTAD
WHERE
PELICULA.IDENT_DIRECTOR = REAL.IDENT_DIRECTOR AND
PELICULA.IDENT_PELICULA = CAST.IDENT_PELICULA AND
PELICULA.IDENT_PELICULA = ESTAD.IDENT_PELICULA AND
CAST.IDENT_ACTOR = ACTOR.IDENT_ACTOR AND
ACTOR.NACIONALIDAD = (SELECT PAIS.IDENT_PAIS FROM PAIS WHERE
PAIS.ETIQUETA = 'FRANCIA')
ORDER BY PELICULA.TITULO, ACTOR.NOMBRE;; Segundo ejercicio
SELECT SUBSTR(PELICULA.TITULO,1,20) TITULO, PELICULA.FECHA_ESTRENO,
SUBSTR((REAL.NOMBRE||'...