Page 243 - Open Soource Technologies 304.indd

Unit 10: Database

This command requires a few command line arguments to get it connected properly. A typical Notes
command line may be use is as follows:
mysql -uuser -ppassword -hhost.domain.com database
• -u specifies the database user account to use
• -p specifies the database user password to use
• -h specifies the database server to connect to
• database specifies the database to act on
Once you are connected you will see this:

Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 1 to server version: 3.21.33b
Reading history-file /home/aawtrey/.mysql_history
Type ‘help’ for help.
mysql>
That mysql> prompt will allow you to enter any valid SQL statement and it will be executed
by the database.
SQL statements can be entered all on one line or broken into smaller pieces
since the “mysql” program ignores extra white space which includes tabs,
spaces, or carriage returns.

Creating Tables
We want to store messages our site visitors leave. We want to track who they are, their email
address, the time they visited and their message. Additionally, we need to define a primary key
to uniquely identify the record. Since we may get more than one “Tom” we can not use any
of the data fields for the key. Here is the SQL statement to create a table to store the data in.

CREATE TABLE guests (
guest_id int(4)
unsigned
zerofill
DEFAULT ‘0000’
NOT NULL
auto_increment,
guest_name varchar(50),
guest_email varchar(50),
guest_time timestamp(14),
guest_message text,
PRIMARY KEY (guest_id)

);
We have defined five types of data to track for our guestbook. The first field is named “guest_id”.
The “int(4)” means it is a 4 digit number. The “unsigned” means that it can only be a positive
number. The “zerofill” is something added to force the number to take up all the digits by
displaying leading zeros. Using that parameter will make the numbers “0001”, “0002”, etc.
Issuing the DEFAULT statement makes the counting start at “0000”.

LOVELY PROFESSIONAL UNIVERSITY 237