Logo Your Web Hosting Solution
Line
MySQL Manual
Introduction
Connecting
Queries
Databases
Import & Export
Tutorials Index
Home Page
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.

Top Of Page 
Line
Copyright© 1996 - 2024 Clockwatchers, Inc.