A Quick MySQL Tutorial

The following tutorial will help you get started with MySQL. The example will create a simple database for storing friends contact information.

Conceptually your information will look something like this:

ID	NameFirst	NameLast	Phone
1	John	Doe	123-4567
2	Jane	Doe	867-5309

Installation and Setup

We want to use the database named "mydb".

USE mydb;

Table Creation

The following code creates a table in the current database, mydb, called "Friends". This table will have 8 columns or fields. Note that each field is comma separated.

CREATE TABLE Friends
(ID INT AUTO_INCREMENT PRIMARY KEY,
NameFirst VARCHAR(64),
NameLast VARCHAR(64),
Address VARCHAR(64),
City VARCHAR(64),
State VARCHAR(64),
Zip INT(5),
PhoneHome VARCHAR(64)
);

Each field needs a type. Is it a word? a number? multiple paragraphs of text? Lets take a look at the second field (we'll skip the first field, ID, for now).

NameFirst VARCHAR(64),

NameFirst is what we want this field to be called.
VARCHAR is the field type. In this case, variable character. This will work for letters, numbers, pretty much anything thats on a regular keyboard.
(64) is the length. Meaning that this field will only hold a maximum of 64 characters. Most people dont have first names longer than this...

Zip INT(5),

Here we used the INT type, which stands for integer. This is good for whole numbers. If we wanted to have decimals, we could use the type DOUBLE. This type takes two lengths, one for the number of digits before the decimal point, and one for after. For example:

BankAccount DOUBLE(4,2),

PhoneHome is not an INT because we usually have a - in there, but this is up to you.
Other useful types include DATE and TEXT.

ID INT AUTO_INCREMENT PRIMARY KEY,

So back to this line. As you should have guessed by now, it creates an integer field called ID. So whats that other stuff after it? Well its helpfull in these databases for each entry to have a unique identifier. In this case, each entry will have a number associated with it. No two will have the same number, and as entries are added, this number will increment automatically. Its just good practice to have this field in (almost) every table.

To delete an entire table, use the DROP command.

DROP TABLE Friends;

Data Manipulation

So we have our table but we have no data in it. Lets fix that.

INSERT INTO Friends ( ID, NameFirst, NameLast, City )
VALUES ( NULL, "John", "Doe", "New York" );

Simply put, this makes a new row in the table, and inserts into that row the value of NULL for ID, John for NameFirst, and so forth.

So thats great and all, but we left out a lot of Johns information! If we run the previous command again with the other data, it will create a new row. So we need a way to identify which row we want to modify. Sure are lucky we have a unique number for each row...

UPDATE Friends SET State = 'Alaska' WHERE ID = '1';

So now we're UPDATing table Friends, and we're SETting the State to Alaska, WHERE the ID is 1.

DELETE FROM Friends WHERE ID = '2';

This deletes everything from Friends that has an ID of 2. Glad these numbers are unique!

ALTER TABLE Friends DROP NameLast;

This alters table Friends, and deletes the field NameLast. Similarly, to add a column, replace DROP with ADD.

Viewing and Searching

So now that we have a table and data in the table, how do we see it? To do that we must write a query.

SHOW TABLES;

Shows you all the tables in the current database.

SHOW COLUMNS FROM Friends;

Shows you the fields you have in table Friends.

SELECT * FROM Friends;

This query selects everything (thats what the * means) from table Friends and returns or displays it.
So we can use this same logic to only select certain rows too.

SELECT * from Friends WHERE ID = '3';

This will only show you the row where the ID is 3, instead of the whole table.

SELECT * from Friends WHERE ID > '5' AND ID < '10';

Excellent, we can add boolean logic to our queries! This shows all rows with an ID between 5 and 10.

SELECT * FROM Friends WHERE NameFirst LIKE '%Jo%';

This query is useful when we dont quite remember what someones name is. There was a Jo somewhere in there... The % acts as a wild card, matching everything. With the "Jo" between two %, anyone with Jo somewhere in their first name will be returned.