So let's get our feet wet with Postgres, so on my Mac, I have it running on windows, you need to
have it running as well and you can see that it's running on a Mac by looking at this window, if you
see it, or coming up here.
And if it says stop, then it's running.
Otherwise get it going, then start Dvor and we'll connect to our localhost connection, which is called
postgrads.
In my case, you may have me may need your something else, but I'm going to connect and I'm going to
look at our bookings database.
We'll double click on that to make that our active database.
So I'm now connected.
So there is nothing in this database right now.
We have under our schemas for public, we have no tables and that's fine.
What I want to do is start playing with some sequel.
So I'm going to come up here and choose a sequel, Ed, and that will pop up a little window that allows
me to start typing some commands.
Now, when you connect to a database and it doesn't matter if it's postgrads, if it's a relational
database like Postgrads or MySQL or Marea DB, you have to interact with it using a language just as
go as a language or as a language.
There's a language to talk to database, and it's called sequel, which is spelled QOL and it stands
for structured query language.
So we don't have a database, we have a database, we have nothing in it right now.
But we can still play with a little bit.
When you want to get information from a database, you're going to select information.
So I'm going to type the command select and I'll just select in single quotes fish and end my command
with a semicolon.
And I can run that by clicking the little button right here and down at the bottom.
It shows me that I selected fish and it says column questionmark, column questionmark because there's
no actual column there.
If we are selecting from a table, there might be or there would be names for things.
Now I can actually name this fish.
I can say select fish as and I would just call it animal with no quotes.
So I'm naming this select and I run it again.
It says now you've selected fish from the column animal.
But what if you wanted more than that?
I can say select fish as animal, ten as a page and then one hundred point one as weight.
So again, I have select fish as animal comma, ten as age, comma and one hundred point one as weight.
And if I run that I will get three columns.
So I have an animal, an age and weight.
And you notice that each of these looks different is what I what I actually entered fish.
I had to put it in quotes because that's character data.
It's string's.
Ten is an integer, one hundred point one is a float, sometimes called a double.
Now this is really helpful.
I can select nothing from nothing or select something from nothing.
It'd be far more useful if we actually had a table.
So what I'm going to do now is create a sample table.
So back over on the left hand side where it says tables, I'm going to create a new table and I will
call my table.
I will call it animal animals, plural, OK?
And it has no columns at the moment.
I need to give it some columns.
So I've I've created I'm creating a table that has not saved yet, so it doesn't exist.
And you can see where it is by looking at this little path at the top.
It's on the server called postgrads in the database called bookings on the schema called public.
And then there's tables and I'm on new table, which will be called animals.
What?
I'm done with it.
So I'm going to give it that name.
And now I want to give it a column.
So I will right click in this area and say create a new column.
Now we are going to use a pattern where every row in a table in our database has a unique I.D., a number.
And the easiest way to do that is by creating a column called ID.
And this is a kind of a pattern will be using throughout this course.
The data type is not VirTra, which would be character data there is called character bearing or voiture.
Instead, I'm going to change it to serial.
So I click click on this, click on the little arrow that shows up here and just look for serial.
So somewhere under here I'm going to have serial serial not serial two but serial.
OK, so that's what I'm gonna choose for its data type length.
I'll leave at 11, which makes I can have billions of records in this table and I will just say not
no, I have to have not known.
And the funny thing about Dvir is when you click on it, you actually have to click on it twice to get
that checkmark and click, OK, so I've now given it one column of type ID length eleven.
It's not know and it's a serial.
Now the serial type will actually do some magic for us.
Every time we insert a record into this row, we insert one line of data, one row of data, which we
call a record.
It will automatically increment the ID by one for us.
OK, now I'm going to create a second column, so create a new column and I will just call this name.
I'll call it animal name, animal name.
And again, I'm going to make that not null because every animal has to have a name and I'll click,
OK, and I'll just save this.
So I click save and it will ask me if I want to create it.
Are persistent in.
Yes, I do want to persistance now I have a table.
OK, so if I look over here on the left hand side I have one under my server postgrads, my database
bookings, my schema public under my tables I have one table called animals and it's currently nothing.
So if I go back to my school by Sehgal editor here now, I can make my select statement a little more
useful.
I can say select star, which means get everything from animals and hit return and I'll get nothing
when I run this because there's nothing in there right now.
So it shows me you have nothing.
You have do have a column ID and a column animal name, but there's nothing in there.
So let's go back to animals and this time let's click on data.
And I want to add a row of data to this table.
And I do that by coming down here.
It's not intuitive.
This is this is a nice free ed for databases or visual GUI tool to connect to databases.
But you could absolutely tell it was designed by programmers with no thought for interface design.
So it's a little awkward.
There are better ones out there, but you have to pay for them.
So I'm going to click on this little button right here.
I'm going to add a new row.
So I click that and it says ID is no, I'm going to leave that.
No, because I want my serial data type to put the number one in there, because this will be my first
record.
But I'll put in dog as an animal name and then hit enter.
And when I when I'm done with that, I come down to the bottom left hand corner here and say safe.
So now I have one row of data.
Let's go back to our SQL editor and run the same command again.
And now it says, I have a dog.
All right, let's go back to animals and add another type.
So I'll add another row here, which I'll call Cat and I'll save that.
And I'll add another row here by clicking on this little plus icon again.
And I'll call this one a fish and I'll save it.
All right.
Now let's go back to our school editor and run this script again.
Run this little command again.
That's a bit of sequel.
And now I have three rows.
Now, I can actually change this a little bit.
I can say select start from animals where the idea is greater than one.
So this actually filters are select statement.
So now I can select everything from animals where the idea is greater than one.
So what should happen is they should give me cat and fish.
And it does or I can say where ID equals to one and run it again and I should just get a dog and I do
or I can say where name equals animal name is not a name, animal name equals and then add single quotes.
I'm going to put lowercase cat see what happens.
I get nothing, but if I go uppercase cat, I can run it and now I get Cat.
So there is a way of selecting data.
Now, we can also insert data into a table by saying insert into it, and then you give your table name,
animal name or animals.
And then in parentheses, I'm going to say animal name, which is the one row that I want to affect.
I don't want to manually insert a value into it because I want my serial data type to handle that for
me.
Then I type values and then in parentheses I will put porc and in my command with the semicolon and
run it.
And it says query finish time in certain animals.
Now let's go back up to here and change our query to select Star from Animals and run.
And now I have a horse in there, so there's a way of inserting data into it.
I can also delete information from it.
I can say or update it.
Lets them do the update.
First update animals set animal name equal to German ship bird where I equals one.
And you can see it's very similar to selected.
You can have this where clause that allows you to specify what you want to effect.
I only want to effect ID one which currently is set to dog under animal name.
I want to change its name animal name from what it is to German Shepherd.
Let's run that.
And now let's go back and select a star from Animals and enter that and there we are.
So German Shepherd has been updated, dog has been changed to German Shepherd.
I can also change the I can delete an individual record, but by saying delete from the table name animals
where ID equals four and that will delete the harsh record.
So I run that and go back and select star again by clicking in that line.
I want to run and there are horses gone.
So now we can create a table, we can insert information, we can update information, we can delete
information and we can select information.
And that is the basic principle for all of the skill commands we're going to be doing.
Now, some of our queries will get more and a query is just selecting information.
Some of our queries will get more sophisticated as time goes on because we'll be able to select information
from multiple tables or a subset of information from a given table.
But this is the basic principle.
It's called CRUD, which is a rather unfortunate acronym, but it stands for Create, Read, Update
and delete.
We can create information, read information, update information and delete information.
And that is really not that difficult.
Now, SQL is an extremely powerful language and it would be a course in itself to go through it in incredible
detail.
But we're going to learn enough to do everything we need to do to make our Web application work.
So when you think about it, we're going to have to have a table for, say, reservations to persist
information to the database.
When people fill out the reservation form, we're going to have to have some way in the database of
storing information for individual reservations by date.
So we'll be able to ask the database, hey, is the date, say, January the 1st to January the 3rd?
Is that available for the general quarters room?
And that's really not that difficult.
So we'll start this process in a little while.
But right now I want to get out, get used to using a database, and then we'll have to go through the
process of connecting it to a Web application and writing all the procedures to manipulate the data
as required.
All right.
That's enough for this time around.
Let's move on.