MySQL Tutorial - Databases
Ensure you have created a username and database for your hosting account via
supportbot before trying this.
1. Access a database
The below accesses/selects a database for use.
mysql> USE sampledb
Database changed
mysql>
|
|
Replace sampledb with the MySQL databases created
for your web hosting account.
1. Creating a table
Tables define structures for data to be entered into databases. Before we create a table, let's
look up the existing tables in the database we just selected.
mysql> SHOW TABLES;
Empty set (0.01 sec)
mysql>
|
|
As expected, our database currently has no tables.
Our table will hold the following information : name, birth date and favorite color. The below creates a table (named person) for this info.
mysql> CREATE TABLE person (name VARCHAR(20),
-> birth DATE,
-> color VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)
mysql>
|
|
Name and color fields were carted as characters, the birth(date) field as a field of the type DATE.
Let's look up the tables for the database we selected again. Our newly
created table should show up.
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_sampledb |
+-----------------------+
| person |
+-----------------------+
1 row in set (0.00 sec)
mysql>
|
|
As expected, our table is now listed. Let's request some more information about the table.
mysql> DESCRIBE person;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| color | varchar(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
|
|
The above shows the structure, as defined earlier.
3. Entering data
Now we will place data into our database.
mysql> INSERT INTO person
-> VALUES ('Barney','1980-04-01','purple');
Query OK, 1 row affected (0.00 sec)
mysql>
|
|
The above entered a record for a person named Barney, born on
1980-04-01 with purple as their favorite color.
You can also import data form a .txt file. The .txt file contains
one record per line (in the same order as the created table).
mysql> LOAD DATA LOCAL INFILE "person.txt" INTO TABLE person;
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
|
|
You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement, the defaults are tab and linefeed.
3. Retrieving data
There are too many different way to retrieve data to cover here.
The most basic example simple shows all data in the requested table.
mysql> SELECT * FROM person;
+--------+--------------+--------+
| name | birth | color |
+--------+--------------+--------+
| Barney | 1980-04-01 | purple |
| Bozo | 1972-12-18 | red |
| Zorro | 1948-12-09 | black |
+--------+--------------+--------+
3 rows in set (0.00 sec)
mysql>
|
|
The below shows how you can request data from a table for one record only.
mysql> SELECT * FROM person WHERE name="Barney";
+--------+--------------+--------+
| name | birth | color |
+--------+--------------+--------+
| Barney | 1980-04-01 | purple |
+--------+--------------+--------+
1 row in set (0.00 sec)
mysql>
|
|
For more complex queries, visit the MySQL web site.