MySQL is one of the most powerful and widely used databases available. Here is a really quick guide to creating a database, creating a table, inserting, selecting and deleting the data, then table, then database. This will not go into too much depth as there are plenty of resources out there already that can provide more information on each step.

I’ll be using the MySQL command line tool on a Linux (Debian!) platform. Assuming I already have mysql running and a passworded root user account:

ns3:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 168753
Server version: 5.0.51a-24-log (Debian)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>


Now we’re ready to go. First we need to create a database:

mysql> create database `test_database`;
Query OK, 1 row affected (0.06 sec)

Now we list all databases:

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| test_database       |
+---------------------+
3 rows in set (0.00 sec)

mysql>

Now we need to specify which database we’re working with:

mysql> use test_database;
Database changed
mysql>

I’ll add a user called ‘test_users’ with a password ‘test_password’ and allow him full access to test_database:

mysql> GRANT ALL ON test_database.* TO test_users@'%' IDENTIFIED BY "test_password";
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> q
Bye
ns3:~#

Now I’ll relog as ‘test_users’

ns3:~# mysql -u test_users -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 168783
Server version: 5.0.51a-24-log (Debian)

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

mysql> use test_database;
Database changed
mysql>

Here I create a new table called ‘test_table’. I’ve created three fields, test_id, test_firstname and test_lastname. There are many different field types and table settings that you can apply, these are beyond the scope of this article.

mysql> CREATE TABLE test_table (
-> test_id int unsigned not null auto_increment primary key,
-> test_firstname varchar (30),
-> test_lastname varchar (30)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| test_table              |
+-------------------------+
1 row in set (0.00 sec)

mysql> describe test_table;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| test_id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test_firstname | varchar(30)      | YES  |     | NULL    |                |
| test_lastname  | varchar(30)      | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql>

Inserting records is this easy..

mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES ('Adam', 'Palmer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES ('Fred', 'Flintstone');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES ('Foo', 'Bar');
Query OK, 1 row affected (0.00 sec)

mysql>

Pulling out data is even easier:

mysql> SELECT test_firstname FROM test_table;
+----------------+
| test_firstname |
+----------------+
| Adam           |
| Fred           |
| Foo            |
+----------------+
3 rows in set (0.00 sec)

mysql> SELECT test_lastname FROM test_table
-> ;
+---------------+
| test_lastname |
+---------------+
| Palmer        |
| Flintstone    |
| Bar           |
+---------------+
3 rows in set (0.00 sec)

mysql> SELECT test_firstname, test_lastname FROM test_table ORDER BY test_lastname ASC;
+----------------+---------------+
| test_firstname | test_lastname |
+----------------+---------------+
| Foo            | Bar           |
| Fred           | Flintstone    |
| Adam           | Palmer        |
+----------------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT test_firstname, test_lastname FROM test_table ORDER BY test_lastname ASC LIMIT 2;
+----------------+---------------+
| test_firstname | test_lastname |
+----------------+---------------+
| Foo            | Bar           |
| Fred           | Flintstone    |
+----------------+---------------+
2 rows in set (0.00 sec)

Now to delete the data. Note that DELETE FROM will delete ALL data in the table unless you constrain it with WHERE conditions. We use the ‘%’ character as a wildcard. %blah% will match ‘helloblah’ and ‘blahhello’ and ‘helloblahhello’

mysql> DELETE FROM test_table WHERE test_firstname='Foo';
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM test_table WHERE test_lastname LIKE '%e%';
Query OK, 2 rows affected (0.00 sec)</div>
Delete the table:
<div class="code">mysql&gt; DROP TABLE test_table;
Query OK, 0 rows affected (0.01 sec)

mysql&gt;

Delete the database:

mysql&gt; DROP DATABASE test_database;
Query OK, 0 rows affected (0.14 sec)

mysql&gt; q
Bye

We’re done. I hope that this has helped. The purpose of the article is to provide a really quick guide to getting started. This blog as a whole is geared towards the user that can take the information above and work the rest out, by trial and error and by google. For that reason I haven’t elaborated on each step or gone into much explanation.