Plantilla estilo manual de usuario
Contents
1. CORE commons Reconocimiento NoComercial Compartirigual 2 5 Espa a Usted es libre de copiar distribuir y comunicar p blicamente la obra hacer obras derivadas Bajo las condiciones siguientes Reconocimiento Debe reconocer los cr ditos de la obra de la manera especificada por el autor o el licenciador pero no de una manera que sugiera que tiene su apoyo o apoyan el uso que hace de su obra No comercial No puede utilizar esta obra para fines comerciales Compartir bajo la misma licencia Si altera o transforma esta obra o genera una obra derivada s lo puede distribuir la obra generada bajo una licencia id ntica a sta e Al reutilizar o distribuir la obra tiene que dejar bien claro los t rminos de la licencia de esta obra e Alguna de estas condiciones puede no aplicarse si se obtiene el permiso del titular de los derechos de autor e Apart from the remix rights granted under this license nothing in this license impairs or restricts the authors moral rights Los derechos derivados de usos leg timos u otras limitaciones reconocidas por ley no se ven afectados por lo anterior Esto es un resumen legible por humanos del texto legal la licencia completa disponible en los idiomas siguientes Para ver una copia completa de la licencia acudir a la direcci n http creativecommons org licenses by nc sa 2 5 es legalcode es 3 esquema de la unidad 4 1 consultas de datos con SOL DOL 6 4 1 1 capacid2. 5 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas 4 1 consultas de datos con SOL DOL DQL es la abreviatura del Data Query Language lenguaje de consulta de datos de SQL El Unico comando que pertenece a este lenguaje es el versatil comando SELECT Este comando permite Obtener datos de ciertas columnas de una tabla proyecci n Obtener registros filas de una tabla de acuerdo con ciertos criterios selecci n Mezclar datos de tablas diferentes asociaci n join Realizar c lculos sobre los datos Agrupar datos El asterisco significa que se seleccionan todas las columnas DISTINCT Hace que no se muestren los valores duplicados columna Es el nombre de una columna de la tabla que se desea mostrar expresi n Una expresi n v lida SQL alias Es un nombre que se le da a la cabecera de la columna en el resultado de esta instrucci n Ejemplos Selecci n de todos los registros de la tabla clientes SELECT FROM Clientes Selecci n de algunos campos SELECT nombre apellido1 apellido2 FROM Clientes 6 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net aritm ticos Los operadores suma resta multiplicaci n y divisi n se pueden utilizar para hacer c lculos en las consultas Cuando se utilizan como expresi n en una consulta SELECT no modifican los datos originales si
3. FROM piezas NATURAL JOIN existencias En ese ejemplo se obtienen los registros de piezas relacionados en existencias a trav s de los campos que tengan el mismo nombre en ambas tablas 25 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas JOIN USING Permite establecer relaciones indicando qu columna o columnas comun a las dos tablas hay que utilizar SELECT FROM piezas JOIN existencias USING tipo modelo Las columnas deben de tener exactamente el mismo nombre en ambas tablas JOIN ON Permite establecer relaciones cuya condici n se establece manualmente lo que permite realizar asociaciones m s complejas o bien asociaciones cuyos campos en las tablas no tienen el mismo nombre SELECT FROM piezas JOIN existencias ON piezas tipo existencias tipo AND piezas modelo existencias modelo relaciones externas La ltima posibilidad es obtener relaciones laterales o externas outer join Para ello se utiliza la sintaxis SELECT FROM piezas LEFT OUTER JOIN existencias ON piezas tipo existencias tipo AND piezas modelo existencias modelo En este consulta adem s de las relacionadas aparecen los datos de los registros de la tabla piezas que no est n en existencias Si el LEFT lo cambiamos por un RIGHT aparecer n las existencias no presentes en la tabla piezas adem s de las relacionadas en ambas tablas La condici n FULL OUTER JOIN producir a un resultado en el que apar
4. lt AAA eri 9 AR 15 0 EOS 8 6 AR 2 Bl 10 Bl 3 Es decir es un resumen de los datos anteriores Los datos n_almacen y cantidad no est n disponibles directamente ya que son distintos en los registros del mismo grupo S lo se pueden utilizar desde funciones como se ver ahora Es decir esta consulta es err nea SELECT tipo modelo cantidad FROM existencias GROUP BY tipo modelo SELECT tipo modelo cantidad ERROR en linea 1 ORA 00979 no es una expresi n GROUP BY Lo interesante de la creaci n de grupos es las posibilidades de c lculo que ofrece Para ello se utilizan funciones que permiten trabajar con los registros de un grupo son Funci n Significado Cuenta los elementos de un grupo Se utiliza el asterisco para no tener que indicar un nombre de columna concreto el resultado es el mismo para cualquier columna Suma los valores de la expresi n 28 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net Funci n Significado Calcula la media aritm tica sobre la expresi n indicada M nimo valor que toma la expresi n indicada M ximo valor que toma la expresi n indicada Calcula la desviaci n est ndar Calcula la varianza Todas las funciones de la tabla anterior se calculan para cada elemento del grupo asi la expresi n SELECT tipo modelo cantidad SUM Cantidad FROM existenci
5. WHERE a sueldo BETWEEN b sueldo_minimo AND b sueldo_maximo 24 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net sintaxis SOL 1999 En la versi n SQL de 1999 se ide una nueva sintaxis para consultar varias tablas La raz n fue separar las condiciones de asociaci n respecto de las condiciones de selecci n de registros Oracle incorpora totalmente esta normativa La sintaxis completa es SELECT tabla1 columna1 tabli columna2 tabla2 columna1 tabla2 columna2 FROM tabla1 CROSS JOIN tabla2 NATURAL JOIN tabla2 JOIN tabla2 USING columna JOIN tabla2 ON tabla1 columa tabla2 columna LEFT RIGHT FULL OUTER JOIN tabla2 ON tabla1 columa tabla2 columna Se describen sus posibilidades en los siguientes apartados CRO JOIN Utilizando la opci n CROSS JOIN se realiza un producto cruzado entre las tablas indicadas Eso significa que cada tupla de la primera tabla se combina con cada tupla de la segunda tabla Es decir si la primera tabla tiene 10 filas y la segunda otras 10 como resultado se obtienen 100 filas resultado de combinar todas entre s Ejemplo SELECT FROM piezas CROSS JOIN existencias No es una operaci n muy utilizada aunque posibilita resolver consultas extremadamente complicadas NATURAL JOIN Establece una relaci n de igualdad entre las tablas a trav s de los campos que tengan el mismo nombre en ambas tablas SELECT
6. Posici n del n mero muestra ceros Formato d lar L S mbolo local de la moneda S Hace que aparezca el s mbolo del signo D Posici n del s mbolo decimal en espa ol la coma G Posici n del separador de grupo en espa ol el punto TO_NUMBER Convierte textos en n meros Se indica el formato de la conversi n utilizando los mismos s mbolos que los comentados anteriormente TO_DATE Convierte textos en fechas Como segundo par metro se utilizan los c digos de formato de fechas comentados anteriormente CAST Funcion muy versatil que permite convertir el resultado a un tipo concreto Sintaxis CAST expresi n AS tipoDatos Ejemplo SELECT CAST 2 34567 AS NUMBER 7 6 FROM DUAL Lo interesante es que puede convertir de un tipo a otro Por ejemplo imaginemos que tenemos una columna en una tabla mal planteada en la que el precio de las cosas se ha escrito en Euros Los datos son se muestra s lo la columna precio PRECIO 25 2 2 8 123 65 18 123 21 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas PRECIO 20 102 5 Imaginemos que queremos doblar el precio no podremos porque la columna es de tipo texto por ello debemos tomar s lo la parte num rica y convertirla a n mero despu s podremos mostrar los precios multiplicados por dos SELECT 2 CAST SUBSTR precio 1 INSTR precio 2 AS NUMBER FROM
7. WHERE para indicar las filas que se modificar n Esta cl usula se puede utilizar con las mismas posibilidades que en el caso del SELECT por lo que es posible utilizar subconsultas Por ejemplo UPDATE empleados SET sueldo sueldo 1 10 WHERE id_seccion SELECT id_seccion FROM secciones WHERE nom_seccion Producci n Esta instrucci n aumenta un 10 el sueldo de los empleados de la secci n llamada Producci n Tambi n podemos utilizar subconsultas en la cl usula SET de la instrucci n UPDATE Ejemplo UPDATE empleados SET puesto_trabajo SELECT puesto_trabajo FROM empleados WHERE id_empleado 12 WHERE seccion 23 Esta instrucci n coloca a todos los empleados de la secci n 23 el mismo puesto de trabajo que el empleado n mero 12 Este tipo de actualizaciones s lo son v lidas si el subselect devuelve un nico valor que adem s debe de ser compatible con la columna que se actualiza Hay que tener en cuenta que las actualizaciones no pueden saltarse las reglas de integridad que posean las tablas gubconsultas en la instrucci n DELETE Al igual que en el caso de las instrucciones INSERT o SELECT DELETE dispone de cl usula WHERE y en dicha cl usulas podemos utilizar subconsultas Por ejemplo DELETE empleados WHERE id_empleado IN SELECT id_empleado FROM errores_graves En este caso se trata de una subconsulta creada con el operador IN se eliminar n los empleados cuyo identificador est dentro de la tab
8. cambiar los caracteresSustitutivos caracteresSustitutivos son los caracteres que reemplazan a los anteriores De tal modo que el primer car cter a cambiar se cambia por el primer car cter sustitutivo el segundo por el segundo y as sucesivamente Ejemplo SELECT TRANSLATE prueba ue wx FROM DUAL El resultado ser a el texto prwxba de tal forma que la u se cambia por la w y la e por la x caracteresACambiar Si la segunda cadena es m s corta los caracteres de la primera que no encuentran sustituto se eliminan Ejemplo SELECT TRANSLATE prueba ue w FROM DUAL Da como resultado prwba LPAD texto anchuraM xima Rellena el texto a la izquierda LPAD o a la derecha RPAD con el car cter indicado para caracterDeRelleno ocupar la anchura indicada RPAD texto anchuraMaxima Si el texto es m s grande que la anchura caracterDeRelleno indicada el texto se recorta Si no se indica car cter de relleno se rellenar el espacio marcado con espacios en blanco Ejemplo LPAD Hola 10 da como resultado REVERSE texto Invierte el texto le da la vuelta 15 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas otras funciones de caracteres Funci n Descripci n ASCII car cter Devuelve el c digo ASCII del car cter indicado CHR n mero Devuelve el car cter correspondiente al c digo ASCII indicado SOU
9. lenguaje SQL Il Consultas El resultado ser a AR 6 AR 6 AR 7 AR 7 AR 8 AR 8 AR 9 AR 9 AR 12 AR 12 AR 15 AR 15 AR 20 AR 20 AR 21 AR 21 BI 10 BI 10 BI 20 BI 20 BI 22 BI 22 BI 24 BI 24 En la mayor a de bases de datos la funci n CONCAT se describe m s adelante realiza la misma funci n Se pueden realizar consultas que restrinjan los datos de salida de las tablas Para ello se utiliza la cl usula WHERE Esta cl usula permite colocar una condici n que han de cumplir todos los registros los que no la cumplan no aparecen en el resultado Ejemplo SELECT Tipo Modelo FROM Pieza WHERE Precio gt 3 operadores de comparaci n Se pueden utilizar en la cl usula WHERE son Operador Significado gt Mayor que lt Menor que gt Mayor o igual que lt Menor o igual que 8 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net Operador Significado Igual lt gt Distinto l Distinto Se pueden utilizar tanto para comparar n meros como para comparar textos y fechas En el caso de los textos las comparaciones se hacen en orden alfab tico S lo que es un orden alfab tico estricto Es decir el orden de los caracteres en la tabla de c digos En muchas bases de datos hay problemas con la y otros s mbolos nacionales en especial al ordenar o comparar con el signo de mayor o menor ya que la el o
10. mayoria de funciones precisan que se les envie datos de entrada parametros o argumentos que son necesarios para realizar el calculo de la funci n Este resultado l gicamente depende de los par metros enviados Dichos par metros se pasan entre par ntesis De tal manera que la forma de invocar a una funci n es nombreFunci n par metro1 par metro2 1 Si una funci n no precisa par metros como SYSDATE no hace falta colocar los par ntesis En realidad hay dos tipos de funciones Funciones que operan con datos de la misma fila Funciones que operan con datos de varias filas diferentes funciones de agrupaci n En este apartado se tratan las funciones del primer tipo m s adelante se comentan las de agrupaci n Nota tabla DUAL Oracle En los siguientes apartados se describen algunas de las funciones m s interesantes las m s importantes son las remarcadas con un fondo naranja m s intenso 12 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net funciones num ricas redondeos ROUND n decimales Redondea el n mero al siguiente n mero con el n mero de decimales indicado m s cercano ROUND 8 239 2 devuelve 8 3 TRUNC n decimales Los decimales del n mero se cortan para que s lo aparezca el n mero de decimales indicado matem ticas MOD n1 n2 aa el resto resultado de divi
11. tabla normal La creaci n de la vista del ejemplo es compleja ya que hay relaciones complicadas pero una vez creada la vista se le pueden hacer consultas como si se tratara de una tabla normal Incluso se puede utilizar el comando DESCRIBE sobre la vista para mostrar la estructura de los campos que forman la vista o utilizarse como subconsulta en los comandos UPDATE o DELETE mostrar la lista de vistas La vista del diccionario de datos de Oracle USER_VIEWS permite mostrar una lista de todas las vistas que posee el usuario actual Es decir para saber qu vistas hay disponibles se usa SELECT FROM USER_VIEWS La columna TEXT de esa vista contiene la sentencia SQL que se utiliz para crear la vista sentencia que es ejecutada cada vez que se invoca a la vista 37 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas Se utiliza el comando DROP VIEW DROP VIEW nombreDeVista 38
12. CT tipo modelo precio FROM piezas WHERE precio IN 3 5 8 Obtiene piezas cuyos precios sean 3 5 u 8 no valen ni el precio 4 ni el 6 por ejemplo Se usa sobre todo con textos permite obtener registros cuyo valor en un campo cumpla una condicion textual LIKE utiliza una cadena que puede contener estos simbolos Simbolo Significado Una serie cualquiera de caracteres _ Un caracter cualquiera Ejemplos Selecciona nombres que empiecen por S SELECT nombre FROM personas WHERE nombre LIKE S Selecciona las personas cuyo apellido sea Sanchez Senchez Stnchez SELECT apellido1 FROM Personas WHERE apellido1 LIKE S_nchez 4 3 6 I NULL Devuelve verdadero si el valor que examina es nulo SELECT nombre apellidos FROM personas WHERE telefono IS NULL Esa instrucci n selecciona a la gente que no tiene tel fono Se puede usar la expresion IS NOT NULL que devuelve verdadero en el caso contrario cuando la expresi n no es nula 10 2 curso de administraci n de sistemas inform ticos autor Jorge Sanchez www jorgesanchez net precedencia de operadores A veces las expresiones que se producen en los SELECT son muy extensas y es dificil saber que parte de la expresi n se eval a primero por ello se indica la siguiente tabla de precedencia tomada de Oracle Orden de precedencia Multiplicar dividir Suma Resta Concatenaci n Comparacione
13. MINUS SELECT Primero se hace la uni n y luego la diferencia A pesar del poco ilustrativo t tulo de este apartado la idea es sencilla Se trata de c mo utilizar instrucciones SELECT dentro de las instrucciones DML INSERT DELETE o UPDATE ello permite dar m s potencia a dichas instrucciones relleno de registros a partir de filas de una consulta Hay un tipo de consulta llamada de adici n de datos que permite rellenar datos de una tabla copiando el resultado de una consulta Se hace mediante la instrucci n INSERT y en definitiva permite copiar datos de una consulta a otra Ese relleno se basa en una consulta SELECT que poseer los datos a a adir L gicamente el orden de esos campos debe de coincidir con la lista de campos indicada en la instrucci n INSERT Sintaxis INSERT INTO tabla campo1 campo2 SELECT campoCompatibleCampo1 campoCompatibleCampo2 FROM lista DeTablas otras cl usulas del SELECT Ejemplo INSERT INTO clientes2004 dni O SELECT dni nombre localidad direcci n FROM clientes WHERE problemas 0 L gicamente las columnas del SELECT se tienen que corresponder con las columnas a rellenar mediante INSERT observar las flechas 34 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net subconsultas en la instrucci n UPDATE La instrucci n UPDATE permite modificar filas Es muy habitual el uso de la cl usula
14. NDEX texto Devuelve el valor fon tico del texto Es una funci n muy interesante para buscar textos de los que se no se sabe con exactitud su escritura Por ejemplo SELECT FROM personas WHERE SOUNDEX apellido1 SOUNDEX Smith En el ejemplo se busca a las personas cuyo primer apellido suena como Smith funciones de trabajo con nulos Permiten definir valores a utilizar en el caso de que las expresiones tomen el valor nulo NVL valor sustituto Si el valor es NULL devuelve el valor sustituto de otro modo devuelve valor NVL2 valor sustituto 1 Variante de la anterior devuelve el valor sustituto2 sustituto1 si valor no es nulo Si valor es nulo devuelve el sustituto2 16 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net Funci n Descripci n COALESCE listaExpresiones Devuelve la primera de las expresiones que no es nula Ejemplo CREATE TABLE test coll VARCHAR2 1 col2 VARCHAR2 1 col3 VARCHAR2 1 INSERT INTO test VALUES NULL B C INSERT INTO test VALUES A NULL C INSERT INTO test VALUES NULL NULL C INSERT INTO test VALUES A B C SELECT COALESCE col1 col2 col3 FROM test El resultado es B A C A Devuelve nulo si valor1 es igual a valor2 De otro modo devuelve valor1 NULLIF valor1 valor2 funciones de fecha y manejo de fechas e intervalos Las fe
15. adas utilizando WHERE Esta cl usula eliminar columnas en base a la condici n indicada 2 Se establecen los grupos indicados en la cl usula GROUP BY 3 Se calculan los valores de las funciones de totales COUNT SUM AVG 4 Se filtran los registros que cumplen la cl usula HAVING 3 El resultado se ordena en base al apartado ORDER BY uso de subconsultas simples Se trata de una t cnica que permite utilizar el resultado de una tabla SELECT en otra consulta SELECT Permite solucionar consultas que requieren para funcionar el resultado previo de otra consulta La sintaxis es SELECT listaExpresiones FROM tabla WHERE expresi n OPERADOR SELECT listaExpresiones FROM tabla 30 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net Se puede colocar el SELECT dentro de las cl usulas WHERE HAVING o FROM El operador puede ser gt lt gt lt 0 IN Ejemplo SELECT nombre_empleado paga FROM em pleados SELECT paga FROM empleados WHERE nombre _empleado Martina gt L gicamente el resultado de la subconsulta debe incluir el campo que estamos analizando Se pueden realizar esas subconsultas las veces que haga falta SELECT nombre_empleado paga FROM em pleados SELECT paga FROM empleadosWHERE nombre_empleado Luis En realidad lo primero que hace la base de datos es calcular el resultado de la subconsulta SELECT nomb
16. ades 6 4 1 2 sintaxis sencilla del comando SELECT 6 4 2 c lculos 7 4 2 1 aritm ticos 7 4 2 2 concatenaci n de textos 7 4 3 condiciones 8 4 3 1 operadores de comparaci n 8 4 3 2 valores l gicos 9 4 3 3 BETWEEN 9 4 3 4 IN 10 4 3 5 LIKE 10 4 3 6 IS NULL 10 4 3 7 precedencia de operadores 1 4 4 ordenaci n 11 4 5 funciones 12 4 5 1 funciones 12 4 5 2 funciones num ricas 13 4 5 3 funciones de caracteres 14 4 5 4 funciones de trabajo con nulos 16 4 5 5 funciones de fecha y manejo de fechas 17 4 5 6 funciones de conversi n 19 4 5 7 funci n DECODE 22 4 6 obtener datos de m ltiples tablas 23 4 6 1 producto cruzado o cartesiano de tablas 23 4 6 2 asociando tablas 23 4 6 3 relaciones sin igualdad 24 4 6 4 sintaxis SOL 1999 25 4 7 agrupaciones 27 4 7 1 funciones de c lculo con grupos 28 4 7 2 condiciones HAVING 29 4 8 subconsultas 30 4 8 1 uso de subconsultas simples 30 4 8 2 uso de subconsultas de m ltiples filas 32 4 9 combinaciones especiales 33 4 9 1 uniones 33 4 9 2 intersecciones 33 4 9 3 diferencia 33 4 10 DQL en instrucciones DML 34 4 10 1 relleno de registros a partir de filas de una consulta 34 4 10 2 subconsultas en la instrucci n UPDATE 35 4 10 3 subconsultas en la instrucci n DELETE 35 4 11 vistas QA a 4 11 1 introducci n 36 4 11 2 creaci n de vistas 36 4 11 3 mostrar la lista de vistas 37 4 11 4 borrar vistas 38
17. as GROUP BY tipo modelo Obtiene este resultado ao GAMBA I a 10530 eS a Se Se suman las cantidades para cada grupo A veces se desea restringir el resultado de una expresi n agrupada por ejemplo con SELECT tipo modelo cantidad SUM Cantidad FROM existencias WHERE SUM Cantidad gt 500 GROUP BY tipo modelo Pero Oracle devolver a este error WHERE SUM Cantidad gt 500 ERROR en linea 3 ORA 00934 funci n de grupo no permitida aqui 29 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas La razon es que Oracle calcula primero el WHERE y luego los grupos por lo que esa condicion no la puede realizar al no estar establecidos los grupos Por ello se utiliza la clausula HAVING que se ejecuta una vez realizados los grupos Se usaria de esta forma SELECT tipo modelo cantidad SUM Cantidad FROM existencias GROUP BY tipo modelo HAVING SUM Cantidad gt 500 Eso no implica que no se pueda usar WHERE sta expresi n s es v lida SELECT tipo modelo cantidad SUM Cantidad FROM existencias WHERE tipo AR GROUP BY tipo modelo HAVING SUM Cantidad gt 500 En definitiva el orden de ejecuci n de la consulta marca lo que se puede utilizar con WHERE y lo que se puede utilizar con HAVING Para evitar problemas estos podr an ser los pasos en la ejecuci n de una instrucci n de agrupaci n por parte del gestor de bases de datos 1 Seleccionar las filas dese
18. cado ANY Compara con cualquier registro de la subconsulta La instrucci n es v lida si hay un registro en la subconsulta que permite que la comparacion sea cierta ALL Compara con todos los registros de la consulta La instrucci n resulta cierta si es cierta toda comparaci n con los registros de la subconsulta IN No usa comparador ya que sirve para comprobar si un valor se encuentra en el resultado de la subconsulta NOT IN Comprueba si un valor no se encuentra en una subconsulta Ejemplo SELECT nombre sueldo FROM empleados WHERE sueldo gt ALL SELECT sueldo FROM empleados La consulta anterior obtiene el empleado que m s cobra Otro ejemplo SELECT nombre FROM empleados WHERE dni IN SELECT dni FROM directivos En ese caso se obtienen los nombres de los empleados cuyos dni est n en la tabla de directivos Si se necesita comprobar dos columnas en una consulta IN se hace SELECT nombre FROM empleados WHERE cod1 cod2 IN SELECT cod1 cod2 FROM directivos 32 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net La palabra UNION permite anadir el resultado de un SELECT a otro SELECT Para ello ambas instrucciones tienen que utilizar el mismo numero y tipo de columnas Ejemplo SELECT nombre FROM provincias UNION SELECT nombre FROM comunidades El resultado es una tabla que contendra nombres de provincia y de comunidades Es
19. chas se utilizan muchisimo en todas las bases de datos Oracle proporciona dos tipos de datos para manejar fechas los tipos DATE y TIMESTAMP En el primer caso se almacena una fecha concreta que incluso puede contener la hora en el segundo caso se almacena un instante de tiempo mas concreto que puede incluir incluso fracciones de segundo Hay que tener en cuenta que a los valores de tipo fecha se les pueden sumar numeros y se entenderia que esta suma es de dias Si tiene decimales entonces se suman dias horas minutos y segundos La diferencia entre dos fechas tambi n obtiene un numero de dias intervalos Los intervalos son datos relacionados con las fechas en si pero que no son fechas Hay dos tipos de intervalos el INTERVAL DAY TO SECOND que sirve para representar dias horas minutos y segundos y el INTERVAL YEAR TO MONTH que representa a os y meses 1 Ejemplo tomado de http www psoug org reference string_func html 17 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas Para los intervalos de ano a mes los valores se pueden indicar de estas formas 123 anos y seis meses INTERVAL 123 6 YEAR 4 TO MONTH 123 a os INTERVAL 123 YEAR 4 TO MONTH 6 meses INTERVAL 6 MONTH 3 TO MONTH La precision en el caso de indicar tanto a os como meses se indica s lo en el ano En intervalos de dias a segundos los intervalos se pueden indicar como 4 dias 10 horas 12 minuto
20. decir UNION crea una sola tabla con registros que est n presentes en cualquiera de las consultas Si est n repetidas s lo aparecen una vez para mostrar los duplicados se utiliza UNION ALL en lugar de la palabra UNION Es muy importante se alar que tanto sta cl usula como el resto de combinaciones especiales requieren en los dos SELECT que unen el mismo tipo de columnas y en el mismo orden De la misma forma la palabra INTERSECT permite unir dos consultas SELECT de modo que el resultado ser n las filas que est n presentes en ambas consultas Ejemplo tipos y modelos de piezas que se encuentren s lo en los almacenes 1y2 SELECT tipo modelo FROM existencias WHERE n_almacen 1 INTERSECT SELECT tipo modelo FROM existencias WHERE n_almacen 2 Con MINUS tambi n se combinan dos consultas SELECT de forma que apareceran los registros del primer SELECT que no est n presentes en el segundo Ejemplo tipos y modelos de piezas que se encuentren el almac n 1 y no en el 2 SELECT tipo modelo FROM existencias WHERE n_almacen 1 MINUS SELECT tipo modelo FROM existencias WHERE n_almacen 2 33 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas Se podr an hacer varias combinaciones anidadas una uni n cuyo resultado se intersectar con otro SELECT por ejemplo en ese caso es conveniente utilizar par ntesis para indicar qu combinaci n se hace primero SELECT UNION SELECT
21. dir n1 entre n POWER valor exponente Eleva el valor al exponente indicado SQRT n Calcula la raiz cuadrada de n SIGN n Devuelve 1 si n es positivo cero si vale cero y 1 si es negativo ABS n Calcula el valor absoluto de n EXP n Calcula en es decir el exponente en base e del numero n LN n Logaritmo neperiano de n LOG n Logaritmo en base 10 de n SIN n Calcula el seno de n n tiene que estar en radianes COS n Calcula el coseno de n n tiene que estar en radianes TAN n Calcula la tangente de n n tiene que estar en radianes ACOS n Devuelve en radianes el arco coseno de n ASIN n Devuelve en radianes el arco seno de n ATAN n Devuelve en radianes el arco tangente de n SINH n Devuelve el seno hiperb lico de n COSH n Devuelve el coseno hiperbolico de n TANH n Devuelve la tangente hiperbolica de n 13 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas funciones de caracteres conversi n del texto a may sculas y min sculas Son Funci n Descripci n LOWER texto Convierte el texto a min sculas funciona con los caracteres espa oles UPPER texto Convierte el texto a may sculas INITCAP texto Coloca la primera letra de cada palabra en may sculas funciones de transformaci n Funci n Descripci n TRIM caracteres FROM texto RTRIM texto Elimina los espaciosa la derecha del texto LTRIM texto Elimina los espacios a la izqu
22. e la semana 1 lunes 2 martes LAST_DAY fecha Obtiene el ltimo d a del mes al que pertenece la fecha Devuelve un valor DATE EXTRACT valor FROM fecha Extrae un valor de una fecha concreta El valor puede ser day d a month mes year a o etc GREATEST fecha1 fecha2 Devuelve la fecha m s moderna la lista LEAST fecha1 fecha2 Devuelve la fecha m s antigua la lista ROUND fecha formato Redondea la fecha al valor de aplicar el formato a la fecha El formato puede ser YEAR Hace que la fecha refleje el a o completo MONTH Hace que la fecha refleje el mes completo m s cercano a la fecha HH24 Redondea la hora a las 00 00 mas cercanas DAY Redondea al dia m s cercano TRUNC fecha formato Igual que el anterior pero trunca la fecha en lugar de redondearla funciones de conversi n Oracle es capaz de convertir datos autom ticamente a fin de que la expresi n final tenga sentido En ese sentido son f ciles las conversiones de texto a n mero y viceversa Ejemplo SELECT 5 3 FROM DUAL El resultado es 8 SELECT 5 3 FROM DUAL El resultado es 53 Tambi n ocurre eso con la conversi n de textos a fechas De hecho es forma habitual de asignar fechas Pero en diversas ocasiones querremos realizar conversiones expl citas 19 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas TO_CHAR Obtiene
23. ecen los registros no relacionados de ambas tablas 26 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net Es muy com n utilizar consultas en las que se desee agrupar los datos a fin de realizar c lculos en vertical es decir calculados a partir de datos de distintos registros Para ello se utiliza la cl usula GROUP BY que permite indicar en base a qu registros se realiza la agrupaci n Con GROUP BY la instrucci n SELECT queda de esta forma SELECT listaDeExpresiones FROM listaDeTablas JOIN tablasRelacionadasYCondicionesDeRelaci n WHERE condiciones GROUP BY grupos HAVING condicionesDeGrupo ORDER BY columnas En el apartado GROUP BY se indican las columnas por las que se agrupa La funci n de este apartado es crear un nico registro por cada valor distinto en las columnas del grupo Si por ejemplo agrupamos en base a las columnas tipo y modelo en una tabla de existencias se crear un nico registro por cada tipo y modelo distintos SELECT tipo modelo FROM existencias GROUP BY tipo modelo Si la tabla de existencias sin agrupar es Tl MODELO N_ALMACEN CANTIDAD AR 6 1 2500 AR 6 2 5600 AR 6 3 2430 AR 9 1 250 AR 9 2 4000 AR 9 3 678 AR 15 1 5667 AR 20 3 43 Bl 10 2 340 Bl 10 3 23 27 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas La consulta anterior creara esta salida i AR __ lt
24. ierda que posea el texto TRIM texto Elimina los espacios en blanco a la izquierda y la derecha del texto y los espacios dobles del interior Elimina del texto los caracteres indicados Por ejemplo TRIM h FROM nombre elimina las haches de la columna nombre que est n a la izquierda y a la derecha SUBSTR texto n m Obtiene los m siguientes caracteres del texto a partir de la posici n n si m no se indica se cogen desde n hasta el final LENGTH texto Obtiene el tama o del texto INSTR texto textoBuscado posInicial nAparici n Obtiene la posici n en la que se encuentra el texto buscado en el texto inicial Se puede empezar a buscar a partir de una posici n inicial concreta e incluso indicar el n mero de aparici n del texto buscado Ejemplo si buscamos la letra a y ponemos 2 en nAparici n devuelve la posici n de la segunda letra a del texto Si no lo encuentra devuelve 0 14 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net Funci n Descripci n REPLACE texto textoABuscar Buscar el texto a buscar en un determinado textoReemplazo texto y lo cambia por el indicado como texto de reemplazo Si no se indica texto de reemplazo entonces est funci n elimina el texto a buscar TRANSLATE texto Potentisima funci n que permite transformar caracteres Los caracteresACambiar son los caracteres que se van a
25. la de errores graves 35 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas e Y es introducci n Una vista no es m s que una consulta almacenada a fin de utilizarla tantas veces como se desee Una vista no contiene datos sino la instrucci n SELECT necesaria para crear la vista eso asegura que los datos sean coherentes al utilizar los datos almacenados en las tablas Por todo ello las vistas gastan muy poco espacio de disco Las vistas se emplean para Realizar consultas complejas m s f cilmente ya que permiten dividir la consulta en varias partes Proporcionar tablas con datos completos Utilizar visiones especiales de los datos Ser utilizadas como tablas que resumen todos los datos Ser utilizadas como cursores de datos en los lenguajes procedimentales como PL SQL Hay dos tipos de vistas Simples Las forman una sola tabla y no contienen funciones de agrupaci n Su ventaja es que permiten siempre realizar operaciones DML sobre ellas Complejas Obtienen datos de varias tablas pueden utilizar funciones de agrupaci n No siempre permiten operaciones DML creaci n de vistas Sintaxis CREATE OR REPLACE FORCE NOFORCE VIEW vista Calias alias2 AS consultaSELECT WITH CHECK OPTION CONSTRAINT restricci n WITH READ ONLY CONSTRAINT restricci n OR REPLACE Si la vista ya exist a la cambia por la actual FORCE Crea la vista aun
26. no que como resultado de la vista generada por SELECT aparece un nueva columna Ejemplo SELECT nombre precio precio 1 16 FROM articulos Esa consulta obtiene tres columnas La tercera tendr como nombre la expresi n utilizada para poner un alias basta utilizar dicho alias tras la expresi n SELECT nombre precio precio 1 16 AS precio_con_iva FROM articulos La prioridad de esos operadores es la normal tienen m s prioridad la multiplicaci n y divisi n despu s la suma y la resta En caso de igualdad de prioridad se realiza primero la operaci n que est m s a la izquierda Como es l gico se puede evitar cumplir esa prioridad usando par ntesis el interior de los par ntesis es lo que se ejecuta primero Cuando una expresi n aritm tica se calcula sobre valores NULL el resultado es el propio valor NULL Se puede utilizar cualquiera de los operadores aritm ticos suma resta multiplicaci n divisi n Como es habitual la multiplicaci n y la divisi n tienen preferencia sobre la suma y la resta en el orden de ejecuci n de la instrucci n dicho orden se puede alterar mediante el uso de los par ntesis concatenaci n de textos Todas las bases de datos incluyen alg n operador para encadenar textos En SQLSERVER es el signo en Oracle son los signos Ejemplo Oracle SELECT tipo modelo tipo modelo Clave Pieza FROM piezas 7 sistemas gestores de bases de datos Unidad 4
27. petir continuamente el nombre de la tabla se puede utilizar un alias de tabla SELECT a cod_tarea a descripcion_tarea b dni_empleado b nombre empleado WHERE a dni ESTAY 23 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas Al apartado WHERE se le pueden a adir condiciones encadenandolas con el operador AND Ejemplo SELECT a cod_tarea a descripcion_tarea FROM tareas a empleados b WHERE a dni_empleado b dni AND b nombre_empleado Javier Finalmente indicar que se pueden enlazar m s de dos tablas a trav s de sus campos relacionados Ejemplo SELECT a cod_tarea a descripcion_tarea b nombre_empleado c nombre_utensilio FROM tareas a empleados b utensilios_utilizados c WHERE a dni_empleado b dni AND a cod_tarea c cod_tarea relaciones sin igualdad A las relaciones descritas anteriormente se las llama relaciones en igualdad equijoins ya que las tablas se relacionan a trav s de campos que contienen valores iguales en dos tablas Sin embargo no siempre las tablas tienen ese tipo de relaci n por ejemplo EMPLEADOS Antonio 18000 Marta 21000 Sonia 15000 CATEGORIAS D 6000 11999 C 12000 17999 B 18000 20999 A 20999 80000 En el ejemplo anterior podriamos averiguar la categoria a la que pertenece cada empleado pero estas tablas poseen una relaci n que ya no es de igualdad La forma ser a SELECT a empleado a sueldo b categoria FROM empleados a categorias b
28. precios La combinaci n de SUBSTR e INSTR es para obtener s lo los n meros Incluso es posible que haya que utilizar REPLACE para cambiar los puntos por comas para utilizar el separador decimal del idioma espa ol funci n DECODE Funci n que permite realizar condiciones en una consulta Se eval a una expresi n y se colocan a continuaci n pares valor resultado de forma que si se la expresi n equivale al valor se obtiene el resultado indicado Se puede indicar un ltimo par metro con el resultado a efectuar en caso de no encontrar ninguno de los valores indicados Sintaxis DECODEL expresi n valor1 resultado1 l Lvalor2 resultado2 valorPordefecto Ejemplo SELECT DECODKE cotizacion 1 salario 0 85 2 salario 0 93 3 salario 0 96 salario FROM empleados En el ejemplo dependiendo de la cotizaci n se muestra rebajado el salario un 85 si la cotizaci n es uno un 93 si es dos y un 96 si es tres Si la cotizaci n no es ni uno ni dos ni tres sencillamente se muestra el salario sin m s 22 2 curso de administraci n de sistemas inform ticos autor Jorge Sanchez www jorgesanchez net e 4 6 obtener datos de m ltiples tablas Es m s que habitual necesitar en una consulta datos que se encuentran distribuidos en varias tablas Las bases de datos relacionales se basan en que los datos se distribuyen en tablas que se pueden relacionar mediante un campo Ese campo es el que permi
29. que los datos de la consulta SELECT no existan vista Nombre que se le da a la vista alias Lista de alias que se establecen para las columnas devueltas por la consulta SELECT en la que se basa esta vista El n mero de alias debe coincidir con el n mero de columnas devueltas por SELECT 36 2 curso de administraci n de sistemas inform ticos autor Jorge S nchez www jorgesanchez net WITH CHECK OPTION Hace que s lo las filas que se muestran en la vista puedan ser a adidas INSERT o modificadas UPDATE La restricci n que sigue a esta secci n es el nombre que se le da a esta restricci n de tipo CHECK OPTION WITH READ ONLY Hace que la vista sea de s lo lectura Permite grabar un nombre para esta restricci n Lo bueno de las vistas es que tras su creaci n se utilizan como si fueran una tabla Ejemplo CREATE VIEW resumen alias id_localidad localidad poblacion n_provincia provincia superficie capital_provincia id_comunidad comunidad capital_comunidad AS SELECT 1 id_localidad l nombre l poblacion n_provincia p nombre p superficie 12 nombre id_comunidad c nombre 13 nombre FROM localidades 1 JOIN provincias p USING n_provincia JOIN comunidades c USING id_comunidad JOIN localidades 12 ON p id_capital 12 id_ localidad JOIN localidades 13 ON c id_capital 13 id_ localidad SELECT DISTINCT comunidad capital_comunidad FROM resumen La vista pasa a usarse como una
30. rden ASCII no respeta el orden de cada alfabeto nacional No obstante es un problema que tiende a arreglarse en la actualidad en todos los SGBD en Oracle no existe problema alguno especialmente si son compatibles con Unicode valores l gicos Operador Significado AND Devuelve verdadero si las expresiones a su izquierda y derecha son ambas verdaderas Son OR Devuelve verdadero si cualquiera de las dos expresiones a izquierda y derecha del OR son verdaderas NOT Invierte la l gica de la expresi n que est a su derecha Si era verdadera mediante NOT pasa a ser falso Ejemplos Obtiene a las personas de entre 25 y 50 a os SELECT nombre apellido1 apellido2 FROM personas WHERE edad gt 25 AND edad lt 50 Obtiene a la gente de m s de 60 a os o de menos de 20 SELECT nombre apellido1 apellido2 FROM personas WHERE edad gt 60 OR edad lt 20 Obtiene a la gente de con primer apellido entre la A y la O SELECT nombre apellido1 apellido2 FROM personas WHERE apellido1 gt A AND apellido2 lt 2 BETWEEN El operador BETWEEN nos permite obtener datos que se encuentren en un rango Uso 9 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas SELECT tipo modelo precio FROM piezas WHERE precio BETWEEN 3 AND 8 Saca piezas cuyos precios est n entre 3 y 8 ambos incluidos Permite obtener registros cuyos valores est n en una lista de valores SELE
31. re empleado paga FROM empleados WHERE paga lt 1870 La ultima consulta obtiene los empleados cuyas pagas est n entre lo que gana Luis 1870 euros y lo que gana Martina 2500 Las subconsultas siempre se deben encerrar entre par ntesis y se deberia colocar a la derecha del operador relacional Una subconsulta que utilice los valores gt lt gt tiene que devolver un Unico valor de otro modo ocurre un error Ademas tienen que tener el mismo tipo de columna para relacionar la subconsulta con la consulta que la utiliza no puede ocurrir que la subconsulta tenga dos columnas y ese resultado se compare usando una sola columna en la consulta general 31 sistemas gestores de bases de datos Unidad 4 lenguaje SQL Il Consultas uso de subconsultas de m ltiples filas En el apartado anterior se comentaba que las subconsultas s lo pueden devolver una fila Pero a veces se necesitan consultas del tipo mostrar el sueldo y nombre de los empleados cuyo sueldo supera al de cualquier empleado del departamento de ventas La subconsulta necesaria para ese resultado mostrar a todos los sueldos del departamento de ventas Pero no podremos utilizar un operador de comparaci n directamente ya que esa subconsulta devuelve m s de una fila La soluci n a esto es utilizar instrucciones especiales entre el operador y la consulta que permiten el uso de subconsultas de varias filas Esas instrucciones son Instrucci n Signifi
32. s gt lt IS NOT NULL NOT LIKE IN NOT AND OR 4 4 ordenaci n El orden inicial de los registros obtenidos por un SELECT no guarda mas que una relaci n respecto al orden en el que fueron introducidos Para ordenar en base a criterios m s interesantes se utiliza la cl usula ORDER BY COIN O UI A w N j gt En esa cl usula se coloca una lista de campos que indica la forma de ordenar Se ordena primero por el primer campo de la lista si hay coincidencias por el segundo si ah tambi n las hay por el tercero y as sucesivamente Se puede colocar las palabras ASC O DESC por defecto se toma ASC Esas palabras significan en ascendente de la A a la Z de los n meros peque os a los grandes o en descendente de la Z a la a de los n meros grandes a los peque os respectivamente Sintaxis completa de SELECT para una sola tabla SELECT DISTINCT columna expresi n LAS alias y FROM tabla WHERE condici n ORDER BY expresi ni expresi n2 ASC DESC 11 sistemas gestores de bases de datos Unidad 4 lenguaje SQL II Consultas funciones Todos los SGBD implementan funciones para facilitar la creacion de consultas complejas Esas funciones dependen del SGBD que utilicemos las que aqui se comentan son algunas de las que se utilizan con Oracle Todas las funciones devuelven un resultado que procede de un determinado calculo La
33. s y 7 con 352 segundos INTERVAL 4 10 12 7 352 DAY TO SECOND 3 4 dias 10 horas 12 minutos INTERVAL 4 10 12 DAY TO MINUTE 4 dias 10 horas INTERVAL 4 10 DAY TO HOUR 4 d as INTERVAL 4 DAY 10 horas INTERVAL 10 HOUR 25 horas INTERVAL 253 HOUR 12 minutos INTERVAL 12 MINUTE 30 segundos INTERVAL 30 SECOND 8 horas y 50 minutos INTERVAL 8 50 HOUR TO MINUTE 7 minutos 6 segundos INTERVAL 7 06 MINUTE TO SECOND 8 horas 7 minutos 6 segundos INTERVAL 8 07 06 HOUR TO SECOND Esos intervalos se pueden sumar a valores de tipo DATE o TIMESTAMP para hacer c lculos Gracias a ello se permiten sumar horas o minutos por ejemplo a los datos de tipo TIMESTAMP Funci n Descripci n SYSDATE Obtiene la fecha y hora actuales SYSTIMESTAMP Obtiene la fecha y hora actuales en formato TIMESTAMP 18 2 curso de administraci n de sistemas inform ticos autor Jorge Sanchez www jorgesanchez net calcular fechas Funci n Descripci n ADDMONTHS fecha n A ade a la fecha el numero de meses indicado porn MONTHS_BETWEEN fecha7 Obtiene la diferencia en meses entre las dos fecha2 fechas puede ser decimal NEXT_DAY fecha d a Indica cual es el d a que corresponde a a adir a la fecha el d a indicado El d a puede ser el texto Lunes Martes Mi rcoles si la configura ci n est en espa ol o el n mero de d a d
34. te integrar los datos de las tablas Por ejemplo si disponemos de una tabla de empleados cuya clave es el dni y otra tabla de tareas que se refiere a tareas realizadas por los empleados es seguro si el dise o est bien hecho que en la tabla de tareas aparecer el dni del empleado para saber qu empleado realiz la tarea En el ejemplo anterior si quiere obtener una lista de los datos de las tareas y los empleados se podr a hacer de esta forma SELECT cod_tarea descripcion_tarea dni_empleado nombre_empleado FROM tareas empleados La sintaxis es correcta ya que efectivamente en el apartado FROM se pueden indicar varias tareas separadas por comas Pero eso produce un producto cruzado aparecer n todos los registros de las tareas relacionados con todos los registros de empleados El producto cartesiano a veces es til para realizar consultas complejas pero en el caso normal no lo es necesitamos discriminar ese producto para que s lo aparezcan los registros de las tareas relacionadas con sus empleados correspondientes A eso se le llama asociar join tablas La forma de realizar correctamente la consulta anterior asociado las tareas con los empleados que la realizaron ser a SELECT cod_tarea descripcion_tarea dni_empleado nombre_empleado Hi tareas N tese que se utiliza la notaci n tabla columna para evitar la ambiguedad ya que el mismo nombre de campo se puede repetir en ambas tablas Para evitar re
35. un texto a partir de un numero o una fecha En especial se utiliza con fechas ya que de numero a texto se suele utilizar de forma implicita fechas En el caso de las fechas se indica el formato de conversion que es una cadena que puede incluir estos simbolos en una cadena de texto YY Ano en formato de dos cifras YYYY Ano en formato de cuatro cifras MM Mes en formato de dos cifras MON Las tres primeras letras del mes MONTH Nombre completo del mes DY D a de la semana en tres letras DAY D a completo de la semana D D a de la semana del 1 al 7 DD D a en formato de dos cifras DDD D a del a o Q Semestre WW Semana del a o AM Indicador AM PM Indicador PM HH12 Hora de 1 a 12 HH24 Hora de 0 a 23 MI Minutos 0 a 59 SS Segundos 0 a 59 SSSS Segundos desde medianoche las Posici n de los separadores donde se pongan estos s mbolos aparecer n en el resultado Ejemplos SELECT TO_CHAR SYSDATE DD MONTH YYYY DAY HH MI SS FROM DUAL Sale 16 AGOSTO 2004 LUNES 08 35 15 por ejemplo 20 2 curso de administraci n de sistemas inform ticos autor Jorge Sanchez www jorgesanchez net n meros Para convertir n meros a textos se usa est funci n cuando se desean caracter sticas especiales En ese caso en el formato se pueden utilizar estos simbolos 9 Posici n del n mero 0
Download Pdf Manuals
Related Search