Howto: Using a MYSQL Server Database
Any session starts by connecting to the MySQL server. For API’s which require a servername, use localhost.
The example below shows you how to connect to the server from a unix shell.
mysql -u username -p mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1136 to server version: 4.1.11-Debian_4sarge4-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
Lets Query the Database:
mysql> SELECT CURRENT_DATE;
+—————–+
| CURRENT_DATE |
+—————–+
| 2006-09-27 |
+—————–+
1 row in set (0.00 sec)
A simple example of course, notice that usual c syntax is used for termination of a command. There is an exception to this rule or two, quit is one of them.
When mysql is done executing the command mysql> prompt is returned
Lets Query Multiple Entries:
mysql> SELECT VERSION(),CURRENT_DATE;
+—————————+————–+
| VERSION() | CURRENT_DATE |
+—————————+————–+
| 4.1.11-Debian_4sarge4-log | 2006-09-27 |
+—————————+————–+
1 row in set (0.00 sec)
Accessing a Database:
mysql> USE mydb
database changed
mysql>
You need to replace mydb with the mysql database you created with your webhost (since this is a simple guide, im assuming your not running your own SQL server, etc).
Lets Create a Table:
Tables define structures for data to be entered into databases. Before we create the table, let’s look up the existing tables in the database we selected. For me my database is called mysql.
mysql> SHOW TABLES;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
As I expected, my tables do exist in my database and MYSQL has happily shown them to me.
Lets Create a Table:
mysql> CREATE TABLE person (name VARCHAR(20),
-> birth DATE,
-> website VARCHAR(20));
Query OK, 0 rows affected (0.08 sec)
Excellent the table was created successfully. If your feeling overly happy and want to check that the person table exists, just type SHOW TABLES; again at the mysql> prompt. You will probably want to , at some point, view the specifics of your table, in SQL this is called DESCRIBE.
Show Table Data (DESCRIBE):
mysql > DESCRIBE person
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| NAME | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| website | varchar(20) | YES | | NULL | |
+———+————-+——+—–+———+——-+
Entering Data:
mysql> INSERT INTO person
-> VALUES(’Azio’,'1984-07-21′,’www.azio.org’);
Query OK, 1 row affected (0.00 sec)
Viewing your shiny new table with a query:
mysql> SELECT * FROM person
+——+————+————–+
| NAME | birth | website |
+——+————+————–+
| Azio | 1984-07-21 | www.azio.org |
+——+————+————–+
Da Da!