Pesquisa de site

Aprenda MySQL/MariaDB para iniciantes - Parte 1

Neste artigo mostraremos como criar um banco de dados (também conhecido como esquema), tabelas (com tipos de dados) e explicaremos como executar a Linguagem de Manipulação de Dados (DML ) operações com dados em um servidor MySQL/MariaDB.

Presume-se que você já tenha 1) instalado os pacotes necessários em seu sistema Linux e 2) executado mysql_secure_installation para melhorar a segurança do servidor de banco de dados . Caso contrário, siga os guias abaixo para instalar o servidor MySQL/MariaDB.

  1. Instale o banco de dados MySQL mais recente em sistemas Linux
  2. Instale o banco de dados MariaDB mais recente em sistemas Linux

Para resumir, nos referiremos ao MariaDB exclusivamente ao longo deste artigo, mas os conceitos e comandos descritos aqui também se aplicam ao MySQL.

Criação de bancos de dados, tabelas e usuários autorizados

Como você sabe, um banco de dados pode ser definido em termos simples como uma coleção organizada de informações. Particularmente, MariaDB é um sistema de gerenciamento de banco de dados relacional (RDBMS) e utiliza a Structure Query Language para realizar operações em bancos de dados. Além disso, lembre-se de que MariaDB usa os termos banco de dados e esquema de forma intercambiável.

Para armazenar informações persistentes em um banco de dados, usaremos tabelas que armazenam linhas de dados. Freqüentemente, duas ou mais tabelas estarão relacionadas de alguma forma. Isso faz parte da organização que caracteriza o uso de bancos de dados relacionais.

Criando um novo banco de dados

Para criar um novo banco de dados chamado BooksDB, entre no prompt do MariaDB com o seguinte comando (será solicitado que você insira a senha do usuário root do MariaDB):

[root@TecMint ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

Depois que o banco de dados for criado, precisamos criar pelo menos duas tabelas nele. Mas primeiro vamos explorar o conceito de tipos de dados.

Apresentando os tipos de dados MariaDB

Como explicamos anteriormente, tabelas são objetos de banco de dados onde manteremos informações persistentes. Cada tabela consiste em dois ou mais campos (também conhecidos como colunas) de um determinado tipo de dados (o tipo de informação) que tal campo pode armazenar.

Os tipos de dados mais comuns no MariaDB são os seguintes (você pode consultar a lista completa na documentação online oficial do MariaDB):

Numérico:
  1. BOOLEAN considera 0 como falso e quaisquer outros valores como verdadeiros.
  2. TINYINT, se usado com SIGNED, cobre o intervalo de -128 a 127, enquanto o intervalo UNSIGNED é de 0 a 255.
  3. SMALLINT, se usado com SIGNED, cobre o intervalo de -32768 a 32767. O intervalo UNSIGNED é de 0 a 65535.
  4. INT, se usado com UNSIGNED, cobre o intervalo de 0 a 4294967295 e -2147483648 a 2147483647 caso contrário.

Nota: Em TINYINT, SMALLINT e INT, o padrão SIGNED é assumido.

DOUBLE(M, D), onde M é o número total de dígitos e D é o número de dígitos após a vírgula decimal, representa um número de ponto flutuante de precisão dupla. Se UNSIGNED for especificado, valores negativos não serão permitidos.

Corda :
  1. VARCHAR(M) representa uma string de comprimento variável onde M é o comprimento máximo permitido da coluna em bytes (65.535 em teoria). Na maioria dos casos, o número de bytes é idêntico ao número de caracteres, exceto alguns caracteres que podem ocupar até 3 bytes. Por exemplo, a letra espanhola ñ representa um caractere, mas ocupa 2 bytes.
  2. TEXT(M) representa uma coluna com comprimento máximo de 65.535 caracteres. Entretanto, como acontece com VARCHAR(M), o comprimento máximo real é reduzido se caracteres multibyte forem armazenados. Se M for especificado, a coluna será criada como o menor tipo que pode armazenar tal número de caracteres.
  3. MEDIUMTEXT(M) e LONGTEXT(M) são semelhantes a TEXT(M), apenas que os comprimentos máximos permitidos são 16.777.215 e 4.294.967.295 caracteres, respectivamente.
Data e hora:
  1. DATA representa a data no formato AAAA-MM-DD.
  2. TIME representa a hora no formato HH:MM:SS.sss (horas, minutos, segundos e milissegundos).
  3. DATETIME é a combinação de DATE e TIME no formato AAAA-MM-DD HH:MM:SS.
  4. TIMESTAMP é usado para definir o momento em que uma linha foi adicionada ou atualizada.

Depois de revisar esses tipos de dados, você estará em melhor posição para determinar qual tipo de dados precisa atribuir a uma determinada coluna em uma tabela.

Por exemplo, o nome de uma pessoa pode caber facilmente em um VARCHAR(50), enquanto uma postagem de blog precisará do tipo TEXT (escolha M como de acordo com suas necessidades específicas).

Criando tabelas com chaves primárias e estrangeiras

Antes de nos aprofundarmos na criação de tabelas, há dois conceitos fundamentais sobre bancos de dados relacionais que precisamos revisar: chaves primárias e estrangeiras.

Uma chave primária contém um valor que identifica exclusivamente cada linha ou registro da tabela. Por outro lado, uma chave estrangeira é usada para criar um link entre os dados em duas tabelas e para controlar os dados que podem ser armazenados na tabela onde a chave estrangeira está localizada. Tanto as chaves primárias quanto as estrangeiras são geralmente INTs.

Para ilustrar, vamos usar BookstoreDB e criar duas tabelas chamadas AuthorsTBL e BooksTBL como segue. A restrição NOT NULL indica que o campo associado requer um valor diferente de NULL.

Além disso, AUTO_INCREMENT é usado para aumentar em um o valor das colunas de chave primária INT quando um novo registro é inserido na tabela.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]>

Agora podemos prosseguir e começar a inserir registros em AuthorsTBL e BooksTBL.

Selecionando, inserindo, atualizando e excluindo linhas

Primeiro preencheremos a tabela AuthorsTBL. Por que? Porque precisamos ter valores para AuthorID antes de inserir registros no BooksTBL.

Execute a seguinte consulta no prompt do MariaDB:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Depois disso, selecionaremos todos os registros de AuthorsTBL. Lembre-se de que precisaremos do AuthorID de cada registro para criar a consulta INSERT para BooksTBL.

Se quiser recuperar um registro por vez, você pode usar uma cláusula WHERE para indicar uma condição que uma linha deve atender para ser retornada. Por exemplo,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternativamente, você pode selecionar todos os registros simultaneamente:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Agora vamos criar a consulta INSERT para BooksTBL, usando o AuthorID correspondente para corresponder ao autor de cada livro. Um valor 1 em BookIsAvailable indica que o livro está em estoque, 0 caso contrário:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Neste ponto faremos um SELECT para ver os registros em BooksTBL. Então vamos ATUALIZAR o preço de “O Alquimista” de Paulo Coelho e SELECIONAR esse disco específico novamente.

Observe como o campo BookLastUpdated agora mostra um valor diferente. Como explicamos anteriormente, um campo TIMESTAMP mostra o valor quando o registro foi inserido ou modificado pela última vez.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]>

Embora não façamos isso aqui, você também pode excluir um registro se não for mais usado. Por exemplo, suponha que queiramos excluir “O Alquimista ” de BooksTBL.

Para fazer isso, usaremos a instrução DELETE da seguinte forma:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Como no caso de UPDATE, é uma boa ideia fazer um SELECT primeiro para visualizar os registros que podem ser potencialmente impactados pelo EXCLUIR.

Além disso, não se esqueça de adicionar a cláusula WHERE e uma condição (BookID=6) para selecionar o registro específico a ser removido. Caso contrário, você corre o risco de deletar todas as linhas da tabela!

Se desejar concatenar dois (ou mais) campos, você pode usar a instrução CONCAT. Por exemplo, digamos que queremos retornar um conjunto de resultados que consiste em um campo com o nome do livro e autor no formato “O Alquimista (Paulo Coelho)” e outra coluna com o preço.

Isso exigirá um JOIN entre AuthorsTBL e BooksTBL no campo comum compartilhado por ambas as tabelas (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Como podemos ver, CONCAT nos permite unir múltiplas expressões de string separadas por vírgulas. Você também notará que escolhemos o alias Descrição para representar o conjunto de resultados da concatenação.

A saída da consulta acima é mostrada na imagem abaixo:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Criar usuário para acessar o banco de dados BookstoreDB

Usar root para executar todas as operações DML em um banco de dados é uma má ideia. Para evitar isso, podemos criar uma nova conta de usuário MariaDB (vamos chamá-la de bookstoreuser) e atribuir todas as permissões necessárias para BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Ter um usuário dedicado e separado para cada banco de dados evitará danos a todo o banco de dados caso uma única conta seja comprometida.

Dicas extras sobre MySQL

Para limpar o prompt do MariaDB, digite o seguinte comando e pressione Enter:

MariaDB [BookstoreDB]> \! clear

Para inspecionar a configuração de uma determinada tabela, faça:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

Por exemplo,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

Uma rápida inspeção revela que o campo BookIsAvailable admite valores NULL. Como não queremos permitir isso, vamos ALTER a tabela da seguinte forma:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Sinta-se à vontade para mostrar as colunas novamente – o SIM destacado na imagem acima deve agora ser um NÃO).

Finalmente, para visualizar todos os bancos de dados em seu servidor, faça:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;

[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

A imagem a seguir mostra o resultado do comando acima após acessar o prompt do MariaDB como o bookstoreuser (observe como esta conta não pode “ver” nenhum banco de dados diferente de BookstoreDB e information_schema (disponível para todos os usuários):

Resumo

Neste artigo explicamos como executar operações DML e como criar um banco de dados, tabelas e usuários dedicados em um banco de dados MariaDB. Além disso, compartilhamos algumas dicas que podem facilitar sua vida como administrador de sistema/banco de dados.

  1. Parte de administração de banco de dados MySQL – 1
  2. Parte de administração de banco de dados MySQL – 2
  3. Ajuste e otimização de desempenho do MySQL – Parte 3

Se você tiver alguma dúvida sobre este artigo, não hesite em nos informar! Sinta-se à vontade para usar o formulário de comentários abaixo para entrar em contato conosco.