Detailed Notes
1. Date and time functions select adddate(type_datetime, INTERVAL 1 DAY), adddate(type_date, INTERVAL 1 DAY), adddate(type_year, INTERVAL 1 DAY) from all_dates; select adddate(type_datetime, INTERVAL 1 MONTH), adddate(type_date, INTERVAL 1 MONTH), adddate(type_year, INTERVAL 1 MONTH) from all_dates;
select adddate(type_datetime, INTERVAL -2 MONTH), adddate(type_date, INTERVAL -2 MONTH), adddate(type_year, INTERVAL -2 MONTH) from all_dates;
select adddate(type_datetime, INTERVAL 1 YEAR), adddate(type_date, INTERVAL 1 YEAR), adddate(type_year, INTERVAL 1 YEAR) from all_dates;
select year(adddate(type_datetime, INTERVAL 1 YEAR)) dtm, year(adddate(type_date, INTERVAL 1 YEAR)) dt, year(adddate(type_year, INTERVAL 1 YEAR)) yr from all_dates;
select dayname(type_datetime), dayname(type_date) from all_dates;
select adddate(type_datetime, INTERVAL 5 SECOND), adddate(type_date, INTERVAL 5 SECOND) from all_dates;
select datediff(type_datetime,now(quit)), type_datetime, now() from all_dates; select datediff(type_datetime,now()) / 365, type_datetime, now() from all_dates;
Transcript Text
[Music] well hello and welcome back we are continuing looking at sql development uh particularly in the mysql mariadb world but a lot of this is more or less uh going to be applicable regardless of whatever database you're in this is an area where it will be a little different syntactically in some of the other databases because we're going to start talking about stored procedures today and this is actually something that wasn't even supported by my sequel until you know a version which now it's been for a while so but there's there was a point where it didn't even support them and i think it probably helps to start with what is a stored procedure a stored procedure is roughly uh i mean it's a screw it's a procedure it's a function essentially although functions are a little different and we'll talk about those but a stored procedure is essentially a script it is instead of us doing you know we could do like select star from address and then we could do select star from uh what was that from app user where was that t1 id equals let's just say one so we can do this like you know combination of when we can sit there and start typing multiple uh commands essentially and we can also do this outside of the database we could have some sort of a odbc jdbc you know type of connection or native connection into the database and have whatever language we use you know c sharp java python whatever actually send commands and put together some a series of commands and some related logic based on that but that's not always uh one it's not always the best approach it's not always option uh an option and um you know the third thing is it's actually faster often to do stuff within the database than to do this back and forth calls you know if you have to like pull a result set out of the database into your application wherever it's at do some work make a couple other calls into the database take the results do some more work it's easier you know you've got all that transact that transfer from database application and it's nice to not have that so that's why we have stored procedures actually it's it's probably more simple than that it was just so that we can do a little more let me put some more logic into our database and not have to worry about another language now the first thing i need to explain because of the way it works with stored procedures is there's a thing called delimiter now currently you can see if i do select star from app user or equals one this semicolon is my delimiter that says that ham at the end of it a line if i use the delimiter command and say i want to use this slash slash now when i do that select star notice it's i've got this little arrow it says hey i'm looking for more like you didn't finish the line i can't execute it yet so now if i do that now when i do that slash it'll give me you know allow me to do multiple calls and it means as soon as i put a semicolon it's not going to end so i could even do here i'm going to select star from uh address and then do a semicolon and now what i see here and this allows me to actually put together multiple things and you can see here where first it grabbed from app user and then it did the call select from star address so it's actually saying that hey each of these this is a command because i've got my my line terminator of a semicolon but the delimiter is going to tell me when i'm actually going to execute it so it allows me to actually batch together a couple things which is needed when we get into stored procedures now the first thing that we want to do in a store procedure is let's just keep it very simple so what does one look like well once we've set our delimiter and you got to make sure you do that otherwise you'll have some issues once you start throwing semicolons in here it's pretty easy so i'm going to do create procedure and i'm just going to call this um i'm going to call my tables and i can give it parameters but i'm not at this point and enter now i'm going to do a begin and then i'm just going to say show tables i put my semicolon like i normally would i do an end and so now this is my procedure my tables begin show tables end and i can come back i can do my little completion and it doesn't like it in the end so i need to do probably a semicolon here uh no what did i miss create procedure my tables so let's put you there oh shoot that's not a it's because that's not that okay so now i've got that my mistake i forgot how to do show tables and so now i've created this procedure now let me change my delimiter back to semicolon and now i've created this procedure which uh let me do it like this just so that you have it so delimiter is the dot and let's do this clean this up a little bit because we can probably come back to this okay so now we have a procedure and if we wanted to look there's a couple of different ways we can do this and the one is we can do show procedures status i believe it is oops i gotta type it right and i can see that here i have this table in the database and it's a little hard to read here but rough let's see if we open up a little bit database is tutorial the name is my tables it's a procedure the definer so that's who who's the user that created it was it modified created security type it's got some additional stuff that we don't really care as much about so we have this table now this procedure now called my tables and in order to do it to run it we can do call my tables or semicolon and so now it goes in and it does a show tables now if we were to go in and try to replace uh create it again so if we go do this now when it comes through it says my tables already exist so what we can do is uh let's see so we're still in that delimiter good so we can also try we can do create or replace procedure my table and it's going to be let's see let's say use tutorial and so we can do that so now it's going to say i'm going to jump in tutorial and do tables so now i take the same thing uh let's leave it at that for now oops i don't need to change my delimiter so i can do that let's make sure that it pastes in it does and i do use is not allowed in store procedures and so here we're seeing where yes you can't use everything there's like and the problem here is that my um stored procedure lives in a specific database and so i can't just jump in but what i can do and so let's just look at the same thing if i say what are my databases i don't know what i have as an option here oh uh so information screen so let's say i use and i use information schema okay so now i'm there if i do show tables i notice i haven't set my delimiter back so now if i do call let's go back to where i did that call um oh let's just do this whoops delimiter is now back to here and where we do our call that worked here we go call my tables now we're going to find that access denied for user to database information schema well i'm not able to see it but i'm it's also not calling it properly i have to actually say tell it what database it was in and then i can call it so there's a couple of things we you know we have some limits to and it's but it's mostly uh within a database in the mysql mariadb world we're going to make so we're going to do some work with it uh work on tables there if we want to refer to something in another database then we have to explicitly give it a uh give it a name which in this case like you know information schema dot or tutorial dot to tell us which database we're in and we could do that all around we could always be we could always like we could do create a replace procedure uh tutorial dot my tables and we can do it there so let's see show tables let's say um oh let's do i wonder if we do uh what was that show procedure i wonder if it allow us to do that one it may not we'll find out so let's take that okay so it allowed us to do that so now if we do call where's our call here we go uh and so it does work so show procedure status so we came in and we show list of tables and then we get our procedure status so we are able to you know essentially i don't batch up a couple of commands now there are other things we can do that um are not necessarily gonna like we can do this let's do this let's go back to this one so let's go back to our oh let me do this um so that's my tables now let's do um my data and let's do select star from address select star from uh app user what are our tables and let's say look at type look up type oh not my date my data let's create that guy or gal uh get our delimiter back and now we're gonna do a call my data and we can see that each of those selects comes through now we can send it something so now we can do um let's call it ident and we can do select uh let's do it with an underscore just to make it easier and i think we're gonna and i'm not i'm just gonna go simple right now um we're gonna get more complicated on our parameters next time around but let's see where um what is our address id t1id okay t1 id equals oops id equals underscore id oh let's call this let's get address so now now if i call git address and if i don't give it a parameter it's going to say that hey oh because i'm not the right player let me go ahead and use tutorial first so i can i don't have to specify who i am and here it tells me incorrect number of arguments expected one got zero so it even tells you how many expected so i'm gonna do get address two and now i can see that it now i've passed a parameter and so we now i'm giving it something a little more useful as far as my procedures go i think i just want to stop today because we're going to start getting into a little bit more fun stuff with this this is it does go beyond this how we can actually um start working with our data a little bit more and get a little bit more into how we deal with parameters and things like that there is a lot to learn about these so we will definitely be spending a little bit of time on stored procedures and we'll see a lot of the same things as we get into functions and then we'll even do a little bit of trigger related stuff as we're other things as we're moving along but hey uh each day we're just getting a little bit better learning a few more things and uh slogging our way through this so hopefully you learned a couple things today and you come back next time and we just continue learning our way through this until we're all masters of some sort that being said 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 looking at sql development uh
particularly in the mysql mariadb world
but a lot of this is more or less uh
going to be applicable regardless of
whatever database you're in
this is an area where it will be a
little different syntactically
in some of the other databases because
we're going to start talking about
stored procedures today
and this is
actually something that wasn't even
supported by my sequel until
you know a version which now it's been
for a while so but there's there was a
point where it didn't even support them
and i think it probably helps to start
with what is a stored procedure
a stored procedure is roughly
uh i mean it's a screw it's a procedure
it's a function essentially although
functions are a little different and
we'll talk about those
but a stored procedure is essentially a
script it is instead of us doing you
know we could do like select star from
address
and then we could do select
star from
uh what was that from app user where
was that t1 id
equals let's just say
one
so we can do this like you know
combination of when we can sit there and
start typing multiple
uh commands essentially
and we can also do this outside of the
database we could have some sort of a
odbc jdbc you know type of connection or
native connection into
the database and have
whatever language we use you know c
sharp java python
whatever
actually send commands
and
put together some
a series of commands and some related
logic based on that
but
that's not always
uh one it's not always the best approach
it's not always option uh an option
and um you know the third thing is it's
actually faster often to do stuff within
the database than to do this back and
forth calls you know if you have to
like pull a result set out of the
database into your application wherever
it's at
do some work
make a couple other calls into the
database take the results do some more
work
it's easier you know you've got all that
transact that
transfer
from database application and it's nice
to not have that
so that's why we have stored procedures
actually it's it's probably more simple
than that it was just so that we can do
a little more let me put some more logic
into
our database and not have to worry about
another language
now the first thing i need to explain
because of the way it works
with stored procedures is there's a
thing called delimiter
now currently
you can see if i do select star from app
user or equals one
this semicolon is my delimiter
that says that ham at the end of it a
line if i use the delimiter command
and say i want to use this slash slash
now
when i do that select star
notice it's i've got this little arrow
it says hey i'm looking for more like
you didn't finish the line i can't
execute it yet
so now if i do that
now when i do that slash it'll give me
you know allow me to do multiple calls
and it means as soon as i put a
semicolon it's not going to end
so i could even do here i'm going to
select star from
uh address
and then do a semicolon
and now what i see here and this allows
me to actually
put together multiple things and you can
see here where first
it grabbed from app user
and then
it did the call select from star address
so it's actually saying that hey each of
these this is a command
because i've got my
my line terminator of a semicolon but
the delimiter is going to tell me when
i'm actually going to execute it so it
allows me to actually
batch together a couple things which is
needed when we get into stored
procedures
now the first thing
that we want to do in a store procedure
is
let's just keep it very simple
so what does one look like well once
we've set our delimiter and you got to
make sure you do that otherwise you'll
have some issues once you start throwing
semicolons in here it's pretty easy so
i'm going to do create procedure
and
i'm just going to call this um
i'm going to call my tables
and i can give it parameters but i'm not
at this point
and enter now i'm going to do a begin
and then i'm just going to say show
tables
i put my semicolon like i normally would
i do an end
and so now this is my procedure my
tables begin show tables end
and i can come back i can do my little
completion
and it doesn't like it in the end so i
need to do probably a semicolon here
uh no what did i miss create procedure
my tables
so let's put you there
oh shoot that's not a it's because
that's not that okay so now i've got
that my mistake
i forgot how to do show tables
and so now i've created this procedure
now let me change my delimiter back to
semicolon
and now i've created this procedure
which uh let me
do it like this
just so that you have it so delimiter is
the dot
and
let's do this
clean this up a little bit
because we can probably come back to
this okay
so now we have a procedure
and if we wanted to look there's a
couple of different ways we can do this
and the one is we can do show
procedures status i believe it is oops i
gotta type it right
and i can see that here i have this
table
in the database and it's a little hard
to read here but rough
let's see if we open up a little bit
database is tutorial the name is my
tables it's a procedure
the definer so that's who who's the user
that created it was it modified created
security type it's got some additional
stuff that we don't really care as much
about so we have this table now this
procedure now
called my tables
and in order to do it
to run it we can do call my tables
or semicolon
and
so now it goes in and it does a show
tables
now if we were to go in and try to
replace uh create it again
so if we go do
this
now when it comes through it says my
tables already exist so what we can do
is uh let's see so we're still in that
delimiter good so we can also try we can
do create or replace
procedure my table
and it's going to be
let's see
let's say use tutorial
and so we can do that so now it's going
to say i'm going to jump in tutorial and
do tables so now
i take the same thing
uh let's leave it at that for now oops i
don't need to change my delimiter
so i can do that let's make sure that it
pastes in it does
and i do
use
is not allowed in store procedures and
so here we're seeing where yes you can't
use everything there's like and the
problem here is that my
um
stored procedure lives in a specific
database
and so i can't just jump in but what i
can do
and so let's just look at the same thing
if i say
what are my databases i don't know what
i have as an option here
oh
uh so information screen so let's say i
use
and i use
information
schema
okay so now i'm there if i do show
tables
i notice i haven't set my delimiter back
so now if i do call
let's go back to where i did that call
um oh let's just do this
whoops
delimiter is now back to here
and where we do our call that worked
here we go call my tables
now
we're going to find that
access denied for user to database
information schema well i'm not able to
see it but i'm it's also not calling it
properly i have to actually say
tell it what database it was in
and then i can call it
so there's a couple of things we you
know we have some limits to and it's but
it's mostly
uh within a database in the mysql
mariadb world
we're going to make so we're going to do
some work with it uh work on tables
there if we want to refer to something
in another database then we have to
explicitly
give it a uh give it a name which in
this case like you know information
schema dot or tutorial dot to tell us
which database we're in and we could do
that all around we could always be
we could always like we could do create
a replace procedure
uh tutorial dot
my tables
and we can do it there
so let's see show tables let's say um
oh let's do
i wonder if we do uh
what was that show procedure
i wonder if it allow us to do that one
it may not we'll find out
so let's take that
okay so it allowed us to do that so now
if we do call
where's our call here we go
uh and so it does work so show procedure
status so we came in and we show list of
tables
and then we get
our procedure status
so we are able to
you know
essentially i don't batch up a couple of
commands
now there are other things we can do
that um
are not necessarily gonna like we can do
this let's do this
let's go back to this one so let's go
back to our oh let me do this
um
so that's my tables
now let's do um
my data
and let's do select
star from
address
select star from
uh
app user
what are our tables
and let's say look at type
look up
type
oh not my date my data
let's create that guy
or gal
uh get our delimiter back and now we're
gonna do a call
my data
and we can see that each of those
selects comes through
now we can
send it something so now we can do
um
let's call it
ident
and we can do select
uh let's do it with an underscore just
to make it easier
and i think we're gonna and i'm not i'm
just gonna go simple right now
um we're gonna get more complicated on
our
parameters next time around but let's
see where
um
what is our address id
t1id okay
t1 id equals
oops
id equals
underscore id
oh let's call this
let's get address
so now
now if i call git address and if i don't
give it a parameter
it's going to say that hey oh because
i'm not the right player let me go ahead
and
use tutorial
first so i can i don't have to specify
who i am and here it tells me incorrect
number of arguments
expected one got zero so it even tells
you how many expected so i'm gonna do
get address two
and now i can see that it
now i've passed a parameter and so we
now i'm giving it
something a little more useful
as far as my procedures go
i think i just want to stop today
because we're going to start getting
into a little bit more fun stuff with
this this is it does go beyond this how
we can actually
um
start working with our data a little bit
more and get a little bit more into how
we deal with
parameters and things like that
there is a lot to learn about these so
we will definitely be spending a little
bit of time on stored procedures and
we'll see a lot of the same things as we
get into functions
and then we'll even do a little bit of
trigger related stuff as we're
other things as we're moving along but
hey
uh each day we're just getting a little
bit better learning a few more things
and uh slogging our way through this so
hopefully you learned a couple things
today and you come back next time and we
just continue learning our way through
this until we're all
masters of some sort that being said go
out there and have yourself a great day
a great week and we will talk to you
next time
you