This tip walks you through the steps of creating a database in mySQL. The database created in this tip, is a simple database and table to be used with a guest book application.
Create the Database File
The first step is to create the database file. Start the mySQL client and type the following command:
mysql> create database guestdb;
Query OK, 1 row affected (0.00 sec)
mysql>
The command creates the the database,
guestdb. If successful, you should receive a response like the one shown above.
After the database is created, we must select it to continue with our database building.
mysql> use guestdb;
Database changed
mysql>
Now with our database selected, we are ready for the next step.
Create a Table
Next, you need to create a database table to hold your data. The SQL here defines a table. Below is the command and the output from the command. The database created below will be used in a web guestbook application.
mysql> create table guestTbl
-> (msgID int not null primary key auto_increment,
-> name varchar(30),
-> email varchar(30),
-> msgFrom varchar(30),
-> msgDate timestamp(14),
-> msgBody text);
Query OK, 0 rows affected (0.11 sec)
mysql>
In this example, the
create command creates a table named
guestTbl. The table has six fields described below.
msgID - a unique number assigned to each entry. The field hold an integer value that is automatically incremented each time an entry is added.
name - a 30 character field holding the name of the guest.
email - a 30 character field holding the email address of the guest.
msgFrom - a 30 character field holding the location of the guest.
msgDate - the date and time the entry is added to the database.
msgBody - a text field holding a text message entered by the guest.
Our next step is to add data to the table.
Add data to the Table
Now we need to add some sample data to the table, so we can actually run some queries against it.
mysql> insert into guestTbl
-> (msgID, name, email, msgFrom, msgDate, msgBody) values
-> (NULL, 'Test User1', 'testuser@test.com','Smallville USA',now(),
-> 'This is the body for message 1.');
Query OK, 1 row affected (0.27 sec)
mysql>
The above command adds one row to the database. If we repeat this process for three other users, we have enough data to perform a query on the table.
Query the Database
Now we can query our database. To retrieve all the data in the table, perform the following command.
mysql> select * from guestTbl;
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
| msgID | name | email |
msgFrom | msgDate | msgBody |
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
| 1 | Test User1 | testuser@test.com |
Smallville USA | 20001117154626 | This is the body for message 1. |
| 2 | Test User2 | testuser2@test.com |
Smalltown USA | 20001117155043 | This is the body for message 2. |
| 3 | Test User3 | testuser3@test.com |
Smallcity CA | 20001117155201 | This is the body for message 3. |
| 4 | Test User4 | testuser4@test.com |
LittleTown CO | 20001117155319 | This is the body for message 4. |
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
4 rows in set (0.06 sec)
mysql>
First off, you'll notice that if the data does not fit on a line, it is wrapped. All the data entered for the four test users is there. We can get cleaner looking output by selecting a couple of fields.
For example:
mysql> select name,email,msgFrom from guestTbl;
+------------+--------------------+----------------+
| name | email | msgFrom |
+------------+--------------------+----------------+
| Test User1 | testuser@test.com | Smallville USA |
| Test User2 | testuser2@test.com | Smalltown USA |
| Test User3 | testuser3@test.com | Smallcity CA |
| Test User4 | testuser4@test.com | LittleTown CO |
+------------+--------------------+----------------+
4 rows in set (0.05 sec)
mysql>
Quite a bit more readable.
Summary
This tip walked through the steps of creating a test database in mySQL. If you followed along, you should be able to create the db, table, insert data, and then retrieve data via a query. I hope it is helpful.
----Source : Internet
Do post your queries and comments .. It may help me and others to improve knowledge..
