This tutorial will show you how to create and query a database using SQL and the mySQL command terminal. If you don't currently have mySQL installed head over to www.mysql.com and go grab yourself a copy after all its totally free!! With mySQL installed on your system you are good to go and create your first database.

	Note
If you don't want to install mySQL on your home system and you have web hosting with phpMyAdmin installed you can follow these steps by using the SQL window. Some steps will differ however to this tutorial.

Step 1

Locate the mySQL terminal on your system if you are using windows look for mysql.exe. Once you have found it open the executable file and you will be presented with a command prompt, this is the mySQL interface with which we will interact with.

Step 2

Time for our first bit of SQL, with the command prompt open type in:

						CREATE
  DATABASE kirupa;

When you have typed this press the return key. You will see this message:

						QUERY
  OK, 1 row affected

Step 3

With a new database created called kirupa, we now need to use that database to create our tables, to do this we use the following command:

USE kirupa;

Once typed hit return again (Note: when you see a line ending with a semi-colon this denotes the end of our SQL command thus you press the return key). You will see a short message saying DATABASE CHANGED this means exactly that, you are now using the kirupa database we created.

Step 4

Now we are going to create a new table in our kirupa database. Type in the following:

						CREATE TABLE
  users (

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(70),

  location VARCHAR(20),

  date DATE NOT NULL

  );

What we have just done is create a table that looks like the following:

id	name	location	date
INT	VARCHAR(70)	VARCHAR(20)	DATE

Column details:

Column Name	Description
id	This column accepts integer values (e.g. 0........9). This column has been selected to be our primary key and it increments by 1 each time a record is entered (this will be clearer when we insert some data into our table).
name	This column accepts up to 70 characters of data.
location	This column accepts up to 20 characters of data.
date	This columns accepts a DATE value (e.g. 2005-04-10)

A simplified look at our database:

There is more of this tutorial in the next section. Let's go there now!

---

---

Step 5

With a table all set up lets now insert some data into it. BUT before we do that lets look at our tables workings, enter the following:

						DESCRIBE users;

You should see:

Looking at the table structure we can see what values are accepted by which columns.

Well lets now insert some data into our users table, enter the following:

INSERT INTO
  users SET

  name=”Granville Barnett”,

  location=”Bradford”,

  date=CURDATE();

What we have just done is enter some data into our table. I used a function in mySQL called CURDATE() to create a new date value.

ENTER A FEW DIFFERENT RECORDS USING THE METHOD I JUST SHOWED YOU.

Step 6

We are now going to view the records in our table using SELECT. Enter the following:

						SELECT * FROM users;

You will now see:

What you see are the contents of your table.

	Note
I spoke earlier about the AUTO_INCREMENT becoming more noticeable later on, well here you see that the id column is incremented by 1 every time we enter a record – this is the AUTO_INCREMENT in practice.

We can use several various ways to order the way our data presented to us, if we want we can view our table's contents in the order of our users' locations (a...z), lets do that:

						SELECT name, location FROM users ORDER BY location;

Press Return/Enter, and you will see just the name and location columns of our table:

I am displaying the two columns for two reasons. The first reason being the clarity of the results – I only wanted to order my results by location (a..z) and I wanted to see the users' names. Second, I specifically wanted to exclude the id and date columns.

	Note
There are many ways to view your data using the SELECT statement – so many that its out of the scope of this tutorial to cover them! To see all the ways (and there are tons of them) visit www.mysql.com.

You are almost done, but before you can celebrate, the next section awaits you. Onward!

---

---

Step 7

Now we are going to update a record, here is where our id column comes in very handy!! In the mySQL terminal type in the command to view the records so that all columns appear then find a user to which you want to modify their details – look at their corresponding id number!! then type in the following command:

						UPDATE users SET

  name=”Kirupa.com”,

  location=”Spain”,

  date=CURDATE()

  WHERE id=4;

You should see:

See how I update record with id 4 (WHERE id=4) I changed the name of the user and location of the user to Kirupa.com and Spain respectively.

Hopefully by now the power of SQL is sinking in, and I've barely touched the surface!! Now let me explain one last key function that you will no doubt need – the DELETE statement.

Step 8

Once again display the contents of the users' table and find someone you want to delete then make a note of their corresponding id number. Type in the following:

						DELETE FROM users WHERE id=4;

In the above statement I deleted this row:

Sorry Kirupa. Now lets view the contents of our table again and see if anything has changed.

See how the kirupa record has gone?? well I hope you do!!

	Note
Use the DELETE statement with great care as unlike some applications etc (mySQL is not really an application but rather a database server) you cannot retrieve a previously deleted record. It is possible to make incremental backups of your database and restore data that way – again this is out of the scope of this tutorial.

 
Conclusion
What we have just done is covered the following:

• Creating a database
• Creating a table and specifying the tables column names and their acceptable values.
• Inserting data into our table
• Selecting our data in a specific way that we see only the information we asked for and in a specific order
• Updating a record in our table
• Deleting a record in our table

Hopefully by now you will know these statements well! Don't forget that in languages such as PHP you can quote the exact same query statements covered in this tutorial.

If you have any questions, please contact me or post on the forums.

G www.apixelmind.com

---

---

Just a final word before we wrap up. What you've seen here is freshly baked content without added preservatives, artificial intelligence, ads, and algorithm-driven doodads. A huge thank you to all of you who buy kirupa's books, became a paid subscriber, watch the videos, and/or interact on the forums.

Your support keeps this site going! 😇