After the introduction to tables in databases, now it’s time to start creating tables based on, as we programmers say, “chopping” SQL sentences.
I recommend that you carefully read the points that I explain below and try the examples presented, if you do, I assure you that you will be creating tables in a few minutes.
I will explain to you below:
- What is the SQL statement to create a table.
- How to drop a table with SQL.
- How to create columns for a table with SQL language.
- How to add columns to an already created table.
- How to modify a column with the ALTER statement.
- Create tables with SQL statements
- Bases to create columns of a table in SQL language
- Define SQL columns in the CREATE definition of a table
Create tables with SQL statements
For create tables in SQL we have to use the CREATE command. This command, which allows you to create different components in a database, is also necessary to create tables.
The SQL CREATE statement it must be accompanied by the type of element that we are going to create, in this case, it will be TABLE (a table). Finally, as the third element, we will specify the name of the table to create.
Remember that, within the same database, the names of the tables cannot be repeated, they cannot contain spaces or special characters such as accents or hyphens.
With that said, let’s see example to create a table Video game call:
CREATE TABLE videojuego ( );
Note the order of the commands and the name of the table as a configuration parameter. The parentheses delimit the configuration of its columns. In this example it is empty because we have not yet covered the creation of columns in a table.
As an example, and so that you can test the command in a DBMS like MySQL, I leave you the following query that you can try yourself:
CREATE TABLE videojuego ( id INT )
Bases to create columns of a table in SQL language
Create columns in a table is a task that always goes hand in hand with creating tables, since to create a column we always need a table that serves as a destination.
We can create columns in a table in two different ways:
- Defining them while creating a new table.
- Adding them to an already created table.
Define SQL columns in the CREATE definition of a table
For define the columns in the same query creating a new table, we must do it within the parentheses that go after the name of the table in question. The column name should always be followed by the type of information it will store:
CREATE TABLE videojuego ( id INT, nombre VARCHAR(30), productora VARCHAR(50), edad_recomendada INT, puntuacion_usuarios DECIMAL(3,2) );
In this CREATE SQL example I have defined 5 columns with the following types:
- INT or integer numeric value. This type can contain integers, that is, numbers without decimal places.
- VARCHAR(L). This type is used for text strings with a given maximum length ( L ).
- DECIMAL(D,P). To store decimals we can make use of the DECIMAL type by setting the maximum number of digits ( D ) and the number of decimal places after the comma ( P ).
There are many more types of values for table columns, but the most common are:
- INT. for integers
- VARCHAR. For text strings.
- TEXT. For very long texts.
- DECIMAL. For decimals with a certain precision.
- DATE. To store dates.
- TIME. To store time data.
Not to be taken lightly definition of column data typeswhy? Well, because thanks to a correct type definition, we will be able, later, to tell the database system to make certain special comparisons such as: comparison of dates, time, decimals, etc.