SQL: Creating Tables

In a previous post, I went through some of the history and the basics of SQL. In this post, we will learn about creating tables. As a quick reminder, SQL is used in relational databases. A relational database stores data in a table or multiple tables that are connected to each other in some way. So it is important to know how these tables are created in the first place.

As mentioned in the previous post, there are a number of different ‘dialects’ of SQL and there can be small differences in syntax. For this case, we will be using MySQL.

Creating a Table

To create a table the coding is quite simple. Once you are in the specific data base that you want, you use CREATE TABLE <table_name> () . A quick note: CREATE TABLE and create table are the same. Many programmers will use all capitals so that there is a clear difference between the commands from SQL and the variables used or created. So, it seems pretty simple. You have the statement and whatever table name you decide on. Inside the parentheses is where there can be a bit more confusion.

Inside the parentheses is where you can choose the column names and what data type they are. The first step is to decide on the column name, then you add a space and choose the data type. There is a comma between each column name and data type set. One area of confusion can be caused by the data type.

Choosing the right data type is important because in MySQL the types are strict. That means if the column has a numeric data type, the only entries allowed are numeric in nature. For example, an entry could be 99 , -99 , or 42 . What is not allowed is a string describing a number like "42" or "forty-two”.

While it is possible that this strictness could be confusing at first, it is actually very useful. By doing it this way, if there was a mathematical process used to create a new column, there would be no error. For example, if there is column for the age of a pet. If the table was about dogs, we might want the age of the dogs in dog years. So, the age column would need to be multiplied by seven. However, if MySQL did not have that strictness, that statement might return an error due to trying the multiply a string by a numeral.

SQL Data Types

There are multiple data types that can be used in a table. They are separated into several categories: numeric, string, date and time, spatial, and json. Each of these categories have several data types that can be used depending on the need for the column. In the numeric category, there are:

1. Integer Types: INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, and BIGINT
2. Fixed-Point Types: DECIMAL, NUMERIC
3. Floating-Point Types: FLOAT, DOUBLE
4. and Bit-Value Type: BIT

HERE’S THE CONFUSION!!! What data type are we supposed to use? Unfortunately, that depends mostly on what type of data you are expecting to be in the column. INTEGER and INT are the same, just different ways to write it. The differences in the Integer types is the range and the storage required. TINYINT has 1 byte of storage and can hold unsigned numbers up to 255, while INT needs 4 bytes of storage and can hold unsigned numbers up to 4,294,967,295. In DECIMAL there are precision and scale attributes. The first number, precision, determines the number of significant digits that the data type can store, while the scale determines the number of digits that can be stored after the decimal point. Again, it depends on what type of data you are expecting the column to hold.

The string category data types consist of:

1. CHAR
2. VARCHAR
3. BINARY
4. VARBINARY
5. BLOB
6. TEXT
7. ENUM
8. and SET

Some of the differences are once again how the data is stored and how much storage is required. Let us use CHAR and VARCHAR as an example. If 42 is chosen as the length of the data type CHAR, if the value entered is less than 42, the entry will be right-padded by adding spaces to the end until the value takes up 42 characters. In VARCHAR, the length is variable. The value could be up to 42 characters long. However, if the value is 20 characters long, the value entered will be 20 instead of the 42.

The most common data types that are likely to be used while learning are INT for numerical values and VARCHAR for string values. That is not say it isn’t possible to see others depending on the data you are using.

An Example of Creating a Table

For a simple example we can use a phone book. The most basic phone book will have names and phone numbers. So there will be two columns: the name, which will be a string and the phone number, which will be numerical. If the phone book only uses the first and last name, even the longest names are unlikely to be more than 42 characters long. The phone number in the case of a country like the USA is 10 digits long. Now that we have the expected information, we can create the table.

CREATE TABLE phone_book (
    name VARCHAR(42),
    phone INT
);

I hope this post useful in your exploration of creating tables in MySQL. Thank you for reading.

For more Documentation on Data Types:

MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types