Resumo: Aprenda a usar comandos SQL básicos no sistema de gerenciamento de banco de dados de código aberto MySQL.
O MySQL é o gerenciador de banco de dados relacional mais utilizado. Sua facilidade de uso e licença de código aberto contribuíram muito para essa popularidade.
Na semana passada, abordei a instalação e configuração do MySQL no Ubuntu. Neste artigo, apresentarei vários recursos MySQL, principalmente noções básicas .
Nota: As consultas (comandos) do MySQL não fazem distinção entre maiúsculas e minúsculas ; no entanto, é uma prática comum usar ALL CAPS para as palavras - chave do comando real e minúsculas para o restante .
Conectando e desconectando do servidor MySQL
Para poder inserir consultas, primeiro você precisa se conectar ao servidor usando o MySQL e usar o prompt do MySQL. O comando para fazer isso é:
mysql -h host_name -u user -p -h é usado para especificar um nome de host (se o servidor estiver localizado em outra máquina; se não for, apenas omitir), -u menciona o usuário e -p especifica que você deseja inserir uma senha .
Embora não seja recomendado (por motivos de segurança), você pode inserir a senha diretamente no comando digitando-a logo após -p . Por exemplo, se a senha para test_user for 1234 e você estiver tentando se conectar na máquina que está usando, poderá usar:
mysql -u test_user -p1234 Se você inseriu com sucesso os parâmetros requeridos, você será saudado pelo prompt do shell do MySQL ( mysql> ):
Para desconectar -se do servidor e sair do prompt do mysql, digite:
QUIT Digitar quit (o MySQL não faz distinção entre maiúsculas e minúsculas) ou \ q também funcionará. Pressione Enter para sair.
Você também pode gerar informações sobre a versão com um comando simples:
sudo mysqladmin -u root version -p Nota: Certifique-se de estar conectado ao servidor antes de inserir qualquer uma das consultas que abordarei.
Se você quiser ver uma lista de opções, use:
mysql --help Usando consultas no MySQL
O MySQL armazena dados em tabelas e usa comandos chamados consultas ( SQL = linguagem de consulta estruturada). Antes de me aprofundar em armazenar, acessar e modificar dados, vou abordar as consultas básicas para que você obtenha o jeito.
Como o MySQL usa tabelas, a saída de consultas também será exibida em tabelas . Todas as instruções SQL devem ser seguidas por um ponto - e - vírgula ( ; ), embora haja exceções (mais notavelmente: QUIT). Você pode separar colunas com vírgula ( , ). Aqui estão alguns exemplos básicos:
mysql> SELECT VERSION(); mysql> SELECT CURRENT_DATE; mysql> SELECT VERSION(), CURRENT_DATE; Por exemplo, a terceira consulta imprimiria algo semelhante a isto:
+-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 8.0.15 | 2019-04-13 | +-----------+--------------+ 1 row in set (0, 41 sec) Como o ponto-e-vírgula ( ; ) marca o final de uma instrução, você também pode escrever várias instruções em uma única linha.
Por exemplo, em vez de:
mysql> SELECT VERSION(); mysql> SELECT CURRENT_DATE; Você também pode escrever:
mysql> SELECT VERSION(); SELECT CURRENT_DATE; Você também pode inserir consultas com várias linhas (se você não incluir um ponto - e - vírgula no final da linha antes de pressionar Enter ). Nesse caso, o MySQL irá simplesmente inserir um prompt diferente para permitir que você continue seu comando. Por exemplo:
mysql> SELECT -> VERSION() ->, -> CURRENT_DATE; Consultas com várias linhas também ocorrerão se você não terminar uma cadeia em uma linha (uma palavra cercada por ' ou ' ).
Se você quiser cancelar uma consulta, digite \ c e pressione Enter .
Existem prompts com diferentes significados:
- mysql> = pronto para nova consulta
- -> = esperando pela próxima linha de consulta de várias linhas
- '> = esperando pela próxima linha, aguardando a conclusão de uma string que começou com uma aspa simples (
') - “> = Esperando pela próxima linha, aguardando a conclusão de uma string que começou com aspas duplas ( “ )
- `> = esperando pela próxima linha, aguardando a conclusão de um identificador que começou com um backtick (
`) - / *> = esperando pela próxima linha, aguardando a conclusão de um comentário que começou com
/*
Você também pode imprimir a hora atual ( hh: mm: ss ) ao lado da data atual com NOW (), assim como o usuário com o qual você está conectado usando USER () :
mysql> SELECT NOW(); mysql> SELECT USER(); Isto irá produzir algo semelhante a isto:
+---------------------+ | NOW() | +---------------------+ | 2019-04-13 23:53:48 | +---------------------+ 1 row in set (0, 00 sec) +----------------+ | USER() | +----------------+ | [email protected] | +----------------+ 1 row in set (0, 00 sec) O MySQL permite calcular cálculos matemáticos também:
mysql> SELECT COS(PI()/3), (10-2+4)/3; Saída:
+--------------------+------------+ | COS(PI()/3) | (10-2+4)/3 | +--------------------+------------+ | 0.5000000000000001 | 4.0000 | +--------------------+------------+ Usando bancos de dados no MySQL
1. Obtendo informações sobre bancos de dados
Primeiro de tudo, você pode listar bancos de dados disponíveis com:
mysql> SHOW DATABASES; Você também pode ver o banco de dados selecionado com:
mysql> SELECT DATABASE(); Isso produzirá NULL se nenhum banco de dados for selecionado. Aqui está um exemplo de saída para as duas declarações mencionadas:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ +------------+ | DATABASE() | +------------+ | NULL | +------------+ 2. Criando Bancos de Dados
Isso é feito simplesmente inserindo um comando:
mysql> CREATE DATABASE example_db; Nota: No Ubuntu 18.04 (ou em qualquer outro sistema baseado no Unix), os nomes de bancos de dados e tabelas fazem distinção entre maiúsculas e minúsculas .
3. Selecionando Bancos de Dados
Para selecionar um banco de dados, você deve mencionar que deseja usá- lo:
mysql> USE example_db; Se tiver sucesso, você receberá a mensagem:
Database changed Se não for bem-sucedido, você receberá um erro informando que o MySQL não consegue encontrar o banco de dados especificado.
Você também pode selecionar um banco de dados ao se conectar ao servidor mencionando o nome de um banco de dados existente no final do comando connect:
mysql -h host_name -u user_name -p example_table Por exemplo:
mysql -u root -p example_table Usando tabelas no SQL
1. Obtendo informações sobre tabelas
Para listar as tabelas no banco de dados atual, use:
mysql> SHOW TABLES; Nota: Certifique-se de ter selecionado um banco de dados.
Se o banco de dados estiver vazio (por exemplo, um recém-criado), a saída será:
Empty set (0, 00 sec) Depois de criar tabelas, a saída será ao longo da linha de:
+----------------------+ | Tables_in_example_db | +----------------------+ | table_1 | | table_2 | +----------------------+ 1 row in set (0, 00 sec) 2. Criando Tabelas
Para criar tabelas, você precisa especificar o layout - as colunas e o tipo de dados que elas devem armazenar.
No meu exemplo, vou armazenar informações sobre um grupo de pessoas: nome, data de nascimento, sexo, país. Aqui está como eu posso criar essa tabela:
mysql> CREATE TABLE table_1 (name VARCHAR(30), birth_date DATE, sex CHAR(1), country VARCHAR(40)); Nota: Você também pode escrever o comando em várias linhas .
Você pode ver que mencionei o nome da tabela ( table_1 ) e o nome das colunas ( nome, data de nascimento, sexo, país ). Após os nomes das colunas, especifiquei o tipo de dados que eles armazenam. VARCHAR (n) são strings com no máximo n caracteres de comprimento, DATE é autoexplicativa (formato CCYY-MM-DD ) e CHAR (1) significa um único caractere (especificamente, pretendo usar 'm' e 'f ' para macho e fêmea ). Outros tipos comuns incluem INT (inteiros), BOOL (booleanos), TIME (hh: mm: ss), Existem muitos tipos de dados disponíveis para uso no MySQL (numérico, string, data e hora). Você também pode usar tipos de dados mais complexos, como AUTO_INCREMENT .
A tabela aparecerá agora se você SHOW TABLES .
Se desejar, você pode modificar o layout de uma tabela usando ALTER TABLE :
mysql> ALTER TABLE table_1 ADD email VARCHAR(50); mysql> ALTER TABLE table_1 DROP birth_date; Esses exemplos adicionaram uma coluna (primeiro exemplo) e excluíram uma coluna (segundo exemplo). Você pode conferir mais informações sobre o ALTER TABLE aqui, pois ele também possui usos mais avançados.
3. Descrevendo Tabelas
Você pode ver a estrutura de uma tabela a qualquer momento com:
mysql> DESCRIBLE table_name; Por exemplo:
mysql> DESCRIBE table_1; vai sair:
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | name | varchar(30) | YES | | NULL | | | birth_date | date | YES | | NULL | | | sex | char(1) | YES | | NULL | | | country | varchar(40) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0, 00 sec) 4. Excluindo Tabelas
A instrução para excluir tabelas é:
DROP TABLE table_name; 5. Inserindo Dados em Tabelas
Para inserir dados, você deve especificar os valores a serem introduzidos em cada coluna (na mesma ordem que na definição da tabela). Para valores vazios ou desconhecidos, você deve usar NULL . Certifique-se de valores não numéricos com aspas ( ' ) ou aspas duplas ( “ ). Os valores devem ser separados por vírgulas ( , ).
Aqui estão alguns exemplos para Smith e Emily:
mysql> INSERT INTO table_1 VALUES ('Smith', '1980-04-24', 'm', 'Argentina'); mysql> INSERT INTO table_1 VALUES ('Emily', '1994-07-19', 'f', NULL); 6. Esvaziando Mesas
Se você quiser esvaziar uma tabela (exclua todas as entradas), use:
DELETE FROM table_name; Usando WHERE, você pode excluir linhas específicas :
DELETE FROM table_name WHERE col_name = value Eu irei mais a fundo nas seções seguintes.
7. Atualizando Entradas da Tabela
A sintaxe para atualizar uma entrada é:
UPDATE table_name SET col = 'value' WHERE conditions Por exemplo:
UPDATE table_1 SET country = 'France' WHERE name = 'Emily' Se você não especificar condições, todas as entradas serão alteradas.
8. Recuperando Dados de Tabelas
O comando MySQL usado para extrair dados de tabelas é SELECT . A estrutura de tal declaração é:
SELECT what FROM where WHERE conditions; Analisarei alguns aplicativos comuns, para que você possa entender como extrair exatamente o que deseja do banco de dados.
uma. Selecionando todos os dados
Primeiro de tudo, o uso mais simples é exibir todos os dados de uma tabela . Por exemplo:
mysql> SELECT * FROM table_1; O curinga ( * ) representa tudo, table_1 é a tabela da qual estou extraindo. Você pode ver que omiti a parte WHERE ; É opcional ter condições para os dados selecionados.
b. Selecionando dados particulares
Primeiramente, vou passar por cima da seleção de linhas .
Para selecionar linhas específicas, você precisa especificar condições que restrinjam os dados:
mysql> SELECT * FROM table_1 WHERE name = 'Smith'; mysql> SELECT * FROM table_1 WHERE sex = 'm'; mysql> SELECT * FROM table_1 WHERE birth_date SELECT * FROM table_1 WHERE sex = 'f' AND birth_date > '1991-1-1'; mysql> SELECT * FROM table_1 WHERE sex = 'm' OR country = 'France'; mysql> SELECT * FROM table_1 WHERE country IS NOT NULL; Nos dois primeiros exemplos, simplesmente comparo strings ( case-insensitive ). Você também pode comparar valores como datas e inteiros com operadores de comparação ( >, =, <=, = ). é usado para denotar ' não é igual '. Você pode especificar várias condições usando operadores lógicos ( AND, OR ). E tem precedência mais alta que OR . É melhor usar parênteses quando tiver condições mais complexas.
IS NOT NULL é uma maneira de exibir somente linhas que não possuem um valor para a coluna especificada. Você não pode usar operadores de comparação aritmética com NULL, pois representa um valor ausente (o resultado também será NULL ). Você deve usar IS NULL e IS NOT NULL .
Ambos NULL e são tratados como FALSE, o resto como TRUE .
Agora vou cobrir exibindo colunas específicas .
Para fazer isso, você precisa especificar as colunas que deseja exibir, separadas por vírgulas. Por exemplo:
mysql> SELECT name, birth_date FROM table_1; Você também pode se livrar de repetir dados . Por exemplo, se eu quiser obter todas as datas de nascimento (sem obter o mesmo valor várias vezes se várias pessoas nascerem nessa data), usarei:
mysql> SELECT DISTINCT birth_date FROM table_1; Isso exibirá somente resultados DISTINCT .
Para ser ainda mais específico, você pode combinar a exibição de colunas específicas com condições ( WHERE ):
mysql> SELECT name, sex FROM table_1 WHERE country = 'France' AND birth_date < '1991-1-1'; c. Classificando dados
Para classificar os dados, você usa ORDER_BY :
mysql> SELECT name FROM table_1 ORDER BY birth_date; Você pode ver que combinei isso com a seleção de dados específicos. O comando acima exibirá os nomes de todas as entradas, ordenadas em ordem crescente por data de nascimento.
Você também pode encomendar em ordem decrescente :
mysql> SELECT name FROM table_1 ORDER BY birth_date DESC; A classificação pode ser aplicada em várias colunas . Por exemplo, para classificar em ordem decrescente por data de nascimento e pessoas nascidas na mesma data em ordem crescente por nome, eu usaria:
mysql> SELECT name FROM table_1 ORDER BY birth_date DESC, name; d. Manipulando datas
Você pode obter a data atual usando CURDATE () . Usando este e outro ano, você pode calcular uma diferença (por exemplo, para obter a idade de uma pessoa) com TIMESTAMPDIFF () :
mysql> SELECT name, birth_date, CURDATE(), -> TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age -> FROM table_1 ORDER BY age; Aqui está a saída para um exemplo de table_1 :
+--------+------------+------------+------+ | name | birth_date | CURDATE() | age | +--------+------------+------------+------+ | Emily | 1994-07-19 | 2019-04-13 | 24 | | Danny | 1992-08-04 | 2019-04-13 | 26 | | Joanna | 1992-08-04 | 2019-04-13 | 26 | | Joe | 1985-03-11 | 2019-04-13 | 34 | | Smith | 1980-04-24 | 2019-04-13 | 38 | +--------+------------+------------+------+ TIMESTAMPDIFF () usa como argumentos a unidade a ser usada para o resultado (YEAR) e duas datas (birth_date, CURDATE ()) para as quais calcular uma diferença. A palavra-chave AS ( alias) nomeia a coluna resultante e facilita o trabalho com (neste exemplo: classificação por idade).
Para referir-se a partes específicas de datas, você pode usar YEAR (), MONTH () e DAYOFMONTH (), usando a data como argumento . Por exemplo:
mysql> SELECT name, birth_date, MONTH(birth_date) FROM table_1; Você pode comparar resultados (meses, anos, dias) como números normais. No entanto, para compará- las com coisas como o mês seguinte, você não pode simplesmente adicionar a CURDATE (), pois isso pode fazer com que você verifique o mês 13 ou outro incômodo . A solução para isso é INTERVAL e DATE_ADD () :
mysql> SELECT name, birth_date FROM table_1 WHERE MONTH(birth_date) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); Você também pode usar o operador de módulo ( MOD ):
mysql> SELECT name, birth_date FROM pet WHERE MONTH(birth_date) = MOD(MONTH(CURDATE()), 12) + 1; Usar datas inválidas retornará NULL e produzirá ADVERTÊNCIAS, exibidas com:
mysql> SHOW WARNINGS; e. Usando correspondência de padrões
No MySQL, _ significa qualquer caractere único e % para 0+ caracteres e os padrões são (por padrão) insensíveis a maiúsculas e minúsculas . Em vez de = e , para usar padrões que você deve usar como e não como :
mysql> SELECT birth_date FROM table_1 WHERE name LIKE '%a%'; mysql> SELECT birth_date FROM table_1 WHERE name LIKE '%b'; mysql> SELECT birth_date FROM table_1 WHERE name LIKE 'c%'; mysql> SELECT * FROM table_1 WHERE name LIKE '___'; Estes exemplos selecionam as datas de nascimento das entradas onde o nome contém ' a ' (primeiro exemplo), termina com ' b ' (segundo exemplo) ou começa com ' c ' (terceiro exemplo). O último exemplo seleciona linhas em que o nome tem exatamente três caracteres (três instâncias de ' _ ').
Você também pode usar expressões regulares estendidas, com REGEXP_LIKE () (também operadores REGEXP e RLIKE ). Expressões regulares estão além do escopo deste guia, mas você pode conferir mais informações aqui.
f. Contando os resultados
A contagem de dados é importante e tem muitos usos no mundo real. O MySQL usa COUNT () para essas tarefas. O exemplo mais simples é contar as entradas de uma tabela :
mysql> SELECT COUNT(*) FROM table_1; Você também pode dividir a contagem entre os grupos . Por exemplo, eu poderia GROUP BY country e exibir quantas entradas existem em cada país:
mysql> SELECT country, COUNT(*) FROM table_1 GROUP BY country; Você poderia mencionar grupos ainda mais específicos inserindo mais colunas para agrupar . Por exemplo:
mysql> SELECT country, sex, COUNT(*) FROM table_1 GROUP BY country, sex; Esses resultados também podem ser usados em conjunto com WHERE para restringir a saída.
Tenha cuidado ao contar. Se você especificar qualquer outra coisa para imprimir além de COUNT () e não mencionar essas colunas após GROUP BY, poderá obter um erro ou resultados inesperados (consulte ONLY_FULL_GROUP_BY ).
g. Usando várias tabelas
Isso é algo que você pode querer fazer em um contexto um pouco mais complexo.
Como exemplo, imagine que há outra tabela ( tabela_2 ) que armazena a data ( data ) em que as pessoas ( nome ) participaram de reuniões ( reunião ).
Você pode exibir a idade das pessoas nessas tabelas quando elas participaram de reuniões:
mysql> SELECT table_1.name, TIMESTAMPDIFF(YEAR, birth_date, date) AS age, meeting FROM table_1 INNER JOIN table_2 ON table_1.name = table_2.name; Isso produzirá algo como:
+-------+------+-----------------+ | name | age | meeting | +-------+------+-----------------+ | Emily | 21 | Dog Lovers Club | | Emily | 22 | Dog Lovers Club | | Emily | 23 | Hackathon | | Smith | 36 | TED Talk | | Smith | 38 | Footbal Match | +-------+------+-----------------+ Vou tentar explicar a sintaxe . Para nome, tivemos que mencionar table_1.name e table_2.name, já que a coluna está presente em ambas as tabelas (para birth_date, data e reunião, não precisamos especificar a tabela, pois elas são exclusivas de uma delas). Isso usa as declarações que eu já cobri.
A parte interessante é esta:
FROM table_1 INNER JOIN table_2 ON table_1.name = table_2.name; INNER JOIN coloca as tabelas juntas e pega a linha que tem algo em comum, uma condição especificada pela palavra chave ON ; Nesse caso, onde os nomes correspondem.
Nota: Você também pode juntar a mesma tabela consigo mesma, talvez para comparar dois resultados SELECT.
Usando o modo de lote no MySQL
Outro recurso interessante é o modo em lote . Em vez do shell interativo, você pode colocar as instruções em um arquivo e executá- las:
mysql -h host_name -u user_name -p < batch_file Você será solicitado a inserir uma senha (se necessário para o usuário). Se você quiser continuar correndo em vez de parar com erros, use –force .
Você pode até redirecionar a saída para outro programa ou para um arquivo:
mysql -h host_name -u user_name -p < batch_file | less mysql -h host_name -u user_name -p output_file Você também pode obter saída interativa com saída -t ou eco com parâmetros -v .
Se você estiver no shell interativo do MySQL e quiser executar um script, use um destes dois:
mysql> source batch_file; mysql> \. batch_file; Empacotando
Neste artigo, abordei diferentes maneiras de usar o MySQL para manipular bancos de dados tabulares, até mesmo aprofundar em recursos mais avançados .
Eu ficaria feliz se este guia ajudar usuários em dificuldades e iniciantes. Mesmo se você não for iniciante, espero que você leia algo que você não conhece. Se isso te ajudou, nos conte nos comentários!
Recomendado
Publicações Populares
