I
am sure you are now keen to begin to use this software to organize and manipulate data in
practice.
Let’s start by creating a database.
We will use the ‘Sales’ database.
We will show you how to create your own database, also called schema, starting from scratch.
Only in this way can you grasp just how powerful SQL can be.
Therefore, first we will define a database, and then create the other components, such
as tables and fields.
We will use the Data Definition Language CREATE statement to create a database.
Quite intuitively, the command we need in this case is CREATE DATABASE.
The syntax to abide by is CREATE DATABASE, the optional statement [IF NOT EXISTS], the
name of the database, and finally, a semicolon.
Let’s break down this entire line to understand the meaning of each element.
CREATE DATABASE will obviously create a database as an abstract unit.
This means that for the moment the database will not contain any data and will simply
represent an object ready to be filled with information.
Think of it as creating a canvas on which you would be able to draw your relational
schemas later.
IF NOT EXISTS will verify if a database with the same name exists already.
If it does, this will impede us from creating a database with the same name.
The brackets around indicate that the statement between them is optional in the syntax.
Optional means you could either type or omit the statement.
This is the situation with [IF NOT EXISTS] – even if you don’t include it the code
will run anyway.
Nevertheless, we advise you to always use it because it allows you to avoid potential
errors when working with large amounts of data.
It is good to build the habit of attaching this phrase straight after the CREATE statement.
Next, we will have to specify the name of the new database.
We strongly suggest you give a name that is short but at the same time as related to the
content of the data as possible.
Try to avoid assigning names like “database_01” or “database_about_sales_in_New_York”.
In our case, the data we will work with is about the customer sales registered in a company.
For this reason, we will call the database “Sales”.
Whether you use uppercase or lowercase letters only, or both – it’s the same for SQL.
The SQL code is not case sensitive neither when assigning names of objects nor when writing
queries.
Otherwise, in this element the quotes are optional – you could either say “Sales”
within quotes, or without using any quotes.
It makes no difference.
Finally, you should always end your statements with the semicolon character.
It functions as a statement terminator.
Yes, it is often true that your queries will run properly without it, but this is not the
point.
In longer scripts, when your code contains more than a single statement, the semicolon
is indispensable.
So, please, try to end your statements with this character – this will not only help
you avoid errors sometimes, but will also improve the readability of your code.
Ok!
So…
I guess we are ready to type down our first query and run it, aren’t we?
Let’s type CREATE DATABASE IF NOT EXISTS, without brackets, “Sales” (keeping in
mind that we are allowed to omit the quotes without any problem), and semicolon.
Now, it is important to note that this entire line is nothing but a piece of text until
you execute it.
To execute, or run, a query, you must press this lightning symbol.
The alternative is to implement the shortcut combination Ctrl, Shift and Enter.
So, what are we waiting for?
Let’s press this button and see the output of our work.
At first sight, it seems nothing happened.
This is not true though.
When new databases, schemas, or tables are created, we always need to refresh the content
in the “Schemas” section at the bottom left part of the screen by pressing this little
button here.
Ha!
The “Sales” database is ready for use!
Great!
Please, remember that you could have achieved the same output if you had used CREATE SCHEMA
instead of CREATE DATABASE.
Considering that database and schema are practically synonyms, this seems like a logical alternative,
right?
Well, we just completed our first task – creating a database.
Congratulations!
In this video, we will look at the MySQL workbench interface in more detail as it can help us
manage our database better.
The symbol located before the name “Sales” indicates us that the object in question is
a database.
You’ve seen it before, haven’t you?
😊 It stands for a database.
Moreover, by pressing this miniature triangle, we see how this database can be broken down
into four components – Tables, Views, Stored Procedures, and Functions.
Since we have not created any of these for the moment, nothing will happen if you click
any of the respective names.
It is important to be familiar with this little symbol that appears when you hover above the
name of the database.
It is a feature of the MySQL workbench you will love when you fill in your database with
data.
Once you press it, a new tab containing information about the database will open in the right
part of the screen.
Although all these fields are currently empty, when we fill our database with information,
we will be able to inspect its tables, columns, indexes, triggers, views, stored procedures,
functions, and grants in a great level of detail.
All right.
We just saw how to obtain information about the “Sales” database and some of its components.
However, we don’t know how to tell the computer that “Sales” is going to be the database
we want to apply the SQL code and operations to.
After having created the database, you must type ‘USE “Sales” semicolon’ in the
SQL query editor field.
By running this line, you will select the existing “Sales” database and you will
be ready to apply various SQL commands and data manipulation tasks on it.