About Me
Miscellaneous
Geek Stuff
Skydiving
Geek Stuff : Tutorials : MySQL : Creating a Database and Table in MySQL
In this tutorial we will be creating a new MySQL database. Then we will create a new table, and insert some records into it. We will wrap up with a couple of queries to test it out and see how it works. Woo?
Note: Installing the MySQL server will not be covered in this tutorial. It assumes you already have a functioning MySQL server available to work with.
In the archives you can still get to my old Apache, MySQL and PHP installation tutorial. It may be useful, but has not been updated since 2002 and will probably not be updated again. Most current Linux distributions come with all of this pre-installed now.
To begin you will need to login to your MySQL server. Since this tutorial is focused on working from the Linux command line, login using your MySQL username and password.
mysql -u username -p
Create a MySQL Database and Table
If you haven't already, I would highly suggest going over to http://www.phpmyadmin.net/, then download and install "phpMyAdmin" on your server. Any worthwhile web hosting company probably already has it installed for you though. It will make your life easier and you won't have to go through the tedious UNIX/Linux command line stuff I'm about to cover.
Create a Database
We will begin by creating a MySQL database called "myDatabase" with the following command.
CREATE DATABASE myDatabase;
Since we will be working with this database for the remainder of this tutorial we will now tell the MySQL server that we want the following commands and queries to be made against our new `myDatabase' database using the following command.
USE myDatabase;
Create a Table
Now we will add a table to our database called "people" that will contain the names of ourself and some other cool people.. Yeah, go download phpMyAdmin and skip this geek stuff. :)
CREATE TABLE people (
peopleID int(5) NOT NULL auto_increment,
firstName varchar(255) NOT NULL,
lastName varchar(255) NOT NULL,
birthDate date NOT NULL default '0000-00-00',
recstate tinyint(1) NOT NULL default '1',
PRIMARY KEY (peopleID)
);
Populate the Table
Now we will add some data records to our shiney new table to make it useful. We'll do this with the following INSERT command to insert as many rows/records as you like.
INSERT INTO people (peopleID, firstName, lastName, birthDate, recstate) ↵
VALUES (NULL, 'Tim', 'Patterson', '1971-05-02', 1);
Note: The values are inserted in the order the fields appear in the first set of () parens. And the first value is "NULL" since the `peopleID' field is assigned by MySQL (and this can be left out altogether if you prefer), since it's the "primary key" we set it up to "auto_increment" when we created the table. Also note that text strings and dates need to be surrounded by a single quote, and integers (like our 'recstate' field) don't.
Select Some Records to Test
If you're still doing it the hard way, via the console/shell, you can now query the database to see the results. I added a few more records so we'd have something to work with in the PHP code later.
SELECT * FROM people;
Running the previous simple select query (* is a "wild card" and gets everything) should return all of the rows in the table. In my table it looks something like this in a terminal window.
+-----------------------------------------------------------+ | peopleID | firstName | lastName | birthDate | recstate | +-----------+-----------+-----------+------------+----------+ | 1 | Tim | Patterson | 1971-05-02 | 1 | | 2 | Edward | Van Halen | 1955-01-26 | 1 | | 3 | Billy | Sheehan | 1953-03-19 | 1 | | 4 | Mark | Hoppus | 1972-03-15 | 1 | | 5 | Tom | DeLonge | 1975-12-13 | 1 | | 6 | Travis | Barker | 1975-11-14 | 1 | +-----------------------------------------------------------+
Note: In `phpMyAdmin' you would need only click on the "Browse" tab, and it all looks beautiful in an HTML table complete with alternate shading on the rows for clarity. Woo?
