Quick Reference – MySQL

Chapter 1 – Introduction to MYSQL

What is MYSQL?

A database is a storage place for information. Usually the information is organized into a series of related categories, where it is stored in an orderly fashion, and can be modified, updated, searched for, retrieved and/or deleted as often as necessary.

MYSQL was once the most popular open source database management system. It was free, and earned a reputation for reliability and ease of use. Nowadays, MariaDB is the open source variation of MYSQL, but for now we’ll continue to call it MYSQL rather than getting into the long and complicated history.

Do you ever visit Youtube, Wikipedia, Flickr or Facebook? Have you ever considered how that these gigantic websites manage all of their data, users, etc.? These, and many thousands of other websites make use of massive databases.

Databases are made up of one or more tables of organized data that can be linked together. If you are familiar with Excel/Openoffice spreadsheets or HTML tables, where rows of data are labeled at the top of each column, then you will already have the database-concept half-mastered. If not, stick with us, and we’ll explain!

MySQL is officially pronounced “my-ess-cue-el”, but is sometimes called “my sequel” or other variations.

MYSQL Requirements

Since it is safe to assume that you have access to a browser, a computer, an electrical socket to plug in your computer, and a power plant or other source to generate the power available to your electrical socket, then the only remaining requirement to run MYSQL is a web server, which should already come with MYSQL installed if you anticipated this need.

In this tutorial we will learn how to create, populate, modify, update and delete a mysql database directly from the command line. There is an alternative method, and that is to use a GUI, such as phpMyAdmin. phpMyAdmin allows you to maintain MYSQL databases from your browser, with more clicking and less typing. (You can talk to your web host about your options if they do not support a MYSQL GUI.)

Another more popular method of maintaining a MYSQL database is by using PHP scripts on a website, where information can be gathered and stored in a database, then retreived/modified/updated as needed.

Database Design Concepts

Are you familiar with Excel spreadsheets, or HTML tables, where data can be organized into rows with multiple columns, each column neatly labeled? If not, take a peek at the following examples.

An Excel spreadsheet:


An HTML table:

Question Answer
Why do birds fly South? Because it’s too far to walk.
What did the bee say to the flower? Hello honey!
How do you know that cats are sensitive creatures? They never cry over spilt milk!

MYSQL databases can have multiple tables, each of which function in the same manner as an excel spreadsheet or HTML table. From the command line, a MYSQL database table might look something like this:


The moral of this story is preparation by organization. Before creating a database, write down your plan of action. Decide how many tables that you want to begin with (more can be added at any time) and then determine how many columns each table should have (the limit is 4096 columns per table) by creating a column for each type of data that will be stored. (Additional columns can be added later if you miss something now.)

We will learn how to make the following table to store information about our pets.


MYSQL Syntax

Syntax is a set of rules that define how MYSQL can and cannot be written. MYSQL is, in that sense, no different from any other language. If I wrote this sentence backward and upside down, it would not be following the rules, and it is doubtful that anyone would understand it.

MYSQL expects only one main rule to be followed. Each statement must end with a semicolon “;” in order to indicate that the statement has ended.

MYSQL “code” is easy to read and write, and will make sense as long as you understand the English language.

A useful habit when writing MYSQL is capitalizing the reserve works that make up each command that is written. This helps to separate the reserve words and make the intent of the code more obvious at a glance.

The two lines below perform the same function of displaying all of the data stored in the “example” table, but only one allows you to separate reserve words at a glance.

SELECT * FROM example;

select * from example;

If you think that the second example is easier to read and interpret, then we will agree to disagree, and I will continue to use the syntax of the first example of the sake of this tutorial and my future MYSQL projects. ;-)

Chapter 2 – MYSQL Basics

Connecting & Disconnecting From the MySQL Server

From the command line, connecting to MYSQL may require knowledge of the host, the username and the password that are provided by the hosting company that you are using.

mysql -h host -u username -p

The above line starts out by naming the program we want to run (MYSQL). The “-h” part identifies the host, which will be provided by your hosting company and typed in the place of “host”. The “-u” identifies the username, which is also provided by the hosting company and will replace the word “username”. The “-p” part states that there is a password that needs to be entered, but notice that it is not entered here (for security reasons). Instead, after typing in this line and hitting the <enter> key, you will be prompted with something like this:

Enter password:

…And there you type in your password, where it will either not show up as you type it in, or show up as a series of asterisks * that keep passersby from reading your password. (unless they watched your fingers as you typed it in!)

After typing in your password and hitting <enter> again, you will be presented with a welcome screen similar to this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.1.55-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


And you are ready to type in commands that can create databases, add information, update information, etc.

All commands that you type in will show up after the “mysql>”.

When you are finished, simply type in “quit” and you will be signed out and returned to the original command prompt.

mysql> quit

Let’s have some fun!

Creating & Using A MYSQL Database

You may have an Administrator that will create your database for you, but if not, creating a database is a simple process.

Before creating a database, you will want to check through any existing databases in order to confirm that your database does not already exist.


This command will print out an alphabetical list of the databases that you have access to. It might look something like this:

| Database |
| pets     |
| test     |

And it might not look something like this, because you have not yet created the “pets” database. Let’s do that now.

mysql> CREATE DATABASE pets;

Note: Database names are case-sensitive!

Now that your database is created, you have to select it for use. How, you might ask? I’m glad you asked!

mysql> USE pets;

As soon as you see the message “Database changed”, you know you’re on the right track!

Alternately, you can skip the “USE database;” step by adding the database name when connecting to MYSQL. Example:

mysql> mysql -h host -u username -p pets

After logging in, if at any time you forget what database you are using, simply type in “SELECT database();” and you will be told.

And now you’re all set and ready to go!

Creating MYSQL Database Tables

Creating a table must begin with a plan. What do you want to store in your table? How many columns does your table need? What should each column be labeled? What kind of data do you plan to store in each column?

The syntax to create a table is: CREATE TABLE tablename (columnnames);

For our example, let’s return to our pets table and explain how it was created. The examples below are identical, except the second example contains extra line breaks and whitespace that make it more readable. Either example would create a table when entered “as is” into a mysql command line.

CREATE TABLE pets (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(75), breed VARCHAR(75), gender VARCHAR(7), dateofbirth VARCHAR(25), description TEXT, pedigree TEXT, medicalrecord TEXT, PRIMARY KEY(id));

name VARCHAR(75),
breed VARCHAR(75),
gender VARCHAR(7),
dateofbirth VARCHAR(25),
description TEXT,
pedigree TEXT,
medicalrecord TEXT,

Let’s break down our example. After naming the table, the columns are all listed between parentheses in the order that they will appear in the table (left-to-right). Each column name, comma separated, is assigned a data type that limits (or not) the type and amount of data that can be stored.

First, we assign a column name of “id” and give it a data type of “INT”, which stands for integer, meaning that only integers (numbers between -2147483648 and 2147483647) can be stored in this column. Also, “NOT NULL” prevents this column from allowing null (empty) fields, and “AUTO INCREMENT” automatically assigns a number to each new row. The first row will be 1, and each new row will be incremented by 1 (2, 3, 4, 5, etc.)

The second, third, fourth and fifth columns each have a column name, followed by a data type of varchar(). This data types allows “various characters” (letters, numbers, spaces, punctuation, etc.) but limits the number of characters to the number specified between parenthesis. The pet’s name, for example, can be up to 75 characters long, but if the name exceeds this limit, it will not be stored properly.

The sixth, seventh and eighth columns each have a column name, followed by a data type of “text”. This data type allows any string as long as it does not exceed 65535 characters.

The final detail is not a column. A primary key is a unique index used to identify the rows in a table. Every table should have one primary key, and only one primary key. A primary key column cannot contain NULL (empty) values, and must contain unique values. In our example, this makes the “id” column an ideal candidate for the primary key column. In fact, the “id” column in our example serves little purpose other than being the primary key.

There are severalbasic data type options available that you can learn more about before creating your own table.

So now you know how to create a table. Next we can learn how to look at the table we just created.

Showing & Describing Tables

Now that we have learned how to create a table, let’s look at the list of tables we have created in our database.

| Tables_in_YourDatabase          |
| pets                            |
1 row in set (0.00 sec)

As you can see, “SHOW TABLES;” will print out a list of the tables that have been created in the current database. To view more detailed information about each table, use “DESCRIBE tablename;”.

mysql> DESCRIBE pets;
| Field         | Type        | Null | Key | Default | Extra          |
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| name          | varchar(75) | YES  |     | NULL    |                |
| breed         | varchar(75) | YES  |     | NULL    |                |
| gender        | varchar(7)  | YES  |     | NULL    |                |
| dateofbirth   | varchar(25) | YES  |     | NULL    |                |
| description   | text        | YES  |     | NULL    |                |
| pedigree      | text        | YES  |     | NULL    |                |
| medicalrecord | text        | YES  |     | NULL    |                |
8 rows in set (0.00 sec)

As you can see, all necessary table information is listed for reference when a table is described.

And last but not least, if you ever lose track of what database you are using, using SELECT DATABASE(); will show you the currently selected database.

Deleting Tables and Databases

At some point in time you might need to get rid of a MYSQL database table. A single command will get the job done, so take care to not delete the wrong table. Also, make sure that you really don’t want any of the information stored in the table, because it will all be deleted as well.

The syntax to delete a table is: DROP TABLE tablename;

So, to get rid of the table that we created in our last example, we can use:


…and we no longer have a place to store our pet’s information.

To drop an entire database, including any tables that the database still contains, the syntax is:

DROP DATABASE databasename;


In order to prevent errors occurring when deleting a MYSQL table or database that may no longer exist, “if exists” can be used.


Chapter 3 – Data Manipulation

MYSQL Data Types

Data types are assigned to a MYSQL table when the table is first created.

Let’s take a quick look at some of the basic data type options available:

Data Type Type of Data Stored In Data Type
TINYINT Numeric Type (-128 thru 127)
SMALLINT Numeric Type (-32768 thru 32767)
MEDIUMINT Numeric Type (-8388608 thru 8388607)
INT Numeric Type (-2147483648 thru 2147483647)
BIGINT Numeric Type (9223372036854775808 thru 9223372036854775807)
CHAR() Characters Without Trailing Spaces (0 thru 255)
VARCHAR() Characters With Trailing Spaces (0 thru 65535)
TINYTEXT Text With Maximum Length of 255 Characters
TEXT Text With Maximum Length of 65,535 Characters
MEDIUMTEXT Text With Maximum Length of 16,777,215 Characters
LONGTEXT Text With Maximum Length of 4,294,967,295 Characters
DATETIME Date/Time Type (0000-00-00 00:00:00)
DATE Date/Time Type (0000-00-00)
TIMESTAMP Date/Time Type (0000-00-00 00:00:00)
TIME Date/Time Type (00:00:00)
YEAR Date/Time Type (0000)

It’s all a matter of picking the best option for the type of data that you plan to store, and sticking to the plan!

MYSQL Operators

MYSQL operators are most often used when searching for patterns. Let’s review some of the most common operators:

Operator Description
AND, && Logical AND
= Assign a Value (As Part of a SET Statement, or As Part of the SET Clause In An UPDATE Statement)
/ Division Operator
= Equal Operator
>= Greater Than or Equal Operator
> Greater Than Operator
<= Less Than or Equal Operator
< Less Than Operator
LIKE Simple Pattern Matching
- Minus Operator
% Modulo Operator
!=, <> Not Equal Operator
NOT LIKE Negation of Simple Pattern Matching
||, OR Logical OR
+ Addition Operator
* Multiplication Operator

As we progress, you will see some of these operators used, and can begin to experiment with others.

Inserting MYSQL Data

“Insert” is the term generally used to refer to the act of putting data into a MYSQL database table. You will need to know not only the name of the table that you will be inserting data into, but also the names and types of each column. You don’t want to put letters into a column that only accepts integers, or a 75-character phrase into a column that only accepts 25 characters!

The syntax used to insert data into a MYSQL table is: INSERT INTO tablename () VALUES ();

Inside the first set of parenthesis, immediately following your table name, you will specify the name of each column that you want data to be inserted into. Inside the second set of parenthesis, immediately following the word “VALUES”, you will specify the data that should be inserted into each column that was specified.

This means that if you specify 3 different columns you will need to specify 3 different values, no more, no less.

Each value should be surrounded by single quotes and separated by commas. The columns do not need to be surrounded by single quotes, but they must be separated by commas.

The order of the values inserted must match the order of the columns specified. If you list columns “A” and “B” in that order, the first value will be inserted into “A”, and the second value will be inserted into “B”.

Consider the following examples, which will insert data into the table that we created in a previous examples.

INSERT INTO pets (name, breed, gender, dateofbirth, description, pedigree, medicalrecord) VALUES ('Chester', 'Shiba Inu', 'Male', '09-30-2008', 'Fluffy', 'Unknown', '');

name, breed, gender, dateofbirth, description, pedigree, medicalrecord
'Tamika', 'Ferret', 'Female', '03-12-2010', 'Slim', 'Unknown', ''

'Slippery, Orangish',

Each example, although they are broken down in different manners, indicate valid records that can be inserted into the table.

But wait, did we forget about the table’s first column, the “id” column? I didn’t! Since the id column was created as an “AUTO INCREMENT” column, that means it fills itself in with an id number when a record is inserted into the database. You shouldn’t have to mess around with this type of column.

If a column is not created as an “AUTO INCREMENT” or “NOT NULL” column, and you don’t have any data to store in it yet, it can be left blank by leaving it out of the columns and values that are listed when a record is inserted. For example:

INSERT INTO pets (name, description) VALUES ('Percy', 'Grayish-Brownish');

The above example will insert a perfectly valid record into the pets table with a new id number and several blank columns.

The best advice that I can leave you with is to keep a close eye on your apostrophes and commas. Some of the most common errors are caused due to misplaced or excess apostrophes and/or commas.

Note: If you need to insert an apostrophe into a column as a part of the data, escape it with a backslash (\). Example:

INSERT INTO pets (name, description) VALUES ('Spot', 'Percy\'s Friend');

Selecting MYSQL Data

Now that you have inserted data into your database, you will at some point in time want to access that data. This can be done by “selecting” the data that you want.


This simple example can be read as “select all from pets”, and will print out onto your screen every single record stored in the pets database. The asterisk is a wildcard (meaning “all”) that allows you to be less selective in your selection.

In order to be more selective in your selection, the “where” clause can be used. It allows you to specify requirements that a record (or multiple records) must meet before they can be returned.

SELECT * FROM pets WHERE name LIKE 'Tamika';

Here we have a query that can be read as “select all from pets where name is like Tamika”. Instead of sending you all of the records in the pets table, this statement will only return record(s) in the pets table where “Tamika” is stored in the name column. (Bonus: Did you recognize the operator that we used in this query?)

This leaves us with one simple problem. The WHERE clause will look for an identical match to the word(s)/phrase(s)/character(s) that are specified. What if you only know part of the data that you are searching for? The % wildcard will allow you to search for partial matches.

SELECT * FROM pets WHERE breed LIKE '%Ferret%';

In this example we see our search term surrounded by the % wildcard. This means that all records that contain “Ferret” in the breed column will be returned, even if they contain additional characters before and/or after the word “Ferret”. (“Black-Footed Ferret”, “Ferret” and “Ferret Weasel” will now all be valid matches.)

If the wildcard is only used on one side of the search term, then additional characters are only allowed to that one side.

Results are generally not case-sensitive, so searching for “ferret” or “tamika” will turn up the record just as well as searching for “Ferret” or “Tamika”.

If you only need specific columns returned, you can specify which column (or columns) in place of using the * asterisk wildcard. Multiple column names should be comma separated.

SELECT name, gender, description FROM pets;



Updating MYSQL Data

Often, data that is stored in your MYSQL table will need to be changed or updated/replaced with new data. The UPDATE statement is used for this purpose.

The syntax is: UPDATE tablename SET column = newdata WHERE column LIKE currentdata;

You need to specify which table needs updates, and which column(s) in the table need updated. Unless you want that column updated (with the same data) in every single record in the table, you will also need to identify the row/record that you want to update.

Let’s consider the following example:

mysql> SELECT * FROM pets;
| id | name      | breed     | gender | dateofbirth | description        | pedigree | medicalrecord |
| 1  | Chester   | Shiba Inu | Male   | 09-30-2008  | Fluffy             | Unknown  |               |
| 2  | Tamika    | Ferret    | Female | 03-12-2010  | Slim               | Unknown  |               |
| 3  | Sylvester | Goldfish  | Male   | 01-15-2011  | Slippery, Orangish | Unknown  |               |
3 rows in set (0.00 sec)

mysql> UPDATE pets SET description = 'Muddy' WHERE name LIKE "Chester";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE pets SET description = 'Chubby', pedigree = 'None' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM pets;
| id | name      | breed     | gender | dateofbirth | description        | pedigree | medicalrecord |
| 1  | Chester   | Shiba Inu | Male   | 09-30-2008  | Muddy              | Unknown  |               |
| 2  | Tamika    | Ferret    | Female | 03-12-2010  | Chubby             | None     |               |
| 3  | Sylvester | Goldfish  | Male   | 01-15-2011  | Slippery, Orangish | Unknown  |               |
3 rows in set (0.00 sec)


As you can see, after selecting all of the data in our pets table so that we would view it, two UPDATE statements were successfully performed.

The first UPDATE statement set the description to “muddy” in the pets table where the pet’s name was “Chester”.

The second UPDATE statement set the description to “Chubby” AND set the pedigree to “None” (notice how the two are comma separated) in the pets table where the record id was equal to 2.

So as you can see, updating a record is as simple as knowing what data you want updated, and pinpointing the exact record where it needs updated.

It is possible to update multiple records at one time. If, for example, you have multiple Chesters, it might not have rained on all of them, so always think ahead. The id column was added for the purpose of uniquely identifying each row, so it is usually the safest and most practical option.

Deleting MYSQL Data

When a record or row in a MYSQL table is no longer necessary to keep around, you can get rid of it using the DELETE statement.

The syntax is: DELETE FROM tablename WHERE column = data;


DELETE FROM pets WHERE name LIKE "Tamika";
DELETE FROM pets WHERE id = 3;

In short, the table and the record must both in some way be identified in order to delete the proper data.

As with all delete statements, take care not to delete too much or the wrong data!

Advanced MYSQL Queries

MYSQL queries can benefit from the use of several operators and clauses that we have not yet covered. Although for the sake of our examples we will be mostly using the SELECT statement, most of these options will work on other statements, such as UPDATE and DELETE as needed.


To begin, let’s jump right into some examples using =operators.

SELECT * FROM pets WHERE name LIKE '%T%' AND gender LIKE 'Female';

SELECT * FROM pets WHERE (breed LIKE 'cat' AND gender LIKE 'male') OR (breed LIKE 'dog' AND gender LIKE 'male');

See how operators can be used to narrow down the results even more by combining multiple conditions? And how parenthesis can be used to group conditions together in order to indicate which condition takes priority (because the AND operator is prioritized over the OR operator)?

Notice that unless you have inserted additional rows into our example pets table, the second example will not return any results.

Order By

Generally results are returned in the order that they are encountered in (or inserted into) a MYSQL table. You can, however, specify the order that you want your results returned, using ORDER BY. This will cause your results to be returned in alphabetical or numerical order, depending on the contents of the column you are ordering by.

By default, when ORDER BY is used, the results are returned in ascending order (ASC), with the smallest values first, followed by the larger values. If you want the results to be in descending order (larger values first, followed by smaller) you can add DESC to the query.

SELECT * FROM pets ORDER BY dateofbirth;


You can limit the number of rows affected by a statement using the LIMIT clause. When given a single numerical argument it will limit the number of rows selected, deleted, updated etc. according to the number specified.

LIMIT can accept two numerical arguments instead of one, and will use the first argument as the offset of the first row to return, and the second argument as the maximum number of rows to return. (Note: LIMIT uses 0 as the offset of the initial row instead of 1.)

SELECT * FROM pets LIMIT 2, 5;

The first example will return the first 3 rows from the pets table. The second example will return 4 rows (rows 3-6, which are the 2nd, 3rd, 4th and 5th rows since the offset is 0).


At some point in time you might have enough data built up that values begin to repeat themselves. We might end up with two dogs named Chester, for example. But what if you want to make a list of all of your pet’s names, in alphabetical order, without multiple values? The DISTINCT clause can make that happen.



There are many instances that you will run into where you will want to count rows. The COUNT() function is used for this purpose. Let’s begin by counting all of the rows in our table:


We can be more selective in the rows that we count as well.

SELECT COUNT(*) FROM pets WHERE name LIKE 'Sylvester';

We can use GROUP BY along with COUNT() if we want to get fancy and, for example, determine how many male pets we have versus how many female.

mysql> SELECT gender, COUNT(*) FROM pets GROUP BY gender;
| gender | COUNT(*) |
| Female |        2 |
| Male   |        4 |
2 rows in set (0.00 sec)

You probably want to test some ideas of your own at this point, so give it a try!

Chapter 4 – Advanced MYSQL

Altering MYSQL Tables

The ability to change the structure of a database table after it has been created, and even after it contains data, will come in handy sooner or later. The ALTER TABLE statement gives you the ability to add, delete and rename columns, as well as create and destroy indexes and change the type of an existing column.

ALTER TABLE – Add Column

The syntax to add a new column to an existing table is: ALTER TABLE tablename ADD COLUMN colname coltype;

By default, additional columns will be added to the end of the specified table. You can use “FIRST” to add a new column to the beginning of the table instead, or “AFTER colname” to specify which existing column the new column should be inserted next to (directly after).


If you are concerned that a column name might already exist, you can used “ADD UNIQUE” in place of “ADD COLUMN” so that the column will only be added if the column name has not already been used.

ALTER TABLE – Delete Column

The syntax to delete an existing column is: ALTER TABLE tablename DROP COLUMN colname;

Any data contained in a dropped column will be deleted as well.


ALTER TABLE – Rename Table

To rename the entire table, the syntax is: ALTER TABLE tablename RENAME TO tablename;


ALTER TABLE – Change Table

To change the name of a column, the syntax is: ALTER TABLE tablename CHANGE oldcolname newcolname coltype;

ALTER TABLE pets CHANGE pedigree bloodline TEXT;

ALTER TABLE – Modify Table

To change/modify the type of a column, the syntax is: ALTER TABLE tablename MODIFY colname coltype;

As an alternative, CHANGE can be used to modify the column type. Both old and new column names must be specified when using CHANGE, but they can be the same so that the column name does not actually change.

ALTER TABLE pets CHANGE breed breed VARCHAR(50);

Backing Up & Restoring A MYSQL Database

Have you ever forgotten to back up your data… and regretted it? Losing data, however it may occur, can pretty much ruin your day. (Don’t ask me how I know!) It is always important to back up your data, and databases are no exception. Lucky for us, MYSQL has already anticipated the problem and provided us with the means of easily backing up our database

Using “mysqldump”, you dump all of the MYSQL statements necessary to re-create the database into a single file.

Note: The mysqldump command is run directly from the command line, and cannot be run when you are already connected to MYSQL.

The syntax is:

mysqldump -u username -p password database > filename.sql

A practical example is:

mysqldump -u Ernest -p TopSecret pets > petsbackup.sql

This command will create a file containing MYSQL statements that, when run, will re-create your entire database.

There are several useful options that can be added into a mysqldump, but we will only look at two. The first, –add-drop-table, will add a DROP TABLE statement before each CREATE TABLE statement in the dumped file, so that during the restore process it is assured that no old data hangs around. The second option, –no-data, will dump only the database structure to the backup file, and will not dump/backup the data stored in the database.

mysqldump --add-drop-table -u username -p password database > filename.sql
mysqldump --no-data -u username -p password database > filename.sql

The restore process, should it ever become necessary, uses “mysqlimport”, with the following syntax:

mysqlimport -u username -p password database filename.sql

A practical example is:

mysqlimport -u Ernest -p TopSecret pets petsbackup.sql