Detailed Notes
1. Create table create table tutorial_one( t_one_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, city VARCHAR(50) NOT NULL, state varchar(2), zip integer, PRIMARY KEY ( t_one_id ) );
2. primary keys 3. data sizes 4. null vs not null 5. auto_increment 6. numbers 7 simple select
Transcript Text
[Music] well hello and welcome back we are continuing our series we're looking at mysql mariadb and getting really just using those as sort of a sql sql tutorial today we're getting started we've created our database and so now we're going to create tables now the concept of a table is if it was a spreadsheet it would be one of the worksheets within that spreadsheet you have columns you have rows and one of the things we will talk about is that you have a way to talk about every specific row that may not have anything to do with the values that are on it so let's start with looking at creating a table so with a create table you start with create and then table and then give it a name so we're going to call this table tutorial underscore one and then you've got some parentheses you can see open and close parentheses and then it's going to be a series of fields and then there's going to be some additional things that may show up that have to do with rules around those fields and we'll talk about each one of these so first one we've got t underscore one underscore id this is uh this next whole things which is the name so that's the name of our field would be like our column name this is the data type the next thing in this case it's an int which is an integer you can also see we can get away with integer as our type and we have some other types here which we'll talk about ah we'll go ahead and talk about now so we have a varchar now notice an int is just so it's just a number it knows roughly how big this number is supposed to be by default a varchar is a a string or a text so a var char would be a length of in this case up to a hundred characters for uh for this value for this value that we're going to call the name is name and we'll call this actually let's call this address name we'll change it to that and so it's because it's a var char that hundred spaces is up to the or characters in length is the maximum that we'll take up in space since it's of that with that var part of it it's different from a just hr that we see below a char automatically allocates a spot for every possible item that could be in there so if it was a char 100 it would allocate 100 characters in memory the var variable one here var is only going to allocate what's needed for that string so if it's a a 10 character string we're saving 90 characters in memory which as database grows can be very crucial now the chars as i said same thing it's also a text or string kind of field it also has a length but it automatically allocates out to that length so you want it to be something that is pretty close to static or from record to record about the same in length ideally like state would be perfect or country code if you use a three-letter country code then it would be a char 3 those kinds of things where all the fields are going to use exactly that amount so that helps quite a bit we have integer which is our zip code now our zip code could be an integer or it could be a string depends on how we want to do it we're going to leave it as an integer just because because there's some things we can work with now the next thing after the type is either going to be null or not null and these are not all required we'll show you a little further down here so not in all means it is a required field i have to have a value for this on every record doesn't mean i have to send it one when i create the record because there may be a default value that it will assign that we even if i don't assign a value which is what we're going to find in this case for our primary key and auto increment is for an integer and it's basically every time you put a record in there it's going to start at one or you can you can set it to a surf a certain starting number and then it's just going to add one to that each time so if you do so we do record one we'll see it's going to have an id one i the t1id of one the next record is going to have t1 id of 2 and so forth now notice so we've got null or not null means it's required we have null which means it is not required so i don't have to enter a state at all it can be empty and it's not going to care and this one has nothing by default it is null which means you you don't have to set a value and then everything else is defaulted out essentially so i can do a sort of shorthand for creating this table and then here i'm going to say that this with this line t1 id is the primary key which means i only have it is a unique value across the database or across the table so row one if t1id because that's my primary key if it's one i can't put another row in there that has a t1 id equal of one and that allows us to specifically address certain rows in the table so i'm going to take that and i'm going to pop over here and somewhere in here there we go going to use my tutorial database which is one we're going to use through most of this stuff and now let's see do i have tables in here i don't have any tables in here because i haven't created anything yet but now if i create the table so there we go create okay zero rows affected now uh jump up there now if i do show tables i can see that i have table tutorial one and if i use d-e-s-c as in like describe let's pull this up a little bigger oh there and the table name tutorial two tor eo1 then it shows me what i just created and you can see here where it's like integer it has it actually set a size to integer so that's going to be out to 11 characters 11 bytes um you can see your char with that 100 that was up here varchar 50 your chart two so you can see all of those now i can i think we looked at this before but in order to see what's in a table i do select and star means just give me all of the columns so i'm going to select star from because it's basically select columns from table name tutorial 1. in this case i don't care about anything else i just give me all the tape all the rows and there are no rows however i can do this see i'm not going to use t1id i'm going to use address name i'm going to use city state zip and just name c states that good okay and i'm gonna do my values and let's just do name one and i'm using a single quote around each of these string type things so name one city one oops the name of quotes and i'll show you actually let me do it this way so it'll be state one and uh zip code one two three four five now i'm gonna do that notice i didn't put my quotes around s1 so it's going to complain it says unknown column it thinks that's a column name instead of a string so i can put quotes around it but note that with the number since it's a number i don't it since it's which has to do with some of the naming conventions that since it knows it's a number we don't have to worry about it getting confused we don't need quotes now if we do our select we can see our record in there if we do it again we're going to certain another record so let's just one two three four five six state two city two name two and then we do select star we can see here that we have these these two rows are in there and we can see that the id that t1 id was one for the first one two for the second one now something about that if we do delete from so we're going to delete a record which is delete from tutorial one and we tell it where and we can give it some information so where t1 id equals two if we do that boom we're down to one but now if we go back and just reinsert that record that nate record two and just select you can see that the t1id is now three because it deleted that but it didn't reset the the count the auto increment for that so we're good it says okay i'm just going to keep on going and so now my new id is three now i can also if i come in let's do three three and three but now what i'm gonna do is i'm gonna give it because that's using the default if i instead give it a t one id in this case then i'm going to see now it entered it and it's set as 1. if i tried to insert it as 1 100 again now it's saying there's a duplicate but if i come in and i create a record for without entering one so let's go look here so now i'm gonna go back and i'm gonna say okay use the default what's the default after i just gave it a 100 it is 101. so it said okay you get you set it to a value so i'm going to reset the value the count value now with each of these let's see what else did i want to cover uh i don't record number simple select okay good we're in good shape so the one other thing we can look at is if we want to get rid of a table we can do it's just drop table and then table name so tutorial one now if i do that and then i show tables i have no tables so now i got to go back and create my table again but let me do this i have this time i'm going to use if i look at table1.sql i have a creation here and oops i'm going to change it to uh address name and note that if you want to use comments these little you can do a hyphen hyphen it gives you a comment so if i save this there we go and i'll go back to the database and uh what's my password i'm going to use tutorial and then i use that source if you remember that from last time i do source sable1.101.sql then it doesn't tell me what it ran but i can go back and look and i'm going to see that that table exists but it doesn't have any data now because we dropped it when we drop a table we drop all of the data in it as well now if you want to sort of reset a table like that you can use a script like i had and nice little bonuses is you can come in and you can say i'm going to drop the table if it exists because otherwise i just do this if i try to create it and it does exist me do that let's see if then if i try to create it and it does exist it's going to say it already exists so what i'm going to want to do is i'm going to come out of here and i'm going to want to say okay i'm going to go ahead and drop it but first i want to make sure i'm in the right database so if i use this as my script now i can come in and regardless of where i'm at i can do sourcetable1.sql and it's going to give me there oh and it's probably going to be there i'm already in the table or the correct database and i will see boom my table is there so there you go that is it for today we're going to come back we're going to continue to work through data types and more tables and more records and lots of different ways we can look at the database we are just getting started so hope you go out there and have yourself a great day a great week and we will talk to you next time three two one you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our series we're looking at
mysql mariadb and getting really just
using those as sort of a
sql sql tutorial
today we're getting started we've
created our database and so now we're
going to create tables
now the concept of a table is if it was
a
spreadsheet it would be one of the
worksheets within that spreadsheet you
have columns you have rows
and
one of the things we will talk about is
that you have a way to talk about every
specific row
that may not have anything to do with
the values that are on it
so let's start with looking at creating
a table
so with a create table you start with
create
and then table and then give it a name
so we're going to call this table
tutorial underscore one
and then you've got some parentheses you
can see open and close parentheses and
then it's going to be
a series of fields
and then there's going to be some
additional things that may show up that
have to do with
rules around those fields
and we'll talk about each one of these
so
first one we've got
t underscore one underscore id
this is
uh this next whole things which is the
name so that's the name of our field
would be like our column name
this is the data type the next thing in
this case it's an int which is an
integer you can also see we can get away
with integer as our type
and we have some other types here
which we'll talk about ah we'll go ahead
and talk about now so we have
a varchar now notice an int is just so
it's just a number it knows roughly how
big this number is supposed to be by
default
a varchar is a
a string or a text
so a var char would be
a length of in this case up to a hundred
characters
for uh for this value for this value
that we're going to call the name is
name
and we'll call this
actually let's call this address name
we'll change it to that
and so
it's
because it's a var char
that hundred spaces is up to the or
characters in length is the maximum that
we'll take up in space
since it's of that with that var part of
it it's different from a just hr that we
see below
a char automatically allocates
a spot for every possible
item that could be in there so if it was
a char 100 it would allocate 100
characters in memory
the var variable one here var
is only going to allocate what's needed
for
that string so if it's a
a 10 character string we're saving 90
characters in memory
which as database grows can be very
crucial
now the chars as i said same thing it's
also a text or string kind of field it
also has a length but it automatically
allocates out to that length so you want
it to be something that is
pretty close to
static or
from record to record about the same in
length
ideally
like state would be perfect or country
code if you use a three-letter country
code then it would be a char 3
those kinds of things where
all the fields are going to use exactly
that amount so that helps quite a bit
we have integer which is our zip code
now our zip code could be an integer or
it could be a string depends on how we
want to do it we're going to leave it as
an integer just because because there's
some things we can work with
now the next thing after the type is
either going to be null or not null
and these are not all required we'll
show you a little further down here so
not in all means it is a required field
i have to have a value for this on every
record doesn't mean i have to send it
one when i create the record because
there may be a default value that it
will assign that we even if i don't
assign a value which is what we're going
to find in this case for our
primary key
and auto increment
is for an integer and it's basically
every time you put a record in there
it's going to start at one or you can
you can set it to a surf a certain
starting number and then it's just going
to add one to that each time so if you
do
so we do record one we'll see it's going
to have an id one i the t1id of one
the next record is going to have t1 id
of 2 and so forth
now notice so we've got null or not null
means it's required we have null which
means
it is not required so i don't have to
enter a state at all it can be empty and
it's not going to care
and this one has nothing
by default
it is null which means you you don't
have to set a value and then everything
else is defaulted out essentially so i
can do a sort of shorthand for creating
this table
and then here
i'm going to say that this
with this line
t1 id is the primary key which means i
only have
it is a unique value across the database
or across the table
so row one if t1id because that's my
primary key if it's one
i can't put another row in there that
has a t1 id equal of one
and that allows us to specifically
address certain
rows in the table
so i'm going to take that and i'm going
to pop over here
and somewhere in here there we go
going to use my tutorial database which
is one we're going to use through most
of this stuff
and now let's see do i have tables in
here i don't have any tables in here
because i haven't created anything yet
but now if i create the table
so there we go create okay zero rows
affected now
uh
jump up there now if i do show tables i
can see that i have table tutorial one
and if i use d-e-s-c as in like describe
let's pull this up a little bigger
oh there and the table name
tutorial
two tor
eo1
then it shows me what i just created and
you can see here where it's like integer
it has it actually set a size to integer
so that's going to be out to 11
characters 11 bytes
um you can see your char with that 100
that was up here
varchar 50 your chart two so you can see
all of those now
i can i think we looked at this before
but in order to see what's in a table i
do select and star means just give me
all of the columns so i'm going to
select star from because it's basically
select
columns from table name
tutorial 1.
in this case i don't care about anything
else i just give me all the tape all the
rows and there are no rows however
i can do this
see
i'm not going to use t1id
i'm going to use address name
i'm going to use city
state
zip and just name c states that good
okay and i'm gonna do my values
and let's just do
name one and i'm using a single quote
around each of these string type things
so name one
city one
oops the name of quotes
and i'll show you actually let me do it
this way so it'll be state one
and uh zip code one two three four five
now i'm gonna do that notice i didn't
put my quotes around s1 so it's going to
complain it says unknown column it
thinks that's a column name instead of a
string
so i can put quotes around it but note
that with the number since it's a number
i don't it since it's which has to do
with some of the naming conventions that
since it knows it's a number we don't
have to worry about it getting confused
we don't need quotes now
if we do our select
we can see our record in there if we do
it again we're going to certain another
record so let's just one two three four
five six
state two
city two
name two
and then we do select star we can see
here
that we have these
these two rows are in there and we can
see that the id that t1 id was one for
the first one two for the second one now
something about that if we do delete
from so we're going to delete a record
which is delete from tutorial
one and we tell it where
and we can give it some information so
where
t1 id equals two
if we do that
boom we're down to one but now if we go
back and just reinsert that record that
nate record two
and just select
you can see that the t1id is now three
because it deleted that but it didn't
reset the
the count the auto increment for that
so we're good it says okay i'm just
going to keep on going and so now my new
id is three now i can also
if i come in let's do three
three
and three but now what i'm gonna do is
i'm gonna give it
because that's using the default if i
instead give it
a t
one id
in this case then i'm going to see
now it entered it and it's set as 1. if
i tried to insert it as 1
100 again
now it's saying there's a duplicate but
if i come in and i create a record for
without entering one so let's go look
here
so now i'm gonna go back and i'm gonna
say okay use the default what's the
default after i just gave it a 100 it is
101. so it said okay you get you set it
to a value so i'm going to reset
the value the count value
now
with each of these let's see what else
did i want to
cover
uh i don't record number simple select
okay good we're in good shape
so
the one other thing we can look at is if
we want to get rid of a table we can do
it's just drop table and then table name
so tutorial one
now if i do that and then i show tables
i have no tables so now i got to go back
and create my table again but let me do
this
i have
this time i'm going to use
if i look at table1.sql
i have a creation here
and oops i'm going to change it to
uh address name
and note that if you want to use
comments
these little you can do a hyphen hyphen
it gives you a comment
so if i save this
there we go
and i'll go back to the database
and
uh what's my password
i'm going to use tutorial
and then i use that source if you
remember that from last time i do source
sable1.101.sql
then it doesn't tell me what it ran but
i can go back and look and i'm going to
see that that table exists
but
it doesn't have any data now because we
dropped it when we drop a table we drop
all of the data in it as well
now if you want to
sort of reset a table like that you can
use a script like i had
and nice little bonuses is you can come
in and you can say i'm going to drop the
table if it exists because otherwise
i just do this if i try to create it and
it does exist
me do that let's see if
then
if i try to create it and it does exist
it's going to say it already exists so
what i'm going to want to do is i'm
going to come out of here
and i'm going to want to say okay i'm
going to go ahead and drop it but first
i want to make sure i'm in the right
database
so if i use this as my script now i can
come in and regardless of where i'm at
i can do sourcetable1.sql
and it's going to give me there oh and
it's probably going to be there i'm
already in the table or the correct
database and i will see
boom my table is there so there you go
that is it for today we're going to come
back we're going to continue to work
through data types and more tables and
more records and
lots of different ways we can look at
the database we are just getting started
so
hope you go out there and have yourself
a great day a great week and we will
talk to you
next time
three two one
you