MySQL com expressão de tabela comum
Benefícios do uso do CTE:
- Torna a consulta mais legível.
- Melhora o desempenho da consulta.
- Pode ser usado como uma alternativa à visão.
- É possível criar um encadeamento de CTE para simplificar a consulta.
- Consultas recursivas podem ser implementadas facilmente usando o CTE.
Sintaxe:
Com cte-name (column1, column2,… columnn) como (Consulta
)
Selecione * do nome CTE;
Aqui, você pode definir qualquer instrução SQL como consulta, selecionar, atualizar, excluir, inserir ou criar instrução. Se você definir a lista de colunas na cláusula, o número de colunas na consulta deve ser o mesmo com o número de colunas definidas com cláusula.
Pré -requisito:
O recurso CTE não é suportado por nenhuma versão do MySQL menor que 8.0. Então, você tem que instalar o MySQL 8.0 antes de praticar o exemplo deste artigo. Você pode verificar a versão atualmente instalada do MySQL executando o seguinte comando.
$ mysql -vA saída mostra que o MySQL versão 8.0.19 está instalado no sistema.
Se a versão correta estiver instalada, crie um banco de dados nomeado mydb e crie duas tabelas nomeadas Usuários e usuários_profile com alguns dados para saber os usos do CTE em MySQL. Execute as seguintes declarações SQL para realizar as tarefas. Essas declarações criarão duas tabelas relacionadas nomeadas Usuários e usuários_profile. Em seguida, alguns dados serão inseridos em ambas as tabelas, inserir declarações.
Criar banco de dados mydb;Use mydb;
Crie usuários de tabela (
Nome de usuário Varchar (50) Chave primária,
senha varchar (50) não nula,
status varchar (10) não nulo);
Crie Tabela Users_profile (
Nome de usuário Varchar (50) Chave primária,
Nome Varchar (50) não nulo,
endereço Varchar (50) não nulo,
Email Varchar (50) não nulo,
Foreign Key (Nome de usuário) Refere -se aos usuários (nome de usuário) na Cascade Exclete);
Insira os valores dos usuários
('admin', '7856', 'ativo'),
('Staff', '90802', 'ativo'),
('gerente', '35462', 'inativo');
Inserir em valores de usuários_profile
('admin', 'administrador', 'dhanmondi', '[email protected] ') ,
('Staff', 'Jakir Nayek', 'Mirpur', '[email protected] '),
('gerente', 'mehr afroz', 'eskaton', '[email protected] ');
Uso de CTE simples:
Aqui um CTE muito simples chamado CTE_USERS_PROFILE é criado onde nenhuma lista de campo é definida com o nome CTE com a cláusula e recuperará todos os dados do usuários_profile mesa. Em seguida, a instrução SELECT é usada para ler todos os registros de CTE_USERS_PROFILE Cte.
Com cte_users_profile como (Selecione * de Users_profile
)
Selecione * de cte_users_profile;
A saída a seguir aparecerá após a execução da declaração.
Uso de CTE simples com lista de colunas:
Você pode criar CTE mais especificamente, definindo a lista de campo com o nome da CTE com a cláusula. Nesse caso, os nomes de campo definidos com o nome CTE serão os mesmos que os nomes de campo definidos na consulta selecionada dentro da cláusula com com. Aqui, nome e e-mail Os campos são usados em ambos os lugares.
Com cte_users_profile (nome, email) como (Selecione o nome, email
De usuários_profile
)
Selecione * de cte_users_profile;
A saída a seguir aparecerá após a execução da declaração acima.
Uso de CTE simples com a cláusula onde:
A instrução SELECT com onde a cláusula pode ser definida na declaração CTE como outra consulta selecionada. A consulta selecionada com recuperação de registros de Usuários e usuários_profile mesas onde os valores de UsersName O campo é igual para as tabelas e o valor de nome de usuário não é 'funcionários'.
Com cte_users como (Selecione usuários.nome de usuário, usuários_profile.Nome, Users_profile.Endereço, usuários_profile.e-mail
De usuários, usuários_profile
Onde usuários.nome de usuário = usuários_profile.nome de usuário e usuários_profile.Nome de usuário 'Staff'
)
Selecione o nome como nome, endereço como endereço
De cte_users;
A saída a seguir aparecerá após a execução da declaração.
Uso de CTE simples com cláusula de grupo por grupo:
Qualquer função agregada pode ser usada na consulta usada no CTE. A instrução CTE a seguir mostra o uso da função Select With Count (). A primeira instrução SELECT é usada para exibir todos os registros de Usuários Tabela e a última instrução SELECT é usada para exibir a saída de CTE que contará o número total de usuários de Usuários Tabela que é ativa.
Selecione * dos usuários;Com cte_users como (
Selecione a contagem (*) como total
De usuários
Onde status = grupo "ativo" por status
)
Selecione Total como 'Usuários ativos totais'
De cte_users;
A saída a seguir aparecerá após a execução da declaração.
Uso de CTE simples com operador sindical:
A declaração CTE a seguir mostra o uso do operador da União na declaração CTE. A saída exibirá os valores de nome de usuário de Usuários Tabela onde o status valor é 'Inativo'E os outros valores de nome de usuário de usuários_profile mesa.
Com cte_users como (Selecione usuários.nome de usuário
De usuários
Onde status = 'inativo'
UNIÃO
Selecione Users_profile.nome de usuário
De usuários_profile
)
Selecione * de cte_users;
A saída a seguir aparecerá após a execução da declaração.
Uso de CTE simples com junção esquerda:
A declaração CTE a seguir mostra o uso da junção esquerda no CTE. A saída exibirá os valores de nome e e-mail campos de usuários_profile tabela aplicando a junção esquerda com base em nome de usuário campo entre Usuários e usuários_profile mesas e onde condição, isso filtrará esses registros de Usuários tabela onde o valor de status é 'Inativo'.
Com cte_users como (Selecione o nome, email
De usuários_profile
Esquerda junta usuários
Nos usuários.nome de usuário = usuários_profile.nome de usuário onde os usuários.status = 'inativo'
)
Selecione * de cte_users;
A saída a seguir aparecerá após a execução da declaração.
Conclusão:
Se você deseja aumentar o desempenho da consulta e obter a saída da consulta mais rapidamente, o CTE é a melhor opção do que outras opções do MySQL. Este artigo ajudará os usuários do MySQL a aprender o uso do CTE para a consulta selecionada com muita facilidade.