Simple MySQL Example

This is a short example script to follow, demonstrating the use of the MySQL command line client, which is installed as <mysql-install-dir>/bin/mysql.

The steps taken are as follows:

• create a table called "test", with an ID and a Name field
• insert some test data
• select some rows
• drop (delete) the table

After completing this tutorial, you will have a basic understanding of the SQL language, and how to use it in MySQL. For further details on each command, and the options available, such as all the field types, see the official MySQL documentation.

Getting started

• To use this tutorial, you will first need to establish a connection to your database. See How to set up a remote MySQL connection.
• The commands you should enter below, are after the mysql> prompt. All other text is output from MySQL.
• All commands must end with a semicolon. If you omit the semicolon, mysql will assume you are entering a command that spans multiple lines, and will wait for the semicolon to terminate the statement.

First, we will create a table.

mysql> create table test (id int, name varchar(20));
Query OK, 0 rows affected (0.41 sec)

Next, let's insert some test data.

mysql> insert into test (id, name) values (1, 'Neale');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test (id, name) values (2, 'Cassie');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test (id, name) values (3, 'Stuart');
Query OK, 1 row affected (0.05 sec)

Our test table now has 3 rows. Let's look at them.

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Neale  |
|    2 | Cassie |
|    3 | Stuart |
+------+--------+
3 rows in set (0.06 sec)

Now we will execute some conditional SQL, to get certain rows only. First, only records where the ID = 2. This is a typical SQL statement used in almost all software to select a specific record, such as a product ID. You can also try "id < 2", or "id >= 2";

mysql> select * from test where id = 2;
+------+--------+
| id   | name   |
+------+--------+
|    2 | Cassie |
+------+--------+
1 row in set (0.05 sec)

Now let's try a search. We will look for any record where the name starts with "Cas". You can also try '%e%' to find any record that contains an "e".

mysql> select * from test where name like 'Cas%';
+------+--------+
| id   | name   |
+------+--------+
|    2 | Cassie |
+------+--------+
1 row in set (0.05 sec)

This is only a test table, so let's drop (delete) it.

mysql> drop table test;
Query OK, 0 rows affected (0.14 sec)

To exit the MySQL command line client, type "exit".

mysql> exit
Bye

You can repeat this tutorial using more fields in your table, or by creating a useful table such as an address book or a list of your products. See the MySQL documentation for a list of other data types (field types) you can use.