mySQL walkthrough

SSH to your account, and from the shell, type:

mysql -p username

That will get you in the SQL prompt:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35795 to server version: 3.21.33b
Type 'help' for help.

Then you can issue standard SQL commands. SQL commands are traditionally shown in all uppercase, but in reality they can be lowercase just as well. Table and column names, however, are case-sensitive.

For example:

mysql> show tables; 

shows a list of all the tables in the database, or none if none. Note that most SQL commands need a ; semicolon at the end.

mysql> describe some_table; 

describes a table. You may not have any tables in the database at this point. Let's create one:

mysql> create table my_table ( id INT, name CHAR(64), tel CHAR(16) );

This means we create a table to store 3 columns: an id for each person, a name for each of them, and a phone number.

Now we have a table structure, however the table is still empty. Let's stuff some data in:

mysql> insert into my_table (id, name, tel) values ( 1, "Mr. Tux", "408-282-1540");

Query OK, 1 row affected (0.01 sec) 

This means that the command you entered went all right. If it wasn't all right, you may read something like:

ERROR 2000: parse error near  at line 1

If you had a typo, you can recall the last type-in with the upward arrow key, just like in the shell. Let's enter a couple more:

mysql> insert into my_table (id, name, tel) values ( 2, "Ms. Tuxinette", "408-282-1540");
mysql> insert into my_table (id, name, tel) values ( 3, "Squishy Junior", "123-456-7890");

To see what is in the table:

mysql> select id,name,tel from my_table; 

+------+----------------+--------------+
| id   | name           | tel          |
+------+----------------+--------------+
|    1 | Mr. Tux        | 408-282-1540 |
|    2 | Ms. Tuxinette  | 408-282-1540 |
|    3 | Squishy Junior | 123-456-7890 |
+------+----------------+--------------+
3 rows in set (0.01 sec)

We could also have said:

mysql>select * from my_table;

gives the same answer, * stands for all the fields (= columns). This is the SELECT sql statement, the most used sql command. It retrieves data according to what you ask for.

As another example, if we want the telephone and name only:

mysql> select tel,name from my_table;

+--------------+----------------+
| tel          | name           |
+--------------+----------------+
| 408-282-1540 | Mr. Tux        |
| 408-282-1540 | Ms. Tuxinette  |
| 123-456-7890 | Squishy Junior |
+--------------+----------------+
3 rows in set (0.02 sec)

Let's get a bit more picky, we want only those whose name contains "ux":

mysql> select * from my_table where name like '%ux%';

+------+---------------+--------------+
| id   | name          | tel          |
+------+---------------+--------------+
|    1 | Mr. Tux       | 408-282-1540 |
|    2 | Ms. Tuxinette | 408-282-1540 |
+------+---------------+--------------+
2 rows in set (0.03 sec)

mysql> select * from my_table where id > 1;

+------+----------------+--------------+
| id   | name           | tel          |
+------+----------------+--------------+
|    2 | Ms. Tuxinette  | 408-282-1540 |
|    3 | Squishy Junior | 123-456-7890 |
+------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> select id from my_table where name='Squishy Junior';

+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.01 sec)

Now we have a small table.

The UPDATE sql command changes a row in a table. You can change the table structure itself with the ALTER TABLE command.