MySQL Database Tutorial - 16 - Creating Custom Columns Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to your 16th MySQL
tutorial and in this lesson I want to
talk to you guys about creating custom
columns now create them custom columns
well that's kind of like a tongue
twister creating custom columns is
something that you can do and it's gonna
come in handy a whole lot for example
say that you were working with your
customers table and your boss wanted to
send out a letter to all your customers
so of course in order to address a
letter at least in the United States you
write the name the address and under
that you write the city and state but in
order to write the city and state you
typically write it like this instead of
right um writing the city and state like
that you write the city with a comma and
then the state after however if we go
ahead and we look at our customers table
there's no way that we can achieve this
because the city and stay are two
different things and the comma doesn't
come after the city name so your boss
comes in and he's like lucky I need a
column that has City comma State and
we're like okay well these are the only
columns we have to work with what do you
want us to do lucky for you you have
been watching my tutorials and you know
that there is a way that you can
temporarily make your own custom columns
so let me go ahead and use a select
statement but instead of selecting city
and state I'm gonna teach you guys about
a simple function now I'll go more I'll
go on more about functions later on but
for now go ahead and type Co n CA T this
is short for concatenate and it's
basically a job that MySQL can do that
it takes a list of items and it combines
those items now the first item we want
to combine is city now of course
whenever you're using lists just
separate it with commas now the second
item we want to combine is just a string
of text we want to include a comma
because remember and this isn't MySQL
this is just an example atoms comma and
why would be an example where I live so
in between the city and state I want a
comma in a space
so any time you were just including raw
text go ahead and put it in between the
single quotation marks so a comma and
space is the next thing I want and then
the last thing in my list of course I
need a common to separate my list is the
state
so again city comment space state now
those three things need to be separated
with a comma so now that I'm gonna go
ahead and select that column and again
this is a column that I'm gonna make up
from customers I can go ahead and run
this and check it out I now created a
new custom column that is made up of the
city name with a comma in a space after
it and then the state so Adams is this
teddy is that I say steady with the XS
steady Adams comma space New York
Gary come space Indiana I think that is
Phoenix comma space Arizona so that is
how you can use the concat to pretty
much like I said concat is short for
concatenate which means join things
together so it takes a bunch of items as
a list and it joins them together you
can either join together two columns
that already exist or whenever you want
to use text like maybe a comma or maybe
you want to use a hyphen or plus sign
you need to include that in between
single quotation marks now you're saying
okay well I know PHP or some other
program already or maybe Java and in
order to reference this column it needs
to have a name so what is the name of
this column is it city no is a state no
is it concat city coma State comma
whatever no in order to give this new
column a name because check it out
whenever you make a table every column
has a name in order to name your new
column you need to specify that whenever
you're writing your query so here's the
query ahead before select this column
the custom column we made up from
customers however it doesn't have a name
yet so in order to name it go ahead and
say select that
as and after the word as you give a name
such as new underscore address now
whenever you select this column it's
gonna have the name new address so
select my custom column as the name new
address now I just go ahead and I messed
that up so let me go ahead and run this
and check it out now we have our custom
column formatted exactly how we wanted
it to be formatted and it now has the
name new address so now whenever we are
working with a program like PHP that
needs to know the name of this it knows
whenever uses the name new address to
use this information it's pretty much
like a nickname that you can give to
your column so whenever you're
programming you can refer to it now the
last thing I want to teach you guys
about whenever used in custom columns is
how to use mathematical operations so
say for example you were having I don't
know like say we had this website and
this was like eBay and you were having a
sale of all your items where you were
selling them for $1 off so instead of
204 this would be 104 and instead of one
49.99 this would be 148 99 well in order
to you know if we don't want to ruin
this column already let's just go ahead
and make an additional custom column
with a new price so let's go ahead hop
over to where we can type in our queries
and let me show you it beforehand first
select the name and the cost from items
now what this does is it goes head and
gives us the name of the item and the
cost of the item so now let's go ahead
and add an additional custom column on
here in we will call it sale price or
something so we want to select the name
and cost and just like before but now we
want to go ahead and select cost minus 1
now what this is going to do is it's
going to create a new column that
basically takes the price or the cost of
the item and subtracts 1 from it so our
first column is going to be 148 99 the
second one is going to be 104 the
third one's gonna be $13.99 so on and so
forth however just like before this
third column is a custom column
therefore it doesn't have a name yet so
in order to name it we need to use AZ
and then we give a name like sale price
or something clever like that so now it
creates a third custom column whenever
you run this query and it treats it it
gives it the nickname sale price so
whenever we were to code this with our
you know whenever we were programming
our new and improved eBay we can use the
sale price column and we still retain
our important information now a couple
things you may want to take note of
computers have a weird way whenever they
perform math on well just whenever they
perform math because they don't perform
math like a human and they make these
little rounding errors and I'll talk
about more on that later on but we're
gonna learn how to format this properly
for example drop all these unneeded
decimal points and I'll explain to you
guys why computers can't perform math in
the way that mean you can later on but
for now that isn't the you know that's
not I don't want to get into all that
right now I just want to go ahead and
talk about custom columns and how to
nickname your custom columns so the last
thing I want to talk about is you know
how I used - as a simple mathematical
operation you can also use plus and
whenever you want to multiply you use
the asterisks the symbol above the eight
and that means multiply it by one and
divide is the forward slash so again
plus - asterisks for multiply and
forward slash for divide so you know
don't look for like a division key on
your keyboard or something because it
doesn't exist
anyways I bet your brains are jam-packed
full of information so please go take a
break go do something else go watch some
TV or something in when you're ready to
finally come back to these tutorials
because I know it's a lot then you're
welcome back so anyways thank you guys
for watching don't forget to subscribe
don't forget to add me on Google+ and I
will see you guys in the next video