Detailed Notes
Tutorial notes:
1. Where options = null not null and greater or less than 2. order by 3. group by 4. stat table
create table stats( stat_id INT NOT NULL AUTO_INCREMENT, code varchar(4), value1 int, value2 int, PRIMARY KEY ( stat_id ) ); insert into stats(code,value1,value2) values ('A',1,95); insert into stats(code,value1,value2) values ('B',2,195); insert into stats(code,value1,value2) values ('C',1,295); insert into stats(code,value1,value2) values ('ABC',2,395); insert into stats(code,value1,value2) values ('ABCD',1,495); insert into stats(code,value1,value2) values ('ABCE',2,595); insert into stats(code,value1,value2) values ('ABEC',3,695); insert into stats(code,value1,value2) values ('AEBC',3,795);
select code,value1,max(value2) from stats group by value1 order by value1; select * from stats where code in ('A','ABC');
Transcript Text
[Music] well hello and welcome back we are continuing our series of uh tutorials about my sequel maria db really trying to keep it general sequel but you know sometimes we do manage to sort of split into a couple of things that are more or less specific to my sequel mariadb particularly if you're using a higher end more like you know super full-featured situation like a quicken or not quick oracle or sql server where they do have their own extensions but we're going to try to keep it as useful as possible and continuing this episode where we're going to look into we're going to like dig into some query type stuff this time so we're going to talk about where clauses and i think we're going to start with let's start with our basic stuff so when we had if you go back to our tutorial parent if we select everything from there we have four rows and what we've seen before for the most part is a select although we've used inner joins and outer joins but we're now going to talk about within our query and we'll start with something simple so we're going to start about a single table now in this one let's say that we only want everybody that has a creation uh let's start with this uh only one has a t1 id of three now we could there's a couple ways we go about it but with the where clause the easiest way is we're gonna say select star from tutorial parent where and then we just give it the column name so we say t one id and then we say equals three so everywhere it equals three give me the records bam we have our two records now we can also do greater than so i could say uh where it is let's say less than four now notice we get three records back because null is not less than four is it greater than uh is it greater than zero let's find out nope it is also not greater than zero so it's important to note here that because we're using greater than or less than on the uh this energy value it doesn't it doesn't know where to sort or where to put as far as greater than or less than a null so it just ignores it it's like nope i'm not even going to think about that row but note here that if i do where t1 id is less than 4 it's going to give me all of these if i just do if it's less than 3 then it's just going to give me the only one where the id is less than now i can also do if i go back to my equals i can do a not equals and this is again note that even though i do not equals null is not which makes it double negative is not not equal to 3. now i could say what if i say if it's not equal to null then i get nothing because whoops i get nothing because this is a numeric representation this is a numeric not equal when i want to deal with null i would have to say it either is or it is not so if i say where everywhere a word is null then i get my record if i say everywhere where it is not null then i'm going to get all of these other records so i'm able to do a lot of your basic stuff that you would think of you know if you do greater than less than nulls are either going to be they either it either they they are it's either they is or they is not so it either they are or they are not and you can also do uh let's see what else should we want to make sure i get what we want to uh no not no equal greater than less than not equal let's now what we can also do with our query let's go back to uh we'll keep it simple so if we go back to select star one of things we can do is we can actually order the results like here um the paranoid it looks like it's sort of in like a natural order of where the record is but that's actually not guaranteed what i could do is i could say let me start with the lowest to the highest of the ordered ids and that is called order by so and that's after the where clause if you have one so it's basically one of the last things you do is you say order by and then i give it the column name so i'm going to order by t1 id and when i do that now here null comes first it takes the nulls first and then it does them in order for t1 id and that's going to be and there's two different ways you can do it you can do ascending which is asc and that's what is by default or i can go in descending order and now the threes are at the top the one and then the nulls at the bottom so note that when you're doing although lesser than and greater than does not with a number take into account no when you do an order by null is less than any number so if i had some i mean i could i could play around with the numbers and put like a negative in there or something like that and null is still going to be there now i can also do um [Music] now some of these are going to be pretty straightforward because if i do if i do order by user login make a string then you can see that it's ordered in that fashion luckily or unfortunately maybe in my case since i have these in different ways i have those number the names ordering the same as ids that could be a problem but let's look at tutorial one whoops and play around with that a little bit because i think that one's different yeah so here you can see by default it goes by t1id if i want to go by state then i'm going to see s234 i can order by zip that's going to be the same order shoot i did the same thing in both of those but note that now have completely nothing to do with the t1 id column because that's in a completely reverse order so i can do an order by i can also do a thing called a group by and so when i go back to parent then i can come in and i can do group by um start year and now what it does is these are sort of like roll-ups it's just going to grab a record because we only have two start years we have 20 22 and then we have dolls for these two and so it's only showing me groupings for these and the rest of data sort of doesn't seem to matter very much but that's because i'm not doing grouping functions so in order to look at those let's play around a little bit we're going to create yet another table and this one we're going to call stats and all it's got is an id and then there we go uh an id a code and then two integer values and then what i want to do is i'm going to take i'm just going to go ahead and insert it whoop what was my code code was for oops let me do this i didn't do that right so let me take these three there we go okay so now if i do select star from stats i'm going to see a lot of stats so i've got eight rows now if i want to do a um and of course i didn't uh i probably didn't do this right for my total group buys i can let's do this that's i'm going to change these real quick so i'm going to delete from stat actually it's truncate stats so i'm going to empty the table and instead i'm going to come in and i'm going to change these first values so let's get one two one two let's do three and three okay so now let's take that and insert those because i needed some repeating values which i do i have so now if i just like start from stats okay so i can see and let's do order by value one just so it's easy to see that or by value one so i can see i can have i've got three that have the same value one two are threes with a two and two with 3. now if i do group by and actually order by is the last thing so i'm going to keep it that way but i'm still going to i'm going to add my group by value 1. and so i still see it in order but now it's basically just taken the first record it finds as you can see for each of the values now i can actually do something interesting with that so instead of just doing the normal star i'm going to do i select code because remember we can tell it which columns we want so let's go ahead and do these columns so i've dropped my id out now what i want to do is instead of showing that value i'm going to do a sum of the values of value 2. and so here i should see 95 plus 295 plus 6 4.95 or for the twos i should see one three and 595 added and then here i'll see six plus seven if we do that now we can see that i've got these totals and so now i'm actually generating in my query i'm generating totals of like values the same thing is i can instead of a sum i can do a max and now it shows me the maximum value for each of these i can do a min and show the minimum value let me take this and copy it so just make sure it's in the notes and yeah that'll work uh and so men we can see that it's the minimum the 95 where'd they go 95 195. 695 and now we can also do let's go back to our where's because that gives us some basics for doing some group buys in order by now we can group across multiple fields but we're not going to get into that yet but i do want to do is i'm going to go back to i'm just going to select star from stats and now i'm going to do where because there's another thing that i think is very useful for us to get i'm going to wear code let's say where code equals a so if we do that we get that one value back if we do abc we get the abc record now we can do a list of values so we could say where code in and give it parentheses and give it the list of values so if i do n a and in abc which is these two you know where it's in a and also abc then we can see those two records so let me make sure i capture that for our notes and then we can also do a like so now i can say where code like now if i want to do work code like a it's only going to give me an a because it says like is it there's not really any ambiguity it's going to be the same as an equal but let's say i put a percent which is a wild card after the a this says give me everything where it starts with a now i get all these records so let me go look at the e i don't think we started with the d anywhere we did not start with an anywhere but we do see an e sort of floating around so here nothing starts with an e but we do have we can put a percent before that and say is there anything that ends with an e yes as a matter of fact there is now the other thing is we can say give me a record where e exists anywhere in the code and now i get it whether it's at the end the middle or there now i can do some other stuff because i could say let's see i can do multiple strings here so i could say let's say it has to have b and e next to each other b first followed by e there is that case um i can reverse it and say eb but now i can't put a percent between those and get both because it's still going to precede the e by from the b so we can place the that wild card wherever within our our string that we're looking for now note that this is uh this is not a number so if we wanted to do uh value two like let's say seven percent say it starts with the seven oh i lied it is gonna allow that so i can do that within my numbers so even though these are and those are um those are integers but we are okay with that so we can find some things that are so basically let's go back to look at like our uh let's go look at our data here and let's see if we can find something so let's say we want to look for a zip code i granted that strings but still so you could also look for any zip code that well we sort of played around with that so i guess that's not as good let's see if we get something different here we may revisit this just to get some funky data oh let's try this let's play around with it with um it's like star from where uh let's see are those yeah so update date like let's take a look at this one uh percent oh nine and we're going to see that we've got that date so we could also do it like for every date 0409 or if we wanted to start it then we could go 2022 there we go and that's going to give us all of those dates so we can play around with this quite a bit as far as i like it gives us just a yet another and also a very powerful way to do our searches uh or our where clauses the warning i would say about a like as opposed to just about every other way to grab data is that it's not as index friendly so if you're building big complex queries you're taking a lot of data back then or such i guess it's more like if you're searching through a table with a lot of data then it can take longer to do the like quite a bit sometimes than to do a a greater or less sam and we'll talk more about indexes as we go further into this this discussion but i think that's probably pretty good place for now for us to wrap this one up so we've we've played around quite a bit now with some very basic uh queries we've built some tables we've got some relationships and we'll just keep on uh keeping on in the next episode around so hope you go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our series of uh tutorials
about
my sequel maria db really trying to keep
it general
sequel but you know sometimes we do
manage to sort of split into a couple of
things that are more or less specific to
my sequel mariadb particularly if you're
using a higher end
more like you know super full-featured
situation like a
quicken or not quick oracle or
sql server where they do have their own
extensions but we're going to try to
keep it as useful as possible and
continuing this episode where we're
going to look into
we're going to like dig into some query
type stuff this time
so we're going to talk about where
clauses
and i think we're going to start with
let's start with
our basic stuff so when we had if you go
back to our tutorial parent if we select
everything from there
we have four rows
and
what we've seen before for the most part
is a select although we've used inner
joins and outer joins but we're now
going to talk about
within
our query and we'll start with something
simple so we're going to start about a
single table
now in this one
let's say that we only want
everybody that has a creation uh let's
start with this uh only one has a t1 id
of three
now we could there's a couple ways we go
about it but with the where clause the
easiest way is we're gonna say select
star from
tutorial parent
where
and then we just give it the column name
so we say t
one id
and then we say equals three so
everywhere it equals three give me the
records bam we have our two records
now we can also do greater than so i
could say uh where it is
let's say less than four
now notice we get three records back
because null
is not less than four
is it greater than uh is it greater than
zero let's find out
nope it is also not greater than zero
so it's important to note here that
because we're using greater than or less
than on the
uh this energy value it doesn't
it doesn't know where to sort or where
to put
as far as greater than or less than a
null so it just ignores it it's like
nope i'm not even going to think about
that row
but note here that if i do where t1 id
is less than 4 it's going to give me all
of these if i just do
if it's less than 3
then it's just going to give me the only
one
where the id is less
than now i can also do if i go back to
my equals i can do a not equals
and this is again note that even though
i do not equals null
is not
which makes it double negative is not
not equal to 3.
now i could say
what if i say if it's not equal to null
then i get nothing because whoops
i get nothing
because this is a numeric representation
this is a numeric not equal when i want
to deal with null i would have to say it
either is or it is not so if i say where
everywhere a word is null
then i get my record
if i say everywhere where it is not null
then i'm going to get all of these other
records
so i'm able to
do a lot of your basic stuff that you
would think of you know if you do
greater than less than nulls are either
going to be they either it either they
they are it's either they is or they is
not so it either they are or they are
not
and you can also do
uh let's see what else should we want to
make sure i get what we want to
uh no not no equal
greater than less than not equal
let's now what we can also do with our
query let's go back to uh we'll keep it
simple so if we go back to select star
one of things we can do is we can
actually order
the results
like here
um the paranoid it looks like it's sort
of in like a natural order of where the
record is but that's actually not
guaranteed what i could do is i could
say let me start with the lowest to the
highest of the ordered ids
and that is called order by so and
that's after the where clause if you
have one
so it's basically one of the last things
you do is you say order by and then i
give it the column name
so i'm going to order by t1 id
and when i do that
now here
null comes first
it takes the nulls first and then
it does them in order for t1 id
and that's going to be
and there's two different ways you can
do it you can do ascending
which is asc and that's what is by
default or
i can go in descending order
and now the threes are at the top the
one and then the nulls at the bottom so
note that when you're doing although
lesser than and greater than
does not
with a number take into account no
when you do an order by null is less
than any number
so if i had some i mean i could
i could play around with the numbers and
put like a negative in there or
something like that and null is still
going to be there now
i can also do um
[Music]
now some of these are going to be pretty
straightforward because if i do
if i do order by
user login
make a string
then you can see that it's ordered in
that fashion luckily or unfortunately
maybe in my case since i have these in
different ways
i have those number the names ordering
the same as ids that could be a problem
but let's look at tutorial one
whoops
and play around with that a little bit
because i think that one's different
yeah so here you can see by default it
goes by t1id
if i want to go by state
then i'm going to see s234
i can order by zip
that's going to be the same order shoot
i did the same thing in both of those
but
note that now
have completely nothing to do with the
t1 id column because that's in a
completely reverse order
so i can do an order by i can also do
a thing called a group by and so
when i go back to
parent
then
i can come in
and i can do group by
um start year
and now what it does
is these are sort of like roll-ups
it's just going to grab a record because
we only have two start years we have 20
22 and then we have dolls for these two
and so it's only showing me
groupings for these
and the rest of data sort of doesn't
seem to matter very much
but that's because i'm not doing
grouping functions
so in order to look at those let's play
around a little bit we're going to
create yet another table
and this one we're going to call stats
and all it's got is an id
and then there we go
uh an id a code and then two integer
values
and then what i want to do is i'm going
to take
i'm just going to go ahead and insert it
whoop what was my code
code was for oops
let me do this
i didn't do that right
so let me take these three
there we go okay so now if i do select
star from stats i'm going to see a lot
of stats
so i've got eight rows
now
if i want to do a
um
and of course i didn't uh i probably
didn't do this right for my total group
buys
i can
let's do this
that's
i'm going to change these real quick so
i'm going to delete from stat actually
it's truncate stats
so i'm going to empty the table
and instead i'm going to come in and i'm
going to change these first values so
let's get one
two
one two
let's do three and three okay
so now let's take that and insert those
because i needed some repeating values
which i do i have so now if i just like
start from stats
okay
so i can see and let's do order by value
one just so it's easy to see that or by
value one
so i can see i can have i've got three
that have the same value one two are
threes with a two and two with 3. now if
i do group by
and actually order by is the last thing
so i'm going to keep it that way but i'm
still going to i'm going to add my
group by
value 1.
and so i still see it in order but now
it's basically just taken the first
record it finds as you can see for each
of the values
now i can actually do something
interesting with that so instead of just
doing the normal star i'm going to do
i select code because remember we can
tell it which
columns we want so let's go ahead and do
these columns
so i've dropped my id out now what i
want to do
is instead of showing that value i'm
going to do a sum
of the values
of value 2.
and so here
i should see 95 plus 295 plus 6 4.95 or
for the twos i should see one three and
595 added and then here
i'll see six plus seven
if we do that now we can see that i've
got these
totals and so now i'm actually
generating in my query i'm generating
totals of like values
the same thing is i can instead of a sum
i can do a max
and now it shows me the maximum value
for each of these i can do a min
and show the minimum value
let me take this and copy it so just
make sure it's in the notes
and
yeah that'll work
uh and so men we can see that it's the
minimum the 95 where'd they go
95
195. 695
and now we can also do
let's go back to our where's
because that gives us some basics for
doing some group buys in order by now we
can group
across multiple fields
but we're not going to get into that yet
but i do want to do is i'm going to go
back to
i'm just going to select star from stats
and now i'm going to do where
because there's another thing that i
think is very useful for us to get i'm
going to wear code let's say where code
equals
a
so if we do that we get that one value
back
if we do abc
we get the abc record
now we can do
a list of values so we could say where
code in
and give it parentheses and give it the
list of values
so if i do n a and in abc
which is these two you know where it's
in a
and also abc
then we can see those two records
so let me make sure i capture that for
our notes
and then we can also do a like so now i
can say where code
like now if i want to do work code like
a
it's only going to give me an a because
it says
like is it there's not really any
ambiguity it's going to be the same as
an equal but let's say i put a percent
which is a wild card after the a
this says give me everything where it
starts with a
now
i get all these records
so let me go look at the e i don't think
we started with the d anywhere we did
not start with an anywhere but we do see
an e sort of floating around
so here
nothing starts with an e
but we do have we can put a percent
before that and say is there anything
that ends with an e yes as a matter of
fact there is
now the other thing is we can say give
me a record where e exists anywhere
in the code
and now i get it whether it's at the end
the middle or there
now i can do some other stuff because i
could say
let's see
i can do multiple strings here so i
could say let's say it has to
have b and e next to each other
b first followed by e
there is that case
um i can reverse it
and say eb
but now i can't put a percent between
those and get both
because it's still going to precede the
e
by from the b so we can place the that
wild card wherever within our
our string that we're looking for now
note that this is uh this is not a
number so if we wanted to do
uh value two
like let's say seven percent
say it starts with the seven
oh i lied it is gonna allow that
so i can do that within my numbers
so even though these are and those are
um
those are integers but we are okay with
that so we can find some things that are
so basically let's go back to look at
like our
uh let's go look at
our data here
and let's see if we can find something
so let's say we want to look for a zip
code
i granted that strings but still so you
could also look for
any zip code that
well we sort of played around with that
so i guess that's not
as good
let's see if we get something different
here
we may revisit this just to get some
funky data oh let's try this let's play
around with it with um it's like star
from
where
uh let's see are those yeah so update
date like let's take a look at this one
uh
percent oh nine
and we're going to see that we've got
that date so we could also do it like
for every date
0409
or if we wanted to start it then we
could go 2022
there we go and that's going to give us
all of those dates
so we can play around with this quite a
bit as far as i like it gives us just a
yet another
and also a very powerful way to do our
searches
uh or our where clauses
the warning i would say about a like
as opposed to
just about every other way to grab data
is that it's not as index friendly so if
you're building big complex queries
you're taking a lot of data back
then or such i guess it's more like if
you're searching through a table with a
lot of data
then it can take longer to do the like
quite a bit sometimes than to do a
a greater or less sam and we'll talk
more about indexes as we go further into
this
this discussion but i think that's
probably pretty good place for now for
us to wrap this one up
so we've
we've played around quite a bit now with
some very basic uh queries we've built
some tables we've got some relationships
and we'll just keep on uh keeping on in
the next episode around so hope you go
out there and have yourself a great day
a great week and we will talk to you
next time
you