Explicación detallada de la consulta de Oracle

La consulta es una operación importante sobre los datos. El usuario envía una solicitud de consulta y el software compilado la transforma en un archivo binario para que el servidor la consulte y luego devuelve el conjunto de resultados de la consulta al usuario. La consulta generará una tabla virtual y los resultados se mostrarán en forma de tabla. , pero los resultados en realidad no se almacenan. La ejecución de una consulta solo extrae datos de la tabla de datos y los muestra en forma de tabla.

SELECT

FGROM

[WHERE ]

[GROUP BY < expresión de agrupación>]

[HAVING ]

[ORDER BY [ASC o DESC]]

agrupar por es se utiliza para agrupar los resultados de la consulta para estadísticas. Los resultados se generan después de agrupar los nombres que siguen al grupo.

Agrupar por también puede ir seguido de varias columnas para indicar una agrupación de varias columnas. Al agrupar en varias columnas, se dará prioridad a la agrupación anterior.

agrupar por nombre de columna, nombre de columna

La cláusula have se usa para limitar los resultados de visualización del grupo y solo se puede usar junto con agrupar por. No hay forma de usar funciones agregadas directamente en donde, es decir, no se puede usar la suma promedio, etc., por lo que se cita el tener y estas funciones se pueden usar en el tener.

Ordenar mediante clasificación, seguido del nombre de la columna y el método de clasificación. Si no se agrega nada, el orden predeterminado es ascendente. ASC significa orden ascendente, DESC significa orden descendente.

También puede configurar la clasificación de varias columnas en Oracle

orden por nombre de columna 1 para ascendente y descendente, nombre de columna 2 para ascendente y descendente

; El primero es el tipo principal, el segundo es el tipo para el siguiente nivel.

Nota: Al compararte contigo mismo, no puedes utilizar el tener. Puedes crear una nueva columna.

Nota: Si la declaración de selección también contiene agrupar por, tener, ordenar por, ordenar por agrupar por, tener, ordenar por

La agrupación y la agregación se utilizan juntas con fines estadísticos. información.

donde es para el servicio from y solo se puede usar con campos reales para filtrar las filas generadas por las operaciones especificadas en la cláusula from

group by se usa para agrupar el donde. cláusula. La salida de

have se usa para filtrar filas de los resultados agrupados

ordenar por se usa para ordenar los resultados filtrados

(1) Función de agrupación. : max min?avg sum count

max representa el valor máximo de la columna, min representa el valor mínimo de la columna, avg representa el valor promedio de la columna, sum representa la suma de la columna y count representa el número de filas de la columna.

Nota: Las funciones de agrupación (max, min, avg, count, sum) solo pueden aparecer en la cláusula have y en la cláusula order by en la lista de selección, y no pueden aparecer en la cláusula where y en la cláusula group by. .

(2) Consulta de varias tablas

La consulta de varias tablas se refiere a la consulta de dos o más tablas o vistas. En aplicaciones prácticas, cuando la consulta de una sola tabla no puede satisfacer Cuando es necesario. , generalmente se utilizan consultas de varias tablas. Por ejemplo: mostrar la ubicación del departamento de ventas y los nombres de sus empleados, en este caso se necesita (tabla de departamento y tabla emp).

Las conexiones de consulta de varias tablas generalmente se pueden dividir en: uniones internas, uniones externas izquierdas, uniones externas derechas y uniones completas.

Nota: Al utilizar consultas de varias tablas, puede establecer un alias para cada tabla. Si la tabla especifica un alias, todas las declaraciones en la declaración deben usar el alias en lugar del nombre real de la tabla. Y al escribir atributos, si el atributo es exclusivo de una de las tablas, no es necesario escribir un alias. Si hay dos tablas, debe especificar qué atributo de cada tabla. El formato es: nombre de tabla.nombre de atributo.

seleccione el nombre de la columna del alias de la tabla 1, alias de la tabla 2...

Nota: e es el alias de emp, d es el alias de dept.

Pero si opera la tabla, debe establecer un alias, como consultar el número de empleados en cada departamento cuyo salario es superior al salario promedio del departamento. Hay una tabla promedio y se debe establecer un alias para esta tabla (recordatorio: si solo hay una tabla modificada, no es necesario establecer un alias, pero si hay varias tablas, debe establecer un alias) .

Uniones internas

Las uniones internas forman una nueva tabla mediante el uso de operadores de comparación para hacer coincidir los valores en las columnas comunes de cada tabla, es decir: unir las dos tablas ** * Las filas existentes se eliminan para la conexión. Si algunas filas no son compartidas por las dos tablas, no se realizará la conexión.

seleccione

de la tabla 1

tabla de combinación interna 2

en condiciones coincidentes

o

seleccione

de la Tabla 1 Tabla 2

donde coinciden las condiciones

Unión exterior izquierda

Unión exterior izquierda y unión interior La diferencia es: al configurar la conexión exterior izquierda, la tabla principal y el apéndice se configuran primero y el apéndice después. La unión interna es para generar las partes coincidentes de las dos tablas, mientras que la unión externa izquierda es para escribir la tabla principal completa y las tablas del apéndice corresponden una a una.

seleccione

de la tabla 1

tabla de unión izquierda 2

según las condiciones coincidentes

unión exterior derecha

La unión exterior derecha y la unión exterior izquierda son básicamente iguales excepto que la tabla principal de la unión exterior derecha está escrita en la parte posterior.

seleccione

de la tabla 1

únase a la derecha de la tabla 2

según las condiciones coincidentes

unión completa

La combinación completa consiste en agregar datos no coincidentes de la tabla izquierda y la tabla derecha sobre la base de una combinación de igual valor. La palabra clave utilizada es combinación externa completa o combinación completa.

seleccione

de la tabla 1

unión completa de la tabla 2

en condiciones coincidentes

autounión

También existe un caso especial de autounión en Oracle, una tabla no se puede comparar consigo misma, por lo que cuando se necesita comparar dos informaciones de su propia tabla, también es necesario usarla. una combinación para conectarse, es decir, una tabla de la misma tabla no se puede comparar consigo misma.

(3) Subconsulta

Una subconsulta se refiere a una declaración de selección anidada en otras declaraciones SQL, también llamada consulta anidada. El orden de ejecución de las sentencias SQL es de derecha a izquierda, por lo que al ejecutar una consulta, primero se ejecutará la subconsulta de la izquierda y luego la consulta principal.

Las subconsultas se dividen en subconsultas de una sola fila y subconsultas de varias filas. Las subconsultas de una sola fila se refieren a declaraciones de subconsulta que devuelven una fila de datos, y las subconsultas de varias filas se refieren a declaraciones de consulta que devuelven varias filas de. datos. Las subconsultas también se pueden dividir en subconsultas de varias columnas, subconsultas de varias filas y subconsultas de varias columnas y varias filas.

Si la consulta interna no devuelve ningún registro al realizar una subconsulta, la comparación entre el campo DEPTNO y NULL en la condición externa siempre será falsa, lo que significa que la consulta externa no devolverá ningún resultado.

Se resume como:

Una subconsulta de una sola fila significa que la subconsulta solo devuelve una sola columna y una sola fila de datos.

Una subconsulta de múltiples subconsulta de fila significa que devuelve una sola columna y varias filas de datos. Es para una sola columna.

La subconsulta de varias columnas se refiere a una declaración de subconsulta que devuelve datos en varias columnas.

Subconsulta de una sola fila

donde deptno = (valor de una sola fila)

Subconsulta de varias filas

donde deptno in (valor de varias filas)

Subconsulta de varias columnas:

donde (trabajo, número de departamento) = (seleccione trabajo, número de departamento de emp donde ename = 'KING')

Subconsulta de varias columnas y subconsulta de fila

donde (trabajo,deptno) en (seleccione trabajo,deptno de emp donde ename='KING')

Subconsulta de una sola fila

Puede utilice =, >, <, >=, <=, <> en la consulta externa de una subconsulta de una sola fila y otros operadores de comparación.

Los resultados devueltos por la consulta interna deben coincidir con el campo (DEPTNO) en la condición de la consulta externa.

Subconsulta de varias filas

Al utilizar una subconsulta de varias filas en la cláusula WHERE, puede utilizar operadores de comparación de varias filas (IN, ALL, ANY).

IN: igual a cualquiera.

TODOS: Compara con todos los valores devueltos por la subconsulta. Por ejemplo: sal>ALL(1,2,3) es equivalente a sal>3, es decir, mayor que todos.

ANY: Compara con cualquier valor devuelto por la subconsulta. Por ejemplo: sal>ANY(1,2,3) es equivalente a sal>1, es decir, puede ser mayor que cualquiera.

Nota: El operador ANY debe usarse junto con el operador de comparación de una sola fila, y las filas devueltas solo deben coincidir con cualquier resultado de la subconsulta.

Subconsulta de varias columnas

La subconsulta de varias columnas es lo mismo que la subconsulta de varias filas, excepto que cuando se utiliza una subconsulta de varias columnas, habrá varias columnas para hacer coincidir.

(4) Operaciones de conjunto

Para combinar los resultados de múltiples declaraciones de selección, puede utilizar los símbolos de operación de conjunto unión, unión de todos, intersección y menos.

Unión: Este operador se utiliza para obtener la unión de dos conjuntos de resultados. Cuando se utiliza este operador, las filas duplicadas en el conjunto de resultados se eliminarán automáticamente

union all: esta operación es similar a la unión, pero no cancelará las filas duplicadas ni las ordenará

intersect: use este operador para obtener la intersección de dos conjuntos de resultados

menos: use este operador para obtener el conjunto de diferencias de dos conjuntos de resultados. Solo mostrará que existe en el primer conjunto, y allí. no hay datos en el segundo conjunto

En resumen, la operación de conjunto es combinar dos o más conjuntos de resultados en un solo conjunto de resultados.

intersect ?Intersect devuelve todos los registros de dos consultas

union all ?Union devuelve todos los registros de cada consulta, incluidos los registros duplicados

union ?Intersection devuelve todos los registros de cada consulta, excluyendo registros duplicados

MINUS ?El complemento devuelve los registros restantes después de los registros verificados por la primera consulta menos los registros recuperados por la segunda consulta

Nota: cuando se utilizan operaciones de conjunto , el número de columnas y los tipos de columnas devueltos por la consulta deben coincidir y los nombres de las columnas pueden ser diferentes.

(1) Palabra clave distinta

En Oracle, pueden ocurrir varias situaciones idénticas, luego puedes usar Distinct para eliminar filas duplicadas

(2) Varias tablas El la consulta y la subconsulta de una sola fila pueden lograr la misma función

Consultar los nombres, trabajos y salarios de los empleados del departamento de ventas

(3) Mostrar los empleados cuyo salario es superior a el salario promedio de su propio departamento Información del empleado

Análisis:

1. Encuentre el salario promedio de todos los departamentos

seleccione deptno,avg(sal) de emp agrupar por deptno;

2. Encuentre la información salarial de todos

seleccione ename,sal,deptno from emp;

3. Combine los dos conjuntos de resultados usando un multi -combinación de unión de tabla

seleccione * de emp,(seleccione deptno,avg(sal) avgsal de emp grupo por deptno) damao donde emp.deptno=damao.deptno;

4. Elimina aquellos con salario inferior al promedio Solo esos datos:

select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao donde emp.deptno=damao.deptno and sal>avgsal;

(4) Introducción a la tabla emp

¿Tipo de campo?

empno?NUMBER(4) representa el número de empleado, ¿cuál? es un número único

ename? VAECHAR2(10) ? Representa el nombre del empleado

trabajo VARCHAR2(9) Representa el puesto de trabajo

mgr ?NUMBER(4) ) Representa el número de líder de un empleado

fecha de contratación ? FECHA representa la fecha de empleo

sal ? > comm ?NUMBER(7,2) ? representa bonificación o comisión

deptno NUMBER(2) número de departamento