phpMyAdmin MySQL database – How to create table, insert data, set up foreign key

phpMyAdmin MySQL database – How to create table, insert data, set up foreign key


Hey guys it’s Eddie the Magic Monk. In
this lesson we’re going to talk about how to create tables and populate the
tables with data inside phpMyAdmin. So I’m going to assume that you guys have
already learned how to log on to phpMyAdmin and that you’ve already created
a MySQL database so if you don’t know how to log in to this page and you don’t
see a database that’s already on the left then you need to probably see your
teacher to try and get it set up for you or look up a tutorial on how to set that
up but once you’ve got it set up okay you’re going to select the database that
you want to create the table for and you’re going to type in the name of the
table you want to create so let’s say I want to create a table for a students
database alright students in a school so let’s
put in students you know how many columns do we want so let’s say we want
five columns so click go and now inside the students table we can decide what
each of our columns are going to be so here is where we type in the name of
each column so let’s start off with the student ID student ID and the ID is
going to be an integer because it’s just going to count up in whole numbers 1 2 3
and so on and the ID is going to be our primary key primary key means that it’s
going to uniquely identify every single row in this table so every single row or
record is going to have a different ID and you’re going to click on auto
increment because we don’t want to manually set
the ID every time we want it to just automatically set it for us okay then we
have a student let’s say we want the students to have names so first name alright the first name of the student
how many characters do you think it’s going to have so let’s put in here 30
characters now variable character as opposed to integer can store a string
strings of text so you can have a, b, c, d and so on so that’s why we can’t use
integer because integer can only store whole numbers so first name is a
variable character of 30 characters probably going to have a last name
another variable character 30 characters and let’s put in here grade grade 2 digits all right because you
probably while the grades can only be from 1 to 12 so that’s up to two
digits and then let’s say let’s put in address and that’s going to have a lot
more information so let’s put in text because we don’t know how many
characters it’s going to take up lastly we want to choose a storage engine and
let’s choose InnoDB because if you hover your mouse over the tooltip that comes
up says that it allows foreign keys which is very important so I’m going to
create another table in a second with a foreign key that refers to the key in
this table so foreign key is basically a column that
refers to the primary key in another table so InnoDB so click Save and now
it’s created the students table so in the table students let’s insert some
fake data so let’s put in here first person Edward Smith grade 11 address 21
Jump Street 21 Jump Street let’s put in here just any suburb
Taipei Taiwan and let’s put in here someone else Emma Smith Emma Jones grade
9 address 5 Mars Street Wellington it’s all made up so just click go and you can
see that it’s inserted two records and this is the SQL that is used if you are
interested so if you click browse and now you can see the two people and then
what we want to do now is I’m going to create another table so let’s go back to
the database and let’s click new to create another table and this time I’m
going to create a table called studies so the table studies is going to have
the student ID from the students table and it’s going to have subject name as
variable character let’s say 20 characters because the name of the
subject is just put in 30 characters in case and then if you save that –
storage engine and InnoDB if you save that now it has the studies table so we click
studies go to structure again what you’re going to do is basically check
box both fields and click primary why because both
fields combined makes up the primary key right because the student ID is not
going to be unique in this table which you’re going to see in a second the
subject name is not going to be unique but the both of them combined
will be unique for every single row so let’s insert some data but just before
we do that let’s click relation view and we’re going to make the student ID refer
to the ID column in the students table and if you click Save what’s going to
happen is when you insert some data okay for student ID it is limited to what
you’ve inserted in the other table so the value is going to be one or two you
can’t have a value outside of that so first student is going to study the
subject English and the first student is going to study subject maths and let’s
insert insert a few new rows second student is going to study maths second
student is going to study sport and so on so click go and you will see that
it has populated this table with some data showing ID one is studying
English so what is 1 so you can click on that and it shows you student ID 1 is
Edward ok so yeah that’s what’s good about the foreign key so you can click
on this to find out who the student is who’s student number 2 that study sport
click that and it shows you that it’s Emma Jones
okay thanks for watching this tutorial guys see you next time.

Danny Hutson

28 thoughts on “phpMyAdmin MySQL database – How to create table, insert data, set up foreign key

  1. How to Export and Import Database in MySQL Database Table
    http://datainflow.com/export-import-database-mysql-database-table

  2. i love you!!! thank you so so so so so so much! i have an exam in a couple of days and this really helped me out since wamp DID NOT want to load and neither did any similar app this has been helpfull thank you!:

  3. Is there anyway I can personally contact you? I really want to learn coding in phone. I'm in Ghana and I really want to learn coding. Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *