Detailed Notes
1. Create a second table 2. Foreign keys 3. alter table ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ]; 4. Date types: DATE, TIME, DATETIME, TIMESTAMP, YEAR
Transcript Text
[Music] well hello and welcome back we are continuing talking about our mysql mazria database mariadb type tutorials and we are into day four and today we are going to continue talking about tables basically and the creation of but we talked about last time we talked about creating just a table and putting some data into it this time we're going to talk about a we're going to talk about creating a parent table a child parent print child relationship so if i can remember my password and my user id and type that right i'll be able to get into database and if we see our tables we've got our just we've got our one table if you remember tutorial one is our table and we had just a sort of like an address kind of table so this time we're going to create a sort of like a user we're going to call him tutorial parent so this is what we're looking at making next and we're going to see a lot of similarities that we've seen before so we've got a parent id is the the key the primary key that we talked about before before it was whoops i have to make sure i hit the right keys when i switch before we had t1 id for tutorial one this one is going to be uh t parent id so tutorial parent id and again not null auto increment so we're gonna be able to count those up now here we're gonna actually because this is the parent we have to we're going to give it a child record so we are going to point to it by giving it t1 id so wherever we create a record here and let's make sure i still have some of those oh i got too many type it to me letters okay so there's nothing in there so let's go see if i can do an insert real quick got one [Music] let's go back uh so we'll do four and we'll create another one three and a two and we'll just go with three records for now so if we go back there so we've got essentially call these our address type records so we've got those so we're gonna do is we're going to be able to grab it we're going to refer to it based on that id so that id we're going to store here and then for this record this we'll call it a user we're going to have a name let's call it username i like that a little better and it's again we're just going to have a varchar 100 not null we're going to use the default we're going to talk about defaults a little bit more here i'll come back to that and then email login i'll call it user password just because it's better i know pwd will work but we're going to call it user login so we'll and these we've seen with the varchars now we have a primary key which we've talked about before he doesn't need to be i don't know i had that type twice and then um now the way we're gonna link is we have to create a constraint and we're gonna call and we give it a constraint name so we're gonna call it simple fk for simple foreign key and we're gonna tell the type of constraint so foreign key is the type of constraint and the way we do it with this one is we're going to say what column in this this table so t1 id we're going to link that to by the references command by that keyword to the table name tutorial1 and then t1id in there so we could call this something else i could call this like if i want to call this address id and call this address id then it would link those two that way even though i'm changing the names but for consistency oh accidentally hit the wrong way i don't want to save right now consistently let's keep the names the same so it's a little easier to deal with our inserts and things of that nature i need to stop accidentally saving stuff now let's talk about that default we've we have not used the default keyword before but we have mentioned the idea of there being default values that can be assigned and if you look at the description you can see where there's a default so here default's always null we haven't really messed with that before and that is the default if you don't give it one the default is going to be null but now when we create this table and let's hope that i entered everything properly so that it so that it does create it does now if i look at tutorial parent then we'll see that the default is not assigned so let's actually do a record into that i'm going to insert into tutorial parent and let's give it uh t1id because we're gonna we're gonna go ahead and tie it into an address uh username email user login and password okay and value now let's do this what values are going to be so we need to use a valid well we do need to use a valid t1 id so we've got one two and three are valid so let's just tie it to well let's type to three we'll start we'll be a little different type to three the username is going to be user one the email let's call user one at test.com uh let's see each login will be user one and a password i'll just call it test user one let's see if that works it does so now if we do that select star from tutorial parent if i enter that letter right then we'll see that we've got our record in there now let's take that same record so i said that it has to be valid it has to exist so now we know that we have values 1 2 and 3 in our that tutorial 1 table let's change it to five and see what happens now we get this it's going to tell us we can't do that because we cannot update or add or update a child row the foreign key constraint fails and so remember we created this foreign key and named it simple fk well it tells us exactly that in tutorial parent the constraint simple fk foreign key that one that we created it failed so it's telling us right there that foreign key tells us that doesn't exist which says okay that t1 id that we sent does not exist in tutorial 1 as a t1 id value so we're cool now we can however because this is a this has created a mini to one relationship so we can reuse that address that we used before it's not like a primary key it doesn't have to be unique and we can create user two and we're gonna come in and now if we look at all our tables we see that it's got that even though two and both of them refer to uh t1 id and we can see here where it tried to increment actually that tried because of this insert that failed insert it did try to increment the t parent id but failed so it just said well i'm going to go ahead and jump to the next available id or the next id and my counting up in my auto increment so we have our values there now if we want to get further if we want to actually utilize this as part of a report or something like that we can do select star we use that before from and this is where we're going to use what's called an inner join so we're going to select everything from tutorial parent so we want each of these records here but we also want to instead of that id we also want to see what was their address information so in that case and we're going to do an alias so we're going to call it as tp so that'll be so tutorial parent is instead going to be tp as we refer to it elsewhere and then we do this thing called an inner join which means that we are going to only grab records where this next thing occurs so we're going to interjoin uh tutorial 1 and we'll call it t o uh no we're not because that's it doesn't like that we'll call it t1 on so it says i'm going to take data from this other table and i'm going to join it where uh it's on instead of where clause or tp dot t1 id equals the record in t1 t1 t1 dot t1 id and so now we can see that it pulls this very long thing here but it's going to pull so we can see the parent record here uh a parent one id username all the way through the password and then here's the matching record from the tutorial1 table and so you can see down here where it comes into address name now you can get a little more you know you can clean that up a little bit so you don't see the duplicates so i can instead and i'm going to use these aliases that i showed so i can do like tp dot username comma tp.email comma tp dot user login comma i'm not going to show the password so i'm going to tp dot address name comma oh that's not tp now that's t1 because it's in the t1 table t1.city and t1.state oh and t1.zip so now i can clean that up a little bit and i have the username email and user login from the parent and then from the address i have the name city state zip so that gives me a join that i can do now another thing i wanted to touch on in this episode we've talked about is being able to alter a table so once i've created a table we've seen that like here once i've created a table before we would drop it and then recreate it but instead i can do an alter and let me see if i've got one that's like that'll probably work no don't want that there we go and in that what you do is so i can take let's say i want to add a column we can use this for modify as well but for right now let's go ahead and do alter table uh let's see what i want to add let's go ahead and add to the user table let's uh we'll add a login date so because we haven't seen dates before so we're going to alter table tutorial parent add column and we're going to give it a name so it will be let's uh creation whoops creation date it's going to be a date time and it can be null actually i don't think it's co i don't think it's column let's go see i bet it is way back i think it is it's just a col as we are scrolling back through a lot of commands to see if i can find where i last use it which a long time ago apparently wow i got way back on that okay it may be okay i'm just gonna do it this way it's better to go if i can do the pace there okay i got right all right so now if we look uh describe tutorial parent we can see that now i it's this date this creation date date time is in there and if i look from there oops i will see oops i need to do an f if i look here and see that it's null anywhere now i can do an update i'm just going to make it very simple right now just to show you uh update tutorial parent and i'm going to set the creation date to now so now i can see where now i can see dates there now you have uh there are variations and dates you can do a date time which is what we did where you've got a time you have a date 22 4 1 and then you have a time you can also do just a date or just a time or a time stamp or a year now time stamp i do want to do that is if i've got that here here we go so let's add so that was created and let's get the column updation update date time stamp and i will get into some of the uh update there we go and he's going to be default by null so we should be good so if we come in here and now if we do the same thing we do that update in the timestamp instead we do update date and show you what a timestamp looks like and we will get more into dates and times and everything else at a later date and later discussions but here because it's going to format it even though it's a time stamp it's going to give me that full date time but depending on what you pull out of it you'll get a long integer which is basically a system time kind of thing this one's nice enough to format stuff if i wanted to do oops if i wanted to add let's say creation let's see that's called start year and make it a year then we can look at it here let's see they're all known but if we update it then we will find out oh it doesn't like to do that so start year it doesn't like to do it with the now so i have to format that out so instead let's do 2022 and we can see that there you go got all our data there so i think that'll probably cover for this time we're in a good spot so we have added a child apparent child relationship in our data we have talked about dates and we're going to continue digging into those as we go into future episodes so for now we'll wrap this one up uh we'll have show notes for a lot of these calls as well as always and uh as always go out there 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 talking about our mysql
mazria database
mariadb type tutorials and we are into
day four
and today we are going to continue
talking about tables basically
and the creation of but we talked about
last time we talked about
creating just a table and putting some
data into it this time we're going to
talk about
a
we're going to talk about creating a
parent table a child parent print child
relationship
so
if i can remember my password and my
user id and type that right
i'll be able to get into
database and
if we see our tables we've got our just
we've got our one table if you remember
tutorial one is our table
and we had just a sort of like an
address kind of table so this time
we're going to create a sort of like a
user we're going to call him tutorial
parent so this is what we're looking at
making next
and we're going to see a lot of
similarities that we've seen before so
we've got a parent id is the
the key the primary key that we talked
about before
before it was whoops
i have to make sure i hit the right keys
when i switch
before we had t1 id for tutorial one
this one is going to be
uh t parent id so tutorial parent id and
again not null auto increment so we're
gonna be able to count those up now here
we're gonna actually
because this is the parent we have to
we're going to give it a child record
so we are going to point to it
by giving it t1 id so wherever we create
a record here
and let's make sure i still have some of
those
oh
i got too many
type it to me letters okay so there's
nothing in there so let's go see if i
can do an insert real quick got one
[Music]
let's go back
uh so we'll do four
and we'll create another one
three
and a two
and we'll just go with three records for
now
so if we go back there so we've got
essentially call these our address type
records so we've got those so we're
gonna do is we're going to be able to
grab it we're going to refer to it based
on that id
so that id we're going to store here and
then for this record this we'll call it
a user we're going to have a name let's
call it username i like that a little
better
and it's again we're just going to have
a varchar 100
not null we're going to use the default
we're going to talk about defaults a
little bit more here i'll come back to
that and then email login
i'll call it user password just because
it's better i know pwd will work but
we're going to call it user login
so we'll
and these we've seen with the varchars
now
we have a primary key which we've talked
about before
he doesn't need to be i don't know i had
that type twice and then um now the way
we're gonna link
is we have to create a constraint
and we're gonna call and we give it a
constraint name so we're gonna call it
simple fk for simple foreign key
and we're gonna tell the type of
constraint so foreign key is the type of
constraint and the way we do it with
this one
is we're going to say what column
in this
this table so t1 id we're going to link
that
to by the references command
by that keyword to the table name
tutorial1
and then t1id in there so we could call
this something else i could call this
like if i want to call this
address id
and call this
address id then it would link those two
that way even though i'm changing the
names but for consistency
oh
accidentally hit the wrong way i don't
want to save right now
consistently let's keep the names the
same so it's a little easier to deal
with our inserts and things of that
nature i need to stop accidentally
saving stuff
now let's talk about that default we've
we have not used
the default keyword before
but we have mentioned the idea of there
being default values that can be
assigned and if you look at the
description
you can see where there's a default so
here default's always null we haven't
really messed with that before
and that is the default if you don't
give it one the default is going to be
null but now when we create this table
and let's hope that i entered everything
properly so that it
so that it does
create it does
now if i look at
tutorial parent
then we'll see that the default is not
assigned
so let's actually do a record into that
i'm going to insert into
tutorial parent
and let's give it
uh t1id because we're gonna we're gonna
go ahead and tie it into an address
uh username
user login
and password okay
and value now let's do this what values
are going to be so we need to use a
valid well we do need to use a valid t1
id so we've got one two and three are
valid so let's just tie it to well let's
type to three we'll start we'll be a
little different
type to three
the username is going to be
user one
the email
let's call user one at test.com
uh let's see each login will be user one
and a password i'll just call it test
user one
let's see if that works it does
so now if we do that select star from
tutorial parent
if i enter that letter right then we'll
see that we've got our record in there
now
let's take that same record so i said
that it has to be valid it has to exist
so now we know that we have values 1 2
and 3 in our
that tutorial 1 table let's change it to
five
and see what happens
now we get this it's going to tell us we
can't do that
because
we cannot update or add or update a
child row
the foreign key constraint fails and so
remember we created this foreign key
and named it simple fk
well it tells us exactly that
in tutorial parent
the constraint simple fk foreign key
that one that we created it failed so
it's telling us right there that foreign
key tells us that doesn't exist which
says
okay that t1 id that we sent does not
exist in tutorial 1 as a t1 id value
so we're cool
now we can
however
because this is a this has created a
mini to one relationship so we can reuse
that address that we used before
it's not like a primary key it doesn't
have to be unique
and we can create user two
and we're gonna come in
and now if we look at all our tables
we see that it's got that even though
two and both of them refer to
uh t1 id
and we can see here where it tried to
increment actually that tried because of
this insert that failed insert it did
try to increment the t parent id
but failed so it just said well i'm
going to go ahead and jump to the next
available id or the next id and my
counting up in my auto increment
so we have our values there
now if we want to
get further if we want to actually
utilize this
as part of a report or something like
that
we can do
select star we use that before from and
this is where we're going to use what's
called an inner join so we're going to
select everything from tutorial parent
so we want each of these records here
but we also want to instead of that id
we also want to see what was their
address information
so in that case and we're going to
do an alias so we're going to call it as
tp so that'll be so
tutorial parent is instead going to be
tp as we refer to it elsewhere
and then we do this thing called an
inner join which means that we are going
to only grab records where this next
thing
occurs so we're going to interjoin
uh tutorial
1
and we'll call it t o uh no we're not
because that's it doesn't like that
we'll call it t1
on so it says i'm going to take
data from this other table and i'm going
to join it where
uh it's on instead of where clause or tp
dot
t1 id
equals the record in t1 t1
t1 dot
t1 id
and so now
we can see that it pulls this very long
thing here but it's going to pull so we
can see the parent record here
uh a parent one id
username all the way through the
password and then
here's the matching record from the
tutorial1 table and so you can see down
here where it comes into address name
now you can get a little more
you know you can clean that up a little
bit so you don't see the duplicates
so i can instead and i'm going to use
these aliases that i showed
so i can do like tp dot
username
comma tp.email
comma
tp dot
user login
comma i'm not going to show the password
so i'm going to tp dot
address name
comma
oh that's not tp now that's t1
because it's in the t1 table
t1.city
and t1.state
oh and t1.zip
so now i can clean that up a little bit
and i have the username email and user
login from the parent and then from the
address i have the name city state zip
so that gives me a join
that i can do now another thing i wanted
to
touch on in this episode
we've talked about is being able to
alter a table
so once i've created a table we've seen
that like here
once i've created a table before we
would drop it and then recreate it
but instead i can do an alter
and let me see if i've got one that's
like
that'll probably work
no don't want that
there we go
and in that what you do is so i can take
let's say i want to
add a column
we can use this for modify as well but
for right now let's go ahead and do
alter
table
uh let's see what i want to add let's go
ahead and add
to the user table let's
uh we'll add a login date so because we
haven't seen dates before so we're going
to alter table
tutorial parent
add column
and we're going to give it a name so it
will be
let's uh creation
whoops
creation date
it's going to be a date time
and it can be null
actually i don't think it's co i don't
think it's column let's go see i bet it
is
way back i think it is
it's just a col
as we are scrolling back through a lot
of
commands to see if i can find where i
last use it which a long time ago
apparently
wow i got way back on that
okay it may be
okay i'm just gonna do it this way
it's better to go if i can do the pace
there okay i got right
all right so now
if we look
uh describe tutorial parent
we can see that now i it's this date
this creation date date time is in there
and if i look
from there
oops i will see
oops i need to do an f
if i look here and see that it's null
anywhere now i can do an update i'm just
going to make it very simple right now
just to show you
uh update
tutorial
parent
and i'm going to set the
creation date
to
now
so now i can see where now i can see
dates there
now you have uh there are variations and
dates
you can do a date time which is what we
did
where you've got a time you have a date
22 4 1 and then you have a time you can
also do just a date or just a time or a
time stamp or a year now time stamp
i do want to do that
is if i've got that here here we go so
let's add
so that was
created and let's get the column
updation update date
time stamp
and i will get into some of the
uh update there we go
and he's going to be default by null so
we should be good
so if we come in here
and now if we do the same thing we do
that update
in the timestamp instead we do
update date and show you what a
timestamp looks like and we will get
more into dates and times and everything
else at a later date and
later discussions
but here because it's going to format it
even though it's a time stamp it's going
to give me that full date time
but depending on what you pull out of it
you'll get a
long integer which is basically a system
time kind of thing
this one's nice enough to format stuff
if i wanted to do
oops if i wanted to add let's
say creation
let's see
that's called start year
and make it a year
then we can look at it here
let's see they're all known but if we
update it
then we will find out
oh it doesn't like to do that so start
year it doesn't like to do it with the
now so i have to format that out so
instead let's do 2022
and we can see that there you go got all
our data there
so i think that'll probably cover for
this time
we're in a good spot
so we have added a child apparent child
relationship in our data
we have talked about dates and we're
going to continue digging into those as
we go into future episodes
so for now we'll wrap this one up uh
we'll have show notes for a lot of these
calls as well as always
and uh as always go out there have
yourself a great day a great week and we
will talk to you next time
you