SQL for Beginners

Most modern web applications today interact with databases, usually with a language called SQL. Lucky for us, this language is quite easy to learn. In this article, we are going to start with some basic SQL queries and use them to interact with a MySQL database.

What You Need

SQL (Structured Query Language) is a language designed for interacting with relational database management systems (RDBMS), like MySQL, Oracle, or SQLite. To perform the SQL queries in this article, I suggest that you have MySQL installed. I also recommend phpMyAdmin as a visual interface to MySQL.

The following applications make it very easy to install both MySQL and phpMyAdmin on your local machine:

• WAMP for Windows
• MAMP for Mac

We are going to use the command line for queries. WAMP already comes with a MySQL console.

Creating a Database

Here comes our very first query. We are going to create a database to work with.

Firstly, open up your MySQL console and login. For WAMP, the default password is blank. For MAMP, the password should be root by default.

After logging in, type this query and hit enter:

CREATE DATABASE my_first_db;

Note that semicolon (;) is added at the end of the query, just like at the end of lines of code in JavaScript.

Also, the special words CREATE DATABASE are case insensitive, along with all special words in SQL. But for the sake of readability and convention, we will be writing them in uppercase.

List All Databases

The following query is used to get a list of all databases you have.

SHOW DATABASES;

Delete a Database

You can delete an existing database with the following query.

DROP DATABASE my_first_db;

It's always a good practice to take a backup of your database before you delete it. Later on, we'll see how you can take a complete backup of your database.

Selecting a Database

You can use the USE keyword to select a database you want to operate with. It is a statement and does not require a semicolon at the end.

USE my_first_db

It tells MySQL to select a default database to work with, for the current session. Now we are ready to create tables and do other things under this database.

What is a Database Table?

You can think of a database table like a spreadsheet or CSV file which holds structured data as shown in the following screenshot.

Just like in this example, tables have column names, and rows of data. With SQL queries we can create these tables. We can also add, read, update and delete the data.

Creating a Table

With this query we can create tables in the database. Unfortunately the MySQL documentation is not very friendly for new learners. The structure of this type of query can get very complex, but we will start with an easy one.

The following query will create a table with two columns.

CREATE TABLE users (
    username VARCHAR(20),
    create_date DATE
);

Note that we are able to write a query in multiple lines, and even use tabs for indentation.

The first line is easy. We just create a table named users. Following that, in parentheses, we have a list of table columns separated by commas. After each column name, we have a data type, such as VARCHAR or DATE.

The VARCHAR(20) data type means that the column is a string type, and can be a maximum of twenty characters long. The DATE is also a data type which is specifically used for storing dates, in this format: YYYY-MM-DD.

Primary Keys

Ideally, we should also include a column of user_id, which will be a primary key. Without getting too much into the details, you can think of a primary key as a way to identify each row in a table. It's a unique number to identify any row and can't be duplicated for multiple rows.

Now the query should look like:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20),
    create_date DATE
);

The INT data type makes this a 32 bit integer type (ie. numeric). The AUTO_INCREMENT keyword automatically generates a new id number every time we add new rows of data. It is not required, but makes it much more convenient.

This column does not have to be an integer, but it is the most commonly used type. Having a PRIMARY KEY column also is not required, but it is strongly recommended for good database design and performance.

Let's run the query:

List All Tables

The following query allows you to get a list of tables that are currently in the database.

SHOW TABLES;

Show Table Structure

To see the structure of an existing table, you can use the following query.

EXPLAIN users;

Fields (aka. columns) are listed in the results, with their properties.

Delete a Table

Just like the DROP DATABASES command, the following query deletes a table and its contents, without a warning.

DROP TABLE users;

Again, be very carful with any DROP operation!

Modify a Table

You can modify a table with the ALTER query. This query also can have quite a complex structure because of the multitude of changes it can perform on a table. Let's look at some simple examples.

Make sure to re-create the table we just dropped or the following queries obviously won't work!

Add a Column

ALTER TABLE users
    ADD email VARCHAR(100)
    AFTER username;

Thanks to the readability of SQL, I don't think this even needs an explanation. You can just the ADD keyword to add a new column in the ALTER query.

Remove a Column

In the same way, You can just the DROP keyword to remove an existing column in the ALTER query.

ALTER TABLE users
DROP email;

It is also very simple. But use it with caution as it permanently removes data without a warning.

Re-add the email column because we are going to use it later:

ALTER TABLE users
    ADD email VARCHAR(100)
    AFTER username;

Modify a Column

Sometimes you may want to change the properties of a column, so you don't have to delete and recreate it.

Let's have a look at the following query.

ALTER TABLE users
    CHANGE username
    user_name VARCHAR(30);

It would rename the username column to user_name and change the type from VARCHAR(20) to VARCHAR(30). A change like this should not disturb any of the existing data in the table.

Add Data to a Table

Let's see how to add some data into the table using the following query.

INSERT INTO users VALUES(
    NULL,
    'johndoe',
    'john@doe.com',
    '2009-12-14'
);

As you can see, VALUES() contains the list of field values, separated by commas. The string values are enclosed in single quotes. And the values need to be in the order of the columns that were defined when we created the table.

Note that the first value is NULL for the primary key field we created, which is user_id. We do this so that an id is automatically generated, because the column is set to AUTO_INCREMENT. When entering a row of data for the first time, the id will be 1. Next inserted row will be 2 and so on and so forth.

In this way, you can add more data in the users table.

Alternate Syntax—SET

Here is another syntax for inserting rows.

INSERT INTO users SET
    user_name = 'tutsplus',
    email = 'tutsplus@gmail.com',
    create_date = '2009-12-15';

In the above query, we are using the keyword SET instead of VALUES, and it is not followed by parentheses. This is safer than VALUES, because there is less danger of forgetting or mixing up the order of columns. There are a few things to note here:

• A column can be omitted. For example we did not assign a value to user_id, which will default to the AUTO_INCREMENT functionality. If you omit a VARCHAR column, it would default to an empty string (unless a different default value was specified during table creation).
• Each column has to be referenced by its name. Because of this, they can be in any order, unlike the previous syntax.

Alternate Syntax—Named Values

Here is yet another syntax. In this case we tell the INSERT statement what columns we are supplying values for, and then use the VALUES keyword to pass those values.

INSERT INTO users (email, user_name, create_date)
VALUES ('foo@bar.com','foobar','2009-12-16');

Again, since each column is referenced by name, they can be in any order.

The LAST_INSERT_ID() Function

You can use the following query to get the AUTO_INCREMENT id for the last inserted row, in the current session.

SELECT LAST_INSERT_ID();

The NOW() Function

I think it is a good time to demonstrate how you can use a MySQL function inside your queries.

The NOW() function returns the current date. So you can use it to automatically set a DATE column to the current day while inserting a new row.

INSERT INTO users SET
    user_name = 'tutsplus',
    email = 'tutsplus@gmail.com',
    create_date = NOW();

Note that we received a warning from MySQL, but it is not a big deal. The reason is that NOW() actually returns time information as well.

But the create_date column we created only contains the DATE, and not the time, therefore the returned data was truncated. We could use the CURDATE() function instead, which returns just the date, but the data stored at the end would be the same either way.

Read Data From a Table

Obviously the data we added would be useless unless we can read it. This is where the SELECT query comes in.

Here is the simplest possible SELECT query for reading from a table:

SELECT * FROM USERS;

In this case, the asterisk (*) means that we asked to fetch all the columns from the table. If you want only specific columns, the query would look like this:

SELECT user_name, email FROM users;

The WHERE Clause

More often than not, we are only interested in some of the rows, and not all. For example, let's say we want the email address for the tutsplus user.

To retrieve it, you can use the following query.

SELECT email FROM users WHERE user_name = 'tutsplus';

Think of it like an IF statement. The WHERE clause allows you to put conditions in the query for the results you are looking for.

Note that for the equality condition, only a single equal sign is used (=), instead of double equals (==) which you might be used to from programming.

You can use other comparison conditions too:

SELECT * FROM users WHERE user_id <= 2;
SELECT * FROM users WHERE create_date != '2009-12-14;

The AND and OR operators can be used to combine conditions:

SELECT * FROM users WHERE user_name = 'tutsplus' OR user_id = 1;

Note that numeric values do not have to be inside quotes.

The IN Operator

The IN operator is useful for matching multiple values.

SELECT * FROM users WHERE
create_date IN ('2009-12-15', '2009-12-16');

The LIKE Operator

The LIKE operator allows you to do wildcard searches.

SELECT * FROM users WHERE
email LIKE '%tuts%';

The percentage sign (%) is used as the wildcard.

The ORDER BY Clause

If you want the results to be returned in a specific order, you can use the ORDER BY clause.

SELECT * FROM users ORDER BY create_date;

SELECT * FROM users ORDER BY user_name DESC;

The default order is ASC (ie. ascending). You can add DESC to order it by descending order.

The LIMIT ... OFFSET Clause

With the help of the LIMIT and OFFSET clause, you can limit the number of returned results.

SELECT * FROM users LIMIT 2;

SELECT * FROM users LIMIT 1 OFFSET 2;

SELECT * FROM users LIMIT 2,1;

The LIMIT 2 clause just gets the first 2 results. The LIMIT 1 OFFSET 2 gets 1 result, after the first 2 results. The LIMIT 2, 1 means the same thing, but note that the first number is the offset and the second number is the limit.

The GROUP BY Clause

The GROUP BY clause is really useful when you want to aggregate the data based on any particular column. Let's assume that in our users table, it has country_id column, which stores the country of the user. Now, if you want to get the count of all users based on country, you can use the GROUP BY clause to achieve it.

The query would look something like this.

SELECT country_id, count(*) as total_users 
FROM users
GROUP BY country_id;

The JOIN Clause

The JOIN clause helps you to select common data from two different tables. Ideally, both tables should contain a column which is common among those two tables.

In our example, we've users table which contains the user_id column. Now, let's assume that we've another table called user_details, which contains user_id, user_company_name and user_ssn_no columns. The user_details table is used to store extra information about users. In this case, we've the user_id column in both tables and it's common, so you can use the JOIN clause to retrieve all the user information by using a single query.

Let's have a look the following query.

SELECT * 
FROM users 
INNER JOIN user_details 
ON users.user_id = user_details.user_id;

It would give you all the details of all users in a single query. There are different types of joins like LEFT JOIN, RIGHT JOIN and INNER JOIN. The INNER JOIN is one of the most commonly used join amongst all.

The HAVING Clause

The HAVING clause is generally used along with the the GROUP BY clause. You can think of it as a WHERE clause for the aggregated columns like COUNT, SUM etc. So basically, when you want to filter results based on the aggregate columns, you need to use the HAVING clause.

In the earlier section, we discussed how you can find total users group by the country_id column. We can extend the same example and filter the countries that have more than two users.

Let's have a look at the following example.

SELECT country_id, count(*) as total_users  
FROM users 
GROUP BY country_id
HAVING total_users >= 2;

Update Data in a Table

The following query is used to update the data in a table.

UPDATE users SET
email = 'changed@gmail.com',
user_name = 'barfoo'
WHERE user_name = 'foobar';

Most of the time, it is used with a WHERE clause, because you would want only specific rows to be updated. If a WHERE clause is not provided, all rows would be updated with the same changes! So, you have to be careful when you use UPDATE query.

You can also use a LIMIT clause to limit the number of rows to be updated.

UPDATE users SET
create_date = '2009-12-01'
WHERE create_date = '2009-12-14' LIMIT 1;

Delete Data From a Table

Just like UPDATE, the following query is also usually used with a WHERE clause.

DELETE FROM users WHERE user_name = 'batman';

Truncate a Table

To delete the entire contents of a table, you can just do this:

DELETE FROM users;

But it is usually more performance efficient to use TRUNCATE instead.

The TRUNCATE clause also resets the AUTO_INCREMENT numbers so a new row will again start from the beginning. But this does not happen with a DELETE query, and the counter keeps going up.

Escaping String Values and Special Words

String Values

Certain characters need to be escaped, otherwise you can have problems.

Let's have a look at the following query.

INSERT INTO users SET user_name = 'O\'Reilly';

The backslash (\) is used for escaping. That ensures that the character (for example the ' character in this example) is treated as part of the string, ignoring it's special meaning in SQL. 

This is also very important for security reasons. Any user input going into the database needs to be properly escaped. In PHP, you use the mysql_real_escape_string() function or use prepared statements since they do escaping automatically.

Special Words

Since MySQL has many special words like SELECT or UPDATE, you can prevent collision by putting quotes around your table and column names. But these are not the regular quotes; you need to use the back-tick (`) character.

Let's say you want to add a column named delete for some reason:

As you can see, when we tried to run the query without back-tick character, MySQL gave us the warning. And when we used the back-tick character, it allowed us to run the query successfully.

How to Backup a Database

In this section, we'll quickly see how can take a backup of your MySQL database with the help of the command line console.

Let's have a look at the following command.

$mysqldump -h {HOSTNAME} -u {USERNAME} -p {DATABASENAME} > backup.sql

You need to replace the placeholders as required. The {HOSTNAME} should be replaced with your server name, it would be localhost if you are working locally. The {USERNAME} should be replaced with your MySQL username. The {DATABASENAME} should be replaced with your MySQL database name you want to take backup of. When you press enter to run the above command, it would ask for the password, since we've supplied the -p parameter. You just need to enter your MySQL password and it would make a backup of whole database into the backup.sql file.

How to Restore Backup

A backup isn't much use without a way to restore it!

Have a look at the following command.

$mysql -h {HOSTNAME} -u {USERNAME} -p {DATABASENAME} < backup.sql

This will restore the database.

Conclusion

Hopefully in this article I was able to convince you that SQL is very powerful, yet is easy to learn.