OK so we made it through all of that crud that's sort of a joke.
Anyways we're now talking about string functions.
So basically we've seen how to interact with our database create stuff insert it update delete all of
that.
But now we're going to focus more on select or reading the data out because what we've been doing right
now is just reading the data directly as it is.
So I can say you know I select breed and name from cats and that's it.
I get it back and that's great but there are things that I could do with that data.
So with numbers I might want to add them together or I might want to I don't know reverse a string or
replace all spaces with a certain character or conjoin first name and last name or conjoin you know
two pieces of data together.
All of these are different operations different ways of printing or data out that we can do.
And so we're going to start focusing on them in this section we're talking about string functions.
So there's going to be a bunch of things that we see I already mentioned some of them we can concatenate
words we can take certain parts of a string and leave other parts out.
We can reverse we can uppercase lowercase replace characters all sorts of things.
But before we get there first thing you want to do is show you something that I think is pretty useful
and that we'll use pretty often from here on out.
So up until this point all of the sequel that we've been writing has been manually entered into the
command line into the CLIA here.
So if I want to create a table I have to type create table and then the name of the table cat but let's
say that I mess it up so let's say I typed singular cat which technically isn't a problem per se but
it's a good idea to have things pluralized.
It won't break but.
So let's say I don't notice for a little bit though.
So I create a table cat and we want you know name is Vardar 100.
Age is 10.
When are doing age is it just like that.
Let's say we leave it at that.
Now this isn't a great example because it has a short line.
But if I messed it up I have to go all the way back here and edit it right and do cats like this.
But let's now suppose that I have something even longer and I want to put it on separate lines and format
it nicely like this great table cats and then we have printed c there and you do name is var char and
let's say misfile this like that age isn't it.
And then I realized oh shoot I totally messed up.
I don't have a way of getting back there.
I don't have an easy way of getting here.
So I have to retype the whole line or copy it.
There's no simple way of editing it.
So what I want to show you is that we can run code from a file and that's really really useful.
Running sequel files that we're going to talk about here.
So basically by the end we will be working with files that look like this.
They're nicely formatted they have nice syntax highlighting we can edit them make changes and so on
and run them when we're ready rather than typing things manually into the command line.
That doesn't mean that we won't be typing things here because it's very useful to go away Siri.
It's a very useful tool.
I should probably add that out while it's a useful tool that allows us to explore things try things
out if they're short lived.
It's great but if we're really trying to do more serious stuff it's much easier and better to work in
a file.
You can make edits you can share it with people.
So that's what we're going to do.
So let's go ahead.
I mean get out of this and I'll just hit enter and get an error and that's fine.
So right now I'm in Cat's database and if I do show tables we don't know I do have a catch table but
if I look at it it's empty.
So what I'm actually going to do is drop that catch table drop table cats.
OK.
So what I want to do is remake the cat's table and what I could do is what I just did here create table
cat and do the whole thing.
But rather than that I'm going to do it in a file and then all I'll have to do here is run that file
execute it with one short line so I appear to have a new file.
Again you can do file new file.
You can use the shortcut shown here.
You can right click and do new file as well whatever we do though.
We need a new file and I'm just going to put something in there so create table and I am showing you
creating a table but we'll be putting any sequel queery in here creating a table dropping tables inserting
data selecting any sort of query we want to create table cat that's so easy now to format it nicely.
We'll have a name which is Vardar 100.
Let's do an idea as well.
See how easy that was.
If I made a mistake ID will be an ant not no auto increment comma name or char and then we'll do age
isn't and will do.
Primary Key is cat ID.
So this should all be review.
But what's nice is I have this query like this.
I need to save the file.
So I did command us but you can also go file save.
And we need to give it a name any name will do.
I'll just call it first file and this is the most important part as well.
So that's what identifies the contents of the file as secret code.
So we need that click Save make sure changed up there.
Also you can see we got a syntax highlighting now which is also very useful.
Ok so I'm sure by now you can see the value in doing this.
You have a history of your work.
You can edit it share it with people.
It's just a much better way of doing it.
So how do we actually run it.
Well this is a magic source and then a file on it.
So in our case our file name is first filed as well.
So we just need source file name as well.
So I'm going actually quit out of the Seelye and first thing I want to show you is that it matters where
you are when you initialize your Seelye.
So when I type LS here and if you're not familiar with that it just lists the current files.
So this shows me is that I have book data.
First file and problems are.
So we want first file.
Don't worry about these other two.
So we are in the correct directory to access that file.
So if I start up to a lie with my ask you will see T.L. space Selye and I use the cat app database just
like that I'll make some space.
Now all I do is source first file dot as well just like that.
And we'll see what happens.
It says queery OK.
So now how do we know it works.
Well we could do describe cats and here's our table.
So that's all there is to actually executing the file source and the file name.
But what I want to show you two things.
I'll go ahead and make a new directory.
You don't have to do this but I'm just going to call it I don't know testing.
And then instead of testing I'll make a new file and what I'll put inside of that file is a select statement.
So let's start from cat.
Actually let's do an insert insert into cats and we'll just do what we have here.
Name and age which I'm just realizing and capitalized so we could obviously go back and change that
here and rerun it.
But we'll leave it.
So insert into cat's name and age just like that
values and name will be let's do Charlie.
Age will be let's do an old cat 17.
Save the file.
And you file save and make sure the file is as well and so do rename and I'll just call this insert
dot as well.
OK let's actually just to show you we can do more than one thing at a time.
Insert both or let's insert too.
And this one will be called.
I think we already have a Sindee let's do a Khani who is 10 and then at the end we'll also do select
star from cats just like that.
So these three things will be executed at once if we do it correctly.
So now to reference this file our Seelye is running in this folder so we can't just say insert data
as well.
If we did that source in-circuit as well it tells us it can't find that file.
So what we need to do is do a source testing slash insert data as well and there we go.
So that's important.
You have to access the correct path.
You have to refer to this file by its full path to get there.
So if you're in this folder all that we can access are these.
But if we drill to this folder then we have access.
And obviously you could extrapolate that to as many layers as you need.
So now let's make sure it worked.
You can see after it worked we had queery OK one row affected.
That was our first answer.
And then the same thing our second insert and then our select statement gave us this right here.
So that's all there is to running code from a file.
It's really really useful.
We'll be doing it pretty much nonstop.
And the first thing that we're going to do is actually get some new data into our database using this
in the next video.