Now to create a new table,
we can click on tables here,
right click on that and choose, create table.
Alternatively, again, with that database selected,
we can also click on create new table up here
and this screen will open up.
If you get an error that no schema was selected,
you might want to double click onto your schema first
and here we can now create a new table in that database.
Now, a table is a bit more complex than the database itself
because now we have to define the columns
that we wanna have in there.
Because remember in relational database management systems,
it's all about having clear schemas and structures
in your database tables.
So that it's very clear, which fields,
every record that's stored in this table should have.
So if it should have some texts or some numbers,
so you have to clearly define your schema for a table here,
but it actually all starts with a name.
And here I will now name this restaurants
regarding the naming convention.
It's generally up to you,
you wanna avoid white spaces here though
instead the convention is to name your databases
and also your tables, all lower case.
And then regarding the tables,
it is quite common to choose to plural form here.
And since we store a bunch of a restaurant items
in that table, we can choose the restaurants
as a table name.
You could all just name it, restaurant, whatever you want,
and you will see different approaches out there in the wild.
I'm a fan of using the plural form here
and name it restaurants.
If your table named would be a name that consists
of multiple words actually
you would separate them with an underscore between
don't use white space here.
So now that's my name,
again, we can choose how characters should be stored
and we can keep the defaults here.
We wanna keep that engine here, which is kind of the,
the storage engine under the hood
that will be used by the SQL server for storing the data
that will end up in the table on actual files,
because remember a database still in the end stores,
data and files,
but it handles that file access for you
in a very efficient way.
And there are different storage engines that can be utilized
for that, but InnoDB is a great engine
and unless you really know what you're doing,
you should stick to it.
Now we don't need a comment,
but instead now here in this area, we can add our columns.
We can define the schema, the structure of this table,
and we can add a first column.
So a first field that has to set by every record
that's added to this table by clicking in here,
double clicking in here,
and then we can add a new column and it is quite normal
that you want to have some unique identifier
for every record that will end up in that table.
And very often data's called ID.
So that's not a must do,
but it's very common that you have a column called ID
in your database table.
Now, the next thing we have to choose
after choosing this name is the data type.
And actually here, if you click in there,
you got a drop down with a lot of options.
MySQL or SQL in general supports a broad variety
of data types that can be stored in a database.
There are some exotic ones which you will rarely use
and then there are a couple of very common ones.
For example, INT is used to store numbers
without decimal places,
Varchar would be used for storing texts that's not too long.
So short pieces of texts like a name or an address.
Decimal, can be used for storing data with decimal places
where you need a high amount of precision, for example,
for storing prices or anything like that.
Datetimes stores a date and a time as the name suggests
and then I'm not going to go through all these data types.
Again, the official docs or a full course
are the place to go for that,
but a couple of other interesting types
that I wanna mention briefly are DATE,
which is used to just store a date without a time portion,
then also Float to store a number with decimal places
that does not need to be too precise.
So where you don't need a high amount of precision
for these decimal places.
And then we, for example,
also have Boolean in case you wanna store true or false.
So a yes or no answer in one of the fields
of your database table.
Now, here I'll stick to INT because the ID
which I wanna assign to every record should be a number
one, two, three and so on
that is different for every record.
Now this should also be the main way
of identifying entries in the restaurants table.
So each restaurant should have a unique ID.
The names might be duplicate,
there could be two restaurants with the same name
in different locations,
but each restaurant should have a unique ID
and this unique identification criteria,
which you need for every database table,
every database table needs at least one field,
one column that holds unique values,
which you can use to clearly identify your records.
This kind of field is called a Primary Key.
And that's why we have this checkbox here in this PK column.
This makes this a Primary Key.
You can also see this down there in the bottom right.
And that simply means that automatically MySQL
will ensure that you cant store duplicate values
in that ID field.
So if you try to store an ID that already was stored before
you would get an error,
if you already have a restaurant with the ID for,
and you would try to store another restaurant with the ID
for you would get an error
and you cannot also check this AI box here
to turn on auto incrementing,
which means that whenever you add a new record
to that restaurant's table,
you don't need to set the ID on your own,
which would mean that you, first of all,
have to look at all the other entries to find out which IDs
are still available,
but instead, whenever you store a new entry
in the restaurants table,
if AI is checked here, if Auto-Increment is checked,
then MySQL this server will automatically
assign a new unique ID to be added entry.
And it will simply take the ID assigned
to the previous entry and incremented by one,
so add one to it.
And that's why I'm using INT as a data type,
because I wanna use a number without decimal places
as an ID so that it can be incremented
for every new record.
I also have an NN checked, which stands for not null,
which simply means that this field must always be set.
You are not allowed to add an entry
into this restaurant's table where you don't have an ID.
Now we don't need to check the other options here.
Unique sounds like you need to check it
and you can check it but if something is set
as a Primary Key, it's automatically unique.
So you don't have to check unique
if you already set it as a Primary Key,
we also don't need the other options and therefore,
we now have our first column.
Now we can add a second column by simply clicking
in that second row here.
When we define our schema and then, well,
it comes down to what else we wanna store.
And for example,
we might wanna store the name of a restaurant.
So I'll give this column a name of name
and the data type here in deed should be some text
and Varchar is the right solution here
if it's not super long text for a super long text,
you would have the text type here
or even long text if it's a very long text,
but here I don't want to have an overly long text,
just a short piece of text and therefore I'll use Varchar
and between those parentheses,
you can then define the maximum amount of characters
that are allowed here.
And I'll choose 255 here, which is a common value.
So therefore for all set this, and I'll also check not null
because I don't want an empty value here.
You have to provide some name.
I don't check anything else because duplicate names
are allowed.
It's not my Primary Key instead, that's my ID
and it's also not auto incrementing or anything like that.
Now we might want to store more data
here I'll just add one more field
and that will be the type of restaurant
and I'll also set this to be text.
Also use Varchar 255 here, and also make this not null.
So that would be something like Italian or Indian or German.
And with that, I have these three columns,
this is not the final scheme I want.
Instead we will soon plan this schema in more details,
here we're just getting started with this
MySQL Workbench tool
and with these SQL language in general.
So we will come back to this later
and create more elaborate tables
once we have our final schema in mind,
but to get started,
this doesn't look too bad and therefore now
with that all defined,
we can leave all the rest and click on apply here.
And again, we then see the SQL statement
that will be executed.
And just to make this very clear,
you don't need to use this graphical user interface,
which we have used here.
You don't need to do that,
neither for creating a database nor for creating a table
once you know SQL, once you know it really well,
you can of course, write those statements on your own
and then just execute them
either through a tool like this, or in other ways,
like directly connecting to a database
through the command line.
But that's all a bit more advanced
and something not the main goal of this course.
Therefore this graphical user interface,
MySQL Workbench is of course perfect to get started
with SQL and to get started with working
with that database.
So therefore here,
we got a number of SQL statement that was generated for us
and here we see that the keywords are a create table.
Then we tell the server for which database
we want to create a new table
and then the table name separated by a dot.
So it's database name dot table name
again, we got those back ticks here around our names,
but if there are no special characters in those names,
you can actually omit these back ticks here,
you don't need to have them here.
So just to prove this I'll remove them
and then between parentheses here,
we have the different columns that we wanna define.
The names are between those back ticks
and again, they are not required here
because there are no special characters in our names.
If there would be, we would need these back ticks.
And then we have to configuration for every column
after the column name.
And then the different columns are separated by commas.
And here we can see that for the ID,
it should be an indenture,
not null and Auto-Increment, names should be a Varchar
and not null and type as well.
And then after defining all these columns,
we also make it clear that the Primary Key off that table
will be the ID column.
And we can all to remove the back ticks here
last but not least.
The engine that should be used for this table is defined
and if we now click apply, that's executed
and if I click finish, now, if we expand tables here,
we see our table.
And if we expand that,
we can also have a look at the columns that we defined here.
And that's, by the way,
just some graphical representation of our tables,
which we can use here in MySQL Workbench
to analyze our database and the tables we got in there.
So now with that,
we created our table in our database
and we saw our first SQL statements.
Now let's also dive into more SQL statements
and add first pieces of data into this restaurant's table
before we then take what we learned thus far
and apply it to the main goal of this section
to build the real schema and the real tables
for this restaurants reviews application.