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
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
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
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
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
user_id and set the
VARCHAR, which is short text. The date of
birth will be
DATE. The last one,
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
data type columns and specifies the maximum number of characters, we'll set the
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
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
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
only delete the one we need. The Auto Increment checkbox causes that the value
user_id is incremented automatically and the users are
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