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:
Tipos Numéricos:
- INT: Para números inteiros
- DECIMAL: Para números decimais precisos
- FLOAT/DOUBLE: Para números decimais aproximados
Tipos de String:
- VARCHAR: Para strings de comprimento variável
- CHAR: Para strings de comprimento fixo
- TEXT: Para texto longo
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 NULLprice: Um número decimal preciso com 10 dígitos no total e 2 casas decimaisdescription: Um campo de texto para descrições mais longasweight: Um número de ponto flutuante para valores decimais aproximadosin_stock: Um campo booleano (TRUE/FALSE)created_at: Armazena automaticamente o carimbo de data/hora de criaçãolast_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:
- PRIMARY KEY: Garante a identificação única de cada registro
- UNIQUE: Impede valores duplicados em uma coluna
- NOT NULL: Garante que uma coluna não pode conter valores NULL
- CHECK: Valida os dados antes de serem inseridos
- FOREIGN KEY: Garante a integridade referencial entre as tabelas
- 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:
- 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.
- 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.
- 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.
- 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:
- Verificar se você está no banco de dados correto
- Verificar se a tabela existe
- 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:
- Compreensão e uso de diferentes tipos de dados MySQL
- Criação de tabelas com restrições apropriadas
- Modificação de estruturas de tabelas usando ALTER TABLE
- Recuperação de metadados e informações da tabela
- 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.
