Estrutura de Tabelas MySQL e Tipos de Dados

Introdução

Neste laboratório, exploraremos os fundamentos das estruturas de tabelas e tipos de dados do MySQL. Compreender como criar, modificar e gerenciar tabelas é crucial para qualquer pessoa que trabalhe com bancos de dados. Aprenderemos sobre os diferentes tipos de dados no MySQL, como escolher o tipo de dado certo para suas necessidades e como realizar operações básicas em tabelas. Ao final deste laboratório, você terá experiência prática na criação de tabelas, na definição de colunas com tipos de dados apropriados e na modificação de estruturas de tabelas.

Objetivos

Ao concluir este laboratório, você será capaz de:

• Compreender os principais tipos de dados do MySQL e quando usá-los
• Criar tabelas com definições de coluna apropriadas
• Modificar estruturas de tabelas existentes
• Remover tabelas quando não forem mais necessárias
• Visualizar e compreender metadados de tabelas

Compreendendo os Tipos de Dados MySQL

Nesta etapa, exploraremos os tipos de dados do MySQL mais comumente usados. Compreender os tipos de dados é crucial porque escolher o tipo de dado certo para suas colunas afeta tanto a integridade dos dados quanto o desempenho do banco de dados.

Como muitos comandos SQL estão envolvidos nesta etapa, recomendamos o uso do terminal web. Clique na aba "Terminal" para abri-lo; ele funciona como o terminal da área de trabalho.

Vamos começar conectando ao MySQL:

sudo mysql -u root

Agora que estamos conectados, vamos criar um novo banco de dados para nossos experimentos:

CREATE DATABASE store;
USE store;

Vamos examinar as principais categorias de tipos de dados do MySQL:

1. Tipos Numéricos:

   • INT: Para números inteiros
   • DECIMAL: Para números decimais precisos
   • FLOAT/DOUBLE: Para números decimais aproximados

2. Tipos de String:

   • VARCHAR: Para strings de comprimento variável
   • CHAR: Para strings de comprimento fixo
   • TEXT: Para texto longo

3. Tipos de Data e Hora:

   • DATE: Para datas (AAAA-MM-DD)
   • TIME: Para hora (HH:MM:SS)
   • DATETIME: Para data e hora

Vamos criar uma tabela simples que demonstra esses diferentes tipos de dados:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Vamos detalhar a estrutura desta tabela:

• id: Um inteiro de auto-incremento que serve como chave primária (primary key)
• name: Uma string de comprimento variável que não pode ser NULL
• price: Um número decimal preciso com 10 dígitos no total e 2 casas decimais
• description: Um campo de texto para descrições mais longas
• weight: Um número de ponto flutuante para valores decimais aproximados
• in_stock: Um campo booleano (TRUE/FALSE)
• created_at: Armazena automaticamente o carimbo de data/hora de criação
• last_updated: Atualiza automaticamente quando o registro muda

Para ver a estrutura da nossa tabela:

DESCRIBE products;

Você deve ver uma saída como esta:

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+-------------------------------+
8 rows in set (0.001 sec)

Criando Tabelas com Restrições

Nesta etapa, aprenderemos sobre restrições de tabelas e como elas ajudam a manter a integridade dos dados. Criaremos uma estrutura de tabela mais complexa que demonstra vários tipos de restrições.

Vamos criar duas tabelas relacionadas para entender relacionamentos e restrições:

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Vamos examinar as restrições que usamos:

1. PRIMARY KEY: Garante a identificação única de cada registro
2. UNIQUE: Impede valores duplicados em uma coluna
3. NOT NULL: Garante que uma coluna não pode conter valores NULL
4. CHECK: Valida os dados antes de serem inseridos
5. FOREIGN KEY: Garante a integridade referencial entre as tabelas
6. DEFAULT: Fornece um valor padrão se nenhum for especificado

Para ver a estrutura da tabela com restrições:

SHOW CREATE TABLE inventory_items;

Isso mostrará a instrução CREATE TABLE completa, incluindo todas as restrições:

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Sample output -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Modificando a Estrutura da Tabela

Nesta etapa, aprenderemos como modificar tabelas existentes usando comandos ALTER TABLE. Esta é uma tarefa comum quando seus requisitos de dados mudam ao longo do tempo.

Vamos modificar nossa tabela de produtos para adicionar algumas novas colunas e modificar as existentes:

1. Adicionar uma nova coluna:

ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

Este comando adiciona uma nova coluna chamada manufacturer após a coluna name.

2. Modificar uma coluna existente:

ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

Este comando altera a coluna description para uma string de comprimento variável com um comprimento máximo de 500 caracteres. Ele também define um valor padrão de 'No description available' para novas linhas.

3. Renomear uma coluna:

ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

Este comando renomeia a coluna weight para product_weight e altera seu tipo de dado para um número decimal com 8 dígitos no total e 2 casas decimais.

4. Remover uma coluna:

ALTER TABLE products
DROP COLUMN in_stock;

Este comando remove a coluna in_stock da tabela.

Vamos adicionar um índice composto para melhorar o desempenho da consulta:

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

Este comando cria um índice composto nas colunas name e manufacturer.

Para ver todas as alterações que fizemos:

DESCRIBE products;
SHOW INDEX FROM products;

Você deve ver a estrutura da tabela e os índices atualizados:

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

Trabalhando com Informações da Tabela

Nesta etapa, exploraremos várias maneiras de recuperar informações sobre nossas tabelas e sua estrutura.

Primeiro, vamos analisar as informações de status da tabela:

SHOW TABLE STATUS FROM store;

Este comando mostra informações sobre cada tabela no banco de dados, incluindo:

• Mecanismo de armazenamento (Storage engine)
• Formato da linha (Row format)
• Número de linhas
• Comprimento médio da linha (Average row length)
• Comprimento dos dados (Data length)
• Comprimento do índice (Index length)

Para ver todas as tabelas em nosso banco de dados:

SHOW TABLES;

Para ver informações detalhadas sobre uma coluna específica:

SHOW FULL COLUMNS FROM products;

Isso fornece informações adicionais sobre cada coluna, incluindo:

• Tipo da coluna (Column type)
• Collation
• Privilégios (Privileges)
• Comentários (Comments)

Para ver todos os índices em uma tabela:

SHOW INDEX FROM products;

Também podemos obter informações sobre nossas tabelas do banco de dados INFORMATION_SCHEMA:

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

Excluindo Tabelas e Limpeza

Nesta etapa final, aprenderemos como remover tabelas com segurança quando elas não forem mais necessárias. Esta é uma habilidade importante, mas deve ser usada com cautela, pois a exclusão de uma tabela exclui permanentemente todos os seus dados.

Antes de excluir uma tabela, é uma boa prática:

1. Verificar se você está no banco de dados correto
2. Verificar se a tabela existe
3. Garantir que você tenha um backup, se necessário

Vamos começar verificando nosso banco de dados e tabelas atuais:

SELECT DATABASE();
SHOW TABLES;

Para excluir uma tabela com segurança, podemos usar a cláusula IF EXISTS:

DROP TABLE IF EXISTS inventory_items;

Observe que precisamos excluir as tabelas com restrições de chave estrangeira primeiro. Se tentássemos excluir a tabela de categorias primeiro, receberíamos um erro porque inventory_items a referencia.

Agora podemos excluir as tabelas restantes:

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

Para verificar se as tabelas foram removidas:

SHOW TABLES;

Você deve ver um conjunto vazio, indicando que todas as tabelas foram excluídas.

Resumo

Neste laboratório, cobrimos os aspectos essenciais de como trabalhar com tabelas e tipos de dados MySQL:

1. Compreensão e uso de diferentes tipos de dados MySQL
2. Criação de tabelas com restrições apropriadas
3. Modificação de estruturas de tabelas usando ALTER TABLE
4. Recuperação de metadados e informações da tabela
5. Exclusão segura de tabelas quando elas não são mais necessárias

Essas habilidades formam a base para trabalhar com bancos de dados MySQL de forma eficaz.