Ads 468x60px

Wednesday, December 7, 2011

MySQL Tutorial: Create SQL Tables

Create Tables in phpMyAdmin
 


One of the easiest way to create a table is through phpMyAdmin, which is available on most hosts that offer MySQL databases (ask your host for a link). First you need to login to phpMyAdmin.
On the left hand side you will see "phpMyAdmin" logo, some small icons, and below them you will see your database name. Click on your database name. Now on the right hand side any tables you may have in your database will be displayed, as well as a box labeled "Create new table on database"
Click this and create a database as we have in the diagram below.

Adding Rows and Columns 
 
 Let's say we work in a doctor's office and wanted to make a simple table with a person's name, age, height, and the date we collected this information. On the previous page we entered "people" as the name of our table, and chose to have 4 fields. This brings up a new phpmyadmin page where we can fill in the fields and their types to add rows and columns. (See an example above)
We have filled in the field names as: name, age, height, and date. We have set the data types as VARCAR, INT (INTEGER), FLOAT and DATETIME. We set a length of 30 on the name, and have left all other fields blank.

 SQL Query Window in phpMyAdmin
Perhaps a quicker way to add a table is by clicking on the small "SQL" button on the left hand side below the phpMyAdmin logo. This will bring up a query window where we can type our commands. You should run this command:

 CREATE TABLE people (name VARCHAR(30), age INTEGER, height FLOAT, date DATETIME) 
As you can see, the command "CREATE TABLE" does exactly that, creates a table which we have called "people". Then inside the ( brackets ) we tell it what columns to make. The first is called "name" and is VARCAR, the 30 indicates we are allowing up to 30 characters. The second, "age" is an INTEGER, the third "height" is a FLOAT and the forth "date" is DATETIME.
Regardless of which method you chose, if you would like to see a breakdown of what you just did click on the "people" link that now appears on the left hand side of your screen. On the right you should now see the fields you added, their data types, and other information.

Using Command Lines
If you prefer you can also run commands from a command line to create a table. A lot of web hosts don't give you shell access to the server anymore, or allow remote access to the MySQL servers. If you want to do it this way you may have to install MySQL locally, or try this nifty web interface. First you will need to login to your MySQL database. If you are unsure how try using this line: mysql -u Username -p Password DbName Then you can run the command:

 CREATE TABLE people (name VARCHAR(30), age INTEGER, height FLOAT, date DATETIME); 
To view what you have just created try typing in:
describe people;
No matter which method you chose to use, you should now have a table setup and ready for us to enter data into.

0 comments:

Post a Comment