Informatec Digital » Databases » Creating Tables in MySQL: Examples and Explanations

Complete guide to creating tables in MySQL: concepts, examples, and best practices

What is MySQL and why is it important?

Creating tables in MySQL: Basic concepts

Create a database in MySQL

CREATE DATABASE nombre_base_de_datos;

Create a table in MySQL

CREATE TABLE nombre_tabla (
  columna1 tipo_dato,
  columna2 tipo_dato,
  columna3 tipo_dato,
  ...
);

Data types in MySQL

• INT o INTEGER: to store integers.
• VARCHAR: to store text strings of variable length.
• CHAR: to store fixed-length text strings.
• DECIMAL: to store decimal numbers.
• DATE: to store dates.
• TIME: to store hours.
• DATETIME: to store combined dates and times.

Creating tables in MySQL: Examples and explanations

Example 1: Create a users table

CREATE TABLE usuarios (
  id INT PRIMARY KEY,
  nombre VARCHAR(50),
  email VARCHAR(100),
  fecha_registro DATE
);

Example 2: Create a product table

CREATE TABLE productos (
  id INT PRIMARY KEY,
  nombre VARCHAR(100),
  precio DECIMAL(10, 2),
  fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

How to create a table in MySQL with constraints?

• Primary key: A primary key is a column (or set of columns) that uniquely identifies each record in a table. To create a primary key in MySQL, you can use the following syntax:

CREATE TABLE nombre_tabla (
  columna1 tipo_dato,
  columna2 tipo_dato,
  ...,
  PRIMARY KEY (columna1)
);

• In this example, the column “column1” has been defined as the primary key.
• Foreign key: A foreign key establishes a relationship between two tables based on a common column. To create a foreign key in MySQL, you need to follow these steps:
  1. First, create the main table that will have the primary key:

CREATE TABLE tabla_principal (
  id INT PRIMARY KEY,
  ...
);

CREATE TABLE tabla_secundaria (
  id INT,
  ...
  FOREIGN KEY (id) REFERENCES tabla_principal(id)
);

• 1. In this example, the column “id” in the table “child_table” has been defined as a foreign key and references the column “id” in the table “parent_table”.
• Integrity Constraints: Integrity constraints ensure that data stored in a table follows certain rules. For example, you can set an integrity constraint to ensure that a value is not null or that it conforms to a specific format. Here is an example of how to create an integrity constraint:

CREATE TABLE nombre_tabla (
  columna1 tipo_dato CONSTRAINT nombre_restriccion NOT NULL,
  ...
);

• In this example, the constraint “constraint_name” ensures that the column “column1” cannot contain null values.

Creating tables in MySQL: Advanced considerations

How to create a table in MySQL with primary and foreign keys?

CREATE TABLE tabla_principal (
  id INT PRIMARY KEY,
  ...
);

CREATE TABLE tabla_secundaria (
  id INT,
  ...
  FOREIGN KEY (id) REFERENCES tabla_principal(id)
);

How to create a table in MySQL with indexes?

CREATE TABLE nombre_tabla (
  columna1 tipo_dato,
  columna2 tipo_dato,
  ...
  INDEX nombre_indice (columna1, columna2)
);

How to modify a table in MySQL?

ALTER TABLE nombre_tabla ADD columna tipo_dato;

ALTER TABLE nombre_tabla DROP COLUMN columna;

ALTER TABLE nombre_tabla MODIFY columna nuevo_tipo_dato;

How to drop a table in MySQL?

DROP TABLE nombre_tabla;

How to query a table in MySQL?

SELECT * FROM nombre_tabla;

How to import and export data in MySQL?

How to backup and restore a table in MySQL?

How to optimize table performance in MySQL?

How to fix common problems when creating tables in MySQL?

Conclusions