Aprenda a usar várias funções do MySQL e MariaDB - Parte 2

Esta é a segunda parte de uma série de 2 articulações sobre o essencial de Mariadb / mysql comandos. Consulte nosso artigo anterior sobre este tópico antes de prosseguir.

1. Aprenda MySQL/Mariadb Basics para iniciantes - Parte 1

Nesta segunda parte da série para iniciantes MySQL/Mariadb, explicaremos como limitar o número de linhas retornadas por um Selecione consulta e como solicitar o conjunto de resultados com base em uma determinada condição.

Além disso, aprenderemos a agrupar os registros e executar a manipulação matemática básica em campos numéricos. Tudo isso nos ajudará a criar um script SQL que podemos usar para produzir relatórios úteis.

Pré -requisitos

Para começar, siga estas etapas:

1. Faça o download do funcionários amostra de banco de dados, que inclui seis tabelas que consistem em 4 milhões de registros no total.

# wget https: // launchpad.net/test-db/funcionários-db-1/1.0.6/+Download/Funcionários_db-Full-1.0.6.alcatrão.BZ2 # tar xjf funcionários_db-full-1.0.6.alcatrão.BZ2 # CD Funcionários_db 

2. Introduzir o Mariadb solicitar e criar um banco de dados nomeado funcionários:

# mysql -u root -p Digite a senha: Bem -vindo ao monitor mariadb. Os comandos terminam com; ou \ g. Seu ID de conexão MariaDB é 2 servidor versão: 10.1.14-Mariadb Mariadb Server Copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB e outros. Digite 'ajuda;' ou '\ h' para obter ajuda. Digite '\ c' para limpar a instrução de entrada atual. Mariadb [(nenhum)]> Criar funcionários do banco de dados; Consulta ok, 1 linha afetada (0.00 seg) 

3. Importe -o para o seu servidor mariadb da seguinte forma:

Mariadb [(nenhum)]> funcionários de origem.SQL 

Aguarde 1-2 minutos até que o banco de dados de amostra seja carregado (lembre-se de que estamos falando 4m registros aqui!).

4. Verifique se o banco de dados foi importado corretamente listando suas tabelas:

MARIADB [funcionários]> Usar funcionários; Banco de dados alterado MARIADB [funcionários]> Mostrar mesas; +---------------------+ | Tabelas_in_employes | + -----------------------+ | Departamentos | | Dept_emp | | Dept_manager | | funcionários | | salários | | títulos | + -----------------------+ 6 linhas no conjunto (0.02 Sec) 

5. Criar uma conta especial para usar com o funcionários Banco de dados (sinta -se à vontade para escolher o nome e a senha de outra conta):

MARIADB [funcionários]> Criar usuário [email protegido] identificado por 'empadminspass'; Consulta ok, 0 linhas afetadas (0.03 seg) MARIADB [funcionários]> Conceder todos os privilégios aos funcionários.* para [email protegido]; Consulta ok, 0 linhas afetadas (0.02 Sec) MARIADB [funcionários]> Privilégios descarregados; Consulta ok, 0 linhas afetadas (0.00 seg) MARIADB [funcionários]> saída Tchau 

Agora faça o login como Empadmin Usuário no prompt de mariadb.

# mysql -u empadmin -p Digite a senha: Bem -vindo ao monitor mariadb. Os comandos terminam com; ou \ g. Seu ID de conexão MariaDB é 4 servidor versão: 10.1.14-Mariadb Mariadb Server Copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB e outros. Digite 'ajuda;' ou '\ h' para obter ajuda. Digite '\ c' para limpar a instrução de entrada atual. Mariadb [(nenhum)]> Usar funcionários; Leitura Informações da tabela para a conclusão dos nomes de tabela e colunas Você pode desativar esse recurso para obter uma inicialização mais rápida com -um banco de dados 

Aprenda comandos básicos do MySQL para iniciantes

Verifique se todas as etapas descritas na imagem acima foram concluídas antes de prosseguir.

Encomendar e limitar o número de linhas no conjunto de resultados

A tabela de salários contém todas as rendas de cada funcionário com datas de partida e final. Podemos querer ver os salários de EMP_NO = 10001 ao longo do tempo. Isso ajudará a responder às seguintes perguntas:

1. Ele / ela recebeu algum aumento?
2. Se assim for, quando?

Execute a seguinte consulta para descobrir:

MARIADB [funcionários]> Selecione * dos salários em que Emp_no = 10001 Ordem por From_Date; +--------+--------+------------+------------+ | EMP_NO | salário | From_date | to_date | +--------+--------+------------+------------+| 10001 | 60117 | 1986-06-26 | 1987-06-26 | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | | 10001 | 66596 | 1989-06-25 | 1990-06-25 | | 10001 | 66961 | 1990-06-25 | 1991-06-25 | | 10001 | 71046 | 1991-06-25 | 1992-06-24 | | 10001 | 74333 | 1992-06-24 | 1993-06-24 | | 10001 | 75286 | 1993-06-24 | 1994-06-24 | | 10001 | 75994 | 1994-06-24 | 1995-06-24 | | 10001 | 76884 | 1995-06-24 | 1996-06-23 | | 10001 | 80013 | 1996-06-23 | 1997-06-23 | | 10001 | 81025 | 1997-06-23 | 1998-06-23 | | 10001 | 81097 | 1998-06-23 | 1999-06-23 | | 10001 | 84917 | 1999-06-23 | 2000-06-22 | | 10001 | 85112 | 2000-06-22 | 2001-06-22 | | 10001 | 85097 | 2001-06-22 | 2002-06-22 | | 10001 | 88958 | 2002-06-22 | 9999-01-01 | +--------+--------+------------+------------+17 linhas no set ( 0.03 seg) 

Agora, e se precisarmos ver os 5 mais recentes aumentos? Nós podemos fazer Ordem de From_date Desc. O Desc Palavra -chave indica que queremos classificar o conjunto de resultados em ordem decrescente.

Adicionalmente, Limite 5 nos permite retornar apenas o topo 5 linhas no conjunto de resultados:

MARIADB [funcionários]> Selecione * dos salários em que Emp_No = 10001 Ordem por From_Date Desc Limit 5; +--------+--------+------------+------------+ | EMP_NO | salário | From_date | to_date | +--------+--------+------------+------------+| 10001 | 88958 | 2002-06-22 | 9999-01-01 | | 10001 | 85097 | 2001-06-22 | 2002-06-22 | | 10001 | 85112 | 2000-06-22 | 2001-06-22 | | 10001 | 84917 | 1999-06-23 | 2000-06-22 | | 10001 | 81097 | 1998-06-23 | 1999-06-23 | +--------+--------+------------+--------------+5 linhas no set ( 0.00 seg) 

Consulta tabela mysql por ordem

Você também pode usar ORDENAR POR com vários campos. Por exemplo, a consulta a seguir ordenará o conjunto de resultados com base na data de nascimento do funcionário em forma ascendente (o padrão) e, em seguida, pelos sobrenomes em forma descendente alfabética:

MARIADB [funcionários]> Selecione concat (last_name, ',', primeiro_name) como nome, gênero como gênero, contrate_date como "data de contratação" da ordem dos funcionários por birth_date, last_name desc limite 10; +--------------------+--------+------------+ | Nome | Gênero | Data de aluguel | +----------------------+--------+------------+| Whitcomb, Kiyokazu | M | 1988-07-26 | | Schaad, Ronghao | M | 1988-07-10 | | Remmele, Supot | M | 1989-01-27 | | Pocchiola, Jouni | M | 1985-03-10 | | Kuzuoka, Eishiro | M | 1992-02-12 | | DeCaestecker, Moni | M | 1986-10-06 | | Wiegley, Mircea | M | 1985-07-18 | | Vendrig, Sachar | M | 1985-11-04 | | Tsukuda, Cedric | F | 1993-12-12 | | Tischendorf, Percy | M | 1986-11-10 | +----------------------+--------+------------+10 linhas no set (0.31 seg) 

Consulta Mysql Tabela por data de nascimento

Você pode ver mais informações sobre LIMITE aqui.

Records de agrupamento / Max, Min, AVG e Round

Como mencionamos anteriormente, o salários A tabela contém a renda de cada funcionário ao longo do tempo. Além do mais LIMITE, Podemos usar o Máx e Min Palavras -chave para determinar quando o número máximo e mínimo de funcionários foi contratado:

MARIADB [funcionários]> Selecione concat (last_name, ',', primeiro_name) como nome, max (b.salário) como "max. salário "dos funcionários a junção de salários B em um.emp_no = b.emp_no onde a.Emp_no em (10001, 10002, 10003) Grupo por um.emp_no; +-----------------+-------------+ | Nome | Máx. salário | +-----------------+-------------+| Facello, Georgi | 88958 | | Simmel, Bezalel | 72527 | | Bamford, Parto | 43699 | +-------------------+-------------+3 linhas em set (0.02 Sec) MARIADB [funcionários]> Selecione concat (last_name, ',', primeiro_name) como nome, min (b.salário) como "min. salário "dos funcionários a junção de salários B em um.emp_no = b.emp_no onde a.Emp_no em (10001, 10002, 10003) Grupo por um.emp_no; +-----------------+-------------+ | Nome | Min. salário | +-----------------+-------------+| Facello, Georgi | 60117 | | Simmel, Bezalel | 65828 | | Bamford, Parto | 40006 | +-------------------+-------------+3 linhas em set (0.00 seg) 

Agrupando registros MySQL usando palavras -chave Max e Min

Com base nos conjuntos de resultados acima, você pode adivinhar o que a consulta abaixo retornará?

MARIADB [funcionários]> Selecione Concat (Last_Name, ',', First_Name) como nome, Round (AVG (B (B.salário), 2) como "avg. salário "dos funcionários a junção de salários B em um.emp_no = b.emp_no onde a.Emp_no em (10001, 10002, 10003) Grupo por um.emp_no; +-----------------+-------------+ | Nome | Avg. salário | +-----------------+-------------+| Facello, Georgi | 75388.94 | | Simmel, Bezalel | 68854.50 | | Bamford, Parto | 43030.29 | +-------------------+-------------+3 linhas em set (0.01 seg) 

Se você concorda que ele retornará a média (conforme especificado por Avg) salário ao longo do tempo arredondado para 2 decimais (conforme indicado por REDONDO), você tem razão.

Se queremos ver a soma dos salários agrupados pelo funcionário e devolver o topo 5, Podemos usar a seguinte consulta:

MARIADB [funcionários]> Selecione EMP_NO, soma (salário) como salário do grupo de salários por EMP_NO Ordem por Salário Limite 5; +--------+---------+ | EMP_NO | Salário | +--------+---------+| 109334 | 2553036 | | 43624 | 2492873 | | 66793 | 2383923 | | 237542 | 2381119 | | 47978 | 2374024 | +--------+---------+5 linhas no conjunto (2.22 seg) 

Na consulta acima, os salários são agrupados pelo funcionário e a soma é realizada.

Reunindo tudo

Felizmente, não precisamos executar a consulta após a consulta para produzir um relatório. Em vez disso, podemos criar um script com uma série de comandos SQL para retornar todos os conjuntos de resultados necessários.

Depois de executar o script, ele retornará as informações necessárias sem mais intervenção. Por exemplo, vamos criar um arquivo chamado MaxMinavg.SQL No diretório de trabalho atual com o seguinte conteúdo:

--Selecionar banco de dados Use funcionários; -Calcule os salários máximos Selecione concat (last_name, ',', primeiro_name) como nome, max (b.salário) como "max. salário "dos funcionários a junção de salários B em um.emp_no = b.emp_no onde a.Emp_no em (10001, 10002, 10003) Grupo por um.emp_no; -Calcular salários mínimos Selecione concat (last_name, ',', primeiro_name) como nome, min (b.salário) como "min. salário "dos funcionários a junção de salários B em um.emp_no = b.emp_no onde a.Emp_no em (10001, 10002, 10003) Grupo por um.emp_no; -Calcule médias, rodadas para 2 locais decimais Selecione concat (last_name, ',', primeiro_name) como nome, redond (avg (b (b.salário), 2) como "avg. salário "dos funcionários a junção de salários B em um.emp_no = b.emp_no onde a.Emp_no em (10001, 10002, 10003) Grupo por um.emp_no; 

Linhas começando com dois traços são ignoradas e as consultas individuais são executadas uma após a outra. Podemos executar este script da linha de comando Linux:

# mysql -u empadmin -p < maxminavg.sql Digite a senha:  Nome Max. Salário Facello, Georgi 88958 Simmel, Bezalel 72527 Bamford, Parto 43699 Nome Min. Salário Facello, Georgi 60117 Simmel, Bezalel 65828 Bamford, Parto 40006 Nome AVG. Salário Facello, Georgi 75388.94 Simmel, Bezalel 68854.50 Bamford, Parto 43030.29 

ou do prompt de mariadb:

# mysql -u empadmin -p Digite a senha: Bem -vindo ao monitor mariadb. Os comandos terminam com; ou \ g. Seu ID de conexão MariaDB é 4 servidor versão: 10.1.14-Mariadb Mariadb Server Copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB e outros. Digite 'ajuda;' ou '\ h' para obter ajuda. Digite '\ c' para limpar a instrução de entrada atual. Mariadb [(nenhum)]> Fonte maxminAvg.SQL Leitura Informações da tabela para a conclusão dos nomes de tabela e colunas Você pode desativar esse recurso para obter uma inicialização mais rápida com -um banco de dados 

Script MySQL para executar comandos SQL

Resumo

Neste artigo, explicamos como usar várias funções de mariadb para refinar os conjuntos de resultados retornados por Selecione declarações. Depois de definirem, várias consultas individuais podem ser inseridas em um script para executá -lo com mais facilidade e reduzir o risco de erro humano.

Você tem alguma dúvida ou sugestão sobre este artigo? Sinta -se à vontade para nos deixar uma nota usando o formulário de comentário abaixo. Estamos ansiosos para ouvir de você!