El lenguaje SQL (I)

Publicado: julio 12, 2011 en Oracle

¿Qué es S.Q.L.?

  • Structured Query Language
  • Establecido como el lenguaje de base de datos relacional estándar.
  • Existen numerosos productos que soportan SQL, cada uno de ellos con pequeñas diferencias sin apenas importancia (p.ej. Oracle).
  • El SQL estándar es el publicado por ANSI e ISO.

Características de S.Q.L.

  • Lenguaje de definición de datos (DDL)
    • Create, Alter, Drop.
  • Lenguaje de manipulación de datos (DML)
    • Insert, Update, Delete.
  • Lenguaje de control de datos (DCL)
    • Grant, Revoke.
  • Control de transacciones
    • Commit, Rollback, Savepoint
  • Restricciones de integridad
    • Referencial, datos.

S.Q.L.
La sentencia SELECT

S.Q.L.
Sentencia Básica

  • Ejemplos:
    SELECT empno, ename
    FROM emp;
    SELECT *
    FROM emp;

S.Q.L.
Etiquetas de Columnas por defecto

  • Justificación de etiquetas y datos por defecto:
    • Izquierda fechas y caracteres
    • Derecha datos numéricos
  • Etiquetas y datos, por defecto, se muestran en mayúsculas.
  • Posibilidad del uso de alias de columnas.

S.Q.L.
Alias de columna

  • Un alias de columna renombra un encabezamiento de columna.
  • Útil, especialmente, en cálculos.
  • Sintaxis:
    • Sigue inmediatamente al nombre de la columna mediante la palabra clave AS entre ellos.
    • Se requiere encerrar un alias entre comillas dobles si contiene espacios en blanco, caracteres especiales o es case sensitive.

S.Q.L.
Uso de Alias de columna

  • SQL> SELECT ename AS nombre, sal salario, sal*12 AS “Salario Anual” FROM emp;
  • Resultado:

S.Q.L.
Expresiones aritméticas

  • Operadores:
    • Suma (+)
    • Resta (-)
    • Multiplicación (*)
    • División (/)
  • Cualquier sentencia SQL
  • Precedencia de operadores: * / + –
  • Operadores misma prioridad se evalúan de izq. a derecha.
  • Paréntesis sobreescriben reglas de precedencia.

S.Q.L.
Valor NULL

  • NULL es un valor inaccesible, sin valor desconocido o inaplicable.
  • NULL no representa ni un cero ni un blanco.
  • Las expresiones aritméticas que contengan NULL se evalúan a NULL (<> cero).

S.Q.L.
Operador de concatenación

  • Representado por dos barras verticales: ||
  • Vincula columnas o cadenas de caracteres.
  • Crea una columna resultado que es una expresión de tipo carácter.
  • Ejemplo:

S.Q.L.
Filas duplicadas

  • Las consultas, por defecto, muestran todas las filas, incluyendo las duplicadas.
  • Las duplicadas se eliminan usando DISTINCT en la cláusula SELECT:

S.Q.L.
Uso de la cláusula WHERE

  • Establece un criterio de selección aplicable a la tabla de la que se quiere recuperar datos.
  • Se especifica a continuación de FROM.
  • La condición de WHERE permite comparar columnas con otras columnas, valores, literales, expresiones aritméticas o funciones.

S.Q.L.
Sintaxis de WHERE

  • Ejemplo:

S.Q.L.
Operadores de comparación

  • Aplicables en las condiciones de la cláusula WHERE:
    • = Igual que
    • > Mayor que
    • >= Mayor que o igual a
    • < Menor que
    • <= Menor que o igual a
    • <> Distinto
    • BETWEEN m AND n Entre m y n (inclusive)
    • IN(lista) Se encuentra en la lista
    • LIKE Se ajusta a un patrón
    • IS NULL Es valor nulo

S.Q.L.
Ejemplos de comparación

  • SQL> SELECT ename, saL FROM emp
    WHERE sal BETWEEN 1000 AND 1500;
  • SQL> SELECT empno, ename, sal, mgr FROM EMP
    WHERE mgr IN (7902, 7566, 7788);
  • SQL> SELECT ename FROM emp
    WHERE ename LIKE‘_A%’;

    • Sintaxis de LIKE:
      • “%” representa cero o varios caracteres
      • “_” representa un solo carácter
  • SQL> SELECT ename, mgr FROM emp
    WHERE mgr IS NULL;

S.Q.L.
Operadores lógicos

  • AND devuelve TRUE si ambas condiciones son TRUE.
  • OR devuelve TRUE si alguna de las condiciones es TRUE.
  • NOT devuelve TRUE si la siguiente condición es FALSE.

S.Q.L.
Ejemplos con operadores lógicos

  • SQL> SELECT empno, ename, job, sal FROM emp
    WHERE sal >= 1100
    AND job = ‘CLERK’;
  • SQL> SELECT empno, ename, job, sal FROM emp
    WHERE sal >= 2000
    OR job = ‘MANAGER’;
  • SQL> SELECT ename, job FROM emp
    WHERE job NOT IN (‘CLERK’, ‘MANAGER’, ‘ANALYST’);

S.Q.L.
Reglas de precedencia

  • Siempre es aconsejable, para facilitar la lectura de la sentencia SQL, utilizar paréntesis que fuercen la prioridad de los operadores lógicos.
  • Sin paréntesis, el orden de evaluación es:
    • 1: Todos los operadores de comparación
    • 2: NOT
    • 3: AND
    • 4: OR

S.Q.L.
Recuperaciones ordenadas

  • Las filas recuperadas en la sentencia SELECT, si no se especifica nada, no tienen ningún orden determinado.
  • Se pueden ordenar con la cláusula ORDER BY siempre al final de una sentencia SELECT.
    • ASC Orden ascendente. Por defecto.
    • DESC Orden descendente.

S.Q.L.
Sintaxis de ORDER BY

  • Ejemplo:

 

FUNCIONES S.Q.L.

S.Q.L.
Funciones SQL

  • Existen dos tipos de funciones:
    • Funciones a nivel de fila
    • Funciones a nivel de múltiples filas
  • Funciones a nivel de fila. Tipos:
    • Carácter
    • Número
    • Fecha
    • Conversión
  • Funciones a nivel de grupo. Tipos:
    • Manipulan grupos de filas y devuelven un resultado por cada uno de ellos.

S.Q.L.
Funciones de Caracteres

  • Pueden ser de dos tipos:
    • Funciones de conversión
    • Funciones de manipulación de caracteres
  • De manipulación de caracteres:
    • CONCAT
    • SUBSTR
    • LENGTH
    • INSTR
    • LPAD Y RPAD
  • De conversión:
    • LOWER , UPPER e INITCAP

S.Q.L.
Funciones de conversión caracteres

  • LOWER: Convierte a minúsculas.
  • UPPER: Convierte a mayúsculas.
  • INITCAP: Convierte la primera letra de cada palabra en mayúsculas, y el resto en minúscula.
  • Atención: Usar una función de conversión dentro de la cláusula WHERE puede ser altamente ineficiente porque si la columna afectada forma parte de un índice éste lo desactiva, provocando un bajo rendimiento.

S.Q.L.
Funciones manipulación caracteres

  • CONCAT: Concatena dos valores.
  • SUBSTR: Extrae una subcadena.
  • LENGTH: Devuelve la longitud de la cadena.
  • INSTR: Devuelve la posición de un carácter o subcadena.
  • LPAD: Justifica a la derecha la cadena.
  • RPAD: Justifica a la izquierda la cadena.

S.Q.L.
Funciones Numéricas

  • ROUND (columna | expresión, n)
    • Redondea a n posiciones decimales. Si se omite n, no se redondea con decimales. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas, …
  • TRUNC (columna | expresión, n)
    • Trunca en la enésima posición decimal. Si se omite n, sin lugares decimales. Si n es negativo, los números a la izquierda del punto decimal se truncan a cero.
  • MOD (m, n)
    • Devuelve el resto de la división de m por n.

S.Q.L.
Ejemplos de funciones numéricas

  • SQL> SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1)
    FROM SYS.DUAL;
  • Resultado: 45.92   46   50
  • SQL> SELECT TRUNC(45.923, 2), TRUNC(45,923), TRUNC(45.923, -1)
    FROM SYS.DUAL;
  • Resultado: 45.92   45   40

S.Q.L.
Trabajando con fechas

  • Oracle almacena fechas en un formato numérico interno de 7 bytes:
    • Siglo, año, mes, día, horas, minutos, segundos
  • El formato de fecha por defecto es DD-MON-YY
  • SYSDATE es una función que devuelve fecha y hora (pseudocolumna del sistema)
  • DUAL es una tabla virtual de la bd., que puede ser usada para inspeccionar SYSDATE.

S.Q.L.
Operadores aritméticos de fechas

  • Sumar o restar un número a/o de una fecha da por resultado una fecha.
  • Restar dos fechas para encontrar la cantidad de días entre esas fechas.
  • Sumar horas a una fecha dividiendo la cantidad de horas por 24.

S.Q.L.
Funciones de Fecha (I)

  • MONHTS_BETWEEN (fecha1, fecha2)
    • Número de meses entre dos fechas. El resultado puede ser positivo o negativo.
  • ADD_MONTHS (fecha, n)
    • Añade n meses a fecha, según calendario. N debe de ser un número entero y puede ser negativo.
  • NEXT_DAY (fecha, ‘caracter’)
    • Devuelve la fecha del día especificado (‘carácter’) siguiente a fecha. Carácter puede ser un número representando un día o una cadena de caracteres, p.ej. ‘FRIDAY’.

S.Q.L.
Funciones de Fecha (II)

  • LAST_DAY (fecha)
    • Devuelve la fecha del último día del mes que contiene fecha.
  • ROUND (fecha [,’fmt’])
    • Cuando no se especifica ningún formato, devuelve la fecha del primer día del mes contenido en fecha. Si fmt=YEAR, encuentra el primer día del año.
  • TRUNC (fecha [,’fmt’])
    • Devuelve la fecha con la porción del día truncado en la unidad especificada por el modelo de formato fmt. Si se omite el formato, laf echa se trunca en el día más próximo.

S.Q.L.
Ejemplos funciones de fecha

  • MONTS_BETWEEN (‘01-SEP-95’, ‘11-JAN-94’) 19.6774194
  • ADD_MONTHS(‘11-JAN-94’, 6) ‘11-JUL-94’
  • NEXT_DAY (‘01-SEP-95’, ‘FRIDAY’) ‘08-SEP-95’
  • LAST_DAY (‘01-SEP-95’) ‘30-SEP-95’
  • ROUND (‘25-JUL-95’, ‘MONTH’) ‘01-AUG-95’
  • ROUND (‘25-JUL-95’, ‘YEAR’) ‘01-JAN-96’
  • TRUNC (‘25-JUL-95’, ‘MONTH’) ‘01-JUL-95’
  • TRUNC (‘25-JUL-95’, ‘YEAR’) ‘01-JAN-95’

S.Q.L.
Formatos de Fecha (I)

  • YYYY / YEAR
    • Año completo en número / Año en letras
  • MM / MONTH
    • Nº del mes con dos dígitos / Nombre completo del mes
  • DY / DAY
    • Día de la semana en tres letras / Nombre completo del día
  • fm (fill mode)
    • Elimina los espacios en blanco de relleno o suprime ceros a la izquierda

S.Q.L.
Formatos de Fecha (II)

  • Obtención de la hora:
    • HH / HH12 / HH24
      • Hora del día / Hora (1-12) / Hora (1-24)
    • MI / SS / SSSS
      • Minutos / Segundos / Segundos después de medianoche
    • AM o PM
      • Indicador del Meridiano
    • Sufijo SP / SPTH o THSP
      • Deletreo del número / Deletreo números ordinales
    • Se permiten literales

S.Q.L.
Funciones de conversión (I)

  • La conversión de tipos de datos puede ser:
      • IMPLÍCITA: Realizada automáticamente por Oracle
      • EXPLÍCITA: El usuario es quien la realiza
  • Conversión Implícita de datos
      • De VARCHAR2 o CHAR a NUMBER
      • De VARCHAR2 o CHAR a DATE
      • De NUMBER a VARCHAR2
      • De DATE a VARCHAR2
    • Estas conversiones se realizan por asignaciones, si Oracle 8 puede convertir el tipo de dato del valor utilizado en la asignación en el tipo de dato que era el objetivo de la asignación.

S.Q.L.
Funciones de conversión (II)

  • TO_CHAR (número | fecha [,’fmt’])
    • Convierte un número o fecha en una cadena de caracteres VARCHAR2 con el modelo de formato fmt.
      • 9: Representa un número
      • 0: Fuerza a que se muestra el cero
      • $: Signo de dólar
      • L: Usa el signo de moneda local
      • .: Imprime el punto decimal
      • ;: Imprime el indicador de millar
      • Para fechas, los fmt anteriores.

S.Q.L.
Funciones de conversión (III)

  • TO_NUMBER (char)
    • Convierte una cadena de caracteres con dígitos en un número.
  • TO_DATE (char [,’fmt’])
    • Convierte una cadena de caracteres representando una fecha en un valor de fecha según el fmt especificado. Si se omite el fmt, el formato es DD-MON-YY.
  • NVL (expr1, expr2)
    • Convierte un nulo (expr1) a un valor de tipo fecha, cadena o número (expr2).

S.Q.L.
La Función DECODE

  • Hace las veces de sentencia CASE o IF-THEN-ELSE, para facilitar consultas condicionales.
    • Descifra una expresión después de compararla con cada valor de búsqueda. Si la expresión es la misma que la búsqueda, se devuelve el resultado. Si se omite el valor por defecto, se devolverá un valor nulo donde una búsqueda no coincida con ninguno de los valores resultantes.

S.Q.L.
Uso de DECODE

  • SQL> SELECT job, sal,
    DECODE (job, ‘ANALYST’, sal*1.1, ‘CLERK’, sal*1.15, ‘MANAGER’, sal*1.20, sal) AS “Nuevo salario”
    FROM emp;
  • Si job = ‘ANALYST ‘ entonces el salario se incrementa en un 10%
  • Si job = ‘CLERK’ entonces se incrementa en un 15%
  • Si jog = ‘MANAGER’ entonces se incrementa en un 20%
  • Para otro caso, entones no hay incremento de salario

FUNCIONES DE GRUPO

S.Q.L.
Funciones de Grupo (I)

  • AVG ([DISTINCT | ALL] n)
    • Valor promedio de n.
  • COUNT ({* | [DISTINCT | ALL |] expr})
    • Cantidad de filas con expr no nulo. Con * se cuentan todas las filas incluyendo duplicadas y valores nulos.
  • MAX ([DISTINCT | ALL] expr)
    • Valor máximo de expr.
  • MIN ([DISTINCT | ALL] expr)
    • Valor mínimo de expr., ignorando los valores nulos.

S.Q.L.
Funciones de Grupo (II)

  • STDDEV ([DISTINCT | ALL] n)
    • Desviación estándar de n, ignorando los valores nulos.
  • SUM ([DISTINCT | ALL] n)
    • Suma los valores de n, ignorando los valores nulos.
  • VARIANCE ([DISTINCT | ALL] n)
    • Varianza de n, ignorando los valores nulos
  • Estas funciones no se pueden usar en la cláusula WHERE.

S.Q.L.
Funciones de grupo y Nulos

  • Las funciones de grupo IGNORAN los valores nulos de las columnas.
  • ¿Qué resultado obtendríamos se calculamos la media de la comisión de los empleados?

S.Q.L.
NVL y funciones de grupo

  • Esta media no es correcta porque se han ignorado las filas cuya comisión es nula.
  • Solución: Uso de la función NVL para forzar a las funciones de grupo que admitan los valores nulos.

S.Q.L.
Sintaxis de GROUP BY

  • Crea grupo de datos, por lo tanto se pueden usar funciones de grupo para devolver información resumida para cada grupo.

S.Q.L.
Uso de GROUP BY (I)

  • Si se incluye una función de grupo en una cláusula SELECT, no se puede seleccionar resultados individuales a menos que la columna aparezca en la cláusula GROUP BY.
  • No se pueden usar alias en GROUP BY.
  • Por defecto, tras un GROUP BY, las filas se ordenan de forma ascendente
  • Ejemplo:
    >SQL> SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

S.Q.L.
Uso de GROUP BY (II)

  • La columna referenciada por GROUP BY no es necesario seleccionarla.
  • Todas las columnas mencionadas en la SELECT que no son funciones de grupo, tienen que estar en la cláusula GROUP BY.
  • Se pueden formar agrupaciones sobre múltiples columnas:
    >SQL> SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job;

S.Q.L.
Consultas no válidas

  • Cualquier columna o expresión en la SELECT que no sea una función agregada, tiene que ser especificada en la cláusula GROUP BY
    SQL> SELECT deptno, COUNT(ename) FROM emp;
  • No puede usar una cláusula WHERE para restringir grupos. Utilice la cláusula HAVING para restringir grupos.
    SQL> SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000 GROUP BY deptno;

S.Q.L.
Cláusula HAVING

  • Use la cláusula HAVING para restringir grupos:
    • Los registros son agrupados
    • Se aplica la función de grupo
    • Los grupos que se corresponden con la cláusula HAVING se visualizan (condición TRUE).
  • HAVING puede preceder a GROUP BY, pero se recomienda que se ponga en primer lugar GROUP BY porque es más lógico. (1º se calculan grupos y posteriormente se calcula HAVING sobre esos gpos.).

S.Q.L.
Sintaxis de HAVING

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s