MySQL Terminal: Create Databases, Tables and more.

Create database

CREATE DATABASE database_name;

The CREATE DATABASE command in MySQL is used to create a new database.

• CREATE DATABASE: Command that creates a new database.
• database_name: Name of the database you want to create. The name must be unique within the MySQL instance.

Notes:

• The database name must follow naming conventions and cannot contain certain special characters.
• If you try to create a database that already exists, MySQL will return an error. To avoid this, you can use the IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS database_name;

This version only creates the database if it does not already exist, avoiding errors.

View Database

SHOW DATABASES;

The SHOW DATABASES; command in MySQL is used to list all the databases available on the MySQL server instance you are connected to.

Example output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| library_games      |
+--------------------+

Explanation:

• information_schema, mysql, performance_schema, and sys are system databases that MySQL uses to manage the instance itself and store information about privileges, performance, etc.
• Any other databases listed are those that were created by you or by other applications, such as the library_games database in this example.

This command is useful for checking which databases are available to manage or query.

Connect to database

USE database_name;

The USE database_name; command in MySQL is used to select a specific database and set it as the active database for the current session.

Create table

The CREATE TABLE command is used in MySQL to create a new table within a database. The basic syntax of the command defines the names of the fields (columns) and their respective data types.

CREATE TABLE table_name (
field1 data_type,
field2 data_type,
...
fieldN data_type
);

Components:

• table_name: Name of the table you want to create.
• field1, field2, ... fieldN: Names of the columns (fields) that the table will have.
• data_type: The type of data that the column will store (such as INT, VARCHAR, DATE, etc.).

Practical example:

Suppose you are creating a table called games to store information about games, where each game has an ID, a title, a genre, and a release date:

CREATE TABLE games (
id INT AUTO_INCREMENT PRIMARY KEY, 
title VARCHAR(100), 
gender VARCHAR(50), 
release_date DATE 
);

Explanation:

• id INT AUTO_INCREMENT PRIMARY KEY: Creates an id column of type integer (INT), which increments automatically and is the primary key of the table.
• title VARCHAR(100): Creates a title column that can store texts of up to 100 characters.
• genre VARCHAR(50): Creates a genre column to store the genre of the game, with up to 50 characters.
• release_date DATE: Creates a column to store the release date of the game in date format (DATE).

Notes:

• It is important to define the correct data types for each field according to what you want to store.
• Defining a PRIMARY KEY ensures that the values ​​in this column are unique for each row.

View contents of a table

The DESC command (or its full form DESCRIBE) is used in MySQL to display the structure of a table. It shows the column names, their data types, and other relevant information, such as whether the column allows null values ​​or is part of a primary key.

DESC table_name;

ou

DESCRIBE table_name;

Example:

Suppose you want to see the structure of the games table created earlier:

DESC games;

Example output:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int          | NO   | PRI | NULL    | auto_increment |
| title        | varchar(100) | YES  |     | NULL    |                |
| gender       | varchar(50)  | YES  |     | NULL    |                |
| release_date | date         | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Output Explanation:

• Field: Column name.
• Type: The type of data stored in the column (such as int, varchar, date).
• Null: Indicates whether the column allows null values ​​(YES) or not (NO).
• Key: Shows whether the column is a key (PRI for primary key, UNI for unique key, MUL for index with duplicate values ​​allowed).
• Default: The default value of the column, if any.
• Extra: Additional information, such as auto_increment.

This command is useful for quickly checking the structure of a table without having to look at the original creation code.