SQ L Óscar Javier Segura Amorós
[email protected]
Cedida su publicación a el Rinconcito de Delphi No está autorizada su reproducción sin permiso expreso de su autor
SQL. El lenguaje universal de las Bases de Datos. Óscar Javier Segura Amorós -
[email protected]
Vamos a conocer SQL, un lenguaje de definición y manipulación utilizado por todas las grandes Bases de Datos. SQL (Standard Query Language, Language, Leng Lengua uaje je de consultas consultas estándar o estructurado), es un len lengu guaj ajee que que nos nos permitirá operar con nuestras bases de datos de una forma sencilla y potente. Se encuentra muy difundido y es soportado soportado por todos todos los los grandes sistemas sistemas gestores de bases de datos datos como como pueden ser: Oracle, Interbase, Microsoft, Microsoft, Infor Informi mix, x, Sybase, Sybase, etc. La gran ven venta taja ja que que nos ofrece SQL frente a otras formas de trabajar es su su estandarización, no es necesario que cuando nos encontramos ante diferentes sistemas gestores tengamos que aprender como funciona cada uno de ellos de una forma más o menos detallada, simplemente debemos conocer como se trabaja con SQL, ya que su sintaxis y estructuración sufre muy pocas variaciones de un sistema otro. otro. Por lo lo tanto SQL nos ofrece ofr ece una única forma de trabajar con independe independencia ncia del sistema con el que tengamos que hacerlo, con el consiguiente ahorro de tiempo y energías por parte de programador. Para comenz comenzar ar a entender entender las grandes grandes virt virtud udes es que tien tienee SQL y las interesan interesantes tes facil facilid idad ades es y posibilidades que nos brinda, vamos a imaginar la siguiente situación. Imaginemos que hemos diseñado una base de datos para un grupo de clientes. El diseño de ésta, lógicamente, debe de ser independiente del sistema gestor gestor en el que vaya a funcionar, pero qué ocurre cuando llegamo llegamoss al a l client clientee con el diseño diseño hecho hecho y tenemos que montarlo. Si tenemos la suerte de conocer el sistema gestor que el cliente utiliza, no tenemos ningún problema, pero qué pasa cuando no lo conocemos, perdemos mucho tiempo. Además, si la misma base de datos la vamos vamos a mon montar tar para diferentes diferentes clientes, clientes, lo lo normal es que que nos nos encontre encontremos mos con diferentes diferentes gestores. Pues este inconveniente, que en principio para el programador puede ser un problema, SQL nos lo resuelve. Si conocemos SQL, podremos llegar al cliente con los scrips de SQL en los que tenemos ya escritos todas las sentencias necesarias para: crear la base de datos, crear todas las tablas y relaciones, todos los tipos de datos de las tablas, todas las reglas de validación y las consultas para informes, solamente nos restará ejecutar esos scrips en el servidor y trabajo finalizado. ¿Qué hemos conseguido? Hemos conseguido rapidez, eficiencia, que el cliente quede satisfecho y nos hemos ahorrado interminables momentos de problemas porqu porquee el sistema sistema gestor gestor que que util utiliz izaa el cliente cliente no termi terminam namos os de conoce conocerlo rlo mu muyy bien bien.. Qué mara maravi vill lloso oso,, ¿verdad?. Bienvenidos a SQL. Un poco de historia. historia. SQL comenzó comenzó a ver la luz sobre el e l final de la la década de los los 70, 70, implem implementa entado do como como un proto prototi tipo po por por IBM y en 1979 aparece el primer primer sistema sistema gestor gestor de bases bas es de datos que que lo soporta, Oracle. Poco tiempo después, otros sistemas como DB2, Interbase, etc, también quisieron adoptar al nuevo recién nacido. Ya en 1987, 1992, 1995, 1996 se le confirió la estandarización a través de las normas ISO, hasta la actual actualida idad, d, en la la que que todo todo sistema sistema SQL se basa en el e l estándar SQL-3 (ISO-99 (ISO-99)) en el e l que que se le han añadid añadidoo nuevas características como la creación de TADs (tipos abstractos de datos), reglas activas (triggers disparadores), cursores sensitivos, operador de unión recursiva, etc. Aunque Aunque todo el SQL está estandarizado, sí es cierto que que encontramos pequeñas diferencias en la la sintaxis sintaxis de las sentencias en los diferentes sistemas gestores. Estas diferencias repito que son mínimas y una vez realizados los scrips de SQL que, por ejemplo, diseñan una base de datos, tendremos que hacer muy pocas variacio variaciones nes si queremos queremos que que esos scrips scrips se ejecuten en diferentes sistemas gestores Empl Emplaz azoo al a l lecto lectorr para siguientes artículos en los que abordaré las diferencias más notables entre los principales sistemas gestores de bases de datos datos SQL, en los los que trataré tratar é de orientarlo orientarlo sobre la la variaciones sintácticas sintácticas con las que que nos vamos a encontra encontrar. r. Insist Insistoo de nuev nuevoo en que que esas difere diferenci ncias as son son míni mínima mas, s, tan mínim mínimas as que que no llega llegann a ser un inconveniente para la utilización de SQL.
SQL. El lenguaje universal de las Bases de Datos. Óscar Javier Segura Amorós -
[email protected]
Vamos a conocer SQL, un lenguaje de definición y manipulación utilizado por todas las grandes Bases de Datos. SQL (Standard Query Language, Language, Leng Lengua uaje je de consultas consultas estándar o estructurado), es un len lengu guaj ajee que que nos nos permitirá operar con nuestras bases de datos de una forma sencilla y potente. Se encuentra muy difundido y es soportado soportado por todos todos los los grandes sistemas sistemas gestores de bases de datos datos como como pueden ser: Oracle, Interbase, Microsoft, Microsoft, Infor Informi mix, x, Sybase, Sybase, etc. La gran ven venta taja ja que que nos ofrece SQL frente a otras formas de trabajar es su su estandarización, no es necesario que cuando nos encontramos ante diferentes sistemas gestores tengamos que aprender como funciona cada uno de ellos de una forma más o menos detallada, simplemente debemos conocer como se trabaja con SQL, ya que su sintaxis y estructuración sufre muy pocas variaciones de un sistema otro. otro. Por lo lo tanto SQL nos ofrece ofr ece una única forma de trabajar con independe independencia ncia del sistema con el que tengamos que hacerlo, con el consiguiente ahorro de tiempo y energías por parte de programador. Para comenz comenzar ar a entender entender las grandes grandes virt virtud udes es que tien tienee SQL y las interesan interesantes tes facil facilid idad ades es y posibilidades que nos brinda, vamos a imaginar la siguiente situación. Imaginemos que hemos diseñado una base de datos para un grupo de clientes. El diseño de ésta, lógicamente, debe de ser independiente del sistema gestor gestor en el que vaya a funcionar, pero qué ocurre cuando llegamo llegamoss al a l client clientee con el diseño diseño hecho hecho y tenemos que montarlo. Si tenemos la suerte de conocer el sistema gestor que el cliente utiliza, no tenemos ningún problema, pero qué pasa cuando no lo conocemos, perdemos mucho tiempo. Además, si la misma base de datos la vamos vamos a mon montar tar para diferentes diferentes clientes, clientes, lo lo normal es que que nos nos encontre encontremos mos con diferentes diferentes gestores. Pues este inconveniente, que en principio para el programador puede ser un problema, SQL nos lo resuelve. Si conocemos SQL, podremos llegar al cliente con los scrips de SQL en los que tenemos ya escritos todas las sentencias necesarias para: crear la base de datos, crear todas las tablas y relaciones, todos los tipos de datos de las tablas, todas las reglas de validación y las consultas para informes, solamente nos restará ejecutar esos scrips en el servidor y trabajo finalizado. ¿Qué hemos conseguido? Hemos conseguido rapidez, eficiencia, que el cliente quede satisfecho y nos hemos ahorrado interminables momentos de problemas porqu porquee el sistema sistema gestor gestor que que util utiliz izaa el cliente cliente no termi terminam namos os de conoce conocerlo rlo mu muyy bien bien.. Qué mara maravi vill lloso oso,, ¿verdad?. Bienvenidos a SQL. Un poco de historia. historia. SQL comenzó comenzó a ver la luz sobre el e l final de la la década de los los 70, 70, implem implementa entado do como como un proto prototi tipo po por por IBM y en 1979 aparece el primer primer sistema sistema gestor gestor de bases bas es de datos que que lo soporta, Oracle. Poco tiempo después, otros sistemas como DB2, Interbase, etc, también quisieron adoptar al nuevo recién nacido. Ya en 1987, 1992, 1995, 1996 se le confirió la estandarización a través de las normas ISO, hasta la actual actualida idad, d, en la la que que todo todo sistema sistema SQL se basa en el e l estándar SQL-3 (ISO-99 (ISO-99)) en el e l que que se le han añadid añadidoo nuevas características como la creación de TADs (tipos abstractos de datos), reglas activas (triggers disparadores), cursores sensitivos, operador de unión recursiva, etc. Aunque Aunque todo el SQL está estandarizado, sí es cierto que que encontramos pequeñas diferencias en la la sintaxis sintaxis de las sentencias en los diferentes sistemas gestores. Estas diferencias repito que son mínimas y una vez realizados los scrips de SQL que, por ejemplo, diseñan una base de datos, tendremos que hacer muy pocas variacio variaciones nes si queremos queremos que que esos scrips scrips se ejecuten en diferentes sistemas gestores Empl Emplaz azoo al a l lecto lectorr para siguientes artículos en los que abordaré las diferencias más notables entre los principales sistemas gestores de bases de datos datos SQL, en los los que trataré tratar é de orientarlo orientarlo sobre la la variaciones sintácticas sintácticas con las que que nos vamos a encontra encontrar. r. Insist Insistoo de nuev nuevoo en que que esas difere diferenci ncias as son son míni mínima mas, s, tan mínim mínimas as que que no llega llegann a ser un inconveniente para la utilización de SQL.
Funcionamiento Funcionamiento de las bases de datos SQL Siempre que realicemos un aplicación que vaya a trabajar sobre una base de datos, ésta la situaremos dentro de un servid servidor or de base de datos. El servidor servidor puede ser una máquin máquinaa dedicada, cuya única única función función sea la de atender petici peticione oness de sus clientes, clientes, devol devolvié viéndo ndoles les la inform informaci ación ón y ejecutan ejecutando do los los proced procedimi imient entos os ordenados ordenados por estos sobre la base de datos. O también el servidor servidor puede ser la misma misma máqu máquina ina sobre la la que que corra la la prop propia ia aplicaci aplicación. ón. Dependerá Depen derá ya de la estructura e import importanc ancia ia del sistema informático informático sobre el e l que que vaya a funcionar funcionar nuestra aplicació aplicación. n. Sea cual cu al sea la fórmul fórmulaa que que vayamos a utiliz utilizar, ar, la forma de trabajar será ser á la misma, misma, la la única única difere diferenci nciaa será si trabajam trabajamos os con un solo solo ordenador, ordenador, que hará simult simultáne áneame amente nte las funciones funciones de servidor servidor y cliente, o con una una red re d de de ordenadores en la la que los clientes y el servidor, en princ principi ipio, o, quedan definidos de una forma más clara. Una vez que conocemos como se estructura una base de datos SQL vamos a ver, a grandes rasgos, cómo funciona, cómo se realiza la comunicación entre el servidor y el cliente. Vamos a verlo muy por encima, sin entrar en detalles, simplemente para entender la forma de trabajar. El cliente tendrá instalado un cliente SQL y el servidor tendrá instalado un servidor de SQL. La base de datos estará instalada en el servidor y será únicamente el servidor de SQL el que acceda a ésta. Cuando nuestra aplicac aplicación ión realice realice una una llam llamad adaa a una una senten sentencia cia SQL no será ella lla quien quien la la ejecute. Esa senten sentencia cia llam llamar aráá al clie client ntee de SQL SQL que tene tenemo moss inst instal alad adoo en nuestr nuestraa máqu áquina ina y será él quie quienn se pong pongaa en comunicación con el servidor de SQL. Una vez que el servidor SQL ha recibido la petición del cliente la ejecutará sobre la la base de datos, y el resultado resultado de la ejecuci ejecución ón será devuel devuelta ta al client clientee que se la pasará a la aplicación. Es el funcionamiento clásico de una distribución cliente-servidor. No es el momento de ensalzar las virtudes virtudes que que tiene esta forma de trabajar, simplem simplemente ente comentaré que tiene tiene el inconven inconvenient ientee de la rapidez, es lógico que el tiempo tiempo que que se necesita para realizar todo el proceso proces o desde desde que la la aplicación aplicación realiza realiza la la llamada llamada al cliente cliente con la sentencia sentencia SQL hasta que éste le devuelve devuelve los datos, es mayor mayor que si la la aplicació aplicaciónn trabajara trabajar a directamente con la base de datos, de todas formas tampoco quiero dar la impresión de que el retardo de tiempo es importante, porque es mínimo. Pero en contraposición encontramos muchas ventajas, sobre todo cuando van a ser varios los usuarios que utilizando simultáneamente nuestra aplicación trabajen con la base de datos, ventajas como: la integridad de los datos, la recuperación ante fallos, las inevitables colisiones, las transacciones, transacciones, etc.
Las instrucciones de SQL Las instrucciones instrucciones de de SQL las podemos podemos divid dividir ir en e n dos dos grandes gr andes grupos: las instrucciones que trabajan con la estructura de los datos, Lenguaje de definición de datos (LDD), y las instrucciones que trabajan con los datos en sí, Lenguaje de manipulación de datos (LMD). El lenguaje de definición de datos nos permitirá:crear nuestra base de datos, los tipos de datos definidos por por el e l usuario, usuario, las tablas, las reglas de validac validación ión de de los datos y las relaciones. El lengu lenguaje aje de manipulac manipulación ión de datos nos permitirá: introduci introducir, r, modificar modificar y borrar datos, da tos, realizar consultas y definir definir nuestros procedim procedimient ientos os y triggers o disparadores. Como podem podemos os apreciar, apreciar , tenemos tenemos aun mucho trabajo trabajo por delante, pero prometo que va a valer la pena el esfuerzo. Comenza Comenzaremo remoss conoci conociend endoo las instrucc instruccione ioness de defi defini nici ción ón de datos datos y contin continuar uaremo emoss vien viendo do las de manipulación. Para hacer más fácil el aprendizaje vamos a trabajar sobre una hipotética base de datos. Una base de datos sencilla que nos va a permitir reflejar las explicaciones que se realicen sobre SQL en un ejemplo práctico. Este ejemplo lo iremos intercalando con otros pequeños ejemplos no relacionados con él, que sirvan para completar el lado práctico del aprendizaje. A lo largo del presente artículo conoceremos las instrucciones de definición de datos, dejando para el próxim próximoo las instrucciones instrucciones de manipula manipulació ciónn de esos datos. De D e todas formas, al a l final de esta entrega daremos una pequeña pincelada a las instrucciones de manipulación para que el lector vaya introduciéndose un poco en ellas y nos sirva de cierto avance o introducción para la parte de SQL, que de momento, nos vamos a quedar sin conocer.
Diseño de una base de datos Aunque no es estrictamente el tema de este artículo voy a nombrar brevemente los pasos que debemos seguir cuando vamos a diseñar una base de datos. De la misma forma que ya he hecho anteriormente, vuelvo a emplazar al lector para siguientes artículos donde trataremos este tema con mucha más profundidad. Cuando nos enfrentamos al diseño de una base de datos muchos programadores se lanzan directamente al interfaz del sistema gestor a comenzar a introducir tablas, atributos y relaciones. Gran error, aunque nos encontremos en la era de las nuevas tecnologías debemos todavía comenzar nuestro trabajo con unos folios en blanco y un lápiz, mejor gastar un poco de tiempo al principio que desesperarnos cuando estamos terminando el trabajo porque nos hemos dado cuenta que tenemos errores de diseño, normalmente estos errores son muy difíciles de solucionar, cuando al no detectarlos previamente hemos basado todo nuestro diseño y trabajo sobre ellos. Cuando tenemos que realizar el diseño de una base de datos comenzaremos por reunirnos con el cliente y que nos especifique con todo detalle como funciona el sistema que tenemos que informatizar. Esa reunión la tendremos que repetir cuantas veces sea necesaria hasta que conozcamos perfectamente la problemática que tenemos que resolver. Una vez conozcamos perfectamente el sistema comenzaremos a diseñarnos sobre papel el esquema lógico de nuestra base de datos. Existen muchas modelados para representar la realidad que queremos informatizar, aunque el más conocido y extendido es el Entidad Relación Extendido (EER). No se trata mas que de una forma de representar nuestra base de datos a través de un dibujo, de forma que podemos identificar rápidamente todos los componentes de la misma. En la “Figura 1” podemos ver el EER de nuestra base de datos.
Figura 1. Modelo lógico, esquema ERR, de nuestra base de datos de ejemplo, Facturación. Una vez tenemos diseñada nuestra base de datos, utilizando por ejemplo el modelo EER, obtendremos de él las tablas, campos de cada una de ellas y relaciones. Al obtener los elementos de la base de datos del esquema lógico nos aseguramos una metodología, un formalismo y evitamos los errores de diseño típicos que siempre aparecen cuando estos se realizan de forma intuitiva. Una vez que ya tenemos las tablas, sus campos y relaciones, ya podemos con SQL generar las instrucciones de los scrips de creación y manipulación de la base de datos.
Base de datos Facturación Del EER de nuestra base de datos Facturación obtenemos las siguientes tablas, campos y relaciones. Tabla Clientes: •
•
•
•
•
•
DNI
Caracter(9) (Clave Primaria)
Nombre
CaracterVariable(50)
Direccion
CaracterVariable(60)
Ciudad
CaracterVariable(30)
CodigoPostal
Caracter(5)
Telefono
Caracter(9)
Numero
Entero (Clave Primaria)
Fecha
Fecha
Total
Flotante
DNI
Caracter(9) (Clave Ajena hacia Clientes)
Tabla Facturas •
•
•
•
Tabla LineasFactura •
•
•
•
•
•
Numero
Autoincremento (Clave Primaria)
Factura
Entero (Clave Ajena hacia Facturas) Valor no nulo
Cantidad
Entero
Articulo
Entero (Clave Ajena hacia Articulos) Valor no nulo
Precio
Flotante
Total
Flotante
Codigo
Entero (Clave Primaria)
Nombre
CaracterVariable(40)
PrecioCompra
Flotante
IVA
Flotante
Tabla Articulos •
•
•
•
La Clave Ajena hace referencia a que el dato de ese campo deben o ser nulos, si es permitido que lo sea, o existir en la tabla a la que hace referencia dicho campo. Esta regla es conocida como la regla de la integridad referencial. El concepto de Clave Primaria significa que el valor de dicho campo sirve de valor de identificación de ese registro dentro de la tabla, de forma que el dato en él introducido no puede ser nulo ni estar repetido en otro registro de la misma tabla. En la tabla Clientes su Clave Primaria es el DNI (Documento Nacional de Identidad) porque todos los clientes son personas y por lo tanto todos tienen que tener un DNI, primera condición de la Clave Primaria, nunca puede ser nula, y además el DNI no se va a repetir nunca puesto que no existen dos iguales, segunda condición de la Clave Primaria.
El campo Total de la tabla Facturas se refiere al total de la factura. El campo Total de la tabla LineasFactura se refiere al total de cada una de las líneas. Ambos datos son del tipo flotante pensando en que la facturación que intentamos hacer va enfoca al Euro. Como el Euro tiene dos posiciones decimales nos es necesario asignarle un flotante. En la tabla Artículos el campo IVA (Impuesto sobre el Valor Añadido) hace referencia a este impuesto que cada uno de los artículos llevará. El dato del IVA es un porcentaje sobre el precio del artículo. Si nos fijamos ya tenemos definidas las tablas, los campos de dichas tablas, junto con sus tipos de datos y las relaciones que existen entre las diferentes tablas. Las relaciones las tenemos plasmadas a través de los campos de Clave Ajena que nos indican con que tabla estará relacionado el campo que sufre la clave. Los campos que llevan la característica de Valor no nulo nunca pueden dejarse en blanco. Si nos fijamos, estos campos son Claves Ajenas, por lo tanto lo que estamos consiguiendo es obligando a que siempre exista la relación, es decir, si existe un registro en la tabla, este registro estará obligado a relacionarse con la otra tabla, porque el campo que sirve para relacionar ambas tablas nunca puede estar en blanco.
Convenciones de diseño y algún consejo previo Voy a citar las normas de diseño que debemos seguir para trabajar con las bases de datos de SQL y algún consejo que siempre nos vendrá bien seguir. En el desarrollo de los siguientes apartados utilizaré la notación estándar que describo a continuación: •
•
•
•
•
•
•
•
MAYÚSCULAS Palabras clave de Transact-SQL. Cursiva Parámetros
de la sintaxis de Transact-SQL.
| (barra vertical) Separa los elementos de sintaxis con corchetes o llaves. Sólo se puede elegir uno de los elementos. [ ] (corchetes) Elementos de sintaxis opcionales. No escriba los corchetes. { } (llaves) Elementos de sintaxis requeridos. No escriba las llaves. [,...n] Indica que el elemento anterior se puede repetir n veces. Los elementos se separan mediante comas. [ ...n] Indica que el elemento anterior se puede repetir n veces. Los elementos se separan mediante espacios en blanco.
Negrita Nombre de bases de datos, tablas, índices, procedimientos almacenados, programas, tipos de datos y texto que deben escribirse exactamente como se muestra.
En el nombre de la base de datos, de una tabla, de una regla o de un tipo definido por el usuario, es decir en los elementos que el usuario crea y que por lo tanto debe de nombrar, no se pueden introducir espacios en blanco, lo normal es sustituir esos espacios en blanco por el carácter de subrayado ,'_', o comenzar la siguiente palabra por una letra mayúscula. Ejemplo: Lineas Pedido, sería incorrecto, podríamos nombrar esta tabla como Lineas_Pedido,o LineasPedido . A lo largo del artículo yo utilizaré cualquiera de las dos formas indistintamente, aunque lo adecuado es que se elija una forma y se mantenga durante todo el trabajo. Para definir los CONSTRAINT, elemento que abordaremos más tarde, si define una clave primaria el nombre de esta tendrá la forma cp_nombre_tabl a, si se trata de una clave alternativa su nombre será cal_nombre_tabla y si se trata de una clave ajena caj_nombre_tabla. Si existiera dentro de la misma tabla más de una clave del mismo tipo irían numeradas, cp_nombre_tabla1 , cp_nombre_tabla2 , ... .
Crear, conectarse y borrar una base de datos (CREATE DATABASE, CONNECT DATABASE, DROP DATABASE) Cuando comenzamos a crear nuestra base de datos en el servidor, el primer paso delmontaje es crear la base de datos. Hasta que no creamos la base de datos no podemos comenzar a crear las tablas y demás elementos. La creación de la base de datos se hará una única vez. Normalmente el proceso de creación se hace desde el entorno visual del sistema gestor pero no existe ningún inconveniente en hacerlo utilizando comandos SQL. Si lo hacemos desde SQL el comando de creación es CREATE DATABASE, y su sintaxis: CREATE DATABASE nombre_base_datos
La sintaxis de CREATE DATABASE admite muchos más parámetros aunque normalmente no se utilizan. En los sistemas gestores a la hora de crear nuestra base de datos debemos de estar situados en la base de datos del sistema, en la mayoría de los sistemas llamada master. Esto es así porque en ella se introducirá la información de las propiedades de nuestra base de datos, la que terminamos de crear. Una vez que hemos creado nuestra base de datos, proceso que debemos realizar solamente la primera vez, cada vez que queramos trabajar con ella tendremos que establecer la conexión entre nuestro cliente de SQL y el servidor de SQL. Este proceso, al igual que el proceso de creación, se suele realizar desde el interfaz gráfico. Normalmente el sistema gestor nos ofrece un menú desplegable con todas las bases de datos existentes donde seleccionamos la base de datos con la que vamos a trabajar. El comando SQL equivalente a este proceso es CONNECT, y su sintaxis: CONNECT DATABASE nombre_base_datos
Con el comando CONNECT sí vamos a encontrar diferencias importantes dependiendo del sistema gestor con el que trabajemos. Por ese motivo normalmente se realiza desde el interface gráfico. Para borrar una base de datos, tan sencillo como: DROP DATABASE nombre_base_datos
Crear tablas (CREATE TABLE) Para crear las tablas utilizamos el comando CREATE TABLE, cuya sintaxis es: CREATE TABLE nombre_tabla ( columna1 columna2 ... columnan
tipo_dato, tipo_dato, ..., tipo_dato,
CONSTRAINT cp_nombre_tabla PRIMARY KEY (columna1,...,columnan), [CONSTRAINT cal_nombre_tabla UNIQUE (columna1,...,columanan), [CONSTRAINT caj_nombre_tabla FOREIGN KEY (columna1,...,columnan) REFERENCES tabla] )
Las posibilidades de CREATE TABLE son muchas más de las que he mencionado en la lista anterior pero realmente no son utilizadas. Por defecto, cuando creamos una tabla todos los campos de la tabla admitirán valores nulos. En principio es lo adecuado. Solamente encontraremos problemas con los campos que sean claves primarias o alternativas, puesto que por definición estos campos siempre tiene que tener valor, nunca pueden estar vacíos. Por ello, en estas columnas estaremos obligados a añadirles la opción NOT NULL, que obliga a que
esos campos no admitan valores nulos. Esa opción también tendremos que utilizarla en los campos que sean claves ajenas y estemos obligados a que todos los registros de nuestra tabla estén relacionados a través de esa clave ajena. La forma de utilización es muy sencilla: columna1 tipo_dato NOT NULL,
Ahora vamos a conocer los tipos de datos que podemos aplicar a los campos. SQL nos ofrece gran variedad de tipos de datos, aunque como ocurre siempre, nos acostumbramos a utilizar unos pocos y si en alguna ocasión necesitamos plasmar alguna situación un poco más especial recurrimos a la ayuda de SQL para ver más detalladamente otros tipos de datos o nos creamos los nuestros propios. •
•
•
•
•
•
•
•
Integer
enteros comprendidos entre -231 (-2.147.483.648) y 231 - 1 (2.147.483.647).
Smallint
enteros comprendidos entre 215 (-32.768) y 215 - 1 (32.767).
Tinyint
enteros comprendidos 0 y 255.
Real
flotantes comprendidos entre -3,40E + 38 y 3,40E + 38.
Datetime
fechas.
Char
caracteres fijos hasta 8.000.
Varchar
caracteres variables hasta 8.000.
Text
caracteres variables hasta 231 - 1 (1.147.483.647).
Un pequeño comentario sobre los tipos de datos. La diferencia entre caracteres fijos (Char) y caracteres variables (Varchar) estriba en que si nosotros hacemos con Char una reserva de memoria de 50 posiciones y al introducir datos solamente utilizamos 20, el resto será memoria desperdiciada, mientras que si hemos realizado la reserva utilizando Varchar, caracteres variables, si al final solamente utilizamos 20, el resto de memoria será liberada. Lo más adecuado es utilizar siempre para caracteres Varchar, utilizando Char solamente en aquellos casos en que sepamos de antemano que siempre se va a consumir todo el espacio reservado, como podrían ser campos como los números de teléfono, códigos de texto de tamaño fijo, etc. Si en algún momento necesitamos tener un campo entero que sea autonumérico, es decir, que el sistema gestor vaya controlando su valor e incrementándolo o decrementándolo de forma automática, sin que nosotros tengamos que preocuparnos por ello, tenemos que utilizar la opción IDENTITY. Esta opción necesitará dos parámetros, la inicialización, el valor por el que tiene que comenzar, y el incremento, en el que indicamos el valor de incremento, tanto si es positivo como negativo. Su sintaxis: columna1 integer identity (1,1),
Según el ejemplo anterior, el campo columna1, será un autoincrementable que comenzará en el valor 1 e irá incrementando de uno en uno. Y como no hay mejor forma de aprender que trabajando un poco, vamos a ver como serían los comandos SQL para la creación de nuestras tablas de ejemplo. Create table Clientes ( DNI char(9) not null, Nombre varchar(50), Direccion varchar(60), Ciudad varchar(30), CP char(5), Telefonochar(9), constraint cp_Clientes )
primary key (DNI)
Fijémonos en la creación de tabla que acabamos de realizar, y destaquemos algunos detalles. El DNI es la clave primaria, así queda definido en la última línea de la instrucción de creación de la tabla, además, a esta clase, se le ha dado el nombre de cp_Clientes, como ya se había recomendado en el apartado de Convenciones de diseño. Si la clave primaria de nuestra tabla Clientes es el DNI, por definición este campo nunca podrá contener valores nulos, por lo que vamos a tener que indicárselo, indicación que hacemos, tal como se aprecia en el listado, añadiéndole al campo la opción NOT NULL. create table ( Numero Fecha Total DNI
Facturas integer identity (1,1) not null, datetime, real, char(9),
constraint cp_Facturas primary key (Numero), constraint caj_Facturas foreign key (DNI) references Clientes )
En la creación de la tabla Facturas nos encontramos con el comando IDENTITY, que indica que el número de factura se irá generando automáticamente comenzando por el número de factura 1 y creciendo de uno en uno. También nos encontramos la definición de una clave ajena, el campo DNI será una clave ajena qe relacione una factura con un cliente. create table Articulos ( Codigo integer not null, Nombre varchar(40), PrecioCompra real, IVA real, constraint cp_Articulos primary key (Codigo) )
create table LineasFactura ( Numero integer identity(1,1) not null, Factura integer not null, Cantidadinteger, Articulointeger not null, Precio real, Total real, constraint cp_LineasFactura primary key (Numero), constraint caj_LineasFactura1 foreing key(Factura) references Facturas, constraint caj_LineasFactura2 foreing key(Articulo) references Articulos )
Aquí nos encontramos con la existencia de dos claves ajenas, como podemos ver sus nombres son el mismo solamente que numerados para que no haya error. Además a los campos de estas dos claves ajenas se les ha asignado la característica de no poder tomar valores nulos. Por lo tanto, siempre que exista una línea de factura ésta va a tener que pertenecer a una factura y ser de la venta de un artículo, no pueden existir líneas de factura que no cumplan estas condiciones. En realidad el comando CONSTRAINT no es de obligado cumplimiento, podríamos definir claves, tanto ajenas como primarias sin la necesidad de utilizar CONSTRAINT pero no podríamos darles un nombre a dichas claves. En siguientes apartados veremos que inconveniente se nos plantea si tomamos la determinación de no dar nombre a nuestras claves.
Insercción, modificación y borrado de datos (INSERT,UPDATE, DELETE) Una vez que ya tenemos creadas las tablas podemos comenzar a trabajar con los datos, insertando, modificando y borrando datos. ¿Que cómo?, pues lo vemos enseguida. A continuación la sintaxis de cada uno de los procesos respectivamente: INSERT INTO tabla [(columna1,...,columnan)] VALUES (valor1,...,valorn) UPDATE tabla SET
columna1=valor1,...,columnan=valorn WHERE condicion
DELETE FROM tabla WHERE condicion
Bueno, esta sintaxis parece más sencilla que la vista en el apartado anterior. El comando INSERT no tiene ningún secreto, es muy sencilla su forma. Donde podemos encontrar algún problema es con la actualización y el borrado, problema en el sentido de entender y a posteriori determinar correctamente la condición de la cláusula WHERE. Las filas de la tabla que cumplan la condición de la cláusula where será las filas que sufran la actualización o el borrado dependiendo del comando utilizado. Vamos a unos ejemplos. Insert into Clientes (DNI,Nombre,Direccion,Ciudad,CP,Telefono) values (“21547789”,”Agapito Lafuente Corral”,”c./La plaza nueva”,”Alicante”,”03005”,”987785655”) Insert into Clientes (“12456445”,”Maria del mar Sanchez Juan”,”c./La marina”,”Madrid”,”25455”,”887452114)
En la segunda inserción se me ha olvidado poner los nombres de los campos de la tabla, pero no es incorrecto. Si cuando realizamos una inserción ésta afecta a todos los campos de la tabla no es necesario que indiquemos los nombre de los mismos, solamente es necesario cuando vayamos a realizar inserciones en las que algunos campos del nuevo registro no vayamos a rellenarlos, es este caso esos campos en blanco quedarán definidos con el valor NULL. Aquí hay que hacer una pequeña llamada de atención. Podemos perfectamente hacer inserciones que solamente afectan a un subconjunto de los campos de un registro y el resto dejarlo en blanco, pero tenemos que tener la precaución de nunca dejar en blanco ningún campo que hayamos definido como NOT NULL, nos daría un error y no realizaría la inserción. /* Actualiza la dirección y el CP del cliente con DNI 21547789 */ Update Clientes set Direccion=”Plaza Mayor”, CP=”66789” where DNI=”21547789” /*Borra el cliente con DNI 12456445 */ Delete from Clientes where DNI=”12456445” /* Borra todos los clientes de los que no tengamos nombre */ Delete from Clientes where Nombre is NULL
Como podemos apreciar en los ejemplo de los borrados y actualizaciones, utilizar estos procedimientos no tiene, al igual que las inserciones, una gran complicación. El pequeño problema, en los dos primeros, estriba en la cláusula WHERE. Los ejemplos mostrados nos enseñan como utilizar WHERE en sus posibilidades más sencillas, obviamente, podríamos generar condiciones de borrado y actualización algo más complejas. Dejo para el artículo del próximo número de la revista Síntesis el abordar casuísticas más complejas de la cláusula, donde conoceremos con detalle toda su potencia. No obstante voy a citar, junto con algún ejemplo más, algunos de los operadores que podemos utilizar en ella: •
•
•
•
•
•
AND Es el “y” lógico. OR Es el “o” lógico. NOT Es el negador lógico. <, >, <=, >=, =, <> Son los comparadores. BETWEEN Especifica un intervalo de valores. LIKE Compara con un modelo. Ver tabla apartado Creación de reglas para datos.
•
IN Compara con una lista de datos.
/* Borra todas las facturas emitidas antes del 1/1/2000 */ Delete from Facturas where fecha < “1/1/2000” /* Actualiza el valor del descuento al 25% a todos los clientes cuya última compra esté entre 200000 y 300000 pesetas */ Update Cliente set descuento=25 where total_ultima_compra between 200000 and 300000 /* Borra todos los articulos cuyo código comienza por 210 */ Delete from Articulos where codigo like “210%”
Modificar y borrar una tabla (ALTER TABLE, DROP TABLE) Si necesitamos borrar una tabla, situación que se da en raras ocasiones, el comando es: DROP TABLE nombre_tabla
Con todo el trabajo que lleva crear una tabla y lo fácil que es borrarla. Hay que llevar cierta precaución si la tabla se encuentra relacionada con otras tablas. Y ahora, después del tremendo esfuerzo que nos ha supuesto conocer la sintaxis para el borrado de la tabla, je, je, vamos a continuar con nuestra línea normal, ALTER TABLE.
ALTER TABLE, a nadie se le escapa, sirve para realizar modificaciones de definición sobre las tablas ya generadas, modificaciones añadiendo o eliminando campos o claves. Su sintaxis: {ADD {COLUMN tipo de campo[(tamaño)] ALTER TABLE tabla [CONSTRAINT indice] CONSTRAINT indice multicampo} | DROP {COLUMN campo CONSTRAINT nombre del indice} }
Ya lo sé, un poquito liado ¿verdad?, venga, vamos a ver unos ejemplos: /* Agrega un campo Comentario a la tabla Clientes */ Alter table Clientes add column Comentario varchar(255) /* Elimina el campo IVA de la tabla Articulos */ Alter table Articulos drop column IVA /* Añade un campo CIF a la tabla Articulos y después lo convierte en clave ajena para relacionar cada artículo con el proveedor que lo suministra. El CIF es el identificador de un proveedor */ Alter table Articulos add column CIF char(9) Alter table Articulos add constraint caj_Articulos foreing key (CIF) references Proveedores /* Aunque no tenga sentido, borraremos la clave ajena de la tabla LineasFactura a la tabla Factura */ Alter table LineasFactura drop constraint caj_LineasFactura1
En el apartado donde he explicado como se crean las tablas he comentado que cuando generamos una clave, bien sea ajena, primaria o alternativa, el comando CONSTRAINT no es obligatorio incluirlo aunque si recomendable, ahora podemos darnos cuenta del por qué. Si no utilizamos CONSTRAINT no podremos poner un nombre a la nueva clave generada, si la clave no dispone de nombre después no podremos tratarla con otros comandos como son los vistos en este apartado. No cuesta nada, al generar las claves, otorgarles un nombre, y podemos evitar problemas más adelante.
Creación y eliminación de reglas para los datos (CREATE RULE, SP_BINDRULE, DROP RULE, SP_UNBINDRULE) Las reglas nos van a permitir tener un control sobre los datos que se insertan en nuestras tablas, son reglas de validación de datos. A través de una regla, vamos a definir una serie de condiciones, o podemos ver también como asignar un formato a los datos de los campos. Cuando realicemos una inserción en un registro, ésta se llevará a termino solamente si los datos cumplen las condiciones, o tienen el formato, que hemos impuesto en la regla. Las reglas que generemos se asociarán a campos de nuestras tablas. No podemos asociar una regla a una tabla completa, solamente a uno de sus campos. El comando para crear reglas es CREATE RULE, y su sintaxis es muy sencilla: CREATE RULE nombre_regla AS @variable LIKE expresion1 [OR @variable LIKE expresion2 ... OR @variable LIKE expresionN]
Una vez que hemos creado nuestra regla tendremos que hacerle saber al sistema a qué campo de qué tabla tiene que ir asociada, es decir, tenemos que indicar qué datos concretos tiene que controlar. La vinculación de una regla a un campo concreto de nuestra base de datos se realiza con el comando SP_BINDRULE, y es así de fácil: EXEC SP_BINDRULE nombre_regla, 'tabla.campo'
El parámetro expresion del comando LIKE define el dominio correcto de los datos, es decir, define la forma o regla que tienen que cumplir los datos para poder ser aceptados y por lo tanto introducidos en el campo. A continuación vamos a ver de que símbolos disponemos para crear dicha expresión: •
•
•
•
% Cualquier cadena de 0 o más caracteres. _ (subrayado) Cualquier carácter individual. [ ] Carácter individual dentro de un conjunto o intervalo: [abcde], [a-e] [ ^ ] Negación
Vamos a despejar las nubes de nuestras mentes, ¿cómo?, pues como siempre, con unos ejemplitos. Imaginemos una tabla de asignaturas donde tenemos todas las asignaturas que se imparten en un colegio. En la tabla tendremos un campo dia que indica el día que esa asignatura es impartida. Las asignaturas solamente se imparten de lunes a viernes, situación que tendremos que controlar a la hora de introducir los datos de las asignaturas en la tabla. Una forma de controlarlo sería perfectamente con una regla. create rule dias_lectivos as @dia like 'lunes' or @dia like 'martes' or @dia like 'miercoles' or @dia like 'jueves' or @dia like 'viernes' exec sp_bindrule dias_lectivos, 'asignaturas.dia'
Imaginemos una tabla Articulos de una tienda de muebles que sólo comercializa muebles de madera, concretamente sólo de roble, cerezo y haya. create rule tipo_madera as @madera like'roble' or @dia like 'cerezo' or @dia like 'haya' exec sp_bindrule tipo_madera, 'articulos.material'
Imaginemos una tabla Articulos de una fábrica de componentes electrónicos donde el código de los nuevos artículos debe tener siempre el mismo formato, comenzar por la letra H, después una letra y por último tres números. create rule restriccion_codigo as @codigo like 'H[A-Z]7[0-9][0-9][0-9]' sp_bindrule restriccion_codigo, 'articulos.codigo'
Si necesitamos borrar una regla creada, el comando es DROP RULE, pero antes de borrarla, si ya la habíamos vinculado a algún campo, tendremos que desvincular la regla del campo al que esté vinculada. El comando para desvincular reglas es SP_UNBINDRULE. Si no desvinculamos antes de borrar la regla, se producirá un error y no la podremos borrar. La sintaxis de estos comandos es: EXEC SP_UNBINDRULE 'tabla.campo' DROP RULE nombre_regla
Una vez desvinculado el campo de su regla podremos vincularle otra regla diferente.
Creación y eliminación de tipos (SP_ADDTYPE, SP_DROPTYPE)
definidos
por
el
usuario
Como usuarios podemos crearnos nuestros propios tipos de datos. La principalutilidad de esta posiblidad en estos momentos para nosotros será ahorrarnos trabajo. Si vamos a tener en nuestra base de datos en varias tablas un campo que va a ser del mismo tipo, al cual vamos a tener que asociarle una regla, es siempre más cómodo crearnos un tipo de dato para él y asociarle una regla a ese tipo de dato, el cual, cada vez que lo utilizamos para definir un campo cualquiera de nuestras tablas, este campo ya tendrá asociada la regla. Si no obramos así, tendríamos que definirnos el campo en cada una de las tablas con un tipo de dato simple y luego a cada campo tener que ir asociándole por separado la regla, es una solución mucho más laboriosa que la primera. Definiendo un tipo de dato, o definiendo un tipo de dato y vinculándole una regla, ahorramos tiempo y esfuerzo, porque solamente el trabajo lo tendremos que realizar una vez, ya que al asociar a diferentes campos de diferentes tablas el tipo de dato generado, estos campos ya estarán perfectamente definidos, con regla incluida. Sin embargo, si no lo hacemos así, tendremos que repetir el mismo proceso tantas veces como el campo aparezca en nuestra base de datos. Para definir o generar, un tipo de dato por el usuario el comando es ADD_TYPE: SP_ADDTYPE nombre_tipo_dato_usuario, 'tipo_dato_simple'
Para vincular una regla con un tipo de dato de usuario, el comando también es SP_BINDRULE, como hemos visto en el apartado anterior, pero con una pequeñísima diferencia sintáctica, las comillas: EXEC SP_BINDRULE nombre_regla, tipo_dato_usuario
Para desvincular un tipo de dato de usuario de su regla asociada, el comando, al igual que ocurre con la vinculación, es el mismo que para desvincular una regla de un campo de una tabla, y también sufre la misma variación que el comando de vinculación. El comando es SP_UNBINDRULE, y su sintaxis: EXEC SP_UNBINDRULE tipo_dato_usuario
Veamos un ejemplo. Si en nuestra base de datos vamos a tener un campo teléfono varias veces repetido, una vez en la tabla de clientes, otra en la de proveedores, otra en la de colaboradores y otra vez más en la de subcontratados, y siempre este campo va a tener el mismo formato, nueve números, la mejor opción sería definirlo como tipo de usuario y asignarle una regla. De forma que cuando en cada una de las tablas generemos el campo teléfono, le asignemos este tipo de dato donde ya tenemos incorporado el formato y la regla de validación. La secuencias de instrucciones sería:
sp_addtype tipo_telefono, 'char(9)' create rule telefono_correcto as @telefono like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' exec sp_bindrule telefono_correcto, tipo_telefono create table clientes ( ... telefono tipo_telefono, ... )
Si queremos borrar el tipo de dato definido por el usuario, SP_DROPTYPE. Pero si tiene una regla vinculada antes tendremos que romper el enlace que les une. Sería conveniente que si la regla no la vamos a asignar a otro tipo de dato o a otro campo de alguna tabla, borrar la regla. Partiendo del ejemplo del número de teléfono vamos a borrar el tipo de dato. Como está vinculado a una regla, antes tendremos que desvincular y además, como la regla ya no tiene sentido la borraremos también. Exec sp_unbindrule tipo_telefono drop rule telefono_correcto sp_droptype tipo_telefono
El borrar un tipo de dato de usuario no es una acción que se suela realizar, a no ser que inmediatamente después de haberlo creado nos demos cuenta que está mal definido, o si el campo o campos de las tablas para los que se definió el tipo de datos los eliminamos de sus tablas correspondientes.
Creación y borrado de valores por defecto (CREATE DEFAULT, SP_BINDEFAULT, DROP DEFAULT, SP_UNBINDEFAULT) Un valor por defecto es un valor que se le asigna a un campo de la tabla mientras haya ausencia de otro valor introducido por el usuario. Las instancias del campo tendrán por valor su valor por defecto si éste no se cambia. Cuando un campo, en la mayoría de las ocasiones, va a tener siempre un valor concreto, este valor se le puede asignar por defecto, de forma que a no ser que a la hora de la inserción de un nuevo registro o de su actualización lo cambiemos, siempre permanecerá con él. Los pasos para crear un valor por defecto son: primero crear el valor por defecto, CREATE DEFAULT, y segundo vincular este valor por defecto, o a un campo determinado de una tabla o a un tipo de dato definido por el usuario. Para vincular, el comando es SP_BINDEFAULT , y la sintaxis de los comandos es: CREATE DEFAULT nombre_valor as valor EXEC SP_BINDEFAULT 'nombre_valor, 'tabla.campo' /* Para campo concretos de tablas */ EXEC SP_BINDEFAULT nombre_valor, tipo_dato_usuario /* Para tipos de datos de usuarios */
Un ejemplo. En una tabla de datos de trabajadores de una empresa donde se reflejan sus datos laborales mensuales, tenemos un campo horas_trabajadas, que refleja las horas trabajadas por casa empleado al mes. A no ser que el empleado falte por algún motivo injustificado al trabajo, sus horas trabajadas mensuales son siempre las mismas, supongamos 120. Podríamos aplicarle a este campo un valor por defecto que nos evite para cada trabajador tener que estar siempre introduciendo este valor. Solamente tendremos que cambiar el valor cuando el trabajador no se ajuste a las horas normales de trabajo. create default horas_trabajadas_120 as 120
exec sp_bindefault 'horas_trabajadas_120', 'trabajo_mes.horas_trabajadas'
Los valores por defecto definidos se puede borrar con el comando DROP_DEFAULT, pero si los tenemos ya vinculados previamente tendremos que romper el enlace con SP_UNBINDEFAULT . ¿La sintaxis?, enseguida: DROP DEFAULT nombre_valor SP_UNBINDEFAULT 'tabla.campo' /* Para campos concretos de tablas */ SP_UNBINDEFAULT tipo_dato_usuario /* Para tipos de datos definidos por el usuario */
Si deseáramos borrar el valor por defecto utilizado en el ejemplo anterior, primero, a nadie se le escapa ya, tendríamos que desvincularlo del campo o del tipo de dato de usuario y después proceder a su eliminación. Veamos la secuencia de instrucciones: sp_unbindefault 'trabajo_mes.horas_trabajadas' drop default horas_trabajadas_120
Breve anticipo del siguiente artículo. Las consultas con SQL, de momento... Bueno, ya hemos llegado casi al final. A lo largo de todo el artículo hemos conocido los fundamentos de SQL, un lenguaje soportado por todos los grandes gestores de bases de datos, que nos permite trabajar con nuestros datos con una independencia del gestor. SQL día a día se instaura más en el mercado con lo que se hace ya imposible pensar en trabajar con bases de datos sin pensar en SQL. Hasta aquí hemos conocido como podemos definir nuestros datos, en el próximo artículo, dentro de dos meses, veremos como podemos trabajar con ellos. Como se que la espera es larga voy a iniciar un poco los temas que podremos conocer , espero que antes que podamos darnos cuenta, en lo que será una continuación de hasta lo aquí expuesto. No sirve de nada que diseñemos nuestra base de datos, sus tablas, sus reglas, nuestros propios tipos de datos, introduzcamos datos, etc..., si después no vamos a manipularlos, a darles vueltas, a jugar con ellos. Uno de los comandos que SQL nos ofrece para divertirnos con nuestros datos es SELECT, de hecho, es uno de los más conocidos de todo el lenguaje. No vamos a detallar en este momento como utilizar SELECT solamente pondremos su sintaxis más sencilla y mostraré algunos ejemplos de utilización. SELECT campo1[, campo2, ... ,campoN] FROM tabla1 [, tabla2, ... ,tablaN] [ WHERE condicion1 [AND condicion2 | OR condicion2 | ... | AND condicionN | OR condicionN] ]
Traducido a un lenguaje natural podríamos interpretar la sintaxis anterior como: selecciona estos campos que se encuentran en estas tablas si cumplen estas condiciones. Esta sería una forma muy llana de interpretar el comando SELECT, y muy poco ortodoxa, pero sencilla para el que nunca haya visto antes el comando. Vamos a poner un cuantos ejemplos, antes de verlos recomiendo que se recuerde el apartado inserción modificación y borrado de datos, de este mismo artículo, donde explicamos, a grandes rasgos, el funcionamiento de la WHERE. /* Tenemos una tabla Artículos y queremos ver todos sus datos */
select * from Articulos /*De la tabla Artículos sólo queremos ver los datos de los campos Código y Nombre del artículo */ select Codigo, Nombre from Articulos /* El mismo caso que el anterior pero de los artículos cuyo precio de venta sea mayor que 1000 pesetas */ select Codigo, Nombre, PrecioVenta from Articulos where PrecioVenta > 1000 /* El mismo caso que el anterior pero los artículos que tengan un precio de venta comprendido entre 1000 y 3000 pesetas */ select Codigo, Nombre, PrecioVenta from Articulos where PrecioVenta between 1000 and 3000 /* Todos los clientes que residan en la ciudad de Alicante */ select * from Clientes where Ciudad=”Alicante” /* Todos los artículos cuya materia prima sea el acero o el aluminio */ select Nombre from Articulos where materia=”aluminio” or materia=”acero”
Y así podríamos continuar en innumerables ejemplos hasta llegar a determinar toda la potencia de SELECT. Ha llegado ya el momento de despedirnos, espero haber ayudado a que conozca mejor SQL, y a que se interese, si no lo estaba ya, por este mundo de las bases de datos. Le emplazo para los siguientes números de la revista Síntesis donde continuaré buceando por este océano tan grande, el Océano Bases de datos, para intentar que usted, desde mí pequeña contribución, siempre se mantenga a flote en él y sin perder el rumbo. Un saludo, muchas gracias y hasta pronto.
Manipulación de datos en SQL. Las Consultas. Óscar Javier Segura Amorós -
[email protected]
Las consultas de SQL con su archiconocido SELECT, de ahora en adelante, uno de nuestros mejores amigos. Hola de nuevo. Continua aquí la serie sobre el lenguaje SQL que comenzó en el número anterior de la revista. Lo primero de todo y antes de proseguir, vamos a hacer un poco de memoria para recordar las bases que ya quedaron sentadas en el artículo anterior y que nos van a servir de punto de partida en éste. En el anterior artículo comenzamos viendo que es SQL (Lenguaje de Consultas Estándar), vimos que era un lenguaje para trabajar con las bases de datos, que era un lenguaje muy difundido por su alta estandarización y que prácticamente la totalidad de los sistemas gestores de bases de datos lo trabajaban y en muchas ocasiones, sin diferencias notables en su forma. Vimos que tiene dos ámbitos de trabajo, pues podemos clasificar todas sus instrucciones dentro de dos grandes grupos: las instrucciones que trabajan con la estructura de los datos, o lenguaje de definición de datos (LDD) y las instrucciones que trabajan con los datos en sí, o lenguaje de manipulación de datos (LMD). En el pasa artículo nos centramos solamente en el lenguaje de definición de datos, momento en el cual abordamos las instrucciones más importante dentro del grupo de definición de datos, como las instrucciones para crear las tablas, las reglas, los tipos de datos, insertar valores, borrarlos, modificarlos,..., prometiendo una continuación al emplazaros a todos para la siguiente entrega donde abordaría las instrucciones de manipulación de datos, y como lo prometido es deuda, aquí estoy de nuevo. En el artículo de este número vamos a empezar a conocer las instrucciones de manipulación de datos. Estas son muchas y variadas por lo que, de momento, no las vamos a tratar en su totalidad. Vamos a empezar por el principio, centrándonos en las consultas bajo SQL dejando ya para el próximo artículo las vistas y los procedimientos almacenados. Sería un disparate, por su complejidad y extensión, pretender conocer todo el lenguaje de manipulación de datos en solamente un artículo. De esta forma, vamos a poder centrarnos en algo más concreto, las consultas, verlas con profundidad y descubrir toda la potencia que nos ofrecen. Bueno, si nos vamos a centrar en las consultas, lo primero que nos tendremos que plantear es, ¿qué es una consulta?, ¿para qué sirve?. Preguntas totalmente fuera de lugar puesto que todos ya sabemos a estas altura que las consultas son unos procedimientos que nos van a permitir interrogar a nuestra base de datos lanzando preguntas a las tablas para obtener de esta forma los datos que nosotros necesitamos en un momento determinado. De nada sirve introducir datos en unas distribuciones de tablas si después no podemos recuperarlos, es más, ya no basta recuperar esos datos, sino que además queremos recuperar no la totalidad de los datos, solamente vamos a querer recuperar en cada momento los datos que no son necesarios. Para eso existen los criterios de búsqueda de datos dentro de las consultas. La complejidad de las consultas estriba, no en hacer la consulta en si, puesto que una consulta que nos muestre todos los datos de una tabla o de un conjunto de ellas no tiene ningún misterio. Muchas veces la complejidad la vamos a encontrar en generar esos criterios, esas condiciones que van a tener que satisfacer los datos que la consulta nos va a mostrar, los datos que nosotros queremos conocer de entre todos los que tenemos almacenados. Y ya sin más dilación empecemos que tenemos mucho camino por recorrer, suerte en el recorrido.
Base de datos de ejemplo. Gestión de proveedores. Junto con el artículo encontrareis varios ficheros para descargar. Estos ficheros son un apoyo, un complemento para poder entender mejor los diferentes apartados que a continuación abordaremos. Durante todo el artículo completaré las explicaciones de cada apartado con ejemplos. Estos ejemplos están realizados sobre una base de datos, Gestión de proveedores , la cual podría ser un pequeño ejemplo de control de artículos de una empresa informática. En los ficheros encontrareis el scrip de SQL para generar la base de datos e introducir en ella unos datos, que por supuesto son imaginarios, datos suficientes para poder trabajar cómodamente. Además encontrareis otros ficheros con propuestas de ejercicios y sus soluciones, basados también en la en Gestión de proveedores, que servirán para poder practicar todos los apartados vistos en el artículo. Los ficheros son 3, los cuales os describo a continuación: ?
?
?
Fichero BaseDatos.sql: Dentro de este fichero encontrareis el scrip de SQL para la creación de la base de datos de Gestión de proveedores junto con las instrucciones de inserción de los datos para que se pueda trabajar ya con ella. El scrip está optimizado para utilizarlo en SQL Server 7, lo cual no quiere decir que no vaya a funcionar en otros gestores. Si lo queremos utilizar en Interbase tendríamos que hacer dos pequeñas modificaciones: en la creación de las tablas sustituir el tipo de datos para fechas, datetime , por el tipo soportado por Interbase, date , y en las inserciones de los datos, las fechas que están escritas en el formato dd/mm/aaaa cambiarlas por el formato mm/dd/aaaa. Tras estas dos modificaciones podríamos correr el scrip en Interbase sin ningún problema. Fichero Ejercicios.txt: Donde encontrareis los enunciados de propuestas de ejercicios para poder practicar todo lo aprendido. Los ejercicios están basados en la base de datos de Gestión de proveedores. Fichero Soluciones.sql: Donde encontrareis las soluciones a los ejercicios propuestos en el fichero Ejercicios.txt en forma de scrip de SQL.
Os presento ahora las tablas de la base de datos de ejemplo Gestión de proveedores: VENDEDOR ( numvend SMALLINT not null, nomvend VARCHAR(30), nombrecomer VARCHAR(30), telefono VARCHAR(15),calle VARCHAR(30), ciudad VARCHAR(20), provincia VARCHAR(20), cod_postal CHAR(5) ) Clave primaria: (numvend)
PIEZA ( numpieza VARCHAR(16) not null, nompieza VARCHAR(30), preciovent INTEGER ) Clave primaria: (numpieza)
PRECIOSUM ( numpieza VARCHAR(16) not null, numvend SMALLINT not null, preciounit INTEGER, diassum SMALLINT, descuento SMALLINT ) Clave primaria: (numpieza,numvend) Clave ajena: (numpieza)--> PIEZA Clave ajena: (numvend)--> VENDEDOR
PEDIDO ( numpedido SMALLINT not null, numvend SMALLINT, fecha datetime ) Clave primaria: (numpedido) Clave ajena: (numvend)--> VENDEDOR
LINPED ( numpedido SMALLINT not null, numlinea SMALLINT not null, numpieza VARCHAR(16), preciocompra INTEGER, cantpedida SMALLINT, fecharecep datetime, cantrecibida SMALLINT ) Clave primaria: (numpedido,numlinea) Clave ajena: (numpedido)--> PEDIDO Clave ajena: (numpieza)--> PIEZA
INVENTARIO ( numbin SMALLINT not null, numpieza VARCHAR(16) not null, cantdisponible SMALLINT, fecharecuento datetime, periodorecuen SMALLINT, cantajuste SMALLINT, cantreord SMALLINT, puntoreord SMALLINT ) Clave primaria: (numbin) Clave alternativa: (numpieza) Clave ajena: (numpieza)--> PIEZA
Es una ventaja, si antes de realizar los ejercicios y tras crear la base de datos e insertar los datos, listamos los datos de cada una de las tablas por impresora. De esta forma al tener un listado de cada una de ellas podemos comprobar sobre el papel si los datos obtenidos con la ejecución de nuestras consultas son los datos correctos o la consulta es incompleta o errónea. ¿Qué como podemos hacer eso?, muy fácil, veamos el siguiente apartado. No perder de vista el esquema de las tablas que se han presentado, puesto que entenderemos mejor las explicaciones de los apartados.
Selección de columnas en consultas de una sola tabla (SELECT, FROM) En las consultas, la selección de las columnas que vamos a mostrar se realiza con las cláusulas SELECT, para indicar las columnas a mostrar y FROM para indicar la tabla en la que se encuentran dichas columnas. Su sintaxis: SELECT [DISTINCT] Columna1,...,ColumnaN FROM Tabla
La sintaxis descrita, la cual es muy simple, serviría solamente para mostrar las columnas de una única tabla, puesto que en la cláusula FROM solamente hemos especificado una tabla. En siguientes apartados veremos como realizar consultas en las que se utilizan varias tablas, no basta con indicar las diferentes tablas en la cláusula FROM. Si queremos mostrar la totalidad de las columnas de una tabla no es necesario enumerarlas todas dentro de la cláusula SELECT, bastaría con porner un *, indicativo de totalidad. En el apartado anterior recomendaba que se listaran todos los datos de cada una de las tablas. Si queremos mostrar toda la tabla Vendedor, podríamos comenzar por ella misma, es decir, todos sus datos, todas sus columnas: select *
from vendedor
Al poner un * nos ahorramos el tener que escribir el nombre de todos sus campos. Esta consulta la tendríamos que repetir para cada una de las seis tablas. Si ahora quisiéramos ver solamente los datos correspondientes al numero de vendedor y su nombre, sin querer mostrar la información del resto de las columnas, tendremos que seleccionar exclusivamente estos campos. Recordar que aunque la tabla seleccionada tenga más campos, solamente se mostrarán aquellos que indiquemos, seleccionemos en la cláusula SELECT: select numvend, nomvend from vendedor
Si lo deseamos, podemos dar un nombre a las columnas que mostramos en la consulta. Para renombralas bastará con escribir el nombre que les queramos dar, entre comillas si es un nombre compuesto o si es un nombre simple directamente, después del nombre real de la columna en la sentencia SELECT. Si no lo hacemos el gestor nos mostrará el nombre real de las columnas. Interbase no soporta los renombramientos compuestos de columnas. select numvend “Número de vendedor”, nomvend “Nombre del vendedor” from vendedor
Cuando renombramos las columnas, indicar, que el nuevo nombre pertenece solamente al ámbito de la consulta, por lo que nunca se cambiará el nombre real de la columna dentro la de base de datos, es lo lógico. Sería un auténtico galimatías si cada vez que renombramos una columna en una consulta ese nuevo nombre también se extendiera a las correspondientes tablas. El parámetro DISTINCT se utiliza cuando en los datos obtenidos en la consulta tenemos algunas filas que son iguales, con este parámetro eliminaremos de la consulta aquellas filas que sean repetidas. select distinct provincia from vendedor
Como existen varios vendedores que pertenecen a las mismas provincias, al ejecutar esta consulta obtendríamos la provincia de cada uno de los vendedores por lo que tendríamos muchos nombres de provincias que se repiten. Para evitarlo indicamos a la consulta que solamente nos muestre aquellas filas que sean diferentes, eliminando así del resultado las que repiten.
Ordenar resultados de la consulta (ORDER BY) Podemos realizar ordenaciones de los resultados que las consultas nos van a devolver. Podemos elegir que los resultados obtenidos se muestren de forma ascendente o descendente, basándose en los valores de unas determinadas columnas de la consulta. Esto lo conseguimos con la instrucción ORDER BY. ORDER BY Columna1,...,ColumnaN ASC|DESC] [
ORDER BY lo situaremos siempre por debajo de la cláusula FROM, esto no quiere decir que deba ir exactamente bajo esta cláusula, puesto que en los siguientes apartados veremos otras instrucciones que podemos utilizar, la única condición es que ORDER BY nunca se situe antes que SELECT o FROM. La columnas indican los campos que nos van a servir como criterios de ordenación, de forma que si indicamos varias columnas siempre se ordenarán los valores en función de la primera, y si en la primera se encuentran valores iguales, entonces las filas correspondientes a estos valores se ordenarán según la segunda columna indicada, y así sucesivamente.
ASC y DESC que son dos parámetros opcionales, se utilizan para indicar la ordenación, si deseamos una ordenación ascendente o descendente respectivamente. En el caso que se se indique ninguno de los dos parámetros se realizará una ordenación ascendente.
Si no especificamos nosotros en la consulta ningún orden de listado, los datos serán mostrados en el orden en el que se encuentran en las tablas. Un ejemplo. Mostrar de todos los vendedores su nombre, dirección y número de vendedor de forma ordenada. select nomvend “nombre”, direccion, numvend “número vendedor” from vendedor order by nomvend
Condición de selección (WHERE) En la mayoría de nuestras consultas no querremos hacer listados de la totalidad de los datos de una tabla, sino que querremos hacer listado parciales según algún criterio de selección de datos, es decir, mostrar solamente aquellos datos que cumplan unas determinadas condiciones, unos determinados criterios. Para imponer estos criterios de selección se utiliza la cláusula WHERE y su sintaxis es: WHERE Condición [and Condición2 | or Condición2 | ... |
and CondiciónN | or CondiciónN]
En una cláusula WHERE podemos poner tantos criterios de selección como necesitemos, uniendo dichos criterios por los los operadores lógicos AND y OR. Los criterios o condiciones se pueden especificar de muchas formas, de momento vamos a ver solamente la forma más simple, los comparadores : <, >, <=, <=, =, <>, !=, !<, !>. En siguientes apartados utilizaremos cláusulas que nos van a permitir simplificar las condiciones o darles mayor potencia. Y como no hay nada mejor que un ejemplo... Antes hemos listado todo el contenido de la tabla vendedor, ahora solamente vamos a querer listar a aquellos vendedores cuya ciudad sea Alicante: select * from vendedor where ciudad=”Alicante”
Como la ciudad es un campo de tipo carácter siempre que queramos trabajar con él tenemos que hacerlo poniendo el dato entre comillas dobles. Queremos los vendedores de que pertenezcan a la empresa OLE ESPAÑA y sean de la ciudad de Madrid: select * from vendedor where nombrecomer=”ole españa” and ciudad=”madrid”
Y ahora queremos los número de pieza y la fecha de recuento de todas aquellas piezas de las que tengamos una cantidad disponible superior o igual a 10 unidades: select numpieza,fecharecuento from inventario whhere cantdisponible>=10
Llegados a este punto un comentario sobre diferencias de funcionamiento de un gestor de bases de datos a otro. Los ejemplos ahora citados funcionan perfectamente en SQL Server, mientras que si los intentamos utilizar bajo Interbase hay que tener una pequeña precaución, puesto que éste hace distinción entre mayúsculas y minúsculas en los datos, así que tendremos que tener la precaución de trabajar siempre en nuestra base de datos en un solo formato, o bien mayúsculas o minúsculas. Si el primer ejemplo de todos lo ejecutamos bajo Interbase la consulta no nos hubiera devuelto ningún dato, siendo esto incorrecto, al sí haber datos que cumplen el criterio, pero no se muestran porque nosotros no hemos puesto el término de condición en mayúsculas, tal y como se encuentra el dato almacenado en la tabla.
Consultas con varias tablas Cuando realizamos consultas en las que vamos a utilizar varias tablas, dos, tres, cuatro, ..., normalmente esas tablas son tablas relacionadas, es decir, unas tablas hacen referencias a las otras a través de las claves ajenas que hayamos definido en la creación de las mismas. En pocas ocasiones vamos, en una misma consulta, a visualizar datos de varias tablas que no están relacionadas. La forma de realizar una consulta utilizando varias tablas es muy sencilla, basta con poner los nombre de las tablas, separadas por comas, en la cláusula FROM . FROM Tabla1,...,TablaN
Poner los nombre de las tablas a utilizar dentro de la cláusula FROM sería suficiente si las tablas fueran tablas no relacionadas, es decir, si fuera una de esas consultas que en raras ocasiones se nos va a presentar realizar. Si las tablas están relacionadas, deberemos dentro de la cláusula WHERE indicarlo. La forma de indicarlo es igualando los campos que tienen en común cada tabla con las demás. FROM Tabla1, Tabla2 WHERE Tabla1.CampoComun=Tabla2.CampoComun
Veámoslo con un ejemplo. Queremos obtener todas los números de pieza y en que número de pedido se han solicitado siendo el número de vendedor el 1. ¡Qué nadie se asuste!, parece más de lo que en realidad es. ¿La solución?, sí, claro, al instante: select linped.numpedido "Numero pedido",numpieza "Numero de pieza" from linped,pedido where numvend=1 and linped.numpedido=pedido.numpedido
Necesitamos la tabla Pedido puesto que en ella se encuentra el número de vendedor al que se realiza el pedido y necesitamos también la tabla Linped (Linea de pedido) puesto que en ella se encuentran las piezas que se piden en cada pedido. Estas dos tablas están relacionadas por el dato Numpedido (Número de pedido), que si repasamos el esquema de las tablas, es la clave ajena que existe de Linped a Pedido, y este enlace se lo indicamos a la consulta en el apartado del WHERE, además también dentro de éste apartado indicamos el criterio de que el vendedor tiene que ser el número 1. Incluso para que queden más claros los datos mostrados, renombramos las columnas. En el ejemplo anterior apreciamos como el dato común en las dos tablas y que sirve de enlace entre ellas es Numperido, y cada vez que hacemos referencia a él tenemos que indicar a que Numpedido nos estamos refiriendo, si al de la tabla Linped o al de la tabla Pedido. Esto lo realizamos escribiendo el nombre de la tabla, un punto y el nombre del campo duplicado, por así llamarlo. Por ese motivo en la cláusula SELECT, cuando utilizamos Numpedido le tenemos que anteponer la tabla a la que pertenece. En el caso del SELECT, la tabla a la que pertenece Numpedido es indiferente, puesto que ya en el WHERE nos hemos ocupado de igualar esos números de pedido. Sin embargo no ocurre así en el WHERE, donde si tenemos que hacer corresponder cada número de pedido de la igualdad con su tabla, por un lado el Numpedido de la tabla Linped y por el otro el Numpedido de la tabla Pedido. Si no realizamos el enlace entre las tablas dentro de la consulta se produciría un producto cartesiano entre los datos de ambas tablas, relacionando así cada dato de la primera tabla con todos los datos de la segunda y por lo tanto mostrándonos un conjunto de datos que no son reales. Recomiendo que pruebes a repetir la consulta anterior suprimiendo el enlace de las tablas y observes el resultado. Es recomendable, para optimizar la consulta, indicar siempre el enlace de las tablas como última condición en el WHERE. Cuando nosotros dentro del apartado FROM indicamos que queremos trabajar con varias tablas, el gestor crea una tabla virtual, dentro del ámbito de la consulta, en la que introduce el resultado de realizar el producto cartesiano entre las tablas que nosotros le hemos indicado. Después sobre esta tabla virtual elimina las filas, los registros, que no cumplan los criterios indicados en el WHERE. Va evaluando sobre los datos de la tabla virtual los criterios por el orden en el que se encuentran escritos, de esta forma si nosotros antes de especificar el enlace hemos especifica el resto de criterios, a la hora de comprobarlos
tendrá que operar con menos datos puesto que muchos de ellos ya los habrá eliminado porque no cumplían las condiciones anteriores. Entendamos que al comprobar el enlace tiene que recorrer todos los registros de la tabla comprobando la igualdad de cada registro con todos los demás, trabajo muy arduo, ayudémosle a que las comprobación sea lo más pequeña posible. Tras comprobar todos los datos con los criterios impuestos y los enlaces, los datos que hayan superado todas las condiciones serán los que queden en la tabla virtual, y está nos será presentada a nosotros como resultado de la consulta.
Rangos de valores (BETWEEN) En las consultas, la búsqueda de valores entre unos rangos determinados resulta de gran importancia. Podríamos hacerlas utilizando los operadores de comparación, pero existe una forma mejor, la instrucción BETWEEN. [NOT] BETWEEN Expresión1 AND Expresión2 WHERE Columna
La Columna será la columna dentro de la cual queramos comprobar si existe el valor comprendido dentro del rango que nosotros marcamos. La Expresión1 y Expresión2 será el menor valor del rango y el mayor valor del rango respectivamente. Los rangos de valores deben de ser numéricos o fechas. Fijémonos que podemos realizar selecciones inversas al poder utilizar el operador NOT. Si queremos encontrar todas aquellas piezas cuyo precio de venta esté comprendido entre 500 ptas y 2000 ptas. Tenemos dos formas de hacerlo, la primera, que es la más larga y la segunda que es la más adecuada: /*Primera forma. Para principiantes. No es su caso*/ select * from pieza where preciovent>=500 and preciovent<=2000 /*Segunda forma.*/ select * from pieza where preciovent between 500 and 2000
Si por el contrario, quisiéramos encontrar todas aquellas piezas cuyo precio de venta no esté comprendido entre 500 y 2000 ptas también tenemos dos formas de hacerlo:
/*La primera, que como antes no es la más correcta*/ select * from pieza where preciovent<=500 or preciovent>=2000 /*La segunda, la buena*/ select * from pieza where preciovent not between 500 and 2000
Búsqueda en listas de valores (IN) Una lista es una sucesión de valores separados entre comas. La cláusula IN nos permite trabajar con una lista de valores, para ello, la sintaxis sería la siguiente: [NOT] IN WHERE Columna
(lista de valores)
De esta forma podemos comparar los datos de una columna de una tabla con una lista de valores de una forma muy sencilla, evitándonos el tener que comparar la columna con cada uno de los valores de forma independiente y uniendo todas esas comparaciones con el operador OR. La lista de valores puede ser de tres tipos: o una lista de valores numéricos, en cuyo caso irán separados por comas, o una lista de valores alfanuméricos, o fechas, en cuyos casos, cadenas alfanuméricas o fechas, irán separados por comas y además cada uno de ellos encerrado entre comillas dobles o simples. Listar todos las líneas de los pedidos 1,2 y 5. select * from linped where numpedido in (1,2,5)
Listar los nombres, números y cantidad disponible de todas aquellas piezas suministradas por vendedores de las provincias de Alicante, Madrid o Valencia. select nompieza "Nombre pieza",li.numpieza "Número pieza", cantdisponible "Cantidad disponible", provincia "Provincia" from vendedor ve, pieza pie, inventario inv, pedido pe,linped li where provincia in ("Alicante","Madrid","Valencia") and ve.numvend=pe.numvend and pe.numpedido=li.numpedido and li.numpieza=pie.numpieza and pie.numpieza=inv.numpieza
Comentemos el ejemplo anterior que tiene mucha “miga”. Lo más complicado es la cantidad de enlaces que hemos tenido que hacer entre las diferentes tablas. Esta gran cantidad de enlaces es debido a que dos de los datos que queremos obtener están en una tabla, el tercero está en otra y la condición que se debe de cumplir está en otra diferente, por eso tanta tabla y tanto enlace. Si nos fijamos en la cláusula FROM, tras cada una de las tablas que queremos cargar hemos definido un alias, de forma que cuando queramos, en otro apartado de la consulta, hacer referencia a esa tabla lo podamos hacer de forma más cómoda utilizando el alias, es simplemente por comodidad. En la cláusula WHERE hemos puesto el criterio de la provincia lo primero de todo y después los enlaces, de esta forma optimizamos la ejecución de la consulta como ya he explicado en apartados anteriores. En la cláusula SELECT hacemos referencia al campo Numpieza, campo o columna que aparece en varias de las tablas que hemos cargado para la consulta, por lo tanto, tendremos que indicar de cuales de dichas tablas queremos que sean obtenidos los datos de dicha columna, como ya previamente habíamos enlazado las tablas es indiferente el ámbito que le asignemos. Y centrándonos ya en la parte de la consulta que se refiere a este apartado fijaros como indicado en la lista los valores que queremos encontrar, los nombre de las provincias, y como los valores son alfanumérico los hemos escrito entre comillas dobles.
Búsqueda de valores en una cadena (LIKE) Podemos preguntar por subcadenas dentro de columnas tipo carácter. Para ello usaremos el operador LIKE y su sintáxis: [NOT] LIKE “Expresión” WHERE Columna
La Expresión puede estar compuesta por los siguientes signos: ?
Letra y números.
?
%: Cualquier cadena de 0 o más caractéres.
?
_: Cualquier carácter individual o ausencia de éste.
?
[]: Conjunto de caracteres, como [abcdef], o un intervalo de estos, como [a-e]
?
[^]: Negación.
Por lo tanto, con los signos citados podemos generar expresiones que representen las posibilidades de cadenas que nosotros buscamos. La cláusula LIKE no se utiliza cuando buscamos una cadena en concreto, o un conjunto de cadenas determinadas que no sean parecidas, para eso utilizamos una lista de valores con la sentencia IN. LIKE la utilizaremos cuando busquemos un conjunto de cadenas en las que exista un patrón, donde podamos encontrar una expresión, utilizando los signos permitidos, que nos permita identificar a todas las cadenas del conjunto que buscamos. Dependiendo de lo fácil o complicado que sea encontrar el patrón de las cadenas que buscamos, optaremos por utilizar LIKE, si es fácil encontrar el patrón, o IN, enumerando la totalidad de las cadenas, si no existe patrón o es muy complicado encontrarlo. Es muy importante destacar que se diferencia entre mayúsculas y minúsculas. Encontrar todos los vendedores cuyo nombre es Manuel. select * from vendedor where nomvend like “Manuel %”
Nos vemos obligados a utilizar LIKE puesto que sabemos que el nombre debe de empezar por Manuel pero desconocemos cuales van a ser sus apellidos por lo que tendremos que poner el comodín de las cadenas “%”. Ahora encontrar todos los vendedores cuyo nombre es Manuel o Manolo select * from vendedor where nomvend like “Man[uel,olo] %”
Hemos encontrado el patrón, ambos nombre comienzan por las letras “Man” y depués el sufijo “uel” o “olo”, y como en el caso anterior, posteriormente “%” puesto que desconocemos sus apellidos. Encontrar todos los vendedores que uno de sus apellidos sea Pérez. select * from vendedor where nomvend like “% Pérez %”
Como uno de sus apellidos es Pérez, pero desconocemos su nombre y su segundo apellido, en el caso que Pérez fuera el primero, o si es el segundo apellido, desconocemos tanto su nombre como su primer apellido, ponemos “%” tanto delante como detrás. En el caso en el que buscáramos solamente a los vendedores cuyo segundo apellido es Pérez, deberíamos de colocar un solo “%” delante del apellido, puesto que al estar seguros que Pérez es su segundo apellido no habrá ningún texto detrás de éste. Encontrar todas las piezas cuyo número no empieza por T, tengan a continuación un guión,tres ceros, cualquier número y terminen con cualquier letra. select * from pieza where numpieza like “[^T]-000[0-9]%[A-Z]”
Esta ha sido ya, lo reconozco, una búsqueda caprichosa, pero no está de más hacerla y comprobar la potencia que LIKE nos facilita. Mucha atención a los signos utilizados y el orden de aparición.
En Interbase las expresiones compuestas por los signos [ ] o [^] no son soportadas.
Valores nulos (IS NULL) Cuando se habla de valores nulos, automáticamente se piensa en un carácter en blanco, sin embargo, al hacer esta reflexión caemos en un grave error. Un valor nulo es la ausencia de valor, es decir, cuando a una columna no se le atribuye ningún valor. Como casi todos los lenguajes existentes en la actualidad, en SQL también existe una palabra reservada que hace referencia al valor nulo, la palabra clave NULL. Para realizar una consulta sobre los valores nulos de una columna específica basta con seguir la siguiente sintaxis: WHERE Columna IS NULL
Un ejemplo rapidísimo. Obtener todas las piezas que no tengan descuento aplicado. select * from pieza where descuento is null
No hay que incurrir en el error típico de hacerlo así: descuento=NULL o descuento=”NULL” puesto que de ninguna de las dos formas obtendremos los resultados esperados.
Funciones de agregado Las funciones de agregado realizan un cálculo sobre un conjunto de valores y devuelven un solo valor. Con excepción de COUNT, las funciones de agregado omiten los valores nulos. A continuación un listado de las funciones de agregado: AVG ([DISTINCT] Expresión ): Devuelve la media de los valores de un grupo. Los valores nulos se omiten. ALL|DISTINCT] Expresión ): Devuelve el número de elementos de ? COUNT (*|[ un grupo. ALL|DISTINCT] Expresión): Devuelve el valor máximo de la ? MAX ([ expresión. ALL|DISTINCT] Expresión): Devuelve el valor mínimo de la ? MIN ([ expresión. Devuelve la suma de todos los valores o de sólo los valores DISTINCT en la expresión especificada. ALL|DISTINCT] Expresión): Devuelve la suma de todos los valores ? SUM ([ o de sólo los valores DISTINCT en la expresión especificada. Sólo puede utilizarse con columnas numéricas. Los valores nulos se omiten. ? STDEV (Expresión): Devuelve la desviación típica estadística de todos los valores de la expresión especificada. ? STDEVP (Expresión ): Devuelve la desviación típica estadística de la población para todos los valores de la expresión especificada. ? VAR (Expresión): Devuelve la varianza estadística de todos los valores en una expresión dada. ? VARP (Expresión ): Devuelve la varianza estadística del llenado para todos los valores en la expresión dada. ?
Dichas funciones agregadas retornan valores calculados sobre una determinada columna. Estas funciones devuelven un único valor para todas las tuplas seleccionadas mediante la condición de la cláusula WHERE, si no se especifica ésta, el cálculo se realiza sobre la totalidad de la columna. Las funciones estadísticas precisan que la expresión que se evalúe se construya sobre columnas de tipo de datos numéricos.
Las expresiones pueden contener el nombre de una columna o un cálculo sobre una o varias columnas, Si se especifica la clave DISTINC, la expresión obligatoriamente ha de ser un nombre de columna, y se asume que la función se calcula únicamente sobre valores distintos de la expresión. Deberemos tener en cuenta que no se pueden anidar funciones, es decir AVG(MIN(preciovent)) no está permitido. Las funciones de agregado sólo se pueden utilizar como expresiones en: la lista de selección de una instrucción SELECT, cláusula COMPUTE o COMPUTE BY y en la cláusula HAVING, la cual veremos a continuación. También podemos utilizar los operadores aritméticos: + (sumar), - (restar), * (multiplicar), / (dividir) y % (módulo) para realizar cálculos. Unos ejemplos. Obtener la diferencia entre cantidad pedida y cantidad recibida de las líneas del pedido1. select (cantpedida-cantrecibida) from linped where numlinea=1
Obtener la cantidad de provincias distintas de las que tenemos conocimiento de algún proveedor. select count(distinc provincia) from vendedor
Las funciones agregadas las vamos a utilizar en la mayoría de las ocasiones junto con agrupaciones de los datos.
Agrupaciones y selecciones (GROUP BY, HAVING) Podemos realizar consultas en las cuales se realicen agrupaciones de filas de las tablas que utilizamos. La agrupación consiste en formar subconjuntos dentro de una tabla en función de los valores que tengan los datos de las columnas por las que deseemos agrupar. Las agrupaciones se realizan con la cláusula GROUP BY, la cual siempre tiene que ir situada inmediatamente detrás de WHERE, o en el caso que no existiera inmediatamente detrás de FROM. Su sintaxis es: GROUP BY Columna1,...,ColumnaN
Cuando realizamos una agrupación, podríamos decir que se forman subconjuntos dentro de la tablaen la que se encuentra la columna por la que agrupamos, de forma que todas las filas que tengan el mismo valor dentro de la columna por la que agrupamos formarán parte del mismo subconjunto. Así nos encontraremos como resultado tantos subconjuntos como valores diferentes tienen los datos de la columna por la que hemos agrupado. En el caso que agrupemos por varias columnas simultáneamente la explicación anterior se amplía para varias columnas. Un dato muy importante es que cuando utilizamos GROUP BY las columnas que aparecen en la cláusula SELECT pueden ser solamente de dos tipos: columnas agrupadas que aparecen en GROUP BY, o columnas funciones agregadas aplicadas a cualquier otra expresión o columna. Veamos un ejemplo. Imaginemos la tabla Linped (línea de pedido), en la cual encontramos un listado con todas las líneas de todos los pedidos que se han realizado. En cada línea encontramos los datos de la pieza que se ha pedido, que cantidad de pieza que se ha pedido, el número de pedido al que pertenece esa línea, etc. Linped no es más que la tabla detalle de su tabla maestra que es Pedido. Si en Linped tuviéramos los siguientes datos, imaginemos, sólo por el momento, que Linped tiene esta estructura y estos datos: Número pedido
Linea pedido
Número pieza
Cantidad pedida
(numpedido)
(numlinea)
(numpieza)
(cantpedida)
1
1
AAA
20
1
2
BBB
10
Número pedido
Linea pedido
Número pieza
Cantidad pedida
(numpedido)
(numlinea)
(numpieza)
(cantpedida)
1 2
3 1
CCC AAA
15 8
2 3 3
2 1 2
HHH BBB PPP
10 22 10
3
3
AAA
3
Si realizamos un agrupamiento por el número del pedido nos encontraríamos con tres subgrupos: el primero formado por las filas de las tres líneas de pedido del pedido uno, el segundo subgrupo formado por las dos filas de las líneas del segundo pedido, y el tercer y último subgrupo formado por las tres filas de las líneas de pedido que pertenecen al pedido número tres. Si por ejemplo quisiéramos saber el número de líneas que forman cada uno de los pedidos, claramente necesitaremos utilizar COUNT (numlinea), pero si lo utilizamos sin más, es decir, sin realizar ninguna agrupación, lo que obtendremos será el número de líneas de pedido que hay en la tabla, es decir, su número de filas. Necesitamos, sin embargo, que el COUNT haga referencia a las líneas de pedido de cada pedido. Se hace necesario que realicemos una agrupación por el número de pedido y así COUNT nos devolverá el número de líneas de pedido de cada uno de los subconjuntos formados: select numpedido, count(numlinea) from linped group by numpedido
Ya hemos logrado obtener el número de líneas de pedido que forman cada uno de los pedidos. Hemos necesitado agrupar por el número de pedido, por lo tanto, como lo tenemos en el GROUP BY estamos obligados a ponerlo también en el SELECT. Si intentamos mostrar cualquier otra columna de la tabla Linped veremos que se genera un error, puesto que estamos mostrar una columna por la que no estamos agrupando, recordar este detalle puesto que en muchas ocasiones incurrimos en este error. Otro ejemplo. Queremos obtener para cada una de las piezas que hemos pedido en los primeros 100 pedidos la cantidad total de unidades que han pedido de cada una de ellas. select numpieza "Número de la pieza",sum(cantpedida) "Cantidad pedida" from linped where numpedido<=100 group by numpieza order by numpieza
En esta ocasión no hemos tenido que agrupar por el número de pedido, es un dato que nos nos importa ahora, sino que hemos tenido que agrupar por el número de la pieza. Y para obtener la cantidad que se ha pedido de cada una de ellas realizamos la suma de todas las cantidades pedidas por cada una de las piezas. Hemos tenido la precaución de renombrar las columnas y ordenar ascendentemente por el número de la pieza para que quede más claro el resultado de la consulta. Como ya sabemos, disponemos de la cláusula WHERE para imponer condiciones sobre las filas que vamos a mostrar en la consulta, de forma que solamente aquellas filas que cumplan las condiciones marcadas en el WHERE serán mostradas. Los grupos también tienen su propia cláusula para imponer condiciones, la cláusula HAVING. Con HAVING vamos a indicar las condiciones que tienen que cumplir los grupos formados por GROUP BY para que puedan ser mostrados en el resultado de la consulta. HAVING simpre estará situado inmediatamente detrás de GROUP BY. Su sintaxis es: HAVING Condición1,...,CondiciónN
Retomamos los ejemplos anteriores, pero ahora queremos saber el número de líneas que forman cada uno de los pedidos, pero solo de aquellos pedidos en los que se hayan pedido más de cinco unidades entre todas sus piezas. select numpedido, count(numlinea) from linped group by numpedido having min(cantpedida)>5
Y como remate final, vamos a obtener para cada una de las piezas que hemos pedido en los primeros 100 pedidos la cantidad total de unidades que han pedido de cada una de ellas siempre y cuando esa pieza la hayamos pedido más de una vez. select numpieza "Número de la pieza",sum(cantpedida) "Cantidad pedida", count(*) from linped where numpedido<=100 group by numpieza having count(*)>1 order by numpieza
Es la misma consulta que ya habíamos realizado anteriormente sólo que ahora imponemos una condición al subconjunto, y es que exista más de una fila. Si existe más de una fila significa que esa pieza se ha pedido más de una vez, puesto que estamos agrupando por la pieza, por su número, los subgrupos tendrán tantas filas como veces aparezca esa pieza en la tabla de líneas de pedido, o lo que es lo mismo, el subconjunto tendrá tantas filas como veces se haya pedido la pieza.
¿Y en el próximo articulo qué?. Esto toca ya su fin. Ya hemos andado el camino, por esta vez. Hemos vistos muchas cosas. Hemos hecho un recorrido amplio por las consultas en SQL, nos han presentado al famoso por excelencia del lenguaje de consultas estándar, SELECT, y de paso, a unos cuantos amigos suyos sin los cuales no podría hacer nada. Tenemos en nuestras manos las herramientas suficientes para satisfacer la mayoría de los interrogantes que le podemos plantear a los datos de nuestras tablas. Ahora solamente nos resta jugar con estas herramientas y exprimir su posibilidades y su potencia. Recordar que junto con la revistas disponéis de tres ficheros en los que apoyaros para realizar los juegos que antes comentábamos. El primero, con la base de datos que nos ha servido de ejemplo durante todo el artículo y sobre la que hemos basado muchas de las explicaciones, y los otros dos con propuestas de consultas que abarcan todos los apartados que hemos tratado y sus correspondientes soluciones. Como se suele hacer en estos casos, recomendaros que primero intentéis resolver vosotros solos los problemas que se os plantean y después miréis las soluciones, y como siempre, lo haréis al revés, pero en fin, yo también lo haría. En el próximo número continuaremos con SQL embarcándonos esta vez en las consultas anidadas, que se trata de pasarle a una consulta el resultado de otra consulta y que estos datos devueltos por la primera de todas, la subconsulta, sirva a la segunda para satisfacer una serie de condiciones impuestas. Además conoceremos las vistas y los procedimientos almacenados. Todo un mundo nos espera aun. Ha llegado ya el momento de la despedida, como siempre, espero haber sido de ayuda a que conozcáis mejor SQL y os emplazo al próximo número de la revista SINTESIS donde nos volveremos a encontrar. Un saludo, muchas gracias y hasta pronto.
SQL. Vistas y procedimientos. Óscar Javier Segura Amorós
[email protected]
Ya estamos aquí un número más para continuar viendo este maravilloso lenguaje de base de datos que es SQL. Hasta este número, a través de los artículos publicados en los números anteriores de SÍNTESIS, hemos empezado a conocer SQL, su propósito, su estructura, su utilización y sus instrucciones. Con el artículo de hoy llegamos ya al finaldel camino, aunque no terminaremos de andarlo del todo, por si en el futuro queremos seguir paseando por estos paisajes. Hoy vamos a conocer las vistas y los procedimientos almacenados, intentaremos dar una visión amplia de para qué nos pueden servir, y después profundizar ya más en la sintaxis y peculiaridades de ambos. Una buena utilización de las vistas y los procedimientos en el gestor de nuestras bases de datos nos va a aportar una mayor sencillez en la programación de las aplicaciones que van a trabajar con las bases de datos, sobre todo si nos encontramos en estructuras cliente/servidor y si a una misma base de datos se le ataca desde aplicaciones diferentes. Vamos a conseguir optimizar nuestro trabajo. Tenemos que intentar que la mayoría del trabajo que hay que hacer contra la base de datos la realice el gestor de la base de datos en el servidor. No es muy correcto obtener todos los datos del servidor, pasarlos a la aplicación y una vez que los tenemos ya en la aplicación analizarlos, tratarlos y quedarnos solamente con los necesarios, con esta política estamos produciendo una sobrecarga en la red o en el canal de transferencia de datos utilizado y además estamos relentizando el funcionamiento de nuestra aplicación. Hay que intentar distribuir el trabajo entre los diferentes elementos que conforman nuestro sistema informático, haciendo que cada uno de ellos cumpla con los cometidos para los que existe y para los que por tanto está preparado. El gestor de la base de datos tienen la razón de ser, no solamente para mantener la base datos y atender nuestras peticiones de información, sino también para desahogar el funcionamiento de los clientes, y eso lo tenemos que tener muy claro. En el artículo de este número, a través de las vista y los procedimientos vamos a ver como podemos eliminar cierta programación del cliente y situarla sobre el servidor, optimizando así nuestro trabajo y consiguiendo una aplicación más rápida, y más fácil de mantener y acturalizar. Programación referida no solamente al análisis y tratamiento de los datos, sino también a la seguridad e integridad de los mismos.
Vistas Una vista es una consulta almacenada, una estructura SELECT permanente dentro de la base de datos. Permanente desde el momento que se crea hasta el momento que se elimina, si éste último llega a producirse. Tanto la creación como el borrado de la vista depende del administrador de la base de datos, o en su defecto, del usuario de la misma. Los datos que la vista devuelve, es decir, los datos que forman el dominio de la consulta, se van a conformar en lo que podríamos denominar una tabla virtual; tabla , porque vamos a poder trabajar con esos datos como si pertenecieran a una tabla más de nuestra base de datos, pudiendo sobre ellos realizar inserciones, borrados, actualizaciones, incluso consultas, y virtual porque en realidad no es una tabla propiamente dicha de nuestra base de datos aunque podamos trabajar con ella como tal. Las vistas, principalmente las vamos a utilizar para las siguientes funciones:
?
?
?
?
?
?
?
Restringir al usuario a filas concretas de una tabla. Por ejemplo, hacer que un empleado sólo vea las filas que guardan sus datos en una tabla de seguimiento de actividad laboralsin que pueda ver los datos del resto de compañeros. Restringir al usuario a columnas específicas. Por ejemplo, en la tabla de personal donde tenemos todos los datos de cada trabajador mostrar solamente los referentes a nombre, dirección, teléfono y e-mail, no mostrando los datos más sensibles, permitiendo acceder solamente a esos datos a empleados con un grado de responsabilidad y mando superior, haciendo esto posible a través de otra vista o dando los permisos pertinentes para que éste pueda trabajar con la tabla de personal directamente. Combinar columnas de varias tablas de forma que parezcan una sola. Por ejemplo, para usuarios noveles facilitarles el trabajo con las consultas o para no permitir que conozcan plenamente la estructura de la base de datos. Agregar información en lugar de presentar los detalles. Por ejemplo, presentar la suma de una columna, su valor máximo o mínimo en lugar de presentar todos los valores individuales. Dejar de forma permanente hechas las consulta más usuales de los usuarios para que las puedan ejecutar de forma rápida, evitando de esta forma, que tengan que teclear cada días las mismas consultas o recuperarlas de un fichero de scripts de SQL. Filtrar la inserción de nuevos datos en la base de datos o filtrar las modificaciones de los ya existentes de forma que solamente se pueda trabajar con un subconjunto definido de los datos. Por ejemplo que el personal del departamento de publicidad solamente pueda trabajar con los datos de aquellos trabajadores que en la tabla de contratos aparecen contratados para dicho departamento. Por motivos de seguridad. Podemos generar vistas que den acceso a subconjuntos de tablas o columnas de nuestra base de datos y después asignar a cada usuario permisos para poder utilizar determinadas vistas. De esta forma estamos ofreciendo a cada tipo de usuario acceso exclusivamente a los datos que les hacen falta evitando de así que un usuario pueda ver o trabajar con datos que en principio él no tendría que conocer o poder modificar.
Creación de vistas (CREATE VIEW) Crear una vista es tan sencillo como crear la consulta que queremos que almacene dicha vista, anteponiendo la cláusula CREATE VIEW. La sintaxis para la creación es: CREATE VIEW nombre_vista [(columna1, ..., columnaN)] AS SELECT expresión FROM tabla1, ..., tablaN, vista1, ..., vistaN [WHERE expresión] [GROUP BY [ALL] expresión1, ..., expresiónN ] [HAVING condición] [WITH CHECK OPTION]
Una vez que hemos creado la vista, los datos que la consulta devuelve se convierten en una tabla, como anteriormente explicaba, cuyo nombre es el nombre que le hemos asignado a la vista y por lo tanto podemos hacer referencia a ella utilizando el nombre de la misma. Por ejemplo, si quisiéramos ver todos los datos que devuelve la vista, simplemente tenemos que hacer una consulta, y dicha consulta la hacemos como si la hiciéramos para una tabla cualquiera. SELECT * FROM nombre_vista
A continuación profundizaremos en cada apartado de la estructura de la creación de la vista.
?
?
El nombre de la vista debe de cumplir las reglas de los identificadores. Especificar el nombre del propietario de la vista es opcional. Las columnas son los nombres que se van a utilizar para las identificar a las columnas de la vista. Asignar un nombre a una columna sólo es necesario cuando la columna se derive de una expresión aritmética, una función o una constante, cuando dos o más columnas pueden tener el mismo nombre, debido normalmente a una combinación, o cuando la columna de la vista recibe un nombre distinto al de la columna de la que se deriva. Los nombre de las columna las podemos asignar también en la consulta, tras la cláusula SELECT como se hace en las consultas, sin nos resulta más sencillo. Si no especificamos ningún nombre a las columnas, estas adquirirán el nombre de la columna de la que se derivan.
?
?
?
Tras el comando AS escribiremos la consulta que almacenará la vista. En la instrucción SELECT definimos la vista. Podemos utilizar consultas sencilla o complejas, con una tabla o con varias, podemos trabajar también con otras vistas utilizándolas como simples tablas, las únicas restricciones que tenemos, las únicas cosas que no podemos utilizar en las consultas que definen una vista es la utilización de las cláusulas ORDER BY, COMPUTE o COMPUTE BY, la utilización de la palabra clave INTO y hacer referencias a tablas temporales. El argumento WITH CHECK OPTION exige que todas las instrucciones de modificación de datos ejecutadas contra la vista cumplan los criterios establecidos en el apartado SELECT. Si nosotros estipulamos en la consulta una serie de condiciones, en el futuro, a la hora de realizar alguna actualización de los datos, bien sea modificándolos o insertando datos nuevos, no podremos llevarla a cabo si dicha actualización no supera las condiciones establecidas. Es una buena forma de filtrar las modificaciones e inserciones en nuestras tablas, de forma que controlamos sobre que subdominios de datos del dominio original de la tabla el usuario puede trabajar.
Con el argumento WITH CHECK OPTION hay que tener un poco de cuidado. Imaginemos la situación de una empresa donde existen dos administrativos, uno dedicado exclusivamente a las cuentas de grandes clientes y otro dedicado a las cuentas de clientes pequeños y medianos, la clasificación se establece en función del volumen de compra anual que dichos clientes nos realizan. Para que cada uno de los administrativos tenga acceso solamente a los datos de los clientes con los que tienen que trabajar, una solución, es crear dos vistas, una vista trabajará con los clientes cuya compra anual sea superior a una cierta cantidad y la otra vista trabajará con el resto de clientes. De esta forma no tenemos que crear dos tablas de clientes diferentes, la estructura de la base de datos no se tiene que modificar. Si un día el administrativo que se ocupa de los grandes clientes no puede ir a trabajar y hace falta dar de alta a un nuevo “gran cliente”, se puede ocupar el otro administrativo, ¿pero qué ocurrirá?. Pues muy sencillo, cuando el segundo administrativo de alta al nuevo cliente y quiera comprobar sus datos, éste no le aparecerá por ningún sitio, y si intenta volver a darle de alta, el sistema le dirá que ya existe, ¡para volverse loco!, pensará, y como siempre terminará culpando al informático. ¿Qué ha ocurrido?, el alta del nuevo cliente se ha realizado sin ningún problema, ya está introducido en la base de datos, pero como el administrativo trabaja con la tabla de clientes a través de una vista, y esta le muestra solamente un subconjunto de los datos, a él le parece que no ha conseguido introducir al nuevo cliente. Veamos cuales podrían ser las vistas de esta situación. CREATE TABLE CLIENTES(
CODIGO INTEGER NOT NULL, NUM_IDEN_FISCAL VARCHAR(10) NOT NULL, NOMBRE VARCHAR(40), DIRECCIÓN VARCHAR(80), CIUDAD VARCHAR(30), TELEFONO VARCHAR(9), _ANUAL INTEGER, COMPRA CONSTRAINT CP _CLIENTES PRIMARY KEY (CODIGO),
CONSTRAINT CALT _CLIENTES UNIQUE KEY (NUM_IDEM_FISCALN), CONSTRAINT CAJ _CLIENTES FOREING KEY (COMPRA_ANUAL) REFERENCES (PREVISIONES) ) CREATE VIEW VISTA_CLIENTES_GRANDES AS SELECT * FROM CLIENTES WHERE COMPRA_ANUAL >= 10000000 CREATE VIEW VISTA_CLIENTES_PEQUEÑOS AS SELECT * FROM CLIENTES WHERE COMPRA_ANUAL < 10000000
Para evitar la situación que he descrito podríamos haber utilizado el argumento WITH CHECK OPTION. Si creamos las vista utilizando este argumento, estamos obligando a que en el futuro cuando queramos realizar una modificación o una inserción, éstas van a tener que cumplir los criterios establecidos en la vista. En el ejemplo concreto, si la hubiéramos utilizado, el administrativo al intentar dar de alta al cliente no hubiera podido, puesto que el administrativo va a realizar la inserción contra la vista de clientes pequeños por lo que el cliente no va a satisfacer la condición impuesta en el campo de COMPRA_ANUAL. Como no se había establecido el argumento de chequeo, la inserción se puede realizar perfectamente, pero a la hora de comprobar los datos, el nuevo dato no aparecerá, no porque no exista, sino porque la vista no lo tiene que mostrar. En nuestro caso, cuando el administrativo de los clientes grandes se vuelva a incorporar al trabajo y ejecute su vista podrá comprobar que efectivamente su compañero si que había insertado al nuevo cliente, por más que éste le repita que no entiende lo que ha sucedido porque él no conseguía el día anterior verlo y al final le terminará costando el puesto al informático.
Borrado de vistas (DROP VIEW) Para eliminar una vista, o varias, utilizamos la cláusula DROP VIEW, que se usa de una forma tan sencilla como: DROP VIEW nombre_vista1, ... , nombre_vistaN
Cuando borramos la vista, lo único que se elimina es ésta, es decir, las tablas o vistas con las que estaba relacionada permanecen intactas y podemos continuar utilizándolas sin ningún problema. No sucede lo mismo si lo que hacemos es borrar una tabla o vista que era utilizada por otra vista. Cuando se da esta situación nos pueden ocurrir dos cosas, dependiendo del sistema gestor de bases de datos que utilicemos: ?
Si utilizamos SQL Server , por ejemplo, al borrar la tabla, la vista que la utilizaba permanecerá intacta, pero al intentar utilizar la vista, el gestor nos devolverá un mensaje de error indicando que alguna de las tablas que se utilizan en la vista ya no están disponibles, por lo que no podremos trabajar con ella, no tiene fuente de datos de obtener los resultados. Al no haberse borrado la vista, ya que simplemente se queda inutilizada, si volvemos a crear la tabla, podremos volver a utilizar la vista sin ningún problema. Aquí solamente hay que tener la precaución de crear la tabla con el mismo nombre y como mínimo, con los mismos campos que se utilizan en la vista.
?
Si utilizamos Interbase 6 , por ejemplo, al intentar borrar la tabla, el gestor nos devolverá un mensaje de error indicándonos que no podemos borrar la tabla porque hay algún elemento
asociado a ésta, tendremos previamente que borrar la vista, para a continuación poder borrar la tabla. Las dos políticas que se utilizan cuando se da la situación de borrar una tabla que tiene asociada una vista son las descritas en los puntos anteriores. Hemos citado el ejemplo de dos gestores concretos, pero el resto de gestores se acogen a uno de los dos funcionamientos mencionados. Continuando con el ejemplo del apartado anterior, podríamos borrar las dos vistas incluso simultáneamente: DROP VIEW vista_clientes_grandes, vista_clientes_pequeños
Procedimientos almacenados Un procedimientos podríamos decir que es una evolución de las vistas, imaginemos una vista que admitiese parámetros para poder acotar la consulta de forma dinámica, pues ya tenemos un procedimiento. Los procedimientos almacenados de SQL son similares a los procedimientos de otros lenguajes de programación en el sentido de que puede: ?
?
?
Aceptar parámetros de entrada y devolver varios valores en forma de parámetros de salida. Contener instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos. Devolver un valor de estado para indicar si la operación se ha realizado correctamente o ha habido algún error.
Los procedimientos almacenados tienen ventajas e inconvenientes, como todo en esta vida. Las ventajas: ?
?
?
?
?
Ayudan a mantener la consistencia de la base de datos. Si dejamos al usuario que campe a sus anchas con las instrucciones de actualización, UPDATE, INSERT y DELETE, corremos el riesgo que no siempre se cumplan las reglas de consistencia. Una forma de evitarlo es trabajando las actualizaciones a través de procedimientos, puesto que tenemos un control de cómo se va a realizar ese proceso. Reducen el tráfico en la red. Los procedimientos se ejecutan en el servidor por lo que no tenemos que recibir tablas enteras de datos para realizar después nuestros análisis, el análisis de los datos se ha realizado ya en el servidor y nosotros recibimos simplemente los datos que necesitamos. Ahorramos tiempo de desarrollo. Es normal que en una estructura cliente-servidor varias aplicaciones trabajen con la misma base de datos Si programamos en la propia base de datos las reglas de consistencia nos ahorramos tener que repetir la misma programación en las distintas aplicaciones, con lo que también disminuimos las posibilidades de errores u olvidos de reglas. Si creamos procedimientos almacenados que son utilizados por una o varias aplicaciones, es más fácil, llegado el momento, cambiar la programación del procedimiento que cambiar la programación de la totalidad de las aplicaciones. Ejecución más rápida puesto que el procedimiento es compilado y optimizado en el momento de su creación y después podemos utilizarlo ya directamente tantas veces como queramos. Si por el contrario utilizamos lotes de comandos de SQL, cada vez que los utilizamos se tienen que compilar y optimizar.
Creación de procedimientos (CREATE PROCEDURE) Para la creación de un procedimiento utilizamos la cláusula CREATE PROCEDURE, pero dependiendo del gestor que vayamos a utilizar encontramos unas pequeñas diferencias. Vamos a ver la sintaxis para SQL Server e Interbase, por coger dos de los más utilizados. Para SQL Server CREATE PROCEDURE nombre_procedimiento
[@variable1 tipoDato, ... , @variableN tipoDato [OUTPUT]] AS
Instrucciones SQL Para Interbase CREATE PROCEDURE nombre_procedimiento
[ (variable_entrada1 tipoDato, ..., variable_entradaN tipoDato)] [ RETURNS (variable_salida1 tipoDato, ..., varialbe_salidaN tipoDato) ] AS [ DECLARE VARIABLE variable_local1 tipoDato; DECLARE VARIABLE variable_localN tipoDato; ] BEGIN
Instrucciones SQL END
El nombre_procedimiento es el nombre del nuevo procedimiento almacenado, utilizaremos este nombre cada vez que queramos referirnos a él, bien sea para ejecutarlo o para borrarlo. Los nombre de procedimientos deben seguir las reglas de los identificadores y deben de ser únicos en la base de datos. Las variables, son los parámetros del procedimiento. Cuando creamos un procedimiento podemos declarar una o varias variables y cuando ejecutamos e l procedimiento el usuario debe de asignar los valores correspondientes a dichas variables y en el orden en las que estas han sido declaradas. En SQL Server el nombre de las variables siempre empieza por el signo @, no siendo así en Interbase. Las variables son locales al procedimiento por lo que podemos declarar variables con el mismo nombre en procedimientos diferentes. El tipoDato en la declaración de una variable es el dominio de dicha variable. Los procedimientos pueden devolver valores a la ejecución del mismo. La devolución de estos valores, que no serán más que variables, se deben de indicar en el momento de la declaración de las mismas. Aquí encontramos otra diferencia de sintaxis entre gestores, en SQL Server se debe de utiliza la cláusula OUTPUT al lado de la variable que queremos que retorne su valor, y en Interbase, este tipo de variables deben de declararse separadas de las variables de entrada y después de la cláusula RETURNS. Salvo las diferencias sintácticas es exactamente lo mismo.
AS son las acciones que va a llevar a cabo el procedimiento. DECLARE VARIABLE, nos sirve para poder declarar variables locales para utilizar en el bloque de instrucciones SQL. son las instrucciones de SQL que se incluirán en el procedimiento. Aquí existe alguna limitación, no todas las cláusulas de SQL tienen cabida aquí, depende de cada gestor de bases de datos exactamente que instrucciones son aceptadas y cuales no y en qué forma se aceptan. Instrucciones SQL
De forma predeterminada, los parámetros admiten el valor NULL. Si se pasa una valor de parámetro como NULL y ese parámetro se utiliza en una instrucción CREATE o ALTER TABLE que hacer referencia a una columna que no admite valores NULL, se generará un error.
Principales instrucciones SQL permitidas en los procedimientos A continuación vamos a comentar las principales instrucciones de SQL que son soportadas en los procedimientos. Como ya hemos podido comprobar anteriormente en la creación del procedimiento, pueden existir diferencias entre los distintos gestores de bases de datos, yo voy a centrarme en Interbase, aunque las diferencias con otros gestores en la mayoría de las instrucciones es nula o mínima. ?
Asignaciones a las variables de expresiones. Las variables serán cualquiera de las declaradas en la creación del procedimiento. Variable = Expresion
?
Llamadas a procedimientos, con la precaución de que no se admiten expresiones en los parámetros execute procedure nombre_procedimiento [ parametros de entrada ] [ returning_values parametros de salida ]
?
Condicionales if (condición) then instrucción [ else instrucción ]
?
Bucles controlados por condición while (condición) do instrucción
?
Instrucciones SQL. Podemos incluir cualquier condición de manipulación de datos. Estas instrucciones pueden utilizar variables locales y parámetros, siempre que éstas estén precedidas de dos puntos “:”, para que se puedan diferenciar las variables y parámetros de los nombres de columnas. update nominas set monto = monto * :plus where comisiones > 10000;
Se pueden utilizar instrucciones select siempre y cuando devuelvan una sola fila, para instrucciones más generales utilizaremos la instrucción for. Si queremos transferir valores a variables o parámetros lo haremos con la cláusula into. select nombre from clientes where codigo = 23445 into :nombre_empresa; ?
Iteración sobre consultas. Recorremos el conjunto de filas definido por la instrucción select. Para cada fila, transfiere los valores a las variables de la cláusula into, de forma similar a lo que sucede con las selecciones únicas, y ejecuta entonces la instrucción de la sección do. for instruccion_select into variables do instrucción
?
Instrucciones de control. La instrucción exit termina la ejecución del procedimiento actual. La instrucción suspend, se utiliza con procedimientos que devuelven un conjunto de filas a la rutina que lo ha llamado, suspende temporalmente el procedimiento, hasta que la rutina que lo llama haya procesado los valores retornados. exit; suspend;
Borrado de procedimientos (DROP PROCEDURES) Para eliminar uno o más procedimientos almacenados utilizamos la cláusula DROP PROCEDURE: DROP PROCEDURE procedimiento1, ... , procedimientoN
Ejecución de procedimientos (EXECUTE) Para ejecutar un procedimiento..., así de sencillo: EXECUTE PROCEDURE nombre_procedimiento [parámetro1, ... , parámetroN]
Si no tenemos muchas ganas de escribir, en lugar de EXCUTE podemos utilizar EXEC y a continuación los valores que queremos darle a los parámetros. Recordar que los valores de los parámetros tienen que pasarse al procedimiento en el mismo orden en el que han sido definidos en el momento de la creación del mismo.
El carácter de terminación (SET TERM) Si vamos a introducir los procedimientos almacenados en la base de datos a través de un fichero script es necesario que utilicemos un carácter de terminación para que la aplicación que vayamos a utilizar para tal fin sepa diferenciar cuando termina un bloque de instrucciones y comienza otro. El carácter de terminación lo establecemos nosotros a través de la cláusula SET TERM. SET TERM terminador
El carácter terminador por defecto es el punto y coma “;”, ahora bien, en los procedimientos tenemos que introducir los bloques de instrucciones SQL, por lo que aparecerán caracteres de terminación que pertenecen a los diferentes apartados del bloque de instrucciones pero no indican que ha terminado el procedimiento. Sin embargo la aplicación que utilicemos para introducir el script tomará estos terminadores como marcas de que ha terminado el procedimiento almacenado. Este es el motivo de tener que cambiar el carácter de terminación. Como carácter terminador tendremos que escoger uno que no vayamos a utilizar en el resto del script, normalmente se utiliza el acento circunflejo “^”. SET TERM ^ ;
Como el cambiar el carácter de terminación también es una instrucción de SQL, tendremos que ponerle el punto y coma “;”, señal de que termina. De ahora en adelante, hasta que lo volvamos a cambiar, cada instrucción que escribamos irá finalizada por “^”. Para reponer todo a su sitio y dejarlo como ha sido siempre tendremos que volver a cambiar el carácter de terminación. SET TERM ; ^
Fin (THE END) Ya hemos dado un paseo por las vistas y los procedimientos almacenados. Espero que compartáis la idea de la distribución del trabajo que apuntaba en la introducción del artículo, y que al igual que yo,penséis que
