Clivern

A Software Engineer and Occasional Writer.

Kick-Starting MySQL

12 April 2014

Today MySQL is one of the most popular open source databases as it supports millions of web applications. We will discuss the basics of MySQL like creating databases and tables and interacting with data.

Creating and Using Database

The second step after designing a database is to create it. You can create database with CREATE DATABASE statement. Suppose you want to create app database.

mysql> CREATE DATABASE app;

Query OK, 1 row affected (0.06 sec)

To avoid conflicts with other databases, You should add IF NOT EXISTS to statement like that.

mysql> CREATE DATABASE IF NOT EXISTS app;

Query OK, 1 row affected (0.01 sec)

Once you have created the database. You should choose it as the database to work with. Just use the following statement.

mysql> USE app;

Database changed

Creating Tables

Now we are ready to create our first table. I hope you already familiar with column types to be able to create table structure. By the way, Here’s the statement to create users table.

mysql> CREATE TABLE `users` (
    ->    `id` int(11) not null auto_increment,
    ->    `name` varchar(60) not null,
    ->    `email` varchar(60) not null,
    ->    `created_at` timestamp not null default CURRENT_TIMESTAMP,
    ->    PRIMARY KEY (`id`),
    ->    UNIQUE KEY (`email`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Query OK, 0 rows affected (0.52 sec)

The INSERT Statement

The INSERT statement used to insert new data to tables. Here’s how to insert new user.

mysql> INSERT INTO users VALUES (1,'hele','[email protected]','2014-04-12 20:46:53');

Query OK, 1 row affected (0.07 sec)

To insert many users with one statement, You can do the following.

mysql> INSERT INTO users VALUES (2,'mark','[email protected]','2014-04-12 20:46:53'),
    -> (3,'marl','[email protected]','2014-04-12 20:46:53'),
    -> (4,'keli','[email protected]','2014-04-12 20:46:53'),
    -> (5,'john','[email protected]','2014-04-12 20:46:53');

Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

Suppose you need to insert data but without using order. You can insert using an alternative syntax.

mysql> INSERT INTO users (name,email) VALUES ('slee','[email protected]');

Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO users (name,email) VALUES ('lee','[email protected]'),
    -> ('saw','[email protected]'),
    -> ('kell','[email protected]');

Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

As you can see, Columns names parentheses included before values parentheses. There’s another syntax in which you list column name and value together. Here’s an example.

mysql> INSERT INTO users SET name='sandr',email='[email protected]';

Query OK, 1 row affected (0.05 sec)

The SELECT Statement

The SELECT statement used to read data from a table. Let’s retrieve all data from users table.

mysql> SELECT * FROM users;

+----+-------+-------------------+---------------------+
| id | name  | email             | created_at          |
+----+-------+-------------------+---------------------+
|  1 | mark  | m@cl.com          | 2014-04-12 20:46:53 |
|  2 | marl  | n@cl.com          | 2014-04-12 20:46:53 |
|  3 | keli  | k@cl.com          | 2014-04-12 20:46:53 |
|  4 | john  | mo@cl.com         | 2014-04-12 20:46:53 |
|  5 | hele  | hele@cl.com       | 2014-04-12 20:46:53 |
|  6 | slee  | se@clivern.com    | 2014-04-12 21:00:39 |
|  7 | lee   | lee@clivern.com   | 2014-04-12 21:02:18 |
|  8 | saw   | saw@clivern.com   | 2014-04-12 21:02:18 |
|  9 | kell  | kell@clivern.com  | 2014-04-12 21:02:18 |
| 10 | sandr | sandr@clivern.com | 2014-04-12 21:04:15 |
+----+-------+-------------------+---------------------+
10 rows in set (0.00 sec)

As you can see, I used * wildcard character to retrieve all coulmns in the table. If you want to retrieve custom columns, You can do the following.

mysql> SELECT name, email FROM users;

+-------+-------------------+
| name  | email             |
+-------+-------------------+
| mark  | m@cl.com          |
| marl  | n@cl.com          |
| keli  | k@cl.com          |
| john  | mo@cl.com         |
| hele  | hele@cl.com       |
| slee  | se@clivern.com    |
| lee   | lee@clivern.com   |
| saw   | saw@clivern.com   |
| kell  | kell@clivern.com  |
| sandr | sandr@clivern.com |
+-------+-------------------+
10 rows in set (0.00 sec)

The WHERE clause allows us to choose which rows to return from SELECT statement which means that returned rows passed the condition created by WHERE clause. Here’s an example.

mysql> SELECT name, email FROM users WHERE id=5;

+------+-------------+
| name | email       |
+------+-------------+
| hele | hele@cl.com |
+------+-------------+
1 row in set (0.00 sec)

In the previous example, We used equals (=) operator but we can also use other operators (>, <, >=, <= and !=). Also we can combine many conditions using AND, OR, NOT and XOR operators. Explore the following examples.

mysql> SELECT name, email FROM users WHERE id>7;

+-------+-------------------+
| name  | email             |
+-------+-------------------+
| saw   | saw@clivern.com   |
| kell  | kell@clivern.com  |
| sandr | sandr@clivern.com |
+-------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT name, email FROM users WHERE id>7 AND name='saw';

+------+-----------------+
| name | email           |
+------+-----------------+
| saw  | saw@clivern.com |
+------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT name, email FROM users WHERE id>10 OR name='saw';

+------+-----------------+
| name | email           |
+------+-----------------+
| saw  | saw@clivern.com |
+------+-----------------+
1 row in set (0.00 sec)

The LIKE clause is used to match strings. For example, If we used LIKE 'cli%', It will match the string cli followed by zero or more characters. Also you can match strings that ends with ern with LIKE '%ern'. Finally, the LIKE '%ver%' will match strings that have ver substring in them. Let’s explore these examples.

mysql> SELECT name, email FROM users WHERE id>5 AND name LIKE 'sa%';

+-------+-------------------+
| name  | email             |
+-------+-------------------+
| saw   | saw@clivern.com   |
| sandr | sandr@clivern.com |
+-------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT name, email FROM users WHERE id>5 AND name LIKE '%aw';

+------+-----------------+
| name | email           |
+------+-----------------+
| saw  | saw@clivern.com |
+------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT name, email FROM users WHERE id>5 AND name LIKE '%a%';

+-------+-------------------+
| name  | email             |
+-------+-------------------+
| saw   | saw@clivern.com   |
| sandr | sandr@clivern.com |
+-------+-------------------+
2 rows in set (0.00 sec)

If you like to match strings that start with s letter and has lenght of three or more. You should use underscore character like that.

mysql> SELECT name, email FROM users WHERE id>5 AND name LIKE 's__%';

+-------+-------------------+
| name  | email             |
+-------+-------------------+
| slee  | se@clivern.com    |
| saw   | saw@clivern.com   |
| sandr | sandr@clivern.com |
+-------+-------------------+
3 rows in set (0.00 sec)

The ORDER BY clause is used to sort returned rows. It is followed by the column that you want to use as sort key and then the order type (ASC or DESC). Consider the following examples.

mysql> SELECT name, email FROM users WHERE id<6 ORDER BY name ASC;

+------+-------------+
| name | email       |
+------+-------------+
| hele | hele@cl.com |
| john | mo@cl.com   |
| keli | k@cl.com    |
| mark | m@cl.com    |
| marl | n@cl.com    |
+------+-------------+
5 rows in set (0.00 sec)

mysql> SELECT name, email FROM users WHERE id<6 ORDER BY created_at,name DESC;

+------+-------------+
| name | email       |
+------+-------------+
| marl | n@cl.com    |
| mark | m@cl.com    |
| keli | k@cl.com    |
| john | mo@cl.com   |
| hele | hele@cl.com |
+------+-------------+
5 rows in set (0.00 sec)

The LIMIT clause used to limit the number of rows returned from SELECT statement. Here’s an example.

mysql> SELECT name, email FROM users WHERE id<6 ORDER BY name ASC LIMIT 2;

+------+-------------+
| name | email       |
+------+-------------+
| hele | hele@cl.com |
| john | mo@cl.com   |
+------+-------------+
2 rows in set (0.00 sec)

It’s also can be used to return specific number of rows with offset. Here’s an example.

mysql> SELECT name, email FROM users WHERE id<6 ORDER BY name ASC LIMIT 2,1;

+------+----------+
| name | email    |
+------+----------+
| keli | k@cl.com |
+------+----------+
1 row in set (0.00 sec)

mysql> SELECT name, email FROM users WHERE id<6 ORDER BY name ASC LIMIT 2,2;

+------+----------+
| name | email    |
+------+----------+
| keli | k@cl.com |
| mark | m@cl.com |
+------+----------+
2 rows in set (0.00 sec)

The UPDATE Statement

The UPDATE statement is used to change existing data. Here’s an example.

mysql> UPDATE users SET name='new name' WHERE id=5;

Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT name FROM users WHERE id=5;

+----------+
| name     |
+----------+
| new name |
+----------+
1 row in set (0.00 sec)

The DELETE Statement

The DELETE statement is used to remove a whole rows. Explore the following example.

mysql> DELETE FROM users WHERE id=5;

Query OK, 1 row affected (0.07 sec)

mysql> DELETE FROM users WHERE id<4;

Query OK, 3 rows affected (0.07 sec)

Dropping Tables

To drop table, You can use DROP TABLE statement like that.

mysql> DROP TABLE users;

Query OK, 0 rows affected (0.16 sec)

Dropping Database

Similar to previous statement with a little change. You can use DROP DATABAE statement to drop database like that.

mysql> DROP DATABASE app;

Query OK, 0 rows affected (0.06 sec)