Pesquisa de site

Melhore o seu conhecimento de banco de dados com esta folha de dicas do MariaDB e MySQL

Leia este artigo e baixe nossa folha de dicas gratuita para começar a usar um banco de dados de código aberto.

Ao escrever um aplicativo ou configurar um para um servidor, eventualmente você precisará armazenar informações persistentes. Às vezes, um arquivo de configuração, como um arquivo INI ou YAML servirá. Outras vezes, um formato de arquivo personalizado projetado em XML ou JSON ou similar é melhor.

Mas às vezes você precisa de algo que possa validar entradas, pesquisar informações rapidamente, fazer conexões entre dados relacionados e, em geral, lidar com o trabalho dos usuários de maneira adequada. É para isso que um banco de dados foi projetado, e o MariaDB (um fork do MySQL feito por alguns de seus desenvolvedores originais) é uma ótima opção. Eu uso MariaDB neste artigo, mas as informações se aplicam igualmente ao MySQL.

É comum interagir com um banco de dados através de linguagens de programação. Por esse motivo, existem bibliotecas SQL para Java, Python, Lua, PHP, Ruby, C++ e muitas outras. No entanto, antes de usar essas bibliotecas, é útil entender o que está acontecendo com o mecanismo de banco de dados e por que sua escolha de banco de dados é importante. Este artigo apresenta o MariaDB e o comando mysql para familiarizá-lo com os conceitos básicos de como um banco de dados lida com dados.

Se você ainda não possui o MariaDB, siga as instruções em meu artigo sobre como instalar o MariaDB no Linux. Se você não estiver no Linux, use as instruções fornecidas na página de download do MariaDB.

Interaja com MariaDB

Você pode interagir com o MariaDB usando o comando mysql. Primeiro, verifique se o seu servidor está funcionando usando o subcomando ping, digitando sua senha do MariaDB quando solicitado:

$ mysqladmin -u root -p ping
Enter password:
mysqld is alive

Para facilitar a exploração do SQL, abra uma sessão interativa do MariaDB:

$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.
Commands end with ; or \g.
[...]
Type 'help;' or '\h' for help.
Type '\c' to clear the current input statement.

MariaDB [(none)]>

Isso coloca você em um subshell MariaDB, e seu prompt agora é um prompt MariaDB. Seus comandos Bash habituais não funcionam aqui. Você deve usar comandos MariaDB. Para ver uma lista de comandos MariaDB, digite help (ou apenas ?). Esses são comandos administrativos para seu shell MariaDB, portanto, são úteis para personalizar seu shell, mas não fazem parte da linguagem SQL.

Aprenda noções básicas de SQL

A Structured Query Language (SQL) tem o nome do que fornece: um método para consultar o conteúdo de um banco de dados em uma sintaxe previsível e consistente para receber resultados úteis. SQL se parece muito com uma frase comum em inglês, embora um pouco robótica. Por exemplo, se você se conectou a um servidor de banco de dados e precisa entender com o que precisa trabalhar, digite SHOW DATABASES; e pressione Enter para obter os resultados.

Os comandos SQL terminam com ponto e vírgula. Se você esquecer o ponto e vírgula, o MariaDB assume que você deseja continuar sua consulta na próxima linha, onde você pode fazer isso ou encerrar a consulta com ponto e vírgula.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.000 sec)

Isso mostra que há quatro bancos de dados presentes: information_schema, mysql, performance_schema e test. Para emitir consultas a um banco de dados, você deve selecionar qual banco de dados deseja que o MariaDB use. Isso é feito com o comando MariaDB use. Depois de escolher um banco de dados, o prompt do MariaDB muda para refletir o banco de dados ativo.

MariaDB [(none)]> use test;
MariaDB [(test)]>

Mostrar tabelas do banco de dados

Os bancos de dados contêm tabelas, que podem ser visualizadas da mesma forma que uma planilha: como uma série de linhas (chamadas registros em um banco de dados) e colunas. A interseção de uma linha e uma coluna é chamada de campo.

Para ver as tabelas disponíveis em um banco de dados (você pode considerá-las como guias em uma planilha com várias planilhas), use a palavra-chave SQL SHOW novamente:

MariaDB [(test)]> SHOW TABLES;
empty set

O banco de dados test não tem muito o que ver, então use o comando use para mudar para o banco de dados mysql.

MariaDB [(test)]> use mysql;
MariaDB [(mysql)]> SHOW TABLES;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
[...]
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.000 sec)

Existem muito mais tabelas neste banco de dados! O banco de dados mysql é o banco de dados de gerenciamento do sistema para esta instância MariaDB. Ele contém dados importantes, incluindo toda uma estrutura de usuários para gerenciar privilégios de banco de dados. É um banco de dados importante e nem sempre você precisa interagir diretamente com ele, mas não é incomum manipulá-lo em scripts SQL. Também é útil entender o banco de dados mysql quando você estiver aprendendo o MariaDB porque ele pode ajudar a demonstrar alguns comandos SQL básicos.

Examinar uma tabela

A última tabela listada no banco de dados mysql desta instância é intitulada user. Esta tabela contém dados sobre usuários com permissão para acessar o banco de dados. No momento, há apenas um usuário root, mas você pode adicionar outros usuários com privilégios variados para controlar se cada usuário pode visualizar, atualizar ou criar dados. Para ter uma ideia de todos os atributos que um usuário MariaDB pode ter, você pode visualizar os cabeçalhos das colunas em uma tabela:

> SHOW COLUMNS IN user;
MariaDB [mysql]> SHOW columns IN user;
+-------------+---------------+------+-----+----------+
| Field       | Type          | Null | Key | Default  |
+-------------+---------------+------+-----+----------+
| Host        | char(60)      | NO   | PRI |          |
| User        | char(80)      | NO   | PRI |          |
| Password    | char(41)      | NO   |     |          |
| Select_priv | enum('N','Y') | NO   |     | N        |
| Insert_priv | enum('N','Y') | NO   |     | N        |
| Update_priv | enum('N','Y') | NO   |     | N        |
| Delete_priv | enum('N','Y') | NO   |     | N        |
| Create_priv | enum('N','Y') | NO   |     | N        |
| Drop_priv   | enum('N','Y') | NO   |     | N        |
[...]
47 rows in set (0.001 sec)

Crie um novo usuário

Se você precisa da ajuda de alguém para administrar um banco de dados ou está configurando um banco de dados para um computador usar (por exemplo, em uma instalação WordPress, Drupal ou Joomla), é comum precisar de uma conta de usuário extra no MariaDB . Você pode criar um usuário MariaDB adicionando-o à tabela user no banco de dados mysql ou pode usar a palavra-chave SQL CREATE para solicitar MariaDB para fazer isso por você. Este último apresenta algumas funções auxiliares para que você não precise gerar todas as informações manualmente:

> CREATE USER 'tux'@'localhost' IDENTIFIED BY 'really_secure_password';

Ver campos da tabela

Você pode visualizar campos e valores em uma tabela de banco de dados com a palavra-chave SELECT. Neste exemplo, você criou um usuário chamado tux, então selecione as colunas na tabela user:

> SELECT user,host FROM user;
+------+------------+
| user | host       |
+------+------------+
| root | localhost  |
[...]
| tux  | localhost  |
+------+------------+
7 rows in set (0.000 sec)

Conceder privilégios a um usuário

Observando a listagem de colunas na tabela user, você pode explorar o status de um usuário. Por exemplo, o novo usuário tux não tem permissão para fazer nada com o banco de dados. Usando a instrução WHERE, você pode visualizar apenas o registro de tux:

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | N           | N           | N           |
+------+-------------+-------------+-------------+

Use o comando GRANT para modificar as permissões do usuário:

> GRANT SELECT on *.* TO 'tux'@'localhost';
> FLUSH PRIVILEGES;

Verifique sua alteração:

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | Y           | N           | N           |
+------+-------------+-------------+-------------+

O usuário tux agora tem privilégios para selecionar registros de todas as tabelas.

Crie um banco de dados personalizado

Até agora, você interagiu apenas com os bancos de dados padrão. A maioria das pessoas raramente interage muito com os bancos de dados padrão fora do gerenciamento de usuários. Normalmente, você cria um banco de dados e o preenche com tabelas cheias de dados personalizados.

Crie um banco de dados MariaDB

Você já deve saber como criar um novo banco de dados no MariaDB. É muito parecido com criar um novo usuário:

> CREATE DATABASE example;
Query OK, 1 row affected (0.000 sec)
> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
[...]

Torne este novo banco de dados ativo com o comando use:

> use example;

Crie uma tabela

Criar uma tabela é mais complexo do que criar um banco de dados porque você deve definir os títulos das colunas. MariaDB fornece muitas funções convenientes para você usar ao criar colunas, incluindo definições de tipo de dados, opções de incremento automático, restrições para evitar valores vazios, carimbos de data/hora automatizados e muito mais.

Aqui está uma tabela simples para descrever um conjunto de usuários:

> CREATE table IF NOT EXISTS member (
    -> id INT auto_increment PRIMARY KEY,
    -> name varchar(128) NOT NULL,
    -> startdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.030 sec)

Esta tabela fornece um identificador exclusivo para cada linha usando uma função de incremento automático. Ele contém um campo para o nome do usuário, que não pode ficar vazio (ou nulo), e gera um carimbo de data/hora quando o registro é criado.

Preencha esta tabela com alguns dados de amostra usando a palavra-chave SQL INSERT:

> INSERT INTO member (name) VALUES ('Alice');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Bob');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Carol');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('David');
Query OK, 1 row affected (0.011 sec)

Verifique os dados da tabela:

> SELECT * FROM member;
+----+-------+---------------------+
| id | name  | startdate           |
+----+-------+---------------------+
|  1 | Alice | 2020-10-03 15:25:06 |
|  2 | Bob   | 2020-10-03 15:26:43 |
|  3 | Carol | 2020-10-03 15:26:46 |
|  4 | David | 2020-10-03 15:26:51 |
+----+-------+---------------------+
4 rows in set (0.000 sec)

Adicione várias linhas de uma vez

Agora crie uma segunda tabela:

> CREATE table IF NOT EXISTS linux (
    -> id INT auto_increment PRIMARY KEY,
    -> distro varchar(128) NOT NULL,
Query OK, 0 rows affected (0.030 sec)

Preencha-o com alguns dados de amostra, desta vez usando um pequeno atalho VALUES para que você possa adicionar várias linhas em um comando. A palavra-chave VALUES espera uma lista entre parênteses, mas pode receber várias listas separadas por vírgulas:

> INSERT INTO linux (distro)
 -> VALUES ('Slackware'), ('RHEL'),('Fedora'),('Debian');
Query OK, 4 rows affected (0.011 sec)
Records: 4  Duplicates: 0  Warnings: 0
> SELECT * FROM linux;
+----+-----------+
| id | distro    |
+----+-----------+
|  1 | Slackware |
|  2 | RHEL      |
|  3 | Fedora    |
|  4 | Debian    |
+----+-----------+

Crie relacionamentos entre tabelas

Agora você tem duas tabelas, mas não há relacionamento entre elas. Cada uma delas contém dados independentes, mas talvez seja necessário associar um membro da primeira tabela a um item específico listado na segunda.

Para fazer isso, você pode criar uma nova coluna para a primeira tabela que corresponda a algo na segunda. Como ambas as tabelas foram projetadas com identificadores exclusivos (o campo id de incremento automático), a maneira mais fácil de conectá-las é usar o campo id de uma como seletor para o outro.

Crie uma nova coluna na primeira tabela para representar um valor na segunda tabela:

> ALTER TABLE member ADD COLUMN (os INT);
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0
> DESCRIBE member;
DESCRIBE member;
+-----------+--------------+------+-----+---------+------+
| Field     | Type         | Null | Key | Default | Extra|
+-----------+--------------+------+-----+---------+------+
| id        | int(11)      | NO   | PRI | NULL    | auto_|
| name      | varchar(128) | NO   |     | NULL    |      |
| startdate | timestamp    | NO   |     | cur[...]|      |
| os        | int(11)      | YES  |     | NULL    |      |
+-----------+--------------+------+-----+---------+------+

Usando os IDs exclusivos da tabela linux, atribua uma distribuição a cada membro. Como os registros já existem, use a palavra-chave SQL UPDATE em vez de INSERT. Especificamente, você deseja selecionar uma linha e atualizar o valor de uma coluna. Sintaticamente, isso é expresso um pouco ao contrário, com a atualização acontecendo primeiro e a seleção correspondente por último:

> UPDATE member SET os=1 WHERE name='Alice';
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Repita esse processo para os outros nomes na tabela member para preenchê-la com dados. Para variar, atribua três distribuições diferentes nas quatro linhas (dobrando em uma).

Unir tabelas

Agora que essas duas tabelas estão relacionadas, você pode usar SQL para exibir os dados associados. Existem muitos tipos de junções em bancos de dados e você pode experimentá-los todos depois de conhecer o básico. Aqui está uma junção básica para correlacionar os valores encontrados no campo os da tabela member com o campo id do linux tabela:

SELECT * FROM member JOIN linux ON member.os=linux.id;
+----+-------+---------------------+------+----+-----------+
| id | name  | startdate           | os   | id | distro    |
+----+-------+---------------------+------+----+-----------+
|  1 | Alice | 2020-10-03 15:25:06 |    1 |  1 | Slackware |
|  2 | Bob   | 2020-10-03 15:26:43 |    3 |  3 | Fedora    |
|  4 | David | 2020-10-03 15:26:51 |    3 |  3 | Fedora    |
|  3 | Carol | 2020-10-03 15:26:46 |    4 |  4 | Debian    |
+----+-------+---------------------+------+----+-----------+
4 rows in set (0.000 sec)

Os campos os e id formam a junção.

Em uma aplicação gráfica, você pode imaginar que o campo os pode ser definido por um menu suspenso, cujos valores são extraídos do conteúdo do campo distro do a tabela linux. Ao usar tabelas separadas para conjuntos de dados exclusivos, mas relacionados, você garante a consistência e a validade dos dados e, graças ao SQL, pode associá-los dinamicamente posteriormente.

Baixe a folha de dicas do MariaDB e MySQL

MariaDB é um banco de dados de nível empresarial. Ele foi projetado e comprovado como um mecanismo de banco de dados robusto, poderoso e rápido. Aprendê-lo é um grande passo para usá-lo para fazer coisas como gerenciar aplicativos da web ou bibliotecas de linguagens de programação. Como referência rápida ao usar o MariaDB, baixe nossa folha de dicas sobre MariaDB e MySQL.