MySQL Database Tutorial - 17 - Functions Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to tutorial number
17 and in this lesson what we're gonna
do is I'm gonna talk more about
functions because in the last tutorial
what we did is we took a look at the
concat function which basically took a
list of items and put them all together
but now I want to talk a little bit more
about functions and the different type
of functions so basically what a
function is is it's a name that does
something for example concat does
something to a list of items aka string
it together but other functions have
different names so let's go ahead and
take a look at some simple examples
first so I'm gonna go ahead and select
the name of a customer and I'm gonna
also select another custom column what
I'm gonna do is I'm gonna use a function
called upper now again like I said after
every function they always have
parenthesis and in between the
parentheses is when you put the extra
information now this information differs
from functional function for example
upper just takes the name of a column
concat takes a list it really depends on
the function what you have to put inside
those parentheses but what this function
is gonna do is it basically takes to
this column and it changes it all to
uppercase letters so you just go ahead
and run this query and then you'll see
what I'm talking about from customers so
again the job of concat was the tie
things together the job of upper is just
the upper case everything and each
different function has a different job
so that is how you can think of
functions just jobs so as you can see
here is the original name just standard
format and whenever you apply the upper
function to the column name it changes
everything to upper so using functions
is actually really simple just go ahead
and write the function name and then put
whatever information you need to write
in between the parentheses now another
cool thing is we already saw concat and
upper those are called string functions
basically they're functions that you can
apply to characters or texts but you can
also use in numeric functions for
example get the absolute value of
numbers get the square root of number
let me go ahead and show you guys an
example select cost and I'll also make
my second column to find the square root
of cost so square root is the name of
the function and cost and by the way I'm
not going to show you all the functions
because once you learn how to you know
use functions in a very basic fashion
then it gets kind of you know repetitive
so instead of showing you all the
functions I'm just gonna show you the
basics and if you want a list then go on
the mysql website or probably a bunch of
different websites have the list of
functions by anyways in the first column
it's gonna be the original cost of the
item and in the second column I'm just
gonna get the square root of that cost
from items now to be honest I would have
no idea when you would ever want the
square root of something how much
something cost because you never go into
a grocery store see box of cereal that's
like five dollars bike hmm I need you to
know the square root of this you know
but this is just an example so basically
as you can see the first column is the
cost 14 - or excuse me 150 - 15 bucks
and the second column is the square root
of that number so the square root of
149.99 is 1224 so on and so forth so
that is how you can use very simple
basic functions both character functions
and numeric functions however since I
have time I might as well show you guys
this there is another type of function
and that's called an aggregate function
you see what these functions did is they
basically changed the data in some way
so you ended up with an entire column of
information so the original column was
this and then your new column was the
exact same length for exists in this
example each one was like 30 rows long
well what an aggregate function does is
it takes an entire column of information
and it gives you one answer for example
let me go ahead and I would say I want
the average of this column so what it
would do is it would add up all of these
numbers and give you the average and of
course the average isn't thirty columns
long it's only one answer
oh say we wanted to add all of the items
together the price of all the items and
figure out the average cost of all of
the items on my website in order to do
that just go ahead and select average is
an aggregate function so this is only
gonna give you one number as a result
not a whole list the average cost of all
the items so let me go ahead and run
this and check it out instead of giving
you a huge list of numbers it just adds
up all of the prices of all the items on
your website and the average cost of all
of them is 463 dollars there you go but
what if you wanted to figure out
something like how many bids are on all
of the items well what we could do is we
could hop over to items and say okay
bids 3 plus 32 is 35 + 566 is I've got
to go ahead and get my graphing
calculator or what we could do is we
could just use an aggregate function so
in order to do this the aggregate
function for this is sum what sum does
is it takes a column of bids and that
adds up pretty much adds them all up
figures out the sum of all of the bids
so let me just finish this query from
items and check it out there were ten
thousand nine hundred and thirty nine
bids made on all of the items on my
website pretty sweet huh so in the next
tutorial what I'm gonna do is I'm gonna
talk to you guys about the different
aggregate functions and also show you
guys some other cool examples and how to
run several aggregate functions at once
so there's a lot more to cover with
functions and aggregate functions so
sorry but you aren't done yet so thank
you guys for watching don't forget to
subscribe and I'll see you guys in the
next video