Lesson 2 - MySQL step by step: Creating a Database And a Table

In the previous lesson, MySQL step by step: Introduction and installation, we introduced relational databases and prepared the environment. In today's MySQL tutorial, we're going to create a database and a table in it.

Let's create a database, we usually need just a single database for one project (one website).

In phpMyAdmin, we'll click on the Database tab at the top. We'll fill in the database name (e.g. database_for_web). In databases, we name items using just English alphabet (without accent characters) in lower case and we separate words with underscores. I hope it's clear why using accent characters isn't a good idea, the upper and lower case letters are distinguished on Linux and most servers are running Linux. We'll set the encoding to utf8_general_ci. It's a very good idea to use this standard encoding for our database. Even if the application which later communicates with the database used a different encoding, it can be easily forced later and the data would then be converted to this encoding. However, we'll almost always use utf8 in our application, which communicates with the database. We'll confirm.

This step has almost always been already done by our webhosting provider, and we often get just one database named as our account (something like mywebsite.com1). We have everything set up to learn the SQL.

The SQL language

SQL refers to Structured Query Language. It's a declarative language. While in imperative languages, we actually tell the computer what to do, step by step, in declarative languages, we just specify what result we need, and we're no longer interested in how the computer achieves this result. That's why database queries are simplified to something like "select 10 users ordered by ranking". The database can understand such a query, converts it to some of its own instructions, and then processes it. Then it really returns the result, even we don't know how it's been done. If the command seemed exaggerating to you, it's not the case, the commands really look close to this.

SQL was originally named SEQUEL (Structured English Query Language) and was developed in IBM's laboratories to create a simplified English language to communicate with the database. The SQL (relational) databases were then widely adopted. Today, practically nothing else is being used, and although SQL has significant disadvantages in object-oriented programming, companies don't want to switch to anything else (although there are alternative solutions). But that's another story.

Creating the database using the phpMyAdmin's interface resulted in SQL code being generated and executed. A new database has been created by it. The code looked like this:

CREATE DATABASE `database_for_web` CHARACTER SET utf8 COLLATE utf8_general_ci;

You can view all the commands generated by phpMyAdmin by clicking the "Console" button at the bottom of the window.

You probably understand the CREATE DATABASE part. CHARACTER SET is a set of all the characters that a database can contain. COLLATE defines collation, it's usually related to the character set and determines how the characters are ordered after each other. This is useful for foreign languages, for example, uft8_czech_ci would define that "Č" comes after "C" and so on. We set both to UTF8 and specify the collation we want, usually just the general English one.

In SQL, we usually write commands in upper case letters, because that way it's better distinguished from the rest of the query or from our application code (for example, in PHP). We write table names, column names, and other identifiers in lower case and using the underscore notation. It's a good habit to wrap these in backticks (on the QWERTY keyboard it's the key under the Esc key).

Try to remove the database (the "house" icon in the left pane -> Database tab -> check the field for database_for_web and then Delete).

The database has disappeared and we can see that phpMyAdmin has generated another query (these queries are often displayed after we click on something). This time the query was:

DROP DATABASE `database_for_web`;

There's probably nothing to explain. We already know 2 SQL statements, creating a database and removing it. However, we won't use either of them in our applications, since we create a database only once when creating the project and we can do this simply in an administration tool. The same goes for creating tables. It's the manipulation with data using SQL what will be really important for us and we'll get there soon.

Executing SQL Queries

Now, we'll create a database once again, but instead of clicking it, we'll submit an SQL query to do it. In the top bar, click on the SQL item. A window opens, where we can write SQL queries directly. We'll insert our query to create a database (from above) and execute it:

We can see, that we've achieved the same result as when we just clicked it all. Open the database in the left column. phpMyAdmin offers us to create a table there. Remember the example of the user table that we showed in the previous lesson? It had the first name, last name, date of birth, and the number of articles columns. We already said that each table should have a column that has a unique value for each row. So our table is eventually going to have 5 columns, we'll name it user. Enter the values into phpMyAdmin:

Quite a lot of fields are displayed, but you don't need to worry about it. The first column is for the column names of the table. Fill in the names of our columns, which are user_id, first_name, last_name, birth_date, and articles_count. It proved to be a good practice for me to always name id columns with the table name prefix, but it's not required.

Let's move on to the 2nd column, where the data types of individual table columns are defined. We have INTs predefined, which are integers. There's a lot of data types, but we're going to get by just a few (maybe even till the end of the course). We'll keep INT for user_id and set the first_name and last_name to VARCHAR, which is short text. The date of birth will be DATE. The last one, article_count, will be set to the INT type. In time, we'll describe other data types, but I won't bother you with them now

The 3rd column, Length/Values is useful only for the VARCHAR data type columns and specifies the maximum number of characters, we'll set the first_name and last_name to 60 characters.

Since the MySQL version 5.7, it's important to keep an eye on the 4th "Default" column. In earlier versions, if you didn't specify values for all the columns when inserting a new row into a table, MySQL automatically inserted some default values. In the new versions, it'll run into the error state: Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 1364 Field 'xxx' doesn't have a default value and the query won't be executed. So, if you're creating a column that doesn't always have to have its value specified, it's better to set a default one. Note that in our case, when inserting a new user, the number of their articles may not be specified because the default value is set to NULL, which means it hasn't been specified.

Next, there are other columns that aren't so important to us and we're not going to fill them in. The last thing, we fill in, is the Key column for the user_id column. We'll set the key to PRIMARY and tick the check-box next to it, marked A_I (as Auto Increment). By this, we've set the user_id column as the primary key of the table. Keys (sometimes called indexes) allow us to identify an item in the table. Every table should have such a primary key (though it theoretically doesn't have to). For example, if we wanted to delete a user, we'd delete them by this key (by user_id). If we deleted them by name, we'd delete multiple items, since there might be more users with the same name. By user_id, we only delete the one we need. The Auto Increment checkbox causes that the value of user_id is incremented automatically and the users are numbered.

By the way, notice the Add field option. It's for the case when you think you need just few fields at the beginning, and while designing the table, you find out that it needs to have more. Now we save the table.

The table will appear in the left column, we can click it but it's empty now. The SQL query to create the table would look like this:

CREATE TABLE user (
  user_id int AUTO_INCREMENT,
  first_name varchar(60),
  last_name varchar(60),
  birth_date date,
  articles_count int,
  PRIMARY KEY (user_id)
);

The first line is clear, the other lines define the individual columns of the table and their data types. For the VARCHAR type, we also define the number of characters. AUTO_INCREMENT next to the user_id indicates that the database will assign the ID of the previous user + 1 to each new user. Finally, we set user_id as the primary key of the table.

Deleting a table is the same as deleting a database. You can delete it in phpMyAdmin after opening the corresponding database, the SQL would be:

DROP TABLE `user`;

Try to drop the table and then create it again using the SQL query. In the next lesson, MySQL step by step: Inserting And Deleting Table Data, we'll insert some data into it