Database

Home / Topic / Database / CREATE Operations in SQL

CREATE Operations in SQL

In this chapter, we will learn how to perform CREATE operations in SQL databases, focusing on database creation, table creation, and schema definition. The CREATE operation is fundamental in the database design process, as it allows us to establish the structure we will use to store information.

Creating a Database

Before we can store data, we need to create a database. Below is how to create a database in MySQL:

sql

"This command creates a database named my_database. We do not need to specify any other parameters unless we want to define a specific character set or collation."

We can verify that the database was successfully created by executing the following command to list all databases in MySQL:

sql

"The SHOW DATABASES command shows us all existing databases on our MySQL server."

Creating Tables

Once we have a database, the next step is to create tables. Tables are where data is actually stored. Each table must have a unique name within the database and must be composed of columns that define data types.

Creating a Table in MySQL

sql

"Here we are creating a table named users with four columns: id, name, email, and created_at. The id column is an integer acting as the primary key, name and email are text strings, and created_at is a field that stores the creation date and time of the record."

Primary Keys and Uniqueness

It is important for each table to have a column that acts as a primary key. The primary key ensures that each row in the table is unique and can be used to uniquely identify each record. In the previous example, the id column is the primary key.

Additionally, we can set uniqueness constraints on other columns, as we have done with the email column in the previous example.

Defining Data Types

When creating a table, it is also crucial to correctly define the data types for each column. Some of the most common data types in MySQL include:

INT: Integer.
VARCHAR(n): Text string with a limit of n characters.
DECIMAL(m, d): Decimal number with m digits, of which d are decimals.
DATE: Date (without time).
TIMESTAMP: Date and time.

Below is an example of how to create a table with different data types:

sql

"In this example, the products table has a variety of data types. The price column uses the decimal type to represent the price with two decimals, while release_date uses the DATE data type to store the release date."

Creating Tables with Relationships

In many cases, tables will be related to each other. For this, we use foreign keys, which establish a relationship between rows of one table and another.

Example of Creating Related Tables

sql

"In this example, we are creating a table orders that is related to the users table through the user_id field. The foreign key establishes that each order is associated with a user."

Summary

In this chapter, we have learned to use the CREATE command to create databases and tables in SQL. We have also seen how to define columns, data types, and establish relationships between tables. These are the first steps to efficiently structure our databases.

In the next chapter, we will explore how to insert data into the tables we have created using the INSERT operation in SQL.

Back to previous chapter

Mark as read and continue

Ask me anything