Curso MySQL #06 - Alterando a Estrutura da Tabela (ALTER TABLE e DROP TABLE)

Introduction and Course Overview

In this section, Gustavo Guanabara introduces himself as the teacher of the database course and provides an overview of the sixth class.

Class Introduction

• Gustavo Guanabara welcomes the viewers to the sixth class of the database course.

• The focus of this class is to learn how to change the structure of a table.

• The course progresses step by step, covering 1 or 2 commands per class.

Changing Table Structure

In this section, Gustavo explains how to change the structure of a table in MySQL using ALTER TABLE command.

Adding a New Column

• To change the table structure, use ALTER TABLE command.

• Gustavo demonstrates adding a new column called "profession" to the existing "people" table.

• The syntax for adding a column is: ALTER TABLE people ADD COLUMN profession varchar(10);

Verifying Changes

• To verify if the changes were successful, use DESCRIBE or DESC command followed by the table name (e.g., DESCRIBE people;).

• After executing DESCRIBE, it shows that a new column "profession" has been added to the table.

Conclusion

In this class, Gustavo taught how to change the structure of a table in MySQL using ALTER TABLE command. He demonstrated adding a new column called "profession" to an existing table and verified the changes using DESCRIBE command.

Introduction to ALTER TABLE and DROP TABLE commands

In this section, the instructor introduces the ALTER TABLE and DROP TABLE commands in MySQL. These commands are used to modify the structure of a table and delete a table, respectively.

ALTER TABLE command

• The ALTER TABLE command is used to modify the structure of a table.

• It can be used to add or remove columns from a table.

• The instructor demonstrates how to use the ALTER TABLE command to add a new column called "profissao" (profession) to the "Pessoas" (People) table.

• Example: ALTER TABLE pessoas ADD COLUMN profissao VARCHAR(10);

DROP TABLE command

• The DROP TABLE command is used to delete an entire table from the database.

• It permanently removes both the structure and data of the table.

• The instructor advises caution when using this command and recommends always keeping backups of the database.

• To drop a table, use the following syntax: DROP TABLE <table_name>.

• Example: DROP TABLE pessoas;

Understanding DDL and DML Commands

This section explains the difference between Data Definition Language (DDL) and Data Manipulation Language (DML) commands in MySQL.

DDL Commands

• DDL commands are used for defining or modifying database structures, such as tables, indexes, or constraints.

• Examples of DDL commands include CREATE DATABASE, CREATE TABLE, ALTER TABLE, and DROP TABLE.

DML Commands

• DML commands are used for manipulating data within tables.

• Examples of DML commands include INSERT INTO, UPDATE, DELETE FROM, and SELECT.

Classifying ALTER TABLE and DROP TABLE Commands

In this section, we discuss whether ALTER TABLE and DROP TABLE commands are classified as DDL or DML commands.

• ALTER TABLE and DROP TABLE commands are classified as DDL (Data Definition Language) commands.

• These commands modify the structure of a table rather than manipulating the data within it.

• It is important to be cautious when using these commands, as they can permanently alter or delete tables and their associated data.

Importance of Backing Up Database

This section emphasizes the importance of backing up databases before making any structural changes.

• It is crucial to always have a backup of the database before performing any modifications.

• Making a copy of the database ensures that data can be restored in case of accidental deletion or corruption.

• Never make changes to a production database without first creating a backup.

Difference Between ALTER TABLE and DROP TABLE

This section clarifies the difference between ALTER TABLE and DROP TABLE commands in MySQL.

• The ALTER TABLE command is used to modify the structure of a table by adding or removing columns, changing column types, etc.

• The DROP TABLE command is used to completely delete a table from the database, including its structure and data.

• It is important to exercise caution when using these commands, especially with DROP TABLE, as it permanently removes all associated data.

Confirmation for Table Deletion

In this section, we discuss whether MySQL prompts for confirmation before deleting a table using DROP TABLE command.

• MySQL does not prompt for confirmation before executing the DROP TABLE command.

• When you drop a table using this command, it immediately deletes both the structure and data without asking for confirmation.

• Therefore, it is essential to double-check your actions and ensure you have backups before executing such commands.

Best Practices for Database Management

This section provides best practices for managing databases and avoiding accidental data loss.

• Never make changes to a database that is currently active or in production.

• Always create a backup of the database before performing any modifications.

• Be cautious when using DDL commands like ALTER TABLE and DROP TABLE, as they can have irreversible consequences if not used correctly.

• It is recommended to use descriptive table and column names to ensure clarity and maintainability of the database structure.

Retrieving Table Structure

This section explains how to retrieve the structure of a table in MySQL.

• To retrieve the structure of a table, you can use the DESCRIBE command or the SELECT statement with an asterisk (*) to select all columns from the table.

• Example: DESCRIBE pessoas; or SELECT * FROM pessoas;

• These commands provide information about the columns (fields) present in the table, their data types, and other attributes.

Adding Columns to Existing Tables

In this section, we learn how to add new columns to existing tables in MySQL.

• To add a new column to an existing table, use the ALTER TABLE command with ADD COLUMN followed by the column definition.

• Example: ALTER TABLE pessoas ADD COLUMN profissao VARCHAR(10);

• The new column will be added with its specified data type and length constraints.

The transcript provided does not cover additional timestamps.

New Section

The speaker discusses the importance of not manipulating data and explains that deleting data was only a consequence of the structure.

Importance of Not Manipulating Data

• The speaker emphasizes the significance of not manipulating data.

• Deleting data is mentioned as a consequence of the structure, rather than intentional manipulation.