Connecting to a MySQL Database with a Database Client

Now that you have installed and configured your database, you will need to connect to it and access data. This can be done in many ways. For instance, you can use the plethora of tools from the command line some tools are proprietary, some open source. You can also use most standard web browsers. You can also use tools such as the MySQL Control Center, as seen in the previous section, or another client, called Knoda, that comes with your SUSE Linux CD-ROMs.

Let's begin by learning how to use a simple command-line database client called MySQL Monitor.

Using the MySQL Monitor Database Client

The MySQL Monitor client allows you to connect to a MySQL server and manipulate its databases. To establish a connection, complete the following:

1.	Open a terminal session and switch to your Root user account.
2.	At the shell prompt, enter mysql -h localhost -u root -p. Note This command assumes you are connecting to a database running on the same system. You can use the -h option to specify a different hostname if the MySQL database you want to connect to is running on a different system.
3.	When prompted, enter the password you created previously for your MySQL Root user account. The screen shown in Figure 29.9 is displayed. Figure 29.9. Using mysql.

Notice that the shell prompt has been replaced with a mysql prompt, indicating that mysql is running. From this prompt, you can enter commands to manage and manipulate your databases.

For example, suppose you want to create a new database named contacts, create a table within it, and insert data into the table. You can do this by completing the following:

1. At the mysql prompt, enter CREATE DATABASE contacts;. You should be prompted that the database has been created.

2. To tell the mysql client to use the database you just created, enter USE contacts at the mysql prompt. The utility should respond with Database changed.

3. To see the tables that have been defined by default within the contacts database, enter SHOW TABLES; at the mysql prompt. It should report Empty set, indicating that no tables have been defined.

   At this point, things get a little more complex. You will need to use the CREATE TABLE command to create the table. That's easy enough; however, you will also need to define your table's columns in the command as well. To do this, you first need to define the columns that you want to include in the table. Then you need to decide what kind of data will be stored in each column as well as how long the data in the table will be.

4. In the table we're building here, let's suppose we want four columns:

   • first

   • last

   • phone

   • lastcontact

   We know that the first and last columns will contain alphabetical characters of an undetermined length. We know that phone will contain 10 numbers with two hyphens (area_code-prefix-number). The lastcontact column will contain the date of last contact. In the next step, you will enter the CREATE TABLE command that will create a table named vendors and format these columns.

5. At the mysql prompt, enter CREATE TABLE vendors (first VARCHAR(15), last VARCHAR(15), phone CHAR(12), lastcontact DATE);.

6. Verify that the table and columns were created correctly by entering DESCRIBE vendors; at the mysql prompt. You should see that the vendors table and its associated columns were created, as shown in Figure 29.10.

   Figure 29.10. Creating the vendors table.

   
   
   

   Notice in Figure 29.9 that each column is null, indicating that no data has been added to the table. In the next step, you will use mysql to add a vendor named Diana Nelson, whose phone number is 801-555-1234 and was last contacted on July 24, 2005.

7. To insert the preceding data into the table, enter INSERT INTO vendors VALUES ('Diana','Nelson','801-555-1234','2005-07-24'); at the mysql prompt. The utility should prompt you that the query was OK and that one row was affected.

8. To view the data in your table, enter SELECT * FROM vendors; at the mysql prompt. Your MySQL server should respond with all the data in the table, as shown in Figure 29.11.

   Figure 29.11. Displaying the contents of the vendors table.

   
   
   

You've just built a very basic MySQL database and table. This is only a small taste of what you can do with MySQL. If you want to learn more advanced MySQL skills, visit http://www.mysql.com.

Let's now take a look at the Knoda database client.

Knoda is a database front end for MySQL and PostgreSQL and contains a generic ODBC driver. You can use it for creating tables and databases, forms, reports, and queries. Knoda has also an export/import filter for CSV files. The package contains a general library with dialog boxes for the handling of databases, which can be used in other applications. It can be accessed by going to the KDE menu and selecting Office, Database, Knoda. You can see the Knoda client version 0.7 in Figure 29.12.

Figure 29.12. Using the Knoda client.

To configure the client, you can select mysql from the drop-down box so that the MySQL database can be accessed and managed with the Knoda client. To adjust the client, click the Options button on the Knoda client. The Preferences dialog box opens, as shown in Figure 29.13.

Figure 29.13. Setting the Knoda client preferences.

To attach to the database, click the Connect button on the Knoda client. This will either connect you to or stop you from connecting to the MySQL database. Most times, problems will be miniscule. Other times, the issues (and what you need to do to resolve them) could take weeks. Working in the world of programming and development will take some time, practice, and patience. If you have problems connecting to your database, try rebooting your system and checking inetd for the running service or the local firewall to make sure that MySQL ports are able to be used by the systemthe system .