Mysql con expresión de tabla común

La expresión de la tabla común (CTE) es una característica importante de MySQL que se utiliza para generar un conjunto de resultados temporales. Se puede usar con cualquier instrucción SQL como seleccionar, insertar, actualizar, etc. Las consultas complicadas se pueden simplificar mediante el uso de CTE. El conjunto de resultados de cualquier consulta se almacena como un objeto para la tabla derivada en el momento de la ejecución de la consulta. Pero CTE puede ser autorreferenciación, lo que significa que la misma consulta se puede hacer referencia varias veces usando CTE. Por esta razón, el rendimiento de CTE es mejor que la tabla derivada. Con la cláusula se usa para definir un CTE y se puede definir más de un CTE en una sola declaración utilizando esta cláusula. En este artículo se explica cómo se puede aplicar un CTE en la consulta para que sea más legible y aumentar el rendimiento de la consulta.

Beneficios del uso de CTE:

• Hace que la consulta sea más legible.
• Mejora el rendimiento de la consulta.
• Se puede usar como alternativa a la vista.
• Es posible crear un encadenamiento de CTE para simplificar la consulta.
• Las consultas recursivas se pueden implementar fácilmente utilizando CTE.

Sintaxis:

Con name CTE (columna1, columna2, ... columnn) as (
Consulta
)
Seleccionar * de CTE-name;

Aquí, puede definir cualquier instrucción SQL como consulta, seleccionar, actualizar, eliminar, insertar o crear instrucción. Si define la lista de columnas con cláusula, entonces el número de columnas en la consulta debe ser el mismo con el número de columnas definidas con cláusula.

Requisito previo:

La función CTE no es compatible con ninguna versión de MySQL menor de 8.0. Entonces, tienes que instalar mysql 8.0 Antes de practicar el ejemplo de este artículo. Puede verificar la versión actualmente instalada de MySQL ejecutando el siguiente comando.

$ mysql -v

La salida muestra que MySQL versión 8.0.19 se instala en el sistema.

Si se instala la versión correcta, cree una base de datos con nombre mydb y crear dos tablas nombradas usuarios y Users_profile con algunos datos para conocer los usos de CTE en MySQL. Ejecute las siguientes declaraciones SQL para hacer las tareas. Estas declaraciones crearán dos tablas relacionadas con nombre usuarios y Users_profile. A continuación, algunos datos se insertarán en ambas tablas mediante declaraciones de inserción.

Crear base de datos mydb;
Usar mydb;
Crear usuarios de tabla (
nombre de usuario Varchar (50) Clave principal,
contraseña varchar (50) no nula,
estado varchar (10) no nulo);
Crear tabla users_profile (
nombre de usuario Varchar (50) Clave principal,
Nombre Varchar (50) No nulo,
dirección varchar (50) no nula,
Envíe un correo electrónico a Varchar (50) no nulo,
Referencias de la clave extranjera (nombre de usuario) Referencias (nombre de usuario) en Delete Cascade);
Insertar en los valores de los usuarios
('admin', '7856', 'activo'),
('Personal', '90802', 'activo'),
('gerente', '35462', 'inactivo');
Insertar en los valores de usuarios_profile
('admin', 'administrador', 'dhanmondi', '[email protected] ') ,
('Personal', 'Jakir Nayek', 'Mirpur', '[email protected] '),
('Gerente', 'Mehr Afroz', 'Eskaton', '[email protected] ');

Uso de CTE simple:

Aquí un CTE muy simple llamado cte_users_profile se crea donde ninguna lista de campo se define con el nombre de CTE con cláusula y recuperará todos los datos del Users_profile mesa. A continuación, la instrucción SELECT se usa para leer todos los registros de cte_users_profile Cte.

Con cte_users_profile as (
SELECCIONAR * DE USUSE_PROFILE
)
Seleccionar * de cte_users_profile;

La siguiente salida aparecerá después de ejecutar la declaración.

Uso de CTE simple con la lista de columnas:

Puede crear CTE más específicamente definiendo la lista de campo con el nombre de CTE con cláusula. En este caso, los nombres de campo definidos con el nombre de CTE serán los mismos que los nombres de campo definidos en la consulta de selección dentro de la cláusula con. Aquí, nombre y correo electrónico Los campos se usan en ambos lugares.

Con cte_users_profile (nombre, correo electrónico) como (
Seleccionar nombre, correo electrónico
De ussers_profile
)
Seleccionar * de cte_users_profile;

La siguiente salida aparecerá después de ejecutar la instrucción anterior.

Uso de CTE simple con la cláusula Where:

La declaración de selección con la cláusula donde se puede definir en la instrucción CTE como otra consulta selecta. La consulta de selección con registros de recuperación de usuarios y Users_profile tablas donde los valores de Nombre de usuarios el campo es igual para las tablas y el valor de nombre de usuario no es 'personal'.

Con cte_users como (
Seleccionar usuarios.nombre de usuario, users_profile.Nombre, Uss_Profile.dirección, users_profile.correo electrónico
De los usuarios, ussers_profile
Donde usuarios.Nombre de usuario = Users_Profile.nombre de usuario y usuarios_profile.nombre de usuario 'personal'
)
Seleccione el nombre como nombre, dirección como dirección
De cte_users;

La siguiente salida aparecerá después de ejecutar la declaración.

Uso de CTE simple con grupo por cláusula:

Cualquier función agregada se puede usar en la consulta que se usa en CTE. La siguiente instrucción CTE muestra el uso de la función Seleccionar consulta con Count (). La primera declaración de selección se utiliza para mostrar todos los registros de usuarios La tabla y la última declaración de selección se usa para mostrar la salida de CTE que contará el número total de usuarios desde usuarios mesa que están activas.

Seleccionar * de los usuarios;
Con cte_users como (
Seleccionar recuento (*) como total
De los usuarios
Donde status = "activo" grupo por estado
)
Seleccione Total como 'Usuarios activos totales'
De cte_users;

La siguiente salida aparecerá después de ejecutar la declaración.

Uso de CTE simple con un operador sindical:

La siguiente declaración de CTE muestra el uso del operador sindical en la declaración CTE. La salida mostrará los valores de nombre de usuario de usuarios mesa donde el estado el valor es 'Inactivo'y los otros valores de nombre de usuario de Users_profile mesa.

Con cte_users como (
Seleccionar usuarios.nombre de usuario
De los usuarios
Donde status = 'inactivo'
UNIÓN
Seleccione Uss_Profile.nombre de usuario
De ussers_profile
)
Seleccionar * de cte_users;

La siguiente salida aparecerá después de ejecutar la declaración.

Uso de CTE simple con unión izquierda:

La siguiente declaración de CTE muestra el uso de la izquierda en CTE. La salida mostrará los valores de nombre y correo electrónico campos de Users_profile tabla aplicando unión a la izquierda basada en nombre de usuario campo entre usuarios y Users_profile tablas y dónde condición, que filtrarán esos registros de usuarios tabla donde el valor de estado es 'Inactivo'.

Con cte_users como (
Seleccionar nombre, correo electrónico
De ussers_profile
Izquierda se une a los usuarios
En usuarios.Nombre de usuario = Users_Profile.nombre de usuario donde usuarios.status = 'inactivo'
)
Seleccionar * de cte_users;

La siguiente salida aparecerá después de ejecutar la declaración.

Conclusión:

Si desea aumentar el rendimiento de la consulta y obtener la salida de consulta más rápido, entonces el CTE es la mejor opción que otras opciones de MySQL. Este artículo ayudará a los usuarios de MySQL a aprender el uso de CTE para la consulta seleccionada muy fácilmente.