So now we had a first look at
inserting data and selecting data.
What's of course missing,
is that we can also update and delete data.
And therefore that's what we'll have a look at now.
I'll again create a new query here in a new tab,
and we can update elements in a database table
with the UPDATE command.
Now with the UPDATE command, we first of all
need to tell SQL or the MySQL server,
which database table holds the value which we wanna update.
In my case, that's of course the restaurants table.
Then we use the SET keyword to make it clear
which values or which columns in that table
should be set to a new value.
And for that I could, for example say,
that I want to change the name to Web Dev Meals.
I want to change the name of the restaurant
with the name Web Dev Mealery, to Web Dev Meals.
Now this instruction would set the name to that value
for all the records in the database table.
Sometimes that is what you want.
But in this case here, I only wanna adjust the column value
for a single record in this database table.
I wanna adjust the name off the restaurant
with the name Web Dev Mealery.
And that's why we can also use
the WHERE keyword here as well, to narrow down
the amount of records that should be updated,
because you can update all the records,
a bunch of records, or a single record,
whatever meets your condition here.
In this case, I know I only wanna update
the restaurant with the ID one,
and therefore we can say where ID equals one,
like this.
If I now execute this it succeeds.
And if I quickly run another statement here
where I select all the values from restaurants,
like this, we can see that now, indeed,
this was changed to Web Dev Meals.
So updating this value worked.
Now what if we want to get rid of it altogether?
Well, then we can write another query
where we delete our record.
For this we use the DELETE keyword, the DELETE command.
And then we first of all need to add FROM
and specify from which table we want to delete a value.
For example, in this case, restaurants.
Now, again, we also need to specify which records to delete.
And that's again where we use WHERE to narrow down
the records that should be deleted,
because with DELETE, you can delete one record
or multiple records depending on the condition you add here
and how many records get selected by that condition.
So if I know I wanna delete the restaurant
with the ID one, I can say this.
And here, as well as with the UPDATE command,
you see why that ID thing is so important.
Why having a unique identifier per record is so important.
If we would want to delete the first restaurant
with that name, we could also select it by name,
but if we have a large amount of restaurants
in our database, there is a high chance
that we have multiple restaurants with the same name,
especially for something like Pizza House
or anything like that.
That's why we want some unique identifier like the ID
to make sure we really only update or delete
the record we wanna delete or update.
So therefore here, that's not a command we can execute
to get rid of that first restaurant.
It succeeded, and if I now select all my data again,
and I run this query again, we indeed see that now
we only have two restaurants left.
Please note that the ID of the remaining restaurants
did not change.
And that hopefully makes a lot of sense.
It would be very bad if you could delete one item
and all the IDs of all the other records would change.
You don't want to have randomness like this
in your database.
Your other records should not be affected
just because you deleted a record.
So these records were not affected
but the first restaurant was deleted.
And therefor now we had a first look
at the basic CRUD functionality, at inserting,
so creating, reading, updating, and deleting elements
or records into our database tables.
We also saw how we can create a database and a table
in the first place.
And we had a first glimpse at the SQL syntax
and how we write these SQL instructions.
It's definitely quite different from JavaScript.
We don't have files with all those instructions,
which we then execute.
Instead, we can just execute these instructions on the fly
whenever we wanna perform a certain action.
But on the other hand, that makes a lot of sense hopefully,
because whilst a web server is an ongoing thing
that needs to react to different incoming requests,
this here really are one-time actions
that we wanna execute to perform a certain action.
And this is how SQL generally works.
Now with that, let's go back to this course goal,
which we defined and let's plan the actual schema
and the amount of tables we need
to store the restaurant data and the reviews.
And then let's implement that all
with our newly gained SQL knowledge.